GL_HIERVIEW_PERSON_INFO

Details

Schema: FUSION
Object owner: GL
Object type: VIEW

Columns

Name
PERSON_DISPLAY_NAME PRIMARY_EMAIL_ADDRESS PRIMARY_PHONE DEPARTMENT_NAME JOB_NAME ADDRESS_LINE_1 ADDRESS_LINE_2 ADDRESS_LINE_3 TOWN_OR_CITY COUNTRY PERSON_ID POSTAL_CODE

Name

PERSON_DISPLAY_NAME

PRIMARY_EMAIL_ADDRESS

PRIMARY_PHONE

DEPARTMENT_NAME

JOB_NAME

ADDRESS_LINE_1

ADDRESS_LINE_2

ADDRESS_LINE_3

TOWN_OR_CITY

COUNTRY

PERSON_ID

POSTAL_CODE

Query

SQL_Statement
Select PersonName.DISPLAY_NAME AS PERSON_DISPLAY_NAME, EmailAddress.EMAIL_ADDRESS AS PRIMARY_EMAIL_ADDRESS, PER_PHONE_FORMAT_PKG.GET_DISPLAY_PHONE(Phone.PHONE_ID,'phones', NULL ) AS PRIMARY_PHONE, Department.NAME AS DEPARTMENT_NAME, Job.NAME AS JOB_NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3 , TOWN_OR_CITY, COUNTRY, Person.PERSON_ID, POSTAL_CODE FROM PER_ALL_PEOPLE_F Person, PER_PHONES Phone, PER_PERSON_NAMES_F_V PersonName, PER_EMAIL_ADDRESSES EmailAddress, PER_ALL_ASSIGNMENTS_M Assignment, HR_ORGANIZATION_UNITS_F_TL Department, PER_JOBS_F_TL Job, PER_ADDRESSES_F Addresses WHERE Assignment.ASSIGNMENT_TYPE IN ('E','C','N') AND TRUNC(SYSDATE) BETWEEN Assignment.EFFECTIVE_START_DATE AND Assignment.EFFECTIVE_END_DATE AND Assignment.EFFECTIVE_LATEST_CHANGE = 'Y' AND Assignment.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND Assignment.PERSON_ID = Person.PERSON_ID AND Person.PERSON_ID = PersonName.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PersonName.EFFECTIVE_START_DATE AND PersonName.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN Person.EFFECTIVE_START_DATE AND Person.EFFECTIVE_END_DATE AND Person.PRIMARY_PHONE_ID = Phone.PHONE_ID (+) AND Person.PRIMARY_EMAIL_ID = EmailAddress.EMAIL_ADDRESS_ID (+) AND Assignment.ORGANIZATION_ID = Department.ORGANIZATION_ID (+) AND TRUNC(SYSDATE) BETWEEN Department.EFFECTIVE_START_DATE (+) AND Department.EFFECTIVE_END_DATE (+) AND SYS_CONTEXT('USERENV','LANG') = Department.LANGUAGE (+) AND Assignment.JOB_ID = Job.JOB_ID (+) AND TRUNC(SYSDATE) BETWEEN Job.EFFECTIVE_START_DATE (+) AND Job.EFFECTIVE_END_DATE (+) AND SYS_CONTEXT('USERENV','LANG') = Job.LANGUAGE (+) AND Person.MAILING_ADDRESS_ID = Addresses.Address_Id (+) AND TRUNC(SYSDATE) BETWEEN Addresses.EFFECTIVE_START_DATE (+) AND Addresses.EFFECTIVE_END_DATE (+)

SQL_Statement

Select PersonName.DISPLAY_NAME AS PERSON_DISPLAY_NAME,

EmailAddress.EMAIL_ADDRESS AS PRIMARY_EMAIL_ADDRESS,

PER_PHONE_FORMAT_PKG.GET_DISPLAY_PHONE(Phone.PHONE_ID,'phones', NULL ) AS PRIMARY_PHONE,

Department.NAME AS DEPARTMENT_NAME,

Job.NAME AS JOB_NAME,

ADDRESS_LINE_1,

ADDRESS_LINE_2,

ADDRESS_LINE_3 ,

TOWN_OR_CITY,

COUNTRY,

Person.PERSON_ID,

POSTAL_CODE

FROM PER_ALL_PEOPLE_F Person,

PER_PHONES Phone,

PER_PERSON_NAMES_F_V PersonName,

PER_EMAIL_ADDRESSES EmailAddress,

PER_ALL_ASSIGNMENTS_M Assignment,

HR_ORGANIZATION_UNITS_F_TL Department,

PER_JOBS_F_TL Job,

PER_ADDRESSES_F Addresses

WHERE Assignment.ASSIGNMENT_TYPE IN ('E','C','N')

AND TRUNC(SYSDATE) BETWEEN Assignment.EFFECTIVE_START_DATE AND Assignment.EFFECTIVE_END_DATE AND Assignment.EFFECTIVE_LATEST_CHANGE = 'Y' AND Assignment.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND Assignment.PERSON_ID = Person.PERSON_ID AND Person.PERSON_ID = PersonName.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PersonName.EFFECTIVE_START_DATE AND PersonName.EFFECTIVE_END_DATE AND TRUNC(SYSDATE) BETWEEN Person.EFFECTIVE_START_DATE AND Person.EFFECTIVE_END_DATE AND Person.PRIMARY_PHONE_ID = Phone.PHONE_ID (+) AND Person.PRIMARY_EMAIL_ID = EmailAddress.EMAIL_ADDRESS_ID (+) AND Assignment.ORGANIZATION_ID = Department.ORGANIZATION_ID (+) AND TRUNC(SYSDATE) BETWEEN Department.EFFECTIVE_START_DATE (+) AND Department.EFFECTIVE_END_DATE (+) AND SYS_CONTEXT('USERENV','LANG') = Department.LANGUAGE (+) AND Assignment.JOB_ID = Job.JOB_ID (+) AND TRUNC(SYSDATE) BETWEEN Job.EFFECTIVE_START_DATE (+) AND Job.EFFECTIVE_END_DATE (+) AND SYS_CONTEXT('USERENV','LANG') = Job.LANGUAGE (+) AND Person.MAILING_ADDRESS_ID = Addresses.Address_Id (+) AND TRUNC(SYSDATE) BETWEEN Addresses.EFFECTIVE_START_DATE (+) AND Addresses.EFFECTIVE_END_DATE (+)