ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_SendApp]
@p_REQ_ID VARCHAR(20),
@p_PROCESS_ID VARCHAR(20),
@p_TLNAME VARCHAR(20),
@p_MAKER_ID VARCHAR(20)
AS
BEGIN TRANSACTION
	IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE  REQ_ID=@p_REQ_ID AND  (REQ_DT IS NULL OR REQ_DT='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Ngày yêu cầu bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE  REQ_ID=@p_REQ_ID AND  (REQ_REASON IS NULL OR REQ_REASON='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Mục đích yêu cầu bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END
	IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE  REQ_ID=@p_REQ_ID AND  (PL_REQ_ID IS NULL OR PL_REQ_ID='')) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Tờ trình chủ trương bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END
	IF(NOT EXISTS(SELECT REQ_DOC_ID FROM dbo.TR_REQUEST_DOC_DT WHERE  REQ_DOC_ID=@p_REQ_ID ) )
	BEGIN
			ROLLBACK TRANSACTION
			SELECT '-1' AS Result ,'' REQ_CODE, ''  REQ_ID, N'Chi tiết hàng hóa bắt buộc nhập' ErrorDesc
			RETURN '-1'
	END

	--- XOA DATA CU
	DELETE PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID
	-----
	DECLARE @NS_TO_TRINH DECIMAL(18,0), @NS_PYCMS_USE DECIMAL(18,0), @NS_LUY_KE DECIMAL(18,0),@PL_REQ_CODE VARCHAR(50)
	SET @PL_REQ_CODE =(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))
	SET @NS_TO_TRINH =(SELECT TOTAL_AMT FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))
	SET @NS_PYCMS_USE =(SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID)
	SET @NS_LUY_KE =ISNULL((SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID))),0)
	DECLARE @p_PL_REQ_ID VARCHAR(15),@p_REQ_DATE DATETIME
	SELECT @p_PL_REQ_ID=PL_REQ_ID,@p_REQ_DATE=REQ_DT  FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID
	IF( EXISTS(
			SELECT Temp.GD_ID FROM (SELECT GD_ID,SUM(TOTAL_AMT) AS TOTAL_AMT_ETM FROM dbo.TR_REQUEST_DOC_DT   WHERE REQ_DOC_ID=@p_REQ_ID
			GROUP BY GD_ID) Temp WHERE    TOTAL_AMT_ETM > 
			((SELECT SUM(TEMP2.TOTAL_AMT) AS TOTAL_AMT_APP FROM
			(SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT
			WHERE REQ_ID=@p_PL_REQ_ID
			UNION 
			SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT
			WHERE REQ_ID=(SELECT REQ_PARENT_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID) ) TEMP2 WHERE TEMP2.GOODS_ID=Temp.GD_ID
			GROUP BY TEMP2.GOODS_ID) - (SELECT ISNULL(SUM(TOTAL_AMT),0) AS TOTAL_AMT_EXE FROM dbo.TR_REQUEST_DOC_DT WHERE GD_ID=Temp.GD_ID AND REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=@p_PL_REQ_ID)
			GROUP BY GD_ID))))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID,
			N'Thông tin liên quan tới phiếu yêu cầu mua sắm có sử dụng tờ trình số: '+ @PL_REQ_CODE
			+CHAR(10)+ N'Tổng số tiền lũy kế sử dụng ngân sách tờ trình: '+FORMAT(@NS_LUY_KE ,'#,#', 'vi-VN')
			+CHAR(10)+N'Tổng số tiền ngân sách còn lại của tờ trình: '+ FORMAT(@NS_TO_TRINH -@NS_LUY_KE ,'#,#', 'vi-VN')
			+CHAR(10)+ N'Số tiền sử dụng ngân sách cho phiếu yêu cầu mua sắm hiện tại vượt '+FORMAT(@NS_LUY_KE+@NS_PYCMS_USE -@NS_TO_TRINH ,'#,#', 'vi-VN')+N' so với tổng số tiền ngân sách còn lại của tờ trình.'
			+CHAR(10) +N'Vui lòng tra cứu tổng số tiền các phiếu yêu cầu mua sắm để biết thêm thông tin chi tiêt!' ErrorDesc 
			RETURN '0'
		END
	IF(EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE))
		BEGIN
			ROLLBACK TRANSACTION
			SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Lưới thông tin hàng hóa theo tờ trình: Ngày cần không được phép để trống và phải lớn hơn ngày yêu cầu mua sắm' ErrorDesc 
			RETURN '0'
		END
	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_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID

	SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID)
	SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE)
	-- KIEM TRA XEM CO CAP PHE DUYET HAY KHONG
	IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <>''))
	BEGIN
		UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='SIGN' WHERE REQ_ID=@p_REQ_ID
		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)
		    'SIGN',        -- PROCESS_ID - varchar(10)
		    'C',        -- STATUS - varchar(5)
		    (SELECT RoleName FROM TL_USER WHERE TLNANME =(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)),  -- ROLE_USER - varchar(50)
		    @BRANCH_CREATE,  
			@DEP_CREATE,      -- 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ờ cấp phê duyệt trung gian xác nhận phiếu',       -- NOTES - nvarchar(500)
		    NULL       -- IS_HAS_CHILD - bit
		 )
	END
	ELSE
	BEGIN
	UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='APPNEW' WHERE REQ_ID=@p_REQ_ID
	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,  
			@DEP_CREATE,      -- 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
		 )
	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)
					    --@p_PROCESS_ID,        -- PROCESS_ID - varchar(10)
						'SEND',
					    @p_TLNAME,        -- CHECKER_ID - varchar(15)
					    GETDATE(), -- APPROVE_DT - datetime
					    N'Nhân viên tạo phiếu và gửi phê duyệt thành công' ,       -- PROCESS_DESC - nvarchar(1000)
					    N'Nhân viên gửi phê duyệt '        -- NOTES - nvarchar(1000)
					 )
	
		IF @@Error <> 0 GOTO ABORT
COMMIT TRANSACTION
SELECT '0' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc
		RETURN '-1'
End


¿
ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Search]
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_DT VARCHAR(20)= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@P_REQ_ENTRIES nvarchar(MAX)	= NULL,
@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_RECEIVER_PO	nvarchar(250)	= NULL,
@p_RECEIVER_DEBIT	nvarchar(250)	= NULL,
@p_REQ_PAY_TYPE	varchar(15)	= NULL,
@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,
@p_REQ_AMT	decimal(18, 0)	= NULL,
@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,
@p_MAKER_ID	varchar(15)	= NULL,
@p_CREATE_DT	varchar(25)	= NULL,
@p_EDITOR_ID	varchar(15)	= NULL,
@p_AUTH_STATUS	varchar(1)	= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_APPROVE_DT	varchar(25)	= NULL,
@p_CREATE_DT_KT	varchar(25)	= NULL,
@p_MAKER_ID_KT	varchar(15)	= NULL,
@p_AUTH_STATUS_KT	varchar(1)	= NULL,
@p_CHECKER_ID_KT	varchar(1)	= NULL,
@p_APPROVE_DT_KT  varchar(25)= null,
@p_CORE_NOTE	nvarchar(500)	= NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_NOTES	varchar(15)	= NULL,
@p_RECORD_STATUS	varchar(1)	= NULL,
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_PROCESS	varchar(15)	= NULL,
@p_PAY_PHASE VARCHAR(15)= NULL,
@p_XMP_TEMP XML = NULL,
@p_TOP INT = 10,
@p_LEVEL varchar(10) = NULL,
@p_FRMDATE VARCHAR(20)= NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
@p_IS_UPDATE_KT VARCHAR(15) = NULL,
@p_IS_TRANSFER VARCHAR(15)= NULL,
@p_DVDM_ID VARCHAR(15)= NULL,
@p_USER_LOGIN VARCHAR(15) = NULL,
@p_RATE DECIMAL(18,0) =0,
@p_FUNCTION VARCHAR(15) = NULL,
@p_TYPE_SEARCH VARCHAR(15) = NULL
AS
SET @p_TOP = NULL
DECLARE @TB_TYPE TABLE (BRANCH_TYPE VARCHAR(15))
IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_LOGIN)='HS')
BEGIN
	INSERT INTO @TB_TYPE VALUES('HS')
END
ELSE
BEGIN
	INSERT INTO @TB_TYPE VALUES('PGD')
	INSERT INTO @TB_TYPE VALUES('CN')
