if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalculateCrossDays]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CalculateCrossDays]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalculateExcludeDays]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CalculateExcludeDays]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalculateReAdmitDifferenceDaysForAbstract]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CalculateReAdmitDifferenceDaysForAbstract]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalculateReAdmitDifferenceDaysForDAD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CalculateReAdmitDifferenceDaysForDAD]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalculateReAdmitDifferenceDaysForNACRS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CalculateReAdmitDifferenceDaysForNACRS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalculateTotalDays]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CalculateTotalDays]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckInvalidateDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CheckInvalidateDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckLogin]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CheckLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckUnknownDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CheckUnknownDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckUnknownTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CheckUnknownTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ConvertToDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ConvertToDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ConvertToTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ConvertToTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_F_CalcAgeCode]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_F_CalcAgeCode]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_F_CalculateAge]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_F_CalculateAge]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_F_GetQuery]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_F_GetQuery]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_F_Get_Database]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_F_Get_Database]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_F_Upload_GetRecodeText]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_F_Upload_GetRecodeText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_F_Upload_GetSqlFrom]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_F_Upload_GetSqlFrom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_F_Upload_GetSqlSelect]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_F_Upload_GetSqlSelect]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_GET_DATE_int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_GET_DATE_int]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DSS_GET_TIME_int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DSS_GET_TIME_int]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetConditionComparisonType]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetConditionComparisonType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetFieldComparisonType]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetFieldComparisonType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetFieldConditionOperator]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetFieldConditionOperator]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetFieldOccurrence]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetFieldOccurrence]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetGrouperHeader]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetGrouperHeader]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetLogicalConditionOperator]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetLogicalConditionOperator]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetModuleDescription]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetModuleDescription]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetRuleSeverity]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetRuleSeverity]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMT_GetSameOccurrenceMultiFormDescription]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMT_GetSameOccurrenceMultiFormDescription]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get4DigitTime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Get4DigitTime]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCorrectionForMerge]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetCorrectionForMerge]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetFixedChartNumber]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetFixedChartNumber]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetGrouperAuditValue]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetGrouperAuditValue]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetPostalDescription]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetPostalDescription]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetQuestionValues]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetQuestionValues]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRehabActiveLos]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetRehabActiveLos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get_NACRSCareTypeByMisCodes]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Get_NACRSCareTypeByMisCodes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IsDateCross]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[IsDateCross]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_ADL01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_ADL01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_ADL05]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_ADL05]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_ADL06]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_ADL06]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_ADL1A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_ADL1A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_ADL5A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_ADL5A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_ADL6A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_ADL6A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_ADLD7]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_ADLD7]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_BEHD4]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_BEHD4]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_BEHI4]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_BEHI4]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_CAT02]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_CAT02]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_CNT02]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_CNT02]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_CNT03]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_CNT03]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_CNT04]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_CNT04]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_CNT2A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_CNT2A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_CNT3A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_CNT3A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_COG01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_COG01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_COG1A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_COG1A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_COM01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_COM01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_COM1A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_COM1A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_DEL0X]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_DEL0X]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_DRG01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_DRG01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_FAL02]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_FAL02]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_GetPreviousID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_GetPreviousID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_INF0X]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_INF0X]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_MOB01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_MOB01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_MOB1A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_MOB1A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_MOD4A]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_MOD4A]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_NUT01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_NUT01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_PAI0X]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_PAI0X]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_PAN01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_PAN01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_PRU05]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_PRU05]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_PRU06]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_PRU06]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_PRU09]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_PRU09]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_RES01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_RES01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_RSPX2]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_RSPX2]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MDS20_QI_WGT01]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MDS20_QI_WGT01]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MF_IndexName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MF_IndexName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MF_PrimaryKeyName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MF_PrimaryKeyName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ModuleConcat]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ModuleConcat]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NACRS_CareType_MisCodes]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[NACRS_CareType_MisCodes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RemoveLeadingZero]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[RemoveLeadingZero]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_CRS_Main_DateField]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_CRS_Main_DateField]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_DAD_Provider_Prefix_9]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_DAD_Provider_Prefix_9]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_EncounterNumber_RPB]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_EncounterNumber_RPB]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GET_GroupID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GET_GroupID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetAlbertaSubmissionType]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetAlbertaSubmissionType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetAlbertaSubmitterPrefix]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetAlbertaSubmitterPrefix]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetFromClauseBaseTbl]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetFromClauseBaseTbl]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetFromClauseSegment]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetFromClauseSegment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetIAR_AssessmentType]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetIAR_AssessmentType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetPatientAddress]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetPatientAddress]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetPeriodDateFr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetPeriodDateFr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetPeriodDateRange]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetPeriodDateRange]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetPeriodDateTo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetPeriodDateTo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetProvinceCode]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetProvinceCode]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetQuery]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetQuery]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetRecodeSegment]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetRecodeSegment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetRecodeText]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetRecodeText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetSelectClauseSegment]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetSelectClauseSegment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetSqlBaseTbl]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetSqlBaseTbl]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetSqlFrom]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetSqlFrom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetSqlSelect]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetSqlSelect]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetTableAlias]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetTableAlias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetTempTable]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetTempTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_GetULI]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_GetULI]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_Group_Case_Weight]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_Group_Case_Weight]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_Group_Case_Weight_2012]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_Group_Case_Weight_2012]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_HCNVersion_Concatenate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_HCNVersion_Concatenate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_Recode_RemoveDecimal]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_Recode_RemoveDecimal]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUF_RehabWight]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SUF_RehabWight]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_ChartNumberFillLeadingZero]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_ChartNumberFillLeadingZero]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_ACCS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_ACCS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_CCRS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_CCRS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_CJRR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_CJRR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_DAD_ICD10]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_DAD_ICD10]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_IAR_MH]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_IAR_MH]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_MACRS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_MACRS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_MADE_ICD10]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_MADE_ICD10]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_NACRS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_NACRS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_OMHRS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_OMHRS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_FILENAME_ReciprocalBilling]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_FILENAME_ReciprocalBilling]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_GetFromClauseBaseTbl]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_GetFromClauseBaseTbl]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_GetFromClauseSegment]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_GetFromClauseSegment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_GetSelectClauseSegment]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_GetSelectClauseSegment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_GetSpecialProjectQuestion]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_GetSpecialProjectQuestion]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_GetSpecialProjectQuestionByQuestion]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_GetSpecialProjectQuestionByQuestion]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_SELECT_CCRS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_SELECT_CCRS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_SELECT_CCRS_NEW]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_SELECT_CCRS_NEW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_SELECT_CCRS_OLD]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_SELECT_CCRS_OLD]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SU_SELECT_OMHRS]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[SU_SELECT_OMHRS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ToNumber]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ToNumber]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getTodayDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getTodayDate]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE    Function [dbo].[CalculateCrossDays](
    @DefStartDate datetime,
    @DateFrom1 datetime,
    @DateTo1   datetime,
    @DateFrom2     datetime,
    @DateTo2       datetime,
    @Now          datetime)
    returns int
as
begin
    declare @Days  int
    declare @Start datetime
    declare @End   datetime
    
    set @Days = 0
    if dbo.IsDateCross(@DateFrom1, @DateTo1, @DateFrom2, @DateTo2) = 1
    begin
        if @DateFrom1 <= @DefStartDate 
        begin
            if @DefStartDate < @DateFrom2
                set @Start = @DateFrom2
            else
                set @Start = @DefStartDate
        end
        else
        begin
            if @DateFrom1 < @DateFrom2
                set @Start = @DateFrom2
            else
                set @Start = @DateFrom1
        end

        if @DateTo1 < @Now 
        begin
            if @DateTo1 <= @DateTo2
                set @End = @DateTo1
            else
                set @End = @DateTo2
        end
        else
        begin
            if @Now <= @DateTo2
                set @End = @Now
            else
                set @End = @DateTo2
        end
        set @Days = datediff(day, @Start, @End) 
        if @Days < 0 set @Days = 0
        else if @End < @Now set @Days = @Days + 1    -- EN-00293 count today @End <= @Now Waiting prove
    end

    return(@Days)
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create Function CalculateExcludeDays(
    @DefStartDate datetime,
    @DateFrom1    datetime,
    @DateTo1      datetime,
    @DateFrom2    datetime,
    @DateTo2      datetime,
    @Now          datetime)
    returns int
as
begin
    declare @Days  int
    declare @Start datetime
    declare @End   datetime
    
    set @Days = 0
    if @DateFrom1 > @DateTo2 or @DateFrom2 > @DateTo1
    begin
        if @DateFrom1 <= @DefStartDate 
            if @DateTo1 < @Now
                set @Days = datediff(day, @DefStartDate, @DateTo1) 
            else
                set @Days = datediff(day, @DefStartDate, @Now)
        else
            if @DateTo1 < @Now
                set @Days = datediff(day, @DateFrom1, @DateTo1) + 1
            else
                set @Days = datediff(day, @DateFrom1, @Now)
    end
    else 
    begin
        if @DateFrom2 between @DateFrom1 and @DateTo1 
        begin
            --Calculate first part
            set @Start = @DateFrom1
            set @End   = @DateFrom2
    
            if @DefStartDate > @DateFrom1
                set @Start = @DefStartDate
            if @End > @Now
                set @End = @Now
            if @Start < @End 
                set @Days = datediff(day, @Start, @End)
        end
        if @DateTo2 between @DateFrom1 and @DateTo1
        begin
            --Calculate last part
            set @Start = @DefStartDate
            set @End   = @DateTo1
    
            if @DateTo2 > @Start
                set @Start = @DateTo2
            if @End > @Now
                set @End = @Now
            if @Start < @End 
                set @Days = @Days + Datediff(Day, @Start, @End)
        end
    end

    return(@Days)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function dbo.CalculateReAdmitDifferenceDaysForAbstract(
    @ChartNumber varchar(20),
    @AdmitDate datetime,
    @AdmitTime datetime,
    @AbstractID int
    )
    returns int
as
begin
    declare @days int
    
    set @days = (select min(datediff(day, r.Admitdate, @AdmitDate)) from AB_Abstract r 
        where r.ChartNumber = @ChartNumber and r.AbstractID <> @AbstractID 
            and (r.Admitdate < @Admitdate or (r.Admitdate = @Admitdate and [dbo].[DSS_GET_TIME_int](AdmitTime) <= [dbo].[DSS_GET_TIME_int](@AdmitTime))))
        
    return(@days)        
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[CalculateReAdmitDifferenceDaysForDAD](
    @ChartNumber varchar(20),
    @AdmitDate datetime,
    @AbstractID int
    )
    returns int
as
begin
    declare @days int
    
    set @days = (select min(datediff(day, r.dischargedate, @AdmitDate)) from AB_Abstract r 
        where r.ChartNumber = @ChartNumber and r.AbstractID <> @AbstractID and r.dischargedate <= @Admitdate and r.CareType = 1 )
        
    return(@days)        
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function dbo.CalculateReAdmitDifferenceDaysForNACRS(
    @ChartNumber varchar(20),
    @AdmitDate datetime,
    @AbstractID int
    )
    returns int
as
begin
    declare @days int
    
    set @days = (select min(datediff(day, r.Admitdate, @AdmitDate)) from AB_Abstract r 
        where r.ChartNumber = @ChartNumber and r.AbstractID <> @AbstractID and r.Admitdate <= @Admitdate and r.CareType = 5 )
        
    return(@days)        
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  Function CalculateTotalDays(
    @DefStartDate datetime,
    @DateFrom1    datetime,
    @DateTo1      datetime,
    @Now          datetime)
    returns int
as
begin
    declare @Days  int
    declare @Start datetime
    declare @End   datetime
    
    set @Days = 0
    if @DateFrom1 <= @DefStartDate 
        set @Start = @DefStartDate
    else
        set @Start = @DateFrom1

    if @DateTo1 < @Now 
        set @End = @DateTo1
    else
        set @End = @Now

    set @Days = datediff(day, @Start, @End) 
    if @End < @Now set @Days = @Days + 1 --EN-00293 count today  @End <= @Now Waiting Proved

    return(@Days)
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create  Function [dbo].[CheckInvalidateDate](
    @value varchar(100)
)    
    returns char(1)
as
begin
    return(case when isdate(@value) = 0 and isnull(@value, '') <> '' then 'Y' else '' end)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  function [dbo].[CheckLogin]()
    returns @value table(HospitalID int primary key)
begin
    if suser_sname() in ('MED2020Main', 'WinRecs') 
        insert into @value values(0)
    else if (select count(*) from H_HospitalProfile) = 1
        insert into @value values(0)
    else 
        insert into @value select top 1 isnull(HospitalID, 0) from u_users u where u.NTAuthority = suser_sname() order by isnull(HospitalID, 0)

    return 
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  Function [dbo].[CheckUnknownDate](
    @value varchar(100)
)    
    returns char(1)
as
begin
    return(case when @value = '99999999' then 'Y' else '' end)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  Function [dbo].[CheckUnknownTime](
    @value varchar(100)
)    
    returns char(1)
as
begin
    return(case when @value = '9999' then 'Y' else '' end)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  Function [dbo].[ConvertToDate](
    @value varchar(100)
)    
    returns datetime
as
begin
    return(case when isdate(@value) = 1 then cast(@value as datetime) else null end)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  Function [dbo].[ConvertToTime](
    @value varchar(100)
)    
    returns datetime
as
begin
    return(case when isdate('1900/01/01 ' + left(@value, 2) + ':' + substring(@value, 3, 2)) = 1 then cast('1900/01/01 ' + left(@value, 2) + ':' + substring(@value, 3, 2) as datetime) else null end)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function DSS_F_CalcAgeCode(@birthday datetime, @admitday datetime)
	returns int
as
begin
	declare @code int
	declare @months int
	declare @days int

    set @months = datediff(month, @birthday, @admitday)
	if (@months is null)
		set @code = null
	else
    begin
		if @months <= 2 
		begin
        	set @days = datediff(day, @birthday, @admitday)
			if @days < 31 
            begin
                set @code = 1 
--                if datepart(month, @birthday) < datepart(month, @admitday) 
--                    and datepart(day, @birthday) <= datepart(day, @admitday) 
--                    set @code = 2 
            end
            else set @code = 2
		end
		else 
            if @months < 24 or (@months = 24 and datepart(day, @birthday) > datepart(day, @admitday))
                set @code = 2 
            else 
                set @code = 3
    end
	return ( @code )
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function DSS_F_CalculateAge(@BirthDate datetime, @AdmitDate datetime)
	returns int
as
begin
    return (case dbo.DSS_F_CalcAgeCode(@BirthDate, @AdmitDate)
        when 1 then datediff(day,   @BirthDate, @AdmitDate)
        when 2 then datediff(month, @BirthDate, @AdmitDate) - (case when datepart(day, @Birthdate) > datepart(day, @Admitdate) then 1 else 0 end)
        when 3 then datediff(year,  @BirthDate, @AdmitDate) - (case when datepart(month, @Birthdate) > datepart(month, @Admitdate) then 1 when datepart(month, @Birthdate) = datepart(month, @Admitdate) and datepart(day, @Birthdate) > datepart(day, @Admitdate) then 1 else 0 end)
        else null
	end)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function dbo.DSS_F_GetQuery
(
      @ExecutionID int
    , @AppID int
    , @SubID int 
    , @HospitalID  int
)
returns @Query table 
( 
  SegmentType int
, OrderBy int
, SqlSegment varchar(1000)
)
as 
begin

    declare @TableName  varchar(50) 
    declare @TableAlias varchar(50)
    declare @OrderBy    int


    -- set the base table to be used for all queries 
--     if (@AppID in (9999470, 470, 471, 472, 473, 474, 475))  
--         set @BaseTable = 'CRS_Demographic' 
--     else if (@GroupID in (20, 21, 22, 23))  
--         set @BaseTable = 'RH_ClientInfo' 
--     else  
--         set @BaseTable = 'AB_Abstract' 

     
    -- dynamic insert statement based on DSS_Upload_StructureFile 
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select top 1 1, 0, 'insert into ' + dbo.DSS_F_Get_Database(@ExecutionID, @HospitalID) + TargetTable + '(AbstractID'
    from DSS_Upload_StructureFile  
    where AppID = @AppID and SubID = @SubID 

    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select  
          OrderBy     = OrderBy + 10
        , SegmentType = 0 
        , SqlSegment  = ', ' + TargetField
    from DSS_Upload_StructureFile  
    where AppID = @AppID and SubID = @SubID 
    order by OrderBy

    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select distinct 
          OrderBy     = (select max(OrderBy) + 1 from @Query where SegmentType = 0) 
        , SegmentType = 0 
        , SqlSegment  = ') ' 

    -- define the select clause for each query  
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select 1, 1, 'select RecordSelect.AbstractID' 
     
    --_______________________________________________________________________________________________________________ 
    -- define the base from clause common to each query  
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select distinct  
      OrderBy     = 1 
    , SegmentType = 2 
    , SqlSegment  = 
        'from DSS_Upload_ExecProperties SuProperties ' +  
        'join DSS_Upload_RecordSelect RecordSelect on SuProperties.ExecutionID = RecordSelect.ExecutionID ' + 
        'join AB_Main AB_Main on AB_Main.UniversalID = RecordSelect.LinkID ' 
    from DSS_Upload_StructureFile  
    where AppID = @AppID and SubID = @SubID 

    --_______________________________________________________________________________________________________________ 
    -- define the sub tables joined to the base from clause (this differs per query) 

    set @OrderBy = 2

    declare cur_upload_table cursor for
        select distinct TableName, TableAlias 
        from dbo.DSS_Upload_StructureFile   
        where AppID = @AppID and SubID = @SubID and TableName <> 'AB_Main'
    open cur_upload_table
    fetch from cur_upload_table into @TableName, @TableAlias
    while(@@fetch_status = 0)
    begin
        insert @Query ( OrderBy, SegmentType, SqlSegment ) 
        select distinct  
          OrderBy     = @OrderBy 
        , SegmentType = 2 
        , SqlSegment  = 'left join ' + @TableName + ' ' + @TableAlias + (case when @TableName = 'AB_Main' then ' on AB_Main.UniversalID = ' + @TableAlias + '.UniversalID ' else ' on AB_Main.AbstractID = ' + @TableAlias + '.AbstractID ' end)
        set @OrderBy = @OrderBy  + 1
        fetch from cur_upload_table into @TableName, @TableAlias
    end
    close  cur_upload_table
    deallocate cur_upload_table

    --_______________________________________________________________________________________________________________ 
    -- dynamic select and from clauses based on DSS_Upload_StructureFile 
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select  
          OrderBy     = OrderBy + 10
        , SegmentType = 1 
        , SqlSegment  = ', ' + dbo.DSS_F_Upload_GetSqlSelect(AppID, SubID, TargetTable, TargetField, TableName, TableAlias, FieldName, FieldAlias, FieldID, RecodeID, IsDisplay)
    from DSS_Upload_StructureFile  
    where AppID = @AppID and SubID = @SubID 
    order by OrderBy
     
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select  
          OrderBy     = OrderBy + 10
        , SegmentType = 2 
        , SqlSegment  = dbo.DSS_F_Upload_GetSqlFrom( AppID, TableAlias, FieldAlias, FieldName, FieldID ) 
    from (select AppID, SubID, TableName, TableAlias, FieldAlias, FieldName, FieldID, min(OrderBy) as OrderBy
        from DSS_Upload_StructureFile 
        where AppID = @AppID and SubID = @SubID 
        group by AppID, SubID, TableName, TableAlias, FieldAlias, FieldName, FieldID) a
    order by OrderBy 
     
    --_______________________________________________________________________________________________________________ 
    -- static where clause and order by clause with difference only in the @ExecutionID 
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select distinct 
          OrderBy     = (select max(OrderBy) + 1 from @Query where SegmentType = 2) 
        , SegmentType = 2 
        , SqlSegment  = 'where RecordSelect.ExecutionID = ' + convert( varchar(10), @ExecutionID ) + 
            (case when @SubID = 1 then ' ' else ' and AB.AbstractID is not null ' end) + 
            'order by RecordSelect.AbstractID ' 
    
    return 
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create Function DSS_F_Get_Database(
    @ExecutionID int, 
    @HospitalID int
)   returns varchar(100)
as
begin
    declare @Database  varchar(50)
    declare @Server    varchar(50)
    declare @Local_Server varchar(100)
    declare @Local_Database varchar(100)
    set @Local_Server = cast( SERVERPROPERTY( 'servername' ) as varchar(100) )
    set @Local_Database = db_name()
    select top 1 @Server = ltrim( rtrim( ServerName )), @Database = ltrim( rtrim( DatabaseName ))
    from dbo.DSS_Import_Category
    where HospitalID = isnull( @HospitalID, HospitalID ) 
    order by HospitalID
    if @Server = '(local)' or @Server = @Local_Server set @Server = null
    if @Server is null and @Database = @Local_Database set @Database = null
    
    return ( ( case when @Server is null then '' else '_Med2020_MEDDSS' + cast(@ExecutionID as varchar(10)) + '.' end ) + 
             ( case when @Database is null then '' else @Database + '.dbo.' end ) ) 
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE    function DSS_F_Upload_GetRecodeText  
( 
    @AppID       int 
    , @SubID         int
    , @TargetTable varchar(50) 
    , @TargetField varchar(50) 
    , @TableName   varchar(50) 
    , @TableAlias  varchar(50) 
    , @FieldName   varchar(50) 
    , @FieldAlias  varchar(50) 
    , @FieldID     int 
    , @DataType    int
    , @LookUpTable varchar(50)
    , @RecodeID     int         
)  
returns varchar(1000) 
begin 
    /*  
    ESCAPE SEQUENCE FOR EACH  
    VARIABLE ARE AS FOLLOWS: 
     
    \x = @ExecutionID 
    \g = @GroupID 
    \s = @SubGroupID 
    \t = @TableName  
    \a = @TableAlias 
    \c = @FieldName 
    \f = @FieldAlias 
    \u = @FieldID 
    \d = @DataType 
    \o = @Occurrence 
    \l = @LookUpTable 
    \i = @ICDType 
    */ 
 
    declare  
      @result      varchar(1000) 

    select @result =  
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
 
            RecodeText  
         
        , '\a', convert( varchar(10), isnull( @AppID    , 0 ) ) ) 
        , '\s', convert( varchar(10), isnull( @SubID , 0 ) ) ) 
        , '\x',                       isnull( @TargetTable  , '') ) 
        , '\y',                       isnull( @TargetField  , '') ) 
        , '\t',                       isnull( @TableName  , '') ) 
        , '\o',                       isnull( @TableAlias , '') ) 
        , '\c',                       isnull( @FieldName  , '') ) 
        , '\f',                       isnull( @FieldAlias , '') ) 
        , '\u', convert( varchar(10), isnull( @FieldID    , 0 ) ) ) 
        , '\d', convert( varchar(10), isnull( @DataType   , 0 ) ) ) 
        , '\l',                       isnull( @LookUpTable, '') ) 
    from DSS_Upload_Recode 
    where RecodeID = @RecodeID 
 
    return ( @result ) 
end 




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function DSS_F_Upload_GetSqlFrom 
(
    @AppID      int,          @TableAlias  varchar(50), 
    @FieldAlias varchar(50),  @FieldName   varchar(50),
    @FieldID     int 
)  
returns varchar(1000) 
begin 
    declare @result varchar(1000) 
 
    declare 
      @DataType    int 
    , @LookUpTable varchar(50) 
 
    select  top 1
      @DataType    = u.DataType 
    , @LookUpTable = u.LookUpTable 
    from U_UsersFields u  
    where u.UserID = 1  
    and u.FieldID = @FieldID 
    and u.AppID = @AppID 
 
    if @DataType in (21, 26, 31, 32, 50, 51, 56, 54, 60, 61, 62, 63) begin 
 
        set @result =  
            case  
            when (@DataType in (21, 50, 51, 56, 62, 63)) 
                then 'left join ' + @LookUpTable + ' ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
             
            when (@DataType in (26, 31, 32)) 
                then 'left join LU_CodeFile ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
             
            when (@DataType = 54) 
                then case @LookUpTable                 
                    when 'LLU_ProviderServices'  then 'left join LU_ProviderService '    + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                    when 'LLU_ProviderSpecialty' then 'left join LU_ProviderSpecialty '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                end                         
     
            when (@DataType = 60) 
                then 'left join ICD_DiagFile9   Diag_9  on ' + @TableAlias + '.' + @FieldName + ' = Diag_9.Code ' +  
                     'left join ICD_DiagFile9cm Diag_C  on ' + @TableAlias + '.' + @FieldName + ' = Diag_C.Code ' +
                     'left join ICD_DiagFile10  Diag_T  on ' + @TableAlias + '.' + @FieldName + ' = Diag_T.Code '  

            when (@DataType = 61)  
                then 'left join ICD_ProcFile9   Proc_9 on ' + @TableAlias + '.' + @FieldName + ' = Proc_9.Code ' + 
                     'left join ICD_ProcFile9cm Proc_C on ' + @TableAlias + '.' + @FieldName + ' = Proc_C.Code ' +
                     'left join ICD_ProcFile10  Proc_T on ' + @TableAlias + '.' + @FieldName + ' = Proc_T.Code '  
     
            else '' 
            end  
    end     
    else begin 
        set @result = '' 
    end 
 
    return (@result) 
