VIEW: APPS.IEX_OKL_PROMISES_V

Source

View Text - Preformatted

SELECT PRD.PROMISE_DETAIL_ID, PRD.CUST_ACCOUNT_ID, HCA.ACCOUNT_NUMBER, TO_NUMBER(NULL), NULL, CAS.CAS_ID, CAS.CASE_NUMBER, PRD.CONTRACT_ID, KH.CONTRACT_NUMBER, PRD.CREATION_DATE, PRD.PROMISE_DATE, PRD.PROMISE_AMOUNT, PRD.PROMISE_PAYMENT_METHOD, LKPV1.MEANING, PRD.STATUS, LKPV2.MEANING, PRD.ACCOUNT, PRD.PROMISE_ITEM_NUMBER, PRD.CURRENCY_CODE, PRD.BROKEN_ON_DATE, PRD.UWQ_STATUS, LKPV3.MEANING, PRD.UWQ_ACTIVE_DATE, PRD.UWQ_COMPLETE_DATE, PRD.AMOUNT_DUE_REMAINING, prd.promise_made_by, HP.party_name, ALKP.meaning, decode(prd.STATE, 'BROKEN_PROMISE', 'Y', 'N'), su.customer_site_use_id, su.location, su.address, su.party_site_number, prd.resource_id, res.source_name FROM IEX_CASES_VL CAS, IEX_CASE_OBJECTS CAO, OKC_K_HEADERS_B KH, IEX_PROMISE_DETAILS PRD, HZ_CUST_ACCOUNTS HCA, IEX_LOOKUPS_V LKPV1, IEX_LOOKUPS_V LKPV2, IEX_LOOKUPS_V LKPV3, hz_cust_account_roles HCAR, hz_relationships HR, ar_lookups ALKP, hz_parties HP, iex_cust_site_uses_v su, iex_case_definitions cdef, JTF_RS_RESOURCE_EXTNS res WHERE PRD.DELINQUENCY_ID IS NULL and PRD.CNSLD_INVOICE_ID IS NULL and PRD.CONTRACT_ID IS NOT NULL and PRD.CONTRACT_ID = KH.ID and PRD.CONTRACT_ID = CAO.OBJECT_ID(+) and CAO.OBJECT_CODE(+) = 'CONTRACTS' and CAO.CAS_ID = CAS.CAS_ID(+) and PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID and LKPV1.LOOKUP_TYPE(+) = 'IEX_PAYMENT_TYPES' and LKPV1.LOOKUP_CODE(+) = prd.PROMISE_PAYMENT_METHOD and LKPV2.LOOKUP_TYPE = 'IEX_PROMISE_STATUSES' and LKPV2.LOOKUP_CODE = prd.status and HR.subject_id(+) = prd.promise_made_by and HP.party_id(+) = prd.promise_made_by AND HCAR.party_id(+) = HR.party_id and (1 = 1 or (HCAR.cust_account_id = prd.cust_account_id and exists (select promise_made_by from IEX_PROMISE_DETAILS where promise_detail_id = prd.promise_detail_id))) AND ALKP.lookup_code(+) = HCAR.role_type AND ALKP.lookup_type(+) = 'ACCT_ROLE_TYPE' AND CAS.CAS_ID = cdef.cas_id and cdef.column_name = 'BILL_TO_ADDRESS_ID' and cdef.column_value = su.customer_site_use_id AND LKPV3.LOOKUP_TYPE(+) = 'IEX_UWQ_NODE_STATUS' and LKPV3.LOOKUP_CODE(+) = prd.UWQ_STATUS and prd.resource_id = res.resource_id

View Text - HTML Formatted

