Search Results hz_org_profiles_ext

Overview

The AR.HZ_ORG_PROFILES_EXT_SG table is a staging table within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 architecture, specifically in the Accounts Receivable (AR) module. Its primary role is to support the versioning of organization profiles when the system is configured to create new profile records upon update. The table acts as a temporary holding area for tracking pairs of old and new organization profile identifiers, enabling the subsequent copying of custom extension data from the old profile version to the new one. This process ensures that extended attributes, which represent customizations to the standard TCA (Trading Community Architecture) data model, remain associated with the correct, current profile record after a versioning event.

Key Information Stored

The table's structure is minimal and focused on tracking identifiers and process status. The key columns are:

• OLD_PROFILE_ID (NUMBER): The identifier of the superseded organization profile record from HZ_ORG_PROFILES.
• NEW_PROFILE_ID (NUMBER): The identifier of the newly created organization profile record. Together with OLD_PROFILE_ID, this forms the table's primary key (HZ_ORG_PROFILES_EXT_SG_PK).
• WORK_UNIT_NUMBER (NUMBER): A worker unit identifier used for parallel processing, likely by the concurrent program that manages the data copy.
• STATUS (VARCHAR2): Indicates the processing status of the record (e.g., PENDING, PROCESSED, ERROR) for the copy operation.

The table resides in the APPS_TS_INTERFACE tablespace, which is typical for transient, process-oriented data.

Common Use Cases and Queries

The primary use case is the execution of the "Copy Organization Extensions Data for Profile Versioning" concurrent program. This program reads from this staging table to identify which profile IDs require their extension data to be replicated. Common queries involve monitoring the process. For instance, to check for pending copy operations:

SELECT old_profile_id, new_profile_id, status
FROM ar.hz_org_profiles_ext_sg
WHERE status = 'PENDING';

Another typical query joins to the main profile table to provide business context for the IDs stored:

SELECT sg.old_profile_id, sg.new_profile_id, sg.status,
       oldp.party_id, hzp.party_name
FROM ar.hz_org_profiles_ext_sg sg,
     hz_org_profiles oldp,
     hz_parties hzp
WHERE sg.old_profile_id = oldp.organization_profile_id
AND oldp.party_id = hzp.party_id
AND sg.status = 'ERROR';

This table is not intended for direct transactional use or reporting by end-users; it is a system-managed object central to the extensions versioning workflow.

Related Objects

This staging table is integral to the profile versioning and extensions ecosystem within TCA. Its key relationships are:

• Primary Source/Target (Logical): The table's OLD_PROFILE_ID and NEW_PROFILE_ID columns reference the ORGANIZATION_PROFILE_ID column in HZ_ORG_PROFILES, the core table for organization profile information.
• Extensions Tables (Logical): The concurrent program that uses this staging table reads from and writes to the organization profile extensions tables, such as HZ_ORG_PROFILES_EXT and related _EXT_BO tables. It copies rows where ORGANIZATION_PROFILE_ID equals the OLD_PROFILE_ID and creates new rows with the NEW_PROFILE_ID.
• Concurrent Program: The "Copy Organization Extensions Data for Profile Versioning" program (likely a PL/SQL package) is the primary consumer of data in this table.
• Profile Option: The process is governed by the "HZ: Profile Version Method for Updates" profile option, which determines when new profile versions are created, thereby populating this staging table.

The table has no documented foreign key constraints, as its relationships are managed procedurally by the concurrent program.