1 PACKAGE BODY csf_util_pvt AS
2 /* $Header: CSFVUTLB.pls 120.11 2007/12/18 06:23:53 ipananil ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_UTIL_PVT';
5
6 g_hsecs_old NUMBER;
7 g_seq_old NUMBER;
8 g_type_old NUMBER;
9 g_uom_minutes CONSTANT VARCHAR2(3) := fnd_profile.VALUE('CSF_UOM_MINUTES');
10 g_default_uom CONSTANT VARCHAR2(3) := fnd_profile.value('CSF_DEFAULT_EFFORT_UOM');
11
12
13 /****************************************************************
14 * FND API Constants *
15 *****************************************************************/
16
17 FUNCTION get_miss_num RETURN NUMBER IS
18 BEGIN
19 RETURN fnd_api.g_miss_num;
20 END get_miss_num;
21
22 FUNCTION get_miss_char RETURN VARCHAR2 IS
23 BEGIN
24 RETURN fnd_api.g_miss_char;
25 END get_miss_char;
26
27 FUNCTION get_miss_date RETURN DATE IS
28 BEGIN
29 RETURN fnd_api.g_miss_date;
30 END get_miss_date;
31
32 FUNCTION get_valid_level_none RETURN NUMBER IS
33 BEGIN
34 RETURN fnd_api.g_valid_level_none;
35 END get_valid_level_none;
36
37 FUNCTION get_valid_level_full RETURN NUMBER IS
38 BEGIN
39 RETURN fnd_api.g_valid_level_full;
40 END get_valid_level_full;
41
42 FUNCTION get_ret_sts_success RETURN VARCHAR2 IS
43 BEGIN
44 RETURN fnd_api.g_ret_sts_success;
45 END get_ret_sts_success;
46
47 FUNCTION get_ret_sts_error RETURN VARCHAR2 IS
48 BEGIN
49 RETURN fnd_api.g_ret_sts_error;
50 END get_ret_sts_error;
51
52 FUNCTION get_ret_sts_unexp_error RETURN VARCHAR2 IS
53 BEGIN
54 RETURN fnd_api.g_ret_sts_unexp_error;
55 END get_ret_sts_unexp_error;
56
57 FUNCTION get_true RETURN VARCHAR2 IS
58 BEGIN
59 RETURN fnd_api.g_true;
60 END get_true;
61
62 FUNCTION get_false RETURN VARCHAR2 IS
63 BEGIN
64 RETURN fnd_api.g_false;
65 END get_false;
66
67 FUNCTION get_first RETURN NUMBER IS
68 BEGIN
69 RETURN fnd_msg_pub.g_first;
70 END get_first;
71
72 FUNCTION get_next RETURN NUMBER IS
73 BEGIN
74 RETURN fnd_msg_pub.g_next;
75 END get_next;
76
77 FUNCTION get_last RETURN NUMBER IS
78 BEGIN
79 RETURN fnd_msg_pub.g_last;
80 END get_last;
81
82 FUNCTION get_previous RETURN NUMBER IS
83 BEGIN
84 RETURN fnd_msg_pub.g_previous;
85 END get_previous;
86
87 FUNCTION get_msg_lvl_unexp_error RETURN NUMBER IS
88 BEGIN
89 RETURN fnd_msg_pub.g_msg_lvl_unexp_error;
90 END get_msg_lvl_unexp_error;
91
92 FUNCTION get_msg_lvl_error RETURN NUMBER IS
93 BEGIN
94 RETURN fnd_msg_pub.g_msg_lvl_error;
95 END get_msg_lvl_error;
96
97 FUNCTION get_msg_lvl_success RETURN NUMBER IS
98 BEGIN
99 RETURN fnd_msg_pub.g_msg_lvl_success;
100 END get_msg_lvl_success;
101
102 FUNCTION get_msg_lvl_debug_high RETURN NUMBER IS
103 BEGIN
104 RETURN fnd_msg_pub.g_msg_lvl_debug_high;
105 END get_msg_lvl_debug_high;
106
107 FUNCTION get_msg_lvl_debug_medium RETURN NUMBER IS
108 BEGIN
109 RETURN fnd_msg_pub.g_msg_lvl_debug_medium;
110 END get_msg_lvl_debug_medium;
111
112 FUNCTION get_msg_lvl_debug_low RETURN NUMBER IS
113 BEGIN
114 RETURN fnd_msg_pub.g_msg_lvl_debug_low;
115 END get_msg_lvl_debug_low;
116
117 /**
118 * Returns the Address of a Location given the Location ID.
119 * <br>
120 * Supports two formats of Addresses - Default being Complete
121 * 1. Complete Address - Address 1, 2, 3, 4, ZIP, City, State, Country
122 * 2. Short Address - ZIP, City, State / Province
123 *
124 * @param p_location_id Location ID corresponding to the Address desired
125 * @param p_small_flag Short ('Y') / Complete ('N') Address (Optional)
126 */
127 FUNCTION get_address(p_location_id NUMBER, p_small_flag VARCHAR2 DEFAULT NULL)
128 RETURN VARCHAR2 IS
129 BEGIN
130 RETURN csf_tasks_pub.get_task_address(NULL, NULL, p_location_id, p_small_flag);
131 END get_address;
132
133
134 /**
135 * Adds a Timing Mark in the CSR_TIMERS_B so that we can assess the performance of
136 * each operation.
137 *
138 * @param p_seq Sequence Number of the Timer
139 * @param p_name Name of the Timing Mark Logged
140 * @param p_type
141 * @param p_descr Description of the Timing Mark Logged for better information.
142 */
143 PROCEDURE add_timer(p_seq NUMBER, p_name VARCHAR2, p_type NUMBER, p_descr VARCHAR2) IS
144 CURSOR c_timer IS
145 SELECT hsecs FROM v$timer;
146 l_idx NUMBER;
147 l_hsecs NUMBER;
148 BEGIN
149 OPEN c_timer;
150 FETCH c_timer INTO l_hsecs;
151 CLOSE c_timer;
152
153 IF p_type = 0 THEN
154 g_hsecs_old := l_hsecs;
155 g_seq_old := p_seq;
156 g_type_old := 0;
157 ELSIF p_type = 1 AND p_seq = g_seq_old THEN
158 -- found timing pair
159 -- insert directly into table, this is a quick-and-dirty solution, this
160 -- has to be replaced by storage into pl/sql table which will be copied
161 -- to csr_timers_b when Get Results button is pushed on Timing Tests
162 -- Management UI
163 BEGIN
164 INSERT INTO csr_timers_b (seq, NAME, VALUE, meaning, description)
165 VALUES (p_seq, p_name, (l_hsecs - g_hsecs_old) * 10, 'TIME', p_descr);
166
167 COMMIT WORK;
168 EXCEPTION
169 WHEN OTHERS THEN
170 NULL;
171 END;
172 ELSE
173 g_hsecs_old := NULL;
174 g_seq_old := NULL;
175 g_type_old := NULL;
176 END IF;
177 END add_timer;
178
179 /**
180 * Checks whether the given Territory ID is one among the Selected Territories
181 * of the given user. If no user is given, then it checks using the signed
182 * in user (FND_GLOBAL.USER_ID)
183 *
184 * @param p_terr_id Territory ID of the Territory to be checked
185 * @param p_user_id User ID of the user whose list of Territories is used
186 */
187 FUNCTION is_terr_selected( p_terr_id IN NUMBER
188 , p_user_id IN NUMBER DEFAULT NULL ) RETURN NUMBER
189 IS
190 l_selected_terr VARCHAR2(4000) := NULL;
191 l_return_value NUMBER := 0;
192 BEGIN
193 l_selected_terr := get_selected_terr(p_user_id);
194
195 IF l_selected_terr IS NULL THEN
196 l_return_value := -1;
197 ELSE
198 l_return_value := INSTR(',' || l_selected_terr || ',', ',' || TO_CHAR(p_terr_id) || ',');
199 END IF;
200
201 RETURN l_return_value;
202 END is_terr_selected;
203
204 /**
205 * Returns the list of Territories added to the given User.
206 *
207 * @param p_user_id User ID of the user to get the list of User's Territories
208 */
209 FUNCTION get_selected_terr( p_user_id NUMBER DEFAULT NULL ) RETURN VARCHAR2
210 IS
211 l_selected_terr VARCHAR2(4000);
212 l_terr_table jtf_number_table;
213 BEGIN
214 l_terr_table := get_selected_terr_table(p_user_id);
215
216 FOR i IN 1..l_terr_table.COUNT LOOP
217 -- Maximum permissible size of a database column of type VARCHAR2 is 4000
218 IF LENGTH(l_selected_terr || ',' || l_terr_table(i)) > 4000 THEN
219 RETURN SUBSTR(l_selected_terr,2);
220 END IF;
221 l_selected_terr := l_selected_terr || ',' || l_terr_table(i);
222 END LOOP;
223
224 RETURN SUBSTR(l_selected_terr, 2);
225 END get_selected_terr;
226
227 /**
228 * Gets the List of Territories selected as a PLSQL Table
229 *
230 * @param p_user_id User ID of the user to get the list of User's Territories
231 */
232 FUNCTION get_selected_terr_table( p_user_id NUMBER DEFAULT NULL )
233 RETURN jtf_number_table
234 IS
235 l_selected_terr VARCHAR2(4000);
236 l_terr_table jtf_number_table;
237 i PLS_INTEGER;
238 l_user_id NUMBER DEFAULT NULL;
239
240 CURSOR c_selected_terr( b_user_id NUMBER ) IS
241 SELECT terr_id
242 from csf_user_selected_terrs
243 WHERE user_id = b_user_id;
244
245 CURSOR c_plan_terr(b_resource_id NUMBER) IS
246 SELECT DISTINCT pt.terr_id
247 FROM csf_plan_terrs pt
248 , jtf_rs_group_members m
249 WHERE m.resource_id = b_resource_id
250 AND NVL(m.delete_flag, 'N') <> 'Y'
251 AND pt.group_id = m.group_id;
252
253 CURSOR c_service_terr IS
254 SELECT DISTINCT tq.terr_id
255 FROM jtf_qual_type_usgs q
256 , jtf_terr_qtype_usgs_all tq
257 WHERE q.source_id = -1002
258 AND tq.qual_type_usg_id = q.qual_type_usg_id;
259 BEGIN
260 l_terr_table := jtf_number_table();
261
262 -- Use the logged in user's ID if no USER ID is passed to this function
263 IF p_user_id IS NULL THEN
264 l_user_id := fnd_global.user_id;
265 ELSE
266 l_user_id := p_user_id;
267 END IF;
268
269 -- Get the list of selected territories corresponding to the USER ID
270 OPEN c_selected_terr( l_user_id );
271 FETCH c_selected_terr INTO l_selected_terr;
272 CLOSE c_selected_terr;
273
274 -- If the user has selected territories.. then return those.
275 IF l_selected_terr IS NOT NULL THEN
276 LOOP
277 i := INSTR(l_selected_terr, ',');
278 EXIT WHEN i = 0;
279 l_terr_table.extend(1);
280 l_terr_table(l_terr_table.LAST) := SUBSTR(l_selected_terr, 1, i-1);
281 l_selected_terr := SUBSTR(l_selected_terr, i+1);
282 END LOOP;
283 IF l_selected_terr IS NOT NULL AND LENGTH(TRIM(l_selected_terr)) > 0 THEN
284 l_terr_table.extend(1);
285 l_terr_table(l_terr_table.LAST) := l_selected_terr;
286 END IF;
287 ELSE
288 -- If the user has selected territories.. then return those.
289 OPEN c_plan_terr(csf_resource_pub.resource_id(l_user_id));
290 FETCH c_plan_terr BULK COLLECT INTO l_terr_table;
291 CLOSE c_plan_terr;
292
293 -- The given user is not attached to any Planner Group.
294 -- Return all territories under Oracle Service.
295 IF l_terr_table.COUNT = 0 THEN
296 OPEN c_service_terr;
297 FETCH c_service_terr BULK COLLECT INTO l_terr_table;
298 CLOSE c_service_terr;
299 END IF;
300 END IF;
301
302 RETURN l_terr_table;
303 END get_selected_terr_table;
304
305
306 /**
307 * Saves the list of selected territories to the database
308 *
309 * @param p_selected_terr List of Territories to be stored
310 * @param p_user_id User ID of the user for whom we are storing the list
311 */
312 PROCEDURE set_selected_terr( p_selected_terr IN VARCHAR2 DEFAULT NULL
313 , p_user_id IN NUMBER DEFAULT NULL )
314 IS
315 l_user_id NUMBER DEFAULT NULL;
316 BEGIN
317 IF p_user_id IS NULL THEN
318 l_user_id := fnd_global.user_id;
319 ELSE
320 l_user_id := p_user_id;
321 END IF;
322
323 IF p_selected_terr IS NOT NULL THEN
324 UPDATE csf_user_selected_terrs
325 SET terr_id = p_selected_terr
326 , last_updated_by = fnd_global.user_id
327 , last_update_date = SYSDATE
328 , last_update_login = fnd_global.login_id
329 , object_version_number = object_version_number + 1
330 WHERE user_id = l_user_id;
331
332 IF sql%NOTFOUND THEN
333 INSERT INTO csf_user_selected_terrs (
334 user_id
335 , terr_id
336 , last_updated_by
337 , last_update_date
338 , last_update_login
339 , created_by
340 , creation_date
341 , object_version_number
342 ) VALUES (
343 l_user_id
344 , p_selected_terr
345 , fnd_global.user_id
346 , SYSDATE
347 , fnd_global.login_id
348 , fnd_global.user_id
349 , SYSDATE
350 , 1);
351 END IF;
352 END IF;
353 END set_selected_terr;
354
355 /**
356 * Returns the Object Name given the Object Type Code and Object ID.
357 * <p>
358 * This procedure is very useful so that the TABLE NAME is not hardcoded to get
359 * the Object Name for a given Object ID. Rather it uses the table JTF_OBJECTS
360 * to get the SQL that should be used and forms a Dynamic SQL to get the Object
361 * Name
362 *
363 * @param p_object_type_code Type Code of the Object whose Name is required
364 * @param p_object_id Identifier of the Object whose Name is required
365 */
366 FUNCTION get_object_name(p_object_type_code IN VARCHAR2, p_object_id IN NUMBER)
367 RETURN VARCHAR2 IS
368 CURSOR c_ref IS
369 SELECT select_id
370 , select_name
371 , from_table
372 , where_clause
373 FROM jtf_objects_vl
374 WHERE object_code = p_object_type_code;
375 l_rec c_ref%ROWTYPE;
376 -- max data from jtf_objects_vl can be about 2600
377 l_stmt VARCHAR2(3000);
378 -- highest max col length found in dom1151 = 421
379 l_name VARCHAR2(500) := NULL;
380 BEGIN
381 OPEN c_ref;
382 FETCH c_ref INTO l_rec;
383 IF c_ref%NOTFOUND THEN
384 CLOSE c_ref;
385 RETURN NULL;
386 END IF;
387 CLOSE c_ref;
388
389 l_stmt := 'SELECT ' || l_rec.select_name || ' FROM ' || l_rec.from_table || ' WHERE ';
390 IF l_rec.where_clause IS NOT NULL THEN
391 l_stmt := l_stmt || l_rec.where_clause || ' AND ';
392 END IF;
393 l_stmt := l_stmt || l_rec.select_id || ' = :object_id';
394
395 EXECUTE IMMEDIATE l_stmt INTO l_name USING p_object_id;
396
397 RETURN l_name;
398 EXCEPTION
399 WHEN OTHERS THEN
400 RETURN NULL;
401 END get_object_name;
402
403 /**
404 * Returns the Descrption/Name of the given UOM Code
405 */
406 FUNCTION get_uom(p_code VARCHAR2)
407 RETURN VARCHAR2 IS
408 l_uom VARCHAR2(2000) := NULL;
409 CURSOR c_uom(p_code VARCHAR2) IS
410 SELECT unit_of_measure_tl
411 FROM mtl_units_of_measure_vl
412 WHERE uom_code = p_code;
413 BEGIN
414 OPEN c_uom(p_code);
415 FETCH c_uom INTO l_uom;
416 CLOSE c_uom;
417 RETURN NVL(l_uom, p_code);
418 END get_uom;
419
420 FUNCTION is_uom_valid(p_value VARCHAR2)
421 RETURN VARCHAR2 IS
422 CURSOR c_uom(p_value VARCHAR2) IS
423 SELECT 'TRUE'
424 FROM mtl_units_of_measure_vl
425 WHERE uom_code = p_value AND(TRUNC(disable_date) >= TRUNC(SYSDATE) OR disable_date IS NULL);
426 l_ret VARCHAR2(5);
427 BEGIN
428 OPEN c_uom(p_value);
429 FETCH c_uom INTO l_ret;
430 IF c_uom%NOTFOUND THEN
431 l_ret := 'FALSE';
432 END IF;
433 CLOSE c_uom;
434 RETURN l_ret;
435 END is_uom_valid;
436
437 /**
438 * Returns the Default UOM (in Minutes) profile by reading the profile
439 * "CSF: The unit of measure for minutes" (CSF_UOM_MINUTES).
440 *
441 * @return Value of "CSF: The unit of measure for minutes" (CSF_UOM_MINUTES)
442 */
443 FUNCTION get_uom_minutes RETURN VARCHAR2 AS
444 BEGIN
445 RETURN g_uom_minutes;
446 END get_uom_minutes;
447
448 /**
449 * Converts the given Duration in the given Duration UOM to the UOM as defined by the
450 * profile "CSF: The unit of measure for minutes" (CSF_UOM_MINUTES) there by
451 * converting the value to Minutes.
452 *
453 * @param p_duration Duration to be converted to Minutes UOM
454 * @param p_duration_uom Source UOM
455 */
456 FUNCTION convert_to_minutes(p_duration IN NUMBER, p_duration_uom IN VARCHAR2)
457 RETURN NUMBER AS
458 l_duration NUMBER;
459 l_uom_minutes VARCHAR2(30);
460 BEGIN
461 l_duration := p_duration;
462 l_uom_minutes := get_uom_minutes;
463 IF l_uom_minutes IS NOT NULL AND p_duration_uom IS NOT NULL THEN
464 l_duration :=
465 inv_convert.inv_um_convert(0, 0, p_duration, p_duration_uom, l_uom_minutes, NULL, NULL);
466 IF l_duration < 0 THEN
467 l_duration := p_duration;
468 END IF;
469 END IF;
470 RETURN l_duration;
471 EXCEPTION
472 WHEN OTHERS THEN
473 RETURN l_duration;
474 END convert_to_minutes;
475
476 /**
477 * Gets the Task Effort along with the UOM after converting the effort so
478 * as to represent it in the Default UOM "CSF: Default Effort UOM".
479 * <br>
480 * Its better for this API to be called only for Child Tasks so that they
481 * are appropriately represented in a better UOM rather than the UOM used by
482 * Scheduler (Minutes) to create the Child Task.
483 * For Parent Tasks / Normal Tasks, the effort and its UOM should not be
484 * converted as they are entered by the Teleservice Operators.
485 * <br>
486 * Suppose the effort cannot be represented as a Whole Number in the Default
487 * UOM then the effort will be represented as a combination of many UOMs.
488 * <br>
489 * Examples
490 * --------
491 * CSF: Default Effort UOM - HR.
492 *
493 * -------------------------------------------------------
494 * | Input Effort | Input UOM | Output |
495 * -------------------------------------------------------
496 * | | | |
497 * | 50 | MIN | 50 Minute |
498 * | 60 | MIN | 1 Hour |
499 * | 70 | MIN | 1 Hour 10 Minute |
500 * | 1500 | MIN | 25 Hour |
501 * | 2 | HR | 2 Hour |
502 * | 2 | DAY | 48 Hour |
503 * -------------------------------------------------------
504 *
505 * <br>
506 * @param p_effort Effort which needs to be converted
507 * @param p_effort_uom Effort UOM of the above Task Effort
508 *
509 * @return Effort appro converted to Default UOM followed by "UOM Full Form"
510 */
511 FUNCTION get_effort_in_default_uom(p_effort NUMBER, p_effort_uom VARCHAR2)
512 RETURN VARCHAR2 IS
513 l_result VARCHAR2(100);
514 l_converted_effort NUMBER;
515 l_remaining_effort NUMBER;
516 l_uom_rate NUMBER;
517 BEGIN
518
519 IF p_effort_uom = g_default_uom THEN
520 l_result := p_effort || ' ' || csf_util_pvt.get_uom(p_effort_uom);
521 ELSE
522 inv_convert.inv_um_conversion(p_effort_uom, g_default_uom, NULL, l_uom_rate);
523
524 l_converted_effort := TRUNC(ROUND(p_effort * l_uom_rate, 5));
525 l_remaining_effort := TRUNC(p_effort - ROUND(l_converted_effort/l_uom_rate, 5));
526
527 IF l_converted_effort <> 0 THEN
528 l_result := l_converted_effort || ' ' || csf_util_pvt.get_uom(g_default_uom);
529 END IF;
530
531 IF l_remaining_effort <> 0 THEN
532 IF l_result IS NOT NULL THEN
533 l_result := l_result || ' ';
534 END IF;
535 l_result := l_result || l_remaining_effort || ' ' || csf_util_pvt.get_uom(p_effort_uom);
536 END IF;
537 END IF;
538
539 RETURN l_result;
540 END get_effort_in_default_uom;
541
542 /**
543 * (<b>Deprecated</b>) Retained because Service Team is still using it.
544 * Returns the Qualifier Table having the list of valid Qualifiers
545 * based on the Task Information of the given Task ID.
546 *
547 * @deprecated Use CSF_RESOURCE_PUB.GET_RES_QUALIFIER_TABLE
548 */
549 FUNCTION get_qualifier_table ( p_task_id NUMBER )
550 RETURN csf_resource_pub.resource_qualifier_tbl_type IS
551 BEGIN
552 RETURN csf_resource_pub.get_res_qualifier_table(p_task_id);
553 END get_qualifier_table;
554
555 FUNCTION get_query_where(p_query_id NUMBER)
556 RETURN VARCHAR2 IS
557 l_where csf_dc_queries_b.where_clause%TYPE;
558 l_enabled_flag VARCHAR2(1);
559 l_owner_id NUMBER;
560 l_owner_type VARCHAR2(30);
561
562 CURSOR c_query_info IS
563 SELECT q.where_clause
564 , NVL(qe.owner_enabled_flag, 'N') owner_enabled_flag
565 , ( SELECT COUNT(*)
566 FROM csf_plan_task_owners
567 WHERE user_id = fnd_global.user_id
568 ) owners_count
569 FROM csf_dc_queries_b q
570 , csf_dc_query_extns qe
571 WHERE q.query_id = p_query_id
572 AND qe.user_id(+) = fnd_global.user_id
573 AND qe.query_id(+) = q.query_id;
574
575 l_query_info c_query_info%ROWTYPE;
576 BEGIN
577 -- query the where clause corresponding to list value
578 OPEN c_query_info;
579 FETCH c_query_info INTO l_query_info;
580 CLOSE c_query_info;
581
582 IF (l_query_info.owner_enabled_flag IN ('Y', 'y')) THEN
583 IF (l_query_info.owners_count = 0) THEN
584 l_query_info.where_clause :=
585 l_query_info.where_clause
586 || ' and owner_id = ' || csf_resource_pub.resource_id
587 || ' and owner_type_code = ''' || csf_resource_pub.resource_type || '''';
588 ELSE
589 l_query_info.where_clause :=
590 l_query_info.where_clause
591 || ' AND (owner_id, owner_type_code)
592 IN (SELECT owner_id, owner_type_code
593 FROM csf_plan_task_owners
594 WHERE user_id=fnd_global.user_id) ';
595 END IF;
596 END IF;
597 RETURN l_query_info.where_clause;
598 END get_query_where;
599
600 BEGIN
601 g_timing_activated := fnd_profile.value('LOGACTIVATED') = 'Y';
602 g_logging_activated := fnd_profile.value('AFLOG_ENABLED') = 'Y';
603 END csf_util_pvt;
[Home] [Help]