ALTER TABLE radiology..tbULRequest ADD 
	[EditedByID] [varchar](20) NULL
 GO 
 
 ALTER TABLE radiology..tbULWomensResultOB ADD 
	[isReleased] [bit] NULL
 GO 
 

alter table  [Medicare].[dbo].[tbMedPackage] add CaseType integer
GO

CREATE INDEX [by_CaseType] ON MEDICARE..tbMedPackage([CaseType]) 
GO

alter table  [Medicare].[dbo].[tbMedPackage] add CaseType integer
GO

CREATE INDEX [by_CaseType] ON MEDICARE..tbMedPackage([CaseType]) 
GO

CREATE INDEX [by_Package] ON MEDICARE..tbMedPackage([Package]) 
GO

CREATE INDEX [by_ICDCode] ON MEDICARE..[tbMed_MedicalCaseRates]([ICDCode]) 
GO

CREATE INDEX [by_PackageID] ON MEDICARE..[tbMed_MedicalCaseRates]([PackageID]) 
GO

CREATE INDEX [by_RVSCode] ON MEDICARE..[tbMed_Procedures]([RVSCODE]) 
GO

CREATE INDEX [by_PackageID] ON MEDICARE..[tbMed_Procedures]([PackageID]) 
GO


use build_file
Go
Create Function dbo.SoundsLike(@OriginalText VarChar(8000))
Returns VarChar(8000)
As
Begin

  Declare @Output VarChar(8000)
  Set @Output = ''

  While Not @OriginalText Is NULL
    Begin
      Set @Output = @Output + Soundex(Left(@OriginalText, PatIndex('%[ ]%', @OriginalText + ' ')-1))
      Set @OriginalText = Right(@OriginalText, NullIf(Len(@OriginalText) - PatIndex('%[ ]%', @OriginalText + ' '), -1))
    End

  Return @Output
End
GO


select * from Doctors.dbo.PatientStatus
GO

use Doctors
go


CREATE INDEX [by_StatusID] ON Doctors.dbo.PatientStatus ([StatusID]) 
GO 

if  not exists (SELECT 1 FROM [PatientStatus]) 
begin 
INSERT INTO  Doctors.dbo.PatientStatus (StatusID,StatusDesc)   VALUES('0','UnAttended')
INSERT INTO  Doctors.dbo.PatientStatus (StatusID,StatusDesc)   VALUES('1','Attended')
INSERT INTO  Doctors.dbo.PatientStatus (StatusID,StatusDesc)   VALUES('2','Ongoing')
INSERT INTO  Doctors.dbo.PatientStatus (StatusID,StatusDesc)   VALUES('3','Another')
INSERT INTO  Doctors.dbo.PatientStatus (StatusID,StatusDesc)   VALUES('4','Referred')
INSERT INTO  Doctors.dbo.PatientStatus (StatusID,StatusDesc)   VALUES('5','Failed')
INSERT INTO  Doctors.dbo.PatientStatus (StatusID,StatusDesc)   VALUES('6','APE')
end

        

/****** 
USE [Medicare]
GO
CREATE NONCLUSTERED INDEX [IX_tbMedPackage] ON [dbo].[tbMedPackage] 
(
	[PackageID] ASC,
	[IsAllowSlashing] ASC,
	[Status] ASC,
	[isAllowSecondCR] ASC,
	[isACR] ASC,
	[canMultiple] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
******/

USE [Medicare]
GO
 CREATE NONCLUSTERED INDEX [by_PackageID] ON [dbo].[tbMedPackage] 
