This proc will clean up customer Loyalty summary records on the customer (AR_CUST) table from history.

This proc only considers historical transactions and adjustments.  This should be run after all posting events have occurred.

--EXEC USR_AR_CUST_LOY_RESET 'BCOS', NULL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    Red Rook
-- Create date: 10/03/2019
-- Description:  Cleans Up Customer Header Values related to Loyalty
-- =============================================
ALTER PROCEDURE USR_AR_CUST_LOY_RESET 
  @LOY_PGM_COD T_COD = NULL,
  @CUST_NO T_CUST_NO = NULL
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- RESET CUSTOMER HEADER INFO
  -- START BY SETTING ALL VALUES TO THEIR DEFAULTS

    UPDATE AR_CUST 
  SET ORD_BAL = 0, 
    NO_OF_ORDS = 0,
    FST_SAL_DAT = NULL, 
    LST_SAL_DAT = NULL,
    TOT_LOY_PTS_ADJ = 0,
    LST_LOY_ADJ_DAT = NULL, 
    LST_LOY_ADJ_DOC_NO = NULL, 
    LST_LOY_PTS_ADJ = 0,
    TOT_LOY_PTS_EARND = 0,
    LST_LOY_EARN_TKT_DAT = NULL,
    LST_LOY_EARN_TKT_TIM = NULL,
    LST_LOY_EARN_TKT_NO = NULL, 
    LST_LOY_PTS_EARN = 0,
    TOT_LOY_PTS_RDM = 0, 
    LST_LOY_RDM_TKT_DAT = NULL, 
    LST_LOY_RDM_TKT_TIM = NULL, 
    LST_LOY_RDM_TKT_NO = NULL, 
    LST_LOY_PTS_RDM = 0, 
    LOY_PTS_BAL = 0
  WHERE 1=1
    AND CUST_NO = COALESCE(@CUST_NO, CUST_NO)
    AND LOY_PGM_COD = @LOY_PGM_COD

  -- RECALC CUSTOMER ORDER BALANCE AND NUMBER OF ORDERS
  UPDATE AR_CUST 
  SET ORD_BAL = TOT_ORD_BAL,
    NO_OF_ORDS = TOT_ORDS
  FROM (  SELECT CUST_NO, SUM(ORD_TOT) AS TOT_ORD_BAL, COUNT(*) AS TOT_ORDS 
      FROM VI_PS_DOC_HDR O 
      WHERE DOC_TYP = 'O' 
      GROUP BY CUST_NO) ORDERS
  WHERE 1=1
    AND ORDERS.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- RECALC CUSTOMER FIRST AND LAST SALE DATE
  UPDATE AR_CUST 
  SET FST_SAL_DAT = _FST_SAL_DAT,
    LST_SAL_DAT = _LST_SAL_DAT
  FROM (  SELECT CUST_NO, MIN(TKT_DAT) AS _FST_SAL_DAT, MAX(TKT_DAT) AS _LST_SAL_DAT 
      FROM (SELECT CUST_NO, TKT_DAT FROM VI_PS_DOC_HDR WHERE DOC_TYP = 'T' 
          UNION ALL
          SELECT CUST_NO, TKT_DAT FROM VI_PS_TKT_HIST
          ) ALL_TKTS
      GROUP BY CUST_NO) TKT_INFO
  WHERE 1=1
    AND TKT_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- RECALC CUSTOMER LOYALTY SUMMARIES
  -- ================= EARNINGS ==================== --
  -- Total Ticket Loyalty Earned

  UPDATE AR_CUST 
  SET 
    TOT_LOY_PTS_EARND = _TOT_LOY_PTS_EARND
  FROM (
    SELECT CUST_NO, 
      SUM(LOY_PTS_EARND_NET) AS _TOT_LOY_PTS_EARND
    FROM (
      SELECT CUST_NO, 
        H.TKT_DT AS EARNED_DT, 
        COALESCE(L.LOY_PTS_EARND_NET, 0) AS LOY_PTS_EARND_NET
      FROM PS_TKT_HIST H 
          JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      WHERE 1=1
        AND COALESCE(L.LOY_PTS_EARND_NET, 0) <> 0
    ) TKT_LOY_DET
    GROUP BY CUST_NO
  ) TKT_INFO
  WHERE 1=1
    AND TKT_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- Last Ticket Loyalty Earning Dates
  UPDATE AR_CUST 
  SET 
    LST_LOY_EARN_TKT_DAT = _LST_LOY_EARN_TKT_DAT,
    LST_LOY_EARN_TKT_TIM = _LST_LOY_EARN_TKT_TIM
  FROM (
    SELECT CUST_NO, 
      dbo.fnDateOnly(MAX(EARNED_DT)) AS _LST_LOY_EARN_TKT_DAT, 
      dbo.fnTimeOnly(MAX(EARNED_DT)) AS _LST_LOY_EARN_TKT_TIM
    FROM (
      SELECT CUST_NO, 
        H.TKT_DT AS EARNED_DT, 
        COALESCE(L.LOY_PTS_EARND_NET, 0) AS LOY_PTS_EARND_NET
      FROM PS_TKT_HIST H 
          JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      WHERE 1=1
        AND COALESCE(L.LOY_PTS_EARND_NET, 0) <> 0
    ) TKT_LOY_DET
    GROUP BY CUST_NO
  ) TKT_INFO
  WHERE 1=1
    AND TKT_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- Last Loyalty Earning Ticket 
  -- Dependent upon: Last Ticket Loyalty Earning Dates (From previous Query)
  UPDATE AR_CUST 
  SET 
    LST_LOY_EARN_TKT_NO = _LST_LOY_EARN_TKT_NO
  FROM (
    SELECT 
      H.CUST_NO,
      TKT_DT,
      MAX(H.TKT_NO) AS _LST_LOY_EARN_TKT_NO
    FROM PS_TKT_HIST H 
      JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      JOIN AR_CUST INNER_C ON INNER_C.CUST_NO = H.CUST_NO AND H.TKT_DT = INNER_C.LST_LOY_EARN_TKT_DT 
    WHERE 1=1
      AND COALESCE(L.LOY_PTS_EARND_NET, 0) <> 0
    GROUP BY H.CUST_NO, TKT_DT
  ) LOY_EARN_DET
  WHERE 1=1
    AND LOY_EARN_DET.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD
    
  -- Last Loyalty Ticket Earnings
  -- Dependent upon: Last Loyalty Earning Ticket AND Last Ticket Loyalty Earning Dates (From previous Queries)
  UPDATE AR_CUST 
  SET 
    LST_LOY_PTS_EARN = _LST_LOY_PTS_EARN
  FROM (
    SELECT 
      H.CUST_NO,
      TKT_DT,
      COALESCE(L.LOY_PTS_EARND_NET, 0) AS _LST_LOY_PTS_EARN,  
      H.TKT_NO AS _LST_LOY_EARN_TKT_NO
    FROM PS_TKT_HIST H 
      JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      JOIN AR_CUST INNER_C ON INNER_C.CUST_NO = H.CUST_NO AND H.TKT_NO = INNER_C.LST_LOY_EARN_TKT_NO AND H.TKT_DT = INNER_C.LST_LOY_EARN_TKT_DT 
    WHERE 1=1
      AND COALESCE(L.LOY_PTS_EARND_NET, 0) <> 0
    ) LOY_EARN_DET
  WHERE 1=1
    AND LOY_EARN_DET.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- ================= REDEMPTIONS ==================== --
  -- Total Ticket Loyalty Redeemed
  UPDATE AR_CUST 
  SET 
    TOT_LOY_PTS_RDM = _TOT_LOY_PTS_RDM
  FROM (
    SELECT CUST_NO, 
      SUM(LOY_PTS_RDM) AS _TOT_LOY_PTS_RDM
    FROM (
      SELECT CUST_NO, 
        H.TKT_DT AS REDEEM_DT, 
        COALESCE(L.LOY_PTS_RDM, 0) AS LOY_PTS_RDM
      FROM PS_TKT_HIST H 
          JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      WHERE 1=1
        AND COALESCE(L.LOY_PTS_RDM, 0) <> 0
    ) TKT_LOY_DET
    GROUP BY CUST_NO
  ) TKT_INFO
  WHERE 1=1
    AND TKT_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD
    
  -- Last Ticket Loyalty Redeemed Dates 
  UPDATE AR_CUST 
  SET 
    LST_LOY_RDM_TKT_DAT = _LST_LOY_RDM_TKT_DAT,
    LST_LOY_RDM_TKT_TIM = _LST_LOY_RDM_TKT_TIM
  FROM (
    SELECT CUST_NO, 
      dbo.fnDateOnly(MAX(REDEEM_DT)) AS _LST_LOY_RDM_TKT_DAT, 
      dbo.fnTimeOnly(MAX(REDEEM_DT)) AS _LST_LOY_RDM_TKT_TIM
    FROM (
      SELECT CUST_NO, 
        H.TKT_DT AS REDEEM_DT, 
        COALESCE(L.LOY_PTS_RDM, 0) AS LOY_PTS_RDM
      FROM PS_TKT_HIST H 
          JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      WHERE 1=1
        AND COALESCE(L.LOY_PTS_RDM, 0) <> 0
    ) TKT_LOY_DET
    GROUP BY CUST_NO
  ) TKT_INFO
  WHERE 1=1
    AND TKT_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD
    
  -- Last Loyalty Redeemed Ticket 
  -- Dependent upon: Last Ticket Loyalty Redeemed Dates (From previous Query)
  UPDATE AR_CUST 
  SET 
    LST_LOY_RDM_TKT_NO = _LST_LOY_RDM_TKT_NO
  FROM (
    SELECT 
      H.CUST_NO,
      TKT_DT,
      MAX(H.TKT_NO) AS _LST_LOY_RDM_TKT_NO
    FROM PS_TKT_HIST H 
      JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      JOIN AR_CUST INNER_C ON INNER_C.CUST_NO = H.CUST_NO AND H.TKT_DT = INNER_C.LST_LOY_EARN_TKT_DT 
    WHERE 1=1
      AND COALESCE(L.LOY_PTS_RDM, 0) <> 0
    GROUP BY H.CUST_NO, TKT_DT
  ) LOY_EARN_DET
  WHERE 1=1
    AND LOY_EARN_DET.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD
    
  -- Last Loyalty Ticket Points Redeemed
  -- Dependent upon: Last Loyalty Redeemed Ticket AND Last Ticket Loyalty Redeemed Dates (From previous Queries)
  UPDATE AR_CUST 
  SET 
    LST_LOY_PTS_RDM = _LST_LOY_PTS_RDM
  FROM (
    SELECT 
      H.CUST_NO,
      COALESCE(L.LOY_PTS_RDM, 0) AS _LST_LOY_PTS_RDM
    FROM PS_TKT_HIST H 
      JOIN PS_TKT_HIST_LOY_PGM L ON H.DOC_ID = L.DOC_ID AND H.BUS_DAT = L.BUS_DAT
      JOIN AR_CUST INNER_C ON INNER_C.CUST_NO = H.CUST_NO AND H.TKT_NO = INNER_C.LST_LOY_EARN_TKT_NO AND H.TKT_DT = INNER_C.LST_LOY_EARN_TKT_DT 
    WHERE 1=1
      AND COALESCE(L.LOY_PTS_RDM, 0) <> 0
    ) LOY_RDM_DET
  WHERE 1=1
    AND LOY_RDM_DET.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD
  
  -- ================= ADJUSTMENTS ==================== --
  -- Last Loyalty Adjustment
  UPDATE AR_CUST 
  SET 
    TOT_LOY_PTS_ADJ = _TOT_LOY_PTS_ADJ,
    LST_LOY_ADJ_DAT = _LST_LOY_ADJ_DAT
  FROM (
    SELECT CUST_NO, 
      SUM(NO_OF_PTS) AS _TOT_LOY_PTS_ADJ, 
      MAX(TRX_DAT) AS _LST_LOY_ADJ_DAT
    FROM (
      SELECT CUST_NO, NO_OF_PTS, TRX_DAT 
      FROM AR_LOY_PT_ADJ_HIST
    ) ADJ_LOY_DET
    GROUP BY CUST_NO
  ) ADJ_INFO
  WHERE 1=1
    AND ADJ_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- Last Loyalty Adjustment DocNo
  -- Dependent upon: Last Loyalty Adjustment (From previous Query)
  UPDATE AR_CUST 
  SET 
    LST_LOY_ADJ_DOC_NO = _LST_LOY_ADJ_DOC_NO
  FROM (
      SELECT H.CUST_NO, MAX(H.DOC_NO) AS _LST_LOY_ADJ_DOC_NO
      FROM AR_LOY_PT_ADJ_HIST H
      JOIN AR_CUST INNER_C ON H.CUST_NO = INNER_C.CUST_NO AND H.TRX_DAT = INNER_C.LST_LOY_ADJ_DAT
      GROUP BY H.CUST_NO
    ) ADJ_INFO
  WHERE 1=1
    AND ADJ_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- Last Loyalty Adjustment Points
  -- Dependent upon: Last Loyalty Adjustment DocNo AND Last Loyalty Adjustment (From previous Queries)
  UPDATE AR_CUST 
  SET 
    LST_LOY_PTS_ADJ = _LST_LOY_PTS_ADJ
  FROM (
      SELECT H.CUST_NO, MAX(H.NO_OF_PTS) AS _LST_LOY_PTS_ADJ
      FROM AR_LOY_PT_ADJ_HIST H
      JOIN AR_CUST INNER_C ON H.CUST_NO = INNER_C.CUST_NO AND H.DOC_NO = INNER_C.LST_LOY_ADJ_DOC_NO AND H.TRX_DAT = INNER_C.LST_LOY_ADJ_DAT
      GROUP BY H.CUST_NO
    ) ADJ_INFO
  WHERE 1=1
    AND ADJ_INFO.CUST_NO = AR_CUST.CUST_NO
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD
    
  -- ================= TOTALS ==================== --
  -- Customer Loyalty Balance
  UPDATE AR_CUST 
  SET LOY_PTS_BAL = TOT_LOY_PTS_ADJ + TOT_LOY_PTS_EARND - TOT_LOY_PTS_RDM
  WHERE 1=1
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

  -- RETURN ANY MISCALCULATIONS
  SELECT CUST_NO, NAM, LOY_PTS_BAL, TOT_LOY_PTS_ADJ, TOT_LOY_PTS_EARND, TOT_LOY_PTS_RDM, * 
  FROM AR_CUST 
  WHERE 1=1
    AND LOY_PTS_BAL <> (TOT_LOY_PTS_ADJ+TOT_LOY_PTS_EARND-TOT_LOY_PTS_RDM)
    AND AR_CUST.CUST_NO = COALESCE(@CUST_NO, AR_CUST.CUST_NO)
    AND AR_CUST.LOY_PGM_COD = @LOY_PGM_COD

END
GO