4 ---
1 PACKAGE jtf_task_utl AUTHID CURRENT_USER AS
2 /* $Header: jtfptkls.pls 120.10 2011/12/12 17:16:03 anangupt ship $ */
3 g_minimum_effort NUMBER := 0;
5 --- The following variable is used in the function Validate_Time_UOM
6 ---
7 g_uom_time_class mtl_units_of_measure.uom_class%TYPE
8 := fnd_profile.VALUE ('JTF_TIME_UOM_CLASS');
9
10 -- Commented out by SBARAT on 29/12/2005 for bug# 4866066
11 /*g_yes CONSTANT CHAR := 'Y';
12 g_no CONSTANT CHAR := 'N';*/
13
14 g_perz_suffix CONSTANT VARCHAR2(9) := ':JTF_TASK';
15 g_validate_category boolean DEFAULT true;
16
17 g_tasks_read_privelege constant VARCHAR2(30) := 'JTF_TASK_READ_ONLY' ;
18 g_tasks_full_privelege constant VARCHAR2(30) := 'JTF_TASK_FULL_ACCESS' ;
19
20 g_show_error_for_dup_reference Boolean DEFAULT True; -- 2102281
21
22 -- Added by SBARAT on 29/12/2005 for bug# 4866066
23 FUNCTION g_yes RETURN VARCHAR2;
24 FUNCTION g_no RETURN VARCHAR2;
25
26 FUNCTION validate_shift_construct (p_shift_construct_id IN NUMBER)
27 RETURN BOOLEAN;
28
29 PROCEDURE call_internal_hook (
30 p_package_name IN VARCHAR2,
31 p_api_name IN VARCHAR2,
32 p_processing_type IN VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2
34 );
35
36 FUNCTION get_escalation_owner (p_task_id IN NUMBER)
37 RETURN VARCHAR2;
38
39 FUNCTION get_escalation_level (p_task_id IN NUMBER)
40 RETURN VARCHAR2;
41
42 PROCEDURE validate_location_id (
43 p_location_id IN NUMBER,
44 p_address_id IN NUMBER,
45 p_task_id IN NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2
47 );
48
49 FUNCTION validate_lookup (
50 p_lookup_type IN VARCHAR2,
51 p_lookup_code IN VARCHAR2,
52 p_lookup_type_name IN VARCHAR2
53 )
54 RETURN BOOLEAN;
55
56 PROCEDURE validate_contact (
57 p_contact_id IN NUMBER,
58 p_task_id IN NUMBER,
59 p_contact_type_code IN VARCHAR2,
63 PROCEDURE validate_contact_point (
60 x_return_status OUT NOCOPY VARCHAR2
61 );
62
64 p_contact_id IN NUMBER,
65 p_phone_id IN NUMBER,
66 x_return_status OUT NOCOPY VARCHAR2,
67 p_owner_table_name IN VARCHAR2 DEFAULT 'JTF_TASK_CONTACTS'
68 );
69
70 PROCEDURE check_duplicate_contact (
71 p_contact_id IN NUMBER,
72 p_task_id IN NUMBER,
73 p_contact_type_code IN VARCHAR2,
74 p_task_contact_id IN NUMBER DEFAULT NULL,
75 x_return_status OUT NOCOPY VARCHAR2
76 );
77
78 PROCEDURE validate_distance (
79 p_distance_units IN NUMBER,
80 p_distance_tag IN VARCHAR2,
81 x_return_status OUT NOCOPY VARCHAR2
82 );
83
84 FUNCTION get_owner (p_object_type_code IN VARCHAR2, p_object_id IN NUMBER)
85 RETURN VARCHAR2;
86
87 ---------
88 --------- Validate Task Template Group
89 ---------
90 PROCEDURE validate_task_template_group (
91 p_task_template_group_id IN NUMBER,
92 p_task_template_group_name IN VARCHAR2,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_task_template_group_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
95 );
96
97 PROCEDURE validate_costs (
98 x_return_status OUT NOCOPY VARCHAR2,
99 p_costs IN NUMBER,
100 p_currency_code IN VARCHAR2
101 );
102
103 FUNCTION currency_code (p_currency_code IN VARCHAR2)
104 RETURN VARCHAR2;
105
106 PROCEDURE validate_object_type (
107 p_object_code IN VARCHAR2,
108 p_object_type_name IN VARCHAR2,
109 p_object_type_tag IN VARCHAR2 DEFAULT NULL,
110 p_object_usage IN VARCHAR2 DEFAULT NULL,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_object_code IN OUT NOCOPY VARCHAR2 -- Fixed from OUT to IN OUT
113 );
114
115 PROCEDURE validate_party_site (
116 p_party_site_id IN NUMBER,
117 p_party_site_number IN VARCHAR2,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_party_site_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
120 );
121
122 PROCEDURE validate_party (
123 p_party_id IN NUMBER,
124 p_party_number IN VARCHAR2,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_party_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
127 );
128
129 PROCEDURE validate_cust_account (
130 p_cust_account_id IN NUMBER,
131 p_cust_account_number IN VARCHAR2,
132 x_return_status OUT NOCOPY VARCHAR2,
133 x_cust_account_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
134 );
135
136 PROCEDURE validate_dates (
137 p_date_tag IN VARCHAR2 DEFAULT NULL,
138 p_start_date IN DATE DEFAULT NULL,
139 p_end_date IN DATE DEFAULT NULL,
140 x_return_status OUT NOCOPY VARCHAR2
141 );
142
143 PROCEDURE validate_date_types (
144 p_date_type_id IN NUMBER,
145 p_date_type IN VARCHAR2,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_date_type_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
148 );
149
150 FUNCTION validate_dependency_id (p_dependency_id IN NUMBER)
151 RETURN BOOLEAN;
152
153 FUNCTION to_boolean (x VARCHAR2)
154 RETURN BOOLEAN;
155
156 FUNCTION get_task_template_group (p_task_template_id IN NUMBER)
157 RETURN NUMBER;
158
159 FUNCTION created_by
160 RETURN NUMBER;
161
162 FUNCTION updated_by
163 RETURN NUMBER;
164
165 FUNCTION login_id
166 RETURN NUMBER;
167
168 PROCEDURE validate_customer_info (
169 p_cust_account_number IN VARCHAR2,
170 p_cust_account_id IN NUMBER,
171 p_customer_number IN VARCHAR2,
172 p_customer_id IN NUMBER,
173 p_address_id IN NUMBER,
174 p_address_number IN VARCHAR2,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_cust_account_id IN OUT NOCOPY NUMBER, -- Fixed from OUT to IN OUT
177 x_customer_id IN OUT NOCOPY NUMBER, -- Fixed from OUT to IN OUT
178 x_address_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
179 );
180
181 PROCEDURE get_object_details (
182 p_task_id IN NUMBER,
183 p_template_flag IN VARCHAR2,
184 x_return_status OUT NOCOPY VARCHAR2,
185 x_source_object_code OUT NOCOPY VARCHAR2
186 );
187
188 PROCEDURE validate_flag (
189 p_api_name IN VARCHAR2 DEFAULT NULL,
190 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
191 x_return_status OUT NOCOPY VARCHAR2,
192 p_flag_name IN VARCHAR2,
193 p_flag_value IN VARCHAR2
194 );
195
196 PROCEDURE validate_task (
197 x_return_status OUT NOCOPY VARCHAR2,
198 p_task_id IN NUMBER DEFAULT NULL,
199 p_task_number IN VARCHAR2 DEFAULT NULL,
200 x_task_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
201 );
202
203 PROCEDURE validate_task_template (
204 x_return_status OUT NOCOPY VARCHAR2,
205 p_task_id IN NUMBER DEFAULT NULL,
206 p_task_number IN VARCHAR2 DEFAULT NULL,
207 x_task_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
208 );
209
213 FUNCTION validate_time_uom (p_uom_code IN VARCHAR2)
210 FUNCTION validate_dependency_code (p_dependency_code IN VARCHAR2)
211 RETURN BOOLEAN;
212
214 RETURN VARCHAR2;
215
216 PROCEDURE validate_effort (
217 p_api_name IN VARCHAR2 DEFAULT NULL,
218 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
219 p_tag IN VARCHAR2 DEFAULT NULL,
220 p_tag_uom IN VARCHAR2 DEFAULT NULL,
221 x_return_status OUT NOCOPY VARCHAR2,
222 p_effort IN NUMBER,
223 p_effort_uom IN VARCHAR2
224 );
225
226 PROCEDURE validate_task_type (
227 p_task_type_id IN NUMBER,
228 p_task_type_name IN VARCHAR2,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_task_type_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
231 );
232
233 PROCEDURE validate_task_status (
234 p_task_status_id IN NUMBER,
235 p_task_status_name IN VARCHAR2,
236 p_validation_type IN VARCHAR2,
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_task_status_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
239 );
240
241 PROCEDURE validate_task_priority (
242 p_task_priority_id IN NUMBER,
243 p_task_priority_name IN VARCHAR2,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_task_priority_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
246 );
247
248 PROCEDURE validate_task_owner (
249 p_owner_type_name IN VARCHAR2 DEFAULT NULL,
250 p_owner_type_code IN VARCHAR2,
251 p_owner_id IN NUMBER,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_owner_id IN OUT NOCOPY NUMBER, -- Fixed from OUT to IN OUT
254 x_owner_type_code IN OUT NOCOPY VARCHAR2 -- Fixed from OUT to IN OUT
255 );
256
257 PROCEDURE validate_timezones (
258 p_timezone_id IN NUMBER DEFAULT NULL,
259 p_timezone_name IN VARCHAR2 DEFAULT NULL,
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_timezone_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
262 );
263
264 PROCEDURE validate_parent_task_id (
265 p_parent_task_id IN NUMBER,
266 p_source_object_code IN VARCHAR2,
267 p_source_object_id IN NUMBER,
268 x_return_status OUT NOCOPY VARCHAR2
269 );
270
271 PROCEDURE validate_notification (
272 p_notification_flag IN VARCHAR2,
273 p_notification_period IN NUMBER,
274 p_notification_period_uom IN VARCHAR2,
275 x_return_status OUT NOCOPY VARCHAR2
276 );
277
278 PROCEDURE validate_alarm (
279 p_alarm_start IN NUMBER,
280 p_alarm_start_uom IN VARCHAR2,
281 p_alarm_on IN VARCHAR2,
282 p_alarm_count IN NUMBER,
283 p_alarm_interval IN NUMBER,
284 p_alarm_interval_uom IN VARCHAR2,
285 x_return_status OUT NOCOPY VARCHAR2
286 );
287
288 PROCEDURE validate_assigned_by (
289 p_assigned_by_id IN NUMBER,
290 p_assigned_by_name IN VARCHAR2,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_assigned_by_id IN OUT NOCOPY NUMBER -- Fixed from OUT to IN OUT
293 );
294
295 PROCEDURE validate_source_object (
296 p_object_code IN VARCHAR2,
297 p_object_id IN NUMBER,
298 p_tag IN VARCHAR2 DEFAULT NULL,
299 p_object_name IN VARCHAR2,
300 x_return_status OUT NOCOPY VARCHAR2
301 );
302
303 PROCEDURE validate_reference_codes (
304 p_reference_code IN VARCHAR2,
305 x_return_status OUT NOCOPY VARCHAR2
306 );
307
308 FUNCTION g_miss_char
309 RETURN VARCHAR2;
310
311 FUNCTION g_miss_date
312 RETURN DATE;
313
314 FUNCTION g_miss_number
315 RETURN NUMBER;
316
317 FUNCTION get_translated_lookup (
318 p_lookup_type IN VARCHAR2,
319 p_lookup_code IN VARCHAR2
320 )
321 RETURN VARCHAR2;
322
323 PROCEDURE privelege_all_tasks (
324 p_profile_name IN VARCHAR2,
325 x_privelege_all_tasks OUT NOCOPY VARCHAR2,
326 x_return_status OUT NOCOPY VARCHAR2
327 );
328
329 PROCEDURE get_default_owner (
330 x_owner_type_code OUT NOCOPY VARCHAR2,
331 x_owner_id OUT NOCOPY NUMBER,
332 x_owner_type_code_name OUT NOCOPY VARCHAR2,
333 x_owner_name OUT NOCOPY VARCHAR2,
334 x_return_status OUT NOCOPY VARCHAR2
335 );
336
337 ----
338 ---- function returns the UOM class
339 ----
340 FUNCTION get_uom_time_class
341 RETURN VARCHAR2;
342
343 ----
347 RETURN VARCHAR2;
344 ----
345 ----
346 FUNCTION is_task_closed (p_task_status_id IN NUMBER)
348
349 ----
350 ----
351 ----
352 FUNCTION get_customer_name (p_customer_id IN NUMBER)
353 RETURN VARCHAR2;
354
355 ----
356 ----
357 ----
358 PROCEDURE validate_missing_task_id (
359 p_task_id IN NUMBER,
360 x_return_status OUT NOCOPY VARCHAR2
361 );
362
363 ----
364 ----
365 ----
366 PROCEDURE validate_missing_contact_id (
367 p_task_contact_id IN NUMBER,
368 x_return_status OUT NOCOPY VARCHAR2
369 );
370
371 -----
372 -----
373 -----
374 PROCEDURE validate_missing_phone_id (
375 p_task_phone_id IN NUMBER,
376 x_return_status OUT NOCOPY VARCHAR2
377 );
378
379 -----
380 -----
381 -----
382 PROCEDURE validate_application_id (
383 p_application_id IN NUMBER,
384 x_return_status OUT NOCOPY VARCHAR2
385 );
386
387 -----
388 -----
389 -----
390 FUNCTION get_user_name (p_user_id IN NUMBER)
391 RETURN VARCHAR2;
392
393 -----
394 -----
395 -----
396 FUNCTION get_parent_task_number (p_task_id IN NUMBER)
397 RETURN VARCHAR2;
398
399 -----
400 -----
401 -----
402 FUNCTION get_territory_name (p_terr_id IN NUMBER)
403 RETURN VARCHAR2;
404
405 -----
406 -----
407 -----
408 PROCEDURE validate_phones_table (
409 p_owner_table_name IN VARCHAR2,
410 x_return_status OUT NOCOPY VARCHAR2
411 );
412 -----
413 -----
414 -----
415
416 PROCEDURE validate_category (
417 p_category_id in number,
418 x_return_status out NOCOPY varchar2);
419 -----
420 -----
421 -----
422
423 PROCEDURE check_security_privilege(
424 p_task_id number,
425 p_session varchar2,
426 x_return_status out NOCOPY varchar2);
427 -----
428 -----
429 -----
430 FUNCTION g_no_char
431 RETURN VARCHAR2;
432 -----
433 -----
434 -----
435 FUNCTION g_yes_char
436 RETURN VARCHAR2;
437
438 -----
439 -----
440 -----
441 FUNCTION g_false_char
442 RETURN VARCHAR2;
443 -----
444 -----
445 -----
446 FUNCTION g_true_char
447 RETURN VARCHAR2;
448
449 PROCEDURE validate_party_site_acct (
450 p_party_id number,
451 p_party_site_id number,
452 p_cust_account_id number,
453 x_return_status out NOCOPY varchar2);
454 -----
455 -----
456 -----
457
458 FUNCTION GET_CATEGORY_NAME_FOR_TASK ( p_task_id in number,
459 p_resource_id in number,
460 p_resource_type_code in varchar2 )
461 RETURN varchar2 ;
462 -----
463 -----
464 -----
465 FUNCTION GET_CATEGORY_NAME ( p_category_id in number )
466 RETURN varchar2 ;
467
468 procedure delete_category( p_category_name in varchar2 );
469
470 PROCEDURE set_calendar_dates (
471 p_show_on_calendar in varchar2 default null,
472 p_date_selected in varchar2 default null,
473 p_planned_start_date in date default null,
474 p_planned_end_date in date default null,
475 p_scheduled_start_date in date default null,
476 p_scheduled_end_date in date default null,
477 p_actual_start_date in date default null,
478 p_actual_end_date in date default null,
479 x_show_on_calendar in out NOCOPY varchar2, -- Fixed from OUT to IN OUT
480 x_date_selected in out NOCOPY varchar2, -- Fixed from OUT to IN OUT
481 x_calendar_start_date out NOCOPY date,
482 x_calendar_end_date out NOCOPY date,
483 x_return_status out NOCOPY varchar2
484 );
485 -----
486 -----
487 -----
488 PROCEDURE validate_status (
489 p_status_id IN NUMBER,
490 p_type IN varchar2,
491 x_return_status OUT NOCOPY VARCHAR2
492 );
493
494 function getURL ( p_web_function_name in varchar2 )
495 return varchar2 ;
496
497 function getURLparameter ( p_object_code in varchar2 )
498 return varchar2 ;
499
500 FUNCTION check_truncation (p_object_name in varchar2)
501 return varchar2;
502
503 -- Added for Enhancement # 2102281
504 FUNCTION check_duplicate_reference (p_task_id jtf_tasks_b.task_id%type,
505 p_object_id hz_relationships.object_id%type,
506 p_object_type_code jtf_task_references_b.object_type_code%type)
507 return boolean;
508
509 -- Added for Enhancement # 2102281
510 FUNCTION check_reference_delete (p_task_id jtf_tasks_b.task_id%type,
511 p_object_id hz_relationships.object_id%type)
512 return boolean;
513
514 PROCEDURE create_party_reference (
515 p_reference_from in varchar2,
516 p_task_id in number,
517 p_party_type_code in varchar2 default null,
518 p_party_id in number,
519 x_msg_count out NOCOPY number,
520 x_msg_data out NOCOPY varchar2,
521 x_return_status out NOCOPY varchar2);
522
523 PROCEDURE delete_party_reference (
524 p_reference_from in varchar2,
525 p_task_id in number,
526 p_party_type_code in varchar2 default null,
527 p_party_id in number,
528 x_msg_count out NOCOPY number,
529 x_msg_data out NOCOPY varchar2,
530 x_return_status out NOCOPY varchar2);
531
532 --Bug 2467222 for assignee category update
536 task_assignment_id,
533 CURSOR c_assignee_or_owner (p_task_id NUMBER,p_category_id NUMBER) IS
534 SELECT
535 object_version_number,
537 assignment_status_id,
538 decode( p_category_id, fnd_api.g_miss_num, category_id, p_category_id) category_id
539 FROM jtf_task_all_assignments
540 WHERE task_id = p_task_id
541 AND resource_id = ( SELECT resource_id
542 FROM jtf_rs_resource_extns
543 WHERE user_id = fnd_global.user_id)
544 AND resource_type_code not in ('RS_GROUP','RS_TEAM');
545
546 -- CBusiness Event System Enhancement # 2391065
547 CURSOR c_ass_orig (b_task_assignment_id IN NUMBER) IS
548 SELECT
549 object_version_number,
550 task_assignment_id,
551 assignment_status_id,
552 category_id,
553 resource_id,
554 resource_type_code,
555 actual_start_date,
556 actual_end_date,
557 assignee_role,
558 show_on_calendar
559 FROM jtf_task_all_assignments
560 WHERE task_assignment_id = b_task_assignment_id;
561
562 PROCEDURE update_task_category (
563 p_api_version IN NUMBER,
564 p_object_version_number IN OUT NOCOPY NUMBER,
565 p_task_assignment_id IN NUMBER DEFAULT fnd_api.g_miss_num,
566 p_category_id IN NUMBER DEFAULT jtf_task_utl.g_miss_number,
567 x_return_status OUT NOCOPY VARCHAR2,
568 x_msg_count OUT NOCOPY NUMBER,
569 x_msg_data OUT NOCOPY VARCHAR2);
570
571 FUNCTION get_owner_detail (p_object_type_code IN VARCHAR2, p_object_id IN NUMBER)
572 RETURN VARCHAR2;
573
574 FUNCTION get_status_name (p_status_id IN NUMBER)
575 RETURN VARCHAR2;
576
577 FUNCTION enable_audit (p_enable IN BOOLEAN DEFAULT TRUE)
578 RETURN BOOLEAN;
579
580
581 FUNCTION TaskToServerTz(p_date IN DATE,p_TaskTz IN NUMBER)
582 RETURN DATE;
583
584 FUNCTION ServerToTaskTz(p_date IN DATE,p_TaskTz IN NUMBER)
585 RETURN DATE;
586
587
588
589 END;
[Home] [Help]