Project Centric Customer with budgetary control: Update expense report code combination based on project when project details are in chart of accounts.

Problem Statement

As per intended design Oracle Fusion Expenses does not perform sub-ledger accounting or transaction‑level account derivation within the Expenses module itself. Instead, accounting is deferred to Oracle Fusion Accounts Payable, where expense reports are interfaced as invoices. At the time of expense submission and approval, a default GL account combination—not fully enriched with project accounting attributes such as Project, Task, Expenditure Organization, and Expense Type—is used for budgetary control validation.

This default accounting string is budget‑checked before the final GL distribution is derived. The complete project‑based accounting logic is applied later, during Subledger Accounting (SLA) processing at posting, when project attributes are used to generate the actual ledger entries. As a result, the account combination used for budget consumption differs from the account combination ultimately posted to the General Ledger.

This sequencing creates a structural misalignment between budgetary control (commitment and consumption) and actual accounting, leading to GL string mismatches between project budgets and posted expenses. The outcome is inaccurate budget tracking, failed or misleading budget validations, reconciliation challenges between Projects and General Ledger, and increased dependency on manual corrections. This issue stems from the architectural separation between expense capture, budget checking, and final accounting derivation within Oracle Fusion’s Expenses and Payables design. This introduces a critical challenge for organizations with project-centric financial models with encumbrance and budgetary control on GL string.

The specific use case is applicable when

• Project is implemented
• Project is part of chart of account structure
• Budgetary control is enabled only on chart of accounts.

*Note: TAD (Transaction Account Definition) in Accounts Payable designed to work only for invoices entered directly in accounts payable not designed to work on payment requests interfaced from expense module.

Solution

Bolt-on solution in Oracle Integration Cloud (OIC) to derive and update GL string on expense report, below is the business process flow and the visual diagram:

 

 

Process Flow Description

1. Expense submission (Employee)
   The employee submits an expense report in Oracle Fusion Expenses, capturing required attributes such as Project, Task, Expenditure Organization, and Expense Type. In case of expense that is charged against a project the accounting is governed by project owning organization and the expenditure type. * Note that usually the natural account is derived by expense type and primary balancing segment and cost center is derived from the employee default expense account. Navigation: Setup and Maintenance->Search for all setups tasks (Manage Users).  
•   Expense type are mapped to project expenditure type in expense report template setup as shown below. Navigation: Setup and Maintenance-> Expenses->Expenses-> Manage Expense Report Templates
2. Service account as first approver
   The Expense workflow is configured so that all expense reports are routed first to a service account approver. This approver acts as a system‑controlled gate and prevents the expense from progressing before GL correction is applied.
3. OIC Expense Data Extraction
   Oracle Integration Cloud (OIC) retrieves approved‑pending expense reports using Fusion Expenses REST APIs.
4. GL String Derivation in OIC
   OIC applies custom derivation logic (e.g., Project, Task, Expenditure Organization, and Expense Type) to calculate the correct GL account combination, fully aligned with project costing and accounting rules.
5. OIC calls web service to validate and generate code combination. 
6. GL Distribution Update via REST API
   OIC updates the expense report distributions in Fusion Expenses with the derived correct GL string, replacing the default distribution.
7. Automated Approval by OIC
   Once the GL string is updated successfully, OIC programmatically approves the expense report, allowing the workflow to proceed to the next business approver.
8. Business Approval and Audit
   The expense report completes remaining managerial approvals and auditor review within the Expenses module, now carrying the corrected GL distribution.
9. Transfer to Accounts Payable
   After final approval and audit, the expense report is interfaced to Oracle Fusion Payable as an invoice with the updated GL string.
10. Validation and Budgetary Control
    The invoice is validated and budget‑checked in Payable against the correct GL account combination interfaced from expense module, ensuring alignment between budget consumption and accounting.
11. Posting to General Ledger
    Subledger Accounting posts the invoice to the General Ledger using the same GL string, eliminating mismatch between budgetary control and actual.

 

 

Technical Information

Technical component involved.

