Search Results month_bucket




Overview
Explore in AI Chat

Overview

APPS.POA_PURCHASE_SALES_V is a Purchasing (PO) module view in Oracle E-Business Suite 12.1.1 and 12.2.2 that consolidates purchase-side and sales-side transaction values into a single reporting structure. It serves as a unified source for procurement analytics, spend reporting, and period-based aggregation of purchasing activity. The view is registered in the E-Business Suite Technical Reference Manual (ETRM) with a status of VALID and resides in the APPS schema, making it accessible to standard EBS responsibilities and custom reporting tools such as Oracle Reports, BI Publisher, and OBIEE.

The view is primarily intended to expose a PURCHASE_AMOUNT figure alongside a placeholder SALES_AMOUNT, enabling organizations to report on committed or received purchasing value by item, operating unit, currency, and period. The sales column is deliberately populated as TO_NUMBER(NULL), indicating the view was designed as an extension point for downstream or customer-specific logic rather than a delivered sales ledger.

Underlying Base Objects

Although the ETRM metadata records no explicitly documented base objects, the view definition draws from the following Purchasing and Receiving tables:

  • RCV_TRANSACTIONS (RT, RTP) — Receiving transactions, including receipts, returns to vendor, and corrections, along with their parent transactions.
  • PO_LINE_LOCATIONS_ALL (PLL) — Shipment and schedule line details, including price overrides, quantity, consigned flags, and drop-ship flags.
  • PO_LINES_ALL (POL) — Item-level purchase order lines and matching basis.
  • PO_HEADERS_ALL (POH) — PO header attributes such as currency code and type lookup code.

The view is a UNION ALL of two branches: a receipt-driven branch joined to RCV_TRANSACTIONS, and a shipment-driven branch for lines where RECEIPT_REQUIRED_FLAG is N. This dual structure allows the view to capture both received and directly approved purchase commitments.

Key Columns

  • PURCHASE_AMOUNT — The core measure. In the receipt branch it is derived from PRICE_OVERRIDE multiplied by the UOM-converted PRIMARY_QUANTITY, signed by transaction type (RECEIVE = 1, RETURN TO VENDOR = -1, CORRECT based on parent). In the shipment branch it is either AMOUNT − AMOUNT_CANCELLED (matching basis AMOUNT) or (QUANTITY − QUANTITY_CANCELLED) × PRICE_OVERRIDE.
  • SALES_AMOUNT — Always NULL in the delivered view.
  • MONTH_BUCKET — Transaction or creation date formatted as MONTH YYYY for period aggregation.
  • TRANSACTION_DATE — The receipt transaction date or line location creation date.
  • OU_ID — Operating unit identifier from PLL.ORG_ID.
  • ITEM_ID — Inventory item identifier.
  • CURRENCY — PO header currency code.

Common Use Cases and Queries

The view is used to analyze procurement spend by item, operating unit, and month, and to reconcile receipt-based purchasing against approved shipments. A typical query aggregating purchase value by period:

  • SELECT MONTH_BUCKET, OU_ID, SUM(PURCHASE_AMOUNT) FROM APPS.POA_PURCHASE_SALES_V WHERE CURRENCY = 'USD' GROUP BY MONTH_BUCKET, OU_ID;
  • Item-level spend analysis joining to MTL_SYSTEM_ITEMS_B on ITEM_ID.
  • Operating-unit comparisons using OU_ID filtered against HR_OPERATING_UNITS.
  • Period-over-period trending using TRANSACTION_DATE.

Because the view blends receipts and non-receipted shipments, care must be taken to avoid double counting where both conditions apply; filters on RECEIPT_REQUIRED_FLAG are honored internally by the UNION ALL structure.