VIEW: APPS.PA_PARTY_RESOURCE_DETAILS_V

Source

View Text - Preformatted

SELECT hzp.party_id party_id, hzp.party_name party_name, hzp.party_type party_type, hzp.email_address email_address, hzp.primary_phone_number phone_number, hzp.address1 address1, hzp.address2 address2, hzp.address3 address3, hzp.address4 address4, hzp.city city, hzp.county county, hzp.state state, hzp.province province, hzp.postal_code postal_code, hzp.country country, hzo.party_id org_party_id, hzo.party_name org_name, hzr.start_date, hzr.end_date FROM hz_parties hzp, hz_parties hzo, hz_relationships hzr WHERE hzp.party_type = 'PERSON' AND hzo.party_type = 'ORGANIZATION' AND hzr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF') AND hzr.status = 'A' AND hzr.subject_id = hzp.party_id AND hzr.object_id = hzo.party_id and hzr.subject_type = 'PERSON' and hzr.object_table_name = 'HZ_PARTIES' and hzr.subject_table_name = 'HZ_PARTIES' UNION ALL SELECT hzo.party_id party_id, hzo.party_name party_name, hzo.party_type party_type, hzo.email_address email_address, hzo.primary_phone_number phone_number, hzo.address1 address1, hzo.address2 address2, hzo.address3 address3, hzo.address4 address4, hzo.city city, hzo.county county, hzo.state state, hzo.province province, hzo.postal_code postal_code, hzo.country country, -999 org_party_id, NULL org_name, hzo.creation_date, TO_DATE(NULL) FROM hz_parties hzo WHERE hzo.party_type = 'ORGANIZATION'

View Text - HTML Formatted

SELECT HZP.PARTY_ID PARTY_ID
, HZP.PARTY_NAME PARTY_NAME
, HZP.PARTY_TYPE PARTY_TYPE
, HZP.EMAIL_ADDRESS EMAIL_ADDRESS
, HZP.PRIMARY_PHONE_NUMBER PHONE_NUMBER
, HZP.ADDRESS1 ADDRESS1
, HZP.ADDRESS2 ADDRESS2
, HZP.ADDRESS3 ADDRESS3
, HZP.ADDRESS4 ADDRESS4
, HZP.CITY CITY
, HZP.COUNTY COUNTY
, HZP.STATE STATE
, HZP.PROVINCE PROVINCE
, HZP.POSTAL_CODE POSTAL_CODE
, HZP.COUNTRY COUNTRY
, HZO.PARTY_ID ORG_PARTY_ID
, HZO.PARTY_NAME ORG_NAME
, HZR.START_DATE
, HZR.END_DATE
FROM HZ_PARTIES HZP
, HZ_PARTIES HZO
, HZ_RELATIONSHIPS HZR
WHERE HZP.PARTY_TYPE = 'PERSON'
AND HZO.PARTY_TYPE = 'ORGANIZATION'
AND HZR.RELATIONSHIP_CODE IN ('EMPLOYEE_OF'
, 'CONTACT_OF')
AND HZR.STATUS = 'A'
AND HZR.SUBJECT_ID = HZP.PARTY_ID
AND HZR.OBJECT_ID = HZO.PARTY_ID
AND HZR.SUBJECT_TYPE = 'PERSON'
AND HZR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZR.SUBJECT_TABLE_NAME = 'HZ_PARTIES' UNION ALL SELECT HZO.PARTY_ID PARTY_ID
, HZO.PARTY_NAME PARTY_NAME
, HZO.PARTY_TYPE PARTY_TYPE
, HZO.EMAIL_ADDRESS EMAIL_ADDRESS
, HZO.PRIMARY_PHONE_NUMBER PHONE_NUMBER
, HZO.ADDRESS1 ADDRESS1
, HZO.ADDRESS2 ADDRESS2
, HZO.ADDRESS3 ADDRESS3
, HZO.ADDRESS4 ADDRESS4
, HZO.CITY CITY
, HZO.COUNTY COUNTY
, HZO.STATE STATE
, HZO.PROVINCE PROVINCE
, HZO.POSTAL_CODE POSTAL_CODE
, HZO.COUNTRY COUNTRY
, -999 ORG_PARTY_ID
, NULL ORG_NAME
, HZO.CREATION_DATE
, TO_DATE(NULL)
FROM HZ_PARTIES HZO
WHERE HZO.PARTY_TYPE = 'ORGANIZATION'