insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FBegQty,FSecBegQty,FBegBal,FBegDiff)
select t1.FItemID,t1.FStockID,t1.FStockPlaceID,t1.FBatchNo,t1.FAuxPropID,t1.FKFPeriod,t1.FKFDate,t1.FBegQty,t1.FSecBegQty,round(t1.FBegBal,2),round(t1.FBegDiff,2)
From ICInvBal t1
Where FPeriod = 1 And FYear = 2016 And (FBegQty <> 0 Or FSecBegQty<>0 Or FBegBal <> 0 or FBegDiff<>0)
insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FReceive,FSecReceive,FDebit,FRevDiff)
select t2.FItemID,isnull(t2.FDCStockID,0),isnull(t2.FDCSPID,0),t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),''),sum(t2.FQty),sum(t2.FSecQty),sum(round(t2.FAmount,2)),round(sum(case t3.FTrack when 81 then round(t2.FAmount,2) -round(t2.FPlanAmount,2) else 0 end),2)
from ICStockBill t1,ICStockBillEntry t2,t_ICItem t3
where t1.FInterID=t2.FInterID and t1.FDate<'2016-02-01' and FDate>='2016-01-01'
and t1.FCancellation=0 and t2.FItemID=t3.FItemID and (t1.FTrantype in(1,2,93,10,40,101,102) OR (t1.FtranType=100 AND t1.FBillTypeID=12542))
group by t2.FDCStockID,t2.FDCSPID,t2.FItemID,t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,t2.FKFDate
insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FReceive,FSecReceive,FCredit,FSendDiff)
select t2.FItemID,isnull(t2.FDCStockID,0),isnull(t2.FDCSPID,0),t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),''),sum(-t2.FQty),sum(-t2.FSecQty),Sum(Round(t2.FAmount,2)),sum(case t3.FTrack when 81 then Round(t2.FPlanAmount,2) -round(t2.FAmount,2) else 0 end)
from ICStockBill t1,ICStockBillEntry t2,t_ICItem t3
where t1.FInterID=t2.FInterID and t1.FDate<'2016-02-01' and FDate>='2016-01-01'
and t1.FCancellation=0 and t2.FItemID=t3.FItemID and (t1.FtranType=100 AND t1.FBillTypeID=12541)
group by t2.FDCStockID,t2.FDCSPID,t2.FItemID,t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,t2.FKFDate
insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FReceive,FSecReceive,FDebit,FRevDiff)
select t2.FItemID,isnull(t2.FDCStockID,0),isnull(t2.FDCSPID,0),t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),''),sum(t2.FQty),sum(t2.FSecQty),Sum(Round(t2.FAmtRef,2)),Sum(case t3.FTrack when 81 then Round(t2.FAmtRef,2) -round(t2.FPlanAmount,2) else 0 end)
from ICStockBill t1,ICStockBillEntry t2,t_ICItem t3
where t1.FInterID=t2.FInterID and t1.FDate<'2016-02-01' and FDate>='2016-01-01'
and t1.FCancellation=0 and t2.FItemID=t3.FItemID and t1.FTrantype =41
group by t2.FDCStockID,t2.FDCSPID,t2.FItemID,t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,t2.FKFDate
insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FSend,FSecSend,FCredit,FSendDiff)
select t2.FItemID,isnull(t2.FDCStockID,0),isnull(t2.FDCSPID,0),t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),''),sum(t2.FQty),sum(t2.FSecQty),Sum(Round(t2.FAmount,2)),Sum(case t3.FTrack when 81 then Round(t2.FAmount,2) -round(t2.FPlanAmount,2) else 0 end)
from ICStockBill t1,ICStockBillEntry t2,t_ICItem t3
where t1.FInterID=t2.FInterID and t1.FDate<'2016-02-01' and FDate>='2016-01-01'
and t1.FCancellation=0 and t2.FItemID=t3.FItemID and t1.FTrantype in(21,28,29,43)
group by t2.FDCStockID,t2.FDCSPID,t2.FItemID,t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,t2.FKFDate
insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FSend,FSecSend,FCredit,FSendDiff)
select t2.FItemID,isnull(t2.FSCStockID,0) ,isnull(t2.FDCSPID,0),t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),''),sum(t2.FQty),sum(t2.FSecQty),Sum(Round(t2.FAmount,2)),Sum(case t3.FTrack when 81 then Round(t2.FAmount,2) -round(t2.FPlanAmount,2) else 0 end)
from ICStockBill t1,ICStockBillEntry t2,t_ICItem t3
where t1.FInterID=t2.FInterID and t1.FDate<'2016-02-01' and FDate>='2016-01-01'
and t1.FCancellation=0 and t2.FItemID=t3.FItemID and t1.FTrantype =92
group by t2.FSCStockID,t2.FDCSPID,t2.FItemID,t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,t2.FKFDate
insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FSend,FSecSend,FCredit,FSendDiff)
select t2.FItemID,isnull(t2.FSCStockID,0),isnull(t2.FSCSPID,0),t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),''),sum(t2.FQty),sum(t2.FSecQty),Sum(Round(t2.FAmount,2)),Sum(case t3.FTrack when 81 then Round(t2.FAmount,2) -round(t2.FPlanAmount,2) else 0 end)
from ICStockBill t1,ICStockBillEntry t2,t_ICItem t3
where t1.FInterID=t2.FInterID and t1.FDate<'2016-02-01' and FDate>='2016-01-01'
and t1.FCancellation=0 and t2.FItemID=t3.FItemID and t1.FTrantype=41
group by t2.FSCStockID,t2.FSCSPID,t2.FItemID,t2.FBatchNo,t2.FAuxPropid,t2.FKFPeriod,t2.FKFDate
insert into #TmpInvBal_1(FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FSend,FSecSend,FCredit,FSendDiff)
select t2.FItemID,isnull(t2.FSCStockID,0),isnull(t2.FDCSPID,0),t2.FBatchNo,t2.FAuxPropID,t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),''),sum(t2.FQty),sum(t2.FSecQty),Sum(Round(t2.FAmount,2)),Sum(case t3.FTrack when 81 then Round(t2.FAmount,2) -round(t2.FPlanAmount,2) else 0 end)
from ICStockBill t1,ICStockBillEntry t2,t_ICItem t3
where t1.FInterID=t2.FInterID and t1.FDate<'2016-02-01' and FDate>='2016-01-01'
and t1.FCancellation=0 and t2.FItemID=t3.FItemID and (t1.FTrantype =24)
group by t2.FSCStockID,t2.FDCSPID,t2.FItemID,t2.FBatchNo,t2.FAuxPropid,t2.FKFPeriod,t2.FKFDate
delete t1 from ICInvBal t1 where FPeriod=1 and FYear=2016
AND t1.FItemID not in (Select distinct FItemID from t_ICItem)
delete t1 from ICInvBal t1,t_ICItem t2 where FPeriod=1 and FYear=2016
AND t1.FItemID=t2.FItemID
update t1 set FBatchNo='' from #TmpInvBal_1 t1,t_ICItem t2 where t1.FItemID=t2.FItemID and t2.FTrack<>80 and t2.FBatchManager<>1
update t1 set FAuxPropID=0 from #TmpInvBal_1 t1,t_ICItem t2 where t1.FItemID=t2.FItemID and t2.FAuxClassID=0
insert into ICInvBal(FBrNo,FYear,FPeriod,FItemID,FStockID,FStockPlaceID,FBatchNo,FAuxPropID,FKFPeriod,FKFDate,FBegQty,FSecBegQty,FBegBal,FBegDiff,
FReceive,FSecReceive,FDebit,FReceiveDiff,FSend,FSecSend,FCredit,FSendDiff)
select '0',2016,1,P.FItemID,P.FStockID,P.FStockPlaceID,P.FBatchNo,P.FAuxPropID,P.FKFPeriod,P.FKFDate,
round(Sum(P.FBegQty),10),round(Sum(P.FSecBegQty),10),round(Sum(P.FBegBal),2),round(Sum(P.FBegDiff),2), round(Sum(P.FReceive),10),round(Sum(P.FSecReceive),10),round(Sum(P.FDebit),2),round(Sum(P.FRevDiff),2)
,round(sum(P.FSend),10),round(sum(P.FSecSend),10),round(sum(P.FCredit),2),round(sum(P.FSendDiff),2)
from #TmpInvBal_1 P
left join t_icitem t1 on p.FItemID=t1.FItemID
group by P.FStockID,P.FStockPlaceID,P.FItemID,P.FBatchNo,P.FAuxPropID,P.FKFPeriod,P.FKFDate
go
select isnull(FValue,'') from t_SystemProFile where Fcategory='GL' and Fkey='PeriodCount'
go
update ICInvBal SET FEndQty=round(FBegQty-FSend+FReceive,10),
FSecEndQty=round(FSecBegQty-FSecSend+FSecReceive,10),
FEndBal=FBegBal+FDebit-FCredit,FEndDiff=FBegDiff+FReceiveDiff-FSendDiff
FROM ICInvBal,t_ICItem t1
Where ICInvBal.FYear = 2016 And ICInvBal.FPeriod = 1
and ICInvBal.FItemID=t1.FItemID
go
SELECT FValue FROM T_SYSTEMPROFILE WHERE FCATEGORY='IC' AND Fkey='MakeZanGuVoucher'
go
update t_SystemProfile set FValue='2016' where Fcategory='IC' and Fkey='CurrentYear'
go
update t_SystemProfile set FValue='2' where Fcategory='IC' and Fkey='CurrentPeriod'
go
INSERT INTO ICInvBal(FBrNo,FYear,FPeriod,FItemID,FBatchNo,FAuxPropID,FBegQty,FSecBegQty,
FYtdReceive,FSecYtdReceive,FYtdSend,FSecYtdSend,FBegBal,FYtdDebit,FYtdCredit,FBegDiff,FStockID,FStockPlaceID,FKFDate,FKFPeriod)
select ICInvBal.FBrNo,2016,2,ICInvBal.FItemID,ICInvBal.FBatchNo,ICInvBal.FAuxPropID,round(ICInvBal.FEndQty,10),round(ICInvBal.FSecEndQty,10),
round(ICInvBal.FYtdReceive,10) ,round(ICInvBal.FSecYtdReceive,10) ,round(ICInvBal.FYtdSend,10),round(ICInvBal.FSecYtdSend,10), round(ICInvBal.FEndBal,2), round(ICInvBal.FYtdDebit,2), round(ICInvBal.FYtdCredit,2), round(ICInvBal.FEndDiff,2),ICInvBal.FStockID,ICInvBal.FStockPlaceID,ICInvBal.FKFDate,ICInvBal.FKFPeriod
From ICInvBal
left join t_ICItem on ICInvBal.FItemID=t_ICItem.FItemID
WHERE ICInvBal.FYear=2016 and
ICInvBal.FPeriod=1 and (round(ICInvBal.FEndQty,10)<>0 or round(ICInvBal.FEndBal,2)<>0 or round(ICInvBal.FEndDiff,2)<>0)
go
select MIN(FPeriod) from ICInvBal where FYear=2016
go
update t1 SET t1.FYtdReceive=round(t1.FReceive,10),t1.FSecYtdReceive=round(t1.FSecReceive,10), t1.FYtdSend=round(t1.FSend,10),t1.FSecYtdSend=round(t1.FSecSend,10),t1.FYtdDebit=round(t1.FDebit,2),t1.FYtdCredit=round(t1.FCredit,2), t1.FYtdReceiveDiff=round(t1.FReceiveDiff,2),t1.FYtdSendDiff=round(t1.FSendDiff,2) from ICInvBal t1 left join t_icitem u1 on t1.FItemID=u1.FItemID where t1.FPeriod=1 AND t1.FYear=2016
go
delete t1 from ICBal t1,t_ICItem t2
where t1.FPeriod=1 AND t1.FYear=2016
and t1.FItemID=t2.FItemID and t2.FTrack not in(78,79,20308)
delete t1 from ICBal t1,t_ICItem t2
where t1.FPeriod=1 AND t1.FYear=2016
and t1.FItemID=t2.FItemID and t2.FTrack in(78,79,20308)And FBillInterID=-999
insert into ICBal(FBrNo,FYear,FPeriod,FItemID,FBatchNo,FAuxPropID,
FBegQty,FSecBegQty,FReceive,FSecReceive,FSend,FSecSend,FYtdReceive,FSecYtdReceive,FYtdSend,FSecYtdSend,FEndQty,FSecEndQty,FBegBal,
FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,FBegDiff,
FReceiveDiff,FSendDiff,FEndDiff,FBillInterID,FEntryID,
FStockGroupID,FYtdReceiveDiff,FYtdSendDiff)
select '0',2016,1,t1.FItemID,t1.FBatchNo,t1.FAuxPropID,
sum(t1.FBegQty),sum(t1.FSecBegQty),sum(t1.FReceive),sum(t1.FSecReceive),sum(t1.FSend),sum(t1.FSecSend),0,0,0,0,sum(t1.FEndQty),sum(t1.FSecEndQty),sum(t1.FBegBal),
sum(t1.FDebit),sum(t1.FCredit),0,0,sum(t1.FEndBal),sum(t1.FBegDiff),
sum(t1.FReceiveDiff),sum(t1.FSendDiff),sum(t1.FEndDiff),0,0,
t2.FGroupID,0,0
from ICInvBal t1 inner join t_Stock t2 on t1.FStockID=t2.FItemID
And t2.FTypeID<>502
inner join t_ICItem t3 on t1.FItemID=t3.FItemID
where t3.FTrack not in(78,79,20308)
and t1.FYear= 2016 and t1.FPeriod=1 group by t1.FItemID,t1.FBatchNo,t1.FAuxPropID,t2.FGroupID
insert into ICBal(FBrNo,FYear,FPeriod,FItemID,FBatchNo,FAuxPropID,
FBegQty,FSecBegQty,FReceive,FSecReceive,FSend,FSecSend,FYtdReceive,FSecYtdReceive,FYtdSend,FSecYtdSend,FEndQty,FSecEndQty,FBegBal,
FDebit,FCredit,FYtdDebit,FYtdCredit,FEndBal,FBegDiff,
FReceiveDiff,FSendDiff,FEndDiff,FBillInterID,FEntryID,
FStockGroupID,FYtdReceiveDiff,FYtdSendDiff)
select '0',2016,1,t1.FItemID,t1.FBatchNo,t1.FAuxPropID,
0,0,sum(t1.FReceive),sum(t1.FSecReceive),sum(t1.FSend),sum(t1.FSecSend),0,0,0,0,0,0,0,
sum(t1.FDebit),sum(t1.FCredit),0,0,0,sum(t1.FBegDiff),
sum(t1.FReceiveDiff),sum(t1.FSendDiff),sum(t1.FEndDiff),-999,0,
t2.FGroupID,0,0
from ICInvBal t1 inner join t_Stock t2 on t1.FStockID=t2.FItemID
And t2.FTypeID<>502
inner join t_ICItem t3 on t1.FItemID=t3.FItemID
where t3.FTrack in(78,79,20308)
and t1.FYear= 2016 and t1.FPeriod=1 group by t1.FItemID,t1.FBatchNo,t1.FAuxPropID,t2.FGroupID
INSERT INTO ICBal(FBrNo,FYear,FPeriod,FItemID,FBatchNo,FAuxPropID,FBegQty,FSecBegQty,
FYtdReceive,FSecYtdReceive,FYtdSend,FSecYtdSend,FBegBal,FYtdDebit,FYtdCredit,FBegDiff,FBillInterID,FStockGroupID,FEntryID)
select ICBal.FBrNo,2016,2,ICBal.FItemID,ICBal.FBatchNo,ICBal.FAuxPropID,round(ICBal.FEndQty,10),round(ICBal.FSecEndQty,10),
round(ICBal.FYtdReceive,10) ,round(ICBal.FSecYtdReceive,10), round(ICBal.FYtdSend,10), round(ICBal.FSecYtdSend,10),round(ICBal.FEndBal,2),round(ICBal.FYtdDebit,2),round(ICBal.FYtdCredit,2),round(ICBal.FEndDiff,2), ICBal.FBillInterID,ICBal.FStockGroupID,ICBal.FEntryID
From ICBal
inner join t_ICItem on ICBal.FItemID=t_ICItem.FItemID
WHERE ICBal.FYear=2016 and
ICBal.FPeriod=1 and (round(ICBal.FEndQty,10)<>0 or round(ICBal.FSecEndQty,10)<>0 or round(ICBal.FEndBal,2)<>0 or round(ICBal.FEndDiff,2)<>0)
go
DELETE FROM k3CaculateUpdatePrice
go
select FValue from t_SystemProFile where FKey='ServicePack' and FCategory='Base'
go
create table #POInvBal
(FItemID int not null ,
FStockID int not null ,
FBatchNo varchar(255) not null default '',
FAuxPropID varchar(255) not null default '',
FSPID int not null default(0),
FKFPeriod int not null default(0),
FKFDate varchar(20) not null,
FBegQty decimal(23,10) not null default(0),
FSecBegQty decimal(23,10) not null default(0),
FReceive decimal(23,10) not null default(0),
FSecReceive decimal(23,10) not null default(0),
FSend decimal(23,10) not null default(0),
FSecSend decimal(23,10) not null default(0))
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FBegQty,FSecBegQty,FSPID,FKFPeriod,FKFDate)
select FItemID,FStockID,FBatchNo,FAuxPropID,isnull(FBegQty,0),isnull(FSecBegQty,0),FSPID,FKFPeriod,FKFDate
From POInvBal
Where FPeriod = 1
and FYear=2016
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FReceive,FSecReceive,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from POInstock t1,POInstockEntry t2,t_Stock t3
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01' and t1.FTranType=72
and t1.FCheckerID<>0 and t1.FInterID=t2.FInterID and t2.FStockID=t3.FItemID
and t3.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FReceive,FSecReceive,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FDCStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from ZPStockBill t1,ZPstockBillEntry t2,t_Stock t3
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01' and t1.FTranType=6
and t1.FCheckerID<>0 and t1.FInterID=t2.FInterID and t2.FDCStockID=t3.FItemID
and t3.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FSend,FSecSend,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FDCStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from ZPStockBill t1,ZPstockBillEntry t2,t_Stock t3
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01' and t1.FTranType=26
and t1.FCheckerID<>0 and t1.FInterID=t2.FInterID and t2.FDCStockID=t3.FItemID
and t3.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FSend,FSecSend,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t5.FDCStockID ,t5.FBatchNo,t5.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t5.FDCSPID,0),t5.FKFPeriod,isnull(convert(varchar(10),t5.FKFDate,120),'')
from ICStockBill t1,ICStockBillEntry t2,ZPStockBill t3,ZPStockBillEntry t5,t_Stock t4
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01'
and t1.FInterID=t2.FInterID and t2.FSourceInterID=t3.FInterID
and t3.FInterID=t5.FInterID and t2.FSourceEntryID=t5.FEntryID
and t5.FDCStockID=t4.FItemID and t4.FTypeID in(501,502,503)
and t1.FTranType =10
And t3.FCancellation=0 and t3.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FSend,FSecSend,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t5.FStockID ,t5.FBatchNo ,t5.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t5.FDCSPID,0),t5.FKFPeriod,isnull(convert(varchar(10),t5.FKFDate,120),'')
from ICStockBill t1,ICStockBillEntry t2,POInstock t3,POInstockEntry t5,t_Stock t4
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01'
and t1.FInterID=t2.FInterID and t2.FSourceInterID=t3.FInterID
and t3.FInterID=t5.FInterID and t2.FSourceEntryID=t5.FEntryID
and t5.FStockID=t4.FItemID and t4.FTypeID in(501,502,503)
and t1.FTranType in(1,10)
And t3.FCancellation=0 and t3.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FSend,FSecSend,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FSCStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FSCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from POStockBill t1,POStockBillEntry t2,t_Stock t4
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01'
and t1.FInterID=t2.FInterID and t1.FTranType=74
and t2.FSCStockID=t4.FItemID and t4.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FReceive,FSecReceive,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FDCStockID ,t2.FBatchNo ,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from POStockBill t1,POStockBillEntry t2,t_Stock t4
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01'
and t1.FInterID=t2.FInterID and t1.FTranType=74
and t2.FDCStockID=t4.FItemID and t4.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FSend,FSecSend,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from POInstock t1,POInstockEntry t2,t_Stock t3
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01' and t1.FTranType=73
and t1.FCheckerID<>0 and t1.FInterID=t2.FInterID and t2.FStockID=t3.FItemID
and t3.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FReceive,FSecReceive,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FDCStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from ICSTJGBill t1,ICSTJGBillEntry t2,t_Stock t3
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01' and t1.FTranType=92
and t1.FCheckerID<>0 and t1.FInterID=t2.FInterID and t2.FDCStockID=t3.FItemID
and t3.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FReceive,FSecReceive,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from POInStock t1,POInStockEntry t2,t_Stock t3
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01' and t1.FTranType=702
and t1.FCheckerID<>0 and t1.FInterID=t2.FInterID and t2.FStockID=t3.FItemID
and t3.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FSend,FSecSend,FSPID,FKFPeriod,FKFDate)
select t2.FItemID,t2.FSCStockID,t2.FBatchNo,t2.FAuxPropID,t2.FQty,t2.FSecQty,IsNull(t2.FDCSPID,0),t2.FKFPeriod,isnull(convert(varchar(10),t2.FKFDate,120),'')
from ICSTJGBill t1,ICSTJGBillEntry t2,t_Stock t3
where t1.FDate>='2016-01-01' and t1.FDate<'2016-02-01' and t1.FTranType=137
and t1.FCheckerID<>0 and t1.FInterID=t2.FInterID and t2.FSCStockID=t3.FItemID
and t3.FTypeID in(501,502,503)
And t1.FCancellation=0 and t1.FStatus>0
Update t1 set t1.FBegQty=round(t1.FBegQty,10),t1.FSecBegQty=round(t1.FSecBegQty,10),
t1.FReceive=round(t1.FReceive,10),t1.FSecReceive=round(t1.FSecReceive,10),
t1.FSend=round(t1.FSend,10),t1.FSecSend=round(t1.FSecSend,10)
from #POInvBal t1 left join t_ICItem u1 on t1.fitemid=u1.fitemid
delete t1 from POInvBal t1,t_ICItem t2
Where FPeriod = 1 And FYear = 2016
AND t1.FItemID=t2.FItemID
Delete P From #TmpInvBal_1 P Inner Join t_stock t On P.FStockID=t.FItemID Where t.FTypeID<>502
insert into #POInvBal(FItemID,FStockID,FBatchNo,FAuxPropID,FSPID,FKFPeriod,FKFDate,FBegQty,FSecBegQty,FReceive,FSecReceive,FSend,FSecSend)
select u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FAuxPropID,u1.FStockPlaceID,u1.FKFPeriod,u1.FKFDate,round(Sum(u1.FBegQty),10),round(Sum(u1.FSecBegQty),10),round(Sum(u1.FReceive),10),round(Sum(u1.FSecReceive),10),round(sum(u1.FSend),10),round(sum(u1.FSecSend),10)
from #TmpInvBal_1 u1
left join t_icitem t1 on u1.FItemID=t1.FItemID
group by u1.FStockID,u1.FItemID,u1.FBatchNo,u1.FAuxPropID,u1.FStockPlaceID,u1.FKFPeriod,u1.FKFDate
Drop Table #TmpInvBal_1
insert into POInvBal(FBrNo,FYear,FPeriod,FItemID,FStockID,FBatchNo,FAuxPropID,FSPID,FKFPeriod,FKFDate,FBegQty,FSecBegQty,FReceive,FSecReceive,FSend,FSecSend)
select '0',2016,1,u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FAuxPropID,u1.FSPID,u1.FKFPeriod,u1.FKFDate,round(Sum(u1.FBegQty),10),round(Sum(u1.FSecBegQty),10),round(Sum(u1.FReceive),10),round(Sum(u1.FSecReceive),10),round(sum(u1.FSend),10),round(sum(u1.FSecSend),10)
from #POInvBal u1
left join t_icitem t1 on u1.FItemID=t1.FItemID
group by u1.FStockID,u1.FItemID,u1.FBatchNo,u1.FAuxPropID,u1.FSPID,u1.FKFPeriod,u1.FKFDate
update POInvBal set FEndQty=FBegQty+FReceive-FSend,
FSecEndQty=FSecBegQty+FSecReceive-FSecSend
Where FPeriod = 1
and FYear=2016
insert into POInvBal(FBrNo,FYear,FPeriod,FItemID,FStockID,FBatchNo,FAuxPropID,FBegQty,FSecBegQty,FSPID,FKFPeriod,FKFDate)
select u1.FBrNo,2016,2,u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FAuxPropID,round(u1.FEndQty,10),round(u1.FSecEndQty,10),u1.FSPID,u1.FKFPeriod,u1.FKFDate
From POInvBal u1
left join t_icitem t1 on u1.FItemID=t1.FItemID
Where FPeriod = 1
and FYear=2016
drop table #POInvBal
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go
12345.jpg(24.30KB)
推荐阅读