1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| CREATE OR REPLACE FUNCTION F_1300_GET_DATE
RETURN DATE AS PROCESSING_DATE DATE;
MANUAL_DATE VARCHAR2(10);
V_SYSDATE VARCHAR2(10);
stmt VARCHAR2(50);
COUNTER NUMBER;
RUN_MODE NUMBER(1) DEFAULT 0;
BEGIN
-- get the run mode from the control table "FND_1200_CONTROL"
EXECUTE IMMEDIATE 'select count(*) from FND_1200_CONTROL_TABLE' INTO COUNTER;
IF COUNTER=1
THEN EXECUTE IMMEDIATE 'select run_mode from FND_1200_CONTROL_TABLE' INTO RUN_MODE;
EXECUTE IMMEDIATE 'select to_char(date_manual,''DD.MM.YYYY'') from FND_1200_CONTROL_TABLE' INTO MANUAL_DATE;
END IF;
-- current date
stmt := 'select to_char(sysdate,''DD.MM.YYYY'') from dual';
execute immediate stmt into V_SYSDATE;
IF RUN_MODE=0
-- last day of the previous month
THEN PROCESSING_DATE := LAST_DAY(ADD_MONTHS(TO_DATE(V_SYSDATE,'DD.MM.YYYY'),-1));
ELSIF RUN_MODE=1
-- current date
THEN PROCESSING_DATE := V_SYSDATE;
ELSIF RUN_MODE=2
-- set the date manual
THEN PROCESSING_DATE := TO_DATE(MANUAL_DATE,'DD.MM.YYYY');
END IF;
RETURN PROCESSING_DATE;
END F_1300_GET_DATE; |
Partager