Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Fusion Blog
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

Business Requirement

Well before we start this post one question which would arise in the mind of many consultants could be:

Why should we go via the Extract Route if we can fetch the details using BI SQL.

Well there are some advantages like:

  1. You can handle large volume of data
  2. You do not need to build Custom / Complex Bursting Query to deliver the data-file via different channels to different recipients (HCM Extract does this for you behind the scene)
  3. We can get the data in HDL Template so if required can be used for HDL Load (this can be done via BI SQL too but then you might need to have good knowledge on SQL)
  4. You may make use of “Incremental” (Changes Only) feature.

 

We will build this custom extract and some of the unique features this custom extract would demonstrate are:

  1. Get Data in HDL Template format for “PersonAbsenceEntry” Business Object
  2. Extract will have input parameters like Absence Type, Person Number and Cut-Off Date so one can run the extract for only a specific absence type, for a unique person number and the cut-off date would be used to fetch only those absence entries where the cut-off date falls between the absence start date and absence end date (Significance / Importance of providing Cut-Off Date input parameter is discussed in detail in subsequent paragraph)
  3. User will have a Drop Down List available for “Absence Type” input so that they can choose which particular type of absence records they would like to extract. It is not a mandatory parameter though and if left Blank it will fetch values for all absence type
  4. Person Number parameter allows you to provide a specific person number value and all absence records for that specific person will be fetched. This again is also not a mandatory parameter and if left Blank will return data for all employees. This particular parameter is a Textbox type parameter meaning you would need to type the exact value of the person number (any errors in providing input won’t return any data)

Importance / Significance of Cut-Off Date User Input Parameter

Imagine a situation where you would like to update absence reason or comments for all absence entries where the absence entry spans across a particular date. In this case you can use this extract download the data add absence reason and re-load the file back.

Worked-Out Example

We would create a custom extract Titled “Absence Entry Details Extract” of Inbound Interface type and some of the salient features of the extract are:

Data Group Name: Person Data Group

Attribute Name

Attribute Value

Data Group Name

Person Data Group

*Tag Name

Person_Data_Group

User Entity

PER_EXT_PAY_EMPLOYEES_UE

Threading Database Item

Extract Employee Assignment ID

Threading Action Type

Object Actions

Root Data Group

Checked

 

Data Group Filter Criteria for  Person Data Group

paaf.assignment_type = 'E' 

and paaf.primary_assignment_flag = 'Y' 

and papf.person_number = nvl(pay_report_utils.get_parameter_value('PERSON_NUMBER'),papf.person_number)

 

Extract Record for Person Data Group

Extract Record Name : Person Record

Attribute Name

Attribute Value

Sequence

10

Name

Person Record

Tag Name

Person_Record

Type

Detail Record

Process Type

Fast Formula

 

Extract Record for Call Absence Data Group

Extract Record Name : Call Absence Data Group

Attribute Name

Attribute Value

Sequence

20

Name

Call Absence Data Group

Tag Name

Call_Absence_Data_Group

Type

Hierarchy traversal only

Process Type

 

Next Data Group

Absence Data Group

 

Absence Data Group (Config Details)

Data Group Name: Absence Data Group

Attribute Name

Attribute Value

Data Group Name

Absence Data Group

*Tag Name

Absence_Data_Group

User Entity

ABS_EXT_ABSENCE_ENTRY_UE



Connect Data Groups: Absence Data Group

Attribute Name

Attribute Value

Parent Data Group

Person Data Group

Parent Data Group Database Item

Extract Employee Person ID

Database Item

Absence – Person Id

 

Data Group Filter Criteria for  Absence Data Group

(

atyl.name=nvl(pay_report_utils.get_parameter_value('ABSENCE_TYPE'),atyl.name)

AND abs.start_date<pay_report_utils.get_parameter_value_date('CUT_OFF_DATE')

AND abs.end_date>=pay_report_utils.get_parameter_value_date('CUT_OFF_DATE')

AND abs.absence_status_cd || '-' || abs.absence_status_cd NOT IN ('ORA_WITHDRAWN-APPROVED','SAVED-','SUBMITTED-DENIED')

)

 

