VIEW: APPS.IEX_LS_PROMISE_TO_PAY_V

Source

View Text - Preformatted

SELECT PRD.PROMISE_DETAIL_ID, PRD.CUST_ACCOUNT_ID, HCA.ACCOUNT_NUMBER, PRD.DELINQUENCY_ID, DEL.PAYMENT_SCHEDULE_ID, PSA.CUSTOMER_TRX_ID, PSA.TRX_NUMBER, PSA.TRX_DATE, PSA.TERMS_SEQUENCE_NUMBER, PSA.DUE_DATE, PRD.CREATION_DATE, PRD.PROMISE_DATE, PRD.PROMISE_AMOUNT, PRD.PROMISE_PAYMENT_METHOD, FNDLKPV1.MEANING, PRD.STATUS, FNDLKPV2.MEANING, PRD.ACCOUNT, PRD.PROMISE_ITEM_NUMBER, PRD.CURRENCY_CODE, PRTY2.PARTY_ID, PRTY2.PARTY_NAME , PRTY3.PARTY_ID, PRTY3.PERSON_FIRST_NAME, PRTY3.PERSON_LAST_NAME FROM IEX_PROMISE_DETAILS PRD, IEX_DELINQUENCIES DEL, AR_PAYMENT_SCHEDULES PSA, HZ_CUST_ACCOUNTS HCA, FND_LOOKUP_VALUES_VL FNDLKPV1, FND_LOOKUP_VALUES_VL FNDLKPV2, HZ_PARTIES PRTY2, HZ_PARTIES PRTY3 WHERE PRD.DELINQUENCY_ID IS NOT NULL AND PRD.DELINQUENCY_ID = DEL.DELINQUENCY_ID AND PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+) AND HCA.PARTY_ID = PRTY2.PARTY_ID AND DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID(+) AND PRD.STATUS = FNDLKPV2.LOOKUP_CODE AND FNDLKPV2.LOOKUP_TYPE = 'IEX_PROMISE_STATUSES' AND PRD.PROMISE_PAYMENT_METHOD = FNDLKPV1.LOOKUP_CODE(+) AND FNDLKPV1.LOOKUP_TYPE(+) = 'IEX_PAYMENT_TYPES' AND PRD.PROMISE_MADE_BY = PRTY3.PARTY_ID(+) union SELECT prd.PROMISE_DETAIL_ID, prd.CUST_ACCOUNT_ID, hca.ACCOUNT_NUMBER, to_number(null), to_number(null), to_number(null), null, to_date(null), to_number(null), to_date(null), prd.CREATION_DATE, prd.PROMISE_DATE, prd.PROMISE_AMOUNT, prd.PROMISE_PAYMENT_METHOD, FNDLKPV1.MEANING, prd.STATUS, FNDLKPV2.MEANING, prd.ACCOUNT, prd.PROMISE_ITEM_NUMBER, prd.CURRENCY_CODE, PRTY2.PARTY_ID, PRTY2.PARTY_NAME, PRTY3.PARTY_ID, PRTY3.PERSON_FIRST_NAME, PRTY3.PERSON_LAST_NAME from IEX_PROMISE_DETAILS prd, HZ_CUST_ACCOUNTS hca, FND_LOOKUP_VALUES_VL FNDLKPV1, FND_LOOKUP_VALUES_VL FNDLKPV2, HZ_PARTIES PRTY2, HZ_PARTIES PRTY3 where prd.DELINQUENCY_ID is null and prd.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID AND HCA.PARTY_ID = PRTY2.PARTY_ID AND FNDLKPV1.LOOKUP_TYPE = 'IEX_PAYMENT_TYPES' and prd.PROMISE_PAYMENT_METHOD = FNDLKPV1.LOOKUP_CODE and FNDLKPV2.LOOKUP_TYPE = 'IEX_PROMISE_STATUSES' and prd.status = FNDLKPV2.LOOKUP_CODE and PRD.PROMISE_MADE_BY = PRTY3.PARTY_ID(+)

