AR iReceivables Customers Statement Data Definition

SELECT
 pmt_sch.trx_number,
 pmt_sch.trx_type,
 ari_utilities.get_lookup_meaning('INV/CM/ADJ', pmt_sch.trx_type) trx_type_meaning,
 pmt_sch.TERMS_SEQUENCE_NUMBER,
 pmt_sch.trx_date, 
 to_char(pmt_sch.trx_date, 'YYYY-MM-DD') StrTrxDate,
 pmt_sch.due_date,
 to_char(pmt_sch.due_date, 'YYYY-MM-DD') StrDueDate,
 pmt_sch.AR_LOOKUP_CODE_STATUS_MEANING, 
 pmt_sch.ct_purchase_order,
 pmt_sch.INVOICE_CURRENCY_CODE,
 pmt_sch.amount_due_original,
 to_char(pmt_sch.amount_due_original, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30))
       AS Amount_Due_Original_Formatted,
 pmt_sch.AMOUNT_DUE_REMAINING,
 to_char(pmt_sch.AMOUNT_DUE_REMAINING, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30))
       AS Amount_due_remaining_Formatted,
 pmt_sch.AMOUNT_IN_DISPUTE,
 to_char(pmt_sch.AMOUNT_IN_DISPUTE, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30))
       AS Amount_in_dispute_Formatted,
 NULL AS disc_amt,
 NULL AS Disc_amt_Formatted,
 NULL AS discount_last_date,
 /*
 pmt_sch.disc_amt,
 to_char(pmt_sch.disc_amt, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30))
       AS Disc_amt_Formatted,
 decode(pmt_sch.disc_amt,0,null,to_char(pmt_sch.discount_last_date, 'YYYY-MM-DD')) discount_last_date,
 */
 pmt_sch.customer_number,
 pmt_sch.customer_name,
 pmt_sch.bill_to_location,
 pmt_sch.amount_in_claim,
 to_char(pmt_sch.amount_in_claim, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30))
       AS Amount_in_Claim_Formatted,
 pmt_sch.ON_ACCOUNT_AMOUNT,
 to_char(pmt_sch.ON_ACCOUNT_AMOUNT, fnd_currency_cache.get_format_mask(INVOICE_CURRENCY_CODE, 30))
       AS Amount_on_Account_Formatted,
customerContact,
sales_orders
 FROM 
 (
  SELECT
       ct.PURCHASE_ORDER AS CT_PURCHASE_ORDER,
       ArPaymentSchedulesV.TRX_NUMBER trx_number,                                                                             
       ArPaymentSchedulesV.TRX_DATE trx_date,
       ArPaymentSchedulesV.AMOUNT_DUE_ORIGINAL amount_due_original,
       ArPaymentSchedulesV.CUSTOMER_ID,
       ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID,  
       ArPaymentSchedulesV.class as trx_type,
       ArPaymentSchedulesV.TERMS_SEQUENCE_NUMBER,
       /*
	   DECODE(ArPaymentSchedulesV.CLASS,
             'PMT',
             ( select -sum(app.amount_applied)
               from AR_PAYMENT_SCHEDULES ps, ar_receivable_applications app
               where ps.customer_id = ArPaymentSchedulesV.customer_id
               AND    decode( (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID),
                                NULL, nvl(ps.customer_site_use_id,-10), ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID)
                                  = nvl(ps.customer_site_use_id,-10)
               AND ps.invoice_currency_code = ArPaymentSchedulesV.INVOICE_CURRENCY_CODE
               AND app.cash_receipt_id = ps.cash_receipt_id
               AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
               AND app.status = 'UNAPP'
               AND app.cash_receipt_id = ArPaymentSchedulesV.CASH_RECEIPT_ID
             ),
             ArPaymentSchedulesV.AMOUNT_DUE_REMAINING
            ) AS AMOUNT_DUE_REMAINING, */
ArPaymentSchedulesV.AMOUNT_DUE_REMAINING AS AMOUNT_DUE_REMAINING,
(select case when to_char(:TemplateType)='ARICUSTMTCNTCT' then
(select  RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (sales_order|| ',' AS "Seg")
)
ORDER BY sales_order ASC
).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
','
)  from ra_customer_trx_lines ral where ral.customer_trx_id = ct.customer_trx_id
	     ) 
