USP_DATALIST_STUDENTSCHOLARSHIPS

Displays a tree view of scholarships awarded to the student along with a scholarship total as the root node.
Parameters

Parameter	Parameter Type	Mode	Description
@STUDENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@CURRENCYCODE	tinyint	IN	Currency
Definition

Copy


              CREATE procedure dbo.USP_DATALIST_STUDENTSCHOLARSHIPS
              (
                @STUDENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @CURRENCYCODE tinyint = 2
              )
              as
                  set nocount on;

                  --Multicurrency

                  declare @CURRENCYID uniqueidentifier;
                  declare @MULTICURRENCYENABLED bit;
                  declare @HOLDER int;
                  declare @CURRENCIESAREDIFFERENT bit = 0;
                  declare @DISPLAYCURRENCY uniqueidentifier;

                  set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
                  if @MULTICURRENCYENABLED = 0 
                    set @CURRENCYCODE = 1

                  if @CURRENCYCODE = 1
                    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                  if @CURRENCYCODE = 2
                    set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

                  --If currency filter is base then we need to see if all rows return same currency 

                  --so we know if we can display the aggregate or not.

                  if @CURRENCYCODE = 0
                  begin
                    Select @HOLDER = count(ID) 
                    from
                      dbo.DESIGNATIONLEVELRECIPIENT as RECIPIENT                                       
                    where
                      RECIPIENT.CONSTITUENTID = @STUDENTID and
                      dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_DESIGNATIONLEVELRECIPIENTID(RECIPIENT.ID), @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1
                    group by BASECURRENCYID

                    Select @CURRENCIESAREDIFFERENT = case when @@ROWCOUNT > 1 then 1 else 0 end
                  end

                  if @CURRENCIESAREDIFFERENT = 0
                  begin
                    select top 1 
                      @DISPLAYCURRENCY = case @CURRENCYCODE
                        when 0 then RECIPIENT.BASECURRENCYID
                        else @CURRENCYID
                      end
                    from 
                      dbo.DESIGNATIONLEVELRECIPIENT as RECIPIENT 
                    where
                      RECIPIENT.CONSTITUENTID = @STUDENTID and
                      dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_DESIGNATIONLEVELRECIPIENTID(RECIPIENT.ID), @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1
                  end

                  declare @PARENTID uniqueidentifier
                    set @PARENTID = newid();

                  --Total amount from scholarship funds

                  select
                    @PARENTID,
                    'Total funds (' + cast(count(*)as varchar) +')' as NAME,
                    null as DATE,
                    sum(case
                      when @CURRENCIESAREDIFFERENT = 1 then null
                      else 
                        case @CURRENCYCODE
                          when 0 then dbo.UFN_DESIGNATIONLEVELRECIPIENT_GETAMOUNTINCURRENCY(RECIPIENT.ID,RECIPIENT.BASECURRENCYID)
                          else dbo.UFN_DESIGNATIONLEVELRECIPIENT_GETAMOUNTINCURRENCY(RECIPIENT.ID,@CURRENCYID)
                        end
                    end) as AMOUNT,
                    null as PARENTID,
                    null as DESIGNATIONLEVELID,
                    @DISPLAYCURRENCY as DISPLAYCURRENCY
                  from
                    dbo.DESIGNATIONLEVELRECIPIENT as RECIPIENT     
                  where
                    RECIPIENT.CONSTITUENTID = @STUDENTID and
                    dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_DESIGNATIONLEVELRECIPIENTID(RECIPIENT.ID), @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1

                  union all

                  --List of all received scholarship funds

                  select
                    RECIPIENT.ID,                                  
                    DESIGNATIONLEVEL.NAME,                                         
                    RECIPIENT.DATE,
                    case @CURRENCYCODE
                      when 0 then dbo.UFN_DESIGNATIONLEVELRECIPIENT_GETAMOUNTINCURRENCY(RECIPIENT.ID,RECIPIENT.BASECURRENCYID)
                      else dbo.UFN_DESIGNATIONLEVELRECIPIENT_GETAMOUNTINCURRENCY(RECIPIENT.ID,@CURRENCYID)
                    end as AMOUNT,
                    @PARENTID as PARENTID,
                    RECIPIENT.DESIGNATIONLEVELID,
                    case @CURRENCYCODE
                      when 0 then RECIPIENT.BASECURRENCYID
                      else @CURRENCYID
                    end as DISPLAYCURRENCY
                  from
                    dbo.DESIGNATIONLEVELRECIPIENT as RECIPIENT 
                    inner join dbo.DESIGNATIONLEVEL on RECIPIENT.DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
                    left outer join dbo.SCHOLARSHIPTERM on RECIPIENT.SCHOLARSHIPTERMID = SCHOLARSHIPTERM.ID                        
                  where
                    RECIPIENT.CONSTITUENTID = @STUDENTID and
                    dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, dbo.UFN_SITEID_MAPFROM_DESIGNATIONLEVELRECIPIENTID(RECIPIENT.ID), @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1