Search Results ic_purg_vw1




Overview
Explore in AI Chat

Overview

IC_PURG_VW1 is an APPS-owned view within the GMI - Process Manufacturing Inventory module of Oracle E-Business Suite, documented as VALID in ETRM for releases 12.1.1 and 12.2.2. Its description, "Transaction purge view," identifies its purpose: presenting a consolidated, cross-functional register of documents eligible for purge processing. The view is a UNION of header records drawn from batch, purchase order, receiving, inventory transaction, and process order tables, each filtered to a status that signals the document is closed, completed, or otherwise eligible for archival or deletion. Because no single EBS table spans these diverse document families, IC_PURG_VW1 provides a single read-only access point for purge programs and reconciliation reports that must enumerate purgeable transactions across production, procurement, receiving, and inventory.

In the user's search context of "ship_status," the view is directly relevant because two of its UNION branches reference SHIP_STATUS in OP_ORDR_DTL. Those branches exclude process orders and bills of lading that have detail lines with SHIP_STATUS below 20 and an incomplete status, ensuring that partially shipped or open shipment lines are not swept into purge processing.

Underlying Base Objects

The documented base objects are APPS synonyms: IC_TRAN_CMP, OP_BILL_LAD, OP_ORDR_DTL, OP_ORDR_HDR, PM_BTCH_HDR, PO_ORDR_HDR, and PO_RECV_HDR. The UNION comprises six branches, each tagged with a literal DOC_TYPE value:

  • PROD — PM_BTCH_HDR where BATCH_STATUS = 4 (closed batches).
  • PORD — PO_ORDR_HDR where PO_STATUS = 20 (closed purchase orders).
  • RECV — PO_RECV_HDR where DELETE_MARK = 0 (active receipt headers).
  • Inventory and process transactions — IC_TRAN_CMP filtered on DOC_TYPE in ('ADJI','ADJR','GRDI','GRDR','STSI','STSR','TRNI','TRNR'), covering adjustments, goods receipts, stock transfers, and transfer issues/receipts.
  • OPSO (orders) — OP_ORDR_HDR where DELETE_MARK = 0, COMPLETED_IND >= 0, and ORDER_ID not in any OP_ORDR_DTL line having SHIP_STATUS < 20 with COMPLETED_IND = 0.
  • OPSO (bills of lading) — OP_BILL_LAD where DELETE_MARK = 0 and BOL_ID not referenced by any OP_ORDR_DTL line with SHIP_STATUS < 20 and COMPLETED_IND = 0.

The commented-out ORDER_TYPE = 1 predicate in the order branch indicates a previously narrower scope that was deliberately broadened to include all process order types.

Key Columns

The view projects five columns from heterogeneous sources, aligned positionally across the UNION:

  • DOC_TYPE — document classification tag ('PROD', 'PORD', 'RECV', 'OPSO', or the underlying IC_TRAN_CMP type such as 'ADJI', 'GRDI', 'STSI', 'TRNI').
  • ORGN_CODE — the organization (plant or warehouse) that owns the document.
  • DOC_NO — the human-readable document number (batch, PO, receipt, BOL, or order number). Inventory transaction rows supply an empty string here.
  • DOC_ID — the internal surrogate key (BATCH_ID, PO_ID, RECV_ID, ORDER_ID, BOL_ID, or IC_TRAN_CMP DOC_ID) used by purge routines for row-level operations.
  • DOC_DATE — the reference date, populated from LAST_UPDATE_DATE, RECV_DATE, or TRANS_DATE depending on the source branch.

Common Use Cases and Queries

The principal use case is driving purge or archive processing. A purge program selects candidate documents by organization and age, then uses DOC_ID with DOC_TYPE to invoke the appropriate deletion logic. A second use case is reconciliation reporting: counting closed documents by type and organization before a purge window.

To identify purgeable shipment-related documents while honoring SHIP_STATUS logic:

  • SELECT doc_type, orgn_code, doc_no, doc_id, doc_date FROM apps.ic_purg_vw1 WHERE doc_type = 'OPSO' AND orgn_code = :org AND doc_date < :cutoff;
  • SELECT doc_type, COUNT(*) FROM apps.ic_purg_vw1 GROUP BY doc_type;

Because the OPSO branches already exclude orders and BOLs whose detail lines show SHIP_STATUS below 20 with COMPLETED_IND = 0, the view itself enforces the shipping-completion rule; querying OP_ORDR_DTL separately is unnecessary unless line-level detail is required.