Oracle EBS Secured Every Operating Unit in a Session Variable. Your Warehouse Copied the Table.

Oracle E-Business Suite keeps your legal entities apart with a variable that the security system sets when someone signs in. A view reads that variable and filters the rows. A replication connector never signs in, so it copies the table underneath, which is exactly what Oracle's own documentation tells it to do.

A controller wants one number before the close: invoiced revenue last month.

Query your Oracle EBS data with AI against a replicated warehouse and the schema is welcoming. Receivables transactions sit in ra_customer_trx_all, their lines sit in ra_customer_trx_lines_all, and a customer_trx_id joins the two. Sum the lines, filter the dates, done. Any competent agent writes that query immediately, and so would you.

Both tables also carry a column called org_id. It's an ordinary integer, it has no constraint on it, and nothing in the schema suggests a total is wrong without it.

It returns a number. The number is too big, and every row that went into it is correct.

Before you start

• An Oracle E-Business Suite estate replicated into a warehouse. Fivetran's EBS connector doesn't call an API: "Oracle EBS is built on the Oracle database. Instead of pulling data from its API, we pull directly from the source database." Any log-based replication of the same database reaches the same objects.
• An Enterprise or Business Critical Fivetran plan, which the connector page states as a requirement. Nothing below depends on Fivetran specifically, only on the connection having no application session.
• No free instance to practise on. Oracle's Vision demo database is the right sample estate and it's seeded multi-organisation, but the appliances need an Oracle account, the deployment guides are My Oracle Support documents, and a single-node instance is a large virtual machine. This post runs against the extract your employer already has, or against the non-production instance they already run.
• Expect Oracle's own names, in upper case. A database connector has no destination schema of its own, so you get Oracle's physical objects with Oracle's spelling. Snowflake shows RA_CUSTOMER_TRX_ALL; Postgres and BigQuery destinations may lower-case it. The SQL below uses lower case, so check yours.
• Know which tables were selected. Fivetran's data blocking works at column, table, and schema level, so what landed was somebody's deliberate choice.

The question

"Invoiced revenue last month."

This is the question finance asks of Receivables every period. It sets the revenue line, it feeds the ageing, and it's the first figure anybody checks against the subledger.

It comes out of the warehouse rather than off an EBS screen because it has to sit beside the CRM, the bank feed, and last year's actuals. Nobody wants revenue in one system and everything it gets compared against in another.

What breaks

Here's the query almost anyone writes first, agent or human.

SELECT SUM(l.quantity_invoiced * l.unit_selling_price) AS invoiced
FROM   ra_customer_trx_lines_all l
JOIN   ra_customer_trx_all h
  ON   h.customer_trx_id = l.customer_trx_id
WHERE  h.trx_date >= DATE '2026-08-01'
  AND  h.trx_date <  DATE '2026-09-01';

Nothing about it is careless. The join is right. Oracle documents that AutoInvoice writes one generated value to "RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID, AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID, RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID, and RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID", so headers and lines really do meet on that column. The date filter is right. The amount columns are the documented ones.

The query returns revenue for every operating unit the company runs, added together.

If the business is one legal entity in one country, that's the right answer. If it runs a US entity, a UK entity, and a shared services centre, the controller just got all three as one figure, in whatever currencies they invoiced. Nothing errors. Nothing is null. No row is duplicated. Each contributing row is a real invoice line that really was invoiced last month.

The failure surfaces at the reconciliation, when the number doesn't match the one Receivables shows on screen, and nobody can see why because the SQL is correct.

The same rows, two ways in. Every quotation is verbatim from Oracle's E-Business Suite Concepts guide; the operating-unit names are illustrative.

Why it breaks

The filter that made the application's numbers right was never a column.

The partition is a column. The rule that applies it is not

Oracle states the storage half plainly in its Concepts guide:

Tables that contain Multiple Organizations data can be identified by the suffix "_ALL" in the table name. These tables include a column called ORG_ID, which partitions Multiple Organizations data by organization.

So org_id is on ra_customer_trx_all, on ra_customer_trx_lines_all, and on every other _ALL table you can see in the warehouse. The column landed. Look at it in your destination and it's an ordinary integer, indistinguishable from a type code or a batch id.

The next sentence is where the rule lives:

Every Multiple Organizations table has a corresponding view that partitions the table's data by operating unit. Multiple Organizations views partition data by including a DECODE on the internal variable CLIENT_INFO. This variable is set by the security system to the operating unit designated for the responsibility.

Read that as a warehouse engineer. The predicate isn't stored anywhere. It's computed at query time from a variable, and the variable is set by the login.

Oracle tells the connector to take the unfiltered table

This is the sentence the whole post rests on, and it's a note in Oracle's own guide:

If accessing data from a Multiple Organizations partitioned object when CLIENT.INFO has not been set (for example, from SQL*Plus), you must use the _ALL table, not the view.

A replication connector is precisely such a connection. It has no responsibility, no security system, and no session, so the view would return nothing. Oracle's instruction is to read the _ALL table.

The connector didn't pick the wrong object. It picked the only one that works, and the vendor documented that choice. What it copied was the data without the rule, because the rule was never in the data.

Release 12 moves the boundary, and it's still not copyable

If your estate is on R12 the mechanism has a different name, from the Multiple Organizations Implementation Guide:

Each user can access, process, and report on data only for the operating units assigned to the MO: Operating Unit or MO: Security Profile profile option. The MO: Operating Unit profile option only provides access to one operating unit. The MO: Security Profile provides access to multiple operating units from a single responsibility.

A profile option attached to a responsibility replicates no better than a session variable. Either way the boundary lives with the user, and an extract has no user.

Oracle's own join path is written in view names

This is the detail that makes the trap concrete, and it's an accident of the documentation.

Oracle writes the bill-to customer chain as one equality string:

HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITE.CUST_ACCOUNT_ID and HZ_CUST_ACCT_SITE.CUSTOMER_SITE_ID = HZ_CUST_SITE_USES.CUST_ACCT_SITE_ID and RA_SITE_USES.SITE_USE_CODE = 'BILL_TO'

Look at the object names. HZ_CUST_ACCT_SITE, HZ_CUST_SITE_USES, and RA_SITE_USES carry no _ALL suffix. By Oracle's own rule, the _ALL name is the table and the unsuffixed name is the multi-org view.

The vendor's documented join path is written against objects your warehouse connection can't use. Paste it into your SQL client and it fails, or worse, it finds a leftover object and returns nothing. Resolve those three against your own catalog before you declare any of them, because the table names are guessable and guessing is how a wrong join gets into a model.

What the application did for you

Nobody inside EBS meets this problem, because a user can't reach a second operating unit without being granted it. Oracle enforces the boundary three ways and all three sit outside the data.

Choosing an organisation is the first act of every session, implicitly through a responsibility or explicitly when entering a transaction. The view then rewrites the query, so a user reading RA_CUSTOMER_TRX gets their own operating unit without writing a predicate. And in R12 the profile options decide which units a responsibility can reach at all.

Oracle's Concepts guide puts the principle in five words: "Information is secured by operating unit."

A connector copies tables. Security that lives in a session, a view definition, and a responsibility's profile options has nothing to copy.

The rule and the documentation disagree about which objects exist for you. Both quotations are verbatim from Oracle; the resolved table names are deliberately left blank, because Oracle's pages read this morning don't spell them.

The fix

Put the operating unit back into the query, in both places it belongs.

SELECT SUM(l.quantity_invoiced * l.unit_selling_price) AS invoiced
FROM   ra_customer_trx_lines_all l
JOIN   ra_customer_trx_all h
  ON   h.customer_trx_id = l.customer_trx_id
 AND   h.org_id          = l.org_id
WHERE  h.org_id     = :org_id
  AND  l.line_type  = 'LINE'
  AND  h.trx_date  >= DATE '2026-08-01'
  AND  h.trx_date  <  DATE '2026-09-01';

Three changes, and each one is load-bearing.

org_id is in the WHERE clause because nothing else will put it there. That's the whole trap in one predicate.

org_id is also in the join condition. A header and its lines always belong to the same operating unit, and saying so keeps the partition in the shape of the query rather than only in a filter somebody can drop.

And line_type = 'LINE' keeps tax and freight out of a revenue figure. Oracle's definition is blunt: "Enter 'LINE', 'TAX', 'FREIGHT' or 'CHARGES' to specify the line type for this transaction. (CHARGES refers to finance charges.) You must enter a value in this column." Tax rows and freight rows live in the same table as revenue rows. That's a second trap, and it deserves its own post rather than a paragraph here, but a revenue query has to survive it.

In the semantic model