END
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
declare @tmp_Login table(BRANCH_ID varchar(15))
insert into @tmp_Login  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
DECLARE @ROLE_ID VARCHAR(20) , @DEP_ID_LG VARCHAR(15) = NULL, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15)
DECLARE @BRANCH_TYPE VARCHAR(15)
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
DECLARE @TMP_DEP TABLE(DEP_ID VARCHAR(15))
SET @COST_LG =(SELECT TOP 1 COST_ID FROM PL_COSTCENTER_DT WHERE DEP_ID =@DEP_ID_LG)
SET @DVDM_ID =(SELECT TOP 1 DVDM_ID FROM PL_COSTCENTER WHERE COST_ID =@COST_LG)
INSERT INTO @TMP_DEP SELECT B.DEP_ID FROM PL_COSTCENTER_DT B  WHERE COST_ID =@COST_LG
DECLARE @BRANCH_TYPE_LG VARCHAR(15)
SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
IF(@p_TOP=NULL OR @p_TOP='' OR @p_TOP=0)BEGIN
		SELECT A.*,
		PR.PROCESS_DESC AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
		BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE,  AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME,
		A.REQ_AMT AS TOTAL_AMT_TEMP,CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE  ISNULL(H.SOTIEN_TT,0) END AS TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE,
		TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,
		PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME, PM.PAY_PHASE AS KY_TAM_UNG,
		DV.DVDM_CODE, DV.DVDM_NAME, PC.ROLE_USER AS ROLE_CONF, PR.PROCESS_DESC AS CONF_STATUS, PC1.NOTES AS NEXT_STEP, PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,
		PC3.TLNAME AS EXEC_USER, TL8.TLFullName AS RECIVER_MONEY_FULLNAME, BR1.BRANCH_TYPE AS BRANCH_TYPE_CR, BR.BRANCH_TYPE, @p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE
        FROM TR_REQ_ADVANCE_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'--Loại thanh toán (Nội bộ/Thanh toán/Nợ)
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'--Hình thức thanh toán( Tiền mặt/Chuyển khoản)			 
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			 LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'
			 LEFT JOIN
			 (
			 SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID = H.PAY_ADV_ID
			 LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'
			 LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID
			 LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID
			 LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C'
			 LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			 LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME
			 LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
        WHERE 1=1 
			 AND (A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID IS NULL OR @p_REQ_PAY_ID ='')
			  AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
			  AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
			  AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='')
			  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
			  --Luanlt-2019/10/14-Thêm Filter  REQ_TYPE,FROMDATE TODATE REF_ID
			  AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
			  AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='')
              AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='')
			  --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME  BRANCH_ID DEP_ID KT_AUTH
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
			  AND(A.BRANCH_CREATE = @p_BRANCH_CREATE or @p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)
			  AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND(A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL)
			  AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
			  AND((A.TRANSFER_MAKER IS NOT NULL AND @p_IS_TRANSFER='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              OR((A.TRANSFER_MAKER IS NULL AND @p_IS_TRANSFER='N'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
			  AND((@p_LEVEL='ALL' AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp))
              OR((@p_LEVEL='UNIT' AND A.BRANCH_ID=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))
			  AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login))
              OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
			  --- KIEM TRA NEU LA MAN HINH TIM KIEM PHIEU TAM UNG DE HOAN TAM UNG THI CHI LAY NHUNG PHIEU TAM UNG CO SO TIEN TAM UNG CON LAI =0
			  AND(((@p_PROCESS='PM' AND  (A.REQ_AMT- ISNULL(H.SOTIEN_TT,0)) >0) AND A.REQ_TYPE='I') OR @p_PROCESS IS NULL OR @p_PROCESS ='')
			  AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='')	  
			  AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
			  AND (
			    (A.REQ_TYPE IN ('I','P','D')
				AND (@p_TYPE_SEARCH='HC' AND EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN AND BRANCH_TYPE ='HS') 
										  AND ( (A.PROCESS IS NOT NULL AND A.PROCESS <> '' AND (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =A.MAKER_ID) = (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN))
										   OR(A.MAKER_ID =@P_USER_LOGIN OR A.TRASFER_USER_RECIVE =@p_USER_LOGIN))
				OR (@p_TYPE_SEARCH='HC' AND NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN AND BRANCH_TYPE ='HS')
										  AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
				OR (@p_TYPE_SEARCH ='HC' AND (A.DVDM_ID IS NOT NULL OR A.DVDM_ID <>'' AND A.DVDM_ID =@DVDM_ID AND A.PROCESS IS NOT NULL AND A.PROCESS <>'') OR (A.DEP_ID IN (SELECT * FROM @TMP_DEP) AND A.PROCESS IS NOT NULL AND A.PROCESS <>''))
				OR (@p_TYPE_SEARCH ='HC' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER ='TGD' AND STATUS ='C'))
				-- HOAC NGUOC LAI NEU HC NHUNG PHIEU DUOC TAO O PGD, CN
				OR (@p_TYPE_SEARCH='KT' AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)))
				)
				OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH ='')
		ORDER BY A.CREATE_DT DESC;
    END;
    ELSE BEGIN
        SELECT A.*,ISNULL(B.AUTH_STATUS_NAME,N'Chờ duyệt') AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
		BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME AS DEP_NAME, DP.DEP_CODE AS DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, 
		A.REQ_AMT AS TOTAL_AMT_TEMP,CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE  ISNULL(H.SOTIEN_TT,0) END AS TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
		TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE,
		PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME,PM.PAY_PHASE AS KY_TAM_UNG,
		DV.DVDM_CODE, DV.DVDM_NAME, PC.ROLE_USER AS ROLE_CONF, PR.PROCESS_DESC +N' vào lúc '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103))  AS CONF_STATUS, PC1.NOTES AS NEXT_STEP,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,PC3.TLNAME AS EXEC_USER,TL8.TLFullName AS RECIVER_MONEY_FULLNAME, BR1.BRANCH_TYPE AS BRANCH_TYPE_CR, BR.BRANCH_TYPE,@p_TYPE_SEARCH AS TYPE_SEARCH,
		SR.ROLE_ID AS ROLE_ID_CRE
        FROM TR_REQ_ADVANCE_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON A.DEP_ID= DP.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D'
			 LEFT JOIN
			 (
			 SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID = H.PAY_ADV_ID
			 LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I'
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D'
			 LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID
			 --LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID
			 LEFT JOIN 
			 (
				SELECT P.REQ_ID, P.PROCESS_DESC, P.APPROVE_DT AS APP_DT FROM PL_PROCESS P
				INNER JOIN 
				(
					SELECT MAX (PR.ID) AS ID, MAX(PR.REQ_ID) AS REQ_ID FROM PL_PROCESS PR
					GROUP BY PR.REQ_ID
				) PR ON PR.REQ_ID = P.REQ_ID AND P.ID = PR.ID
			 ) AS PR ON A.REQ_PAY_ID = PR.REQ_ID
			 LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C'
			 LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			 LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME	
			 LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
        WHERE 1=1 
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' OR @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
			  AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
			  AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
			 OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='')
			  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
			  --Luanlt-2019/10/14-Thêm Filter  REQ_TYPE,FROMDATE TODATE REF_ID
			  AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
			  AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='')
              AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='')
			  --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME  BRANCH_ID DEP_ID KT_AUTH
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  --AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
			  AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND((A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL) OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S')))
			  AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
			  AND(( @p_IS_TRANSFER='Y' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID)) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              OR((@p_IS_TRANSFER='N' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID)))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
			  AND(((@p_PROCESS='PM' AND  (A.REQ_AMT- ISNULL(H.SOTIEN_TT,0)) >0)  AND A.REQ_TYPE='I') OR @p_PROCESS IS NULL OR @p_PROCESS ='')	
			  AND((@p_LEVEL='ALL' AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp))
              OR((@p_LEVEL='UNIT' AND A.BRANCH_ID=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))

				AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login))
              OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
			  --AND ((@p_TRASFER_USER_RECIVE IS NOT NULL AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME = @p_TRASFER_USER_RECIVE ))
			  --OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')	
			  AND ((@p_FUNCTION ='KT' AND EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND TLNAME= @p_USER_LOGIN))
					OR @p_FUNCTION IS NULL OR @p_FUNCTION ='' OR @p_FUNCTION ='TF')	  
			  AND(A.MAKER_ID LIKE '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID ='')
			  --AND(A.MAKER_ID_KT LIKE '%'+@p_TRASFER_USER_RECIVE+'%' OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
			  --AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
			 AND(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME =@p_TRASFER_USER_RECIVE AND TYPE_JOB='XL' AND REQ_ID = A.REQ_PAY_ID) OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE='')
			  AND
			  ( 
			  A.MAKER_ID =@p_USER_LOGIN OR (A.TRASFER_USER_RECIVE = @p_USER_LOGIN AND A.AUTH_STATUS NOT IN ('E','R'))
			  OR(
			  A.AUTH_STATUS <>'E' AND((@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE ='I' 
										AND (A.TRASFER_USER_RECIVE = @p_USER_LOGIN 
										OR (@ROLE_ID = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID AND ROLE_USER =@ROLE_ID)
										OR (A.DVDM_ID IS NULL AND A.DEP_ID =@DEP_ID_LG))
										))
			  OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE <> 'I' AND 
			  (A.TRASFER_USER_RECIVE = @p_USER_LOGIN OR (@ROLE_ID IN ('GDDV','GDK','KTT','TPTC') AND A.BRANCH_ID =@p_BRANCH_LOGIN AND A.DEP_ID =@DEP_ID_LG 
																					AND( A.TRASFER_USER_RECIVE IS NULL OR A.TRASFER_USER_RECIVE ='' OR (A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>'')))))
			  OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND (@ROLE_ID = 'GDDV' AND 
			  ((A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>'') OR A.TRASFER_USER_RECIVE ='' OR A.TRASFER_USER_RECIVE IS NULL)
			  AND ((A.BRANCH_CREATE <> @p_BRANCH_LOGIN AND A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'') OR A.BRANCH_CREATE = @p_BRANCH_LOGIN)
			  AND A.BRANCH_ID IN (SELECT * FROM @tmp)))
			  OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'PGD' AND (@ROLE_ID IN ('GDDV','TPGD') AND A.BRANCH_ID =@p_BRANCH_LOGIN))
			  OR(@p_TYPE_SEARCH ='HC' AND (A.DVDM_ID IS NOT NULL AND A.DVDM_ID <>'' AND A.DVDM_ID =@DVDM_ID AND A.PROCESS IS NOT NULL AND A.PROCESS <>'' AND @ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID))
			  OR(A.DEP_ID IN (SELECT * FROM @TMP_DEP) AND A.PROCESS IS NOT NULL AND A.PROCESS <>'' AND @ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID)))
			  OR(@p_TYPE_SEARCH ='HC' AND (@ROLE_ID = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER  IN ('TGD','HDQT') AND STATUS ='C')))
			  OR(@p_TYPE_SEARCH='KT' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
			  OR(@p_TYPE_SEARCH='PAY' AND (A.PAY_AMT -A.REQ_AMT) >=0)
			  )))	
		ORDER BY A.CREATE_DT DESC;
    END;


