use build_file
go
----------------------------------------
create table tbCoRoomAmenities
(
RoomID varchar(10),
AmenityID varchar(10),
Status bit ,
Quantity int null
)


create table tbCoAmenities
(
AmenityID varchar(10),
Amenity varchar (50)
)
------------------------------------------
USE [Password]
GO
/****** Object:  Table [dbo].[tbChangeLog]    Script Date: 09/06/2012 03:51:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbChangeLog](
        [ID] [bigint] IDENTITY(1,1) NOT NULL,
        [UpdateDate] [datetime] NULL,
        [UpdatedBy] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ActionDone] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ModuleName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
-------------------------------------------
USE [Build_File]
GO
/****** Object:  Table [dbo].[tbDBImage]    Script Date: 08/06/2012 13:49:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbDBImage](
        [ID] [bigint] IDENTITY(1,1) NOT NULL,
        [TableName] [varchar](15) NULL,
        [FieldName] [varchar](15) NULL,
        [IDKEY] [varchar](15) NULL,
        [DBImage] [image] NULL,
        [FileType] [varchar](15) NULL,
        [FileSize] [int] NULL,
        [ImgNote] [text] NULL,
        [TransDATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
-----------------------------------------
alter table TbBuildUserSettings
add sShowUserValidation bit null

alter table TbBuildUserSettings
add sRoomStatus bit null

alter table TbBuildUserSettings
add sDeposittype bit null

alter table TbBuildUserSettings
add sHospitalList bit null

alter table TbBuildUserSettings
add sICDCode bit null

alter table TbBuildUserSettings
add sCreditMemo bit null

alter table TbBuildUserSettings
add sCausesofConsultation bit null

alter table TbBuildUserSettings
add sHoliday bit null

alter table TbBuildUserSettings
add sShowUserValidation bit null
go


alter table build_file..tbcoroom
add WithAmenities varchar(1)
                                 
alter table build_file..tbbuildusersettings
add sEarningPoints bit null



alter table build_file..tbcodoctor
add ClinicTelNum varchar(70)

alter table BUILD_FILE..tbCoRevenueCode
add  IsPatientCard bit null

alter table build_file..tbcoroomamenities
add Quantity int null

alter table  build_file..tbcoptexam
add ClassType varchar(2)

alter table build_file..tbcocompany
add IsEmployee bit

alter table build_file..tbcootherrevenue
add FormType varchar(2)

alter table build_file..tbcocompanyrates
add RateCompany1 float

alter table build_file..tbcocompanyrates
add ItemID varchar(10)


alter table build_file..tbcoUltraexam
add OpenQuantity varchar(1) null

alter table build_file..TbCoXrayExam
add OpenQuantity varchar(1) null

alter table build_file..TbCoCTExam
add OpenQuantity varchar(1) null

alter table build_file..TbCoOtherRevenue
add OpenQuantity varchar(1) null,
Classification varchar(10) null
go
-----------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'SP_Bui_UpdateItem')
EXEC ('CREATE PROC dbo.SP_Bui_UpdateItem AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[SP_Bui_UpdateItem] --->Dated 02/08/2012 by sensei
(@RevenueID as varchar(2), @ItemID as varchar(5), @SectionID as varchar(3), @ExamName as varchar(45),
    @RateA as float, @RateB as Float, @RateC as float, @RateD as float, @RateE as float,
        @RateF as Float, @RateG as Float, @RateH as Float,
        @Fixrate as varchar(1),
        @Status as varchar(1), @MedicareType as varchar(1), @common as varchar(1), @RateSD as float=0.00, @IsSenior as bit, @IsSpecial as bit,
        @AccountCode as varchar(12),
        @KidneyActive as varchar(1),
        @DRActive as varchar(1),
        @HSActive as varchar(1),
        @ERActive as varchar(1),
        @ICUActive as varchar(1),
        @PICUActive as varchar(1),
        @ORActive as varchar(1),
        @NurseryActive as varchar(1),
        @NurseActive as varchar(1),
        @OPDActive as varchar(1),
        @PulmoActive as varchar(1),
        @PTActive as varchar(1),
        @MedRecActive as varchar(1),
        @NuclearActive as varchar(1),
        @SICUActive as varchar(1),
        @CCUActive as varchar(1),
        @WCActive as varchar(1),
        @EndoActive as varchar(1),
        @EyeActive as varchar(1),
        @CathLabActive as varchar(1),
        @rateI as float = 0)
as
        If (@RevenueID = 'AU')
    update build_file..tbcoaudexam
                                set
                                        AudSectionID=@SectionID,
                                        AudExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where audexamid = @itemid
else
If (@RevenueID = 'CT')
    update build_file..tbcoctexam
                                set
                                        CTSectionID=@SectionID,
                                        CTExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where ctexamid = @itemid
else
If (@RevenueID = 'HS')
    update build_file..tbcohsexam
                                set
                                        HSSectionID=@SectionID,
                                        HSExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where hsexamid = @itemid
If (@RevenueID = 'LB')
   update build_file..tbcolabexam
                                set
                                        LABSectionID=@SectionID,
                                        LABExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where LABexamid = @itemid
else
If (@RevenueID = 'NU')
       update build_file..tbconucexam
                                set
                                        NUCSectionID=@SectionID,
                                        NUCExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where NUCexamid = @itemid
else
If (@RevenueID = 'PT')
       update build_file..tbcoptexam
                                set
                                        PTSectionID=@SectionID,
                                        PTExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where PTexamid = @itemid
else
If (@RevenueID = 'PA')
      update build_file..tbcortsexam
                                set
                                        RTSSectionID=@SectionID,
                                        RTSExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where RTSexamid = @itemid
else
If (@RevenueID = 'US')
       update build_file..tbcoultraexam
                                set
                                        ULTRASectionID=@SectionID,
                                        ULTRAExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where ULTRAexamid = @itemid
else
If (@RevenueID = 'XR')
       update build_file..tbcoxrayexam
                                set
                                        XRAYSectionID=@SectionID,
                                        XRAYExam=@ExamName,
                                        RateA=@RateA,
                                        RateB=@RateB,
                                        RateC=@RateC,
                                        RateD=@RateD,
                                        RateE=@RateE,
                                        RateF=@RateF,
                                        RateG=@RateG,
                                        RateH=@RateH,
                                        Fixrate=@Fixrate,
                                        Status=@Status,
                                        MedicareType=@MedicareType,
                                        common=@common,
                                        RateSD=@RateSD,
                                        IsSenior=@IsSenior,
                                        IsSpecial=@IsSpecial,
                                        AccountCode=@AccountCode,
                                        rateI=@rateI
                                where XRAYexamid = @itemid
else
    Update tbcootherrevenue
                        set
                                OtherRevenue=@ExamName,
                                RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH,
                                Fixrate=@Fixrate,
                                Status=@Status,
                                MedicareType=@MedicareType,
                                common=@common,
                                RateSD=@RateSD,
                                IsSenior=@IsSenior,
                                IsSpecial=@IsSpecial,
                                AccountCode=@AccountCode,
                                KidneyActive=@KidneyActive,
                                DRActive=@DRActive,
                                HSActive=@HSActive,
                                ERActive=@ERActive,
                                ICUActive=@ICUActive,
                                PICUActive=@PICUActive,
                                ORActive=@ORActive,
                                NurseryActive=@NurseryActive,
                                NurseActive=@NurseActive,
                                OPDActive=@OPDActive,
                                PulmoActive=@PulmoActive,
                                PTActive=@PTActive,
                                MedRecActive=@MedRecActive,
                                NuclearActive=@NuclearActive,
                                SICUActive=@SICUActive,
                                CCUActive=@CCUActive,
                                WCActive=@WCActive,
                                EndoActive=@EndoActive,
                                EyeActive=@EyeActive,
                                CathLabActive=@CathLabActive,
                                rateI=@rateI
                             where otherrevenueid = @itemid and othersectionid = @revenueid
GO
----------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_RoomAmenities')
EXEC ('CREATE PROC dbo.sp_Bui_RoomAmenities AS SELECT 1')
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[sp_Bui_RoomAmenities] --->Dated 02/08/2012 by sensei
@RoomID as varchar(10),
@AmenityID as varchar(10),
@Status as bit,
@Quantity as int

as
IF (Exists(Select AmenityID from Build_file..tbCoRoomAmenities Where AmenityID=@AmenityID and RoomID=@RoomID))
begin
update build_file..tbcoRoomAmenities
set RoomID=@RoomID,
        AmenityID=@AmenityID,
        Status=@Status,
        Quantity=@Quantity
where AmenityID=@AmenityID and RoomID=@RoomID
end;
else
begin
insert into build_file..tbcoroomAmenities
(RoomID,AmenityID,Status,Quantity)
values
(@RoomID,@AmenityID,@Status,@Quantity)
end;
GO
------------------------------------------------------------------------
go
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_BatchUpdate')
EXEC ('CREATE PROC dbo.sp_Bui_BatchUpdate AS SELECT 1')
GO

ALTER procedure [dbo].[sp_Bui_BatchUpdate] --->Dated 02/08/2012 by sensei
          @RevenueID as varchar(2),
          @ItemID as varchar(5),
          @RateA as float,
          @RateB as Float,
          @RateC as float,
          @RateD as float,
          @RateE as float,
          @RateF as float,
          @RateG as float,
          @RateH as float,
          @RateI as float,
          @RateSD as float = 0.00

        AS

        If (@RevenueID = 'AU')

                Update tbcoaudexam

                        set     RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH
                        where audexamid = @itemid
        else

        If (@RevenueID = 'CT')

                Update tbcoctexam

                        set RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH,
                                RateSD = @RateSD
                        where ctexamid = @itemid
        else

        If (@RevenueID = 'HS')

                Update tbcohsexam

                        set  RateA=@RateA,
                                 RateB=@RateB,
                                 RateC=@RateC,
                                 RateD=@RateD,
                                 RateE=@RateE,
                                 RateF=@RateF,
                                 RateG=@RateG,
                                 RateH=@RateH
                        where hsexamid = @itemid
        else

        If (@RevenueID = 'LB')

                Update tbcolabexam

                        set  RateA=@RateA,
                                 RateB=@RateB,
                                 RateC=@RateC,
                                 RateD=@RateD,
                                 RateE=@RateE,
                                 RateF=@RateF,
                                 RateG=@RateG,
                                 RateH=@RateH
                        where labexamid = @itemid
        else

        If (@RevenueID = 'NU')

                Update tbconucexam

                        set  RateA=@RateA,
                                 RateB=@RateB,
                                 RateC=@RateC,
                                 RateD=@RateD,
                                 RateE=@RateE,
                                 RateF=@RateF,
                                 RateG=@RateG,
                                 RateH=@RateH
                        where nucexamid = @itemid
        else

        If (@RevenueID = 'PT')

                Update tbcoptexam

                        set RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH,
                                RateSD = @RateSD
                        where ptexamid = @itemid
        else

        If (@RevenueID = 'PA')

                Update tbcortsexam

                        set RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH,
                                RateI=@RateI
                        where rtsexamid = @itemid
        else

        If (@RevenueID = 'US')

                Update tbcoultraexam

                        set RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH,
                                RateI=@RateI,
                                RateSD = @RateSD
                        where ultraexamid = @itemid
        else

        If (@RevenueID = 'XR')

                Update tbcoxrayexam

                        set RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH,
                                RateI=@RateI,
                                RateSD = @RateSD
                        where xrayexamid = @itemid
        else

                Update tbcootherrevenue

                        set RateA=@RateA,
                                RateB=@RateB,
                                RateC=@RateC,
                                RateD=@RateD,
                                RateE=@RateE,
                                RateF=@RateF,
                                RateG=@RateG,
                                RateH=@RateH,
                                RateI=@RateI
                        where otherrevenueid = @itemid and othersectionid = @revenueid
GO
----------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'spBuild_UserSettingsNew')
EXEC ('CREATE PROC dbo.spBuild_UserSettingsNew AS SELECT 1')
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Procedure [dbo].[spBuild_UserSettingsNew] --->Dated 02/08/2012 by sensei
@Employeeid as varchar(10)

as

SELECT isnull(EmployeeID, 0)EmployeeIDisnull,
      isnull(sCompany, 0)sCompany,
      isnull(sBussinessClass, 0) sBussinessClass,
      isnull(sDepartment, 0) sDepartment,
      isnull(sNationality, 0) sNationality,
      isnull(sAddress, 0) sAddress,
      isnull(sBarangay, 0) sBarangay,
      isnull(sReligion, 0)sReligion,
      isnull(sDoctor, 0)sDoctor,
      isnull(sRoom, 0)sRoom,
      isnull(sStation, 0)sStation,
      isnull(sRoomClass, 0)sRoomClass,
      isnull(sAccomodation, 0)sAccomodation,
      isnull(sDosage, 0)sDosage,
      isnull(sItemPackage, 0)sItemPackage,
      isnull(sRevenue, 0)sRevenue,
      isnull(sCharging, 0)sCharging,
      isnull(sAccounting, 0)sAccounting,
      isnull(sLabCode, 0)sLabCode,
      isnull(sLabSpecimen, 0)sLabSpecimen,
      isnull(sSection, 0)sSection,
      isnull(sService, 0)sService,
      isnull(sAddressRPT, 0)sAddressRPT,
      isnull(sDoctorsRPT, 0)sDoctorsRPT,
      isnull(sCompanyRPT, 0)sCompanyRPT,
      isnull(sRoomCodesRPT, 0)sRoomCodesRPT,
      isnull(sDepartmentsRPT, 0)sDepartmentsRPT,
      isnull(sBarangaysRPT, 0)sBarangaysRPT,
      isnull(sRvcRPT, 0)sRvcRPT,
      isnull(sRevExamRPT, 0)sRevExamRPT,
      isnull(sARCodesRPT, 0)sARCodesRPT,
      isnull(sGLChartCodesRPT, 0)sGLChartCodesRPT,
      isnull(sAccountingRPT, 0)sAccountingRPT,
      isnull(sDeptARCodeRPT, 0)sDeptARCodeRPT,
      isnull(sInterpretRPT, 0)sInterpretRPT,
      isnull(sRCCodesRPT, 0)sRCCodesRPT,
      isnull(sTelephoneRPT, 0)sTelephoneRPT,
      isnull(sMedicalSpecializationRPT, 0)sMedicalSpecializationRPT,
      isnull(sSurgicalCodeRPT, 0)sSurgicalCodeRPT,
      isnull(sDoctorProfFee, 0)sDoctorProfFee,
      isnull(sDiscountRate, 0)sDiscountRate,
      isnull(sCompanyRate, 0)sCompanyRate,
      isnull(sDocSpecialization, 0)sDocSpecialization,
      isnull(sSOP, 0)sSOP,
      isnull(sExePackage, 0)sExePackage,
      isnull(sUserLog, 0)sUserLog,
      isnull(sUserSetting, 0)sUserSetting,
      isnull(sForInfo, 0)sForInfo,

          isnull(sRoomStatus,0)sRoomStatus,
          isnull(sDeposittype, 0)sDepositType,
          isnull(sHospitalList,0)sHospitalList,
          isnull(sICDCode,0)sICDcode,
      isnull(sCreditMemo,0)sCreditMemo,
          isnull(sCausesofConsultation,0)sCausesofConsultation,
      isnull(sHoliday,0)sHoliday,
          isnull(sShowUserValidation , 0) sShowUserValidation


  FROM BUILD_FILE.dbo.TbBuildUserSettings

where employeeid = @Employeeid
go


--------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_Update_Revenue_Center_New')
EXEC ('CREATE PROC dbo.sp_Bui_Update_Revenue_Center_New AS SELECT 1')
GO
ALTER PROCEDURE [dbo].[sp_Bui_Update_Revenue_Center_New] --->Dated 02/08/2012 by sensei

(        @RevenueID             as varchar(2),

         @Itemname              as varchar(45),

         @lgrp                  as varchar(1),

         @reportrevenue         as varchar(1),

         @medicaretype  as varchar(1),

         @drcr                  as varchar(1),

         @fix                   as varchar(1),

         @recordstatus          as varchar(1),

         @rwith                         as varchar(1),

         @billingactive         as varchar(1),

         @cashieractive         as varchar(1),

         @nurseactive           as varchar(1),

         @parsactive            as varchar(1),

         @medicareactive        as varchar(1),

         @kidneyactive  as varchar(1),

         @dractive              as varchar(1),

         @eractive              as varchar(1),

         @icuactive             as varchar(1),

         @nuactive              as varchar(1),

         @oractive              as varchar(1),

         @opdactive             as varchar(1),

         @PAactive              as varchar(1) = null,

         @WithProfFee as bit = null,

         @PrepaidActive as varchar(1) = null,

         @EndoActive as varchar(1) = null,

         @DebitCode             as varchar(15) = null,

         @CreditCode            as varchar(15) = null,

         @AccountName   as varchar(30) = null,

         @NurseryActive         as varchar(1) = null,

         @PICUActive            as varchar(1) = null,

         @CVUActive             as varchar(1)= null,

         @HemaActive            as varchar(1) = null,

         @HSActive              as varchar(1) = null,

         @StationID             as varchar(4) = null,

         @CCUActive      as varchar(1) = null,

     @IsPatientCard  as bit,

         @StockCardRevenueID as varchar(5) = ''

)

AS

Update   tbcorevenuecode

set   Itemname = @itemname,

          lgrp = @lgrp,

          reportrevenue =  @reportrevenue,

          medicaretype = @medicaretype,

          drcr = @drcr,

          fix = @fix,

          recordstatus = @recordstatus,

          rwith = @rwith,

          billingactive =  @billingactive,

          cashieractive =  @cashieractive,

          nurseactive = @nurseactive,

          parsactive = @parsactive,

          medicareactive =  @medicareactive,

          kidneyactive =  @kidneyactive,

          dractive = @dractive,

          eractive = @eractive,

          icuactive =  @icuactive,

          nuactive =  @nuactive,

          oractive = @oractive,

          opdactive = @opdactive,

          PAactive = @PAactive,

          WithProfFee = @WithProfFee,

          PrepaidActive = @PrepaidActive,

          EndoActive = @EndoActive,

          GLINDebitCode = @DebitCode,

          GLINCreditCode = @CreditCode,

          AccountName = @AccountName,

          NurActive = @NurseryActive,

          PICUActive = @PICUActive,

          CVUActive = @CVUActive,

          HemaActive = @HemaActive,

          HSActive = @HSActive,

          StationID = @StationID,

          CCUActive = @CCUActive,

          IsPatientCard = @IsPatientCard,

          StockCardRevenueID = @StockCardRevenueID

where     revenueid = @RevenueID
GO
--------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_Insert_Revenue_Center_New')
EXEC ('CREATE PROC dbo.sp_Bui_Insert_Revenue_Center_New AS SELECT 1')
GO
ALTER PROCEDURE [dbo].[sp_Bui_Insert_Revenue_Center_New] --->Dated 02/08/2012 by sensei

(        @RevenueID             as varchar(2),

         @Itemname              as varchar(45),

         @lgrp                  as varchar(1),

         @reportrevenue         as varchar(1),

         @medicaretype  as varchar(1),

         @drcr                  as varchar(1),

         @fix                   as varchar(1),

         @recordstatus          as varchar(1),

         @rwith                 as varchar(1),

         @billingactive         as varchar(1),

         @cashieractive         as varchar(1),

         @nurseactive           as varchar(1),

         @parsactive            as varchar(1),

         @medicareactive        as varchar(1),

         @kidneyactive          as varchar(1),

         @dractive              as varchar(1),

         @eractive              as varchar(1),

         @icuactive             as varchar(1),

         @nuactive              as varchar(1),

         @oractive              as varchar(1),

         @opdactive             as varchar(1),

         @PAactive              as varchar(1) = null,

         @PrepaidActive as varchar(1) = null,

        @EndoActive             as varchar(1) = null,

        @DebitCode              as varchar(15) = null,

        @CreditCode             as varchar(15) = null,

        @AccountName            as varchar(30) = null,

        @NurseryActive          as varchar(1) = null,

        @PICUActive             as varchar(1) = null,

        @CVUActive              as varchar(1) = null,

        @HemaActive             as varchar(1) = null,

        @HSActive               as varchar(1) = null,

        @StationID              as varchar(4) = null,

        @CCUActive          as varchar(1) = null,

        @StockCardRevenueID as varchar(5) = '',

        @IsPatientCard as bit
)

AS

insert into tbcorevenuecode(RevenueID, Itemname, lgrp,

          reportrevenue, medicaretype, drcr,

          fix, recordstatus, rwith, billingactive,

          cashieractive, nurseactive, parsactive,

          medicareactive, kidneyactive, dractive,

          eractive, icuactive, nuactive, oractive,opdactive,PAactive,

          PrepaidActive, EndoActive, GLInDebitCode, GLInCreditCode, AccountName,

          NurActive, PICUActive, CVUActive, HemaActive, HSActive, StationID,

      CCUActive, IsPatientCard, StockCardRevenueID )

values   (@RevenueID,  @Itemname, @lgrp,

         @reportrevenue, @medicaretype, @drcr,

         @fix, @recordstatus, @rwith, @billingactive,

         @cashieractive,  @nurseactive, @parsactive,

         @medicareactive, @kidneyactive, @dractive,

         @eractive, @icuactive, @nuactive, @oractive,@opdactive,@PAactive,

     @PrepaidActive, @EndoActive, @DebitCode, @CreditCode, @AccountName,

         @NurseryActive, @PICUActive, @CVUActive, @HemaActive, @HSActive,

     @StationID, @CCUActive, @IsPatientCard, @StockCardRevenueID)
GO
----------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'spBuild_UserSettingsNew')
EXEC ('CREATE PROC dbo.spBuild_UserSettingsNew AS SELECT 1')
GO

ALTER Procedure [dbo].[spBuild_UserSettingsNew] --->Dated 02/08/2012 by sensei
@Employeeid as varchar(10)

as

SELECT isnull(EmployeeID, 0)EmployeeIDisnull,
      isnull(sCompany, 0)sCompany,
      isnull(sBussinessClass, 0) sBussinessClass,
      isnull(sDepartment, 0) sDepartment,
      isnull(sNationality, 0) sNationality,
      isnull(sAddress, 0) sAddress,
      isnull(sBarangay, 0) sBarangay,
      isnull(sReligion, 0)sReligion,
      isnull(sDoctor, 0)sDoctor,
      isnull(sRoom, 0)sRoom,
      isnull(sStation, 0)sStation,
      isnull(sRoomClass, 0)sRoomClass,
      isnull(sAccomodation, 0)sAccomodation,
      isnull(sDosage, 0)sDosage,
      isnull(sItemPackage, 0)sItemPackage,
      isnull(sRevenue, 0)sRevenue,
      isnull(sCharging, 0)sCharging,
      isnull(sAccounting, 0)sAccounting,
      isnull(sLabCode, 0)sLabCode,
      isnull(sLabSpecimen, 0)sLabSpecimen,
      isnull(sSection, 0)sSection,
      isnull(sService, 0)sService,
      isnull(sAddressRPT, 0)sAddressRPT,
      isnull(sDoctorsRPT, 0)sDoctorsRPT,
      isnull(sCompanyRPT, 0)sCompanyRPT,
      isnull(sRoomCodesRPT, 0)sRoomCodesRPT,
      isnull(sDepartmentsRPT, 0)sDepartmentsRPT,
      isnull(sBarangaysRPT, 0)sBarangaysRPT,
      isnull(sRvcRPT, 0)sRvcRPT,
      isnull(sRevExamRPT, 0)sRevExamRPT,
      isnull(sARCodesRPT, 0)sARCodesRPT,
      isnull(sGLChartCodesRPT, 0)sGLChartCodesRPT,
      isnull(sAccountingRPT, 0)sAccountingRPT,
      isnull(sDeptARCodeRPT, 0)sDeptARCodeRPT,
      isnull(sInterpretRPT, 0)sInterpretRPT,
      isnull(sRCCodesRPT, 0)sRCCodesRPT,
      isnull(sTelephoneRPT, 0)sTelephoneRPT,
      isnull(sMedicalSpecializationRPT, 0)sMedicalSpecializationRPT,
      isnull(sSurgicalCodeRPT, 0)sSurgicalCodeRPT,
      isnull(sDoctorProfFee, 0)sDoctorProfFee,
      isnull(sDiscountRate, 0)sDiscountRate,
      isnull(sCompanyRate, 0)sCompanyRate,
      isnull(sDocSpecialization, 0)sDocSpecialization,
      isnull(sSOP, 0)sSOP,
      isnull(sExePackage, 0)sExePackage,
      isnull(sUserLog, 0)sUserLog,
      isnull(sUserSetting, 0)sUserSetting,
      isnull(sForInfo, 0)sForInfo,

          isnull(sRoomStatus,0)sRoomStatus,
          isnull(sDeposittype, 0)sDepositType,
          isnull(sHospitalList,0)sHospitalList,
          isnull(sICDCode,0)sICDcode,
      isnull(sCreditMemo,0)sCreditMemo,
          isnull(sCausesofConsultation,0)sCausesofConsultation,
      isnull(sHoliday,0)sHoliday,
          isnull(sShowUserValidation , 0) sShowUserValidation ,
          isnull(sEarningPoints, 0)sEarningPoints


  FROM BUILD_FILE.dbo.TbBuildUserSettings

where employeeid = @Employeeid
GO
----------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Build_UserSettings')
EXEC ('CREATE PROC dbo.sp_Build_UserSettings AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[sp_Build_UserSettings] --->Dated 02/08/2012 by sensei

@EmployeeID as varchar(30),
@sCompany as varchar(1),
@sBussinessClass as varchar(1),
@sDepartment as varchar(1),
@sDoctor as varchar(1),
@sNationality as varchar(1),
@sReligion as varchar(1),
@sAddress as varchar(1),
@sBarangay as varchar(1),
@sRoomClass as varchar(1),
@sRoom as varchar(1),
@sStation as varchar(1),
@sAccomodation as varchar(1),
@sDosage as varchar(1),
@sItemPackage as varchar(1),

@sRevenue as varchar(1),
@sCharging as varchar(1),
@sAccounting as varchar(1),

@sLabCode as varchar(1),
@sLabSpecimen as varchar(1),
@sSection as varchar(1),
@sService as varchar(1),

@sAddressRPT as varchar(1)='0',
@sDoctorsRPT as varchar(1)='0',
@sCompanyRPT as varchar(1)='0',
@sRoomCodesRPT as varchar(1)='0',

@sDepartmentsRPT as varchar(1)='0',
@sBarangaysRPT as varchar(1)='0',

@sRvcRPT as varchar(1)='0',
@sRevExamRPT as varchar(1)='0',

@sARCodesRPT as varchar(1)='0',
@sGLChartCodesRPT as varchar(1)='0',
@sAccountingRPT as varchar(1)='0',
@sDeptARCodeRPT as varchar(1)='0',
@sInterpretRPT as varchar(1)='0',
@sRCCodesRPT as varchar(1)='0',
@sTelephoneRPT as varchar(1) ='0',
@sMedicalSpecializationRPT as varchar(1) = '0',
@sSurgicalCodeRPT as varchar(1) = '0',
@sDocProfFee as varchar(1)='0',
@sDiscountRate as varchar(1)='0',
@sCompanyRate as varchar(1)='0',
@sDocSpec as varchar(1) ='0',
@sSOP as varchar(1) = '0',
@sExePackage as varchar(1) = '0',
@UserLog as varchar(1)= '0',
@UserSetting as varchar(1) = '1',
@ForInfo as varchar(1) = '0',
@RoomStatus as varchar(1) = '0',
@DepositType as varchar(1) = '0',
@HospitalList varchar(1) = '0',
@ICDcode varchar(1) = '0',
@CreditMemo varchar(1) = '0',
@CausesofConsultation varchar(1) = '0',
@Holiday varchar(1) = '0',
@UserValidation varchar(1) = '0',
@Points varchar(1)= '0'

as

If exists (Select *  from tbBuildUserSettings where employeeId= @EmployeeId)
Begin
        Update TbBuildUserSettings
                set EmployeeId = @Employeeid,
                       sCompany = @sCompany,
                                  sBussinessClass = @sBussinessClass,
                       sDepartment = @sDepartment,
                       sDoctor = @sDoctor,
                       sNationality = @sNationality,
                       sReligion = @sReligion,
                       sAddress = @sAddress,
                       sBarangay = @sBarangay,
                                  sRoomClass = @sRoomClass,
                                  sRoom = @sRoom,
                       sStation= @sStation,
                       sAccomodation = @sAccomodation,
                       sDosage = @sDosage,
                        sItemPackage = @sItemPackage,
                        sCharging = @sCharging,
                        sRevenue = @sRevenue,
                        sAccounting = @sAccounting,
                        sLabCode = @SLabCode,
                        sLabSpecimen = @sLabSpecimen,
                        sSection = @sSection,
                        sService = @sService,

                        sAddressRPT = @sAddressRPT,
                        sDoctorsRPT = @sDoctorsRPT ,
                        sCompanyRPT = @sCompanyRPT,
                        sRoomCodesRPT = @sRoomCodesRPT,

                        sDepartmentsRPT= @sDepartmentsRPT ,
                        sBarangaysRPT= @sBarangaysRPT,

                        sRvcRPT = @sRvcRPT,
                        sRevExamRPT = @sRevExamRPT,

                        sARCodesRPT = @sARCodesRPT,
                        sGLChartCodesRPT = @sGLChartCodesRPT,
                        sAccountingRPT = @sAccountingRPT,
                        sDeptARCodeRPT= @sDeptARCodeRPT,
                        sInterpretRPT = @sInterpretRPT,
                        sRCCodesRPT = @sRCCodesRPT,
                        sTelephoneRPT   = @sTelephoneRPT,
                        sMedicalSpecializationRPT = @sMedicalSpecializationRPT,
                        sSurgicalCodeRPT = @sSurgicalCodeRPT,
                        sDoctorProfFee = @sDocProfFee,
                        sDiscountRate = @sDiscountRate,
                        sCompanyRate = @sCompanyRate,
                        sDocSpecialization = @sDocSpec,
                        sSOP = @sSOP,
                        sExePackage = @sExePackage,
                        sUserlog = @UserLog,
                        sUserSetting = @UserSetting,
                        sForInfo = @ForInfo,
                        sRoomStatus = @roomstatus,
                        sDepositType = @DepositType,
                        sHospitalList = @HospitalList,
                        sICDcode = @ICDcode,
                        sCreditMemo= @CreditMemo,
                        sCausesofConsultation= @CausesofConsultation,
                        sHoliday =@Holiday,
                        sShowUserValidation  = @UserValidation,
                        sEarningPoints = @Points
        Where EmployeeId =  @EmployeeId;
     End
Else
     Begin

        Insert into TbBuildUserSettings(EmployeeID,sCompany,sBussinessClass,sDepartment,sDoctor,sNationality, sReligion, sAddress,
                        sBarangay,sRoomClass, sRoom,sStation,sAccomodation, sDosage, sItemPackage,
                        sCharging, sRevenue, sAccounting, sLabCode, sLabSpecimen, sSection, sService, sAddressRPT, sDoctorsRPT,  sCompanyRPT, sRoomCodesRPT,
                        sDepartmentsRPT, sBarangaysRPT, sRvcRPT, sRevExamRPT, sARCodesRPT, sGLChartCodesRPT,  sAccountingRPT, sDeptARCodeRPT, sInterpretRPT,
                        sRCCodesRPT, sTelephoneRPT,  sMedicalSpecializationRPT, sSurgicalCodeRPT,  sDoctorProfFee, sDiscountRate, sCompanyRate, sDocSpecialization,
                        sSOP, sExePackage, sUSerLog, sUserSetting, sForInfo,sRoomStatus,sDepositType,sHospitalList,sICDcode,
                        sCreditMemo,sCausesofConsultation,sHoliday,sShowUserValidation,sEarningPoints)
                values(@EmployeeID,@sCompany,@sBussinessClass, @sDepartment,@sDoctor,@sNationality, @sReligion, @sAddress,
                             @sBarangay,@sRoomClass, @sRoom,@sStation,@sAccomodation, @sDosage, @sItemPackage,
                        @sCharging, @sRevenue, @sAccounting, @sLabCode, @sLabSpecimen, @sSection, @sService, @sAddressRPT,
                        @sDoctorsRPT , @sCompanyRPT,  @sRoomCodesRPT, @sDepartmentsRPT , @sBarangaysRPT, @sRvcRPT,
                        @sRevExamRPT, @sARCodesRPT,  @sGLChartCodesRPT, @sAccountingRPT, @sDeptARCodeRPT, @sInterpretRPT,
                        @sRCCodesRPT, @sTelephoneRPT, @sMedicalSpecializationRPT,  @sSurgicalCodeRPT, @sDocProfFee, @sDiscountRate,
                        @sCompanyRate, @sDocSpec, @sSOP, @sExePackage, @UserLog, @UserSetting, @ForInfo,
            @RoomStatus,@DepositType,@HospitalList,@ICDcode,
                        @CreditMemo,@CausesofConsultation,@Holiday,@UserValidation,@Points      )
     End;
GO
-----------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_Save_tbPointsSetUp')
EXEC ('CREATE PROC dbo.sp_Bui_Save_tbPointsSetUp AS SELECT 1')
GO

ALTER procedure [dbo].[sp_Bui_Save_tbPointsSetUp] --->Dated 02/08/2012 by sensei
@ID as varchar(5),
@Amount as int,
@Points as int,
@Billing as bit,
@Cashier as bit,
@Prepaid as bit,
@AR as bit,
@Medicare as bit,
@Kidney as bit,
@ICU as bit,
@Generic as bit,
@CVU as bit,
@DR as bit,
@PICU as bit,
@Nursery as bit,
@Endoscopy as bit,
@HS as bit,
@NU as bit,
@Nursing as bit,
@CCU as bit,
@OR as bit,
@Pulmo as bit,
@ER as bit,
@OPD as bit,
@Hematology as bit
as
if exists(select * from build_file..tbPointsSetUp where ID=@ID and Amount=@Amount)


begin
update build_file..tbPointsSetUp
set
ID=@ID,
AMount=@Amount,
Points=@Points,
Billing=@Billing,
Cashier=@Cashier,
Prepaid=@Prepaid,
AR=@AR,
Medicare=@Medicare,
Kidney=@Kidney,
ICU=@ICU,
Generic=@Generic,
CVU=@CVU,
DR=@DR,
PICU=@PICU,
Nursery=@NUrsery,
Endoscopy=@Endoscopy,
HS=@HS,
NU=@NU,
Nursing=@Nursing,
CCU=@CCU,
[OR]=@OR,
Pulmo=@Pulmo,
ER=@ER,
OPD=@OPD,
Hematology=@Hematology
where ID=@ID and Amount=@Amount
end
else
begin
insert into build_file..tbPointsSetUp
(ID,Amount,Points,Billing,Cashier,Prepaid,AR,Medicare,Kidney,ICU,Generic,CVU,DR,PICU,Nursery,Endoscopy,HS,NU,Nursing,CCU,[OR],Pulmo,ER,OPD,Hematology)
values
(@ID,@Amount,@Points,@Billing,@Cashier,@Prepaid,@AR,@Medicare,@Kidney,@ICU,@Generic,@CVU,@DR,@PICU,@Nursery,@Endoscopy,@HS,@NU,@Nursing,@CCU,@OR,@Pulmo,@ER,@OPD,@Hematology)
end
GO
--------------------------------
USE [Build_File]
GO
/****** Object:  StoredProcedure [dbo].[sp_Bui_Search_Company]    Script Date: 08/03/2012 12:41:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'sp_Bui_Search_Company')
EXEC ('CREATE PROC dbo.sp_Bui_Search_Company AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[sp_Bui_Search_Company](@SearchCriteria as varchar(60), @type as varchar(1)) --->Dated 03/08/2012 by sensei
AS

If (@Type = '1')

Select '' as dumm, isnull(a.company,'') as Company, isnull(a.accountnum,'')as AccountNum, isnull(b. description,'') as Description, isnull(a.cardcode,'') as CardCode,
(case when a.status = 'A' then 'Active'
else 'Inactive' End) as status
from tbcocompany a left outer join tbcobusinessclass b
on a.class = b.code
order by a.company
else

If (@Type = '2')

Select isnull(a.accountnum,'') as AccountNum, isnull(a.company,'') as Company, isnull(a.housestreet,'') as HouseStreet, isnull(a.barangay,'') as Barangay, isnull(a.zipcode,'') as Zipcode, isnull(a.status,'') as Status,isnull(a.cardcode,'') as CardCode,
isnull(a.tin,'') as TIN, isnull(a.vat,'') as VAT, isnull(a.fax,'') as FAX, isnull(a.status,'') as Status, isnull(a.phone,'') as Phone,
(case when ltrim(rtrim(isnull(b.description,''))) = '' then '(NONE)'
else b.description End) as description,
(case when ltrim(rtrim(isnull(c.town,'') + ', ' + isnull(c.province,''))) = ',' then '(NONE)'
else isnull(c.town,'') + ', ' + isnull(c.province,'') End) as province, IsNull( a.Class, '' ) as Class ,
IsNull( a.ContactPerson, '' ) as ContactPerson, IsNull( a.Position, '' ) as Position, IsNull( a.Officer, '' ) as Officer,
isnull(CardCode, '') as CardCode, isnull(EmployeeID,'') as EmployeeID, isnull(a.CreditLimit, 0) as CreditLimit, CompanyDiscount,
moaStartDate, moaEndDate , moaNumber, comppassword, isnull(a.isEmployee,0) as isEmployee
from tbcocompany a left outer join tbcobusinessclass b
on a.class = b.code
left outer join tbcoaddress c
on a.zipcode = c.zipcode
where a.accountnum = @searchcriteria
else

If (@type = '3')

Select * from tbcoCompany where ltriM(rtrim(isnull(company,''))) = @SearchCriteria
else

If (@Type = '4')

Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, b.code,a.cardcode,
(case when a.status = 'A' then 'Active'
else 'Inactive' End) as status
from tbcocompany a left outer join tbcobusinessclass b
on a.class = b.code
order by b.description,a.company

else

If (@Type = '5')
Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code,isnull(a.cardcode,'') as Cardcode,
(case when a.status = 'A' then 'Active'
else 'Inactive' End) as status
from tbcocompany a left outer join tbcobusinessclass b
on a.class = b.code
order by a.accountnum

else

If (@Type = '6')
Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as AccountNum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code,isnull(a.cardcode,'') as Cardcode,
(case when a.status = 'A' then 'Active'
else 'Inactive' End) as status
from tbcocompany a left outer join tbcobusinessclass b
on a.class = b.code
order by a.company

else

If (@Type = '7')
Select '' as dumm, isnull(a.companyid,'') as CompanyID,isnull(a.company,'') as Company, isnull(a.accountnum,'') as Accountnum, isnull(b.code,'') + ' - ' + isnull(b.description,'') as description, isnull(b.code,'') as Code, isnull(a.cardcode,'') as CardCode,
(case when a.status = 'A' then 'Active'
else 'Inactive' End) as status
from tbcocompany a left outer join tbcobusinessclass b
on a.class = b.code
where b.description not like '%advance%'
order by a.company
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON
----------------------------