USP_DATAFORMTEMPLATE_PREPROCESS_EDIT_SPONSORSHIPOPPORTUNITYRESERVEBUSINESS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@IDSETREGISTERID	uniqueidentifier	IN
@GROUPID	uniqueidentifier	IN
@GENDERCODE	int	IN
@SPROPPAGERANGEID	uniqueidentifier	IN
@ISHIVPOSITIVECODE	int	IN
@HASCONDITIONCODE	int	IN
@ISORPHANEDCODE	int	IN
@MAXKEYNUMBER	int	IN
@RESERVEENDDATE	datetime	IN
@INCLUDEPENDING	bit	IN
@MULTIPLELOCATION	xml	IN
Definition

Copy


        CREATE procedure dbo.USP_DATAFORMTEMPLATE_PREPROCESS_EDIT_SPONSORSHIPOPPORTUNITYRESERVEBUSINESS
            (        
                @ID uniqueidentifier ,
                @CHANGEAGENTID uniqueidentifier = null,           
              @NAME nvarchar(100),
              @DESCRIPTION nvarchar(255),
              @IDSETREGISTERID uniqueidentifier,
              @GROUPID uniqueidentifier,    
                --@OPPORTUNITYLOCATIONID uniqueidentifier,    

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

          @INCLUDEPENDING bit,
          --@INCLUDEINELIGIBLE bit

          @MULTIPLELOCATION xml
              )
              as          
                set nocount on;

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

              declare @CURRENTDATE datetime
              set @CURRENTDATE = getdate()

           if @MAXKEYNUMBER is null
           set @MAXKEYNUMBER  = 0

          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             

                -- update BP                    


              update dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS
                     set NAME = @NAME,
               DESCRIPTION= @DESCRIPTION,
               IDSETREGISTERID= @IDSETREGISTERID,
               --OPPORTUNITYLOCATIONID= @OPPORTUNITYLOCATIONID,

               CHILDGENDERCODE= isnull(@GENDERCODE,0),
               ISHIVPOSITIVECODE= isnull(@ISHIVPOSITIVECODE,0),
               HASCONDITIONCODE= isnull(@HASCONDITIONCODE,0),
               ISORPHANEDCODE= isnull(@ISORPHANEDCODE,0),
               SPROPPAGERANGEID= @SPROPPAGERANGEID,
               GROUPID= @GROUPID,
               MAXKEYNUMBER= @MAXKEYNUMBER,
               CHANGEDBYID= @CHANGEAGENTID,
               DATECHANGED= @CURRENTDATE,
               ENDDATE = @RESERVEENDDATE,
               INCLUDEPENDING = @INCLUDEPENDING,
               --INCLUDEINELIGIBLE = @INCLUDEINELIGIBLE

               MULTIPLELOCATION = @MULTIPLELOCATION
           where ID = @ID
       end try

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