¿
ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_Search]
@p_REQ_PAY_ID	varchar(15)= NULL,
@p_REQ_PAY_CODE	varchar(50)	= NULL,
@p_REQ_DT VARCHAR(20)= NULL,
@p_BRANCH_ID	varchar(15)	= NULL,
@p_DEP_ID	varchar(15)	= NULL,
@p_REQ_REASON	nvarchar(MAX)	= NULL,
@p_REQ_TYPE	varchar(15)	= NULL,
@P_REQ_ENTRIES nvarchar(MAX)	= NULL,
@p_REQ_DESCRIPTION	nvarchar(MAX)	= NULL,
@p_REF_ID	varchar(15)	= NULL,
@p_RECEIVER_PO	nvarchar(250)	= NULL,
@p_REQ_PAY_TYPE	varchar(15)	= NULL,
@p_REQ_TYPE_CURRENCY	nvarchar(50)	= NULL,
@p_REQ_AMT	decimal(18, 0)	= NULL,
@p_REQ_TEMP_AMT	decimal(18, 0)	= NULL,
@p_MAKER_ID	varchar(15)	= NULL,
@p_CREATE_DT	varchar(25)	= NULL,
@p_EDITOR_ID	varchar(15)	= NULL,
@p_AUTH_STATUS	varchar(1)	= NULL,
@p_CHECKER_ID	varchar(15)	= NULL,
@p_APPROVE_DT	varchar(25)	= NULL,
@p_CREATE_DT_KT	varchar(25)	= NULL,
@p_MAKER_ID_KT	varchar(15)	= NULL,
@p_AUTH_STATUS_KT	varchar(1)	= NULL,
@p_CHECKER_ID_KT	varchar(1)	= NULL,
@p_APPROVE_DT_KT  varchar(25)= null,
@p_CONFIRM_NOTE	nvarchar(500)	= NULL,
@p_BRANCH_CREATE	varchar(15)	= NULL,
@p_NOTES	varchar(15)	= NULL,
@p_RECORD_STATUS	varchar(1)	= NULL,
@p_TRANSFER_MAKER	nvarchar(50)	= NULL,
@p_TRANSFER_DT	varchar(25)	= NULL,
@p_TRASFER_USER_RECIVE	varchar(15)	= NULL,
@p_PROCESS	varchar(15)	= NULL,
@p_PAY_PHASE VARCHAR(15) = NULL,
@p_TOP INT = 10,
@p_LEVEL varchar(10) = NULL,
@p_FRMDATE VARCHAR(20)= NULL,
@p_TODATE VARCHAR(20) = NULL,
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
@p_IS_UPDATE_KT VARCHAR(15) = NULL,
@P_IS_TRANSFER VARCHAR(15) = NULL,
@p_TERM_ID VARCHAR(15) = NULL,
@P_USER_LOGIN VARCHAR(15)= NULL,
@p_FUNCTION VARCHAR(15) = NULL,
@p_TYPE_SEARCH VARCHAR(15) = NULL
AS
SET @p_TOP = NULL
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
declare @tmp_Login table(BRANCH_ID varchar(15))
insert into @tmp_Login  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
DECLARE @ROLE_ID VARCHAR(20) , @DEP_ID_LG VARCHAR(15) = NULL, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15)
DECLARE @BRANCH_TYPE VARCHAR(15)
SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)
DECLARE @TMP_DEP TABLE(DEP_ID VARCHAR(15))
SET @COST_LG =(SELECT TOP 1 COST_ID FROM PL_COSTCENTER_DT WHERE DEP_ID =@DEP_ID_LG)
SET @DVDM_ID =(SELECT TOP 1 DVDM_ID FROM PL_COSTCENTER WHERE COST_ID =@COST_LG)
INSERT INTO @TMP_DEP SELECT B.DEP_ID FROM PL_COSTCENTER_DT B  WHERE COST_ID =@COST_LG
DECLARE @BRANCH_TYPE_LG VARCHAR(15)
SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN)
IF(@p_TOP=NULL OR @p_TOP='' OR @p_TOP=0)BEGIN
		SELECT A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
		BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE,  AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
		ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME, '' EMP_FULLNAME, PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,PC3.TLNAME AS EXEC_USER,@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE, '' AS BRANCH_TYPE_CR
        FROM TR_REQ_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE'--Loại thanh toán (Nội bộ/Thanh toán/Nợ)
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'--Hình thức thanh toán( Tiền mặt/Chuyển khoản)			 
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			 LEFT JOIN 
			 (
			 SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			 LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
        WHERE 1=1 
			  AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
              AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp))
              OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
			  AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='')
			  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
			  --Luanlt-2019/10/14-Thêm Filter  REQ_TYPE,FROMDATE TODATE REF_ID
			  AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
			  AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='')
              AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='')
			  --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME  BRANCH_ID DEP_ID KT_AUTH
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
			  AND(A.BRANCH_CREATE = @p_BRANCH_CREATE or @p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)
			  AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND(A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL)
			  AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
			  -- Neu ben phan he ke toan thi chi lay nhung user co maker_id khac null
			  AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='' OR @p_FUNCTION ='TF')	
			  AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='')
			  -- BO SUNG DOAN CODE DO DON VI THI TRUONG PHONG NAO CHI DUOC THAY CAC GIAO DICH CUA PHONG DO THOI
			   -- NEU LA PHIEU DE NGHI TAM UNG NOI BO DO HOI SO TAO THI TRUONG PHONG CHI DUOC PHEP THAY CAC GIAO DICH DO NHAN VIEN CUA MINH TAO
			--AND ( 
			--  (@p_FUNCTION <>'TF' AND A.REQ_TYPE IN ('I','P','D') AND EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_TYPE ='HS' AND BRANCH_ID = A.BRANCH_ID) AND A.DEP_ID = @DEP_ID_LG)
			--  OR (@p_FUNCTION <>'TF' AND EXISTS(SELECT * FROM CM_BRANCH WHERE BRANCH_TYPE IN ('CN', 'PGD') AND BRANCH_ID = A.BRANCH_ID))
			--  OR (@p_FUNCTION ='TF' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login))
			--  )
			--- CHECK NEU TIM KIEM O HCQT THI PHONG BAN NAO THAY GIAO DICH CUA PHONG BAN DO
			AND (@p_TYPE_SEARCH='HC' AND EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = A.BRANCH_ID AND BRANCH_TYPE ='HS') 
									  AND ( ((A.PROCESS IS NOT NULL AND A.PROCESS <> '' OR A.TRASFER_USER_RECIVE IS NULL OR A.TRASFER_USER_RECIVE ='') AND (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =A.MAKER_ID) = (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN))
										   OR(A.MAKER_ID =@P_USER_LOGIN OR A.TRASFER_USER_RECIVE =@p_USER_LOGIN))
			OR (@p_TYPE_SEARCH='HC' AND NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = A.BRANCH_ID AND BRANCH_TYPE ='HS')
									  AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
			-- HOAC NGUOC LAI NEU HC NHUNG PHIEU DUOC TAO O PGD, CN
			OR (@p_TYPE_SEARCH='KT' AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
			OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH ='')
		ORDER BY A.CREATE_DT DESC;
    END;
    ELSE BEGIN
        SELECT A.*,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
		TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
		BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
		--Luanlt--2019/10/15-Sửa AL,AL1
		BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, ISNULL((A.REQ_AMT -H.SOTIEN_TT),0) AS TOTAL_AMT_TEMP, 
		ISNULL(H.SOTIEN_TT,0) TOTAL_AMT_PAY_HIS,
		TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,'' EMP_FULLNAME,PO.PO_CODE, PO.PO_NAME, S.SUP_NAME,S.TAX_NO SUP_TAX_NO,
		PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,PC3.TLNAME AS EXEC_USER,@p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE,BR1.BRANCH_TYPE AS BRANCH_TYPE_CR
        FROM TR_REQ_PAYMENT A
             LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
             LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
             LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
             LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
             LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
             LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
             LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
			 LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
             LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
			 --Luanlt--2019/10/15-Sửa AL,AL1
			 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE'
			 LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
			 LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
			 LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
			 LEFT JOIN 
			 (
			 SELECT PAY_ADV_ID, SUM(AMT_PAY) SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID
			 ) H ON A.REQ_PAY_ID =H.PAY_ADV_ID
			 LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P'
			 LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = PO.SUP_ID AND A.REQ_TYPE='P'
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' 
			 LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C'
			  LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID
        WHERE 1=1 
			  AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
			  AND(A.MAKER_ID =@p_MAKER_ID OR @p_MAKER_ID='' OR @p_MAKER_ID IS NULL)
			  AND(A.DEP_ID=@p_DEP_ID OR @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND((@p_LEVEL='ALL' AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp_Login))
              OR((@p_LEVEL='UNIT' AND A.BRANCH_ID=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))
              AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login))
              OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
			  AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='')
			  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
			  --Luanlt-2019/10/14-Thêm Filter  REQ_TYPE,FROMDATE TODATE REF_ID
			  AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL)
			  AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='')
              AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='')
			  --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME  BRANCH_ID DEP_ID KT_AUTH
			  AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
			  AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' or @p_REQ_REASON='' OR @p_REQ_REASON IS NULL)
			  --AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'')
			  --AND(A.BRANCH_CREATE = @p_BRANCH_CREATE or @p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)
			  AND(A.DEP_ID = @p_DEP_ID  or @p_DEP_ID='' OR @p_DEP_ID IS NULL)
			  AND((A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL) OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S')))
			  AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL)
			  --AND((A.TRANSFER_MAKER IS NOT NULL AND @p_IS_TRANSFER='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              --OR((A.TRANSFER_MAKER IS NULL AND @p_IS_TRANSFER='N'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')
			  -- Neu ben phan he ke toan thi chi lay nhung user co maker_id khac null
			    AND ((@p_FUNCTION ='KT' AND EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND TLNAME= @p_USER_LOGIN)) 
				OR @p_FUNCTION ='' OR @p_FUNCTION IS NULL OR @p_FUNCTION ='TF' )	  
			 -- AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='')
			  AND(( @p_IS_TRANSFER='Y' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID)) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
              OR((@p_IS_TRANSFER='N' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID )))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')	
			AND
			  ( 
			  A.MAKER_ID =@p_USER_LOGIN  OR ( A.TRASFER_USER_RECIVE = @P_USER_LOGIN AND A.AUTH_STATUS NOT IN ('E','R'))
			  OR(A.AUTH_STATUS <>'E' AND
			  (
			  (@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS'  AND (A.TRASFER_USER_RECIVE = @p_USER_LOGIN OR (@ROLE_ID IN ('GDDV','GDK','KTT','TPTC') AND A.BRANCH_ID =@p_BRANCH_LOGIN AND A.DEP_ID =@DEP_ID_LG 
																																		  AND( A.TRASFER_USER_RECIVE IS NULL OR A.TRASFER_USER_RECIVE ='' OR (A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>'')))))
			  OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND (@ROLE_ID = 'GDDV' AND 
			  ((A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>'') OR A.TRASFER_USER_RECIVE ='' OR A.TRASFER_USER_RECIVE IS NULL)
			  AND ((A.BRANCH_CREATE <> @p_BRANCH_LOGIN AND A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'') OR A.BRANCH_CREATE = @p_BRANCH_LOGIN)
			  AND A.BRANCH_ID IN (SELECT * FROM @tmp)))
			  OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'PGD' AND (@ROLE_ID IN ('GDDV','TPGD') AND A.BRANCH_ID =@p_BRANCH_LOGIN))
			  OR(@p_TYPE_SEARCH ='HC' AND @ROLE_ID ='KSV' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
			  OR(@p_TYPE_SEARCH='KT' AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
			  OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH =''
			  )))	
		ORDER BY A.CREATE_DT DESC;
    END;
