Results for “hz_code_assignments_u2”

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

Overview

The HZ_CODE_ASSIGNMENTS table, owned by the AR schema, is an intersection table within the Oracle E-Business Suite Trading Community Architecture (TCA) data model. Its role is to link classification codes defined in FND_LOOKUP_VALUES to parties or other entities stored in the table identified by the OWNER_TABLE_NAME column. A representative example is the association of a classification code representing "databases" with Oracle Corporation. Each row records the assignment of an industrial classification code to an object, with its primary key being CODE_ASSIGNMENT_ID.

From a Data Vault modeling perspective, the heuristic classification of this table is a link. It resolves a many-to-many relationship between classification code definitions and the owning entities (most commonly parties), and it carries descriptive and effectivity attributes such as dates and flags. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

Key Information Stored

The most significant columns in this table are:

• CODE_ASSIGNMENT_ID — the surrogate primary key; a unique identifier (NUMBER(15)) for the assignment of a classification code to an object.
• OWNER_TABLE_NAME — VARCHAR2(30) identifying the table that stores the owner of the class code.
• OWNER_TABLE_ID — the identifier (NUMBER(15)) of the owner of the class code.
• CLASS_CATEGORY — VARCHAR2(30) naming the classification category.
• CLASS_CODE — VARCHAR2(30) holding the classification code itself.
• PRIMARY_FLAG — indicates whether this is the primary class code of a class category for the organization (Y for primary, N otherwise).
• CONTENT_SOURCE_TYPE — VARCHAR2(30) denoting the source of the content (for example, Dun & Bradstreet or user-entered).
• START_DATE_ACTIVE / END_DATE_ACTIVE — the dates between which the class code applies to the organization.
• OWNER_TABLE_KEY_1 through OWNER_TABLE_KEY_5 — additional key segments used to qualify the owner.
• ACTUAL_CONTENT_SOURCE — the resolved content source value used in the business-key index.
• STATUS and OBJECT_VERSION_NUMBER — standard TCA lifecycle and optimistic locking columns.

The documented unique indexes distinguish the surrogate key from business-key candidates. HZ_CODE_ASSIGNMENTS_U1 enforces uniqueness on CODE_ASSIGNMENT_ID alone, while HZ_CODE_ASSIGNMENTS_U2 is a composite unique index spanning OWNER_TABLE_ID, OWNER_TABLE_KEY_1 through OWNER_TABLE_KEY_5, OWNER_TABLE_NAME, ACTUAL_CONTENT_SOURCE, CLASS_CATEGORY, CLASS_CODE, and START_DATE_ACTIVE. The non-unique index HZ_CODE_ASSIGNMENTS_N1 supports lookups on CLASS_CATEGORY and CLASS_CODE. IMPORTANCE_RANKING is documented as no longer used.

Common Use Cases and Queries

Typical scenarios involve retrieving all classification codes assigned to a party, finding parties assigned a particular code, and filtering on active effectivity windows. A representative query pattern follows:

• Retrieve active codes for a party: SELECT class_category, class_code, primary_flag FROM hz_code_assignments WHERE owner_table_name = 'HZ_PARTIES' AND owner_table_id = :party_id AND (end_date_active IS NULL OR end_date_active > SYSDATE);
• Identify the primary code in a category: add AND primary_flag = 'Y' and constrain class_category.
• Find owners of a given code: use the N1 index by filtering on class_category and class_code.
• Reporting on industry classification coverage commonly joins this table to HZ_PARTIES and to the lookup values underlying CLASS_CATEGORY and CLASS_CODE.

Related Objects

• HZ_PARTIES — referenced via OWNER_TABLE_ID (HZ_CODE_ASSIGNMENTS.OWNER_TABLE_ID → HZ_PARTIES), the most common owner entity.
• HZ_CLASS_CATEGORIES — referenced via CLASS_CATEGORY, defining the valid classification categories.
• FND_LOOKUP_VALUES — source of the classification codes linked to owners.
• HZ_IMP_CLASSIFICS_SG — interface table referencing HZ_CODE_ASSIGNMENTS via CODE_ASSIGNMENT_ID, used during import/merge processing.
• HZ_CODE_ASSIGNMENTS_U1 / _U2 / _N1 — supporting unique and non-unique indexes on the table.