Monday 8 July 2013

Fusion Apps - File Based Loader Part - III

In the first two blog posts (PART I & PART II) I have explained how to create HCM Business Objects Mapping file and use this file to create data file for loading into Fusion. I also explained how to create the zip file and register that using composite. Now as I mentioned in the last post that when you call and submit the composite to register the file, the composite actually moves the file to a path and then call hr2hrSqlldr.py  python script to load the data into staging tables.

Let's see what are the series of actions it does. If you open the python script you will find that it first checks what are all the input details provided by the user when he/she called the composite from a table named HRC_XFER_JOB_PARAMS. From this table it gets the list of LBOs that were loaded in the zip file. Now user may load all or few LBOs which are supported by FBL. In order to maintain the data integrity it has to load the LBOs in a a pre-defined sequence. To do this it gets the processing sequence from a table HRC_LOADER_METADATA_REGISTRY. The next screen shows the loading sequence from this table.
LBOs are processed starting with the one having the largest processing_order value. Oracle generates separate JOB IDs for each of the top level LBOs which were specified in the composite parameters. Then it creates SQL*Loader Control file for loading data into HRC_LOADER_BATCH_HEADER and HRC_LOADER_JOB tables and sets the LINE_STATUS = 'Ready To Process'. Once this is done it gets all the data files for each of the LBOs and then creates SQL*Loader Control file dynamically for them to insert into HRC_LOADER_BATCH_LINES_SRC table. Now once all data files have been imported into the table, it then calls a PL/SQL procedure HRC_SQLLOADER.PROCESS_BATCH_TRANSFORM to do the data transformation for each of the top level LBOs.

Now for some reasons it does not insert data into those tables in my instance. Don't know why. May be I might be doing something worng. So I logged SR with Oracle and initial response was that our version(11.1.4.0.0) is not the latest one where FBL works as expected.  Let's do this by running Load HCM Data
 Click on Load HCM Data under Coexistence.























Click On Schedule and select the file you loaded. I have already a loaded file Location28.zip. So let me show you the steps with this file.






Select the file, LBO and run mode. Now here I want to do - Import into staging tables and then load them to base tables in a single run. So I selected Import and Load Batch Data. You can do Import first and then load them through Load Batch Data process. Click on submit.


 It submits an ESS job which them submits child processes.









Click Refresh to see whether the process is completed.











 Click Processes tab to see whether it has any error.











 Click on the Batch Name Link as shown.











 As can be seen that it has one record and it failed. Click on the 1 under Failed Objects.






 
 Once you do that it opens a screen which looks like this. Expand Location LBO and select the data to see the error.








Here the error is 'Location code already exists'. I intentionally took this address to show the various features. Basically I created a Location from Fusion Application Navigator-->Workforce Structures->Manage Location



 This picture shows the error details.











I used that location code to create data file. Wanted to explore whether it is possible to update the data which are not created by FBL. So you can see with this process you can not update data which are not created by FBL. Recently in release 5 onwards Oracle has introduced Business Object Key Map HCM Extract to generate the GUIDs for business objects created in application manually. Will explain the process of doing that in a separate post after completing this series.

Many of ou may be wondering why did it try to insert in stead of update. 
Oracle Fusion HCM maintains a Key Mapping table HRC_LOADER_BATCH_KEY_MAP that records, for each business object, its type, source GUID, and Oracle Fusion ID (the ID by which it is identified in Oracle Fusion). For example:
OBJECT TYPE  SOURCE GUID ORACLE FUSION ID

---------------  ---------------  ----------------------
Person              PERS123          006854
Person              PERS456          059832

When a record is imported to the Load Batch Data stage tables, the import process compares the record’s source GUID and object-type values with values in the Key Mapping table:

  • If the values exist in the Key Mapping table, then the process replaces the source GUID in the stage tables with the Oracle Fusion ID.
  • If the values do not exist in the Key Mapping table, then an Oracle Fusion ID is generated for the record, recorded in the Key Mapping table, and used to replace the source GUID in the stage tables.
So for my case I did not know the GUID created by application and also there was not entry in the key mapping table. So it generated a new fusion id and marked the record as Create not Update.

Let's see this process. I am going to update the Location code from the application.
  












 The record details.












 MEHABUB_LOC is the location which is there in the system. Let's change this to MEHABUB_LOC1









 Then set the Batch status to 'Ready to Process'











 Rerun the Load Batch Data Process.











 Click on Run.












 Click on Submit.












 Note down the Process ID. This is nothing but ESS job.











 Wait till the process is completed.











