APEX

Hi All,

I am getting the ORA-01839: date not valid for month specified error when I run the below code as a On Load - After Region as the Process Point and the ORA-01840: input value not long enough for date format error when its in the On Load - Before Header process point.

:p127_year is a page item select list that returns the year as a varchar...

I have no clue as to why I am getting this error

Please help!

Thanks in advance

Sidharth
---------------------------------


DECLARE

xxx number;

yyy number;

r_id number;

YEAR char(100);

temp_start_date_date date;

temp_end_date_date date;

temp_month char(11);

i number;

temp_i varchar2(1000);

BEGIN

DELETE FROM PSA_MONTHLY_ACTIVE;

i:= 1;

LOOP

temp_i:= TO_CHAR(i,'99');

YEAR:= TO_CHAR(:p127_year_select);

SELECT PSA_LOOKUP.CODE_VALUE INTO temp_month FROM PSA_LOOKUP WHERE PSA_LOOKUP.CODE_NAME = 'PSA_MONTH' AND TO_CHAR(DESCRIPTION,'99') = temp_i AND ACTIVE_FLAG = 'Y';

i:= i + 1;

SELECT TO_DATE('01-'||temp_month||'-'||substr(YEAR,3,2),'dd-mon-yy') INTO temp_start_date_date FROM DUAL;

IF(1=1) THEN

IF temp_month = 'SEP' OR temp_month = 'APR' OR temp_month = 'JUN' OR temp_month = 'NOV' THEN

SELECT TO_DATE('30-'||temp_month||'-'||substr(YEAR,3,2),'dd-mon-yy') INTO temp_end_date_date FROM DUAL;

ELSE

SELECT TO_DATE('31-'||temp_month||'-'||substr(YEAR,3,2),'dd-mon-yy') INTO temp_end_date_date FROM DUAL;

END IF;

IF temp_month = 'FEB' THEN

IF REMAINDER(TO_NUMBER(YEAR),4) = 0 THEN
SELECT TO_DATE('29-'||temp_month||'-'||substr(YEAR,3,2),'dd-mon-yy') INTO temp_end_date_date FROM DUAL;

ELSE
SELECT TO_DATE('28-'||temp_month||'-'||substr(YEAR,3,2),'dd-mon-yy') INTO temp_end_date_date FROM DUAL ;

END IF;

ELSE

SELECT TO_DATE('31-'||temp_month||'-'||substr(YEAR,3,2),'dd-mon-yy') INTO temp_end_date_date FROM DUAL;

END IF;

END IF;


SELECT COUNT(PSA_SERVICE_PARTNER.SP_ROW_ID) INTO xxx FROM PSA_SERVICE_PARTNER, PSA_SP_CONTRACT_DETAILS WHERE

months_between(decode("PSA_SP_CONTRACT_DETAILS"."CONTRACT_START_DATE",null,temp_end_date_date,"PSA_SP_CONTRACT_DETAILS"."CONTRACT_START_DATE","PSA_SP_CONTRACT_DETAILS"."CONTRACT_START_DATE"),temp_end_date_date)<=0 AND

months_between(decode("PSA_SP_CONTRACT_DETAILS"."CONTRACT_END_DATE",null,temp_start_date_date,"PSA_SP_CONTRACT_DETAILS"."CONTRACT_END_DATE","PSA_SP_CONTRACT_DETAILS"."CONTRACT_END_DATE"),temp_start_date_date)>=0 AND

months_between(decode("PSA_SP_CONTRACT_DETAILS"."CALCULATED_TERMINATE_DATE",null,temp_start_date_date,"PSA_SP_CONTRACT_DETAILS"."CALCULATED_TERMINATE_DATE","PSA_SP_CONTRACT_DETAILS"."CALCULATED_TERMINATE_DATE"),temp_start_date_date)>=0 AND

PSA_SERVICE_PARTNER.SP_ROW_ID = PSA_SP_CONTRACT_DETAILS.SP_ROW_ID;


SELECT COUNT(PSA_RESOURCE_MANAGER.ROW_ID) INTO yyy FROM PSA_RESOURCE_MANAGER WHERE

months_between(decode("PSA_RESOURCE_MANAGER"."JOIN_DATE",null,temp_end_date_date,"PSA_RESOURCE_MANAGER"."JOIN_DATE","PSA_RESOURCE_MANAGER"."JOIN_DATE"),temp_end_date_date)<=0 AND

months_between(decode("PSA_RESOURCE_MANAGER"."QUIT_DATE",null,temp_start_date_date,"PSA_RESOURCE_MANAGER"."QUIT_DATE","PSA_RESOURCE_MANAGER"."QUIT_DATE","PSA_RESOURCE_MANAGER"."QUIT_DATE"),temp_start_date_date)>=0;


SELECT MAX(ROW_ID) INTO r_id FROM PSA_MONTHLY_ACTIVE;

IF r_id IS null THEN

r_id:= 1;

ELSE

SELECT MAX(ROW_ID)+1 INTO r_id FROM PSA_MONTHLY_ACTIVE;

END IF;

INSERT INTO PSA_MONTHLY_ACTIVE VALUES(r_id,
TO_CHAR(:p127_year_select),
temp_month,
yyy,
xxx);

EXIT WHEN i>12;

END LOOP;

END;