USP_REVENUEPAYMENT_EDIT_2

Stored procedure to save a revenue payment.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@DATE	datetime	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
@CONSTITUENTACCOUNTID	uniqueidentifier	IN
@GIVENANONYMOUSLY	bit	IN
@DONOTRECEIPT	bit	IN
@CHANGEDATE	datetime	IN
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	IN
@REFERENCE	nvarchar(255)	IN
@DIRECTDEBITRESULTCODE	nvarchar(10)	IN
@LOWPRICE	decimal(19, 4)	IN
@HIGHPRICE	decimal(19, 4)	IN

Definition

Copy



            CREATE procedure dbo.USP_REVENUEPAYMENT_EDIT_2
            (
                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @DATE datetime,
                @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,
                @CONSTITUENTACCOUNTID uniqueidentifier,
                @GIVENANONYMOUSLY bit,
                @DONOTRECEIPT bit,
                @CHANGEDATE datetime = null,
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
                @REFERENCE nvarchar(255) = null,
                @DIRECTDEBITRESULTCODE nvarchar(10),
                @LOWPRICE decimal(19,4) = null,
                @HIGHPRICE decimal(19,4) = null
            )
            as
            set nocount on;

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

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

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

            declare @REVENUEPAYMENTMETHODID uniqueidentifier
            select top 1 
                @AMOUNT = AMOUNT,
                @REVENUEPAYMENTMETHODID = ID,
                @ORIGINALPAYMETHODCODE = PAYMENTMETHODCODE 
            from dbo.REVENUEPAYMENTMETHOD
            where REVENUEID = @ID

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


            declare @FIELDSCHANGED bit;
            set @FIELDSCHANGED = 0;

            -- check to see if the date changed

            if (select count(REVENUE.ID) from dbo.REVENUE where REVENUE.ID = @ID and DATE = @DATE) = 0
                set @FIELDSCHANGED = 1;

            update dbo.REVENUE
            set    GIVENANONYMOUSLY = @GIVENANONYMOUSLY, 
                DATE = @DATE,
                DONOTRECEIPT = @DONOTRECEIPT,
                CHANGEDBYID = @CHANGEAGENTID, 
                DATECHANGED = @CHANGEDATE
            where ID = @ID;

            if @FIELDSCHANGED = 1
                exec dbo.USP_REVENUE_UPDATERERECEIPTS @ID, @CHANGEAGENTID, @CHANGEDATE;

            if @REVENUEPAYMENTMETHODID is null
            begin
                set @REVENUEPAYMENTMETHODID = newid();
                insert into dbo.REVENUEPAYMENTMETHOD
                    (ID, REVENUEID, PAYMENTMETHODCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values (@REVENUEPAYMENTMETHODID, @ID, @PAYMENTMETHODCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
            end
            else
                update dbo.REVENUEPAYMENTMETHOD
                set    PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
                    CHANGEDBYID = @CHANGEAGENTID, 
                    DATECHANGED = @CHANGEDATE 
                where ID = @REVENUEPAYMENTMETHODID;

            exec dbo.USP_REVENUE_UPDATEPAYMENTDETAILS @REVENUEPAYMENTMETHODID, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER, @CONSTITUENTACCOUNTID, @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @PROPERTYSUBTYPECODEID, @GIFTINKINDSUBTYPECODEID, @SALEDATE, @SALEAMOUNT, @BROKERFEE, @SALEPOSTDATE, @SALEPOSTSTATUSCODE, @CHANGEAGENTID,@CHANGEDATE, 0, @OTHERPAYMENTMETHODCODEID, @DIRECTDEBITRESULTCODE, @LOWPRICE, @HIGHPRICE, @AMOUNT,@ORIGINALPAYMENTMETHODCODE=@ORIGINALPAYMETHODCODE;


            exec dbo.USP_REVENUEREFERENCE_EDIT @ID, @REFERENCE, @CHANGEAGENTID;            

            return 0;