USP_DATAFORMTEMPLATE_ADD_PDACCOUNTCODEMAPDEVELOPMENTMEMBERSHIPINSTALLMENTS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PDACCOUNTCODEMAPDEVELOPMENTMEMBERSHIPINSTALLMENTS
(
    @SYSTEMID uniqueidentifier,
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @REVENUETYPES xml = null,
    @DEBITGLACCOUNTID uniqueidentifier = null,
    @DEBITPDACCOUNTSEGMENTVALUEID uniqueidentifier = null,
    @CREDITGLACCOUNTID uniqueidentifier = null,
    @CREDITPDACCOUNTSEGMENTVALUEID uniqueidentifier = null,
    @WRITEOFFREASONCODESUBTYPECODE xml = null
)
    as
        set nocount on;

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

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

        declare @CURRENTDATE datetime = getdate()

        declare @ALLCODESID uniqueidentifier = '99999999-9999-9999-9999-999999999999'
        declare @PDACCOUNTCODEMAPOFFICEID uniqueidentifier = 'D38E0F1D-845F-42CD-837E-8030F538A32B'
        declare @OFFICEID int = 19

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

        begin try
            insert into dbo.PDACCOUNTCODEMAPPING (
                ID,
                REVENUETYPE,
                PDACCOUNTCODEMAPOFFICEID,
                OFFICEID,
                DEBITGLACCOUNTID,
                DEBITPDACCOUNTSEGMENTVALUEID,
                CREDITGLACCOUNTID,
                CREDITPDACCOUNTSEGMENTVALUEID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED,
                PDACCOUNTSYSTEMID
            )
            values (
                @ID,
                @REVTYPEMASK,
                @PDACCOUNTCODEMAPOFFICEID,
                @OFFICEID,
                @DEBITGLACCOUNTID,
                @DEBITPDACCOUNTSEGMENTVALUEID,
                @CREDITGLACCOUNTID,
                @CREDITPDACCOUNTSEGMENTVALUEID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
                @SYSTEMID
            )

            if (@REVTYPEMASK & 52) <> 0
            begin
                declare @REASONCODES table (
                    ID uniqueidentifier
                )
                insert into @REASONCODES
                select distinct REASONCODE.ID
                from @WRITEOFFREASONCODESUBTYPECODE.nodes('/WRITEOFFREASONCODESUBTYPECODE/ITEM') T(c)
                cross apply (select T.c.value('(REASONCODESUBTYPECODEID)[1]','uniqueidentifier') ID) REASONCODE

                if not exists(select 1 from @REASONCODES) or exists(select 1 from @REASONCODES where ID = @ALLCODESID)
                begin
                    delete from @REASONCODES
                    insert @REASONCODES
                    values (@ALLCODESID)
                end

                insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (
                    ADDITIONALSUBTYPE,
                    PDACCOUNTCODEMAPPINGID,
                    SUBTYPEID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    15,
                    @ID,
                    REASONCODES.ID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from @REASONCODES [REASONCODES]
            end
        end try
        begin catch
            exec dbo.USP_RAISE_ERROR
            return 1
        end catch

        return 0