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

/*

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)