eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_RECEIVABLE_APPLICATIONS_V

Source

View Text - Preformatted


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 , decode(ps_inv.payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK'), 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 ,APP.INCLUDE_IN_ACCUMULATION INCLUDE_IN_ACCUMULATION ,APP.ON_ACCT_CUST_ID ,APP.ON_ACCT_CUST_SITE_USE_ID ,APP.ON_ACCT_PO_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

View Text - HTML Formatted

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
, DECODE(PS_INV.PAYMENT_SCHEDULE_ID
, -1
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'ON_ACC')
, -3
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'RCPT_WRITE_OFF')
, -4
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'CLAIM_INV')
, -6
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'CC_REFUND')
, -8
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'REFUND')
, -9
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ACTIVITY_APPS'
, 'CC_CHARGEBACK')
, 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
, APP.INCLUDE_IN_ACCUMULATION INCLUDE_IN_ACCUMULATION
, APP.ON_ACCT_CUST_ID
, APP.ON_ACCT_CUST_SITE_USE_ID
, APP.ON_ACCT_PO_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