[GH]重算補休時數(針對時數不合的人)

DECLARE @IS_SHOW_ONLY YN; SELECT @IS_SHOW_ONLY = 'N'
DECLARE @重算所有人 YN; SELECT @重算所有人 = 'N'

DECLARE @OT TABLE
(
  PER_SERIL_NO PER_SERIL_NO
, OT_COMPEN_HR FLOAT
, OT_COMPEN_RHR FLOAT
, KP_ORG_OT_COMPEN FLOAT
)
DECLARE @LEV TABLE
(
  PER_SERIL_NO PER_SERIL_NO
, LEV_HR FLOAT
, LEV_BORROW_HR FLOAT
)
INSERT  INTO @OT
        SELECT  PER_SERIL_NO
              , [OT_COMPEN_HR] = SUM(OT_COMPEN_HR)
              , [OT_COMPEN_RHR] = SUM(OT_COMPEN_RHR)
              , [KP_ORG_OT_COMPEN] = SUM(KP_ORG_OT_COMPEN)
        FROM    LOL_OT
        WHERE   EFORM_STATUS_COD IN ( 'W', 'P' )
        GROUP BY PER_SERIL_NO

INSERT  INTO @LEV
        SELECT  PER_SERIL_NO
              , [LEV_HR] = SUM(LEV_HR)
              , [LEV_BORROW_HR] = SUM(LEV_BORROW_HR)
        FROM    LOL_LEV
        WHERE   EFORM_STATUS_COD IN ( 'W', 'P' )
                AND LEV_TYPE_COD = '00014'
        GROUP BY PER_SERIL_NO

DECLARE @NO_MATCH TABLE (
    PER_SERIL_NO PER_SERIL_NO
)
INSERT INTO @NO_MATCH
SELECT  A.PER_SERIL_NO
FROM    @OT A
JOIN    @LEV B ON A.PER_SERIL_NO = B.PER_SERIL_NO
WHERE   1 = 1
        AND ( A.OT_COMPEN_HR + B.LEV_BORROW_HR ) != ( A.OT_COMPEN_RHR + B.LEV_HR )

IF @IS_SHOW_ONLY ='Y' BEGIN

    SELECT  A.PER_SERIL_NO
          , A.OT_COMPEN_HR + B.LEV_BORROW_HR
          , A.OT_COMPEN_RHR + B.LEV_HR
          , A.OT_COMPEN_HR
          , A.OT_COMPEN_RHR
          , A.KP_ORG_OT_COMPEN
          , B.LEV_HR
          , B.LEV_BORROW_HR
    FROM    @OT A
    JOIN    @LEV B ON A.PER_SERIL_NO = B.PER_SERIL_NO
    JOIN    @NO_MATCH C ON A.PER_SERIL_NO = C.PER_SERIL_NO

END ELSE BEGIN
    SET NOCOUNT ON
    DELETE A
      FROM dbo.LOL_LEV_OT A
     WHERE EXISTS (SELECT 'X'
                     FROM LOL_LEV X
                    WHERE A.LE_APPLY_NO=X.LE_APPLY_NO
                      AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE X.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')
        )

    UPDATE A
       SET OT_COMPEN_RHR = OT_COMPEN_HR
         , MOD_REASON = REPLACE(MOD_REASON,N'加班補休假撤回',N'')
      FROM dbo.LOL_OT A
     WHERE EFORM_STATUS_COD IN ('P','W')
       AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE A.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')

    UPDATE A
       SET LEV_BORROW_HR = 0
      FROM LOL_LEV A
     WHERE EFORM_STATUS_COD IN ('P','W')
       AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE A.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')

    DECLARE @strOT_PER_SERIL_NO PER_SERIL_NO,
            @strOT_LE_APPLY_NO CHAR(20)

    DECLARE lev_cursor CURSOR FOR
    SELECT PER_SERIL_NO, LE_APPLY_NO
      FROM LOL_LEV A
     WHERE LEV_TYPE_COD = '00014'
       AND EFORM_STATUS_COD IN ('W','P')
       AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE A.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')
     ORDER BY PER_SERIL_NO , SDATE

    OPEN lev_cursor
    FETCH NEXT FROM lev_cursor INTO @strOT_PER_SERIL_NO, @strOT_LE_APPLY_NO
    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC spApplyLeaveOT '', @strOT_PER_SERIL_NO, @strOT_LE_APPLY_NO, NULL, NULL, 0, 'RECALC-20110222', 0
            FETCH NEXT FROM lev_cursor INTO @strOT_PER_SERIL_NO, @strOT_LE_APPLY_NO
        END
    CLOSE lev_cursor
    DEALLOCATE lev_cursor
    SET NOCOUNT OFF
END