alter table syspar3 add timeformat int;
alter table leavereqfile2 add cancel_reason1 TEXT;
alter table leavereqfile2 add cancel_reason2 TEXT;
alter table overtimereqfile2 add cancel_reason1 TEXT;
alter table overtimereqfile2 add cancel_reason2 TEXT;
alter table undertimereqfile2 add cancel_reason1 TEXT;
alter table undertimereqfile2 add cancel_reason2 TEXT;
alter table obreqfile2 add cancel_reason1 TEXT;
alter table obreqfile2 add cancel_reason2 TEXT;


alter table obfile1 add subdrycde varchar(100);
alter table overtimefile1 add subdrycde varchar(100);
alter table overtimefile1 add subdrycde varchar(100);
alter table leavesfile1 add subdrycde varchar(100);


alter table baselinesecurityfile add minnumspec int;
alter table baselinesecurityfile add pwlifetimeadmin int;
alter table baselinesecurityfile add disacc int;
alter table users add dtelastlog date;
update users set dtelastlog='2015-05-12';



//2015-03-09
alter table appnotificationfile add reqsterfullname varchar(150);
alter table users add nickname varchar(100);
alter table syspar3 add insubcde int;
alter table obreqfile2 alter notes text;

//2015-03-16
alter table leavesfile1 add viewstat TINYINT;
alter table overtimefile1 add viewstat TINYINT;
alter table undertimefile1 add viewstat TINYINT;
alter table obfile1 add viewstat TINYINT;
alter table leavesfile1 add finalviewstat TINYINT;
alter table overtimefile1 add finalviewstat TINYINT;
alter table undertimefile1 add finalviewstat TINYINT;
alter table obfile1 add finalviewstat TINYINT;

create table messageboardfile3 (recid BIGINT IDENTITY(1,1),empcode VARCHAR(30),msgid BIGINT)

//2015-03-17
alter table approverfile alter column appfullname VARCHAR(100);
alter table approverfile alter column fullname VARCHAR(100);
alter table approverfile2 alter column appfullname VARCHAR(100);
alter table approverfile2 alter column fullname VARCHAR(100);
alter table users add empno VARCHAR(30);

// 2015-03-18
create table approverfile3 (recid BIGINT IDENTITY(1,1),appempcode VARCHAR(100),appfullname VARCHAR(200),empcode VARCHAR(200),fullname VARCHAR(200),docnum VARCHAR(100),module VARCHAR(100),level VARCHAR(100),dteapproval date,status VARCHAR(100));
INSERT INTO [dbo].[menus] ([modcde], [menprg], [mencap], [mengrp], [mensub], [linenum], [mennum], [menidx], [menlvl], [menico], [aclkey], [usrtyp], [usrtyp2], [menheader], [mencol], [menrow]) VALUES
('multi',	'view_apprep.php',	'Approval Report',	'01',	'',	11.0000,	'99',	'01-89',	1.0000,	'',	'',	'approver',	'last approver',	NULL,	NULL,	NULL);
alter table syspar3 add rndpw int;
alter table syspar3 add defpw varchar(100);


//2015-03-23
INSERT INTO [dbo].[menus] ([modcde], [menprg], [mencap], [mengrp], [mensub], [linenum], [mennum], [menidx], [menlvl], [menico], [aclkey], [usrtyp], [usrtyp2], [menheader], [mencol], [menrow]) VALUES
('multi',	'view_actrep.php',	'User Activity Report',	'UTIL00',	'',	.0000,	'0',	'01-99-061',	2.0000,	'',	'',	'|admin|supervisor|',	'',	'Utilities',	1,	1);
alter table syspar3 add inclink int;
alter table users add profilepic varchar(200);//discovery
truncate table dashboardfile;//discovery
truncate table userdashboardfile;//discovery
INSERT INTO [dbo].[dashboardfile] ([notifcode], [notifcap], [notifprg], [notifnum]) VALUES
('bdaybrd',	'Birthday Celebrant',	'vw_birthday.php',	1);//discovery

update menus set mencap = 'For Approval' where mencap='Approval';//discovery


TKM

alter table authorizedobfile alter obnote text;


03-19-15
ALTER TABLE jobvacancyrequestfile ADD subdrycde varchar(100);

3-23-15

TKM
ALTER TABLE empdefleave ADD subdrycde varchar(100);

