How to Sum JD Edwards Actuals Without Adding the Labor Hours to the Dollars

Query JD Edwards data with AI against a replicated General Accounting schema and the first finance question anyone asks, what did this cost centre spend this year, comes back too big. Not because a join is wrong. The F0902 balances table holds one row per account per ledger type, the same twelve period columns carry actual dollars on one row and labor hours on the next, and a two-letter ledger type code is the only thing that separates them.

A controller asks what the maintenance cost centre has spent year to date.

Point an AI agent at the landed schema and it finds the answer in seconds. F0902 is the Account Balances table. It has twelve period columns and a fiscal year. Sum the periods, filter the year, group by account.

The query compiles. The number comes back one clean row per account, in the right ballpark, a bit higher than the trial balance the controller has open in the other window.

It includes the budget. And the labor hours.

Before you start

• JD Edwards already landed in a warehouse. There is no application-level JD Edwards connector; Fivetran's catalogue has Oracle and SQL Server and no JDE entry, and that is the route. EnterpriseOne runs on Oracle Database or SQL Server, every application table is an ordinary table in a schema, conventionally PRODDTA, and you replicate the database rather than the application. There is no JD Edwards API scope to request. This post is written for the team that has done that.
• There is no free instance, and the fallback isn't free. Oracle publishes JD Edwards EnterpriseOne Trial Edition as an image for Oracle Cloud Infrastructure. It deploys on "a single Oracle Cloud Infrastructure (OCI) Compute instance", is "for demonstration and training purposes only", and "contains only the Pristine (PS920) environment". So a reader without a JD Edwards estate can get one to poke at, on a metered OCI tenancy, and everything below still runs against your own replicated schema.
• Nothing lands with descriptions. No connector-published ERD, no dbt source package, no curated column names. A JD Edwards column is a two-character table prefix plus a data-item alias: F0902 uses GB, so its ledger type is GBLT and its account id is GBAID; F0901 uses GM, so the same account id there is GMAID. Oracle's field-level dictionary is inside the product, in Data Dictionary (P92001), not on the public web.
• Casing varies by extract tool. Oracle sources are uppercase (PRODDTA.F0902, GBLT). Snowflake destinations usually preserve that, others lower-case, dbt layers rename. This post writes SQL uppercase to match the source. Adjust to whatever your destination did.
• Select the five General Accounting tables rather than syncing the schema. F0902, F0911, F0901, F0006, and F0025 answer most questions this post is about, and syncing thousands of two-letter-prefixed tables with no descriptions is a warehouse nobody can read.

The question

"What did the maintenance cost centre spend year to date?"

It is the first line of every variance conversation. Actual against budget, this year against last, one business unit against another. Finance has been answering it out of JD Edwards for as long as the company has run JD Edwards, and the controller already has this month's trial balance to compare against.

What breaks

Here is the query, and it is the first thing anyone writes:

SELECT b.GBMCU  AS business_unit,
       b.GBOBJ  AS object_account,
       SUM(b.GBAN01 + b.GBAN02 + b.GBAN03 + b.GBAN04 + b.GBAN05 + b.GBAN06
         + b.GBAN07 + b.GBAN08 + b.GBAN09 + b.GBAN10 + b.GBAN11 + b.GBAN12)
         AS year_to_date
FROM   PRODDTA.F0902 b
WHERE  b.GBCTRY = 20
  AND  b.GBFY   = 26
GROUP  BY b.GBMCU, b.GBOBJ;

Nothing about it is careless. Oracle documents F0902 as the table that "stores account balances including net postings for each period", the fiscal year is split across a century column and a two-digit year column (so fiscal 2026 is GBCTRY = 20 and GBFY = 26; a single four-digit literal matches nothing), and the twelve period columns are where the postings live. The query returns exactly one row per account, which is what a balance query should return.

It adds the budget ledger to the actuals ledger. Then it adds the labor hours to both.

There is no row-count symptom. The WHERE clause is correct. Every column is a number. The total is wrong by whatever the budget and the hours happen to sum to, and the only way to notice is to already know the right answer.

