|
楼主 |
发表于 2009-11-10 15:16:43
|
显示全部楼层
本帖最后由 lyfan 于 2009-11-10 15:18 编辑
问题已经解决
经过跟踪出现下面语句
create view OM_HX_CK_List
as
--先材料期初后出库单
--期初的委外材料期初
select distinct top 100 percent RKRdRecords.AutoID, --子表标识
OM_POmain.MainID, --订单主表标识
'' as CKcode, --出库单号
'' as CKdate, --出库日期
OM_RdRecord.cInvCode as CKInvCode, --材料编号
Inventory.cInvName as CKInvName, --材料名称
Inventory.cInvStd as CKInvStd, --规格型号
Inventory.cComunitCode as CKComunitCode, --计量单位
ComputationUnit.cComUnitName as CKComUnitName, --单位名称
isnull(OM_RdRecord.iQuantity,0) as CKQuantity, --出库数量
--改为核销单价?
--OM_RdRecord.iUnitCost as CKUnitCost, --出库单价
OM_JSRecords.iHXUnitCost as CKUnitCost, --出库单价
OM_RdRecord.iPrice as CKPrice, --出库金额
isnull(OM_JSRecords.HXQuantity,0) as CKHXQuantity, --材料核销数量
--把核销单价也记录上 2008-01-03 13.50
--OM_RdRecord.iUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
OM_JSRecords.iHXUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
--改为从核销记录表中读取 2008-01-03 13.50
--isnull(OM_RdRecord.iQuantity,0) - isnull(OM_RdRecord.iHXQuantity,0) as CKnonHXQuantity, --材料未核销数量
OM_JSRecords.nonHXQuantity as CKnonHXQuantity, --材料未核销数量
--改为上面的 核销单价×未核销数量
--OM_RdRecord.iUnitCost * (isnull(OM_RdRecord.iQuantity,0) - isnull(OM_RdRecord.iHXQuantity,0)) as CKnonHXPrice, --材料未核销金额
OM_JSRecords.iHXUnitCost * isnull(OM_JSRecords.nonHXQuantity,0) as CKnonHXPrice, --材料未核销金额
OM_ProductPO.cVenCode as CKVenCode, --供应商编号
Vendor.cVenName as CKVenName, --供应商名称
Vendor.cVenAbbName as CKVenAbbName, --供应商简称
--10个存货自由项
isnull(OM_RdRecord.cFree1,'') as cFree1,
isnull(OM_RdRecord.cFree2,'') as cFree2,
isnull(OM_RdRecord.cFree3,'') as cFree3,
isnull(OM_RdRecord.cFree4,'') as cFree4,
isnull(OM_RdRecord.cFree5,'') as cFree5,
isnull(OM_RdRecord.cFree6,'') as cFree6,
isnull(OM_RdRecord.cFree7,'') as cFree7,
isnull(OM_RdRecord.cFree8,'') as cFree8,
isnull(OM_RdRecord.cFree9,'') as cFree9,
isnull(OM_RdRecord.cFree10,'') as cFree10,
--16个存货自定义项
isnull(Inventory.cInvDefine1,'') as cInvDefine1,
isnull(Inventory.cInvDefine2,'') as cInvDefine2,
isnull(Inventory.cInvDefine3,'') as cInvDefine3,
isnull(Inventory.cInvDefine4,'') as cInvDefine4,
isnull(Inventory.cInvDefine5,'') as cInvDefine5,
isnull(Inventory.cInvDefine6,'') as cInvDefine6,
isnull(Inventory.cInvDefine7,'') as cInvDefine7,
isnull(Inventory.cInvDefine8,'') as cInvDefine8,
isnull(Inventory.cInvDefine9,'') as cInvDefine9,
isnull(Inventory.cInvDefine10,'') as cInvDefine10,
Inventory.cInvDefine11,
Inventory.cInvDefine12,
Inventory.cInvDefine13,
Inventory.cInvDefine14,
Inventory.cInvDefine15,
Inventory.cInvDefine16,
OM_JSRecords.ID
from OM_POmain
inner join OM_PODetails on OM_PODetails.MainID = OM_POmain.MainID
left join OM_RdRecord on OM_RdRecord.cInvCode = OM_PODetails.cInvCode
and isnull(OM_RdRecord.cFree1,'') = isnull(OM_PODetails.cFree1,'')
and isnull(OM_RdRecord.cFree2,'') = isnull(OM_PODetails.cFree2,'')
and isnull(OM_RdRecord.cFree3,'') = isnull(OM_PODetails.cFree3,'')
and isnull(OM_RdRecord.cFree4,'') = isnull(OM_PODetails.cFree4,'')
and isnull(OM_RdRecord.cFree5,'') = isnull(OM_PODetails.cFree5,'')
and isnull(OM_RdRecord.cFree6,'') = isnull(OM_PODetails.cFree6,'')
and isnull(OM_RdRecord.cFree7,'') = isnull(OM_PODetails.cFree7,'')
and isnull(OM_RdRecord.cFree8,'') = isnull(OM_PODetails.cFree8,'')
and isnull(OM_RdRecord.cFree9,'') = isnull(OM_PODetails.cFree9,'')
and isnull(OM_RdRecord.cFree10,'') = isnull(OM_PODetails.cFree10,'')
inner join OM_ProductPO on OM_ProductPO.ID = OM_POmain.ID
left join Vendor on Vendor.cVenCode = OM_ProductPO.cVenCode
inner join Inventory on Inventory.cInvCode = OM_RdRecord.cInvCode
left join ComputationUnit on ComputationUnit.cComunitCode = Inventory.cComunitCode
inner join RdRecords RKRdRecords on RKRdRecords.iOMMPoIds = OM_POmain.MainID
inner join OM_JSRecords on OM_JSRecords.AutoID = RKRdRecords.AutoID
and OM_JSRecords.HXQuantity <> 0
and OM_RdRecord.ID = OM_JSRecords.QCID
where isnull(OM_RdRecord.bOMFirst,0) = 1
order by OM_JSRecords.ID
union all
--期初的出库
select distinct top 100 percent RKRdRecords.AutoID, --子表标识
OM_POmain.MainID, --订单主表标识
RdRecord.cCode as CKcode, --出库单号
RdRecord.dDate as CKdate, --出库日期
RdRecords.cInvCode as CKInvCode, --材料编号
Inventory.cInvName as CKInvName, --材料名称
Inventory.cInvStd as CKInvStd, --规格型号
Inventory.cComunitCode as CKComunitCode, --计量单位
ComputationUnit.cComUnitName as CKComUnitName, --单位名称
isnull(RdRecords.iQuantity,0) as CKQuantity, --出库数量
--不取核销单价,取实时单价
RdRecords.iUnitCost as CKUnitCost, --出库单价
--OM_JSRecords.iHXUnitCost as CKUnitCost, --出库单价
RdRecords.iPrice as CKPrice, --出库金额
isnull(OM_JSRecords.HXQuantity,0) as CKHXQuantity, --材料核销数量
--材料核销金额 实时单价×核销数量
RdRecords.iUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
--OM_JSRecords.iHXUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
--改为从核销记录表中读取 2008-01-03 13.50
--isnull(RdRecords.iQuantity,0) - isnull(RdRecords.iHXQuantity,0) as CKnonHXQuantity, --材料未核销数量
OM_JSRecords.nonHXQuantity as CKnonHXQuantity, --材料未核销数量
--未核销金额 实时单价×未核销数量
--RdRecords.iUnitCost * (isnull(RdRecords.iQuantity,0) - isnull(RdRecords.iHXQuantity,0)) as CKnonHXPrice, --材料未核销金额
RdRecords.iUnitCost * isnull(OM_JSRecords.nonHXQuantity,0) as CKnonHXPrice, --材料未核销金额
OM_ProductPO.cVenCode as CKVenCode, --供应商编号
Vendor.cVenName as CKVenName, --供应商名称
Vendor.cVenAbbName as CKVenAbbName, --供应商简称
--10个存货自由项
isnull(RdRecords.cFree1,'') as cFree1,
isnull(RdRecords.cFree2,'') as cFree2,
isnull(RdRecords.cFree3,'') as cFree3,
isnull(RdRecords.cFree4,'') as cFree4,
isnull(RdRecords.cFree5,'') as cFree5,
isnull(RdRecords.cFree6,'') as cFree6,
isnull(RdRecords.cFree7,'') as cFree7,
isnull(RdRecords.cFree8,'') as cFree8,
isnull(RdRecords.cFree9,'') as cFree9,
isnull(RdRecords.cFree10,'') as cFree10,
--16个存货自定义项
isnull(Inventory.cInvDefine1,'') as cInvDefine1,
isnull(Inventory.cInvDefine2,'') as cInvDefine2,
isnull(Inventory.cInvDefine3,'') as cInvDefine3,
isnull(Inventory.cInvDefine4,'') as cInvDefine4,
isnull(Inventory.cInvDefine5,'') as cInvDefine5,
isnull(Inventory.cInvDefine6,'') as cInvDefine6,
isnull(Inventory.cInvDefine7,'') as cInvDefine7,
isnull(Inventory.cInvDefine8,'') as cInvDefine8,
isnull(Inventory.cInvDefine9,'') as cInvDefine9,
isnull(Inventory.cInvDefine10,'') as cInvDefine10,
Inventory.cInvDefine11,
Inventory.cInvDefine12,
Inventory.cInvDefine13,
Inventory.cInvDefine14,
Inventory.cInvDefine15,
Inventory.cInvDefine16,
OM_JSRecords.ID
from OM_POmain
inner join OM_PODetails on OM_PODetails.MainID = OM_POmain.MainID
left join OM_JS_RdRecords RdRecords on RdRecords.iOMMPoIds = OM_PODetails.SubID
inner join OM_JS_RdRecord RdRecord on RdRecord.ID = RdRecords.ID
--此条件是为了限制必须是委外入库,只要传递的AutoID正确,则此条件没有作用
and RdRecord.cVouchType = '11' and RdRecord.cBusType = '委外出库'
inner join OM_ProductPO on OM_ProductPO.ID = OM_POmain.ID
left join Vendor on Vendor.cVenCode = OM_ProductPO.cVenCode
inner join Inventory on Inventory.cInvCode = RdRecords.cInvCode
left join ComputationUnit on ComputationUnit.cComunitCode = Inventory.cComunitCode
inner join RdRecords RKRdRecords on RKRdRecords.iOMMPoIds = OM_POmain.MainID
inner join OM_JSRecords on OM_JSRecords.AutoID = RKRdRecords.AutoID
and OM_JSRecords.HXQuantity <> 0
and RdRecords.AutoID = OM_JSRecords.CKAutoID
order by OM_JSRecords.ID
union all
--正常的委外材料期初
select distinct top 100 percent RKRdRecords.AutoID, --子表标识
OM_POmain.MainID, --订单主表标识
'' as CKcode, --出库单号
'' as CKdate, --出库日期
OM_RdRecord.cInvCode as CKInvCode, --材料编号
Inventory.cInvName as CKInvName, --材料名称
Inventory.cInvStd as CKInvStd, --规格型号
Inventory.cComunitCode as CKComunitCode, --计量单位
ComputationUnit.cComUnitName as CKComUnitName, --单位名称
isnull(OM_RdRecord.iQuantity,0) as CKQuantity, --出库数量
--改为核销单价?
--OM_RdRecord.iUnitCost as CKUnitCost, --出库单价
OM_JSRecords.iHXUnitCost as CKUnitCost, --出库单价
OM_RdRecord.iPrice as CKPrice, --出库金额
isnull(OM_JSRecords.HXQuantity,0) as CKHXQuantity, --材料核销数量
--把核销单价也记录上 2008-01-03 13.50
--OM_RdRecord.iUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
OM_JSRecords.iHXUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
--改为从核销记录表中读取 2008-01-03 13.50
--isnull(OM_RdRecord.iQuantity,0) - isnull(OM_RdRecord.iHXQuantity,0) as CKnonHXQuantity, --材料未核销数量
OM_JSRecords.nonHXQuantity as CKnonHXQuantity, --材料未核销数量
--改为上面的 核销单价×未核销数量
--OM_RdRecord.iUnitCost * (isnull(OM_RdRecord.iQuantity,0) - isnull(OM_RdRecord.iHXQuantity,0)) as CKnonHXPrice, --材料未核销金额
OM_JSRecords.iHXUnitCost * isnull(OM_JSRecords.nonHXQuantity,0) as CKnonHXPrice, --材料未核销金额
OM_ProductPO.cVenCode as CKVenCode, --供应商编号
Vendor.cVenName as CKVenName, --供应商名称
Vendor.cVenAbbName as CKVenAbbName, --供应商简称
--10个存货自由项
isnull(OM_RdRecord.cFree1,'') as cFree1,
isnull(OM_RdRecord.cFree2,'') as cFree2,
isnull(OM_RdRecord.cFree3,'') as cFree3,
isnull(OM_RdRecord.cFree4,'') as cFree4,
isnull(OM_RdRecord.cFree5,'') as cFree5,
isnull(OM_RdRecord.cFree6,'') as cFree6,
isnull(OM_RdRecord.cFree7,'') as cFree7,
isnull(OM_RdRecord.cFree8,'') as cFree8,
isnull(OM_RdRecord.cFree9,'') as cFree9,
isnull(OM_RdRecord.cFree10,'') as cFree10,
--16个存货自定义项
isnull(Inventory.cInvDefine1,'') as cInvDefine1,
isnull(Inventory.cInvDefine2,'') as cInvDefine2,
isnull(Inventory.cInvDefine3,'') as cInvDefine3,
isnull(Inventory.cInvDefine4,'') as cInvDefine4,
isnull(Inventory.cInvDefine5,'') as cInvDefine5,
isnull(Inventory.cInvDefine6,'') as cInvDefine6,
isnull(Inventory.cInvDefine7,'') as cInvDefine7,
isnull(Inventory.cInvDefine8,'') as cInvDefine8,
isnull(Inventory.cInvDefine9,'') as cInvDefine9,
isnull(Inventory.cInvDefine10,'') as cInvDefine10,
Inventory.cInvDefine11,
Inventory.cInvDefine12,
Inventory.cInvDefine13,
Inventory.cInvDefine14,
Inventory.cInvDefine15,
Inventory.cInvDefine16,
OM_JSRecords.ID
from OM_POmain
inner join OM_PODetails on OM_PODetails.MainID = OM_POmain.MainID
left join OM_RdRecord on OM_RdRecord.cInvCode = OM_PODetails.cInvCode
and isnull(OM_RdRecord.cFree1,'') = isnull(OM_PODetails.cFree1,'')
and isnull(OM_RdRecord.cFree2,'') = isnull(OM_PODetails.cFree2,'')
and isnull(OM_RdRecord.cFree3,'') = isnull(OM_PODetails.cFree3,'')
and isnull(OM_RdRecord.cFree4,'') = isnull(OM_PODetails.cFree4,'')
and isnull(OM_RdRecord.cFree5,'') = isnull(OM_PODetails.cFree5,'')
and isnull(OM_RdRecord.cFree6,'') = isnull(OM_PODetails.cFree6,'')
and isnull(OM_RdRecord.cFree7,'') = isnull(OM_PODetails.cFree7,'')
and isnull(OM_RdRecord.cFree8,'') = isnull(OM_PODetails.cFree8,'')
and isnull(OM_RdRecord.cFree9,'') = isnull(OM_PODetails.cFree9,'')
and isnull(OM_RdRecord.cFree10,'') = isnull(OM_PODetails.cFree10,'')
inner join OM_ProductPO on OM_ProductPO.ID = OM_POmain.ID
left join Vendor on Vendor.cVenCode = OM_ProductPO.cVenCode
inner join Inventory on Inventory.cInvCode = OM_RdRecord.cInvCode
left join ComputationUnit on ComputationUnit.cComunitCode = Inventory.cComunitCode
inner join RdRecords RKRdRecords on RKRdRecords.iOMMPoIds = OM_POmain.MainID
inner join OM_JSRecords on OM_JSRecords.AutoID = RKRdRecords.AutoID
and OM_JSRecords.HXQuantity <> 0
and OM_RdRecord.ID = OM_JSRecords.QCID
where isnull(OM_RdRecord.bOMFirst,0) = 0
order by OM_JSRecords.ID
union all
--正常的出库
select distinct top 100 percent RKRdRecords.AutoID, --子表标识
OM_POmain.MainID, --订单主表标识
RdRecord.cCode as CKcode, --出库单号
RdRecord.dDate as CKdate, --出库日期
RdRecords.cInvCode as CKInvCode, --材料编号
Inventory.cInvName as CKInvName, --材料名称
Inventory.cInvStd as CKInvStd, --规格型号
Inventory.cComunitCode as CKComunitCode, --计量单位
ComputationUnit.cComUnitName as CKComUnitName, --单位名称
isnull(RdRecords.iQuantity,0) as CKQuantity, --出库数量
--不取核销单价,取实时单价
RdRecords.iUnitCost as CKUnitCost, --出库单价
--OM_JSRecords.iHXUnitCost as CKUnitCost, --出库单价
RdRecords.iPrice as CKPrice, --出库金额
isnull(OM_JSRecords.HXQuantity,0) as CKHXQuantity, --材料核销数量
--材料核销金额 实时单价×核销数量
RdRecords.iUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
--OM_JSRecords.iHXUnitCost * isnull(OM_JSRecords.HXQuantity,0) as CKHXPrice, --材料核销金额
--改为从核销记录表中读取 2008-01-03 13.50
--isnull(RdRecords.iQuantity,0) - isnull(RdRecords.iHXQuantity,0) as CKnonHXQuantity, --材料未核销数量
OM_JSRecords.nonHXQuantity as CKnonHXQuantity, --材料未核销数量
--未核销金额 实时单价×未核销数量
--RdRecords.iUnitCost * (isnull(RdRecords.iQuantity,0) - isnull(RdRecords.iHXQuantity,0)) as CKnonHXPrice, --材料未核销金额
RdRecords.iUnitCost * isnull(OM_JSRecords.nonHXQuantity,0) as CKnonHXPrice, --材料未核销金额
OM_ProductPO.cVenCode as CKVenCode, --供应商编号
Vendor.cVenName as CKVenName, --供应商名称
Vendor.cVenAbbName as CKVenAbbName, --供应商简称
--10个存货自由项
isnull(RdRecords.cFree1,'') as cFree1,
isnull(RdRecords.cFree2,'') as cFree2,
isnull(RdRecords.cFree3,'') as cFree3,
isnull(RdRecords.cFree4,'') as cFree4,
isnull(RdRecords.cFree5,'') as cFree5,
isnull(RdRecords.cFree6,'') as cFree6,
isnull(RdRecords.cFree7,'') as cFree7,
isnull(RdRecords.cFree8,'') as cFree8,
isnull(RdRecords.cFree9,'') as cFree9,
isnull(RdRecords.cFree10,'') as cFree10,
--16个存货自定义项
isnull(Inventory.cInvDefine1,'') as cInvDefine1,
isnull(Inventory.cInvDefine2,'') as cInvDefine2,
isnull(Inventory.cInvDefine3,'') as cInvDefine3,
isnull(Inventory.cInvDefine4,'') as cInvDefine4,
isnull(Inventory.cInvDefine5,'') as cInvDefine5,
isnull(Inventory.cInvDefine6,'') as cInvDefine6,
isnull(Inventory.cInvDefine7,'') as cInvDefine7,
isnull(Inventory.cInvDefine8,'') as cInvDefine8,
isnull(Inventory.cInvDefine9,'') as cInvDefine9,
isnull(Inventory.cInvDefine10,'') as cInvDefine10,
Inventory.cInvDefine11,
Inventory.cInvDefine12,
Inventory.cInvDefine13,
Inventory.cInvDefine14,
Inventory.cInvDefine15,
Inventory.cInvDefine16,
OM_JSRecords.ID
from OM_POmain
inner join OM_PODetails on OM_PODetails.MainID = OM_POmain.MainID
left join RdRecords on RdRecords.iOMMPoIds = OM_PODetails.SubID
inner join RdRecord on RdRecord.ID = RdRecords.ID
--此条件是为了限制必须是委外入库,只要传递的AutoID正确,则此条件没有作用
and RdRecord.cVouchType = '11' and RdRecord.cBusType = '委外出库'
inner join OM_ProductPO on OM_ProductPO.ID = OM_POmain.ID
left join Vendor on Vendor.cVenCode = OM_ProductPO.cVenCode
inner join Inventory on Inventory.cInvCode = RdRecords.cInvCode
left join ComputationUnit on ComputationUnit.cComunitCode = Inventory.cComunitCode
inner join RdRecords RKRdRecords on RKRdRecords.iOMMPoIds = OM_POmain.MainID
inner join OM_JSRecords on OM_JSRecords.AutoID = RKRdRecords.AutoID
and OM_JSRecords.HXQuantity <> 0
and RdRecords.AutoID = OM_JSRecords.CKAutoID
order by OM_JSRecords.ID
在查询分析器里面执行出现
服务器: 消息 207,级别 16,状态 3,过程 OM_HX_CK_List,行 6
列名 'ID' 无效。
服务器: 消息 207,级别 16,状态 1,过程 OM_HX_CK_List,行 6
列名 'ID' 无效。
服务器: 消息 207,级别 16,状态 1,过程 OM_HX_CK_List,行 6
列名 'ID' 无效。
服务器: 消息 207,级别 16,状态 1,过程 OM_HX_CK_List,行 6
列名 'ID' 无效。
在到这张视图的“设计视图”里面查看语句并且和正常数据库对比。最后发现
OM_JSRecords这张表里面少“ID”字段。添加上再升级就行。 |
评分
-
查看全部评分
|