VIEW: APPS.AST_BUSINESS_CONTACTS_V

Source

View Text - Preformatted

SELECT o.party_id , o.party_number , o.party_type , p.party_id , p.party_number , p.party_type , ar1.description , rel.relationship_id , rel.party_id , p.person_first_name , p.person_middle_name , p.person_last_name , p.party_name , o.party_name , ar2.meaning , rel.status from hz_relationships rel , hz_parties p , hz_parties o , ar_lookups ar1 , ar_lookups ar2 , ar_lookups ar3 , hz_relationship_types hz WHERE o.party_type = 'ORGANIZATION' AND ar1.lookup_type = 'HZ_RELATIONSHIP_ROLE' AND ar1.lookup_code = HZ.ROLE AND o.party_id = rel.object_id AND p.party_type = 'PERSON' AND p.party_id = rel.subject_id AND rel.status in ('A','I') AND ar2.lookup_type = 'REGISTRY_STATUS' AND ar2.lookup_code = rel.status AND ar3.lookup_code(+) = p.person_pre_name_adjunct AND ar3.lookup_type(+) = 'CONTACT_TITLE' AND rel.relationship_code = hz.forward_rel_code AND rel.relationship_type = hz.relationship_type AND rel.object_type = 'ORGANIZATION' AND rel.subject_type = 'PERSON' AND hz.subject_type = 'PERSON' AND hz.object_type = 'ORGANIZATION' AND rel.party_id is not null

View Text - HTML Formatted

SELECT O.PARTY_ID
, O.PARTY_NUMBER
, O.PARTY_TYPE
, P.PARTY_ID
, P.PARTY_NUMBER
, P.PARTY_TYPE
, AR1.DESCRIPTION
, REL.RELATIONSHIP_ID
, REL.PARTY_ID
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PARTY_NAME
, O.PARTY_NAME
, AR2.MEANING
, REL.STATUS
FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, AR_LOOKUPS AR1
, AR_LOOKUPS AR2
, AR_LOOKUPS AR3
, HZ_RELATIONSHIP_TYPES HZ
WHERE O.PARTY_TYPE = 'ORGANIZATION'
AND AR1.LOOKUP_TYPE = 'HZ_RELATIONSHIP_ROLE'
AND AR1.LOOKUP_CODE = HZ.ROLE
AND O.PARTY_ID = REL.OBJECT_ID
AND P.PARTY_TYPE = 'PERSON'
AND P.PARTY_ID = REL.SUBJECT_ID
AND REL.STATUS IN ('A'
, 'I')
AND AR2.LOOKUP_TYPE = 'REGISTRY_STATUS'
AND AR2.LOOKUP_CODE = REL.STATUS
AND AR3.LOOKUP_CODE(+) = P.PERSON_PRE_NAME_ADJUNCT
AND AR3.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND REL.RELATIONSHIP_CODE = HZ.FORWARD_REL_CODE
AND REL.RELATIONSHIP_TYPE = HZ.RELATIONSHIP_TYPE
AND REL.OBJECT_TYPE = 'ORGANIZATION'
AND REL.SUBJECT_TYPE = 'PERSON'
AND HZ.SUBJECT_TYPE = 'PERSON'
AND HZ.OBJECT_TYPE = 'ORGANIZATION'
AND REL.PARTY_ID IS NOT NULL