Here is an example has been provided. Please modify accordingly as per Business requirement.
Assumption : Viewer is aware of how to create an HCM Extract in Oracle HCM Cloud environment using BI Report.
Using Inbound Interface process, we can Extract data from Oracle HCM Cloud environment and load the data back accordingly in Oracle HCM Cloud environment, using HDL.
Here is a scenario how to achieve it.
Suppose, in the Person External Identifier section, the Username details for each Person to login in the Oracle HCM Cloud environment is also present.We will be checking if there are any changes between Person External Identifier Username details and User Profile Username details.
If yes, we will correct the User Profile Username details with Person External Identifier Username details.
Our main data source will be BI Report and we will be creating pipe delimited ".dat" file from there.
So, that is why we are creating this dummy HCM Extract.
Why we need HCM Extract is because, only HCM Extract has this functionality to call HDL using Inbound Interface option, in the "Extract Delivery Options" section within HCM Extract from the "Delivery Type".
Using the below SQL, a Data model needs to be created first.
Then an eText template needs to be created.
Using this Data Model and etext template, the required Report needs to be created to create the User.dat file. Pass this Report path in the dummy HCM Extract's Delivery Option.
SQL to get the details for the User.dat file (Modify as required):
----------------------------------------------------
SELECT 'LINK' AS LINK --(This Link attribute will be used to link with the delivered Global Data Model Report)
,'N' AS SUSPENDED
,A.PERSON_NUMBER
,A.EXT_IDENTIFIER_NUMBER
,B.USERNAME
FROM
(SELECT DISTINCT PAPF.PERSON_NUMBER
,PEAI.EXT_IDENTIFIER_NUMBER
FROM PER_ALL_PEOPLE_F PAPF,PER_EXT_APP_IDENTIFIERS PEAI
WHERE PAPF.PERSON_ID = PEAI.PERSON_ID
AND PEAI.EXT_IDENTIFIER_TYPE = <pass the External Identifier Type code here>
AND TO_CHAR(SYSDATE, 'YYYY/MM/DD') BETWEEN TO_CHAR(PEAI.DATE_FROM,'YYYY/MM/DD') AND NVL(TO_CHAR(PEAI.DATE_TO,'YYYY/MM/DD'),'4712/12/31')
ORDER BY PAPF.PERSON_NUMBER
) A,
(SELECT DISTINCT P.PERSON_NUMBER
,U.USERNAME
FROM PER_USERS U, PER_ALL_PEOPLE_F P
WHERE U.PERSON_ID = P.PERSON_ID
ORDER BY P.PERSON_NUMBER
) B
WHERE A.PERSON_NUMBER = B.PERSON_NUMBER
AND A.EXT_IDENTIFIER_NUMBER <> B.USERNAME
AND EXISTS
(SELECT 'X'
FROM PER_ALL_PEOPLE_F PAPF, PER_EXT_APP_IDENTIFIERS PEAI
WHERE PAPF.PERSON_ID = PEAI.PERSON_ID
AND PEAI.EXT_IDENTIFIER_TYPE = <pass the External Identifier Type code here>
AND TO_CHAR(SYSDATE,'YYYY/MM/DD') BETWEEN TO_CHAR(PEAI.DATE_FROM,'YYYY/MM/DD') AND NVL(TO_CHAR(PEAI.DATE_TO,'YYYY/MM/DD'),''4712/12/31')
)
Sample User.dat file details below :
------------------------------------------------
METADATA|User|Suspended|PersonNumber|Username
MERGE|User|N|<SQL will pass the Person Number here through eText template>|<SQL will pass the Username here through eText template>
Now follow the steps in the HCm Extract :
------------------------------------------------
Step 1 : Create a dummy HCM Extract.
In the Parameters section add a parameter called Auto Load (Tag Name = Auto_Load)
with Data Type as "Text" with Default Value as "Y".
For the Data Group I have taken "PER_EXT_ASG_STATUS_DETAILS_UE" as
User Entity as it returns very less row count.
Step 2 : In Data group Filter criteria I have put condition as "1=2" so that it doesn't return
any data set.
Step 3 : In the Data Group a Data Record has been created and a Dummy Attribute has been
added called "Dummy Field".
It's Data Type is "Text", Type is "String" and string value has been passed as "Demo"
Step 4 : Now for the Extract Delivery Option, you can pass the details as below :
Start Date : 01/01/1901
End Date : 12/31/4712
Delivery Option Name : HDLOutput
Output Type : Text
Report : <pass the BI Report path here ;
example : /Custom/..../SM_USERNAME_UPDATE_RPT.xdo>
Template Name : <pass the eText template name here;
example : SM_USERNAME_UPDATE_TMPLT>
Output Name : User
Delivery Type : Inbound Type
Required Bursting Node : ticked
In the "Additional Details" section please pass the details as below :
Encryption Mode : None
Override File : .dat
Run Time File Name : blank
Integration Name : UserNameUpdate
Integration Type : Data Loader
Integration Parameters : blank
Key : blank
Locale : blank
Time Zone : blank
Compress : blank
Compressed Delivery Group : User.zip
Step 5 : Navigate to Home --> My Clients Groups --> Data Exchange --> Refine Extracts
(Under HCM Extracts)
Search for the dummy HCM Extract which has been created. Select the HCM Extract
and click on the "Edit" button.
Step 6 : Once the HCM Extract is opened, in the "Tasks" tab, under Flow Task the
HCM Extract name will be present.
Now click on the "Actions" button and select the option "Select and Add".
A search popup will appear and search the task, named
"Initiate HCM Data Loader" (Generate HCM Data Loader file and optionally
perform a data load).Now select the task and click on the "Done" button.
Step 7 : Now select the task "Initiate HCM Data Loader" click on the "Go to Task" button.
2 options will be there :
a> Data Loader Archive Action
b> Data Loader Configuration
Step 8 : Now select the 1st option "Data Loader Archive Action".Click on the "Edit" button.
In the "Parameter Basis" dropdown, select the option "Bind to Flow Task".
In the "Basis Value" dropdown, select the option "Extract Name , Submit , Payroll Process"
Step 9 : Now select the 2nd option "Data Loader Configuration". Click on the "Edit" button.
In the "Parameter Basis" dropdown, select the option "Constant Bind".
In the "Basis Value" multiline text box, please provide the below details :
ImportMaximumErrors=100,LoadMaximumErrors=100,LoadConcurrentThreads=8
,LoadGroupSize=100
Step 10 : Now click on the "Next" button and then click on the "Submit" button.
Now submit the HCM Extract and check the Inbound Interface process.
