USP_DATAFORMTEMPLATE_EDIT_PDCOMPOSITESEGMENTMAPPINGLIST

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PDCOMPOSITESEGMENTMAPPINGLIST (
  @ID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@MAPPINGS as xml
  )
as
set nocount on;

declare @DEFAULT_GUID uniqueidentifier = '99999999-9999-9999-9999-999999999999';

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

declare @CURRENTDATE datetime;

set @CURRENTDATE = getdate();

-- I am not sure why this even exists since it is derived from PDACCOUNTSTRUCTUREID. I am probably wrong and don't understand the table structure.

declare @PDCOMPOSITESEGMENTID uniqueidentifier;

select @PDCOMPOSITESEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENTID
from PDACCOUNTSTRUCTURE
where PDACCOUNTSTRUCTURE.ID = @ID

declare @TMAPPINGS as table (
  ID uniqueidentifier
  ,ID1 uniqueidentifier
  ,ID2 uniqueidentifier
  ,ID3 uniqueidentifier
  ,ID4 uniqueidentifier
  ,[NAME] nvarchar(50)
  ,SEGMENTID uniqueidentifier
  );

insert into @TMAPPINGS (
  ID
  ,ID1
  ,ID2
  ,ID3
  ,ID4
  ,[NAME]
  ,SEGMENTID
  )
select coalesce(ID, newid())
  ,ID1
  ,ID2
  ,ID3
  ,ID4
  ,[NAME]
  ,SEGMENTID
from UFN_PDCOMPOSITESEGMENTENTRIES_FROMITEMLISTXML(@MAPPINGS)

--Pre-check for duplicates

if exists (
    select
        1
    from
        @TMAPPINGS T
    group by
        T.ID1, T.ID2, T.ID3, T.ID4
    having
        count(1) > 1
    )
begin
    raiserror('CK_PDCOMPOSITESEGMENTMAPPINGENTRY_VALIDMAPPING', 16, 1);
    return 1;
end

merge dbo.PDCOMPOSITESEGMENTMAPPING as target
using (
  select ID
    ,[NAME]
    ,SEGMENTID
  from @TMAPPINGS
  ) as source
  on (target.ID = source.ID)
when matched
  then
    update
    set target.[NAME] = source.[NAME]
      ,target.PDACCOUNTSEGMENTVALUEID = source.SEGMENTID
      ,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.[NAME]
      ,source.SEGMENTID
      ,@PDCOMPOSITESEGMENTID -- ??

      ,@ID -- PDACCOUNTSTRUCTUREID

      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@CURRENTDATE
      )
when not matched by source
  and target.PDACCOUNTSTRUCTUREID = @ID
  then
    delete;

/* 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

merge dbo.PDCOMPOSITESEGMENTMAPPINGENTRY as target
using (
  select 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
    from (
      select ID
        ,ID1
        ,ID2
        ,ID3
        ,ID4
      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 = @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
      );

return 0;