VIEW: APPS.IEX_WRITEOFFS_BALI_V

Source

View Text - Preformatted

SELECT dl.party_cust_id party_id, dl.cust_account_id cust_account_id, rtx.bill_to_site_use_id customer_site_use_id, 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 writeoff_id, wrf.cas_id case_id, wrf.writeoff_request_date writeoff_request_date, wrf.last_update_date last_update_date, wrf.contract_id contract_id, wrf.contract_number contract_number, wrf.org_id org_id FROM iex_delinquencies dl, iex_writeoffs wrf, ra_customer_trx rtx, ar_lookups alk WHERE dl.delinquency_id = wrf.delinquency_id and wrf.delinquency_id is not null and rtx.customer_trx_id = dl.transaction_id and alk.lookup_type = 'ADJUST_REASON' and wrf.writeoff_reason = alk.lookup_code UNION ALL select wrff.party_id party_id, wrff.cust_account_id cust_account_id, to_number(wrff.customer_site_use_id) customer_site_use_id, wrff.delinquency_id delinquency_id, null transaction_id, null transaction_number, iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', 'WRITEOFF') status, wrff.disposition_code disposition_code, iex_utilities.get_lookup_meaning('IEX_WRITEOFF_DISPOSITION_CODE', wrff.DISPOSITION_CODE) disposition, wrff.writeoff_reason writeoff_reason_code, alkk.meaning writeoff_reason, wrff.writeoff_id writeoff_id, wrff.cas_id case_id, wrff.writeoff_request_date writeoff_request_date, wrff.last_update_date last_update_date, wrff.contract_id contract_id, wrff.contract_number contract_number, wrff.org_id org_id from iex_writeoffs wrff, ar_lookups alkk WHERE wrff.contract_id is not null and wrff.contract_number is not null and alkk.lookup_type = 'ADJUST_REASON' and wrff.writeoff_reason = alkk.lookup_code

View Text - HTML Formatted

SELECT DL.PARTY_CUST_ID PARTY_ID
, DL.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, RTX.BILL_TO_SITE_USE_ID CUSTOMER_SITE_USE_ID
, 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 WRITEOFF_ID
, WRF.CAS_ID CASE_ID
, WRF.WRITEOFF_REQUEST_DATE WRITEOFF_REQUEST_DATE
, WRF.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WRF.CONTRACT_ID CONTRACT_ID
, WRF.CONTRACT_NUMBER CONTRACT_NUMBER
, WRF.ORG_ID ORG_ID
FROM IEX_DELINQUENCIES DL
, IEX_WRITEOFFS WRF
, RA_CUSTOMER_TRX RTX
, AR_LOOKUPS ALK
WHERE DL.DELINQUENCY_ID = WRF.DELINQUENCY_ID
AND WRF.DELINQUENCY_ID IS NOT NULL
AND RTX.CUSTOMER_TRX_ID = DL.TRANSACTION_ID
AND ALK.LOOKUP_TYPE = 'ADJUST_REASON'
AND WRF.WRITEOFF_REASON = ALK.LOOKUP_CODE UNION ALL SELECT WRFF.PARTY_ID PARTY_ID
, WRFF.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, TO_NUMBER(WRFF.CUSTOMER_SITE_USE_ID) CUSTOMER_SITE_USE_ID
, WRFF.DELINQUENCY_ID DELINQUENCY_ID
, NULL TRANSACTION_ID
, NULL TRANSACTION_NUMBER
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATUS'
, 'WRITEOFF') STATUS
, WRFF.DISPOSITION_CODE DISPOSITION_CODE
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_WRITEOFF_DISPOSITION_CODE'
, WRFF.DISPOSITION_CODE) DISPOSITION
, WRFF.WRITEOFF_REASON WRITEOFF_REASON_CODE
, ALKK.MEANING WRITEOFF_REASON
, WRFF.WRITEOFF_ID WRITEOFF_ID
, WRFF.CAS_ID CASE_ID
, WRFF.WRITEOFF_REQUEST_DATE WRITEOFF_REQUEST_DATE
, WRFF.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WRFF.CONTRACT_ID CONTRACT_ID
, WRFF.CONTRACT_NUMBER CONTRACT_NUMBER
, WRFF.ORG_ID ORG_ID
FROM IEX_WRITEOFFS WRFF
, AR_LOOKUPS ALKK
WHERE WRFF.CONTRACT_ID IS NOT NULL
AND WRFF.CONTRACT_NUMBER IS NOT NULL
AND ALKK.LOOKUP_TYPE = 'ADJUST_REASON'
AND WRFF.WRITEOFF_REASON = ALKK.LOOKUP_CODE