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 Go to next message
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 16 times)
Re: Cursor within a cursor [message #665281 is a reply to message #665278] Mon, 28 August 2017 13:30 Go to previous message
Littlefoot
Messages: 21266
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;
Previous Topic: Compare and contrast TRUNCATE and DELETE for a table
Next Topic: Error Linking to C DLL from PL/SQL
Goto Forum:
  


Current Time: Thu Dec 14 12:47:42 CST 2017

Total time taken to generate the page: 0.01393 seconds