HRIS/ESS
ALTER TABLE employeeextension ADD acr_visa varchar(100);
ALTER TABLE employeeextension ADD acr_visatype varchar(100);
ALTER TABLE employeeextension ADD acr_visadate varchar(100);
ALTER TABLE employeeextension ADD acr_visavdate varchar(100);
ALTER TABLE employeeextension ADD acr_visaadd varchar(100);
ALTER TABLE employeefile ADD spousebday varchar(100);

ESS

create table postschedfile (recid bigint IDENTITY(1,1) PRIMARY KEY,
datefrom date,
dateto date,
postdte date,
posttime varchar(20));

create table ignoredrequestfile1 (recid bigint IDENTITY(1,1) PRIMARY KEY,
module VARCHAR(30),
docnum VARCHAR(30),
appempcode VARCHAR(30)
)


ESS

INSERT INTO [dbo].[menus] ([modcde], [menprg], [mencap], [mengrp], [mensub], [linenum], [mennum], [menidx], [menlvl], [menico], [aclkey], [usrtyp], [usrtyp2], [menheader], [mencol], [menrow]) VALUES
('multi',	'vw_postsched.php',	'Posting Schedule File',	'UTIL00',	'',	NULL,	NULL,	'01-99-09',	2.0000,	NULL,	NULL,	'admin|supervisor',	'NULL',	'Utilities',	1,	1);


TRUNCATE 
truncate table appnotificationfile;
truncate table reqnotificationfile;
truncate table leavesfile1;
truncate table leavereqfile2;
truncate table overtimefile1;
truncate table overtimereqfile2;
truncate table undertimefile1;
truncate table undertimereqfile2;
truncate table obfile1;
truncate table obreqfile2;
truncate table approverfile;
truncate table approverfile2;
truncate table approverfile3;

ESS
alter table leavetypefile add xorder varchar(10);


ESS
04-06-2015
ALTER TABLE users ALTER COLUMN usrname VARCHAR(100);

ESS
04-13-2015
alter table syspar3 add sendemail varchar(10);

ESS 04-16-2015
alter table leavesfile1 add canceltyp int;
alter table overtimefile1 add canceltyp int;
alter table undertimefile1 add canceltyp int;
alter table obfile1 add canceltyp int;
CREATE TABLE [appnotificationfile] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[empcode]varchar(50) NULL 
,[module]varchar(50) NULL 
,[content]varchar(255) NULL 
,[trndte]date NULL 
,[status]tinyint NULL 
,[submodule]varchar(50) NULL 
,[sublevel]tinyint NULL 
,[docnum]varchar(50) NULL 
,[hrstatus]tinyint NULL 
,[reqsterfullname]varchar(150) NULL 
);

create table emailmessagesetupfile
(recid bigint IDENTITY(1,1) PRIMARY KEY
,[etitle]varchar(200) NULL
,[ebody]text NULL
,[eregard]varchar(200) NULL
,[esender]varchar(200) NULL
,[eaddpos]varchar(200) NULL
,[esal]varchar(200) NULL);

	alter table obreqfile2 add obindate1 date;
	alter table obreqfile2 add oboutdate1 date;




04-20-2015
alter table syspar add reqlognum varchar(50);
alter table syspar add reqlognumlock varchar(50);


04-22-2015
alter table reqnotificationfile add reqstat varchar(50);
alter table reqnotificationfile add approver varchar(50);

04-27-2015
alter table useractivitylogfile add newval varchar(255);
alter table useractivitylogfile add oldval varchar(255);
alter table useractivitylogfile add fields varchar(255);
update baselinesecurityfile set holdunusedacc='';
update baselinesecurityfile set deldisacc='';
alter table users add usrsesstim varchar (255);
alter table users add usrsessid varchar (255);
alter table users add usripaddress varchar (255);

04-30-2015
alter table baselinesecurityfile add passwrdreuse bigint;
create table passwordhistoryfile (recid bigint IDENTITY(1,1) PRIMARY KEY , empcode varchar(200) NULL, usrpwd  varchar(255) NULL);


alter table users add actlock2 int;

create table passwordhistoryfile (recid bigint IDENTITY(1,1) PRIMARY KEY , empcode varchar(200) NULL, usrpwd  varchar(255) NULL);

