Avatar of lavam1986
lavam1986
 asked on

Custom Procedure

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
Oracle Database

Avatar of undefined
Last Comment
lavam1986

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Gerwin Jansen

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
lavam1986

ASKER
Thanks

Grants were given on synonyms but on base tables
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes