用户问题是这个物料没有创建bom，为何在无物料清单物料查询中查询不出来？
现象如下：
跟踪发现，系统查询语句如下：
select v.invcode As InvCode,v.invaddcode As InvAddCode,v.invname As InvName, v.invstd As InvStd,mpsflag as bFlag, v.ComUnitName As ComUnitName, v.invattr as sInvAttr,p.Free1 As InvFree_1,p.Free2 As InvFree_2,p.Free3 As InvFree_3, p.Free4 As InvFree_4,p.Free5 As InvFree_5,p.Free6 As InvFree_6,p.Free7 As InvFree_7, p.Free8 As InvFree_8,p.Free9 As InvFree_9,p.Free10 As InvFree_10 , v.InvDefine1 As InvDefine_1 ,v.InvDefine2 As InvDefine_2 ,v.InvDefine3 As InvDefine_3, v.InvDefine4 As InvDefine_4 ,v.InvDefine5 As InvDefine_5 ,v.InvDefine6 As InvDefine_6, v.InvDefine7 As InvDefine_7 ,v.InvDefine8 As InvDefine_8,v.InvDefine9 As InvDefine_9 , v.InvDefine10 As InvDefine_10,v.InvDefine11 As InvDefine_11 ,v.InvDefine12 As InvDefine_12 , v.InvDefine13 As InvDefine_13,v.InvDefine14 As InvDefine_14 ,v.InvDefine15 As InvDefine_15 , v.InvDefine16 As InvDefine_16,p.cBasEngineerFigNo as BasEngineerFigNo,p.partid as PartId, v.EnglishName
from v_bas_inventory v, bas_part p
where (
not exists (select bom_parent.* from bom_parent,bom_bom where bom_parent.parentid = p.partid and bom_bom.bomid = bom_parent.bomid
and bom_bom.bomtype in (1,2)) –该物料没有主bom和替代bom
and p.invcode = v.invcode –存货编码在物料表中存在
and v.BomParent = 1 –允许bom母件
and v.invattr in(2,3,4,5,7) –物料属性判断
and coalesce(v.dedate,‘2099-12-31‘) > ‘2015-01-31 00:00:00‘ –是否停用
)
and 1=1 And ((v.invcode >= N‘021500‘) And (v.invcode <= N‘021500‘)) order by v.invcode
从上面的语句中可以看出要符合以下5个条件才能查询出结果：
1， 该物料没有主bom和替代bom；
前台在物料清单资料查询中应查询不出结果！
后台下面查询语句结果应为空！
select bom_parent.* from bom_parent,bom_bom,bas_part where bom_parent.parentid = bas_part.partid and bom_bom.bomid = bom_parent.bomid
and bom_bom.bomtype in (1,2) and PartId in (select partid from bas_part where InvCode=‘存货编码‘ )
2， 存货编码在物料表中存在；
下面查询语句应该查询出结果：
select * from bas_part where InvCode=‘存货编码‘
如果查询不出结果，说明该存货在bas_part表缺少记录，数据存在问题！
3， 存货有允许bom母件的属性；
4， 物料是否停用；
根据停用日期和当前日期判断！
注：查询条件“是否过滤停用物料”为是时才判断此条件！否则不判断此条件！
5， 物料属性判断；
即v.invattr in(2,3,4,5,7) v：视图v_bas_inventory
查看该物料的属性的语句如下：
select invattr from v_bas_inventory where InvCode=‘存货编码‘
查询结果应为2,3,4,5,7其中的一个！
查看该视图的invattr取值条件如下：
(CASE WHEN Inventory.BSelf = 1 THEN 3
--自制，取值为3
WHEN Inventory.BSelf = 0 AND Inventory.bProxyForeign = 1 THEN 2 –-无自制+委外，取值为2
WHEN Inventory.BSelf = 0 AND (Inventory.BPurchase = 1 OR s.bImport = 1) THEN 1
--无自制+（采购或进口），取值为1
WHEN Inventory.bPlanInv = 1 THEN 7
--计划品，取值为7
WHEN Inventory.bPTOModel = 1 THEN 5
--PTO模型，取值为5
WHEN Inventory.bCheckItem = 1 THEN 4
--选项类，取值为4
ELSE 0 END) –其他取0
总结一下，也就是存货属性必须勾选“自制”、“委外”、“计划品”、“PTO”、“选项类”中任意一个！