(
	[PackageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [by_Status_PackageID] ON [dbo].[tbMedPackage] 
(
	[Status] ASC,
	[PackageID] ASC
)
GO


USE [Doctors]
GO

/****** Object:  Table [dbo].[SOAP]    Script Date: 2/4/2016 5:13:31 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SOAP](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[HospNum] [varchar](10) NULL,
	[IDNum] [varchar](10) NULL,
	[DoctorID] [varchar](10) NULL,
	[SOAPtxt] [text] NULL,
	[SOAPDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



USE [Doctors]
GO

/****** Object:  Table [dbo].[SOAPTemplate]    Script Date: 2/4/2016 5:13:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SOAPTemplate](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](250) NULL,
	[Category] [varchar](10) NULL,
	[ISMain] [varchar](1) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO




        CREATE INDEX [by_IDNum] ON Doctors..DocNotes ([IDNum]) 
        GO
        
        CREATE INDEX [by_IDNum] ON Doctors..SOAP ([IDNum]) 
        GO
        
        CREATE INDEX [by_IDNum] ON Doctors..DOC_Orders ([IDNum]) 
        GO
        
        CREATE INDEX [by_IDNum] ON Doctors..DOC_Notes ([IDNum]) 
        GO
        
        CREATE INDEX [by_HospNum] ON Doctors..Allergies ([HospNum]) 
        GO
        
        CREATE INDEX [by_HospNum] ON Doctors..SocialHistory ([HospNum]) 
        GO
        
        CREATE INDEX [by_HospNum] ON Doctors..VitalSigns ([HospNum]) 
        GO
        
        CREATE INDEX [by_HospNum] ON Doctors..FamilyHistory ([HospNum]) 
        GO

        CREATE INDEX [by_HospNum] ON Doctors..MedicalHistory ([HospNum]) 
        GO
        
        CREATE INDEX [by_HospNum] ON Doctors..DOC_Immunization ([HospNum]) 
        GO
           
        CREATE INDEX [by_HospNum] ON Doctors..ObsHist ([HospNum]) 
        GO
        
        CREATE INDEX [by_HospNum] ON REGISTER.dbo.tbMedicalHistory ([HospNum]) 
        GO


        CREATE INDEX [by_HospNum] ON REGISTER.dbo.[tbPhysicalExamination] ([HospNum]) 
        GO


        CREATE INDEX [by_Common] ON [Build_FIle].[dbo].[tbIcdDiagMain]([ISCOMMON]) 
        GO

USE [STATION]
GO

/****** Object:  Table [dbo].[tbNurseHomeMeds]    Script Date: 03/13/2014 15:43:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbNurseHomeMeds](
	[Hospnum] [varchar](10) NOT NULL,
	[IDnum] [varchar](10) NOT NULL,
	[PatientType] [varchar](1) NULL,
	[ItemID] [varchar](5) NULL,
	[Amount] [float] NULL,
	[Quantity] [float] NULL,
	[Dosage] [varchar](8) NULL,
	[SectionID] [varchar](4) NULL,
	[RequestDate] [datetime] NULL,
	[RevenueID] [varchar](2) NULL,
	[DoctorID] [varchar](10) NULL,
	[RecordStatus] [varchar](1) NULL,
	[UserID] [varchar](10) NULL,
	[LabNum] [varchar](10) NULL,
	[RequestNum] [varchar](10) NULL,
	[ReferenceNum] [varchar](10) NULL,
	[Remarks] [varchar](500) NULL,
	[StationID] [varchar](10) NULL,
	[Stat] [varchar](1) NULL,
	[dcrdate] [smalldatetime] NULL,
	[isGeneric] [bit] NULL,
	[SpecimenID] [varchar](4) NULL,
	[Dialysis] [varchar](1) NULL,
	[Transplant] [varchar](1) NULL,
	[AMPickup] [bit] NULL,
	[DoctorName] [varchar](50) NULL,
	[Printed] [varchar](1) NULL,
	[isOpened] [bit] NOT NULL,
	[Consumption_Date] [datetime] NULL,
	[ReaderID] [varchar](10) NULL,
	[Revoke_UserID] [varchar](10) NULL,
	[Revoke_StationID] [varchar](10) NULL,
	[Revoke_Date] [datetime] NULL,
	[IsPortable] [bit] NULL,
	[Reason] [varchar](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N''''' - Pending; X-Processed; R - Cancelled' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbNurseHomeMeds', @level2type=N'COLUMN',@level2name=N'RecordStatus'
GO

ALTER TABLE [dbo].[tbNurseHomeMeds] ADD  CONSTRAINT [DF_tbNurseHomeMeds_RecordStatus]  DEFAULT ('') FOR [RecordStatus]
GO

ALTER TABLE [dbo].[tbNurseHomeMeds] ADD  CONSTRAINT [DF_tbNurseHomeMeds_Dialysis]  DEFAULT ((0)) FOR [Dialysis]
GO

ALTER TABLE [dbo].[tbNurseHomeMeds] ADD  CONSTRAINT [DF_tbNurseHomeMeds_Transplant]  DEFAULT ((0)) FOR [Transplant]
GO

ALTER TABLE [dbo].[tbNurseHomeMeds] ADD  CONSTRAINT [DF_tbNurseHomeMeds_AMPickup]  DEFAULT ((0)) FOR [AMPickup]
GO

ALTER TABLE [dbo].[tbNurseHomeMeds] ADD  CONSTRAINT [DF_tbNurseHomeMeds_isOpened]  DEFAULT ((0)) FOR [isOpened]
GO



USE [STATION]
GO

/****** Object:  StoredProcedure [dbo].[Nurse_AppendHomeMeds]    Script Date: 03/13/2014 15:43:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





/****** Object:  Stored Procedure dbo.Nurse_AppendHomeMeds    Script Date: 07/01/2000 1:19:56 AM ******/
CREATE PROCEDURE [dbo].[Nurse_AppendHomeMeds]
@Hospnum As Varchar(10),
@IDNum As Varchar(10),
@PatientType As Varchar(1),
@Item As Varchar(5),
@Amount As Float,
@Quantity As float,
@Dosage As Varchar(8),
@SectionID As Varchar(4),
@RequestDate As Datetime,
@RevenueID As Varchar(4),
@DoctorID As Varchar(10),
@RecordStatus As Varchar(1) = '',
@UserID As Varchar(10),
@Remarks As Varchar(100),
@Description As Varchar(100),
@StationID As Varchar(10),
@Stat As Varchar(1),
@IsGeneric As Bit,
@SpecimenID As varchar(4) = '',
@Transplant As varchar(1) = '',
@Dialysis As varchar(1) = '',
@IsAmPickUp as bit  = 0,
@DoctorName AS Varchar(50)=NULL,
@IsPortable As Bit=0,
@Reason as varchar(100) = NULL,
@Reason2 as varchar(100) = NULL,
@DOTSClass int = 0,
@DOTSReason int = 0, 
@PulmonarySite varchar(100) = ''

 AS

Declare @Refnum As Varchar(10);
Declare @Labnum As Varchar(8);
Declare @ChargeSlip As Varchar(8);
Declare @ReasonRadio as varchar(100);

set @ReasonRadio = @Reason

if isnull(@ReasonRadio,'') = '' 
	begin 
		set @ReasonRadio = @Reason2
	end;


Begin Tran

IF substring(@RevenueID,1,2) = 'PH' 
  Begin
     Select @ChargeSlip = rtrim(Convert(Varchar(8),ChargeSlip)) from tbNursePHSlip
     Update tbNursePHSlip Set ChargeSlip = ChargeSlip + 1;
   Set  @RefNum = 'PH'+@ChargeSlip;
  End;


	 Insert STATION..tbNurseHomeMeds (Hospnum,IDNum ,PatientType ,ItemID ,Amount,Quantity ,Dosage,SectionID,RequestDate,
					RevenueID ,DoctorID ,RecordStatus,UserID ,RequestNum ,LabNum,Remarks,StationID,Stat,IsGeneric,SpecimenID,Dialysis,Transplant,AmPickUp,DoctorName,Reason)
		Values			(@Hospnum,@IDNum ,@PatientType ,@Item ,@Amount,@Quantity ,@Dosage,@SectionID,GETDATE(),
					@RevenueID ,@DoctorID ,@RecordStatus,@UserID ,@Refnum ,@LabNum,@Remarks,@StationID,@Stat,@IsGeneric,@SpecimenID,@Dialysis,@Transplant,@IsAmPickUp,@DoctorName,@ReasonRadio) ;

If @@error<>0
   begin
      goto abort_save;
   end;

Commit Tran;
Return 0;

abort_save:
Rollback Tran;
Return -10001;






GO


        

USE [STATION]
GO

/****** Object:  StoredProcedure [dbo].[List_AllTakeHomeMeds]    Script Date: 03/13/2014 15:37:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[List_AllTakeHomeMeds]
@HospNum As Varchar(10),
@IDNum As Varchar(10),
@DepartmentCode As Varchar(3)
AS

Begin
	SELECT	DISTINCT 
			TBNURSEHOMEMEDS.RevenueID As Dept, 
			TBNURSEHOMEMEDS.ItemID AS Item#, 
			tbCoOtherRevenue.ItemName + ' (' + tbCoOtherRevenue.ItemDesc + ')' AS Description, 	
			convert(char,TBNURSEHOMEMEDS.Quantity,0) AS QTY, 
			convert(varchar(1000),TBNURSEHOMEMEDS.AMOUNT)  AS Amount, 
			convert(varchar(25),TBNURSEHOMEMEDS.REQUESTDATE,0) AS [Charge Date/Time] ,
			tbPassWordMain.Initial AS Initial,
			CASE
			when isnull(TBNURSEHOMEMEDS.recordstatus,'') = '' then 'PENDING'
			WHEN TBNURSEHOMEMEDS.RECORDSTATUS = 'R' THEN 'REVOKED'
			WHEN TBNURSEHOMEMEDS.RECORDSTATUS = 'X' THEN 'PROCESSED'
			END AS Status,
			tbnursehomemeds.requestnum
	FROM	STATION..TBNURSEHOMEMEDS AS TBNURSEHOMEMEDS 
			LEFT OUTER  JOIN INVENTORY..tbInvMaster AS tbCoOtherRevenue 
				ON TBNURSEHOMEMEDS.ItemID = tbCoOtherRevenue.ItemID
            LEFT OUTER JOIN PASSWORD..tbPasswordMaster AS tbPasswordMain 
				ON RTRIM(TBNURSEHOMEMEDS.UserID) = RTRIM(tbPasswordMain.EmployeeID)
	WHERE	TBNURSEHOMEMEDS.IDNum = @IDNum and 
			TBNURSEHOMEMEDS.RevenueID = 'PH'
	ORDER By Description 
 
End


GO


USE [Radiology]
GO

/****** Object:  Index [by_RefNum]    Script Date: 04/01/2014 14:28:49 ******/
CREATE NONCLUSTERED INDEX [by_RefNum] ON [dbo].[tbRISPatientQueueNo] 
(
	[Refnum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [Radiology]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbRISPatientQueueNo](
	[Hospnum] [varchar](8) NULL,
	[IDNum] [varchar](50) NULL,
	[PatientNum] [int] NULL,
	[Status] [varchar](1) NULL,
	[UserCode] [varchar](30) NULL,
	[revenueid] [varchar](2) NULL,
	[Requestnum] [varchar](15) NULL,
	[Refnum] [varchar](15) NULL,
	[RequestDate] [varchar](15) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
 
-----------------


USE [MIntranet]
GO
/****** Object:  Table [dbo].[MessagesRead]    Script Date: 05/28/2014 19:02:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MessagesRead](
	[UserID] [varchar](50) NOT NULL,
	[LastWOMSID] [bigint] NULL,
	[LastChatID] [bigint] NULL,
	[LastNewsID] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [MIntranet]
GO

/****** Object:  Index [byUserID]    Script Date: 05/28/2014 19:02:27 ******/
CREATE NONCLUSTERED INDEX [byUserID] ON [dbo].[MessagesRead] 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO




/****** FOR BUILD FILE ******/









USE BUILD_FILE 
GO

ALTER TABLE [TbBuildUserSettings]
ADD sInputTax bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sPHICnumber] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sBankAccount] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sDoctorRate] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sDoctorUtilities] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sAmenities] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sLogoandImage] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sBatchUpdate] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sClinicInfo] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sDocAccess] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [sClinicAddinfo] bit NULL


