1 PACKAGE CAC_SYNC_TASK_COMMON AUTHID CURRENT_USER AS
2 /* $Header: cacvstcs.pls 120.10.12000000.4 2007/10/19 07:56:56 vsood ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtavstcs.pls |
9 | |
10 | DESCRIPTION |
11 | This package is a common for sync task |
12 | |
13 | NOTES |
14 | |
15 | |
16 | Date Developer Change |
17 | ------ --------------- -------------------------------------- |
18 | 04-Nov-2004 sachoudh Created.
19 | 01=FEB-2005 rhshriva Added the record collab_details_rec |
20 | 26=SEP-2005 deeprao Added the record delete_tasks |
21 *=======================================================================*/
22
23 G_TASK_TYPE_GENERAL CONSTANT NUMBER := 15; -- Task Type Name = General
24 G_NEW CONSTANT VARCHAR2(10) := 'New';
25 G_MODIFY CONSTANT VARCHAR2(10) := 'Modify';
26 G_DELETE CONSTANT VARCHAR2(10) := 'Delete';
27 G_GMT_TIMEZONE_ID CONSTANT NUMBER := 0;
28 G_SERVER_TIMEZONE_ID CONSTANT NUMBER := TO_NUMBER (fnd_profile.VALUE ('SERVER_TIMEZONE_ID'));
29 G_SYNC_SUCCESS CONSTANT NUMBER := 0;
30 G_CLIENT_TIMEZONE_ID CONSTANT NUMBER := TO_NUMBER (fnd_profile.VALUE ('CLIENT_TIMEZONE_ID'));
31
32 -- Define update_type
33 G_UPDATE_ALL CONSTANT VARCHAR2(10) := 'UPDATE_ALL';
34 G_UPDATE_STATUS CONSTANT VARCHAR2(13) := 'UPDATE_STATUS';
35 G_DO_NOTHING CONSTANT VARCHAR2(10) := 'DO_NOTHING';
36
37 G_PREFIX_INVITEE CONSTANT VARCHAR2(8) := 'INVITE: ';
38 G_APPOINTMENT CONSTANT VARCHAR2(12) := 'APPOINTMENT';
39 G_TASK CONSTANT VARCHAR2(5) := 'TASK';
40 G_REQ_APPOINTMENT CONSTANT VARCHAR2(12) := 'APPOINTMENTS';
41 G_REQ_TASK CONSTANT VARCHAR2(12) := 'TASKS';
42
43 G_USER_STATUS_RULE BOOLEAN;
44 G_LOGIN_RESOURCE_ID NUMBER;
45 G_CARRIAGE_RETURN_XML VARCHAR2(6) := '
';
46 G_CARRIAGE_RETURN_ORACLE VARCHAR2(10) := '
47 ';
48 G_USER_DEFAULT_REPEAT_COUNT CONSTANT NUMBER := TO_NUMBER (fnd_profile.VALUE ('JTF_TASK_DEFAULT_REPEAT_COUNT'));
49 --CAC Sync: Include Tasks Without Date -added the profile to check if this profile is yes then bring endless task
50 G_CAC_SYNC_TASK_NO_DATE CONSTANT VARCHAR2(255):= nvl(fnd_profile.VALUE ('CAC_SYNC_TASK_NO_DATE'),'N');
51 -- The record type to store a group id and resource_type "RS_GROUP"
52 TYPE resource_list_rec IS RECORD (
53 resource_id NUMBER,
54 resource_type VARCHAR2(100),
55 resource_name VARCHAR2(360)
56 );
57
58
59 -- The PLSQL table to store a list of group ids and resource_types
60 TYPE resource_list_tbl IS TABLE OF resource_list_rec
61 INDEX BY BINARY_INTEGER;
62
63 PROCEDURE get_resource_details (
64 x_resource_id OUT NOCOPY NUMBER,
65 x_resource_type OUT NOCOPY VARCHAR2
66 );
67
68 FUNCTION get_client_priority(p_importance_level IN NUMBER)
69 RETURN NUMBER;
70
71 -------------------------------
72 -- Public cursor
73 -------------------------------
74
75 -- This returns a PLSQL table that stores a list of group ids and resource types
76 FUNCTION get_group_calendar (p_resource_id IN NUMBER) RETURN resource_list_tbl;
77
78 PROCEDURE get_group_resource (
79 p_request_type IN VARCHAR2,
80 p_resource_id IN NUMBER,
81 p_resource_type IN VARCHAR2,
82 x_resources OUT NOCOPY resource_list_tbl
83 );
84
85 PROCEDURE get_alarm_mins (
86 p_task_rec IN cac_sync_task.task_rec,
87 x_alarm_mins OUT NOCOPY NUMBER
88 );
89
90 -- Used in creating a new task
91 FUNCTION convert_gmt_to_client (p_date IN DATE) RETURN DATE;
92 -- Used in updating a task
93 FUNCTION convert_gmt_to_task (p_date IN DATE,
94 p_task_id IN NUMBER) RETURN DATE;
95 -- Used in getting a list of tasks
96 FUNCTION convert_task_to_gmt (p_date IN DATE,
97 p_timezone_id IN NUMBER) RETURN DATE;
98 -- Used to establish a new syncanchor
99 FUNCTION convert_server_to_gmt (p_date IN DATE) RETURN DATE;
100
101 -- Used to convert syncanchor from GMT to Server timezone
102 FUNCTION convert_gmt_to_server (p_date IN DATE) RETURN DATE;
103
104 -- Used in creating a task
105 PROCEDURE convert_dates (
106 p_task_rec IN cac_sync_task.task_rec,
107 p_operation IN VARCHAR2, --CREATE OR UPDATE
108 x_planned_start OUT NOCOPY DATE,
109 x_planned_end OUT NOCOPY DATE,
110 x_scheduled_start OUT NOCOPY DATE,
111 x_scheduled_end OUT NOCOPY DATE,
112 x_actual_start OUT NOCOPY DATE,
113 x_actual_end OUT NOCOPY DATE,
114 x_date_selected OUT NOCOPY VARCHAR2,
115 x_show_on_calendar OUT NOCOPY VARCHAR2
116 );
117
118 -- Used in getting a list of tasks
119 PROCEDURE adjust_timezone(
120 p_timezone_id IN NUMBER,
121 p_syncanchor IN DATE,
122 p_planned_start_date IN DATE,
123 p_planned_end_date IN DATE,
124 p_scheduled_start_date IN DATE,
125 p_scheduled_end_date IN DATE,
126 p_actual_start_date IN DATE,
127 p_actual_end_date IN DATE,
128 p_item_display_type IN NUMBER,
129 x_task_rec IN OUT NOCOPY cac_sync_task.task_rec);
130
131 FUNCTION get_max_enddate(p_recurrence_rule_id IN NUMBER)
132 RETURN DATE;
133
134 PROCEDURE make_prefix(
135 p_assignment_status_id IN NUMBER,
136 p_source_object_type_code IN VARCHAR2,
137 p_resource_type IN VARCHAR2,
138 p_resource_id IN NUMBER,
139 p_group_id IN NUMBER,
140 x_subject IN OUT NOCOPY VARCHAR2
141 );
142
143 PROCEDURE check_delete_data(
144 p_task_id IN NUMBER,
145 p_resource_id IN NUMBER,
146 p_objectcode IN VARCHAR2,
147 x_status_id OUT NOCOPY NUMBER,
148 x_delete_flag OUT NOCOPY VARCHAR2
149 );
150
151 FUNCTION get_assignment_id (p_task_id IN NUMBER,
152 p_resource_id IN NUMBER,
153 p_resource_type IN VARCHAR2 DEFAULT 'RS_EMPLOYEE'
154 )
155 RETURN NUMBER;
156
157 FUNCTION get_assignment_status_id (p_task_id IN NUMBER,
158 p_resource_id IN NUMBER
159 )
160 RETURN NUMBER;
161
162 PROCEDURE get_assignment_info(p_task_id IN NUMBER
163 ,p_resource_id IN NUMBER
164 ,x_assignee_role OUT NOCOPY VARCHAR2
165 ,x_assignment_status_id OUT NOCOPY NUMBER);
166
167 PROCEDURE get_owner_info(p_task_id IN NUMBER,
168 x_task_name OUT NOCOPY VARCHAR2,
169 x_owner_id OUT NOCOPY NUMBER,
170 x_owner_type_code OUT NOCOPY VARCHAR2);
171
172 FUNCTION get_access(p_group_id IN VARCHAR2
173 ,p_resource_id IN NUMBER
174 )
175 RETURN VARCHAR2;
176
177 FUNCTION get_source_object_type(p_task_id IN NUMBER)
178 RETURN VARCHAR2;
179
180 FUNCTION get_update_type (
181 p_task_id IN NUMBER,
182 p_resource_id IN NUMBER,
183 p_subject IN VARCHAR2
184 )
185 RETURN VARCHAR2;
186
187 FUNCTION get_recurrence_rule_id(p_task_id IN NUMBER)
188 RETURN NUMBER;
189
190 PROCEDURE convert_recur_date_to_client(p_base_start_time IN DATE,
191 p_base_end_time IN DATE,
192 p_start_date IN DATE,
193 p_end_date IN DATE,
194 p_occurs_which IN NUMBER,
195 p_uom IN VARCHAR2,
196 x_date_of_month OUT NOCOPY NUMBER,
197 x_start_date IN OUT NOCOPY DATE,
198 x_end_date IN OUT NOCOPY DATE);
199
200 PROCEDURE convert_recur_date_to_server(p_base_start_time IN DATE,
201 p_base_end_time IN DATE,
202 p_start_date IN DATE,
203 p_end_date IN DATE,
204 p_occurs_which IN NUMBER,
205 p_uom IN VARCHAR2,
206 x_date_of_month OUT NOCOPY NUMBER,
207 x_start_date IN OUT NOCOPY DATE,
208 x_end_date IN OUT NOCOPY DATE);
209
210 PROCEDURE get_all_nonrepeat_tasks(
211 p_request_type IN VARCHAR2,
212 p_syncanchor IN DATE,
213 p_recordindex IN NUMBER,
214 p_resource_id IN NUMBER,
215 p_principal_id IN NUMBER,
216 p_resource_type IN VARCHAR2,
217 p_source_object_type IN VARCHAR2,
218 p_get_data IN BOOLEAN,
219 x_totalnew IN OUT NOCOPY NUMBER,
220 x_totalmodified IN OUT NOCOPY NUMBER,
221 x_data IN OUT NOCOPY cac_sync_task.task_tbl
222 --p_new_syncanchor in date
223 );
224 PROCEDURE get_all_deleted_tasks(
225 p_request_type IN VARCHAR2,
226 p_syncanchor IN DATE,
227 p_recordindex IN NUMBER,
228 p_resource_id IN NUMBER,
229 p_principal_id IN NUMBER,
230 p_resource_type IN VARCHAR2,
231 p_source_object_type IN VARCHAR2,
232 p_get_data IN BOOLEAN,
233 x_totaldeleted IN OUT NOCOPY NUMBER,
234 x_data IN OUT NOCOPY cac_sync_task.task_tbl
235 --p_new_syncanchor in date
236 );
237 PROCEDURE get_all_repeat_tasks(
238 p_request_type IN VARCHAR2,
239 p_syncanchor IN DATE,
240 p_recordindex IN NUMBER,
241 p_resource_id IN NUMBER,
242 p_principal_id IN NUMBER,
243 p_resource_type IN VARCHAR2,
244 p_source_object_type IN VARCHAR2,
245 p_get_data IN BOOLEAN,
246 x_totalnew IN OUT NOCOPY NUMBER,
247 x_totalmodified IN OUT NOCOPY NUMBER,
248 -- x_totaldeleted IN OUT NOCOPY NUMBER,
249 x_data IN OUT NOCOPY cac_sync_task.task_tbl,
250 x_exclusion_data IN OUT NOCOPY cac_Sync_Task.exclusion_tbl
251 --p_new_syncanchor in date
252 );
253
254 PROCEDURE create_new_data(p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
255 ,p_mapping_type IN VARCHAR2 DEFAULT G_NEW -- Fixed bug 2497963 for 9i issue
256 ,p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl
257 ,p_resource_id IN NUMBER
258 ,p_resource_type IN VARCHAR2
259 );
260
261 PROCEDURE update_existing_data(p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
262 ,p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl
263 ,p_resource_id IN NUMBER
264 ,p_resource_type IN VARCHAR2);
265
266 PROCEDURE delete_exclusion_task(
267 p_repeating_task_id IN NUMBER
268 ,x_task_rec IN OUT NOCOPY cac_sync_task.task_rec
269 );
270
271 PROCEDURE delete_task_data(
272 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
273 ,p_delete_map_flag IN BOOLEAN DEFAULT TRUE
274 );
275
276 PROCEDURE reject_task_data(
277 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
278 );
279
280 FUNCTION changed_repeat_rule(p_task_rec IN cac_sync_task.task_rec)
281 RETURN BOOLEAN;
282
283 -- function to check if a repeating task is on exclusion list
284 FUNCTION check_for_exclusion(p_sync_id IN NUMBER,
285 p_exclusion_tbl IN OUT NOCOPY cac_sync_task.exclusion_tbl,
286 p_calendar_start_date IN DATE,
287 p_client_time_zone_id NUMBER)
288 RETURN BOOLEAN;
289
290 FUNCTION get_excluding_taskid (p_sync_id IN NUMBER,
291 p_recurrence_rule_id IN NUMBER,
292 p_exclusion_rec IN OUT NOCOPY cac_sync_task.exclusion_rec)
293 RETURN NUMBER;
294
295 PROCEDURE transformStatus(p_task_status_id IN out NOCOPY NUMBER,
296 p_task_sync_id IN NUMBER,
297 x_operation IN OUT NOCOPY VARCHAR2
298 );
299 /*
300 FUNCTION getChangedStatusId(p_task_status_id IN NUMBER,
301 p_source_object_type_code IN VARCHAR2
302 )
303 RETURN NUMBER;
304
305 FUNCTION checkUserStatusRule RETURN BOOLEAN;
306 */--commented out these lines as the correpsoding package body is alos commented.
307 FUNCTION is_this_new_task (
308 p_sync_id IN NUMBER
309 )
310 RETURN BOOLEAN;
311
312 FUNCTION get_task_id (
313 p_sync_id IN NUMBER
314 )
315 RETURN NUMBER;
316
317 FUNCTION get_ovn (p_task_id IN NUMBER)
318 RETURN NUMBER;
319
320 FUNCTION get_ovn (p_task_assignment_id IN NUMBER)
321 RETURN NUMBER;
322
323 PROCEDURE do_mapping (
324 p_task_id IN NUMBER,
325 p_principal_id IN NUMBER,
326 p_operation IN VARCHAR2,
327 x_task_sync_id IN OUT NOCOPY NUMBER
328 );
329
330 FUNCTION already_selected(p_task_id IN NUMBER DEFAULT NULL
331 ,p_sync_id IN NUMBER DEFAULT NULL
332 ,p_task_tbl IN cac_sync_task.task_tbl)
336 PROCEDURE add_task (
333 RETURN BOOLEAN;
334
335 -- this procedure is made public only for testing ...should not be used otherwise
337 p_request_type IN VARCHAR2,
338 p_resource_id IN NUMBER,
339 p_principal_id IN NUMBER,
340 p_resource_type IN VARCHAR2,
341 p_recordindex IN NUMBER,
342 p_operation IN VARCHAR2,
343 p_task_sync_id IN NUMBER,
344 p_task_id IN NUMBER,
345 p_task_name IN VARCHAR2,
346 p_owner_type_code IN VARCHAR2,
347 p_description IN VARCHAR2,
348 p_task_status_id IN NUMBER,
349 p_task_priority_id IN NUMBER,
350 p_private_flag IN VARCHAR2,
351 p_date_selected IN VARCHAR2,
352 p_timezone_id IN NUMBER,
353 p_syncanchor IN DATE,
354 p_planned_start_date IN DATE,
355 p_planned_end_date IN DATE,
356 p_scheduled_start_date IN DATE,
357 p_scheduled_end_date IN DATE,
358 p_actual_start_date IN DATE,
359 p_actual_end_date IN DATE,
360 p_calendar_start_date IN DATE,
361 p_calendar_end_date IN DATE,
362 p_alarm_on IN VARCHAR2,
363 p_alarm_start IN NUMBER,
364 p_recurrence_rule_id IN NUMBER,
365 p_occurs_uom IN VARCHAR2,
366 p_occurs_every IN NUMBER,
367 p_occurs_number IN NUMBER,
368 p_start_date_active IN DATE,
369 p_end_date_active IN DATE,
370 p_sunday IN VARCHAR2,
371 p_monday IN VARCHAR2,
372 p_tuesday IN VARCHAR2,
373 p_wednesday IN VARCHAR2,
374 p_thursday IN VARCHAR2,
375 p_friday IN VARCHAR2,
376 p_saturday IN VARCHAR2,
377 p_date_of_month IN VARCHAR2,
378 p_occurs_which IN VARCHAR2,
379 p_locations IN VARCHAR2,
380 p_free_busy_type IN VARCHAR2,
381 p_dial_in IN VARCHAR2,
382 x_task_rec IN OUT NOCOPY cac_sync_task.task_rec
383 );
384
385 FUNCTION set_alarm_date (
386 p_task_id IN NUMBER,
387 p_request_type IN VARCHAR2,
388 p_scheduled_start_date IN DATE,
389 p_planned_start_date IN DATE,
390 p_actual_start_date IN DATE,
391 p_alarm_flag IN VARCHAR2,
392 p_alarm_start IN NUMBER
393 )
394 RETURN DATE ;
395
396 FUNCTION get_dial_in_value( p_task_id IN NUMBER)
397 RETURN VARCHAR2;
398
399 -- Added to fix bug 2382927
400 FUNCTION validate_syncid(p_syncid IN NUMBER)
401 RETURN BOOLEAN;
402
403 -- Cursor added for appointment attendee information
404 /* CURSOR GET_ADDENDEE_RESOURCE (b_task_id IN NUMBER)
405 IS
409 a.task_assignment_id,
406 SELECT r.resource_name,
407 a.assignee_role,
408 a.task_id,
410 a.resource_id,
411 a.resource_type_code
412 FROM jtf_task_all_assignments a,
413 jtf_rs_resources_vl r
414 WHERE a.task_id = b_task_id
415 AND a.resource_id = r.resource_id;
416 */
417
418 FUNCTION find_source_object_type_code(objectcode IN VARCHAR2)
419
420 return VARCHAR2;
421
422 procedure delete_bookings (
423 p_principal_id IN NUMBER);
424
425 procedure create_updation_record
426 (p_exclusion IN OUT NOCOPY cac_sync_task.exclusion_rec,
427 p_task_rec IN cac_sync_task.task_rec ,
428 p_exclude_task_id IN NUMBER,
429 p_rec_rule_id IN NUMBER
430 );
431
432 function is_recur_rule_same (
433 p_task_rec IN OUT NOCOPY cac_sync_task.task_rec
434
435 ) return boolean;
436
437 PROCEDURE delete_tasks(
438 p_task_id IN OUT NOCOPY NUMBER,
439 x_return_status IN OUT NOCOPY VARCHAR2
440 );
441
442 FUNCTION get_task_timezone_id (p_task_id IN NUMBER)
443 RETURN NUMBER;
444
445 PROCEDURE is_appointment_existing(p_task_sync_id IN NUMBER, x_result OUT NOCOPY VARCHAR2);
446
447 END CAC_SYNC_TASK_COMMON ; -- Package spec
[Home] [Help]