Extract Record for Absence Data Group

Extract Record Name : Absence Record

Attribute Name

Attribute Value

Sequence

10

Name

Absence Record

Tag Name

Absence_Record

Type

Detail Record

Process Type

Fast Formula





Extract Attributes for Absence Record

Name

Tag Name

Data Source

Person Number

Person_Number

Extract Employee Person Number

Employer

Employer

Assignment Legal Employer Name

Absence Type

Absence_Type

Absence - Type

Absence Type Reason

Absence_Type_Reason

Absence - Type Reason

Absence Status

Absence_Status

Absence - Status Code

Approval Status

Approval_Status

Absence - Approval Status Code

PerAbsenceEntryId

PerAbsenceEntryId

Absence - Person Entry Id

Absence Start Date

Absence_Start_Date

Absence - Start Date

Absence Start Time

Absence_Start_Time

Absence – Start Time

Absence End Date

Absence_End_Date

Absence - End Date

Absence End Time

Absence_End_Time

Absence – End Time

Comments

Comments

Absence - Comments

 

Extract Delivery Options (Configuration)

Delivery Option Name: CSV

Attribute Name

Attribute Value

*Delivery Option Name

CSV

*Output Type

CSV

Report

 

Template Name

 

*Output Name

AEDECSV

*Delivery Type

WebCenter Content

Required

Checked

*We need to populate additional details too for this delivery type

Additonal Details: CSV

Attribute Name

Attribute Value

Delimiter

|

Encryption Mode

None

Integration Name

AEDECSV

Print Header Record

No

Print Default Attributes

No

 

Delivery Option Name: DATA

Attribute Name

Attribute Value

*Delivery Option Name

DATA

*Output Type

Data

Report

 

Template Name

 

*Output Name

AEDEDATA

*Delivery Type

WebCenter Content

Required

Checked

*We need to populate additional details too for this delivery type

Additonal Details: DATA

Attribute Name

Attribute Value

Encryption Mode

None

Integration Name

AEDEDATA

 

Delivery Option Name: ETEXT

Attribute Name

Attribute Value

*Delivery Option Name

ETEXT

*Output Type

Text

Report

/Custom/Practice Samples/AbsenceEntryDetailsReport.xdo

Template Name

AbsenceEntryDetailsTemplate

*Output Name

AEDETEXT

*Delivery Type

WebCenter Content

Required

Checked

*We need to populate additional details too for this delivery type

Additonal Details: ETEXT

Attribute Name

Attribute Value

Encryption Mode

None

Integration Name

AEDEETEXT



Delivery Option Name: HDL

Attribute Name

Attribute Value

*Delivery Option Name

HDL

*Output Type

Text

Report

/Custom/Practice Samples/AbsenceEntryDetailsReport.xdo

Template Name

AbsenceEntryDetailsHDLTemplate

*Output Name

PersonAbsenceEntry

*Delivery Type

WebCenter Content

Required

Checked

 

*We need to populate additional details too for this delivery type

Additonal Details: HDL

Attribute Name

Attribute Value

Encryption Mode

None

Override File Extension

.dat

Run Time File Name

RunTimeFileNamePersonAbsenceEntry

Integration Name

AbsenceEntryDetailsHDLTemplate

Integration Type

PersonAbsenceEntry

*Delivery Type

Data Loader

Compressed Delivery Group

AEDE.zip

 

Input Parameters (Configuration)

Input Parameter Name: Person Number

Attribute Name

Attribute Value

*Flow Parameter

Person Number

BaseFlowParameterName

PERSON_NUMBER

*Display Format

Text

 

Input Parameter Name: Absence Type

Attribute Name

Attribute Value

*Flow Parameter

Absence Type

BaseFlowParameterName

ABSENCE_TYPE

*Display Format

Value Set

Lookup

GET_ABSENCE_TYPE_VS

Parameter Basis

 

Basis Value

 

 

Input Parameter Name: Cut Off Date

