Fusion Apps File Based Loader (FBL) - Part II

In my previous post I explained how to generate cross reference files from Fusion. I will use those files to generate my data file. You have to create data file as per the format which can be read by FBL. The file must be  '|' delimited flat file. It should have column which are supported by FBL. 

Now the question is how will you know what are the columns it looks for. Oracle has published a column mapping spread sheet which is there at Data Conversion Reference Library. If you do not have access to it, then as per the MOS document Oracle Fusion Human Capital Management File-Based Loader V1.1 [ID 1533860.1] ask your COE to send an email to Oracle. This has all the required resources for HCM Data Conversion.
One of the important files, is the column mapping spread sheet which you can get it from here. You need to filter the business object in that spread sheet. For my case it is Location. So I filtered it and it looks like the next picture.

This spreadsheet explains what should be the columns present in the flat file. You can maintain any order of the columns in the flat file but the first row of your file should be the column names.
Here is the sample data file which I used for my test.



If I open my data file in excel, it looks like this.

You can see that I have provided LOCATION_ID a value from my side which has nothing to do with LOCATION_ID stored in PER_LOCATIONS table in fusion. Only constraint is that it has to be unique. So you can have your own naming convention for this. Next as per the column mapping spread sheet the SET_ID value has to be taken from the  SET_ID cross reference file. You will see that file in the in zip created as per my first post. Also keep in mind  that date fields must have date format specified in the mapping sheet. If you do not maintain the format you are going to get the errors while loading the data in fusion. Here for location it has to be YYYY-MM-DD. Once you create the file you have to name it as per the Fusion standard. For location it is F_LOCATION_VO.dat. Create a folder named Location in your machine and put the F_LOCATION_VO.dat file inside it and then zip the Location folder and name the zip file whatever you want. I named it as LocationMehabub28.zip. You can specify any name you want.
You can load multiple business objects at a time. If you do that then you have to create separate folders for each Business object and inside each folder you have to put your data file with related objects like the next picture

Once you are done with putting all data files created as per the column mapping spreadsheet then take all folders and zip them. This zip file is going to be the file which will be uploaded to Fusion.

The flat files will be having naming convention as F_<BUSINESS OBJECT>_VO.dat.
One of the biggest challenges in FBL is creating these flat files. If you want to convert all objects in one shot then you have to create the relationship yourself. For example if you want to create departments along with location then you will create location data file by providing LOCATION_ID a unique value and then passing this value to LOCATION_ID column of department file. In this way you create the referential data integrity in the flat file itself. When you load the zip file in Fusion, it first extracts the individual business objects and then dynamically creates the loading order as per the data model created in HCM. There is a table which stores this loading order sequence of the HCM Data Model. So whenever you load business objects through FBL oracle knows which objects need to be converted first and accordingly it creates mapping information with data file and fusion ids. 
Here is the link from where you can get the sample data files for each business object. The next step is to register the zip file in fusion. This is done by calling a composite HcmCommonBatchLoaderCoreInboundLoaderComposite. If you are a cloud customer oracle will give you the URL for the composite. But if you have on-premise installation, then log on to your Oracle Enterprise Manager Fusion Applications Control and search the composite under Farm_HCMDomain -> SOA->soa-infra-->default as shown in the next picture.

Copy the URL and paste it in mozila firefox










Once you call the composite it will look like this

Here under payload section you specify any unique value for RunControlID and ProcessInstance.
For ZipFileName specify the zip file which you created. For me it is LocationMehabub28.zip. For FileLocation, if you are SaaS/Cloud customer then Oracle will provide you the SFTP path where you need to copy the zip file. Generally you will put your zip file under ftp_inbox folder of the SFTP path. But if you are on-premise customer then your system administrator has to setup the SOA FTP Adapter. The instructions are documented in Fusion Applications Post-Installation Guide. Once your administrator has setup that, he can give you the host and unix path where you need to copy the zip file. For me the path has been setup as /home/appsfa/E_1. As I have copied the file under ftp_inbox, I have specified the FileLocation as ftp_inbox.
The next picture shows that I have put my file there.

Now let's see how will I tell Fusion how many Business Objects are there inside the zip file.

As shown in the picture you specify the Business Object names under LBOList section of the payload. As I am only loading location, I have specified Location as the LBO. If you have multiple, then click on + sign to add them.



Now all these web-services are having security policies associated with them and you have to specify the User ID and password under WS-Security Section. This is the application user id and password who is having required privileges.

Once you submit you should get the No Response  as shown in the next picture

This means the composite call is successful. Check in unix box the file should be removed from ftp_inbox.

