Results for “execute_dynamic_sql”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGW_PROP_APPROVALS_PVT is a private PL/SQL package in the APPS schema that implements the approval-routing engine for Oracle Grants Management (formerly Oracle Grants Proposal). The _PVT suffix indicates that this is an internal, implementation-only package: its procedures and functions are not exposed as a public API and are intended for use by other packages within the same product family, such as IGW_PROP_APPROVALS_PUB or the Grants Proposal forms. Its business purpose is to evaluate the business rules configured for a proposal, determine the appropriate approval hierarchy, and launch the corresponding Oracle Workflow approval process for a submitted proposal or budget. The package is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2 ETRM repositories.

Key Procedures and Functions

The documented package exposes twelve procedures and functions. START_APPROVAL_PROCESS is the top-level entry point; it orchestrates the submission of a proposal into the approval workflow. VALIDATE_LOGGED_USER_RIGHTS verifies that the session user holds the privileges required to approve or act on the proposal. START_APPROVAL performs the actual invocation of the Workflow engine once validation has passed. GET_BUSINESS_RULES retrieves the applicable rules for the proposal's organization and business context, while EXECUTE_BUSINESS_RULE and EXECUTE_LINE evaluate individual rules and their line-level conditions. FOUND_STRING and NOT_FOUND_STRING are utility functions that return the literal tokens used by the rule-expression evaluator. EXECUTE_DYNAMIC_SQL runs dynamically constructed SQL generated from those rule expressions. GET_PARENT_ORG_ID resolves the parent organization in the organizational hierarchy, and ASSIGN_SO_ROLE assigns the appropriate sub-role identity to the approver. POPULATE_LOCAL_WF_TABLES loads the Workflow attribute tables with the proposal, budget, and routing data required by the approval notification. Parameter signatures are not published in the ETRM metadata and should be confirmed against the deployed package specification.

Tables Accessed

The package reads and writes a broad set of IGW and shared tables through APPS synonyms. It references IGW_PROPOSALS_ALL for the proposal header, IGW_PROP_CHECKLIST for submission checklists, and IGW_PROP_MAPS / IGW_PROP_MAPS_S for proposal-to-organization mappings. Budget data is drawn from IGW_BUDGETS, IGW_BUDGET_DETAILS, and IGW_BUDGET_PERIODS. Approval rule configuration is held in IGW_BUSINESS_RULES_ALL and IGW_BUSINESS_RULE_LINES, while organizational hierarchy resolution uses IGW_ORG_MAPS_ALL, IGW_ORG_MAP_DETAILS, and HR_ALL_ORGANIZATION_UNITS. Approval run history and audit records are written to IGW_PROP_APPROVAL_RUNS and IGW_PROP_APPROVAL_RUNS_S. FND_USER is joined to establish approver identity.

Usage Notes

IGW_PROP_APPROVALS_PVT is not intended for direct support call. It is invoked indirectly from the Grants Proposal forms and from IGW_PROP_APPROVALS_PUB when a user submits a proposal or budget for approval, or when administrative actions such as reassignment or recall require the routing logic to be re-evaluated. It is not referenced by any other documented package, which reinforces its role as a leaf-level implementation unit behind the public Grants approvals API. Because it relies on Oracle Workflow (FND_API and STANDARD dependencies) and executes dynamic SQL derived from IGW_BUSINESS_RULE_LINES, customizations to approval routing should be implemented through the business-rule setup tables rather than by modifying this package.