Query to get costed Inventory Transaction Details

 SELECT ESIB.ITEM_NUMBER ITEM,

ESITL.DESCRIPTION DESCRIPTION,

(

SELECT DISTINCT eirb.CROSS_REFERENCE

FROM EGP_ITEM_RELATIONSHIPS_B eirb

WHERE eirb.INVENTORY_ITEM_ID = esib.INVENTORY_ITEM_ID

AND (

CASE 

WHEN ORG_INDEPENDENT_FLAG = 'Y'

THEN 1

WHEN eirb.ORGANIZATION_ID = esib.ORGANIZATION_ID

THEN 1

ELSE 0

END

) = 1

AND eirb.SUB_TYPE = 'SOURCE_REF'

AND eirb.ITEM_RELATIONSHIP_TYPE = 'ITEM_XREF'

) ITEM_REF,

INV_ORG.ORGANIZATION_NAME WAREHOUSE,

IMT.TRANSACTION_ID TRANSACTION_NUM,

IMT.TRANSACTION_DATE TRANSACTION_DATE,

PRIM_UOM.UNIT_OF_MEASURE AS ITEM_PRIMARY_UOM,

IMT.TRANSACTION_QUANTITY,

IUOMTL.UNIT_OF_MEASURE TRX_UOM,

ITST.TRANSACTION_SOURCE_TYPE_NAME TRX_SOURCE,

(

SELECT sum(unit_cost)

FROM (

SELECT DISTINCT COST_ELEMENT_ID,

unit_cost

FROM CST_TRANSACTIONS it,

CST_LAYER_COSTS tc,

CST_INV_TRANSACTIONS CIT

WHERE 1 = 1

AND it.TRANSACTION_ID = tc.TRANSACTION_ID

AND CIT.CST_INV_TRANSACTION_ID = IT.CST_INV_TRANSACTION_ID

AND cit.EXTERNAL_SYSTEM_REF_ID = to_char(IMT.TRANSACTION_ID)

)

) unit_cost,

ITT.TRANSACTION_TYPE_NAME TRANSACTION_TYPE

FROM INV_MATERIAL_TXNS IMT,

EGP_SYSTEM_ITEMS_B ESIB,

EGP_SYSTEM_ITEMS_TL ESITL,

INV_UNITS_OF_MEASURE_B IUOMB,

INV_UNITS_OF_MEASURE_TL IUOMTL,

INV_UNITS_OF_MEASURE_VL PRIM_UOM,

INV_ORGANIZATION_DEFINITIONS_V INV_ORG,

INV_TXN_SOURCE_TYPES_VL ITST,

INV_TRANSACTION_TYPES_VL ITT

WHERE 1 = 1

AND IMT.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID

AND IMT.ORGANIZATION_ID = ESIB.ORGANIZATION_ID

AND ESIB.INVENTORY_ITEM_ID = ESITL.INVENTORY_ITEM_ID

AND ESIB.ORGANIZATION_ID = ESITL.ORGANIZATION_ID

AND ESITL.LANGUAGE = 'US'

AND ESIB.ORGANIZATION_ID = INV_ORG.ORGANIZATION_ID(+)

AND IMT.TRANSACTION_UOM = IUOMB.UOM_CODE

AND IUOMB.UNIT_OF_MEASURE_ID = IUOMTL.UNIT_OF_MEASURE_ID

AND IUOMTL.LANGUAGE = 'US'

AND PRIM_UOM.UOM_CODE(+) = ESIB.PRIMARY_UOM_CODE

AND ITST.TRANSACTION_SOURCE_TYPE_ID(+) = IMT.TRANSACTION_SOURCE_TYPE_ID

AND ITT.TRANSACTION_TYPE_ID(+) = IMT.TRANSACTION_TYPE_ID

AND IMT.COSTED_FLAG IS NULL