檢查有沒有料號未設成本分類 (SQL)

偶而會有料號未設成本分類, 造成會計在算成本時出現錯誤, 可以用以下 SQL 來檢查:

SELECT msi.inventory_item_id,msi.segment1,msi.organization_id
FROM mtl_system_items_b msi
WHERE msi.organization_id = 86
AND MSI.INVENTORY_ITEM_ID IN (SELECT DISTINCT inventory_item_id
FROM mtl_material_transactions
WHERE organization_id =86)

AND NOT EXISTS(
SELECT category_id
FROM mtl_item_categories mic
WHERE mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_set_id = 1100000042);