EXEC CheckInventory
2、我們進入SQL企業管理器，也不難得到CheckInventory的詳細代碼[K/310.1]
CREATE Procedure CheckInventory
as
Declare @CurYear Int, --當前年份
@CurPeriod int, --起始的會計期間
@StartPeriod SmallInt, --啟用期間
@NowPeriod Smallint, --當前期間
@StartTime DateTime, --期間開始日期
@EndTime DateTime, --期間結束日期
@DiffCount Decimal(28,0) --計算是否有差異數量
--2.取出當前年份
SELECT @CurPeriod=FValue FROM t_Systemprofile WHERE FKey='CurrentPeriod' And FCategory='IC'
SELECT @CurYear=FValue FROM t_Systemprofile WHERE FKey='CurrentYear' And FCategory='IC'
--3.取出當前期間的起始日期
EXEC GetPeriodStartEnd @CurPeriod,@StartTime output,@EndTime output
SELECT FItemID,FStockID,FBatchNo,FStockPlaceID,case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) End as FKFDate,ISNULL(FKFPeriod,'') as FKFPeriod,
Sum(FBegQty) As FQty ,0 As FQtyLock Into #RealTimeQty
FROM ICInvBal WHERE FPeriod=@CurPeriod And FYear=@CurYear
Group By FItemID,FStockID,FBatchNo,FStockPlaceID,FKFDate,FKFPeriod
Having sum(FBegQty)<>0
--SELECT FItemID,FStockID,FBatchNo,FQty FROM #RealTimeQty
Insert Into #RealTimeQty
SELECT t1.FItemID,t2.FDCStockID As FStockID,IsNull(t1.FBatchNO,''),ISNULL(t1.FDCSPID,'') as FStockPlaceID, case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end ,ISNULL(t1.FKFPeriod,''),
Sum(t1.FQty) As FQty,0 As FQtyLock
FROM ICStockBillEntry t1,ICStockBill t2
WHERE 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) And FDate>=@StartTime
Group By t1.FItemID,t2.FDCStockID,t1.FBatchNo,t1.FDCSPID,t1.FKFDate,t1.FKFPeriod
......[以下代碼省略]
3、我們也不難看出其中幾個參數的意思
@CurYear Int, --當前年份
@CurPeriod int, --起始的會計期間 [原代碼註釋可能有歧義,應為當前期間，當然，也可以解釋為校對即時庫存的起始期間]
@StartPeriod SmallInt, --啟用期間 [該參數基本沒有用]
@NowPeriod Smallint, --當前期間 [源代碼註釋錯了,該參數基本沒有用]
@StartTime DateTime, --期間開始日期
@EndTime DateTime, --期間結束日期[該參數基本沒有用]
@DiffCount Decimal(28,0) --計算是否有差異數量
除了幾個沒有使用的參數外,其他幾個重要的參數就是時間節點了。
從SQL代碼可以看出，“校對”即時庫存的功能應該就是從當前期間的第壹天開始的。
4、我們是否可以經過改進，來控制校對即時庫存的起始點呢？應該是可以的。
CREATE Procedure CheckInventoryEX
@CurYear Int=0, --起始年份
@CurPeriod int=0, --起始會計期間
as
Declare @StartPeriod SmallInt, --啟用期間
@NowPeriod Smallint, --當前期間
@StartTime DateTime, --期間開始日期
@EndTime DateTime, --期間結束日期
@DiffCount Decimal(28,0) --計算是否有差異數量
--2.取出當前年份
if @CurPeriod<=0 or @CurPeriod>12
begin
SELECT @CurPeriod=FValue FROM t_Systemprofile WHERE FKey='CurrentPeriod' And FCategory='IC'
end
if @CurYear<=0
begin
SELECT @CurYear=FValue FROM t_Systemprofile WHERE FKey='CurrentYear' And FCategory='IC'
end
--3.取出當前期間的起始日期
EXEC GetPeriodStartEnd @CurPeriod,@StartTime output,@EndTime output
SELECT FItemID,FStockID,FBatchNo,FStockPlaceID,case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) End as FKFDate,ISNULL(FKFPeriod,'') as FKFPeriod,
Sum(FBegQty) As FQty ,0 As FQtyLock Into #RealTimeQty
FROM ICInvBal WHERE FPeriod=@CurPeriod And FYear=@CurYear
Group By FItemID,FStockID,FBatchNo,FStockPlaceID,FKFDate,FKFPeriod
Having sum(FBegQty)<>0
--SELECT FItemID,FStockID,FBatchNo,FQty FROM #RealTimeQty
Insert Into #RealTimeQty
SELECT t1.FItemID,t2.FDCStockID As FStockID,IsNull(t1.FBatchNO,''),ISNULL(t1.FDCSPID,'') as FStockPlaceID, case when FKFDate is null then '' else convert(varchar(10),FKFDate,120) end ,ISNULL(t1.FKFPeriod,''),
Sum(t1.FQty) As FQty,0 As FQtyLock
FROM ICStockBillEntry t1,ICStockBill t2
WHERE 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) And FDate>=@StartTime
Group By t1.FItemID,t2.FDCStockID,t1.FBatchNo,t1.FDCSPID,t1.FKFDate,t1.FKFPeriod