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.