Now many of you might be asking where did fusion put this file in the system and what does it do with this file. In order to get the details you need to have some knowledge about SOA. I assume that you have some basic idea about that. So let's get into the business of tracing the flow. This section applies to On-premise customer because in SaaS environment you will not have EM access.

Log on to Oracle Enterprise Manager Fusion Middleware Control and search the composite HcmCommonBatchLoaderCoreInboundLoaderComposite which you called.

Under dashboard you will get all the recent instances of the of the service.

Click on InboundLoaderProcess.
You will see something like the next picture.

This means that it has actually moved the file to /u01/ODI_FILE_ROOT_HCM/E_1/X_300000001133519/stage folder. You can cross verify that from unix.
It also calls a python script to unzip the files, create the loading sequence of the business objects and then creates control file for each LBO.
The following picture taken from Oracle Enterprise Manager Fusion Applications Control shows which script it calls and where we can find that script

We will see what this python script hr2hrSqlldr.py does in detail in the next series Part III.









The following picture confirms that the composite process has successfully completed.

In the next post I will explain how to import the data from this file to Fusion Tables.
















So you now know how to create the data files, where to put that and how to register the files in Fusion.
There are few documents about FBL - FBL Overview, User Guide and Step-by-Step instructions in Data Conversion Reference Library.

Please go through them to have solid understanding.

Cheers.

Fusion Apps - HCM File Based Loader (FBL) - Part I

File Based Loader is the inbound integration tool. Oracle has also HR2HR but that is for integration between EBS/PSFT and Fusion. Let's see what are these two.

HR2HR:
HR2HR is designed to allow a source system of record (typically one of the applications unlimited products) to co-exist alongside a target Fusion system, allowing data to be iteratively transferred from the source to the target ensuring that Fusion is synchronized with the system of record. Typically this is used by customers who want to take advantage of new functionality provided in the Fusion Talent, Compensation, Core HR offerings, whilst maintaining their source system as the system of record for reference and employee data.
In HR2HR there are predefined extract mechanisms that are built for EBS and PSFT. These generate a snapshot of the data that exists in the system of record. This is then moved onto an SFTP server and Fusion is notified via a webservice call that such a file is ready for processing. Fusion then retrieves the file from the SFTP server, unzips it and initiates processing. In the 1st stage of processing it is passed through an ODI layer where the data is compared against the existing Fusion data and a series of records is written to the staging tables that represent the specific actions that need to be taken on particular data objects in order to synchronize them with the system of record. These changes might be creates, updates, deletes or data effective changes.

File Based Loader:
File Based Loader is designed to support the same set of objects as HR2HR but where there is not a predefined source system of record. The intention is that this will be used by customers who are only performing an initial data load and who want to bulk load a large volume of data including history. Unlike HR2HR, File Loader simply takes the data from the zip files and loads it directly into the data loading stage tables rather than going through an ODI layer. It then processes the staging data to resolve the primary and foreign key references.
Because File Loader does not have a dependency on ODI it has been possible to extend the number of attributes that are supported within each object, and so for example descriptive flexfield columns are supported in File Loader whereas they are not available in HR2HR.
However, as File Based Loader does not have any pre-defined extracts to populate the data files it is necessary for implementation teams to develop their own extract mechanisms and to ensure that the data is in a form that will satisfy the Fusion business rules.
File Loader supports both data creation and updates. It also supports the full creation of history as well as processing terminated work relationships.

So in short 

• Use FBL to Load HR Data from your legacy system to Fusion HCM
• It is a flat file based data upload utility
• It validates and loads data into Fusion
• It supports error correction
• It loads complex data objects with full date effective history
• You can use multiple data sources to prepare the data file
• Can be used as Application to Application Integration
• Can be used as Conversion Tool
• Can be used for on going data sync
• It is mainly used for Bulk data loading
• Can be used to update business objects provided they were created using FBL. At the end of this series I will show that data created in Fusion through UI can not be updated using FBL.

How File-Based Loader Works
File-Based Loader uses the Oracle Fusion HCM Load Batch Data process to load your source data to the Oracle Fusion application tables. Load Batch Data is a generic utility for loading data to Oracle Fusion from external sources; for example, it is used by Oracle Fusion Coexistence for HCM, the Oracle Fusion Enterprise Structures Configurator (ESC), and the Oracle Fusion HCM Spreadsheet Data Loader. A key feature of Load Batch Data is that data is imported initially in object-specific batches to stage tables. Data is prepared for load in the stage tables and then loaded from the stage tables to the Oracle Fusion application tables.

