I'm stumped...I have researched and tried different ways to resolve this without success. Hopefully someone can give me some ideas because I'm all out. I am trying to populate a REF CURSOR in a PL/SQL stored procedure so that Actuate 8 reporting software can access to generate a report. I can generate the report for a single month but now the user wants to generate the report for all 12 months of the year or up to the P_MONTH_END_DATE entered. Any ideas would really help since the user also want a similar report for every week of the year too...
Here is what works for a single month:
SPEC:
CREATE OR REPLACE PACKAGE TRAVEL_REPORTS AS
TYPE GEN_CURSOR IS REF CURSOR;
PROCEDURE ASIRTC001(P_MONTH_END_DATE IN VARCHAR2,
P_REPORT_DATA OUT GEN_CURSOR);
END TRAVEL_COMP_REPORTS;
/
PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY TRAVEL_COMP_REPORTS AS
PROCEDURE ASIRTC001(P_MONTH_END_DATE IN VARCHAR2,
P_REPORT_DATA OUT GEN_CURSOR)
IS
V_YEAR VARCHAR2(4);
V_CALENDAR_MONTH VARCHAR2(2);
V_CALENDAR_YEAR_MONTH VARCHAR2(6);
BEGIN
V_CALENDAR_YEAR_MONTH := SUBSTR(P_MONTH_END_DATE,1,6);
V_CALENDAR_MONTH := SUBSTR(P_MONTH_END_DATE,5,2);
V_YEAR := SUBSTR(P_MONTH_END_DATE,1,4);
OPEN P_REPORT_DATA FOR
SELECT
SUM(TOTAL_REC),
SUM(TOTAL_COMP),
SUM(OUT_PLUS_2),
SUM(OUT_LESS_2)
FROM(
SELECT
CASE WHEN TO_CHAR(DATE_RECEIVED,'YYYYMMDD') >= V_CALENDAR_YEAR_MONTH||'01'
AND
TO_CHAR(DATE_RECEIVED,'YYYYMMDD') <= P_MONTH_END_DATE
THEN + 1
ELSE 0
END AS TOTAL_REC,
CASE WHEN TO_CHAR(DATE_COMPLETED_DCPS,'YYYYMMDD') >= V_CALENDAR_YEAR_MONTH||'01' AND
TO_CHAR(DATE_COMPLETED_DCPS,'YYYYMMDD') <= P_MONTH_END_DATE AND
CLERK_NUM <> 0 AND
DATE_COMPLETED_DCPS IS NOT NULL THEN + 1
ELSE 0
END AS TOTAL_COMP,
CASE WHEN DATE_COMPLETED_DCPS IS NULL AND
TO_CHAR(DATE_RECEIVED,'YYYYMMDD') <= V_CALENDAR_YEAR_MONTH||'14' THEN + 1
ELSE 0
END AS OUT_PLUS_2,
CASE WHEN DATE_COMPLETED_DCPS IS NULL
AND TO_CHAR(DATE_RECEIVED,'YYYYMMDD') >= V_CALENDAR_YEAR_MONTH||'15'
AND
TO_CHAR(DATE_RECEIVED,'YYYYMMDD') <= P_MONTH_END_DATE THEN +1
ELSE 0
END AS OUT_LESS_2
FROM NEWPORT.TRAVEL_COMP_LOG
WHERE CLAIM_STATUS = 'N');
EXCEPTION
WHEN OTHERS THEN
LOG81.SAVELINE(SQLCODE,SQLERRM,'ASIRTC001','');
RAISE_APPLICATION_ERROR (-20010,TO_CHAR(SQLCODE)||' '||SQLERRM);
END ASIRTC001;
Here is what works for a single month:
SPEC:
CREATE OR REPLACE PACKAGE TRAVEL_REPORTS AS
TYPE GEN_CURSOR IS REF CURSOR;
PROCEDURE ASIRTC001(P_MONTH_END_DATE IN VARCHAR2,
P_REPORT_DATA OUT GEN_CURSOR);
END TRAVEL_COMP_REPORTS;
/
PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY TRAVEL_COMP_REPORTS AS
PROCEDURE ASIRTC001(P_MONTH_END_DATE IN VARCHAR2,
P_REPORT_DATA OUT GEN_CURSOR)
IS
V_YEAR VARCHAR2(4);
V_CALENDAR_MONTH VARCHAR2(2);
V_CALENDAR_YEAR_MONTH VARCHAR2(6);
BEGIN
V_CALENDAR_YEAR_MONTH := SUBSTR(P_MONTH_END_DATE,1,6);
V_CALENDAR_MONTH := SUBSTR(P_MONTH_END_DATE,5,2);
V_YEAR := SUBSTR(P_MONTH_END_DATE,1,4);
OPEN P_REPORT_DATA FOR
SELECT
SUM(TOTAL_REC),
SUM(TOTAL_COMP),
SUM(OUT_PLUS_2),
SUM(OUT_LESS_2)
FROM(
SELECT
CASE WHEN TO_CHAR(DATE_RECEIVED,'YYYYMMDD') >= V_CALENDAR_YEAR_MONTH||'01'
AND
TO_CHAR(DATE_RECEIVED,'YYYYMMDD') <= P_MONTH_END_DATE
THEN + 1
ELSE 0
END AS TOTAL_REC,
CASE WHEN TO_CHAR(DATE_COMPLETED_DCPS,'YYYYMMDD') >= V_CALENDAR_YEAR_MONTH||'01' AND
TO_CHAR(DATE_COMPLETED_DCPS,'YYYYMMDD') <= P_MONTH_END_DATE AND
CLERK_NUM <> 0 AND
DATE_COMPLETED_DCPS IS NOT NULL THEN + 1
ELSE 0
END AS TOTAL_COMP,
CASE WHEN DATE_COMPLETED_DCPS IS NULL AND
TO_CHAR(DATE_RECEIVED,'YYYYMMDD') <= V_CALENDAR_YEAR_MONTH||'14' THEN + 1
ELSE 0
END AS OUT_PLUS_2,
CASE WHEN DATE_COMPLETED_DCPS IS NULL
AND TO_CHAR(DATE_RECEIVED,'YYYYMMDD') >= V_CALENDAR_YEAR_MONTH||'15'
AND
TO_CHAR(DATE_RECEIVED,'YYYYMMDD') <= P_MONTH_END_DATE THEN +1
ELSE 0
END AS OUT_LESS_2
FROM NEWPORT.TRAVEL_COMP_LOG
WHERE CLAIM_STATUS = 'N');
EXCEPTION
WHEN OTHERS THEN
LOG81.SAVELINE(SQLCODE,SQLERRM,'ASIRTC001','');
RAISE_APPLICATION_ERROR (-20010,TO_CHAR(SQLCODE)||' '||SQLERRM);
END ASIRTC001;