VIEW: APPS.IEX_PROFILE_DELINQUENCIES_V

Source

View Text - Preformatted

SELECT hzca.party_id party_cust_id, hzca.cust_account_id cust_account_id, hzca.account_number account_number, rct.customer_trx_id customer_trx_id, rct.trx_number invoice_number, aps.payment_schedule_id payment_schedule_id, aps.due_date due_date, aps.amount_due_remaining amount_due, aps.invoice_currency_code amount_due_cc, aps.invoice_currency_code currency, aps.amount_due_original original_amount_due, aps.invoice_currency_code original_amount_due_cc, (SELECT Nvl(sum(rcr.total_amount), 0) FROM ra_cm_requests rcr WHERE rcr.customer_trx_id = aps.customer_trx_id and rcr.status <> 'NOT_APPROVED') amount_in_dispute, aps.invoice_currency_code amount_in_dispute_cc, decode(acr.cash_receipt_id, null, null, ara.amount_applied) last_payment_amount, decode(acr.cash_receipt_id, null, null, aps.invoice_currency_code) last_payment_amount_cc, acr.receipt_date last_payment_date, ara.receivable_application_id receivable_application_id, aps.customer_site_use_id customer_site_use_id, id.delinquency_id delinquency_id, su.party_site_number party_site_number, su.location location, su.address address, aps.org_id org_id, op_unit.name operating_name, cons.cons_billing_number cons_billing_number FROM iex_delinquencies id , ra_customer_trx rct , ar_Receivable_applications ara, ar_cash_receipts acr, ar_payment_schedules aps_r, ar_cash_receipt_history acrh, ar_payment_schedules aps, hz_cust_accounts hzca, iex_cust_site_uses_v su, hr_operating_units op_unit, ar_cons_inv cons WHERE id.payment_schedule_id = aps.payment_schedule_id AND id.payment_schedule_id+0 = aps.payment_schedule_id AND ara.payment_schedule_id = aps_r.payment_schedule_id(+) AND nvl(aps_r.class(+), 'PMT') = 'PMT' AND id.status IN ('DELINQUENT', 'PREDELINQUENT') AND aps.customer_site_use_id = su.customer_site_use_id AND aps.customer_trx_id = rct.customer_trx_id AND ara.applied_payment_schedule_id(+) = aps.payment_schedule_id AND ara.cash_receipt_id = acr.cash_receipt_id(+) AND acr.cash_receipt_id = acrh.cash_receipt_id(+) AND nvl(acr.confirmed_flag, 'Y') = 'Y' AND acr.reversal_date is null AND acrh.status(+) not in (decode (acrh.factor_flag(+), 'Y', 'RISK_ELIMINATED', 'N', ' '), 'REVERSED') AND acrh.current_record_flag(+) = 'Y' AND nvl(acr.receipt_date, sysdate) = (select nvl(MAX(b.receipt_date), sysdate) from ar_receivable_applications a, ar_cash_receipts b, ar_cash_receipt_history c, ar_payment_schedules d where a.applied_payment_schedule_id = aps.payment_schedule_id and d.payment_schedule_id = a.payment_schedule_id and d.class = 'PMT' and a.status = 'APP' and a.cash_receipt_id = b.cash_receipt_id and b.reversal_date is null and nvl(b.confirmed_flag, 'Y') = 'Y' and b.cash_receipt_id = c.cash_receipt_id and c.status not in (decode (C.factor_flag, 'Y', 'RISK_ELIMINATED', 'N', ' '), 'REVERSED') ) AND aps.customer_id = hzca.cust_account_id AND aps.customer_id+0 = hzca.cust_account_id AND aps.org_id = op_unit.organization_id(+) AND aps.cons_inv_id = cons.cons_inv_id(+)

View Text - HTML Formatted