For this process to succeed, you need to deliver your data to File-Based Loader in a format that satisfies Oracle Fusion business rules. The Oracle Fusion Human Capital Management File-Based Loader V1.1 User’s Guide explains how to meet this requirement. The MOS note is Oracle Fusion Human Capital Management File-Based Loader V1.1 [ID 1533860.1]

What are other tools available

Oracle has a documented these under HCM SaaS Integrations. Here is the slide which talks about that

What is the latest version of FBL

The latest FBL version is FBL1.1 and is available from 11.1.5.0.0.

End to End Picture

This picture shows the full flow of FBL data loading process. The process starts from Fusion HCM. In order to successfully load data you need to perform the following steps.

1.User Performs Steups in Fusion HCM - Select and Configure Offerings like Workforce Deployment, Workforce Compensation depending on your requirements and business process flow. Create Implementation Projects and complete all the configuration tasks.

2. Generate Mapping File and Send to Source System - Once the configuration steps are completed you need to create reference mapping file which will be used while creating actual data files. In order to do that you need to first specify the configuration parameters. Go to Administration --> Setup and Maintainace.
Click on All task tab and search for task Manage HCM Configuration for Coexistence as shown

Click on Go to Task. Which will open the screen like the next picture

Click on Detach to see the full table. Specify the values of all the parameters. Below are the details of the parameters. Depending on your version you will see some extra parameters. For me it is 11.1.4.0.0 and FBL 1.0

PARAMETER NAME	PARAMETER VALUE
Environment Properties File	/u01/APPLTOP/instance/ess/config/environment.properties
HRC Product Top	/u01/APPLTOP/fusionapps/applications/hcm/hrc
Load HCM Data Files Automatically	N
Loader Cache Clear Limit	99
Loader Chunk Size	200
Loader Maximum Errors	100
Loader Number of Processes	1
Loader Save Size	1
ODI Context	DEVELOPMENT
ODI Log Level	2
ODI Language	AMERICAN_AMERICA.WE8ISO8859P1
ODI Password	(As supplied by Oracle)
ODI Root Directory	/u01/oracle/ODI_FILE_ROOT_HCM
ODI User	FUSION_APPS_HCM_ODI_SUPERVISOR_APPID
ODI Work Repository	FUSIONAPPS_WREP
On Demand FTP Root Directory	/u01/oracle/ODI_FILE_ROOT_HCM/ftp_inbox
Use Python Loader	Y
User Name Expression	Optionally, you can specify one of:
	loaderCtx.getPersonAttr(“PersonNumber”)
	loaderCtx.getAssignmentAttr(“AssignmentNumber”)

Note: If you have on-premise customer then replace APPLTOP with the actual path. Specify ODI password. All of these will be provided by your DBA who installed Fusion Apps.

Your parameters should look like this

Click on Submit as shown in the next picture to save this Configuration in the system.

Once you click you submit it will give you Confirmation and click on OK.

Now that configuration parameters are set and also Offering configurations are complete we need to generate the mapping file. From the same screen click on Submit from within Generate Mapping File for HCM Business Objects as shown

It will submit an Enterprise Schedular Job. Note down the job number. We may need this to debug and find your corresponding zip file name.

Click on refresh to check the status. If the process status is 12 that means your file has been successfully created. Any other status means you have some problems. To find out exact cause you have to check the ESS log from Navigator --> Tools --> Scheduled Process. If you don't find meaningful error detail then you have to check in Fusion HCM Domain EM console. I will explain in a separate post about how to check log files from EM. Once completed note the zip file name and log into unix box and go to ODI Root Directory/E_1

For Example the process ended in status 12 as shown.

For me the ODI root/E_1 is
/u01/ODI_FILE_ROOT_HCM/E_1. For SaaS customer this will done by Oracle or they can give you access where all these files will be copied.
Now use any FTP tool to copy the file. As shown in the picture it creates a separate folder for each run. The name is same as the numeric part of the zip file.

I used WinSCP to copy the file from unix server to my local machine.

You can now see the file in your local machine.

Unzip the file and you can see list of cross reference files generated by Fusion.

In my example I will show how to upload Location in fusion. And all the locations which I will upload will be associated with Common Set ID so that the locations are available for all Legal Entities.

You can upload all these files and store them in mapping tables using SQL*Loader. While writing extraction script you can dynamically get the information from there and create data files.

So till now I have explained

• What is HR2HR?
• What is FBL and how does it work?
• How to configure Fusion to start using FBL?
• How to generate Cross Reference Files?
• How to get that from Unix and save it to Local machine?