The same account, the same fiscal year, the same twelve columns, four rows. Oracle's own illustration of the AA and AU ledgers puts 100.00 beside 5: a hundred dollars on one row, five units on the next. A sum across rows adds them.

Why it breaks

Oracle states the mechanism directly, in the General Accounting setup documentation:

"You define the ledgers that you maintain in the general ledger in UDC table 09/LT. Ledgers contain management and control information for: Statistics or units, Budgets, Forecasts, Accrual basis amounts, Cash basis amounts."

The four it names are AA (actual amounts), AU (actual units), BA (budget amounts), BU (budget units), and then: "Ledger types U1–U9 and UA–UZ are provided for your business needs."

And the sentence that makes it a warehouse problem rather than a bookkeeping footnote:

"When you post a journal entry that has units, the system creates a separate F0902 record that it uses to record the units in the units ledger that is associated with the amount ledger. For example, the system posts the amounts to the AA ledger and posts units to the AU ledger."

Read that as a schema and three things follow.

F0902 is one row per account per ledger, and the ledgers aren't all money. Oracle's own grain sentence, from the tables appendix, names it second: "One record exists for each account/ledger type/subledger/subledger type/fiscal year and, if you post by currency, transaction currency." Filter one account and one year and you still have one row per ledger the installation maintains.

Units aren't in a side table. They're in the same columns. The posting process manufactures an extra F0902 row for the units and writes the quantity into GBAN01 through GBAN14, the same columns that hold dollars on the AA row. Nothing about the column name, the column type, or the table name says so. GBAN01 is a number on every row.

The set of ledgers is per installation. Oracle reserves 35 codes for the customer's own use. F0025, the Ledger Type Master, holds each ledger's rules and its comparison column titles, not what the business means by U1. Somebody has to say.

One table, one two-letter column

This is the same shape as Workday's type column, where one table holds several populations and a single column says which rows a question means. It is the opposite of NetSuite's multi-book, where the second set of books is a separate table you have to join to, so the symptom is duplicate rows and a row count that looks wrong. JD Edwards keeps every ledger in one table and one set of columns. There is no duplicate row and no row count to notice. The actuals, the budget, and the labor hours arrive as one clean row each per account, and the only symptom is a total that is too big.

An AI agent reading the schema reasonably concludes that F0902 holds account balances, that GBAN01 is January's posting, and that summing them for a year gives the year. Each conclusion is true of the AA row. None is true of the table.

What JD Edwards did for you

Inside JD Edwards you never chose a ledger, because the program chose it for you.

Oracle documents the default on the trial-balance programs in the processing options. On T/B by Company (P09216): "Specify a ledger type from UDC table 09/LT to use when displaying records. If you leave this processing option blank, the system uses ledger type AA." On Trial Balance / Ledger Comparison (P09210A), the second ledger defaults the same way: "If you leave this processing option blank, the system uses ledger type AA." On Account Ledger Inquiry: "The program uses AA as the default ledger type."

And the programs read the table you now have. Oracle's 9.2 guide: "Trial balance reports use information from the F0902 table."

So for as long as anyone has run finance out of JD Edwards, GBLT = 'AA' has been applied by the program, silently, as a default nobody had to know about. It is configuration on the application. Replication copied F0902. It did not copy P09216's processing options. The table arrives in the warehouse with the filter removed and no trace that one was ever there.

This is the same move as Salesforce's "Opportunities with Products" report type holding the line-item grain, and as the ServiceNow inheritance that never reaches the warehouse: the application held the rule, and only the tables were replicated.

The fix

None of this is a query problem. Adding AND b.GBLT = 'AA' fixes one query for one person on one afternoon, and the next person to ask about spend starts from the same schema and reaches the same wrong number by the same correct route.

It belongs in the semantic model, where the ledger is declared once and every monetary question inherits it.

The joins go in first, and each one carries a citation rather than a confidence, because none of them is a foreign key in the replicated schema. JD Edwards enforces referential integrity in the application layer, which is exactly why Oracle ships integrity reports: R09705 exists to find rows where F0902 and F0911 disagree, which is only necessary because nothing in the database prevents it.

