/*
Run this script on:

        (local)\SQLEXPRESS.gAMSPro_VietCapitalBank_v2    -  This database will be modified

to synchronize it with:

        118.69.72.241,5036.gAMSPro_VietcapitalBank_v2

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.1.6.5463 from Red Gate Software Ltd at 7/13/2020 9:28:04 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[CM_ACCOUNT_PAY_Ins]'
GO
ALTER PROC [dbo].[CM_ACCOUNT_PAY_Ins]
@p_USER_ID VARCHAR(15) = NULL,
@p_ACC_TYPE VARCHAR(150) = NULL,
@p_ACC_NO VARCHAR(150) = NULL,
@p_ACC_NAME VARCHAR(150) = NULL,
@p_TK_GL VARCHAR(100) = NULL,
@p_TK_GL_NAME VARCHAR(1000) = NULL,
@p_MAKER_ID VARCHAR(15)= NULL
AS
BEGIN TRANSACTION
	IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_TYPE =@p_ACC_TYPE AND REF_ID =@p_USER_ID) AND @p_ACC_TYPE <> 'ENTRIES' )
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản tạm ứng của nhân viên ' +@p_USER_ID + N' đã tồn tại trong hệ thống' ErrorDesc
		RETURN '-1'
	END
	IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_TYPE =@p_ACC_TYPE AND REF_ID =@p_USER_ID) AND @p_ACC_TYPE = 'ENTRIES' )
	BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản hạch toán ' + @p_ACC_NO + N' đã tồn tại trong hệ thống' ErrorDesc
		RETURN '-1'
	END
	IF(@p_TK_GL ='' OR @p_TK_GL IS NULL)
	BEGIN
	ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ACC_ID, N'Tài khoản GL không được phép để trống' ErrorDesc
		RETURN '-1'
	END
	IF(@p_TK_GL_NAME ='' OR @p_TK_GL_NAME IS NULL)
	BEGIN
	ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ACC_ID, N'Tên tài khoản GL không được phép để trống' ErrorDesc
		RETURN '-1'
	END
	IF(@p_ACC_NO ='' OR @p_ACC_NO IS NULL)
	BEGIN
	ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ACC_ID, N'Số tài khoản không được phép để trống' ErrorDesc
		RETURN '-1'
	END
	IF(@p_ACC_NAME ='' OR @p_ACC_NAME IS NULL)
	BEGIN
	ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ACC_ID, N'Tên tài khoản không được phép để trống' ErrorDesc
		RETURN '-1'
	END
	IF(@p_ACC_TYPE ='ADV_PAY')
	BEGIN
		INSERT INTO CM_ACCOUNT_PAY (REF_ID,ACC_TYPE,ACC_NUM,ACC_NAME,FUNTION,TK_GL,CREATE_DT,MAKER_ID,EDITOR_ID,EDITOR_DT,AUTH_STATUS,CHECKER_ID,APPROVE_DT,TK_GL_NAME) 
		VALUES (@p_USER_ID,@p_ACC_TYPE,@p_ACC_NO,@p_ACC_NAME,'I',@p_TK_GL,GETDATE(),@p_MAKER_ID,NULL,NULL,'U',NULL,NULL,@p_TK_GL_NAME)
	END
	ELSE
	BEGIN
		INSERT INTO CM_ACCOUNT(ACC_NO,ACC_NAME,TK_GL,TK_GL_NAME,MAKER_ID,CREATE_DT,AUTH_STATUS,EDITOR_ID,EDITOR_DT,CHECKER_ID,APPROVE_DT) VALUES (@p_ACC_NO,@p_ACC_NAME,@p_TK_GL,@p_TK_GL_NAME,@p_MAKER_ID,GETDATE(),'U',NULL,NULL,NULL,NULL)
	END
COMMIT TRANSACTION
SELECT '0' as Result, ''  ACC_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ACC_ID, '' ErrorDesc
		RETURN '-1'
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[PL_ROLE_DATA_CONFIG]'
GO
CREATE TABLE [dbo].[PL_ROLE_DATA_CONFIG]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[ROLE_TYPE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRANCH_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEP_ID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_PL_ROLE_DATA_CONFIG] on [dbo].[PL_ROLE_DATA_CONFIG]'
GO
ALTER TABLE [dbo].[PL_ROLE_DATA_CONFIG] ADD CONSTRAINT [PK_PL_ROLE_DATA_CONFIG] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_MASTER_Search_New]'
GO
/*
Select * from PL_MASTER  where PLAN_TYPE = '3'
EXEC [PL_MASTER_Search] '','','','','DV0001','DV0001','1','','Y','100', 'ALL'
EXEC  [PL_MASTER_Search]  NULL,'','','','DV0021','DV0002','1','','N','100', ''
select * from pl_master where BRANCH_ID = 'dv0001' AND RECORD_STATUS = 1
*/
ALTER PROCEDURE [dbo].[PL_MASTER_Search_New]
@P_PLAN_CODE nvarchar(15) = NULL,
@p_PLAN_NAME NVARCHAR(200) = NULL,
@P_YEAR NVARCHAR(4) = NULL,
@P_COST_ID nvarchar(15) = NULL,
@P_BRANCH_ID nvarchar(15) = NULL,
@P_BRANCHLOGIN NVARCHAR(15) = NULL,
@P_PLAN_TYPE_ID NVARCHAR(15) = NULL,
@p_DEP_ID NVARCHAR(15) = NULL,
@p_GOOD_NAME NVARCHAR(200) = NULL,
@p_USER_LOGIN VARCHAR(200) = NULL,
@P_TOP INT = 10,
@P_LEVEL varchar(10) = NULL
AS
BEGIN	
/*
	dbo.FN_GET_CHINHANH(D.BRANCH_ID,'KV') KHU_VUC,
	dbo.FN_GET_CHINHANH(D.BRANCH_ID,'CN') CHI_NHANH,
	D.BRANCH_NAME PGD,	

*/
	--IF (@P_STATUS is null) OR (LEN(@P_STATUS) < 1)
	--BEGIN SET @P_STATUS = '' END
	SET @P_TOP = NULL
	declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@P_BRANCHLOGIN)

	DECLARE @BRANCH_TYPE VARCHAR(15),@DEPLOGIN VARCHAR(20),@ROLE_USER VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0
	DECLARE @DVDM_ID TABLE( DVDM_ID VARCHAR(20))
	
	SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@P_BRANCHLOGIN)
	SET @DEPLOGIN =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)
	
	IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@P_BRANCHLOGIN AND DEP_ID=@DEPLOGIN))
		SET @IS_ALL=1
	SET @ROLE_USER = (SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)


	IF(@IS_ALL=1 OR @ROLE_USER='TGD')
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1
	END
	ELSE
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  PC.DVDM_ID FROM 
									dbo.PL_COSTCENTER PC 
									LEFT JOIN dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
									WHERE PT.DEP_ID=@DEPLOGIN AND PT.BRANCH_ID=@P_BRANCHLOGIN AND EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1)
		
	END
		IF (@P_TOP = 0 OR @P_TOP IS NULL)
		BEGIN
		SELECT L.*, D.BRANCH_CODE,LDT.QUANTITY_APP,LDT.QUANTITY_USE, LDT.QUANTITY_REMAIN,
		LDT.QUANTITY_ETM,LDT.QUANTITY_REMAIN_ETM,LDT.AMT_APPROVE,LDT.AMT_EXE,LDT.AMT_REMAIN,LDT.AMT_ETM,LDT.AMT_REMAIN_ETM	,LDT.AMT_TRANSFER,LDT.AMT_RECEIVE_TRANSFER,
		
		D.BRANCH_NAME PGD, E.DEP_CODE, E.DEP_NAME,CC.DVDM_CODE  AS COST_CODE,CC.DVDM_NAME AS COST_NAME,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,
		CASE WHEN @BRANCH_TYPE='HS' THEN D.BRANCH_NAME + '-' + E.DEP_NAME
		ELSE D.BRANCH_NAME END AS DVCP_NAME
		FROM PL_MASTER L 
		INNER JOIN (
			SELECT DT.PLAN_ID,
			SUM(ISNULL(DT.QUANTITY,0)) AS QUANTITY_APP,SUM(ISNULL(DT.QUANTITY_EXE,0)) AS  QUANTITY_USE,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_REMAIN,SUM(ISNULL(DT.QUANTITY_ETM,0)) AS  QUANTITY_ETM,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_REMAIN_ETM,

			SUM(ISNULL(DT.AMT_APP,0)) AS AMT_APPROVE,SUM(ISNULL(DT.AMT_EXE,0)) AS  AMT_EXE,
			SUM(ISNULL(DT.AMT_TF,0)) AS AMT_TRANSFER,SUM(ISNULL(DT.AMT_RECEIVE_TF,0)) AS AMT_RECEIVE_TRANSFER,
			SUM(ISNULL(DT.AMT_APP,0)+ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_EXE,0)) AS AMT_REMAIN,
			SUM(ISNULL(DT.AMT_ETM,0)) AS  AMT_ETM,
			SUM(ISNULL(DT.AMT_APP,0) +ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)) AS AMT_REMAIN_ETM 
			FROM dbo.PL_TRADEDETAIL DT 
			LEFT JOIN dbo.CM_GOODS CG ON DT.GOODS_ID=CG.GD_ID
			WHERE ( CG.GD_NAME LIKE '%' + @p_GOOD_NAME +'%' OR @p_GOOD_NAME IS NULL OR @p_GOOD_NAME='')
			GROUP BY DT.PLAN_ID
		) LDT ON LDT.PLAN_ID = L.PLAN_ID
		LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = L.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT E ON E.DEP_ID = L.DEPT_ID
		LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=L.COST_ID
		LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=L.PLAN_TYPE_ID
		WHERE 1=1
			AND (L.PLAN_CODE LIKE '%' + @P_PLAN_CODE + '%' OR @P_PLAN_CODE IS NULL OR @P_PLAN_CODE = '')
			AND (L.PLAN_NAME LIKE '%' + @p_PLAN_NAME + '%' OR @p_PLAN_NAME = '' OR @p_PLAN_NAME IS NULL)
			AND (L.[YEAR] LIKE '%' + @P_YEAR + '%' OR @P_YEAR IS NULL OR @P_YEAR = '')
			AND (L.PLAN_TYPE_ID = @P_PLAN_TYPE_ID OR @P_PLAN_TYPE_ID IS NULL OR @P_PLAN_TYPE_ID = '')
			AND (L.COST_ID = @P_COST_ID OR @P_COST_ID IS NULL OR @P_COST_ID = '')
			AND (L.DEPT_ID = @P_DEP_ID OR @P_DEP_ID IS NULL OR @P_DEP_ID = '')
			AND (L.BRANCH_ID = @P_BRANCH_ID OR @P_BRANCH_ID IS NULL OR @P_BRANCH_ID = '')
			AND ((@BRANCH_TYPE='HS' AND((L.BRANCH_ID=@P_BRANCHLOGIN AND  L.DEPT_ID=@DEPLOGIN ) OR L.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))) OR (@BRANCH_TYPE <>'HS' AND L.BRANCH_ID=@P_BRANCHLOGIN))
			
			AND L.RECORD_STATUS = '1'	
		END
		ELSE 
		BEGIN

				IF (@P_TOP <> 0 AND @P_TOP IS NOT NULL )
				BEGIN
				
			SELECT L.*, D.BRANCH_CODE,LDT.QUANTITY_APP,LDT.QUANTITY_USE, LDT.QUANTITY_REMAIN,
LDT.QUANTITY_ETM,LDT.QUANTITY_REMAIN_ETM,LDT.AMT_APPROVE,LDT.AMT_EXE,LDT.AMT_REMAIN,LDT.AMT_ETM,LDT.AMT_REMAIN_ETM	,LDT.AMT_TRANSFER,LDT.AMT_RECEIVE_TRANSFER,
		
		D.BRANCH_NAME PGD, E.DEP_CODE, E.DEP_NAME,CC.DVDM_CODE  AS COST_CODE,CC.DVDM_NAME AS COST_NAME,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,
		CASE WHEN @BRANCH_TYPE='HS' THEN D.BRANCH_NAME + '-' + E.DEP_NAME
		ELSE D.BRANCH_NAME END AS DVCP_NAME
		FROM PL_MASTER L 
		INNER JOIN (
			SELECT DT.PLAN_ID,
			SUM(ISNULL(DT.QUANTITY,0)) AS QUANTITY_APP,SUM(ISNULL(DT.QUANTITY_EXE,0)) AS  QUANTITY_USE,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_REMAIN,SUM(ISNULL(DT.QUANTITY_ETM,0)) AS  QUANTITY_ETM,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_REMAIN_ETM,

			SUM(ISNULL(DT.AMT_APP,0)) AS AMT_APPROVE,SUM(ISNULL(DT.AMT_EXE,0)) AS  AMT_EXE,
			SUM(ISNULL(DT.AMT_TF,0)) AS AMT_TRANSFER,SUM(ISNULL(DT.AMT_RECEIVE_TF,0)) AS AMT_RECEIVE_TRANSFER,
			SUM(ISNULL(DT.AMT_APP,0)+ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_EXE,0)) AS AMT_REMAIN,
			SUM(ISNULL(DT.AMT_ETM,0)) AS  AMT_ETM,
			SUM(ISNULL(DT.AMT_APP,0) +ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)) AS AMT_REMAIN_ETM 
			FROM dbo.PL_TRADEDETAIL DT 
			LEFT JOIN dbo.CM_GOODS CG ON DT.GOODS_ID=CG.GD_ID
			WHERE ( CG.GD_NAME LIKE '%' + @p_GOOD_NAME +'%' OR @p_GOOD_NAME IS NULL OR @p_GOOD_NAME='')
			GROUP BY DT.PLAN_ID
		) LDT ON LDT.PLAN_ID = L.PLAN_ID
		LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = L.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT E ON E.DEP_ID = L.DEPT_ID
		LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=L.COST_ID
		LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=L.PLAN_TYPE_ID
		WHERE 1=1
			AND (L.PLAN_CODE LIKE '%' + @P_PLAN_CODE + '%' OR @P_PLAN_CODE IS NULL OR @P_PLAN_CODE = '')
			AND (L.PLAN_NAME LIKE '%' + @p_PLAN_NAME + '%' OR @p_PLAN_NAME = '' OR @p_PLAN_NAME IS NULL)
			AND (L.[YEAR] LIKE '%' + @P_YEAR + '%' OR @P_YEAR IS NULL OR @P_YEAR = '')
			AND (L.PLAN_TYPE_ID = @P_PLAN_TYPE_ID OR @P_PLAN_TYPE_ID IS NULL OR @P_PLAN_TYPE_ID = '')
			AND (L.COST_ID = @P_COST_ID OR @P_COST_ID IS NULL OR @P_COST_ID = '')
			AND (L.DEPT_ID = @P_DEP_ID OR @P_DEP_ID IS NULL OR @P_DEP_ID = '')
				AND (L.BRANCH_ID = @P_BRANCH_ID OR @P_BRANCH_ID IS NULL OR @P_BRANCH_ID = '')
			AND ((@BRANCH_TYPE='HS' AND((L.BRANCH_ID=@P_BRANCHLOGIN AND  L.DEPT_ID=@DEPLOGIN ) OR L.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))) OR (@BRANCH_TYPE <>'HS' AND L.BRANCH_ID=@P_BRANCHLOGIN))
			AND L.RECORD_STATUS = '1'	
				
			END
			
		END
	
	
	
END


--EXEC [PL_MASTER_Search] '','','','DV0001','','A','N',100
--EXEC [PL_MASTER_Search] '','','','DV0002','3','Y',100
--EXEC [PL_MASTER_Search] '','','','DV0001','','N',0
--EXEC [PL_MASTER_Search] '','','','DV0001','','Y',0
/*
Select * from PL_MASTER where PLAN_TYPE='3'
EXEC [PL_MASTER_Search] '','','','DV0001','3','Y',100
Select * from PL_MASTER where [STATUS] IN (*)

declare @temp table(_Status varchar(1))
insert into @temp values ('N')

select * from PL_MASTER where [STATUS] IN (select _Status from @temp )
*/










GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[TR_REQ_DOC_Ins_To_PO]'
GO

ALTER PROCEDURE [dbo].[TR_REQ_DOC_Ins_To_PO] @p_TR_REQ_ID VARCHAR(15)
AS
BEGIN TRANSACTION;
	DECLARE @l_SUP_ID VARCHAR(15)
	DECLARE @p_PO_CODE VARCHAR(15),
        @p_BRANCH_ID VARCHAR(20),
		@p_SUP_ID VARCHAR(20),
		@p_TOTAL_AMT DECIMAL(18,2),
		@p_MAKER_ID VARCHAR(20),
		@p_TR_REQ_CODE VARCHAR(20),
		@p_SUP_NAME NVARCHAR(200),
		@p_SUP_ADDR NVARCHAR(200),
		@p_BRANCH_RE VARCHAR(20),
        @p_PO_NAME NVARCHAR(100);
