VIEW: APPS.IEX_RECEIVABLE_APPLICATIONS_V

Source

View Text - Preformatted

SELECT 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.APPLICATION_REF_TYPE,'CLAIM',ARP_DEDUCTION.GET_TM_ORACLE_REASON(APP.secondary_application_ref_id),NULL) APPLICATION_REF_REASON_MEANING , commented by varangan for bug#4079414*/ APP.CUSTOMER_REFERENCE , /*APP.CUSTOMER_REASON , commented by varangan for bug#4079414*/ /*APP.APPLIED_REC_APP_ID , commented by varangan for bug#4079414*/ /*APP.SECONDARY_APPLICATION_REF_TYPE SECONDARY_APPLICATION_REF_TYPE , commented by varangan for bug#4079414*/ /*APP.SECONDARY_APPLICATION_REF_NUM SECONDARY_APPLICATION_REF_NUM , commented by varangan for bug#4079414*/ PS_INV.ORG_ID ORG_ID , OP_UNIT.NAME OPERATING_UNIT 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 , HR_OPERATING_UNITS OP_UNIT 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 CTT.ORG_ID(+)=PS_INV.ORG_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 AND OP_UNIT.ORGANIZATION_ID(+) = PS_INV.ORG_ID

View Text - HTML Formatted

SELECT 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.APPLICATION_REF_TYPE
, 'CLAIM'
, ARP_DEDUCTION.GET_TM_ORACLE_REASON(APP.SECONDARY_APPLICATION_REF_ID)
, NULL) APPLICATION_REF_REASON_MEANING
, COMMENTED BY VARANGAN FOR BUG#4079414*/ APP.CUSTOMER_REFERENCE
, /*APP.CUSTOMER_REASON
, COMMENTED BY VARANGAN FOR BUG#4079414*/ /*APP.APPLIED_REC_APP_ID
, COMMENTED BY VARANGAN FOR BUG#4079414*/ /*APP.SECONDARY_APPLICATION_REF_TYPE SECONDARY_APPLICATION_REF_TYPE
, COMMENTED BY VARANGAN FOR BUG#4079414*/ /*APP.SECONDARY_APPLICATION_REF_NUM SECONDARY_APPLICATION_REF_NUM
, COMMENTED BY VARANGAN FOR BUG#4079414*/ PS_INV.ORG_ID ORG_ID
, OP_UNIT.NAME OPERATING_UNIT
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
, HR_OPERATING_UNITS OP_UNIT
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 CTT.ORG_ID(+)=PS_INV.ORG_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
AND OP_UNIT.ORGANIZATION_ID(+) = PS_INV.ORG_ID