¿
ALTER PROCEDURE [dbo].[TR_PO_MASTER_Upd]
@p_PO_ID	varchar(15) = NULL,
@P_PO_TYPE INT = NULL,
@p_PO_CODE	varchar(15)  = NULL,
@p_PO_NAME	nvarchar(200)  = NULL,
@p_CONTRACT_ID	varchar(15)  = NULL,
@p_SUP_ID	varchar(15)  = NULL,
@p_SUP_NAME	nvarchar(200)  = NULL,
@p_SUP_ADDR	nvarchar(200)  = NULL,
@p_INPUT_DT	VARCHAR(20) = NULL,
@p_PAYMENT_DT	VARCHAR(20) = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_REQ_DOC_ID	varchar(15)  = NULL,
@p_DELIVERY_DT	VARCHAR(20) = NULL,
@p_PAYAPP_DT	VARCHAR(20) = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_TR_REQ_ID VARCHAR(20) = NULL,
@p_TR_REQ_CODE VARCHAR(20)= NULL,
@P_LISTASSET XML = NULL,
@P_LISTPAYMENT XML = NULL,
@P_LISTROLE XML = NULL,
@p_BRANCH_ID varchar(15) = null,
@p_IS_CLOSED VARCHAR(1) = NULL
AS
	DECLARE @REF_CODE VARCHAR(15) = NULL, @PDN_TT VARCHAR(15) = NULL
	SET @REF_CODE =
	(	SELECT TOP 1 B.REQ_PAY_CODE
		FROM TR_REQ_ADVANCE_DT A
		INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID
		WHERE A.REF_ID =@p_PO_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)
	)
	--- TIM KIEM XEM PO NAY CO DUOC PHEP THANH TOAN HAY KHONG
	SET @PDN_TT =
	(	SELECT TOP 1 B.REQ_PAY_CODE
		FROM TR_REQ_ADVANCE_DT A
		INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID
		WHERE A.REF_ID =@p_CONTRACT_ID AND (B.AUTH_STATUS_KT <>'A' OR B.AUTH_STATUS_KT IS NULL)
	)
	IF(@p_IS_CLOSED ='Y' AND @REF_CODE IS NOT NULL)
	BEGIN
		ROLLBACK TRANSACTION
			SELECT '-1' Result, @p_PO_ID AS PO_ID, N'PO đang được thực hiện tạm ứng với số phiếu: '+@REF_CODE AS ErrorDesc 
			RETURN '-1'
	END
	IF(@p_IS_CLOSED ='Y' AND @PDN_TT IS NOT NULL)
	BEGIN
		ROLLBACK TRANSACTION
			SELECT '-1' Result, @p_PO_ID AS PO_ID, N'PO đang được thực hiện thanh toán với số phiếu: '+@PDN_TT AS ErrorDesc 
			RETURN '-1'
	END
	IF EXISTS(SELECT * FROM TR_PO_MASTER WHERE [PO_CODE] = @p_PO_CODE AND [PO_ID] <> @p_PO_ID)
	BEGIN
		SELECT ErrorCode Result, '' PO_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'PO-00002'
		RETURN '0'
	END
	--- 21052020 KIEM TRA XEM NEU PYCMS DA TON TAI HAY CHUA
	IF((SELECT COUNT(*) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_DOC_ID)=1)
	BEGIN
			IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_REQ_DOC_ID AND AUTH_STATUS IN ('E','U','A') AND PO_ID <>@p_PO_ID))
			BEGIN
				ROLLBACK TRANSACTION
				SELECT '-1' Result, @p_PO_ID AS PO_ID, N'Phiếu yêu cầu mua sắm số :'+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_DOC_ID)+N' đã được link tới PO có số: '+(SELECT TOP 1 PO_CODE FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_REQ_DOC_ID) AS ErrorDesc 
				RETURN '-1'
			END
	END
	------
	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET
	DECLARE AssetDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/AssetDetail',2)
	WITH 
	(
		PD_ID VARCHAR(15),
		PLAN_ID	varchar(15)  ,
		TRADE_ID	varchar(15)  ,
		GOODS_ID	varchar(15)  ,
		[DESCRIPTION] nvarchar(500),
		UNIT_ID	varchar(15)  ,
		QUANTITY	decimal(18)  ,
		PRICE	decimal(18)  ,
		TOTAL_AMT	decimal(18),
		IS_DELIVERY	varchar(1) ,
		DELIVERY_DT	VARCHAR(20),
		PAYMENT_STATUS	varchar(4),
		AMOUNT_PAID	decimal(18)  ,
		PAID_DT	VARCHAR(20) ,
		INVOICENO	varchar(1000),
		NOTES	nvarchar(1000),
		RECEIVE_BRANCH	varchar(15),
		RECEIVE_ADDR	nvarchar(1000),
		RECEIVE_PERSON	nvarchar(500),
		RECEIVE_TEL	varchar(100),
		EXP_DELIVERY_DT varchar(20),
		GOODS_NAME NVARCHAR(500),
		INVOICE_DT	VARCHAR(20),
		--THIEUVQ 100415 THEM LOAI HANG HOA THUC TE
		GOODSTYPE_REAL	VARCHAR(15),
		GOODSTYPE_REAL_NAME	VARCHAR(15),
		--THIEUVQ 13092016 THEM VAT, CONTRACT_DT
		VAT DECIMAL(18,2),
		PRICE_VAT DECIMAL(18,0),
		CONTRACT_DT VARCHAR(15)
		)
	OPEN AssetDetail

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTPAYMENT
	DECLARE PaymentDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)
	WITH 
	(
		PAY_ID	VARCHAR(15),
		PAY_PHASE	varchar(15),
		EXPECTED_DT	VARCHAR(20),
		[PERCENT]	decimal(18),
		[AMOUNT]	decimal(18),
		NOTES	nvarchar(1000)
	)
	OPEN PaymentDetail
	PRINT 'PASS KHOI TAO'

	--Nhom user gui mail (DAO EDIT)
	Exec sp_xml_preparedocument @hdoc Output,@P_LISTROLE
	DECLARE LISTROLE CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/LISTROLE',2)
	WITH 
	(
		NOTIFI_ID	VARCHAR(15),
		TL_NAME	varchar(15),
		EDITOR_DT VARCHAR(20),
		EDITOR_ID VARCHAR(15),
		NOTES nvarchar(1000)
	)
	OPEN LISTROLE

