Search Results start_org_changed

Overview

PA_ORG_UTILS is a PL/SQL utility package in the Oracle EBS Projects (PA) module that centralizes access to organization hierarchy metadata used throughout Oracle Projects. Organizations in Oracle Projects are arranged into hierarchies (org structures) that support burdening, costing, and cross-charge processing. Each hierarchy is versioned, and each version has a designated start organization and a defined maximum depth or level. PA_ORG_UTILS exposes the accessor functions and denormalization routines that Oracle Projects forms, concurrent programs, and internal APIs use to resolve these hierarchy attributes at runtime.

The package is declared with AUTHID CURRENT_USER, meaning it executes with the privileges of the invoking user rather than the package owner. It is owned by APPS and is classified as OTHER in the ETRM documentation, indicating it is largely an internal support package rather than a public, published API. Its documented header carries a version stamp of 120.3, last modified in 2007, and several functions are annotated with a change request reference (1333116) related to burdening hierarchy handling. The presence of pragmas (RESTRICT_REFERENCES WNDS, WNPS) confirms the functions are intended to be side-effect free and callable from SQL, not only from PL/SQL.

Key Procedures and Functions

The package exposes 26 documented procedures and functions. The central accessors are the hierarchy version and start-organization lookups:

• GET_START_ORG_ID — returns the start organization ID for a given organization hierarchy usage.
• GET_START_ORG_ID2 — added under CR 1333116 to return the start organization while correctly handling burdening hierarchies.
• GET_START_ORG_ID_SCH — returns the start organization for a specified org hierarchy version ID.
• GET_ORG_VERSION_ID and GET_ORG_VERSION_ID2 — return the active hierarchy version ID for a usage; the second variant provides burdening-aware resolution.
• GET_ORG_LEVEL — returns the level of a child or parent organization within a hierarchy, given the version, organization, and start organization.
• GET_MAX_ORG_LEVEL — returns the deepest level in a hierarchy version starting from a given start organization.
• CHECK_ORG_IN_ORGHIERARCHY, CHECK_ORGHIERARCHY_TYPE, and CHECK_ORG_TYPE — validation helpers that confirm whether an organization belongs to a hierarchy, whether a hierarchy is of a given type, and whether an organization is of a particular type.
• CREATE_ORG_HIER_DENORM_LEVELS and POPULATE_HIER_DENORM_SCH — build and populate the denormalized hierarchy level data.
• CREATE_ORG_HIERARCHY_DENORM, POPULATE_HIERARCHY_DENORM, and POPULATE_HIERARCHY_DENORM2 — higher-level denormalization routines, including a burdening-aware variant.
• MAINTAIN_ORG_HIST_IMP, MAINTAIN_ORG_HIST_BRI, MAINTAIN_ORG_HIST_BRD, and MAINTAIN_ORG_INFO_HIST_BRI — history maintenance routines, typically bound to triggers or called during hierarchy change processing.
• START_ORG_CHANGED — invoked when the start organization of a hierarchy changes, to propagate dependent updates.

All named functions carry RESTRICT_REFERENCES pragmas, so they may be referenced from SQL statements as well as PL/SQL blocks.

Tables Accessed

The package reads and writes several Projects and HR tables through APPS synonyms:

• PA_ORG_HIERARCHY_DENORM — the denormalized hierarchy table that the populate and maintain routines write to and the accessors read from.
• PER_ORG_STRUCTURE_ELEMENTS — the HR organization structure definition that supplies parent/child relationships and levels.
• HR_ORGANIZATION_INFORMATION — descriptive organization attributes.
• PA_ALL_ORGANIZATIONS — the Projects view of valid organizations.
• PA_IMPLEMENTATIONS and PA_IMPLEMENTATIONS_ALL — implementation-level configuration that determines hierarchy behavior.
• PA_CC_TP_SCHEDULE_LINE_LKP — cross-charge transfer price lookup used in hierarchy resolution.
• DUAL and PLITBLM — utility objects used for singleton selects and PL/SQL table manipulation.

Usage Notes

PA_ORG_UTILS is invoked primarily from Oracle Projects forms and concurrent programs whenever hierarchy information must be resolved. The denormalization procedures are typically run by the organization hierarchy maintenance concurrent program or by the underlying hierarchy population logic, while the accessor functions (GET_START_ORG_ID, GET_ORG_VERSION_ID, and their successors) are called inline from other PL/SQL packages, SQL views, and forms. The package is referenced by 11 other packages in the EBS schema, indicating it is a foundational dependency for organization-related processing.

Custom code should generally call the higher-numbered variants (GET_START_ORG_ID2, GET_ORG_VERSION_ID2) when burdening hierarchies are in use, since the original functions predate the CR 1333116 burdening fix. Because the package uses AUTHID CURRENT_USER and RESTRICT_REFERENCES, calling code must ensure the invoking schema has appropriate synonyms and privileges. No public API is documented in ETRM; direct modification of the denormalized table by custom code is not recommended. In 12.1.1 and 12.2.2 the package structure is consistent, so code written against one release is generally portable to the other.