Search Results ozf_code_conversions_all_pk

Overview

OZF_CODE_CONVERSIONS_ALL is a Trade Management (OZF) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores conversion mappings between external codes and internal identifiers. It supports claim reasons, products, price lists, and units of measure, and allows each mapping to be scoped to a party, customer account, or site level. This design lets Trade Management resolve incoming partner-supplied values into the correct internal EBS entities during claim processing, promotion settlement, and pricing validation.

From a Data Vault modeling perspective, the table is best characterized as a link. It captures relationships between an external code value and an internal identifier, and anchors those relationships to a party (HZ_PARTIES) and/or customer account (HZ_CUST_ACCOUNTS). The presence of ORG_ID and START_DATE_ACTIVE/END_DATE_ACTIVE also gives it satellite-like, time-bound attributes, but its primary function is associative: binding external identifiers to internal master data within an operating unit context.

Key Information Stored

The surrogate primary key is CODE_CONVERSION_ID, defined through the OZF_CODE_CONVERSIONS_ALL_PK constraint. A secondary unique index, OZF_CODE_CONVERSIONS_ALL_U1, is defined on (CODE_CONVERSION_ID, ORG_ID), which functions as the business-key candidate and enforces uniqueness within an operating unit.

• CODE_CONVERSION_ID — Surrogate primary key; joins to child tables such as OZF_SUPP_CODE_CONVERSIONS_ALL.
• ORG_ID — Operating unit identifier; part of the unique business key.
• PARTY_ID — Foreign key to HZ_PARTIES; scopes the conversion to a trading partner.
• CUST_ACCOUNT_ID — Foreign key to HZ_CUST_ACCOUNTS; scopes the conversion to a customer account.
• CODE_CONVERSION_TYPE — Indicates the domain of the mapping (claim reason, product, price list, or unit of measure).
• EXTERNAL_CODE — The partner-supplied code value that must be translated.
• INTERNAL_CODE — The corresponding internal EBS identifier or code.
• DESCRIPTION — Free-text explanation of the conversion.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Effective-dating window controlling when the mapping is valid.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS for multi-tenant data isolation.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/BC4J framework.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield storage for customer-specific extensions.
• Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — Who/when tracking.

Common Use Cases and Queries

Typical scenarios include validating inbound claim files, resolving partner product codes during claim entry, and building reporting extracts of active conversions per partner. Because the table is effective-dated, queries must filter on SYSDATE between START_DATE_ACTIVE and END_DATE_ACTIVE.

• Resolve an external code for a specific party:

  SELECT INTERNAL_CODE FROM OZF_CODE_CONVERSIONS_ALL WHERE PARTY_ID = :p_party_id AND EXTERNAL_CODE = :p_ext_code AND CODE_CONVERSION_TYPE = :p_type AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE+1) AND ORG_ID = :p_org_id;

• List all conversions for a customer account, joined to HZ_CUST_ACCOUNTS for the account name.
• Report on conversion coverage by type and party to identify missing mappings before a claim cycle.
• Join to OZF_SUPP_CODE_CONVERSIONS_ALL (child) to retrieve supplementary conversion details keyed by CODE_CONVERSION_ID.
• Audit recent changes using LAST_UPDATE_DATE, CREATED_BY, and LAST_UPDATED_BY.

Related Objects

The most significant relationships are documented through the table's foreign keys and its child reference.

• HZ_PARTIES — Referenced by OZF_CODE_CONVERSIONS_ALL.PARTY_ID; supplies the trading partner identity.
• HZ_CUST_ACCOUNTS — Referenced by OZF_CODE_CONVERSIONS_ALL.CUST_ACCOUNT_ID; supplies the customer account scope.
• FND_SECURITY_GROUPS — Referenced by OZF_CODE_CONVERSIONS_ALL.SECURITY_GROUP_ID; enforces multi-tenant isolation.
• OZF_SUPP_CODE_CONVERSIONS_ALL — Child table referencing CODE_CONVERSION_ID; stores supplementary conversion records that depend on the parent.
• OZF_CODE_CONVERSIONS_ALL_PK / OZF_CODE_CONVERSIONS_ALL_U1 — The primary and unique constraints that govern key integrity and join performance.

These relationships make OZF_CODE_CONVERSIONS_ALL a central lookup point when integrating external Trade Management data with internal EBS party, account, and security structures.