WISE12.2按截止日期新建盘点方案提示：没有可用的数据
跟踪了下，实际上是查询可用数据的过程中报错了。因为SQL语句写错了！
select * from Truncate Table ICInvBackupTemp --这个是跟踪到的语句，这里有错误，所以前面是select * from
Insert Into ICInvBackupTemp
(FBrNo,FItemID,FStockID,FBatchNo,FMtoNo,FStockPlaceID,FKFDate,FKFPeriod,FQty ,FQytLock,
FQtyAct,FCheckQty,FSelect,FAdj,FDateBackup,FMaxBillInterID,FAuxQty,FAuxQtyAct,
FAuxCheckQty,FInterID,FSecQty,FAdjQty,FAuxPropID,FSupplyID)
SELECT 0 as FBrNo,t9.FItemID,t9.FStockID,t9.FBatchNo,t9.FMtoNo,t9.FStockPlaceID,case when t9.FKFDate is null
then '' else convert(varchar(10),t9.FKFDate,120) End as FKFDate,ISNULL(t9.FKFPeriod,'') as FKFPeriod,
Sum(t9.FBegQty) As FQty ,0 As FQtyLock,
Sum(t9.FBegQty) As FQtyAct,Sum(t9.FBegQty) As FCheckQty,0 as FSelect,0 as FAdj,'2008-02-20' as FDateBackup,2091
as FMaxBillInterID,Sum(t9.FBegQty / t3.FCoefficient) as FAuxQty,Sum(t9.FBegQty / t3.FCoefficient) as FAuxQtyAct,
Sum(t9.FBegQty / t3.FCoefficient) as FAuxCheckQty,'4' as FInterID,Sum(t9.FSecBegQty) as FSecQty,
0 as FAdjQty,t9.FAuxPropID,t9.FSupplyID
FROM ICInvBal t9 inner join t_ICItem t4 on t9.fitemid = t4.fitemid
left join t_stockplace t5 on t9.fstockplaceid = t5.fspid
left join t_Supplier ts on t9.FSupplyID = ts.FItemID
left join t_MeasureUnit t3 on t4.FStoreUnitID=t3.FMeasureUnitID WHERE FPeriod=2 And FYear='2008'
and t9.fstockid in (339) and datediff(dd,'2008-02-20',t9.FStockInDate)<=0
Group By t9.FItemID,t9.FStockID,t9.FBatchNo,t9.FMtoNo,t9.FStockPlaceID,t9.FKFDate,t9.FKFPeriod,t9.FAuxPropID ---实际错误的点：t9.FSupplyID
Having Sum(t9.FBegQty) <> 0
Insert Into ICInvBackupTemp
(FBrNo,FItemID,FStockID,FBatchNo,FMtoNo,FStockPlaceID,FKFDate,FKFPeriod,FQty ,FQytLock,
FQtyAct,FCheckQty,FSelect,FAdj,FDateBackup,FMaxBillInterID,FAuxQty,FAuxQtyAct,
FAuxCheckQty,FInterID,FSecQty,FAdjQty,FAuxPropID,FSupplyID)
SELECT 0 as FBrNo,t1.FItemID,t1.FDCStockID As FStockID,IsNull(t1.FBatchNO,'') as FBatchNo,ISNULL(t1.FMtoNo,'') AS FMtoNo,ISNULL(t1.FDCSPID,'') as FStockPlaceID,
case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end as FKFDate,ISNULL(t1.FKFPeriod,'') as FKFPeriod,
Sum(Case When t2.FTranType In (1,2,5,10,40,41) Then t1.FQty When t2.FTranType In (21,28,29,43) Then -t1.FQty End) As FQty,0 As FQtyLock,
Sum(Case When t2.FTranType In (1,2,5,10,40,41) Then t1.FQty When t2.FTranType In (21,28,29,43) Then -t1.FQty End) As FQtyAct,
Sum(Case When t2.FTranType In (1,2,5,10,40,41) Then t1.FQty When t2.FTranType In (21,28,29,43) Then -t1.FQty End) As FCheckQty,
0 as FSelect,0 as FAdj,'2008-02-20' as FDateBackup,2091 as FMaxBillInterID,
Sum(Case When t2.FTranType In (1,2,5,10,40,41) Then t1.FQty When t2.FTranType In (21,28,29,43) Then -t1.FQty End)/t6.fcoefficient as FAuxQty,
Sum(Case When t2.FTranType In (1,2,5,10,40,41) Then t1.FQty When t2.FTranType In (21,28,29,43) Then -t1.FQty End)/t6.fcoefficient as FAuxQtyAct,
Sum(Case When t2.FTranType In (1,2,5,10,40,41) Then t1.FQty When t2.FTranType In (21,28,29,43) Then -t1.FQty End)/t6.fcoefficient as FAuxCheckQty,
'4' as FInterID,Sum(Case When t2.FTranType In (1,2,5,10,40,41) Then t1.FSecQty When t2.FTranType In (21,28,29,43) Then -t1.FSecQty End) as FSecQty,
0 as FAdjQty,t1.FAuxPropID,t1.FEntrySupply
FROM ICStockBillEntry t1 inner Join ICStockBill t2
on t1.FInterID=t2.FInterID And (t2.FCheckerID>0 or t2.FCheckerID <0 or FUpStockWhenSave=1)
And t2.FCancelLation=0
And t2.FTranType In (1,2,5,10,40,41,21,28,29,43) And t2.FDate>='2008-02-01' And t2.FDate<='2008-02-20'
inner join t_ICItem t4 on t1.fitemid=t4.fitemid
inner join t_stockplace t5 on isnull(t1.FDCSPID,0)=t5.fspid
inner join t_Supplier ts on t1.FEntrySupply = ts.FItemID
inner join t_measureunit t6 on t1.funitid=t6.fmeasureunitid and t1.FDCStockID in (339)
AND NOT (t2.FTranType =1 AND ISNULL(t2.FPOMode,0) = 36681)
Group By t1.FItemID,t1.FDCStockID,t1.FBatchNo,t1.FMtoNo,t1.FDCSPID,t1.FKFDate,t1.FKFPeriod,t1.FAuxPropID,t6.fcoefficient
Insert Into ICInvBackupTemp
(FBrNo,FItemID,FStockID,FBatchNo,FMtoNo,FStockPlaceID,FKFDate,FKFPeriod,FQty ,FQytLock,
FQtyAct,FCheckQty,FSelect,FAdj,FDateBackup,FMaxBillInterID,FAuxQty,FAuxQtyAct,
FAuxCheckQty,FInterID,FSecQty,FAdjQty,FAuxPropID,FSupplyID)
SELECT 0 as FBrNo,t1.FItemID,t1.FSCStockID As FStockID,IsNull(t1.FBatchNO,'') as FBatchNo,ISNULL(t1.FMtoNo,'') AS FMtoNo,ISNULL(t1.FDCSPID,'') as FStockPlaceID,
case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end as FKFDate,ISNULL(t1.FKFPeriod,'') as FKFPeriod,
Sum(-t1.FQty) As FQty,0 As FQtyLock,
Sum(-t1.FQty) As FQtyAct,Sum(-t1.FQty) As FCheckQty,0 as FSelect,0 as FAdj,'2008-02-20' as FDateBackup,
2091 as FMaxBillInterID,Sum(-t1.FQty)/t6.fcoefficient as FAuxQty,Sum(-t1.FQty)/t6.fcoefficient as FAuxQtyAct,
Sum(-t1.FQty)/t6.fcoefficient as FAuxCheckQty,'4' as FInterID,Sum(-t1.FSecQty) as FSecQty,
0 as FAdjQty,t1.FAuxPropID,t1.FEntrySupply
FROM ICStockBillEntry t1 inner join ICStockBill t2
on t1.FInterID=t2.FInterID And (t2.FCheckerID>0 or t2.FCheckerID <0 or FUpStockWhenSave=1)
And t2.FTranType = 24 And datediff(dd,'2008-02-20',t2.fdate)<=0 and datediff(dd,t2.fdate,'2008-02-01')<=0
And t2.FCancelLation=0
inner join t_ICItem t4 on t1.fitemid=t4.fitemid
inner join t_stockplace t5 on isnull(t1.FDCSPID,0)=t5.fspid
inner join t_Supplier ts on t1.FEntrySupply = ts.FItemID
inner join t_measureunit t6 on t1.funitid=t6.fmeasureunitid and t1.FSCStockID in (339)
Group By t1.FItemID,t1.FSCStockID,t1.FBatchNo,t1.FMtoNo,t1.FDCSPID,t1.FKFDate,t1.FKFPeriod,t1.FAuxPropID,t6.fcoefficient
Insert Into ICInvBackupTemp
(FBrNo,FItemID,FStockID,FBatchNo,FMtoNo,FStockPlaceID,FKFDate,FKFPeriod,FQty ,FQytLock,
FQtyAct,FCheckQty,FSelect,FAdj,FDateBackup,FMaxBillInterID,FAuxQty,FAuxQtyAct,
FAuxCheckQty,FInterID,FSecQty,FAdjQty,FAuxPropID,FSupplyID)
SELECT 0 as FBrNo,t1.FItemID,t1.FSCStockID As FStockID,IsNull(t1.FBatchNO,'') as FBatchNo,ISNULL(t1.FMtoNo,'') AS FMtoNo,ISNULL(t1.FSCSPID,'') as FStockPlaceID,
case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end as FKFDate,ISNULL(t1.FKFPeriod,'') as FKFPeriod,
Sum(-t1.FQty) As FQty,0 As FQtyLock,
Sum(-t1.FQty) As FQtyAct,Sum(-t1.FQty) As FCheckQty,0 as FSelect,0 as FAdj,'2008-02-20' as FDateBackup,
2091 as FMaxBillInterID,Sum(-t1.FQty)/t6.fcoefficient as FAuxQty,Sum(-t1.FQty)/t6.fcoefficient as FAuxQtyAct,
Sum(-t1.FQty)/t6.fcoefficient as FAuxCheckQty,'4' as FInterID,Sum(-t1.FSecQty) as FSecQty,
0 as FAdjQty,t1.FAuxPropID,t1.FEntrySupply
FROM ICStockBillEntry t1 INNER JOIN ICStockBill t2
on t1.FInterID=t2.FInterID And (t2.FCheckerID>0 or t2.FCheckerID <0 or FUpStockWhenSave=1)
And t2.FTranType = 41 And datediff(dd,'2008-02-20',t2.fdate)<=0 and datediff(dd,t2.fdate,'2008-02-01')<=0
And t2.FCancelLation=0
inner join t_ICItem t4 on t1.fitemid=t4.fitemid
inner join t_stockplace t5 on isnull(t1.FSCSPID,0)=t5.fspid
inner join t_Supplier ts on t1.FEntrySupply = ts.FItemID
inner join t_measureunit t6 on t1.funitid=t6.fmeasureunitid and t1.FSCStockID in (339)
Group By t1.FItemID,t1.FSCStockID,t1.FBatchNo,t1.FMtoNo,t1.FSCSPID,t1.FKFDate,t1.FKFPeriod,t1.FAuxPropID,t6.fcoefficient
Insert Into ICInvBackupTemp
(FBrNo,FItemID,FStockID,FBatchNo,FMtoNo,FStockPlaceID,FKFDate,FKFPeriod,FQty ,FQytLock,
FQtyAct,FCheckQty,FSelect,FAdj,FDateBackup,FMaxBillInterID,FAuxQty,FAuxQtyAct,
FAuxCheckQty,FInterID,FSecQty,FAdjQty,FAuxPropID,FSupplyID)
SELECT 0 as FBrNo,t1.FItemID,t1.FStockID As FStockID,IsNull(t1.FBatchNO,'') as FBatchNo,ISNULL(t1.FToMtoNo,'') AS FMtoNo,ISNULL(t1.FSPID,'') as FStockPlaceID,
case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end as FKFDate,ISNULL(t1.FKFPeriod,'') as FKFPeriod,
Sum(t1.FChangeBaseQty) As FQty,0 As FQtyLock,
Sum(t1.FChangeBaseQty) As FQtyAct,Sum(t1.FChangeBaseQty) As FCheckQty,0 as FSelect,0 as FAdj,'2008-02-20' as FDateBackup,
2091 as FMaxBillInterID,Sum(t1.FChangeBaseQty)/t6.fcoefficient as FAuxQty,Sum(t1.FChangeBaseQty)/t6.fcoefficient as FAuxQtyAct,
Sum(t1.FChangeBaseQty)/t6.fcoefficient as FAuxCheckQty,'4' as FInterID,Sum(t1.FChangeSecQty) as FSecQty,
0 as FAdjQty,t1.FAuxPropID,t1.FEntrySupply
FROM t_MTOChangeEntry t1 INNER JOIN t_MTOChange t2
on t1.FID=t2.FID And (t2.FCheckerID>0 or t2.FCheckerID <0 or FUpStockWhenSave=1)
And t2.FClassTypeID = 1107011 And datediff(dd,'2008-02-20',t2.fdate)<=0 and datediff(dd,t2.fdate,'2008-02-01')<=0
inner join t_ICItem t4 on t1.fitemid=t4.fitemid
inner join t_stockplace t5 on isnull(t1.FSPID,0)=t5.fspid
inner join t_Supplier ts on t1.FEntrySupply = ts.FItemID
inner join t_measureunit t6 on t1.funitid=t6.fmeasureunitid and t1.FStockID in (339)
Group By t1.FItemID,t1.FStockID,t1.FBatchNo,t1.FToMtoNo,t1.FSPID,t1.FKFDate,t1.FKFPeriod,t1.FAuxPropID,t6.fcoefficient
Insert Into ICInvBackupTemp
(FBrNo,FItemID,FStockID,FBatchNo,FMtoNo,FStockPlaceID,FKFDate,FKFPeriod,FQty ,FQytLock,
FQtyAct,FCheckQty,FSelect,FAdj,FDateBackup,FMaxBillInterID,FAuxQty,FAuxQtyAct,
FAuxCheckQty,FInterID,FSecQty,FAdjQty,FAuxPropID,FSupplyID)
SELECT 0 as FBrNo,t1.FItemID,t1.FStockID As FStockID,IsNull(t1.FBatchNO,'') as FBatchNo,ISNULL(t1.FFromMtoNo,'') AS FMtoNo,ISNULL(t1.FSPID,'') as FStockPlaceID,
case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end as FKFDate,ISNULL(t1.FKFPeriod,'') as FKFPeriod,
Sum(-t1.FChangeBaseQty) As FQty,0 As FQtyLock,
Sum(-t1.FChangeBaseQty) As FQtyAct,Sum(-t1.FChangeBaseQty) As FCheckQty,0 as FSelect,0 as FAdj,'2008-02-20' as FDateBackup,
2091 as FMaxBillInterID,Sum(-t1.FChangeBaseQty)/t6.fcoefficient as FAuxQty,Sum(-t1.FChangeBaseQty)/t6.fcoefficient as FAuxQtyAct,
Sum(-t1.FChangeBaseQty)/t6.fcoefficient as FAuxCheckQty,'4' as FInterID,Sum(-t1.FChangeSecQty) as FSecQty,
0 as FAdjQty,t1.FAuxPropID,t1.FEntrySupply
FROM t_MTOChangeEntry t1 INNER JOIN t_MTOChange t2
on t1.FID=t2.FID And (t2.FCheckerID>0 or t2.FCheckerID <0 or FUpStockWhenSave=1)
And t2.FClassTypeID = 1107011 And datediff(dd,'2008-02-20',t2.fdate)<=0 and datediff(dd,t2.fdate,'2008-02-01')<=0
inner join t_ICItem t4 on t1.fitemid=t4.fitemid
inner join t_stockplace t5 on isnull(t1.FSPID,0)=t5.fspid
inner join t_Supplier ts on t1.FEntrySupply = ts.FItemID
inner join t_measureunit t6 on t1.funitid=t6.fmeasureunitid and t1.FStockID in (339)
Group By t1.FItemID,t1.FStockID,t1.FBatchNo,t1.FFromMtoNo,t1.FSPID,t1.FKFDate,t1.FKFPeriod,t1.FAuxPropID,t6.fcoefficient
Insert Into ICInvBackupTemp
(FBrNo,FItemID,FStockID,FBatchNo,FMtoNo,FStockPlaceID,FKFDate,FKFPeriod,FQty ,FQytLock,
FQtyAct,FCheckQty,FSelect,FAdj,FDateBackup,FMaxBillInterID,FAuxQty,FAuxQtyAct,
FAuxCheckQty,FInterID,FSecQty,FAdjQty,FAuxPropID,FSupplyID)
SELECT 0 as FBrNo,t1.FItemID,t1.FStockID As FStockID,IsNull(t1.FBatchNO,'') as FBatchNo,ISNULL(t1.FMTONo,'') AS FMTONo,ISNULL(t1.FSPID,'') as FStockPlaceID,
case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end as FKFDate,ISNULL(t1.FKFPeriod,'') as FKFPeriod,
Sum(t1.FQty) As FQty,0 As FQtyLock,
Sum(t1.FQty) As FQtyAct,
Sum(t1.FQty) As FCheckQty,
0 as FSelect,0 as FAdj,'2008-02-20' as FDateBackup,2091 as FMaxBillInterID,
Sum( t1.FQty )/t6.fcoefficient as FAuxQty,
Sum( t1.FQty )/t6.fcoefficient as FAuxQtyAct,
Sum( t1.FQty )/t6.fcoefficient as FAuxCheckQty,
'4 ' as FInterID,
Sum(t1.FSecQty) as FSecQty,
0 as FAdjQty,t1.FAuxPropID,t2.FSupplyID
FROM ICVMIInStockEntry t1 inner Join ICVMIInStock t2
on t1.FID=t2.FID And (t2.FCheckerID>0 or t2.FCheckerID <0 or t2.FUpStockWhenSave=1)
--And t2.FCancelLation=0
And t2.FClassTypeID = 1007601 And datediff(dd,'2008-02-20',t2.fdate)<=0 and datediff(dd,t2.fdate,'2008-02-01')<=0
inner join t_ICItem t4 on t1.fitemid=t4.fitemid
inner join t_stockplace t5 on isnull(t1.FSPID,0)=t5.fspid
inner join t_Supplier ts on t2.FSupplyID = ts.FItemID
inner join t_measureunit t6 on t1.funitid=t6.fmeasureunitid and t1.FStockID in (339)
Group By t1.FItemID,t1.FStockID,t1.FBatchNo,t1.FMtoNo,t1.FSPID,t1.FKFDate,t1.FKFPeriod,t1.FAuxPropID,t6.fcoefficient
推荐阅读