The SQL above is one analyst getting it right once. Declaring it is how the next question gets it right without anybody remembering.

Nothing here is a foreign key in the destination, because a database connector creates no constraints. Every join is a declaration carrying its source.

relationships:
  - from_table: ra_customer_trx_lines_all
    from_column: customer_trx_id
    to_table: ra_customer_trx_all
    to_column: customer_trx_id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Many lines per transaction header. Oracle documents AutoInvoice writing one
      generated CUSTOMER_TRX_ID to RA_CUSTOMER_TRX_ALL, AR_PAYMENT_SCHEDULES_ALL,
      RA_CUSTOMER_TRX_LINES_ALL and RA_CUST_TRX_LINE_GL_DIST_ALL. Match on org_id
      as well: a header and its lines always share an operating unit, and declaring
      that keeps the partition in the join rather than only in the filter.
    source: https://docs.oracle.com/cd/E18727_01/doc.121/e13512/T447348T383863.htm

  - from_table: ra_cust_trx_line_gl_dist_all
    from_column: customer_trx_id
    to_table: ra_customer_trx_all
    to_column: customer_trx_id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      Accounting distributions per transaction, from the same AutoInvoice sentence.
      Joining distributions and lines to the header in one query fans out. Read one
      or the other.
    source: https://docs.oracle.com/cd/E18727_01/doc.121/e13512/T447348T383863.htm

Then the part carrying the weight. No column type can say that a column is a security boundary, so the model has to say it.

entities:
  - name: ebs_receivables_transaction
    description: >
      One Receivables transaction header in one operating unit. RA_CUSTOMER_TRX_ALL
      is a Multiple Organizations partitioned table. Oracle's rule is that tables
      holding Multiple Organizations data carry the _ALL suffix and "include a column
      called ORG_ID, which partitions Multiple Organizations data by organization".
      Inside the application a user never sees more than the operating units their
      responsibility grants. In the warehouse every operating unit is in this table
      and nothing requires a filter.
    resolves_to:
      table: ra_customer_trx_all
      key: [customer_trx_id]
    required_dimensions:
      - >
        org_id. Every aggregate either filters it to one operating unit or groups by
        it. A total across operating units is a real question, and it is a different
        question from the one Receivables answers on screen.
    forbidden_selectors:
      - >
        Any SUM, COUNT or AVG over this table or its lines with no org_id in the WHERE
        clause and no org_id in the GROUP BY. The figure will be the whole company
        where the business meant one legal entity.
      - >
        Reading an unsuffixed multi-org view name from a warehouse. Oracle: "If
        accessing data from a Multiple Organizations partitioned object when
        CLIENT.INFO has not been set (for example, from SQL*Plus), you must use the
        _ALL table, not the view."
    caveats:
      - >
        Which operating unit the business means is a decision a person records. It is
        not in the data. It was in the responsibility.
      - >
        An operating unit is not a ledger. SET_OF_BOOKS_ID is a second, different cut
        and the two do not have to line up.
    source: https://docs.oracle.com/cd/E18727_01/doc.121/e12841/T120505T120523.htm

  - name: ebs_invoice_revenue_line
    description: >
      A revenue line, as distinct from the tax, freight and finance-charge lines that
      share its table. Oracle's LINE_TYPE takes 'LINE', 'TAX', 'FREIGHT' or 'CHARGES'
      and is mandatory, so a line table with no LINE_TYPE filter is a revenue figure
      with tax in it.
    resolves_to:
      table: ra_customer_trx_lines_all
      key: [customer_trx_line_id]
      selector: "ra_customer_trx_lines_all.line_type = 'LINE'"
    caveats:
      - >
        Tax and freight lines point at the line they belong to through
        LINK_TO_CUST_TRX_LINE_ID, so they are recoverable when a question wants them.
    source: https://docs.oracle.com/cd/E18727_01/doc.121/e13512/T447348T383863.htm

And the metric, which can't be computed without the two filters that make it mean anything.

