VIEW: APPS.AR_APP_ADJ_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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