USP_STANDINGORDERSIMPORTPROCESS_VERIFYREVENUEWITHREFERENCE

Verifies revenue information for the standing orders import process using reference number.
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@ACCOUNTID	uniqueidentifier	IN
@REFERENCENUMBER	nvarchar(20)	IN
@AMOUNT	money	IN
@CUTOFFDATE	datetime	IN
@PAYFUTURE	bit	IN
@PAYMENTCURRENCYID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure USP_STANDINGORDERSIMPORTPROCESS_VERIFYREVENUEWITHREFERENCE
            (
                @CONSTITUENTID uniqueidentifier,
                @ACCOUNTID uniqueidentifier,
                @REFERENCENUMBER nvarchar(20),
                @AMOUNT money,
                @CUTOFFDATE datetime,
                @PAYFUTURE bit,
                @PAYMENTCURRENCYID uniqueidentifier
            )
            as
                set nocount on;

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate();

                declare @MULTICURRENCYENABLED bit 
                set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');

                if @PAYMENTCURRENCYID is null 
                    set @PAYMENTCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();   

                with INSTALLMENTS_CTE (
                    ID, 
                    TRANSACTIONTYPECODE, 
                    TRANSACTIONAMOUNT, 
                    TRANSACTIONCURRENCYID,
                    BASECURRENCYID
                ) as (
                    select 
                        REVENUE.ID, 
                        REVENUE.TRANSACTIONTYPECODE,
                        REVENUE.TRANSACTIONAMOUNT,
                        REVENUE.TRANSACTIONCURRENCYID,
                        REVENUE.BASECURRENCYID
                    from 
                        REVENUESCHEDULESTANDINGORDERPAYMENT
                    inner join REVENUE on
                          REVENUESCHEDULESTANDINGORDERPAYMENT.ID = REVENUE.ID
                    inner join REVENUEPAYMENTMETHOD on
                          REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID

                    where 
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 11 and 
                        REVENUE.CONSTITUENTID = @CONSTITUENTID and
                        REVENUESCHEDULESTANDINGORDERPAYMENT.CONSTITUENTACCOUNTID = @ACCOUNTID and
                        dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(REVENUE.ID) = @REFERENCENUMBER
                )
                    select 
                         INSTALLMENTS_CTE.ID, 
                         INSTALLMENTS_CTE.TRANSACTIONTYPECODE,
                         INSTALLMENTS_CTE.BASECURRENCYID
                    from 
                        INSTALLMENTS_CTE
                    left outer join INSTALLMENT on
                        INSTALLMENT.REVENUEID = INSTALLMENTS_CTE.ID
                    inner join REVENUESCHEDULE on
                          REVENUESCHEDULE.ID = INSTALLMENTS_CTE.ID
                    where
                        (@MULTICURRENCYENABLED = 0 or 
                         INSTALLMENTS_CTE.TRANSACTIONCURRENCYID = @PAYMENTCURRENCYID) and
                        (
                            (
                            --Pledge

                                ((INSTALLMENT.DATE <= @CUTOFFDATE) or (@PAYFUTURE = 1)) and
                                (INSTALLMENTS_CTE.TRANSACTIONTYPECODE = 1) and 

                                (INSTALLMENT.SEQUENCE = 
                                    (select COALESCE(MAX(SEQUENCE),0) + 1 as SEQUENCE
                                     from
                                        dbo.INSTALLMENT
                                         inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID 
                                        left join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                                        left join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                     where
                                        INSTALLMENT.REVENUEID = INSTALLMENTS_CTE.ID
                                        and
                                        (
                                            INSTALLMENTSPLITPAYMENT.ID is not null
                                            or INSTALLMENTSPLITWRITEOFF.WRITEOFFID is not null
                                        )
                                    )
                                ) and

                                (INSTALLMENT.TRANSACTIONAMOUNT = @AMOUNT)

                            )
                            or ( 
                            --Recurring gift


                                REVENUESCHEDULE.STATUSCODE = 0 and --Active

                                (INSTALLMENTS_CTE.TRANSACTIONTYPECODE = 2) and
                                ((REVENUESCHEDULE.NEXTTRANSACTIONDATE <= @CUTOFFDATE) or (@PAYFUTURE = 1)) and

                                (INSTALLMENTS_CTE.TRANSACTIONAMOUNT = @AMOUNT)
                            )
                        )