USP_DATAFORMTEMPLATE_EDITLOAD_EVENTGOAL

The load procedure used by the edit dataform template "EventGoal Edit 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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@EVENTID	uniqueidentifier	INOUT	Event
@REVENUETOTAL	money	INOUT	Total
@REVENUEMONTHLY	money	INOUT	Monthly
@REVENUEWEEKLY	money	INOUT	Weekly
@REVENUEDAILY	money	INOUT	Daily
@REVENUESPONSOR	money	INOUT	Sponsors
@REVENUECOMPANY	money	INOUT	Companies
@REVENUETEAM	money	INOUT	Teams
@REVENUEINDIVIDUAL	money	INOUT	Individual participants
@RECRUITMENTSPONSOR	int	INOUT	Sponsors
@RECRUITMENTCOMPANY	int	INOUT	Companies
@RECRUITMENTTEAM	int	INOUT	Teams
@RECRUITMENTINDIVIDUAL	int	INOUT	Individual participants
@COMMUNICATIONTOTAL	int	INOUT	Total
@COMMUNICATIONMONTHLY	int	INOUT	Monthly
@COMMUNICATIONWEEKLY	int	INOUT	Weekly
@COMMUNICATIONDAILY	int	INOUT	Daily
@COMMUNICATIONSPONSOR	int	INOUT	Sponsors
@COMMUNICATIONCOMPANY	int	INOUT	Companies
@COMMUNICATIONTEAM	int	INOUT	Teams
@COMMUNICATIONINDIVIDUAL	int	INOUT	Individual participants
@ISACTIVE	bit	INOUT	ISACTIVE
@RECRUITMENTFUNDRAISINGCOACHES	int	INOUT	Fundraising coaches
@RECRUITMENTFITNESSCOACHES	int	INOUT	Fitness coaches
@RECRUITMENTDONOR	int	INOUT	Donors
@REVENUERETENTION	decimal(7, 2)	INOUT	Percentage of total revenue to retain
@REVENUESPONSORRETENTION	decimal(7, 2)	INOUT	Percentage of sponsor revenue to retain
@REVENUECOMPANYRETENTION	decimal(7, 2)	INOUT	Percentage of company revenue to retain
@REVENUETEAMRETENTION	decimal(7, 2)	INOUT	Percentage of team revenue to retain
@REVENUEINDIVIDUALRETENTION	decimal(7, 2)	INOUT	Percentage of individual participant revenue to retain
@RECRUITMENTFUNDRAISINGCOACHRETENTION	decimal(7, 2)	INOUT	Percentage of fundraising coaches to retain
@RECRUITMENTFITNESSCOACHRETENTION	decimal(7, 2)	INOUT	Percentage of fitness coaches to retain
@RECRUITMENTSPONSORRETENTION	decimal(7, 2)	INOUT	Percentage of sponsors to retain
@RECRUITMENTCOMPANYRETENTION	decimal(7, 2)	INOUT	Percentage of companies to retain
@RECRUITMENTDONORRETENTION	decimal(7, 2)	INOUT	Percentage of donors to retain
@RECRUITMENTTEAMRETENTION	decimal(7, 2)	INOUT	Percentage of teams to retain
@RECRUITMENTINDIVIDUALRETENTION	decimal(7, 2)	INOUT	Percentage of individual participants to retain
@REVENUEHOUSEHOLD	money	INOUT	Households
@RECRUITMENTHOUSEHOLD	int	INOUT	Households
@COMMUNICATIONHOUSEHOLD	int	INOUT	Households
@REVENUEHOUSEHOLDRETENTION	decimal(7, 2)	INOUT	Percentage of households revenue to retain
@RECRUITMENTHOUSEHOLDRETENTION	decimal(7, 2)	INOUT	Percentage of households to retain
@REVENUEREGISTRATION	money	INOUT	Registration
@REVENUEREGISTRATIONRETENTION	decimal(7, 2)	INOUT	Percentage of registration revenue to retain
@PRIORYEAREVENTID	uniqueidentifier	INOUT	Prior year event id

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_EVENTGOAL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @EVENTID uniqueidentifier = null output,
    @REVENUETOTAL money = null output,
    @REVENUEMONTHLY money = null output,
    @REVENUEWEEKLY money = null output,
    @REVENUEDAILY money = null output,
    @REVENUESPONSOR money = null output,
    @REVENUECOMPANY money = null output,
    @REVENUETEAM money = null output,
    @REVENUEINDIVIDUAL money = null output,
  --  @RECRUITMENTTOTAL int = null output,
  --  @RECRUITMENTMONTHLY int = null output,
  --  @RECRUITMENTWEEKLY int = null output,
  --  @RECRUITMENTDAILY int = null output,
    @RECRUITMENTSPONSOR int = null output,
    @RECRUITMENTCOMPANY int = null output,
    @RECRUITMENTTEAM int = null output,
    @RECRUITMENTINDIVIDUAL int = null output,
    @COMMUNICATIONTOTAL int = null output,
    @COMMUNICATIONMONTHLY int = null output,
    @COMMUNICATIONWEEKLY int = null output,
    @COMMUNICATIONDAILY int = null output,
    @COMMUNICATIONSPONSOR int = null output,
    @COMMUNICATIONCOMPANY int = null output,
    @COMMUNICATIONTEAM int = null output,
    @COMMUNICATIONINDIVIDUAL int = null output,
    @ISACTIVE bit= null output, 

     --@MINIMIMUMFUNDRAISINGGOAL money = null output,
    --@MINIMIMUMPARTICIPANTGOAL int = null output,
    --@MINIMUMFUNDRAISEGOALPERMEMBER    money = null output,
    --@MINIMIMUMTEAMMEMBERGOAL int = null output,
    --@MINIMIMUMDONORGOAL    int = null output,
    --@MINIMIMUMNEWMEMBERGOAL    int = null output,
    --@MINIMIMUMNEWDONORGOAL    int = null output,

    @RECRUITMENTFUNDRAISINGCOACHES int = null output,
    @RECRUITMENTFITNESSCOACHES int = null output,
    @RECRUITMENTDONOR int = null output,
    @REVENUERETENTION decimal(7,2) = null output,
    @REVENUESPONSORRETENTION decimal(7,2) = null output,
    @REVENUECOMPANYRETENTION decimal(7,2) = null output,
    @REVENUETEAMRETENTION decimal(7,2) = null output,
    @REVENUEINDIVIDUALRETENTION decimal(7,2) = null output,
    @RECRUITMENTFUNDRAISINGCOACHRETENTION decimal(7,2) = null output,
    @RECRUITMENTFITNESSCOACHRETENTION decimal(7,2) = null output,
    @RECRUITMENTSPONSORRETENTION decimal(7,2) = null output,
    @RECRUITMENTCOMPANYRETENTION decimal(7,2) = null output,
    @RECRUITMENTDONORRETENTION decimal(7,2) = null output,
    @RECRUITMENTTEAMRETENTION decimal(7,2) = null output,
    @RECRUITMENTINDIVIDUALRETENTION decimal(7,2) = null output,
    @REVENUEHOUSEHOLD money = null output,
    @RECRUITMENTHOUSEHOLD int = null output,
    @COMMUNICATIONHOUSEHOLD int = null output,
    @REVENUEHOUSEHOLDRETENTION decimal(7,2) = null output,
    @RECRUITMENTHOUSEHOLDRETENTION decimal(7,2) = null output, 
    @REVENUEREGISTRATION money = null output,
    @REVENUEREGISTRATIONRETENTION decimal(7,2) = null output, 

    @PRIORYEAREVENTID uniqueidentifier = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 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.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
    select
        @DATALOADED = 1,
        @TSLONG = EG.TSLONG,
        @EVENTID = EG.EVENTID, 
        @REVENUETOTAL = REVENUETOTAL, 
        @REVENUEMONTHLY = REVENUEMONTHLY, 
        @REVENUEWEEKLY = REVENUEWEEKLY, 
        @REVENUEDAILY = REVENUEDAILY, 
        @REVENUESPONSOR = REVENUESPONSOR, 
        @REVENUECOMPANY = REVENUECOMPANY, 
        @REVENUETEAM = REVENUETEAM, 
        @REVENUEINDIVIDUAL = REVENUEINDIVIDUAL, 
      --  @RECRUITMENTTOTAL = RECRUITMENTTOTAL, 
      --  @RECRUITMENTMONTHLY = RECRUITMENTMONTHLY, 
      --  @RECRUITMENTWEEKLY = RECRUITMENTWEEKLY, 
      --  @RECRUITMENTDAILY = RECRUITMENTDAILY, 
        @RECRUITMENTSPONSOR = RECRUITMENTSPONSOR, 
        @RECRUITMENTCOMPANY = RECRUITMENTCOMPANY, 
        @RECRUITMENTTEAM = RECRUITMENTTEAM, 
        @RECRUITMENTINDIVIDUAL = RECRUITMENTINDIVIDUAL, 
        @COMMUNICATIONTOTAL = COMMUNICATIONTOTAL, 
        @COMMUNICATIONMONTHLY = COMMUNICATIONMONTHLY, 
        @COMMUNICATIONWEEKLY = COMMUNICATIONWEEKLY, 
        @COMMUNICATIONDAILY = COMMUNICATIONDAILY, 
        @COMMUNICATIONSPONSOR = COMMUNICATIONSPONSOR, 
        @COMMUNICATIONCOMPANY = COMMUNICATIONCOMPANY, 
        @COMMUNICATIONTEAM = COMMUNICATIONTEAM, 
        @COMMUNICATIONINDIVIDUAL = COMMUNICATIONINDIVIDUAL,
        @ISACTIVE = EG.ISACTIVE, 

    --    @MINIMIMUMFUNDRAISINGGOAL= MINIMIMUMFUNDRAISINGGOAL,
    --    @MINIMIMUMPARTICIPANTGOAL= MINIMIMUMPARTICIPANTGOAL,
    --    @MINIMUMFUNDRAISEGOALPERMEMBER= MINIMUMFUNDRAISEGOALPERMEMBER,
    --    @MINIMIMUMTEAMMEMBERGOAL= MINIMIMUMTEAMMEMBERGOAL,
    --    @MINIMIMUMDONORGOAL= MINIMIMUMDONORGOAL,
    --    @MINIMIMUMNEWMEMBERGOAL= MINIMIMUMNEWMEMBERGOAL,
    --    @MINIMIMUMNEWDONORGOAL= MINIMIMUMNEWDONORGOAL,

        @RECRUITMENTFUNDRAISINGCOACHES = RECRUITMENTFUNDRAISINGCOACHES,
        @RECRUITMENTFITNESSCOACHES = RECRUITMENTFITNESSCOACHES,
        @RECRUITMENTDONOR = RECRUITMENTDONOR,
        @REVENUERETENTION= REVENUERETENTION*100,
        @REVENUESPONSORRETENTION= REVENUESPONSORRETENTION*100,
        @REVENUECOMPANYRETENTION= REVENUECOMPANYRETENTION*100,    
        @REVENUETEAMRETENTION= REVENUETEAMRETENTION*100,
        @REVENUEINDIVIDUALRETENTION= REVENUEINDIVIDUALRETENTION*100,
        @RECRUITMENTFUNDRAISINGCOACHRETENTION= RECRUITMENTFUNDRAISINGCOACHRETENTION*100,
        @RECRUITMENTFITNESSCOACHRETENTION= RECRUITMENTFITNESSCOACHRETENTION*100,
        @RECRUITMENTSPONSORRETENTION= RECRUITMENTSPONSORRETENTION*100,
        @RECRUITMENTCOMPANYRETENTION= RECRUITMENTCOMPANYRETENTION*100,
        @RECRUITMENTDONORRETENTION= RECRUITMENTDONORRETENTION*100,
        @RECRUITMENTTEAMRETENTION= RECRUITMENTTEAMRETENTION*100,
        @RECRUITMENTINDIVIDUALRETENTION= RECRUITMENTINDIVIDUALRETENTION*100,
        @REVENUEHOUSEHOLD = REVENUEHOUSEHOLD,
        @RECRUITMENTHOUSEHOLD = RECRUITMENTHOUSEHOLD,
        @COMMUNICATIONHOUSEHOLD= COMMUNICATIONHOUSEHOLD,
        @REVENUEHOUSEHOLDRETENTION = REVENUEHOUSEHOLDRETENTION*100,
        @RECRUITMENTHOUSEHOLDRETENTION = RECRUITMENTHOUSEHOLDRETENTION*100 ,
        @REVENUEREGISTRATION = REVENUEREGISTRATION,
        @REVENUEREGISTRATIONRETENTION  = REVENUEREGISTRATIONRETENTION*100, 
        @PRIORYEAREVENTID = EX.PRIORYEAREVENTID 

    from dbo.EVENTGOAL EG (NOLOCK)
    inner join dbo.EVENT E (NOLOCK)ON EG.EVENTID = E.ID
    left join dbo.EVENTEXTENSION EX (NOLOCK)ON E.ID = EX.EVENTID
    where E.ID = @ID

    return 0;