DEV Community: Chinnamsetti Satyanarayana Swamy

Bypassing Layout Compiler Errors in Fixed-Width and Delimiter-Based eText Banking Templates

Chinnamsetti Satyanarayana Swamy — Fri, 17 Jul 2026 12:35:38 +0000

Introduction:

In Oracle Fusion HCM and ERP implementations, outbound third-party integrations frequently demand flat-file text formats rather than standard visual layouts like PDF or Excel. The most prominent example is generating Electronic Funds Transfer (EFT) payment files for global financial institutions.

Banks enforce rigid formatting structures—such as specific record lengths, padding characters, and delimiter rules—to process direct deposits safely. Within the Oracle ecosystem, the most efficient way to generate these files is by building BI Publisher eText Templates. This technical guide explains how to design, construct, and validate an upgrade-safe, delimiter-based eText structure directly within Microsoft Word.

eText Layout:

Unlike standard Rich Text Format (RTF) templates that focus on visual design elements, an eText template acts as a data translator. It tells the BI Publisher layout engine exactly how to map raw XML data structures into flat ASCII text configurations.

An enterprise eText template consists of two major operational tables.

The Setup Table (Command Block): Defines global parameters like the output character set, dynamic delimiters (e.g., commas or tabs), and record end characters (e.g., Carriage Return/Line Feed).
The Record Layout Table (Data Block): Defines the exact sequence, maximum length, and alignment padding rules for individual database attributes mapped from the underlying XML payload.

Template Implementation:

Command Setup Block:
To get started, open Microsoft Word and create a single two-column table. This block instructs the layout compiler on how to format the macro structure of the final text document.

Data Record Block:
Next, define how your employee bank records will print. Create a separate five-column table in Word. This table maps directly to your data model group element (for example, /DATA_DS/G_1).

The table requires a specific header structure to pass the engine's translation process.

Key Functions for Advanced Banking Logic:

When dealing with production payment configurations, raw data fields often require transformation rules before hitting the text interface. Here are three critical built-in eText functions to maximize formatting control.

TRUNCATE Logic: Banks will reject lines if an employee's name exceeds field parameters. Setting a maximum length in column two automatically caps string extractions safely.

Number Formatting (9999.99): Financial networks frequently mandate that decimal points be completely removed from numeric values (e.g., $1,250.45 must print as 000000125045). Define an explicit format mask of 99999D99 with a custom decimal setting to handle currency conversions smoothly.

Conditional Displays: Use DISPLAY_CONDITION lines inside command blocks to skip rendering payment rows if bank routing fields are missing, preventing runtime file corruption.

Testing and Validation:

Once your Word document layout is complete, save the file as a Rich Text Format (.rtf) document. Upload it to your target BI Publisher report interface and apply the eText template type attribute.

While performing testing, please do the below additional checks to avoid errors.

Check for Hidden Truncations: Open the generated .txt file in an editor like Notepad++ and turn on hidden characters. Ensure that total record lengths match the bank's schema requirements exactly.
Validate the XML Tag Casing: BI Publisher is strictly case-sensitive. If your data model SQL prints person_number in lowercase, but your eText table declares PERSON_NUMBER, the compiler will output a blank string without throwing an explicit syntax error.

Conclusion:

Mastering custom eText templates allows technical teams to build dependable, secure, and upgrade-safe bank integrations by embedding logic directly within BI Publisher, eliminating the need for external, complex parsing scripts. This approach ensures compliant, precise, and lightweight file formatting while maintaining 100% standard functionality that survives system updates.

A Step-by-Step Guide to Conditional SFTP Bursting in Oracle Cloud BI Publisher

Chinnamsetti Satyanarayana Swamy — Fri, 17 Jul 2026 10:24:44 +0000

Introduction:

Oracle Fusion BI Publisher (BIP) is the primary workhorse for enterprise data extraction and outbound document delivery. While the standard bursting engine handles basic distribution well such as emailing a payslip to a specific worker real world integration requirements are often much more demanding.

A frequent challenge arises when an organization needs a single payroll or financial report to execute, split its data rows dynamically by business unit or country, and securely deliver those separate files into completely different third-party SFTP servers or specific, nested directory paths. Hardcoding static endpoints within delivery channels fails to scale.

This technical guide provides an end-to-end walkthrough on how to construct an advanced SQL data model and a dynamic Bursting Control XML script to route high-volume system files across separate secure servers and target sub-folders automatically.

Business Requirement:

Consider a multinational enterprise utilizing Oracle HCM Cloud across different entities(for eg: US, UK, and India). At the end of a processing cycle, a single third-party benefits interface file must be generated.

However, regulatory compliance and infrastructure policies dictate that:

The US data file must be delivered to an external server named US_BENEFITS_SFTP in the folder /us/production/.
The UK data file must be delivered to a separate server named UK_BENEFITS_SFTP in the folder /uk/compliance/.
The system must perform these calculations and routing actions seamlessly during a single scheduled enterprise process run, without spawning multiple reports or requiring manual file splitting by IT administrators.

Solution:

1. Dynamic Bursting SQL Query:
The secret to dynamic routing lies within the DELIVERY_KEY and the specific parameter mappings inside your data model's bursting query. Instead of typing fixed server names, we use conditional SQL CASE statements or DECODE logic to establish distinct connection strings on a row-by-row basis.

Navigate to your Data Model, click on the Bursting tab, and implement an advanced query structured like shown below:

select 
    legal_entity_id as key,
    'BenefitsTemplate' as template,
    'en-US' as template_locale,
    'TEXT' as output_format,
    'SFTP' as delivery_channel,
  case when le_country = 'US' then 'US_BENEFITS_SFTP' when le_country = 'UK' then 'UK_BENEFITS_SFTP' else 'GLOBAL_BENEFITS_SFTP' end as parameter1,
  case when le_country = 'US' then '/us/production/' when le_country = 'UK' then '/uk/compliance/' else '/global/dropzone/' end as parameter2,
  'BEN_INTERFACE_' || le_country || '_' || to_char(sysdate, 'YYYYMMDD_HH24MISS') || '.txt' as parameter3,
  'sftp_oracle_user' as parameter4
from
(
    select 
        haou.legal_entity_id,
        ft.country as le_country
  from 
        per_all_assignments_m paam
    inner join hr_all_organization_units haou on paam.legal_entity_id = haou.organization_id
    inner join fnd_territories ft on haou.location_id = ft.territory_code
  where 
        trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
  group by 
        haou.legal_entity_id, ft.country
)

2. Bursting Control XML:
Once the data model query exposes these dynamic parameters, you must map them explicitly to the BI Publisher engine's delivery definitions. Under the Bursting Control XML block, implement the following clean mapping logic.

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://oracle.com" type="bursting">
   <xapi:request select="/DATA_DS/G_1">
      <xapi:delivery>
         <xapi:ftp server="${PARAMETER1}" remote-directory="${PARAMETER2}" remote-file="${PARAMETER3}" username="${PARAMETER4}">
            <!-- Password token -->
         </xapi:ftp>
      </xapi:delivery>
      <xapi:document output-type="${OUTPUT_FORMAT}" template-type="rtf" repository="generic">
         <xapi:template select="${TEMPLATE}" locale="${TEMPLATE_LOCALE}"/>
      </xapi:document>
   </xapi:request>
</xapi:requestset>

Best Practices & Environment Verifications:

To avoid processing exceptions during delivery, ensure your environment adheres to these three critical deployment rules.

Admin Console Pre-Configuration: The server values returned by PARAMETER1 (e.g., US_BENEFITS_SFTP) must exactly match the name of the SFTP server registered under the Administration -> Delivery -> FTP page in BI Publisher. If a typo exists, the engine throws a generic Delivery failed exception.

Directory Permissions Validation: Ensure that the user account declared in PARAMETER4 has absolute read, write, and execute permissions on the paths generated by PARAMETER2. If the directory path is missing on the remote host, BIP will not auto-create it unless specified by server parameters; it will fail the handshake instead.

Handling Memory Overheads: When processing massive employee structures, avoid passing data variables directly inside the bursting filename query string (PARAMETER3) if they can cause duplicates. Duplicate keys force file locking sequences on the target SFTP, lowering processing performance.

Conclusion:

By separating delivery settings from fixed properties and adding decision-making logic into the SQL data layer, technical consultants can greatly reduce the effort needed to maintain reports. This approach changes a fixed file extraction script into a smart, flexible distribution process that can handle complex global delivery requirements in a single scheduled run.

Visual Builder Studio: Migration Steps and Best Practices

Chinnamsetti Satyanarayana Swamy — Thu, 16 Jul 2026 10:42:25 +0000

In the previous blog (Previous blog link is provided at the end of this blog.), we learned the basics of Visual Builder Studio, including how to create a project and manage repositories. Now that you have a good understanding of these concepts, it's time to look at some practical tips that can help you work more effectively.

In this blog, we'll go through some best practices, useful migration tips, and common things to watch out for when working with Visual Builder Studio.

Migration:

Create a new Branch:

In the left navigator, click Git.
Click the Refs view and then click Branches.
From the Repositories drop-down list, select the repository.
Click + Create Branch.
In the New Branch dialog box, in Name, enter the branch name. From the Base drop-down list, select the main branch as the base branch.
Click Create.

Configure Package:

Deploy Package:

Pipeline:

Things to Consider

Use the same VB Project and extension for all changes in HCM.
Enable Branch Protection on Main branch and optionally other branches.
Ensure that when working in multiple environments, the same Project is used.
Only merge changes to the main branch when they are ready for Production.
Use Share to provide the URL to a deployed version of your changes to reviewers.
No two developers should work on the same page at the same time.
Make sure that the source and target instances are of the same release, with the same standard and one-off patches applied to both environments.
Only use Dev POD for all the VBS changes and migrate your changes from
1. Dev pod to higher environments. 2.Developers should work in separate feature branches to avoid conflict.

Customization of Processes Using Page Properties in Oracle Visual Builder Studio (VB Studio):

Functional administrators use page properties to extend Redwood pages.

Page properties enable extensibility for:

Reordering tabs in Journeys.
Showing or hiding panels in Connections.
Configuring guided journeys.
Expressions can be written in Express mode to conditionally render elements (e.g., displaying person pronouns for specific roles only).

Accessing Page Properties:

Available page properties for a page are accessible via the Page Properties pane in the Designer.
If the pane shows "We didn't find any extendable page properties on this page," it means no page properties have been delivered for that page by HCM yet.

Additional Notes:

Many page properties have already been delivered across various Redwood pages.
These properties provide diverse options to tailor the application.

Common Uses of Page Properties Include:

Adding custom content to a page or region.
Configuring guided journeys.
Enabling nudges.
Controlling page elements such as sorting, tabs, and field display in some cases.

Recommended Project Structure:

