USP_REVENUE_GETGIFTFEEPOSTEDGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2

            CREATE procedure dbo.USP_REVENUE_GETGIFTFEEPOSTEDGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2
            (
                @REVENUEID uniqueidentifier,
                @XML xml,
                @POSTDATE datetime,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )

            as

            set nocount on;

            if @CHANGEAGENTID is null
                exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            if @CHANGEDATE is null 
                set @CHANGEDATE = getdate()

            -- build a temporary table containing the values from the XML

            declare @TempTbl table (
               [ACCOUNT] nvarchar(100),
               [AMOUNT] money,
               [GLPAYMENTMETHODREVENUETYPEMAPPINGID] uniqueidentifier,
               [ID] uniqueidentifier,
               [REFERENCE] nvarchar(255),
               [TRANSACTIONTYPECODE] tinyint,
               [ORGANIZATIONAMOUNT] money,
               [TRANSACTIONAMOUNT] money,
               [TRANSACTIONCURRENCYID] uniqueidentifier,
               [BASEEXCHANGERATEID] uniqueidentifier,
               [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
               [GLACCOUNTID] uniqueidentifier
            )

            declare @PDACCOUNTSYSTEMID uniqueidentifier;
            select @PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
            from dbo.FINANCIALTRANSACTION FT
            where FT.ID = @REVENUEID;

            insert into @TempTbl select 
                D.[ACCOUNT],
                D.[AMOUNT],
                D.[GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                D.[ID],
                D.[REFERENCE],
                D.[TRANSACTIONTYPECODE],
                D.[ORGANIZATIONAMOUNT],
                D.[TRANSACTIONAMOUNT],
                D.[TRANSACTIONCURRENCYID],
                D.[BASEEXCHANGERATEID],
                D.[ORGANIZATIONEXCHANGERATEID],
                A.ID
            from dbo.UFN_REVENUE_GETGIFTFEEGLDISTRIBUTION_FROMITEMLISTXML(@XML) D
            inner join dbo.GLACCOUNT A on A.ACCOUNTNUMBER = D.ACCOUNT and A.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            if @@Error <> 0
                return 1;

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

            update JE set
                BASEAMOUNT = D.AMOUNT
                ,COMMENT = D.REFERENCE
                ,TRANSACTIONTYPECODE = D.TRANSACTIONTYPECODE
                ,ORGAMOUNT = D.ORGANIZATIONAMOUNT
                ,TRANSACTIONAMOUNT = D.TRANSACTIONAMOUNT
                ,GLACCOUNTID = D.GLACCOUNTID
                ,POSTDATE = @POSTDATE
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CHANGEDATE
            from dbo.JOURNALENTRY JE
            inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
            inner join @TempTbl D on D.ID = JEX.DISTRIBUTIONTABLEID;

            update JEX set
                GLPAYMENTMETHODREVENUETYPEMAPPINGID = D.GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,ACCOUNT = D.ACCOUNT
                ,PRECALCORGANIZATIONEXCHANGERATEID = D.ORGANIZATIONEXCHANGERATEID
                ,PRECALCBASEEXCHANGERATEID = D.BASEEXCHANGERATEID
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CHANGEDATE
            from dbo.JOURNALENTRY_EXT JEX
            inner join @TempTbl D on D.ID = JEX.DISTRIBUTIONTABLEID;


            if @@Error <> 0
                return 3;    

            -- insert new items

            declare @DISTRIBUTIONS table(
                    GLTRANSACTIONID uniqueidentifier,
                    ACCOUNT nvarchar(100),
                    AMOUNT money,
                    REFERENCE nvarchar(255),
                    TRANSACTIONTYPECODE tinyint,
                    GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                    REVENUEGLDISTRIBUTIONID uniqueidentifier,
                    ORGANIZATIONAMOUNT money,
                    TRANSACTIONAMOUNT money,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier
            );

            insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REVENUEGLDISTRIBUTIONID,
                                        ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID)
            select 
                newid(), 
                [ACCOUNT],
                [AMOUNT],
                [REFERENCE],
                [TRANSACTIONTYPECODE], 
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [ORGANIZATIONAMOUNT],
                [TRANSACTIONAMOUNT],
                [TRANSACTIONCURRENCYID],
                [BASEEXCHANGERATEID],
                [ORGANIZATIONEXCHANGERATEID]
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[GIFTFEEGLDISTRIBUTION] as data where data.ID = [temp].ID)

            -- Insert rows in GLTRANSACTION table

            insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID,
                                            ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID)
            select
                GLTRANSACTIONID,
                TRANSACTIONTYPECODE,
                ACCOUNT,
                AMOUNT,
                REFERENCE,
                @POSTDATE,
                'Blackbaud Enterprise',
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE,
                dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE([ACCOUNT],@REVENUEID),
                ORGANIZATIONAMOUNT,
                TRANSACTIONAMOUNT,
                TRANSACTIONCURRENCYID,
                BASEEXCHANGERATEID,
                ORGANIZATIONEXCHANGERATEID                        
            from 
                @DISTRIBUTIONS;



            insert into dbo.GIFTFEEGLDISTRIBUTION
                (ID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
                    ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID)
            select
                REVENUEGLDISTRIBUTIONID,
                @REVENUEID,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                REFERENCE, 
                AMOUNT,
                ACCOUNT,
                TRANSACTIONTYPECODE,
                GLTRANSACTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE,
                ORGANIZATIONAMOUNT,
                TRANSACTIONAMOUNT,
                TRANSACTIONCURRENCYID,
                BASEEXCHANGERATEID,
                ORGANIZATIONEXCHANGERATEID
            from 
                @DISTRIBUTIONS;    

            if @@Error <> 0
                return 4;

            return 0;