VIEW: APPS.CS_SR_HZ_CUST_CONT_V

Source

View Text - Preformatted

SELECT p.party_id, p.party_number, p.party_type, p.party_name, p.person_first_name, p.person_last_name, a.account_number, a.account_name, l.meaning party_type_meaning from hz_parties p, hz_cust_accounts a, cs_lookups l WHERE p.party_type = 'PERSON' and a.party_id(+) = p.party_id and l.lookup_type = 'CS_SR_CONTACT_TYPE' and l.lookup_code = p.party_type and p.status IN ('A', 'I') union all select r.party_id, r.party_number, r.party_type, r.party_name, p.person_first_name, p.person_last_name, a.account_number, a.account_name, l.meaning party_type_meaning from hz_parties p, hz_parties r, hz_relationships rel, hz_cust_accounts a, cs_lookups l where rel.party_id = r.party_id and rel.subject_id = p.party_id and r.party_type = 'PARTY_RELATIONSHIP' and p.party_type = 'PERSON' and a.party_id(+) = p.party_id and l.lookup_type = 'CS_SR_CONTACT_TYPE' and l.lookup_code = r.party_type and p.status IN ('A', 'I') and r.status IN ('A', 'I')

View Text - HTML Formatted

SELECT P.PARTY_ID
, P.PARTY_NUMBER
, P.PARTY_TYPE
, P.PARTY_NAME
, P.PERSON_FIRST_NAME
, P.PERSON_LAST_NAME
, A.ACCOUNT_NUMBER
, A.ACCOUNT_NAME
, L.MEANING PARTY_TYPE_MEANING
FROM HZ_PARTIES P
, HZ_CUST_ACCOUNTS A
, CS_LOOKUPS L
WHERE P.PARTY_TYPE = 'PERSON'
AND A.PARTY_ID(+) = P.PARTY_ID
AND L.LOOKUP_TYPE = 'CS_SR_CONTACT_TYPE'
AND L.LOOKUP_CODE = P.PARTY_TYPE
AND P.STATUS IN ('A'
, 'I') UNION ALL SELECT R.PARTY_ID
, R.PARTY_NUMBER
, R.PARTY_TYPE
, R.PARTY_NAME
, P.PERSON_FIRST_NAME
, P.PERSON_LAST_NAME
, A.ACCOUNT_NUMBER
, A.ACCOUNT_NAME
, L.MEANING PARTY_TYPE_MEANING
FROM HZ_PARTIES P
, HZ_PARTIES R
, HZ_RELATIONSHIPS REL
, HZ_CUST_ACCOUNTS A
, CS_LOOKUPS L
WHERE REL.PARTY_ID = R.PARTY_ID
AND REL.SUBJECT_ID = P.PARTY_ID
AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND P.PARTY_TYPE = 'PERSON'
AND A.PARTY_ID(+) = P.PARTY_ID
AND L.LOOKUP_TYPE = 'CS_SR_CONTACT_TYPE'
AND L.LOOKUP_CODE = R.PARTY_TYPE
AND P.STATUS IN ('A'
, 'I')
AND R.STATUS IN ('A'
, 'I')