create table schednotificationfile (recid bigint IDENTITY(1,1) PRIMARY KEY , empcode varchar(200) NULL, appempcode varchar(200) NULL,trndate varchar(255) NULL,effectvedte varchar(255) NULL,shiftcode varchar(255),oldshift varchar(255),is_view int,is_viewapp int);


05-19-2015

create table daytran2 (recid BIGINT IDENTITY(1,1), reqlognum varchar(50), deptcode varchar(15),locationid varchar(15), paydte DATE, empno varchar(15),ampm varchar(2), timereg varchar(10), updsta varchar(10), empcode varchar(30),subdrycde varchar(30),fullname varchar(100),timeio varchar(11), actdte DATE, in_out varchar(1), dtetim varchar(15), remarks varchar(100), linenum int, status tinyint)

alter table daytran2 add is_posted varchar(30);
alter table daytran2 add is_final varchar(30);
alter table daytran2 add approve varchar(30);
alter table daytran2 add apvdte date;
alter table daytran2 add cancel_reason2 varchar(200);

alter table daytran2 add is_last varchar(10);
alter table daytran2 add trndte date;
alter table reqnotificationfile add approver varchar(100);
alter table reqnotificationfile add reqstat varchar(100); 

tkm

alter table daytran add approver varchar(100)
alter table daytran add empcode varchar(100);
alter table daytran add subdrycde varchar(100);


INSERT INTO [dbo].[menus] ([modcde], [menprg], [mencap], [mengrp], [mensub], [linenum], [mennum], [menidx], [menlvl], [menico], [aclkey], [usrtyp], [usrtyp2], [menheader], [mencol], [menrow]) VALUES
('multi',	'view_attendance.php',	'View Employee Attendance',	'DTR00',	'',	.0000,	'0',	'01-10-100',	2.0000,	'',	'',	'last approver',	'last approver',	'View Schedule',	1,	1);



alter table baselinesecurityfile add onminlenpw int;
alter table baselinesecurityfile add onmaxlenpw int;
alter table baselinesecurityfile add onminnumlcl int;
alter table baselinesecurityfile add onminnumnc int;
alter table baselinesecurityfile add onminnumucl int;
alter table baselinesecurityfile add onminnumspec int;
alter table baselinesecurityfile add onpwlifetimeadmin int;
alter table baselinesecurityfile add onpwlifetime int;
alter table baselinesecurityfile add onfailedlog int;
alter table baselinesecurityfile add onautologout int;
alter table baselinesecurityfile add onrpcbe int;
alter table baselinesecurityfile add ondisacc int;
alter table baselinesecurityfile add onpasswrdreuse int;
alter table baselinesecurityfile add onallowname int;
alter table baselinesecurityfile add onallowrep int;
alter table baselinesecurityfile add onallowseq int;
alter table baselinesecurityfile add onduallog int;


create table usersubsidiaryfile (recid bigint IDENTITY(1,1) PRIMARY KEY , usrcde varchar(200) NULL,appcde varchar(200) NULL, subdrycde  varchar(255) NULL);


05-25-2015	

alter table expensesreqfile2 add cancel_reason2 varchar(100);
alter table expensesfile1 add iscancel varchar(50);
alter table expensesreqfile2 add empcode varchar(100);
alter table expensesfile1 add reqdteto date;
alter table expensesfile1 add reqdtefrm date;
alter table expensesfile1 add reqstr varchar(100);
alter table expensesreqfile2 add exprefnum varchar(100);
alter table expensesreqfile2 add exptinno varchar(100);
alter table expensesreqfile2 add expissuer varchar(100);
alter table expensesfile1 add is_last varchar(50);
alter table expensesreqfile2 add is_last varchar(50);
alter table expensesfile1 add is_posted varchar(50);
alter table expensesreqfile2 add is_posted varchar(50);
alter table expensesfile1 add cancel_reason2 varchar(100);
alter table expensesfile1 add finalviewstat varchar(50);
alter table expensesfile1 add apvhrs varchar(50);

05-28-2015

INSERT INTO [dbo].[moduledirfile] ([module], [submodule], [url], [sublevel]) VALUES
('APPROVAL',	'EXPENSES',	'v_exp_approval2.php',	2);

INSERT INTO [dbo].[moduledirfile] ([module], [submodule], [url], [sublevel]) VALUES
('REQUEST',	'EXPENSES',	'v_exp_transaction2.php',	1);

05-29-2015

