USP_DATAFORMTEMPLATE_EDIT_PDACCOUNTCODEMAPDEVELOPMENTMEMBERSHIPINSTALLMENTS

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

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
    declare @CURRENTDATE datetime = getdate()

    declare @ALLCODESID uniqueidentifier = '99999999-9999-9999-9999-999999999999'
    declare @REVTYPEMASK integer 
    select @REVTYPEMASK = sum(NAMEID) from dbo.UFN_PDACCOUNTCODEMAPPING_REVENUETYPES_FROMITEMLISTXML(@REVENUETYPES)

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

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

        if (@REVTYPEMASK & 52) <> 0
        begin
            --Get selected reason codes

            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

            --Remove old reason codes

            delete PDACCOUNTCODEMAPPINGSUBTYPE
            from dbo.PDACCOUNTCODEMAPPINGSUBTYPE
            where 
                PDACCOUNTCODEMAPPINGID = @ID and
                ADDITIONALSUBTYPE = 15 and
                not exists (select 1 from @REASONCODES where ID = PDACCOUNTCODEMAPPINGSUBTYPE.SUBTYPEID)

            --Add new reason codes

            insert into dbo.PDACCOUNTCODEMAPPINGSUBTYPE (
                ADDITIONALSUBTYPE,
                PDACCOUNTCODEMAPPINGID,
                SUBTYPEID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                15,
                @ID,
                REASONCODES.ID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from @REASONCODES [REASONCODES]
            where not exists(
                select 1
                from dbo.PDACCOUNTCODEMAPPINGSUBTYPE 
                where 
                    PDACCOUNTCODEMAPPINGID = @ID and
                    ADDITIONALSUBTYPE = 15 and
                    SUBTYPEID = [REASONCODES].ID
            )
        end
        else
        begin
            delete PDACCOUNTCODEMAPPINGSUBTYPE 
            from dbo.PDACCOUNTCODEMAPPINGSUBTYPE 
            where ADDITIONALSUBTYPE = 15 and PDACCOUNTCODEMAPPINGID = @ID
        end

        if @CONTEXTCACHE is not null
        set CONTEXT_INFO @CONTEXTCACHE

        if dbo.UFN_PDACCOUNTCODEMAPPING_VALIDMAPPINGWITHSUBTYPES2(@ID, @CURRENTDATE) = 0
            raiserror('BBERR_PDACCOUNTCODEMAPPING_VALIDMAPPINGWITHSUBTYPES', 16, 1)
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;