Sunday, February 7, 2016

Configuring HCM Data Loader in Fusion Applications

DISCLAIMER: PLEASE NOTE THAT THE VIEWS PUBLISHED HERE ARE OF THE AUTHOR OWN AND THE RESULTS AND OBSERVATIONS MAY VARY DEPENDING UPON THE APPLICATION VERSION.THESE ARE AUTHOR'S PERSON EXPERIENCES WHICH HE HAS SHARED FOR GENERAL AWARENESS. IF YOU FIND ANYTHING WHICH SEEMS INCORRECT/UNAPPROPRIATE PLEASE FEEL FREE TO WRITE AT ASHISH1234U@GMAIL.COM

 It seems a lot of Traction Now about Fusion Applications and specifically the major challenge faced by most Customers is Data Migration from Traditional ERP Systems to new age Fusion Applications.

Till few months back everyone was using FBL ( File Based Loader) for migration Core HR Related Data and it had lot of issues. To Overcome the Problem Oracle Dev Team has released a new Tool HDL ( HCM Data Loader) which makes bulk loading easy and has many advanced features.

HDL is a feature available in Fusion Applications ( Both On -Premise and Cloud) post 11.1.9.2 ( Release 9.2) but recommended mostly on Rel 9 PB 7 and above.

I have posted an article on MOSC ( My Oracle Support Community) detailing about How-To Configure HCM Data Loader in Fusion Enviornment.

The article has brief description and screenshots. Link can be accessed below :
How-To Configure HCM Data Loader in Fusion Applications


Friday, February 5, 2016

Deleting Unwanted Data From Fusion Applications by Using HCM Delete Diagnostics

DISCLAIMER: PLEASE NOTE THAT THE VIEWS PUBLISHED HERE ARE OF THE AUTHOR OWN AND THE RESULTS AND OBSERVATIONS MAY VARY DEPENDING UPON THE APPLICATION VERSION.THESE ARE AUTHOR'S PERSON EXPERIENCES WHICH HE HAS SHARED FOR GENERAL AWARENESS. IF YOU FIND ANYTHING WHICH SEEMS INCORRECT/UNAPPROPRIATE PLEASE FEEL FREE TO WRITE AT ASHISH1234U@GMAIL.COM


It is a very common requirement to delete unwanted data from Fusion Applications and this can be done via HCM Delete Diagnostics. A tool available in your Non Production Enviornments which enables you to delete unwanted data from the system. Since the deletion happens from Application Base tables the data source ( UI, FBL,HDL, Spreadsheet Loader , Web Service...) or any other mechanism used to upload data hardly matters. caution however must be takien to test this before applying the same blindly.

A brief example with screenshots and worked example can be found below :
Fusion Applications - Using HCM Delete Diagnostics ( A Tool to Delete Unwanted Data From Non Prod Enviornments)



Wednesday, February 3, 2016

Fusion HCM Data Mapping ( Application Screens to Database Fields )

DISCLAIMER: PLEASE NOTE THAT THE VIEWS PUBLISHED HERE ARE OF THE AUTHOR OWN AND THE RESULTS AND OBSERVATIONS MAY VARY DEPENDING UPON THE APPLICATION VERSION.THESE ARE AUTHOR'S PERSON EXPERIENCES WHICH HE HAS SHARED FOR GENERAL AWARENESS. IF YOU FIND ANYTHING WHICH SEEMS INCORRECT/UNAPPROPRIATE PLEASE FEEL FREE TO WRITE AT ASHISH1234U@GMAIL.COM