INSERT INTO [dbo].[moduledirfile] ([module], [submodule], [url], [sublevel]) VALUES
('CANCELLATION',	'EXPENSES',	'v_exp_approval2.php',	2),
('CANCELLATION',	'LEAVE',	'v_leave_approval2.php',	2),
('CANCELLATION',	'UNDERTIME',	'v_ul_approval2.php',	2),
('CANCELLATION',	'OVERTIME',	'v_ot_approval2.php',	2),
('CANCELLATION',	'OFFICIAL BUSINESS',	'v_ob_approval2.php',	2);


alter table syspar add linktohr int;
alter table syspar add hrhost varchar(200);
alter table syspar add hrdb varchar(200);
alter table syspar add hruser varchar(200);
alter table syspar add hrpw varchar(200);


INSERT INTO [dbo].[menus] ([modcde], [menprg], [mencap], [mengrp], [mensub], [linenum], [mennum], [menidx], [menlvl], [menico], [aclkey], [usrtyp], [usrtyp2], [menheader], [mencol], [menrow]) VALUES
('multi',	'v_reqlog_approval2.php',	'Request Log (Final)',	'APR00',	'',	.0000,	'0',	'01-10-11',	2.0000,	'',	'',	'last approver',	'last approver',	'Approval',	1,	1);



2015-06-15

alter table syspar3 add rndpw int;
alter table syspar3 add defpw varchar(100);

2015-06-16
alter table leavesfile add mustuse int;

06-20-2015
alter table syspar add edonum varchar(50);
alter table syspar add edonumlock varchar(50);

create table edofile1 (recid bigint IDENTITY(1,1) PRIMARY KEY , empcode varchar(200) NULL,fullname varchar(200) NULL, subdrycde  varchar(255) NULL,trndte date,approve varchar(200) NULL,notes text,edodocnum varchar(50),noofdays int,is_final int,is_last int,viewstat int,finalviewstat int,cancel_reason2 text,canceltyp int,is_cancel int);

alter table syspar add cosdocnum varchar(50);
alter table syspar add cosdocnumlock varchar(50);

create table cosfile1 (recid bigint IDENTITY(1,1) PRIMARY KEY , empcode varchar(200) NULL,fullname varchar(200) NULL, subdrycde  varchar(255) NULL,trndte date,approve varchar(200) NULL,reqdtefrm date,reqdteto date,reqstr varchar(50),cosdocnum varchar(50),is_final int,is_last int,viewstat int,finalviewstat int,canceltyp int,is_cancel int,late int,is_posted int);

create table cosreqfile2 (recid bigint IDENTITY(1,1) PRIMARY KEY , empcode varchar(200) NULL,fullname varchar(200) NULL, subdrycde  varchar(255) NULL,trndte date,status varchar(200) NULL,cosdate date,cancel_reason1 text,cancel_reason2 text,shiftcode varchar(100),cday varchar(100),cosdocnum varchar(50),procdte	varchar(30),procdteto date,
procdtefrm date,notes text);


alter table postschedfile add is_posted int;

2015-07-22
alter table syspar add lwpbalmode int;


alter table daytran2 add tmp_status varchar(100);
alter table cosfile1 add tmp_status varchar(100);
alter table daytran2 add cancel_reason1 text;


2015 09 08 - adjustments
alter table syspar
add adjlvdocnumlock int null,
adjotdocnumlock int null,
adjrhdocnumlock int null;

alter table syspar
add 'adjlvdocnum' varchar(15) null,
'adjotdocnum' varchar(15) null,
'adjrhdocnum' varchar(15) null;