metrics:
  - name: ebs_invoiced_amount
    calculation: >
      Invoiced revenue for a period, within one operating unit. Reads revenue lines
      only, joins to the header on both customer_trx_id and org_id, and cannot be
      computed without an org_id filter or grouping.
    requires_entity: ebs_receivables_transaction
    source_tables: [ra_customer_trx_all, ra_customer_trx_lines_all]
    primary_table: ra_customer_trx_lines_all
    required_filters: [org_id, line_type]
    other_names: [invoiced, invoiced revenue, billings, AR invoiced amount]
    bindings:
      Oracle: >
        SUM(ra_customer_trx_lines_all.quantity_invoiced
            * ra_customer_trx_lines_all.unit_selling_price)
    confidence: proposed
    review_state: unreviewed
    notes: >
      Not a bookings or a recognised-revenue figure. Credit memos live in the same
      header table and whether they net off is a decision a person makes once, on
      cust_trx_type_id.

required_filters is the line doing the work. A metric that can't be computed without naming an operating unit can't quietly answer a question about all of them.

Reproduce it yourself

You need an EBS extract in a warehouse and a SQL client. There's no free instance, so this runs on your own estate, or on the non-production instance your employer already keeps.

1. Confirm your table names and casing. EBS stores upper case and destinations differ:

SELECT table_name
FROM   information_schema.tables
WHERE  table_name ILIKE 'ra_customer_trx%';

2. Ask whether this applies to you at all. One line:

SELECT COUNT(DISTINCT org_id) AS operating_units FROM ra_customer_trx_all;

A result of 1 means your estate is single-org and this post is a precaution. Anything above 1 means every unfiltered total you've ever run against these tables was a company-wide figure.

3. Measure your own exposure. This is the query worth keeping:

SELECT h.org_id,
       COUNT(DISTINCT h.customer_trx_id)                  AS transactions,
       COUNT(*)                                           AS lines,
       SUM(l.quantity_invoiced * l.unit_selling_price)    AS invoiced,
       MIN(h.trx_date)                                    AS first_trx_date,
       MAX(h.trx_date)                                    AS last_trx_date,
       COUNT(DISTINCT h.invoice_currency_code)            AS currencies
FROM   ra_customer_trx_all h
JOIN   ra_customer_trx_lines_all l
  ON   l.customer_trx_id = h.customer_trx_id
WHERE  l.line_type = 'LINE'
GROUP  BY h.org_id
ORDER  BY invoiced DESC;

It answers four things at once. How many operating units the extract holds, what share of the total each one contributes, which window of dates landed, and whether more than one currency is in play. That last column matters more than it looks: a cross-operating-unit sum is often a cross-currency sum as well, and the second error hides inside the first.

4. Find out what an operating unit is called. Your org_id values are integers, and a name for them isn't established here. Practitioner sources point at HR_OPERATING_UNITS.ORGANIZATION_ID, and no Oracle page read for this post says so. Check your own catalog, confirm the join, then declare it. Until somebody does, every answer names a legal entity by number.

5. Check the tax and freight share. The same reserved trap, on your data:

SELECT line_type, COUNT(*) AS lines
FROM   ra_customer_trx_lines_all
GROUP  BY line_type
ORDER  BY lines DESC;

6. Declare it once. Put the relationships, the entities, and the metric into your semantic model, so the next person who asks for invoiced revenue gets an operating unit in the query without knowing this post exists.

What this looks like in Agami

All of the above holds for whoever builds the semantic model. Here's what it is in our product, in this post's terms.

A column can be declared as a dimension every total has to name. org_id arrives as an ordinary integer with no constraint and no comment. Declaring it a required dimension on the transaction entity means an aggregate either filters it or groups by it, and one that does neither is refused rather than answered. Table and column scope gates run before the SQL does, so that refusal happens before anything reaches the database.

A join can carry the partition as well as the cardinality. The header-to-lines relationship ships with org_id on both sides of the match. The declaration is a line in a file, which makes the rule auditable in a way a prompt never is.

A metric carries the filters it can't be computed without. ebs_invoiced_amount names org_id and line_type as part of its definition, with Oracle's own pages as the source. Dropping either doesn't produce a different view of the same question, it produces a different question.

Fan-out is detected per aggregate before the SQL runs, from declared join cardinality. Joining distributions and lines to the same header inflates a total, and that gets named: which join inflated which number. It isn't blocked, because whether the fan is a bug depends on the question.

Reconciliation is how the number earns trust. Point it at the Receivables figure your finance team already closes on, as a screenshot, a CSV, or numbers pasted into chat. Where ours and theirs disagree, the SQL opens. On this schema the disagreement is usually an operating unit, and finding which one is the useful output.

