物控平台数据问题（有的物料有库存，而物控平台查询不出明细）
系统版本：k3 wise 15.0版本
原因：查询语句有问题。
-----15.0版本物控平台查询语句------
备注：增加“计划跟踪号”的查询条件，而对老数据的关联查询语句时，语句有问题：
AND INV.FMTONO = IA.FMTONO ------正确语句应该写成这样子： AND INV.FMTONO = isnull(IA.FMTONO,'')
SELECT I.FItemID ,CASE WHEN I.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(IA.FAuxPropID,0) END FAuxPropID,IA.FMTONO
SUM(ISNULL(INV.FQty,0)+ISNULL(POINV.FQty,0)) FInvQty,MAX(ISNULL(I.FNumber,''))FNumber,MAX(ISNULL(I.FSecInv,0))FSecInv,I.FAuxInMrpCal
FROM t_ICItem I with(nolock)
LEFT JOIN (SELECT P.FItemID,CASE WHEN T.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(P.FAuxPropID,0) END FAuxPropID,p.FMTONO FROM ICPlan_PMCDetail P with(nolock) INNER JOIN t_ICItem T with(nolock) ON T.FItemID=P.FItemID GROUP BY P.FItemID,P.FMTONO,CASE WHEN T.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(P.FAuxPropID,0) END) IA ON IA.FItemID=I.FItemID
LEFT JOIN ICPlan_PMCRunPara MRPStock with(nolock) ON MRPStock.FItemIClassD = 13 AND MRPStock.FValue='1'
LEFT JOIN ICInventory INV with(nolock) ON INV.FItemID = I.FItemID
AND INV.FMTONO = IA.FMTONO
------正确语句应该写成这样子： AND INV.FMTONO = isnull(IA.FMTONO,'')
AND (I.FAuxInMrpCal=0 OR I.FAuxInMrpCal=1 AND IA.FAuxPropID=INV.FAuxPropID) AND INV.FStockID = MRPStock.FItemID AND EXISTS(SELECT TOP 1 1 FROM v_PlanCategoryStock WHERE FStockID=MRPStock.FItemID AND FEntryID IN(1,2,3))
LEFT JOIN POInventory POINV with(nolock) ON POINV.FItemID = I.FItemID AND POINV.FMTONO = IA.FMTONO AND (I.FAuxInMrpCal=0 OR I.FAuxInMrpCal=1 AND IA.FAuxPropID=POINV.FAuxPropID) AND POINV.FStockID = MRPStock.FItemID AND EXISTS(SELECT TOP 1 1 FROM v_PlanCategoryStock WHERE FStockID=MRPStock.FItemID AND FEntryID IN(1,2,3))
LEFT JOIN t_Emp EmpOR with(nolock) ON EmpOR.FItemID = I.FOrderRector --采购负责人
LEFT JOIN t_Emp EmpPlanner with(nolock) ON EmpPlanner.FItemID = I.FPlanner --计划员
WHERE I.FErpClsID IN (1,3,2,13,7) AND I.FNumber>='80.01.109'AND I.FNumber<='80.01.109' AND I.FDeleted=0 AND (EXISTS(SELECT TOP 1 1 FROM ICPlan_PMCDetail PMC with(nolock) WHERE PMC.FItemID=I.FItemID AND PMC.FMTONO = IA.FMTONO AND (I.FAuxInMrpCal=0 OR I.FAuxInMrpCal=1 AND IA.FAuxPropID=PMC.FAuxPropID) AND PMC.FPlanCategory IN(1,2,3)) OR INV.FQty>0 OR POINV.FQty>0)
GROUP BY I.FItemID,I.FAuxInMrpCal,CASE WHEN I.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(IA.FAuxPropID,0) END,IA.FMTONO
-----14.3版本物控平台查询语句------
SELECT I.FItemID ,CASE WHEN I.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(IA.FAuxPropID,0) END FAuxPropID,
SUM(ISNULL(INV.FQty,0)+ISNULL(POINV.FQty,0)) FInvQty,MAX(I.FNumber)FNumber,MAX(I.FSecInv)FSecInv,I.FAuxInMrpCal
FROM t_ICItem I with(nolock)
LEFT JOIN (SELECT P.FItemID,CASE WHEN T.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(P.FAuxPropID,0) END FAuxPropID FROM ICPlan_PMCDetail P with(nolock) INNER JOIN t_ICItem T with(nolock) ON T.FItemID=P.FItemID GROUP BY P.FItemID,CASE WHEN T.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(P.FAuxPropID,0) END) IA ON IA.FItemID=I.FItemID
LEFT JOIN ICPlan_PMCRunPara MRPStock with(nolock) ON MRPStock.FItemIClassD = 13 AND MRPStock.FValue='1'
LEFT JOIN ICInventory INV with(nolock) ON INV.FItemID = I.FItemID
AND (I.FAuxInMrpCal=0 OR I.FAuxInMrpCal=1 AND IA.FAuxPropID=INV.FAuxPropID) AND INV.FStockID = MRPStock.FItemID AND EXISTS(SELECT TOP 1 1 FROM v_PlanCategoryStock WHERE FStockID=MRPStock.FItemID AND FEntryID IN(1,2,3))
LEFT JOIN POInventory POINV with(nolock) ON POINV.FItemID = I.FItemID AND (I.FAuxInMrpCal=0 OR I.FAuxInMrpCal=1 AND IA.FAuxPropID=POINV.FAuxPropID) AND POINV.FStockID = MRPStock.FItemID AND EXISTS(SELECT TOP 1 1 FROM v_PlanCategoryStock WHERE FStockID=MRPStock.FItemID AND FEntryID IN(1,2,3))
LEFT JOIN t_Emp EmpOR with(nolock) ON EmpOR.FItemID = I.FOrderRector --采购负责人
LEFT JOIN t_Emp EmpPlanner with(nolock) ON EmpPlanner.FItemID = I.FPlanner --计划员
WHERE I.FErpClsID IN (1,3,2,13,7) AND I.FNumber>='80.01.109'AND I.FNumber<='80.01.109' AND I.FDeleted=0 AND (EXISTS(SELECT TOP 1 1 FROM ICPlan_PMCDetail PMC with(nolock) WHERE PMC.FItemID=I.FItemID AND (I.FAuxInMrpCal=0 OR I.FAuxInMrpCal=1 AND IA.FAuxPropID=PMC.FAuxPropID) AND PMC.FPlanCategory IN(1,2,3)) OR INV.FQty>0 OR POINV.FQty>0)
GROUP BY I.FItemID,I.FAuxInMrpCal,CASE WHEN I.FAuxInMrpCal=0 THEN 0 ELSE ISNULL(IA.FAuxPropID,0) END
推荐阅读