USP_DATALIST_ORGANIZATIONHIERARCHY

CREATE procedure dbo.USP_DATALIST_ORGANIZATIONHIERARCHY
(
    @CURRENTAPPUSERID uniqueidentifier
)
as
    set nocount on;

    declare @CURRENTDATE datetime = getdate();    

    declare @ORGANIZATIONNAME nvarchar(100) = dbo.UFN_INSTALLATIONINFO_GETINSTALLATIONNAME();
    declare @ORGANIZATIONID uniqueidentifier = 'C090DCA2-F496-4C0D-8775-BD01B92C7948';

    select
        1 [ISROOT],
        @ORGANIZATIONID [ID],
        null [PARENTID],
        0 [SEQUENCE],
        1 [USERHASSITEACCESS],
        1 [ISVACANT],
        @ORGANIZATIONNAME [POSITION],
        '' [POSITIONHOLDER],
        '' [BUSINESSUNIT]

    union all

    select
        0 [ISROOT],
        ORGANIZATIONHIERARCHY.ID,
        coalesce(ORGANIZATIONHIERARCHY.PARENTID, @ORGANIZATIONID),
        ORGANIZATIONHIERARCHY.SEQUENCE,
        dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, ORGANIZATIONPOSITION.SITEID) [USERHASSITEACCESS],
        case when ORGANIZATIONPOSITIONHOLDER.ID is null then 1 else 0 end [ISVACANT],
        ORGANIZATIONPOSITION.NAME [POSITION],
        CONSTITUENT.NAME [POSITIONHOLDER],
        dbo.UFN_BUSINESSUNITCODE_GETDESCRIPTION(ORGANIZATIONPOSITION.BUSINESSUNITCODEID) [BUSINESSUNIT]
    from
        dbo.ORGANIZATIONHIERARCHY
    inner join
        dbo.ORGANIZATIONPOSITION on ORGANIZATIONPOSITION.ID = ORGANIZATIONHIERARCHY.ID
    left outer join
        dbo.ORGANIZATIONPOSITIONHOLDER on ORGANIZATIONPOSITIONHOLDER.POSITIONID = ORGANIZATIONPOSITION.ID
    left outer join
        dbo.CONSTITUENT on CONSTITUENT.ID = ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID
    where
        (
            -- only grab the active position holder

            (ORGANIZATIONPOSITIONHOLDER.DATETO is null) or 
            (
                (ORGANIZATIONPOSITIONHOLDER.DATEFROM is null or ORGANIZATIONPOSITIONHOLDER.DATEFROM < @CURRENTDATE) and
                (@CURRENTDATE <= ORGANIZATIONPOSITIONHOLDER.DATETO)
            )
        )