Hrcm Function Schema

Function Name:Check_AccountNumberReference

Description:

This function checks whether the AccountNumber exists or not before insertion.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[Check_AccountNumberReference]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Check_AccountNumberReference] (@AccountNumber varchar(25))
returns int
AS 
BEGIN
    Declare @v_RetVal   int;
    SET @v_RetVal = 0;
     If exists(select 1 from HIM_Visit where AccountNumber = @AccountNumber)
        BEGIN
           SET @v_RetVal = 1;
        END
    return @v_RetVal;
END
GO

======================================================

Function Name:CheckApplicationUserId

Description:

This function checks whether the ApplicationUserId exists or not before insertion.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[CheckApplicationUserId]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CheckApplicationUserId] (@ApplicationUserId int)
returns int
AS 
BEGIN
Declare @v_RetVal int;
      SET @v_RetVal = 0;
        If exists(select 1 from MasterHRCM.dbo.MST_ApplicationUser where ApplicationUserId=@ApplicationUserId)
              BEGIN
                    SET @v_RetVal = 1;
              END
      return @v_RetVal;
END
GO

======================================================

Function Name:CheckDqColumnId

Description:

This function checks whether the DQColumnId exists or not before insertion.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[CheckDqColumnId]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CheckDqColumnId] (@DqColumnId int)
returns int
AS 
BEGIN
Declare @v_RetVal int;
          SET @v_RetVal = 0;
              If exists(select 1 from MasterHRCM.dbo.MST_DqColumn where DqColumnId=@DqColumnId)
                          BEGIN
                                      SET @v_RetVal = 1;
                          END
          return @v_RetVal;
END
GO

======================================================

Function Name:CheckJobTitleId

Description:

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[CheckJobTitleId]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CheckJobTitleId] (@JobTitleId int)
returns int
AS 
BEGIN
Declare @v_RetVal int;
SET @v_RetVal = 0;
  If exists(select 1 from MasterHRCM.dbo.MST_JobTitle where JobTitleId=@JobTitleId)
    BEGIN
        SET @v_RetVal = 1;
    END
return @v_RetVal;
END
GO

======================================================

Function Name:Check_PhysicianIDReference

Description:

This function checks whether the PhysicianId exists or not before insertion.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[Check_PhysicianIDReference]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Check_PhysicianIDReference] (@PhysicianID varchar(30))
returns int
AS 
BEGIN
    Declare @v_RetVal   int;
    SET @v_RetVal = 0;
     If exists(select 1 from HIM_Physician where PhysicianID = @PhysicianID)
        BEGIN
           SET @v_RetVal = 1;
        END
    return @v_RetVal;
END
GO

======================================================

Function Name:Convert_Temperature

Description:

This function describes convertion's to tempareture's for inserting Vitals.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[Convert_Temperature]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Krishnareddy>
-- Create date: <Create Date,13/02/2017>
-- Description: <Description,For Vitals insertion converting standards>
-- =============================================
CREATE  FUNCTION [dbo].[Convert_Temperature]
(
@temperature NUMERIC(18,2),
@Degree CHAR
)
RETURNS NUmeric(18,2)

AS
BEGIN
--This function converts temperature vaue from one measure to other.
--If you pass Celsius then it will return Fahrenheit and vice versa.
DECLARE @Result AS NUMERIC(18,2)

IF UPPER(@Degree) = 'F'
SET @Result = (@temperature-32.0)* 5/9 --F to C conversion
IF UPPER(@Degree) ='C'
SET @Result = ((@temperature * 9) /5 )+ 32.0 --C to F Conversion

RETURN  @Result
END
GO

======================================================

Function Name:convertTimeZoneToUtc

Description:

