
PRINT N'Refreshing View [sOrderEntry].[vExtListValueMaster]...';


GO
EXECUTE sp_refreshsqlmodule N'[sOrderEntry].[vExtListValueMaster]';


GO
PRINT N'Refreshing View [sOrderEntry].[vExtTestParameterMaster]...';


GO
EXECUTE sp_refreshsqlmodule N'[sOrderEntry].[vExtTestParameterMaster]';


GO
PRINT N'Refreshing View [sOrderEntry].[vGetOrganizationTestParameter]...';


GO
EXECUTE sp_refreshsqlmodule N'[sOrderEntry].[vGetOrganizationTestParameter]';


GO
PRINT N'Creating View [sHospitalization].[vIntimationAssignedDoctor]...';


GO
CREATE VIEW [sHospitalization].[vIntimationAssignedDoctor]
AS

WITH ConfigCheck AS
(
    SELECT 
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM [sConfig].[tConfigurationMaster] CM
                WHERE CM.ItemKey = 'ClaimIntimation.HospitalizationAssignDoctorMaster'
                AND CM.ItemValue = 'True'
                AND CM.IsObsolete = 0
            )
            THEN 1 
            ELSE 0
        END AS UseNewTable
)

SELECT *
FROM
(
    ------------------------------------------------
    -- NEW TABLE
    ------------------------------------------------
    SELECT
        N.Id,
        N.OrgId,
        N.BusinessId,
        N.SubBusinessId,
        N.LocationId,
        N.SubLocationId,
        N.StateId,
        N.OrganizationId,
        N.DoctorEmployeeId,
        N.AssignDesignationId AS DesignationId,
        N.AssignDoctorIndex,
        N.IsObsolete,
        N.CreatedUserId,
        N.CreatedDateTime,
        N.UpdatedUserId,
        N.UpdatedDateTime,
        N.AssignDesignationName,
        NULL AS AssignTypeId,
        NULL AS AssignSubTypeId,
        1 AS TableType
    FROM [sHospitalization].[tClaimIntimationAssignedDoctor] N
    WHERE N.IsObsolete = 0

    UNION ALL

    ------------------------------------------------
    -- OLD TABLE
    ------------------------------------------------
    SELECT
        O.Id,
        O.OrgId,
        NULL AS BusinessId,
        NULL AS SubBusinessId,
        NULL AS LocationId,
        NULL AS SubLocationId,
        NULL AS StateId,
        NULL AS OrganizationId,
        O.DoctorEmployeeId,
        O.DesignationId AS DesignationId,
        NULL AS AssignDoctorIndex,
        O.IsObsolete,
        O.CreatedUserId,
        O.CreatedDateTime,
        O.UpdatedUserId,
        O.UpdatedDateTime,
        NULL AS AssignDesignationName,
        NULL AS AssignTypeId,
        NULL AS AssignSubTypeId,
        0 AS TableType
    FROM [sHospitalization].[tIntimationAssignedDoctor] O
    WHERE O.IsObsolete = 0

) AD
WHERE AD.TableType =
(
    SELECT UseNewTable FROM ConfigCheck
);
GO
PRINT N'Altering Function [sWellness].[fn_IsEmployeeRegistered]...';


GO
ALTER FUNCTION [sWellness].[fn_IsEmployeeRegistered]
(
    @rProgramId BIGINT,
    @rEmployeeId BIGINT
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = 0;

	-- Registered Not Required
    IF EXISTS
    (
        SELECT 1
        FROM [sWellness].[tProgramMaster] PM
        WHERE PM.Id = @rProgramId
          AND PM.IsRegistrationRequired <> 1
    )
    BEGIN
        SET @Result = 3;
    END

    -- Already registered

    ELSE IF EXISTS
    (
        SELECT 1
        FROM [sWellness].[tProgramParticipant] PP
        WHERE PP.ProgramId = @rProgramId
          AND PP.ParticipantSourceId = @rEmployeeId
    )
    BEGIN
        SET @Result = 1;
    END

    -- Registration closed
    ELSE IF EXISTS
    (
        SELECT 1
        FROM [sWellness].[tProgramMaster] PM
        WHERE PM.Id = @rProgramId
          AND CONVERT(DATE,PM.RegisterUptoDatetime,103) < CONVERT(DATE,sCommon.fGetDate(),103)
    )
    BEGIN
        SET @Result = 2;
    END

    RETURN @Result;
END
GO
PRINT N'Refreshing Function [dbo].[fAbnormalFindingsHealthIndicator]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[fAbnormalFindingsHealthIndicator]';


GO
PRINT N'Refreshing Function [dbo].[fn_GetTestProfileServiceValue_Inline]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[fn_GetTestProfileServiceValue_Inline]';