HDL Template for Assignment Supervisor

HDL Template for Assignment Supervisor

Prerequisite: 

1. Worker data should be loaded. Click here to find the sample Worker.dat file.   

To load the assignment supervisor we will use Worker.dat template.

You can see step by step video to load assignment supervisor using this Link

Please find the below sample template :

METADATA|AssignmentSupervisor|AssignmentId(SourceSystemId)|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|ManagerAssignmentId(SourceSystemId)|ManagerId(SourceSystemId)|ManagerType|PersonId(SourceSystemId)|PrimaryFlag

MERGE|AssignmentSupervisor|XXTEST_ASSIGN2|XXTEST|XXTEST_MANAGER1|2018/01/01|4712/12/31|XXTEST_ASSIGN1|XXTEST_PER1|LINE_MANAGER|XXTEST_PER2|Y

Oracle Cloud - Load Worker Data - HDL

 Load Worker Data using HCM Loader

In earlier blog we have seen how to define Source Keys and we have created "XXTEST".

Prerequisite: 

1. Work structure should be loaded. Click here to find the sample Work structure.   

We will use the same Source Key while loading the HCM Object in Oracle Fusion.

You can see step by step video using this link

Now we will see how to load Worker data in Oracle Fusion using HCM Loader.

Components in Worker File which we are going to load :

Worker

Person Name

National Identifier

Email

Phone

Address

Citizenship

Ethnicity

Legislative Data

Work Relationship

Work Terms

Assignments

Download the object template from instance.

Navigation to download :

Navigator -> My Client Group -> Data Exchange -> View Business Objects 

You can see list of all object template.

Download the template for Worker Objects.

We will use only required fields from that template to load the Worker Object.

Lets see object wise HDL File.

METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonNumber|ActionCode|StartDate|DateOfBirth

MERGE|Worker|XXTEST|XXTEST_PER1|2018/01/01|4712/12/31|XXTEST1|HIRE|2018/01/01|1975/01/01

METADATA|PersonName|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|LegislationCode|NameType|FirstName|MiddleNames|LastName|Title

MERGE|PersonName|XXTEST|XXTEST_PERNAME1|XXTEST_PER1|2018/01/01|4712/12/31|US|GLOBAL|Adam||Baro|MR. 

METADATA|PersonNationalIdentifier|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegislationCode|IssueDate|ExpirationDate|NationalIdentifierType|NationalIdentifierNumber|PrimaryFlag

MERGE|PersonNationalIdentifier|XXTEST|XXTEST_NID1|XXTEST_PER1|US|||SSN|456-23-4167|Y

METADATA|PersonEmail|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateFrom|DateTo|EmailType|EmailAddress|PrimaryFlag

MERGE|PersonEmail|XXTEST|XXTEST_EMAIL1|XXTEST_PER1|2018/01/01|4712/12/31|W1|Adam.baro@ha.com|Y

METADATA|PersonPhone|SourceSystemOwner|SourceSystemId|PhoneType|DateFrom|DateTo|PersonId(SourceSystemId)|PhoneNumber|PrimaryFlag

MERGE|PersonPhone|XXTEST|XXTEST_WPH1|W1|2018/01/01|4712/12/31|XXTEST_PER1|789-123-2345|Y

METADATA|PersonAddress|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|AddressType|AddressLine1|AddressLine2|AddressLine3|AddressLine4|Building|FloorNumber|TownOrCity|Region1|Region2|Region3|Country|PostalCode

MERGE|PersonAddress|XXTEST|XXTEST_ADDR1|2018/01/01|4712/12/31|XXTEST_PER1|HOME|300 Some street|Unit 1|||||Pasadena|Los Angeles|CA||US|91101

METADATA|PersonCitizenship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateFrom|DateTo|LegislationCode|CitizenshipStatus

MERGE|PersonCitizenship|XXTEST|XXTEST_CTZSHIP1|XXTEST_PER1|2018/01/01|4712/12/31|AM|A

METADATA|PersonEthnicity|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegislationCode|Ethnicity|PrimaryFlag

MERGE|PersonEthnicity|XXTEST|XXTEST_ETHNIC1|XXTEST_PER1|US|1|Y

METADATA|PersonLegislativeData|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|MaritalStatus|MaritalStatusDate|Sex

