6 Replies Latest reply: Jan 21, 2013 1:18 AM by 986044 RSS

    error ORA-01008: Not all variables bound

    986044
      SELECT 
        
      MAX((SELECT TO_CHAR(MAX(a.order_number)) FROM oe_order_headers_All a WHERE a.cust_po_number=ct.purchase_order)) AS order_number1,   
           MAX(DECODE(CT.PREVIOUS_CUSTOMER_TRX_ID,NULL,ct.interface_header_attribute1,(SELECT ct1.interface_header_attribute1
                                                                     FROM  ra_customer_trx_all CT1
                                         WHERE CT1.CUSTOMER_TRX_ID = CT.PREVIOUS_CUSTOMER_TRX_ID))) AS ORDER_NUMBER,
                                         
            
            
         
                                       
      0 AS order_value,
              
      
       
       
      NVL(ct.purchase_order,SU.LOCATION) purchase_order,
          
      
          (SELECT SUM(L.ORDERED_QUANTITY*L.UNIT_SELLING_PRICE) 
          FROM oe_order_lines_all l,
             oe_order_headers_all h
           
        WHERE l.header_id = h.header_id
        AND h.order_number IN (SELECT MAX(a.order_number) FROM oe_order_headers_All a WHERE a.cust_po_number=ct.purchase_order)) AS order_value1,                           
                                         
          
         
          
      null AS CONTRACT_START_DATE,
      
      
      
      
      
      (SELECT MIN(TRUNC(TO_DATE(l.Attribute14), 'DD'))
          FROM oe_order_lines_all l,
             oe_order_headers_all h
           
        WHERE l.header_id = h.header_id
        AND h.order_number IN (SELECT MAX(a.order_number) FROM oe_order_headers_All a WHERE a.cust_po_number=ct.purchase_order)) AS CONTRACT_START_DATE1,
         
        
      null AS CONTRACT_END_DATE,
            
      
        
         (SELECT MAX(TRUNC(TO_DATE(l.Attribute14), 'DD'))
          FROM oe_order_lines_all l,
             oe_order_headers_all h
           
        WHERE l.header_id = h.header_id
        AND h.order_number IN (SELECT MAX(a.order_number) FROM oe_order_headers_All a WHERE a.cust_po_number=ct.purchase_order)) AS CONTRACT_END_DATE1,                             
                                         
            
         
                 
      
      
                                                                                         
                                                                                         
              CUST_ACCT.ACCOUNT_NUMBER, 
                SUBSTRB(party.party_name,1,50) Cust_name, 
                SU.LOCATION AS MID, 
                 PS.TRX_NUMBER AS INVOICE_NUM, 
                PS.TRX_DATE AS INVOICE_DATE, 
                PS.AMOUNT_DUE_ORIGINAL AS INVOICE_VALUE,
                ctt.description,
                CT.PREVIOUS_CUSTOMER_TRX_ID,
                CT.CUSTOMER_TRX_ID,
      
                
      
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING,
                                 
         
         PS.CUSTOMER_TRX_ID AS PS_CUSTOMER_TRX_ID,
         PS.CUST_TRX_TYPE_ID,
         PS.CUSTOMER_ID,
         PS.CUSTOMER_SITE_USE_ID,
         
         AR1.RECEIPT_NUMBER,
         AR1.AMOUNT_APPLIED,
         AR1.APPLY_DATE,
         ct.interface_header_attribute1,
          CASE 
      WHEN (( ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) >=1 )  AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 30 )) 
      THEN '  1 - 30  Days Due' 
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 31) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 60) ) 
      THEN ' 31 - 60  Days Due' 
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 61) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 90 )) 
      THEN ' 61 - 90  Days Due'
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 91) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 180 )) 
      THEN ' 91 - 180 Days Due'
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 181) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 360 )) 
      THEN '181 - 360 Days Due'
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 361) ) 
      THEN '361+ Days Due' 
      ELSE 'Current' 
      END AS aging,
      NULL cash_receipt_id,
      AR1.GL_POSTED_DATE,
      
      
      
      CASE 
      WHEN (( ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) >=1 )  AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 30 )) 
      THEN 
      NULL
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 31) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 60) ) 
      THEN 
      NULL
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 61) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 90 )) 
      THEN 
      NULL
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 91) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 180 )) 
      THEN 
      NULL
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 181) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 360 )) 
      THEN
      NULL
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 361) ) 
      THEN
      NULL
      ELSE
      
      
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING
      
      END AS Current_AGE,
      
      
      
      
      CASE 
      WHEN (( ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) >=1 )  AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 30 )) 
      THEN
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING
      
      
      END AS S30_Days,
      
      CASE 
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 31) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 60) ) 
      THEN 
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING
      
      END AS S31_60_Days,
      
      CASE 
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 61) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 90 )) 
      THEN
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING
      
      END AS S61_90_Days,
      
      CASE
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 91) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 180 )) 
      THEN
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING
      
      END AS S91_180_Days,
      
      CASE
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 181) AND  (ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0) <= 360 )) 
      THEN
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING
      
      END AS S181_360_Days,
      
      
      CASE
      WHEN ((ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 361) ) 
      THEN
      
      ((SELECT NVL( SUM ( DECODE
                         ( 'Y', 'Y',
                            (DECODE(ps9.CLASS, 'CM',
                                         DECODE ( ra9.application_type, 'CM',
                                                   ra9.acctd_amount_applied_from,
                                                   ra9.acctd_amount_applied_to),
                                         ra9.acctd_amount_applied_to) +
                              NVL(ra9.acctd_earned_discount_taken,0) +
                             NVL(ra9.acctd_unearned_discount_taken,0) ),
                           ( ra9.amount_applied +
                             NVL(ra9.earned_discount_taken,0) +
                             NVL(ra9.unearned_discount_taken,0) )
                         ) *
                         DECODE
                         ( ps9.CLASS, 'CM',
                           DECODE(ra9.application_type, 'CM', -1, 1), 1 ) 
                      ), 0 )
        
          FROM  ar_receivable_applications ra9,
                  ar_cash_receipt_history crh9, 
                 ar_payment_schedules ps9
          WHERE (ra9.applied_payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
                     OR
                     ra9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID)
         AND   ra9.status||'' = 'APP'
              AND   ra9.cash_receipt_history_id = crh9.cash_receipt_history_id(+)
              AND   (crh9.reversal_gl_date IS NULL
                     OR crh9.reversal_gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY'))
          AND   NVL( ra9.confirmed_flag, 'Y' ) = 'Y'
          AND   ra9.gl_date+0 > TO_DATE(:SYSDATE1,'DD-MON-YY')
          AND   ps9.payment_schedule_id = PS.PAYMENT_SCHEDULE_ID
        
        
         )) + PS.AMOUNT_DUE_REMAINING
      
      
      END AS S361_plus_Days,
      PS.PAYMENT_SCHEDULE_ID
      
        
      
      
        
      
          
      FROM ar_lookups                 al_status, 
         ar_collectors              ar_coll, 
         ar_cons_inv_all            cons, 
         ra_cust_trx_types_all          ctt, 
         ra_batch_sources_all          bs, 
         ra_customer_trx_all          ct, 
         hz_cust_site_uses_all          su, 
         hz_cust_accounts            cust_acct, 
         hz_parties              party, 
         ar_payment_schedules_all        ps,
         AR_RECEIVABLE_APPLICATIONS_V      ar1
         
      
      WHERE 
      PS.CUSTOMER_ID                    = CUST_ACCT.CUST_ACCOUNT_ID        AND 
      CUST_ACCT.PARTY_ID              = PARTY.PARTY_ID            AND 
      PS.CUSTOMER_SITE_USE_ID          = SU.SITE_USE_ID            AND 
      PS.CUSTOMER_TRX_ID              = CT.CUSTOMER_TRX_ID          AND 
      CT.BATCH_SOURCE_ID              = BS.BATCH_SOURCE_ID          AND 
      CT.CUST_TRX_TYPE_ID            = CTT.CUST_TRX_TYPE_ID          AND 
      PS.STATUS                  = AL_STATUS.LOOKUP_CODE          AND 
      AL_STATUS.LOOKUP_TYPE            = 'INVOICE_TRX_STATUS'          AND 
      PS.COLLECTOR_LAST              = AR_COLL.COLLECTOR_ID (+)        AND 
      PS.CONS_INV_ID                = CONS.CONS_INV_ID (+)          AND 
      PS.STATUS                  = NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS) AND 
      su.site_use_code              = 'BILL_TO'                        AND 
      
      SU.location                NOT LIKE '0%'                       AND 
      SU.LOCATION NOT IN ('INTERCO') AND
      
      
      AR1.CUSTOMER_TRX_ID  (+)             =PS.CUSTOMER_TRX_ID                 AND 
      AR1.CUST_TRX_TYPE_ID(+)             =PS.CUST_TRX_TYPE_ID               AND 
      AR1.CUSTOMER_ID  (+)               =PS.CUSTOMER_ID                   AND 
      AR1.BILL_TO_SITE_USE_ID  (+)          =PS.CUSTOMER_SITE_USE_ID             AND 
      AR1.TRX_CLASS_CODE(+)           =PS.CLASS                     AND 
      AR1.TRX_NUMBER(+)               =PS.TRX_NUMBER                   AND
      SU.ORG_ID =144
      
      --AR1.GL_POSTED_DATE <= TO_DATE(:SYSDATE1,'DD-MON-YY') 
      --AND   ROUND((TO_DATE(:SYSDATE1,'DD-MON-YY') - ps.DUE_DATE),0)  >= 181
      --and PS.AMOUNT_DUE_REMAINING >0
      ----and SUBSTRB(party.party_name,1,50) like 'A%' 
      --AND CUST_ACCT.ACCOUNT_NUMBER IN ( 6319)
      &LP_CUST_NO 
      &LP_CUST_NAME
      
      AND PS.TRX_DATE < = TO_DATE(:SYSDATE1,'DD-MON-YY')
      
      GROUP BY CUST_ACCT.ACCOUNT_NUMBER, 
          SUBSTRB(party.party_name,1,50), 
          SU.LOCATION , 
           PS.TRX_NUMBER, 
          PS.TRX_DATE, 
          PS.AMOUNT_DUE_ORIGINAL,
          ctt.description,
          CT.PREVIOUS_CUSTOMER_TRX_ID,
          CT.CUSTOMER_TRX_ID,
          PS.AMOUNT_DUE_REMAINING,
          PS.CUSTOMER_TRX_ID,
             PS.CUST_TRX_TYPE_ID,
             PS.CUSTOMER_ID,
             PS.CUSTOMER_SITE_USE_ID,
            AR1.RECEIPT_NUMBER,
             AR1.AMOUNT_APPLIED,
             AR1.APPLY_DATE,
             ct.interface_header_attribute1,
             ct.purchase_order,
          AR1.GL_POSTED_DATE,
          ps.DUE_DATE,
          PS.PAYMENT_SCHEDULE_ID