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

4

Carol is right: joining PO_HEADERS works for regular receipts, but it doesn’t work for delivery changes and corrections in RCV_TRANSACTIONS. If you use average or periodic costing, ACTUAL_COST won’t always match the GL. You might want to change the “TRANSACTION_SOURCE_TYPE_ID” filter to get better tracking, especially for internal requests and transfers. Overall, a good query for PO receipts and RTVs, but it can be changed to fit more complicated needs.

5

Thanks, Carol, for reviewing and sharing your feedback. As mentioned, the queries are flexible and can be tailored to suit different customer needs. We’ve shared an updated version of the query for your reference.

SELECT

rct.transaction_id,

rct.transaction_type,

rct.transaction_date,

rct.quantity,

rct.UOM_CODE,

flv.meaning SOURCE_DOCUMENT,

rsh.receipt_num,

rsh.SHIPMENT_NUM,

–rsh.RECEIPT_NUM,

rsh.SHIPPED_DATE,

pha.segment1 AS po_number,

pla.line_num AS po_line_num,

esib.ITEM_NUMBER,

IMT.WAYBILL_AIRBILL,

IMT.NUMBER_OF_CONTAINERS,

IMT.SUBINVENTORY_CODE SUBINVENTORY,

IMT.REVISION ITEM_REVISION,

IMT.PRIMARY_QUANTITY,

IMT.TRANSACTION_UOM,

TRUNC(IMT.TRANSACTION_DATE) IMT_TRANSACTION_DATE,

IMT.CREATION_DATE,

IMT.ERROR_EXPLANATION,

IMT.VENDOR_LOT_NUMBER SUPPLIER_LOT,

NVL(imt.actual_cost,0) AS unit_cost,

NVL(imt.actual_cost,0) \* imt.primary_quantity AS value,

(

    SELECT SUM(NVL(xla.accounted_dr,0) - NVL(xla.accounted_cr,0))

    FROM   xla_distribution_links xdl,

           xla_ae_lines xla

    WHERE  1=1

    AND xdl.source_distribution_type = 'DELIVER'

    AND    xdl.source_distribution_id_num_1 = imt.transaction_id

    AND    xdl.ae_header_id = xla.ae_header_id

    AND    xdl.ae_line_num  = xla.ae_line_num

) AS amount

FROM

rcv_transactions rct,

FND_LOOKUP_VALUES_vl flv,

rcv_shipment_headers rsh,

RCV_SHIPMENT_LINES rsl,

–po_distributions_all pda,

po_lines_all pla,

po_headers_all pha,

INV_MATERIAL_TXNS imt,

egp_system_items_b esib

WHERE

rct.SOURCE_DOCUMENT_CODE = flv.lookup_code

AND flv.lookup_type = ‘RCV_SOURCE_DOCUMENT_TYPE’

AND rct.shipment_header_id = rsh.shipment_header_id

AND rct.shipment_line_id = rsl.shipment_line_id

AND rsh.shipment_header_id = rsl.shipment_header_id

– AND rsl.po_distribution_id = pda.po_distribution_id

AND rsl.po_line_id = pla.po_line_id(+)

AND pla.po_header_id = pha.po_header_id(+)

AND rct.transaction_id = imt.rcv_transaction_id

AND imt.inventory_item_id = esib.inventory_item_id

AND imt.organization_id = esib.organization_id