Attribute Name

Attribute Value

*Flow Parameter

Cut Off Date

BaseFlowParameterName

CUT_OFF_DATE

*Display Format

Date

Lookup

 

Parameter Basis

SQL Bind

Basis Value

SELECT to_date(‘04/01/2020’, ‘MM/DD/YYYY’) FROM DUAL

 

Value Set (Configuration)

Navigation->Setup and Maintenance-> Search Task (Manage Global Absences Value Set)-> Create

Value Set Name: GET_ABSENCE_TYPE_VS

Attribute Name

Attribute Value

Value Set Code

GET_ABSNECE_TYPE_VS

Description

Custom Value Set Created To Fetch All Absence Types

*Module

Absence Management

Validation Type

Table

Value Data Type

Character

*From Clause

anc_absence_types_f_tl absence_type

Value Column Name

absence_type.name

WHERE Clause

absence_type.language = 'US'

and    trunc(sysdate) between absence_type.effective_start_date and absence_type.effective_end_date

and    absence_type.absence_type_id 

IN

(

select absence_entry.absence_type_id

from   anc_per_abs_entries absence_entry,

       per_all_people_f papf

where  absence_entry.person_id = papf.person_id

and    trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

)

ORDER BY Clause

absence_type.name asc

 

Configuration To Invoke “Initiate HCM Data Loader”

Navigation: My Client Groups->Payroll-> Administration (Payroll Flow Patterns)-> Search for Absence Entry Details Extract

Edit and Add Initiate HCM Data Loader Flow Task

Task Name: Data Loader Archive Option

Attribute Name

Attribute Value

Name

Data Loader Archive Option

Execution Mode

Submit

Data Type

Text

Parameter Basis

Bind To Flow Task

Basis Value

Absence Entry Details Extract, Submit, Payroll Process

Usage

Input Parameter

 

Task Name: Data Loader Configuration

Attribute Name

Attribute Value

Name

Data Loader Configuration

Execution Mode

Submit

Data Type

Text

Parameter Basis

Constant Bind

Basis Value

ImportMaximumErrors=100,

LoadMaximumErrors=100

,LoadConcurrentThreads=8,

LoadGroupSize=100

Usage

Input Parameter

 

And with this all the configuration is complete.

We will now run the extract and verify results

Verification

We will now try to submit the extract.

Navigation->My Client Groups->Data Exchange->Submit Extracts->Absence Entry Details Extract

A screenshot of a cell phone

Description automatically generated

 

We can also find the extract results from My Client Groups->Payroll->View Flows-> Search for AEDE:FirstRun

A screenshot of a cell phone

Description automatically generated

 

We can download the output file and we will see that some records have been downloaded (in HDL Template)

A screenshot of a cell phone

Description automatically generated

 

Also if we navigate to My Client Groups-> Data Exchange->Import and Load Data

A screenshot of a social media post

Description automatically generated

 

Now if we check one absence record which got updated as part of this activity we could see that “Last Updated By” would show the user name of HDL User

A screenshot of a social media post

Description automatically generated

 

Also if we open the Absence transaction we expect to see the comments section updated with “Adding Comments for Demo Purpose” which is the static value we had in our ETEXT Template (used for HDL Load)

A screenshot of a social media post

Description automatically generated

 

Summary

So this is how we can update details of absence transactions using an Inbound Interface HCM Extract. 

I have tried to explain the entire process in a single snapshot, hope this would make things clear.

A screenshot of a cell phone

Description automatically generated

 

We can use the same concept to update other transactions too.

Hope this was useful. Thanks and have a great day ahead.

 

You can also download the files using the below links:

 

Absence Entry Details Extract.XML

AbsenceEntryDetailsExtract_dm.xdm.catalog

AbsenceEntryDetailsHDLTemplate.rtf

AbsenceEntryDetailsReport.xdo.catalog

AbsenceEntryDetailsTemplate.rtf

GET_ABSENCE_TYPE_VS.sql


Ashish Harbhajanka

Add comment


Security code
Refresh

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner