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.