Use one project for all HCM changes.
Use one Git repository.
Have one workspace (multiple workspaces are allowed).
Use multiple development/feature branches per developer/page.
Avoid multiple projects to prevent overwriting.
Use local branches for development and commit them in individual development/feature branches (example: cwk_branch, emp_branch)
Use release branches(main) for deployment.

VBS Branching Strategy:

Why do you need a branching strategy

Allows control over what is deployed to Production.
Allows users to create personalizations in isolation from others or in conjunction depending on the requirement.
Allows different sets of personalizations to be developed and deployed at different.

How do you decide what your branching strategy should be
What are your timelines?
• How is you team structured ?
• How streamlined do you need the development environment to be?
• How much complexity can you tolerate ?
NOTE: Managing branches can only be done in Advanced Mode.

Create a Blank Branch (Clean Code Reference)

Acts as a clean template with no custom code.
Serves as a reference point for any new feature branches created.
Ensures developers always branch from a consistent baseline.

Setup Branch Protection for Main branch(Production Release)

This is your release branch.
All code gets merged here through pull/merge requests
Include approval workflows to control what gets into higher environments and production. NOTE: Make the main branch private and setup some approval workflows before merging the code to main branch

Local Development Branch:

The local branch gets autocreated whenever we open the workspace
We do all our changes in the local development branch
Once all the changes are completed, we publish the changes to the working environment and also push the code to feature branch.

Feature Branch (Branch per Page or Developer)

Create a separate Feature branch for each UI page being modified or per Developer based on the feature being developed.
Promotes isolated development and avoids conflicts.

Development Flow:

Developers make changes in local branches.
Once completed and tested, changes are committed to the feature branches.
From feature branches, merge into the main branch after testing and review.

Approval Work Flow Diagram:

