USE [hr]
GO
/****** Object: Table [dbo].[HRM_DailyAttendance] Script Date: 22/Nov/2019 05:14:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_DailyAttendance](
[AttId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[EmpId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__EmpId__70D3A237] DEFAULT ((0)),
[InTime] [datetime] NULL,
[OutTime] [datetime] NULL,
[ReasonForLeave] [varchar](500) NULL CONSTRAINT [df_HRM_DailyAttendance_ReasonForLeave] DEFAULT (''),
[AttDate] [datetime] NULL,
[ShiftId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__Shift__71C7C670] DEFAULT ((0)),
[LeaveId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__Leave__72BBEAA9] DEFAULT ((0)),
[CatId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__CatId__73B00EE2] DEFAULT ((0)),
[DeptId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__DeptI__74A4331B] DEFAULT ((0)),
[Attendance] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily__Atten__75985754] DEFAULT ('P'),
[SerialNo] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__Seria__768C7B8D] DEFAULT ((0)),
[OfficialOut] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily__Offic__77809FC6] DEFAULT ('N'),
[Flag] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_DailyA__Flag__7874C3FF] DEFAULT ('N'),
[HalfDayLeave] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_HalfDayLeave] DEFAULT ('N'),
[OTRequiredForHoliday] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_OTRequiredForHoliday] DEFAULT ('N'),
[MisPunch] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_MisPunch] DEFAULT ('N'),
[Permisable] [numeric](18, 0) NOT NULL DEFAULT ((0)),
[Notes] [varchar](1000) NOT NULL DEFAULT (''),
[LeaveAppId] [numeric](9, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_LeaveAppId] DEFAULT ((0)),
[ConsiderForSalary] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_ConsiderForSalary] DEFAULT ('Y'),
[LeaveId2] [numeric](18, 0) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_LeaveId2] DEFAULT ((0)),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_DailyAtt_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_DailyAtt_EntryComputer] DEFAULT (''),
[ImportMachine] [varchar](500) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_ImportMachine] DEFAULT (''),
[BillNo] [varchar](100) NOT NULL CONSTRAINT [df_Hrm_DailyAttendance_BillNo] DEFAULT (''),
[CustId] [numeric](18, 0) NOT NULL CONSTRAINT [Df_Hrm_DailyAttendance_CustId] DEFAULT ((0)),
[LocationId] [numeric](18, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_LocationId] DEFAULT ((0)),
[othrs] [varchar](9) NULL,
[remarks] [varchar](1000) NULL,
[prodn] [numeric](12, 3) NULL,
[outtime1] [datetime] NULL,
[entrydate] [datetime] NULL,
[ent_down_date] [datetime] NULL,
[otentrydate] [datetime] NULL,
[otentredby] [numeric](10, 0) NULL,
[otaprovedby] [numeric](10, 0) NULL,
[otaproveddate] [datetime] NULL,
[otprocess] [char](1) NULL,
[Latedet] [char](3) NULL,
[LatePunch] [numeric](10, 3) NULL,
[earlypunch] [numeric](10, 3) NULL,
[F_S_half] [varchar](3) NULL,
[forceabsent] [char](1) NULL,
[ShiftStartTime] [datetime] NULL,
[ShiftEndTime] [datetime] NULL,
[LunchStartTime] [datetime] NULL,
[LunchEndTime] [datetime] NULL,
[CalStartTime] [datetime] NULL,
[CalEndTime] [datetime] NULL,
[FHFlag] [char](3) NULL,
[SHFlag] [char](3) NULL,
[FullFlag] [char](3) NULL,
[ChkShiftStartTime] [int] NULL,
[GraceTime] [int] NULL,
[HalfDayOnduty] [char](1) NULL,
[Onduty] [char](1) NULL,
[otentrycomputer] [varchar](40) NULL,
[newsystem] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[HRM_DailyAttendance] WITH CHECK ADD CONSTRAINT [df_halfdleave] CHECK (([halfdayleave]='Y' OR [halfdayleave]='N'))
GO
ALTER TABLE [dbo].[HRM_DailyAttendance] CHECK CONSTRAINT [df_halfdleave]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Attendance Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'AttId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'EmpId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reason For Leave' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'ReasonForLeave'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Attendance Date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'AttDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Shift Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'ShiftId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Leave Type Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'LeaveId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Category Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'CatId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Department Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'DeptId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Attendance Present/Absent' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'Attendance'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SerialNo for multiple regards' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'SerialNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Official Out Yes/No' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'OfficialOut'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag (Attendance Verification Yes/No)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'Flag'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OT Required For Holiday (Yes/No)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'OTRequiredForHoliday'
GO
USE [hr]
GO
/****** Object: Table [dbo].[HRM_DailyAttendance_Amend] Script Date: 22/Nov/2019 05:15:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_DailyAttendance_Amend](
[AttId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[EmpId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__EmpId__70D3A237] DEFAULT ((0)),
[InTime] [datetime] NULL,
[OutTime] [datetime] NULL,
[ReasonForLeave] [varchar](700) NULL CONSTRAINT [df_HRM_DailyAttendance_Amend_ReasonForLeave] DEFAULT (''),
[AttDate] [datetime] NULL,
[ShiftId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Shift__71C7C670] DEFAULT ((0)),
[LeaveId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Leave__72BBEAA9] DEFAULT ((0)),
[CatId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__CatId__73B00EE2] DEFAULT ((0)),
[DeptId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__DeptI__74A4331B] DEFAULT ((0)),
[Attendance] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Atten__75985754] DEFAULT ('P'),
[SerialNo] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Seria__768C7B8D] DEFAULT ((0)),
[OfficialOut] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Offic__77809FC6] DEFAULT ('N'),
[Flag] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_DailyA_Amend__Flag__7874C3FF] DEFAULT ('N'),
[HalfDayLeave] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_HalfDayLeave] DEFAULT ('N'),
[OTRequiredForHoliday] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_OTRequiredForHoliday] DEFAULT ('N'),
[MisPunch] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_MisPunch] DEFAULT ('N'),
[Permisable] [numeric](18, 0) NOT NULL DEFAULT ((0)),
[Notes] [varchar](1000) NOT NULL DEFAULT (''),
[LeaveAppId] [numeric](9, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_Amend_LeaveAppId] DEFAULT ((0)),
[ConsiderForSalary] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_ConsiderForSalary] DEFAULT ('Y'),
[LeaveId2] [numeric](18, 0) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_LeaveId2] DEFAULT ((0)),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_DailyAtt_Amend_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_DailyAtt_Amend_EntryComputer] DEFAULT (''),
[ImportMachine] [varchar](500) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_ImportMachine] DEFAULT (''),
[BillNo] [varchar](100) NOT NULL CONSTRAINT [df_Hrm_DailyAttendance_Amend_BillNo] DEFAULT (''),
[CustId] [numeric](18, 0) NOT NULL CONSTRAINT [Df_Hrm_DailyAttendance_Amend_CustId] DEFAULT ((0)),
[LocationId] [numeric](18, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_Amend_LocationId] DEFAULT ((0)),
[othrs] [varchar](9) NULL,
[remarks] [varchar](1000) NULL,
[prodn] [numeric](12, 3) NULL,
[outtime1] [datetime] NULL,
[entrydate] [datetime] NULL,
[ent_down_date] [datetime] NULL,
[otentrydate] [datetime] NULL,
[otentredby] [numeric](10, 0) NULL,
[otaprovedby] [numeric](10, 0) NULL,
[otaproveddate] [datetime] NULL,
[otprocess] [char](1) NULL,
[Latedet] [char](3) NULL,
[LatePunch] [numeric](10, 3) NULL,
[earlypunch] [numeric](10, 3) NULL,
[F_S_half] [varchar](3) NULL,
[forceabsent] [char](1) NULL,
[ShiftStartTime] [datetime] NULL,
[ShiftEndTime] [datetime] NULL,
[LunchStartTime] [datetime] NULL,
[LunchEndTime] [datetime] NULL,
[CalStartTime] [datetime] NULL,
[CalEndTime] [datetime] NULL,
[FHFlag] [char](3) NULL,
[SHFlag] [char](3) NULL,
[FullFlag] [char](3) NULL,
[ChkShiftStartTime] [int] NULL,
[GraceTime] [int] NULL,
[HalfDayOnduty] [char](1) NULL,
[Onduty] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[emp_personal] Script Date: 22/Nov/2019 05:15:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[emp_personal](
[empid] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[cardno] [varchar](15) NOT NULL,
[empcode] [varchar](15) NOT NULL,
[empname] [varchar](50) NOT NULL,
[father_name] [varchar](50) NULL,
[address] [varchar](600) NULL,
[cityid] [numeric](6, 0) NULL,
[pincode] [varchar](7) NULL,
[phone] [varchar](20) NULL,
[email] [varchar](50) NULL,
[blood_group] [varchar](10) NULL,
[d_o_b] [datetime] NULL,
[d_o_j] [datetime] NULL,
[gender] [varchar](20) NULL,
[m_status] [int] NULL,
[e_cont_name] [varchar](50) NULL,
[e_cont_phone] [varchar](20) NULL,
[e_cont_address] [varchar](600) NULL,
[photo_path] [varchar](90) NULL,
[photo] [varbinary](max) NULL,
[stateid] [numeric](6, 0) NULL,
[loginid] [numeric](6, 0) NULL,
[entrydate] [datetime] NULL,
[entrycomputer] [varchar](30) NULL,
[leave_approval] [char](1) NULL,
[locationid] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[emp_personal] ADD [resign] [char](1) NULL DEFAULT ('N')
ALTER TABLE [dbo].[emp_personal] ADD [resingdate] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [report_to] [numeric](10, 0) NULL DEFAULT ((0))
SET ANSI_PADDING ON
ALTER TABLE [dbo].[emp_personal] ADD [esiapplicable] [char](1) NULL DEFAULT ('N')
ALTER TABLE [dbo].[emp_personal] ADD [contractorid] [numeric](10, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [loginname] [varchar](40) NULL
ALTER TABLE [dbo].[emp_personal] ADD [password] [varchar](50) NULL
ALTER TABLE [dbo].[emp_personal] ADD [resign_type] [varchar](20) NULL
ALTER TABLE [dbo].[emp_personal] ADD [idproof] [varbinary](max) NULL
ALTER TABLE [dbo].[emp_personal] ADD [addrproof] [varbinary](max) NULL
ALTER TABLE [dbo].[emp_personal] ADD [ageproof] [varbinary](max) NULL
ALTER TABLE [dbo].[emp_personal] ADD [approvaldept] [varchar](30) NULL
ALTER TABLE [dbo].[emp_personal] ADD [accid] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [adv_approval] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [MulticompEmp] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [cod_approval] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [peraddress] [varchar](500) NULL
ALTER TABLE [dbo].[emp_personal] ADD [expdtreleave] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [actreldate] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [ManuvalPunch] [char](1) NULL DEFAULT ('N')
ALTER TABLE [dbo].[emp_personal] ADD [d_o_b_actual] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [ot_app] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [esiapp] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [pfapp] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [qualification] [varchar](100) NULL
ALTER TABLE [dbo].[emp_personal] ADD [preexp] [numeric](12, 4) NULL
ALTER TABLE [dbo].[emp_personal] ADD [appraiser] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [rev_officer] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [promo_year] [numeric](5, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [w_off] [varchar](20) NULL
ALTER TABLE [dbo].[emp_personal] ADD [mop_coordinator] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [mop_coord] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [power_user] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [webuser] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [l_break_date] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [mop_req] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [resign_enteredby] [numeric](12, 0) NULL
CONSTRAINT [PK_emp_personal] PRIMARY KEY CLUSTERED
(
[cardno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[emp_employementdet] Script Date: 22/Nov/2019 05:15:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[emp_employementdet](
[empid] [numeric](10, 0) NOT NULL,
[pfno] [varchar](30) NULL,
[esino] [varchar](30) NULL,
[bank_accno] [varchar](40) NULL,
[bank_name] [varchar](80) NULL,
[panno] [varchar](20) NULL,
[aadharno] [varchar](20) NULL,
[voter_id] [varchar](20) NULL,
[drive_licence] [varchar](20) NULL,
[licence_validity] [datetime] NULL,
[loginname] [varchar](30) NULL,
[login_password] [varchar](30) NULL,
[deptid] [numeric](7, 0) NULL,
[catid] [numeric](7, 0) NULL,
[shiftrotationpattern] [varchar](20) NULL,
[entrydate] [datetime] NULL,
[complementno] [varchar](30) NULL,
[uanno] [varchar](40) NULL,
[deptchangeid] [numeric](12, 0) NULL,
[deptchangeon] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[scalemaster] Script Date: 22/Nov/2019 05:16:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[scalemaster](
[id] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[empid] [numeric](6, 0) NOT NULL,
[deptid] [numeric](6, 0) NOT NULL,
[catid] [numeric](6, 0) NOT NULL,
[desigid] [numeric](6, 0) NOT NULL,
[adid] [numeric](6, 0) NOT NULL,
[amount] [numeric](12, 2) NOT NULL,
[inc_date] [datetime] NOT NULL,
[employer_amt] [numeric](12, 2) NULL DEFAULT ((0)),
[entrydate] [datetime] NULL,
[entrycomputer] [varchar](40) NULL,
[loginid] [numeric](10, 0) NULL,
[active_scale] [char](1) NULL,
[rval] [int] NULL,
CONSTRAINT [PK_scale] PRIMARY KEY CLUSTERED
(
[empid] ASC,
[inc_date] ASC,
[adid] ASC,
[catid] ASC,
[amount] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[scalemaster] WITH CHECK ADD FOREIGN KEY([catid])
REFERENCES [dbo].[hrm_category] ([catid])
GO
ALTER TABLE [dbo].[scalemaster] WITH CHECK ADD FOREIGN KEY([deptid])
REFERENCES [dbo].[department] ([deptid])
GO
ALTER TABLE [dbo].[scalemaster] WITH CHECK ADD FOREIGN KEY([adid])
REFERENCES [dbo].[hrm_allowancededuction] ([adid])
GO
USE [hr]
GO
/****** Object: Table [dbo].[Designation] Script Date: 22/Nov/2019 05:16:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designation](
[desigid] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[designation] [varchar](40) NOT NULL,
[catid] [numeric](6, 0) NOT NULL,
[entryempid] [numeric](6, 0) NULL,
[entrycomputer] [varchar](30) NULL,
[entrydatetime] [datetime] NULL,
[ltaapplicable] [char](1) NULL,
[latepunchallow] [char](1) NULL DEFAULT ('N'),
[DesigGroup] [numeric](1, 0) NULL DEFAULT ((1)),
[Desig_grp] [int] NULL,
[slab_desig] [int] NULL,
[staff_worker] [char](1) NULL,
[edu_allow] [numeric](12, 2) NULL,
[sodexo] [numeric](12, 0) NULL,
[desig_slno] [int] NULL,
[Grade] [int] NULL,
PRIMARY KEY CLUSTERED
(
[desigid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Designation] WITH CHECK ADD FOREIGN KEY([catid])
REFERENCES [dbo].[hrm_category] ([catid])
GO
ALTER TABLE [dbo].[Designation] WITH CHECK ADD CHECK (([designation]<>''))
GO
USE [hr]
GO
/****** Object: Table [dbo].[Hrm_ShiftRotation] Script Date: 22/Nov/2019 05:17:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hrm_ShiftRotation](
[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[EmpId] [numeric](18, 0) NOT NULL,
[ShiftId] [numeric](18, 0) NOT NULL,
[ChangeDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[ShiftRotationPeriod] [numeric](18, 0) NOT NULL,
[CatId] [numeric](18, 0) NOT NULL,
[DeptId] [numeric](18, 0) NOT NULL,
[StartDate] [datetime] NULL,
[loginid] [numeric](12, 0) NULL,
[EntryComputer] [varchar](30) NULL,
[entrydate] [datetime] NULL,
[chflag] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[HRM_Shift] Script Date: 22/Nov/2019 05:17:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_Shift](
[ShiftId] [int] IDENTITY(1,1) NOT NULL,
[Shift] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[Stime] [datetime] NULL,
[ETime] [datetime] NULL,
[LunchStart] [datetime] NULL,
[LunchEnd] [datetime] NULL,
[NextDay] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Shift__NextD__3E131840] DEFAULT ('N'),
[NightShift] [varchar](10) NOT NULL CONSTRAINT [DF_hrm_HRM_shift] DEFAULT ('N'),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Shift_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_Shift_EntryComputer] DEFAULT (''),
[active] [varchar](2) NULL,
[locationid] [int] NULL,
[entrydate] [datetime] NULL,
[companyid] [numeric](2, 0) NULL,
[previousday] [char](1) NULL,
[shiftname] [varchar](20) NULL,
CONSTRAINT [PK_HRM_Shift_ShiftID] PRIMARY KEY CLUSTERED
(
[ShiftId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_leavemaster] Script Date: 22/Nov/2019 05:18:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_leavemaster](
[id] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[appno] [varchar](30) NOT NULL,
[appdate] [datetime] NULL,
[empid] [numeric](10, 0) NOT NULL,
[approvalby] [numeric](10, 0) NOT NULL,
[elbal] [numeric](10, 2) NULL,
[leave_from] [datetime] NULL,
[leave_to] [datetime] NULL,
[leavedays] [numeric](12, 2) NULL,
[reason] [varchar](500) NULL,
[entrydatetime] [datetime] NULL,
[logid] [numeric](10, 0) NULL,
[entrycomputer] [varchar](30) NULL,
[approveddate] [datetime] NULL,
[leavetype] [numeric](10, 0) NULL,
[adv_amount] [numeric](12, 2) NULL,
[returnon] [datetime] NULL,
[approved] [char](1) NULL,
[adv_approved_amt] [numeric](12, 2) NULL,
[adv_approved_on] [datetime] NULL,
[adv_approvedby] [numeric](10, 0) NULL,
[adv_approvalby] [numeric](10, 0) NULL,
[adv_amtpaid] [numeric](12, 2) NULL,
PRIMARY KEY CLUSTERED
(
[appno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_leavedet] Script Date: 22/Nov/2019 05:18:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_leavedet](
[id] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[tranid] [numeric](10, 0) NOT NULL,
[appno] [varchar](30) NULL,
[leavedate] [datetime] NULL,
[halfday] [char](2) NULL,
[approved] [char](1) NULL,
[approveddate] [datetime] NULL,
[approval_computer] [varchar](50) NULL,
[approval_login] [numeric](12, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[hrm_leavedet] WITH CHECK ADD CONSTRAINT [CK__hrm_leave__halfd__6AEFE058] CHECK (([halfday]='FH' OR [halfday]='SH' OR [halfday]='F'))
GO
ALTER TABLE [dbo].[hrm_leavedet] CHECK CONSTRAINT [CK__hrm_leave__halfd__6AEFE058]
GO
USE [hr]
GO
/****** Object: Table [dbo].[HRM_LeaveType] Script Date: 22/Nov/2019 05:19:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_LeaveType](
[LeaveId] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LeaveType] [varchar](50) NOT NULL CONSTRAINT [DF_HRM_LeaveType_LeaveType] DEFAULT (' '),
[Description] [varchar](50) NOT NULL CONSTRAINT [DF_HRM_LeaveType_Description] DEFAULT (' '),
[Paid] [varchar](50) NOT NULL CONSTRAINT [DF__HRM_LeaveT__Paid__7A5D0C71] DEFAULT ('Y'),
[LeaveDays] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Leave__Leave__7C4554E3] DEFAULT ((0)),
[SlNo] [numeric](9, 0) NOT NULL CONSTRAINT [DF_HRM_LeaveType_SlNo] DEFAULT ((0)),
[ExcessLeaveAllowed] [varchar](3) NOT NULL DEFAULT ('N'),
[MaxLimitToCarryforward] [numeric](5, 2) NOT NULL CONSTRAINT [Df_HRM_LeaveTypes_MaxLimitToCarryforward] DEFAULT ((0)),
[MaxLimitReq] [char](1) NOT NULL CONSTRAINT [Df_HRM_LeaveTypes_MaxLimitReq] DEFAULT ('N'),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_LeaveType_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_LeaveType_EntryComputer] DEFAULT (''),
[LeaveEncashment] [varchar](1) NOT NULL CONSTRAINT [df_HRM_LeaveType_LeaveEncashment] DEFAULT ('N'),
[entrydate] [datetime] NULL,
[disppayroll] [char](1) NULL,
[staff_worker] [char](1) NULL,
CONSTRAINT [pk_hrm_leavetype_LeaveId] PRIMARY KEY CLUSTERED
(
[LeaveId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Leave Type Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'LeaveId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Leave Type' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'LeaveType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Paid Leave (Yes/No)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'Paid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'No of Leave days ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'LeaveDays'
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_weekoff] Script Date: 22/Nov/2019 05:19:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_weekoff](
[id] [numeric](12, 0) IDENTITY(1,1) NOT NULL,
[empid] [numeric](10, 0) NULL,
[catid] [numeric](10, 0) NULL,
[week_off] [varchar](15) NULL,
[eff_from] [datetime] NULL,
[eff_to] [datetime] NULL,
[entrydate] [datetime] NULL,
[entrycomputer] [varchar](40) NULL,
[loginid] [numeric](12, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_weeklyoff] Script Date: 22/Nov/2019 05:19:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_weeklyoff](
[LDate] [datetime] NULL,
[LDesc] [varchar](50) NOT NULL,
[Year] [numeric](18, 0) NOT NULL,
[Day] [varchar](10) NOT NULL,
[DateChange] [datetime] NULL,
[EntryEmpId] [numeric](18, 0) NOT NULL,
[EntryComputer] [varchar](100) NOT NULL,
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[LocationId] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [FinancialYear] [varchar](25) NOT NULL
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [leaveid] [numeric](10, 0) NULL
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [empid] [numeric](10, 0) NULL
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [catid] [numeric](10, 0) NULL
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[Leave] Script Date: 22/Nov/2019 05:20:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Leave](
[LDate] [datetime] NULL,
[LDesc] [varchar](50) NOT NULL CONSTRAINT [DF_Leave_LDesc] DEFAULT (' '),
[Year] [numeric](18, 0) NOT NULL CONSTRAINT [DF__Leave__Year__5FFE1BF0] DEFAULT ((0)),
[Day] [varchar](10) NOT NULL CONSTRAINT [DF_Leave_Day] DEFAULT (' '),
[DateChange] [datetime] NULL,
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Leave_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_Leave_EntryComputer] DEFAULT (''),
[ID] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LocationId] [numeric](18, 0) NOT NULL CONSTRAINT [df_Leave_LocationId] DEFAULT ('1')
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Leave] ADD [FinancialYear] [varchar](25) NOT NULL CONSTRAINT [df_Leave_FinancialYear] DEFAULT ('')
ALTER TABLE [dbo].[Leave] ADD [leaveid] [numeric](10, 0) NULL
CONSTRAINT [pk_leave_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [sandfitsetime]
GO
/****** Object: Table [dbo].[DeviceLogs] Script Date: 22/Nov/2019 05:21:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DeviceLogs](
[DeviceLogId] [int] IDENTITY(1,1) NOT NULL,
[DownloadDate] [datetime] NULL,
[DeviceId] [int] NOT NULL,
[UserId] [nvarchar](50) NOT NULL,
[LogDate] [datetime] NOT NULL,
[Direction] [nvarchar](255) NULL,
[AttDirection] [nvarchar](255) NULL,
[C1] [nvarchar](255) NULL,
[C2] [nvarchar](255) NULL,
[C3] [nvarchar](255) NULL,
[C4] [nvarchar](255) NULL,
[C5] [nvarchar](255) NULL,
[C6] [nvarchar](255) NULL,
[C7] [nvarchar](255) NULL,
[WorkCode] [nvarchar](255) NULL,
[ChkData] [char](1) NULL,
[ChkData1] [char](1) NULL,
CONSTRAINT [PK_DeviceLogs] PRIMARY KEY CLUSTERED
(
[DeviceLogId] ASC,
[UserId] ASC,
[LogDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [sandfitsetime]
GO
/****** Object: Table [dbo].[Devices] Script Date: 22/Nov/2019 05:21:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Devices](
[DeviceId] [int] IDENTITY(1,1) NOT NULL,
[DeviceFName] [nvarchar](255) NOT NULL,
[DeviceSName] [nvarchar](255) NOT NULL,
[DeviceDirection] [nvarchar](255) NULL,
[SerialNumber] [nvarchar](255) NULL,
[ConnectionType] [nvarchar](255) NULL,
[IpAddress] [nvarchar](255) NULL,
[BaudRate] [nvarchar](255) NULL,
[CommKey] [nvarchar](255) NOT NULL CONSTRAINT [DF_Devices_CommKey] DEFAULT (0),
[ComPort] [nvarchar](255) NULL,
[LastLogDownloadDate] [datetime] NULL CONSTRAINT [DF_Devices_LastLogDownloadDate] DEFAULT ('1900-01-01 00:00'),
[C1] [nvarchar](255) NULL,
[C2] [nvarchar](255) NULL,
[C3] [nvarchar](255) NULL,
[C4] [nvarchar](255) NULL,
[C5] [nvarchar](255) NULL,
[C6] [nvarchar](255) NULL,
[C7] [nvarchar](255) NULL,
[TransactionStamp] [nvarchar](50) NULL DEFAULT ('0'),
[LastPing] [datetime] NULL,
[DeviceType] [nvarchar](255) NULL,
[OpStamp] [nvarchar](255) NULL,
[DownLoadType] [int] NULL,
[Timezone] [nvarchar](50) NULL,
[DeviceLocation] [nvarchar](50) NULL,
[TimeOut] [nvarchar](50) NULL,
CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED
(
[DeviceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [sandfitsetime]
GO
/****** Object: Table [dbo].[DevicesStatus] Script Date: 22/Nov/2019 05:23:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DevicesStatus](
[DeviceStatusId] [int] IDENTITY(1,1) NOT NULL,
[DeviceId] [int] NULL,
[DeviceStatus] [nvarchar](255) NULL,
[LastDeviceStatusOn] [datetime] NULL,
CONSTRAINT [DevicesStatus_PK] PRIMARY KEY CLUSTERED
(
[DeviceStatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [production]
GO
/****** Object: Table [dbo].[HR_Controls] Script Date: 22/Nov/2019 05:29:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_Controls](
[TranMasId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Locationid] [int] NULL,
[Yrmon] [int] NULL,
[CategoryId] [numeric](10, 0) NULL,
[WeeklyOff] [varchar](50) NULL,
[GraceTimeMin] [numeric](10, 0) NULL,
[GraceTimeCount] [numeric](10, 0) NULL,
[OTMaxHrs] [numeric](10, 0) NULL,
[OTMinHrs] [numeric](10, 0) NULL,
[RegPermissionMaxMinutes] [numeric](10, 0) NULL,
[RegPermissionMinMinutes] [numeric](10, 0) NULL,
[SplPermissionMaxMinutes] [numeric](10, 0) NULL,
[SplPermissionCount] [numeric](10, 0) NULL,
[ChkShiftStTime] [numeric](10, 0) NULL,
[EffFrom] [datetime] NULL,
[Active] [char](1) NULL,
[CreatedBy] [varchar](50) NULL,
[CreatedDate] [datetime] NULL,
[CreatedSystem] [varchar](100) NULL,
[DesigGroup] [numeric](1, 0) NULL,
[LatePermissionCount] [int] NULL,
[EarlyOutPunch] [numeric](10, 0) NULL DEFAULT ((0)),
[RegPermissionTotMinutes] [numeric](10, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [production]
GO
/****** Object: Table [dbo].[HR_ShiftChangeRequest] Script Date: 22/Nov/2019 05:32:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_ShiftChangeRequest](
[TranId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[LocationId] [int] NULL,
[EntryDate] [date] NULL,
[DeptId] [int] NULL,
[CatId] [int] NULL,
[EmpId] [int] NULL,
[ShiftId] [int] NULL,
[ShiftStartDate] [date] NULL,
[ShiftEndDate] [date] NULL,
[CreatedBy] [varchar](200) NULL,
[CreatedDate] [datetime] NULL,
[CreatedSystem] [varchar](200) NULL,
[Status] [char](1) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [production]
GO
/****** Object: Table [dbo].[Hr_TwelveHrShiftCtrlHrs] Script Date: 22/Nov/2019 05:32:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hr_TwelveHrShiftCtrlHrs](
[TranId] [int] IDENTITY(1,1) NOT NULL,
[LocationId] [int] NULL,
[ShiftEndTimeMaxMinutes] [int] NULL
) ON [PRIMARY]
GO
USE [production]
GO
/****** Object: Table [dbo].[HR_Permission] Script Date: 22/Nov/2019 05:33:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_Permission](
[TranId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[LocationId] [int] NULL,
[Yrmon] [int] NULL,
[EntryDate] [date] NULL,
[EmpId] [numeric](10, 0) NULL,
[PermissionHrsMin] [numeric](10, 0) NULL,
[FromTime] [datetime] NULL,
[ToTime] [datetime] NULL,
[Reason] [varchar](250) NULL,
[Approved] [char](1) NULL,
[CreatedBy] [varchar](100) NULL,
[CreatedDate] [datetime] NULL,
[CreatedSystem] [varchar](100) NULL,
[ApprovedBy] [varchar](100) NULL,
[ApprovedDate] [datetime] NULL,
[ApprovedSystem] [varchar](100) NULL,
[PermissionType] [char](2) NULL,
[PermissionLevel] [char](2) NULL,
[Download] [char](1) NULL DEFAULT ('N'),
[UnApprovalRemarks] [varchar](150) NULL,
CONSTRAINT [UC_HR_Permission] UNIQUE NONCLUSTERED
(
[EmpId] ASC,
[FromTime] ASC,
[Approved] ASC,
[PermissionType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
GO
/****** Object: Table [dbo].[HRM_DailyAttendance] Script Date: 22/Nov/2019 05:14:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_DailyAttendance](
[AttId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[EmpId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__EmpId__70D3A237] DEFAULT ((0)),
[InTime] [datetime] NULL,
[OutTime] [datetime] NULL,
[ReasonForLeave] [varchar](500) NULL CONSTRAINT [df_HRM_DailyAttendance_ReasonForLeave] DEFAULT (''),
[AttDate] [datetime] NULL,
[ShiftId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__Shift__71C7C670] DEFAULT ((0)),
[LeaveId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__Leave__72BBEAA9] DEFAULT ((0)),
[CatId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__CatId__73B00EE2] DEFAULT ((0)),
[DeptId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__DeptI__74A4331B] DEFAULT ((0)),
[Attendance] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily__Atten__75985754] DEFAULT ('P'),
[SerialNo] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily__Seria__768C7B8D] DEFAULT ((0)),
[OfficialOut] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily__Offic__77809FC6] DEFAULT ('N'),
[Flag] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_DailyA__Flag__7874C3FF] DEFAULT ('N'),
[HalfDayLeave] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_HalfDayLeave] DEFAULT ('N'),
[OTRequiredForHoliday] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_OTRequiredForHoliday] DEFAULT ('N'),
[MisPunch] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_MisPunch] DEFAULT ('N'),
[Permisable] [numeric](18, 0) NOT NULL DEFAULT ((0)),
[Notes] [varchar](1000) NOT NULL DEFAULT (''),
[LeaveAppId] [numeric](9, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_LeaveAppId] DEFAULT ((0)),
[ConsiderForSalary] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_ConsiderForSalary] DEFAULT ('Y'),
[LeaveId2] [numeric](18, 0) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_LeaveId2] DEFAULT ((0)),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_DailyAtt_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_DailyAtt_EntryComputer] DEFAULT (''),
[ImportMachine] [varchar](500) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_ImportMachine] DEFAULT (''),
[BillNo] [varchar](100) NOT NULL CONSTRAINT [df_Hrm_DailyAttendance_BillNo] DEFAULT (''),
[CustId] [numeric](18, 0) NOT NULL CONSTRAINT [Df_Hrm_DailyAttendance_CustId] DEFAULT ((0)),
[LocationId] [numeric](18, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_LocationId] DEFAULT ((0)),
[othrs] [varchar](9) NULL,
[remarks] [varchar](1000) NULL,
[prodn] [numeric](12, 3) NULL,
[outtime1] [datetime] NULL,
[entrydate] [datetime] NULL,
[ent_down_date] [datetime] NULL,
[otentrydate] [datetime] NULL,
[otentredby] [numeric](10, 0) NULL,
[otaprovedby] [numeric](10, 0) NULL,
[otaproveddate] [datetime] NULL,
[otprocess] [char](1) NULL,
[Latedet] [char](3) NULL,
[LatePunch] [numeric](10, 3) NULL,
[earlypunch] [numeric](10, 3) NULL,
[F_S_half] [varchar](3) NULL,
[forceabsent] [char](1) NULL,
[ShiftStartTime] [datetime] NULL,
[ShiftEndTime] [datetime] NULL,
[LunchStartTime] [datetime] NULL,
[LunchEndTime] [datetime] NULL,
[CalStartTime] [datetime] NULL,
[CalEndTime] [datetime] NULL,
[FHFlag] [char](3) NULL,
[SHFlag] [char](3) NULL,
[FullFlag] [char](3) NULL,
[ChkShiftStartTime] [int] NULL,
[GraceTime] [int] NULL,
[HalfDayOnduty] [char](1) NULL,
[Onduty] [char](1) NULL,
[otentrycomputer] [varchar](40) NULL,
[newsystem] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[HRM_DailyAttendance] WITH CHECK ADD CONSTRAINT [df_halfdleave] CHECK (([halfdayleave]='Y' OR [halfdayleave]='N'))
GO
ALTER TABLE [dbo].[HRM_DailyAttendance] CHECK CONSTRAINT [df_halfdleave]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Attendance Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'AttId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'EmpId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reason For Leave' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'ReasonForLeave'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Attendance Date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'AttDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Shift Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'ShiftId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Leave Type Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'LeaveId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Category Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'CatId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Department Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'DeptId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Attendance Present/Absent' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'Attendance'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SerialNo for multiple regards' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'SerialNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Official Out Yes/No' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'OfficialOut'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag (Attendance Verification Yes/No)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'Flag'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OT Required For Holiday (Yes/No)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_DailyAttendance', @level2type=N'COLUMN',@level2name=N'OTRequiredForHoliday'
GO
USE [hr]
GO
/****** Object: Table [dbo].[HRM_DailyAttendance_Amend] Script Date: 22/Nov/2019 05:15:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_DailyAttendance_Amend](
[AttId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[EmpId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__EmpId__70D3A237] DEFAULT ((0)),
[InTime] [datetime] NULL,
[OutTime] [datetime] NULL,
[ReasonForLeave] [varchar](700) NULL CONSTRAINT [df_HRM_DailyAttendance_Amend_ReasonForLeave] DEFAULT (''),
[AttDate] [datetime] NULL,
[ShiftId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Shift__71C7C670] DEFAULT ((0)),
[LeaveId] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Leave__72BBEAA9] DEFAULT ((0)),
[CatId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__CatId__73B00EE2] DEFAULT ((0)),
[DeptId] [numeric](10, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__DeptI__74A4331B] DEFAULT ((0)),
[Attendance] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Atten__75985754] DEFAULT ('P'),
[SerialNo] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Seria__768C7B8D] DEFAULT ((0)),
[OfficialOut] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Daily_Amend__Offic__77809FC6] DEFAULT ('N'),
[Flag] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_DailyA_Amend__Flag__7874C3FF] DEFAULT ('N'),
[HalfDayLeave] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_HalfDayLeave] DEFAULT ('N'),
[OTRequiredForHoliday] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_OTRequiredForHoliday] DEFAULT ('N'),
[MisPunch] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_MisPunch] DEFAULT ('N'),
[Permisable] [numeric](18, 0) NOT NULL DEFAULT ((0)),
[Notes] [varchar](1000) NOT NULL DEFAULT (''),
[LeaveAppId] [numeric](9, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_Amend_LeaveAppId] DEFAULT ((0)),
[ConsiderForSalary] [varchar](1) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_ConsiderForSalary] DEFAULT ('Y'),
[LeaveId2] [numeric](18, 0) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_LeaveId2] DEFAULT ((0)),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_DailyAtt_Amend_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_DailyAtt_Amend_EntryComputer] DEFAULT (''),
[ImportMachine] [varchar](500) NOT NULL CONSTRAINT [DF_HRM_DailyAttendance_Amend_ImportMachine] DEFAULT (''),
[BillNo] [varchar](100) NOT NULL CONSTRAINT [df_Hrm_DailyAttendance_Amend_BillNo] DEFAULT (''),
[CustId] [numeric](18, 0) NOT NULL CONSTRAINT [Df_Hrm_DailyAttendance_Amend_CustId] DEFAULT ((0)),
[LocationId] [numeric](18, 0) NOT NULL CONSTRAINT [df_HRM_DailyAttendance_Amend_LocationId] DEFAULT ((0)),
[othrs] [varchar](9) NULL,
[remarks] [varchar](1000) NULL,
[prodn] [numeric](12, 3) NULL,
[outtime1] [datetime] NULL,
[entrydate] [datetime] NULL,
[ent_down_date] [datetime] NULL,
[otentrydate] [datetime] NULL,
[otentredby] [numeric](10, 0) NULL,
[otaprovedby] [numeric](10, 0) NULL,
[otaproveddate] [datetime] NULL,
[otprocess] [char](1) NULL,
[Latedet] [char](3) NULL,
[LatePunch] [numeric](10, 3) NULL,
[earlypunch] [numeric](10, 3) NULL,
[F_S_half] [varchar](3) NULL,
[forceabsent] [char](1) NULL,
[ShiftStartTime] [datetime] NULL,
[ShiftEndTime] [datetime] NULL,
[LunchStartTime] [datetime] NULL,
[LunchEndTime] [datetime] NULL,
[CalStartTime] [datetime] NULL,
[CalEndTime] [datetime] NULL,
[FHFlag] [char](3) NULL,
[SHFlag] [char](3) NULL,
[FullFlag] [char](3) NULL,
[ChkShiftStartTime] [int] NULL,
[GraceTime] [int] NULL,
[HalfDayOnduty] [char](1) NULL,
[Onduty] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[emp_personal] Script Date: 22/Nov/2019 05:15:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[emp_personal](
[empid] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[cardno] [varchar](15) NOT NULL,
[empcode] [varchar](15) NOT NULL,
[empname] [varchar](50) NOT NULL,
[father_name] [varchar](50) NULL,
[address] [varchar](600) NULL,
[cityid] [numeric](6, 0) NULL,
[pincode] [varchar](7) NULL,
[phone] [varchar](20) NULL,
[email] [varchar](50) NULL,
[blood_group] [varchar](10) NULL,
[d_o_b] [datetime] NULL,
[d_o_j] [datetime] NULL,
[gender] [varchar](20) NULL,
[m_status] [int] NULL,
[e_cont_name] [varchar](50) NULL,
[e_cont_phone] [varchar](20) NULL,
[e_cont_address] [varchar](600) NULL,
[photo_path] [varchar](90) NULL,
[photo] [varbinary](max) NULL,
[stateid] [numeric](6, 0) NULL,
[loginid] [numeric](6, 0) NULL,
[entrydate] [datetime] NULL,
[entrycomputer] [varchar](30) NULL,
[leave_approval] [char](1) NULL,
[locationid] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[emp_personal] ADD [resign] [char](1) NULL DEFAULT ('N')
ALTER TABLE [dbo].[emp_personal] ADD [resingdate] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [report_to] [numeric](10, 0) NULL DEFAULT ((0))
SET ANSI_PADDING ON
ALTER TABLE [dbo].[emp_personal] ADD [esiapplicable] [char](1) NULL DEFAULT ('N')
ALTER TABLE [dbo].[emp_personal] ADD [contractorid] [numeric](10, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [loginname] [varchar](40) NULL
ALTER TABLE [dbo].[emp_personal] ADD [password] [varchar](50) NULL
ALTER TABLE [dbo].[emp_personal] ADD [resign_type] [varchar](20) NULL
ALTER TABLE [dbo].[emp_personal] ADD [idproof] [varbinary](max) NULL
ALTER TABLE [dbo].[emp_personal] ADD [addrproof] [varbinary](max) NULL
ALTER TABLE [dbo].[emp_personal] ADD [ageproof] [varbinary](max) NULL
ALTER TABLE [dbo].[emp_personal] ADD [approvaldept] [varchar](30) NULL
ALTER TABLE [dbo].[emp_personal] ADD [accid] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [adv_approval] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [MulticompEmp] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [cod_approval] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [peraddress] [varchar](500) NULL
ALTER TABLE [dbo].[emp_personal] ADD [expdtreleave] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [actreldate] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [ManuvalPunch] [char](1) NULL DEFAULT ('N')
ALTER TABLE [dbo].[emp_personal] ADD [d_o_b_actual] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [ot_app] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [esiapp] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [pfapp] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [qualification] [varchar](100) NULL
ALTER TABLE [dbo].[emp_personal] ADD [preexp] [numeric](12, 4) NULL
ALTER TABLE [dbo].[emp_personal] ADD [appraiser] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [rev_officer] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [promo_year] [numeric](5, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [w_off] [varchar](20) NULL
ALTER TABLE [dbo].[emp_personal] ADD [mop_coordinator] [numeric](12, 0) NULL
ALTER TABLE [dbo].[emp_personal] ADD [mop_coord] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [power_user] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [webuser] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [l_break_date] [datetime] NULL
ALTER TABLE [dbo].[emp_personal] ADD [mop_req] [char](1) NULL
ALTER TABLE [dbo].[emp_personal] ADD [resign_enteredby] [numeric](12, 0) NULL
CONSTRAINT [PK_emp_personal] PRIMARY KEY CLUSTERED
(
[cardno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[emp_employementdet] Script Date: 22/Nov/2019 05:15:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[emp_employementdet](
[empid] [numeric](10, 0) NOT NULL,
[pfno] [varchar](30) NULL,
[esino] [varchar](30) NULL,
[bank_accno] [varchar](40) NULL,
[bank_name] [varchar](80) NULL,
[panno] [varchar](20) NULL,
[aadharno] [varchar](20) NULL,
[voter_id] [varchar](20) NULL,
[drive_licence] [varchar](20) NULL,
[licence_validity] [datetime] NULL,
[loginname] [varchar](30) NULL,
[login_password] [varchar](30) NULL,
[deptid] [numeric](7, 0) NULL,
[catid] [numeric](7, 0) NULL,
[shiftrotationpattern] [varchar](20) NULL,
[entrydate] [datetime] NULL,
[complementno] [varchar](30) NULL,
[uanno] [varchar](40) NULL,
[deptchangeid] [numeric](12, 0) NULL,
[deptchangeon] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[scalemaster] Script Date: 22/Nov/2019 05:16:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[scalemaster](
[id] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[empid] [numeric](6, 0) NOT NULL,
[deptid] [numeric](6, 0) NOT NULL,
[catid] [numeric](6, 0) NOT NULL,
[desigid] [numeric](6, 0) NOT NULL,
[adid] [numeric](6, 0) NOT NULL,
[amount] [numeric](12, 2) NOT NULL,
[inc_date] [datetime] NOT NULL,
[employer_amt] [numeric](12, 2) NULL DEFAULT ((0)),
[entrydate] [datetime] NULL,
[entrycomputer] [varchar](40) NULL,
[loginid] [numeric](10, 0) NULL,
[active_scale] [char](1) NULL,
[rval] [int] NULL,
CONSTRAINT [PK_scale] PRIMARY KEY CLUSTERED
(
[empid] ASC,
[inc_date] ASC,
[adid] ASC,
[catid] ASC,
[amount] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[scalemaster] WITH CHECK ADD FOREIGN KEY([catid])
REFERENCES [dbo].[hrm_category] ([catid])
GO
ALTER TABLE [dbo].[scalemaster] WITH CHECK ADD FOREIGN KEY([deptid])
REFERENCES [dbo].[department] ([deptid])
GO
ALTER TABLE [dbo].[scalemaster] WITH CHECK ADD FOREIGN KEY([adid])
REFERENCES [dbo].[hrm_allowancededuction] ([adid])
GO
USE [hr]
GO
/****** Object: Table [dbo].[Designation] Script Date: 22/Nov/2019 05:16:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Designation](
[desigid] [numeric](6, 0) IDENTITY(1,1) NOT NULL,
[designation] [varchar](40) NOT NULL,
[catid] [numeric](6, 0) NOT NULL,
[entryempid] [numeric](6, 0) NULL,
[entrycomputer] [varchar](30) NULL,
[entrydatetime] [datetime] NULL,
[ltaapplicable] [char](1) NULL,
[latepunchallow] [char](1) NULL DEFAULT ('N'),
[DesigGroup] [numeric](1, 0) NULL DEFAULT ((1)),
[Desig_grp] [int] NULL,
[slab_desig] [int] NULL,
[staff_worker] [char](1) NULL,
[edu_allow] [numeric](12, 2) NULL,
[sodexo] [numeric](12, 0) NULL,
[desig_slno] [int] NULL,
[Grade] [int] NULL,
PRIMARY KEY CLUSTERED
(
[desigid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Designation] WITH CHECK ADD FOREIGN KEY([catid])
REFERENCES [dbo].[hrm_category] ([catid])
GO
ALTER TABLE [dbo].[Designation] WITH CHECK ADD CHECK (([designation]<>''))
GO
USE [hr]
GO
/****** Object: Table [dbo].[Hrm_ShiftRotation] Script Date: 22/Nov/2019 05:17:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hrm_ShiftRotation](
[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[EmpId] [numeric](18, 0) NOT NULL,
[ShiftId] [numeric](18, 0) NOT NULL,
[ChangeDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[ShiftRotationPeriod] [numeric](18, 0) NOT NULL,
[CatId] [numeric](18, 0) NOT NULL,
[DeptId] [numeric](18, 0) NOT NULL,
[StartDate] [datetime] NULL,
[loginid] [numeric](12, 0) NULL,
[EntryComputer] [varchar](30) NULL,
[entrydate] [datetime] NULL,
[chflag] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[HRM_Shift] Script Date: 22/Nov/2019 05:17:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_Shift](
[ShiftId] [int] IDENTITY(1,1) NOT NULL,
[Shift] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[Stime] [datetime] NULL,
[ETime] [datetime] NULL,
[LunchStart] [datetime] NULL,
[LunchEnd] [datetime] NULL,
[NextDay] [varchar](1) NOT NULL CONSTRAINT [DF__HRM_Shift__NextD__3E131840] DEFAULT ('N'),
[NightShift] [varchar](10) NOT NULL CONSTRAINT [DF_hrm_HRM_shift] DEFAULT ('N'),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Shift_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_Shift_EntryComputer] DEFAULT (''),
[active] [varchar](2) NULL,
[locationid] [int] NULL,
[entrydate] [datetime] NULL,
[companyid] [numeric](2, 0) NULL,
[previousday] [char](1) NULL,
[shiftname] [varchar](20) NULL,
CONSTRAINT [PK_HRM_Shift_ShiftID] PRIMARY KEY CLUSTERED
(
[ShiftId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_leavemaster] Script Date: 22/Nov/2019 05:18:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_leavemaster](
[id] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[appno] [varchar](30) NOT NULL,
[appdate] [datetime] NULL,
[empid] [numeric](10, 0) NOT NULL,
[approvalby] [numeric](10, 0) NOT NULL,
[elbal] [numeric](10, 2) NULL,
[leave_from] [datetime] NULL,
[leave_to] [datetime] NULL,
[leavedays] [numeric](12, 2) NULL,
[reason] [varchar](500) NULL,
[entrydatetime] [datetime] NULL,
[logid] [numeric](10, 0) NULL,
[entrycomputer] [varchar](30) NULL,
[approveddate] [datetime] NULL,
[leavetype] [numeric](10, 0) NULL,
[adv_amount] [numeric](12, 2) NULL,
[returnon] [datetime] NULL,
[approved] [char](1) NULL,
[adv_approved_amt] [numeric](12, 2) NULL,
[adv_approved_on] [datetime] NULL,
[adv_approvedby] [numeric](10, 0) NULL,
[adv_approvalby] [numeric](10, 0) NULL,
[adv_amtpaid] [numeric](12, 2) NULL,
PRIMARY KEY CLUSTERED
(
[appno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_leavedet] Script Date: 22/Nov/2019 05:18:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_leavedet](
[id] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[tranid] [numeric](10, 0) NOT NULL,
[appno] [varchar](30) NULL,
[leavedate] [datetime] NULL,
[halfday] [char](2) NULL,
[approved] [char](1) NULL,
[approveddate] [datetime] NULL,
[approval_computer] [varchar](50) NULL,
[approval_login] [numeric](12, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[hrm_leavedet] WITH CHECK ADD CONSTRAINT [CK__hrm_leave__halfd__6AEFE058] CHECK (([halfday]='FH' OR [halfday]='SH' OR [halfday]='F'))
GO
ALTER TABLE [dbo].[hrm_leavedet] CHECK CONSTRAINT [CK__hrm_leave__halfd__6AEFE058]
GO
USE [hr]
GO
/****** Object: Table [dbo].[HRM_LeaveType] Script Date: 22/Nov/2019 05:19:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HRM_LeaveType](
[LeaveId] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LeaveType] [varchar](50) NOT NULL CONSTRAINT [DF_HRM_LeaveType_LeaveType] DEFAULT (' '),
[Description] [varchar](50) NOT NULL CONSTRAINT [DF_HRM_LeaveType_Description] DEFAULT (' '),
[Paid] [varchar](50) NOT NULL CONSTRAINT [DF__HRM_LeaveT__Paid__7A5D0C71] DEFAULT ('Y'),
[LeaveDays] [numeric](18, 0) NOT NULL CONSTRAINT [DF__HRM_Leave__Leave__7C4554E3] DEFAULT ((0)),
[SlNo] [numeric](9, 0) NOT NULL CONSTRAINT [DF_HRM_LeaveType_SlNo] DEFAULT ((0)),
[ExcessLeaveAllowed] [varchar](3) NOT NULL DEFAULT ('N'),
[MaxLimitToCarryforward] [numeric](5, 2) NOT NULL CONSTRAINT [Df_HRM_LeaveTypes_MaxLimitToCarryforward] DEFAULT ((0)),
[MaxLimitReq] [char](1) NOT NULL CONSTRAINT [Df_HRM_LeaveTypes_MaxLimitReq] DEFAULT ('N'),
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_LeaveType_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_LeaveType_EntryComputer] DEFAULT (''),
[LeaveEncashment] [varchar](1) NOT NULL CONSTRAINT [df_HRM_LeaveType_LeaveEncashment] DEFAULT ('N'),
[entrydate] [datetime] NULL,
[disppayroll] [char](1) NULL,
[staff_worker] [char](1) NULL,
CONSTRAINT [pk_hrm_leavetype_LeaveId] PRIMARY KEY CLUSTERED
(
[LeaveId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Leave Type Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'LeaveId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Leave Type' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'LeaveType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Paid Leave (Yes/No)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'Paid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'No of Leave days ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HRM_LeaveType', @level2type=N'COLUMN',@level2name=N'LeaveDays'
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_weekoff] Script Date: 22/Nov/2019 05:19:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_weekoff](
[id] [numeric](12, 0) IDENTITY(1,1) NOT NULL,
[empid] [numeric](10, 0) NULL,
[catid] [numeric](10, 0) NULL,
[week_off] [varchar](15) NULL,
[eff_from] [datetime] NULL,
[eff_to] [datetime] NULL,
[entrydate] [datetime] NULL,
[entrycomputer] [varchar](40) NULL,
[loginid] [numeric](12, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[hrm_weeklyoff] Script Date: 22/Nov/2019 05:19:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hrm_weeklyoff](
[LDate] [datetime] NULL,
[LDesc] [varchar](50) NOT NULL,
[Year] [numeric](18, 0) NOT NULL,
[Day] [varchar](10) NOT NULL,
[DateChange] [datetime] NULL,
[EntryEmpId] [numeric](18, 0) NOT NULL,
[EntryComputer] [varchar](100) NOT NULL,
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[LocationId] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [FinancialYear] [varchar](25) NOT NULL
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [leaveid] [numeric](10, 0) NULL
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [empid] [numeric](10, 0) NULL
ALTER TABLE [dbo].[hrm_weeklyoff] ADD [catid] [numeric](10, 0) NULL
GO
SET ANSI_PADDING OFF
GO
USE [hr]
GO
/****** Object: Table [dbo].[Leave] Script Date: 22/Nov/2019 05:20:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Leave](
[LDate] [datetime] NULL,
[LDesc] [varchar](50) NOT NULL CONSTRAINT [DF_Leave_LDesc] DEFAULT (' '),
[Year] [numeric](18, 0) NOT NULL CONSTRAINT [DF__Leave__Year__5FFE1BF0] DEFAULT ((0)),
[Day] [varchar](10) NOT NULL CONSTRAINT [DF_Leave_Day] DEFAULT (' '),
[DateChange] [datetime] NULL,
[EntryEmpId] [numeric](18, 0) NOT NULL CONSTRAINT [DF_Leave_EntryEmpId] DEFAULT ((0)),
[EntryComputer] [varchar](100) NOT NULL CONSTRAINT [DF_Leave_EntryComputer] DEFAULT (''),
[ID] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LocationId] [numeric](18, 0) NOT NULL CONSTRAINT [df_Leave_LocationId] DEFAULT ('1')
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Leave] ADD [FinancialYear] [varchar](25) NOT NULL CONSTRAINT [df_Leave_FinancialYear] DEFAULT ('')
ALTER TABLE [dbo].[Leave] ADD [leaveid] [numeric](10, 0) NULL
CONSTRAINT [pk_leave_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [sandfitsetime]
GO
/****** Object: Table [dbo].[DeviceLogs] Script Date: 22/Nov/2019 05:21:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DeviceLogs](
[DeviceLogId] [int] IDENTITY(1,1) NOT NULL,
[DownloadDate] [datetime] NULL,
[DeviceId] [int] NOT NULL,
[UserId] [nvarchar](50) NOT NULL,
[LogDate] [datetime] NOT NULL,
[Direction] [nvarchar](255) NULL,
[AttDirection] [nvarchar](255) NULL,
[C1] [nvarchar](255) NULL,
[C2] [nvarchar](255) NULL,
[C3] [nvarchar](255) NULL,
[C4] [nvarchar](255) NULL,
[C5] [nvarchar](255) NULL,
[C6] [nvarchar](255) NULL,
[C7] [nvarchar](255) NULL,
[WorkCode] [nvarchar](255) NULL,
[ChkData] [char](1) NULL,
[ChkData1] [char](1) NULL,
CONSTRAINT [PK_DeviceLogs] PRIMARY KEY CLUSTERED
(
[DeviceLogId] ASC,
[UserId] ASC,
[LogDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [sandfitsetime]
GO
/****** Object: Table [dbo].[Devices] Script Date: 22/Nov/2019 05:21:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Devices](
[DeviceId] [int] IDENTITY(1,1) NOT NULL,
[DeviceFName] [nvarchar](255) NOT NULL,
[DeviceSName] [nvarchar](255) NOT NULL,
[DeviceDirection] [nvarchar](255) NULL,
[SerialNumber] [nvarchar](255) NULL,
[ConnectionType] [nvarchar](255) NULL,
[IpAddress] [nvarchar](255) NULL,
[BaudRate] [nvarchar](255) NULL,
[CommKey] [nvarchar](255) NOT NULL CONSTRAINT [DF_Devices_CommKey] DEFAULT (0),
[ComPort] [nvarchar](255) NULL,
[LastLogDownloadDate] [datetime] NULL CONSTRAINT [DF_Devices_LastLogDownloadDate] DEFAULT ('1900-01-01 00:00'),
[C1] [nvarchar](255) NULL,
[C2] [nvarchar](255) NULL,
[C3] [nvarchar](255) NULL,
[C4] [nvarchar](255) NULL,
[C5] [nvarchar](255) NULL,
[C6] [nvarchar](255) NULL,
[C7] [nvarchar](255) NULL,
[TransactionStamp] [nvarchar](50) NULL DEFAULT ('0'),
[LastPing] [datetime] NULL,
[DeviceType] [nvarchar](255) NULL,
[OpStamp] [nvarchar](255) NULL,
[DownLoadType] [int] NULL,
[Timezone] [nvarchar](50) NULL,
[DeviceLocation] [nvarchar](50) NULL,
[TimeOut] [nvarchar](50) NULL,
CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED
(
[DeviceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [sandfitsetime]
GO
/****** Object: Table [dbo].[DevicesStatus] Script Date: 22/Nov/2019 05:23:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DevicesStatus](
[DeviceStatusId] [int] IDENTITY(1,1) NOT NULL,
[DeviceId] [int] NULL,
[DeviceStatus] [nvarchar](255) NULL,
[LastDeviceStatusOn] [datetime] NULL,
CONSTRAINT [DevicesStatus_PK] PRIMARY KEY CLUSTERED
(
[DeviceStatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [production]
GO
/****** Object: Table [dbo].[HR_Controls] Script Date: 22/Nov/2019 05:29:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_Controls](
[TranMasId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Locationid] [int] NULL,
[Yrmon] [int] NULL,
[CategoryId] [numeric](10, 0) NULL,
[WeeklyOff] [varchar](50) NULL,
[GraceTimeMin] [numeric](10, 0) NULL,
[GraceTimeCount] [numeric](10, 0) NULL,
[OTMaxHrs] [numeric](10, 0) NULL,
[OTMinHrs] [numeric](10, 0) NULL,
[RegPermissionMaxMinutes] [numeric](10, 0) NULL,
[RegPermissionMinMinutes] [numeric](10, 0) NULL,
[SplPermissionMaxMinutes] [numeric](10, 0) NULL,
[SplPermissionCount] [numeric](10, 0) NULL,
[ChkShiftStTime] [numeric](10, 0) NULL,
[EffFrom] [datetime] NULL,
[Active] [char](1) NULL,
[CreatedBy] [varchar](50) NULL,
[CreatedDate] [datetime] NULL,
[CreatedSystem] [varchar](100) NULL,
[DesigGroup] [numeric](1, 0) NULL,
[LatePermissionCount] [int] NULL,
[EarlyOutPunch] [numeric](10, 0) NULL DEFAULT ((0)),
[RegPermissionTotMinutes] [numeric](10, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [production]
GO
/****** Object: Table [dbo].[HR_ShiftChangeRequest] Script Date: 22/Nov/2019 05:32:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_ShiftChangeRequest](
[TranId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[LocationId] [int] NULL,
[EntryDate] [date] NULL,
[DeptId] [int] NULL,
[CatId] [int] NULL,
[EmpId] [int] NULL,
[ShiftId] [int] NULL,
[ShiftStartDate] [date] NULL,
[ShiftEndDate] [date] NULL,
[CreatedBy] [varchar](200) NULL,
[CreatedDate] [datetime] NULL,
[CreatedSystem] [varchar](200) NULL,
[Status] [char](1) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [production]
GO
/****** Object: Table [dbo].[Hr_TwelveHrShiftCtrlHrs] Script Date: 22/Nov/2019 05:32:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hr_TwelveHrShiftCtrlHrs](
[TranId] [int] IDENTITY(1,1) NOT NULL,
[LocationId] [int] NULL,
[ShiftEndTimeMaxMinutes] [int] NULL
) ON [PRIMARY]
GO
USE [production]
GO
/****** Object: Table [dbo].[HR_Permission] Script Date: 22/Nov/2019 05:33:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_Permission](
[TranId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[LocationId] [int] NULL,
[Yrmon] [int] NULL,
[EntryDate] [date] NULL,
[EmpId] [numeric](10, 0) NULL,
[PermissionHrsMin] [numeric](10, 0) NULL,
[FromTime] [datetime] NULL,
[ToTime] [datetime] NULL,
[Reason] [varchar](250) NULL,
[Approved] [char](1) NULL,
[CreatedBy] [varchar](100) NULL,
[CreatedDate] [datetime] NULL,
[CreatedSystem] [varchar](100) NULL,
[ApprovedBy] [varchar](100) NULL,
[ApprovedDate] [datetime] NULL,
[ApprovedSystem] [varchar](100) NULL,
[PermissionType] [char](2) NULL,
[PermissionLevel] [char](2) NULL,
[Download] [char](1) NULL DEFAULT ('N'),
[UnApprovalRemarks] [varchar](150) NULL,
CONSTRAINT [UC_HR_Permission] UNIQUE NONCLUSTERED
(
[EmpId] ASC,
[FromTime] ASC,
[Approved] ASC,
[PermissionType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
No comments:
Post a Comment