DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER PROCEDURE dbo.TR_REQ_PROCESS_CHILD_App

@p_REQ_ID VARCHAR(20),

@p_PROCESS_ID VARCHAR(20),

@p_TLNAME VARCHAR(20),

@p_MAKER_ID VARCHAR(20),

@p_TYPE_JOB VARCHAR(20),

@p_PROCESS_DES NVARCHAR(MAX),

@p_REF_ID INT,

@p_XMLDATA XML

AS

BEGIN TRANSACTION

-- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC

--IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C' AND PROCESS_ID=@p_PROCESS_ID))

--BEGIN

--	SELECT 1 as Result, '' ErrorDesc

--	RETURN 0

--END

-- BO SUNG NHUNG PHONG BAN DO USER NAY KIEM NHIẸM

DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))

INSERT INTO @DEP_AUTH SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@p_MAKER_ID 

AND 1=1

AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL)

AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL)

IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC  WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DMMS'))

BEGIN

	--IF(EXISTS(

	--SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

	--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

	--WHERE PR.PROCESS_ID='DMMS' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' AND PRC.STATUS_JOB='C'))

	--BEGIN

	--	ROLLBACK TRANSACTION  

	--	SELECT -1 Result, N'Tại đầu mối mua sắm. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' chưa được xử lý. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt!' ErrorDesc  

	--	RETURN -1

	--END

	--IF(NOT EXISTS(

	--SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

	--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

	--WHERE PR.PROCESS_ID='DMMS' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' ))

	--BEGIN

	--	ROLLBACK TRANSACTION  

	--	SELECT -1 Result, N'Tại đầu mối mua sắm. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' chưa điều phối xử lý. Vui lòng điều phối nhân viên xử lý phiếu!' ErrorDesc  

	--	RETURN -1 

	--END

	IF(EXISTS(

	SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

	LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

	WHERE PR.PROCESS_ID='DMMS' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' AND PRC.STATUS_JOB='C' AND PRC.PROCESS_ID =@p_REF_ID))

	BEGIN

		ROLLBACK TRANSACTION  

		SELECT -1 Result, N'Tại đơn vị mua sắm. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' chưa được xử lý. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt!' ErrorDesc  

		RETURN -1

	END

	IF(NOT EXISTS(

	SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

	LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

	WHERE PR.PROCESS_ID='DMMS' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' AND PRC.PROCESS_ID =@p_REF_ID ))

	BEGIN

		ROLLBACK TRANSACTION  

		SELECT -1 Result, N'Tại đơn vị mua sắm. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' chưa điều phối xử lý. Vui lòng điều phối nhân viên xử lý phiếu!' ErrorDesc  

		RETURN -1 

	END

END

--- 16 04 2021 LUCTV BO SUNG VALIDATE

--IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC  WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DMMS'))

--BEGIN

--	IF(EXISTS(

--	SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

--	LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

--	WHERE PR.PROCESS_ID='DMMS' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' AND PRC.STATUS_JOB='C' AND PRC.ID =@p_REF_ID))

--	BEGIN

--		ROLLBACK TRANSACTION  

--		SELECT -1 Result, N'Tại đầu mối mua sắm. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' chưa được xử lý. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt!' ErrorDesc  

--		RETURN -1

--	END

--	IF(NOT EXISTS(

--	SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

--	LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

--	WHERE PR.PROCESS_ID='DMMS' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' AND PRC.ID =@p_REF_ID ))

--	BEGIN

--		ROLLBACK TRANSACTION  

--		SELECT -1 Result, N'Tại đầu mối mua sắm. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' chưa điều phối xử lý. Vui lòng điều phối nhân viên xử lý phiếu!' ErrorDesc  

--		RETURN -1 

--	END

--END

--- NEU BUOC XU LY LA DVCM

--- 16 04 2021 LUCTV BO SUNG VALIDATE

IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC  WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))

BEGIN

	SET @p_PROCESS_ID =(SELECT PROCESS_ID FROM dbo.TR_REQUEST_DOC  WHERE REQ_ID=@p_REQ_ID)

	IF(EXISTS(

	SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

	LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

	WHERE PR.PROCESS_ID='DVCM' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' AND PRC.STATUS_JOB='C' AND PRC.PROCESS_ID =@p_REF_ID))

	BEGIN

		ROLLBACK TRANSACTION  

		SELECT -1 Result, N'Tại đơn vị chuyên môn. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' trước đó bạn đã giao cho nhân viên & chưa được xử lý. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt!' ErrorDesc  

		RETURN -1

	END

	--IF(NOT EXISTS(

	--SELECT PRC.ID FROM dbo.PL_REQUEST_PROCESS PR 

	--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD PRC ON PRC.PROCESS_ID=PR.ID

	--WHERE PR.PROCESS_ID='DVCM' AND PR.REQ_ID=@p_REQ_ID AND PRC.TYPE_JOB='XL' AND PRC.PROCESS_ID =@p_REF_ID ))

	--BEGIN

	--	ROLLBACK TRANSACTION  

	--	SELECT -1 Result, N'Tại đơn vị chuyên môn. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' chưa điều phối xử lý. Vui lòng điều phối nhân viên xử lý phiếu!' ErrorDesc  

	--	RETURN -1 

	--END

END

---

---------------BAODNQ 22/12/2022: Nếu PYCMS đã qua bước trưởng đơn vị ĐMMS duyệt thì k cho ấn duyệt lại--------------------------

IF(EXISTS(SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'DMMS' AND STATUS = 'P' AND (CHECKER_ID IS NOT NULL AND CHECKER_ID <> '' AND CHECKER_ID = @p_MAKER_ID)))

BEGIN

	ROLLBACK TRANSACTION  

	SELECT -1 Result, N'Tại đầu mối mua sắm. Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +

	N' đã được trưởng đơn vị đầu mối mua sắm phê duyệt. Vui lòng chờ đợi bước xử lý tiếp theo xử lý phiếu' ErrorDesc  

	RETURN -1 

END

DECLARE @IS_KT VARCHAR(15)

SET @IS_KT =(SELECT TOP 1 IS_KT FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)

--- END LUCTV 08122020

IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R'))

OR(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID AND STATUS ='R')))

	BEGIN

		ROLLBACK TRANSACTION

		SELECT -1 Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang bị từ chối. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt lại!' ErrorDesc

		RETURN -1

END

Declare @hdoc INT

	EXEC sp_xml_preparedocument @hdoc Output,@p_XMLDATA

	DECLARE @lstFILE TABLE(

	ATTACH_ID  VARCHAR(20),

	IS_READ	BIT 

	)

	INSERT INTO @lstFILE

	SELECT *

	FROM OPENXML(@hDoc,'/Root/ATTACH_FILE',2)

	WITH 

	(

		ATTACH_ID  VARCHAR(20),

		IS_READ	BIT  

	)

IF(EXISTS(SELECT TR_REQUEST_DOC_FILE_ID FROM dbo.TR_REQUEST_DOC_FILE WHERE REQ_ID=@p_REQ_ID AND IS_VIEW=1

 AND EXISTS(SELECT ATTACH_ID FROM @lstFILE WHERE [@lstFILE].ATTACH_ID=TR_REQUEST_DOC_FILE.ATTACH_ID AND IS_READ=0)))

 BEGIN

		ROLLBACK TRANSACTION

			SELECT -1 Result, N'File đinh kèm bắt buộc đọc' ErrorDesc 

			RETURN 0

 END

DECLARE @LEVEL INT,	@TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)

		UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='P'

		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB

		SET @LEVEL=ISNULL((SELECT TOP 1 LEVEL_JOB 

		FROM dbo.PL_REQUEST_PROCESS_CHILD 

		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND TLNAME=@p_TLNAME AND TYPE_JOB=@p_TYPE_JOB

		ORDER BY LEVEL_JOB DESC),0)

		UPDATE dbo.PL_REQUEST_PROCESS_CHILD SET STATUS_JOB='C'

		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND LEVEL_JOB=(@LEVEL-1)

			SET @TYPE_JOB_NAME =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDNAME = 'JOB_TYPE' AND CDTYPE='REQ' AND CDVAL=@p_TYPE_JOB)

			SET @FULLNAME =(SELECT TLFullName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME )

		INSERT INTO dbo.PL_PROCESS

				(

					REQ_ID,

					PROCESS_ID,

					CHECKER_ID,

					APPROVE_DT,

					PROCESS_DESC,NOTES

				)

				VALUES

				(   @p_REQ_ID,        -- REQ_ID - varchar(15)

					@p_PROCESS_ID,        -- PROCESS_ID - varchar(10)

					@p_MAKER_ID,        -- CHECKER_ID - varchar(15)

					GETDATE() , -- APPROVE_DT - datetime

					@p_PROCESS_DES ,

					ISNULL(@TYPE_JOB_NAME,N'Đơn vị chuyên môn')+ N' đã phê duyệt'       -- PROCESS_DESC - nvarchar(1000)

				)

		IF(NOT EXISTS (SELECT * FROM  dbo.PL_REQUEST_PROCESS_CHILD 

		WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_REF_ID AND STATUS_JOB <> 'P'))

		BEGIN

				IF(@p_PROCESS_ID='DMMS')

				BEGIN

				DECLARE @PROCESS_PARENT VARCHAR(20)

				SET @PROCESS_PARENT='DMMS'

				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE(),NOTES=N'Đầu mối mua sắm đã phê duyệt' WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@p_PROCESS_ID

				DECLARE @LIMIT_VALUE_KT DECIMAL(18,0),@ROLE_KT VARCHAR(20),@DVDM_KT VARCHAR(20),@NOTES_KT NVARCHAR(200),@TOTAL_AMT_REQ DECIMAL(18,0)

				SET @LIMIT_VALUE_KT = (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')

				SET @ROLE_KT=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')

				SET @DVDM_KT=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='KT')

				SET @TOTAL_AMT_REQ =(SELECT SUM(((PRICE * QUANTITY) + ISNULL(TAXES,0))* ISNULL(EXCHANGE_RATE,1)) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT 

				--WHERE REQ_DOC_ID=@p_REQ_ID AND TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' OR NOTES ='CDT'))

				WHERE REQ_DOC_ID=@p_REQ_ID)

				--IF(@TOTAL_AMT_REQ > @LIMIT_VALUE_KT AND @IS_KT ='1') -- CODE CŨ CHẶN K QUA KẾ TOÁN

				----------BAODNQ 6/5/2022: THÊM ĐOẠN CODE ĐI QUA PHÒNG KẾ TOÁN-----------

				-------------Nếu PYCMS > 100tr và có BB xét giá 100-500tr hoặc trên 500tr

				IF(@TOTAL_AMT_REQ >= 100000000 

					AND (

						EXISTS(SELECT* FROM TR_REQ_DOC_XETGIA_100M_500M WHERE REQ_DOC_ID = @p_REQ_ID)

						OR EXISTS (SELECT * FROM TR_REQ_DOC_XETGIA_TREN_500M WHERE REQ_DOC_ID = @p_REQ_ID)

						)

					)

					--------------Nếu đã tồn tại DVCM kế toán thì k thêm vào-----------------

					AND NOT EXISTS(SELECT REQ_COST_ID FROM TR_REQUEST_COSTCENTER WHERE REQ_ID = @p_REQ_ID

									AND COST_ID = @DVDM_KT)

				BEGIN

					SET @IS_KT = '1'

				--------------END BAODNQ--------------------------

					DECLARE @l_REQ_COST_ID VARCHAR(15)

					EXEC SYS_CodeMasters_Gen 'TR_REQUEST_COSTCENTER', @l_REQ_COST_ID out

					INSERT dbo.TR_REQUEST_COSTCENTER

					(

					    REQ_COST_ID,

					    COST_ID,

					    REQ_ID,

					    NOTES,

					    AUTH_STATUS,

					    MAKER_ID,

					    CREATE_DT,

					    CHECKER_ID,

					    APPROVE_DT

					)

					VALUES

					(   @l_REQ_COST_ID,        -- REQ_COST_ID - varchar(15)

						@DVDM_KT,        -- COST_ID - varchar(15)			    

						@p_REQ_ID,        -- REQ_ID - varchar(15)

					    N'',       -- NOTES - nvarchar(500)

					    'U',        -- AUTH_STATUS - varchar(1)

					    @p_MAKER_ID,        -- MAKER_ID - varchar(15)

					    GETDATE(), -- CREATE_DT - datetime

					    '',        -- CHECKER_ID - varchar(15)

					   NULL -- APPROVE_DT - datetime

					    )

				END

				--------------------BAODNQ 18/7/2022: Nếu có DVCM trùng DMMS thì xác nhận luôn--------

				DECLARE @p_DVDM_DMMS_ID VARCHAR(15) = (

					SELECT CDV.DVDM_ID

					FROM CM_DMMS CD

					LEFT JOIN PL_COSTCENTER_DT PCD ON CD.BRANCH_ID = PCD.BRANCH_ID AND CD.DEP_ID = PCD.DEP_ID

					LEFT JOIN PL_COSTCENTER PC ON PCD.COST_ID = PC.COST_ID

					LEFT JOIN CM_DVDM CDV ON PC.DVDM_ID = CDV.DVDM_ID

					WHERE CD.DMMS_ID = (SELECT DVDM_ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID = 'DMMS' AND REQ_ID = @p_REQ_ID)

					AND CDV.IS_DVCM = 1

				)

				IF(EXISTS(SELECT*FROM TR_REQUEST_COSTCENTER WHERE REQ_ID = @p_REQ_ID AND COST_ID = @p_DVDM_DMMS_ID))

				BEGIN

					UPDATE TR_REQUEST_COSTCENTER SET

						AUTH_STATUS='A',

						APPROVE_DT=GETDATE(),

						CHECKER_ID=@p_MAKER_ID,

						NOTES = N'Xác nhận'

					WHERE REQ_ID = @p_REQ_ID AND COST_ID = @p_DVDM_DMMS_ID

				END

				IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID) AND EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' )))

					BEGIN

					DECLARE @COST_ID VARCHAR(20)

					DECLARE lstCostCenter CURSOR FOR

					SELECT COST_ID  FROM dbo.TR_REQUEST_COSTCENTER

					WHERE REQ_ID=@p_REQ_ID

					 OPEN lstCostCenter

		 			FETCH NEXT FROM lstCostCenter INTO @COST_ID

					WHILE @@FETCH_STATUS = 0 

					BEGIN 

					IF(NOT EXISTS(SELECT * FROM TR_REQUEST_COSTCENTER WHERE REQ_ID = @p_REQ_ID AND COST_ID = @COST_ID AND AUTH_STATUS = 'A'))

					BEGIN

					IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND 

					(ROLE_USER='GDDV' OR ROLE_USER IN (SELECT ROLE_OLD FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_NEW='GDDV') )AND ( DVDM_ID=@COST_ID OR @COST_ID IN ((SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC

					LEFT JOIN dbo.PL_COSTCENTER_DT PCD ON PCD.COST_ID = PC.COST_ID

					WHERE PCD.BRANCH_ID=PL_REQUEST_PROCESS.BRANCH_ID AND PCD.DEP_ID=PL_REQUEST_PROCESS.DEP_ID)) ) ))

					BEGIN

					--IF(@COST_ID=@DVDM_KT AND @IS_KT ='1')

          IF(@COST_ID=@DVDM_KT)

					BEGIN

						INSERT INTO dbo.PL_REQUEST_PROCESS

						(

							REQ_ID,

							PROCESS_ID,

							STATUS,

							ROLE_USER,

							BRANCH_ID,

							CHECKER_ID,

							APPROVE_DT,

							PARENT_PROCESS_ID,

							IS_LEAF,

							COST_ID,

							DVDM_ID,

							NOTES,

							IS_HAS_CHILD

						)

						VALUES

						(   @p_REQ_ID,        -- REQ_ID - varchar(15)

							'DVCM',        -- PROCESS_ID - varchar(10)

							'U',        -- STATUS - varchar(5)

							'KSV',        -- ROLE_USER - varchar(50)

							'',        -- BRANCH_ID - varchar(15)

							'',        -- CHECKER_ID - varchar(15)

							NULL, -- APPROVE_DT - datetime

						   @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

							'N',        -- IS_LEAF - varchar(1)

							'',        -- COST_ID - varchar(15)

							@COST_ID ,

							N'Chờ đơn vị chuyên môn xác nhận' ,

							1       -- DVDM_ID - varchar(15)

						 )

					END

					ELSE

					BEGIN

				    INSERT INTO dbo.PL_REQUEST_PROCESS

				    (

				        REQ_ID,

				        PROCESS_ID,

				        STATUS,

				        ROLE_USER,

				        BRANCH_ID,

				        CHECKER_ID,

				        APPROVE_DT,

				        PARENT_PROCESS_ID,

				        IS_LEAF,

				        COST_ID,

				        DVDM_ID,

						NOTES,

						IS_HAS_CHILD

				    )

				    VALUES

				    (   @p_REQ_ID,        -- REQ_ID - varchar(15)

				        'DVCM',        -- PROCESS_ID - varchar(10)

				        'U',        -- STATUS - varchar(5)

				        'GDDV',        -- ROLE_USER - varchar(50)

				        '',        -- BRANCH_ID - varchar(15)

				        '',        -- CHECKER_ID - varchar(15)

						NULL, -- APPROVE_DT - datetime

				       @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

				        'N',        -- IS_LEAF - varchar(1)

				        '',        -- COST_ID - varchar(15)

				        @COST_ID ,

						N'Chờ đơn vị chuyên môn xác nhận' ,

						1       -- DVDM_ID - varchar(15)

				     )

					 END

					END

					END

					FETCH NEXT FROM lstCostCenter INTO @COST_ID

					END

					CLOSE lstCostCenter

					DEALLOCATE lstCostCenter

				END

				----------------BAODNQ 6/7/2022: Nếu có BB xét giá trên 500tr và có check chọn qua GDK TC----------

				IF(EXISTS(SELECT * FROM TR_REQ_DOC_XETGIA_TREN_500M WHERE REQ_DOC_ID = @p_REQ_ID AND HAS_GDK_TC = 'Y'))

				BEGIN

					IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))

					BEGIN

						SET @PROCESS_PARENT='DVCM'

					END

					DECLARE @p_ROLE_ID VARCHAR(20), @p_DVDM_ID VARCHAR(15)

					SET @p_ROLE_ID = 'GDK'

					SET @p_DVDM_ID = 'DM0000000000017' --- PHÒNG TÀI CHÍNH

					INSERT INTO dbo.PL_REQUEST_PROCESS

					(

					    REQ_ID,

					    PROCESS_ID,

					    STATUS,

					    ROLE_USER,

					    BRANCH_ID,

					    CHECKER_ID,

					    APPROVE_DT,

					    PARENT_PROCESS_ID,

					    IS_LEAF,

					    COST_ID,

					    DVDM_ID,

					    NOTES,

					    IS_HAS_CHILD

					)

					VALUES

					(  @p_REQ_ID,        -- REQ_ID - varchar(15)

					   'GDK_TC',        -- PROCESS_ID - varchar(10)

					    'U',        -- STATUS - varchar(5)

					    @p_ROLE_ID,        -- ROLE_USER - varchar(50)

					    '',        -- BRANCH_ID - varchar(15)

					    '',        -- CHECKER_ID - varchar(15)

					    NULL, -- APPROVE_DT - datetime

					    @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

					    '',        -- IS_LEAF - varchar(1)

					    '',        -- COST_ID - varchar(15)

					    @p_DVDM_ID,        -- DVDM_ID - varchar(15)

					    N'Chờ Giám đốc khối Tài Chính phê duyệt',       -- NOTES - nvarchar(500)

					    0       -- IS_HAS_CHILD - bit

					    )

					SET @PROCESS_PARENT= 'GDK_TC'

				END

				---------------END BAODNQ 6/7/2022--------------------------

				DECLARE @TOTAL_AMT DECIMAL(18,2),@ROLE_PDTH VARCHAR(20)	,@LIMIT_VALUE DECIMAL(18,2)	,@IS_NEXT BIT,@PROCESS_ID  VARCHAR(5),@ROLE_ID VARCHAR(20),@DVDM_ID VARCHAR(20),@NOTES NVARCHAR(50),@DVDM_NAME NVARCHAR(200)

				--IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))

				--	SET @PROCESS_PARENT='DVCM'

				IF(NOT EXISTS(SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'GDK_TC')

				AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = 'DVCM'))

				BEGIN

					SET @PROCESS_PARENT='DVCM'

				END

				SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV'))

				--SET @IS_NEXT=1

				IF(@IS_NEXT =1 AND EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' ))) 

				BEGIN

				SET @DVDM_ID=(SELECT DVDM_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')

				SET @ROLE_ID=(SELECT ROLE_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PDPYC')

				SET @NOTES =(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID=@ROLE_ID)

				SET @DVDM_NAME= (SELECT DVDM_NAME FROM dbo.CM_DVDM WHERE DVDM_ID=@DVDM_ID)

				SET @NOTES = @NOTES + ' ' + @DVDM_NAME

				INSERT INTO dbo.PL_REQUEST_PROCESS

					(

					    REQ_ID,

					    PROCESS_ID,

					    STATUS,

					    ROLE_USER,

					    BRANCH_ID,

					    CHECKER_ID,

					    APPROVE_DT,

					    PARENT_PROCESS_ID,

					    IS_LEAF,

					    COST_ID,

					    DVDM_ID,

					    NOTES,

					    IS_HAS_CHILD

					)

					VALUES

					(  @p_REQ_ID,        -- REQ_ID - varchar(15)

					   'GDK_PYC',        -- PROCESS_ID - varchar(10)

					    'U',        -- STATUS - varchar(5)

					    @ROLE_ID,        -- ROLE_USER - varchar(50)

					    '',        -- BRANCH_ID - varchar(15)

					    '',        -- CHECKER_ID - varchar(15)

					    NULL, -- APPROVE_DT - datetime

					    @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

					    '',        -- IS_LEAF - varchar(1)

					    '',        -- COST_ID - varchar(15)

					    @DVDM_ID,        -- DVDM_ID - varchar(15)

					    N'Chờ ' +@NOTES+N' phê duyệt',       -- NOTES - nvarchar(500)

					    0       -- IS_HAS_CHILD - bit

					    )

					SET @PROCESS_PARENT= 'GDK_PYC'

                END

			----------------BAODNQ 11/6/2022 PYCMS CÓ TỜ TRÌNH CĐT------------------------

				IF(EXISTS (SELECT * FROM PL_APPOINT_CONTRACTOR WHERE TR_REQUEST_DOC_ID = @p_REQ_ID AND TYPE_PL <> 'TTCP'))

				BEGIN

					DECLARE @p_LIMIT_PTGDK_HT DECIMAL(18,0),

							@p_LIMIT_TGD DECIMAL(18,0),

							@p_TOTAL_AMT_REQ_DOC DECIMAL(18,0) = (

								SELECT TOTAL_AMT FROM TR_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID

							)

					----------------Set hạng mức------------------

					DECLARE @t_HANGHOA_TABLE TABLE(HH_ID VARCHAR(15), HH_CODE VARCHAR(100))

					INSERT INTO @t_HANGHOA_TABLE

						SELECT TRDT.HANGHOA_ID, CH.HH_CODE 

						FROM TR_REQUEST_DOC_DT TRDT

						LEFT JOIN CM_HANGHOA CH ON TRDT.HANGHOA_ID = CH.HH_ID

						WHERE TRDT.REQ_DOC_ID = @p_REQ_ID

					IF(EXISTS(SELECT * FROM @t_HANGHOA_TABLE WHERE HH_CODE IN ('HCQT-003-000007', 'SPMKT-001-000007')))

					BEGIN

						SET @p_LIMIT_PTGDK_HT = 300000000

						SET @p_LIMIT_TGD = 500000000

					END

					ELSE IF(EXISTS(SELECT * FROM @t_HANGHOA_TABLE WHERE HH_CODE IN ('THQHCC-001-000003')))

					BEGIN

						SET @p_LIMIT_PTGDK_HT = 50000000

						SET @p_LIMIT_TGD = 300000000

					END

					ELSE

					BEGIN

						SET @p_LIMIT_PTGDK_HT = 100000000

						SET @p_LIMIT_TGD = 300000000

					END

					----- LUCTV 05.04.2023 KIEM TRA NEU MUA SẮM THEO HÌNH THỨC CHỈ ĐỊNH THẦU ĐỘC QUYỀN, THÌ KHÔNG QUA TGĐ CHO NÊN SẼ CHO HẠN MỨC TGĐ LÊN 1 CON SỐ ẢO (100 TỶ ĐỒNG)

					IF(EXISTS(SELECT * FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID AND TRAN_TYPE_ID ='TRN0000000010'))

					BEGIN

						SET @p_LIMIT_TGD = 500000000000

					END

					---- END

					------------ PCYMS CÓ TỜ TRÌNH CĐT VƯỢT HẠN MỨC PTGDK HỖ TRỢ-------

					IF(@p_TOTAL_AMT_REQ_DOC > @p_LIMIT_PTGDK_HT) --- 08032023 LUCTV BO CHO NAY VI PTGD KHOI HO TRO KHONG CON CHỨC DANH NÀY NUA

					BEGIN

						--SET @DVDM_ID = 'DM0000000000015' ---PHÒNG HỖ TRỢ

						--SET @ROLE_ID = 'PTGD' -- PHÓ TỔNG GIÁM ĐỐC KHỐI

						--INSERT INTO dbo.PL_REQUEST_PROCESS

						--(

						--	REQ_ID,

						--	PROCESS_ID,

						--	STATUS,

						--	ROLE_USER,

						--	BRANCH_ID,

						--	CHECKER_ID,

						--	APPROVE_DT,

						--	PARENT_PROCESS_ID,

						--	IS_LEAF,

						--	COST_ID,

						--	DVDM_ID,

						--	NOTES,

						--	IS_HAS_CHILD

						--)

						--VALUES

						--(  @p_REQ_ID,        -- REQ_ID - varchar(15)

						--   'PTGDK_CDT',        -- PROCESS_ID - varchar(10)

						--	'U',        -- STATUS - varchar(5)

						--	@ROLE_ID,        -- ROLE_USER - varchar(50)

						--	'',        -- BRANCH_ID - varchar(15)

						--	'',        -- CHECKER_ID - varchar(15)

						--	NULL, -- APPROVE_DT - datetime

						--	@PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

						--	'',        -- IS_LEAF - varchar(1)

						--	'',        -- COST_ID - varchar(15)

						--	@DVDM_ID,        -- DVDM_ID - varchar(15)

						--	N'Chờ Phó Tổng Giám đốc khối Hỗ Trợ phê duyệt',   -- NOTES - nvarchar(500)

						--	0       -- IS_HAS_CHILD - bit

						--)

						--SET @PROCESS_PARENT= 'PTGDK_CDT'

						IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID ='GDK_PYC' AND DVDM_ID =@DVDM_ID))

						BEGIN

							SET @DVDM_ID = 'DM0000000000015' ---PHÒNG HỖ TRỢ

							SET @ROLE_ID = 'GDK' -- PHÓ TỔNG GIÁM ĐỐC KHỐI

							INSERT INTO dbo.PL_REQUEST_PROCESS

							(

								REQ_ID,

								PROCESS_ID,

								STATUS,

								ROLE_USER,

								BRANCH_ID,

								CHECKER_ID,

								APPROVE_DT,

								PARENT_PROCESS_ID,

								IS_LEAF,

								COST_ID,

								DVDM_ID,

								NOTES,

								IS_HAS_CHILD

							)

							VALUES

							(  @p_REQ_ID,        -- REQ_ID - varchar(15)

							   'GDK_PYC',        -- PROCESS_ID - varchar(10)

								'U',        -- STATUS - varchar(5)

								@ROLE_ID,        -- ROLE_USER - varchar(50)

								'',        -- BRANCH_ID - varchar(15)

								'',        -- CHECKER_ID - varchar(15)

								NULL, -- APPROVE_DT - datetime

								@PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

								'',        -- IS_LEAF - varchar(1)

								'',        -- COST_ID - varchar(15)

								@DVDM_ID,        -- DVDM_ID - varchar(15)

								N'Chờ ' +@NOTES+N' phê duyệt',       -- NOTES - nvarchar(500)

								0       -- IS_HAS_CHILD - bit

								)

								SET @PROCESS_PARENT= 'GDK_PYC'

						END

					END

					-------------------TỜ TRÌNH CĐT CÓ CHECK GDK TÀI CHÍNH-----------

					IF(EXISTS (SELECT * FROM PL_APPOINT_CONTRACTOR WHERE TR_REQUEST_DOC_ID = @p_REQ_ID AND TYPE_PL <> 'TTCP' AND HAS_GDK_TC = 'Y'))

					BEGIN

						SET @DVDM_ID = 'DM0000000000017' --- PHÒNG TÀI CHÍNH

						SET @ROLE_ID = 'GDK'

						INSERT INTO dbo.PL_REQUEST_PROCESS

						(

							REQ_ID,

							PROCESS_ID,

							STATUS,

							ROLE_USER,

							BRANCH_ID,

							CHECKER_ID,

							APPROVE_DT,

							PARENT_PROCESS_ID,

							IS_LEAF,

							COST_ID,

							DVDM_ID,

							NOTES,

							IS_HAS_CHILD

						)

						VALUES

						(  @p_REQ_ID,        -- REQ_ID - varchar(15)

						   'GDK_CDT_TC',        -- PROCESS_ID - varchar(10)

							'U',        -- STATUS - varchar(5)

							@ROLE_ID,        -- ROLE_USER - varchar(50)

							'',        -- BRANCH_ID - varchar(15)

							'',        -- CHECKER_ID - varchar(15)

							NULL, -- APPROVE_DT - datetime

							@PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

							'',        -- IS_LEAF - varchar(1)

							'',        -- COST_ID - varchar(15)

							@DVDM_ID,        -- DVDM_ID - varchar(15)

							N'Chờ Giám đốc khối Tài Chính phê duyệt',       -- NOTES - nvarchar(500)

							0       -- IS_HAS_CHILD - bit

						)

						SET @PROCESS_PARENT= 'GDK_CDT_TC'

					END

					---- LUCTV 17-03-2023 NEU TO TRINH ĐÃ LÀ CHỈ ĐỊNH THẦU THÌ TẠI PYCMS DÙ CÓ LÀM TỜ TRÌNH CĐT BAO NHIÊU ĐI CHĂNG NỮA CŨNG CHỈ DỪNG LẠI Ở GĐK HỖ TRỢ

					DECLARE @p_PL_REQ_ID VARCHAR(15)

					SET @p_PL_REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)

					IF(NOT EXISTS (SELECT * FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID AND PL_REQDT_ID IN (SELECT REQDT_ID FROM PL_REQUEST_DOC_DT 

					WHERE REQ_ID =@p_PL_REQ_ID AND TRADE_TYPE = 'CDT')))

					BEGIN

					-------------------PCYMS CÓ TỜ TRÌNH CĐT VƯỢT HẠN MỨC TGD-------

					IF(@p_TOTAL_AMT_REQ_DOC > @p_LIMIT_TGD)

					BEGIN

						----------------Qua thư ký TGĐ trc-----------------

						--SET @DVDM_ID = 'DM0000000000013'

						SET @DVDM_ID = NULL

						SET @ROLE_ID = 'TKTGD'

						INSERT INTO dbo.PL_REQUEST_PROCESS

						(

							REQ_ID,

							PROCESS_ID,

							STATUS,

							ROLE_USER,

							BRANCH_ID,

							CHECKER_ID,

							APPROVE_DT,

							PARENT_PROCESS_ID,

							IS_LEAF,

							COST_ID,

							DVDM_ID,

							NOTES,

							IS_HAS_CHILD

						)

						VALUES

						(  @p_REQ_ID,        -- REQ_ID - varchar(15)

						   'TKTGD_CDT',        -- PROCESS_ID - varchar(10)

							'U',        -- STATUS - varchar(5)

							@ROLE_ID,        -- ROLE_USER - varchar(50)

							'',        -- BRANCH_ID - varchar(15)

							'',        -- CHECKER_ID - varchar(15)

							NULL, -- APPROVE_DT - datetime

							@PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

							'',        -- IS_LEAF - varchar(1)

							'',        -- COST_ID - varchar(15)

							@DVDM_ID,        -- DVDM_ID - varchar(15)

							N'Chờ Thư ký Tổng Giám đốc phê duyệt',   -- NOTES - nvarchar(500)

							0       -- IS_HAS_CHILD - bit

						)

						SET @PROCESS_PARENT= 'TKTGD_CDT'

						---------------Qua TGĐ------------------

						--SET @DVDM_ID = 'DM0000000000013'

						SET @DVDM_ID = NULL

						SET @ROLE_ID = 'TGD'

						INSERT INTO dbo.PL_REQUEST_PROCESS

						(

							REQ_ID,

							PROCESS_ID,

							STATUS,

							ROLE_USER,

							BRANCH_ID,

							CHECKER_ID,

							APPROVE_DT,

							PARENT_PROCESS_ID,

							IS_LEAF,

							COST_ID,

							DVDM_ID,

							NOTES,

							IS_HAS_CHILD

						)

						VALUES

						(  @p_REQ_ID,        -- REQ_ID - varchar(15)

						   'TGD_CDT',        -- PROCESS_ID - varchar(10)

							'U',        -- STATUS - varchar(5)

							@ROLE_ID,        -- ROLE_USER - varchar(50)

							'',        -- BRANCH_ID - varchar(15)

							'',        -- CHECKER_ID - varchar(15)

							NULL, -- APPROVE_DT - datetime

							@PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

							'',        -- IS_LEAF - varchar(1)

							'',        -- COST_ID - varchar(15)

							@DVDM_ID,        -- DVDM_ID - varchar(15)

							N'Chờ Tổng Giám đốc phê duyệt',   -- NOTES - nvarchar(500)

							0       -- IS_HAS_CHILD - bit

						)

						SET @PROCESS_PARENT= 'TGD_CDT'

					END

				END

				----------------------------END BAODNQ 11/6/2022------------------

				END -- LUCTV 17-03-2023 NEU TO TRINH ĐÃ LÀ CHỈ ĐỊNH THẦU THÌ TẠI PYCMS DÙ CÓ LÀM TỜ TRÌNH CĐT BAO NHIÊU ĐI CHĂNG NỮA CŨNG CHỈ DỪNG LẠI Ở GĐK HỖ TRỢ

				INSERT INTO dbo.PL_REQUEST_PROCESS

				(

				    REQ_ID,

				    PROCESS_ID,

				    STATUS,

				    ROLE_USER,

				    BRANCH_ID,

				    CHECKER_ID,

				    APPROVE_DT,

				    PARENT_PROCESS_ID,

				    IS_LEAF,

				    COST_ID,

				    DVDM_ID,

				    NOTES,

				    IS_HAS_CHILD

				)

				VALUES

				(   @p_REQ_ID,        -- REQ_ID - varchar(15)

				    'APPROVE',        -- PROCESS_ID - varchar(10)

				    'U',        -- STATUS - varchar(5)

				    '',        -- ROLE_USER - varchar(50)

				    '',        -- BRANCH_ID - varchar(15)

				    '',        -- CHECKER_ID - varchar(15)

				    NULL, -- APPROVE_DT - datetime

				    @PROCESS_PARENT,        -- PARENT_PROCESS_ID - varchar(10)

				    'N',        -- IS_LEAF - varchar(1)

				    '',        -- COST_ID - varchar(15)

				    '',        -- DVDM_ID - varchar(15)

				    N'Hoàn tất',       -- NOTES - nvarchar(500)

				    NULL       -- IS_HAS_CHILD - bit

				    )

				DECLARE @PROCESS_NEXT_ID VARCHAR(10)

				SET @PROCESS_NEXT_ID = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)

				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID

				UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT_ID WHERE REQ_ID=@p_REQ_ID

				END -- KET THUC DMMS

				ELSE IF(@p_PROCESS_ID='DVCM')

				BEGIN

						DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID VARCHAR(15),@PROCESS_NEXT VARCHAR(10)

							SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)

							SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)

							SET @ROLE_ID=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID)

							-- LUCTV 07082020 KIEM TRA NEU ROLE KHAC ROLE GDDV THI PHAI CHUYEN ROLE THANH GDDV

							--IF(@ROLE_ID ='KTT')

							--BEGIN

							--	SET @ROLE_ID ='GDDV'

							--END

							SET @PROCESS_NEXT = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@p_PROCESS_ID)

						UPDATE dbo.TR_REQUEST_COSTCENTER SET AUTH_STATUS='A',APPROVE_DT=GETDATE(),CHECKER_ID=@p_MAKER_ID, NOTES = @p_PROCESS_DES WHERE REQ_ID=@p_REQ_ID AND COST_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN

																																	dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID WHERE BRANCH_ID=@BRANCH_ID AND (DEP_ID=@DEP_ID OR DEP_ID IN (SELECT * FROM @DEP_AUTH)))

						UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',CHECKER_ID=@p_MAKER_ID,

						APPROVE_DT=GETDATE() ,NOTES=N'Đơn vị chuyên môn xác nhận' 

						WHERE REQ_ID=@p_REQ_ID AND( ROLE_USER=@ROLE_ID OR ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID))AND DVDM_ID IN (SELECT PC.DVDM_ID FROM dbo.PL_COSTCENTER PC LEFT JOIN

																																	dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID

																																	WHERE BRANCH_ID=@BRANCH_ID AND (DEP_ID=@DEP_ID OR DEP_ID IN (SELECT * FROM @DEP_AUTH)))

						IF(NOT EXISTS( SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@p_PROCESS_ID AND REQ_ID=@p_REQ_ID AND STATUS='C'))

						BEGIN

										UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PROCESS_ID=@PROCESS_NEXT AND REQ_ID=@p_REQ_ID

										UPDATE dbo.TR_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID

										----------------BAODNQ 6/7/2022-----------------

										IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = @PROCESS_NEXT AND STATUS = 'C' AND (NOTES IS NULL OR NOTES = '')))

										BEGIN

											DECLARE @p_PROCESS_NOTES NVARCHAR(MAX)

											IF(@PROCESS_NEXT = 'GDK_TC')

											BEGIN

												SET @p_PROCESS_NOTES = N'Chờ Giám đốc khối Tài Chính phê duyệt'

											END

											ELSE IF(@PROCESS_NEXT = 'GDK_PYC')

											BEGIN

												SET @NOTES =(SELECT ROLE_DESC FROM dbo.TL_SYSROLE WHERE ROLE_ID='GDK')

												SET @p_PROCESS_NOTES = N'Chờ ' +@NOTES+N' phê duyệt'

											END

											UPDATE PL_REQUEST_PROCESS SET 

												NOTES = @p_PROCESS_NOTES

											WHERE REQ_ID = @p_REQ_ID AND PROCESS_ID = @PROCESS_NEXT

										END

										----------------END BAODNQ 6/7/2022-----------------

					END

				END --- KET THUC DON VI CHUYEN MON

			--IF(@PROCESS_NEXT='APPROVE')

			IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE PROCESS_ID ='APPROVE' AND REQ_ID =@p_REQ_ID))

			BEGIN

				DECLARE @TempTB TABLE

				(

					TOTAL_AMT DECIMAL(18,2),

					TRADE_ID VARCHAR(20),

					PLAN_ID VARCHAR(20)

				)

				INSERT INTO @TempTB			

				SELECT SUM(DT.TOTAL_AMT) TOTAL_AMT,PLDT.TRADE_ID,PLDT.PLAN_ID FROM dbo.TR_REQUEST_DOC_DT DT 

				LEFT JOIN dbo.PL_REQUEST_DOC_DT PLDT ON DT.PL_REQDT_ID=PLDT.REQDT_ID

				WHERE TRAN_TYPE_ID  IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK') AND DT.REQ_DOC_ID=@p_REQ_ID

				GROUP BY	PLDT.TRADE_ID,PLDT.PLAN_ID

				UPDATE dbo.PL_TRADEDETAIL SET AMT_EXE =AMT_EXE + (SELECT TOTAL_AMT FROM @TempTB WHERE [@TempTB].TRADE_ID=PL_TRADEDETAIL.TRADE_ID AND PL_TRADEDETAIL.PLAN_ID=[@TempTB].PLAN_ID) WHERE PL_TRADEDETAIL.TRADE_ID IN (SELECT TRADE_ID FROM @TempTB)

				IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND  TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK')))

					EXEC dbo.TR_REQ_DOC_Ins_To_PO @p_TR_REQ_ID = @p_REQ_ID -- varchar(15)

				------------BAODNQ 26/3/2022: Duyệt bb xét giá khi duyệt PYCMS hoàn tất------------

				DECLARE @p_TR_REQUEST_DOC_TOTAL_AMT DECIMAL(18,0),

						@p_RECORD_ID_100 VARCHAR(15), --bb xét giá dưới 100

						@p_RECORD_ID_100_500 VARCHAR(15), -- bb xét giá 100-500

						@p_RECORD_ID_500 VARCHAR(15) --bb xét giá trên 500

				SET @p_TR_REQUEST_DOC_TOTAL_AMT = (

					SELECT TOTAL_AMT FROM TR_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID

				)

				SET @p_RECORD_ID_100 = (SELECT RECORD_ID FROM TR_REQ_DOC_XETGIA_DUOI_100M WHERE REQ_DOC_ID = @p_REQ_ID)

				SET @p_RECORD_ID_100_500 = (SELECT RECORD_ID FROM TR_REQ_DOC_XETGIA_100M_500M WHERE REQ_DOC_ID = @p_REQ_ID)

				SET @p_RECORD_ID_500 = (SELECT RECORD_ID FROM TR_REQ_DOC_XETGIA_TREN_500M WHERE REQ_DOC_ID = @p_REQ_ID)

					----------TH BB XÉT GIÁ DƯỚI 100TR------

				--IF(@p_TR_REQUEST_DOC_TOTAL_AMT < 100000000)

				--BEGIN

				--	SET @p_RECORD_ID = (

				--		SELECT RECORD_ID FROM TR_REQ_DOC_XETGIA_DUOI_100M WHERE REQ_DOC_ID = @p_REQ_ID

				--	)

				--	IF(@p_RECORD_ID IS NULL OR @p_RECORD_ID = '')

				--	BEGIN

				--		ROLLBACK TRANSACTION

				--		SELECT '-1' as Result, N'Biên bản xét giá không tồn tại' ErrorDesc

				--		RETURN '-1'

				--	END

				--		-------DUYỆT BB XÉT GIÁ---------

				--	UPDATE TR_REQ_DOC_XETGIA_DUOI_100M SET

				--		AUTH_STATUS = 'A',

				--		CHECKER_ID = @p_TLNAME,

				--		APPROVE_DT = GETDATE()

				--	WHERE RECORD_ID = @p_RECORD_ID

				--	IF @@ERROR <> 0 GOTO ABORT

				--END

				IF(@p_RECORD_ID_100 IS NOT NULL AND @p_RECORD_ID_100 <> '')

				BEGIN

						-------DUYỆT BB XÉT GIÁ---------

					UPDATE TR_REQ_DOC_XETGIA_DUOI_100M SET

						AUTH_STATUS = 'A',

						CHECKER_ID = @p_TLNAME,

						APPROVE_DT = GETDATE()

					WHERE RECORD_ID = @p_RECORD_ID_100

					IF @@ERROR <> 0 GOTO ABORT

				END

					----------TH BB XÉT GIÁ TỪ 100TR - 500TR------

				--ELSE IF (@p_TR_REQUEST_DOC_TOTAL_AMT >= 100000000 AND @p_TR_REQUEST_DOC_TOTAL_AMT < 500000000)

				--BEGIN

				--	SET @p_RECORD_ID = (

				--		SELECT RECORD_ID FROM TR_REQ_DOC_XETGIA_100M_500M WHERE REQ_DOC_ID = @p_REQ_ID

				--	)

				--	IF(@p_RECORD_ID IS NULL OR @p_RECORD_ID = '')

				--	BEGIN

				--		ROLLBACK TRANSACTION

				--		SELECT '-1' as Result, N'Biên bản xét giá không tồn tại' ErrorDesc

				--		RETURN '-1'

				--	END

				--		-------DUYỆT BB XÉT GIÁ---------

				--	UPDATE TR_REQ_DOC_XETGIA_100M_500M SET

				--		AUTH_STATUS = 'A',

				--		CHECKER_ID = @p_TLNAME,

				--		APPROVE_DT = GETDATE()

				--	WHERE RECORD_ID = @p_RECORD_ID

				--	IF @@ERROR <> 0 GOTO ABORT

				--END

				ELSE IF (@p_RECORD_ID_100_500 IS NOT NULL AND @p_RECORD_ID_100_500 <> '')

				BEGIN

					-----DUYỆT BB XÉT GIÁ---------

					UPDATE TR_REQ_DOC_XETGIA_100M_500M SET

						AUTH_STATUS = 'A',

						CHECKER_ID = @p_TLNAME,

						APPROVE_DT = GETDATE()

					WHERE RECORD_ID = @p_RECORD_ID_100_500

					IF @@ERROR <> 0 GOTO ABORT

				END

					----------TH BB XÉT GIÁ TRÊN 500TR------

				--ELSE

				--BEGIN

				--	SET @p_RECORD_ID = (

				--		SELECT RECORD_ID FROM TR_REQ_DOC_XETGIA_TREN_500M WHERE REQ_DOC_ID = @p_REQ_ID

				--	)

				--	IF(@p_RECORD_ID IS NULL OR @p_RECORD_ID = '')

				--	BEGIN

				--		ROLLBACK TRANSACTION

				--		SELECT '-1' as Result, N'Biên bản xét giá không tồn tại' ErrorDesc

				--		RETURN '-1'

				--	END

				--		-------DUYỆT BB XÉT GIÁ---------

				--	UPDATE TR_REQ_DOC_XETGIA_TREN_500M SET

				--		AUTH_STATUS = 'A',

				--		CHECKER_ID = @p_TLNAME,

				--		APPROVE_DT = GETDATE()

				--	WHERE RECORD_ID = @p_RECORD_ID

				--	IF @@ERROR <> 0 GOTO ABORT

				--END

				ELSE IF (@p_RECORD_ID_500 IS NOT NULL AND @p_RECORD_ID_500 <> '')

				BEGIN

					-------DUYỆT BB XÉT GIÁ---------

					UPDATE TR_REQ_DOC_XETGIA_TREN_500M SET

						AUTH_STATUS = 'A',

						CHECKER_ID = @p_TLNAME,

						APPROVE_DT = GETDATE()

					WHERE RECORD_ID = @p_RECORD_ID_500

					IF @@ERROR <> 0 GOTO ABORT

				END

				--------------END BAODNQ 26/3/2022----------------

			END

		END

		IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT 0 as Result, '' ErrorDesc

RETURN 0

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT -1 as Result, '' ErrorDesc

		RETURN -1

End