PL/SQL populating a REF CURSOR

[babsjoy]

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;

 

[SantaMufasa]

I have...tried different ways to resolve this without success...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...

So what are the results of the "without success" options you have tried so far (Syntax errors...run-time errors...logic errors [i.e., unexpected/undesirable results])?


Also, without confirmation of the contents/values/data types of your "DATE-like" expressions, it is rather difficult to confirm the quality and behaviours of your data. I highly recommend that when working with Oracle DATE expressions, that (when possible) you try to use actual DATE expressions for comparisons rather than hobbling together TO_CHAR extractions of DATE expressions...It reduces the risk of logic errors due to character-comparison issues.

Looking forward to your post that contains the responses we need to continue,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[LKBrwnDBA]

Aggregate by Year+Month and select rows form jan-01 to the requested date.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[babsjoy]

LKbrwnDBA - this won't work for the last two totals OUT_PLUS_2 and OUT_LESS_2. These fields represent outstanding record count (not completed) older then 2 weeks within each month and outstanding record count (not completed) less then 2 week old within the same month this is done by using the date_recieved field.


SantaMusfa - it will take a little bit to gather the information you requested its forth coming ...

 

[babsjoy]

Santa Musfa - my without success options are many but I can describe a few that I have tried. The big problem is getting the correct results for the last two SUM columns OUT_PLUS_2 AND OUT_LESS_2. These are record counts of what records are outstanding greater then 2 weeks or less then 2 weeks within each month of the year. This is going to change since records are going to be completed (DATE_COMPLETED_DCPS) populated with a date at some point. Therefore if the field is not NULL I have to figure what month or months it was outstanding possibly greater then two weeks or less then 2 weeks.

Field definitions:
CLERK_NUM NUMBER(3)
DATE_RECEIVED DATE
DATE_COMPLETED_DCPS DATE


- your correct I could remove the to_char logic but haven't yet since these fields were just converted from CHAR(8) to DATE

- used SQL CUBE and ROLLUP commands results first two columns gave the correct results couldn't get the last two to work

- SQL JOINS, UNIONS, tried creating a view that would match up each record with a MONTH_END_TABLE (I created and populated manually with all 12 month-end dates).

- using a cursor variable to return a record. Couldn't OPEN CURSOR and received an Actuate error

- began trying to use a temporary table to store query results for each month then create a cursor accessing the temporary table but I haven't gotten to far.

I hope this is what you where looking for.

 

[babsjoy]

I forgot to mention that I did make a suggestion, but the higher ups shot it down, was to run a batch job either weekly or monthly to accumlate the required record count TOTALS and save this information to a table in the database. Then when the user chose to generate the report the information could be obtained right from the table. Might this be the only way to accomplish this?

 

[Thargy]

babs,

I suggest that you divide this problem into two. First of all,work on the query that produces what you want. Learn how to modify it to work for a different month.

When you've done that, create a simple dummy stored procedure that returns a ref_cursor, by selecting from dual say. Once you are sure that you can return the cursor successfully, and have the technique cold, insert your query and it should work.

Once you've cracked that, go on to the weeks etc. By the way, Santa is (as usual) spot on, converting strings to dates is just a bad idea. Convert the strings to dates, and then stop flaffing about with conversion.



Regards

T

 

[babsjoy]

thargtheslayer - this is what I am stuck on.

"modify it to work for a different month"

I can't figure out how to get the results I want for the last to columns in the query to work for 12 different months. I can only get it to work for a single month.

 

[oradba101]

Greetings,

Is Actuate based upon Crystal Reports?

Regards,



William Chadbourne
Programmer/Analyst

 

[babsjoy]

oradba101 - I don't know enough about Crystal Reports. I am guessing they are similar products made by two different companies.
Actuate allows Drag and drop reporting. Connecting to a database and choosing a data source ( I use pl/sql stored procedure). Also allows the user the ability to modify methods, this I am unfamiliar with.

 

[babsjoy]

I have been working on the below (needs some tweaking and tested thoroughly) seems like its kinda giving me what I am looking but yet again those last two columns are difficult to calculate since the DATE_COMPLETED_DCPS field can change from NULL to a date value at any time during the month/months. With this procedure I can't seem to open the cursor. If the cursor doesn't need to be opened in the stored procedure then I receive an Actuate error. "the stored procedure does not have a result set. I'm off on vacation next week so I can not respond to any posts. Thanks in advance for any and all input on this issue.

CREATE OR REPLACE PACKAGE TC_REPORTS_TEST AS
TYPE TEST_REPORT_REC IS RECORD
(RPT_DATE DATE,
TOTAL_REC NUMBER,
TOTAL_COMP NUMBER,
TOTAL_OUTSTAND_1 NUMBER,
TOTAL_OUTSTAND_2 NUMBER,
TOTAL_UNTOUCHED NUMBER,
TOTAL_RECVD_PER_WEEK NUMBER);
TYPE GEN_CURSOR IS REF CURSOR RETURN TEST_REPORT_REC;

