UFN_SMARTQUERY_ORGANIZATIONPOSITIONS

Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@ORGANIZATIONHIERARCHY	xml	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@MAXROWS	int	IN
Definition

Copy


                create function dbo.UFN_SMARTQUERY_ORGANIZATIONPOSITIONS
                (
                    @ORGANIZATIONHIERARCHY xml = null,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @MAXROWS integer = null
                ) 
                returns table 
                as 
                return
                    (
                        select 
                            ORGANIZATIONPOSITIONHOLDER.ID [ID], 
                            ORGANIZATIONPOSITION.NAME [POSITIONTITLE], 
                            coalesce(CONSTITUENT.NAME, '<Vacant>') [CONSTITUENTNAME], 
                            ORGANIZATIONPOSITIONHOLDER.DATEFROM [DATEFROM], 
                            ORGANIZATIONPOSITIONHOLDER.DATETO [DATETO],
                            BUSINESSUNITCODE.DESCRIPTION [BUSINESSUNIT]
                        from
                            dbo.UFN_ORGANIZATIONHIERARCHY_SELECTEDNODES(@ORGANIZATIONHIERARCHY, @CURRENTAPPUSERID) POSITIONS
                        inner join 
                            dbo.ORGANIZATIONPOSITION on ORGANIZATIONPOSITION.ID = POSITIONS.ID
                        left outer join 
                            dbo.ORGANIZATIONPOSITIONHOLDER on ORGANIZATIONPOSITIONHOLDER.POSITIONID = ORGANIZATIONPOSITION.ID
                        left outer join 
                            dbo.CONSTITUENT on ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = CONSTITUENT.ID
                        left outer join
                            dbo.BUSINESSUNITCODE on BUSINESSUNITCODE.ID = ORGANIZATIONPOSITION.BUSINESSUNITCODEID
                    )