VIEW: APPS.IEX_PROMISES_V

Source

View Text - Preformatted

SELECT prd.PROMISE_DETAIL_ID PROMISE_DETAIL_ID, prd.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, hca.ACCOUNT_NUMBER ACCOUNT_NUMBER, prd.DELINQUENCY_ID DELINQUENCY_ID, del.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, psa.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, psa.TRX_NUMBER TRX_NUMBER, psa.TRX_DATE TRX_DATE, psa.TERMS_SEQUENCE_NUMBER INSTALLMENT, psa.DUE_DATE DUE_DATE, prd.CREATION_DATE CREATION_DATE, prd.PROMISE_DATE PROMISE_DATE, prd.PROMISE_AMOUNT PROMISE_AMOUNT, prd.PROMISE_PAYMENT_METHOD PROMISE_PAYMENT_METHOD, LKPV1.MEANING PROMISE_PAYMENT_METHOD_DSP, prd.STATUS STATUS, LKPV2.MEANING STATUS_DSP, prd.ACCOUNT ACCOUNT, prd.PROMISE_ITEM_NUMBER PROMISE_ITEM_NUMBER, prd.CURRENCY_CODE CURRENCY_CODE, prd.BROKEN_ON_DATE BROKEN_ON_DATE, prd.UWQ_STATUS UWQ_STATUS, LKPV3.MEANING UWQ_STATUS_DSP, prd.UWQ_ACTIVE_DATE UWQ_ACTIVE_DATE, prd.UWQ_COMPLETE_DATE UWQ_COMPLETE_DATE, prd.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING, prd.promise_made_by PROMISE_MADE_BY, HP.party_name PROMISE_MADE_BY_NAME, '' ROLE, decode(prd.STATE, 'BROKEN_PROMISE', 'Y', 'N') BROKEN, psa.customer_site_use_id CUSTOMER_SITE_USE_ID, su.location LOCATION, su.address ADDRESS, su.party_site_number PARTY_SITE_NUMBER, prd.resource_id RESOURCE_ID, res.source_name RESOURCE_NAME, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER, del.org_id ORG_ID, op_unit.name OPERATING_UNIT FROM IEX_PROMISE_DETAILS PRD , IEX_DELINQUENCIES DEL , AR_PAYMENT_SCHEDULES_ALL PSA , HZ_CUST_ACCOUNTS HCA , IEX_LOOKUPS_V LKPV1 , IEX_LOOKUPS_V LKPV2 , IEX_LOOKUPS_V LKPV3 , hz_parties HP , iex_cust_site_uses_v su , JTF_RS_RESOURCE_EXTNS res , AR_CONS_INV CONS , HR_OPERATING_UNITS OP_UNIT WHERE PRD.CONTRACT_ID IS NULL AND PRD.CNSLD_INVOICE_ID IS NULL AND prd.DELINQUENCY_ID is not null and prd.DELINQUENCY_ID = del.DELINQUENCY_ID and prd.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID and del.PAYMENT_SCHEDULE_ID = psa.PAYMENT_SCHEDULE_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 HP.party_id(+) = prd.promise_made_by AND LKPV3.LOOKUP_TYPE(+) = 'IEX_UWQ_NODE_STATUS' and LKPV3.LOOKUP_CODE(+) = prd.UWQ_STATUS and psa.customer_site_use_id = su.customer_site_use_id and prd.resource_id = res.resource_id and cons.cons_inv_id(+) = psa.cons_inv_id and del.org_id = op_unit.organization_id(+)

View Text - HTML Formatted

SELECT PRD.PROMISE_DETAIL_ID PROMISE_DETAIL_ID
, PRD.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, PRD.DELINQUENCY_ID DELINQUENCY_ID
, DEL.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PSA.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PSA.TRX_NUMBER TRX_NUMBER
, PSA.TRX_DATE TRX_DATE
, PSA.TERMS_SEQUENCE_NUMBER INSTALLMENT
, PSA.DUE_DATE DUE_DATE
, PRD.CREATION_DATE CREATION_DATE
, PRD.PROMISE_DATE PROMISE_DATE
, PRD.PROMISE_AMOUNT PROMISE_AMOUNT
, PRD.PROMISE_PAYMENT_METHOD PROMISE_PAYMENT_METHOD
, LKPV1.MEANING PROMISE_PAYMENT_METHOD_DSP
, PRD.STATUS STATUS
, LKPV2.MEANING STATUS_DSP
, PRD.ACCOUNT ACCOUNT
, PRD.PROMISE_ITEM_NUMBER PROMISE_ITEM_NUMBER
, PRD.CURRENCY_CODE CURRENCY_CODE
, PRD.BROKEN_ON_DATE BROKEN_ON_DATE
, PRD.UWQ_STATUS UWQ_STATUS
, LKPV3.MEANING UWQ_STATUS_DSP
, PRD.UWQ_ACTIVE_DATE UWQ_ACTIVE_DATE
, PRD.UWQ_COMPLETE_DATE UWQ_COMPLETE_DATE
, PRD.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PRD.PROMISE_MADE_BY PROMISE_MADE_BY
, HP.PARTY_NAME PROMISE_MADE_BY_NAME
, '' ROLE
, DECODE(PRD.STATE
, 'BROKEN_PROMISE'
, 'Y'
, 'N') BROKEN
, PSA.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, SU.LOCATION LOCATION
, SU.ADDRESS ADDRESS
, SU.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, PRD.RESOURCE_ID RESOURCE_ID
, RES.SOURCE_NAME RESOURCE_NAME
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, DEL.ORG_ID ORG_ID
, OP_UNIT.NAME OPERATING_UNIT
FROM IEX_PROMISE_DETAILS PRD
, IEX_DELINQUENCIES DEL
, AR_PAYMENT_SCHEDULES_ALL PSA
, HZ_CUST_ACCOUNTS HCA
, IEX_LOOKUPS_V LKPV1
, IEX_LOOKUPS_V LKPV2
, IEX_LOOKUPS_V LKPV3
, HZ_PARTIES HP
, IEX_CUST_SITE_USES_V SU
, JTF_RS_RESOURCE_EXTNS RES
, AR_CONS_INV CONS
, HR_OPERATING_UNITS OP_UNIT
WHERE PRD.CONTRACT_ID IS NULL
AND PRD.CNSLD_INVOICE_ID IS NULL
AND PRD.DELINQUENCY_ID IS NOT NULL
AND PRD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_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 HP.PARTY_ID(+) = PRD.PROMISE_MADE_BY
AND LKPV3.LOOKUP_TYPE(+) = 'IEX_UWQ_NODE_STATUS'
AND LKPV3.LOOKUP_CODE(+) = PRD.UWQ_STATUS
AND PSA.CUSTOMER_SITE_USE_ID = SU.CUSTOMER_SITE_USE_ID
AND PRD.RESOURCE_ID = RES.RESOURCE_ID
AND CONS.CONS_INV_ID(+) = PSA.CONS_INV_ID
AND DEL.ORG_ID = OP_UNIT.ORGANIZATION_ID(+)