SELECT PRD.PROMISE_DETAIL_ID
, PRD.CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER
, TO_NUMBER(NULL)
, NULL
, CAS.CAS_ID
, CAS.CASE_NUMBER
, PRD.CONTRACT_ID
, KH.CONTRACT_NUMBER
, PRD.CREATION_DATE
, PRD.PROMISE_DATE
, PRD.PROMISE_AMOUNT
, PRD.PROMISE_PAYMENT_METHOD
, LKPV1.MEANING
, PRD.STATUS
, LKPV2.MEANING
, PRD.ACCOUNT
, PRD.PROMISE_ITEM_NUMBER
, PRD.CURRENCY_CODE
, PRD.BROKEN_ON_DATE
, PRD.UWQ_STATUS
, LKPV3.MEANING
, PRD.UWQ_ACTIVE_DATE
, PRD.UWQ_COMPLETE_DATE
, PRD.AMOUNT_DUE_REMAINING
, PRD.PROMISE_MADE_BY
, HP.PARTY_NAME
, ALKP.MEANING
, DECODE(PRD.STATE
, 'BROKEN_PROMISE'
, 'Y'
, 'N')
, SU.CUSTOMER_SITE_USE_ID
, SU.LOCATION
, SU.ADDRESS
, SU.PARTY_SITE_NUMBER
, PRD.RESOURCE_ID
, RES.SOURCE_NAME
FROM IEX_CASES_VL CAS
, IEX_CASE_OBJECTS CAO
, OKC_K_HEADERS_B KH
, IEX_PROMISE_DETAILS PRD
, HZ_CUST_ACCOUNTS HCA
, IEX_LOOKUPS_V LKPV1
, IEX_LOOKUPS_V LKPV2
, IEX_LOOKUPS_V LKPV3
, HZ_CUST_ACCOUNT_ROLES HCAR
, HZ_RELATIONSHIPS HR
, AR_LOOKUPS ALKP
, HZ_PARTIES HP
, IEX_CUST_SITE_USES_V SU
, IEX_CASE_DEFINITIONS CDEF
, JTF_RS_RESOURCE_EXTNS RES
WHERE PRD.DELINQUENCY_ID IS NULL
AND PRD.CNSLD_INVOICE_ID IS NULL
AND PRD.CONTRACT_ID IS NOT NULL
AND PRD.CONTRACT_ID = KH.ID
AND PRD.CONTRACT_ID = CAO.OBJECT_ID(+)
AND CAO.OBJECT_CODE(+) = 'CONTRACTS'
AND CAO.CAS_ID = CAS.CAS_ID(+)
AND PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND LKPV1.LOOKUP_TYPE(+) = 'IEX_PAYMENT_TYPES'
AND LKPV1.LOOKUP_CODE(+) = PRD.PROMISE_PAYMENT_METHOD
AND LKPV2.LOOKUP_TYPE = 'IEX_PROMISE_STATUSES'
AND LKPV2.LOOKUP_CODE = PRD.STATUS
AND HR.SUBJECT_ID(+) = PRD.PROMISE_MADE_BY
AND HP.PARTY_ID(+) = PRD.PROMISE_MADE_BY
AND HCAR.PARTY_ID(+) = HR.PARTY_ID
AND (1 = 1 OR (HCAR.CUST_ACCOUNT_ID = PRD.CUST_ACCOUNT_ID
AND EXISTS (SELECT PROMISE_MADE_BY
FROM IEX_PROMISE_DETAILS
WHERE PROMISE_DETAIL_ID = PRD.PROMISE_DETAIL_ID)))
AND ALKP.LOOKUP_CODE(+) = HCAR.ROLE_TYPE
AND ALKP.LOOKUP_TYPE(+) = 'ACCT_ROLE_TYPE'
AND CAS.CAS_ID = CDEF.CAS_ID
AND CDEF.COLUMN_NAME = 'BILL_TO_ADDRESS_ID'
AND CDEF.COLUMN_VALUE = SU.CUSTOMER_SITE_USE_ID
AND LKPV3.LOOKUP_TYPE(+) = 'IEX_UWQ_NODE_STATUS'
AND LKPV3.LOOKUP_CODE(+) = PRD.UWQ_STATUS
AND PRD.RESOURCE_ID = RES.RESOURCE_ID