INV Physical Inventory Transactions (Oracle Fusion SQL)

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

This INV physical inventory transactions query displays material adjustment transactions for inventory items across organizations resulting from physical inventory counts, helping users track and audit inventory discrepancies.

#sqlquery

SELECT 
  -- SQL4Fusion (An Orbit Analytics Project)
  -- Tracks inventory adjustments from physical counts across items and organizations.
    HAOU.NAME                       ORGANIZATION_NAME,
    -- ITEM INFO
    ESIT.DESCRIPTION                ITEM_DESCRIPTION,
    -- TRANSACTION INFO
    ITTV.TRANSACTION_TYPE_NAME,	
    IMT.TRANSACTION_REFERENCE,
    IMT.TRANSACTION_DATE,
    IMT.CREATION_DATE,
    IMT.CREATED_BY,
    -- QUANTITY & UOM
    IMT.PRIMARY_QUANTITY,
    IMT.TRANSACTION_UOM,
    -- LOCATION INFO
    IMT.SUBINVENTORY_CODE
FROM 
    INV_MATERIAL_TXNS                  IMT,
    INV_TRANSACTION_TYPES_B            ITTB,
    INV_TRANSACTION_TYPES_VL           ITTV,
    HR_ALL_ORGANIZATION_UNITS          HAOU,
    EGP_SYSTEM_ITEMS_TL                ESIT
WHERE 
    IMT.TRANSACTION_TYPE_ID                 = ITTB.TRANSACTION_TYPE_ID
    AND IMT.TRANSACTION_SOURCE_TYPE_ID      = ITTV.TRANSACTION_TYPE_ID
    AND IMT.ORGANIZATION_ID                 = HAOU.ORGANIZATION_ID
    AND IMT.INVENTORY_ITEM_ID               = ESIT.INVENTORY_ITEM_ID
    AND IMT.ORGANIZATION_ID                 = ESIT.ORGANIZATION_ID
    AND ESIT.LANGUAGE                       = USERENV('LANG')
    AND IMT.TRANSACTION_SOURCE_TYPE_ID      = 8;