BEGIN TRANSACTION
		IF @p_DELIVERY_DT = ''
			SET @p_DELIVERY_DT = NULL
		IF @p_PAYAPP_DT	= ''
			SET @p_PAYAPP_DT = NULL
		--insert master
		UPDATE TR_PO_MASTER SET [PO_TYPE] = @P_PO_TYPE, [PO_CODE] = @p_PO_CODE,[PO_NAME] = @p_PO_NAME,
		[CONTRACT_ID] = @p_CONTRACT_ID,[SUP_ID] = @p_SUP_ID,[SUP_NAME] = @p_SUP_NAME,[SUP_ADDR] = @p_SUP_ADDR,
		[INPUT_DT] = CONVERT(DATETIME, @p_INPUT_DT, 103),[PAYMENT_DT] = CONVERT(DATETIME, @p_PAYMENT_DT, 103),
		[TOTAL_AMT] = @p_TOTAL_AMT,[REQ_DOC_ID] = @p_TR_REQ_ID,[DELIVERY_DT] = CONVERT(DATETIME, @p_DELIVERY_DT, 103),
		[PAYAPP_DT] = CONVERT(DATETIME, @p_PAYAPP_DT, 103),[NOTES] = @p_NOTES,[RECORD_STATUS] = '1',
		[MAKER_ID] = @p_MAKER_ID,[CREATE_DT] = CONVERT(DATETIME, @p_CREATE_DT, 103),[AUTH_STATUS] = @p_AUTH_STATUS,
		[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103), BRANCH_ID = @p_BRANCH_ID
		WHERE  PO_ID= @p_PO_ID
		IF @@Error <> 0 GOTO ABORT
		PRINT 'INSERT MASTER SUCCESS'
		UPDATE dbo.TR_PO_MASTER_TEMP SET TR_REQ_ID=@p_TR_REQ_ID,TR_REQ_CODE=@p_TR_REQ_CODE WHERE PO_ID=@p_PO_ID

		--insert asset detail tương đương như NỘI DUNG trong bảng contract detail
		Declare 
		@PAY_ID VARCHAR(15),
		@PD_ID VARCHAR(15),
		@PLAN_ID	varchar(15),
		@TRADE_ID	varchar(15),
		@GOODS_ID	varchar(15),
		@DESCRIPTION nvarchar(500),
		@UNIT_ID	varchar(15),
		@QUANTITY	decimal(18),
		@PRICE	decimal(18),
		@TOTAL_AMT	decimal(18),
		@IS_DELIVERY	varchar(1),
		@DELIVERY_DT	VARCHAR(20),
		@PAYMENT_STATUS	varchar(4),
		@AMOUNT_PAID	decimal(18),
		@PAID_DT	VARCHAR(20),
		@INVOICENO	varchar(20),
		@NOTES	nvarchar(1000),
		@PO_ID	varchar(15)  = NULL,
		@PAY_PHASE	VARCHAR(20) = NULL,
		@EXPECTED_DT	VARCHAR(20) = NULL,
		@PERCENT	decimal(18)  = NULL,
		@AMOUNT	decimal(18)  = NULL,
		@RECEIVE_BRANCH	varchar(15)=NULL,
		@RECEIVE_ADDR	nvarchar(1000)=NULL,
		@RECEIVE_PERSON	nvarchar(500)=NULL,
		@RECEIVE_TEL	varchar(100)=NULL,
		@EXP_DELIVERY_DT varchar(20) = NULL,
		@GOODS_NAME NVARCHAR(500) = NULL,
		@INVOICE_DT	VARCHAR(20),
		@GOODSTYPE_REAL	VARCHAR(15) = NULL,
		@GOODSTYPE_REAL_NAME	VARCHAR(15) = NULL,
		@VAT DECIMAL(18,2),
		@PRICE_VAT DECIMAL(18,0),
		@CONTRACT_DT VARCHAR(15),
		--DAO MOI THEM
		@NOTIFI_ID VARCHAR(15),
		@TL_NAME VARCHAR(15),
		@EDITOR_DT VARCHAR(20),
		@EDITOR_ID VARCHAR(15),
		@NOTES_ROLE nvarchar(1000)

		DECLARE @l_GOODSTYPE_ID VARCHAR(15)

		DELETE FROM TR_PO_DETAIL WHERE PO_ID = @P_PO_ID 

		FETCH NEXT FROM AssetDetail INTO @PD_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,@EXP_DELIVERY_DT,@GOODS_NAME, @INVOICE_DT,
		@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT
		WHILE @@FETCH_STATUS = 0
		BEGIN
			--if( NOT EXISTS (SELECT * FROM PL_MASTER A WHERE A.PLAN_ID = @PLAN_ID))
			--BEGIN
			--	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'TR-00002'
			--	GOTO ABORT
			--END

			IF(LEN(@PD_ID) = 0)

			BEGIN
				EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @PD_ID out
				IF @PD_ID ='' OR @PD_ID IS NULL GOTO ABORT
			END
			
			IF(@DELIVERY_DT = '')
				SET @DELIVERY_DT = NULL
			IF(@PAID_DT = '')
				SET @PAID_DT = NULL
			IF(@INVOICE_DT = '')
				SET @INVOICE_DT = NULL				
			IF @EXP_DELIVERY_DT = '' SET @EXP_DELIVERY_DT = NULL

			/***THIEUVQ THEM KIEM TRA VA LUU THONG TIN LOAI HANG HOA THUC TE***/
			--BEGIN
			--CHUA CO LOAI HANG HOA THI THEM MOI
			IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''
			BEGIN
				--NEU TON TAI THI LAY RA ID
				SET @GOODSTYPE_REAL = (SELECT TOP 1 GD_RETYPE_ID FROM CM_GOODSTYPE_REAL WHERE [TYPE_NAME] = @GOODSTYPE_REAL_NAME)
				--NEU CHUA CO THI THEM MOI
				IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''
				BEGIN
					EXEC SYS_CodeMasters_Gen 'CM_GOODSTYPE_REAL', @l_GOODSTYPE_ID OUT
					IF @l_GOODSTYPE_ID='' OR @l_GOODSTYPE_ID IS NULL GOTO ABORT

					INSERT INTO CM_GOODSTYPE_REAL VALUES (@l_GOODSTYPE_ID, @l_GOODSTYPE_ID, @GOODSTYPE_REAL_NAME, '', '1',
					@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),'A',@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103))
					IF @@ERROR <> 0 GOTO ABORT

					SET @GOODSTYPE_REAL = @l_GOODSTYPE_ID
				END
			END			
			--END

			INSERT INTO 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(@PD_ID ,@p_PO_ID ,@PLAN_ID ,@TRADE_ID ,@GOODS_ID,@DESCRIPTION ,@UNIT_ID ,@QUANTITY ,@PRICE ,@TOTAL_AMT ,
			@IS_DELIVERY ,CONVERT(DATETIME, @DELIVERY_DT, 103) ,@PAYMENT_STATUS ,@AMOUNT_PAID ,
			CONVERT(DATETIME, @PAID_DT, 103) ,@INVOICENO ,@NOTES , @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,
			@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),CONVERT(DATETIME, @EXP_DELIVERY_DT, 103),
			@GOODS_NAME, CONVERT(DATETIME, @INVOICE_DT, 103),@GOODSTYPE_REAL,@VAT,@PRICE_VAT,@CONTRACT_DT)
			IF @@ERROR <> 0 GOTO ABORT

		-- next Group_Id
			FETCH NEXT FROM AssetDetail INTO @PD_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, @EXP_DELIVERY_DT,@GOODS_NAME,@INVOICE_DT,
			@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT
		END
		
		--insert payment detail
		
		DELETE FROM TR_PO_PAYMENT WHERE PO_ID = @p_PO_ID

		FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES
		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @PAY_ID
			IF(LEN(@PAY_ID) = 0)
			BEGIN
					EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @PAY_ID out
					IF @PAY_ID ='' OR @PAY_ID IS NULL GOTO ABORT
			END
			IF @EXPECTED_DT = ''
				SET @EXPECTED_DT = NULL
			INSERT INTO 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(@PAY_ID ,@p_PO_ID ,@PAY_PHASE ,CONVERT(DATETIME, @EXPECTED_DT, 103) ,@PERCENT ,@AMOUNT ,@NOTES ,@p_RECORD_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) )
			FETCH NEXT FROM PaymentDetail INTO @PAY_ID,@PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES
		END

		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail

		---INSERT NHOM QUYEN USER THONG BAO (DAO-EDIT)
		DELETE FROM TL_ROLE_NOTIFICATION WHERE PO_ID=@p_PO_ID

	   FETCH NEXT FROM LISTROLE INTO
		@NOTIFI_ID,
		@TL_NAME,
		@EDITOR_DT,
		@EDITOR_ID,
		@NOTES_ROLE
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF(LEN(@NOTIFI_ID)=0)
			EXEC SYS_CodeMasters_Gen 'TL_ROLE_NOTIFICATION', @NOTIFI_ID out
			IF @NOTIFI_ID='' OR @NOTIFI_ID IS NULL GOTO ABORT

			INSERT INTO [dbo].[TL_ROLE_NOTIFICATION]
           ([NOTIFI_ID]
           ,[NOTIFI_CODE]
           ,[NOTIFI_NAME]
           ,[TYPE]
           ,[PO_ID]
           ,[TL_NAME]
           ,[BRANCH_ID]
           ,[RECORD_STATUS]
           ,[AUTH_STATUS]
           ,[EDITOR_ID]
           ,[EDIT_DT]
           ,[NOTES])
			 VALUES
           (@NOTIFI_ID
           ,''
           ,''
           ,'PO'
           ,@p_PO_ID
           ,@TL_NAME
           ,@p_BRANCH_ID
           ,'1'
           ,'U'
           ,@EDITOR_ID
           ,CONVERT(DATETIME, @EDITOR_DT, 103)
           ,@NOTES_ROLE)
			
			IF @@ERROR <> 0 GOTO ABORT
			
			FETCH NEXT FROM LISTROLE INTO
			@NOTIFI_ID,
			@TL_NAME,
			@EDITOR_DT,
			@EDITOR_ID,
			@NOTES_ROLE
		END
		
		CLOSE LISTROLE
		DEALLOCATE LISTROLE
		--- BAT DAU CAP NHAT AUTH_STATUS ='U' NEU BAM GUI PHE DUYET
		IF(@p_RECORD_STATUS ='U')
		BEGIN
			UPDATE TR_PO_MASTER SET AUTH_STATUS ='U' WHERE PO_ID =@p_PO_ID
		END
