Thanks for the answer.
I have changed the script now to use months_between function. The objective of the script was to do the below

1. Given a date (some time in the back say 28/03/1999)
2. Find out the monthly anniversary date just immediately after the system date.

So after making the changes as you have suggested if I run this using input date (for Point 1) as 28/03/1999 and a system date as 01/04/2020 what I get is 28/03/2020 back which is understood from the script.
Then I need to apply just one more add_months to arrive at the date I need.

I think this method gives me a consistent and correct answer all the time to the objective as I have mentioned above.

Thanks for helping me out.
~ Arnab P

The method you suggested as a solution for the problem is the correct one indeed.
But I would like to ask if the design of add_months is good enough.

As you mention, the doc says: "If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date."

So it works as designed. The problem with this function is, that it's missing a few days each year.
Say for instance:

select add_months(to_date('30/11/2021','dd/mm/yyyy'),1) from dual;

ADD_MONTHS(TO_DATE('30/11/2021','DD/MM/YYYY'),1)
------------------------------------------------
31-DEC-21 

If an application needs, for any reason, 29/11/2021 to return 29/12/2021, then the same application might expect 30/11/2021 to return 30/12/2021 - but 30/12/2021 is "hidden" by the function and cannot be achieved.

I would argue, that add_months should have a second, optional in parameter, that alters between "last day" in according to application specifications.

select date'2020-11-30' + interval '1' month ,
       add_months ( date'2020-11-30', 1 ) 
from   dual;

DATE'2020-11-30'+INTERVAL'1'MONTH    ADD_MONTHS(DATE'2020-11-30',1)   
30-DEC-2020 00:00:00                 31-DEC-2020 00:00:00   

select date'2020-10-31' + interval '1' month
from   dual;

ORA-01839: date not valid for month specified

I agree with A. Olsen when saying
"add_months should have a second, optional in parameter, that alters between "last day" in according to application specifications."

Meanwhile, this is how I solved when running into this problem today, Reconciling reports made in pure SQL with same reports in Informatica,
turned out to have minor differences in some corner cases.

create or replace function infa_add_months(in_date date, in_months number)
return date deterministic
is
    -- Implementation style Informatica's ADD_TO_DATE
    -- Add_to_date adds one month like this:
    --   Date 2022-03-31 + 1 Month = 2022-04-31
    -- Since 2022-04-31 is not a valid date, then last day of the month
    -- is returned, that is 2022-04-30. In the same way, 
    --   Date 2022-04-30 + 1 Month is 2022-05-30

    l_date date;
begin
    l_date := add_months(in_date, in_months);
    
    -- Oracle's add_month will preserve the "Last Day", E.g:
    -- Adding one month to 2022-04-30 results in 2022-05-31
    -- Informatica does not do that, and will return 2022-05-30
    -- We compensate by saying new day of month cannot be greater than
    -- original day of month.
    if extract(day from l_date) > extract(day from in_date)
    then
      l_date := l_date - (extract(day from l_date) - extract(day from in_date));
    end if;
    
    return l_date;
end infa_add_months;
/

At least it explains the logic behind it, but I was getting a result yesterday when comparing last year's data with actual data and that was showing based on this function, so instead of getting data for the 28th the query wanted to return data for the 29th.

SELECT ADD_MONTHS(TO_DATE('2023-02-27', 'yyyy-mm-dd'), 12), ADD_MONTHS(TO_DATE('2023-02-28', 'yyyy-mm-dd'), 12)
FROM DUAL;

ADD_MONTHS(TO_DATE('2023-02-27','YYYY-MM-DD'),12)
-------------------------------------------------
ADD_MONTHS(TO_DATE('2023-02-28','YYYY-MM-DD'),12)
-------------------------------------------------
27-FEB-24
29-FEB-24

Basically, while in 99.99% of the cases, add_months returns the wanted result, I would consider the behavior as a bug in ths situation.

I've seen conversations go back and forth on many occassions about this - I've wasted more time than I care to admit on this topic!
The fact is given a month isn't a precise unit of time - so there is always going to be vaguery and interpretation. You may as well try to define the length of a piece of string.

Given that that 28/2/2023 is the last day of Feburary 2023, then I would say 12 months later is the last day of Feburary 2024 - so 29/2/2024.
So The ADD_MONTHS(,12) function I would argue is the correct answer or at the very least an acceptable answer, not a bug.

Looking at it the other way, if we insist 28/2/2024 (not the 29th) is considered 1 year later than 28/2/2023, then presumably we can all agree one year earlier than 28/2/2024 is 28/2/2023.
So what is one year earlier than 29/2/2024? It can't be 29/2/2023 so what is is:-
* 1/3/2023 - This is in a completely different month, so could be perceived to be "wrong" (although I imagine many leap year birthdays are celebrated then)
* 28/2/2023 - so we are saying one year earlier than BOTH 28/2/2024 and 29/2/2024 falls on the same day, which could also be perceived to be "wrong".

We often get the same debate in any year, leap or not, when people don't like that one month after 31st January is 28th (or 29th) February - they see it as having less time to e.g. meet a deadline set by the application.
However, they can't give a precise definition of what constitutes one month, that works in all cases, both while adding and subtracting. Any suggestions they give are just as "perceviably wrong" and subject to a counter-argument as the problem they are trying to solve.
The same people who are paid monthly do not have a problem with technically being paid more per hour in February than any other month though!

If you are in the "28/2/2023 + 1 year = 28/2/2024" camp, you can always use NUMTODSINTERVAL:-

SELECT TIMESTAMP '2023-02-28 00:00:00' + NUMTOYMINTERVAL (1,'YEAR') oneyearlater
FROM DUAL;
ONEYEARLATER
---------------------------------------------------------------------------
28-FEB-24 00.00.00.000000000


However, this doesn't work so well in reverse....
SELECT TIMESTAMP '2024-02-29 00:00:00' - NUMTOYMINTERVAL (1,'YEAR') oneyearearlier
FROM DUAL;
*
ERROR at line 1:
ORA-01839: date not valid for month specified