SQL & PL/SQL

declare
credit_amount number:=0;
debit_amount number:=0;
v1 number:=0;
v2 number:=0;
v3 varchar2(500);
v4 varchar2(500);
v5 varchar2(240);
v6 varchar2(240);
v7 number;
v8 varchar2(240);
v9 varchar2(240);
v10 number;
v11 number;
v12 varchar2(240);
v13 varchar2(240);
v14 varchar2(240);
v15 number;
v16 number;
--This cursor is for liability records which are in GL not in AP
cursor c1(p_header_id number,p_reference_2 varchar2,p_reference_4 varchar2)
is
(select
jl.je_header_id "JE_HEADER_ID"
,jl.period_name "PERIOD"
,glcc.concatenated_segments "ACCOUNT_CODE"
,DECODE(jl.accounted_dr,null,0,jl.accounted_dr)"ACCOUNTED_DEBIT"
,DECODE(jl.accounted_cr,null,0,jl.accounted_cr) "ACCOUNTED_CREDIT"
,DECODE(jl.accounted_dr,null,0,jl.accounted_dr) - DECODE(jl.accounted_cr,null,0,jl.accounted_cr) "NET"
,glcc.CODE_COMBINATION_ID "CODE_COMBINATION_ID"
,jl.SET_OF_BOOKS_ID "SET_OF_BOOKS_ID"
,jl.PERIOD_NAME "PERIOD_NAME"
,DECODE(Jl.entered_dr,null,0,Jl.entered_dr)"ENTERED_DEBIT"
,DECODE(Jl.entered_cr,null,0,Jl.entered_cr) "ENTERED_CREDIT"
,jl.reference_1 "SUPPLIER"
,jl.reference_2 "INVOICE_ID"
,jl.reference_3 "CHECK_ID"
,jl.reference_4 "CHECK_NUMBER"
,jl.reference_5 "INVOICE_NUM"
,jl.reference_6 "'AP_PAYMT_JUST_INSERTED'"
,jl.reference_7 "set_of_books_id"
,jl.GL_SL_LINK_ID "GL_SL_LINK_ID"
,jl.REFERENCE_8 "INVOICE_DIST_LINE_NUMBER"
,jl.reference_9 "INVOICE_PAYMENT_ID"
,jl.REFERENCE_10 "LIABILITY"
,jl.TAX_CODE_ID "TAX_CODE_ID"
,jl.TAX_GROUP_ID "TAX_GROUP_ID"
FROM
gl_je_lines jl
, apps.gl_code_combinations_KFV glcc
, gl_je_headers jh
WHERE
jl.period_name='Mar-10'
and glcc. code_combination_id in (1016,1296,1298)
and jh.je_header_id = jl.je_header_id
AND glcc.code_combination_id = jl.code_combination_id
and jh.je_source = 'Payables'
AND jl.je_header_id = p_header_id
and jl.reference_2 = p_reference_2
and jl.reference_4 = p_reference_4
MINUS
select
ir.je_header_id
, h.period_name "APPERIOD"
,g.CONCATENATED_SEGMENTS "AP ACCOUNT CODE"
,DECODE(l.accounted_dr,null,0,l.accounted_dr) "AP ACCOUNTED_DR"
,DECODE(l.accounted_cr,null,0,l.accounted_cr) "AP ACCOUNTED_CR"
,DECODE(l.accounted_dr,null,0,l.accounted_dr) - DECODE(l.accounted_cr,null,0,l.accounted_cr) "NET"
,l.CODE_COMBINATION_ID "AP_CCID"
,h.set_of_books_id
,h.PERIOD_NAME "PERIOD_NAME"
,DECODE(l.entered_dr,null,0,l.entered_dr)"ENTERED_DEBIT"
,DECODE(l.entered_cr,null,0,l.entered_cr) "ENTERED_CREDIT"
,l.reference1 "SUPPLIER"
,l.reference2 "INVOICE_Id"
,l.reference3 "reference_3"
,l.reference4 "reference_4"
,l.reference5 "INVOICE_NUM"
,l.reference6 "reference_6"
,l.reference7 "reference_7"
,l.GL_SL_LINK_ID "GL_SL_LINK_ID"
,l.REFERENCE8 "REFERENCE_8"
,l.reference9 "reference_9"
,l.REFERENCE10 "REFERENCE_10"
,l.TAX_CODE_ID "TAX_CODE_ID"
,l.TAX_LINK_ID "TAX_LINK_ID"
from
ap_ae_lines_all l,
ap_ae_headers_all h,
gl_code_combinations_kfv g
,gl_import_references ir
where
ir.gl_sl_link_id=l.gl_sl_link_id
AND g.CODE_COMBINATION_ID = l.CODE_COMBINATION_ID
and h.ae_header_id = l.ae_header_id
AND h.period_name ='Mar-10'
AND g.CODE_COMBINATION_ID in (1016,1296,1298)
AND ir.JE_HEADER_ID = p_header_id
and l.reference2 = p_reference_2
and l.reference4 = p_reference_4);
--This cursor is for writeoff records
cursor c2
is
(select * from gl_je_lines
where period_name='Mar-10'
and reference_10='WRITEOFF'
and reference_2 in ('525706','525600'));
credit number:=0;
debit number:=0;
j varchar2(240);