end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE    function dbo.DSS_F_Upload_GetSqlSelect  
( 
  @AppID       int 
, @SubID       int 
, @TargetTable varchar(50) 
, @TargetField varchar(50) 
, @TableName   varchar(50) 
, @TableAlias  varchar(50) 
, @FieldName   varchar(50) 
, @FieldAlias  varchar(50) 
, @FieldID     int 
, @RecodeID    int
, @IsDisplay   int
)  
returns varchar(1000) 
begin 
 
    declare @result varchar(1000) 

    declare @LookUpTable varchar(50) 
    declare @DataType    int 
 
    select  
      @DataType    = DataType 
    , @LookUpTable = LookUpTable 
    from U_UsersFields 
    where UserID = 1
    and AppID = @AppID
    and FieldID = @FieldID
 
 
    set @result =  
        case  
        when (@RecodeID <> 0) 
            -- fetch the recode 
            then @TargetField + ' = ' + dbo.DSS_F_Upload_GetRecodeText(@AppID, @SubID, @TargetTable, @TargetField, @TableName, @TableAlias, @FieldName, @FieldAlias, @FieldID, @DataType, @LookUpTable, @RecodeID)
 
        when (@DataType in (0, 1, 2, 3, 6, 9, 10, 12, 17, 20, 52, 98)) 
            -- FieldAlias = TableAlias.FieldName 
            then @TargetField + ' = ' + @TableAlias + '.' + @FieldName  
 
        when (@DataType in (21, 26, 31, 32, 50, 51, 54, 56, 60, 61, 62, 63)) 
            -- FieldAlias = LookUpTable.CIHIValue 
            then  (case when @IsDisplay = 1 then @TargetField + ' = ' + @FieldAlias + '.DisplayCode'  
                        when @IsDisplay = 0 then @TargetField + ' = ' + @FieldAlias + '.UserDescription'  
                        else '' 
                   end)
        when (@DataType = 11)  
            -- FieldAlias = 'YYYY/MM/DD' 
            then @TargetField + ' = ' + @TableAlias + '.' + @FieldName   
 
        when (@DataType = 15) 
            -- FieldAlias = 'HH:MM:SS' 
            then @TargetField + ' = ' + @TableAlias + '.' + @FieldName 
 
        when (@DataType is not null) 
            -- FieldAlias = DataType: X 
            then @TargetField + ' = DataType: ''' + convert( varchar(10), @DataType ) + '''' 
 
            -- FieldAlias = (Default clause) 
        else @TargetField + ' = ''@@@@@@@@@@''' 
        end  
 
    return ( @result ) 
end 




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE function DSS_GET_DATE_int
( 
    @date datetime 
)
returns int as 
begin
    -- It is the same as WI_GET_DATE_int
    return
    (
        isnull( datepart( year,  @date ) * 10000 + 
                datepart( month, @date ) * 100   +
                datepart( day,   @date ), -1 ) 
    )
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function dbo.DSS_GET_TIME_int
( 
    @time datetime 
)
returns int as 
begin
    --It is the same as WI_GET_TIME_int
    return
    (
        isnull( datepart( hour,   @time ) * 100 + 
                datepart( minute, @time ), -1 )
    )
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetConditionComparisonType (@ConditionComparisonType int)
returns varchar(50)
AS
BEGIN
    return 
		CASE
			 WHEN @ConditionComparisonType = 1 THEN 'Field Condition'
             WHEN @ConditionComparisonType = 2 THEN 'Logical Condition'
		ELSE '' END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetFieldComparisonType (@FieldComparisonType int)
returns varchar(50)
AS
BEGIN
    return 
		CASE
			 WHEN @FieldComparisonType = 656 THEN 'Compare Field'
             WHEN @FieldComparisonType = 657 THEN 'Compare Values'
		ELSE '' END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetFieldConditionOperator (@FieldOperator varchar(50))
returns varchar(50)
AS
BEGIN
    return 
		CASE
			 WHEN @FieldOperator = '580' THEN 'Any Value'
             WHEN @FieldOperator = '581' THEN 'Blank (no value)'
			 WHEN @FieldOperator = '582' THEN 'Equal To'
             WHEN @FieldOperator = '584' THEN 'Not Equal'
			 WHEN @FieldOperator = '594' THEN 'Like'
			 WHEN @FieldOperator = '595' THEN 'Not Like'
			 WHEN @FieldOperator = '592' THEN 'Starts with'
             When @FieldOperator = '593' THEN 'Not Starts With'
             WHEN @FieldOperator = '586' THEN 'Less'
			 WHEN @FieldOperator = '587' THEN 'Less or Equal'
             WHEN @FieldOperator = '588' THEN 'Greater'
             WHEN @FieldOperator = '589' THEN 'Greater or Equal'
             WHEN @FieldOperator = '590' THEN 'Between'
             WHEN @FieldOperator = '591' THEN 'Not Between'
		ELSE '' END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetFieldOccurrence (@FieldOccurrence varchar(50))
returns varchar(50)
AS
BEGIN
    return 
		CASE
			 WHEN @FieldOccurrence = '620' THEN 'First Occurence'
             WHEN @FieldOccurrence = '621' THEN 'Other Occurrences (>1)'
			 WHEN @FieldOccurrence = '622' THEN 'All Occurrences'
             WHEN @FieldOccurrence = '623' THEN 'Specific Occurrence'
		ELSE '' END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetGrouperHeader (@FieldKey varchar(50))
returns varchar(50)
AS
BEGIN
    return 
		CASE
		/*BuildGrouperListCMG*/
			WHEN @FieldKey = 'FKCMG' THEN 'Grouper: CMG'
			WHEN @FieldKey = 'FKMCMG' THEN 'Grouper: Medical CMG'
			WHEN @FieldKey = 'FKMCC' THEN 'Grouper: MCC'
			WHEN @FieldKey = 'FKRIW' THEN 'Grouper: R.I.W.'
			WHEN @FieldKey = 'FKIRIW' THEN 'Grouper: Inpatient Typical RIW'
			WHEN @FieldKey = 'FKCoMorb' THEN 'Grouper: Comorbidity'
			WHEN @FieldKey = 'FKRIL' THEN 'Grouper: R.I.L.'
			WHEN @FieldKey = 'FKRIWA' THEN 'Grouper: RIW Atypical'
			WHEN @FieldKey = 'FKMOH' THEN 'Grouper: M.O.H.'
			WHEN @FieldKey = 'FKRIWD' THEN 'Grouper: R.I.W. $'
			WHEN @FieldKey = 'FKMOHD' THEN 'Grouper: M.O.H. $'
			WHEN @FieldKey = 'FKELOS' THEN 'Grouper: ELOS'
			WHEN @FieldKey = 'FKTrim' THEN 'Grouper: Trim'
			WHEN @FieldKey = 'FKCMGAgeCat' THEN 'Grouper: CMG Age Cat'
			WHEN @FieldKey = 'FKDxPart' THEN 'Grouper: Dx/Interv Part'
			WHEN @FieldKey = 'FKFlagInterv' THEN 'Grouper: Flag Interv'
			WHEN @FieldKey = 'FKIntervEvent' THEN 'Grouper: Interv. Event'
			WHEN @FieldKey = 'FKOOHInterv' THEN 'Grouper: OOH Interv'
			WHEN @FieldKey = 'FKFICard' THEN 'Grouper: FI Cardioversion'
			WHEN @FieldKey = 'FKFICell' THEN 'Grouper: FI CellSaver'
			WHEN @FieldKey = 'FKFIChemo' THEN 'Grouper: FI Chemotherapy'
			WHEN @FieldKey = 'FKFIDial' THEN 'Grouper: FI Dialysis'
			WHEN @FieldKey = 'FKFIHeart' THEN 'Grouper: FI Heart Resuscitation'
			WHEN @FieldKey = 'FKFIVentG96' THEN 'Grouper: FI Invasive Vent > 96'
			WHEN @FieldKey = 'FKFIVentL96' THEN 'Grouper: FI Invasive Vent < 96'
			WHEN @FieldKey = 'FKFIFeed' THEN 'Grouper: FI Feeding Tube'
			WHEN @FieldKey = 'FKFIPara' THEN 'Grouper: FI Paracentesis'
			WHEN @FieldKey = 'FKFINut' THEN 'Grouper: FI Parenteral Nutrition'
			WHEN @FieldKey = 'FKFIPleu' THEN 'Grouper: FI Pleurocentesis'
			WHEN @FieldKey = 'FKFIRadio' THEN 'Grouper: FI Radiotherapy'
			WHEN @FieldKey = 'FKFITrach' THEN 'Grouper: FI Tracheostomy'
			WHEN @FieldKey = 'FKFIVasc' THEN 'Grouper: FI Vascular Access Device'
			WHEN @FieldKey = 'FKFIEndo' THEN 'Grouper: FI Endoscopy'
			WHEN @FieldKey = 'FKFIBio' THEN 'Grouper: FI Biopsy'
			WHEN @FieldKey = 'FKFIECMO' THEN 'Grouper: FI ECMO'
			WHEN @FieldKey = 'FKRITotal' THEN 'Grouper: Inpatient RI Total Factor'
			WHEN @FieldKey = 'FKComorbTotal' THEN 'Grouper: Comorbidity Total Factor'
			WHEN @FieldKey = 'FKIntervEp' THEN 'Grouper: Intervention Episode'
			WHEN @FieldKey = 'FKAttS' THEN 'Grouper: Intervention Attribute Status'
			WHEN @FieldKey = 'FKAttL' THEN 'Grouper: Intervention Attribute Location'
			WHEN @FieldKey = 'FKAttE' THEN 'Grouper: Intervention Attribute Extent'
			WHEN @FieldKey = 'FKBatDate' THEN 'Grouper: Date Batch Grouped'
			WHEN @FieldKey = 'FKHIG' THEN 'Grouper: HIG Code'
			WHEN @FieldKey = 'FKHIGWght' THEN 'Grouper: HIG Weight'
			WHEN @FieldKey = 'FKHIGELOS' THEN 'Grouper: HIG ELOS'
			WHEN @FieldKey = 'FKHIGAgeCat' THEN 'Grouper: HIG Age Category'
			WHEN @FieldKey = 'FKHIGAtyp' THEN 'Grouper: HIG Atypical Status'
			WHEN @FieldKey = 'FKHIGFITotal' THEN 'Grouper: HIG FI Total Count'
			WHEN @FieldKey = 'FKHIGHome' THEN 'Grouper: HIG Homecare Flag'
			WHEN @FieldKey = 'FKHIGMat' THEN 'Grouper: HIG Maternal Age Flag'
			WHEN @FieldKey = 'FKHIGPall' THEN 'Grouper: HIG Palliative Care Flag'
			WHEN @FieldKey = 'FKHIGOOH' THEN 'Grouper: HIG OOH Flag'
			WHEN @FieldKey = 'FKHIGSCU' THEN 'Grouper: HIG SCU Flag'
			WHEN @FieldKey = 'FKHIGLong' THEN 'Grouper: HIG Long Stay Trim Days'
			WHEN @FieldKey = 'FKHIGShort' THEN 'Grouper: HIG Short Stay Trim Days'
			/*BuildGrouperListCACS*/
			WHEN @FieldKey = 'FKCACSMAC' THEN 'Grouper: MAC'
			WHEN @FieldKey = 'FKCACSCell' THEN 'Grouper: CACS Cell'
			WHEN @FieldKey = 'FKCACSACW' THEN 'Grouper: ACW'
			WHEN @FieldKey = 'FKCACSAge' THEN 'Grouper: Age Category'
			WHEN @FieldKey = 'FKCACSAnaest' THEN 'Grouper: Anaesthetic'
			WHEN @FieldKey = 'FKCACSTech' THEN 'Grouper: Inv Tech Count'
			WHEN @FieldKey = 'FKCACSDate' THEN 'Grouper: Date Batch Grouped'
			WHEN @FieldKey = 'FKCACSON' THEN 'Grouper: CACS ON'
			/*BuildGrouperListDPG*/
			WHEN @FieldKey = 'FKDPG' THEN 'Grouper: DPG'
			WHEN @FieldKey = 'FKDPGRIW' THEN 'Grouper: R.I.W.'	
		ELSE '' END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetLogicalConditionOperator (@LogicalConditionOperator int)
returns varchar(50)
AS
BEGIN
    return 
		CASE
			 WHEN @LogicalConditionOperator = 651 THEN 'And'
			 WHEN @LogicalConditionOperator = 652 THEN 'Or'
		ELSE '' END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetModuleDescription (@AppID int)
returns varchar(50)
AS
BEGIN
	return
		CASE
			WHEN @AppID = 200 THEN 'AbsCare [CC.R.] ~ICD 10' 
			WHEN @AppID = 201 THEN 'AbsCare [Inpatient] ~ICD 10'
			WHEN @AppID = 202 THEN 'AbsCare [S.D.S] ~ICD 10'
			WHEN @AppID = 401 THEN 'AmCare ~ICD 10'
			ELSE '' 
		END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetRuleSeverity (@RuleSeverity int)
returns varchar(50)
AS
BEGIN
	return
	CASE
		WHEN @RuleSeverity = 671 THEN 'Critical'
			WHEN @RuleSeverity = 672 THEN 'Error'
			WHEN @RuleSeverity = 673 THEN 'Warning'
			WHEN @RuleSeverity = 670 THEN 'Info'
			ELSE 'Warning'
		END
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function EMT_GetSameOccurrenceMultiFormDescription (@MultipleIndex int, @AppID int)
returns varchar(50)
AS
BEGIN

DECLARE @Header_User varchar(50) 

select @Header_User = u.Header_User
			from U_Fields f with (nolock) 
			inner join U_UsersFields u with (nolock) on f.FieldID = u.FieldID 
			where f.IsGroup = 3 and u.UserID = 1 and u.FieldVisible = 1 and u.AppID = @AppID and u.MultipleIndex = @MultipleIndex 			
			return @Header_User
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE function Get4DigitTime( @Time datetime) returns varchar(4)
as
begin
    return (isnull( left( replace( convert( varchar(8), @Time, 8), ':', ''), 4), ''))
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Function [dbo].[GetCorrectionForMerge](@TableName varchar(100))
    returns nvarchar(4000)
as
begin
    declare @insert_sql nvarchar(2000)
    declare @select_sql nvarchar(2000)
    declare @column_name varchar(100)

    declare cur_table cursor for
        select column_name from information_schema.columns where table_name = @TableName and column_name <> 'AbstractID'
    
    set @insert_sql = ''    
    set @select_sql = ''    
    open cur_table
    fetch next from cur_table into @column_name
    while(@@fetch_status = 0)
    begin
        set @insert_sql = @insert_sql + ',[' + @column_name + ']'
        if @column_name = 'IsDeleted'
            set @select_sql = @select_sql + ',3'
        else if @column_name = 'IsCorrected'
            set @select_sql = @select_sql + ',2'
        else if @column_name = 'LinkID'
            set @select_sql = @select_sql + ',0'
        else
            set @select_sql = @select_sql + ',[' + @column_name + ']'
    
        fetch next from cur_table into @column_name
    end
    close cur_table
    deallocate cur_table
    return('insert into ' + @TableName + '(AbstractID' + @insert_sql + ') select @NewID' + @select_sql + ' from ' + @TableName + ' where AbstractID = @OldID ')
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[GetFixedChartNumber](
    @ChartNumber varchar(10)
) returns varchar(10) as 
begin
/*
<recode_name>
CHARTNUMBER : pad zeros to fix chartnumber  PR - 06021
</recode_name>
*/
	declare @Front 	varchar(20)
	declare @Rear	varchar(20)
	declare @i		int
	declare @j 		int

	set @ChartNumber = ltrim(rtrim(@ChartNumber))
	if len(@ChartNumber) < 10
	begin
	    set @i = patindex('%[^0]%', @ChartNumber)
        if @i > 0 set @ChartNumber = substring(@ChartNumber, @i, 20)

	    set @i = patindex('%[ 0123456789]%', @ChartNumber)
	    if @i>1
	    begin
		    set @Front = left(@ChartNumber, @i-1)
		    set @Rear = right(@ChartNumber, len(@ChartNumber) - @i + 1)
	    end
	    else
	    begin
		    set @Front = ''
		    set @Rear = @ChartNumber
	    end
	    set @j = len(@Rear)
	    set @i = patindex('%[^ 0]%',@Rear)
	    if @i = 0 and @j > 0
		    set @Rear = '0'
	    else if patindex('%[^ 0123456789]%', @Rear) = 0
		    set @Rear = right(@Rear, @j - @i + 1)
		
		if @Front = '' and isnumeric(@Rear) = 0
		begin
		    set @Front = @Rear
		    set @Rear = ''
		end
	    set @ChartNumber =  isnull(@Front, '') + Replicate('0', 10 - len(isnull(@Front, '') + isnull(@Rear, ''))) + isnull(@Rear, '')
    end    
	return(upper(@ChartNumber))
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function dbo.GetGrouperAuditValue(
    @AbstractID int, @GroupYear int, @FieldChanged varchar(100)
) returns varchar(100)
as
begin    
    return( (select top 1 NewValue from dbo.AB_GrouperAudit where AbstractID = @AbstractID and GroupingYear = @GroupYear and FieldChanged = @FieldChanged order by DateGrouped desc) )
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function GetPostalDescription (@SearchPostal varchar(20), @SearchDate datetime)
	returns varchar(255)
as
begin
    -- Retrieve the user description from LU_PostalCode for a given postal code value.
    -- Mainly used in views for Crystal reports.
    
	declare @UserDescription varchar(255)

    select top 1 @UserDescription = UserDescription
    from LU_PostalCode
    where @SearchDate between ValidFrom and ValidTo
    and CIHIValue in 
    (
        left( @SearchPostal, 6 ), 
    	left( @SearchPostal, 5 ), 
    	left( @SearchPostal, 4 ), 
    	left( @SearchPostal, 3 ), 
        left( @SearchPostal, 2 ), 
        left( @SearchPostal, 1 ) 
    )
    order by len(CIHIValue) desc

    set @UserDescription = isnull(@UserDescription, '')

	return ( @UserDescription )
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[GetQuestionValues](
    @len int, @Q1 varchar(10), @Q2 varchar(10), @Q3 varchar(10), @Q4 varchar(10)
) 
returns varchar(4)
as
begin
    declare @value varchar(40)
    set @Value = left(isnull(@Q1, ''), 1) + 
                (case when @len > 1 then left(isnull(@Q2, ''), 1) else '' end) +
                (case when @len > 2 then left(isnull(@Q3, ''), 1) else '' end) +
                (case when @len > 3 then left(isnull(@Q4, ''), 1) else '' end)
    if isnumeric(@value) = 1 set @value = right('0000' + @value, @len)
    return (@value)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function dbo.GetRehabActiveLos(
    @AbstractID int, @AssessmentType varchar(10),
    @AdmitDate datetime, @DischargeDate datetime, @Followupdate datetime, @CurrentDate datetime)
    returns int
as
--This function is for ticket ED - 01556
begin
    declare @Los int
    declare @enddate datetime
    
    if @AssessmentType = '1' and @DischargeDate is null
    begin
        set @DischargeDate = (select top 1 Dischargedate from dbo.RH_ClientInfo inner join LU_CodeFile on AssessmentType = code 
                                where LinkID = @AbstractID and CIHIValue in ('2', '4') order by CIHIValue)
        set @enddate = isnull(@DischargeDate, @CurrentDate)
    end
    else
        set @enddate = (case when @AssessmentType in ('1', '2', '4') 
                                then (case when @DischargeDate > @CurrentDate then @CurrentDate
                                           else @DischargeDate end)
                             when @AssessmentType = '3' 
                                then (case when @followupDate is null then @CurrentDate
                                           when @followupDate > @CurrentDate then @CurrentDate
                                           else @followupDate end)
                             else @CurrentDate 
                        end)

    set @Los = datediff(d, @admitdate, @enddate)
    if isnull(@Los, 0) = 0 set @LOS = 1
    return(@LOS)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[Get_NACRSCareTypeByMisCodes] 
( 
    @MISCode varchar(20), @AmcareGroup varchar(20)
)  
returns varchar(10)  
as  
begin 
   declare  @UseCareType as varchar(10)
   set @UseCareType = (case when @AmcareGroup = 'ED' then 'B-E' --Emergency
                            when @AmcareGroup = 'DS' then 'B-D' --Daysurgery
                            when @AmcareGroup = 'CL' then 'B-C' --Clinic
                            when isnull(@AmcareGroup, '') <> '' then 'B' --
                            else (case when @MISCode like '7_310%'  then 'B-E' 
                                       when substring(@MISCode, 3, 5) in ('34025', '34020', '34055') or  
                                            substring(@MISCode, 3, 3) in ('260', '265', '262', '310', '360', '362', '365', '369') then 'B-D' --Daysurgery
                                       when isnull(@MISCode, '') = '' then 'B'    
			                           else 'B-C'
			                      end)
			           end)    
	return @UseCareType		                
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Function IsDateCross(
    @DateFrom1 datetime,
    @DateTo1   datetime,
    @DateFrom2 datetime,
    @DateTo2   datetime)
    returns int
