|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_ById]
|
|
3
|
@REQ_ID varchar(15),
|
|
4
|
@p_USER_LOGIN VARCHAR(20) = NULL
|
|
5
|
AS
|
|
6
|
|
|
7
|
--SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID= @REQ_ID
|
|
8
|
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,
|
|
9
|
A.REQ_REASON, A.REQ_CONTENT,A.PL_REQ_ID,A.TOTAL_AMT, A.NOTES, A.RECORD_STATUS,A.MAKER_ID, A.CREATE_DT,
|
|
10
|
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,
|
|
11
|
D.AUTH_STATUS_NAME,UDV.TLFullName AS CHECKER_NAME,UMS.TLFullName AS USER_DVMS_NAME,UM.TLFullName AS MAKER_NAME,
|
|
12
|
CMS.DMMS_NAME,A.DMMS_ID,
|
|
13
|
RP.NOTES AS PROCESS_STATUS,RP.APPROVE_DT AS PROCESS_APP_DT,UP.TLFullName AS PROCESS_CHECKER_NAME,
|
|
14
|
RP.CHECKER_ID AS PREV_PROCESS_USER, --USER ĐÃ XỬ LÝ TRƯỚC ĐÓ
|
|
15
|
RP.ROLE_USER,
|
|
16
|
CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
|
|
17
|
ELSE N'Chờ duyệt' END AS AUTH_STATUS_PROCESS_NAME,
|
|
18
|
RPN.NOTES AS PROCESS_STATUS_NEXT
|
|
19
|
,G.BRANCH_NAME AS BRANCH_DO_NAME,G.BRANCH_CODE AS BRANCH_DO_CODE,PLRD.REQ_CODE AS PL_REQ_CODE,
|
|
20
|
DVMS.BRANCH_CODE AS BRANCH_CODE_DVMS,DVMS.BRANCH_NAME AS BRANCH_NAME_DVMS,DVC.BRANCH_CODE AS BRANCH_CREATE_CODE,
|
|
21
|
DVC.BRANCH_NAME AS BRANCH_CREATE_NAME,
|
|
22
|
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,
|
|
23
|
'' 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,
|
|
24
|
RPN.STATUS AS STATUS_NEXT,
|
|
25
|
PLRP.STATUS AS STATUS_CURR,
|
|
26
|
RPC.STATUS_JOB AS STATUS_JOB,A.REQ_PARENT_ID,A.DEP_CREATE,APR.REQ_CODE AS REQ_PARENT_CODE,
|
|
27
|
-------------BAODNQ 14/11/2022 : HOT_FIX TẠM THỜI, NẾU ĐV TẠO <> HS THÌ SHOW ĐƠN VỊ-----------------
|
|
28
|
-------------NẾU ĐV TẠO LÀ HS THÌ SHOW PHÒNG BAN-----------------------
|
|
29
|
--CDC.DEP_NAME AS DEP_CREATE_NAME,
|
|
30
|
CASE
|
|
31
|
WHEN (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH CBT WHERE CBT.BRANCH_ID = A.BRANCH_CREATE) = 'HS'
|
|
32
|
THEN CDC.DEP_NAME
|
|
33
|
ELSE DVC.BRANCH_NAME
|
|
34
|
END AS DEP_CREATE_NAME,
|
|
35
|
CASE
|
|
36
|
WHEN (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH CBT WHERE CBT.BRANCH_ID = A.BRANCH_CREATE) = 'HS'
|
|
37
|
THEN CDC.DEP_CODE
|
|
38
|
ELSE DVC.BRANCH_CODE
|
|
39
|
END AS DEP_CREATE_CODE,
|
|
40
|
-------------------ENDBAODNQ------------------------
|
|
41
|
--CDC.DEP_CODE AS DEP_CREATE_CODE,
|
|
42
|
1 AS IS_ShowBTNDVCM,
|
|
43
|
-----------------------BAODNQ 8/3/2022: Lấy biên bản xét giá-------------------
|
|
44
|
--XG1.RECORD_ID AS RECORD_ID_1,XG2.RECORD_ID AS RECORD_ID_2,XG3.RECORD_ID AS RECORD_ID_3,
|
|
45
|
CASE
|
|
46
|
WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.RECORD_ID
|
|
47
|
WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.RECORD_ID
|
|
48
|
WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.RECORD_ID
|
|
49
|
END
|
|
50
|
AS RECORD_ID,
|
|
51
|
CASE
|
|
52
|
WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.REQ_CODE
|
|
53
|
WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.REQ_CODE
|
|
54
|
WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.REQ_CODE
|
|
55
|
END
|
|
56
|
AS BBXG_CODE,
|
|
57
|
CASE
|
|
58
|
WHEN XG1.RECORD_ID IS NOT NULL THEN '10_100M'
|
|
59
|
WHEN XG2.RECORD_ID IS NOT NULL THEN '100_500M'
|
|
60
|
WHEN XG3.RECORD_ID IS NOT NULL THEN '500M'
|
|
61
|
END
|
|
62
|
AS REPORT_PRICE_TYPE,
|
|
63
|
CASE
|
|
64
|
WHEN XG1.RECORD_ID IS NOT NULL THEN XG1.TOTAL_SUPPLIERS
|
|
65
|
WHEN XG2.RECORD_ID IS NOT NULL THEN XG2.TOTAL_SUPPLIERS
|
|
66
|
WHEN XG3.RECORD_ID IS NOT NULL THEN XG3.TOTAL_SUPPLIERS
|
|
67
|
END
|
|
68
|
AS TOTAL_SUPPLIERS,
|
|
69
|
-----------------------END BAODNQ-------------------
|
|
70
|
----------BAODNQ 10/6/2022 : LẤY TỜ TRÌNH CĐT-------
|
|
71
|
PAC.REQ_ID AS PL_APPOINT_CONTRACTOR_ID,
|
|
72
|
PAC.REQ_CODE AS PL_APPOINT_CONTRACTOR_CODE,
|
|
73
|
----------BAODNQ 20/7/2022 : Lấy DVDM_ID của DMMS-----
|
|
74
|
(
|
|
75
|
SELECT CDV.DVDM_ID
|
|
76
|
FROM CM_DMMS CD
|
|
77
|
LEFT JOIN PL_COSTCENTER_DT PCD ON CD.BRANCH_ID = PCD.BRANCH_ID AND CD.DEP_ID = PCD.DEP_ID
|
|
78
|
LEFT JOIN PL_COSTCENTER PC ON PCD.COST_ID = PC.COST_ID
|
|
79
|
LEFT JOIN CM_DVDM CDV ON PC.DVDM_ID = CDV.DVDM_ID
|
|
80
|
WHERE CD.DMMS_ID = (SELECT DVDM_ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID = 'DMMS' AND REQ_ID = @REQ_ID)
|
|
81
|
AND CDV.IS_DVCM = 1
|
|
82
|
) AS DVDM_DMMS_ID,
|
|
83
|
---------BAODNQ 19/8/2022 : Lấy thêm role user bước xử lý hiện tại------------
|
|
84
|
PLRP.ROLE_USER AS CURRENT_ROLE_USER,
|
|
85
|
-----------BAODNQ 26/8/2022 : Lấy thêm loại tờ trình CDT / TTCP-----------
|
|
86
|
PAC.TYPE_PL AS PL_APP_TYPE
|
|
87
|
|
|
88
|
|
|
89
|
FROM TR_REQUEST_DOC A
|
|
90
|
--LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS ='C'
|
|
91
|
------------BAODNQ 10/11/2022: TRUYỀN THÊM USER_LOGIN---------------
|
|
92
|
LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS ='C'
|
|
93
|
AND(
|
|
94
|
(
|
|
95
|
--------------TRONG TH CÓ > 2 DVCM XỬ LÝ CÙNG LÚC THÌ LEFT JOIN THEO USER_LOGIN-----------
|
|
96
|
PLRP.PROCESS_ID = 'DVCM'
|
|
97
|
AND EXISTS(
|
|
98
|
SELECT PC.COST_ID FROM PL_COSTCENTER PC
|
|
99
|
LEFT JOIN PL_COSTCENTER_DT PCD ON PC.COST_ID = PCD.COST_ID
|
|
100
|
LEFT JOIN CM_DEPARTMENT CD ON PCD.DEP_ID = CD.DEP_ID
|
|
101
|
LEFT JOIN TL_USER TU ON PCD.BRANCH_ID = TU.TLSUBBRID AND CD.DEP_ID = TU.SECUR_CODE
|
|
102
|
WHERE (PC.DVDM_ID = PLRP.DVDM_ID OR PLRP.DVDM_ID IS NULL OR PLRP.DVDM_ID = '')
|
|
103
|
AND TU.TLNANME = @p_USER_LOGIN
|
|
104
|
)
|
|
105
|
)
|
|
106
|
OR PLRP.PROCESS_ID <> 'DVCM'
|
|
107
|
)
|
|
108
|
-----------------------ENDBAODNQ----------------------
|
|
109
|
LEFT JOIN dbo.PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
110
|
LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
|
|
111
|
LEFT JOIN CM_BRANCH G ON A.BRANCH_DO=G.BRANCH_ID
|
|
112
|
LEFT JOIN dbo.TL_USER U ON U.TLNANME =A.USER_REQUEST
|
|
113
|
LEFT JOIN dbo.TL_USER UMS ON UMS.TLNANME =A.USER_DVMS
|
|
114
|
LEFT JOIN dbo.CM_BRANCH DVMS ON DVMS.BRANCH_ID=A.BRANCH_DVMS
|
|
115
|
LEFT JOIN dbo.CM_BRANCH DVC ON DVC.BRANCH_ID=A.BRANCH_CREATE
|
|
116
|
LEFT JOIN dbo.CM_DEPARTMENT CDC ON CDC.DEP_ID=A.DEP_CREATE
|
|
117
|
LEFT JOIN dbo.TR_REQUEST_DOC APR ON APR.REQ_ID=A.REQ_PARENT_ID
|
|
118
|
LEFT JOIN dbo.TL_USER UM ON UM.TLNANME=A.MAKER_ID
|
|
119
|
LEFT JOIN
|
|
120
|
(
|
|
121
|
SELECT REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES FROM
|
|
122
|
dbo.PL_REQUEST_PROCESS
|
|
123
|
GROUP BY REQ_ID,PROCESS_ID,PARENT_PROCESS_ID,STATUS,NOTES
|
|
124
|
) RPN ON RPN.REQ_ID=A.REQ_ID AND( [RPN].[STATUS]='C' OR [RPN].[STATUS]='R')
|
|
125
|
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)
|
|
126
|
LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
|
|
127
|
LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
|
|
128
|
--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'
|
|
129
|
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' AND (RPC.TLNAME = @p_USER_LOGIN OR @p_USER_LOGIN IS NULL OR @p_USER_LOGIN = '')
|
|
130
|
LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
|
|
131
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
|
|
132
|
LEFT JOIN
|
|
133
|
(
|
|
134
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
135
|
dbo.CM_DMMS
|
|
136
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
137
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
138
|
UNION ALL
|
|
139
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
140
|
FROM dbo.CM_DVDM
|
|
141
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
142
|
LEFT JOIN (
|
|
143
|
select TLNANME,TLFullName from TL_USER
|
|
144
|
)TL_USER ON TL_USER.TLNANME=A.SIGN_USER
|
|
145
|
--Cuong LX
|
|
146
|
LEFT JOIN dbo.TR_REQ_DOC_XETGIA_DUOI_100M XG1 ON XG1.REQ_DOC_ID=A.REQ_ID
|
|
147
|
LEFT JOIN dbo.TR_REQ_DOC_XETGIA_100M_500M XG2 ON XG2.REQ_DOC_ID=A.REQ_ID
|
|
148
|
LEFT JOIN dbo.TR_REQ_DOC_XETGIA_TREN_500M XG3 ON XG3.REQ_DOC_ID=A.REQ_ID
|
|
149
|
--------------BAODNQ 10/6/2022 : LEFT JOIN TỜ TRÌNH CHỈ ĐỊNH THẦU--------
|
|
150
|
LEFT JOIN PL_APPOINT_CONTRACTOR PAC ON A.REQ_ID = PAC.TR_REQUEST_DOC_ID
|
|
151
|
----------------------END BAODNQ-----------------------
|
|
152
|
WHERE 1 = 1
|
|
153
|
AND A.REQ_ID = @REQ_ID
|