i varchar2(4000):='0';
cursor c3 (p_invoice_id varchar2)
is
(select
jl.je_header_id "JE_HEADER_ID"
,jl.period_name "PERIOD"
,glcc.concatenated_segments "ACCOUNT_CODE"
,DECODE(jl.accounted_dr,null,0,jl.accounted_dr)"ACCOUNTED_DEBIT"
,DECODE(jl.accounted_cr,null,0,jl.accounted_cr) "ACCOUNTED_CREDIT"
,DECODE(jl.accounted_dr,null,0,jl.accounted_dr) - DECODE(jl.accounted_cr,null,0,jl.accounted_cr) "NET"
,glcc.CODE_COMBINATION_ID "CODE_COMBINATION_ID"
,jl.SET_OF_BOOKS_ID "SET_OF_BOOKS_ID"
,jl.PERIOD_NAME "PERIOD_NAME"
,DECODE(Jl.entered_dr,null,0,Jl.entered_dr)"ENTERED_DEBIT"
,DECODE(Jl.entered_cr,null,0,Jl.entered_cr) "ENTERED_CREDIT"
,jl.reference_1 "SUPPLIER"
,jl.reference_2 "INVOICE_ID"
,jl.reference_3 "CHECK_ID"
,jl.reference_4 "CHECK_NUMBER"
,jl.reference_5 "INVOICE_NUM"
,jl.reference_6 "'AP_PAYMT_JUST_INSERTED'"
,jl.reference_7 "set_of_books_id"
,jl.GL_SL_LINK_ID "GL_SL_LINK_ID"
,jl.REFERENCE_8 "INVOICE_DIST_LINE_NUMBER"
,jl.reference_9 "INVOICE_PAYMENT_ID"
,jl.REFERENCE_10 "LIABILITY"
,jl.TAX_CODE_ID "TAX_CODE_ID"
,jl.TAX_GROUP_ID "TAX_GROUP_ID"
FROM
gl_je_lines jl
, apps.gl_code_combinations_KFV glcc
, gl_je_headers jh
WHERE
jl.period_name='Mar-10'
and glcc. code_combination_id in (1016,1296,1298)
and jh.je_header_id = jl.je_header_id
AND glcc.code_combination_id = jl.code_combination_id
and jh.je_source = 'Payables'
and jl.reference_2 in (p_invoice_id)
MINUS
select
ir.je_header_id
, h.period_name "AP PERIOD"
,g.CONCATENATED_SEGMENTS "AP ACCOUNT CODE"
,DECODE(l.accounted_dr,null,0,l.accounted_dr) "AP ACCOUNTED_DR"
,DECODE(l.accounted_cr,null,0,l.accounted_cr) "AP ACCOUNTED_CR"
,DECODE(l.accounted_dr,null,0,l.accounted_dr) - DECODE(l.accounted_cr,null,0,l.accounted_cr) "NET"
,l.CODE_COMBINATION_ID "AP_CCID"
,h.set_of_books_id
,h.PERIOD_NAME "PERIOD_NAME"
,DECODE(l.entered_dr,null,0,l.entered_dr)"ENTERED_DEBIT"
,DECODE(l.entered_cr,null,0,l.entered_cr) "ENTERED_CREDIT"
,l.reference1 "SUPPLIER"
,l.reference2 "INVOICE_Id"
,l.reference3 "reference_3"
,l.reference4 "reference_4"
,l.reference5 "INVOICE_NUM"
,l.reference6 "reference_6"
,l.reference7 "reference_7"
,l.GL_SL_LINK_ID "GL_SL_LINK_ID"
,l.REFERENCE8 "REFERENCE_8"
,l.reference9 "reference_9"
,l.REFERENCE10 "REFERENCE_10"
,l.TAX_CODE_ID "TAX_CODE_ID"
,l.TAX_LINK_ID "TAX_LINK_ID"
from
ap_ae_lines_all l,
ap_ae_headers_all h,
gl_code_combinations_kfv g
,gl_import_references ir
where
ir.gl_sl_link_id=l.gl_sl_link_id
AND g.CODE_COMBINATION_ID = l.CODE_COMBINATION_ID
and h.ae_header_id = l.ae_header_id
AND h.period_name ='Mar-10'
AND g.CODE_COMBINATION_ID in (1016,1296,1298)
and l.reference2 in (p_invoice_id)); --here if i put l.reference2 in (p_invoice_id)) it must show the details of
-- of all.but it doesnot display sir
--here if i put l.reference2 not in (p_invoice_id)) it shows that id also sir

