EBS HRMS 定制化配置

EBS HRMS 定制化配置

一、 扩展 Connector Schema

修改Oracle_EBS_HRMS-11.1.1.5.0/ scripts/OIM_EMPLOYEE_WRAPPER.pck文件中的属性定义，标注红色的为修改和添加的内容。

procedure get_schema( schemaout OUT schemalist ) AS

attr attributelist; BEGIN

schemaout := schemalist(); schemaout.extend(1); attr := attributelist(); attr.extend (56);

attr (1) := attributeinfo('HIRE_DATE','date',1,1,0,1); attr (2) := attributeinfo('BUSINESS_GROUP_ID','varchar2',1,1,0,1);

attr (3) := attributeinfo('LAST_NAME','varchar2',1,1,1,1); attr (4) := attributeinfo('FIRST_NAME','varchar2',1,1,1,1); attr (5) := attributeinfo('SEX','varchar2',1,1,0,1);

attr (6) := attributeinfo('PERSON_TYPE_ID','varchar2',1,1,0,1); attr (7) := attributeinfo('EMPLOYEE_NUMBER','varchar2',1,1,0,1);

attr (8) := attributeinfo('PERSON_ID','varchar2',1,1,0,1); attr (9) := attributeinfo('TITLE','varchar2',1,1,0,1);

attr (10) := attributeinfo('EMAIL_ADDRESS','varchar2',1,1,0,1); attr (11) := attributeinfo('MARITAL_STATUS','varchar2',1,1,0,1);

attr (12) := attributeinfo('NATIONALITY','varchar2',1,1,0,1); attr (13) := attributeinfo('NATIONAL_IDENTIFIER','varchar2',1,1,0,1);

attr (14) := attributeinfo('DATE_OF_BIRTH','date',1,1,0,1); attr (15) := attributeinfo('TOWN_OF_BIRTH','varchar2',1,1,0,1); attr (16) := attributeinfo('REGION_OF_BIRTH','varchar2',1,1,0,1); attr (17) := attributeinfo('COUNTRY_OF_BIRTH','varchar2',1,1,0,1); attr (18) := attributeinfo('USER_PERSON_TYPE','varchar2',1,1,0,1); attr (19) :=

attributeinfo('EFFECTIVE_START_DATE','date',1,1,0,1); attr (20) := attributeinfo('ACTUAL_TERMINATION_DATE','date',1,1,0,1);

attr (21) := attributeinfo('SUPERVISOR_ID','varchar2',1,1,0,1); attr (22) := attributeinfo('SUPERVISOR_NAME','varchar2',0,0,0,1);

attr (23) := attributeinfo('JOB','varchar2',1,1,0,1); attr (24) := attributeinfo('GRADE','varchar2',1,1,0,1);

attr (25) := attributeinfo('DEPARTMENT','varchar2',1,1,0,1); attr (26) := attributeinfo('PERSON_UPDATED_DATE','date',1,1,0,1); attr (27) := attributeinfo('ASSIGNMENT_UPDATED_DATE','date',1,1,0,1); attr.extend;

attr (28) := attributeinfo('U_ID','varchar2',1,1,1,1); attr.extend;

attr (29) := attributeinfo('FAMILY_NAME','varchar2',1,1,0,1); attr.extend;

attr (30) := attributeinfo('GIVEN_NAME','varchar2',1,1,0,1); attr.extend;

attr (31) := attributeinfo('IN_DATE','varchar2',1,1,0,1); attr.extend; attr (32) := attributeinfo('EMPLOYEE_STATUS','varchar2',1,1,0,1); attr.extend; attr (33) := attributeinfo('OFFICE_LOCATION','varchar2',1,1,0,1);

schemaout( 1 ) := schema_object('__PERSON__',attr);

修改完成后在数据库中重新编译。 二、

更新操作

2.1新建用户自定义字段

登录http://172.29.41.203:15000/sysadmin管理界面，新建纱箱“userdef”，保存并关闭。

单击“系统实体”下的“用户”项，单击“新建”按钮，在出现以下界面中选择文本。

确定后，在显示标签和名称中添加新字段。

使用同样步骤添加上所有自定义字段。

2.2添加用户资源实体

1. 登录到 Oracle Identity Manager Design Console。 cd /u01/oracle/Middleware/Oracle_IDM1/designconsole ./xlclient.sh

输入 xelsysadm/Welcome1

单击Yes

2. 展开 Resource Management文件夹, 双击Resource Objects并单击查找按键, 在右侧窗体下方新出现 Resource Objects Table页签。

3. 在 Resource Objects Table页签内查找Oracle EBS HRMS Trusted User 项并双击数字栏。

在以下界面中单击Object Reconciliation页签。

4. 在 Object Reconciliation 页内, 单击 Add Field按钮在出现的 Add Reconciliation

Field 对话框中添加属性字段信息，如U_ID。

5.在 Field Name 字段, 输入属性值，如U_ID。

