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,  
@p_TEMP_PAY_STATUS VARCHAR(10) = NULL  
--select * from CM_ALLCODE where CDTYPE = 'TEMPPAYSTATUS'  
--HT Hoàn tất  
--DCHU Đang chờ hoàn ứng  
--DHU Đã hoàn ứng  
--'' tất cả  
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)  
-- KHAI BAO UY QUYEN KIEM NHIEM
DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))  
INSERT INTO @TABLE_ROLE VALUES (@ROLE_ID)
INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID  
AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='') AND TLNAME =@p_USER_LOGIN
--AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)  
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)  
 -- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM  
DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))  
INSERT INTO @DEP_AUTH VALUES (@DEP_ID_LG)
INSERT INTO @DEP_AUTH SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN  
AND (CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) OR EFF_DATE IS NULL OR EFF_DATE ='')
AND (CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) OR EXP_DATE IS NULL OR EXP_DATE ='')

DECLARE @BRANCH_AUTH TABLE (BRN_AUTH VARCHAR(15))
INSERT INTO @BRANCH_AUTH VALUES (@BRANCH_TYPE_LG)
INSERT INTO @BRANCH_AUTH SELECT BRANCH_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN AND 
CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)
--END
--IF(EXISTS(SELECT * FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID))  
-- SET @ROLE_ID=(SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID)  
SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)  
IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD','TKTGD','TKHDQT'))  
BEGIN  
PRINT @ROLE_ID  
END  
ELSE  
BEGIN  
--SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN)  
SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN)  
IF(@ROLE_ID IS NULL OR @ROLE_ID ='')  
BEGIN  
SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN))  
END  
END  

IF(@p_TOP=NULL OR @p_TOP='' OR @p_TOP=0)BEGIN  
SELECT A.*,'' as TEMP_PAY_STATUS ,  
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,  
ISNULL(A.PAY_AMT,0) 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,  
BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE, '' AS BRANCH_CODE_CONTRACT, '' AS BRANCH_NAME_CONTRACT
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  
LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_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 ='')  
--AND (  
--(A.PAY_AMT = A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'HT'))  
--OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') )  
--OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU'))  
--OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = ''  
--)  

ORDER BY A.CREATE_DT DESC;  
END;  
ELSE BEGIN  
SELECT A.*,'' as TEMP_PAY_STATUS ,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(A.PAY_AMT,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, CASE WHEN PC.ROLE_USER ='GDDV' THEN (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN) ELSE PC.ROLE_USER END AS ROLE_CONF, 
--PR.PROCESS_DESC +N' vào lúc '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103)) AS CONF_STATUS, 
PR.PROCESS_DESC +N' vào lúc '+ ISNULL(FORMAT(PR.APP_DT,'dd/MM/yyyy H:mm:ss'),'') AS CONF_STATUS,
--PC1.NOTES AS NEXT_STEP,  
---2021112 CAU HINH BUOC XU LY TIEP THEO PDN TAM UNG NOI BO
CASE WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS ='E' THEN N'Phiếu đang chờ gửi phê duyệt' 
WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS_KT ='R' THEN N'Phiếu đang chờ GDV xử lý'
WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS_KT ='P' THEN N'Phiếu đang chờ KSV xử lý'
WHEN A.REQ_TYPE='I' AND A.AUTH_STATUS ='R' THEN N'Phiếu bị trả về đơn vị' 
WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND  (A.PROCESS IS NULL OR A.PROCESS ='') AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND ISNULL(A.NOTES,'')='' THEN N'Chờ cấp duyệt trung gian xác nhận'
WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND  (A.PROCESS IS NULL OR A.PROCESS ='') AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND ISNULL(A.NOTES,'') <> '' THEN N'Chờ trưởng đơn vị xác nhận'
WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND   ISNULL(A.TRASFER_USER_RECIVE,'') = ''  AND ISNULL(A.PROCESS,'') = '' THEN N'Chờ trưởng đơn vị xác nhận'
WHEN A.REQ_TYPE='I' AND A.BRANCH_CREATE='DV0001' AND (A.PROCESS IS NOT NULL OR A.PROCESS ='0') THEN PC1.NOTES
WHEN A.REQ_TYPE='I' AND  A.BRANCH_CREATE <> 'DV0001' AND ISNULL(A.TRASFER_USER_RECIVE,'') <> '' AND (A.PROCESS IS NULL OR A.PROCESS ='') THEN N'Chờ cấp duyệt trung gian xác nhận'
WHEN A.REQ_TYPE='I' AND  A.BRANCH_CREATE<>'DV0001' AND (ISNULL(A.TRASFER_USER_RECIVE,'') ='' OR A.PROCESS ='0') THEN PC1.NOTES END AS NEXT_STEP,  
PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,CASE WHEN PC3.TLNAME IS NOT NULL AND PC3.TLNAME <>'' THEN @P_USER_LOGIN ELSE PC3.TLNAME END AS EXEC_USER,TL8.TLFullName AS RECIVER_MONEY_FULLNAME, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR, BR.BRANCH_TYPE,@p_TYPE_SEARCH AS TYPE_SEARCH,  
SR.ROLE_ID AS ROLE_ID_CRE, ISNULL(H.SOTIEN_TT,0) AS STTT, A.REQ_AMT - ISNULL(H.SOTIEN_TT,0) AS SL_CL,BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE, '' AS BRANCH_CODE_CONTRACT, '' AS BRANCH_NAME_CONTRACT
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  
--WHERE P.REQ_ID =@p_REQ_PAY_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 OR EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_PAY_ID AND STATUS_JOB ='C' AND TLNAME =@p_USER_LOGIN))
AND PC.STATUS='C'  
--LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER IN (SELECT * FROM @TABLE_ROLE) 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  
LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_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_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 OR 
(A.REQ_TYPE ='I' AND A.AUTH_STATUS <>'A' AND EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('GDDV','PP','GDK','KTT','PTGD','TGD','HDQT','TKTGD','TKHDQT'))))
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='' OR A.CREATE_DT IS NULL OR A.REQ_DT IS NULL)  
AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL OR A.REQ_DT IS NULL)  
--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) OR ISNULL(A.AUTH_STATUS_KT,'') ='A')) --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) AND ISNULL(A.AUTH_STATUS_KT,'') <>'A' ))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='')  
AND(( @p_IS_TRANSFER='Y' AND 
	 (@p_TYPE_SEARCH ='TKTGD' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='1'))
	 OR(@p_TYPE_SEARCH ='TKHDQT' AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='2'))
	 OR (@p_TYPE_SEARCH ='KT' AND (EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND (PROCESS_ID IS NULL OR PROCESS_ID ='' OR PROCESS_ID ='3'))  OR ISNULL(A.AUTH_STATUS_KT,'') ='A'))
	 ) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat  
