USP_PAYMENT_EDIT

Stored procedure to save a payment.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@TYPECODE	tinyint	IN
@DATE	datetime	IN
@AMOUNT	money	IN
@PAYMENTMETHODCODE	tinyint	IN
@CHECKDATE	UDT_FUZZYDATE	IN
@CHECKNUMBER	nvarchar(20)	IN
@REFERENCEDATE	UDT_FUZZYDATE	IN
@REFERENCENUMBER	nvarchar(20)	IN
@CARDHOLDERNAME	nvarchar(255)	IN
@CREDITCARDNUMBER	nvarchar(4)	IN
@CREDITTYPECODEID	uniqueidentifier	IN
@AUTHORIZATIONCODE	nvarchar(20)	IN
@EXPIRESON	UDT_FUZZYDATE	IN
@ISSUER	nvarchar(100)	IN
@NUMBEROFUNITS	decimal(20, 3)	IN
@SYMBOL	nvarchar(25)	IN
@MEDIANPRICE	decimal(19, 4)	IN
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	IN
@PROPERTYSUBTYPECODEID	uniqueidentifier	IN
@RECEIPTAMOUNT	money	IN
@CONSTITUENTACCOUNTID	uniqueidentifier	IN
@SPLITS	xml	IN
@FINDERNUMBER	bigint	IN
@SOURCECODE	nvarchar(50)	IN
@APPEALID	uniqueidentifier	IN
@BENEFITS	xml	IN
@BENEFITSWAIVED	bit	IN
@GIVENANONYMOUSLY	bit	IN
@MAILINGID	uniqueidentifier	IN
@CHANNELCODEID	uniqueidentifier	IN
@DONOTACKNOWLEDGE	bit	IN
@DONOTRECEIPT	bit	IN
@CHANGEDATE	datetime	IN

Definition

