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

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