Search This Blog

Friday, September 9, 2011

Reading parameterized cursor example

declare
p_eod_signal_type varchar2(29) := 'FAGIO';
p_eod_date varchar2(29) := '2011-08-09';
v_rec_count number := 0;
 CURSOR gl_eod_txns_c(p_eod_date IN VARCHAR2, p_eod_signal_type IN VARCHAR2) IS
SELECT je_header_id,
           period,
           je_line_num,
           effective_date,
           bank_day,
           accounted_cr,
           accounted_dr,
           is_credit,
           verification_number,
           source_nr,
           clnr,
           account_nr,
           rst,
           aggr_flag,
           accounting_sequence,
           ledger_id, --added by p950nle for RESTORE
           je_category_key --added for defect 1037
    FROM   xxogl_f04_gl_eod_txns_v xfgetv
    WHERE  xfgetv.eod_date = p_eod_date
    AND xfgetv.ledger_id in decode(p_eod_signal_type, 'CAL', '2047', 'FAGIO', '2044');
   
begin
  for l2 in gl_eod_txns_c(p_eod_date, p_eod_signal_type)
  loop
     dbms_output.put_line(gl_eod_txns_c%Rowcount || ' ' || l2.ledger_id);
  end loop;
  dbms_output.put_line('Executed');
end;

No comments:

Post a Comment