VIEW: APPS.IEX_LATER_STAGE_DELINQUENCY_V

Source

View Text - Preformatted

SELECT nvl(dl.party_cust_id, rps.party_id) party_id, NVL(dl.cust_account_id, rps.cust_account_id) cust_account_id, hca.account_number account_number, NVL(rtx.bill_to_site_use_id, to_number(rps.customer_site_use_id)) customer_site_use_id, su.location location, dl.delinquency_id delinquency_id, dl.transaction_id transaction_id, rtx.trx_number transaction_number, iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', 'REPOSSESSION') status, rps.disposition_code disposition_code, iex_utilities.get_lookup_meaning('IEX_REPO_DISPOSITION', rps.disposition_code) disposition, 'N/A' writeoff_reason_code, 'N/A' writeoff_reason, rps.repossession_id stage_id, cas.cas_id case_id, cas.case_number case_number, rps.repossession_date stage_date, rps.last_update_date last_update_date, rps.contract_id contract_id, rps.contract_number contract_number, rps.org_id org_id, rps.creation_date creation_date, op_unit.name operating_unit from iex_repossessions RPS, iex_case_objects cob,iex_cases cas,hr_operating_units op_unit,hz_cust_accounts hca, hz_cust_site_uses su, iex_delinquencies dl, ra_customer_trx rtx WHERE cob.object_id(+)=rps.contract_id AND cob.cas_id=cas.cas_id(+) AND rps.org_id = op_unit.organization_id(+) AND rps.cust_account_id = hca.cust_account_id (+) AND rps.customer_site_use_id = su.site_use_id(+) AND rtx.customer_trx_id(+) = dl.transaction_id AND dl.delinquency_id(+) = rps.delinquency_id AND rtx.customer_trx_id(+) = dl.transaction_id UNION ALL SELECT NVL(dl.party_cust_id, wrf.party_id) party_id, NVL(dl.cust_account_id, wrf.cust_account_id) cust_account_id, hca.account_number account_number, NVL(rtx.bill_to_site_use_id, to_number(wrf.customer_site_use_id)) customer_site_use_id, su.location location, dl.delinquency_id delinquency_id, dl.transaction_id transaction_id, rtx.trx_number transaction_number, iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', 'WRITEOFF') status, wrf.disposition_code disposition_code, iex_utilities.get_lookup_meaning('IEX_WRITEOFF_DISPOSITION_CODE', wrf.disposition_code) disposition, wrf.writeoff_reason writeoff_reason_code, alk.meaning writeoff_reason, wrf.writeoff_id stage_id, cas.cas_id case_id, cas.case_number case_number, wrf.writeoff_request_date stage_date, wrf.last_update_date last_update_date, wrf.contract_id contract_id, wrf.contract_number contract_number, wrf.org_id org_id, wrf.creation_date creation_date, op_unit.name operating_unit FROM iex_delinquencies dl, iex_writeoffs wrf, ra_customer_trx rtx, ar_lookups alk, hr_operating_units op_unit, hz_cust_accounts hca, hz_cust_site_uses su, iex_cases cas, iex_case_objects cob WHERE cob.object_id(+)=wrf.contract_id AND cob.cas_id=cas.cas_id(+) AND dl.delinquency_id(+) = wrf.delinquency_id AND rtx.customer_trx_id(+) = dl.transaction_id AND alk.lookup_type = 'ADJUST_REASON' AND wrf.writeoff_reason = alk.lookup_code AND wrf.org_id = op_unit.organization_id(+) AND wrf.cust_account_id = hca.cust_account_id(+) AND wrf.customer_site_use_id = su.site_use_id(+) UNION ALL SELECT NVL(dl.party_cust_id, lit.party_id) party_id, NVL(dl.cust_account_id, lit.cust_account_id) cust_account_id, hca.account_number account_number, NVL(rtx.bill_to_site_use_id, to_number(lit.customer_site_use_id)) customer_site_use_id, su.location location, dl.delinquency_id delinquency_id, dl.transaction_id transaction_id, rtx.trx_number transaction_number, iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', 'LITIGATION') status, lit.disposition_code disposition_code, iex_utilities.get_lookup_meaning('IEX_LTG_DISPOSITION', lit.disposition_code) disposition, 'N/A' writeoff_reason_code, 'N/A' writeoff_reason, lit.litigation_id stage_id, cas.cas_id case_id, cas.case_number case_number, lit.judgement_date stage_date, lit.last_update_date last_update_date, lit.contract_id contract_id, lit.contract_number contract_number, lit.org_id org_id, lit.creation_date creation_date, op_unit.name operating_unit FROM iex_delinquencies dl, iex_litigations lit, ra_customer_trx rtx, hr_operating_units op_unit, hz_cust_accounts hca, hz_cust_site_uses su, iex_cases cas, iex_case_objects cob WHERE cob.object_id(+)=lit.contract_id AND cob.cas_id=cas.cas_id(+) AND dl.delinquency_id(+) = lit.delinquency_id AND rtx.customer_trx_id(+) = dl.transaction_id AND lit.org_id = op_unit.organization_id(+) AND lit.cust_account_id = hca.cust_account_id(+) AND lit.customer_site_use_id = su.site_use_id(+)

