SQL Query to get Supervisor Change HDL for Assignment

5 Shares

In this article we will look into how to update the supervisor for employees on their assignment record using HCM Data Loader.

When there is a requirement to add a new supervisor for a bunch of employees, there are two options:
1) We can add a new row for these employees on “Manage Employment” page with Action as “Manager Change” and update the manager to reflect the new manager and save the transaction. It is time taking process and it is prone to errors as there is manual intervention. Hence this method is not suggested when the impacted employees are more in number.. (>10)..
2) We can prepare an HDL file to load new row on “Manage Employment” page and update the supervisor.. Even in this method, we will need to manually create the HDL file to load the new row and update Supervisor, however it takes lesser time than the first option..

We can clearly see that option 2 wins over option 1 when the impacted employees is huge. So we will try to automate the HDL creation using a SQL Query and have very little manual intervention to update the New Manager Person Number and New Manager Assignment Number.. Remaining everything we will handle in the SQL.. It is very efficient way and is less prone to errors.

Table of Contents

SQL Query to get the HDL file for Supervisor update on the assignment record:

SELECT MAIN
FROM (
	(
		SELECT 'MERGE|WorkTerms|E||Y|1|2019/02/07|MANAGER_CHANGE|' || REPLACE(PAAM.ASSIGNMENT_NUMBER, 'E', 'ET') || '|' || PLE.NAME || '|' || TO_CHAR(PPOS.DATE_START, 'YYYY/MM/DD') || '|' || PAPF.PERSON_NUMBER AS MAIN
			,PAPF.PERSON_NUMBER
			,1 AS SNO
		FROM PER_ALL_ASSIGNMENTS_M PAAM
			,PER_ALL_PEOPLE_F PAPF
			,PER_PERIODS_OF_SERVICE PPOS
			,PER_LEGAL_EMPLOYERS PLE
		WHERE SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE
				AND PAAM.EFFECTIVE_END_DATE
			AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
				AND PAPF.EFFECTIVE_END_DATE
			AND SYSDATE BETWEEN PLE.EFFECTIVE_START_DATE
				AND PLE.EFFECTIVE_END_DATE
			AND PLE.ORGANIZATION_ID = PAAM.LEGAL_ENTITY_ID
			AND PAAM.PERSON_ID = PAPF.PERSON_ID
			AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
			AND PAAM.PRIMARY_FLAG = 'Y'
			AND PAAM.ASSIGNMENT_TYPE = 'E'
			AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
			AND PAAM.PERSON_ID = PPOS.PERSON_ID
		)
	
	UNION
	
	(
		SELECT 'MERGE|Assignment|2019/02/07||1|Y|MANAGER_CHANGE|' || PAAM.ASSIGNMENT_NUMBER || '|' || REPLACE(PAAM.ASSIGNMENT_NUMBER, 'E', 'ET') || '|' || TO_CHAR(PPOS.DATE_START, 'YYYY/MM/DD') || '|' || PAPF.PERSON_NUMBER AS MAIN
			,PAPF.PERSON_NUMBER
			,4 AS SNO
		FROM PER_ALL_ASSIGNMENTS_M PAAM
			,PER_ALL_PEOPLE_F PAPF
			,PER_PERIODS_OF_SERVICE PPOS
		WHERE SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE
				AND PAAM.EFFECTIVE_END_DATE
			AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
				AND PAPF.EFFECTIVE_END_DATE
			AND PAAM.PERSON_ID = PAPF.PERSON_ID
			AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
			AND PAAM.PRIMARY_FLAG = 'Y'
			AND PAAM.ASSIGNMENT_TYPE = 'E'
			AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
			AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
			AND PAAM.PERSON_ID = PPOS.PERSON_ID
		)
	
	UNION
	
	(
		SELECT 'MERGE|AssignmentSupervisor|' || PAAM.ASSIGNMENT_NUMBER || '|2019/02/07|4712/12/31|LINE_MANAGER|' || PAPF_MGR.PERSON_NUMBER || '|Y|' || PAAM_MGR.ASSIGNMENT_NUMBER || '|LINE_MANAGER|<NewManagerPersonNumber>|<NewManagerAssignmentNumber>' AS MAIN
			,PAPF.PERSON_NUMBER
			,7 AS SNO
		FROM PER_ALL_ASSIGNMENTS_M PAAM
			,PER_ALL_PEOPLE_F PAPF
			,PER_PERIODS_OF_SERVICE PPOS
			,per_assignment_supervisors_f pasf
			,PER_ALL_PEOPLE_F PAPF_MGR
			,PER_ALL_ASSIGNMENTS_M PAAM_MGR
		WHERE SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE
				AND PAAM.EFFECTIVE_END_DATE
			AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE
				AND PAPF.EFFECTIVE_END_DATE
			AND PAAM.PERSON_ID = PAPF.PERSON_ID
			AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
			AND PAAM.PRIMARY_FLAG = 'Y'
			AND PAAM.ASSIGNMENT_TYPE = 'E'
			AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
			AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
			AND PAAM.PERSON_ID = PPOS.PERSON_ID
			AND PASF.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID
			AND PASF.PERSON_ID = PAAM.PERSON_ID
			AND PASF.PRIMARY_FLAG = 'Y'
			AND SYSDATE BETWEEN PASF.EFFECTIVE_START_DATE
				AND PASF.EFFECTIVE_END_DATE
			AND pasf.manager_id = PAPF_MGR.person_id
			AND SYSDATE BETWEEN PAPF_MGR.EFFECTIVE_START_DATE
				AND PAPF_MGR.EFFECTIVE_END_DATE
			AND pasf.MANAGER_ASSIGNMENT_ID = paam_mgr.assignment_id
			AND pasf.manager_id = PAAM_MGR.person_id
			AND paam_mgr.ASSIGNMENT_TYPE = 'E'
			AND paam_mgr.EFFECTIVE_LATEST_CHANGE = 'Y'
			AND SYSDATE BETWEEN paam_mgr.EFFECTIVE_START_DATE
				AND paam_mgr.EFFECTIVE_END_DATE
		)
	
	UNION
	
	(
		SELECT ' ' AS MAIN
			,'1' AS PERSON_NUMBER
			,2 AS SNO
		FROM DUAL
		)
	
	UNION
	
	(
		SELECT 'METADATA|WorkTerms|WorkerType|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|ActionCode|AssignmentNumber|LegalEmployerName|DateStart|PersonNumber' AS MAIN
			,'ALL' AS PERSON_NUMBER
			,0 AS SNO
		FROM DUAL
		)
	
	UNION
	
	(
		SELECT 'METADATA|Assignment|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|ActionCode|AssignmentNumber|WorkTermsNumber|DateStart|PersonNumber' AS MAIN
			,'ALL' AS PERSON_NUMBER
			,3 AS SNO
		FROM DUAL
		)
	
	UNION
	
	(
		SELECT ' ' AS MAIN
			,'ALL' AS PERSON_NUMBER
			,5 AS SNO
		FROM DUAL
		)
	
	UNION
	
	(
		SELECT 'METADATA|AssignmentSupervisor|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|ManagerType|ManagerPersonNumber|PrimaryFlag|ManagerAssignmentNumber|NewManagerType|NewManagerPersonNumber|NewManagerAssignmentNumber' AS MAIN
			,'ALL' AS PERSON_NUMBER
			,6 AS SNO
		FROM DUAL
		)
	)
