ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_ById] @REQ_ID varchar(15) AS --SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID= @REQ_ID SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT, ISNULL(A.REQ_TYPE,1) AS REQ_TYPE,A.SIGN_USER,TL_USER.TLFullName as SIGN_USER_NAME, A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,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_DO, A.PROCESS_ID,A.BRANCH_CREATE, A.USER_REQUEST, A.IS_KT,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.CHECKER_ID AS PREV_PROCESS_USER, --USER ĐÃ XỬ LÝ TRƯỚC ĐÓ 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,ISNULL(PLRP.ID,0) 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, 1 AS IS_ShowBTNDVCM, -----------------------BAODNQ 8/3/2022: Lấy biên bản xét giá------------------- --XG1.RECORD_ID AS RECORD_ID_1,XG2.RECORD_ID AS RECORD_ID_2,XG3.RECORD_ID AS RECORD_ID_3, CASE WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.RECORD_ID WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.RECORD_ID WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.RECORD_ID END AS RECORD_ID, CASE WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.REQ_CODE WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.REQ_CODE WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.REQ_CODE END AS BBXG_CODE, CASE WHEN XG1.RECORD_ID IS NOT NULL THEN '10_100M' WHEN XG2.RECORD_ID IS NOT NULL THEN '100_500M' WHEN XG3.RECORD_ID IS NOT NULL THEN '500M' END AS REPORT_PRICE_TYPE, CASE WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.TOTAL_SUPPLIERS WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.TOTAL_SUPPLIERS WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.TOTAL_SUPPLIERS END AS TOTAL_SUPPLIERS, -----------------------END BAODNQ------------------- ----------BAODNQ 10/6/2022 : LẤY TỜ TRÌNH CĐT------- PAC.REQ_ID AS PL_APPOINT_CONTRACTOR_ID, PAC.REQ_CODE AS PL_APPOINT_CONTRACTOR_CODE, ----------BAODNQ 20/7/2022 : Lấy DVDM_ID của DMMS----- ( SELECT CDV.DVDM_ID FROM CM_DMMS CD LEFT JOIN PL_COSTCENTER_DT PCD ON CD.BRANCH_ID = PCD.BRANCH_ID AND CD.DEP_ID = PCD.DEP_ID LEFT JOIN PL_COSTCENTER PC ON PCD.COST_ID = PC.COST_ID LEFT JOIN CM_DVDM CDV ON PC.DVDM_ID = CDV.DVDM_ID WHERE CD.DMMS_ID = (SELECT DVDM_ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID = 'DMMS' AND REQ_ID = @REQ_ID) AND CDV.IS_DVCM = 1 ) AS DVDM_DMMS_ID, STUFF( (SELECT ',' + TRN_TYPE FROM CM_TRAN_TYPE --WHERE TRN_TYPE IN ('TRN0000000003', 'TRN0000000006', 'TRN0000000007') FOR XML PATH('')), 1, 1, '') AS BBXG_TRN_TYPE, STUFF( (SELECT ',' + TRN_TYPE FROM CM_TRAN_TYPE --WHERE TRN_TYPE = 'TRN0000000003' FOR XML PATH('')), 1, 1, '') AS CDT_TRN_TYPE FROM TR_REQUEST_DOC A LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS ='C' 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 ( select TLNANME,TLFullName from TL_USER )TL_USER ON TL_USER.TLNANME=A.SIGN_USER --Cuong LX LEFT JOIN dbo.TR_REQ_DOC_XETGIA_DUOI_100M XG1 ON XG1.REQ_DOC_ID=A.REQ_ID LEFT JOIN dbo.TR_REQ_DOC_XETGIA_100M_500M XG2 ON XG2.REQ_DOC_ID=A.REQ_ID LEFT JOIN dbo.TR_REQ_DOC_XETGIA_TREN_500M XG3 ON XG3.REQ_DOC_ID=A.REQ_ID --------------BAODNQ 10/6/2022 : LEFT JOIN TỜ TRÌNH CHỈ ĐỊNH THẦU-------- LEFT JOIN PL_APPOINT_CONTRACTOR PAC ON A.REQ_ID = PAC.TR_REQUEST_DOC_ID ----------------------END BAODNQ----------------------- WHERE 1 = 1 AND A.REQ_ID = @REQ_ID