COMMIT TRANSACTION
IF(@p_RECORD_STATUS ='U')
BEGIN
	-- INSERT VAO LOG
	INSERT INTO dbo.PL_PROCESS
	(
						REQ_ID,
						PROCESS_ID,
						CHECKER_ID,
						APPROVE_DT,
						PROCESS_DESC,NOTES
					)
					VALUES
					(   @p_PO_ID,        -- REQ_ID - varchar(15)
						'SEND',        -- PROCESS_ID - varchar(10)
						@p_MAKER_ID,        -- CHECKER_ID - varchar(15)
						GETDATE(), -- APPROVE_DT - datetime
					   N'Nhân viên gửi phê duyệt phiếu thành công' ,N'Gửi phê duyệt')
	SELECT '2' as Result, @p_PO_ID  PO_ID, N'PO số: '+ @p_PO_CODE +N' đã được gửi phê duyệt thành công. Vui lòng đợi cấp có thẩm quyền phê duyệt' ErrorDesc
	RETURN '2'
	
END
ELSE
BEGIN
	SELECT '0' as Result, @p_PO_ID  PO_ID, N'PO số: '+ @p_PO_CODE +N' đã được cập nhật dữ liệu thành công. Bạn có thể bấm gửi phê duyệt' ErrorDesc
	RETURN '0'