--- LAY DANH SACH NHA CUNG CAP CUA HANG HOA TRONG PYCMS - LUCTV 25052020
	DECLARE @TABLE_NCC TABLE (SUP_ID VARCHAR(15))
	INSERT INTO @TABLE_NCC SELECT SUP_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_TR_REQ_ID GROUP BY SUP_ID
	WHILE((SELECT COUNT(*) FROM @TABLE_NCC)>0)
	BEGIN
	SET @l_SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
	--insert master		
	DECLARE @l_PO_ID VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'TR_PO_MASTER', @l_PO_ID OUT;
	IF @l_PO_ID = '' OR @l_PO_ID IS NULL
	GOTO ABORT;
	SELECT @p_BRANCH_ID=BRANCH_DVMS,@p_BRANCH_RE=BRANCH_CREATE,@p_TR_REQ_CODE=REQ_CODE,@p_PO_NAME=REQ_REASON,@p_MAKER_ID=USER_DVMS FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TR_REQ_ID

	--SELECT TOP 1 @p_SUP_ID = SUP_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID
	SET @p_SUP_ID =@l_SUP_ID
	SET @p_TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)
	--TU PHAT SINH SO PO
	DECLARE @ldate INT = (SELECT YEAR(GETDATE()) );
	EXEC [dbo].[TR_PO_MASTER_NO_Gen] @p_BRANCH_ID, @ldate, @p_PO_CODE OUT;
	IF @p_PO_CODE = ''
	   OR @p_PO_CODE IS NULL
		GOTO ABORT;

	INSERT INTO TR_PO_MASTER
	(
		[PO_TYPE],
		[PO_ID],
		[PO_CODE],
		[PO_NAME],
		[CONTRACT_ID],
		[SUP_ID],
		[SUP_NAME],
		[SUP_ADDR],
		[INPUT_DT],
		[PAYMENT_DT],
		[TOTAL_AMT],
		[REQ_DOC_ID],
		[DELIVERY_DT],
		[PAYAPP_DT],
		[NOTES],
		[RECORD_STATUS],
		[MAKER_ID],
		[CREATE_DT],
		[AUTH_STATUS],
		[CHECKER_ID],
		[APPROVE_DT],
		BRANCH_ID
	)
	VALUES
	(1, @l_PO_ID, @p_PO_CODE, @p_PO_NAME, '', @p_SUP_ID, @p_SUP_NAME, @p_SUP_ADDR, GETDATE(),
	 NULL, @p_TOTAL_AMT, @p_TR_REQ_ID, NULL,
	 NULL, '', '1', @p_MAKER_ID, GETDATE(),
	 'E', NULL, NULL, @p_BRANCH_ID);
	IF @@Error <> 0
		GOTO ABORT;
	PRINT 'INSERT MASTER SUCCESS';
	INSERT INTO dbo.TR_PO_MASTER_TEMP
	(
		PO_ID,
		TR_REQ_ID,
		TR_REQ_CODE
	)
	VALUES
	(   @l_PO_ID,      -- PO_ID - varchar(15)
		@p_TR_REQ_ID,  -- TR_REQ_ID - varchar(20)
		@p_TR_REQ_CODE -- TR_REQ_CODE - varchar(20)
	 );
	 DECLARE @HANGHOA_ID VARCHAR(20),@QUANTITY DECIMAL(18,0),@PRICE DECIMAL(18,2),@TOTAL_AMT DECIMAL(18,2),@HH_TYPE VARCHAR(20),@HH_NAME NVARCHAR(100)
	 DECLARE lstData CURSOR FOR
	 SELECT HANGHOA_ID,QUANTITY,PRICE,TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID
		
	OPEN lstData
	FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SELECT @HH_NAME= HH_NAME,@HH_TYPE=HH_TYPE_ID FROM dbo.CM_HANGHOA WHERE HH_ID=@HANGHOA_ID
	DECLARE @l_PD_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @l_PD_ID out
			IF @l_PD_ID='' OR @l_PD_ID IS NULL GOTO ABORT		

	 INSERT INTO dbo.TR_PO_DETAIL
	 (
		 PD_ID,
		 PO_ID,
		 PLAN_ID,
		 TRADE_ID,
		 GOODS_ID,
		 DESCRIPTION,
		 UNIT_ID,
		 QUANTITY,
		 PRICE,
		 TOTAL_AMT,
		 IS_DELIVERY,
		 DELIVERY_DT,
		 PAYMENT_STATUS,
		 AMOUNT_PAID,
		 PAID_DT,
		 INVOICENO,
		 NOTES,
		 RECEIVE_BRANCH,
		 RECEIVE_ADDR,
		 RECEIVE_PERSON,
		 RECEIVE_TEL,
		 RECORD_STATUS,
		 MAKER_ID,
		 CREATE_DT,
		 AUTH_STATUS,
		 CHECKER_ID,
		 APPROVE_DT,
		 EXP_DELIVERY_DT,
		 GOODS_NAME,
		 INVOICE_DT,
		 GOODSTYPE_REAL,
		 VAT,
		 PRICE_VAT,
		 CONTRACT_DT
	 )
	 VALUES
	 (   @l_PD_ID,        -- PD_ID - varchar(15)
		 @l_PO_ID,        -- PO_ID - varchar(15)
		 '',        -- PLAN_ID - varchar(15)
		 '',        -- TRADE_ID - varchar(15)
		 @HANGHOA_ID,        -- GOODS_ID - varchar(15)
		 N'',       -- DESCRIPTION - nvarchar(500)
		 '',        -- UNIT_ID - varchar(15)
		 @QUANTITY,      -- QUANTITY - decimal(18, 0)
		 ROUND(@PRICE/1.1,0),      -- PRICE - decimal(18, 0)
		 @TOTAL_AMT,      -- TOTAL_AMT - decimal(18, 0)
		 '',        -- IS_DELIVERY - varchar(1)
		 NULL, -- DELIVERY_DT - datetime
		 'CTT',        -- PAYMENT_STATUS - varchar(4)
		 NULL,      -- AMOUNT_PAID - decimal(18, 0)
		 NULL, -- PAID_DT - datetime
		 '',        -- INVOICENO - varchar(1000)
		 N'',       -- NOTES - nvarchar(1000)
		 @p_BRANCH_RE,        -- RECEIVE_BRANCH - varchar(15)
		 N'',       -- RECEIVE_ADDR - nvarchar(1000)
		 N'',       -- RECEIVE_PERSON - nvarchar(500)
		 '',        -- RECEIVE_TEL - varchar(100)
		 '',        -- RECORD_STATUS - varchar(1)
		 @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		 GETDATE(), -- CREATE_DT - datetime
		 '',        -- AUTH_STATUS - varchar(50)
		 '',        -- CHECKER_ID - varchar(15)
		 NULL, -- APPROVE_DT - datetime
		 NULL, -- EXP_DELIVERY_DT - datetime
		 @HH_NAME,       -- GOODS_NAME - nvarchar(500)
		 NULL, -- INVOICE_DT - datetime
		 @HH_TYPE,        -- GOODSTYPE_REAL - varchar(15)
		 10,      -- VAT - decimal(18, 2)
		--@TOTAL_AMT -   (ROUND(@PRICE/1.1,0) *@QUANTITY),      -- PRICE_VAT - decimal(18, 0)
		 @PRICE -ROUND(@PRICE/1.1,0),
		 ''         -- CONTRACT_DT - varchar(15)
		 )
 
	   FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
	END
	 CLOSE lstData;
    DEALLOCATE lstData;

	SET @TOTAL_AMT=(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)

	DECLARE @l_PAY_ID VARCHAR(15)
	EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
	IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
	INSERT INTO dbo.TR_PO_PAYMENT
	(
	    PAY_ID,
	    PO_ID,
	    PAY_PHASE,
	    EXP_DT,
	    [PERCENT],
	    AMOUNT,
	    NOTES,
	    RECORD_STATUS,
	    MAKER_ID,
	    CREATE_DT,
	    AUTH_STATUS,
	    CHECKER_ID,
	    APPROVE_DT
	)
	VALUES
	(   @l_PAY_ID,        -- PAY_ID - varchar(15)
	    @l_PO_ID,        -- PO_ID - varchar(15)
	    '1',        -- PAY_PHASE - varchar(20)
	    GETDATE(), -- EXP_DT - datetime
	    50,      -- PERCENT - decimal(18, 0)
	    @TOTAL_AMT / 2 ,      -- AMOUNT - decimal(18, 0)
	    N'',       -- NOTES - nvarchar(1000)
	    '1',        -- RECORD_STATUS - varchar(1)
	    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
	    GETDATE(), -- CREATE_DT - datetime
	    'U',        -- AUTH_STATUS - varchar(50)
	    '',        -- CHECKER_ID - varchar(15)
	    NULL  -- APPROVE_DT - datetime
	    )
		EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
	IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
	INSERT INTO dbo.TR_PO_PAYMENT
	(
	    PAY_ID,
	    PO_ID,
	    PAY_PHASE,
	    EXP_DT,
	    [PERCENT],
	    AMOUNT,
	    NOTES,
	    RECORD_STATUS,
	    MAKER_ID,
	    CREATE_DT,
	    AUTH_STATUS,
	    CHECKER_ID,
	    APPROVE_DT
	)
	VALUES
	(   @l_PAY_ID,        -- PAY_ID - varchar(15)
	    @l_PO_ID,        -- PO_ID - varchar(15)
	    '2',        -- PAY_PHASE - varchar(20)
	    GETDATE(), -- EXP_DT - datetime
	    50,      -- PERCENT - decimal(18, 0)
	    @TOTAL_AMT / 2 ,      -- AMOUNT - decimal(18, 0)
	    N'',       -- NOTES - nvarchar(1000)
	    '1',        -- RECORD_STATUS - varchar(1)
	    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
	    GETDATE(), -- CREATE_DT - datetime
	    'E',        -- AUTH_STATUS - varchar(50)
	    '',        -- CHECKER_ID - varchar(15)
	    NULL  -- APPROVE_DT - datetime
	    )

	IF(@TOTAL_AMT <=5000000)
	BEGIN
		UPDATE dbo.TR_PO_MASTER SET AUTH_STATUS='A' WHERE PO_ID=@l_PO_ID
	END
	--XOA DI NHA CUNG CAP
	DELETE FROM @TABLE_NCC WHERE SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
	--- KET THUC VONG LAP KIEM TRA DANH SACH NHA CUNG CAP PYCMS
	END
	COMMIT TRANSACTION;
	RETURN 1;
	ABORT:
	BEGIN
		ROLLBACK TRANSACTION;
		RETURN 1;
	END;
	ABORT1:
	BEGIN
		CLOSE lstData;
		DEALLOCATE lstData;
		ROLLBACK TRANSACTION;
		RETURN 1;
	END;







GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_REQUEST_DOC_DT_ById]'
GO
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_DT_ById]
@P_REQ_ID varchar(15),
@P_TYPE VARCHAR(20),
@P_USER_LOGIN VARCHAR(20)
AS

DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID  VARCHAR(15),@ROLE VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0

DECLARE
@COST_ID TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID TABLE (
	DVDM_ID VARCHAR(15)
)


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

SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@P_USER_LOGIN)
 IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID))
		SET @IS_ALL=1
SET @ROLE=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@P_USER_LOGIN)

	INSERT INTO @COST_ID
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID

	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID



IF(@P_TYPE='DVKD')
BEGIN

SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  B.GD_CODE, B.GD_ID, B.GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
  
ISNULL(PL.AMT_APP,0) AS AMT_APP,
ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
ISNULL(PL.AMT_ETM,0) AS AMT_ETM,
ISNULL(PL.AMT_TF,0) AS AMT_TF,
ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
GROUP BY Temp.TRADE_ID),0)
 AS AMT_REMAIN_REQ,
A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT
FROM PL_REQUEST_DOC_DT A
LEFT JOIN dbo.PL_TRADEDETAIL PL ON A.TRADE_ID=PL.TRADE_ID
LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
LEFT JOIN dbo.CM_GOODSTYPE GT ON B.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
 WHERE A.REQ_ID=@P_REQ_ID 
 END
ELSE IF(@P_TYPE='PDTT')
BEGIN

SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  B.GD_CODE, B.GD_ID, B.GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
  
ISNULL(PL.AMT_APP,0) AS AMT_APP,
ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
ISNULL(PL.AMT_ETM,0) AS AMT_ETM,
ISNULL(PL.AMT_TF,0) AS AMT_TF,
ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
GROUP BY Temp.TRADE_ID),0)
 AS AMT_REMAIN_REQ,
A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT
FROM PL_REQUEST_DOC_DT A
LEFT JOIN dbo.PL_TRADEDETAIL PL ON A.TRADE_ID=PL.TRADE_ID
LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
LEFT JOIN dbo.CM_GOODSTYPE GT ON B.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
LEFT JOIN dbo.PL_REQUEST_TRANSFER PLT ON PLT.REQ_DOC_ID=A.REQ_ID AND PLT.TO_GOOD_ID=A.GOODS_ID
 WHERE A.REQ_ID=@P_REQ_ID AND (@ROLE='TGD' OR @IS_ALL=1   OR @ROLE='HDQT' OR  EXISTS(SELECT * FROM @DVDM_ID WHERE [@DVDM_ID].DVDM_ID=A.DVDM_ID OR [@DVDM_ID].DVDM_ID=A.KHOI_ID ) OR  EXISTS(SELECT * FROM @DVDM_ID WHERE [@DVDM_ID].DVDM_ID=PLT.FR_DVDM_ID OR [@DVDM_ID].DVDM_ID=PLT.FR_KHOI_ID ))
 END
 ELSE IF(@P_TYPE='XLTT')
BEGIN

SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  B.GD_CODE, B.GD_ID, B.GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
  
ISNULL(PL.AMT_APP,0) AS AMT_APP,
ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
ISNULL(PL.AMT_ETM,0) AS AMT_ETM,
ISNULL(PL.AMT_TF,0) AS AMT_TF,
ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
GROUP BY Temp.TRADE_ID),0)
 AS AMT_REMAIN_REQ,
A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT
FROM PL_REQUEST_DOC_DT A
LEFT JOIN dbo.PL_TRADEDETAIL PL ON A.TRADE_ID=PL.TRADE_ID
LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
LEFT JOIN dbo.CM_GOODSTYPE GT ON B.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
 WHERE A.REQ_ID=@P_REQ_ID AND (@ROLE='TGD' OR @IS_ALL=1 OR @ROLE='HDQT' OR @ROLE ='KSV' OR @ROLE ='GDV' OR  EXISTS(SELECT * FROM @DVDM_ID WHERE [@DVDM_ID].DVDM_ID=A.DVDM_ID OR [@DVDM_ID].DVDM_ID=A.KHOI_ID )
 OR EXISTS
 (SELECT * FROM dbo.CM_GOOD_DVDM CGD WHERE CGD.GD_ID=A.GOODS_ID AND CGD.DVCM_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
 OR EXISTS(SELECT * FROM dbo.PL_REQUEST_TRANSFER PT WHERE PT.TO_TRADE_ID=A.TRADE_ID AND PT.TO_GOOD_ID=A.GOODS_ID AND 
 (PT.FR_BRN_ID=@BRANCH_ID AND ( PT.FR_DEP_ID=@DEP_ID OR PT.FR_DEP_ID IS NULL OR PT.FR_DEP_ID='') ))
 )

 END

ELSE IF( @P_TYPE='PYC')
BEGIN

SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  B.GD_CODE, B.GD_ID, B.GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
ISNULL(PL.AMT_APP,0) AS AMT_APP,
ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
ISNULL(PL.AMT_ETM,0) AS AMT_ETM,
ISNULL(PL.AMT_TF,0) AS AMT_TF,
ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
GROUP BY Temp.TRADE_ID),0)
 AS AMT_REMAIN_REQ,
A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT
FROM PL_REQUEST_DOC_DT A
LEFT JOIN dbo.PL_TRADEDETAIL PL ON A.TRADE_ID=PL.TRADE_ID
LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
LEFT JOIN dbo.CM_GOODSTYPE GT ON B.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
 WHERE A.REQ_ID=@P_REQ_ID 

 UNION ALL
 
SELECT A.REQDT_ID,A.NAME,A.GOODS_ID,A.QUANTITY,A.TOTAL_AMT,A.PRICE,A.TRADE_ID,A.PLAN_ID,PL.NOTES,A.DESCRIPTION,A.HANGHOA_ID,HH.HH_CODE,HH.HH_NAME,A.CURRENCY,A.EXCHANGE_RATE,A.TAXES,
  B.GD_CODE, B.GD_ID, B.GD_NAME, C.UNIT_CODE, C.UNIT_ID, A.UNIT_NAME,
