Results for “hz_org_profiles_ext”
4 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
The AR.HZ_ORG_PROFILES_EXT_SG table is a staging (interface) table in the Oracle E-Business Suite Trade Management (ETRM) data model. It holds organization profile tracking data used by the profile versioning process to propagate custom extension (flexfield) attribute values from an obsolete organization profile record to its newly created replacement. The table is owned by the AR schema, resides in the APPS_TS_INTERFACE tablespace, and is classified as VALID with FND Design Data registered as AR.HZ_ORG_PROFILES_EXT_SG.
Its role is driven by the HZ: Profile Version Method for Updates profile option. When that option is configured such that updates to an organization profile end-date the existing record and create a new version, the extension data — custom attributes that extend the main organization profile data model — remains attached to the old profile identifier. This staging table captures the pairing of old and new profile identifiers so that the Copy Organization Extensions Data for Profile Versioning concurrent program can migrate the extension rows onto the new profile record. From a heuristic Data Vault modeling perspective, the table behaves as a link table, since it resolves the relationship between two business entities (the old and new organization profile versions) via a composite key rather than storing descriptive attributes about a single entity.
Key Information Stored
The documented physical schema is compact, containing four columns. The most significant are:
- OLD_PROFILE_ID (NUMBER 15) — Identifier of the pre-existing organization profile version whose extension rows require migration.
- NEW_PROFILE_ID (NUMBER 15) — Identifier of the newly created organization profile version that will receive the copied extension data.
- WORK_UNIT_NUMBER (NUMBER) — Worker unit number, used to partition or track the set of profile pairs processed by the concurrent program run.
- STATUS (VARCHAR2 30) — Processing status flag indicating where each old/new pairing stands in the copy cycle.
The surrogate primary key, HZ_ORG_PROFILES_EXT_SG_PK, is a composite key defined on (NEW_PROFILE_ID, OLD_PROFILE_ID). Both of these columns are also the primary business-key candidates, since together they uniquely identify a versioning pair. No separate single-column unique index is documented; the uniqueness of the versioning relationship is enforced entirely through the composite primary key.
Common Use Cases and Queries
The principal use case is monitoring and troubleshooting the Copy Organization Extensions Data for Profile Versioning program. Administrators and technical consultants query this table to confirm which profile pairs are pending, in progress, or failed. For example, to find unprocessed pairs:
SELECT old_profile_id, new_profile_id, status FROM hz_org_profiles_ext_sg WHERE status <> 'COMPLETE';- Correlating a specific version transition:
SELECT * FROM hz_org_profiles_ext_sg WHERE old_profile_id = 123 AND new_profile_id = 456; - Assessing workload per concurrent run:
SELECT work_unit_number, COUNT(*) FROM hz_org_profiles_ext_sg GROUP BY work_unit_number;
Because the table is an interface object, it is not intended for end-user reporting on extensions themselves. Analysts join it to the organization profile and extension tables to verify that copied rows carry the correct ORGANIZATION_PROFILE_ID after the program completes. This validates data integrity following profile versioning, a common audit requirement in regulated environments. Operators should treat this table as transient: rows accumulate as profile updates occur and are consumed or cleared by the program.
Related Objects
The following objects are significant to the operation of this staging table:
- AR.HZ_ORG_PROFILES_EXT — the extension tables holding the actual custom attribute values; the copy program reads rows for the OLD_PROFILE_ID and inserts equivalents for the NEW_PROFILE_ID via the ORGANIZATION_PROFILE_ID column.
- HZ_ORG_PROFILES — parent organization profile entity that provides the profile version identifiers referenced by OLD_PROFILE_ID and NEW_PROFILE_ID.
- HZ_ORG_PROFILES_EXT_SG_PK — the composite primary key constraint enforcing uniqueness of each old/new profile pair.
- Copy Organization Extensions Data for Profile Versioning — the concurrent program that consumes this staging table.
- FND Design Data AR.HZ_ORG_PROFILES_EXT_SG — the registered design metadata entry.
No foreign key relationships are documented for this object; the heuristic classification indicates it functions as a standalone staging entity whose logical dependencies are enforced by application logic rather than database constraints.
-
eTRM - AR Tables and Views 12.2.2
Territory information
-
eTRM - AR Tables and Views 12.1.1
Territory information