I’m working on a custom Oracle Fusion AP-to-GL reconciliation report and would appreciate input from anyone who has built something similar.
Business Requirement
Finance currently performs AP reconciliation using multiple Oracle Fusion reports:
-
Accounts Payable Trial Balance
-
Payables Posted Invoice Register
-
Payables Posted Payment Register
-
GL Trial Balance
The reconciliation process is essentially:
Opening AP Trial Balance
- Current Period Posted Invoices
− Current Period Posted Payments
= Closing AP Trial Balance
= GL AP Liability Balance
The problem is that Oracle’s seeded reports are difficult to analyze and reconcile.
Users export everything to Excel and perform manual VLOOKUPs, pivot tables, and variance analysis.
We are trying to build a single custom report (or a set of custom reports) that provides:
-
Posted Invoice Activity
-
Posted Payment Activity
-
AP Liability Movement
-
AP Trial Balance Position
-
GL Liability Balance
-
Reconciliation Variance
in a finance-friendly flattened format.
Current Challenge
We initially tried extending a custom Account Analysis report built from GL.
The query starts from:
GL_JE_LINES
-> GL_IMPORT_REFERENCES
-> XLA_AE_LINES
-> XLA_DISTRIBUTION_LINKS
-> AP_INVOICE_DISTRIBUTIONS_ALL
-> AP_INVOICES_ALL
and separately joins AP payment information through a UNION ALL approach.
For a liability natural account (segment3):
-
GL Trial Balance Net Activity = ~₹2.53 Lakhs
-
Custom Account Analysis Report Net Activity = ~₹61 Crores
Clearly something is wrong.
My suspicion is one or more of the following:
-
Fan-out caused by XLA_DISTRIBUTION_LINKS
-
Multiple AP distributions multiplying GL lines
-
Invoice and payment double counting
-
Ledger set expansion
-
Period mismatch
-
Incorrect SLA-to-GL join logic
Oracle Seeded Reports Reviewed
Payables Invoice Register
Main tables:
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
GL_CODE_COMBINATIONS
This appears to be an operational report and not a GL-reconciled report.
Payables Payment Register
Main tables:
AP_CHECKS_ALL
CE_BANK_ACCOUNTS
CE_BANK_BRANCHES
POZ_SUPPLIER_SITES_V
HZ_LOCATIONS
Again appears operational rather than accounting-reconciled.
Payables Trial Balance
Uses:
AP_TRIAL_BALANCES_GT
GL_CODE_COMBINATIONS
which suggests Oracle is precomputing AP liability balances before rendering the report.
Target Design
My ideal report would support parameters such as:
-
Ledger
-
Business Unit
-
Period
-
Liability Account (Natural Account / Segment3)
-
Supplier (optional)
and return:
-
Supplier
-
Invoice Number
-
Invoice Date
-
Payment Number
-
Payment Date
-
Liability Account
-
Journal Source
-
Journal Category
-
Voucher Number
-
Accounted DR
-
Accounted CR
-
Accounted Net
-
Original Invoice Amount
-
Remaining Amount
-
Payment Amount
-
Trial Balance Amount
-
GL Balance
-
Variance
-
Transaction Type
(Transaction Type = Invoice / Payment / Adjustment / Other)
Technical Question
Has anyone successfully built a Fusion AP-to-GL reconciliation report that:
-
Reconciles back to GL Trial Balance
-
Provides invoice-level visibility
-
Provides payment-level visibility
-
Avoids SLA fan-out issues
-
Uses GL + SLA as the accounting source of truth
If yes:
-
What was your driving table?
-
Did you start from GL_JE_LINES or AP_TRIAL_BALANCES_GT?
-
How did you handle XLA_DISTRIBUTION_LINKS without duplicating rows?
-
Did you use GL_IMPORT_REFERENCES or another SLA linkage?
-
Any known pitfalls around AP liability accounting in Fusion?
I’m particularly interested in proven SQL patterns or architecture approaches that successfully reconcile:
Opening AP TB
+ Invoice Activity
- Payment Activity
=
Closing AP TB
=
GL Liability Balance
Any design guidance, sample SQL patterns, or lessons learned would be greatly appreciated.