(Link to previous Blog: https://dev.to/satyanarayana_swamy/get-started-with-oracle-visual-builder-studio-for-fusion-apps-extensions-4fdd)

Get Started with Oracle Visual Builder Studio for Fusion Apps Extensions

Chinnamsetti Satyanarayana Swamy — Wed, 15 Jul 2026 12:56:02 +0000

Introduction:

With the rapid evolution of the Oracle Redwood User Experience (UX), classic page personalization tools—such as Page Composer, Sandboxes, and Design Studio—are transitioning to a new standard. To modify, extend, or personalize modern Redwood pages in Oracle Fusion Cloud, implementation teams must transition to Oracle Visual Builder Studio (VBS).

VBS gives consultants the exact low-code and visual development framework used by Oracle's internal product engineers to build the core cloud application rows. This blog provides a step-by-step introduction to the fundamentals of VBS, outlining the baseline architecture and guiding you through your very first UI extension layout.

VB Studio (VBS) is included as part of the Oracle SaaS license and built into Oracle Fusion SaaS environments such as HCM or ERP.
VB Studio is the recommended option for extending Fusion user interface.
All new modules in Fusion Apps (the Redwood modules) are built using Visual Builder, and VBS is the tool used for both Oracle’s own development and customer customizations.
Fusion VB Studio comes enabled in both the dev/test instances.

Oracle Cloud – Overview:

Visual Builder is available out of the box for your Fusion environments.
Visual Builder can be used to personalize Redwood pages and can be used to migrate technical objects.
VBS is GIT online.
VBS can be connected to a "Source".

Oracle Cloud – Security Consideration:

There are delivered roles that are required to provide access to VBS

Developer role: ORA_FND_APPLICATION_DEVELOPER_JOB
Admin role: ORA_FND_APPLICATION_ADMINISTRATOR_JOB

You can define who is on the team for a project once you create the project (see more info below).

VBS Life Cycle:

Functional Activities:
Design:

Confirm Redwood changes
Confirm source & target instances
Identity key workflows and conditional rules

Build:

Extend Redwood pages (Required, Hidden, Read Only, Value, Conditional rules, etc)
Publish code to feature branch/source instance (ie: cwk_employee)

Test:

Login to target to validate the changes
Verify redwood page updates (fields, rules and visibility)
Test features and functionality

Technical Activities:
Design:

Configure VBS (Project, workspace, report)
Plan version control strategy and branching model

Build:

Any advanced features (java script/json updates)
Approve changes & deploy to main branch

Test:

Perform regression testing on impacted components
Verify deployment success and error logs

Other Activities:

Build upstream (ie: UAT, PROD) pipeline
Monitor pipeline health and resolve build failures quickly
Manage post-refresh deployment activities

VBS Components:

Project
Workspace
Git
Builds
Environments

Initial Setup – Create Project:

Create a Project as an empty project.
Add the team when creating the project (can be adjusted later).
This must happen at the beginning of the project.

Workspace:

Workspaces are where most of a user’s work occurs.
Workspaces are unique to a user identity.
Workspace lets you operate on a local copy of the Extension source, which is a clone of the copy that's in the central VBS repository.
Changes made are stored in the git repository and ultimately get synced back to the project repository.
Also associates an environment for deploying packaged code.

Initial Setup – Create Repository

Navigate to GIT and select 'Create Repository'.
Create a New Branch per pillars (Recruiting, Core-HR, Benefits).

Enable Branch Protection on the Main branch
Never make direct updates to the Main branch (see recommended branch structure)

Branches

When you open a workspace and make personalization's, the changes are initially stored in a branch, separate from other code.
A branch isolates the changes you're working on, allowing you to develop without affecting the Master code.
Once changes are finalized, they can be merged into the main(production) branch, which holds the Master set of code changes.

Environments:

Use Environments to define the target Oracle Cloud Applications where you want to deploy code changes.
Environments Page

Create and delete environments.
Add/remove instances tied to an environment.
View the health status, account names, and service ID’s for each environment
Review the deployments for extensions.
Use the extension lifecycle button to manage deployed extensions across environments.

Now that we have covered what Visual Builder Studio is, how to set up your project, and how to manage repositories, you are ready for the next step. In my next blog, I will share key best practices, migration tips, and critical factors to watch out for.

Solving Complex Overtime Thresholds: Incorporating Holiday and Leave Hours into OTL Calculations

Chinnamsetti Satyanarayana Swamy — Tue, 14 Jul 2026 09:36:57 +0000

Introduction:

Managing compliance for multi-tiered compensation policies is a major operational challenge for global enterprises. The problem intensifies when managing complex overtime (OT) and compensatory time rules across distinct employee populations—such as Non-Exempt workers, Public Safety Officers, and Exempt teams.

A common pitfall in Oracle Cloud Time and Labor (OTL) implementations occurs when Absence Management transactions (like Holiday, Annual, or Sick Leave) must factor directly into weekly overtime thresholds. Out-of-the-box rule templates often struggle to process multi-tier splits or capture absence hours within active timecard evaluation passes.

This technical guide presents an end-to-end framework. It showcases three production-grade, two-pass Time Calculation Fast Formulas that dynamically include absence hours, reduce statutory limits, and split premium allocations across custom worker groups.

Business Scenario & Policy Matrix:

Core Architectural Challenge:

Standard Fast Formulas evaluate timecards line by line. When an absence record contains no payroll mapping data, standard evaluation loops completely ignore it.

To overcome this constraint, we implement a Two-Pass Scan Strategy:

Pass 1 (Pre-Scan Optimization Loop): Isolates all holiday or module absence hours prior to calculating premiums. It splits these hours across tracking buckets for Week 1 and Week 2, and dynamically shifts down the overtime thresholds (v_net_threshold = pMaxHrs - v_holiday_hrs).
Pass 2 (Sequential Allocation Loop): Iterates through actual worked hours. It contrasts cumulative work totals against the newly shifted thresholds, allowing absence periods to seamlessly push worked hours into premium bands.

Step-by-Step Formula Implementation:

Formula 1: Non-Exempt Workers (Double-Pay Component Splitting)
Demonstrates dynamic threshold manipulation and simultaneously populates parallel output arrays (out_measure_ary_over and out_measure_ary_over_p) to drive multiple payroll elements on a single split entry.

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : Oracle HCM Cloud
--  Formula  Name   : ORA_OTL_NON_EXEMPT_OVERTIME_FF
--  Formula Type    : Time Calculation rule
--  Purpose         : OT Calculation for Non Exempt Employees

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    01-07-2026    Satyanarayana Swamy Chinnamsetti      1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables and DBI Items*/
default for HWM_CTXARY_RECORD_POSITIONS is empty_text_number
default for measure                     is empty_number_number
default for PayrollTimeType             is empty_text_number

/*@section - Input variables */
inputs are HWM_CTXARY_RECORD_POSITIONS,
           measure,
           PayrollTimeType

/*@section - Local variable declarations and initializations*/
ffs_id                      = get_context(HWM_FFS_ID, 0)
rule_id                     = get_context(HWM_RULE_ID, 0)  
ffName                      = 'ORA_OTL_NON_EXEMPT_OVERTIME_FF'
hExecType               = Get_Hdr_Text(rule_id, 'RULE_EXEC_TYPE', 'CREATE')
hCreateYn               = 'N'
pMaxHrs                 = get_rvalue_number(rule_id, 'DEFINED_LIMIT', 40)
wMaAry              = HWM_CTXARY_RECORD_POSITIONS.count
nidx                = 0
wkTotalHrsTc        = 0
v_holiday_hrs_w1    = 0
v_holiday_hrs_w2    = 0
scan_idx                = 0
day_scan_c              = 0
day_process_c       = 0
rLog                        = add_rlog(ffs_id, rule_id, '>>> Enter - ' || ffName)

/*@section - Return Variable initialization*/
out_measure_ary_under   = empty_number_number
out_measure_ary_over    = empty_number_number
out_measure_ary_over_p  = empty_number_number

/*@section - Business Logic and Interface*/
if (upper(hExecType) = 'CREATE') then 
(
    hCreateYn = 'Y'
)
v_net_threshold     = pMaxHrs

while (scan_idx < wMaAry) loop
(
  scan_idx = scan_idx + 1  
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'END_DAY') then
    (
        day_scan_c = day_scan_c + 1
    )
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'DETAIL') then
    (
        if (NOT PayrollTimeType.exists(scan_idx) and measure.exists(scan_idx)) then
        (
            if (day_scan_c < 7) then
            (
                v_holiday_hrs_w1 = v_holiday_hrs_w1 + measure[scan_idx]
            )
            else
            (
                v_holiday_hrs_w2 = v_holiday_hrs_w2 + measure[scan_idx]
            )
        )
    )
)
v_net_threshold = pMaxHrs - v_holiday_hrs_w1

rLog = add_rlog(ffs_id, rule_id, 'W1 Holiday Hrs: ' || to_char(v_holiday_hrs_w1) || ' | W1 Net Threshold: ' || to_char(v_net_threshold))

