About Natural Account in Oracle Fusion

 

 

About Natural Account in Oracle Fusion

(Documented by Venkat)

******************************************************

How is the Natural Account identified and mapped in the Oracle Fusion GL Table?

 Step 1: A journal entry is created using a code combination.

Step 2: The combination includes multiple segments (Company, Department, Natural Account, etc.).

Step 3: The Accounting Flexfield Definition identifies the Natural Account segment (e.g., SEGMENT3).

Step 4: That segment value (e.g., 5100) determines the account type (e.g., Expense)

 

 

 

GL_CODE_COMBINATIONS Table

                                          Holds up to 30 segment columns (SEGMENT1 to SEGMENT30).
                                 One of these segments is designated as the Natural Account.

Accounting Flexfield Definition

v Defines which segment corresponds to the Natural Account.

Example: SEGMENT3 might be labeled as Natural Account in the above pic.

 

Mapping Process

We check the Flexfield Setup to see which segment is assigned.

That segment in the GL combinations table holds the Natural Account values (like 5100 for “Expenses”).

GL_CODE_COMBINATIONS → Accounting Flexfield Definition → Natural Account Segment

What is Account Type?

Each Natural Account is assigned an Account Type (A, L, E, R, or X).

It is critical because it determines how Oracle performs Year-End Closing.

Revenue/Expense accounts are zeroed out to Retained Earnings.

Asset/Liability/Equity accounts carry their balances forward.

What is Account Validation?

It usually has a Value Set attached to it to ensure only predefined, valid account numbers are used in the system.

Why is the account called "Natural"?

It is called "natural" because it follows the logical, inherent classification of accounting.

Regardless of which department spends the money, the "natural" classification of a "Laptop Purchase" remains an "Asset" or "Equipment Expense."

   

Real-Time Requirement:-

Q: Write a SQL query to  fetch Natural Account, account_type, currency, and invoice amount details

SELECT

    gcc.segment1 AS natural_account,

    gcc.account_type,

    ai.invoice_currency_code,

    SUM(ail.amount) AS total_invoice_amount

FROM

    ap_invoices_all ai,

    ap_invoice_lines_all ail,

    ap_invoice_distributions_all aid,

    xla_distribution_links xdl,

    xla_ae_lines xal,

    gl_code_combinations gcc

WHERE

    ai.invoice_id = ail.invoice_id

    AND ail.invoice_id = aid.invoice_id

    AND ail.line_number = aid.invoice_line_number

    AND aid.invoice_distribution_id = xdl.source_distribution_id_num_1

   

AND xdl.source_distribution_type = 'AP_INV_DIST'

    AND xdl.ae_header_id = xal.ae_header_id

   

AND xdl.ae_line_num = xal.ae_line_num

    AND xal.code_combination_id = gcc.code_combination_id

    AND ai.cancelled_date IS NULL

GROUP BY

    gcc.segment1,

    gcc.account_type,

    ai.invoice_currency_code

ORDER BY

    total_invoice_amount DESC 

FAQ

Q: If a journal entry fails due to an invalid Natural Account, how would you troubleshoot? 

A: Check the chart of accounts setup, validate the Natural Account segment value, and ensure it is enabled and assigned to the correct account type.

Q: How would you design a Chart of Accounts for a multinational company? 

A: Include segments like Company, Department, Natural Account, and Location. Ensure Natural Account is standardized across entities for consolidated reporting.

Q:Can you give an example of a Natural Account value? 

A:Example: 5100 – Office Supplies Expense (Expense account).

Q: How does the Natural Account interact with Subledger Accounting (SLA)? 

A: SLA rules map transactions from subledgers (AP, AR, FA) to the Natural Account segment in GL.

Q: How do BI Publisher or OTBI reports use the Natural Account? 

A:They filter and group financial data based on Natural Account values for reporting.

Q: What controls can be applied to Natural Accounts? 

A: Cross-validation rules, segment value security, and account hierarchies.

 

The ORA-12505 error in Oracle SQL Developer means

 The ORA-12505 error in Oracle SQL Developer

Root cause of the problem: 

The most common cause is that the Oracle Database instance has not been started or is not registered with the listener. This generally happens when the computer has been shut down or SQL Developer has not been used for a long time. 

