--检查哪些物料未分配至业务单元

SELECT f2.FNUMBER "公司编码", f2.FNAME_L2 "公司名称", a.FNUMBER "库存组织编码", a.FNAME_L2 "库存组织名称", COUNT(*)
FROM
(SELECT a.fid,a.FNUMBER,a.FNAME_L2,a.fisunion FROM T_ORG_BaseUnit a
LEFT JOIN T_ORG_Company c ON a.FID = c.FID
LEFT JOIN T_ORG_OUPartStorage f ON a.FID = f.FUNITID
WHERE --c.FISCU = 1
f.FIsBizUnit = 1 AND a.FNAME_L2 <> '行政部' AND a.FNUMBER like '1200%') a

LEFT JOIN T_BD_Material b ON a.fisunion = b.fisweighted --查询物料
LEFT JOIN T_BD_MaterialInventory c ON c.FMATERIALID = b.fid and c.FOrgUnit = a.fid --库存资料

LEFT JOIN T_ORG_UnitRelation f1 ON a.FID = f1.FFROMUNITID AND f1.FTYPERELATIONID = '00000000-0000-0000-0000-0000000000070FE9F8B5'
LEFT JOIN T_ORG_BaseUnit f2 ON f1.FTOUNITID = f2.FID --公司

GROUP BY f2.FNUMBER, f2.FNAME_L2, a.FNUMBER, a.FNAME_L2, b.FSTATUS, c.FOrgUnit

HAVING c.FOrgUnit IS NULL AND b.FSTATUS = '1'