while (nidx < wMaAry) loop
(     
  nidx = nidx + 1     
  tcRecPosition = HWM_CTXARY_RECORD_POSITIONS[nidx]  
  if (tcRecPosition = 'END_DAY') then
  (
        day_process_c = day_process_c + 1  
        if (day_process_c = 7) then
        (
            wkTotalHrsTc    = 0 
            v_net_threshold = pMaxHrs - v_holiday_hrs_w2

            rLog = add_rlog(ffs_id, rule_id, '--- Switch to Week 2 --- W2 Holiday Hrs: ' || to_char(v_holiday_hrs_w2) || ' | W2 Net Threshold: ' || to_char(v_net_threshold))
    )
  )  
  if (tcRecPosition = 'DETAIL') then
  (
        if (measure.exists(nidx)) then
        (
            tcMeasure = measure[nidx]
            if (PayrollTimeType.exists(nidx)) then
            (
                if (wkTotalHrsTc >= v_net_threshold) then
                (
                    out_measure_ary_under[nidx]  = 0            
                    out_measure_ary_over[nidx]   = tcMeasure
                    out_measure_ary_over_p[nidx] = tcMeasure
                )
                else if ((wkTotalHrsTc + tcMeasure) > v_net_threshold) then
                (
                    v_reg_allowed                            = v_net_threshold - wkTotalHrsTc
                    v_ot_split                                   = tcMeasure - v_reg_allowed
                    out_measure_ary_under[nidx]  = v_reg_allowed
                    out_measure_ary_over[nidx]   = v_ot_split
                    out_measure_ary_over_p[nidx] = v_ot_split
                )
                else
                (
                    out_measure_ary_under[nidx]  = tcMeasure
                )
                wkTotalHrsTc = wkTotalHrsTc + tcMeasure
            )
        )
  )
)  
rLog = add_rlog(ffs_id, rule_id, '<< Exit - ' || ffName)

/*@section - Return the calculated value*/
return out_measure_ary_under, out_measure_ary_over, out_measure_ary_over_p

Formula 2: Public Safety Personnel (In-Line Factor Scaling)
Directly scales the calculated remainder array by a 1.5x factor in the script's calculation loop. It handles the ELSE branch cleanly to preserve and return unmapped holiday records to the payroll engine.

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : Oracle HCM Cloud
--  Formula  Name   : ORA_OTL_PUBLIC_SAFETY_OVERTIME_FF
--  Formula Type    : Time Calculation rule
--  Purpose         : Compensatory Time Earned Calculation with Holiday Threshold Reductions

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    02-07-2026    Satyanarayana Swamy Chinnamsetti      1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables and DBI Items*/
default for HWM_CTXARY_RECORD_POSITIONS is empty_text_number  
default for measure                     is empty_number_number 
default for PayrollTimeType             is empty_text_number 

/*@section - Input variables */
inputs are HWM_CTXARY_RECORD_POSITIONS,
                     measure, 
                     PayrollTimeType

/*@section - Local variable declarations and initializations*/
ffName            = 'ORA_OTL_PUBLIC_SAFETY_OVERTIME_FF'
ffs_id            = GET_CONTEXT(HWM_FFS_ID, 0)
rule_id           = GET_CONTEXT(HWM_RULE_ID, 0)
pMaxHrs           = get_rvalue_number(rule_id, 'DEFINED_LIMIT', 40) 
wMaAry            = HWM_CTXARY_RECORD_POSITIONS.count
scan_idx          = 0
nidx              = 0
day_scan_c        = 0
day_process_c     = 0
v_holiday_hrs_w1  = 0
v_holiday_hrs_w2  = 0
wkTotalHrsTc      = 0   
rLog              = add_rlog(ffs_id, rule_id, '>>> Enter - ' || ffName)

/*@section - Return Variable initialization*/
measure_under = empty_number_number 
measure_over  = empty_number_number

/*@section - Business Logic and Interface*/
while (scan_idx < wMaAry) loop
(
  scan_idx = scan_idx + 1  
  if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'END_DAY') then
  (
    day_scan_c = day_scan_c + 1
  )
  if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'DETAIL') then
  (
    if (not PayrollTimeType.exists(scan_idx) and measure.exists(scan_idx)) then
    (
      if (day_scan_c < 7) then
      (
        v_holiday_hrs_w1 = v_holiday_hrs_w1 + measure[scan_idx]
      )
      else
      (
        v_holiday_hrs_w2 = v_holiday_hrs_w2 + measure[scan_idx]
      )
    )
  )
)
v_net_threshold = pMaxHrs - v_holiday_hrs_w1

rLog = add_rlog(ffs_id, rule_id, 'W1 Holiday Hrs: ' || to_char(v_holiday_hrs_w1) || ' | W1 Net Threshold: ' || to_char(v_net_threshold))