MERGE|PersonLegislativeData|XXTEST|XXTEST_LEG1|2018/01/01|4712/12/31|XXTEST_PER1|US|M|1998/05/12|M

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|DateStart|EnterpriseSeniorityDate|LegalEmployerSeniorityDate|ActualTerminationDate|Comments|LastWorkingDate|NotifiedTerminationDate|PrimaryFlag|ProjectedTerminationDate|RehireAuthorizerPersonId|RehireAuthorizor|RehireReason|WorkerNumber|WorkerType|OnMilitaryServiceFlag|RehireRecommendationFlag

MERGE|WorkRelationship|XXTEST|XXTEST_PDSERVICE1|XXTEST_PER1|US1 Legal Entity|2018/01/01|||||||Y|||||XXTEST1|E|N|Y

METADATA|WorkTerms|ActionCode|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentName|AssignmentNumber|AssignmentStatusTypeCode|AssignmentType|BusinessUnitShortCode|PositionOverrideFlag|PrimaryWorkTermsFlag|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|LegalEmployerName|SystemPersonType

MERGE|WorkTerms|HIRE|XXTEST|XXTEST_WRKTERM1|2018/01/01|4712/12/31|1|Y||XXTEST_WRKTERM1|ACTIVE_PROCESS|ET|US1 Business Unit|Y|Y|XXTEST_PDSERVICE1|XXTEST_PER1|US1 Legal Entity|EMP

METADATA|Assignment|ActionCode|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentName|AssignmentNumber|AssignmentStatusTypeCode|AssignmentType|BusinessUnitShortCode|PositionOverrideFlag|PrimaryAssignmentFlag|PrimaryFlag|SystemPersonType|LegalEmployerName|JobId(SourceSystemId)|LocationId(SourceSystemId)|OrganizationId(SourceSystemId)|PositionId(SourceSystemId)|GradeId(SourceSystemId)|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PersonTypeCode|ManagerFlag|LabourUnionMemberFlag|WorkTermsAssignmentId(SourceSystemId)|AssignmentCategory|Frequency|HourlySalariedCode|NormalHours|NoticePeriod|ProbationPeriod|ProbationUnit|ReasonCode

MERGE|Assignment|HIRE|XXTEST|XXTEST_ASSIGN1|2018/01/01|4712/12/31|1|Y|E-XXTEST1|XXTEST_ASSIGN1|ACTIVE_PROCESS|E|US1 Business Unit|Y|Y|Y|EMP|US1 Legal Entity|XXTEST_JOB1|XXTEST_LOC65XXTEST_DEPT1||XXTEST_GRADE01|XXTEST_PDSERVICE1|XXTEST_PER1|Employee|Y|N|XXTEST_WRKTERM1|FR|W|S|40|0|||NEWHIRE

Save as "Worker.dat"

Make zip file including all object's HDL File.

Load the zip file in Oracle Fusion

Navigator -> My Client Group -> Data Exchange -> Import and Load -> Import

Import the prepared zip file.

Refresh to see the status of loading.

Once Loaded successfully, you can verify the Work Structure.

You can see step by step video using this link

Oracle Cloud - Creation of Simple OTBI Report

Development of Simple OTBI Report

In this blog we are going to learn how to develop/create your first OTBI report.

You can see Step by Step Creation of OTBI Report in this video

We will develop the report for below details:

• Person Number
• Date of Birth
• First Name
• Last Name
• Grade Name
• Job Name
• Department

To achieve this requirement follow the below steps.

Step 1 - 

Open the Oracle Fusion Application.

Step 2 - Navigation

Follow the below navigation,

Navigator -> Tools -> Report & Analytics -> Click on Browse Catalog

Step 3 - Creation of Simple Report 

Click on New and then Analysis

It will pop up list of predefined Subject Areas in Oracle Fusion.

Select the appropriate Subject Area to develop the OTBI report.

As per our requirement we are going to select "Worker Management - Worker Assignment Real Time"

Subject Area.

It will open the report development page.

It contains 3 sections:

1) Subject Area - 

It hold all the selected Subject Area Details.

2) Selected Columns - 

In this section we can add columns required for report.

3) Filters - 

Using this section we can add filter to restrict the data in report.

Step 4 - Adding Column in Report

As per our requirement add the columns.

Person Number, DOB, First name, Last Name are related to Worker.