6. Field Type列表，选择数据的类型， 如String。 7.如果属性值为必填值，需要选定Reauired。 8.保存后关闭对话框。 2.3更新用户过程定义

1. 展开Process Management 并双击 Process Definition项

2. 查找并双击Oracle EBS HRMS Trusted User 过程定义。

3. 在 Reconciliation Field Mapping页内单击 Add Field Map按钮。

4.在Field name 列表中选择2.2节用户资源实体中添加的字段，如UID。

5. 在 User Attribute列表中选择在2.1节用户自定义步骤中添加的字段，如usr_u_id。

6. 保存后关闭对话框。

2.4更新数据同步的属性映射

对新添加的自定义属性，需要添加新的属性映射。

1. 展开 Administration文件夹并双击Lookup Definition。

2. 输入Lookup.EBSHRMS.ReconAttrMap.Trusted 值后单击查找按钮。

3. 单击Add按钮来新添加行。

4.在Code Key字段输入2.2节中定义的属性值，如U_ID。 5.在Decode字段输入1节中定义的属性值，如U_ID。 6.保存后关闭对话框。

2.5创建用户资源实例的同步配置

注：创建同步配置把以上的操作更新到MDS中。

1. 在 Resource Management 文件夹内的Resource Objects中搜索 Oracle EBS HRMS Trusted User。

3. 在 Object Reconciliation页内单击 Create Reconciliation Profile项并保存。

三、

更新Connector Bundle

注：必须把先前添加的新属性字段更新到Connector Bundle（org.identityconnectors.ebs-1.0.1115.jar）中。 1. 解

压

Oracle_EBS_HRMS-11.1.1.5.0/bundle/org.identityconnectors.ebs-1.0.11150.jar文件到当前目录。

2.在configuration文件夹内，编辑search.properties文件。 3. 找到HRMS_CURRENT_EMPLOYEE_RECON_QUERY和

HRMS_CURRENT_FUTURE_EMPLOYEE_RECON_QUERY对应的SQL，并添加与第2节中定义相同的属性值。 4. 保存修改结果到文件。

5. 确认新修改的SQL能够正常执行并包括新添加的字段。

6. 更新connector bundle (org.identityconnectors.ebs-1.0.11150.jar) 包，执行以下命令。

jar -cvfm META-INF/MANIFEST.MF * org.identityconnectors.ebs-1.0.11150.jar 7. 运行更新 JAR脚本来更新数据库中的connector bundle。

[oracle@idmdev bin]$ cd $OAM_ORACLE_HOME/server/bin [oracle@idmdev bin]$ ./UpdateJars.sh For running the Utilities the following environment variables need to be set APP_SERVER is weblogic OIM_ORACLE_HOME is /u01/oracle/Middleware/Oracle_IDM1 JAVA_HOME is /u01/oracle/Middleware/jdk1.7 MW_HOME is /u01/oracle/Middleware WL_HOME is /u01/oracle/Middleware/wlserver_10.3 DOMAIN_HOME is /u01/oracle/Middleware/user_projects/domains/oim_domain Executing oracle.iam.platformservice.utils.JarUpdateUtility in IPv4 mode [Enter Xellerate admin username :]xelsysadm [Enter the admin password :] Welcome1 [[Enter serverURL (Ex. t3://oimhostname:oimportno for weblogic or corbaloc:iiop:localhost:2801 for websphere)]:]t3://idmdev.tp-link.net:15000 [[Enter context (i.e.: weblogic.jndi.WLInitialContextFactory for weblogic or com.ibm.websphere.naming.WsnInitialContextFactory for websphere)]:]weblogic.jndi.WLInitialContextFactory Logging configuration class \java.lang.ClassNotFoundException: oracle.core.ojdl.logging.LoggingConfiguration log4j:WARN No appenders could be found for logger (org.springframework.jndi.JndiTemplate). log4j:WARN Please initialize the log4j system properly. Enter the jar type 1.JavaTasks 2.ScheduleTask 3.ThirdParty 4.ICFBundle 4 Enter the path/location of jar file : /home/software/connector/Oracle_EBS_HRMS-11.1.1.5.0/bundle/org.identityconnectors.ebs-1.0.11150.jar Do u want to update more jars [y/n] :n

8. 更新oim_hrms_connector用户访问权限

create or replace synonym oim_hrms_connector.XXTP_HR_IDM_PEOPLE_V for APPS.XXTP_HR_IDM_PEOPLE_V;

grant select on APPS.XXTP_HR_IDM_PEOPLE_V to oim_hrms_connector; 9. 更新成功后，重启OIM所有服务。 四、

SQL样例

4.1 HRMS_CURRENT_EMPLOYEE_RECON_QUERY

SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY person_id) Row_Num,resultTable.* 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.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_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 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

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_SERVICE PPS, 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 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

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

本文来源：https://www.bwwdw.com/article/wiv3.html