A HRMS TargetコネクタのサンプルのSQL問合せ

この付録では、search.propertiesファイルでHRMS Targetコネクタの問合せを更新する際に使用可能なサンプルのSQL問合せを示します。

次のSQL問合せを使用して、ターゲット・リソースのリコンシリエーションで新しい属性Blood Typeを追加した場合にHRMS_CURRENT_EMPLOYEE_RECON_QUERYおよびHRMS_CURRENT_FUTURE_EMPLOYEE_RECON_QUERYの問合せを更新できます。この新しく追加された属性は、第5.3.4項「search.propertiesファイルの更新」で説明されている手順を実行する際に追加されます。

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>