1 PACKAGE BODY jta_sync_task_utl AS
2 /* $Header: jtavstnb.pls 120.2 2006/02/10 02:48:13 sbarat ship $ */
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 | 10-Feb-2006 Swapan Barat Added NOCOPY hint for OUT parameter. Bug# 5029957
20 | (Using dual check-in option to check-in file so that the
21 | code can be propagated in both ver 11 and 12 line, since
22 | NOCOPY is a mandatory mandate for all pl/sql packages.)
23 *=======================================================================*/
24
25 FUNCTION is_this_first_task(p_task_id IN NUMBER)
26 RETURN BOOLEAN
27 IS
28 CURSOR c_first_taskid (b_task_id NUMBER) IS
29 SELECT min(task_id) task_id
30 FROM jtf_tasks_b
31 WHERE NVL(deleted_flag,'N') = 'N'
32 AND recurrence_rule_id = (SELECT recurrence_rule_id
33 FROM jtf_tasks_b
34 WHERE task_id = b_task_id);
35
36 rec_first_taskid c_first_taskid%ROWTYPE;
37 l_result BOOLEAN := FALSE;
38 BEGIN
39 OPEN c_first_taskid(b_task_id => p_task_id);
40 FETCH c_first_taskid INTO rec_first_taskid;
41 IF c_first_taskid%FOUND
42 THEN
43 IF p_task_id = rec_first_taskid.task_id
44 THEN
45 l_result := TRUE;
46 END IF;
47 END IF;
48 CLOSE c_first_taskid;
49
50 RETURN l_result;
51 END is_this_first_task;
52
53 FUNCTION get_new_first_taskid(p_calendar_start_date IN DATE,
54 p_recurrence_rule_id IN NUMBER)
55 RETURN NUMBER
56 IS
57 CURSOR c_task (b_start_date DATE, b_recurrence_rule_id NUMBER) IS
58 SELECT task_id
59 FROM jtf_tasks_b
60 WHERE calendar_start_date > b_start_date
61 AND recurrence_rule_id = b_recurrence_rule_id
62 AND NVL(deleted_flag,'N') = 'N'
63 HAVING ROWNUM = 1
64 GROUP BY ROWNUM, task_id, calendar_start_date
65 ORDER BY calendar_start_date;
66
67 l_task_id NUMBER;
68 BEGIN
69 OPEN c_task(p_calendar_start_date, p_recurrence_rule_id);
70 FETCH c_task INTO l_task_id;
71 IF c_task%NOTFOUND
72 THEN
73 l_task_id := 0;
74 END IF;
75 CLOSE c_task;
76
77 RETURN l_task_id;
78 END get_new_first_taskid;
79
80 FUNCTION exist_syncid(p_task_id IN NUMBER,
81 x_sync_id OUT NOCOPY NUMBER)
82 RETURN BOOLEAN
83 IS
84 CURSOR c_sync (b_task_id NUMBER) IS
85 SELECT task_sync_id
86 FROM jta_sync_task_mapping
87 WHERE task_id = b_task_id;
88
89 l_result BOOLEAN := FALSE;
90 BEGIN
91 OPEN c_sync (p_task_id);
92 FETCH c_sync INTO x_sync_id;
93 ----------------------------------------------------------------------
94 -- If it's already been sycned,
95 -- then we must update the mapping table with the new minimum task id
96 ----------------------------------------------------------------------
97 IF c_sync%FOUND
98 THEN
99 l_result := TRUE;
100 END IF;
101 CLOSE c_sync;
102
103 RETURN l_result;
104 END exist_syncid;
105
106 PROCEDURE update_mapping(p_task_id IN NUMBER)
107 IS
108 CURSOR c_assignee (b_task_id NUMBER) IS
109 SELECT jtaa.resource_id
110 , jtb.calendar_start_date
111 , jtb.recurrence_rule_id
112 FROM jtf_task_all_assignments jtaa
113 , jtf_tasks_b jtb
114 WHERE jtaa.task_id = b_task_id
115 AND jtb.task_id = jtaa.task_id;
116
117 l_sync_id NUMBER;
118 l_first BOOLEAN := FALSE;
119 l_exist_new_first_task BOOLEAN := FALSE;
120 l_new_minimum_task_id NUMBER;
121 BEGIN
122 l_first := is_this_first_task(p_task_id => p_task_id);
123
124 FOR rec_assignee IN c_assignee(p_task_id)
125 LOOP
126 l_new_minimum_task_id := get_new_first_taskid(
127 p_calendar_start_date => rec_assignee.calendar_start_date,
128 p_recurrence_rule_id => rec_assignee.recurrence_rule_id
129 );
130 IF l_new_minimum_task_id > 0
131 THEN
132 l_exist_new_first_task := TRUE;
133 END IF;
134
135 IF l_first and l_exist_new_first_task
136 THEN
137 IF exist_syncid(p_task_id => p_task_id,
138 x_sync_id => l_sync_id)
139 THEN
140 jta_sync_task_map_pkg.update_row (
141 p_task_sync_id => l_sync_id,
142 p_task_id => l_new_minimum_task_id,
143 p_resource_id => rec_assignee.resource_id
144 );
145 END IF;
146 END IF;
147 END LOOP;
148
149 END update_mapping;
150
151 END jta_sync_task_utl;
[Home] [Help]