OR((@p_IS_TRANSFER='N' AND 
	(@p_TYPE_SEARCH ='TKTGD' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND  PROCESS_ID ='1') AND ISNULL(A.AUTH_STATUS_KT,'') <>'A' )
	OR (@p_TYPE_SEARCH ='TKHDQT' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND PROCESS_ID ='2') AND ISNULL(A.AUTH_STATUS_KT,'') <>'A' )
	OR (@p_TYPE_SEARCH ='KT' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND (PROCESS_ID IS NULL OR PROCESS_ID ='' OR PROCESS_ID ='3')) AND ISNULL(A.AUTH_STATUS_KT,'') <>'A' ))
OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER=''))  

AND(((@p_PROCESS='PM' AND (A.REQ_AMT- ISNULL(A.PAY_AMT,0.00)) >0)  
AND (A.REQ_TYPE IN('I','D','P') OR (@DEP_ID_LG ='DEP000000000014' AND A.REQ_TYPE IN ('I','P','D'))) AND  
((A.DEP_ID =@DEP_ID_LG OR A.MAKER_ID =@p_USER_LOGIN OR A.MAKER_ID IS NULL) OR(@BRANCH_TYPE_LG <>'HS' AND A.BRANCH_ID =@p_BRANCH_LOGIN) OR (@p_REQ_PAY_CODE IS NOT NULL AND @p_REQ_PAY_CODE <> '' AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE)))  
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) OR (@p_REQ_PAY_CODE IS NOT NULL AND @p_REQ_PAY_CODE <> '' AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE)))    
AND ((@p_FUNCTION ='KT' AND (EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD X WHERE X.REQ_ID = A.REQ_PAY_ID AND (X.TLNAME= @p_USER_LOGIN OR X.TLNAME =@p_TRASFER_USER_RECIVE))) OR A.CHECKER_ID_KT ='admin' OR A.AUTH_STATUS_KT ='A')  
OR @p_FUNCTION IS NULL OR @p_FUNCTION ='' OR (@p_FUNCTION ='TF' AND A.AUTH_STATUS='A') OR @p_FUNCTION ='SIGN')
AND(A.MAKER_ID LIKE '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID ='')    
AND(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME =@p_TRASFER_USER_RECIVE AND TYPE_JOB IN ('XL','KS','TP') 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 (  

		(EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('GDDV','TP','KTT','TC')) AND (A.BRANCH_ID =@p_BRANCH_LOGIN OR A.BRANCH_ID IN (SELECT * FROM @BRANCH_AUTH))
				AND (A.DEP_ID = @DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))
				AND ((@p_AUTH_STATUS ='A' AND ISNULL(PROCESS,'') ='0' AND 
				EXISTS(SELECT * FROM PL_PROCESS WHERE REQ_ID =A.REQ_PAY_ID AND PROCESS_DESC IN (N'Trưởng đơn vị xác nhận phiếu',N'Cấp phê duyệt trung gian xác nhận phiếu'))
				OR (@p_AUTH_STATUS ='U' AND ISNULL(PROCESS,'')='') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS =''))
				) 
		OR  
		(@ROLE_ID IN ('NVMS','NVTT','TBP') AND  (A.TRASFER_USER_RECIVE = @p_USER_LOGIN  OR A.MAKER_ID =@p_MAKER_ID OR A.DEP_ID=@p_DEP_ID)
																AND (A.AUTH_STATUS =@p_AUTH_STATUS OR ISNULL(@p_AUTH_STATUS,'') =''))
		OR (EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH  IN ('GDK','PTGD')) AND (A.DVDM_ID =@DVDM_ID OR (A.DVDM_ID IS NULL OR A.DVDM_ID ='')) AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID
																													AND ROLE_USER IN (SELECT * FROM @TABLE_ROLE) AND A.BRANCH_CREATE ='DV0001' AND A.PROCESS<>'' AND A.PROCESS IS NOT NULL
																													AND ((@p_AUTH_STATUS ='A' AND STATUS IN ('P','A')) OR (@p_AUTH_STATUS = 'U' AND STATUS ='C') OR(ISNULL(@p_AUTH_STATUS,'') ='' AND STATUS IN ('C','P','A')))
																													))
		OR (EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLE_AUTH IN ('TKTGD', 'TKHDQT','TGD','HDQT')) AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID
																													AND ROLE_USER IN(SELECT * FROM @TABLE_ROLE) AND A.BRANCH_CREATE ='DV0001' AND A.PROCESS<>'' AND A.PROCESS IS NOT NULL
																												    AND ((@p_AUTH_STATUS ='A' AND STATUS IN ('P','A')) OR
																													(@p_AUTH_STATUS = 'U' AND STATUS ='C') OR(ISNULL(@p_AUTH_STATUS,'') ='' ))
																													))
	 
	)-- NEU ROLE NAM TRONG DS CAC ROLE WORKFLOW
)
-- NGUOC LAI NEU LA TAM UNG KHAC
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','PP','TBP','TP','TC') AND (A.BRANCH_ID =@p_BRANCH_LOGIN OR A.BRANCH_ID IN(SELECT * FROM @BRANCH_AUTH)) AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))  
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 (@ROLE_ID NOT IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH)))  
))
-- HẾT KIỂM TRA LIÊN QUAN ĐẾN LOẠI TẠM ỨNG
OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND (@ROLE_ID IN ('GDDV','PGD','TPGD','PPGD') 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)  
 