This function describes converting any time zone to current UTC time.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[convertTimeZoneToUtc]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[convertTimeZoneToUtc]
(
@TimeZoneName varchar(5),
@Localtime datetime
)
returns DateTime
AS
BEGIN
Declare @UtcTime datetime
    If(@TimeZoneName='IST')
    Begin
    SET @UtcTime=DATEADD(MINUTE,-30,DATEADD(HOUR,-5,@Localtime))
    End
    else if(@TimeZoneName='PST')
    Begin
    SET @UtcTime=DATEADD(HOUR,+8,@Localtime)
    End
return @UtcTime
End
GO

======================================================

Function Name:convertUtcToTimeZone

Description:

This function describes converting current UTC time time zone to current time zone.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[convertUtcToTimeZone]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[convertUtcToTimeZone]
(
@TimeZoneName varchar(5),
@UtcTime datetime
)
returns DateTime
AS
BEGIN
Declare @TargetTime datetime
    If(@TimeZoneName='IST')
    Begin
    SET @TargetTime=DATEADD(MINUTE,+30,DATEADD(HOUR,+5,@UtcTime))
    End
    else if(@TimeZoneName='PST')
    Begin
    SET @TargetTime=DATEADD(HOUR,-8,@UtcTime)
    End
return @TargetTime
End
GO

======================================================

Function Name:fnFormatDate

Description:

This function describes information about Date format conditions.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[fnFormatDate]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnFormatDate] (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
with SchemaBinding
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask

    IF(@StringDate='YYYY')
        SET @StringDate = REPLACE(@StringDate, 'YYYY',DATENAME(YY, @Datetime))
    IF(@StringDate='YY')
        SET @StringDate = REPLACE(@StringDate, 'YY',RIGHT(DATENAME(YY, @Datetime),2))
    IF(@StringDate='Month')
        SET @StringDate = REPLACE(@StringDate, 'Month',DATENAME(MM, @Datetime))
    IF(@StringDate='Mon')
        SET @StringDate = REPLACE(@StringDate, 'Mon',LEFT(DATENAME(MM, @Datetime),3))
    IF(@StringDate='MM')
        SET @StringDate = REPLACE(@StringDate, 'MM',RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF(@StringDate='M')
        SET @StringDate = REPLACE(@StringDate, 'M',CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF(@StringDate='DD')
        SET @StringDate = REPLACE(@StringDate, 'DD',RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF(@StringDate='D')
        SET @StringDate = REPLACE(@StringDate, 'D',DATENAME(DD, @Datetime))    
    IF(@StringDate='HH')
        SET @StringDate = REPLACE(@StringDate, 'HH',RIGHT('0'+DATENAME(HH, @Datetime),2))    
    IF(@StringDate='MI')
        SET @StringDate = REPLACE(@StringDate, 'MI',RIGHT('0'+DATENAME(MI, @Datetime),2))    
    IF(@StringDate='SS')
        SET @StringDate = REPLACE(@StringDate, 'SS',RIGHT('0'+DATENAME(SS, @Datetime),2))    
    IF(@StringDate='MSS')
        SET @StringDate = REPLACE(@StringDate, 'MSS',DATENAME(MILLISECOND, @Datetime))    
    IF(@StringDate='HH:MI:SS')
        SET @StringDate = REPLACE(@StringDate, 'HH:MI:SS',CONVERT(varchar(10),@Datetime,108))
    IF(@StringDate='HH:MI')
        SET @StringDate = REPLACE(@StringDate, 'HH:MI',RIGHT('0'+DATENAME(HH, @Datetime),2)+':'+RIGHT('0'+DATENAME(MI, @Datetime),2))
    IF(@StringDate='MM/DD/YY')
        SET @StringDate = REPLACE(@StringDate, 'MM/DD/YY',CONVERT(varchar(8),@Datetime,1))
    IF(@StringDate='MM/DD/YYYY')
        SET @StringDate = REPLACE(@StringDate, 'MM/DD/YYYY',CONVERT(varchar(10),@Datetime,101))
    IF(@StringDate='DD/MM/YY')
        SET @StringDate = REPLACE(@StringDate, 'DD/MM/YY',CONVERT(varchar(8),@Datetime,3))
    IF(@StringDate='DD/MM/YYYY')
        SET @StringDate = REPLACE(@StringDate, 'DD/MM/YYYY',CONVERT(varchar(10),@Datetime,103))
    IF(@StringDate='Mon DD YYYY HH:MI')
        SET @StringDate = REPLACE(@StringDate, 'Mon DD YYYY HH:MI',CONVERT(varchar(25),@Datetime,100))
    IF(@StringDate='MM DD YYYY')
        SET @StringDate = REPLACE(@StringDate, 'MM DD YYYY',DATENAME(MM, @Datetime) + RIGHT(CONVERT(VARCHAR(12), @Datetime, 107), 9))
    IF(@StringDate='MM/DD/YYYY HH:MI:SS')
        SET @StringDate = REPLACE(@StringDate, 'MM/DD/YYYY HH:MI:SS',CONVERT(varchar(10),@Datetime,101)+ ' ' + CONVERT(varchar(8),@Datetime,108))
    IF(@StringDate='MM/DD/YYYY HH:MI')
        SET @StringDate = REPLACE(@StringDate, 'MM/DD/YYYY HH:MI',CONVERT(varchar(10),@Datetime,101)+ ' ' + CONVERT(varchar(5),@Datetime,108))
    IF(@StringDate='MM/DD')
        SET @StringDate = REPLACE(@StringDate, 'MM/DD',RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2)+ '/' + RIGHT('0'+DATENAME(DD, @Datetime),2))


RETURN @StringDate

END

--select dbo.fnFormatDate(GETDATE(),'MM/DD')
GO

======================================================

Function Name:fngetPhysician

Description:

With help of this function based of physicianId we can get Physician details.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[fngetPhysician]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fngetPhysician] (@PhysicianId varchar(30))
RETURNS VARCHAR(50)
AS
BEGIN
    Declare @PhyName varchar(80)=NULL
    select @PhyName=FirstName+' '+LastName from HIM_Physician where PhysicianID=@PhysicianId
    return @PhyName;    
END

--select dbo.fngetPhysician('03640')
--select dbo.fngetPhysician(NULL)
GO

======================================================

Function Name:fnGetPhysicianReference

Description:

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[fnGetPhysicianReference]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetPhysicianReference] (@AccountNumber varchar(30),@ElementType varchar(25),@SourceUniqueId int)
RETURNS VARCHAR(150)
AS
BEGIN
    Declare @DrName varchar(150)=NULL

    If(@ElementType='ICD')
    Begin
        select @DrName=
        'Dr. '+isnull(UPPER(LEFT(phy.FirstName, 1))+' '+ UPPER(LEFT(phy.LastName,1))+LOWER(SUBSTRING(phy.LastName,2,LEN(phy.LastName))),isnull(doc.PhysicianName,'XXX'))+' in '+dt.DocumentAliasName+' '+
        dbo.fnFormatDate(dbo.convertUtcToTimeZone('IST',doc.TranscriptionDateTime),'MM/DD')
        from NLP_DxCodeByDocument dd
        inner join NLP_DxCode final on final.AccountNumber=dd.AccountNumber and final.DxCode=dd.DxCode
        inner join HIM_Document doc on doc.documentId=dd.DocumentId
        inner join MST_DocumentType dt on dt.DocumentTypeID=doc.DocumentTypeID
        inner join MST_InternalDocumentType idt on idt.InternalDocumentTypeID=dt.InternalDocumentTypeID
        left join MST_Physician phy on phy.PhysicianID=doc.PhysicianID
        where final.AccountNumber=@AccountNumber and final.DxExclusionTypeId=0 and final.DxCodeId=@SourceUniqueId
    End
    else If (@ElementType='PCS')
    Begin
        select @DrName=
        'Dr. '+isnull(UPPER(LEFT(phy.FirstName, 1))+' '+ UPPER(LEFT(phy.LastName,1))+LOWER(SUBSTRING(phy.LastName,2,LEN(phy.LastName))),isnull(doc.PhysicianName,'XXX'))+' in '+dt.DocumentAliasName+' '+
        dbo.fnFormatDate(dbo.convertUtcToTimeZone('IST',doc.TranscriptionDateTime),'MM/DD')
        from NLP_PCSCodeByDocument dd
        inner join NLP_PCSCode final on final.AccountNumber=dd.AccountNumber and final.PCSCode=dd.PCSCode
        inner join HIM_Document doc on doc.documentId=dd.DocumentId
        inner join MST_DocumentType dt on dt.DocumentTypeID=doc.DocumentTypeID
        inner join MST_InternalDocumentType idt on idt.InternalDocumentTypeID=dt.InternalDocumentTypeID
        left join MST_Physician phy on phy.PhysicianID=doc.PhysicianID
        where final.AccountNumber=@AccountNumber and final.PcsExclusionTypeId=0 and final.PCSCodeID=@SourceUniqueId
    End
    else If(@ElementType='MedicalConcept')  
    Begin
        select @DrName=
        'Dr. '+isnull(UPPER(LEFT(phy.FirstName, 1))+' '+ UPPER(LEFT(phy.LastName,1))+LOWER(SUBSTRING(phy.LastName,2,LEN(phy.LastName))),isnull(doc.PhysicianName,'XXX'))+' in '+dt.DocumentAliasName+' '+
        dbo.fnFormatDate(dbo.convertUtcToTimeZone('IST',doc.TranscriptionDateTime),'MM/DD')
        from AQAS_QueryAlertElementResult qr
        inner join NLP_QueryAlert_MedicalConcept mc on qr.AccountNumber=mc.AccountNumber and mc.QAMedicalConceptId=qr.Result_Source_UniqueId
        inner join NLP_QueryAlert_MedicalConcept mc1 on mc1.AccountNumber=mc.AccountNumber and mc1.MedicalConcept=mc.MedicalConcept
        inner join HIM_Document doc on doc.documentId=mc1.DocumentId and doc.AccountNumber=mc1.AccountNumber
        inner join MST_DocumentType dt on dt.DocumentTypeID=doc.DocumentTypeID
        inner join MST_InternalDocumentType idt on idt.InternalDocumentTypeID=dt.InternalDocumentTypeID
        left join MST_Physician phy on phy.PhysicianID=doc.PhysicianID
        where qr.ClinicalElementTypeId=14 and qr.AccountNumber=@AccountNumber and mc1.Finding='Positive' and qr.Active=1 and mc1.QAMedicalConceptId=@SourceUniqueId
    End
    else If(@ElementType='DocumentContext')
    Begin
        /*
        select @DrName=
        'Dr. '+isnull(UPPER(LEFT(phy.FirstName, 1))+' '+ UPPER(LEFT(phy.LastName,1))+LOWER(SUBSTRING(phy.LastName,2,LEN(phy.LastName))),isnull(doc.PhysicianName,'XXX'))+' in '+dt.DocumentAliasName+' '+
        dbo.fnFormatDate(dbo.convertUtcToTimeZone('IST',doc.TranscriptionDateTime),'MM/DD')
        from User_QueryElementResult qr
        inner join HIM_Document doc on qr.AccountNumber=doc.AccountNumber and qr.DocumentId=doc.DocumentID
        inner join MST_DocumentType dt on dt.DocumentTypeID=doc.DocumentTypeID
        inner join MST_InternalDocumentType idt on idt.InternalDocumentTypeID=dt.InternalDocumentTypeID
        left join MST_Physician phy on phy.PhysicianID=doc.PhysicianID
        where qr.ClinicalElementTypeId=14 and qr.AccountNumber=@AccountNumber and qr.DocumentId=@SourceUniqueId and qr.Active=1*/
        select @DrName=
        'Dr. '+isnull(UPPER(LEFT(phy.FirstName, 1))+' '+ UPPER(LEFT(phy.LastName,1))+LOWER(SUBSTRING(phy.LastName,2,LEN(phy.LastName))),isnull(doc.PhysicianName,'XXX'))+' in '+dt.DocumentAliasName+' '+
        dbo.fnFormatDate(dbo.convertUtcToTimeZone('IST',doc.TranscriptionDateTime),'MM/DD')
        from HIM_Document doc
        inner join MST_DocumentType dt on dt.DocumentTypeID=doc.DocumentTypeID
        inner join MST_InternalDocumentType idt on idt.InternalDocumentTypeID=dt.InternalDocumentTypeID
        left join MST_Physician phy on phy.PhysicianID=doc.PhysicianID
        where doc.AccountNumber=@AccountNumber and doc.DocumentId=@SourceUniqueId

    End 


    return @DrName
END
GO

======================================================

Function Name:fngetUserName

Description:

with help of this function you can get full name of a User based on ApplicationUserId.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[fngetUserName]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fngetUserName] (@ApplicationUserId int,@reqName varchar(20))
RETURNS VARCHAR(50)
AS
BEGIN

    Declare @Name varchar(50)
    If(@reqName='UserName') 
    select @Name=UserName from MasterHRCM.dbo.MST_ApplicationUser where ApplicationUserId=@ApplicationUserId
    If(@reqName='FullName') 
    select @Name=FullName from MasterHRCM.dbo.MST_ApplicationUser where ApplicationUserId=@ApplicationUserId

    return @Name;

