How to sort data in rtf template

 How to sort data in rtf template

Sort the data in Data model is the recommended approach.

But if you want to sort the data in rtf template then you have to use below xml tag code.

<?sort:fieldname?><?fieldname?>  

this will sort the data in ascending order be default as it is not mentioned.

if user want to sort the data in descending order then use below code

<?sort:fieldname;'descending'?><?fieldname?>  

HDL Template for Global Temporary Transfer of Worker

For this use Worker.dat

Please find the below sample template :

METADATA|WorkRelationship|PersonNumber|LegalEmployerName|DateStart|ActualTerminationDate|PrimaryFlag|WorkerNumber|WorkerType|RehireRecommendationFlag|OnMilitaryServiceFlag|ActionCode|ReasonCode|DateOfDeath

MERGE|WorkRelationship|XXTEST9|US1 Legal Entity|2018/01/01||Y|XXTEST9|E|Y|N|GLB_TEMP_ASG||

MERGE|WorkRelationship|XXTEST9|UK Legal Entity|2019/01/01||N|XXTEST9|E|Y|N|GLB_TEMP_ASG||

METADATA|WorkTerms|ActionCode|PersonNumber|LegalEmployerName|DateStart|WorkerType|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|AssignmentType|PersonTypeCode|AssignmentStatusTypeCode|BusinessUnitShortCode|PrimaryWorkTermsFlag|SystemPersonType|ReasonCode

MERGE|WorkTerms|GLB_TEMP_ASG|XXTEST9|US1 Legal Entity|2018/01/01|E|XXTEST_WRKTERM9|2019/01/01|4712/12/31|Y|1|ET|Employee|SUSPEND_PROCESS|US1 Business Unit|Y|EMP|

MERGE|WorkTerms|GLB_TEMP_ASG|XXTEST9|UK Legal Entity|2019/01/01|E|XXTEST_WRKTERM9-2|2019/01/01|4712/12/31|Y|1|ET|Employee|ACTIVE_PROCESS|UK Business Unit|Y|EMP|

METADATA|Assignment|ActionCode|PersonNumber|LegalEmployerName|DateStart|WorkerType|AssignmentNumber|WorkTermsNumber|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|AssignmentType|PersonTypeCode|AssignmentStatusTypeCode|BusinessUnitShortCode|GradeCode|JobCode|LocationCode|DepartmentName|ManagerFlag|LabourUnionMemberFlag|ReasonCode

MERGE|Assignment|GLB_TEMP_ASG|XXTEST9|US1 Legal Entity|2018/01/01|E|XXTEST_ASSIGN9|XXTEST_WRKTERM9|2019/01/01|4712/12/31|Y|1|E|Employee|SUSPEND_PROCESS|US1 Business Unit|XXTEST_GRADE1|XXTEST_CFO|XXTEST_LOC12|XXTEST Department1|Y|N|

MERGE|Assignment|GLB_TEMP_ASG|XXTEST9|UK Legal Entity|2019/01/01|E|XXTEST_ASSIGN9-2|XXTEST_WRKTERM9-2|2019/01/01|4712/12/31|Y|1|E|Employee|ACTIVE_PROCESS|UK Business Unit|XXTEST_GRADE1|ADM|XXTEST_LOC12|XXTEST Department1|Y|N|

 

SQL Query to Fetch Global Transfer records in Fusion HCM

SQL Query to Fetch Global Transfer records in Fusion HCM 

select

papf.person_number, ppnf.full_name,

to_char(ppos.date_start,'mm/dd/yyyy') Hire_date

,(select hou.name from hr_organization_units hou where organization_id = ppos.LEGAL_ENTITY_ID)  Current_LE

from per_All_assignments_m paam

,per_all_people_F papf

,per_person_names_f ppnf

,PER_PERIODS_OF_SERVICE ppos

where paam.person_id = papf.person_id

and papf.person_id = ppnf.person_id

and ppos.period_of_service_id = paam.period_of_service_id

and ppnf.name_type = 'GLOBAL'

and sysdate between papf.effective_start_date and papf.effective_end_date

and sysdate between ppnf.effective_start_date and ppnf.effective_end_date

and  sysdate  between paam.effective_start_date and paam.effective_end_date

and paam.person_id = papf.person_id

and paam.assignment_status_type = 'ACTIVE' 

and paam.assignment_type = 'E'

and paam.action_code = 'GLB_TRANSFER'

Note :- Do let me know in case of any issue in query.

SQL Query to Fetch Inactive Employees in Fusion HCM

SQL Query to Fetch Inactive Employees in Fusion HCM 

select 

paam.*