as
begin
    return(case when @DateFrom1 between @DateFrom2 and @DateTo2 
                    or @DateTo1 between @DateFrom2 and @DateTo2 
                    or @DateFrom2 between @DateFrom1 and @DateTo1
                    or @DateTo2 between @DateFrom1 and @DateTo1
                  then 1 else 0 end)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_ADL01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    @J5c   varchar(50), @P1ao varchar(50),
    @G1aA_R   varchar(50),    @G1bA_R  varchar(50), @G1hA_R varchar(50),
    @G1iA_R   varchar(50),
    @Prev_G1aA_R   varchar(50),    @Prev_G1bA_R  varchar(50), @Prev_G1hA_R varchar(50),
    @Prev_G1iA_R   varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1aA int, @G1bA int, @G1hA int, @G1iA int
    declare @Prev_G1aA  int, @Prev_G1bA  int, @Prev_G1hA int, @Prev_G1iA int
    
    declare @T_Exclude int
    if isnumeric(@G1aA_R) = 1 set @G1aA = cast(@G1aA_R as int)
    if isnumeric(@G1bA_R) = 1 set @G1bA = cast(@G1bA_R as int)
    if isnumeric(@G1hA_R) = 1 set @G1hA = cast(@G1hA_R as int)
    if isnumeric(@G1iA_R) = 1 set @G1iA = cast(@G1iA_R as int)
    if isnumeric(@Prev_G1aA_R) = 1 set @Prev_G1aA = cast(@Prev_G1aA_R as int)
    if isnumeric(@Prev_G1bA_R) = 1 set @Prev_G1bA = cast(@Prev_G1bA_R as int)
    if isnumeric(@Prev_G1hA_R) = 1 set @Prev_G1hA = cast(@Prev_G1hA_R as int)
    if isnumeric(@Prev_G1iA_R) = 1 set @Prev_G1iA = cast(@Prev_G1iA_R as int)

    if @G1aA = 8 set @G1aA = 4
    if @G1bA = 8 set @G1bA = 4
    if @G1hA = 8 set @G1hA = 4
    if @G1iA = 8 set @G1iA = 4
    if @Prev_G1aA = 8 set @Prev_G1aA = 4
    if @Prev_G1bA = 8 set @Prev_G1bA = 4
    if @Prev_G1hA = 8 set @Prev_G1hA = 4
    if @Prev_G1iA = 8 set @Prev_G1iA = 4
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_G1aA is null or @Prev_G1bA is null or @Prev_G1hA is null or @Prev_G1iA is null then 1
                           when @G1aA is null or @G1bA is null or @G1hA is null or @G1iA is null then 1
                           when @Prev_G1aA = 4 and @Prev_G1bA = 4 and @Prev_G1hA = 4 and @Prev_G1iA = 4 then 1
                      end)
                      
    declare @T_Worsen_One int                                                 
    set @T_Worsen_One = 0
    IF @G1aA - @Prev_G1aA > 0 set @T_Worsen_One = @T_Worsen_One + 1 
    IF @G1bA - @Prev_G1bA > 0 set @T_Worsen_One = @T_Worsen_One + 1 
    IF @G1hA - @Prev_G1hA > 0 set @T_Worsen_One = @T_Worsen_One + 1 
    IF @G1iA - @Prev_G1iA > 0 set @T_Worsen_One = @T_Worsen_One + 1 
                           
    declare @T_Worsen_Two int
    set @T_Worsen_Two = 0
    IF @G1aA - @Prev_G1aA > 1 set @T_Worsen_Two = @T_Worsen_Two + 1
    IF @G1bA - @Prev_G1bA > 1 set @T_Worsen_Two = @T_Worsen_Two + 1
    IF @G1hA - @Prev_G1hA > 1 set @T_Worsen_Two = @T_Worsen_Two + 1 
    IF @G1iA - @Prev_G1iA > 1 set @T_Worsen_Two = @T_Worsen_Two + 1 

    declare @QI_ADL01_D int
    declare @QI_ADL01_N int
    set @QI_ADL01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_ADL01_D
    end
    else
    begin
        IF @QI_ADL01_D = 1 
        begin
            IF @T_Worsen_One >= 2 OR @T_Worsen_Two >= 1 
                set @QI_ADL01_N = 1
            ELSE
                set @QI_ADL01_N = 0
        end
        set @return_value = @QI_ADL01_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function dbo.MDS20_QI_ADL05(
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    @J5c   varchar(50), @P1ao varchar(50),
    @G1bA_R   varchar(50),    @G1dA_R varchar(50),
    @G1eA_R   varchar(50),
    @Prev_G1bA_R  varchar(50), @Prev_G1dA_R varchar(50),
    @Prev_G1eA_R   varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1bA int, @G1dA int, @G1eA int
    declare @Prev_G1bA  int, @Prev_G1dA int, @Prev_G1eA int
    
    declare @T_Exclude int
    if isnumeric(@G1bA_R) = 1 set @G1bA = cast(@G1bA_R as int)
    if isnumeric(@G1dA_R) = 1 set @G1dA = cast(@G1dA_R as int)
    if isnumeric(@G1eA_R) = 1 set @G1eA = cast(@G1eA_R as int)
    if isnumeric(@Prev_G1bA_R) = 1 set @Prev_G1bA = cast(@Prev_G1bA_R as int)
    if isnumeric(@Prev_G1dA_R) = 1 set @Prev_G1dA = cast(@Prev_G1dA_R as int)
    if isnumeric(@Prev_G1eA_R) = 1 set @Prev_G1eA = cast(@Prev_G1eA_R as int)
    
    if @G1bA = 8 set @G1bA = 4
    if @G1dA = 8 set @G1dA = 4
    if @G1eA = 8 set @G1eA = 4
    if @Prev_G1bA = 8 set @Prev_G1bA = 4
    if @Prev_G1dA = 8 set @Prev_G1dA = 4
    if @Prev_G1eA = 8 set @Prev_G1eA = 4
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @G1bA is null or @G1dA is null or @G1eA is null then 1
                           when (@Prev_G1bA is null or @Prev_G1dA is null or @Prev_G1eA is null) and 
                                (@G1bA + @G1dA + @G1eA) > 0
                            then 1
                      end)
                      
    declare @T_Mid_Loss_ADL int                                                 
    set @T_Mid_Loss_ADL = @G1bA + @G1dA + @G1eA
    
    declare @T_Prev_Mid_Loss_ADL int
    set @T_Prev_Mid_Loss_ADL = @Prev_G1bA + @Prev_G1dA + @Prev_G1eA

    declare @QI_ADL05_D int
    declare @QI_ADL05_N int
    set @QI_ADL05_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_ADL05_D
    end
    else
    begin
        IF @QI_ADL05_D = 1 
        begin
            IF (@T_Mid_Loss_ADL - @T_Prev_Mid_Loss_ADL) < 0 
                set @QI_ADL05_N = 1
            ELSE IF @T_Mid_Loss_ADL = 0 AND @T_Prev_Mid_Loss_ADL = 0 
                set @QI_ADL05_N = 1
            ELSE
                set @QI_ADL05_N = 0
        end
        set @return_value = @QI_ADL05_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function dbo.MDS20_QI_ADL06(
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    @J5c   varchar(50), @P1ao varchar(50),
    @G1gA_R   varchar(50),    @G1jA_R varchar(50),
    @Prev_G1gA_R  varchar(50), @Prev_G1jA_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1gA int, @G1jA int
    declare @Prev_G1gA  int, @Prev_G1jA int
    
    declare @T_Exclude int
    if isnumeric(@G1gA_R) = 1 set @G1gA = cast(@G1gA_R as int)
    if isnumeric(@G1jA_R) = 1 set @G1jA = cast(@G1jA_R as int)
    if isnumeric(@Prev_G1gA_R) = 1 set @Prev_G1gA = cast(@Prev_G1gA_R as int)
    if isnumeric(@Prev_G1jA_R) = 1 set @Prev_G1jA = cast(@Prev_G1jA_R as int)
    
    if @G1gA = 8 set @G1gA = 4
    if @G1jA = 8 set @G1jA = 4
    if @Prev_G1gA = 8 set @Prev_G1gA = 4
    if @Prev_G1jA = 8 set @Prev_G1jA = 4
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @G1gA is null or @G1jA is null then 1
                           when (@Prev_G1gA is null or @Prev_G1jA is null) and 
                                (@G1gA + @G1jA) > 0
                            then 1
                      end)
                      
    declare @T_Early_Loss_ADL int                                                 
    set @T_Early_Loss_ADL = @G1gA + @G1jA 
    
    declare @T_Prev_Early_Loss_ADL int
    set @T_Prev_Early_Loss_ADL = @Prev_G1gA + @Prev_G1jA

    declare @QI_ADL06_D int
    declare @QI_ADL06_N int
    set @QI_ADL06_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_ADL06_D
    end
    else
    begin
        IF @QI_ADL06_D = 1 
        begin
            IF (@T_Early_Loss_ADL - @T_Prev_Early_Loss_ADL) < 0
                set @QI_ADL06_N = 1
            ELSE IF @T_Early_Loss_ADL = 0 AND @T_Prev_Early_Loss_ADL = 0 
                set @QI_ADL06_N = 1
            ELSE
                set @QI_ADL06_N = 0
        end
        set @return_value = @QI_ADL06_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function dbo.MDS20_QI_ADL1A(
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    @J5c   varchar(50), @P1ao varchar(50),
    @G1aA_R   varchar(50),    @G1bA_R varchar(50), @G1hA_R   varchar(50),    @G1iA_R varchar(50),
    @Prev_G1aA_R  varchar(50), @Prev_G1bA_R varchar(50), @Prev_G1hA_R  varchar(50), @Prev_G1iA_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1aA int, @G1bA int, @G1hA int, @G1iA int
    declare @Prev_G1aA  int, @Prev_G1bA int, @Prev_G1hA int, @Prev_G1iA int
    
    declare @T_Exclude int
    if isnumeric(@G1aA_R) = 1 set @G1aA = cast(@G1aA_R as int)
    if isnumeric(@G1bA_R) = 1 set @G1bA = cast(@G1bA_R as int)
    if isnumeric(@G1hA_R) = 1 set @G1hA = cast(@G1hA_R as int)
    if isnumeric(@G1iA_R) = 1 set @G1iA = cast(@G1iA_R as int)
    if isnumeric(@Prev_G1aA_R) = 1 set @Prev_G1aA = cast(@Prev_G1aA_R as int)
    if isnumeric(@Prev_G1bA_R) = 1 set @Prev_G1bA = cast(@Prev_G1bA_R as int)
    if isnumeric(@Prev_G1hA_R) = 1 set @Prev_G1hA = cast(@Prev_G1hA_R as int)
    if isnumeric(@Prev_G1iA_R) = 1 set @Prev_G1iA = cast(@Prev_G1iA_R as int)
    
    if @G1aA = 8 set @G1aA = 4
    if @G1bA = 8 set @G1bA = 4
    if @G1hA = 8 set @G1hA = 4
    if @G1iA = 8 set @G1iA = 4
    if @Prev_G1aA = 8 set @Prev_G1aA = 4
    if @Prev_G1bA = 8 set @Prev_G1bA = 4
    if @Prev_G1hA = 8 set @Prev_G1hA = 4
    if @Prev_G1iA = 8 set @Prev_G1iA = 4
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @G1aA is null or @G1bA is null or @G1hA is null or @G1iA is null then 1
                           when @Prev_G1aA is null or @Prev_G1bA is null or @Prev_G1hA is null or @Prev_G1iA is null then 1
                           when @Prev_G1aA = 0 and @Prev_G1bA = 0 and @Prev_G1hA = 0 and @Prev_G1iA = 0 then 1
                      end)
                      
    declare @T_Improve_One int                                                 
    set @T_Improve_One = 0
    IF @Prev_G1aA - @G1aA > 0 set @T_Improve_One = @T_Improve_One + 1
    IF @Prev_G1bA - @G1bA > 0 set @T_Improve_One = @T_Improve_One + 1 
    IF @Prev_G1hA - @G1hA > 0 set @T_Improve_One = @T_Improve_One + 1
    IF @Prev_G1iA - @G1iA > 0 set @T_Improve_One = @T_Improve_One + 1

    declare @T_Improve_Two int
    set @T_Improve_Two = 0
    IF @Prev_G1aA - @G1aA > 1 set @T_Improve_Two = @T_Improve_Two + 1 
    IF @Prev_G1bA - @G1bA > 1 set @T_Improve_Two = @T_Improve_Two + 1 
    IF @Prev_G1hA - @G1hA > 1 set @T_Improve_Two = @T_Improve_Two + 1 
    IF @Prev_G1iA - @G1iA > 1 set @T_Improve_Two = @T_Improve_Two + 1


    declare @QI_ADL1A_D int
    declare @QI_ADL1A_N int
    set @QI_ADL1A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_ADL1A_D
    end
    else
    begin
        IF @QI_ADL1A_D = 1 
        begin
            IF @T_Improve_One >= 2 OR @T_Improve_Two >= 1
                set @QI_ADL1A_N = 1
            ELSE
                set @QI_ADL1A_N = 0
        end
        set @return_value = @QI_ADL1A_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_ADL5A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    @J5c   varchar(50), @P1ao varchar(50),
    @G1bA_R   varchar(50),    @G1dA_R varchar(50),
    @G1eA_R   varchar(50),
    @Prev_G1bA_R  varchar(50), @Prev_G1dA_R varchar(50),
    @Prev_G1eA_R   varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1bA int, @G1dA int, @G1eA int
    declare @Prev_G1bA  int, @Prev_G1dA int, @Prev_G1eA int
    
    declare @T_Exclude int
    if isnumeric(@G1bA_R) = 1 set @G1bA = cast(@G1bA_R as int)
    if isnumeric(@G1dA_R) = 1 set @G1dA = cast(@G1dA_R as int)
    if isnumeric(@G1eA_R) = 1 set @G1eA = cast(@G1eA_R as int)
    if isnumeric(@Prev_G1bA_R) = 1 set @Prev_G1bA = cast(@Prev_G1bA_R as int)
    if isnumeric(@Prev_G1dA_R) = 1 set @Prev_G1dA = cast(@Prev_G1dA_R as int)
    if isnumeric(@Prev_G1eA_R) = 1 set @Prev_G1eA = cast(@Prev_G1eA_R as int)
    
    if @G1bA = 8 set @G1bA = 4
    if @G1dA = 8 set @G1dA = 4
    if @G1eA = 8 set @G1eA = 4
    if @Prev_G1bA = 8 set @Prev_G1bA = 4
    if @Prev_G1dA = 8 set @Prev_G1dA = 4
    if @Prev_G1eA = 8 set @Prev_G1eA = 4
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @G1bA is null or @G1dA is null or @G1eA is null then 1
                           when (@Prev_G1bA is null or @Prev_G1dA is null or @Prev_G1eA is null) and 
                                (@G1bA + @G1dA + @G1eA) > 0
                            then 1
                      end)
                      
    declare @T_Mid_Loss_ADL int                                                 
    set @T_Mid_Loss_ADL = @G1bA + @G1dA + @G1eA
    
    declare @T_Prev_Mid_Loss_ADL int
    set @T_Prev_Mid_Loss_ADL = @Prev_G1bA + @Prev_G1dA + @Prev_G1eA

    declare @QI_ADL5A_D int
    declare @QI_ADL5A_N int
    set @QI_ADL5A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_ADL5A_D
    end
    else
    begin
        IF @QI_ADL5A_D = 1 
        begin
            IF (@T_Mid_Loss_ADL - @T_Prev_Mid_Loss_ADL) > 0 
                set @QI_ADL5A_N = 1
            ELSE IF @T_Mid_Loss_ADL = 12 AND @T_Prev_Mid_Loss_ADL = 12
                set @QI_ADL5A_N = 1
            ELSE
                set @QI_ADL5A_N = 0
        end
        set @return_value = @QI_ADL5A_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_ADL6A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    @J5c   varchar(50), @P1ao varchar(50),
    @G1gA_R   varchar(50),    @G1jA_R varchar(50),
    @Prev_G1gA_R  varchar(50), @Prev_G1jA_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1gA int, @G1jA int
    declare @Prev_G1gA  int, @Prev_G1jA int
    
    declare @T_Exclude int
    if isnumeric(@G1gA_R) = 1 set @G1gA = cast(@G1gA_R as int)
    if isnumeric(@G1jA_R) = 1 set @G1jA = cast(@G1jA_R as int)
    if isnumeric(@Prev_G1gA_R) = 1 set @Prev_G1gA = cast(@Prev_G1gA_R as int)
    if isnumeric(@Prev_G1jA_R) = 1 set @Prev_G1jA = cast(@Prev_G1jA_R as int)
    
    if @G1gA = 8 set @G1gA = 4
    if @G1jA = 8 set @G1jA = 4
    if @Prev_G1gA = 8 set @Prev_G1gA = 4
    if @Prev_G1jA = 8 set @Prev_G1jA = 4
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @G1gA is null or @G1jA is null then 1
                           when (@Prev_G1gA is null or @Prev_G1jA is null) and 
                                (@G1gA + @G1jA) > 0
                            then 1
                      end)
                      
    declare @T_Early_Loss_ADL int                                                 
    set @T_Early_Loss_ADL = @G1gA + @G1jA 
    
    declare @T_Prev_Early_Loss_ADL int
    set @T_Prev_Early_Loss_ADL = @Prev_G1gA + @Prev_G1jA

    declare @QI_ADL6A_D int
    declare @QI_ADL6A_N int
    set @QI_ADL6A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_ADL6A_D
    end
    else
    begin
        IF @QI_ADL6A_D = 1 
        begin
            IF (@T_Early_Loss_ADL - @T_Prev_Early_Loss_ADL) > 0
                set @QI_ADL6A_N = 1
            ELSE IF @T_Early_Loss_ADL = 8 AND @T_Prev_Early_Loss_ADL = 8 
                set @QI_ADL6A_N = 1
            ELSE
                set @QI_ADL6A_N = 0
        end
        set @return_value = @QI_ADL6A_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_ADLD7](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    @J5c   varchar(50), @P1ao varchar(50),
    @ADL_Long_Form_cc int,  @Prev_ADL_Long_Form_cc int
) returns int
begin
    declare @return_value int
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @ADL_Long_Form_cc is null then 1
                           when @Prev_ADL_Long_Form_cc is null then 1
                      end)
                      

    declare @QI_ADLD7_D int
    declare @QI_ADLD7_N int
    set @QI_ADLD7_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_ADLD7_D
    end
    else
    begin
        IF @QI_ADLD7_D = 1 
        begin
            IF @ADL_Long_Form_cc - @Prev_ADL_Long_Form_cc >= 1
                set @QI_ADLD7_N = 1
            ELSE
                set @QI_ADLD7_N = 0
        end
        set @return_value = @QI_ADLD7_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_BEHD4](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    
    @E4aA_R   varchar(50), @E4bA_R varchar(50),
    @E4cA_R   varchar(50), @E4dA_R varchar(50),
    @Prev_E4aA_R   varchar(50), @Prev_E4bA_R varchar(50),
    @Prev_E4cA_R   varchar(50), @Prev_E4dA_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @E4aA int, @E4bA int, @E4cA int, @E4dA int
    declare @Prev_E4aA int, @Prev_E4bA int, @Prev_E4cA int, @Prev_E4dA int

    if isnumeric(@E4aA_R) = 1 set @E4aA = cast(@E4aA_R as int)
    if isnumeric(@E4bA_R) = 1 set @E4bA = cast(@E4bA_R as int)
    if isnumeric(@E4cA_R) = 1 set @E4cA = cast(@E4cA_R as int)
    if isnumeric(@E4dA_R) = 1 set @E4dA = cast(@E4dA_R as int)
    if isnumeric(@Prev_E4aA_R) = 1 set @Prev_E4aA = cast(@Prev_E4aA_R as int)
    if isnumeric(@Prev_E4bA_R) = 1 set @Prev_E4bA = cast(@Prev_E4bA_R as int)
    if isnumeric(@Prev_E4cA_R) = 1 set @Prev_E4cA = cast(@Prev_E4cA_R as int)
    if isnumeric(@Prev_E4dA_R) = 1 set @Prev_E4dA = cast(@Prev_E4dA_R as int)
    
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when @E4aA is null or @E4bA is null or @E4cA is null or @E4dA is null then 1
                      end)
                      
    declare @T_COUNT int
    set @T_COUNT = 0
    IF @E4aA > 0 set @T_COUNT = @T_COUNT + 1 
    IF @E4bA > 0 set @T_COUNT = @T_COUNT + 1 
    IF @E4cA > 0 set @T_COUNT = @T_COUNT + 1 
    IF @E4dA > 0 set @T_COUNT = @T_COUNT + 1 

    declare @T_Prev_Count int
    set @T_Prev_Count = 0
    IF @Prev_E4aA > 0 set @T_Prev_Count = @T_Prev_Count + 1 
    IF @Prev_E4bA > 0 set @T_Prev_Count = @T_Prev_Count + 1 
    IF @Prev_E4cA > 0 set @T_Prev_Count = @T_Prev_Count + 1 
    IF @Prev_E4dA > 0 set @T_Prev_Count = @T_Prev_Count + 1


    declare @QI_BEHD4_D int
    declare @QI_BEHD4_N int
    set @QI_BEHD4_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_BEHD4_D
    end
    else
    begin
        IF @QI_BEHD4_D = 1 
        begin
            IF (@T_COUNT - @T_Prev_Count) > 0
                set @QI_BEHD4_N = 1
            ELSE
                set @QI_BEHD4_N = 0
        end
        set @return_value = @QI_BEHD4_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_BEHI4](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1     varchar(50),    
    @E4aA_R   varchar(50), @E4bA_R varchar(50),
    @E4cA_R   varchar(50), @E4dA_R varchar(50),
    @Prev_E4aA_R   varchar(50), @Prev_E4bA_R varchar(50),
    @Prev_E4cA_R   varchar(50), @Prev_E4dA_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @E4aA int, @E4bA int, @E4cA int, @E4dA int
    declare @Prev_E4aA int, @Prev_E4bA int, @Prev_E4cA int, @Prev_E4dA int

    if isnumeric(@E4aA_R) = 1 set @E4aA = cast(@E4aA_R as int)
    if isnumeric(@E4bA_R) = 1 set @E4bA = cast(@E4bA_R as int)
    if isnumeric(@E4cA_R) = 1 set @E4cA = cast(@E4cA_R as int)
    if isnumeric(@E4dA_R) = 1 set @E4dA = cast(@E4dA_R as int)
    if isnumeric(@Prev_E4aA_R) = 1 set @Prev_E4aA = cast(@Prev_E4aA_R as int)
    if isnumeric(@Prev_E4bA_R) = 1 set @Prev_E4bA = cast(@Prev_E4bA_R as int)
    if isnumeric(@Prev_E4cA_R) = 1 set @Prev_E4cA = cast(@Prev_E4cA_R as int)
    if isnumeric(@Prev_E4dA_R) = 1 set @Prev_E4dA = cast(@Prev_E4dA_R as int)
    
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when @E4aA is null or @E4bA is null or @E4cA is null or @E4dA is null then 1
                      end)
                      
    declare @T_COUNT int
    set @T_COUNT = 0
    IF @E4aA > 0 set @T_COUNT = @T_COUNT + 1 
    IF @E4bA > 0 set @T_COUNT = @T_COUNT + 1 
    IF @E4cA > 0 set @T_COUNT = @T_COUNT + 1 
    IF @E4dA > 0 set @T_COUNT = @T_COUNT + 1 

    declare @T_Prev_Count int
    set @T_Prev_Count = 0
    IF @Prev_E4aA > 0 set @T_Prev_Count = @T_Prev_Count + 1 
    IF @Prev_E4bA > 0 set @T_Prev_Count = @T_Prev_Count + 1 
    IF @Prev_E4cA > 0 set @T_Prev_Count = @T_Prev_Count + 1 
    IF @Prev_E4dA > 0 set @T_Prev_Count = @T_Prev_Count + 1


    declare @QI_BEHI4_D int
    declare @QI_BEHI4_N int
    set @QI_BEHI4_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_BEHI4_D
    end
    else
    begin
        IF @QI_BEHI4_D = 1 
        begin
            IF (@T_COUNT - @T_Prev_Count) < 0
                set @QI_BEHI4_N = 1
            ELSE
                set @QI_BEHI4_N = 0
        end
        set @return_value = @QI_BEHI4_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_CAT02](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @J5c     varchar(50),    @P1ao   varchar(50), @H3d varchar(50)
) returns int
begin
    declare @return_value int
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @H3d is null then 1
                      end)
                      
    declare @QI_CAT02_D int
    declare @QI_CAT02_N int
    set @QI_CAT02_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_CAT02_D
    end
    else
    begin
        IF @QI_CAT02_D = 1 
        begin
            IF @H3d = '1'
                set @QI_CAT02_N = 1
            ELSE
                set @QI_CAT02_N = 0
        end
        set @return_value = @QI_CAT02_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_CNT02](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50), 
    @H1a_R varchar(50), @H3i varchar(50),
    @Prev_H1a_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @H1a int, @Prev_H1a int
    
    if isnumeric(@H1a_R) = 1 set @H1a = cast(@H1a_R as int)
    if isnumeric(@Prev_H1a_R) = 1 set @Prev_H1a = cast(@Prev_H1a_R as int)
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_H1a = 4 then 1
                           when @H1a is null then 1
                           when @Prev_H1a is null and @H1a > 0 then 1
                           when isnull(@H3i, '1') = '1' then 1
                      end)
                      
    declare @QI_CNT02_D int
    declare @QI_CNT02_N int
    set @QI_CNT02_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_CNT02_D
    end
    else
    begin
        IF @QI_CNT02_D = 1 
        begin
            IF @H1a - @Prev_H1a > 0
                set @QI_CNT02_N = 1
            ELSE
                set @QI_CNT02_N = 0
        end
        set @return_value = @QI_CNT02_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_CNT03](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50), 
    @H1b_R varchar(50), @H3i varchar(50),
    @Prev_H1b_R varchar(50)
    
) returns int
begin
    declare @return_value int
    declare @H1b int, @Prev_H1b int
    if isnumeric(@H1b_R) = 1 set @H1b = cast(@H1b_R as int)
    if isnumeric(@Prev_H1b_R) = 1 set @Prev_H1b = cast(@Prev_H1b_R as int)
    
    --@CalendarYear int,      --ED - 02870
    --@CalendarQuarter int    --ED - 02870
    --declare @FiscalYear int
    --set @FiscalYear = @CalendarYear - (case when @CalendarYear = 1 then 1 else 0 end)   ----ED - 02870
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_H1b = 4 then 1
                           when @H1b is null then 1
                           when @Prev_H1b is null and @H1b > 0 then 1
                           --when isnull(@H3i, '1') = '1' then 1  --ED - 02870 CIHI remove H3i in 2012 and @FiscalYear < 2012
                      end)
                      
    declare @QI_CNT03_D int
    declare @QI_CNT03_N int
    set @QI_CNT03_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_CNT03_D
    end
    else
    begin
        IF @QI_CNT03_D = 1 
        begin
            IF @H1b - @Prev_H1b > 0
                set @QI_CNT03_N = 1
            ELSE
                set @QI_CNT03_N = 0
        end
        set @return_value = @QI_CNT03_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_CNT04](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @J5c varchar(50),    @P1ao   varchar(50),  @I2k varchar(50)
) returns int
begin
    declare @return_value int
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @I2k is null then 1
                      end)
                      
    declare @QI_CNT04_D int
    declare @QI_CNT04_N int
    set @QI_CNT04_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_CNT04_D
    end
    else
    begin
        IF @QI_CNT04_D = 1 
        begin
            IF @I2k = '1'
                set @QI_CNT04_N = 1
            ELSE
                set @QI_CNT04_N = 0
        end
        set @return_value = @QI_CNT04_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_CNT2A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50), 
    @H1a_R varchar(50), @H3i varchar(50),
    @Prev_H1a_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @H1a int, @Prev_H1a int
    
    if isnumeric(@H1a_R) = 1 set @H1a = cast(@H1a_R as int)
    if isnumeric(@Prev_H1a_R) = 1 set @Prev_H1a = cast(@Prev_H1a_R as int)
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_H1a = 0 then 1
                           when @H1a is null then 1
                           when @Prev_H1a is null and @H1a > 0 then 1
                           when isnull(@H3i, '1') = '1' then 1
                      end)
                      
    declare @QI_CNT2A_D int
    declare @QI_CNT2A_N int
    set @QI_CNT2A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_CNT2A_D
    end
    else
    begin
        IF @QI_CNT2A_D = 1 
        begin
            IF @H1a - @Prev_H1a < 0
                set @QI_CNT2A_N = 1
            ELSE
                set @QI_CNT2A_N = 0
        end
        set @return_value = @QI_CNT2A_N
    end
    
    return @return_value

