troubleshooting Question

Custom Procedure

Avatar of lavam1986
lavam1986 asked on
Oracle Database
2 Comments1 Solution784 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros