INV Purchase Order Transactions (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Inventory (INV-Fusion-SQL's)
1

This INV Purchase Order Transactions query displays material transactions such as PO receipts, returns to vendor, and delivery adjustments for inventory organizations, helping users track procurement movements and financial valuation.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project)
  -- Displays PO-related inventory transactions and values for tracking and reconciliation.
  HAOU.NAME                                 INVENTORY_ORGANIZATION_NAME,
  -- Supplier Info
  PS.SEGMENT1                               SUPPLIER_NUMBER,
  PS.VENDOR_NAME 							SUPPLIER_NAME, 
  -- Purchase Order Info
  PH.SEGMENT1                               PURCHASE_ORDER_NUMBER,
  PL.LINE_NUM                               PO_LINE_NUMBER,
  -- Receipt / Shipment Info
  RSH.RECEIPT_NUM                           RECEIPT_NUMBER,
  RSH.SHIPMENT_NUM                          SHIPMENT_NUMBER,
  IMT.WAYBILL_AIRBILL,
  IMT.NUMBER_OF_CONTAINERS,
  RT.TRANSACTION_ID                         RECEIVING_TRANSACTION,
  -- Inventory / Item Info
  IMT.SUBINVENTORY_CODE                     SUBINVENTORY,
  IMT.REVISION                              ITEM_REVISION,
  IMT.PRIMARY_QUANTITY                      QUANTITY,
  NVL(IMT.ACTUAL_COST, 0)                   UNIT_COST,
  (NVL(IMT.ACTUAL_COST, 0) * IMT.PRIMARY_QUANTITY) VALUE,
  IMT.TRANSACTION_UOM,
  TRUNC(IMT.TRANSACTION_DATE)               TRANSACTION_DATE,
  IMT.CREATION_DATE,
  IMT.ERROR_EXPLANATION,
  IMT.VENDOR_LOT_NUMBER                     SUPPLIER_LOT,
  -- Project / Task Info
  PPAB.PROJECT_ID,
  PPAB.SEGMENT1                             PROJECT_NUMBER,
  PPAB.CREATED_BY                           PROJECT_NAME,
  PTV.TASK_ID,
  PTV.TASK_NUMBER,
  PTV.TASK_NAME,
  -- Transaction Info
  IMT.TRANSACTION_ID                        INVENTORY_TRANSACTION,
  IMT.TRANSACTION_REFERENCE,
  DECODE(IMT.COSTED_FLAG, 'N', 'No', NVL(IMT.COSTED_FLAG, 'Yes')) VALUED_FLAG,
  IMT.TRANSACTION_SOURCE_NAME,
  -- Inventory Organization Parameters (Lot/Serial control)
  IOP.LOT_NUMBER_GENERATION,
  IOP.LOT_NUMBER_UNIQUENESS,
  IOP.SERIAL_NUMBER_GENERATION,
  IOP.SERIAL_NUMBER_TYPE,
  IOP.SOURCE_TYPE,
  IOP.STOCK_LOCATOR_CONTROL_CODE,
  -- System / Language Info
  ESIT.LANGUAGE
FROM 
    INV_MATERIAL_TXNS                         IMT,
    INV_ORG_PARAMETERS                        IOP,
    PO_HEADERS                                PH,
    PO_LINES                                  PL,
    RCV_TRANSACTIONS                          RT,
    RCV_SHIPMENT_HEADERS                      RSH,
    POZ_SUPPLIERS_V                             PS,
    EGP_SYSTEM_ITEMS_TL                       ESIT,
    PJF_PROJECTS_ALL_B                        PPAB,
    PJF_TASKS_V                               PTV,
    INV_TRANSACTION_TYPES_B                   ITTB,
    HR_ALL_ORGANIZATION_UNITS                 HAOU,
    INV_TXN_SOURCE_TYPES_B                    ITSTB
WHERE 
    1 = 1
    AND IMT.ORGANIZATION_ID = IOP.ORGANIZATION_ID
    AND IMT.TRANSACTION_SOURCE_TYPE_ID = 1
    AND PH.PO_HEADER_ID = IMT.TRANSACTION_SOURCE_ID
    AND RT.TRANSACTION_ID = IMT.RCV_TRANSACTION_ID
    AND PL.PO_LINE_ID = RT.PO_LINE_ID
    AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
    AND PS.VENDOR_ID = PH.VENDOR_ID
    AND ESIT.LANGUAGE(+) = USERENV('LANG')
    AND ESIT.INVENTORY_ITEM_ID(+) = IMT.INVENTORY_ITEM_ID
    AND ESIT.ORGANIZATION_ID(+) = IMT.ORGANIZATION_ID
    AND IMT.PROJECT_ID = PPAB.PROJECT_ID(+)
    AND IMT.TASK_ID = PTV.TASK_ID(+)
    AND IMT.TRANSACTION_TYPE_ID = ITTB.TRANSACTION_TYPE_ID(+)
    AND HAOU.ORGANIZATION_ID = IMT.ORGANIZATION_ID
    AND ITSTB.TRANSACTION_SOURCE_TYPE_ID = IMT.TRANSACTION_SOURCE_TYPE_ID 
	ORDER BY 	PS.SEGMENT1,
				PH.SEGMENT1  ,
				PL.LINE_NUM
2

This INV Purchase Order Transactions query is fine, but I have a few quick flags.

Joining PO_HEADERS with TRANSACTION_SOURCE_ID works for regular receipts, but delivery changes and corrections can still get through, depending on how they were handled in RCV_TRANSACTIONS.

ACTUAL_COST is also fine for valuing receipts, but don’t expect it to match up perfectly with GL in average or periodic costing, even with COSTED_FLAG.

TRANSACTION_SOURCE_TYPE_ID = 1 It correctly limits to PO activity, but keep in mind that it doesn’t include internal requests and transfers that some users expect.

Overall, it’s good for receiving POs and analyzing RTVs, especially for reconciliation views.

3

Hello Carol,

Thanks so much for reviewing the query and sharing your thoughts. We really appreciate your input. Yes, the query can absolutely be customized to meet different customer needs.

Regards