Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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