VIEW: APPS.AMS_ORG_EMPS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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