Search Results aso_approval_instance_status

Overview

The APPS.ASO_APPROVAL_INSTANCES_ALL_V view exposes approval instance data for Oracle Order Capture quotations. It consolidates the approval workflow state of a quote header with the identity of the requester who initiated the approval, so that report writers, forms, and integration programs can query a single, denormalized record instead of joining the transactional approval table to lookup and human-resources sources at every call site. The view is owned by the APPS schema and is marked VALID in ETRM 12.2.2, and it is the same definition shipped with 12.1.1.

Its principal contribution is the REQUESTER_NAME column. The view does not store a requester name in the base tables; instead, it resolves REQUESTER_USERID to a display name using a two-tier lookup. It first attempts to find a resource name in JTF_RS_RESOURCE_EXTNS, and if none is active at the current date, it falls back to the person's FULL_NAME from PER_ALL_PEOPLE_F joined to FND_USER. This makes the view the canonical source for "who requested this approval" questions in ASO reporting.

Underlying Base Objects

The documented base objects are ASO_APR_OBJ_APPROVALS (SYNONYM), ASO_LOOKUPS (VIEW), ASO_QUOTE_HEADERS (SYNONYM), FND_USER (SYNONYM), JTF_RS_RESOURCE_EXTNS (SYNONYM), and PER_ALL_PEOPLE_F (SYNONYM).

• ASO_QUOTE_HEADERS_ALL supplies the quote context: QUOTE_HEADER_ID, QUOTE_NUMBER, and QUOTE_VERSION. It is joined to the approval table on QUOTE_HEADER_ID = OBJECT_ID.
• ASO_APR_OBJ_APPROVALS is the primary fact-like source, providing the approval instance identifier, status code, requester user id, requester comments, and the standard WHO audit columns.
• ASO_LOOKUPS is joined on LOOKUP_TYPE = 'ASO_APPROVAL_INSTANCE_STATUS' to translate the stored status code into a user-facing MEANING.
• JTF_RS_RESOURCE_EXTNS and PER_ALL_PEOPLE_F/FND_USER are used only in the scalar subqueries that derive REQUESTER_NAME.

Key Columns

• QUOTE_HEADER_ID / QUOTE_NUMBER / QUOTE_VERSION — identify the quote and its revision to which the approval belongs.
• OBJECT_APPROVAL_ID / OBJECT_ID / OBJECT_TYPE — the approval object key; OBJECT_ID is the quote header id for this view's join.
• APPROVAL_INSTANCE_ID — the approval workflow instance.
• APPROVAL_STATUS — the decoded status meaning from ASO_LOOKUPS; APPROVAL_STATUS_CODE retains the raw lookup code.
• REQUESTER_NAME — the resolved display name of the requester, described above.
• REQUESTER_COMMENTS — free-text justification captured at submission.
• START_DATE / END_DATE — validity window of the approval instance.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

Typical uses include open-approval aging reports, audit trails of who requested approval on a quote, and integration extracts that push approval status to external workflow or CRM systems.

• List all pending approvals for a quote: SELECT quote_number, quote_version, approval_status, requester_name, start_date FROM aso_approval_instances_all_v WHERE quote_number = :p_quote_number;
• Find approvals requested by a user: SELECT quote_number, approval_status, requester_name FROM aso_approval_instances_all_v WHERE requester_name LIKE :p_name;
• Age open approvals: SELECT quote_number, requester_name, TRUNC(SYSDATE) - TRUNC(start_date) days_open FROM aso_approval_instances_all_v WHERE approval_status_code = 'PENDING';

Because REQUESTER_NAME is built from subqueries against effective-dated tables, filter results carefully in high-volume extracts; the view is best used with a restricting predicate on quote header or approval status.