Search Results find_hierarchy_level
Overview
AMW_UTILITY_PVT is a private PL/SQL utility package in the Oracle E-Business Suite Applications (APPS) schema, belonging to the Oracle Internal Controls Manager (OICM) / Audit Management product family, identified by the AMW prefix. It is a shared infrastructure package rather than a business-facing API, and its PVT classification indicates that it is intended for internal use by other AMW packages and is not designed to be called directly by customers or external integrations. The package centralizes common low-level operations — referential integrity validation, lookup translation, message handling, workflow notification, hierarchy traversal, and debug logging — so that the broader AMW codebase does not duplicate this logic. The header comment records its original creation on 04/01/2005, and the source version reference (amwvutls.pls 120.5) confirms it is a maintained component of the OICM seed data object library. Because it exposes 51 documented subprograms and is referenced by 42 other packages, AMW_UTILITY_PVT functions as a foundational dependency across the OICM module.
Key Procedures and Functions
The documented interface groups into several functional categories:
- Validation and integrity: CHECK_FK_EXISTS validates foreign key references against a target table and primary key, honoring a data-type indicator and an optional additional WHERE clause. CHECK_UNIQUENESS verifies uniqueness constraints, and CHECK_LOOKUP_EXISTS confirms that a value exists within AMW_LOOKUPS. IS_Y_OR_N enforces flag validation.
- Lookup and name resolution: GET_LOOKUP_MEANING, GET_LOB_MEANING, GET_EMPLOYEE_NAME, GET_ORGANIZATION_NAME, GET_PROCESS_NAME, GET_RISK_NAME, and GET_CONTROL_NAME translate internal identifiers into user-readable descriptions. GET_MESSAGE_TEXT retrieves message text for display.
- Hierarchy and process utilities: FIND_HIERARCHY_LEVEL resolves position within a hierarchy, while GET_PROC_ORG_OPINION_STATUS and GET_PROC_ORG_OPINION_DATE retrieve process-organization opinion attributes.
- Messaging and workflow: DEBUG_MESSAGE, ERROR_MESSAGE, and DISPLAY_MESSAGES manage the four-level debug framework (none, error, medium, SQL, full). SEND_WF_STANDALONE_MESSAGE and WAIT_FOR_REQ interact with Oracle Workflow and concurrent request processing.
The package declares constants including debug level values, G_NUMBER and G_VARCHAR2 type indicators, the AMW_LOOKUPS schema name, and a RESOURCE_LOCKED exception mapped to Oracle error -54.
Tables Accessed
The package reads and writes across the OICM data model through APPS synonyms. AMW_PROCESS, AMW_PROCESS_NAMES_TL, and AMW_PROCESS_ORGANIZATION supply process definition and organization assignment data. AMW_PROCESS_LOCKS supports concurrency control during process execution, consistent with the RESOURCE_LOCKED exception. AMW_EXCEPTIONS_B, AMW_EXCEPTIONS_TL, and AMW_EXCEPTIONS_REASONS store exception records and their translated descriptions. AMW_CONTROL_ASSOCIATIONS and AMW_OBJECTIVE_ASSOCIATIONS hold control and objective linkage data, while AMW_OBJECT_OPINION_TYPES and AMW_OPINION_TYPES_B define opinion classifications used by the opinion status functions. AMW_AUDIT_PROJECTS, AMW_EXECUTION_SCOPE, and AMW_LATEST_HIERARCHIES provide audit project context and hierarchy snapshots. AMW_PARAMETERS stores configurable setup values.
Usage Notes
AMW_UTILITY_PVT is invoked internally by the 42 dependent AMW packages, typically from OICM forms, concurrent programs, and workflow background processes during audit execution, exception processing, and opinion capture. The user search term "get_project_count" does not correspond to any documented function in this package; project counting logic resides elsewhere in the OICM object set. Customizations should call the public AMW APIs rather than this PVT package, since its interface is subject to change without notice between releases such as 12.1.1 and 12.2.2.