GO

ALTER TABLE [TbBuildUserSettings]
ADD [isAllowUploadDoctor] bit NULL


GO

ALTER TABLE [tbCoDoctor]
ADD [LicenseExpireDate] [varchar](30)


GO

ALTER TABLE [tbCoDoctor]
ADD [S2Number] [varchar](50)


GO

ALTER TABLE [tbCoDoctor]
ADD [S2Expiry] [varchar](20)


GO

ALTER TABLE [tbCoDoctor]
ADD [PHICExpireDate] [varchar](30)


GO

ALTER TABLE [tbCoDoctor]
ADD [CVStatus] [varchar](1)


GO

ALTER TABLE [tbCoDoctor]
ADD S2IssueDate [varchar](30)

use build_File
go

alter table tbcoaudexam
add RateSP  float

use build_File
go

alter table tbcoctexam
add RateSP  float

use build_File
go

alter table tbcohsexam
add RateSP  float

use build_File
go

alter table tbcolabexam
add RateSP float

use build_File
go

alter table tbconucexam
add RateSP float

use build_File
go

alter table tbcoptexam
add RateSP float

use build_File
go

alter table tbcortsexam
add RateSP float

use build_File
go

alter table tbcoultraexam
add RateSP float

use build_File
go

alter table tbcoxrayexam
add RateSP float

