VIEW: APPS.AMW_PERSON_COMPANY_V

Source

View Text - Preformatted

SELECT P.PARTY_ID PERSON_ID, HZ_FORMAT_PUB.format_name(P.PARTY_ID) PERSON_NAME, R.OBJECT_ID COMPANY_ID, (SELECT PARTY_NAME FROM HZ_PARTIES WHERE PARTY_ID = R.OBJECT_ID) COMPANY_NAME FROM HZ_PARTIES P, HZ_RELATIONSHIPS R WHERE R.SUBJECT_ID(+) = P.PARTY_ID AND R.SUBJECT_TYPE(+) = 'PERSON' AND R.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND R.OBJECT_TYPE(+) = 'ORGANIZATION' AND R.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND R.RELATIONSHIP_CODE(+) = 'EMPLOYEE_OF' AND R.START_DATE(+) <= SYSDATE AND R.STATUS(+) = 'A' AND (R.END_DATE >= SYSDATE OR R.END_DATE IS NULL)

View Text - HTML Formatted

SELECT P.PARTY_ID PERSON_ID
, HZ_FORMAT_PUB.FORMAT_NAME(P.PARTY_ID) PERSON_NAME
, R.OBJECT_ID COMPANY_ID
, (SELECT PARTY_NAME
FROM HZ_PARTIES
WHERE PARTY_ID = R.OBJECT_ID) COMPANY_NAME
FROM HZ_PARTIES P
, HZ_RELATIONSHIPS R
WHERE R.SUBJECT_ID(+) = P.PARTY_ID
AND R.SUBJECT_TYPE(+) = 'PERSON'
AND R.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND R.OBJECT_TYPE(+) = 'ORGANIZATION'
AND R.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND R.RELATIONSHIP_CODE(+) = 'EMPLOYEE_OF'
AND R.START_DATE(+) <= SYSDATE
AND R.STATUS(+) = 'A'
AND (R.END_DATE >= SYSDATE OR R.END_DATE IS NULL)