USP_REVENUEPAYMENT_LOAD

Stored procedure to load a payment.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@DATE	datetime	INOUT
@AMOUNT	money	INOUT
@PAYMENTMETHODCODE	tinyint	INOUT
@CHECKDATE	UDT_FUZZYDATE	INOUT
@CHECKNUMBER	nvarchar(20)	INOUT
@REFERENCEDATE	UDT_FUZZYDATE	INOUT
@REFERENCENUMBER	nvarchar(20)	INOUT
@CARDHOLDERNAME	nvarchar(255)	INOUT
@CREDITCARDNUMBER	nvarchar(4)	INOUT
@CREDITTYPECODEID	uniqueidentifier	INOUT
@AUTHORIZATIONCODE	nvarchar(20)	INOUT
@EXPIRESON	UDT_FUZZYDATE	INOUT
@ISSUER	nvarchar(100)	INOUT
@NUMBEROFUNITS	decimal(20, 3)	INOUT
@SYMBOL	nvarchar(25)	INOUT
@MEDIANPRICE	decimal(19, 4)	INOUT
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	INOUT
@PROPERTYSUBTYPECODEID	uniqueidentifier	INOUT
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT
@POSTSTATUSCODE	tinyint	INOUT
@POSTDATE	datetime	INOUT
@FINDERNUMBER	bigint	INOUT
@GIVENANONYMOUSLY	bit	INOUT
@DONOTRECEIPT	bit	INOUT
@RECEIPTGENERATED	bit	INOUT
@TSLONG	bigint	INOUT
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT
@REFERENCE	nvarchar(255)	INOUT
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT
@LOWPRICE	decimal(19, 4)	INOUT
@HIGHPRICE	decimal(19, 4)	INOUT

Definition

Copy



            CREATE procedure dbo.USP_REVENUEPAYMENT_LOAD
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @CONSTITUENTID uniqueidentifier = null output,    
                @DATE datetime = null output,
                @AMOUNT money = null output,                    
                @PAYMENTMETHODCODE tinyint = null output,
                @CHECKDATE dbo.UDT_FUZZYDATE = null output,
                @CHECKNUMBER nvarchar(20) = null output,
                @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
                @REFERENCENUMBER nvarchar(20) = null output,                    
                @CARDHOLDERNAME nvarchar(255) = null output,
                @CREDITCARDNUMBER nvarchar(4) = null output,
                @CREDITTYPECODEID uniqueidentifier = null output,
                @AUTHORIZATIONCODE nvarchar(20) = null output,
                @EXPIRESON dbo.UDT_FUZZYDATE = null output,
                @ISSUER nvarchar(100) = null output,
                @NUMBEROFUNITS decimal(20,3) = null output,
                @SYMBOL nvarchar(25) = null output,
                @MEDIANPRICE decimal(19,4) = null output,
                @GIFTINKINDSUBTYPECODEID uniqueidentifier = null output,
                @PROPERTYSUBTYPECODEID uniqueidentifier = null output,                    
                @CONSTITUENTACCOUNTID uniqueidentifier = null output,
                @POSTSTATUSCODE tinyint = null output,
                @POSTDATE datetime = null output,
                @FINDERNUMBER bigint = null output,
                @GIVENANONYMOUSLY bit = null output,
                @DONOTRECEIPT bit = null output,
                @RECEIPTGENERATED bit = null output,
                @TSLONG bigint = 0 output,
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
                @REFERENCE nvarchar(255) = null output,
                @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
                @LOWPRICE decimal(19,4) = null output,
                @HIGHPRICE decimal(19,4) = null output
            )
            as
            set nocount on;

            declare @REVENUEID uniqueidentifier;
            set @DATALOADED = 0;
            set @TSLONG = 0;

            select top 1
                @REVENUEID = REVENUE.ID,
                @DATALOADED = 1,
                @CONSTITUENTID = REVENUE.CONSTITUENTID,
                @FINDERNUMBER = REVENUE.FINDERNUMBER,
                @DATE = REVENUE.DATE,
                @AMOUNT = REVENUE.AMOUNT,
                @POSTSTATUSCODE = case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end,
                @POSTDATE = REVENUE.POSTDATE,
                @DONOTRECEIPT = DONOTRECEIPT,
                @RECEIPTGENERATED = case when REVENUERECEIPT.ID is null then 0 else 1 end,
                @GIVENANONYMOUSLY = REVENUE.GIVENANONYMOUSLY,
                @TSLONG = REVENUE.TSLONG,
                @REFERENCE = REVENUEREFERENCE.REFERENCE
            from dbo.REVENUE
            left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
            left join dbo.REVENUERECEIPT on REVENUERECEIPT.REVENUEID = REVENUE.ID
            left join dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID
            where REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 0

            exec dbo.USP_REVENUE_GETPAYMENTDETAILS
                @REVENUEID = @ID,
                @PAYMENTMETHODCODE = @PAYMENTMETHODCODE output,
                @CHECKDATE = @CHECKDATE output,
                @CHECKNUMBER = @CHECKNUMBER output,
                @REFERENCEDATE = @REFERENCEDATE output,
                @REFERENCENUMBER = @REFERENCENUMBER output,                    
                @CARDHOLDERNAME = @CARDHOLDERNAME output,
                @CREDITCARDNUMBER = @CREDITCARDNUMBER output,
                @CREDITTYPECODEID = @CREDITTYPECODEID output,
                @AUTHORIZATIONCODE = @AUTHORIZATIONCODE output,
                @EXPIRESON = @EXPIRESON output,
                @ISSUER = @ISSUER output,
                @NUMBEROFUNITS = @NUMBEROFUNITS output,
                @SYMBOL = @SYMBOL output,
                @MEDIANPRICE = @MEDIANPRICE output,
                @GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID output,
                @PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID output,                    
                @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output,
                @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID output,
                @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE output,
                @LOWPRICE = @LOWPRICE output,
                @HIGHPRICE = @HIGHPRICE output

            return 0;