USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPTRANSFERREPORT

The load procedure used by the view dataform template "Sponsorship Transfer Report Page Expression 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.
@SPONSORSHIPOPPORTUNITYMARKINELIGIBLEPROCESSID	uniqueidentifier	INOUT	SPONSORSHIPOPPORTUNITYMARKINELIGIBLEPROCESSID
@OPPORTUNITYTYPE	nvarchar(10)	INOUT	OPPORTUNITYTYPE
@OPPORTUNITYNAME	nvarchar(100)	INOUT	OPPORTUNITYNAME
@SPONSORSHIPLOCATIONCLOSEDPROCESSID	uniqueidentifier	INOUT	SPONSORSHIPLOCATIONCLOSEDPROCESSID
@LOCATIONNAME	nvarchar(200)	INOUT	LOCATIONNAME
@SPONSORSHIPTRANSFERPROCESSID	uniqueidentifier	INOUT	SPONSORSHIPTRANSFERPROCESSID

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPTRANSFERREPORT
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SPONSORSHIPOPPORTUNITYMARKINELIGIBLEPROCESSID uniqueidentifier = null output,
    @OPPORTUNITYTYPE nvarchar(10) = null output,
    @OPPORTUNITYNAME nvarchar(100) = null output,
    @SPONSORSHIPLOCATIONCLOSEDPROCESSID uniqueidentifier = null output,
    @LOCATIONNAME nvarchar(200) = null output,
  @SPONSORSHIPTRANSFERPROCESSID uniqueidentifier = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    -- 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.
    select @DATALOADED = 1,
           @OPPORTUNITYTYPE = case when SPONSORSHIPOPPORTUNITYCHILD.ID is not null then 'Child' else 'Project' end,
           @OPPORTUNITYNAME = isnull(SPONSORSHIPOPPORTUNITYCHILD.NAME,SPONSORSHIPOPPORTUNITYPROJECT.NAME),
           @SPONSORSHIPOPPORTUNITYMARKINELIGIBLEPROCESSID = SPONSORSHIPOPPORTUNITYTRANSFERPROCESS.ID
    from dbo.SPONSORSHIPOPPORTUNITYTRANSFERPROCESS
    inner join dbo.SPONSORSHIPOPPORTUNITYTRANSFERPROCESSSTATUS on SPONSORSHIPOPPORTUNITYTRANSFERPROCESSSTATUS.PARAMETERSETID = SPONSORSHIPOPPORTUNITYTRANSFERPROCESS.ID
    left outer join dbo.SPONSORSHIPOPPORTUNITYCHILD on SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIPOPPORTUNITYTRANSFERPROCESS.ID
    left outer join dbo.SPONSORSHIPOPPORTUNITYPROJECT on SPONSORSHIPOPPORTUNITYPROJECT.ID = SPONSORSHIPOPPORTUNITYTRANSFERPROCESS.ID
    where SPONSORSHIPOPPORTUNITYTRANSFERPROCESSSTATUS.ID = @ID

    if @DATALOADED = 0
        select @DATALOADED = 1,
               @OPPORTUNITYTYPE = '',
               @OPPORTUNITYNAME = '',
               @SPONSORSHIPLOCATIONCLOSEDPROCESSID = SPONSORSHIPLOCATIONCLOSEPROCESS.ID,
               @LOCATIONNAME = dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SPONSORSHIPLOCATIONCLOSEPROCESS.ID)
        from dbo.SPONSORSHIPLOCATIONCLOSEPROCESS
        inner join dbo.SPONSORSHIPLOCATIONCLOSEPROCESSSTATUS on SPONSORSHIPLOCATIONCLOSEPROCESSSTATUS.PARAMETERSETID = SPONSORSHIPLOCATIONCLOSEPROCESS.ID
        where SPONSORSHIPLOCATIONCLOSEPROCESSSTATUS.ID = @ID

        select @DATALOADED = 1,
               @OPPORTUNITYTYPE = '',
               @OPPORTUNITYNAME = '',
               @SPONSORSHIPTRANSFERPROCESSID = SPONSORSHIPTRANSFERPROCESS.ID       
        from dbo.SPONSORSHIPTRANSFERPROCESS
        inner join dbo.SPONSORSHIPTRANSFERPROCESSSTATUS on SPONSORSHIPTRANSFERPROCESSSTATUS.PARAMETERSETID = SPONSORSHIPTRANSFERPROCESS.ID
        where SPONSORSHIPTRANSFERPROCESSSTATUS.ID = @ID


    return 0;