GO
PRINT N'Altering [dbo].[pal_Account]...';


GO
ALTER TABLE [dbo].[pal_Account] ALTER COLUMN [procedureDescription] VARCHAR (5000) NULL;


GO
PRINT N'Starting rebuilding table [dbo].[pal_Practice]...';


GO
IF NOT EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'vatRate' AND Object_ID = Object_ID(N'pal_Practice'))
BEGIN

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_pal_Practice] (
    [recId]          INT           IDENTITY (1, 1) NOT NULL,
    [name]           VARCHAR (350) NULL,
    [description]    VARCHAR (350) NULL,
    [qualifications] VARCHAR (100) NULL,
    [postal1]        VARCHAR (350) NULL,
    [postal2]        VARCHAR (350) NULL,
    [postal3]        VARCHAR (350) NULL,
    [postalCode]     VARCHAR (10)  NULL,
    [physical1]      VARCHAR (350) NULL,
    [physical2]      VARCHAR (350) NULL,
    [physical3]      VARCHAR (350) NULL,
    [physicalCode]   VARCHAR (10)  NULL,
    [vatNum]         VARCHAR (50)  NULL,
    [vatRate]        INT           NULL,
    [tel]            VARCHAR (50)  NULL,
    [fax]            VARCHAR (50)  NULL,
    [mobile]         VARCHAR (50)  NULL,
    [email]          VARCHAR (350) NULL,
    [bank]           VARCHAR (50)  NULL,
    [branch]         VARCHAR (50)  NULL,
    [branchCode]     VARCHAR (20)  NULL,
    [accountNo]      VARCHAR (20)  NULL,
    [accountType]    VARCHAR (50)  NULL,
    [reference]      VARCHAR (50)  NULL,
    [practiceNo]     VARCHAR (50)  NULL,
    [HPCSANo]        VARCHAR (50)  NULL,
    [incNum]         VARCHAR (300) NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_pal_Practice1] PRIMARY KEY CLUSTERED ([recId] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[pal_Practice])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_pal_Practice] ON;
        INSERT INTO [dbo].[tmp_ms_xx_pal_Practice] ([recId], [name], [description], [qualifications], [postal1], [postal2], [postal3], [postalCode], [physical1], [physical2], [physical3], [physicalCode], [vatNum], [tel], [fax], [mobile], [email], [bank], [branch], [branchCode], [accountNo], [accountType], [reference], [practiceNo], [HPCSANo], [incNum])
        SELECT   [recId],
                 [name],
                 [description],
                 [qualifications],
                 [postal1],
                 [postal2],
                 [postal3],
                 [postalCode],
                 [physical1],
                 [physical2],
                 [physical3],
                 [physicalCode],
                 [vatNum],
                 [tel],
                 [fax],
                 [mobile],
                 [email],
                 [bank],
                 [branch],
                 [branchCode],
                 [accountNo],
                 [accountType],
                 [reference],
                 [practiceNo],
                 [HPCSANo],
                 [incNum]
        FROM     [dbo].[pal_Practice]
        ORDER BY [recId] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_pal_Practice] OFF;
    END

DROP TABLE [dbo].[pal_Practice];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_pal_Practice]', N'pal_Practice';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_pal_Practice1]', N'PK_pal_Practice', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END
GO

UPDATE	P
	SET	vatRate = 14
--SELECT	*
FROM	pal_Practice P

GO

PRINT N'Creating [dbo].[pal_PaymentType]...';


GO
IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_NAME = 'pal_PaymentType'))
BEGIN
CREATE TABLE [dbo].[pal_PaymentType] (
    [recId]             INT           IDENTITY (1, 1) NOT NULL,
    [paymentCode]       VARCHAR (50)  NULL,
    [paymentType]       VARCHAR (250) NULL,
    [transactionTypeId] INT           NULL,
    [allowAllocations]  BIT           NOT NULL,
    CONSTRAINT [PK_pal_PaymentType] PRIMARY KEY CLUSTERED ([recId] ASC)
);
PRINT N'Creating [dbo].[DF_pal_PaymentType_allowAllocations]...';

ALTER TABLE [dbo].[pal_PaymentType]
    ADD CONSTRAINT [DF_pal_PaymentType_allowAllocations] DEFAULT ((0)) FOR [allowAllocations];
END

TRUNCATE TABLE [dbo].[pal_PaymentType]
GO
SET IDENTITY_INSERT [dbo].[pal_PaymentType] ON 

GO
INSERT [dbo].[pal_PaymentType] ([recId], [paymentCode], [paymentType], [transactionTypeId], [allowAllocations]) VALUES (1, N'CARD', N'Credit Card', 1, 1)
GO
INSERT [dbo].[pal_PaymentType] ([recId], [paymentCode], [paymentType], [transactionTypeId], [allowAllocations]) VALUES (2, N'CASH', N'Cash', 1, 1)
GO
INSERT [dbo].[pal_PaymentType] ([recId], [paymentCode], [paymentType], [transactionTypeId], [allowAllocations]) VALUES (3, N'CHEQUE', N'Cheque', 1, 1)
GO
INSERT [dbo].[pal_PaymentType] ([recId], [paymentCode], [paymentType], [transactionTypeId], [allowAllocations]) VALUES (4, N'EFT', N'Electronic Transfer', 1, 1)
GO
INSERT [dbo].[pal_PaymentType] ([recId], [paymentCode], [paymentType], [transactionTypeId], [allowAllocations]) VALUES (5, N'M-EFT', N'Medical Aid Transfer', 1, 1)
GO
INSERT [dbo].[pal_PaymentType] ([recId], [paymentCode], [paymentType], [transactionTypeId], [allowAllocations]) VALUES (6, N'CRED', N'Credit Note', 2, 1)
GO
INSERT [dbo].[pal_PaymentType] ([recId], [paymentCode], [paymentType], [transactionTypeId], [allowAllocations]) VALUES (7, N'DEB', N'Debit Note', 2, 0)
GO
SET IDENTITY_INSERT [dbo].[pal_PaymentType] OFF
GO