else
(select null from dual) 
end from dual)AS sales_orders,
(select case when to_char(:TemplateType)='ARICUSTMTCNTCT' then
(SELECT  party.person_first_name ||' '|| party.person_last_name customerContact
FROM    hz_cust_account_roles       acct_role,
        hz_relationships            rel,
	--			ra_customer_trx_all raall,
        hz_parties                  party
WHERE acct_role.cust_account_role_id =  ct.BILL_TO_CONTACT_ID
AND   acct_role.ROLE_TYPE            = 'CONTACT'
AND   acct_role.party_id             = rel.party_id
AND   rel.subject_id                 = party.party_id
AND   rel.SUBJECT_TABLE_NAME         = 'HZ_PARTIES'
AND   rel.OBJECT_TABLE_NAME          = 'HZ_PARTIES'
AND   rel.DIRECTIONAL_FLAG           = 'F') 
else
(select null from dual) 
end from dual)AS customerContact,
	    NULL as disc_amt,
	    NULL discount_last_date,
		/*
		DECODE(ArPaymentSchedulesV.CLASS,'PMT', 0, nvl(to_number(AR_IREC_PAYMENTS.get_discount_wrapper(ArPaymentSchedulesV.PAYMENT_SCHEDULE_ID, ArPaymentSchedulesV.AMOUNT_DUE_REMAINING)),0)) as disc_amt,
	    ( select min(
DECODE(TLD.DISCOUNT_DAYS ,NULL ,NVL(TLD.DISCOUNT_DATE,
                 DECODE ( LEAST(TO_NUMBER(TO_CHAR(PS.TRX_DATE,'DD')), 
                 NVL(T.DUE_CUTOFF_DAY,32)) ,T.DUE_CUTOFF_DAY,LAST_DAY(
                 ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD) ) + 
                 LEAST(TLD.DISCOUNT_DAY_OF_MONTH,               
                 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(PS.TRX_DATE, 
                 TLD.DISCOUNT_MONTHS_FORWARD+1)),'DD'))) ,      
                 LAST_DAY(ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD-1)) + 
                 LEAST(TLD.DISCOUNT_DAY_OF_MONTH ,TO_NUMBER(TO_CHAR(LAST_DAY( 
                 ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD)),'DD')))  ) ) ,
                 PS.TRX_DATE + TLD.DISCOUNT_DAYS) + 
                 TO_NUMBER(DECODE(AR_IREC_PAYMENTS.is_grace_days_enabled_wrapper,'Y',
                                        nvl(CUST_SITE_LEVEL.discount_grace_days,
                                        nvl(CUST_ACCT_LEVEL.discount_grace_days,0)  ),
                                        0)))  last_date
from AR_PAYMENT_SCHEDULES ps, RA_TERMS_LINES_DISCOUNTS  TLD,
            RA_TERMS    T,
            HZ_CUSTOMER_PROFILES CUST_SITE_LEVEL,
            HZ_CUSTOMER_PROFILES CUST_ACCT_LEVEL
where ArPaymentSchedulesV.payment_schedule_id = ps.payment_schedule_id
 AND cust_site_level.cust_account_id(+) = ps.customer_id
   AND cust_site_level.site_use_id(+) = ps.customer_site_use_id
   AND cust_acct_level.cust_account_id = ps.customer_id
   AND cust_acct_level.site_use_id IS NULL
   AND t.term_id = ps.term_id
   AND tld.term_id = t.term_id
   AND tld.sequence_num = ps.terms_sequence_number
   and trunc(
DECODE(TLD.DISCOUNT_DAYS ,NULL ,NVL(TLD.DISCOUNT_DATE,
                 DECODE ( LEAST(TO_NUMBER(TO_CHAR(PS.TRX_DATE,'DD')), 
                 NVL(T.DUE_CUTOFF_DAY,32)) ,T.DUE_CUTOFF_DAY,LAST_DAY( 
                 ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD) ) + 
                 LEAST(TLD.DISCOUNT_DAY_OF_MONTH,               
                 TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(PS.TRX_DATE, 
                 TLD.DISCOUNT_MONTHS_FORWARD+1)),'DD'))) ,      
                 LAST_DAY(ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD-1)) + 
                 LEAST(TLD.DISCOUNT_DAY_OF_MONTH ,TO_NUMBER(TO_CHAR(LAST_DAY( 
                 ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD)),'DD')))  ) ) ,
                 PS.TRX_DATE + TLD.DISCOUNT_DAYS) + 
                 TO_NUMBER(DECODE(AR_IREC_PAYMENTS.is_grace_days_enabled_wrapper,'Y',
                                        nvl(CUST_SITE_LEVEL.discount_grace_days,
                                        nvl(CUST_ACCT_LEVEL.discount_grace_days,0)  ),
                                        0)))>=trunc(sysdate) ) as discount_last_date,
	   */
       ArPaymentSchedulesV.PAYMENT_SCHEDULE_ID,
       ArPaymentSchedulesV.DUE_DATE,
       (SELECT nvl ((SELECT fnd_message.get_string('AR', 'ARI_STATUS_OVERDUE') FROM dual WHERE ( trunc(sysdate) > ArPaymentSchedulesV.due_date ) AND ArPaymentSchedulesV.CLASS <> 'PMT'AND ArPaymentSchedulesV.STATUS = 'OP'), ARI_UTILITIES.get_lookup_meaning('PAYMENT_SCHEDULE_STATUS', ArPaymentSchedulesV.STATUS))
        FROM DUAL
       ) as AR_LOOKUP_CODE_STATUS_MEANING,
      ArPaymentSchedulesV.AMOUNT_IN_DISPUTE,
      acct.account_number customer_number,
      acct.account_name customer_name,
      billto_uses.Location bill_to_location,
      shipto_uses.Location ship_to_location,
      ArPaymentSchedulesV.CASH_RECEIPT_ID,
      ARI_UTILITIES.get_lookup_meaning('INV/CM/ADJ', ArPaymentSchedulesV.class) as ACCT_DTLS_VIEW_TRX_TYPE,
      ArPaymentSchedulesV.INVOICE_CURRENCY_CODE,
  DECODE(ArPaymentSchedulesV.CLASS,             
	     'PMT',
       (select -sum(app.amount_applied)
       from AR_PAYMENT_SCHEDULES ps, ar_receivable_applications app
       where ps.customer_id = ArPaymentSchedulesV.customer_id
       AND    decode( (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID),
                     NULL, nvl(ps.customer_site_use_id,-10),
ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID)
         = nvl(ps.customer_site_use_id,-10)
       AND ps.invoice_currency_code =
ArPaymentSchedulesV.INVOICE_CURRENCY_CODE
       AND app.cash_receipt_id = ps.cash_receipt_id
       and app.applied_payment_schedule_id = -4
       AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
       AND app.status = 'OTHER ACC'
       AND app.cash_receipt_id = ArPaymentSchedulesV.CASH_RECEIPT_ID), null) AS
  amount_in_claim, 
  DECODE(ArPaymentSchedulesV.CLASS,             
	     'PMT',
       ( select -sum(app.amount_applied)
               from AR_PAYMENT_SCHEDULES ps, ar_receivable_applications app
               where ps.customer_id = ArPaymentSchedulesV.customer_id
               AND    decode( (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID),
                                NULL, nvl(ps.customer_site_use_id,-10), ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID)
                                  = nvl(ps.customer_site_use_id,-10)
               AND ps.invoice_currency_code = ArPaymentSchedulesV.INVOICE_CURRENCY_CODE
               AND app.cash_receipt_id = ps.cash_receipt_id
               AND nvl( app.confirmed_flag, 'Y' ) = 'Y'
               AND app.status = 'ACC'
               AND app.cash_receipt_id = ArPaymentSchedulesV.CASH_RECEIPT_ID
             ),null) AS ON_ACCOUNT_AMOUNT
