Search Results ozf_code_conversions_all_n2

Overview

OZF.OZF_CODE_CONVERSIONS_ALL is a transactional table in the Oracle E-Business Suite Trading Community and Trade Management (OZF) schema that stores cross-reference mappings between external codes and internal identifiers. These conversions apply to claim reasons, products, price lists, and units of measure, scoped at the party, customer account, or site level. The table therefore functions as a code translation registry that allows external systems, third-party data feeds, and legacy sources to present values in their own terminology while EBS resolves them to canonical internal codes.

The table resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, confirming it is treated as transactional, multi-organization data. The presence of ORG_ID confirms it is an operating-unit–aware object, subject to Multi-Org security. From a Data Vault modeling perspective, the metadata's foreign-key structure suggests a link classification (heuristic), since the table records relationships between parties, accounts, and code values rather than serving purely as a descriptive satellite. The 34-column schema includes standard WHO audit columns and a complete Descriptive Flexfield (DFF) attribute1–attribute15 block for extensibility.

Key Information Stored

The surrogate primary key is CODE_CONVERSION_ID, enforced through the primary key constraint OZF_CODE_CONVERSIONS_ALL_PK. The unique index OZF_CODE_CONVERSIONS_ALL_U1 spans (CODE_CONVERSION_ID, ORG_ID), representing the documented business-key candidate and reinforcing the multi-org tenancy of the record. The most significant columns include:

• CODE_CONVERSION_ID — surrogate identifier and primary key for each conversion record.
• ORG_ID — operating unit identifier; participates in the unique index and Multi-Org filtering.
• CODE_CONVERSION_TYPE — discriminator indicating which entity the mapping applies to (claim reason, product, price list, or UOM).
• EXTERNAL_CODE and INTERNAL_CODE — the two sides of the translation; EXTERNAL_CODE holds the source value, INTERNAL_CODE the EBS canonical value.
• PARTY_ID and CUST_ACCOUNT_ID — optional scope qualifiers that limit a mapping to a specific party or customer account; when null, the mapping applies more broadly.
• START_DATE_ACTIVE and END_DATE_ACTIVE — effective dating for time-bound mappings.
• DESCRIPTION — free-text explanation of the conversion.
• OBJECT_VERSION_NUMBER — optimistic locking control used by the HTML (OA Framework) UI.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive Flexfield segments for customer-specific extensions.
• SECURITY_GROUP_ID — references FND_SECURITY_GROUPS and supports function/row-level security and data sharing across organizations.
• Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — audit traceability.

Common Use Cases and Queries

Typical scenarios include resolving an inbound external claim-reason or product code to its internal EBS identifier, generating cross-reference reports, and diagnosing translation failures during interface loads. A common lookup query filters by type and external value:

• SELECT internal_code FROM ozf.ozf_code_conversions_all WHERE code_conversion_type = :p_type AND external_code = :p_ext AND org_id = :p_org AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
• Party-scoped resolution joining PARTY_ID to HZ_PARTIES to confirm the mapping target.
• Reporting active versus expired mappings using START_DATE_ACTIVE / END_DATE_ACTIVE.
• Reverse lookup (internal to external) using the OZF_CODE_CONVERSIONS_ALL_N2 index on (CODE_CONVERSION_TYPE, INTERNAL_CODE).
• Multi-org reporting filtered by ORG_ID for a specific operating unit.

Index N1 supports party-based retrieval, N2 supports internal-code lookups, and N3 supports external-code lookups — all keyed on CODE_CONVERSION_TYPE, so queries should always include that column to benefit from indexing.

Related Objects

• HZ_CUST_ACCOUNTS — joined via CUST_ACCOUNT_ID to scope mappings to customer accounts.
• HZ_PARTIES — joined via PARTY_ID for party-level mappings.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for security-group scoped data.
• OZF_SUPP_CODE_CONVERSIONS_ALL — supplementary conversions table with FK CODE_CONVERSION_ID referencing this table; extends the mapping model.
• OZF_CODE_CONVERSIONS_ALL_U1 / _N1 / _N2 / _N3 — indexes supporting uniqueness and access paths.