PROCEDURE ASIRTC001(P_MONTH_END_DATE IN VARCHAR2,
P_REPORT_DATA OUT GEN_CURSOR);


END TC_REPORTS_TEST;
/

CREATE OR REPLACE PACKAGE BODY MISTPK540_TC_REPORTS_TEST AS

PROCEDURE ASIRTC001(P_MONTH_END_DATE IN VARCHAR2,
P_REPORT_DATA OUT GEN_CURSOR)

IS

V_LAST_RPT_MONTH VARCHAR(10);

V_MONTH_END_DATE DATE;
V_BEGIN_MONTH VARCHAR2(3);
V_BEGIN_YEAR VARCHAR2(4);
V_BEGIN_DATE DATE;
V_MONTH VARCHAR2(2);



TYPE TEST_REPORT_REC IS RECORD
(RPT_DATE DATE,
TOTAL_REC NUMBER,
TOTAL_COMP NUMBER,
OUTSTAND_PLUS_2 NUMBER,
OUTSTAND_LESS_2 NUMBER,
TOTAL_UNTOUCHED NUMBER,
TOTAL_RECVD_PER_WEEK NUMBER);

-- TYPE R_CURSOR IS REF CURSOR RETURN TEST_RPT_REC;
R_REC TEST_REPORT_REC;

CURSOR MONTH_END_DATA IS
SELECT CALENDAR_MONTH_END
FROM NEWPORT.CALENDAR_MONTH_END_DATES;


BEGIN

V_LAST_RPT_MONTH := SUBSTR(P_MONTH_END_DATE,1,2) + 1;


-- OPEN P_REPORT_DATA
FOR M_REC IN MONTH_END_DATA
LOOP
R_REC.RPT_DATE:= M_REC.CALENDAR_MONTH_END;
V_MONTH := TO_CHAR(M_REC.CALENDAR_MONTH_END,'MM');

IF V_LAST_RPT_MONTH > V_MONTH THEN

V_BEGIN_MONTH:= SUBSTR(M_REC.CALENDAR_MONTH_END,4,3);
V_BEGIN_YEAR := SUBSTR(M_REC.CALENDAR_MONTH_END,8,2);

V_BEGIN_DATE := '01-'||V_BEGIN_MONTH||V_BEGIN_YEAR;
V_MONTH_END_DATE := M_REC.CALENDAR_MONTH_END;




SELECT COUNT(*) INTO R_REC.TOTAL_REC
FROM NEWPORT.TRAVEL_COMP_LOG
WHERE CLAIM_STATUS = 'N'
AND DATE_RECEIVED >= V_BEGIN_DATE
AND DATE_RECEIVED <= V_MONTH_END_DATE;

SELECT COUNT(*) INTO R_REC.TOTAL_COMP
FROM NEWPORT.TRAVEL_COMP_LOG
WHERE CLAIM_STATUS = 'N'
AND DATE_RECEIVED >= V_BEGIN_DATE
AND DATE_RECEIVED <= V_MONTH_END_DATE
OR (DATE_COMPLETED_DCPS >= V_BEGIN_DATE
AND DATE_COMPLETED_DCPS <= V_MONTH_END_DATE);


-- OUTSTANDING FOR 2 WEEKS OR MORE WITHIN EACH MONTH
SELECT COUNT(*) INTO R_REC.OUTSTAND_PLUS_2
FROM NEWPORT.TRAVEL_COMP_LOG
WHERE CLAIM_STATUS = 'N'
AND DATE_COMPLETED_DCPS IS NULL
AND DATE_RECEIVED >= '01-'||V_BEGIN_MONTH||V_BEGIN_YEAR
AND DATE_RECEIVED <= '14-'||V_BEGIN_MONTH||V_BEGIN_YEAR
OR (DATE_COMPLETED_DCPS > V_MONTH_END_DATE
AND DATE_COMPLETED_DCPS <= '14-'||V_BEGIN_MONTH||V_BEGIN_YEAR);


-- OUTSTANDING FOR LESS THAN 2 WEEKS WITHIN EACH MONTH
SELECT COUNT(*) INTO R_REC.OUTSTAND_LESS_2
FROM NEWPORT.TRAVEL_COMP_LOG
WHERE CLAIM_STATUS = 'N'
AND DATE_RECEIVED >= '15-'||V_BEGIN_MONTH||V_BEGIN_YEAR
AND DATE_RECEIVED <= V_MONTH_END_DATE
AND DATE_COMPLETED_DCPS IS NULL
OR (DATE_RECEIVED >= '15-'||V_BEGIN_MONTH||V_BEGIN_YEAR
AND DATE_RECEIVED <= V_MONTH_END_DATE
AND DATE_COMPLETED_DCPS > V_MONTH_END_DATE );



-- P_REPORT_DATA := TEST_REPORT_REC;

END IF;
END LOOP;