VIEW: APPS.IEX_DELINQUENCIES_BALI_V

Source

View Text - Preformatted

SELECT DLN.PARTY_CUST_ID CUSTOMER_ID , DLN.CUST_ACCOUNT_ID CUST_ACCOUNT_ID , DLN.DELINQUENCY_ID DELINQUENCY_ID , DLN.TRANSACTION_ID TRANSACTION_ID , DLN.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , DLN.OPEN_CLOSE_STATUS OPEN_CLOSE_STATUS ,IEX_UTILITIES.get_lookup_meaning('IEX_DELINQUENCY_STATUS',DLN.OPEN_CLOSE_STATUS) STATE , DLN.STATUS STATUS , 0 AMOUNT , APS.INVOICE_CURRENCY_CODE AMOUNT_UNIT , DLN.BANKRUPTCY_ID BANKRUPTCY_ID , DLN.WRITEOFF_ID WRITEOFF_ID , DLN.REPOSSESSION_ID REPOSSESSION_ID , DLN.LITIGATION_ID LITIGATION_ID , ICO.CAS_ID CASE_ID , ABL.REPORT_HEADING1||ABL.REPORT_HEADING2 AGING_BUCKET , DLN.SCORE_VALUE SCORE , ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER , ICO.CAS_ID CASE_NUMBER , TO_CHAR(TRUNC(SYSDATE) - TRUNC(APS.DUE_DATE)) DAYS , APS.TRX_NUMBER TRX_NUMBER , DLN.UWQ_STATUS UWQ_STATUS , DLN.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID , SU.LOCATION LOCATION , SU.ADDRESS ADDRESS , SU.PARTY_SITE_NUMBER PARTY_SITE_NUMBER , DLN.CREATION_DATE CREATION_DATE , DLN.LAST_UPDATE_DATE LAST_UPDATE_DATE , APS.CONS_INV_ID CONS_INV_ID , CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER , DLN.ORG_ID ORG_ID , OP_UNIT.NAME OPERATING_UNIT , HP.PARTY_NAME PARTY_NAME FROM IEX_DELINQUENCIES_STAGE_V DLN , AR_AGING_BUCKET_LINES_VL ABL , HZ_CUST_ACCOUNTS ACC , AR_PAYMENT_SCHEDULES APS , IEX_CUST_SITE_USES_V SU , AR_CONS_INV CONS , HR_OPERATING_UNITS OP_UNIT , HZ_PARTIES HP ,okl_cnsld_ar_strms_b LSM ,iex_case_objects ICO WHERE DLN.AGING_BUCKET_LINE_ID = ABL.AGING_BUCKET_LINE_ID(+) AND DLN.CUST_ACCOUNT_ID = ACC.CUST_ACCOUNT_ID AND dln.transaction_id =lsm.receivables_invoice_id(+) and lsm.khr_id =ico.object_id(+) AND DLN.PAYMENT_SCHEDULE_ID = APS.PAYMENT_SCHEDULE_ID AND SU.CUSTOMER_SITE_USE_ID(+) = DLN.CUSTOMER_SITE_USE_ID AND CONS.CONS_INV_ID(+) = APS.CONS_INV_ID AND DLN.ORG_ID = OP_UNIT.ORGANIZATION_ID(+) AND DLN.PARTY_CUST_ID = HP.PARTY_ID

View Text - HTML Formatted

SELECT DLN.PARTY_CUST_ID CUSTOMER_ID
, DLN.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, DLN.DELINQUENCY_ID DELINQUENCY_ID
, DLN.TRANSACTION_ID TRANSACTION_ID
, DLN.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, DLN.OPEN_CLOSE_STATUS OPEN_CLOSE_STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('IEX_DELINQUENCY_STATUS'
, DLN.OPEN_CLOSE_STATUS) STATE
, DLN.STATUS STATUS
, 0 AMOUNT
, APS.INVOICE_CURRENCY_CODE AMOUNT_UNIT
, DLN.BANKRUPTCY_ID BANKRUPTCY_ID
, DLN.WRITEOFF_ID WRITEOFF_ID
, DLN.REPOSSESSION_ID REPOSSESSION_ID
, DLN.LITIGATION_ID LITIGATION_ID
, ICO.CAS_ID CASE_ID
, ABL.REPORT_HEADING1||ABL.REPORT_HEADING2 AGING_BUCKET
, DLN.SCORE_VALUE SCORE
, ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER
, ICO.CAS_ID CASE_NUMBER
, TO_CHAR(TRUNC(SYSDATE) - TRUNC(APS.DUE_DATE)) DAYS
, APS.TRX_NUMBER TRX_NUMBER
, DLN.UWQ_STATUS UWQ_STATUS
, DLN.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, SU.LOCATION LOCATION
, SU.ADDRESS ADDRESS
, SU.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, DLN.CREATION_DATE CREATION_DATE
, DLN.LAST_UPDATE_DATE LAST_UPDATE_DATE
, APS.CONS_INV_ID CONS_INV_ID
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, DLN.ORG_ID ORG_ID
, OP_UNIT.NAME OPERATING_UNIT
, HP.PARTY_NAME PARTY_NAME
FROM IEX_DELINQUENCIES_STAGE_V DLN
, AR_AGING_BUCKET_LINES_VL ABL
, HZ_CUST_ACCOUNTS ACC
, AR_PAYMENT_SCHEDULES APS
, IEX_CUST_SITE_USES_V SU
, AR_CONS_INV CONS
, HR_OPERATING_UNITS OP_UNIT
, HZ_PARTIES HP
, OKL_CNSLD_AR_STRMS_B LSM
, IEX_CASE_OBJECTS ICO
WHERE DLN.AGING_BUCKET_LINE_ID = ABL.AGING_BUCKET_LINE_ID(+)
AND DLN.CUST_ACCOUNT_ID = ACC.CUST_ACCOUNT_ID
AND DLN.TRANSACTION_ID =LSM.RECEIVABLES_INVOICE_ID(+)
AND LSM.KHR_ID =ICO.OBJECT_ID(+)
AND DLN.PAYMENT_SCHEDULE_ID = APS.PAYMENT_SCHEDULE_ID
AND SU.CUSTOMER_SITE_USE_ID(+) = DLN.CUSTOMER_SITE_USE_ID
AND CONS.CONS_INV_ID(+) = APS.CONS_INV_ID
AND DLN.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)
AND DLN.PARTY_CUST_ID = HP.PARTY_ID