即时库存查询
Create Table #TempInventory(
[FBrNo] [varchar] (10) NOT NULL ,
[FItemID] [int] NOT NULL ,
[FBatchNo] [varchar] (200) NOT NULL ,
[FStockID] [int] NOT NULL ,
[FQty] [decimal](28, 10) NOT NULL ,
[FBal] [decimal](20, 2) NOT NULL ,
[FStockPlaceID] [int] NULL ,
[FKFPeriod] [int] NOT NULL Default(0),
[FKFDate] [varchar] (255) NOT NULL ,
[FMyKFDate] [varchar] (255),
[FStockTypeID] [Int] NOT NULL,
[FQtyLock] [decimal](28, 10) NOT NULL,
[FAuxPropID] [int] NOT NULL,
[FSecQty] [decimal](28, 10) NOT NULL
)
Insert Into #TempInventory
Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID,u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),500,u1.FQtyLock,u1.FAuxPropID,u1.FSecQty From ICInventory u1 where u1.FQty<>0Insert Into #TempInventory Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID,u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),u1.FStockTypeID,0,u1.FAuxPropID,u1.FSecQty From POInventory u1
where u1.FQty<>0
Select t2.fnumber as 仓库代码,t2.FName as 仓库名称,t1.fnumber as 物料代码,t1.FName as 物料名称,t1.FModel as 型号,u1.FBatchNo as 批号,t5.fnumber as 仓位代码,t5.FName as 仓位, t3.FName as 基本单位,u1.FQty as 基本单位数量, t19.FName as 第二计量单位,u1.FSecQty as 第二数量, u1.FQtyLock as[ 锁定数量(基本单位)] From #TempInventory u1
left join t_ICItem t1 on u1.FItemID = t1.FItemID
left join t_Stock t2 on u1.FStockID=t2.FItemID
left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID
left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID
left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID
left join t_AuxItem t9 on u1.FAuxPropID=t9.FItemID
left join t_Measureunit t19 on t1.FSecUnitID=t19.FMeasureunitID
where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0)
and t1.FDeleted=0 AND t2.FTypeID in (500,20291,20293)
Order By t1.FNumber,u1.FBatchNo
Drop Table #TempInventory
联系客服