January 8, 2009 at 11:43 pm
Hi All,
I am new to performance tuning. I wanted to do tune below stored procedure. Please help me that anything needs to be change.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[SCHL_PopulateScheduleData]
(
@p_iWindow tinyint,--1=SCHEDULE_MAINT, 2=PROJECT_MAINT, 3=POSITION_MAINT
@p_iActiveUserKey int,
@p_iActiveConsultantKey int,
@p_iConsultantKey int = 0,
@p_iProjectKey int = 0,
@p_iPositionKeyint = 0,
@p_iCalculateTotals int = 0,
@p_iAssocScheduleKey int = 0,
@p_iAddScheduleItemType int = 0, --1:Add an activity row, 2:Add a position row
@p_iPositionJagged int = 1 --When set to 1 (the default) generates a jagged array for position. Otherwise, rows
--are generated for all fiscal periods.
) AS
/*
Populates the work tables with the schedule data at the period level and daily level if applicable.
- Work_Period_Schedule
- Work_Period_Schedule_Col_Items
- Work_Period_Schedule_Row_Items
- Work_Daily_Schedule
If @p_iWindow = 1 µSCHEDULE_MAINT╞
- Load Work_Period_Schedule_Row_Items with a row for every assignment, every reservation and every activity for the associate
- Load Work_Period_Schedule_Col_Items
- Load Work_Period_Schedule with the period schedule hours for all activities, assignments and reservations for the associate.
- Load Work_Daily_Schedule table with the daily scheduled hours for all activities, assignments and reservations for the associate.
Else If @p_iWindow = 2 µPROJECT_MAINT╞
- Load Work_Period_Schedule_Row_Items with a row for every position in this project.
- Load Work_Period_Schedule_Col_Items Load Work_Period_Schedule with the period schedule hours for all positions under this project.
- Load Work_Daily_Schedule table with the daily scheduled hours for all positions under this project.
Else If @p_iWindow = 3 µPOSITION_MAINT╞
- Load Work_Period_Schedule_Row_Items with a row for the position itself
- Load Work_Period_Schedule_Col_Items Load Work_Period_Schedule with the period schedule hours for the position.
- Load Work_Daily_Schedule table with the daily scheduled hours for the position.
End If
*/
SET NOCOUNT ON
DECLARE
@sSQL nvarchar(4000),
@sTable_WorkDailySchedule varchar(255),
@iIsNew int,
@sMsg varchar(255),
@sRoom char(1),
@sObjFamily varchar(8),
@iActiveUserFamilyKey int,
@iFamilyKey int,
@iSchedulingBasisCode tinyint,-- 1) Calendar, 2)Forecast
@iLastClosedFiscalPeriod int,
@iLastClosedFiscalYear int,
@iLastClosedFiscalPeriodEndDate datetime,
@iAllowAssocEditActvFcstInd tinyint,
@iAllowAssocFillSrchPosnInd tinyint,
@iProfilePlnWorkSchdInhCd tinyint,
@sTag varchar(1000),
@iWorkScheduleID int,
@iProjectUserID int,
@iProjectID int,
@iProjectName varchar(255),
@iConsultantUserID int,
@iConsultantID int,
@iSchedulingLevelCode tinyint,
@iAllowAssocEditNonProjInd tinyint,
@iActiveUserID int,
@sPermMask char(6),
@fFullDayHours float,
@iCalendarID int,
@iTEInstalled tinyint,
@iProjectProjectMgmtIntegrationInd int,
@uType char(1),
@uId int,
@p_sAccessProfMaintAllowed char(1),
@p_sAccessProfSchedAllowed char(1)
IF @p_iWindow = 1 --SCHEDULE_MAINT
BEGIN
CREATE TABLE #CO_HO
(
fiscal_period_key int,
hours float,
avail_time_ind char(1),
schedule_item_type tinyint
)
END
IF @p_iActiveConsultantKey IS NULL SET @p_iActiveConsultantKey = 0
IF @p_iAddScheduleItemType IS NULL SET @p_iAddScheduleItemType = 0
--If adding an item, see if it already exists... If so, there's no point in continuing.
IF @p_iAddScheduleItemType = 1
AND EXISTS
(
SELECT
1
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items WITH (READUNCOMMITTED)
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
AND schedule_item_type = 1
AND schedule_item_key = @p_iAssocScheduleKey
)
BEGIN
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
action_ind = 1
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
AND schedule_item_type = 1
AND schedule_item_key = @p_iAssocScheduleKey
RETURN
END
IF @p_iAddScheduleItemType = 2
AND EXISTS
(
SELECT
1
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items WITH (READUNCOMMITTED)
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
AND schedule_item_type = 2
AND schedule_item_key = @p_iPositionKey
)
BEGIN
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
action_ind = 1
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
AND schedule_item_type = 2
AND schedule_item_key = @p_iPositionKey
RETURN
END
--Validate the parameters.
IF @p_iAddScheduleItemType = 0
BEGIN
IF @p_iWindow = 1 AND ISNULL(@p_iConsultantKey, 0) = 0
BEGIN
SELECT @sMsg = 'Internal error: @p_iConsultantKey is required when @p_iWindow = 1.'
RAISERROR(@sMsg,11, 1)
RETURN
END
ELSE IF @p_iWindow = 2 AND ISNULL(@p_iProjectKey, 0) = 0
BEGIN
SELECT @sMsg = 'Internal error: @p_iProjectKey is required when @p_iWindow = 2.'
RAISERROR(@sMsg,11, 1)
RETURN
END
ELSE IF @p_iWindow = 3 AND ISNULL(@p_iProjectKey, 0) = 0 AND ISNULL(@p_iPositionKey, 0) = 0
BEGIN
SELECT @sMsg = 'Internal error: @p_iProjectKey and @p_iPositionKey are required when @p_iWindow = 3.'
RAISERROR(@sMsg,11, 1)
RETURN
END
END
ELSE IF @p_iWindow = 1 --SCHEDULE_MAINT
BEGIN
IF @p_iAddScheduleItemType = 1 AND ISNULL(@p_iAssocScheduleKey, 0) = 0
BEGIN
SELECT @sMsg = 'Internal error: @p_iAssocScheduleKey is required when @p_iAddScheduleItemType = 1.'
RAISERROR(@sMsg,11, 1)
RETURN
END
IF @p_iAddScheduleItemType = 2 AND ISNULL(@p_iPositionKey, 0) = 0
BEGIN
SELECT @sMsg = 'Internal error: @p_iPositionKey is required when @p_iAddScheduleItemType = 2.'
RAISERROR(@sMsg,11, 1)
RETURN
END
END
--Determine if T&E was installed.
IF EXISTS
(
SELECT
1
FROM
Product_Module_Version
WHERE
module_id = 4
)
SET @iTEInstalled = 1
ELSE
SET @iTEInstalled = 0
--Get the name of the brokered table.
EXEC COMM_GetBrokeredTableName 'Work_Daily_Schedule', @p_iActiveUserKey, @p_iActiveConsultantKey, @sTable_WorkDailySchedule OUTPUT, @iIsNew OUTPUT
--No need to worry about @iIsNew here. We are rebuilding the table anyway.
-- CR 24590
-- When adding an item from Associate schedule window, we need to look at the @iIsNew value
IF (@p_iWindow = 1) AND (@p_iAddScheduleItemType > 0)
BEGIN
--The table shouldn't be new at this point. If it is, the table expired and they lost their changes. So, throw an error.
--They will need to start their edits over.
IF @iIsNew = 1
BEGIN
--1057: An attempt was made to update a position that does not exist.
RAISERROR ('&&@COMM@ @1196@',11,1)
RETURN
END
END
--Look up the old keys for the consultant.
IF @p_iWindow = 1
BEGIN
SELECT
@iConsultantUserID = c.member_id,
@iConsultantID = c.consultant_id
FROM
CXmain.dbo.Consultants c WITH (READUNCOMMITTED)
WHERE
c.consultant_key = @p_iConsultantKey
END
--Get user related data.
IF @p_iActiveConsultantKey > 0
SELECT
@iFamilyKey = f.family_key,
@iActiveUserFamilyKey = f.family_key,
@iActiveUserID = u.user_id
FROM
CXmain.dbo.Consultants c WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Users u WITH (READUNCOMMITTED)
ON c.member_id = u.user_id
INNER JOIN CXmain.dbo.Family f
ON u.room = f.room
AND u.family = f.family
WHERE
c.consultant_key = @p_iActiveConsultantKey
ELSE
SELECT
@iFamilyKey = f.family_key,
@iActiveUserID = u.user_id,
@iActiveUserFamilyKey = f.family_key
FROM
CXmain.dbo.Users u WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Family f
ON u.room = f.room
AND u.family = f.family
INNER JOIN CXmain.dbo.Family_Settings fs
ON u.room = f.room
AND u.family = f.family
WHERE
u.user_key = @p_iActiveUserKey
--Get room and family and other associate/project info
IF @p_iWindow = 1 --SCHEDULE_MAINT
BEGIN
SELECT
@sRoom = room,
@sObjFamily = family,
@sTag = tag
FROM
CXmain.dbo.Users u WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Consultants c WITH (READUNCOMMITTED)
ON u.user_id = c.member_id
WHERE
c.consultant_key = @p_iConsultantKey
END
ELSE IF @p_iWindow = 3 --POSITION_MAINT
BEGIN
SELECT
@sRoom = room,
@sObjFamily = family
FROM
CXmain.dbo.Users u WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Project_Detail pd
ON pd.user_id = u.user_id
WHERE
pd.position_key = @p_iPositionKey
END
ELSE
BEGIN
SELECT
@sRoom = room,
@sObjFamily = family,
@iProjectProjectMgmtIntegrationInd = p.project_mgmt_integration_ind
FROM
CXmain.dbo.Users u WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Projects p
ON p.user_id = u.user_id
WHERE
p.project_key = @p_iProjectKey
END
------------------------------------------------------------------------
-- Clear existing data from the work tables.
-------------------------------------------------------------------------
IF @p_iAddScheduleItemType = 0
BEGIN
--Work_Period_Schedule_Col_Items
DELETE CXwork.dbo.Work_Period_Schedule_Col_Items WITH (REPEATABLEREAD)
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
--Work_Period_Schedule_Row_Items
DELETE CXwork.dbo.Work_Period_Schedule_Row_Items WITH (REPEATABLEREAD)
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
--Work_Period_Schedule
DELETE CXwork.dbo.Work_Period_Schedule WITH (REPEATABLEREAD)
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
--Work_Daily_Schedule
SET @sSQL = N' --Clear Work_Daily_Schedule
DELETE ' + @sTable_WorkDailySchedule + N' WITH (REPEATABLEREAD)
WHERE
active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
'
EXEC sp_executesql --Clear Work_Daily_Schedule
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int ',
@p_iActiveUserKey, @p_iActiveConsultantKey
END
-------------------------------------------------------------------------
-- Populate Work_Period_Schedule_Col_Items
-------------------------------------------------------------------------
IF @p_iAddScheduleItemType = 0
BEGIN
--Just need to make one pass. There will be one pass that will insert a row for every fiscal period defined in the room.
SET @sSQL = N'
INSERT CXwork.dbo.Work_Period_Schedule_Col_Items
(
active_user_key,
active_consultant_key,
col_index,
fiscal_period_key,
fiscal_year,
fiscal_period,
fiscal_period_name,
fiscal_period_start_date,
fiscal_period_end_date,
closed_ind,
period_variance,
fiscytd_variance,
utilized_time,
capacity_adjustments,
initial_capacity,
period_utilization_pct,
available_hours
)
SELECT
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
''FY'' + CAST(fyp.fiscal_year AS varchar(20)) + ''_'' + REPLACE(STR(fyp.fiscal_period, 3, 0), '' '', ''0''), --col_index
fyp.fiscal_period_key, --fiscal_period_key
fyp.fiscal_year, --fiscal_year
fyp.fiscal_period, --fiscal_period
fyp.name, --fiscal_period_name
fyp.start_date, --fiscal_period_start_date
fyp.end_date, --fiscal_period_end_date
CASE
WHEN fyp.close_date <= GETDATE() THEN 1
ELSE 0
END, --closed_ind
NULL, --period_variance
NULL, --fiscytd_variance
NULL, --utilized_time
NULL, --capacity_adjustments
NULL, --initial_capacity
NULL, --period_utilization_pct'
IF @p_iWindow = 1
SET @sSQL = @sSQL + N'
ISNULL(pws.available_hours, 0) --available_hours'
ELSE
SET @sSQL = @sSQL + N'
NULL --available_hours'
SET @sSQL = @sSQL + N'
FROM
CXmain.dbo.Fiscal_Year_Period fyp'
IF @p_iWindow = 1
SET @sSQL = @sSQL + N'
--Available hours
LEFT OUTER JOIN CXmain.dbo.Consultants_PWS pws WITH (READUNCOMMITTED) --Use this View instead of the PWS table directly.
ON fyp.fiscal_period_key = pws.fiscal_period_key
AND pws.consultant_key = @p_iConsultantKey'
SET @sSQL = @sSQL + N'
WHERE
fyp.room = @sRoom'
EXEC sp_executesql --Row1
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @sRoom char(1), @p_iConsultantKey int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @sRoom, @p_iConsultantKey
END
-------------------------------------------------------------------------
-- Populate Work_Period_Schedule_Row_Items
-------------------------------------------------------------------------
--select 'row'--~~
--NOTE: When updating this query
IF @p_iAddScheduleItemType <> 1
BEGIN
SET @sSQL = N'
--Row1: Fill the rows table with data for positions.
INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
(
active_user_key,
active_consultant_key,
row_index,
schedule_item_key,
schedule_item_type,
schedule_item_name,
schedule_item_updated_ind,
consultant_key,
consultant_user_id,
consultant_id,
consultant_name,
full_period_hours,
scheduling_level_code,
schedule_item_parent_key,
schedule_item_parent_name,
customer_id,
customer_name,
position_details,
position,
posn_staffing_status,
reservation_ind,
bill_ind,
posn_start_date,
posn_end_date,
posn_resc_pool_key,
posn_resc_pool_name,
bus_rule_mask,
target_hours,
posn_total_hours,
posn_total_actual_hours,
posn_total_est_cmpl_hours,
posn_total_schedule_hours,
period_menu_mask,
permission_mask,
change_stamp,'
IF @p_iAddScheduleItemType = 2
SET @sSQL = @sSQL + N'
action_ind,'
SET @sSQL = @sSQL + N'
updated_ind,
critical_location_key,
critical_consultant_key,
family_key,
calc_fp_schd_hrs_on_day_chg_cd,
position_status_key
)
SELECT
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
''POSN_S'' + CAST(pd.user_id AS varchar(20)) + ''_'' + CAST(pd.project_id AS varchar(20)) +
''_'' + CAST(pd.position AS varchar(20)),--row_index
pd.position_key, --schedule_item_key
2, --schedule_item_type 2=position assignment
pd.description, --schedule_item_name
0, --schedule_item_updated_ind
pd.consultant_key, --consultant_key
pd.profile_user_id, --consultant_user_id
pd.profile_consultant_id, --consultant_id
case when pd.project_object_id = 2 or pd.reservation_ind = 1 then c.tag when pd.project_object_id = 6 then pd.external_item else "" end, --consultant_name
0, --full_period_hours
NULL, --scheduling_level_code
p.project_key, --schedule_item_parent_key
p.identifier, --schedule_item_parent_name
p.customer_id, --customer_id
cu.customer_name, --customer_name
ISNULL(CONVERT(varchar(500), pd.position_details), ''''), --position_details
pd.position, --position
pd.project_object_id, --posn_staffing_status
pd.reservation_ind, --reservation_ind
pd.bill_ind, --bill_ind
pd.start_date, --posn_start_date
pd.end_date, --posn_end_date
pd.resource_pool_key, --posn_resc_pool_key
rp.name, --posn_resc_pool_name
'
IF @p_iWindow = 1
SET @sSQL = @sSQL + N'
s.bus_rule_mask, --bus_rule_mask
'
ELSE
SET @sSQL = @sSQL + N'
NULL, --bus_rule_mask
'
SET @sSQL = @sSQL + N'
pd.target_hours, --target_hours
NULL, --posn_total_hours
NULL, --posn_total_actual_hours
NULL, --posn_total_est_cmpl_hours
NULL, --posn_total_schedule_hours
''00000000000'', --period_menu_mask
NULL, --permission_mask
pcs.change_stamp, --change_stamp'
IF @p_iAddScheduleItemType = 2
SET @sSQL = @sSQL + N'
1, --action_ind'
SET @sSQL = @sSQL + N'
0, --updated_ind'
IF @p_iWindow = 2
SET @sSQL = @sSQL + N'
CASE
WHEN pd.office_location_critical_ind = 1 THEN pd.office_location_key
ELSE NULL
END, --critical_location_key
CASE
WHEN pc.critical_ind = 1 THEN c2.consultant_key
ELSE NULL
END, --critical_consultant_key'
ELSE
SET @sSQL = @sSQL + N'
NULL, --critical_location_key
NULL, --critical_consultant_key'
IF @p_iWindow = 1
SET @sSQL = @sSQL + N'
f.family_key, --family_key
fs.calc_fp_schd_hrs_on_day_chg_cd,
pd.position_status_key '
ELSE
SET @sSQL = @sSQL + N'
NULL, --family_key
NULL, --calc_fp_schd_hrs_on_day_chg_cd
pd.position_status_key '
SET @sSQL = @sSQL + N'
FROM
CXmain.dbo.Project_Detail pd
LEFT OUTER JOIN Position_Change_Stamp pcs
ON pd.position_key = pcs.position_key
INNER JOIN CXmain.dbo.Users u WITH (READUNCOMMITTED)
ON u.user_id = pd.user_id
AND u.room = @sRoom
INNER JOIN CXmain.dbo.Projects p
ON pd.user_id = p.user_id
AND pd.project_id = p.project_id
'
IF @p_iWindow = 1
SET @sSQL = @sSQL + N'
INNER JOIN Family f
ON f.room = u.room
AND f.family = u.family
INNER JOIN Family_Settings fs
ON fs.room = u.room
AND fs.family = u.family
INNER JOIN Status s
ON pd.position_status_key = s.status_key'
SET @sSQL = @sSQL + N'
INNER JOIN CXmain.dbo.Customers cu
ON p.customer_id = cu.customer_id
LEFT OUTER JOIN CXmain.dbo.Resource_Pool rp
ON pd.resource_pool_key = rp.resource_pool_key
LEFT OUTER JOIN Consultants c WITH (READUNCOMMITTED)
ON c.consultant_key = pd.consultant_key'
IF @p_iWindow = 2
SET @sSQL = @sSQL + N'
LEFT OUTER JOIN
(
Position_Candidate pc
INNER JOIN Consultants c2 WITH (READUNCOMMITTED)
ON pc.consultant_user_id = c2.member_id
AND pc.consultant_id = c2.consultant_id
AND pc.critical_ind = 1 --This will prevent rows from duplicating since only one candidate can be critical.
)
ON
pc.project_user_id = pd.user_id
AND pc.project_id = pd.project_id
AND pc.position = pd.position
'
--NOTE: Performance could be improved if Position_Candidate was keyed by position_key and consultant_key.
--Then, Consultants c2 could be dropped from the join.
SET @sSQL = @sSQL + N'
WHERE
'
--Only get forecast data for the object specified.
IF @p_iWindow = 3 --POSITION_MAINT
SET @sSQL = @sSQL + N'
pd.position_key = @p_iPositionKey
'
IF @p_iWindow = 2 --PROJECT MAINT
SET @sSQL = @sSQL + N'
p.project_key = @p_iProjectKey
'
IF @p_iWindow = 1 AND @p_iAddScheduleItemType = 2 --SCHEDULE_MAINT
SET @sSQL = @sSQL + N'
pd.consultant_key = @p_iConsultantKey
AND pd.position_key = @p_iPositionKey
'
ELSE IF @p_iWindow = 1
SET @sSQL = @sSQL + N'
pd.consultant_key = @p_iConsultantKey
'
EXEC sp_executesql --Row1
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @sRoom char(1), @p_iProjectKey int, @p_iPositionKey int, @p_iConsultantKey int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @sRoom, @p_iProjectKey, @p_iPositionKey, @p_iConsultantKey
END
IF @p_iWindow = 1 AND @p_iAddScheduleItemType <> 2 --SCHEDULE_MAINT
BEGIN
SET @sSQL = N'
--Row2: Fill the rows table with data for activities (detail).
INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
(
active_user_key,
active_consultant_key,
row_index,
schedule_item_key,
schedule_item_type,
schedule_item_name,
schedule_item_updated_ind,
consultant_key,
consultant_user_id,
consultant_id,
consultant_name,
full_period_hours,
schedule_item_parent_key,
schedule_item_parent_name,
customer_id,
customer_name,
position_details,
position,
posn_staffing_status,
reservation_ind,
bill_ind,
posn_start_date,
posn_end_date,
posn_resc_pool_key,
posn_resc_pool_name,
target_hours,
posn_total_hours,
posn_total_actual_hours,
posn_total_est_cmpl_hours,
posn_total_schedule_hours,
period_menu_mask,
permission_mask,
change_stamp,'
IF @p_iAddScheduleItemType = 1
SET @sSQL = @sSQL + N'
action_ind,'
SET @sSQL = @sSQL + N'
updated_ind
)
SELECT
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
''ACT_'' + CAST(a.activity_key AS varchar(20)) + ''_'' + CAST(a.assoc_schedule_key AS varchar(20)), --row_index
a.assoc_schedule_key, --schedule_item_key
1, --schedule_item_type...1=activity
a.description, --schedule_item_name
0, --schedule_item_updated_ind
@p_iConsultantKey, --consultant_key
@iConsultantUserID, --consultant_user_id
@iConsultantID, --consultant_id
NULL, --consultant_name
0, --full_period_hours
a.activity_key, --schedule_item_parent_key
av.activity, --schedule_item_parent_name
NULL, --customer_id
NULL, --customer_name
'''', --comments
NULL, --position
NULL, --posn_staffing_status
NULL, --reservation_ind,
NULL, --bill_ind,
NULL, --posn_start_date
NULL, --posn_end_date
NULL, --posn_resc_pool_key
NULL, --posn_resc_pool_name
NULL, --target_hours
NULL, --posn_total_hours
NULL, --posn_total_actual_hours
NULL, --posn_total_est_cmpl_hours
NULL, --posn_total_schedule_hours
''00000000000'', --period_menu_mask
NULL, --permission_mask
a.change_stamp, --change_stamp'
IF @p_iAddScheduleItemType = 1
SET @sSQL = @sSQL + N'
1, --action_ind'
SET @sSQL = @sSQL + N'
0 --updated_ind
FROM
CXmain.dbo.Associate_Schedule a
INNER JOIN Activities av
ON a.activity_key = av.activity_key
WHERE
consultant_key = @p_iConsultantKey
'
IF @p_iAddScheduleItemType = 1
SET @sSQL = @sSQL + N'
AND a.assoc_schedule_key = @p_iAssocScheduleKey
'
EXEC sp_executesql --Row1
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @p_iConsultantKey int, @p_iAssocScheduleKey int, @iConsultantUserID int, @iConsultantID int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @p_iConsultantKey, @p_iAssocScheduleKey, @iConsultantUserID, @iConsultantID
----Row2b: Fill the rows table with data for activities (summary).
--INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
--(
--active_user_key,
--active_consultant_key,
--row_index,
--schedule_item_key,
--schedule_item_type,
--full_period_hours,
--period_menu_mask
--)
--SELECT DISTINCT
--@p_iActiveUserKey, --active_user_key
--@p_iActiveConsultantKey, --active_consultant_key
--''ACT_'' + CAST(a.activity_key AS varchar(20)), --row_index
--a.activity_key, --schedule_item_key
--3, --schedule_item_type...3=Summary activity
--0, --full_period_hours
--'00000000000' --period_menu_mask
--FROM
--CXmain.dbo.Associate_Schedule a
--WHERE
--consultant_key = @p_iConsultantKey
IF @p_iAddScheduleItemType = 0
BEGIN
--Get the full day hours for the associate.
SELECT
@fFullDayHours = ISNULL(cs.full_day_hours, 0),
@iCalendarID = ISNULL(cs.calendar_id, 0)
FROM
Consultants c WITH (READUNCOMMITTED)
INNER JOIN Consultants_Settings cs WITH (READUNCOMMITTED)
ON cs.user_id = c.member_id
AND cs.consultant_id = c.consultant_id
WHERE
c.consultant_key = @p_iConsultantKey
--Row2c: Fill the rows table with data for company holidays.
INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
(
active_user_key,
active_consultant_key,
row_index,
schedule_item_key,
schedule_item_type,
schedule_item_name,
consultant_key,
consultant_user_id,
consultant_id,
full_period_hours,
period_menu_mask,
position_details
)
VALUES
(
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
'COMPANY_HOLIDAY', --row_index
@iCalendarID, --schedule_item_key
0, --schedule_item_type...
'Company Holidays', --schedule_item_name
@p_iConsultantKey, --consultant_key
@iConsultantUserID, --consultant_user_id
@iConsultantID, --consultant_id
0, --full_period_hours
'00000000000', --period_menu_mask,
''
)
--Insert a hidden row for company holidays marked as available time
INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
(
active_user_key,
active_consultant_key,
row_index,
schedule_item_key,
schedule_item_type,
schedule_item_name,
consultant_key,
consultant_user_id,
consultant_id,
full_period_hours,
period_menu_mask,
position_details
)
VALUES
(
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
'COMPANY_HOLIDAY', --row_index
@iCalendarID, --schedule_item_key
100, --schedule_item_type...
'', --schedule_item_name
@p_iConsultantKey, --consultant_key
@iConsultantUserID, --consultant_user_id
@iConsultantID, --consultant_id
0, --full_period_hours
'00000000000', --period_menu_mask,
''
)
--Insert a hidden row for company holidays marked as unavailable time
INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
(
active_user_key,
active_consultant_key,
row_index,
schedule_item_key,
schedule_item_type,
schedule_item_name,
consultant_key,
consultant_user_id,
consultant_id,
full_period_hours,
period_menu_mask,
position_details
)
VALUES
(
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
'COMPANY_HOLIDAY', --row_index
@iCalendarID, --schedule_item_key
101, --schedule_item_type
'', --schedule_item_name
@p_iConsultantKey, --consultant_key
@iConsultantUserID, --consultant_user_id
@iConsultantID, --consultant_id
0, --full_period_hours
'00000000000', --period_menu_mask,
''
)
--Add a filler row if no other rows exists so the user will still be able to see the grid in Schedule Maintenance.
IF NOT EXISTS
(
SELECT
1
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
WHERE
ri.schedule_item_type = 1
AND ri.active_user_key = @p_iActiveUserKey
AND ri.active_consultant_key = @p_iActiveConsultantKey
)
INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
(
active_user_key,
active_consultant_key,
row_index,
schedule_item_key,
schedule_item_type,
schedule_item_name,
schedule_item_updated_ind,
consultant_key,
consultant_user_id,
consultant_id,
consultant_name,
full_period_hours,
schedule_item_parent_key,
schedule_item_parent_name,
customer_id,
customer_name,
position_details,
posn_staffing_status,
reservation_ind,
bill_ind,
posn_start_date,
posn_end_date,
posn_resc_pool_key,
posn_resc_pool_name,
target_hours,
posn_total_hours,
posn_total_actual_hours,
posn_total_est_cmpl_hours,
posn_total_schedule_hours,
period_menu_mask,
permission_mask,
change_stamp,
updated_ind
)
VALUES
(
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
'ACT_0_0', --row_index
0, --schedule_item_key
1, --schedule_item_type...1=activity
'', --schedule_item_name
0, --schedule_item_updated_ind
@p_iConsultantKey, --consultant_key
@iConsultantUserID, --consultant_user_id
@iConsultantID, --consultant_id
NULL, --consultant_name
0, --full_period_hours
NULL, --schedule_item_parent_key
NULL, --schedule_item_parent_name
NULL, --customer_id
NULL, --customer_name
'''', --comments
NULL, --posn_staffing_status
NULL, --reservation_ind,
NULL, --bill_ind
NULL, --posn_start_date
NULL, --posn_end_date
NULL, --posn_resc_pool_key
NULL, --posn_resc_pool_name
NULL, --target_hours
NULL, --posn_total_hours
NULL, --posn_total_actual_hours
NULL, --posn_total_est_cmpl_hours
NULL, --posn_total_schedule_hours
'00000000000', --period_menu_mask
NULL, --permission_mask
NULL, --change_stamp
0 --updated_ind
)
END
END
--If in the project view, insert a row...for the 'other bucket' where actual hours that don't fall under a specific position fall.
IF @p_iAddScheduleItemType = 0 AND @iTEInstalled = 1
BEGIN
IF @p_iWindow = 2 --PROJECT_MAINT
BEGIN
SELECT
@iProjectUserID = user_id,
@iProjectID = project_id,
@iProjectName = identifier
FROM
CXmain.dbo.Projects p
WHERE
project_key = @p_iProjectKey
INSERT CXwork.dbo.Work_Period_Schedule_Row_Items
(
active_user_key,
active_consultant_key,
row_index,
schedule_item_key,
schedule_item_type,
schedule_item_name,
schedule_item_updated_ind,
consultant_key,
consultant_name,
full_period_hours,
schedule_item_parent_key,
schedule_item_parent_name,
customer_id,
customer_name,
position_details,
posn_staffing_status,
reservation_ind,
posn_start_date,
posn_end_date,
posn_resc_pool_key,
posn_resc_pool_name,
target_hours,
posn_total_hours,
posn_total_actual_hours,
posn_total_est_cmpl_hours,
posn_total_schedule_hours,
period_menu_mask,
permission_mask,
change_stamp,
updated_ind
)
VALUES
(
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
'POSN_S' + CAST(@iProjectUserID AS varchar(20)) + '_' + CAST(@iProjectID AS varchar(20)) + '_0', --row_index
0, --schedule_item_key
2, --schedule_item_type...1=activity
'Other', --schedule_item_name
0, --schedule_item_updated_ind
NULL, --consultant_key
NULL, --consultant_name
0, --full_period_hours
@p_iProjectKey, --schedule_item_parent_key
@iProjectName, --schedule_item_parent_name
NULL, --customer_id
NULL, --customer_name
'', --comments
NULL, --posn_staffing_status
0, --reservation_ind, CR 22822 - set to 0 so row totals are calculated correctly for "0" row
NULL, --posn_start_date
NULL, --posn_end_date
NULL, --posn_resc_pool_key
NULL, --posn_resc_pool_name
NULL, --target_hours
NULL, --posn_total_hours
NULL, --posn_total_actual_hours
NULL, --posn_total_est_cmpl_hours
NULL, --posn_total_schedule_hours
'00000000000', --period_menu_mask
NULL, --permission_mask
NULL, --change_stamp
0 --updated_ind
)
END
END
IF @p_iWindow = 2 OR @p_iWindow = 3 --PROJECT_MAINT or POSITION_MAINT
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
calendar_id = ISNULL(cs.calendar_id, 0),
full_period_hours = ISNULL(cs.full_period_hours, 0),
full_day_hours = cs.full_day_hours,
scheduling_level_code = fs.scheduling_level_code,
calc_fp_schd_hrs_on_day_chg_cd = fs.calc_fp_schd_hrs_on_day_chg_cd
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Consultants c WITH (READUNCOMMITTED)
ON ri.consultant_key = c.consultant_key
INNER JOIN CXmain.dbo.Consultants_Settings cs WITH (READUNCOMMITTED)
ON c.member_id = cs.user_id
AND c.consultant_id = cs.consultant_id
INNER JOIN CXmain.dbo.Users u WITH (READUNCOMMITTED)
ON c.member_id = u.user_id
INNER JOIN Family_Settings fs
ON u.family = fs.family
AND u.room = fs.room
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
-- ELSE update the Row Items table in the "@p_iWindow = 1" section of code.
-------------------------------------------------
-- Set security
-------------------------------------------------
IF @p_iWindow = 1 --SCHEDULE_MAINT
BEGIN
--Schedule Maint Menu Mask:
--bit 1: Position Maintenance
--bit 2: Position Detail
--bit 3: Project Maintenance
--bit 4: Project Detail
--bit 5: -
--bit 6: Schedule_Detail
--bit 7: -
--bit 8: Clear Viewable Hours
--bit 9: Change Reservation To Assignment
--bit 10: Remove Assignment
--bit 11: Remove Reservation
--Get the active user's permission mask for the associate.
SELECT
@sPermMask = permission_mask
FROM
CXmain.dbo.Viewable_Consultants_vw
WHERE
user_type = 'S'
AND user_id = @iActiveUserID
AND consultant_key = @p_iConsultantKey
--Get the consultant's scheduling level code.
SELECT
@iSchedulingLevelCode = fs.scheduling_level_code,
@iAllowAssocEditNonProjInd = fs.allow_assoc_edit_non_proj_ind
FROM
CXmain.dbo.Consultants c WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Users u WITH (READUNCOMMITTED)
ON c.member_id = u.user_id
INNER JOIN CXmain.dbo.Family_Settings fs
ON u.room = fs.room
AND u.family = fs.family
WHERE
c.consultant_key = @p_iConsultantKey
-- Update the Row Items table
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
calendar_id = ISNULL(cs.calendar_id, 0),
full_period_hours = ISNULL(cs.full_period_hours, 0),
full_day_hours = cs.full_day_hours,
scheduling_level_code = ISNULL(fs.scheduling_level_code, @iSchedulingLevelCode)
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Consultants c WITH (READUNCOMMITTED)
ON ri.consultant_key = c.consultant_key
INNER JOIN CXmain.dbo.Consultants_Settings cs WITH (READUNCOMMITTED)
ON c.member_id = cs.user_id
AND c.consultant_id = cs.consultant_id
LEFT OUTER JOIN CXmain.dbo.Projects p WITH (READUNCOMMITTED)
ON ri.schedule_item_parent_key = p.project_key
AND ri.schedule_item_type = 2
LEFT OUTER JOIN CXmain.dbo.Users u WITH (READUNCOMMITTED)
ON p.user_id = u.user_id
LEFT OUTER JOIN Family_Settings fs
ON u.family = fs.family
AND u.room = fs.room
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
SET @sSQL = N'
--Get the active users permissions for each project.
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
permission_mask = ISNULL(vc.permission_mask, ''000'')
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
LEFT OUTER JOIN CXmain.dbo.Projects_All_Security_In_One_VW vc
ON vc.user_id = @iActiveUserID
AND vc.project_key = ri.schedule_item_parent_key
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
'
IF @p_iAddScheduleItemType = 2
SET @sSQL = @sSQL + N'
AND ri.schedule_item_key = @p_iPositionKey
AND ri.schedule_item_type = 2
'
EXEC sp_executesql --Row3
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @p_iPositionKey int, @iActiveUserID int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @p_iPositionKey, @iActiveUserID
--Build the menu mask
SET @sSQL = N'
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET period_menu_mask =
--bit 1: Position Maintenance
CASE WHEN ri.permission_mask like ''1%'' --active user has View permission to the project
AND @p_iActiveConsultantKey = 0--A user, not an asssociate
THEN ''1'' ELSE ''0'' END +
--bit 2: Position Detail
CASE WHEN ri.permission_mask like ''1%'' --active user has View permission to the project
THEN ''1'' ELSE ''0'' END +
--bit 3: Project Maintenance
CASE WHEN ri.permission_mask like ''1%'' --active user has View permission to the project
AND @p_iActiveConsultantKey = 0--Not an assoc
THEN ''1'' ELSE ''0'' END +
--bit 4: Project Detail
CASE WHEN ri.permission_mask like ''1%'' --active user has View permissions to the project
THEN ''1'' ELSE ''0'' END +
--bit 5: -
''0'' +
--bit 6: Schedule_Detail
CASE WHEN (@p_iActiveConsultantKey = 0 AND SUBSTRING(@sPermMask, 3, 1) <> ''1'') OR (@p_iActiveConsultantKey > 0 AND @iAllowAssocEditNonProjInd = 0)
THEN ''0''
WHEN ri.schedule_item_type = 1 --Row is an activity
THEN ''1''
WHEN ri.schedule_item_type = 2 --Row is a position
AND (ri.scheduling_level_code IN (2, 3) --Project owners code
OR @iSchedulingLevelCode IN (2, 3)) --Associates code
THEN ''1''
ELSE ''0''
END +
--bit 7: -
''0'' +
--bit 8: Clear Viewable Hours
CASE WHEN ri.schedule_item_type = 2 --Row is a pos assignment
THEN ''0''
WHEN @p_iActiveConsultantKey = 0
AND SUBSTRING(@sPermMask, 3, 1) = ''1'' --active user has Schedule permissions to the associate
THEN ''1''
WHEN @p_iActiveConsultantKey > 0
AND @iAllowAssocEditNonProjInd = 1 --"Allow Editing of Non-Project Related Activities" is checked
THEN ''1''
ELSE ''0''
END +
--bit 9: Change Reservation To Assignment
CASE WHEN @p_iActiveConsultantKey = 0
AND ri.reservation_ind = 1
AND SUBSTRING(ri.permission_mask, 3, 1) = ''1'' --active user has Assign permissions to the project
THEN ''1''
WHEN @p_iActiveConsultantKey > 0
AND ri.reservation_ind = 1
AND SUBSTRING(ri.permission_mask, 3, 1) = ''1'' --active user has Assign permissions to the project
AND SUBSTRING(ri.bus_rule_mask, 4, 1) = ''1'' --"Allow Associate to Assign from Schedule" is set for the positions status
THEN ''1''
ELSE ''0''
END +
--bit 10: Remove Assignment
CASE WHEN @p_iActiveConsultantKey = 0
AND ri.posn_staffing_status = 2
AND SUBSTRING(ri.permission_mask, 3, 1) = ''1'' --active user has Assign permissions to the project
AND SUBSTRING(@sPermMask, 3, 1) = ''1'' --active user has Schedule permissions to the associate
THEN ''1''
WHEN @p_iActiveConsultantKey > 0
AND ri.posn_staffing_status = 2
AND SUBSTRING(ri.permission_mask, 3, 1) = ''1'' --active user has Assign permissions to the project
AND (SUBSTRING(ri.bus_rule_mask, 4, 1) = ''1'' --"Allow Associate to Assign from Schedule" set for the positions status
OR SUBSTRING(ri.bus_rule_mask, 5, 1) = ''1'') --"Allow Associate to Assign from Search" set for the positions status
THEN ''1''
ELSE ''0''
END +
--bit 11: Remove Reservation
CASE WHEN @p_iActiveConsultantKey = 0
AND ri.reservation_ind = 1
AND SUBSTRING(ri.permission_mask, 3, 1) = ''1'' --active user has Assign permissions to the project.
AND SUBSTRING(@sPermMask, 5, 1) = ''1'' --active user has Reserve permissions to the associate.
THEN ''1''
WHEN @p_iActiveConsultantKey > 0
AND ri.reservation_ind = 1
AND SUBSTRING(ri.permission_mask, 3, 1) = ''1'' --active user has Assign permissions to the project.
AND SUBSTRING(ri.bus_rule_mask, 6, 1) = ''1'' --"Allow Associate to Reserve" set for the positions status.
THEN ''1''
ELSE ''0''
END
FROM CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
WHERE ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey '
IF @p_iAddScheduleItemType = 2
SET @sSQL = @sSQL + N'
AND ri.schedule_item_key = @p_iPositionKey
AND ri.schedule_item_type = 2 '
EXEC sp_executesql --Row3
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @p_iPositionKey int, @sPermMask char(6), @iSchedulingLevelCode int, @iAllowAssocEditNonProjInd int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @p_iPositionKey, @sPermMask, @iSchedulingLevelCode, @iAllowAssocEditNonProjInd
END
IF @p_iWindow = 2 AND @p_iAddScheduleItemType = 0 --PROJECT_MAINT
BEGIN
--Project Maint Menu Mask:
--bit 1: Position Maintenance
--bit 2: Profile Maintenance
--bit 3: Profile Detail
--bit 4: Associate Schedule
--bit 5: -
--bit 6: Schedule Detail
--bit 7: Assign Associate
--bit 8: Change Associate
--bit 9: Remove Associate
--bit 10: Replicate Position
--bit 11: Delete Position
--Get the project owner's scheduling level code.
SELECT
@iSchedulingLevelCode = fs.scheduling_level_code
FROM
CXmain.dbo.Projects p
INNER JOIN CXmain.dbo.Users u WITH (READUNCOMMITTED)
ON p.user_id = u.user_id
INNER JOIN CXmain.dbo.Family_Settings fs
ON u.room = fs.room
AND u.family = fs.family
WHERE
p.project_key = @p_iProjectKey
--Get the active user's permission mask for the associate.
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
permission_mask = vc.permission_mask,
family_key = vc.consultant_family_key,
-- cr26325 Added column for get the associate status
status =vc.status
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
LEFT OUTER JOIN CXmain.dbo.Viewable_Consultants_vw vc
ON vc.user_type = 'S'
AND vc.user_id = @iActiveUserID
AND vc.consultant_key = ri.consultant_key
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
--Get the active user's permission mask for the associate.
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
profile_int_acct_display_code = fs.profile_int_acct_display_code,
profile_ext_acct_display_code = fs.profile_ext_acct_display_code
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Family f
ON f.family_key = ri.family_key
INNER JOIN CXmain.dbo.Family_Settings fs
ON f.room = fs.room
AND f.family = fs.family
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
--Look up the active user's security to the project.
SELECT
@sPermMask = permission_mask
FROM
CXmain.dbo.Projects_All_Security_In_One_VW
WHERE
user_id = @iActiveUserID
AND project_key = @p_iProjectKey
SELECT @uType= user_type , @uId = user_id
FROM USERS WHERE @p_iActiveUserKey = user_key
EXEC MENU_CheckMenuAccess @uType, @uId, 122, @p_sAccessProfMaintAllowed output -- KJC
EXEC MENU_CheckMenuAccess @uType, @uId, 126, @p_sAccessProfSchedAllowed output -- KJC
--Build the menu mask
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
period_menu_mask =
--bit 1: Position Maintenance
CASE
WHEN ri.schedule_item_key > 0 AND @p_iActiveConsultantKey = 0 THEN '1' --The row has a valid position, and the active user is not an associate.
ELSE '0'
END +
--bit 2: Profile Maintenance
CASE
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key = @iFamilyKey
AND @p_sAccessProfMaintAllowed = "Y" -- KJC
AND ri.permission_mask like '1%' --The active user has query access.
THEN '1'
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key <> @iFamilyKey
AND ri.permission_mask like '1%' --The active user has edit access.
THEN '1'
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key <> @iFamilyKey
AND ri.permission_mask like '1%' --The active user has query access.
AND ri.profile_ext_acct_display_code IN (1, 3)
THEN '1'
ELSE '0'
END +
--bit 3: Profile Detail
CASE
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key = @iFamilyKey
AND ri.permission_mask like '1%' --The active user has query access to the associate.
THEN '1'
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key <> @iFamilyKey
AND SUBSTRING(ri.permission_mask, 2, 1) = '1' --The active user has edit access to the associate.
THEN '1'
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key <> @iFamilyKey
AND ri.profile_ext_acct_display_code IN (1, 3)
AND ri.permission_mask like '1%' --The active user has query access to the associate.
THEN '1'
ELSE '0'
END +
--bit 4: Associate Schedule
CASE
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key = @iFamilyKey
AND @p_sAccessProfSchedAllowed = "Y" -- KJC
AND ri.permission_mask like '1%' --The active user has query access.
THEN '1'
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key <> @iFamilyKey
AND ri.permission_mask like '1%' --The active user has edit access.
THEN '1'
WHEN
ri.consultant_key > 0 --The row has an associate
AND ri.family_key <> @iFamilyKey
AND ri.permission_mask like '1%' --The active user has query access.
AND ri.profile_ext_acct_display_code IN (1, 3)
THEN '1'
ELSE '0'
END +
'0' + --bit 5: Place holder
--bit 6: Schedule Detail
CASE
/* CR 26503 - Suresh Babu K - Start - Added query to check whether a position has
* an associate with 'Active' status. The following query returns '1' when a position
* has an associate with 'Active' status and @iSchedulingLevelCode value is either 2 or
* 3 or CXwork.dbo.Work_Period_Schedule_Row_Items.scheduling_level_code value is either
* 2 or 3. Otherwise it returns '0'.
*/
WHEN
ISNULL(ri.status, 'A') = 'A'
OR ri.consultant_key <= 0
AND ( @iSchedulingLevelCode IN (2, 3) OR ri.scheduling_level_code IN (2, 3) )
THEN '1'
ELSE '0'
/* CR 26503 - End*/
END +
--bit 7: Assign Associate
CASE
WHEN
SUBSTRING(@sPermMask, 3, 1) = '1'
AND posn_staffing_status <> 2
AND ri.schedule_item_key > 0
THEN '1'
ELSE '0'
END +
--bit 8, bit 9: Change Associate, Remove Associate
CASE
WHEN
SUBSTRING(@sPermMask, 3, 1) = '1'
AND posn_staffing_status = 2
AND SUBSTRING(ri.permission_mask, 3, 1) = 1
AND ri.schedule_item_key > 0
AND ri.status='A'
THEN '11'
ELSE '00'
END +
--bit 10: Replicate Position
CASE
WHEN SUBSTRING(@sPermMask, 2, 1) = '1' AND ri.schedule_item_key > 0 THEN '1'
ELSE '0'
END +
--bit 11: Delete Position
CASE
WHEN
SUBSTRING(@sPermMask, 2, 1) = '1'
AND ri.schedule_item_key > 0
THEN '1'
ELSE '0'
END
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
--Update the Menu Mask if the Project is Integrated with MS Project
IF @iProjectProjectMgmtIntegrationInd = 1
BEGIN
--If the project is not checked out to the active user then
--disable the following actions
--bit 7: Assign Associate
--bit 8: Change Associate
--bit 9: Remove Associate
--bit 11: Delete Position
IF NOT EXISTS (
SELECT 1
FROM
ProjectIntg.dbo.Project_Map pm
WHERE
integrated_system_code = 1
AND project_key = @p_iProjectKey
-- Checked out to Active User; and not checked out by the API.
AND check_out_status_code = 2
AND checked_out_status_by_user_key = @p_iActiveUserKey
AND api_ind = 0
)
BEGIN
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
period_menu_mask = SUBSTRING( period_menu_mask, 1, 6) + '000' + SUBSTRING( period_menu_mask, 10, 1) + '0'
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
END
ELSE
--If the project is checked out to the active user and
--if either of the following coditions are true
--1)position has an integrated associate assigned or
--2)position is Open or Open (Posted) and has an integrated role or
--3)position had an integrated associate assigned at the time of checkout.
-- 4)position had an integrated role assigned at the time of checkout.
--then disable this action
--bit 11: Delete Position
BEGIN
--1)Disable Delete Position action if position has an integrated associate assigned.
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
period_menu_mask = SUBSTRING( period_menu_mask, 1, 10) + '0'
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Consultants c WITH (READUNCOMMITTED) ON (
ri.consultant_key = c.consultant_key
)
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
AND ri.posn_staffing_status = 2 --Assigned.
AND c.project_mgmt_integration_ind = 1 --Associate is integrated.
--2)Disable Delete Position action if position is Open or Open (Posted) and has an integrated role.
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
period_menu_mask = SUBSTRING( period_menu_mask, 1, 10) + '0'
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Project_Detail pd ON (
ri.schedule_item_key = pd.position_key
) INNER JOIN CXmain.dbo.Project_Roles pr ON (
pr.room = @sRoom
AND pr.family = @sObjFamily
AND pr.role_id = pd.role_id
)
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
AND ri.posn_staffing_status IN (1,3) --Open or Open(Posted).
AND pr.project_mgmt_integration_ind = 1 --Role is integrated.
--3)Disable Delete Position action if position had an integrated associate assigned at the time of checkout.
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
period_menu_mask = SUBSTRING( period_menu_mask, 1, 10) + '0'
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN ProjectIntg.dbo.Position_Before pb ON (
ri.schedule_item_key = pb.position_key
) INNER JOIN CXmain.dbo.Consultants c WITH (READUNCOMMITTED) ON (
pb.consultant_key = c.consultant_key
)
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
AND pb.staffing_status_code = 2 --Assigned at checkout
AND c.project_mgmt_integration_ind = 1 --Associate integrated.
--4)Disable Delete Position action if position had an integrated role assigned at the time of checkout.
UPDATE CXwork.dbo.Work_Period_Schedule_Row_Items
SET
period_menu_mask = SUBSTRING( period_menu_mask, 1, 10) + '0'
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN ProjectIntg.dbo.Position_Before pb ON (
ri.schedule_item_key = pb.position_key
) INNER JOIN CXmain.dbo.Project_Roles pr ON (
pr.room = @sRoom
AND pr.family = @sObjFamily
AND pr.role_id = pb.role_id
)
WHERE
ri.active_user_key = @p_iActiveUserKey
ANDri.active_consultant_key = @p_iActiveConsultantKey
AND ri.schedule_item_type = 2
AND pb.staffing_status_code IN (1,3) --Open or Open(Posted).
AND pr.project_mgmt_integration_ind = 1 --Role is integrated.
END
END
END
-------------------------------------------------------------------------
-- Populate Work_Period_Schedule
-------------------------------------------------------------------------
--select 'period start'--~~
--Get company holiday data.
IF @p_iWindow = 1 AND @p_iAddScheduleItemType = 0 --Schedule Maint
BEGIN
--Figure out the total company holiday hours.
SET @sSQL = N'
INSERT #CO_HO
(
fiscal_period_key,
hours,
schedule_item_type
)
SELECT
rfd.fiscal_period_key,
COUNT(*) * @fFullDayHours,
0
FROM
CXmain.dbo.Family_Calendar_Item fci
INNER JOIN Room_Fiscal_Day rfd
ON fci.item_date = rfd.calendar_date
WHERE
rfd.room = @sRoom
AND fci.calendar_id = @iCalendarID
GROUP BY
rfd.fiscal_period_key
'
EXEC sp_executesql --Company Holidays
@sSQL,
N'@sRoom char(1), @fFullDayHours float, @iCalendarID int ',
@sRoom, @fFullDayHours, @iCalendarID
--Figure out the company holiday hours split by avail_time_ind.
SET @sSQL = N'
INSERT #CO_HO
(
fiscal_period_key,
hours,
avail_time_ind,
schedule_item_type
)
SELECT
rfd.fiscal_period_key,
COUNT(*) * @fFullDayHours,
fci.avail_time_ind,
CASE fci.avail_time_ind
WHEN ''Y'' THEN 100
ELSE 101
END
FROM
CXmain.dbo.Family_Calendar_Item fci
INNER JOIN Room_Fiscal_Day rfd
ON fci.item_date = rfd.calendar_date
WHERE
rfd.room = @sRoom
AND fci.calendar_id = @iCalendarID
GROUP BY
rfd.fiscal_period_key, fci.avail_time_ind
'
EXEC sp_executesql --Company Holidays
@sSQL,
N'@sRoom char(1), @fFullDayHours float, @iCalendarID int ',
@sRoom, @fFullDayHours, @iCalendarID
END
--Populate work period schedule.
SET @sSQL = N'
INSERT CXwork.dbo.Work_Period_Schedule
(
active_user_key,
active_consultant_key,
schedule_item_key,
schedule_item_type,
fiscal_period_key,
schedule_hours,
actual_hours,
cell_overallocated_code,
updated_ind
)
SELECT
ri.active_user_key, --active_user_key
ri.active_consultant_key, --active_consultant_key
ri.schedule_item_key, --schedule_item_key
ri.schedule_item_type, --schedule_item_type
ci.fiscal_period_key, --fiscal_period_key
CASE ri.schedule_item_type'
IF @p_iWindow = 1 --SCHEDULE_MAINT
SET @sSQL = @sSQL + N'
WHEN 0 THEN ISNULL(ch.hours, 0)
WHEN 100 THEN ISNULL(ch.hours, 0)
WHEN 101 THEN ISNULL(ch.hours, 0) '
SET @sSQL = @sSQL + N'
WHEN 1 THEN ISNULL(aps.hours, 0)
WHEN 2 THEN ISNULL(pps.hours, 0)
ELSE 0
END, --schedule_hours'
IF @iTEInstalled = 1
SET @sSQL = @sSQL + N'
ISNULL(ah.actual_hours, 0), --actual_hours'
ELSE
SET @sSQL = @sSQL + N'
0, --actual_hours'
SET @sSQL = @sSQL + N'
0, --cell_overallocated_code
0 --updated_ind
FROM
(CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED) CROSS JOIN CXwork.dbo.Work_Period_Schedule_Col_Items ci WITH (READUNCOMMITTED))'
IF @p_iWindow = 1 --SCHEDULE_MAINT
SET @sSQL = @sSQL + N'
--Company holidays
LEFT OUTER JOIN #CO_HO ch WITH (REPEATABLEREAD)
ON ri.schedule_item_type = ch.schedule_item_type
AND ci.fiscal_period_key = ch.fiscal_period_key'
SET @sSQL = @sSQL + N'
--Scheduled hours for positions
LEFT OUTER JOIN CXmain.dbo.Position_Period_Schedule pps
ON ri.schedule_item_key = pps.position_key
AND ri.schedule_item_type = 2
AND ci.fiscal_period_key = pps.fiscal_period_key
--Scheduled hours for activities
LEFT OUTER JOIN CXmain.dbo.Associate_Period_Schedule aps
ON ri.schedule_item_key = aps.assoc_schedule_key
AND ri.schedule_item_type = 1
AND ci.fiscal_period_key = aps.fiscal_period_key'
IF @iTEInstalled = 1
SET @sSQL = @sSQL + N'
--Actual hours
LEFT OUTER JOIN CXwork.CXlogin.Actual_Hours_Cache_By_Period ah
ON ci.fiscal_period_key = ah.fiscal_period_key'
--Need this for project maintenance. Project maintenance has an 'other' bucket where actual hours
--that don't belong to a spcofic position fall. In this case, the position_key is 0. If you don't specify
--what project you are on, you get duplicate records when you join with table
--CXlogin.Actual_Hours_Cache_By_Period.
IF @p_iWindow = 2 AND @iTEInstalled = 1 --PROJECT_MAINT
SET @sSQL = @sSQL + N'
AND ah.project_key = @p_iProjectKey'
IF @p_iWindow = 1 AND @iTEInstalled = 1 --SCHEDULE_MAINT
SET @sSQL = @sSQL + N'
AND
(
(
ri.schedule_item_type = 2 --Position
AND ri.schedule_item_key = ah.position_key
AND ri.consultant_key = ah.consultant_key
)
--OR
--(
--ri.schedule_item_type = 3 --Summary activity
--AND ri.schedule_item_key = ah.activity_key
--)
)'
ELSE IF @iTEInstalled = 1
SET @sSQL = @sSQL + N'
AND ri.schedule_item_type = 2
AND ri.schedule_item_key = ah.position_key
AND ri.consultant_key = ah.consultant_key'
--Commented out the statement below for performance. At first glance, it looked like not saving all the zero rows would be more
--efficient. But, it does not appear to be true for three reasons. One, Most of the rows will have some non-zero value anyway. So
--suppressing them won't help that much. Two, if you don't create the rows here, you'll have to create them one call at a time
--in SCHL_PutWorkPeriodSchedule.sp if the user changes any zero rows to non-zero rows. This would be very bad for performance
--because the query below would have to be run separately for each cell. Three, removing all the ISNULL's will make this query
--faster.
SET @sSQL = @sSQL + N'
WHERE
(
aps.hours > 0
OR (ri.schedule_item_type = 2 AND ri.schedule_item_key = 0) --The "other" bucket for Project Maintenance.
OR pps.hours > 0'
IF @p_iPositionJagged = 0
SET @sSQL = @sSQL + N'
OR @p_iWindow = 3 --Don''t use jagged array for Position Maintenance. (This option is only used whent the schedule is being generated.)'
IF @iTEInstalled = 1
SET @sSQL = @sSQL + N'
OR ah.actual_hours IS NOT NULL'
IF @p_iWindow = 1 --SCHEDULE_MAINT
SET @sSQL = @sSQL + N'
OR ch.hours > 0'
SET @sSQL = @sSQL + N'
)
AND ri.active_user_key = @p_iActiveUserKey
AND ri.active_consultant_key = @p_iActiveConsultantKey
AND ci.active_user_key = @p_iActiveUserKey
AND ci.active_consultant_key = @p_iActiveConsultantKey '
IF @p_iAddScheduleItemType = 1
SET @sSQL = @sSQL + N'
AND ri.schedule_item_key = @p_iAssocScheduleKey
AND ri.schedule_item_type = 1'
IF @p_iAddScheduleItemType = 2
SET @sSQL = @sSQL + N'
AND ri.schedule_item_key = @p_iPositionKey
AND ri.schedule_item_type = 2'
EXEC sp_executesql -- Populate Work_Period_Schedule
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @p_iProjectKey int, @p_iAssocScheduleKey int, @p_iPositionKey int, @p_iWindow tinyint ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @p_iProjectKey, @p_iAssocScheduleKey, @p_iPositionKey, @p_iWindow
--Get the actual hours for the 'other bucket'. This includes any actual hours for consultants that have booked T&E
--directly to a position but are not assigned or reserved to the position.
-- get "other" actuals at the project level and the consultant is filling 0 or multiple positions
IF @p_iWindow = 2 AND @p_iAddScheduleItemType = 0 AND @iTEInstalled = 1
BEGIN
UPDATE CXwork.dbo.Work_Period_Schedule
SET
actual_hours =
(
SELECT
ISNULL(SUM(ah.actual_hours), 0)
FROM
CXwork.CXlogin.Actual_Hours_Cache_By_Period ah
WHERE
wps.fiscal_period_key = ah.fiscal_period_key
AND ah.project_key = @p_iProjectKey
AND ah.activity_key = 1
AND ah.position_key = 0
AND NOT EXISTS
(
SELECT
1
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
WHERE
ah.consultant_key = ri.consultant_key
AND ah.position_key = ri.schedule_item_key
AND ri.schedule_item_type = 2
AND ri.schedule_item_key > 0
AND ri.active_user_key = @p_iActiveUserKey
AND ri.active_consultant_key = @p_iActiveConsultantKey
)
)
FROM
CXwork.dbo.Work_Period_Schedule wps WITH (READUNCOMMITTED)
WHERE
wps.active_user_key = @p_iActiveUserKey
AND wps.active_consultant_key = @p_iActiveConsultantKey
AND wps.schedule_item_type = 2
AND wps.schedule_item_key = 0
-- CR 22822 - get "other" actuals at the position level and the consultant is not filling the position
UPDATE CXwork.dbo.Work_Period_Schedule
SET
actual_hours = isnull(actual_hours,0) +
(
SELECT
ISNULL(SUM(ah.actual_hours), 0)
FROM
CXwork.CXlogin.Actual_Hours_Cache_By_Period ah
WHERE
wps.fiscal_period_key = ah.fiscal_period_key
AND ah.project_key = @p_iProjectKey
AND ah.activity_key = 1
AND ah.position_key <> 0
AND NOT EXISTS
(
SELECT
1
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
WHERE
ah.consultant_key = ri.consultant_key
AND ri.schedule_item_type = 2
AND ri.schedule_item_key = ah.position_key
AND ri.active_user_key = @p_iActiveUserKey
AND ri.active_consultant_key = @p_iActiveConsultantKey
)
)
FROM
CXwork.dbo.Work_Period_Schedule wps WITH (READUNCOMMITTED)
WHERE
wps.active_user_key = @p_iActiveUserKey
AND wps.active_consultant_key = @p_iActiveConsultantKey
AND wps.schedule_item_type = 2
AND wps.schedule_item_key = 0
--Delete the 'other' bucket records if no hours were found.
IF NOT EXISTS
(
SELECT
1
FROM
CXwork.dbo.Work_Period_Schedule WITH (READUNCOMMITTED)
WHERE
actual_hours > 0
AND schedule_item_type = 2
AND schedule_item_key = 0
AND active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
)
BEGIN
DELETE CXwork.dbo.Work_Period_Schedule WITH (REPEATABLEREAD)
WHERE
schedule_item_type = 2
AND schedule_item_key = 0
AND active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
DELETE CXwork.dbo.Work_Period_Schedule_Row_Items WITH (REPEATABLEREAD)
WHERE
schedule_item_type = 2
AND schedule_item_key = 0
AND active_user_key = @p_iActiveUserKey
AND active_consultant_key = @p_iActiveConsultantKey
END
END
-- CR 22822 - get actuals from consultant(s) not assigned to this position.
IF @p_iWindow = 3 AND @iTEInstalled = 1
BEGIN
UPDATE CXwork.dbo.Work_Period_Schedule
SET
actual_hours = isnull(actual_hours,0) +
(
SELECT
ISNULL(SUM(ah.actual_hours), 0)
FROM
CXwork.CXlogin.Actual_Hours_Cache_By_Period ah
WHERE
wps.fiscal_period_key = ah.fiscal_period_key
AND ah.project_key = @p_iProjectKey
AND ah.position_key = @p_iPositionKey
AND ah.activity_key = 1
AND NOT EXISTS
(
SELECT
1
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
WHERE
ah.consultant_key = ri.consultant_key
AND ri.schedule_item_type = 2
AND ri.schedule_item_key = ah.position_key
AND ri.active_user_key = @p_iActiveUserKey
AND ri.active_consultant_key = @p_iActiveConsultantKey
)
)
FROM
CXwork.dbo.Work_Period_Schedule wps WITH (READUNCOMMITTED)
WHERE
wps.active_user_key = @p_iActiveUserKey
AND wps.active_consultant_key = @p_iActiveConsultantKey
AND wps.schedule_item_type = 2
AND wps.schedule_item_key = @p_iPositionKey
END
-------------------------------------------------------------------------
-- Populate Work_Daily_Schedule
-------------------------------------------------------------------------
--Get associate daily data.
IF @p_iAddScheduleItemType <> 2
BEGIN
SET @sSQL = N'
INSERT ' + @sTable_WorkDailySchedule + N'--Get associate daily data.
(
active_user_key,
active_consultant_key,
schedule_item_key,
schedule_item_type,
schedule_date,
fiscal_period_key,
hours,
day_of_week,
updated_ind,
day_overallocated_code,
consultant_key,
full_day_hours,
calendar_id
)
SELECT
ri.active_user_key, --active_user_key
ri.active_consultant_key, --active_consultant_key
ri.schedule_item_key, --schedule_item_key
ri.schedule_item_type, --schedule_item_type
ads.schedule_date, --schedule_date
ads.fiscal_period_key, --fiscal_period_key
ISNULL(ads.hours, 1), --schedule_hours: Should only be null when scheduling level = 2
DATEPART(dw, ads.schedule_date), --day_of_week
0, --updated_ind
0, --day_overallocated_code
ri.consultant_key, --consultant_key
ri.full_day_hours, --full_day_hours
ri.calendar_id --calendar_id
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Associate_Daily_Schedule ads
ON ri.schedule_item_key = ads.assoc_schedule_key
WHERE
ri.schedule_item_type = 1
AND ri.active_user_key = @p_iActiveUserKey
AND ri.active_consultant_key = @p_iActiveConsultantKey
'
IF @p_iAddScheduleItemType = 1
SET @sSQL = @sSQL + N'
AND ri.schedule_item_key = @p_iAssocScheduleKey
'
EXEC sp_executesql --Get associate daily data.
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @p_iAssocScheduleKey int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @p_iAssocScheduleKey
END
--Get position daily data.
IF @p_iAddScheduleItemType <> 1
BEGIN
SET @sSQL = N'
INSERT ' + @sTable_WorkDailySchedule + N'--Get position daily data.
(
active_user_key,
active_consultant_key,
schedule_item_key,
schedule_item_type,
schedule_date,
fiscal_period_key,
hours,
day_of_week,
updated_ind,
day_overallocated_code,
consultant_key,
full_day_hours,
calendar_id
)
SELECT
ri.active_user_key, --active_user_key
ri.active_consultant_key, --active_consultant_key
ri.schedule_item_key, --schedule_item_key
ri.schedule_item_type, --schedule_item_type
pds.schedule_date, --schedule_date
pds.fiscal_period_key, --fiscal_period_key
ISNULL(pds.hours, 1), --hours: Should only be null when scheduling level = 2
DATEPART(dw, pds.schedule_date), --day_of_week
0, --updated_ind
0, --day_overallocated_code
ri.consultant_key, --consultant_key
ri.full_day_hours, --full_day_hours
ri.calendar_id --calendar_id
FROM
CXwork.dbo.Work_Period_Schedule_Row_Items ri WITH (READUNCOMMITTED)
INNER JOIN CXmain.dbo.Position_Daily_Schedule pds
ON ri.schedule_item_key = pds.position_key
WHERE
ri.schedule_item_type = 2
AND ri.active_user_key = @p_iActiveUserKey
AND ri.active_consultant_key = @p_iActiveConsultantKey
'
IF @p_iAddScheduleItemType = 2
SET @sSQL = @sSQL + N'
AND ri.schedule_item_key = @p_iPositionKey
'
EXEC sp_executesql --Get position daily data.
@sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @p_iPositionKey int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @p_iPositionKey
END
--Get the company holiday data.
IF @p_iWindow = 1 AND @p_iAddScheduleItemType = 0
BEGIN
SET @sSQL = N'
INSERT ' + @sTable_WorkDailySchedule + N'
(
active_user_key,
active_consultant_key,
schedule_item_key,
schedule_item_type,
schedule_date,
fiscal_period_key,
hours,
day_of_week,
updated_ind,
day_overallocated_code,
consultant_key,
full_day_hours,
calendar_id
)
SELECT
@p_iActiveUserKey, --active_user_key
@p_iActiveConsultantKey, --active_consultant_key
fci.calendar_id, --schedule_item_key
0, --schedule_item_type
rfd.calendar_date, --schedule_date
rfd.fiscal_period_key, --fiscal_period_key
COUNT(*) * @fFullDayHours, --hours
rfd.day_of_week, --day_of_week
0, --updated_ind
0, --day_overallocated_code
@p_iConsultantKey, --consultant_key
NULL, --full_day_hours
NULL --calendar_id
FROM
CXmain.dbo.Family_Calendar_Item fci
INNER JOIN Room_Fiscal_Day rfd
ON fci.item_date = rfd.calendar_date
WHERE
rfd.room = @sRoom
AND fci.calendar_id = @iCalendarID
GROUP BY
rfd.calendar_date, rfd.fiscal_period_key, rfd.day_of_week, fci.calendar_id
--Need to do the group by because there could be more than one holiday on the same date.'
EXEC sp_executesql @sSQL,
N'@p_iActiveUserKey int, @p_iActiveConsultantKey int, @fFullDayHours float, @p_iConsultantKey int, @sRoom char(1), @iCalendarID int ',
@p_iActiveUserKey, @p_iActiveConsultantKey, @fFullDayHours, @p_iConsultantKey, @sRoom, @iCalendarID
END
IF @p_iCalculateTotals = 1
EXEC SCHL_CalculateTotals
@p_iWindow,
@p_iActiveUserKey,
@p_iActiveConsultantKey,
@p_iConsultantKey,
@p_iProjectKey,
@p_iPositionKey
SET NOCOUNT OFF
Please help me.
Thanks
KB.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
January 9, 2009 at 12:15 am
I attached few execution plans.
check these and give me any suggestion.
KB
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
January 9, 2009 at 12:49 am
- loose the dynamic sql ( www.sommarskog.se/dynamic_sql.html )
- why are you using the hints with the delete statements you are executing ??
- don't comment (--) lines in your dynamic sql because they may comment more of your statement than you'd expected.
Can you add
set statistics IO on -- show IO data for all objects
set statistics time on -- show cpu/elaps time for every statement
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 9, 2009 at 1:03 am
The first thing I would suggest is you try to split that up into smaller procedures. First, it'll make it more likely for the thing to get an optimal plan, second it will make things a lot easier when trying to find and fix the bottleneck
Your procedure is over 2000 lines of code and that's going to take hours to go through and look for optimisations.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 4:00 am
Hi ALZDBA,
I added
set statistics IO on
set statistics time on.
Also I removed all the commented lines in dynamic sql.
And i got this output.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
January 9, 2009 at 5:18 am
Like Gail said, because it is a big sproc, with multiple functionallities, it may be hard to "just optimize by reading it" and therefor it is better to split it into "single function" sproc that are called by this current sproc.
With this we aim for plan reuse with optimal execution plans.
Here's a MS doc handling "Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005"
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
It is certainly worth reading.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 9, 2009 at 6:57 am
KSB (1/8/2009)
Hi All,I am new to performance tuning. I wanted to do tune below stored procedure. Please help me that anything needs to be change.
My first question would be.... why do you think this stored procedure needs any tuning at all? Not saying it does or doesn't... why do YOU think it needs to be tuned? Why did you select it as something that needs to be tuned?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply