IF ((SELECT COUNT(recId) FROM pal_MSOperationType WHERE operationType = 'ERA') = 0)
	INSERT INTO pal_MSOperationType (operationType)
	VALUES ('ERA');


IF NOT EXISTS(SELECT * FROM sys.columns 
		WHERE Name = N'responsePayload'
		AND Object_ID = Object_ID(N'pal_MSFetchResponseLog'))
BEGIN
	ALTER TABLE pal_MSFetchResponseLog
		ADD responsePayload NVARCHAR(MAX) NULL;
END



IF NOT EXISTS(SELECT * FROM sys.columns 
		WHERE Name = N'processDate'
		AND Object_ID = Object_ID(N'pal_MSFetchResponseLog'))
BEGIN
	ALTER TABLE pal_MSFetchResponseLog
		ADD processDate DateTime NULL;
END




IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEra'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEra](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[fetchResponseId] [int] NULL,
	[medicalSchemeName] [nvarchar](50) NULL,
	[medicalSchemeAdministrator] [nvarchar](50) NULL,
	[medicalSchemeRegistrationNumber] [nvarchar](50) NULL,
	[eraReferenceNumber] [nvarchar](50) NULL,
	[eraIssueDate] [datetime] NULL,
	[eraFromPeriod] [datetime] NULL,
	[eraToPeriod] [datetime] NULL,
	[openingBalance] [decimal](18, 2) NULL,
	[closingBalance] [decimal](18, 2) NULL,
	[totalPaymentAmount] [decimal](18, 2) NULL,
	[totalEraClaimedAmount] [decimal](18, 2) NULL,
	[totalEraPaidAmount] [decimal](18, 2) NULL,
	[totalEraJournalAmount] [decimal](18, 2) NULL,
	 CONSTRAINT [PK_pal_MSEra] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
END

IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEraClaimItem'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEraClaimItem](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[eraMemberId] [int] NULL,
	[claimAccountId] [nvarchar](50) NULL,
	[claimResponseResultCode] [nvarchar](50) NULL,
	[itemClaimAmount] [decimal](18, 2) NULL,
	[totalItemPaidAmount] [decimal](18, 2) NULL,
	 CONSTRAINT [PK_pal_MSEraClaimItem] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
END

IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEraClaimItemGeneralComments'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEraClaimItemGeneralComments](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[eraClaimItemId] [int] NULL,
	[generalComments] [nvarchar](1000) NULL,
	 CONSTRAINT [PK_pal_MSEraClaimItemGeneralComments] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
END

IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEraClaimItemResponse'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEraClaimItemResponse](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[eraClaimItemId] [int] NULL,
	[responseCode] [nvarchar](50) NULL,
	[responseDescription] [nvarchar](100) NULL,
	 CONSTRAINT [PK_pal_MSEraClaimItemResponse] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
END

IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEraJournal'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEraJournal](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[eraId] [int] NULL,
	[journalAmount] [decimal](18, 2) NULL,
	[journalDescription] [nvarchar](100) NULL,
	[journalDate] [datetime] NULL,
	 CONSTRAINT [PK_pal_MSEraJournal] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
END

IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEraMember'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEraMember](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[eraId] [int] NULL,
	[memberSurname] [nvarchar](50) NULL,
	[memberFullName] [nvarchar](50) NULL,
	[membershipNo] [nvarchar](50) NULL,
	[memberAccountNo] [nvarchar](50) NULL,
	[dependantCode] [nvarchar](50) NULL,
	[patientSurname] [nvarchar](50) NULL,
	[patientInitials] [nvarchar](50) NULL,
	[patientFullName] [nvarchar](50) NULL,
	[patientDOB] [datetime] NULL,
	[patientIdNumber] [nvarchar](50) NULL,
	[patientAccountNo] [nvarchar](50) NULL,
	[totalClaimedAmount] [decimal](18, 2) NULL,
	[totalItemPaidAmount] [decimal](18, 2) NULL,
	 CONSTRAINT [PK_pal_MSEraMember] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
END

IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEraBankDeposit'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEraBankDeposit](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[eraId] [int] NULL,
	[depositReference] [nvarchar](50) NULL,
	[payorName] [nvarchar](50) NULL,
	[paidFromAccountNumber] [nvarchar](50) NULL,
	[paidFromBranchCode] [nvarchar](50) NULL,
	[paidFromBankName] [nvarchar](50) NULL,
	[paidToAccountNumber] [nvarchar](50) NULL,
	[paidToBranchCode] [nvarchar](50) NULL,
	[paidToBankName] [nvarchar](50) NULL,
	[paymentDate] [datetime] NULL,
	[paymentMethod] [nvarchar](50) NULL,
	[paymentReferenceNumber] [nvarchar](50) NULL,
	[paymentAmount] [decimal](18, 2) NULL,
	[openingBalance] [decimal](18, 2) NULL,
	[closingBalance] [decimal](18, 2) NULL,
	 CONSTRAINT [PK_pal_MSEraBankDeposit] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
END


IF (NOT EXISTS 
	(SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'pal_MSEraClaimItemAccount'))
BEGIN

	CREATE TABLE [dbo].[pal_MSEraClaimItemAccount](
	[recId] [int] IDENTITY(1,1) NOT NULL,
	[eraClaimItemId] [int] NULL,
	[accountId] [int] NULL,
	[originalProcessDate] [datetime] NULL,
	[manualProcessed] [bit] NULL,
	[manualProcessDate] [datetime] NULL,
	[manualProcessUserId] [int] NULL,
	 CONSTRAINT [PK_pal_MSEraClaimItemAccount] PRIMARY KEY CLUSTERED 
	(
		[recId] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

END


IF EXISTS ( SELECT * 
            FROM   sys.views 
            WHERE  name='pal_vAccountUnprocessedEra' and type='v')
BEGIN
    DROP VIEW [dbo].[pal_vAccountUnprocessedEra]
END


IF EXISTS ( SELECT * 
            FROM   sys.views 
            WHERE  name='pal_vRemittanceListing' and type='v')
BEGIN
    DROP VIEW [dbo].[pal_vRemittanceListing]
END


IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[sp_RemittanceListingByDate]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    DROP PROCEDURE [dbo].[sp_RemittanceListingByDate]




IF NOT EXISTS(SELECT * FROM sys.columns 
		WHERE Name = N'manualAllocated'
		AND Object_ID = Object_ID(N'pal_MSEraClaimItemAccount'))
BEGIN
	ALTER TABLE pal_MSEraClaimItemAccount
		ADD manualAllocated BIT NULL;
END




IF NOT EXISTS(SELECT * FROM sys.columns 
		WHERE Name = N'manualAllocateDate'
		AND Object_ID = Object_ID(N'pal_MSEraClaimItemAccount'))
BEGIN
	ALTER TABLE pal_MSEraClaimItemAccount
		ADD manualAllocateDate DATETIME NULL;
END




IF NOT EXISTS(SELECT * FROM sys.columns 
		WHERE Name = N'manualAllocateUserId'
		AND Object_ID = Object_ID(N'pal_MSEraClaimItemAccount'))
BEGIN
	ALTER TABLE pal_MSEraClaimItemAccount
		ADD manualAllocateUserId INT NULL;
END




IF NOT EXISTS(SELECT * FROM sys.columns 
		WHERE Name = N'rejected'
		AND Object_ID = Object_ID(N'pal_MSEraClaimItemAccount'))
BEGIN
	ALTER TABLE pal_MSEraClaimItemAccount
		ADD rejected BIT NULL;
END