In this post we will look into the SQL Query to get the employee personal and assignment information which is the most frequent requirement when we have any integrations with third party systems.
If the integration has to be incremental file, then the approach would be create a changes only HCM extract and achieve the requirement.
Have a look at the below posts to know more on HCM Extracts:
- Basics of HCM Extracts in Fusion HCM
- Resolve A system error occurred during processing in Extracts
- Configure GMFZT logging for an HCM Extract
- New features in HCM Extracts in 19B release
- Importing Changes Only HCM Extract post 20A update
- Scheduling and Cancelling an existing schedule of HCM Extract
- Skip Output file of HCM Extract when no data is fetched
- Basics of Changes Only Functionality in HCM Extract
- Using Table based Valueset when DBI is unavailable
- Most frequently used User Entities in HCM Extracts
- How to view the output of HCM Extract run by other users?
- How to link/connect the User Entities in HCM Extracts
- Fix for assertion failure error in HCM Extracts
- Top 50 Interview questions and answers on HCM Extracts
- HCM Extracts Dynamic Output Filename guide
- SQL Query to find scheduled HCM Extracts
- Achieve complex requirements using Custom Global Reports Data Model
- Payroll Flow and HCM Extracts Mapping for Payroll Module
- Hiding XML nodes in HCM Extract Output
- Running the latest extract version using Payroll Flow
- How to convert HCM Extract Export XML to Readable format?
- Deleted Data Report using Audit Functionality
- Passing Logged in User details to HCM Extract
- How to handle FF not compiled error for HCM Extracts
- How to handle Daylight Savings timings for HCM Extract schedules
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.