USP_DATAFORMTEMPLATE_EDITLOAD_PAYMENT2

Loads payment information.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(700)	INOUT	Constituent
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@APPLICATIONCODE	tinyint	INOUT	Application
@RECEIPTAMOUNT	money	INOUT	Receipt amount
@REVENUESTREAMS	xml	INOUT	Revenue streams
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@BENEFITS	xml	INOUT	Money benefits
@BENEFITSWAIVED	bit	INOUT	Benefits waived
@GIVENANONYMOUSLY	bit	INOUT	Payment is anonymous
@MAILINGID	uniqueidentifier	INOUT	Mailing
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@DONOTRECEIPT	bit	INOUT	Do not receipt
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@REFERENCE	nvarchar(255)	INOUT	Reference
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@CANHAVEUNAPPLIEDMG	bit	INOUT
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@FINDERNUMBER	bigint	INOUT	Finder number
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT	Other method
@CHECKDATE	UDT_FUZZYDATE	INOUT	Check date
@CHECKNUMBER	nvarchar(20)	INOUT	Check number
@REFERENCEDATE	UDT_FUZZYDATE	INOUT	Reference date
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference number
@CARDHOLDERNAME	nvarchar(255)	INOUT	Name on card
@CREDITCARDNUMBER	nvarchar(4)	INOUT	Card number
@CREDITTYPECODEID	uniqueidentifier	INOUT	Card type
@AUTHORIZATIONCODE	nvarchar(20)	INOUT	Authorization code
@EXPIRESON	UDT_FUZZYDATE	INOUT	Expires on
@ISSUER	nvarchar(100)	INOUT	Issuer
@NUMBEROFUNITS	decimal(20, 3)	INOUT	Number of units
@SYMBOL	nvarchar(25)	INOUT	Symbol
@MEDIANPRICE	decimal(19, 4)	INOUT	Median price
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	INOUT	Subtype
@PROPERTYSUBTYPECODEID	uniqueidentifier	INOUT	Subtype
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT	Account
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT	Result code
@LOWPRICE	decimal(19, 4)	INOUT	Low price
@HIGHPRICE	decimal(19, 4)	INOUT	High price
@HASSOLDSTOCK	bit	INOUT	Has sold stock
@HASSOLDPROPERTY	bit	INOUT	Has sold property
@GIFTINKINDITEMNAME	nvarchar(100)	INOUT	Item name
@GIFTINKINDDISPOSITIONCODE	tinyint	INOUT	Disposition
@GIFTINKINDNUMBEROFUNITS	int	INOUT	Number of units
@GIFTINKINDFAIRMARKETVALUE	money	INOUT	Fair market value per unit
@GIFTINKINDUSEAPPRAISALTHRESHOLD	bit	INOUT
@GIFTINKINDAPPRAISALTHRESHOLD	money	INOUT
@GIFTINKINDGIFTAPPRAISED	bit	INOUT
@DIRECTDEBITISREJECTED	bit	INOUT	Rejected
@PERCENTAGEBENEFITS	xml	INOUT	Percent benefits
@HASCREDITCARDBEENCHARGED	bit	INOUT
@BASECURRENCYID	uniqueidentifier	INOUT
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT
@BASEEXCHANGERATEID	uniqueidentifier	INOUT
@EXCHANGERATE	decimal(20, 8)	INOUT
@HADSPOTRATE	bit	INOUT
@BASEDECIMALDIGITS	tinyint	INOUT
@BASEROUNDINGTYPECODE	tinyint	INOUT
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT
@HASMATCHINGGIFTCLAIM	bit	INOUT
@ADJUSTMATCHINGGIFTCLAIMS	tinyint	INOUT
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT
@UPDATEGIFTFEEOPTION	tinyint	INOUT
@UPDATETRIBUTEOPTION	tinyint	INOUT
@HASTRIBUTE	bit	INOUT
@VALIDATETRIBUTES	bit	INOUT
@ALLOWGLDISTRIBUTIONS	bit	INOUT
@HASSOLDGIFTINKIND	bit	INOUT
@SEPAMANDATEID	uniqueidentifier	INOUT

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PAYMENT2
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @CONSTITUENTID uniqueidentifier = null output,    
                @CONSTITUENTNAME nvarchar(700) = null output,
                @DATE datetime = null output,
                @AMOUNT money = null output,
                @APPLICATIONCODE tinyint = null output,
                @RECEIPTAMOUNT money = null output,
                @REVENUESTREAMS xml = null output,
                @SOURCECODE nvarchar(50) = null output,
                @APPEALID uniqueidentifier = null output,
                @BENEFITS xml = null output,
                @BENEFITSWAIVED bit = null output,
                @GIVENANONYMOUSLY bit = null output,
                @MAILINGID uniqueidentifier = null output,
                @CHANNELCODEID uniqueidentifier = null output,
                @DONOTRECEIPT bit = null output,
                @DONOTACKNOWLEDGE bit = null output,
                @REFERENCE nvarchar(255) = null output,
                @POSTSTATUSCODE tinyint = null output,
                @POSTDATE datetime = null output,
                @CANHAVEUNAPPLIEDMG bit = null output,
                @TSLONG bigint = 0 output,
                @FINDERNUMBER bigint = null output,
                @PAYMENTMETHODCODE tinyint = null output,
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = 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,
                @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
                @LOWPRICE decimal(19,4) = null output,
                @HIGHPRICE decimal(19,4) = null output,
                @HASSOLDSTOCK bit = null output,
                @HASSOLDPROPERTY bit = null output,
                @GIFTINKINDITEMNAME nvarchar(100) = null output,
                @GIFTINKINDDISPOSITIONCODE tinyint = null output,
                @GIFTINKINDNUMBEROFUNITS int = null output,
                @GIFTINKINDFAIRMARKETVALUE money = null output,
                @GIFTINKINDUSEAPPRAISALTHRESHOLD bit = null output,
                @GIFTINKINDAPPRAISALTHRESHOLD money = null output,
                @GIFTINKINDGIFTAPPRAISED bit = null output,
                @DIRECTDEBITISREJECTED bit = null output,
                @PERCENTAGEBENEFITS xml = null output,
                @HASCREDITCARDBEENCHARGED bit = null output,
                @BASECURRENCYID uniqueidentifier = null output,
                @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                @BASEEXCHANGERATEID uniqueidentifier = null output,
                @EXCHANGERATE decimal(20,8) = null output,
                @HADSPOTRATE bit = null output,
                @BASEDECIMALDIGITS tinyint = null output,
                @BASEROUNDINGTYPECODE tinyint = null output,
                @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
                @HASMATCHINGGIFTCLAIM bit = null output,
                @ADJUSTMATCHINGGIFTCLAIMS tinyint = null output,
                @PDACCOUNTSYSTEMID uniqueidentifier = null output,
                @UPDATEGIFTFEEOPTION tinyint = null output,
                @UPDATETRIBUTEOPTION tinyint = null output,
                @HASTRIBUTE bit = null output,
                @VALIDATETRIBUTES bit = null output,
                @ALLOWGLDISTRIBUTIONS bit = null output,
                @HASSOLDGIFTINKIND bit = null output,
                @SEPAMANDATEID uniqueidentifier = null output
            )
            as
            set nocount on;

            set @PDACCOUNTSYSTEMID = (select PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE where PDACCOUNTSYSTEMFORREVENUE.ID = @ID);
            set @ALLOWGLDISTRIBUTIONS  = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

            exec dbo.USP_PAYMENT_LOAD 
                @ID, @DATALOADED output, @CONSTITUENTID output,    @CONSTITUENTNAME output,
                @DATE output, @AMOUNT output, @APPLICATIONCODE output, @RECEIPTAMOUNT output, 
                @REVENUESTREAMS output, @SOURCECODE output, @POSTSTATUSCODE output, @POSTDATE output,
                @APPEALID output, @BENEFITS output, @BENEFITSWAIVED output, 
                @GIVENANONYMOUSLY output, @MAILINGID output, @CHANNELCODEID output, 
                @DONOTRECEIPT output, @REFERENCE output, @TSLONG output, @DONOTACKNOWLEDGE output, 
                @FINDERNUMBER output, @PERCENTAGEBENEFITS output,
                @BASECURRENCYID output, @TRANSACTIONCURRENCYID output,
                @BASEEXCHANGERATEID output, @EXCHANGERATE output, @HADSPOTRATE output, 
                @BASEDECIMALDIGITS output, @BASEROUNDINGTYPECODE output, @TRANSACTIONCURRENCYDESCRIPTION output;

            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,
                @GIFTINKINDITEMNAME = @GIFTINKINDITEMNAME output,
                @GIFTINKINDDISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE output,
                @GIFTINKINDNUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS output,
                @GIFTINKINDFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE output,
                @DIRECTDEBITISREJECTED = @DIRECTDEBITISREJECTED output,
                @HASCREDITCARDBEENCHARGED = @HASCREDITCARDBEENCHARGED output,
                @SEPAMANDATEID = @SEPAMANDATEID output;

            select @HASMATCHINGGIFTCLAIM = 1
            from dbo.REVENUEMATCHINGGIFT
                left join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUEMATCHINGGIFT.ID
            where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = @ID
                and REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID is not null
                and INSTALLMENTSPLITPAYMENT.ID is null;

            select @HASTRIBUTE = count(1)
            from dbo.REVENUETRIBUTE
            where REVENUETRIBUTE.REVENUEID = @ID;
            set @UPDATETRIBUTEOPTION = 0;

            set @ADJUSTMATCHINGGIFTCLAIMS = 3;
            set @UPDATEGIFTFEEOPTION = 0;

            select @CANHAVEUNAPPLIEDMG = ISORGANIZATION
            from dbo.CONSTITUENT
            where ID = @CONSTITUENTID;

            set @HASSOLDSTOCK = 0;
            if @PAYMENTMETHODCODE = 4
            begin
                if exists (    select 1 from dbo.STOCKSALE 
                            inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                            where REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                    set @HASSOLDSTOCK = 1;
            end

            set @HASSOLDPROPERTY = 0;
            if @PAYMENTMETHODCODE = 5
            begin
                if exists (    select 1 from dbo.PROPERTYDETAIL 
                            inner join dbo.REVENUEPAYMENTMETHOD on PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                            where PROPERTYDETAIL.ISSOLD = 1 and REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                    set @HASSOLDPROPERTY = 1;                    
            end

            set @HASSOLDGIFTINKIND = 0;
            if @PAYMENTMETHODCODE = 6
            begin
                if exists (    select 1 from dbo.GIFTINKINDSALE 
                            inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
                            where REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                    set @HASSOLDGIFTINKIND = 1;
            end            

            select top 1
                @GIFTINKINDUSEAPPRAISALTHRESHOLD = USEAPPRAISALTHRESHOLD,
                @GIFTINKINDAPPRAISALTHRESHOLD = APPRAISALTHRESHOLD
            from
                dbo.GIFTINKINDAPPRAISALSETTINGS

            if exists (    select top(1) 1 
                        from dbo.GIFTINKINDAPPRAISAL inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
                        where REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                set @GIFTINKINDGIFTAPPRAISED = 1;
            else
                set @GIFTINKINDGIFTAPPRAISED = 0;

            set @VALIDATETRIBUTES = 1;