USP_PAYMENT_ADD

            CREATE procedure dbo.USP_PAYMENT_ADD
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime,
                @CONSTITUENTID uniqueidentifier,                    
                @DATE datetime,
                @AMOUNT money = 0,
                @PAYMENTMETHODCODE tinyint = 1,
                @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
                @CHECKNUMBER nvarchar(20) = '',
                @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
                @REFERENCENUMBER nvarchar(20) = '',
                @CARDHOLDERNAME nvarchar(255) = '',
                @CREDITCARDNUMBER nvarchar(4) = '',
                @CREDITTYPECODEID uniqueidentifier = null,
                @AUTHORIZATIONCODE nvarchar(20) = '',
                @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
                @ISSUER nvarchar(100) = '',
                @NUMBEROFUNITS decimal(20,3) = 0,
                @SYMBOL nvarchar(25) = '',
                @MEDIANPRICE decimal(19,4) = 0,
                @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
                @PROPERTYSUBTYPECODEID uniqueidentifier = null,
                @RECEIPTAMOUNT money = 0,
                @CONSTITUENTACCOUNTID uniqueidentifier = null,
                @REVENUESTREAMS xml = null,
                @POSTSTATUSCODE tinyint = 1,
                @POSTDATE datetime = null,
                @FINDERNUMBER bigint = null,
                @SOURCECODE nvarchar(50) = null,
                @APPEALID uniqueidentifier = null,
                @BENEFITS xml = null,
                @BENEFITSWAIVED bit = 0,
                @GIVENANONYMOUSLY bit = null,
                @MAILINGID uniqueidentifier = null,
                @CHANNELCODEID uniqueidentifier = null,
                @DONOTACKNOWLEDGE bit = 0,
                @DONOTRECEIPT bit = 0,
                @BATCHNUMBER nvarchar(100) = '',
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
                @REFERENCE nvarchar(255) = null,
                @TRIBUTEID uniqueidentifier = null,
                @LETTERCODEID uniqueidentifier = null,
                @DIRECTDEBITRESULTCODE nvarchar(10) = '',
                @LOWPRICE decimal(19,4) = 0,
                @HIGHPRICE decimal(19,4) = 0,
                @GIFTINKINDITEMNAME nvarchar(100) = '',
                @GIFTINKINDDISPOSITIONCODE tinyint = 0,
                @GIFTINKINDNUMBEROFUNITS int = 0,
                @GIFTINKINDFAIRMARKETVALUE money = 0,
                @PERCENTAGEBENEFITS xml = null,
                @PDACCOUNTSYSTEMID uniqueidentifier = null,
                @TRANSACTIONCURRENCYID uniqueidentifier = null,
                @BASECURRENCYID uniqueidentifier = null,
                @BASEEXCHANGERATEID uniqueidentifier = null,
                @EXCHANGERATE decimal(20,8) = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SEPAMANDATEID uniqueidentifier = null,
                @TRANSACTIONID uniqueidentifier = null,
                @MERCHANTACCOUNTID uniqueidentifier = null,
                @VENDORID nvarchar(50) = ''
            )
            as
            set nocount on;

            if @PDACCOUNTSYSTEMID is null
                    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'

            declare @ALLOWGLDISTRIBUTIONS bit
            set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

            if @ALLOWGLDISTRIBUTIONS = 0 
            begin
                set @POSTSTATUSCODE = 2        -- Do not post

                set @POSTDATE = null
            end
            else
                -- In case API user calls with POSTSTATUSCODE null (or BBMETAL for unit testing).

                set @POSTSTATUSCODE = isnull(@POSTSTATUSCODE, 1)

            begin try
                exec dbo.USP_PAYMENT_ADDBASE @ID output, @CHANGEAGENTID, @CURRENTDATE, @CONSTITUENTID,
                                        @DATE, @AMOUNT, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER,
                                        @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER,
                                        @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER,
                                        @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @GIFTINKINDSUBTYPECODEID,
                                        @PROPERTYSUBTYPECODEID, @RECEIPTAMOUNT, @CONSTITUENTACCOUNTID,
                                        @POSTSTATUSCODE, @POSTDATE, @FINDERNUMBER,
                                        @SOURCECODE, @APPEALID, @BENEFITS, @BENEFITSWAIVED, @GIVENANONYMOUSLY, 
                                        @MAILINGID, @CHANNELCODEID, @DONOTACKNOWLEDGE,@DONOTRECEIPT,
                                        @BATCHNUMBER, @OTHERPAYMENTMETHODCODEID, @REFERENCE, @TRIBUTEID, @LETTERCODEID, 
                                        @DIRECTDEBITRESULTCODE, @LOWPRICE, @HIGHPRICE, @GIFTINKINDITEMNAME, 
                                        @GIFTINKINDDISPOSITIONCODE, @GIFTINKINDNUMBEROFUNITS, 
                                        @GIFTINKINDFAIRMARKETVALUE, @PERCENTAGEBENEFITS, @TRANSACTIONCURRENCYID,
                                        @BASECURRENCYID, @BASEEXCHANGERATEID, @EXCHANGERATE, @CURRENTAPPUSERID,
                                        @SEPAMANDATEID, @TRANSACTIONID, @MERCHANTACCOUNTID, @VENDORID;

                exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
                    @ID = @ID,
                    @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
                    @CHANGEDATE = @CURRENTDATE,
                    @CHANGEAGENTID = @CHANGEAGENTID    

                declare @SPLITSDECLININGGIFTAID xml, @COVENANTGIFTSPLITS xml, @GIFTAIDSPONSORSHIPSPLITS xml
                if not @REVENUESTREAMS is null
                begin

                    --Error if an exchange rate isn't entered, but the transaction and base currencies are different,

                    --    and the payment is for anything other than a donation, other, or unapplied MG.

                    if @BASEEXCHANGERATEID is null and @TRANSACTIONCURRENCYID <> @BASECURRENCYID
                    begin
                        if exists(
                            select 1
                            from dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS)
                            where not (
                                APPLICATIONCODE in (0,4,11,100)
                            )
                        )
                        begin
                            raiserror('BBERR_INVALIDAPPLICATIONSWITHNORATE : Payments without an exchange rate can only be applied to the donation, other, or unapplied matching gift application types.', 13, 1);
                            return 1;
                        end
                    end

          if exists
                        (select
                            1
                        from
                            dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS) APPS
                            inner join dbo.FINANCIALTRANSACTION FT on APPS.APPLICATIONID = FT.ID and FT.TYPECODE in (0,1,4,5,6,7)
                        where
                            FT.PDACCOUNTSYSTEMID <> @PDACCOUNTSYSTEMID)
          begin
            raiserror('BBERR_MISMATCHACCOUNTSYSTEM', 13, 1);
              return 1;
          end

                    -- Bug 194561 - if it is an auction item, do not allow payment to be anything but cash, check, or credit card. Those are the only payment methods implemented in AMPro.

                    -- If more methods are added, update the error message in Errors.resx.

          -- Bug 2505357 --Added PayPal and Venmo to available payment methods for Auction Purchases

                    if @PAYMENTMETHODCODE not in (0, 1, 2, 101, 102) -- cash, check, credit card, paypal, venmo

                    and exists
                    (
                        select
                            APPLICATIONCODE
                        from
                            dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS)
                        where
                            APPLICATIONCODE = 12 -- auction purchase

                    )
                    begin
                        raiserror('BBERR_AUCTIONITEM_PAYMENT_INVALIDPAYMENTMETHOD', 13, 1);
                        return 1;
                    end


                    exec dbo.USP_REVENUE_APPLYTOREVENUESTREAMS @ID, @CONSTITUENTID, @DATE, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE, @SPLITSDECLININGGIFTAID output, @COVENANTGIFTSPLITS output, @GIFTAIDSPONSORSHIPSPLITS output, @PDACCOUNTSYSTEMID;
                end

                exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 0, @SPLITSDECLININGGIFTAID, @COVENANTGIFTSPLITS, @GIFTAIDSPONSORSHIPSPLITS; --revenue transaction type code for payment is 0


                if @AMOUNT <(select sum(TRANSACTIONAMOUNT) from dbo.REVENUESPLIT where REVENUEID = @ID)
                    raiserror('BBERR_AMOUNTLESSTHANAPPLIEDAMOUNT', 13, 1);

                if @AMOUNT <> (select sum(TRANSACTIONAMOUNT) from dbo.REVENUESPLIT where REVENUEID = @ID)
                    raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);

                if (select count(*) from dbo.REVENUESPLIT where REVENUEID = @ID) = 0
                    raiserror('BBERR_NOAPPLICATIONS', 13, 1);

                declare @SPLITS xml;
                declare @APPLIEDAMOUNT money;
                select @SPLITS = (select sum(REVENUESPLIT.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, 0 as TYPECODE
                                    from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 0
                                    group by REVENUESPLIT_EXT.DESIGNATIONID
                                    for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);

        if exists (select 1
            from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
            inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
            where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and (REVENUESPLIT_EXT.TYPECODE = 0 or REVENUESPLIT_EXT.TYPECODE = 4) and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
            and DESIGNATION.ISACTIVE = 0)
          raiserror('Revenue cannot be added to inactive designations.', 13, 2);

                select @APPLIEDAMOUNT = sum(REVENUESPLIT.TRANSACTIONAMOUNT)
                    from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                    where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 0;

                --if @APPLIEDAMOUNT > 0 

                --begin

                    -- create matching gift records

                    if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG() > 0
                        exec USP_MATCHINGGIFTPLEDGE_AUTOADD @CONSTITUENTID, @CHANGEAGENTID, @ID, @DATE, @APPLIEDAMOUNT, @RECEIPTAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                    if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG() > 0
                        exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @CONSTITUENTID, @CHANGEAGENTID, @ID, @DATE, @APPLIEDAMOUNT, @RECEIPTAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                --end


                -- Only create the records if this is a UK product and there is a specified tribute

                if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1 and @TRIBUTEID is not null
                    exec dbo.USP_REVENUETRIBUTETAXCLAIMAMOUNT_ADDUPDATE @ID, @CHANGEAGENTID, @CURRENTDATE;

        -- LTM - WI #186567, #167954 - break up this logic and add to appropriate SP to allow some triggers to work based on appealid.        

                --If any marketing information is null, try to default based on applications        

                --if @SOURCECODE is null or @SOURCECODE = '' or @MAILINGID is null or @APPEALID is null

                    --exec dbo.USP_REVENUE_DEFAULTMARKETINGINFORMATION @ID, @CHANGEAGENTID, @CURRENTDATE;


                if @POSTSTATUSCODE <> 2
                  begin
                        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

                        -- save any benefit distributions

                        exec dbo.USP_SAVE_PAYMENT_BENEFITGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

            -- save any auction purchase distributions

            exec dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE, @PDACCOUNTSYSTEMID;
                end

                -- USP_PAYMENT_ADDGIFTFEES creates GL distributions so it needs to be called after USP_PDACCOUNTSYSTEM_LINKTOREVENUE

                if not @REVENUESTREAMS is null
                    exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

      end try

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

            return 0;