Search Results edr_trans_query_temp_s

Overview

EDR_STANDARD is the core utility package body within the Oracle E-Business Suite Advanced EDR (Electronic Document Review / Approval Routing) module, owned by the APPS schema. Its documented API classification is OTHER, indicating that it is a shared, cross-cutting support library rather than a single-purpose processing engine. The package supplies reusable PL/SQL building blocks that other EDR components, Oracle Approvals Management (AME) integrations, and Oracle Workflow event subscribers rely upon during transaction review, approval, and routing operations.

Functionally, EDR_STANDARD concentrates on four responsibilities: translating approval-rule inputs and variables into runtime values, evaluating signature (PSIG) status and requirement logic, formatting and comparing date and audit values, and querying EDR transaction review results through temporary staging tables. Because its dependency list reaches into AME_API2, WF_CORE, WF_EVENTS, FND_FLEX_DESCVAL, and FND_MESSAGE, the package sits at the intersection of EDR, AME, and Workflow, and is referenced by eighteen other database objects.

Key Procedures and Functions

Seventeen procedures and functions are documented:

• PSIG_STATUS — Evaluates and returns the current signature status for an EDR review document.
• PSIG_REQUIRED — Determines whether a signature is mandatory for a given document or approval context.
• EREC_REQUIRED — Determines whether an electronic record is required.
• PSIG_QUERY and PSIG_QUERY_ONE — Retrieve signature records, respectively as a set and as a single row, from the temporary query structures and PSIG document tables.
• GET_AMERULE_INPUT_VALUES and GET_AMERULE_INPUT_VARIABLES — Populate AME rule input values and the associated variable definitions used during rule evaluation.
• DISPLAY_DATE, DISPLAY_DATE_ONLY, and DISPLAY_TIME_ONLY — Format date and time values for display, honoring FND_DATE and FND_TIMEZONES conventions.
• COMPARE_AUDITVALUES — Compares audit values, typically to identify changes between prior and current document states.
• FIND_WF_NTF_RECIPIENT — Resolves the notification recipient for a Workflow notification derived from routing rules and directory data.
• GET_DESC_FLEX_SINGLE_PROMPT, GET_DESC_FLEX_ALL_PROMPTS, and GET_DESC_FLEX_ALL_VALUES — Retrieve descriptive flexfield context, prompt, and value metadata through FND_DESCR_FLEX_CONTEXTS and FND_DESCR_FLEX_COL_USAGE_VL.
• GET_MEANING — Returns the meaning of a lookup code from FND_LOOKUPS.
• GET_USER_RESPONSE — Obtains a user response value, supporting interactive approval flows.

Tables Accessed

The package reads and writes the following documented objects via APPS synonyms:

• EDR_TRANS_QUERY_TEMP and EDR_TRANS_QUERY_TEMP_S — temporary staging tables holding transaction review query results; the sequence EDR_TRANS_QUERY_TEMP_S supplies surrogate keys during result generation. This is the object the user searched for.
• EDR_PSIG_DOCUMENTS — signature document definitions used by the PSIG functions.
• EDR_AMERULE_INPUT_VAR and EDR_AMETRAN_INPUT_VAR — AME rule input variable definitions and transaction-level input variable values.
• AME_CALLING_APPS — identifies the calling application for AME integration.
• FND_APPLICATION, FND_DESCR_FLEX_CONTEXTS, and FND_LOOKUPS — application registration, descriptive flexfield context, and lookup meanings.
• WF_EVENTS, WF_EVENT_SUBSCRIPTIONS, WF_EVENT_T, and WF_ROUTING_RULES — Workflow event data and notification routing used to resolve recipients.
• DBMS_SQL, DUAL, and PLITBLM — dynamic SQL execution and utility references.

Usage Notes

EDR_STANDARD is invoked programmatically rather than directly by end users. Forms-based EDR review and approval windows call the PSIG, date display, flexfield, and meaning utilities to render signature status, formatted dates, and descriptive flexfield prompts. Concurrent programs and Workflow background processes invoke the AME rule input and notification recipient functions during approval routing. Custom extensions should call these procedures rather than reimplementing status, requirement, or formatting logic, because the package enforces the same FND_GLOBAL, FND_PROFILE, and AME conventions used elsewhere in the EDR and AME integration stack. Queries against EDR_TRANS_QUERY_TEMP and EDR_TRANS_QUERY_TEMP_S should be treated as transient; the sequence-based temporary table contents are meaningful only within the session or process that generated them.