USP_DATAFORMTEMPLATE_ADD_PURPOSEANDDESIGNATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PURPOSEANDDESIGNATION (
  @ID uniqueidentifier = null output
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@INITIALPARENTDESIGNATIONID uniqueidentifier
  ,@PARENTDESIGNATIONID uniqueidentifier = null
  ,@PURPOSEID uniqueidentifier = null
  ,@DESIGNATIONLOOKUPID nvarchar(450) = null
  ,@DESIGNATIONVANITYNAME nvarchar(512) = null
  ,@DESIGNATIONREPORT1CODEID uniqueidentifier = null
  ,@DESIGNATIONREPORT2CODEID uniqueidentifier = null
  ,@VSECATEGORYID uniqueidentifier = null
  ,@CAMPAIGNID uniqueidentifier = null
  ,@STARTDATE datetime = null
  ,@ENDDATE datetime = null
  ,@DESIGNATIONUSECODEID uniqueidentifier = null
  ,@VSESUBCATEGORYID uniqueidentifier = null
  ,@ISREVENUEDESIGNATION bit = 0
  ,@PURPOSENAME nvarchar(100) = ''
  ,@PURPOSEDESCRIPTION nvarchar(255) = ''
  ,@PURPOSECATEGORYCODEID uniqueidentifier = null
  ,@PURPOSETYPEID uniqueidentifier = null
  ,@STEWARDSHIPPACKAGEPROCESSID uniqueidentifier = null
  ,@ADMINISTRATORID uniqueidentifier = null
  ,@PURPOSELOOKUPID nvarchar(100) = null
  ,@PURPOSEDESIGNATIONREPORT1CODEID uniqueidentifier = null
  ,@PURPOSEDESIGNATIONREPORT2CODEID uniqueidentifier = null
  ,@SITEID uniqueidentifier = null
  ,@CURRENTAPPUSERID uniqueidentifier
  ,@PURPOSEVANITYNAME nvarchar(512) = ''
  ,@TIMEPERIODCODE tinyint = null
  ,@ISENDOWED bit = null
  ,@ISFULLYFUNDED bit = null
  ,@ISINCOMETOPRINCIPAL bit = null
  ,@INCOMETOPRINCIPALPERCENT decimal(5, 2) = null
  ,@STATEMENTWORDING nvarchar(1024) = null
  ,@ENDOWMENTMINAMOUNT money = 0
  ,@ENDOWMENTTARGETDATE date = null
  ,@ENDOWMENTMINAMOUNTDATEMET date = null
  ,@PURPOSELOCATION tinyint = 0
  ,@SYSTEMSEGMENTMAPPINGS xml = null
  ,@ISSYSTEMGENERATEDDESIGNATION bit = 0
  )
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @ISACTIVE bit = 1;
  declare @EXISTINGPURPOSE bit = 0;

  if @PURPOSEID is not null
    set @EXISTINGPURPOSE = 1;

  if @ID is null
    set @ID = newid();

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  set @ISSYSTEMGENERATEDDESIGNATION = coalesce(@ISSYSTEMGENERATEDDESIGNATION, 0);
  set @CURRENTDATE = getdate();

  begin try
    if @EXISTINGPURPOSE = 0
    begin
      exec dbo.USP_DATAFORMTEMPLATE_ADD_DESIGNATIONLEVEL @ID = @PURPOSEID output
        ,@CHANGEAGENTID = @CHANGEAGENTID
        ,@NAME = @PURPOSENAME
        ,@DESCRIPTION = @PURPOSEDESCRIPTION
        ,@DESIGNATIONLEVELCATEGORYCODEID = @PURPOSECATEGORYCODEID
        ,@DESIGNATIONLEVELTYPEID = @PURPOSETYPEID
        ,@STEWARDSHIPPACKAGEPROCESSID = @STEWARDSHIPPACKAGEPROCESSID
        ,@ADMINISTRATORID = @ADMINISTRATORID
        ,@LOOKUPID = @PURPOSELOOKUPID
        ,@DESIGNATIONREPORTCODE1ID = @PURPOSEDESIGNATIONREPORT1CODEID
        ,@DESIGNATIONREPORTCODE2ID = @PURPOSEDESIGNATIONREPORT2CODEID
        ,@SITEID = @SITEID
        ,@CURRENTAPPUSERID = @CURRENTAPPUSERID
        ,@VANITYNAME = @PURPOSEVANITYNAME
        ,@TIMEPERIODCODE = @TIMEPERIODCODE
        ,@ISENDOWED = @ISENDOWED
        ,@ISFULLYFUNDED = @ISFULLYFUNDED
        ,@ISINCOMETOPRINCIPAL = @ISINCOMETOPRINCIPAL
        ,@INCOMETOPRINCIPALPERCENT = @INCOMETOPRINCIPALPERCENT
        ,@STATEMENTWORDING = @STATEMENTWORDING
        ,@ENDOWMENTMINAMOUNT = @ENDOWMENTMINAMOUNT
        ,@ENDOWMENTTARGETDATE = @ENDOWMENTTARGETDATE
        ,@ENDOWMENTMINAMOUNTDATEMET = @ENDOWMENTMINAMOUNTDATEMET
    end

    declare @DESIGNATIONLEVEL1ID uniqueidentifier;
    declare @DESIGNATIONLEVEL2ID uniqueidentifier;
    declare @DESIGNATIONLEVEL3ID uniqueidentifier;
    declare @DESIGNATIONLEVEL4ID uniqueidentifier;
    declare @DESIGNATIONLEVEL5ID uniqueidentifier;

    if @PARENTDESIGNATIONID is not null
      select @DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL1ID
        ,@DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL2ID
        ,@DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL3ID
        ,@DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL4ID
        ,@DESIGNATIONLEVEL5ID = DESIGNATIONLEVEL5ID
      from dbo.DESIGNATION
      where DESIGNATION.ID = @PARENTDESIGNATIONID;

    if @DESIGNATIONVANITYNAME is null
      set @DESIGNATIONVANITYNAME = '';

    if @DESIGNATIONLEVEL1ID is null
      set @DESIGNATIONLEVEL1ID = @PURPOSEID
    else
      if @DESIGNATIONLEVEL2ID is null
        set @DESIGNATIONLEVEL2ID = @PURPOSEID
      else
        if @DESIGNATIONLEVEL3ID is null
          set @DESIGNATIONLEVEL3ID = @PURPOSEID
        else
          if @DESIGNATIONLEVEL4ID is null
            set @DESIGNATIONLEVEL4ID = @PURPOSEID
          else
            if @DESIGNATIONLEVEL5ID is null
              set @DESIGNATIONLEVEL5ID = @PURPOSEID
            else
              raiserror (
                  'ERR_INVALIDDESIGNATIONLEVEL'
                  ,13
                  ,1
                  );

    if len(@DESIGNATIONVANITYNAME) = 0
      raiserror (
          'ERR_VANITYNAME_REQUIRED'
          ,13
          ,1
          );

    declare @BASECURRENCYID uniqueidentifier;

    select @BASECURRENCYID = BASECURRENCYID
    from dbo.DESIGNATIONLEVEL
    where ID = @PURPOSEID

    if @ISSYSTEMGENERATEDDESIGNATION = 0
    begin
      insert into dbo.DESIGNATION (
        ID
        ,DESIGNATIONLEVEL1ID
        ,DESIGNATIONLEVEL2ID
        ,DESIGNATIONLEVEL3ID
        ,DESIGNATIONLEVEL4ID
        ,DESIGNATIONLEVEL5ID
        ,USERID
        ,VANITYNAME
        ,DESIGNATIONREPORT1CODEID
        ,DESIGNATIONREPORT2CODEID
        ,VSECATEGORYID
        ,STARTDATE
        ,ENDDATE
        ,DESIGNATIONUSECODEID
        ,VSESUBCATEGORYID
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
        ,ISREVENUEDESIGNATION
        ,BASECURRENCYID
        ,ISACTIVE
        )
      values (
        @ID
        ,@DESIGNATIONLEVEL1ID
        ,@DESIGNATIONLEVEL2ID
        ,@DESIGNATIONLEVEL3ID
        ,@DESIGNATIONLEVEL4ID
        ,@DESIGNATIONLEVEL5ID
        ,@DESIGNATIONLOOKUPID
        ,@DESIGNATIONVANITYNAME
        ,@DESIGNATIONREPORT1CODEID
        ,@DESIGNATIONREPORT2CODEID
        ,@VSECATEGORYID
        ,@STARTDATE
        ,@ENDDATE
        ,@DESIGNATIONUSECODEID
        ,@VSESUBCATEGORYID
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE
        ,@ISREVENUEDESIGNATION
        ,@BASECURRENCYID
        ,@ISACTIVE
        );

      if @CAMPAIGNID is not null
        insert into dbo.DESIGNATIONCAMPAIGN (
          ID
          ,DESIGNATIONID
          ,CAMPAIGNID
          ,ADDEDBYID
          ,CHANGEDBYID
          ,DATEADDED
          ,DATECHANGED
          )
        values (
          newid()
          ,@ID
          ,@CAMPAIGNID
          ,@CHANGEAGENTID
          ,@CHANGEAGENTID
          ,@CURRENTDATE
          ,@CURRENTDATE
          );
    end
    else
    begin
      --changed constraint to allow system generated active designations, so the following should not be necessary

      --if exists (

      --    select top 1 1

      --    from dbo.DESIGNATIONLEVEL

      --    where ISACCEPTINGFUNDS = 0

      --      and ID in (

      --        @DESIGNATIONLEVEL1ID

      --        ,@DESIGNATIONLEVEL2ID

      --        ,@DESIGNATIONLEVEL3ID

      --        ,@DESIGNATIONLEVEL4ID

      --        ,@DESIGNATIONLEVEL5ID

      --        )

      --    )

      --  set @ISACTIVE = 0

      insert into dbo.DESIGNATION (
        ID
        ,DESIGNATIONLEVEL1ID
        ,DESIGNATIONLEVEL2ID
        ,DESIGNATIONLEVEL3ID
        ,DESIGNATIONLEVEL4ID
        ,DESIGNATIONLEVEL5ID
        ,USERID
        ,VANITYNAME
        ,DESIGNATIONREPORT1CODEID
        ,DESIGNATIONREPORT2CODEID
        ,VSECATEGORYID
        ,STARTDATE
        ,ENDDATE
        ,DESIGNATIONUSECODEID
        ,VSESUBCATEGORYID
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
   ,ISREVENUEDESIGNATION
        ,BASECURRENCYID
        ,SYSTEMGENERATED
        ,ISACTIVE
        )
      values (
        @ID
        ,@DESIGNATIONLEVEL1ID
        ,@DESIGNATIONLEVEL2ID
        ,@DESIGNATIONLEVEL3ID
        ,@DESIGNATIONLEVEL4ID
        ,@DESIGNATIONLEVEL5ID
        ,cast(@ID as nvarchar(512))
        ,'System Generated Designation'
        ,null
        ,null
        ,null
        ,null
        ,null
        ,null
        ,null
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE
        ,0
        ,@BASECURRENCYID
        ,1
        ,@ISACTIVE
        );
    end

    declare @DESIGNATIONHASGLMAPPING bit = 0;
    declare @PURPOSEHASGLMAPPING bit = 0;
    declare @DESIGNATIONTABLEID uniqueidentifier = '2B1E041E-8FA3-4301-A5DB-E6531E9C3CED';
    declare @PURPOSETABLEID uniqueidentifier = '4C4A084F-597C-4CDE-BFB6-F1730397A01A';
    declare @DEFAULT_GUID uniqueidentifier = '99999999-9999-9999-9999-999999999999';
    declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    declare @FROMTASKGUID uniqueidentifier = '00000000-0000-0000-0000-000000000001';
    declare @FROMPURPOSESEARCHGUID uniqueidentifier = '00000000-0000-0000-0000-000000000002';

    if @SYSTEMSEGMENTMAPPINGS is not null
    begin
      if @ISSYSTEMGENERATEDDESIGNATION = 0
        and 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 @EXISTINGPURPOSE = 0
        and 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
    end

    if @DESIGNATIONHASGLMAPPING = 0
      and @PURPOSEHASGLMAPPING = 0
      set @SYSTEMSEGMENTMAPPINGS = null

    if @SYSTEMSEGMENTMAPPINGS is not null
    begin
      /* Insert/Update the simple mappings for all GL systems */
      merge dbo.PDACCOUNTSEGMENTMAPPING as Target
      using (
        select case 
            when T.c.value('(PDACCOUNTTABLESAVAILABLEFORSEGMENTID)[1]', 'uniqueidentifier') = @DESIGNATIONTABLEID
              then @ID
            else @PURPOSEID
            end LONGDESCRIPTIONID
          ,T.c.value('(PDACCOUNTSTRUCTUREID)[1]', 'uniqueidentifier') as PDACCOUNTSTRUCTUREID
          ,T.c.value('(PDACCOUNTSEGMENTVALUEID)[1]', 'uniqueidentifier') as PDACCOUNTSEGMENTVALUEID
          ,@CURRENTDATE as CURRENTDATE
          ,@CHANGEAGENTID as CHANGEAGENTID
        from @SYSTEMSEGMENTMAPPINGS.nodes('/SYSTEMSEGMENTMAPPINGS/ITEM/SEGMENTMAPPINGS/ITEM') T(c)
        ) as Source
        on (Target.LongDescriptionID = Source.LongDescriptionID)
      when matched
        then
          update
          set Target.PDACCOUNTSEGMENTVALUEID = Source.PDACCOUNTSEGMENTVALUEID
            ,Target.CHANGEDBYID = Source.CHANGEAGENTID
            ,Target.DATECHANGED = Source.CURRENTDATE
      when not matched by Target
        then
          insert (
            PDACCOUNTSTRUCTUREID
            ,LONGDESCRIPTIONID
            ,PDACCOUNTSEGMENTVALUEID
            ,ISDEFAULT
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
            )
          values (
            Source.PDACCOUNTSTRUCTUREID
            ,Source.LONGDESCRIPTIONID
            ,Source.PDACCOUNTSEGMENTVALUEID
            ,0
            ,Source.CHANGEAGENTID
            ,Source.CHANGEAGENTID
            ,Source.CURRENTDATE
            ,Source.CURRENTDATE
            );

      /* Insert/Update composite segments segment values for all account systems */
      declare @TMAPPINGS as table (
        ID uniqueidentifier primary key
        ,ID1 uniqueidentifier
        ,ID2 uniqueidentifier
        ,ID3 uniqueidentifier
        ,ID4 uniqueidentifier
        ,MAPPINGNAME nvarchar(50)
        ,PDACCOUNTSEGMENTVALUEID uniqueidentifier
        ,PDCOMPOSITESEGMENTID uniqueidentifier
        ,PDACCOUNTSTRUCTUREID uniqueidentifier
        );

      insert into @TMAPPINGS (
        ID
        ,MAPPINGNAME
        ,PDCOMPOSITESEGMENTID
        ,PDACCOUNTSTRUCTUREID
        ,PDACCOUNTSEGMENTVALUEID
        ,ID1
        ,ID2
        ,ID3
        ,ID4
        )
      select newid()
        ,c.value('(MAPPINGNAME)[1]', 'nvarchar(50)') as MAPPINGNAME
        ,c.value('(../../PDCOMPOSITESEGMENTID)[1]', 'uniqueidentifier') as PDCOMPOSITESEGMENTID
        ,c.value('../../PDACCOUNTSTRUCTUREID[1]', 'uniqueidentifier') as PDACCOUNTSTRUCTUREID
        ,c.value('(PDACCOUNTSEGMENTVALUEID)[1]', 'uniqueidentifier') as PDACCOUNTSEGMENTVALUEID
        ,case COALESCE(T.c.value('(../../TABLE1ID)[1]', 'uniqueidentifier'), @EMPTYGUID)
          when @EMPTYGUID
            then null
          when @DESIGNATIONTABLEID
            then @ID
          when @PURPOSETABLEID
            then @PURPOSEID
          else case 
              when T.c.value('(../../TABLE1SHOWSIMPLE)[1]', 'bit') = 1
                then T.c.value('(TABLE1SIMPLEVALUE)[1]', 'uniqueidentifier')
              else T.c.value('(TABLE1SEARCHVALUE)[1]', 'uniqueidentifier')
              end
          end ID1
        ,case COALESCE(T.c.value('(../../TABLE2ID)[1]', 'uniqueidentifier'), @EMPTYGUID)
          when @EMPTYGUID
            then null
          when @DESIGNATIONTABLEID
            then @ID
          when @PURPOSETABLEID
            then @PURPOSEID
          else case 
              when T.c.value('(../../TABLE2SHOWSIMPLE)[1]', 'bit') = 1
                then T.c.value('(TABLE2SIMPLEVALUE)[1]', 'uniqueidentifier')
              else T.c.value('(TABLE2SEARCHVALUE)[1]', 'uniqueidentifier')
              end
          end ID2
        ,case COALESCE(T.c.value('(../../TABLE3ID)[1]', 'uniqueidentifier'), @EMPTYGUID)
          when @EMPTYGUID
            then null
          when @DESIGNATIONTABLEID
            then @ID
          when @PURPOSETABLEID
            then @PURPOSEID
          else case 
              when T.c.value('(../../TABLE3SHOWSIMPLE)[1]', 'bit') = 1
                then T.c.value('(TABLE3SIMPLEVALUE)[1]', 'uniqueidentifier')
              else T.c.value('(TABLE3SEARCHVALUE)[1]', 'uniqueidentifier')
              end
          end ID3
        ,case COALESCE(T.c.value('(../../TABLE4ID)[1]', 'uniqueidentifier'), @EMPTYGUID)
          when @EMPTYGUID
            then null
          when @DESIGNATIONTABLEID
            then @ID
          when @PURPOSETABLEID
            then @PURPOSEID
          else case 
              when T.c.value('(../../TABLE4SHOWSIMPLE)[1]', 'bit') = 1
                then T.c.value('(TABLE4SIMPLEVALUE)[1]', 'uniqueidentifier')
              else T.c.value('(TABLE4SEARCHVALUE)[1]', 'uniqueidentifier')
              end
          end ID4
      from @SYSTEMSEGMENTMAPPINGS.nodes('/SYSTEMSEGMENTMAPPINGS/ITEM/COMPOSITESEGMENTMAPPINGS/ITEM/COMPOSITESEGMENTVALUES/ITEM') T(c)

      merge dbo.PDCOMPOSITESEGMENTMAPPING as target
      using (
        select ID
          ,MAPPINGNAME
          ,PDCOMPOSITESEGMENTID
          ,PDACCOUNTSTRUCTUREID
          ,PDACCOUNTSEGMENTVALUEID
          ,ID1
          ,ID2
          ,ID3
          ,ID4
        from @TMAPPINGS
        ) as source
        on (target.ID = source.ID)
      when matched
        then
          update
          set target.[NAME] = source.MAPPINGNAME
            ,target.PDACCOUNTSEGMENTVALUEID = source.PDACCOUNTSEGMENTVALUEID
            ,target.CHANGEDBYID = @CHANGEAGENTID
            ,target.DATECHANGED = @CURRENTDATE
            ,target.COMPOSITESEGMENTKEY = ''
      when not matched by target
        then
          insert (
            ID
            ,[NAME]
            ,PDACCOUNTSEGMENTVALUEID
            ,PDCOMPOSITESEGMENTID
            ,PDACCOUNTSTRUCTUREID
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
            )
          values (
            source.ID
            ,source.MAPPINGNAME
            ,source.PDACCOUNTSEGMENTVALUEID
            ,source.PDCOMPOSITESEGMENTID
            ,source.PDACCOUNTSTRUCTUREID
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CURRENTDATE
            ,@CURRENTDATE
            );

      /* we must delete and re-add all entries because it attempts to validate uniqueness, and if we didn't delete them we would have a timing issue */
      delete
      from dbo.PDCOMPOSITESEGMENTMAPPINGENTRY
      from dbo.PDCOMPOSITESEGMENTMAPPINGENTRY ENTRIES
      inner join @TMAPPINGS NEWROWS on ENTRIES.PDCOMPOSITESEGMENTMAPPINGID = NEWROWS.ID

      /* Insert/Update composite segments other table values for all account systems */
      merge dbo.PDCOMPOSITESEGMENTMAPPINGENTRY as target
      using (
        select distinct MAPPINGID
          ,CSTL.ID as TABLELISTID
          ,LONGDESCRIPTIONID
          ,case 
            when LONGDESCRIPTIONID = @DEFAULT_GUID
              then 1
            else 0
            end as ISDEFAULT
        from (
          select ID as MAPPINGID
            ,CAST(SUBSTRING(COL, 3, 1) as int) as SEQUENCE
            ,LDID as LONGDESCRIPTIONID
            ,PDCOMPOSITESEGMENTID
          from (
            select ID
              ,ID1
              ,ID2
              ,ID3
              ,ID4
              ,PDCOMPOSITESEGMENTID
            from @TMAPPINGS
            ) p
          UNPIVOT(LDID for COL in (
                ID1
                ,ID2
                ,ID3
                ,ID4
                )) as unpvt
          ) as unpvt
        inner join dbo.PDCOMPOSITESEGMENTTABLELIST as CSTL on unpvt.SEQUENCE = CSTL.SEQUENCE
          and CSTL.PDCOMPOSITESEGMENTID = unpvt.PDCOMPOSITESEGMENTID
        ) as source
        on (
            target.PDCOMPOSITESEGMENTMAPPINGID = source.MAPPINGID
            and target.PDCOMPOSITESEGMENTTABLELISTID = source.TABLELISTID
            )
      when matched
        and not source.LONGDESCRIPTIONID is null
        then
          update
          set target.LONGDESCRIPTIONID = source.LONGDESCRIPTIONID
            ,target.ISDEFAULT = source.ISDEFAULT
            ,target.CHANGEDBYID = @CHANGEAGENTID
            ,target.DATECHANGED = @CURRENTDATE
      when not matched by target
        and not source.LONGDESCRIPTIONID is null
        then
          insert (
            ID
            ,PDCOMPOSITESEGMENTMAPPINGID
            ,PDCOMPOSITESEGMENTTABLELISTID
            ,LONGDESCRIPTIONID
            ,ISDEFAULT
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED
            )
          values (
            newid()
            ,source.MAPPINGID
            ,source.TABLELISTID
            ,source.LONGDESCRIPTIONID
            ,source.ISDEFAULT
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CURRENTDATE
            ,@CURRENTDATE
            );
    end
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR

    return 1
  end catch

  if @INITIALPARENTDESIGNATIONID=@FROMPURPOSESEARCHGUID
    set @ID = @PURPOSEID;

  return 0
end