Search Results document_line_id




Overview
Explore in AI Chat

Overview

The APPS.CLN_PO_CHANGE_RESPONSE_LINE_V view is a procurement-side reporting and integration object within Oracle E-Business Suite, owned by the APPS schema. It exposes supplier responses to purchase order change requests at the document line level, aggregating status information captured against change request records. The view is particularly relevant to the Oracle Supplier Network (OSN) and CLN (Collaboration) product family, where purchase order acknowledgements, change acknowledgements, and response reasons are exchanged between buying organizations and suppliers.

The view serves as a consolidated presentation layer over change request response data. Rather than requiring callers to navigate the transactional PO_CHANGE_REQUESTS table directly, the view pre-aggregates line-level and shipment-level responses into a single row per document line. This makes it suitable for reporting, workflow evaluation, and integration endpoints that need a compact view of supplier response status. Because the view filters by the current change request group identifier returned by CLN_PO_CHANGE_RESPONSE_PKG.Get_change_request_group_id, it is scoped to the active collaboration context rather than returning the entire historical set of change requests.

Underlying Base Objects

The view is defined over three documented references. The primary data source is PO_CHANGE_REQUESTS, accessed through a synonym, which stores the individual change request and response records, including request level, request status, response reason, and supplier line reference. The view restricts this table to records where REQUEST_LEVEL is either 'LINE' or 'SHIPMENT', and where the CHANGE_REQUEST_GROUP_ID matches the value returned by the packaged function.

The second referenced object is the package CLN_PO_CHANGE_RESPONSE_PKG, invoked in a scalar subquery against DUAL. This package supplies the change request group identifier that scopes the view to the current response cycle. The third reference, DUAL, is used only as the syntactic container for that function call. In ETRM 12.2.2, these references are documented as a package and two synonyms, indicating that the view depends on packaged state at query time.

Key Columns

  • DOCUMENT_LINE_ID — the grouping key and the PO document line identifier to which the aggregated response applies.
  • REQUEST_STATUS — derived via MAX(DECODE(REQUEST_LEVEL,'LINE',REQUEST_STATUS,NULL)), this returns the request status specifically recorded at the line level, or null when no line-level row exists.
  • MIN_REQUEST_STATUS — computed as MIN(REQUEST_STATUS) across both line and shipment level rows, capturing the least favorable (alphabetically minimum) status within the group.
  • RESPONSE_REASON — derived via MAX(DECODE(REQUEST_LEVEL,'LINE',RESPONSE_REASON,NULL)), exposing the supplier's stated reason for the line-level response.
  • SUPPLIER_LINE_REF — the maximum supplier-assigned line reference, useful for matching the buying organization's line to the supplier's own numbering.

The presence of both REQUEST_STATUS and MIN_REQUEST_STATUS allows consumers to distinguish between a line-specific status and the overall worst-case status when shipment-level rows also exist. This is a common pattern where a header or line may be acknowledged while an individual shipment schedule is rejected or changed.

Common Use Cases and Queries

A typical consumer queries this view to determine whether a given document line has been acknowledged, rejected, or changed by the supplier, and to retrieve the supplier's reason. The request_status column, being the term most often searched, is the principal filter attribute.

SELECT document_line_id,
       request_status,
       min_request_status,
       response_reason,
       supplier_line_ref
FROM   apps.cln_po_change_response_line_v
WHERE  document_line_id = :p_document_line_id;

Because the view is already scoped to the active change request group, no additional join to the package is required by the caller. A reporting query might aggregate response statuses to measure supplier responsiveness or identify lines awaiting acknowledgement:

SELECT request_status, COUNT(*)
FROM   apps.cln_po_change_response_line_v
GROUP  BY request_status;

Integration endpoints frequently select request_status, min_request_status, and response_reason in a single fetch to transform supplier responses into downstream workflow events. Users should note that the view's result set depends on CLN_PO_CHANGE_RESPONSE_PKG.Get_change_request_group_id returning a valid group identifier; if the package context has not been initialized in the session, the subquery returns null and the view yields no rows.