This INV Account Transactions query displays detailed account transaction data, including item costs, quantities, and organizational accounting segments, for inventory material transactions over all available periods, helping users analyze financial and inventory movements to support accounting reconciliation and operational decision-making.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Displays inventory account transactions with costs and quantities for analysis.
HAOU.NAME ORGANIZATION_NAME,
ESIB.ITEM_NUMBER,
IMT.SUBINVENTORY_CODE SUBINVENTORY_CODE,
-- GL / Accounting Information
GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' ||
GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' ||
GCC.SEGMENT5 || '.' || GCC.SEGMENT6 GL_ACCOUNT,
-- Transaction Details
IMT.TRANSACTION_DATE TRANSACTION_DATE,
IMT.CREATION_DATE TRANSACTION_CREATION_DATE,
IMT.TRANSACTION_REFERENCE TRANSACTION_REFERENCE,
IMT.PRIMARY_QUANTITY TRANSACTION_QUANTITY,
IMT.ACTUAL_COST TRANSACTION_ITEM_COST,
IMT.REVISION ITEM_REVISION,
NVL(IMT.COSTED_FLAG, 'Y') COSTED_FLAG,
-- Stock Locator
ITL.SEGMENT1 || '-' || ITL.SEGMENT2 || '-' || ITL.SEGMENT3 STOCK_LOCATOR_CODE,
-- Item Attributes
ESIB.BOM_ITEM_TYPE BILL_OF_MATERIAL_ITEM_TYPE,
ESIB.SOURCE_TYPE ITEM_SOURCE_TYPE,
ESIB.WIP_SUPPLY_TYPE WORK_ORDER_SUPPLY_TYPE,
ESIB.RESERVABLE_TYPE RESERVABLE_TYPE,
ESIB.LOCATION_CONTROL_CODE LOCATION_CONTROL_CODE,
ESIB.INVENTORY_PLANNING_CODE INVENTORY_PLANNING_CODE,
ESIB.ALLOWED_UNITS_LOOKUP_CODE ALLOWED_UNITS,
ESIB.ATO_FORECAST_CONTROL ATO_FORECAST_CONTROL,
ESIB.AUTO_REDUCE_MPS AUTO_REDUCE_MPS_FLAG
FROM
INV_MATERIAL_TXNS IMT,
EGP_SYSTEM_ITEMS_B ESIB,
GL_CODE_COMBINATIONS GCC,
INV_ITEM_LOCATIONS ITL,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND IMT.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
AND IMT.ORGANIZATION_ID = ESIB.ORGANIZATION_ID
AND IMT.DISTRIBUTION_ACCOUNT_ID = GCC.CODE_COMBINATION_ID
AND IMT.LOCATOR_ID = ITL.INVENTORY_LOCATION_ID(+)
AND IMT.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
ORDER BY HAOU.NAME
,ESIB.ITEM_NUMBER;