27,579
社区成员
发帖
与我相关
我的任务
分享
K3Wise 存储过程CheckInventory我改了一下.
INSERT INTO ICInventory(FBrNo, FItemID, FBatchNo, FMTONo, FStockID, FQty, FSecQty, FBal, FStockPlaceID, FKFPeriod, FKFDate,
FQtyLock, FAuxPropID,FSupplyID)
SELECT '0', FItemID, FBatchNo, FMTONo, FStockID, FQty, FSecQty, 0, FStockPlaceID, FKFPeriod,
CASE WHEN ( SELECT TOP 1
FStockInDate
FROM icinvinitial ccc
WHERE ccc.FItemID = #RTSum.FItemID
AND ( ccc.FBatchNo = #RTSum.FBatchNo ) ) IS NULL
THEN (SELECT TOP ( 1 )
a.FDate
FROM dbo.ICStockBill AS a
INNER JOIN dbo.ICStockBillEntry AS b
ON a.FInterID = b.FInterID
WHERE ( b.FItemID = #RTSum.FItemID )
AND ( b.FBatchNo = #RTSum.FBatchNo )
ORDER BY a.FDate )
ELSE ( SELECT TOP 1
FStockInDate
FROM icinvinitial ccc
WHERE ccc.FItemID = #RTSum.FItemID
AND ( ccc.FBatchNo = #RTSum.FBatchNo ) )
END
,
FQtyLock, FAuxPropID,isnull(( SELECT TOP ( 1 )
s.FItemID
FROM dbo.t_Supplier AS s
INNER JOIN dbo.ICStockBill AS a
INNER JOIN dbo.ICStockBillEntry AS b
ON a.FInterID = b.FInterID
ON s.FItemID = a.FSupplyID
WHERE ( NOT ( a.FSupplyID IS NULL )
)
AND ( b.FItemID = #RTSum.FItemID )
AND ( b.FBatchNo = #RTSum.FBatchNo )
ORDER BY a.FDate DESC ) ,0)
FROM #RTSum
---K3 WISE
Select B.FNumber AS 物料代码,B.FName AS 物料名称,B.FModel AS 规格型号,C.FName As 单位,D.FName AS 仓库,A.FBatchNo As 批号,A.FQty As 库存数量,A.FKFDate As 生产采购日期
From ICInventory A
Left Join t_ICItem B On A.FItemID = B.FItemID
Left Join t_MeasureUnit C On B.FUnitID = C.FMeasureUnitID
Left Join t_Stock D On A.FStockID = D.FItemID