View: AR_TA_ESS_CREDIT_V

View Text

SELECT SP.ORG_ID
, SP.SET_OF_BOOKS_ID
, CUST_ACCT.ACCOUNT_NUMBER
, CP.CREDIT_HOLD
, DECODE(CPA.OVERALL_CREDIT_LIMIT
, NULL
, CP.SITE_USE_ID
, CPA.OVERALL_CREDIT_LIMIT/1000) CL
FROM HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_AMTS CPA
, GL_SETS_OF_BOOKS SOB
, AR_SYSTEM_PARAMETERS_ALL SP
WHERE SP.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.SITE_USE_ID IS NULL
AND CPA.SITE_USE_ID IS NULL
AND CUST_ACCT.CUST_ACCOUNT_ID = CPA.CUST_ACCOUNT_ID
AND CPA.CURRENCY_CODE = SOB.CURRENCY_CODE UNION SELECT SP2.ORG_ID
, SP2.SET_OF_BOOKS_ID
, CUST_ACCT.ACCOUNT_NUMBER
, CP.CREDIT_HOLD
, CP.SITE_USE_ID
FROM HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUSTOMER_PROFILES CP
, AR_SYSTEM_PARAMETERS_ALL SP2
WHERE CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.SITE_USE_ID IS NULL
AND (CUST_ACCT.CUST_ACCOUNT_ID
, SP2.ORG_ID) NOT IN (SELECT CP1.CUST_ACCOUNT_ID
, SP1.ORG_ID
FROM HZ_CUST_PROFILE_AMTS CP1
, AR_SYSTEM_PARAMETERS_ALL SP1
, GL_SETS_OF_BOOKS SOB1
WHERE CP1.SITE_USE_ID IS NULL
AND SP1.SET_OF_BOOKS_ID = SOB1.SET_OF_BOOKS_ID
AND CP1.CURRENCY_CODE = SOB1.CURRENCY_CODE )

Columns