from

 per_all_assignments_m paam ,

per_all_people_f  papf 

where 1=1

and papf.person_id = paam.person_id

and paam.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'

and paam.effective_latest_change = 'Y'

and paam.primary_flag = 'Y'

and paam.assignment_type= 'E'

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

and exists ( select '1' from per_all_assignments_m paam1 where paam1 .assignment_id = paam.assignment_id

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

and t1.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'

)

Top 50 Fusion HCM Technical Questions - Part 2

 Top 50 Oracle Fusion HCM Technical Questions - Part 2

Top 50 Oracle Fusion HCM Technical Questions - Part 1 Click 

Q) Mandatory Records to Load Worker File for NEW Hire?

->  Worker , Person Name , Person Legislative Data, Assignment, Work Term.

Q) Sequence of WorkStructure Business Object While Load?

-> Work Structure Business Object loads in below sequence 

     1. Location

     2. Grade

     3. Job

     4. Orgnization

     5. Position

Q) If any Location is end dated and same location we are providing in Worker File then what will happen?

-> HDL will fail to load the data. It will give error as "You must enter a valid value for the LocationId field." 

Q) Can we delete Location using HDL?

-> We can not delete Location. You can end date the that Location and make inactive.

Q) What will happen in below Scenario?

        Scenario - If we have future date changes for any worker and we tried to load the current any changes for the same worker using HDL.

-> If we tried to load the current changes then it will purge the future changes because by default 

     PURGE_FUTURE_CHANGES value set as Y.

     If you want to keep future dated changes then set PURGE_FUTURE_CHANGES N so that it will         keep future dated changes after the data load.

Q) Which Role is required to process the file using HCM Data Loader?

->  You required the Human Capital Management Integration Specialist Role (ORA_HRC_HUMAN_CAPITAL_MANAGEMENT_INTEGRATION_SPECIALIST_JOB)

Q) How can you change the delimiter in HDL File at run time?

->  You can set delimiter in HDL File using SET FILE_DELIMITER command.

Q) "The party identified by the specified party ID does not exist" Error while loading Personal Payment Method using HDL?

-> Reason - > Party Id is not created for those Worker.

 Solution - Run Below scheduled process 

                1 - Maintain Party and Location Current Record information

                2 - Synchronize Person Records 

Q) How to create personal Payment Method incase joint account scenario?

-> Create two different personal payment method for each employee and both can be tagged to same account.

In this case primary account holder name will be displayed in UI for both methods. 

Q) Talent Profile data loaded successfully using HDL, But not able to see at front end.

->  Loaded Profile information is not able to see at front end because of two reasons

            1. Check Section Id/Section Name is correctly used

             2. Check Content Type Id/Content Type is used correctly.

Q) Load Non Workers with person type Pensioner using HDL?

-> You can use Person Type Code and System Person Type with Pensioner in WorTerm and assignment Metadata line. 

Q) Supervisor Load is failed what could be the reason and solution for the same?

-> Cause - Manager's hire date is earlier than assignments effective start date

      Solution -  Add Work Term and Assignment record which is later than Manger's hire date   along with supervisor record.

Q) Roles Required to execute the HDL Load (HCM Data loader)?

->  Human Capital Management Integration Specialist. 

          

Note - For any doubts or additional information, please reach out in the comment section.

 

 Sample Templates for eText BI Publisher Reports (Delimiter & Fixed Position)

Introduction to eText BI Publisher Reports

Oracle BI Publisher (BIP) provides eText report templates to generate text-based output files such as .txt, .dat, or interface files required for integrations with external systems.

Unlike PDF or Excel outputs, eText reports are mainly used when:

• Data must follow a specific layout

• Files are consumed by external payroll, banking, or legacy systems

• Strict formatting rules (delimiter or fixed length) are required

In this blog, we’ll cover:

• What eText BI Publisher reports are

• how to create eText report in BI Publisher

• Differences between Delimiter-Based and Fixed Position-Based eText

• BI Publisher eText report example

• how to create fixed position eText report

• common errors in BI Publisher eText report

---

Difference Between Delimiter & Fixed Position eText

Delimiter VS Fixed Position eText

You can see step by step video to develop eText BIP Report using this Link

---

🔹 Sample 1: Delimiter-Based eText Template

This template generates output with values separated by a delimiter (for example, |).

You can see step by step video to develop eText BIP Report using this Link

<TEMPLATE TYPE>	DELIMITER_BASED
<OUTPUT CHARACTER SET>	iso-8859-1
<CASE CONVERSION>	UPPER
<NEW RECORD CHARACTER>	Carriage Return

 

