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

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

--P2P Cycle SQL Script

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_requisition_headers_all prh,   

    por_requisition_lines_all   prl,   

    por_req_distributions_all   prd,   

    po_distributions_all        pda,   

    po_headers_all              pha,

    po_lines_all                pla,

    po_line_locations_all       plla,  

    rcv_shipment_headers        rsh,   

    rcv_shipment_lines          rsl,   

    rcv_transactions            rt,    

    ap_invoice_lines_all        aila,

    ap_invoices_all             aia,

    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

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

PO Query

SELECT

    hou_prc.bu_name,

    poh.segment1,

    poh.document_status,

    poh.type_lookup_code,

    poh.currency_code,

    poh.creation_date,

    ppnf.full_name,

    hp.party_name,

    sup.segment1 supplier_num,

    ss.vendor_site_code,

    pol.line_num,

    pol.item_description,

    pol.unit_price,

    pol.quantity,

    pol.uom_code,

    (pol.unit_price * pol.quantity) line_amount,

    pll.shipment_num,

    pll.quantity shipment_qty,

    pll.quantity_received,

    pll.quantity_billed,

    pll.promised_date,

    hl.location_code,

    pod.distribution_num,

    pod.quantity_ordered,

    /* Manually concatenating segments since KFV is unavailable */

    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || gcc.segment4 "Account"

FROM 

    po_headers_all poh,

    po_lines_all pol,

    po_line_locations_all pll,

    po_distributions_all pod,

    poz_suppliers_v sup,

    hz_parties hp,

    poz_supplier_sites_all_m ss,

    fun_all_business_units_v hou_prc,

    per_person_names_f ppnf,

    gl_code_combinations gcc,

    hr_locations_all hl

WHERE 1=1

    AND poh.po_header_id = pol.po_header_id

    AND pol.po_line_id = pll.po_line_id

    AND pll.line_location_id = pod.line_location_id

    AND poh.vendor_id = sup.vendor_id

    AND sup.party_id = hp.party_id

    AND poh.vendor_site_id = ss.vendor_site_id

    AND poh.prc_bu_id = hou_prc.bu_id

    AND poh.agent_id = ppnf.person_id

    AND ppnf.name_type = 'GLOBAL'

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

    AND pod.code_combination_id = gcc.code_combination_id

    AND pll.ship_to_location_id = hl.location_id(+)

ORDER BY 

    poh.segment1 DESC, 

    pol.line_num ASC

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

SELECT

    hou_prc.bu_name "Procurement BU",

    hp.party_name "Supplier Name",

    ss.vendor_site_code "Supplier Site",

    hp_con.person_first_name || ' ' || hp_con.person_last_name "Supplier Contact",

    hl_bill.location_code "Bill-to-Location",

    hl_ship.location_code "Ship-to-Location",

    poh.creation_date "Creation_date",

    ppnf.full_name "Buyer",

    poh.document_status "Status",

    poh.segment1 "PO Number",

    pol.line_num "Line Number",

    pol.item_description "Item Description",

    pol.quantity "Qty",

    pol.uom_code "UOM",

    pol.unit_price "Unit Price",

    /* Added Line Amount derived column */

    (pol.quantity * pol.unit_price) "Line Amount",

    /* Concatenated Charge Account */

    gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || gcc.segment4 "Charge Account",

    pod.distribution_num "Distribution Number"

FROM 

    po_headers_all poh,

    po_lines_all pol,

    po_line_locations_all pll,

    po_distributions_all pod,

    poz_suppliers sup,

    hz_parties hp,                

    hz_parties hp_con,            

    poz_supplier_sites_v ss,

    fun_all_business_units_v hou_prc,

    per_person_names_f ppnf,

    gl_code_combinations gcc,

    hr_locations_all hl_ship,

    hr_locations_all hl_bill,

    poz_supplier_contacts psc

WHERE 1=1

    /* Core PO Table Joins */

    AND poh.po_header_id = pol.po_header_id

    AND pol.po_line_id = pll.po_line_id

    AND pll.line_location_id = pod.line_location_id

    

    /* Supplier and Site Joins */

    AND poh.vendor_id = sup.vendor_id

    AND sup.party_id = hp.party_id

    AND poh.vendor_site_id = ss.vendor_site_id

    

    /* Contact Joins with Outer Join for stability */

    AND poh.vendor_contact_id = psc.vendor_contact_id(+)

    AND psc.per_party_id = hp_con.party_id(+) 

    

    /* Organization and Buyer Filter */

    AND poh.prc_bu_id = hou_prc.bu_id

    AND poh.agent_id = ppnf.person_id

    AND ppnf.name_type = 'GLOBAL'

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

    

    /* Accounting and Location Outer Joins */

    AND pod.code_combination_id = gcc.code_combination_id

    AND pll.ship_to_location_id = hl_ship.location_id(+)

    AND poh.bill_to_location_id = hl_bill.location_id(+)

    

    /* Parameters for BIP Data Model */

    AND (poh.segment1 = :P_PO_NUM OR :P_PO_NUM IS NULL)

    AND TRUNC(poh.creation_date) BETWEEN :P_FROM_DATE AND :P_TO_DATE

ORDER BY 

    poh.segment1 DESC, 

    pol.line_num ASC

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

LOV SQL Script

---------------

select poh.segment1

from

po_headers_all poh

Procurement_BU

Supplier_Name

Supplier_Site

Supplier_Contact

Bill_to_Location

Ship_to_Location

Creation_date

Buyer

Status

PO_Number

Line_Number

Item_Description

Qty

UOM

Unit_Price

Line_Amount

Charge_Account

Distribution_Number

US1 Business Unit

Lee Supplies

Lee US1

Ryan Lee

Seattle

Atlanta Branch

2026-04-17T01:00:52.000+00:00

Roth, Calvin

CLOSED FOR RECEIVING

US165320

1

Site supplies

200

zzu

22

4400

101-10-60540-620

1

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.