Results for “ozf_settlement_docs_u1”

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

Overview

OZF_SETTLEMENT_DOCS_ALL is a transactional table in the Oracle Trade Management (OZF) schema that stores settlement document details for utilization records. Its primary purpose is to capture the financial settlement of trade management claims and accruals by recording documents generated or referenced across Oracle Receivables (AR), Oracle Payables (AP), and Oracle General Ledger (GL), depending on the settlement type. Each row represents a single settlement document associated with a parent settlement, linking trade promotion activity to the actual accounting and payment transactions that discharge it. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and is controlled through standard Oracle EBS WHO columns, indicating it is an operational, auditable transaction table rather than a setup entity.

Mined from its foreign key topology, the table exhibits a standalone profile in Data Vault terms. As a modeling suggestion, it could be treated as a satellite attached to a settlement hub or link, since it holds descriptive and measurable attributes (amounts, dates, statuses) referencing settlements and claims rather than serving as a pure hub or a pure junction between two business keys.

Key Information Stored

The table contains 53 documented columns. The surrogate primary key is SETTLEMENT_DOC_ID, enforced by the unique index OZF_SETTLEMENT_DOCS_U1 in APPS_TS_TX_IDX. While SETTLEMENT_DOC_ID is also the only unique index column and therefore the sole documented business-key candidate, in practice the natural business identity of a settlement document is derived from its associated SETTLEMENT_ID, SETTLEMENT_NUMBER, and SETTLEMENT_TYPE rather than the surrogate.

The most significant columns include:

• SETTLEMENT_DOC_ID — surrogate unique identifier and primary key.
• SETTLEMENT_ID — the parent settlement identifier; non-unique index OZF_SETTLEMENT_DOCS_N1 supports lookups.
• SETTLEMENT_TYPE — classifies the settlement (for example AR, AP, or GL oriented); indexed by OZF_SETTLEMENT_DOCS_N2.
• SETTLEMENT_NUMBER and SETTLEMENT_DATE — human-readable number and effective date of the settlement.
• SETTLEMENT_AMOUNT and SETTLEMENT_ACCTD_AMOUNT — entered and accounted settlement values.
• STATUS_CODE and PAYMENT_STATUS — workflow and payment state indicators.
• INVOICE_PAYMENT_ID — AP payment reference, foreign key to AP_INVOICE_PAYMENTS_ALL.
• CHECK_ID, CHECK_NUMBER, CHECK_DATE, VOUCHER_ID, VOUCHER_NUMBER, and PAYMENT_METHOD — AP disbursement detail attributes.
• GL_DATE — accounting date carried into General Ledger.
• CLAIM_ID and CLAIM_LINE_ID — linkage back to the originating claim and claim line; CLAIM_ID is indexed by OZF_SETTLEMENT_DOCS_N3.
• ORG_ID and SECURITY_GROUP_ID — multi-org and security context.
• OBJECT_VERSION_NUMBER — optimistic locking control for HTML-based UI operations.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — descriptive flexfield structure and segments.

Common Use Cases and Queries

Typical usage centers on settlement reconciliation, claims reporting, and financial audit. A common reporting pattern joins settlement documents to their claims via CLAIM_ID to trace paid amounts back to trade promotion activity:

• Retrieving all settlement documents for a given settlement: SELECT * FROM OZF.OZF_SETTLEMENT_DOCS_ALL WHERE SETTLEMENT_ID = :p_settlement_id;
• Filtering by settlement type for AR/AP/GL segmentation: SELECT SETTLEMENT_DOC_ID, SETTLEMENT_NUMBER, SETTLEMENT_AMOUNT, STATUS_CODE FROM OZF.OZF_SETTLEMENT_DOCS_ALL WHERE SETTLEMENT_TYPE = 'AP'; This benefits from index OZF_SETTLEMENT_DOCS_N2.
• Claim-level audit joining to claims: SELECT d.SETTLEMENT_NUMBER, c.CLAIM_NUMBER FROM OZF.OZF_SETTLEMENT_DOCS_ALL d JOIN OZF.OZF_CLAIMS_ALL c ON d.CLAIM_ID = c.CLAIM_ID;
• Payment reconciliation linking settlements to AP payments through INVOICE_PAYMENT_ID to AP_INVOICE_PAYMENTS_ALL.
• Downstream analysis of funds actually paid by joining to OZF_FUNDS_PAID_ALL on SETTLEMENT_DOC_ID.

Because the table carries ORG_ID and SECURITY_GROUP_ID, queries should always apply the appropriate organizational and security predicates to respect multi-org access controls.

Related Objects

The table participates in a well-defined relationship network:

• OZF_CLAIMS_ALL — referenced via CLAIM_ID; links settlement documents to the originating claim.
• OZF_CLAIM_LINES_ALL — referenced via CLAIM_LINE_ID; provides line-level claim detail.
• AP_INVOICE_PAYMENTS_ALL — referenced via INVOICE_PAYMENT_ID; connects to AP payment records.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; enforces data security.
• OZF_FUNDS_PAID_ALL — references this table through SETTLEMENT_DOC_ID (as SETTLEMENT_DOC_ID is the child column on OZF_FUNDS_PAID_ALL), capturing funds actually disbursed against each settlement document.

These joins establish OZF_SETTLEMENT_DOCS_ALL as the central financial record bridging trade management claims with AR, AP, and GL accounting outcomes within Oracle E-Business Suite 12.1.1 and 12.2.2.