[Home] [Help]
PACKAGE BODY: APPS.CAC_SYNC_TASK_UTL
Source
1 PACKAGE BODY cac_sync_task_utl AS
2 /* $Header: cacvstnb.pls 120.1 2005/06/10 20:20:59 rhshriva noship $ */
3 /*=======================================================================+
4 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +========================================================================+
7 | FILENAME
8 | jtavstnb.pls
9 |
10 | DESCRIPTION
11 | This package defines the utility commonly used for sync.
12 |
13 | NOTES
14 |
15 | UPDATE NOTES
16 | Date Developer Change
17 |------------ --------------- -------------------------------------
18 | 28-May-2002 Chanik Jang Created.
19 *=======================================================================*/
20
21 FUNCTION is_this_first_task(p_task_id IN NUMBER)
22 RETURN BOOLEAN
23 IS
24 CURSOR c_first_taskid (b_task_id NUMBER) IS
25 SELECT min(task_id) task_id
26 FROM jtf_tasks_b
27 WHERE NVL(deleted_flag,'N') = 'N'
28 AND recurrence_rule_id = (SELECT recurrence_rule_id
29 FROM jtf_tasks_b
30 WHERE task_id = b_task_id);
31
32 rec_first_taskid c_first_taskid%ROWTYPE;
33 l_result BOOLEAN := FALSE;
34 BEGIN
35 OPEN c_first_taskid(b_task_id => p_task_id);
36 FETCH c_first_taskid INTO rec_first_taskid;
37 IF c_first_taskid%FOUND
38 THEN
39 IF p_task_id = rec_first_taskid.task_id
40 THEN
41 l_result := TRUE;
42 END IF;
43 END IF;
44 CLOSE c_first_taskid;
45
46 RETURN l_result;
47 END is_this_first_task;
48
49 FUNCTION get_new_first_taskid(p_calendar_start_date IN DATE,
50 p_recurrence_rule_id IN NUMBER)
51 RETURN NUMBER
52 IS
53 CURSOR c_task (b_start_date DATE, b_recurrence_rule_id NUMBER) IS
54 SELECT task_id
55 FROM jtf_tasks_b
56 WHERE calendar_start_date > b_start_date
57 AND recurrence_rule_id = b_recurrence_rule_id
58 AND NVL(deleted_flag,'N') = 'N'
59 HAVING ROWNUM = 1
60 GROUP BY ROWNUM, task_id, calendar_start_date
61 ORDER BY calendar_start_date;
62
63 l_task_id NUMBER;
64 BEGIN
65 OPEN c_task(p_calendar_start_date, p_recurrence_rule_id);
66 FETCH c_task INTO l_task_id;
67 IF c_task%NOTFOUND
68 THEN
69 l_task_id := 0;
70 END IF;
71 CLOSE c_task;
72
73 RETURN l_task_id;
74 END get_new_first_taskid;
75
76 FUNCTION exist_syncid(p_task_id IN NUMBER,
77 x_sync_id OUT NOCOPY NUMBER,
78 x_principal_id OUT NOCOPY NUMBER)
79 RETURN BOOLEAN
80 IS
81 CURSOR c_sync (b_task_id NUMBER) IS
82 SELECT task_sync_id, principal_id
83 FROM jta_sync_task_mapping
84 WHERE task_id = b_task_id;
85
86 l_result BOOLEAN := FALSE;
87 BEGIN
88 OPEN c_sync (p_task_id);
89 FETCH c_sync INTO x_sync_id,x_principal_id ;
90 ----------------------------------------------------------------------
91 -- If it's already been sycned,
92 -- then we must update the mapping table with the new minimum task id
93 ----------------------------------------------------------------------
94 IF c_sync%FOUND
95 THEN
96 l_result := TRUE;
97 END IF;
98 CLOSE c_sync;
99
100 RETURN l_result;
101 END exist_syncid;
102
103 PROCEDURE update_mapping(p_task_id IN NUMBER)
104 IS
105 CURSOR c_assignee (b_task_id NUMBER) IS
106 SELECT jtaa.resource_id
107 , jtb.calendar_start_date
108 , jtb.recurrence_rule_id
109 FROM jtf_task_all_assignments jtaa
110 , jtf_tasks_b jtb
111 WHERE jtaa.task_id = b_task_id
112 AND jtb.task_id = jtaa.task_id;
113
114 l_sync_id NUMBER;
115 l_principal_id NUMBER;
116 l_first BOOLEAN := FALSE;
117 l_exist_new_first_task BOOLEAN := FALSE;
118 l_new_minimum_task_id NUMBER;
119 BEGIN
120 l_first := is_this_first_task(p_task_id => p_task_id);
121
122 FOR rec_assignee IN c_assignee(p_task_id)
123 LOOP
124 l_new_minimum_task_id := get_new_first_taskid(
125 p_calendar_start_date => rec_assignee.calendar_start_date,
126 p_recurrence_rule_id => rec_assignee.recurrence_rule_id
127 );
128 IF l_new_minimum_task_id > 0
129 THEN
130 l_exist_new_first_task := TRUE;
131 END IF;
132
133 IF l_first and l_exist_new_first_task
134 THEN
135 IF exist_syncid(p_task_id => p_task_id,
136 x_sync_id => l_sync_id,
137 x_principal_id => l_principal_id)
138 THEN
139 cac_sync_task_map_pkg.update_row (
140 p_task_sync_id => l_sync_id,
141 p_task_id => l_new_minimum_task_id,
142 p_resource_id => rec_assignee.resource_id,
143 p_principal_id => l_principal_id
144 );
145 END IF;
146 END IF;
147 END LOOP;
148
149 END update_mapping;
150
151 END cac_sync_task_utl;