ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_TRANSFER_Upd] @p_REQ_ID varchar(15) = NULL, @p_TYPE_PROCESS varchar(50) = NULL, @p_MAKERID varchar(100)= NULL, @p_AUTH_STATUS VARCHAR(1) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL, @p_APPROVE_DT VARCHAR(50) = NULL, @p_USERNAME varchar(100) = NULL, @p_NOTES NVARCHAR(MAX)= NULL, @p_DVKD_USER_APP NVARCHAR(500)= NULL AS BEGIN DECLARE @NOTIFATION NVARCHAR(100) = NULL, @BRANCHID VARCHAR(20)= NULL, @DEP_ID VARCHAR(20), @NOTIFY_TO_USER VARCHAR(MAX) = '', @COMPLETE BIT = 0, @REQ_TYPE VARCHAR(20) = (SELECT TOP 1 trsd.REQ_TYPE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @p_REQ_ID), @CURR_PROCESS VARCHAR(50) = (SELECT TOP 1 A.PROCESS_ID FROM PL_REQUEST_PROCESS A WHERE A.REQ_ID = @p_REQ_ID AND A.STATUS = 'C') SELECT @BRANCHID = tu.TLSUBBRID, @DEP_ID = tu.DEP_ID FROM TL_USER tu WHERE tu.TLNANME = @p_CHECKER_ID DECLARE @ASSET_ID_LST VARCHAR(MAX), @CURRENT_TRANS_ERROR NVARCHAR(MAX) BEGIN TRANSACTION IF(EXISTS(SELECT REQ_ID FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND STATUS = 'REJECT')) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Phiếu yêu cầu đang bị trả về' ErrorDesc RETURN '-1' END -- GỬI TRƯỞNG ĐƠN VỊ IF(@p_TYPE_PROCESS='S_DVKD') BEGIN --PHUCVH 26/12/22 RÀNG BUỘC KHÔNG CHO TẠO 2 PYC CÙNG 1 TÀI SẢN CHƯA HOÀN THÀNH SỬA CHỮA --&& TS ĐÃ ĐƯỢC THÊM Ở LƯỚI TS CẦN THAY THẾ TRÊN PYC CẤP PHÁT KHÔNG ĐƯỢC YÊU CẦU SỬA CHỮA IF(@REQ_TYPE = 'SC') BEGIN DECLARE @MESSAGE_VALIDATION NVARCHAR(MAX) DECLARE @TABLE_ASSCODE_VALIDATION TABLE (ASSET_CODE VARCHAR(100), REQ_CODE VARCHAR(100)) --RÀNG BUỘC TS ĐÃ ĐƯỢC THÊM Ở LƯỚI TS CẦN THAY THẾ TRÊN PYC CẤP PHÁT KHÔNG ĐƯỢC YÊU CẦU SỬA CHỮA INSERT INTO @TABLE_ASSCODE_VALIDATION SELECT ISNULL(G.ASSET_CODE,G.ASS_CODE_TMP) ASSET_CODE, F.REQ_CODE FROM ( SELECT B.ASS_ID, C.REQ_CODE FROM TR_REQUEST_SHOP_DOC_DT B LEFT JOIN TR_REQUEST_SHOP_DOC C ON B.REQ_DOC_ID = C.REQ_ID WHERE C.AUTH_STATUS NOT IN ('E','R','D') AND B.REQ_DT_TYPE = 'ASSET_BROKEN' AND B.ASS_ID IN (SELECT D.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT D WHERE D.REQ_DOC_ID = @p_REQ_ID) GROUP BY B.ASS_ID, C.REQ_CODE ) F LEFT JOIN ASS_MASTER G ON F.ASS_ID = G.ASSET_ID IF(EXISTS(SELECT 1 FROM @TABLE_ASSCODE_VALIDATION)) BEGIN SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN '' ELSE '|' END + N'Tài sản ' + C.ASSET_CODE + N' đã được yêu cầu thay thế trong phiếu số' + C.REQ_CODE FROM ( SELECT ASSET_CODE,REQ_CODE FROM @TABLE_ASSCODE_VALIDATION) C FOR XML PATH ('')) , '|', '
')) ROLLBACK TRANSACTION SELECT '-1' as Result, @MESSAGE_VALIDATION ErrorDesc RETURN '-1' END --RÀNG BUỘC KHÔNG CHO TẠO 2 PYC CÙNG 1 TÀI SẢN CHƯA HOÀN THÀNH SỬA CHỮA INSERT INTO @TABLE_ASSCODE_VALIDATION SELECT ISNULL(C.ASSET_CODE,C.ASS_CODE_TMP),B.REQ_CODE FROM TR_REQUEST_SHOP_DOC_DT A LEFT JOIN TR_REQUEST_SHOP_DOC B ON A.REQ_DOC_ID = B.REQ_ID LEFT JOIN ASS_MASTER C ON A.ASS_ID = C.ASSET_ID WHERE A.ASS_ID IN (SELECT C.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT C WHERE C.REQ_DOC_ID = @p_REQ_ID) AND B.IS_DONE = '0' AND B.REQ_TYPE = 'SC' AND B.AUTH_STATUS NOT IN ('E','D','R') AND A.REQ_DOC_ID <> @p_REQ_ID IF(EXISTS(SELECT 1 FROM @TABLE_ASSCODE_VALIDATION)) BEGIN SET @MESSAGE_VALIDATION = (SELECT REPLACE((SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN '' ELSE '|' END + N'Tài sản ' + C.ASSET_CODE + N' trong ' + C.REQ_CODE + N' chưa được hoàn thành sửa chữa' FROM (SELECT ASSET_CODE,REQ_CODE FROM @TABLE_ASSCODE_VALIDATION) C FOR XML PATH ('')) , '|', '
')) ROLLBACK TRANSACTION SELECT '-1' as Result, @MESSAGE_VALIDATION ErrorDesc RETURN '-1' END END IF(@REQ_TYPE = 'TH') BEGIN -- KIỂM TRA TÀI SẢN ĐANG TREO TRONG GIAO DỊCH KHÁC SET @ASSET_ID_LST = (SELECT B.ASSET_ID + '|' FROM (SELECT A.ASSET_ID FROM TR_REQUEST_DOC_ASSET_DT A where A.REQ_DOC_ID = @p_REQ_ID) B FOR XML PATH ('')) SELECT @CURRENT_TRANS_ERROR = dbo.ASSET_CHECK_CURRENT_TRANS(@ASSET_ID_LST) IF(@CURRENT_TRANS_ERROR IS NOT NULL AND @CURRENT_TRANS_ERROR <> '') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, @CURRENT_TRANS_ERROR ErrorDesc RETURN '-1' END END ELSE BEGIN -- KIỂM TRA TÀI SẢN ĐANG TREO TRONG GIAO DỊCH KHÁC SET @ASSET_ID_LST = (SELECT B.ASS_ID + '|' FROM (SELECT A.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT A where A.REQ_DOC_ID = @p_REQ_ID) B FOR XML PATH ('')) SELECT @CURRENT_TRANS_ERROR = dbo.ASSET_CHECK_CURRENT_TRANS(@ASSET_ID_LST) IF(@CURRENT_TRANS_ERROR IS NOT NULL AND @CURRENT_TRANS_ERROR <> '') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, @CURRENT_TRANS_ERROR ErrorDesc RETURN '-1' END END --PHUCVH 09/11/22 UPDATE TÀI SẢN TREO GIAO DỊCH IF(@REQ_TYPE = 'TH') BEGIN UPDATE ASS_MASTER SET CURRENT_TRANS = @p_REQ_ID, CURRENT_TRANS_TYPE = (SELECT A.REQ_TYPE FROM TR_REQUEST_SHOP_DOC A WHERE A.REQ_ID = @p_REQ_ID) WHERE ASSET_ID IN (SELECT B.ASSET_ID FROM TR_REQUEST_DOC_ASSET_DT B WHERE B.REQ_DOC_ID = @p_REQ_ID) END ELSE BEGIN UPDATE ASS_MASTER SET CURRENT_TRANS = @p_REQ_ID, CURRENT_TRANS_TYPE = (SELECT A.REQ_TYPE FROM TR_REQUEST_SHOP_DOC A WHERE A.REQ_ID = @p_REQ_ID) WHERE ASSET_ID IN (SELECT B.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT B WHERE B.REQ_DOC_ID = @p_REQ_ID) END INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,DEP_ID,PARENT_PROCESS_ID,IS_LEAF,NOTES,PROCESS_TYPE) VALUES(@p_REQ_ID,'APPNEW','C','GDDV',@BRANCHID,@DEP_ID,'','N',NULL,'Approve') INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'S_DVKD', @p_CHECKER_ID, GETDATE(), N'Gửi trưởng đơn vị', N'Gửi trưởng đơn vị thành công'); UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='APPNEW', AUTH_STATUS = 'U' WHERE REQ_ID=@p_REQ_ID UPDATE TR_REQUEST_SHOP_DOC_DT SET AUTH_STATUS = 'U' WHERE REQ_DOC_ID = @p_REQ_ID SET @NOTIFATION= N'Gửi trưởng đơn vị thành công' --UPDATE NGÀY GỬI DUYỆT UPDATE TR_REQUEST_SHOP_DOC SET SEND_APP_DT = GETDATE() WHERE REQ_ID = @p_REQ_ID IF @@Error <> 0 GOTO ABORT END -- --QLTS gửi DVCM ELSE IF(@p_TYPE_PROCESS='S_DVCM') BEGIN IF(EXISTS(SELECT REQ_ID FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND STATUS = 'REJECT')) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Phiếu yêu cầu đã bị trả về. Gửi duyệt thất bại.' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT REQ_ID FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND (HO_NOTES IS NULL OR HO_NOTES = ''))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Vui lòng nhập ý kiến của bộ phận QLTS trước khi gửi duyệt.' ErrorDesc RETURN '-1' END INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'S_DVCM', @p_CHECKER_ID, GETDATE(), N'Gửi DVCM', N'QLTS gửi DVCM thành công'); UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'DVCM' WHERE REQ_ID = @p_REQ_ID UPDATE PL_REQUEST_PROCESS SET STATUS = 'P', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'QLTS_N' UPDATE PL_REQUEST_PROCESS SET STATUS = 'C' WHERE PROCESS_ID = 'DVCM' AND REQ_ID = @p_REQ_ID SET @p_NOTES = N'Gửi DVCM thành công' IF @@Error <> 0 GOTO ABORT --END SET @NOTIFATION= N'Gửi DVCM thành công' --UPDATE NGÀY GỬI DUYỆT UPDATE TR_REQUEST_SHOP_DOC SET SEND_APP_DT = GETDATE() WHERE REQ_ID = @p_REQ_ID END -- QLTS DUYỆT ELSE IF(@p_TYPE_PROCESS='QLTS_D') BEGIN IF(EXISTS(SELECT TOP 1 A.HO_NOTES FROM TR_REQUEST_SHOP_DOC A WHERE A.REQ_ID = @p_REQ_ID AND (A.HO_NOTES IS NULL OR A.HO_NOTES = ''))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'QLTS chưa nhập ý kiến. Duyệt thất bại' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT trsd.REQ_ID FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @p_REQ_ID AND trsd.MAKER_ID = @p_CHECKER_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_ID AS ID, N'Bạn không có quyền duyệt phiếu này' ErrorDesc RETURN '-1' END IF((SELECT TOP 1 STATUS FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID) = 'TBP_D') BEGIN --TRƯỞNG BỘ PHẬN QLTS DUYỆT UPDATE PL_REQUEST_PROCESS SET [STATUS]='P',NOTES = @p_NOTES ,CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103) WHERE REQ_ID=@p_REQ_ID AND [STATUS]='C' AND PROCESS_ID = 'TBP_D' INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'TBP_D', @p_CHECKER_ID, GETDATE(), N'Trưởng bộ phận QLTS duyệt', @p_NOTES); UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='QLTS_D' WHERE REQ_ID=@p_REQ_ID INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,IS_LEAF,NOTES,PROCESS_TYPE) VALUES(@p_REQ_ID,'QLTS_D','C','GDDV_QLTS','DV0001','TBP_D','DEP000000000048','N',NULL,'Approve') SET @NOTIFATION= N'Trưởng bộ phận QLTS duyệt thành công' END ELSE IF((SELECT TOP 1 STATUS FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID) = 'QLTS_D') BEGIN --TRƯỞNG ĐƠN VỊ QLTS DUYỆT UPDATE PL_REQUEST_PROCESS SET [STATUS]='P', NOTES = @p_NOTES , CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103) WHERE REQ_ID=@p_REQ_ID AND [STATUS]='C' IF @@Error <> 0 GOTO ABORT INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'QLTS_D', @p_CHECKER_ID, GETDATE(), N'Trưởng đơn vị QLTS duyệt', @p_NOTES); IF(@REQ_TYPE <> 'DCTS') BEGIN --PHIẾU SỬA CHỮA TÀI SẢN, THU HỒI TÀI SẢN INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],IS_LEAF) VALUES(@p_REQ_ID,'APPROVE','C','N') UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='APPROVE',AUTH_STATUS = 'A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,GETDATE(),103) WHERE REQ_ID=@p_REQ_ID IF(@REQ_TYPE = 'TH') BEGIN --UPDATE ASS_MASTER XONG GIAO DỊCH UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL WHERE ASSET_ID IN (SELECT A.ASSET_ID FROM TR_REQUEST_DOC_ASSET_DT A WHERE A.REQ_DOC_ID = @p_REQ_ID) END ELSE BEGIN --UPDATE ASS_MASTER XONG GIAO DỊCH UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL WHERE ASSET_ID IN (SELECT A.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT A WHERE A.REQ_DOC_ID = @p_REQ_ID) END SET @NOTIFATION= N'Phê duyệt thành công' END ELSE BEGIN DECLARE @PRICE_OF_ASSET DECIMAL = (SELECT MAX(b.BUY_PRICE) FROM TR_REQUEST_SHOP_DOC_DT A LEFT JOIN ASS_MASTER B ON A.ASS_ID = B.ASSET_ID WHERE A.REQ_DOC_ID = @p_REQ_ID) IF(@PRICE_OF_ASSET > 30000000) BEGIN --LÊN TTDVNB INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,IS_LEAF,NOTES,PROCESS_TYPE) VALUES(@p_REQ_ID,'TTQLTS_D','C','GDDV','DV0001','QLTS_D',(SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE DEP_CODE = '05N20'),'N',NULL,'Approve') UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'TTQLTS_D' WHERE REQ_ID = @p_REQ_ID SET @NOTIFATION= N'Trưởng đơn vị phê duyệt thành công. Phiếu đã gừi lên Trung tâm dịch vụ nội bộ.' END ELSE BEGIN INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],IS_LEAF) VALUES(@p_REQ_ID,'APPROVE','C','N') UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='APPROVE',AUTH_STATUS = 'A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,GETDATE(),103) WHERE REQ_ID=@p_REQ_ID --UPDATE ASS_MASTER XONG GIAO DỊCH UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL WHERE ASSET_ID IN (SELECT A.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT A WHERE A.REQ_DOC_ID = @p_REQ_ID) SET @NOTIFATION= N'Phê duyệt thành công.' END END END ELSE IF((SELECT TOP 1 STATUS FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID) = 'TTQLTS_D') BEGIN --TRUNG TÂM DỊCH VỤ NỘI BỘ DUYỆT UPDATE PL_REQUEST_PROCESS SET [STATUS]='P', NOTES = @p_NOTES , CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CONVERT(DATETIME, @p_APPROVE_DT, 103) WHERE REQ_ID=@p_REQ_ID AND [STATUS]='C' IF @@Error <> 0 GOTO ABORT INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],IS_LEAF) VALUES(@p_REQ_ID,'APPROVE','C','N') INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'TTQLTS_D', @p_CHECKER_ID, GETDATE(), N'Trung tâm dịch vụ nội bộ phê duyệt', @p_NOTES); UPDATE TR_REQUEST_SHOP_DOC SET [STATUS]='APPROVE',AUTH_STATUS = 'A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,GETDATE(),103) WHERE REQ_ID=@p_REQ_ID --UPDATE ASS_MASTER XONG GIAO DỊCH UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL WHERE ASSET_ID IN (SELECT A.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT A WHERE A.REQ_DOC_ID = @p_REQ_ID) SET @NOTIFATION= N'Phê duyệt thành công' END --UPDATE NGÀY GỬI DUYỆT UPDATE TR_REQUEST_SHOP_DOC SET SEND_APP_DT = GETDATE() WHERE REQ_ID = @p_REQ_ID IF @@Error <> 0 GOTO ABORT END ELSE IF(@p_TYPE_PROCESS = 'DVCM_D') BEGIN UPDATE TR_REQUEST_SHOP_COSTCENTER SET AUTH_STATUS = 'A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_ID = @p_REQ_ID AND COST_ID = @DEP_ID UPDATE PL_REQUEST_PROCESS SET STATUS = 'P', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103), NOTES = @p_NOTES WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'DVCM_D' AND PARENT_PROCESS_ID = 'DVCM' AND DEP_ID = @DEP_ID INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'DVCM_D', @p_CHECKER_ID, GETDATE(), N'Trưởng bộ phận DVCM duyệt', @p_NOTES); -- CHECK DVCM ĐÃ NHẬP ĐỦ HẾT CHƯA IF(NOT EXISTS(SELECT TOP 1 trsc.REQ_COST_ID FROM TR_REQUEST_SHOP_COSTCENTER trsc WHERE trsc.REQ_ID = @p_REQ_ID AND (trsc.AUTH_STATUS = 'U' OR trsc.AUTH_STATUS = 'E'))) BEGIN IF(@REQ_TYPE = 'SC') BEGIN UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'QLTS_NL',HO_NOTES = N'- Kế hoạch (Trong/Ngoài NS):' + CHAR(10) + N'- Nội dung đề xuất:' + CHAR(10) + N'- Ghi chú khác:' WHERE REQ_ID = @p_REQ_ID INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,IS_LEAF,NOTES,PROCESS_TYPE) VALUES(@p_REQ_ID,'QLTS_NL','C','QLTS','DV0001','DVCM_D','DEP000000000048','N',NULL,'Update') END ELSE BEGIN UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'TBP_D' WHERE REQ_ID = @p_REQ_ID INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,IS_LEAF,NOTES,PROCESS_TYPE) VALUES(@p_REQ_ID,'TBP_D','C','TBP_QLTS','DV0001','DVCM_D','DEP000000000048','N',NULL,'Approve') END --UPDATE NGÀY GỬI DUYỆT UPDATE TR_REQUEST_SHOP_DOC SET SEND_APP_DT = GETDATE() WHERE REQ_ID = @p_REQ_ID END SET @NOTIFATION= N'Trưởng phòng DVCM duyệt thành công' IF @@Error <> 0 GOTO ABORT END ELSE IF(@p_TYPE_PROCESS = 'S_GDDV_QLTS') BEGIN -- GỬI TRƯỞNG BỘ PHẬN QLTS DUYỆT IF(EXISTS(SELECT TOP 1 A.HO_NOTES FROM TR_REQUEST_SHOP_DOC A WHERE A.REQ_ID = @p_REQ_ID AND (A.HO_NOTES IS NULL OR A.HO_NOTES = ''))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Vui lòng nhập ý kiến của bộ phận QLTS trước khi gửi duyệt.' ErrorDesc RETURN '-1' END INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'S_TBP_QLTS', @p_CHECKER_ID, CONVERT(DATETIME,@p_APPROVE_DT,103), N'QLTS gửi phê duyệt', N'QLTS gửi trưởng bộ phận QLTS phê duyệt thành công'); UPDATE PL_REQUEST_PROCESS SET STATUS = 'P', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_ID = @p_REQ_ID AND (PROCESS_ID = 'QLTS_N' OR PROCESS_ID = 'QLTS_NL')--PYC_SC INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,IS_LEAF,NOTES,PROCESS_TYPE) VALUES(@p_REQ_ID,'TBP_D','C','TBP_QLTS','DV0001','QLTS_N','DEP000000000048','N',NULL,'Approve') UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'TBP_D' WHERE REQ_ID = @p_REQ_ID IF @@Error <> 0 GOTO ABORT SET @NOTIFATION= N'Gửi trưởng bộ phận QLTS phê duyệt thành công' --UPDATE NGÀY GỬI DUYỆT UPDATE TR_REQUEST_SHOP_DOC SET SEND_APP_DT = GETDATE() WHERE REQ_ID = @p_REQ_ID END ELSE IF(@p_TYPE_PROCESS = 'S_GDDV_DVCM') BEGIN IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER trsc WHERE trsc.REQ_ID = @p_REQ_ID AND trsc.COST_ID = @DEP_ID AND trsc.AUTH_STATUS = 'E' AND (trsc.RE_CONTENT IS NULL OR trsc.RE_CONTENT = ''))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Vui lòng nhập Nội Dung Đề Xuất của bạn trước khi gửi Trưởng bộ phận DVCM phê duyệt' ErrorDesc RETURN '-1' END INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'S_GDDVDVCM', @p_CHECKER_ID, CONVERT(DATETIME,@p_APPROVE_DT,103), N'DVCM gửi Trưởng bộ phận DVCM', N'DVCM gửi Trưởng bộ phận DVCM phê duyệt thành công'); UPDATE TR_REQUEST_SHOP_COSTCENTER SET AUTH_STATUS = 'U' WHERE REQ_ID = @p_REQ_ID AND COST_ID = @DEP_ID UPDATE PL_REQUEST_PROCESS SET STATUS = 'P', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_ID = @p_REQ_ID AND DEP_ID = @DEP_ID AND PROCESS_ID = 'DVCM' AND PARENT_PROCESS_ID = 'QLTS_N' UPDATE PL_REQUEST_PROCESS SET STATUS = 'C' WHERE REQ_ID = @p_REQ_ID AND DEP_ID = @DEP_ID AND PROCESS_ID = 'DVCM_D' AND PARENT_PROCESS_ID = 'DVCM' IF @@Error <> 0 GOTO ABORT SET @NOTIFATION= N'Gửi trưởng bộ phận DVCM phê duyệt thành công' END ELSE IF(@p_TYPE_PROCESS = 'S_DVCM_OR_QLTSNL') BEGIN --PYC SỬA CHỮA: NẾU CÓ DVCM THÌ GỬI DVCM. NẾU KO CÓ THÌ QUAY LẠI QLTS_NL IF(EXISTS(SELECT REQ_ID FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND STATUS = 'REJECT')) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Phiếu yêu cầu đã bị trả về. Gửi duyệt thất bại.' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID = @p_REQ_ID)) BEGIN --GỬI DVCM INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'S_DVCM', @p_CHECKER_ID, GETDATE(), N'Gửi DVCM', N'QLTS gửi DVCM thành công'); UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'DVCM' WHERE REQ_ID = @p_REQ_ID UPDATE PL_REQUEST_PROCESS SET STATUS = 'P', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'QLTS_N' UPDATE PL_REQUEST_PROCESS SET STATUS = 'C' WHERE PROCESS_ID = 'DVCM' AND REQ_ID = @p_REQ_ID SET @p_NOTES = N'Gửi DVCM thành công' SET @NOTIFATION= N'Gửi DVCM thành công' END ELSE --QUAY LẠI QLTS BEGIN INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'S_QLTS_NL', @p_CHECKER_ID, GETDATE(), N'Gửi bộ phận QLTS xử lý', N'Gửi bộ phận QLTS xử lý thành công'); UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'QLTS_NL', HO_NOTES = N'- Kế hoạch (Trong/Ngoài NS):' + CHAR(10) + N'- Nội dung đề xuất: ' + CHAR(10) + N'- Ghi chú khác:' WHERE REQ_ID = @p_REQ_ID UPDATE PL_REQUEST_PROCESS SET STATUS = 'P', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@p_APPROVE_DT,103) WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'QLTS_N' INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID,DEP_ID,IS_LEAF,NOTES,PROCESS_TYPE) VALUES(@p_REQ_ID,'QLTS_NL','C','QLTS','DV0001','QLTS_N','DEP000000000048','N',NULL,'Update') SET @p_NOTES = N'Gửi bộ phận QLTS xử lý thành công' SET @NOTIFATION= N'Gửi bộ phận QLTS xử lý thành công' END --UPDATE NGÀY GỬI DUYỆT UPDATE TR_REQUEST_SHOP_DOC SET SEND_APP_DT = GETDATE() WHERE REQ_ID = @p_REQ_ID IF @@Error <> 0 GOTO ABORT END ELSE IF(@p_TYPE_PROCESS = 'CANCEL') BEGIN --QLTS HUỶ PHIẾU IF(NOT EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_DOC A WHERE A.REQ_ID = @p_REQ_ID AND ((A.REQ_TYPE IN ('DCTS','TH') AND A.STATUS = 'QLTS_N') OR(A.REQ_TYPE = 'SC' AND A.STATUS IN ('QLTS_N','QLTS_NL'))))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' WH_O_HCQT_MUL_ID, N'Huỷ thất phải. Vui lòng kiểm tra lại trạng thái phiếu' ErrorDesc RETURN '-1' END IF(@REQ_TYPE = 'TH') BEGIN UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL WHERE ASSET_ID IN (SELECT A.ASSET_ID FROM TR_REQUEST_DOC_ASSET_DT A WHERE A.REQ_DOC_ID = @p_REQ_ID) END ELSE IF(@REQ_TYPE IN ('DCTS','SC')) BEGIN UPDATE ASS_MASTER SET CURRENT_TRANS = NULL, CURRENT_TRANS_TYPE = NULL WHERE ASSET_ID IN (SELECT A.ASS_ID FROM TR_REQUEST_SHOP_DOC_DT A WHERE A.REQ_DOC_ID = @p_REQ_ID) END UPDATE TR_REQUEST_SHOP_DOC SET STATUS = 'CANCEL', AUTH_STATUS = 'D' WHERE REQ_ID = @p_REQ_ID INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'CANCEL', @p_CHECKER_ID, GETDATE(), N'Chuyên viên QLTS huỷ phiếu.', @p_NOTES); -- DELETE PL_REQUEST_PROCESS -- WHERE REQ_ID = @p_REQ_ID AND STATUS = 'C' AND PROCESS_ID IN ('QLTS_N','QLTS_NL') DELETE PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND ((STATUS = 'C' AND PROCESS_ID IN ('QLTS_N','QLTS_NL')) OR STATUS = 'U') INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,CHECKER_ID,APPROVE_DT,PARENT_PROCESS_ID,IS_LEAF,NOTES,BRANCH_ID,DEP_ID) VALUES(@p_REQ_ID,'CANCEL','C','QLTS',@p_CHECKER_ID,GETDATE(),@CURR_PROCESS,'Y',N'Chuyên viên QLTS huỷ phiếu',@BRANCHID,@DEP_ID) SET @p_NOTES = N'Huỷ phiếu thành công' SET @NOTIFATION= N'Huỷ phiếu thành công' END COMMIT TRANSACTION SELECT '0' as Result, @NOTIFATION AS NOTIFATION, '' ErrorDesc, @NOTIFY_TO_USER AS NEXT_USER_NOTIFI, @COMPLETE AS COMPLETE RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' AS RESULT, '' ErrorDesc RETURN '-1' End END GO IF @@ERROR <> 0 SET NOEXEC ON GO PRINT N'Altering [dbo].[TR_REQUEST_REPAIR_ASS_Upd_QLTS]' GO ALTER PROCEDURE [dbo].[TR_REQUEST_REPAIR_ASS_Upd_QLTS] @p_REQ_ID varchar(15) = null , @p_REQ_TYPE VARCHAR(10) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL, @p_APPROVE_DT VARCHAR(100) = NULL, @p_HO_NOTES NVARCHAR(1000) = NULL, @p_NOTES NVARCHAR(MAX) = NULL, @p_ListCostCenter XML AS BEGIN TRANSACTION DECLARE @TYPE_PYC VARCHAR(10) = (SELECT TOP 1 trsd.REQ_TYPE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @p_REQ_ID) IF(@TYPE_PYC <> 'SC') BEGIN --PYC ĐIỀU CHUYỂN && THU HỒI IF(@p_HO_NOTES IS NULL OR @p_HO_NOTES = '') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_ID REQ_ID, N'Vui lòng nhập ý kiến của bộ phận QLTS' ErrorDesc RETURN '-1' END IF(ISNULL(@p_NOTES,'') <> 'SKIP_PL_PROCESS') BEGIN INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'QLTS_N', @p_CHECKER_ID, CONVERT(DATETIME,@p_APPROVE_DT,103), N'QLTS nhập ý kiến', N'QLTS nhập ý kiến thành công'); END UPDATE TR_REQUEST_SHOP_DOC SET HO_NOTES = @p_HO_NOTES WHERE REQ_ID = @p_REQ_ID DELETE TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID = @p_REQ_ID DELETE PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND (PROCESS_ID = 'DVCM' OR PROCESS_ID = 'DVCM_D') --START THÊM DVCM DECLARE @hdoc2 INT Declare @l_NOTES NVARCHAR(MAX), @l_AUTH_STATUS VARCHAR(5), @COST_ID varchar(15), @REQ_COST_ID varchar(15), @MAKER_ID VARCHAR(100), @CREATE_DT VARCHAR(50), @CHECKER_ID VARCHAR(100), @APPROVE_DT VARCHAR(50), @l_ASS_STATUS NVARCHAR(MAX), @l_RE_CONTENT NVARCHAR(MAX), @l_QUANTITY INT Exec sp_xml_preparedocument @hdoc2 Output,@p_ListCostCenter DECLARE ListCostCenters CURSOR FOR SELECT * FROM OPENXML(@hdoc2,'/Root/ListCostCenter',2) WITH ( REQ_COST_ID VARCHAR(15), COST_ID varchar(15), AUTH_STATUS VARCHAR(5), NOTES nvarchar(MAX), MAKER_ID VARCHAR(100), CREATE_DT VARCHAR(50), CHECKER_ID VARCHAR(100), APPROVE_DT VARCHAR(50), ASS_STATUS NVARCHAR(MAX), RE_CONTENT NVARCHAR(MAX), QUANTITY INT ) OPEN ListCostCenters FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@l_AUTH_STATUS, @l_NOTES, @MAKER_ID, @CREATE_DT, @CHECKER_ID, @APPROVE_DT, @l_ASS_STATUS, @l_RE_CONTENT, @l_QUANTITY WHILE @@FETCH_STATUS = 0 BEGIN IF( NOT EXISTS(SELECT 1 FROM dbo.TR_REQUEST_SHOP_COSTCENTER WHERE COST_ID=@COST_ID AND REQ_ID=@p_REQ_ID)) BEGIN EXEC SYS_CodeMasters_Gen 'TR_REQUEST_SHOP_COSTCENTER', @REQ_COST_ID out IF @REQ_COST_ID='' OR @REQ_COST_ID IS NULL GOTO ABORT INSERT INTO dbo.TR_REQUEST_SHOP_COSTCENTER ( REQ_COST_ID, COST_ID, REQ_ID, NOTES, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, ASS_STATUS, RE_CONTENT, QUANTITY ) VALUES ( @REQ_COST_ID, -- REQ_COST_ID - varchar(15) @COST_ID, -- COST_ID - varchar(15) @p_REQ_ID, -- REQ_ID - varchar(15) @l_NOTES, -- NOTES - nvarchar(500) @l_AUTH_STATUS, -- AUTH_STATUS - varchar(1) @MAKER_ID, -- MAKER_ID - varchar(15) CONVERT(DATETIME,@CREATE_DT,103), -- CREATE_DT - datetime @CHECKER_ID, -- CHECKER_ID - varchar(15) CONVERT(DATETIME,@APPROVE_DT,103) -- APPROVE_DT - datetime , @l_ASS_STATUS , @l_RE_CONTENT , @l_QUANTITY ) END IF @@ERROR <> 0 GOTO ABORT2 FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID,@COST_ID,@l_AUTH_STATUS, @l_NOTES, @MAKER_ID, @CREATE_DT, @CHECKER_ID, @APPROVE_DT, @l_ASS_STATUS, @l_RE_CONTENT, @l_QUANTITY END CLOSE ListCostCenters DEALLOCATE ListCostCenters --END DVCM INSERT INTO PL_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID,DEP_ID, PARENT_PROCESS_ID, IS_LEAF, NOTES, PROCESS_TYPE) SELECT @p_REQ_ID, 'DVCM', 'U', 'DVCM', (CASE WHEN 1=1 THEN (SELECT B.BRANCH_ID FROM CM_DEPARTMENT B WHERE B.DEP_ID = A.COST_ID)END) AS BRANCH_ID, A.COST_ID, --DEP_ID 'QLTS_N', 'N', NULL, 'Update' FROM TR_REQUEST_SHOP_COSTCENTER A WHERE REQ_ID = @p_REQ_ID --TRƯỞNG DVCM CẬP DUYỆT INSERT INTO PL_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID,DEP_ID, PARENT_PROCESS_ID, IS_LEAF, NOTES, PROCESS_TYPE) SELECT @p_REQ_ID, 'DVCM_D', 'U', 'GDDV', (CASE WHEN 1=1 THEN (SELECT B.BRANCH_ID FROM CM_DEPARTMENT B WHERE B.DEP_ID = A.COST_ID)END) AS BRANCH_ID, A.COST_ID, --DEP_ID 'DVCM', 'N', NULL, 'Approve' FROM TR_REQUEST_SHOP_COSTCENTER A WHERE REQ_ID = @p_REQ_ID END ELSE BEGIN --PYC SỬA CHỮA IF(ISNULL(@p_NOTES,'') <> 'SKIP_PL_PROCESS') BEGIN INSERT INTO PL_PROCESS (REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES) VALUES (@p_REQ_ID, 'QLTS_N', @p_CHECKER_ID, CONVERT(DATETIME,@p_APPROVE_DT,103), N'QLTS xử lý', N'QLTS xử lý thành công'); END IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_DOC WHERE REQ_ID = @p_REQ_ID AND STATUS = 'QLTS_N')) BEGIN --ĐANG Ở BƯỚC QLTS CHỌN DVCM DELETE TR_REQUEST_SHOP_COSTCENTER WHERE REQ_ID = @p_REQ_ID DELETE PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND (PROCESS_ID = 'DVCM' OR PROCESS_ID = 'DVCM_D') --START THÊM DVCM DECLARE @hdoc3 INT Declare @l_NOTES_1 NVARCHAR(MAX), @l_AUTH_STATUS_1 VARCHAR(5), @COST_ID_1 varchar(15), @REQ_COST_ID_1 varchar(15), @MAKER_ID_1 VARCHAR(100), @CREATE_DT_1 VARCHAR(50), @CHECKER_ID_1 VARCHAR(100), @APPROVE_DT_1 VARCHAR(50), @l_ASS_STATUS_1 NVARCHAR(MAX), @l_RE_CONTENT_1 NVARCHAR(MAX), @l_QUANTITY_1 INT Exec sp_xml_preparedocument @hdoc3 Output,@p_ListCostCenter DECLARE ListCostCenters CURSOR FOR SELECT * FROM OPENXML(@hdoc3,'/Root/ListCostCenter',2) WITH ( REQ_COST_ID VARCHAR(15), COST_ID varchar(15), AUTH_STATUS VARCHAR(5), NOTES nvarchar(MAX), MAKER_ID VARCHAR(100), CREATE_DT VARCHAR(50), CHECKER_ID VARCHAR(100), APPROVE_DT VARCHAR(50), ASS_STATUS NVARCHAR(MAX), RE_CONTENT NVARCHAR(MAX), QUANTITY INT ) OPEN ListCostCenters FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID_1,@COST_ID_1,@l_AUTH_STATUS_1, @l_NOTES_1, @MAKER_ID_1, @CREATE_DT_1, @CHECKER_ID_1, @APPROVE_DT_1, @l_ASS_STATUS_1, @l_RE_CONTENT_1, @l_QUANTITY_1 WHILE @@FETCH_STATUS = 0 BEGIN IF( NOT EXISTS(SELECT 1 FROM dbo.TR_REQUEST_SHOP_COSTCENTER WHERE COST_ID=@COST_ID_1 AND REQ_ID=@p_REQ_ID)) BEGIN EXEC SYS_CodeMasters_Gen 'TR_REQUEST_SHOP_COSTCENTER', @REQ_COST_ID_1 out IF @REQ_COST_ID_1='' OR @REQ_COST_ID_1 IS NULL GOTO ABORT INSERT INTO dbo.TR_REQUEST_SHOP_COSTCENTER ( REQ_COST_ID, COST_ID, REQ_ID, NOTES, AUTH_STATUS, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, ASS_STATUS, RE_CONTENT, QUANTITY ) VALUES ( @REQ_COST_ID_1, -- REQ_COST_ID - varchar(15) @COST_ID_1, -- COST_ID - varchar(15) @p_REQ_ID, -- REQ_ID - varchar(15) @l_NOTES_1, -- NOTES - nvarchar(500) @l_AUTH_STATUS_1, -- AUTH_STATUS - varchar(1) @MAKER_ID_1, -- MAKER_ID - varchar(15) CONVERT(DATETIME,@CREATE_DT_1,103), -- CREATE_DT - datetime @CHECKER_ID_1, -- CHECKER_ID - varchar(15) CONVERT(DATETIME,@APPROVE_DT_1,103) -- APPROVE_DT - datetime , @l_ASS_STATUS_1 , @l_RE_CONTENT_1 , @l_QUANTITY_1 ) END IF @@ERROR <> 0 GOTO ABORT2 FETCH NEXT FROM ListCostCenters INTO @REQ_COST_ID_1,@COST_ID_1,@l_AUTH_STATUS_1, @l_NOTES_1, @MAKER_ID_1, @CREATE_DT_1, @CHECKER_ID_1, @APPROVE_DT_1, @l_ASS_STATUS_1, @l_RE_CONTENT_1, @l_QUANTITY_1 END CLOSE ListCostCenters DEALLOCATE ListCostCenters --END DVCM INSERT INTO PL_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID,DEP_ID, PARENT_PROCESS_ID, IS_LEAF, NOTES, PROCESS_TYPE) SELECT @p_REQ_ID, 'DVCM', 'U', 'DVCM', (CASE WHEN 1=1 THEN (SELECT B.BRANCH_ID FROM CM_DEPARTMENT B WHERE B.DEP_ID = A.COST_ID)END) AS BRANCH_ID, A.COST_ID, --DEP_ID 'QLTS_N', 'N', NULL, 'Update' FROM TR_REQUEST_SHOP_COSTCENTER A WHERE REQ_ID = @p_REQ_ID --TRƯỞNG DVCM CẬP DUYỆT INSERT INTO PL_REQUEST_PROCESS (REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID,DEP_ID, PARENT_PROCESS_ID, IS_LEAF, NOTES, PROCESS_TYPE) SELECT @p_REQ_ID, 'DVCM_D', 'U', 'GDDV', (CASE WHEN 1=1 THEN (SELECT B.BRANCH_ID FROM CM_DEPARTMENT B WHERE B.DEP_ID = A.COST_ID)END) AS BRANCH_ID, A.COST_ID, --DEP_ID 'DVCM', 'N', NULL, 'Approve' FROM TR_REQUEST_SHOP_COSTCENTER A WHERE REQ_ID = @p_REQ_ID END ELSE BEGIN IF(@p_HO_NOTES IS NULL OR @p_HO_NOTES = '') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_ID REQ_ID, N'Vui lòng nhập ý kiến của bộ phận QLTS' ErrorDesc RETURN '-1' END UPDATE TR_REQUEST_SHOP_DOC SET HO_NOTES = @p_HO_NOTES WHERE REQ_ID = @p_REQ_ID END END COMMIT TRANSACTION SELECT '0' as Result, @p_REQ_ID REQ_ID, '' ErrorDesc, N'QLTS xử lý thành công' Message RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' AS RESULT RETURN '-1' END ABORT2: BEGIN ROLLBACK TRANSACTION CLOSE ListCostCenters DEALLOCATE ListCostCenters SELECT '-1' AS RESULT RETURN '-1' END GO IF @@ERROR <> 0 SET NOEXEC ON GO PRINT N'Altering [dbo].[rpt_PYC_CAPPHAT]' GO ALTER PROC [dbo].[rpt_PYC_CAPPHAT] @REQ_ID VARCHAR(15) = NULL AS BEGIN DECLARE @SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL --TABLE 0 SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, AG.GROUP_NAME AS GR_NAME, AG.GROUP_CODE AS GR_CODE, cd.DEP_NAME, ce.EMP_NAME, trsdd.QTY_ETM, trsdd.REASON, TU.TLFullName AS MAKER, TU2.TLFullName AS TDV FROM TR_REQUEST_SHOP_DOC_DT trsdd LEFT JOIN TR_REQUEST_SHOP_DOC trsd ON trsdd.REQ_DOC_ID = trsd.REQ_ID LEFT JOIN CM_DEPARTMENT cd ON trsd.DEP_ID = cd.DEP_ID LEFT JOIN CM_EMPLOYEE ce ON trsdd.EMP_ID = ce.EMP_ID LEFT JOIN ASS_GROUP AG ON trsdd.ASS_GROUP_ID = AG.GROUP_ID LEFT JOIN TL_USER TU ON TU.TLNANME = trsd.MAKER_ID LEFT JOIN TL_USER TU2 ON TU2.TLNANME = trsd.CHECKER_ID WHERE trsdd.REQ_DOC_ID = @REQ_ID AND REQ_DT_TYPE = 'ORGINAL' --GROUP BY AG.GROUP_NAME, AG.GROUP_CODE, --cd.DEP_NAME, ce.EMP_NAME, trsdd.QTY_ETM, trsdd.REASON, TU.TLFullName, TU2.TLNANME ORDER BY STT --TABLE 1 DVCM KHOI CNTT IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00')) BEGIN SELECT TOP 1 A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(PP.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID LEFT JOIN PL_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND A.MAKER_ID = PP.CHECKER_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00' ORDER BY PP.APPROVE_DT DESC SET @SHOW_TABLE_DVCM_IT = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 2 DVCM KHAC IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00')) BEGIN SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT( (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.CHECKER_ID = A.MAKER_ID ORDER BY PP.APPROVE_DT DESC) , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00' ORDER BY A.COST_ID SET @SHOW_TABLE_DVCM_KHAC = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_DOC trsd LEFT JOIN CM_DEPARTMENT cd ON trsd.DEP_ID = cd.DEP_ID LEFT JOIN PL_REQUEST_PROCESS PRP ON trsd.REQ_ID = PRP.REQ_ID AND PRP.PROCESS_ID = 'QLTS_NL' LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID WHERE trsd.REQ_ID = @REQ_ID)) BEGIN SELECT DISTINCT trsd.HO_NOTES, tu.TLFullName, ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT( (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = trsd.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC) , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_DOC trsd LEFT JOIN CM_DEPARTMENT cd ON trsd.DEP_ID = cd.DEP_ID LEFT JOIN PL_REQUEST_PROCESS PRP ON trsd.REQ_ID = PRP.REQ_ID AND PRP.PROCESS_ID = 'QLTS_NL' LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID WHERE trsd.REQ_ID = @REQ_ID END ELSE BEGIN SELECT N'' NOTES, N'' TLFullName, N'' POS_NAME END --TABLE 4 PHE DUYET DVCM KHOI CNTT IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(PP.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME LEFT JOIN PL_PROCESS PP ON A.REQ_ID = PP.REQ_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00' ORDER BY PP.APPROVE_DT DESC END ELSE BEGIN SELECT '' AS NOTES END END ELSE BEGIN SELECT N'' NOTES END --QTLS_D 5 IF(EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS PRP LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'QLTS_D')) BEGIN SELECT DISTINCT PRP.NOTES AS HO_NOTES, tu.TLFullName, ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT( (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = PRP.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC) , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS PRP LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID LEFT JOIN PL_PROCESS PP ON PRP.REQ_ID = PP.REQ_ID AND PRP.CHECKER_ID = PP.CHECKER_ID WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'QLTS_D' END ELSE BEGIN SELECT N'' HO_NOTES, N'' TLFullName, N'' POS_NAME END --TTQTLS_D 6 SELECT DISTINCT ISNULL(PRP.NOTES, '') AS HO_NOTES, ISNULL(tu.TLFullName, '') AS TLFullName , ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT( (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = PRP.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC) , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS PRP LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID --LEFT JOIN PL_PROCESS PP ON PRP.REQ_ID = PP.REQ_ID AND PRP.CHECKER_ID = PP.CHECKER_ID WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'TTQLTS_D' --TABLE 7 SELECT TU.TLFullName AS MAKER, TU2.TLFullName AS TDV, ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT( A.CREATE_DT , 'dd/MM/yyyy HH:mm:ss') AS POS_MAKER, ISNULL(CE2.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT( A.APPROVE_DT , 'dd/MM/yyyy HH:mm:ss') AS POS_TDV FROM TR_REQUEST_SHOP_DOC A LEFT JOIN TL_USER TU ON TU.TLNANME = A.MAKER_ID LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = A.MAKER_ID LEFT JOIN TL_USER TU2 ON TU2.TLNANME = A.CHECKER_ID LEFT JOIN CM_EMPLOYEE_LOG CE2 ON CE2.USER_DOMAIN = A.CHECKER_ID WHERE A.REQ_ID = @REQ_ID --TABLE 8 SỐ PYC SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID --PHUCVH TABLE 9 NGÀY TDV DUYỆT SELECT TOP 1 N'Ngày ' + CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT FROM PL_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW' ORDER BY pp.ID DESC --TABLE 10 PHÊ DUYỆT DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00')) BEGIN SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(A.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00' ORDER BY C.DEP_ID END ELSE BEGIN SELECT '' AS NOTES FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00' END END ELSE BEGIN SELECT N'' NOTES END --TABLE 11 PHÊ DUYỆT TBP_QLTS SELECT DISTINCT ISNULL(PRP.NOTES, ISNULL((SELECT TOP 1 PP.NOTES FROM PL_PROCESS PP WHERE PP.REQ_ID = PRP.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID AND PRP.STATUS = 'P' ORDER BY PP.APPROVE_DT DESC),'')) AS HO_NOTES, ISNULL(tu.TLFullName, '') AS TLFullName , ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT( (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = PRP.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC) , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS PRP LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID --LEFT JOIN PL_PROCESS PP ON PRP.REQ_ID = PP.REQ_ID AND PRP.CHECKER_ID = PP.CHECKER_ID WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'TBP_D' --SHOW TABLE DVCM IT IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN SELECT N'DVCMIT' AS MERGE_REGION END --SHOW TABLE DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'DVCMKHAC' AS MERGE_REGION END --SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'TITLEDVCM' AS MERGE_REGION END END GO IF @@ERROR <> 0 SET NOEXEC ON GO PRINT N'Altering [dbo].[rpt_PYC_DIEUCHUYEN]' GO ALTER PROC [dbo].[rpt_PYC_DIEUCHUYEN] @REQ_ID VARCHAR(15) = NULL AS BEGIN DECLARE @SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL --TABLE 0 SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, am.ASSET_NAME AS ASS_NAME, ISNULL(am.ASSET_CODE,am.ASS_CODE_TMP) AS ASSET_CODE, ISNULL(PB.DEP_NAME,ISNULL(TT.DEP_NAME,K.DEP_NAME)) AS DEP_NAME, ce.EMP_NAME, '1' QTY_ETM, B.REASON AS REQ_CONTENT FROM TR_REQUEST_SHOP_DOC A LEFT JOIN TR_REQUEST_SHOP_DOC_DT B ON A.REQ_ID = B.REQ_DOC_ID LEFT JOIN CM_DEPARTMENT K ON B.SUBBRANCH_OLD = K.DEP_ID LEFT JOIN CM_DEPARTMENT TT ON B.FATHER_OLD = TT.DEP_ID LEFT JOIN CM_DEPARTMENT PB ON B.DEP_OLD = PB.DEP_ID LEFT JOIN ASS_MASTER am ON am.ASSET_ID = B.ASS_ID LEFT JOIN CM_EMPLOYEE ce ON B.EMP_ID = ce.EMP_ID WHERE A.REQ_ID = @REQ_ID --TABLE 1 DVCM KHOI CNTT IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00')) BEGIN SELECT TOP 1 A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(D.APPROVE_DT,'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID LEFT JOIN PL_PROCESS D ON A.REQ_ID = D.REQ_ID AND D.PROCESS_ID = 'S_GDDVDVCM' AND A.MAKER_ID = D.CHECKER_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00' ORDER BY D.ID DESC SET @SHOW_TABLE_DVCM_IT = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 2 DVCM KHAC IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00')) BEGIN SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'S_GDDVDVCM' AND PP.CHECKER_ID = A.MAKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00' ORDER BY A.COST_ID SET @SHOW_TABLE_DVCM_KHAC = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS SELECT TOP 1 A.HO_NOTES, D.TLFullName, ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND (PP.PROCESS_ID = 'S_DVCM' OR PP.PROCESS_ID = 'S_TBP_QLTS') AND PP.CHECKER_ID = PL.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_DOC A LEFT JOIN PL_REQUEST_PROCESS PL ON A.REQ_ID = PL.REQ_ID AND PL.PROCESS_ID = 'QLTS_N' LEFT JOIN CM_EMPLOYEE_LOG C ON PL.CHECKER_ID = C.USER_DOMAIN LEFT JOIN TL_USER D ON C.USER_DOMAIN = D.TLNANME WHERE A.REQ_ID = @REQ_ID --TABLE 4 PHE DUYET DVCM KHOI CNTT IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00' ORDER BY C.DEP_ID END ELSE BEGIN SELECT '' AS NOTES END END ELSE BEGIN SELECT N'' NOTES END --table 5 PHÊ DUYỆT PHÒNG QLTS IF(EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'QLTS_D')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, C.TLFullName, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'QLTS_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'QLTS_D' END ELSE BEGIN SELECT N'' NOTES, '' TLFullName, '' POS_NAME END --Phucvh table 6 USER TẠO DUYỆT SELECT TOP 1 C.TLFullName AS USER_CREATE, CHAR(13) + ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'S_DVKD' AND PP1.CHECKER_ID = A.MAKER_ID ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME, E.TLFullName AS USER_APPROVE, CHAR(13) + ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'DVKD_D' ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_1 FROM TR_REQUEST_SHOP_DOC A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN --NGƯỜI TẠO LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --NGƯỜI TẠO LEFT JOIN PL_REQUEST_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND PP.PROCESS_ID = 'APPNEW' LEFT JOIN CM_EMPLOYEE_LOG D ON PP.CHECKER_ID = D.USER_DOMAIN --NGƯỜI DUYỆT LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME --NGƯỜI DUYỆT WHERE A.REQ_ID = @REQ_ID --PHUCVH TABLE 7 SỐ PYC SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID --PHUCVH TABLE 8 NGÀY TDV DUYỆT SELECT TOP 1 N'Ngày ' + CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT FROM PL_REQUEST_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW' END --TABLE 9 PHÊ DUYỆT DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00')) BEGIN SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00' ORDER BY C.DEP_ID END ELSE BEGIN SELECT '' AS NOTES FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00' END END ELSE BEGIN SELECT N'' NOTES END --TABLE 10 PHÊ DUYỆT TBP_QLTS IF(EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS WHERE REQ_ID = @REQ_ID AND PROCESS_ID = 'TBP_D')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS HO_NOTES, C.TLFullName, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'TBP_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'TBP_D' END ELSE BEGIN SELECT N'' HO_NOTES, '' TLFullName, '' POS_NAME END --TABLE 11 PHÊ DUYỆT TRUNG TÂM DỊCH VỤ NỘI BỘ DECLARE @PRICE_OF_ASSET DECIMAL = (SELECT MAX(b.BUY_PRICE) FROM TR_REQUEST_SHOP_DOC_DT A LEFT JOIN ASS_MASTER B ON A.ASS_ID = B.ASSET_ID WHERE A.REQ_DOC_ID = @REQ_ID) IF(@PRICE_OF_ASSET > 30000000) BEGIN IF(EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS prp WHERE prp.REQ_ID = @REQ_ID AND prp.PROCESS_ID = 'TTQLTS_D')) BEGIN SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'TTQLTS_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'TTQLTS_D' END END --SHOW TABLE DVCM IT IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN SELECT N'DVCMIT' AS MERGE_REGION END --SHOW TABLE DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'DVCMKHAC' AS MERGE_REGION END --SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'TITLEDVCM' AS MERGE_REGION END GO IF @@ERROR <> 0 SET NOEXEC ON GO PRINT N'Altering [dbo].[rpt_PYC_SUACHUA]' GO ALTER PROC [dbo].[rpt_PYC_SUACHUA] @REQ_ID VARCHAR(15) = NULL AS BEGIN DECLARE @SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL --TABLE 0 SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, am.ASSET_NAME AS ASS_NAME, ISNULL(am.ASSET_CODE,am.ASS_CODE_TMP) AS ASSET_CODE, ISNULL(PB.DEP_NAME,ISNULL(TT.DEP_NAME,K.DEP_NAME)) AS DEP_NAME, ce.EMP_NAME, '1' QTY_ETM, B.REPAIR_REASON AS REQ_CONTENT FROM TR_REQUEST_SHOP_DOC A LEFT JOIN TR_REQUEST_SHOP_DOC_DT B ON A.REQ_ID = B.REQ_DOC_ID LEFT JOIN CM_DEPARTMENT K ON B.SUBBRANCH_OLD = K.DEP_ID LEFT JOIN CM_DEPARTMENT TT ON B.FATHER_OLD = TT.DEP_ID LEFT JOIN CM_DEPARTMENT PB ON B.DEP_OLD = PB.DEP_ID LEFT JOIN ASS_MASTER am ON am.ASSET_ID = B.ASS_ID LEFT JOIN CM_EMPLOYEE ce ON B.EMP_ID = ce.EMP_ID WHERE A.REQ_ID = @REQ_ID --TABLE 1 DVCM KHOI CNTT IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00')) BEGIN SELECT TOP 1 A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(D.APPROVE_DT,'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID LEFT JOIN PL_PROCESS D ON A.REQ_ID = D.REQ_ID AND D.PROCESS_ID = 'S_GDDVDVCM' AND A.MAKER_ID = D.CHECKER_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00' SET @SHOW_TABLE_DVCM_IT = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 2 DVCM KHAC IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00')) BEGIN SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'S_GDDVDVCM' AND PP.CHECKER_ID = A.MAKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00' ORDER BY A.COST_ID SET @SHOW_TABLE_DVCM_KHAC = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS SELECT TOP 1 A.HO_NOTES, D.TLFullName, ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND (PP.PROCESS_ID = 'S_DVCM' OR PP.PROCESS_ID = 'S_TBP_QLTS') AND PP.CHECKER_ID = PL.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_DOC A LEFT JOIN PL_REQUEST_PROCESS PL ON A.REQ_ID = PL.REQ_ID AND PL.PROCESS_ID = 'QLTS_NL' LEFT JOIN CM_EMPLOYEE_LOG C ON PL.CHECKER_ID = C.USER_DOMAIN LEFT JOIN TL_USER D ON C.USER_DOMAIN = D.TLNANME --LEFT JOIN CM_EMPLOYEE D ON C.EMP_CODE = D.EMP_CODE WHERE A.REQ_ID = @REQ_ID --table 4 phe duyet dvcm khoi cntt IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00' ORDER BY C.DEP_ID END ELSE BEGIN SELECT '' AS NOTES END END ELSE BEGIN SELECT N'' NOTES END --table 5 PHÊ DUYỆT PHÒNG QLTS IF(EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --LEFT JOIN CM_EMPLOYEE C ON B.EMP_CODE = C.EMP_CODE WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'QLTS_D')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, C.TLFullName, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'QLTS_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --LEFT JOIN CM_EMPLOYEE C ON B.EMP_CODE = C.EMP_CODE WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'QLTS_D' ORDER BY A.ID DESC END ELSE BEGIN SELECT N'' NOTES END --Phucvh table 6 USER TẠO DUYỆT SELECT TOP 1 C.TLFullName AS USER_CREATE, CHAR(13) + ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'S_DVKD' AND PP1.CHECKER_ID = A.MAKER_ID ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME, E.TLFullName AS USER_APPROVE, CHAR(13) + ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'DVKD_D' ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_1 FROM TR_REQUEST_SHOP_DOC A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN --NGƯỜI TẠO LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --NGƯỜI TẠO LEFT JOIN PL_REQUEST_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND PP.PROCESS_ID = 'APPNEW' LEFT JOIN CM_EMPLOYEE_LOG D ON PP.CHECKER_ID = D.USER_DOMAIN --NGƯỜI DUYỆT LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME --NGƯỜI DUYỆT WHERE A.REQ_ID = @REQ_ID --PHUCVH TABLE 7 SỐ PYC SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID --PHUCVH TABLE 8 NGÀY TDV DUYỆT SELECT TOP 1 N'Ngày ' + CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT FROM PL_REQUEST_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW' ORDER BY pp.ID DESC END --TABLE 9 PHÊ DUYỆT DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00')) BEGIN SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00' ORDER BY C.DEP_ID END ELSE BEGIN SELECT '' AS NOTES FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00' END END ELSE BEGIN SELECT N'' NOTES END --TABLE 10 PHÊ DUYỆT TBP_QLTS SELECT TOP 1 ISNULL(A.NOTES,'') AS HO_NOTES, C.TLFullName, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'TBP_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --LEFT JOIN CM_EMPLOYEE C ON B.EMP_CODE = C.EMP_CODE WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'TBP_D' ORDER BY A.ID DESC --SHOW TABLE DVCM IT IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN SELECT N'DVCMIT' AS MERGE_REGION SET @SHOW_TABLE_DVCM_IT = '1' END --SHOW TABLE DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'DVCMKHAC' AS MERGE_REGION END --SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'TITLEDVCM' AS MERGE_REGION END GO IF @@ERROR <> 0 SET NOEXEC ON GO PRINT N'Altering [dbo].[rpt_PYC_THUHOI]' GO ALTER PROC [dbo].[rpt_PYC_THUHOI] @REQ_ID VARCHAR(15) = NULL AS BEGIN DECLARE @SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL --TABLE 0 SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, am.ASSET_NAME AS ASS_NAME, ISNULL(am.ASSET_CODE,am.ASS_CODE_TMP) AS ASSET_CODE, ISNULL(PB.DEP_NAME,ISNULL(TT.DEP_NAME,K.DEP_NAME)) AS DEP_NAME, ce.EMP_NAME, '1' QTY_ETM, CA.CONTENT AS REQ_CONTENT FROM TR_REQUEST_SHOP_DOC A LEFT JOIN TR_REQUEST_DOC_ASSET_DT B ON A.REQ_ID = B.REQ_DOC_ID LEFT JOIN CM_ALLCODE CA ON B.REQ_ASSET_REASON = CA.CDVAL AND CA.CDNAME = 'REASON_ASS_REQ' AND CA.CDTYPE = 'REQ_ASSET' LEFT JOIN CM_DEPARTMENT K ON B.SUBBRANCH_OLD = K.DEP_ID LEFT JOIN CM_DEPARTMENT TT ON B.FATHER_OLD = TT.DEP_ID LEFT JOIN CM_DEPARTMENT PB ON B.DEP_OLD = PB.DEP_ID LEFT JOIN ASS_MASTER am ON am.ASSET_ID = B.ASSET_ID LEFT JOIN CM_EMPLOYEE ce ON B.EMP_OLD = ce.EMP_ID WHERE A.REQ_ID = @REQ_ID --TABLE 1 DVCM KHOI CNTT IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00')) BEGIN SELECT DISTINCT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(D.APPROVE_DT,'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID LEFT JOIN PL_PROCESS D ON A.REQ_ID = D.REQ_ID AND D.PROCESS_ID = 'S_GDDVDVCM' AND A.MAKER_ID = D.CHECKER_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00' SET @SHOW_TABLE_DVCM_IT = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 2 DVCM KHAC IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00')) BEGIN SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'S_GDDVDVCM' AND PP.CHECKER_ID = A.MAKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00' ORDER BY A.COST_ID SET @SHOW_TABLE_DVCM_KHAC = '1' END ELSE BEGIN SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME END --TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS SELECT TOP 1 A.HO_NOTES, D.TLFullName, ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND (PP.PROCESS_ID = 'S_DVCM' OR PP.PROCESS_ID = 'S_TBP_QLTS') AND PP.CHECKER_ID = PL.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM TR_REQUEST_SHOP_DOC A LEFT JOIN PL_REQUEST_PROCESS PL ON A.REQ_ID = PL.REQ_ID AND PL.PROCESS_ID = 'QLTS_N' LEFT JOIN CM_EMPLOYEE_LOG C ON PL.CHECKER_ID = C.USER_DOMAIN LEFT JOIN TL_USER D ON C.USER_DOMAIN = D.TLNANME WHERE A.REQ_ID = @REQ_ID --TABLE 4 PHE DUYET DVCM KHOI CNTT IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00' ORDER BY C.DEP_ID END ELSE BEGIN SELECT '' AS NOTES END END ELSE BEGIN SELECT N'' NOTES END --table 5 PHÊ DUYỆT PHÒNG QLTS IF(EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'QLTS_D')) BEGIN SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, C.TLFullName, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'QLTS_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'QLTS_D' END ELSE BEGIN SELECT N'' NOTES END --Phucvh table 6 USER TẠO DUYỆT SELECT TOP 1 C.TLFullName AS USER_CREATE, CHAR(13) + ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'S_DVKD' AND PP1.CHECKER_ID = A.MAKER_ID ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME, E.TLFullName AS USER_APPROVE, CHAR(13) + ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP1.APPROVE_DT FROM PL_PROCESS PP1 WHERE PP1.REQ_ID = A.REQ_ID AND PP1.PROCESS_ID = 'DVKD_D' ORDER BY PP1.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_1 FROM TR_REQUEST_SHOP_DOC A LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN --NGƯỜI TẠO LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME --NGƯỜI TẠO LEFT JOIN PL_REQUEST_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND PP.PROCESS_ID = 'APPNEW' LEFT JOIN CM_EMPLOYEE_LOG D ON PP.CHECKER_ID = D.USER_DOMAIN --NGƯỜI DUYỆT LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME --NGƯỜI DUYỆT WHERE A.REQ_ID = @REQ_ID ORDER BY PP.ID DESC --PHUCVH TABLE 7 SỐ PYC SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID --PHUCVH TABLE 8 NGÀY TDV DUYỆT SELECT TOP 1 N'Ngày ' + CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT FROM PL_REQUEST_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW' ORDER BY pp.ID DESC END --TABLE 9 PHÊ DUYỆT DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00')) BEGIN SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'DVCM_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00' ORDER BY C.DEP_ID END ELSE BEGIN SELECT '' AS NOTES FROM TR_REQUEST_SHOP_COSTCENTER A LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00' END END ELSE BEGIN SELECT N'' NOTES END --TABLE 10 PHÊ DUYỆT TBP_QLTS SELECT TOP 1 ISNULL(A.NOTES,'') AS HO_NOTES, C.TLFullName, ISNULL(B.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.PROCESS_ID = 'TBP_D' AND PP.CHECKER_ID = A.CHECKER_ID ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME FROM PL_REQUEST_PROCESS A LEFT JOIN CM_EMPLOYEE_LOG B ON A.CHECKER_ID = B.USER_DOMAIN LEFT JOIN TL_USER C ON B.USER_DOMAIN = C.TLNANME WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'TBP_D' --SHOW TABLE DVCM IT IF(@SHOW_TABLE_DVCM_IT IS NOT NULL) BEGIN SELECT N'DVCMIT' AS MERGE_REGION END --SHOW TABLE DVCM KHÁC IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'DVCMKHAC' AS MERGE_REGION END --SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL) BEGIN SELECT N'TITLEDVCM' AS MERGE_REGION END GO IF @@ERROR <> 0 SET NOEXEC ON GO PRINT N'Altering [dbo].[TR_REQUEST_SHOP_DOC_Search]' GO ALTER PROCEDURE [dbo].[TR_REQUEST_SHOP_DOC_Search] @p_REQ_ID varchar(15) = NULL, @p_REQ_CODE nvarchar(100) = NULL, @p_REQ_NAME nvarchar(200) = NULL, @p_REQ_DT VARCHAR(20) = NULL, @p_REQ_TYPE varchar(20) = NULL, @p_REQ_CONTENT NVARCHAR(1000)=NULL, @p_TOTAL_AMT decimal = NULL, @p_NOTES nvarchar(1000) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_MAKER_ID NVARCHAR(100) = NULL, @p_CREATE_DT VARCHAR(20) = NULL, @p_AUTH_STATUS varchar(50) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL, @p_APPROVE_DT VARCHAR(20) = NULL, @p_RECEIVE_BRANCH VARCHAR(15) = NULL, @p_USERNAME VARCHAR(100) = NULL, @p_BRANCH_ID VARCHAR(15)=NULL, @p_DEP_ID VARCHAR(15)=NULL, @p_STATUS VARCHAR(15)=NULL, @p_TOP INT = 10, @p_DVKD_MANAGE_APP_FROM VARCHAR(20) = NULL, @p_DVKD_MANAGE_APP_TO VARCHAR(20) = NULL, @p_REGION_ID varchar(15) = NULL, @p_CDTYPE_PYC VARCHAR(20) = NULL --Phucvh Truyền thêm CDTYPE để xác định loại PYC AS BEGIN -- PAGING DECLARE @ROLE_KT VARCHAR(50) = (SELECT TU.RoleName FROM TL_USER TU WHERE TU.TLNANME = @p_USERNAME) DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20) DECLARE @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20)) -- INSERT INTO @ROLE_LOGIN SELECT DisplayName AS ROLE_USER FROM AbpRoles WHERE Id IN (SELECT A.RoleId FROM AbpUserRoles A -- LEFT JOIN TL_USER B ON A.UserId = B.ID -- WHERE B.TLNANME = @p_USERNAME) INSERT INTO @ROLE_LOGIN SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_USERNAME) tugr SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME) SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME) DECLARE @lstCOST TABLE ( COST_ID VARCHAR(20) ) INSERT INTO @lstCOST SELECT COST_ID FROM dbo.CM_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID -- TienLee 11/14/21 -- -- DECLARE @TempSTATUS TABLE ( STATUS VARCHAR(20) ) IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='QLTS' )) BEGIN INSERT INTO @TempSTATUS VALUES('DVKD') INSERT INTO @TempSTATUS VALUES('DVCM') END ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' )) BEGIN INSERT INTO @TempSTATUS VALUES('QLTS_N') INSERT INTO @TempSTATUS VALUES('DVCM') END ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='CVMS' )) BEGIN INSERT INTO @TempSTATUS VALUES('QLTS_N') INSERT INTO @TempSTATUS VALUES('QLTS_XL') INSERT INTO @TempSTATUS VALUES('DVCM') END DECLARE @lstBRANCH_DEP TABLE ( BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20) ) -- IF(NOT EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' OR ROLE_USER='CVMS_TBP' )) -- BEGIN -- INSERT INTO @lstBRANCH_DEP -- (BRANCH_ID,DEP_ID) -- SELECT TLSUBBRID,DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME -- -- INSERT INTO @lstBRANCH_DEP -- (BRANCH_ID,DEP_ID) -- SELECT BRANCH_ID,DEP_ID FROM dbo.CM_KHOI_DT -- WHERE KHOI_ID IN (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID= @DEP_ID AND BRANCH_ID=@BRANCH_ID) -- END -- -- -- -- DECLARE @tbDep TABLE(DEP_ID VARCHAR(20)) --INSERT INTO @tbDep --SELECT ckd.DEP_ID FROM CM_DEPARTMENT cd ----LEFT JOIN CM_KHOI ck ON cd.DEP_CODE=ck.KHOI_CODE ----LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID --WHERE cd.DEP_ID=@p_DEP_ID DECLARE @REQ_ID_Temp TABLE(REQ_ID VARCHAR(20)) INSERT INTO @REQ_ID_Temp SELECT A.REQ_ID FROM TR_REQUEST_SHOP_DOC A LEFT JOIN PL_REQUEST_PROCESS B ON A.REQ_ID = B.REQ_ID AND B.STATUS = 'C' WHERE (EXISTS(SELECT RL.ROLE_USER FROM @ROLE_LOGIN RL WHERE RL.BRANCH_ID = B.BRANCH_ID AND (B.BRANCH_ID = 'DV0001' AND B.DEP_ID = RL.DEP_ID OR B.BRANCH_ID <> 'DV0001') AND RL.ROLE_USER = B.ROLE_USER) OR EXISTS(SELECT * FROM @ROLE_LOGIN RL JOIN PL_REQUEST_PROCESS C ON C.REQ_ID = A.REQ_ID AND C.STATUS = 'P' AND RL.BRANCH_ID = C.BRANCH_ID AND (C.BRANCH_ID = 'DV0001' AND C.DEP_ID = RL.DEP_ID OR C.BRANCH_ID <> 'DV0001') AND RL.ROLE_USER = C.ROLE_USER) OR A.MAKER_ID = @p_USERNAME) GROUP BY A.REQ_ID IF((EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_SLAS' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) BEGIN IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0) BEGIN -- PAGING BEGIN SELECT A.REQ_ID, A.REQ_CODE, A.REQ_NAME, --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS, A.DEP_ID, A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, --I.CONTENT AS REQ_STATUS_NAME, CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' ELSE I.CONTENT END REQ_STATUS_NAME, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE, CASE WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE G.BRANCH_NAME END AS BRANCH_NAME, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME, I.CONTENT AS REQ_TYPE_NAME, CASE WHEN A.STATUS = 'APPROVE'AND A.AUTH_STATUS = 'A' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE '' END AS COLOR --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_SHOP_DOC A LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC -- LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C' --LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE' LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') -- AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') -- AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) OR @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) OR (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) ) --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) --AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') -- AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '') AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT') OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS) AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') ORDER BY A.REQ_DT DESC -- PAGING END END ELSE BEGIN -- PAGING BEGIN SELECT TOP(CONVERT(INT,@p_TOP)) A.REQ_ID, A.REQ_CODE, A.REQ_NAME, --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS, A.DEP_ID, A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, --I.CONTENT AS REQ_STATUS_NAME, CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' ELSE I.CONTENT END REQ_STATUS_NAME, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE, CASE WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE G.BRANCH_NAME END AS BRANCH_NAME, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME, I.CONTENT AS REQ_TYPE_NAME, CASE WHEN A.STATUS = 'APPROVE'AND A.AUTH_STATUS = 'A' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE '' END AS COLOR --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_SHOP_DOC A LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC -- LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C' --LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE' LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') -- AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') -- AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) OR @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) OR (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) ) --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) --AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') -- AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '') AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT') OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS) AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') ORDER BY A.REQ_DT DESC -- PAGING END END END ELSE BEGIN IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0) BEGIN -- PAGING BEGIN SELECT A.REQ_ID, A.REQ_CODE, A.REQ_NAME, --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS, A.DEP_ID, A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, --I.CONTENT AS REQ_STATUS_NAME, CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' ELSE I.CONTENT END REQ_STATUS_NAME, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE, CASE WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE G.BRANCH_NAME END AS BRANCH_NAME, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME, CASE WHEN A.STATUS = 'APPROVE'AND A.AUTH_STATUS = 'A' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE '' END AS COLOR --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_SHOP_DOC A LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC -- LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C' --LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE' LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') -- AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') -- AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) OR @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) OR (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) ) --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) --AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') -- AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '') AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT') OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS) AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') ORDER BY A.REQ_DT DESC -- PAGING END END ELSE BEGIN -- PAGING BEGIN SELECT TOP(CONVERT(INT,@p_TOP)) A.REQ_ID, A.REQ_CODE, A.REQ_NAME, --ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, CASE WHEN A.REQ_TYPE = 'CPTS' AND A.IS_DONE = '1' AND A.STATUS = 'DONE' THEN 'APPROVE' ELSE A.STATUS END AS STATUS, A.DEP_ID, A.HO_NOTES, K.CONTENT AS REQ_TYPE_NAME, --I.CONTENT AS REQ_STATUS_NAME, CASE WHEN A.IS_DONE = '1' AND A.STATUS = 'APPROVE' THEN N'Hoàn tất' ELSE I.CONTENT END REQ_STATUS_NAME, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE, CASE WHEN A.DEP_ID IS NOT NULL AND A.DEP_ID <> '' THEN G.BRANCH_NAME + ' - ' + CD.DEP_NAME ELSE G.BRANCH_NAME END AS BRANCH_NAME, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME, CASE WHEN A.STATUS = 'APPROVE'AND A.AUTH_STATUS = 'A' AND DATEDIFF(DAY,GETDATE(),DATEADD(DAY,CONVERT(INT,SP.ParaValue),A.SEND_APP_DT)) < 0 THEN '#f2dede' ELSE '' END AS COLOR --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_SHOP_DOC A LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQASSTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] AND I.CDTYPE = @p_CDTYPE_PYC -- LEFT JOIN PL_REQUEST_PROCESS prp1 ON prp1.REQ_ID = A.REQ_ID AND prp1.STATUS = 'C' --LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID LEFT JOIN SYS_PARAMETERS SP ON 1=1 AND SP.ParaKey = 'NUM_OF_DAYS_LATE' LEFT JOIN CM_DEPARTMENT CD ON A.DEP_ID = CD.DEP_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') --AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') --AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') AND (@p_DVKD_MANAGE_APP_FROM IS NULL AND @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) OR @p_DVKD_MANAGE_APP_FROM IS NULL AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) OR (CONVERT(DATE, A.REQ_DT, 103)) >= (CONVERT(DATE, @p_DVKD_MANAGE_APP_FROM, 103)) AND (CONVERT(DATE, A.REQ_DT, 103)) <= (CONVERT(DATE, @p_DVKD_MANAGE_APP_TO, 103)) ) --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' --OR A.REQ_ID LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) --AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') -- AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE = '') AND (A.STATUS IN ('NEW','REJECT') AND A.MAKER_ID = @p_USERNAME OR A.STATUS NOT IN ('NEW','REJECT') OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND (@p_STATUS IS NULL OR @p_STATUS = '' OR A.STATUS = @p_STATUS) AND (EXISTS(SELECT 1 FROM @REQ_ID_Temp RL WHERE RL.REQ_ID = A.REQ_ID) OR (@ROLE_KT IN ('GDV','KSV') AND A.AUTH_STATUS = 'A' ) OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS',NULL,NULL) US WHERE US.TLNANME = @p_USERNAME)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') ORDER BY A.REQ_DT DESC -- PAGING END END END END GO ALTER TRIGGER dbo.TRIG_CM_EMPLOYEE_SYNC_Ins ON dbo.CM_EMPLOYEE_SYNC AFTER INSERT AS BEGIN --STORE ĐỒNG BỘ NHÂN VIÊN VÀ ĐIỀU CHUYỂN TÀI SẢN NHÂN SỰ --29/09/22 SET NOCOUNT ON; IF (EXISTS(SELECT TOP 1 MaNS FROM INSERTED)) BEGIN DECLARE @MaDV_Cu_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVCu FROM INSERTED) DECLARE @MaDV_Moi_SYNC VARCHAR(500) = (SELECT TOP 1 MaDVMoi FROM INSERTED) --UPDATE MÃ MỚI DO KT UPDATE IF(@MaDV_Cu_SYNC = '0100') BEGIN SET @MaDV_Cu_SYNC = '0600' END IF(@MaDV_Cu_SYNC = '0101') BEGIN SET @MaDV_Cu_SYNC = '0601' END IF(@MaDV_Moi_SYNC = '0100') BEGIN SET @MaDV_Moi_SYNC = '0600' END IF(@MaDV_Moi_SYNC = '0101') BEGIN SET @MaDV_Moi_SYNC = '0601' END --MAP DATA HR VỚI KT IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC)) BEGIN SET @MaDV_Cu_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Cu_SYNC) END IF(EXISTS(SELECT 1 FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC)) BEGIN SET @MaDV_Moi_SYNC = (SELECT TOP 1 KT_ID FROM CM_BRANCH_DEP_MAP WHERE HR_ID = @MaDV_Moi_SYNC) END DECLARE @MACD_CU_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDCu FROM INSERTED) DECLARE @MACD_MOI_SYNC VARCHAR(500) = (SELECT TOP 1 MaCDMoi FROM INSERTED) --START: PARAM INSERT NHÂN VIÊN MỚI DECLARE @l_EMP_ID VARCHAR(500) DECLARE @p_EMP_CODE varchar(500) = (SELECT TOP 1 MaNS FROM INSERTED) DECLARE @p_EMP_NAME nvarchar(500) = (SELECT TOP 1 HoTen FROM INSERTED) DECLARE @p_BRANCH_ID varchar(15) = NULL DECLARE @p_DEP_ID varchar(15) = NULL DECLARE @p_POS_CODE VARCHAR(50) = NULL DECLARE @p_POS_NAME NVARCHAR(MAX) = NULL DECLARE @p_NOTES nvarchar(1000) = N'ĐỒNG BỘ' DECLARE @p_RECORD_STATUS varchar(1) = '1' DECLARE @p_AUTH_STATUS varchar(1) = 'A' DECLARE @p_MAKER_ID varchar(15) = 'ADMIN' DECLARE @p_CREATE_DT VARCHAR(50) = GETDATE() DECLARE @p_CHECKER_ID varchar(15) = 'ADMIN' DECLARE @p_APPROVE_DT VARCHAR(50) = GETDATE() --END: PARAM INSERT NHÂN VIÊN MỚI --START: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG DECLARE @I_EMP_ID VARCHAR(20) = (SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED)) DECLARE @I_STATUS VARCHAR(5) DECLARE @C_Ma_DV_Cu VARCHAR(500) = NULL --MÃ ĐV CŨ ĐỂ CHECK IF DECLARE @C_Ma_DV_Moi VARCHAR(500) = NULL --MÃ DV MỚI ĐỂ CHECK IF DECLARE @C_Ma_PB_Cu VARCHAR(500) = NULL --MÃ PB CŨ ĐỂ CHECK IF DECLARE @C_Ma_PB_Moi VARCHAR(500) = NULL --MÃ PB MỚI ĐỂ CHECK IF --END: PARAM INSERT CM_EMPLOYEE_TRANSFER_LOG DECLARE @TLNAME VARCHAR(200) = NULL DECLARE @BLOCK_ID_OLD VARCHAR(15) = NULL --Mã khối cũ check điều chuyển nhân sự DECLARE @CENTER_ID_OLD VARCHAR(15) = NULL --Mã trung tâm cũ check điều chuyển nhân sự DECLARE @DEP_ID_OLD VARCHAR(15) = NULL --Mã phòng ban cũ check điều chuyển nhân sự DECLARE @BLOCK_ID_NEW VARCHAR(15) = NULL --Mã khối mới check điều chuyển nhân sự DECLARE @CENTER_ID_NEW VARCHAR(15) = NULL --Mã trung tâm mới check điều chuyển nhân sự DECLARE @DEP_ID_NEW VARCHAR(15) = NULL --Mã phòng ban mới check điều chuyển nhân sự --START: PARAM INSERT --CHECK XEM ĐƠN VỊ/PHÒNG BAN CỦA NHÂN VIÊN MỚI CỬA KH LƯU TRONG CỘT MaDVCu HAY CỘT MaDVMoi IF (EXISTS(SELECT TOP 1 MaDVCu FROM INSERTED WHERE MaDVCu IS NOT NULL AND MaDVCu <> '')) BEGIN --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC)) BEGIN SET @p_BRANCH_ID = 'DV0001' SET @C_Ma_DV_Cu = 'DV0001' SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC) SET @C_Ma_PB_Cu = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Cu_SYNC) END ELSE BEGIN SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC) SET @C_Ma_DV_Cu = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Cu_SYNC) END END --ELSE IF(EXISTS(SELECT TOP 1 MaDVMoi FROM INSERTED WHERE MaDVMoi IS NOT NULL AND MaDVMoi <> '')) BEGIN --CHECK XEM MÃ LÀ ĐƠN VỊ HAY PHÒNG BAN IF(EXISTS(SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = (SELECT MaDVMoi FROM INSERTED))) BEGIN SET @p_BRANCH_ID = 'DV0001' SET @C_Ma_DV_Moi = 'DV0001' SET @p_DEP_ID = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC) SET @C_Ma_PB_Moi = (SELECT TOP 1 cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @MaDV_Moi_SYNC) END ELSE BEGIN SET @p_BRANCH_ID = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC) SET @C_Ma_DV_Moi = (SELECT TOP 1 cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @MaDV_Moi_SYNC) END END --GET KHỐI, TRUNG TÂM, PHÒNG BAN EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Cu ,@BLOCK_ID_OLD OUT ,@CENTER_ID_OLD OUT , @DEP_ID_OLD OUT --GET KHỐI, TRUNG TÂM, PHÒNG BAN EXEC CM_DEPARTMENT_GET_BLOCK_CENTER @C_Ma_PB_Moi, @BLOCK_ID_NEW OUT ,@CENTER_ID_NEW OUT ,@DEP_ID_NEW OUT IF(@p_BRANCH_ID <> 'DV0001') BEGIN SET @p_DEP_ID = NULL END PRINT @C_Ma_DV_Cu PRINT @C_Ma_DV_Moi IF((@C_Ma_DV_Cu IS NULL OR @C_Ma_DV_Cu = '') AND (@C_Ma_DV_Moi IS NULL OR @C_Ma_DV_Moi = '')) BEGIN --N'Mã DV cũ và Mã DV mới không có giá trị' PRINT 1 END ELSE BEGIN IF((SELECT COUNT(*) FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE) > 1) BEGIN SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE AND CEL.BRANCH_CODE = @MaDV_Cu_SYNC AND (CEL.DEP_CODE = @C_Ma_PB_Cu OR @C_Ma_PB_Cu IS NULL)) END ELSE BEGIN SET @TLNAME = (SELECT TOP 1 CEL.USER_DOMAIN FROM CM_EMPLOYEE_LOG CEL WHERE CEL.EMP_CODE = @p_EMP_CODE) END --CHECK DATA CHỨC DANH LƯU CỘT CDMOI HAY CDCU IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDMoi IS NOT NULL AND MaCDMoi <> '')) BEGIN --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_MOI_SYNC)) BEGIN INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID) VALUES (@MACD_MOI_SYNC, (SELECT TOP 1 CDMoi FROM INSERTED), NULL, NULL); END SET @p_POS_CODE = @MACD_MOI_SYNC SET @p_POS_NAME = (SELECT TOP 1 CDMoi FROM INSERTED) END ELSE IF(EXISTS(SELECT 1 FROM INSERTED WHERE MaCDCu IS NOT NULL AND MaCDCu <> '')) BEGIN --NẾU CHỨC DANH CHƯA CÓ TRONG HỆ THỐNG THÌ THÊM MỚI CHỨC DANH IF(NOT EXISTS(SELECT 1 FROM CM_MAPPING_CHUCDANH_ROLE WHERE MA_CHUC_DANH = @MACD_CU_SYNC)) BEGIN INSERT INTO CM_MAPPING_CHUCDANH_ROLE (MA_CHUC_DANH, TEN_CHUC_DANH, NHOM_CHUC_DANH, ROLE_ID) VALUES (@MACD_CU_SYNC, (SELECT TOP 1 CDCu FROM INSERTED), NULL, NULL); END SET @p_POS_CODE = @MACD_CU_SYNC SET @p_POS_NAME = (SELECT TOP 1 CDCu FROM INSERTED) END --CHECK NHÂN VIÊN XEM CÓ XEM TRONG HỆ THỐNG KHÔNG IF(EXISTS(SELECT TOP 1 ce.EMP_ID FROM CM_EMPLOYEE ce WHERE ce.EMP_CODE = (SELECT MaNS FROM INSERTED))) BEGIN IF(EXISTS( SELECT atmd.ASSET_ID FROM ASS_TRANSFER_MULTI_DT atmd WHERE (atmd.EMP_ID = @I_EMP_ID AND atmd.EMP_ID_OLD = @I_EMP_ID AND (atmd.BRANCH_ID = @C_Ma_DV_Moi AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'') AND ISNULL(atmd.KHOI_ID,'') = ISNULL(@BLOCK_ID_NEW,'') AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'') AND ISNULL(atmd.CENTER_ID,'') = ISNULL(@CENTER_ID_NEW,'') AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'') AND ISNULL(atmd.DEPT_ID,'') = ISNULL(@DEP_ID_NEW,'')) AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC CHO CÙNG NV OR (atmd.EMP_ID_OLD = @I_EMP_ID AND atmd.EMP_ID <> @I_EMP_ID AND atmd.BRANCH_ID_OLD = @C_Ma_DV_Cu AND ISNULL(atmd.KHOI_ID_OLD,'') = ISNULL(@BLOCK_ID_OLD,'') AND ISNULL(atmd.CENTER_ID_OLD,'') = ISNULL(@CENTER_ID_OLD,'') AND ISNULL(atmd.DEPT_ID_OLD,'') = ISNULL(@DEP_ID_OLD,'') AND atmd.AUTH_STATUS_KT <> 'A') --ĐIỀU CHUYỂN TS TỪ ĐV NÀY SANG ĐV KHÁC NHƯNG KHÁC NV UNION ALL SELECT am.ASSET_ID FROM ASS_MASTER am WHERE am.BRANCH_ID = @C_Ma_DV_Cu AND ISNULL(am.DEPT_ID,'') = ISNULL(@C_Ma_PB_Cu,'') AND am.EMP_ID = @I_EMP_ID )) SET @I_STATUS = '0' ELSE SET @I_STATUS = '1' INSERT INTO CM_EMPLOYEE_TRANSFER_LOG (EMP_ID, TRANSFER_DT, BRANCH_ID_OLD, DEP_ID_OLD, BRANCH_ID_NEW, DEP_ID_NEW, STATUS) VALUES (@I_EMP_ID, CONVERT(DATETIME,(SELECT TOP 1 TGTao FROM INSERTED),103), @C_Ma_DV_Cu,@C_Ma_PB_Cu, @C_Ma_DV_Moi, @C_Ma_PB_Moi, @I_STATUS); --CHECK TRƯỜNG HỢP 1 NHÂN VIÊN CÓ QUYỀN 2 CHI NHÁNH TRỞ TRÊN (KHÁC TLNAME) --PHUCVH 22/12/22 CHECK NẾU MADV,PB MỚI THAY ĐỔI THÌ UPDATE LẠI MADV,PB CHO NHANVIEN IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE AND ISNULL(@p_BRANCH_ID,'') <> '' AND ((ISNULL(@p_BRANCH_ID,'') = 'DV0001' AND ISNULL(@p_DEP_ID,'') <> '') OR (ISNULL(@p_BRANCH_ID,'') <> 'DV0001' AND ISNULL(@p_DEP_ID,'') = '')) AND (ISNULL(A.BRANCH_ID,'') <> ISNULL(@p_BRANCH_ID,'') OR ISNULL(A.DEP_ID,'') <> ISNULL(@p_DEP_ID,'')))) BEGIN --NẾU LÀ DVCM THÌ SKIP. KHÔNG UPDATE BRANCH VÀ DEP IF(NOT EXISTS(SELECT 1 FROM TL_USER A LEFT JOIN AbpUserRoles B ON A.ID = B.UserId LEFT JOIN AbpRoles C ON B.RoleId = C.Id WHERE A.TLNANME = @TLNAME AND C.DisplayName = 'DVCM')) BEGIN UPDATE CM_EMPLOYEE SET BRANCH_ID = @p_BRANCH_ID, DEP_ID = @p_DEP_ID WHERE EMP_CODE = @p_EMP_CODE --CHECK UPDATE TL_USER VÀ CM_EMPLOYEE_LOG IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE_LOG WHERE EMP_CODE = @p_EMP_CODE)) BEGIN UPDATE CM_EMPLOYEE_LOG SET BRANCH_CODE = (SELECT TOP 1 BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_ID), DEP_CODE = (SELECT TOP 1 DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID = @p_DEP_ID) WHERE EMP_CODE = @p_EMP_CODE AND USER_DOMAIN = @TLNAME UPDATE TL_USER SET TLSUBBRID = @p_BRANCH_ID, SECUR_CODE = @p_DEP_ID, DEP_ID = @p_DEP_ID WHERE TLNANME = @TLNAME END END END --PHUCVH 22/12/22 CHECK NẾU MA CHUC DANH THAY ĐỔI THÌ UPDATE IF(EXISTS(SELECT 1 FROM CM_EMPLOYEE A WHERE A.EMP_CODE = @p_EMP_CODE AND ISNULL(A.POS_CODE,'') <> ISNULL(@p_POS_CODE,''))) BEGIN UPDATE CM_EMPLOYEE SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME WHERE EMP_CODE = @p_EMP_CODE UPDATE CM_EMPLOYEE_LOG SET POS_CODE = @p_POS_CODE, POS_NAME = @p_POS_NAME WHERE EMP_CODE = @p_EMP_CODE AND USER_DOMAIN = @TLNAME END --IF @@Error <> 0 GOTO ABORT --SET @Message = N'Đồng bộ điều chuyển nhân sự thành công' END ELSE BEGIN --KHÔNG CÓ TRONG HỆ THỐNG --THÊM THÔNG TIN NHÂN VIÊN EXEC SYS_CodeMasters_Gen 'CM_EMPLOYEE', @l_EMP_ID out INSERT INTO CM_EMPLOYEE([EMP_ID],[EMP_CODE],[EMP_NAME],[BRANCH_ID],[DEP_ID],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[POS_CODE],[POS_NAME]) VALUES(@l_EMP_ID ,@p_EMP_CODE ,@p_EMP_NAME ,@p_BRANCH_ID ,@p_DEP_ID ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_POS_CODE, @p_POS_NAME ) --IF @@Error <> 0 GOTO ABORT --SET @Message = N'Đồng bộ nhân viên mới thành công' END END END END