BEGIN
for writeoff_rec in c2
LOOP


FOR Main_cur in c1(writeoff_rec.je_header_id,writeoff_rec.reference_2,writeoff_rec.reference_4)
LOOP
j:='0';
IF writeoff_rec.accounted_dr is not null AND Main_cur.ACCOUNTED_CREDIT<>0
THEN
v10:=Main_cur.ACCOUNTED_CREDIT;
credit_amount:= credit_amount+Main_cur.ACCOUNTED_CREDIT;
ELSIF writeoff_rec.accounted_cr is not null AND Main_cur.ACCOUNTED_DEBIT<>0
THEN
v11:=Main_cur.ACCOUNTED_DEBIT;
debit_amount:= debit_amount+Main_cur.ACCOUNTED_DEBIT;
END IF;
if c1%found then
j:=Main_cur.INVOICE_ID;
end if;
END LOOP;
-- i:=i||','||j;

i:= i||','''||j||'''';
END LOOP;

dbms_output.put_line(i); --Here i got all invoiceids of varchar2 records without single qutations
--its look like '0','23232','2324234' etc.
for cash_clearing_cur in c3(i)--Here is the problem ..is it correct way here i am passing parameter to cursor
loop
v3:=0;
v4:=0;
v5:=0;
v6:=0;
v7:=0;
v8:=0;
v9:=0;
v10:=0;
v11:=0;
v12:=0;
v13:=0;
v14:=0;
v15:=0;
v16:=0;
credit:=credit+cash_clearing_cur.ACCOUNTED_CREDIT;
debit:=debit+cash_clearing_cur.ACCOUNTED_DEBIT;
v3:=cash_clearing_cur.JE_HEADER_ID;
v4:=cash_clearing_cur.INVOICE_ID;
v5:=cash_clearing_cur.CHECK_NUMBER;
v6:=cash_clearing_cur.LIABILITY;
v7:=cash_clearing_cur.CODE_COMBINATION_ID;
v8:=cash_clearing_cur.PERIOD;
v9:=cash_clearing_cur.ACCOUNT_CODE;
v10:=cash_clearing_cur.ACCOUNTED_CREDIT;
v11:=cash_clearing_cur.ACCOUNTED_DEBIT;
v12:=cash_clearing_cur.SUPPLIER;
v13:=cash_clearing_cur.CHECK_ID;
v14:=cash_clearing_cur.INVOICE_NUM;
v15:=cash_clearing_cur.GL_SL_LINK_ID;
v16:=cash_clearing_cur.SET_OF_BOOKS_ID;
DBMS_OUTPUT.PUT_LINE('HEAd '||v3||','||'inv_id '||v4||','||
'chk_num '||v5||','||'ref10 '||v6||','||'CCID '||V7||','||'PED '||V8
||','||'acctcode '||v9||','||'acct_Ct '||V10
||','||'acct_Dt '||v11||','||'chk_id '||v13||','||'inv_num '||V14
||','||'link '||v15||','||'sob '||v16||','||'suplir '||V12);
end loop;
DBMS_OUTPUT.PUT_LINE( 'Dr Amt ' ||debit || 'Cr amt ' || credit );
EXCEPTION
when too_many_rows then
dbms_output.put_line('Invalid no of rows');
when no_data_found then
dbms_output.put_line('no data found exception');
when others then
dbms_output.put_line('Other than Invalid no of rows');
dbms_output.put_line(SQLERRM);
END;
/
ouutput was
0,0 that means cursor c3 is not compiled because i am passing multiple values in parametr .is there any solution