ISNULL(PL.AMT_APP,0) AS AMT_APP,
ISNULL(PL.AMT_EXE,0) AS AMT_EXE,
ISNULL(PL.AMT_ETM,0) AS AMT_ETM,
ISNULL(PL.AMT_TF,0) AS AMT_TF,
ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0) - ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)- ISNULL(PL.AMT_ETM,0)-ISNULL((SELECT SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT Temp WHERE Temp.REQ_ID=@P_REQ_ID AND Temp.TRADE_ID=A.TRADE_ID
AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <>'APPROVE')
GROUP BY Temp.TRADE_ID),0)
 AS AMT_REMAIN_REQ,
A.REQDT_TYPE,A.KHOI_ID,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,
 A.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,A.TRADE_TYPE,A.SUP_ID,CS.SUP_NAME,CS.SUP_CODE,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID,A.[NAME] AS REASON_CDT
FROM PL_REQUEST_DOC_DT A
LEFT JOIN dbo.PL_TRADEDETAIL PL ON A.TRADE_ID=PL.TRADE_ID
LEFT JOIN CM_GOODS B On A.GOODS_ID=B.GD_ID
LEFT JOIN dbo.CM_GOODSTYPE GT ON B.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID

LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.KHOI_ID
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=A.HANGHOA_ID
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=A.SUP_ID
 WHERE A.REQ_ID=(SELECT REQ_PARENT_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID)  
 END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_REQ_GOOD_Search]'
GO

/*
SELECT * FROM PL_TRADEDETAIL WHERE PLAN_ID = 'PLM000000000143'
select * from TR_CONTRACT_DT WHERE CONTRACT_ID = 'TRC000000000039'
	[TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000143','TRC000000000039','','',''
	exec [TR_PO_GOODS_Search] 1,'','','','','',NULL,'','PLM000000000003','','','',''
	exec [TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000003','','','',''
	SELECT * FROM TR_CONTRACT WHERE CONTRACT_TYPE = '1'
	SELECT * FROM PL_MASTER 
*/

ALTER PROCEDURE [dbo].[PL_REQ_GOOD_Search]
	@P_REQDT_TYPE  varchar(1)= NULL,
	@p_GD_ID VARCHAR(15) = NULL,
	@p_GD_CODE	varchar(15)  = NULL,
	@p_GD_NAME	nvarchar(200)  = NULL,
	@p_BRANCH_CODE	varchar(15)  = NULL,
	@p_BRANCH_ID VARCHAR(20) = NULL,
	@p_DEP_ID VARCHAR(20) = NULL,	
	@p_BRANCH_LOGIN varchar(15)  = NULL,
	@p_USER_LOGIN VARCHAR(20) = NULL,
	@P_PLAN_ID NVARCHAR(15) = NULL,
	@P_HH_ID VARCHAR(15),
	@p_GD_TYPE_ID VARCHAR(20) = NULL,
	@p_GD_TYPE_CODE VARCHAR(20)= NULL,
	@p_GD_TYPE_NAME NVARCHAR(500)= NULL,
	@P_TOP	INT = null
AS
BEGIN
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,@GD_CODE VARCHAR(50),
	 @BRANCH_TYPE VARCHAR(20),@BRANCH_LOGIN VARCHAR(20),@DEP_LOGIN VARCHAR(20),@ROLE_USER VARCHAR(20),@IS_ALL BIT

	
	SET @IS_ALL=0
	 
	 DECLARE @DVDM_ID TABLE( DVDM_ID VARCHAR(20))

	 SET @BRANCH_TYPE= (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_ID)
	 SELECT @BRANCH_LOGIN=TLSUBBRID,@DEP_LOGIN=SECUR_CODE ,@ROLE_USER=RoleName FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN
	 IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_LOGIN AND DEP_ID=@DEP_LOGIN))
		SET @IS_ALL=1
	 
	IF(@IS_ALL=1 OR @ROLE_USER='TGD')
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1
	END
	ELSE
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  PC.DVDM_ID FROM 
									dbo.PL_COSTCENTER PC 
									LEFT JOIN dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
									WHERE PT.DEP_ID=@DEP_LOGIN AND PT.BRANCH_ID=@BRANCH_LOGIN AND EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1)
		
	END
	DECLARE @lstGD TABLE (
	GD_ID VARCHAR(20)
	)

	DECLARE @lstGD_CODE TABLE (
	GD_CODE VARCHAR(20)
	)
	INSERT INTO @lstGD
	SELECT GD_ID  FROM dbo.CM_HANGHOA_GOODS WHERE HH_ID=@P_HH_ID
	

	IF(@P_HH_ID IS NOT NULL AND @P_HH_ID <>'')
	BEGIN	
		IF(NOT EXISTS(SELECT PM.PLAN_ID FROM dbo.PL_MASTER PM 
		LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.PLAN_ID=PM.PLAN_ID
		WHERE (PT.GOODS_ID =@p_GD_ID OR EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=PT.GOODS_ID)) 
		AND PM.YEAR = YEAR(GETDATE()) 
		AND     ( 
						(
							@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID 
							AND ((@p_DEP_ID = @DEP_LOGIN) OR (@p_DEP_ID <> @DEP_LOGIN AND PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))
						) 
					OR 
					(@BRANCH_TYPE='CN' AND PM.BRANCH_ID =@p_BRANCH_ID  AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))   ))
					OR
					(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID      )
				)
			)
			
		)
		BEGIN
			SET @P_REQDT_TYPE='O'
			INSERT INTO @lstGD_CODE			
			SELECT REPLACE(GD_CODE,'.I.','.O.') FROM dbo.CM_GOODS WHERE GD_ID IN (SELECT GD_ID FROM @lstGD)				
		END                                                         
	END




	IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
	BEGIN	
		IF(@P_REQDT_TYPE='I')
		BEGIN
			SELECT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,CG.GD_CODE,CG.GD_NAME,PM.BRANCH_ID,PM.DEPT_ID,
			ISNULL(DT.QUANTITY,0) AS QUANTITY,
			ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
			ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
			ISNULL(DT.QUANTITY,0)-	ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
			ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM, 
			ISNULL( DT.AMT_APP,0) AS AMT_APP,
			ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
			ISNULL( DT.AMT_ETM,0) AS AMT_ETM, 
			ISNULL( DT.AMT_TF,0) AS AMT_TF, 
			ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,	
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN, 
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0) AS AMT_REMAIN_ETM,
			CB.BRANCH_CODE,CB.BRANCH_NAME,'I' AS REQDT_TYPE,
			DT.NOTES,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,CD.DEP_CODE,CD.DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM dbo.PL_MASTER PM
			LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
			LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
			
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
			
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND (CB.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE +'%' OR @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE ='')
			--AND (PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)))
			--AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')
			AND (PM.PLAN_ID LIKE '%' + @P_PLAN_ID+'%' OR @P_PLAN_ID IS NULL OR @P_PLAN_ID='' )
			AND (DT.GOODS_ID = @p_GD_ID    OR @p_GD_ID IS NULL OR @p_GD_ID ='')
			AND  (EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=DT.GOODS_ID) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
			AND     ( 
						(
							@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID 
							AND ((@p_DEP_ID = @DEP_LOGIN) OR (@p_DEP_ID <> @DEP_LOGIN AND PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))
						) 
					OR 
					(@BRANCH_TYPE='CN' AND PM.BRANCH_ID =@p_BRANCH_ID  AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_LOGIN) ))   ))
					OR
					(@BRANCH_TYPE='PGD' AND PM.BRANCH_ID =@p_BRANCH_ID      )
				)
			AND PM.YEAR = YEAR(GETDATE())

			AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
		ELSE
        BEGIN
			SELECT '' AS PLAN_ID,'' AS PLAN_CODE,'' AS TRADE_ID, CG.GD_ID AS GOODS_ID, CG.GD_CODE ,CG.GD_NAME ,'' AS BRANCH_ID,'' AS DEPT_ID,
			@l_QUANTITY AS QUANTITY,
			@l_QUANTITY_EXE AS QUANTITY_EXE,
			@l_QUANTITY_EXE AS QUANTITY_ETM,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN_ETM,
			@l_AMT AS AMT_APP,
			@l_AMT_EXE AS AMT_EXE,
			@l_AMT_EXE AS AMT_ETM,
			@l_AMT_EXE AS AMT_TF,
			@l_AMT_EXE AS AMT_RECEIVE_TF,
			@l_AMT_REMAIN AS AMT_REMAIN,
			@l_AMT_REMAIN AS AMT_REMAIN_ETM,
			'' AS BRANCH_CODE,
			N'Ngoài kế hoạch' AS BRANCH_NAME,
			'O' AS REQDT_TYPE,
			'' AS NOTES
			,'' AS PLAN_TYPE_CODE,'' AS PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,CK.DVDM_NAME AS KHOI_NAME,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,'' DEP_CODE,'' DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM  dbo.CM_GOODS CG 
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID


		
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND CG.GD_CODE LIKE '%.O.%'
			AND (EXISTS(SELECT GD_CODE FROM @lstGD_CODE WHERE [@lstGD_CODE].GD_CODE=CG.GD_CODE ) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
			AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
	END
	ELSE
	BEGIN	
		IF(@P_REQDT_TYPE='I')
		BEGIN
			SELECT PM.PLAN_ID,PM.PLAN_CODE,DT.TRADE_ID,DT.GOODS_ID,CG.GD_CODE,CG.GD_NAME,PM.BRANCH_ID,PM.DEPT_ID,
			ISNULL(DT.QUANTITY,0) AS QUANTITY,
			ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_EXE,
			ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_ETM,
			ISNULL(DT.QUANTITY,0)-	ISNULL(DT.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
			ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM, 
			ISNULL( DT.AMT_APP,0) AS AMT_APP,
			ISNULL( DT.AMT_EXE,0) AS AMT_EXE,
			ISNULL( DT.AMT_ETM,0) AS AMT_ETM, 
			ISNULL( DT.AMT_TF,0) AS AMT_TF, 
			ISNULL( DT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,	
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)- ISNULL(DT.AMT_EXE,0) AS AMT_REMAIN, 
			ISNULL(DT.AMT_APP,0) + ISNULL( DT.AMT_RECEIVE_TF,0)- ISNULL( DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0) AS AMT_REMAIN_ETM,
			CB.BRANCH_CODE,CB.BRANCH_NAME,'I' AS REQDT_TYPE,
			DT.NOTES,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,CD.DEP_CODE,CD.DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM dbo.PL_MASTER PM
			LEFT JOIN dbo.PL_TRADEDETAIL DT ON DT.PLAN_ID=PM.PLAN_ID
			LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PM.PLAN_TYPE_ID
			
			LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PM.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PM.DEPT_ID
			
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND (CB.BRANCH_CODE LIKE '%' + @p_BRANCH_CODE +'%' OR @p_BRANCH_CODE IS NULL OR @p_BRANCH_CODE ='')
			--AND (PM.BRANCH_ID IN (SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)))
			--AND(PM.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID='')
			AND (PM.PLAN_ID LIKE '%' + @P_PLAN_ID+'%' OR @P_PLAN_ID IS NULL OR @P_PLAN_ID='' )
				AND (DT.GOODS_ID = @p_GD_ID    OR @p_GD_ID IS NULL OR @p_GD_ID ='')
			AND  (EXISTS(SELECT GD_ID FROM @lstGD WHERE [@lstGD].GD_ID=DT.GOODS_ID) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
		AND     ( 
						(
							@BRANCH_TYPE='HS' AND PM.BRANCH_ID =@p_BRANCH_ID AND PM.DEPT_ID=@p_DEP_ID 
							AND ((@p_DEP_ID = @DEP_LOGIN) OR (@p_DEP_ID <> @DEP_LOGIN AND PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID)))
						) 
					OR 
					(@BRANCH_TYPE<>'HS' AND PM.BRANCH_ID =@p_BRANCH_ID  AND ((@p_BRANCH_ID=@BRANCH_LOGIN) OR (@p_BRANCH_ID <> @BRANCH_LOGIN AND PM.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID) ))   )
				)
		AND PM.YEAR = YEAR(GETDATE())
		AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
		ELSE
        BEGIN
			SELECT '' AS PLAN_ID,'' AS PLAN_CODE,'' AS TRADE_ID, CG.GD_ID AS GOODS_ID, CG.GD_CODE ,CG.GD_NAME ,'' AS BRANCH_ID,'' AS DEPT_ID,
			@l_QUANTITY AS QUANTITY,
			@l_QUANTITY_EXE AS QUANTITY_EXE,
			@l_QUANTITY_EXE AS QUANTITY_ETM,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN,
			@l_QUANTITY_REMAIN AS QUANTITY_REMAIN_ETM,
			@l_AMT AS AMT_APP,
			@l_AMT_EXE AS AMT_EXE,
			@l_AMT_EXE AS AMT_ETM,
			@l_AMT_EXE AS AMT_TF,
			@l_AMT_EXE AS AMT_RECEIVE_TF,
			@l_AMT_REMAIN AS AMT_REMAIN,
			@l_AMT_REMAIN AS AMT_REMAIN_ETM,
			'' AS BRANCH_CODE,
			N'Ngoài kế hoạch' AS BRANCH_NAME,
			'O' AS REQDT_TYPE,
			'' AS NOTES
			,'' AS PLAN_TYPE_CODE,'' AS PLAN_TYPE_NAME,CM.DVDM_ID AS COST_ID,CM.DVDM_CODE AS COST_CODE,CM.DVDM_NAME AS COST_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_ID AS KHOI_ID,
			DM.DVDM_ID,DM.DVDM_CODE,DM.DVDM_NAME,'' DEP_CODE,'' DEP_NAME,GT.GD_TYPE_NAME,GT.GD_TYPE_CODE,GT.GD_TYPE_ID
			FROM  dbo.CM_GOODS CG 
			LEFT JOIN dbo.CM_GOODSTYPE GT ON CG.GD_CODE LIKE '%.' + GT.GD_TYPE_CODE +'.%'
			LEFT JOIN dbo.CM_GOOD_DVDM CGDM ON CGDM.GD_ID=CG.GD_ID
			LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=CGDM.DVDM_ID
			LEFT JOIN dbo.CM_DVDM CM ON CM.DVDM_ID=CGDM.DVCM_ID
			LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=CGDM.KHOI_ID
			WHERE (1=1)
			AND (CG.GD_NAME LIKE '%' +  @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME ='')
			AND (CG.GD_CODE LIKE '%' +  @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE ='')
			AND CG.GD_CODE LIKE '%.O.%'
			AND (EXISTS(SELECT GD_CODE FROM @lstGD_CODE WHERE [@lstGD_CODE].GD_CODE=CG.GD_CODE ) OR @P_HH_ID ='' OR @P_HH_ID IS NULL)
			AND (GT.GD_TYPE_ID LIKE '%' +  @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID ='')
			AND (GT.GD_TYPE_CODE LIKE '%' +  @p_GD_TYPE_CODE + '%' OR @p_GD_TYPE_CODE IS NULL OR @p_GD_TYPE_CODE ='')
			AND (GT.GD_TYPE_NAME LIKE N'%' +  @p_GD_TYPE_NAME + N'%' OR @p_GD_TYPE_NAME IS NULL OR @p_GD_TYPE_NAME ='')
		END
	END
END
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s





GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_REQUEST_TRANSFER_ByID]'
GO
ALTER PROCEDURE [dbo].[PL_REQUEST_TRANSFER_ByID]
@P_REQ_ID varchar(15),
@p_TYPE VARCHAR(15),
@p_TLNAME VARCHAR(20)
AS

DECLARE
@COST_ID TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID TABLE (
	DVDM_ID VARCHAR(15)
)

DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID  VARCHAR(15),@ROLE VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0

SET @DEP_ID=(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)

IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID))
		SET @IS_ALL=1

SET @ROLE=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)

	INSERT INTO @COST_ID
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID

	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID

IF(@p_TYPE='DVKD')
BEGIN
SELECT A.*,FB.BRANCH_CODE AS FR_BRANCH_CODE, FB.BRANCH_NAME AS FR_BRANCH_NAME,TB.BRANCH_CODE AS TO_BRANCH_CODE,
TB.BRANCH_NAME AS TO_BRANCH_NAME,FG.GD_CODE AS FR_GD_CODE,FG.GD_NAME AS FR_GD_NAME,TG.GD_CODE AS TO_GD_CODE,TG.GD_NAME AS TO_GD_NAME,CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
,DM.DVDM_CODE,DM.DVDM_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,FD.DEP_NAME AS FR_DEP_NAME,FD.DEP_CODE AS FR_DEP_CODE,TD.DEP_NAME AS TO_DEP_NAME,TD.DEP_CODE AS TO_DEP_CODE,
ISNULL(PL.AMT_APP,0) AS AMT_APP,ISNULL(PL.AMT_EXE,0) AS AMT_EXE, ISNULL(PL.AMT_ETM,0) AS AMT_ETM,ISNULL(PL.AMT_TF,0) AS AMT_TF, ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL((SELECT  SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_TRANSFER Temp WHERE
 Temp.REQ_DOC_ID=@P_REQ_ID AND Temp.FR_TRADE_ID= A.FR_TRADE_ID AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <> 'APPROVE' )
 GROUP BY Temp.FR_TRADE_ID
 ),0)AS AMT_REMAIN_ETM_TF,GTF.GD_TYPE_NAME AS FR_GD_TYPE_NAME,GTF.GD_TYPE_CODE AS FR_GD_TYPE_CODE,GTF.GD_TYPE_ID AS FR_GD_TYPE_ID,
 GTT.GD_TYPE_NAME AS TO_GD_TYPE_NAME,GTT.GD_TYPE_CODE AS TO_GD_TYPE_CODE,GTT.GD_TYPE_ID AS TO_GD_TYPE_ID
FROM dbo.PL_REQUEST_TRANSFER A
LEFT JOIN dbo.CM_BRANCH FB ON FB.BRANCH_ID=A.FR_BRN_ID
LEFT JOIN dbo.CM_BRANCH TB ON TB.BRANCH_ID=A.TO_BRN_ID
LEFT JOIN dbo.CM_DEPARTMENT FD ON FD.DEP_ID=A.FR_DEP_ID
LEFT JOIN dbo.CM_DEPARTMENT TD ON TD.DEP_ID=A.TO_DEP_ID
LEFT JOIN dbo.CM_GOODS FG ON FG.GD_ID=A.FR_GOOD_ID
LEFT JOIN dbo.CM_GOODS TG ON TG.GD_ID=A.TO_GOOD_ID
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.FR_DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.FR_KHOI_ID
LEFT JOIN dbo.PL_TRADEDETAIL PL ON PL.TRADE_ID=A.FR_TRADE_ID
LEFT JOIN dbo.CM_GOODSTYPE GTF ON FG.GD_CODE LIKE '%.' + GTF.GD_TYPE_CODE +'.%'
LEFT JOIN dbo.CM_GOODSTYPE GTT ON TG.GD_CODE LIKE '%.' + GTT.GD_TYPE_CODE +'.%'
 WHERE A.REQ_DOC_ID=@P_REQ_ID 
END
ELSE IF(@p_TYPE='PDTT')
BEGIN
SELECT A.*,FB.BRANCH_CODE AS FR_BRANCH_CODE, FB.BRANCH_NAME AS FR_BRANCH_NAME,TB.BRANCH_CODE AS TO_BRANCH_CODE,
TB.BRANCH_NAME AS TO_BRANCH_NAME,FG.GD_CODE AS FR_GD_CODE,FG.GD_NAME AS FR_GD_NAME,TG.GD_CODE AS TO_GD_CODE,TG.GD_NAME AS TO_GD_NAME,CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
,DM.DVDM_CODE,DM.DVDM_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,FD.DEP_NAME AS FR_DEP_NAME,FD.DEP_CODE AS FR_DEP_CODE,TD.DEP_NAME AS TO_DEP_NAME,TD.DEP_CODE AS TO_DEP_CODE,
ISNULL(PL.AMT_APP,0) AS AMT_APP,ISNULL(PL.AMT_EXE,0) AS AMT_EXE, ISNULL(PL.AMT_ETM,0) AS AMT_ETM,ISNULL(PL.AMT_TF,0) AS AMT_TF, ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL((SELECT  SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_TRANSFER Temp WHERE
 Temp.REQ_DOC_ID=@P_REQ_ID AND Temp.FR_TRADE_ID= A.FR_TRADE_ID AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <> 'APPROVE' )
 GROUP BY Temp.FR_TRADE_ID
 ),0)AS AMT_REMAIN_ETM_TF,GTF.GD_TYPE_NAME AS FR_GD_TYPE_NAME,GTF.GD_TYPE_CODE AS FR_GD_TYPE_CODE,GTF.GD_TYPE_ID AS FR_GD_TYPE_ID,
 GTT.GD_TYPE_NAME AS TO_GD_TYPE_NAME,GTT.GD_TYPE_CODE AS TO_GD_TYPE_CODE,GTT.GD_TYPE_ID AS TO_GD_TYPE_ID
FROM dbo.PL_REQUEST_TRANSFER A
LEFT JOIN dbo.CM_BRANCH FB ON FB.BRANCH_ID=A.FR_BRN_ID
LEFT JOIN dbo.CM_BRANCH TB ON TB.BRANCH_ID=A.TO_BRN_ID
LEFT JOIN dbo.CM_DEPARTMENT FD ON FD.DEP_ID=A.FR_DEP_ID
LEFT JOIN dbo.CM_DEPARTMENT TD ON TD.DEP_ID=A.TO_DEP_ID
LEFT JOIN dbo.CM_GOODS FG ON FG.GD_ID=A.FR_GOOD_ID
LEFT JOIN dbo.CM_GOODS TG ON TG.GD_ID=A.TO_GOOD_ID
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.FR_DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.FR_KHOI_ID
LEFT JOIN dbo.PL_TRADEDETAIL PL ON PL.TRADE_ID=A.FR_TRADE_ID
LEFT JOIN dbo.CM_GOODSTYPE GTF ON FG.GD_CODE LIKE '%.' + GTF.GD_TYPE_CODE +'.%'
LEFT JOIN dbo.CM_GOODSTYPE GTT ON TG.GD_CODE LIKE '%.' + GTT.GD_TYPE_CODE +'.%'
 WHERE A.REQ_DOC_ID=@P_REQ_ID AND (@ROLE='TGD' OR @IS_ALL=1 OR @ROLE='HDQT' OR EXISTS(SELECT * FROM @DVDM_ID WHERE DVDM_ID=A.FR_DVDM_ID OR DVDM_ID=A.FR_KHOI_ID OR
 A.TO_DVDM_ID=DVDM_ID OR A.TO_KHOI_ID=DVDM_ID
 ))
END
ELSE IF(@p_TYPE='XLTT' OR @p_TYPE='TFJOB')
BEGIN
SELECT A.*,FB.BRANCH_CODE AS FR_BRANCH_CODE, FB.BRANCH_NAME AS FR_BRANCH_NAME,TB.BRANCH_CODE AS TO_BRANCH_CODE,
TB.BRANCH_NAME AS TO_BRANCH_NAME,FG.GD_CODE AS FR_GD_CODE,FG.GD_NAME AS FR_GD_NAME,TG.GD_CODE AS TO_GD_CODE,TG.GD_NAME AS TO_GD_NAME,CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
,DM.DVDM_CODE,DM.DVDM_NAME,CK.DVDM_CODE AS KHOI_CODE,CK.DVDM_NAME AS KHOI_NAME,FD.DEP_NAME AS FR_DEP_NAME,FD.DEP_CODE AS FR_DEP_CODE,TD.DEP_NAME AS TO_DEP_NAME,TD.DEP_CODE AS TO_DEP_CODE,
ISNULL(PL.AMT_APP,0) AS AMT_APP,ISNULL(PL.AMT_EXE,0) AS AMT_EXE, ISNULL(PL.AMT_ETM,0) AS AMT_ETM,ISNULL(PL.AMT_TF,0) AS AMT_TF, ISNULL(PL.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_EXE,0) AS AMT_REMAIN,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) AS AMT_REMAIN_ETM,
 ISNULL(PL.AMT_APP,0) + ISNULL(PL.AMT_RECEIVE_TF,0)-ISNULL(PL.AMT_TF,0)-ISNULL(PL.AMT_ETM,0) - ISNULL((SELECT  SUM(Temp.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_TRANSFER Temp WHERE
 Temp.REQ_DOC_ID=@P_REQ_ID AND Temp.FR_TRADE_ID= A.FR_TRADE_ID AND EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@P_REQ_ID AND PROCESS_ID <> 'APPROVE' )
 GROUP BY Temp.FR_TRADE_ID
 ),0)AS AMT_REMAIN_ETM_TF,GTF.GD_TYPE_NAME AS FR_GD_TYPE_NAME,GTF.GD_TYPE_CODE AS FR_GD_TYPE_CODE,GTF.GD_TYPE_ID AS FR_GD_TYPE_ID,
 GTT.GD_TYPE_NAME AS TO_GD_TYPE_NAME,GTT.GD_TYPE_CODE AS TO_GD_TYPE_CODE,GTT.GD_TYPE_ID AS TO_GD_TYPE_ID
FROM dbo.PL_REQUEST_TRANSFER A
LEFT JOIN dbo.CM_BRANCH FB ON FB.BRANCH_ID=A.FR_BRN_ID
LEFT JOIN dbo.CM_BRANCH TB ON TB.BRANCH_ID=A.TO_BRN_ID
LEFT JOIN dbo.CM_DEPARTMENT FD ON FD.DEP_ID=A.FR_DEP_ID
LEFT JOIN dbo.CM_DEPARTMENT TD ON TD.DEP_ID=A.TO_DEP_ID
LEFT JOIN dbo.CM_GOODS FG ON FG.GD_ID=A.FR_GOOD_ID
LEFT JOIN dbo.CM_GOODS TG ON TG.GD_ID=A.TO_GOOD_ID
LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=A.FR_DVDM_ID
LEFT JOIN dbo.CM_DVDM CK ON CK.DVDM_ID=A.FR_KHOI_ID
LEFT JOIN dbo.PL_TRADEDETAIL PL ON PL.TRADE_ID=A.FR_TRADE_ID
LEFT JOIN dbo.CM_GOODSTYPE GTF ON FG.GD_CODE LIKE '%.' + GTF.GD_TYPE_CODE +'.%'
LEFT JOIN dbo.CM_GOODSTYPE GTT ON TG.GD_CODE LIKE '%.' + GTT.GD_TYPE_CODE +'.%'
 WHERE A.REQ_DOC_ID=@P_REQ_ID AND  (A.FR_BRN_ID=@BRANCH_ID AND( A.FR_DEP_ID=@DEP_ID OR A.FR_DEP_ID IS NULL OR A.FR_DEP_ID='')OR  @ROLE='TGD' OR @IS_ALL=1 OR @ROLE='HDQT')
END




 --EXEC TR_PO_DETAIL_ById 'TRPM00000000001', 'DV0001'






GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_REQUEST_COSTCENTER_ByID]'
GO
ALTER PROCEDURE [dbo].[PL_REQUEST_COSTCENTER_ByID]
@P_REQ_ID varchar(15),
@p_TYPE VARCHAR(15),
@p_TLNAME VARCHAR(20)
AS

DECLARE @DEP_ID VARCHAR(15),@BRANCH_ID  VARCHAR(15),@ROLE VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0

DECLARE
@COST_ID TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID TABLE (
	DVDM_ID VARCHAR(15)
)

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

SET @BRANCH_ID=(SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
SET @ROLE=(SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME)
IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID))
		SET @IS_ALL=1


	INSERT INTO @COST_ID
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID

	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID) GROUP BY DVDM_ID


IF(@p_TYPE='DVKD')
BEGIN
	SELECT A.*,CC.DVDM_NAME AS COST_NAME,CC.DVDM_CODE AS COST_CODE,  CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
	FROM dbo.PL_REQUEST_COSTCENTER A
	LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
	 WHERE A.REQ_ID=@P_REQ_ID
END
ELSE IF(@p_TYPE='PDTT')
BEGIN
	SELECT A.*,CC.DVDM_NAME AS COST_NAME,CC.DVDM_CODE AS COST_CODE,  CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
FROM dbo.PL_REQUEST_COSTCENTER A
	LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
 WHERE A.REQ_ID=@P_REQ_ID  AND (
 ( A.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @ROLE ='TGD' OR @IS_ALL=1 OR @ROLE='HDQT'))

END
ELSE IF(@p_TYPE='XLTT' OR @p_TYPE='TFJOB')
BEGIN
	SELECT A.*,CC.DVDM_NAME AS COST_NAME,CC.DVDM_CODE AS COST_CODE,  CASE  WHEN A.AUTH_STATUS='A' THEN N'Đã xác nhận' ELSE N'Chưa xác nhận' END AS STATUS_NAME
	FROM dbo.PL_REQUEST_COSTCENTER A
	LEFT JOIN dbo.CM_DVDM CC ON A.COST_ID=CC.DVDM_ID
	 WHERE A.REQ_ID=@P_REQ_ID  AND (
	 ( A.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))
 OR @ROLE ='TGD' OR @IS_ALL=1  OR @ROLE='HDQT' )
 
END



 --EXEC TR_PO_DETAIL_ById 'TRPM00000000001', 'DV0001'






GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_REQUEST_DOC_App]'
GO
ALTER PROCEDURE [dbo].[PL_REQUEST_DOC_App]
    @p_REQ_ID VARCHAR(15) = NULL,
    @p_AUTH_STATUS VARCHAR(1) = NULL,
    @p_CHECKER_ID VARCHAR(15) = NULL,
    @p_APPROVE_DT DATETIME = NULL,
    @p_ROLE_LOGIN VARCHAR(50) = NULL,
    @p_BRANCH_LOGIN VARCHAR(15),
    @p_PROCESS_DES NVARCHAR(500)
AS

BEGIN TRANSACTION;
---LUCTV KIEM TRA NEU TO TRINH DANG BI TRA VE THI KHONG DUOC PHEP DUYET
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE AUTH_STATUS ='R' AND REQ_ID =@p_REQ_ID))
BEGIN
	ROLLBACK TRANSACTION
	SELECT '-1' as Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_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
--SET @p_APPROVE_DT = @p_APPROVE_DT 

--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '';
IF (NOT EXISTS (SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID))
    SET @ERRORSYS = 'REQ-00002';
IF @ERRORSYS <> ''
BEGIN
    ROLLBACK TRANSACTION;
    SELECT ErrorCode Result,
           ErrorDesc ErrorDesc
    FROM SYS_ERROR
    WHERE ErrorCode = @ERRORSYS;
    RETURN '0';
END;
DECLARE  @ERROR BIT ,@EROOR_DES NVARCHAR(500)
SELECT @ERROR=ERROR,
       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'APPNEW','PL_REQUEST_DOC',@p_CHECKER_ID,'APPNEW')
IF(@ERROR=1)
BEGIN
	 ROLLBACK TRANSACTION;
    SELECT '-1'  Result,
           @EROOR_DES ErrorDesc
   
    RETURN '0';
END
--UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
--WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN))
DECLARE @BRANCH_TYPE_LOGIN VARCHAR(15)
SET @BRANCH_TYPE_LOGIN = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
DECLARE @Result VARCHAR(5),
        @TOTAL_TRANSFER DECIMAL(18, 0),
        @TOTAL_AMT DECIMAL(18, 0),
        @ROLE_USER_NOTIFI VARCHAR(50),
        @ROLE_ID VARCHAR(20),
        @ROLE_TF VARCHAR(20),
        @LIMIT_VALUE DECIMAL(18, 0),
        @STEP_CURR VARCHAR(20),
        @STEP_PARENT VARCHAR(20),
        @COST_ID VARCHAR(20),
        @FR_BRANCH_ID VARCHAR(20),
        @FR_DEP_ID VARCHAR(20),
        @DVDM_ID VARCHAR(20),
        @IS_NEXT BIT = 0,
        @TOTAL_AMT_GD DECIMAL(12, 0),
        @STOP BIT,
        @NOTES NVARCHAR(100);
DECLARE @ROLE_CDT VARCHAR(20),
        @DVDM_CDT VARCHAR(20),
        @LIMIT_VALUE_CDT VARCHAR(20),
        @NOTES_CDT VARCHAR(20);
