Search Results show_record

Overview

The APPS.PMI_SECURITY_PKG package is a restricted Oracle EBS PL/SQL security utility that belongs to the Process Manufacturing Intelligence (PMI) product family. Its principal business function is to evaluate and report the security access rights that a given Oracle Applications user holds with respect to a specific organization or organization hierarchy. In environments where multiple inventory organizations, operating units, or process manufacturing facilities coexist, controlling which users may view or transact against a given organization is essential to data integrity and segregation of duties. This package centralizes that evaluation logic so that PMI components, Oracle Forms, and other dependent modules can consistently enforce organization-level access without duplicating security queries.

The package header declares AUTHID CURRENT_USER, meaning that all SQL executed within it runs under the privileges and schema context of the invoking session rather than the defining schema. This is a deliberate design choice for a security package, as it ensures that row-level and organization-level access checks respect the caller's own security profile. The header also includes a PRAGMA RESTRICT_REFERENCES directive (WNDS, WNPS) on its single function, confirming that the routine does not write to the database or package state—it is a pure read-only query.

Key Procedures and Functions

• SHOW_RECORD — The sole documented routine in this package. It is a FUNCTION returning a VARCHAR2 value. Its purpose is to determine and return the organization-related security record or access status for a specified organization, organization type, and user. It accepts an organization code, an organization type indicator (with a documented default), and a user identifier (also defaulted). The return value represents the resolved security outcome, which callers use to decide whether to grant access. Because the routine is declared read-only via the restrict-references pragma, it is safe to invoke from within SQL statements and other query-centric contexts.

The package exposes no additional documented procedures or functions beyond SHOW_RECORD, reflecting its narrow, single-purpose role as an organization security resolver.

Tables Accessed

The package reads from two tables, accessed through APPS synonyms:

• SY_ORGN_MST — The organization master table, which holds the definition and attributes of each organization, including the organization code and type. It supplies the organization context used to match a request against a defined organization.
• SY_ORGN_USR — The organization–user association table, which records which users are mapped to which organizations. This is the core security assignment table that determines user-level access to an organization.

Both tables are read-only in this context, consistent with the WNDS pragma. The package joins organization master data with user-assignment data to resolve whether the specified user has a valid security record for the specified organization.

Usage Notes

PMI_SECURITY_PKG is invoked by 47 other packages within the Oracle EBS environment, according to the documented metadata, making it a foundational dependency for PMI and related security checks. It is typically called from Oracle Forms and PL/SQL program units that must validate organization access before displaying records or permitting transactions, and from concurrent programs or custom code that require a consistent, centralized organization security test. Because SHOW_RECORD honors the invoking user's privileges through AUTHID CURRENT_USER, callers can rely on it to enforce the same organization security model applied throughout the application. Developers extending PMI should reuse this package rather than issuing direct queries against SY_ORGN_MST and SY_ORGN_USR, ensuring uniform enforcement of the organization security model across Oracle EBS 12.1.1 and 12.2.2.