View: AMS_ORG_EMPS_V

Product:	AMS - Marketing
Description:	This view stores the details of contacts who are employees
Implementation/DBA Data:	APPS.AMS_ORG_EMPS_V

View Text

SELECT R.SUBJECT_ID
, R.OBJECT_ID
, R.PARTY_ID
, P.PARTY_TYPE
, R.RELATIONSHIP_ID
, R.RELATIONSHIP_TYPE
, P.PARTY_NUMBER
, P.PARTY_NAME
, P.EMAIL_ADDRESS
, P.VALIDATED_FLAG
, P.CREATION_DATE
, P.CREATED_BY
, P.LAST_UPDATE_DATE
, P.LAST_UPDATED_BY
, P.LAST_UPDATE_LOGIN
, P.REQUEST_ID
, P.PROGRAM_APPLICATION_ID
, P.PROGRAM_ID
, P.PROGRAM_UPDATE_DATE
, P.WH_UPDATE_DATE
, P.ORIG_SYSTEM_REFERENCE
, P.CUSTOMER_KEY
, P.DUNS_NUMBER
, P.SIC_CODE
, P.SIC_CODE_TYPE
, P.HQ_BRANCH_IND
, P.TAX_NAME
, P.TAX_REFERENCE
, P.JGZZ_FISCAL_CODE
, P.GROUP_TYPE
, P.PERSON_PRE_NAME_ADJUNCT
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_NAME_SUFFIX
, P.PERSON_TITLE
, P.PERSON_ACADEMIC_TITLE
, P.PERSON_PREVIOUS_LAST_NAME
, P.KNOWN_AS
, P.PERSON_IDEN_TYPE
, P.PERSON_IDENTIFIER
, P.ADDRESS1
, P.ADDRESS2
, P.ADDRESS3
, P.ADDRESS4
, P.CITY
, P.STATE
, P.PROVINCE
, P.COUNTY
, P.POSTAL_CODE
, P.COUNTRY
, LANG. TERRITORY_SHORT_NAME COUNTRY_NAME
, P.STATUS
, P.CREATED_BY CREATED_BY_NAME
, P.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_NAME
, P.LAST_UPDATED_BY LAST_UPDATED_BY_NAME
FROM HZ_PARTIES P
, HZ_RELATIONSHIPS R
, HZ_PARTIES P1
, HZ_PARTIES P2
, FND_TERRITORIES_TL LANG
WHERE R.RELATIONSHIP_TYPE = 'EMPLOYEE_OF'
AND R.SUBJECT_ID = P.PARTY_ID
AND R.OBJECT_ID= P2. PARTY_ID
AND P2.STATUS = 'A'
AND P2.PARTY_TYPE = 'ORGANIZATION'
AND R.PARTY_ID = P1. PARTY_ID
AND R.ACTUAL_CONTENT_SOURCE = 'USER_ENTERED'
AND P1.STATUS = 'A'
AND P1 . PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND LANG. LANGUAGE = USERENV('LANG')
AND LANG.TERRITORY_CODE = P.COUNTRY

Columns