CREATE  Procedure Mig_Roster
	@f_number varchar(8),
	@f_weekend datetime
AS	

DECLARE @id varchar(6),
	@entry1 varchar(36),
	@entry2 varchar(36),
	@entry3 varchar(36),
	@entry4 varchar(36),
	@entry5 varchar(36),
	@entry6 varchar(36),
	@entry7 varchar(36),
	@weekend datetime,
	@locationname char(40), 
	@payno varchar(8),
	@fullname char(40),
	@Atype varchar(2),
	@Ahours decimal(7,2),
	@Areason varchar(2),
	@entryinserted bit,
	@ReturnCode int,
	@error int,
	@values varchar(255)

BEGIN
	SET @entryinserted = 0

	--Get the rosterdatarecords for employee
	SELECT 	@id = rdr.id,
		@entry1 = rdr.e1,
		@entry2 = rdr.e2,
		@entry3 = rdr.e3,
		@entry4 = rdr.e4,
		@entry5 = rdr.e5,
		@entry6 = rdr.e6,
		@entry7 = rdr.e7,
		@locationname = l.name,
		@fullname = btas.fullname
	
	FROM datarecord rdr
		INNER JOIN rosrecord rr ON rdr.parent_id = rr.id
		INNER JOIN btass btas ON rr.assign = btas.id
		AND btas.pay = @f_number 
		INNER JOIN locationweek lw ON rr.parent_id = lw.id
			AND (lw.weekend = @f_weekend)
		INNER JOIN location l ON lw.parent_id = l.id 
			
		ORDER BY lw.weekend
	
	--Execute convertroster sproc to convert rosterentry data and return absence type/reasons codes
	BEGIN
		EXEC @ReturnCode = Convertroster @entry = @entry1,
					   @shift = 1,
					   @Abtype = @Atype OUTPUT,
					   @Abhours = @Ahours OUTPUT, 
				           @Abreason = @Areason OUTPUT	

		IF  @ReturnCode != 0
		--Write to exception table if error occurs 
		BEGIN
			SET @entryinserted = 1
			EXEC @ReturnCode = Exceptions @messtype = '01',
						@messtype = 'Error - Entry1'

			IF  @ReturnCode != 0 
			--Write to error table if exceptions update fails
			BEGIN
				SET @values = 'Paynumber = ' + @f_number + ' Error Number is ' + CONVERT(varchar, @error)
				EXEC Mig_WriteLog  @f_error = 'Creating exceptions table',
							@f_procedure =  'Roster',
							@f_values = @values
				RETURN -9
			END
		END					
		ELSE
		IF @entryinserted = 0
		--Insert employee roster details into roster table if no errors
		BEGIN
			INSERT INTO Tester.dbo.roster
				(staffname,
				paynumber,
				location,
				weekending,
				type,
				hours,
				reason,
				rosterentrydate)	
			
				VALUES
				(@fullname,
				@f_number,
				@locationname,
				@f_weekend,
				@Atype,
				@Ahours, 
				@Areason,
				DATEADD(Day,-6,@f_weekend)) 
					 
		END
		BEGIN
	
			EXEC @ReturnCode = MIG_Convertroster @entry = @entry2,
						   @shift = 1,
						   @Abtype = @Atype OUTPUT,
						   @Abhours = @Ahours OUTPUT, 
				           	@ABS_reason = @Areason OUTPUT	

			IF  @ReturnCode != 0 
			BEGIN
				SET @entryinserted = 1
				EXEC @ReturnCode = Exceptions @messtype = '02',
							@messtype = 'Error - Entry2'

				IF  @ReturnCode != 0 
				BEGIN
					SET @values = 'Paynumber = ' + @f_number + ' Error Number is ' + CONVERT(varchar, @error)
					EXEC Mig_WriteLog  @f_error = 'Creating exceptions table',
								@f_procedure =  'Roster',
								@f_values = @values
					RETURN -9
				END
			END	
		END
		
		IF @entryinserted = 0
		BEGIN
			INSERT INTO Tester.dbo.roster
				(staffname,
				paynumber,
				location,
				weekending,
				type,
				hours,
				reason,
				rosterentrydate)	
			
				VALUES
				(@staffname,
				@f_paynumber,
				@locationname,
				@f_weekending,
				@type,
				@hours, 
				@reason,
				DATEADD(Day,-1,@f_weekend)) 
					 
		END
	END

END
	
	

quote:

---

	--Execute convertroster sproc to convert rosterentry data and return absence type/reasons codes
	BEGIN
		Declare @entryNum int
		Set @entryNum = 1

		While @entryNum<8
		Begin
			if (@entryNum=1) Set @entry = @entry1
			if (@entryNum=2) Set @entry = @entry2
			if (@entryNum=3) Set @entry = @entry3
			if (@entryNum=4) Set @entry = @entry4
			if (@entryNum=5) Set @entry = @entry5
			if (@entryNum=6) Set @entry = @entry6
			if (@entryNum=7) Set @entry = @entry7

			EXEC @ReturnCode = Convertroster @entry,
						   @shift = 1,
						   @Abtype = @Atype OUTPUT,
						   @Abhours = @Ahours OUTPUT, 
					           @Abreason = @Areason OUTPUT	
			
			IF  @ReturnCode != 0
			--Write to exception table if error occurs 
			BEGIN
				SET @entryinserted = 1
				EXEC @ReturnCode = Exceptions @messtype = '01',
							@messtype = 'Error - Entry1'
	
				IF  @ReturnCode != 0 
				--Write to error table if exceptions update fails
				BEGIN
					SET @values = 'Paynumber = ' + @f_number + ' Error Number is ' + CONVERT(varchar, @error)
					EXEC Mig_WriteLog  @f_error = 'Creating exceptions table',
								@f_procedure =  'Roster',
								@f_values = @values
					RETURN -9
				END
			END					
			ELSE
			IF @entryinserted = 0
			--Insert employee roster details into roster table if no errors
			BEGIN
				INSERT INTO Tester.dbo.roster
					(staffname,
					paynumber,
					location,
					weekending,
					type,
					hours,
					reason,
					rosterentrydate)	
				
					VALUES
					(@fullname,
					@f_number,
					@locationname,
					@f_weekend,
					@Atype,
					@Ahours, 
					@Areason,
					DATEADD(Day,-6,@f_weekend)) 
						 
			END

			Set @entryNum = @entryNum + 1
		End
	END

---