USP_DATAFORMTEMPLATE_EDITLOAD_ORGANIZATIONHIERARCHY

The load procedure used by the edit dataform template "Organization Hierarchy Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ORGANIZATIONNAME	nvarchar(100)	INOUT	Name
@ORGANIZATIONHIERARCHY	xml	INOUT	Hierarchy
@SITEREQUIRED	bit	INOUT	Site Required
@DEFAULTSITEID	uniqueidentifier	INOUT	Site

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_ORGANIZATIONHIERARCHY
(
  @DATALOADED bit = 0 output,
  @CURRENTAPPUSERID uniqueidentifier,
  @ORGANIZATIONNAME nvarchar(100) = null output,
  @ORGANIZATIONHIERARCHY xml = null output,
  @SITEREQUIRED bit = null output,
  @DEFAULTSITEID uniqueidentifier = null output
)
as
begin
  set nocount on;

  set @DATALOADED = 1;
  set @ORGANIZATIONNAME = dbo.UFN_INSTALLATIONINFO_GETINSTALLATIONNAME();
  declare @CURRENTDATE datetime = getdate();    

  set @ORGANIZATIONHIERARCHY =
  (
    select
      ORGANIZATIONHIERARCHY.[ID],
      ORGANIZATIONHIERARCHY.PARENTID,
      ORGANIZATIONHIERARCHY.[SEQUENCE],
      ORGANIZATIONPOSITION.NAME,
      ORGANIZATIONPOSITION.SITEID,
      ORGANIZATIONPOSITION.BUSINESSUNITCODEID,
      (
        select
          ORGANIZATIONPOSITIONHOLDER.ID,
          ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID,
          ORGANIZATIONPOSITIONHOLDER.DATEFROM,
          ORGANIZATIONPOSITIONHOLDER.DATETO,
          case 
            when 
                ORGANIZATIONPOSITIONHOLDER.DATETO is null or 
                (
                    (ORGANIZATIONPOSITIONHOLDER.DATEFROM is null or ORGANIZATIONPOSITIONHOLDER.DATEFROM < @CURRENTDATE) and
                    (@CURRENTDATE <= ORGANIZATIONPOSITIONHOLDER.DATETO)
                )
            then cast(1 as bit) 
            else cast(0 as bit)
          end ISACTIVE
        from
          dbo.ORGANIZATIONPOSITIONHOLDER
        where
          ORGANIZATIONPOSITIONHOLDER.POSITIONID = ORGANIZATIONPOSITION.ID
        for xml raw('ITEM'), type, elements, root('POSITIONHOLDERS'), BINARY BASE64
      ),
      dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, ORGANIZATIONPOSITION.SITEID) as USERHASSITEACCESS
    from
      dbo.ORGANIZATIONHIERARCHY
    left outer join
      dbo.ORGANIZATIONPOSITION on ORGANIZATIONPOSITION.ID = ORGANIZATIONHIERARCHY.ID
    for xml raw('ITEM'), type, elements, root('ORGANIZATIONHIERARCHY'), BINARY BASE64
  );

  set @SITEREQUIRED = dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID);
  set @DEFAULTSITEID = dbo.UFN_APPUSER_DEFAULTSITEFORUSER(@CURRENTAPPUSERID);

  return 0;

end