UFN_BILLINGINDIVIDUALACTIVITYDATALIST

Returns activity for the billing activity datalist filtered by a given set of filters, used in open item and balance forward systems.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@TYPECODE	tinyint	IN
@STATUSCODE	tinyint	IN
@TRANSACTIONDATERANGECODE	tinyint	IN
@TRANSACTIONDATERANGEBILLINGCYCLEID	uniqueidentifier	IN
@TRANSACTIONDUEDATERANGECODE	tinyint	IN
@TRANSACTIONDUEDATERANGEBILLINGCYCLEID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_BILLINGINDIVIDUALACTIVITYDATALIST
            (
                @CONSTITUENTID uniqueidentifier,
                @TYPECODE tinyint = 0,
                @STATUSCODE tinyint = 0,
                @TRANSACTIONDATERANGECODE tinyint = 0,
                @TRANSACTIONDATERANGEBILLINGCYCLEID uniqueidentifier = null,
                @TRANSACTIONDUEDATERANGECODE tinyint = 0,
                @TRANSACTIONDUEDATERANGEBILLINGCYCLEID uniqueidentifier = null
            )
            returns @TRANSACTIONS table
                (
                    ID uniqueidentifier,
                    DATE datetime,
                    DUEDATE datetime,
                    DAYSLATE integer,
                    DUEDATEDISPLAY nvarchar(50),
                    TYPE nvarchar(50),
                    TYPECODE tinyint,
                    DESCRIPTION nvarchar(200),
                    AMOUNT money,
                    AMOUNTREMAINING money,
                    PAIDBY nvarchar(100),
                    STATUSCODE tinyint,
                    STATUS nvarchar(100)
                )
            begin

                declare @BILLINGSTARTDATE datetime = null;
                declare @BILLINGENDDATE datetime = null;

                if ((@TRANSACTIONDATERANGECODE > 3) and (not @TRANSACTIONDATERANGEBILLINGCYCLEID is null) and (@TRANSACTIONDATERANGEBILLINGCYCLEID != '00000000-0000-0000-0000-000000000000'))
                begin
                    select @BILLINGSTARTDATE = BILLINGSTARTDATE,
                            @BILLINGENDDATE = BILLINGENDDATE
                    from dbo.ACADEMICYEAR
                    where ACADEMICYEAR.ID = @TRANSACTIONDATERANGEBILLINGCYCLEID;
                end
                else if ((@TRANSACTIONDATERANGECODE >= 1) and (@TRANSACTIONDATERANGECODE <= 3))
                begin
                    set @BILLINGSTARTDATE = dateadd(d, @TRANSACTIONDATERANGECODE * -30 ,dbo.UFN_DATE_GETEARLIESTTIME(getdate()));
                    set @BILLINGENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
                end

                declare @BILLINGSTARTDUEDATE datetime;
                declare @BILLINGENDDUEDATE datetime;

                if ((@TRANSACTIONDUEDATERANGECODE > 3) and (not @TRANSACTIONDUEDATERANGEBILLINGCYCLEID is null) and (@TRANSACTIONDUEDATERANGEBILLINGCYCLEID != '00000000-0000-0000-0000-000000000000'))
                begin
                    select @BILLINGSTARTDUEDATE = BILLINGSTARTDATE,
                            @BILLINGENDDUEDATE = BILLINGENDDATE
                    from dbo.ACADEMICYEAR
                    where ACADEMICYEAR.ID = @TRANSACTIONDUEDATERANGEBILLINGCYCLEID;
                end
                else if ((@TRANSACTIONDUEDATERANGECODE >= 1) and (@TRANSACTIONDUEDATERANGECODE <= 3))
                begin
                    set @BILLINGSTARTDUEDATE = dateadd(d, @TRANSACTIONDUEDATERANGECODE * -30 ,dbo.UFN_DATE_GETEARLIESTTIME(getdate()));
                    set @BILLINGENDDUEDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
                end

                insert into @TRANSACTIONS
                    (ID, 
                    DATE,
                    DUEDATE,
                    DAYSLATE,
                    DUEDATEDISPLAY, 
                    TYPE,
                    TYPECODE,
                    DESCRIPTION,
                    AMOUNT,
                    AMOUNTREMAINING,
                    PAIDBY,
                    STATUSCODE,
                    STATUS)
                select ID,
                       DATE,
                       DUEDATE,
                       DAYSLATE,
                       DUEDATEDISPLAY,
                       TYPE,
                       TYPECODE,
                       DESCRIPTION,
                       AMOUNT,
                       AMOUNTREMAINING,
                       PAIDBY,
                       STATUSCODE,
                       STATUS
             from dbo.UFN_BILLINGINDIVIDUALTRANSACTIONDETAILS(@CONSTITUENTID, 
                                                                    @TYPECODE, 
                                                                    @STATUSCODE, 
                                                                    @BILLINGSTARTDATE, 
                                                                    @BILLINGENDDATE, 
                                                                    @BILLINGSTARTDUEDATE, 
                                                                    @BILLINGENDDUEDATE);

                return;
            end