DECLARE @DATA_DVDM TABLE
(
    DVDM_ID VARCHAR(20),
    TOTAL_AMT DECIMAL(12, 0),
	IS_PTGD BIT
);
--UPDATE dbo.PL_REQUEST_COSTCENTER 
--SET DVMD_ID=(SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE PL_COSTCENTER.COST_ID=PL_REQUEST_COSTCENTER.COST_ID),
--TOTAL_AMT_GD=(SELECT SUM(PM.TOTAL_AMT) AS AMT FROM
--(SELECT PLAN_ID,GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT  WHERE REQDT_TYPE='I' AND REQ_ID=@p_REQ_ID) PR
--LEFT JOIN dbo.PL_MASTER PM ON PR.PLAN_ID=PM.PLAN_ID
--WHERE PM.COST_ID=PL_REQUEST_COSTCENTER.COST_ID)
--WHERE REQ_ID=@p_REQ_ID
	INSERT INTO @DATA_DVDM
	SELECT KHOI_ID,
		   SUM(TOTAL_AMT) AS TOTAL_AMT,DM.IS_PTGD
	FROM dbo.PL_REQUEST_DOC_DT DT
	LEFT JOIN CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1
	WHERE REQ_ID = @p_REQ_ID AND DT.KHOI_ID IS NOT NULL AND DT.KHOI_ID <>''
	GROUP BY KHOI_ID,DM.IS_PTGD;


	DELETE FROM dbo.PL_REQUEST_PROCESS
	WHERE REQ_ID = @p_REQ_ID;
	DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10),
	@BRANCH_CREATE_TYPE VARCHAR(10)


	SELECT @BRANCH_ID =BRANCH_ID,@DEP_ID=DEP_ID,@BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID

	SET @BRANCH_TYPE=(SELECT  TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
	SET @BRANCH_CREATE_TYPE=(SELECT  TOP 1 BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)

	
	IF(@BRANCH_TYPE='PGD')
		SET @BRANCH_ID=(SELECT TOP 1 FATHER_ID  FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
		
	
	-- KIEM TRA XEM CO CAP PHE DUYET TRUNG GIAN HAY KHONG 20 05 2020
	IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN'))
	BEGIN
		
		DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID
		INSERT INTO dbo.PL_PROCESS
		(
			REQ_ID,
			PROCESS_ID,
			CHECKER_ID,
			APPROVE_DT,
			PROCESS_DESC,NOTES
		)
		VALUES
		(   @p_REQ_ID,        -- REQ_ID - varchar(15)
			'SIGN',        -- PROCESS_ID - varchar(10)
			@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
			@p_APPROVE_DT , -- APPROVE_DT - datetime
			N'Cấp phê duyệt trung gian xác nhận tờ trình chủ trương',
			N'Cấp phê duyệt trung gian'
		)
	--- DUA CAP PHE DUYET TRUONG DON VI
	    INSERT INTO dbo.PL_REQUEST_PROCESS
		(
		    REQ_ID,
		    PROCESS_ID,
		    STATUS,
		    ROLE_USER,
		    BRANCH_ID,
			DEP_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)
		    'APPNEW',        -- PROCESS_ID - varchar(10)
		    'C',        -- STATUS - varchar(5)
		    'GDDV',        -- ROLE_USER - varchar(50)
		    --@BRANCH_CREATE, 
			@BRANCH_ID,
			@DEP_ID,      -- BRANCH_ID - varchar(15)
		    '',        -- CHECKER_ID - varchar(15)
		    NULL,      -- APPROVE_DT - datetime
		    '',        -- PARENT_PROCESS_ID - varchar(10)
		    'N',        -- IS_LEAF - varchar(1)
		    '',        -- COST_ID - varchar(15)
		    '',        -- DVDM_ID - varchar(15)
		    N'Chờ trưởng đơn vị phê duyệt',       -- NOTES - nvarchar(500)
		    NULL       -- IS_HAS_CHILD - bit
		 )
	--- UPDATE PROCESS_ID VE APP_NEW
	UPDATE PL_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID
	END
	ELSE
	BEGIN -- NGUOC LAI LA GIAM DOC DON VI PHE DUYET
		IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> ''))
		BEGIN
			IF(NOT EXISTS (SELECT * FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@p_REQ_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi cấp phê duyệt trung gian xác nhận. Vui lòng đợi nhân viên '+(SELECT SIGN_USER FROM PL_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+' xác nhận phiếu!' ErrorDesc
				RETURN '-1'
			END
		END
		INSERT INTO dbo.PL_REQUEST_PROCESS
		(
			REQ_ID,
			PROCESS_ID,
			STATUS,
			ROLE_USER,
			BRANCH_ID,
			DEP_ID,
			CHECKER_ID,
			APPROVE_DT,
			PARENT_PROCESS_ID,
			IS_LEAF,
			NOTES
		)
		VALUES
		(   @p_REQ_ID,               -- REQ_ID - varchar(15)
			'APPNEW',                  -- PROCESS_ID - varchar(10)
			'P',                     -- STATUS - varchar(5)
			'GDDV',                      -- ROLE_USER - varchar(50)
			@BRANCH_ID  ,
			@DEP_ID,                      -- BRANCH_ID - varchar(15)
			@p_CHECKER_ID,           -- CHECKER_ID - varchar(15)
			GETDATE() , -- APPROVE_DT - datetime
			NULL, 'N', N'Trưởng đơn vị phê duyệt');
		SET @STEP_CURR = 'APPNEW';
		SET @STEP_PARENT = 'APPNEW';

		DECLARE @ROLE_KT VARCHAR(20), @DVDM_KT VARCHAR(20),@NOTES_KT NVARCHAR(500)


		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 @NOTES_KT = (SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDVAL='KT' AND CDNAME='PROCESS_ID' AND CDTYPE='REQ') 

		IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND ROLE_USER=@ROLE_KT AND ( DVDM_ID=@DVDM_KT OR @DVDM_KT 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
				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)
					'KT',       -- PROCESS_ID - varchar(10)
					'U',          -- STATUS - varchar(5)
					@ROLE_KT,       -- ROLE_USER - varchar(50)
					'',           -- BRANCH_ID - varchar(15)
					'',           -- CHECKER_ID - varchar(15)
					NULL,         -- APPROVE_DT - datetime
					@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
					'N',          -- IS_LEAF - varchar(1)
					'',           -- COST_ID - varchar(15)
					@DVDM_KT, N'Chờ phòng kế toán xác nhận', 1
					-- DVDM_ID - varchar(15)
					);

				SET @STEP_PARENT='KT'
				END




		IF (EXISTS
		(
			SELECT REQ_COST_ID
			FROM dbo.PL_REQUEST_COSTCENTER
			WHERE REQ_ID = @p_REQ_ID
		)
		   )
		BEGIN
			DECLARE lstCostCenter CURSOR FOR
			SELECT COST_ID
			FROM dbo.PL_REQUEST_COSTCENTER
			WHERE REQ_ID = @p_REQ_ID AND COST_ID IS NOT NULL AND COST_ID <>'';
			OPEN lstCostCenter;
			FETCH NEXT FROM lstCostCenter
			INTO @COST_ID;
			WHILE @@FETCH_STATUS = 0
			BEGIN
				IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND ROLE_USER='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
				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
					@STEP_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

					UPDATE PL_REQUEST_COSTCENTER SET AUTH_STATUS ='A',NOTES=N'Đồng ý' WHERE 1= 1 AND
					 REQ_ID=@p_REQ_ID  AND COST_ID=@COST_ID


			
				END

				FETCH NEXT FROM lstCostCenter
				INTO @COST_ID;
			END;
			CLOSE lstCostCenter;
			DEALLOCATE lstCostCenter;
			IF(EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVCM'))
				SET @STEP_PARENT = 'DVCM';
		END;
		SET @TOTAL_AMT =
		(
			SELECT SUM(TOTAL_AMT) AS TOTAL_AMT
			FROM dbo.PL_REQUEST_DOC_DT
			WHERE REQ_ID = @p_REQ_ID
		);
		IF (EXISTS
		(
			SELECT REQ_TRANSFER_ID
			FROM dbo.PL_REQUEST_TRANSFER
			WHERE REQ_DOC_ID = @p_REQ_ID
		)
		   )
		BEGIN


			IF (EXISTS
			(
				SELECT FR_BRN_ID
				FROM dbo.PL_REQUEST_TRANSFER
				WHERE REQ_DOC_ID = @p_REQ_ID
					  AND
					  (
						  FR_BRN_ID <> @BRANCH_CREATE
						  OR FR_DEP_ID <> @DEP_CREATE
					  )
			)
			   )
			BEGIN
				DECLARE lstTransfer CURSOR FOR
				SELECT FR_BRN_ID,
					   FR_DEP_ID
				FROM dbo.PL_REQUEST_TRANSFER
				WHERE REQ_DOC_ID = @p_REQ_ID
					  AND
					  (
						  FR_BRN_ID <> @BRANCH_CREATE
						  OR FR_DEP_ID <> @DEP_CREATE
					  )
				GROUP BY FR_BRN_ID,
						 FR_DEP_ID;
				OPEN lstTransfer;
				FETCH NEXT FROM lstTransfer
				INTO @FR_BRANCH_ID,
					 @FR_DEP_ID;
				WHILE @@FETCH_STATUS = 0
				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,
						DEP_ID
					)
					VALUES
					(   @p_REQ_ID,     -- REQ_ID - varchar(15)
						'DVDC',        -- PROCESS_ID - varchar(10)
						'U',           -- STATUS - varchar(5)
						'GDDV',        -- ROLE_USER - varchar(50)
						@FR_BRANCH_ID, -- BRANCH_ID - varchar(15)
						'',            -- CHECKER_ID - varchar(15)
						NULL,          -- APPROVE_DT - datetime
						@STEP_PARENT,  -- PARENT_PROCESS_ID - varchar(10)
						'N',           -- IS_LEAF - varchar(1)
						'',            -- COST_ID - varchar(15)
						'',            -- DVDM_ID - varchar(15)
						N'Chờ đơn vị điều chuyển xác nhận', 1, @FR_DEP_ID);
					FETCH NEXT FROM lstTransfer
					INTO @FR_BRANCH_ID,
						 @FR_DEP_ID;
				END;
				CLOSE lstTransfer;
				DEALLOCATE lstTransfer;
				SET @STEP_PARENT = 'DVDC';
			END;

			-- Đầu mối nhận
			DECLARE lstTransfer CURSOR FOR
			SELECT TO_DVDM_ID
			FROM dbo.PL_REQUEST_TRANSFER
			WHERE REQ_DOC_ID = @p_REQ_ID AND TO_DVDM_ID IS NOT NULL AND TO_DVDM_ID <>''
			GROUP BY TO_DVDM_ID;
			OPEN lstTransfer;
			FETCH NEXT FROM lstTransfer
			INTO @DVDM_ID;
			WHILE @@FETCH_STATUS = 0
			BEGIN
				IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND ROLE_USER='GDDV' AND ( DVDM_ID=@DVDM_ID OR @DVDM_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
				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)
					'DVDM_DC',    -- 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
					@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
					'N',          -- IS_LEAF - varchar(1)
					'',           -- COST_ID - varchar(15)
					@DVDM_ID,     -- DVDM_ID - varchar(15)
					N'Chờ đơn vị đầu mối xác nhận', 0);
				END
				FETCH NEXT FROM lstTransfer
				INTO @DVDM_ID;
			END;
			CLOSE lstTransfer;
			DEALLOCATE lstTransfer;


			IF (EXISTS
			(
				SELECT FR_BRN_ID
				FROM dbo.PL_REQUEST_TRANSFER
				WHERE REQ_DOC_ID = @p_REQ_ID
					  AND FR_BRN_ID = @BRANCH_CREATE
					  AND FR_DEP_ID = @DEP_CREATE
			)
			   )
			BEGIN
				-- Đầu mối cho
				DECLARE lstTransfer CURSOR FOR
				SELECT FR_DVDM_ID
				FROM dbo.PL_REQUEST_TRANSFER
				WHERE REQ_DOC_ID = @p_REQ_ID
					  AND FR_BRN_ID = @BRANCH_CREATE
					  AND FR_DEP_ID = @DEP_CREATE
					  AND FR_DVDM_ID IS NOT NULL AND FR_DVDM_ID <>''
					  AND NOT EXISTS
				(
					SELECT *
					FROM dbo.PL_REQUEST_PROCESS
					WHERE REQ_ID = @p_REQ_ID
						  AND PROCESS_ID = 'DVDM_DC'
						  AND DVDM_ID = FR_DVDM_ID
				)
				GROUP BY FR_DVDM_ID;
				OPEN lstTransfer;
				FETCH NEXT FROM lstTransfer
				INTO @DVDM_ID;
				WHILE @@FETCH_STATUS = 0
				BEGIN
				IF(NOT EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND ROLE_USER='GDDV' AND ( DVDM_ID=@DVDM_ID OR @DVDM_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
					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)
						'DVDM_DC',    -- 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
						@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
						'N',          -- IS_LEAF - varchar(1)
						'',           -- COST_ID - varchar(15)
						@DVDM_ID,     -- DVDM_ID - varchar(15)
						N'Chờ đơn vị đầu mối xác nhận', 0);
				END
					FETCH NEXT FROM lstTransfer
					INTO @DVDM_ID;
				END;
				CLOSE lstTransfer;
				DEALLOCATE lstTransfer;




			   IF(EXISTS(SELECT TOP 1 ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DVDM_DC'))
					SET @STEP_PARENT='DVDM_DC'


			END;



			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)
				'TC',         -- PROCESS_ID - varchar(10)
				'U',          -- STATUS - varchar(5)
				'TC',         -- ROLE_USER - varchar(50)
				'',           -- BRANCH_ID - varchar(15)
				'',           -- CHECKER_ID - varchar(15)
				NULL,         -- APPROVE_DT - datetime
				@STEP_PARENT, -- PARENT_PROCESS_ID - varchar(10)
				'N',          -- IS_LEAF - varchar(1)
				'',           -- COST_ID - varchar(15)
				'',           -- DVDM_ID - varchar(15)
				N'Chờ đơn vị Tài chính xác nhận',1);
			SET @STEP_PARENT = 'TC';


			IF (NOT EXISTS
			(
				SELECT REQ_TRANSFER_ID
				FROM dbo.PL_REQUEST_TRANSFER
				WHERE REQ_DOC_ID = @p_REQ_ID
					  AND
					  (
						  FR_BRN_ID <> @BRANCH_CREATE
						  OR FR_DEP_ID <> @DEP_CREATE
					  )
			)
			   )
			BEGIN
				---Duyệt DC
				IF (
				   (
					   SELECT COUNT(T.FR_KHOI_ID) AS COUNT_ROW
					   FROM
					   (
						   SELECT FR_KHOI_ID
						   FROM dbo.PL_REQUEST_TRANSFER
						   WHERE REQ_DOC_ID = @p_REQ_ID
						   GROUP BY FR_KHOI_ID
					   ) T
				   ) > 1
				   )
				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)
						'TGD_DC',                       -- PROCESS_ID - varchar(10)
						'U',                            -- STATUS - varchar(5)
						'TGD',                          -- ROLE_USER - varchar(50)
						'',                             -- BRANCH_ID - varchar(15)
						'',                             -- CHECKER_ID - varchar(15)
						NULL,                           -- APPROVE_DT - datetime
						@STEP_PARENT,                   -- PARENT_PROCESS_ID - varchar(10)
						'',                             -- IS_LEAF - varchar(1)
						'',                             -- COST_ID - varchar(15)
						'',                             -- DVDM_ID - varchar(15)
						N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
						NULL                            -- IS_HAS_CHILD - bit
						);
					SET @STEP_PARENT = 'TGD_DC';
				END;
				ELSE
				BEGIN
					DECLARE @LIMTT_MAX DECIMAL(18, 2),
							@LIMIT_APP DECIMAL(18, 2),
							@KHOI_ID_TF VARCHAR(20);
					SET @KHOI_ID_TF =
					(
						SELECT TOP 1
							   FR_KHOI_ID
						FROM dbo.PL_REQUEST_TRANSFER
						WHERE REQ_DOC_ID = @p_REQ_ID
					);

					IF(@KHOI_ID_TF IS NOT NULL AND @KHOI_ID_TF<>'')
					BEGIN
					SET @LIMIT_APP =
					(
						SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
						FROM dbo.LIMIT_ACCUMULATE
						WHERE ROLE_ID = 'GDK'
							  AND DVDM_ID = @KHOI_ID_TF
					);
					SET @TOTAL_TRANSFER =
					(
						SELECT SUM(TOTAL_AMT) AS TOTAL
						FROM dbo.PL_REQUEST_TRANSFER
						WHERE REQ_DOC_ID = @p_REQ_ID
					);
					SET @LIMTT_MAX =
					(
						SELECT LIMIT_VALUE
						FROM dbo.TL_SYSROLE_LIMIT
						WHERE ROLE_ID = 'GDK'
							  AND LIMIT_TYPE = 'DCNS'
					);

					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_DC',                      -- PROCESS_ID - varchar(10)
						'U',                           -- STATUS - varchar(5)
						'GDK',                         -- ROLE_USER - varchar(50)
						'',                            -- BRANCH_ID - varchar(15)
						'',                            -- CHECKER_ID - varchar(15)
						GETDATE(),                     -- APPROVE_DT - datetime
						@STEP_PARENT,                  -- PARENT_PROCESS_ID - varchar(10)
						'N',                           -- IS_LEAF - varchar(1)
						'',                            -- COST_ID - varchar(15)
						@KHOI_ID_TF,                   -- DVDM_ID - varchar(15)
						N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
						NULL                           -- IS_HAS_CHILD - bit
						);
					SET @STEP_PARENT = 'GDK_DC';


					IF (@TOTAL_TRANSFER > @LIMTT_MAX OR @TOTAL_TRANSFER > @LIMIT_APP)
					BEGIN
					IF(EXISTS(SELECT TOP 1 DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=1))
					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)
							'PTGD_DC',                     -- PROCESS_ID - varchar(10)
							'U',                           -- STATUS - varchar(5)
							'PTGD',                        -- ROLE_USER - varchar(50)
							'',                            -- BRANCH_ID - varchar(15)
							'',                            -- CHECKER_ID - varchar(15)
							GETDATE(),                     -- APPROVE_DT - datetime
							@STEP_PARENT,                  -- PARENT_PROCESS_ID - varchar(10)
							'N',                           -- IS_LEAF - varchar(1)
							'',                            -- COST_ID - varchar(15)
							@KHOI_ID_TF,                   -- DVDM_ID - varchar(15)
							N'Chờ giám đốc khối xác nhận', -- NOTES - nvarchar(500)
							NULL                           -- IS_HAS_CHILD - bit
							);
						SET @STEP_PARENT = 'PTGD_DC';
						SET @LIMTT_MAX =
						(
							SELECT LIMIT_VALUE
							FROM dbo.TL_SYSROLE_LIMIT
							WHERE ROLE_ID = 'PTGD'
								  AND LIMIT_TYPE = 'DCNS'
						);
						SET @LIMIT_APP =
						(
							SELECT ISNULL(MAX_AMT, 0) - ISNULL(TOTAL_APP_AMT, 0) AS LIMIT_APP
							FROM dbo.LIMIT_ACCUMULATE
							WHERE ROLE_ID = 'PTGD'
								  AND DVDM_ID = @KHOI_ID_TF
						);

					END
						IF (@TOTAL_TRANSFER > @LIMTT_MAX OR @TOTAL_TRANSFER > @LIMIT_APP OR EXISTS(SELECT TOP 1 DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=0))
						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)
								'TGD_DC',                       -- PROCESS_ID - varchar(10)
								'U',                            -- STATUS - varchar(5)
								'TGD',                          -- ROLE_USER - varchar(50)
								'',                             -- BRANCH_ID - varchar(15)
								'',                             -- CHECKER_ID - varchar(15)
								NULL,                           -- APPROVE_DT - datetime
								@STEP_PARENT,                   -- PARENT_PROCESS_ID - varchar(10)
								'',                             -- IS_LEAF - varchar(1)
								'',                             -- COST_ID - varchar(15)
								'',                             -- DVDM_ID - varchar(15)
								N'Chờ tổng giám đốc phê duyệt', -- NOTES - nvarchar(500)
								NULL                            -- IS_HAS_CHILD - bit
								);
							SET @STEP_PARENT = 'TGD_DC';
						END;

					END;

					END


				END;
       
     
			END;




		END;
		ELSE
		BEGIN
	
		IF(@BRANCH_CREATE_TYPE='PGD')
		BEGIN
			DECLARE @BRANCH_PARENT VARCHAR(15)
			SET @BRANCH_PARENT=(SELECT TOP 1 FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
			IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND BRANCH_ID=@BRANCH_PARENT AND (DEP_ID IS NULL OR DEP_ID='')))
			BEGIN
				INSERT INTO dbo.PL_REQUEST_PROCESS
				(
				REQ_ID,
				PROCESS_ID,
				STATUS,
				ROLE_USER,
				BRANCH_ID,
				DEP_ID,
				CHECKER_ID,
				APPROVE_DT,
				PARENT_PROCESS_ID,
				IS_LEAF,
				NOTES
				)
				VALUES
				(   
				@p_REQ_ID,               -- REQ_ID - varchar(15)
				'DVC',                  -- PROCESS_ID - varchar(10)
				'U',                     -- STATUS - varchar(5)
				'GDDV',                      -- ROLE_USER - varchar(50)
				@BRANCH_PARENT,
				NULL,                      -- BRANCH_ID - varchar(15)
				NULL,           -- CHECKER_ID - varchar(15)
				NULL , -- APPROVE_DT - datetime
				@STEP_PARENT, 'N', N'Chờ giám đốc Chi Nhánh phê duyệt');

				SET @STEP_CURR = 'DVC';
				SET @STEP_PARENT = 'DVC';
			END
	
		END
		ELSE 
		IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV'
		AND ((
			BRANCH_ID=@BRANCH_CREATE 
			AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='') 
			AND (DEP_ID IS NULL OR DEP_ID='')))
			)
			OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC 
			LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID WHERE PL_REQUEST_PROCESS.DVDM_ID=PC.DVDM_ID AND DEP_ID=@DEP_CREATE AND BRANCH_ID=@BRANCH_CREATE)
			)
		))
		BEGIN
		INSERT INTO dbo.PL_REQUEST_PROCESS
				(
				REQ_ID,
				PROCESS_ID,
				STATUS,
				ROLE_USER,
				BRANCH_ID,
				DEP_ID,
				CHECKER_ID,
				APPROVE_DT,
				PARENT_PROCESS_ID,
				IS_LEAF,
				NOTES
				)
				VALUES
				(   
				@p_REQ_ID,               -- REQ_ID - varchar(15)
				'DVC',                  -- PROCESS_ID - varchar(10)
				'U',                     -- STATUS - varchar(5)
				'GDDV',                      -- ROLE_USER - varchar(50)
				@BRANCH_CREATE,
				@DEP_CREATE,                      -- BRANCH_ID - varchar(15)
				NULL,           -- CHECKER_ID - varchar(15)
				NULL , -- APPROVE_DT - datetime
				@STEP_PARENT, 'N', N'Chờ giám đốc Chi Nhánh phê duyệt');

		SET @STEP_CURR = 'DVC';
		SET @STEP_PARENT = 'DVC';
		END

		IF(EXISTS( SELECT * FROM   @DATA_DVDM))
		BEGIN
		SET @IS_NEXT =
		(
			SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDDV')
		);
		IF (@IS_NEXT = 1)
		BEGIN

			DECLARE lstCostCenter CURSOR FOR
			SELECT DVDM_ID,
				   TOTAL_AMT
			FROM @DATA_DVDM;
			OPEN lstCostCenter;
			FETCH NEXT FROM lstCostCenter
			INTO @DVDM_ID,
				 @TOTAL_AMT_GD;
			WHILE @@FETCH_STATUS = 0
			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)
					'GDK_TT',                                  -- PROCESS_ID - varchar(10)
					'U',                                       -- STATUS - varchar(5)
					'GDK',                                     -- ROLE_USER - varchar(50)
					'',                                        -- BRANCH_ID - varchar(15)
					'',                                        -- CHECKER_ID - varchar(15)
					NULL,                                      -- APPROVE_DT - datetime
					@STEP_PARENT,                              -- PARENT_PROCESS_ID - varchar(10)
					'N',                                       -- IS_LEAF - varchar(1)
					'',                                        -- COST_ID - varchar(15)
					@DVDM_ID, N'Chờ giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
					);
				FETCH NEXT FROM lstCostCenter
				INTO @DVDM_ID,
					 @TOTAL_AMT_GD;
			END;
			CLOSE lstCostCenter;
			DEALLOCATE lstCostCenter;

			SET @IS_NEXT =
			(
				SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'GDK')
			);

			SET @STEP_PARENT = 'GDK_TT';
			IF (@IS_NEXT = 1)
			BEGIN

				IF( EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=1) )
				BEGIN
				DECLARE lstCostCenter CURSOR FOR
				SELECT DVDM_ID,
					   TOTAL_AMT
				FROM @DATA_DVDM WHERE IS_PTGD=1;
				OPEN lstCostCenter;
				FETCH NEXT FROM lstCostCenter
				INTO @DVDM_ID,
					 @TOTAL_AMT_GD;
				WHILE @@FETCH_STATUS = 0
				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)
						'PTGDK_TT',                                         -- PROCESS_ID - varchar(10)
						'U',                                                -- STATUS - varchar(5)
						'PTGD',                                             -- ROLE_USER - varchar(50)
						'',                                                 -- BRANCH_ID - varchar(15)
						'',                                                 -- CHECKER_ID - varchar(15)
						NULL,                                               -- APPROVE_DT - datetime
						@STEP_PARENT,                                       -- PARENT_PROCESS_ID - varchar(10)
						'N',                                                -- IS_LEAF - varchar(1)
						'',                                                 -- COST_ID - varchar(15)
						@DVDM_ID, N'Chờ phó tổng giám đốc khối xác nhận', 0 -- DVDM_ID - varchar(15)
						);
					FETCH NEXT FROM lstCostCenter
					INTO @DVDM_ID,
						 @TOTAL_AMT_GD;
				END;
				CLOSE lstCostCenter;
				DEALLOCATE lstCostCenter;

				SET @IS_NEXT =
				(
					SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID, 'PTGD')
				);

				SET @STEP_PARENT = 'PTGDK_TT';
				END
				IF(EXISTS (SELECT DVDM_ID FROM @DATA_DVDM WHERE IS_PTGD=0))
				BEGIN
				SET @IS_NEXT=1
				END
				IF (@IS_NEXT = 1)
				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)
						'TGD',                               -- PROCESS_ID - varchar(10)
						'U',                                 -- STATUS - varchar(5)
						'TGD',                               -- ROLE_USER - varchar(50)
						'',                                  -- BRANCH_ID - varchar(15)
						'',                                  -- CHECKER_ID - varchar(15)
						NULL,                                -- APPROVE_DT - datetime
						@STEP_PARENT,                        -- PARENT_PROCESS_ID - varchar(10)
						'N',                                 -- IS_LEAF - varchar(1)
						'',                                  -- COST_ID - varchar(15)
						'', N'Chờ tổng giám đốc xác nhận', 0 -- DVDM_ID - varchar(15)
						);
					SET @STEP_PARENT = 'TGD';

					IF((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) > (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='TCTT-HDQT'))
					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)
						'HDQT',                               -- PROCESS_ID - varchar(10)
						'U',                                 -- STATUS - varchar(5)
						'HDQT',                               -- ROLE_USER - varchar(50)
						'',                                  -- BRANCH_ID - varchar(15)
						'',                                  -- CHECKER_ID - varchar(15)
						NULL,                                -- APPROVE_DT - datetime
						@STEP_PARENT,                        -- PARENT_PROCESS_ID - varchar(10)
						'N',                                 -- IS_LEAF - varchar(1)
						'',                                  -- COST_ID - varchar(15)
						'', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
						);
					SET @STEP_PARENT = 'HDQT';
					END
				END;



	
			--ELSE
			--BEGIN

			--END
		
			END;

		END;
		END
		END
		INSERT INTO dbo.PL_REQUEST_PROCESS
		(
			REQ_ID,
			PROCESS_ID,
			STATUS,
			ROLE_USER,
			BRANCH_ID,
			CHECKER_ID,
			APPROVE_DT,
			PARENT_PROCESS_ID,
			IS_LEAF,
			NOTES
		)
		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
			@STEP_PARENT, 'Y', N'Hoàn tất');



		IF @@Error <> 0
			GOTO ABORT;



		DECLARE @PROCESS_ID_CURR VARCHAR(10);
		SET @PROCESS_ID_CURR =
		(
			SELECT TOP 1
				   PROCESS_ID
			FROM dbo.PL_REQUEST_PROCESS
			WHERE REQ_ID = @p_REQ_ID
				  AND PARENT_PROCESS_ID = 'APPNEW'
		);

		UPDATE dbo.PL_REQUEST_PROCESS
		SET STATUS = 'C'
		WHERE PARENT_PROCESS_ID = 'APPNEW'
			  AND REQ_ID = @p_REQ_ID;
		UPDATE dbo.PL_REQUEST_DOC
		SET AUTH_STATUS = @p_AUTH_STATUS,
			APPROVE_DT = @p_APPROVE_DT,
			CHECKER_ID = @p_CHECKER_ID,
			PROCESS_ID = @PROCESS_ID_CURR
		WHERE REQ_ID = @p_REQ_ID;

		UPDATE dbo.PL_REQUEST_DOC_DT
		SET CHECKER_ID=@p_CHECKER_ID,
		APPROVE_DT=@p_APPROVE_DT
		WHERE REQ_ID = @p_REQ_ID;

		INSERT INTO dbo.PL_PROCESS
		(
			REQ_ID,
			PROCESS_ID,
			CHECKER_ID,
			APPROVE_DT,
			PROCESS_DESC,
			NOTES
		)
		VALUES
		(   @p_REQ_ID,                                            -- REQ_ID - varchar(15)
			'APPNEW',                                               -- PROCESS_ID - varchar(10)
			@p_CHECKER_ID,                                        -- CHECKER_ID - varchar(15)
			@p_APPROVE_DT,                                        -- APPROVE_DT - datetime
			@p_PROCESS_DES, CASE WHEN @BRANCH_TYPE_LOGIN ='PGD' THEN N'Trưởng phòng giao dịch xác nhận phiếu' ELSE N'Trưởng đơn vị phê duyệt' END -- PROCESS_DESC - nvarchar(1000)
			);
		IF (EXISTS
		(
			SELECT REQ_ID
			FROM dbo.PL_REQUEST_DOC
			WHERE REQ_ID = @p_REQ_ID
				  AND PROCESS_ID = 'APPROVE'
		)
		   )
		BEGIN
			EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID;
			EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID;
			SET @Result = '0';
		END;
		SET @Result = '1';