Steps to Resolve the problem:

Step 1: Open Command Prompt as Administrator

To ensure you have the necessary permissions to manage database services, you must run the Command Prompt with elevated privileges.

• Press the Windows Key, type cmd.

• Right-click on Command Prompt and select Run as administrator.

Step 2: Launch SQL*Plus without Logging In

Once the terminal is open, you need to enter the SQL*Plus environment without immediately connecting to a specific database schema.

• Type the following command and press Enter: sqlplus /nolog

Step 3: Connect as a System Administrator (SYSDBA)

Next, you need to connect to the Oracle instance with administrative rights to change its status.

• Type the following command: conn sys as sysdba;

• When prompted for a password, type the password (e.g., admin) and press Enter.

  Note: For security reasons, the characters will not appear on the screen as you type the password.

If the database is down, you will see a message stating: "Connected to an idle instance."

Step 4: Start the Oracle Instance

Finally, you need to move the database from an idle state to an open state so that SQL Developer can connect to it.

• Type the following command and press Enter: startup

Wait a few moments for the process to complete. You will see several lines of data regarding the System Global Area (SGA), followed by the confirmation message: "ORACLE instance started."

---

Conclusion

Database is now open and mounted! You can now return to SQL Developer and connect to your database as usual. If you frequently encounter this issue, ensure that your Oracle services are set to "Automatic" in the Windows Services panel.

Check the following for execution process:

Oracle Integration Service Limits

𝐎𝐫𝐚𝐜𝐥𝐞 𝐈𝐧𝐭𝐞𝐠𝐫𝐚𝐭𝐢𝐨𝐧 𝐂𝐥𝐨𝐮𝐝 (𝐎𝐈𝐂) – 𝐒𝐞𝐫𝐯𝐢𝐜𝐞 𝐋𝐢𝐦𝐢𝐭𝐬 (𝐆𝐞𝐧 2 𝐯𝐬 𝐆𝐞𝐧 3)

=======================================================

Understanding service limits is very important while designing integrations in Oracle Integration Cloud (OIC).

Many performance and timeout issues occur because we don’t consider payload size, adapter limits, or execution time constraints during design.

Here’s a quick comparison of key limits:

=======================================

REST Adapter payload limits

SOAP XML payload size

 FTP read/download limits

Stage file processing limits

Connectivity Agent limits

Timeout configurations (Sync / Async / Scheduled)

Active integration limits

Key Highlights:

===============

Synchronous timeout: 300 seconds

Asynchronous & Scheduled timeout: 6 hours

Outbound adapter timeout: 240 seconds

JavaScript timeout: 15 seconds

Gen 3 supports higher payload limits compared to Gen 2 in many areas

Always design integrations considering:

Large file handling strategy (chunking / stage file)

Async patterns for long-running processes

Agent payload restrictions

Base64 and email size limits

Proper architecture planning helps avoid runtime failures and improves performance.

P2P Cycle SQL Script in Oracle Fusion

 --P2P Cycle SQL Script

Functional Flow

1 ) Purchase Requisition

Business user identifies need

Creates PR in system

Budget gets validated

2 ) PR Approval

Manager reviews and approves

Budget check happens

Approval routing complete

3 ) Purchase Order Creation

Approved PR converts to PO

Supplier gets selected

Terms & conditions set

4 ) PO Approval

Amount-based routing

Multi-level approval workflow

Final authorization received

5 ) Goods/Service Receipt

Warehouse receives items

Quantity verified

Quality inspection done

6 ) Invoice Receipt

Supplier sends invoice

Manual or EDI entry

Initial validation runs

7 ) 3-Way Match

PO vs Receipt vs Invoice

Quantity must match

Price tolerance checked

8 ) Invoice Approval

Holds resolved

Final invoice approval

Exceptions handled

9 ) Payment Processing

Payment terms applied

Payment run created

Bank file generated

10 ) GL Accounting

Technical Flow

SELECT 

