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:

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:

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

   errcode        OUT      NUMBER,
   errbuf         OUT      VARCHAR2,
   start_period   IN       VARCHAR2,
   end_period     IN       VARCHAR2
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;

   CURSOR gl_date_cur (
      x_start_period   VARCHAR2,
      x_end_period     VARCHAR2
         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,
                                       AND TO_DATE (x_end_period,
      ORDER BY period;

   CURSOR total_inv_cur (x_gl_date VARCHAR2,x_trx_number VARCHAR2 )
      (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);
    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;
   delete  xx.xx_nfs_cls_inv;
     --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)
      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)
          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;
           total_inv_rec.age between 91 and 120 then
           x_age_120:=x_age_120 + 1;
           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
      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
        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>'
       apps.fnd_file.put_line (apps.fnd_file.output,
                                || print_report_rec.current_period
                                || '</CLOSED_CURRENT_PERIOD>'
       apps.fnd_file.put_line (apps.fnd_file.output,
                                || print_report_rec.total
                                || '</TOTAL_INV_CREATED>'
      apps.fnd_file.put_line (apps.fnd_file.output,
                                || print_report_rec.days30
                                || '</DAY30S>'
      apps.fnd_file.put_line (apps.fnd_file.output,
                                || print_report_rec.days60
                                || '</DAY60S>'
      apps.fnd_file.put_line (apps.fnd_file.output,
                                || '</DAY90S>'
       apps.fnd_file.put_line (apps.fnd_file.output,
                                || '</DAY120S>'
       apps.fnd_file.put_line (apps.fnd_file.output,
                                || print_report_rec.more121
                                || '</DAY121S>'
      apps.fnd_file.put_line (apps.fnd_file.output,'</LINE>');
 apps.fnd_file.put_line (apps.fnd_file.output,'</HEADER>');
     apps.fnd_file.put_line (apps.fnd_file.log,'MAIN Exception : '||SQLERRM);

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