use build_File
go

alter table tbcootherrevenue
add RateSP float

USE BUILD_FILE
GO
ALTER TABLE [TbCoRevenueCode]
ADD AllowedEditAmount varchar(1)
GO

USE BUILD_FILE
GO
ALTER TABLE [TbCoRevenueCode]
ADD chkAllowedExemptAllMarkup varchar(1)
GO

USE BUILD_FILE
GO
ALTER TABLE [TbCoRevenueCode]
ADD isAllowedDiscountPerDay BIT
GO

USE BUILD_FILE
GO
ALTER TABLE [TbCoRevenueCode]
ADD AllowDiscountPerDay float
GO

USE BUILD_FILE
GO
ALTER TABLE [TbCoRevenueCode]
ADD PHACTIVE varchar(1)
GO

USE BUILD_FILE
GO
ALTER TABLE [TbCoRevenueCode]
ADD CSActive varchar(1)

USE BUILD_FILE
GO
ALTER TABLE [TbCoDoctor]
ADD PMCCDateExpire varchar(15)
GO

GO

ALTER TABLE tbCoAdmPackage_Items
add SpecimenID varchar(10)


GO



GO
CREATE PROCEDURE [dbo].[Lab_Barcode_Update]
@Barcode as varchar(15),
@BCCheckSum as varchar(1),
@RefNum as varchar(10),
@SpecimenID as varchar(4) = null,
@SectionID as varchar(4) = null

