In this article we will look into how to pass the logged in user details like User Person Number, User Legal Entity, User Business Unit, User Email Address etc.
Table of Contents
Use cases:
- Sending the output of extract dynamically to the email address of the user who runs the extract
- Getting the Output for the specific legal employer which the employee is in which is like adding security to that specific legal employer dynamically.
How Extract Works:
The HCM Extract will be run with the user “FUSION_APPS_HCM_ESS_APPID” (Enterprise Scheduler Job Application Identify for HCM) irrespective of which user runs it. So if we add the logic to get the user details using FND_GLOBAL.USERNAME or fnd_global.get_session_attribute(‘HCM_USER_PERSONID’) in the advanced filter then it doesn’t work as it tries to get the details for FUSION_APPS_HCM_ESS_APPID user which doesn’t exist and hence the extract would not pick any data.
So the only option is to create an additional input parameter for the Payroll Flow Pattern (Which would be the HCM extract name). Add Post-SQL to that input parameter to pass the input user details and hire the input parameter. The flow parameters will be set with the logged user details, so the parameter would be passed to extract and this input parameter can be used in the advanced filter to achieve the requirement.
Post-SQL for Logged In User Email Address:
SELECT e.email_address
FROM per_email_addresses e,
per_all_people_f p,
per_users u
WHERE e.email_address_id = p.primary_email_id
AND p.person_id = u.person_id
AND u.username = FND_GLOBAL.USER_NAME
AND sysdate BETWEEN p.effective_start_date AND p.effective_end_datePost-SQL for Logged In User Legal Entity based on AOR:
SELECT V2.organization_id
FROM PER_ASG_RESPONSIBILITIES v1,
hr_legal_entities V2
WHERE person_id = fnd_global.get_session_attribute('HCM_USER_PERSONID')
AND v1.legal_entity_id = v2.organization_id
AND v2.classification_code = 'HCM_LEMP'Post-SQL for Logged In User Person Number:
SELECT p.person_number
FROM per_all_people_f p,
per_users u
WHERE p.person_id = u.person_id
AND u.username = FND_GLOBAL.USER_NAME
AND sysdate BETWEEN p.effective_start_date AND p.effective_end_dateAfter these Post-SQLs are assigned to the Input parameters on the Payroll Flow, these input parameters can be used in the advanced filter condition on the Root Data Group to get this criteria applied to the extract.
Ex: Input Parameter is LEGAL_ENTITY:
Here is how to define the input parameter and the post-sql for the payroll flow.
Once this is done, then the next part would be to write the advanced filter using this input parameter. We will have to look at the ROOT Data GROUP UE SQL to find out the alias name that we need to use in the advanced filter.
Advanced Filter for Logged In Legal Entity:
paam.legal_entity_id = pay_report_utils.get_parameter_value('LEGAL_ENTITY')Advanced Filter for Logged In Person Number:
papf.person_number = pay_report_utils.get_parameter_value(‘PERSON_NUMBER’)
Once these two setups are done, then you are ready to run the extract. The Input Parameter will pick the value from Post-SQL and then pass it to the extract.. Advanced Filter will add criteria to get the logged in user info for the Root Data Group and then you would get the output appropriately.
Tip: To send extract output to logged in email address, just add input parameter and then use that input parameter as To attribute in the email delivery option. And the extract will send the output to logged in email dynamically.
This was a real-time use case for one of our Telegram Group members who had to achieve this requirement within a day due to the integration being tested in UAT phase. So the people on the group came up with these innovative approaches to achieve the requirement. And finally the original poster of the message achieved his requirement by following this approach. Happy Ending..
Do you want to be part of the group? Join by clicking the below link