while (nidx < wMaAry) loop
(     
  nidx                  = nidx + 1     
  tcRecPosition = HWM_CTXARY_RECORD_POSITIONS[nidx]  
  if (tcRecPosition = 'END_DAY') then
  (
    day_process_c = day_process_c + 1  
    if (day_process_c = 7) then
    (
      wkTotalHrsTc    = 0 
      v_net_threshold = pMaxHrs - v_holiday_hrs_w2
      rLog = add_rlog(ffs_id, rule_id, '--- Switch to Week 2 --- W2 Holiday Hrs: ' || to_char(v_holiday_hrs_w2) || ' | W2 Net Threshold: ' || to_char(v_net_threshold))
    )
  )
  if (tcRecPosition = 'DETAIL') then
  (
    if (measure.exists(nidx)) then
    (
      tcMeasure = measure[nidx]
      if (PayrollTimeType.exists(nidx)) then
      (
        if (wkTotalHrsTc >= v_net_threshold) then
        (
          measure_under[nidx] = 0            
          measure_over[nidx]  = tcMeasure * 1.5
        )
        else if ((wkTotalHrsTc + tcMeasure) > v_net_threshold) then
        (
          v_reg_allowed             = v_net_threshold - wkTotalHrsTc
          v_comp_split              = tcMeasure - v_reg_allowed
          measure_under[nidx]   = v_reg_allowed
          measure_over[nidx]    = v_comp_split * 1.5
        )
        else
        (
          measure_under[nidx] = tcMeasure
        )
        wkTotalHrsTc = wkTotalHrsTc + tcMeasure
      )
      else
      (
        measure_under[nidx] = tcMeasure
      )
    )
  )
)  
rLog = add_rlog(ffs_id, rule_id, '<< Exit - ' || ffName)

/*@section - Return the calculated value*/ 
return measure_under, measure_over

Formula 3: Exempt Teams (Multi-Tier Comp Time Cascade)
Solves the challenge of progressive, multi-tier allocation cascading. This block processes chunk remnants sequentially across distinct limits: Standard regular time up to 37.5 hours, Tier 1 premium time up to 40.0 hours (1.0x), and Tier 2 premium time past 40.0 hours (1.5x).

/*
---------------------------------------------------------------------------------------------------------
--                      Copyright(C) Oracle
--                      All Rights Reserved
---------------------------------------------------------------------------------------------------------
--  Application     : Oracle HCM Cloud
--  Formula  Name   : ORA_OTL_EXEMPT_OVERTIME_FF
--  Formula Type    : Time Calculation rule
--  Purpose         : 

--  CHANGE LOG
--  Date           Author                             Version      Description
---------------------------------------------------------------------------------------------------------
    03-07-2026    Satyanarayana Swamy Chinnamsetti      1.0          Initial Version
---------------------------------------------------------------------------------------------------------
*/
/*@section - Default values for input variables and DBI Items*/
default for HWM_CTXARY_RECORD_POSITIONS is empty_text_number  
default for measure                     is empty_number_number 
default for PayrollTimeType             is empty_text_number 

/*@section - Input variables */
inputs are HWM_CTXARY_RECORD_POSITIONS,
                     measure, 
                     PayrollTimeType 

/*@section - Local variable declarations and initializations*/
ffName            = 'ORA_OTL_EXEMPT_OVERTIME_FF'
ffs_id            = GET_CONTEXT(HWM_FFS_ID, 0) 
rule_id           = GET_CONTEXT(HWM_RULE_ID, 0) 
pMaxHrs           = get_rvalue_number(rule_id, 'DEFINED_LIMIT', 37.5) 
pTier1Gap         = 2.5
wMaAry            = HWM_CTXARY_RECORD_POSITIONS.count
scan_idx          = 0
nidx              = 0
day_scan_c        = 0
day_process_c     = 0
v_holiday_hrs_w1  = 0
v_holiday_hrs_w2  = 0
wkTotalHrsTc      = 0   
rLog              = add_rlog(ffs_id, rule_id, '>>> Enter - ' || ffName)

/*@section - Return Variable initialization*/
measure_under = empty_number_number
measure_over = empty_number_number

/*@section - Business Logic and Interface*/
while (scan_idx < wMaAry) loop
(
    scan_idx = scan_idx + 1  
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'END_DAY') then
    (
        day_scan_c = day_scan_c + 1
    )
    if (HWM_CTXARY_RECORD_POSITIONS[scan_idx] = 'DETAIL') then
    (
        if (not PayrollTimeType.exists(scan_idx) and measure.exists(scan_idx)) then
        (
            if (day_scan_c < 7) then
            (
                v_holiday_hrs_w1 = v_holiday_hrs_w1 + measure[scan_idx]
            )
            else
            (
                v_holiday_hrs_w2 = v_holiday_hrs_w2 + measure[scan_idx]
            )
        )
    )
)
v_net_threshold1 = pMaxHrs - v_holiday_hrs_w1
v_net_threshold2 = v_net_threshold1 + pTier1Gap

rLog = add_rlog(ffs_id, rule_id, 'W1 Regular Limit: ' || to_char(v_net_threshold1) || ' | W1 Tier 1 Limit: ' || to_char(v_net_threshold2))