<LEVEL>	DATA_DS
<MAXIMUMLENGTH>	<FORMAT>	<DATA>	<COMMENTS>
<NEW RECORD>	TableHeader
20	Alpha	'Person Id'
1	Alpha	'|'
20	Alpha	'Title'
1	Alpha	'|'
20	Alpha	'First Name'
1	Alpha	'|'
20	Alpha	'Last Name'

 

<LEVEL>	G_1
<MAXIMUMLENGTH>	<FORMAT>	<DATA>	<COMMENTS>
<NEW RECORD>	CLRDAta
20	Alpha	PERSON_ID
1	Alpha	'|'
20	Alpha	TITLE
1	Alpha	'|'
20	Alpha	FIRST_NAME
1		'|'
20	Alpha	LAST_NAME
<END LEVEL>	G_1

 

<END LEVEL>

DATA_DS

 This format is useful when the target system expects delimiter-separated text files.

⭐ HDL Template for Personal Payment Method in Oracle Fusion HCM (PersonalPaymentMethod.dat + Direct Deposit Example)

 HDL Template for Personal Payment Method in Oracle Fusion HCM

PersonalPaymentMethod.dat Step-by-Step Guide (Direct Deposit Example)

Personal Payment Method is used in Oracle Fusion HCM Payroll to define how an employee receives salary payments, such as:

• Direct Deposit (Bank Transfer)

• Check

• Cash (based on country setup)

Instead of manually entering payment methods for each employee, you can load them in bulk using HCM Data Loader (HDL).

This blog explains how to load Personal Payment Method details using:

📌 PersonalPaymentMethod.dat

---

✅ Prerequisites (Mandatory)

Before loading Personal Payment Method details, ensure the following are already loaded:

1) Bank and Bank Branch

You must load:

• Bank.dat

• BankBranch.dat

2) External Bank Account

You must load:

• ExternalBankAccount.dat

⚠️ If these are missing, the PersonalPaymentMethod HDL will fail.

Links : 

1. Bank and Bank Branches data should be loaded. Click here to find the sample bank details.  

2. External bank Account should be loaded. Click here to find the sample file.  

Video Link - You can see step by step video to load Personal Payment Method Details using this Link

---

✅ HDL File Used

For Personal Payment Method, use:

📌 PersonalPaymentMethod.dat

---

✅ Business Object Used

• PersonalPaymentMethod

This object creates the payment method record for the assignment.

---

📌 Sample HDL Template (Personal Payment Method)

1) PersonalPaymentMethod HDL Example (Direct Deposit)

METADATA|PersonalPaymentMethod|SourceSystemOwner|SourceSystemId|LegislativeDataGroupName|AssignmentNumber|PersonalPaymentMethodCode|EffectiveStartDate|PaymentAmountType|Amount|Percentage|ProcessingOrder|OrganizationPaymentMethodCode|BankName|BankBranchNumber|BankCountryCode|BankAccountNumber|BankAccountType

MERGE|PersonalPaymentMethod|XXTEST|XXTEST_PAYMETHOD2|US Legislative Data Group|XXTEST_ASSIGN2|XXTEST_PAYMETHOD2|2021/04/19|P||100|1|Direct Deposit|American Bank|000000123|US|3456|Checking

⭐ HDL Template for External Bank Account Details in Oracle Fusion HCM (ExternalBankAccount.dat + Example)

⭐ HDL Template for External Bank Account Details in Oracle Fusion HCM

ExternalBankAccount.dat + ExternalBankAccountOwner.dat Step-by-Step Guide

In Oracle Fusion HCM, employee bank account details are required for payroll processing and payments.
Instead of entering bank accounts manually in the UI, you can load them in bulk using HCM Data Loader (HDL).

This blog explains how to load:

✅ External Bank Account (Account details)
✅ External Bank Account Owner (Employee mapping)

using ExternalBankAccount.dat.

For this use ExternalBankAccount.dat file.

You can see step by step video to load External Bank Account Details using this Link

---

✅ Prerequisite (Mandatory)

Before loading external bank accounts:

1) Bank and Bank Branch must already exist in Fusion

You must load:

• Bank.dat

• BankBranch.dat

📌 If Bank / Branch does not exist, the ExternalBankAccount load will fail.

---

✅ HDL File Used

For this requirement use:

📌 ExternalBankAccount.dat

---

✅ Business Objects Used

HDL requires these objects:

1) ExternalBankAccount

Stores bank account master information.

2) ExternalBankAccountOwner

Links the bank account to the employee/person.

Click here to find the sample bank details.   

---

📌 Sample HDL Template (External Bank Account)

