﻿----------------------------------------- Shehab 2026-06-2 --------------

INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (15703, N'اعادة ارسال العرض الوظيفي', N'ReSend Job Offer', 1246, 2)
GO
----------------------------------------- End 2026-06-2 --------------
----------------------------------------- AK 2026-06-03 -----------------
INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1263,N'Copy User Privileges',N'نسخ صلاحيات المستخدمين',7,N'/CopyUserPrivileges',N'Index',N'fas fa-users-cog',N'get',2,1,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1263,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1263,2)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1263,3)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (1452,N'نسخ صلاحيات المستخدمين',N'Copy User Privileges',1263,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (1453,N'حفظ نسخ صلاحيات المستخدمين',N'Save Copy User Privileges',1263,2)
GO
CREATE PROC [dbo].[CopySecurityUserPrivileges]
	@fromUserCode int, @toUserCode int ,@locationCode tinyint, @creationDate DateTime, @createdBy int, @isCopySystemMenuPrivilege bit, @isCopySectionPrivilege bit
	AS
	BEGIN
	IF(@isCopySystemMenuPrivilege = 1)
	BEGIN
	DELETE SecurityUserDetail WHERE SecurityUserID=@toUserCode
	INSERT INTO SecurityUserDetail (SecurityUserID,PrivilegeID,CreationDate,CreatedBy)
	SELECT @toUserCode,PrivilegeID,@creationDate,@createdBy FROM SecurityUserDetail where SecurityUserID=@fromUserCode
	UNION
	SELECT @toUserCode,PrivilegeID,@creationDate,@createdBy FROM SecurityGroupDetail where SecurityGroupID IN (SELECT SecurityGroupCode FROM SecurityUser where SecurityUserCode=@fromUserCode)
	END
	IF(@isCopySectionPrivilege = 1)
	BEGIN
	-- SecurityUserSection
	DELETE SecurityUserSection WHERE SecurityUserCode=@toUserCode
	INSERT INTO SecurityUserSection (SecurityUserCode,SectionCode,LocationCode,FullOption,Salaries)
	SELECT @toUserCode,SectionCode,@locationCode,FullOption,Salaries FROM SecurityUserSection where SecurityUserCode=@fromUserCode AND LocationCode=@locationCode
	-- SecurityUserEmployee
	DELETE SecurityUserEmployee WHERE SecurityUserCode=@toUserCode
	INSERT INTO SecurityUserEmployee (SecurityUserCode,EmployeeCode,LocationCode,FullOption)
	SELECT @toUserCode,EmployeeCode,@locationCode,FullOption FROM SecurityUserEmployee where SecurityUserCode=@fromUserCode AND LocationCode=@locationCode
	END
	END
	GO
----------------------------------------- End 2026-06-03 -----------------
----------------------------------------- Hammad 2026-06-03 --------------

----------------------------------------- Ali 2026-06-3 --------------

INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (15704, N'ايقاف الطلب واضافة طلب جديد', N'Refused Request and add new ', 1246, 2)
GO
update WF_JobOfferRequests set IsFromRecruitment=1 where  ForeignerUserCode in(select ForeignerUserCode from ForeignerUser where IsFromWFRequest=1)
Go
----------------------------------------- End 2026-06-3 --------------



 
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_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 = 25
               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_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-06-03 --------------