Introduction
Many a times there is a requirement that if a specific extract attribute does not hold any data, we would like to replace the value with say “No Data Found”. In this example, we would try and demonstrate how to accomplish this.
We would create a simple HCM Extract titled “Person Details” which will comprise of the following extract attributes:
- Person Number
- Enterprise Hire Date
- Assignment Number
- Assignment Action Code
- Person Display Name
- Person Age
- Person Blood Type
- Assignment Department Name
- Assignment Business Unit Name
- Assignment Location Code
- Assignment Status
- Assignment Position Name
- Assignment Effective Start Date
- Assignment Effective End Date
- Employee Category
- Employment Category
Out of the above 16 attributes we would apply Conditional Replacements for Employee Category and Employment Category field
Col
|
Name
|
Type
|
Data Source
|
1
|
PersonNumber
|
Database Item Group
|
Extract Employee Person Number
|
2
|
EnterpriseHireDate
|
Database Item Group
|
Extract Employee Relationship Original Date Of Hire
|
3
|
AssignmentNumber
|
Database Item Group
|
Extract Employee Assignment Number
|
4
|
AssignmentActionCode
|
Database Item Group
|
Assignment Action Code
|
5
|
PersonDisplayName
|
Database Item Group
|
Person Display Name
|
6
|
PersonAge
|
Database Item Group
|
Person Age
|
7
|
PersonBloodType
|
Database Item Group
|
Person Blood Type
|
8
|
AssignmentDepartmentName
|
Database Item Group
|
Assignment Department Name
|
9
|
AssignmentBusinessUnitName
|
Database Item Group
|
Assignment Business Unit Name
|
10
|
AssignmentLocationCode
|
Database Item Group
|
Assignment Location Code
|
11
|
AssignmentStatus
|
Database Item Group
|
Assignment Status Type - Lookup Meaning
|
12
|
AssignmentPositionName
|
Database Item Group
|
Assignment Position Name
|
13
|
AsgEffStartDate
|
Database Item Group
|
Extract Employee Assignment Effective Start Date
|
14
|
AsgEffEndDate
|
Database Item Group
|
Extract Employee Assignment Effective End Date
|
15
|
EmployeeCategory
|
Database Item Group
|
Assignment Employee Category - Lookup Meaning
|
16
|
EmploymentCategory
|
Database Item Group
|
Assignment Employment Category - Lookup Meaning
|
Conditional Replacement
Attribute Name
|
Replacement Condition
|
Replacement Value
|
EmployeeCategory
|
EmployeeCategory Is Null
|
No Data Found
|
EmploymentCategory
|
EmploymentCategory Is Null
|
No Data Found
|
Data Group Details
We would define a single data group , details for the same are in table below:
Name
|
*Tag Name
|
User Entity
|
Threading Database Item
|
Threading Action Type
|
Root Data Group
|
PersonDetailsDataGroup
|
PersonDetailsDataGroup
|
PER_EXT_PAY_EMPLOYEES_UE
|
Extract Employee Person ID
|
Object Actions
|
Checked
|
Data Group Filter Criteria
We would only fetch the primary assignment record and also try to fetch 10 records only.
Filter Criteria Condition
|
((((paaf.assignment_type='E')
AND (paaf.primary_assignment_flag='Y'))
AND (paaf.primary_flag='Y'))
AND (paaf.primary_work_relation_flag='Y')
AND rownum < 11)
|
Extract Delivery Options
We will define a Extract Delivery Option of CSV type with the following details
Attribute Name
|
Attribute Value
|
*Delivery Option Name
|
PersonDetailsCSV
|
*Output Type
|
CSV
|
*Output Name
|
PersonDetailsCSV
|
*Delivery Type
|
WebCenter Content
|
Required
|
Checked
|
Delimiter
|
|
|
Encryption Mode
|
None
|
Run Time File Name
|
PersonDetails
|
Integration Name
|
PDCSV
|
Print Header Record
|
No
|
Print Default Attributes
|
No
|
Compress
|
No
|
Once the setup is done the Extract definition would appear as below:
Running Extract
Now the extract definition is complete so we should submit the extract and verify result.
Navigation-> My Client Groups->Data Exchange->Submit Extracts -> Person Details (Submit)
We can check results from below navigation
Navigation-> My Client Groups->Data Exchange->View Extract Results -> Search (T001)
And when we have a look at PersonDetailsCSV file we could see that there is “No Data Found” data against Employee Category for many employee records.
You can download the files from below:
Person Details.xml
PersonDetails.csv
About the Author
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
........