please tell me can we edit spawned program. if so then please edit blow program.i want that below program show only greater 90 days outstanding.
want only one bucket that is >90.
[program]
CREATE OR REPLACE procedure ecel_ar_age_report_busc(errbuf out varchar2,
retcode out number,
p_busc in varchar2,
p_prod in varchar2,
p_as_on in date) is
sstr varchar2(2000);
tdays number(5);
tot_dr number(16, 2);
tot_cr number(16, 2);
tot_b1 number(16, 2);
tot_b2 number(16, 2);
tot_b3 number(16, 2);
tot_b4 number(16, 2);
tot_b5 number(16, 2);
tot_b6 number(16, 2);
tot_b7 number(16, 2);
tot_b8 number(16, 2);
tot_b9 number(16, 2);
gtot_dr number(16, 2) := 0;
gtot_cr number(16, 2) := 0;
gtot_b1 number(16, 2) := 0;
gtot_b2 number(16, 2) := 0;
gtot_b3 number(16, 2) := 0;
gtot_b4 number(16, 2) := 0;
gtot_b5 number(16, 2) := 0;
gtot_b6 number(16, 2) := 0;
gtot_b7 number(16, 2) := 0;
gtot_b8 number(16, 2) := 0;
gtot_b9 number(16, 2) := 0;
tclosing number(16, 2);
pagesize number(6) := 66;
lines number(6) := 66;
linesize number(3) := 275;
ctr number(4) := 0;
actr number(4) := 0;
page_no number(4) := 1;
tdlr varchar2(15) := 'ZZZZZZZZ';
tdlr_code varchar2(15);
tname varchar2(30);
tcity varchar2(20);
tregion varchar2(20);
tarea varchar2(20);
tdoc_no varchar2(30);
tdoc_date date;
tdoc_amt number(16,2);
tcr_dr varchar2(2);
tcl_dr number(16, 2) := 0;
t_trx_amt number(16, 2) := 0;
t_applied_amt number(16, 2) := 0;
t_dr_bal number(16, 2) := 0;
t_cr_bal number(16, 2) := 0;
cursor c1 is
select rac.customer_number customer_no,
substr(rac.customer_name, 1, 30) customer_name,
raa.city||'('||raa.state||')' city,
rac.attribute14 region,
rac.attribute15 area,
rac.status status
from ra_customers rac,
ra_addresses_all raa,
ra_site_uses_all ras
where ras.site_use_code = 'BILL_TO'
and ras.address_id = raa.address_id
and rac.customer_id = raa.customer_id
and nvl(rac.attribute15,'NO_AREA')=nvl(p_busc,'NO_AREA')
-- and instr(nvl(p_customer_no, rac.customer_number), rac.customer_number) > 0
order by rac.customer_number;
cursor dr_doc is
select * from
(select 1 drcr,
a.trx_date trx_date,
c.name || '/' || a.trx_number trx_number,
C.type TYPE,
a.customer_trx_id,
sum(nvl(a.exchange_rate, 1) * b.extended_amount) trx_amount
from ra_customer_trx_all a,
ra_customer_trx_lines_all b,
ra_cust_trx_types_all c,
RA_CUST_TRX_LINE_GL_DIST_ALL D,
ra_customers e,
ra_site_uses_all f
where e.customer_number = tdlr_code
and a.complete_flag = 'Y'
and a.bill_to_site_use_id = f.site_use_id
and f.org_id=84
and a.bill_to_customer_id = e.customer_id
and c.org_id = a.org_id
and a.cust_trx_type_id = c.cust_trx_type_id
and c.type in ('INV', 'DM')
and a.customer_trx_id = b.customer_trx_id
AND A.CUSTOMER_TRX_ID=D.CUSTOMER_TRX_ID
AND NVL(D.CUSTOMER_TRX_LINE_ID,0)=0
AND D.ACCOUNT_CLASS='REC'
and d.latest_rec_flag = 'Y'
and nvl(e.attribute15,'NO_AREA')=nvl(p_busc,'NO_AREA')
and ((a.attribute10=p_prod and p_prod is not null) or p_prod is null)
and TRUNC(d.GL_DATE)<=p_as_on
GROUP BY a.trx_date,c.name || '/' || a.trx_number,C.type,a.customer_trx_id
order by drcr, trx_date desc);
begin
for cr in c1
loop
tdlr_code := cr.customer_no;
if tdlr <> tdlr_code or tdlr = 'ZZZZZZZZ' then
tdlr := tdlr_code;
begin
SELECT SUM(A.DR_AMOUNT)-SUM(A.CR_AMOUNT) into tcl_dr
FROM (select a.customer_trx_id trx_id,
nvl((decode(type ,'CM', 0 , nvl(a.exchange_rate, 1) * extended_amount)), 0) dr_amount,
nvl((decode(type, 'CM', -1 * nvl(a.exchange_rate, 1) * extended_amount,0)), 0) cr_amount
from ra_customer_trx_all a,
ra_customer_trx_lines_all b,
ra_cust_trx_types_all c,
RA_CUST_TRX_LINE_GL_DIST_ALL D,
ra_customers e,
ra_site_uses_all f
where e.customer_number = tdlr_code
and a.complete_flag = 'Y'
and a.bill_to_site_use_id = f.site_use_id
and f.org_id=84
and a.bill_to_customer_id = e.customer_id
and c.org_id = a.org_id
and a.cust_trx_type_id = c.cust_trx_type_id
and a.customer_trx_id = b.customer_trx_id
AND A.CUSTOMER_TRX_ID=D.CUSTOMER_TRX_ID
AND NVL(D.CUSTOMER_TRX_LINE_ID,0)=0
AND D.ACCOUNT_CLASS='REC'
and d.latest_rec_flag = 'Y'
and nvl(e.attribute15,'NO_AREA')=nvl(p_busc,'NO_AREA')
and ((a.attribute10=p_prod and p_prod is not null) or p_prod is null)
and TRUNC(d.GL_DATE)<=p_as_on
-- group by a.customer_trx_id,
union all
select b.cash_receipt_id trx_id,
decode(sign(nvl(b.exchange_rate, 1) * b.amount),-1, (nvl(b.exchange_rate, 1) * b.amount),0) dr_amount,
decode(sign(nvl(b.exchange_rate, 1) * b.amount),1,(nvl(b.exchange_rate, 1) * b.amount),0) cr_amount
from ra_customers a,
ar_cash_receipts_all b,
ar_cash_receipt_history_all c
where a.customer_number = tdlr_code
and b.org_id=84
and a.customer_id = b.pay_from_customer
and b.cash_receipt_id = c.cash_receipt_id
and nvl(a.attribute15,'NO_AREA')=nvl(p_busc,'NO_AREA')
and ((b.attribute1=p_prod and p_prod is not null) or p_prod is null)
and TRUNC(C.GL_DATE)<=p_as_on
AND C.FIRST_POSTED_RECORD_FLAG='Y'
union all
select b.cash_receipt_id trx_id,
decode(sign(nvl(b.exchange_rate, 1) * b.amount),1, (nvl(b.exchange_rate, 1) * b.amount),0) dr_amount,
decode(sign(nvl(b.exchange_rate, 1) * b.amount),-1,(nvl(b.exchange_rate, 1) * b.amount),0) cr_amount
from ra_customers a,
ar_cash_receipts_all b,
ar_cash_receipt_history_all c
where a.customer_number = tdlr_code
and b.org_id=84
and a.customer_id = b.pay_from_customer
and b.cash_receipt_id = c.cash_receipt_id
and nvl(a.attribute15,'NO_AREA')=nvl(p_busc,'NO_AREA')
and ((b.attribute1=p_prod and p_prod is not null) or p_prod is null)
and TRUNC(C.GL_DATE)<=p_as_on
AND C.CURRENT_RECORD_FLAG='Y'
AND C.STATUS='REVERSED') A;
exception
when others then
tcl_dr := 0;
end;
tname := rpad(cr.customer_name, 30);
tcity := rpad(nvl(cr.city, ' '), 20);
tregion := rpad(NVL(cr.region,' '),20);
tarea :=rpad(nvl(cr.area,' '), 20);
tclosing := nvl(tcl_dr, 0);
tot_dr := 0;
tot_cr := 0;
tot_b1 := 0;
tot_b2 := 0;
tot_b3 := 0;
tot_b4 := 0;
tot_b5 := 0;
tot_b6 := 0;
tot_b7 := 0;
tot_b8 := 0;
tot_b9 := 0;
if tclosing > 0 then
for doc in dr_doc
loop
t_trx_amt := doc.trx_amount;
tdoc_date := doc.trx_date;
if t_trx_amt > tclosing then
tdoc_amt := tclosing;
else
tdoc_amt := t_trx_amt;
end if;
tclosing := tclosing - tdoc_amt;
tot_dr := tot_dr + tdoc_amt;
gtot_dr := gtot_dr + tdoc_amt;
if trunc(tdoc_date) >= p_as_on - 30 then
tot_b1 := tot_b1 + tdoc_amt;
gtot_b1 := gtot_b1 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 30 and
trunc(tdoc_date) >= p_as_on - 60 then
tot_b2 := tot_b2 + tdoc_amt;
gtot_b2 := gtot_b2 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 60 and
trunc(tdoc_date) >= p_as_on - 90 then
tot_b3 := tot_b3 + tdoc_amt;
gtot_b3 := gtot_b3 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 90 and
trunc(tdoc_date) >= p_as_on - 180 then
tot_b4 := tot_b4 + tdoc_amt;
gtot_b4 := gtot_b4 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 180 and
trunc(tdoc_date) >= p_as_on - 365 then
tot_b5 := tot_b5 + tdoc_amt;
gtot_b5 := gtot_b5 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 365 and
trunc(tdoc_date) >= p_as_on - 730 then
tot_b6 := tot_b6 + tdoc_amt;
gtot_b6 := gtot_b6 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 730 and
trunc(tdoc_date) >= p_as_on - 1095 then
tot_b7 := tot_b7 + tdoc_amt;
gtot_b7 := gtot_b7 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 1095 and
trunc(tdoc_date) >= p_as_on - 1460 then
tot_b8 := tot_b8 + tdoc_amt;
gtot_b8 := gtot_b8 + tdoc_amt;
end if;
if trunc(tdoc_date) < p_as_on - 1460 then
tot_b9 := tot_b9 + tdoc_amt;
gtot_b9 := gtot_b9 + tdoc_amt;
end if;
if tclosing = 0 then
exit;
end if;
end loop;
if tclosing > 0 then
tot_dr := tot_dr + tclosing;
tot_b1 := tot_b1 + tclosing;
gtot_b1 := gtot_b1 + tclosing;
gtot_dr := gtot_dr + tclosing;
end if;
else
tot_cr := -tclosing;
gtot_cr := gtot_cr + tot_cr;
end if;
if tot_dr <> 0 or tot_cr <> 0 then
if lines + 4 > pagesize then
if page_no > 1 then
sstr := chr(12);
fnd_file.put_line(fnd_file.output, sstr);
end if;
sstr := 'ACCOUNTS RECEIVABLES AGEING STATEMENT AS ON ';
sstr := sstr || to_char(p_as_on, 'DD/MM/YYYY')||' '||'FOR :'||p_busc||' '||'PRODUCT :'||p_prod;
sstr := rpad(sstr, linesize - 22) || 'Run Date : ' || to_char(sysdate, 'DD/MM/YYYY');
fnd_file.put_line(fnd_file.output, sstr);
-- fnd_file.put_line(fnd_file.output, sstr);
sstr := ' ';
fnd_file.put_line(fnd_file.output, sstr);
sstr := 'Page No. : ' || to_char(page_no,'999') || '.';
fnd_file.put_line(fnd_file.output, sstr);
sstr := lpad(' ', linesize, '-');
fnd_file.put_line(fnd_file.output, sstr);
sstr := 'S No. Dlr Code Dealer Name City Region Area ';
sstr := sstr || ' Debit <------------------------------------ Outstanding Due by ------------------------------------------------------------------> Credit ';
fnd_file.put_line(fnd_file.output, sstr);
sstr := ' ';
sstr := sstr || ' Balance 0-TO-30 31-TO-60 61 TO 90 91-TO-180 181-TO-365 1-2 Years 2-3 Years 3-4 Years >4 Years Balance';
fnd_file.put_line(fnd_file.output, sstr);
sstr := lpad(' ', linesize, '-');
fnd_file.put_line(fnd_file.output, sstr);
lines := 9;
page_no := page_no + 1;
end if;
actr := actr + 1;
ctr := ctr + 1;
sstr := lpad(ltrim(to_char(ctr, '9999')), 4) || '. ' || rpad(cr.customer_no,
;sstr := sstr || rpad(' ', 10) || ' ' || tname || ' ' || tcity||' '||tregion||' '||tarea;
sstr := sstr || to_char(tot_dr, '9999999999.99');
sstr := sstr || to_char(tot_b1, '9999999999.99');
sstr := sstr || to_char(tot_b2, '9999999999.99');
sstr := sstr || to_char(tot_b3, '9999999999.99');
sstr := sstr || to_char(tot_b4, '9999999999.99');
sstr := sstr || to_char(tot_b5, '9999999999.99');
sstr := sstr || to_char(tot_b6, '9999999999.99');
sstr := sstr || to_char(tot_b7, '9999999999.99');
sstr := sstr || to_char(tot_b8, '9999999999.99');
sstr := sstr || to_char(tot_b9, '9999999999.99');
sstr := sstr || to_char(tot_cr, '9999999999.99');
fnd_file.put_line(fnd_file.output, sstr);
lines := lines + 1;
end if;
end if;
end loop;
sstr := lpad(' ', linesize, '-');
fnd_file.put_line(fnd_file.output, sstr);
sstr := '*** GRAND TOTAL = ' || to_char(gtot_dr - gtot_cr, '9999999999.99') || ' DR ';
sstr := sstr || ' ';
sstr := sstr || to_char(gtot_dr, '9999999999.99');
sstr := sstr || to_char(gtot_b1, '9999999999.99');
sstr := sstr || to_char(gtot_b2, '9999999999.99');
sstr := sstr || to_char(gtot_b3, '9999999999.99');
sstr := sstr || to_char(gtot_b4, '9999999999.99');
sstr := sstr || to_char(gtot_b5, '9999999999.99');
sstr := sstr || to_char(gtot_b6, '9999999999.99');
sstr := sstr || to_char(gtot_b7, '9999999999.99');
sstr := sstr || to_char(gtot_b8, '9999999999.99');
sstr := sstr || to_char(gtot_b9, '9999999999.99');
sstr := sstr || to_char(gtot_cr, '9999999999.99');
fnd_file.put_line(fnd_file.output, sstr);
sstr := lpad(' ', linesize, '-');
fnd_file.put_line(fnd_file.output, sstr);
end;
/
[/program]