1 PACKAGE BODY CS_SR_UTIL_PKG AS
2 /* $Header: cssrutib.pls 120.1 2005/12/19 03:46 appldev ship $ */
3
4 -- ------------------------------------------------------
5 -- Get_Last_Update_Date
6 -- Get either the last update date of the request record
7 -- itself, or the last update date of the actions.
8 -- Always retrive the latest date.
9 -- ------------------------------------------------------
10
11 FUNCTION Get_Last_Update_Date (
12 p_incident_id IN NUMBER,
13 p_last_update_date IN DATE )
14 RETURN DATE IS
15 l_max_action_date DATE;
16 BEGIN
17
18 SELECT MAX(last_update_date) INTO l_max_action_date
19 FROM cs_incident_actions
20 WHERE incident_id = p_incident_id;
21
22 IF (l_max_action_date > p_last_update_date) THEN
23 return l_max_action_date;
24 ELSE
25 return p_last_update_date;
26 END IF;
27
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 return p_last_update_date;
31
32 END Get_Last_Update_Date;
33
34
35 -- ------------------------------------------------------
36 -- Get_Related_Statuses_Cnt
37 -- Return the number of related statuses for a SR or
38 -- Action type.
39 -- ------------------------------------------------------
40
41 FUNCTION Get_Related_Statuses_Cnt (
42 p_incident_type_id IN NUMBER ) RETURN NUMBER IS
43 l_count NUMBER;
44 BEGIN
45
46 SELECT count(*) INTO l_count
47 FROM cs_incident_cycle_steps
48 WHERE incident_type_id = p_incident_type_id
49 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
50 AND trunc(nvl(end_date_active, sysdate));
51 return l_count;
52 END;
53
54 -- ------------------------------------------------------
55 -- New function added here for Field Service enhancement# 1520471
56 -- scheduler_is_installed
57 -- Returns True or FalsE depending on whether
58 -- scheduler is installed or not
59 -- ------------------------------------------------------
60
61 FUNCTION scheduler_is_installed return varchar2
62 IS
63 l_version varchar2(2000) := null;
64
65 cursor c_app
66 is
67 select max(i.product_version)
68 from fnd_product_installations i
69 , fnd_application a
70 where i.application_id = a.application_id
71 and a.application_short_name = 'CSR'
72 and i.status = 'I';
73 BEGIN
74 open c_app;
75 fetch c_app into l_version;
76 if c_app%found
77 then
78 if l_version is not null
79 then
80 return CS_CORE_UTIL.get_g_true;
81 end if;
82 end if;
83 close c_app;
84
85 -- csf_message.debug('Scheduler version '||nvl(l_version,'<none>'));
86
87 return CS_CORE_UTIL.get_g_false;
88 END scheduler_is_installed;
89
90 -- ------------------------------------------------------
91 -- Get_Default_Values
92 -- ------------------------------------------------------
93
94 PROCEDURE Get_Default_Values(
95 p_default_type_id IN OUT NOCOPY NUMBER,
96 p_default_type IN OUT NOCOPY VARCHAR2,
97 p_default_type_workflow IN OUT NOCOPY VARCHAR2,
98 p_default_type_workflow_nm IN OUT NOCOPY VARCHAR2,
99 p_default_type_cnt IN OUT NOCOPY NUMBER,
100 p_default_severity_id IN OUT NOCOPY NUMBER,
101 p_default_severity IN OUT NOCOPY VARCHAR2,
102 p_default_urgency_id IN OUT NOCOPY NUMBER,
103 p_default_urgency IN OUT NOCOPY VARCHAR2,
104 p_default_owner_id IN OUT NOCOPY NUMBER,
105 p_default_owner IN OUT NOCOPY VARCHAR2,
106 p_default_status_id IN OUT NOCOPY NUMBER,
107 p_default_status IN OUT NOCOPY VARCHAR2
108 ) IS
109 l_default_owner_type varchar2(100);
110 BEGIN
111
112 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_TYPE', p_default_type_id);
113 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_SEVERITY', p_default_severity_id);
114 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_URGENCY', p_default_urgency_id);
115 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_OWNER', p_default_owner_id);
116 FND_PROFILE.Get('CS_SR_DEFAULT_OWNER_TYPE', l_default_owner_type);
117 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_STATUS', p_default_status_id);
118
119 --
120 -- Get default service request type and related information
121 --
122 IF (p_default_type_id IS NOT NULL) THEN
123 BEGIN
124
125 SELECT name,
126 workflow,
127 related_statuses_cnt
128 INTO p_default_type,
129 p_default_type_workflow,
130 p_default_type_cnt
131 -- 11.5.10. Changed to look at the secured view. rmanabat, 12/03/03
132 --FROM cs_incident_types_rg_v
133 FROM cs_incident_types_rg_v_sec
134 WHERE incident_type_id = p_default_type_id
135 AND incident_subtype = 'INC'
136 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
137 AND trunc(nvl(end_date_active, sysdate));
138
139 IF (p_default_type_workflow IS NOT NULL) THEN
140 BEGIN
141
142 SELECT display_name
143 INTO p_default_type_workflow_nm
144 FROM wf_runnable_processes_v
145 WHERE item_type = 'SERVEREQ'
146 AND process_name = p_default_type_workflow;
147
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 p_default_type_workflow := NULL;
151 END;
152 END IF;
153
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 p_default_type_id := NULL;
157 END;
158 END IF;
159
160 --
161 -- Get default service request severity
162 --
163 IF (p_default_severity_id IS NOT NULL) THEN
164 BEGIN
165
166 SELECT name
167 INTO p_default_severity
168 FROM cs_incident_severities
169 WHERE incident_severity_id = p_default_severity_id
170 AND incident_subtype = 'INC'
171 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
172 AND trunc(nvl(end_date_active, sysdate));
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 p_default_severity_id := NULL;
177 END;
178 END IF;
179
180 --
181 -- Get default service request urgency
182 --
183 IF (p_default_urgency_id IS NOT NULL) THEN
184 BEGIN
185
186 SELECT name
187 INTO p_default_urgency
188 FROM cs_incident_urgencies
189 WHERE incident_urgency_id = p_default_urgency_id
190 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
191 AND trunc(nvl(end_date_active, sysdate));
192
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN
195 p_default_urgency_id := NULL;
196 END;
197 END IF;
198
199 --
200 -- Get default service request owner
201 --
202 IF (p_default_owner_id IS NOT NULL) THEN
203 BEGIN
204
205 SELECT resource_name
206 INTO p_default_owner
207 FROM cs_sr_owners_v
208 WHERE resource_id = p_default_owner_id and
209 resource_type = l_default_owner_type;
210
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 p_default_owner_id := NULL;
214 WHEN TOO_MANY_ROWS THEN
215 p_default_owner_id := NULL;
216 END;
217 END IF;
218
219 --
220 -- Get default service request status
221 --
222 IF (p_default_status_id IS NOT NULL) THEN
223 BEGIN
224
225 SELECT name
226 INTO p_default_status
227 FROM cs_incident_statuses
228 WHERE incident_status_id = p_default_status_id
229 AND incident_subtype = 'INC'
230 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
231 AND trunc(nvl(end_date_active, sysdate));
232
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 p_default_status := NULL;
236 END;
237 END IF;
238
239
240
241 END Get_Default_Values;
242
243
244
245 -- ------------------------------------------------------
246 -- Get_Default_Values
247 --
248 -- Overloaded This Procedure as per sarayu . rmanabat 08/17/01
249 -- ------------------------------------------------------
250
251 PROCEDURE Get_Default_Values(
252 p_default_type_id IN OUT NOCOPY NUMBER,
253 p_default_type IN OUT NOCOPY VARCHAR2,
254 p_default_type_workflow IN OUT NOCOPY VARCHAR2,
255 p_default_type_workflow_nm IN OUT NOCOPY VARCHAR2,
256 p_default_type_cnt IN OUT NOCOPY NUMBER,
257 p_default_severity_id IN OUT NOCOPY NUMBER,
258 p_default_severity IN OUT NOCOPY VARCHAR2,
259 p_default_urgency_id IN OUT NOCOPY NUMBER,
260 p_default_urgency IN OUT NOCOPY VARCHAR2,
261 p_default_owner_id IN OUT NOCOPY NUMBER,
262 p_default_owner IN OUT NOCOPY VARCHAR2,
263 p_default_status_id IN OUT NOCOPY NUMBER,
264 p_default_status IN OUT NOCOPY VARCHAR2,
265 p_default_group_type IN OUT NOCOPY VARCHAR2,
266 p_default_group_type_name IN OUT NOCOPY VARCHAR2,
267 p_default_group_owner_id IN OUT NOCOPY NUMBER,
268 p_default_group_owner IN OUT NOCOPY VARCHAR2,
269 p_group_mandatory IN OUT NOCOPY VARCHAR2,
270 p_default_resource_type IN OUT NOCOPY VARCHAR2,
271 p_default_resource_type_name IN OUT NOCOPY VARCHAR2,
272 p_incident_owner_mandatory IN OUT NOCOPY VARCHAR2,
273 p_default_type_maint_flag IN OUT NOCOPY VARCHAR2,
274 p_default_cmro_flag IN OUT NOCOPY VARCHAR2
275 ) IS
276 l_default_owner_type varchar2(100);
277 BEGIN
278
279 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_TYPE', p_default_type_id);
280 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_SEVERITY', p_default_severity_id);
281 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_URGENCY', p_default_urgency_id);
282 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_OWNER', p_default_owner_id);
283 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_STATUS', p_default_status_id);
284 FND_PROFILE.Get('CS_SR_DEFAULT_GROUP_TYPE', p_default_group_type);
285 FND_PROFILE.Get('CS_SR_DEFAULT_GROUP_OWNER', p_default_group_owner_id);
286 FND_PROFILE.Get('CS_SR_GROUP_MANDATORY', p_group_mandatory);
287 FND_PROFILE.Get('CS_SR_OWNER_MANDATORY', p_incident_owner_mandatory);
288
289 cs_sr_security_context.set_sr_security_context('SRTYPE_ID',p_default_type_id) ;
290 --
291 -- Get default service request type and related information
292 --
293 -- Bug 4885246 . Added the CRMO flag to the sql
294 IF (p_default_type_id IS NOT NULL) THEN
295 BEGIN
296 SELECT name,
297 workflow,
298 related_statuses_cnt,
299 maintenance_flag,
300 cmro_flag
301 INTO p_default_type,
302 p_default_type_workflow,
303 p_default_type_cnt,
304 p_default_type_maint_flag,
305 p_default_cmro_flag
306 -- 11.5.10. Changed to look at the secured view. rmanabat, 12/03/03
307 --FROM cs_incident_types_rg_v
308 FROM cs_incident_types_rg_v_sec
309 WHERE incident_type_id = p_default_type_id
310 AND incident_subtype = 'INC'
311 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
312 AND trunc(nvl(end_date_active, sysdate));
313
314 IF (p_default_type_workflow IS NOT NULL) THEN
315 BEGIN
316 SELECT display_name
317 INTO p_default_type_workflow_nm
318 FROM wf_runnable_processes_v
319 WHERE item_type = 'SERVEREQ'
320 AND process_name = p_default_type_workflow;
321
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 p_default_type_workflow := NULL;
325 END;
326 END IF;
327
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 p_default_type_id := NULL;
331 END;
332 END IF;
333
334 --
335 -- Get default service request severity
336 --
337 IF (p_default_severity_id IS NOT NULL) THEN
338 BEGIN
339 SELECT name
340 INTO p_default_severity
341 FROM cs_incident_severities
342 WHERE incident_severity_id = p_default_severity_id
343 AND incident_subtype = 'INC'
344 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
345 AND trunc(nvl(end_date_active, sysdate));
346
347 EXCEPTION
348 WHEN NO_DATA_FOUND THEN
349 p_default_severity_id := NULL;
350 END;
351 END IF;
352
353 --
354 -- Get default service request urgency
355 --
356 IF (p_default_urgency_id IS NOT NULL) THEN
357 BEGIN
358 SELECT name
359 INTO p_default_urgency
360 FROM cs_incident_urgencies
361 WHERE incident_urgency_id = p_default_urgency_id
362 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
363 AND trunc(nvl(end_date_active, sysdate));
364
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 p_default_urgency_id := NULL;
368 END;
369 END IF;
370
371 -- Get default service request group type name from jtf_objects
372 IF (p_default_group_type IS NOT NULL) THEN
373 BEGIN
374 SELECT name
375 into p_default_group_type_name
376 FROM jtf_objects_vl o, jtf_object_usages ou
377 WHERE
378 o.object_code = ou.object_code AND
379 ou.object_user_code = 'RESOURCES' AND
380 o.object_code = p_default_group_type;
381
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 p_default_group_type_name := NULL;
385 WHEN TOO_MANY_ROWS THEN
386 p_default_group_type_name := NULL;
387 END;
388 END IF;
389
390 -- Get default service request group owner
391 --
392 IF (p_default_group_owner_id IS NOT NULL) and
393 (p_default_group_type IS NOT NULL) THEN
394 BEGIN
395 SELECT resource_name
396 INTO p_default_group_owner
397 FROM cs_sr_owners_v
398 WHERE resource_id = p_default_group_owner_id and
399 resource_type = p_default_group_type;
400
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 p_default_group_owner := NULL;
404 WHEN TOO_MANY_ROWS THEN
405 p_default_group_owner := NULL;
406 END;
407 END IF;
408
409 --
410 -- Get default service request owner and owner type
411 --
412 IF (p_default_owner_id IS NOT NULL) THEN
413 BEGIN
414 SELECT resource_name ,'RS_'|| category
415 INTO p_default_owner,p_default_resource_type
416 -- 11.5.10. Changed to look at the secured view. rmanabat, 12/03/03
417 --FROM jtf_rs_resource_extns_vl
418 FROM cs_jtf_rs_resource_extns_sec
419 WHERE resource_id = p_default_owner_id;
420
421 EXCEPTION
422 WHEN NO_DATA_FOUND THEN
423 p_default_owner := NULL;
424 p_default_owner_id := NULL;
425 p_default_resource_type := NULL;
426 WHEN TOO_MANY_ROWS THEN
427 p_default_owner := NULL;
428 p_default_owner_id := NULL;
429 p_default_resource_type := NULL;
430 END;
431 END IF;
432
433 --
434 -- Get default service request owner type name
435 --
436 IF (p_default_resource_type IS NOT NULL) THEN
437 BEGIN
438 SELECT name
439 into p_default_resource_type_name
440 FROM jtf_objects_vl o, jtf_object_usages ou
441 WHERE
442 o.object_code = ou.object_code AND
443 ou.object_user_code = 'RESOURCES' AND
444 o.object_code = p_default_resource_type;
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 p_default_resource_type_name := NULL;
449 WHEN TOO_MANY_ROWS THEN
450 p_default_resource_type_name := NULL;
451 END;
452 END IF;
453
454 --
455 -- Get default service request status
456 --
457 IF (p_default_status_id IS NOT NULL) THEN
458 BEGIN
459 SELECT name
460 INTO p_default_status
461 FROM cs_incident_statuses
462 WHERE incident_status_id = p_default_status_id
463 AND incident_subtype = 'INC'
464 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
465 AND trunc(nvl(end_date_active, sysdate));
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 p_default_status := NULL;
470 END;
471 END IF;
472
473 END Get_Default_Values;
474
475
476
477 END CS_SR_UTIL_PKG;
[Home] [Help]