View: AR_OPEN_ITEMS_V

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

View Text

SELECT /* PURPOSE: THIS VIEW IS USED MAINLY IN APPLICATIONS LOVS TO SHOW */ /* OPEN TRANSACTIONS */ PS.TRX_NUMBER TRX_NUMBER
, PS.CUSTOMER_ID CUSTOMER_ID
, CST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, SUBSTRB(CST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, SU.LOCATION LOCATION
, PS.CLASS CLASS
, DECODE(PS.CUSTOMER_ID
, -1
, NULL
, -3
, NULL
, -4
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)) CLASS_MEANING
, CTT.NAME TRX_TYPE
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, SUBSTR(DECODE(SIGN(PS.CUSTOMER_ID)
, -1
, NULL
, TO_CHAR( DECODE(APP.STATUS
, NULL
, PS.AMOUNT_DUE_REMAINING
, SUM(APP.AMOUNT_APPLIED)*-1)
, FND_CURRENCY.GET_FORMAT_MASK( DECODE(SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, 'USD'
, PS.INVOICE_CURRENCY_CODE)
, 30)))
, 1
, 30) BALANCE_DUE_CURR
, DECODE(APP.STATUS
, NULL
, PS.AMOUNT_DUE_REMAINING
, SUM(APP.AMOUNT_APPLIED)*-1) BALANCE_DUE_CURR_UNFORMATTED
, DECODE(APP.STATUS
, NULL
, PS.ACCTD_AMOUNT_DUE_REMAINING
, SUM(APP.ACCTD_AMOUNT_APPLIED_FROM)*-1) BALANCE_DUE_FUNCTIONAL
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, PS.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED
, PS.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED
, PS.EXCHANGE_RATE TRX_EXCHANGE_RATE
, DECODE(PS.CLASS
, 'PMT'
, CR.RECEIPT_DATE
, CT.TRX_DATE) TRX_DATE
, PS.GL_DATE TRX_GL_DATE
, TRUNC(DECODE(SIGN(PS.CUSTOMER_ID)
, -1
, FND_DATE.CANONICAL_TO_DATE(NULL)
, DECODE(PS.CLASS
, 'PMT'
, CR.RECEIPT_DATE
, PS.DUE_DATE) )) TRX_DUE_DATE
, PS.TERM_ID TERM_ID
, T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG
, T.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'N'
, CTT.ALLOW_OVERAPPLICATION_FLAG) ALLOW_OVERAPPLICATION_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'N'
, CTT.NATURAL_APPLICATION_ONLY_FLAG) NATURAL_APPLICATION_ONLY_FLAG
, DECODE(PS.PAYMENT_SCHEDULE_ID
, -3
, 'P'
, CTT.CREATION_SIGN) CREATION_SIGN
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, TO_NUMBER(DECODE( SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, NULL
, PS.TERMS_SEQUENCE_NUMBER)) TERMS_SEQUENCE_NUMBER
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.STATUS STATUS
, CT.PURCHASE_ORDER
, CT.INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE1
, CT.INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE2
, CT.INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE3
, CT.INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE4
, CT.INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE5
, CT.INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE6
, CT.INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE7
, CT.INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE8
, CT.INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE9
, CT.INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE10
, CT.INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE11
, CT.INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE12
, CT.INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE13
, CT.INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE14
, CT.INTERFACE_HEADER_ATTRIBUTE15 INTERFACE_HEADER_ATTRIBUTE15
, BS.NAME TRX_BATCH_SOURCE_NAME
, NVL(PS.AMOUNT_ADJUSTED
, 0) AMOUNT_ADJUSTED
, NVL(PS.AMOUNT_ADJUSTED_PENDING
, 0) AMOUNT_ADJUSTED_PENDING
, NVL(PS.AMOUNT_LINE_ITEMS_REMAINING
, 0) AMOUNT_LINE_ITEMS_REMAINING
, NVL(PS.FREIGHT_ORIGINAL
, 0) FREIGHT_ORIGINAL
, NVL(PS.FREIGHT_REMAINING
, 0) FREIGHT_REMAINING
, NVL(PS.RECEIVABLES_CHARGES_REMAINING
, 0) RECEIVABLES_CHARGES_REMAINING
, NVL(PS.TAX_ORIGINAL
, 0) TAX_ORIGINAL
, NVL(PS.TAX_REMAINING
, 0) TAX_REMAINING
, PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
, CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, BS.DEFAULT_REFERENCE
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, 'CLAIM'
, APP.STATUS) OPEN_RECEIPT_STATUS
, DECODE(APP.STATUS
, NULL
, NULL
, 'UNAPP'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, 'UNAPP')
, PS_DUMMY.TRX_NUMBER) OPEN_RECEIPT_STATUS_MEANING
, NVL(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REFERENCE
, -1
, APP.CUSTOMER_REFERENCE
, NULL)
, CT.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE
, 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'
, CT.REASON_CODE)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
, CT.REASON_CODE)) REASON
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)) RECEIVABLE_APPLICATION_ID
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM) APPLICATION_REF_NUM
, CST.ACCOUNT_NAME ACCOUNT_DESCRIPTION
, DECODE(PS.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REASON
, NULL)
, NULL) CUSTOMER_REASON
FROM RA_CUSTOMER_TRX CT
, RA_CUST_TRX_TYPES CTT
, HZ_CUST_SITE_USES SU
, RA_BATCH_SOURCES BS /*
, AR_LOOKUPS LU NOT USED -GET LOOKUP MEANING USED INSTEAD. */
, HZ_CUST_ACCOUNTS CST
, HZ_PARTIES CST_PARTY
, RA_TERMS T
, AR_RECEIVABLE_APPLICATIONS APP
, AR_CASH_RECEIPTS CR
, AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PS_DUMMY
, AR_CONS_INV CI
WHERE PS.CLASS IN ('CB'
, 'CM'
, 'DEP'
, 'DM'
, 'INV'
, 'BR'
, 'PMT') /* 20-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS.RESERVED_TYPE IS NULL /* 29-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND PS.RESERVED_VALUE IS NULL /* 29-APR-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND DECODE(PS.PAYMENT_SCHEDULE_ID
, -4
, ARPT_SQL_FUNC_UTIL.CHECK_ICLAIM_INSTALLED
, 'T') = 'T'
AND PS.PAYMENT_SCHEDULE_ID NOT IN ( -5
, -2
, -7) /* SNAMBIAR RECEIPT CHARGEBACK */
AND T.TERM_ID(+) = PS.TERM_ID
AND CT.CUSTOMER_TRX_ID(+) = PS.CUSTOMER_TRX_ID
AND BS.BATCH_SOURCE_ID (+) = CT.BATCH_SOURCE_ID
AND CTT.CUST_TRX_TYPE_ID(+) = PS.CUST_TRX_TYPE_ID
AND CST.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID
AND CST.PARTY_ID = CST_PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID /*AND PS.CLASS = LU.LOOKUP_CODE NOT USED -GET LOOKUP MEANING USED INSTEAD.*/
AND CT.PREVIOUS_CUSTOMER_TRX_ID IS NULL /*AND LU.LOOKUP_TYPE = 'INV/CM' NOT USED -GET LOOKUP MEANING USED INSTEAD.*/
AND CI.CONS_INV_ID(+) = PS.CONS_INV_ID
AND PS.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+)
AND CR.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' GROUP BY DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM)
, PS_DUMMY.TRX_NUMBER
, PS.TRX_NUMBER
, PS.CUSTOMER_ID
, CST.ACCOUNT_NUMBER
, CST_PARTY.PARTY_NAME
, SU.LOCATION
, PS.CLASS
, DECODE(PS.CUSTOMER_ID
, -1
, NULL
, -3
, NULL
, -4
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS))
, CTT.NAME
, PS.CUST_TRX_TYPE_ID
, PS.INVOICE_CURRENCY_CODE
, APP.STATUS
, PS.AMOUNT_DUE_REMAINING
, PS.INVOICE_CURRENCY_CODE
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, PS.DISCOUNT_TAKEN_EARNED
, PS.DISCOUNT_TAKEN_UNEARNED
, PS.EXCHANGE_RATE
, CR.RECEIPT_DATE
, CT.TRX_DATE
, PS.GL_DATE
, PS.DUE_DATE
, PS.TERM_ID
, T.CALC_DISCOUNT_ON_LINES_FLAG
, T.PARTIAL_DISCOUNT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG
, CTT.CREATION_SIGN
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, CT.CUSTOMER_TRX_ID
, PS.STATUS
, CT.PURCHASE_ORDER
, CT.INTERFACE_HEADER_ATTRIBUTE1
, CT.INTERFACE_HEADER_ATTRIBUTE2
, CT.INTERFACE_HEADER_ATTRIBUTE3
, CT.INTERFACE_HEADER_ATTRIBUTE4
, CT.INTERFACE_HEADER_ATTRIBUTE5
, CT.INTERFACE_HEADER_ATTRIBUTE6
, CT.INTERFACE_HEADER_ATTRIBUTE7
, CT.INTERFACE_HEADER_ATTRIBUTE8
, CT.INTERFACE_HEADER_ATTRIBUTE9
, CT.INTERFACE_HEADER_ATTRIBUTE10
, CT.INTERFACE_HEADER_ATTRIBUTE11
, CT.INTERFACE_HEADER_ATTRIBUTE12
, CT.INTERFACE_HEADER_ATTRIBUTE13
, CT.INTERFACE_HEADER_ATTRIBUTE14
, CT.INTERFACE_HEADER_ATTRIBUTE15
, BS.NAME
, PS.AMOUNT_ADJUSTED
, PS.AMOUNT_ADJUSTED_PENDING
, PS.AMOUNT_LINE_ITEMS_REMAINING
, PS.FREIGHT_ORIGINAL
, PS.FREIGHT_REMAINING
, PS.RECEIVABLES_CHARGES_REMAINING
, PS.TAX_ORIGINAL
, PS.TAX_REMAINING
, PS.SELECTED_FOR_RECEIPT_BATCH_ID
, CI.CONS_BILLING_NUMBER
, CT.BILL_TO_SITE_USE_ID
, BS.DEFAULT_REFERENCE
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, DECODE(APP.STATUS
, NULL
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('AR_OPEN_RECEIPT_STATUSES'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, 'CLAIM'
, APP.STATUS) ))
, APP.DISPLAY
, 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.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REFERENCE
, -1
, APP.CUSTOMER_REFERENCE
, NULL)
, CT.CUSTOMER_REFERENCE
, CT.REASON_CODE
, CST.ACCOUNT_NAME
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -4
, APP.CUSTOMER_REASON
, NULL)

Columns