Search Results get_proc_org_opinion_date




Overview
Explore in AI Chat

Overview

AMW_PUBLIC_PKG is a public PL/SQL package in the APPS schema that serves as a lightweight query interface into the Oracle EBS Audit Management and Workflow (AMW) opinion model. Its principal responsibility is to expose the current audit or opinion status associated with an organization process, allowing callers to retrieve the most recent recorded opinion without embedding the underlying join logic in their own code. The package header carries the identifier amwpbpkb.pls 115.0, dated 2004, indicating that its interface has remained largely unchanged across the EBS 12.1.1 and 12.2.2 releases. The package is classified as an "OTHER" API rather than a formal public API, which is significant: it is a convenience wrapper intended for internal AMW consumers and not a supported extension point.

Key Procedures and Functions

The package exposes two documented functions, both of which accept a process identifier, an organization identifier, and an opinion mode, and both of which return a single scalar value.

  • GET_PROC_ORG_OPINION_STATUS — Returns the audit result recorded for a given process and organization combination for the specified opinion mode. It resolves the correct opinion row by navigating from the opinion type code through AMW_OPINION_TYPES_B and AMW_OBJECT_OPINION_TYPES to the AMW_ORG_PROCESS object, then selects the row in AMW_OPINIONS_V bearing the maximum last_update_date. Only the most recent opinion is returned.
  • GET_PROC_ORG_OPINION_DATE — Returns the last update date associated with the most recent opinion for the same process, organization, and mode combination. It is effectively the timestamp companion to the status function and is typically called alongside it to present a "status as of" value.

Both functions are structured defensively: the NO_DATA_FOUND exception is trapped and NULL is returned. GET_PROC_ORG_OPINION_STATUS additionally traps WHEN OTHERS and returns NULL, which means unexpected errors are silently suppressed rather than propagated to the caller.

Tables Accessed

The package reads from three documented tables, all accessed through APPS synonyms.

  • AMW_OPINIONS_V — The primary source of opinion data. It is queried on pk1_value (process), pk3_value (organization), and object_opinion_type_id, with a correlated subquery on last_update_date to isolate the current row. Despite the _V suffix, it is used here as a queryable view.
  • AMW_OBJECT_OPINION_TYPES — Maps an opinion type to a specific business object. It is used to constrain the query to the AMW_ORG_PROCESS object.
  • AMW_OPINION_TYPES_B — The base opinion type definition table, queried by opinion_type_code to translate the caller's mode argument into an opinion_type_id.

FND_OBJECTS is also referenced via a subquery resolving the object name 'AMW_ORG_PROCESS' to its object_id, linking the AMW opinion model to the standard Oracle Application Object Library object registry.

Usage Notes

Because the function signatures are not documented as a supported public API, AMW_PUBLIC_PKG is best regarded as an internal helper. It is typically invoked from AMW-related forms or from custom code that needs to display an opinion status on an organization process without reproducing the multi-table join. Callers should be aware that the WHEN OTHERS handler returns NULL on error, which can make genuine failures indistinguishable from "no opinion recorded"; any dependent logic should therefore treat a NULL result as indeterminate rather than as a negative opinion. The package is referenced by zero other packages in the documented metadata, reinforcing that its callers are forms or external customizations rather than the packaged AMW application tier.