Create an Inbound Interface process using HCM Extract - Automated process to upload an HDL file (Part-1)

There are requirements where you need to rectify an existing record in fusion. Today's topic is to create an user friendly automated process to extract the existing record, transform it and load the same using HDL on a single click.

So to achieve this we would be creating an inbound interface process using HCM extract and BI report.

I am sharing step by step process with a simple example here which I had created with the help of my good friend Pavan.

Let's start, we would update the User Category of a person record. 

1> Create an BI report to extract and transform the existing data in an HDL format

     > Prepare an extract query

SELECT '1' KEY

               ,'MERGE|User|'||papf.person_number||'|EMPLOYEE_USER' mdata

   FROM  fusion.per_users            pu,

                per_all_people_f            papf

WHERE pu.person_id IS NOT NULL

      AND pu.active_flag = 'Y'

      AND papf.person_id = pu.person_id

      AND ( (trunc(sysdate) BETWEEN papf.effective_start_date

               AND papf.effective_end_date)

                  OR papf.effective_start_date >= trunc(sysdate)) 

      AND  person_number is not null

      AND papf.person_number = '169980'

> Now create a Data Model

             >  Tools > Reports and Analytics > Browse Catalog > New > Data Model

                  Data > View > Export > It will create an XML output

   > Based on the above XML data now create an RTF template 

 > Save the DM under Custom folder and create a report using this RTF template, make sure your repot is referring to the correct DM

                Please note down the 

                Template Name: HDLCallDemo and the  

                Report Path:/Custom/HDL Call Demo/UserCatHDLReport/HDLCallDemoRpt.xdo 

           > Now create bursting with the report template name

           > And add an additional permission  

               Search for 'Enterprise Scheduler Job Application Identity for HCM' under All and add

          > Now check if report is returning the data

2> Lets move on to creating an Extract (Part-2)