CREATE TABLE [adjleavereqfile2] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[leavedocnum]varchar(20) NULL 
,[leavedate]date NULL 
,[leavehours]decimal(18,2) NULL 
,[leavedays]decimal(18,2) NULL 
,[remarks]varchar(255) NULL 
,[lday]varchar(10) NULL 
,[status]varchar(20) NULL 
,[apvdte]date NULL 
,[apvhrs]decimal(18,2) NULL 
,[apvdays]decimal(18,2) NULL 
,[is_final]int NULL DEFAULT '0'
,[procdte]varchar(50) NULL 
,[procdtefrm]date NULL 
,[procdteto]date NULL 
,[trndte]date NULL 
,[empcode]varchar(30) NULL 
,[is_late]tinyint NULL 
,[is_advanced]int NULL DEFAULT '0'
,[is_posted]int NULL 
,[notes]text NULL 
,[tmp_apv_hrs]decimal(18,2) NULL 
,[tmp_apv_dys]decimal(18,2) NULL 
,[ampm]varchar(2) NULL 
,[leavetyp2]varchar(50) NULL 
,[subdrycde]varchar(100) NULL 
,[tmp_status]varchar(100) NULL 
,[cancel_reason1]text NULL 
,[cancel_reason2]text NULL 
);
/*END LINE*/
CREATE TABLE [adjleavesfile1] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[empcode]varchar(30) NULL DEFAULT ''
,[fullname]varchar(50) NULL DEFAULT ''
,[leavedtefrm]datetime NULL 
,[leavedteto]datetime NULL 
,[trndte]datetime NULL 
,[approve]varchar(30) NULL DEFAULT ''
,[leavereason]text NULL 
,[leavenote]text NULL 
,[leavedocnum]varchar(20) NULL DEFAULT ''
,[leavetype]varchar(20) NULL DEFAULT ''
,[dteapproved]datetime NULL 
,[leaveday]decimal(18,2) NULL 
,[paygroup]varchar(15) NULL DEFAULT ''
,[appempcode]varchar(30) NULL DEFAULT ''
,[comcde]varchar(50) NULL 
,[leavehrs]decimal(18,2) NULL 
,[is_posted]varchar(30) NULL 
,[procdte]varchar(30) NULL 
,[from]date NULL 
,[to]date NULL 
,[procdtefrm]date NULL 
,[procdteto]date NULL 
,[is_final]int NULL 
,[totalhrs]decimal(18,2) NULL 
,[totaldays]decimal(18,2) NULL 
,[reqdtefrm]date NULL 
,[reqdteto]date NULL 
,[reqstr]varchar(255) NULL 
,[apvhrs]decimal(18,2) NULL 
,[apvdays]decimal(18,2) NULL 
,[ampm]varchar(20) NULL 
,[leavetyp2]varchar(50) NULL 
,[iscancel]int NULL 
,[subdrycde]varchar(100) NULL 
,[is_last]int NULL 
,[viewstat]tinyint NULL 
,[finalviewstat]tinyint NULL 
,[canceltyp]int NULL 
);
/*END LINE*/
CREATE TABLE [adjleavesfile2] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[leavedocnum]varchar(20) NULL DEFAULT ''
,[appempcode]varchar(30) NULL DEFAULT ''
,[dteapproved]datetime NULL 
,[approve]varchar(30) NULL DEFAULT ''
,[last_approve]varchar(30) NULL DEFAULT ''
,[id]bigint NULL 
);
/*END LINE*/
CREATE TABLE [adjobfile1] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[adjreqhrdocnum]varchar(15) NULL DEFAULT ''
,[empcode]varchar(30) NULL DEFAULT ''
,[fullname]varchar(50) NULL DEFAULT ''
,[approve]varchar(30) NULL DEFAULT ''
,[reason]text NULL 
,[obin]varchar(15) NULL DEFAULT ''
,[obout]varchar(15) NULL DEFAULT ''
,[trndte]datetime NULL 
,[dteapproved]datetime NULL 
,[paygroup]varchar(15) NULL DEFAULT ''
,[totalhrs]decimal(18,2) NULL DEFAULT 0
,[client_name]varchar(150) NULL DEFAULT ''
,[client_add]varchar(255) NULL DEFAULT ''
,[telno]varchar(30) NULL DEFAULT ''
,[conper]varchar(150) NULL DEFAULT ''
,[reghrdate]datetime NULL 
,[notes]text NULL 
,[late]int NULL DEFAULT 0
,[comcde]varchar(15) NULL DEFAULT ''
,[is_posted]varchar(30) NULL 
,[procdte]varchar(30) NULL 
,[procdtefrm]date NULL 
,[procdteto]date NULL 
,[is_final]tinyint NULL DEFAULT '0'
,[reqdtefrm]date NULL 
,[reqdteto]date NULL 
,[reqstr]varchar(255) NULL 
,[reghrtype]varchar(50) NULL 
,[iscancel]int NULL 
,[subdrycde]varchar(100) NULL 
,[is_last]int NULL 
,[viewstat]tinyint NULL 
,[finalviewstat]tinyint NULL 
,[canceltyp]int NULL 
);
/*END LINE*/
CREATE TABLE [adjobfile2] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[obdocnum]varchar(30) NULL DEFAULT ''
,[appempcode]varchar(30) NULL DEFAULT ''
,[dteapproved]datetime NULL 
,[approve]varchar(30) NULL DEFAULT ''
,[last_approve]varchar(30) NULL DEFAULT ''
,[id]bigint NULL 
);
/*END LINE*/
CREATE TABLE [adjobreqfile2] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[outmiltime]varchar(20) NULL 
,[obdte]date NULL 
,[oboutmiltime3]varchar(20) NULL 
,[trndte]date NULL 
,[reason]varchar(255) NULL 
,[is_posted]tinyint NULL 
,[oboutmiltime4]varchar(20) NULL 
,[inampm]varchar(2) NULL 
,[oboutmiltime5]varchar(20) NULL 
,[oboutmiltime1]varchar(20) NULL 
,[telno]varchar(20) NULL 
,[oboutmiltime6]varchar(20) NULL 
,[apvdte]date NULL 
,[oboutmiltime2]varchar(20) NULL 
,[obinmiltime4]varchar(20) NULL 
,[is_final]int NULL 
,[outampm]varchar(2) NULL 
,[conper]varchar(50) NULL 
,[obinmiltime3]varchar(20) NULL 
,[obinmiltime5]varchar(20) NULL 
,[obdocnum]varchar(20) NULL 
,[obinmiltime2]varchar(20) NULL 
,[obinmiltime6]varchar(20) NULL 
,[is_advanced]tinyint NULL 
,[obout6]varchar(10) NULL 
,[obout1]varchar(10) NULL 
,[obinmiltime1]varchar(20) NULL 
,[obout3]varchar(10) NULL 
,[procdteto]date NULL 
,[inhour]varchar(2) NULL 
,[client_name]varchar(50) NULL 
,[apvdays]int NULL 
,[obout2]varchar(10) NULL 
,[procdtefrm]date NULL 
,[inmiltime]varchar(20) NULL 
,[obout5]varchar(10) NULL 
,[obout4]varchar(10) NULL 
,[outmin]varchar(2) NULL 
,[is_late]tinyint NULL 
,[obin4]varchar(10) NULL 
,[obin3]varchar(10) NULL 
,[outhour]varchar(2) NULL 
,[notes]text NULL 
,[obin2]varchar(10) NULL 
,[status]varchar(20) NULL 
,[cday]varchar(5) NULL 
,[client_add]varchar(70) NULL 
,[obin5]varchar(10) NULL 
,[empcode]varchar(30) NULL 
,[procdte]varchar(50) NULL 
,[obin6]varchar(1) NULL 
,[obin1]varchar(20) NULL 
,[inmin]varchar(2) NULL 
,[obtype]varchar(50) NULL 
,[obhours]decimal(18,2) NULL 
,[numbreak]int NULL 
,[tmp_status]varchar(100) NULL 
,[cancel_reason1]text NULL 
,[cancel_reason2]text NULL 
,[obindate1]date NULL 
,[oboutdate1]date NULL 
);
/*END LINE*/
CREATE TABLE [adjovertimefile1] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[adjreqhrdocnum]varchar(15) NULL DEFAULT ''
,[empcode]varchar(30) NULL DEFAULT ''
,[fullname]varchar(50) NULL DEFAULT ''
,[approve]varchar(30) NULL DEFAULT ''
,[reason]text NULL 
,[notes]text NULL 
,[otin]varchar(15) NULL DEFAULT ''
,[otout]varchar(15) NULL DEFAULT ''
,[dtefrm]datetime NULL 
,[dteto]datetime NULL 
,[trndte]datetime NULL 
,[dteapproved]datetime NULL 
,[paygroup]varchar(15) NULL DEFAULT ''
,[othrs]decimal(18,4) NULL DEFAULT 0
,[late]int NULL DEFAULT 0
,[comcde]varchar(15) NULL DEFAULT ''
,[apvhrs]float NULL 
,[apvdays]float NULL 
,[dapvhrs]float NULL 
,[dapvdays]float NULL 
,[is_manual]tinyint NULL 
,[man_empcode]varchar(30) NULL 
,[man_fullname]varchar(50) NULL 
,[is_posted]varchar(10) NULL 
,[procdte]varchar(30) NULL 
,[procdtefrm]date NULL 
,[procdteto]date NULL 
,[procfrm]date NULL 
,[procto]date NULL 
,[totalhrs]decimal(18,2) NULL 
,[is_final]tinyint NULL 
,[reqdtefrm]date NULL 
,[reqdteto]date NULL 
,[reqstr]varchar(50) NULL 
,[iscancel]int NULL 
,[is_last]int NULL 
,[subdrycde]varchar(100) NULL 
,[viewstat]tinyint NULL 
,[finalviewstat]tinyint NULL 
,[canceltyp]int NULL 
);
/*END LINE*/
CREATE TABLE [adjovertimefile2] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[otdocnum]varchar(30) NULL 
,[id]bigint NULL 
,[dteapproved]date NULL 
,[appempcode]varchar(30) NULL 
,[approve]varchar(30) NULL 
);
/*END LINE*/
CREATE TABLE [adjovertimereqfile2] (recid bigint IDENTITY(1,1) PRIMARY KEY
,[otdocnum]varchar(30) NULL DEFAULT ''
,[empcode]varchar(30) NULL DEFAULT ''
,[trndte]date NULL 
,[dtefrm]date NULL 
,[cday]varchar(3) NULL 
,[reason]varchar(255) NULL 
,[notes]text NULL 
,[inhour]varchar(2) NULL 
,[inmin]varchar(2) NULL 
,[inampm]varchar(2) NULL 
,[outhour]varchar(2) NULL 
,[outmin]varchar(2) NULL 
,[outampm]varchar(2) NULL 
,[appempcde]varchar(30) NULL 
,[apvdte]date NULL 
,[apvhrs]decimal(18,2) NULL 
,[status]varchar(30) NULL 
,[hours]decimal(18,2) NULL 
,[is_final]tinyint NULL DEFAULT '0'
,[inmiltime]varchar(10) NULL 
,[outmiltime]varchar(10) NULL 
,[is_manual]tinyint NULL 
,[man_empcode]varchar(30) NULL 
,[man_fullname]varchar(50) NULL 
,[procdte]varchar(30) NULL 
,[procdtefrm]date NULL 
,[procdteto]date NULL 
,[is_late]tinyint NULL DEFAULT '0'
,[is_advanced]tinyint NULL DEFAULT '0'
,[is_posted]tinyint NULL 
,[otdate]date NULL 
,[tmp_apv_hrs]decimal(18,2) NULL 
,[subdrycde]varchar(100) NULL 
,[tmp_status]varchar(100) NULL 
,[cancel_reason1]text NULL 
,[cancel_reason2]text NULL 
,[otindate1]date NULL 
,[otoutdate1]date NULL 
);
/*END LINE*/

