View: AR_MASS_APPLICATIONS_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.AR_MASS_APPLICATIONS_V

View Text

SELECT /*+ FIRST_ROWS INDEX(CUST HZ_CUST_ACCOUNTS_U1) INDEX(BS RA_BATCH_SOURCES_U1) INDEX(CTT RA_CUST_TRX_TYPES_U1) INDEX(RCR HZ_CUST_ACCT_RELATE_N1) INDEX(SU HZ_CUST_SITE_USES_U1) INDEX(T RA_TERMS_B_U1) USE_NL(PS_INV) USE_NL(CT) USE_NL(CTT) USE_NL(CUST) USE_NL(SU) USE_NL(BS) USE_NL(T) USE_NL(CI) PUSH_SUBQ ORDERED */PS_INV.ROWID ROW_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.CASH_RECEIPT_ID
, -1) CASH_RECEIPT_ID
, PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, TO_NUMBER('') CM_CUSTOMER_TRX_ID
, PS_INV.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PS_INV.LAST_UPDATED_BY LAST_UPDATED_BY
, PS_INV.CREATION_DATE CREATION_DATE
, PS_INV.CREATED_BY CREATED_BY
, PS_INV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS_INV.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PS_INV.PROGRAM_ID PROGRAM_ID
, PS_INV.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, PS_INV.REQUEST_ID REQUEST_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.TRX_NUMBER
, NULL) RECEIPT_NUMBER
, 'N' 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
, PS_INV.TERMS_SEQUENCE_NUMBER INSTALLMENT
, TO_NUMBER('') AMOUNT_APPLIED
, TO_NUMBER('') AMOUNT_APPLIED_FROM
, TO_NUMBER('') TRANS_TO_RECEIPT_RATE
, TO_NUMBER('') DISCOUNT
, PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNTS_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNTS_UNEARNED
, PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED
, DECODE(PS_INV.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, PS_INV.AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
, PS_INV.DUE_DATE DUE_DATE
, PS_INV.STATUS STATUS
, PS_INV.TERM_ID TERM_ID
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS_INV.CLASS) 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
, PS_INV.CUSTOMER_SITE_USE_ID BILL_TO_SITE_USE_ID
, TO_NUMBER('') DAYS_LATE
, TO_NUMBER('') LINE_NUMBER
, TO_NUMBER('') CUSTOMER_TRX_LINE_ID
, GREATEST(PS_INV.TRX_DATE
, ARP_VIEW_CONSTANTS.GET_APPLY_DATE) APPLY_DATE /* BUG FIX 3400584*/
, ARP_VIEW_CONSTANTS.GET_DEFAULT_GL_DATE( GREATEST(PS_INV.GL_DATE
, ARP_VIEW_CONSTANTS.GET_RECEIPT_GL_DATE
, DECODE(NVL(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT')
, 'INV_REC_DT')
, 'INV_REC_SYS_DT'
, SYSDATE
, 'INV_REC_DT'
, PS_INV.GL_DATE))) GL_DATE /*3079614*/
, NULL GL_POSTED_DATE
, NULL 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
, TO_NUMBER('') ACCTD_AMOUNT_APPLIED_TO
, TO_NUMBER('') ACCTD_AMOUNT_APPLIED_FROM
, TO_NUMBER('') EXCHANGE_GAIN_LOSS
, 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
, CT.DEFAULT_USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, CT.DEFAULT_USSGL_TRX_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
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.RECEIVABLE_APPLICATION_ID)) RECEIVABLE_APPLICATION_ID
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.SECONDARY_APPLICATION_REF_ID)) SECONDARY_APPLICATION_REF_ID
, NULL ATTRIBUTE_CATEGORY
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
, CI.CONS_BILLING_NUMBER TRX_BILLING_NUMBER
, NULL GLOBAL_ATTRIBUTE_CATEGORY
, NULL GLOBAL_ATTRIBUTE1
, NULL GLOBAL_ATTRIBUTE2
, NULL GLOBAL_ATTRIBUTE3
, NULL GLOBAL_ATTRIBUTE4
, NULL GLOBAL_ATTRIBUTE5
, NULL GLOBAL_ATTRIBUTE6
, NULL GLOBAL_ATTRIBUTE7
, NULL GLOBAL_ATTRIBUTE8
, NULL GLOBAL_ATTRIBUTE9
, NULL GLOBAL_ATTRIBUTE10
, NULL GLOBAL_ATTRIBUTE11
, NULL GLOBAL_ATTRIBUTE12
, NULL GLOBAL_ATTRIBUTE13
, NULL GLOBAL_ATTRIBUTE14
, NULL GLOBAL_ATTRIBUTE15
, NULL GLOBAL_ATTRIBUTE16
, NULL GLOBAL_ATTRIBUTE17
, NULL GLOBAL_ATTRIBUTE18
, NULL GLOBAL_ATTRIBUTE19
, NULL GLOBAL_ATTRIBUTE20
, NULL COMMENTS
, CTT.ATTRIBUTE10 TRANSACTION_CATEGORY
, NULL RECEIVABLES_TRX_ID
, NULL REC_ACTIVITY_NAME
, NULL APPLICATION_REF_TYPE /*THESE COLUMNS ARE ADDED FOR BUG 2545431 */
, NULL APPLICATION_REF_TYPE_MEANING
, NULL APPLICATION_REF_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM)
, CT.CT_REFERENCE) APPLICATION_REF_NUM
, NULL PAYMENT_SET_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_REASON)
, CT.REASON_CODE) APPLICATION_REF_REASON
, DECODE(PS_INV.CLASS
, 'PMT'
, ARP_DEDUCTION.GET_TM_ORACLE_REASON(TO_NUMBER( DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.SECONDARY_APPLICATION_REF_ID)))
, '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) ) APPLICATION_REF_REASON_MEANING
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REFERENCE)
, CT.CUSTOMER_REFERENCE) CUSTOMER_REFERENCE
, DECODE(PS_INV.CLASS
, 'PMT'
, DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REASON)
, NULL) CUSTOMER_REASON
, TO_NUMBER(NULL) APPLIED_REC_APP_ID
, NULL SECONDARY_APPLICATION_REF_NUM
, NULL SECONDARY_APPLICATION_REF_TYPE
FROM AR_PAYMENT_SCHEDULES PS_INV
, RA_CUSTOMER_TRX CT
, RA_CUST_TRX_TYPES CTT
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SU
, RA_BATCH_SOURCES BS
, AR_RECEIVABLE_APPLICATIONS APP
, RA_TERMS_B T
, AR_CONS_INV CI
WHERE PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL
AND PS_INV.RESERVED_TYPE IS NULL
AND PS_INV.RESERVED_VALUE IS NULL
AND PS_INV.CLASS||'' <> 'GUAR'
AND PS_INV.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND PS_INV.STATUS = 'OP'
AND BS.BATCH_SOURCE_ID(+) = CT.BATCH_SOURCE_ID
AND PS_INV.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND PS_INV.CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
AND CUST.PARTY_ID = PARTY.PARTY_ID
AND PS_INV.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+)
AND CI.CONS_INV_ID(+) = PS_INV.CONS_INV_ID
AND PS_INV.TERM_ID = T.TERM_ID(+)
AND PS_INV.CASH_RECEIPT_ID = APP.CASH_RECEIPT_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)
, PS_INV.ROWID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.CASH_RECEIPT_ID
, -1)
, PS_INV.CUSTOMER_TRX_ID
, PS_INV.LAST_UPDATE_DATE
, PS_INV.LAST_UPDATED_BY
, PS_INV.CREATION_DATE
, PS_INV.CREATED_BY
, PS_INV.LAST_UPDATE_LOGIN
, PS_INV.PROGRAM_APPLICATION_ID
, PS_INV.PROGRAM_ID
, PS_INV.PROGRAM_UPDATE_DATE
, PS_INV.REQUEST_ID
, DECODE(PS_INV.CLASS
, 'PMT'
, PS_INV.TRX_NUMBER
, NULL)
, PS_INV.CUSTOMER_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST.ACCOUNT_NUMBER
, PS_INV.TRX_NUMBER
, PS_INV.TERMS_SEQUENCE_NUMBER
, PS_INV.DISCOUNT_TAKEN_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED
, PS_INV.DISCOUNT_TAKEN_EARNED
, PS_INV.DISCOUNT_TAKEN_UNEARNED
, PS_INV.AMOUNT_DUE_REMAINING
, PS_INV.DUE_DATE
, PS_INV.STATUS
, PS_INV.TERM_ID
, PS_INV.CLASS
, CTT.NAME
, CTT.CUST_TRX_TYPE_ID
, CT.TRX_DATE
, SU.LOCATION
, PS_INV.CUSTOMER_SITE_USE_ID
, GREATEST(PS_INV.TRX_DATE
, ARP_VIEW_CONSTANTS.GET_APPLY_DATE) /* BUG FIX 3400584*/ /*3079614*/
, ARP_VIEW_CONSTANTS.GET_DEFAULT_GL_DATE( GREATEST(PS_INV.GL_DATE
, ARP_VIEW_CONSTANTS.GET_RECEIPT_GL_DATE
, DECODE(NVL(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT')
, 'INV_REC_DT')
, 'INV_REC_SYS_DT'
, SYSDATE
, 'INV_REC_DT'
, PS_INV.GL_DATE)))
, PS_INV.EXCHANGE_RATE
, PS_INV.INVOICE_CURRENCY_CODE
, PS_INV.AMOUNT_DUE_ORIGINAL
, PS_INV.AMOUNT_IN_DISPUTE
, PS_INV.AMOUNT_LINE_ITEMS_ORIGINAL
, PS_INV.ACCTD_AMOUNT_DUE_REMAINING
, PS_INV.DISCOUNT_REMAINING
, T.CALC_DISCOUNT_ON_LINES_FLAG
, T.PARTIAL_DISCOUNT_FLAG
, CTT.ALLOW_OVERAPPLICATION_FLAG
, CTT.NATURAL_APPLICATION_ONLY_FLAG
, CTT.CREATION_SIGN
, PS_INV.PAYMENT_SCHEDULE_ID
, CT.DEFAULT_USSGL_TRANSACTION_CODE
, CT.DEFAULT_USSGL_TRX_CODE_CONTEXT
, CT.PURCHASE_ORDER
, CT.DOC_SEQUENCE_ID
, CT.DOC_SEQUENCE_VALUE
, BS.NAME
, PS_INV.AMOUNT_ADJUSTED
, PS_INV.AMOUNT_ADJUSTED_PENDING
, PS_INV.AMOUNT_LINE_ITEMS_REMAINING
, PS_INV.FREIGHT_ORIGINAL
, PS_INV.FREIGHT_REMAINING
, PS_INV.RECEIVABLES_CHARGES_REMAINING
, PS_INV.TAX_ORIGINAL
, PS_INV.TAX_REMAINING
, PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, TO_NUMBER(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.SECONDARY_APPLICATION_REF_ID))
, CI.CONS_BILLING_NUMBER
, CTT.ATTRIBUTE10
, CT.REASON_CODE
, CT.CT_REFERENCE
, CT.CUSTOMER_REFERENCE
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_REASON)
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.APPLICATION_REF_NUM)
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REFERENCE)
, DECODE (APP.APPLIED_PAYMENT_SCHEDULE_ID
, NULL
, NULL
, APP.CUSTOMER_REASON) HAVING DECODE(PS_INV.CLASS
, 'PMT'
, (SUM(APP.AMOUNT_APPLIED) * -1)
, PS_INV.AMOUNT_DUE_REMAINING) <> 0

Columns