ECC Enterprise Asset Management, Stores Materials (Maintenance)

Description
Categories: Enterprise Command Center
Columns: Material Name, Organization Id, Inv Organization Code, Material Uom, Inventory Item Id, Cross Ref Id, Cross Reference, Cross Ref Type, Manufacturer Id, Manufacturer Name ...
Imported from Enterprise Command Center
Dataset Key: eam-store-mat
Query Procedure: EAM_ECC_MAT_UTIL_PVT.GET_ECC_STORES_MAT_DL_INFO
Security Procedure:
select
x.*
from
(
SELECT * from ( Select /*+ leading(eccsm,msi,msitl)  cardinality(eccsm 1000) */
       eccsm.MATERIAL_NAME ,
	msitl.description MATERIAL_DESCRIPTION ,
	eccsm.ORGANIZATION_ID  ,
	eccsm.INV_ORGANIZATION_CODE  ,
	eccsm.MATERIAL_UOM,
	eccsm.INVENTORY_ITEM_ID  ,
	(select LOOKUP_VALUE from eam_ecc_tl_staging where LOOKUP_TYPE='EAM_ITEM_TYPE' and LANGUAGE=msitl.language and LOOKUP_CODE=eccsm.eam_item_type) as EAM_ITEM_TYPE ,
	eccsm.CROSS_REF_ID  ,
	eccsm.CROSS_REFERENCE ,
       (select mcrtl.description from mtl_cross_references_tl mcrtl where mcrtl.cross_reference_id = eccsm.CROSS_REF_ID and language = msitl.language)
       AS CROSS_REF_DESC,
	eccsm.CROSS_REF_TYPE  ,
	eccsm.MANUFACTURER_ID  ,
	eccsm.MANUFACTURER_NAME ,
	eccsm.MANUFACTURER_PART_NUM,
	eccsm.SUBINVENTORY_CODE  ,
	(select onhand_quantity from EAM_ECC_MAT_AVAIL_QUANTITY where inventory_item_id = eccsm.inventory_item_id
        and organization_id = eccsm.organization_id
        and subinventory_code = eccsm.subinventory_code ) ON_HAND_QTY,
	(select available_quantity from EAM_ECC_MAT_AVAIL_QUANTITY where inventory_item_id = eccsm.inventory_item_id
        and organization_id = eccsm.organization_id
        and subinventory_code = eccsm.subinventory_code ) AVAIL_TO_TRANSACT_QTY ,
	eccsm.RECORD_TYPE ,
	msitl.LANGUAGE ,
	eccsm.ECC_LAST_UPDATE_DATE ,
	eccsm.ECC_SPEC_ID ,
	eccsm.ASSET_BOM  ,
	eccsm.LOCATOR  ,
	eccsm.LOT_NUMBER ,
	eccsm.ITEM_CATEGORY
FROM EAM_ECC_STORES_MAT_TEMP eccsm ,mtl_system_items_b msi,mtl_system_items_tl msitl
WHERE eccsm.inventory_item_id = msi.inventory_item_id
and eccsm.organization_id = msi.organization_id
and msi.inventory_item_id = msitl.inventory_item_id(+)
and msi.organization_id = msitl.organization_id(+)
and msitl.language in ('US')  ) PIVOT (max(MATERIAL_DESCRIPTION) as MATERIAL_DESCRIPTION ,
                                      max(EAM_ITEM_TYPE) as EAM_ITEM_TYPE,
                                      max(CROSS_REF_DESC) as CROSS_REF_DESC
                                      for LANGUAGE in ('US' "US"))
) x
where
2=2