# subject_areas/general_accounting/relationships.yaml
relationships:
  - from_table: F0902
    from_column: GBAID
    to_table: F0901
    to_column: GMAID
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Account balances to account master, on the internal account id. The
      columns share no name and no constraint, so no foreign-key inference
      tool will find this join. F0902 holds one row per account per ledger
      type per subledger per subledger type per fiscal year, so the
      many_to_one holds only once GBLT is constrained; without it the same
      account appears once per ledger.
    source: https://docs.oracle.com/cd/E15156_01/e1apps90pbr0/eng/psbooks/1aga/htm/1aga44.htm

  - from_table: F0901
    from_column: GMMCU
    to_table: F0006
    to_column: MCMCU
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Account master to business unit master. F0006 carries the company and
      the category codes that most cost-centre reporting groups by.
    source: https://docs.oracle.com/cd/E15156_01/e1apps90pbr0/eng/psbooks/1aga/htm/1aga44.htm

Then the part that carries the weight. The schema can't say which rows are money, so the model has to:

entities:
  - name: jde_ledger_type
    description: >
      Which set of books an F0902 row belongs to, and therefore what unit its
      period columns are denominated in. Oracle defines the installation's
      ledgers in UDC table 09/LT and names four: AA actual amounts, AU actual
      units, BA budget amounts, BU budget units. Installations add their own
      in the ranges U1-U9 and UA-UZ. AA and BA are money. AU and BU are
      quantities posted into the SAME GBAN01..GBAN14 columns, because the
      post process creates a separate F0902 record for units rather than a
      separate table. No column name, column type or table name
      distinguishes them.
    resolves_to:
      table: F0902
      column: GBLT
      default_for_money: "AA"
      description_source: F0025
    caveats:
      - >
        Every monetary metric must constrain GBLT. This cannot be inferred
        from the data: an installation with a large BA ledger and a small AA
        ledger looks exactly like one with a large AA ledger, because the rows
        are the same shape.
      - >
        AA is the right default because JD Edwards itself uses it. Oracle
        documents P09216 and P09210A as falling back to ledger type AA when
        the processing option is left blank. Cite that rather than asserting
        a convention.
      - >
        AU and BU are NOT money and must never be summed into a currency
        measure, formatted as currency, or compared to one.
      - >
        U1-U9 and UA-UZ are per installation. A person has to say what their
        own custom ledgers hold; F0025 gives the rules and the column titles,
        not the business meaning.
    source: https://docs.oracle.com/cd/E15156_01/e1apps90pbr0/eng/psbooks/1aga/htm/1aga04.htm

And the metric binds to the entity rather than to the table, with the ledger as a required filter:

metrics:
  - name: jde_actual_ytd
    calculation: >
      Year-to-date actual amounts by account, from the posted balances table,
      with the ledger constrained to AA so that budget amounts and unit
      quantities are excluded. Periods 13 and 14 are adjusting periods and
      belong in a full-year figure, not in a monthly trend.
    requires_entity: jde_ledger_type
    required_filters: [GBLT]
    source_tables: [F0902]
    primary_table: F0902
    other_names: [actuals, actual spend, year to date, ytd]
    citation: >
      Oracle, Setting Up Ledger Types for General Accounting, and the P09216
      processing option that defaults the ledger to AA. The entity supplies
      the ledger, which is the part the schema cannot express.

required_filters: [GBLT] is the load-bearing line. It says that reaching F0902 for a currency amount without naming a ledger isn't a stylistic choice; it is a number the model has already declared invalid.

Four decisions in here aren't inferable from the schema, and a person makes them once.

What the custom ledgers hold. Oracle reserves U1–U9 and UA–UZ for the customer, so an installation's ledger set is site-specific by design. F0025 says whether U1 must balance and what its comparison column is titled. It does not say that U1 is the statutory ledger for the German subsidiary. Somebody has to.

Whether "spend" means AA alone. Cash-basis, accrual-basis, and forecast ledgers are all in Oracle's own list of what ledgers carry. Finance means one of them, and the model has to be told which.

The fiscal date pattern. Which calendar month period 1 maps to is a company-level setting, not a property of F0902, and every month-over-month chart depends on it.

