|
1
|
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 ,
|
|
2
|
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
|
|
3
|
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',
|
|
4
|
DP.DEP_NAME AS N'Phòng ban yêu cầu',
|
|
5
|
CASE WHEN A.PROCESS_ID ='APPROVE' THEN N'Hoàn tất'
|
|
6
|
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ý'
|
|
7
|
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')
|
|
8
|
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')
|
|
9
|
THEN N'Đang chờ KSV phê duyệt'
|
|
10
|
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')
|
|
11
|
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')
|
|
12
|
THEN N'Đang chờ KSV điều phối'
|
|
13
|
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')
|
|
14
|
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')
|
|
15
|
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')
|
|
16
|
THEN N'Đang chờ trưởng ĐMMS phê duyệt'
|
|
17
|
END
|
|
18
|
AS N'Tình trạng thực hiện'
|
|
19
|
FROM TR_REQUEST_DOC A
|
|
20
|
INNER JOIN
|
|
21
|
(
|
|
22
|
SELECT REQ_DOC_ID,SUM(TOTAL_AMT) AS SUM_MS_THUCTE, SUM(TOTAL_AMT_ETM) AS SUM_MS_TOTRINH FROM TR_REQUEST_DOC_DT
|
|
23
|
GROUP BY REQ_DOC_ID
|
|
24
|
) X ON A.REQ_ID = X.REQ_DOC_ID
|
|
25
|
INNER JOIN CM_BRANCH BR ON A.BRANCH_CREATE = BR.BRANCH_ID
|
|
26
|
LEFT JOIN CM_DEPARTMENT DP ON A.DEP_CREATE = DP.DEP_ID
|
|
27
|
INNER JOIN PL_REQUEST_PROCESS_CHILD P ON A.REQ_ID =P.REQ_ID
|
|
28
|
WHERE CONVERT(DATE, P.TRANFER_DT, 103) >='2021-01-01' AND CONVERT(DATE, P.TRANFER_DT, 103) <='2021-06-30'
|
|
29
|
AND P.TLNAME IN ('vanpt2','tanvt') AND TYPE_JOB IN ('KS','XL')
|
|
30
|
ORDER BY BR.BRANCH_NAME
|