谁帮忙看看 业务结账提示错误

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