1) ExternalBankAccount (Bank Account Master)

METADATA|ExternalBankAccount|SourceSystemOwner|SourceSystemId|AccountNumber|BankBranchName|BankName|CountryCode|CurrencyCode|AccountType
MERGE|ExternalBankAccount|XXTEST|XXTEST_BANKACCT2|123456|AMC123|American Bank|US|USD|CheckingImportant Notes

• AccountNumber must be valid format as per business rules

• BankBranchName + BankName must match the existing bank setup

• CountryCode / CurrencyCode should match bank country

---

2) ExternalBankAccountOwner (Link to Employee)

METADATA|ExternalBankAccountOwner|SourceSystemOwner|SourceSystemId|PersonNumber|AccountNumber|BankBranchNumber|BankNumber|CountryCode|CurrencyCode|BankBranchName|BankName|ExternalBankAccountId(SourceSystemId)
MERGE|ExternalBankAccountOwner|XXTEST|XXTEST_ACCTOWNER2|XXTEST2|123456|123|Test123|US|USD|AMC123|American Bank|XXTEST_BANKACCT2Key Fields Explained

• PersonNumber → Employee person number

• ExternalBankAccountId(SourceSystemId) → must match the ExternalBankAccount SourceSystemId

• BankNumber + BankBranchNumber → must match what you loaded in Bank.dat and BankBranch.dat

---

✅ Step-by-Step Process to Load External Bank Account Using HDL

Step 1: Prepare the ExternalBankAccount.dat file

Include both sections:

• ExternalBankAccount

• ExternalBankAccountOwner

---

Step 2: Save and ZIP the file

Save as:

✅ ExternalBankAccount.dat

Compress into:

✅ ExternalBankAccount.zip

---

Step 3: Upload via HDL

Go to:

My Client Groups → Data Exchange → HCM Data Loader → Import and Load

Upload and submit the ZIP file.

---

Step 4: Verify the results

After successful load, validate from UI:

Navigation (Common)

My Client Groups → Person Management → Search Person → Payment Methods / Bank Accounts

(Exact path depends on your security role.)

---

⭐ Best Practices (Recommended)

• Always load Bank + Bank Branch first

• Use meaningful SourceSystemId format like:

  • EXTBANK_E12345_01

  • EXTBANKOWNER_E12345_01

• Ensure BankName and BankBranchName match exactly

• Keep one bank account per row (avoid duplicates)

• Maintain separate IDs for each person and account mapping

• Use consistent CountryCode + CurrencyCode

---

⚠️ Common Errors and Fixes

❌ Error: “Bank branch not found”

✅ Fix:
Load BankBranch.dat first and ensure BankBranchName matches.

---

❌ Error: “Invalid PersonNumber”

✅ Fix:
Confirm the worker exists and PersonNumber is correct.

---

❌ Error: “ExternalBankAccountId not found”

✅ Fix:
Make sure:

ExternalBankAccountId(SourceSystemId) = SourceSystemId of ExternalBankAccount row.

---

❌ Error: “Duplicate bank account”

✅ Fix:
Use unique SourceSystemId for each account record.

---

🔗 Internal Links Section

Add this section near the end of the blog:

🔗 Related HDL Templates (Must Read)

• HDL Template for Bank and Bank Branch Details (Bank.dat + BankBranch.dat)

• HDL Template for Worker.dat

• HDL Template for Assignment Changes

• HDL Template for Element Entry

• HDL Template for Dependent Enrollment

(Replace these with your actual post links.)

---

❓ FAQ 

1) Which HDL file is used to load external bank accounts in Oracle Fusion HCM?

External bank accounts are loaded using ExternalBankAccount.dat.

---

2) What is ExternalBankAccountOwner used for?

ExternalBankAccountOwner is used to link the bank account to a person (employee) using PersonNumber.

---

3) Can I load external bank account without loading bank and branch?

No. Bank and branch must exist first, otherwise HDL will fail.

---

4) What is the key linking field between account and owner?

The linking happens using:

ExternalBankAccountId(SourceSystemId)

---

5) Can I load multiple accounts for the same employee?

Yes. Create separate ExternalBankAccount and ExternalBankAccountOwner rows with unique SourceSystemId.

🏦 How to Load Bank and Bank Branch Details Using HDL in Oracle Fusion HCM (Bank.dat + BankBranch.dat)

Using Bank.dat and BankBranch.dat (Step-by-Step Guide)

In Oracle Fusion HCM, bank and bank branch records are commonly required for payroll implementations, employee payment methods, and external bank integrations.