This is a very common question I have encountered in my previous implementations where in a Consultant ( Technical / Functional / Techno – Functional) or for that matter even Business Users ( for UAT Purposes) are interested to understand which Fusion Applications UI field gets mapped to which corresponding Data Field in Database.
Truly speaking there isn’t a formal documentation available, but I came across a MOSC document Fusion Record Names.pdf ( link : https://community.oracle.com/docs/DOC-821238 ) shared by Prasanna and compiled by him for few of the objects ( related with HCM ).
I have tried to again present the same in a more easy and readable format but the entire hard work is of Prasanna.
Before starting lets try to define / explain the terminologies used in this article . They are :
a)      Object Name
These are the specific objects or Business Entities which are generally represented as a single or multiple pages on UI . Some examples being ( Person , PersonDocumentation, WorkRelationshipByPerson, Location, Jobs, JobFamily, Position , Business Units , Department , Grades, Grade Rates , Element Entry to name a few)

b)      Search Records
These are the Record Types which are exposed on the UI pages and enables search ( Each Page has a search button and that search is executed on a record type variable which is basically a UI Page ). Few Examples being per_persons, per_all_people_f , per_person_names_f ,per_email_Addresses ……. And many more )

c)      Base Records
These are actual Base Records ( in other words actual DB Tables ). Few examples being
            PER_DRIVERS_LICENSE_TYPES , PER_DRIVERS_LICENSES,PER_PERSON_DLVRY_METHODS,PER_IMAGES ,PER_CITIZENSHIPS
              ,PER_CONTACT_RELSHIPS_F ,PER_PERSON_TYPE_USAGES_M ,PER_RELIGIONS ,PER_PHONES ,PER_NATIONAL_IDENTIFIERS …. To
               Name a few.                                                

In Essence basis the below spreadsheet first you identify the Business Object ( from Fusion UI ) and then you need to check all the corresponding tables mentioned and look for the data field. In most cases the names of the field ( called LABEL / PROMPT in UI ) corresponds to similar names in Database but in case of any discrepancy you may raise a SR to get the same clarified.
You may even verify the same by entering some data in UI and checking the same using SQL queries on the Database side ( using BI Data Model in SaaS Environment).

Fusion HCM Data Mapping ( Application Screens to Database Fields)

Friday, January 29, 2016

A Self Published Book on Oracle Applications - "An Introduction To Fusion Applications"

DISCLAIMER: PLEASE NOTE THAT THE VIEWS PUBLISHED HERE ARE OF THE AUTHOR OWN AND THE RESULTS AND OBSERVATIONS MAY VARY DEPENDING UPON THE APPLICATION VERSION.THESE ARE AUTHOR'S PERSON EXPERIENCES WHICH HE HAS SHARED FOR GENERAL AWARENESS. IF YOU FIND ANYTHING WHICH SEEMS INCORRECT/UNAPPROPRIATE PLEASE FEEL FREE TO WRITE AT ASHISH1234U@GMAIL.COM



Please check the same at following link :

https://t.co/aeFJ1SbQRi


Tuesday, January 19, 2016

Fusion Apps Articles

DISCLAIMER: PLEASE NOTE THAT THE VIEWS PUBLISHED HERE ARE OF THE AUTHOR OWN AND THE RESULTS AND OBSERVATIONS MAY VARY DEPENDING UPON THE APPLICATION VERSION.THESE ARE AUTHOR'S PERSON EXPERIENCES WHICH HE HAS SHARED FOR GENERAL AWARENESS. IF YOU FIND ANYTHING WHICH SEEMS INCORRECT/UNAPPROPRIATE PLEASE FEEL FREE TO WRITE AT ASHISH1234U@GMAIL.COM

Please check few of my Fusion Apps Articles on MOSC ( My Oracle Support Community)

Link Below :

https://community.oracle.com/message/13640187#13640187


Please have a look and provide suggestions for improvement

Sunday, October 25, 2015

Creating User Role Details Report in Fusion Applications

DISCLAIMER: PLEASE NOTE THAT THE VIEWS PUBLISHED HERE ARE OF THE AUTHOR OWN AND THE RESULTS AND OBSERVATIONS MAY VARY DEPENDING UPON THE APPLICATION VERSION.THESE ARE AUTHOR'S PERSON EXPERIENCES WHICH HE HAS SHARED FOR GENERAL AWARENESS. IF YOU FIND ANYTHING WHICH SEEMS INCORRECT/UNAPPROPRIATE PLEASE FEEL FREE TO WRITE AT ashish_harbhajanka@yahoo.co.in

