EBS HRMS 定制化配置(4)

来源：网络收集时间：2019-03-22 下载这篇文档手机版

说明：文章内容仅供预览，部分内容可能不全，需要完整文档或者需要复制内容，请下载word后使用。下载word有问题请添加微信号:或QQ：处理（尽可能给您提供完整文档），感谢您的支持与谅解。

AS nationality,NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location

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 PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAAF.period_of_service_id = PPS.period_of_service_id(+) AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'

AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND PPU.EFFECTIVE_END_DATE > sysdate

AND(TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)

AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID 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, PAPF.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, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME AS supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, 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, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location

FROM PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_PLACEMENT PPS, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF,

PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, XXTP_HR_IDM_PEOPLE_V XHIPV 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 PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAPF.CURRENT_NPW_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'

AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND PPU.EFFECTIVE_END_DATE > sysdate

AND (TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)

AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID ) resultTable --

) --WHERE Row_Num BETWEEN and

order by person_id

4.2 HRMS_CURRENT_FUTURE_EMPLOYEE_RECON_QUERY

SELECT * FROM ( SELECT resultTable.*, rownum Row_Num FROM (

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, PAPF.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, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME AS supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date ,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, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location

FROM PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_SERVICE PPS, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, XXTP_HR_IDM_PEOPLE_V XHIPV 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 PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+)

AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAAF.period_of_service_id = PPS.period_of_service_id(+)

AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'

AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)

AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID

UNION

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 AS user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date, PAPF.EFFECTIVE_END_DATE AS effective_end_date,PAPF.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, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, PAPF.TOWN_OF_BIRTH AS town_of_birth, PAPF.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, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location

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 PAAF.PERSON_ID = PAPF.PERSON_ID

AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+)

AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y' AND PAAF.period_of_service_id = PPS.period_of_service_id(+) AND PAPF.PERSON_ID = PPS.PERSON_ID AND PPS.DATE_START > SYSDATE

AND PPS.DATE_START BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE

AND SUP.EFFECTIVE_START_DATE(+) <= PAAF.EFFECTIVE_START_DATE AND SUP.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE) AND PAPF.PERSON_ID = XHIPV.PERSON_ID

UNION

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, PAPF.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, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME AS supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date,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, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location

FROM PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_PLACEMENT PPS, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, XXTP_HR_IDM_PEOPLE_V XHIPV 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 PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+)

AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAPF.CURRENT_NPW_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'

AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND (TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)

AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID

UNION

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 AS user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date, PAPF.EFFECTIVE_END_DATE AS effective_end_date,PAPF.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, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, PAPF.TOWN_OF_BIRTH AS town_of_birth, PAPF.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, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location

FROM PER_ALL_PEOPLE_F PAPF, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, PER_PERIODS_OF_PLACEMENT PPS, XXTP_HR_IDM_PEOPLE_V XHIPV WHERE PPT.USER_PERSON_TYPE IN('Contingent Worker','Contingent Employee') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID AND PAAF.PERSON_ID = PAPF.PERSON_ID

AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+)

AND PAPF.CURRENT_NPW_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y' AND PAPF.PERSON_ID = PPS.PERSON_ID AND PPS.DATE_START > SYSDATE

AND PPS.DATE_START BETWEEN

PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE

AND SUP.EFFECTIVE_START_DATE(+) <= PAAF.EFFECTIVE_START_DATE AND SUP.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE) AND PAPF.PERSON_ID = XHIPV.PERSON_ID

) resultTable -- ) WHERE Row_Num BETWEEN and

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库EBS HRMS 定制化配置(4)在线全文阅读。

EBS HRMS 定制化配置(4).doc 将本文的Word文档下载到电脑，方便复制、编辑、收藏和打印下载失败或者文档不完整，请联系客服人员解决！

下载这篇word文档

本文链接：https://www.77cn.com.cn/wenku/zonghe/539618.html（转载请注明文章来源）

上一篇：Moto Droid 烧号、root、1x上网无敌菜鸟教程(已经写到读ESN)
下一篇：基于单片机的锂离子电池电量检测系统设计（本科毕业论文）