in a form there are 2 page items Age and DOB, on Age there is a DA on Change, True action Set Value is of Type PL/SQL Function body, the code is:
IF :P43_AGE IS NOT NULL THEN
RETURN add_months(TRUNC(sysdate,'YYYY'),0-12* :P43_AGE);
END IF;
it sets DOB column, and DOB column on DA sets values of another 3 columns Years, Months, Days (prefix is Age) , DA is on Change here too, and it sets value too as below:
SELECT TRUNC(MONTHS_BETWEEN(:P43_REG_DATE,:P43_DOB)/12) YR,
TRUNC(MOD(MONTHS_BETWEEN(:P43_REG_DATE,:P43_DOB),12)) MN,
TRUNC(SYSDATE-ADD_MONTHS(:P43_DOB,TRUNC(MONTHS_BETWEEN(:P43_REG_DATE,:P43_DOB)/12)*12+TRUNC(MOD(MONTHS_BETWEEN(:P43_REG_DATE,:P43_DOB),12)))) DY
FROM DUAL;
now, user require if i enter 45 in Age column, it should calculate all things which is working fine, but if there is no Age data to enter and user Select a date in Date Picker it should calculate all column values including Age column. how to handle it? when sets Age column value it is firing DA on Change to set value of DOB and this starts an infinite loop.
please help to overcome this situation.
regards