VIEW: APPS.IEX_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) , ps.invoice_currency_code , app.status ,- app.amount_applied , ps1.invoice_currency_code , 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 ,ct.trx_number ,arpt_sql_func_util.get_lookup_meaning ('PAYMENT_TYPE' ,app.status) FROM ra_customer_trx ct ,ra_customer_trx_lines ctl ,ra_cust_trx_types ctt ,ar_receipt_methods rm ,ar_cash_receipts cr ,ar_payment_schedules ps ,ar_payment_schedules ps1 ,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.APPLIED_PAYMENT_SCHEDULE_ID = ps1.PAYMENT_SCHEDULE_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) ,ps.invoice_currency_code ,app.status ,app.amount_applied ,ps1.invoice_currency_code ,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 ,ct.trx_number ,arpt_sql_func_util.get_lookup_meaning ('PAYMENT_TYPE' ,app.status) FROM ra_customer_trx ct ,ra_customer_trx_lines rtl ,ra_cust_trx_types ctt ,ar_payment_schedules ps ,ar_payment_schedules ps1 ,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.customer_trx_id = ct.customer_trx_id (+) AND app.payment_schedule_id = ps1.payment_schedule_id(+) UNION ALL SELECT app.rowid ,app.payment_schedule_id ,psa.trx_number ,decode (sign (ps.payment_schedule_id) ,- 1 ,arpt_sql_func_util.get_lookup_meaning ('PAYMENT_TYPE' ,app.status) ,NULL) ,to_char (NULL) ,app.apply_date ,nvl (app.amount_applied_from ,app.amount_applied) ,ps.invoice_currency_code ,app.status ,app.amount_applied ,psa.invoice_currency_code ,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 ,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 ,psa.trx_number ,arpt_sql_func_util.get_lookup_meaning ('PAYMENT_TYPE' ,app.status) FROM ar_payment_schedules ps ,ar_payment_schedules psa ,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 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 ,ct.invoice_currency_code ,to_char (NULL) status ,to_number (NULL) ,to_char (NULL) ,ctl.line_number ,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 ,ct.trx_number ,arpt_sql_func_util.get_lookup_meaning ('APPROVAL_TYPE' ,adj.status) FROM ar_adjustments adj ,ar_receivables_trx rt ,ra_customer_trx ct ,ra_customer_trx_lines ctl ,ar_payment_schedules_all ps 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.customer_trx_line_id = ctl.customer_trx_line_id (+) and adj.PAYMENT_SCHEDULE_ID=ps.PAYMENT_SCHEDULE_ID UNION ALL SELECT app.rowid ,app.payment_schedule_id ,NULL trx_rec_number ,arpt_sql_func_util.get_lookup_meaning ('RECEIVABLES_TRX' ,rt.type) ,NULL ,app.apply_date ,nvl (app.amount_applied_from ,app.amount_applied) ,ps.invoice_currency_code ,app.status ,app.amount_applied ,ps1.invoice_currency_code ,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 ,to_char (NULL) ,arpt_sql_func_util.get_lookup_meaning ('PAYMENT_TYPE' ,app.status) FROM ar_payment_schedules ps , ar_payment_schedules ps1 ,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 app.applied_payment_schedule_id = ps1.payment_schedule_id(+) AND sign (app.applied_payment_schedule_id) < 0 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) ,ps.invoice_currency_code ,app.status ,app.amount_applied ,ps_dummy.invoice_currency_code ,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 ,to_char (NULL) ,arpt_sql_func_util.get_lookup_meaning ('PAYMENT_TYPE' ,app.status) FROM ar_payment_schedules ps ,ar_payment_schedules ps_dummy ,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

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)
, PS.INVOICE_CURRENCY_CODE
, APP.STATUS
, - APP.AMOUNT_APPLIED
, PS1.INVOICE_CURRENCY_CODE
, 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
, CT.TRX_NUMBER
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('PAYMENT_TYPE'
, APP.STATUS)
FROM RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX_LINES CTL
, RA_CUST_TRX_TYPES CTT
, AR_RECEIPT_METHODS RM
, AR_CASH_RECEIPTS CR
, AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PS1
, 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.APPLIED_PAYMENT_SCHEDULE_ID = PS1.PAYMENT_SCHEDULE_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)
, PS.INVOICE_CURRENCY_CODE
, APP.STATUS
, APP.AMOUNT_APPLIED
, PS1.INVOICE_CURRENCY_CODE
, 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
, CT.TRX_NUMBER
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('PAYMENT_TYPE'
, APP.STATUS)
FROM RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX_LINES RTL
, RA_CUST_TRX_TYPES CTT
, AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PS1
, 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.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID (+)
AND APP.PAYMENT_SCHEDULE_ID = PS1.PAYMENT_SCHEDULE_ID(+) UNION ALL SELECT APP.ROWID
, APP.PAYMENT_SCHEDULE_ID
, PSA.TRX_NUMBER
, DECODE (SIGN (PS.PAYMENT_SCHEDULE_ID)
, - 1
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('PAYMENT_TYPE'
, APP.STATUS)
, NULL)
, TO_CHAR (NULL)
, APP.APPLY_DATE
, NVL (APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, PS.INVOICE_CURRENCY_CODE
, APP.STATUS
, APP.AMOUNT_APPLIED
, PSA.INVOICE_CURRENCY_CODE
, 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
, 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
, PSA.TRX_NUMBER
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('PAYMENT_TYPE'
, APP.STATUS)
FROM AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PSA
, 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 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
, CT.INVOICE_CURRENCY_CODE
, TO_CHAR (NULL) STATUS
, TO_NUMBER (NULL)
, TO_CHAR (NULL)
, CTL.LINE_NUMBER
, 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
, CT.TRX_NUMBER
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('APPROVAL_TYPE'
, ADJ.STATUS)
FROM AR_ADJUSTMENTS ADJ
, AR_RECEIVABLES_TRX RT
, RA_CUSTOMER_TRX CT
, RA_CUSTOMER_TRX_LINES CTL
, AR_PAYMENT_SCHEDULES_ALL PS
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.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID (+)
AND ADJ.PAYMENT_SCHEDULE_ID=PS.PAYMENT_SCHEDULE_ID UNION ALL SELECT APP.ROWID
, APP.PAYMENT_SCHEDULE_ID
, NULL TRX_REC_NUMBER
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('RECEIVABLES_TRX'
, RT.TYPE)
, NULL
, APP.APPLY_DATE
, NVL (APP.AMOUNT_APPLIED_FROM
, APP.AMOUNT_APPLIED)
, PS.INVOICE_CURRENCY_CODE
, APP.STATUS
, APP.AMOUNT_APPLIED
, PS1.INVOICE_CURRENCY_CODE
, 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
, TO_CHAR (NULL)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('PAYMENT_TYPE'
, APP.STATUS)
FROM AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PS1
, 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 APP.APPLIED_PAYMENT_SCHEDULE_ID = PS1.PAYMENT_SCHEDULE_ID(+)
AND SIGN (APP.APPLIED_PAYMENT_SCHEDULE_ID) < 0 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)
, PS.INVOICE_CURRENCY_CODE
, APP.STATUS
, APP.AMOUNT_APPLIED
, PS_DUMMY.INVOICE_CURRENCY_CODE
, 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
, TO_CHAR (NULL)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('PAYMENT_TYPE'
, APP.STATUS)
FROM AR_PAYMENT_SCHEDULES PS
, AR_PAYMENT_SCHEDULES PS_DUMMY
, 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