SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO ALTER PROCEDURE dbo.TR_REQUEST_DOC_Search @p_REQ_ID varchar(15) = NULL, @p_REQ_CODE nvarchar(100) = NULL, @p_REQ_NAME nvarchar(200) = NULL, @p_REQ_DT VARCHAR(20) = NULL, @p_REQ_TYPE varchar(20) = NULL, @p_REQ_CONTENT NVARCHAR(1000)=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_TOTAL_AMT decimal = NULL, @p_NOTES nvarchar(1000) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_MAKER_ID NVARCHAR(100) = NULL, @p_CREATE_DT VARCHAR(20) = NULL, @p_AUTH_STATUS varchar(50) = NULL, @p_CHECKER_ID VARCHAR(100) = NULL, @p_APPROVE_DT VARCHAR(20) = NULL, @p_CONTRACT_CODE VARCHAR(15) = NULL, @p_RECEIVE_BRANCH VARCHAR(15) = NULL, @p_USERNAME VARCHAR(100) = NULL, @p_BRANCH_ID VARCHAR(15)=NULL, @p_DEP_ID VARCHAR(15)=NULL, @p_STATUS VARCHAR(15)=NULL, @p_TOP INT = 10, @p_DVKD_MANAGE_APP_FROM VARCHAR(20) = NULL, @p_DVKD_MANAGE_APP_TO VARCHAR(20) = NULL, @p_REGION_ID varchar(15) = NULL, @p_LIST_CONTRACT_CODE VARCHAR(MAX) = NULL, @p_LIST_MSTT VARCHAR(MAX) = NULL AS BEGIN -- PAGING SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20) DECLARE @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20)) INSERT INTO @ROLE_LOGIN SELECT DisplayName AS ROLE_USER FROM AbpRoles WHERE Id IN (SELECT A.RoleId FROM AbpUserRoles A LEFT JOIN TL_USER B ON A.UserId = B.ID WHERE B.TLNANME = @p_USERNAME) SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME) SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME) DECLARE @lstCOST TABLE ( COST_ID VARCHAR(20) ) INSERT INTO @lstCOST SELECT COST_ID FROM dbo.CM_COSTCENTER_DT WHERE BRANCH_ID=@BRANCH_ID AND DEP_ID=@DEP_ID -- TienLee 11/14/21 -- DECLARE @REQUEST_DOC_DT_TABLE_CC TABLE ( CONTRACT_CODE VARCHAR(50) NULL ) DECLARE @REQUEST_DOC_DT_TABLE_M TABLE ( MSTT VARCHAR(15) NULL ) IF @p_LIST_CONTRACT_CODE IS NOT NULL OR @p_LIST_CONTRACT_CODE = '' BEGIN DECLARE @id VARCHAR(15) DECLARE setTable CURSOR FOR SELECT VALUE AS 'CONTRACT_CODE' FROM dbo.wsiSplit(@p_LIST_CONTRACT_CODE, ',') OPEN setTable FETCH NEXT FROM setTable INTO @id WHILE @@FETCH_STATUS = 0 BEGIN IF(@id != 'null') BEGIN INSERT INTO @REQUEST_DOC_DT_TABLE_CC(CONTRACT_CODE) VALUES(@id) END FETCH NEXT FROM setTable INTO @id END CLOSE setTable DEALLOCATE setTable END -- IF @p_LIST_MSTT IS NOT NULL OR @p_LIST_MSTT = '' BEGIN DECLARE @id_ VARCHAR(15) DECLARE setTable_ CURSOR FOR SELECT VALUE AS 'MSTT' FROM dbo.wsiSplit(@p_LIST_MSTT, ',') OPEN setTable_ FETCH NEXT FROM setTable_ INTO @id_ WHILE @@FETCH_STATUS = 0 BEGIN IF(@id_ != 'null') BEGIN INSERT INTO @REQUEST_DOC_DT_TABLE_M(MSTT) VALUES(@id_) END FETCH NEXT FROM setTable_ INTO @id_ END CLOSE setTable_ DEALLOCATE setTable_ END DECLARE @TempSTATUS TABLE ( STATUS VARCHAR(20) ) IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='QLTS' )) BEGIN INSERT INTO @TempSTATUS VALUES('DVKD') INSERT INTO @TempSTATUS VALUES('DVCM') END ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' )) BEGIN INSERT INTO @TempSTATUS VALUES('QLTS_N') --INSERT INTO @TempSTATUS VALUES('DVCM') END --ELSE IF(EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='CVMS' )) --BEGIN -- INSERT INTO @TempSTATUS VALUES('QLTS_N') -- INSERT INTO @TempSTATUS VALUES('QLTS_XL') -- --INSERT INTO @TempSTATUS VALUES('DVCM') --END DECLARE @lstBRANCH_DEP TABLE ( BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20) ) IF(NOT EXISTS(SELECT ROLE_USER FROM @ROLE_LOGIN WHERE ROLE_USER ='DVCM' OR ROLE_USER='CVMS_TBP' )) BEGIN INSERT INTO @lstBRANCH_DEP (BRANCH_ID,DEP_ID) SELECT TLSUBBRID,DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_USERNAME INSERT INTO @lstBRANCH_DEP (BRANCH_ID,DEP_ID) SELECT BRANCH_ID,DEP_ID FROM dbo.CM_KHOI_DT WHERE KHOI_ID IN (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID= @DEP_ID AND BRANCH_ID=@BRANCH_ID) END DECLARE @tbDep TABLE(DEP_ID VARCHAR(20)) INSERT INTO @tbDep SELECT ckd.DEP_ID FROM CM_DEPARTMENT cd LEFT JOIN CM_KHOI ck ON cd.DEP_CODE=ck.KHOI_CODE LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID WHERE cd.DEP_ID=@p_DEP_ID IF((EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_SLAS' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) BEGIN IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0) BEGIN -- PAGING BEGIN SELECT A.REQ_ID, A.REQ_CODE, A.REQ_NAME, ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.CONTRACT_ID, A.SUP_ID, A.SUP_NAME, A.SUP_ADDR, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, A.STATUS, A.DEP_ID, A.HO_NOTES,C.CONTRACT_CODE, E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR, K.CONTENT AS REQ_TYPE_NAME, CASE WHEN A.STATUS='DONE' AND A.BRANCH_ID='DV0001' AND KHOI.KHOI_CODE <>'D2200' THEN N'Đợi PMS trình Ban điều hàng theo đúng quy định' WHEN A.STATUS='DONE' AND( A.BRANCH_ID <> 'DV0001' OR KHOI.KHOI_CODE='D2200') THEN N'Phiếu đang chuyển trình Ban điều hành.' -- NguyenTD: Fix theo yêu cầu chị Loan ELSE ISNULL(J.CONTENT, I.CONTENT) -- ELSE END AS REQ_STATUS_NAME, F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME, F.TOTAL_AMT AS C_TOTAL_AMT , ISNULL((SELECT SUM(TR_REQ.TOTAL_AMT) FROM TR_REQUEST_DOC TR_REQ WHERE TR_REQ.CONTRACT_ID = A.CONTRACT_ID),0) AS C_DONE_AMT, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,G.BRANCH_NAME, PRP.DVKD_USER_APP, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_DOC A LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] LEFT JOIN CM_ALLCODE J ON J.CDNAME='TRREQAFTER' AND J.CDVAL=A.AFTER_APPROVE LEFT JOIN ( SELECT CK.KHOI_ID,CK.KHOI_CODE,ckd.DEP_ID FROM CM_KHOI ck LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID ) KHOI ON KHOI.DEP_ID=A.DEP_ID LEFT JOIN dbo.PL_REQUEST_PROCESS PRP ON PRP.REQ_ID=A.REQ_ID AND (PRP.ID = (SELECT TOP (1) ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID IN (SELECT STATUS FROM @TempSTATUS ) AND PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID ORDER BY ID DESC) OR ( NOT EXISTS(SELECT STATUS FROM @TempSTATUS) AND PRP.PROCESS_ID IN (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS T WHERE T.REQ_ID=A.REQ_ID AND T.PROCESS_ID=@p_STATUS) OR ((PRP.PROCESS_ID='DVKD_XN' ) AND @p_STATUS='DONE'))) --LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND A.REQ_DT >='2021-09-01' AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) --AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') --AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '') AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '') AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') --AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL) AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1) AND (A.STATUS = @p_STATUS OR @p_STATUS IS NULL OR @p_STATUS = '' OR @p_STATUS='WAITAPP' OR @p_STATUS='DONEAPP' OR (@p_STATUS ='WAITPO' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS ='CREATEDPO' AND A.AUTH_STATUS='A' AND EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ADDNEW_ID FROM dbo.ASS_ADDNEW_PO ASSPO INNER JOIN dbo.TR_PO_MASTER PM ON PM.PO_ID=ASSPO.PO_ID WHERE PM.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS_USE' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ASS_USE.USER_MASTER_ID FROM dbo.ASS_USE_MULTI_MASTER ASS_USE WHERE ASS_USE.REQ_ID=A.REQ_ID AND ASS_USE.AUTH_STATUS='A')) OR (@p_STATUS = 'QLTS_N' AND A.STATUS LIKE '%QLTS%') OR (@p_STATUS = 'DVCM_XN' AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS PRC WHERE PRC.REQ_ID=A.REQ_ID AND PRC.PROCESS_ID='DVCM' AND PRC.STATUS='P')) OR(@p_STATUS='IN_PROCESS' AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS PRC2 WHERE PRC2.REQ_ID=A.REQ_ID AND PRC2.STATUS='C')) OR(@p_STATUS='CVMS_REJECT' AND EXISTS(SELECT * FROM dbo.PL_PROCESS PLR WHERE (PLR.PROCESS_DESC='Reject' OR PLR.PROCESS_DESC='REJECT_QLTS' OR PLR.PROCESS_DESC='REJECT_DVCM') AND PROCESS_ID='CVMS_N' AND PLR.REQ_ID=A.REQ_ID)) ) --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) --AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH)) --AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' OR EXISTS(SELECT REQ_DOC_ID FROM TR_REQUEST_DOC_DT WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH AND REQ_DOC_ID = A.REQ_ID)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR A.DEP_ID IN (SELECT DEP_ID FROM @tbDep)) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT LEFT JOIN dbo.TR_CONTRACT TC ON TC.CONTRACT_ID=DTT.CONTRACT_ID INNER JOIN @REQUEST_DOC_DT_TABLE_CC DT_1 ON DT_1.CONTRACT_CODE = ISNULL(TC.CONTRACT_CODE,DTT.CONTRACT_CODE) WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_CONTRACT_CODE IS NULL OR @p_LIST_CONTRACT_CODE = '' ) AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT INNER JOIN @REQUEST_DOC_DT_TABLE_M DT_1 ON DT_1.MSTT = DTT.MSTT WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_MSTT IS NULL OR @p_LIST_MSTT = '' ) ORDER BY PRP.APPROVE_DT,A.REQ_DT DESC -- PAGING END END ELSE BEGIN -- PAGING BEGIN SELECT TOP(CONVERT(INT,@P_TOP)) A.REQ_ID, A.REQ_CODE, A.REQ_NAME, ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.CONTRACT_ID, A.SUP_ID, A.SUP_NAME, A.SUP_ADDR, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, A.STATUS, A.DEP_ID, A.HO_NOTES,C.CONTRACT_CODE, E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR, K.CONTENT AS REQ_TYPE_NAME, CASE WHEN A.STATUS='DONE' AND A.BRANCH_ID='DV0001' AND KHOI.KHOI_CODE <>'D2200' THEN N'Đợi PMS trình Ban điều hàng theo đúng quy định' WHEN A.STATUS='DONE' AND( A.BRANCH_ID <> 'DV0001' OR KHOI.KHOI_CODE='D2200') THEN N'Phiếu đang chuyển trình Ban điều hành.' -- NguyenTD: Fix theo yêu cầu chị Loan ELSE ISNULL(J.CONTENT, I.CONTENT) -- ELSE END AS REQ_STATUS_NAME, F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME, F.TOTAL_AMT AS C_TOTAL_AMT , ISNULL((SELECT SUM(TR_REQ.TOTAL_AMT) FROM TR_REQUEST_DOC TR_REQ WHERE TR_REQ.CONTRACT_ID = A.CONTRACT_ID),0) AS C_DONE_AMT, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,G.BRANCH_NAME, PRP.DVKD_USER_APP, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME --RQT.STATUS AS TEST D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_DOC A LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] LEFT JOIN CM_ALLCODE J ON J.CDNAME='TRREQAFTER' AND J.CDVAL=A.AFTER_APPROVE LEFT JOIN ( SELECT CK.KHOI_ID,CK.KHOI_CODE,ckd.DEP_ID FROM CM_KHOI ck LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID ) KHOI ON KHOI.DEP_ID=A.DEP_ID LEFT JOIN dbo.PL_REQUEST_PROCESS PRP ON PRP.REQ_ID=A.REQ_ID AND (PRP.ID = (SELECT TOP (1) ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID IN (SELECT STATUS FROM @TempSTATUS ) AND PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID ORDER BY ID DESC) OR ( NOT EXISTS(SELECT STATUS FROM @TempSTATUS) AND PRP.PROCESS_ID IN (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS T WHERE T.REQ_ID=A.REQ_ID AND T.PROCESS_ID=@p_STATUS) OR ((PRP.PROCESS_ID='DVKD_XN') AND @p_STATUS='DONE')))--LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = @p_AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND A.REQ_DT >='2021-09-01' --AND ((A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') ) AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '') AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '') AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') --AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') --AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL) AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1) AND (A.STATUS = @p_STATUS OR @p_STATUS IS NULL OR @p_STATUS = '' OR @p_STATUS='WAITAPP' OR @p_STATUS='DONEAPP' OR (@p_STATUS ='WAITPO' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS ='CREATEDPO' AND A.AUTH_STATUS='A' AND EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ADDNEW_ID FROM dbo.ASS_ADDNEW_PO ASSPO INNER JOIN dbo.TR_PO_MASTER PM ON PM.PO_ID=ASSPO.PO_ID WHERE PM.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS_USE' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ASS_USE.USER_MASTER_ID FROM dbo.ASS_USE_MULTI_MASTER ASS_USE WHERE ASS_USE.REQ_ID=A.REQ_ID AND ASS_USE.AUTH_STATUS='A')) OR (@p_STATUS = 'QLTS_N' AND A.STATUS LIKE '%QLTS%') OR (@p_STATUS = 'DVCM_XN' AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS PRC WHERE PRC.REQ_ID=A.REQ_ID AND PRC.PROCESS_ID='DVCM' AND PRC.STATUS='P')) OR(@p_STATUS='IN_PROCESS' AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS PRC2 WHERE PRC2.REQ_ID=A.REQ_ID AND PRC2.STATUS='C')) OR(@p_STATUS='CVMS_REJECT' AND EXISTS(SELECT * FROM dbo.PL_PROCESS PLR WHERE (PLR.PROCESS_DESC='Reject' OR PLR.PROCESS_DESC='REJECT_QLTS' OR PLR.PROCESS_DESC='REJECT_DVCM') AND PROCESS_ID='CVMS_N' AND PLR.REQ_ID=A.REQ_ID)) ) --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) --AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH)) --AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' OR EXISTS(SELECT REQ_DOC_ID FROM TR_REQUEST_DOC_DT WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH AND REQ_DOC_ID = A.REQ_ID)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR A.DEP_ID IN (SELECT DEP_ID FROM @tbDep)) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT LEFT JOIN dbo.TR_CONTRACT TC ON TC.CONTRACT_ID=DTT.CONTRACT_ID INNER JOIN @REQUEST_DOC_DT_TABLE_CC DT_1 ON DT_1.CONTRACT_CODE = ISNULL(TC.CONTRACT_CODE,DTT.CONTRACT_CODE) WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_CONTRACT_CODE IS NULL OR @p_LIST_CONTRACT_CODE = '' ) AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT INNER JOIN @REQUEST_DOC_DT_TABLE_M DT_1 ON DT_1.MSTT = DTT.MSTT WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_MSTT IS NULL OR @p_LIST_MSTT = '' ) ORDER BY PRP.APPROVE_DT,A.REQ_DT DESC -- PAGING END END END ELSE BEGIN IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0) BEGIN -- PAGING BEGIN SELECT A.REQ_ID, A.REQ_CODE, A.REQ_NAME, ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.CONTRACT_ID, A.SUP_ID, A.SUP_NAME, A.SUP_ADDR, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, A.STATUS, A.DEP_ID, A.HO_NOTES,C.CONTRACT_CODE, E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR, K.CONTENT AS REQ_TYPE_NAME, CASE WHEN A.STATUS='DONE' AND A.BRANCH_ID='DV0001' AND KHOI.KHOI_CODE <>'D2200' THEN N'Đợi PMS trình Ban điều hàng theo đúng quy định' WHEN A.STATUS='DONE' AND( A.BRANCH_ID <> 'DV0001' OR KHOI.KHOI_CODE='D2200') THEN N'Phiếu đang chuyển trình Ban điều hành.' -- NguyenTD: Fix theo yêu cầu chị Loan ELSE ISNULL(J.CONTENT, I.CONTENT) -- ELSE END AS REQ_STATUS_NAME, F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME, F.TOTAL_AMT AS C_TOTAL_AMT , ISNULL((SELECT SUM(TR_REQ.TOTAL_AMT) FROM TR_REQUEST_DOC TR_REQ WHERE TR_REQ.CONTRACT_ID = A.CONTRACT_ID),0) AS C_DONE_AMT, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,G.BRANCH_NAME, PRP.DVKD_USER_APP, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_DOC A LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] LEFT JOIN CM_ALLCODE J ON J.CDNAME='TRREQAFTER' AND J.CDVAL=A.AFTER_APPROVE LEFT JOIN ( SELECT CK.KHOI_ID,CK.KHOI_CODE,ckd.DEP_ID FROM CM_KHOI ck LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID ) KHOI ON KHOI.DEP_ID=A.DEP_ID LEFT JOIN dbo.PL_REQUEST_PROCESS PRP ON PRP.REQ_ID=A.REQ_ID AND (PRP.ID = (SELECT TOP (1) ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID IN (SELECT STATUS FROM @TempSTATUS ) AND PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID ORDER BY ID DESC) OR ( NOT EXISTS(SELECT STATUS FROM @TempSTATUS) AND PRP.PROCESS_ID IN (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS T WHERE T.REQ_ID=A.REQ_ID AND T.PROCESS_ID=@p_STATUS) OR ((PRP.PROCESS_ID='DVKD_XN' ) AND @p_STATUS='DONE'))) --LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = A.AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND (((A.MAKER_ID=@p_USERNAME OR( (G.BRANCH_TYPE='HS' AND EXISTS(SELECT T.DEP_ID FROM @lstBRANCH_DEP T WHERE T.BRANCH_ID=A.BRANCH_ID AND T.DEP_ID=A.DEP_ID)) OR ( (G.BRANCH_TYPE<>'HS' AND EXISTS(SELECT T.BRANCH_ID FROM @lstBRANCH_DEP T WHERE T.BRANCH_ID=A.BRANCH_ID)) ) ) ) AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') ) OR EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND (PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND ((@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P') OR (@p_AUTH_STATUS<>'A' AND @p_AUTH_STATUS<>'U') OR @p_AUTH_STATUS IS NULL) --AND (((PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND (@p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)) OR -- (@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P')) AND (PL_REQUEST_PROCESS.DVKD_USER_APP IS NULL OR PL_REQUEST_PROCESS.DVKD_USER_APP ='') AND PL_REQUEST_PROCESS.ROLE_USER in (SELECT r.DisplayName FROM TL_USER u LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID LEFT JOIN AbpRoles r ON ur.RoleId = r.Id where u.TLNANME=@p_USERNAME) AND BRANCH_ID=@BRANCH_ID AND ( EXISTS(SELECT * FROM dbo.CM_BRANCH WHERE CM_BRANCH.BRANCH_ID = PL_REQUEST_PROCESS.BRANCH_ID AND CM_BRANCH.BRANCH_TYPE<>'HS') OR DEP_ID IS NULL OR DEP_ID='' OR DEP_ID IN (SELECT DEP_ID FROM dbo.CM_KHOI_DT WHERE KHOI_ID IN (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID=@DEP_ID)) ) AND (COST_ID IN (SELECT COST_ID FROM @lstCOST) OR COST_ID ='' OR COST_ID IS NULL) ) OR EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND DVKD_USER_APP = @p_USERNAME AND (PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND ((@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P') OR (@p_AUTH_STATUS<>'U' AND @p_AUTH_STATUS<>'A') OR @p_AUTH_STATUS IS NULL)) OR ( EXISTS(SELECT COST_ID FROM dbo.TR_REQUEST_COSTCENTER TRCO WHERE TRCO.REQ_ID=A.REQ_ID AND TRCO.COST_ID IN (SELECT COST_ID FROM @lstCOST) ) AND NOT EXISTS(SELECT ID FROM PL_REQUEST_PROCESS PRPC2 WHERE PRPC2.REQ_ID =A.REQ_ID AND PRPC2.COST_ID IN (SELECT COST_ID FROM @lstCOST)) AND @p_AUTH_STATUS<>'U' ) OR( EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)) OR (EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_SLAS' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)) AND A.REQ_DT >='2021-09-01') ) AND ((A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') )) AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) --AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') --AND (A.REQ_NAME LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '') AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '') AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') --AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') --AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL) AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1) AND (A.STATUS = @p_STATUS OR @p_STATUS IS NULL OR @p_STATUS = '' OR @p_STATUS='WAITAPP' OR @p_STATUS='DONEAPP' OR (@p_STATUS ='WAITPO' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS ='CREATEDPO' AND A.AUTH_STATUS='A' AND EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ADDNEW_ID FROM dbo.ASS_ADDNEW_PO ASSPO INNER JOIN dbo.TR_PO_MASTER PM ON PM.PO_ID=ASSPO.PO_ID WHERE PM.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS_USE' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ASS_USE.USER_MASTER_ID FROM dbo.ASS_USE_MULTI_MASTER ASS_USE WHERE ASS_USE.REQ_ID=A.REQ_ID AND ASS_USE.AUTH_STATUS='A')) OR (@p_STATUS = 'QLTS_N' AND A.STATUS LIKE '%QLTS%') OR (@p_STATUS = 'DVCM_XN' AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS PRC WHERE PRC.REQ_ID=A.REQ_ID AND PRC.PROCESS_ID='DVCM' AND PRC.STATUS='P')) OR(@p_STATUS='IN_PROCESS' AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS PRC2 WHERE PRC2.REQ_ID=A.REQ_ID AND PRC2.STATUS='C')) OR(@p_STATUS='CVMS_REJECT' AND EXISTS(SELECT * FROM dbo.PL_PROCESS PLR WHERE (PLR.PROCESS_DESC='Reject' OR PLR.PROCESS_DESC='REJECT_QLTS' OR PLR.PROCESS_DESC='REJECT_DVCM') AND PROCESS_ID='CVMS_N' AND PLR.REQ_ID=A.REQ_ID)) ) --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) --AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH)) --AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' OR EXISTS(SELECT REQ_DOC_ID FROM TR_REQUEST_DOC_DT WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH AND REQ_DOC_ID = A.REQ_ID)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR A.DEP_ID IN (SELECT DEP_ID FROM @tbDep)) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT LEFT JOIN dbo.TR_CONTRACT TC ON TC.CONTRACT_ID=DTT.CONTRACT_ID INNER JOIN @REQUEST_DOC_DT_TABLE_CC DT_1 ON DT_1.CONTRACT_CODE = ISNULL(TC.CONTRACT_CODE,DTT.CONTRACT_CODE) WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_CONTRACT_CODE IS NULL OR @p_LIST_CONTRACT_CODE = '' ) AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT INNER JOIN @REQUEST_DOC_DT_TABLE_M DT_1 ON DT_1.MSTT = DTT.MSTT WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_MSTT IS NULL OR @p_LIST_MSTT = '' ) ORDER BY PRP.APPROVE_DT,A.REQ_DT ASC -- PAGING END END ELSE BEGIN -- PAGING BEGIN SELECT TOP(CONVERT(INT,@P_TOP)) A.REQ_ID, A.REQ_CODE, A.REQ_NAME, ISNULL(PRP.APPROVE_DT,PRP2.APPROVE_DT) AS REQ_DT, A.REQ_TYPE, A.REQ_CONTENT, A.CONTRACT_ID, A.SUP_ID, A.SUP_NAME, A.SUP_ADDR, A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS, A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS, A.CHECKER_ID, A.APPROVE_DT, A.BRANCH_ID, A.STATUS, A.DEP_ID, A.HO_NOTES,C.CONTRACT_CODE, E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR, K.CONTENT AS REQ_TYPE_NAME, CASE WHEN A.STATUS='DONE' AND A.BRANCH_ID='DV0001' AND KHOI.KHOI_CODE <>'D2200' THEN N'Đợi PMS trình Ban điều hàng theo đúng quy định' WHEN A.STATUS='DONE' AND( A.BRANCH_ID <> 'DV0001' OR KHOI.KHOI_CODE='D2200') THEN N'Phiếu đang chuyển trình Ban điều hành.' -- NguyenTD: Fix theo yêu cầu chị Loan ELSE ISNULL(J.CONTENT, I.CONTENT) -- ELSE END AS REQ_STATUS_NAME, F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME, F.TOTAL_AMT AS C_TOTAL_AMT , ISNULL((SELECT SUM(TR_REQ.TOTAL_AMT) FROM TR_REQUEST_DOC TR_REQ WHERE TR_REQ.CONTRACT_ID = A.CONTRACT_ID),0) AS C_DONE_AMT, @p_RECEIVE_BRANCH AS RECEIVE_BRANCH,G.BRANCH_CODE,G.BRANCH_NAME, PRP.DVKD_USER_APP, --CASE WHEN RQT.STATUS = 'C' OR RQT.STATUS = 'U' THEN N'Chờ duyệt' --ELSE N'Đã duyệt' END AS AUTH_STATUS_NAME --RQT.STATUS AS TEST D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME --D.AUTH_STATUS_NAME -- SELECT END FROM TR_REQUEST_DOC A LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID LEFT JOIN CM_ALLCODE K ON K.CDNAME = 'TRREQTYPE' AND K.CDVAL = A.REQ_TYPE LEFT JOIN CM_ALLCODE I ON I.CDNAME = 'TRREQSTATUS' AND I.CDVAL = A.[STATUS] LEFT JOIN CM_ALLCODE J ON J.CDNAME='TRREQAFTER' AND J.CDVAL=A.AFTER_APPROVE LEFT JOIN ( SELECT CK.KHOI_ID,CK.KHOI_CODE,ckd.DEP_ID FROM CM_KHOI ck LEFT JOIN CM_KHOI_DT ckd ON ck.KHOI_ID = ckd.KHOI_ID ) KHOI ON KHOI.DEP_ID=A.DEP_ID LEFT JOIN dbo.PL_REQUEST_PROCESS PRP ON PRP.REQ_ID=A.REQ_ID AND (PRP.ID = (SELECT TOP (1) ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID IN (SELECT STATUS FROM @TempSTATUS ) AND PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID ORDER BY ID DESC) OR ( NOT EXISTS(SELECT STATUS FROM @TempSTATUS) AND PRP.PROCESS_ID IN (SELECT T.PARENT_PROCESS_ID FROM dbo.PL_REQUEST_PROCESS T WHERE T.REQ_ID=A.REQ_ID AND T.PROCESS_ID=@p_STATUS) OR ((PRP.PROCESS_ID='DVKD_XN' ) AND @p_STATUS='DONE'))) --LEFT JOIN dbo.CM_AUTH_STATUS AUTH ON AUTH.AUTH_STATUS = @p_AUTH_STATUS --LEFT JOIN dbo.PL_REQUEST_PROCESS RQT ON RQT.REQ_ID = A.REQ_ID --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR (RQT.PROCESS_ID = A.STATUS AND EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)))) --LEFT JOIN dbo.PL_REQUEST_PROCESS PRP1 ON PRP1.REQ_ID = A.REQ_ID AND PRP1.PROCESS_ID = 'CVMS_N' LEFT JOIN dbo.PL_REQUEST_PROCESS PRP2 ON PRP2.REQ_ID = A.REQ_ID AND PRP2.PROCESS_ID = 'DVKD' LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=A.MAKER_ID WHERE 1 = 1 AND (G.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '') AND (CONVERT(DATETIME,@p_DVKD_MANAGE_APP_FROM,103) <= CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_FROM IS NULL OR @p_DVKD_MANAGE_APP_FROM = '') AND (DATEADD(day,1,CONVERT(DATETIME,@p_DVKD_MANAGE_APP_TO,103)) > CAST (PRP2.RECEPTION_DT AS DATE) OR @p_DVKD_MANAGE_APP_TO IS NULL OR @p_DVKD_MANAGE_APP_TO = '') --AND (((RQT.PROCESS_ID = A.STATUS AND RQT.ROLE_USER IN (SELECT ROLE_USER FROM @ROLE_LOGIN) AND (@p_USERNAME = RQT.DVKD_USER_APP OR (RQT.DVKD_USER_APP IS NULL OR RQT.DVKD_USER_APP = ''))) OR (RQT.STATUS = 'U' AND RQT.PROCESS_ID = 'DVKD')) OR EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN))) --AND ((@p_AUTH_STATUS = 'U' AND (RQT.STATUS = 'C' OR RQT.STATUS = 'U')) OR (@p_AUTH_STATUS = 'A' AND (RQT.STATUS = 'P' OR RQT.STATUS IS NULL))OR (@p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')) AND (((A.MAKER_ID=@p_USERNAME OR( (G.BRANCH_TYPE='HS' AND EXISTS(SELECT T.DEP_ID FROM @lstBRANCH_DEP T WHERE T.BRANCH_ID=A.BRANCH_ID AND T.DEP_ID=A.DEP_ID)) OR( (G.BRANCH_TYPE<>'HS' AND EXISTS(SELECT T.BRANCH_ID FROM @lstBRANCH_DEP T WHERE T.BRANCH_ID=A.BRANCH_ID)) ) ) ) AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') ) OR EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND (PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND ((@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P') OR (@p_AUTH_STATUS<>'A' AND @p_AUTH_STATUS<>'U') OR @p_AUTH_STATUS IS NULL) --AND (((PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND (@p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)) OR -- (@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P')) AND (PL_REQUEST_PROCESS.DVKD_USER_APP IS NULL OR PL_REQUEST_PROCESS.DVKD_USER_APP ='') AND PL_REQUEST_PROCESS.ROLE_USER in (SELECT r.DisplayName FROM TL_USER u LEFT JOIN AbpUserRoles ur ON ur.UserId = u.ID LEFT JOIN AbpRoles r ON ur.RoleId = r.Id where u.TLNANME=@p_USERNAME) AND BRANCH_ID=@BRANCH_ID AND ( EXISTS(SELECT * FROM dbo.CM_BRANCH WHERE CM_BRANCH.BRANCH_ID = PL_REQUEST_PROCESS.BRANCH_ID AND CM_BRANCH.BRANCH_TYPE<>'HS') OR DEP_ID IS NULL OR DEP_ID='' OR DEP_ID IN (SELECT DEP_ID FROM dbo.CM_KHOI_DT WHERE KHOI_ID IN (SELECT KHOI_ID FROM dbo.CM_KHOI_DT WHERE DEP_ID=@DEP_ID)) ) AND (COST_ID IN (SELECT COST_ID FROM @lstCOST) OR COST_ID ='' OR COST_ID IS NULL) ) OR EXISTS (SELECT * FROM dbo.PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND DVKD_USER_APP = @p_USERNAME AND (PL_REQUEST_PROCESS.STATUS='C' OR PL_REQUEST_PROCESS.STATUS='P') AND ((@p_AUTH_STATUS='U' AND PL_REQUEST_PROCESS.STATUS='C') OR (@p_AUTH_STATUS='A' AND PL_REQUEST_PROCESS.STATUS='P') OR (@p_AUTH_STATUS<>'U' AND @p_AUTH_STATUS<>'A') OR @p_AUTH_STATUS IS NULL)) OR ( EXISTS(SELECT COST_ID FROM dbo.TR_REQUEST_COSTCENTER TRCO WHERE TRCO.REQ_ID=A.REQ_ID AND TRCO.COST_ID IN (SELECT COST_ID FROM @lstCOST) ) AND NOT EXISTS(SELECT ID FROM PL_REQUEST_PROCESS PRPC2 WHERE PRPC2.REQ_ID =A.REQ_ID AND PRPC2.COST_ID IN (SELECT COST_ID FROM @lstCOST)) AND @p_AUTH_STATUS<>'U' ) OR( EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_ROLE' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)) OR (EXISTS(SELECT * FROM PL_CONFIG_DATA WHERE TYPE_DATA = 'REQUEST_DOC_SLAS' AND REF_ID IN (SELECT ROLE_USER FROM @ROLE_LOGIN)) AND A.REQ_DT >='2021-09-01') ) AND ((A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') )) AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR @p_REQ_CODE IS NULL OR @p_REQ_CODE = '') AND (A.REQ_ID LIKE '%' + @p_REQ_ID + '%' OR @p_REQ_ID IS NULL OR @p_REQ_ID = '') AND (A.REQ_CONTENT LIKE '%' + @p_REQ_CONTENT + '%' OR @p_REQ_CONTENT IS NULL OR @p_REQ_CONTENT = '') AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0) AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '') AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '') AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '') AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '') --AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') --AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL) AND (A.REQ_TYPE = @p_REQ_TYPE OR @p_REQ_TYPE IS NULL OR @p_REQ_TYPE =-1) AND (A.STATUS = @p_STATUS OR @p_STATUS IS NULL OR @p_STATUS = '' OR @p_STATUS='WAITAPP' OR @p_STATUS='DONEAPP' OR (@p_STATUS ='WAITPO' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS ='CREATEDPO' AND A.AUTH_STATUS='A' AND EXISTS (SELECT PO.PO_ID FROM dbo.TR_PO_MASTER PO WHERE PO.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ADDNEW_ID FROM dbo.ASS_ADDNEW_PO ASSPO INNER JOIN dbo.TR_PO_MASTER PM ON PM.PO_ID=ASSPO.PO_ID WHERE PM.REQ_DOC_ID=A.REQ_ID)) OR (@p_STATUS='WAITASS_USE' AND A.AUTH_STATUS='A' AND A.STATUS='APPROVE' AND NOT EXISTS(SELECT ASS_USE.USER_MASTER_ID FROM dbo.ASS_USE_MULTI_MASTER ASS_USE WHERE ASS_USE.REQ_ID=A.REQ_ID AND ASS_USE.AUTH_STATUS='A')) OR (@p_STATUS = 'QLTS_N' AND A.STATUS LIKE '%QLTS%') OR (@p_STATUS = 'DVCM_XN' AND EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS PRC WHERE PRC.REQ_ID=A.REQ_ID AND PRC.PROCESS_ID='DVCM' AND PRC.STATUS='P')) OR(@p_STATUS='IN_PROCESS' AND EXISTS(SELECT ID FROM PL_REQUEST_PROCESS PRC2 WHERE PRC2.REQ_ID=A.REQ_ID AND PRC2.STATUS='C')) OR(@p_STATUS='CVMS_REJECT' AND EXISTS(SELECT * FROM dbo.PL_PROCESS PLR WHERE (PLR.PROCESS_DESC='Reject' OR PLR.PROCESS_DESC='REJECT_QLTS' OR PLR.PROCESS_DESC='REJECT_DVCM') AND PROCESS_ID='CVMS_N' AND PLR.REQ_ID=A.REQ_ID)) ) --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) --AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH)) --AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' OR EXISTS(SELECT REQ_DOC_ID FROM TR_REQUEST_DOC_DT WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH AND REQ_DOC_ID = A.REQ_ID)) AND A.RECORD_STATUS = '1' AND (@p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.BRANCH_ID = @p_BRANCH_ID) AND (@p_DEP_ID IS NULL OR @p_DEP_ID = '' OR A.DEP_ID IN (SELECT DEP_ID FROM @tbDep)) AND (A.REQ_NAME COLLATE Latin1_general_CI_AI Like N'%' + @p_REQ_NAME + N'%' COLLATE Latin1_general_CI_AI OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') AND( TU.TLFullName COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR TU.TLNANME COLLATE Latin1_general_CI_AI Like N'%' + @p_MAKER_ID + N'%' COLLATE Latin1_general_CI_AI OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '') AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT LEFT JOIN dbo.TR_CONTRACT TC ON TC.CONTRACT_ID=DTT.CONTRACT_ID INNER JOIN @REQUEST_DOC_DT_TABLE_CC DT_1 ON DT_1.CONTRACT_CODE = ISNULL(TC.CONTRACT_CODE,DTT.CONTRACT_CODE) WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_CONTRACT_CODE IS NULL OR @p_LIST_CONTRACT_CODE = '' ) AND (EXISTS(SELECT * FROM TR_REQUEST_DOC_DT DTT INNER JOIN @REQUEST_DOC_DT_TABLE_M DT_1 ON DT_1.MSTT = DTT.MSTT WHERE DTT.REQ_DOC_ID = A.REQ_ID) OR @p_LIST_MSTT IS NULL OR @p_LIST_MSTT = '' ) ORDER BY PRP.APPROVE_DT,A.REQ_DT DESC -- PAGING END END END END -- PAGING