-------- Table Script -----------

Alter TAble TA_tbl_ApprovalSettings Add CompressedWorkHours bit,MinWeeklyCompressedHours decimal(5)


CREATE TABLE [dbo].[TA_CompressedWorkHoursApplication](
	[CWH_App_Id] [int] IDENTITY(1,1) NOT NULL,
	[Employee_Id] [varchar](50) NOT NULL,
	[ApplicationDate] [datetime] NOT NULL,
	[FromDate] [date] NOT NULL,
	[ToDate] [date] NOT NULL,
	[Status] [varchar](20) NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[YearSettings_Id] [varchar](20) NOT NULL,
	[IsDeleted] [bit] NOT NULL,
	[Reason] [nvarchar](250) NULL,
	[CompressedWorkHour] [float] NULL,
	[Remarks] [nvarchar](250) NULL,
	[SanctionEMP] [int] NULL,
	[CancellationReason] [nvarchar](250) NULL,
	[CancelledOn] [date] NULL,
	[CancelledBy] [nvarchar](50) NULL,
	[CancelledMailTrigger] [int] NULL,
	[ModifiedDate] [nvarchar](50) NULL,
 CONSTRAINT [PK_TA_CompressedWorkHoursApplication] PRIMARY KEY CLUSTERED 
(
	[CWH_App_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



Alter table TA_Tbl_ProcessedData add C_WH Nvarchar(20) 

-- View Script---
ALTER VIEW [dbo].[VW_FetchDataFromProcessedData]
AS
SELECT P.Date, P.Shift_Id, P.InTime, P.OutTime, P.Employee_Code_Id, P.Process_Rec_ID, P.HrsWork, P.HrsOT, P.NormOT, P.OffOT, P.HolOT, P.EarlyOut, P.LateIn, P.WorkHrs, P.Attendance, P.HrsLess, P.HrsExtra, P.LateInMins, P.EarlyOutMins, 
                  P.ExtraMins, P.OtMins, P.OvertimeStatus, P.TotalDays, P.LeaveCode, P.LateEntryReason, P.EarlyOutReason, P.BreakOut, P.BreakIn, P.BreakHours, P.BreakLateMins, P.BreakLate, P.TotalLate, P.TotalLateMins, P.ProcessFlag, P.Status, 
                  P.FNAttendanceCode, P.ANAttendanceCode, P.LateExit, P.EarlyEntry, P.BreakHrs, P.LunchHrs, P.BreakHrsInMins, P.InNotation, P.LunchHrsInMins, P.OutNotation, P.Shift_EndTime, P.Inpunch, P.Outpunch, 
                  dbo.TA_Tbl_Shift.Shift_StartTime, dbo.TA_Tbl_Shift.Shift_SecondHalf_StartTime, P.ActOTMins, P.ActNormOT, P.SPEC_NormOT1, P.SPEC_NormOTMins1, P.SPEC_NormOT2, P.SPEC_NormOTMins2, P.SPEC_OffOT1, P.SPEC_OffOTMins1, 
                  P.SPEC_OffOT2, P.SPEC_OffOTMins2, P.SPEC_HOLOT1, P.SPEC_HOLOTMins1, P.SPEC_HOLOT2, P.SPEC_HOLOTMins2, P.ActHolOTMins, P.ActOffOTMins, P.ActHolOT, P.ActOffOT, P.PermissionHrs, P.PermissionHrsInMins, 
                  P.PermissionStatus, P.InLocation, P.OutLocation, '' AS In_Latitude, '' AS In_Longitude, '' AS Out_Latitude, '' AS Out_Longitude, P.RemainingBufferHours, P.DeductBufferInMins, P.AdjHrs,
				  P.C_WH
FROM     dbo.TA_Tbl_ProcessedData AS P LEFT OUTER JOIN
                  dbo.TA_Tbl_Shift ON P.Shift_Id = dbo.TA_Tbl_Shift.Shift_Id
GO




