SQL Query to fetch employee person and assignment info

If the integration requires a full file every time and if the vendor handles the changes based upon the file then we can go with creating a BI Report.

SQL Query to get the Person Demographic Information in BI Report:

SELECT PAPF.PERSON_NUMBER
,(SELECT MAX(FTB.NLS_TERRITORY)
FROM FND_TERRITORIES_B FTB
WHERE FTB.TERRITORY_CODE = ADDR_M.COUNTRY) WORK_COUNTRY
,NAME_G.TITLE glb_title
,NAME_G.FIRST_NAME glb_first_name
,NAME_G.MIDDLE_NAMES glb_middle_name
,NAME_G.LAST_NAME glb_last_name
,NAME_G.PRE_NAME_ADJUNCT glb_prefix
,NAME_G.SUFFIX glb_suffix
,NAME_G.HONORS glb_honors
,NAME_G.PREVIOUS_LAST_NAME glb_prev_name
,NAME_G.KNOWN_AS glb_known_as
,NAME_AR.TITLE loc_title
,NAME_AR.FIRST_NAME loc_firstname
,NAME_AR.MIDDLE_NAMES loc_middle_name
,NAME_AR.LAST_NAME loc_last_name
,NAME_AR.PRE_NAME_ADJUNCT loc_prefix
,NAME_AR.SUFFIX loc_suffix
,NAME_AR.HONORS loc_honors
,NAME_AR.KNOWN_AS loc_known_as
,NAME_AR.PREVIOUS_LAST_NAME loc_prev_last_name
,(SELECT MAX(FTB.NLS_TERRITORY)
FROM FND_TERRITORIES_B FTB
WHERE FTB.TERRITORY_CODE = ADDR_H.COUNTRY) HOME_Country
,ADDR_U_H.ADDRESS_TYPE
,ADDR_H.ADDRESS_LINE_1 HOME_ADDRESS_LINE_1
,ADDR_H.ADDRESS_LINE_2 HOME_ADDRESS_LINE_2
,ADDR_H.ADDRESS_LINE_3 HOME_ADDRESS_LINE_3
,ADDR_H.ADDRESS_LINE_4 HOME_ADDRESS_LINE_4
,ADDR_H.REGION_1
,ADDR_H.REGION_2
,ADDR_H.REGION_3
,ADDR_H.POSTAL_CODE
,ADDR_H.TOWN_OR_CITY
,PHONE_H.PHONE_TYPE "Phone Type"
,PHONE_H.COUNTRY_CODE_NUMBER " Country Code"
,PHONE_H.AREA_CODE " Area Code"
,PHONE_H.PHONE_NUMBER " Phone Number"
,PHONE_W.PHONE_TYPE " Phone Type 1"
,PHONE_W.COUNTRY_CODE_NUMBER
,(SELECT MAX(FL.MEANING)
FROM HR_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'EMAIL_TYPE'
AND FL.LOOKUP_CODE = EMAIL.EMAIL_TYPE) "E-Mail Type"
,EMAIL.EMAIL_ADDRESS " E-Mail"
,PPLF.LEGISLATION_CODE "Legislation"
,PPLF.PER_INFORMATION1
,(
SELECT MAX(FL.MEANING)
FROM HR_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'MAR_STATUS'
AND FL.LOOKUP_CODE = PPLF.MARITAL_STATUS
) "Marital Status"
,(
SELECT MAX(FL.MEANING)
FROM FND_LOOKUP_VALUES_TL FL
WHERE FL.LOOKUP_TYPE = 'PER_RELIGION'
AND FL.LOOKUP_CODE(+) = PR.RELIGION
AND FL.LANGUAGE = USERENV('LANG')
) "Religion"
,DECODE(PPLF.SEX, 'M', 'Male', 'F', 'Female', PPLF.SEX) "Gender"
,(
SELECT MAX(FL.MEANING)
FROM FND_LOOKUP_VALUES_TL FL
WHERE FL.LOOKUP_TYPE = 'PER_HIGHEST_EDUCATION_LEVEL'
AND FL.LOOKUP_CODE = PPLF.HIGHEST_EDUCATION_LEVEL
AND FL.LANGUAGE = USERENV('LANG')
) "Highest Education Level"
,PAPF.PERSON_ID
,PAPF.ATTRIBUTE_CATEGORY
,PAPF.ATTRIBUTE1 "PERSON ATTRIBUTE1"
,PAPF.ATTRIBUTE2 "PERSON ATTRIBUTE2"
,PAPF.ATTRIBUTE3 "PERSON ATTRIBUTE3"
,PAPF.ATTRIBUTE4 "PERSON ATTRIBUTE4"
,PAPF.ATTRIBUTE5 "PERSON ATTRIBUTE5"
,PAPF.ATTRIBUTE6 "PERSON ATTRIBUTE6"
,PPOS.WORKER_NUMBER
,PPOS.LAST_WORKING_DATE
,PPOS.ACTUAL_TERMINATION_DATE
,PPOS.ON_MILITARY_SERVICE
,TO_CHAR(PPOS.DATE_START, 'DD-MON-YYYY') HIRE_DATE
,TO_CHAR(PAPF.EFFECTIVE_START_DATE, 'DD-MON-YYYY') EFFECTIVE_START_DATE
,TO_CHAR(PER.DATE_OF_BIRTH, 'DD-MON-YYYY') DATE_OF_BIRTH
,TRUNC(MONTHS_BETWEEN(SYSDATE, PER.DATE_OF_BIRTH) / 12) || '.' ||
TRUNC((MONTHS_BETWEEN(SYSDATE, PER.DATE_OF_BIRTH))
- (TRUNC(MONTHS_BETWEEN(SYSDATE, PER.DATE_OF_BIRTH) / 12) * 12)) AGE
,PPTL.USER_PERSON_TYPE PERSON_TYPE
,(
SELECT MAX(FT.NLS_TERRITORY)
FROM FND_TERRITORIES_B FT
WHERE FT.TERRITORY_CODE = PNI.LEGISLATION_CODE
) NATIONAL_IDENTIFIER_COUNTRY
,(
SELECT MAX(FL.MEANING)
FROM HR_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'PER_NATIONAL_IDENTIFIER_TYPE'
AND FL.LOOKUP_CODE = PNI.NATIONAL_IDENTIFIER_TYPE
) NATIONAL_IDENTIFIER_TYPE
,TO_CHAR(PNI.NATIONAL_IDENTIFIER_NUMBER) NATIONAL_IDENTIFIER_NUMBER
,(
SELECT MAX(FL.MEANING)
FROM FND_LOOKUP_VALUES_TL FL
WHERE FL.LOOKUP_TYPE = 'NATIONALITY'
AND FL.LOOKUP_CODE(+) = PCZ.LEGISLATION_CODE
AND FL.LANGUAGE = USERENV('LANG')
) NATIONALITY
,TO_CHAR(PCZ.DATE_FROM, 'DD-MON-YYYY') NATIONALITY_FROM_DATE
,TO_CHAR(PCZ.DATE_TO, 'DD-MON-YYYY') NATIONALITY_TO_DATE
,DECODE(PCZ.CITIZENSHIP_STATUS, 'A', 'Active', 'I', 'Inactive') NATIONALITY_STATUS
,(
SELECT MAX(FL.MEANING)
FROM HR_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'EMPLOYEE_CATG'
AND FL.LOOKUP_CODE = ASG_ACT.EMPLOYEE_CATEGORY
) WORKER_TYPE
,(
SELECT MAX(FL.MEANING)
FROM HR_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'EMP_CAT'
AND FL.LOOKUP_CODE = ASG_ACT.EMPLOYMENT_CATEGORY
) ASSIGNMENT_CATEGORY
,DECODE(ASG_ACT.WORK_AT_HOME, 'Y', 'Yes', 'N', 'No') WORK_AT_HOME
,DECODE(ASG_ACT.MANAGER_FLAG, 'Y', 'Yes', 'N', 'No') WORKING_AS_MANAGER
,DECODE(ASG_ACT.HOURLY_SALARIED_CODE, 'S', 'Salaried', 'H', 'Hourly Paid') HOURLY_PAID_OR_SALARIED
,ASG_ACT.NORMAL_HOURS WORKING_HOURS
,(
SELECT MAX(FL.MEANING)
FROM FND_LOOKUP_VALUES_TL FL
WHERE FL.LOOKUP_TYPE = 'FREQUENCY'
AND FL.LOOKUP_CODE = ASG_ACT.FREQUENCY
AND FL.LANGUAGE = USERENV('LANG')
) FREQUENCY
,ASG_ACT.TIME_NORMAL_START START_TIME
,ASG_ACT.TIME_NORMAL_FINISH END_TIME
,ASG_ACT.PROBATION_PERIOD
,(
SELECT MAX(FL.MEANING)
FROM HR_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'PROBATION_PERIOD'
AND FL.LOOKUP_CODE = ASG_ACT.PROBATION_UNIT
) PROBATION_UNIT
,ASG_ACT.NOTICE_PERIOD
,(
SELECT MAX(FL.MEANING)
FROM HR_LOOKUPS FL
WHERE FL.LOOKUP_TYPE = 'PROBATION_PERIOD'
AND FL.LOOKUP_CODE = ASG_ACT.NOTICE_PERIOD_UOM
) NOTICE_PERIOD_UOM
,TRUNC(MONTHS_BETWEEN(SYSDATE, PPOS.DATE_START) / 12) || '.' ||
TRUNC((MONTHS_BETWEEN(SYSDATE, PPOS.DATE_START))
- (TRUNC(MONTHS_BETWEEN(SYSDATE, PPOS.DATE_START) / 12) * 12)) YR_N_MON
,(
SELECT SAL.SALARY_AMOUNT
FROM CMP_SALARY SAL
WHERE SAL.ASSIGNMENT_ID = ASG_ACT.ASSIGNMENT_ID
AND TRUNC(SYSDATE) BETWEEN NVL(SAL.DATE_FROM, SYSDATE)
AND NVL(SAL.DATE_TO, SYSDATE)
) SALARY_AMOUNT
,(
SELECT CSB.NAME
FROM CMP_SALARY SAL
,CMP_SALARY_BASES CSB
WHERE CSB.SALARY_BASIS_ID = SAL.SALARY_BASIS_ID
AND SAL.ASSIGNMENT_ID = ASG_ACT.ASSIGNMENT_ID
AND TRUNC(SYSDATE) BETWEEN NVL(SAL.DATE_FROM, SYSDATE)
AND NVL(SAL.DATE_TO, SYSDATE)
) SALARY_BASIS
,ASG_ACT.PEOPLE_GROUP_ID
,PPG.GROUP_NAME PEOPLE_GROUP
,PPG.SEGMENT1 "PEOPLE_G_NAME"
,ASG_ACT.RETIREMENT_AGE
,TO_CHAR(ASG_ACT.RETIREMENT_DATE, 'DD-MON-YYYY')
,PJ.JOB_CODE
,PJ.NAME JOB_NAME
,HAP.POSITION_CODE
,HAP.NAME POSITION_NAME
,HAP.BARGAINING_UNIT_CD
,PG.GRADE_CODE
,PG.NAME GRADE_NAME
,MGRP.DISPLAY_NAME MANAGER_NAME
,TO_CHAR(MGR_PAPF.PERSON_NUMBER) MANAGER_PERSON_NUMBER
,PLDF.LOCATION_CODE
,PLDF.LOCATION_NAME
,MGR_PLDF.LOCATION_NAME MGR_LOCATION_NAME
,PASTTL.USER_STATUS ASSIGNMENT_STATUS
,HR_ORG.NAME ORGANIZATION_NAME
,LEGAL_ENTITY.NAME LEGAL_ENTITY
,BU_UNIT.NAME BUSINESS_UNIT
,ASG_ACT.ASS_ATTRIBUTE1
,ASG_ACT.ASS_ATTRIBUTE2
,ASG_ACT.ASS_ATTRIBUTE3
,ASG_ACT.ASS_ATTRIBUTE4
,ASG_ACT.ASS_ATTRIBUTE5
,ASG_ACT.ASS_ATTRIBUTE6
FROM PER_ALL_PEOPLE_F PAPF
,PER_PERSONS PER
,PER_PERSON_NAMES_F NAME_G
,PER_PERSON_NAMES_F NAME_AR
,PER_EMAIL_ADDRESSES EMAIL
,PER_PHONES PHONE_H
,PER_PHONES PHONE_W
,PER_RELIGIONS PR
,PER_PERSON_ADDR_USAGES_F ADDR_U_H
,PER_ADDRESSES_F ADDR_H
,PER_PERSON_ADDR_USAGES_F ADDR_U_M
,PER_ADDRESSES_F ADDR_M
,PER_PEOPLE_LEGISLATIVE_F PPLF
,PER_NATIONAL_IDENTIFIERS PNI
,PER_CITIZENSHIPS PCZ
,PER_ALL_ASSIGNMENTS_F ASG_ACT
,PER_PERIODS_OF_SERVICE PPOS
,PER_PERSON_TYPE_USAGES_M PPTU
,PER_PERSON_TYPES_VL PPTL
,PER_JOBS PJ
,HR_ALL_POSITIONS HAP
,PER_GRADES PG
,PER_ASSIGNMENT_SUPERVISORS_F PASF
,PER_PERSON_NAMES_F MGRP
,PER_ALL_PEOPLE_F MGR_PAPF
,PER_ALL_ASSIGNMENTS_M MGR_ASG
,PER_LOCATION_DETAILS_F_VL PLDF
,PER_LOCATION_DETAILS_F_VL MGR_PLDF
,PER_ASSIGNMENT_STATUS_TYPES_TL PASTTL
,HR_ORGANIZATION_UNITS LEGAL_ENTITY
,HR_ORGANIZATION_UNITS HR_ORG
,HR_ORGANIZATION_UNITS BU_UNIT
,PAY_PEOPLE_GROUPS PPG
WHERE PER.PERSON_ID(+) = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF.EFFECTIVE_START_DATE, SYSDATE)
AND NVL(PAPF.EFFECTIVE_END_DATE, SYSDATE)
AND NAME_G.PERSON_ID(+) = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(NAME_G.EFFECTIVE_START_DATE, SYSDATE)
AND NVL(NAME_G.EFFECTIVE_END_DATE, SYSDATE)
AND UPPER(NAME_G.NAME_TYPE) = UPPER('GLOBAL')
AND NAME_AR.PERSON_ID = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(NAME_AR.EFFECTIVE_START_DATE, SYSDATE)
AND NVL(NAME_AR.EFFECTIVE_END_DATE, SYSDATE)
AND UPPER(NAME_AR.NAME_TYPE) <> UPPER('GLOBAL')
AND EMAIL.PERSON_ID(+) = PAPF.PERSON_ID
AND EMAIL.EMAIL_ADDRESS_ID(+) = PAPF.PRIMARY_EMAIL_ID
AND TRUNC(SYSDATE) BETWEEN NVL(EMAIL.DATE_FROM(+), SYSDATE)
AND NVL(EMAIL.DATE_TO(+), SYSDATE)
AND PHONE_H.PERSON_ID(+) = PAPF.PERSON_ID
AND PHONE_H.PHONE_ID(+) = PAPF.PRIMARY_PHONE_ID
AND UPPER(PHONE_H.PHONE_TYPE(+)) = UPPER('HM')
AND TRUNC(SYSDATE) BETWEEN NVL(PHONE_H.DATE_FROM(+), SYSDATE)
AND NVL(PHONE_H.DATE_TO(+), SYSDATE)
AND PHONE_W.PERSON_ID(+) = PAPF.PERSON_ID
AND PHONE_W.PHONE_ID(+) = PAPF.PRIMARY_PHONE_ID
AND UPPER(PHONE_W.PHONE_TYPE(+)) = UPPER('WM')
AND TRUNC(SYSDATE) BETWEEN NVL(PHONE_W.DATE_FROM(+), SYSDATE)
AND NVL(PHONE_W.DATE_TO(+), SYSDATE)
AND PR.PERSON_ID(+) = PAPF.PERSON_ID
AND ADDR_U_H.PERSON_ID(+) = PAPF.PERSON_ID
AND UPPER(ADDR_U_H.ADDRESS_TYPE(+)) = UPPER('HOME')
AND TRUNC(SYSDATE) BETWEEN NVL(ADDR_U_H.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(ADDR_U_H.EFFECTIVE_END_DATE(+), SYSDATE)
AND ADDR_H.ADDRESS_ID(+) = ADDR_U_H.ADDRESS_ID
AND TRUNC(SYSDATE) BETWEEN NVL(ADDR_H.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(ADDR_H.EFFECTIVE_END_DATE(+), SYSDATE)
AND ADDR_U_M.PERSON_ID(+) = PAPF.PERSON_ID
AND UPPER(ADDR_U_M.ADDRESS_TYPE(+)) = UPPER('MAIL')
AND TRUNC(SYSDATE) BETWEEN NVL(ADDR_U_M.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(ADDR_U_M.EFFECTIVE_END_DATE(+), SYSDATE)
AND ADDR_M.ADDRESS_ID(+) = ADDR_U_M.ADDRESS_ID
AND TRUNC(SYSDATE) BETWEEN NVL(ADDR_M.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(ADDR_M.EFFECTIVE_END_DATE(+), SYSDATE)
AND PPLF.PERSON_ID(+) = PAPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PPLF.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(PPLF.EFFECTIVE_END_DATE(+), SYSDATE)
AND PNI.PERSON_ID(+) = PAPF.PERSON_ID
AND PNI.NATIONAL_IDENTIFIER_ID(+) = PAPF.PRIMARY_NID_ID
AND PCZ.PERSON_ID(+) = PAPF.PERSON_ID
AND ASG_ACT.PERSON_ID = PAPF.PERSON_ID
AND UPPER(ASG_ACT.PRIMARY_FLAG(+)) = UPPER('Y')
AND ASG_ACT.ASSIGNMENT_TYPE IN ('E','C')
AND TRUNC(SYSDATE) BETWEEN NVL(ASG_ACT.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(ASG_ACT.EFFECTIVE_END_DATE(+), SYSDATE)
AND ASG_ACT.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PASTTL.ASSIGNMENT_STATUS_TYPE_ID(+) = ASG_ACT.ASSIGNMENT_STATUS_TYPE_ID
AND PASTTL.BUSINESS_GROUP_ID(+) = ASG_ACT.BUSINESS_GROUP_ID
AND PASTTL.LANGUAGE = USERENV('LANG')
AND PPOS.PERSON_ID(+) = ASG_ACT.PERSON_ID
AND PPOS.PERIOD_OF_SERVICE_ID(+) = ASG_ACT.PERIOD_OF_SERVICE_ID
AND (PPOS.ACTUAL_TERMINATION_DATE(+) >= TRUNC(SYSDATE)
OR PPOS.ACTUAL_TERMINATION_DATE(+) IS NULL)
AND PPTU.PERSON_ID(+) = PAPF.PERSON_ID
AND UPPER(PPTU.SYSTEM_PERSON_TYPE) = UPPER('EMP')
AND PPTU.PERSON_TYPE_ID = PPTL.PERSON_TYPE_ID
AND PJ.JOB_ID(+) = ASG_ACT.JOB_ID
AND HAP.POSITION_ID(+) = ASG_ACT.POSITION_ID
AND PG.GRADE_ID(+) = ASG_ACT.GRADE_ID
AND PASF.ASSIGNMENT_ID(+) = ASG_ACT.ASSIGNMENT_ID
AND PASF.PERSON_ID(+) = ASG_ACT.PERSON_ID
AND UPPER(PASF.PRIMARY_FLAG(+)) = UPPER('Y')
AND MGRP.PERSON_ID(+) = PASF.MANAGER_ID
AND TRUNC(SYSDATE) BETWEEN NVL(MGRP.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(MGRP.EFFECTIVE_END_DATE(+), SYSDATE)
AND MGR_PAPF.PERSON_ID(+) = PASF.MANAGER_ID
AND TRUNC(SYSDATE) BETWEEN NVL(MGR_PAPF.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(MGR_PAPF.EFFECTIVE_END_DATE(+), SYSDATE)
AND UPPER(MGRP.NAME_TYPE) = UPPER('GLOBAL')
AND MGR_ASG.ASSIGNMENT_ID(+) = PASF.MANAGER_ASSIGNMENT_ID
AND MGR_ASG.PERSON_ID(+) = PASF.MANAGER_ID
AND UPPER(MGR_ASG.ASSIGNMENT_TYPE(+)) IN ('E','C')
AND UPPER(MGR_ASG.PRIMARY_FLAG(+)) = UPPER('Y')
AND TRUNC(SYSDATE) BETWEEN NVL(MGR_ASG.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(MGR_ASG.EFFECTIVE_END_DATE(+), SYSDATE)
AND PLDF.LOCATION_ID(+) = ASG_ACT.LOCATION_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PLDF.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(PLDF.EFFECTIVE_END_DATE(+), SYSDATE)
AND MGR_PLDF.LOCATION_ID(+) = MGR_ASG.LOCATION_ID
AND TRUNC(SYSDATE) BETWEEN NVL(MGR_PLDF.EFFECTIVE_START_DATE(+), SYSDATE)
AND NVL(MGR_PLDF.EFFECTIVE_END_DATE(+), SYSDATE)
AND HR_ORG.ORGANIZATION_ID(+) = ASG_ACT.ORGANIZATION_ID
AND LEGAL_ENTITY.ORGANIZATION_ID(+) = ASG_ACT.LEGAL_ENTITY_ID
AND BU_UNIT.ORGANIZATION_ID(+) = ASG_ACT.BUSINESS_UNIT_ID
AND PPG.PEOPLE_GROUP_ID(+) = ASG_ACT.PEOPLE_GROUP_ID

Tip: The above long SQL Query is fetching data from around 33 tables and most of them are left outer joins, so even if they don’t have data you will still get data in the output.