Copy



            CREATE procedure dbo.USP_PAYMENT_EDIT
            (
                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @TYPECODE tinyint,
                @DATE datetime,
                @AMOUNT money,                    
                @PAYMENTMETHODCODE tinyint,
                @CHECKDATE dbo.UDT_FUZZYDATE,
                @CHECKNUMBER nvarchar(20),
                @REFERENCEDATE dbo.UDT_FUZZYDATE,
                @REFERENCENUMBER nvarchar(20),
                @CARDHOLDERNAME nvarchar(255),
                @CREDITCARDNUMBER nvarchar(4),
                @CREDITTYPECODEID uniqueidentifier,
                @AUTHORIZATIONCODE nvarchar(20),
                @EXPIRESON dbo.UDT_FUZZYDATE,
                @ISSUER nvarchar(100),
                @NUMBEROFUNITS decimal(20,3),
                @SYMBOL nvarchar(25),
                @MEDIANPRICE decimal(19,4),
                @GIFTINKINDSUBTYPECODEID uniqueidentifier,
                @PROPERTYSUBTYPECODEID uniqueidentifier,
                @RECEIPTAMOUNT money,
                @CONSTITUENTACCOUNTID uniqueidentifier,
                @SPLITS xml,
                @FINDERNUMBER bigint,
                @SOURCECODE nvarchar(50),
                @APPEALID uniqueidentifier,
                @BENEFITS xml,
                @BENEFITSWAIVED bit,
                @GIVENANONYMOUSLY bit,
                @MAILINGID uniqueidentifier,
                @CHANNELCODEID uniqueidentifier,
                @DONOTACKNOWLEDGE bit,
                @DONOTRECEIPT bit,
                @CHANGEDATE datetime = null
            )
            as
            set nocount on;

            declare @SALEDATE datetime;
            declare @SALEAMOUNT money;
            declare @BROKERFEE money;
            declare @SALEPOSTDATE datetime;
            declare @SALEPOSTSTATUSCODE tinyint;

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

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

            if @AMOUNT < 0
                raiserror('The amount cannot be negative.', 13, 1);

            if @PAYMENTMETHODCODE = 5
                select top 1
                    @SALEDATE = SALEDATE,
                    @SALEAMOUNT = SALEAMOUNT,
                    @BROKERFEE = BROKERFEE,
                    @SALEPOSTDATE = SALEPOSTDATE,
                    @SALEPOSTSTATUSCODE = SALEPOSTSTATUSCODE
                from dbo.PROPERTYDETAIL
                where 
                    PROPERTYDETAIL.ID = @ID and
                    ISSOLD = 1

            declare @OLDAMOUNT money;

            select @OLDAMOUNT = AMOUNT
            from dbo.REVENUE
            where ID = @ID;

            /*JamesWill CR270391-031907 2007/03/19 Validate to make sure that the new amount does not overpay a commitment*/
            if not @TYPECODE in (0, 5) --You can't overpay gifts or recurring gifts, so it doesn't apply here

            begin
                declare @COMMITMENTID uniqueidentifier;
                declare @BALANCE money;

                if @TYPECODE in (4, 8) --Pledge Payment, Matching Gift Pledge Payment

                begin
                    select @COMMITMENTID = PLEDGEID
                    from dbo.INSTALLMENTPAYMENT where PAYMENTID = @ID;

                    set @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(@COMMITMENTID);
                end
                else if @TYPECODE = 6 --Event registration fee

                begin
                    select @COMMITMENTID = REGISTRANTID
                    from dbo.EVENTREGISTRANTPAYMENT where PAYMENTID = @ID;

                    set @BALANCE = dbo.UFN_EVENTREGISTRANT_GETBALANCE(@COMMITMENTID);
                end
                else
                    set @BALANCE = 0; --Unknown


                set @BALANCE = @BALANCE + @OLDAMOUNT; --The original amount for this revenue no longer applies. 

                set @BALANCE = @BALANCE - @AMOUNT; 
                if @BALANCE < 0
                    raiserror('The amount applied towards an application cannot be greater than its balance.', 13, 1);
            end

            if not (@TYPECODE in (6, 4, 8))-- update splits if not Event Fee\Pledge\MG payment

                exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT;

            /* Validate payment information */

            if @FINDERNUMBER is null
                set @FINDERNUMBER = 0;

      if @OLDAMOUNT <> @AMOUNT 
        -- make sure we aren't trying to modify revenue in a locked/closed deposit/bank

        exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;

            update dbo.REVENUE
            set    AMOUNT = @AMOUNT, 
                RECEIPTAMOUNT = @RECEIPTAMOUNT, 
                FINDERNUMBER = @FINDERNUMBER,
                SOURCECODE = @SOURCECODE, 
                APPEALID = @APPEALID, 
                BENEFITSWAIVED = @BENEFITSWAIVED,
                GIVENANONYMOUSLY = @GIVENANONYMOUSLY, 
                MAILINGID = @MAILINGID, 
                CHANNELCODEID = @CHANNELCODEID, 
                DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE, 
                DATE = @DATE,
                DONOTRECEIPT = @DONOTRECEIPT,
                CHANGEDBYID = @CHANGEAGENTID, 
                DATECHANGED = @CHANGEDATE 
            where ID = @ID;


            if not (@TYPECODE in (6, 4, 8))-- update splits if not Event Fee\Pledge\MG payment

                exec dbo.USP_REVENUE_GETSPLITS_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CHANGEDATE;

            -- update benefits

            exec dbo.USP_REVENUE_GETBENEFITS_UPDATEFROMXML @ID, @BENEFITS, @CHANGEAGENTID, @CHANGEDATE;

            -- Update solicitors

            exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CHANGEDATE;

            -- update the payment information for each revenue record in the transaction

            exec dbo.USP_REVENUE_UPDATEPAYMENTDETAILS @ID, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER, @CONSTITUENTACCOUNTID, @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @PROPERTYSUBTYPECODEID, @GIFTINKINDSUBTYPECODEID, @SALEDATE, @SALEAMOUNT, @BROKERFEE, @SALEPOSTDATE, @SALEPOSTSTATUSCODE, @CHANGEAGENTID,@CHANGEDATE,@ORIGINALPAYMENTMETHODCODE=@PAYMENTMETHODCODE;

            /*Special handling for some payment types*/
            if @TYPECODE = 4 or @TYPECODE = 8 --Pledge\MG payment

                exec dbo.USP_PLEDGEPAYMENT_EDIT @ID, @AMOUNT, @CHANGEAGENTID, @CHANGEDATE

            if @TYPECODE = 5 --Recurring gift payment

                exec dbo.USP_RECURRINGPAYMENT_EDIT @ID, @AMOUNT, @CHANGEAGENTID, @CHANGEDATE

            return 0;