AMS - VB

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 (''))

                                , '|', '<br />'))

          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 (''))

                                        , '|', '<br />'))

          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