View: AR_RECEIVABLE_APPLICATIONS_V

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

View Text

SELECT /*+ INDEX(APP AR_RECEIVABLE_APPLICATIONS_N1) INDEX(PS_INV AR_PAYMENT_SCHEDULES_U1 AR_PAYMENT_SCHEDULES_N2 AR_PAYMENT_SCHEDULES_N6) INDEX(CR AR_CASH_RECEIPTS_U1) INDEX(CT RA_CUSTOMER_TRX_U1) INDEX(CTT RA_CUST_TRX_TYPES_U1) INDEX(BS RA_BATCH_SOURCES_U2) INDEX(SU HZ_CUST_SITE_USES_U1) INDEX(T RA_TERMS_U1) INDEX(AR_RECEIVABLES_TRX_U1) ORDERED*/ APP.ROWID ROW_ID
, APP.CASH_RECEIPT_ID CASH_RECEIPT_ID
, PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, TO_NUMBER('') CM_CUSTOMER_TRX_ID
, APP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, APP.LAST_UPDATED_BY LAST_UPDATED_BY
, APP.CREATION_DATE CREATION_DATE
, APP.CREATED_BY CREATED_BY
, APP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, APP.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, APP.PROGRAM_ID PROGRAM_ID
, APP.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, APP.REQUEST_ID REQUEST_ID
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, 'Y' APPLIED_FLAG
, PS_INV.CUSTOMER_ID CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, PS_INV.TRX_NUMBER TRX_NUMBER
, TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, NULL
, PS_INV.TERMS_SEQUENCE_NUMBER)) INSTALLMENT
, APP.AMOUNT_APPLIED AMOUNT_APPLIED
, NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED) AMOUNT_APPLIED_FROM
, APP.TRANS_TO_RECEIPT_RATE TRANS_TO_RECEIPT_RATE
, TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, NULL
, NVL(APP.EARNED_DISCOUNT_TAKEN
, 0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
, 0))) DISCOUNT
, APP.EARNED_DISCOUNT_TAKEN DISCOUNTS_EARNED
, APP.UNEARNED_DISCOUNT_TAKEN DISCOUNTS_UNEARNED
, PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED
, TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, NULL
, PS_INV.AMOUNT_DUE_REMAINING)) AMOUNT_DUE_REMAINING
, DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, TO_DATE(NULL)
, PS_INV.DUE_DATE) DUE_DATE
, PS_INV.STATUS STATUS
, PS_INV.TERM_ID TERM_ID
, DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, NULL
, L_CLASS.MEANING) TRX_CLASS_NAME
, PS_INV.CLASS TRX_CLASS_CODE
, CTT.NAME TRX_TYPE_NAME
, CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CT.TRX_DATE TRX_DATE
, SU.LOCATION LOCATION_NAME
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, NULL
, TRUNC(APP.APPLY_DATE) -TRUNC(PS_INV.DUE_DATE))) DAYS_LATE
, CTL.LINE_NUMBER LINE_NUMBER
, CTL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID
, APP.APPLY_DATE APPLY_DATE
, APP.GL_DATE GL_DATE
, APP.GL_POSTED_DATE GL_POSTED_DATE
, APP.REVERSAL_GL_DATE REVERSAL_GL_DATE
, PS_INV.EXCHANGE_RATE EXCHANGE_RATE
, DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, CR.CURRENCY_CODE
, PS_INV.INVOICE_CURRENCY_CODE) INVOICE_CURRENCY_CODE
, PS_INV.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS_INV.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, PS_INV.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, NULL
, PS_INV.ACCTD_AMOUNT_DUE_REMAINING)) ACCTD_AMOUNT_DUE_REMAINING
, APP.ACCTD_AMOUNT_APPLIED_TO ACCTD_AMOUNT_APPLIED_TO
, APP.ACCTD_AMOUNT_APPLIED_FROM ACCTD_AMOUNT_APPLIED_FROM
, TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, NULL
, APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO
, APP.ACCTD_AMOUNT_APPLIED_FROM))) EXCHANGE_GAIN_LOSS
, PS_INV.DISCOUNT_REMAINING DISCOUNT_REMAINING
, ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS_INV.TERM_ID
, 'CALC_DISCOUNT_ON_LINES_FLAG') CALC_DISCOUNT_ON_LINES_FLAG
, ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS_INV.TERM_ID
, 'PARTIAL_DISCOUNT_FLAG') PARTIAL_DISCOUNT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APPLICATION_ONLY_FLAG
, CTT.CREATION_SIGN CREATION_SIGN
, PS_INV.PAYMENT_SCHEDULE_ID APPLIED_PAYMENT_SCHEDULE_ID
, APP.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, APP.USSGL_TRANSACTION_CODE_CONTEXT USSGL_TRANSACTION_CODE_CONTEXT
, CT.PURCHASE_ORDER PURCHASE_ORDER
, CT.DOC_SEQUENCE_ID TRX_DOC_SEQUENCE_ID
, CT.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, BS.NAME TRX_BATCH_SOURCE_NAME
, PS_INV.AMOUNT_ADJUSTED AMOUNT_ADJUSTED
, PS_INV.AMOUNT_ADJUSTED_PENDING AMOUNT_ADJUSTED_PENDING
, PS_INV.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, PS_INV.FREIGHT_ORIGINAL FREIGHT_ORIGINAL
, PS_INV.FREIGHT_REMAINING FREIGHT_REMAINING
, PS_INV.RECEIVABLES_CHARGES_REMAINING RECEIVABLES_CHARGES_REMAINING
, PS_INV.TAX_ORIGINAL TAX_ORIGINAL
, PS_INV.TAX_REMAINING TAX_REMAINING
, PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
, APP.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID
, APP.SECONDARY_APPLICATION_REF_ID SECONDARY_APPLICATION_REF_ID
, APP.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, APP.ATTRIBUTE1 ATTRIBUTE1
, APP.ATTRIBUTE2 ATTRIBUTE2
, APP.ATTRIBUTE3 ATTRIBUTE3
, APP.ATTRIBUTE4 ATTRIBUTE4
, APP.ATTRIBUTE5 ATTRIBUTE5
, APP.ATTRIBUTE6 ATTRIBUTE6
, APP.ATTRIBUTE7 ATTRIBUTE7
, APP.ATTRIBUTE8 ATTRIBUTE8
, APP.ATTRIBUTE9 ATTRIBUTE9
, APP.ATTRIBUTE10 ATTRIBUTE10
, APP.ATTRIBUTE11 ATTRIBUTE11
, APP.ATTRIBUTE12 ATTRIBUTE12
, APP.ATTRIBUTE13 ATTRIBUTE13
, APP.ATTRIBUTE14 ATTRIBUTE14
, APP.ATTRIBUTE15 ATTRIBUTE15
, CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER
, APP.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, APP.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, APP.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, APP.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, APP.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, APP.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, APP.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, APP.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, APP.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, APP.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, APP.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, APP.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, APP.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, APP.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, APP.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, APP.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, APP.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, APP.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, APP.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, APP.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, APP.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, APP.COMMENTS
, CTT.ATTRIBUTE10 TRANSACTION_CATEGORY
, APP.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, ART.NAME
, DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, ART.NAME
, NULL)) REC_ACTIVITY_NAME
, APP.APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -7
, 'AR_PREPAYMENT_TYPE'
, 'APPLICATION_REF_TYPE')
, APP.APPLICATION_REF_TYPE) APPLICATION_REF_TYPE_MEANING
, APP.APPLICATION_REF_ID
, APP.APPLICATION_REF_NUM
, APP.PAYMENT_SET_ID
, APP.APPLICATION_REF_REASON
, DECODE(APP.SECONDARY_APPLICATION_REF_ID
, NULL
, DECODE(PS_INV.CLASS
, '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))
, ARP_DEDUCTION.GET_TM_ORACLE_REASON( APP.SECONDARY_APPLICATION_REF_ID) ) APPLICATION_REF_REASON_MEANING
, APP.CUSTOMER_REFERENCE
, APP.CUSTOMER_REASON
, APP.APPLIED_REC_APP_ID
, APP.SECONDARY_APPLICATION_REF_TYPE SECONDARY_APPLICATION_REF_TYPE
, APP.SECONDARY_APPLICATION_REF_NUM SECONDARY_APPLICATION_REF_NUM
FROM AR_RECEIVABLE_APPLICATIONS APP
, AR_CASH_RECEIPTS CR
, AR_PAYMENT_SCHEDULES PS_INV
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, RA_CUSTOMER_TRX CT
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX_LINES CTL
, RA_BATCH_SOURCES BS
, HZ_CUST_SITE_USES SU
, AR_CONS_INV CI
, AR_LOOKUPS L_CLASS
, AR_RECEIVABLES_TRX ART
WHERE APP.DISPLAY = 'Y'
AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CT.CUSTOMER_TRX_ID(+) = PS_INV.CUSTOMER_TRX_ID
AND BS.BATCH_SOURCE_ID (+) = CT.BATCH_SOURCE_ID
AND CTT.CUST_TRX_TYPE_ID(+) = PS_INV.CUST_TRX_TYPE_ID
AND CUST.CUST_ACCOUNT_ID(+) = PS_INV.CUSTOMER_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = PS_INV.CUSTOMER_SITE_USE_ID
AND CTL.CUSTOMER_TRX_LINE_ID(+) = APP.APPLIED_CUSTOMER_TRX_LINE_ID
AND PS_INV.CLASS = L_CLASS.LOOKUP_CODE
AND L_CLASS.LOOKUP_TYPE = 'INV/CM'
AND CI.CONS_INV_ID(+) = PS_INV.CONS_INV_ID
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
AND ART.RECEIVABLES_TRX_ID(+) = APP.RECEIVABLES_TRX_ID

Columns