USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEADJUST_3

The load procedure used by the edit dataform template "Posted Pledge Edit Form 3"

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(255)	INOUT	Constituent
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@SENDPLEDGEREMINDER	bit	INOUT	Send pledge reminders
@SPLITS	xml	INOUT	Designations
@FREQUENCYCODE	tinyint	INOUT	Frequency
@NUMBEROFINSTALLMENTS	int	INOUT	No. installments remaining
@NEXTTRANSACTIONDATE	datetime	INOUT	Next installment date
@AMOUNTPAID	money	INOUT	Amount paid
@INSTALLMENTS	xml	INOUT
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@BENEFITS	xml	INOUT	Benefits
@BENEFITSWAIVED	bit	INOUT	Benefits waived
@GIVENANONYMOUSLY	bit	INOUT	Pledge is anonymous
@MAILINGID	uniqueidentifier	INOUT	Mailing
@CHANNELCODEID	uniqueidentifier	INOUT	Channel
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@PLEDGESUBTYPEID	uniqueidentifier	INOUT	Subtype
@MAXSOFTCREDITAMOUNT	money	INOUT	Max soft credit amount
@MAXSOLICITORAMOUNT	money	INOUT	Max solicitor amount
@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.
@MAINSINGLEDESIGNATIONID	uniqueidentifier	INOUT	Designation
@FINDERNUMBERVALID	bit	INOUT	FINDERNUMBERVALID
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity
@OPPORTUNITYCONSTITUENTNAME	nvarchar(154)	INOUT
@OPPORTUNITYASKDATE	datetime	INOUT
@OPPORTUNITYASKAMOUNT	money	INOUT
@LASTPAYMENTSEQUENCE	int	INOUT	Installment sequence of last payment
@REFERENCE	nvarchar(255)	INOUT	Reference
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@ADJUSTMENTDATE	datetime	INOUT	Adjusted Date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjusted Post Date
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Post Status Code
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment reason

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEADJUST_3
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @CONSTITUENTID uniqueidentifier = null output,
                        @CONSTITUENTNAME nvarchar(255) = null output,
                        @DATE datetime = null output,
                        @AMOUNT money = null output,
                        @POSTSTATUSCODE tinyint = null output,
                        @POSTDATE datetime = null output,
                        @SENDPLEDGEREMINDER bit = null output,
                        @SPLITS xml = null output,
                        @FREQUENCYCODE tinyint = null output,
                        @NUMBEROFINSTALLMENTS int = null output,
                        @NEXTTRANSACTIONDATE datetime = null output,
                        @AMOUNTPAID money = null output,
                        @INSTALLMENTS xml = null output,
                        @FINDERNUMBER bigint = 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,
                        @DONOTACKNOWLEDGE bit = null output,
                        @PLEDGESUBTYPEID uniqueidentifier = null output,
                        @MAXSOFTCREDITAMOUNT money = null output,
                        @MAXSOLICITORAMOUNT money = null output,
                        @TSLONG bigint = 0 output,
                        @MAINSINGLEDESIGNATIONID uniqueidentifier = null output,
                        @FINDERNUMBERVALID bit = null output,
                        @OPPORTUNITYID uniqueidentifier = null output,
                        @OPPORTUNITYCONSTITUENTNAME nvarchar(154) = null output,
                        @OPPORTUNITYASKDATE datetime = null output,
                        @OPPORTUNITYASKAMOUNT money = null output,
                        @LASTPAYMENTSEQUENCE int = null output,
                        @REFERENCE nvarchar(255) = null output,
                        @CATEGORYCODEID uniqueidentifier = null output,
                        @ADJUSTMENTDATE datetime = null output,
                        @ADJUSTMENTPOSTDATE datetime = null output,
                        @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
                        @ADJUSTMENTREASON nvarchar(300) = null output
                    )
                    as
                    begin
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        declare @LASTINSTALLMENT uniqueidentifier;

                        select  @ADJUSTMENTDATE = getdate(),
                                @ADJUSTMENTPOSTDATE = getdate(),
                                @ADJUSTMENTPOSTSTATUSCODE = 0

                        select
                            @ADJUSTMENTDATE = DATE,
                            @ADJUSTMENTPOSTDATE = POSTDATE,
                            @ADJUSTMENTPOSTSTATUSCODE = 1,
                            @ADJUSTMENTREASON = REASON
                        from dbo.ADJUSTMENT
                        where REVENUEID = @ID and POSTSTATUSCODE = 1

                        select 
                            @DATALOADED = 1,
                            @CONSTITUENTID = CONSTITUENT.ID,
                            @CONSTITUENTNAME = CONSTITUENT.NAME,
                            @DATE = REVENUE.DATE,
                            @AMOUNT = REVENUE.AMOUNT,
                            @POSTSTATUSCODE = 0,
                            @MAXSOFTCREDITAMOUNT = 0,
                            @MAXSOLICITORAMOUNT = coalesce((select max(AMOUNT) from dbo.REVENUESOLICITOR where REVENUESPLITID = REVENUE.ID), 0),
                            @POSTDATE = REVENUE.POSTDATE,
                            @SENDPLEDGEREMINDER = REVENUESCHEDULE.SENDPLEDGEREMINDER,
                            @FREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE,
                            @NUMBEROFINSTALLMENTS = REVENUESCHEDULE.NUMBEROFINSTALLMENTS,
                            @NEXTTRANSACTIONDATE = coalesce(REVENUESCHEDULE.NEXTTRANSACTIONDATE, REVENUESCHEDULE.STARTDATE),
                            @TSLONG = REVENUE.TSLONG,                            
                            @FINDERNUMBER = FINDERNUMBER,
                            @SOURCECODE = SOURCECODE,
                            @APPEALID = APPEALID,
                            @BENEFITSWAIVED = BENEFITSWAIVED,
                            @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
                            @MAILINGID = MAILINGID,
                            @CHANNELCODEID = CHANNELCODEID,
                            @DONOTACKNOWLEDGE = DONOTACKNOWLEDGE,
                            @PLEDGESUBTYPEID = PLEDGESUBTYPEID,
                            @REFERENCE = REVENUEREFERENCE.REFERENCE,
                            @CATEGORYCODEID = (select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID where REVENUEID = REVENUE.ID)
                        from
                            dbo.REVENUE
                        inner join
                            dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                        inner join
                            dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                        left join
                            dbo.REVENUEREFERENCE on REVENUE.ID = REVENUEREFERENCE.ID
                        where
                            REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 1;

                        if @DATALOADED = 1
                        begin
                            select top 1
                                @OPPORTUNITYID = RO.OPPORTUNITYID,
                                @OPPORTUNITYCONSTITUENTNAME = OC.NAME,
                                @OPPORTUNITYASKDATE = O.ASKDATE,
                                @OPPORTUNITYASKAMOUNT = O.ASKAMOUNT
                            from
                                dbo.REVENUESPLIT
                            left outer join
                                dbo.REVENUEOPPORTUNITY RO on RO.ID = REVENUESPLIT.ID
                            left outer join
                                dbo.OPPORTUNITY O on O.ID = RO.OPPORTUNITYID
                            left outer join
                                dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                            left outer join
                                dbo.CONSTITUENT OC on OC.ID = PP.PROSPECTID
                            where REVENUESPLIT.REVENUEID = @ID;

                            set @AMOUNTPAID = @AMOUNT - dbo.UFN_PLEDGE_GETBALANCE(@ID);            

                            set @LASTINSTALLMENT = dbo.UFN_INSTALLMENT_GETLASTINSTALLMENTPAID(@ID);

                            if @LASTINSTALLMENT is null 
                                begin
                                    set @LASTPAYMENTSEQUENCE = 0;
                                end
                            else
                                begin
                                    set @LASTPAYMENTSEQUENCE = (select SEQUENCE from dbo.INSTALLMENT where ID = @LASTINSTALLMENT);
                                end

                            set @INSTALLMENTS = dbo.UFN_INSTALLMENT_GETINSTALLMENTS_TOITEMLISTXML(@ID);

                            -- set @NUMBEROFINSTALLMENTS to the number of unpaid installments

                            select @NUMBEROFINSTALLMENTS = @NUMBEROFINSTALLMENTS - @LASTPAYMENTSEQUENCE


                            set @SPLITS = dbo.UFN_REVENUE_GETSPLITS_TOITEMLISTXML(@ID);
                            set @INSTALLMENTS = dbo.UFN_INSTALLMENT_GETINSTALLMENTS_TOITEMLISTXML(@ID);
                            set @BENEFITS = dbo.UFN_REVENUE_GETBENEFITS_TOITEMLISTXML(@ID);

                            if @FINDERNUMBER = 0
                                set @FINDERNUMBER = null;

                            if (not @FINDERNUMBER is null)
                                set @FINDERNUMBERVALID = 1;
                            else
                                set @FINDERNUMBERVALID = 0;

                        end

                        return 0;

                    end