VIEW: APPS.AMS_PERSON_RELATIONSHIPS_V

Source

View Text - Preformatted

SELECT prsn.party_id ,pr.party_id ,prsn.party_name ,prsn.person_last_name ,prsn.person_first_name ,prsn.person_middle_name ,prsn.known_as ,pr.status ,prsn.last_ordered_date ,pr.object_id ,party.party_type ,party.party_name ,pr.relationship_id ,pr.relationship_type , cpph.phone_country_code || DECODE(cpph.phone_area_code, NULL, '', ' '||cpph.phone_area_code) || DECODE(cpph.phone_number, NULL, '', ' '||cpph.phone_number) || DECODE(cpph.phone_extension, NULL, '', ' ext. '||cpph.phone_extension) ,cpph.phone_country_code ,cpph.phone_area_code ,cpph.phone_number ,cpph.phone_extension ,cpph.phone_line_type ,relparty.email_address ,relparty.address1 || DECODE(relparty.address2, NULL, '', ' '||relparty.address2) || DECODE(relparty.address3, NULL, '', ' '||relparty.address3) || DECODE(relparty.address4, NULL, '', ' '||relparty.address4) || ', '||relparty.city ||', '||relparty.state||', '||relparty.postal_code||', '||relparty.country ,relparty.address1 || DECODE(relparty.address2, NULL, '', ' '||relparty.address2) || DECODE(relparty.address3, NULL, '', ' '||relparty.address3) || DECODE(relparty.address4, NULL, '', ' '||relparty.address4) ,relparty.address1 ,relparty.address2 ,relparty.address3 ,relparty.address4 ,relparty.state ,relparty.city ,relparty.province ,relparty.county ,relparty.postal_code ,relparty.country ,prsn.creation_date ,prsn.last_update_date ,prsn.created_by ,prsn.last_updated_by FROM hz_parties prsn ,hz_parties party ,hz_parties relparty ,hz_contact_points cpph ,hz_org_contacts c ,hz_relationships pr WHERE prsn.party_type = 'PERSON' AND pr.status='A' AND prsn.status='A' AND party.status='A' AND prsn.party_id = pr.subject_id AND pr.object_id = party.party_id AND pr.party_id = relparty.party_id AND pr.party_id = cpph.owner_table_id (+) AND cpph.owner_table_name (+) = 'HZ_PARTIES' AND cpph.contact_point_type (+) = 'PHONE' AND cpph.primary_flag (+) = 'Y' AND cpph.ACTUAL_CONTENT_SOURCE(+) = 'USER_ENTERED' AND pr.object_type='ORGANIZATION' AND PR.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED' AND pr.relationship_code IN('CONTACT','CONTACT_OF','EMPLOYEE_OF') AND c.party_relationship_id=pr.relationship_id

View Text - HTML Formatted

SELECT PRSN.PARTY_ID
, PR.PARTY_ID
, PRSN.PARTY_NAME
, PRSN.PERSON_LAST_NAME
, PRSN.PERSON_FIRST_NAME
, PRSN.PERSON_MIDDLE_NAME
, PRSN.KNOWN_AS
, PR.STATUS
, PRSN.LAST_ORDERED_DATE
, PR.OBJECT_ID
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PR.RELATIONSHIP_ID
, PR.RELATIONSHIP_TYPE
, CPPH.PHONE_COUNTRY_CODE || DECODE(CPPH.PHONE_AREA_CODE
, NULL
, ''
, ' '||CPPH.PHONE_AREA_CODE) || DECODE(CPPH.PHONE_NUMBER
, NULL
, ''
, ' '||CPPH.PHONE_NUMBER) || DECODE(CPPH.PHONE_EXTENSION
, NULL
, ''
, ' EXT. '||CPPH.PHONE_EXTENSION)
, CPPH.PHONE_COUNTRY_CODE
, CPPH.PHONE_AREA_CODE
, CPPH.PHONE_NUMBER
, CPPH.PHONE_EXTENSION
, CPPH.PHONE_LINE_TYPE
, RELPARTY.EMAIL_ADDRESS
, RELPARTY.ADDRESS1 || DECODE(RELPARTY.ADDRESS2
, NULL
, ''
, ' '||RELPARTY.ADDRESS2) || DECODE(RELPARTY.ADDRESS3
, NULL
, ''
, ' '||RELPARTY.ADDRESS3) || DECODE(RELPARTY.ADDRESS4
, NULL
, ''
, ' '||RELPARTY.ADDRESS4) || '
, '||RELPARTY.CITY ||'
, '||RELPARTY.STATE||'
, '||RELPARTY.POSTAL_CODE||'
, '||RELPARTY.COUNTRY
, RELPARTY.ADDRESS1 || DECODE(RELPARTY.ADDRESS2
, NULL
, ''
, ' '||RELPARTY.ADDRESS2) || DECODE(RELPARTY.ADDRESS3
, NULL
, ''
, ' '||RELPARTY.ADDRESS3) || DECODE(RELPARTY.ADDRESS4
, NULL
, ''
, ' '||RELPARTY.ADDRESS4)
, RELPARTY.ADDRESS1
, RELPARTY.ADDRESS2
, RELPARTY.ADDRESS3
, RELPARTY.ADDRESS4
, RELPARTY.STATE
, RELPARTY.CITY
, RELPARTY.PROVINCE
, RELPARTY.COUNTY
, RELPARTY.POSTAL_CODE
, RELPARTY.COUNTRY
, PRSN.CREATION_DATE
, PRSN.LAST_UPDATE_DATE
, PRSN.CREATED_BY
, PRSN.LAST_UPDATED_BY
FROM HZ_PARTIES PRSN
, HZ_PARTIES PARTY
, HZ_PARTIES RELPARTY
, HZ_CONTACT_POINTS CPPH
, HZ_ORG_CONTACTS C
, HZ_RELATIONSHIPS PR
WHERE PRSN.PARTY_TYPE = 'PERSON'
AND PR.STATUS='A'
AND PRSN.STATUS='A'
AND PARTY.STATUS='A'
AND PRSN.PARTY_ID = PR.SUBJECT_ID
AND PR.OBJECT_ID = PARTY.PARTY_ID
AND PR.PARTY_ID = RELPARTY.PARTY_ID
AND PR.PARTY_ID = CPPH.OWNER_TABLE_ID (+)
AND CPPH.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CPPH.CONTACT_POINT_TYPE (+) = 'PHONE'
AND CPPH.PRIMARY_FLAG (+) = 'Y'
AND CPPH.ACTUAL_CONTENT_SOURCE(+) = 'USER_ENTERED'
AND PR.OBJECT_TYPE='ORGANIZATION'
AND PR.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'
AND PR.RELATIONSHIP_CODE IN('CONTACT'
, 'CONTACT_OF'
, 'EMPLOYEE_OF')
AND C.PARTY_RELATIONSHIP_ID=PR.RELATIONSHIP_ID