-- =============================================
-- Author:        Joseph Hurley
-- Create date: 2010/02/18
-- Description:    Return active Clarity Projects and Tasks
-- =============================================
CREATE FUNCTION [dbo].[fnClarityPTRet]
(    
    )
RETURNS TABLE
AS
RETURN
(
    SELECT DISTINCT prob_ctg.zcl_proj_id AS ProjID, prob_ctg.zcl_task_id AS TaskID
            FROM call_req INNER JOIN
            act_log ON call_req.persid = act_log.call_req_id INNER JOIN
            prob_ctg ON call_req.category = prob_ctg.persid
            WHERE     (act_log.zClarityLastSync IS NULL) AND (NOT (prob_ctg.zcl_proj_id IS NULL)) AND (NOT (prob_ctg.zcl_task_id IS NULL))
            AND act_log.time_spent <> 0
            UNION SELECT DISTINCT
            CASE WHEN chg.zcl_proj_id IS NOT NULL THEN chg.zcl_proj_id ELSE chgcat.zcl_proj_id END AS ProjID,
            CASE WHEN chg.zcl_task_id IS NOT NULL THEN chg.zcl_task_id ELSE chgcat.zcl_task_id END AS TaskID
            FROM         chg INNER JOIN
            chgcat ON chg.category = chgcat.code INNER JOIN
            chgalg ON chg.id = chgalg.change_id
            WHERE     (CASE WHEN chg.zcl_proj_id IS NOT NULL THEN chg.zcl_proj_id ELSE chgcat.zcl_proj_id END IS NOT NULL)
            AND (CASE WHEN chg.zcl_task_id IS NOT NULL THEN chg.zcl_task_id ELSE chgcat.zcl_task_id END IS NOT NULL)
            AND (chgalg.zClarityLastSync IS NULL)
            AND chgalg.time_spent <> 0
            UNION SELECT DISTINCT
            CASE WHEN chg_1.zcl_proj_id IS NOT NULL THEN chg_1.zcl_proj_id ELSE chgcat_1.zcl_proj_id END AS ProjID,
            CASE WHEN chg_1.zcl_task_id IS NOT NULL THEN chg_1.zcl_task_id ELSE chgcat_1.zcl_task_id END AS TaskID
            FROM         wf INNER JOIN
            chg AS chg_1 INNER JOIN
            chgcat AS chgcat_1 ON chg_1.category = chgcat_1.code INNER JOIN
            chgalg AS chgalg_1 ON chg_1.id = chgalg_1.change_id ON wf.object_id = chg_1.id
            WHERE     (wf.object_type = N'chg')
                AND (CASE WHEN chg_1.zcl_proj_id IS NOT NULL THEN chg_1.zcl_proj_id ELSE chgcat_1.zcl_proj_id END IS NOT NULL)
                AND (CASE WHEN chg_1.zcl_task_id IS NOT NULL THEN chg_1.zcl_task_id ELSE chgcat_1.zcl_task_id END IS NOT NULL)
                AND wf.actual_duration <> 0  
                AND ((wf.zClarityLastSync IS NULL) OR (ISNULL(wf.actual_duration,0) - ISNULL(zClarityTime,0) <> 0))
)


GO
GRANT SELECT ON  [dbo].[fnClarityPTRet] TO [ServiceDesk]
GO