﻿--------------------------End 2026-03-01---------------------------
INSERT [dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId], [ConfigDescription])
VALUES (N'MinVacationEndBeforeResidenceExpiryDays', N'Minimum Vacation End Date Before Residence Expiry (Days)', N'الحد الأدنى لتاريخ نهاية الإجازة قبل انتهاء الإقامة (بالأيام) ', N'10', 2, 37, 2, 1, NULL, NULL, 11, N'الحد الأدنى لتاريخ نهاية الإجازة قبل انتهاء الإقامة (بالأيام)')
GO
--------------------------End 2026-03-01---------------------------
--------------------------mostafa 2026-03-02---------------------------

update SystemMenu set ImageURL='fa-solid fa-person-military-rifle'  where SystemMenuCode=1056
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-file-signature' WHERE SystemMenuCode = 1174;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-calendar-check' WHERE SystemMenuCode = 1180;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-gear' WHERE SystemMenuCode = 1205;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-file-invoice-dollar' WHERE SystemMenuCode = 1207;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-comment-dots' WHERE SystemMenuCode = 1209;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-chart-line' WHERE SystemMenuCode IN (1211,1213);
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-chart-pie' WHERE SystemMenuCode IN (1212,1214);
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-money-bill-trend-up' WHERE SystemMenuCode = 1224;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-money-bill-transfer' WHERE SystemMenuCode = 1225;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-money-bill-wave' WHERE SystemMenuCode = 1227;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-building-circle-check' WHERE SystemMenuCode = 1233;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-id-card' WHERE SystemMenuCode = 1236;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-id-badge' WHERE SystemMenuCode = 1238;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-earth-americas' WHERE SystemMenuCode IN (1242,1244);
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-flag' WHERE SystemMenuCode = 1243;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-file-contract' WHERE SystemMenuCode = 1246;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-boxes-stacked' WHERE SystemMenuCode = 1249;
UPDATE SystemMenu SET ImageURL = 'fa-solid fa-gauge-high' WHERE SystemMenuCode IN (1250,1251);
--------------------------ENd 2026-03-02---------------------------
----------------------------------------- AK 2026-03-02 -----------------------
INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1250,N'Dashboard',N'اللوحة الرئيسية',583,N'/GenerlLedger/Dashboard',N'Index',N'fa fa-thumb-tack',N'get',0,1,1)
GO
INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1251,N'Dashboard',N'اللوحة الرئيسية',1196,N'/SAL/Dashboard',N'Index',N'fa fa-thumb-tack',N'get',0,1,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1250,18)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1251,21)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (23228,N'اللوحة الرئيسية',N'Dashboard',1250,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (23229,N'عرض اللوحة الرئيسية',N'View Dashboard',1250,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (23230,N'مؤشر أرصده البنوك',N'Bank Balances Indicator',1250,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (23231,N'مؤشر المصروفات',N'Expenses Indicator',1250,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (23232,N'مديونية أكبر 10 عملاء',N'Top 10 Customers Indebtedness',1250,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (23233,N'دائنية أكبر 10 موردين',N'Top 10 Suppliers Credit Balances',1250,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (22115,N'اللوحة الرئيسية',N'Dashboard',1251,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (22116,N'عرض اللوحة الرئيسية',N'View Dashboard',1251,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (22117,N'أكثر 10 أصناف مبيعا',N'Top 10 Best-Selling Items',1251,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (22118,N'أكثر 10 أصناف ركودا',N'Top 10 Slow-Moving Items',1251,2)
GO
-----------------------------------------END AK 2026-03-02 --------------------
--------------------------Ali 2026-03-02---------------------------

 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription])
VALUES(N'WFAllowAddProjectAndOutFingerPrint',N'Ability to integrate Out and entry fingerprint',N'امكانية دمج بصمة الدخول والخروج',N'false',2,2,1,1,Null,Null,6,N'امكانية دمج بصمة الدخول والخروج  و المشروع والوحدات والمهام')
Go
--------------------------End 2026-03-02---------------------------
--------------------------Hammas 2026-03-04------------------------


 
Alter VIEW [dbo].[ArchiveWFRequestView]
AS
WITH   RequestFiles
AS     (SELECT   RequestId,
                 RequestTypeCode,
                 STRING_AGG([FileName], '|') WITHIN GROUP (ORDER BY [FileName]) AS CombinedFileNames
        FROM     WF_RequestsFile
        GROUP BY RequestId, RequestTypeCode),
       MasterRequestFiles
AS     (SELECT   MasterRequestCode,
                 STRING_AGG([FileName], '|') WITHIN GROUP (ORDER BY [FileName]) AS CombinedFileNames
        FROM     WF_MasterRequestsFiles
        GROUP BY MasterRequestCode),
       ArchiveWFRequest
AS     (SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               request.UnitNumber AS RequestId,
               request.AddDate AS CreationDate,
               request.DepartmentApproval,
               request.EmployeeId AS EmployeeCode,
               11 AS RequestTypeCode,
               request.FromDate,
               request.ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_VacationRequest AS request
               INNER JOIN
               VacationType AS requestType
               ON requestType.VacationCode = request.VacationTypeId
               LEFT OUTER JOIN
               RequestFiles
               ON request.UnitNumber = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 11
        WHERE  request.DepartmentApproval = 1
               AND (UnitNumber IN (SELECT RequestId
                                   FROM   RequestFiles
                                   WHERE  RequestTypeCode = 11)
                    OR request.[FileName] IS NOT NULL)
               AND request.VacationRequestId IN (SELECT VacationRequestId
                                                 FROM   WF_VacationRequestSignature AS sig
                                                 WHERE  sig.VacationRequestId = request.VacationRequestId)
        UNION ALL
			SELECT  RequestFiles.CombinedFileNames AS FileName,
               request.UnitNumber AS RequestId,
               request.AddDate AS CreationDate,
               request.DepartmentApproval,
               request.EmployeeId AS EmployeeCode,
               RequestFiles.RequestTypeCode AS RequestTypeCode,
               request.FromDate,
               request.ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_VacationRequest AS request
               INNER JOIN
               VacationType AS requestType
               ON requestType.VacationCode = request.VacationTypeId
               INNER JOIN
               RequestFiles
               ON request.UnitNumber = RequestFiles.RequestId
                  AND  RequestFiles.RequestTypeCode = 17
			

        WHERE  request.DisclaimerDepartmentApproval = 1
               AND (UnitNumber IN (SELECT RequestId
                                   FROM   RequestFiles
                                   WHERE  RequestTypeCode = 17))

								      AND    (UnitNumber IN (SELECT RequestCode
                                           FROM   WF_DisclaimerRequestSignature
                                           WHERE  RequestTypeId = 11))

               AND request.VacationRequestId IN (SELECT VacationRequestId
                                                 FROM   WF_VacationRequestSignature AS sig
                                                 WHERE  sig.VacationRequestId = request.VacationRequestId)
        UNION ALL

        SELECT COALESCE (request.[AttachementFilePath], RequestFiles.CombinedFileNames) AS FileName,
               PermissionRequestId AS RequestId,
               AddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               2 AS RequestTypeCode,
               request.PermissionsDate AS FromDate,
               request.PermissionsDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_PermissionRequest AS request
               INNER JOIN
               TimePermissionsTypes AS requestType
               ON requestType.TimePermissionCode = request.TimePermissionCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.PermissionRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 2
        WHERE  (PermissionRequestId IN (SELECT RequestId
                                        FROM   RequestFiles
                                        WHERE  RequestTypeCode = 2)
                OR 
				(request.[AttachementFilePath] IS NOT NULL 
               AND request.[AttachementFilePath] != ''))

               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               OverTimeRequestId AS RequestId,
               RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               3 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDateTo AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_OverTimeRequest AS request
               INNER JOIN
               WF_OvertimeType AS requestType
               ON requestType.OvertimeTypeCode = request.OvertimeTypeCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.OverTimeRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 3
        WHERE  (OverTimeRequestId IN (SELECT RequestId
                                      FROM   RequestFiles
                                      WHERE  RequestTypeCode = 3)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               AirTicketRequestCode AS RequestId,
               CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               4 AS RequestTypeCode,
               request.TicketFromDate AS FromDate,
               request.TicketToDate AS ToDate,
               '' AS TypeName,
               '' AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_AirTicketRequest AS request
               LEFT OUTER JOIN
               RequestFiles
               ON request.AirTicketRequestCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 4
        WHERE  (AirTicketRequestCode IN (SELECT RequestId
                                         FROM   RequestFiles
                                         WHERE  RequestTypeCode = 4)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               FingerPrintRequestId AS RequestId,
               RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               6 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_FingerPrintRequest AS request
               INNER JOIN
               WF_FingerPrintType AS requestType
               ON requestType.FingerPrintTypeCode = request.FingerPrintTypeCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.FingerPrintRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 6
        WHERE  (FingerPrintRequestId IN (SELECT RequestId
                                         FROM   RequestFiles
                                         WHERE  RequestTypeCode = 6)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[AttachementFilePath], RequestFiles.CombinedFileNames) AS FileName,
               ResignationRequestCode AS RequestId,
               CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               7 AS RequestTypeCode,
               request.ResignationDate AS FromDate,
               request.ResignationDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_ResignationRequest AS request
               INNER JOIN
               ResignationReasons AS requestType
               ON requestType.ResignationCode = request.ResignationCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.ResignationCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 7
        WHERE  (ResignationRequestCode IN (SELECT RequestId
                                           FROM   RequestFiles
                                           WHERE  RequestTypeCode = 7)
                OR request.[AttachementFilePath] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
		 SELECT RequestFiles.CombinedFileNames AS FileName,
               ResignationRequestCode AS RequestId,
               CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               17 AS RequestTypeCode,
               request.ResignationDate AS FromDate,
               request.ResignationDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_ResignationRequest AS request
               INNER JOIN
               ResignationReasons AS requestType
               ON requestType.ResignationCode = request.ResignationCode
              INNER JOIN
               RequestFiles
               ON request.ResignationRequestCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode =17
				   

        WHERE  (ResignationRequestCode IN (SELECT RequestId
                                           FROM   RequestFiles
                                           WHERE  RequestTypeCode = 17))

										   and    (ResignationRequestCode IN (SELECT RequestCode
                                           FROM   WF_DisclaimerRequestSignature
                                           WHERE  RequestTypeId = 7))
               AND DisclaimerDepartmentApproval = 1
        UNION ALL

        SELECT RequestFiles.CombinedFileNames AS FileName,
               LoanRequestId AS RequestId,
               RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               9 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_LoanRequest AS request
               INNER JOIN
               WF_LoanType AS requestType
               ON requestType.LoanTypeCode = request.LoanTypeCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.LoanRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 9
        WHERE  (LoanRequestId IN (SELECT RequestId
                                  FROM   RequestFiles
                                  WHERE  RequestTypeCode = 9))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], RequestFiles.CombinedFileNames) AS FileName,
               PromotionRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               10 AS RequestTypeCode,
               request.EffectDate AS FromDate,
               request.EffectDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_PromotionRequest AS request
               INNER JOIN
               PromotionReasons AS requestType
               ON requestType.PromotionReasonCode = request.PromotionReasonCode
               LEFT OUTER JOIN
               RequestFiles
               ON request.PromotionRequestCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 10
        WHERE  (PromotionRequestCode IN (SELECT RequestId
                                         FROM   RequestFiles
                                         WHERE  RequestTypeCode = 10)
                OR request.[FileName] IS NOT NULL)
               AND DepartmentApproval = 1
        UNION ALL
        SELECT RequestFiles.CombinedFileNames AS FileName,
               PenaltyRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               13 AS RequestTypeCode,
               request.PenaltyDate AS FromDate,
               request.PenaltyDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_PenaltyRequest AS request
               INNER JOIN
               PenaltiesList AS requestType
               ON requestType.PenaltyCode = request.PenaltyCode
               INNER JOIN
               RequestFiles
               ON request.PenaltyRequestCode = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 13
        WHERE  (PenaltyRequestCode IN (SELECT RequestId
                                       FROM   WF_RequestsFile
                                       WHERE  RequestTypeCode = 13))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT RequestFiles.CombinedFileNames AS FileName,
               ChangeDayoffRequestId AS RequestId,
               request.RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeId AS EmployeeCode,
               18 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               '' AS TypeName,
               '' AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_ChangeDayoffRequest AS request
               INNER JOIN
               RequestFiles
               ON request.ChangeDayoffRequestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 18
        WHERE  (ChangeDayoffRequestId IN (SELECT RequestId
                                          FROM   WF_RequestsFile
                                          WHERE  RequestTypeCode = 18))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT RequestFiles.CombinedFileNames AS FileName,
               CompensationRequestForADayOfRestId AS RequestId,
               request.RecordAddDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               21 AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_CompensationRequestForADayOfRest AS request
               INNER JOIN
               WF_CompensationAllowanceTypes AS requestType
               ON requestType.CompensationAllowanceTypeCode = request.CompensationAllowanceTypeCode
               INNER JOIN
               RequestFiles
               ON request.CompensationRequestForADayOfRestId = RequestFiles.RequestId
                  AND RequestFiles.RequestTypeCode = 21
        WHERE  (CompensationRequestForADayOfRestId IN (SELECT RequestId
                                                       FROM   WF_RequestsFile
                                                       WHERE  RequestTypeCode = 21))
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_GeneralsRequest AS generalRequest
               ON generalRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_GeneralRequestType AS requestType
               ON requestType.GeneralRequestTypeCode = generalRequest.GeneralRequestTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode = 19
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_BonusesRequest AS bonusesRequest
               ON bonusesRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_BonusType AS requestType
               ON requestType.BonusTypeCode = bonusesRequest.BonusTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode = 15
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_MonthlyEffectsRequest AS monthlyEffectsRequest
               ON monthlyEffectsRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_MonthlyEffectType AS requestType
               ON requestType.MonthlyEffectTypeCode = monthlyEffectsRequest.MonthlyEffectTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode = 27
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_ResidenceRequest AS residenceRequest
               ON residenceRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_ResidenceTypes AS requestType
               ON requestType.ResidenceTypeCode = residenceRequest.ResidenceTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 29
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_FinancialPaymentRequest AS financialPaymentRequest
               ON financialPaymentRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_FinancialPaymentType AS requestType
               ON requestType.FinancialPaymentTypeCode = financialPaymentRequest.FinancialPaymentTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 30
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_IntroductionletterRequest AS introductionletterRequest
               ON introductionletterRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_letterTypes AS requestType
               ON requestType.letterTypeCode = introductionletterRequest.letterTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 50
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_TimeSheetRequestDetail AS timeSheetRequest
               ON timeSheetRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_MonthlyEffectType AS requestType
               ON requestType.MonthlyEffectTypeCode = timeSheetRequest.MonthlyEffectTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 55
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_AssignmentRequests AS assignmentRequest
               ON assignmentRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               Contract AS requestType
               ON requestType.ContractCode = assignmentRequest.SubContractCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 59
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_RenewContractRequests AS renewContractRequest
               ON renewContractRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               Contract AS requestType
               ON requestType.ContractCode = renewContractRequest.ContractCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 60
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_TransferRequest AS transferRequest
               ON transferRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               PromotionReasons AS requestType
               ON requestType.PromotionReasonCode = transferRequest.TransferType
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 62
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               requestType.Name AS TypeName,
               requestType.Arabic_Name AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               INNER JOIN
               WF_SocialCareRequest AS socialCareRequest
               ON socialCareRequest.MasterRequestCode = request.MasterRequestCode
               INNER JOIN
               WF_SocialCareTypes AS requestType
               ON requestType.SocialCareTypeCode = socialCareRequest.SocialCareTypeCode
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND RequestTypeCode = 65
               AND DepartmentApproval = 1
        UNION ALL
        SELECT COALESCE (request.[FileName], MasterRequestFiles.CombinedFileNames) AS FileName,
               request.MasterRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               request.RequestDate AS FromDate,
               request.RequestDate AS ToDate,
               '' AS TypeName,
               '' AS TypeNameAr,
               2 AS ArchTypeCode
        FROM   WF_MasterRequests AS request
               LEFT OUTER JOIN
               MasterRequestFiles
               ON request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
        WHERE  EmployeeCode IS NOT NULL
               AND (request.MasterRequestCode = MasterRequestFiles.MasterRequestCode
                    OR [FileName] IS NOT NULL)
               AND DepartmentApproval = 1
               AND RequestTypeCode NOT IN (19, 15, 27, 29, 50, 55, 59, 60, 62, 65)
        UNION ALL
        SELECT request.[AirTicketAttachment] AS FileName,
               request.FollowRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               1 AS DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               (CASE WHEN RequestTypeCode = 11 THEN vacRequest.FromDate ELSE resignationRequest.ResignationDate END) AS FromDate,
               (CASE WHEN RequestTypeCode = 11 THEN vacRequest.ToDate ELSE resignationRequest.ResignationDate END) AS ToDate,
               'Follow Request - Air Ticket' AS TypeName,
               'متابعة الطلب - تذكرة طيران' AS TypeNameAr,
               3 AS ArchTypeCode
        FROM   WF_FollowRequest AS request
               LEFT OUTER JOIN
               WF_FollowVacationRequest AS vacRequest
               ON vacRequest.FollowRequestCode = request.FollowRequestCode
               LEFT OUTER JOIN
               WF_FollowResignationRequest AS resignationRequest
               ON resignationRequest.FollowRequestCode = request.FollowRequestCode
        WHERE  request.[AirTicketAttachment] IS NOT NULL
        UNION ALL
        SELECT request.[VisaAttachment] AS FileName,
               request.FollowRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               1 AS DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               (CASE WHEN RequestTypeCode = 11 THEN vacRequest.FromDate ELSE resignationRequest.ResignationDate END) AS FromDate,
               (CASE WHEN RequestTypeCode = 11 THEN vacRequest.ToDate ELSE resignationRequest.ResignationDate END) AS ToDate,
               'Follow Request - Visa' AS TypeName,
               'متابعة الطلب - تاشيرة' AS TypeNameAr,
               3 AS ArchTypeCode
        FROM   WF_FollowRequest AS request
               LEFT OUTER JOIN
               WF_FollowVacationRequest AS vacRequest
               ON vacRequest.FollowRequestCode = request.FollowRequestCode
               LEFT OUTER JOIN
               WF_FollowResignationRequest AS resignationRequest
               ON resignationRequest.FollowRequestCode = request.FollowRequestCode
        WHERE  request.[VisaAttachment] IS NOT NULL
        UNION ALL
        SELECT request.[SalaryAdvanceAttachment] AS FileName,
               request.FollowRequestCode AS RequestId,
               request.CreationDate AS CreationDate,
               1 AS DepartmentApproval,
               EmployeeCode AS EmployeeCode,
               RequestTypeCode AS RequestTypeCode,
               (CASE WHEN RequestTypeCode = 11 THEN vacRequest.FromDate ELSE resignationRequest.ResignationDate END) AS FromDate,
               (CASE WHEN RequestTypeCode = 11 THEN vacRequest.ToDate ELSE resignationRequest.ResignationDate END) AS ToDate,
               'Follow Request - Salary Advance' AS TypeName,
               'متابعة الطلب - تصفية راتب' AS TypeNameAr,
               3 AS ArchTypeCode
        FROM   WF_FollowRequest AS request
               LEFT OUTER JOIN
               WF_FollowVacationRequest AS vacRequest
               ON vacRequest.FollowRequestCode = request.FollowRequestCode
               LEFT OUTER JOIN
               WF_FollowResignationRequest AS resignationRequest
               ON resignationRequest.FollowRequestCode = request.FollowRequestCode
        WHERE  request.[SalaryAdvanceAttachment] IS NOT NULL)
	

SELECT ArchiveWFRequest.*,
       emp.Name AS EmployeeNameEn,
       emp.Arabic_Name AS EmployeeNameAR,
       requestType.RequestTypeNameEn AS RequestTypeNameEn,
       requestType.RequestTypeName AS RequestTypeNameAR
FROM   ArchiveWFRequest
       INNER JOIN
       Employees AS emp
       ON emp.EmployeeCode = ArchiveWFRequest.EmployeeCode
       INNER JOIN
       WF_RequestType AS requestType
       ON requestType.RequestTypeId = ArchiveWFRequest.RequestTypeCode;
GO


--------------------------End 2026-03-04---------------------------
