Results for “owner_role”

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

Overview

PO_GA_ORG_ASSIGN_MERGE_V is a VALID database view owned by the APPS schema and shipped with the Oracle Purchasing (PO) module. Its purpose is to reconcile two distinct sources of organizational assignment data for Global Agreements: entries that exist only in draft form within the PO_DRAFTS framework, and entries that have been committed to the production PO_GA_ORG_ASSIGNMENTS table. The view therefore functions as a merge or consolidation layer, presenting a single result set that a consumer can treat as the current working state of organizational assignments without needing to know whether a given row is still in draft or already published.

The name of the view and its defining query make the merge intent explicit. The first branch of the UNION ALL retrieves in-progress draft rows, while the second branch retrieves committed rows that have no corresponding active draft. This design supports the "Global Agreement" (GA) workflow, in which purchasers propose organizational assignments, route them through a draft approval cycle, and eventually publish them. The view is typically consumed by Purchasing forms, concurrent programs, or reporting layers that must display a unified picture of assignments regardless of their lifecycle stage.

The presence of the OWNER_ROLE column in the projection is significant. In the draft branch, the view filters on DFT.OWNER_ROLE = PO_GLOBAL.ROLE, meaning only drafts owned by the Global role are surfaced. This is the primary mechanism by which the view scopes its output to globally managed agreements rather than ordinary, local purchasing documents.

Underlying Base Objects

Per the ETRM metadata, the view is defined over three base objects, all referenced as SYNONYMS in the APPS schema:

• PO_GA_ORG_ASSIGNMENTS (PGOA) — the production table holding committed organizational assignments for Global Agreements. Its columns PO_HEADER_ID, ORGANIZATION_ID, ENABLED_FLAG, VENDOR_SITE_ID, and PURCHASING_ORG_ID form the core payload of the view.
• PO_GA_ORG_ASSIGN_DRAFT (PGOAD) — the draft staging table for the same assignments, carrying additional workflow columns such as CHANGE_ACCEPTED_FLAG and DELETE_FLAG.
• PO_DRAFTS (DFT) — the master drafts table, which supplies OWNER_USER_ID, OWNER_ROLE, STATUS, and DRAFT_ID. It is joined to PO_GA_ORG_ASSIGN_DRAFT on DRAFT_ID.

The first UNION ALL branch joins PO_GA_ORG_ASSIGN_DRAFT to PO_DRAFTS on DRAFT_ID, filters out soft-deleted drafts (DELETE_FLAG IS NULL), restricts to the Global role, and excludes drafts whose STATUS is 'COMPLETED'. The second branch selects from PO_GA_ORG_ASSIGNMENTS only where no non-completed Global-role draft exists for the same PO_HEADER_ID and ORGANIZATION_ID combination. Columns unavailable in the production branch (OWNER_USER_ID, OWNER_ROLE, STATUS, DRAFT_ID, CHANGE_ACCEPTED_FLAG, DELETE_FLAG) are returned as NULL or TO_NUMBER(NULL) to preserve column parity across the UNION.

Key Columns

• OWNER_USER_ID / OWNER_ROLE — identifies the user and role owning the draft; OWNER_ROLE is central to the Global role filter and is NULL for committed rows.
• STATUS — draft lifecycle state; 'COMPLETED' drafts are deliberately suppressed. NULL for published rows.
• DRAFT_ID — key linking the row to PO_DRAFTS; NULL for committed rows.
• CHANGE_ACCEPTED_FLAG / DELETE_FLAG — workflow flags governing whether a draft change was accepted or marked for deletion; both NULL for committed rows.
• PO_HEADER_ID / ORGANIZATION_ID — the composite business key identifying the Global Agreement and the organization to which the assignment applies.
• ENABLED_FLAG / VENDOR_SITE_ID / PURCHASING_ORG_ID — assignment attributes controlling enablement, vendor site, and owning purchasing organization.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, propagated from the respective source table.

Common Use Cases and Queries

A frequent requirement is to report the unified state of organizational assignments for a given Global Agreement header. The following query lists both draft and committed rows:

• SELECT PO_HEADER_ID, ORGANIZATION_ID, ENABLED_FLAG, OWNER_ROLE, STATUS, DRAFT_ID, DELETE_FLAG FROM APPS.PO_GA_ORG_ASSIGN_MERGE_V WHERE PO_HEADER_ID = :p_header_id ORDER BY ORGANIZATION_ID;
• Identify pending Global-role drafts awaiting completion: SELECT DISTINCT DRAFT_ID, OWNER_USER_ID, STATUS FROM APPS.PO_GA_ORG_ASSIGN_MERGE_V WHERE DRAFT_ID IS NOT NULL AND STATUS <> 'COMPLETED';
• Detect committed assignments with no active draft (published-only rows): SELECT PO_HEADER_ID, ORGANIZATION_ID FROM APPS.PO_GA_ORG_ASSIGN_MERGE_V WHERE DRAFT_ID IS NULL;

Because the view encapsulates the draft-versus-published reconciliation, reports and integrations should prefer it over querying PO_GA_ORG_ASSIGNMENTS directly; a direct query against the base table will omit uncommitted draft changes and produce a stale picture of the agreement. Note also the UNION ALL semantics: if a draft exists but is COMPLETED, the production branch still supplies the row, and if a draft is soft-deleted the production branch likewise takes over, ensuring a single authoritative row per PO_HEADER_ID and ORGANIZATION_ID.