OR ((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 = 'CN' AND A.BRANCH_ID IN (SELECT * FROM @tmp))  
OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'PGD' AND (@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') 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 <> 'TKTGD' 
	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 IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER IN ('TGD','HDQT','TKTGD','TKHDQT') AND STATUS ='C')))  
OR(@p_TYPE_SEARCH='KT' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))  
OR(@p_TYPE_SEARCH='HC' AND (@ROLE_ID IN ('KSV','GDV')  OR @DEP_ID_LG ='DEP000000000022') AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))  
OR(@p_TYPE_SEARCH='HC' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND TLNAME =@p_USER_LOGIN AND TYPE_JOB ='XL')) 
OR(@p_TYPE_SEARCH IN ('TK','TKTGD','TKHDQT') AND A.PROCESS<>'' AND A.PROCESS IS NOT NULL
					AND ( (@p_FUNCTION ='TF' AND  EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID AND ROLE_USER =@ROLE_ID AND STATUS ='C'))
							OR (ISNULL(@p_FUNCTION,'') <> 'TF' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID = A.REQ_PAY_ID AND ROLE_USER =@ROLE_ID AND
							((@p_IS_TRANSFER ='N' AND STATUS ='C') OR (@p_IS_TRANSFER <>'N' AND STATUS IN ('C','P'))))))
					)
OR(@p_TYPE_SEARCH='PAY' AND (A.REQ_AMT -ISNULL(A.PAY_AMT,0)) >0 AND (  
(@BRANCH_TYPE_LG ='HS' AND (A.DEP_ID =@DEP_ID_LG OR A.MAKER_ID =@p_USER_LOGIN OR A.MAKER_ID IS NULL OR 1=1)) 
OR (@BRANCH_TYPE_LG <> 'HS' AND A.BRANCH_ID = @p_BRANCH_LOGIN)
OR (@p_REQ_PAY_CODE IS NOT NULL AND @p_REQ_PAY_CODE <> '' AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE)))  
)
OR(@p_TYPE_SEARCH='HC' AND (@ROLE_ID IN ('KSV','GDV')  OR @DEP_ID_LG ='DEP000000000022') AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))
))
AND (  
(A.PAY_AMT = A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'HT'))  
OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') )  
OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU'))  
OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = ''  
)  
AND (A.RECORD_STATUS ='1' AND A.AUTH_STATUS <>'X')
ORDER BY A.CREATE_DT DESC;  
END;  ---2022321