DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROCEDURE [dbo].[rpt_PL_REQUEST_DOC_DT_ById]

@P_REQ_ID varchar(50)

AS

DECLARE

			 @l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,

			 @l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0

			DECLARE @listTRADE TABLE

			(

				GOODS_ID VARCHAR(20),

				TRADE_ID VARCHAR(20),

				PLAN_ID VARCHAR(20),

				AMT_APP DECIMAL(18,2),

				AMT_EXE DECIMAL (18,2),

				AMT_ETM DECIMAL (18,2),

				AMT_TF DECIMAL (18,2),

				AMT_RECEIVE_TF DECIMAL (18,2)

			)

			IF(EXISTS(SELECT * FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID='APPROVE'))

			BEGIN

				IF(EXISTS(SELECT * FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID))

				BEGIN

					INSERT INTO @listTRADE

					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)

					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF FROM dbo.PL_TRADEDETAIL_HIST WHERE REQ_ID=@P_REQ_ID

				END

				ELSE

				BEGIN

					INSERT INTO @listTRADE

					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)

					SELECT PT.GOODS_ID,PT.TRADE_ID,PT.PLAN_ID,PT.AMT_APP,PT.AMT_EXE,PT.AMT_ETM-PLDT.TOTAL_AMT,PT.AMT_TF-PLFT.TOTAL_AMT,PT.AMT_RECEIVE_TF-PLTT.TOTAL_AMT FROM dbo.PL_TRADEDETAIL PT

					LEFT JOIN 

					(

					SELECT TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM

					dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@P_REQ_ID

					GROUP BY TRADE_ID

					) PLDT ON PLDT.TRADE_ID = PT.TRADE_ID

					LEFT JOIN 

					(

					SELECT FR_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM

					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID

					GROUP BY FR_TRADE_ID

					) PLFT ON PLFT.FR_TRADE_ID = PT.TRADE_ID

					LEFT JOIN 

					(

					SELECT TO_TRADE_ID, SUM(TOTAL_AMT) TOTAL_AMT FROM

					dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@P_REQ_ID

					GROUP BY TO_TRADE_ID

					) PLTT ON PLTT.TO_TRADE_ID = PT.TRADE_ID

					WHERE (PT.TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR PT.TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))

				END

			END

			ELSE

			BEGIN

					INSERT INTO @listTRADE

					( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF)

					SELECT GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF FROM 

					dbo.PL_TRADEDETAIL WHERE (TRADE_ID IN (SELECT TRADE_ID FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) OR 

					TRADE_ID IN (SELECT FR_TRADE_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID))

			END

			SELECT  ROW_NUMBER() OVER (ORDER BY HH.HH_NAME) AS STT, HH.HH_NAME,PLDT.DESCRIPTION,PLDT.CURRENCY,PLDT.UNIT_NAME,

			FORMAT(ISNULL(PLDT.QUANTITY,0), '#,#') AS QUANTITY,

			FORMAT(ISNULL(PLDT.PRICE,0), '#,#.##') AS PRICE,

			FORMAT(ISNULL(PLDT.QUANTITY,0) * ISNULL(PLDT.PRICE,0), '#,#') AS TOTAL_AMT_NT,

			FORMAT(ISNULL(PLDT.TAXES,0), '#,#') AS TAXES,

			FORMAT(ISNULL(PLDT.EXCHANGE_RATE,1), '#,#') AS EXCHANGE_RATE,

			FORMAT(ISNULL(PLDT.QUANTITY,0) * ISNULL(PLDT.PRICE,0)+ISNULL(PLDT.TAXES,0), '#,#') AS TOTAL_AMT_TAX,

			FORMAT((ISNULL(PLDT.QUANTITY,0) * ISNULL(PLDT.PRICE,0)+ISNULL(PLDT.TAXES,0)) * ISNULL(PLDT.EXCHANGE_RATE,1), '#,#') AS TOTAL_AMT_EXC,

			CASE

				WHEN PLDT.TRADE_TYPE='VCCB' THEN N'Theo quy định của BVB'

				ELSE N'Chỉ định'

			END AS HTMS,

			PLDT.NAME AS REASON,

			CASE

				WHEN PLDT.TRADE_TYPE='VCCB' THEN '' ELSE CS.SUP_NAME END SUP_NAME,

			CG.GD_NAME

			FROM 

			dbo.PL_REQUEST_DOC_DT PLDT

			LEFT JOIN dbo.CM_HANGHOA HH ON PLDT.HANGHOA_ID=HH.HH_ID

			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID

			LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID

			LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=PLDT.SUP_ID

			WHERE PLDT.REQ_ID=@P_REQ_ID

			DECLARE @APP_DATE DATETIME = (SELECT APPROVE_DT FROM TR_REQUEST_DOC WHERE REQ_ID = @P_REQ_ID)

			SELECT ROW_NUMBER() OVER (ORDER BY ISNULL(CG.GD_NAME,LOG_DT.GOOD_NAME)) AS STT ,ISNULL(CG.GD_NAME,LOG_DT.GOOD_NAME) AS GD_NAME,

			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0) AS AMT_APP,

			ISNULL(PT.AMT_ETM,0) AS AMT_EXE,

			--CASE WHEN (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0) -ISNULL(PT.AMT_ETM,0)) < 0 THEN (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-SUM(ISNULL(PLDT.TOTAL_AMT,0))) 

			--	ELSE ISNULL(PT.AMT_ETM,0) END AS AMT_EXE,

			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) - ISNULL(PT.AMT_ETM,0) AS AMT_REMAIN,

			--CASE WHEN (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0)) < 0 THEN 0 ELSE (ISNULL(PT.AMT_APP,0)  + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0)) END AS AMT_REMAIN,

			SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT,

			ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) -ISNULL(PT.AMT_ETM,0) -SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT_FN,

			--CASE WHEN (ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0)) < 0 THEN 0

			--	ELSE (ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)-ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0) -SUM(ISNULL(PLDT.TOTAL_AMT,0))) END AS TOTAL_AMT_FN,

			CASE WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName				

				ELSE N'' END AS CHECKER_NAME,			

			'' AS XN_NAME

			FROM

			dbo.PL_REQUEST_DOC_DT PLDT

			LEFT JOIN 

			(

				SELECT A.TRADE_ID, ISNULL(SUM(A.TOTAL_AMT),0) AS AMT_EXE

				FROM PL_REQUEST_DOC_DT A

				INNER JOIN PL_REQUEST_DOC B ON A.REQ_ID = B.REQ_ID AND B.AUTH_STATUS = 'A'

				WHERE A.REQ_ID <> @P_REQ_ID

				AND A.APPROVE_DT <= @APP_DATE

				AND A.TRADE_ID IN (SELECT TRADE_ID FROM PL_REQUEST_DOC_DT WHERE REQ_ID = @P_REQ_ID AND TRADE_ID IS NOT NULL AND TRADE_ID <> '' GROUP BY TRADE_ID)

				GROUP BY A.TRADE_ID

			) AS TR_AMT ON TR_AMT.TRADE_ID = PLDT.TRADE_ID

			LEFT JOIN @listTRADE PT ON PT.TRADE_ID=PLDT.TRADE_ID

			LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PLDT.PLAN_ID

			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID

			LEFT JOIN dbo.PL_IMPORT_DT LOG_DT ON LOG_DT.TRADE_ID=PLDT.TRADE_ID

			LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLDT.CHECKER_ID

			LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLDT.REQ_ID

			WHERE PLDT.REQ_ID=@P_REQ_ID

			GROUP BY PT.GOODS_ID,PT.AMT_APP,/*PT.AMT_EXE*/TR_AMT.AMT_EXE,PT.AMT_RECEIVE_TF,PT.AMT_TF,PT.AMT_ETM,CG.GD_NAME,PLDT.REQ_ID,

							USC.TLFullName,PL.PROCESS_ID,LOG_DT.GOOD_NAME, PLDT.TRADE_ID

			-- Điều chuyển ngân sách

			SELECT  ROW_NUMBER() OVER (ORDER BY ISNULL(CGF.GD_NAME,LF_DT.GOOD_NAME)) AS STT ,ISNULL(CGF.GD_NAME,LF_DT.GOOD_NAME) AS FR_GD_NAME,

			(ISNULL(PTF.AMT_APP,0) + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0)) AS FR_AMT_APP, -- được duyệt (ngân sách trước khi điều chuyển)

			ISNULL(PTF.AMT_ETM,0) AS FR_AMT_EXE,

			ISNULL(PTF.AMT_APP,0)  + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0) -ISNULL(PTF.AMT_ETM,0) AS FR_AMT_REMAIN, -- còn lại (ngân sách trước khi điều chuyển)

			ISNULL(CGT.GD_NAME,LT_DT.GOOD_NAME) AS TO_GD_NAME,

			(ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)) AS TO_AMT_APP,

			ISNULL(PTT.AMT_ETM,0) AS TO_AMT_EXE,

			ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0) AS TO_AMT_REMAIN,

			ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_AMT,

			ISNULL(PTF.AMT_APP, 0) + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0) -  ISNULL((SELECT SUM(T.TOTAL_AMT) FROM dbo.PL_REQUEST_TRANSFER T WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_APP_FINAL,

			(ISNULL(PTF.AMT_APP,0)  + ISNULL(PTF.AMT_RECEIVE_TF,0)-ISNULL(PTF.AMT_TF,0) -ISNULL(PTF.AMT_ETM,0)) - ISNULL((SELECT SUM(T.TOTAL_AMT) 

			FROM dbo.PL_REQUEST_TRANSFER T

			WHERE T.FR_TRADE_ID=PLTF.FR_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) AS FR_AMT_REMAIN_FINAL,

		    CASE WHEN ISNULL(PLTF.TO_TRADE_ID,'') <> '' THEN

				ISNULL(PTT.AMT_APP,0) + ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0) + ISNULL((SELECT SUM(T.TOTAL_AMT) 

				FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0)

			ELSE

				ISNULL(PLTF.TOTAL_AMT, 0)

			END

			AS TO_AMT_APP_FINAL,

			CASE WHEN ISNULL(PLTF.TO_TRADE_ID,'') <> '' THEN FORMAT(ISNULL(PLDT.TOTAL_AMT, 0),'#,0') ELSE FORMAT(ISNULL(PLTF.TOTAL_AMT, 0),'#,0') END AS TOTAL_AMT_SIGN, -- Số tiền trình sử dụng

			CASE WHEN ISNULL(PLTF.TO_TRADE_ID,'') <> '' THEN

				(ISNULL(PTT.AMT_APP,0)+ ISNULL(PTT.AMT_RECEIVE_TF,0)-ISNULL(PTT.AMT_TF,0)-ISNULL(PTT.AMT_ETM,0)) + ISNULL((SELECT SUM(T.TOTAL_AMT)

				FROM dbo.PL_REQUEST_TRANSFER T WHERE T.TO_TRADE_ID=PLTF.TO_TRADE_ID AND T.REQ_DOC_ID=PLTF.REQ_DOC_ID), 0) - ISNULL(PLDT.TOTAL_AMT, 0) 

			ELSE 0 END AS TOTAL_AMT_SIGN_FN,

			PR.APPROVE_DT,

			US.TLFullName AS TC_NAME,

			CASE

				WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName

				ELSE N''

			END AS CHECKER_NAME	

			FROM dbo.PL_REQUEST_TRANSFER PLTF

			LEFT JOIN @listTRADE PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID

			LEFT JOIN @listTRADE PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID

			LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID

			LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID

			LEFT JOIN dbo.PL_IMPORT_DT LF_DT ON LF_DT.TRADE_ID=PLTF.FR_TRADE_ID

			LEFT JOIN dbo.PL_IMPORT_DT LT_DT ON LT_DT.TRADE_ID=PLTF.TO_TRADE_ID

			LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'

			LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID

			LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID

			LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID

			LEFT JOIN

			(

			SELECT GOODS_ID,REQ_ID, SUM(TOTAL_AMT) AS TOTAL_AMT FROM

			dbo.PL_REQUEST_DOC_DT 

			WHERE REQ_ID=@P_REQ_ID 

			GROUP BY GOODS_ID,REQ_ID ) PLDT 

			ON PLDT.REQ_ID = PL.REQ_ID AND PLDT.GOODS_ID = PLTF.TO_GOOD_ID

			WHERE PLTF.REQ_DOC_ID=@P_REQ_ID

			--DECLARE @TABLE TABLE(TLFullName NVARCHAR(100))

			--INSERT INTO @TABLE 

			--SELECT '- ' + TLFullName + ';' AS NAME FROM PL_PROCESS F1

			--LEFT JOIN TL_USER F2 ON F1.CHECKER_ID = F2.TLNANME

			--WHERE (1=1)

			--AND F1.REQ_ID =@P_REQ_ID

			--AND RoleName in ('TPGD','GDDV', 'GDK', 'PTGD', 'TGD', 'HDQT','KTT','KSV')

			--UPDATE @TABLE SET TLFullName = REPLACE(TLFullName,';','.')

			--WHERE TLFullName =(SELECT TOP 1 TLFullName AS NAME FROM @TABLE ORDER BY TLFullName DESC)

			--SELECT DISTINCT(TLFullName) AS NAME FROM @TABLE ORDER BY TLFullName

			-- TABLE3

			DECLARE @TABLE TABLE(ID INT, POS_NAME NVARCHAR(100))

			INSERT INTO @TABLE 

			SELECT F1.ID , '- ' + F3.POS_NAME + ';' AS NAME FROM PL_REQUEST_PROCESS F1

			LEFT JOIN TL_USER F2 ON F1.CHECKER_ID = F2.TLNANME

			LEFT JOIN CM_EMPLOYEE_LOG F3 ON F3.USER_DOMAIN = F2.TLNANME

			WHERE (1=1)

			AND F1.REQ_ID = @P_REQ_ID

			AND RoleName in ('HDQT', 'TGD', 'PTGD', 'GDK')

			AND ((F1.STATUS='C' AND F1.PROCESS_ID <>'APPROVE') OR (F1.PROCESS_ID = (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS  T WHERE T.REQ_ID=@P_REQ_ID AND T.PROCESS_ID='APPROVE' AND T.STATUS='C')))

			UPDATE @TABLE SET POS_NAME = REPLACE(POS_NAME,';','.')

			WHERE POS_NAME =(SELECT TOP 1 POS_NAME AS NAME FROM @TABLE ORDER BY POS_NAME DESC)

			SELECT POS_NAME AS NAME FROM @TABLE ORDER BY ID DESC

			SELECT NULL

			--SELECT  ROW_NUMBER() OVER (ORDER BY CGF.GD_NAME) AS STT ,CGF.GD_NAME AS GD_NAME_ADD,

			--ISNULL(PTF.AMT_APP,0) AS FR_AMT_APP_ADD,

			--ISNULL(PTF.AMT_EXE,0) AS FR_AMT_EXE_ADD,

			--ISNULL(PTF.AMT_APP,0)-ISNULL(PTF.AMT_EXE,0) AS FR_AMT_REMAIN_ADD,

			--ISNULL(PTT.AMT_APP,0) AS TO_AMT_APP_ADD,

			--ISNULL(PTT.AMT_EXE,0) AS TO_AMT_EXE_ADD,

			--ISNULL(PTT.AMT_APP,0)-ISNULL(PTT.AMT_EXE,0) AS TO_AMT_REMAIN_ADD,

			--ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_AMT_ADD,

			--PR.APPROVE_DT,

			--US.TLFullName AS TC_NAME,

			--CASE

			--	WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName

			--	ELSE N''

			--END AS CHECKER_NAME	

			--FROM dbo.PL_REQUEST_TRANSFER PLTF 

			--LEFT JOIN dbo.PL_TRADEDETAIL PTF ON PTF.TRADE_ID=PLTF.FR_TRADE_ID

			--LEFT JOIN dbo.PL_TRADEDETAIL PTT ON PTT.TRADE_ID=PLTF.TO_TRADE_ID

			--LEFT JOIN dbo.CM_GOODS CGF ON CGF.GD_ID=PLTF.FR_GOOD_ID

			--LEFT JOIN dbo.CM_GOODS CGT ON CGT.GD_ID=PLTF.TO_GOOD_ID

			--LEFT JOIN dbo.PL_REQUEST_PROCESS PR ON PR.REQ_ID=PLTF.REQ_DOC_ID AND PR.PROCESS_ID='TC'

			--LEFT JOIN dbo.TL_USER US ON US .TLNANME=PR.CHECKER_ID

			--LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLTF.CHECKER_ID

			--LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLTF.REQ_DOC_ID

			--WHERE PLTF.REQ_DOC_ID=@P_REQ_ID

			-- LAY THONG TIN CAP PHE DUYET

			IF(EXISTS(SELECT * FROM  PL_REQUEST_DOC_DT WHERE REQ_ID =@P_REQ_ID ))

			BEGIN

				DECLARE @TABLE_RETURN_05 TABLE (APPR NVARCHAR(250),APPR1 NVARCHAR(250),APPR2 NVARCHAR(250),APPR3 NVARCHAR(250))

				DECLARE @APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15)

				DECLARE @POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50)

				DECLARE @DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50)

				IF((SELECT COUNT(*) FROM PL_REQUEST_PROCESS WHERE ROLE_USER ='GDK' AND CHECKER_ID IS NOT NULL AND CHECKER_ID <>'' AND REQ_ID =@P_REQ_ID) =1)

				BEGIN

					IF((SELECT COUNT(*) FROM PL_REQUEST_PROCESS WHERE ROLE_USER ='PTGD' AND CHECKER_ID IS NOT NULL AND CHECKER_ID <>'' AND REQ_ID =@P_REQ_ID) =1)

					BEGIN

						SET @APPR =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME 

						WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK' )

						SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)

						SET @DATE =(SELECT TOP  1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

						FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME 

						WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK')

						SET @APPR1 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.ROLE_USER ='PTGD'

						WHERE A.REQ_ID= @P_REQ_ID)

						SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)

						SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.ROLE_USER ='PTGD'

						WHERE A.REQ_ID= @P_REQ_ID)

						SET @APPR2 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD' AND A.ROLE_USER ='TGD'

						WHERE A.REQ_ID= @P_REQ_ID AND 1> 2)

						SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)

						SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD'

						WHERE A.REQ_ID= @P_REQ_ID AND 1 > 2)

						SET @APPR3 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='HDQT' AND A.ROLE_USER ='HDQT'

						WHERE A.REQ_ID= @P_REQ_ID AND 1> 2)

						SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)

						SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='HDQT'

						WHERE A.REQ_ID= @P_REQ_ID AND 1> 2)	

					END

					ELSE

					BEGIN 

						SET @APPR =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME

						WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK' )

						SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)

						SET @DATE =(SELECT TOP  1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

						FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME 

						WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK')

						SET @APPR1 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND  A.ROLE_USER ='PTGD'

						WHERE A.REQ_ID= @P_REQ_ID)

						SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)

						SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.ROLE_USER='PTGD'

						WHERE A.REQ_ID= @P_REQ_ID)

						SET @APPR2 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.ROLE_USER ='PTGD' AND A.CHECKER_ID <>@APPR1

						WHERE A.REQ_ID= @P_REQ_ID)

						SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)

						SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.ROLE_USER ='PTGD' AND A.CHECKER_ID <>@APPR1

						WHERE A.REQ_ID= @P_REQ_ID)

						SET @APPR3 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD' AND A.ROLE_USER ='TGD'

						WHERE A.REQ_ID= @P_REQ_ID AND 1> 2)

						SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)

						SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

						INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD'

						WHERE A.REQ_ID= @P_REQ_ID AND 1> 2)

					END

						BEGIN

						INSERT INTO @TABLE_RETURN_05 VALUES (

															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR),

															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),

															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME =  @APPR2),

															(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3)

															)

						INSERT INTO @TABLE_RETURN_05 VALUES (@POS,@POS1,@POS2,@POS3)

						INSERT INTO @TABLE_RETURN_05 VALUES ((CASE WHEN @DATE IS NOT NULL AND @DATE <>'' THEN N'Đã chấp thuận lúc: '+@DATE ELSE '' END),

						(CASE WHEN @DATE1 IS NOT NULL AND @DATE1 <>'' THEN N'Đã chấp thuận lúc: '+@DATE1 ELSE '' END),

						(CASE WHEN @DATE2 IS NOT NULL AND @DATE2 <>'' THEN N'Đã chấp thuận lúc: '+@DATE2 ELSE '' END),

						(CASE WHEN @DATE3 IS NOT NULL AND @DATE3 <>'' THEN N'Đã chấp thuận lúc: '+@DATE3 ELSE '' END))

						END

				END

				ELSE

				BEGIN

					SET @APPR =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME  

					WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK' )

					SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)

					SET @DATE =(SELECT TOP  1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

					FROM PL_REQUEST_PROCESS  A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME 

					WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK')

					-- VAN CON LA GDK

					SET @APPR1 =(SELECT  TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME  AND A.CHECKER_ID <> @APPR

					WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK')

					SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)

					SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME  AND A.CHECKER_ID <> @APPR

					WHERE A.REQ_ID= @P_REQ_ID AND A.ROLE_USER ='GDK')

					-- PHO TONG

					--SET @APPR1 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

					--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD' AND A.ROLE_USER ='PTGD'

					--WHERE A.REQ_ID= @P_REQ_ID)

					--SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)

					--SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

					--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='PTGD'

					--WHERE A.REQ_ID= @P_REQ_ID)

					SET @APPR2 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.ROLE_USER ='PTGD'

					WHERE A.REQ_ID= @P_REQ_ID)

					SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)

					SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.ROLE_USER ='PTGD'

					WHERE A.REQ_ID= @P_REQ_ID)

					--SET @APPR3 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

					--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD' AND A.ROLE_USER ='TGD'

					--WHERE A.REQ_ID= @P_REQ_ID AND 1> 2)

					--SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)

					--SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

					--INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='TGD'

					--WHERE A.REQ_ID= @P_REQ_ID AND 1 > 2)	

					--- LUCTV: 20211012 BO SUN CHU KY GDK TAI CHINH

					SET @APPR3 =(SELECT TOP 1 A. CHECKER_ID FROM PL_REQUEST_PROCESS A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.CHECKER_ID ='nhalc' AND A.CHECKER_ID NOT IN (@APPR, @APPR1)

					WHERE A.REQ_ID= @P_REQ_ID)

					SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)

					SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_REQUEST_PROCESS  A 

					INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.CHECKER_ID ='nhalc' AND A.CHECKER_ID NOT IN (@APPR, @APPR1)

					WHERE A.REQ_ID= @P_REQ_ID)

					BEGIN

					INSERT INTO @TABLE_RETURN_05 VALUES (

														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR),

														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),

														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME =  @APPR2),

														(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3)

														)

					INSERT INTO @TABLE_RETURN_05 VALUES (@POS,@POS1,@POS2,@POS3)

					INSERT INTO @TABLE_RETURN_05 VALUES ((CASE WHEN @DATE IS NOT NULL AND @DATE <>'' THEN N'Đã chấp thuận lúc: '+@DATE ELSE '' END),

						(CASE WHEN @DATE1 IS NOT NULL AND @DATE1 <>'' THEN N'Đã chấp thuận lúc: '+@DATE1 ELSE '' END),

						(CASE WHEN @DATE2 IS NOT NULL AND @DATE2 <>'' THEN N'Đã chấp thuận lúc: '+@DATE2 ELSE '' END),

						(CASE WHEN @DATE3 IS NOT NULL AND @DATE3 <>'' THEN N'Đã chấp thuận lúc: '+@DATE3 ELSE '' END))

					END

				END

			END

SELECT * FROM  @TABLE_RETURN_05