Results for “supplier_ref_number”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The APPS.PO_CXML_LINE_LOC_ARCH_V view is a Purchasing (PO) module reporting object in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes archived purchase order shipment and line-location information in a denormalized, CXML-oriented format intended for supplier integration, punchout/extract processing, and historical procurement reporting. The view resolves shipment-level attributes from the archive shipment table, joins corresponding line and header attributes, and enriches the result with item, unit of measure, ship-to organization, currency, and hazardous material descriptors. It is registered in ETRM with status VALID under the APPS schema.

Because the view reads from the archive table PO_LINE_LOCATIONS_ARCHIVE_ALL rather than the live PO_LINE_LOCATIONS_ALL, its result set reflects the state of purchase order shipments captured in the Purchasing archive, making it suitable for audit, historical reconciliation, and integration extracts where the current transactional record may have changed. The name prefix "CXML" indicates the column set was shaped to supply Commerce XML (cXML) order and shipment payloads, and it is one of the archived counterparts to the live shipment extract views used by the Purchasing document publishing and supplier integration flows.

Underlying Base Objects

The view text joins the following documented base objects:

• PO_LINE_LOCATIONS_ARCHIVE_ALL — primary driver; supplies header, release, line, location, quantity, date, price, and tax attributes (aliased PLLA).
• PO_LINES_ALL — line-level details including line number, item, revision, description, and note to vendor (PLA).
• FINANCIALS_SYSTEM_PARAMS_ALL — used twice to derive the buying organization currency and default inventory organization (FSPA, FSP).
• HR_ORGANIZATION_UNITS_V — resolves the ship-to organization name (HOU_V).
• MTL_UNITS_OF_MEASURE_TL — supplies the translated unit of measure code (MUOMTL), filtered by USERENV('LANG').
• MTL_SYSTEM_ITEMS_B_KFV — provides the concatenated item segment value (MSIV).
• PO_HAZARD_CLASSES — supplies the hazard class description (PHC).

ETRM additionally records package dependencies on HR_API, HR_GENERAL, and HR_SECURITY, which are invoked implicitly through the HR organization units view for security and organization resolution. The join predicates enforce outer joins on the item and hazard class relationships, while requiring that net ordered quantity (QUANTITY minus QUANTITY_CANCELLED) be greater than zero, so fully cancelled shipments are excluded. The result set is ordered by PO_HEADER_ID, PO_LINE_ID, and LINE_LOCATION_ID.

Key Columns

Prominent columns include PO_HEADER_ID, REVISION_NUM, PO_RELEASE_ID, PO_LINE_ID, LINE_LOCATION_ID, LINE_NUM, and SHIPMENT_NUM, which form the key hierarchy of the extract. ORDERED_QUANTITY is computed as QUANTITY less QUANTITY_CANCELLED, and UOM carries the translated unit of measure code.

Supplier-facing and scheduling attributes include NEED_BY_DATE, PROMISED_DATE, LAST_ACCEPT_DATE, and SHIPTO_ORG_NAME. Pricing and tax attributes include PRICE_OVERRIDE, TAXABLE_FLAG, TAX_CODE_ID, and the column most directly relevant to the search term ESTIMATED_TAX_AMOUNT, sourced from PLLA.ESTIMATED_TAX_AMOUNT, alongside TAX_NAME. Item and descriptive columns include ITEM, ITEMX (vendor product number), ITEMRV/ITEMRVX (item revision), DESCRIPTION, NOTE_TO_VENDOR, and HAZRDMTL for the hazard class.

Note the fix-up in the view definition: the tax amount column is written as "PLLA. ESTIMATED_TAX_AMOUNT" with a space after the qualifier dot — functionally equivalent to PLLA.ESTIMATED_TAX_AMOUNT, but worth recognizing when reviewing view DDL. The final SELECT also includes three literal placeholders ('LINENUM', 'SHIPMENTNUM', 'BUYERPARTNUM'), which are static strings in the view text rather than dynamic values.

Common Use Cases and Queries

Typical uses include extracting archived PO shipment lines for cXML order acknowledgement or invoice matching, reconciling estimated tax against actual tax lines, and historical spend reporting by supplier, ship-to organization, or item. A basic query to retrieve shipments with their estimated tax is:

• SELECT po_header_id, po_line_id, line_location_id, shipment_num, ordered_quantity, uom, estimated_tax_amount, tax_name FROM apps.po_cxml_line_loc_arch_v WHERE estimated_tax_amount IS NOT NULL;
• To review tax exposure by buying currency: SELECT buying_org_currency, SUM(estimated_tax_amount) FROM apps.po_cxml_line_loc_arch_v GROUP BY buying_org_currency;
• To list shipments needing acknowledgement: SELECT po_header_id, shipment_num, need_by_date, promised_date, shipto_org_name FROM apps.po_cxml_line_loc_arch_v WHERE promised_date > need_by_date;

Because the view is defined over archive tables and uses USERENV('LANG') for UOM translation, callers should confirm the session language and expect performance characteristics similar to a multi-table archive join. For ETRM purposes, ESTIMATED_TAX_AMOUNT is confirmed as a direct, unaliased passthrough of the archived shipment column.