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
Grants were given on synonyms but on base tables