USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIP

The load procedure used by the view dataform template "Sponsorship 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.
@NAME	nvarchar(700)	INOUT	Name
@SPONSORSHIPPROGRAMNAME	nvarchar(100)	INOUT	Program name
@SPONSORSHIPSTART	date	INOUT	Start date
@SPONSORSHIPEND	date	INOUT	End date
@SPONSORSHIPOPPORTUNITYID	uniqueidentifier	INOUT	SPONSORSHIPOPPORTUNITYID
@PROGRAMID	uniqueidentifier	INOUT	PROGRAMID
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@REVENUECONSTITUENTID	uniqueidentifier	INOUT	REVENUECONSTITUENTID
@REVENUECONSTITUENTTEXT	nvarchar(700)	INOUT	Gift sponsorship paid for by
@STATUS	nvarchar(100)	INOUT	Status
@REVENUEID	uniqueidentifier	INOUT	REVENUEID
@SPONSORSHIPOPPORTUNITYTYPECODE	tinyint	INOUT	SPONSORSHIPOPPORTUNITYTYPECODE
@OPPORTUNITYNAME	nvarchar(100)	INOUT	OPPORTUNITYNAME
@TRANSACTIONID	uniqueidentifier	INOUT	TRANSACTIONID
@ACTIONCODE	tinyint	INOUT	ACTIONCODE
@DELETEALLOWED	bit	INOUT	DELETEALLOWED
@REACTIVATEALLOWED	bit	INOUT	REACTIVATEALLOWED
@NEXTSPONSORSHIPID	uniqueidentifier	INOUT	NEXTSPONSORSHIPID
@NEXTSPONSORSHIPTEXT	nvarchar(700)	INOUT	NEXTSPONSORSHIPTEXT
@NEXTSPONSORSHIPLABEL	nvarchar(100)	INOUT	NEXTSPONSORSHIPLABEL
@PREVIOUSSPONSORSHIPID	uniqueidentifier	INOUT	PREVIOUSSPONSORSHIPID
@PREVIOUSSPONSORSHIPTEXT	nvarchar(700)	INOUT	PREVIOUSSPONSORSHIPTEXT
@PREVIOUSSPONSORSHIPLABEL	nvarchar(100)	INOUT	PREVIOUSSPONSORSHIPLABEL
@ALLOWSOLESPONSORSHIP	bit	INOUT	ALLOWSOLESPONSORSHIP
@ALLOWCHANGESTARTDATE	bit	INOUT	ALLOWCHANGESTARTDATE
@ALLOWCHANGEPLANNEDENDDATE	bit	INOUT	ALLOWCHANGEPLANNEDENDDATE
@REMOVESOLESPONSORSHIP	bit	INOUT	REMOVESOLESPONSORSHIP
@SHOWREVENUE	bit	INOUT	SHOWREVENUE
@AFFILIATECONSTITUENTID	uniqueidentifier	INOUT	AFFILIATECONSTITUENTID
@AFFILIATECONSTITUENTTEXT	nvarchar(100)	INOUT	Affiliate organization
@PLANNEDENDDATE	date	INOUT	Expiration date
@SPONSORTYPECODE	tinyint	INOUT	SPONSORTYPECODE
@CHANGEDBY	nvarchar(128)	INOUT	Cancelled by
@EXPIRATIONREASON	nvarchar(100)	INOUT	EXPIRATIONREASON
@OPPORTUNITYLOOKUPID	nvarchar(20)	INOUT	OPPORTUNITYLOOKUPID
@COMMITMENTID	nvarchar(20)	INOUT	Commitment ID
@ADDITIONALRECURRINGGIFTREVENUEID	uniqueidentifier	INOUT	ADDITIONALRECURRINGGIFTREVENUEID
@SHOWADDRECURRINGADDITIONALGIFT	bit	INOUT	SHOWADDRECURRINGADDITIONALGIFT
@SHOWGOTORECURRINGADDITIONALGIFT	bit	INOUT	SHOWGOTORECURRINGADDITIONALGIFT
@ISPENDING	bit	INOUT
@PENDINGBATCHNUMBER	nvarchar(100)	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@ATTRIBUTEDEFINED	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIP
(
    @ID uniqueidentifier, 
    @DATALOADED bit = 0 output,
    @NAME nvarchar(700) = null output,
    @SPONSORSHIPPROGRAMNAME nvarchar(100) = null output,
    @SPONSORSHIPSTART date = null output,
    @SPONSORSHIPEND  date = null output,
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null output,
    @PROGRAMID uniqueidentifier=null output,
    @CONSTITUENTID uniqueidentifier=null output,
    @REVENUECONSTITUENTID uniqueidentifier = null output,
    @REVENUECONSTITUENTTEXT nvarchar(700) = null output,
    @STATUS nvarchar(100) = null output,
    @REVENUEID uniqueidentifier=null output,
    @SPONSORSHIPOPPORTUNITYTYPECODE tinyint=null output,
    @OPPORTUNITYNAME nvarchar(100) = null output,
    @TRANSACTIONID uniqueidentifier = null output,
    @ACTIONCODE tinyint = null output,
    @DELETEALLOWED bit = null output,
    @REACTIVATEALLOWED bit = null output,
    @NEXTSPONSORSHIPID uniqueidentifier = null output,
    @NEXTSPONSORSHIPTEXT nvarchar(700) = null output,
    @NEXTSPONSORSHIPLABEL nvarchar(100) = null output,
    @PREVIOUSSPONSORSHIPID uniqueidentifier = null output,
    @PREVIOUSSPONSORSHIPTEXT nvarchar(700) = null output,
    @PREVIOUSSPONSORSHIPLABEL nvarchar(100) = null output,
    @ALLOWSOLESPONSORSHIP bit = null output,
    @ALLOWCHANGESTARTDATE bit = null output,
    @ALLOWCHANGEPLANNEDENDDATE bit = null output,
    @REMOVESOLESPONSORSHIP bit = null output,
    @SHOWREVENUE bit = null output,
    @AFFILIATECONSTITUENTID uniqueidentifier = null output,
    @AFFILIATECONSTITUENTTEXT nvarchar(100) = null output,
    @PLANNEDENDDATE date = null output,
    @SPONSORTYPECODE tinyint = null output,
    @CHANGEDBY nvarchar(128) = null output,
    @EXPIRATIONREASON nvarchar(100) = null output,
    @OPPORTUNITYLOOKUPID nvarchar(20) = null output,
    @COMMITMENTID nvarchar(20) = null output,
    @ADDITIONALRECURRINGGIFTREVENUEID uniqueidentifier = null output,
    @SHOWADDRECURRINGADDITIONALGIFT bit = null output,
    @SHOWGOTORECURRINGADDITIONALGIFT bit = null output,
  @ISPENDING bit = null output,
  @PENDINGBATCHNUMBER nvarchar(100) = null output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @ATTRIBUTEDEFINED bit = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

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

      declare @CURRENTDATEEARLIESTTIME date;
      set @CURRENTDATEEARLIESTTIME = @CURRENTDATE;

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.

    declare @REVENUESPLITID uniqueidentifier=null;

  --AdiSa 1/23/2013 WI 242703: We first evaluate if the current app user is a sys admin or 

  --has permission to view the payment details view form. If permission is granted then set

  --the @SHOWREVENUE variable to true. Otherwise hide the payment tab completely.

  declare @PAYMENTDETAILSVIEWFORMID uniqueidentifier;
  set @PAYMENTDETAILSVIEWFORMID = 'BAFCA6F5-4CDB-4173-AECE-113713D38BFB';

  -- SPONSORSHIP PROGRAM START AND END DATES

    select
      @DATALOADED = 1,
      @CONSTITUENTID = SPONSORSHIP.CONSTITUENTID,
            @REVENUECONSTITUENTID = REVENUE.CONSTITUENTID,
            @REVENUECONSTITUENTTEXT = 
                case 
                when SPONSORSHIP.CONSTITUENTID = REVENUE.CONSTITUENTID or REVENUE.ID is null
                then ''
                else (select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID))
                end,            
      @SPONSORSHIPOPPORTUNITYID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID,
            @OPPORTUNITYNAME = dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID),
      @SPONSORSHIPSTART = SPONSORSHIP.STARTDATE,
      @SPONSORSHIPEND = SPONSORSHIP.ENDDATE,
      @REVENUESPLITID = SPONSORSHIP.REVENUESPLITID,
      @STATUS = SPONSORSHIP.STATUS +
            case when SPONSORSHIP.STATUSCODE = 2 then
              case when LASTTRANSACTION.ACTIONCODE in(2,3) then
                     ' (' + case LASTTRANSACTION.ACTIONCODE
                                    when 2 then
                                      case SPONSORSHIP.ID
                                        when LASTTRANSACTION.CONTEXTSPONSORSHIPID then
                                          case LASTREASON.REASONTYPECODE when 11 then 'Expired' else 'Cancelled' end
                                        when LASTTRANSACTION.DECLINEDSPONSORSHIPID then 'Declined' end
                                    when 3 then 'Terminated'
                                    end + ')'
                    when LASTTRANSACTION.ACTIONCODE = 7 and SPONSORSHIP.ID = LASTTRANSACTION.DECLINEDSPONSORSHIPID then
                     ' (Declined)'
                    else '' end
            when SPONSORSHIP.STATUSCODE = 1 and LASTTRANSACTION.ACTIONCODE = 6 then
             ' (Transfer pending)'
            else '' end,
      @TRANSACTIONID = LASTTRANSACTION.ID,
      @ACTIONCODE = LASTTRANSACTION.ACTIONCODE,
      @DELETEALLOWED = dbo.UFN_SPONSORSHIP_DELETEALLOWED(SPONSORSHIP.ID),
      @REACTIVATEALLOWED = dbo.UFN_SPONSORSHIP_REACTIVATEALLOWED(SPONSORSHIP.ID),
      @PROGRAMID = SPONSORSHIP.SPONSORSHIPPROGRAMID,
      @SPONSORSHIPPROGRAMNAME = SPONSORSHIPPROGRAM.NAME,
      @ALLOWSOLESPONSORSHIP = case when dbo.UFN_SPONSORSHIP_OFFERSOLESPONSORSHIP(SPONSORSHIPOPPORTUNITYGROUP.ID,SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID) = 1
                                     and  SPONSORSHIP.ISSOLESPONSORSHIP = 0
                                     and not exists (select 'X' from SPONSORSHIP S2 where S2.SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITY.ID and S2.STATUSCODE in(0,1) and S2.ID != @ID)
                                     and SPONSORSHIPOPPORTUNITY.AVAILABILITYCODE = 0
                                     and SPONSORSHIP.STATUSCODE = 1
                                     and LASTTRANSACTION.ACTIONCODE <> 6
                                     then 1
                                     else 0 end,    
        @REMOVESOLESPONSORSHIP =  case when SPONSORSHIP.ISSOLESPONSORSHIP = 1
                                  and SPONSORSHIP.STATUSCODE = 1
                                                            and LASTTRANSACTION.ACTIONCODE <> 6
                                                            then 1
                                                            else 0
                                                            end,
      @SPONSORSHIPOPPORTUNITYTYPECODE = SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE,
      @REVENUEID = REVENUESPLIT.REVENUEID,
        @NAME = SPONSORSHIP_NF.NAME,
      @NEXTSPONSORSHIPID = NEXTSPONSORSHIP.ID,
      @NEXTSPONSORSHIPTEXT = case when LASTTRANSACTION.ACTIONCODE = 5 then (select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(NEXTSPONSORSHIP.CONSTITUENTID)) else dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(NEXTSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) end,
      @NEXTSPONSORSHIPLABEL = case when NEXTSPONSORSHIP.ID is not null then case LASTTRANSACTION.ACTIONCODE when 5 then 'Reassigned' when 6 then 'Pending transfer' else 'Transferred' end + ' to:' end,
      @PREVIOUSSPONSORSHIPID = PREVIOUSSPONSORSHIP.ID,
      @PREVIOUSSPONSORSHIPTEXT = case when CREATETRANSACTION.ACTIONCODE = 5 then (select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(PREVIOUSSPONSORSHIP.CONSTITUENTID)) else dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(PREVIOUSSPONSORSHIP.SPONSORSHIPOPPORTUNITYID) end,
      @PREVIOUSSPONSORSHIPLABEL = case when PREVIOUSSPONSORSHIP.ID is not null then case when PREVIOUSSPONSORSHIP.STATUSCODE = 0 then 'Pending transfer' else case CREATETRANSACTION.ACTIONCODE when 5 then 'Reassigned' else 'Transferred' end end + ' from:' end,
    @AFFILIATECONSTITUENTID = 
                case 
                when SPONSORSHIPAFFILIATEPROGRAM.ID is null
                then null
                else SPONSORSHIPAFFILIATEPROGRAM.AFFILIATEID
                end,
        @AFFILIATECONSTITUENTTEXT = 
                case 
              when SPONSORSHIPAFFILIATEPROGRAM.ID is null
                then ''
                else (select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(SPONSORSHIPAFFILIATEPROGRAM.AFFILIATEID))
                end,
        @PLANNEDENDDATE = SPONSORSHIP.PLANNEDENDDATE,
        @ALLOWCHANGEPLANNEDENDDATE = 
        case SPONSORSHIP.STATUSCODE
          when 1 then 1
            else 0
        end,
        @SHOWREVENUE = case when REVENUESPLIT.REVENUEID is not null and (SPONSORSHIP.STATUSCODE = 1 or LASTTRANSACTION.ACTIONCODE in(2,3,5)) and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists(select SITEID from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITES where dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @PAYMENTDETAILSVIEWFORMID, SITEID) = 1)) then 1 else 0 end,
        @CHANGEDBY = (select USERNAME from CHANGEAGENT where ID = SPONSORSHIP.CHANGEDBYID),
        @EXPIRATIONREASON = EXPIREREASON.REASON,
        @OPPORTUNITYLOOKUPID = SPONSORSHIPOPPORTUNITY.LOOKUPID,
        @COMMITMENTID = SPONSORSHIPCOMMITMENT.LOOKUPID,
        @ADDITIONALRECURRINGGIFTREVENUEID = SPONSORSHIPRECURRINGADDITIONALGIFT.REVENUEID,
        @SHOWADDRECURRINGADDITIONALGIFT = 
            case when SPONSORSHIPRECURRINGADDITIONALGIFT.REVENUEID is null and SPONSORSHIP.STATUSCODE = 1
                then 1
            else 0
            end,
        @SHOWGOTORECURRINGADDITIONALGIFT = 
            case when SPONSORSHIPRECURRINGADDITIONALGIFT.REVENUEID is not null and SPONSORSHIP.STATUSCODE = 1
                then 1
            else 0
            end,
        @ISPENDING = REVENUESCHEDULE.ISPENDING
     from
      dbo.SPONSORSHIP
    inner join dbo.SPONSORSHIPTRANSACTION CREATETRANSACTION on CREATETRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and CREATETRANSACTION.TRANSACTIONSEQUENCE = (select min(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MINSEQUENCE where MINSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and SPONSORSHIP.ID = MINSEQUENCE.TARGETSPONSORSHIPID)
    left outer join dbo.SPONSORSHIP PREVIOUSSPONSORSHIP on PREVIOUSSPONSORSHIP.ID = case when CREATETRANSACTION.ACTIONCODE in(1,5,6,7,8) then case when CREATETRANSACTION.CONTEXTSPONSORSHIPID <> SPONSORSHIP.ID then CREATETRANSACTION.CONTEXTSPONSORSHIPID end end
    inner join dbo.SPONSORSHIPTRANSACTION LASTTRANSACTION on LASTTRANSACTION.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and LASTTRANSACTION.TRANSACTIONSEQUENCE = (select max(TRANSACTIONSEQUENCE) from dbo.SPONSORSHIPTRANSACTION MAXSEQUENCE where MAXSEQUENCE.SPONSORSHIPCOMMITMENTID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID and SPONSORSHIP.ID in(MAXSEQUENCE.CONTEXTSPONSORSHIPID,MAXSEQUENCE.TARGETSPONSORSHIPID,MAXSEQUENCE.DECLINEDSPONSORSHIPID) and MAXSEQUENCE.ACTIONCODE <> 9)
    left outer join dbo.SPONSORSHIP NEXTSPONSORSHIP on NEXTSPONSORSHIP.ID = case when LASTTRANSACTION.ACTIONCODE in(1,5,6,7,8) then case when LASTTRANSACTION.TARGETSPONSORSHIPID <> SPONSORSHIP.ID then LASTTRANSACTION.TARGETSPONSORSHIPID end end
    inner join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID
    inner join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.ID = SPONSORSHIP.SPONSORSHIPPROGRAMID
    left outer join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    left outer join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    left outer join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPONSORSHIP.CONSTITUENTID) SPONSORSHIP_NF
    left outer join dbo.SPONSORSHIPAFFILIATEPROGRAM on SPONSORSHIPAFFILIATEPROGRAM.ID = SPONSORSHIP.SPONSORSHIPPROGRAMID
    left outer join dbo.SPONSORSHIPREASON as LASTREASON on LASTREASON.ID = LASTTRANSACTION.SPONSORSHIPREASONID
    left outer join dbo.SPONSORSHIPREASON as EXPIREREASON on EXPIREREASON.ID = SPONSORSHIP.EXPIRATIONREASONID
    inner join dbo.SPONSORSHIPCOMMITMENT on SPONSORSHIPCOMMITMENT.ID = SPONSORSHIP.SPONSORSHIPCOMMITMENTID
    left outer join dbo.SPONSORSHIPRECURRINGADDITIONALGIFT on SPONSORSHIPRECURRINGADDITIONALGIFT.SPONSORSHIPID = SPONSORSHIP.ID and SPONSORSHIPRECURRINGADDITIONALGIFT.STATUSCODE in (0,1,5)
    where 
      SPONSORSHIP.ID = @ID;

  --Determine if we can change the start date on the sponsorship            

    select 
         @ALLOWCHANGESTARTDATE = 
            case S.ACTIONCODE
                when 0 --Add

                then 1
                else 0
            end
    from 
         SPONSORSHIPTRANSACTION S
    where 
         TARGETSPONSORSHIPID = @ID
         and TRANSACTIONSEQUENCE = (select MIN(TRANSACTIONSEQUENCE) from sponsorshiptransaction where TARGETSPONSORSHIPID = @ID)

    select top 1
      @SPONSORTYPECODE = case
                           when min(SPONSORTYPECODE) = max(SPONSORTYPECODE) then min(SPONSORTYPECODE)
                           else 0
                         end
    from dbo.SPONSORDATERANGE
    where CONSTITUENTID = @CONSTITUENTID
    group by case when DATEFROM <= @CURRENTDATEEARLIESTTIME then 0 else 1 end,
             case when (DATETO is null or DATETO > @CURRENTDATEEARLIESTTIME) then 0 else 1 end
    order by case when DATEFROM <= @CURRENTDATEEARLIESTTIME then 0 else 1 end,
             case when (DATETO is null or DATETO > @CURRENTDATEEARLIESTTIME) then 0 else 1 end

    if @ISPENDING = 1
    begin
        select top 1
            @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER 
        from 
            dbo.BATCH
            inner join dbo.BATCHREVENUE on BATCHREVENUE.BATCHID = BATCH.ID
            left join dbo.BATCHREVENUEAPPLICATION as [BATCHAPP] on [BATCHAPP].BATCHREVENUEID = BATCHREVENUE.ID 
        where 
            BATCH.STATUSCODE not in (1, 2) 
            and (
                ([BATCHAPP].REVENUEID = @REVENUEID and BATCHREVENUE.PAYINGPENDINGREVENUEID is null and [BATCHAPP].WASGENERATED = 1) 
                or
                ([BATCHAPP].REVENUEID is null and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID)
                or
                ([BATCHAPP].REVENUEID = @REVENUEID and BATCHREVENUE.PAYINGPENDINGREVENUEID = @REVENUEID)
                )
    end

  set @ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('SPONSORSHIP',@CURRENTAPPUSERID)

    return 0;