Results for “ozf_mc_transactions”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OZF_MC_TRANSACTIONS is a secured, organization-striped view owned by the APPS schema in Oracle Trade Management (product family OZF). It is a reporting and integration artifact rather than a transactional entry point: it exposes the multi-currency transaction staging records that Trade Management accruals and settlement flows generate before those amounts are pushed to General Ledger. Because the view carries posting control identifiers, multi-currency conversion attributes, and the GL_POSTED_DATE column, it functions as the queryable bridge between Trade Management accounting events and the GL interface. Customers and implementers search this object primarily to answer the question "has this Trade Management transaction been posted to the general ledger, and on what date?" — hence the frequent search on gl_posted_date.

Underlying Base Objects

The view is defined over a single base object, OZF_MC_TRANSACTIONS_ALL, referenced in the metadata as a synonym. OZF_MC_TRANSACTIONS_ALL is the actual multi-currency transaction table, and it is partitioned across operating units by the ORG_ID column. The view applies standard Multi-Org access security through a WHERE clause that resolves the current organization from USERENV('CLIENT_INFO') and matches it against the row's ORG_ID, with a fallback of -99. Rows whose ORG_ID does not equal the session's current operating unit are therefore hidden, and rows with a NULL ORG_ID are treated as belonging to the -99 pseudo-organization. The view performs no joins, aggregation, or column transformation; every column is a direct projection of the base table. This means DML against the view is not possible without an INSTEAD-OF trigger, and performance characteristics are inherited directly from OZF_MC_TRANSACTIONS_ALL.

• MC_RECORD_ID — Primary identifier for the multi-currency transaction record.
• SOURCE_OBJECT_NAME / SOURCE_OBJECT_ID — Identify the originating Trade Management entity (for example, an accrual or settlement) that produced the accounting line.
• CURRENCY_CODE, EXCHANGE_RATE_TYPE, EXCHANGE_RATE_DATE, EXCHANGE_RATE — The currency conversion context applied when the transaction was converted to the ledger currency.
• SET_OF_BOOKS_ID / SET_OF_BOOKS_TYPE — The target ledger. In 12.1.1 this is the set of books; in 12.2.2 the same column is retained for ledger identification under the subledger accounting model.
• AMOUNT_COLUMN1 through AMOUNT_COLUMN10 — Generic, flexibly mapped amount buckets whose meaning is determined by the posting setup for the transaction type.
• POSTING_CONTROL_ID — Groups or controls the posting run that processed the record; used to trace a record back to its Create Accounting execution.
• GL_POSTED_DATE — The date the transaction was transferred and posted in General Ledger. A NULL value typically indicates the record has been staged but not yet posted; the search term gl_posted_date almost always targets this column.
• ORG_ID — Operating unit, driving the Multi-Org security filter.
• Attribute1–15 and OBJECT_VERSION_NUMBER — Descriptive flexfield storage and optimistic locking token respectively.

Common Use Cases and Queries

The most common requirement is to determine posting status and to reconcile Trade Management activity to GL balances for a given period and operating unit. A typical query lists unposted records for the current organization, relying on the view's automatic ORG_ID filter:

SELECT mc_record_id, source_object_name, source_object_id,
       currency_code, exchange_rate, gl_posted_date
FROM   ozf_mc_transactions
WHERE  gl_posted_date IS NULL;

A second pattern retrieves all records posted within a period window, which is useful for period-close reconciliation:

SELECT mc_record_id, amount_column1, amount_column2,
       posting_control_id, gl_posted_date
FROM   ozf_mc_transactions
WHERE  gl_posted_date BETWEEN :p_start AND :p_end
ORDER  BY gl_posted_date;

Because the view enforces Multi-Org security, queries run from a PL/SQL or concurrent program context must set CLIENT_INFO (for example, via FND_CLIENT_INFO or MO_GLOBAL) so that the organization filter resolves correctly; otherwise only rows matching the resolved ORG_ID, or the -99 fallback, are returned. Cross-operating-unit reporting should query OZF_MC_TRANSACTIONS_ALL with an explicit ORG_ID predicate rather than relying on the secured view.