View: ICX_AR_RECEIVABLE_APPL_V

View Text

SELECT /* PURPOSE: THE VIEW AR_RECEIVABLE_APPLICATIONS_V IS USED IN */ /* THE APPLICATIONS WINDOW IN RECEIPT WORKBENCH */ APP.ROWID ROW_ID
, APP.CASH_RECEIPT_ID CASH_RECEIPT_ID
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, L_ACC.MEANING
, PS_INV.TRX_NUMBER) TRX_NUMBER
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, L_CLASS.MEANING) TRX_CLASS_NAME
, TO_CHAR(APP.AMOUNT_APPLIED
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS_INV.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_APPLIED
, TO_CHAR(TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, NVL(APP.EARNED_DISCOUNT_TAKEN
, 0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
, 0)))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS_INV.INVOICE_CURRENCY_CODE
, 30)) DISCOUNT
, APP.APPLY_DATE APPLY_DATE
, TO_CHAR(PS_INV.AMOUNT_DUE_ORIGINAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS_INV.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_ORIGINAL
, TO_CHAR(TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, APP.AMOUNT_APPLIED*-1
, PS_INV.AMOUNT_DUE_REMAINING))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS_INV.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_REMAINING
, CUST.CUSTOMER_NAME CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER CUSTOMER_NUMBER
, SU.LOCATION LOCATION_NAME
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, 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
, APP.GL_DATE GL_DATE
, APP.GL_POSTED_DATE GL_POSTED_DATE
, APP.REVERSAL_GL_DATE REVERSAL_GL_DATE
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, PS_INV.TERMS_SEQUENCE_NUMBER)) INSTALLMENT
, TO_DATE(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, PS_INV.DUE_DATE)) DUE_DATE
, TO_NUMBER(DECODE(APP. APPLIED_PAYMENT_SCHEDULE_ID
, -1
, NULL
, TO_DATE(APP.APPLY_DATE) - TRUNC(PS_INV.DUE_DATE))) DAYS_LATE
, APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO
, APP.ACCTD_AMOUNT_APPLIED_FROM) AMOUNT_EXCH_RATE_DIFF_BASE
, APP.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, APP.USSGL_TRANSACTION_CODE_CONTEXT USSGL_TRANSACTION_CODE_CONTEXT
, 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
, PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS_INV.CUSTOMER_ID CUSTOMER_ID
, PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNTS_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNTS_UNEARNED
, PS_INV.STATUS STATUS
, PS_INV.TERM_ID TERM_ID
, PS_INV.CLASS TRX_CLASS_CODE
, PS_INV.EXCHANGE_RATE EXCHANGE_RATE
, PS_INV.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PS_INV.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, PS_INV.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING
, PS_INV.DISCOUNT_REMAINING DISCOUNT_REMAINING
, PS_INV.PAYMENT_SCHEDULE_ID APPLIED_PAYMENT_SCHEDULE_ID
, 'Y' APPLIED_FLAG
, CT.TRX_DATE TRX_DATE
, CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, CT.DOC_SEQUENCE_ID TRX_DOC_SEQUENCE_ID
, CT.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, DECODE (APP.AMOUNT_APPLIED_FROM
, NULL
, NULL
, '*')
, TO_CHAR(APP.AMOUNT_APPLIED_FROM
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( CR.CURRENCY_CODE
, 30))
, APP.TRANS_TO_RECEIPT_RATE
, TO_CHAR((APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO
, APP.ACCTD_AMOUNT_APPLIED_FROM))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( CR.CURRENCY_CODE
, 30))
FROM AR_PAYMENT_SCHEDULES PS_INV
, RA_CUSTOMER_TRX CT
, RA_SITE_USES SU
, AR_LOOKUPS L_CLASS
, AR_LOOKUPS L_ACC
, RA_CUSTOMERS CUST
, AR_RECEIVABLE_APPLICATIONS APP
, AR_CASH_RECEIPTS CR
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
AND APP.DISPLAY = 'Y'
AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CT.CUSTOMER_TRX_ID(+) = PS_INV.CUSTOMER_TRX_ID
AND CUST.CUSTOMER_ID(+) = PS_INV.CUSTOMER_ID
AND SU.SITE_USE_ID(+) = CT.BILL_TO_SITE_USE_ID
AND PS_INV.CLASS = L_CLASS.LOOKUP_CODE /* GET ON ACCOUNT DESCRIPTION */
AND L_CLASS.LOOKUP_TYPE = 'INV/CM'
AND L_ACC.LOOKUP_TYPE = 'PAYMENT_TYPE'
AND L_ACC.LOOKUP_CODE= 'ACC'

Columns