ALTER PROCEDURE [dbo].[sproc_tblWkHrsVsBudgeted]
AS
-------------------------------------------------------------------------------
-- Procedure : sproc_tblWkHrsVsBudgeted
-- SubSystem : Labor Model
-- Description: Refer to HRISLaborModel.doc
--
-- * creates a table (tblWkHrsVsBudgeted) of exactly 15 Weekly Hours vs Budgeted values
-- by week start for each plant. tblWkHrsVsBudgeted construction is divided into steps:
-- 1) An empty table is created with 15 weeks per Plant.
-- 2) PT/FT mixes from Retail Hours Rollup.xls are added. The PT/FT values are given
-- in PPD time base and each incoming value writes to two adjacent weekstart dates.
-- 3) Actual PPD Labor Hours from the Activity Archive dbo.vStoreDlyActivityYTD are added
-- by weekstart for each Plant.
-- 4) Budgeted Labor Hours from LOCKED down Labor Hours forecasts are added.
-- 5) Now, Actual vs Budgeted Variance calculations are possible and they are added.
-- 6) The Plant, Market, Division, Channel STARS are added.
-- 7) Call Exec sproc_PivotWkHrsAct to convey the new values to the Crystal Reports view.
--
-- History:
-- ---------
--
-- When Who What
-- ---- --- ----
--
-- 15AUG07 J. Martin v1.7 * Join Actuals from PPD archive using weekstarts and not weekends.
-- 03AUG07 J. Martin v1.6 * Use new vStoreDlyLaborForeYTD format.
-- 05JUL07 J. Martin v1.5 * Call fnFifthWeek code inline.
-- 03JUL07 J. Martin v1.4 * Get Actual Labor Hours from the PPD Activity Archive
-- 26JUN07 J. Martin v1.3 * Map NULLs to 0 for Cyrstal Reports DIV/MKT sums.
-- 12JUN07 J. Martin v1.2 * Added CHSTAR (Channel STAR) columns to tblWkHrsVsBudgeted.
-- 05JUN07 J. Martin v1.1 * Added MKTSTAR and DIVSTAR columns to tblWkHrsVsBudgeted.
-- 26MAY07 P. Sutera v1.0
-- J. Martin
-------------------------------------------------------------------------------
BEGIN
SET NOCOUNT ON
SET DATEFIRST 7

-- STAR date ranges
DECLARE @Startdate datetime
DECLARE @Enddate datetime

-- sp_executesql
DECLARE @Command nvarchar(4000)

-- sp_showmessage
DECLARE @RowCnt bigint
DECLARE @ProcID int
DECLARE @Msg nvarchar(400)

-------------------------------------------------------------------------------------
SET NOCOUNT ON
SET @ProcID = @@PROCID
-------------------------------------------------------------------------------------
-- forecasted activity by week for each store.
-------------------------------------------------------------------------------------
SET @Msg = 'Creating tblWkHrsVsBudgeted...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

-----------------------------------------------------------------------------
-- create a new tblWkHrsVsBudgeted
if object_id('tblWkHrsVsBudgeted') is null BEGIN
CREATE TABLE dbo.tblWkHrsVsBudgeted (
Plant nvarchar(10) NULL
,dtWeek datetime NULL
,intWeek int NULL
,intFore int NULL
,intAct int NULL
,intVar int NULL
,intVarPercent int NULL
,intFT int NULL
,intP int NULL
,intFTPercent int NULL
,intPTPercent int NULL
,STAR decimal(6,1) NULL
,MKTSTAR decimal(6,1) NULL
,DIVSTAR decimal(6,1) NULL
,CHSTAR decimal(6,1) NULL)
END
TRUNCATE TABLE dbo.tblWkHrsVsBudgeted
-----------------------------------------------------------------------------
-- load weekly plants (10 weeks) with no data
SET @Msg = 'Loading fifth week data...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

-- loop counters
DECLARE @WEEKSTART DATETIME
DECLARE @dtStart DATETIME
DECLARE @WeekEnd DATETIME
DECLARE @SaveMonth INTEGER
DECLARE @IntWeeks INTEGER
DECLARE @j INTEGER
DECLARE @k INTEGER
DECLARE @m INTEGER
DECLARE @Weeks TABLE (WeekStart datetime,
intWeek integer)
------------------------------------------------------------------------------
-- init counters
SET @dtStart = DATEADD(m,-1,GetDate())
SET @intWeeks = 10

-- First day of the month (minus time component)
SET @WeekEnd = convert(datetime,convert(int,convert(float,
(DATEADD(dd, -DATEPART(dd, @dtStart ), @dtStart ) + 1)
)))
-- Last day of the week (sets the month a week falls in)
SET @SaveMonth = Month(DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 7),@WeekEnd))

-- First day of the first week
SET @WeekStart = DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 1),@WeekEnd)

-- find the # of weeks in @intWeeks started at the current month/year.
SET @j = 1 -- count weeks in @intWeeks.
SET @k = 0 -- count weeks in 1 month.
SET @m = 1 -- let client sorts return the expected pivot order (use ORDER BY Plant, intWeek).

-----------------------------------------------------------------------------
-- If a month doesn’t contain 5 weeks naturally, add a NULL week in its place.
WHILE (@j <= @intWeeks ) BEGIN
-- month changed
If @SaveMonth <> Month(DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 7),@WeekEnd)) Begin

-- write NULL
IF @k < 5 AND @m < (@intWeeks + 1) BEGIN
INSERT @Weeks SELECT NULL, @m
SET @m = @m + 1
END

-- save the month and reset the week counter
SET @SaveMonth = Month(DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 7),@WeekEnd))
SET @k = 0
End

-- write a naturally occurring fith week.
IF @m < (@intWeeks + 1) BEGIN
INSERT @Weeks SELECT @WeekStart, @m
END

-- advance counters
SET @m = @m + 1
SET @j = @j + 1
SET @k = @k + 1
SET @WeekEnd = DATEADD(dd,7,@WeekEnd)
SET @WeekStart = DATEADD(dd,-(DATEPART(dw, @WeekEnd) - 1),@WeekEnd)
END

INSERT tblWkHrsVsBudgeted
SELECT PLANTS.Plant
,WEEKS.WeekStart
,WEEKS.intWeek
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM vLaborStoreDetail PLANTS,
@Weeks WEEKS

-----------------------------------------------------------------------------
-- Add Labor Hours Acutals from the Actitity Archive (PPD)
-- Add FT/PT mixes from Retail Hours Rollup.xls (PPE)
--SET DATEFIRST 1
SET @Msg = 'Loading Actuals and Headcounts...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

-- second PPE week
UPDATE VARIANCE
SET
Plant = ACTUALS.Plant
,intFore = 0
,intAct = ISNULL(ACTARC.LaborHours,0)
,intVar = 0
,intVarPercent = 0
,intFT = ISNULL(ACTUALS.Total_FT,0)
,intPT = ISNULL(ACTUALS.Total_PT,0)
,intFTPercent =

CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN 0
ELSE (ISNULL(ACTUALS.Total_FT,0) + .00) / ((ISNULL(ACTUALS.Total_FT,0) +
ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
,intPTPercent = CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN 0
ELSE (ISNULL(ACTUALS.Total_PT,0) + .00) /
((ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
FROM tblLookupHrsRollup ACTUALS
JOIN tblWkHrsVsBudgeted VARIANCE
ON VARIANCE.Plant = ACTUALS.Plant
AND VARIANCE.dtWeek = DATEADD(dd, -6, ACTUALS.PPE)

-- get PPD Actuals from the Activity Archive
JOIN (SELECT Plant
,Activitydate =
(DATEADD(dd,-(DATEPART(dw, Activitydate) - 1),Activitydate))
,LaborHours =
SUM(ISNULL(LaborHours,0))
FROM vStoreDlyActivityYTD
GROUP BY Plant, (DATEADD(dd,-(DATEPART(dw, Activitydate) - 1),Activitydate))
HAVING SUM(ISNULL(LaborHours,0)) > 0) ACTARC

ON VARIANCE.Plant = ACTARC.Plant
AND VARIANCE.dtWeek = ACTARC.ActivityDate

--first PPE week
UPDATE VARIANCE
SET
Plant = ACTUALS.Plant
,intFore = 0
,intAct = ISNULL(ACTARC.LaborHours,0)
,intVar = 0
,intVarPercent = 0
,intFT = ISNULL(ACTUALS.Total_FT,0)
,intPT = ISNULL(ACTUALS.Total_PT,0)
,intFTPercent = CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN 0
ELSE (ISNULL(ACTUALS.Total_FT,0) + .00) / ((ISNULL(ACTUALS.Total_FT,0) +
ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
,intPTPercent = CASE (ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0))
WHEN 0 THEN 0
ELSE (ISNULL(ACTUALS.Total_PT,0) + .00) /
((ISNULL(ACTUALS.Total_FT,0) + ISNULL(ACTUALS.Total_PT,0)) + .00) * 100
END
FROM tblLookupHrsRollup ACTUALS
JOIN tblWkHrsVsBudgeted VARIANCE
ON VARIANCE.Plant = ACTUALS.Plant
AND VARIANCE.dtWeek = DATEADD(dd, -13, ACTUALS.PPE)

-- get PPD Actuals from the Activity Archive
JOIN (SELECT Plant
,Activitydate =
(DATEADD(dd,-(DATEPART(dw, Activitydate) - 1),Activitydate))
,LaborHours =
SUM(ISNULL(LaborHours,0))
FROM vStoreDlyActivityYTD
GROUP BY Plant, (DATEADD(dd,-(DATEPART(dw, Activitydate) - 1),Activitydate))
HAVING SUM(ISNULL(LaborHours,0)) > 0) ACTARC

ON VARIANCE.Plant = ACTARC.Plant
AND VARIANCE.dtWeek = ACTARC.ActivityDate


-----------------------------------------------------------------------------
-- load Budgeted Hours and Variance Percent from the forecasted hours archive
SET @Msg = 'Loading Budgeted Hours...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

UPDATE VARIANCE
SET intFore =
ISNULL(FORE.Budgeted_Hours,0)
,intVar =
ISNULL(FORE.Budgeted_Hours,0) - ISNULL(VARIANCE.intAct,0)
,intVarPercent = CASE ISNULL(FORE.Budgeted_Hours,0)
WHEN 0 THEN 0
ELSE ((ISNULL(FORE.Budgeted_Hours,0) - ISNULL(VARIANCE.intAct,0)) +.00) /
(ISNULL(FORE.Budgeted_Hours,0) + .00) * 100
END
FROM vStoreDlyLaborForeYTD FORE
JOIN tblWkHrsVsBudgeted VARIANCE
ON FORE.Plant = VARIANCE.Plant
AND FORE.dtWeekStart = VARIANCE.dtWeek
AND VARIANCE.intAct IS NOT NULL
AND VARIANCE.intAct <> 0

-----------------------------------------------------------------------------
-- load Plant STAR
SET DATEFIRST 1
SET @Msg = 'Loading Plant STARS...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

UPDATE VARIANCE
SET STAR = STAR.STAR
FROM (SELECT ACTARC.Plant
,Activitydate =
DATEADD(dd,- (DATEPART(dw, ACTARC.Activitydate)7),ACTARC.Activitydate)
,InTraffic =
SUM(ISNULL(ACTARC.InTraffic,0))
,LaborHours =
SUM(ISNULL(ACTARC.LaborHours,0))
,STAR =
CASE SUM(ISNULL(ACTARC.LaborHours,0))
WHEN 0 THEN 0
ELSE CAST(SUM(ISNULL(ACTARC.InTraffic,0) + .00) / SUM(ISNULL(ACTARC.LaborHours,0) + .00)
AS DECIMAL(6,1))
END
FROM vStoreDlyActivityYTD ACTARC
GROUP BY ACTARC.Plant,
DATEADD(dd,-(DATEPART(dw, ACTARC.Activitydate) - 7),ACTARC.Activitydate)) STAR
JOIN tblWkHrsVsBudgeted VARIANCE
ON VARIANCE.PLANT = STAR.PLANT
AND VARIANCE.DTWEEK = STAR.ACTIVITYDATE
AND VARIANCE.intAct IS NOT NULL
AND VARIANCE.intAct <> 0
-----------------------------------------------------------------------------
-- load Market STAR
SET @Msg = 'Loading Market STARS...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

UPDATE VARIANCE
SET MKTSTAR = MKTSTAR.MKTSTAR
FROM (SELECT MARKETS.PLANT Plant
,MARKETS.MARKET Market
,PLANTS.ACTIVITYDATE Activitydate
,PLANTS.MKTSTAR MKTSTAR
FROM
((SELECT Market = FOS.Market
,Activitydate = DATEADD(dd,- (DATEPART(dw, ACTARC.Activitydate) -7),ACTARC.Activitydate)
,MKTSTAR =
CASE SUM(ISNULL(ACTARC.LaborHours,0))
WHEN 0 THEN 0
ELSE CAST(SUM(ISNULL(ACTARC.InTraffic,0) + .00) / SUM(ISNULL(ACTARC.LaborHours,0) + .00)
AS DECIMAL(6,1))
END
FROM vStoreDlyActivityYTD ACTARC,
dbo.tblLaborStoreDetail FOS
WHERE ACTARC.Plant = FOS.Plant
GROUP BY FOS.MARKET,
DATEADD(dd,-(DATEPART(dw, Activitydate) - 7),Activitydate)) PLANTS

RIGHT OUTER JOIN dbo.tblLaborStoreDetail MARKETS
ON PLANTS.Market = MARKETS.Market)) MKTSTAR
JOIN tblWkHrsVsBudgeted VARIANCE
ON VARIANCE.PLANT = MKTSTAR.PLANT
AND VARIANCE.DTWEEK = MKTSTAR.ACTIVITYDATE
AND VARIANCE.intAct IS NOT NULL
AND VARIANCE.intAct <> 0

-----------------------------------------------------------------------------
-- load Division STAR
SET @Msg = 'Loading Division STARS...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

UPDATE VARIANCE
SET DIVSTAR = DIVSTAR.DIVSTAR
FROM (SELECT DIVISIONS.PLANT Plant
,DIVISIONS.DIVISION Division
,PLANTS.ACTIVITYDATE Activitydate
,PLANTS.DIVSTAR DIVSTAR
FROM
((SELECT Division = FOS.Division
,Activitydate = DATEADD(dd,- (DATEPART(dw, ACTARC.Activitydate) - 7),ACTARC.Activitydate)
,DIVSTAR =
CASE SUM(ISNULL(ACTARC.LaborHours,0))
WHEN 0 THEN 0
ELSE CAST(SUM(ISNULL(ACTARC.InTraffic,0) + .00) / SUM(ISNULL(ACTARC.LaborHours,0) + .00)
AS DECIMAL(6,1))
END
FROM vStoreDlyActivityYTD ACTARC,
dbo.tblLaborStoreDetail FOS
WHERE ACTARC.Plant = FOS.Plant
GROUP BY FOS.DIVISION,
DATEADD(dd,-(DATEPART(dw, Activitydate) - 7),Activitydate)) PLANTS

RIGHT OUTER JOIN dbo.tblLaborStoreDetail DIVISIONS
ON PLANTS.Division = DIVISIONS.Division)) DIVSTAR
JOIN tblWkHrsVsBudgeted VARIANCE
ON VARIANCE.PLANT = DIVSTAR.PLANT
AND VARIANCE.DTWEEK = DIVSTAR.ACTIVITYDATE
AND VARIANCE.intAct IS NOT NULL
AND VARIANCE.intAct <> 0

-----------------------------------------------------------------------------
-- load Channel STAR
SET @Msg = 'Loading Channel STAR...'
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg

UPDATE VARIANCE
SET CHSTAR = CHSTAR.CHSTAR
FROM (SELECT Activitydate =
DATEADD(dd,-(DATEPART(dw, Activitydate) - 7),Activitydate)
,CHSTAR =
CASE SUM(ISNULL(LaborHours,0))
WHEN 0 THEN 0
ELSE CAST(SUM(ISNULL(InTraffic,0) + .00) / SUM(ISNULL(LaborHours,0) + .00)
AS DECIMAL(6,1))
END
FROM vStoreDlyActivityYTD
GROUP BY DATEADD(dd,-(DATEPART(dw, Activitydate) - 7),Activitydate)) CHSTAR
JOIN tblWkHrsVsBudgeted VARIANCE
ON VARIANCE.DTWEEK = CHSTAR.ACTIVITYDATE
AND VARIANCE.intAct IS NOT NULL
AND VARIANCE.intAct <> 0

-------------------------------------------------------------------------------------
-- show row counts
SELECT @RowCnt = COUNT(Plant) FROM tblWkHrsVsBudgeted
SET @Msg = 'tblWkHrsVsBudgeted: '
EXEC sp_showmessage @objid = @ProcID, @msg = @Msg, @rowcount = @RowCnt
-------------------------------------------------------------------------------------

END -- exec sproc_tblWkHrsVsBudgeted