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), app.status, 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, 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_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' 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, 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, 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_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.payment_schedule_id = ct.customer_trx_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), app.status, ps.invoice_currency_code, 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, 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, to_char(NULL) 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, 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), 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 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(+) 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), 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, to_char(NULL), arpt_sql_func_util.get_lookup_meaning('PAYMENT_TYPE', app.status) FROM ar_payment_schedules ps, 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 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, ps.invoice_currency_code, 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, 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)
, APP.STATUS
, 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
, 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_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' 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
, 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
, 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_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.PAYMENT_SCHEDULE_ID = CT.CUSTOMER_TRX_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)
, APP.STATUS
, PS.INVOICE_CURRENCY_CODE
, 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
, 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
, TO_CHAR(NULL) 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
, 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)
, 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
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(+) 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)
, 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
, TO_CHAR(NULL)
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('PAYMENT_TYPE'
, APP.STATUS)
FROM AR_PAYMENT_SCHEDULES PS
, 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 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
, PS.INVOICE_CURRENCY_CODE
, 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
, 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