
/* Detail : This procedure is created by Yogesh Chaudhari on 15th April 2016
   Purpose : This procedure is used to updated reportID column by Row_Number in ACA_1095CEmployeeLevelData and ACA_1095BEmployeeData table as per its ReportRunID*/


IF NOT EXISTS (SELECT * FROM SYS.objects WHERE name = 'OES_SP_ACA_UpdateRecordId' AND [type] = 'P')
BEGIN
	EXEC ('
			CREATE PROCEDURE OES_SP_ACA_UpdateRecordId
			AS
			BEGIN
				PRINT(''OES_SP_ACA_UpdateRecordId'')
			END
		')
		
END
GO


ALTER PROCEDURE [dbo].[OES_SP_ACA_UpdateRecordId] (@StaticReportTypeID INT,@ReportRunID BIGINT)
AS
	BEGIN

		DECLARE @IsRecordIDNull Int

		If @StaticReportTypeID = 20 
			BEGIN
				Select  Top 1 @IsRecordIDNull = RecordId from ACA_1095CEmployeeLevelData where ReportRunID=@ReportRunID order by EMployeeID

				If (ISNull(@IsRecordIDNull,0) = 0)  
				BEGIN		
					UPDATE ACA_1095CEMPData 	SET ACA_1095CEMPData.RecordID = ACA_1095CEMPData.New_RecordID FROM 
					(SELECT RecordID, ROW_NUMBER() OVER (ORDER BY employeeid) AS New_RecordID FROM ACA_1095CEmployeeLevelData 
					where ReportRunID =@ReportRunID) ACA_1095CEMPData      
				END	
			END
		Else
			BEGIN
				Select  Top 1 @IsRecordIDNull = RecordId from ACA_1095BEmployeeData where ReportRunID=@ReportRunID order by EMployeeID

				If (ISNull(@IsRecordIDNull,0) = 0)  
				BEGIN		
					UPDATE ACA_1095BEMPData 	SET ACA_1095BEMPData.RecordID = ACA_1095BEMPData.New_RecordID FROM 
					(SELECT RecordID, ROW_NUMBER() OVER (ORDER BY employeeid) AS New_RecordID FROM ACA_1095BEmployeeData 
					where ReportRunID =@ReportRunID) ACA_1095BEMPData      
				END	
			END	
		
	END
GO