Oracle E-Business Suite and Federal Financials: Architecture, Data Model, and Tables

1. What runs on Oracle in DoD

Three systems in this suite run on Oracle E-Business Suite: DAI, DEAMS, and GCSS-MC. DAI and DEAMS use the Oracle U.S. Federal Financials extension for budget execution, Treasury reporting, and prompt payment. CEFMS is a custom Army Corps of Engineers application. It does not use the EBS data model, and the public sources reviewed for this site do not confirm its database product.

2. Technical architecture

Oracle EBS is a three-tier system. A browser or Forms client talks to the application tier. The application tier runs web pages, forms, and batch programs. All business data sits in one Oracle database.

Schemas, editions, and patching

• Each product owns its tables in its own schema: GL, AP, AR, PO, FA, XLA, FV, and so on. All programs connect as APPS, which holds synonyms to every table and the PL/SQL code.
• APPLSYS owns the foundation tables. FND_USER holds users. FND_RESPONSIBILITY holds responsibilities. FND_USER_RESP_GROUPS_DIRECT holds assignments. These support an access review.
• Release 12.2 uses edition-based redefinition. Users work in the run edition while a patch is applied to the patch edition. Cutover swaps both the file system and the edition. The adop utility runs five phases: prepare, apply, finalize, cutover, cleanup.
• Because of editions, queries should read tables through the APPS synonym. Reading the physical table directly can return obsolete columns.

3. Organizational model

Oracle separates who owns the books from who does the transactions. A ledger owns balances. An operating unit owns subledger transactions. An inventory organization owns stock.

The Accounting Flexfield

The chart of accounts is a key flexfield with up to 30 segments. Each program defines its own segments. A federal chart usually has a fund segment as the balancing segment, a natural account segment that holds the USSGL account, and segments for organization, program, object class, and budget year. Every distinct combination that has been used is one row in GL_CODE_COMBINATIONS, identified by CODE_COMBINATION_ID. Every transaction table stores that ID, not the segment values.

4. General Ledger data model

ACTUAL_FLAG separates three kinds of balance in the same tables. A is actual. B is budget. E is encumbrance. In a federal ledger, the USSGL budgetary accounts in the 4000 series are posted as actual journals to budgetary accounts, so a federal trial balance reads ACTUAL_FLAG = A for both proprietary and budgetary accounts.

5. Subledger Accounting: the bridge from transaction to GL

In Release 12 no subledger writes to the GL directly. Each transaction raises an accounting event. The Create Accounting program reads the event, applies the rules in the subledger accounting method, and writes a subledger journal entry in the XLA tables. A second step transfers those entries to the GL. The link IDs written at each step are what allow a drill from a GL balance to the source transaction.

1. Start from a balance in GL_BALANCES. Find the lines in GL_JE_LINES for that CODE_COMBINATION_ID and period.
2. Join GL_JE_LINES to GL_IMPORT_REFERENCES on JE_HEADER_ID and JE_LINE_NUM.
3. Join GL_IMPORT_REFERENCES to XLA_AE_LINES on GL_SL_LINK_ID and GL_SL_LINK_TABLE.
4. Join XLA_AE_LINES to XLA_AE_HEADERS on AE_HEADER_ID, then to XLA_EVENTS and XLA_TRANSACTION_ENTITIES.
5. Read ENTITY_CODE and SOURCE_ID_INT_1. For AP_INVOICES the ID is AP_INVOICES_ALL.INVOICE_ID. For AP_PAYMENTS it is AP_CHECKS_ALL.CHECK_ID. For receiving it is the receiving transaction.
6. Use XLA_DISTRIBUTION_LINKS when the test needs one specific distribution line and not the whole transaction.

Journals with a JE_SOURCE of Manual or Spreadsheet have no rows in GL_IMPORT_REFERENCES. That absence is how an analyst separates subledger-supported postings from manual journals in the universe of transactions.

6. Procure-to-pay document flow

7. Other module data models

8. Oracle U.S. Federal Financials

Federal Financials is an add-on product with its own schema, FV. It adds what a commercial ledger lacks: Treasury Account Symbols, multi-level budget execution, Treasury reporting attributes, prompt payment, Treasury payment confirmation, and federal year-end closing.

How budget execution works

1. An appropriation is entered at the top budget level. The transaction type decides which USSGL budgetary accounts are debited and credited.
2. Funds are distributed down through apportionment, allotment, and any lower levels the agency defined. Each distribution is a document in FV_BE_TRX_HDRS with lines in FV_BE_TRX_DTLS.
3. External budget tools can load the same transactions through FV_BE_INTERFACE. The import validates every record in a group and loads the group only when all records pass.
4. Approved transactions raise accounting events. Create Accounting posts them to the budgetary accounts in the GL.
5. Budgetary control in the GL then checks every requisition, order, and invoice against the allotted balance for its fund and budget segments.

In Release 11i, federal postings were driven by USSGL transaction codes entered on each transaction. In Release 12 that logic moved into Subledger Accounting rules, so the proprietary and budgetary lines are both derived when accounting is created.

9. Interfaces and open interface tables

10. Security and controls

• Access is granted by assigning responsibilities to users. A responsibility fixes the menu of functions, the operating units through MO: Security Profile, and the ledgers through a data access set.
• Segregation of duties is tested at the function level: for example, holding both supplier entry and payment functions. Oracle Advanced Controls or a GRC tool applies the rule set.
• Flexfield security rules limit which segment values a responsibility can use. Cross-validation rules block invalid account combinations at entry.
• Standard WHO columns on every table record CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE. They show the last change only.
• Full history needs AuditTrail, which writes shadow tables for chosen columns, or database auditing.
• Journal approval, purchase order approval, and invoice approval run on Oracle Workflow. The approval hierarchy and limits are configuration and belong in the control documentation.

11. Trial balance and universe of transactions queries

1. Read GL_BALANCES for the ledger and period with ACTUAL_FLAG = A and the ledger currency. Join to GL_CODE_COMBINATIONS for the segments.
2. Compute the ending balance as beginning balance plus period net, debits less credits.
3. Group by fund segment, USSGL account segment, and the other segments that carry SFIS attributes. Join the fund to FV_FUND_PARAMETERS and FV_TREASURY_SYMBOLS to add the TAS.
4. For the universe of transactions, read GL_JE_LINES joined to GL_JE_HEADERS for the same ledger and period with status posted. The sum by account must equal the period net in GL_BALANCES.
5. Extend each line to its source through GL_IMPORT_REFERENCES and the XLA tables as described in section 5.
6. List XLA_EVENTS that are not processed and interface rows that are not imported. Those are transactions that exist in the system and are not yet in the trial balance.