-- 1. Requisition Data (PR)

    prh.requisition_number,

    prh.document_status            AS req_status,

    prl.line_number                AS req_line_number, 

    prl.item_description           AS req_description,

    prl.quantity                   AS req_quantity,

    -- 2. Purchasing Data (PO)

    pha.segment1                   AS po_number,

    pha.document_status            AS po_status,

    pla.line_num                   AS po_line_number,  -- PO stays as line_num

    plla.quantity                  AS po_quantity_ordered,   

    -- 3. Receiving Data (GRN)

    rsh.receipt_num,

    -- 4. Payables Data (Invoice,Payment)

    aia.invoice_num,

    aia.invoice_amount,

    aca.check_number               AS payment_number,

    -- 5. Subledger Accounting Data (SLA - The Rules Engine)

    xah.ae_header_id               AS sla_journal_id,

    xal.accounting_class_code      AS account_type, -- LIABILITY, EXPENSE, CASH

    xal.entered_dr                 AS sla_debit,

    xal.entered_cr                 AS sla_credit,

    -- 6. General Ledger Data (GL - The Final Destination)

    gjh.name                       AS gl_journal_name,

    gjh.status                     AS gl_posting_status, -- P = Posted, U = Unposted

    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 AS gl_account_string

FROM 

--POR Tables

    por_requisition_headers_all prh,   

    por_requisition_lines_all   prl,   

    por_req_distributions_all   prd,   

--PO Tables

    po_distributions_all        pda,   

    po_headers_all              pha,

    po_lines_all                pla,

    po_line_locations_all       plla,  

    rcv_shipment_headers        rsh,   

--Receipts Tables

    rcv_shipment_lines          rsl,   

    rcv_transactions            rt,    

-- Payable Tables

    ap_invoice_lines_all        aila,

    ap_invoices_all             aia,

--Payment Tables

    ap_invoice_payments_all     aipa,

    ap_checks_all               aca,

     --Subledger Accounting (SLA) Tables

    xla_transaction_entities    xte,

    xla_ae_headers              xah,

    xla_ae_lines                xal,

    -- General Ledger (GL) Tables

    gl_import_references        gir,

    gl_je_headers               gjh,

    gl_je_lines                 gjl,

    gl_code_combinations        gcc

WHERE 1=1

    -- JOIN: PO to PR Link

    AND pha.po_header_id = pla.po_header_id

    AND pla.po_line_id = plla.po_line_id

    AND plla.line_location_id = pda.line_location_id

    AND pda.req_distribution_id = prd.distribution_id(+)

    AND prd.requisition_line_id = prl.requisition_line_id(+)

    AND prl.requisition_header_id = prh.requisition_header_id(+)

    -- JOIN: PO to Receipt Link 

    AND plla.line_location_id = rsl.po_line_location_id(+)

    AND rsl.shipment_header_id = rsh.shipment_header_id(+)

    AND rsl.shipment_line_id = rt.shipment_line_id(+)

    AND rt.transaction_type(+) = 'RECEIVE' 

    -- JOIN: PO to AP Invoice Link

    AND pla.po_line_id = aila.po_line_id(+)

    AND aila.invoice_id = aia.invoice_id(+)

    -- JOIN: AP Invoice to Payment Link

    AND aia.invoice_id = aipa.invoice_id(+)

    AND aipa.check_id = aca.check_id(+)  

    -- JOINS: AP Invoice to SLA to GL

    -- JOIN: AP Invoice to SLA Transaction Entity (The Subledger Bridge)

    -- source_id_int_1 stores the Invoice ID when entity_code is 'AP_INVOICES'

    AND aia.invoice_id = xte.source_id_int_1(+)

    AND xte.entity_code(+) = 'AP_INVOICES'

    AND xte.application_id(+) = 200 -- 200 is the hardcoded ID for Payables

    -- JOIN: SLA Entity to SLA Headers and Lines

    AND xte.entity_id = xah.entity_id(+)

    AND xah.ae_header_id = xal.ae_header_id(+)

    -- JOIN: SLA Lines to GL Import References (The GL Bridge)

    AND xal.gl_sl_link_id = gir.gl_sl_link_id(+)

    AND xal.gl_sl_link_table(+) = 'XLA_AE_LINES'

    -- JOIN: GL Import References to GL Journal Headers Lines

    AND gir.je_header_id = gjh.je_header_id(+)

    AND gir.je_header_id = gjl.je_header_id(+)

    AND gir.je_line_num = gjl.je_line_num(+)

    -- JOIN: GL Line to Code Combinations (To get the Account String)

    AND gjl.code_combination_id = gcc.code_combination_id(+)

