--不同组织物料类别差异列表
SELECT MSI1.SEGMENT1 ITEM_NUMBER,
MSI1.DESCRIPTION,
MC1.DESCRIPTION MASTER_LEVEL,
FU1.DESCRIPTION USER_NAME,
MIC1.LAST_UPDATE_DATE,
MC2.DESCRIPTION ORG_LEVEL,
FU2.DESCRIPTION USER_NAME,
MIC2.LAST_UPDATE_DATE
FROM MTL_SYSTEM_ITEMS_B MSI1,
MTL_SYSTEM_ITEMS_B MSI2,
MTL_ITEM_CATEGORIES MIC1,
MTL_ITEM_CATEGORIES MIC2,
MTL_CATEGORIES MC1,
MTL_CATEGORIES MC2,
FND_USER FU1,
FND_USER FU2
WHERE MSI1.INVENTORY_ITEM_ID = MIC1.INVENTORY_ITEM_ID(+)
AND MSI1.ORGANIZATION_ID = MIC1.ORGANIZATION_ID(+)
AND MSI1.INVENTORY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
AND MSI2.INVENTORY_ITEM_ID = MIC2.INVENTORY_ITEM_ID(+)
AND MSI2.ORGANIZATION_ID = MIC2.ORGANIZATION_ID(+)
AND MIC1.CATEGORY_ID = MC1.CATEGORY_ID
AND MIC2.CATEGORY_ID = MC2.CATEGORY_ID
AND MIC1.ORGANIZATION_ID = 204 --A组织ID
AND MIC2.ORGANIZATION_ID = 706 --B组织ID
AND MIC1.LAST_UPDATED_BY = FU1.USER_ID
AND MIC2.LAST_UPDATED_BY = FU2.USER_ID
AND MC1.CATEGORY_ID <> MC2.CATEGORY_ID
AND UPPER (MSI1.INVENTORY_ITEM_STATUS_CODE) = 'ACTIVE'
ORDER BY 1;