Search Results build_eres_query

Overview

GMA_STANDARD is a shared PL/SQL utility package owned by APPS in the Oracle E-Business Suite. It supports the Oracle Process Manufacturing (OPM) quality and electronic records infrastructure, providing reusable routines for e-signature (electronic signature) status and requirement checks, electronic record (eRecord) handling, and Oracle Approvals Management (AME) rule input retrieval. The package is classified in ETRM as an OTHER API, indicating it is not a public, versioned business API but rather an internal helper package invoked by Oracle Forms and other PL/SQL components.

Its global declarations include a signature status variable (G_SIGNATURE_STATUS) used to capture workflow outcomes such as APPROVED or REJECTED, an API version constant (G_API_VERSION), and a set of PL/SQL record and collection types. These include eventDetails/eventQuery for event key queries and RuleInputvalues/ameruleinputvalues for AME approval rule inputs. These types are central to the interface between the quality modules and the AME approval engine.

Key Procedures and Functions

• PSIG_STATUS — Returns the signature status for a given event. The status reflects the latest event occurrence and returns values such as PENDING, COMPLETE, or ERROR.
• PSIG_REQUIRED — Determines whether a signature is required for a given event, returning a boolean TRUE or FALSE.
• EREC_REQUIRED — Determines whether an electronic record is required for a given event, returning a boolean TRUE or FALSE.
• PSIG_QUERY — Called from forms before invoking the Transaction Query form; accepts an eventQuery collection and returns a numeric result.
• GET_AMERULE_INPUT_VALUES — Retrieves AME input variable values for a given transaction. It accepts the AME application, AME rule identifier, AME rule name, and returns an ameruleinputvalues collection. This is the object the user searched for, and it is the mechanism by which OPM feeds transaction context into AME approval rules.
• DISPLAY_DATE — Returns a formatted display date string, a presentation helper for forms.
• UPLOAD_FILE — Handles file upload processing, supporting attachment and electronic record storage.
• BUILD_ERES_QUERY — Constructs the query used for electronic records retrieval.
• GET_ERECORD_ID — Returns the identifier of an electronic record for a given context.

Tables Accessed

The package references several tables through APPS synonyms. EDR_FILES_B and EDR_FILES_B_S provide the electronic record file base table and its translated/security variant, used by UPLOAD_FILE and the eRecord routines. FND_ATTACHED_DOCUMENTS and FND_ATTACHED_DOCUMENTS_S supply attachment metadata, while FND_DOCUMENT_CATEGORIES classifies attachments. FND_LOBS stores the large object content for uploaded files. FND_DESCR_FLEX_COLUMN_USAGES supports descriptive flexfield column determination, and DUAL is used for singleton select expressions. PLITBLM is an Oracle Forms-supplied table used for list-of-values handling.

Usage Notes

GMA_STANDARD is invoked primarily from Oracle Forms in the OPM quality modules, where PSIG_QUERY, PSIG_STATUS, and PSIG_REQUIRED drive signature validation before transaction submission. GET_AMERULE_INPUT_VALUES is called during AME approval rule evaluation to populate rule input values with transaction-specific data. The package is referenced by three other packages, so it should be treated as a dependency that must remain backward compatible. Customizations should call the documented procedures rather than modifying the package body, since it is a seed-shipped, non-versioned utility.