SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER         function fn_dGetHistoryInfo(@Prod varchar(40), @BeginDate Datetime, @ProdInsDate DateTime,
  @CostType Int, @QtyRule Int, @AlltType Int, @ObVerse Int)
returns @ResultTable table (PROD varchar(40) primary key, FQTY decimal(30,10),FOQTY decimal(30,10), FCOST decimal(30,10), FAMT decimal(30,10))
as 
begin
  declare @Result int
  set @Result = 0
  declare @eDate datetime
  declare @dFCost decimal(30,10)
  declare @dFQty decimal(30,10)
  declare @dFOQty decimal(30,10)
  declare @dFAmt decimal(30,10)
  declare @iY int
  declare @iM int
  declare @iD int
  declare @tDate varchar(10)
  declare @dTmpAmt  decimal(30,10) set @dTmpAmt  = 0
  declare @dBalQty  decimal(30,10) set @dBalQty  = 0
  declare @dBalOQty decimal(30,10) set @dBalOQty = 0
  declare @dBalCost decimal(30,10) set @dBalCost = 0
  declare @dBalAmt  decimal(30,10) set @dBalAmt  = 0
  declare @dProdFCost  decimal(30,10) set @dProdFCost  = 0
  declare @CloseDate datetime set @CloseDate = (select CLOSEDATE from SYSCTRL)
  if @CostType = 0 
  begin
    set @eDate = @BeginDate-1
    if @eDate < @ProdInsDate
      set @eDate = @ProdInsDate
    set @dFCost = dbo.fn_dGetCost(@Prod, @eDate, @CostType)
  end
  else if @CostType = 2 
  begin
    if @BeginDate <= @ProdInsDate 
      set @dFCost = (select FCOST from PRODUCT where CODE = @Prod)
    else begin
      set @iY = Year(@BeginDate) set @iM = Month(@BeginDate) set @iD = Day(@BeginDate)
      if @iD <> 1
      begin
        set @eDate = @BeginDate
        set @dFCost = dbo.fn_dGetCost(@Prod, @eDate, @CostType)
      end
      else begin
        if @iM <> 1
          set @tDate = cast(@iY as varchar(4))+'/'+ cast(@iM-1 as varchar(2))+'/28'
        else
          set @tDate = cast(@iY-1 as varchar(4))+'/12/28'
        set @eDate = cast(@tDate as datetime)
        set @dFCost = dbo.fn_dGetCost(@Prod, @eDate, @CostType)
      end
    end
  end
  else set @dFCost = (select FCOST from PRODUCT where CODE = @Prod)
  set @dProdFCost = (select FCOST from PRODUCT where CODE = @Prod)
  set @dFQty = (select FQTY from PRODUCT where CODE = @Prod)
  set @dFOQty = (select FOUQTY from PRODUCT where CODE=@Prod)
  if @CostType = 0
    set @dFAmt = @dFQty * @dFCost
  else
    --set @dFAmt = Round(@dFQty *@dProdFCost, dbo.fn_iPam('A'), 0)
    --bug11126 与6881一样,不要四舍五入
    set @dFAmt = @dFQty *@dProdFCost
  set @dTmpAmt = @dTmpAmt + @dFAmt
  set @dBalQty = @dBalQty + @dFQty
  set @dBalOQty = @dBalOQty + @dFOQty
  --bug10868 与6881一样,需四舍五入
  --set @dBalAmt = @dBalAmt + @dFAmt
  set @dBalAmt = @dBalAmt + Round(@dFAmt, dbo.fn_iPam('A'), 0)
  set @dBalCost= @dFCost
  --declare for count cursor data--
  declare @v1_sdate datetime
  declare @v1_qty decimal(30,10)
  declare @v1_ouqty decimal(30,10)
  declare @v1_qtytran int
  declare @v1_qtyop int
  declare @v1_sortid varchar(2)
  declare @v1_cost decimal(30,10)
  declare @v1_amt decimal(30,10)
  declare @v1_amt1 decimal(30,10)
  declare @v1_costtran int
  declare @iSide int
  --declare for count cursor data--
  declare pt_v1 cursor for
    select T.SDATE,T.QTY,T.OUQTY,T.QTYTRAN,T.QTYOP,T.SORTID,T.COST,T.AMT*T.QTYOP AMT,T.COSTTRAN,T.AMT AS AMT1
      from PRODTRAN T  
    where (T.SDATE > @CloseDate and T.SDATE <  @BeginDate)
      and (T.SORTID <> '5C')
      and ((@QtyRule = 0) or ((@QtyRule = 1) and (T.SORTID not in ('1D','1E','5D','5E'))))
      and ((@AlltType = 1) or ((@AlltType = 0) and ((T.SORTID <> '1Z') AND (T.SORTID <> '51'))))
      and (T.PROD = @Prod)
    order by T.SDATE,T.SORTID,T.VCHRCODE,T.ERI
  open pt_v1
  fetch next from pt_v1
  into @v1_sdate, @v1_qty,@v1_ouqty, @v1_qtytran, @v1_qtyop, @v1_sortid, @v1_cost, @v1_amt, @v1_costtran,@v1_amt1
  while @@FETCH_STATUS = 0
  begin
    set @dFQty = 0
    set @dFOQty = 0
    set @dFAmt = 0
    set @iSide = dbo.fn_iGetSide(@v1_sortid, @v1_qtyop)
    if @v1_sortid = '1C'
      set @dFAmt = Round(@v1_amt, dbo.fn_iPam('A'), 0)

    else
    begin
      if @v1_qtytran = 1
      begin
        set @dFQty = @v1_qty*@v1_qtyop
        set @dFOQty =@v1_ouqty*@v1_qtyop
        if (@iSide = 1) or (@v1_sortid = '1W') and (@v1_qtyop = 1)
        begin
          if (@v1_sortid = '1W')
            set @dFAmt = Round(@v1_amt1, dbo.fn_iPam('A'), 0)
          else
          begin 
            if @v1_costtran = 1
	       set @dFAmt = Round(@v1_amt, dbo.fn_iPam('A'), 0)
	    else
    	       set @dFAmt = Round(@v1_qty*@v1_cost*@v1_qtyop, dbo.fn_iPam('A'), 0)	
          end	
        end
        else if (@iSide =- 1) and (@v1_sortid = '1W') and (@v1_qtyop = -1)
          set @dFAmt = Round(@v1_amt1, dbo.fn_iPam('A'), 0)

        else if (@iSide =- 1)
        begin
          if @CostType = 2
            set @dFAmt = Round(@v1_qty*@v1_qtyop*dbo.fn_dGetCost(@Prod, @v1_sdate, 2), dbo.fn_iPam('A'), 0)
          else
          begin --判断出货时，结存是否为0
            if Round(@dBalQty + @dFQty, dbo.fn_iPam('Q'), 0) = 0
              set @dFAmt = @dBalAmt*@v1_qtyop
            else
              set @dFAmt = Round(@v1_qty*@v1_cost*@v1_qtyop, dbo.fn_iPam('A'), 0)
          end
        end
      end
      else if @v1_sortid = '1X'
        set @dFAmt = Round(@v1_amt*@v1_qtyop, dbo.fn_iPam('A'), 0)
    end
    set @dBalQty = Round(@dBalQty + @dFQty, dbo.fn_iPam('Q'), 0)
    set @dBalOQty = Round(@dBalOQty + @dFOQty, dbo.fn_iPam('Q'), 0) 
    set @dBalAmt = @dBalAmt + @dFAmt
    if @CostType = 2
      set @dTmpAmt = @dTmpAmt + Round(@dFQty*@dFCost, dbo.fn_iPam('A'), 0)
    else
      set @dTmpAmt = @dTmpAmt + @dFAmt
    if (@v1_costtran <> 0) and (@dBalQty <> 0)
      set @dBalCost = Round(@dTmpAmt / @dBalQty, dbo.fn_iPam('P'), 0)
    if @CostType <> 1
    begin
      declare @tmpSDate datetime
      if (@CostType = 0) 
      begin
        set @tmpSDate = @BeginDate - 1
        if @tmpSDate < @ProdInsDate 
          set @tmpSDate = @ProdInsDate
      end
      else if @CostType = 2
        set @tmpSDate = @BeginDate
      if (@CostType = 0) or ((@CostType = 2) and (@BeginDate > @ProdInsDate))
      begin
        set @iY = Year(@tmpSDate) set @iM = Month(@tmpSDate) set @iD = Day(@tmpSDate)
        if @iD <> 1
          set @dBalCost = dbo.fn_dGetCost(@Prod, @tmpSDate, @CostType) 
        else begin
          if @iM <> 1
            set @tDate = cast(@iY as varchar(4))+'/'+ cast(@iM-1 as varchar(2))+'/28'
          else
            set @tDate = cast(@iY-1 as varchar(4))+'/12/28'
          set @tmpSDate = cast(@tDate as datetime)
          set @dBalCost = dbo.fn_dGetCost(@Prod, @tmpSDate, @CostType)
        end
      end
    end
    if @ObVerse = 0
    begin
      set @dTmpAmt = @dBalCost*@dBalQty
      set @dBalAmt = Round(@dBalCost*@dBalQty, dbo.fn_iPam('A'), 0)
    end

    fetch next from pt_v1
    into @v1_sdate, @v1_qty,@v1_ouqty, @v1_qtytran, @v1_qtyop, @v1_sortid, @v1_cost, @v1_amt, @v1_costtran,@v1_amt1
  end
  close pt_v1
  deallocate pt_v1
  declare @InsertAmt decimal(30,10)
  if @ObVerse = 0
    set @InsertAmt = @dTmpAmt
  else
    set @InsertAmt = @dBalAmt
  insert @ResultTable  
    select @Prod, @dBalQty,@dBalOQty, @dBalCost, @InsertAmt
  return
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO