Search Results po_sgd_mod_linelocs_v

Overview

PO_SGD_MOD_LINELOCS_V is an APPS-owned database view in the Oracle E-Business Suite Purchasing (PO) module. It is part of the "SGD" (Supplier Gateway / sourcing and draft modification) family of objects used by Oracle Purchasing's draft and document modification framework. The view presents purchase order shipment and line location attributes in a normalized, attribute-value (key/value) format rather than the conventional wide row format of the underlying table. Each row represents a single column value for a given line location record, identified by a composite primary key consisting of PO_HEADER_ID, DRAFT_ID, PO_LINE_ID, and LINE_LOCATION_ID.

Because the view pivots column values into rows through a TO_CHAR conversion and a DECODE-based lookup, it is intended primarily as an internal technical component supporting the draft comparison and modification engine, rather than as a general-purpose reporting view. It allows modifying applications to enumerate, compare, and display individual changed attributes (columns) on a shipment, including both the column name and the column value. The view does not store data itself; it is defined entirely over Purchasing draft tables and a units-of-measure lookup.

Underlying Base Objects

The documented ETRM metadata for release 12.2.2 identifies three referenced base objects:

• PO_LINE_LOCATIONS_DRAFT_ALL (synonym) — the primary source of the shipment/line location rows. The view's inner query selects from this draft table, exposing columns such as SHIPMENT_NUM, SHIP_TO_LOCATION_ID, NEED_BY_DATE, PROMISED_DATE, quantity columns (QUANTITY, QUANTITY_RECEIVED, QUANTITY_ACCEPTED, QUANTITY_REJECTED, QUANTITY_BILLED, QUANTITY_CANCELLED), amount columns, receiving and matching controls, and tolerance attributes. This confirms the view operates on draft (unapproved or in-progress) purchasing documents.
• MTL_UNITS_OF_MEASURE_TL (synonym) — referenced within a scalar subquery in the DECODE expression. When COL_NAME equals UNIT_MEAS_LOOKUP_CODE, the view resolves the descriptive unit of measure from this translation table filtered by the user's session language (USERENV('LANG')).
• PO_GEN_DIFF_PKG (package) — a Purchasing package associated with the generic difference/modification utilities that consume this view's attribute-value output for change detection.

The view therefore sits between the draft storage layer and the higher-level modification and comparison logic.

Key Columns

• PK1_VALUE (PO_HEADER_ID), PK2_VALUE (DRAFT_ID), PK3_VALUE (PO_LINE_ID), PK4_VALUE (LINE_LOCATION_ID) — the composite key identifying the owning header, draft, line, and shipment record. PK5_VALUE is exposed as NULL.
• COL_NAME — the name of the underlying shipment attribute being exposed (for example, NEED_BY_DATE, QUANTITY, RECEIPT_DAYS_EXCEPTION_CODE).
• COL_VALUE — the character representation of that attribute's value, produced by TO_CHAR conversion of the source column.
• COL_DESC — a descriptive value; populated only when COL_NAME is UNIT_MEAS_LOOKUP_CODE, in which case it returns the translated unit of measure description; otherwise it is NULL.

This structure makes the view a vertical (attribute-value) projection of the horizontal PO_LINE_LOCATIONS_DRAFT_ALL columns.

Common Use Cases and Queries

The view supports draft comparison, audit, and modification tooling, where individual changed attributes on a shipment must be identified and rendered. A typical query retrieves all attributes for a specific shipment:

• SELECT COL_NAME, COL_VALUE, COL_DESC FROM APPS.PO_SGD_MOD_LINELOCS_V WHERE PO_HEADER_ID = :header_id AND DRAFT_ID = :draft_id AND PO_LINE_ID = :line_id AND LINE_LOCATION_ID = :location_id;
• To isolate a single attribute, filter by COL_NAME, for example WHERE COL_NAME = 'NEED_BY_DATE'.
• To resolve unit-of-measure descriptions, filter on COL_NAME = 'UNIT_MEAS_LOOKUP_CODE' and read COL_DESC.

Developers should treat this view as an internal, read-only integration object. Its attribute-value design is well suited to generic difference engines but is not ideal for high-volume reporting, where querying PO_LINE_LOCATIONS_DRAFT_ALL directly is generally more efficient. Behavior is consistent across the documented 12.1.1 and 12.2.2 releases, as the view definition relies on standard draft tables and the units-of-measure translation table present in both.