end    

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_CNT3A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50), 
    @H1b_R varchar(50), @H3i varchar(50),
    @Prev_H1b_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @H1b int, @Prev_H1b int
    
    if isnumeric(@H1b_R) = 1 set @H1b = cast(@H1b_R as int)
    if isnumeric(@Prev_H1b_R) = 1 set @Prev_H1b = cast(@Prev_H1b_R as int)
    
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_H1b = 0 then 1
                           when @H1b is null then 1
                           when @Prev_H1b is null and @H1b > 0 then 1
                           --when isnull(@H3i, '1') = '1' then 1    --CIHI remove the H3i in 2012
                      end)
                      
    declare @QI_CNT3A_D int
    declare @QI_CNT3A_N int
    set @QI_CNT3A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_CNT3A_D
    end
    else
    begin
        IF @QI_CNT3A_D = 1 
        begin
            IF @H1b - @Prev_H1b < 0
                set @QI_CNT3A_N = 1
            ELSE
                set @QI_CNT3A_N = 0
        end
        set @return_value = @QI_CNT3A_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_COG01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @CPS_cc int,    @Prev_CPS_cc int
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_CPS_cc is null and @CPS_cc > 0 then 1
                           when @CPS_cc is null then 1
                           when @Prev_CPS_cc = 6 then 1
                      end)
                      
    declare @QI_COG01_D int
    declare @QI_COG01_N int
    set @QI_COG01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_COG01_D
    end
    else
    begin
        IF @QI_COG01_D = 1 
        begin
            IF @CPS_cc - @Prev_CPS_cc > 0
                set @QI_COG01_N = 1
            ELSE
                set @QI_COG01_N = 0
        end
        set @return_value = @QI_COG01_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_COG1A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @CPS_cc int,    @Prev_CPS_cc int
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_CPS_cc is null and @CPS_cc > 0 then 1
                           when @CPS_cc is null then 1
                           when @Prev_CPS_cc = 0 then 1
                      end)
                      
    declare @QI_COG1A_D int
    declare @QI_COG1A_N int
    set @QI_COG1A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_COG1A_D
    end
    else
    begin
        IF @QI_COG1A_D = 1 
        begin
            IF @CPS_cc - @Prev_CPS_cc < 0
                set @QI_COG1A_N = 1
            ELSE
                set @QI_COG1A_N = 0
        end
        set @return_value = @QI_COG1A_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_COM01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @C4_R varchar(50), @C6_R varchar(50),
    @Prev_C4_R varchar(50), @Prev_C6_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @C4 int, @C6 int, @Prev_C4 int, @Prev_C6 int
    
    if isnumeric(@C4_R) = 1 set @C4 = cast(@C4_R as int)
    if isnumeric(@C6_R) = 1 set @C6 = cast(@C6_R as int)
    if isnumeric(@Prev_C4_R) = 1 set @Prev_C4 = cast(@Prev_C4_R as int)
    if isnumeric(@Prev_C6_R) = 1 set @Prev_C6 = cast(@Prev_C6_R as int)
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @C4 is null or @C6 is null then 1
                           when (@Prev_C4 is null or @Prev_C6 is null) AND (isnull(@C4, 0) + isnull(@C6, 0) > 0) then 1
                           when isnull(@Prev_C4, 0) + isnull(@Prev_C6, 0) = 6 then 1
                      end)
                      
    declare @QI_COM01_D int
    declare @QI_COM01_N int
    set @QI_COM01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_COM01_D
    end
    else
    begin
        IF @QI_COM01_D = 1 
        begin
            IF (isnull(@C4, 0) + isnull(@C6, 0)) - (isnull(@Prev_C4, 0) + isnull(@Prev_C6, 0)) > 0 
                set @QI_COM01_N = 1
            ELSE
                set @QI_COM01_N = 0
        end
        set @return_value = @QI_COM01_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_COM1A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @C4_R varchar(50), @C6_R varchar(50),
    @Prev_C4_R varchar(50), @Prev_C6_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @C4 int, @C6 int, @Prev_C4 int, @Prev_C6 int
    
    if isnumeric(@C4_R) = 1 set @C4 = cast(@C4_R as int)
    if isnumeric(@C6_R) = 1 set @C6 = cast(@C6_R as int)
    if isnumeric(@Prev_C4_R) = 1 set @Prev_C4 = cast(@Prev_C4_R as int)
    if isnumeric(@Prev_C6_R) = 1 set @Prev_C6 = cast(@Prev_C6_R as int)
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @C4 is null and @C6 is null then 1
                           when (@Prev_C4 is null and @Prev_C6 is null) AND (isnull(@C4, 0) + isnull(@C6, 0) > 0) then 1
                           when isnull(@Prev_C4, 0) + isnull(@Prev_C6, 0) = 0 then 1
                      end)
                      
    declare @QI_COM1A_D int
    declare @QI_COM1A_N int
    set @QI_COM1A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_COM1A_D
    end
    else
    begin
        IF @QI_COM1A_D = 1 
        begin
            IF (isnull(@C4, 0) + isnull(@C6, 0)) - (isnull(@Prev_C4, 0) + isnull(@Prev_C6, 0)) < 0 
                set @QI_COM1A_N = 1
            ELSE
                set @QI_COM1A_N = 0
        end
        set @return_value = @QI_COM1A_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_DEL0X](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @B5a_R varchar(50), @B5b_R varchar(50), @B5c_R varchar(50), 
    @B5d_R varchar(50), @B5e_R varchar(50), @B5f_R varchar(50),
    @Prev_B5a_R varchar(50), @Prev_B5b_R varchar(50), @Prev_B5c_R varchar(50), 
    @Prev_B5d_R varchar(50), @Prev_B5e_R varchar(50), @Prev_B5f_R varchar(50),
    @CPS_cc int
) returns int
begin
    declare @return_value int
    declare @B5a int, @B5b int, @B5c int, @B5d int, @B5e int, @B5f int 
    declare @Prev_B5a int, @Prev_B5b int, @Prev_B5c int, @Prev_B5d int, @Prev_B5e int, @Prev_B5f int
    
    if isnumeric(@B5a_R) = 1 set @B5a = cast(@B5a_R as int)
    if isnumeric(@B5b_R) = 1 set @B5b = cast(@B5b_R as int)
    if isnumeric(@B5c_R) = 1 set @B5c = cast(@B5c_R as int)
    if isnumeric(@B5d_R) = 1 set @B5d = cast(@B5d_R as int)
    if isnumeric(@B5e_R) = 1 set @B5e = cast(@B5e_R as int)
    if isnumeric(@B5f_R) = 1 set @B5f = cast(@B5f_R as int)
    if isnumeric(@Prev_B5a_R) = 1 set @Prev_B5a = cast(@Prev_B5a_R as int)
    if isnumeric(@Prev_B5b_R) = 1 set @Prev_B5b = cast(@Prev_B5b_R as int)
    if isnumeric(@Prev_B5c_R) = 1 set @Prev_B5c = cast(@Prev_B5c_R as int)
    if isnumeric(@Prev_B5d_R) = 1 set @Prev_B5d = cast(@Prev_B5d_R as int)
    if isnumeric(@Prev_B5e_R) = 1 set @Prev_B5e = cast(@Prev_B5e_R as int)
    if isnumeric(@Prev_B5f_R) = 1 set @Prev_B5f = cast(@Prev_B5f_R as int)
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @B5a is null or @B5b is null or @B5c is null or @B5d is null or @B5e is null or @B5f is null then 1
                           when (@Prev_B5a is null and @B5a > 0) or (@Prev_B5b is null and @B5b > 0) or (@Prev_B5c is null and @B5c > 0) or
                                (@Prev_B5d is null and @B5d > 0) or (@Prev_B5e is null and @B5e > 0) or (@Prev_B5f is null and @B5f > 0) 
                                then 1
                      end)
                      
    declare @QI_DEL0X_D int
    declare @QI_DEL0X_N int
    set @QI_DEL0X_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_DEL0X_D
    end
    else
    begin
        IF @QI_DEL0X_D = 1 
        begin
           IF (@B5A = 2 OR @B5B = 2 OR @B5C = 2 OR @B5D = 2 OR @B5E = 2 OR @B5F = 2)
            OR ( (@B5A = 1 AND @Prev_B5a = 2) OR (@B5B = 1 AND @Prev_B5b = 2) OR
                 (@B5C = 1 AND @Prev_B5c = 2) OR (@B5D = 1 AND @Prev_B5d = 2) OR
                 (@B5E = 1 AND @Prev_B5e = 2) OR (@B5F = 1 AND @Prev_B5f = 2))
            OR (((@B5A = 1 AND @Prev_B5a = 0) OR (@B5B = 1 AND @Prev_B5b = 0) OR
                 (@B5C = 1 AND @Prev_B5c = 0) OR (@B5D = 1 AND @Prev_B5d = 0) OR
                 (@B5E = 1 AND @Prev_B5e = 0) OR (@B5F = 1 AND @Prev_B5f = 0))
                AND @CPS_cc in (0, 1, 2, 3))
                set @QI_DEL0X_N = 1
            ELSE
                set @QI_DEL0X_N = 0
        end
        set @return_value = @QI_DEL0X_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_DRG01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @J5c varchar(50),    @P1ao   varchar(50),   
    @J1i varchar(50),    @J1e    varchar(50),
    @O4a int, @I1ii varchar(50), @I1x varchar(50)
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1
                           when isnull(@I1ii, '1') = '1' then 1
                           when isnull(@I1x, '1') = '1' then 1
                           when isnull(@J1i, '1') = '1' then 1
                           when isnull(@J1e, '1') = '1' then 1  --292-0027
                           when @O4a is null then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                      end)
                      
    declare @QI_DRG01_D int
    declare @QI_DRG01_N int
    set @QI_DRG01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_DRG01_D
    end
    else
    begin
        IF @QI_DRG01_D = 1 
        begin
           IF @O4a > 0
                set @QI_DRG01_N = 1
            ELSE
                set @QI_DRG01_N = 0
        end
        set @return_value = @QI_DRG01_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_FAL02](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @J4a varchar(50)
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1
                           when @J4a is null then 1
                      end)
                      
    declare @QI_FAL02_D int
    declare @QI_FAL02_N int
    set @QI_FAL02_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_FAL02_D
    end
    else
    begin
        IF @QI_FAL02_D = 1 
        begin
           IF @J4a = '1'
                set @QI_FAL02_N = 1
            ELSE
                set @QI_FAL02_N = 0
        end
        set @return_value = @QI_FAL02_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_GetPreviousID](
    @LinkID int, @CalendarYear int, @CalendarQuarter int, 
    @A3 datetime, @AdmitDate datetime
) returns int
as 
begin
    declare @MaxA3 datetime
    declare @ID int
    
    set @MaxA3 = (select max(A3) from CRS_Demographic 
				  where LinkID = @LinkID 
				        and datediff(day, @AdmitDate, A3) >= 93 
						and datepart(year, A3) = (case when @CalendarQuarter = 1 then @CalendarYear - 1 else @CalendarYear end)
						and datepart(quarter, A3) = (case when @CalendarQuarter = 1 then 4 else @CalendarQuarter - 1 end)
						and AA8 in (14502, 14503, 14504, 14505, 14506, 14511)
						and datediff(day, A3, @A3) between 45 and 165)
						
    set @ID = (select top 1 AbstractID from CRS_Demographic	
                where LinkID = @LinkID and A3 = @MaxA3 and AA8 in (14502, 14503, 14504, 14505, 14506, 14511)
                order by AbstractID desc)
    
    return @ID
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_INF0X](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @J5c varchar(50),    @P1ao   varchar(50),
    @I2f varchar(50), @I2g varchar(50), @I2h varchar(50), 
    @I2k varchar(50), @I2l varchar(50), @I2m varchar(50),
    @J1h varchar(50), @J1k varchar(50)
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @I2f is null or @I2g is null or @I2h is null or @I2k is null or @I2l is null or 
                                @I2m is null or @J1h is null or @J1k is null then 1
                      end)
                      
    declare @QI_INF0X_D int
    declare @QI_INF0X_N int
    set @QI_INF0X_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_INF0X_D
    end
    else
    begin
        IF @QI_INF0X_D = 1 
        begin
           IF @I2f = '1' OR @I2g = '1' OR @I2h = '1' OR @I2k = '1' OR @I2l = '1' OR @I2m = '1' OR @J1h = '1' OR @J1k = '1'
                set @QI_INF0X_N = 1
            ELSE
                set @QI_INF0X_N = 0
        end
        set @return_value = @QI_INF0X_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_MOB01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @G1eA_R varchar(50), @Prev_G1eA_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1eA int
    declare @Prev_G1eA int
    
    if isnumeric(@G1eA_R) = 1 set @G1eA = cast(@G1eA_R as int)
    if isnumeric(@Prev_G1eA_R) = 1 set @Prev_G1eA = cast(@Prev_G1eA_R as int)

    if @G1eA = 8 set @G1eA = 4
    if @Prev_G1eA = 8 set @Prev_G1eA = 4
        
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_G1eA = 4 then 1
                           when @G1eA is null then 1
                           when (@Prev_G1eA is null and @G1eA > 0) then 1
                      end)
                      
    declare @QI_MOB01_D int
    declare @QI_MOB01_N int
    set @QI_MOB01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_MOB01_D
    end
    else
    begin
        IF @QI_MOB01_D = 1 
        begin
           IF @G1eA - @Prev_G1eA > 0
                set @QI_MOB01_N = 1
            ELSE
                set @QI_MOB01_N = 0
        end
        set @return_value = @QI_MOB01_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_MOB1A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @G1eA_R varchar(50), @Prev_G1eA_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @G1eA int
    declare @Prev_G1eA int
    
    if isnumeric(@G1eA_R) = 1 set @G1eA = cast(@G1eA_R as int)
    if isnumeric(@Prev_G1eA_R) = 1 set @Prev_G1eA = cast(@Prev_G1eA_R as int)

    if @G1eA = 8 set @G1eA = 4
    if @Prev_G1eA = 8 set @Prev_G1eA = 4
        
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @Prev_G1eA = 0 then 1
                           when @G1eA is null then 1
                           when (@Prev_G1eA is null and @G1eA > 0) then 1
                      end)
                      
    declare @QI_MOB1A_D int
    declare @QI_MOB1A_N int
    set @QI_MOB1A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_MOB1A_D
    end
    else
    begin
        IF @QI_MOB1A_D = 1 
        begin
           IF @G1eA - @Prev_G1eA < 0
                set @QI_MOB1A_N = 1
            ELSE
                set @QI_MOB1A_N = 0
        end
        set @return_value = @QI_MOB1A_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_MOD4A](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @DRS_cc int, @Prev_DRS_cc int
) returns int
begin
    declare @return_value int

    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when @Prev_DRS_cc = 14 then 1
                           when @DRS_cc is null then 1
                           when (@Prev_DRS_cc is null and @DRS_cc > 0) then 1
                      end)
                      
    declare @QI_MOD4A_D int
    declare @QI_MOD4A_N int
    set @QI_MOD4A_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_MOD4A_D
    end
    else
    begin
        IF @QI_MOD4A_D = 1 
        begin
           IF @DRS_cc - @Prev_DRS_cc > 0
                set @QI_MOD4A_N = 1
            ELSE
                set @QI_MOD4A_N = 0
        end
        set @return_value = @QI_MOD4A_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_NUT01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50),   @J5c varchar(50),    @P1ao   varchar(50),
    @K5b varchar(50)
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when @K5b is null then 1
                      end)
                      
    declare @QI_NUT01_D int
    declare @QI_NUT01_N int
    set @QI_NUT01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_NUT01_D
    end
    else
    begin
        IF @QI_NUT01_D = 1 
        begin
           IF @K5b = '1'
                set @QI_NUT01_N = 1
            ELSE
                set @QI_NUT01_N = 0
        end
        set @return_value = @QI_NUT01_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_PAI0X](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @J2a varchar(50),   @J2b varchar(50), @AA8  varchar(50)
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case --when @PV_QI_Flag = 0 then 1
                           when cast(@AA8 as int) = 1 then 1
                           when @J2a is null or ((@J2a = '1' or @J2a = '2') and @J2b is null) then 1
                           when @J2a = '0' and @J2b in ('1', '2', '3') then 1
                      end)
                      
    declare @QI_PAI0X_D int
    declare @QI_PAI0X_N int
    set @QI_PAI0X_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_PAI0X_D
    end
    else
    begin
        IF @QI_PAI0X_D = 1 
        begin
           IF (@J2a = '2' AND @J2b = '2') OR @J2b = '3'
                set @QI_PAI0X_N = 1
            ELSE
                set @QI_PAI0X_N = 0
        end
        set @return_value = @QI_PAI0X_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_PAN01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @Pain_Scale_cc int,   @Prev_Pain_Scale_cc int
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1
                           when @Prev_Pain_Scale_cc = 3 then 1
                           when @Pain_Scale_cc is null then 1   
                           when @Prev_Pain_Scale_cc is null and @Pain_Scale_cc > 0 then 1
                      end)
                      
    declare @QI_PAN01_D int
    declare @QI_PAN01_N int
    set @QI_PAN01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_PAN01_D
    end
    else
    begin
        IF @QI_PAN01_D = 1 
        begin
           IF @Pain_Scale_cc - @Prev_Pain_Scale_cc > 0
                set @QI_PAN01_N = 1
            ELSE
                set @QI_PAN01_N = 0
        end
        set @return_value = @QI_PAN01_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_PRU05](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @M2a_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @M2a int
    
    if isnumeric(@M2a_R) = 1 set @M2a = cast(@M2a_R as int)
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1 end)
                      
    declare @QI_PRU05_D int
    declare @QI_PRU05_N int
    set @QI_PRU05_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_PRU05_D
    end
    else
    begin
        IF @QI_PRU05_D = 1 
        begin
           IF @M2a > 1
                set @QI_PRU05_N = 1
            ELSE
                set @QI_PRU05_N = 0
        end
        set @return_value = @QI_PRU05_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_PRU06](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @M2a_R varchar(50), @Prev_M2a_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @M2a int, @Prev_M2a int
    
    if isnumeric(@M2a_R) = 1 set @M2a = cast(@M2a_R as int)
    if isnumeric(@Prev_M2a) = 1 set @Prev_M2a = cast(@Prev_M2a as int)
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1 
                           when @M2a is null then 1
                           when @Prev_M2a is null and @M2a > 0 then 1
                           when @Prev_M2a = 4 then 1
                      end)
                      
    declare @QI_PRU06_D int
    declare @QI_PRU06_N int
    set @QI_PRU06_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_PRU06_D
    end
    else
    begin
        IF @QI_PRU06_D = 1 
        begin
           IF @M2a > 1 AND (@M2a - @Prev_M2a) > 0 AND @Prev_M2a < 4
                set @QI_PRU06_N = 1
            ELSE
                set @QI_PRU06_N = 0
        end
        set @return_value = @QI_PRU06_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[MDS20_QI_PRU09](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @M2a_R varchar(50), @Prev_M2a_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @M2a int, @Prev_M2a int
    
    if isnumeric(@M2a_R) = 1 set @M2a = cast(@M2a_R as int)
    if isnumeric(@Prev_M2a) = 1 set @Prev_M2a = cast(@Prev_M2a as int)
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @Inc_QI_Flag = 0 then 1 
                           when @M2a is null then 1
                           when @Prev_M2a >= 2 then 1
                      end)
                      
    declare @QI_PRU09_D int
    declare @QI_PRU09_N int
    set @QI_PRU09_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_PRU09_D
    end
    else
    begin
        IF @QI_PRU09_D = 1 
        begin
           IF @M2a >= 2 AND @Prev_M2a <= 1 
                set @QI_PRU09_N = 1
            ELSE
                set @QI_PRU09_N = 0
        end
        set @return_value = @QI_PRU09_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_RES01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @B1 varchar(50), @I1bb varchar(50), 
    @P4c varchar(50), @P4d varchar(50), @P4e varchar(50)
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1 
                           when isnull(@B1, '1') = '1' then 1
                           when isnull(@I1bb, '1') = '1' then 1
                           when @P4c is null or @P4d is null or @P4e is null then 1
                      end)
                      
    declare @QI_RES01_D int
    declare @QI_RES01_N int
    set @QI_RES01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_RES01_D
    end
    else
    begin
        IF @QI_RES01_D = 1 
        begin
           IF @P4c = '2' OR @P4d = '2' OR @P4e = '2'
                set @QI_RES01_N = 1
            ELSE
                set @QI_RES01_N = 0
        end
        set @return_value = @QI_RES01_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_RSPX2](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @I2f_R varchar(50), @J1b_R varchar(50), @J1l_R varchar(50), @J1k_R varchar(50), 
    @Prev_I2f_R varchar(50), @Prev_J1b_R varchar(50), @Prev_J1l_R varchar(50), @Prev_J1k_R varchar(50)
) returns int
begin
    declare @return_value int
    declare @I2f int, @J1b int, @J1l int, @J1k int
    declare @Prev_I2f int, @Prev_J1b int, @Prev_J1l int, @Prev_J1k int
    
    if isnumeric(@I2f_R) = 1 set @I2f = cast(@I2f_R as int)
    if isnumeric(@J1b_R) = 1 set @J1b = cast(@J1b_R as int)
    if isnumeric(@J1l_R) = 1 set @J1l = cast(@J1l_R as int)
    if isnumeric(@J1k_R) = 1 set @J1k = cast(@J1k_R as int)
    if isnumeric(@Prev_I2f_R) = 1 set @Prev_I2f = cast(@Prev_I2f_R as int)
    if isnumeric(@Prev_J1b_R) = 1 set @Prev_J1b = cast(@Prev_J1b_R as int)
    if isnumeric(@Prev_J1l_R) = 1 set @Prev_J1l = cast(@Prev_J1l_R as int)
    if isnumeric(@Prev_J1k_R) = 1 set @Prev_J1k = cast(@Prev_J1k_R as int)
    
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1 
                           when @I2f is null and @J1b is null and @J1l is null and @J1k is null then 1
                           when (@Prev_I2f is null or @Prev_J1b is null or @Prev_J1l is null or @Prev_J1k is null) 
                                and (@I2f > 0 or @J1b > 0 or @J1l > 0 or @J1k > 0) then 1
                      end)
    declare @T_Respiratory int, @T_Prev_Respiratory int
    set @T_Respiratory = isnull(@I2f, 0) + isnull(@J1b, 0) + isnull(@J1k, 0) + isnull(@J1l, 0)
    set @T_Prev_Respiratory = isnull(@Prev_I2f, 0) + isnull(@Prev_J1b, 0) + isnull(@Prev_J1k, 0) + isnull(@Prev_J1l, 0)
                  
    declare @QI_RSPX2_D int
    declare @QI_RSPX2_N int
    set @QI_RSPX2_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_RSPX2_D
    end
    else
    begin
        IF @QI_RSPX2_D = 1 
        begin
           IF (@T_Respiratory > 0 AND @T_Prev_Respiratory = 0)
            OR (@T_Prev_Respiratory > 0 AND @T_Respiratory >= @T_Prev_Respiratory)
                set @QI_RSPX2_N = 1
            ELSE
                set @QI_RSPX2_N = 0
        end
        set @return_value = @QI_RSPX2_N
    end
    
    return @return_value

end    


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function [dbo].[MDS20_QI_WGT01](
    @IsDenominatorFlag int, @PV_QI_Flag int,    
    @Inc_QI_Flag       int, @Prev_Ax_Flag int,
    @J5c varchar(50),    @P1ao   varchar(50),
    @K3a varchar(50), @K5h varchar(50)
) returns int
begin
    declare @return_value int
     
    declare @T_Exclude int
    
    set @T_Exclude = (case when @PV_QI_Flag = 0 then 1
                           when isnull(@K3a, '9') = '9' then 1
                           when isnull(@J5c, '1') = '1' then 1
                           when isnull(@P1ao, '1') = '1' then 1
                           when isnull(@K5h, '1') = '1' then 1
                      end)
                      
    declare @QI_WGT01_D int
    declare @QI_WGT01_N int
    set @QI_WGT01_D = (case when @T_Exclude = 1 then 0 else 1 end)
    
    if @IsDenominatorFlag = 1
    begin
        set @return_value = @QI_WGT01_D
    end
    else
    begin
        IF @QI_WGT01_D = 1 
        begin
           IF @K3a = '1'
                set @QI_WGT01_N = 1
            ELSE
                set @QI_WGT01_N = 0
        end
        set @return_value = @QI_WGT01_N
    end
    
    return @return_value

end    



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



create function dbo.MF_IndexName ( 
    @TableName sysname, @ColumnName sysname 
)
returns sysname
as 
begin
    declare @result sysname 

    select @result = i.name 
    from sysindexes I 
    where I.id = object_id(@TableName) 
    and I.indid > 0 
    and I.indid < 255 
    and INDEXPROPERTY(I.id, I.name, N'IsStatistics') = 0 
    and INDEXPROPERTY(I.id, I.name, N'IsHypothetical') = 0 
    and I.name not in 
    (
        select O.name 
        from sysobjects O 
        where O.parent_obj = I.id 
        and OBJECTPROPERTY(O.id, N'isConstraint') = 1
    )
    and I.indid in 
    (
        select indid 
        from sysindexkeys K
        where id = I.id
        and colid in 
        (
            select colid 
            from syscolumns 
            where id = K.id
            and name = @ColumnName
        )
    )

    return @result
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



create function dbo.MF_PrimaryKeyName ( 
    @TableName sysname
)
returns sysname
as 
begin
    declare @result sysname 

    select @result = name 
        from sysobjects 
        where parent_obj = object_id(@TableName)
        and type = N'K'
        and xtype = N'PK'

    return @result
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE FUNCTION ModuleConcat ( @FieldID int )
RETURNS varchar (2500)
AS
BEGIN

DECLARE @sModule varchar (50)
DECLARE @sModules varchar (1000)

DECLARE @tmpModules TABLE
  (Module varchar (1000))

   INSERT @tmpModules
      SELECT    
	U_AppIDs.ModuleName AS [Module]     
FROM    U_UsersFields INNER JOIN
                      U_AppIDs ON U_UsersFields.AppID = U_AppIDs.AppID
WHERE     
	(U_UsersFields.DataType <> 100000) 
	AND (U_UsersFields.UserID = 1)
	AND U_UsersFields.FieldID = @FieldID
ORDER BY 	
	U_AppIDs.ModuleName

DECLARE  ModulesCur CURSOR FAST_FORWARD FOR
SELECT Module FROM @tmpModules

OPEN ModulesCur

FETCH NEXT FROM ModulesCur INTO @sModule
SET @sModules = @sModule
WHILE @@FETCH_STATUS = 0
BEGIN	
	FETCH NEXT FROM ModulesCur INTO @sModule	
	if (@@FETCH_STATUS <> -1) and (@@FETCH_STATUS <> -2)
		select @sModules = @sModules + ', ' + isnull(@sModule, '')		
END
CLOSE ModulesCur
DEALLOCATE ModulesCur
RETURN @sModules


END

