alter FUNCTION  [dbo].[GetNextDay](@dt datetime , @empcode varchar(50)  )
RETURNS datetime
AS 
BEGIN 
DECLARE @zoneid VARCHAR(50)
declare @lvlflag varchar(50)
declare @utdt DATETIME 
DECLARE @RETDT DATETIME 
DECLARE @COMPDT DATETIME


Select @lvlflag= b.ulm_user_field_flag from  bbraun_emis.dbo.emp_reference a join bbraun_emis.dbo.user_lvl_master b on b.ulm_user_lvl_id = a.ER_USER_LVL and a.er_emp_code = @empcode
SELECT  @zoneid = ZONECODE FROM bbraun_emis.dbo.VWREGIONLINK WHERE CITYCODE IN (SELECT DISTINCT HM_CITY_CODE FROM bbraun_emis.dbo.HOSP_MASTER WHERE HM_HOSP_CODE IN (SELECT HER_HOSP_CODE FROM bbraun_emis.dbo.HOSP_EMP_REL WHERE HER_EMP_CODE in(@EMPCODE)))
select  @compdt = holiday_date from  oriffconnect.dbo.holiday_master where zone_code = @zoneid and field_staff = @lvlflag and  holiday_date = @dt 

if(@@ROWCOUNT = 0)
	begin
		 
	Select @utdt = DATEADD(dd,1,@utdt)
        SeT   @utdt = ([dbo].[GetNextDay](@utdt , @empcode))
    end
IF(@@ROWCOUNT <> 0)
begin
set @utdt = @dt
end
Select @RETDT = @utdt
RETURN @RETDT

END