Project

General

Profile

Download (31.2 KB)

PL_PROCESS_CURRENT_NGUOIXULY.txt

Luc Tran Van, 12/22/2020 10:09 AM

 
1 
??
2
3 
EXEC SP_HELPTEXT 'PL_PROCESS_CURRENT_NGUOIXULY'
4
5 
Tr?ng th?i: Select th?nh c?ng
6
7
8
9 
Text
10
11 
/*
12 
SELECT * FROM PL_TRADEDETAIL WHERE PLAN_ID = 'PLM000000000143'
13 
select * from TR_CONTRACT_DT WHERE CONTRACT_ID = 'TRC000000000039'
14 
[TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000143','TRC000000000039','','',''
15 
exec [TR_PO_GOODS_Search] 1,'','','','','',NULL,'','PLM000000000003','','','',''
16 
exec [TR_PO_GOODS_Search] 0,'','','','','',NULL,'','PLM000000000003','','','',''
17 
SELECT * FROM TR_CONTRACT WHERE CONTRACT_TYPE = '1'
18 
SELECT * FROM PL_MASTER
19 
*/
20
21 
CREATE FUNCTION [dbo].[PL_PROCESS_CURRENT_NGUOIXULY](
22 
@p_REQ_ID VARCHAR(20)=NULL,
23 
@p_USER_LOGIN VARCHAR(20)=NULL,
24 
@p_TYPE VARCHAR(20)=NULL,
25 
@p_IS_RETURN_NAME BIT = 1)
26 
RETURNS NVARCHAR(4000)
27 
BEGIN
28
29 
DECLARE @PROCESS_CURR VARCHAR(20),@IS_HAS_CHILD BIT, @NGUOIXULY NVARCHAR(4000), @NGUOIXULYTLNAME NVARCHAR(4000)
30
31 
SELECT TOP 1 @PROCESS_CURR = PROCESS_ID,@IS_HAS_CHILD=IS_HAS_CHILD FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID AND STATUS='C'
32
33 
IF(@p_TYPE LIKE 'TTCT%')
34 
BEGIN
35 
IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
36
37 
BEGIN
38 
select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName
39 
FROM(
40 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
41 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
42 
LEFT JOIN
43 
(
44 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
45 
LEFT JOIN(
46 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
47 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
48 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
49 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
50 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
51 
WHERE REQ_ID =@p_REQ_ID AND STATUS='C' ) RE
52 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),
53
54 
@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME
55 
FROM(
56 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
57 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
58 
LEFT JOIN
59 
(
60 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
61 
LEFT JOIN(
62 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
63 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
64 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
65 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
66 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
67 
WHERE REQ_ID =@p_REQ_ID AND STATUS='C' ) RE
68 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
69
70 
END
71 
ELSE IF(@IS_HAS_CHILD=1)
72 
BEGIN
73 
select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName
74 
FROM(
75 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTE
76 
S ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
77 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
78 
LEFT JOIN
79 
(
80 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
81 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
82 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
83 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
84 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
85 
LEFT JOIN
86 
(
87 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
88 
LEFT JOIN(
89 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
90 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
91 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
92 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
93 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
94 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
95 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),
96
97 
@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME
98 
FROM(
99 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOTE
100 
S ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
101 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
102 
LEFT JOIN
103 
(
104 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
105 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
106 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
107 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
108 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
109 
LEFT JOIN
110 
(
111 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
112 
LEFT JOIN(
113 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
114 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
115 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
116 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
117 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
118 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
119 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
120
121 
END
122
123
124 
END
125 
ELSE IF(@p_TYPE LIKE 'PYC%')
126 
BEGIN
127 
IF(@PROCESS_CURR='DMMS')
128 
BEGIN
129 
--SELECT @NGUOIXULY = STRING_AGG(RE.TLFullName,', '), @NGUOIXULYTLNAME = STRING_AGG(RE.TLNAME,', ')
130 
select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName
131 
FROM(
132 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOT
133 
ES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
134 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
135 
LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
136 
LEFT JOIN
137 
(
138 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
139 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
140 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
141 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
142 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
143 
LEFT JOIN
144 
(
145 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID FROM dbo.TL_USER TU
146 
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
147 
LEFT JOIN(
148 
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
149 
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
150 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
151 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
152 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),
153
154 
@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME
155 
FROM(
156 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOT
157 
ES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
158 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
159 
LEFT JOIN dbo.TR_REQUEST_DOC TR ON TR.REQ_ID=PL.REQ_ID
160 
LEFT JOIN
161 
(
162 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
163 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
164 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
165 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
166 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
167 
LEFT JOIN
168 
(
169 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DMMS_ID FROM dbo.TL_USER TU
170 
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=TU.TLSUBBRID
171 
LEFT JOIN(
172 
SELECT PC.DMMS_ID,PC.DEP_ID,PC.BRANCH_ID FROM dbo.CM_DMMS PC
173 
) Temp ON (Temp.DEP_ID=TU.SECUR_CODE OR CB.BRANCH_TYPE<>'HS') AND Temp.BRANCH_ID=TU.TLSUBBRID
174 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.DMMS_ID=TR.DMMS_ID) AND TempC.ID IS NULL
175 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
176 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
177
178
179 
END
180 
ELSE IF(@IS_HAS_CHILD=0 OR @IS_HAS_CHILD IS NULL)
181
182 
BEGIN
183 
--SELECT @NGUOIXULY = STRING_AGG(RE.TLFullName,', '), @NGUOIXULYTLNAME = STRING_AGG(RE.TLNAME,', ')
184 
select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName
185 
FROM(
186 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
187 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
188 
LEFT JOIN
189 
(
190 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
191 
LEFT JOIN(
192 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
193 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
194 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
195 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
196 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
197 
WHERE REQ_ID =@p_REQ_ID AND STATUS='C') RE
198 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),
199
200 
@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME
201 
FROM(
202 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,TempU.TLNANME AS TLNAME,TempU.TLFullName,PL.NOTES FROM dbo.PL_REQUEST_PROCESS PL
203 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
204 
LEFT JOIN
205 
(
206 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
207 
LEFT JOIN(
208 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
209 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
210 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
211 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
212 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
213 
WHERE REQ_ID =@p_REQ_ID AND STATUS='C') RE
214 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
215
216 
END
217 
ELSE IF(@IS_HAS_CHILD=1)
218 
BEGIN
219 
--SELECT @NGUOIXULY = STRING_AGG(RE.TLFullName,', '), @NGUOIXULYTLNAME = STRING_AGG(RE.TLNAME,', ')
220 
select @NGUOIXULY = STUFF((select ', ' + RE.TLFullName
221 
FROM(
222 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOT
223 
ES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
224 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
225 
LEFT JOIN
226 
(
227 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
228 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
229 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
230 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
231 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
232 
LEFT JOIN
233 
(
234 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
235 
LEFT JOIN(
236 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
237 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
238 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
239 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
240 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
241 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
242 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, ''),
243
244 
@NGUOIXULYTLNAME = STUFF((select ', ' + RE.TLNAME
245 
FROM(
246 
SELECT distinct PL.REQ_ID,PL.PROCESS_ID,CD.DVDM_NAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLNANME ELSE TU.TLNANME END AS TLNAME,CASE WHEN TempC.ID IS NULL THEN TempU.TLFullName ELSE TU.TLFullName END AS TLFullName,CASE WHEN TempC.ID IS NULL THEN PL.NOT
247 
ES ELSE TempC.CONTENT END AS NOTES FROM dbo.PL_REQUEST_PROCESS PL
248 
LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=PL.DVDM_ID
249 
LEFT JOIN
250 
(
251 
SELECT PRC.ID, PRC.PROCESS_ID,PRC.REQ_ID,PRC.TLNAME,AC.CONTENT FROM dbo.PL_REQUEST_PROCESS_CHILD PRC
252 
LEFT JOIN dbo.CM_ALLCODE AC ON AC.CDVAL=PRC.TYPE_JOB AND AC.CDNAME='JOB_TYPE' AND CDTYPE='REQ'
253 
WHERE (PRC.STATUS_JOB='C' OR PRC.STATUS_JOB='R' )
254 
)TempC ON TempC.PROCESS_ID =PL.ID AND PL.IS_HAS_CHILD=1
255 
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=TempC.TLNAME
256 
LEFT JOIN
257 
(
258 
SELECT TU.TLNANME,TU.TLFullName,TU.RoleName,Temp.DVDM_ID,TU.SECUR_CODE,TU.TLSUBBRID FROM dbo.TL_USER TU
259 
LEFT JOIN(
260 
SELECT PC.DVDM_ID,PD.DEP_ID,PD.BRANCH_ID FROM dbo.PL_COSTCENTER PC
261 
LEFT JOIN dbo.PL_COSTCENTER_DT PD ON PC.COST_ID=PD.COST_ID) Temp ON Temp.DEP_ID=TU.SECUR_CODE AND Temp.BRANCH_ID=TU.TLSUBBRID
262 
) TempU ON (TempU.RoleName=PL.ROLE_USER OR PL.ROLE_USER IN (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=TempU.RoleName)) AND (TempU.TLSUBBRID=PL.BRANCH_ID OR PL.BRANCH_ID IS NULL OR PL.BRANCH_ID='')
263 
AND (TempU.SECUR_CODE=PL.DEP_ID OR PL.DEP_ID IS NULL OR PL.DEP_ID='')
264 
AND (TempU.DVDM_ID=PL.DVDM_ID OR PL.DVDM_ID IS NULL OR PL.DVDM_ID ='')
265 
WHERE PL.REQ_ID =@p_REQ_ID AND PL.STATUS='C') RE
266 
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
267
268 
END
269 
END
270 
IF(@p_IS_RETURN_NAME = 1)
271 
RETURN @NGUOIXULYTLNAME
272
273 
RETURN @NGUOIXULY
274 
END
275 
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s
276
277
278
279
280
281
282
283 
Result ErrorDesc
284 
SUCCESSFULL
285