END
COMMIT TRANSACTION;
IF(EXISTS(SELECT * FROM PL_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID))
BEGIN
	SELECT @Result AS Result,
       @ROLE_USER_NOTIFI AS ROLE_NOTIFI,
       '' ErrorDesc;
		RETURN '0';
END
ELSE
BEGIN
	SELECT '4' as Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đã được đợi cấp phê duyệt trung gian xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt' ErrorDesc
	RETURN '4'
END
ABORT:
BEGIN

    ROLLBACK TRANSACTION;
    SELECT '-1' AS Result,
           '' ROLE_NOTIFI,
           '' ErrorDesc;
    RETURN '-1';
END;






GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_REQUEST_PROCESS_App]'
GO
ALTER PROCEDURE [dbo].[PL_REQUEST_PROCESS_App]
    @p_REQ_ID VARCHAR(15) = NULL,
	@p_AUTH_STATUS VARCHAR(1) = NULL,
	@p_CHECKER_ID varchar(15)  = NULL,
	@p_APPROVE_DT DATETIME = NULL,
	@p_ROLE_LOGIN VARCHAR(50) = NULL,
	@p_BRANCH_LOGIN VARCHAR(15),
	@p_PROCESS_DESC NVARCHAR(MAX)
	
AS

--SET @p_APPROVE_DT= CAST(@p_APPROVE_DT AS DATE)
	--Validation is here
DECLARE @ERRORSYS NVARCHAR(15) = '' 
  IF ( NOT EXISTS ( SELECT * FROM PL_REQUEST_DOC WHERE  REQ_ID = @p_REQ_ID))
	SET @ERRORSYS = 'REQ-00002'
IF @ERRORSYS <> '' 
BEGIN
   ROLLBACK TRANSACTION
	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
	RETURN '0'
END 

BEGIN TRANSACTION
		IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM PL_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' as Result,N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_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
	--UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)
	--WHERE REQ_DOC_ID = @p_REQ_ID AND FR_BRN_ID IN (SELECT BRANCH_ID FROM  CM_BRANCH_GETCHILDID(@p_BRANCH_LOGIN))

	 DECLARE
	@Result VARCHAR(5),
	@PROCESS_CURR VARCHAR(10),
	@STEP_CURR INT,
	@STEP_NEXT INT,
	@PROCESS_NEXT VARCHAR(10),
	@ROLE_USER_NOTIFI VARCHAR(50),
	@DEP_ID VARCHAR(15),
	@IS_LEAF VARCHAR(1),
	@NOTES NVARCHAR(500),
	@IS_NEXT BIT,@ROLE_USER VARCHAR(20),@ROLE_NEXT VARCHAR(20),@LIMTT_MAX  DECIMAL(18,2),@LIMIT_APP DECIMAL(18,2) ,@KHOI_ID_TF VARCHAR(20),@TOTAL_TRANSFER DECIMAL(18,2),@PROCESS_ID_NEXT VARCHAR(20),
	@TOTAL_AMT DECIMAL(18,2),
	@STEP_PARENT VARCHAR(20),
	@NOTES_CDT VARCHAR(20),
	@ROLE_CDT VARCHAR(20),
	@DVDM_CDT VARCHAR(20),
	@LIMIT_VALUE_CDT DECIMAL(18,2),
	@DVDM_ID_TT VARCHAR(20),
	@TOTAL_AMT_GD DECIMAL(18,2),
	@BRANCH_CREATE VARCHAR(15),
	@BRANCH_CREATE_TYPE VARCHAR(15),
	@DEP_CREATE VARCHAR(15),
	@BRANCH_PARENT VARCHAR(15)


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

	SET @PROCESS_CURR= (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C')
	SET @PROCESS_NEXT=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)

	
		
		SET @NOTES =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   
	DECLARE @DVDM_ID TABLE (
		DVDM_ID VARCHAR(15)
	)
