1 PACKAGE JTA_SYNC_TASK_COMMON AUTHID CURRENT_USER AS
2 /* $Header: jtavstcs.pls 115.58 2002/12/13 22:25:44 cjang 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 | 21-Feb-2002 cjang Refactoring |
19 | 22-Feb-2002 cjang Refactoring |
20 | 25-Feb-2002 cjang Bug Fix to select assignments |
21 | for an assignee |
22 | Removed p_alarm_uom on set_alarm_date()|
23 | Removed include_record() |
24 | 27-Feb-2002 cjang Added can_update_task, get_max_enddate |
25 | Added recurrences to the cursor c_task |
26 | Added a cursor c_repeating_task |
27 | Added add_task_with_recurrence |
28 | Modified adjust_timezone |
29 | 28-Feb-2002 cjang Integrate with invitee function |
30 | 01-Mar-2002 cjang Refactoring and Bug Fix |
31 | 06-Mar-2002 cjang Added get_all_nonrepeat_tasks |
32 | get_all_repeat_tasks |
33 | create_new_data |
34 | update_existing_data |
35 | add_nonrepeat_task |
36 | add_repeat_task |
37 | convert_task_to_gmt |
38 | Modified parameters on add_task |
39 | 11-Mar-2002 cjang Added the followings |
40 | - delete_exclusion_task |
41 | - delete_task_data |
42 | - reject_task_data |
43 | - changed_repeat_rule |
44 | - update_repeating_rule |
45 | 11-Mar-2002 sanjeev - Changing methos to include exclusion |
46 | 13-Mar-2002 cjang Added insert_or_update_mapping |
47 | Changed OUT to IN OUT on the parameter |
48 | x_task_rec in delete_exclusion_task|
49 | 25-Mar-2002 sanjeev Added procedure transformStatus and |
50 | function getChangedStatusId() |
51 | 08-Apr-2002 arpatel Merged convert_dates2 with convert_dates|
52 | 08-Apr-2002 ssallaka merged do_mapping anf insert_mapping |
53 | 19-Apr-2002 cjang Removed p_get_data from add_task() |
54 | 30-Apr-2002 cjang Added a new parameter p_resource_type |
55 | in get_assignment_id |
56 | 23-May-2002 cjang Added validate_syncid() |
57 | to fix bug 2382927 |
58 | 05-Aug-2002 cjang Changed the default for the parameter |
59 | p_mapping_type to G_NEW in the proc |
60 | create_new_data(). |
61 | Fixed bug 2497963 |
62 | 04-Oct-2002 cjang Fixed bug 2469488, 2469487, 2469479 |
63 | Added function already_selected |
64 *=======================================================================*/
65 G_TASK_TYPE_GENERAL CONSTANT NUMBER := 15; -- Task Type Name = General
66 G_NEW CONSTANT VARCHAR2(10) := 'New';
67 G_MODIFY CONSTANT VARCHAR2(10) := 'Modify';
68 G_DELETE CONSTANT VARCHAR2(10) := 'Delete';
69 G_GMT_TIMEZONE_ID CONSTANT NUMBER := 0;
70 G_SERVER_TIMEZONE_ID CONSTANT NUMBER := TO_NUMBER (fnd_profile.VALUE ('SERVER_TIMEZONE_ID'));
71 G_SYNC_SUCCESS CONSTANT NUMBER := 0;
72 G_CLIENT_TIMEZONE_ID CONSTANT NUMBER := TO_NUMBER (fnd_profile.VALUE ('CLIENT_TIMEZONE_ID'));
73
74 -- Define update_type
75 G_UPDATE_ALL CONSTANT VARCHAR2(10) := 'UPDATE_ALL';
76 G_UPDATE_STATUS CONSTANT VARCHAR2(13) := 'UPDATE_STATUS';
77 G_DO_NOTHING CONSTANT VARCHAR2(10) := 'DO_NOTHING';
78
79 G_PREFIX_INVITEE CONSTANT VARCHAR2(8) := 'INVITE: ';
80 G_APPOINTMENT CONSTANT VARCHAR2(12) := 'APPOINTMENT';
81 G_TASK CONSTANT VARCHAR2(5) := 'TASK';
82 G_REQ_APPOINTMENT CONSTANT VARCHAR2(12) := 'APPOINTMENTS';
83 G_REQ_TASK CONSTANT VARCHAR2(12) := 'TASKS';
84
85 G_USER_STATUS_RULE BOOLEAN;
86 G_LOGIN_RESOURCE_ID NUMBER;
87 G_CARRIAGE_RETURN_XML VARCHAR2(6) := '
';
88 G_CARRIAGE_RETURN_ORACLE VARCHAR2(1) := '
89 ';
90 G_USER_DEFAULT_REPEAT_COUNT CONSTANT NUMBER := TO_NUMBER (fnd_profile.VALUE ('JTF_TASK_DEFAULT_REPEAT_COUNT'));
91
92 -- The record type to store a group id and resource_type "RS_GROUP"
93 TYPE resource_list_rec IS RECORD (
94 resource_id NUMBER,
95 resource_type VARCHAR2(100)
96 );
97
98 -- The PLSQL table to store a list of group ids and resource_types
99 TYPE resource_list_tbl IS TABLE OF resource_list_rec
100 INDEX BY BINARY_INTEGER;
101
102 PROCEDURE get_resource_details (
103 x_resource_id OUT NOCOPY NUMBER,
104 x_resource_type OUT NOCOPY VARCHAR2
105 );
106
107 FUNCTION get_client_priority(p_importance_level IN NUMBER)
108 RETURN NUMBER;
109
110 -------------------------------
111 -- Public cursor
112 -------------------------------
113
114 -- This returns a PLSQL table that stores a list of group ids and resource types
115 FUNCTION get_group_calendar (p_resource_id IN NUMBER) RETURN resource_list_tbl;
116
117 PROCEDURE get_group_resource (
118 p_request_type IN VARCHAR2,
119 p_resource_id IN NUMBER,
120 p_resource_type IN VARCHAR2,
121 x_resources OUT NOCOPY resource_list_tbl
122 );
123
124 PROCEDURE get_alarm_mins (
125 p_task_rec IN jta_sync_task.task_rec,
126 x_alarm_mins OUT NOCOPY NUMBER
127 );
128
129 -- Used in creating a new task
130 FUNCTION convert_gmt_to_client (p_date IN DATE) RETURN DATE;
131 -- Used in updating a task
132 FUNCTION convert_gmt_to_task (p_date IN DATE,
133 p_task_id IN NUMBER) RETURN DATE;
134 -- Used in getting a list of tasks
135 FUNCTION convert_task_to_gmt (p_date IN DATE,
136 p_timezone_id IN NUMBER) RETURN DATE;
137 -- Used to establish a new syncanchor
138 FUNCTION convert_server_to_gmt (p_date IN DATE) RETURN DATE;
139
140 -- Used to convert syncanchor from GMT to Server timezone
141 FUNCTION convert_gmt_to_server (p_date IN DATE) RETURN DATE;
142
143 -- Used in creating a task
144 PROCEDURE convert_dates (
145 p_task_rec IN jta_sync_task.task_rec,
146 p_operation IN VARCHAR2, --CREATE OR UPDATE
147 x_planned_start OUT NOCOPY DATE,
148 x_planned_end OUT NOCOPY DATE,
149 x_scheduled_start OUT NOCOPY DATE,
150 x_scheduled_end OUT NOCOPY DATE,
151 x_actual_start OUT NOCOPY DATE,
152 x_actual_end OUT NOCOPY DATE,
153 x_date_selected OUT NOCOPY VARCHAR2,
154 x_show_on_calendar OUT NOCOPY VARCHAR2
155 );
156
157 -- Used in getting a list of tasks
158 PROCEDURE adjust_timezone(
159 p_timezone_id IN NUMBER,
160 p_syncanchor IN DATE,
161 p_planned_start_date IN DATE,
162 p_planned_end_date IN DATE,
163 p_scheduled_start_date IN DATE,
164 p_scheduled_end_date IN DATE,
165 p_actual_start_date IN DATE,
166 p_actual_end_date IN DATE,
167 p_item_display_type IN NUMBER,
168 x_task_rec IN OUT NOCOPY jta_sync_task.task_rec);
169
170 FUNCTION get_max_enddate(p_recurrence_rule_id IN NUMBER)
171 RETURN DATE;
172
173 PROCEDURE make_prefix(
174 p_assignment_status_id IN NUMBER,
175 p_source_object_type_code IN VARCHAR2,
176 p_resource_type IN VARCHAR2,
177 p_resource_id IN NUMBER,
178 p_group_id IN NUMBER,
179 x_subject IN OUT NOCOPY VARCHAR2
180 );
181
182 PROCEDURE check_delete_data(
183 p_task_id IN NUMBER,
184 p_resource_id IN NUMBER,
185 p_objectcode IN VARCHAR2,
186 x_status_id OUT NOCOPY NUMBER,
187 x_delete_flag OUT NOCOPY VARCHAR2
188 );
189
190 FUNCTION get_assignment_id (p_task_id IN NUMBER,
191 p_resource_id IN NUMBER,
192 p_resource_type IN VARCHAR2 DEFAULT 'RS_EMPLOYEE'
193 )
194 RETURN NUMBER;
195
196 FUNCTION get_assignment_status_id (p_task_id IN NUMBER,
197 p_resource_id IN NUMBER
198 )
199 RETURN NUMBER;
200
201 PROCEDURE get_assignment_info(p_task_id IN NUMBER
202 ,p_resource_id IN NUMBER
203 ,x_assignee_role OUT NOCOPY VARCHAR2
204 ,x_assignment_status_id OUT NOCOPY NUMBER);
205
206 PROCEDURE get_owner_info(p_task_id IN NUMBER,
207 x_task_name OUT NOCOPY VARCHAR2,
208 x_owner_id OUT NOCOPY NUMBER,
209 x_owner_type_code OUT NOCOPY VARCHAR2);
210
211 FUNCTION get_access(p_group_id IN VARCHAR2
212 ,p_resource_id IN NUMBER
213 )
214 RETURN VARCHAR2;
215
216 FUNCTION get_source_object_type(p_task_id IN NUMBER)
217 RETURN VARCHAR2;
218
219 FUNCTION get_update_type (
220 p_task_id IN NUMBER,
221 p_resource_id IN NUMBER,
222 p_subject IN VARCHAR2
223 )
224 RETURN VARCHAR2;
225
226 FUNCTION get_recurrence_rule_id(p_task_id IN NUMBER)
227 RETURN NUMBER;
228
229 PROCEDURE convert_recur_date_to_client(p_base_start_time IN DATE,
230 p_base_end_time IN DATE,
231 p_start_date IN DATE,
232 p_end_date IN DATE,
233 p_occurs_which IN NUMBER,
234 p_uom IN VARCHAR2,
235 x_date_of_month OUT NOCOPY NUMBER,
236 x_start_date IN OUT NOCOPY DATE,
237 x_end_date IN OUT NOCOPY DATE);
238
239 PROCEDURE get_all_nonrepeat_tasks(
240 p_request_type IN VARCHAR2,
241 p_syncanchor IN DATE,
242 p_recordindex IN NUMBER,
243 p_resource_id IN NUMBER,
244 p_resource_type IN VARCHAR2,
245 p_source_object_type IN VARCHAR2,
246 p_get_data IN BOOLEAN,
247 x_totalnew IN OUT NOCOPY NUMBER,
248 x_totalmodified IN OUT NOCOPY NUMBER,
249 x_data IN OUT NOCOPY jta_sync_task.task_tbl
250 --p_new_syncanchor in date
251 );
252 PROCEDURE get_all_deleted_tasks(
253 p_request_type IN VARCHAR2,
254 p_syncanchor IN DATE,
255 p_recordindex IN NUMBER,
256 p_resource_id IN NUMBER,
257 p_resource_type IN VARCHAR2,
258 p_source_object_type IN VARCHAR2,
259 p_get_data IN BOOLEAN,
260 x_totaldeleted IN OUT NOCOPY NUMBER,
261 x_data IN OUT NOCOPY jta_sync_task.task_tbl
262 --p_new_syncanchor in date
263 );
264 PROCEDURE get_all_repeat_tasks(
265 p_request_type IN VARCHAR2,
266 p_syncanchor IN DATE,
267 p_recordindex IN NUMBER,
268 p_resource_id IN NUMBER,
269 p_resource_type IN VARCHAR2,
270 p_source_object_type IN VARCHAR2,
271 p_get_data IN BOOLEAN,
272 x_totalnew IN OUT NOCOPY NUMBER,
273 x_totalmodified IN OUT NOCOPY NUMBER,
274 -- x_totaldeleted IN OUT NOCOPY NUMBER,
275 x_data IN OUT NOCOPY jta_sync_task.task_tbl,
276 x_exclusion_data IN OUT NOCOPY Jta_Sync_Task.exclusion_tbl
277 --p_new_syncanchor in date
278 );
279
280 PROCEDURE create_new_data(p_task_rec IN OUT NOCOPY jta_sync_task.task_rec
281 ,p_mapping_type IN VARCHAR2 DEFAULT G_NEW -- Fixed bug 2497963 for 9i issue
282 ,p_exclusion_tbl IN jta_sync_task.exclusion_tbl
283 ,p_resource_id IN NUMBER
284 ,p_resource_type IN VARCHAR2
285 );
286
287 PROCEDURE update_existing_data(p_task_rec IN OUT NOCOPY jta_sync_task.task_rec
288 ,p_exclusion_tbl IN jta_sync_task.exclusion_tbl
289 ,p_resource_id IN NUMBER
290 ,p_resource_type IN VARCHAR2);
291
292 PROCEDURE delete_exclusion_task(
293 p_repeating_task_id IN NUMBER
294 ,x_task_rec IN OUT NOCOPY jta_sync_task.task_rec
295 );
296
297 PROCEDURE delete_task_data(
298 p_task_rec IN OUT NOCOPY jta_sync_task.task_rec
299 ,p_delete_map_flag IN BOOLEAN DEFAULT TRUE
300 );
301
302 PROCEDURE reject_task_data(
303 p_task_rec IN OUT NOCOPY jta_sync_task.task_rec
304 );
305
306 FUNCTION changed_repeat_rule(p_task_rec IN jta_sync_task.task_rec)
307 RETURN BOOLEAN;
308
309 -- function to check if a repeating task is on exclusion list
310 FUNCTION check_for_exclusion(p_sync_id IN NUMBER,
311 p_exclusion_tbl IN jta_sync_task.exclusion_tbl,
312 p_calendar_start_date IN DATE,
316 FUNCTION get_excluding_taskid (p_sync_id IN NUMBER,
313 p_client_time_zone_id NUMBER)
314 RETURN BOOLEAN;
315
317 p_recurrence_rule_id IN NUMBER,
318 p_exclusion_rec IN jta_sync_task.exclusion_rec)
319 RETURN NUMBER;
320
321 PROCEDURE transformStatus(p_task_status_id IN out NOCOPY NUMBER,
322 p_task_sync_id IN NUMBER,
323 x_operation IN OUT NOCOPY VARCHAR2
324 );
325
326 FUNCTION getChangedStatusId(p_task_status_id IN NUMBER,
327 p_source_object_type_code IN VARCHAR2
328 )
329 RETURN NUMBER;
330
331 FUNCTION checkUserStatusRule RETURN BOOLEAN;
332
333 FUNCTION is_this_new_task (
334 p_sync_id IN NUMBER
335 )
336 RETURN BOOLEAN;
337
338 FUNCTION get_task_id (
339 p_sync_id IN NUMBER
340 )
341 RETURN NUMBER;
342
343 FUNCTION get_ovn (p_task_id IN NUMBER)
344 RETURN NUMBER;
345
346 FUNCTION get_ovn (p_task_assignment_id IN NUMBER)
347 RETURN NUMBER;
348
349 PROCEDURE do_mapping (
350 p_task_id IN NUMBER,
351 p_operation IN VARCHAR2,
352 x_task_sync_id IN OUT NOCOPY NUMBER
353 );
354
355 FUNCTION already_selected(p_task_id IN NUMBER DEFAULT NULL
356 ,p_sync_id IN NUMBER DEFAULT NULL
357 ,p_task_tbl IN jta_sync_task.task_tbl)
358 RETURN BOOLEAN;
359
360 -- this procedure is made public only for testing ...should not be used otherwise
361 PROCEDURE add_task (
362 p_request_type IN VARCHAR2,
363 p_resource_id IN NUMBER,
364 p_resource_type IN VARCHAR2,
365 p_recordindex IN NUMBER,
366 p_operation IN VARCHAR2,
367 p_task_sync_id IN NUMBER,
368 p_task_id IN NUMBER,
369 p_task_name IN VARCHAR2,
370 p_owner_type_code IN VARCHAR2,
371 p_description IN VARCHAR2,
372 p_task_status_id IN NUMBER,
373 p_task_priority_id IN NUMBER,
374 p_private_flag IN VARCHAR2,
375 p_date_selected IN VARCHAR2,
376 p_timezone_id IN NUMBER,
377 p_syncanchor IN DATE,
378 p_planned_start_date IN DATE,
379 p_planned_end_date IN DATE,
380 p_scheduled_start_date IN DATE,
381 p_scheduled_end_date IN DATE,
382 p_actual_start_date IN DATE,
383 p_actual_end_date IN DATE,
384 p_calendar_start_date IN DATE,
385 p_calendar_end_date IN DATE,
386 p_alarm_on IN VARCHAR2,
387 p_alarm_start IN NUMBER,
388 p_recurrence_rule_id IN NUMBER,
389 p_occurs_uom IN VARCHAR2,
390 p_occurs_every IN NUMBER,
391 p_occurs_number IN NUMBER,
392 p_start_date_active IN DATE,
393 p_end_date_active IN DATE,
394 p_sunday IN VARCHAR2,
395 p_monday IN VARCHAR2,
396 p_tuesday IN VARCHAR2,
397 p_wednesday IN VARCHAR2,
398 p_thursday IN VARCHAR2,
399 p_friday IN VARCHAR2,
400 p_saturday IN VARCHAR2,
401 p_date_of_month IN VARCHAR2,
402 p_occurs_which IN VARCHAR2,
403 x_task_rec IN OUT NOCOPY jta_sync_task.task_rec
404 );
405
406 FUNCTION set_alarm_date (
407 p_task_id IN NUMBER,
408 p_request_type IN VARCHAR2,
409 p_scheduled_start_date IN DATE,
410 p_planned_start_date IN DATE,
411 p_actual_start_date IN DATE,
412 p_alarm_flag IN VARCHAR2,
413 p_alarm_start IN NUMBER
414 )
415 RETURN DATE ;
416
417 -- Added to fix bug 2382927
418 FUNCTION validate_syncid(p_syncid IN NUMBER)
419 RETURN BOOLEAN;
420
421 END jta_sync_task_common; -- Package spec
[Home] [Help]