Please find the document which provides step-by-step guide to creating a sample BIP Report in Fusion Applications.

Scribd Link :

https://www.scribd.com/doc/286937617/Fusion-Applications-UserRoleDetailReport




Fusion Applications – User Role Detail Report
Business Requirement:
It is a very common requirement in almost all implementation where in the Stakeholders are interested to know how many user accounts are there is the system along with the corresponding roles attached to them. While oracle has delivered some seeded Reports which could be run from ‘Application Diagnostic Framework, screenshot below (Troubleshooting -> Run Diagnostic Tests)
Oracle Delivered Reports
1)      User Roles Display
2)      User and Role : Role Details
3)      User and Role : User Details
                                   Navigation to Run Delivered Reports
Login to Application -> Run Diagnostic Tests
After Running Reports :

Delivered Report 1 -> user Roles Display
User Roles Display: (Displays the Roles attached to procurement user)
Input Value : Sachin.Satpute
Output Screenshot :
Delivered Report 2 -> user and Role : Role Details

User and Role : Role Details
Takes Role Name as input and displays all users who have the role attached to their user account. ( Secured i.e if the individual who is running the Report has access to a specific Data Set individuals belonging to that Data Set would only be displayed).
Input Value : Employee
Output Screenshot :
Delivered Report 3 -> user and Role : User Details

User and Role : User Details
Takes User Name as input and displays all roles attached to the user. (Secured i.e if the individual who is running the Report has access to a User Account being passed as input data would be displayed else no.)
Input value : Sachin.Satpute

Output Screenshot :

Need For a Custom Consolidated Report
But if we need a consolidated report which will list all the users along with all the roles they have neither of the delivered ones help.
So we need a Custom Report (Preferred O/P Format could be RTF, Interactive, Excel,PPT. )
Since we are planning to display a colorful report with Different colour Code Combination ( using Conditional Formatting feature we choose PPT Format for this example)

Custom Report name -> User and Role Details Report
SQL Used for User and Role Details Report

select papf.person_number "PersonNumber",
       ppnf_emp.full_name "PersonName",
           pu_emp.user_start_date "UserAccountStartDate",
           pu_emp.user_end_date "UserAccountEndDate",
           pu_emp.is_user_account_active "UserAccountStatus",
           pu_emp.username "UserAccountName",
           pu_emp.is_user_account_suspended "IsUserAccountSuspended",
           pu_emp.user_distinguished_name "UserAccountInternalName",
           pur_emp.method_code "RoleProvisioningMethod",
           pur_emp.is_user_role_active "IsUserRoleActive",
           pur_emp.is_user_role_terminated "IsUserRoleTerminated",
           Case
           When (pur_emp.is_user_role_active = 'Yes' AND pur_emp.is_user_role_terminated = 'No' ) Then 'Active'
           When (pur_emp.is_user_role_active = 'No' AND pur_emp.is_user_role_terminated = 'No') Then 'NeitherActiveNorTerminated'
           When    (pur_emp.is_user_role_active = 'Yes' AND pur_emp.is_user_role_terminated = 'Yes') Then 'ActiveAndTerminated'
           When    (pur_emp.is_user_role_active = 'No'  AND pur_emp.is_user_role_terminated = 'Yes' ) Then 'InactiveAndNotTerminated'
           End as "UserRoleStatus",   
           pur_emp.role_start_date "UserRoleStartDate",
           pur_emp.role_end_date   "UserRoleEndDate",
           prd_emp.abstract_role   "IsRoleAAbstractRole",
           prd_emp.job_role        "IsRoleAJobRole",
           prd_emp.data_role       "IsRoleADataRole",
           prd_emp.is_role_active  "IsRoleActive",
           Case
           When (prd_emp.abstract_role = 'Y' AND prd_emp.job_role = 'N' AND prd_emp.data_role = 'N' ) Then 'Abstract Role'
           When (prd_emp.abstract_role = 'N' AND prd_emp.job_role = 'Y' AND prd_emp.data_role = 'N' ) Then 'Job Role'
           When (prd_emp.abstract_role = 'N' AND prd_emp.job_role = 'N' AND prd_emp.data_role = 'Y' ) Then 'Data Role'
           When (prd_emp.abstract_role is NULL AND prd_emp.job_role is NULL AND prd_emp.data_role is NULL ) Then '--NA--'
           End as "UserRoleType",
           prd_emp.role_common_name "RoleCommonName",
           prd_emp.multitenancy_common_name "MultitenancyCommonName",
           prd_emp.role_distinguished_name "IsRoleDistinguishedName",
           prdt_emp.role_name       "UserRoleName",
           prdt_emp.RoleDescription
