Home » SQL & PL/SQL » SQL & PL/SQL » Cursor within a cursor (Oracle Database 11g Enterprise Edition Release )
Cursor within a cursor [message #665278] |
Mon, 28 August 2017 13:19 |
|
megha2525
Messages: 62 Registered: June 2012 Location: columbus
|
Member |
|
|
Hi Everyone,
I do have a query that returns the following rows.
Attached is a screenshot showing the sample data.
For each project_id, there are multiple rows.
I have a requirement to calculate the budget, expenses and revenue.
So the code I have is
DECLARE
v_bdgt gl.gl_balances.project_to_date_dr%TYPE;
v_exp gl.gl_balances.project_to_date_dr%TYPE;
v_rev gl.gl_balances.project_to_date_dr%TYPE;
v_rec gl.gl_balances.project_to_date_dr%TYPE;
v_curr_net_assets gl.gl_balances.project_to_date_dr%TYPE;
v_disable BOOLEAN := FALSE;
v_current_period apps.fnd_flex_values_vl.description%TYPE;
v_cnt integer;
CURSOR c1 is
SELECT PRJ.PROJECT_ID,
PRJ.NEXT_PROJECT_ID,
PRJ.DOCUMENT_NO,
PRJ.AWARD_ID,
PRJ.AWARD_END_DATE,
PRJ.PROJECT_END_DATE,
PRJ.ACCNT_REP,
PRJ.ACCNT_RLTNSHP_TYPE,
BLL.BILLING_ID,
BLL.PAYMENT_METHOD,
SPN.AGENCY_ABBRV,
GCC.SEGMENT3,
GCC.CODE_COMBINATION_ID,
GBL.ACTUAL_FLAG,
GBL.PROJECT_TO_DATE_CR - GBL.PROJECT_TO_DATE_DR PJTD_DIFF,
GBL.PERIOD_NET_CR - GBL.PERIOD_NET_DR PRTD_DIFF
FROM pittrpa.BILLING BLL,
pittrpa.PROJECT PRJ,
pittrpa.SPONSOR SPN,
GL.GL_CODE_COMBINATIONS GCC,
GL.GL_BALANCES GBL
WHERE BLL.BILLING_ID = PRJ.BILLING_ID
AND PRJ.PROJECT_STATUS_CODE = 'A'
AND PRJ.AWARD_END_DATE <=
(LAST_DAY (TO_DATE (:P_PERIOD, 'MON-RR') - 90))
AND BLL.AGENCY_ID = SPN.AGENCY_ID
AND ( ( GCC.SEGMENT1 = '04'
AND GCC.SEGMENT4 = PRJ.PROJECT_ID
AND BLL.PAYMENT_METHOD = '70')
OR ( GCC.SEGMENT1 = '05'
AND GCC.SEGMENT5 = PRJ.PROJECT_ID
AND BLL.PAYMENT_METHOD != '70'))
AND GBL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GBL.PERIOD_NAME = :P_PERIOD
ORDER BY PRJ.ACCNT_REP,
BLL.PAYMENT_METHOD,
BLL.BILLING_ID,
PRJ.PROJECT_ID;
BEGIN -- Begin Main Logic
SELECT ffv.description
INTO v_current_period
FROM apps.fnd_flex_values_vl ffv, apps.fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = 'PITT_PROCESSING_PERIODS'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value = 'CURRENT';
DBMS_OUTPUT.PUT_LINE ('Current period ' || v_current_period);
FOR j IN c1
LOOP
IF j.actual_flag = 'B'
AND (j.segment3 BETWEEN '5000' AND '9999')
THEN
v_bdgt := v_bdgt + j.PJTD_DIFF + j.PRTD_DIFF;
END IF;
IF j.actual_flag = 'A'
AND (j.segment3 BETWEEN '5000' AND '9999')
THEN
v_exp := v_exp + j.PJTD_DIFF + j.PRTD_DIFF;
END IF;
IF j.actual_flag = 'A'
AND (j.segment3 BETWEEN '4000' AND '4999')
THEN
v_rev := v_rev + j.PJTD_DIFF + j.PRTD_DIFF;
END IF;
IF j.actual_flag = 'A'
AND (j.segment3 = '1200' OR j.segment3 = '1202')
THEN
v_rec := v_rec + j.PJTD_DIFF + j.PRTD_DIFF;
END IF;
IF j.actual_flag = 'A' AND (j.segment3 = '3010')
THEN
v_curr_net_assets :=
v_curr_net_assets + j.PJTD_DIFF + j.PRTD_DIFF;
END IF;
IF (v_bdgt - v_exp = 0)
AND (v_rev + v_exp = 0)
AND (v_rec = 0)
AND (v_curr_net_assets = 0)
THEN
v_disable := TRUE;
ELSE
v_disable := FALSE;
END IF;
if j.accnt_rltnshp_type IN ( 'I' ,'S') THEN
v_disable := TRUE;
ELSE
v_disable := FALSE;
END IF;
IF j.accnt_rltnshp_type = 'M'
then
SELECT count(PROJECT_ID) into v_cnt FROM PITTRPA.PROJECT p WHERE p.BILLING_ID = j.billing_id and p.accnt_rltnshp_type = 'S' and
p.project_status_code <> 'I';
if v_cnt >= 1 then
v_disable := FALSE;
end if;
END IF;
IF (v_disable)
THEN
INSERT INTO pitt.rpar310_disable_accounts (CODE_COMBINATION_ID,
NEXT_PROJECT_ID,
PROJECT_ID,
DOCUMENT_NO,
AWARD_ID,
AWARD_END_DATE,
PROJECT_END_DATE,
ACCNT_REP,
BILLING_ID,
PAYMENT_METHOD,
AGENCY_ABBRV,
SEGMENT3,
ACTUAL_FLAG,
PJTD_DIFF,
PRTD_DIFF)
VALUES ( j.CODE_COMBINATION_ID,
j.NEXT_PROJECT_ID,
j.PROJECT_ID,
j.DOCUMENT_NO,
j.AWARD_ID,
j.AWARD_END_DATE,
j.PROJECT_END_DATE,
j.ACCNT_REP,
j.BILLING_ID,
j.PAYMENT_METHOD,
j.AGENCY_ABBRV,
j.SEGMENT3,
j.ACTUAL_FLAG,
j.PJTD_DIFF,
j.PRTD_DIFF );
END IF;
END LOOP;
COMMIT;
END;
But, this is where I am stuck.
To calculate budget I use
IF j.actual_flag = 'B'
AND (j.segment3 BETWEEN '5000' AND '9999')
THEN
v_bdgt := v_bdgt + j.PJTD_DIFF + j.PRTD_DIFF;
END IF;
Now, the cursor I have is for each row. How can I tell the code to add up the budget as long as the actual_flag = 'B' and segment3 between 5000 and 9999 for a given
project_id. How can I accomplish this?
Thanks much,
Megha
-
Attachment: query.jpg
(Size: 698.77KB, Downloaded 1204 times)
|
|
|
Re: Cursor within a cursor [message #665281 is a reply to message #665278] |
Mon, 28 August 2017 13:30 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Something like this?
declare
...
begin
for cur_p in (select distinct project_id from project where ...)
loop
for cur_d in (your C1 cursor SELECT statement here
... and prj.project_id = cur_p.project_id
)
loop
the rest of your code here
end loop;
end loop;
end;
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:56:08 CDT 2024
|