View Text - HTML Formatted

SELECT PRD.PROMISE_DETAIL_ID
, PRD.CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER
, PRD.DELINQUENCY_ID
, DEL.PAYMENT_SCHEDULE_ID
, PSA.CUSTOMER_TRX_ID
, PSA.TRX_NUMBER
, PSA.TRX_DATE
, PSA.TERMS_SEQUENCE_NUMBER
, PSA.DUE_DATE
, PRD.CREATION_DATE
, PRD.PROMISE_DATE
, PRD.PROMISE_AMOUNT
, PRD.PROMISE_PAYMENT_METHOD
, FNDLKPV1.MEANING
, PRD.STATUS
, FNDLKPV2.MEANING
, PRD.ACCOUNT
, PRD.PROMISE_ITEM_NUMBER
, PRD.CURRENCY_CODE
, PRTY2.PARTY_ID
, PRTY2.PARTY_NAME
, PRTY3.PARTY_ID
, PRTY3.PERSON_FIRST_NAME
, PRTY3.PERSON_LAST_NAME
FROM IEX_PROMISE_DETAILS PRD
, IEX_DELINQUENCIES DEL
, AR_PAYMENT_SCHEDULES PSA
, HZ_CUST_ACCOUNTS HCA
, FND_LOOKUP_VALUES_VL FNDLKPV1
, FND_LOOKUP_VALUES_VL FNDLKPV2
, HZ_PARTIES PRTY2
, HZ_PARTIES PRTY3
WHERE PRD.DELINQUENCY_ID IS NOT NULL
AND PRD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
AND PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID = PRTY2.PARTY_ID
AND DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID(+)
AND PRD.STATUS = FNDLKPV2.LOOKUP_CODE
AND FNDLKPV2.LOOKUP_TYPE = 'IEX_PROMISE_STATUSES'
AND PRD.PROMISE_PAYMENT_METHOD = FNDLKPV1.LOOKUP_CODE(+)
AND FNDLKPV1.LOOKUP_TYPE(+) = 'IEX_PAYMENT_TYPES'
AND PRD.PROMISE_MADE_BY = PRTY3.PARTY_ID(+) UNION SELECT PRD.PROMISE_DETAIL_ID
, PRD.CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, PRD.CREATION_DATE
, PRD.PROMISE_DATE
, PRD.PROMISE_AMOUNT
, PRD.PROMISE_PAYMENT_METHOD
, FNDLKPV1.MEANING
, PRD.STATUS
, FNDLKPV2.MEANING
, PRD.ACCOUNT
, PRD.PROMISE_ITEM_NUMBER
, PRD.CURRENCY_CODE
, PRTY2.PARTY_ID
, PRTY2.PARTY_NAME
, PRTY3.PARTY_ID
, PRTY3.PERSON_FIRST_NAME
, PRTY3.PERSON_LAST_NAME
FROM IEX_PROMISE_DETAILS PRD
, HZ_CUST_ACCOUNTS HCA
, FND_LOOKUP_VALUES_VL FNDLKPV1
, FND_LOOKUP_VALUES_VL FNDLKPV2
, HZ_PARTIES PRTY2
, HZ_PARTIES PRTY3
WHERE PRD.DELINQUENCY_ID IS NULL
AND PRD.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = PRTY2.PARTY_ID
AND FNDLKPV1.LOOKUP_TYPE = 'IEX_PAYMENT_TYPES'
AND PRD.PROMISE_PAYMENT_METHOD = FNDLKPV1.LOOKUP_CODE
AND FNDLKPV2.LOOKUP_TYPE = 'IEX_PROMISE_STATUSES'
AND PRD.STATUS = FNDLKPV2.LOOKUP_CODE
AND PRD.PROMISE_MADE_BY = PRTY3.PARTY_ID(+)