Search Results execute_dynamic_sql

Overview

IGW_PROPOSAL_APPROVAL is an Oracle E-Business Suite PL/SQL package owned by the APPS schema that supports the Grants/Projects proposal approval workflow. Its primary responsibility is to initiate and drive the approval process for proposals stored in IGW_PROPOSALS_ALL, evaluate the business rules and rule lines that govern approval routing, and populate the local workflow tables used to hand the proposal into Oracle Workflow for human and role-based approvals. The package is declared AUTHID CURRENT_USER, meaning it executes with the privileges of the invoker rather than the definer, and its header dates to release 11i (version 115.7), carried forward into EBS 12.1.1 and 12.2.2. It also validates whether the logged-in user holds the rights required to act on a proposal, resolves parent organizations and sales-order roles, and prepares the Workflow staging data. Because it bridges proposal data, budget data, business-rule definitions, organization mappings, and Workflow, it is a foundational component of the proposal lifecycle.

Key Procedures and Functions

• START_APPROVAL_PROCESS — The main entry point that launches the approval process for a given proposal. It follows the standard FND_API pattern (message list, commit, validate-only flags) and returns return status, message count, and message data.
• VALIDATE_LOGGED_USER_RIGHTS — Confirms that the logged-in user holds the rights required to approve the specified proposal, returning a status indicator.
• START_APPROVAL — An alternate/older entry point that initiates approval and returns an error message and return status.
• GET_BUSINESS_RULES — Retrieves the applicable business rules for a given rule type, generating a run ID and reporting whether rules were found and whether any are invalid.
• EXECUTE_BUSINESS_RULE — Evaluates a single business rule identified by rule ID and returns the result.
• EXECUTE_LINE — Evaluates one rule line given its expression type, left value, operator, and right value; this is the core comparison logic behind rule evaluation.
• FOUND_STRING / NOT_FOUND_STRING — Helpers that return the literal strings representing found and not-found conditions used in rule evaluation.
• EXECUTE_DYNAMIC_SQL — Builds and executes dynamically constructed SQL, the routine most closely associated with the user's search term; it underpins the dynamic evaluation of rule expressions.
• GET_PARENT_ORG_ID — Resolves the parent organization identifier for use in approval routing and organization mapping.
• ASSIGN_SO_ROLE — Assigns a sales-order role in support of approval responsibility assignment.
• POPULATE_LOCAL_WF_TABLES — Loads the local Workflow staging tables that carry the proposal into Oracle Workflow.

Tables Accessed

The package reads and writes through APPS synonyms. Proposal data resides in IGW_PROPOSALS_ALL and IGW_PROP_MAPS/IGW_PROP_MAPS_S. Approval bookkeeping uses IGW_PROP_APPROVAL_RUNS and IGW_PROP_APPROVAL_RUNS_S; checklist handling uses IGW_PROP_CHECKLIST. Business rules are read from IGW_BUSINESS_RULES_ALL and IGW_BUSINESS_RULE_LINES. Budget context comes from IGW_BUDGETS, IGW_BUDGET_DETAILS, and IGW_BUDGET_PERIODS. Organization and role resolution uses IGW_ORG_MAPS_ALL, IGW_ORG_MAP_DETAILS, HR_ALL_ORGANIZATION_UNITS, and FND_USER. These tables are read to determine eligibility and routing and are written to record approval runs and stage Workflow data.

Usage Notes

IGW_PROPOSAL_APPROVAL is typically invoked from the Grants/Projects proposal forms when a user submits a proposal for approval, and from concurrent processes that evaluate business rules and stage Workflow data. It is referenced by one other package, so customizations should preserve the documented call signatures. The presence of EXECUTE_DYNAMIC_SQL warrants caution: because the package is AUTHID CURRENT_USER and builds SQL dynamically, its effective privileges depend on the invoking user, and any custom rule content feeding into dynamic SQL should be treated as a security-sensitive input. Standard FND_API message-handling conventions apply to START_APPROVAL_PROCESS.