SELECT HZCA.PARTY_ID PARTY_CUST_ID
, HZCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, RCT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, RCT.TRX_NUMBER INVOICE_NUMBER
, APS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, APS.DUE_DATE DUE_DATE
, APS.AMOUNT_DUE_REMAINING AMOUNT_DUE
, APS.INVOICE_CURRENCY_CODE AMOUNT_DUE_CC
, APS.INVOICE_CURRENCY_CODE CURRENCY
, APS.AMOUNT_DUE_ORIGINAL ORIGINAL_AMOUNT_DUE
, APS.INVOICE_CURRENCY_CODE ORIGINAL_AMOUNT_DUE_CC
, (SELECT NVL(SUM(RCR.TOTAL_AMOUNT)
, 0)
FROM RA_CM_REQUESTS RCR
WHERE RCR.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RCR.STATUS <> 'NOT_APPROVED') AMOUNT_IN_DISPUTE
, APS.INVOICE_CURRENCY_CODE AMOUNT_IN_DISPUTE_CC
, DECODE(ACR.CASH_RECEIPT_ID
, NULL
, NULL
, ARA.AMOUNT_APPLIED) LAST_PAYMENT_AMOUNT
, DECODE(ACR.CASH_RECEIPT_ID
, NULL
, NULL
, APS.INVOICE_CURRENCY_CODE) LAST_PAYMENT_AMOUNT_CC
, ACR.RECEIPT_DATE LAST_PAYMENT_DATE
, ARA.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID
, APS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, ID.DELINQUENCY_ID DELINQUENCY_ID
, SU.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, SU.LOCATION LOCATION
, SU.ADDRESS ADDRESS
, APS.ORG_ID ORG_ID
, OP_UNIT.NAME OPERATING_NAME
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
FROM IEX_DELINQUENCIES ID
, RA_CUSTOMER_TRX RCT
, AR_RECEIVABLE_APPLICATIONS ARA
, AR_CASH_RECEIPTS ACR
, AR_PAYMENT_SCHEDULES APS_R
, AR_CASH_RECEIPT_HISTORY ACRH
, AR_PAYMENT_SCHEDULES APS
, HZ_CUST_ACCOUNTS HZCA
, IEX_CUST_SITE_USES_V SU
, HR_OPERATING_UNITS OP_UNIT
, AR_CONS_INV CONS
WHERE ID.PAYMENT_SCHEDULE_ID = APS.PAYMENT_SCHEDULE_ID
AND ID.PAYMENT_SCHEDULE_ID+0 = APS.PAYMENT_SCHEDULE_ID
AND ARA.PAYMENT_SCHEDULE_ID = APS_R.PAYMENT_SCHEDULE_ID(+)
AND NVL(APS_R.CLASS(+)
, 'PMT') = 'PMT'
AND ID.STATUS IN ('DELINQUENT'
, 'PREDELINQUENT')
AND APS.CUSTOMER_SITE_USE_ID = SU.CUSTOMER_SITE_USE_ID
AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND ARA.APPLIED_PAYMENT_SCHEDULE_ID(+) = APS.PAYMENT_SCHEDULE_ID
AND ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID(+)
AND ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID(+)
AND NVL(ACR.CONFIRMED_FLAG
, 'Y') = 'Y'
AND ACR.REVERSAL_DATE IS NULL
AND ACRH.STATUS(+) NOT IN (DECODE (ACRH.FACTOR_FLAG(+)
, 'Y'
, 'RISK_ELIMINATED'
, 'N'
, ' ')
, 'REVERSED')
AND ACRH.CURRENT_RECORD_FLAG(+) = 'Y'
AND NVL(ACR.RECEIPT_DATE
, SYSDATE) = (SELECT NVL(MAX(B.RECEIPT_DATE)
, SYSDATE)
FROM AR_RECEIVABLE_APPLICATIONS A
, AR_CASH_RECEIPTS B
, AR_CASH_RECEIPT_HISTORY C
, AR_PAYMENT_SCHEDULES D
WHERE A.APPLIED_PAYMENT_SCHEDULE_ID = APS.PAYMENT_SCHEDULE_ID
AND D.PAYMENT_SCHEDULE_ID = A.PAYMENT_SCHEDULE_ID
AND D.CLASS = 'PMT'
AND A.STATUS = 'APP'
AND A.CASH_RECEIPT_ID = B.CASH_RECEIPT_ID
AND B.REVERSAL_DATE IS NULL
AND NVL(B.CONFIRMED_FLAG
, 'Y') = 'Y'
AND B.CASH_RECEIPT_ID = C.CASH_RECEIPT_ID
AND C.STATUS NOT IN (DECODE (C.FACTOR_FLAG
, 'Y'
, 'RISK_ELIMINATED'
, 'N'
, ' ')
, 'REVERSED') )
AND APS.CUSTOMER_ID = HZCA.CUST_ACCOUNT_ID
AND APS.CUSTOMER_ID+0 = HZCA.CUST_ACCOUNT_ID
AND APS.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND APS.CONS_INV_ID = CONS.CONS_INV_ID(+)