USE [gAMSPro_VietcapitalBank_v2] GO /****** Object: StoredProcedure [dbo].[TR_REQUEST_DOC_Search] Script Date: 04-Sep-20 12:45:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_Search] @p_REQ_ID varchar(15) = NULL, @p_REQ_CODE varchar(50) = NULL, @p_PL_REQ_CODE varchar(50) = NULL, @p_REQ_NAME nvarchar(200) = NULL, @p_REQ_DT DATETIME = NULL, @p_REQ_CONTENT NVARCHAR(1000)=NULL, @p_TOTAL_AMT decimal = NULL, @p_NOTES nvarchar(1000) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_MAKER_ID varchar(12) = NULL, @p_CREATE_DT DATETIME = NULL, @p_AUTH_STATUS varchar(50) = NULL, @p_CHECKER_ID varchar(12) = NULL, @p_APPROVE_DT DATETIME = NULL, @p_BRANCH_DO VARCHAR(15) = NULL, @p_BRANCH_CREATE VARCHAR(15) = NULL, @p_USER_REQUEST VARCHAR(15) = NULL, @p_BRANCH_LOGIN VARCHAR(15)=NULL, @p_TLNAME_USER VARCHAR(20)=NULL, @p_ROLE_USER VARCHAR(20), @p_TOP INT = 10, @p_PROCESS_STATUS varchar(50) = NULL, @p_FR_DATE DATETIME = NULL, @p_TO_DATE DATETIME = NULL, @p_TYPE VARCHAR(15), @p_TYPE_TRANFER VARCHAR(15)=NULL, @p_YEAR INT =NULL, @p_IS_TRANSFER VARCHAR(10) = NULL AS BEGIN IF(@p_ROLE_USER ='KTT') SET @p_ROLE_USER ='GDDV' DECLARE @DEP_ID VARCHAR(15) DECLARE @COST_ID TABLE ( COST_ID VARCHAR(15), DVDM_ID VARCHAR(15) ) SET @DEP_ID =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_TLNAME_USER) INSERT INTO @COST_ID SELECT DT.COST_ID,PC.DVDM_ID FROM dbo.PL_COSTCENTER_DT DT LEFT JOIN dbo.PL_COSTCENTER PC ON PC.COST_ID=DT.COST_ID WHERE DEP_ID=@DEP_ID AND BRANCH_ID=@p_BRANCH_LOGIN IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0 OR @P_TOP>0) BEGIN IF(@p_TYPE='DVKD' ) BEGIN SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME, D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME, CMS.DMMS_NAME,A.DMMS_ID, RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME, RP.ROLE_USER, CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME, RPN.NOTES AS PROCESS_STATUS_NEXT ,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE, DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE, DVC.BRANCH_NAME AS BRANCH_CREATE_NAME, PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME) AS TRANFER_DT , '' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID, RPN.STATUS AS STATUS_NEXT, RP.STATUS AS STATUS_CURR, '' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE, A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER FROM TR_REQUEST_DOC A LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID LEFT JOIN ( SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM dbo.PL_REQUEST_PROCESS GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES ) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R') LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC) LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID LEFT JOIN ( SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM dbo.CM_DMMS LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID UNION ALL SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME FROM dbo.CM_DVDM )CMS ON CMS.DMMS_ID=A.DMMS_ID LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME WHERE 1 = 1 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 LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') 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 (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='') --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE)) AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE)) AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '') AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '') AND A.RECORD_STATUS = '1' AND (A.MAKER_ID=@p_TLNAME_USER OR (EXISTS(SELECT * FROM dbo.PL_REQUEST_PROCESS PLRP WHERE A.REQ_ID=PLRP.REQ_ID AND PLRP.PROCESS_ID='APPNEW' AND (PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND PLRP.BRANCH_ID=@p_BRANCH_LOGIN AND (PLRP.DEP_ID=@DEP_ID OR PLRP.DEP_ID IS NULL OR PLRP.DEP_ID =''))) OR (A.SIGN_USER IS NOT NULL AND A.SIGN_USER <>'' AND A.SIGN_USER =@p_TLNAME_USER AND A.AUTH_STATUS <>'E')) AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR) ORDER BY A.CREATE_DT DESC END ELSE IF(@p_TYPE='TFJOB') BEGIN SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME, D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME, CMS.DMMS_NAME,A.DMMS_ID, RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME, RP.ROLE_USER, CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME, RPN.NOTES AS PROCESS_STATUS_NEXT ,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE, DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE, DVC.BRANCH_NAME AS BRANCH_CREATE_NAME, PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID, '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID, RPN.STATUS AS STATUS_NEXT, PLRP.STATUS AS STATUS_CURR, '' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER FROM TR_REQUEST_DOC A INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS = 'C' AND PLRP.IS_HAS_CHILD=1 LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID LEFT JOIN ( SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM dbo.PL_REQUEST_PROCESS GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES ) RPN ON RPN.REQ_ID=A.REQ_ID AND ( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R') LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC) LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME LEFT JOIN ( SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM dbo.CM_DMMS LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID UNION ALL SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME FROM dbo.CM_DVDM )CMS ON CMS.DMMS_ID=A.DMMS_ID LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME WHERE 1 = 1 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 LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') 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 (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='') --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE)) AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE)) -- AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '') AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '') AND A.RECORD_STATUS = '1' AND ( ( ( (PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) ) OR ((A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND DEP_ID=@DEP_ID) OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID)) AND A.PROCESS_ID='DMMS')))) OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND TLNAME=@p_TLNAME_USER AND TYPE_JOB='KS') )) AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR) AND (RPC.TYPE_JOB <>'TP' OR RPC.TYPE_JOB IS NULL OR RPC.TYPE_JOB ='') AND ( ((RPC.REQ_ID IS NOT NULL OR RPC.REQ_ID <> '') AND @p_IS_TRANSFER = 'Y') OR ((RPC.REQ_ID IS NULL OR RPC.REQ_ID = '') AND @p_IS_TRANSFER = 'N') OR @p_IS_TRANSFER = '' OR @p_IS_TRANSFER IS NULL ) ORDER BY A.CREATE_DT DESC END ELSE IF(@p_TYPE='DMMS') BEGIN SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME, D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME, CMS.DMMS_NAME,A.DMMS_ID, RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME, RP.ROLE_USER, CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME, RPN.NOTES AS PROCESS_STATUS_NEXT ,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE, DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE, DVC.BRANCH_NAME AS BRANCH_CREATE_NAME, PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID, '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID, RPN.STATUS AS STATUS_NEXT, PLRP.STATUS AS STATUS_CURR, RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER FROM TR_REQUEST_DOC A INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <>'U' AND PLRP.PROCESS_ID = 'DMMS' LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID LEFT JOIN ( SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM dbo.PL_REQUEST_PROCESS GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES ) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R') LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC) LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT' LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME LEFT JOIN ( SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM dbo.CM_DMMS LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID UNION ALL SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME FROM dbo.CM_DVDM )CMS ON CMS.DMMS_ID=A.DMMS_ID LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME WHERE 1 = 1 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 LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') 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 (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (RPC.TYPE_JOB LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='') --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE)) AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE)) -- AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '') AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '') AND A.RECORD_STATUS = '1' AND ( ((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ( ( A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND DEP_ID=@DEP_ID) OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID) ))) OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DMMS'))) AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR) ORDER BY A.CREATE_DT DESC END ELSE IF(@p_TYPE='PDYC') BEGIN SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME, D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME, CMS.DMMS_NAME,A.DMMS_ID, RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME, PLRP.ROLE_USER, CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME, RPN.NOTES AS PROCESS_STATUS_NEXT ,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE, DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE, DVC.BRANCH_NAME AS BRANCH_CREATE_NAME, PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID, '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID, RPN.STATUS AS STATUS_NEXT, PLRP.STATUS AS STATUS_CURR, RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE, A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER FROM TR_REQUEST_DOC A INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U' LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID LEFT JOIN ( SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM dbo.PL_REQUEST_PROCESS GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES ) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R') LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC) LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.REQ_ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME LEFT JOIN ( SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM dbo.CM_DMMS LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID UNION ALL SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME FROM dbo.CM_DVDM )CMS ON CMS.DMMS_ID=A.DMMS_ID LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME WHERE 1 = 1 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 LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') 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 (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='') --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE)) AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE)) -- AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '') AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '') AND A.RECORD_STATUS = '1' AND (PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND PLRP.PROCESS_ID <>'DMMS' AND PLRP.PROCESS_ID<>'APPNEW' AND ( (PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) OR PLRP.DVDM_ID ='' OR PLRP.DVDM_ID IS NULL)) AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR) ORDER BY A.CREATE_DT DESC END ELSE IF(@p_TYPE='DVCM') BEGIN SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,ISNULL(A.TOTAL_AMT,0.00) AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME, D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME, CMS.DMMS_NAME,A.DMMS_ID, RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME, PLRP.ROLE_USER, CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME, RPN.NOTES AS PROCESS_STATUS_NEXT ,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE, DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE, DVC.BRANCH_NAME AS BRANCH_CREATE_NAME, PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID, '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID, RPN.STATUS AS STATUS_NEXT, PLRP.STATUS AS STATUS_CURR, RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER FROM TR_REQUEST_DOC A INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS<>'U' LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID LEFT JOIN ( SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM dbo.PL_REQUEST_PROCESS GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES ) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R') LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC) LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT' LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME LEFT JOIN ( SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM dbo.CM_DMMS LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID UNION ALL SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME FROM dbo.CM_DVDM )CMS ON CMS.DMMS_ID=A.DMMS_ID LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME WHERE 1 = 1 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 LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') 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 (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='') --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE)) AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE)) -- AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '') AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '') AND A.RECORD_STATUS = '1' AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND(((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ((PLRP.DVDM_ID IN (SELECT DVDM_ID FROM @COST_ID) ))) OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PL_REQUEST_PROCESS_CHILD.PROCESS_ID=PLRP.ID AND PLRP.PROCESS_ID='DVCM') )) AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR) ORDER BY A.CREATE_DT DESC END ELSE IF(@p_TYPE='DVKD_PARENT' ) BEGIN SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME, D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME, CMS.DMMS_NAME,A.DMMS_ID, RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME, RP.ROLE_USER, CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME, RPN.NOTES AS PROCESS_STATUS_NEXT ,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE, DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE, DVC.BRANCH_NAME AS BRANCH_CREATE_NAME, PLRD.REQ_NAME AS PL_REQ_NAME,'' TYPE_JOB,'' AS USER_JOB,'' AS USER_JOB_NAME,'' AS TRANSFER_MAKER,CAST(NULL AS DATETIME) AS TRANFER_DT , '' AS TRANSFER_MAKER_ID,'' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,RP.ID AS REF_ID, RPN.STATUS AS STATUS_NEXT, RP.STATUS AS STATUS_CURR, '' AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER FROM TR_REQUEST_DOC A LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID LEFT JOIN ( SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM dbo.PL_REQUEST_PROCESS GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES ) RPN ON RPN.REQ_ID=A.REQ_ID AND ([RPN].[STATUS]='C' OR [RPN].[STATUS]='R') LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC) LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID LEFT JOIN ( SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM dbo.CM_DMMS LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID UNION ALL SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME FROM dbo.CM_DVDM )CMS ON CMS.DMMS_ID=A.DMMS_ID LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME WHERE 1 = 1 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 LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') 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 (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='') --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE)) AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE)) AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '') AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '') AND A.RECORD_STATUS = '1' AND (A.BRANCH_CREATE =@p_BRANCH_LOGIN AND (A.DEP_CREATE=@DEP_ID OR A.DEP_CREATE ='' OR A.DEP_CREATE IS NULL)) AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR) AND (A.REQ_PARENT_ID IS NULL OR A.REQ_PARENT_ID='') ORDER BY A.CREATE_DT DESC END ELSE IF(@p_TYPE='DMMS_PARENT') BEGIN SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, A.REQ_TYPE, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT AS TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT, A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.BRANCH_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST,U.TLFullName AS USER_REQUEST_NAME, D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME, CMS.DMMS_NAME,A.DMMS_ID, RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME, RP.ROLE_USER, CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt' ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME, RPN.NOTES AS PROCESS_STATUS_NEXT ,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE, DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE, DVC.BRANCH_NAME AS BRANCH_CREATE_NAME, PLRD.REQ_NAME AS PL_REQ_NAME,RPC.TYPE_JOB,RPC.TLNAME AS USER_JOB,TU.TLFullName AS USER_JOB_NAME,TFM.TLFullName AS TRANSFER_MAKER,RPC.TRANFER_DT ,RPC.MAKER_ID AS TRANSFER_MAKER_ID, '' AS TYPE_JOB_XL,'' AS USER_JOB_XL,PLRD.REQ_CODE AS SO_TT_CT, PLRD.REQ_DT AS NGAY_NHAN_TT, PLRD.EFFEC_DT NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,PLRP.ID AS REF_ID, RPN.STATUS AS STATUS_NEXT, PLRP.STATUS AS STATUS_CURR, RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,CDC.DEP_NAME AS DEP_CREATE_NAME,CDC.DEP_CODE AS DEP_CREATE_CODE,A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME, '' AS IS_TRANSFER FROM TR_REQUEST_DOC A INNER JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <>'U' LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID LEFT JOIN ( SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM dbo.PL_REQUEST_PROCESS GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES ) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R') LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC) LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND A.PROCESS_ID <>'PDHT' LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME LEFT JOIN ( SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM dbo.CM_DMMS LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID UNION ALL SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME FROM dbo.CM_DVDM )CMS ON CMS.DMMS_ID=A.DMMS_ID LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME WHERE 1 = 1 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 LIKE '%' + @p_REQ_NAME + '%' OR @p_REQ_NAME IS NULL OR @p_REQ_NAME = '') 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 (CAST(A.CREATE_DT AS DATE)=CAST(@p_CREATE_DT AS DATE) OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '') AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '') AND (CAST(A.APPROVE_DT AS DATE) = CAST(@p_APPROVE_DT AS DATE) OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '') AND (A.PROCESS_ID LIKE '%' + @p_PROCESS_STATUS + '%' OR @p_PROCESS_STATUS IS NULL OR @p_PROCESS_STATUS='') --AND (A.BRANCH_ID IN (SELECT BRANCH_ID FROM CM_BRANCH_GETCHILDID(@p_BRANCH_ID))) AND(@p_FR_DATE IS NULL OR CAST(A.REQ_DT AS DATE) >= CAST(@p_FR_DATE AS DATE)) AND(@p_TO_DATE IS NULL OR CAST(A.REQ_DT AS DATE) <= CAST(@p_TO_DATE AS DATE)) -- AND (A.BRANCH_CREATE LIKE '%' + @p_BRANCH_CREATE + '%' OR @p_BRANCH_CREATE IS NULL OR @p_BRANCH_CREATE = '') AND (A.PL_REQ_ID LIKE '%' + @p_PL_REQ_CODE + '%' OR @p_PL_REQ_CODE IS NULL OR @p_PL_REQ_CODE = '') AND A.RECORD_STATUS = '1' AND ((PLRP.ROLE_USER=@p_ROLE_USER OR PLRP.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@p_ROLE_USER)) AND ( A.DMMS_ID IN (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID =@p_BRANCH_LOGIN AND DEP_ID=@DEP_ID) OR A.DMMS_ID IN (SELECT DVDM_ID FROM @COST_ID) )OR (EXISTS(SELECT ID FROM dbo.PL_REQUEST_PROCESS_CHILD WHERE TLNAME=@p_TLNAME_USER AND PROCESS_ID=PLRP.ID) )) AND (( @p_AUTH_STATUS='A' AND A.PROCESS_ID='APPROVE') OR( @p_AUTH_STATUS='U' AND A.PROCESS_ID <> 'APPROVE') OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '') AND(@p_YEAR IS NULL OR YEAR(A.REQ_DT)=@p_YEAR) AND (A.REQ_PARENT_ID IS NULL OR A.REQ_PARENT_ID='') ORDER BY A.CREATE_DT DESC END END END