--select dbo.moduleconcat (560)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function [dbo].[NACRS_CareType_MisCodes] 
( 
    @UseCareType int 
)  
returns @results table  
( 
    Code int 
) 
as  
begin 
   if @UseCareType = 15642 -- B-C 
        insert @results ( Code ) 
           select Code  
        from LU_MISCode  
        where not ((
            left(CIHIValue, 1) = '7' 
            and ( 
                substring(CIHIValue, 3, 5) in ('34025', '34020', '34055') or  
                substring(CIHIValue, 3, 3) in ('260', '265', '262', '310', '360', '362', '365', '369')) --ED - 01939
			and isnull(AmcareGroup, '') = ''               
--PR - 03310
--            left(CIHIValue, 1) = '7' 
--            and ( 
--                substring(CIHIValue, 3, 5) in ('34066', '35066', '53066', '34086', '53086', '41544') or  
--                substring(CIHIValue, 3, 3) in ('420', '466') 
            
			) or AmcareGroup in ('DS', 'ED')
        )
        
 
   else if @UseCareType = 15643 -- B-D 
        insert @results ( Code ) 
           select Code 
        from LU_MISCode  
        where ( 
            left(CIHIValue, 1) = '7' 
            and ( 
--PR - 03310
                substring(CIHIValue, 3, 5) in ('34025', '34020', '34055') or  
                substring(CIHIValue, 3, 3) in ('260', '265', '262', '360', '362', '365', '369') --ED - 01939
--                substring(CIHIValue, 3, 5) = ('34025') or  
--                substring(CIHIValue, 3, 3) in ('260', '265') or  
--                right(CIHIValue, 5) in ('34020', '34055') or 
--                right(CIHIValue, 3) = ('262') 
            ) and isnull(AmcareGroup, '') = ''
        ) or AmcareGroup = 'DS'
 
    
   else if @UseCareType = 15644 -- B-E 
        insert @results ( Code ) 
           select Code 
        from LU_MISCode  
--PR - 03310
        where (CIHIValue like '7_310%' and isnull(AmcareGroup, '') = '') or AmcareGroup = 'ED'
--        where not ( 
--            left(CIHIValue, 1) = '7' 
--            and ( 
--                substring(CIHIValue, 3, 5) in ('34066', '35066', '53066', '34086', '53086', '41544', '34025') or  
--                substring(CIHIValue, 3, 3) in ('420', '466', '260', '265') or 
--                right(CIHIValue, 5) in ('34020', '34055') or 
--                right(CIHIValue, 3) = ('262') 
--            ) 
--        ) 

   else if @UseCareType = 15645 -- B-E 2.x-0929 add Opioid case
        insert @results ( Code ) 
           select Code 
        from LU_MISCode  
        where AmcareGroup = 'ED'
 
   else -- all types 
   begin  
       insert @results values (0) 
       insert @results ( Code ) 
           select Code from LU_MISCode  
   end 
return  
end 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE function dbo.RemoveLeadingZero
(
    @Value varchar(20)
) 
returns varchar(20) as
begin 
/*
<recode_name>
Remove Leading space and zeros.
</recode_name>
*/

    set @Value = ltrim(rtrim(@Value))
    set @Value = substring(@Value, patindex( '%[^ 0]%', @Value), 20)

    -- PR - 00865
	declare @Front 	varchar(20)
	declare @Rear	varchar(20)
	declare @i		int
	declare @j 		int

	set @i = patindex('%[ 0123456789]%', @Value)
	if @i > 1
	begin
		set @Front = left(@Value, @i-1)
		set @Rear = ltrim(substring(@Value, @i, 20))
        if @Rear <> ''
        begin
            set @j = patindex( '%[^ 0]%', @Rear)
            if @j = 0
                set @Rear = ''
            else
                set @Rear = isnull(substring(@Rear, @j, 20), '')
        end
        set @Value = @Front + @Rear
	end

	return (@Value)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  function [dbo].[SUF_CRS_Main_DateField] ( 
    @Z1            int, 
    @AdmitDate     datetime,
    @AB1b          datetime,
    @A3            datetime,
    @Z7            datetime,
    @DischargeDate datetime,
    @Z12		   datetime,
    @Z13           datetime
) returns varchar(10) as 
begin
    return 
    (
        convert( varchar(10), 
            case @Z1 
            when 15351 then @AdmitDate     -- ADMISSION BACKGROUND
            when 15352 then @AB1b          -- RE-ENTRY
            when 15353 then @A3            -- FULL ASSESSMENT
            when 15354 then @A3            -- QUARTERLY ASSESSMENT
            when 15355 then @A3            -- MEDICAL ASSESSMENT
            when 15356 then @Z7            -- DEMOGRAPHIC CORRECTION
            when 15357 then @DischargeDate -- DISCHARGE ASSESSMENT
            when 203621 then @Z12		   -- Update ASSESSMENT
            when 203622 then @Z13		   -- Project ASSESSMENT
            --2012
            when 18539 then @AdmitDate     -- ADMISSION BACKGROUND
            when 18540 then @A3            -- FULL ASSESSMENT
            when 18541 then @A3            -- QUARTERLY ASSESSMENT
            when 18542 then @A3            -- MEDICAL ASSESSMENT
            when 18543 then @DischargeDate -- DISCHARGE ASSESSMENT
            when 18544 then @Z12		   -- Update ASSESSMENT
            when 18545 then @Z13		   -- Project ASSESSMENT
            else null 
            end
        , 111 )
    )
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  function [dbo].[SUF_DAD_Provider_Prefix_9] ( 
    @ProviderType varchar(20), @ProviderNumber varchar(15)
) returns varchar(15)
as
begin
    --999999999XXXXXX for provider type P
    declare @Province varchar(20)
    
    set @Province = (select top 1 p.CIHIValue from h_HospitalProfile h inner join lu_province p on h.provincenumber = p.code)
    if @Province = '5' and @ProviderType = 'P' and len(ltrim(isnull(@ProviderNumber, ''))) > 0
    begin
        set @ProviderNumber = ltrim(rtrim(@ProviderNumber))
        if len(@ProviderNumber) < 6 
            set @ProviderNumber = Replicate('0', 6 - len(@ProviderNumber)) + @ProviderNumber
        --else
        --    set @ProviderNumber = right(@ProviderNumber, 6)
            
        if len(@ProviderNumber) < 15 
            set @ProviderNumber = Replicate('9', 15 - len(@ProviderNumber)) + @ProviderNumber
	end
    return ( ltrim(rtrim(@ProviderNumber)) )
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_EncounterNumber_RPB](
    @Su_Type int, @EncounterNumber varchar(20), @AncillaryName varchar(20), @AncillaryCode varchar(12) --, @MISCode int
) returns varchar(10)
as
begin
    declare @returnvalue varchar(1)
    declare @i int
    declare @MISCodeCIHIValue varchar(9)

    if @SU_type = 38
    begin
        if not exists (select code from H_HospitalProfile where isRBillingChart = 1)
            set @SU_type = 0 
    end

    set @i = cast(replace(@AncillaryName, 'AncillaryField', '') as int)
    if @SU_Type = 38
    begin
        if @i > len(@EncounterNumber)
            set @returnvalue = ''
        else
        begin
            set @returnvalue = substring(@EncounterNumber, @i, 1)
        end
    end
    --ED - 01949
--    else if exists (select * from H_HospitalProfile h inner join lu_province p on h.provincenumber = p.code where p.cihivalue = '3')
--    begin --This is for NB 
--        if @MISCode > 0 and @i <= 9
--        begin
--            set @MISCodeCIHIValue = (select top 1 CIHIValue from Lu_miscode where code = @MISCode)
--            if @i <= len(@MISCodeCIHIValue)
--                set @returnvalue = substring(@MISCodeCIHIValue, @i, 1) 
--        end    
--    end
    else
        set @returnvalue = left(isnull(cast(@Ancillarycode as varchar(12)), ''), 1)
    return (@returnvalue)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_GET_GroupID] (
    @SUType   int,
    @AppID    int,
    @FiscalYr int,
    @ProvCode int,
    @NacrsLevelCode int = 0
) returns int as 
begin

    declare @t table (GroupID int, Province int)
    declare @group_id int

	if @AppID = 401 
	begin
		if @NacrsLevelCode = 1
			set @ProvCode = -1  --ED - 01794
		else if @NacrsLevelCode = 2
			set @ProvCode = -2  --ED - 02102
		else if @NacrsLevelCode = 0
			set @ProvCode = -3  --2.x-0278
	end        

    insert @t (GroupID, Province)
    select GroupID, Province
    from SU_StructureList
    where SU_Type = @SUType
      and AppID = @AppID
      and BatchYear = @FiscalYr
      and Province in (@ProvCode, 0) 
    
    if exists(select * from @t where Province = @ProvCode)
        select @group_id = GroupID from @t where Province = @ProvCode
    else
        select @group_id = GroupID from @t where Province = 0

    return ( @group_id )

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





create function dbo.SUF_GetAlbertaSubmissionType (
    @CIHIReturnStatus int
) returns varchar(1)
as 
begin

    return (
        case @CIHIReturnStatus
            when 1 then 'A'    -- Submission: add record 
            when 2 then 'D'    -- Correction: delete record
            when 3 then 'A'    -- Correction: add record 
            when 4 then 'C'    -- Correction: update record
        end
    )
end






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO






CREATE  function dbo.SUF_GetAlbertaSubmitterPrefix (
    @FacilityNumber varchar(4)
) returns varchar(3)
as 
begin 
    return (
        case 
            
            -- Chinook Health Region
            when @FacilityNumber in ('8009', '8023', '8028', '8050', '8071', '8075', '8080', '8086', '8087', '8091', '8100', '8330', '8911', '8912')
            then 'CHR'
            
            -- Palliser Health Region
            when @FacilityNumber in ('8005', '8011', '8014', '8048', '8079', '8084', '8331')
            then 'MHA'
            
            -- Headwaters Health Region
            when @FacilityNumber in ('8002', '8022', '8027', '8060', '8111', '8139', '8207', '8332', '8816')
            then 'CGA'
            
            -- Calgary Health Region
            when @FacilityNumber in ('8015', '8016', '8020', '8148', '8203', '8240', '8333', '8625', '8809', '8810', '8811', '8812', '8813', '8814', '8815', '8817', '8818', '8819', '8820', '8821', '8826', '8827', '8904', '8905', '8906', '8908', '8909')
            then 'CAL'
            
            -- Region 5 Health Region
            when @FacilityNumber in ('8026')
            then space(3)
            
            when @FacilityNumber in ('8034')
            then 'DDH'
            
            when @FacilityNumber in ('8036')
            then 'DHS'
            
            when @FacilityNumber in ('8057')
            then 'HDS'
            
            when @FacilityNumber in ('8101', '8134', '8334')
            then 'THD'
            
            -- David Thompson Health Region
            when @FacilityNumber in ('8004', '8007', '8037', '8047', '8063', '8068', '8083', '8088', '8092', '8093', '8094', '8103', '8119', '8335', '8913', '8914')
            then 'DTH'
            
            -- East Central Health Region
            when @FacilityNumber in ('8021', '8025', '8030', '8031', '8032', '8054', '8058', '8064', '8066', '8077', '8089', '8097', '8102', '8108', '8109', '8112', '8336')
            then 'ECH'
            
            -- Westview Health Region
            when @FacilityNumber in ('8033', '8045', '8061', '8065', '8150', '8337')
            then 'STP'
            
            -- Crossroads Health Region
            when @FacilityNumber in ('8013', '8035', '8114', '8338')
            then 'CRA'
            
            -- Capital Health Region
            when @FacilityNumber in ('8040', '8041', '8042', '8043', '8044', '8070', '8120', '8149', '8242', '8298', '8339', '8626', '8800', '8802', '8803', '8804', '8805', '8806', '8807', '8808', '8822', '8823', '8824', '8825', '8900', '8901', '8902', '8903', '8915')
            then 'ACR'
            
            -- Aspen Health Region
            when @FacilityNumber in ('8001')
            then 'ATH'
            
            when @FacilityNumber in ('8003')
            then 'BRR'
            
            when @FacilityNumber in ('8012')
            then 'BOY'
            
            when @FacilityNumber in ('8078')
            then 'MAY'
            
            when @FacilityNumber in ('8113')
            then 'WES'
            
            when @FacilityNumber in ('8116')
            then 'WHI'
            
            when @FacilityNumber in ('8122')
            then 'WLC'
            
            when @FacilityNumber in ('8133')
            then 'FOX'
            
            when @FacilityNumber in ('8136')
            then 'SWA'
            
            when @FacilityNumber in ('8340')
            then 'AHS' -- 'AHW'
            
            -- Lake Land Health Region
            when @FacilityNumber in ('8029', '8046', '8052', '8055', '8067', '8069', '8077', '8081', '8082', '8095', '8099', '8105', '8107', '8110', '8115', '8122', '8141', '8341')
            then 'FSA'
            
            -- Mistahia Health Region
            when @FacilityNumber in ('8006', '8049', '8056', '8062', '8096', '8106', '8121', '8132', '8133', '8342')
            then 'QEG'
            
            -- Peace Health Region
            when @FacilityNumber in ('8074')
            then 'SHM'
            
            when @FacilityNumber in ('8076')
            then 'MCH'
            
            when @FacilityNumber in ('8085', '8343')
            then 'PRH'
            
            when @FacilityNumber in ('8132')
            then 'GBD'
            
            -- Keeweetinok Lakes Healt Region
            when @FacilityNumber in ('8059')
            then 'HPR'
            
            when @FacilityNumber in ('8118')
            then 'SLK'
            
            when @FacilityNumber in ('8144')
            then 'WAB'
            
            when @FacilityNumber in ('8344')
            then 'HPC'
            
            -- Northern Lights Health Region
            when @FacilityNumber in ('8117', '8345')
            then 'NOR'
            
            -- Northwestern Health Region
            when @FacilityNumber in ('8053', '8123', '8145', '8346')
            then 'HLG'
            
            -- All Health Regions can submit for
            when @FacilityNumber in ('8996', '8073', '8888', '8997', '8998', '8999')
            then space(3)
            
            -- THIS IS FOR TESTING PURPOSES ONLY.  
            -- It can be deleted if it interferes with the actual function.
            when @FacilityNumber in ('1235')
            then 'ZZZ'

            else space(3)
        end
    )

end








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE function [dbo].[SUF_GetFromClauseBaseTbl] (
    @TableName  varchar(50),
    @TempTable  varchar(50),
    @Occurrence int
) returns varchar(300)
begin
    declare @TableAlias varchar(50)
    set @TableAlias = dbo.SUF_GetTableAlias( @TableName, @Occurrence )

    return (
        'left join ' + @TempTable + ' ' + @TableAlias + ' on RecordSelect.AbstractID = ' + @TableAlias + '.AbstractID ' + 
        case when (@Occurrence > 0) then 'and ' + @TableAlias + '.' + 
            case (replace( @TableName, '##', '' ))
                when 'AB_CMG'             then 'OccurrenceNumber'
                when 'AB_Diagnosis'       then 'DiagnosisOccurrence'
                when 'AB_DPG'             then 'OccurrenceNumber'
                when 'AB_Intervention'    then 'IntervOccurrence'
                when 'AB_MIS'             then 'MISOccurrence'
                when 'AB_PatientService'  then 'ServiceOccurrence'
                when 'AB_Project'         then 'ProjectOccurrence'
                when 'AB_Provider'        then 'ProviderOccurrence'
                when 'AB_SpecialCareUnit' then 'SCUOccurrence'
                when 'RH_Diagnosis'       then 'DiagnosisOccurrence'
                when 'RH_Provider'        then 'ProviderOccurrence'
                when 'RH_Intervention'    then 'IntervOccurrence'
                when 'RH_ServiceInterupt' then 'InteruptOccurrence'
                when 'CRS_Signature'      then 'ProviderOccurrence'
                when 'AB_SpecialProjects' then 'OccurrenceNumber'	--ED - 02385
            end + ' = ' + convert( varchar(3), @Occurrence ) + ' '
            else ''
        end
    )
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  function [dbo].[SUF_GetFromClauseSegment] 
(
    @ExecutionID int        ,
    @GroupID     int        ,
    @SubGroupID  int        ,
    @TableName   varchar(50),
    @TableAlias  varchar(50),
    @FieldName   varchar(50),
    @FieldAlias  varchar(50),
    @FieldID     int        ,
    @ICDType     char(1)    ,
    @LookUpTable varchar(50),
    @DataType    int        ,
    @Occurrence  int        
) 
returns varchar(300)
begin

    declare @result varchar(300)

    if @DataType in (21, 26, 31, 32, 50, 51, 56, 54, 60, 61, 62, 63, 71, 72, 73, 74)
        set @result = 
            case 
            when (@DataType in (21, 50, 51, 56, 62, 63, 71, 72, 73, 74))
                then 'left join ' + @LookUpTable + ' ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
            
            when (@DataType in (26, 31, 32))
                then 'left join LU_CodeFile ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
            
            when (@DataType = 54)
                then case @LookUpTable                
                    when 'LLU_ProviderServices'  then 'left join LU_ProviderService '    + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'LLU_ProviderSpecialty' then 'left join LU_ProviderSpecialty '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                end                        
    
            when (@DataType = 60) 
                then case @ICDType 
                    when '9' then 'left join ICD_DiagFile9 '   + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'C' then 'left join ICD_DiagFile9cm ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'T' then 'left join ICD_DiagFile10 '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                end
            when (@DataType = 61) 
                then case @ICDType
                    when '9' then 'left join ICD_ProcFile9 '   + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'C' then 'left join ICD_ProcFile9cm ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'T' then 'left join ICD_ProcFile10 '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                end
    
            else ''
            end 
    else
        set @result = ''

    return (@result)
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_GetIAR_AssessmentType] 
(
    @AbstractID int
) 
returns varchar(50)
begin
    declare @IAR_CCIM_IsSubmitted varchar(50)
    declare @IsDeleted int
    declare @X2 varchar(50)
    
    set @IAR_CCIM_IsSubmitted = (select top 1 l.CIHIValue from dbo.OMHRS_IAR_CCIM z inner join lu_codeFile l
            on  z.IAR_CCIM_IsSubmitted = l.code where z.AbstractID = @AbstractID)
    set @IsDeleted = (select top 1 IsDeleted from dbo.OMHRS_RecInfo where AbstractID = @AbstractID)
    
    set @X2 = 'N'
    if @IAR_CCIM_IsSubmitted = 'Y' 
    begin
        if @IsDeleted in (1, 3)
            set @X2 = 'D'
        else
            set @X2 = 'C'
    end 
    
    return (@X2)
    
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_GetPatientAddress](
    @ChartNumber varchar(10), 
    @HospitalID  int
) returns char(267)
as 
begin
    -- This is IAR submission to get patient address
    declare @FullAddress char(267)
    declare @Address1 varchar(60)
    declare @Address2 varchar(60)
    declare @PostalCode varchar(10)
    declare @Country varchar(30)
    declare @City varchar(30)
    declare @Province varchar(30)
    declare @Phone varchar(20)
    declare @HealthCareNumber varchar(12)
    declare @HCNVersion varchar(2)
    declare	@HCNProvince varchar(3)
    declare @Sex varchar(1)
    declare @Birthdate varchar(8)
    declare @EstimatedBirthdate varchar(1)
    
    if @HospitalID is null set @HospitalID = 0
    
    if  @HospitalID > 0 
        select top 1
            @Address1 = isnull(PrimaryAddress, ''),
            @Address2 = isnull(PrimaryAddress2, ''),
            @Phone = isnull(left(PrimaryPhone, 20), ''),
            @City = isnull(City, ''),
            @Province = left(isnull(p.DisplayCode, ''), 30),
            @Country = left(isnull(c.DisplayCode, ''), 30),
            @PostalCode = left(isnull(cm.PostalCode, ''), 10),
            @HealthCareNumber = left(isnull(cm.HealthCareNumber, ''), 12),
            @HCNVersion  = left(isnull(cm.HCNVersion, ''), 2),
            @HCNProvince = (case when isnull(h.CIHIValue, '') in ('99', '') then '-90' else left(isnull(h.CIHIValue, ''), 3) end),
            @Sex = left(isnull(g.CIHIValue, ''), 1),
            @Birthdate = left(isnull(convert(varchar(20), cm.Birthdate, 112), ''), 8),
            @EstimatedBirthdate = (case when isnull(e.CIHIValue, '') in ('Y', '1') then '1' else '0' end)
        from CM_CPI cm
            left join LU_CPI_Country c on cm.Country = c.code
            left join LU_CPI_Province p on cm.Province = p.code
            left join LU_Gender_I g on cm.Gender = g.code
            left join LU_HCNProvince h on cm.HCNProvince = h.code
            left join LU_YesBlankAlpha e on cm.BirthDateEstimated = e.code
        where ChartNumber = @ChartNumber and 
            isnull(cm.HospitalID, 0) in (0, @HospitalID)
        order by cm.HospitalID desc
    else
        select top 1
            @Address1 = isnull(PrimaryAddress, ''),
            @Address2 = isnull(PrimaryAddress2, ''),
            @Phone = isnull(left(PrimaryPhone, 20), ''),
            @City = isnull(City, ''),
            @Province = left(isnull(p.DisplayCode, ''), 30),
            @Country = left(isnull(c.DisplayCode, ''), 30),
            @PostalCode = left(isnull(cm.PostalCode, ''), 10),
            @HealthCareNumber = left(isnull(cm.HealthCareNumber, ''), 12),
            @HCNVersion  = left(isnull(cm.HCNVersion, ''), 2),
            @HCNProvince = (case when isnull(h.CIHIValue, '') in ('99', '') then '-90' else left(isnull(h.CIHIValue, ''), 3) end),
            @Sex = left(isnull(g.CIHIValue, ''), 1),
            @Birthdate = left(isnull(convert(varchar(20), cm.Birthdate, 112), ''), 8),
            @EstimatedBirthdate = (case when isnull(e.CIHIValue, '') in ('Y', '1') then '1' else '0' end)
            
        from CM_CPI cm
            left join LU_CPI_Country c on cm.Country = c.code
            left join LU_CPI_Province p on cm.Province = p.code
            left join LU_Gender_I g on cm.Gender = g.code
            left join LU_HCNProvince h on cm.HCNProvince = h.code
            left join LU_YesBlankAlpha e on cm.BirthDateEstimated = e.code
        where ChartNumber = @ChartNumber 
        order by cm.HospitalID 
    
    if isnull(@HealthCareNumber, '') = '' set @HCNVersion = ''   
    if isnull(@EstimatedBirthdate, '') = '' set @EstimatedBirthdate = '0'
    
    set @FullAddress = cast(@Address1 as char(60)) + cast(@Address2 as char(60)) + cast(@City as char(30)) + cast(@Province as char(30)) + 
            cast(@Country as char(30)) + cast(@PostalCode as char(10)) + cast(@Phone as char(20)) +
            cast(@HealthCareNumber as char(12)) + cast(@HCNVersion as char(2)) + cast(@HCNProvince as char(3)) +
            cast(@Sex as char(1)) + cast(@Birthdate as char(8)) + cast(@EstimatedBirthdate as char(1))

    return @FullAddress
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  function [dbo].[SUF_GetPeriodDateFr] (
    @cihi_year int,
    @cihi_period int
) returns varchar(10) as 
begin
declare @result datetime
declare @year int, @month int

--2.x-0213 disable BC period for Rehab, CCRS
-- province of BC
if exists ( select * from H_HospitalProfile where ProvinceNumber = 14 ) and
     @cihi_period not in (15401,15402,15403,15404,15405,15651,15652,15653, 15654,15655,15656,15657,15658,15659,15660,15661,15662) 
begin

    if @cihi_period = 1 
        select @result = cast(str(@cihi_year, 4) + '/04/01' as datetime)
    else
        select @result = CIHIDateFrom from LU_BatchPeriod where Code = @cihi_period

end

--Rehab and CCRS Ticket 8525
else if @cihi_period in (15401,15402,15403,15404,15405,15651,15652,15653,
        15654,15655,15656,15657,15658,15659,15660,15661,15662) begin
    if @cihi_period in (15404, 15660, 15661, 15662)
        set @year = @cihi_year + 1
    else 
        set @year = @cihi_year

    set @month = (case @cihi_period when 15401 then 4    -- Quarter 1
                                    when 15402 then 7    -- Quarter 2
                                    when 15403 then 10   -- Quarter 3
                                    when 15404 then 1    -- Quarter 4
                                    when 15405 then 4    -- Full Year

                                    when 15651 then 4    -- April
                                    when 15652 then 5    -- May
                                    when 15653 then 6    -- June
                                    when 15654 then 7    -- July
                                    when 15655 then 8    -- August
                                    when 15656 then 9    -- Septemper
                                    when 15657 then 10   -- October
                                    when 15658 then 11   -- November
                                    when 15659 then 12   -- December
                                    when 15660 then 1    -- January
                                    when 15661 then 2    -- Feburary
                                    when 15662 then 3    -- March
                  end)
    set @result = 
        cast(
        cast(
            (@year * 10000) + 
            (@month * 100) + 1
        as varchar(8))
        as datetime)end
   
-- all other provinces
else begin

    if(@cihi_period > 9) 
        set @year = @cihi_year + 1
    else 
        set @year = @cihi_year

    if(@cihi_period > 9) 
        set @month = @cihi_period - 9
    else 
        set @month = @cihi_period + 3
    
    set @result = 
        cast(
        cast(
            (@year * 10000) + 
            (@month * 100) + 1
        as varchar(8))
        as datetime)
end

return convert(varchar(10), @result, 111)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





create function dbo.SUF_GetPeriodDateRange (
    @prov_no int,
    @cihi_pr int,
    @date_to datetime,
    @get_end bit
) returns varchar(10) as 
begin

    declare @ret varchar(10)
    
    -- province of BC
    if (@prov_no = 14) begin
    
        if (@get_end = 1) 
            select @date_to = CIHIDateTo from LU_BatchPeriod where Code = @cihi_pr
        else
            select @date_to = CIHIDateFrom from LU_BatchPeriod where Code = @cihi_pr
    end
    
    -- all other provinces
    else begin
    
        set @date_to = 
            cast(
            cast(
                (year(@date_to) * 10000) + 
                (month(@date_to) * 100) + 1
            as varchar(8))
            as datetime)
    
        if (@get_end = 1) 
            set @date_to = 
                dateadd(dd, -1, 
                dateadd(mm,  1, 
                    cast(@date_to as datetime)
                ))
    
    end
    
    set @ret = convert(varchar(10), @date_to, 111)
    
    return @ret
end






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  function [dbo].[SUF_GetPeriodDateTo](
    @cihi_year int,
    @cihi_period int
) returns varchar(10) as 
begin
declare @result datetime
declare @year int, @month int

--2.x-0213 disable BC period for Rehab, CCRS
-- province of BC
if exists ( select * from H_HospitalProfile where ProvinceNumber = 14 ) and
     @cihi_period not in (15401,15402,15403,15404,15405,15651,15652,15653, 15654,15655,15656,15657,15658,15659,15660,15661,15662) 
    select @result = CIHIDateTo from LU_BatchPeriod where Code = @cihi_period
    