DECLARE  @ERROR BIT ,@EROOR_DES NVARCHAR(500)

IF(@PROCESS_CURR LIKE '%_DC')
BEGIN
	SELECT @ERROR=ERROR,
       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDDC','PL_REQUEST_DOC',@p_CHECKER_ID,@PROCESS_CURR)
		IF(@ERROR=1)
		BEGIN
	 ROLLBACK TRANSACTION;
    SELECT '-1'  Result,
           N'LỖI 1' ErrorDesc
   
    RETURN '0';
	END
END
SELECT @ERROR=ERROR,
       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDTT','PL_REQUEST_DOC',@p_CHECKER_ID,@PROCESS_CURR)
		IF(@ERROR=1)
		BEGIN
	 ROLLBACK TRANSACTION;
    SELECT '-1'  Result,
           @EROOR_DES ErrorDesc
   
    RETURN '0';
END
	
	

	INSERT INTO @DVDM_ID
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN) GROUP BY DVDM_ID
	

	
			
	UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='P',NOTES=@NOTES+N' đã phê duyệt',CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT WHERE PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID AND ROLE_USER=@p_ROLE_LOGIN AND ( DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR DVDM_ID ='' OR DVDM_ID IS NULL)
	
	
	IF(@PROCESS_CURR <>'DVCM' AND @PROCESS_CURR <>'DVDM' AND @PROCESS_CURR <>'TC')
	BEGIN
		UPDATE dbo.PL_REQUEST_DOC_DT SET CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT WHERE REQ_ID=@p_REQ_ID AND  ( KHOI_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @p_ROLE_LOGIN='TGD' OR @p_ROLE_LOGIN='HDQT' OR @p_ROLE_LOGIN='GDDV' )
	END
	IF(@PROCESS_CURR LIKE '%_DC')
	BEGIN
		UPDATE dbo.PL_REQUEST_TRANSFER SET CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=@p_APPROVE_DT WHERE REQ_DOC_ID=@p_REQ_ID AND  ( FR_KHOI_ID IN (SELECT DVDM_ID FROM @DVDM_ID) OR @p_ROLE_LOGIN='TGD' OR @p_ROLE_LOGIN='HDQT')
	END
	
	INSERT INTO dbo.PL_PROCESS
			(
				REQ_ID,
				PROCESS_ID,
				CHECKER_ID,
				APPROVE_DT,
				PROCESS_DESC,NOTES
			)
			VALUES
			(   @p_REQ_ID,        -- REQ_ID - varchar(15)
				@PROCESS_CURR,        -- PROCESS_ID - varchar(10)
				@p_CHECKER_ID,        -- CHECKER_ID - varchar(15)
				GETDATE(), -- APPROVE_DT - datetime
				@p_PROCESS_DESC ,@NOTES+N' đã phê duyệt'       -- PROCESS_DESC - nvarchar(1000)
			)	
	
	
		


		SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID

	
		SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)


			
	SET @Result='1'
	IF(NOT EXISTS(SELECT PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@PROCESS_CURR AND [STATUS] <> 'P'))
	BEGIN	

			IF(@PROCESS_CURR LIKE '%_DC' AND @PROCESS_NEXT NOT LIKE '%_DC' AND @PROCESS_NEXT NOT LIKE 'TC')
			BEGIN
				

				SET @ROLE_USER=(SELECT TOP 1 ROLE_USER FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID=@PROCESS_CURR)
				IF(@ROLE_USER <> 'TGD')
				BEGIN
					
					
							SET @KHOI_ID_TF=(SELECT TOP 1 FR_KHOI_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID)
							SET @LIMIT_APP=(SELECT ISNULL(MAX_AMT,0)- ISNULL(TOTAL_APP_AMT,0) AS LIMIT_APP FROM dbo.LIMIT_ACCUMULATE WHERE ROLE_ID=@ROLE_USER AND DVDM_ID=@KHOI_ID_TF)
							SET @TOTAL_TRANSFER=(SELECT SUM(TOTAL_AMT) AS TOTAL FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID)

							IF(@TOTAL_TRANSFER>@LIMIT_APP)
							BEGIN
							DECLARE @LEVEL INT
								SELECT @LEVEL=BRANCH_ID FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='DCNS' AND ROLE_ID =@ROLE_USER
								SET @LEVEL=@LEVEL+1
								SELECT TOP 1 @ROLE_NEXT= ROLE_ID,@PROCESS_ID_NEXT=NOTES FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='DCNS' AND BRANCH_ID=@LEVEL 
								IF(@ROLE_NEXT='PTGD' AND EXISTS(SELECT * FROM dbo.CM_DVDM WHERE IS_KHOI=1 AND DVDM_ID=@KHOI_ID_TF AND IS_PTGD=0))
								BEGIN
								SELECT TOP 1 @ROLE_NEXT= ROLE_ID,@PROCESS_ID_NEXT=NOTES FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='DCNS' AND BRANCH_ID=@LEVEL 
							
								END
								SET @NOTES =(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_ID_NEXT LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   
								
								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,
								    DEP_ID
								)
								VALUES
								(   @p_REQ_ID,        -- REQ_ID - varchar(15)
								    @PROCESS_ID_NEXT,        -- PROCESS_ID - varchar(10)
								    'U',        -- STATUS - varchar(5)
								    @ROLE_NEXT,        -- ROLE_USER - varchar(50)
								    '',        -- BRANCH_ID - varchar(15)
								    '',        -- CHECKER_ID - varchar(15)
								    NULL, -- APPROVE_DT - datetime
								    @PROCESS_CURR,        -- PARENT_PROCESS_ID - varchar(10)
								    'N',        -- IS_LEAF - varchar(1)
								    '',        -- COST_ID - varchar(15)
								    @KHOI_ID_TF,        -- DVDM_ID - varchar(15)
								    N'Chờ '+@NOTES+' phê duyệt',       -- NOTES - nvarchar(500)
								    0,      -- IS_HAS_CHILD - bit
								    ''         -- DEP_ID - varchar(20)
								)
							UPDATE dbo.PL_REQUEST_PROCESS SET PARENT_PROCESS_ID=@PROCESS_ID_NEXT WHERE PROCESS_ID=@PROCESS_NEXT
							SET @PROCESS_NEXT=@PROCESS_ID_NEXT
							END
							ELSE
							BEGIN
							SET @STEP_PARENT=@PROCESS_CURR
							UPDATE dbo.LIMIT_ACCUMULATE SET TOTAL_APP_AMT = ISNULL(TOTAL_APP_AMT,0) + @TOTAL_TRANSFER WHERE ROLE_ID=@ROLE_USER AND DVDM_ID=@KHOI_ID_TF
						   -- INSERT PL_TRADE_DETAIL
						   SELECT TOP 1* FROM PL_TRADEDETAIL
				DECLARE @l_TRADE_ID VARCHAR(15)
				EXEC SYS_CodeMasters_Gen 'PL_TRADEDETAIL', @l_TRADE_ID out
				IF @l_TRADE_ID ='' OR @l_TRADE_ID IS NULL GOTO ABORT
				
				INSERT INTO PL_TRADEDETAIL
				(
					[TRADE_ID],
					[PLAN_ID],
					[GOODS_ID],
					[GOODS_NAME],
					[GOODS_TYPE],
					[UNIT_ID],
					M1,
					M2,
					M3,
					M4,
					M5,
					M6,
					M7,
					M8,
					M9,
					M10,
					M11,
					M12,
					[QUANTITY],
					[QUANTITY_EXE],
					[PRICE],
					[START_DT_AMORT],
					[MONTH_AMORT],
					[END_DT_AMORT],
					[RATE_AMORT],
					[NOTES],
					[RECORD_STATUS],
					[MAKER_ID],
					[CREATE_DT],
					[AUTH_STATUS],
					[CHECKER_ID],
					[APPROVE_DT],AMT_RECEIVE_TF
				)
				VALUES
				(
					@l_TRADE_ID,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					NULL,
					0,
					0,
					NULL,
					NULL,
					NULL,
					0,
					'',
					'1', 'admin' ,GETDATE() ,'A' ,'admin' ,GETDATE(),0
				)
						   ---
							BEGIN
									IF(@BRANCH_CREATE_TYPE='PGD')
									BEGIN
	
										SET @BRANCH_PARENT=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
										IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND BRANCH_ID=@BRANCH_PARENT AND (DEP_ID IS NULL OR DEP_ID='')))
										BEGIN
											INSERT INTO dbo.PL_REQUEST_PROCESS
											(
											REQ_ID,
											PROCESS_ID,
											STATUS,
											ROLE_USER,
											BRANCH_ID,
											DEP_ID,
											CHECKER_ID,
											APPROVE_DT,
											PARENT_PROCESS_ID,
											IS_LEAF,
											NOTES
											)
											VALUES
											(   
											@p_REQ_ID,               -- REQ_ID - varchar(15)
											'DVC',                  -- PROCESS_ID - varchar(10)
											'U',                     -- STATUS - varchar(5)
											'GDDV',                      -- ROLE_USER - varchar(50)
											@BRANCH_PARENT,
											NULL,                      -- BRANCH_ID - varchar(15)
											NULL,           -- CHECKER_ID - varchar(15)
											NULL , -- APPROVE_DT - datetime
											@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');

											SET @STEP_CURR = 'DVC';
											SET @STEP_PARENT = 'DVC';
										END
	
									END
									ELSE 
									IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV'
									AND ((
										BRANCH_ID=@BRANCH_CREATE 
										AND ((DEP_ID =@DEP_CREATE) OR ((@DEP_CREATE IS NULL OR @DEP_CREATE='') 
										AND (DEP_ID IS NULL OR DEP_ID='')))
										)
											OR EXISTS(SELECT PC.COST_ID FROM dbo.PL_COSTCENTER PC 
											LEFT JOIN dbo.PL_COSTCENTER_DT PCDT ON PCDT.COST_ID = PC.COST_ID WHERE PL_REQUEST_PROCESS.DVDM_ID=PC.DVDM_ID AND DEP_ID=@DEP_CREATE AND BRANCH_ID=@BRANCH_CREATE)
											)
									))
									BEGIN
									INSERT INTO dbo.PL_REQUEST_PROCESS
											(
											REQ_ID,
											PROCESS_ID,
											STATUS,
											ROLE_USER,
											BRANCH_ID,
											DEP_ID,
											CHECKER_ID,
											APPROVE_DT,
											PARENT_PROCESS_ID,
											IS_LEAF,
											NOTES
											)
											VALUES
											(   
											@p_REQ_ID,               -- REQ_ID - varchar(15)
											'DVC',                  -- PROCESS_ID - varchar(10)
											'U',                     -- STATUS - varchar(5)
											'GDDV',                      -- ROLE_USER - varchar(50)
											@BRANCH_CREATE,
											@DEP_CREATE,                      -- BRANCH_ID - varchar(15)
											NULL,           -- CHECKER_ID - varchar(15)
											NULL , -- APPROVE_DT - datetime
											@STEP_PARENT, 'N', N'Trưởng đơn vị phê duyệt');

									SET @STEP_CURR = 'DVC';
									SET @STEP_PARENT = 'DVC';
									END




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

								SET @TOTAL_AMT = (SELECT TOTAL_AMT FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)
								IF(@IS_NEXT=1 AND NOT EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='TGD'))
								BEGIN
								IF(EXISTS(SELECT * FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_REQ_ID AND KHOI_ID IS NOT NULL AND KHOI_ID <>''))
								BEGIN
								DECLARE lstCostCenter CURSOR FOR
								SELECT KHOI_ID  FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID AND KHOI_ID IS NOT NULL AND KHOI_ID <>''  AND NOT EXISTS(
									SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='GDK'
								)
								GROUP BY KHOI_ID
										 OPEN lstCostCenter
		 						FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
								WHILE @@FETCH_STATUS = 0 
								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)
										'GDK_TT',        -- PROCESS_ID - varchar(10)
										'U',        -- STATUS - varchar(5)
										'GDK',        -- ROLE_USER - varchar(50)
										'',        -- BRANCH_ID - varchar(15)
										'',        -- CHECKER_ID - varchar(15)
										NULL, -- APPROVE_DT - datetime
										@STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
										'N',        -- IS_LEAF - varchar(1)
										'',        -- COST_ID - varchar(15)
										@DVDM_ID_TT ,
										N'Chờ giám đốc khối xác nhận',
										0        -- DVDM_ID - varchar(15)
									  )
									  SET @STEP_PARENT='GDK_TT'	
								FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
								END
								CLOSE lstCostCenter
								DEALLOCATE lstCostCenter
		
								SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'GDK'))

								
								IF(@IS_NEXT=1)
								BEGIN
								IF(EXISTS(SELECT KHOI_ID  FROM dbo.PL_REQUEST_DOC_DT DT
								LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1 WHERE REQ_ID=@p_REQ_ID AND KHOI_ID IS NOT NULL AND KHOI_ID <>'' AND DM.IS_PTGD=1 AND NOT EXISTS(
									SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='PTGD'
								) ))
									BEGIN
									DECLARE lstCostCenter CURSOR FOR
									SELECT KHOI_ID  FROM dbo.PL_REQUEST_DOC_DT DT
									LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1 WHERE REQ_ID=@p_REQ_ID AND DM.IS_PTGD=1 AND NOT EXISTS(
										SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND DVDM_ID=KHOI_ID AND ROLE_USER='PTGD'
									)
									GROUP BY KHOI_ID
									OPEN lstCostCenter
		 							FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
									WHILE @@FETCH_STATUS = 0 
									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)
											'PTGDK_TT',        -- PROCESS_ID - varchar(10)
											'U',        -- STATUS - varchar(5)
											'PTGD',        -- ROLE_USER - varchar(50)
											'',        -- BRANCH_ID - varchar(15)
											'',        -- CHECKER_ID - varchar(15)
											NULL, -- APPROVE_DT - datetime
											@STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
											'N',        -- IS_LEAF - varchar(1)
											'',        -- COST_ID - varchar(15)
											@DVDM_ID_TT ,
											N'Chờ phó tổng giám đốc khối xác nhận',
											0        -- DVDM_ID - varchar(15)
										  )	
									  
									SET @STEP_PARENT='PTGDK_TT'
									FETCH NEXT FROM lstCostCenter INTO @DVDM_ID_TT
									END
									CLOSE lstCostCenter
									DEALLOCATE lstCostCenter
								
									SET @IS_NEXT= (SELECT dbo.FN_CHECK_LIMIT_PL_REQ(@p_REQ_ID,'PTGD'))
									END
								IF(EXISTS(SELECT KHOI_ID,TOTAL_AMT  FROM dbo.PL_REQUEST_DOC_DT DT
								LEFT JOIN dbo.CM_DVDM DM ON DM.DVDM_ID=DT.KHOI_ID AND DM.IS_KHOI=1 WHERE REQ_ID=@p_REQ_ID AND DM.IS_PTGD=0 ))
								BEGIN
								SET @IS_NEXT=1
								END
								

								IF(@IS_NEXT=1)
								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)
										'TGD',        -- PROCESS_ID - varchar(10)
										'U',        -- STATUS - varchar(5)
										'TGD',        -- ROLE_USER - varchar(50)
										'',        -- BRANCH_ID - varchar(15)
										'',        -- CHECKER_ID - varchar(15)
										NULL, -- APPROVE_DT - datetime
										@STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
										'N',        -- IS_LEAF - varchar(1)
										'',        -- COST_ID - varchar(15)
										'' ,
										N'Chờ tổng giám đốc xác nhận',
										0        -- DVDM_ID - varchar(15)
									  )
									  SET @STEP_PARENT='TGD'	
									  IF((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) > (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='TCTT-HDQT'))
			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)
                'HDQT',                               -- PROCESS_ID - varchar(10)
                'U',                                 -- STATUS - varchar(5)
                'HDQT',                               -- ROLE_USER - varchar(50)
                '',                                  -- BRANCH_ID - varchar(15)
                '',                                  -- CHECKER_ID - varchar(15)
                NULL,                                -- APPROVE_DT - datetime
                @STEP_PARENT,                        -- PARENT_PROCESS_ID - varchar(10)
                'N',                                 -- IS_LEAF - varchar(1)
                '',                                  -- COST_ID - varchar(15)
                '', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
                );
            SET @STEP_PARENT = 'HDQT';
			END
								END
				
						--ELSE
						--BEGIN
					
						--END
				END
								END
								END
								END

							UPDATE dbo.PL_REQUEST_PROCESS SET PARENT_PROCESS_ID=@STEP_PARENT WHERE PROCESS_ID=@PROCESS_NEXT
							SET @PROCESS_NEXT=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)
							END
				END
				ELSE
				BEGIN
							
							
							--- BO SUNG VAO BANG PL_TRADE_DETAIL
							SET @STEP_PARENT='TGD'	
							IF((SELECT SUM(TOTAL_AMT) FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID=@p_REQ_ID) > (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='TCTT-HDQT'))
							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)
                'HDQT',                               -- PROCESS_ID - varchar(10)
                'U',                                 -- STATUS - varchar(5)
                'HDQT',                               -- ROLE_USER - varchar(50)
                '',                                  -- BRANCH_ID - varchar(15)
                '',                                  -- CHECKER_ID - varchar(15)
                NULL,                                -- APPROVE_DT - datetime
                @STEP_PARENT,                        -- PARENT_PROCESS_ID - varchar(10)
                'N',                                 -- IS_LEAF - varchar(1)
                '',                                  -- COST_ID - varchar(15)
                '', N'Chờ HDQT xác nhận', 0 -- DVDM_ID - varchar(15)
                );
							SET @STEP_PARENT = 'HDQT';
							
								UPDATE dbo.PL_REQUEST_PROCESS SET PARENT_PROCESS_ID=@STEP_PARENT WHERE PROCESS_ID=@PROCESS_NEXT
								SET @PROCESS_NEXT=(SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)
							END
								
				
						--ELSE
						--BEGIN
					
						--END
							
								
								

						
				END

			END
			UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PARENT_PROCESS_ID=@PROCESS_CURR AND REQ_ID=@p_REQ_ID
			UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
				SET @IS_LEAF=(SELECT TOP 1 IS_LEAF FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)
			
			
			IF(@IS_LEAF='Y')
			BEGIN
					EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID 
					EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID

				SET @Result='0'
			END
	END


	IF @@Error <> 0 GOTO ABORT
			