•     BI Publisher report that will list all the expense report with pending approval action against the service account. The key information will include the expense_report_Id, expense_Id, Expense_distribution_Id, Itemization_parent_expense_id of parent and child in case of itemization and all the attributes to derive the code combinations. The URL for the report query https://github.com/sohailakht-wq/ExpenseBlog.git 

•       REST API to get the expense item details,

fscmRestApi/resources/11.13.18.05/expenses/{expensesUniqID}

•       REST API to update the distribution on expense report, for parent expense item

/fscmRestApi/resources/11.13.18.05/expenses/{expensesID}/child/ExpenseDistribution/{ExpenseDistId}

 

•      REST API to validate and generate code combination, the service returns the code combination id that will be used in REST API to update the distribution of expense report /fscmRestApi/resources/11.13.18.05/accountCombinations

•     REST API to update the distribution on expense report, for itemized expense items. The details to update the distribution id will be pulled using the BI Publisher report, the URL for the sample query is shared towards the end of the blog.

/fscmRestApi/resources/11.13.18.05/expenses/{parentExpenseId}/child/ExpenseItemization/{ExpenseId}//child/ExpenseItemizationDistribution/{ExpenseDistId}

Sample JSON for payload:

{

"CodeCombinationId": 965949,

"CostCenter": "0374",

"ExpenseDistId": 300000273834331,

"ExpenseId": 300000273834330,

"ExpenseReportId": 300000273834332,

"Company": "002"

}

•       Query to get the actionable workflow task

select ASSIGNEES,

                   ASSIGNEESDISPLAYNAME,

                   TASKNUMBER,

                   WORKFLOWPATTERN,

                   WFPATTERNSORT

FROM

(

SELECT  ASSIGNEES,

                                ASSIGNEESDISPLAYNAME,

                                TASKNUMBER,

                                WORKFLOWPATTERN,

                                DECODE(WORKFLOWPATTERN,'AGGREGATION',1,2) WFPATTERNSORT

FROM FA_FUSION_SOAINFRA.WFTASK WT

WHERE 1=1

AND WT.PROCESSNAME = 'ExpenseApprovalProcess'

AND WT.CATEGORY = 'Expenses'

AND WT.COMPONENTNAME = 'FinExmWorkflowExpenseApproval'

AND WT.STATE = 'ASSIGNED'

AND WT.ASSIGNEESDISPLAYNAME = 'Service Account Name'

AND IDENTIFICATIONKEY = :P_ExpensereportId

AND NOT EXISTS

                (SELECT 1 FROM

                 FA_FUSION_SOAINFRA.WFTASKHISTORY  WH

                 WHERE WH.STATE = 'OUTCOME_UPDATED'

                   AND WH.OUTCOME = 'APPROVE'

                   AND WH.TASKNUMBER = WT.TASKNUMBER

                   AND WH.ASSIGNEESDISPLAYNAME = 'Service Account Name'

                )

ORDER BY TASKNUMBER DESC, WFPATTERNSORT ASC

) WHERE 1=1

AND ROWNUM =1

•       REST API to approve/Reject the expense report based on the TaskNumber from Above. Once the workflow is approved the expense report will move to supervisor approver and start following the normal approver chain as per the approval workflow configuration.

https://xxxx.oraclecloud.com/bpm/api/4.0/tasks/{TaskNumber}

Method to choose will be PUT

Sample JSON for payload:

{

    “action”:{“id”: “APPROVE”}

}

• Below is the URL for sample query for expense report data to be used in OIC. Build the custom solution in OIC to orchestrate the BIP report call and subsequent call of REST API to update the distribution followed by approval of workflow.

URL for the Expense Query: https://github.com/sohailakht-wq/ExpenseBlog.git 

Key outcome

This bolt‑on solution ensures that:

• Budgetary control is executed against the final, project‑accurate GL string
• Actual posted to the General Ledger match budgets in budgetary control.
• Accounts Payable will be able to see the distribution on Invoice same as final accounting string in GL making it consistent with other standard invoices where the GL distribution is derived using transaction account definition in Accounts Payable.
• By aligning the Accounts Payable subledger distribution with the final General Ledger account string, the solution ensures consistent reporting, reduces reconciliation effort, and strengthens audit transparency.