Expand the Selected Subject Area then Search for Worker Folder to add above columns.

Expand Worker Folder add above mentioned columns like Person Number, DOB etc.

Then add Job,Grade and Department Name columns from respective folders.

To run the report click on Results.

It will give you the output.

Step 5 - Change the Column Heading

Click Column Setting -> Column Properties -> Column Format (tab)

Enable the Custom Heading check box.

Enter the required Column Heading.

Run the report again to check if column heading are changed or not.

Step 5 - Add Filter on OTBI Report.

Suppose we need to apply filter on Person Number then click Column Setting  of Person Number, select Filter.

It will popup filter details window.

In this you can mention how you need to apply the filter based on your requirement.

Run the report to check if filter is working fine.

Step 6 - Save the OTBI Report.

Save the report in My Folder.

You can find your report in My Folder.

You can see Step by Step Creation of OTBI Report in this video

Oracle Cloud - Load Work Structure Components - HDL

 Load Work Structure using HCM Data Loader

In earlier blog we have seen how to define Source Keys and we have created "XXTEST".

We will use the same Source Key while loading the HCM Object in Oracle Fusion.

You can see step by step video using this link

Now we will see how to load Work Structure Components in Oracle Fusion using HCM Loader.

Work Structure Components :

• Grade
• Job
• Location
• Organization
• Position 

Download the object template from instance.

Navigation to download :

Navigator -> My Client Group -> Data Exchange -> View Business Objects 

You can see list of all object template.

Download the template for Work Structure Objects.

We will use only required fields from that template to load the Work Structure Object.

Lets see object wise HDL File.

Grade : 

METADATA|Grade|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|SetCode|GradeCode|GradeName|ActiveStatus

MERGE|Grade|XXTEST|XXTEST_GRADE01|1951/01/01|4712/12/31|COMMON|XXTEST_GRADE01|XXTEST Grade01|A

Highlighted in Yellow are the Source Keys used to load.

Save file as "Grade.dat"

Job:

METADATA|Job|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|SetCode|JobCode|Name|ActiveStatus|RegularTemporary

MERGE|Job|XXTEST|XXTEST_JOB1|1951/01/01|4712/12/31|COMMON|XXTEST_CFO|XXTEST: Chief Financial Officer|A|R

Save file as "Job.dat"

Location :

METADATA|Location|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|SetCode|LocationCode|LocationName|Description|ActiveStatus|AddressLine1|AddressLine2|TownOrCity|Region1|Region2|PostalCode|Country

MERGE|Location|XXTEST|XXTEST_LOC5|1951/01/01|4712/12/31|COMMON|XXTEST_LOC5|XXTEST Location5|XXTEST Location6|A|250 Some st1||Pasadena|Los Angeles|CA|91101|US

Save file as "Location.dat"

Organization :

Organization has 2 components to load:

1) Organization

2) Org Unit Classification

METADATA|Organization|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|Name

MERGE|Organization|XXTEST|XXTEST_DEPT1|1951/01/01|4712/12/31|XXTEST Department1

METADATA|OrgUnitClassification|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|OrganizationId(SourceSystemId)|ClassificationCode|SetCode|Status

MERGE|OrgUnitClassification|XXTEST|XXTEST_DEPT_CLASS1|1951/01/01|4712/12/31|XXTEST_DEPT1|DEPARTMENT|COMMON|A

Save file as  "Organization.dat"

Position :

METADATA|Position|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PositionCode|Name|BusinessUnitName|ActiveStatus|JobId(SourceSystemId)|DepartmentId(SourceSystemId)|HiringStatus|PositionType|FTE|HeadCount|FullPartTime|WorkingHours|Frequency

MERGE|Position|XXTEST|XXTEST_POISITION1|1951/01/01|4712/12/31|XXTEST_POS1|XXTEST Position1|US1 Business Unit|A|XXTEST_JOB1|XXTEST_DEPT1|APPROVED|NONE|1|1|FULL_TIME|40|W

Save as "Position.dat"

Make zip file including all object's HDL File.

Load the zip file in Oracle Fusion

Navigator -> My Client Group -> Data Exchange -> Import and Load -> Import

Import the prepared zip file.

Refresh to see the status of loading.

Once Loaded successfully, you can verify the Work Structure.

You can see step by step video using this link