In the following two series I have explained how to create data file for Location Business Object and how to register the file in Fusion for processing:

1. Fusion Apps - File Based Loader Part -II
2. Fusion Apps - File Based Loader - Part III

----------Updated on 7th July 2013  with link of the remaining two series...

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

Fusion Apps SaaS Service Scenarios

Hosting Service scenarios for Oracle Cloud Fusion Applications services

Oracle has documented different service scenarios which customer may ask for if they want to use SaaS. While creating service requests for cloud apps you will be presented with a list of services that Oracle will support.

Following are the services that are provided:

Email Notifications - Enable or disable email notifications

IP Address Whitelist - Allow access to Oracle Fusion Cloud Services to a list or range of specific client IP addresses for their services from corporate network.

Language Pack installation - Ask for additional language pack.

Personalize Logo - Personalize Fusion Applications with your company logo.

Production to Test (P2T) copy - Copy the Production environment database into a Test environment on a one-off basis.

Enabling Flexfields for OTBI analysis - You can configure DFF and create analysis/dashboard based on that. To enable the DFF for OTBI analysis you can create service requests.

FA Admin Password reset  - Request to reset password provisioned with IT Security Manager Job Role, Configure SSO -  Configure Single Sign-On (SSO) access to Oracle Fusion Cloud Services based on Identity Federation.

Environment Resizing - By default, test environments are sized to support enough concurrent users for basic use of the environment. In order to assure that your use of the test environment will not be limited on a go-forward basis, you should request the pod resizing service option for your test environment(s) early on in your implementation. Production environments are sized to support the number of users you have purchased

All of these are well documented in MOS note Hosting Service scenarios for Oracle Cloud Fusion Applications services [ID 1529174.1]

Fusion Apps - Synchronizing Customized Flexfields in the MDS Repository for SOA

In my previous post I explained  How to Configure Extensible Flexfield.  When you customize and deploy Oracle Fusion Applications flexfields a new XSD file is generated in the MDS repository for Oracle Fusion Applications. SOA composite applications in Oracle Fusion Applications reference copies of the original XSD schema files included in the MDS repository for SOA. So the updated XSD files must be synchronized in the MDS repository for SOA for use in the fact models in business rules. 

To perform this synchronization, a special SOA composite application named UpdateSOAMDS is included with Oracle Fusion Applications. By default, UpdateSOAMDS is automatically deployed. When a synchronization is required, you manually invoke an instance of this SOA composite application to synchronize the updated XSD files in the MDS repository for SOA. You can view the results of this synchronization in the audit trail in Oracle Enterprise Manager Fusion Applications Control.

Let's see how to do that. In this example I had modified Location Information EFF in Fusion HCM Workforce Structure. So I should log  into HCM Domain and run the UpdateSOAMDS  from there.

Click on the UpdateSOAMDS composite. On the right hand side click on Test button. Once you click on Test, you will see a screen like the next picture.

Select Operation - updateDuring  and payload as P1D. It means last 1 day. If you want to run it for last 1 Month and 15 days and  3 Hour then it should be P1M15DT3H. After entering the values click on Test Web Service.

You can trace the result.

Click on Instances

Click on the latest instance id which has been submitted by you. It will open a new screen which looks like the next picture.

Click on the composite to trace.

As marked in the picture, after running the composite the system has synchronized the XSD schema of the modified Location Information EFF extensible flexfield to SOA metadata repository.

This will make the new context sensitive segments to be available for use in SOA.

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

Fusion Apps - Customizing and Extending BPMN Processes

The Oracle Fusion Customer Relationship Management (CRM) and Oracle Fusion Human Capital Management (HCM) product families of Oracle Fusion Apps use Business Process Management Notation (BPMN) processes to define some of the process flows used within their applications.

Business Process Composer enables you to customize these processes. In Fusion Apps BPMN Processes has been deployed using BPM Projects which in turn has used Project Templates specifically provided for Fusion Apps. 

The technical components inside PBM projects are called the business catalog. The business catalog contains various reusable services that a BPMN process can use to connect to other components of the application, including other processes, systems, and databases.

The business catalog contains the following reusable components:

Business Catalog Components Available in Business Process Composer

Business Catalog Component	Description
Services	Services are used to connect a BPMN process with other processes, systems, and services, including BPEL processes, databases.
Human tasks	Human tasks enable you to define how end users interact with your BPMN processes. Human tasks are implemented in a BPMN process using the user task.
Business rules	Oracle business rules are statements that describe business policies or describe key business decisions.

Oracle Fusion Apps provide multiple tools for customizing and extending BPMN processes. These tools are 

