Search Results ci_status

Overview

APPS.PA_CI_SUPPLIER_DETAILS_V is a reporting and integration view in Oracle E-Business Suite that consolidates supplier-related change order (Control Item) information originating from Oracle Purchasing and Project Accounting. The view joins supplier detail rows stored in PA_CI_SUPPLIER_DETAILS to Purchasing document headers and lines, vendor master records, and the parent Control Item record, thereby exposing a denormalized, business-friendly result set that includes PO numbers, vendor names, currency, change amounts, and change descriptions.

The view is defined as a UNION of two queries. The first query returns rows where the supplier detail is associated with a valid purchase order header and line, resolving POH.SEGMENT1 as PO_NUMBER, POL.LINE_NUM as PO_LINE_NUM, and POH.ORG_ID as PO_ORG_ID. The second query returns rows where PO_HEADER_ID is NULL, substituting NULL for PO_NUMBER, 0 for PO_LINE_NUM, and 0 for PO_ORG_ID. This design ensures that supplier change items not tied to a purchase order are still reported, with placeholder values for the purchasing columns.

Underlying Base Objects

The view is constructed over the following documented objects: PA_CI_SUPPLIER_DETAILS (synonym), PA_CONTROL_ITEMS (synonym), PO_HEADERS_ALL (synonym), PO_LINES_ALL (synonym), and PO_VENDORS (view). Additional ETRM metadata also references PA_CI_TYPES_VL, PA_RESOURCE_LIST_MEMBERS, and PA_TASKS as related base objects for the Control Item framework.

The core driving table is PA_CI_SUPPLIER_DETAILS, aliased SI, which stores supplier-specific change transactions. It is joined to PA_CONTROL_ITEMS (CI) on CI_ID to obtain the change type, project, and status. It is joined to PO_HEADERS_ALL (POH) and PO_LINES_ALL (POL) on PO_HEADER_ID and PO_LINE_ID to resolve purchasing document context, and to PO_VENDORS (POV) on VENDOR_ID to obtain the supplier name.

Key Columns

• CI_TRANSACTION_ID, CI_ID, CI_TYPE_ID — Identify the supplier change transaction and its parent Control Item and type.
• VENDOR_ID, VENDOR_NAME — Supplier identifier and descriptive name from PO_VENDORS.
• PO_HEADER_ID, PO_LINE_ID, PO_NUMBER, PO_LINE_NUM — Purchasing document references; PO_NUMBER and PO_LINE_NUM are NULL/0 for non-PO rows.
• PO_ORG_ID — Operating unit (ORG_ID) from PO_HEADERS_ALL, returned as 0 when no PO header exists. This is the column users search on for "po_org_id".
• CHANGE_TYPE, CHANGE_DESCRIPTION, CHANGE_AMOUNT, CURRENCY_CODE — Nature, description, value, and currency of the supplier change.
• CI_STATUS — Status code of the parent Control Item, sourced from PA_CONTROL_ITEMS.STATUS_CODE.
• PROJECT_ID — Project context from PA_CONTROL_ITEMS.PROJECT_ID.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard audit columns.

Common Use Cases and Queries

This view is typically used in supplier change order reports, Project Accounting reconciliation, and custom integrations that need to link project control items back to purchasing documents and vendors. Filtering by PO_ORG_ID allows reporting per operating unit.

Sample query by operating unit:

SELECT ci_id, ci_transaction_id, vendor_name, po_number, po_line_num,
       po_org_id, change_type, change_amount, currency_code, ci_status, project_id
FROM   apps.pa_ci_supplier_details_v
WHERE  po_org_id = :org_id
ORDER BY po_number, po_line_num;

Sample query isolating non-PO supplier changes:

SELECT ci_id, vendor_name, change_type, change_amount, ci_status, project_id
FROM   apps.pa_ci_supplier_details_v
WHERE  po_org_id = 0
AND    po_header_id IS NULL;