ORDER BY SNO
	,PERSON_NUMBER

After we get the SQL output, copy it to text file, save it as Worker.dat, zip it and invoke the HCM Data Loader.

Sample HDL file created by the SQL Query:

METADATA|WorkTerms|WorkerType|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|ActionCode|AssignmentNumber|LegalEmployerName|DateStart|PersonNumber
MERGE|WorkTerms|E||Y|1|2019/02/07|MANAGER_CHANGE|ET123-3|US Operating Co, LLC|2018/09/12|335312
METADATA|Assignment|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|ActionCode|AssignmentNumber|WorkTermsNumber|DateStart|PersonNumber
MERGE|Assignment|2019/02/07||1|Y|MANAGER_CHANGE|E123-3|ET123-3|2018/09/12|335312
METADATA|AssignmentSupervisor|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|ManagerType|ManagerPersonNumber|PrimaryFlag|ManagerAssignmentNumber|NewManagerType|NewManagerPersonNumber|NewManagerAssignmentNumber
MERGE|AssignmentSupervisor|E123-3|2019/02/07|4712/12/31|LINE_MANAGER|374968|Y|E374968-2|LINE_MANAGER|234|E234-2

HDL process will add a new row for the employee with Action as “Manager Change” and updates the Supervisor.

Note: Do your due diligence and verify the employees in the output created by the SQL Query. Once the Supervisor gets updated, it will be an overhead to revert the changes.

5 Shares

This is what i am looking and Appreciate your help. However, one can build other queries definitely. I think there is no issue even if we pass the WR component as well ?

Of course its not needed though for Manager

Yes Chetan. WR is not required for manager, hence it is not added over here.. We can build queries in the same structure for all business objects.

Thanks you for your tips and tricks they are very helpful.

I keep receiving the error ManagerAssignmentId is required. I am loading managers for users who do not currently have a manager from a single date in time.. But when I only fill NewManager fields I receive this error. Do you have any ideas how to overcome this?

Once again thank you,
M

Thanks for your efforts but now it is easy to use ‘Bulk updates’ instead of HDL for supervisor changes as is it more simple and takes very less time too

Mass updates *

Yes. With every new release there will be host of new features which can be done easily which could have earlier took more time and effort to accomplish it.

Thanks, Sricharan,
I am getting below error message
The corresponding date effective record in work terms and assignments for date 2020-07-05 and action MANAGER_CHANGE is missing for the AssignmentSupervisor.

In visible box by plugintheme

SQL Query to get the HDL file for Supervisor update on the assignment record:

Sample HDL file created by the SQL Query:

You Might Also Like

How to modify the Person Number after hiring the employee?

HCM Data Loader Error Report

Have you used HCM Data Loader Dashboard?

Quick Links