SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON

DECLARE @LATEST_DB_VER INT
SET @LATEST_DB_VER = '843145'

DECLARE @DATABASE_VERSION INT
SET @DATABASE_VERSION = (SELECT SYS_VALUE FROM [PYAppRole].[SYSOPTION] WHERE NAME LIKE 'DATABASE_VERSION')
IF (@LATEST_DB_VER > @DATABASE_VERSION)
    BEGIN
		DECLARE @MAX_LENGTH INT
		SET @MAX_LENGTH = (SELECT Top 1 character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'EMPS' and column_name LIKE 'NAME' ORDER BY character_maximum_length DESC )
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[EMPS] ALTER COLUMN NAME VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT Top 1 character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'EMPS' and column_name LIKE 'LAST_NAME' ORDER BY character_maximum_length DESC)
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[EMPS] ALTER COLUMN LAST_NAME VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'APPLICNT' and column_name LIKE 'LAST_NAME')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[APPLICNT] ALTER COLUMN LAST_NAME VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'APPLICNT' and column_name LIKE 'MIDDLE_NAME')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[APPLICNT] ALTER COLUMN MIDDLE_NAME VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'APPLICNT' and column_name LIKE 'FIRST_NAME')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[APPLICNT] ALTER COLUMN FIRST_NAME VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayAccrualSchedule' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayAccrualSchedule] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayAccrualStatus' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayAccrualStatus] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayDeds' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayDeds] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayEmpDeds' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayEmpDeds] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayHistoryOfChanges' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayHistoryOfChanges] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayPayHistory' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayPayHistory] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayScheduleRate' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayScheduleRate] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPaySpTaxes' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPaySpTaxes] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayTimeEntries' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayTimeEntries] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'NetPayUDData' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[NetPayUDData] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'QTRSORTREC' and column_name LIKE 'LAST_NAME')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[QTRSORTREC] ALTER COLUMN LAST_NAME VARCHAR (256) NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'SORTREC' and column_name LIKE 'NAME')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[SORTREC] ALTER COLUMN NAME VARCHAR (256) NOT NULL
			END

		SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'WebPayGrid' and column_name LIKE 'EmpName')
		IF @MAX_LENGTH < 256
			BEGIN
				ALTER TABLE [PYAppRole].[WebPayGrid] ALTER COLUMN EmpName VARCHAR (256) NULL
			END

    	SET @MAX_LENGTH = (SELECT character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'HRTABLES' and column_name LIKE 'HR_RIGHT')
		IF @MAX_LENGTH < 1500
			BEGIN
				ALTER TABLE [PYAppRole].[HRTABLES] ALTER COLUMN HR_RIGHT VARCHAR (1500) NULL
			END
        IF NOT EXISTS(select * from sys.columns where Name = N'PERIOD_STARTING_DATE' and object_id = OBJECT_ID(N'PYAppRole.PROLLS'))
            BEGIN
                alter table [PYAppRole].[PROLLS] ADD PERIOD_STARTING_DATE DATETIME NULL
            END
        IF NOT EXISTS(select * from sys.columns where Name = N'USE_CUSTOM_FOLDER' and object_id = OBJECT_ID(N'PYAppRole.PRCTLCE'))
            BEGIN
                alter table [PYAppRole].[PRCTLCE] ADD USE_CUSTOM_FOLDER VARCHAR(1) NULL
            END
        IF NOT EXISTS(select * from sys.columns where Name = N'CUSTOM_FOLDER_PATH' and object_id = OBJECT_ID(N'PYAppRole.PRCTLCE'))
            BEGIN
                alter table [PYAppRole].[PRCTLCE] ADD CUSTOM_FOLDER_PATH VARCHAR(512) NULL
            END

		/* NVPCID-412 - Asit - Created composite key for UDTIME table*/
		IF EXISTS(SELECT *  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME ='UDTIMEPK')
		BEGIN
			ALTER TABLE [PYAppRole].[UDTIME] 
				DROP CONSTRAINT UDTIMEPK
		END
		IF EXISTS(SELECT * FROM sys.indexes WHERE name='UDTIMEIX1')
		BEGIN		
			DROP INDEX [UDTIMEIX1] ON [PYAppRole].[UDTIME]
		END

		/* NVPCID-634 - Asit - handled DDL for Payroll column to become not null */
  		UPDATE [PYAppRole].[UDTIME] set PAYROLL = '000000' where PAYROLL IS NULL	
   		ALTER TABLE [PYAppRole].[UDTIME] ALTER COLUMN PAYROLL VARCHAR(6)  NOT NULL
   		EXEC('ALTER TABLE [PYAppRole].[UDTIME] ADD CONSTRAINT UDTIMEPK PRIMARY KEY (SEQC,PAYROLL)')
   		CREATE NONCLUSTERED INDEX [UDTIMEIX1] ON [PYAppRole].[UDTIME]
   		(
   			[PAYROLL] ASC,
   			[SEQC] ASC
   		) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

        /* NVPCID-748 - asit - Add script to include Disalbe_PIT check parameter if not present in sysoption table. */
        IF NOT EXISTS (SELECT Sys_Value FROM PYAppRole.SYSOPTION WHERE Name = 'Disable_PIT')
        BEGIN
            INSERT INTO PYAppRole.SYSOPTION (Name, Description, sys_value, usercustomizable)
            VALUES ('Disable_PIT','Disable Point In Time Reporting','TRUE','Y') 
        END

        /*NVPCID-10044 - Add new cobra report icons table entries*/
        IF NOT EXISTS (SELECT ICON_DESC FROM PYAppRole.ICONS WHERE ITEM = 'CPBAL')
        BEGIN
            INSERT INTO PYAppRole.ICONS (ITEM, REPORT_TYPE, DATA_TYPE, PERIOD, ICON_DESC, TITLE, LENGTH, EDIT_TYPE, DECIMALS, RECORD_TYPE)
            VALUES ('CPBAL', '*', 'E', NULL, 'Cobra Balance', 'Cobra@Bal', 10, 'N', 2, 'C')
        END
        IF NOT EXISTS (SELECT ICON_DESC FROM PYAppRole.ICONS WHERE ITEM = 'CLPAYD')
        BEGIN
            INSERT INTO PYAppRole.ICONS (ITEM, REPORT_TYPE, DATA_TYPE, PERIOD, ICON_DESC, TITLE, LENGTH, EDIT_TYPE, DECIMALS, RECORD_TYPE)
            VALUES ('CLPAYD', '*', 'E', NULL, 'Cobra Last Payment Date', 'Cobra@Last@PayDate', 10, 'D', 0, 'C')
        END

		/* NVPCID-704 - Asit - BEGIN - Data migration script from EmpHr table to Depend table on spouse information. */
        IF (@DATABASE_VERSION <= '843120') /* Don't change this version in future. The below script need to run only for the DB having version below 843119 */
        BEGIN
    		INSERT INTO PYAppRole.DEPEND(EMP,DEPSEQ,NAME,SORTPTR,STREET,STREET2,CITY,ZIP,RELATIONSHIP,PHONE,SSNO,BIRTHDATE,STUDENT,HANDICAPPED,SEX,CONCURRENCY_ID,SD01)
			select	hre.EMP,
					--CASE WHEN  (SELECT TOP 1 DEPSEQ FROM PYAppRole.DEPEND WHERE emp = hre.EMP ORDER BY DEPSEQ DESC) IS NULL THEN '0001' ELSE FORMAT(CAST((SELECT TOP 1 DEPSEQ FROM PYAppRole.DEPEND WHERE emp = hre.EMP ORDER BY DEPSEQ DESC) AS int) + 1, '0000') END ,
					CASE WHEN  (SELECT TOP 1 DEPSEQ FROM PYAppRole.DEPEND WHERE emp = hre.EMP ORDER BY DEPSEQ DESC) IS NULL THEN '0001' 
						ELSE REPLICATE('0', 4-len(CAST((SELECT TOP 1 DEPSEQ FROM PYAppRole.DEPEND WHERE emp = hre.EMP ORDER BY DEPSEQ DESC) AS int) + 1)) 
							+ convert(varchar, CAST((SELECT TOP 1 DEPSEQ FROM PYAppRole.DEPEND WHERE emp = hre.EMP ORDER BY DEPSEQ DESC) AS int) + 1) 
					END,
					hre.SPOUSE_NAME,
					0,
					hre.SPOUSE_STREET1,
					hre.SPOUSE_STREET2,
					hre.SPOUSE_CITY,
					hre.SPOUSE_ZIP,
					'W',
					hre.SPOUSE_HOME_PHONE,
					hre.SPOUSE_SSNO,
					hre.SPOUSE_BIRTHDATE,
					'',
					hre.HANDICAP_CODE,
					hre.SPOUSE_SEX,
					hre.CONCURRENCY_ID,
					hre.SD01 
			FROM pyapprole.HREMP hre
			WHERE LEN(hre.SPOUSE_NAME) > 0  and
				hre.EMP NOT IN (SELECT DISTINCT EMP FROM PYAppRole.DEPEND WHERE EMP = hre.EMP and RELATIONSHIP = 'W')
        END
		/* NVPCID-704 - Asit - END - Data migration script from EmpHr table to Depend table on spouse information. */
		/* Add a column to PYApprole.REPS table as REPORT_LOCATION */
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[REPS]') AND name = 'REPORT_LOCATION')
		BEGIN	
			ALTER TABLE PYAPPROLE.REPS ADD REPORT_LOCATION VARCHAR(300) NULL
		END
		/* NVPCID-758 - Asit - Start - Add below columns in respective tables*/
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'PRINT_PAYSTUB')
		BEGIN	
			ALTER TABLE PYAPPROLE.EMPS ADD PRINT_PAYSTUB VARCHAR(1) DEFAULT 'N'
		END

		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'DONT_ATTACH_PAYSTUB')
		BEGIN	
			ALTER TABLE PYAPPROLE.EMPS ADD DONT_ATTACH_PAYSTUB VARCHAR(1) DEFAULT 'N'
		END

		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[PRCTLCJ]') AND name = 'EMP_CHECK_OPTION')
		BEGIN	
			ALTER TABLE PYAPPROLE.PRCTLCJ ADD EMP_CHECK_OPTION VARCHAR(1) DEFAULT 'N'
		END
		/* NVPCID-758 - Asit - End - Add below columns in respective tables*/

		/* NVPCID-837 - Asit - Start - Add Column TAB_OUTPUT to REPS Table */
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[REPS]') AND name = 'TAB_OUTPUT')
		BEGIN	
			ALTER TABLE PYAPPROLE.REPS ADD TAB_OUTPUT VARCHAR(1) DEFAULT 'N'
		END
		/* NVPCID-837 - Asit - End - Add Column TAB_OUTPUT to REPS Table */

		/* NVPCID-912 - Asit - Start - Add Column IowaBEN to PRCTLCI Table */
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[PRCTLCI]') AND name = 'IowaBEN')
		BEGIN	
			ALTER TABLE PYAPPROLE.PRCTLCI ADD IowaBEN VARCHAR(8) DEFAULT 'N'
		END
		/* NVPCID-912 - Asit - End - Add Column IowaBEN to PRCTLCI Table */
		/* NVPCID-1005 - 4/14/2017 - Add Columns "VOIDED_BY" and "VOIDED_ON" to HIST table */
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[HIST]') AND name = 'VOIDED_BY')
		BEGIN	
			ALTER TABLE PYAPPROLE.HIST ADD VOIDED_BY VARCHAR(10)
		END
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[HIST]') AND name = 'VOIDED_ON')
		BEGIN	
			ALTER TABLE PYAPPROLE.HIST ADD VOIDED_ON DATETIME
		END
		/* NVPCID-1005 - 4/14/2017 - END - Add Columns "VOIDED_BY" and "VOIDED_ON" to HIST table */
		/* NVPCID-3787 - Add column sit2_gross to CHECKS table */
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'SIT2_GROSS' AND object_id = OBJECT_ID(N'PYAppRole.CHECKS'))
        BEGIN
           ALTER TABLE [PYAppRole].[CHECKS] ADD [SIT2_GROSS] [money] NULL
        END
		/* NVPCID-4759 - Add Columns EPFL_PERCENT AND EPFL_MAX TO SDI TABLE*/
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[SDI]') AND name = 'EPFL_PERCENT')
		BEGIN	
			ALTER TABLE [PYAPPROLE].[SDI] ADD [EPFL_PERCENT] [MONEY] NULL
		END
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[SDI]') AND name = 'EPFL_MAX')
		BEGIN	
			ALTER TABLE [PYAPPROLE].[SDI] ADD [EPFL_MAX] [MONEY] NULL
		END
		/* NVPCID-4759 - END - Add Columns EPFL_PERCENT AND EPFL_MAX TO SDI TABLE*/
		/* NVPCID-4052 - Add OR State Transit Tax*/
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'ORTransitTax' AND object_id = OBJECT_ID(N'PYAppRole.CHECKS'))
        BEGIN
           ALTER TABLE [PYAppRole].[CHECKS] ADD [ORTransitTax] [money] NULL
        END
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'ORTransitTax' AND object_id = OBJECT_ID(N'PYAppRole.EMPTAX'))
        BEGIN
           ALTER TABLE [PYAppRole].[EMPTAX] ADD [ORTransitTax] [money] NULL
        END
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'ORTransitTax' AND object_id = OBJECT_ID(N'PYAppRole.STEREC'))
        BEGIN
           ALTER TABLE [PYAppRole].[STEREC] ADD [ORTransitTax] [money] NULL
        END
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'ORTRN_EXEMPT' AND object_id = OBJECT_ID(N'PYAppRole.EMPS'))
        BEGIN
           ALTER TABLE [PYAppRole].[EMPS] ADD [ORTRN_EXEMPT] VARCHAR(1) NULL
        END
		/* END NVPCID-4052 - Add OR State Transit Tax*/
		/* NVPCID-7316 - Add OR State Transit Tax*/
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'ORTransitTax_Gross' AND object_id = OBJECT_ID(N'PYAppRole.CHECKS'))
        BEGIN
           ALTER TABLE [PYAppRole].[CHECKS] ADD [ORTransitTax_Gross] [money] NULL
        END
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'ORTransitTax_Gross' AND object_id = OBJECT_ID(N'PYAppRole.EMPTAX'))
        BEGIN
           ALTER TABLE [PYAppRole].[EMPTAX] ADD [ORTransitTax_Gross] [money] NULL
        END
		IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'ORTransitTax_Gross' AND object_id = OBJECT_ID(N'PYAppRole.STEREC'))
        BEGIN
           ALTER TABLE [PYAppRole].[STEREC] ADD [ORTransitTax_Gross] [money] NULL
        END
		/* END NVPCID-7316 - Add OR State Transit Tax*/
    	/* NVPCID-9825 - Add Columns MA_PML_EMPLOYER AND MA_PFL_EMPLOYER TO SDI TABLE*/
		IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[PYAPPROLE].[SDI]') AND name = 'MA_PML_EMPLOYER')
		BEGIN	
			ALTER TABLE [PYAPPROLE].[SDI] ADD [MA_PML_EMPLOYER] [MONEY] NULL
		END
		IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[PYAPPROLE].[SDI]') AND name = 'MA_PFL_EMPLOYER')
		BEGIN	
			ALTER TABLE [PYAPPROLE].[SDI] ADD [MA_PFL_EMPLOYER] [MONEY] NULL
		END
		/* NVPCID-9825 - END - Add Columns MA_PML_EMPLOYER AND MA_PFL_EMPLOYER TO SDI TABLE*/
		IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[PRCTLEC]') AND name = 'must_have_check')
		BEGIN	
			ALTER TABLE PYAPPROLE.PRCTLEC ADD must_have_check VARCHAR(8) DEFAULT 'N'
		END
	IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'MULT_JOBS')
	BEGIN
		ALTER TABLE PYAPPROLE.EMPS ADD MULT_JOBS VARCHAR(1) DEFAULT 'N'
	END
	
	IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'TOTAL_DEPENDENTS_AMT')
	BEGIN	
		ALTER TABLE PYAPPROLE.EMPS ADD TOTAL_DEPENDENTS_AMT [money] NULL
	END
	
	IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'OTHER_INCOME')
	BEGIN	
		ALTER TABLE PYAPPROLE.EMPS ADD OTHER_INCOME [money] NULL
	END
	
	IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'DEDUCTIONS')
	BEGIN	
		ALTER TABLE PYAPPROLE.EMPS ADD DEDUCTIONS [money] NULL
	END
	
	IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'EXTRA_WITHHOLDING_PP')
	BEGIN	
		ALTER TABLE PYAPPROLE.EMPS ADD EXTRA_WITHHOLDING_PP [money] NULL
	END
	
	IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'IS_2020W4_FILLED')
	BEGIN	
		ALTER TABLE PYAPPROLE.EMPS ADD IS_2020W4_FILLED VARCHAR(1) DEFAULT 'N'
	END
    IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[PYAPPROLE].[EMPS]') AND name = 'HOH_2020W4')
	BEGIN	
		ALTER TABLE PYAPPROLE.EMPS ADD HOH_2020W4 VARCHAR(1) DEFAULT 'N'
	END
    IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[PYAPPROLE].[PRCTLCE]') AND name = 'KEEP_CHECKS')
	BEGIN	
		ALTER TABLE PYAPPROLE.PRCTLCE ADD KEEP_CHECKS VARCHAR(1) DEFAULT 'N'
	END
    IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'DEFER_FICA' AND object_id = OBJECT_ID(N'PYAppRole.CHECKS'))
    BEGIN
       ALTER TABLE [PYAppRole].[CHECKS] ADD [DEFER_FICA] [money] NULL
    END
    IF NOT EXISTS(select * from sys.columns where Name = N'DEFER_EMPLOYEE_FICA' and object_id = OBJECT_ID(N'PYAppRole.PROLLS'))
    BEGIN
        alter table [PYAppRole].[PROLLS] ADD DEFER_EMPLOYEE_FICA VARCHAR(1) DEFAULT 'N'
    END
  	IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[PYAPPROLE].[PRCTLCJ]') AND name = 'DEFER_EMPLOYEE_FICA')
  	BEGIN	
  		ALTER TABLE PYAPPROLE.PRCTLCJ ADD DEFER_EMPLOYEE_FICA VARCHAR(1) DEFAULT 'N'
  	END
	IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = N'DEFER_FICA' AND object_id = OBJECT_ID(N'PYAppRole.EMPTAX'))
    BEGIN
       ALTER TABLE [PYAppRole].[EMPTAX] ADD [DEFER_FICA] [money] NULL
    END

    SET @MAX_LENGTH = (SELECT Top 1 character_maximum_length FROM information_schema.columns WHERE table_name LIKE 'UDTIME' and column_name LIKE 'SEQC' ORDER BY character_maximum_length DESC )
    IF @MAX_LENGTH < 9
	BEGIN
		 ALTER TABLE [PYAppRole].[UDTIME] DROP CONSTRAINT UDTIMEPK
		 ALTER TABLE [PYAppRole].[UDTIME] ALTER COLUMN SEQC VARCHAR (9) NOT NULL
		 ALTER TABLE [PYAppRole].[UDTIME] ADD CONSTRAINT UDTIMEPK PRIMARY KEY CLUSTERED 
			(
				[SEQC] ASC,
				[PAYROLL] ASC
			)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

		 update [PYAppRole].[UDTIME] set SEQC =  'U0' + RTRIM(SUBSTRING(SEQC, 2, 7))
	END

		UPDATE [PYAppRole].[SYSOPTION] SET SYS_VALUE = @LATEST_DB_VER WHERE NAME LIKE 'DATABASE_VERSION'
    END