In Next Blog we will see how to load Worker Data in Oracle Fusion Using HCM Data Loader.

Define Source System Owner In Oracle HCM Fusion

Define Source System Owner 

 

In this blog we will learn how to define Source Keys in oracle fusion.

Source Keys has 2 components SourceSystemOwner, SourceSytemId.

To use this Source Keys while loading the data in Oracle Fusion we need to define the Source System Owner, now we will see how to define Source System Owner.

You can see step by step video using this link.

Navigation:

Navigator -> Setup & Maintenance -> Task Panel Tab -> Click Search

It will popup Search Page.

On this page you can search for “Manage Common Lookups”.

Open the Manage Common Lookups definition page.

In Meaning field enter Source System Owner and search for the same.

In search results you can see Source System Owner Definition of lookup followed by values

In Lookup code values section you can add values.

Below are the details you need to enter:

1)    Lookup Code         – XXTEST

2)    Display Sequence   - 1

3)    Enabled                  - Checked

4)    Start Date               - 01/01/1951

5)    End Date                - 12/31/4712

6)    Meaning                - XXTEST

7)    Description            - XXTEST

     

        Click Save and close.

    In this way you can define Source System Owner.

   Now you can able to load the data using Source Keys in Oracle HCM Fusion (HDL/HCM Loader).

In next blog we will see How to Load WorkStructure in Oracle HCM Fusion.

Oracle Fusion BIP Report Questions with Answers

 Q) Navigation to build the BIP Report in Oracle Fusion.

-> 

Navigation to Build the BIP Report in Oracle Fusion.

Data Model Navigation.

Tools -> Report and Analytics -> New -> Data Model

Data Template Navigation.

Tools -> Report and Analytics -> New -> Report.

Q) What is Data model?

->  Data Model is a object which contains set of instructions for BI Publisher to retrieve data and structure data for report. Data Model defines data source, data structure and parameter for the report.

Q) What are the Components of Data Model?

->  Components of Data Model.

1. Data Set

2. Event Triggers

3. Flexfields

4. List of value

5. Parameters

6. Bursting.

Q) What is Data Set?

-> Data Set contains logic to retrieve the data from the single data source. A data set can retrieve the data from variety of data sources like database, existing data file, web service call etc.

Q) Can we use Triggers in BIP Report?

-> Yes, we can use Event Triggers in BIP report. The Data Model supports before data and after data triggers as well as schedule triggers.

Before Data and After Data triggers consists of a call to execute a set of functions defined in PL/SQL package stored in oracle database.

Schedule Trigger is executed for a scheduled reports.

Q) What is split by and deliver by?

-> Split by - Choose how you want to split the data. 

Deliver by - Choose to whom you want to deliver the data.

Q) How to generate multiple language report in BIP ?

-> We can use translation feature to generate the report in multiple language repot. Template can be translated from the properties page from the properties you can generate XLIFF files for the single template. Click extract translation to generate the XLIFF files.

Q) How to schedule the BIP reports?

-> 

1) In report and analytics -> New -> Report Job

2) In Create Section -> Published Reporting -> Report Job

3) In Catalog -> Select Report -> More-> Schedule

4)  You can schedule the report using ESS Job also.

For more Oracle Fusion HDL Videos  please click here

For more Oracle Fusion Extract PDF Output Videos  please click here

HRMS Technical interview Questions

HRMS Technical Interview Questions

Q) What is SIT?

->   

•  SIT: SIT is basically a KFF.This we can use to capture additional person information.

• The KFF which we are using here is Personal Analysis Keyflexfield. We can create and assign N number of SITs to a person.
• Table PER_PERSON_ANALYSES and PER_ANALYSIS_CRITERIA
• The API to create SIT information is HR_SIT_API.
•  Define a structure for the Personal Analysis Keyflexfield. For this you have to navigate to Application Developer > Flexfield > Key > Segments Query for Personal Analysis Flexfield.
• Enable the SIT.
• For this navigate to US Super HRMS Manager > Other Definitions > Special Information Types
• Select the special information types you want to use in your Business Group.
• Enable each type by checking the Enabled check box.
• Check the other boxes to specify how you plan to use the special information type. This makes it available in the list of values in other windows as follows:
• a. Job : in the Job Requirements window
• b. Position : in the Position Requirements window
• c. Other : in the Special Information window
• d. Skill : in the Skill Provisions and Search for Event windows in Oracle Training Administration
• The OSHA and ADA check boxes are for US users only.

 Q) What is EIT?

