UFN_CLIENTUSERS_TASKS

create function dbo.UFN_CLIENTUSERS_TASKS
(
    @CLIENTUSERSID int,
    @TASKGUID uniqueidentifier = null
)
returns table
as return
    --TWG 05/15/2013 inlining this function

    with
    TASK_CTE (
        TASKGUID,
        CLIENTSITESID,
        ISSITETASK,
        APPLYTOCHILDREN
    ) as (
        select SGT.TASKGUID,
               SGCRCS.CLIENTSITESID,
               SG.SITETASKSGROUP as ISSITETASK,
               case when sum(cast(SGCRCS.APPLYTOCHILDREN as int)) > 0 then 1 else 0 end as APPLYTOCHILDREN
        from dbo.fnUserRoles(@CLIENTUSERSID, 1) UR
        inner join CMSSECURITYGROUPCLIENTROLECLIENTSITE SGCRCS
        on UR.CLIENTROLESID = SGCRCS.CLIENTROLESID
        inner join CMSSECURITYGROUP SG
        on SG.ID = SGCRCS.CMSSECURITYGROUPID
        inner join CMSSECURITYGROUPTASK SGT
        on SGT.CMSSECURITYGROUPID = SG.ID
        where (@TASKGUID is null or SGT.TASKGUID = @TASKGUID)
        group by SGT.TASKGUID, SGCRCS.CLIENTSITESID, SG.SITETASKSGROUP
    ),
    CHILDTASK_CTE (
        TASKGUID,
        CLIENTSITEID,
        ISSITETASK
    ) as (
        select distinct
            TASKS.TASKGUID,
            CHILD.ID CLIENTSITEID,
            TASKS.ISSITETASK
        from (
            select
                TASKGUID,
                CLIENTSITESID,
                ISSITETASK,
                APPLYTOCHILDREN
            from TASK_CTE
            where CLIENTSITESID is not null and APPLYTOCHILDREN = 1) TASKS        
        cross apply dbo.UFN_CLIENTSITE_GETCHILDIDS(TASKS.CLIENTSITESID) CHILD
    )
    select
        TASK.TASKGUID,
        TASK.CLIENTSITESID,
        TASK.ISSITETASK,
        TASK.APPLYTOCHILDREN
    from
        TASK_CTE TASK
    union all
    select
        CHILDTASK.TASKGUID,
        CHILDTASK.CLIENTSITEID,
        CHILDTASK.ISSITETASK,
        1
    from
        CHILDTASK_CTE CHILDTASK
    where
        not exists (select 1 from TASK_CTE TASK where TASK.CLIENTSITESID = CHILDTASK.CLIENTSITEID and TASK.TASKGUID = CHILDTASK.TASKGUID);