SQL Query to get Supervisor Change HDL for Assignment
SQL Query to get Supervisor Change HDL for Assignment

 1,187 total views

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

Want to learn Fusion Technical tools? Checkout this article

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.

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.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 8 Comments

  1. Unknown

    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 ?

  2. Unknown

    Of course its not needed though for Manager

  3. 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.

  4. Anonymous

    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

  5. Anonymous

    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

  6. Anonymous

    Mass updates *

  7. Sricharan

    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.

  8. Gaurav

    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.

Leave a Reply