while (nidx < wMaAry) LOOP
(     
    nidx                    = nidx + 1     
    tcRecPosition = HWM_CTXARY_RECORD_POSITIONS[nidx]  
    if (tcRecPosition = 'END_DAY') then
    (
        day_process_c = day_process_c + 1  
        if (day_process_c = 7) then
        (
            wkTotalHrsTc     = 0 
            v_net_threshold1 = pMaxHrs - v_holiday_hrs_w2
            v_net_threshold2 = v_net_threshold1 + pTier1Gap
            rLog = add_rlog(ffs_id, rule_id, '--- Switch to Week 2 --- W2 Regular Limit: ' || to_char(v_net_threshold1) || ' | W2 Tier 1 Limit: ' || to_char(v_net_threshold2))
        )
    )
    if (tcRecPosition = 'DETAIL') then
    (
        if (measure.exists(nidx)) then
        (
            tcMeasure = measure[nidx]
            if (PayrollTimeType.exists(nidx)) then
            (
                v_rem         = tcMeasure
                v_reg_chunk   = 0
                v_tier1_chunk = 0
                v_tier2_chunk = 0
                if (wkTotalHrsTc < v_net_threshold1) then
                (
                    v_avail1 = v_net_threshold1 - wkTotalHrsTc
                    if (v_rem <= v_avail1) then
                    (
                        v_reg_chunk  = v_rem
                        wkTotalHrsTc = wkTotalHrsTc + v_rem
                        v_rem        = 0
                    )
                    else
                    (
                        v_reg_chunk  = v_avail1
                        wkTotalHrsTc = wkTotalHrsTc + v_avail1
                        v_rem        = v_rem - v_avail1
                    )
                )
                if (v_rem > 0 and wkTotalHrsTc >= v_net_threshold1 and wkTotalHrsTc < v_net_threshold2) then
                (
                    v_avail2 = v_net_threshold2 - wkTotalHrsTc
                    if (v_rem <= v_avail2) then
                    (
                        v_tier1_chunk = v_rem
                        wkTotalHrsTc  = wkTotalHrsTc + v_rem
                        v_rem         = 0
                    )
                    else
                    (
                        v_tier1_chunk = v_avail2
                        wkTotalHrsTc  = wkTotalHrsTc + v_avail2
                        v_rem         = v_rem - v_avail2
                    )
                )
                if (v_rem > 0 and wkTotalHrsTc >= v_net_threshold2) then
                (
                    v_tier2_chunk = v_rem
                    wkTotalHrsTc  = wkTotalHrsTc + v_rem
                    v_rem         = 0
                )
                measure_under[nidx] = v_reg_chunk
                if ((v_tier1_chunk + v_tier2_chunk) > 0) then
                (
                    measure_over[nidx] = (v_tier1_chunk * 1.0) + (v_tier2_chunk * 1.5)
                )
            )
            else
            (
                measure_under[nidx] = tcMeasure
            )
        )
    )
)  
rLog = add_rlog(ffs_id, rule_id, '<< Exit - ' || ffName)

/*@section - Return the calculated value*/ 
return measure_under, measure_over

Key Functional Mechanics Explanations

1. Dynamic Threshold Shifting
Hardcoding 37.5 or 40-hour limits inside a calculation rule forces the engine to ignore overlapping historical balances. By executing an isolated pre-scan loop across data indexes (HWM_CTXARY_RECORD_POSITIONS), the logic shifts thresholds dynamically before distributing hours into worked categories. This approach includes absences in threshold metrics without altering the original absence records.

2. Remainder Cascading Logic
The cascading architecture implemented in Formula 3 uses an isolated container tracker (v_rem) to break down individual time entries. When an entry spans multiple limit zones, the logic carves out and counts chunks against available room profiles sequentially. This guarantees down-to-the-minute allocation accuracy for complex multi-tier plans.

3. Simultaneous Component Routing
To minimize configuration overhead, Formula 1 uses a multi-array mapping strategy. Instead of running separate rules to generate basic premium additions, the calculation engine returns out_measure_ary_over and out_measure_ary_over_p concurrently. This approach automatically routes double-pay balances to separate payroll components during the evaluation run.

Conclusion

Integrating timecard calculations across OTL, Absence Management, and Global Payroll requires a deliberate, memory-conscious approach to rule design. Using a two-pass scan architecture allows your team to enforce multi-tiered compliance rules, account for cross-module absence variances, and maintain stable processing performance across large user groups.

Loading Global User-Defined Tables (UDT) Without an LDG using HCM Data Loader

Chinnamsetti Satyanarayana Swamy — Fri, 03 Jul 2026 15:28:30 +0000

Before we get into loading a User-Defined Table (UDT) without a Legislative Data Group (LDG) using HCM Data Loader, let's first understand what a UDT is and why it's an important feature in Oracle HCM.

What is a User-Defined Table (UDT)?
A User-Defined Table (UDT) in Oracle Fusion HCM is a flexible way to store business-specific data that can be accessed whenever it's needed. You can think of it as a spreadsheet built into Oracle HCM, where you define your own lookup values instead of hardcoding them into Fast Formulas or integrations.

Rather than embedding values directly in your logic, you store them in a UDT and retrieve them whenever required. This makes your solutions easier to maintain and update over time.

A User-Defined Table is made up of three main components:

Columns: These represent the different types of information you want to store.
Rows: These act as the lookup keys or criteria used to identify a specific record.
Column Instances: These are the actual values stored where a row and a column intersect.

Why Use User-Defined Tables?
If you've ever had to update hardcoded values in a Fast Formula, you already know how time-consuming and error-prone it can be. User-Defined Tables solve this problem by keeping your business data separate from your application logic.

Some of the key benefits include:

Simpler maintenance: Business values can be updated without modifying the Fast Formula itself.
Dynamic lookups: Functions such as GET_TABLE_VALUE allow you to retrieve values from the table based on the lookup key you provide.
Greater flexibility: UDTs can store text, numbers, ranges, and other business-specific values, making them suitable for a wide variety of use cases.

By using UDTs, you can build solutions that are easier to manage, more scalable, and adaptable to changing business requirements.

Why use HCM Data Loader to create the User-Defined Table?
Creating a simple User-Defined Table (UDT) with just a few rows and a single column is easy through the Oracle Fusion user interface. The process is straightforward, for a local table, select the appropriate Legislative Data Group (LDG) from the dropdown list. If you are creating a global table, simply leave the LDG field empty.

While this approach works well for small datasets, it becomes impractical in real-world business scenarios. Organizations often maintain hundreds or even thousands of records for items such as payroll mappings, regional allowances, or other reference data. Entering such large datasets manually through the application is both time-consuming and prone to errors.

