Hi
I am trying to work on the lift n shift of custom proc from 11i to r12
i am getting strange errors
below is procedure
CREATE OR REPLACE PROCEDURE xx.xx_NSF_CLOSED_INV (
errcode OUT NUMBER,
errbuf OUT VARCHAR2,
start_period IN VARCHAR2,
end_period IN VARCHAR2
)
AS
x_inv_age NUMBER;
x_age_30 NUMBER;
x_age_60 NUMBER;
x_age_90 NUMBER;
x_age_120 NUMBER;
x_age_121 NUMBER;
x_total_invoices NUMBER;
x_period VARCHAR2(25);
lx_period VARCHAR2(25);
curr_period NUMBER;
x_total_inv_created NUMBER :=0;
x_org_id NUMBER :=APPS.FND_PROFILE.VALUE('ORG_ID');
CURSOR gl_date_cur (
x_start_period VARCHAR2,
x_end_period VARCHAR2
)
IS
SELECT trx.trx_number, to_char(trx.trx_date,'MON-YYYY') PERIOD
FROM apps.ra_customer_trx_all trx,
apps.ra_cust_trx_types_all typ
where trx.cust_trx_type_id=typ.cust_trx_type_id
and trx.trx_date BETWEEN TO_DATE(x_start_period,'DD-MON-YYYY') AND TO_DATE(x_end_period,'DD-MON-YYYY')
and typ.description like '%NSF%'
and trx.org_id=x_org_id;
/* (SELECT DISTINCT TO_char (pay.gl_date_closed, 'MON-YYYY') period
-- TO_char (pay.gl_date, 'MON-YYYY') period
FROM apps.ar_payment_schedules_all pay,
apps.ra_cust_trx_types_all typ
WHERE pay.cust_trx_type_id = typ.cust_trx_type_id
AND pay.CLASS = 'INV'
AND typ.description LIKE '%NSF%'
AND pay.status = 'CL'
and pay.org_id=x_org_id
AND trunc(pay.gl_date_closed) BETWEEN TO_DATE (x_start_period,
'DD-MON-YYYY'
)
AND TO_DATE (x_end_period,
'DD-MON-YYYY'
))
ORDER BY period;
*/
CURSOR total_inv_cur (x_gl_date VARCHAR2,x_trx_number VARCHAR2 )
IS
(SELECT distinct trx_number,(gl_date_closed - trx_date) age,TRX_DATE,gl_date_closed --total_invoices
FROM apps.ar_payment_schedules_all pay,
apps.ra_cust_trx_types_all typ
WHERE pay.cust_trx_type_id = typ.cust_trx_type_id
AND pay.CLASS = 'INV'
AND typ.description LIKE '%NSF%'
AND pay.status ='CL'
AND pay.org_id=x_org_id
AND pay.trx_number=x_trx_number
AND TO_char (pay.gl_date, 'MON-YYYY') =x_gl_date);
-- AND TO_CHAR(PAY.GL_DATE,'MON-YYYY') =TO_CHAR(TO_DATE(x_gl_date,'DD-MM-YYYY'),'MON-YYYY'));
cursor print_report_cur IS
SELECT to_date(period,'mm/yyyy')x_period
, SUM(total)TOTAL,sum(current_period)current_period,sum(days30)days30,sum(days60)days60,sum(days90)days90,sum(DAYS120)days120,sum(more121)more121
FROM xx.xx_nfs_cls_inv
group by period
order by x_period asc;
/*
SELECT to_date(period,'mm/yyyy')x_period
, total,current_period,days30,days60,days90,DAYS120,more121
FROM xx.xx_nfs_cls_inv
order by x_period asc;
*/
BEGIN
delete xx.xx_nfs_cls_inv;
commit;
--apps.fnd_file.put_line (apps.fnd_file.output,'X GL DATTE :'||x_gl_date);
apps.fnd_file.put_line (apps.fnd_file.output, '<?xml version="1.0" encoding="ISO-8859-1"?>' );
apps.fnd_file.put_line (apps.fnd_file.output, '<HEADER>');
apps.fnd_file.put_line (apps.fnd_file.output, '<START_PERIOD>'||start_period||'</START_PERIOD>');
apps.fnd_file.put_line (apps.fnd_file.output, '<END_PERIOD>'||end_period||'</END_PERIOD>');
FOR gl_date_rec IN gl_date_cur (start_period, end_period)
LOOP
x_total_invoices := 0;
x_inv_age := 0;
x_age_30 := 0;
x_age_60 := 0;
x_age_90 := 0;
x_age_120 := 0;
x_age_121 := 0;
curr_period :=0;
--x_period := NULL;
x_total_inv_created := 1;
/*
SELECT count(trx_number)
INTO x_total_inv_created
--distinct trx_number,(program_update_date - trx_date) age,TRX_DATE,program_update_date --total_invoices
FROM apps.ar_payment_schedules_all pay,
apps.ra_cust_trx_types_all typ
WHERE pay.cust_trx_type_id = typ.cust_trx_type_id
AND pay.CLASS = 'INV'
AND typ.description LIKE '%NSF%'
AND pay.org_id=x_org_id
AND TO_char (pay.trx_date, 'MON-YYYY') =gl_date_rec.period;
*/
-- apps.fnd_file.put_line (apps.fnd_file.output,'<LINE>');
-- x_period:=To_char(to_date(gl_date_rec.period,'DD-MM-YYYY'),'MON-YYYY');
FOR total_inv_rec IN total_inv_cur (gl_date_rec.period,gl_date_rec.trx_number)
LOOP
x_total_invoices:=x_total_invoices +1;
IF total_inv_rec.age BETWEEN 0 AND 30 THEN
x_age_30:=x_age_30 + 1;
elsif total_inv_rec.age between 31 and 60 then
x_age_60:=x_age_60 + 1;
elsif total_inv_rec.age between 61 and 90 then
x_age_90:=x_age_90 + 1;
elsif
total_inv_rec.age between 91 and 120 then
x_age_120:=x_age_120 + 1;
else
x_age_121:=x_age_121 + 1;
END IF;
IF TO_CHAR(total_inv_rec.TRX_DATE,'MM/YYYY')=TO_CHAR(total_inv_rec.gl_date_closed,'MM/YYYY') THEN
curr_period:= curr_period + 1;
END IF;
apps.fnd_file.put_line (apps.fnd_file.log,'Trx_date :'||total_inv_rec.TRX_DATE||' '||
'GL Closed Date :' || total_inv_rec.gl_date_closed ||' '||'TRX NUMBER : '||gl_date_rec.trx_number ||' '||
'INVOICE AGe : '||total_inv_rec.age
);
END LOOP; --TOTAL INVOICE END LOOP
--TABLE xx_nfs_cls_inv(period VARCHAR2(25),total NUMBER,days30 NUMBER,days60 NUMBER,days90 NUMBER,more90 NUMBER)
INSERT INTO xx.xx_nfs_cls_inv
VALUES(gl_date_rec.period,x_total_inv_created,x_total_invoices,x_age_30,x_age_60,x_age_90,x_age_120,x_age_121);
COMMIT;
/*
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'Period : ' || gl_date_rec.period
);
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'Total Closed : '
|| x_total_invoices
);
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'Total Created : '
|| curr_period
);
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'1-30Days : ' || x_age_30
);
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'31-60 Days : '
|| x_age_60
);
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'61-90 Days : ' || x_age_90
);
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'91-120 Days : '
|| x_age_120
);
apps.fnd_file.put_line (apps.fnd_file.log,' '
||'121 + Days : '
|| x_age_121
);
*/
END LOOP; --DATE END LOOP
apps.fnd_file.put_line (apps.fnd_file.log,'Total Invoices Creatd:'||x_total_inv_created);
FOR print_report_rec IN print_report_cur
LOOP
lx_period := NULL;
lx_period:=to_char(print_report_rec.x_period ,'MON-YYYY');
apps.fnd_file.put_line (apps.fnd_file.output,'<LINE>');
apps.fnd_file.put_line (apps.fnd_file.output,
'<PERIOD>'
||lx_period
|| '</PERIOD>'
);
apps.fnd_file.put_line (apps.fnd_file.output,
'<CLOSED_CURRENT_PERIOD>'
|| print_report_rec.current_period
|| '</CLOSED_CURRENT_PERIOD>'
);
apps.fnd_file.put_line (apps.fnd_file.output,
'<TOTAL_INV_CREATED>'
|| print_report_rec.total
|| '</TOTAL_INV_CREATED>'
);
apps.fnd_file.put_line (apps.fnd_file.output,
'<DAY30S>'
|| print_report_rec.days30
|| '</DAY30S>'
);
apps.fnd_file.put_line (apps.fnd_file.output,
'<DAY60S>'
|| print_report_rec.days60
|| '</DAY60S>'
);
apps.fnd_file.put_line (apps.fnd_file.output,
'<DAY90S>'
||print_report_rec.days90
|| '</DAY90S>'
);
apps.fnd_file.put_line (apps.fnd_file.output,
'<DAY120S>'
||print_report_rec.days120
|| '</DAY120S>'
);
apps.fnd_file.put_line (apps.fnd_file.output,
'<DAY121S>'
|| print_report_rec.more121
|| '</DAY121S>'
);
apps.fnd_file.put_line (apps.fnd_file.output,'</LINE>');
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.output,'</HEADER>');
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.log,'MAIN Exception : '||SQLERRM);
END;
/
any me errors are
line position error
36 14 PL/SQL: ORA-00942: table or view does not exist
34 10 PL/SQL: SQL Statement ignored
64 20 PL/SQL: ORA-00942: table or view does not exist
62 7 PL/SQL: SQL Statement ignored
124 43 PLS-00364: loop index variable 'GL_DATE_REC' use is invalid
124 7 PL/SQL: Statement ignored
156 13 PLS-00364: loop index variable 'GL_DATE_REC' use is invalid
156 25 PL/SQL: ORA-00984: column not allowed here
155 6 PL/SQL: SQL Statement ignored
the table which is being said as not exist is oracle standard table
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.