FROM   per_all_people_f papf
JOIN 
(
SELECT ppnf.full_name,
        ppnf.person_id
 FROM   per_person_names_f ppnf
 WHERE  ppnf.name_type = 'GLOBAL'
 AND    TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
) ppnf_emp
ON (
    ppnf_emp.person_id  = papf.person_id
    AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   )
LEFT OUTER JOIN
(
SELECT pu.person_id,
       pu.user_id, 
       pu.username,
       to_char(pu.start_date,'DD-MM-RRRR') user_start_date,
       to_char(pu.end_date,'DD-MM-RRRR')   user_end_date,
           DECODE(pu.active_flag,'N','Inactive','Y','Active') is_user_account_active,
           DECODE(pu.suspended,'N','No','Y','Yes') is_user_account_suspended,
           pu.user_distinguished_name
FROM   per_users pu
) pu_emp
ON (pu_emp.person_id = papf.person_id AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date)
LEFT OUTER JOIN
(
SELECT pur.user_id,
       pur.role_id,
           pur.role_guid,
           DECODE(pur.method_code,'A','Automatic','M','Manually','E','Externally Provisioned') method_code,
           DECODE(pur.active_flag,'N','No','Y','Yes') is_user_role_active,
           DECODE(pur.terminated_flag,'N','No','Y','Yes') is_user_role_terminated,
           to_char(pur.start_date,'DD-MM-RRRR') role_start_date,
           to_char(pur.end_date,'DD-MM-RRRR')   role_end_date
FROM   per_user_roles pur
) pur_emp
ON (pu_emp.user_id = pur_emp.user_id)
JOIN
(
SELECT          prd.role_id,
        prd.role_guid,
                        prd.abstract_role,
                        prd.job_role,
                        prd.data_role,
                        DECODE(prd.active_flag,'N','No','Y','Yes') is_role_active,
                        prd.role_common_name,
                        prd.multitenancy_common_name,
                        prd.role_distinguished_name
FROM    per_roles_dn prd
) prd_emp
ON (pur_emp.role_id = prd_emp.role_id AND pur_emp.role_guid = prd_emp.role_guid)
JOIN
(
SELECT          prdt.role_id,
                        prdt.role_name,
                        prdt.description RoleDescription,
                        prdt.source_lang
FROM            per_roles_dn_tl prdt
) prdt_emp
ON (prd_emp.role_id = prdt_emp.role_id AND pur_emp.role_id = prdt_emp.role_id AND prdt_emp.source_lang = 'US' )
WHERE papf.person_number = nvl(:pn_person_number, papf.person_number)
AND   TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date





Conditional Formatting
The different colour code feature can be added using the Conditional Formatting fields
( Highlight and Manage Formats )


Report Output

Scheduling the Report

Scheduling the Report :
 
Scheduled Output (PPT Format)
ESS Job Creation
Parameter Definition

Running Scheduled Job



Running Scheduled Job for One Person

Person Number : 10