This is where HCM Data Loader (HDL) becomes the preferred solution. HDL enables you to package large volumes of reference data into a single data file and import everything in one operation, making bulk data loading both efficient and reliable. However, moving from the user interface to HDL introduces an important challenge, particularly when working with global tables where the Legislative Data Group (LDG) field is intentionally left blank.

To begin, we will create a standard User-Defined Table (UDT) with a Legislative Data Group (LDG) using HCM Data Loader (HDL). For this, we will use the UserDefinedTable.dat business object.

Below are the Metadata and Merge lines for a sample UDT.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName
MERGE|UserDefinedTable|ORA_US_RELOCATION_ALLOWANCE_UDT|M|T|ORA_US_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableCode|LegislativeDataGroupName|RowName|RowLowRangeOrName
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group|GRADE_1|GRADE_1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|ORA_US_RELOCATION_ALLOWANCE_UDT|IN Legislative Data Group|GRADE_2|GRADE_2
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|UserTableCode|RowLowRangeOrName
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|IN Legislative Data Group|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|GRADE_1
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|IN Legislative Data Group|MAX_AMOUNT|ORA_US_RELOCATION_ALLOWANCE_UDT|GRADE_2

Once the data loader completes successfully, we can navigate to the User-Defined Tables section to verify that the table structure, column parameters, and row entries were correctly generated within our Legislative Data Group.

Now, let's try to create a global UDT by using the exact same METADATA and MERGE line structures, but with one change, we will remove the Legislative Data Group references entirely from the data file.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName
MERGE|UserDefinedTable|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|M|T|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableCode|RowName|RowLowRangeOrName
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_1|GRADE_1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_2|GRADE_2
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|UserColumnCode|UserTableCode|RowLowRangeOrName
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_1
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|GRADE_2

While you are trying to load the dat file, you will encounter the below issue.

To resolve this parent-child relationship issue, the deployment process must be divided into two sequential load stages.

First Load: Create a UserDefinedTable.dat file by including the UserDefinedTable, UserDefinedTableColumn, and UserDefinedTableRow METADATA and MERGE records together in a single HDL file. This load establishes the table structure without creating any UserDefinedTableColumnInstance records.

Second Load: After the first load completes successfully, create a separate UserDefinedTable.dat file containing only the UserDefinedTableColumnInstance METADATA and MERGE records.

First Load:
Below are the METADATA and MERGE lines.

METADATA|UserDefinedTable|UserTableCode|RangeOrMatch|UserKeyUnits|UserRowTitle|UserTableName|LegislativeDataGroupName|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTable|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT|M|T|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT_ROWS|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT||HRC_SQLLOADER|RELOCATION_ALLOWANCE_TABLE
METADATA|UserDefinedTableColumn|UserColumnCode|DataType|UserColumnName|UserTableCode|LegislativeDataGroupName|UserTableId(SourceSystemId)|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTableColumn|MAX_AMOUNT|T|MAX_AMOUNT|ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT||RELOCATION_ALLOWANCE_TABLE|HRC_SQLLOADER|RELOCATION_ALLOWANCE_COLUMN
METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|UserTableId(SourceSystemId)|RowName|RowLowRangeOrName|LegislativeDataGroupName|SourceSystemOwner|SourceSystemId
MERGE|UserDefinedTableRow|1|4712/12/31|1951/01/01|RELOCATION_ALLOWANCE_TABLE|GRADE_1|GRADE_1||HRC_SQLLOADER|RELOCATION_ALLOWANCE_ROW1
MERGE|UserDefinedTableRow|2|4712/12/31|1951/01/01|RELOCATION_ALLOWANCE_TABLE|GRADE_2|GRADE_2||HRC_SQLLOADER|RELOCATION_ALLOWANCE_ROW2

We have added multiple METADATA fields to overcome the parent-child relationship issue.

Second Load:
For the second load pass to work, your data file must reference the exact records generated during the first step. Because we lack an LDG to use as a user-key qualifier, we must rely on database surrogate IDs. This means before building your column instance file, you need to run a quick BI Publisher SQL query to extract the newly generated UserColumnId and UserRowId values. Adding these unique system identifiers into your second file establishes the exact relationship required by the data loader engine.

Below is the SQL Query to get the UserColumnId and UserRowId.

select 
    t.base_user_table_name,
    r.user_row_id,
    r.row_low_range_or_name,
    c.user_column_id,
    c.base_user_column_name
from 
    ff_user_tables t,
    ff_user_rows_f r ,
    ff_user_columns c
where 
    t.base_user_table_name = 'ORA_GLOBAL_RELOCATION_ALLOWANCE_UDT'
    and t.user_table_id = r.user_table_id
    and t.user_table_id = c.user_table_id

Below are the METADATA and MERGE lines for second load.

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|SourceSystemOwner|SourceSystemId|UserColumnId|UserRowId
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|5000|HRC_SQLLOADER|RELOCATION_ALLOWANCE_VAL1|300000118166578|300000118166576
MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|8000|HRC_SQLLOADER|RELOCATION_ALLOWANCE_VAL2|300000118166578|300000118166577

With both load passes complete, let’s navigate back to the application to verify that our global table structure and data instances have been created properly.

Splitting your load into a two-step process and leveraging BI Publisher to extract those specific surrogate keys is the most reliable workaround to handle global UDT layouts. I hope this saves you time on your next Global UDT Creation.