DROP PROCEDURE [hips].[AddressByPatientList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 October 2012
-- Description:   Gets all the address records for a patient
-- =============================================
CREATE PROCEDURE [hips].[AddressByPatientList] 
(
	@PatientMasterId INT
)
AS
BEGIN
	SELECT  a.AddressId,
			a.AddressLine1,
			a.AddressLine2,
			a.PlaceName,
			a.AustralianStateId,
			st.Code AS AustralianStateCode,
			st.Description AS AustralianStateName,
			a.InternationalStateCode,
			a.PostCode,
			a.CountryId,
			c.DESCRIPTION AS CountryName,
			a.AddressTypeId,
			at.DESCRIPTION AS AddressTypeDescription,
			a.DateCreated,
			a.UserCreated,
			a.DateModified,
			a.UserModified
			
	FROM  [hips].[PatientMasterAddress] as pma
	LEFT OUTER JOIN [hips].[Address] a ON a.AddressId = pma.AddressId
	LEFT OUTER JOIN [hips].[State] AS st ON st.StateID = a.AustralianStateId
	Inner JOIN [hips].[Country] AS c ON c.CountryId = a.CountryId
	INNER JOIN [hips].[AddressType] AS at ON at.AddressTypeId = a.AddressTypeId
	WHERE pma.PatientMasterId = @PatientMasterId
END
GO

DROP PROCEDURE [hips].[AddressDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 October 2012
-- Description:   Deletes an existing record from the Addresss Table
-- =============================================
CREATE PROCEDURE [hips].[AddressDelete] 
(
	  @AddressId INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int,
			@intRowsAffected int

    SELECT  @intErrorCode = 0,
			@intRowsAffected = 0

    DELETE
     FROM [hips].[Address]
     WHERE [AddressId] = @AddressId

    SELECT @intErrorCode = @@ERROR,
   		   @intRowsAffected = @@ROWCOUNT

	SELECT @intRowsAffected AS RowsAffected

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[AddressGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 15 October 2012
-- Description:   Gets a specified record from the Address Table
-- =============================================
CREATE PROCEDURE [hips].[AddressGet] 
(
	@AddressId INT
)
AS
BEGIN
SELECT  a.AddressId,
		a.AddressLine1,
		a.AddressLine2,
		a.PlaceName,
		a.AustralianStateId,
		st.Code AS AustralianStateCode,
		st.Description AS AustralianStateName,
		a.InternationalStateCode,
		a.PostCode,
		a.CountryId,
		c.DESCRIPTION AS CountryName,
		a.AddressTypeId,
		at.DESCRIPTION AS AddressTypeDescription,
		a.DateCreated,
		a.UserCreated,
		a.DateModified,
		a.UserModified
		
FROM  hips.Address a
LEFT OUTER JOIN hips.State AS st ON st.StateID = a.AustralianStateId
Inner JOIN hips.Country AS c ON c.CountryId = a.CountryId
INNER JOIN hips.AddressType AS at ON at.AddressTypeId = a.AddressTypeId
WHERE a.AddressId = @AddressId
END
GO

DROP PROCEDURE [hips].[AddressInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 October 2012
-- Description:   Inserts a new record into the Address Table
-- =============================================
CREATE PROCEDURE [hips].[AddressInsert]
(
       @AddressLine1 VARCHAR(100)
      ,@AddressLine2 VARCHAR(100) = null
      ,@PlaceName VARCHAR(100) = null
      ,@AustralianStateId INT
      ,@InternationalStateCode VARCHAR(10) = NULL
      ,@Postcode VARCHAR(10) = null
      ,@CountryId INT
      ,@AddressTypeId INT
      ,@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @intErrorCode INT,
            @AddressId  INT
    SET @intErrorCode = 0
    INSERT
     INTO [hips].[Address]
         ( [AddressLine1],
           [AddressLine2],
           [PlaceName],
           [AustralianStateId],
           [InternationalStateCode],
           postcode,
           [CountryId],
           [AddressTypeId],
           [DateCreated],
           [UserCreated],
           [DateModified],
           [UserModified]
         )
    VALUES
         (
            @AddressLine1
           ,@AddressLine2
           ,@PlaceName
           ,@AustralianStateId
           ,@InternationalStateCode
           ,@Postcode
           ,@CountryId
           ,@AddressTypeId
          , GETDATE()
          , @UserModified
          , GETDATE()
          , @UserModified
         )
    SELECT @intErrorCode = @@ERROR 
           , @AddressId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
       SELECT  a.AddressId,
        a.AddressLine1,
        a.AddressLine2,
        a.PlaceName,
        a.AustralianStateId,
        st.Description AS AustralianStateName,
        a.InternationalStateCode,
        a.PostCode,
        a.CountryId,
        c.DESCRIPTION AS CountryName,
        a.AddressTypeId,
        at.DESCRIPTION AS AddressTypeDescription,
        a.DateCreated,
        a.UserCreated,
        a.DateModified,
        a.UserModified
        
FROM  hips.Address a
LEFT OUTER JOIN hips.State AS st ON st.StateID = a.AustralianStateId
Inner JOIN hips.Country AS c ON c.CountryId = a.CountryId
INNER JOIN hips.AddressType AS at ON at.AddressTypeId = a.AddressTypeId
WHERE a.AddressId = @AddressId
    END
    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[AddressTypeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 12 Oct 2012
-- Description:   Gets a specific record from the AddressType Table
-- =============================================
CREATE PROCEDURE [hips].[AddressTypeGet] 
(
	@AddressTypeId INT = null
)
AS
BEGIN
SELECT  at.AddressTypeId,
		at.Code,
		at.Description,
		at.DateCreated,
		at.UserCreated,
		at.DateModified,
		at.UserModified
		
FROM  hips.AddressType at
WHERE at.AddressTypeId = @AddressTypeId OR @AddressTypeId IS NULL
ORDER BY at.Description asc
END
GO

DROP PROCEDURE [hips].[AddressUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 October 2012
-- Description:   Updates an existing record into the Addresss Table
-- =============================================
CREATE PROCEDURE [hips].[AddressUpdate] 
(      @AddressId INT
      ,@AddressLine1 VARCHAR(100)
      ,@AddressLine2 VARCHAR(100) = null
      ,@PlaceName VARCHAR(100) = null
      ,@AustralianStateId INT
      ,@InternationalStateCode VARCHAR(10) = NULL
      ,@Postcode VARCHAR(10) = null
      ,@CountryId INT
      ,@AddressTypeId INT
	  ,@UserModified VARCHAR(256)
	  ,@DateModified datetime
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
            	FROM [hips].[Address]
				WHERE [AddressId] = @AddressId AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'Address', N'AddressID', @AddressId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[Address]
           SET [AddressLine1] = @AddressLine1
			  ,[AddressLine2] = @AddressLine2
			  ,[PlaceName] = @PlaceName
			  ,[AustralianStateId] = @AustralianStateId
			  ,[InternationalStateCode] = @InternationalStateCode
			  ,[Postcode] = @Postcode
			  ,[CountryId] = @CountryId
			  ,[AddressTypeId] = @AddressTypeId
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified
         WHERE [AddressID] = @AddressID

        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
		SELECT  a.AddressId,
				a.AddressLine1,
				a.AddressLine2,
				a.PlaceName,
				a.AustralianStateId,
				st.Description AS AustralianStateName,
				a.InternationalStateCode,
				a.PostCode,
				a.CountryId,
				c.DESCRIPTION AS CountryName,
				a.AddressTypeId,
				at.DESCRIPTION AS AddressTypeDescription,
				a.DateCreated,
				a.UserCreated,
				a.DateModified,
				a.UserModified	
		FROM  hips.Address a
		LEFT OUTER JOIN hips.State AS st ON st.StateID = a.AustralianStateId
		Inner JOIN hips.Country AS c ON c.CountryId = a.CountryId
		INNER JOIN hips.AddressType AS at ON at.AddressTypeId = a.AddressTypeId
		WHERE a.AddressId = @AddressId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[CdaDocumentNumberInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   2 June 2014
-- Description:   Inserts a new record into the CdaDocumentNumber Table
-- =============================================
CREATE PROCEDURE [hips].[CdaDocumentNumberInsert] 
(
	@CdaSetNumberId INT,
	@DocumentCreationDateTime DATETIME,
	@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode INT
		,@CdaDocumentNumberId INT
		,@CurrentDate DATETIME

    SET @intErrorCode = 0
	SET @CurrentDate = GETDATE()
    
	INSERT INTO [hips].[CdaDocumentNumber]
         ( 
          [CdaSetNumberId]
         , [DocumentCreationDateTime]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         )
		VALUES
         ( 
           @CdaSetNumberId
         , @DocumentCreationDateTime
         , @CurrentDate
         , @UserModified
         , @CurrentDate
         , @UserModified
         )
    
    SELECT @intErrorCode = @@ERROR, @CdaDocumentNumberId = SCOPE_IdENTITY()
	
    IF @intErrorCode = 0
    BEGIN
        SELECT [CdaDocumentNumberId]
		 , [CdaSetNumberId]
         , [DocumentCreationDateTime]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         FROM [hips].[CdaDocumentNumber]
         WHERE [CdaDocumentNumberId] = @CdaDocumentNumberId
   end
   RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[CdaSetNumberGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   1 June 2014
-- Description:   Gets a specified record from the CdaSetNumber Table
-- =============================================
CREATE PROCEDURE [hips].[CdaSetNumberGet] 
(
	   @CdaSetNumberId INT = null
	  ,@EpisodeId INT = null
	  ,@DocumentTypeId int = null
) AS
BEGIN
SELECT
	csn.CdaSetNumberId,
	csn.EpisodeId,
	csn.DocumentTypeId,
	csn.DocumentFormatId,
	csn.ModeOfSeparationId,
	csn.AdmissionDateTime,
	csn.DischargeDateTime,
	csn.DateCreated,
	csn.UserCreated,
	csn.DateModified,
	csn.UserModified,
	(
		SELECT COUNT(CdaDocumentNumberId) 
		FROM hips.CdaDocumentNumber 
		WHERE CdaSetNumberId = csn.CdaSetNumberId
	) AS DocumentCount
FROM  hips.CdaSetNumber As csn
WHERE (csn.EpisodeId = @EpisodeId OR @EpisodeId IS NULL)
AND (csn.CdaSetNumberId = @CdaSetNumberId OR @CdaSetNumberId IS NULL)
AND (csn.DocumentTypeId = @DocumentTypeId OR @DocumentTypeId IS NULL)

END
GO

DROP PROCEDURE [hips].[CdaSetNumberInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   2 June 2014
-- Description:   Inserts a new record into the CdaSetNumber Table
-- =============================================
CREATE PROCEDURE [hips].[CdaSetNumberInsert] 
(
	  @EpisodeId INT
	, @DocumentTypeId INT
	, @DocumentFormatId INT
	, @ModeOfSeparationId INT
	, @AdmissionDateTime DATETIME = NULL
	, @DischargeDateTime DATETIME = NULL
	, @UserModified varchar(256)
) 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @CdaSetNumberId int

    SET @intErrorCode = 0

	INSERT INTO hips.CdaSetNumber
	(
		EpisodeId,
		DocumentTypeId,
		DocumentFormatId,
		ModeOfSeparationId,
		AdmissionDateTime,
		DischargeDateTime,
		DateCreated,
        UserCreated,
        DateModified,
        UserModified
		)
VALUES
	(
	  @EpisodeId
	, @DocumentTypeId
	, @DocumentFormatId
	, @ModeOfSeparationId
	, @AdmissionDateTime
	, @DischargeDateTime
	, GETDATE()
    , @UserModified
    , GETDATE()
    , @UserModified
	)
	
	 SELECT @intErrorCode = @@ERROR , @CdaSetNumberId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT csn.CdaSetNumberId,
			csn.EpisodeId,
			csn.DocumentTypeId,
			csn.DocumentFormatId,
			csn.ModeOfSeparationId,
			csn.AdmissionDateTime,
			csn.DischargeDateTime,
			csn.DateCreated,
			csn.UserCreated,
			csn.DateModified,
			csn.UserModified
		FROM  hips.CdaSetNumber AS csn
		WHERE CdaSetNumberId = @CdaSetNumberId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[CdaSetNumberUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   2 June 2014
-- Description:   Updates a record into the CdaSetNumber Table
-- =============================================
CREATE PROCEDURE [hips].[CdaSetNumberUpdate] 
(
	  @CdaSetNumberId INT
	, @EpisodeId INT
	, @DocumentTypeId INT
	, @DocumentFormatId INT
	, @ModeOfSeparationId INT
	, @AdmissionDateTime DATETIME
	, @DischargeDateTime DATETIME
	, @UserModified varchar(256)
	, @DateModified datetime
) AS
BEGIN
    SET NOCOUNT ON
	DECLARE @intErrorCode INT
    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
            	FROM [hips].[CdaSetNumber]
				WHERE [CdaSetNumberId] = @CdaSetNumberId AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30) 
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'CdaSetNumber', N'CdaSetNumberId', @CdaSetNumberId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
	
	IF @intErrorCode = 0
    BEGIN
		UPDATE hips.CdaSetNumber
		SET
				EpisodeId = @EpisodeId,
				DocumentTypeId = @DocumentTypeId,
				DocumentFormatId = @DocumentFormatId,
				ModeOfSeparationId = @ModeOfSeparationId,
				AdmissionDateTime = @AdmissionDateTime,
				DischargeDateTime = @DischargeDateTime,
				DateModified = GETDATE(),
				UserModified = @UserModified
		WHERE   CdaSetNumberId = @CdaSetNumberId

		SELECT @intErrorCode = @@ERROR
	END

    IF @intErrorCode = 0
    BEGIN
        SELECT csn.CdaSetNumberId,
			csn.EpisodeId,
			csn.DocumentTypeId,
			csn.DocumentFormatId,
			csn.ModeOfSeparationId,
			csn.AdmissionDateTime,
			csn.DischargeDateTime,
			csn.DateCreated,
			csn.UserCreated,
			csn.DateModified,
			csn.UserModified
		FROM  hips.CdaSetNumber AS csn
		WHERE CdaSetNumberId = @CdaSetNumberId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[ClinicalDocumentGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 7 March 2012
-- Description:   Gets a specified record from the ClinicalDocument Table
-- 08/10/2012 - changed Pcehr table to ClinicalDocument
-- 06/12/2012 - Added status and removal information
-- 16/06/2013 - Changed set ID length from 50 to 128
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentGet] 
(
	   @EpisodeId INT = null
	  ,@SourceSystemSetId Varchar(128) = null
	  ,@DocumentTypeId int = null
) AS
BEGIN
SELECT cd.ClinicalDocumentId,
		cd.SourceSystemSetId,
		cd.EpisodeId,
		cd.DocumentTypeId,
		dt.Description as DocumentTypeDescription,
		dt.Code as DocumentTypeCode,
		cd.RemovalReasonId,
		rr.Description as RemovalReasonDescription,
		cd.RemovedDate,
		cd.ClinicalDocumentStatusId,
		cd.DateCreated,
		cd.UserCreated,
		cd.DateModified,
		cd.UserModified
FROM  hips.ClinicalDocument As cd
INNER JOIN hips.DocumentType As dt on dt.DocumentTypeId = cd.DocumentTypeId
INNER JOIN hips.RemovalReason AS rr ON cd.RemovalReasonId = rr.RemovalReasonId
WHERE (EpisodeId = @EpisodeId OR @EpisodeId IS NULL)
AND (SourceSystemSetId = @SourceSystemSetId OR @SourceSystemSetId IS NULL)
AND (cd.DocumentTypeId = @DocumentTypeId OR @DocumentTypeId IS NULL)

END
GO

DROP PROCEDURE [hips].[ClinicalDocumentInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 7 March 2012
-- Description:   Inserts a new record into the PCEHR Table
-- 08/10/2012 - changed Pcehr -> ClinicalDocument
-- 06/12/2012 - Added status and removal information
-- 16/06/2013 - Changed set ID length from 50 to 128
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentInsert] 
(
	  @SourceSystemSetId varchar(128)
	, @EpisodeId INT
	, @DocumentTypeId INT
	, @RemovalReasonId INT
	, @RemovedDate DATETIME = NULL
	, @ClinicalDocumentStatusId INT
	, @UserModified varchar(256)
) 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @ClinicalDocumentId int

    SET @intErrorCode = 0


	INSERT INTO hips.ClinicalDocument
	(
		SourceSystemSetId,
		EpisodeId,
		DocumentTypeId,
		RemovalReasonId,
		RemovedDate,
		ClinicalDocumentStatusId,
		[DateCreated],
        [UserCreated],
        [DateModified],
        [UserModified]
		)
VALUES
	(
	@SourceSystemSetId
	, @EpisodeId
	, @DocumentTypeId
	, @RemovalReasonId
	, @RemovedDate
	, @ClinicalDocumentStatusId
	, GETDATE()
    , @UserModified
    , GETDATE()
    , @UserModified
	)
	
	 SELECT @intErrorCode = @@ERROR , @ClinicalDocumentId = SCOPE_IdENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [ClinicalDocumentId],
                SourceSystemSetId,
                EpisodeId,
                cd.DocumentTypeId,
				dt.Description as DocumentTypeDescription,
				dt.Code as DocumentTypeCode,
				cd.RemovalReasonId,
				rr.Description as RemovalReasonDescription,
				cd.RemovedDate,
				cd.ClinicalDocumentStatusId,
                cd.[DateCreated],
                cd.[UserCreated],
                cd.[DateModified],
                cd.[UserModified]
		FROM [hips].[ClinicalDocument] cd
		INNER JOIN hips.DocumentType As dt on dt.DocumentTypeId = cd.DocumentTypeId
		INNER JOIN hips.RemovalReason AS rr ON cd.RemovalReasonId = rr.RemovalReasonId
		WHERE [ClinicalDocumentId] = @ClinicalDocumentId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[ClinicalDocumentList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 02 April 2012
-- Description:   Gets all records from the Pcehrs Table
-- 08/10/2012 - Changed Pcehr table to ClinicalDocument
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentList] 
(
	@dateLastUpdated AS DATE = NULL
)
AS
BEGIN
SELECT  p.ClinicalDocumentId,
		p.SourceSystemSetId,
		p.EpisodeId,
		p.DocumentTypeId,
		p.DateCreated,
		p.UserCreated,
		p.DateModified,
		p.UserModified
		
FROM  hips.ClinicalDocument p
WHERE p.DateModified > @dateLastUpdated OR @dateLastUpdated IS NULL
END
GO

DROP PROCEDURE [hips].[ClinicalDocumentStatusGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 12 Oct 2012
-- Description:   Gets a specific record from the ClinicalDocument Table
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentStatusGet] 
(
	@ClinicalDocumentStatusId INT = null
)
AS
BEGIN
SELECT  c.ClinicalDocumentStatusId,
		c.Description,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified
		
FROM  hips.ClinicalDocumentStatus c
WHERE c.ClinicalDocumentStatusId = @ClinicalDocumentStatusId OR @ClinicalDocumentStatusId IS NULL
ORDER BY c.Description asc
END
GO

DROP PROCEDURE [hips].[ClinicalDocumentUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 13 March 2012
-- Description:   Updates a record in the ClinicalDocument Table
-- 08/10/2012 - change the Pcehr table to ClinicalDocument
-- 06/12/2012 - Added status and removal information
-- 16/06/2013 - Changed set ID length from 50 to 128
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentUpdate] 
(
	  @ClinicalDocumentId INT
	, @SourceSystemSetId VARCHAR(128)
	, @EpisodeId INT
	, @DocumentTypeId INT
	, @RemovalReasonId INT
	, @RemovedDate DATETIME = NULL
	, @ClinicalDocumentStatusId INT
	, @UserModified varchar(256)
	, @DateModified datetime
) AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
            	FROM [hips].[ClinicalDocument]
				WHERE [ClinicalDocumentId] = @ClinicalDocumentId AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'ClinicalDocument', N'ClinicalDocumentId', @ClinicalDocumentId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN

UPDATE hips.ClinicalDocument
SET
		SourceSystemSetId = @SourceSystemSetId,
		EpisodeId = @EpisodeId,
		DocumentTypeId = @DocumentTypeId,
		[RemovalReasonId] = @RemovalReasonId,
		[RemovedDate] = @RemovedDate,
		[ClinicalDocumentStatusId] = @ClinicalDocumentStatusId,
		[DateModified] = GetDate(),
        [UserModified] = @UserModified
WHERE   ClinicalDocumentId = @ClinicalDocumentId
        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
        SELECT [ClinicalDocumentId],
                SourceSystemSetId,
                EpisodeId,
                cd.DocumentTypeId,
				dt.Description as DocumentTypeDescription,
				dt.Code as DocumentTypeCode,
				cd.RemovalReasonId,
				rr.Description as RemovalReasonDescription,
				cd.RemovedDate,
				cd.ClinicalDocumentStatusId,
                cd.[DateCreated],
                cd.[UserCreated],
                cd.[DateModified],
                cd.[UserModified]
		FROM [hips].[ClinicalDocument] cd
		INNER JOIN hips.DocumentType As dt on dt.DocumentTypeId = cd.DocumentTypeId
		INNER JOIN hips.RemovalReason AS rr ON cd.RemovalReasonId = rr.RemovalReasonId
		WHERE [ClinicalDocumentId] = @ClinicalDocumentId
    END

    RETURN(@intErrorCode)


END
GO

DROP PROCEDURE [hips].[ClinicalDocumentUploadList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   16 April 2014
-- Description:   Gets all documents that have been uploaded, removed after being uploaded, pending upload and pending removal
--				  specific to a patient in a hospital. Documents which have never been uploaded will not appear.
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentUploadList]  
(
		@PatientId INT,
		@SourceSystemSetId VARCHAR(128) = NULL,
		@CurrentOnly BIT = 1,
		@DocumentTypeCode VARCHAR(128) = NULL
)
AS
BEGIN
	SELECT  hp.HospitalId,
			e.EpisodeId ,
			e.PatientId ,
			e.EpisodeTypeId,
			e.AdmissionDate,
			e.AdmissionReason,
			e.DischargeDate,
			e.ResponsibleProviderId,
			rp.FamilyName AS ResponsibleProviderFamilyName,
			rp.GivenNames AS ResponsibleProviderGivenNames,
			rpt.Description AS ResponsibleProviderTitle,
			rps.Description AS ResponsibleProviderSuffix,
			cd.ClinicalDocumentId ,
			cd.SourceSystemSetId ,
			cdv.ClinicalDocumentVersionId ,
			cdv.SourceSystemDocumentId , 
			cd.DocumentTypeId ,
			dt.Code AS DocumentTypeCode ,
			dt.Description AS DocumentTypeDescription,
			cd.ClinicalDocumentStatusId,
			cds.Description AS ClinicalDocumentStatusDescription,
			cdv.UploadedDate,
			cdv.SupersededDate,
			pcehrQueue.QueueStatusId, 
			pcehrQueue.QueueStatusDescription,
			pcehrQueue.QueueOperationId,
			pcehrQueue.QueueOperationDescription
	FROM    hips.HospitalPatient hp WITH (NOLOCK) 
	INNER JOIN hips.Episode e WITH (NOLOCK) ON hp.PatientId = e.PatientId
	INNER JOIN hips.ClinicalDocument cd WITH (NOLOCK) ON e.EpisodeId = cd.EpisodeId
	INNER JOIN hips.ClinicalDocumentStatus cds WITH (NOLOCK) ON cd.ClinicalDocumentStatusId = cds.ClinicalDocumentStatusId
	INNER JOIN hips.DocumentType dt WITH (NOLOCK) ON cd.DocumentTypeId = dt.DocumentTypeId
	INNER JOIN hips.HealthProviderIndividual rp WITH (NOLOCK) ON e.ResponsibleProviderId = rp.HealthProviderIndividualId
	INNER JOIN hips.Title rpt WITH (NOLOCK) ON rp.TitleId = rpt.TitleId
	INNER JOIN hips.Suffix rps WITH (NOLOCK) ON rp.SuffixId = rps.SuffixId
	INNER JOIN hips.ClinicalDocumentVersion cdv WITH (NOLOCK)  ON cd.ClinicalDocumentId = cdv.ClinicalDocumentId		
			OUTER APPLY ( SELECT TOP 1
									pmq.QueueStatusId ,
									pmq.QueueOperationId ,
									qs.Description AS QueueStatusDescription,
									qo.Description AS QueueOperationDescription
						  FROM      hips.PcehrMessageQueue AS pmq WITH (NOLOCK) 
									INNER JOIN hips.QueueStatus qs WITH (NOLOCK) ON qs.QueueStatusId = pmq.QueueStatusId
									INNER JOIN hips.QueueOperation qo WITH (NOLOCK) ON qo.QueueOperationId = pmq.QueueOperationId
						  WHERE     pmq.EpisodeId = e.EpisodeId
						  AND		pmq.SourceSystemSetId = cd.SourceSystemSetId
				-- Only consider operations that were queued after the last successful upload or remove of the document. We are not interested in older failures.
						  AND		pmq.DateCreated > cd.DateModified
						  AND		pmq.DateCreated > cdv.DateModified
						  ORDER BY  pmq.PcehrMessageQueueId DESC
						) AS pcehrQueue
	WHERE hp.PatientId = @PatientId 
	AND ( @SourceSystemSetId IS NULL OR cd.SourceSystemSetId = @SourceSystemSetId ) 
	AND ( @CurrentOnly = 0 OR cdv.SupersededDate IS NULL)
	AND ( @DocumentTypeCode IS NULL OR dt.Code = @DocumentTypeCode )
END

GO

DROP PROCEDURE [hips].[ClinicalDocumentVersionGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 12 Oct 2012
-- Description:   Gets one or more records from the ClinicalDocumentVersion table
-- Modified:
-- 06/12/2012 - Made possible to check for duplicate source system document ID
-- 07/12/2012 - Flag to get latest version only
-- 16/06/2013 - Changed set ID length from 50 to 128
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentVersionGet] 
(
	 @ClinicalDocumentId INT = NULL
	,@ClinicalDocumentVersionId INT = NULL
	,@SourceSystemDocumentId VARCHAR(128) = NULL
	,@LatestVersionOnly BIT
)
AS
BEGIN
IF @LatestVersionOnly = 0
SELECT  c.ClinicalDocumentVersionId,
		c.ClinicalDocumentId,
		c.SourceSystemDocumentId,
		c.UploadedDate,
		c.SupersededDate,
		c.Package,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified
FROM  hips.ClinicalDocumentVersion c
WHERE (c.ClinicalDocumentId = @ClinicalDocumentId OR @ClinicalDocumentId IS NULL)
AND (c.ClinicalDocumentVersionId = @ClinicalDocumentVersionId OR @ClinicalDocumentVersionId IS NULL)
AND (c.SourceSystemDocumentId = @SourceSystemDocumentId OR @SourceSystemDocumentId IS NULL)
ELSE
SELECT  TOP 1
		c.ClinicalDocumentVersionId,
		c.ClinicalDocumentId,
		c.SourceSystemDocumentId,
		c.UploadedDate,
		c.SupersededDate,
		c.Package,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified
FROM  hips.ClinicalDocumentVersion c
WHERE (c.ClinicalDocumentId = @ClinicalDocumentId OR @ClinicalDocumentId IS NULL)
AND (c.ClinicalDocumentVersionId = @ClinicalDocumentVersionId OR @ClinicalDocumentVersionId IS NULL)
AND (c.SourceSystemDocumentId = @SourceSystemDocumentId OR @SourceSystemDocumentId IS NULL)
ORDER BY c.ClinicalDocumentVersionId DESC

END
GO

DROP PROCEDURE [hips].[ClinicalDocumentVersionInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 11 Oct 2012
-- Description:   Inserts a new record into the Patient Master Table on the hips first
--					using the hips.ClinicalDocumentVersionInsert then into the hips table
-- Modified:
-- 06/12/2012 - Removed removal information (moved to ClinicalDocument table)
-- 16/06/2013 - Changed set ID length from 50 to 128
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentVersionInsert] 
(
    --hips fields
    @ClinicalDocumentId INT,
    @SourceSystemDocumentId varchar(128),
    @UploadedDate DATETIME = null,
    @SupersededDate datetime = null,
    @Package varbinary(MAX),
    @UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode INT
        ,@ClinicalDocumentVersionId INT
        ,@CurrentDate DATETIME

    SET @intErrorCode = 0
    SET @CurrentDate = GETDATE()


    
        INSERT
        INTO [hips].[ClinicalDocumentVersion]
         ( 
          [ClinicalDocumentId]
         , [SourceSystemDocumentId]
         , [UploadedDate]
         , [SupersededDate]
         , [Package]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         )
        VALUES
         ( 
         @ClinicalDocumentId
         , @SourceSystemDocumentId
         , @UploadedDate
         , @SupersededDate
         , @Package
         , @CurrentDate
         , @UserModified
         , @CurrentDate
         , @UserModified
         )

    
    SELECT @intErrorCode = @@ERROR, @ClinicalDocumentVersionId = SCOPE_IdENTITY()
    
    IF @intErrorCode = 0
    BEGIN
        SELECT [ClinicalDocumentVersionId]
         , [SourceSystemDocumentId]
         , [UploadedDate]
         , [SupersededDate]
         , [Package]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         FROM [hips].[ClinicalDocumentVersion]
         WHERE [ClinicalDocumentVersionId] = @ClinicalDocumentVersionId
   end
   RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[ClinicalDocumentVersionLocalGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   16 April 2014
-- Description:   Gets a single documents that has been uploaded to a patient in a hospital
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentVersionLocalGet]
(
		@PatientId INT,
		@SourceSystemSetId VARCHAR(128),
		@SourceSystemDocumentId VARCHAR(128) = NULL
)
AS
BEGIN
	SELECT  hp.HospitalId,
			e.EpisodeId ,
			e.PatientId ,
			e.EpisodeTypeId,
			e.AdmissionDate,
			e.AdmissionReason,
			e.DischargeDate,
			e.ResponsibleProviderId,
			rp.FamilyName AS ResponsibleProviderFamilyName,
			rp.GivenNames AS ResponsibleProviderGivenNames,
			rpt.Description AS ResponsibleProviderTitle,
			rps.Description AS ResponsibleProviderSuffix,
			cd.ClinicalDocumentId ,
			cd.SourceSystemSetId ,
			cdv.ClinicalDocumentVersionId ,
			cdv.SourceSystemDocumentId , 
			cd.DocumentTypeId ,
			dt.Code AS DocumentTypeCode ,
			dt.Description AS DocumentTypeDescription,
			cd.ClinicalDocumentStatusId,
			cds.Description AS ClinicalDocumentStatusDescription,
			cdv.UploadedDate,
			cdv.SupersededDate,
			cdv.Package
	FROM    hips.HospitalPatient hp WITH (NOLOCK) 
	INNER JOIN hips.Episode e WITH (NOLOCK) ON hp.PatientId = e.PatientId
	INNER JOIN hips.ClinicalDocument cd WITH (NOLOCK) ON e.EpisodeId = cd.EpisodeId
	INNER JOIN hips.ClinicalDocumentStatus cds WITH (NOLOCK) ON cd.ClinicalDocumentStatusId = cds.ClinicalDocumentStatusId
	INNER JOIN hips.DocumentType dt WITH (NOLOCK) ON cd.DocumentTypeId = dt.DocumentTypeId
	INNER JOIN hips.HealthProviderIndividual rp WITH (NOLOCK) ON e.ResponsibleProviderId = rp.HealthProviderIndividualId
	INNER JOIN hips.Title rpt WITH (NOLOCK) ON rp.TitleId = rpt.TitleId
	INNER JOIN hips.Suffix rps WITH (NOLOCK) ON rp.SuffixId = rps.SuffixId
	INNER JOIN hips.ClinicalDocumentVersion cdv WITH (NOLOCK)  ON cd.ClinicalDocumentId = cdv.ClinicalDocumentId		
	WHERE hp.PatientId = @PatientId 
	AND ( cd.SourceSystemSetId = @SourceSystemSetId ) 
	AND ( ( cdv.SourceSystemDocumentId = @SourceSystemDocumentId ) OR ( @SourceSystemDocumentId IS NULL AND cdv.SupersededDate IS NULL) )

END

GO

DROP PROCEDURE [hips].[ClinicalDocumentVersionUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 16 Nov 2012
-- Description:   Updates a record in the ClinicalDocumentVersion table - during a supersede or remove
-- 06/12/2012 - Removed removal information (moved to ClinicalDocument table)
-- 16/06/2013 - Changed set ID length from 50 to 128
-- =============================================
CREATE PROCEDURE [hips].[ClinicalDocumentVersionUpdate] 
(
    --hips fields
    @ClinicalDocumentVersionId INT,
    @ClinicalDocumentId INT,
    @SourceSystemDocumentId varchar(128),
    @UploadedDate DATETIME = null,
    @SupersededDate datetime = null,
    @Package varbinary(MAX),
    @UserModified varchar(256),
    @DateModified datetime
    
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode INT


    SET @intErrorCode = 0
    IF NOT EXISTS (SELECT TOP 1 [DateModified]
                FROM [hips].[ClinicalDocumentVersion]
                WHERE [ClinicalDocumentVersionId] = @ClinicalDocumentVersionId AND [DateModified] = @DateModified)
    BEGIN
        DECLARE @DateModifiedText NVARCHAR(30)
        SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
        RAISERROR (50001, -1, -1, N'ClinicalDocumentVersion', N'ClinicalDocumentVersionId', @ClinicalDocumentVersionId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN

        update
        [hips].[ClinicalDocumentVersion]
        set
          [SourceSystemDocumentId] = @SourceSystemDocumentId
         , [ClinicalDocumentId] = @ClinicalDocumentId
         , [UploadedDate] = @UploadedDate
         , [SupersededDate] = @SupersededDate
         , [Package] = @Package
         , [DateModified] = GetDate()
         , [UserModified] = @UserModified
        where ClinicalDocumentVersionId = @ClinicalDocumentVersionId

    
    SELECT @intErrorCode = @@ERROR 
    end
    
    IF @intErrorCode = 0
    BEGIN
        SELECT [ClinicalDocumentVersionId]
         , [ClinicalDocumentId]
         , [SourceSystemDocumentId]
         , [UploadedDate]
         , [SupersededDate]
         , [Package]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         FROM [hips].[ClinicalDocumentVersion]
         WHERE [ClinicalDocumentVersionId] = @ClinicalDocumentVersionId
   end
   RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[CodeSystemGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 Oct 2012
-- Description:   Gets a specific record from the Country Table
-- =============================================
CREATE PROCEDURE [hips].[CodeSystemGet] 
(
	@CodeSystemId INT = null
	,@DateModified DATETIME = null
)
AS
BEGIN
SELECT  c.CodeSystemId,
		c.Code,
		c.Description,
		c.Oid,
		c.Version,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified
		
FROM  hips.CodeSystem c
WHERE (c.CodeSystemId = @CodeSystemId OR @CodeSystemId IS NULL)
AND (c.DateModified < @DateModified OR @DateModified IS NULL)
ORDER BY c.Description asc
END
GO

DROP PROCEDURE [hips].[ConsentAuditGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 15 Oct 2012
-- Description:   Gets a specific record from the Country Table
-- Modified:    11/01/2013 changed UploadConsent to ConsentWithdrawn
-- =============================================
CREATE PROCEDURE [hips].[ConsentAuditGet] 
(
	@ConsentAuditId INT = null
)
AS
BEGIN
SELECT  c.ConsentAuditId,
		c.EpisodeId,
		c.ConsentWithdrawn,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified
		
FROM  hips.ConsentAudit c
WHERE c.ConsentAuditId = @ConsentAuditId OR @ConsentAuditId IS NULL
END
GO

DROP PROCEDURE [hips].[ConsentAuditInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 14 November 2012
-- Description:   Inserts a new record into the Consent Audit Table
-- Modified: 18/12/2012  added AuditInformation parameter
-- =============================================
CREATE PROCEDURE [hips].[ConsentAuditInsert] 
(
	  @EpisodeId INT
	, @ConsentWithdrawn BIT
	, @AuditInformation VARBINARY(MAX) = NULL
	, @UserModified varchar(256)
) 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @ConsentAuditId int

    SET @intErrorCode = 0


	INSERT INTO hips.ConsentAudit
	(
		[EpisodeId],
		[ConsentWithdrawn],
		[AuditInformation],
		[DateCreated],
        [UserCreated],
        [DateModified],
        [UserModified]
		)
VALUES
	(
	  @EpisodeId
	, @ConsentWithdrawn
	, @AuditInformation
	, GETDATE()
    , @UserModified
    , GETDATE()
    , @UserModified
	)
	
	 SELECT @intErrorCode = @@ERROR , @ConsentAuditId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [ConsentAuditId],
                EpisodeId,
                ConsentWithdrawn,
				AuditInformation,
                [DateCreated],
                [UserCreated],
                [DateModified],
                [UserModified]
         FROM [hips].[ConsentAudit]
         WHERE [ConsentAuditId] = @ConsentAuditId
    END

    RETURN(@intErrorCode)

END

GO

DROP PROCEDURE [hips].[ContactByPatientList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 October 2012
-- Description:   Gets all the Contact records for a patient
-- =============================================
CREATE PROCEDURE [hips].[ContactByPatientList] 
(
	@PatientMasterId INT
)
AS
BEGIN
	SELECT  c.ContactId,
			c.Detail,
			c.ContactMethodId,
			cm.Description AS ContactMethodDescription,
			cm.TelecommunicationType,
			cm.CdaType,
			c.DateCreated,
			c.UserCreated,
			c.DateModified,
			c.UserModified
			
	FROM  hips.PatientMasterContact as pmc
	LEFT OUTER JOIN hips.Contact c ON c.ContactId = pmc.ContactId
	LEFT OUTER JOIN hips.ContactMethod AS cm ON cm.ContactMethodId = c.ContactMethodId
	WHERE pmc.PatientMasterId = @PatientMasterId
END
GO

DROP PROCEDURE [hips].[ContactDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 October 2012
-- Description:   Deletes an existing record from the Contacts Table
-- =============================================
CREATE PROCEDURE [hips].[ContactDelete] 
(
	  @ContactId INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int,
			@intRowsAffected int

    SELECT  @intErrorCode = 0,
			@intRowsAffected = 0

    DELETE
     FROM [hips].[Contact]
     WHERE [ContactId] = @ContactId

    SELECT @intErrorCode = @@ERROR,
   		   @intRowsAffected = @@ROWCOUNT

	SELECT @intRowsAffected AS RowsAffected

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[ContactGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 Oct 2012
-- Description:   Gets a specific record from the Country Table
-- =============================================
CREATE PROCEDURE [hips].[ContactGet] 
(
	@ContactId INT = null
)
AS
BEGIN
	SELECT  c.ContactId,
			c.Detail,
			c.ContactMethodId,
			cm.TelecommunicationType,
			cm.Description,
			cm.CdaType,
			c.DateCreated,
			c.UserCreated,
			c.DateModified,
			c.UserModified
			
	FROM  hips.Contact c
	INNER JOIN hips.ContactMethod AS cm ON cm.ContactMethodId = c.ContactMethodId
	WHERE c.ContactId = @ContactId OR @ContactId IS NULL
END
GO

DROP PROCEDURE [hips].[ContactInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 Oct 2012
-- Description:   Gets a specific record from the Country Table
-- =============================================
CREATE PROCEDURE [hips].[ContactInsert] 
(
	@Detail VARCHAR(256),
	@ContactMethodId INT,
	@UserModified varchar(256)
)
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @ContactId int

    SET @intErrorCode = 0

	INSERT INTO hips.Contact
	        ( Detail ,
	          ContactMethodId ,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	        )
	VALUES  (	@Detail,
				@ContactMethodId,
				GETDATE(),
				@UserModified,
				GETDATE(),
				@UserModified
	
	        )
	SELECT @intErrorCode = @@ERROR , @ContactId = SCOPE_IdENTITY()
    IF @intErrorCode = 0
    BEGIN
		SELECT  c.ContactId,
				c.Detail,
				c.ContactMethodId,
				cm.TelecommunicationType,
				cm.Description,
				cm.CdaType,
				c.DateCreated,
				c.UserCreated,
				c.DateModified,
				c.UserModified
				
		FROM  hips.Contact c
		INNER JOIN hips.ContactMethod AS cm ON cm.ContactMethodId = c.ContactMethodId
		WHERE [ContactId] = @ContactId
    END

    RETURN(@intErrorCode)


END
GO

DROP PROCEDURE [hips].[ContactMethodInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 Oct 2012
-- Description:   Gets a specific record from the Country Table
-- =============================================
CREATE PROCEDURE [hips].[ContactMethodInsert] 
(
	@TelecommunicationType VARCHAR(10),
	@CdaType VARCHAR(10),
	@Description varchar(40),
	@UserModified varchar(256)
)
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @ContactMethodId int

    SET @intErrorCode = 0

	INSERT INTO hips.ContactMethod
	        ( TelecommunicationType ,
	          CdaType ,
	          Description,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	        )
	VALUES  (	@TelecommunicationType,
				@CdaType,
				@Description,
				GETDATE(),
				@UserModified,
				GETDATE(),
				@UserModified
	
	        )
	SELECT @intErrorCode = @@ERROR , @ContactMethodId = SCOPE_IdENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [ContactMethodId],
                TelecommunicationType,
                CdaType,
                Description,
                [DateCreated],
                [UserCreated],
                [DateModified],
                [UserModified]
         FROM [hips].[ContactMethod]
         WHERE [ContactMethodId] = @ContactMethodId
    END

    RETURN(@intErrorCode)


END
GO

DROP PROCEDURE [hips].[ContactUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 22 October 2012
-- Description:   Updates an existing record in the Contacts Table
-- =============================================
CREATE PROCEDURE [hips].[ContactUpdate] 
(      @ContactId INT
      ,@Detail VARCHAR(256)
      ,@ContactMethodId INT
	  ,@UserModified VARCHAR(256)
	  ,@DateModified datetime
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
            	FROM [hips].[Contact]
				WHERE [ContactId] = @ContactId AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30) 
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR) 
		RAISERROR (50001, -1, -1, N'Contact', N'ContactId', @ContactId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[Contact]
           SET [Detail] = @Detail
			  ,[ContactMethodId] = @ContactMethodId
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified
         WHERE [ContactID] = @ContactID

        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
		SELECT  c.ContactId,
				c.Detail,
				c.ContactMethodId,
				cm.TelecommunicationType,
				cm.Description,
				cm.CdaType,
				c.DateCreated,
				c.UserCreated,
				c.DateModified,
				c.UserModified
				
		FROM  hips.Contact c
		INNER JOIN hips.ContactMethod AS cm ON cm.ContactMethodId = c.ContactMethodId
		WHERE c.ContactId = @ContactId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[CountryCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the Country Code Table
-- =============================================
CREATE PROCEDURE [hips].[CountryCodeGet] 
(
	@CodeSystemCode VARCHAR,
	@CountryId INT = NULL
)
AS
BEGIN

	SELECT  c.CountryId, c.Code, c.Description
	FROM    hips.CountryCode cc
			JOIN hips.Country c ON cc.CountryId = c.CountryId
			JOIN hips.CodeSystem cs ON cc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND c.CountryId = @CountryId OR @CountryId IS NULL
	ORDER BY c.Description

END
GO

DROP PROCEDURE [hips].[CountryGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 29 March 2012
-- Description:   Gets a specific record from the Country Table
-- =============================================
CREATE PROCEDURE [hips].[CountryGet] 
(
	@CountryId INT = null
)
AS
BEGIN
SELECT  c.CountryId,
		c.Code,
		c.Description,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified
		
FROM  hips.Country c
WHERE c.CountryId = @CountryId OR @CountryId IS NULL
ORDER BY c.Description asc
END
GO

DROP PROCEDURE [hips].[DisclosureAuditGet]
GO
/****** Object:  StoredProcedure [hips].[DisclosureAuditGet]    Script Date: 12/18/2012 15:47:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================

-- Create date: 15 Oct 2012
-- Description:   Gets a specific record from the Country Table
-- =============================================
CREATE PROCEDURE [hips].[DisclosureAuditGet] 
(
	@DisclosureAuditId INT = null
)
AS
BEGIN
SELECT  c.DisclosureAuditId,
		c.HealthProviderOrganisationId,
		c.PatientMasterId,
		c.PcehrDisclosed,
		c.AuditInformation,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified
		
FROM  hips.DisclosureAudit c
WHERE c.DisclosureAuditId = @DisclosureAuditId OR @DisclosureAuditId IS NULL
END
GO

DROP PROCEDURE [hips].[DisclosureAuditInsert]
GO

/****** Object:  StoredProcedure [hips].[DisclosureAuditInsert]    Script Date: 12/18/2012 15:45:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 18 Dec 2012
-- Description:   Inserts a new record into the Disclosure Audit Table
-- =============================================
CREATE PROCEDURE [hips].[DisclosureAuditInsert] 
(
	  @HealthProviderOrganisationId INT
	, @PatientMasterId INT
	, @PcehrDisclosed BIT
	, @AuditInformation VARBINARY(MAX) = NULL
	, @UserModified varchar(256)
) 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @DisclosureAuditId int

    SET @intErrorCode = 0


	INSERT INTO hips.DisclosureAudit
	(
		[HealthProviderOrganisationId],
		[PatientMasterId],
		[PcehrDisclosed],
		[AuditInformation],
		[DateCreated],
        [UserCreated],
        [DateModified],
        [UserModified]
		)
VALUES
	(
	  @HealthProviderOrganisationId
	, @PatientMasterId
	, @PcehrDisclosed
	, @AuditInformation
	, GETDATE()
    , @UserModified
    , GETDATE()
    , @UserModified
	)
	
	 SELECT @intErrorCode = @@ERROR , @DisclosureAuditId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [DisclosureAuditId],
                HealthProviderOrganisationId,
                PatientMasterId,
				PcehrDisclosed,
				AuditInformation,
                [DateCreated],
                [UserCreated],
                [DateModified],
                [UserModified]
         FROM [hips].[DisclosureAudit]
         WHERE [DisclosureAuditId] = @DisclosureAuditId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[DocumentFormatGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Credte ddte: 11 Jun 2013
-- Description:   Gets a specific record or all records from the DocumentFormat table.
-- =============================================
CREatE PROCEDURE [hips].[DocumentFormatGet] 
(
	@DocumentFormatId INT = null
)
AS
BEGIN
SELECT  df.DocumentFormatId,
		df.Code,
		df.Description,
		df.CodeSystemId,
		cs.Code CodeSystemCode,
		cs.Description CodeSystemDescription,
		df.DateCreated,
		df.UserCreated,
		df.DateModified,
		df.UserModified		
FROM  hips.DocumentFormat df
INNER JOIN hips.CodeSystem cs ON cs.CodeSystemId = df.CodeSystemId
WHERE (df.DocumentFormatId = @DocumentFormatId OR @DocumentFormatId IS NULL)
ORDER BY df.Code asc
END
GO

DROP PROCEDURE [hips].[DocumentTypeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Created date:  12 Oct 2012
-- Description:   Gets all records or a specific record from the DocumentType table
-- Modified:
-- 04/12/2013 - Return repository information
-- =============================================
CREatE PROCEDURE [hips].[DocumentTypeGet] 
(
	@DocumentTypeId INT = null
)
AS
BEGIN
SELECT  dt.DocumentTypeId,
		dt.Description,
		dt.Code,
		dt.DateCreated,
		dt.UserCreated,
		dt.DateModified,
		dt.UserModified,
		dt.RepositoryId,
		r.Description AS RepositoryDescription,
		r.RepositoryUniqueId
		
FROM  hips.DocumentType dt
INNER JOIN hips.Repository r ON dt.RepositoryId = r.RepositoryId
WHERE dt.DocumentTypeId = @DocumentTypeId OR @DocumentTypeId IS NULL
ORDER BY dt.Description asc
END
GO

DROP PROCEDURE [hips].[DownloadedDocumentGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   12 Oct 2012
-- Description:   Gets a specific record from the DocumentType Table
-- Modified:
-- 10/03/2013 - Extend to allow get by patient master or source system document id
-- =============================================
CREATE PROCEDURE [hips].[DownloadedDocumentGet] 
(
	@DownloadedDocumentId INT = NULL
	,@PatientMasterId INT = NULL
	,@SourceSystemDocumentId VARCHAR(256) = NULL
)
AS
BEGIN
SELECT  dd.DownloadedDocumentId,
		dd.PatientMasterId,
		dd.SourceSystemDocumentId,
		dd.Package,
		dd.DownloadedDate,
		dd.ClinicalDocumentStatusId,
		cs.Description,
		dd.CurrentDocumentId,
		dd.DateCreated,
		dd.UserCreated,
		dd.DateModified,
		dd.UserModified
		
FROM  hips.DownloadedDocument dd
INNER JOIN hips.ClinicalDocumentStatus AS cs ON dd.ClinicalDocumentStatusId = cs.ClinicalDocumentStatusId
WHERE (dd.DownloadedDocumentId = @DownloadedDocumentId OR @DownloadedDocumentId IS NULL)
AND (dd.PatientMasterId = @PatientMasterId OR @PatientMasterId IS NULL)
AND (dd.SourceSystemDocumentId = @SourceSystemDocumentId OR @SourceSystemDocumentId IS NULL)

END
GO

DROP PROCEDURE [hips].[DownloadedDocumentInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 12 Oct 2012
-- Description:   Gets a specific record from the DocumentType Table
-- Modified:
-- 10/03/2013 - changed current document ID to allow nulls
-- =============================================
CREatE PROCEDURE [hips].[DownloadedDocumentInsert] 
(
	@PatientMasterId INT,
	@SourceSystemDocumentId VARCHAR(256),
	@Package VARBINARY(MAX),
	@DownloadedDate DATETIME,
	@ClinicalDocumentStatusId INT,
	@CurrentDocumentId INT = NULL,
	@UserModified varchar(256)
	
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @DownloadedDocumentId int

    SET @intErrorCode = 0


	INSERT INTO hips.DownloadedDocument
	(
		PatientMasterId,
		SourceSystemDocumentId,
		Package,
		DownloadedDate,
		ClinicalDocumentStatusId,
		CurrentDocumentId,
		[DateCreated],
        [UserCreated],
        [DateModified],
        [UserModified]
		)
VALUES
	(
	@PatientMasterId
	, @SourceSystemDocumentId
	, @Package
	, @DownloadedDate
	, @ClinicalDocumentStatusId
	, @CurrentDocumentId
	, GETDATE()
    , @UserModified
    , GETDATE()
    , @UserModified
	)
	
	SELECT @intErrorCode = @@ERROR , @DownloadedDocumentId = SCOPE_IdENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [DownloadedDocumentId],
				[PatientMasterId],
                [SourceSystemDocumentId],
                [Package],
                [DownloadedDate],
                [ClinicalDocumentStatusId],
                [CurrentDocumentId],
                [DateCreated],
                [UserCreated],
                [DateModified],
                [UserModified]
         FROM [hips].[DownloadedDocument]
         WHERE [DownloadedDocumentId] = @DownloadedDocumentId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[EpisodeDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: Friday, 22 June 2012 4:31 PM
-- Description:   Deletes an existing record from the Episode Table
-- =============================================
CREATE PROCEDURE [hips].[EpisodeDelete]
(
@EpisodeId INT
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @intErrorCode INT,
			@intRowsAffected INT
	SELECT  @intErrorCode = 0,
			@intRowsAffected = 0
	DELETE
		[hips].[Episode]
	WHERE [EpisodeId] = @EpisodeId
	
	SELECT  @intErrorCode = @@ERROR,
			@intRowsAffected = @@ROWCOUNT

	SELECT @intRowsAffected AS RowsAffected
	RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[EpisodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 30 March 2012
-- Description:   Gets a specific record/records from the Episode Table
-- Modified:    22/11/2012  added Ward,Room,Bed
-- Modified:    18/12/2012  changed UploadConsent to ConsentWithdrawn
-- Modified:    15/09/2013  Added BlockCancelledEpisodeLifeCycles to remove cancelled episodes
-- =============================================
CREATE PROCEDURE [hips].[EpisodeGet] 
(
	@EpisodeId INT = NULL,
	@PatientId INT = NULL,
	@DateLastUpdated DATETIME = NULL,
	@BlockCancelledEpisodeLifeCycles BIT = 0
)
AS
BEGIN
SELECT e.EpisodeId
	, e.PatientId
	, e.SourceSystemEpisodeId
	, e.AdmissionDate
	, e.AdmissionReason
	, e.DischargeDate
	, e.ResponsibleProviderId
	, e.EpisodeTypeId
	, et.Description AS EpisodeType
	, e.EpisodeLifecycleId
	, ec.Description AS EpisodeLifecycle
	, e.ConsentWithdrawn
	, e.Ward
	, e.Room
	, e.Bed
	, e.DateCreated
	, e.UserCreated
	, e.DateModified
	, e.UserModified
FROM hips.Episode e
LEFT JOIN [hips].[EpisodeType] AS et on et.EpisodeTypeId = e.EpisodeTypeId
LEFT JOIN [hips].[EpisodeLifecycle] AS ec on ec.EpisodeLifecycleId = e.EpisodeLifecycleId
WHERE (@EpisodeId IS NULL OR e.EpisodeId = @EpisodeId)
AND   (@PatientId IS NULL OR e.PatientId = @PatientId)
AND   (@BlockCancelledEpisodeLifeCycles=0 OR (e.EpisodeLifecycleId NOT IN (4,5,6,10,12))) 	 
AND   (@DateLastUpdated IS NULL OR e.DateModified > @DateLastUpdated)
ORDER BY e.AdmissionDate DESC

END
GO

DROP PROCEDURE [hips].[EpisodeInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 23 October 2012
-- Description:   Inserts a new record into the Episode Table
-- Modified:    22/11/2012  added Ward,Room,Bed
-- Modified:    18/12/2012  changed UploadConsent to ConsentWithdrawn
-- =============================================

CREATE PROCEDURE [hips].[EpisodeInsert] 
(
	@PatientId INT,
	@SourceSystemEpisodeId VARCHAR(100),
	@AdmissionDate DATETIME,
	@AdmissionReason VARCHAR(1000) = NULL,
	@DischargeDate DATETIME = NULL,
	@ResponsibleProviderId INT,
	@EpisodeTypeId INT,
	@EpisodeLifecycleId INT = NULL,
	@ConsentWithdrawn BIT,
	@Ward VARCHAR(40) = NULL,
	@Room VARCHAR(40) = NULL,
	@Bed VARCHAR(40) = NULL,
	@UserModified VARCHAR(256)
) 
AS
BEGIN
	SET NOCOUNT ON

   DECLARE @intErrorCode INT
          , @EpisodeId INT
          
    SET @intErrorCode = 0
	INSERT INTO [hips].[Episode]
	        ( PatientId,
	          SourceSystemEpisodeId,
	          AdmissionDate,
	          AdmissionReason,
	          DischargeDate,
	          ResponsibleProviderId,
	          EpisodeTypeId,
	          EpisodeLifecycleId,
	          ConsentWithdrawn,
			  Ward,
			  Room,
			  Bed,
	          DateCreated,
	          UserCreated,
	          DateModified,
	          UserModified
	        )
	VALUES  ( @PatientId,
	          @SourceSystemEpisodeId, 
	          @AdmissionDate, 
	          @AdmissionReason,
	          @DischargeDate, 
	          @ResponsibleProviderId, 
	          @EpisodeTypeId,
	          @EpisodeLifecycleId,
	          @ConsentWithdrawn,
			  @Ward,
			  @Room,
			  @Bed,
	          GETDATE(), 
	          @UserModified, 
	          GETDATE(), 
	          @UserModified  
	        )
	        
	 SELECT @intErrorCode = @@ERROR , @EpisodeId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
		SELECT e.EpisodeId
			,e.PatientId
			,e.SourceSystemEpisodeId
			,e.AdmissionDate
			,e.AdmissionReason
			,e.DischargeDate
			,e.ResponsibleProviderId
			,e.EpisodeTypeId
			,et.Description AS EpisodeType
			,e.EpisodeLifecycleId
			,ec.Description AS EpisodeLifecycle
			,e.ConsentWithdrawn
			,e.Ward
			,e.Room
			,e.Bed
			,e.DateCreated
			,e.UserCreated
			,e.DateModified
			,e.UserModified
		FROM hips.Episode e
		LEFT JOIN [hips].[EpisodeType] AS et on et.EpisodeTypeId = e.EpisodeTypeId
		LEFT JOIN [hips].[EpisodeLifecycle] AS ec on ec.EpisodeLifecycleId = e.EpisodeLifecycleId
		WHERE @EpisodeId = e.EpisodeId
    END

    RETURN(@intErrorCode)

	

END
GO

DROP PROCEDURE [hips].[EpisodeLifecycleGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 22 November 2012
-- Description:   Gets a specific record from the EpisodeLifecycle Table
-- =============================================
CREATE PROCEDURE [hips].[EpisodeLifecycleGet] 
(
	@EpisodeLifecycleId INT = NULL
	,@DateModified DATETIME = NULL
)
AS
BEGIN
SELECT  s.EpisodeLifecycleId,
		s.Description,
		s.DateCreated,
		s.UserCreated,
		s.DateModified,
		s.UserModified
		
FROM  hips.EpisodeLifecycle s
WHERE (s.EpisodeLifecycleId = @EpisodeLifecycleId OR @EpisodeLifecycleId IS NULL)
AND (s.DateModified > @DateModified OR @DateModified IS NULL)
END
GO

DROP PROCEDURE [hips].[EpisodePatientExtendedDetailsGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   17 Apr 2014
-- Description:   Gets an Episode for a patient in hospital with Patient and Consent.
-- =============================================
CREATE PROCEDURE [hips].[EpisodePatientExtendedDetailsGet]
    (
      @HospitalCodeSystem VARCHAR(20),
      @PatientId INT,
      @SourceSystemEpisodeId VARCHAR(100)
    )
AS 
    BEGIN

        SELECT  e.EpisodeId ,
				hp.Mrn ,
				pm.CurrentSexId,
				pm.DateOfBirth,
				pm.MedicareNumber,
				pm.MedicareIrn,
				pm.DvaNumber,
				pmn.FamilyName,
				pmn.GivenNames,
				pmnt.Description AS Title,
				pmns.Description AS Suffix,
				hc.Code as HospitalCode,
				h.HospitalId,
				h.Name as HospitalName,
				e.Ward,
				e.Room,
				e.Bed,
                e.AdmissionDate ,
                e.AdmissionReason , 
                e.DischargeDate ,
				rpi.PasProviderIdentifier AS ResponsibleProviderIdentifier,
                rp.FamilyName AS ResponsibleProviderFamilyName ,
                rp.GivenNames AS ResponsibleProviderGivenNames ,
                rpt.Description AS ResponsibleProviderTitle ,
                rps.Description AS ResponsibleProviderSuffix ,
                e.SourceSystemEpisodeId ,
                e.ConsentWithdrawn ,
                (SELECT TOP 1 ca.AuditInformation FROM hips.ConsentAudit ca WHERE e.EpisodeId = ca.EpisodeId ORDER BY ConsentAuditId DESC) AS WithdrawalAuditInformation
        FROM    hips.HospitalPatient hp
                INNER JOIN hips.Episode e ON hp.PatientId = e.PatientId
                INNER JOIN hips.HospitalCode hc	ON hp.HospitalId = hc.HospitalId	
					AND hc.CodeSystemId = (SELECT CodeSystemId FROM hips.CodeSystem WHERE Code = @HospitalCodeSystem)
				INNER JOIN hips.Hospital h ON hp.HospitalId = h.HospitalId
                INNER JOIN hips.HealthProviderIndividual rp ON e.ResponsibleProviderId = rp.HealthProviderIndividualId
				INNER JOIN HIPS.HospitalHealthProviderIndividual rpi ON rp.HealthProviderIndividualId = rpi.HealthProviderIndividualId
                INNER JOIN hips.Title rpt ON rp.TitleId = rpt.TitleId
                INNER JOIN hips.Suffix rps ON rp.SuffixId = rps.SuffixId
                INNER JOIN hips.PatientMaster pm ON hp.PatientMasterId = pm.PatientMasterId
				INNER JOIN hips.PatientMasterName pmn ON pm.PatientMasterId = pmn.PatientMasterId AND pmn.NameTypeId = 2 -- Current Name
                LEFT JOIN hips.Title pmnt ON pmn.TitleId = pmnt.TitleId
				LEFT JOIN hips.Suffix pmns ON pmn.SuffixId = pmns.SuffixId
        WHERE   e.PatientId = @PatientId
		AND		e.SourceSystemEpisodeId = @SourceSystemEpisodeId

     END

GO

DROP PROCEDURE [hips].[EpisodeProviderGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 06 June 2013
-- Description:   Gets a specific record/records from the Episode and Provider Table
-- Modified:    08/04/2014 Added BlockCancelledEpisodeLifeCycles to remove cancelled episodes


-- =============================================
CREATE PROCEDURE [hips].[EpisodeProviderGet] 
(
	@EpisodeId INT = NULL,
	@PatientId INT = NULL,
	@DateLastUpdated DATETIME = NULL,
	@BlockCancelledEpisodeLifeCycles BIT = 0

)
AS
BEGIN


SELECT DISTINCT 
		hcp.HealthProviderIndividualId,
		hcp.IamProviderIdentifier,
		hcp.HpiI,
		hcp.HpiILastValidatedDate,		
		hcp.TitleId,
		t.Description AS Title,		
		hcp.FamilyName,
		hcp.GivenNames,		
		hcp.SuffixId,
		s.Description AS Suffix,
		hcp.DateCreated,
		hcp.UserCreated,
		hcp.DateModified,
		hcp.UserModified
FROM hips.Episode e
INNER JOIN hips.HealthProviderIndividual AS hcp ON hcp.HealthProviderIndividualId = e.ResponsibleProviderId
INNER JOIN hips.Title AS t ON t.TitleId = hcp.TitleId
INNER JOIN hips.Suffix AS s ON s.SuffixId = hcp.SuffixId
WHERE (@EpisodeId IS NULL OR e.EpisodeId = @EpisodeId)
AND   (@PatientId IS NULL OR e.PatientId = @PatientId)
AND   (@BlockCancelledEpisodeLifeCycles=0 OR (e.EpisodeLifecycleId NOT IN (4,5,6,10,12))) 	 
AND   (@DateLastUpdated IS NULL OR e.DateModified > @DateLastUpdated)

END
GO

DROP PROCEDURE [hips].[EpisodeTypeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 19 October 2012
-- Description:   Gets a specific record from the EpisodeType Table
-- =============================================
CREATE PROCEDURE [hips].[EpisodeTypeGet] 
(
	@EpisodeTypeId INT = NULL
	,@DateModified DATETIME = NULL
)
AS
BEGIN
SELECT  s.EpisodeTypeId,
		s.Code,
		s.Description,
		s.DateCreated,
		s.UserCreated,
		s.DateModified,
		s.UserModified
		
FROM  hips.EpisodeType s
WHERE (s.EpisodeTypeId = @EpisodeTypeId OR @EpisodeTypeId IS NULL)
AND (s.DateModified > @DateModified OR @DateModified IS NULL)
END
GO

DROP PROCEDURE [hips].[EpisodeUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 23 October 2012
-- Description:   Updates a record in the Episodes Table
-- Modified:    22/11/2012  added Ward,Room,Bed
-- Modified:    18/12/2012  changed UploadConsent to ConsentWithdrawn
-- Modified:    31/07/2013  clear the discharge date when patient is admitted as inpatient after being discharged from emergency
-- =============================================
CREATE PROCEDURE [hips].[EpisodeUpdate] 
(
    @EpisodeId INT,
    @PatientId INT,
    @SourceSystemEpisodeId VARCHAR(100),
    @AdmissionDate DATETIME,
    @AdmissionReason VARCHAR(1000) = NULL,
    @DischargeDate DATETIME = NULL,
    @ResponsibleProviderId INT,
    @EpisodeTypeId INT,
    @EpisodeLifecycleId INT = NULL,
    @ConsentWithdrawn BIT,
    @Ward VARCHAR(40) = NULL,
    @Room VARCHAR(40) = NULL,
    @Bed VARCHAR(40) = NULL,
    @UserModified varchar(256),
    @DateModified datetime
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
                FROM [hips].[Episode]
                WHERE [EpisodeId] = @EpisodeId AND [DateModified] = @DateModified)
    BEGIN
        DECLARE @DateModifiedText NVARCHAR(30)
        SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
        RAISERROR (50001, -1, -1, N'Episode', N'EpisodeId', @EpisodeId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        IF @AdmissionDate = @DischargeDate AND @EpisodeLifecycleId = 11
        BEGIN
            SET @DischargeDate = NULL
        END

        UPDATE [hips].[Episode]
           SET [PatientId] = @PatientId
            ,[SourceSystemEpisodeId] = @SourceSystemEpisodeId
            ,[AdmissionDate] = @AdmissionDate
            ,[AdmissionReason] = @AdmissionReason
            ,[DischargeDate] = @DischargeDate
            ,[ResponsibleProviderId] = @ResponsibleProviderId
            ,[EpisodeTypeId] = @EpisodeTypeId
            ,[EpisodeLifecycleId] = @EpisodeLifecycleId
            ,[ConsentWithdrawn] = @ConsentWithdrawn
            ,[Ward] = @Ward
            ,[Room] = @Room
            ,[Bed] = @Bed
            ,[DateModified] = GetDate()
            ,[UserModified] = @UserModified
         WHERE [EpisodeId] = @EpisodeId

        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
        SELECT e.EpisodeId
            ,e.PatientId
            ,e.SourceSystemEpisodeId
            ,e.AdmissionDate
            ,e.AdmissionReason
            ,e.DischargeDate
            ,e.ResponsibleProviderId
            ,e.EpisodeTypeId
            ,et.Description AS EpisodeType
            ,e.EpisodeLifecycleId
            ,ec.Description AS EpisodeLifecycle
            ,e.ConsentWithdrawn
            ,e.Ward
            ,e.Room
            ,e.Bed
            ,e.DateCreated
            ,e.UserCreated
            ,e.DateModified
            ,e.UserModified
        FROM hips.Episode e
        LEFT JOIN [hips].[EpisodeType] AS et on et.EpisodeTypeId = e.EpisodeTypeId
        LEFT JOIN [hips].[EpisodeLifecycle] AS ec on ec.EpisodeLifecycleId = e.EpisodeLifecycleId
        WHERE @EpisodeId = e.EpisodeId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HealthProviderIndividualDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 02 April 2012
-- Description:   Deletes an existing record into the HealthProviderIndividuals Table
-- =============================================
CREATE PROCEDURE [hips].[HealthProviderIndividualDelete] 
(
	  @HealthProviderIndividualID INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int,
			@intRowsAffected int

    SELECT  @intErrorCode = 0,
			@intRowsAffected = 0

    DELETE
     FROM [hips].[HealthProviderIndividual]
     WHERE [HealthProviderIndividualID] = @HealthProviderIndividualID

    SELECT @intErrorCode = @@ERROR,
   		   @intRowsAffected = @@ROWCOUNT

	SELECT @intRowsAffected AS RowsAffected

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HealthProviderIndividualGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 2 April 2012
-- Description:   Gets a record from the HealthProviderIndividual Table
-- Modified:-
-- 22/10/2012  Changed to work with Hospital and PAS code
--						    HealthProviderIndividualId is not required at the moment
-- 24/04/2012  Changed LEFT OUTER to INNER joins to avoid locking with transactions
-- =============================================
CREATE PROCEDURE [hips].[HealthProviderIndividualGet] 
(
	@HealthProviderIndividualId INT = NULL,
	@HospitalId INT = NULL,
	@PasProviderIdentifier VARCHAR(30) = NULL
) 
AS
BEGIN
	SELECT
		hcp.HealthProviderIndividualId,
		hcp.IamProviderIdentifier,
		hcp.HpiI,
		hcp.HpiILastValidatedDate,		
		hcp.TitleId,
		t.Description AS Title,		
		hcp.FamilyName,
		hcp.GivenNames,		
		hcp.SuffixId,
		s.Description AS Suffix,
		hcp.DateCreated,
		hcp.UserCreated,
		hcp.DateModified,
		hcp.UserModified
	FROM hips.HospitalHealthProviderIndividual AS hhpi
	INNER JOIN hips.HealthProviderIndividual AS hcp ON hcp.HealthProviderIndividualId = hhpi.HealthProviderIndividualId
	INNER JOIN hips.Title AS t ON t.TitleId = hcp.TitleId
	INNER JOIN hips.Suffix AS s ON s.SuffixId = hcp.SuffixId
	WHERE (@HospitalId = hhpi.HospitalId OR @HospitalId IS NULL)
	AND (@PasProviderIdentifier = hhpi.PasProviderIdentifier OR @PasProviderIdentifier IS NULL)
	AND (@HealthProviderIndividualId = hcp.HealthProviderIndividualId OR @HealthProviderIndividualId IS NULL)
END
GO

DROP PROCEDURE [hips].[HealthProviderIndividualInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 2 April 2012
-- Description:   Inserts a new record into the Health Care Provider Table
-- 22/10/2012 Changed return the data fully populated
-- =============================================

CREATE PROCEDURE [hips].[HealthProviderIndividualInsert] 
(
	@IamProviderIdentifier VARCHAR(30) = NULL,
	@HpiI VARCHAR(16) = NULL,
	@HpiILastValidatedDate DATETIME = NULL,
	@TitleId INT,
	@FamilyName VARCHAR(80),
	@GivenNames VARCHAR(100),
	@SuffixId INT,
	@UserModified VARCHAR(256)
) 
AS
BEGIN
	SET NOCOUNT ON

   DECLARE @intErrorCode int
          , @HealthProviderIndividualId INT
          
    SET @intErrorCode = 0
	INSERT INTO HealthProviderIndividual
	        ( IamProviderIdentifier ,
	          HpiI ,
	          HpiILastValidatedDate ,
	          TitleId ,
	          FamilyName ,
	          GivenNames ,
	          SuffixId ,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	        )
	VALUES  ( @IamProviderIdentifier ,
	          @HpiI , 
	          @HpiILastValidatedDate , 
	          @TitleId ,
	          @FamilyName , 
	          @GivenNames , 
	          @SuffixId , 
	          GETDATE() , 
	          @UserModified , 
	          GETDATE() , 
	          @UserModified  
	        )
	        
	 SELECT @intErrorCode = @@ERROR , @HealthProviderIndividualId = SCOPE_IdENTITY()
    IF @intErrorCode = 0
    BEGIN
	SELECT
		hcp.HealthProviderIndividualId,
		hcp.IamProviderIdentifier,
		hcp.HpiI,
		hcp.HpiILastValidatedDate,
		
		hcp.TitleId,
		t.Description AS Title,
		
		hcp.FamilyName,
		hcp.GivenNames,
		
		hcp.SuffixId,
		s.Description AS Suffix,
		hcp.DateCreated,
		hcp.UserCreated,
		hcp.DateModified,
		hcp.UserModified
		
	FROM hips.HealthProviderIndividual AS hcp 
	LEFT OUTER JOIN hips.Title AS t ON t.TitleId = hcp.TitleId
	LEFT OUTER JOIN hips.Suffix AS s ON s.SuffixId = hcp.SuffixId
	WHERE @HealthProviderIndividualId = hcp.HealthProviderIndividualId

    END

    RETURN(@intErrorCode)

	

END
GO

DROP PROCEDURE [hips].[HealthProviderIndividualUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 16 October 2012
-- Description:   Updates a record into the HealthProviderIndividuals Table
-- =============================================
CREATE PROCEDURE [hips].[HealthProviderIndividualUpdate] 
(
	@HealthProviderIndividualId INT,
	@IamProviderIdentifier VARCHAR(30) = NULL,
    @HpiI VARCHAR(16) = NULL,
    @HpiILastValidatedDate DATETIME = NULL,
    @TitleId INT = NULL,
    @FamilyName VARCHAR(80),
    @GivenNames VARCHAR (100),
    @SuffixId INT = NULL,
	@UserModified varchar(256),
	@DateModified datetime
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
            	FROM [hips].[HealthProviderIndividual]
				WHERE [HealthProviderIndividualId] = @HealthProviderIndividualId AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'HealthProviderIndividual', N'HealthProviderIndividualId', @HealthProviderIndividualId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[HealthProviderIndividual]
           SET [IamProviderIdentifier] = @IamProviderIdentifier
            ,[HpiI] = @HpiI
            ,[HpiILastValidatedDate] = @HpiILastValidatedDate
			,[TitleId] = @TitleId
			,[FamilyName] = @FamilyName
			,[GivenNames] = @GivenNames
			,[SuffixId] = @SuffixId
            ,[DateModified] = GetDate()
            ,[UserModified] = @UserModified
         WHERE [HealthProviderIndividualId] = @HealthProviderIndividualId

        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
		SELECT
			hcp.HealthProviderIndividualId,
			hcp.IamProviderIdentifier,
			hcp.HpiI,
			hcp.HpiILastValidatedDate,
			hcp.TitleId,
			t.Description AS Title,
			hcp.FamilyName,
			hcp.GivenNames,
			hcp.SuffixId,
			s.Description AS Suffix,
			hcp.[DateCreated],
            hcp.[UserCreated],
            hcp.[DateModified],
            hcp.[UserModified]

		FROM hips.HealthProviderIndividual AS hcp
		JOIN hips.Title AS t ON t.TitleId = hcp.TitleId
		LEFT OUTER JOIN hips.Suffix AS s ON s.SuffixId = hcp.SuffixId
		WHERE [HealthProviderIndividualId] = @HealthProviderIndividualId

    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HealthProviderOrganisationGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   29/10/2013
-- Description:   Gets all records or a specific record from the HealthProviderOrganisation Table
-- =============================================
CREATE PROCEDURE [hips].[HealthProviderOrganisationGet] 
(
	@HealthProviderOrganisationId INT = NULL
)
AS
BEGIN
SELECT  hpo.HealthProviderOrganisationId,
		hpo.Name,
		hpo.HpiO,
		hpo.HiCertSerial,
		hpo.PcehrCertSerial,
		hpo.AuthorisedEmployeeName,
		hpo.AuthorisedEmployeeUserId,
		hpo.DateCreated,
		hpo.UserCreated,
		hpo.DateModified,
		hpo.UserModified,
		hpo.[HpoCertSerial],
		hpo.[HiCsp],
		hpo.[PcehrCsp],
		hpo.[HealthProviderOrganisationNetworkId]
FROM  hips.HealthProviderOrganisation hpo
WHERE hpo.HealthProviderOrganisationId = @HealthProviderOrganisationId OR @HealthProviderOrganisationId IS NULL
END
GO

DROP PROCEDURE [hips].[HealthProviderOrganisationPatientGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Description:   Gets a specific record from the HealthProviderOrganisationPatient Table
-- Create date: 18 December 2012
-- =============================================
CREATE PROCEDURE [hips].[HealthProviderOrganisationPatientGet] 
(
	  @HealthProviderOrganisationId INT = NULL
	, @PatientMasterId INT = NULL
	, @HpiO VARCHAR(16) = NULL
	, @DateModified DATETIME = NULL
)
AS
BEGIN
	SELECT    hpop.[HealthProviderOrganisationId]
			, hpop.[PatientMasterId]
			, hpop.[PcehrAdvertised]
			, hpop.[AccessCodeRequiredId]
			, hpop.[PcehrDisclosed]
			, hpop.[DateCreated]
			, hpop.[UserCreated]
			, hpop.[DateModified]
			, hpop.[UserModified]
		
FROM  hips.HealthProviderOrganisationPatient hpop
INNER JOIN hips.HealthProviderOrganisation hpo ON hpo.HealthProviderOrganisationId = hpop.HealthProviderOrganisationId
WHERE (hpop.PatientMasterID = @PatientMasterID OR @PatientMasterId IS NULL)
AND (hpop.HealthProviderOrganisationId = @HealthProviderOrganisationId OR @HealthProviderOrganisationId IS NULL)
AND (hpo.HpiO = @HpiO OR @HpiO IS NULL)
AND (hpop.DateModified >= @DateModified OR @DateModified IS NULL)
END
GO

DROP PROCEDURE [hips].[HealthProviderOrganisationPatientInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 December 2012
-- Description:   Inserts a new record into the HealthProviderOrganisationPatient Table
-- =============================================
CREATE PROCEDURE [hips].[HealthProviderOrganisationPatientInsert] 
(
	  @HealthProviderOrganisationId INT
	, @PatientMasterId INT
	, @PcehrAdvertised BIT = NULL
	, @AccessCodeRequiredId INT
	, @PcehrDisclosed BIT
	, @UserModified VARCHAR(256)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int

    SET @intErrorCode = 0

    INSERT
     INTO [hips].[HealthProviderOrganisationPatient]
         ( [HealthProviderOrganisationId]
		 , [PatientMasterId]
         , [PcehrAdvertised]
         , [AccessCodeRequiredId]
		 , [PcehrDisclosed]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         )
    VALUES
         ( @HealthProviderOrganisationId
		 , @PatientMasterId
         , @PcehrAdvertised
         , @AccessCodeRequiredId
		 , @PcehrDisclosed
         , GETDATE()
         , @UserModified
         , GETDATE()
         , @UserModified
         )

    SELECT @intErrorCode = @@ERROR 
    IF @intErrorCode = 0
    BEGIN
        SELECT [HealthProviderOrganisationId]
			 , [PatientMasterId]
             , [PcehrAdvertised]
             , [AccessCodeRequiredId]
			 , [PcehrDisclosed]
             , [DateCreated]
             , [UserCreated]
             , [DateModified]
             , [UserModified]
         FROM [hips].[HealthProviderOrganisationPatient]
         WHERE [PatientMasterId] = @PatientMasterId
		 AND   [HealthProviderOrganisationId] = @HealthProviderOrganisationId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HealthProviderOrganisationPatientUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 December 2012
-- Description:   Updates an existing record into the HealthProviderOrganisationPatient Table
-- =============================================
CREATE PROCEDURE [hips].[HealthProviderOrganisationPatientUpdate] 
(
	  @HealthProviderOrganisationId INT
	, @PatientMasterId INT
	, @PcehrAdvertised BIT = NULL
	, @AccessCodeRequiredId INT
	, @PcehrDisclosed BIT
	, @UserModified VARCHAR(256)
	, @DateModified datetime
)
AS
BEGIN
    SET NOCOUNT ON
	DECLARE @intErrorCode int

    SET @intErrorCode = 0
    IF NOT EXISTS (SELECT TOP 1 [DateModified]
            	FROM [hips].[HealthProviderOrganisationPatient]
				WHERE [HealthProviderOrganisationId] = @HealthProviderOrganisationId AND [PatientMasterId] = @PatientMasterId AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'HealthProviderOrganisationPatient', N'PatientMasterId', @PatientMasterId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
	IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[HealthProviderOrganisationPatient]
           SET [PcehrAdvertised] = @PcehrAdvertised
             , [AccessCodeRequiredId] = @AccessCodeRequiredId
             , [PcehrDisclosed] = @PcehrDisclosed
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified
         WHERE [PatientMasterId] = @PatientMasterId
		 AND [HealthProviderOrganisationId] = @HealthProviderOrganisationId

        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
		SELECT [HealthProviderOrganisationId]
					 , [PatientMasterId]
					 , [PcehrAdvertised]
					 , [AccessCodeRequiredId]
					 , [PcehrDisclosed]
					 , [DateCreated]
					 , [UserCreated]
					 , [DateModified]
					 , [UserModified]
					 FROM [hips].[HealthProviderOrganisationPatient]            
		WHERE [PatientMasterId] = @PatientMasterId
		AND [HealthProviderOrganisationId] = @HealthProviderOrganisationId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[HL7MessageLogDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 25 July 2012
-- Description:   Deletes an existing record from the Message Table
-- =============================================
CREATE PROCEDURE [hips].[HL7MessageLogDelete]
(
@HL7MessageLogId INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @intErrorCode INT,
@intRowsAffected INT
SELECT  @intErrorCode = 0,
@intRowsAffected = 0
DELETE
 [hips].[HL7MessageLog]
WHERE [HL7MessageLogId] = @HL7MessageLogId
SELECT @intErrorCode = @@ERROR,
@intRowsAffected = @@ROWCOUNT
SELECT @intRowsAffected AS RowsAffected
RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HL7MessageLogGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 25 July 2012
-- Description:   Gets a specified record from the HL7Message Table
-- Modified:
-- 16/04/2013 - Get only the most recently created record with the criteria
--                       Column IsSuccessful bit changed to QueueStatusId int
-- =============================================
CREATE PROCEDURE [hips].[HL7MessageLogGet] 
(
		 @HL7MessageLogId INT = NULL
		,@SendingApplication		 varchar(180) =NULL
		,@SendingFacility		     varchar(180) = NULL
		,@MessageControlId		     varchar(25) = NULL
		,@MessageTime	DATETIME = NULL      
) AS
BEGIN

SELECT TOP 1
		[HL7MessageLogId]
       ,[HospitalId]
       ,[HospitalPatientIdentifier]
       ,[SendingApplication]
       ,[SendingFacility]
       ,[MessageControlId]
       ,[DateTimeOfMessage]
       ,[QueueStatusId]
       ,[ShouldRetry]
       ,[RetryCount]
       ,[FailureReason]
       ,[FullMessage]
       ,[DateCreated]
       ,[UserCreated]
       ,[DateModified]
       ,[UserModified]
FROM [hips].[HL7MessageLog] 
WHERE (@HL7MessageLogId IS NULL OR HL7MessageLogId = @HL7MessageLogId)
AND (@SendingApplication IS NULL OR SendingApplication = @SendingApplication)
AND (@SendingFacility IS NULL OR SendingFacility = @SendingFacility)
AND (@MessageControlId IS NULL OR MessageControlId = @MessageControlId)
AND (@MessageTime IS NULL OR DateTimeOfMessage = @MessageTime)
ORDER BY DateCreated DESC

END

GO

DROP PROCEDURE [hips].[HL7MessageLogInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 25 July 2012
-- Description:   Inserts a new record into the HL7 Message Log Table
-- 16/04/2013 - Column IsSuccessful bit changed to QueueStatusId int
-- =============================================
CREATE PROCEDURE [hips].[HL7MessageLogInsert] 
(
		@HospitalID INT =NULL
       ,@HospitalPatientIdentifier VARCHAR(20)=NULL
       ,@SendingApplication	VARCHAR(180) =NULL
       ,@SendingFacility VARCHAR(180) = NULL
       ,@MessageControlId VARCHAR(25) = NULL
       ,@DateTimeOfMessage DATETIME = NULL
       ,@QueueStatusId INT 
       ,@ShouldRetry BIT
       ,@RetryCount INT 
       ,@FailureReason VARCHAR(256) = NULL
       ,@FullMessage VARCHAR(MAX) 
       ,@UserModified VARCHAR(256)
)								    
AS								   
BEGIN	
    SET NOCOUNT ON
    DECLARE @intErrorCode int
      ,@HL7MessageLogId INT
    SET @intErrorCode = 0						   
				   
	INSERT INTO [hips].[HL7MessageLog]
	        ( HospitalId ,
	          HospitalPatientIdentifier ,
	          SendingApplication ,
	          SendingFacility ,
	          MessageControlId ,
	          DateTimeOfMessage ,
	          QueueStatusId ,
	          ShouldRetry ,
	          RetryCount ,
	          FailureReason ,
	          FullMessage ,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	        )
VALUES
	(
		@HospitalID
       ,@HospitalPatientIdentifier
       ,@SendingApplication
       ,@SendingFacility
       ,@MessageControlId
       ,@DateTimeOfMessage
       ,@QueueStatusId
       ,@ShouldRetry
       ,@RetryCount
       ,@FailureReason
       ,@FullMessage
      , GETDATE()
      , @UserModified
      , GETDATE()
      , @UserModified
	)
SELECT @intErrorCode = @@ERROR 
           , @HL7MessageLogId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT  [HL7MessageLogID]
			   ,[HospitalID]
			   ,[HospitalPatientIdentifier]
			   ,[SendingApplication]
			   ,[SendingFacility]
			   ,[MessageControlId]
			   ,[DateTimeOfMessage]
			   ,[QueueStatusId]
			   ,[ShouldRetry]
			   ,[RetryCount]
			   ,[FailureReason]
			   ,[FullMessage]
			   ,[DateCreated]
			   ,[UserCreated]
			   ,[DateModified]
			   ,[UserModified]
           FROM [hips].[HL7MessageLog] 
           WHERE [HL7MessageLogId] = @HL7MessageLogId
    END
    RETURN(@intErrorCode)
    
END

GO

DROP PROCEDURE [hips].[HL7MessageLogUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 22/07/2012
-- Description:   Updates an existing record in the HL7 Message Log Table
-- 16/04/2013 - Column IsSuccessful bit changed to QueueStatusId int
-- ============================================
CREATE PROCEDURE [hips].[HL7MessageLogUpdate] 
(
	 @HL7MessageLogId int
	,@HospitalId                 int = NULL
	,@HospitalPatientIdentifier  varchar(20)=NULL
	,@SendingApplication		 varchar(180) =NULL
	,@SendingFacility		     varchar(180) = NULL
	,@MessageControlId		     varchar(25) = NULL
	,@DateTimeOfMessage		     datetime = NULL
	,@QueueStatusId			     int
	,@ShouldRetry			     bit
	,@RetryCount        	     int 
	,@FailureReason			     varchar(256) = NULL
	,@FullMessage			     varchar(max) 
	,@UserModified               varchar(256)                          
    ,@DateModified               datetime
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int
    SET @intErrorCode = 0
    IF NOT EXISTS (SELECT TOP 1 [DateModified]
                FROM [hips].[HL7MessageLog]
        WHERE [HL7MessageLogId] = @HL7MessageLogId
        AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'HL7MessageLog', N'HL7MessageLogId', @HL7MessageLogId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[HL7MessageLog]
           SET  [HospitalId]        = @HospitalId               
       ,[HospitalPatientIdentifier]	= @HospitalPatientIdentifier
       ,[SendingApplication]		= @SendingApplication		  
       ,[SendingFacility]			= @SendingFacility		  
       ,[MessageControlId]			= @MessageControlId		  
       ,[DateTimeOfMessage]			= @DateTimeOfMessage		  
       ,[QueueStatusId]				= @QueueStatusId			  
       ,[ShouldRetry]				= @ShouldRetry			  
       ,[RetryCount]				= @RetryCount				  
       ,[FailureReason]				= @FailureReason			  
       ,[FullMessage]				= @FullMessage			  
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified
           WHERE [HL7MessageLogId] = @HL7MessageLogId
        SELECT @intErrorCode = @@ERROR
    END
    IF @intErrorCode = 0
    BEGIN
           SELECT [HL7MessageLogId]
                , [HospitalId]
       ,[HospitalPatientIdentifier]
       ,[SendingApplication]
       ,[SendingFacility]
       ,[MessageControlId]
       ,[DateTimeOfMessage]
       ,[QueueStatusId]
       ,[ShouldRetry]
       ,[RetryCount]
       ,[FailureReason]
       ,[FullMessage]
       ,[DateCreated]
       ,[UserCreated]
       ,[DateModified]
       ,[UserModified]
                     FROM [hips].[HL7MessageLog] 
           WHERE [HL7MessageLogId] = @HL7MessageLogId
    END
    RETURN(@intErrorCode)
END

GO

DROP PROCEDURE [hips].[HospitalAddressGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   23 November 2012
-- Description:   Gets a list of addresses for a specified hospital.
-- Modified:
-- 3 April 2013 - Make HospitalId optional and return HospitalId
-- =============================================
CREATE PROCEDURE [hips].[HospitalAddressGet] 
(
	   @HospitalId INT = NULL
) AS
BEGIN
SELECT DISTINCT
		a.AddressId,
		a.AddressLine1,
		a.AddressLine2,
		a.PlaceName,
		a.AustralianStateId,
		st.Code AS AustralianStateCode,
		st.Description AS AustralianStateName,
		a.InternationalStateCode,
		a.PostCode,
		a.CountryId,
		c.Description AS CountryName,
		a.AddressTypeId,
		at.Description AS AddressTypeDescription,
		a.DateCreated,
		a.UserCreated,
		a.DateModified,
		a.UserModified,
		ha.HospitalId
FROM hips.HospitalAddress ha
INNER JOIN hips.Address a on ha.AddressId = a.AddressId
INNER JOIN hips.Country AS c ON c.CountryId = a.CountryId
INNER JOIN hips.AddressType AS at ON at.AddressTypeId = a.AddressTypeId
LEFT OUTER JOIN hips.State AS st ON st.StateID = a.AustralianStateId
WHERE (@HospitalId IS NULL OR ha.HospitalId = @HospitalId)

END
GO

DROP PROCEDURE [hips].[HospitalCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 July 2012
-- Description:   Gets specified records from the HospitalCode Table
-- =============================================
CREATE PROCEDURE [hips].[HospitalCodeGet] 
(
	 @HospitalId INT = NULL
	,@CodeSystemId INT = NULL
) AS

BEGIN

SELECT hc.[HospitalId]
      ,hc.[CodeSystemId]
      ,hc.[Code]
      ,cs.Code As CodeSystemCode
      ,hc.[DateCreated]
      ,hc.[UserCreated]
      ,hc.[DateModified]
      ,hc.[UserModified]
FROM   [HospitalCode] hc 
inner join CodeSystem cs on hc.CodeSystemId = cs.CodeSystemId

WHERE (hc.[HospitalId] = @HospitalId OR @HospitalId IS NULL)
AND   (hc.[CodeSystemId] = @CodeSystemId         OR @CodeSystemId IS NULL)

END
GO

DROP PROCEDURE [hips].[HospitalContactGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   18 July 2012
-- Description:   Gets hospital contact records from the joined Contact and HospitalContact tables
-- Modified:
-- 3 April 2013 - Make HospitalId optional and return HospitalId
-- 28 May 2013 - Return Description as ContactMethodDescription
-- =============================================
CREATE PROCEDURE [hips].[HospitalContactGet] 
(
	 @HospitalId INT = NULL
) AS

BEGIN
SELECT  c.ContactId,
		c.Detail,
		c.ContactMethodId,
		cm.TelecommunicationType,
		cm.Description AS ContactMethodDescription,
		cm.CdaType,
		c.DateCreated,
		c.UserCreated,
		c.DateModified,
		c.UserModified,
		hc.HospitalId			
FROM hips.Contact c
INNER JOIN hips.ContactMethod AS cm ON cm.ContactMethodId = c.ContactMethodId
INNER JOIN hips.HospitalContact as hc On hc.ContactId = c.ContactId
WHERE (@HospitalId IS NULL OR hc.HospitalId = @HospitalId)

END
GO

DROP PROCEDURE [hips].[HospitalGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   24 July 2012
-- Description:   Gets one or more specified records from the Hospital Table.
--                Provide a HospitalId or both a Code and a CodeSystemId.
-- Modified: 
-- 18/12/2012 added Logo and HealthProviderOrganisationId
-- 24/01/2013 added Code, CodeSystemId and DateModified to match DL
-- 05/07/2013 added UploadDocumentMinimumAge
-- =============================================
CREATE PROCEDURE [hips].[HospitalGet] 
(
	   @HospitalId INT = NULL
	   ,@Code VARCHAR(64) = NULL
	   ,@CodeSystemId INT = NULL
	   ,@DateModified DATETIME = NULL
) AS
BEGIN

IF @HospitalId IS NULL AND @Code IS NOT NULL AND @CodeSystemId IS NOT NULL
BEGIN
	SELECT	@HospitalId = HospitalId
	FROM	hips.HospitalCode
	WHERE	Code = @Code
		AND	CodeSystemId = @CodeSystemId
END

SELECT DISTINCT
		h.HospitalId,		
		h.Description,
		h.Name,
		hpo.Name AS HpioName,
		hpo.HpiO AS HpiO,
		hpo.HiCertSerial AS HiCertSerial,
		hpo.PcehrCertSerial AS PcehrCertSerial,
		hpo.VisitorHospitalId AS VisitorHospitalId,
		hpo.[HpoCertSerial],
		hpo.[HiCsp],
		hpo.[PcehrCsp],
		hpo.[HealthProviderOrganisationNetworkId],
		h.AuthorisedEmployeeName,
		h.AuthorisedEmployeeUserId,
		h.Logo,
		h.HealthProviderOrganisationId,
		h.UploadDocumentMinimumAge,
		h.DateCreated,
		h.UserCreated,
		h.DateModified,
		h.UserModified
		
FROM		hips.Hospital h
LEFT JOIN	hips.HealthProviderOrganisation hpo
ON			h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
WHERE (@HospitalId IS NULL  OR h.HospitalId = @HospitalId)
AND (@DateModified IS NULL OR h.DateModified >= @DateModified)

END
GO

DROP PROCEDURE [hips].[HospitalHealthProviderIndividualInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 23 October 2012
-- Description:   Inserts a new record into the Hospital Health Care Provider Table
-- =============================================

CREATE PROCEDURE [hips].[HospitalHealthProviderIndividualInsert] 
(
	@HospitalId INT,
	@HealthProviderIndividualId INT,
	@CisProviderIdentifier VARCHAR(30) = NULL,
	@PasProviderIdentifier VARCHAR(30),
	@UserModified VARCHAR(256)
) 
AS
BEGIN
	SET NOCOUNT ON

   DECLARE @intErrorCode INT
          
    SET @intErrorCode = 0
	INSERT INTO [hips].[HospitalHealthProviderIndividual]
	        ( HospitalId ,
	          HealthProviderIndividualId ,
	          CisProviderIdentifier ,
	          PasProviderIdentifier ,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	        )
	VALUES  ( @HospitalId ,
	          @HealthProviderIndividualId , 
	          @CisProviderIdentifier , 
	          @PasProviderIdentifier ,
	          GETDATE() , 
	          @UserModified , 
	          GETDATE() , 
	          @UserModified  
	        )
	        
	 SELECT @intErrorCode = @@ERROR 
    IF @intErrorCode = 0
    BEGIN
	SELECT
			  HospitalId ,
	          HealthProviderIndividualId ,
	          CisProviderIdentifier ,
	          PasProviderIdentifier ,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	FROM [hips].[HospitalHealthProviderIndividual]
	WHERE @HospitalId = HospitalId
	AND   @HealthProviderIndividualId = HealthProviderIndividualId

    END

    RETURN(@intErrorCode)

	

END
GO

DROP PROCEDURE [hips].[HospitalPatientDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 25 July 2012
-- Description:   Deletes an existing record from the Message Table
-- =============================================
CREATE PROCEDURE [hips].[HospitalPatientDelete]
(
@PatientId INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @intErrorCode INT,
@intRowsAffected INT
SELECT  @intErrorCode = 0,
@intRowsAffected = 0
DELETE
 [hips].[HospitalPatient]
WHERE [PatientId] = @PatientId
SELECT @intErrorCode = @@ERROR,
@intRowsAffected = @@ROWCOUNT
SELECT @intRowsAffected AS RowsAffected
RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HospitalPatientGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   15 October 2012
-- Description:   Gets a specified record from the HospitalPatient table.
-- Modified:
-- 04/06/2013 - Add ability to get only active records.
--              When the ActiveOnly parameter is true, the MRN that has an
--              episode with the most recent (most future) admission date/time
--              will be chosen from among those with the given hospital and
--              patient master ID.
-- =============================================
CREATE PROCEDURE [hips].[HospitalPatientGet] 
(
	@PatientId INT = null,
	@HospitalId INT = NULL,
	@Mrn VARCHAR(20) = NULL,
	@PatientMasterId INT = NULL,
	@ActiveOnly BIT = NULL	
)
AS
BEGIN
	IF (@ActiveOnly = '1')
	BEGIN
		-- Get only the MRN associated with the latest episode for each patient at each hospital.
		-- In 99% of cases this will return only one MRN for each patient at each hospital.
		-- The only case where it will return more than one is if there are episodes with the
		-- exact same admission date/time on multiple MRNs. In this case they are ordered with the
		-- most recently modified HospitalPatient record first.
		SELECT 
			hp.PatientId,
			hp.PatientMasterId,
			hp.HospitalId,
			hp.Mrn,
			hp.DateCreated,
			hp.UserCreated,
			hp.DateModified,
			hp.UserModified
		FROM hips.HospitalPatient hp
		INNER JOIN hips.Episode e
			ON hp.PatientId = e.PatientId
		INNER JOIN
				(	-- The latest admission date/time for the patient master at the hospital
					SELECT  PatientMasterId, HospitalId, MAX(AdmissionDate) AdmissionDate
					FROM    hips.HospitalPatient hp
					INNER JOIN hips.Episode e
						ON hp.PatientId = e.PatientId
					WHERE  (@PatientMasterId IS NULL OR PatientMasterId = @PatientMasterId)
					AND    (@HospitalId IS NULL OR HospitalId = @HospitalId)
					GROUP BY PatientMasterId, HospitalId
				) AS LatestAdmission
			ON hp.PatientMasterId = LatestAdmission.PatientMasterId
			AND	hp.HospitalId = LatestAdmission.HospitalId
			AND e.AdmissionDate = LatestAdmission.AdmissionDate
		WHERE   (@PatientId = hp.PatientId OR @PatientId is NULL)
			AND (@HospitalId = hp.HospitalId OR @HospitalId is NULL)
			AND (@Mrn = hp.Mrn OR @Mrn is NULL)
			AND (@PatientMasterId = hp.PatientMasterId OR @PatientMasterId  IS NULL)
			AND (@HospitalId = hp.HospitalId OR @HospitalId IS NULL)
		ORDER BY hp.DateModified DESC
	END
	ELSE
	BEGIN
		-- Get all the MRNs, including active and inactive MRNs, for the specified
		-- patient and hospital.
		SELECT
			PatientId,
			PatientMasterId,
			HospitalId,
			Mrn,
			DateCreated,
			UserCreated,
			DateModified,
			UserModified
		FROM hips.HospitalPatient
		WHERE   (@PatientId = PatientId OR @PatientId is NULL)
			AND (@HospitalId = HospitalId OR @HospitalId is NULL)
			AND (@Mrn = Mrn OR @Mrn is NULL)
			AND (@PatientMasterId = PatientMasterId OR @PatientMasterId  IS NULL)
		ORDER BY DateModified DESC
	END
END
GO

DROP PROCEDURE [hips].[HospitalPatientInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 October 2012
-- Description:   Inserts a new record into the Patient Table
-- =============================================
CREATE PROCEDURE [hips].[HospitalPatientInsert]
(
      @PatientMasterId INT
      ,@HospitalId INT
      ,@Mrn VARCHAR(20)
	  ,@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @intErrorCode INT,
			@PatientId  INT
    SET @intErrorCode = 0
    INSERT
     INTO [hips].[HospitalPatient]
         ( [PatientMasterId],
           [HospitalId],
           [Mrn],
           [DateCreated],
           [UserCreated],
           [DateModified],
           [UserModified]
         )
    VALUES
         (
            @PatientMasterId
           ,@HospitalId
           ,@Mrn
          , GETDATE()
          , @UserModified
          , GETDATE()
          , @UserModified
         )
    SELECT @intErrorCode = @@ERROR 
           , @PatientId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT 
           PatientId,
           PatientMasterId,
           HospitalId,
           Mrn,
           DateCreated,
           UserCreated,
           DateModified,
           UserModified
         FROM [hips].[HospitalPatient]
         WHERE [PatientId] = @PatientId
    END
    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HospitalPatientMove]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   30 May 2013
-- Description:   Moves all the MRNs for a hospital patient, from one enterprise ID to another.
--                This includes the current MRN as well as any previously merged ones.
--                If as a result there are no MRNs from any hospital on the old enterprise ID,
--                then moves the IHI to the history as it has been fully resolved.
-- Modified:
-- 19 June 2013 - Create merge conflict alert if merging patients
--                              who have two different IHI numbers.
-- =============================================
CREATE PROCEDURE [hips].[HospitalPatientMove]
(
     @PatientId INT
    ,@HospitalId INT
    ,@FromPatientMasterId INT
    ,@ToPatientMasterId INT
    ,@UserModified varchar(256)
    ,@DateModified DATETIME
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @intErrorCode INT, @FromIhi VARCHAR(16), @ToIhi VARCHAR(16), @ConflictStatus INT, @FromMrn VARCHAR(20), @ToMrn VARCHAR(20), @Message VARCHAR(512), @HospitalDesc VARCHAR(60)

    DECLARE @HealthProviderOrganisationNetworkId INT
    SET @HealthProviderOrganisationNetworkId = (
        SELECT hpo.HealthProviderOrganisationNetworkId 
        FROM hips.HealthProviderOrganisation hpo
        INNER JOIN hips.Hospital h 
                    ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
        WHERE h.HospitalId = @HospitalId
    )

    SET @ConflictStatus = 102
    SET @FromIhi = (SELECT Ihi FROM hips.PatientMasterIhi WHERE PatientMasterId = @FromPatientMasterId AND HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId)
    SET @ToIhi = (SELECT Ihi FROM hips.PatientMasterIhi WHERE PatientMasterId = @ToPatientMasterId AND HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId)
    SET @FromMrn = (SELECT TOP 1 Mrn FROM hips.HospitalPatient WHERE HospitalId = @HospitalId AND PatientMasterId = @FromPatientMasterId ORDER BY DateModified DESC)
    SET @ToMrn = (SELECT TOP 1 Mrn FROM hips.HospitalPatient WHERE HospitalId = @HospitalId AND PatientMasterId = @ToPatientMasterId ORDER BY DateModified DESC)
    SET @HospitalDesc = (SELECT [Description] FROM hips.Hospital WHERE HospitalId = @HospitalId)

    SET @intErrorCode = 0
    IF NOT EXISTS (SELECT TOP 1 [DateModified]
                FROM [hips].[HospitalPatient]
        WHERE [PatientId] = @PatientId
        AND [DateModified] = @DateModified)
    BEGIN
        DECLARE @DateModifiedText NVARCHAR(30)
        SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
        RAISERROR (50001, -1, -1, N'HospitalPatient', N'PatientMasterId', @ToPatientMasterId, N'DateModified', @DateModifiedText) 
        SET @intErrorCode = @@ERROR
    END
    IF @intErrorCode = 0
    BEGIN
        IF (@FromIhi <> @ToIhi) AND (@ToMrn IS NOT NULL)
        BEGIN
                -- We are moving to a PM that already has an MRN from this hospital.
                -- Create IHI Conflict alert against both patient masters
                UPDATE	[hips].[PatientMasterIhi]
                SET		[IhiStatusId] = @ConflictStatus
                        ,[DateModified] = GETDATE()
                        ,[UserModified] = @UserModified
                WHERE [PatientMasterId]	= @FromPatientMasterId
                OR	[PatientMasterId] = @ToPatientMasterId
                AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
                        
                SET @Message = 'IHI conflict merging MRN '
                    + @FromMrn + ' at ' + @HospitalDesc + ' with IHI ' + @FromIhi + ' into MRN '
                    + @ToMrn + ' at ' + @HospitalDesc + ' with IHI ' + @ToIhi
                
                INSERT INTO [hips].IhiLookupAlert
                         ( PatientMasterId ,
                           Message ,
                           Request ,
                           Response ,
                           DateModified ,
                           UserModified ,
                           DateCreated ,
                           UserCreated
                         )
                 VALUES  ( @FromPatientMasterId , -- PatientMasterId - int
                           @Message, -- Message - varchar(512)
                           NULL, -- Request - xml
                           NULL, -- Response - xml
                           GETDATE() , -- DateModified - datetime
                           @UserModified , -- UserModified - varchar(256)
                           GETDATE() , -- DateCreated - datetime
                           @UserModified  -- UserCreated - varchar(256)
                         )
                INSERT INTO [hips].IhiLookupAlert
                         ( PatientMasterId ,
                           Message ,
                           Request ,
                           Response ,
                           DateModified ,
                           UserModified ,
                           DateCreated ,
                           UserCreated
                         )
                 VALUES  ( @ToPatientMasterId , -- PatientMasterId - int
                           @Message, -- Message - varchar(512)
                           NULL, -- Request - xml
                           NULL, -- Response - xml
                           GETDATE() , -- DateModified - datetime
                           @UserModified , -- UserModified - varchar(256)
                           GETDATE() , -- DateCreated - datetime
                           @UserModified  -- UserCreated - varchar(256)
                         )
        END

        UPDATE	hips.HospitalPatient
        SET		PatientMasterId = @ToPatientMasterId,
                DateModified = @DateModified,
                UserModified = @UserModified
        WHERE	PatientMasterId = @FromPatientMasterId
        AND		HospitalId = @HospitalId
   
        SELECT @intErrorCode = @@ERROR

        IF @intErrorCode = 0
        BEGIN
            IF NOT EXISTS (  -- No MRNs left on the source PM
                SELECT	PatientId
                FROM	hips.HospitalPatient
                WHERE	PatientMasterId = @FromPatientMasterId)
            AND NOT EXISTS ( -- and the source PM's IHI is not in conflict
                SELECT	IhiStatusId
                FROM	hips.PatientMasterIhi
                WHERE	PatientMasterId = @FromPatientMasterId
                AND		IhiStatusId = @ConflictStatus
                AND		HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId)
            BEGIN
                INSERT	hips.PatientMasterIhiHistory
                    ([PatientMasterId]
                       ,[HealthProviderOrganisationNetworkId]
                       ,[Ihi]
                       ,[IhiRecordStatusId]
                       ,[IhiStatusId]
                       ,[DateCreated]
                       ,[UserCreated]
                       ,[DateModified]
                       ,[UserModified])
                SELECT	 PatientMasterId
                        ,HealthProviderOrganisationNetworkId
                        ,Ihi
                        ,IhiRecordStatusId
                        ,IhiStatusId
                        ,GETDATE()
                        ,@UserModified
                        ,GETDATE()
                        ,@UserModified
                FROM	hips.PatientMasterIhi
                WHERE	PatientMasterId = @FromPatientMasterId
                AND		HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId

                SELECT @intErrorCode = @@ERROR

                IF @intErrorCode = 0
                BEGIN
                    DELETE	hips.PatientMasterIhi
                    WHERE	PatientMasterId = @FromPatientMasterId
                    AND		HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId

                    SELECT @intErrorCode = @@ERROR
                END
            END
        END
    END

    IF @intErrorCode = 0
    BEGIN
        SELECT 
           PatientId,
           PatientMasterId,
           HospitalId,
           Mrn,
           DateCreated,
           UserCreated,
           DateModified,
           UserModified
         FROM [hips].[HospitalPatient]
         WHERE [PatientId] = @PatientId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HospitalPatientUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 15 October 2012
-- Description:   Inserts a new record into the Patient Table
-- =============================================
CREATE PROCEDURE [hips].[HospitalPatientUpdate]
(
		@PatientId INT,
      @PatientMasterId INT
      ,@HospitalId INT
      ,@Mrn VARCHAR(20)
	  ,@UserModified varchar(256)
	  ,@DateModified DATETIME
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @intErrorCode INT
		
    SET @intErrorCode = 0
    IF NOT EXISTS (SELECT TOP 1 [DateModified]
                FROM [hips].[HospitalPatient]
        WHERE [PatientId] = @PatientId
        AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'HospitalPatient', N'PatientId', @PatientId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
    IF @intErrorCode = 0
    BEGIN
    UPDATE hips.HospitalPatient
		SET PatientMasterId = @PatientMasterId,
			HospitalId = @HospitalId,
			Mrn = @Mrn,
			DateModified = @DateModified,
			UserModified = @UserModified
		WHERE
			PatientId = @PatientId
			
   
    SELECT @intErrorCode = @@ERROR 
    END
    IF @intErrorCode = 0
    BEGIN
        SELECT 
           PatientId,
           PatientMasterId,
           HospitalId,
           Mrn,
           DateCreated,
           UserCreated,
           DateModified,
           UserModified
         FROM [hips].[HospitalPatient]
         WHERE [PatientId] = @PatientId
    END
    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[HospitalProviderGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   16 October 2012
-- Description:   Gets one or more Hospital providers from the HospitalHealthProviderIndividual table
-- =============================================
CREATE PROCEDURE [hips].[HospitalProviderGet] 
(
	   @HospitalId INT = NULL,
	   @HospitalHealthProviderIndividualId INT = NULL
) AS
BEGIN
SELECT 
		h.HospitalId,		
		h.HealthProviderIndividualId,
		h.CisProviderIdentifier,
		h.PasProviderIdentifier,
		h.DateCreated,
		h.UserCreated,
		h.DateModified,
		h.UserModified
		
FROM       hips.HospitalHealthProviderIndividual h
WHERE (@HospitalId IS NULL  OR h.HospitalId = @HospitalId)
AND (@HospitalHealthProviderIndividualId IS NULL OR h.HealthProviderIndividualId = @HospitalHealthProviderIndividualId)

END
GO

DROP PROCEDURE [hips].[HpiiLookupAuditInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 15/11/2013
-- Description:   Inserts a new record into the HpiiLookupAlert Table
-- =============================================
CREATE PROCEDURE [hips].[HpiiLookupAuditInsert] 
(
	@HpiiNumber  VARCHAR(50) = NULL,
	@HpiiStatusId INT = NULL,
	@RegistrationId VARCHAR(20) = NULL,
	@FamilyName VARCHAR(40),
	@GivenNames VARCHAR(500) = NULL,
	@DateOfBirth DATETIME = NULL,
	@Sex VARCHAR(1) = NULL,
	@AustralianState VARCHAR(5) = NULL,
	@Postcode VARCHAR(4) = NULL,
	@CountryCode VARCHAR(4) = NULL,
	@BatchItemId INT = NULL,
	@BatchIdentifier VARCHAR(36) = NULL,
	@Message VARCHAR(200) = NULL,
	@OperatorHpiI VARCHAR(16) = NULL,
	@HpiO VARCHAR(16),
	@Operator VARCHAR(100),
	@IdentifierType VARCHAR(40) = NULL,
	@IdentifierValue VARCHAR(128) = NULL,
	@HiWebServiceName VARCHAR(40),
	@HiWebServiceVersion VARCHAR(10),
	@ServiceMessageRequestID VARCHAR(50) = NULL,
	@ServiceMessageResponseID VARCHAR(50) = NULL,
	@Request xml = NULL,
	@Response xml = NULL,
	@UserModified VARCHAR(256)
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE  @intErrorCode INT,
			 @HpiiLookupAuditId INT,
			 @SexId INT,
			 @AustralianStateId INT

	SELECT @SexId = SexId 
	FROM hips.Sex
	WHERE Code = @Sex

	SELECT @AustralianStateId = StateId 
	FROM hips.State
	WHERE Code = @AustralianState

	SET @intErrorCode = 0
	
	INSERT INTO [hips].[HpiiLookupAudit]
		   ([HpiiNumber]
		   ,[HpiiStatusId]
		   ,[RegistrationId]
		   ,[FamilyName]
		   ,[GivenNames]
		   ,[DateOfBirth]
		   ,[SexId]
		   ,[AustralianStateId]
		   ,[Postcode]
		   ,[CountryCode]
		   ,[BatchItemId]
		   ,[BatchIdentifier]
		   ,[Message]
		   ,[OperatorHpiI]
		   ,[HpiO]
		   ,[Operator]
		   ,[IdentifierType]
		   ,[IdentifierValue]
		   ,[HiWebServiceName]
		   ,[HiWebServiceVersion]
		   ,[ServiceMessageRequestID]
		   ,[ServiceMessageResponseID]
		   ,[Request]
		   ,[Response]
		   ,[DateCreated]
		   ,[UserCreated]
		   ,[DateModified]
		   ,[UserModified])
	VALUES
		   (@HpiiNumber
		   ,@HpiiStatusId
		   ,@RegistrationId
		   ,@FamilyName
		   ,@GivenNames
		   ,@DateOfBirth
		   ,@SexId
		   ,@AustralianStateId
		   ,@Postcode
		   ,@CountryCode
		   ,@BatchItemId
		   ,@BatchIdentifier
		   ,@Message
		   ,@OperatorHpiI
		   ,@HpiO
		   ,@Operator
		   ,@IdentifierType
		   ,@IdentifierValue
		   ,@HiWebServiceName
		   ,@HiWebServiceVersion
		   ,@ServiceMessageRequestID
		   ,@ServiceMessageResponseID
		   ,@Request
		   ,@Response
		   ,GETDATE()
		   ,@UserModified
		   ,GETDATE()
		   ,@UserModified
		   )

	SELECT  @intErrorCode = @@ERROR, 
			@HpiiLookupAuditId = SCOPE_IDENTITY()
	
	IF @intErrorCode = 0
	BEGIN
		SELECT 
			[HpiiLookupAuditId]
		   ,[HpiiNumber]
		   ,[HpiiStatusId]
		   ,[RegistrationId]
		   ,[FamilyName]
		   ,[GivenNames]
		   ,[DateOfBirth]
		   ,[SexId]
		   ,[AustralianStateId]
		   ,[Postcode]
		   ,[CountryCode]
		   ,[BatchItemId]
		   ,[BatchIdentifier]
		   ,[Message]
		   ,[OperatorHpiI]
		   ,[HpiO]
		   ,[Operator]
		   ,[IdentifierType]
		   ,[IdentifierValue]
		   ,[HiWebServiceName]
		   ,[HiWebServiceVersion]
		   ,[ServiceMessageRequestID]
		   ,[ServiceMessageResponseID]
		   ,[Request]
		   ,[Response]
		   ,[DateCreated]
		   ,[UserCreated]
		   ,[DateModified]
		   ,[UserModified]
		 FROM [hips].[HpiiLookupAudit]
		 WHERE [HpiiLookupAuditId] = @HpiiLookupAuditId
	END

	RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[IhiDuplicatePatientCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   23 October 2012
-- Description:   Returns the number of possible 
--				  Duplicated patients in the system.
--
-- This is used to implement HI CR 5875:
--     If an IHI with a supported record status is returned from the HI Service
--     for a patient, the software shall have the capacity to assign that IHI
--     to the patient's record and raise an alert if the search criteria used
--     matches another patient?s demographic data from the same registration
--     source. If an alert is raised, the system shall either discard the IHI
--     or store it against the target patient record and flag the records as
--     potentially conflicting.
--
-- HIPS can treat each hospital as a separate registration source, in which
-- case this stored procedure will only count duplicates that are registered
-- in the same hospital as an MRN on the patient master whose IHI is being
-- searched for.
-- 
-- Modified:
-- 02/04/2013 - If this IHI was provided by another system
--                       it will have no MedicareNumber or DvaNumber
--                       however this should not be considered a
--                       potential duplicate patient record.
-- 27/05/2013 - Add ability to filter by hospital.
-- 26/06/2013 - Change the filter by hospital to a filter that counts
--                       those PMs which have MRNs from the same hospital as
--                       any MRN on the PM which is being searched.
-- =============================================
CREATE PROCEDURE [hips].[IhiDuplicatePatientCount] 
(
	@PatientMasterId INT,
	@LastName VARCHAR(80),
	@FirstNames VARCHAR(80),
	@MedicareNumber VARCHAR(12) = NULL,
	@MedicareIrn VARCHAR(3) = NULL,
	@DvaNumber VARCHAR(18) = NULL,
	@DateOfBirth DATETIME
)
AS
BEGIN
	IF @MedicareNumber IS NULL AND @DvaNumber IS NULL
	BEGIN
		SELECT 0 AS DuplicatePatients
	END
	ELSE
	BEGIN
		SELECT COUNT(DISTINCT dupPM.PatientMasterId) AS DuplicatePatients 
		FROM hips.PatientMaster dupPM WITH (NOLOCK)
		INNER JOIN hips.PatientMasterName pmn  WITH (NOLOCK) ON dupPM.PatientMasterId = pmn.PatientMasterId
		AND pmn.NameTypeId = 2  -- Current Name in PAS
		INNER JOIN hips.HospitalPatient dupHP on dupPM.PatientMasterId = dupHP.PatientMasterId
		INNER JOIN hips.HospitalPatient thisHP on thisHP.HospitalId = dupHP.HospitalId -- Has an HP from same hospital as this PM
		
		WHERE @LastName = pmn.FamilyName
		AND pmn.GivenNames LIKE '%' + RTRIM(@FirstNames) + '%'
		AND (@MedicareNumber = dupPM.MedicareNumber OR @MedicareNumber IS NULL)
		AND (@MedicareIrn = dupPM.MedicareIrn OR @MedicareIrn IS NULL)
		AND (@DvaNumber = dupPM.DvaNumber OR @DvaNumber IS NULL)
		AND @DateOfBirth = dupPM.DateOfBirth
		AND thisHP.PatientMasterId = @PatientMasterId  -- This PM is the one whose IHI is being searched for.
		AND dupPM.PatientMasterId <> @PatientMasterId  -- Don't count a duplicate with itself.
	END
END
GO

DROP PROCEDURE [hips].[IhiLookupAlertInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 11 Oct 2012
-- Description:   Inserts a new record into the IhiLookupAlert Table
-- =============================================
CREATE PROCEDURE [hips].[IhiLookupAlertInsert] 
(
	@PatientMasterId INT,
	@Message VARCHAR(512),
	@Request XML = NULL,
	@Response XML = NULL,
	@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON
	
    DECLARE @intErrorCode INT
		,@IhiLookupAlertId INT

    SET @intErrorCode = 0

    INSERT
     INTO [hips].IhiLookupAlert
             ( PatientMasterId ,
               Message ,
               Request ,
               Response ,
               DateModified ,
               UserModified ,
               DateCreated ,
               UserCreated
             )
     VALUES  ( @PatientMasterId , -- PatientMasterId - int
               @Message , -- Message - varchar(512)
               @Request , -- Request - xml
               @Response , -- Response - xml
               GETDATE() , -- DateModified - datetime
               @UserModified , -- UserModified - varchar(256)
               GETDATE() , -- DateCreated - datetime
               @UserModified  -- UserCreated - varchar(256)
             )

    SELECT @intErrorCode = @@ERROR 
		, @IhiLookupAlertId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [IhiLookupAlertId]
		 , [PatientMasterId]
		 , [Message]
		 , [Request]
         , [Response]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         FROM [hips].[IhiLookupAlert]
         WHERE [IhiLookupAlertId] = @IhiLookupAlertId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[IhiLookupAuditInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 11 Oct 2012
-- Description:   Inserts a new record into the IhiLookupAlert Table
-- =============================================
CREATE PROCEDURE [hips].[IhiLookupAuditInsert] 
(
	@PatientMasterId INT,
	@IhiNumber VARCHAR(50) = NULL,
	@IhiStatus INT,
	@IhiRecordStatus INT,
	@FamilyName VARCHAR(80),
	@GivenName VARCHAR(80),
	@SexId INT,
	@DateOfBirth DATETIME,
	@MedicareNumber VARCHAR(12)=NULL,
	@DvaNumber VARCHAR(18)=NULL,
	@Message VARCHAR(200)=NULL,
	@HpiI VARCHAR(16)=NULL,
	@HpiO VARCHAR(16),
	@Operator VARCHAR(100),
	@Request XML=NULL,
	@Response XML=NULL,
	@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON
	
    DECLARE @intErrorCode INT
		,@IhiLookupAuditId INT
		 , @LastSequenceNumber INT = 0

    SET @intErrorCode = 0
    
    SELECT TOP 1 @LastSequenceNumber = SequenceNumber
	FROM [hips].[IhiLookupAudit]
	WHERE PatientMasterID = @PatientMasterID
	ORDER BY SequenceNumber DESC


    INSERT
     INTO [hips].IhiLookupAudit
             ( PatientMasterId ,
               SequenceNumber ,
               IhiNumber ,
               IhiStatus ,
               IhiRecordStatus ,
               FamilyName ,
               GivenName ,
               SexId ,
               DateOfBirth ,
               MedicareNumber ,
               DvaNumber ,
               Message ,
               HpiI ,
               HpiO ,
               Operator ,
               Request ,
               Response ,
               DateCreated ,
               UserCreated ,
               DateModified ,
               UserModified
             )
     VALUES  ( @PatientMasterId , -- PatientMasterId - int
               @LastSequenceNumber + 1 , -- SequenceNumber - int
               @IhiNumber , -- IhiNumber - varchar(50)
               @IhiStatus , -- IhiStatus - int
               @IhiRecordStatus , -- IhiRecordStatus - int
               @FamilyName , -- FamilyName - varchar(80)
               @GivenName , -- GivenName - varchar(80)
               @SexId , -- SexId - int
               @DateOfBirth , -- DateOfBirth - datetime
               @MedicareNumber , -- MedicareNumber - varchar(12)
               @DvaNumber , -- DvaNumber - varchar(18)
               @Message , -- Message - varchar(200)
               @HpiI , -- HpiI - varchar(16)
               @HpiO , -- HpiO - varchar(16)
               @Operator , -- Operator - varchar(100)
               @Request , -- Request - xml
               @Response , -- Response - xml
               GETDATE() , -- DateCreated - datetime
               @UserModified , -- UserCreated - varchar(256)
               GETDATE() , -- DateModified - datetime
               @UserModified  -- UserModified - varchar(256)
             )
    SELECT @intErrorCode = @@ERROR 
		, @IhiLookupAuditId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [IhiLookupAuditId],
		 PatientMasterId ,
               SequenceNumber ,
               IhiNumber ,
               IhiStatus ,
               IhiRecordStatus ,
               FamilyName ,
               GivenName ,
               SexId ,
               DateOfBirth ,
               MedicareNumber ,
               DvaNumber ,
               Message ,
               HpiI ,
               HpiO ,
               Operator ,
               Request ,
               Response ,
               DateCreated ,
               UserCreated ,
               DateModified ,
               UserModified
         FROM [hips].[IhiLookupAudit]
         WHERE [IhiLookupAuditId] = @IhiLookupAuditId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[IhiUsageCount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   31 July 2012
-- Description:   Returns the number of times an IHI has been used
--                by different patients.
--
-- This is used to implement HI CR 5839:
--     The software shall raise an exception alert whenever an IHI is assigned
--     to a patient record and the same IHI has already been assigned to one or
--     more other records of patients in the local system.
--
-- HIPS treats each hospital as a separate registration source, in which
-- case this stored procedure will only count duplicate IHI on a patient
-- master that has an MRN in the same hospital as an MRN of the patient
-- master whose IHI is being searched for.
-- 
-- Modified:
-- 27/05/2013 - Add ability to filter by hospital.
-- 26/06/2013 - Change the filter by hospital to a filter that counts
--                            those PMs which have MRNs from the same hospital as
--                            any MRN on the PM which is being searched.
-- =============================================
CREATE PROCEDURE [hips].[IhiUsageCount] 
(
    @PatientMasterId INT,
    @Ihi VARCHAR(50),
    @HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
    DECLARE @result INT
    SET @result = 0
    
    SELECT COUNT(DISTINCT pmi.PatientMasterId) AS IhiTimesUsed
      FROM  hips.PatientMasterIhi pmi
      INNER JOIN hips.HospitalPatient thisHP 
            ON thisHP.PatientMasterId = @PatientMasterId
      INNER JOIN hips.HospitalPatient dupHP 
            ON dupHP.HospitalId = thisHP.HospitalId
            AND dupHP.PatientMasterId = pmi.PatientMasterId
            AND dupHP.PatientMasterId <> @PatientMasterId
      INNER JOIN hips.PatientMasterIhi thisPMI
            ON thisPMI.PatientMasterId = thisHP.PatientMasterId
      WHERE pmi.Ihi = @ihi
      AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId


END
GO

DROP PROCEDURE [hips].[LevelTypeCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the LevelType Code Table
-- =============================================
CREATE PROCEDURE [hips].[LevelTypeCodeGet] 
(
	@CodeSystemCode VARCHAR(20),
	@LevelTypeId INT = NULL
)
AS
BEGIN

	SELECT  lt.LevelTypeId, lt.Code, lt.Description
	FROM    hips.LevelTypeCode ltc
			JOIN hips.LevelType lt ON ltc.LevelTypeId = lt.LevelTypeId
			JOIN hips.CodeSystem cs ON ltc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND (lt.LevelTypeId = @LevelTypeId OR @LevelTypeId IS NULL)
	ORDER BY lt.Description

END
GO

DROP PROCEDURE [hips].[MedicareExclusionGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   22 May 2013
-- Description:   Gets all records or a specific record from the MedicareExclusion Table
-- =============================================
CREATE PROCEDURE [hips].[MedicareExclusionGet] 
(
	@MedicareExclusionId INT = NULL
)
AS
BEGIN
SELECT  s.MedicareExclusionId,
		s.MedicareNumber,
		s.Description,
		s.DateCreated,
		s.UserCreated,
		s.DateModified,
		s.UserModified
		
FROM  hips.MedicareExclusion s
WHERE s.MedicareExclusionId = @MedicareExclusionId OR @MedicareExclusionId IS NULL
END
GO

/****** Object:  StoredProcedure [hips].[MonitoringFailedUploadDocument]    Script Date: 07/11/2013 14:20:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[hips].[MonitoringFailedUploadDocument]') AND type in (N'P', N'PC'))
DROP PROCEDURE [hips].[MonitoringFailedUploadDocument]
GO

/****** Object:  StoredProcedure [hips].[MonitoringFailedUploadDocument]    Script Date: 07/11/2013 14:20:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:   Get counts of items for the monitoring tool
--					
-- =============================================

CREATE PROCEDURE [hips].[MonitoringFailedUploadDocument] 
(
	@PcehrMessageQueueId INTEGER 
)
AS
BEGIN

	SELECT 
		Request.value('declare namespace s="http://www.w3.org/2003/05/soap-envelope"; 
		declare namespace x="urn:ihe:iti:xds-b:2007"; 
		(/s:Envelope/s:Body/x:ProvideAndRegisterDocumentSetRequest/x:Document)[1]', 'VARCHAR(MAX)')
		FROM [hips].[PcehrMessageQueue] WITH (NOLOCK)
		WHERE PcehrMessageQueueId = @PcehrMessageQueueId

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[hips].[MonitoringIhiConnectionFailure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [hips].[MonitoringIhiConnectionFailure]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
 
-- Create date:   24 May 2013
-- Description:   Gets IHI % of connection failure in the timeframe specified
-- =============================================
Create PROCEDURE [hips].[MonitoringIhiConnectionFailure] 
(
	@Minutes Integer 
)
AS
BEGIN
	
	DECLARE @ihiRequests INT, @ihiConnectionFailures INT

	SELECT @ihiRequests = COUNT(*) 		
		FROM  hips.IhiLookupAudit s WITH (NOLOCK)
		WHERE 
		DateCreated >= DATEADD(minute, @Minutes * -1, SYSDATETIME())

	SELECT @ihiConnectionFailures = COUNT(*)
		FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 3
		AND
		DateCreated >= DATEADD(minute, @Minutes * -1, SYSDATETIME())

	IF (@ihiRequests = 0)
	BEGIN
		SELECT 0.0
	END
	ELSE
	BEGIN
		SELECT  CAST(ROUND(((@ihiConnectionFailures)*1.0/@ihiRequests*1.0)*100,1,1) AS DECIMAL(6,1)) AS PercentageSuccess
	END

END
GO

/****** Object:  StoredProcedure [hips].[MonitoringItemCounts]    Script Date: 07/11/2013 14:20:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[hips].[MonitoringItemCounts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [hips].[MonitoringItemCounts]
GO

/****** Object:  StoredProcedure [hips].[MonitoringItemCounts]    Script Date: 07/11/2013 14:20:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:   Get counts of items for the monitoring tool
-- =============================================

CREATE PROCEDURE [hips].[MonitoringItemCounts] 
(
	@PcehrFailedDays INTEGER = 1,
	@MonitorHours INTEGER = 8,
	@PasRefreshMinutes INTEGER = 10,
	@IhiRefreshMinutes INTEGER = 60
)
AS
BEGIN

DECLARE @CDAUploaded INTEGER
DECLARE @PCEHRAdvertised INTEGER
DECLARE @MonitoringPcehrOldestPendingMinutes INTEGER
DECLARE @MonitoringRecentPcehrFailedCount INTEGER
DECLARE @MonitoringRecentPcehrPendingCount INTEGER
DECLARE @MonitoringPasErrorCount INTEGER
DECLARE @MonitoringIhiAverageTime DECIMAL(10,3)
DECLARE @MonitoringPcehrAverageTime DECIMAL(10,3)
DECLARE @MonitoringPasMessageCount INTEGER
DECLARE @MonitoringPatientCount INTEGER
DECLARE @MonitoringTotalMessages INTEGER
DECLARE @MonitoringUniqueIhiCount INTEGER
DECLARE @MonitoringIhiAlertCount INTEGER 

SET @CDAUploaded = 0
SET @PCEHRAdvertised = 0
SET @MonitoringPcehrOldestPendingMinutes = 0
SET @MonitoringRecentPcehrFailedCount = 0
SET @MonitoringRecentPcehrPendingCount = 0
SET @MonitoringPasErrorCount = 0
SET @MonitoringIhiAverageTime = 0.0000
SET @MonitoringPcehrAverageTime = 0.0000
SET @MonitoringPasMessageCount = 0
SET @MonitoringPatientCount = 0
SET @MonitoringTotalMessages = 0
SET @MonitoringUniqueIhiCount = 0
SET @MonitoringIhiAlertCount = 0


SELECT @CDAUploaded = COUNT(*) 
	FROM hips.ClinicalDocument  WITH (NOLOCK) 
	WHERE ClinicalDocumentStatusId = 1
 
SELECT @PCEHRAdvertised = COUNT(*) 
	FROM hips.HealthProviderOrganisationPatient  WITH (NOLOCK)
	WHERE PcehrAdvertised = 1 

SELECT @MonitoringPcehrOldestPendingMinutes = COALESCE(MAX(DATEDIFF(minute, DateCreated, GETDATE())), 0)
	FROM [hips].[PcehrMessageQueue]  WITH (NOLOCK)
	WHERE QueueStatusId = 1

SELECT @MonitoringRecentPcehrFailedCount = COUNT(*) 
	FROM hips.PcehrMessageQueue pmq WITH (NOLOCK)
	JOIN hips.Episode e WITH (NOLOCK) ON e.EpisodeId = pmq.EpisodeId
	LEFT JOIN hips.ClinicalDocument cd WITH (NOLOCK) ON cd.EpisodeId = e.EpisodeId
	LEFT JOIN hips.ClinicalDocumentVersion cdv WITH (NOLOCK) ON cd.ClinicalDocumentId = cdv.ClinicalDocumentId
	WHERE QueueStatusId = 3
	AND pmq.DateCreated >= DATEADD(day, -7, SYSDATETIME()) 
	AND (cdv.UploadedDate IS NULL OR cdv.UploadedDate < pmq.DateCreated)

SELECT  @MonitoringRecentPcehrPendingCount = COUNT(*)  
	FROM  hips.PcehrMessageQueue WITH (NOLOCK)
	WHERE QueueStatusId = 1

SELECT @MonitoringPasErrorCount = COUNT(*) 
	FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 1
		AND DateCreated >= DATEADD(hour, @MonitorHours * -1, SYSDATETIME())


SELECT @MonitoringIhiAverageTime =  
	COALESCE(
	CAST((
		AVG(
		CAST(
			(DATEDIFF(millisecond, Request.value('declare namespace s="http://www.w3.org/2003/05/soap-envelope"; 
												  declare namespace h="http://ns.electronichealth.net.au/hi/xsd/common/CommonCoreElements/3.0"; 
												  (/s:Envelope/s:Header/h:timestamp/h:created)[1]', 'datetime'),
								   DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), DateCreated)))AS DECIMAL)/CAST(1000 AS DECIMAL))) AS DECIMAL(10,3)),0)
	FROM [hips].[IhiLookupAudit] WITH (NOLOCK)
	WHERE 
		DateCreated >= DATEADD(hour, @MonitorHours * -1, SYSDATETIME()) 

SELECT @MonitoringPcehrAverageTime= 
	COALESCE(
	CAST((
		AVG(
		CAST(
			(DATEDIFF(millisecond, Request.value('declare namespace s="http://www.w3.org/2003/05/soap-envelope"; 
												  declare namespace h="http://ns.electronichealth.net.au/pcehr/xsd/common/CommonCoreElements/1.0"; 
												  (/s:Envelope/s:Header/h:timestamp/h:created)[1]', 'datetime'),
								   DATEADD(MINUTE, DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()), DateCreated)))AS DECIMAL)/CAST(1000 AS DECIMAL))) AS DECIMAL(10,3)),0)
	FROM [hips].[PcehrAudit] WITH (NOLOCK)
	WHERE
		DateCreated >= DATEADD(hour, @MonitorHours * -1, SYSDATETIME()) 


SELECT @MonitoringPasMessageCount = COUNT(*)  
	FROM  hips.HL7MessageLog WITH (NOLOCK)
	WHERE
		DateCreated >= DATEADD(minute, @PasRefreshMinutes * -1, SYSDATETIME())


SELECT @MonitoringPatientCount = COUNT(*)
	FROM hips.PatientMaster WITH (NOLOCK)


SELECT @MonitoringTotalMessages = COUNT(*)
	FROM hips.HL7MessageLog WITH (NOLOCK)
	WHERE (FailureReason IS NULL)


SELECT @MonitoringUniqueIhiCount = COUNT(DISTINCT Ihi)
	FROM hips.PatientMasterIhi WITH (NOLOCK)


SELECT @MonitoringIhiAlertCount = COUNT(*)
	FROM hips.[MonitoringSyslog]
	WHERE MonitorTabId = 3
	AND 
	DateCreated >= DATEADD(minute, @IhiRefreshMinutes * -1, SYSDATETIME())




SELECT	@CDAUploaded AS CDAUploaded, 
		@PCEHRAdvertised AS PCEHRAdvertised, 
		@MonitoringPcehrOldestPendingMinutes AS MonitoringPcehrOldestPendingMinutes,
		@MonitoringRecentPcehrFailedCount AS MonitoringRecentPcehrFailedCount,
		@MonitoringRecentPcehrPendingCount AS MonitoringRecentPcehrPendingCount,
		@MonitoringPasErrorCount AS MonitoringPasErrorCount,
		@MonitoringIhiAverageTime AS MonitoringIhiAverageTime,
		@MonitoringPcehrAverageTime AS MonitoringPcehrAverageTime,
		@MonitoringPasMessageCount AS MonitoringPasMessageCount,
		@MonitoringPatientCount AS MonitoringPatientCount,
		@MonitoringTotalMessages AS MonitoringTotalMessages,
		@MonitoringUniqueIhiCount AS MonitoringUniqueIhiCount,
		@MonitoringIhiAlertCount AS MonitoringIhiAlertCount

END

GO


/****** Object:  StoredProcedure [hips].[MonitoringPcehrConnectionFailure]    Script Date: 07/11/2013 12:24:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[hips].[MonitoringPcehrConnectionFailure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [hips].[MonitoringPcehrConnectionFailure]
GO

/****** Object:  StoredProcedure [hips].[MonitoringPcehrConnectionFailure]    Script Date: 07/11/2013 12:24:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Create date:   2013
-- Description:   Gets Pcehr % of connection failure in the timeframe specified
-- =============================================
Create PROCEDURE [hips].[MonitoringPcehrConnectionFailure] 
(
	@Minutes Integer 
)
AS
BEGIN
	
	DECLARE @pcehrRequests INT, @pcehrConnectionFailures INT

	SELECT @pcehrRequests = COUNT(*) 		
		FROM  hips.PcehrAudit s WITH (NOLOCK)
		where 
			DateCreated >= DATEADD(minute, @Minutes * -1, SYSDATETIME())

	SELECT @pcehrConnectionFailures = COUNT(*)	
		FROM hips.[MonitoringSyslog]
		WHERE MonitorTabId = 5
		AND 
			DateCreated >= DATEADD(minute, @Minutes * -1, SYSDATETIME())


	IF (@pcehrRequests = 0)
	BEGIN
		SELECT 0.0
	END
	ELSE
	BEGIN
		SELECT  CAST(ROUND(((@pcehrConnectionFailures)*1.0/@pcehrRequests*1.0)*100,1,1) AS DECIMAL(6,1)) AS PercentageSuccess
	END

END

GO

DROP PROCEDURE [hips].[NameTypeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   16 October 2012
-- Description:   Gets one or more NameTypes
-- =============================================
Create PROCEDURE [hips].[NameTypeGet] 
(
	   @NameTypeId INT = NULL
) AS
BEGIN
SELECT 
		h.NameTypeId,		
		h.Code,
		h.DESCRIPTION,
		h.DateCreated,
		h.UserCreated,
		h.DateModified,
		h.UserModified
		
FROM       hips.NameType h
WHERE (@NameTypeId IS NULL  OR h.NameTypeId = @NameTypeId)

END
GO

DROP PROCEDURE [hips].[PatientDisclosureDetailsGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   17 April 2014
-- Description:   Gets a single patient and their disclosae details 
-- =============================================
CREATE PROCEDURE [hips].[PatientDisclosureDetailsGet] 
(
      @HospitalCodeSystem VARCHAR(20),
      @PatientId INT
 )
AS 
    BEGIN

        SELECT  hp.Mrn ,
				h.HospitalId,
				h.Name as HospitalName,
				hc.Code as HospitalCode,
				(SELECT TOP 1 da.AuditInformation 
						FROM hips.DisclosureAudit da 
						WHERE hp.PatientMasterId = da.PatientMasterId 
						AND h.HealthProviderOrganisationId = da.HealthProviderOrganisationId
						ORDER BY DisclosureAuditId DESC) AS DisclosureAuditInformation
        FROM    hips.HospitalPatient hp
				INNER JOIN hips.Hospital h ON hp.HospitalId = h.HospitalId
				INNER JOIN hips.HospitalCode hc	ON hp.HospitalId = hc.HospitalId	
					AND hc.CodeSystemId = (SELECT CodeSystemId FROM hips.CodeSystem WHERE Code = @HospitalCodeSystem)
				
        WHERE   hp.PatientId = @PatientId

END
GO

DROP PROCEDURE [hips].[PatientInHospitalEpisodesGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   09 Apr 2014
-- Description:   Gets a list of episodes for a patient in hospital match other given criteria.
-- =============================================
CREATE PROCEDURE [hips].[PatientInHospitalEpisodesGet]
    (
      @PatientId INT,
      @DaysDischarged INT
    )
AS 
    BEGIN

        DECLARE @DischargeDateCutoff DATETIME
        SET @DischargeDateCutoff = DATEADD(d, -@DaysDischarged, GETDATE())


        SELECT  e.EpisodeId ,
				hp.Mrn ,
                e.AdmissionDate ,
                e.DischargeDate ,				
                rp.FamilyName AS ResponsibleProviderFamilyName ,
                rp.GivenNames AS ResponsibleProviderGivenNames ,
                rpt.Description AS ResponsibleProviderTitle ,
                rps.Description AS ResponsibleProviderSuffix ,
                e.AdmissionReason , 
                e.SourceSystemEpisodeId ,
                e.ConsentWithdrawn 
        FROM    hips.HospitalPatient hp
                INNER JOIN hips.Episode e ON hp.PatientId = e.PatientId
                INNER JOIN hips.HealthProviderIndividual rp ON e.ResponsibleProviderId = rp.HealthProviderIndividualId				
                INNER JOIN hips.Title rpt ON rp.TitleId = rpt.TitleId
                INNER JOIN hips.Suffix rps ON rp.SuffixId = rps.SuffixId
        WHERE   e.PatientId = @PatientId
                AND ( e.EpisodeLifecycleId = 11  -- EpisodeLifecycleId 11 is Admitted, 13 is Discharged
                      OR e.EpisodeLifecycleId = 13
                      AND e.DischargeDate > @DischargeDateCutoff
                    )
     END

GO

DROP PROCEDURE [hips].[PatientInHospitalGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   20 Nov 2013
-- Description:   Gets a list of patients in hospital who have an active IHI and match other given criteria.
-- Modified:
-- 17 March 2014 - Meet SHINeH Requirements: with or without IHI or both, return ConsentWithdrawn, EpisodeCount, AdmissionReason, SourceSystemEpisodeId
-- =============================================
CREATE PROCEDURE [hips].[PatientInHospitalGet] 
(
	@HospitalCodeSystem VARCHAR(20),
	@HospitalId INT = NULL,
	@WithIhi BIT = NULL,
	@WithPcehr BIT = NULL,
	@ExcludeMedicareExclusions BIT,
	@DaysDischarged INT
)
AS
BEGIN

DECLARE @HealthProviderOrganisationNetworkId INT
SET @HealthProviderOrganisationNetworkId = (
    SELECT hpo.HealthProviderOrganisationNetworkId 
    FROM hips.HealthProviderOrganisation hpo
    INNER JOIN hips.Hospital h 
                ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
    WHERE h.HospitalId = @HospitalId
)

DECLARE @DischargeDateCutoff DATETIME
SET @DischargeDateCutoff = DATEADD(d, -@DaysDischarged, GETDATE())

-- Temporary table to store ID of the most recent matching episode for each
-- patient who matches the conditions in the parameters to this stored procedure
CREATE TABLE #MatchingEpisodes (
	 EpisodeId INT NOT NULL
    ,EpisodeCount INT NOT NULL
)

-- In case there are multiple episodes with the same admission date, pick the
-- most recently created episode.
INSERT	#MatchingEpisodes
SELECT	MAX(e.EpisodeId)
       ,MAX(a.EpisodeCount)
FROM hips.Episode e
 INNER JOIN (
	-- Determine the admission date of the most recent episode for each patient who
	-- has a current episode in the required hospital and meets the WithPcehr condition.
	SELECT	e.PatientId,
			MAX(e.AdmissionDate) AS MaxAdmit,
			COUNT(e.EpisodeId) AS EpisodeCount
	FROM	hips.HospitalPatient hp
	 INNER JOIN hips.Episode e
		ON hp.PatientId = e.PatientId
     INNER JOIN hips.Hospital h
	    ON hp.HospitalId = h.HospitalId
	 LEFT JOIN hips.HealthProviderOrganisationPatient hpop
		ON hp.PatientMasterId = hpop.PatientMasterId
		AND h.HealthProviderOrganisationId = hpop.HealthProviderOrganisationId
	WHERE (@HospitalId IS NULL OR hp.HospitalId = @HospitalId)
	  AND (@WithPcehr IS NULL 
			OR   @WithPcehr = '0' AND (    (hpop.PcehrAdvertised = 0 AND hpop.PcehrDisclosed = 0) 
										OR (hpop.HealthProviderOrganisationId IS NULL AND hpop.PatientMasterId IS NULL) 
									  )
			OR   @WithPcehr = '1' AND (hpop.PcehrAdvertised = 1 OR hpop.PcehrDisclosed = 1)
	  )
	AND (	e.EpisodeLifecycleId = 11  -- EpisodeLifecycleId 11 is Admitted, 13 is Discharged
		OR	e.EpisodeLifecycleId = 13 AND e.DischargeDate > @DischargeDateCutoff
		)
	GROUP BY e.PatientId
 ) a 
	ON e.PatientId = a.PatientId
	AND e.AdmissionDate = a.MaxAdmit
WHERE	(	e.EpisodeLifecycleId = 11
		OR	e.EpisodeLifecycleId = 13 AND e.DischargeDate > @DischargeDateCutoff
		)
GROUP BY e.PatientId


SELECT pm.PatientMasterId, 
		pm.CurrentSexId,
		pm.DateOfBirth,
		pm.MedicareNumber,
		pm.MedicareIrn,
		pm.DvaNumber,
		pmn.FamilyName,
		pmn.GivenNames,
		pmnt.Description AS Title,
		pmns.Description AS Suffix,
		pmi.Ihi,
		pmi.IhiStatusId,
		pmi.IhiRecordStatusId,
		pmi.DateLastValidated AS IhiLastValidated,
		pmi.RegisteredSexId,
		pmi.RegisteredFamilyName,
		pmi.RegisteredGivenName,
		pmi.HealthProviderOrganisationNetworkId,
		hp.HospitalId,
		hp.Mrn,
		hc.Code as HospitalCode,
		h.Name as HospitalName,
		e.Ward,
		e.Room,
		e.Bed,
		e.AdmissionDate,
		e.DischargeDate,
		rp.FamilyName AS ResponsibleProviderFamilyName,
		rp.GivenNames AS ResponsibleProviderGivenNames,
		rpt.Description AS ResponsibleProviderTitle,
		rps.Description AS ResponsibleProviderSuffix,
		hpop.PcehrAdvertised,
		hpop.PcehrDisclosed,
		e.ConsentWithdrawn,
		me.EpisodeCount,
		e.AdmissionReason,
		e.SourceSystemEpisodeId
FROM #MatchingEpisodes me
 INNER JOIN hips.Episode e
	ON me.EpisodeId = e.EpisodeId
 INNER JOIN hips.HospitalPatient hp
	ON e.PatientId = hp.PatientId
 INNER JOIN hips.PatientMaster pm
    ON hp.PatientMasterId = pm.PatientMasterId
 INNER JOIN hips.PatientMasterName pmn
	ON pm.PatientMasterId = pmn.PatientMasterId
	AND pmn.NameTypeId = 2 -- Current Name
 INNER JOIN hips.HospitalCode hc
	ON hp.HospitalId = hc.HospitalId
	AND hc.CodeSystemId = (SELECT CodeSystemId FROM hips.CodeSystem WHERE Code = @HospitalCodeSystem)
 INNER JOIN hips.Hospital h
    ON hp.HospitalId = h.HospitalId
 INNER JOIN hips.HealthProviderOrganisation hpo
	ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
 INNER JOIN hips.HealthProviderIndividual rp
	ON e.ResponsibleProviderId = rp.HealthProviderIndividualId
 LEFT JOIN hips.Title pmnt
    ON pmn.TitleId = pmnt.TitleId
 LEFT JOIN hips.Suffix pmns
    ON pmn.SuffixId = pmns.SuffixId
 INNER JOIN hips.Title rpt
    ON rp.TitleId = rpt.TitleId
 INNER JOIN hips.Suffix rps
    ON rp.SuffixId = rps.SuffixId
 LEFT JOIN hips.PatientMasterIhi pmi
	ON pm.PatientMasterId = pmi.PatientMasterId
	AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
 LEFT JOIN hips.HealthProviderOrganisationPatient hpop
	ON pm.PatientMasterId = hpop.PatientMasterId
	AND h.HealthProviderOrganisationId = hpop.HealthProviderOrganisationId
 WHERE
	(	@WithIhi IS NULL 
		OR (@WithIhi = '0' AND (pmi.PatientMasterId IS NULL OR pmi.Ihi IS NULL OR pmi.Ihi = ''))
		OR (
			@WithIhi = '1' 
			AND pmi.IhiStatusId = 1 -- Active IHI
			AND pmi.IhiRecordStatusId = 1 -- Verified IHI
			AND LEN(pmi.Ihi) = 16 -- Correct Length IHI				
		)
	)
AND (	@ExcludeMedicareExclusions = '0'
		OR pm.MedicareNumber IS NULL
		OR pm.MedicareNumber NOT IN (SELECT MedicareNumber FROM hips.MedicareExclusion)
	)

END

GO

DROP PROCEDURE [hips].[PatientMasterAddressDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 Oct 2012
-- Description:   Delete a record from the Patient Master Address Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterAddressDelete] 
(
	@PatientMasterId INT
	
)
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @intErrorCode int,
		@intRowsAffected Int

    SET @intErrorCode = 0
	SELECT @intErrorCode = 0,
			@intRowsAffected = 0
			
	DELETE hips.PatientMasterAddress
	WHERE PatientMasterId = @PatientMasterId
	SELECT @intErrorCode = @@ERROR,
		@intRowsAffected = @@ROWCOuNT
	
	SELECT @intRowsAffected AS RowsAffected

    RETURN(@intErrorCode)


END
GO

DROP PROCEDURE [hips].[PatientMasterAddressInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 Oct 2012
-- Description:   Inserts a record into the Patien Master Address Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterAddressInsert] 
(
	@PatientMasterId INT,
	@AddressId INT,
	@UserModified varchar(256)
)
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @intErrorCode int

    SET @intErrorCode = 0

	INSERT INTO hips.PatientMasterAddress
	        ( PatientMasterId ,
	          AddressId ,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	        )
	VALUES  (	@PatientMasterId,
				@AddressId,
				GETDATE(),
				@UserModified,
				GETDATE(),
				@UserModified
	
	        )
	SELECT @intErrorCode = @@ERROR 
    IF @intErrorCode = 0
    BEGIN
        SELECT PatientMasterId,
                AddressId,
                [DateCreated],
                [UserCreated],
                [DateModified],
                [UserModified]
         FROM [hips].[PatientMasterAddress]
         WHERE [PatientMasterId] = @PatientMasterId
         AND [AddressId] = @AddressId
    END

    RETURN(@intErrorCode)


END
GO

DROP PROCEDURE [hips].[PatientMasterContactGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 15 Oct 2012
-- Description:   Gets all electronic contact details for a patient
-- 05/06/2014 - Fix name of ContactMethodDescription
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterContactGet] 
(
	@PatientMasterId INT
)
AS
BEGIN
	SELECT  c.ContactId,
			c.Detail,
			c.ContactMethodId,
			cm.TelecommunicationType,
			cm.Description AS ContactMethodDescription,
			cm.CdaType,
			c.DateCreated,
			c.UserCreated,
			c.DateModified,
			c.UserModified
			
	FROM  hips.PatientMasterContact AS pmc
	INNER JOIN hips.Contact AS c ON c.ContactId = pmc.ContactId
	INNER JOIN hips.ContactMethod AS cm ON cm.ContactMethodId = c.ContactMethodId
	WHERE pmc.PatientMasterId = @PatientMasterId
END
GO

DROP PROCEDURE [hips].[PatientMasterContactInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 Oct 2012
-- Description:   Inserts a record into the Patien Master Contact Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterContactInsert] 
(
	@PatientMasterId INT,
	@ContactId INT,
	@UserModified varchar(256)
)
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @intErrorCode int

    SET @intErrorCode = 0

	INSERT INTO hips.PatientMasterContact
	        ( PatientMasterId ,
	          ContactId ,
	          DateCreated ,
	          UserCreated ,
	          DateModified ,
	          UserModified
	        )
	VALUES  (	@PatientMasterId,
				@ContactId,
				GETDATE(),
				@UserModified,
				GETDATE(),
				@UserModified
	
	        )
	SELECT @intErrorCode = @@ERROR 
    IF @intErrorCode = 0
    BEGIN
        SELECT PatientMasterId,
                ContactId,
                [DateCreated],
                [UserCreated],
                [DateModified],
                [UserModified]
         FROM [hips].[PatientMasterContact]
         WHERE [PatientMasterId] = @PatientMasterId
         AND [ContactId] = @ContactId
    END

    RETURN(@intErrorCode)


END
GO

DROP PROCEDURE [hips].[PatientMasterDemographicsByHospitalGet] 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Create date:   23/10/2013
-- Description:   Gets a specific record from the Patient Master table.
--
-- Lookup Types Supported:
--    1. Demographic search using all of:
--         CurrentLastName, CurrentFirstNames, DateOfBirth, CurrentSexId
--       and one of:
--           DvaNumber, MedicareNumber
--       and optionally MedicareIrn.
--       For demographic search the business logic must pass a valid medicare
--       number or null, but avoid passing a special value like '8' or '0',
--       which could match records that it should not match.
--
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterDemographicsByHospitalGet] 
(
	@HospitalId INT,
	@CurrentLastName VARCHAR(80) = NULL,
	@CurrentFirstNames VARCHAR(80) = NULL,
	@DateOfBirth DATETIME = NULL,
	@CurrentSexId INT = NULL,
	@MedicareNumber VARCHAR(12) = NULL,
	@MedicareIrn VARCHAR(3) = NULL,
	@DvaNumber VARCHAR(18) = NULL
)
AS
BEGIN
	SELECT pm.[PatientMasterId]
		 , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
		 , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
		 , pmi.Ihi
		 , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
		 , ihis.Description AS IhiStatus
		 , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
		 , pmi.[RegisteredFamilyName]
		 , pmi.[RegisteredGivenName]
		 , irs.Description AS IhiRecordStatus
		 , pmi.DateLastValidated AS IhiLastValidated
		 , pm.[MedicareNumber]
		 , pm.[MedicareIrn]
		 , pm.[IsMedicareNumberValid]
		 , pm.[DvaNumber]
		 , pm.[StatePatientId]
		 , pm.[DateOfBirth]
		 , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
		 , pm.[DateOfDeath]
		 , pm.[DeathIndicatorId]
		 , pm.[DateCreated]
		 , pm.[UserCreated]
		 , pm.[DateModified]
		 , pm.[UserModified]
    FROM	[hips].[PatientMaster] AS pm
    LEFT JOIN [hips].HospitalPatient AS p ON pm.PatientMasterID = p.PatientMasterID
	LEFT JOIN [hips].[Hospital] AS h ON h.HospitalID = p.HospitalID
	LEFT JOIN [hips].[HealthProviderOrganisation] AS hpo ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
    LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID] AND hpo.HealthProviderOrganisationNetworkId = pmi.HealthProviderOrganisationNetworkId
	LEFT JOIN [hips].PatientMasterName pmn ON pmn.PatientMasterId = pm.PatientMasterId AND pmn.NameTypeId = 2
	LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
	LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId
	
    WHERE	(pmn.[FamilyName] = @CurrentLastName)
	AND		(pmn.[GivenNames] = @CurrentFirstNames)
	AND		(pm.[DateOfBirth] = @DateOfBirth)
	AND		(pm.[CurrentSexID] = @CurrentSexID)
	AND		(@DvaNumber IS NULL OR pm.[DvaNumber] = @DvaNumber)
	-- The business logic must ensure that special values (invalid/ineligible) are passed in as null to avoid matching
	AND		(@MedicareNumber IS NULL OR pm.[MedicareNumber] = @MedicareNumber)

	-- Do match even if the record has a null sequence number
	AND		(@MedicareIrn IS NULL OR pm.[MedicareIrn] IS NULL OR pm.[MedicareIrn] = @MedicareIrn)
	AND		h.HospitalId = @HospitalId

END

GO

DROP PROCEDURE [hips].[PatientMasterGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 12 October 2012
-- Description:   Gets a specific record from the Patient Master table.
--
-- Lookup Types Supported:
--    1. PatientMasterId (e.g. 1234567)
--    2. StatePatientId (e.g. '100000090751')
--    3. Ihi (e.g. '8003604567901177')
--    4. HospitalId and Mrn  (e.g. 71, '01234567')
--    5. HospitalCode and CodeSystemId and Mrn ('FMC', 2, '01234567')
--    6. Demographic search using all of:
--         CurrentLastName, CurrentFirstNames, DateOfBirth, CurrentSexId
--       and one of:
--           DvaNumber, MedicareNumber
--       and optionally MedicareIrn.
--       For demographic search the business logic must pass a valid medicare
--       number or null, but avoid passing a special value like '8' or '0',
--       which could match records that it should not match.
--
-- Modified: 23/11/2012 added IHI and StatePatientId lookup
-- Modified: 18/12/2012 removed PcehrAdvertised
-- Modified: 24/01/2012 added conditions to prevent returning all patients
-- Modified: 07/05/2013 tightened use of demographic search
-- Modified: 16/06/2013 added date of death indicator
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterGet] 
(
	@PatientMasterId INT = NULL,
	@HospitalId INT = NULL,
	@HospitalCode VARCHAR(64) = NULL,
	@CodeSystemId INT = NULL,
	@Mrn VARCHAR(20) = NULL,
	@CurrentLastName VARCHAR(80) = NULL,
	@CurrentFirstNames VARCHAR(80) = NULL,
	@DateOfBirth DATETIME = NULL,
	@CurrentSexId INT = NULL,
	@MedicareNumber VARCHAR(12) = NULL,
	@MedicareIrn VARCHAR(3) = NULL,
	@DvaNumber VARCHAR(18) = NULL,
	@Ihi VARCHAR(16) = NULL,
	@StatePatientId VARCHAR(64) = NULL,
	@HealthProviderOrganisationNetworkId INT = NULL
)
AS
BEGIN
	IF @HospitalId IS NULL AND @CodeSystemId IS NOT NULL AND @HospitalCode IS NOT NULL 
	BEGIN
	  SELECT @HospitalId = [HospitalId]
	  FROM [hips].[HospitalCode]
	  WHERE [CodeSystemId] = @CodeSystemId
	  AND [Code] = @HospitalCode
	END

	IF @HealthProviderOrganisationNetworkId IS NULL AND @HospitalId IS NULL AND @HospitalCode IS NULL
	BEGIN
		RAISERROR(50003, -1, -1) 
	END
	
	IF @HealthProviderOrganisationNetworkId IS NULL AND @HospitalId IS NOT NULL
	BEGIN
		SELECT @HealthProviderOrganisationNetworkId = hpo.HealthProviderOrganisationNetworkId
		FROM [hips].HealthProviderOrganisation hpo
		INNER JOIN [hips].[Hospital] h ON hpo.HealthProviderOrganisationId = h.HealthProviderOrganisationId
		WHERE h.HospitalId = @HospitalId
	END
	
	IF @HealthProviderOrganisationNetworkId IS NULL AND @HospitalId IS NULL AND @CodeSystemId IS NOT NULL AND @HospitalCode IS NOT NULL 
	BEGIN
		SELECT @HealthProviderOrganisationNetworkId = hpo.HealthProviderOrganisationNetworkId
		FROM [hips].HealthProviderOrganisation hpo
		INNER JOIN [hips].[Hospital] h ON hpo.HealthProviderOrganisationId = h.HealthProviderOrganisationId
		INNER JOIN [hips].[HospitalCode] hc ON h.HospitalId = hc.HospitalId
		WHERE hc.CodeSystemId = @CodeSystemId AND hc.Code = @HospitalCode
	END

	IF @PatientMasterID IS NULL
	BEGIN
		IF @HospitalId IS NOT NULL AND @Mrn IS NOT NULL
		BEGIN
			SELECT	@PatientMasterId = [PatientMasterId]
			FROM	[hips].[HospitalPatient]
			WHERE	[HospitalId] = @HospitalId
			AND		[Mrn] = @Mrn
		END
		ELSE IF @Ihi IS NOT NULL
		BEGIN
			SELECT	@PatientMasterId = pmi.[PatientMasterId]
			FROM	[hips].[PatientMasterIhi] pmi
			JOIN hips.HospitalPatient hp ON pmi.PatientMasterId = hp.PatientMasterId
			JOIN hips.Hospital h ON hp.HospitalId = h.HospitalId
			JOIN hips.HealthProviderOrganisation hpo ON hpo.HealthProviderOrganisationId = h.HealthProviderOrganisationId
			WHERE	pmi.[Ihi] = @Ihi AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
			AND hp.HospitalId NOT IN  (SELECT VisitorHospitalId FROM hips.HealthProviderOrganisation WHERE VisitorHospitalId IS NOT NULL)
		END
		ELSE IF @StatePatientId IS NOT NULL
		BEGIN
			SELECT	@PatientMasterId = [PatientMasterId]
			FROM	[hips].[PatientMaster]
			WHERE	[StatePatientId] = @StatePatientId
		END				
	END	

	IF  @PatientMasterId IS NULL
	AND @Mrn IS NULL
	AND @StatePatientId IS NULL
	AND @Ihi IS NULL
	AND @CurrentFirstNames IS NOT NULL 
	AND @CurrentLastName IS NOT NULL
	AND @DateOfBirth IS NOT NULL
	AND (  @MedicareNumber IS NOT NULL
		OR @DvaNumber IS NOT NULL
		)
	BEGIN
		SELECT pm.[PatientMasterId]
			 , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
			 , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
			 , pmi.Ihi
			 , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
			 , ihis.Description AS IhiStatus
			 , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
			 , pmi.[RegisteredFamilyName]
			 , pmi.[RegisteredGivenName]
			 , irs.Description AS IhiRecordStatus
			 , pmi.DateLastValidated AS IhiLastValidated
			 , pm.[MedicareNumber]
			 , pm.[MedicareIrn]
			 , pm.[IsMedicareNumberValid]
			 , pm.[DvaNumber]
			 , pm.[StatePatientId]
			 , pm.[DateOfBirth]
			 , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
			 , pmi.[HealthProviderOrganisationNetworkId]
			 , pm.[DateOfDeath]
			 , pm.[DeathIndicatorId]
			 , pm.[DateCreated]
			 , pm.[UserCreated]
			 , pm.[DateModified]
			 , pm.[UserModified]
        FROM	[hips].[PatientMaster] AS pm
        LEFT JOIN [hips].HospitalPatient AS p ON pm.PatientMasterID = p.PatientMasterID
		LEFT JOIN [hips].[Hospital] AS h ON h.HospitalID = p.HospitalID
		LEFT JOIN hips.HealthProviderOrganisation AS hpo
			ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId	
        LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID]
			AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
		LEFT JOIN [hips].PatientMasterName pmn ON pmn.PatientMasterId = pm.PatientMasterId AND pmn.NameTypeId = 2
		LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
		LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId
		
        WHERE	(pmn.[FamilyName] = @CurrentLastName)
		AND		(pmn.[GivenNames] = @CurrentFirstNames)
		AND		(pm.[DateOfBirth] = @DateOfBirth)
		AND		(pm.[CurrentSexID] = @CurrentSexID)
		AND		(@DvaNumber IS NULL OR pm.[DvaNumber] = @DvaNumber)
		-- The business logic must ensure that special values (invalid/ineligible) are passed in as null to avoid matching
		AND		(@MedicareNumber IS NULL OR pm.[MedicareNumber] = @MedicareNumber)

		-- Do match even if the record has a null sequence number
		AND		(@MedicareIrn IS NULL OR pm.[MedicareIrn] IS NULL OR pm.[MedicareIrn] = @MedicareIrn)
	END
	ELSE  -- Lookup by @PatientMasterID
	BEGIN
		SELECT pm.[PatientMasterID]
			 , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
			 , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
			 , pmi.Ihi
			 , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
			 , ihis.Description AS IhiStatus
			 , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
			 , irs.Description AS IhiRecordStatus
			 , pmi.[RegisteredFamilyName]
			 , pmi.[RegisteredGivenName]
			 , pmi.DateLastValidated AS IhiLastValidated
			 , pm.[MedicareNumber]
			 , pm.[MedicareIrn]
			 , pm.[IsMedicareNumberValid]
			 , pm.[DvaNumber]
			 , pm.[StatePatientId]
			 , pm.[DateOfBirth]
			 , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
			 , pmi.[HealthProviderOrganisationNetworkId]
			 , pm.[DateOfDeath]
			 , pm.[DeathIndicatorId]
			 , pm.[DateCreated]
			 , pm.[UserCreated]
			 , pm.[DateModified]
			 , pm.[UserModified]         
	     FROM	[hips].[PatientMaster] AS pm
         INNER JOIN hips.HospitalPatient AS hp
			ON pm.PatientMasterId = hp.PatientMasterId
		 INNER JOIN hips.Hospital AS h
			ON hp.HospitalId = h.HospitalId
		 INNER JOIN hips.HealthProviderOrganisation AS hpo
			ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId	     
         LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID]
			AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
         LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
		 LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId

        WHERE	pm.[PatientMasterID] = @PatientMasterID
		AND     (@DateOfBirth IS NULL OR @DateOfBirth = [DateOfBirth])
	END
END
GO

DROP PROCEDURE [hips].[PatientMasterIhiDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 25 July 2012
-- Description:   Deletes an existing record from the PatientMasterIhi Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterIhiDelete]
(
@PatientMasterId INT
, @HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @intErrorCode INT,
	@intRowsAffected INT
	SELECT  @intErrorCode = 0,
			@intRowsAffected = 0
	DELETE
		[hips].[PatientMasterIhi]
	WHERE [PatientMasterId] = @PatientMasterId AND HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
	
	SELECT @intErrorCode = @@ERROR,
	@intRowsAffected = @@ROWCOUNT
	
	SELECT @intRowsAffected AS RowsAffected
	RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PatientMasterIhiGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:   Gets a specific record or multiple records from the
--                PatientMasterIhi table.
-- Create date:   18 April 2012
-- Modified:
-- 30 Jul 2012 -  Added additional fields
-- 19 Dec 2012 -  Removed PcehrAdvertised
-- 27 Jun 2013 -  Added ability to get by IHI Status.
--                              Removed date of birth return because it
--                              doesn't take into account overrides.
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterIhiGet] 
(
	 @PatientMasterId INT = NULL
	,@IhiStatusId INT = NULL
	,@HealthProviderOrganisationNetworkId INT = NULL
)
AS
BEGIN
		SELECT	  p.[PatientMasterId]
			, p.[Ihi]
			, p.[IhiStatusId]
			, p.[IhiRecordStatusId]
			, p.[RegisteredFamilyName]
			, p.[RegisteredGivenName]
			, p.[RegisteredSexId]
			, p.[DateLastValidated]
			, p.[DateCreated]
			, p.[UserCreated]
			, p.[DateModified]
			, p.[UserModified]
			, p.[RegisteredDateOfBirth]
			, p.[HealthProviderOrganisationNetworkId]
	FROM	hips.PatientMasterIhi p
	WHERE	(@PatientMasterId IS NULL OR p.PatientMasterId = @PatientMasterId)
	AND		(@IhiStatusId IS NULL OR p.IhiStatusId = @IhiStatusId)
	AND HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
END
GO

DROP PROCEDURE [hips].[PatientMasterIhiHistoryInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 27 July 2012
-- Description:   Inserts a new record into the PatientMasterIhiHistory Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterIhiHistoryInsert] 
(
	  @PatientMasterId INT
	, @Ihi VARCHAR(50) = NULL
	, @IhiRecordStatusId INT
	, @IhiStatusId INT
	, @UserModified VARCHAR(256)
	, @HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @IhiHistoryId int

    SET @intErrorCode = 0

    INSERT
     INTO [hips].[PatientMasterIhiHistory]
         ( [PatientMasterId]
         , [Ihi]
         , [IhiRecordStatusId]
         , [IhiStatusId]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         , [HealthProviderOrganisationNetworkId]
         )
    VALUES
         ( @PatientMasterId
         , @Ihi
         , @IhiRecordStatusId
         , @IhiStatusId
         , GETDATE()
         , @UserModified
         , GETDATE()
         , @UserModified
         , @HealthProviderOrganisationNetworkId
         )

    SELECT @intErrorCode = @@ERROR , @IhiHistoryId = SCOPE_IdENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [IhiHistoryId]
             , [PatientMasterId]
             , [Ihi]
             , [IhiRecordStatusId]
			 , [IhiStatusId]
             , [DateCreated]
             , [UserCreated]
             , [DateModified]
             , [UserModified]
             , [HealthProviderOrganisationNetworkId]
         FROM [hips].[PatientMasterIhiHistory]
         WHERE [IhiHistoryId] = @IhiHistoryId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PatientMasterIhiInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 17 April 2012
-- Description:   Inserts a new record into the PatientMasterIhi Table
-- Modified:
-- 30 Jul 2012 - Added additional fields
-- 19 Dec 2012 - Removed PcehrAdvertised and AccessCodeRequiredId
-- 05 Jan 2014 - Added new columns for HealthProviderOrganisationNetworkId
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterIhiInsert] 
(
	  @PatientMasterId INT
	, @Ihi VARCHAR(50) = NULL
	, @IhiStatusId INT
	, @IhiRecordStatusId INT
	, @RegisteredFamilyName VARCHAR(80)
	, @RegisteredGivenName VARCHAR(80)
	, @RegisteredSexId INT
	, @RegisteredDateOfBirth DATETIME = NULL
	, @DateLastValidated DATETIME
	, @UserModified VARCHAR(256)
	, @HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int

    SET @intErrorCode = 0

     INSERT
     INTO [hips].[PatientMasterIhi]
         ( [PatientMasterId]
         , [Ihi]
         , [IhiStatusId]
		 , [IhiRecordStatusId]
		 , [RegisteredFamilyName]
		 , [RegisteredGivenName]
		 , [RegisteredSexId]
         , [DateLastValidated]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         , [RegisteredDateOfBirth]
         , [HealthProviderOrganisationNetworkId]
         )
    VALUES
         ( @PatientMasterId
         , @Ihi
         , @IhiStatusId 
		 , @IhiRecordStatusId 
		 , @RegisteredFamilyName
		 , @RegisteredGivenName
		 , @RegisteredSexId 
         , @DateLastValidated
         , GETDATE()
         , @UserModified
         , GETDATE()
         , @UserModified
         , @RegisteredDateOfBirth
         , @HealthProviderOrganisationNetworkId
         )

    SELECT @intErrorCode = @@ERROR 
    IF @intErrorCode = 0
    BEGIN
        SELECT [PatientMasterId]
             , [Ihi]
             , [IhiStatusId]
			 , [IhiRecordStatusId]
			 , [RegisteredFamilyName]
			 , [RegisteredGivenName]
			 , [RegisteredSexId]
			 , [RegisteredDateOfBirth]
             , [DateLastValidated]
             , [DateCreated]
             , [UserCreated]
             , [DateModified]
             , [UserModified]
             , [HealthProviderOrganisationNetworkId]
         FROM [hips].[PatientMasterIhi]
         WHERE [PatientMasterId] = @PatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
    END

    RETURN(@intErrorCode)
END

GO

DROP PROCEDURE [hips].[PatientMasterIhiUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 17 April 2012
-- Description:   Updates an existing record into the PatientMasterIhi Table
-- 19 Dec 2012 - Removed PcehrAdvertised and AccessCodeRequiredId
-- 05 Jan 2015 - Added new columns for HealthProviderOrganisationNetworkId
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterIhiUpdate] 
(
		  @PatientMasterId INT
	, @Ihi VARCHAR(50) = NULL
	, @IhiStatusId INT
	, @RegisteredFamilyName VARCHAR(80)
	, @RegisteredGivenName VARCHAR(80)
	, @IhiRecordStatusId INT
	, @RegisteredSexId INT
	, @RegisteredDateOfBirth DATETIME = NULL 
	, @DateLastValidated DATETIME
	, @UserModified VARCHAR(256)
	, @DateModified datetime
	, @HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int
    SET @intErrorCode = 0

 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[PatientMasterIhi]
           SET [Ihi] = @Ihi
           	 , [IhiStatusId] = @IhiStatusId 
			 , [IhiRecordStatusId] = @IhiRecordStatusId 
			 , [RegisteredFamilyName] = @RegisteredFamilyName
			 , [RegisteredGivenName] = @RegisteredGivenName
			 , [RegisteredSexId] = @RegisteredSexId 
			 , [RegisteredDateOfBirth] = @RegisteredDateOfBirth
             , [DateLastValidated] = @DateLastValidated
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified                   
         WHERE [PatientMasterId] = @PatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId       

        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
	SELECT [PatientMasterId]
					 , [Ihi]
					 , [IhiStatusId]
					 , [IhiRecordStatusId]
					 , [RegisteredFamilyName]
					 , [RegisteredGivenName]
					 , [RegisteredSexId]
					 , [DateLastValidated]
					 , [DateCreated]
					 , [UserCreated]
					 , [DateModified]
					 , [UserModified]
					 , [RegisteredDateOfBirth]
					 , [HealthProviderOrganisationNetworkId]
					 FROM [hips].[PatientMasterIhi]            
		WHERE [PatientMasterId] = @PatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId   
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[PatientMasterInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 11 Oct 2012
-- Description:   Inserts a new record into the Patient Master Table
-- Modified: 23/11/2012 added StatePatientId
-- Modified: 18/12/2012 removed PcehrAdvertised
-- Modified: 16/06/2013 added date of death indicator
-- Modified: 05/01/2015 Added new columns for HealthProviderOrganisationNetworkId
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterInsert] 
(

	@CurrentSexId INT,
	@DeathIndicatorId INT = null,
	@DateOfBirth DATETIME,
	@DateOfDeath DATETIME = null,
	@MedicareNumber VARCHAR(12) = null,
	@MedicareIrn VARCHAR(3) = null,
	@IsMedicareNumberValid INT = null,
	@DvaNumber VARCHAR(18) = NULL,
	@StatePatientId VARCHAR(64) = NULL,
	@UserModified varchar(256),
	@HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
    SET NOCOUNT ON
	
	IF (@MedicareIrn = '0') 
	BEGIN
		SET @MedicareIrn = NULL
	END  

    DECLARE @intErrorCode INT
		,@PatientMasterId INT

    SET @intErrorCode = 0

    INSERT
     INTO [hips].[PatientMaster]
         ( 
           [CurrentSexId]
		 , [DeathIndicatorId]
         , [DateOfBirth]
         , [DateOfDeath]
         , [MedicareNumber]
         , [MedicareIrn]
         , [IsMedicareNumberValid]
         , [DvaNumber]
		 , [StatePatientId]
         , [DateCreated]
         , [UserCreated]
         , [DateModified]
         , [UserModified]
         )
    VALUES
         ( 
           @CurrentSexId
		 , @DeathIndicatorId 
         , @DateOfBirth
         , @DateOfDeath
         , @MedicareNumber
         , @MedicareIrn
         , @IsMedicareNumberValid
         , @DvaNumber
		 , @StatePatientId
         , GETDATE()
         , @UserModified
         , GETDATE()
         , @UserModified
         )

    SELECT @intErrorCode = @@ERROR 
		, @PatientMasterId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
		SELECT pm.[PatientMasterID]
			 , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
			 , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
			 , pmi.Ihi
			 , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
			 , ihis.Description AS IhiStatus
			 , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
			 , pmi.[RegisteredFamilyName]
			 , pmi.[RegisteredGivenName]
		     , irs.Description AS IhiRecordStatus
			 , pmi.DateLastValidated AS IhiLastValidated
			 , pmi.HealthProviderOrganisationNetworkId
			 , pm.[MedicareNumber]
			 , pm.[MedicareIrn]
			 , pm.[IsMedicareNumberValid]
			 , pm.[DvaNumber]
			 , pm.[StatePatientId]
			 , pm.[DateOfBirth]
			 , pm.[DateOfDeath]
			 , pm.[DeathIndicatorId]
			 , pm.[DateCreated]
			 , pm.[UserCreated]
			 , pm.[DateModified]
			 , pm.[UserModified]         
	     FROM	[hips].[PatientMaster] AS pm
	     LEFT JOIN [hips].[HospitalPatient] AS hp ON pm.PatientMasterId = hp.PatientMasterId
	     LEFT JOIN [hips].[Hospital] AS h ON hp.HospitalId = h.HospitalId
	     LEFT JOIN [hips].[HealthProviderOrganisation] AS hpo ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
         LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID] AND hpo.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
         LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
		 LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId

        WHERE	pm.[PatientMasterID] = @PatientMasterID 
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PatientMasterMerge]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Create date: 11 April 2013
-- Description:   Merges two records in the Patient Master Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterMerge] 
(
    @FromPatientMasterId INT,
    @ToPatientMasterId INT,
    @UserModified varchar(256),
    @HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE @intErrorCode INT
        ,@FromIhi VARCHAR(16)
        ,@ToIhi VARCHAR(16)	
        ,@ConflictStatus INT
        ,@UnknownStatus INT
        ,@Message VARCHAR(512)
        ,@FromStatePatientId VARCHAR(64)
        ,@ToStatePatientId VARCHAR(64)
        ,@FromMrn VARCHAR(20)
        ,@ToMrn VARCHAR(20)
        ,@HospitalDesc VARCHAR(60)

    IF @ToPatientMasterId = @FromPatientMasterId
    BEGIN
        SET @intErrorCode = 50002
        RAISERROR (@intErrorCode, -1, -1, N'PatientMasterId', @FromPatientMasterId)

        RETURN(@intErrorCode)
    END	
        
    SET @intErrorCode = 0
    SET @ConflictStatus = 102
    SET @UnknownStatus = -1

    -- Retrieve the IHI and SAUHI from both records
    SET @FromIhi = (SELECT [Ihi]
    FROM [hips].[PatientMasterIhi]
    WHERE [PatientMasterId]	= @FromPatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId)
    
    SET @ToIhi = (SELECT [Ihi]
    FROM [hips].[PatientMasterIhi]
    WHERE [PatientMasterId]	= @ToPatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId)
    
    SET @FromStatePatientId = (SELECT [StatePatientId]
    FROM [hips].[PatientMaster]
    WHERE [PatientMasterId] = @FromPatientMasterId)

    SET @ToStatePatientId = (SELECT [StatePatientId]
    FROM [hips].[PatientMaster]
    WHERE [PatientMasterId] = @ToPatientMasterId)

    -- Find out whether there are any MRNs from the same hospital on both patient masters.
    -- This should not happen, because the MRNs should have been merged in the PAS first.
    -- If there is a case of this occurring, grab the most recently updated MRN from each.
    -- This will be used to create a merge conflict alert if the IHI's are different.
    SELECT	 @FromMrn = hp1.Mrn
            ,@ToMrn = hp2.Mrn
            ,@HospitalDesc = h.[Description]
    FROM	hips.HospitalPatient hp1
    INNER JOIN hips.HospitalPatient hp2 ON hp1.HospitalId = hp2.HospitalId
    INNER JOIN hips.Hospital h on h.HospitalId = hp1.HospitalId
    WHERE	hp1.PatientMasterId = @FromPatientMasterId
    AND		hp2.PatientMasterId = @ToPatientMasterId
    ORDER BY hp1.DateModified DESC, hp2.DateModified DESC

    -- Move all clinical data (but not demographic data) from the non-surviving to the surviving patient master.
    UPDATE [hips].[HospitalPatient]
    SET [PatientMasterId] = @ToPatientMasterId
    WHERE [PatientMasterId] = @FromPatientMasterId	

    UPDATE [hips].[DownloadedDocument]
    SET [PatientMasterId] = @ToPatientMasterId
    WHERE [PatientMasterId] = @FromPatientMasterId	

    
    IF @FromIhi IS NOT NULL
    BEGIN
        IF @ToIhi IS NOT NULL
        BEGIN
            IF @FromIhi = @ToIhi
            BEGIN
                -- The IHIs match, this should only happen if one is marked as a duplicate.
                -- Remove the IHI from the non-surviving patient master
                DELETE [hips].[PatientMasterIhi]
                WHERE [PatientMasterId] = @FromPatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
                
                -- Remove the alert status, set it back to unknown. HIPS will revalidate and if OK set to Active.
                UPDATE [hips].[PatientMasterIhi]
                SET [IhiStatusId] = @UnknownStatus
                WHERE [PatientMasterId] = @ToPatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
            END
            ELSE IF (@FromMrn IS NOT NULL AND @ToMrn IS NOT NULL)
            BEGIN
                -- There are two different IHI's on the patient masters!
                -- Also, there is an MRN on each PM, from the same hospital!
                -- This means that a PAS merge should have been done before this enterprise merge.
                -- Create IHI Conflict alert against both patient masters.
                UPDATE [hips].[PatientMasterIhi]
                SET [IhiStatusId] = @ConflictStatus
                    ,[DateModified] = GETDATE()
                    ,[UserModified] = @UserModified
                WHERE [PatientMasterId]	= @FromPatientMasterId
                OR	[PatientMasterId] = @ToPatientMasterId
				AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
                        
                SET @Message = 'IHI conflict merging SAUHI ' 
                    + @FromStatePatientId + ' having MRN '	+ @FromMrn + ' at ' + @HospitalDesc + ' and IHI ' + @FromIhi + ' into SAUHI '
                    + @ToStatePatientId + ' having MRN ' + @ToMrn + ' at ' + @HospitalDesc + ' and IHI ' + @ToIhi
                
                INSERT INTO [hips].IhiLookupAlert
                         ( PatientMasterId ,
                           Message ,
                           Request ,
                           Response ,
                           DateModified ,
                           UserModified ,
                           DateCreated ,
                           UserCreated
                         )
                 VALUES  ( @FromPatientMasterId , -- PatientMasterId - int
                           @Message, -- Message - varchar(512)
                           NULL, -- Request - xml
                           NULL, -- Response - xml
                           GETDATE() , -- DateModified - datetime
                           @UserModified , -- UserModified - varchar(256)
                           GETDATE() , -- DateCreated - datetime
                           @UserModified  -- UserCreated - varchar(256)
                         )
                INSERT INTO [hips].IhiLookupAlert
                         ( PatientMasterId ,
                           Message ,
                           Request ,
                           Response ,
                           DateModified ,
                           UserModified ,
                           DateCreated ,
                           UserCreated
                         )
                 VALUES  ( @ToPatientMasterId , -- PatientMasterId - int
                           @Message, -- Message - varchar(512)
                           NULL, -- Request - xml
                           NULL, -- Response - xml
                           GETDATE() , -- DateModified - datetime
                           @UserModified , -- UserModified - varchar(256)
                           GETDATE() , -- DateCreated - datetime
                           @UserModified  -- UserCreated - varchar(256)
                         )
            END
        END
        ELSE
        BEGIN
            -- The survivor's IHI field is blank.
            -- Transfer the IHI from the non-survivor to the survivor.

            -- 1. Remove the survivor's IHI information
            DELETE hips.PatientMasterIhi
            WHERE PatientMasterId = @ToPatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId

            -- 2. Copy the IHI to the survivor
            INSERT hips.PatientMasterIhi
                ( PatientMasterId ,
                  Ihi ,
                  IhiStatusId ,
                  IhiRecordStatusId ,
                  RegisteredGivenName ,
                  RegisteredFamilyName ,
                  RegisteredSexId ,
                  RegisteredDateOfBirth ,
                  DateLastValidated ,
                  DateCreated ,
                  UserCreated ,
                  DateModified ,
                  UserModified ,
                  HealthProviderOrganisationNetworkId
                )				
            SELECT @ToPatientMasterId
                ,fromIhi.Ihi
                ,fromIhi.IhiStatusId
                ,fromIhi.IhiRecordStatusId
                ,fromIhi.RegisteredGivenName
                ,fromIhi.RegisteredFamilyName
                ,fromIhi.RegisteredSexId
                ,fromIhi.RegisteredDateOfBirth
                ,fromIhi.DateLastValidated
                ,GETDATE()
                ,@UserModified
                ,GETDATE()
                ,@UserModified
                , fromIhi.HealthProviderOrganisationNetworkId
            FROM hips.PatientMasterIhi fromIhi
            WHERE fromIhi.PatientMasterId = @FromPatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
            
            -- 3. Remove the IHI from the non-survivor
            DELETE hips.PatientMasterIhi
            WHERE PatientMasterId = @FromPatientMasterId  AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
        END
    END
    ELSE
    BEGIN
        -- The non-survivor's IHI field is blank.
                
        -- Remove the alert status from the survivor, set it back to unknown. 
        -- HIPS will revalidate and if OK set to Active.
        UPDATE [hips].[PatientMasterIhi]
        SET [IhiStatusId] = @UnknownStatus
        WHERE [PatientMasterId] = @ToPatientMasterId AND [HealthProviderOrganisationNetworkId] = @HealthProviderOrganisationNetworkId
    END

    SELECT @intErrorCode = @@ERROR 
    IF @intErrorCode = 0
    BEGIN
        SELECT pm.[PatientMasterID]
             , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
             , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
             , pmi.Ihi
             , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
             , ihis.Description AS IhiStatus
             , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
             , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
             , pmi.[RegisteredFamilyName]
             , pmi.[RegisteredGivenName]
             , irs.Description AS IhiRecordStatus
             , pmi.DateLastValidated AS IhiLastValidated
             , pm.[MedicareNumber]
             , pm.[MedicareIrn]
             , pm.[IsMedicareNumberValid]
             , pm.[DvaNumber]
             , pm.[StatePatientId]
             , pm.[DateOfBirth]
             , pm.[DateOfDeath]
             , pm.[DateCreated]
             , pm.[UserCreated]
             , pm.[DateModified]
             , pm.[UserModified]         
         FROM	[hips].[PatientMaster] AS pm
         LEFT JOIN [hips].[HospitalPatient] AS hp ON pm.PatientMasterId = hp.PatientMasterId
         LEFT JOIN [hips].[Hospital] AS h ON hp.HospitalId = h.HospitalId
         LEFT JOIN [hips].[HealthProviderOrganisation] AS hpo ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
         LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID] AND hpo.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId	
         LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
         LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId
        WHERE	pm.[PatientMasterID] = @ToPatientMasterId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PatientMasterNameDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 October 2012
-- Description:   Deletes an existing record from the Patient Master Names Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterNameDelete] 
(
	  @PatientMasterNameId INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int,
			@intRowsAffected int

    SELECT  @intErrorCode = 0,
			@intRowsAffected = 0

    DELETE
     FROM [hips].[PatientMasterName]
     WHERE [PatientMasterNameId] = @PatientMasterNameId

    SELECT @intErrorCode = @@ERROR,
   		   @intRowsAffected = @@ROWCOUNT

	SELECT @intRowsAffected AS RowsAffected

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PatientMasterNameGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 October 2012
-- Description:   Gets a specified record from the Patient Master Name Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterNameGet] 
(
	@PatientMasterNameId INT = NULL,
	@PatientMasterId INT = NULL
)
AS
BEGIN
SELECT  pmn.PatientMasterNameId,
		pmn.PatientMasterId,
		pmn.TitleId,
		t.Description AS Title,
		pmn.GivenNames,
		pmn.FamilyName,
		pmn.SuffixId,
		s.Description AS Suffix,
		pmn.NameTypeId,
		nt.Description AS NameType,
		pmn.DateCreated,
		pmn.UserCreated,
		pmn.DateModified,
		pmn.UserModified
		
FROM  hips.PatientMasterName pmn
LEFT OUTER JOIN hips.Title AS t ON t.TitleId = pmn.TitleId
LEFT OUTER JOIN hips.Suffix AS s ON s.SuffixId = pmn.SuffixId
LEFT OUTER JOIN hips.NameType AS nt ON nt.NameTypeId = pmn.NameTypeId
WHERE (pmn.PatientMasterNameId = @PatientMasterNameId OR @PatientMasterNameId IS NULL)
AND   (pmn.PatientMasterId = @PatientMasterId OR @PatientMasterId IS NULL)
END
GO

DROP PROCEDURE [hips].[PatientMasterNameInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 October 2012
-- Description:   Inserts a new record into the Patient Master Name Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterNameInsert]
(
       @PatientMasterId INT
      ,@TitleId INT = NULL
      ,@GivenNames VARCHAR(80)
      ,@FamilyName VARCHAR(80)
      ,@SuffixId VARCHAR(80) = NULL
      ,@NameTypeId INT
	  ,@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @intErrorCode INT,
			@PatientMasterNameId  INT
    SET @intErrorCode = 0
    INSERT
     INTO [hips].[PatientMasterName]
         ( [PatientMasterId],
           [TitleId],
           [GivenNames],
           [FamilyName],
           [SuffixId],
           [NameTypeId],
           [DateCreated],
           [UserCreated],
           [DateModified],
           [UserModified]
         )
    VALUES
         (
            @PatientMasterId
           ,@TitleId
           ,@GivenNames
           ,@FamilyName
           ,@SuffixId
           ,@NameTypeId
          , GETDATE()
          , @UserModified
          , GETDATE()
          , @UserModified
         )
    SELECT @intErrorCode = @@ERROR 
           , @PatientMasterNameId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
       SELECT   pmn.PatientMasterNameId,
				pmn.PatientMasterId,
				pmn.TitleId,
				t.Description AS Title,
				pmn.GivenNames,
				pmn.FamilyName,
				pmn.SuffixId,
				s.Description AS Suffix,
				pmn.NameTypeId,
				nt.Description AS NameType,
				pmn.DateCreated,
				pmn.UserCreated,
				pmn.DateModified,
				pmn.UserModified
		FROM  hips.PatientMasterName pmn
		LEFT OUTER JOIN hips.Title AS t ON t.TitleId = pmn.TitleId
		LEFT OUTER JOIN hips.Suffix AS s ON s.SuffixId = pmn.SuffixId
		LEFT OUTER JOIN hips.NameType AS nt ON nt.NameTypeId = pmn.NameTypeId
		WHERE pmn.PatientMasterNameId = @PatientMasterNameId
    END
    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PatientMasterNameUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 18 October 2012
-- Description:   Updates an existing record in the Patient Master Names Table
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterNameUpdate] 
(
       @PatientMasterNameId INT
      ,@PatientMasterId INT
      ,@TitleId INT = NULL
      ,@GivenNames VARCHAR(80)
      ,@FamilyName VARCHAR(80)
      ,@SuffixId VARCHAR(80) = NULL
      ,@NameTypeId INT
	  ,@UserModified VARCHAR(256)
	  ,@DateModified datetime
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
            	FROM [hips].[PatientMasterName]
				WHERE [PatientMasterNameId] = @PatientMasterNameId AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'PatientMasterName', N'PatientMasterNameId', @PatientMasterNameId, N'DateModified', @DateModifiedText) 
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[PatientMasterName]
           SET [PatientMasterId] = @PatientMasterId
			  ,[TitleId] = @TitleId
			  ,[GivenNames] = @GivenNames
			  ,[FamilyName] = @FamilyName
			  ,[SuffixId] = @SuffixId
			  ,[NameTypeId] = @NameTypeId
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified
         WHERE [PatientMasterNameID] = @PatientMasterNameID

        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
       SELECT   pmn.PatientMasterNameId,
				pmn.PatientMasterId,
				pmn.TitleId,
				t.Description AS Title,
				pmn.GivenNames,
				pmn.FamilyName,
				pmn.SuffixId,
				s.Description AS Suffix,
				pmn.NameTypeId,
				nt.Description AS NameType,
				pmn.DateCreated,
				pmn.UserCreated,
				pmn.DateModified,
				pmn.UserModified
		FROM  hips.PatientMasterName pmn
		LEFT OUTER JOIN hips.Title AS t ON t.TitleId = pmn.TitleId
		LEFT OUTER JOIN hips.Suffix AS s ON s.SuffixId = pmn.SuffixId
		LEFT OUTER JOIN hips.NameType AS nt ON nt.NameTypeId = pmn.NameTypeId
		WHERE pmn.PatientMasterNameId = @PatientMasterNameId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[PatientMasterUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 19 October 2012
-- Description:   Updates an existing record in the Patient Master  Table
-- Modified: 18/12/2012 removed PcehrAdvertised
-- Modified: 16/06/2013 added date of death indicator
-- Modified: 05/01/2015 Added new columns for HealthProviderOrganisationNetworkId
-- =============================================
CREATE PROCEDURE [hips].[PatientMasterUpdate] 
(
    @PatientMasterId INT,
    @CurrentSexId INT,
    @DeathIndicatorId INT = null,
    @DateOfBirth DATETIME,
    @DateOfDeath DATETIME = null,
    @MedicareNumber VARCHAR(12) = null,
    @MedicareIrn VARCHAR(3) = null,
    @IsMedicareNumberValid INT = null,
    @DvaNumber VARCHAR(18) = NULL,	 
    @StatePatientId VARCHAR(64) = NULL, 
    @UserModified VARCHAR(256),
    @DateModified datetime,
	@HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
    SET NOCOUNT ON

    IF (@MedicareIrn = '0') 
    BEGIN
        SET @MedicareIrn = NULL
    END  

    DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
                FROM [hips].[PatientMaster]
                WHERE [PatientMasterId] = @PatientMasterId AND [DateModified] = @DateModified)
    BEGIN
        DECLARE @DateModifiedText NVARCHAR(30)
        SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
        RAISERROR (50001, -1, -1, N'PatientMaster', N'PatientMasterID', @PatientMasterID, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[PatientMaster]
           SET [CurrentSexId] = @CurrentSexId
              ,[DeathIndicatorId] = @DeathIndicatorId 
              ,[DateOfBirth] = @DateOfBirth
              ,[DateOfDeath] = @DateOfDeath
              ,[MedicareNumber] = @MedicareNumber
              ,[MedicareIrn] = @MedicareIrn
              ,[IsMedicareNumberValid] = @IsMedicareNumberValid
              ,[DvaNumber] = @DvaNumber
              ,[StatePatientId] = @StatePatientId
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified
         WHERE [PatientMasterID] = @PatientMasterID
        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
        SELECT pm.[PatientMasterID]
             , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
             , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
             , pmi.Ihi
             , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
             , ihis.Description AS IhiStatus
             , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
             , pmi.[RegisteredFamilyName]
             , pmi.[RegisteredGivenName]
             , irs.Description AS IhiRecordStatus
             , pmi.DateLastValidated AS IhiLastValidated
             , pm.[MedicareNumber]
             , pm.[MedicareIrn]
             , pm.[IsMedicareNumberValid]
             , pm.[DvaNumber]
             , pm.[StatePatientId]
             , pm.[DateOfBirth]
             , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
             , pm.[DateOfDeath]
             , pm.[DeathIndicatorId]
             , pm.[DateCreated]
             , pm.[UserCreated]
             , pm.[DateModified]
             , pm.[UserModified]               
			 , pmi.[HealthProviderOrganisationNetworkId]   
         FROM	[hips].[PatientMaster] AS pm
         LEFT JOIN [hips].[HospitalPatient] AS hp ON pm.PatientMasterId = hp.PatientMasterId
         LEFT JOIN [hips].[Hospital] AS h ON hp.HospitalId = h.HospitalId
         LEFT JOIN [hips].[HealthProviderOrganisation] AS hpo ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
         LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID] AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
         LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
         LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId

        WHERE	pm.[PatientMasterID] = @PatientMasterID
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[PatientsWithoutPcehrList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Create date:   3 Sep 2013
-- Description:   Gets a list of patients without PCEHR's (Advertised or Disclosed)
-- Modified:
--  3 Feb 2014 - Make it get patients without PCEHRs
--  6 Feb 2014 - Avoid duplicate results
-- 13 Feb 2014 - Show only most recent episode for each patient
--  5 Jan 2015 - Added new columns for HealthProviderOrganisationNetworkId
-- =============================================
CREATE PROCEDURE [hips].[PatientsWithoutPcehrList] 
(
	@HospitalCodeSystem varchar(20),
	@HospitalId INT = null
)
AS
BEGIN

DECLARE @HealthProviderOrganisationNetworkId INT
SET @HealthProviderOrganisationNetworkId = (
    SELECT hpo.HealthProviderOrganisationNetworkId 
    FROM hips.HealthProviderOrganisation hpo
    INNER JOIN hips.Hospital h 
                ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
    WHERE h.HospitalId = @HospitalId
)

-- Calculate cutoff to include currently admitted or discharged in the last 24 hours
DECLARE @DischargeDateCutoff DATETIME
SET @DischargeDateCutoff = DATEADD(d, -1, GETDATE())

-- Temporary table to store ID of the most recent matching episode for each
-- patient who matches the conditions in the parameters to this stored procedure
CREATE TABLE #MatchingEpisodes (
	EpisodeId INT NOT NULL
)

-- In case there are multiple episodes with the same admission date, pick the
-- most recently created episode.
INSERT	#MatchingEpisodes
SELECT	MAX(e.EpisodeId)
FROM hips.Episode e
 INNER JOIN (
	-- Determine the admission date of the most recent episode for each patient who
	-- has a current episode in the required hospital and meets the WithPcehr condition.
	SELECT	e.PatientId,
			MAX(e.AdmissionDate) AS MaxAdmit
	FROM	hips.HospitalPatient hp
	 INNER JOIN hips.Episode e
		ON hp.PatientId = e.PatientId
	 LEFT JOIN hips.HealthProviderOrganisationPatient hpop
		ON hp.PatientMasterId = hpop.PatientMasterId
	WHERE (@HospitalId IS NULL OR hp.HospitalId = @HospitalId)
	AND (	(hpop.PcehrAdvertised = 0 AND hpop.PcehrDisclosed = 0)
		OR	(hpop.HealthProviderOrganisationId IS NULL AND hpop.PatientMasterId IS NULL) 
		)
	AND (	e.EpisodeLifecycleId = 11	-- 11 is Admitted, 13 is Discharged
		OR	(e.EpisodeLifecycleId = 13 AND e.DischargeDate > @DischargeDateCutoff)
		)
	GROUP BY e.PatientId
 ) a 
	ON	e.PatientId = a.PatientId
	AND e.AdmissionDate = a.MaxAdmit
WHERE	(	e.EpisodeLifecycleId = 11 
		OR	(e.EpisodeLifecycleId = 13 AND e.DischargeDate > @DischargeDateCutoff)
		)
GROUP BY e.PatientId

SELECT DISTINCT 
        pm.PatientMasterId, 
		pm.CurrentSexId,
		pm.DateOfBirth,
		pm.MedicareNumber,
		pm.MedicareIrn,
		pm.DvaNumber,
		pmn.FamilyName,
		pmn.GivenNames,
		pmn.SuffixId,
		pmn.TitleId,
		pmi.Ihi,
		pmi.IhiStatusId,
		pmi.IhiRecordStatusId,
		pmi.DateLastValidated,
		pmi.RegisteredSexId,
		pmi.RegisteredFamilyName,
		pmi.RegisteredGivenName,
		hp.HospitalId,
		hc.Code AS HospitalCode,
		e.Ward,
		e.Room,
		e.Bed		
FROM #MatchingEpisodes me
 INNER JOIN hips.Episode e
	ON me.EpisodeId = e.EpisodeId
 INNER JOIN hips.HospitalPatient hp
	ON e.PatientId = hp.PatientId
 INNER JOIN hips.Hospital h
	ON hp.HospitalId = h.HospitalId
 INNER JOIN hips.HealthProviderOrganisation hpo
	ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
 INNER JOIN hips.PatientMaster pm
	ON hp.PatientMasterId = pm.PatientMasterId
 INNER JOIN hips.PatientMasterName pmn
	ON pm.PatientMasterId = pmn.PatientMasterId
	AND pmn.NameTypeId = 2			-- Current Name Only
 INNER JOIN hips.PatientMasterIhi pmi
	ON pm.PatientMasterId = pmi.PatientMasterId
	AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
	AND pmi.IhiStatusId = 1			-- Active IHIs only
	AND pmi.IhiRecordStatusId = 1	-- Verified IHIs only
	AND LEN(pmi.Ihi) = 16			-- Correct length IHIs only
 INNER JOIN hips.HospitalCode hc
	ON hp.HospitalId = hc.HospitalId
	AND hc.CodeSystemId = (SELECT CodeSystemId FROM hips.CodeSystem WHERE Code = @HospitalCodeSystem)

END


GO

DROP PROCEDURE [hips].[PcehrAuditInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 19 Oct 2012
-- Description:   Inserts a new record into the PcehrAudit Table
-- Modified:
--  4/2/2014 - Don't return Request/Response to save transfer bandwidth
-- =============================================
CREATE PROCEDURE [hips].[PcehrAuditInsert] 
(
	@PatientMasterId INT,
	@ServiceName VARCHAR(256),
	@ServiceMessage VARCHAR(MAX),
	@Request XML=NULL,
	@Response XML=NULL,
	@Ihi VARCHAR(16),
	@HpiO VARCHAR(16),
	@UserName VARCHAR(256),
	@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON
	
    DECLARE @intErrorCode INT
		,@PcehrAuditId INT

    SET @intErrorCode = 0

    INSERT
     INTO [hips].PcehrAudit
             ( PatientMasterId ,
               ServiceName,
               ServiceMessage,               
               Request ,
               Response ,
               Ihi,
               HpiO,
               UserName,
               DateCreated ,
               UserCreated ,
               DateModified ,
               UserModified
             )
     VALUES  ( @PatientMasterId ,    
               @ServiceName , 
               @ServiceMessage , 
               @Request ,
               @Response ,
               @Ihi ,
               @HpiO ,
               @UserName ,               
               GETDATE() , -- DateCreated - datetime
               @UserModified , -- UserCreated - varchar(256)
               GETDATE() , -- DateModified - datetime
               @UserModified  -- UserModified - varchar(256)
             )
    SELECT @intErrorCode = @@ERROR 
		, @PcehrAuditId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [PcehrAuditId],
		 PatientMasterId ,
               ServiceName,
               ServiceMessage,
               --Request,   -- Don't return request/response to save transfer bandwidth
               --Response,
               Ihi,               
               HpiO ,
               UserName ,
               DateCreated ,
               UserCreated ,
               DateModified ,
               UserModified
         FROM [hips].[PcehrAudit]
         WHERE [PcehrAuditId] = @PcehrAuditId
    END

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PcehrMessageQueueCountPending]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
 
-- Create date:   13 Sept 2013
-- Description:   Counts the Pending PcehrMessageQueues for the specific episode that are before the specified datetime
-- =============================================
CREATE PROCEDURE [hips].[PcehrMessageQueueCountPending] 
(
	    @SourceSystemSetId VARCHAR(128)
	   ,@CreatedDateTime DATETIME
	   ,@PcehrMessageQueueId INT
) AS
BEGIN

	SELECT  
		COUNT(*)
	FROM  hips.PcehrMessageQueue As pmq
	INNER JOIN hips.QueueStatus As qs on qs.QueueStatusId = pmq.QueueStatusId
	INNER JOIN hips.QueueOperation AS qo ON qo.QueueOperationId = pmq.QueueOperationId
		WHERE SourceSystemSetId = @SourceSystemSetId
		AND pmq.QueueStatusId = 1
		AND pmq.PcehrMessageQueueId != @PcehrMessageQueueId
		AND pmq.DateCreated < @CreatedDateTime

END
GO

DROP PROCEDURE [hips].[PcehrMessageQueueDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 6 December 2012
-- Description:   Deletes an existing record from the PcehrMessageQueue Table
-- =============================================
CREATE PROCEDURE [hips].[PcehrMessageQueueDelete]
(
	@PcehrMessageQueueId INT
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @intErrorCode INT, @intRowsAffected INT

SELECT  @intErrorCode = 0
		,@intRowsAffected = 0

DELETE [hips].[PcehrMessageQueue]
WHERE [PcehrMessageQueueId] = @PcehrMessageQueueId

SELECT	 @intErrorCode = @@ERROR
		,@intRowsAffected = @@ROWCOUNT

SELECT	@intRowsAffected AS RowsAffected

RETURN(@intErrorCode)

END

GO

DROP PROCEDURE [hips].[PcehrMessageQueueGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   3 March 2013
-- Description:   Gets a specified record from the PcehrMessageQueue Table
-- Modified:
-- 15/09/2013 added SourceSystemSetId & SourceSystemDocumentId
-- =============================================
CREATE PROCEDURE [hips].[PcehrMessageQueueGet] 
(
	    @EpisodeId INT = NULL
	   ,@PcehrMessageQueueId INT = NULL
) AS
BEGIN
SELECT  pmq.PcehrMessageQueueId,
		pmq.QueueOperationId,
		pmq.QueueStatusId,
		qs.Description as QueueStatusDescription,
		qo.Description as QueueOperationName,
		pmq.EpisodeId,
		pmq.SerialisedObject,
		pmq.SourceSystemSetId,
		pmq.SourceSystemDocumentId,
		pmq.Request,
		pmq.Response,
		pmq.Details,
		pmq.DateCreated,
		pmq.UserCreated,
		pmq.DateModified,
		pmq.UserModified
FROM  hips.PcehrMessageQueue As pmq
INNER JOIN hips.QueueStatus As qs on qs.QueueStatusId = pmq.QueueStatusId
INNER JOIN hips.QueueOperation AS qo ON qo.QueueOperationId = pmq.QueueOperationId
WHERE (EpisodeId = @EpisodeId OR @EpisodeId IS NULL)
AND (PcehrMessageQueueId = @PcehrMessageQueueId OR @PcehrMessageQueueId IS NULL)

END
GO

DROP PROCEDURE [hips].[PcehrMessageQueueInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 6 December 2012
-- Description:   Inserts a new record into the PcehrMessageQueue Table
-- Modified:
-- 15/09/2013 Added SourceSystemSetId & SourceSystemDocumentId
-- =============================================
CREATE PROCEDURE [hips].[PcehrMessageQueueInsert] 
(
	@QueueOperationId int,
	@QueueStatusId    int,
	@EpisodeId	int,
	@SourceSystemSetId varchar(128),
	@SourceSystemDocumentId varchar(128) = NULL,
	@SerialisedObject varbinary(max),
	@Request          xml = NULL,
	@Response         xml = NULL,
	@Details          varchar(max) = NULL,
	@UserModified     varchar(256)
) 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @PcehrMessageQueueId int

    SET @intErrorCode = 0


	INSERT INTO hips.PcehrMessageQueue
	(
		 [QueueOperationId]
		,[QueueStatusId]
		,[EpisodeId]
		,[SourceSystemSetId]
		,[SourceSystemDocumentId]
		,[SerialisedObject]
		,[Request]
		,[Response]
		,[Details]
		,[DateCreated]
        ,[UserCreated]
        ,[DateModified]
        ,[UserModified]
	)
VALUES
	(
		 @QueueOperationId
		,@QueueStatusId   
		,@EpisodeId
		,@SourceSystemSetId
		,@SourceSystemDocumentId
		,@SerialisedObject
		,@Request         
		,@Response        
		,@Details         
		,GETDATE()
		,@UserModified
		,GETDATE()
		,@UserModified
	)
	
SELECT @intErrorCode = @@ERROR , @PcehrMessageQueueId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [PcehrMessageQueueId]
           ,pmq.[QueueOperationId]
		   ,qo.[Description] AS [QueueOperationName]
           ,pmq.[QueueStatusId]
		   ,qs.[Description] AS [QueueStatusDescription]
		   ,pmq.EpisodeId
		   ,pmq.SourceSystemSetId
		   ,pmq.SourceSystemDocumentId
           ,[SerialisedObject]
           ,[Request]
           ,[Response]
           ,[Details]
           ,pmq.[DateCreated]
           ,pmq.[UserCreated]
           ,pmq.[DateModified]
           ,pmq.[UserModified]
         FROM [hips].[PcehrMessageQueue] pmq
		 INNER JOIN [hips].[QueueOperation] qo ON pmq.[QueueOperationId] = qo.[QueueOperationId]
		 INNER JOIN [hips].[QueueStatus] qs ON pmq.[QueueStatusId] = qs.[QueueStatusId]
         WHERE [PcehrMessageQueueId] = @PcehrMessageQueueId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[PcehrMessageQueueOperationList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
 
-- Create date:   13 Sept 2013
-- Description:   Lists the PcehrMessageQueues for the specific Time Period
-- =============================================
CREATE PROCEDURE [hips].[PcehrMessageQueueOperationList] 
(
	   @DateTimeFrom DATETIME
	   ,@DateTimeTo DATETIME
) AS
BEGIN

	SELECT  pmq.PcehrMessageQueueId, 
		pmq.DateCreated QueueItemDateCreated,
		pmq.QueueOperationId,
		qo.Description QueueOperationName,
		pmq.QueueStatusId,
		qs.Description QueueStatusName,
		h.HospitalId FacilityId,
		h.Description FacilityName,
		hp.PatientId,
		pmi.Ihi,
		e.EpisodeId,
		e.AdmissionDate AdmissionDateTime,
		pmq.SourceSystemSetId,
		LEN(CAST(pmq.Request AS NVARCHAR(MAX))) RequestSize,
        LEN(pmq.Request.value('declare namespace s="http://www.w3.org/2003/05/soap-envelope"; 
	        declare namespace x="urn:ihe:iti:xds-b:2007"; 
		    (/s:Envelope/s:Body/x:ProvideAndRegisterDocumentSetRequest/x:Document)[1]', 'VARBINARY(MAX)')) CDAPackageSize,
		pmq.Details,
		pmq.SerialisedObject
	FROM hips.PcehrMessageQueue As pmq WITH (NOLOCK) 
	INNER JOIN hips.QueueStatus As qs WITH (NOLOCK) ON qs.QueueStatusId = pmq.QueueStatusId
	INNER JOIN hips.QueueOperation AS qo WITH (NOLOCK) ON qo.QueueOperationId = pmq.QueueOperationId
	INNER JOIN hips.Episode	AS e WITH (NOLOCK) ON pmq.EpisodeId = e.EpisodeId
	INNER JOIN hips.HospitalPatient AS hp WITH (NOLOCK) ON e.PatientId = hp.PatientId
	INNER JOIN hips.Hospital AS h WITH (NOLOCK) ON hp.HospitalId = h.HospitalId
	INNER JOIN hips.HealthProviderOrganisation AS hpo WITH (NOLOCK) ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
	INNER JOIN hips.PatientMasterIhi AS pmi WITH (NOLOCK) ON hp.PatientMasterId = pmi.PatientMasterId AND hpo.HealthProviderOrganisationNetworkId = pmi.HealthProviderOrganisationNetworkId
	WHERE (pmq.DateCreated >= @DateTimeFrom OR @DateTimeFrom IS NULL)
	AND (pmq.DateCreated <= @DateTimeTo OR @DateTimeTo IS NULL)
	ORDER BY pmq.DateCreated

END
GO

DROP PROCEDURE [hips].[PcehrMessageQueueUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 6 December 2012
-- Description:   Updates an existing record in the PcehrMessageQueue  Table
-- Modified:
-- 29/01/2013 added EpisodeId
-- 15/09/2013 added SourceSystemSetId & SourceSystemDocumentId
-- =============================================
CREATE PROCEDURE [hips].[PcehrMessageQueueUpdate] 
(
    @PcehrMessageQueueId int,
    @EpisodeId           int,
    @SourceSystemSetId varchar(128),
    @SourceSystemDocumentId varchar(128) = NULL,
    @QueueOperationId    int,
    @QueueStatusId       int,
    @SerialisedObject    varbinary(max),
    @Request             xml = NULL,
    @Response            xml = NULL,
    @Details             varchar(max) = NULL,
    @UserModified        varchar(256),
    @DateModified        datetime
)
AS
BEGIN
    SET NOCOUNT ON
 DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (SELECT TOP 1 [DateModified]
                FROM [hips].[PcehrMessageQueue]
                WHERE [PcehrMessageQueueId] = @PcehrMessageQueueId AND [DateModified] = @DateModified)
    BEGIN
        DECLARE @DateModifiedText NVARCHAR(30)
        SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
        RAISERROR (50001, -1, -1, N'PcehrMessageQueue', N'PcehrMessageQueueId', @PcehrMessageQueueId, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
 IF @intErrorCode = 0
    BEGIN
        UPDATE [hips].[PcehrMessageQueue]
           SET	 [EpisodeId] = @EpisodeId
                ,[QueueOperationId] = @QueueOperationId
                ,[QueueStatusId] = @QueueStatusId
                ,[SerialisedObject] = @SerialisedObject
                ,[Request] = @Request
                ,[Response] = @Response
                ,[Details] = @Details
                ,[DateModified] = GetDate()
                ,[UserModified] = @UserModified
         WHERE [PcehrMessageQueueId] = @PcehrMessageQueueId
        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
        SELECT [PcehrMessageQueueId]
           ,pmq.[EpisodeId]
           ,pmq.[QueueOperationId]
           ,qo.[Description] AS [QueueOperationName]
           ,pmq.[QueueStatusId]
           ,qs.[Description] AS [QueueStatusDescription]
           ,[SerialisedObject]
           ,[Request]
           ,[Response]
           ,[Details]
           ,pmq.[DateCreated]
           ,pmq.[UserCreated]
           ,pmq.[DateModified]
           ,pmq.[UserModified]
         FROM [hips].[PcehrMessageQueue] pmq
         INNER JOIN [hips].[QueueOperation] qo ON pmq.[QueueOperationId] = qo.[QueueOperationId]
         INNER JOIN [hips].[QueueStatus] qs ON pmq.[QueueStatusId] = qs.[QueueStatusId]
         WHERE [PcehrMessageQueueId] = @PcehrMessageQueueId
    END

    RETURN(@intErrorCode)

END
GO

DROP PROCEDURE [hips].[PendingIhiPcehrLookupDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   30 October 2014
-- Description:   Deletes an existing record from the PendingIhiPcehrLookup table
-- =============================================
CREATE PROCEDURE [hips].[PendingIhiPcehrLookupDelete] 
(
	   @HealthProviderOrganisationId INT
	 , @PatientMasterId INT
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int,
			@intRowsAffected int

    SELECT  @intErrorCode = 0,
			@intRowsAffected = 0

    DELETE
     FROM [hips].[PendingIhiPcehrLookup]
     WHERE [HealthProviderOrganisationId] = @HealthProviderOrganisationId
	 AND [PatientMasterId] = @PatientMasterId 

    SELECT @intErrorCode = @@ERROR,
   		   @intRowsAffected = @@ROWCOUNT

	SELECT @intRowsAffected AS RowsAffected

    RETURN(@intErrorCode)
END
GO

DROP PROCEDURE [hips].[PendingIhiPcehrLookupGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Description:   Gets all records or specific records from the PendingIhiPcehrLookup Table
-- Create date:   30 October 2014
-- =============================================
CREATE PROCEDURE [hips].[PendingIhiPcehrLookupGet] 
(
	  @HealthProviderOrganisationId INT = NULL
	, @PatientMasterId INT = NULL
)
AS
BEGIN
	SELECT    pipl.[HealthProviderOrganisationId]
			, pipl.[PatientMasterId]
			, pipl.[DateCreated]
			, pipl.[UserCreated]
			, pipl.[DateModified]
			, pipl.[UserModified]		
FROM  hips.PendingIhiPcehrLookup pipl
WHERE (pipl.PatientMasterId = @PatientMasterId OR @PatientMasterId IS NULL)
AND (pipl.HealthProviderOrganisationId = @HealthProviderOrganisationId OR @HealthProviderOrganisationId IS NULL)
END
GO

DROP PROCEDURE [hips].[PendingIhiPcehrLookupSave]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Description:   Saves a record in the PendingIhiPcehrLookup table.
-- Create date:   30 October 2014
-- =============================================
CREATE PROCEDURE [hips].[PendingIhiPcehrLookupSave] 
(
	  @HealthProviderOrganisationId INT
	, @PatientMasterId INT
	, @DateModified DATETIME
	, @UserModified varchar(256)
)
AS
BEGIN
	IF EXISTS 
	(
		SELECT 1 FROM hips.PendingIhiPcehrLookup
		WHERE HealthProviderOrganisationId = @HealthProviderOrganisationId
		AND PatientMasterId = @PatientMasterId
	)
	BEGIN
		UPDATE hips.PendingIhiPcehrLookup
		SET	   UserModified = @UserModified 
		     , DateModified = GETDATE()		
			 WHERE HealthProviderOrganisationId = @HealthProviderOrganisationId
			AND PatientMasterId = @PatientMasterId   
	END
	ELSE
	BEGIN
    INSERT hips.PendingIhiPcehrLookup
		(     [HealthProviderOrganisationId]
			, [PatientMasterId]
			, [DateCreated]
			, [UserCreated]
			, [DateModified]
			, [UserModified]
		)
	VALUES
		(
			  @HealthProviderOrganisationId
			, @PatientMasterId
            , GETDATE() -- DateCreated - datetime
            , @UserModified  -- UserCreated - varchar(256)
            , GETDATE()  -- DateModified - datetime
            , @UserModified  -- UserModified - varchar(256)
		)
	END
END
GO

DROP PROCEDURE [hips].[PostalDeliveryTypeCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the PostalDeliveryType Code Table
-- =============================================
CREATE PROCEDURE [hips].[PostalDeliveryTypeCodeGet] 
(
	@CodeSystemCode VARCHAR(20),
	@PostalDeliveryTypeId INT = NULL
)
AS
BEGIN

	SELECT  pdt.PostalDeliveryTypeId, pdt.Code, pdt.Description
	FROM    hips.PostalDeliveryTypeCode pdtc
			JOIN hips.PostalDeliveryType pdt ON pdtc.PostalDeliveryTypeId = pdt.PostalDeliveryTypeId
			JOIN hips.CodeSystem cs ON pdtc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND (pdt.PostalDeliveryTypeId = @PostalDeliveryTypeId OR @PostalDeliveryTypeId IS NULL)
	ORDER BY pdt.Description

END
GO

DROP PROCEDURE [hips].[RemoveAuditInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:   6 May 2013
-- Description:   Inserts a new record into the Remove Audit Table
-- =============================================
CREATE PROCEDURE [hips].[RemoveAuditInsert] 
(
	  @ClinicalDocumentId INT
	, @RemovalReasonId INT
	, @AuditInformation VARBINARY(MAX) = NULL
	, @UserModified varchar(256)
) 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intErrorCode int
          , @RemoveAuditId int

    SET @intErrorCode = 0


	INSERT INTO hips.RemoveAudit
	(
		[ClinicalDocumentId],
		[RemovalReasonId],
		[AuditInformation],
		[DateCreated],
        [UserCreated],
        [DateModified],
        [UserModified]
		)
VALUES
	(
	  @ClinicalDocumentId
	, @RemovalReasonId
	, @AuditInformation
	, GETDATE()
    , @UserModified
    , GETDATE()
    , @UserModified
	)
	
	 SELECT @intErrorCode = @@ERROR , @RemoveAuditId = SCOPE_IDENTITY()
    IF @intErrorCode = 0
    BEGIN
        SELECT [RemoveAuditId],
                ClinicalDocumentId,
                ra.RemovalReasonId,
				rr.Description AS RemovalReasonDescription,
				AuditInformation,
                ra.[DateCreated],
                ra.[UserCreated],
                ra.[DateModified],
                ra.[UserModified]
         FROM [hips].[RemoveAudit] ra
		 INNER JOIN [hips].[RemovalReason] rr ON ra.RemovalReasonId = rr.RemovalReasonId
         WHERE [RemoveAuditId] = @RemoveAuditId
    END

    RETURN(@intErrorCode)

END

GO

DROP PROCEDURE [hips].[SexCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the Sex Code Table
-- Modified:
-- 12/12/2013 - Add code override functionality
-- =============================================
CREATE PROCEDURE [hips].[SexCodeGet] 
(
	@CodeSystemCode VARCHAR(20),
	@SexId INT = NULL
)
AS
BEGIN

	SELECT  sx.SexId, COALESCE(sc.CodeOverride, sx.Code) AS Code, sx.Description
	FROM    hips.SexCode sc
			JOIN hips.Sex sx ON sc.SexId = sx.SexId
			JOIN hips.CodeSystem cs ON sc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND (sx.SexId = @SexId OR @SexId IS NULL)
	ORDER BY sx.Description

END
GO

DROP PROCEDURE [hips].[SexGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 30 March 2012
-- Description:   Gets a specific record from the Sex Table
-- =============================================
CREATE PROCEDURE [hips].[SexGet] 
(
	@SexId INT = NULL
)
AS
BEGIN
SELECT  s.SexId,
		s.Code,
		s.Description,
		s.DateCreated,
		s.UserCreated,
		s.DateModified,
		s.UserModified
		
FROM  hips.Sex s
WHERE s.SexId = @SexId OR @SexId IS NULL
END
GO

DROP PROCEDURE [hips].[SinglePatientInHospitalGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   17 April 2014
-- Description:   Gets a single patient and their details 
-- =============================================
CREATE PROCEDURE [hips].[SinglePatientInHospitalGet] 
 (
      @HospitalCodeSystem VARCHAR(20),
      @PatientId INT,
      @HealthProviderOrganisationNetworkId INT      
  )
AS 
    BEGIN
		
        SELECT  e.EpisodeId ,
				hp.Mrn ,
				pm.CurrentSexId,
				pm.DateOfBirth,
				pm.MedicareNumber,
				pm.MedicareIrn,
				pm.DvaNumber,
				pmn.FamilyName,
				pmn.GivenNames,
				pmnt.Description AS Title,
				pmns.Description AS Suffix,
				pmi.Ihi,
				pmi.IhiStatusId,
				pmi.IhiRecordStatusId,
				pmi.DateLastValidated AS IhiLastValidated,
				pmi.RegisteredSexId,
				pmi.RegisteredFamilyName,
				pmi.RegisteredGivenName,
				hc.Code as HospitalCode,
				h.HospitalId,
				h.Name as HospitalName,
				hpop.PcehrAdvertised,
				hpop.PcehrDisclosed,
				e.Ward,
				e.Room,
				e.Bed,
                e.AdmissionDate ,
                e.AdmissionReason , 
                e.DischargeDate ,
                rp.FamilyName AS ResponsibleProviderFamilyName ,
                rp.GivenNames AS ResponsibleProviderGivenNames ,
                rpt.Description AS ResponsibleProviderTitle ,
                rps.Description AS ResponsibleProviderSuffix ,
                e.SourceSystemEpisodeId ,
                e.ConsentWithdrawn,
				(SELECT COUNT(ee.EpisodeId)
					FROM hips.Episode ee
					WHERE ee.PatientId = hp.PatientId) AS EpisodeCount,				
                (SELECT TOP 1 da.AuditInformation 
						FROM hips.DisclosureAudit da 
						WHERE hp.PatientMasterId = da.PatientMasterId 
						AND h.HealthProviderOrganisationId = da.HealthProviderOrganisationId
						ORDER BY DisclosureAuditId DESC) AS DisclosureAuditInformation
        FROM    hips.HospitalPatient hp
               INNER JOIN hips.Episode e ON hp.PatientId = e.PatientId
                INNER JOIN
				(	-- The latest admission date/time for the patient master at the hospital
					SELECT  PatientMasterId, HospitalId, MAX(AdmissionDate) AdmissionDate
					FROM    hips.HospitalPatient hp
					INNER JOIN hips.Episode e ON hp.PatientId = e.PatientId
					WHERE  e.PatientId = @PatientId
					GROUP BY PatientMasterId, HospitalId
				) AS LatestAdmission
						ON hp.PatientMasterId = LatestAdmission.PatientMasterId
						AND e.AdmissionDate = LatestAdmission.AdmissionDate
                INNER JOIN hips.HospitalCode hc	ON hp.HospitalId = hc.HospitalId	
					AND hc.CodeSystemId = (SELECT CodeSystemId FROM hips.CodeSystem WHERE Code = @HospitalCodeSystem)
				INNER JOIN hips.Hospital h ON hp.HospitalId = h.HospitalId
				INNER JOIN hips.HealthProviderOrganisation AS hpo WITH (NOLOCK) ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId				
                INNER JOIN hips.HealthProviderIndividual rp ON e.ResponsibleProviderId = rp.HealthProviderIndividualId
                INNER JOIN hips.Title rpt ON rp.TitleId = rpt.TitleId
                INNER JOIN hips.Suffix rps ON rp.SuffixId = rps.SuffixId
                INNER JOIN hips.PatientMaster pm ON hp.PatientMasterId = pm.PatientMasterId
				INNER JOIN hips.PatientMasterName pmn ON pm.PatientMasterId = pmn.PatientMasterId AND pmn.NameTypeId = 2 -- Current Name
				LEFT JOIN hips.PatientMasterIhi pmi ON pm.PatientMasterId = pmi.PatientMasterId AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
                LEFT JOIN hips.Title pmnt ON pmn.TitleId = pmnt.TitleId
				LEFT JOIN hips.Suffix pmns ON pmn.SuffixId = pmns.SuffixId
				LEFT JOIN hips.HealthProviderOrganisationPatient hpop
					ON h.HealthProviderOrganisationId = hpop.HealthProviderOrganisationId
					AND hp.PatientMasterId = hpop.PatientMasterId
        WHERE   e.PatientId = @PatientId

END
GO

DROP PROCEDURE [hips].[StateCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the State Code Table
-- =============================================
CREATE PROCEDURE [hips].[StateCodeGet] 
(
	@CodeSystemCode VARCHAR(20),
	@StateId INT = NULL
)
AS
BEGIN

	SELECT  st.StateId, st.Code, st.Description
	FROM    hips.StateCode sc
			JOIN hips.State st ON sc.StateId = st.StateId
			JOIN hips.CodeSystem cs ON sc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND (st.StateId = @StateId OR @StateId IS NULL)
	ORDER BY st.Description

END
GO

DROP PROCEDURE [hips].[StateGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 30 March 2012
-- Description:   Gets a specific record from the State Table
-- =============================================
CREATE PROCEDURE [hips].[StateGet] 
(
	@StateId INT = null
)
AS
BEGIN
SELECT  s.StateId,
		s.Code,
		s.Description,
		s.DateCreated,
		s.UserCreated,
		s.DateModified,
		s.UserModified
		
FROM  hips.State s
WHERE s.StateId = @StateId OR @StateId IS null
END
GO

DROP PROCEDURE [hips].[StreetSuffixTypeCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the StreetSuffixType Code Table
-- =============================================
CREATE PROCEDURE [hips].[StreetSuffixTypeCodeGet] 
(
	@CodeSystemCode VARCHAR,
	@StreetSuffixTypeId INT = NULL
)
AS
BEGIN

	SELECT  sst.StreetSuffixTypeId, sst.Code, sst.Description
	FROM    hips.StreetSuffixTypeCode sstc
			JOIN hips.StreetSuffixType sst ON sstc.StreetSuffixTypeId = sst.StreetSuffixTypeId
			JOIN hips.CodeSystem cs ON sstc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND sst.StreetSuffixTypeId = @StreetSuffixTypeId OR @StreetSuffixTypeId IS NULL
	ORDER BY sst.Description

END
GO

DROP PROCEDURE [hips].[StreetTypeCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the StreetType Code Table
-- =============================================
CREATE PROCEDURE [hips].[StreetTypeCodeGet] 
(
	@CodeSystemCode VARCHAR(20),
	@StreetTypeId INT = NULL
)
AS
BEGIN

	SELECT  st.StreetTypeId, st.Code, st.Description
	FROM    hips.StreetTypeCode ssc
			JOIN hips.StreetType st ON ssc.StreetTypeId = st.StreetTypeId
			JOIN hips.CodeSystem cs ON ssc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND (st.StreetTypeId = @StreetTypeId OR @StreetTypeId IS NULL)
	ORDER BY st.Description

END
GO

DROP PROCEDURE [hips].[SuffixGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 30 March 2012
-- Description:   Gets a specific record from the Suffix Table
-- =============================================
CREATE PROCEDURE [hips].[SuffixGet] 
(
	@SuffixId INT = NULL
)
AS
BEGIN
SELECT  s.SuffixId,
		s.Code,
		s.Description,
		s.DateCreated,
		s.UserCreated,
		s.DateModified,
		s.UserModified
		
FROM  hips.Suffix s
WHERE s.SuffixId = @SuffixId OR @SuffixId IS null
END
GO

DROP PROCEDURE [hips].[SuffixInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 30 October 2012
-- Description:   Inserts a new record into the Suffix Table
-- =============================================

CREATE PROCEDURE [hips].[SuffixInsert] 
(
	@Code VARCHAR(10),
	@Description VARCHAR(10),
	@UserModified VARCHAR(256)
) 
AS
BEGIN
	SET NOCOUNT ON

   DECLARE @intErrorCode INT
          , @SuffixId INT
    SELECT   @SuffixId = SuffixId 
    FROM [hips].Suffix WHERE Code = @Code      
    SET @intErrorCode = 0
    IF (@SuffixId IS NULL)
    BEGIN
		INSERT INTO [hips].[Suffix]
				( Code,
				  Description,
				  DateCreated,
				  UserCreated,
				  DateModified,
				  UserModified
				)
		VALUES  ( @Code,
				  @Description,
				  GETDATE(), 
				  @UserModified, 
				  GETDATE(), 
				  @UserModified  
				)
		        
		 SELECT @intErrorCode = @@ERROR , @SuffixId = SCOPE_IDENTITY()
	END
    IF @intErrorCode = 0
    BEGIN
		SELECT t.SuffixId
			,t.Code
			,t.Description
		    ,t.DateCreated
			,t.UserCreated
			,t.DateModified
			,t.UserModified
		FROM hips.Suffix t
		WHERE @SuffixId = t.SuffixId
    END

    RETURN(@intErrorCode)

	

END
GO

DROP PROCEDURE [hips].[SystemErrorLogInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 15 October 2012
-- Description:   Inserts a new record into the System Error Log Table
-- Modified: 24/07/2013 - Added LogMessageId column
-- =============================================
CREATE PROCEDURE [hips].[SystemErrorLogInsert] 
(
      @LogMessageId INT
	, @Message VARCHAR(200)
	, @ExceptionMessage VARCHAR(500) = NULL
	, @UserModified varchar(256)
) 
AS
BEGIN
    SET NOCOUNT ON
	INSERT INTO hips.SystemErrorLog
	(
		  [LogMessageId]
		, [Message]
		, [ExceptionMessage]
        , [DateCreated]
        , [UserCreated]
        , [DateModified]
        , [UserModified]
	)
VALUES
	(
		  @LogMessageId
		, @Message
		, @ExceptionMessage
        , GETDATE()
        , @UserModified
        , GETDATE()
        , @UserModified
	)

END
GO

DROP PROCEDURE [hips].[TitleGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 30 March 2012
-- Description:   Gets a specific record from the Title Table
-- =============================================
CREATE PROCEDURE [hips].[TitleGet] 
(
	@TitleId INT = NULL
)
AS
BEGIN
SELECT  t.TitleId,
		t.Code,
		t.Description,
		t.DateCreated,
		t.UserCreated,
		t.DateModified,
		t.UserModified
		
FROM  hips.Title t
WHERE t.TitleId = @TitleId OR @TitleId IS NULL
END
GO

DROP PROCEDURE [hips].[TitleInsert]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 30 October 2012
-- Description:   Inserts a new record into the Title Table
-- =============================================

CREATE PROCEDURE [hips].[TitleInsert] 
(
	@Code VARCHAR(10),
	@Description VARCHAR(10),
	@UserModified VARCHAR(256)
) 
AS
BEGIN
	SET NOCOUNT ON

   DECLARE @intErrorCode INT
          , @TitleId INT
    SELECT   @TitleId = TitleId 
    FROM [hips].Title WHERE Code = @Code      
    SET @intErrorCode = 0
    IF (@TitleId IS NULL)
    BEGIN
		INSERT INTO [hips].[Title]
				( Code,
				  Description,
				  DateCreated,
				  UserCreated,
				  DateModified,
				  UserModified
				)
		VALUES  ( @Code,
				  @Description,
				  GETDATE(), 
				  @UserModified, 
				  GETDATE(), 
				  @UserModified  
				)
		        
		 SELECT @intErrorCode = @@ERROR , @TitleId = SCOPE_IDENTITY()
	END
    IF @intErrorCode = 0
    BEGIN
		SELECT t.TitleId
			,t.Code
			,t.Description
		    ,t.DateCreated
			,t.UserCreated
			,t.DateModified
			,t.UserModified
		FROM hips.Title t
		WHERE @TitleId = t.TitleId
    END

    RETURN(@intErrorCode)

	

END
GO

DROP PROCEDURE [hips].[UnitTypeCodeGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date:	  13 Nov 2013	
-- Description:   Gets a list or specific record from the linkage of the UnitType Code Table
-- =============================================
CREATE PROCEDURE [hips].[UnitTypeCodeGet] 
(
	@CodeSystemCode VARCHAR(20),
	@UnitTypeId INT = NULL
)
AS
BEGIN

	SELECT  ut.UnitTypeId, ut.Code, ut.Description
	FROM    hips.UnitTypeCode utc
			JOIN hips.UnitType ut ON utc.UnitTypeId = ut.UnitTypeId
			JOIN hips.CodeSystem cs ON utc.CodeSystemId = cs.CodeSystemId
			WHERE cs.Code = @CodeSystemCode
			AND (ut.UnitTypeId = @UnitTypeId OR @UnitTypeId IS NULL)
	ORDER BY ut.Description

END
GO

DROP PROCEDURE [hips].[VisitorHospitalGenerate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   18 October 2013
-- Description:   Generates or updates visitor hospitals based 
--				  on the Health Provider organisation data
-- =============================================
CREATE PROCEDURE [hips].[VisitorHospitalGenerate]
    (
      @UserModified VARCHAR(256)
    )
AS 
    BEGIN
        SET NOCOUNT ON

	-- Update the name of any exisiting Visitor Hospitals
        UPDATE  [hips].[Hospital]
        SET     [Description] = hpo.[Name] + '_Visitor' ,
                [Name] = hpo.[Name] + '_Visitor' ,
                [DateModified] = GETDATE() ,
                [UserModified] = @UserModified
        FROM    [hips].[HealthProviderOrganisation] hpo
                INNER JOIN [hips].[Hospital] h ON h.HospitalId = hpo.VisitorHospitalId

	-- Insert new Visitor Hospital records into the Hospital table  
        INSERT  [hips].[Hospital]
                ( [Description] ,
                  [Name] ,
                  [HealthProviderOrganisationId] ,
                  [UploadDocumentMinimumAge],
                  [DateCreated] ,
                  [UserCreated] ,
                  [DateModified] ,
                  [UserModified]
		        )
                SELECT  [Name] + '_Visitor' AS [Description] ,
                        [Name] + '_Visitor' AS [Name] ,
                        [HealthProviderOrganisationId] ,
                        0,
                        GETDATE() ,
                        @UserModified ,
                        GETDATE() ,
                        @UserModified
                FROM    [hips].[HealthProviderOrganisation]
                WHERE   VisitorHospitalId IS NULL 

	-- Update the Health Provider Organisation to link to the new Visitor Hospital records
        UPDATE  [hips].[HealthProviderOrganisation]
        SET     VisitorHospitalId = h.HospitalId ,
                [DateModified] = GETDATE() ,
                [UserModified] = @UserModified
        FROM    [hips].[HealthProviderOrganisation] hpo
                INNER JOIN [hips].[Hospital] h ON h.Name = hpo.Name
                                                  + '_Visitor'
        WHERE   VisitorHospitalId IS NULL 

    END
GO

/****** Object:  StoredProcedure [puma].[PatientMasterGet]    Script Date: 06/20/2013 10:12:00 ******/
DROP PROCEDURE [puma].[PatientMasterGet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date:   23 January 2013
-- Description:   Gets one or more records from the Patient Master table,
--                applying overrides from puma.PatientMasterOverride
--                if a record is present in the latter.
-- Lookup Types Supported:
--    1. PatientMasterId (e.g. 1234567)
--    2. StatePatientId (e.g. '100000090751')
--    3. Ihi (e.g. '8003604567901177')
--    4. HospitalId and Mrn  (e.g. 71, '01234567')
--    5. HospitalCode and CodeSystemId and Mrn ('FMC', 2, '01234567')
--    6. Demographic search using all or some of:
--         CurrentLastName, CurrentFirstNames, DateOfBirth, CurrentSexId, DvaNumber, MedicareNumber and MedicareIrn
--       For demographic search
-- =============================================
CREATE PROCEDURE [puma].[PatientMasterGet] 
(
	@PatientMasterId INT = NULL,
	@StatePatientId VARCHAR(64) = NULL,
	@Ihi VARCHAR(16) = NULL,
	@HospitalId INT = NULL,
	@HospitalCode VARCHAR(64) = NULL,
	@CodeSystemId INT = NULL,
	@Mrn VARCHAR(20) = NULL,
	@CurrentLastName VARCHAR(80) = NULL,
	@CurrentFirstNames VARCHAR(80) = NULL,
	@DateOfBirth DATETIME = NULL,
	@CurrentSexId INT = NULL,
	@MedicareNumber VARCHAR(12) = NULL,
	@MedicareIrn VARCHAR(3) = NULL,
	@DvaNumber VARCHAR(18) = NULL,
	@HealthProviderOrganisationNetworkId INT = NULL
)
AS
BEGIN
	IF @HospitalId IS NULL AND @CodeSystemId IS NOT NULL AND @HospitalCode IS NOT NULL 
	BEGIN
	  SELECT @HospitalId = [HospitalId]
	  FROM [hips].[HospitalCode]
	  WHERE [CodeSystemId] = @CodeSystemId
	  AND [Code] = @HospitalCode
	END


	IF @PatientMasterId IS NULL
	BEGIN
		IF @HospitalId IS NOT NULL AND @Mrn IS NOT NULL
		BEGIN
			SELECT	@PatientMasterId = [PatientMasterId]
			FROM	[hips].[HospitalPatient]
			WHERE	[HospitalId] = @HospitalId
			AND		[Mrn] = @Mrn
		END
		ELSE IF @Ihi IS NOT NULL
		BEGIN
			SELECT	@PatientMasterId = [PatientMasterId]
			FROM	[hips].[PatientMasterIhi]
			WHERE	[Ihi] = @Ihi
		END
		ELSE IF @StatePatientId IS NOT NULL
		BEGIN
			SELECT	@PatientMasterId = [PatientMasterId]
			FROM	[hips].[PatientMaster]
			WHERE	[StatePatientId] = @StatePatientId
		END				
	END	
	
	IF @HealthProviderOrganisationNetworkId IS NULL AND @HospitalId IS NULL AND @HospitalCode IS NULL
	BEGIN
		RAISERROR(50003, -1, -1) 
	END
	
	IF @HealthProviderOrganisationNetworkId IS NULL AND @HospitalId IS NOT NULL
	BEGIN
		SELECT @HealthProviderOrganisationNetworkId = hpo.HealthProviderOrganisationNetworkId
		FROM [hips].HealthProviderOrganisation hpo
		INNER JOIN [hips].[Hospital] h ON hpo.HealthProviderOrganisationId = h.HealthProviderOrganisationId
		WHERE h.HospitalId = @HospitalId
	END
	
	IF @HealthProviderOrganisationNetworkId IS NULL AND @HospitalId IS NULL AND @CodeSystemId IS NOT NULL AND @HospitalCode IS NOT NULL 
	BEGIN
		SELECT @HealthProviderOrganisationNetworkId = hpo.HealthProviderOrganisationNetworkId
		FROM [hips].HealthProviderOrganisation hpo
		INNER JOIN [hips].[Hospital] h ON hpo.HealthProviderOrganisationId = h.HealthProviderOrganisationId
		INNER JOIN [hips].[HospitalCode] hc ON h.HospitalId = hc.HospitalId
		WHERE hc.CodeSystemId = @CodeSystemId AND hc.Code = @HospitalCode
	END

	IF  @PatientMasterId IS NULL
	AND @Mrn IS NULL
	AND @StatePatientId IS NULL
	AND @Ihi IS NULL
	AND (  @CurrentFirstNames IS NOT NULL 
		OR @CurrentLastName IS NOT NULL
		OR @DateOfBirth IS NOT NULL
		OR @MedicareNumber IS NOT NULL
		OR @DvaNumber IS NOT NULL)
	BEGIN
		-- Demographic Search
		SELECT pm.[PatientMasterId]
			 , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
			 , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
			 , pmi.Ihi
			 , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
			 , ihis.Description AS IhiStatus
			 , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
			 , pmi.[RegisteredFamilyName]
			 , pmi.[RegisteredGivenName]
			 , irs.Description AS IhiRecordStatus
			 , pmi.DateLastValidated AS IhiLastValidated
			 , COALESCE (pmo.[MedicareNumber], pm.[MedicareNumber]) AS MedicareNumber
			 , COALESCE (pmo.[MedicareIrn], pm.[MedicareIrn]) AS MedicareIrn
			 , COALESCE (pmo.[IsMedicareNumberValid], pm.[IsMedicareNumberValid]) AS IsMedicareNumberValid
			 , COALESCE (pmo.[DvaNumber], pm.[DvaNumber]) AS DvaNumber
			 , pm.[StatePatientId]
			 , COALESCE (pmo.[DateOfBirth], pm.[DateOfBirth]) AS DateOfBirth
			 , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
			 , pmi.[HealthProviderOrganisationNetworkId]
			 , pm.[DateOfDeath]
			 , pm.[DateCreated]
			 , pm.[UserCreated]
			 , pm.[DateModified]
			 , pm.[UserModified]
        FROM	[hips].[PatientMaster] AS pm
		LEFT JOIN [hips].HospitalPatient AS hp ON pm.PatientMasterID = hp.PatientMasterID
		LEFT JOIN [hips].[Hospital] AS h ON h.HospitalID = hp.HospitalID
		LEFT JOIN hips.HealthProviderOrganisation AS hpo
			ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId	
        LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID]
			AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
		LEFT JOIN [hips].PatientMasterName pmn ON pmn.PatientMasterId = pm.PatientMasterId AND pmn.NameTypeId = 2
		LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
		LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId
		LEFT JOIN [puma].PatientMasterOverride AS pmo ON pmo.PatientMasterId = pm.PatientMasterId
		
        WHERE	(@CurrentLastName IS NULL OR pmn.[FamilyName] = @CurrentLastName)
		AND		(@CurrentFirstNames IS NULL OR pmn.[GivenNames] = @CurrentFirstNames)
		AND		(@DateOfBirth IS NULL OR pm.[DateOfBirth] = @DateOfBirth)
		AND		(@CurrentSexId IS NULL OR pm.[CurrentSexId] = @CurrentSexId)
		AND		(@DvaNumber IS NULL OR pm.[DvaNumber] = @DvaNumber)
		-- Do not match a patient master where the query and the record both have one of the special values that mean unknown or ineligible.
		AND		(@MedicareNumber IS NULL OR pm.[MedicareNumber] = @MedicareNumber AND @MedicareNumber <> '0000000000' AND @MedicareNumber <> '0000000009')

		-- Do match even if the record has a null sequence number
		AND		(@MedicareIrn IS NULL OR pm.[MedicareIrn] IS NULL OR pm.[MedicareIrn] = @MedicareIrn)
	END
	ELSE  -- @PatientMasterID IS NOT NULL
	BEGIN
		SELECT pm.[PatientMasterID]
			 , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
			 , sex.Description as RegisteredSex
			 , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
			 , pmi.Ihi
			 , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
			 , ihis.Description AS IhiStatus
			 , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
			 , irs.Description AS IhiRecordStatus
			 , pmi.[RegisteredFamilyName]
			 , pmi.[RegisteredGivenName]
			 , pmi.DateLastValidated AS IhiLastValidated
			 , COALESCE (pmo.[MedicareNumber], pm.[MedicareNumber]) AS MedicareNumber
			 , COALESCE (pmo.[MedicareIrn], pm.[MedicareIrn]) AS MedicareIrn
			 , COALESCE (pmo.[IsMedicareNumberValid], pm.[IsMedicareNumberValid]) AS IsMedicareNumberValid
			 , COALESCE (pmo.[DvaNumber], pm.[DvaNumber]) AS DvaNumber
			 , pm.[StatePatientId]
			 , COALESCE (pmo.[DateOfBirth], pm.[DateOfBirth]) AS DateOfBirth
			 , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
			 , pmi.[HealthProviderOrganisationNetworkId]
			 , pm.[DateOfDeath]
			 , pm.[DateCreated]
			 , pm.[UserCreated]
			 , pm.[DateModified]
			 , pm.[UserModified]         
		FROM	[hips].[PatientMaster] AS pm
		LEFT JOIN [hips].HospitalPatient AS p ON pm.PatientMasterID = p.PatientMasterID
		LEFT JOIN [hips].[Hospital] AS h ON h.HospitalID = p.HospitalID
		LEFT JOIN hips.HealthProviderOrganisation AS hpo
			ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId	
        LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID]
			AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
		LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
		LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId
		LEFT JOIN [puma].PatientMasterOverride AS pmo ON pmo.PatientMasterId = pm.PatientMasterId
		LEFT JOIN [hips].Sex as sex ON sex.SexId = COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1)
        WHERE	pm.[PatientMasterID] = @PatientMasterID
		AND     (@DateOfBirth IS NULL OR @DateOfBirth = pm.[DateOfBirth])
	END
END
GO

/****** Object:  StoredProcedure [puma].[PatientMasterOverrideDelete]    Script Date: 06/20/2013 10:12:00 ******/
DROP PROCEDURE [puma].[PatientMasterOverrideDelete]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 25/01/2013
-- Description:   Deletes an existing record from the PatientMasterOverride Table
-- =============================================
CREATE PROCEDURE [puma].[PatientMasterOverrideDelete]
(
	@PatientMasterId INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @intErrorCode INT,
@intRowsAffected INT
SELECT  @intErrorCode = 0,
@intRowsAffected = 0
DELETE
 [puma].[PatientMasterOverride]
WHERE [PatientMasterId] = @PatientMasterId
SELECT @intErrorCode = @@ERROR,
@intRowsAffected = @@ROWCOUNT
SELECT @intRowsAffected AS RowsAffected
RETURN(@intErrorCode)
END
GO

/****** Object:  StoredProcedure [puma].[PatientMasterOverrideSave]    Script Date: 06/20/2013 10:12:00 ******/
DROP PROCEDURE [puma].[PatientMasterOverrideSave]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================

-- Create date: 6 Dec 2012
-- Description:   Inserts/Updates a record into the Patient Master Override Table
-- =============================================
CREATE PROCEDURE [puma].[PatientMasterOverrideSave] 
(
	@PatientMasterId INT,
	@DateOfBirth DATETIME = NULL,
	@MedicareNumber VARCHAR(12) = NULL,
	@MedicareIrn VARCHAR(3) = NULL,
	@IsMedicareNumberValid BIT = NULL,
	@DvaNumber VARCHAR(18) = NULL,
	@DateModified DATETIME,
	@UserModified varchar(256)
)
AS
BEGIN
    SET NOCOUNT ON

	DECLARE @DoesExist INT
    DECLARE @intErrorCode INT

    SET @intErrorCode = 0
    -- First see if we have an existing record      
    SELECT Top 1 @DoesExist =  pmo.PatientMasterId
    FROM [puma].PatientMasterOverride pmo
    WHERE pmo.PatientMasterId = @PatientMasterId
    
    -- No it does not. Insert a new one
    IF (@DoesExist IS NULL)
    BEGIN
    	INSERT INTO [puma].PatientMasterOverride
	        ( PatientMasterId,
	          DateOfBirth,
	          MedicareNumber,
	          MedicareIrn,
	          IsMedicareNumberValid,
	          DvaNumber,
	          DateCreated,
	          UserCreated,
	          DateModified,
	          UserModified
	        )
			VALUES  (	@PatientMasterId,
						@DateOfBirth,
						@MedicareNumber,
						@MedicareIrn,
						@IsMedicareNumberValid,
						@DvaNumber,
						GETDATE(),
						@UserModified,
						GETDATE(),
						@UserModified
			
					)
			SELECT @intErrorCode = @@ERROR
    END
    
    -- Yes it does, update the new values
    ELSE BEGIN
			UPDATE [puma].PatientMasterOverride
			SET  DateOfBirth = @DateOfBirth		  
				,MedicareNumber = @MedicareNumber	
				,MedicareIrn = @MedicareIrn	 
				,IsMedicareNumberValid = @IsMedicareNumberValid
				,DvaNumber = @DvaNumber
				,[DateModified] = GetDate()
				,[UserModified] = @UserModified
           WHERE  PatientMasterId = @PatientMasterId
        SELECT @intErrorCode = @@ERROR
    END
        
    IF @intErrorCode = 0
    BEGIN
		SELECT pmo.PatientMasterId,
				pmo.DateOfBirth,
				pmo.MedicareNumber,
				pmo.MedicareIrn,
				pmo.IsMedicareNumberValid,
				pmo.DvaNumber,
				pmo.UserCreated,
				pmo.DateCreated,
				pmo.UserModified,
				pmo.DateModified
				
		FROM [puma].[PatientMasterOverride] AS pmo
    END

   RETURN(@intErrorCode)
END
GO

/****** Object:  StoredProcedure [puma].[PatientMasterUpdate]    Script Date: 06/20/2013 10:12:00 ******/
DROP PROCEDURE [puma].[PatientMasterUpdate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 22 January 2013
-- Description:   Updates an existing record in the Patient Master table and removes overrides where the values match. 
-- =============================================
CREATE PROCEDURE [puma].[PatientMasterUpdate] 
(
    @PatientMasterId INT,
	@CurrentSexId INT,
	@DeathIndicatorId INT = null,
	@DateOfBirth DATETIME,
	@DateOfDeath DATETIME = null,
	@MedicareNumber VARCHAR(12) = null,
	@MedicareIrn VARCHAR(3) = null,
	@IsMedicareNumberValid INT = null,
	@DvaNumber VARCHAR(18) = NULL,	 
	@StatePatientId VARCHAR(64) = NULL, 
	@UserModified VARCHAR(256),
	@DateModified datetime,
	@HealthProviderOrganisationNetworkId INT
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @intErrorCode int

    SET @intErrorCode = 0

    IF NOT EXISTS (
		SELECT TOP 1 [DateModified]
		FROM  [hips].[PatientMaster]
		WHERE [PatientMasterId] = @PatientMasterId
		AND [DateModified] = @DateModified)
    BEGIN
		DECLARE @DateModifiedText NVARCHAR(30)
		SET @DateModifiedText = CAST(@DateModified AS NVARCHAR)
		RAISERROR (50001, -1, -1, N'PatientMaster', N'PatientMasterID', @PatientMasterID, N'DateModified', @DateModifiedText)
        SET @intErrorCode = @@ERROR
    END
	IF @intErrorCode = 0
	BEGIN
		UPDATE [hips].[PatientMaster]
           SET [CurrentSexId] = @CurrentSexId
			  ,[DateOfBirth] = @DateOfBirth
			  ,[DateOfDeath] = @DateOfDeath
			  ,[DeathIndicatorId] = @DeathIndicatorId 
			  ,[MedicareNumber] = @MedicareNumber
			  ,[MedicareIrn] = @MedicareIrn
			  ,[IsMedicareNumberValid] = @IsMedicareNumberValid
			  ,[DvaNumber] = @DvaNumber
			  ,[StatePatientId] = @StatePatientId
             , [DateModified] = GetDate()
             , [UserModified] = @UserModified
		 WHERE [PatientMasterID] = @PatientMasterID
		SELECT @intErrorCode = @@ERROR     
		IF @intErrorCode = 0
		BEGIN
			UPDATE [puma].[PatientMasterOverride]
			   SET [MedicareNumber] = CASE WHEN [MedicareNumber] = @MedicareNumber THEN NULL ELSE [MedicareNumber] END
			      ,[IsMedicareNumberValid] = CASE WHEN [IsMedicareNumberValid] = @IsMedicareNumberValid THEN NULL ELSE [IsMedicareNumberValid] END
			      ,[MedicareIrn] = CASE WHEN [MedicareIrn] = @MedicareIrn THEN NULL ELSE [MedicareIrn] END
			      ,[DvaNumber] = CASE WHEN [DvaNumber] = @DvaNumber THEN NULL ELSE [DvaNumber] END
			      ,[DateOfBirth] = CASE WHEN [DateOfBirth] = @DateOfBirth THEN NULL ELSE [DateOfBirth] END
			 WHERE [PatientMasterId] = @PatientMasterId
			SELECT @intErrorCode = @@ERROR
			IF @intErrorCode = 0
			BEGIN
				DELETE [puma].[PatientMasterOverride]
				 WHERE [PatientMasterId] = @PatientMasterId
				   AND [MedicareNumber] IS NULL
				   AND [IsMedicareNumberValid] IS NULL
				   AND [MedicareIrn] IS NULL
				   AND [DvaNumber] IS NULL
				   AND [DateOfBirth] IS NULL
				SELECT @intErrorCode = @@ERROR
			END
		END
	END
    IF @intErrorCode = 0
    BEGIN
		SELECT pm.[PatientMasterID]
			 , COALESCE (pmi.RegisteredSexId,pm.CurrentSexId, -1) AS RegisteredSexId
			 , COALESCE (pm.CurrentSexId, -1) AS CurrentSexId
			 , pmi.Ihi
			 , COALESCE (pmi.IhiStatusId, -1) AS IhiStatusId
			 , ihis.Description AS IhiStatus
			 , COALESCE (pmi.IhiRecordStatusId, -1) AS IhiRecordStatusId
			 , pmi.[RegisteredFamilyName]
			 , pmi.[RegisteredGivenName]
			 , irs.Description AS IhiRecordStatus
			 , pmi.DateLastValidated AS IhiLastValidated
			 , COALESCE(pmo.MedicareNumber,pm.MedicareNumber) AS MedicareNumber
			 , COALESCE(pmo.IsMedicareNumberValid,pm.IsMedicareNumberValid) AS IsMedicareNumberValid
			 , COALESCE(pmo.MedicareIrn,pm.MedicareIrn) AS MedicareIrn
			 , COALESCE(pmo.DvaNumber,pm.DvaNumber) AS DvaNumber
			 , pm.[StatePatientId]
			 , COALESCE(pmo.DateOfBirth,pm.DateOfBirth) AS DateOfBirth
			 , COALESCE (pmi.RegisteredDateOfBirth, pm.DateOfBirth) AS RegisteredDateOfBirth
			 , pm.[DateOfDeath]
			 , pm.[DateCreated]
			 , pm.[UserCreated]
			 , pm.[DateModified]
			 , pm.[UserModified]         
			 , pmi.[HealthProviderOrganisationNetworkId]
	     FROM	[hips].[PatientMaster] AS pm
		 LEFT JOIN [hips].[HospitalPatient] AS hp ON pm.PatientMasterId = hp.PatientMasterId
	     LEFT JOIN [hips].[Hospital] AS h ON hp.HospitalId = h.HospitalId
	     LEFT JOIN [hips].[HealthProviderOrganisation] AS hpo ON h.HealthProviderOrganisationId = hpo.HealthProviderOrganisationId
         LEFT JOIN [hips].[PatientMasterIhi] AS pmi ON pmi.[PatientMasterID] = pm.[PatientMasterID] AND pmi.HealthProviderOrganisationNetworkId = @HealthProviderOrganisationNetworkId
         LEFT JOIN [hips].IhiRecordStatus AS irs on irs.IhiRecordStatusId = pmi.IhiRecordStatusId
		 LEFT JOIN [hips].IhiStatus AS ihis on ihis.IhiStatusId = pmi.IhiStatusId
		 LEFT JOIN [puma].[PatientMasterOverride] AS pmo ON pmo.[PatientMasterId] = pm.[PatientMasterId]

        WHERE	pm.[PatientMasterID] = @PatientMasterID
    END

    RETURN(@intErrorCode)

END
GO