END
ABORT:
BEGIN
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail
		CLOSE LISTROLE
		DEALLOCATE LISTROLE
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
¿
ALTER PROCEDURE [dbo].[TR_PO_MASTER_Ins]
@P_PO_TYPE INT = NULL,
@p_PO_CODE	varchar(15)  = NULL,
@p_PO_NAME	nvarchar(200)  = NULL,
@p_CONTRACT_ID	varchar(15)  = NULL,
@p_SUP_ID	varchar(15)  = NULL,
@p_SUP_NAME	nvarchar(200)  = NULL,
@p_SUP_ADDR	nvarchar(200)  = NULL,
@p_INPUT_DT	VARCHAR(20) = NULL,
@p_PAYMENT_DT	VARCHAR(20) = NULL,
@p_TOTAL_AMT	decimal(18)  = NULL,
@p_REQ_DOC_ID	varchar(15)  = NULL,
@p_DELIVERY_DT	VARCHAR(20) = NULL,
@p_PAYAPP_DT	VARCHAR(20) = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = '1',
@p_MAKER_ID	varchar(15)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = 'U',
@p_CHECKER_ID	varchar(15)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = 'U',
@p_TR_REQ_ID VARCHAR(20) = NULL,
@p_TR_REQ_CODE VARCHAR(20)= NULL,
@P_LISTASSET XML = NULL,
@P_LISTPAYMENT XML = NULL,
@P_LISTROLE XML = NULL,
@p_BRANCH_ID varchar(15) = NULL
AS
	--IF EXISTS(SELECT * FROM TR_PO_MASTER WHERE [PO_CODE] = @p_PO_CODE)
	--BEGIN
	--	SELECT ErrorCode Result, '' PO_ID, '' PO_CODE, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'PO-00002'
	--	RETURN '0'
	--END
	DECLARE @sErrorCode VARCHAR(20)
	IF (@p_SUP_ID IS NOT NULL AND @p_SUP_ID <> '')
	BEGIN
		IF NOT EXISTS(SELECT * FROM CM_SUPPLIER A WHERE A.SUP_ID = @p_SUP_ID)
		BEGIN
			SET @sErrorCode = 'CM-00001'
		END
	END
		
	IF 	@sErrorCode <> ''
	BEGIN
		SELECT ErrorCode Result, ''  PO_ID, ''PO_CODE, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @sErrorCode
		RETURN '0'
	END

	Declare @hdoc INT
	Exec sp_xml_preparedocument @hdoc Output,@P_LISTASSET
	DECLARE AssetDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/AssetDetail',2)
	WITH 
	(
		PLAN_ID	varchar(15)  ,
		TRADE_ID	varchar(15)  ,
		GOODS_ID	varchar(15)  ,
		[DESCRIPTION] nvarchar(500),
		UNIT_ID	varchar(15)  ,
		QUANTITY	decimal(18)  ,
		PRICE	decimal(18)  ,
		TOTAL_AMT	decimal(18),
		IS_DELIVERY	varchar(1) ,
		DELIVERY_DT	VARCHAR(20),
		PAYMENT_STATUS	varchar(4),
		AMOUNT_PAID	decimal(18)  ,
		PAID_DT	VARCHAR(20) ,
		INVOICENO	varchar(1000),
		NOTES	nvarchar(1000),
		RECEIVE_BRANCH	varchar(15),
		RECEIVE_ADDR	nvarchar(1000),
		RECEIVE_PERSON	nvarchar(500),
		RECEIVE_TEL	varchar(100),
		EXP_DELIVERY_DT varchar(20),
		GOODS_NAME NVARCHAR(500),
		INVOICE_DT	VARCHAR(20),
		--THIEUVQ 100415 THEM LOAI HANG HOA THUC TE
		GOODSTYPE_REAL	VARCHAR(15),
		GOODSTYPE_REAL_NAME	VARCHAR(15),
		--THIEUVQ 13092016 THEM VAT, CONTRACT_DT
		VAT DECIMAL(18,2),
		PRICE_VAT DECIMAL(18,0),
		CONTRACT_DT VARCHAR(15)
	)
	OPEN AssetDetail

	Exec sp_xml_preparedocument @hdoc Output,@P_LISTPAYMENT
	DECLARE PaymentDetail CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/PaymentDetail',2)
	WITH 
	(
		PAY_PHASE	varchar(15),
		EXPECTED_DT	VARCHAR(20),
		[PERCENT]	decimal(18),
		[AMOUNT]	decimal(18),
		NOTES	nvarchar(1000)
	)
	OPEN PaymentDetail
	PRINT 'PASS KHOI TAO'

	--Nhom user gui mail (DAO EDIT)
	Exec sp_xml_preparedocument @hdoc Output,@P_LISTROLE
	DECLARE LISTROLE CURSOR FOR
	SELECT *
	FROM OPENXML(@hDoc,'/Root/LISTROLE',2)
	WITH 
	(
		NOTIFI_ID	VARCHAR(15),
		TL_NAME	varchar(15),
		EDITOR_DT VARCHAR(20),
		EDITOR_ID VARCHAR(15),
		NOTES nvarchar(1000)
	)
	OPEN LISTROLE
