 Home |  Book List |  Contents |  Contact Us |
Go to main content
|
|
This appendix lists a sample SQL query that can be used to update queries in the search.properties file for the EBS HRMS connector (Target application).
You can use the following SQL query to update the HRMS_CURRENT_EMPLOYEE_RECON_QUERY and HRMS_CURRENT_FUTURE_EMPLOYEE_RECON_QUERY queries if you have added a new attribute, Blood Type, to your Target application (for target resource reconciliation). This newly added attribute is added as part of performing the procedure described in Updating the search.properties File.
TARGET_HRMS_CURRENT_EMPLOYEE_RECON_QUERY= with PERSON_RECORD as ( \
SELECT PAPF.PERSON_ID AS person_id,PAPF.FIRST_NAME AS first_name,PAPF.LAST_NAME AS last_name,PAPF.EMAIL_ADDRESS AS email_address,PPT.USER_PERSON_TYPE user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date,PAPF.EFFECTIVE_END_DATE AS effective_end_date,EMPLOYEE_NUMBER AS employee_number,PPS.ACTUAL_TERMINATION_DATE AS actual_termination_date,sysdate as SYSTEM_DATE,PAPF.BUSINESS_GROUP_ID AS business_group_id,PAPF.TOWN_OF_BIRTH AS town_of_birth,PPU.PERSON_TYPE_ID AS person_type_id,PAPF.REGION_OF_BIRTH AS region_of_birth,PAPF.COUNTRY_OF_BIRTH AS country_of_birth,PAPF.NATIONAL_IDENTIFIER AS national_identifier,\
PAPF.TITLE AS title,PAPF.MARITAL_STATUS AS marital_status,PAPF.SEX AS sex,PAPF.DATE_OF_BIRTH AS date_of_birth,PAPF.NATIONALITY AS nationality,NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date,PAPF.LAST_UPDATE_DATE AS person_updated_date,null AS assignment_id,null AS asg_effective_start_date,null AS change_reason,null AS organization_id,null AS job_id,null AS grade_id,null as supervisor_id,null AS address_id,null AS add_effective_start_date,null AS style,null AS address_line1,null AS address_line2,null AS address_line3,null AS country,null AS date_from,null AS date_to,null AS postal_code,null AS region_1,null AS region_2,null AS region_3,null AS town_or_city,null AS primary_flag,null as address_type,PAPF.BLOOD_TYPE AS BLOOD_TYPE \
FROM PER_ALL_PEOPLE_F PAPF,PER_PERIODS_OF_SERVICE PPS,PER_PERSON_TYPES PPT ,PER_PERSON_TYPE_USAGES_F PPU WHERE PPT.USER_PERSON_TYPE IN('Employee','Contractor') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y' AND ( PPU.EFFECTIVE_START_DATE > TRUNC(SYSDATE) OR (TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)) AND (PPU.EFFECTIVE_START_DATE > TRUNC(SYSDATE) OR (TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE)) \
union all \
SELECT PAPF.PERSON_ID AS person_id,PAPF.FIRST_NAME AS first_name,PAPF.LAST_NAME AS last_name,PAPF.EMAIL_ADDRESS AS email_address,PPT.USER_PERSON_TYPE user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date,PAPF.EFFECTIVE_END_DATE AS effective_end_date,NPW_NUMBER AS employee_number,PPS.ACTUAL_TERMINATION_DATE AS actual_termination_date,sysdate as SYSTEM_DATE,PAPF.BUSINESS_GROUP_ID AS business_group_id,PAPF.TOWN_OF_BIRTH AS town_of_birth,PPU.PERSON_TYPE_ID AS person_type_id,PAPF.REGION_OF_BIRTH AS region_of_birth,PAPF.COUNTRY_OF_BIRTH AS country_of_birth,PAPF.NATIONAL_IDENTIFIER AS national_identifier,\
PAPF.TITLE AS title,PAPF.MARITAL_STATUS AS marital_status,PAPF.SEX AS sex,PAPF.DATE_OF_BIRTH AS date_of_birth,PAPF.NATIONALITY AS nationality,NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date,PAPF.LAST_UPDATE_DATE AS person_updated_date,null AS assignment_id,null AS asg_effective_start_date,null AS change_reason,null AS organization_id,null AS job_id,null AS grade_id,null as supervisor_id,null AS address_id,null AS add_effective_start_date,null AS style,null AS address_line1,null AS address_line2,null AS address_line3,null AS country,null AS date_from,null AS date_to,null AS postal_code,null AS region_1,null AS region_2,null AS region_3,null AS town_or_city,null AS primary_flag,null as address_type,PAPF.BLOOD_TYPE AS BLOOD_TYPE \
FROM PER_ALL_PEOPLE_F PAPF,PER_PERIODS_OF_PLACEMENT PPS,PER_PERSON_TYPES PPT ,PER_PERSON_TYPE_USAGES_F PPU WHERE PPT.USER_PERSON_TYPE IN('Contingent Employee','Contingent Worker') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAPF.CURRENT_NPW_FLAG = 'Y' AND PPU.EFFECTIVE_END_DATE > sysdate AND ( PPU.EFFECTIVE_START_DATE > TRUNC(SYSDATE) OR (TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)) AND (PPU.EFFECTIVE_START_DATE > TRUNC(SYSDATE) OR (TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE)) \
) select * from ( \
select RESULTTABLE.*,ROW_NUMBER() OVER (ORDER BY person_id) AS Row_Num from ( \
select * from PERSON_RECORD \
union all \
select person.PERSON_ID,person.FIRST_NAME,person.LAST_NAME,person.EMAIL_ADDRESS,person.USER_PERSON_TYPE,person.EFFECTIVE_START_DATE,person.EFFECTIVE_END_DATE ,person.EMPLOYEE_NUMBER,person.ACTUAL_TERMINATION_DATE, \
person.SYSTEM_DATE,person.BUSINESS_GROUP_ID,person.TOWN_OF_BIRTH,person.PERSON_TYPE_ID,person.REGION_OF_BIRTH,person.COUNTRY_OF_BIRTH,person.NATIONAL_IDENTIFIER,person.TITLE,person.MARITAL_STATUS,person.SEX,person.DATE_OF_BIRTH,person.NATIONALITY,person.hire_date,person.person_updated_date, \
PAAF.ASSIGNMENT_ID as assignment_id,PAAF.EFFECTIVE_START_DATE as ASG_EFFECTIVE_START_DATE,PAAF.CHANGE_REASON,PAAF.ORGANIZATION_ID as organization_id,PAAF.JOB_ID,PAAF.GRADE_ID as grade_id,PAAF.SUPERVISOR_ID as supervisor_id,null AS address_id,null AS add_effective_start_date, \
null AS style,null AS address_line1,null AS address_line2,null AS address_line3,null AS country,null AS date_from,null AS date_to,null AS postal_code,null AS region_1,null AS region_2,null AS region_3, \
null AS town_or_city,null AS primary_flag,null as address_type, person.BLOOD_TYPE AS BLOOD_TYPE from PERSON_RECORD person,PER_ALL_ASSIGNMENTS_F PAAF where person.person_id=PAAF.person_id AND PAAF.job_id is not null AND ((PAAF.EFFECTIVE_START_DATE >= sysdate) OR (TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE) ) \
union all \
select person.PERSON_ID,person.FIRST_NAME,person.LAST_NAME,person.EMAIL_ADDRESS,person.USER_PERSON_TYPE,person.EFFECTIVE_START_DATE,person.EFFECTIVE_END_DATE ,person.EMPLOYEE_NUMBER,person.ACTUAL_TERMINATION_DATE, \
person.SYSTEM_DATE,person.BUSINESS_GROUP_ID,person.TOWN_OF_BIRTH,person.PERSON_TYPE_ID,person.REGION_OF_BIRTH,person.COUNTRY_OF_BIRTH,person.NATIONAL_IDENTIFIER,person.TITLE,person.MARITAL_STATUS,person.SEX,person.DATE_OF_BIRTH,person.NATIONALITY,person.hire_date,person.person_updated_date,\
null as assignment_id,null as ASG_EFFECTIVE_START_DATE,null as CHANGE_REASON,null as organization_id,null as JOB_ID,null as grade_id,null as supervisor_id, \
PA.ADDRESS_ID AS address_id,PA.DATE_FROM AS add_effective_start_date,PA.STYLE AS style, PA.ADDRESS_LINE1 AS address_line1, PA.ADDRESS_LINE2 AS address_line2, PA.ADDRESS_LINE3 AS address_line3,PA.COUNTRY AS country,PA.DATE_FROM AS date_from,PA.DATE_TO AS date_to, PA.POSTAL_CODE AS postal_code, PA.REGION_1 AS region_1,PA.REGION_2 AS region_2, PA.REGION_3 AS region_3,PA.TOWN_OR_CITY AS town_or_city,PA.PRIMARY_FLAG AS primary_flag,PA.ADDRESS_TYPE as address_type,person.BLOOD_TYPE AS BLOOD_TYPE from PERSON_RECORD person,PER_ADDRESSES PA where person.person_id= PA.person_id and ((PA.DATE_FROM > sysdate) OR SYSDATE between PA.DATE_FROM and nvl(PA.DATE_TO, TO_DATE('31-DEC-4712','dd-mon-yyyy'))) \
) RESULTTABLE \
--<FILTER> \
) WHERE Row_Num BETWEEN <START_ROW_NUMBER> and <END_ROW_NUMBER>