--Rehab and CCRS Ticket 8525
else if @cihi_period in (15401,15402,15403,15404,15405,15651,15652,15653,
        15654,15655,15656,15657,15658,15659,15660,15661,15662) begin
    if @cihi_period in (15403, 15404, 15405, 15659, 15660, 15661, 15662)
        set @year = @cihi_year + 1
    else 
        set @year = @cihi_year

    set @month = (case @cihi_period when 15401 then 7    -- Quarter 1
                                    when 15402 then 10   -- Quarter 2 (ticket 13065 before 11)
                                    when 15403 then 1    -- Quarter 3
                                    when 15404 then 4    -- Quarter 4
                                    when 15405 then 4    -- Full Year

                                    when 15651 then 5    -- April
                                    when 15652 then 6    -- May
                                    when 15653 then 7    -- June
                                    when 15654 then 8    -- July
                                    when 15655 then 9    -- August
                                    when 15656 then 10   -- Septemper
                                    when 15657 then 11   -- October
                                    when 15658 then 12   -- November
                                    when 15659 then 1    -- December
                                    when 15660 then 2    -- January
                                    when 15661 then 3    -- Feburary
                                    when 15662 then 4    -- March
                  end)
    set @result = 
        cast(
        cast(
            (@year * 10000) + 
            (@month * 100) + 1
        as varchar(8))
        as datetime)
    set @result = dateadd( day, -1, @result )
end
-- all other provinces
else begin
    set @result = dbo.SUF_GetPeriodDateFr( @cihi_year, @cihi_period )
    set @result = dateadd( month, 1, @result )
    set @result = dateadd( day, -1, @result )
end

return convert(varchar(10), @result, 111)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  function [dbo].[SUF_GetProvinceCode] ( 
    @InstNoCIHI varchar(5)
) returns varchar(2)
as
begin

    return (
        case left(@InstNoCIHI, 1)
            when 'I' then ''
            when 'N' then 'NT'
            when 'V' then 'NU'
            when 'Y' then 'YT'
            when '0' then 'NL'  --Replace NF with NL 2009/08/18. Not sure it is ok.
            when '1' then 'PE'
            when '2' then 'NS'
            when '3' then 'NB'
            when '4' then 'QC'
            when '5' then 'ON'
            when '6' then 'MB'
            when '7' then 'SK'
            when '8' then 'AB'
            when '9' then 'BC'
            when 'M' then 'ON' -- Med2020 only
            else ''
        end
    )

end









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function [dbo].[SUF_GetQuery]
(
  @ExecutionID int
, @GroupID int
, @SubGroupID int 
)
returns @Query table 
( 
  SegmentType int
, OrderBy int
, SqlSegment varchar(1000)
)
as 
begin

    declare  
      @AppID      int 
    , @BaseTable  varchar(50) 
     
    select 
      @AppID      = AppID 
    from SU_ExecProperties 
    where ExecutionID = @ExecutionID 
     
    
    --_______________________________________________________________________________________________________________ 
    -- set the base table to be used for all queries 
    if (@AppID in (650, 651, 652, 653, 654, 655))  
        set @BaseTable = 'OMHRS_SecAA' 
    else if (@AppID in (9999470, 470, 471, 472, 473, 474, 475, 476, 477))  
        set @BaseTable = 'CRS_Demographic' 
    else if (@AppID in (250, 251, 252))  
    --else if (@GroupID in (20, 21, 22, 23, 121, 122, 123))  
        set @BaseTable = 'RH_ClientInfo' 
    else if (@AppID in (40000, 40001))  
        set @BaseTable = 'CJRR_Abstract'    --ED - 02444
    else  
        set @BaseTable = 'AB_Abstract' 
     
    --_______________________________________________________________________________________________________________ 
    -- define the select clause for each query  
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select distinct 1, 1, 'select Med2020AbstractID = RecordSelect.AbstractID ' 
    from SU_StructureFile  
    where GroupID = @GroupID and SubGroupID = @SubGroupID 
     
    --_______________________________________________________________________________________________________________ 
    -- define the base from clause common to each query  
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select distinct  
      OrderBy     = 1 
    , SegmentType = 2 
    , SqlSegment  = 
        'from SU_ExecProperties SuProperties ' +  
        'join SU_RecordSelect RecordSelect on SuProperties.ExecutionID = RecordSelect.ExecutionID ' + 
        'join ' + dbo.SUF_GetTempTable ( @ExecutionID, @BaseTable ) + ' ' + @BaseTable + ' ' + 
        'on RecordSelect.AbstractID = ' + @BaseTable + '.AbstractID ' 
    from SU_StructureFile  
    where GroupID = @GroupID and SubGroupID = @SubGroupID 
     
    --_______________________________________________________________________________________________________________ 
    -- define the sub tables joined to the base from clause (this differs per query) 
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select distinct  
      OrderBy     = 2 
    , SegmentType = 2 
    , SqlSegment  = dbo.SUF_GetSqlBaseTbl ( @ExecutionID, GroupID, SubGroupID, Occurrence ) 
    from SU_StructureFile  
    where GroupID = @GroupID and SubGroupID = @SubGroupID 
    and isnull(TableName, '') not in ('', @BaseTable) 
     
    --_______________________________________________________________________________________________________________ 
    -- dynamic select and from clauses based on SU_StructureFile 
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select  
          OrderBy     = TextPosition + 10
        , SegmentType = 1 
        , SqlSegment  = ', ' + dbo.SUF_GetSqlSelect ( @ExecutionID, GroupID, SubGroupID, TextPosition ) 
    from SU_StructureFile  
    where GroupID = @GroupID and SubGroupID = @SubGroupID 
    order by SubGroupID, TextPosition, Occurrence 
     
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select  
          OrderBy     = TextPosition + 10
        , SegmentType = 2 
        , SqlSegment  = dbo.SUF_GetSqlFrom ( @ExecutionID, GroupID, SubGroupID, TextPosition ) 
    from SU_StructureFile 
    where GroupID = @GroupID and SubGroupID = @SubGroupID 
    order by SubGroupID, TextPosition, Occurrence 
     
    --_______________________________________________________________________________________________________________ 
    -- static where clause and order by clause with difference only in the @ExecutionID 
     
    insert @Query ( OrderBy, SegmentType, SqlSegment ) 
    select distinct 
          OrderBy     = (select max(OrderBy) + 1 from @Query) 
        , SegmentType = 2 
        , SqlSegment  = 'where RecordSelect.ExecutionID = ' + convert( varchar(10), @ExecutionID ) + ' ' + 'order by RecordSelect.OrderBy ' 
    from SU_StructureFile s 
    where GroupID = @GroupID and SubGroupID = @SubGroupID 
    
    if @GroupID = 99990470
    begin
        delete from @Query
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(00, 1, 'select * from ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(01, 1, '(select Med2020AbstractID = RecordSelect.AbstractID ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(02, 1, ', Z1 = Z1.CIHIValue')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(03, 1, ', RecordDate = dbo.SUF_CRS_Main_DateField ( CRS_Demographic.Z1, CRS_Demographic.AdmitDate, CRS_Demographic.AB1b, CRS_Demographic.A3, CRS_Demographic.Z7, CRS_Demographic.DischargeDate, CRS_Demographic.Z12, CRS_Demographic.Z13 )')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(04, 1, ', AA1 = CRS_Demographic.AA1')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(05, 1, ', Y3 = Y3.CIHIValue')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(06, 1, ',RecordSelect.OrderBy')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(07, 1, 'from SU_ExecProperties SuProperties ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(08, 1, 'join SU_RecordSelect RecordSelect on SuProperties.ExecutionID = RecordSelect.ExecutionID ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(09, 1, 'join ' + dbo.SUF_GetTempTable ( @ExecutionID, @BaseTable ) + ' CRS_Demographic on RecordSelect.AbstractID = CRS_Demographic.AbstractID ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(10, 1, 'left join LU_CodeFile Z1 on CRS_Demographic.Z1 = Z1.Code ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(11, 1, 'left join LU_CodeFile Y3 on CRS_Demographic.Y3 = Y3.Code ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(12, 1, 'where RecordSelect.ExecutionID = ' + cast(@ExecutionID as varchar(10)) )
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(13, 1, 'union all ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(14, 1, 'select Med2020AbstractID = RecordSelect.AbstractID ')
        --EN - 00651
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(15, 1, ', Z1 = ''MD''')
        --insert @Query ( OrderBy, SegmentType, SqlSegment ) values(15, 1, ', Z1 = (case when dbo.SUF_CRS_Main_DateField ( CRS_Demographic.Z1, CRS_Demographic.AdmitDate, CRS_Demographic.AB1b, CRS_Demographic.A3, CRS_Demographic.Z7, CRS_Demographic.DischargeDate, CRS_Demographic.Z12, CRS_Demographic.Z13 ) < ''2012/04/01'' then ''14'' else ''MD'' end) ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(16, 1, ', RecordDate = dbo.SUF_CRS_Main_DateField ( CRS_Demographic.Z1, CRS_Demographic.AdmitDate, CRS_Demographic.AB1b, CRS_Demographic.A3, CRS_Demographic.Z7, CRS_Demographic.DischargeDate, CRS_Demographic.Z12, CRS_Demographic.Z13 )')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(17, 1, ', AA1 = CRS_Demographic.AA1')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(18, 1, ', Y3 = Y3.CIHIValue')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(19, 1, ', RecordSelect.OrderBy')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(20, 1, 'from SU_ExecProperties SuProperties ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(21, 1, 'join SU_RecordSelect RecordSelect on SuProperties.ExecutionID = RecordSelect.ExecutionID ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(22, 1, 'join ' + dbo.SUF_GetTempTable ( @ExecutionID, @BaseTable ) + ' CRS_Demographic on RecordSelect.AbstractID = CRS_Demographic.AbstractID ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(23, 1, 'join dbo.CRS_Medication on CRS_Demographic.AbstractID = CRS_Medication.AbstractID and CRS_Demographic.Z1 in (15353, 15354, 18540, 18541)')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(24, 1, 'left join LU_CodeFile Z1 on CRS_Demographic.Z1 = Z1.Code ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(25, 1, 'left join LU_CodeFile Y3 on CRS_Demographic.Y3 = Y3.Code ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(26, 1, 'where RecordSelect.ExecutionID = ' + cast(@ExecutionID as varchar(10)) )
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(27, 1, 'union all ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(28, 1, 'select Med2020AbstractID = RecordSelect.AbstractID ')
        --EN -00651
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(29, 1, ', Z1 = ''SP'' ')
        --insert @Query ( OrderBy, SegmentType, SqlSegment ) values(29, 1, ', Z1 = (case when dbo.SUF_CRS_Main_DateField ( CRS_Demographic.Z1, CRS_Demographic.AdmitDate, CRS_Demographic.AB1b, CRS_Demographic.A3, CRS_Demographic.Z7, CRS_Demographic.DischargeDate, CRS_Demographic.Z12, CRS_Demographic.Z13 ) < ''2012/04/01'' then ''18'' else ''SP'' end) ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(30, 1, ', RecordDate = dbo.SUF_CRS_Main_DateField ( CRS_Demographic.Z1, CRS_Demographic.AdmitDate, CRS_Demographic.AB1b, CRS_Demographic.A3, CRS_Demographic.Z7, CRS_Demographic.DischargeDate, CRS_Demographic.Z12, CRS_Demographic.Z13 )')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(31, 1, ', AA1 = CRS_Demographic.AA1')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(32, 1, ', Y3 = Y3.CIHIValue')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(33, 1, ', RecordSelect.OrderBy')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(34, 1, 'from SU_ExecProperties SuProperties ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(35, 1, 'join SU_RecordSelect RecordSelect on SuProperties.ExecutionID = RecordSelect.ExecutionID ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(36, 1, 'join ' + dbo.SUF_GetTempTable ( @ExecutionID, @BaseTable ) + ' CRS_Demographic on RecordSelect.AbstractID = CRS_Demographic.AbstractID ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(37, 1, 'join dbo.AB_SpecialProjects on CRS_Demographic.AbstractID = AB_SpecialProjects.AbstractID and AB_SpecialProjects.CareType = 4 ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(38, 1, 'left join LU_CodeFile Z1 on CRS_Demographic.Z1 = Z1.Code ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(39, 1, 'left join LU_CodeFile Y3 on CRS_Demographic.Y3 = Y3.Code ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(40, 1, 'where RecordSelect.ExecutionID = ' + cast(@ExecutionID as varchar(10)) )
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(41, 1, ') a ')
        insert @Query ( OrderBy, SegmentType, SqlSegment ) values(42, 1, 'order by OrderBy, (case when z1 = ''QA'' then ''MA'' else z1 end) ')
    end
    return 
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function SUF_GetRecodeSegment (
    @RecodeID    int        ,
    @ExecutionID int        ,
    @GroupID     int        ,
    @SubGroupID  int        ,
    @TableName   varchar(50),
    @TableAlias  varchar(50),
    @FieldName   varchar(50),
    @FieldAlias  varchar(50),
    @FieldID     int        ,
    @ICDType     char(1)    ,
    @LookUpTable varchar(50),
    @DataType    int        ,
    @Occurrence  int        
) returns varchar(1000)
begin

/* 
    ESCAPE SEQUENCE FOR EACH 
    VARIABLE ARE AS FOLLOWS:

    \x = @ExecutionID
    \g = @GroupID
    \s = @SubGroupID
    \t = @TableName 
    \a = @TableAlias
    \c = @FieldName
    \f = @FieldAlias
    \u = @FieldID
    \d = @DataType
    \o = @Occurrence
    \l = @LookUpTable
    \i = @ICDType
*/
    return ( 

        select 
            replace (
            replace (
            replace (
            replace (
            replace (
            replace (
            replace (
            replace (
            replace (
            replace (
            replace (
            replace (
    
                RecodeText 
            
            , '\x', convert( varchar(10), isnull( @ExecutionID, 0 ) ) )
            , '\g', convert( varchar(10), isnull( @GroupID    , 0 ) ) )
            , '\s', convert( varchar(10), isnull( @SubGroupID , 0 ) ) )
            , '\t',                       isnull( @TableName  , '') )
            , '\a',                       isnull( @TableAlias , '') )
            , '\c',                       isnull( @FieldName  , '') )
            , '\f',                       isnull( @FieldAlias , '') )
            , '\u', convert( varchar(10), isnull( @FieldID    , 0 ) ) )
            , '\d', convert( varchar(10), isnull( @DataType   , 0 ) ) )
            , '\o', convert( varchar(10), isnull( @Occurrence , 0 ) ) )
            , '\l',                       isnull( @LookUpTable, '') )
            , '\i',                       isnull( @ICDType    , '') )

        from SU_StructureRecode where RecodeID = @RecodeID
    )

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_GetRecodeText]  
( 
  @ExecutionID  int         
, @GroupID      int         
, @SubGroupID   int         
, @TextPosition int         
, @RecodeID     int         
)  
returns varchar(1000) 
begin 
    /*  
    ESCAPE SEQUENCE FOR EACH  
    VARIABLE ARE AS FOLLOWS: 
     
    \x = @ExecutionID 
    \g = @GroupID 
    \s = @SubGroupID 
    \t = @TableName  
    \a = @TableAlias 
    \c = @FieldName 
    \f = @FieldAlias 
    \u = @FieldID 
    \d = @DataType 
    \o = @Occurrence 
    \l = @LookUpTable 
    \i = @ICDType 
    \v = @SU_Type   --EN - 00418
    */ 
 
    declare  
      @result      varchar(1000) 
    , @AppID       int 
    , @TableName   varchar(50) 
    , @TableAlias  varchar(50) 
    , @FieldName   varchar(50) 
    , @FieldAlias  varchar(50) 
    , @FieldID     int 
    , @ICDType     char(1) 
    , @LookUpTable varchar(50) 
    , @DataType    int 
    , @Occurrence  int 
    , @SU_Type     int

    select  
      @AppID   = AppID 
    , @ICDType = ICDType
    , @SU_Type = Su_Type 
    from SU_ExecProperties 
    where ExecutionID = @ExecutionID 
 
    select  
      @TableName  = TableName 
    , @FieldName  = FieldName 
    , @FieldID    = FieldID 
    , @Occurrence = Occurrence 
    from SU_StructureFile 
    where GroupID = @GroupID  
    and SubGroupID = @SubGroupID 
    and TextPosition = @TextPosition 
 
    select  
      @DataType    = DataType 
    , @LookUpTable = LookUpTable 
    from U_UsersFields 
    where UserID = 1 
    and FieldID = @FieldID 
    and AppID = @AppID 
 
    set @TableAlias = dbo.SUF_GetTableAlias( @TableName, @Occurrence ) 
    set @TableName  = dbo.SUF_GetTempTable ( @ExecutionID, @TableName ) 
    set @FieldAlias = dbo.SUF_GetTableAlias( @FieldName, @Occurrence )  
 
    select @result =  
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
        replace ( 
 
            RecodeText  
         
        , '\x', convert( varchar(10), isnull( @ExecutionID, 0 ) ) ) 
        , '\g', convert( varchar(10), isnull( @GroupID    , 0 ) ) ) 
        , '\s', convert( varchar(10), isnull( @SubGroupID , 0 ) ) ) 
        , '\t',                       isnull( @TableName  , '') ) 
        , '\a',                       isnull( @TableAlias , '') ) 
        , '\c',                       isnull( @FieldName  , '') ) 
        , '\f',                       isnull( @FieldAlias , '') ) 
        , '\u', convert( varchar(10), isnull( @FieldID    , 0 ) ) ) 
        , '\d', convert( varchar(10), isnull( @DataType   , 0 ) ) ) 
        , '\o', convert( varchar(10), isnull( @Occurrence , 0 ) ) ) 
        , '\l',                       isnull( @LookUpTable, '') ) 
        , '\i',                       isnull( @ICDType    , '') ) 
        , '\v',                       isnull( @SU_Type    , 0) ) 
    from SU_StructureRecode where RecodeID = @RecodeID 
 
    return ( @result ) 
end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_GetSelectClauseSegment] (
    @ExecutionID int        ,
    @GroupID     int        ,
    @SubGroupID  int        ,
    @TableName   varchar(50),
    @TableAlias  varchar(50),
    @FieldName   varchar(50),
    @FieldAlias  varchar(50),
    @FieldID     int        ,
    @ICDType     char(1)    ,
    @LookUpTable varchar(50),
    @DataType    int        ,
    @Occurrence  int        ,
    @RecodeID    int        ,
    @NoComma     bit        
) returns varchar(300)
begin

    --------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    ----  CREATE THE SELECT CLAUSE SEGMENT -----------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    return (
        case 
            when (@RecodeID <> 0)
                -- fetch the recode
                then dbo.SUF_GetRecodeSegment( @RecodeID, @ExecutionID, @GroupID, @SubGroupID, @TableName, @TableAlias, @FieldName, @FieldAlias, @FieldID, @ICDType, @LookUpTable, @DataType, @Occurrence )

            when (@DataType in (0, 2, 3, 6, 9, 10, 12, 17, 20, 52))
                -- FieldAlias = TableAlias.FieldName
                then @FieldAlias + ' = ' + @TableAlias + '.' + @FieldName 

            when (@DataType in (21, 26, 31, 32, 50, 51, 54, 56, 60, 61, 62, 63, 71, 72, 73, 74))
                -- FieldAlias = LookUpTable.CIHIValue
                then  @FieldAlias + ' = ' + @FieldAlias + '.CIHIValue' 

            when (@DataType = 11) 
                -- FieldAlias = 'YYYY/MM/DD'
                then @FieldAlias + ' = convert( varchar(10), ' + @TableAlias + '.' + @FieldName + ', 111)'

            when (@DataType = 15)
                -- FieldAlias = 'HH:MM:SS'
                then @FieldAlias + ' = convert( varchar(8), ' + @TableAlias + '.' + @FieldName + ', 108)'

            when (@DataType is not null)
                -- FieldAlias = DataType: X
                then @FieldAlias + ' = DataType: ''' + convert( varchar(10), @DataType ) + ''''

                -- FieldAlias = (Default clause)
            else @FieldAlias + ' = ''@@@@@@@@@@'''

        end + case when (@NoComma = 1) then ' ' else ', ' end -- add comma when applicable
    )

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_GetSqlBaseTbl] (
      @ExecutionID int
    , @GroupID     int
    , @SubGroupID  int
    , @Occurrence  int
) returns varchar(1000)
begin

    declare @result varchar(1000)

    declare 
      @TableName  varchar(50)
    , @TableAlias varchar(50)
    , @TempTable  varchar(50)

    select top 1 
      @TableName  = TableName 
    , @TableAlias = dbo.SUF_GetTableAlias( TableName, @Occurrence )
    , @TempTable  = dbo.SUF_GetTempTable ( @ExecutionID, TableName )
    from SU_StructureFile 
    where GroupID = @GroupID 
    and SubGroupID = @SubGroupID 
    order by textposition
    set @result = 'left join ' + @TempTable + ' ' + @TableAlias + ' on RecordSelect.AbstractID = ' + @TableAlias + '.AbstractID '

    if (@Occurrence > 0) 
        set @result = @result + 'and ' + @TableAlias + '.' + 
            case @TableName
            when 'AB_CMG'              then 'OccurrenceNumber'
            when 'AB_Diagnosis'        then 'DiagnosisOccurrence'
            when 'AB_DPG'              then 'OccurrenceNumber'
            when 'AB_Intervention'     then 'IntervOccurrence'
            when 'AB_MIS'              then 'MISOccurrence'
            when 'AB_PatientService'   then 'ServiceOccurrence'
            when 'AB_Project'          then 'ProjectOccurrence'
            when 'AB_Provider'         then 'ProviderOccurrence'
            when 'AB_SpecialCareUnit'  then 'SCUOccurrence'
            when 'RH_Diagnosis'        then 'DiagnosisOccurrence'
            when 'RH_Provider'         then 'ProviderOccurrence'
            when 'RH_Intervention'     then 'IntervOccurrence'
            when 'RH_ServiceInterupt'  then 'InteruptOccurrence'
            when 'CRS_Signature'       then 'ProviderOccurrence'
            when 'AB_SpecialProjects'  then 'OccurrenceNumber'	--EN - 02385
            else 'Occurrence'
            end 
            + ' = ' + convert( varchar(3), @Occurrence ) + ' '

    return ( @result )
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_GetSqlFrom] 
( 
  @ExecutionID  int 
, @GroupID      int 
, @SubGroupID   int 
, @TextPosition int 
)  
returns varchar(1000) 
begin 
    declare @result varchar(1000) 
 
    declare 
      @AppID       int 
    , @ICDType     char(1) 
    , @TableName   varchar(50) 
    , @FieldName   varchar(50) 
    , @FieldID     int 
    , @Occurrence  int 
    , @DataType    int 
    , @LookUpTable varchar(50) 
    , @TableAlias  varchar(50) 
    , @FieldAlias  varchar(50) 
 
    select
      @AppID   = AppID 
    , @ICDType = ICDType 
    from SU_ExecProperties 
    where ExecutionID = @ExecutionID

    select  
      @TableName  = TableName 
    , @FieldName  = FieldName 
    , @FieldID    = FieldID 
    , @Occurrence = Occurrence 
    from SU_StructureFile 
    where GroupID = @GroupID  
    and SubGroupID = @SubGroupID  
    and TextPosition = @TextPosition
 
    select  
      @DataType    = u.DataType 
    , @LookUpTable = u.LookUpTable 
    from U_UsersFields u  
    where u.UserID = 1  
    and u.FieldID = @FieldID 
    and u.AppID = @AppID 
 
    if @DataType in (21, 26, 31, 32, 50, 51, 56, 54, 60, 61, 62, 63, 71, 72, 73, 74) begin 
 
         
        set @TableAlias = dbo.SUF_GetTableAlias( @TableName, @Occurrence ) 
        set @TableName = dbo.SUF_GetTempTable ( @ExecutionID, @TableName ) 
        set @FieldAlias = dbo.SUF_GetTableAlias( @FieldName, @Occurrence )  
     
        set @result =  
            case  
            when (@DataType in (21, 50, 51, 56, 62, 63, 71, 72, 73, 74)) 
                then 'left join ' + @LookUpTable + ' ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
             
            when (@DataType in (26, 31, 32)) 
                then 'left join LU_CodeFile ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
             
            when (@DataType = 54) 
                then case @LookUpTable                 
                    when 'LLU_ProviderServices'  then 'left join LU_ProviderService '    + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                    when 'LLU_ProviderSpecialty' then 'left join LU_ProviderSpecialty '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                end                         
     
            when (@DataType = 60)  
                then case @ICDType  
                    when '9' then 'left join ICD_DiagFile9 '   + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                    when 'C' then 'left join ICD_DiagFile9cm ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                    when 'T' then 'left join ICD_DiagFile10 '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                end 
            when (@DataType = 61)  
                then case @ICDType 
                    when '9' then 'left join ICD_ProcFile9 '   + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                    when 'C' then 'left join ICD_ProcFile9cm ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                    when 'T' then 'left join ICD_ProcFile10 '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code '  
                end 
     
            else '' 
            end  
    end     
    else begin 
        set @result = '' 
    end 
 
    return (@result) 