AND pha.segment1='162027'

ORDER BY 

    pha.segment1, 

    aia.invoice_num,

    xal.entered_dr DESC

Query Result: 

Click Here

HDL in Oracle Fusion HCM Cloud

 

HDL- HCM Data Loader

It is a Bulk Data Loader to load HCM Cloud related to Objects.

What are the Business Objects we can load using HDL?

Location

Jobs

Positions

Grades

Worker-Complex one(hierarchy we need to follow)

What is the process?

HDL will contain templates

Identify the templates for the BO which u are going to load.

Prepare data for the template.

.date file(a pipe delimeter file) in zip format is ready then we will go for the import process.

Import process will move data to Stage Tables.

Load process will move the data to Base Tables(Application Tables)

Once data is there in base tables then you will be able to see it in UI.

 

Navigation:

Navigation bar, MyClient GroupsàData Exchange.

We can find HDL, HSDL & HCM Extracts.

 

What are the location base tables?

Per_locations

Per_locations_details_f

Per_locations_details_f_tl

 

Click on View Business Objects.

 

 

 

 

Search for locations

 

Location will store Place, address and other details of the organization.

File name should be as: Location.dat.

We should not change the file names.(Template Name)

Download the template.

Bkp is for structure

Location is the duplicate file.

Both are DAT file

Open Location with Notepad++

 

 

It is a HDL file

COMMENT: It consists of short text or long text. Compiler ignores the lines in Comment section.

METADATA means column structure.

Eg:

Insert into dept(deptno,dname,loc) values(20,’software’,’blr’);

In the same way

Metadata means columns for the location.

Merge means values for the location

Optional columns we can remove

Insert into dept(deptno,dname) values(20,’software’,);

Template provide the all the possible columns for the component Location.

Location Extra Ino is a component

LocationLegislative

LocationOtherAddress

Are all these mandatory?

We need to check.

Go to Myclient groups

Workforce structures

Locations

Create  a location

 

                       

Extra info optional

Legislative info optional

So, remove optional columns from the HDL file

Every column need to be separated with single pipe delimeter.

 

Click on Location in search section.

 

 

 

File Descriminator: Location

Click on Attributes

LocationId is a Surrogate Key

LocationCode and SetCode are User Key

 

 

Surrogate Id will be only available when updating the existing locations.

For creating a Location, Location Id(primary key) surrogate key is not required to pass.

Only when you are going o update existing Location, we can use Location Id.

Location Code- within the set the code to identify Location uniquely (User Key)

SetCode- COMMON

Within the SetCode Common the location code should be there uniquely.

ActiveStatus- is required for new records.

There is a Lookup available to check status.

To check Lookups, navigate to ‘’Setup and Maintenance’’ , go to Global search and search a Task called ‘’Manage Common Lookups’’

Search for the Lookup name

  

 

 

 

EffectiveStartDate= mandatory pass the value

EffectiveEndDate= optional leave it as null

 

Country=Mandatory

LocationName =Mandatory

SetId=Mandatory(we don’t have ID so alternativel we passed Setcode)

 

Once you are ready with dat zip file, go to HDL page and click on import

 

 

Select zip file, click on submit

We have two options Import only or Import and Load

 

File has placed in UCM content server and created an id automatically for it.

Check delete source file and click on submit.

Then click on Refresh

 

Metadata will be only one on the top

Merge will be multiple records

 

 

Once you do the correction, delete the existing zip file and convert dat file again to zip folder.

 

 

Every import has content id

 

Now, search the location which is created in front-end.

 

Let us try one negative case and two positive case

Change the dates for other records and try to import

For old record, effectiveenddate will be provided.

For new record, no effectiveend date

 

Negative case

COMMENT Data for Business Object: Location Version: V2 Created on: 03-08-2024

METADATA|Location|LocationCode|LocationName|SetCode|ActiveStatus|EffectiveStartDate|EffectiveEndDate|Country

MERGE|Location|xx_blr01_HDL|BLR01|COMMON|A|2024/09/20||IN

MERGE|Location|xx_blr01_HDL|BLR01|COMMON|A|2024/09/20||IN

