VIEW: APPS.AR_RA_SELECTED_TRX_V

Source

View Text - Preformatted

SELECT /* PURPOSE: THE VIEW AR_RA_SELECTED_TRX_V IS USED IN */ /* THE APPLICATIONS WINDOW IN RECEIPT WORKBENCH */ APP.ROWID ROW_ID , APP.CASH_RECEIPT_ID CASH_RECEIPT_ID , PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , CR.RECEIPT_NUMBER RECEIPT_NUMBER , PS_INV.CUSTOMER_ID CUSTOMER_ID , SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER , DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, -1, L_ACC.MEANING, PS_INV.TRX_NUMBER) TRX_NUMBER , TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, -1,NULL, PS_INV.TERMS_SEQUENCE_NUMBER)) INSTALLMENT , APP.AMOUNT_APPLIED AMOUNT_APPLIED , NVL(APP.EARNED_DISCOUNT_TAKEN,0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN,0) DISCOUNT , PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNTS_EARNED , PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNTS_UNEARNED , PS_INV.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING , PS_INV.DUE_DATE DUE_DATE , PS_INV.STATUS STATUS , PS_INV.TERM_ID TERM_ID , 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 , 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 , 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 , PS_INV.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING , APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO, APP.ACCTD_AMOUNT_APPLIED_FROM) AMOUNT_EXCH_RATE_DIFF_BASE , PS_INV.DISCOUNT_REMAINING DISCOUNT_REMAINING , T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG , T.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.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.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 FROM AR_PAYMENT_SCHEDULES PS_INV , RA_CUSTOMER_TRX_LINES CTL , RA_CUSTOMER_TRX CT , RA_BATCH_SOURCES BS , RA_CUST_TRX_TYPES CTT , HZ_CUST_SITE_USES SU , AR_LOOKUPS L_CLASS , AR_LOOKUPS L_ACC , HZ_CUST_ACCOUNTS CUST , HZ_PARTIES PARTY , RA_TERMS T , AR_RECEIVABLE_APPLICATIONS APP , AR_CASH_RECEIPTS CR , AR_CONS_INV CI WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID AND APP.DISPLAY = 'Y' AND APP.STATUS = 'APP' AND APP.REVERSAL_GL_DATE IS NULL AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND T.TERM_ID(+) = PS_INV.TERM_ID AND CT.CUSTOMER_TRX_ID = PS_INV.CUSTOMER_TRX_ID AND BS.BATCH_SOURCE_ID(+) = CT.BATCH_SOURCE_ID AND PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID >0 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(+) = CT.BILL_TO_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 L_ACC.LOOKUP_TYPE = 'PAYMENT_TYPE' AND L_ACC.LOOKUP_CODE = 'ACC'

View Text - HTML Formatted

SELECT /* PURPOSE: THE VIEW AR_RA_SELECTED_TRX_V IS USED IN */ /* THE APPLICATIONS WINDOW IN RECEIPT WORKBENCH */ APP.ROWID ROW_ID
, APP.CASH_RECEIPT_ID CASH_RECEIPT_ID
, PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, PS_INV.CUSTOMER_ID CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, L_ACC.MEANING
, PS_INV.TRX_NUMBER) TRX_NUMBER
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, PS_INV.TERMS_SEQUENCE_NUMBER)) INSTALLMENT
, APP.AMOUNT_APPLIED AMOUNT_APPLIED
, NVL(APP.EARNED_DISCOUNT_TAKEN
, 0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
, 0) DISCOUNT
, PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNTS_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNTS_UNEARNED
, PS_INV.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PS_INV.DUE_DATE DUE_DATE
, PS_INV.STATUS STATUS
, PS_INV.TERM_ID TERM_ID
, 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
, 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
, 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
, PS_INV.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING
, APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO
, APP.ACCTD_AMOUNT_APPLIED_FROM) AMOUNT_EXCH_RATE_DIFF_BASE
, PS_INV.DISCOUNT_REMAINING DISCOUNT_REMAINING
, T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG
, T.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.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.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
FROM AR_PAYMENT_SCHEDULES PS_INV
, RA_CUSTOMER_TRX_LINES CTL
, RA_CUSTOMER_TRX CT
, RA_BATCH_SOURCES BS
, RA_CUST_TRX_TYPES CTT
, HZ_CUST_SITE_USES SU
, AR_LOOKUPS L_CLASS
, AR_LOOKUPS L_ACC
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, RA_TERMS T
, AR_RECEIVABLE_APPLICATIONS APP
, AR_CASH_RECEIPTS CR
, AR_CONS_INV CI
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
AND APP.DISPLAY = 'Y'
AND APP.STATUS = 'APP'
AND APP.REVERSAL_GL_DATE IS NULL
AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND T.TERM_ID(+) = PS_INV.TERM_ID
AND CT.CUSTOMER_TRX_ID = PS_INV.CUSTOMER_TRX_ID
AND BS.BATCH_SOURCE_ID(+) = CT.BATCH_SOURCE_ID
AND PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID >0
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(+) = CT.BILL_TO_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 L_ACC.LOOKUP_TYPE = 'PAYMENT_TYPE'
AND L_ACC.LOOKUP_CODE = 'ACC'