USP_DATAFORMTEMPLATE_EDITSAVE_SALESMETHODPAYMENTMETHOD

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_SALESMETHODPAYMENTMETHOD
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @PAYMENTMETHODS xml
)
as

    set nocount on;

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

    declare @CHANGEDATE datetime;
    set @CHANGEDATE = getdate();

    begin try
        declare @TEMP table
            (ID uniqueidentifier,
             PAYMENTTYPECODE tinyint,
             OTHERPAYMENTMETHODCODEID uniqueidentifier);

        insert into @TEMP
            select coalesce(P.ID, newid()),
                case when P.PAYMENTTYPEID = 'CF693144-0204-4FFD-90EF-335E509F9C69' then 0
                    when P.PAYMENTTYPEID = '49BCEDF6-213C-4734-896B-BB183B487D22' then 1
                    when P.PAYMENTTYPEID = '3D8F2B59-3647-48F0-831A-ABC527C3B7AB' then 2
                    else 10 end as [PAYMENTTYPECODE],
                case when P.PAYMENTTYPEID = 'CF693144-0204-4FFD-90EF-335E509F9C69' then null
                    when P.PAYMENTTYPEID = '49BCEDF6-213C-4734-896B-BB183B487D22' then null
                    when P.PAYMENTTYPEID = '3D8F2B59-3647-48F0-831A-ABC527C3B7AB' then null
                    else P.PAYMENTTYPEID end as [OTHERPAYMENTMETHODCODEID]
            from 
                (SELECT
                    T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                    T.c.value('(PAYMENTTYPEID)[1]','uniqueidentifier') AS 'PAYMENTTYPEID'
                    FROM @PAYMENTMETHODS.nodes('/PAYMENTMETHODS/ITEM') T(c)) AS P;

        declare @contextCache varbinary(128);

        -- cache current context information 

        set @contextCache = CONTEXT_INFO();

        -- set CONTEXT_INFO to @CHANGEAGENTID 

        if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID;

        -- delete any items that no longer exist in the XML table

        delete from dbo.[SALESMETHODPAYMENTMETHOD] 
            where [SALESMETHODPAYMENTMETHOD].SALESMETHODID = @ID
            and [SALESMETHODPAYMENTMETHOD].ID in 
            (select ID from dbo.SALESMETHODPAYMENTMETHOD
            EXCEPT select ID from @TEMP)    

        -- reset CONTEXT_INFO to previous value 

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

        -- update the items that exist in the XML table and the db

        update [SALESMETHODPAYMENTMETHOD]
                set [SALESMETHODPAYMENTMETHOD].[ID]=temp.[ID],
                [SALESMETHODPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID]=temp.[OTHERPAYMENTMETHODCODEID],
                [SALESMETHODPAYMENTMETHOD].[PAYMENTTYPECODE]=temp.[PAYMENTTYPECODE],
                [SALESMETHODPAYMENTMETHOD].CHANGEDBYID = @CHANGEAGENTID,
                [SALESMETHODPAYMENTMETHOD].DATECHANGED = @CHANGEDATE

            from dbo.[SALESMETHODPAYMENTMETHOD] inner join @TEMP as [temp] on [SALESMETHODPAYMENTMETHOD].ID = [temp].ID
            where ([SALESMETHODPAYMENTMETHOD].[PAYMENTTYPECODE] = 10 and [SALESMETHODPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID]<>temp.[OTHERPAYMENTMETHODCODEID]) or 
                ([SALESMETHODPAYMENTMETHOD].[PAYMENTTYPECODE]<>temp.[PAYMENTTYPECODE])

        -- insert new items

        insert into [SALESMETHODPAYMENTMETHOD] 
            ([SALESMETHODID], 
            [ID],
            [OTHERPAYMENTMETHODCODEID],
            [PAYMENTTYPECODE],                
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED)
        select @ID, 
            [ID],
            [OTHERPAYMENTMETHODCODEID],
            [PAYMENTTYPECODE], 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CHANGEDATE, 
            @CHANGEDATE
        from @TEMP as [temp]
        where not exists (select ID from dbo.[SALESMETHODPAYMENTMETHOD] as data where data.ID = [temp].ID)

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

        return 0;