USP_DATAFORMTEMPLATE_ADD_PURPOSEANDDESIGNATION_PRELOAD

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PURPOSEANDDESIGNATION_PRELOAD (
  @INITIALPARENTDESIGNATIONID uniqueidentifier
  ,@CURRENTAPPUSERID uniqueidentifier
  ,@PARENTDESIGNATIONID uniqueidentifier = null output
  ,@SITEREQUIRED bit = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  ,@PURPOSELOCATION tinyint = null output
  ,@PARENTDESIGNATIONPATH nvarchar(512) = null output
  ,@PARENTDESIGNATIONLOOKUPID nvarchar(412) = null output
  ,@DESIGNATIONHASGLMAPPING bit = null output
  ,@PURPOSEHASGLMAPPING bit = null output
  ,@SYSTEMSEGMENTMAPPINGS xml = null output
  ,@SITEID uniqueidentifier = null output
  )
as
begin
  declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
  declare @FROMTASKGUID uniqueidentifier = '00000000-0000-0000-0000-000000000001';
  declare @FROMPURPOSESEARCHGUID uniqueidentifier = '00000000-0000-0000-0000-000000000002';

  select @SITEREQUIRED = dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID)
    ,@SITEID = dbo.UFN_APPUSER_DEFAULTSITEFORUSER(@CURRENTAPPUSERID)
    ,@BASECURRENCYID = (
      select CURRENCYSET.BASECURRENCYID
      from dbo.CURRENCYSET
      where ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID)
      );

  if nullif(nullif(nullif(@INITIALPARENTDESIGNATIONID, @FROMPURPOSESEARCHGUID), @FROMTASKGUID), @EMPTYGUID) is not null
  begin
    set @PARENTDESIGNATIONID = @INITIALPARENTDESIGNATIONID
    set @PURPOSELOCATION = 1

    select @PARENTDESIGNATIONPATH = [NAME]
      ,@PARENTDESIGNATIONLOOKUPID = case 
        when SYSTEMGENERATED = 0
          then USERID
        else null
        end
    from dbo.DESIGNATION
    where ID = @INITIALPARENTDESIGNATIONID
  end

  if @INITIALPARENTDESIGNATIONID = @FROMTASKGUID or @INITIALPARENTDESIGNATIONID = @FROMPURPOSESEARCHGUID
    set @PARENTDESIGNATIONID = @INITIALPARENTDESIGNATIONID

  if exists (
      select top 1 1
      from dbo.PDACCOUNTSTRUCTURE STRUCT
      where STRUCT.PDACCOUNTSYSTEMID in (
          select T1.ID
          from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) T1
          )
        and (
          STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = '2B1E041E-8FA3-4301-A5DB-E6531E9C3CED'
          or (
            (
              select count(PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID)
              from dbo.PDCOMPOSITESEGMENT
              inner join dbo.PDCOMPOSITESEGMENTTABLELIST on PDCOMPOSITESEGMENT.ID = PDCOMPOSITESEGMENTTABLELIST.PDCOMPOSITESEGMENTID
              inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID
              where PDCOMPOSITESEGMENT.ID = STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                and PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID in (
                  'AEBA8FD5-42B6-4D37-93EB-8916BEC1385A'
                  ,'13BD89C4-47B3-48FF-9044-4F07D0D0664D'
                  )
              ) = 1
            and exists (
              select top 1 1
              from dbo.PDCOMPOSITESEGMENT
              inner join dbo.PDCOMPOSITESEGMENTTABLELIST on PDCOMPOSITESEGMENT.ID = PDCOMPOSITESEGMENTTABLELIST.PDCOMPOSITESEGMENTID
              inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID
              where PDCOMPOSITESEGMENT.ID = STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                and PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID = 'AEBA8FD5-42B6-4D37-93EB-8916BEC1385A'
              )
            )
          )
      )
    set @DESIGNATIONHASGLMAPPING = 1

  if exists (
      select top 1 1
      from dbo.PDACCOUNTSTRUCTURE STRUCT
      where STRUCT.PDACCOUNTSYSTEMID in (
          select T1.ID
          from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) T1
          )
        and (
          STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = '4C4A084F-597C-4CDE-BFB6-F1730397A01A'
          or (
            (
              select count(PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID)
              from dbo.PDCOMPOSITESEGMENT
              inner join dbo.PDCOMPOSITESEGMENTTABLELIST on PDCOMPOSITESEGMENT.ID = PDCOMPOSITESEGMENTTABLELIST.PDCOMPOSITESEGMENTID
              inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID
              where PDCOMPOSITESEGMENT.ID = STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                and PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID in (
                  'AEBA8FD5-42B6-4D37-93EB-8916BEC1385A'
                  ,'13BD89C4-47B3-48FF-9044-4F07D0D0664D'
                  )
              ) = 1
            and exists (
              select top 1 1
              from dbo.PDCOMPOSITESEGMENT
              inner join dbo.PDCOMPOSITESEGMENTTABLELIST on PDCOMPOSITESEGMENT.ID = PDCOMPOSITESEGMENTTABLELIST.PDCOMPOSITESEGMENTID
              inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID
              where PDCOMPOSITESEGMENT.ID = STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                and PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID = '13BD89C4-47B3-48FF-9044-4F07D0D0664D'
              )
            )
          )
      )
    set @PURPOSEHASGLMAPPING = 1
  set @SYSTEMSEGMENTMAPPINGS = (
      select GLSYS.ID PDACCOUNTSYSTEMID
        ,GLSYS.name PDACCOUNTSYSTEMNAME
        ,(
          select GLSYS.ID PDACCOUNTSYSTEMID
            ,GLSYS.name PDACCOUNTSYSTEMNAME
            ,STRUCT.ID PDACCOUNTSTRUCTUREID
            ,STRUCT.DESCRIPTION + ':' PDACCOUNTSTRUCTURENAME
            ,STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
            ,cast(null as uniqueidentifier) PDACCOUNTSEGMENTVALUEID
          from dbo.PDACCOUNTSTRUCTURE STRUCT
          where STRUCT.PDACCOUNTSYSTEMID = GLSYS.ID
            and PDACCOUNTTABLESAVAILABLEFORSEGMENTID in (
              '2B1E041E-8FA3-4301-A5DB-E6531E9C3CED'
              ,'4C4A084F-597C-4CDE-BFB6-F1730397A01A'
              )
          order by STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
            ,STRUCT.SEGMENTSEQUENCE
          for xml raw('ITEM')
            ,type
            ,elements
            ,root('SEGMENTMAPPINGS')
            ,binary BASE64
          )
        ,(
          select STRUCT.ID PDACCOUNTSTRUCTUREID
            ,STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID PDCOMPOSITESEGMENTID
            ,STRUCT.DESCRIPTION + ':' PDACCOUNTSTRUCTURENAME
            ,TABLE1.PDACCOUNTTABLESAVAILABLEFORSEGMENTID TABLE1ID
            ,(
              select FRIENDLYTABLENAME
              from dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT as a
              inner join dbo.PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
              where SEQUENCE = 1
                and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
              ) TABLE1FRIENDLYNAME
            ,dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(TABLE1.PDACCOUNTTABLESAVAILABLEFORSEGMENTID) TABLE1SHOWSIMPLE
            ,TABLE2.PDACCOUNTTABLESAVAILABLEFORSEGMENTID TABLE2ID
            ,(
              select FRIENDLYTABLENAME
              from dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT as a
              inner join dbo.PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
              where SEQUENCE = 2
                and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
              ) TABLE2FRIENDLYNAME
            ,dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(TABLE2.PDACCOUNTTABLESAVAILABLEFORSEGMENTID) TABLE2SHOWSIMPLE
            ,TABLE3.PDACCOUNTTABLESAVAILABLEFORSEGMENTID TABLE3ID
            ,isnull((
                select FRIENDLYTABLENAME
                from dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT as a
                inner join dbo.PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                where SEQUENCE = 3
                  and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
                ), 'EMPTY') TABLE3FRIENDLYNAME
            ,dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(TABLE3.PDACCOUNTTABLESAVAILABLEFORSEGMENTID) TABLE3SHOWSIMPLE
            ,TABLE4.PDACCOUNTTABLESAVAILABLEFORSEGMENTID TABLE4ID
            ,isnull((
                select FRIENDLYTABLENAME
                from dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT as a
                inner join dbo.PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                where SEQUENCE = 4
                  and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
                ), 'EMPTY') TABLE4FRIENDLYNAME
            ,dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(TABLE4.PDACCOUNTTABLESAVAILABLEFORSEGMENTID) TABLE4SHOWSIMPLE
          from dbo.PDACCOUNTSTRUCTURE STRUCT
          inner join dbo.PDCOMPOSITESEGMENT on STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDCOMPOSITESEGMENT.ID
          left join dbo.PDCOMPOSITESEGMENTTABLELIST TABLE1 on TABLE1.SEQUENCE = 1
            and TABLE1.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
          left join dbo.PDCOMPOSITESEGMENTTABLELIST TABLE2 on TABLE2.SEQUENCE = 2
            and TABLE2.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
          left join dbo.PDCOMPOSITESEGMENTTABLELIST TABLE3 on TABLE3.SEQUENCE = 3
            and TABLE3.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
          left join dbo.PDCOMPOSITESEGMENTTABLELIST TABLE4 on TABLE4.SEQUENCE = 4
            and TABLE4.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENT.ID
          where STRUCT.PDACCOUNTSYSTEMID = GLSYS.ID
            and (
              select count(PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID)
              from dbo.PDCOMPOSITESEGMENT
              inner join dbo.PDCOMPOSITESEGMENTTABLELIST on PDCOMPOSITESEGMENT.ID = PDCOMPOSITESEGMENTTABLELIST.PDCOMPOSITESEGMENTID
              inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID
              where PDCOMPOSITESEGMENT.ID = STRUCT.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                and PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID in (
                  'AEBA8FD5-42B6-4D37-93EB-8916BEC1385A'
                  ,'13BD89C4-47B3-48FF-9044-4F07D0D0664D'
                  )
              ) = 1
          order by STRUCT.ID
          for xml raw('ITEM')
            ,type
            ,elements
            ,root('COMPOSITESEGMENTMAPPINGS')
            ,binary BASE64
          )
      from dbo.PDACCOUNTSYSTEM GLSYS
      where GLSYS.ID in (
          select T1.ID
          from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) T1
          )
      order by GLSYS.name
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('SYSTEMSEGMENTMAPPINGS')
        ,binary BASE64
      )
  set @SYSTEMSEGMENTMAPPINGS = isnull(@SYSTEMSEGMENTMAPPINGS, '<SYSTEMSEGMENTMAPPINGS></SYSTEMSEGMENTMAPPINGS>')
end