View: AR_ICR_LINES_TRX_V

Product:	AR - Receivables
Description:	(Release 115 Only)
Implementation/DBA Data:	APPS.AR_ICR_LINES_TRX_V

View Text

SELECT PS.PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER
, CI.CONS_BILLING_NUMBER BILLING_NUMBER
, PS.TERM_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, DECODE(PS.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, NVL(PS.AMOUNT_DUE_REMAINING
, 0)) AMOUNT_DUE_REMAINING
, NVL(PS.CUSTOMER_TRX_ID
, -1) CUSTOMER_TRX_ID
, PS.TRX_DATE
, PS.AMOUNT_DUE_ORIGINAL
, NVL(PS.DISCOUNT_TAKEN_EARNED
, 0) DISCOUNT_TAKEN_EARNED
, NVL(PS.DISCOUNT_TAKEN_UNEARNED
, 0) DISCOUNT_TAKEN_UNEARNED
, NVL(PS.AMOUNT_LINE_ITEMS_ORIGINAL
, 0) AMOUNT_LINE_ITEMS_ORIGINAL
, PS.CUSTOMER_ID CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, DECODE(PS.CLASS
, 'BR'
, BR_SITE_USES.LOCATION
, 'PMT'
, RCT_SITE_USES.LOCATION
, SITE_USES.LOCATION) LOCATION
, RA_TERMS.CALC_DISCOUNT_ON_LINES_FLAG
, RA_TERMS.PARTIAL_DISCOUNT_FLAG
, CP.DISCOUNT_GRACE_DAYS GRACE_DAYS
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'NAME') TYPE_NAME
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'ALLOW_OVERAPPLICATION_FLAG') ALLOW_OVERAPPLICATION_FLAG
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'NATURAL_APPLICATION_ONLY_FLAG') NATURAL_APPLICATION_ONLY_FLAG
, ARPT_SQL_FUNC_UTIL.GET_TRX_TYPE_DETAILS(TRX.CUST_TRX_TYPE_ID
, 'CREATION_SIGN') CREATION_SIGN
, PS.CLASS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS) CLASS_MEANING
, TRX.INTERFACE_HEADER_ATTRIBUTE1
, TRX.INTERFACE_HEADER_ATTRIBUTE2
, TRX.INTERFACE_HEADER_ATTRIBUTE3
, TRX.INTERFACE_HEADER_ATTRIBUTE4
, TRX.INTERFACE_HEADER_ATTRIBUTE5
, TRX.INTERFACE_HEADER_ATTRIBUTE6
, TRX.INTERFACE_HEADER_ATTRIBUTE7
, TRX.INTERFACE_HEADER_ATTRIBUTE8
, TRX.INTERFACE_HEADER_ATTRIBUTE9
, TRX.INTERFACE_HEADER_ATTRIBUTE10
, TRX.INTERFACE_HEADER_ATTRIBUTE11
, TRX.INTERFACE_HEADER_ATTRIBUTE12
, TRX.INTERFACE_HEADER_ATTRIBUTE13
, TRX.INTERFACE_HEADER_ATTRIBUTE14
, TRX.INTERFACE_HEADER_ATTRIBUTE15
, TRX.PURCHASE_ORDER
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_NUM)
, BS.DEFAULT_REFERENCE) DEFAULT_REFERENCE
, APP.STATUS APPLICATION_STATUS
, DECODE(APP.STATUS
, NULL
, NULL
, 'UNAPP'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, 'UNAPP')
, PS_DUMMY.TRX_NUMBER) APPLICATION_STATUS_MEANING
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.CUSTOMER_REFERENCE)
, TRX.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_REASON)
, TRX.REASON_CODE) REASON_CODE
, DECODE(PS.CLASS
, 'PMT'
, DECODE( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.APPLICATION_REF_TYPE
, NULL)
, 'CLAIM'
, ARP_DEDUCTION.GET_TM_ORACLE_REASON( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.SECONDARY_APPLICATION_REF_ID
, NULL))
, NULL)
, 'CM'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON'
, TRX.REASON_CODE)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
, TRX.REASON_CODE)) REASON_MEANING
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.RECEIVABLE_APPLICATION_ID) APPLIED_REC_APP_ID
, APP.CASH_RECEIPT_ID APPLIED_CASH_RECEIPT_ID
, CUST_ACCT.ACCOUNT_NAME ACCOUNT_DESCRIPTION
FROM AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUSTOMER_TRX TRX
, HZ_CUST_SITE_USES SITE_USES
, HZ_CUST_SITE_USES BR_SITE_USES
, HZ_CUST_SITE_USES RCT_SITE_USES
, RA_TERMS
, HZ_CUSTOMER_PROFILES CP
, AR_RECEIVABLE_APPLICATIONS APP
, AR_PAYMENT_SCHEDULES PS_DUMMY
, AR_CONS_INV CI
, RA_BATCH_SOURCES BS
WHERE PS.STATUS <> 'CL'
AND PS.CLASS <> 'GUAR'
AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS.RESERVED_TYPE IS NULL /* 08-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND PS.RESERVED_VALUE IS NULL /* 08-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND TRX.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
AND SITE_USES.SITE_USE_ID(+) = TRX.BILL_TO_SITE_USE_ID
AND BR_SITE_USES.SITE_USE_ID(+) = TRX.DRAWEE_SITE_USE_ID /* 01-AUG-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND RCT_SITE_USES.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID
AND PS.TERM_ID = RA_TERMS.TERM_ID (+)
AND CP.CUST_ACCOUNT_ID(+) = TRX.BILL_TO_CUSTOMER_ID
AND CP.SITE_USE_ID IS NULL
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID
AND BS.BATCH_SOURCE_ID (+) = TRX.BATCH_SOURCE_ID
AND PS.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID(+)
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_DUMMY.PAYMENT_SCHEDULE_ID(+)
AND NVL(APP.STATUS
, 'UNAPP') IN ('UNAPP'
, 'ACC'
, 'OTHER ACC')
AND DECODE(APP.STATUS
, 'OTHER ACC'
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4) = -4
AND DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, APP.DISPLAY
, -4
, APP.DISPLAY
, 'Y') = 'Y'
AND (SIGN(PS.PAYMENT_SCHEDULE_ID) <> -1 OR PS.PAYMENT_SCHEDULE_ID IN (-1
, -4)) GROUP BY DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)
, PS.PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER
, CI.CONS_BILLING_NUMBER
, PS.TERM_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.DUE_DATE
, PS.INVOICE_CURRENCY_CODE
, PS.CUSTOMER_TRX_ID
, PS.AMOUNT_DUE_REMAINING
, PS.TRX_DATE
, PS.AMOUNT_DUE_ORIGINAL
, PS.DISCOUNT_TAKEN_EARNED
, PS.DISCOUNT_TAKEN_UNEARNED
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, PS.CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, DECODE(PS.CLASS
, 'BR'
, BR_SITE_USES.LOCATION
, 'PMT'
, RCT_SITE_USES.LOCATION
, SITE_USES.LOCATION)
, RA_TERMS.CALC_DISCOUNT_ON_LINES_FLAG
, RA_TERMS.PARTIAL_DISCOUNT_FLAG
, CP.DISCOUNT_GRACE_DAYS
, TRX.CUST_TRX_TYPE_ID
, PS.CLASS
, TRX.INTERFACE_HEADER_ATTRIBUTE1
, TRX.INTERFACE_HEADER_ATTRIBUTE2
, TRX.INTERFACE_HEADER_ATTRIBUTE3
, TRX.INTERFACE_HEADER_ATTRIBUTE4
, TRX.INTERFACE_HEADER_ATTRIBUTE5
, TRX.INTERFACE_HEADER_ATTRIBUTE6
, TRX.INTERFACE_HEADER_ATTRIBUTE7
, TRX.INTERFACE_HEADER_ATTRIBUTE8
, TRX.INTERFACE_HEADER_ATTRIBUTE9
, TRX.INTERFACE_HEADER_ATTRIBUTE10
, TRX.INTERFACE_HEADER_ATTRIBUTE11
, TRX.INTERFACE_HEADER_ATTRIBUTE12
, TRX.INTERFACE_HEADER_ATTRIBUTE13
, TRX.INTERFACE_HEADER_ATTRIBUTE14
, TRX.INTERFACE_HEADER_ATTRIBUTE15
, TRX.PURCHASE_ORDER
, PS_DUMMY.TRX_NUMBER
, APP.STATUS
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_NUM)
, BS.DEFAULT_REFERENCE)
, TRX.CUSTOMER_REFERENCE
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.CUSTOMER_REFERENCE)
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_REASON)
, TRX.REASON_CODE)
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.APPLICATION_REF_REASON)
, TRX.REASON_CODE
, APP.CASH_RECEIPT_ID
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.APPLICATION_REF_TYPE
, NULL)
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.SECONDARY_APPLICATION_REF_ID
, NULL)
, DECODE(APP.STATUS
, 'UNAPP'
, NULL
, APP.RECEIVABLE_APPLICATION_ID)
, CUST_ACCT.ACCOUNT_NAME HAVING DECODE(PS.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, DECODE(SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, -1
, PS.AMOUNT_DUE_REMAINING)) <> 0

Columns