-> 

• EITs are basically Descriptive Flexfield (DFF).Standard product has EIT DFFs defined in forms such as Person, Assignment, and Job etc. We can add segments to the defined DFF based on our requirements so that it will appear in corresponding screen. Compared to SIT EIT has added security. We can enable EIT for specific responsibility.
• The DFF which we are using here is Extra Person Information under Human Resources application.
• Register the Extra Information Types.
  Navigate to Super HRMS Manager Responsibility. Select Register Extra Information Types (EITs) standard concurrent program from the SRS window. Give appropriate values and submit.
  Concurrent Program Name:- Register Extra Information Types (EITs) Parameters Table Name :- PER_PEOPLE_INFO_TYPES [Select it from the drop down available.
  Since it is a people EIT hence, we have to select this value]
  Information Type :- <Name of our EIT> [identify the EIT from the LOV]
  Multiple Rows :- <Value can be Yes/No> Yes: - if we select Yes, it means we are allowing users to enter more
  than one record for this EIT definition No:- If we select No, it means users are allowed to enter only one
  record for this EIT definition. Legislation Code*:- <Select the proper legislation for the EIT information type>. Here we are selecting "United States".
• API Name : HR_PERSON_EXTRA_INFO_API
   Table Name : PER_PEOPLE_EXTRA_INFO
• Assign the EIT to responsibility.
  Navigate to HRMS responsibility -> Security > Information Types Security. 

Q) What are the standard KFF and tables used for the same?
->
 Table : 
              1. FND_ID_FLEXS
              2. FND_ID_FLEX_SEGMENTS
              3. FND_ID_FLEX_SEGMENTS_TL
              4. FND_ID_FLEX_STRUCTURES
              5. FND_ID_FLEX_STRUCTURES_TL
1. Grade :

• Flex Field Name: Grade Flexfield

• Flex Code : GRD

• Table Name : PER_GRADE_DEFINITIONS

2. Job :

• Flex Field Name: Job Flexfield
• Flex Code : JOB
• Table Name : PER_JOB_DEFINITIONS

3. Position :

• Flex Field Name: Position Flexfield
• Flex Code : POS
• Table Name : PER_POSITION_DEFINITIONS

4. Costing :

• Flex Field Name: Cost Allocation Flexfield
• Flex Code : COST
• Table Name : PAY_COST_ALLOCATION_KEYFLEX

5. Competence FlexFields. 

6. People Group FlexFields.

Q) Tables used for DFF.
-> 
Tables
1. FND_DESCRIPTIVE_FLEXS
2. FND_DESCRIPTIVE_FLEXS_TL
3. FND_DESCR_FLEX_COLUMN_USAGES
4. FND_DESCR_FLEX_COL_USAGE_TL
5. FND_DESCR_FLEX_CONTEXTS
6. FND_DESCR_FLEX_CONTEXTS_TL

 Q) Tables used for Fast Formula.
->

• FF_FUNCTIONS 
• FF_FUNCTION_PARAMETERS
• FF_FORMULAS_F 
• FF_FORMULA_TYPES 
• FF_DATABASE_ITEMS 
• FF_GLOBALS_F 

 Q) Can we run Quick Pay for multiple Employee?
-> No, We can run Quick Pay for single Employee because we need to run it from employee's Assignment Screen.

Q) How to debug Fast Formula?
->
1) Message in output format

e.g.
mess = 'person is' +first_name

return mess

2) Logging from fast formula execution engine

e.g.
ff_utils.set_debug (ff_utils.ROUTING + ff_exec.FF_CACHE_DEBUG);

Q) Can we use Database function in Fast Formula? If yes then how?
-> Yes, we need to register the Database Function using below navigation.
Total Compensation -> Basic -> Fast Formula -> Formula Function

Q) Tables affected by Hiring an employee?
->
Per_all_people_f
Per_all_assignments_f
Per_periods_of_service
Hz_parties

Q) What is the difference between Rehire and Reverse Termination?
-> Rehire - Select Ex-Employee after interview for respective Position. It will create new service record(Person_id will change).
    Reverse Termination - It is the process of retain the employee. Service period will be the same.