﻿----------------------------------------- AK 2026-01-18 --------------------
BEGIN TRANSACTION
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_AllClient_1
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_NeedPublish_1
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_AvailableToCustomer_1
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_PassFixedFormulaToSupReport_1
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_ShowGroup_1
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_ShowSeparatedPage_1
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_Sortable_1
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_ShowMonthAndYear
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_CanReadPayRoll
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_ShowMonthAndYear2
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_DependOnFinancialPrivilege
ALTER TABLE dbo.Reports DROP CONSTRAINT DF_Reports_ShowCalcNo
CREATE TABLE dbo.Tmp_Reports (
	ReportCode int NOT NULL,
	Name nvarchar(150) NOT NULL,
	Arabic_Name nvarchar(150) NOT NULL,
	ReportName nvarchar(150) NOT NULL,
	AllClient bit NOT NULL,
	NeedPublish bit NOT NULL,
	AvailableToCustomer bit NOT NULL,
	FixedFormula nvarchar(MAX) NULL,
	Description nvarchar(500) NULL,
	PassFixedFormulaToSupReport bit NOT NULL,
	ShowGroup bit NOT NULL,
	ShowSeparatedPage bit NOT NULL,
	DefaultTableGroupPrefix int NULL,
	DefaultGroupCode int NULL,
	Sortable bit NOT NULL,
	ShowMonthAndYear bit NOT NULL,
	CanReadPayRoll bit NOT NULL,
	ReportNameEn nvarchar(150) NULL,
	DescriptionEn nvarchar(500) NULL,
	ReportNo nvarchar(50) NULL,
	FileName nvarchar(50) NULL,
	ShowMonthAndYear2 bit NOT NULL,
	DependOnFinancialPrivilege bit NOT NULL,
	ShowCalcNo bit NOT NULL,
	SystemMenuCode int NULL,
	SecurityPrivilegeCode int NULL,
	IsPredefined bit NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE dbo.Tmp_Reports SET (LOCK_ESCALATION = TABLE)
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_AllClient_1 DEFAULT ((0)) FOR AllClient
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_NeedPublish_1 DEFAULT ((0)) FOR NeedPublish
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_AvailableToCustomer_1 DEFAULT ((0)) FOR AvailableToCustomer
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_PassFixedFormulaToSupReport_1 DEFAULT ((0)) FOR PassFixedFormulaToSupReport
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_ShowGroup_1 DEFAULT ((0)) FOR ShowGroup
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_ShowSeparatedPage_1 DEFAULT ((0)) FOR ShowSeparatedPage
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_Sortable_1 DEFAULT ((0)) FOR Sortable
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_ShowMonthAndYear DEFAULT ((0)) FOR ShowMonthAndYear
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_CanReadPayRoll DEFAULT ((0)) FOR CanReadPayRoll
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_ShowMonthAndYear2 DEFAULT ((0)) FOR ShowMonthAndYear2
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_DependOnFinancialPrivilege DEFAULT ((0)) FOR DependOnFinancialPrivilege
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_ShowCalcNo DEFAULT ((0)) FOR ShowCalcNo
ALTER TABLE dbo.Tmp_Reports ADD CONSTRAINT DF_Reports_IsPredefined DEFAULT 0 FOR IsPredefined
IF EXISTS(SELECT * FROM dbo.Reports)
EXEC('INSERT INTO dbo.Tmp_Reports (ReportCode, Name, Arabic_Name, ReportName, AllClient, NeedPublish, AvailableToCustomer, FixedFormula, Description, PassFixedFormulaToSupReport, ShowGroup, ShowSeparatedPage, DefaultTableGroupPrefix, DefaultGroupCode, Sortable, ShowMonthAndYear, CanReadPayRoll, ReportNameEn, DescriptionEn, ReportNo, FileName, ShowMonthAndYear2, DependOnFinancialPrivilege, ShowCalcNo, SystemMenuCode, SecurityPrivilegeCode)
SELECT ReportCode, Name, Arabic_Name, ReportName, AllClient, NeedPublish, AvailableToCustomer, FixedFormula, Description, PassFixedFormulaToSupReport, ShowGroup, ShowSeparatedPage, DefaultTableGroupPrefix, DefaultGroupCode, Sortable, ShowMonthAndYear, CanReadPayRoll, ReportNameEn, DescriptionEn, ReportNo, FileName, ShowMonthAndYear2, DependOnFinancialPrivilege, ShowCalcNo, SystemMenuCode, SecurityPrivilegeCode FROM dbo.Reports WITH (HOLDLOCK TABLOCKX)')
ALTER TABLE dbo.ReportsAppModules DROP CONSTRAINT FK_ReportsAppModules_Reports
ALTER TABLE dbo.ReportsClient DROP CONSTRAINT FK_ReportsClient_Reports
ALTER TABLE dbo.ReportsExceptCountries DROP CONSTRAINT FK_ReportsExceptCountries_Reports
ALTER TABLE dbo.ReportSecurityUsers DROP CONSTRAINT FK_ReportSecurityUsers_Reports
ALTER TABLE dbo.ReportSecurityGroups DROP CONSTRAINT FK_ReportSecurityGroups_Reports
ALTER TABLE dbo.ParameterReports DROP CONSTRAINT FK_ParameterReports_Reports
ALTER TABLE dbo.ReportsFormula DROP CONSTRAINT FK_ReportsFormula_Reports
DROP TABLE dbo.Reports
EXECUTE sp_rename N'dbo.Tmp_Reports', N'Reports', 'OBJECT' 
ALTER TABLE dbo.Reports ADD CONSTRAINT PK_Reports PRIMARY KEY CLUSTERED (ReportCode) WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.ReportsFormula ADD CONSTRAINT FK_ReportsFormula_Reports FOREIGN KEY (ReportCode) REFERENCES dbo.Reports (ReportCode) ON UPDATE NO ACTION ON DELETE NO ACTION 	
ALTER TABLE dbo.ReportsFormula SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.ParameterReports ADD CONSTRAINT FK_ParameterReports_Reports FOREIGN KEY (ReportCode) REFERENCES dbo.Reports (ReportCode) ON UPDATE NO ACTION ON DELETE NO ACTION 	
ALTER TABLE dbo.ParameterReports SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.ReportSecurityGroups ADD CONSTRAINT FK_ReportSecurityGroups_Reports FOREIGN KEY (ReportCode) REFERENCES dbo.Reports (ReportCode) ON UPDATE NO ACTION ON DELETE NO ACTION
ALTER TABLE dbo.ReportSecurityGroups SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.ReportSecurityUsers ADD CONSTRAINT FK_ReportSecurityUsers_Reports FOREIGN KEY (ReportCode) REFERENCES dbo.Reports (ReportCode) ON UPDATE NO ACTION ON DELETE NO ACTION
ALTER TABLE dbo.ReportSecurityUsers SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.ReportsExceptCountries ADD CONSTRAINT FK_ReportsExceptCountries_Reports FOREIGN KEY(ReportCode) REFERENCES dbo.Reports (ReportCode) ON UPDATE NO ACTION ON DELETE NO ACTION
ALTER TABLE dbo.ReportsExceptCountries SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.ReportsClient ADD CONSTRAINT FK_ReportsClient_Reports FOREIGN KEY (ReportCode) REFERENCES dbo.Reports (ReportCode) ON UPDATE NO ACTION ON DELETE NO ACTION 	
ALTER TABLE dbo.ReportsClient SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.ReportsAppModules ADD CONSTRAINT FK_ReportsAppModules_Reports FOREIGN KEY (ReportCode) REFERENCES dbo.Reports (ReportCode) ON UPDATE NO ACTION ON DELETE NO ACTION 	
ALTER TABLE dbo.ReportsAppModules SET (LOCK_ESCALATION = TABLE)
COMMIT
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100001, N'09-General Ledger', N'09-استاذ عام', N'09-GL.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'09-GL-EN.rpt', NULL, NULL, N'GL', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100002, N'10-GL Details', N'10-استاذ عام تفصيلي', N'10-GLByDetail.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'10-GLByDetail-EN.rpt', NULL, NULL, N'GLByDetail', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100003, N'11-Trial balance', N'11-ميزان المراجعه', N'11-TrialBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'11-TrialBalance-EN.rpt', NULL, NULL, N'ميزان المراجعه', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100004, N'12-Detailed trial balance', N'12-ميزان مراجعة تفصيلي', N'12-TrialBalanceByDetail.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'12-TrialBalanceByDetail-EN.rpt', NULL, NULL, N'ميزان مراجعة تفصيلي', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100005, N'14-Customer Statement', N'14-كشف حساب عميل', N'14-CustomerStatment.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'14-CustomerStatment-EN.rpt', NULL, NULL, N'كشف حساب عميل', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100006, N'15-Customer Balances', N'15-ارصدة عملاء', N'15-CustomerBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'15-CustomerBalance-EN.rpt', NULL, NULL, N'ارصدة عملاء', 0, 0, 0, 1147, 23209, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100007, N'18-Supplier statement', N'18-كشف حساب مورد', N'18-SupplierStatment.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'18-SupplierStatment-EN.rpt', NULL, NULL, N'كشف حساب مورد', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100008, N'19-Supplier Balances', N'19-ارصدة موردين', N'19-SupplierBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'19-SupplierBalance-EN.rpt', NULL, NULL, N'ارصدة موردين', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100009, N'21-Bank statement', N'21-كشف حساب بنك', N'21-BankStatment.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'21-BankStatment-EN.rpt', NULL, NULL, N'كشف حساب بنك', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100010, N'20-Safe statement', N'20-كشف حساب خزينة', N'20-SafeStatment.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'20-SafeStatment-EN.rpt', NULL, NULL, N'كشف حساب خزينة', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100011, N'17-Expense statement', N'17-كشف مصروفات', N'17-ExpensesStatment.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'17-ExpensesStatment-EN.rpt', NULL, NULL, N'كشف مصروفات', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100012, N'16-Expense pool', N'16-مجمع مصروفات', N'16-ExpensesBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'16-ExpensesBalance-EN.rpt', NULL, NULL, N'مجمع مصروفات', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100013, N'22-Total Bonds', N'22-سندات مجمعه', N'22-Financial bonds.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'22-Financial bonds-EN.rpt', NULL, NULL, N'سندات مجمعه', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100014, N'13-Journal Entry', N'13-قيود اليومية', N'13-JournalEntry.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'13-JournalEntry-EN.rpt', NULL, NULL, N'قيود اليومية', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100015, N'24-Safes balances', N'24-ارصدة الخزائن', N'24-SafeBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'24-SafeBalance-EN.rpt', NULL, NULL, N'ارصدة الخزائن', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100016, N'25-Bank Balances', N'25-ارصدة البنوك', N'25-BankBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'25-BankBalance-EN.rpt', NULL, NULL, N'ارصدة البنوك', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100017, N'101ItemCard', N'101-كارت الصنف', N'101-ItemCard.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'101-ItemCard-EN.rpt', NULL, NULL, N'101-ItemCard', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100018, N'26-Profitandloss', N'26-قائمة الدخل', N'26-Profitandloss.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'26-Profitandloss-EN.rpt', NULL, NULL, N'26-Profitandloss', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100019, N'27- Budget', N'27-الميزانيه', N'27-BalanceSheet.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'27-BalanceSheet-EN.rpt', NULL, NULL, N'27-BalanceSheet', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100020, N'106-StoreBalanceDetail', N'106-ارصده المخازن - تفصيليه', N'106-StoreBalanceDetail.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'106-StoreBalanceDetail-EN.rpt', NULL, NULL, N'106-StoreBalanceDetail', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100021, N'107-Stock Balances', N'107-ارصدة المخازن', N'107-StoreBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'107-StoreBalance-EN.rpt', NULL, NULL, N'107-StoreBalance', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[Reports] ([ReportCode], [Name], [Arabic_Name], [ReportName], [AllClient], [NeedPublish], [AvailableToCustomer], [FixedFormula], [Description], [PassFixedFormulaToSupReport], [ShowGroup], [ShowSeparatedPage], [DefaultTableGroupPrefix], [DefaultGroupCode], [Sortable], [ShowMonthAndYear], [CanReadPayRoll], [ReportNameEn], [DescriptionEn], [ReportNo], [FileName], [ShowMonthAndYear2], [DependOnFinancialPrivilege], [ShowCalcNo], [SystemMenuCode], [SecurityPrivilegeCode], [IsPredefined]) VALUES (100022, N'108-ItemBalance', N'108-ارصدة الاصناف', N'108-ItemBalance.rpt', 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, NULL, 0, 0, 0, N'108-ItemBalance-EN.rpt', NULL, NULL, N'108-ItemBalance', 0, 0, 0, NULL, NULL, 1)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100006, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100001, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100002, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100003, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100004, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100005, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100007, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100008, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100009, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100010, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100011, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100012, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100013, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100014, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100015, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100016, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100017, 12)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100018, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100019, 18)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100020, 12)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100021, 12)
GO
INSERT [dbo].[ReportsAppModules] ([ReportCode], [AppModuleCode]) VALUES (100022, 12)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (12, 100001, 0, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (13, 100001, 0, NULL, NULL, 11, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (14, 100001, 0, NULL, NULL, 12, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (15, 100001, 0, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (21, 100001, 0, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (12, 100002, 1, NULL, NULL, 19, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (14, 100002, 0, NULL, NULL, 12, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (15, 100002, 0, NULL, NULL, 19, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (17, 100002, 0, NULL, NULL, 15, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (18, 100002, 0, NULL, NULL, 16, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (19, 100002, 0, NULL, NULL, 17, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (20, 100002, 0, NULL, NULL, 18, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (21, 100002, 0, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (12, 100003, 1, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (13, 100003, 0, NULL, NULL, 11, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (14, 100003, 0, NULL, NULL, 12, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (15, 100003, 0, NULL, NULL, 19, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (21, 100003, 0, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (12, 100004, 0, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (14, 100004, 0, NULL, NULL, 12, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (15, 100004, 0, NULL, NULL, 19, 1)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (16, 100004, 0, NULL, NULL, 14, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (17, 100004, 0, NULL, NULL, 15, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (19, 100004, 0, NULL, NULL, 17, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (20, 100004, 0, NULL, NULL, 18, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (21, 100004, 0, NULL, NULL, 19, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (12, 100017, 0, NULL, NULL, 10, 0)
GO
INSERT [dbo].[ParameterReports] ([ParameterCode], [ReportCode], [IsRequired], [DefaultValueFrom], [DefaultValueTo], [SelectionTable], [DisplayMultiSelect]) VALUES (12, 100018, 0, NULL, NULL, 10, 0)
GO
-----------------------------------------END AK 2026-01-18 --------------------
------------------------------------Hammad 2026-1-18 --------------------

 INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1174,N'Competency Approval Signature',N'اعتماد تقييم الكفاءة',1135,N'/PMS/CompetencyApprovalSignature',N'index','fa fa-thumb-tack',N'Get',3,1,1)
Go
-----------------------------------------Ali 2026-1-18 --------------------


IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='HaveDifferentTrack' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN
ALTER TABLE ResignationReasons ADD HaveDifferentTrack BIT Not NULL  DEFAULT (CONVERT([bit],(0)))
END
Go
-----------------------------------------End 2026-1-18 --------------------

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1174,8)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(26133,N'اعتماد تقييم الكفاءة',N'Competency Approval Signature',1174,1)
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(26134,N'عرض اعتماد تقييم الكفاءة',N'View Competency Approval Signature',1174,2)
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(26135,N'حفظ اعتماد تقييم الكفاءة',N'Save Competency Approval Signature',1174,2)
Go
-----------------------------------------End 2026-1-18 --------------------
-----------------------------------------Shehab 2026-1-19 --------------------
GO
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], 
[ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (1240, N'Work Flow Sync With Payroll', 
N'مزامنة سير العمل مع نظام الرواتب', 108, N'/WorkFlow/SyncLogException', N'Index', N'fas fa-file-signature', N'get', 35, 1, 0)

Go
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (1240, 1)
GO

Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(15613,N'مزامنة سير العمل مع نظام الرواتب',N'Work Flow Sync With Payroll',1240,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(15614,N'عرض مزامنة سير العمل مع نظام الرواتب',N'View Work Flow Sync With Payroll',1240,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(15615,N'إضافة مزامنة سير العمل مع نظام الرواتب',N'Add  Work Flow Sync With Payroll',1240,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(15616,N'تعديل مزامنة سير العمل مع نظام الرواتب',N'Edit  Work Flow Sync With Payroll',1240,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(15617,N' حذف مزامنة سير العمل مع نظام الرواتب ',N'Delete  Work Flow Sync With Payroll',1240,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(15618,N'حفظ مزامنة سير العمل مع نظام الرواتب',N'Save  Work Flow Sync With Payroll',1240,2)
GO
-----------------------------------------End 2026-1-19 --------------------
-----------------------------------------Shehab 2026-1-19 --------------------

CREATE OR ALTER PROCEDURE [dbo].[GetSyncLogExceptionRequests]
    @RequestTypeCode INT = NULL,
	@PageNumber INT=1, 
	@PageSize INT=10,
	@language int = 1,
	@EmployeeCode int = Null
AS
BEGIN

SET NOCOUNT ON;
 WITH     PaginatedResults
 as
    (SELECT sync.SyncLogExceptionCode,requests.*, sync.Exception, case when @language = 1 then emp.Arabic_Name else emp.Name end as EmployeeName ,
	case when @language = 1 then reqType.RequestTypeName else reqType.RequestTypeNameEn end as RequestTypeName,

	COUNT(*) OVER () AS TotalRecords,
            CEILING(COUNT(*) OVER () * 1.0 / @PageSize) AS TotalPages,

            ROW_NUMBER() OVER (ORDER BY requests.CreationDate DESC) AS RowNum
    FROM (
        -- vacation request
        SELECT 
            request.UnitNumber AS RequestId,
            request.AddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            11 AS RequestTypeCode,
            request.FromDate AS RequestDate,
            requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_VacationRequest AS request
        INNER JOIN VacationType AS requestType
            ON requestType.VacationCode = request.VacationTypeId
        WHERE DepartmentApproval = 1 
          AND MainType = 0  
          AND request.IsSynced = 0 

        UNION ALL

		-- PermissionRequest
        SELECT 
            request.PermissionRequestId AS RequestId,
            request.AddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            2 AS RequestTypeCode,
            request.PermissionsDate AS RequestDate,
            requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_PermissionRequest AS request
        INNER JOIN TimePermissionsTypes AS requestType
            ON requestType.TimePermissionCode = request.TimePermissionCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  -- ManpowerBudgetRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            66 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_ManpowerBudgetRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		    --BackToWorkRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            49 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_BackToWorkRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		      --SocialCareRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            64 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			socialType.Name AS TypeName,
            socialType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_SocialCareRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_SocialCareTypes socialType on requestType.SocialCareTypeCode = socialType.SocialCareTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0
		 
		 UNION ALL

		 --AssignmentRequests
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            59 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_AssignmentRequests AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		  	 --TransferRequest
        SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            25 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			 '' AS TypeName,
            '' AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_TransferRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --MonthlyEffectsRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            27 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			effectType.Name AS TypeName,
            effectType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_MonthlyEffectsRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_MonthlyEffectType effectType on requestType.MonthlyEffectTypeCode = effectType.MonthlyEffectTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  
		   --TimeSheetRequestDetail
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            55 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			effectType.Name AS TypeName,
            effectType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_TimeSheetRequestDetail AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_MonthlyEffectType effectType on requestType.MonthlyEffectTypeCode = effectType.MonthlyEffectTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0
		 
		 UNION ALL

		   --WF_BonusesRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            15 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			bonusType.Name AS TypeName,
            bonusType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_BonusesRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_BonusType bonusType on requestType.BonusTypeCode = bonusType.BonusTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		     --WF_ChangeEmployeeDataRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            42 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			dataType.Name AS TypeName,
            dataType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_ChangeEmployeeDataRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join WF_EmployeeDataType dataType on requestType.EmployeeDataTypeCode = dataType.EmployeeDataTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --WF_RenewContractRequests
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            60 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			cont.Name AS TypeName,
            cont.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_RenewContractRequests AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join Contract cont on requestType.ContractCode = cont.ContractCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --WF_NewJobRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            26 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_NewJobRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  	--WF_RehireEmployeeRequest
		     SELECT 
            request.MasterRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            69 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			cont.Name AS TypeName,
            cont.Arabic_Name AS TypeNameAr
        FROM WF_MasterRequests AS request
        INNER JOIN WF_RehireEmployeeRequest AS requestType
            ON requestType.MasterRequestCode = request.MasterRequestCode
			join Contract cont on requestType.ContractCode = cont.ContractCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  	  	--WF_OverTimeRequest
		     SELECT 
            request.OverTimeRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            3 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_OverTimeRequest AS request
        INNER JOIN WF_OvertimeType AS requestType
            ON requestType.OvertimeTypeCode = request.OvertimeTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		    	  	--WF_OverTimeRequestFinal
		     SELECT 
            request.OverTimeRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            20 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_OverTimeRequest AS request
        INNER JOIN WF_OvertimeType AS requestType
            ON requestType.OvertimeTypeCode = request.OvertimeTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		    --WF_FingerPrintRequest
		     SELECT 
            request.FingerPrintRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            6 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_FingerPrintRequest AS request
        INNER JOIN WF_FingerPrintType AS requestType
            ON requestType.FingerPrintTypeCode = request.FingerPrintTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		  	 --WF_CompensationRequestForADayOfRest
		     SELECT 
            request.CompensationRequestForADayOfRestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            21 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_CompensationRequestForADayOfRest AS request
		INNER JOIN WF_CompensationAllowanceTypes AS requestType
            ON requestType.CompensationAllowanceTypeCode = request.CompensationAllowanceTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		   --WF_CompensationRequestForADayOfRestFinal
		     SELECT 
            request.CompensationRequestForADayOfRestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            22 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_CompensationRequestForADayOfRest AS request
		INNER JOIN WF_CompensationAllowanceTypes AS requestType
            ON requestType.CompensationAllowanceTypeCode = request.CompensationAllowanceTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL 


		     --WF_LoanRequest
		     SELECT 
            request.LoanRequestId AS RequestId,
            request.RecordAddDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeId AS EmployeeCode,
            9 AS RequestTypeCode,
            request.RequestDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_LoanRequest AS request
		INNER JOIN WF_LoanType AS requestType
            ON requestType.LoanTypeCode = request.LoanTypeCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  
		     --WF_PenaltyRequest
		     SELECT 
            request.PenaltyRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            13 AS RequestTypeCode,
            request.PenaltyDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_PenaltyRequest AS request
		INNER JOIN PenaltiesList AS requestType
            ON requestType.PenaltyCode = request.PenaltyCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		  UNION ALL

		  	 --WF_PromotionRequest
		     SELECT 
            request.PromotionRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            10 AS RequestTypeCode,
            request.EffectDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_PromotionRequest AS request
		INNER JOIN PromotionReasons AS requestType
            ON requestType.PromotionReasonCode = request.PromotionReasonCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0


		  UNION ALL

		  	 --WF_AirTicketRequest
		     SELECT 
            request.AirTicketRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            4 AS RequestTypeCode,
            request.TicketFromDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_AirTicketRequest AS request
		INNER JOIN AirTicketCondition AS requestType
            ON requestType.AirTicketConditionCode = request.AirTicketConditionCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0
		  

		  UNION ALL

		  		  	 --WF_NewHireRequest
		     SELECT 
            request.NewHireRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            5 AS RequestTypeCode,
            request.CreationDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_NewHireRequest AS request
		INNER JOIN Contract AS requestType
            ON requestType.ContractCode = request.ContractCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

		 UNION ALL

		    	 --WF_ResignationRequest
		     SELECT 
            request.ResignationRequestCode AS RequestId,
            request.CreationDate AS CreationDate,
            request.DepartmentApproval,
            request.EmployeeCode AS EmployeeCode,
            7 AS RequestTypeCode,
            request.CreationDate AS RequestDate,
			requestType.Name AS TypeName,
            requestType.Arabic_Name AS TypeNameAr
        FROM WF_ResignationRequest AS request
		INNER JOIN ResignationReasons AS requestType
            ON requestType.ResignationCode = request.ResignationCode
        WHERE DepartmentApproval = 1 
          AND MainType = 0   
          AND request.IsSynced = 0

    ) AS requests
    inner JOIN WF_SyncLogException sync
        ON sync.RequestId = requests.RequestId
       AND sync.RequestTypeCode = requests.RequestTypeCode
	   inner join Employees emp on requests.EmployeeCode = emp.EmployeeCode
	   inner join WF_RequestType reqType on requests.RequestTypeCode = reqType.RequestTypeId
    WHERE (@RequestTypeCode IS NULL OR requests.RequestTypeCode = @RequestTypeCode)
	And (@EmployeeCode IS NULL OR requests.EmployeeCode = @EmployeeCode)
	)
	SELECT   *
    FROM     PaginatedResults
    WHERE    RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)
    ORDER BY RequestTypeCode DESC;
END
GO



-----------------------------------------End 2026-1-19 --------------------
-----------------------------------------Hammad 2026-1-19 ------------------
ALTER TABLE dbo.NotificationTemplateType ADD
	ReminderDuration int NULL
GO
-----------------------------------------End 2026-1-19 --------------------
-----------------------------------------End 2026-1-19 --------------------
-----------------------------------------Shehab 2026-1-19 --------------------
CREATE OR ALTER               VIEW [dbo].[WFRequestSigAutoApproveView]
AS
WITH   RequestAutoApproved
AS  (
SELECT request.UnitNumber AS RequestId,
       request.AddDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeId AS EmployeeCode,
       11 AS RequestTypeCode,
       request.FromDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_VacationRequest AS request
       INNER JOIN VacationType AS requestType
       ON requestType.VacationCode = request.VacationTypeId
       INNER JOIN WF_VacationRequestSignature AS Sig
       ON request.VacationRequestId = Sig.VacationRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.PermissionRequestId AS RequestId,
       AddDate AS CreationDate,
       DepartmentApproval,
       EmployeeId AS EmployeeCode,
       2 AS RequestTypeCode,
       request.PermissionsDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_PermissionRequest AS request
       INNER JOIN TimePermissionsTypes AS requestType
       ON requestType.TimePermissionCode = request.TimePermissionCode
       INNER JOIN WF_PermissionRequestSignature AS Sig
       ON request.PermissionRequestId = Sig.PermissionRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.OverTimeRequestId AS RequestId,
       request.RecordAddDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeId AS EmployeeCode,
       3 AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_OverTimeRequest AS request
       INNER JOIN WF_OvertimeType AS requestType
       ON requestType.OvertimeTypeCode = request.OvertimeTypeCode
       INNER JOIN WF_OverTimeRequestSignature AS Sig
       ON request.OverTimeRequestId = Sig.OverTimeRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.AirTicketRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       4 AS RequestTypeCode,
       request.TicketFromDate AS RequestDate,
       '' AS TypeName,
       '' AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_AirTicketRequest AS request
       INNER JOIN WF_AirTicketRequestSignature AS Sig
       ON request.AirTicketRequestCode = Sig.AirTicketRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.FingerPrintRequestId AS RequestId,
       request.RecordAddDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeId AS EmployeeCode,
       6 AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_FingerPrintRequest AS request
       INNER JOIN WF_FingerPrintType AS requestType
       ON requestType.FingerPrintTypeCode = request.FingerPrintTypeCode
       INNER JOIN WF_FingerPrintRequestSignature AS Sig
       ON request.FingerPrintRequestId = Sig.FingerPrintRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.ResignationRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       7 AS RequestTypeCode,
       request.ResignationDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_ResignationRequest AS request
       INNER JOIN ResignationReasons AS requestType
       ON requestType.ResignationCode = request.ResignationCode
       INNER JOIN WF_ResignationRequestSignature AS Sig
       ON request.ResignationRequestCode = Sig.ResignationRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.LoanRequestId AS RequestId,
       request.RecordAddDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeId AS EmployeeCode,
       9 AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_LoanRequest AS request
       INNER JOIN WF_LoanType AS requestType
       ON requestType.LoanTypeCode = request.LoanTypeCode
       INNER JOIN WF_LoanRequestSignature AS Sig
       ON request.LoanRequestId = Sig.LoanRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.PromotionRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       10 AS RequestTypeCode,
       request.EffectDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_PromotionRequest AS request
       INNER JOIN PromotionReasons AS requestType
       ON requestType.PromotionReasonCode = request.PromotionReasonCode
       INNER JOIN WF_PromotionRequestSignature AS Sig
       ON request.PromotionRequestCode = Sig.PromotionRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.PenaltyRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       13 AS RequestTypeCode,
       request.PenaltyDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate 
FROM   WF_PenaltyRequest AS request
       INNER JOIN PenaltiesList AS requestType
       ON requestType.PenaltyCode = request.PenaltyCode
       INNER JOIN WF_PenaltyRequestSignature AS Sig
       ON request.PenaltyRequestCode = Sig.PenaltyRequestCode
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.ChangeDayoffRequestId AS RequestId,
       request.RecordAddDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeId AS EmployeeCode,
       18 AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       '' AS TypeName,
       '' AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_ChangeDayoffRequest AS request
       INNER JOIN WF_ChangeDayoffRequestSignature AS Sig
       ON request.ChangeDayoffRequestId = Sig.ChangeDayoffRequestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.CompensationRequestForADayOfRestId AS RequestId,
       request.RecordAddDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       21 AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       requestType.Name AS TypeName,
       requestType.Arabic_Name AS TypeNameAr,
       Sig.UserId,Sig.SignatureDate
FROM   WF_CompensationRequestForADayOfRest AS request
       INNER JOIN WF_CompensationAllowanceTypes AS requestType
       ON requestType.CompensationAllowanceTypeCode = request.CompensationAllowanceTypeCode
       INNER JOIN WF_CompensationRequestForADayOfRestSignature AS Sig
       ON request.CompensationRequestForADayOfRestId = Sig.CompensationRequestForADayOfRestId
WHERE  Sig.SignatureComment = 'Auto Approved By System'

UNION ALL

SELECT request.MasterRequestCode AS RequestId,
       request.CreationDate AS CreationDate,
       request.DepartmentApproval,
       request.EmployeeCode AS EmployeeCode,
       request.RequestTypeCode AS RequestTypeCode,
       request.RequestDate AS RequestDate,
       '' AS TypeName,
       '' AS TypeNameAr,
        Sig.UserCode UserId,Sig.SignatureDate
FROM   WF_MasterRequests AS request
       INNER JOIN WF_MasterRequestsSignature AS Sig
       ON request.MasterRequestCode = Sig.MasterRequestCode
WHERE Sig.SignatureComment = 'Auto Approved By System'
)


SELECT RequestAutoApproved.*,
       emp.Name AS EmployeeNameEn,
       emp.Arabic_Name AS EmployeeNameAR,
	   SecurityUser.IsActive as IsActive,
       requestType.RequestTypeNameEn AS RequestTypeNameEn,
       requestType.RequestTypeName AS RequestTypeNameAR,
	          SecurityUser.Name AS SecurityUserNameEn,
       SecurityUser.Arabic_Name AS SecurityUserNameAR,
        SecurityUser.UserName
FROM   RequestAutoApproved
       INNER JOIN
       Employees AS emp
       ON emp.EmployeeCode = RequestAutoApproved.EmployeeCode
       INNER JOIN
       WF_RequestType AS requestType
       ON requestType.RequestTypeId = RequestAutoApproved.RequestTypeCode
	    INNER JOIN
       SecurityUser 
       ON SecurityUser.SecurityUserCode = RequestAutoApproved.UserId;
GO



-----------------------------------------End 2026-1-19 --------------------
-----------------------------------------Shehab 2026-1-19 --------------------
CREATE OR ALTER      PROCEDURE [dbo].[GetPaginatedRequestSigAutoApprove]
@PageNumber INT=1, @PageSize INT=10, @StartDate DATETIME=NULL, @EndDate DATETIME=NULL, @RequestTypeCode INT=NULL, @EmployeeCode INT=NULL, @UserId INT=NULL,@IsActive bit = null
AS
BEGIN
    SET NOCOUNT ON;
    WITH     PaginatedResults
    AS       (SELECT *,
                     COUNT(*) OVER () AS TotalRecords,
                      COUNT(*) OVER (PARTITION BY UserId) AS UserTotalCount,
                     CEILING(COUNT(*) OVER () * 1.0 / @PageSize) AS TotalPages,
                     ROW_NUMBER() OVER (ORDER BY UserId DESC) AS RowNum
              FROM   WFRequestSigAutoApproveView
              WHERE  (@StartDate IS NULL
                      OR SignatureDate >= @StartDate)
                     AND (@EndDate IS NULL
                          OR SignatureDate <= @EndDate)
                     AND (@RequestTypeCode IS NULL
                          OR RequestTypeCode = @RequestTypeCode)
					AND (@UserId IS NULL
                          OR UserId = @UserId)
                     AND (@EmployeeCode IS NULL
                          OR EmployeeCode = @EmployeeCode)
					AND (@IsActive IS NULL
                          OR IsActive = @IsActive)
						  )
    SELECT   *
    FROM     PaginatedResults
    WHERE    RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize)
    ORDER BY UserId DESC;
END
GO



-----------------------------------------End 2026-1-19 -