Whether to read F0902 or F0911. F0902 is the posted aggregate. F0911 is the transaction detail and can hold unposted rows. Oracle ships R09705 because the two can disagree. Which one a given question should use is a policy call, not a schema fact.

Reproduce it yourself

This runs against your own warehouse, which is the only route available for JD Edwards.

1. Run the ledger-split query. It says how exposed you are before you change anything.
2. Run the wrong query from the top of this post. Keep the output.
3. Run the corrected query and compare both against the trial balance for one business unit.
4. Declare the relationships, the entity, and the metric, then ask the question in plain English in the assistant your team already opens, and compare against the number the controller reports.

The ledger-split query. Run this first. It uses only GBLT, GBCTRY, GBFY, and the period columns:

SELECT b.GBLT                       AS ledger_type,
       COUNT(*)                     AS rows_in_ledger,
       SUM(b.GBAN01 + b.GBAN02 + b.GBAN03 + b.GBAN04 + b.GBAN05 + b.GBAN06
         + b.GBAN07 + b.GBAN08 + b.GBAN09 + b.GBAN10 + b.GBAN11 + b.GBAN12)
                                    AS summed_periods
FROM   PRODDTA.F0902 b
WHERE  b.GBCTRY = 20
  AND  b.GBFY   = 26
GROUP  BY b.GBLT
ORDER  BY rows_in_ledger DESC;

Every row that comes back other than AA is a row the naive query was adding in. The AU line is the one to look at first, because it is denominated in hours or pieces, so its contribution to a dollar total isn't merely wrong but meaningless. summed_periods on that line is your own number: the quantity of labor hours your spend report has been calling dollars.

The corrected query. The predicate is the entire fix:

SELECT b.GBMCU  AS business_unit,
       b.GBOBJ  AS object_account,
       SUM(b.GBAN01 + b.GBAN02 + b.GBAN03 + b.GBAN04 + b.GBAN05 + b.GBAN06
         + b.GBAN07 + b.GBAN08 + b.GBAN09 + b.GBAN10 + b.GBAN11 + b.GBAN12)
         AS actual_year_to_date
FROM   PRODDTA.F0902 b
WHERE  b.GBCTRY = 20
  AND  b.GBFY   = 26
  AND  b.GBLT   = 'AA'
GROUP  BY b.GBMCU, b.GBOBJ;

Read 'AA' as the documented default, not as the final answer. If finance reports on a cash-basis ledger, the entity is where that gets written once.

Two things to confirm before you paste. The GBAN01 to GBAN14 alias names and the GBCTRY / GBFY split are corroborated by practitioners and consistent with Oracle's prose, but Oracle's column-level dictionary is inside the product rather than on the public web, so check them against your own F0902 first. And if your destination lower-cased the schema, the identifiers above need the same treatment.

What this looks like in Agami

Everything above holds whoever builds the model. Here is what it is in our product, in the terms this post has used.

The joins are declared with a source, not inferred from a constraint. Introspection reads the tables, the columns, the keys, and how tables join from the warehouse itself. On PRODDTA that finds the structure and stops there, because GBAID = GMAID is not a foreign key and no catalogue says it is. The relationship goes in as readable YAML in your repo with Oracle's page as its citation, and a validator blocks any write that would break the model.

The ledger type is drafted as an entity, and a person approves it. Descriptions, entities, and metric definitions are drafted from the schema, and then your team approves them, reversibly. jde_ledger_type above is that step: AA as the documented default, AU and BU marked as never-money, and the custom U1–UZ ledgers left for a person to name. required_filters: [GBLT] means no currency metric can reach F0902 without it.

Actuals ship once they match the trial balance. Reconciliation takes a screenshot of the T/B by Company screen, a CSV export, or numbers pasted into chat, and compares at a one percent tolerance by default. A mismatch opens the SQL so you can see why. On this schema the mismatch is the BA and AU rows, and finding them is the useful output rather than the failure.

Every answer returns with its SQL beside it. Whether GBLT = 'AA' was applied is visible on the answer, not taken on faith, and a reviewer who knows the ledger set can see a units ledger being summed before the controller does.

A validated question becomes a golden test. Once year-to-date actuals agree with the trial balance for one business unit, that question and its answer are saved, and a change to the model that breaks it is not promoted.

And the number we don't have. We haven't run this on a JD Edwards estate, so there is no share of a real F0902 that isn't AA, no size of the error, and no figure of ours in this post. The ledger-split query returns yours. And the model can only apply the ledger a person named: it records that U1 is the statutory ledger, it does not discover it.

Frequently asked questions

Why is my JD Edwards year-to-date spend from the warehouse higher than the trial balance?

Almost certainly because the query sums F0902 period columns without filtering GBLT. F0902 holds one row per account per ledger type, and Oracle posts budget amounts (BA), actual units (AU), and budget units (BU) into the same GBAN01 to GBAN14 columns as actual amounts (AA), on separate rows. A sum across rows adds the budget and the units to the actuals. Inside JD Edwards the trial-balance programs default the ledger to AA; the warehouse has no default.

What does the GBLT column in F0902 mean?

The ledger type, from UDC table 09/LT. Oracle names AA (actual amounts), AU (actual units), BA (budget amounts), and BU (budget units), and reserves U1–U9 and UA–UZ for the customer's own ledgers. It decides what the period columns on that row are denominated in: money for AA and BA, quantities for AU and BU, and whatever the installation decided for the custom codes. F0025 holds each ledger's rules; it does not hold its business meaning.

Why does JD Edwards store labor hours in the same table as dollars?

Because the posting process creates a separate F0902 record for units rather than a separate table. Oracle: "when you post a journal entry that has units, the system creates a separate F0902 record that it uses to record the units in the units ledger that is associated with the amount ledger." The AU row and the AA row for the same account have the same columns; only the ledger type differs.

Should the semantic model default to the AA ledger?

Yes, and cite why. Oracle documents that T/B by Company (P09216) and Trial Balance / Ledger Comparison (P09210A) use ledger type AA when the processing option is left blank, so AA is the application's own default, not a convention. Then record separately which ledger finance actually reports on, because cash-basis and accrual-basis ledgers are also in Oracle's list and some companies report on one of those.

Does this apply to F0911 as well?

Differently. F0911 is the Account Ledger, one row per transaction, and Oracle notes that "when you use multiple currencies, two records exist for each transaction", which is its own trap. Units on an unposted journal entry are stored in a field on the F0911 row; it is the post process that separates them into an F0902 units row. Which table a question should read is a policy decision, and Oracle ships the R09705 integrity report precisely because the two can disagree.

References

1. Setting Up User-Defined Codes for General Accounting, Oracle JD Edwards EnterpriseOne 9.0 General Accounting. UDC table 09/LT, the ledger list, the four named ledger types, the U1–U9 and UA–UZ ranges, and the sentence on the separate F0902 record for units. Plain HTML, readable in full.
2. Appendix: General Accounting Tables, Oracle. The grain of F0901, F0902, F0911, F0006, and F0025, quoted verbatim.
3. Reviewing Trial Balances, Ledgers, and Subledgers, Oracle. The P09216, P09210A, and Account Ledger Inquiry processing-option defaults to ledger type AA.
4. Example: Ledger Types, Oracle JD Edwards EnterpriseOne 9.2 General Accounting. Oracle's own illustration of F0902 separating AA and AU rows for one fiscal year.
5. Trial Balance Reports, Oracle 9.2. "Trial balance reports use information from the F0902 table."
6. Compare Account Balances To Transactions Report (R09705), Oracle 9.2. The integrity report that exists because F0902 and F0911 can disagree.
7. JD Edwards EnterpriseOne Trial Edition overview, Oracle. Scope, purpose, and the Pristine environment.
8. Oracle connector and SQL Server connector, Fivetran. The database route, since there is no JD Edwards application connector.
9. agami-core on GitHub

Make your JD Edwards data answerable

Agami is the semantic layer between your AI assistant and your warehouse. It declares which ledger a currency question means, so an agent returns a governed year-to-date or says why it cannot.

Start a free trial or talk to us →