Search Results po_position_controls_pk

Overview

PO_POSITION_CONTROLS_ALL is a Purchasing (PO) module table in the Oracle E-Business Suite schema PO. It stores job, organization, and position control definitions used by Oracle Purchasing to constrain and authorize document entry and approval activity. Each row associates a position (POSITION_ID) and, where applicable, a job (JOB_ID) within a specific inventory organization (ORGANIZATION_ID) with a purchasing control group and control function, thereby determining what a buyer occupying that position is permitted to create, modify, or approve.

The table is an _ALL table, meaning it holds data across multiple operating units and organizations without a single-org filter, and it supports the multi-org access control (MOAC) model. Its effective dating columns (START_DATE, END_DATE) allow control assignments to change over time without deleting historical definitions.

The documented relationship structure between PO_POSITION_CONTROLS_ALL, PO_CONTROL_GROUPS_ALL, and PO_CONTROL_FUNCTIONS is many-to-one on both foreign keys. Under a heuristic Data Vault classification mined from this FK structure, the table models a link — it resolves the association between a position/job/organization grain and the control group and control function it is bound to. This classification is offered as a modeling suggestion rather than an Oracle-defined designation.

Key Information Stored

The 33-column physical schema is organized around identity, control assignment, effective dating, and audit. The most significant columns are:

• POSITION_ID — the position this control assignment applies to; part of the composite primary key.
• ORGANIZATION_ID — the inventory organization in which the position is defined; the second component of the primary key.
• JOB_ID — the job associated with the position; this column participates in the unique key PO_POSITION_CONTROLS_UK01 along with ORGANIZATION_ID.
• CONTROL_GROUP_ID — foreign key to PO_CONTROL_GROUPS_ALL, identifying the grouping of purchasing controls applied.
• CONTROL_FUNCTION_ID — foreign key to PO_CONTROL_FUNCTIONS, identifying the specific purchasing function being controlled (for example, document entry or approval authority).
• START_DATE and END_DATE — the effective date range during which the control assignment is active.
• ORG_ID — the operating unit identifier supporting multi-org partitioning.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield (DFF) columns available for customer-defined extensions.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard who-columns used for audit and concurrency.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — the concurrent program context columns identifying the process that last touched the row.

The surrogate key is PO_POSITION_CONTROLS_PK (POSITION_ID, ORGANIZATION_ID). The business-key candidate is PO_POSITION_CONTROLS_UK01 (JOB_ID, ORGANIZATION_ID), which enforces that a job is controlled once per organization regardless of position.

Common Use Cases and Queries

Typical usage centers on resolving which control group and control function apply to a given position or job at a point in time, and on auditing changes to those assignments. A canonical join retrieves active controls for a position:

• Query active assignments: SELECT ppc.position_id, ppc.organization_id, ppc.control_group_id, ppc.control_function_id, ppc.start_date, ppc.end_date FROM po.po_position_controls_all ppc WHERE ppc.organization_id = :org_id AND TRUNC(SYSDATE) BETWEEN ppc.start_date AND NVL(ppc.end_date, TRUNC(SYSDATE)+1);
• Resolve control group descriptions: join PO_CONTROL_GROUPS_ALL on CONTROL_GROUP_ID.
• Resolve control function names: join PO_CONTROL_FUNCTIONS on CONTROL_FUNCTION_ID.
• Reporting on control coverage by job: group by JOB_ID and ORGANIZATION_ID using the unique-key grain.
• Audit reporting: filter on LAST_UPDATE_DATE, REQUEST_ID, and the who-columns to trace changes made by concurrent programs.

Reports are usually scoped by ORG_ID or ORGANIZATION_ID, and date-range predicates are required because multiple historical rows may exist for the same position.

Related Objects

The following objects are the most significant dependents or references, based on the documented foreign keys and primary keys:

• PO_CONTROL_GROUPS_ALL — referenced via PO_POSITION_CONTROLS_ALL.CONTROL_GROUP_ID; defines the control group applied to the position/job.
• PO_CONTROL_FUNCTIONS — referenced via PO_POSITION_CONTROLS_ALL.CONTROL_FUNCTION_ID; defines the controlled purchasing function.
• PO_POSITION_CONTROLS_PK — the primary key constraint on (POSITION_ID, ORGANIZATION_ID).
• PO_POSITION_CONTROLS_UK01 — the unique constraint on (JOB_ID, ORGANIZATION_ID).
• HR positions and jobs — POSITION_ID and JOB_ID reference the HR position and job definitions used by Purchasing.
• ORG_ORGANIZATION_DEFINITIONS / organization views — resolve ORGANIZATION_ID and ORG_ID to organization names for reporting.

Because the table is an _ALL table, its rows are filtered by the MOAC security profile at runtime; reports and integrations should always constrain by ORG_ID or ORGANIZATION_ID to avoid cross-operating-unit leakage.