1 PACKAGE BODY jtf_task_mass_pvt AS
2 /* $Header: jtfvtkbb.pls 120.2 2005/09/29 05:12:39 knayyar ship $ */
3
4 ----------------------------------------------------------------------------------------------------
5 -- Start of comments
6 -- Procedure Name : create_mass_tasks
7 -- Type : Private
8 -- Function : Create tasks for all the members of a group or a team.
9 -- Pre reqs : None
10 -- Parameters :
11 -- name direction type required
12 -- ------ --------- ---- --------
13 -- p_api_version IN NUMBER required
14 -- p_init_msg_list IN VARCHAR2 optional
15 -- x_msg_count OUT NOCOPY NUMBER required
16 -- x_msg_data OUT NOCOPY VARCHAR2 required
17 -- x_return_status OUT NOCOPY VARCHAR2 required
18 -- p_resource_type IN VARCHAR2 required
19 -- p_resource_id IN NUMBER required
20 -- p_task_id IN NUMBER required
21 -- p_task_number IN VARCHAR2 required
22 -- p_keep_record IN VARCHAR2 optional
23 -- p_keep_resource_id IN NUMBER optional
24 -- p_copy_notes IN VARCHAR2 optional
25 -- p_copy_task_assignments IN VARCHAR2 optional
26 -- p_copy_task_rsc_reqs IN VARCHAR2 optional
27 -- p_copy_task_depends IN VARCHAR2 optional
28 -- p_create_recurrences IN VARCHAR2 optional
29 -- p_copy_task_references IN VARCHAR2 optional
30 -- p_copy_task_dates IN VARCHAR2 optional
31
32 -----------------------------------------------------------------------------------------------------
33 PROCEDURE create_mass_tasks( p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
35 p_commit IN VARCHAR2 DEFAULT fnd_api.g_true,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2,
39 p_resource_type IN VARCHAR2,
40 p_resource_id IN NUMBER,
41 p_task_id IN NUMBER,
42 p_task_number IN VARCHAR2,
43 p_keep_record IN VARCHAR2 DEFAULT NULL,
44 p_keep_resource_id IN NUMBER DEFAULT NULL,
45 p_copy_notes IN VARCHAR2 DEFAULT fnd_api.g_false,
46 p_copy_task_assignments IN VARCHAR2 DEFAULT fnd_api.g_false,
47 p_copy_task_rsc_reqs IN VARCHAR2 DEFAULT fnd_api.g_false,
48 p_copy_task_depends IN VARCHAR2 DEFAULT fnd_api.g_false,
49 p_create_recurrences IN VARCHAR2 DEFAULT fnd_api.g_false,
50 p_copy_task_references IN VARCHAR2 DEFAULT fnd_api.g_false,
51 p_copy_task_dates IN VARCHAR2 DEFAULT fnd_api.g_false) IS
52
53
54 --
55 -- Declare a ref cursor
56 --
57 TYPE MassCurTyp IS REF CURSOR;
58 mass_task MassCurTyp;
59
60 --
61 -- Declare local and bind variables
62 --
63 l_task_resource_id number;
64 l_resource_type VARCHAR2(100);
65 l_sql_stmt VARCHAR2(1000);
66 l_api_version CONSTANT NUMBER := 1.0;
67 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MASS_TASKS';
68 l_task_id NUMBER;
69 l_task_assignment_id NUMBER;
70
71 --
72 -- Declare a cursor to return a resource_type for a given resource_id
73 --
74 CURSOR c_resource_type(b_task_resource_id in number) IS
75 select resource_type
76 -- Bug Fix 2909730, Bug# 4455786 MOAC.
77 from jtf_task_resources_vl
78 where resource_id = b_task_resource_id;
79
80 BEGIN
81 --
82 -- Standard start of API savepoint
83 --
84 SAVEPOINT create_mass_tasks;
85
86 --
87 -- initialize API return status to success
88 --
89 x_return_status := fnd_api.g_ret_sts_success;
90
91 --
92 -- Standard call to check for call compatibility
93 --
94 IF NOT fnd_api.compatible_api_call (
95 l_api_version,
96 p_api_version,
97 l_api_name,
98 g_pkg_name
99 )
100 THEN
101 RAISE fnd_api.g_exc_unexpected_error;
102 END IF;
103
104 --
105 -- Initialize message list if p_init_msg_list is set to TRUE
106 --
107 IF fnd_api.to_boolean (p_init_msg_list)
108 THEN
109 fnd_msg_pub.initialize;
110 END IF;
111
112 --
113 --Call the private package JTF_TASK_UTL to validate a given task
114 --
115 jtf_task_utl.validate_task(
116 x_return_status => x_return_status,
117 p_task_id => p_task_id,
118 p_task_number => p_task_number,
119 x_task_id => l_task_id);
120
121 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
122 THEN
123 x_return_status := fnd_api.g_ret_sts_unexp_error;
124 RAISE fnd_api.g_exc_unexpected_error;
125 END IF;
126
127 --
128 -- Check if the passed resource_id and resource_type are not null
129 --
130 IF (p_resource_id IS NOT NULL AND p_resource_type IS NOT NULL) THEN
131
132 --
136 --
133 -- If the resource_type is a Group resource then get all the members of a given group
134 --
135 IF (p_resource_type = 'RS_GROUP') THEN
137 -- If p_keep_record is 'Y' then a new task should be created even if one already exists for the owner.
138 --
139 IF (p_keep_record = 'Y') THEN
140 l_sql_stmt := 'SELECT resource_id FROM jtf_rs_group_members WHERE group_id = :id and delete_flag = ''N''';
141 ELSE
142 l_sql_stmt := 'SELECT resource_id FROM jtf_rs_group_members WHERE group_id = :id and resource_id != :keep_resource_id and delete_flag = ''N''';
143 END IF;
144
145 --
146 --If the resource_type is a Team resource then get all the members of a given team
147 --
148 ELSIF (p_resource_type = 'RS_TEAM') THEN
149 --
150 -- If p_keep_record is 'Y' then a new task should be created even if one already exists for the owner.
151 --
152 IF (p_keep_record = 'Y') THEN
153 l_sql_stmt := 'SELECT team_resource_id FROM jtf_rs_team_members WHERE team_id = :id and resource_type = ''INDIVIDUAL'' and delete_flag = ''N''';
154 ELSE
155 l_sql_stmt := 'SELECT team_resource_id FROM jtf_rs_team_members WHERE team_id = :id and resource_type = ''INDIVIDUAL'' and team_resource_id != :keep_resource_id and delete_flag = ''N''';
156 END IF;
157 END IF;
158
159 IF (p_keep_record = 'Y' and p_keep_resource_id is not null) THEN
160 --
161 -- If p_keep_record is 'Y' then select all the members for a given group or a team
162 --
163 OPEN mass_task FOR l_sql_stmt USING p_resource_id;
164 ELSE
165 --
166 -- If p_keep_record is 'N' then select all the members for a given group or a team except the task owner
167 --
168 OPEN mass_task FOR l_sql_stmt USING p_resource_id, p_keep_resource_id;
169 END IF;
170 LOOP
171 --
172 -- Fetch the task_resource_id
173 --
174 FETCH mass_task INTO l_task_resource_id;
175 EXIT WHEN mass_task%NOTFOUND;
176
177 --
178 -- Get the resource_type for a given resource_id
179 --
180 OPEN c_resource_type(l_task_resource_id);
181 FETCH c_resource_type into l_resource_type;
182 CLOSE c_resource_type;
183
184 --
185 -- Call the public API JTF_TASKS_PUB to Copy a task for all the members of a group or a team
186 --
187 jtf_tasks_pub.copy_task (
188 p_api_version => p_api_version,
189 p_init_msg_list => p_init_msg_list,
190 p_commit => p_commit,
191 p_source_task_id => p_task_id,
192 p_source_task_number => p_task_number,
193 p_copy_task_references => fnd_api.g_true,
194 x_return_status => x_return_status,
195 p_copy_notes => p_copy_notes,
196 p_copy_task_assignments => fnd_api.g_false,
197 p_resource_id => l_task_resource_id,
198 p_resource_type => l_resource_type,
199 x_msg_count => x_msg_count,
200 x_msg_data => x_msg_data,
201 x_task_id => l_task_id,
202 p_copy_task_contacts => fnd_api.g_true,
203 p_copy_task_contact_points => fnd_api.g_true
204 );
205
206 --dbms_output.put_line('task_id :' ||l_task_id);
207
208 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
209 THEN
210 x_return_status := fnd_api.g_ret_sts_unexp_error;
211 RAISE fnd_api.g_exc_unexpected_error;
212 END IF;
213
214 END LOOP;
215 --
216 --Close the cursor
217 --
218 CLOSE mass_task;
219 END IF;
220
221 --
222 --Close the cursor
223 --
224 IF mass_task%ISOPEN
225 THEN
226 CLOSE mass_task;
227 END IF;
228
229 --
230 --Close the cursor
231 --
232 IF c_resource_type%ISOPEN
233 THEN
234 CLOSE c_resource_type;
235 END IF;
236
237 EXCEPTION
238
239 WHEN fnd_api.g_exc_unexpected_error
240 THEN
241 ROLLBACK TO create_mass_tasks;
242 x_return_status := fnd_api.g_ret_sts_unexp_error;
243 fnd_msg_pub.count_and_get (
244 p_count => x_msg_count,
245 p_data => x_msg_data
246 );
247
248 --
249 --Close the cursor
250 --
251 IF mass_task%ISOPEN
252 THEN
253 CLOSE mass_task;
254 END IF;
255
256 --
257 --Close the cursor
258 --
259 IF c_resource_type%ISOPEN
260 THEN
261 CLOSE c_resource_type;
262 END IF;
263
264 WHEN OTHERS
265 THEN
266 ROLLBACK TO create_mass_tasks;
267 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
268 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
269 x_return_status := fnd_api.g_ret_sts_unexp_error;
270 fnd_msg_pub.count_and_get (
271 p_count => x_msg_count,
272 p_data => x_msg_data
273 );
274 END create_mass_tasks;
275
276
277 ----------------------------------------------------------------------------------------------------
278 -- Start of comments
279 -- Procedure Name : validate_resource
280 -- Type : private
281 -- Function : Validate a given resource.
282 -- Pre reqs : None
283 -- Parameters :
284 -- name direction type required
285 -- ------ --------- ---- --------
286 -- p_api_version IN NUMBER required
287 -- p_init_msg_list IN VARCHAR2 optional
288 -- p_resource_type IN VARCHAR2 required
289 -- p_resource_type_id IN NUMBER required
290 -- x_msg_count OUT NOCOPY NUMBER required
291 -- x_msg_data OUT NOCOPY VARCHAR2 required
292 -- x_return_status OUT NOCOPY VARCHAR2 required
293
294 -----------------------------------------------------------------------------------------------------
295 Procedure validate_resource(p_api_version in number,
296 p_init_msg_list in varchar2,
297 p_resource_type in varchar2,
298 p_resource_type_id in number,
299 x_msg_count OUT NOCOPY number,
300 x_msg_data OUT NOCOPY varchar2,
301 x_return_status OUT NOCOPY varchar2) is
302
303 --
304 -- Declare local variables
305 --
306 l_resource_id number;
307
308 --
309 -- Declare a cursor to select the resource_id
310 --
311 cursor c_resource is
312 select resource_id
313 -- Bug Fix 2909730, Bug# 4455786 MOAC.
314 from jtf_task_resources_vl
315 where resource_id = p_resource_type_id
316 and resource_type = p_resource_type;
317 Begin
318
319 --
320 -- initialize API return status to success
321 --
322 x_return_status := fnd_api.g_ret_sts_success;
323
324 --
325 -- Fetch the resource_id into a local variable
326 --
327 open c_resource;
328 fetch c_resource into l_resource_id;
329 close c_resource;
330
331 --
332 -- Check if the resource_id exists
333 --
334 If l_resource_id is null then
335 x_return_status := fnd_api.g_ret_sts_unexp_error;
336 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OWNER_ID');
337 fnd_message.set_token ('P_OWNER_ID', p_resource_type_id);
338 fnd_msg_pub.add;
339 RAISE fnd_api.g_exc_unexpected_error;
340 end if;
341
342 --
343 --Close the cursor
344 --
345 IF c_resource%ISOPEN
346 THEN
347 CLOSE c_resource;
348 END IF;
349
350 exception
351
352 WHEN fnd_api.g_exc_unexpected_error THEN
353 x_return_status := fnd_api.g_ret_sts_unexp_error;
354 fnd_msg_pub.count_and_get (
355 p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358
359 --
360 --Close the cursor
361 --
362 IF c_resource%ISOPEN
363 THEN
364 CLOSE c_resource;
365 END IF;
366
367 WHEN OTHERS THEN
368 x_return_status := fnd_api.g_ret_sts_unexp_error;
369 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_OWNER_ID');
370 fnd_message.set_token ('P_OWNER_ID', p_resource_type_id);
371 fnd_msg_pub.add;
372 RAISE fnd_api.g_exc_unexpected_error;
373
374 End validate_resource;
375
376 END;
[Home] [Help]