APPS.AR_APP_ADJ_V
[Home] [Help]
| Product: | AR - Receivables |
|---|---|
| Description: | (Release 11.5 Only) |
| Implementation/DBA Data: |
APPS.AR_APP_ADJ_V
|
SELECT APP.ROWID
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER
, DECODE(APP.APPLICATION_TYPE
, 'CM'
, CTT.NAME
, RM.NAME)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, APP.APPLY_DATE
, -NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, APP.STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS) /* BUG 2758730: CURRENCY IS SELECTED
FROM PS INSTEAD OF CR */
, PS.INVOICE_CURRENCY_CODE
, -APP.AMOUNT_APPLIED
, CTL.LINE_NUMBER
, APP.GL_DATE
, -(NVL(APP.EARNED_DISCOUNT_TAKEN
, 0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
, 0))
, -( APP.AMOUNT_APPLIED + NVL(APP.EARNED_DISCOUNT_TAKEN
, 0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
, 0) )
, APP.CASH_RECEIPT_ID
, APP.CUSTOMER_TRX_ID
, TO_NUMBER(NULL)
, APP.ATTRIBUTE_CATEGORY
, APP.ATTRIBUTE1
, APP.ATTRIBUTE2
, APP.ATTRIBUTE3
, APP.ATTRIBUTE4
, APP.ATTRIBUTE5
, APP.ATTRIBUTE6
, APP.ATTRIBUTE7
, APP.ATTRIBUTE8
, APP.ATTRIBUTE9
, APP.ATTRIBUTE10
, APP.ATTRIBUTE11
, APP.ATTRIBUTE12
, APP.ATTRIBUTE13
, APP.ATTRIBUTE14
, APP.ATTRIBUTE15
, APP.LAST_UPDATED_BY
, APP.LAST_UPDATE_DATE
, APP.LAST_UPDATE_LOGIN
, APP.CREATED_BY
, APP.CREATION_DATE
, CT.REASON_CODE
, NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
, CT.REASON_CODE)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON'
, CT.REASON_CODE))
, DECODE(APP.APPLICATION_TYPE
, 'CASH'
, PS.DUE_DATE
, NULL)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('RECEIPT_CREATION_STATUS'
, CRH.STATUS)
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, -APP.LINE_APPLIED
, -APP.TAX_APPLIED
, -APP.FREIGHT_APPLIED
, -APP.RECEIVABLES_CHARGES_APPLIED
, 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_NUM
, 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
, APP.CUSTOMER_REFERENCE
, APP.CUSTOMER_REASON
, APP.COMMENTS
, NULL /* REC_ACTIVITY_NAME */
, CT.PURCHASE_ORDER
, APP.SECONDARY_APPLICATION_REF_NUM
, APP.SECONDARY_APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE'
, APP.SECONDARY_APPLICATION_REF_TYPE) SECONDARY_APP_REF_TYPE_MEANING
, FU.USER_NAME CREATED_BY_NAME
FROM RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX_LINES CTL
, RA_CUST_TRX_TYPES CTT
, AR_RECEIPT_METHODS RM
, FND_USER FU
, AR_CASH_RECEIPTS CR
, AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS APP
, AR_CASH_RECEIPT_HISTORY CRH
WHERE APP.STATUS = 'APP'
AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+)
AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID(+)
AND APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND APP.APPLIED_CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND APP.APPLIED_CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID(+)
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID(+)
AND CRH.CURRENT_RECORD_FLAG(+) = 'Y'
AND APP.CREATED_BY = FU.USER_ID UNION ALL SELECT APP.ROWID
, APP.PAYMENT_SCHEDULE_ID
, DECODE ( SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, PS.TRX_NUMBER )
, CTT.NAME
, DECODE ( SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, NULL
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS))
, APP.APPLY_DATE
, NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, APP.STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, PS.INVOICE_CURRENCY_CODE
, APP.AMOUNT_APPLIED
, RTL.LINE_NUMBER
, APP.GL_DATE
, DECODE (SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, TO_NUMBER(NULL)
, NVL(APP.EARNED_DISCOUNT_TAKEN
, 0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
, 0))
, DECODE ( SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, TO_NUMBER(NULL)
, APP.AMOUNT_APPLIED )
, TO_NUMBER(NULL)
, APP.APPLIED_CUSTOMER_TRX_ID
, TO_NUMBER(NULL)
, APP.ATTRIBUTE_CATEGORY
, APP.ATTRIBUTE1
, APP.ATTRIBUTE2
, APP.ATTRIBUTE3
, APP.ATTRIBUTE4
, APP.ATTRIBUTE5
, APP.ATTRIBUTE6
, APP.ATTRIBUTE7
, APP.ATTRIBUTE8
, APP.ATTRIBUTE9
, APP.ATTRIBUTE10
, APP.ATTRIBUTE11
, APP.ATTRIBUTE12
, APP.ATTRIBUTE13
, APP.ATTRIBUTE14
, APP.ATTRIBUTE15
, APP.LAST_UPDATED_BY
, APP.LAST_UPDATE_DATE
, APP.LAST_UPDATE_LOGIN
, APP.CREATED_BY
, APP.CREATION_DATE
, CT.REASON_CODE
, NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
, CT.REASON_CODE)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON'
, CT.REASON_CODE))
, TO_DATE(NULL)
, TO_CHAR(NULL)
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, APP.LINE_APPLIED
, APP.TAX_APPLIED
, APP.FREIGHT_APPLIED
, APP.RECEIVABLES_CHARGES_APPLIED
, 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_NUM
, 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
, APP.CUSTOMER_REFERENCE
, APP.CUSTOMER_REASON
, APP.COMMENTS
, NULL /* REC_ACTIVITY_NAME */
, CT.PURCHASE_ORDER
, APP.SECONDARY_APPLICATION_REF_NUM
, APP.SECONDARY_APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE'
, APP.SECONDARY_APPLICATION_REF_TYPE) SECONDARY_APP_REF_TYPE_MEANING
, FU.USER_NAME CREATED_BY_NAME
FROM RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX_LINES RTL
, RA_CUST_TRX_TYPES CTT
, FND_USER FU
, AR_PAYMENT_SCHEDULES PS
, AR_RECEIVABLE_APPLICATIONS APP
WHERE APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
AND APP.APPLIED_CUSTOMER_TRX_LINE_ID = RTL.CUSTOMER_TRX_LINE_ID(+)
AND APP.STATUS NOT IN ('ACC'
, 'ACTIVITY'
, 'OTHER ACC')
AND APP.APPLIED_CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+) /* DATA FOR ON-ACCOUNT
AND CLAIM INVESTIGION LINES */
AND APP.CREATED_BY = FU.USER_ID UNION ALL SELECT APP.ROWID
, APP.PAYMENT_SCHEDULE_ID
, DECODE ( SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, NULL)
, /*PS.TRX_NUMBER )
, */ PSA.TRX_NUMBER
, NULL
, APP.APPLY_DATE
, NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, APP.STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, PS.INVOICE_CURRENCY_CODE
, /* ADDED FOR DEDUCTIONS PHASE III*/ APP.AMOUNT_APPLIED
, TO_NUMBER(NULL)
, APP.GL_DATE
, TO_NUMBER(NULL)
, DECODE ( SIGN(PS.PAYMENT_SCHEDULE_ID)
, -1
, TO_NUMBER(NULL)
, APP.AMOUNT_APPLIED )
, APP.CASH_RECEIPT_ID
, APP.APPLIED_CUSTOMER_TRX_ID
, TO_NUMBER(NULL)
, APP.ATTRIBUTE_CATEGORY
, APP.ATTRIBUTE1
, APP.ATTRIBUTE2
, APP.ATTRIBUTE3
, APP.ATTRIBUTE4
, APP.ATTRIBUTE5
, APP.ATTRIBUTE6
, APP.ATTRIBUTE7
, APP.ATTRIBUTE8
, APP.ATTRIBUTE9
, APP.ATTRIBUTE10
, APP.ATTRIBUTE11
, APP.ATTRIBUTE12
, APP.ATTRIBUTE13
, APP.ATTRIBUTE14
, APP.ATTRIBUTE15
, APP.LAST_UPDATED_BY
, APP.LAST_UPDATE_DATE
, APP.LAST_UPDATE_LOGIN
, APP.CREATED_BY
, APP.CREATION_DATE
, NULL
, NULL
, TO_DATE(NULL)
, TO_CHAR(NULL)
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, APP.LINE_APPLIED
, APP.TAX_APPLIED
, APP.FREIGHT_APPLIED
, APP.RECEIVABLES_CHARGES_APPLIED
, 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_NUM
, 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
, APP.CUSTOMER_REFERENCE
, APP.CUSTOMER_REASON
, APP.COMMENTS
, DECODE (SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, ART.NAME
, NULL) REC_ACTIVITY_NAME
, NULL PURCHASE_ORDER
, APP.SECONDARY_APPLICATION_REF_NUM
, APP.SECONDARY_APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE'
, APP.SECONDARY_APPLICATION_REF_TYPE) SECONDARY_APP_REF_TYPE_MEANING
, FU.USER_NAME CREATED_BY_NAME
FROM AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PSA
, FND_USER FU
, AR_RECEIVABLE_APPLICATIONS APP
, AR_RECEIVABLES_TRX ART
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID
AND APP.STATUS IN ('ACC'
, 'OTHER ACC')
AND ART.RECEIVABLES_TRX_ID(+) = APP.RECEIVABLES_TRX_ID
AND APP.CREATED_BY = FU.USER_ID UNION ALL SELECT ADJ.ROWID
, ADJ.PAYMENT_SCHEDULE_ID
, DECODE(ADJ.RECEIVABLES_TRX_ID
, -15
, (SELECT TRX.TRX_NUMBER
FROM RA_CUSTOMER_TRX TRX
, RA_CUSTOMER_TRX_LINES LINES
WHERE TRX.CUSTOMER_TRX_ID = LINES.CUSTOMER_TRX_ID
AND LINES.BR_REF_CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND LINES.BR_ADJUSTMENT_ID = ADJ.ADJUSTMENT_ID)
, ADJ.ADJUSTMENT_NUMBER)
, DECODE(NVL(RT.TYPE
, 'ADJUST')
, 'ENDORSEMENT'
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('RECEIVABLES_TRX'
, RT.TYPE)
, DECODE (ADJ.RECEIVABLES_TRX_ID
, -15
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('MISC_PHRASES'
, DECODE (ADJ.RECEIVABLES_TRX_ID
, -15
, 'ASSIGNMENT'
, 'ADJUSTMENT'))
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ADJUSTMENT_TYPE'
, ADJ.TYPE)))
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('MISC_PHRASES'
, DECODE (ADJ.RECEIVABLES_TRX_ID
, -15
, 'ASSIGNMENT'
, 'ADJUSTMENT'))
, ADJ.APPLY_DATE
, ADJ.AMOUNT
, TO_CHAR(NULL) STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPROVAL_TYPE'
, ADJ.STATUS)
, CT.INVOICE_CURRENCY_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ADJ.GL_DATE
, TO_NUMBER(NULL)
, ADJ.AMOUNT
, TO_NUMBER(NULL)
, ADJ.CUSTOMER_TRX_ID
, ADJ.ADJUSTMENT_ID
, ADJ.ATTRIBUTE_CATEGORY
, ADJ.ATTRIBUTE1
, ADJ.ATTRIBUTE2
, ADJ.ATTRIBUTE3
, ADJ.ATTRIBUTE4
, ADJ.ATTRIBUTE5
, ADJ.ATTRIBUTE6
, ADJ.ATTRIBUTE7
, ADJ.ATTRIBUTE8
, ADJ.ATTRIBUTE9
, ADJ.ATTRIBUTE10
, ADJ.ATTRIBUTE11
, ADJ.ATTRIBUTE12
, ADJ.ATTRIBUTE13
, ADJ.ATTRIBUTE14
, ADJ.ATTRIBUTE15
, ADJ.LAST_UPDATED_BY
, ADJ.LAST_UPDATE_DATE
, ADJ.LAST_UPDATE_LOGIN
, ADJ.CREATED_BY
, ADJ.CREATION_DATE
, ADJ.REASON_CODE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ADJUST_REASON'
, ADJ.REASON_CODE)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, ADJ.LINE_ADJUSTED
, ADJ.TAX_ADJUSTED
, ADJ.FREIGHT_ADJUSTED
, ADJ.RECEIVABLES_CHARGES_ADJUSTED
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CT.PURCHASE_ORDER
, NULL
, NULL
, NULL
, FU.USER_NAME CREATED_BY_NAME
FROM AR_ADJUSTMENTS ADJ
, FND_USER FU
, AR_RECEIVABLES_TRX RT
, RA_CUSTOMER_TRX CT
WHERE ADJ.STATUS NOT IN ('R'
, 'U')
AND ADJ.RECEIVABLES_TRX_ID = RT.RECEIVABLES_TRX_ID(+)
AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID(+)
AND ADJ.CREATED_BY = FU.USER_ID UNION ALL SELECT APP.ROWID
, APP.PAYMENT_SCHEDULE_ID
, NULL TRX_REC_NUMBER
, /* DO NOT WANT TO SEE NUMBER FOR -PS_ID */ ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('RECEIVABLES_TRX'
, RT.TYPE)
, NULL
, /*ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, */ APP.APPLY_DATE
, NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, APP.STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, PS.INVOICE_CURRENCY_CODE
, APP.AMOUNT_APPLIED
, TO_NUMBER(NULL)
, APP.GL_DATE
, TO_NUMBER(NULL)
, APP.AMOUNT_APPLIED
, APP.CASH_RECEIPT_ID
, APP.CUSTOMER_TRX_ID
, TO_NUMBER(NULL)
, APP.ATTRIBUTE_CATEGORY
, APP.ATTRIBUTE1
, APP.ATTRIBUTE2
, APP.ATTRIBUTE3
, APP.ATTRIBUTE4
, APP.ATTRIBUTE5
, APP.ATTRIBUTE6
, APP.ATTRIBUTE7
, APP.ATTRIBUTE8
, APP.ATTRIBUTE9
, APP.ATTRIBUTE10
, APP.ATTRIBUTE11
, APP.ATTRIBUTE12
, APP.ATTRIBUTE13
, APP.ATTRIBUTE14
, APP.ATTRIBUTE15
, APP.LAST_UPDATED_BY
, APP.LAST_UPDATE_DATE
, APP.LAST_UPDATE_LOGIN
, APP.CREATED_BY
, APP.CREATION_DATE
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_DATE(NULL)
, TO_CHAR(NULL)
, PS.PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, APP.LINE_APPLIED
, APP.TAX_APPLIED
, APP.FREIGHT_APPLIED
, APP.RECEIVABLES_CHARGES_APPLIED
, 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_NUM
, 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
, APP.CUSTOMER_REFERENCE
, APP.CUSTOMER_REASON
, APP.COMMENTS
, DECODE (SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, RT.NAME
, NULL) REC_ACTIVITY_NAME
, NULL PURCHASE_ORDER
, APP.SECONDARY_APPLICATION_REF_NUM
, APP.SECONDARY_APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE'
, APP.SECONDARY_APPLICATION_REF_TYPE) SECONDARY_APP_REF_TYPE_MEANING
, FU.USER_NAME CREATED_BY_NAME
FROM AR_PAYMENT_SCHEDULES PS
, FND_USER FU
, AR_RECEIVABLE_APPLICATIONS APP
, AR_RECEIVABLES_TRX RT
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.RECEIVABLES_TRX_ID = RT.RECEIVABLES_TRX_ID
AND APP.STATUS = 'ACTIVITY'
AND SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID) < 0
AND APP.CREATED_BY = FU.USER_ID UNION ALL SELECT APP.ROWID
, APP.PAYMENT_SCHEDULE_ID
, PS_DUMMY.TRX_NUMBER
, RT.NAME
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, APP.APPLY_DATE
, NVL(APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, APP.STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
, PS.INVOICE_CURRENCY_CODE
, /* ADDED FOR DEDUCTIONS PHASE III */ APP.AMOUNT_APPLIED
, TO_NUMBER(NULL)
, APP.GL_DATE
, TO_NUMBER(NULL)
, APP.AMOUNT_APPLIED
, PS_DUMMY.CASH_RECEIPT_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, APP.ATTRIBUTE_CATEGORY
, APP.ATTRIBUTE1
, APP.ATTRIBUTE2
, APP.ATTRIBUTE3
, APP.ATTRIBUTE4
, APP.ATTRIBUTE5
, APP.ATTRIBUTE6
, APP.ATTRIBUTE7
, APP.ATTRIBUTE8
, APP.ATTRIBUTE9
, APP.ATTRIBUTE10
, APP.ATTRIBUTE11
, APP.ATTRIBUTE12
, APP.ATTRIBUTE13
, APP.ATTRIBUTE14
, APP.ATTRIBUTE15
, APP.LAST_UPDATED_BY
, APP.LAST_UPDATE_DATE
, APP.LAST_UPDATE_LOGIN
, APP.CREATED_BY
, APP.CREATION_DATE
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, DECODE(APP.APPLICATION_TYPE
, 'CASH'
, PS_DUMMY.DUE_DATE
, TO_DATE(NULL) )
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'RECEIPT_CREATION_STATUS'
, CRH.STATUS)
, APP.APPLIED_PAYMENT_SCHEDULE_ID
, PS.TERMS_SEQUENCE_NUMBER
, PS.CLASS
, APP.LINE_APPLIED
, APP.TAX_APPLIED
, APP.FREIGHT_APPLIED
, APP.RECEIVABLES_CHARGES_APPLIED
, 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
, NULL
, /* APP.APPLICATION_REF_NUM */ 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
, APP.CUSTOMER_REFERENCE
, APP.CUSTOMER_REASON
, APP.COMMENTS
, RT.NAME REC_ACTIVITY_NAME
, NULL PURCHASE_ORDER
, APP.SECONDARY_APPLICATION_REF_NUM
, APP.SECONDARY_APPLICATION_REF_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPLICATION_REF_TYPE'
, APP.SECONDARY_APPLICATION_REF_TYPE) SECONDARY_APP_REF_TYPE_MEANING
, FU.USER_NAME CREATED_BY_NAME
FROM AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PS_DUMMY
, FND_USER FU
, AR_RECEIVABLE_APPLICATIONS APP
, AR_RECEIVABLES_TRX RT
, AR_CASH_RECEIPT_HISTORY CRH
WHERE APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_DUMMY.PAYMENT_SCHEDULE_ID
AND PS_DUMMY.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND APP.RECEIVABLES_TRX_ID = RT.RECEIVABLES_TRX_ID
AND APP.STATUS = 'ACTIVITY'
AND SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID) > 0
AND APP.CREATED_BY = FU.USER_ID