Now you can see that it has created MEHABUB_LOC1 location in fusion.










 This picture shows that the location is now available in application. I am going to use FBL to update the address details of this location.
 I just changed the address details keeping location code and GUID same and created a new data file Location29.zip and called the composite


 This picture shows the composite call parameter details.










 Composite Flow.












 Submit Load HCM Data with Import and Load Batch Data for the file Mehabub29.zip.










 ESS Job id.












 Wait for the process to complete.











 All process completed successfully.











This shows that one object has been changed.
Let's check from the application.









The marked address is what I changed in my data file.










So this completes the series of FBL data loading as a conversion tool.

In summary from this post you know how
  1. FBL python script creates Control files
  2. Oracle derives the processing order of the LBOs
  3. Data transformation happens inside Fusion 
  4. To Load data into staging tables
  5. To get the errors and fix them from Application
  6. To create data file records created with FBL to update
In the next post will discuss how to use FBL as ongoing sync tool using Business Object Key Map Extract . Till then keep trying FBL for different business objects. Please go through the MOS documents mentioned in PART I and PART II.

Cheers........................
 

 

16 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi Mehabub,

    I am using same process as above but I am getting error like "oracle.jbo.DMLException: JBO-26041: Failed to post data to database during "Insert": SQL Statement "INSERT INTO HR_ALL_ORGANIZATION_UNITS_F(ORGANIZATION_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,BUSINESS_GROUP_ID,INTERNAL_EXTERNAL_FLAG,TYPE,ACTION_OCCURRENCE_ID,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGANIZATION_CODE) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14)" can you please tell me the reason for this error please provide your emailid so that I can send the log file.

    Regards,
    Santhosh Kiran

    ReplyDelete
  3. Hi Santhosh,

    Which process failed? Where are you checking the errors? Do you have access to HCM Domain EM ? IF so then check the log files for the specific ESS job which failed.

    Also send me the error details at mehabub_rahaman@yahoo.com

    ReplyDelete
  4. Hi Mehabub,

    I have finished loading the data related to work structures now I am trying to load the data related to salary basis but here batch is not getting created for salary basis but the load hcm data process is going to success did you face any issue like this ?and please let me know is it application issue or data issue?


    Regards,
    Santhosh kiran.

    ReplyDelete
  5. Hi Santosh,

    I faced the same issue while loading locations. There it was problem with the SET ID. I re-exported the cross reference files and then it loaded successfully. I have nor tried loading salary basis.Please check the The GUID reference to the Element Type extracted from Fusion and The GUID reference to the Element Type's Input Value extracted from Fusion.
    This should resolve the issue.

    Thanks,
    Mehabub

    ReplyDelete
  6. Hi Mahesh,
    Good Article. I like your post with details of technical table and overall flow.
    I am trying to load Persons so prepared V_PERSON_VO.DAT file which has Business_Group_ID which is 32 character SETID I am not sure what value I should provide against this. I tried null or Common SetIT but it is erroring out saying Data Error.Can you please some guidance in this regard..? Thanks

    ReplyDelete
  7. Hi,

    Thanks for the feedback. By the way I am Mehabub not Mahesh :-). It is the enterprise id. If you leave it blank, it would be linked to the user's enterprise who is running the FBL. You will get the value from XR_ENTERPRISE.dat file which is there under cross reference file generated from fusion.

    Thanks,
    Mehabub

    ReplyDelete
  8. Hi Mehabub,

    I have started working on Oracle Fusion HCM this week. I found your blog and its really helpful for beginners like me and wanted to thank you and appreciate your efforts.

    Hope to see more articles like this and your blog will be opened every day for my self learning.

    Thanks & Regards,
    Sivakumar P

    ReplyDelete
  9. Hi Sivakumar,

    Thanks for your feedback. When I started I could not find anyone to help me. I thought to post whatever I learned so that it can help at least people like me who started to upgrade their skill. Yes, I am going to post some more articles which are going to be useful(as I think) for others. I am planning to post article on
    How to see log files?
    How to create extract?
    How to create custom Data Source for BIP Reports?
    How to load person records, assignment records, job records etc.

    I think these will be there for almost every HCM project whether it is based on cloud or on premise.

    Keep checking..

    Thanks,
    Mehabub

    ReplyDelete
  10. Hi Mehabub,

    I have installed 11.1.5, trying to load Department using FBL

    I am getting below error while sqlldr trying to load data into fusion :
    ERROR at line 1:
    ORA-20001: HRC_BLOAD_HEADER_NOT_EXISTS
    ORA-06512: at "FUSION.HRC_SQLLOADER", line 7734
    ORA-06512: at line 1

    FUSION.HRC_SQLLOADER object exist in fusion database with valid status.
    Didn't get any info on HRC_BLOAD_HEADER_NOT_EXISTS in database.


    Would like share a X_300000005076917 folder, How I can attach the same to this blog.

    Lesson Learned so far :

    Will have to comment out variable EnterpriseId = $1 XferJobId = $2 ZipFilePath = $3 from hr2hrSqlldr.sh other wise hr2hrSqlldr.py failed while doing ESS.
    It was commented in our instance.

    Regards,
    Nilesh Shilvant
    email : nilesh.shilvant@gmail.com

    ReplyDelete
  11. Hi Nilesh,

    Thank you for your information. We recently upgraded from 11.1.4 to 11.1.5. And after this we started to get this error message. As a result it is showing batch count 0 for all our run. We are working with Oracle to get this fixed. I believe it is problem with the python script which has come as part of release 5.

    Thanks,
    Mehabub

    ReplyDelete
    Replies
    1. Hi Mehabub,

      We too relying on Oracle to get rid off from FBL errors.
      Raised SR with Oracle, waiting for updates.

      Regards,
      Nilesh

      Delete
    2. Hi Mehabub,

      After application of below patch & HCM patch bundle 8 our FBL issue resolved,

      We could able to load Department.zip


      https://support.oracle.com/epmos/faces/PatchSearchResults?searchdata=%3Ccontext+type%3D%22BASIC%22+search%3D%22%26lt%3BSearch%26gt%3B%0A%26lt%3BFilter+name%3D%26quot%3Bpatch_number%26quot%3B+op%3D%26quot%3Bis%26quot%3B+value%3D%26quot%3B16780385%26quot%3B%2F%26gt%3B%0A%26lt%3BFilter+name%3D%26quot%3Bexclude_superseded%26quot%3B+op%3D%26quot%3Bis%26quot%3B+value%3D%26quot%3Bfalse%26quot%3B%2F%26gt%3B%0A%26lt%3B%2FSearch%26gt%3B%22%2F%3E

      Regards,
      Nilesh

      Delete
  12. Hi Nilesh,

    After applying patch bundle 9 we were facing the issue. Oracle informed us that the correct hr2hrSqlldr.py and hr2hrSqlldr.sh scripts were not there in the system. They should have been there with patch bundle 9. Oracle gave us the latest files and after that FBL started working as expected.

    Thanks,
    Mehabub

    ReplyDelete
  13. Hi Mehabub,

    I am new to HCM. I have a requirement where I need to upload file into HCM application. I have followed the steps mentioned by you. I am getting an error while executing the SOA Composite(HcmCommonBatchLoaderCoreInboundLoaderComposite). I am getting the following error: "Invalid authentication status from SFTP subsystem.
    The underlying SFTP session is not authenticated. This could be because of incorrect authentication parameters".
    Could you please help me in resolving this issue.

    I have a couple of questions.

    From which location would the HCM application pick the zip file for pushing the data into the staging tables.
    What is the job of SOA composite and from which location would it pick the Location.zip file and place it in which location on the fusion HCM server?
    What is the default SFTP user credentials for the Fusion HCM server?

    Thanks,
    -Sridhar

    ReplyDelete
  14. Hi ,

    I have build a new Task "update Employee" for Fusion HCM Spreadsheet Loader.
    But I could able to loaded Data from Excel to Staging table fine.

    But getting below error when Ess App trying to invoke WebService

    And I added My custom Service folder dir structure in APM setup as resources and added req entitlement.

    My Custom Service Name : (BulkLoadPeriodOfServiceService)

    Still am I missing anything or need to register my Server in EM? Plz help ?


    Error =>
    -------------
    Request processing resulted in error with the below message.
    Show details
    ERROR:ESS-07033 Job logic indicated a system error occurred while executing an asynchronous java job for request 1053396. Job error is: javax.naming.NameNotFoundException; remaining name 'BulkLoadPeriodOfServiceService'.


    oracle.as.scheduler.ExecutionErrorException: ESS-07033 Job logic indicated a system error occurred while executing an asynchronous java job for request 1053396. Job error is: javax.naming.NameNotFoundException; remaining name 'BulkLoadPeriodOfServiceService'. at oracle.as.scheduler.rp.AsyncFinalizeProcessor.processFinalizeRequest(AsyncFinalizeProcessor.java:131) at oracle.as.scheduler.rp.AsyncJavaSysExecWrapper.finalizeExecution(AsyncJavaSysExecWrapper.java:250) at oracle.as.scheduler.rp.EndpointProcessor.finalizeExecute(EndpointProcessor.java:1035) at oracle.as.scheduler.rp.EndpointProcessor.finalizeExecuteWrapper(EndpointProcessor.java:997) at oracle.as.scheduler.adapter.EndpointImpl.finalizeExecute(EndpointImpl.java:561) at oracle.as.scheduler.ejb.EssAppEndpointBean.finalizeExecute(EssAppEndpointBean.java:162) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at

    Thanks,
    Siva.




    ReplyDelete