Instead of manually creating bank and branch records in the UI, Oracle provides a faster method using HCM Data Loader (HDL).

In this guide, you’ll learn:

• How to load Bank using Bank.dat

• How to load Bank Branch using BankBranch.dat

• Sample HDL templates

• Best practices, common issues, FAQs

You can see step by step video to load Bank and Bank Branch Details using this Link

---

✅ Prerequisites

Before loading bank data using HDL, ensure:

• You have access to HCM Data Loader

• You have required roles to load banking data

• You have correct details like:

  • Bank Name

  • Country Code

  • Bank Number

  • Branch Number

  • Swift Code (optional)

---

📌 Important Note

✔ Always load Bank first, then load Bank Branch.

Because Bank Branch uses BankName as a reference.

---

🏦 Part 1: Load Bank Details Using Bank.dat

Step 1: Create Bank.dat file

Use the below HDL template:

✅ Sample HDL File for Bank

METADATA|Bank|BankName|CountryCode|BankNumber|
SourceSystemOwner|SourceSystemId

MERGE|Bank|American Bank|US|Test123|XXTEST|
XXTEST_BANKTest123

Field Explanation (Quick)

• BankName → Bank name as required in Fusion

• CountryCode → Example: US, IN, AE

• BankNumber → Unique identifier for the bank

• SourceSystemOwner → Your HDL source (example XXTEST)

• SourceSystemId → Unique record ID (must be unique)

---

Step 2: Zip and Upload

1. Save file name as: Bank.dat

2. Compress into zip file:

   • Example: Bank.zip

3. Navigate to:

My Client Groups → Data Exchange → HCM Data Loader → Import and Load

4. Upload ZIP and load

---

🏦 Part 2: Load Bank Branch Details Using BankBranch.dat

Once Bank is created successfully, load branches.

---

Step 1: Create BankBranch.dat file

✅ Sample HDL File for Bank Branch

METADATA|BankBranch|BankName|BankBranchNumber|BankBranchName|CountryCode|
EftSwiftCode|SourceSystemOwner|SourceSystemId
MERGE|BankBranch|American Bank|123|AMC123|US||XXTEST
|XXTEST_BANKBRANCHAM123

Field Explanation (Quick)

• BankName → Must match the bank name already loaded

• BankBranchNumber → Branch identifier

• BankBranchName → Branch name

• CountryCode → Same as bank country

• EftSwiftCode → Optional (Swift/BIC code)

• SourceSystemId → Unique record ID

---

Step 2: Zip and Upload

1. Save file name as: BankBranch.dat

2. Zip it:

   • Example: BankBranch.zip

3. Upload using:

My Client Groups → Data Exchange → HCM Data Loader → Import and Load

---

✅ Best Practices (Highly Recommended)

• Always load Bank first, then BankBranch

• Keep SourceSystemId unique for every record

• Use meaningful SourceSystemId naming like:

  • XX_BANK_US_001

  • XX_BRANCH_US_001

• Maintain a tracker Excel for:

  • Bank Name

  • Bank Number

  • Branch Number

  • Swift Code

• Validate country code is correct (ISO format)

---

⚠️ Common Issues and Fixes

Issue 1: Bank Branch fails with “Bank not found”

✅ Fix:
Bank must exist before branch load. Load Bank.dat first.

Check Bank reference in Bank Branch file is correct.

---

Issue 2: Duplicate record error

✅ Fix:
Change SourceSystemId and ensure it is unique.

---

Issue 3: CountryCode mismatch

✅ Fix:
CountryCode in BankBranch must match the Bank’s country.

---

⭐ FAQ

1. Which HDL files are used to load bank and bank branches?

You should use:

• Bank.dat for bank records

• BankBranch.dat for branch records

---

2. Do we need to load bank before bank branch?

Yes. Bank must exist first because bank branch references the bank name.

---

3. Can we load multiple banks in one HDL file?

Yes. Add multiple MERGE lines:

METADATA|Bank|BankName|CountryCode|BankNumber|SourceSystemOwner|SourceSystemId
MERGE|Bank|American Bank|US|Test123|XXTEST|XXTEST_BANK_001
MERGE|Bank|Global Bank|IN|GB001|XXTEST|XXTEST_BANK_002

---

4. Can we load multiple branches for the same bank?

Yes. Add multiple MERGE lines in BankBranch.dat.

---

5. Is Swift Code mandatory in BankBranch.dat?

No. It is optional, but recommended if required for EFT / international payments.

---

6. Where can we verify banks and branches in Fusion?

You can verify from:

Setup and Maintenance → Manage Banks
(or Bank-related setup tasks depending on your configuration)