This tool...	Enables you to...
Business Process Composer	Customize BPMN processes by creating and deploying BPM projects based on project templates.
Oracle BPM Studio (JDeveloper)	Customize project templates.
Oracle SOA Composer	Customize business rules, domain value maps, and approval configuration and assignment rules at runtime
Oracle BPM Worklist	Customize approval configuration and assignment rules.

As with many ERPs, there are mainly two types of customization - Design Time(DT) and Run Time(RT)

Now if you go by the present Fusion Apps implementation model then it is the SaaS model which most the customers are going for. In SaaS there are multiple access constraints and one of them is that you can not do DT Customization. So you are left with RT customization. Logically next question will be "What can we do with BPMN Processes at RT?"

You can use SOA Composer and BPM Worklist to customize

• Oracle business rules
• Domain value maps
• Approval assignment rules in human workflows such as customizing the approval flow for a specific customer.

These customizations can be made directly to a running Oracle Fusion application without having to redeploy the BPM project.

Using Business Process Composer

you can make the following customizations to a BPM project created from a project template:

• Customize an existing BPMN process.
• Create new BPMN processes.
• Create simple data objects.
• Create and modify some business catalog components.

List of Business Catalog Components

Business Catalog Component	Can be created using Business Process Composer?	Can be customized using Business Process Composer?
Business rules	No	Yes
Human tasks	Yes You can create human tasks using Business Process Composer, however not all functionality of a human task can be customized.	Yes
Services	Yes	Yes

If you want to customize anything other than those mentioned above, then you have to use JDeveloper using Customization Role. There are three blog posts by Fusion Applications Developer Relations Team about how to setup JDeveloper environment for Fusion Appplications Development. 

• JDeveloper and Fusion Applications Explained (Part 1)
• JDeveloper and Fusion Applications Explained (Part 2)
• JDeveloper and Fusion Applications Explained (Part 3)

You can also refer John Brock's blog post on Setting Up A Fusion Development Environment. Here in this blog I will cover RT customizations.

Now you know what can be done with what Tools. So the next logical step will be how to access these tools in Fusion Apps environment.

Let's start with SOA Suite(SOA Composer & BPM Worklist)

Starting the Customization Tool

For Customizing...	Start...	By Entering...
Business rules
Approval configuration and assignment rules	Oracle BPM Worklist (recommended) Oracle SOA Composer Note: If you use Oracle SOA Composer to customize approval configuration and assignment rules during runtime, changes in subsequent revision patches are not applied successfully.	http://host:port/integration/worklistapp http://host:port/soa/composer
Nonapproval business rules	Oracle SOA Composer	http://host:port/soa/composer
Domain value maps	Oracle SOA Composer	http://host:port/soa/composer
SOA composite application endpoint properties such as Oracle WSM security policies and binding component properties	Oracle Enterprise Manager Fusion Applications Control	http://host:port/em

Let's say your HCM Product Family has been deployed on host yourserver.com and port 10619 then SOA Composer can be accessed http://yourserver.com:10619/soa/composer. Once you open the link it may ask you login credentials depending on whether you are already logged on to Fusion Apps or opening a new session. After successful login you would see a screen like this

SOA Composer

BPM Worklist

Alternatively if you have HCM Domain EM access then you can use that to get the SOA Composer and BPM Worklist urls for modifying HCM Approval rules and notifications. You can get the host and port details of HCM Domain using this post. Using this post you can get the roles needed to access EM.

This is the way you get url for BPM Worklist. Similarly you can get the url for SOA Composer and BPM Composer.

There is also FSM task to configure BPM Worklist

Now lets see what are the roles you need to access SOA Composer/BPM Worklist/BPM Composer. The duty role that is needed is BPM Workflow System Admin Role. So let's see what is there in this duty role. As this is a duty/application role, you need to use APM to see the details. So Log on to APM. Here is the post which describes how to navigate to APM. Once you log in then search the role and click on Open as shown

This shows the Application Role Hierarchy

As you can see that this duty has been mapped to External Role/Job Role Application Implementation Consultant and Human Capital Management Application Administrator. SO if you have either of them then you access SOA Composer/BPM Composer/BPM Worklist.

Summary

So you now know

• What are BPMN Processes??
• How they are implemented??
• What Business Catalog Components are available??
• What Tools are available to Customize them??
• How to access those tools??
• What Duty Roles do you need??
• What Job roles d you need??

In the next post I will show how to customize tasks and it's rules using either SOA Composer or BPM Worklist or FSM Task for HCM Approval Configuration.

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