VIEW: APPS.IEX_CUST_ACCOUNTS_V

Source

View Text - Preformatted

SELECT ca.account_number, ca.cust_account_id, ca.account_name, p.party_name, '' role_type_dsp, '' role_party FROM hz_cust_accounts ca, hz_parties p WHERE p.party_id = ca.party_id AND ca.status = 'A' AND p.status = 'A' UNION SELECT ca.account_number, car.cust_account_id, ca.account_name, o.party_name, role_type.meaning role_type_dsp, p.party_name role_party FROM hz_cust_account_roles car, hz_cust_accounts ca, hz_parties O, hz_relationships rel, ar_lookups role_type, hz_parties p WHERE car.cust_account_id = ca.cust_account_id AND car.party_id = rel.party_id AND rel.subject_id = p.party_id AND ca.party_id = o.party_id AND rel.object_id = o.party_id AND o.status = 'A' AND p.status = 'A' AND ca.status = 'A' AND car.status = 'A' AND car.role_type = role_type.lookup_code AND role_type.lookup_type = 'ACCT_ROLE_TYPE' ORDER BY account_number

View Text - HTML Formatted

SELECT CA.ACCOUNT_NUMBER
, CA.CUST_ACCOUNT_ID
, CA.ACCOUNT_NAME
, P.PARTY_NAME
, '' ROLE_TYPE_DSP
, '' ROLE_PARTY
FROM HZ_CUST_ACCOUNTS CA
, HZ_PARTIES P
WHERE P.PARTY_ID = CA.PARTY_ID
AND CA.STATUS = 'A'
AND P.STATUS = 'A' UNION SELECT CA.ACCOUNT_NUMBER
, CAR.CUST_ACCOUNT_ID
, CA.ACCOUNT_NAME
, O.PARTY_NAME
, ROLE_TYPE.MEANING ROLE_TYPE_DSP
, P.PARTY_NAME ROLE_PARTY
FROM HZ_CUST_ACCOUNT_ROLES CAR
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES O
, HZ_RELATIONSHIPS REL
, AR_LOOKUPS ROLE_TYPE
, HZ_PARTIES P
WHERE CAR.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CAR.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_ID = P.PARTY_ID
AND CA.PARTY_ID = O.PARTY_ID
AND REL.OBJECT_ID = O.PARTY_ID
AND O.STATUS = 'A'
AND P.STATUS = 'A'
AND CA.STATUS = 'A'
AND CAR.STATUS = 'A'
AND CAR.ROLE_TYPE = ROLE_TYPE.LOOKUP_CODE
AND ROLE_TYPE.LOOKUP_TYPE = 'ACCT_ROLE_TYPE' ORDER BY ACCOUNT_NUMBER