end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE function [dbo].[SUF_GetSqlSelect]  
( 
  @ExecutionID  int 
, @GroupID      int 
, @SubGroupID   int 
, @TextPosition int 
)  
returns varchar(1000) 
begin 
 
    declare @result varchar(1000) 

    declare 
      @TableName   varchar(50) 
    , @TableAlias  varchar(50) 
    , @FieldName   varchar(50) 
    , @FieldAlias  varchar(50) 
    , @FieldID     int 
    , @ICDType     char(1) 
    , @LookUpTable varchar(50) 
    , @DataType    int 
    , @Occurrence  int 
    , @RecodeID    int 
    , @AppID       int 
 
 
    select  
      @AppID   = AppID 
    , @ICDType = ICDType 
    from SU_ExecProperties 
    where ExecutionID = @ExecutionID 
    
    select  
      @TableName  = TableName 
    , @FieldName  = FieldName 
    , @FieldID    = FieldID 
    , @Occurrence = Occurrence 
    , @RecodeID   = RecodeID 
    from SU_StructureFile
    where GroupID = @GroupID  
    and SubGroupID = @SubGroupID 
    and TextPosition = @TextPosition
    
    select  
      @DataType    = DataType 
    , @LookUpTable = LookUpTable 
    from U_UsersFields 
    where UserID = 1
    and AppID = @AppID
    and FieldID = @FieldID
 
    set @TableAlias = dbo.SUF_GetTableAlias( @TableName, @Occurrence ) 
    set @TableName = dbo.SUF_GetTempTable ( @ExecutionID, @TableName ) 
    set @FieldAlias = dbo.SUF_GetTableAlias( @FieldName, @Occurrence )  
 
    set @result =  
        case  
        when (@RecodeID <> 0) 
            -- fetch the recode 
            then dbo.SUF_GetRecodeText ( @ExecutionID, @GroupID, @SubGroupID, @TextPosition, @RecodeID ) 
 
        when (@DataType in (0, 1, 2, 3, 6, 9, 10, 12, 17, 20, 52)) 
            -- FieldAlias = TableAlias.FieldName 
            then @FieldAlias + ' = ' + @TableAlias + '.' + @FieldName  
 
        when (@DataType in (21, 26, 31, 32, 50, 51, 54, 56, 60, 61, 62, 63, 71, 72, 73, 74)) 
            -- FieldAlias = LookUpTable.CIHIValue 
            then  @FieldAlias + ' = ' + @FieldAlias + '.CIHIValue'  
 
        when (@DataType in (11, 13))  
            -- FieldAlias = 'YYYY/MM/DD' 
            then @FieldAlias + ' = convert( varchar(10), ' + @TableAlias + '.' + @FieldName + ', 111)' 
 
        when (@DataType = 15) 
            -- FieldAlias = 'HH:MM:SS' 
            then @FieldAlias + ' = convert( varchar(8), ' + @TableAlias + '.' + @FieldName + ', 108)' 
 
        when (@DataType is not null) 
            -- FieldAlias = DataType: X 
            then @FieldAlias + ' = DataType: ''' + convert( varchar(10), @DataType ) + '''' 
 
            -- FieldAlias = (Default clause) 
        else @FieldAlias + ' = ''@@@@@@@@@@''' 
        end  
 
    return ( @result ) 
end 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE function dbo.SUF_GetTableAlias
(
    @TableName  varchar(50)
  , @Occurrence int
) 
returns varchar(50)
begin
    declare @result varchar(50)
    
    set @result = @TableName + isnull( convert( varchar(3), nullif(@Occurrence, 0) ), '')

    return ( @result )
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE function dbo.SUF_GetTempTable 
(
    @ExecutionID int
  , @TableName   varchar(50)
) 
returns varchar(50)
begin
    declare @result varchar(50)
    
    set @result = 'SU' + convert( varchar(10), @ExecutionID ) + @TableName 

    return ( @result )
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE function dbo.SUF_GetULI (
    @AbID int
) returns varchar(9)
as 
begin
    -- This is for Alberta AmCare's "Unique Lifetime Identifier" from Anc-1 to Anc-9 inclusive.
    -- Started 2003.
    declare @ULI varchar(9)

    select @ULI =
        isnull((select left(CIHIValue, 1) from LU_Ancillary1 where Code = AncillaryField1), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary2 where Code = AncillaryField2), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary3 where Code = AncillaryField3), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary4 where Code = AncillaryField4), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary5 where Code = AncillaryField5), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary6 where Code = AncillaryField6), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary7 where Code = AncillaryField7), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary8 where Code = AncillaryField8), ' ') +
        isnull((select left(CIHIValue, 1) from LU_Ancillary9 where Code = AncillaryField9), ' ')
    from AB_Ancillary
    where AbstractID = @AbID

    return @ULI
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SUF_Group_Case_Weight] ( 
    @CareType varchar(20), @BatchYear int, @AbstractID int
) returns varchar(8)
as
begin
    declare @value as varchar(8)
    if @CareType = 1 
        set @value = (select CMG_RIW = left(replace(replace(str(CMG_RIW, 10, 5), '.', ''), ' ', '0'), 8) from AB_CMG where AbstractID = @AbstractID and CMG_Year = @BatchYear and OccurrenceNumber = 1)
    if @CareType = 2
    begin
        if @BatchYear < 2011
            set @value = (select CMG_RIW = left(replace(replace(str(DPG_RIW, 10, 5), '.', ''), ' ', '0'), 8) from AB_DPG where AbstractID = @AbstractID and DPG_Year = @BatchYear and OccurrenceNumber = 1) 
        else
            set @value = (select CMG_RIW = left(replace(replace(str(ACW, 10, 5), '.', ''), ' ', '0'), 8) from AB_CACS where AbstractID = @AbstractID and CACS_Year = @BatchYear and OccurrenceNumber = 1) 
    end
    return ( @value )
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE function [dbo].[SUF_Group_Case_Weight_2012] ( 
    @CareType varchar(20), @BatchYear int, @AbstractID int
) returns varchar(9)
as
begin
    declare @value as varchar(8)
    if @CareType = 1 
        set @value = ltrim((select CMG_RIW = str(CMG_RIW, 9, 4) from AB_CMG where AbstractID = @AbstractID and CMG_Year = @BatchYear and OccurrenceNumber = 1))
    if @CareType = 2
        set @value = ltrim((select CMG_RIW = str(ACW, 9, 4) from AB_CACS where AbstractID = @AbstractID and CACS_Year = @BatchYear and OccurrenceNumber = 1))
    return ( @value )
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE function dbo.SUF_HCNVersion_Concatenate ( 
    @HCNumber varchar(12),
    @HCNVersn varchar(2)
) returns varchar(12)
as 
begin

    if exists (select * from H_HospitalProfile where ProvinceNumber = 10) 
        set @HCNumber = @HCNumber + upper(isnull(space(10 - len(@HCNumber)) + space(2 - len(@HCNVersn)) + @HCNVersn, ''))
    
    return @HCNumber

end






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE function dbo.SUF_Recode_RemoveDecimal 
( 
    @value varchar(5) 
) 
returns varchar(4) as
begin
    declare @number numeric(5,1)
    declare @result varchar(6)



    if isnumeric(@value) = 1 begin
        set @number = cast(@value as numeric(5,1))
        set @result = replace(cast(@number as varchar(6)), '.', '')

        if @number > 999 
            set @result = left(@result,4)
        else
            set @result = right(@result,4)
    end

    return( @result )
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  function [dbo].[SUF_RehabWight] ( 
    @value varchar(10)
) returns varchar(10)
as
begin
    declare @First varchar(10), @Last varchar(10)
    declare @pos int
    
    set @value = ltrim(rtrim(@value))
    set @pos = charindex('.', @value)

    if @pos > 1
    begin
        set @first = left(@value, @pos - 1)
        set @last = isnull(substring(@value, @pos + 1, 3), '000')
        if len(@Last) < 3 set @Last = @last + left('000', 3 - len(@last))
        set @value = right(@First + @Last, 6)
    end
    else
    if isnumeric(@Value) = 1
    begin
        if len(@Value) < 4
            set @Value = @Value + '000'
    end
	
    return ( ltrim(rtrim(@value)) )

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create Function SU_ChartNumberFillLeadingZero(@ChartNumber  varchar(10))
returns varchar(10)
as
begin
    declare @Pos int, @Len int
    
    set @Pos = isnull(patindex('%[0-9]%', @ChartNumber), 0)
    set @Len = len(@ChartNumber)
    
    if @pos = 0
        set @ChartNumber = @ChartNumber + REPLICATE('0', 10 - @Len)
    else if @pos = 1
    begin
        if @len < 10 set @ChartNumber = ' ' + REPLICATE('0', 9 - @Len)  + @ChartNumber 
    end
    else
    begin
        set @ChartNumber = STUFF(@ChartNumber, @Pos, 0, REPLICATE('0', 10 - @Len))
    end
    
    return (upper(@ChartNumber))
end





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE function dbo.SU_FILENAME_ACCS (
    @InstNoCode int,
    @FiscalYear int,
    @FiscalPeriod int,
    @SequenceNo int
) returns varchar(50) as 
begin
    declare @result varchar(50)

    /*
    T-FFFFF-YYYY-MM-SSS.TXT
    T=Record Type - Must be 'M' for ICD-10 AbsCare
    FFFFF = Facility Numer
    YYYY = Year - For Alberta they use the calendar year of the last month in the fiscal year eg April 2002-March 2003 would be fiscal 2003.
    MM = Month (actually the period of the fiscal year, eg January would be 10)
    SSS = Sequence Number (same as CIHI)
    '-' must be placed between the file name components
    */

    set @result = 'A-'
    set @result = @result + (select CIHIValue from H_InstitutionProfile where Code = @InstNoCode) + '-'
    set @result = @result + str(@FiscalYear, 4) + '-'
    set @result = @result + (select CIHIValue from LU_BatchPeriod where Code = @FiscalPeriod) + '-'
    set @result = @result + '0' + replace(str(@SequenceNo, 2), ' ', '0')
    set @result = @result + '.txt'

    return (@result)
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  function [dbo].[SU_FILENAME_CCRS](
    @ExecutionID int,
    @SU_Const varchar(20)
) returns varchar(50) as 
begin
    declare @f_name varchar(50)

    if @SU_Const = 'CRS_ASSESS_MAST' begin
    
        declare 
            @x_group int, -- GroupID
            @x_yyyy  int, -- Fiscal Year
            @x_pp    int, -- Fiscal Quarter
            @x_hhhhh int, -- Facility Number
            @x_ss    int  -- Sequence Number

        declare 
            @f_yyyy  char(4), -- Fiscal Year
            @f_pp    char(2), -- Fiscal Quarter
            @f_hhhhh char(5), -- Facility Number
            @f_ss    varchar(3)  -- Sequence Number

        select 
            @x_group = GroupID,
            @x_yyyy  = (case when BatchPeriod <> 15406 then BatchYear
                             else Datepart(year, DateEnd) - (case when datepart(month, DateEnd) > 3 then 0 else 1 end)
                        end),   --ED - 02155
            @x_pp    = (case when BatchPeriod in (15401,15651,15652,15653) and isnull(ProvNoCIHI, '') <> '9' then 1    --Quarter 1
                             when BatchPeriod in (15402,15654,15655,15656) and isnull(ProvNoCIHI, '') <> '9' then 2    --Quarter 2
                             when BatchPeriod in (15403,15657,15658,15659) and isnull(ProvNoCIHI, '') <> '9' then 3    --Quarter 3
                             when BatchPeriod in (15404,15660,15661,15662) and isnull(ProvNoCIHI, '') <> '9' then 4    --Quarter 4
                             when BatchPeriod in (15405) and isnull(ProvNoCIHI, '') <> '9' then 5    --Full Year
                             when BatchPeriod in (15406) then 6    --Q6 ED - 02155
                             else BatchPeriod
                        end),  
            @x_hhhhh = InstNoCode
        from SU_ExecProperties
        where ExecutionID = @ExecutionID

        select @x_ss = count(*) 
        from SU_ExecProperties 
        where GroupID     = @x_group
          and BatchYear   = @x_yyyy
          and (case when BatchPeriod in (15401,15651,15652,15653) and isnull(ProvNoCIHI, '') <> '9' then 1    --Quarter 1
                    when BatchPeriod in (15402,15654,15655,15656) and isnull(ProvNoCIHI, '') <> '9' then 2    --Quarter 2
                    when BatchPeriod in (15403,15657,15658,15659) and isnull(ProvNoCIHI, '') <> '9' then 3    --Quarter 3
                    when BatchPeriod in (15404,15660,15661,15662) and isnull(ProvNoCIHI, '') <> '9' then 4    --Quarter 4
                    when BatchPeriod in (15405) and isnull(ProvNoCIHI, '') <> '9' then 5    --Full Year
                    when BatchPeriod in (15406) then 6    --Q6 ED - 02155
                    else BatchPeriod
               end) = @x_pp
        
        
        set @f_yyyy  = str(@x_yyyy, 4)
        set @f_pp    = replace( str(@x_pp, 2), ' ', '0' )
        set @f_hhhhh = (select CIHIValue from H_InstitutionProfile where Code = @x_hhhhh)
        if @x_yyyy < 2012
            set @f_ss    = replace( str(@x_ss, 2), ' ', '0' )
        else
            set @f_ss    = replace( str(@x_ss, 3), ' ', '0' )
            
        set @f_yyyy  = isnull( @f_yyyy , 'yyyy'  )
        set @f_pp    = isnull( @f_pp   , 'pp'    )
        set @f_hhhhh = isnull( @f_hhhhh, 'hhhhh' )
        set @f_ss    = isnull( @f_ss   , 'ss'    )
        
        
        set @f_name = 'CCRyyyypphhhhhss.txt'
        
        set @f_name = 
            replace (
            replace (
            replace (
            replace (
                @f_name
            , 'yyyy' , @f_yyyy  )
            , 'pp'   , @f_pp    )
            , 'hhhhh', @f_hhhhh )
            , 'ss'   , @f_ss    )
    end
    else
        set @f_name = 
            case @SU_Const
                when 'CRS_ASSESS_ADMT' then 'CCR_ADMT.txt'
                when 'CRS_ASSESS_DEMO' then 'CCR_DEMO.txt'
                when 'CRS_ASSESS_QURT' then 'CCR_QURT.txt'
                when 'CRS_ASSESS_FULL' then 'CCR_FULL.txt'
                when 'CRS_ASSESS_DSCH' then 'CCR_DSCH.txt'
                when 'CRS_ASSESS_RNTR' then 'CCR_RENT.txt'
                when 'CRS_ASSESS_MEDI' then 'CCR_MEDI.txt'
                when 'CRS_ASSESS_UPDATE' then 'CCR_UPD.txt'
                when 'CRS_ASSESS_PROJECT' then 'CCR_PROJ.txt'
                else 'INVALID_FILE_NAME.txt'
            end

    return( @f_name )
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SU_FILENAME_CJRR](
    @ExecutionID int, @SU_Const varchar(50) 
) returns varchar(50) as 
begin
    declare @f_name varchar(50)

    
    declare 
        @x_group int, -- GroupID
        @x_yyyy  int, -- Fiscal Year
        @x_pp    int, -- Fiscal Quarter
        @x_hhhhh int, -- Facility Number
        @x_ss    int  -- Sequence Number
         
    declare 
        @f_yyyy  char(4), -- Fiscal Year
        @f_pp    char(2), -- Batch Year
        @f_hhhhh char(5), -- Facility Number
        @f_ss    varchar(3),  -- Sequence Number
        @f_j     char(1)    -- Knee or Hip
    select 
        @x_group = GroupID,
        @x_yyyy  = BatchYear,
        @x_pp    = BatPerCIHI,  
        @x_hhhhh = InstNoCode
    from SU_ExecProperties
    where ExecutionID = @ExecutionID

    select @x_ss = count(*) + 1
    from SU_ExecProperties 
    where GroupID     = @x_group
      and BatchYear   = @x_yyyy
      and BatPerCIHI  = @x_pp
      and InstNoCode =  @x_hhhhh
      and TerminateSuccess = 1
    
    
    set @f_yyyy  = str(@x_yyyy, 4)
    set @f_pp    = replace( str(@x_pp, 2), ' ', '0' )
    set @f_hhhhh = (select CIHIValue from H_InstitutionProfile where Code = @x_hhhhh)
    set @f_ss    = replace( str(@x_ss, 2), ' ', '0' )
        
    set @f_yyyy  = isnull( @f_yyyy , 'yyyy'  )
    set @f_pp    = isnull( @f_pp   , 'pp'    )
    set @f_hhhhh = isnull( @f_hhhhh, 'hhhhh' )
    set @f_ss    = isnull( @f_ss   , 'ss'    )
    set @f_j       = (case when @SU_Const = 'CJRR_KNEE' then '1' else '2' end)
    
    
    set @f_name = 'DDDyyyypphhhhhjss.txt'
    
    set @f_name = 
        replace (
        replace (
        replace (
        replace (
        replace (
        replace (
            @f_name
        , 'yyyy' , @f_yyyy  )
        , 'pp'   , @f_pp    )
        , 'hhhhh', @f_hhhhh )
        , 'ss'   , @f_ss    )
        , 'j'    , @f_j     )
        , 'DDD'  , 'CJR'    )
return( @f_name )
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function dbo.SU_FILENAME_DAD_ICD10 (
    @InstNoCode int,
    @BatchYear int,
    @BatchPeriod int,
    @IsCorrection int,
    @IsDelete bit,
    @SequenceNo int
) returns varchar(50) as
begin
    declare @result varchar(50)

    declare 
        @file_type varchar(2),
        @batPeriod varchar(2)

    set @file_type = case
        when @IsDelete = 1 then 'DA'
        when @IsCorrection = 1 then 'CA'
        else '' end

    if(@IsDelete = 1 or @IsCorrection = 1) 
        set @batPeriod = '00'
    else
        select @batPeriod = CIHIValue from LU_BatchPeriod where Code = @BatchPeriod

    set @result = 'DAD'
    set @result = @result + str(@BatchYear, 4)
    set @result = @result + @batPeriod
    set @result = @result + (select CIHIValue from H_InstitutionProfile where Code = @InstNoCode)
    set @result = @result + @file_type
    if @BatchYear < 2005
        set @result = @result + replace(str(@SequenceNo, 2), ' ', '0')
    else
        set @result = @result + replace(str(@SequenceNo, 3), ' ', '0')

    set @result = @result + '.txt'

    return (@result)
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE function [dbo].[SU_FILENAME_IAR_MH](
    @ExecutionID int,
    @SU_Const varchar(20)
) returns varchar(50) as 
begin
    declare @f_name varchar(50)

--    if @SU_Const = 'OMH_ASSESS_MAST' begin
    
        declare 
            @x_group int, -- GroupID
            @x_yyyy  int, -- Fiscal Year
            @x_pp    int, -- Fiscal Quarter
            @x_hhhhh int, -- Facility Number
            @x_ss    int  -- Sequence Number

        declare 
            @f_yyyy  char(4), -- Fiscal Year
            @f_pp    char(2), -- Fiscal Quarter
            @f_hhhhh char(5), -- Facility Number
            @f_ss    char(3)  -- Sequence Number

        select 
            @x_group = GroupID,
            @x_yyyy  = BatchYear,
            @x_pp    = (case when BatchPeriod in (15401,15651,15652,15653) and ProvNoCIHI <> '9' then 1    --Quarter 1
                             when BatchPeriod in (15402,15654,15655,15656) and ProvNoCIHI <> '9' then 2    --Quarter 2
                             when BatchPeriod in (15403,15657,15658,15659) and ProvNoCIHI <> '9' then 3    --Quarter 3
                             when BatchPeriod in (15404,15660,15661,15662) and ProvNoCIHI <> '9' then 4    --Quarter 4
                             when BatchPeriod in (15405) and ProvNoCIHI <> '9' then 0    --Full Year
                             else BatchPeriod
                        end),  
            @x_hhhhh = InstNoCode
        from SU_ExecProperties
        where ExecutionID = @ExecutionID

        select @x_ss = count(*) + 1
        from SU_ExecProperties 
        where GroupID     = @x_group
          and BatchYear   = @x_yyyy
          and (case when BatchPeriod in (15401,15651,15652,15653) and ProvNoCIHI <> '9' then 1    --Quarter 1
                    when BatchPeriod in (15402,15654,15655,15656) and ProvNoCIHI <> '9' then 2    --Quarter 2
                    when BatchPeriod in (15403,15657,15658,15659) and ProvNoCIHI <> '9' then 3    --Quarter 3
                    when BatchPeriod in (15404,15660,15661,15662) and ProvNoCIHI <> '9' then 4    --Quarter 4
                    when BatchPeriod in (15405) and ProvNoCIHI <> '9' then 0    --Full Year
                    else BatchPeriod
               end) = @x_pp
            and TerminateSuccess = 1
        
        if @x_ss is null set @x_ss = 1
        
        set @f_yyyy  = str(@x_yyyy, 4)
        set @f_pp    = replace( str(@x_pp, 2), ' ', '0' )
        set @f_hhhhh = (select CIHIValue from H_InstitutionProfile where Code = @x_hhhhh)
        set @f_ss    = replace( str(@x_ss, 3), ' ', '0' )
        
        set @f_yyyy  = isnull( @f_yyyy , 'yyyy'  )
        set @f_pp    = isnull( @f_pp   , 'pp'    )
        set @f_hhhhh = isnull( @f_hhhhh, 'hhhhh' )
        set @f_ss    = isnull( @f_ss   , 'sss'    )
        
        
        set @f_name = 'IARyyyypphhhhhsss.txt'
        
        set @f_name = 
            replace (
            replace (
            replace (
            replace (
                @f_name
            , 'yyyy' , @f_yyyy  )
            , 'pp'   , @f_pp    )
            , 'hhhhh', @f_hhhhh )
            , 'sss'   , @f_ss    )
   -- end
    

    return( @f_name )
end





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SU_FILENAME_MACRS] (
    @InstNoCode int,
    @FiscalYear int,
    @FiscalPeriod int,
    @SequenceNo int
) returns varchar(50) as 
begin
    declare @result varchar(50)

    /*
    T-FFFFF-YYYY-MM-SSS.TXT
    T=Record Type - Must be 'M' for ICD-10 AbsCare
    FFFFF = Facility Numer
    YYYY = Year - For Alberta they use the calendar year of the last month in the fiscal year eg April 2002-March 2003 would be fiscal 2003.
    MM = Month (actually the period of the fiscal year, eg January would be 10)
    SSS = Sequence Number (same as CIHI)
    '-' must be placed between the file name components
    */

    set @result = 'M-'
    set @result = @result + (select CIHIValue from H_InstitutionProfile where Code = @InstNoCode) + '-'
    set @result = @result + str(@FiscalYear, 4) + '-'
    set @result = @result + (select CIHIValue from LU_BatchPeriod where Code = @FiscalPeriod) + '-'
    set @result = @result + '0' + replace(str(@SequenceNo, 2), ' ', '0')   
    set @result = @result + '.txt'

    return (@result)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function dbo.SU_FILENAME_MADE_ICD10 (
    @InstNoCode int,
    @BatchYear int,
    @BatchPeriod int,
    @IsCorrection int,
    @IsDelete bit,
    @SequenceNo int
) returns varchar(50) as
begin
    declare @result varchar(50)

    declare 
        @file_type varchar(2),
        @batPeriod varchar(2)

    set @file_type = case
        when @IsDelete = 1 then 'DA'
        when @IsCorrection = 1 then 'CA'
        else '' end

    if(@IsDelete = 1 or @IsCorrection = 1) 
        set @batPeriod = '00'
    else
        select @batPeriod = CIHIValue from LU_BatchPeriod where Code = @BatchPeriod

    set @result = 'MBHA'
    set @result = @result + str(@BatchYear, 4)
    set @result = @result + @batPeriod
    set @result = @result + (select CIHIValue from H_InstitutionProfile where Code = @InstNoCode)
    set @result = @result + @file_type
    if @BatchYear < 2005
        set @result = @result + replace(str(@SequenceNo, 2), ' ', '0')
    else
        set @result = @result + replace(str(@SequenceNo, 3), ' ', '0')

    set @result = @result + '.txt'

    return (@result)
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  function [dbo].[SU_FILENAME_NACRS] (
    @InstNoCode int,
    @FiscalYear int,
    @FiscalPeriod int,
    @SequenceNo int,
    @NACRSLevelCode int --ED - 01838
) returns varchar(50) as 
begin
    declare @result varchar(50)

    -- NACYYYYPPFFFFFSS.txt before 2005
    -- NACYYYYPPFFFFFSSS.txt fiscal 2005

    set @result = 'NAC'
    set @result = @result + str(@FiscalYear, 4)
    set @result = @result + (select CIHIValue from LU_BatchPeriod where Code = @FiscalPeriod) 
    set @result = @result + (select CIHIValue from H_InstitutionProfile where Code = @InstNoCode)
    if @FiscalYear < 2005
        set @result = @result + replace(str(@SequenceNo, 2), ' ', '0')
    else
    begin
        if @FiscalYear >= 2009
            set @result = @result + cast(isnull(@NACRSLevelCode, 3) as varchar(1))

        set @result = @result + replace(str(@SequenceNo, 3), ' ', '0')
    end
    set @result = @result + '.txt'

    return (@result)
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function dbo.SU_FILENAME_OMHRS(
    @ExecutionID int,
    @SU_Const varchar(20)
) returns varchar(50) as 
begin
    declare @f_name varchar(50)

    if @SU_Const = 'OMH_ASSESS_MAST' begin
    
        declare 
            @x_group int, -- GroupID
            @x_yyyy  int, -- Fiscal Year
            @x_pp    int, -- Fiscal Quarter
            @x_hhhhh int, -- Facility Number
            @x_ss    int  -- Sequence Number

        declare 
            @f_yyyy  char(4), -- Fiscal Year
            @f_pp    char(2), -- Fiscal Quarter
            @f_hhhhh char(5), -- Facility Number
            @f_ss    char(3)  -- Sequence Number

        select 
            @x_group = GroupID,
            @x_yyyy  = BatchYear,
            @x_pp    = (case when BatchPeriod in (15401,15651,15652,15653) and ProvNoCIHI <> '9' then 1    --Quarter 1
                             when BatchPeriod in (15402,15654,15655,15656) and ProvNoCIHI <> '9' then 2    --Quarter 2
                             when BatchPeriod in (15403,15657,15658,15659) and ProvNoCIHI <> '9' then 3    --Quarter 3
                             when BatchPeriod in (15404,15660,15661,15662) and ProvNoCIHI <> '9' then 4    --Quarter 4
                             when BatchPeriod in (15405) and ProvNoCIHI <> '9' then 0    --Full Year
                             else BatchPeriod
                        end),  
            @x_hhhhh = InstNoCode
        from SU_ExecProperties
        where ExecutionID = @ExecutionID

        select @x_ss = count(*) 
        from SU_ExecProperties 
        where GroupID     = @x_group
          and BatchYear   = @x_yyyy
          and (case when BatchPeriod in (15401,15651,15652,15653) and ProvNoCIHI <> '9' then 1    --Quarter 1
                    when BatchPeriod in (15402,15654,15655,15656) and ProvNoCIHI <> '9' then 2    --Quarter 2
                    when BatchPeriod in (15403,15657,15658,15659) and ProvNoCIHI <> '9' then 3    --Quarter 3
                    when BatchPeriod in (15404,15660,15661,15662) and ProvNoCIHI <> '9' then 4    --Quarter 4
                    when BatchPeriod in (15405) and ProvNoCIHI <> '9' then 0    --Full Year
                    else BatchPeriod
               end) = @x_pp
        
        
        set @f_yyyy  = str(@x_yyyy, 4)
        set @f_pp    = replace( str(@x_pp, 2), ' ', '0' )
        set @f_hhhhh = (select CIHIValue from H_InstitutionProfile where Code = @x_hhhhh)
        set @f_ss    = replace( str(@x_ss, 3), ' ', '0' )
        
        set @f_yyyy  = isnull( @f_yyyy , 'yyyy'  )
        set @f_pp    = isnull( @f_pp   , 'pp'    )
        set @f_hhhhh = isnull( @f_hhhhh, 'hhhhh' )
        set @f_ss    = isnull( @f_ss   , 'sss'    )
        
        
        set @f_name = 'MHRyyyypphhhhhsss.txt'
        
        set @f_name = 
            replace (
            replace (
            replace (
            replace (
                @f_name
            , 'yyyy' , @f_yyyy  )
            , 'pp'   , @f_pp    )
            , 'hhhhh', @f_hhhhh )
            , 'sss'   , @f_ss    )
    end
    else
        set @f_name = 
            case @SU_Const
                when 'OMH_ASSESS_FULL'  then 'OMHRS_ADMIT.txt'
                when 'OMH_ASSESS_SHORT' then 'OMHRS_SHORT.txt'
                when 'OMH_ASSESS_QURT'  then 'OMHRS_QURT.txt'
                when 'OMH_ASSESS_CHG'   then 'OMHRS_CHG.txt'
                when 'OMH_ASSESS_DSCH'  then 'OMHRS_DSCH.txt'
                when 'OMH_ASSESS_MED'   then 'OMHRS_MED.txt'
                when 'OMH_ASSESS_DEL'   then 'OMHRS_DEL.txt'
                else 'INVALID_FILE_NAME.txt'
            end

    return( @f_name )
