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:
- You can handle large volume of data
- 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)
- 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)
- 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:
- Get Data in HDL Template format for “PersonAbsenceEntry” Business Object
- 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)
- 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
- 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
We can also find the extract results from My Client Groups->Payroll->View Flows-> Search for AEDE:FirstRun
We can download the output file and we will see that some records have been downloaded (in HDL Template)
Also if we navigate to My Client Groups-> Data Exchange->Import and Load Data
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
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)
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.
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