AS

UPDATE  LABORATORY..tbLabMaster
SET Barcode    = @Barcode,
        BCCheckSum = @BCCheckSum
WHERE   RefNum = @RefNum
        AND SpecimenID = @SpecimenID
        AND SectionID = @SectionID;

GO

alter table password.dbo.tbpasswordmaster add [LogoImage] [image] NULL

GO

/****** 06/23/2014 VBB ******/

USE [PATIENT_DATA]
GO

Alter TABLE [dbo].[tbpatient_Revoke]Add
	[RevokeUser] [varchar](10) NULL

GO


USE [PATIENT_DATA]
GO

/****** Object:  Table [dbo].[tbpatient_Revoke_Report]    Script Date: 06/23/2014 17:00:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbpatient_Revoke_Report](
	[HospNum] [varchar](10) NOT NULL,
	[IdNum] [varchar](10) NOT NULL,
	[AdmDate] [datetime] NULL,
	[ServiceID] [varchar](3) NULL,
	[AttendingDr1] [varchar](4) NULL,
	[AdmittingClerk] [varchar](8) NULL,
	[Age] [varchar](3) NULL,
	[RoomID] [varchar](8) NULL,
	[UserID] [varchar](10) NULL,
	[HostName] [varchar](30) NOT NULL,
	[RevokeDAte] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [PATIENT_DATA]
GO
/****** Object:  StoredProcedure [dbo].[sp_adm_RevokeAdmission_Report]    Script Date: 06/23/2014 16:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[sp_adm_RevokeAdmission_Report]
@IDNum as VarChar(10)
AS



Insert into Patient_data..tbpatient_Revoke_Report(
		  HospNum,
          IdNum,
          AdmDate,
          ServiceID,
          AttendingDr1,
          AdmittingClerk,
          Age,
          RoomID,
          UserID,
          HostName,
		  RevokeDAte)
Select           
		  HospNum,
          IdNum,
          AdmDate,
          ServiceID,
          AttendingDr1,
          AdmittingClerk,
          Age,
          RoomID,
          UserID,
          HostName,
		  GetDate()
From Patient_data..tbPatient 
Where IDNum = @IDNum;

GO

Use PATIENT_DATA
GO

Alter Table tbMaster2
Add PHICNum varchar(30)

GO
Alter table tbAdmReportSetup Add
DoctorsPatientLoad bit not null default('0')

GO
Alter Table tbadmittingsetup
Add isAllowAdmitBlockListed bit not null default('0')

GO
Alter Table tbHOspitalInfo
Add SeaFarer bit not null default('0')

Go
Alter Table tbPatient Add
[CardNum] [varchar](15) NULL,
[fullypaid] [bit] NULL,
[AccountNumII] [varchar](10) NULL,
[ChaplainsRemark] [text] NULL

Go
Alter Table tbMaster2 Add
SpousePosition varchar(40) null,
EmployerPosition varchar(40)null

GO
Alter Table tbPatientInformant Add
InformantOccupation varchar(40) null

GO
Alter Table tbPatient2 Add
[Transient] [varchar](5) NULL,
[DTR] [varchar](15) NULL

GO
Alter Table tbMaster2 Add
OfficeNum varchar(20) null

GO
Alter table tbOutPatient Add
isCPClearance bit not null default('0'),
isPEME bit not null default('0'),
isWithFollowUp bit not null default('0')

GO
Alter Table Patient_Data..tbPatientDiagnosis Add
Ordernumber varchar(3) null

GO





USE [Patient_Data]
GO
/****** Object:  Table [dbo].[tbPatientDocs]    Script Date: 06/27/2014 19:13:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbPatientDocs](
 	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[ConversionTime] [datetime] NULL,
	[HealthRecordID] [varchar](50) NULL,
	[HospitalNumber] [varchar](50) NULL,
	[HospitalName] [varchar](50) NULL,
	[RequestNum] [varchar](50) NULL,
	[LabSection] [varchar](50) NULL,
	[LabExam] [varchar](50) NULL,
	[DocType] [varchar](50) NULL,
	[FileName] [varchar](max) NULL,
	[FileData] [varbinary](max) NULL,
	[FileType] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [byDate] ON [dbo].[tbPatientDocs] 
(
	[ConversionTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [byHealthRecID] ON [dbo].[tbPatientDocs] 
(
	[HealthRecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [byHospNum] ON [dbo].[tbPatientDocs] 
(
	[HospitalNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


alter table patient_data..tbpatientdocs add [ID] [bigint] IDENTITY(1,1) NOT NULL
GO



USE [Doctors]
GO

/****** Object:  Index [by_HospNum]    Script Date: 07/21/2014 12:48:48 ******/
CREATE NONCLUSTERED INDEX [by_HospNum] ON [dbo].[tbPatientMeds] 
(
	[HospNum] ASC
)	


