INV Lot Transactions (Oracle Fusion SQL)

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

This INV lot transactions query displays lot-controlled material transactions for organizations, helping users track and analyze inventory movement by lot.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project)
  -- Lot-controlled material transactions by organization for inventory tracking.
    HOU.NAME						ORGANIZATION_NAME,
	-- Item Details
    ESIB.ITEM_NUMBER,
    ESIT.DESCRIPTION 				 ITEM_DESCRIPTION,
    ESIB.ITEM_TYPE,
    ESIB.INVENTORY_ITEM_STATUS_CODE  ITEM_STATUS,
    ESIB.PRIMARY_UOM_CODE,	
    -- Lot Details
    ILN.LOT_NUMBER,
    -- Transaction Details
    ITLN.TRANSACTION_ID,
    ITLN.TRANSACTION_DATE,
    ITLN.TRANSACTION_SOURCE_NAME,
    ITLN.TRANSACTION_QUANTITY
FROM
    INV_TRANSACTION_LOT_NUMBERS 	ITLN,
    INV_LOT_NUMBERS 				ILN,
    EGP_SYSTEM_ITEMS_B 				ESIB,
    EGP_SYSTEM_ITEMS_TL 			ESIT,
    HR_ORGANIZATION_UNITS 			HOU
WHERE
    ITLN.INVENTORY_ITEM_ID 			= ILN.INVENTORY_ITEM_ID
    AND ITLN.LOT_NUMBER 			= ILN.LOT_NUMBER
    AND ITLN.ORGANIZATION_ID 		= ILN.ORGANIZATION_ID
    AND ESIB.INVENTORY_ITEM_ID 		= ITLN.INVENTORY_ITEM_ID
    AND ESIB.ORGANIZATION_ID 		= ITLN.ORGANIZATION_ID
    AND ESIT.INVENTORY_ITEM_ID 		= ESIB.INVENTORY_ITEM_ID
    AND ESIT.ORGANIZATION_ID 		= ESIB.ORGANIZATION_ID
    AND HOU.ORGANIZATION_ID 		= ITLN.ORGANIZATION_ID
    AND ESIT.LANGUAGE 				= 'US';