alter table adjobfile1 add apvhrs decimal(18,2);

alter table syspar add noofsl int;
alter table syspar add slhrapv int;



alter table edofile1 add otdate date;


alter table employeefile add app_active int;
alter table employeefile add notifybyemail int;

create table approverstatusfile (recid bigint IDENTITY(1,1) PRIMARY KEY,
appstatcde int,
appstatdesc1 varchar(15),
appstatdesc2 varchar(5));

insert into approverstatusfile (appstatcde,appstatdesc1,appstatdesc2) values (1,'Active','Y');
insert into approverstatusfile (appstatcde,appstatdesc1,appstatdesc2) values (0,'Inactive','N');


alter table adjobfile1 add iscancel2 int;
alter table adjovertimefile1 add iscancel2 int;



/* 5-23-16 */
alter table leavereqfile2 add outmiltime varchar(10);
alter table leavereqfile2 add inmiltime varchar(10);
alter table leavereqfile2 add outhour varchar(2);
alter table leavereqfile2 add outmin varchar(2);
alter table leavereqfile2 add outampm varchar(2);
alter table leavereqfile2 add inhour varchar(2);
alter table leavereqfile2 add inmin varchar(2);
alter table leavereqfile2 add inampm varchar(2);

/* 5-25-16 */
alter table overtimereqfile2
add is_earlyot int

/* 12-2-16 */
alter table empreqfile add attachment varchar(400)

/* 10-24-2017 */
ALTER TABLE `edofile1`
ADD `otdate` varchar(200) COLLATE 'latin1_swedish_ci' NULL AFTER `approve`;
