USP_DATAFORMTEMPLATE_EDIT_PDACCOUNTCODEMAPDEVELOPMENTPLEDGES3

    create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PDACCOUNTCODEMAPDEVELOPMENTPLEDGES3(
        @ID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier = null,
        @REVENUETYPES xml,
        @SUBTYPEONE xml,
        @DEBITGLACCOUNTID uniqueidentifier,
        @DEBITPDACCOUNTSEGMENTVALUEID uniqueidentifier,
        @CREDITGLACCOUNTID uniqueidentifier,
        @CREDITPDACCOUNTSEGMENTVALUEID uniqueidentifier, 
        @PDACCOUNTSYSTEMID uniqueidentifier,
        @SUBTYPETWO xml
    )
    as

        set nocount on;

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

    declare @ALLCODES uniqueidentifier = '99999999-9999-9999-9999-999999999999'
        declare @CURRENTDATE datetime
        set @CURRENTDATE = getdate()

    declare @REVTYPEMASK integer
    select @REVTYPEMASK=sum(NAMEID) from dbo.UFN_PDACCOUNTCODEMAPPING_REVENUETYPES_FROMITEMLISTXML(@REVENUETYPES)

        declare @OFFICEID int
        set @OFFICEID = 4

        begin try
            begin tran 

            update dbo.PDACCOUNTCODEMAPPING set
                REVENUETYPE = @REVTYPEMASK,
                OFFICEID = @OFFICEID,
                DEBITGLACCOUNTID = @DEBITGLACCOUNTID,
                DEBITPDACCOUNTSEGMENTVALUEID = @DEBITPDACCOUNTSEGMENTVALUEID,
                CREDITGLACCOUNTID = @CREDITGLACCOUNTID,
                CREDITPDACCOUNTSEGMENTVALUEID = @CREDITPDACCOUNTSEGMENTVALUEID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE,
                PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
            where ID = @ID

          declare @CONTEXTCACHE varbinary(128) = CONTEXT_INFO()
          set CONTEXT_INFO @CHANGEAGENTID

      if (@REVTYPEMASK & 11)<>0
            if exists (select * from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPEONE_FROMITEMLISTXML(@SUBTYPEONE) where SUBTYPEONEID = @ALLCODES) 
                begin
                  if not exists (select * from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where PDACCOUNTCODEMAPPINGID = @ID and SUBTYPEID = @ALLCODES and ADDITIONALSUBTYPE = 5)
                      begin
                        delete from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where PDACCOUNTCODEMAPPINGID = @ID and ADDITIONALSUBTYPE = 5
                        insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (ADDITIONALSUBTYPE, PDACCOUNTCODEMAPPINGID, SUBTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values 
                        (5, @ID, @ALLCODES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                      end 
                end
            else
                begin
                  delete PDACCOUNTCODEMAPPINGSUBTYPE from dbo.PDACCOUNTCODEMAPPINGSUBTYPE 
                  where ADDITIONALSUBTYPE = 5 and not exists(select * from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPEONE_FROMITEMLISTXML(@SUBTYPEONE) TF where TF.SUBTYPEONEID = PDACCOUNTCODEMAPPINGSUBTYPE.SUBTYPEID)
                  and PDACCOUNTCODEMAPPINGID = @ID

            if (select count(*) from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPEONE_FROMITEMLISTXML(@SUBTYPEONE)) = 0
              insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (ADDITIONALSUBTYPE, PDACCOUNTCODEMAPPINGID, SUBTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                      values 
                      (5, @ID, @ALLCODES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
              else     
                    insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (ADDITIONALSUBTYPE, PDACCOUNTCODEMAPPINGID, SUBTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select 5, @ID, SUBTYPEONEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPEONE_FROMITEMLISTXML(@SUBTYPEONE) TF
                    where not exists(select * from PDACCOUNTCODEMAPPINGSUBTYPE WHERE PDACCOUNTCODEMAPPINGSUBTYPE.SUBTYPEID = TF.SUBTYPEONEID and PDACCOUNTCODEMAPPINGID = @ID)
                end            
      else
                delete PDACCOUNTCODEMAPPINGSUBTYPE from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where ADDITIONALSUBTYPE = 5 and PDACCOUNTCODEMAPPINGID=@ID

      if (@REVTYPEMASK & 4)<>0
            if (@SUBTYPETWO is null) or (exists (select * from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPETWO_FROMITEMLISTXML(@SUBTYPETWO) where SUBTYPETWOID = @ALLCODES))
                begin
                  if not exists (select * from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where PDACCOUNTCODEMAPPINGID = @ID and SUBTYPEID = @ALLCODES and ADDITIONALSUBTYPE = 15)
                      begin
                        delete from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where PDACCOUNTCODEMAPPINGID = @ID and ADDITIONALSUBTYPE = 15
                        insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (ADDITIONALSUBTYPE, PDACCOUNTCODEMAPPINGID, SUBTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values 
                        (15, @ID, @ALLCODES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                      end 
                end
            else
                begin
                  delete PDACCOUNTCODEMAPPINGSUBTYPE from dbo.PDACCOUNTCODEMAPPINGSUBTYPE 
                  where ADDITIONALSUBTYPE = 15 and not exists(select * from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPETWO_FROMITEMLISTXML(@SUBTYPETWO) TF where TF.SUBTYPETWOID = PDACCOUNTCODEMAPPINGSUBTYPE.SUBTYPEID)
                  and PDACCOUNTCODEMAPPINGID = @ID

            if (select count(*) from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPETWO_FROMITEMLISTXML(@SUBTYPETWO)) = 0
              insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (ADDITIONALSUBTYPE, PDACCOUNTCODEMAPPINGID, SUBTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                      values 
                      (15, @ID, @ALLCODES, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
              else     
                    insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (ADDITIONALSUBTYPE, PDACCOUNTCODEMAPPINGID, SUBTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select 15, @ID, SUBTYPETWOID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE from dbo.UFN_PDACCOUNTCODEMAPPINGSUBTYPE_SUBTYPETWO_FROMITEMLISTXML(@SUBTYPETWO) TF
                    where not exists(select * from PDACCOUNTCODEMAPPINGSUBTYPE WHERE PDACCOUNTCODEMAPPINGSUBTYPE.SUBTYPEID = TF.SUBTYPETWOID and PDACCOUNTCODEMAPPINGID = @ID)
                end            
      else
        delete PDACCOUNTCODEMAPPINGSUBTYPE from dbo.PDACCOUNTCODEMAPPINGSUBTYPE where ADDITIONALSUBTYPE = 15 and PDACCOUNTCODEMAPPINGID=@ID

          if @CONTEXTCACHE is not null
            set CONTEXT_INFO @CONTEXTCACHE

            if dbo.UFN_PDACCOUNTCODEMAPPING_VALIDMAPPINGWITHSUBTYPES2(@ID, @CURRENTDATE) = 0
                raiserror('CK_PDACCOUNTCODEMAPPING_VALIDMAPPINGWITHSUBTYPES', 16, 1)
            commit         

        end try
        begin catch
            exec dbo.USP_RAISE_ERROR
            return 1
        end catch

    return 0;