ECC Enterprise Asset Management, Stores Materials (Maintenance), SQL1
Description
Categories: Enterprise Command Center
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:
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 , NVL(eccsm.SUBINVENTORY_CODE,'NONE') SUBINVENTORY_CODE_STR, msitl.long_description LONG_DESCRIPTION, (eccsm.INVENTORY_ITEM_ID ||'-' || eccsm.ORGANIZATION_ID ||'-' || NVL(eccsm.SUBINVENTORY_CODE,'NONE')) INV_ORG_SUBINV_STR 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(LONG_DESCRIPTION) as LONG_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 |