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_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)  

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)  

print @DVDM_ID  

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(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'))  

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  

-- KHAI BAO THEM ROLE NHAN UY QUYEN  

DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50))  

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 CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103)  

-- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM  

DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15))  

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 ='') 

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  

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, PC1.NOTES 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  

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 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_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='' 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 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 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_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 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.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 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 (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))  

-- ))  

A.AUTH_STATUS <>'E' AND((@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE ='I'  

AND (  

(@ROLE_ID IN ('GDDV','TP','PP') AND A.BRANCH_ID =@p_BRANCH_LOGIN AND A.DEP_ID = @DEP_ID_LG)  

OR  

(@ROLE_ID NOT IN ('GDDV','TP','PP') 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(A.DEP_ID =@DEP_ID_LG) OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))  

)))  

AND ( 1=1 AND  

( @ROLE_ID IN ('GDK','PTGD') AND (A.DVDM_ID =@DVDM_ID OR (A.DVDM_ID IS NULL OR A.DVDM_ID ='')) AND ((A.PROCESS IS NOT NULL AND A.PROCESS <>'')  

OR A.DEP_ID ='DEP000000000052' OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH)))  

OR (@ROLE_ID NOT IN ('GDK','PTGD'))  

)  

)  

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 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)))  

))  

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 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') 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='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)))  

)))  

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;