END

--select dbo.fngetUserName(7,'UserName')
--select dbo.fngetUserName(7,'FullName')
GO

======================================================

Function Name:fnMSDRGSuffix

Description:

With this function you can get Suffix of an MSDRG data.

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[fnMSDRGSuffix]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fnMSDRGSuffix](@DRGDESC varchar(300))
returns varchar(100)
as
begin
    declare @result varchar(100)
    set @result=null    
    select @result= case when @DRGDESC like '%W/O CC/MC%' then 'WOCCORMCC'
                when @DRGDESC like '%W CC/MCC%' then 'WCCORMCC'
                when @DRGDESC like '%W/O CC%' then 'WOCC'
                when @DRGDESC like '%W/O MCC%' then 'WOMCC'  
                when @DRGDESC like '%W CC%' then 'WCC'  
                when @DRGDESC like '%W MCC%' then 'WMCC' 
                else 'CC/MCC Not Applicable' end
    return @result
End
GO

======================================================

Function Name:fnParseByComma

Description:

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[fnParseByComma]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnParseByComma] (
    @String VARCHAR(max) )
RETURNS @TblSubString TABLE
(
    VarSubString VARCHAR(150)
)
AS
BEGIN
    DECLARE @intPos INT,
            @SubStr VARCHAR(max)

    -- Remove All Spaces
   -- SET @String = REPLACE(@String, ' ','')
    -- Find The First Comma
    SET @IntPos = CHARINDEX(',', @String)
    -- Loop Until There Is Nothing Left Of @String
    WHILE @IntPos > 0
    BEGIN
        -- Extract The String
        SET @SubStr = SUBSTRING(@String, 0, @IntPos)
        -- Insert The String Into The Table
        INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
        -- Remove The String & Comma Separator From The Original
        SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
        -- SET @String = REPLACE(@String, @SubStr + ',', '', 1)
        -- Get The New Index To The String
        SET @IntPos = CHARINDEX(',', @String)
    END
    -- Return The Last One
    INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END

-- select * from dbo.[fnParseByComma]('krishna,hari')
GO

======================================================

Function Name:split

Description:

Schema:

USE [HRCM]
GO
/****** Object:  UserDefinedFunction [dbo].[split]    Script Date: 08/23/2019 18:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[split](
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END
GO