ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [ProceBDCD]

[ProceBDCD]

原创 Linux操作系统 作者:huangjt007 时间:2012-04-23 00:30:52 0 删除 编辑

USE [prise];
GO
SET  ANSI_NULLS ON;
GO
SET  QUOTED_IDENTIFIER ON;
GO

-- =============================================
-- Author:  Sidney
-- ALTER  date: 2010-10-16
-- Description: 备抵-冲抵
-- =============================================
ALTER PROCEDURE [dbo].[ProceBDCD]
  @al_orno INT, @as_ym VARCHAR(7), @as_ctym VARCHAR(7), @as_user VARCHAR(20), @ai_cttimes INT
AS
  BEGIN
    DECLARE @ldc_wcd NUMERIC(18, 4) --未冲抵金额
    DECLARE @ldc_cd NUMERIC(18, 4)
    DECLARE @ldc_prise_all NUMERIC(18, 4)
    DECLARE @ldc_prise_ym NUMERIC(18, 4)
    DECLARE @ldc_bd NUMERIC(18, 4) --是否设定保底多少才冲抵
    DECLARE @as_info VARCHAR(500)
    DECLARE @ls_emno VARCHAR(20)
    DECLARE @li_assign INT
    DECLARE @ldc_pre NUMERIC(18, 4)
    DECLARE @ls_emno2 VARCHAR(20)
    DECLARE @ai_sum INT
    SET @as_info       = ''
    set @ldc_bd        = 0 ---默认总提奖高于0就冲抵

    IF isnull(@as_ctym, '') = ''
      SET @as_ctym = @as_ym

    --获取当月所有提奖
    SELECT @ldc_prise_ym  = sum(t_amnt)
    FROM tbl_priseresult
    WHERE t_orno = @al_orno and t_yymm = @as_ctym

    if @ldc_prise_ym is null
      set @ldc_prise_ym = 0

    --获取所有提奖
    SELECT @ldc_prise_all = sum(t_amnt)
    FROM tbl_priseresult
    WHERE t_orno = @al_orno

    if @ldc_prise_all is null
      set @ldc_prise_all = 0

    --获取未备抵金额,当月备抵部分(提前兑付产生)不算
    select @ldc_wcd       = sum(t_amnt)
    FROM tbl_bdcd
    WHERE t_orno = @al_orno AND t_cttimes = @ai_cttimes and t_jzy <> @as_ctym

    if @ldc_wcd is null
      set @ldc_wcd = 0

    SELECT @li_assign     = count(*)
    FROM tbl_assign
    WHERE tbl_assign.fld_orno = @al_orno AND (@as_ym BETWEEN CONVERT(CHAR(7), isnull(fld_udat, '2010-01-01'), 121) AND CONVERT(CHAR(7), isnull(fld_ddat, '2055-01-01'), 121)
          )

    IF @li_assign IS NULL
      SET @li_assign = 0

    DECLARE
      tbl_assign_Cursor CURSOR FOR
        SELECT rtrim(ltrim(fld_emno)), isnull(fld_pre, 0)
        FROM   tbl_assign
        WHERE  tbl_assign.fld_orno = @al_orno AND (@as_ym BETWEEN CONVERT(CHAR(7), isnull(fld_udat, '2010-01-01'), 121) AND CONVERT(CHAR(7), isnull(fld_ddat, '2055-01-01'), 121)
               )

    if @ldc_prise_ym < 0
      BEGIN
        set @as_info = @as_info + '本月总提奖为负(' + str(@ldc_prise_ym, 8, 2) + '),小于零部分备抵!'

        IF @li_assign > 0
          BEGIN
            OPEN tbl_assign_Cursor

            ---分公司部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, -@ldc_prise_ym * 0.15, @as_info + '分公司部分(15%)',
                         '', @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.15, @ai_cttimes, getdate(),
                         '分公司备抵部分(15%)')

            FETCH NEXT FROM tbl_assign_Cursor
            INTO @ls_emno2, @ldc_pre

            WHILE @@FETCH_STATUS = 0
            BEGIN
              INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                            t_cttimes     )
              VALUES      (getdate(), 8, @al_orno, @as_ctym, @ls_emno2, 9, -@ldc_prise_ym * 0.85 * @ldc_pre,
                           @as_info + ',个人部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')', '', @as_user, @ai_cttimes)

              INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
              VALUES      (@al_orno, @ls_emno2, @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.85 * @ldc_pre, @ai_cttimes, getdate(),
                           '个人备抵部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')')

              FETCH NEXT FROM tbl_assign_Cursor
              INTO @ls_emno2, @ldc_pre
            END

            CLOSE tbl_assign_Cursor
            DEALLOCATE tbl_assign_Cursor
          END
        ELSE
          BEGIN
            -- insert into tbl_priseresult
            ---分公司部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, -@ldc_prise_ym * 0.15,
                         @as_info + ',分公司备抵部分(15%)', '', @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.15, @ai_cttimes, getdate(),
                         '分公司备抵部分(15%)')

            ---个人部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, @ls_emno, 9, -@ldc_prise_ym * 0.85,
                         @as_info + ',个人备抵部分(85%)', '', @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, @ls_emno, @as_ctym, @as_ym, 9, 0, @ldc_prise_ym * 0.85, @ai_cttimes, getdate(),
                         '个人备抵部分(85%)')
          END

        SELECT @as_info AS info, @ldc_prise_ym AS rtn, 9 priz
      END
    else --本月提奖大于零,检查是否需要冲抵
      set @ldc_cd = 0

    if @ldc_prise_ym > 0 and @ldc_wcd < 0 and @ldc_prise_all > @ldc_bd --有可能会要设定在总提奖大于保底佣金是才冲抵
      begin --本月提奖大于零且有提奖未冲抵
        if -@ldc_wcd > @ldc_prise_ym --待冲抵金额大于本月提奖,最多冲抵本月提奖
          begin
            if -@ldc_prise_ym + @ldc_prise_all > @ldc_bd --如果全部冲抵后大于保底,则全部冲抵,否则只冲抵大于保底部分
              begin
                set @ldc_cd = -@ldc_prise_ym
              end
            else
              begin
                set @ldc_cd = -(@ldc_prise_all - @ldc_bd)
              end
          end
        else ---待冲抵金额小于本月提奖,最多冲抵待冲抵金额
          begin
            if @ldc_wcd + @ldc_prise_all > @ldc_bd --如果全部冲抵后大于保底,则全部冲抵,否则只冲抵大于保底部分
              begin
                set @ldc_cd = @ldc_wcd
              end
            else
              begin
                set @ldc_cd = -(@ldc_prise_all - @ldc_bd)
              end
          end

        if @li_assign > 0 --有提奖分配
          begin
            OPEN tbl_assign_Cursor

            ---分公司部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, @ldc_cd * 0.15, @as_info + '分公司冲抵部分(15%)',
                         '', @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.15, @ai_cttimes, getdate(),
                         '分公司冲抵部分(15%)')

            FETCH NEXT FROM tbl_assign_Cursor
            INTO @ls_emno2, @ldc_pre

            WHILE @@FETCH_STATUS = 0
            BEGIN
              INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                            t_cttimes     )
              VALUES      (getdate(),
                           8,
                           @al_orno,
                           @as_ctym,
                           @ls_emno2,
                           9,
                           @ldc_cd * 0.85 * @ldc_pre,
                           @as_info + ',个人冲抵部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')',
                           '',
                           @as_user,
                           @ai_cttimes)

              INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
              VALUES      (@al_orno, @ls_emno2, @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.85 * @ldc_pre, @ai_cttimes, getdate(),
                           '个人冲抵部分(85%),分配比例(' + str(@ldc_pre, 4, 2) + ')')

              FETCH NEXT FROM tbl_assign_Cursor
              INTO @ls_emno2, @ldc_pre
            END

            CLOSE tbl_assign_Cursor
            DEALLOCATE tbl_assign_Cursor
          end
        else --无提奖分配
          begin
            ---分公司部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, '1000000', 9, @ldc_cd * 0.15, @as_info + ',分公司冲抵部分(15%)',
                         '', @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, '1000000', @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.15, @ai_cttimes, getdate(),
                         '分公司冲抵部分(15%)')

            ---个人部分
            INSERT INTO tbl_priseresult(t_cdat, t_flag1, t_orno, t_yymm, t_crep, t_priz, t_amnt, t_gs, t_gs2, t_user,
                          t_cttimes     )
            VALUES      (getdate(), 8, @al_orno, @as_ctym, @ls_emno, 9, @ldc_cd * 0.85, @as_info + ',个人冲抵部分(85%)',
                         '', @as_user, @ai_cttimes)

            INSERT INTO tbl_bdcd(t_orno, t_emno, t_jzy, t_tjy, t_priz, t_flag, t_amnt, t_cttimes, t_time, t_mark)
            VALUES      (@al_orno, @ls_emno, @as_ctym, @as_ym, 9, 0, -@ldc_cd * 0.85, @ai_cttimes, getdate(),
                         '个人冲抵部分(85%)')
          end
      end
  END
GO

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/75527/viewspace-721997/,如需转载,请注明出处,否则将追究法律责任。

上一篇: bdcd
下一篇: ProceYT
请登录后发表评论 登录
全部评论

注册时间:2012-01-31

  • 博文量
    39
  • 访问量
    18848