And what we can't claim. We haven't run this against a real EBS estate, so this page carries no figure for how many operating units a typical company runs, or how far an unfiltered total drifts from the right one. Both depend entirely on how a business is structured, and the discovery query above returns yours. Three things stay open because no Oracle page read for this post settles them: whether org_id joins to HR_OPERATING_UNITS, the table names behind the three multi-org views in Oracle's documented customer path, and how R12 implements the partition underneath the profile options. The semantic model also can't decide which operating unit your business means when it says "revenue". It records that answer once a person gives it.

Frequently asked questions

Why does my Oracle EBS revenue total come out higher than Receivables shows?

Because the query almost certainly has no org_id filter. Oracle partitions Multiple Organizations data by ORG_ID on tables suffixed _ALL, and applies that partition through a view that reads a session variable. A warehouse connection has no session, so it reads the table and gets every operating unit. Add org_id to the WHERE clause, or group by it.

What is ORG_ID in Oracle E-Business Suite?

It's the column that partitions Multiple Organizations data by organization. Oracle's Concepts guide says tables holding that data "can be identified by the suffix '_ALL' in the table name" and that those tables "include a column called ORG_ID". On the AutoInvoice interface Oracle describes it as "the ID of the organization that this transaction belongs to" and says it "is mandatory in a multiple organization environment".

Why did my connector land the _ALL tables instead of the views?

Because Oracle instructs it to. From the Concepts guide: "If accessing data from a Multiple Organizations partitioned object when CLIENT.INFO has not been set (for example, from SQL*Plus), you must use the _ALL table, not the view." The multi-org views filter by DECODEing on a session variable that only the application's security system sets, so they return nothing to a connection that never signed in.

Can I just query the multi-org views from my warehouse instead?

No. The views exist to be read inside a session that has CLIENT_INFO set, and a replication has no such session. That's also why Oracle's own documented bill-to join chain, which is written using HZ_CUST_ACCT_SITE, HZ_CUST_SITE_USES, and RA_SITE_USES, has to be resolved against the tables that actually landed before you can use it.

Does this apply to Release 12, or only to 11i?

Both, in different words. In R12 the boundary is a profile option on the responsibility: "Each user can access, process, and report on data only for the operating units assigned to the MO: Operating Unit or MO: Security Profile profile option." A profile option replicates no better than a session variable does.

Is an operating unit the same thing as a ledger?

No, and treating them as one is a separate way to get a wrong number. SET_OF_BOOKS_ID is an independent cut, and the object it points at changed between releases: Oracle's EPM source-table list marks GL_LEDGERS as R12 only and GL_SETS_OF_BOOKS as 11i. Whether the two boundaries line up in your estate is something to check rather than assume.

References

• Oracle E-Business Suite Concepts, Multiple Organization Architecture. The _ALL and ORG_ID rule, the CLIENT_INFO view mechanism, the instruction to read the table rather than the view, and "Information is secured by operating unit."
• Oracle E-Business Suite Multiple Organizations Implementation Guide. The R12 profile options, MO: Operating Unit and MO: Security Profile.
• Oracle Receivables Reference Guide, AutoInvoice Table and Column Descriptions. ORG_ID, LINE_TYPE, the amount columns, the CUSTOMER_TRX_ID destinations, and the documented bill-to join chain.
• Oracle EPM, Fusion and E-Business Suite source system tables. GL_LEDGERS marked R12 only, GL_SETS_OF_BOOKS marked 11i.
• Fivetran, Oracle E-Business Suite connector. Reads the source database rather than an API, and the Enterprise or Business Critical plan requirement.
• Fivetran, Oracle E-Business Suite setup guide. Prerequisites and connection setup.
• Oracle VM Templates for E-Business Suite. The Vision demo appliances, and the gates in front of them.
• Oracle, data platform reference architecture for EBS analysis. Oracle's own route for analysing EBS data.
• How to Sum JD Edwards Actuals Without Adding the Labor Hours to the Dollars. The nearest Oracle-estate cousin, where a code on the row decides what an amount means.
• In Your Certinia Warehouse, the Table Named Timecard Is Not the Timecard. The same shape, where the rule lived in a formula rather than a column.
• agami-core on GitHub

Make your Oracle EBS data answerable

Agami is the trust layer between your AI assistant and your warehouse. It declares the operating unit as a dimension every total has to name, so invoiced revenue comes back for one legal entity rather than all of them at once.

Start a free trial or talk to us →