Search Results packaging_code_ext

Overview

CLN_XML_ITEMS2_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is a component of the CLN product module, Supply Chain Trading Connector for RosettaNet, and is documented as VALID in the ETRM repository. The view supplies the SHIPITEM items section of the OAG SHOW_SHIPMENT_005 XML payload, the standard RosettaNet business message used to communicate shipment contents between trading partners.

Rather than presenting data from a single transactional entity, the view assembles a shipment-line-level result set by joining order management, shipping execution, inventory, and return material authorization tables. Its distinct purpose is to flatten that multi-table structure into the column shape expected by the RosettaNet XML mapping layer, so that outbound SHOW_SHIPMENT_005 messages can be generated without further transformation logic outside the connector. It is a read-only reporting and integration object; no DML is performed against it, and all maintenance occurs in the underlying base tables.

Underlying Base Objects

The view is defined over the following documented base objects:

• MTL_CUSTOMER_ITEMS (SYNONYM) — supplies the customer-facing part number.
• MTL_ITEM_FLEXFIELDS (VIEW) — supplies the internal supplier item number.
• OE_ORDER_HEADERS_ALL (SYNONYM) — order header context, including the sales order number.
• OE_ORDER_LINES_ALL (SYNONYM) — order line attributes such as line number, source document reference, and customer shipment number.
• RLM_SCHEDULE_LINES_ALL (SYNONYM) — RMA schedule line attributes, including country of origin, customer item revision, and packaging code.
• WSH_DELIVERY_ASSIGNMENTS (SYNONYM) — links deliveries to delivery details and provides the container instance identifier.
• WSH_DELIVERY_DETAILS (SYNONYM) — the primary shipped-line detail source, including quantities, UOMs, lot number, serial number, and descriptive flexfield attributes.
• WSH_DSNO_PACKED_QUANTITY_V (VIEW) — a shipping execution view supplying packed quantities per delivery detail.
• WSH_NEW_DELIVERIES (SYNONYM) — the delivery header, supplying the delivery identifier.

The joins follow the shipment chain: delivery header to delivery assignment, assignment to delivery detail, then lateral linkage to the originating sales order line and, where applicable, the RMA schedule line. The presence of DISTINCT in the view text indicates the join path can produce duplicate rows, which the view eliminates before the XML writer consumes the result set.

Key Columns

Columns fall into several functional groups:

• Shipment identity — DELIVERY_ID, CONTAINER_INSTANCE_ID (aliased from the parent delivery detail ID), which anchor each SHIPITEM entry.
• Part identification — CUSTOMER_PART_NUMBER, SUPPLIER_PART_NUMBER, CUSTOMER_ITEM_REVISION, CUSTOMER_MODEL_SERIAL_NUMBER, and LOT_NUMBER.
• Quantities and UOMs — SHIPPED_QUANTITY, PACKED_QUANTITY, REQUESTED_QUANTITY, ORDERED_QUANTITY, with corresponding ITEM_UOM_CODE_INT, ORDER_QUANTITY_UOM_INT, and related internal UOM codes.
• Order references — SALES_ORDER_NUM, SALES_ORDER_LINE_NUM, SALES_ORDER_HEADER_ID, SALES_ORDER_LINE_ID, CUSTOMER_LINE_NUMBER, ORDER_LINE_NUMBER, ORIG_SYS_DOCUMENT_REF, and CUSTOMER_SHIPMENT_NUMBER.
• Packaging and compliance — PACKAGING_CODE_EXT and COUNTRY_OF_ORIGIN_EXT, both sourced from the RMA schedule line, plus SHIPPING_INSTRUCTIONS and a hardcoded HAZARDOUS_MATERIAL_CODE_INT of 'UN'.
• Descriptive flexfields — ATTRIBUTE_CATEGORY along with ATTRIBUTE1 through ATTRIBUTE15 carried from WSH_DELIVERY_DETAILS, allowing site-specific data to pass through into the payload.
• Sequencing and flags — SORT_ORDER and a constant MINOR_LINE_INDICATOR of 'N'.

The user search term packaging_code_ext resolves to the column RSL.PACKAGING_CODE_EXT PACKAGING_CODE_EXT, populated from RLM_SCHEDULE_LINES_ALL, indicating its relevance to RMA-linked return or repair shipment lines rather than conventional outbound sales order shipments.

Common Use Cases and Queries

Primary use is diagnostic: validating the data that the RosettaNet connector will emit for a given delivery. A typical query filters by delivery and inspects packaging and part attributes:

• SELECT container_instance_id, delivery_id, customer_part_number, supplier_part_number, shipped_quantity, packaging_code_ext WHERE delivery_id = :p_delivery_id
• SELECT sales_order_num, sales_order_line_num, customer_line_number, shipped_quantity, packed_quantity ORDER BY sort_order for order-to-shipment reconciliation.
• SELECT DISTINCT packaging_code_ext, country_of_origin_ext to review the distinct packaging and origin values present on a shipment.
• SELECT lot_number, customer_model_serial_number, attribute1..attribute15 for traceability and flexfield data verification.

The view is also used when troubleshooting connector errors, since a missing or null PACKAGING_CODE_EXT frequently surfaces as an incomplete SHIPITEM segment in the generated XML.