View Text - HTML Formatted

SELECT NVL(DL.PARTY_CUST_ID
, RPS.PARTY_ID) PARTY_ID
, NVL(DL.CUST_ACCOUNT_ID
, RPS.CUST_ACCOUNT_ID) CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, NVL(RTX.BILL_TO_SITE_USE_ID
, TO_NUMBER(RPS.CUSTOMER_SITE_USE_ID)) CUSTOMER_SITE_USE_ID
, SU.LOCATION LOCATION
, DL.DELINQUENCY_ID DELINQUENCY_ID
, DL.TRANSACTION_ID TRANSACTION_ID
, RTX.TRX_NUMBER TRANSACTION_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATUS'
, 'REPOSSESSION') STATUS
, RPS.DISPOSITION_CODE DISPOSITION_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_REPO_DISPOSITION'
, RPS.DISPOSITION_CODE) DISPOSITION
, 'N/A' WRITEOFF_REASON_CODE
, 'N/A' WRITEOFF_REASON
, RPS.REPOSSESSION_ID STAGE_ID
, CAS.CAS_ID CASE_ID
, CAS.CASE_NUMBER CASE_NUMBER
, RPS.REPOSSESSION_DATE STAGE_DATE
, RPS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RPS.CONTRACT_ID CONTRACT_ID
, RPS.CONTRACT_NUMBER CONTRACT_NUMBER
, RPS.ORG_ID ORG_ID
, RPS.CREATION_DATE CREATION_DATE
, OP_UNIT.NAME OPERATING_UNIT
FROM IEX_REPOSSESSIONS RPS
, IEX_CASE_OBJECTS COB
, IEX_CASES CAS
, HR_OPERATING_UNITS OP_UNIT
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES SU
, IEX_DELINQUENCIES DL
, RA_CUSTOMER_TRX RTX
WHERE COB.OBJECT_ID(+)=RPS.CONTRACT_ID
AND COB.CAS_ID=CAS.CAS_ID(+)
AND RPS.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND RPS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID (+)
AND RPS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+)
AND RTX.CUSTOMER_TRX_ID(+) = DL.TRANSACTION_ID
AND DL.DELINQUENCY_ID(+) = RPS.DELINQUENCY_ID
AND RTX.CUSTOMER_TRX_ID(+) = DL.TRANSACTION_ID UNION ALL SELECT NVL(DL.PARTY_CUST_ID
, WRF.PARTY_ID) PARTY_ID
, NVL(DL.CUST_ACCOUNT_ID
, WRF.CUST_ACCOUNT_ID) CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, NVL(RTX.BILL_TO_SITE_USE_ID
, TO_NUMBER(WRF.CUSTOMER_SITE_USE_ID)) CUSTOMER_SITE_USE_ID
, SU.LOCATION LOCATION
, DL.DELINQUENCY_ID DELINQUENCY_ID
, DL.TRANSACTION_ID TRANSACTION_ID
, RTX.TRX_NUMBER TRANSACTION_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATUS'
, 'WRITEOFF') STATUS
, WRF.DISPOSITION_CODE DISPOSITION_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_WRITEOFF_DISPOSITION_CODE'
, WRF.DISPOSITION_CODE) DISPOSITION
, WRF.WRITEOFF_REASON WRITEOFF_REASON_CODE
, ALK.MEANING WRITEOFF_REASON
, WRF.WRITEOFF_ID STAGE_ID
, CAS.CAS_ID CASE_ID
, CAS.CASE_NUMBER CASE_NUMBER
, WRF.WRITEOFF_REQUEST_DATE STAGE_DATE
, WRF.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WRF.CONTRACT_ID CONTRACT_ID
, WRF.CONTRACT_NUMBER CONTRACT_NUMBER
, WRF.ORG_ID ORG_ID
, WRF.CREATION_DATE CREATION_DATE
, OP_UNIT.NAME OPERATING_UNIT
FROM IEX_DELINQUENCIES DL
, IEX_WRITEOFFS WRF
, RA_CUSTOMER_TRX RTX
, AR_LOOKUPS ALK
, HR_OPERATING_UNITS OP_UNIT
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES SU
, IEX_CASES CAS
, IEX_CASE_OBJECTS COB
WHERE COB.OBJECT_ID(+)=WRF.CONTRACT_ID
AND COB.CAS_ID=CAS.CAS_ID(+)
AND DL.DELINQUENCY_ID(+) = WRF.DELINQUENCY_ID
AND RTX.CUSTOMER_TRX_ID(+) = DL.TRANSACTION_ID
AND ALK.LOOKUP_TYPE = 'ADJUST_REASON'
AND WRF.WRITEOFF_REASON = ALK.LOOKUP_CODE
AND WRF.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND WRF.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND WRF.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+) UNION ALL SELECT NVL(DL.PARTY_CUST_ID
, LIT.PARTY_ID) PARTY_ID
, NVL(DL.CUST_ACCOUNT_ID
, LIT.CUST_ACCOUNT_ID) CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, NVL(RTX.BILL_TO_SITE_USE_ID
, TO_NUMBER(LIT.CUSTOMER_SITE_USE_ID)) CUSTOMER_SITE_USE_ID
, SU.LOCATION LOCATION
, DL.DELINQUENCY_ID DELINQUENCY_ID
, DL.TRANSACTION_ID TRANSACTION_ID
, RTX.TRX_NUMBER TRANSACTION_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATUS'
, 'LITIGATION') STATUS
, LIT.DISPOSITION_CODE DISPOSITION_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_LTG_DISPOSITION'
, LIT.DISPOSITION_CODE) DISPOSITION
, 'N/A' WRITEOFF_REASON_CODE
, 'N/A' WRITEOFF_REASON
, LIT.LITIGATION_ID STAGE_ID
, CAS.CAS_ID CASE_ID
, CAS.CASE_NUMBER CASE_NUMBER
, LIT.JUDGEMENT_DATE STAGE_DATE
, LIT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LIT.CONTRACT_ID CONTRACT_ID
, LIT.CONTRACT_NUMBER CONTRACT_NUMBER
, LIT.ORG_ID ORG_ID
, LIT.CREATION_DATE CREATION_DATE
, OP_UNIT.NAME OPERATING_UNIT
FROM IEX_DELINQUENCIES DL
, IEX_LITIGATIONS LIT
, RA_CUSTOMER_TRX RTX
, HR_OPERATING_UNITS OP_UNIT
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES SU
, IEX_CASES CAS
, IEX_CASE_OBJECTS COB
WHERE COB.OBJECT_ID(+)=LIT.CONTRACT_ID
AND COB.CAS_ID=CAS.CAS_ID(+)
AND DL.DELINQUENCY_ID(+) = LIT.DELINQUENCY_ID
AND RTX.CUSTOMER_TRX_ID(+) = DL.TRANSACTION_ID
AND LIT.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND LIT.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND LIT.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+)