INSERT INTO PL_REQUEST_PROCESS VALUES ('PLRD00000202301','GDK_DC','C','GDK','DV0001','',NULL,'TC','N','','DM0000000000017',N'Chờ GĐK Tài Chính xác nhận','0','')
UPDATE PL_REQUEST_DOC SET PROCESS_ID ='GDK_DC' WHERE REQ_ID ='PLRD00000202301'
UPDATE PL_REQUEST_PROCESS SET PARENT_PROCESS_ID ='GDK_DC', STATUS ='U' WHERE ID ='29251'
--- 2021830
¿

ALTER PROCEDURE [dbo].[TR_PO_GOODS_Search_v2]
	@P_PO_TYPE INT = NULL,
	@p_GD_CODE	varchar(20)  = NULL,
	@p_GD_NAME	nvarchar(200)  = NULL,
	@p_GD_TYPE_ID	varchar(20)  = NULL,
	@p_GD_GROUP_ID VARCHAR(20) =NULL,
	@p_DESCRIPTION	nvarchar(2000)  = NULL,
	@p_SUP_ID	varchar(15)  = NULL,
	@p_PRICE	decimal(18)  = NULL,
	@p_UNIT_ID	varchar(15)  = NULL,	
	@P_PLAN_ID NVARCHAR(15) = NULL,
	@P_CONTRACT_ID NVARCHAR(15) = NULL,
	@P_CONTRACT_CODE NVARCHAR(15) = NULL,
	@P_CONTRACT_NAME NVARCHAR(100) = NULL,
	@p_TR_REQ_ID varchar(20)  = NULL,
	@P_TOP	INT = null