USE PATIENT_DATA
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbOutPatientTimeIn](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[IDNum] [varchar](10) NULL,
	[ServiceID] [int] NULL,
	[TimeIN] [datetime] NULL,
	[TimeOut] [datetime] NULL,
	[StatusID] [int] NULL,
	[doctorID] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO



CREATE INDEX by_IDNum on PATIENT_DATA..tbOutPatientTimeIn (IDNum)
GO



USE [Doctors]
GO

if  not exists (SELECT 1 FROM [PatientStatus]) 
begin 
	INSERT [dbo].[PatientStatus] ([StatusID], [StatusDesc]) VALUES (1, N'Attended')
	INSERT [dbo].[PatientStatus] ([StatusID], [StatusDesc]) VALUES (2, N'Ongoing')
	INSERT [dbo].[PatientStatus] ([StatusID], [StatusDesc]) VALUES (3, N'Another')
	INSERT [dbo].[PatientStatus] ([StatusID], [StatusDesc]) VALUES (4, N'Referred')
	INSERT [dbo].[PatientStatus] ([StatusID], [StatusDesc]) VALUES (5, N'Failed')
	INSERT [dbo].[PatientStatus] ([StatusID], [StatusDesc]) VALUES (6, N'APE')
end

go

alter table [Patient_Data].[dbo].[tbOutPatientHistory] alter column  DiagnosisID1 varchar(50)
go
alter table [Patient_Data].[dbo].[tbOutPatientHistory] alter column  DiagnosisID2 varchar(50)
go
alter table [Patient_Data].[dbo].[tbOutPatientHistory] alter column  DiagnosisID3 varchar(50)
go
alter table [Patient_Data].[dbo].[tbOutPatientHistory] alter column  DiagnosisID4 varchar(50)
go
alter table [Patient_Data].[dbo].[tbOutPatientHistory] alter column  DiagnosisID5 varchar(50)
go




USE [Build_File]
GO

/****** Object:  Table [dbo].[tbcoPatientClass]    Script Date: 08/07/2014 17:01:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbcoPatientClass](
	[Classcode] [varchar](5) NULL,
	[Classification] [varchar](50) NULL,
	[Status] [bit] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbcoPatientClass] ADD  DEFAULT ((1)) FOR [Status]
GO


if  not exists (SELECT 1 FROM [tbcoPatientClass]) 
begin 
INSERT [dbo].[tbcoPatientClass] ([Classcode], [Classification], [Status]) VALUES (N'M', N'Member', 1)
INSERT [dbo].[tbcoPatientClass] ([Classcode], [Classification], [Status]) VALUES (N'E', N'Employee', 1)
INSERT [dbo].[tbcoPatientClass] ([Classcode], [Classification], [Status]) VALUES (N'D', N'Dependent', 1)
INSERT [dbo].[tbcoPatientClass] ([Classcode], [Classification], [Status]) VALUES (N'O', N'Outsider', 1)
end
go


USE [Patient_Data]
GO



alter TABLE [dbo].[tbOutPatient] add   	[PatientTypeCombo] [varchar](10) NULL
go


USE [Patient_Data]
GO
 
alter table [tbOutPatient] add [isFollowUp] [bit] NULL 

go