SELECT  A.REQ_CODE AS N'Số phiếu yêu cầu', CONVERT(DATE, A.REQ_DT,103) AS NGAY_YEU_CAU,CONVERT(DATE, P.TRANFER_DT,103) AS NGAY_KIEM_SOAT ,
A.REQ_REASON AS N'Nội dung',X.SUM_MS_TOTRINH AS N'Chủ trương', X.SUM_MS_THUCTE AS N'Thực tế', (SELECT TLNAME FROM 
															PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =A.REQ_ID AND TYPE_JOB ='XL' AND PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_ID AND PROCESS_ID ='DMMS') ) AS N'Người thực hiện mua sắm', BR.BRANCH_NAME AS N'Đơn vị yêu cầu',
 DP.DEP_NAME AS N'Phòng ban yêu cầu',
 CASE WHEN A.PROCESS_ID ='APPROVE' THEN N'Hoàn tất' 
 WHEN A.PROCESS_ID ='DMMS' AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='C') THEN N'Đang xử lý' 
 WHEN A.PROCESS_ID ='DMMS' AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='P')
							AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND STATUS_JOB ='C')
 THEN N'Đang chờ KSV phê duyệt'
 WHEN A.PROCESS_ID ='DMMS' AND NOT EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL')
							AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND STATUS_JOB ='C')
 THEN N'Đang chờ KSV điều phối'
 WHEN A.PROCESS_ID ='DMMS' AND NOT EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='XL' AND STATUS_JOB ='P')
							AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='KS' AND STATUS_JOB ='P')
							AND EXISTS(SELECT TOP 1 REQ_ID FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_ID AND TYPE_JOB ='TP' AND STATUS_JOB ='C')
 THEN N'Đang chờ trưởng ĐMMS phê duyệt'
 WHEN A.PROCESS_ID ='DVCM' THEN N'Chờ DVCM xử lý'
 WHEN A.PROCESS_ID ='GDK_PYC' THEN N'Chờ giám đốc khối hỗ trợ phê duyệt'
 ELSE N'ĐMMS từ chối. ĐVKD đang thực hiện lại'
 END
 AS N'Tình trạng thực hiện'
 FROM  TR_REQUEST_DOC A
INNER JOIN
(
SELECT  REQ_DOC_ID,SUM(TOTAL_AMT) AS SUM_MS_THUCTE, SUM(TOTAL_AMT_ETM) AS SUM_MS_TOTRINH FROM TR_REQUEST_DOC_DT 
GROUP BY REQ_DOC_ID
) X ON A.REQ_ID = X.REQ_DOC_ID
INNER JOIN CM_BRANCH BR ON A.BRANCH_CREATE = BR.BRANCH_ID
LEFT JOIN CM_DEPARTMENT DP ON A.DEP_CREATE = DP.DEP_ID
INNER JOIN PL_REQUEST_PROCESS_CHILD P ON A.REQ_ID =P.REQ_ID
WHERE CONVERT(DATE, P.TRANFER_DT, 103) >='2021-01-01' AND CONVERT(DATE, P.TRANFER_DT, 103) <='2021-07-31'
AND P.TLNAME IN ('vanpt2','tanvt') AND TYPE_JOB IN ('KS','XL')
ORDER BY BR.BRANCH_NAME