足缺料分析表

Set Nocount On
Create Table #DATA (
FIndex int identity,
FItemID int null,
FStockQty decimal(28,10) default(0) null,
FSafeQty decimal(28,10) default(0) null,
FWillInQty decimal(28,10) default(0) null,
FWillOutQty decimal(28,10) default(0) null,
FWillDiffQty decimal(28,10) default(0) null,
FCUStockQty decimal(28,10) default(0) null,
FCUSafeQty decimal(28,10) default(0) null,
FCUWillInQty decimal(28,10) default(0) null,
FCUWillOutQty decimal(28,10) default(0) null,
FCUWillDiffQty decimal(28,10) default(0) null,
FOrderPoint decimal(28,10) default(0) null,
FBatFixQty decimal(28,10) default(0) null,
FLowQty decimal(28,10) default(0) null,
FCUOrderPoint decimal(28,10) default(0) null,
FCUBatFixQty decimal(28,10) default(0) null,
FCULowQty decimal(28,10) default(0) null,
FPutInQty decimal(28,10) default(0) null,
FPlanEndDate datetime null ,
FBillQty decimal(28,10) default(0) null,
FDailyConsume decimal(28,10) default(0) null,
FCUUnitName varchar(80) null default(''),
FLeadTime int null default(0),
FPlanBeginDate datetime null ,
FCUBillQty decimal(28,10) default(0) null,
FSumSort int default(0) null )
create index idxitemid on #DATA(FItemID)

Insert into #DATA (FItemID,FSafeQty,FOrderPoint,FBatFixQty,FLowQty,FDailyConsume,FLeadTime)
Select t1.FItemID FItemID,round(t1.FSecInv,t1.FQtyDecimal) FSafeQty,
t1.FOrderPoint,t1.FBatFixEconomy,t1.FLowLimit,t1.FDailyConsume,t1.FLeadTime
from t_ICItem t1
Where (t1.FErpClsID=1 or t1.FErpClsID=2 or t1.FErpClsID=3 or t1.FErpClsID=7) and rtrim(t1.FNumber) >='aa' and rtrim(t1.FNumber) <='aaa' and t1.FDeleted<>1

Update u1 set u1.FStockQty = isnull(u2.FStockQty,0)
From #DATA u1, (select a.FItemID,isnull(sum(IsNull(a.FStockQty,0)),0) FStockQty
, isnull(sum(IsNull(a.FStockLockQty,0)),0) FStockLockQty
From ( Select 2 FStockType,s1.FStockID, s1.FItemID FItemID, round(s1.FQty - isnull(s4.FQty,0),s2.FQtyDecimal) FStockQty
, round( isnull(s4.FQty,0),s2.FQtyDecimal) as FStockLockQty
From (select u1.FItemID,u1.FStockID,sum(u1.FQty) as FQty From ICInventory u1 Group by u1.FStockID,u1.FItemID ) s1
inner join t_icitem s2 on ( s1.FItemID = s2.FItemID )
inner join t_Stock s3 on ( s1.FStockID = s3.FItemID and s3.FMRPAvail = 1)
inner join ( select distinct FItemID From #DATA ) d1 on (s1.FItemId = d1.FItemID)
left join ( select u2.FItemID,u2.FStockID,sum(case when isnull(u2.FQty,0) <= 0 then 0 else u2.FQty END) FQty
From t_lockstock u2 Group by u2.FStockID,u2.FItemID ) s4
on ( s1.FStockId = s4.FStockID and s1.FItemID = s4.FItemID )

union
Select 1 FStockType,s1.FStockID, s1.FItemID FItemID,sum(round(s1.FQty,s2.FQtyDecimal)) FStockQty
, 0 as FStockLockQty
From POInventory s1, t_ICItem s2, t_Stock s3, (select distinct FItemID F
rom #DATA) d1
Where s1.FStockID = s3.FItemID a

nd ( s3.FTypeID=502 or s3.FTypeID=503 )
and s1.FItemID = s2.FItemID and s2.FItemID = d1.FItemID
Group by s1.FStockID,s1.FItemID ) a Group by a.FItemID ) u2
Where u1.FItemID = u2.FItemID