end





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SU_FILENAME_ReciprocalBilling] (
    @InstNoCode int,
    @BatchYear int,
    @BatchPeriod int,
    @SequenceNo int
) returns varchar(50) as
begin
    declare @result varchar(50)
    declare @InstCIHI varchar(5)
    declare @caretype int
    declare @batPeriod varchar(2)

    select @batPeriod = CIHIValue from LU_BatchPeriod where Code = @BatchPeriod
    select @InstCIHI = CIHIValue, @caretype = associationid from h_Institutionprofile where code = @InstNoCode 

    if @caretype = 5
        set @result = 'NAC'
    else
        set @result = 'DAD'
    set @result = @result + str(@BatchYear, 4)
    set @result = @result + @batPeriod
    set @result = @result + @InstCIHI   --(select CIHIValue from H_InstitutionProfile where Code = @InstNoCode)
    set @result = @result + replace(str(@SequenceNo, 3), ' ', '0')

    set @result = @result + '.rcp'

    return (@result)
end
/*
        @Senddate   datetime    //Parameter

    declare    @FacilityNumber varchar(3)


    select top 1 @HospitalID = isnull(HospitalID, 0), @file_type = (case when AssociationID = 5 then 'O' else 'I' end) 
    from dbo.H_InstitutionProfile where code = @InstNoCode

    set @FacilityNumber = (select top 1 right(FacilityNumber, 3) from dbo.H_HospitalProfile where @HospitalID in (0, code))

    --select @batPeriod = CIHIValue from LU_BatchPeriod where Code = @BatchPeriod

    set @result = @file_type + '-'
    set @result = @result + @FacilityNumber + '-' + convert(varchar(10), @Senddate, 112)
    set @result = @result + '.txt'

*/
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function [dbo].[SU_GetFromClauseBaseTbl] (
    @TableName  varchar(50),
    @Occurrence int
) returns varchar(300)
begin

    declare @TableAlias varchar(50)

    set @TableAlias = case when (@Occurrence > 0)
        then replace( @TableName, '##', '' ) + convert( varchar(3), @Occurrence )
        else replace( @TableName, '##', '' ) 
    end
   
    return (
        
        'left join ' + @TableName + ' ' + @TableAlias + ' on RecordSelect.AbstractID = ' + @TableAlias + '.AbstractID ' + 
        case when (@Occurrence > 0) then 'and ' + @TableAlias + '.' + 
            case (replace( @TableName, '##', '' ))
                when 'AB_CMG'             then 'OccurrenceNumber'
                when 'AB_Diagnosis'       then 'DiagnosisOccurrence'
                when 'AB_DPG'             then 'OccurrenceNumber'
                when 'AB_Intervention'    then 'IntervOccurrence'
                when 'AB_MIS'             then 'MISOccurrence'
                when 'AB_PatientService'  then 'ServiceOccurrence'
                when 'AB_Project'         then 'ProjectOccurrence'
                when 'AB_Provider'        then 'ProviderOccurrence'
                when 'AB_SpecialCareUnit' then 'SCUOccurrence'
                when 'CRS_Signature'      then 'ProviderOccurrence'
                when 'RH_Diagnosis'       then 'DiagnosisOccurrence'
                when 'RH_Intervention'    then 'IntervOccurrence'
                when 'RH_ServiceInterupt' then 'InteruptOccurrence'
                when 'AB_SpecialProjects' then 'OccurrenceNumber'	--ED - 02385
            end + ' = ' + convert( varchar(3), @Occurrence ) + ' '
            else ''
        end
    )

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   function [dbo].[SU_GetFromClauseSegment] (
    @TableAlias  varchar(50),
    @FieldName   varchar(50),
    @FieldAlias  varchar(50),
    @FieldID     int        ,
    @ICDType     char(1)    ,
    @LookUpTable varchar(50),
    @DataType    int        
) returns varchar(300)
begin

    --------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    ----  CREATE THE FROM CLAUSE SEGMENT -------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    return (
        case 
            when (@DataType in (21, 50, 51, 56, 71, 72, 73, 74))
                then 'left join ' + @LookUpTable + ' ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
            
            when (@DataType = 54)
                then case @LookUpTable                
                    when 'LLU_ProviderServices'  then 'left join LU_ProviderService '    + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'LLU_ProviderSpecialty' then 'left join LU_ProviderSpecialty '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                end                        

            when (@DataType = 60) 
                then case @ICDType 
                    when '9' then 'left join ICD_DiagFile9 '   + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'C' then 'left join ICD_DiagFile9cm ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'T' then 'left join ICD_DiagFile10 '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                end
            when (@DataType = 61) 
                then case @ICDType
                    when '9' then 'left join ICD_ProcFile9 '   + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'C' then 'left join ICD_ProcFile9cm ' + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                    when 'T' then 'left join ICD_ProcFile10 '  + @FieldAlias + ' on ' + @TableAlias + '.' + @FieldName + ' = ' + @FieldAlias + '.Code ' 
                end

            else ''
        end 
    )
 

end








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE function [dbo].[SU_GetSelectClauseSegment] (
    @ExecutionID int        ,
    @GroupID     int        ,
    @SubGroupID  int        ,
    @TableName   varchar(50),
    @TableAlias  varchar(50),
    @FieldName   varchar(50),
    @FieldAlias  varchar(50),
    @FieldID     int        ,
    @ICDType     char(1)    ,
    @LookUpTable varchar(50),
    @DataType    int        ,
    @Occurrence  int        ,
    @NoComma     bit        
) returns varchar(300)
begin

    --------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    ----  CREATE THE SELECT CLAUSE SEGMENT -----------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------
    return (
        case 
            when (@FieldName = 'DiagnosisPrefix') and (@GroupID in (2, 3))
                --  THIS IS A SPECIAL CASE FOR DiagnosisPrefix AND E/M DIAGNOSIS CODES
                then @FieldAlias + ' = case ' +
                     'when (left(DiagnosisCode' + convert( varchar(3), @Occurrence ) + '.CIHIValue, 1) collate Latin1_General_CI_AS in (''E'', ''M'')) ' +
                     'then left(DiagnosisCode' + convert( varchar(3), @Occurrence ) + '.CIHIValue, 1) ' +
                     'else ' + @FieldAlias + '.CIHIValue end' 

            when (@FieldAlias = 'reserve')
                -- FieldAlias = TableAlias.FieldName
                then @FieldAlias + ' = ' + @TableAlias + '.' + @FieldName
            

            when (@DataType in (0, 2, 3, 9, 10, 12, 17, 20, 52))
                -- FieldAlias = TableAlias.FieldName
                then @FieldAlias + ' = ' + @TableAlias + '.' + @FieldName 

            when (@DataType in (21, 50, 51, 54, 56, 60, 61, 71, 72, 73, 74))
                -- FieldAlias = LookUpTable.CIHIValue
                then  @FieldAlias + ' = ' + @FieldAlias + '.CIHIValue' 

            when (@DataType = 11) 
                -- FieldAlias = 'YYYY/MM/DD'
                then @FieldAlias + ' = convert( varchar(10), ' + @TableAlias + '.' + @FieldName + ', 111)'

            when (@DataType = 15)
                -- FieldAlias = 'HH:MM:SS'
                then @FieldAlias + ' = convert( varchar(8), ' + @TableAlias + '.' + @FieldName + ', 108)'

            when (@DataType is not null)
                -- FieldAlias = DataType: X
                then @FieldAlias + ''' = DataType: ' + convert( varchar(3), @DataType ) + ''''

                -- FieldAlias = (Default clause)
            else @FieldAlias + ' = ' + isnull((select DefaultVal from SU_Defaults 
                    where GroupID = @GroupID and SubGroupID = @SubGroupID and FieldName = @FieldName and ExecutionID = @ExecutionID ), '') 

        end + case when (@NoComma = 1) then ' ' else ', ' end -- add comma when applicable
    )

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function dbo.SU_GetSpecialProjectQuestion(
	@Question1 varchar(200),  @Question2 varchar(200),	@Question3 varchar(200),  @Question4 varchar(200),	
	@Question5 varchar(200),  @Question6 varchar(200),  @Question7 varchar(200),  @Question8 varchar(200), 
	@Question9 varchar(200),  @Question10 varchar(200), @Question11 varchar(200), @Question12 varchar(200), 
	@Question13 varchar(200), @Question14 varchar(200), @Question15 varchar(200), @Question16 varchar(200), 
	@Question17 varchar(200), @Question18 varchar(200), @Question19 varchar(200), @Question20 varchar(200)
) returns varchar(200)
as 
begin
	declare @question varchar(200)
	
	set @question = ''
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question20, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question19, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question18, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question17, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question16, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question15, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question14, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question13, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question12, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question11, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question10, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question9, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question8, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question7, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question6, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question5, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question4, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question3, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question2, @question)
	set @question = dbo.SU_GetSpecialProjectQuestionByQuestion(@Question1, @question)
			
	return 	@question
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function dbo.SU_GetSpecialProjectQuestionByQuestion(
	@Question varchar(200), @Answers varchar(200)
	) returns varchar(200)
as
begin
	if len(@Answers) > 0
	begin
		set @Answers =  ltrim(rtrim(isnull(@Question, ''))) + ';' + @Answers
	end
	else
		set @Answers = ltrim(rtrim(isnull(@Question, '')))
	return @Answers
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function [dbo].[SU_SELECT_CCRS]
(
    @SU_Const varchar(20),
    @inst_no int,
    @date_fr datetime,
    @date_to datetime,
    @batchperiod int    --ED - 02155
) 
returns @Records table 
( AbstractID int
, patient_id varchar(20)
, event_date datetime
, assessment varchar(2)
, IsSubmited int
, IsDeleted int
, IsCorrected int   --ED - 02710
) 
as 
begin
-- ADMISSION BACKGROUND
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_ADMT')
begin
    if @batchperiod = 15406 --history admission only
        insert @Records
        select AbstractID, AA1, AdmitDate, '10', IsSubmited, IsDeleted, IsCorrected
        from dbo.CRS_Demographic d
            inner join lu_CodeFile l on d.AA8 = l.Code
        where InstitutionNumber = @inst_no
        and d.Z1 in (15351, 18539) and l.CIHIValue = '01'   --admission backgroud only
        and AdmitDate between @date_fr and @date_to
    else
        insert @Records
        select AbstractID, AA1, AdmitDate, '10', IsSubmited, IsDeleted, IsCorrected
        from dbo.CRS_Demographic d
        where InstitutionNumber = @inst_no
        and d.Z1 in (15351, 18539)
        and AdmitDate between @date_fr and @date_to
    
end
-- RE-ENTRY
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_RNTR') and @batchperiod <> 15406 --ED - 02155 exclude Q6
    insert @Records
    select AbstractID, AA1, AB1b, '11', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15352
    and AB1b between @date_fr and @date_to

-- FULL ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_FULL') and @batchperiod <> 15406 --ED - 02155 exclude Q6
    insert @Records
    select AbstractID, AA1, A3, '12', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 in (15353, 18540)
    --PR - 04108
    --and A3 between @date_fr and @date_to 
    and ((A3 between @date_fr and @date_to and 
        not (AA8 = 14502 and Admitdate < @date_fr and datediff(day, Admitdate, A3) <= 14 and AdmitDate < '2012/04/01')) or
        (AA8 = 14502 and Admitdate between @date_fr and @date_to and datediff(day, Admitdate, A3) <= 14))
    

-- QUARTERLY ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_QURT') and @batchperiod <> 15406 --ED - 02155 exclude Q6
    insert @Records
    select AbstractID, AA1, A3, '13', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 in (15354, 18541)
    and A3 between @date_fr and @date_to

-- MEDICAL ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MEDI') and @batchperiod <> 15406 --ED - 02155 exclude Q6 -- ('CRS_ASSESS_MAST')
    insert @Records
    select d.AbstractID, AA1, A3, '14', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
        inner join dbo.CRS_Medication m on d.abstractid = m.abstractid
        left  join LU_CodeFile z on d.Y3 = z.code   --PR - 06229
    where InstitutionNumber = @inst_no
    --PR - 01564
    --and d.Z1 = 15355
    and d.Z1 in (15353, 15354, 18540, 18541)
    and m.Occurrence = 1
    --PR - 04108
    --and A3 between @date_fr and @date_to 
    and (A3 between @date_fr and @date_to or 
        (AA8 = 14502 and Admitdate between @date_fr and @date_to and datediff(day, Admitdate, A3) <= 14))
    and (d.IsDeleted not in (1, 3) or @date_to < '2012/04/01')  --ED - 02710 do not create delettion Medication. It will take from saved the record.
    and isnull(z.CIHIValue, '') <> 'D'  --PR - 06229
    
-- DEMOGRAPHIC CORRECTION
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_DEMO') and @batchperiod <> 15406 --ED - 02155 exclude Q6
    insert @Records
    select AbstractID, AA1, Z7, '15', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15356
    and Z7 between @date_fr and @date_to

-- DISCHARGE ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_DSCH') and @batchperiod <> 15406 --ED - 02155 exclude Q6
    insert @Records
    select AbstractID, AA1, DischargeDate, '16', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 in (15357, 18543)
    --PR - 04390 Reverse PR - 04108 for discharge assessment.
    and DischargeDate between @date_fr and @date_to
    --PR - 04108
    --and DischargeDate between @date_fr and @date_to
    --and (DischargeDate between @date_fr and @date_to
    --   or (AA8 = 14509 and Admitdate between @date_fr and @date_to))

-- Update ASSESSMENT for 2010
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_UPDATE') and @batchperiod <> 15406 --ED - 02155 exclude Q6
    insert @Records
    select AbstractID, AA1, Z12, '17', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 in (203621, 18544)
    and Z12 between @date_fr and @date_to and Z12 >= '2010/04/01'

-- Special Project ASSESSMENT for 2010 --ED - 02385 change combined with other assessment
if @SU_Const in ('CRS_ASSESS_PROJECT') and @batchperiod <> 15406 --ED - 02155 exclude Q6 --'CRS_ASSESS_MAST',
    insert @Records
    --ED - 02385 special project are in assessment.
    --select AbstractID, AA1, Z13, '18', IsSubmited, IsDeleted
    --from dbo.CRS_Demographic d
    --where InstitutionNumber = @inst_no
    --and d.Z1 = 203622
    --and Z13 between @date_fr and @date_to and Z13 >= '2010/04/01'
	select d.AbstractID, AA1, (case --when d.Z1 = 15356 then Z7 --Demographic change
									when d.Z1 in (15351, 18539) then AdmitDate --Admission
									when d.Z1 in (15353, 15354, 18540, 18541) then A3 --Quarterly, Full
									when d.Z1 in (15357, 18543) then DischargeDate --Discharge
									when d.Z1 = 15352 then AB1b --Re-Entry
									when d.Z1 in (203621, 18544) then Z12	--Update Record
								end), '18', IsSubmited, IsDeleted, IsCorrected
    from dbo.CRS_Demographic d
        inner join dbo.AB_SpecialProjects m on d.abstractid = m.abstractid and m.CareType = 4
    where InstitutionNumber = @inst_no
    and m.OccurrenceNumber = 1
	and (case --when d.Z1 = 15356 then Z7 --Demographic change
			  when d.Z1 in (15351, 18539) then AdmitDate --Admission
			  when d.Z1 in (15353, 15354, 18540, 18541) then A3 --Quarterly, Full
			  when d.Z1 in (15357, 18543) then DischargeDate --Discharge
			  --when d.Z1 = 15352 then AB1b --Re-Entry
			  when d.Z1 in (203621, 18544) then Z12	--Update Record
		  end) between @date_fr and @date_to and @date_fr >= '2011/04/01' 
        
return
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function dbo.SU_SELECT_CCRS_NEW
(
    @SU_Const varchar(20),
    @inst_no int,
    @date_fr datetime,
    @date_to datetime
) 
returns @Records table 
( AbstractID int
, patient_id varchar(20)
, event_date datetime
, assessment varchar(2)
, IsSubmited int
, IsDeleted int
) 
as 
begin
-- ADMISSION BACKGROUND
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_ADMT')
    insert @Records
    select AbstractID, AA1, AdmitDate, '10', IsSubmited, IsDeleted
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15351
    and AdmitDate between @date_fr and @date_to

-- RE-ENTRY
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_RNTR')
    insert @Records
    select AbstractID, AA1, AB1b, '11', IsSubmited, IsDeleted
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15352
    and AB1b between @date_fr and @date_to

-- FULL ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_FULL')
    insert @Records
    select AbstractID, AA1, A3, '12', IsSubmited, IsDeleted
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15353
    and A3 between @date_fr and @date_to

-- QUARTERLY ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_QURT')
    insert @Records
    select AbstractID, AA1, A3, '13', IsSubmited, IsDeleted
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15354
    and A3 between @date_fr and @date_to

-- MEDICAL ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_MEDI')
    insert @Records
    select AbstractID, AA1, A3, '14', IsSubmited, IsDeleted
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15355
    and A3 between @date_fr and @date_to

-- DEMOGRAPHIC CORRECTION
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_DEMO')
    insert @Records
    select AbstractID, AA1, Z7, '15', IsSubmited, IsDeleted
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15356
    and Z7 between @date_fr and @date_to

-- DISCHARGE ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_DSCH')
    insert @Records
    select AbstractID, AA1, DischargeDate, '16', IsSubmited, IsDeleted
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15357
    and DischargeDate between @date_fr and @date_to
return
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function dbo.SU_SELECT_CCRS_OLD
(
    @SU_Const varchar(20),
    @inst_no int,
    @date_fr datetime,
    @date_to datetime
) 
returns @Records table 
(
    AbstractID int,
    patient_id varchar(20),
    event_date datetime,
    assessment varchar(2)
) 
as 
begin
-- ADMISSION BACKGROUND
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_ADMT')
    insert @Records
    select AbstractID, AA1, AdmitDate, '10'
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15351
    and IsSubmited != 1
    and IsDeleted = 2
    and AdmitDate between @date_fr and @date_to

-- RE-ENTRY
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_RNTR')
    insert @Records
    select AbstractID, AA1, AB1b, '11'
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15352
    and IsSubmited != 1
    and IsDeleted = 2
    and AB1b between @date_fr and @date_to

-- FULL ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_FULL')
    insert @Records
    select AbstractID, AA1, A3, '12'
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15353
    and IsSubmited != 1
    and IsDeleted = 2
    and A3 between @date_fr and @date_to

-- QUARTERLY ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_QURT')
    insert @Records
    select AbstractID, AA1, A3, '13'
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15354
    and IsSubmited != 1
    and IsDeleted = 2
    and A3 between @date_fr and @date_to

-- MEDICAL ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_MEDI')
    insert @Records
    select AbstractID, AA1, A3, '14'
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15355
    and IsSubmited != 1
    and IsDeleted = 2
    and A3 between @date_fr and @date_to

-- DEMOGRAPHIC CORRECTION
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_DEMO')
    insert @Records
    select AbstractID, AA1, Z7, '15'
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15356
    and IsSubmited != 1
    and IsDeleted = 2
    and Z7 between @date_fr and @date_to

-- DISCHARGE ASSESSMENT
if @SU_Const in ('CRS_ASSESS_MAST', 'CRS_ASSESS_DSCH')
    insert @Records
    select AbstractID, AA1, DischargeDate, '16'
    from dbo.CRS_Demographic d
    where InstitutionNumber = @inst_no
    and d.Z1 = 15357
    and IsSubmited != 1
    and IsDeleted = 2
    and DischargeDate between @date_fr and @date_to
return
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function dbo.SU_SELECT_OMHRS
(
    @SU_Const varchar(20),
    @inst_no int,
    @date_fr datetime,
    @date_to datetime
) 
returns @Records table 
( AbstractID int
, patient_id varchar(20)
, event_date datetime
, assessment varchar(2)
, IsSubmited int
, IsDeleted int
) 
as 
begin
-- All Submission this time.
if @SU_Const in ('OMH_ASSESS_MAST')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '0', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and ((R.IsSubmited = 2 and R.IsCompleted in (3, 4))
            or (R.IsSubmited = 1 and R.IsCompleted in (3, 4) and R.IsCorrected = 2 and IsDeleted = 2))
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 

-- Full Assessment
if @SU_Const in ('OMH_ASSESS_FULL')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '3', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and ((R.IsSubmited = 2 and R.IsCompleted in (3, 4))
            or (R.IsSubmited = 1 and R.IsCompleted in (3, 4) and R.IsCorrected = 2 and IsDeleted = 2))
        and LU.CIHIValue = '3'
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 

-- Short Assessment
if @SU_Const in ('OMH_ASSESS_SHORT')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '7', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and ((R.IsSubmited = 2 and R.IsCompleted in (3, 4))
            or (R.IsSubmited = 1 and R.IsCompleted in (3, 4) and R.IsCorrected = 2 and IsDeleted = 2))
        and LU.CIHIValue = '7'
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 

-- Quarter Assessment
if @SU_Const in ('OMH_ASSESS_QURT')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '4', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and ((R.IsSubmited = 2 and R.IsCompleted in (3, 4))
            or (R.IsSubmited = 1 and R.IsCompleted in (3, 4) and R.IsCorrected = 2 and IsDeleted = 2))
        and LU.CIHIValue = '4'
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 

-- Change Assessment
if @SU_Const in ('OMH_ASSESS_CHG')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '6', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and ((R.IsSubmited = 2 and R.IsCompleted in (3, 4))
            or (R.IsSubmited = 1 and R.IsCompleted in (3, 4) and R.IsCorrected = 2 and IsDeleted = 2))
        and LU.CIHIValue = '6'
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 

-- Discharge Assessment
if @SU_Const in ('OMH_ASSESS_DSCH')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '5', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and ((R.IsSubmited = 2 and R.IsCompleted in (3, 4))
            or (R.IsSubmited = 1 and R.IsCompleted in (3, 4) and R.IsCorrected = 2 and IsDeleted = 2))
        and LU.CIHIValue = '5'
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 

-- Medicine Assessment
if @SU_Const in ('OMH_ASSESS_MED')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '8', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and ((R.IsSubmited = 2 and R.IsCompleted in (3, 4))
            or (R.IsSubmited = 1 and R.IsCompleted in (3, 4) and R.IsCorrected = 2 and IsDeleted = 2))
        and LU.CIHIValue = '8'
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 
-- Delete tion
if @SU_Const in ('OMH_ASSESS_MAST')
    insert @Records
    select AA.AbstractID, ChartNumber, A1, '9', IsSubmited, IsDeleted
    from dbo.OMHRS_SecAA AA
        inner join dbo.OMHRS_SecA A on AA.AbstractID = A.AbstractID
        inner join dbo.OMHRS_RecInfo R on AA.AbstractID = R.AbstractID
        inner join lu_codefile LU on AA.Z1 = LU.Code
    where InstitutionNumber = @inst_no
        and A.A1 between @date_fr and @date_to
        and (R.IsSubmited = 1 and R.IsCorrected = 2 and IsDeleted = 1)
    order by AA.ChartNumber, AA.AdmitDate, LU.CIHIValue, A.A1 

return
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create function dbo.ToNumber(
	@Number varchar(10)
) returns int
as
begin
	declare @value int
	if isnumeric(@Number) = 1 and charindex('.', @Number) = 0
		set @value = cast(@Number as int)
	return(@value)
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE function getTodayDate() returns varchar(50) as
begin
    return (DATENAME(dw, '2002-08-29'))
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