Positive Case:

COMMENT Data for Business Object: Location Version: V2 Created on: 03-08-2024

METADATA|Location|LocationCode|LocationName|SetCode|ActiveStatus|EffectiveStartDate|EffectiveEndDate|Country

MERGE|Location|xx_blr01_HDL|BLR01|COMMON|A|2024/09/20||IN

MERGE|Location|xx_blr07_HDL|BLR07|COMMON|A|2024/09/21||IN

MERGE|Location|xx_blr008_HDL|BLR0|COMMON|A|2024/09/21||IN

date

HDL Error Report

Excel output

 

 

 

 

 

 

 

 

 

 

 

 

Active Employee Roster Query

 Active Employee Roster Query

SELECT 

    -- Person Identifiers

    papf.person_number,

    ppnf.full_name,

    ppnf.first_name,

    ppnf.last_name,

    -- Employment Details

    paam.assignment_number,

    paam.assignment_status_type,

    ppos.date_start                AS hire_date,

    ppos.actual_termination_date,

    -- Job,Department Details

    pjft.name                      AS job_name,

    houtl.name                     AS department_name,

    -- Assignment Metadata

    paam.effective_start_date      AS assignment_start_date,

    paam.effective_end_date        AS assignment_end_date

FROM 

    per_all_people_f               papf,

    per_person_names_f             ppnf,

    per_all_assignments_m          paam,

    per_periods_of_service         ppos,

    per_jobs_f_tl                  pjft,

    hr_organization_units_f_tl     houtl

WHERE 1=1

    -- Person to Name

    AND papf.person_id = ppnf.person_id

    AND ppnf.name_type = 'GLOBAL'

    -- Person to Assignment

    AND papf.person_id = paam.person_id

    -- Person to Work Relationship

    AND papf.person_id = ppos.person_id

    AND paam.period_of_service_id = ppos.period_of_service_id

    -- Assignment to Job Translation (Outer Join)

    AND paam.job_id = pjft.job_id(+)

    AND pjft.language(+) = 'US'

    --  Assignment to Department Translation (Outer Join)

    AND paam.organization_id = houtl.organization_id(+)

    AND houtl.language(+) = 'US'

    -- Active Employees Only (Assignment Status)

    AND paam.assignment_status_type = 'ACTIVE'

    AND paam.primary_flag = 'Y'         

    AND paam.assignment_type = 'E'      

    -- Filter for records active as of TODAY

    AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

    AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

    AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

    AND TRUNC(SYSDATE) BETWEEN pjft.effective_start_date(+) AND pjft.effective_end_date(+)

    AND TRUNC(SYSDATE) BETWEEN houtl.effective_start_date(+) AND houtl.effective_end_date(+)

ORDER BY 

    papf.person_number

Creation_Date vs Invoice_Date in Oracle Fusion

 Creation Date vs Invoice Date in AP_INVICES_ALL

Select 

CREATION_DATE,

INVOICE_DATE

from 

AP_INVICES_ALL

CREATION_DATE

Generated automatically assigns system timestamp (metadata) that records exactly when the record(invoice details) was saved in the Fusion database.

Ex: If you receive Invoice Details on  February 15th 2026 and you entered the details into Fusion DB on February 22nd, then the  Creation Date will be February 22nd, 2026

INVOICE_DATE

It is the date printed on the physical or electronic document sent by the supplier. User Defined Date.

Ex:- If a supplier issues an invoice on February 15th but sends it late, the Invoice Date remains February 15th.

SQL Script for invoices where the delay between the supplier's date and the system entry date is greater than 5/10/15..etc days.

SELECT 

    sup.vendor_name,

    inv.invoice_num,

    inv.invoice_date,

    TRUNC(inv.creation_date) AS system_entry_date,

    -- Truncating both ensures time is removed from both sides

    (TRUNC(inv.creation_date) - inv.invoice_date) AS days_lag

FROM 

    ap_invoices_all inv,

poz_suppliers_v sup

WHERE 1=1

and inv.vendor_id = sup.vendor_id

and (TRUNC(inv.creation_date) - inv.invoice_date) > 5

ORDER BY 

    days_lag DESC

[OR]