BEGIN TRANSACTION
		--- 21052020 KIEM TRA XEM NEU PYCMS DA TON TAI HAY CHUA
		IF((SELECT COUNT(*) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_TR_REQ_ID)=1)
		BEGIN
				IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_TR_REQ_ID AND AUTH_STATUS IN ('E','U','A')))
				BEGIN
					ROLLBACK TRANSACTION
					SELECT '-1' Result, '' AS PO_ID, N'Phiếu yêu cầu mua sắm số :'+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID =@p_TR_REQ_ID)+N' đã được link tới PO có số: '+(SELECT TOP 1 PO_CODE FROM TR_PO_MASTER WHERE REQ_DOC_ID =@p_TR_REQ_ID) AS ErrorDesc 
					RETURN '-1'
				END
		END
		----
		--insert master
		IF @p_DELIVERY_DT = ''
			SET @p_DELIVERY_DT = NULL
		IF @p_PAYAPP_DT	= ''
			SET @p_PAYAPP_DT = NULL
		
		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

		--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(@P_PO_TYPE,@l_PO_ID ,@p_PO_CODE ,@p_PO_NAME ,@p_CONTRACT_ID ,@p_SUP_ID ,@p_SUP_NAME ,@p_SUP_ADDR ,CONVERT(DATETIME, @p_INPUT_DT, 103) ,CONVERT(DATETIME, @p_PAYMENT_DT, 103) ,@p_TOTAL_AMT ,@p_TR_REQ_ID ,CONVERT(DATETIME, @p_DELIVERY_DT, 103) ,CONVERT(DATETIME, @p_PAYAPP_DT, 103) ,@p_NOTES ,'1' ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_AUTH_STATUS ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103),@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)
		    )
	
		--insert asset detail tương đương như NỘI DUNG trong bảng contract detail
		Declare 
		@PLAN_ID	varchar(15),
		@TRADE_ID	varchar(15),
		@GOODS_ID	varchar(15),
		@DESCRIPTION nvarchar(500),
		@UNIT_ID	varchar(15),
		@QUANTITY	decimal(18),
		@PRICE	decimal(18),
		@TOTAL_AMT	decimal(18),
		@IS_DELIVERY	varchar(1),
		@DELIVERY_DT	VARCHAR(20),
		@PAYMENT_STATUS	varchar(4),
		@AMOUNT_PAID	decimal(18),
		@PAID_DT	VARCHAR(20),
		@INVOICENO	varchar(20),
		@NOTES	nvarchar(1000),
		@PO_ID	varchar(15)  = NULL,
		@PAY_PHASE	VARCHAR(20) = NULL,
		@EXPECTED_DT	VARCHAR(20) = NULL,
		@PERCENT	decimal(18)  = NULL,
		@AMOUNT	decimal(18)  = NULL,
		@RECEIVE_BRANCH	varchar(15)=NULL,
		@RECEIVE_ADDR	nvarchar(1000)=NULL,
		@RECEIVE_PERSON	nvarchar(500)=NULL,
		@RECEIVE_TEL	varchar(100)=NULL,
		@EXP_DELIVERY_DT varchar(20) = NULL,
		@GOODS_NAME NVARCHAR(500) = NULL,
		@INVOICE_DT	VARCHAR(20),
		@GOODSTYPE_REAL	VARCHAR(15) = NULL,
		@GOODSTYPE_REAL_NAME	VARCHAR(15) = NULL,
		@VAT DECIMAL(18,2),
		@PRICE_VAT DECIMAL(18,0),
		@CONTRACT_DT VARCHAR(15),
		--DAO MOI THEM
		@NOTIFI_ID VARCHAR(15),
		@TL_NAME VARCHAR(15),
		@EDITOR_DT VARCHAR(20),
		@EDITOR_ID VARCHAR(15),
		@NOTES_ROLE nvarchar(1000)

		DECLARE @l_GOODSTYPE_ID VARCHAR(15)

		FETCH NEXT FROM AssetDetail INTO @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,@EXP_DELIVERY_DT,@GOODS_NAME,@INVOICE_DT,
		@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT
		WHILE @@FETCH_STATUS = 0	
		BEGIN
			--if( NOT EXISTS (SELECT * FROM PL_MASTER A WHERE A.PLAN_ID = @PLAN_ID))
			--BEGIN
			--	SELECT ErrorCode Result, ''  PO_ID, '' PO_CODE, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = 'TR-00002'
			--	GOTO ABORT
			--END
			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
			--NEU PO_TYPE = 0 TRONG KE HOACH SẼ TIEN HANH TRỪ SỐ HÀNG HÓA TRONG KẾ HOẠCH
			--NEU HOP ĐỒNG KHAC NULL THÌ SẼ TRỪ SỐ LƯỢNG ỨNG VỚI SẢN PHẦM TRONG HỢP ĐỒNG
			
			/***THIEUVQ KHI DUYET MOI TRU TREN KE HOACH****/
			--IF(@P_PO_TYPE = 0)
			--BEGIN
			--	UPDATE PL_TRADEDETAIL SET [QUANTITY_EXE] = @QUANTITY WHERE GOODS_ID = @GOODS_ID
			--	IF @@ERROR <> 0 GOTO ABORT
			--	-- CAP NHAP GIA TRI DA THUC HIEN TRONG PL_MASTER
			--END

			/***THIEUVQ THEM KIEM TRA VA LUU THONG TIN LOAI HANG HOA THUC TE***/
			--BEGIN
			--CHUA CO LOAI HANG HOA THI THEM MOI
			IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''
			BEGIN
				--NEU TON TAI THI LAY RA ID
				SET @GOODSTYPE_REAL = (SELECT TOP 1 GD_RETYPE_ID FROM CM_GOODSTYPE_REAL WHERE [TYPE_NAME] = @GOODSTYPE_REAL_NAME)
				--NEU CHUA CO THI THEM MOI
				IF @GOODSTYPE_REAL IS NULL OR @GOODSTYPE_REAL = ''
				BEGIN
					EXEC SYS_CodeMasters_Gen 'CM_GOODSTYPE_REAL', @l_GOODSTYPE_ID OUT
					IF @l_GOODSTYPE_ID='' OR @l_GOODSTYPE_ID IS NULL GOTO ABORT

					INSERT INTO CM_GOODSTYPE_REAL VALUES (@l_GOODSTYPE_ID, @l_GOODSTYPE_ID, @GOODSTYPE_REAL_NAME, '', '1',
					@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),'A',@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103))
					IF @@ERROR <> 0 GOTO ABORT

					SET @GOODSTYPE_REAL = @l_GOODSTYPE_ID
				END
			END			
			--END

			IF(@DELIVERY_DT = '')
				SET @DELIVERY_DT = NULL
			IF(@PAID_DT = '')
				SET @PAID_DT = NULL
			IF(@INVOICE_DT = '')
				SET @INVOICE_DT = NULL
			IF @EXP_DELIVERY_DT = '' SET @EXP_DELIVERY_DT = NULL

			--IF(LEN(@p_CONTRACT_ID) <> 0)
			--CAP NHAP SO HANG DA DUOC GOI BEN TRONG CONTRACT DETAIL
			INSERT INTO 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,@l_PO_ID,@PLAN_ID,@TRADE_ID,@GOODS_ID,@DESCRIPTION,@UNIT_ID,
			@QUANTITY,@PRICE,@TOTAL_AMT,@IS_DELIVERY,CONVERT(DATETIME, @DELIVERY_DT, 103),@PAYMENT_STATUS,@AMOUNT_PAID,
			CONVERT(DATETIME, @PAID_DT, 103),@INVOICENO,@NOTES, @RECEIVE_BRANCH, @RECEIVE_ADDR, @RECEIVE_PERSON, @RECEIVE_TEL,@p_RECORD_STATUS,
			@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),@p_AUTH_STATUS,@p_CHECKER_ID,CONVERT(DATETIME, @p_APPROVE_DT, 103),CONVERT(DATETIME, @EXP_DELIVERY_DT, 103),
			@GOODS_NAME,CONVERT(DATETIME, @INVOICE_DT, 103),@GOODSTYPE_REAL,@VAT,@PRICE_VAT,@CONTRACT_DT)
			IF @@ERROR <> 0 GOTO ABORT1
 
		-- next Group_Id
			FETCH NEXT FROM AssetDetail INTO @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,@EXP_DELIVERY_DT,@GOODS_NAME,@INVOICE_DT,
			@GOODSTYPE_REAL,@GOODSTYPE_REAL_NAME,@VAT,@PRICE_VAT,@CONTRACT_DT
		END
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		--insert payment detail


		FETCH NEXT FROM PaymentDetail INTO @PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES

		WHILE @@FETCH_STATUS = 0
		BEGIN
			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

			IF(@EXPECTED_DT = '')
				SET @EXPECTED_DT = NULL
			
			INSERT INTO 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,@l_PO_ID,@PAY_PHASE,CONVERT(DATETIME, @EXPECTED_DT, 103),
			@PERCENT,@AMOUNT,@NOTES,@p_RECORD_STATUS,@p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),@p_AUTH_STATUS,@p_CHECKER_ID,CONVERT(DATETIME, @p_APPROVE_DT, 103))
			IF @@ERROR <> 0 GOTO ABORT2

			FETCH NEXT FROM PaymentDetail INTO @PAY_PHASE,@EXPECTED_DT,@PERCENT,@AMOUNT,@NOTES
		END
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail

	   ---INSERT NHOM QUYEN USER THONG BAO (DAO-EDIT)
	   FETCH NEXT FROM LISTROLE INTO
		@NOTIFI_ID,
		@TL_NAME,
		@EDITOR_DT,
		@EDITOR_ID,
		@NOTES_ROLE
		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			EXEC SYS_CodeMasters_Gen 'TL_ROLE_NOTIFICATION', @NOTIFI_ID out
			IF @NOTIFI_ID='' OR @NOTIFI_ID IS NULL GOTO ABORT

			INSERT INTO [dbo].[TL_ROLE_NOTIFICATION]
           ([NOTIFI_ID]
           ,[NOTIFI_CODE]
           ,[NOTIFI_NAME]
           ,[TYPE]
           ,[PO_ID]
           ,[TL_NAME]
           ,[BRANCH_ID]
           ,[RECORD_STATUS]
           ,[AUTH_STATUS]
           ,[EDITOR_ID]
           ,[EDIT_DT]
           ,[NOTES]
		  ,[MAKER_ID]
		  ,[CREATE_DT])
			 VALUES
           (@NOTIFI_ID
           ,''
           ,''
           ,'PO'
           ,@l_PO_ID
           ,@TL_NAME
           ,@p_BRANCH_ID
           ,'1'
           ,'U'
           ,@EDITOR_ID
           ,CONVERT(DATETIME, @EDITOR_DT, 103)
           ,@NOTES_ROLE
		   ,@p_MAKER_ID
		   ,CONVERT(DATETIME, @p_CREATE_DT, 103))
			
			IF @@ERROR <> 0 GOTO ABORT
			 FETCH NEXT FROM LISTROLE INTO
			@NOTIFI_ID,
			@TL_NAME,
			@EDITOR_DT,
			@EDITOR_ID,
			@NOTES_ROLE
		END
		
		CLOSE LISTROLE
		DEALLOCATE LISTROLE

COMMIT TRANSACTION
SELECT '0' as Result, @l_PO_ID  PO_ID,@p_PO_CODE PO_CODE, N'Bản nháp số: '+@p_PO_CODE+N' được khởi tạo thành công. Bạn có thể bấm gửi phê duyệt!' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
ABORT1:
BEGIN
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End
ABORT2:
BEGIN
		CLOSE AssetDetail
		DEALLOCATE AssetDetail
		CLOSE PaymentDetail
		DEALLOCATE PaymentDetail
		ROLLBACK TRANSACTION
		SELECT '-1' AS RESULT
		RETURN '-1'
End