ALTER FUNCTION [dbo].[FN_CHECK_LIMIT_TR_REQ]
(	
	-- Add the parameters for the function here
	@REQ_ID VARCHAR(20),
	@p_role VARCHAR(20)
)
RETURNS  BIT
AS
BEGIN

IF( (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@REQ_ID AND  TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK' ))  <=5000000)
BEGIN
	RETURN 0
END
DECLARE @DMMS_ID VARCHAR(20), @SUM_PYC DECIMAL(18,0),@SUM_PYC_CDT DECIMAL(18,0), @REQ_PL_DT_ID VARCHAR(15)
SET @DMMS_ID =(SELECT DMMS_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID =@REQ_ID)
SET @SUM_PYC =(SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@REQ_ID AND  TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK'))

--IF( EXISTS(SELECT DMMS_ID FROM dbo.CM_DMMS WHERE DMMS_ID=@DMMS_ID AND 
--				EXISTS (SELECT CM_BRANCH.BRANCH_ID FROM dbo.CM_BRANCH WHERE CM_BRANCH.BRANCH_TYPE='HS' AND CM_DMMS.BRANCH_ID=CM_BRANCH.BRANCH_ID)))
--BEGIN
	SET @REQ_PL_DT_ID =(SELECT TOP 1 REQDT_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@REQ_ID)
	IF((SELECT TRADE_TYPE FROM PL_REQUEST_DOC_DT WHERE REQDT_ID =@REQ_PL_DT_ID) <> 'CDT'
	OR (SELECT TRADE_TYPE FROM PL_REQUEST_DOC_DT WHERE REQDT_ID =@REQ_PL_DT_ID) IS NULL 
	OR (SELECT TRADE_TYPE FROM PL_REQUEST_DOC_DT WHERE REQDT_ID =@REQ_PL_DT_ID) ='')
	BEGIN
		IF((SELECT TOP 1 TRAN_TYPE_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@REQ_ID) ='TRN0000000003') -- CD THAU
		BEGIN
			SET @SUM_PYC_CDT =(SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@REQ_ID AND TRAN_TYPE_ID ='TRN0000000003')
			IF(ISNULL(@SUM_PYC_CDT,0) >0)
			BEGIN 
				RETURN 1
			END
		END
	END
	IF(@DMMS_ID ='DMMS00000000001' OR @DMMS_ID ='DM0000000000003') -- NEU DMMS LA PHONG HANH CHINH HOI SO 2022214
	BEGIN
		IF(@SUM_PYC >=0 AND @SUM_PYC <=100000000)
		BEGIN
			RETURN 0
		END
		ELSE
		BEGIN
			RETURN 1
		END
	END
	ELSE IF(@DMMS_ID NOT IN ('DMMS00000000001','DM0000000000003') AND (SELECT BRANCH_DVMS FROM TR_REQUEST_DOC WHERE REQ_ID =@REQ_ID) = 'DV0001') --- NEU DMMS LA DVCM O HOI SO VÀ TRONG NHUNG PHIEU NHO HON 10 TR
	BEGIN
		IF(@SUM_PYC <=10000000)
		BEGIN
			RETURN 0
		END
		ELSE
		BEGIN
			RETURN 1
		END
	END
	---
	ELSE IF(@DMMS_ID NOT IN ('DMMS00000000001','DM0000000000003') AND (SELECT BRANCH_DVMS FROM TR_REQUEST_DOC WHERE REQ_ID =@REQ_ID) <> 'DV0001') --- NEU DMMS LA DVCM O HOI SO VÀ TRONG NHUNG PHIEU NHO HON 10 TR
	BEGIN
		IF(@SUM_PYC <=20000000)
		BEGIN
			RETURN 0
		END
		ELSE
		BEGIN
			RETURN 1
		END
	END

	---
--END
----IF( EXISTS(SELECT DMMS_ID FROM dbo.CM_DMMS WHERE DMMS_ID=@DMMS_ID AND 
----				EXISTS (SELECT CM_BRANCH.BRANCH_ID FROM dbo.CM_BRANCH WHERE CM_BRANCH.BRANCH_TYPE='HS' AND CM_DMMS.BRANCH_ID=CM_BRANCH.BRANCH_ID)))
----BEGIN
----	RETURN 1
----END
RETURN 0
END