FROM 
  AR_PAYMENT_SCHEDULES ArPaymentSchedulesV,
  ra_customer_trx ct, hz_cust_accounts acct,ra_terms t,ar_lookups al,
  ar_irec_user_acct_sites_all auasa,
  hz_cust_site_uses billto_uses,
  hz_cust_site_uses shipto_uses
WHERE
  ArPaymentSchedulesV.customer_trx_id = ct.customer_trx_id(+)
  AND ArPaymentSchedulesV.STATUS = 'OP'
  and billto_uses.site_use_id (+)= ct.bill_to_site_use_id
  and shipto_uses.site_use_id (+)=ct.ship_to_site_use_id
  and shipto_uses.site_use_code(+)='SHIP_TO'
  AND acct.cust_account_id = ArPaymentSchedulesV.customer_id
  AND ArPaymentSchedulesV.customer_id = auasa.customer_id
  AND (ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID = auasa.CUSTOMER_SITE_USE_ID
     OR (auasa.CUSTOMER_SITE_USE_ID = -1 AND ArPaymentSchedulesV.CUSTOMER_SITE_USE_ID IS NULL))
  AND ArPaymentSchedulesV.customer_id =:customerId
  AND ArPaymentSchedulesV.INVOICE_CURRENCY_CODE =:CurrencyCode
  AND auasa.user_id = FND_GLOBAL.USER_ID
  AND auasa.session_id =:sessionId
  AND ct.term_id = t.term_id(+)
  AND al.lookup_type='ARI_PMT_APPROVAL_STATUS'
  and al.lookup_code=nvl(ArPaymentSchedulesV.PAYMENT_APPROVAL,'PENDING')
  AND(TRUNC(ArPaymentSchedulesV.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ArPaymentSchedulesV.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
  AND (ct.printing_option =  decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option) OR ArPaymentSchedulesV.CLASS='PMT')
)pmt_sch