USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPOPPORTUNITYGROUP

The save procedure used by the edit dataform template "Sponsorship Opportunity Group Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@SPONSORSHIPOPPORTUNITYTYPECODE	int	IN	Type
@SPONSORSPEROPPORTUNITY	int	IN	Maximum
@OFFERSOLESPONSORSHIP	bit	IN	Offer sole sponsorship
@OVERRIDESPONSORSPEROPPORTUNITY	xml	IN
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN	Location
@GENDERCODE	tinyint	IN	Gender
@SPONSORSHIPOPPORTUNITYAGERANGEID	uniqueidentifier	IN	Age range
@HASCONDITIONCODE	tinyint	IN	Disability/Illness
@ISHIVPOSITIVECODE	tinyint	IN	HIV positive
@ISORPHANEDCODE	tinyint	IN	Orphaned
@SPROPPPROJECTCATEGORYCODE	uniqueidentifier	IN	Category

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPOPPORTUNITYGROUP (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @NAME nvarchar(100),
    @SPONSORSHIPOPPORTUNITYTYPECODE int,
    @SPONSORSPEROPPORTUNITY int,
    @OFFERSOLESPONSORSHIP bit,
    @OVERRIDESPONSORSPEROPPORTUNITY xml,
    @SPONSORSHIPLOCATIONID uniqueidentifier,
    @GENDERCODE tinyint,
    @SPONSORSHIPOPPORTUNITYAGERANGEID uniqueidentifier,
    @HASCONDITIONCODE tinyint,
    @ISHIVPOSITIVECODE tinyint,
    @ISORPHANEDCODE tinyint,
    @SPROPPPROJECTCATEGORYCODE uniqueidentifier
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        -----------------------------------------

        -- validate Overrides

        if @OVERRIDESPONSORSPEROPPORTUNITY is not null
        begin
            declare @OVERRIDESTAB table (SPONSORSHIPLOCATIONID uniqueidentifier,
                                         SPONSORSPEROPPORTUNITY smallint,
                                         OFFERSOLESPONSORSHIP bit)

            insert into @OVERRIDESTAB
            select *
            from dbo.UFN_SPONSORSHIPOPPORTUNITYGROUP_OVERRIDESPONSORSPEROPPORTUNITY(@OVERRIDESPONSORSPEROPPORTUNITY);

            declare @BADCOUNT smallint

            -- sponsors per opportunity invalid

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES
            where OVERRIDES.SPONSORSPEROPPORTUNITY < 1

            if @BADCOUNT > 0
                raiserror('BBERR_INVALIDOVERRIDESPONSORSPEROPPORTUNITY',13,1)

            -- sole sponsorship invalid

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES
            where isnull(OVERRIDES.SPONSORSPEROPPORTUNITY,0) < 2
            and OVERRIDES.OFFERSOLESPONSORSHIP = 1;

            if @BADCOUNT > 0
                raiserror('BBERR_INVALIDOVERRIDEOFFERSOLESPONSORSHIP',13,1)

            -- duplicate locations

            select @BADCOUNT = count(*)
            from (select SPONSORSHIPLOCATIONID
            from @OVERRIDESTAB
            group by SPONSORSHIPLOCATIONID having count(*) > 1) X;

            if @BADCOUNT > 0
                raiserror('BBERR_DUPLICATELOCATIONS',13,1)

            -- overlapping locations

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES1
            inner join @OVERRIDESTAB OVERRIDES2 on 1=1
            inner join dbo.SPONSORSHIPLOCATION L1 on L1.ID = OVERRIDES1.SPONSORSHIPLOCATIONID
            inner join dbo.SPONSORSHIPLOCATION L2 on L2.ID = OVERRIDES2.SPONSORSHIPLOCATIONID
            where L1.HIERARCHYPATH.IsDescendantOf(L2.HIERARCHYPATH) = 1
            and L1.ID <> L2.ID;

            if @BADCOUNT > 0
                raiserror('BBERR_OVERLAPPINGLOCATIONS',13,1)

            if @SPONSORSHIPLOCATIONID is not null
            begin
                -- locations not consistent with group location

                select @BADCOUNT = count(*)
                from @OVERRIDESTAB OVERRIDES
                inner join dbo.SPONSORSHIPLOCATION OVERRIDELOCATION on OVERRIDELOCATION.ID = OVERRIDES.SPONSORSHIPLOCATIONID
                inner join dbo.SPONSORSHIPLOCATION GROUPLOCATION on GROUPLOCATION.ID = @SPONSORSHIPLOCATIONID
                where OVERRIDELOCATION.HIERARCHYPATH.IsDescendantOf(GROUPLOCATION.HIERARCHYPATH) = 0;

                if @BADCOUNT > 0
                    raiserror('BBERR_INVALIDOVERRIDELOCATION',13,1)

                -- location same as group location (i.e., default would never be used)

                select @BADCOUNT = count(*)
                from @OVERRIDESTAB OVERRIDES
                where OVERRIDES.SPONSORSHIPLOCATIONID = @SPONSORSHIPLOCATIONID;

                if @BADCOUNT > 0
                    raiserror('BBERR_SAMELOCATIONASGROUP',13,1)
            end

            -- locations w/ same values as default

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES
            where isnull(OVERRIDES.SPONSORSPEROPPORTUNITY,0) = isnull(@SPONSORSPEROPPORTUNITY,0)
            and OVERRIDES.OFFERSOLESPONSORSHIP = @OFFERSOLESPONSORSHIP

            if @BADCOUNT > 0
                raiserror('BBERR_OVERRIDESWITHSAMEVALUES',13,1)
        end
        -----------------------------------------


        update dbo.SPONSORSHIPOPPORTUNITYGROUP set
            NAME = @NAME,
            SPONSORSHIPOPPORTUNITYTYPECODE = @SPONSORSHIPOPPORTUNITYTYPECODE,
            SPONSORSPEROPPORTUNITY = isnull(@SPONSORSPEROPPORTUNITY,0),
            OFFERSOLESPONSORSHIP = @OFFERSOLESPONSORSHIP,
            OVERRIDESPONSORSPEROPPORTUNITY = @OVERRIDESPONSORSPEROPPORTUNITY,
            SEQUENCE = 1,
            SPONSORSHIPLOCATIONID = @SPONSORSHIPLOCATIONID,
            GENDERCODE = isnull(@GENDERCODE,0),
            SPONSORSHIPOPPORTUNITYAGERANGEID = @SPONSORSHIPOPPORTUNITYAGERANGEID,
            HASCONDITIONCODE = isnull(@HASCONDITIONCODE,0),
            ISHIVPOSITIVECODE = isnull(@ISHIVPOSITIVECODE,0),
            ISORPHANEDCODE = isnull(@ISORPHANEDCODE,0),
            SPROPPPROJECTCATEGORYCODEID=@SPROPPPROJECTCATEGORYCODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID
 --

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch



return 0;