Search Results get_dynamic_sql

Overview

The APPS.IGS_PE_DYNAMIC_PERSID_GROUP package belongs to the Oracle E-Business Suite Student System (Higher Education) product family, within the Person/Entity (IGS_PE) module. It provides the runtime engine for dynamic person identification groups (often referred to informally as "dynamic PIGs"). A dynamic person ID group is a persisted definition that does not store a static list of persons; instead it stores a SQL query segment that is evaluated on demand to produce the current membership of the group. This package assembles, resolves, and returns those SQL statements and evaluates individual membership against them.

The package is classified as OTHER in the ETRM registry rather than as a public API, which indicates it is primarily an internal supporting package consumed by other Student System components. It is referenced by 27 other packages, confirming its role as a shared, low-level service within the person identification infrastructure.

The object header ($Header: IGSPEDGS.pls 120.1 2006/02/02 ... skpandey noship $) indicates that the shipped specification is non-public-facing ("noship" applies to the specification visibility marker) and dates from the 11i/12.0 lineage, carried forward unchanged into 12.1.1 and 12.2.2.

Key Procedures and Functions

• IGS_GET_DYNAMIC_SQL — Returns the dynamic SQL text associated with a given group ID (see parameter list), reporting a status through an OUT parameter. This is the legacy form of the resolution routine.
• GET_DYNAMIC_SQL — The superseding form of the same operation. In addition to the group ID and a status OUT parameter, it returns the group type through an OUT NOCOPY parameter. Callers use the group type to determine how the generated SQL should be interpreted or post-processed.
• GET_DYNAMIC_SQL_FROM_FILE — Reads SQL segments from an external file defined in IGS_PE_DYN_SQLSEGS (keyed by file name) and returns the assembled SQL text. This supports SQL segment definitions that are maintained outside the database and loaded at runtime.
• DYN_PIG_MEMBER — The membership test entry point, and the routine most commonly identified by the search term dyn_pig_member. Given a group ID and a person ID, it returns a numeric result indicating whether the specified person is a member of the dynamic group. This is the function that downstream code calls when it must determine group membership without materialising the full member set.
• IGS_POST_SAVE_DOCUMENT — A post-save handler that persists a SQL segment. It receives a workbook owner, workbook name, worksheet name, a sequence value, and the SQL segment text, and returns a numeric status. This routine supports the authoring and maintenance of dynamic group definitions, typically from a spreadsheet-based or document-oriented maintenance flow.

Tables Accessed

• IGS_PE_PERSID_GROUP_ALL — The base group definition table. The group ID and group type parameters of the resolution functions are typed against group_id and the group view column group_type, indicating that group definitions are read from this table.
• IGS_PE_DYN_SQLSEGS — Stores the SQL segments that make up a dynamic group definition, including the file_name attribute used by GET_DYNAMIC_SQL_FROM_FILE. IGS_POST_SAVE_DOCUMENT writes segments here.
• IGS_PE_DYN_SQLSEGS_S — The companion sequence (or surrogate/summary) object supporting IGS_PE_DYN_SQLSEGS, used when new segment rows are inserted by the post-save routine.

Usage Notes

This package is not intended for direct end-user invocation. It is called from other PL/SQL packages within the Student System (27 documented dependants), from OA Framework or Forms-based group maintenance pages, and from concurrent programs that evaluate or refresh dynamic group membership in batch. Because dynamic groups resolve to SQL at runtime, callers should treat the returned SQL as internally generated and never construct or concatenate it themselves.

On 12.1.1 and 12.2.2 the package remains deployed in the APPS schema exactly as documented; no Online Patching (ADOP) editioning-specific behaviour is described in the source excerpt, and the package owns no editioning view of its own. Implementers extending dynamic group behaviour should wrap calls through the documented functions rather than querying the underlying tables directly, since the SQL assembly logic resides entirely inside this package.