USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPOPPORTUNITYGROUP

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPOPPORTUNITYGROUP
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(100) = null,
    @SPONSORSHIPOPPORTUNITYTYPECODE int = 1,
    @SPONSORSPEROPPORTUNITY int = 1,
    @OFFERSOLESPONSORSHIP bit = 0,
    @OVERRIDESPONSORSPEROPPORTUNITY xml = null,
    @SEQUENCE int = 1,
    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @GENDERCODE tinyint = 0,
    @HASCONDITIONCODE tinyint = 0,
    @ISHIVPOSITIVECODE tinyint = 0,
    @ISORPHANEDCODE tinyint = 0,
    @SPONSORSHIPOPPORTUNITYAGERANGEID uniqueidentifier = null,
    @SPROPPPROJECTCATEGORYCODE uniqueidentifier = null
)
as

set nocount on;

if @ID is null
    set @ID = newid()

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
    -----------------------------------------


    insert into dbo.SPONSORSHIPOPPORTUNITYGROUP
        (ID,
         NAME,
         SPONSORSHIPOPPORTUNITYTYPECODE,
         SPONSORSPEROPPORTUNITY,
         OFFERSOLESPONSORSHIP,
         OVERRIDESPONSORSPEROPPORTUNITY,
         SEQUENCE,
         SPONSORSHIPLOCATIONID,
         GENDERCODE,
         SPONSORSHIPOPPORTUNITYAGERANGEID,
         ISHIVPOSITIVECODE,
         ISORPHANEDCODE,
         HASCONDITIONCODE,
         SPROPPPROJECTCATEGORYCODEID,
         ADDEDBYID,
         CHANGEDBYID,
         DATEADDED,
         DATECHANGED)
    values
        (@ID,
         @NAME,
         @SPONSORSHIPOPPORTUNITYTYPECODE,
         isnull(@SPONSORSPEROPPORTUNITY,0),
         @OFFERSOLESPONSORSHIP,
         @OVERRIDESPONSORSPEROPPORTUNITY,
         @SEQUENCE,
         @SPONSORSHIPLOCATIONID,
         isnull(@GENDERCODE,0),
         @SPONSORSHIPOPPORTUNITYAGERANGEID,
         isnull(@ISHIVPOSITIVECODE,0),
         isnull(@ISORPHANEDCODE,0),
         isnull(@HASCONDITIONCODE,0),
         @SPROPPPROJECTCATEGORYCODE,
         @CHANGEAGENTID,
         @CHANGEAGENTID,
         @CURRENTDATE,
         @CURRENTDATE)

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0