即时库存查询

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