PRINT N'Creating [dbo].[pal_TransactionType]...';


GO
IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_NAME = 'pal_TransactionType'))
BEGIN
CREATE TABLE [dbo].[pal_TransactionType] (
    [recId]           INT          IDENTITY (1, 1) NOT NULL,
    [transactionType] VARCHAR (50) NULL,
    CONSTRAINT [PK_pal_TransactionType] PRIMARY KEY CLUSTERED ([recId] ASC)
);
END

TRUNCATE TABLE [dbo].[pal_TransactionType]
GO
SET IDENTITY_INSERT [dbo].[pal_TransactionType] ON 

GO
INSERT [dbo].[pal_TransactionType] ([recId], [transactionType]) VALUES (1, N'Payment')
GO
INSERT [dbo].[pal_TransactionType] ([recId], [transactionType]) VALUES (2, N'Journal')
GO
SET IDENTITY_INSERT [dbo].[pal_TransactionType] OFF
GO

PRINT N'Creating [dbo].[pal_UserAccess]...';


GO
IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_NAME = 'pal_UserAccess'))
BEGIN
CREATE TABLE [dbo].[pal_UserAccess] (
    [recId]       INT IDENTITY (1, 1) NOT NULL,
    [userId]      INT NOT NULL,
    [allowCredit] BIT NOT NULL,
    [allowTask]   BIT NOT NULL,
    CONSTRAINT [PK_pal_UserAccess] PRIMARY KEY CLUSTERED ([recId] ASC)
);
ALTER TABLE [dbo].[pal_UserAccess]
    ADD CONSTRAINT [DF_pal_UserAccess_allowCredit] DEFAULT ((0)) FOR [allowCredit];

ALTER TABLE [dbo].[pal_UserAccess]
    ADD CONSTRAINT [DF_pal_UserAccess_allowTask] DEFAULT ((0)) FOR [allowTask];

END

TRUNCATE TABLE [dbo].[pal_UserAccess]
GO
SET IDENTITY_INSERT [dbo].[pal_UserAccess] ON 

GO
INSERT [dbo].[pal_UserAccess] ([recId], [userId], [allowCredit], [allowTask]) VALUES (1, 15, 1, 0)
GO
INSERT [dbo].[pal_UserAccess] ([recId], [userId], [allowCredit], [allowTask]) VALUES (2, 2, 1, 0)
GO
INSERT [dbo].[pal_UserAccess] ([recId], [userId], [allowCredit], [allowTask]) VALUES (3, 3, 1, 0)
GO
INSERT [dbo].[pal_UserAccess] ([recId], [userId], [allowCredit], [allowTask]) VALUES (4, 4, 1, 0)
GO
INSERT [dbo].[pal_UserAccess] ([recId], [userId], [allowCredit], [allowTask]) VALUES (5, 17, 1, 0)
GO
INSERT [dbo].[pal_UserAccess] ([recId], [userId], [allowCredit], [allowTask]) VALUES (6, 39, 1, 0)
GO
SET IDENTITY_INSERT [dbo].[pal_UserAccess] OFF
GO


IF EXISTS(select * FROM sys.views where name = 'v_ReceiptListing')
	DROP VIEW [dbo].[v_ReceiptListing]
GO



GO
PRINT N'Altering [dbo].[sp_ReconByFiscalPeriod]...';


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


GO
PRINT N'Altering [dbo].[sp_GetClaimResults]...';


GO

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


GO
PRINT N'Altering [dbo].[sp_GetSurfaceDataAll]...';


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


GO
PRINT N'Altering [dbo].[sp_GetSurfaceItemGridData]...';


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


GO
PRINT N'Refreshing [dbo].[sp_DebtorsAgeReport]...';


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


GO
PRINT N'Refreshing [dbo].[sp_DebtorsAgeReportResult]...';


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


GO
PRINT N'Refreshing [dbo].[sp_DebtorsAssistedReportResult]...';


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


GO
PRINT N'Refreshing [dbo].[sp_GetClaimResultDetails]...';


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


GO
PRINT N'Refreshing [dbo].[sp_GetInvoiceNo]...';


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


GO
PRINT N'Refreshing [dbo].[sp_GetNextSequence]...';


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


GO
PRINT N'Refreshing [dbo].[sp_CreditListingByDate]...';


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


GO
PRINT N'Refreshing [dbo].[sp_DebitListingByDate]...';


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


GO
PRINT N'Refreshing [dbo].[sp_InvoiceListingByDate]...';


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


GO
PRINT N'Refreshing [dbo].[sp_ReceiptListingByDate]...';


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


GO
PRINT N'Refreshing [dbo].[sp_GetSurfaceData]...';


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


GO
PRINT N'Refreshing [dbo].[sp_GetSurfaceItemData]...';


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


GO
PRINT N'Update complete.';


GO
