Search Results closed_code

Overview

JAI_PO_RFQQT_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the JA (Asia/Pacific Localizations) product family and consolidates purchasing document line and shipment information from the Oracle Purchasing core tables into a single, denormalized result set. The view is principally intended to support localizations reporting and inquiry functions that require a flattened view of purchase order, RFQ, quotation, blanket, and planned order data across both line and shipment (line location) levels.

Because it is a standard APPS view, it can be queried directly from SQL*Plus, Oracle Reports, BI Publisher, or OAF-based extensions, and it participates in the standard EBS security model for APPS-owned objects. Its status is VALID and it exposes seventeen columns, including the CLOSED_CODE column that surfaces the shipment-level closed status from PO_LINE_LOCATIONS_ALL.

Underlying Base Objects

The view is defined over three Purchasing base tables, accessed through APPS synonyms:

• PO_HEADERS_ALL — the header source, aliased as C in the first two UNION ALL branches and as PHA in the third. It supplies TYPE_LOOKUP_CODE, which drives the shipment-type filtering logic.
• PO_LINES_ALL — aliased as A (branches one and two) and PLA (branch three), contributing PO_HEADER_ID, PO_LINE_ID, LINE_NUM, ITEM_ID, ITEM_DESCRIPTION, UNIT_MEAS_LOOKUP_CODE, UNIT_PRICE, and QUANTITY.
• PO_LINE_LOCATIONS_ALL — aliased as B, providing LINE_LOCATION_ID, SHIPMENT_NUM, QUANTITY, PRICE_OVERRIDE, SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID, NEED_BY_DATE, CANCEL_FLAG, and CLOSED_CODE.

The definition is a three-branch UNION ALL. The first branch returns normal shipments where SHIPMENT_TYPE is neither 'SCHEDULED' nor 'BLANKET' and excludes the BLANKET / PRICE BREAK combination. The second branch returns price-break shipments for blanket agreements where QUANTITY is non-zero. The third branch returns header-and-line rows for documents whose TYPE_LOOKUP_CODE is BLANKET, PLANNED, QUOTATION, or RFQ, supplying literal zeros for the shipment attributes and NULL for CLOSED_CODE. Cancelled shipments (CANCEL_FLAG = 'Y') are excluded from the first two branches.

Key Columns

• PO_HEADER_ID, PO_LINE_ID — composite keys linking back to the purchasing document and line.
• LINE_NUM, ITEM_ID, ITEM_DESCRIPTION — line-level identification and description.
• L_UOM, L_QTY, UNIT_PRICE — line-level unit of measure, quantity, and price.
• LINE_LOCATION_ID, SHIPMENT_NUM — shipment identity; zero in the third branch.
• LL_UOM, LL_QTY, PRICE_OVERRIDE — shipment-level UOM (defaulting to the line UOM via DECODE when null), shipment quantity, and price override.
• SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID — ship-to destination for the shipment.
• NEED_BY_DATE — shipment need-by date, converted to character with TO_CHAR.
• CLOSED_CODE — the shipment closed status sourced from PO_LINE_LOCATIONS_ALL.CLOSED_CODE. Values include OPEN, CLOSED, CLOSED FOR RECEIVING, CLOSED FOR INVOICING, and FINALLY CLOSED; it is NULL for the header-level rows produced by the third UNION ALL branch.

Common Use Cases and Queries

Typical uses include identifying shipments still open for receiving or invoicing, reporting RFQ and quotation activity, and extracting purchasing detail for Asia/Pacific localization reports.

Retrieve all shipments for a given purchase order:

SELECT po_header_id, line_num, shipment_num, ll_qty, closed_code
FROM   apps.jai_po_rfqqt_v
WHERE  po_header_id = :p_po_header_id
ORDER  BY line_num, shipment_num;

List shipments not yet closed:

SELECT po_header_id, line_num, shipment_num, ll_qty, need_by_date
FROM   apps.jai_po_rfqqt_v
WHERE  closed_code IS NOT NULL
AND    closed_code NOT IN ('CLOSED','FINALLY CLOSED');

Report blanketed items and price-break quantities:

SELECT po_header_id, line_num, item_description, ll_qty, unit_price
FROM   apps.jai_po_rfqqt_v
WHERE  shipment_num = 'PRICE BREAK';

Count documents by closed status for localization dashboards:

SELECT closed_code, COUNT(*) 
FROM   apps.jai_po_rfqqt_v
GROUP  BY closed_code;