USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPOPPORTUNITYRESERVEBUSINESS

The save procedure used by the add dataform template "Sponsorship Opportunity Reserve Business Process Add Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@IDSETREGISTERID	uniqueidentifier	IN	Selection
@GROUPID	uniqueidentifier	IN	Group
@GENDERCODE	int	IN	Gender
@SPROPPAGERANGEID	uniqueidentifier	IN	Age range
@ISHIVPOSITIVECODE	int	IN	HIV positive
@HASCONDITIONCODE	int	IN	Disability/Illness
@ISORPHANEDCODE	int	IN	Orphaned
@MAXKEYNUMBER	int	IN	Target number
@RESERVEENDDATE	datetime	IN	End date
@INCLUDEPENDING	bit	IN	Include pending opportunities
@MULTIPLELOCATION	xml	IN	Locations
Definition

Copy


              CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPOPPORTUNITYRESERVEBUSINESS
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,           
              @NAME nvarchar(100) = '',
              @DESCRIPTION nvarchar(255) = '',
              @IDSETREGISTERID uniqueidentifier = null,
              --@RESERVATIONREASONID uniqueidentifier = null,    

              @GROUPID uniqueidentifier = null,    
                --@OPPORTUNITYLOCATIONID uniqueidentifier = null,    

              @GENDERCODE int = 0,
                @SPROPPAGERANGEID uniqueidentifier = null,
                @ISHIVPOSITIVECODE int = 0,
                @HASCONDITIONCODE int = 0,
                @ISORPHANEDCODE int = 0,              
              @MAXKEYNUMBER int = null,
              @RESERVEENDDATE datetime = null,
          --@KEYID uniqueidentifier = null,

          @INCLUDEPENDING bit = 0,
          --@INCLUDEINELIGIBLE bit = 0,

          @MULTIPLELOCATION xml = null
              )
              as          
                set nocount on;

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

          if @MAXKEYNUMBER is null
           set @MAXKEYNUMBER  = 0


          --set @KEYID = @ID


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

              declare @CURRENTDATE datetime
              set @CURRENTDATE = getdate()

              --set @RESERVEENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(@RESERVEENDDATE);


          begin try

           if @MULTIPLELOCATION is not null
             begin             

                declare @LOCATIONTABLE table (LOCATIONID uniqueidentifier,
                                     LIMIT smallint)

                    insert into @LOCATIONTABLE
                    select *
                    from dbo.UFN_SPONSORSHIPOPPORTUNITYRESERVATION_MULTIPLELOCATION(@MULTIPLELOCATION);                                                

                declare @COUNT smallint

                    -- limit invalid

                    select @COUNT = count(*)
                    from @LOCATIONTABLE LOCATIONTAB
                    where LOCATIONTAB.LIMIT < 1

                if @COUNT > 0
                          raiserror('BBERR_INVALIDLIMIT',13,1)

                -- duplicate locations

                    select @COUNT = count(*)
                    from (select LOCATIONID
                    from @LOCATIONTABLE
                    group by LOCATIONID having count(*) > 1) X;

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

                    -- overlapping locations

                    select @COUNT = count(*)
                    from @LOCATIONTABLE LOCATIONTAB1
                    inner join @LOCATIONTABLE LOCATIONTAB2 on 1=1
                    inner join dbo.SPONSORSHIPLOCATION L1 on L1.ID = LOCATIONTAB1.LOCATIONID
                    inner join dbo.SPONSORSHIPLOCATION L2 on L2.ID = LOCATIONTAB2.LOCATIONID
                    where L1.HIERARCHYPATH.IsDescendantOf(L2.HIERARCHYPATH) = 1
                    and L1.ID <> L2.ID;

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


                if @GROUPID is not null
                      begin
                    declare @GROUPLOCATIONID uniqueidentifier

                    select @GROUPLOCATIONID = SPONSORSHIPLOCATIONID 
                      from dbo.SPONSORSHIPOPPORTUNITYGROUP
                      where ID = @GROUPID

                          -- locations not consistent with group location

                          select @COUNT = count(*)
                          from @LOCATIONTABLE LOCATIONTAB
                          inner join dbo.SPONSORSHIPLOCATION OVERRIDELOCATION on OVERRIDELOCATION.ID = LOCATIONTAB.LOCATIONID
                          inner join dbo.SPONSORSHIPLOCATION GROUPLOCATION on GROUPLOCATION.ID = @GROUPLOCATIONID
                          where OVERRIDELOCATION.HIERARCHYPATH.IsDescendantOf(GROUPLOCATION.HIERARCHYPATH) = 0;

                          if @COUNT > 0
                              raiserror('BBERR_INVALIDGROUPLOCATION',13,1)                      
                      end         

                  declare @TOTAL int;
                   select @TOTAL = sum(LIMIT) from @LOCATIONTABLE

                  if @TOTAL > @MAXKEYNUMBER and @MAXKEYNUMBER > 0
                     raiserror('BBERR_LIMITGREATERTHANKEY',13,1) 
                  end             


                -- CREATE BUSINESS PROCESS ENTRY                     


               insert into dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS
                         ([ID]
                         ,[NAME]
                         ,[DESCRIPTION]
                         ,[IDSETREGISTERID]
               --,[OPPORTUNITYLOCATIONID]

               ,[CHILDGENDERCODE]
               ,[ISHIVPOSITIVECODE]
               ,[HASCONDITIONCODE]
               ,[ISORPHANEDCODE]
               ,[SPROPPAGERANGEID]
               ,[GROUPID]
               ,[MAXKEYNUMBER]             
                         ,[ADDEDBYID]
                         ,[CHANGEDBYID]
                         ,[DATEADDED]
                         ,[DATECHANGED]
               ,[ENDDATE]
                         ,[STATUSCODE]
               ,[INCLUDEPENDING]
               --,[INCLUDEINELIGIBLE]

               ,[MULTIPLELOCATION])
                   values(
                         @ID,
               @NAME,
                         @DESCRIPTION,
                           @IDSETREGISTERID,
                         --@OPPORTUNITYLOCATIONID,

               isnull(@GENDERCODE,0),
               isnull(@ISHIVPOSITIVECODE,0),
               isnull(@HASCONDITIONCODE,0),
               isnull(@ISORPHANEDCODE,0),
               @SPROPPAGERANGEID,
               @GROUPID,
               @MAXKEYNUMBER,
                         @CHANGEAGENTID,
                         @CHANGEAGENTID,
                         @CURRENTDATE,
                         @CURRENTDATE,
               @RESERVEENDDATE,
               0,
               @INCLUDEPENDING,
               --@INCLUDEINELIGIBLE,

               @MULTIPLELOCATION)      

               -- insert into reservation key status


               insert into dbo.SPONSORSHIPOPPORTUNITYRESERVATIONSTATUS
                         ([OPPORTUNITYRESERVATIONKEYID]
               ,[STATUSCODE]             
                         ,[ADDEDBYID]
                         ,[CHANGEDBYID]
                         ,[DATEADDED]
                         ,[DATECHANGED])
                   values(
                         @ID,
               0,
                         @CHANGEAGENTID,
                         @CHANGEAGENTID,
                         @CURRENTDATE,
                         @CURRENTDATE)                     

       end try

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