USP_BILLINGINDIVIDUALSUMMARYOVERVIEW

Returns billing summary information for a given billing individual.
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@BILLINGCYCLEID	uniqueidentifier	INOUT
@BILLINGSTARTDATE	datetime	INOUT
@BILLINGENDDATE	datetime	INOUT
@TOTALBILLED	money	INOUT
@BALANCE	money	INOUT
@LASTPAYMENTID	uniqueidentifier	INOUT
@LASTPAYMENTAMOUNT	nvarchar(100)	INOUT
@LASTPAYMENTDATE	datetime	INOUT
@LASTPAYMENTPAYERID	uniqueidentifier	INOUT
@LASTPAYMENTPAYERNAME	nvarchar(200)	INOUT
@LASTPAYMENTPAYERNAMESTUDENT	nvarchar(200)	INOUT
@LASTPAYMENTMETHODCODE	tinyint	INOUT
@LASTPAYMENTMETHOD	nvarchar(50)	INOUT
@LASTPAYMENTMETHODINFO	nvarchar(50)	INOUT
Definition

Copy


            CREATE procedure dbo.USP_BILLINGINDIVIDUALSUMMARYOVERVIEW
            (
                @CONSTITUENTID uniqueidentifier,
                @BILLINGCYCLEID uniqueidentifier output,
                @BILLINGSTARTDATE datetime output,
                @BILLINGENDDATE datetime output,
                @TOTALBILLED money = null output,
                @BALANCE money = null output,
                @LASTPAYMENTID uniqueidentifier = null output,
                @LASTPAYMENTAMOUNT nvarchar(100) = null output,
                @LASTPAYMENTDATE datetime = null output,
                @LASTPAYMENTPAYERID uniqueidentifier = null output,
                @LASTPAYMENTPAYERNAME nvarchar(200) = null output,
                @LASTPAYMENTPAYERNAMESTUDENT nvarchar(200) = null output,
                @LASTPAYMENTMETHODCODE tinyint = null output,
                @LASTPAYMENTMETHOD nvarchar(50) = null output,
                @LASTPAYMENTMETHODINFO nvarchar(50) = null output
            )
            as
            begin
                set nocount on;

                set @BILLINGSTARTDATE = null;
                set @BILLINGENDDATE = null;

                -- The empty guid or a null means find the billing cycle for me

                if ((@BILLINGCYCLEID is null) or (@BILLINGCYCLEID = '00000000-0000-0000-0000-000000000000'))
                begin
                    -- Get the current billing cycle ID

                    select top 1 @BILLINGCYCLEID = BILLINGCYCLES.ACADEMICYEARID
                    from dbo.UFN_GETBILLINGCYCLESBYINDIVIDUAL(@CONSTITUENTID) as BILLINGCYCLES
                    where (BILLINGCYCLES.BILLINGSTARTDATE <= dbo.UFN_DATE_GETEARLIESTTIME(getdate())) and
                            (BILLINGCYCLES.BILLINGENDDATE >= dbo.UFN_DATE_GETEARLIESTTIME(getdate()));
                end

                -- The ONE guid means all dates, an empty guid means find dates for me.

                if (@BILLINGCYCLEID != '00000000-0000-0000-0000-000000000001')
                begin
                    select @BILLINGSTARTDATE = BILLINGSTARTDATE,
                            @BILLINGENDDATE = BILLINGENDDATE
                    from dbo.ACADEMICYEAR
                    where ACADEMICYEAR.ID = @BILLINGCYCLEID;
                end

                -- Get summary information

                select @TOTALBILLED = dbo.UFN_CHARGETOTALINDIVIDUAL(@CONSTITUENTID, @BILLINGSTARTDATE, @BILLINGENDDATE),
                       @BALANCE = dbo.UFN_BILLINGBALANCEINDIVIDUAL(@CONSTITUENTID, @BILLINGSTARTDATE, @BILLINGENDDATE);

                -- Get last payment information

                select top 1 @LASTPAYMENTID = FINANCIALTRANSACTION.ID,
                             @LASTPAYMENTAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                             @LASTPAYMENTDATE = FINANCIALTRANSACTION.DATE,
                             @LASTPAYMENTPAYERID = RECEIVABLEPAYMENT.CONSTITUENTID,
                             @LASTPAYMENTPAYERNAME = case when dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1 then
                                                            ''
                                                        else
                                                            CONSTITUENT.NAME
                                                        end,
                             @LASTPAYMENTPAYERNAMESTUDENT = case when dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1 then
                                                                CONSTITUENT.NAME
                                                            else
                                                                ''
                                                            end,
                             @LASTPAYMENTMETHODCODE = RECEIVABLEPAYMENT.PAYMENTMETHODCODE,
                             @LASTPAYMENTMETHOD = RECEIVABLEPAYMENT.PAYMENTMETHOD,
                           @LASTPAYMENTMETHODINFO = case when RECEIVABLEPAYMENT.PAYMENTMETHODCODE = 1 then
                                                            RECEIVABLEPAYMENTCHECK.CHECKNUMBER
                                                        else
                                                            ''
                                                        end
                from dbo.FINANCIALTRANSACTION
                    inner join dbo.RECEIVABLEPAYMENT
                        on FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENT.ID
                    inner join dbo.CONSTITUENT
                        on RECEIVABLEPAYMENT.CONSTITUENTID = CONSTITUENT.ID
                    left outer join dbo.RECEIVABLEPAYMENTCHECK
                        on RECEIVABLEPAYMENTCHECK.ID = RECEIVABLEPAYMENT.ID
                where (FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID) and
                        ((@BILLINGENDDATE is null) or (FINANCIALTRANSACTION.DATE <= @BILLINGENDDATE)) and
                        ((@BILLINGSTARTDATE is null) or (FINANCIALTRANSACTION.DATE >= @BILLINGSTARTDATE)) and
                        (FINANCIALTRANSACTION.DELETEDON IS NULL)
                order by FINANCIALTRANSACTION.DATE desc,
                            FINANCIALTRANSACTION.DATEADDED desc;

                return 0;
            end