COMMIT TRANSACTION
SELECT @Result as Result , @ROLE_USER_NOTIFI AS  ROLE_NOTIFI, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
	
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ROLE_NOTIFI ,'' ErrorDesc
		RETURN '-1'
End






GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[PL_REQ_PROCESS_CHILD_App]'
GO
ALTER PROCEDURE [dbo].[PL_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(20),
@p_REF_ID INT
AS
BEGIN TRANSACTION
---LUCTV KIEM TRA NEU TO TRINH DANG BI TRA VE THI KHONG DUOC PHEP DUYET
IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_ID AND STATUS_JOB ='R') OR (EXISTS(SELECT * FROM PL_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 as Result, N'Tờ trình chủ trương số: '+(SELECT REQ_CODE FROM PL_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  @ERROR BIT ,@EROOR_DES NVARCHAR(500)

SELECT @ERROR=ERROR,
       @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDDC','PL_REQUEST_DOC',@p_MAKER_ID,'DVKD')
IF(@ERROR=1)
BEGIN
	 ROLLBACK TRANSACTION;
    SELECT -1  Result,
           @EROOR_DES ErrorDesc
   
    RETURN 0;
END



DECLARE @LEVEL INT,@DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20),@DEP_CREATE VARCHAR(20)
DECLARE @ROLE_ID VARCHAR(20),@NOTES NVARCHAR(500),
				@PROCESS_CURR VARCHAR(10),
				@STEP_CURR INT,
				@STEP_NEXT INT,
				@PROCESS_NEXT VARCHAR(10),
				@TYPE_JOB_NAME NVARCHAR(100), @FULLNAME NVARCHAR(100)

SELECT @BRANCH_ID=  TLSUBBRID,@DEP_ID=SECUR_CODE ,@ROLE_ID=RoleName FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_ID


SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID


DECLARE
@COST_ID_TABLE TABLE (
	COST_ID VARCHAR(15)
)

DECLARE @DVDM_ID_TABLE TABLE (
	DVDM_ID VARCHAR(15)
)

	INSERT INTO @COST_ID_TABLE
	SELECT COST_ID FROM dbo.PL_COSTCENTER_DT WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@BRANCH_ID

	INSERT INTO @DVDM_ID_TABLE
	SELECT DVDM_ID FROM dbo.PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM @COST_ID_TABLE) GROUP BY DVDM_ID

	
			
		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 @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 ,
					@TYPE_JOB_NAME+ N' đã phê duyệt'       -- PROCESS_DESC - nvarchar(1000)
				)

		


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


				SET @PROCESS_CURR = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C')
				SET @PROCESS_NEXT = (SELECT TOP 1 PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR)
				IF(@PROCESS_CURR='TC')
				BEGIN
						SELECT @ERROR=ERROR,
						   @EROOR_DES=ERROR_DES FROM dbo.FN_CHECK_VALIDATE_APP(@p_REQ_ID,'PDDC','PL_REQUEST_DOC',@p_MAKER_ID,'TC')
					IF(@ERROR=1)
					BEGIN
						 ROLLBACK TRANSACTION;
						SELECT '-1'  Result,
							   @EROOR_DES ErrorDesc
   
						RETURN '0';
					END

						SET @NOTES=(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   
						UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',NOTES=@NOTES+ N' đã phê duyệt', CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE()  WHERE REQ_ID=@p_REQ_ID AND ROLE_USER=@ROLE_ID AND PROCESS_ID=@PROCESS_CURR
						UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='C' WHERE REQ_ID=@p_REQ_ID AND PARENT_PROCESS_ID=@PROCESS_CURR
						UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
				END
				ELSE
				BEGIN
				SET @NOTES=(SELECT CONTENT FROM dbo.CM_ALLCODE WHERE (@PROCESS_CURR LIKE CDVAL + '%') AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')   


				UPDATE dbo.PL_REQUEST_COSTCENTER SET AUTH_STATUS='A',APPROVE_DT=GETDATE(),CHECKER_ID=@p_MAKER_ID WHERE REQ_ID=@p_REQ_ID AND COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID_TABLE)
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',NOTES=@NOTES+ N' đã phê duyệt', CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE() WHERE REQ_ID=@p_REQ_ID AND ROLE_USER=@ROLE_ID AND DVDM_ID IN (SELECT DVDM_ID FROM @DVDM_ID_TABLE)
							


				IF(NOT EXISTS( SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID=@PROCESS_CURR 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.PL_REQUEST_DOC SET PROCESS_ID=@PROCESS_NEXT WHERE REQ_ID=@p_REQ_ID
						
				END

				UPDATE dbo.PL_REQUEST_TRANSFER SET AUTH_STATUS='A',CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE()  WHERE REQ_DOC_ID=@p_REQ_ID AND FR_BRN_ID=@BRANCH_ID AND (FR_DEP_ID=@DEP_ID OR FR_DEP_ID IS NULL OR FR_DEP_ID='')
				UPDATE dbo.PL_REQUEST_PROCESS SET STATUS='P',NOTES=@NOTES+ N' đã phê duyệt', CHECKER_ID=@p_MAKER_ID,APPROVE_DT=GETDATE()  WHERE REQ_ID=@p_REQ_ID AND ROLE_USER=@ROLE_ID AND BRANCH_ID=@BRANCH_ID AND (DEP_ID=@DEP_ID OR DEP_ID IS NULL OR DEP_ID='')


		IF(EXISTS(SELECT REQ_TRANSFER_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND (FR_BRN_ID <> @BRANCH_CREATE OR FR_DEP_ID<> @DEP_CREATE)))
			BEGIN
			IF(NOT EXISTS(SELECT REQ_COST_ID FROM dbo.PL_REQUEST_COSTCENTER WHERE REQ_ID=@p_REQ_ID AND AUTH_STATUS<>'A'))
			BEGIN
				    IF(NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND AUTH_STATUS <>'A'))
					BEGIN
							DECLARE 	@LIMIT_VALUE DECIMAL(18,0),@TOTAL_TRANSFER DECIMAL(18,2),
							@IS_NEXT BIT=0,@STEP_PARENT VARCHAR(20),@TOTAL_AMT_GD DECIMAL(12,0),@STOP BIT,@ROLE_TF VARCHAR(20),@DVDM_ID VARCHAR(20),@NOTE NVARCHAR(100)
							DECLARE @ROLE_CDT VARCHAR(20),@DVDM_CDT VARCHAR(20),@LIMIT_VALUE_CDT VARCHAR(20),@NOTES_CDT VARCHAR(20),@TOTAL_AMT DECIMAL(18,2)	
						

							SET @STEP_PARENT=(SELECT PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS WHERE PROCESS_ID='TC' AND REQ_ID=@p_REQ_ID)
						
							SET @NOTE=   (SELECT CONTENT FROM dbo.CM_ALLCODE WHERE CDVAL='DVDM' AND CDNAME='PROCESS_ID' AND CDTYPE='REQ')
							

							DECLARE lstTransfer CURSOR FOR
							SELECT FR_DVDM_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND   FR_DVDM_ID IS NOT NULL AND FR_DVDM_ID <>'' AND (FR_BRN_ID <> @BRANCH_CREATE OR FR_DEP_ID<> @DEP_CREATE)
							AND NOT EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND ROLE_USER='GDDV' AND DVDM_ID=FR_DVDM_ID	)
							GROUP BY FR_DVDM_ID
							OPEN lstTransfer
							FETCH NEXT FROM lstTransfer INTO @DVDM_ID
							WHILE @@FETCH_STATUS = 0 
							BEGIN 
							IF(NOT EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID  AND ROLE_USER='GDDV' AND ( DVDM_ID=@DVDM_ID OR @DVDM_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
								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)
									'DVDM_DC',        -- 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
									@STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
									'N',        -- IS_LEAF - varchar(1)
									'',        -- COST_ID - varchar(15)
									@DVDM_ID ,        -- DVDM_ID - varchar(15)
									N'Chờ '+@NOTE+N' xác nhận'
									,0)
							END

							FETCH NEXT FROM lstTransfer INTO @DVDM_ID
							END
							CLOSE lstTransfer
							DEALLOCATE lstTransfer


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

						UPDATE dbo.PL_REQUEST_PROCESS SET PARENT_PROCESS_ID=@STEP_PARENT,STATUS='U' WHERE  PROCESS_ID='TC' AND REQ_ID=@p_REQ_ID
						SET @STEP_PARENT='TC'

						---Duyệt DC

						IF( (SELECT COUNT(T.FR_KHOI_ID) AS COUNT_ROW FROM (SELECT FR_KHOI_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID AND FR_KHOI_ID IS NOT NULL AND FR_KHOI_ID <>''
						GROUP BY FR_KHOI_ID)T
						) > 1)
						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)
							    'TGD_DC',        -- PROCESS_ID - varchar(10)
							    'U',        -- STATUS - varchar(5)
							    'TGD',        -- ROLE_USER - varchar(50)
							    '',        -- BRANCH_ID - varchar(15)
							    '',        -- CHECKER_ID - varchar(15)
							    NULL, -- APPROVE_DT - datetime
							    @STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
							    '',        -- IS_LEAF - varchar(1)
							    '',        -- COST_ID - varchar(15)
							    '',        -- DVDM_ID - varchar(15)
							    N'Chờ tổng giám đốc phê duyệt',       -- NOTES - nvarchar(500)
							    NULL       -- IS_HAS_CHILD - bit
							    )
							SET	@STEP_PARENT='TGD_DC'
						END
						ELSE
						BEGIN
							DECLARE @LIMTT_MAX  DECIMAL(18,2),@LIMIT_APP DECIMAL(18,2) ,@KHOI_ID_TF VARCHAR(20)
							SET @KHOI_ID_TF=(SELECT TOP 1 FR_KHOI_ID FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID)


							IF(@KHOI_ID_TF IS NOT NULL AND @KHOI_ID_TF <>'')
							BEGIN
							SET @LIMIT_APP=(SELECT ISNULL(MAX_AMT,0)- ISNULL(TOTAL_APP_AMT,0) AS LIMIT_APP FROM dbo.LIMIT_ACCUMULATE WHERE ROLE_ID='GDK' AND DVDM_ID=@KHOI_ID_TF)
							SET @TOTAL_TRANSFER=(SELECT SUM(TOTAL_AMT) AS TOTAL FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID=@p_REQ_ID)
							SET @LIMTT_MAX=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='GDK' AND LIMIT_TYPE='DCNS')

							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_DC',        -- PROCESS_ID - varchar(10)
							    'U',        -- STATUS - varchar(5)
							    'GDK',        -- ROLE_USER - varchar(50)
							    '',        -- BRANCH_ID - varchar(15)
							    '',        -- CHECKER_ID - varchar(15)
							    GETDATE(), -- APPROVE_DT - datetime
							    @STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
							    'N',        -- IS_LEAF - varchar(1)
							    '',        -- COST_ID - varchar(15)
							    @KHOI_ID_TF,        -- DVDM_ID - varchar(15)
							    N'Chờ giám đốc khối xác nhận',       -- NOTES - nvarchar(500)
							    NULL       -- IS_HAS_CHILD - bit
							 )
							 SET @STEP_PARENT='GDK_DC';
							 IF(@TOTAL_TRANSFER>@LIMTT_MAX OR @TOTAL_TRANSFER>@LIMIT_APP)
							 BEGIN
								IF(EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=1))
									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)
							    'PTGD_DC',        -- PROCESS_ID - varchar(10)
							    'U',        -- STATUS - varchar(5)
							    'PTGD',        -- ROLE_USER - varchar(50)
							    '',        -- BRANCH_ID - varchar(15)
							    '',        -- CHECKER_ID - varchar(15)
							    GETDATE(), -- APPROVE_DT - datetime
							    @STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
							    'N',        -- IS_LEAF - varchar(1)
							    '',        -- COST_ID - varchar(15)
							    @KHOI_ID_TF,        -- DVDM_ID - varchar(15)
							    N'Chờ giám đốc khối xác nhận',       -- NOTES - nvarchar(500)
							    NULL       -- IS_HAS_CHILD - bit
							 )
									SET @STEP_PARENT='PTGD_DC'
									SET @LIMTT_MAX=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='GDK' AND LIMIT_TYPE='DCNS')
									SET @LIMIT_APP=(SELECT ISNULL(MAX_AMT,0)- ISNULL(TOTAL_APP_AMT,0) AS LIMIT_APP FROM dbo.LIMIT_ACCUMULATE WHERE ROLE_ID='GDK' AND DVDM_ID=@KHOI_ID_TF)
									
									END
									IF(@TOTAL_TRANSFER>@LIMTT_MAX OR @TOTAL_TRANSFER>@LIMIT_APP OR EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE DVDM_ID=@KHOI_ID_TF AND IS_KHOI=1 AND IS_PTGD=0))
									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)
										'TGD_DC',        -- PROCESS_ID - varchar(10)
										'U',        -- STATUS - varchar(5)
										'TGD',        -- ROLE_USER - varchar(50)
										'',        -- BRANCH_ID - varchar(15)
										'',        -- CHECKER_ID - varchar(15)
										NULL, -- APPROVE_DT - datetime
										@STEP_PARENT,        -- PARENT_PROCESS_ID - varchar(10)
										'',        -- IS_LEAF - varchar(1)
										'',        -- COST_ID - varchar(15)
										'',        -- DVDM_ID - varchar(15)
										N'Chờ tổng giám đốc phê duyệt',       -- NOTES - nvarchar(500)
										NULL       -- IS_HAS_CHILD - bit
									)
									SET	@STEP_PARENT='TGD_DC'
								 END
							
							 END
							 END
						END
						--- Duyệt TT
						
						UPDATE dbo.PL_REQUEST_PROCESS SET [STATUS]='C' WHERE PROCESS_ID='DVDM_DC' AND REQ_ID=@p_REQ_ID
						UPDATE dbo.PL_REQUEST_PROCESS SET PARENT_PROCESS_ID=@STEP_PARENT,STATUS='U' WHERE  PROCESS_ID='APPROVE' AND REQ_ID=@p_REQ_ID
						UPDATE dbo.PL_REQUEST_DOC SET PROCESS_ID='DVDM_DC' WHERE REQ_ID=@p_REQ_ID

				END	
			END
		END

			IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='APPROVE'))
			BEGIN
				
					
					EXEC dbo.PL_REQ_DOC_UPDATE_AFTER_APPROVE @p_REQ_ID = @p_REQ_ID 
					EXEC dbo.PL_REQ_DOC_Ins_To_TR_REQ_DOC @p_PL_REQ_ID = @p_REQ_ID
			END
	
				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






GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO