USP_DATAFORMTEMPLATE_VIEW_BILLINGINDIVIDUALSUMMARY

The load procedure used by the view dataform template "Billing Individual Summary View Form"

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	CONSTITUENTID
@NAME	nvarchar(400)	INOUT	NAME
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	PHONETYPE
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@ISINACTIVE	bit	INOUT	ISINACTIVE
@PICTURE	varbinary	INOUT	PICTURE
@LOOKUPID	nvarchar(100)	INOUT	Student ID
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	DECEASEDDATE
@DECEASED	bit	INOUT	DECEASED
@ISSTUDENT	bit	INOUT	ISSTUDENT
@CURRENTGRADELEVEL	nvarchar(200)	INOUT	Grade level
@CURRENTSTATUS	nvarchar(200)	INOUT	Current status
@CURRENTSCHOOL	nvarchar(100)	INOUT	CURRENTSCHOOL
@CURRENTENROLLMENTID	uniqueidentifier	INOUT	CURRENTENROLLMENTID
@CURRENTSCHOOL2	nvarchar(100)	INOUT	CURRENTSCHOOL2
@CURRENTENROLLMENTID2	uniqueidentifier	INOUT	CURRENTENROLLMENTID2
@CURRENTSCHOOL3	nvarchar(100)	INOUT	CURRENTSCHOOL3
@CURRENTENROLLMENTID3	uniqueidentifier	INOUT	CURRENTENROLLMENTID3
@BALANCE	money	INOUT	Balance as of today
@PASTDUEBALANCE	money	INOUT	Past due balance
@ACCOUNTINGMETHODCODE	tinyint	INOUT	ACCOUNTINGMETHODCODE
@HASRECEIVEDFUNDS	bit	INOUT	HASRECEIVEDFUNDS

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BILLINGINDIVIDUALSUMMARY
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @NAME nvarchar(400) = null output,
                    @ADDRESS nvarchar(300) = null output,
                    @PHONENUMBER nvarchar(100) = null output,
                    @PHONETYPE nvarchar(100) = null output,
                    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                    @ISINACTIVE bit = null output,
                    @PICTURE varbinary(max) = null output,
                    @LOOKUPID nvarchar(100) = null output,
                    @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
                    @DECEASED bit = null output,
                    @ISSTUDENT bit = null output,
                    @CURRENTGRADELEVEL nvarchar(200) = null output,
                    @CURRENTSTATUS nvarchar(200) = null output,
                    @CURRENTSCHOOL nvarchar(100) = null output,
                    @CURRENTENROLLMENTID uniqueidentifier = null output,
                    @CURRENTSCHOOL2 nvarchar(100) = null output,
                    @CURRENTENROLLMENTID2 uniqueidentifier = null output,
                    @CURRENTSCHOOL3 nvarchar(100) = null output,
                    @CURRENTENROLLMENTID3 uniqueidentifier = null output,
                    @BALANCE money = null output,
                    @PASTDUEBALANCE money = null output,
                    @ACCOUNTINGMETHODCODE tinyint = null output,
                    @HASRECEIVEDFUNDS bit = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    --CONSTIT FIELDS

                    select
                        @DATALOADED = 1,
                        @CONSTITUENTID = CONSTITUENT.ID,
                        @NAME = CONSTITUENT.NAME,
                        @WEBADDRESS = CONSTITUENT.WEBADDRESS,
                        @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
                        @ISINACTIVE = CONSTITUENT.ISINACTIVE,
                        @LOOKUPID = CONSTITUENT.LOOKUPID,
                        @ISSTUDENT = dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID),
                        @BALANCE = dbo.UFN_BILLINGBALANCEINDIVIDUAL(CONSTITUENT.ID, null, getdate()),
                        @PASTDUEBALANCE = dbo.UFN_BILLINGPASTDUEBALANCEINDIVIDUAL(CONSTITUENT.ID, null, getdate()),
                        @ACCOUNTINGMETHODCODE = dbo.UFN_APPLICATIONRULES_GETACCOUNTINGMETHODCODE()
                    from
                        dbo.CONSTITUENT
                    where
                        CONSTITUENT.ID = @ID;

                    --EMAIL

                    SELECT @EMAILADDRESS = EMAILADDRESS
                    FROM dbo.EMAILADDRESS
                    WHERE
                    EMAILADDRESS.CONSTITUENTID = @ID and
                    EMAILADDRESS.ISPRIMARY = 1;

                    --PHONE

                    SELECT
                    @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
                    @PHONETYPE = (select DESCRIPTION FROM dbo.PHONETYPECODE WHERE ID = PHONE.PHONETYPECODEID)
                    FROM dbo.PHONE
                    WHERE 
                    PHONE.CONSTITUENTID = @ID and
                    PHONE.ISPRIMARY = 1;

                    --ADDRESS

                    select 
                        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID)
                    from
                        dbo.ADDRESS
                    where
                        ADDRESS.CONSTITUENTID = @ID and
                        ADDRESS.ISPRIMARY = 1;

                    --DECEASED

                    SELECT 
                    @DECEASEDDATE=DECEASEDDATE,
                    @DECEASED = case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end
                    FROM
                    dbo.DECEASEDCONSTITUENT
                    WHERE ID=@ID;

                    --CURRENT STUDENT PROGRESSION INFORMATION

                    set @CURRENTGRADELEVEL = dbo.UFN_STUDENTCURRENTGRADELEVEL_GETNAME(@ID);
                    set @CURRENTSTATUS = dbo.UFN_STUDENTCURRENTSTATUS_GETNAME(@ID);

                    declare @CURR_INFO TABLE
                    (
                  ENROLLMENTID    uniqueidentifier,
                        SCHOOLID uniqueidentifier,
                        SCHOOL nvarchar(100),
                        ROW tinyint
                    );

                   insert into @CURR_INFO 
                   select [ENROLLMENTID], [SCHOOLID], [SCHOOL], [ROW]
                   from dbo.UFN_STUDENT_CURRENTENROLLMENTINFO(@ID, 1) 

                    select
                        @CURRENTENROLLMENTID = CURR_INFO.[ENROLLMENTID],
                        @CURRENTSCHOOL = CURR_INFO.[SCHOOL]
                    from
                        @CURR_INFO as CURR_INFO
                    where 
                        CURR_INFO.[ROW] = 1

                    select 
                        @CURRENTENROLLMENTID2 = CURR_INFO.[ENROLLMENTID],
                        @CURRENTSCHOOL2 = CURR_INFO.[SCHOOL]
                    from
                        @CURR_INFO as CURR_INFO
                    where 
                        (CURR_INFO.[ROW] = 2) and (CURR_INFO.[SCHOOLID] <>  @CURRENTENROLLMENTID)

                    select 
                        @CURRENTENROLLMENTID3 = CURR_INFO.[ENROLLMENTID],
                        @CURRENTSCHOOL3 = CURR_INFO.[SCHOOL]
                    from
                        @CURR_INFO as CURR_INFO
                    where 
                        (CURR_INFO.[ROW] = 3) and (CURR_INFO.[SCHOOLID] NOT IN (@CURRENTENROLLMENTID, @CURRENTENROLLMENTID2)) 

                    set @HASRECEIVEDFUNDS = dbo.UFN_CONSTITUENT_HASRECEIVEDFUNDS(@ID)

                    return 0;