SELECT 

    sup.vendor_name,

    inv.invoice_num,

    inv.invoice_amount,

    inv.invoice_date,

    CAST(inv.creation_date AS DATE) AS system_entry_date,

    inv.gl_date,  -- Adding GL Date to see the accounting impact

    round((CAST(inv.creation_date AS DATE) - inv.invoice_date),2) AS days_lag

FROM 

    ap_invoices_all inv,

poz_suppliers_v sup

WHERE 1=1

and inv.vendor_id = sup.vendor_id

and (CAST(inv.creation_date AS DATE) - inv.invoice_date) > 5

 AND inv.cancelled_date IS NULL 

ORDER BY 

 days_lag DESC

 

About CAST Function

Syntax:

CAST(expression AS target_type)

Ex:

CAST(inv.creation_date AS DATE)

In Oracle Fusion, the CREATION_DATE column value is a TIMESTAMP (2026-02-22 14:30:05).

The INVOICE_DATE value is usually just a DATE ( 2026-02-22 00:00:00).

If we don't use CAST, the "Days Lag" calculation might result in a decimal (like 5.62 days) instead of a clean whole number because of the leftover hours and minutes.

Oracle stores time as a fraction of a 24-hour day:

12 hours = 12 hours = 0.5 days 

6 hours = 0.25 days

1 hour = 1/24 approximately 0.0416 days

When you subtract them without CAST:

2026-02-25.62 - 2026-02-20.00 = 5.62 days

The ".62" is simply the 14 hours and 52 minutes converted into a "percentage of a day."

Other Scenarios

String to Number:-

CAST(attribute1 AS NUMBER) 

Useful if you stored a numeric value in a text-based Descriptive Flexfield.

ID to String:-

CAST(vendor_id AS VARCHAR2(50)) 

Useful when joining an ID to a table where the key was incorrectly stored as text.

Precision Control:-

CAST(amount AS NUMBER(15,2)) — 

Forces a number to exactly two decimal places.

Use TRUNC when you are staying within Oracle (SQL, BI Publisher, OTBI). It is faster to type and very efficient.

Use CAST if you are writing code that might eventually be used in other databases (like SQL Server or PostgreSQL), as TRUNC behaves differently in those systems.

Oracle Fusion HCM Cloud- Employee Creation-REST API

 Step by Step Guide to Create an Employee using REST API 

 

Domain: Oracle Fusion HCM Cloud

Module: Core/Global HR

How to create an Employee using the REST API in Oracle HCM Cloud?

1. Create an Employee from Oracle Fusion UI

Navigation :(Create Person)-- > My Client Groups -- > New Person -- > Hire an Employee

2. GET All Employees using the POSTMAN Tool

Construct REST API using URI+Fusion URL

URI(Uniform Resource Indicator)

/hcmRestApi/resources/11.13.18.05/emps

Fusion URL

https://fusionserveraddress(need to enter active fusion instance here)

REST API URL could be:

Fusion URL+ URI

https://fusionserveraddress//hcmRestApi/resources/11.13.18.05/emps

Method: GET

3. GET a specific Employee

URI /hcmRestApi/resources/11.13.18.05/emps/{empsUniqID}

empsUniqID: Integer(Hash/PrimaryKey/Template Parameter)

REST API could be:

https://fusionserveraddress/hcmRestApi/resources/11.13.18.05/emps/{empsUniqID}

Method: GET

4. To Create Employee using REST API

URI :/hcmRestApi/resources/11.13.18.05/emps

REST API could be:

https://fusionserveraddress/hcmRestApi/resources/11.13.18.05/emps

Method: POST

Sample Request Payload could be:

https://apps2cloudfusion.blogspot.com/2026/02/employee-creation-using-rest-service.html

Sample Response Payload could be:

https://apps2cloudfusion.blogspot.com/2026/02/response-payload-employee-creation.html

5. (Search Person) -- > My Client Groups -- > Person Management. Validate if the Person record is created or not in Fusion UI.

Quick Checklist while preparing a JASON File

  Key names must be FirstName, not First Name.

  Use standard straight quotes ", not "smart" or curly quotes from Word/Teams.

 Use null (no quotes) for empty fields, don't just leave them as "".

 IDs can be strings "3000..." or numbers 3000..., but be consistent.