AS
BEGIN
	DECLARE @PLAN_CODE VARCHAR(15) = (SELECT PLAN_CODE FROM PL_MASTER WHERE PLAN_ID = @P_PLAN_ID)
	DECLARE @PLAN_NAME NVARCHAR(1000) = (SELECT PLAN_NAME FROM PL_MASTER WHERE PLAN_ID = @P_PLAN_ID)
	DECLARE @BRANCHCODE VARCHAR(15) = (SELECT BRANCH_CODE FROM PL_MASTER T1 INNER JOIN CM_BRANCH T2
	ON T1.BRANCH_ID = T2.BRANCH_ID WHERE T1.PLAN_ID = @P_PLAN_ID)
	DECLARE @BRANCHNAME NVARCHAR(1000) = (SELECT BRANCH_NAME FROM PL_MASTER T1 INNER JOIN CM_BRANCH T2
	ON T1.BRANCH_ID = T2.BRANCH_ID WHERE T1.PLAN_ID = @P_PLAN_ID)	
	DECLARE @BRANCH_ADDR NVARCHAR(1000) = (SELECT ADDR FROM PL_MASTER T1 INNER JOIN CM_BRANCH T2
	ON T1.BRANCH_ID = T2.BRANCH_ID WHERE T1.PLAN_ID = @P_PLAN_ID)
	DECLARE @BRANCHID VARCHAR(100) = (SELECT T1.BRANCH_ID FROM PL_MASTER T1 WHERE T1.PLAN_ID = @P_PLAN_ID)
	DECLARE	@l_CONTRACT_DESC NVARCHAR(max)='',
	@l_CONTRACT_ID VARCHAR(15)='', @l_CONTRACT_CODE VARCHAR(15)='', @l_CONTRACT_NAME NVARCHAR(200)='',
	@l_TRADE_ID VARCHAR(15)='', @l_QUANTITY numeric(18,0) = '1', @l_QUANTITY_EXE decimal(18,0), @l_QUANTITY_REMAIN decimal(18,0)

	SET @l_QUANTITY_EXE = 0
	SET @l_QUANTITY_REMAIN = 0
	--- IF TOP = 3 TIM KIEM HANG HOA PO - HOP DONG
	IF(@p_TR_REQ_ID IS NOT NULL AND @p_TR_REQ_ID <> '' )--Trong ke hoach - lAY TRONG PLAN_TRADEDETAIL
	BEGIN
				SELECT H.HH_ID AS [GD_ID],H.HH_CODE AS [GD_CODE], H.HH_NAME [GD_NAME], H.HH_TYPE_ID AS [GD_TYPE_ID],
				H.USE_BRANCH AS [USE_BRANCH],ISNULL(H.AMORT_RATE,0) AS [AMORT_RATE],X.DESCRIPTION AS [DESCRIPTION],
				H.SUP_ID AS [SUP_ID], X.REQDT_ID, X.QUANTITY AS QUANTITY,X.PRICE AS PRICE ,@l_CONTRACT_DESC AS [CONTRAC_DESC], '' TRADE_ID
				,F.PLAN_ID AS PLAN_ID,F.PLAN_CODE AS PLAN_CODE,F.PLAN_NAME AS PLAN_NAME,D.UNIT_ID AS UNIT_ID, D.UNIT_CODE AS UNIT_CODE, D.UNIT_NAME AS UNIT_NAME,
				@l_CONTRACT_ID AS CONTRACT_ID,@l_CONTRACT_CODE AS CONTRACT_CODE, HH.HH_NAME AS GD_NAME_REAL,
				@l_CONTRACT_NAME AS [CONTRACT_NAME]
				, 0.0 AS QUANTITY_EXE, 0.0 AS QUANTITY_REMAIN, --THIEUVQ THEM 22/09/2014
				'' AS CD_ID, --THIEUVQ LAY CHI TIET HOP DONG 14092016
				E.BRANCH_ID AS BRANCH_ID,E.BRANCH_CODE AS BRANCH_CODE, E.BRANCH_NAME AS BRANCH_NAME, @BRANCH_ADDR AS R_ADDR, TY.HH_TYPE_CODE, TY.HH_TYPE_NAME,HG.HH_GROUP_ID,HG.HH_GROUP_CODE,HG.HH_GROUP_NAME
			
				FROM TR_REQUEST_DOC_DT X
				--LEFT JOIN CM_GOODS A   ON A.GD_ID = B.GOODS_ID
				
				--LEFT JOIN PL_REQUEST_DOC D ON X.PL_REQDT_ID
				LEFT JOIN PL_REQUEST_DOC_DT PT ON X.PL_REQDT_ID= PT.REQDT_ID
				LEFT JOIN TR_REQUEST_DOC_PL_DT PLDT ON PLDT.REQPL_DT_ID = X.REQPL_DT_ID
				LEFT JOIN CM_HANGHOA HH ON HH.HH_ID = PLDT.HH_ID
				LEFT JOIN PL_TRADEDETAIL B ON	PT.TRADE_ID = B.TRADE_ID	
				LEFT JOIN PL_MASTER F ON F.PLAN_ID = B.PLAN_ID
				LEFT JOIN CM_BRANCH E ON E.BRANCH_ID =  F.BRANCH_ID
				LEFT JOIN CM_HANGHOA H ON H.HH_ID = X.HANGHOA_ID	
				LEFT JOIN CM_HANGHOA_TYPE TY ON H.HH_TYPE_ID = TY.HH_TYPE_ID
				LEFT JOIN dbo.CM_HANGHOA_GROUP HG ON HG.HH_GROUP_ID=TY.HH_GROUP_ID	
				LEFT JOIN CM_UNIT D ON H.UNIT_ID = D.UNIT_ID	
				WHERE 1 = 1
				AND (H.HH_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
				AND (H.HH_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
				AND (H.HH_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
				AND (X.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
				AND (HG.HH_GROUP_ID LIKE '%' + @p_GD_GROUP_ID + '%' OR @p_GD_GROUP_ID IS NULL OR @p_GD_GROUP_ID = '')
				AND (X.REQ_DOC_ID = @p_TR_REQ_ID OR @p_TR_REQ_ID IS NULL OR @p_TR_REQ_ID = '')
				AND (X.TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK') OR X.TRAN_TYPE_ID IS NULL OR X.TRAN_TYPE_ID='')
			    --AND (H.RECORD_STATUS ='1' AND H.AUTH_STATUS ='A')
				--AND (B.PLAN_ID = @P_PLAN_ID OR @P_PLAN_ID IS NULL OR @P_PLAN_ID = '')
				RETURN
	END
	IF(@P_CONTRACT_ID IS NOT NULL AND @P_CONTRACT_ID <>'')
	BEGIN
		SELECT H.HH_ID AS [GD_ID],H.HH_CODE AS [GD_CODE], H.HH_NAME [GD_NAME], H.HH_TYPE_ID AS [GD_TYPE_ID],
				H.USE_BRANCH AS [USE_BRANCH],ISNULL(H.AMORT_RATE,0) AS [AMORT_RATE],H.DESCRIPTION AS [DESCRIPTION],
				H.SUP_ID AS [SUP_ID],X.QUANTITY AS QUANTITY,X.PRICE AS PRICE ,@l_CONTRACT_DESC AS [CONTRAC_DESC], '' TRADE_ID
				,'' AS PLAN_ID,'' AS PLAN_CODE,'' AS PLAN_NAME,D.UNIT_ID AS UNIT_ID, D.UNIT_CODE AS UNIT_CODE, D.UNIT_NAME AS UNIT_NAME,
				@l_CONTRACT_ID AS CONTRACT_ID,@l_CONTRACT_CODE AS CONTRACT_CODE, 
				@l_CONTRACT_NAME AS [CONTRACT_NAME]
				, 0.0 AS QUANTITY_EXE, 0.0 AS QUANTITY_REMAIN, --THIEUVQ THEM 22/09/2014
				'' AS CD_ID, --THIEUVQ LAY CHI TIET HOP DONG 14092016
				'' AS BRANCH_ID,'' AS BRANCH_CODE, '' AS BRANCH_NAME, @BRANCH_ADDR AS R_ADDR, TY.HH_TYPE_CODE, TY.HH_TYPE_NAME
					,HG.HH_GROUP_ID,HG.HH_GROUP_CODE,HG.HH_GROUP_NAME
				--E.BRANCH_ID, E.BRANCH_CODE, E.BRANCH_NAME, @BRANCH_ADDR AS R_ADDR
				FROM dbo.TR_CONTRACT_DT X
				LEFT JOIN CM_HANGHOA H ON H.HH_ID = X.GOODS_ID	
				LEFT JOIN CM_HANGHOA_TYPE TY ON H.HH_TYPE_ID = TY.HH_TYPE_ID
				LEFT JOIN dbo.CM_HANGHOA_GROUP HG ON HG.HH_GROUP_ID=TY.HH_GROUP_ID
					LEFT JOIN CM_UNIT D ON H.UNIT_ID = D.UNIT_ID
				WHERE 1 = 1
				AND (H.HH_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
				AND (H.HH_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
				AND (H.HH_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
				--AND (X LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
						AND (HG.HH_GROUP_ID LIKE '%' + @p_GD_GROUP_ID + '%' OR @p_GD_GROUP_ID IS NULL OR @p_GD_GROUP_ID = '')
				AND (X.CONTRACT_ID = @P_CONTRACT_ID OR @P_CONTRACT_ID IS NULL OR @P_CONTRACT_ID = '')
				AND (H.RECORD_STATUS ='1' AND H.AUTH_STATUS ='A')
				--AND (B.PLAN_ID = @P_PLAN_ID OR @P_PLAN_ID IS NULL OR @P_PLAN_ID = '')
				RETURN
	END
	ELSE IF(@p_TR_REQ_ID IS  NULL OR @p_TR_REQ_ID = '' )--Trong ke hoach - lAY TRONG PLAN_TRADEDETAIL
	BEGIN
				SELECT H.HH_ID AS [GD_ID],H.HH_CODE AS [GD_CODE], H.HH_NAME [GD_NAME], H.HH_TYPE_ID AS [GD_TYPE_ID],
				H.USE_BRANCH AS [USE_BRANCH],ISNULL(H.AMORT_RATE,0) AS [AMORT_RATE],H.DESCRIPTION AS [DESCRIPTION],
				H.SUP_ID AS [SUP_ID],0 AS QUANTITY,0 AS PRICE ,@l_CONTRACT_DESC AS [CONTRAC_DESC], '' TRADE_ID
				,'' AS PLAN_ID,'' AS PLAN_CODE,''AS PLAN_NAME,D.UNIT_ID AS UNIT_ID, D.UNIT_CODE AS UNIT_CODE, D.UNIT_NAME AS UNIT_NAME,
				@l_CONTRACT_ID AS CONTRACT_ID,@l_CONTRACT_CODE AS CONTRACT_CODE, 
				@l_CONTRACT_NAME AS [CONTRACT_NAME]
				, 0.0 AS QUANTITY_EXE, 0.0 AS QUANTITY_REMAIN, --THIEUVQ THEM 22/09/2014
				'' AS CD_ID, --THIEUVQ LAY CHI TIET HOP DONG 14092016
				'' AS BRANCH_ID,'' AS BRANCH_CODE,'' AS BRANCH_NAME, @BRANCH_ADDR AS R_ADDR, TY.HH_TYPE_CODE, TY.HH_TYPE_NAME
					,HG.HH_GROUP_ID,HG.HH_GROUP_CODE,HG.HH_GROUP_NAME
				--E.BRANCH_ID, E.BRANCH_CODE, E.BRANCH_NAME, @BRANCH_ADDR AS R_ADDR
				FROM  CM_HANGHOA H 	
				LEFT JOIN CM_HANGHOA_TYPE TY ON H.HH_TYPE_ID = TY.HH_TYPE_ID	
					LEFT JOIN dbo.CM_HANGHOA_GROUP HG ON HG.HH_GROUP_ID=TY.HH_GROUP_ID				
				LEFT JOIN CM_UNIT D ON H.UNIT_ID = D.UNIT_ID
			WHERE 1 = 1
				AND (H.HH_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
				AND (H.HH_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
				AND (H.HH_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
				AND (H.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
				AND (HG.HH_GROUP_ID LIKE '%' + @p_GD_GROUP_ID + '%' OR @p_GD_GROUP_ID IS NULL OR @p_GD_GROUP_ID = '')
				AND (H.RECORD_STATUS ='1' AND H.AUTH_STATUS ='A')
				--AND (B.PLAN_ID = @P_PLAN_ID OR @P_PLAN_ID IS NULL OR @P_PLAN_ID = '')
				RETURN
	END
	
END
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s