[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_RESOURCES_PUB
Source
1 PACKAGE BODY jtf_task_resources_pub AS
2 /* $Header: jtfptkrb.pls 120.1.12000000.3 2007/08/06 12:14:22 rkamasam ship $ */
3 PROCEDURE create_task_rsrc_req(
4 p_api_version IN NUMBER
5 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
6 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
7 , p_task_id IN NUMBER DEFAULT fnd_api.g_miss_num
8 , p_task_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char
9 , p_task_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char
10 , p_task_type_id IN NUMBER DEFAULT fnd_api.g_miss_num
11 , p_task_type_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char
12 , p_task_template_id IN NUMBER DEFAULT fnd_api.g_miss_num
13 , p_task_template_name IN VARCHAR2 DEFAULT fnd_api.g_miss_char
14 , p_resource_type_code IN VARCHAR2
15 , p_required_units IN NUMBER
16 , p_enabled_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no
17 , x_return_status OUT NOCOPY VARCHAR2
18 , x_msg_count OUT NOCOPY NUMBER
19 , x_msg_data OUT NOCOPY VARCHAR2
20 , x_resource_req_id OUT NOCOPY NUMBER
21 , p_attribute1 IN VARCHAR2 DEFAULT NULL
22 , p_attribute2 IN VARCHAR2 DEFAULT NULL
23 , p_attribute3 IN VARCHAR2 DEFAULT NULL
24 , p_attribute4 IN VARCHAR2 DEFAULT NULL
25 , p_attribute5 IN VARCHAR2 DEFAULT NULL
26 , p_attribute6 IN VARCHAR2 DEFAULT NULL
27 , p_attribute7 IN VARCHAR2 DEFAULT NULL
28 , p_attribute8 IN VARCHAR2 DEFAULT NULL
29 , p_attribute9 IN VARCHAR2 DEFAULT NULL
30 , p_attribute10 IN VARCHAR2 DEFAULT NULL
31 , p_attribute11 IN VARCHAR2 DEFAULT NULL
32 , p_attribute12 IN VARCHAR2 DEFAULT NULL
33 , p_attribute13 IN VARCHAR2 DEFAULT NULL
34 , p_attribute14 IN VARCHAR2 DEFAULT NULL
35 , p_attribute15 IN VARCHAR2 DEFAULT NULL
36 , p_attribute_category IN VARCHAR2 DEFAULT NULL
37 ) IS
38 --Declare the variables
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK_RSRC_REQ';
41 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
42 l_task_id jtf_tasks_b.task_id%TYPE := p_task_id;
43 l_task_number jtf_tasks_b.task_number%TYPE := p_task_number;
44 l_task_name jtf_tasks_tl.task_name%TYPE := p_task_name;
45 l_task_type_id jtf_task_types_b.task_type_id%TYPE := p_task_type_id;
46 l_task_type_name jtf_task_types_tl.NAME%TYPE := p_task_type_name;
47 l_task_template_id jtf_task_templates_b.task_template_id%TYPE := p_task_template_id;
48 l_task_template_name jtf_task_templates_tl.task_name%TYPE := p_task_template_name;
49 l_enabled_flag jtf_task_rsc_reqs.enabled_flag%TYPE := p_enabled_flag;
50 l_resource_type_code jtf_task_rsc_reqs.resource_type_code%TYPE := p_resource_type_code;
51 l_required_units jtf_task_rsc_reqs.required_units%TYPE := p_required_units;
52 l_msg_data VARCHAR2(2000);
53 l_msg_count NUMBER;
54 x CHAR;
55 --
56 BEGIN
57 SAVEPOINT create_task_rsrc_req;
58
59 x_return_status := fnd_api.g_ret_sts_success;
60
61 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
62 RAISE fnd_api.g_exc_unexpected_error;
63 END IF;
64
65 IF fnd_api.to_boolean(p_init_msg_list) THEN
66 fnd_msg_pub.initialize;
67 END IF;
68
69 --Check for Required Parameters
70 --Resource subtype
71 IF l_resource_type_code IS NULL THEN
72 x_return_status := fnd_api.g_ret_sts_unexp_error;
73 fnd_message.set_name('JTF', 'JTF_TASK_NULL_RES_TYPE_CODE');
74 fnd_msg_pub.ADD;
75 RAISE fnd_api.g_exc_unexpected_error;
76 END IF;
77
78 --Required Units
79 IF l_required_units IS NULL THEN
80 x_return_status := fnd_api.g_ret_sts_unexp_error;
81 fnd_message.set_name('JTF', 'JTF_TASK_NULL_REQ_UNIT');
82 fnd_msg_pub.ADD;
83 RAISE fnd_api.g_exc_unexpected_error;
84 END IF;
85
86 --- Validate enabled flag
87 jtf_task_utl.validate_flag(
88 p_api_name => l_api_name
89 , p_init_msg_list => fnd_api.g_false
90 , x_return_status => l_return_status
91 , p_flag_name => 'Enabled_Flag'
92 , p_flag_value => l_enabled_flag
93 );
94
95 IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
96 fnd_message.set_name('JTF', 'JTF_TASK_INVALID_FLAG');
97 fnd_message.set_token('ENABLED_FLAG', p_enabled_flag);
98 RAISE fnd_api.g_exc_unexpected_error;
99 END IF;
100
101 ---- if Enabled flag is null,Task is not Active
102
103 --Validations for Task_id ,Task_template_id and task_type _id
104 --based on the status of P_ENABLED_FLAG
105 IF l_enabled_flag IN(jtf_task_utl.g_yes, jtf_task_utl.g_no) THEN
106 IF (p_task_template_id <> fnd_api.g_miss_num OR p_task_template_name <> fnd_api.g_miss_char) THEN
107 SELECT DECODE(p_task_template_id, fnd_api.g_miss_num, NULL, p_task_template_id)
108 INTO l_task_template_id
109 FROM DUAL;
110
111 jtf_task_resources_pvt.validate_task_template(
112 x_return_status => l_return_status
113 , p_task_template_id => l_task_template_id
114 , p_task_name => l_task_template_name
115 , x_task_template_id => l_task_template_id
116 , x_task_name => l_task_template_name
117 );
118
119 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
120 RAISE fnd_api.g_exc_error;
121 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
122 RAISE fnd_api.g_exc_unexpected_error;
123 END IF;
124 ELSIF(
125 p_task_id <> fnd_api.g_miss_num
126 OR p_task_name <> fnd_api.g_miss_char
127 OR p_task_number <> fnd_api.g_miss_char
128 ) THEN
129 SELECT DECODE(p_task_id, fnd_api.g_miss_num, NULL, p_task_id)
130 INTO l_task_id
131 FROM DUAL;
132
133 SELECT DECODE(p_task_number, fnd_api.g_miss_char, NULL, p_task_number)
134 INTO l_task_number
135 FROM DUAL;
136
137 jtf_task_utl.validate_task(
138 x_return_status => l_return_status
139 , p_task_id => l_task_id
140 , p_task_number => l_task_number
141 , x_task_id => l_task_id
142 );
143
144 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
145 RAISE fnd_api.g_exc_error;
146 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
147 RAISE fnd_api.g_exc_unexpected_error;
148 END IF;
149
150 IF l_task_id IS NULL THEN
151 x_return_status := fnd_api.g_ret_sts_unexp_error;
152 fnd_message.set_name('JTF', 'JTF_TASK_INVALID_TASK_ID');
153 fnd_message.set_token('TASK_ID', p_task_id);
154 fnd_msg_pub.ADD;
155 RAISE fnd_api.g_exc_unexpected_error;
156 END IF;
157 ELSIF(p_task_type_id <> fnd_api.g_miss_num OR p_task_type_name <> fnd_api.g_miss_char) THEN
158 SELECT DECODE(p_task_type_id, fnd_api.g_miss_num, NULL, p_task_type_id)
159 INTO l_task_type_id
160 FROM DUAL;
161
162 SELECT DECODE(p_task_type_name, fnd_api.g_miss_char, NULL, p_task_type_name)
163 INTO l_task_type_name
164 FROM DUAL;
165
166 jtf_task_resources_pvt.validate_task_type(
167 x_return_status => l_return_status
168 , p_task_type_id => l_task_type_id
169 , p_name => l_task_type_name
170 , x_task_type_id => l_task_type_id
171 , x_task_name => l_task_type_name
172 );
173
174 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
175 RAISE fnd_api.g_exc_error;
176 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
177 RAISE fnd_api.g_exc_unexpected_error;
178 END IF;
179
180 IF l_task_type_id IS NULL THEN
181 x_return_status := fnd_api.g_ret_sts_unexp_error;
182 fnd_message.set_name('JTF', 'JTF_TASK_INVALID_TYPE_ID');
183 fnd_message.set_token('TASK_ID', p_task_type_id);
184 fnd_msg_pub.ADD;
185 RAISE fnd_api.g_exc_unexpected_error;
186 END IF;
187 ELSIF (l_task_template_id IS NULL) AND(l_task_id IS NULL) AND(l_task_type_id IS NULL) THEN
188 x_return_status := fnd_api.g_ret_sts_unexp_error;
189 fnd_message.set_name('JTF', 'JTF_TASK_INV_INPUT');
190 fnd_msg_pub.ADD;
191 RAISE fnd_api.g_exc_unexpected_error;
192 ELSIF (l_task_template_id IS NOT NULL)
193 AND (l_task_id IS NOT NULL)
194 AND (l_task_type_id IS NOT NULL) THEN
195 x_return_status := fnd_api.g_ret_sts_unexp_error;
196 fnd_message.set_name('JTF', 'JTF_TASK_INV_INPUT');
197 fnd_msg_pub.ADD;
198 RAISE fnd_api.g_exc_unexpected_error;
199 ELSIF (l_task_template_id IS NOT NULL) AND(l_task_id IS NOT NULL) THEN
200 x_return_status := fnd_api.g_ret_sts_unexp_error;
201 fnd_message.set_name('JTF', 'JTF_TASK_INV_INPUT');
202 fnd_msg_pub.ADD;
203 RAISE fnd_api.g_exc_unexpected_error;
204 ELSIF (l_task_id IS NOT NULL) AND(l_task_type_id IS NOT NULL) THEN
205 x_return_status := fnd_api.g_ret_sts_unexp_error;
206 fnd_message.set_name('JTF', 'JTF_TASK_INV_INPUT');
207 fnd_msg_pub.ADD;
208 RAISE fnd_api.g_exc_unexpected_error;
209 ELSIF (l_task_template_id IS NOT NULL) AND(l_task_type_id IS NOT NULL) THEN
210 x_return_status := fnd_api.g_ret_sts_unexp_error;
211 fnd_message.set_name('JTF', 'JTF_TASK_INV_INPUT');
212 fnd_msg_pub.ADD;
213 RAISE fnd_api.g_exc_unexpected_error;
214 END IF;
215
216 IF NOT jtf_task_resources_pvt.validate_resource_type_code(p_resource_type_code => l_resource_type_code) THEN
217 fnd_message.set_name('JTF', 'JTF_TASK_INV_RES_TYP_COD');
218 fnd_message.set_token('RESOURCE_CODE', p_resource_type_code);
219 --fnd_msg.add;
220 RAISE fnd_api.g_exc_error;
221 END IF;
222
223 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
224 fnd_message.set_name('JTF', 'JTF_TASK_INV_RES_TYP_COD');
225 fnd_message.set_token('RESOURCE_CODE', p_resource_type_code);
226 --fnd_msg.add;
227 RAISE fnd_api.g_exc_error;
228 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
229 fnd_message.set_name('JTF', 'JTF_TASK_INV_RES_TYP_COD');
230 fnd_message.set_token('RESOURCE_CODE', p_resource_type_code);
231 --fnd_msg.add;
232 RAISE fnd_api.g_exc_unexpected_error;
233 END IF;
234
235 --Call to private API to insert into JTF_TASK_RESOURCE_REQ
236 jtf_task_resources_pvt.create_task_rsrc_req(
237 p_api_version => l_api_version
238 , p_init_msg_list => fnd_api.g_false
239 , p_commit => fnd_api.g_false
240 , p_task_id => l_task_id
241 , p_task_name => l_task_name
242 , p_task_number => l_task_number
243 , p_task_type_id => l_task_type_id
244 , p_task_type_name => l_task_name
245 , p_task_template_id => l_task_template_id
246 , p_task_template_name => l_task_template_name
247 , p_resource_type_code => l_resource_type_code
248 , p_required_units => l_required_units
249 , p_enabled_flag => l_enabled_flag
250 , x_return_status => l_return_status
251 , x_msg_data => l_msg_data
252 , x_msg_count => l_msg_count
253 , x_resource_req_id => x_resource_req_id
254 , p_attribute1 => p_attribute1
255 , p_attribute2 => p_attribute2
256 , p_attribute3 => p_attribute3
257 , p_attribute4 => p_attribute4
258 , p_attribute5 => p_attribute5
259 , p_attribute6 => p_attribute6
260 , p_attribute7 => p_attribute7
261 , p_attribute8 => p_attribute8
262 , p_attribute9 => p_attribute9
263 , p_attribute10 => p_attribute10
264 , p_attribute11 => p_attribute11
265 , p_attribute12 => p_attribute12
266 , p_attribute13 => p_attribute13
267 , p_attribute14 => p_attribute14
268 , p_attribute15 => p_attribute15
269 , p_attribute_category => p_attribute_category
270 );
271 END IF;
272
273 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
274 RAISE fnd_api.g_exc_error;
275 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
276 RAISE fnd_api.g_exc_unexpected_error;
277 END IF;
278
279 IF fnd_api.to_boolean(p_commit) THEN
280 COMMIT WORK;
281 END IF;
282
283 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
284 EXCEPTION
285 WHEN fnd_api.g_exc_error THEN
286 ROLLBACK TO create_task_rsrc_req;
287 x_return_status := fnd_api.g_ret_sts_error;
288 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
289 WHEN fnd_api.g_exc_unexpected_error THEN
290 ROLLBACK TO create_task_rsrc_req;
291 x_return_status := fnd_api.g_ret_sts_unexp_error;
292 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
293 WHEN OTHERS THEN
294 ROLLBACK TO create_task_rsrc_req;
295 x_return_status := fnd_api.g_ret_sts_unexp_error;
296 /* if fnd_msg_pub.check_msg_level ( fnd_msg_pub.g_msg_lvl_unexp_error )
297 then
298 fnd_msg_pub.add_exc_msg(g_pkg_name,l_api_name) ;
299 end if ;*/
300 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
301 END;
302
303 --Procedure to Update the Task Resource Requirements
304 PROCEDURE update_task_rscr_req(
305 p_api_version IN NUMBER
306 , p_object_version_number IN OUT NOCOPY NUMBER
307 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
308 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
309 , p_resource_req_id IN NUMBER
310 , p_task_id IN NUMBER DEFAULT fnd_api.g_miss_num
311 , p_task_name IN VARCHAR2 DEFAULT NULL
312 , p_task_number IN VARCHAR2 DEFAULT NULL
313 , p_task_type_id IN NUMBER DEFAULT fnd_api.g_miss_num
314 , p_task_type_name IN VARCHAR2 DEFAULT NULL
315 , p_task_template_id IN NUMBER DEFAULT fnd_api.g_miss_num
316 , p_task_template_name IN VARCHAR2 DEFAULT NULL
317 , p_resource_type_code IN VARCHAR2
318 , p_required_units IN NUMBER
319 , p_enabled_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no
320 , x_return_status OUT NOCOPY VARCHAR2
321 , x_msg_count OUT NOCOPY NUMBER
322 , x_msg_data OUT NOCOPY VARCHAR2
326 , p_attribute4 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
323 , p_attribute1 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
324 , p_attribute2 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
325 , p_attribute3 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
327 , p_attribute5 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
328 , p_attribute6 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
329 , p_attribute7 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
330 , p_attribute8 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
331 , p_attribute9 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
332 , p_attribute10 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
333 , p_attribute11 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
334 , p_attribute12 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
335 , p_attribute13 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
336 , p_attribute14 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
337 , p_attribute15 IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
338 , p_attribute_category IN VARCHAR2 DEFAULT jtf_task_utl.g_miss_char
339 ) IS
340 l_api_version CONSTANT NUMBER := 1.0;
341 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_RSRC_REQ';
342 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
343 l_task_id jtf_tasks_b.task_id%TYPE := p_task_id;
344 l_task_number jtf_tasks_b.task_number%TYPE := p_task_number;
345 l_task_name jtf_tasks_tl.task_name%TYPE := p_task_name;
346 l_task_type_id jtf_task_types_b.task_type_id%TYPE := p_task_type_id;
347 l_task_type_name jtf_task_types_tl.NAME%TYPE := p_task_type_name;
348 l_task_template_id jtf_task_templates_b.task_template_id%TYPE := p_task_template_id;
349 l_task_template_name jtf_task_templates_tl.task_name%TYPE := p_task_template_name;
350 l_enabled_flag jtf_task_rsc_reqs.enabled_flag%TYPE := p_enabled_flag;
351 l_resource_type_code jtf_task_rsc_reqs.resource_type_code%TYPE := p_resource_type_code;
352 l_required_units jtf_task_rsc_reqs.required_units%TYPE := p_required_units;
353 l_msg_data VARCHAR2(2000);
354 l_msg_count NUMBER;
355 x CHAR;
356 l_resource_req_id NUMBER := p_resource_req_id;
357 l_rowid ROWID;
358
359 CURSOR jtf_rsrc_req_cur IS
360 SELECT DECODE(p_task_id, fnd_api.g_miss_num, task_id, p_task_id) task_id
361 , DECODE(p_task_template_id, fnd_api.g_miss_num, task_template_id, p_task_template_id)
362 task_template_id
363 , p_task_type_id task_type_id
364 , p_resource_type_code resource_type_code
365 , p_required_units required_units
366 , enabled_flag
367 , DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) attribute1
368 , DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) attribute2
369 , DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) attribute3
370 , DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) attribute4
371 , DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) attribute5
372 , DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) attribute6
373 , DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) attribute7
374 , DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) attribute8
375 , DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) attribute9
376 , DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) attribute10
377 , DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) attribute11
378 , DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) attribute12
379 , DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) attribute13
380 , DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) attribute14
381 , DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) attribute15
382 , DECODE(
383 p_attribute_category
384 , fnd_api.g_miss_char, attribute_category
385 , p_attribute_category
386 ) attribute_category
387 FROM jtf_task_rsc_reqs
388 WHERE resource_req_id = p_resource_req_id;
389
390 task_rsrc_req_rec jtf_rsrc_req_cur%ROWTYPE;
391 BEGIN
392 SAVEPOINT update_task_rsrc_req;
393 x_return_status := fnd_api.g_ret_sts_success;
394
395 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
396 RAISE fnd_api.g_exc_unexpected_error;
397 END IF;
398
399 IF fnd_api.to_boolean(p_init_msg_list) THEN
400 fnd_msg_pub.initialize;
401 END IF;
402
403 -- check if the resource requirement is invalid or null.
404 IF (l_resource_req_id IS NULL) THEN
405 fnd_message.set_name('JTF', 'JTF_TASK_NULL_RES_REQ_ID');
409 END IF;
406 fnd_msg_pub.ADD;
407 x_return_status := fnd_api.g_ret_sts_unexp_error;
408 RAISE fnd_api.g_exc_unexpected_error;
410
411 OPEN jtf_rsrc_req_cur;
412
413 FETCH jtf_rsrc_req_cur
414 INTO task_rsrc_req_rec;
415
416 IF jtf_rsrc_req_cur%NOTFOUND THEN
417 fnd_message.set_name('JTF', 'JTF_TASK_INV_RES_REQ_ID');
418 fnd_message.set_token('RESOURCE_REQ_ID', p_resource_req_id);
419 fnd_msg_pub.ADD;
420 x_return_status := fnd_api.g_ret_sts_unexp_error;
421 RAISE fnd_api.g_exc_unexpected_error;
422 END IF;
423
424 ---enabled_flag
425 l_enabled_flag := task_rsrc_req_rec.enabled_flag;
426
427 --- Validating for the task
428 IF NOT(p_task_id = fnd_api.g_miss_num AND p_task_number = fnd_api.g_miss_char) THEN
429 SELECT DECODE(p_task_id, fnd_api.g_miss_num, NULL, p_task_id)
430 INTO l_task_id
431 FROM DUAL;
432
433 SELECT DECODE(p_task_number, fnd_api.g_miss_char, NULL, p_task_number)
434 INTO l_task_number
435 FROM DUAL;
436
437 IF (l_enabled_flag = fnd_api.g_true) THEN
438 ---- Here the task id is assigned null, if the task id is NOT input,
439 --- because then task number could be input.
440
441 --- This means task id is being updated.
442 jtf_task_utl.validate_task(
443 x_return_status => l_return_status
444 , p_task_id => l_task_id
445 , p_task_number => l_task_number
446 , x_task_id => l_task_id
447 );
448 ELSE
449 jtf_task_utl.validate_task_template(
450 x_return_status => l_return_status
451 , p_task_id => l_task_template_id
452 , p_task_number => NULL
453 , x_task_id => l_task_template_id
454 );
455 END IF;
456
457 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
458 RAISE fnd_api.g_exc_error;
459 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
460 RAISE fnd_api.g_exc_unexpected_error;
461 END IF;
462
463 IF l_task_id IS NULL THEN
464 x_return_status := fnd_api.g_ret_sts_unexp_error;
465 fnd_message.set_name('JTF', 'JTF_TASK_INVALID_TASK_ID');
466 fnd_message.set_token('TASK_ID', p_task_id);
467 fnd_msg_pub.ADD;
468 RAISE fnd_api.g_exc_unexpected_error;
469 END IF;
470 ELSE
471 l_task_id := task_rsrc_req_rec.task_id;
472 END IF;
473
474 jtf_task_resources_pub.lock_task_resources(
475 p_api_version => 1.0
476 , p_init_msg_list => fnd_api.g_false
477 , p_commit => fnd_api.g_false
478 , p_resource_requirement_id => l_resource_req_id
479 , p_object_version_number => p_object_version_number
480 , x_return_status => x_return_status
481 , x_msg_data => x_msg_data
482 , x_msg_count => x_msg_count
483 );
484
485 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
486 x_return_status := fnd_api.g_ret_sts_unexp_error;
487 RAISE fnd_api.g_exc_unexpected_error;
488 END IF;
489
490 jtf_task_resources_pvt.update_task_rscr_req
491 (
492 p_api_version => 1.0
493 , p_object_version_number => p_object_version_number
494 , p_init_msg_list => fnd_api.g_false
495 , p_commit => fnd_api.g_false
496 , p_resource_req_id => l_resource_req_id
497 , p_task_id => l_task_id
498 , p_task_name => l_task_name
499 , p_task_number => NULL
500 , p_task_type_id => l_task_type_id
501 , p_task_type_name => NULL
502 , p_task_template_id => l_task_template_id
503 , p_task_template_name => NULL
504 , p_resource_type_code => l_resource_type_code
505 , p_required_units => l_required_units
506 , p_enabled_flag => l_enabled_flag
507 , x_return_status => l_return_status
508 , x_msg_data => l_msg_data
509 , x_msg_count => l_msg_count
510 , p_attribute1 => task_rsrc_req_rec.attribute1
511 , p_attribute2 => task_rsrc_req_rec.attribute2
512 , p_attribute3 => task_rsrc_req_rec.attribute3
513 , p_attribute4 => task_rsrc_req_rec.attribute4
514 , p_attribute5 => task_rsrc_req_rec.attribute5
515 , p_attribute6 => task_rsrc_req_rec.attribute6
516 , p_attribute7 => task_rsrc_req_rec.attribute7
517 , p_attribute8 => task_rsrc_req_rec.attribute8
518 , p_attribute9 => task_rsrc_req_rec.attribute9
519 , p_attribute10 => task_rsrc_req_rec.attribute10
520 , p_attribute11 => task_rsrc_req_rec.attribute11
521 , p_attribute12 => task_rsrc_req_rec.attribute12
522 , p_attribute13 => task_rsrc_req_rec.attribute13
523 , p_attribute14 => task_rsrc_req_rec.attribute14
527
524 , p_attribute15 => task_rsrc_req_rec.attribute15
525 , p_attribute_category => task_rsrc_req_rec.attribute_category
526 );
528 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
529 RAISE fnd_api.g_exc_error;
530 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
531 RAISE fnd_api.g_exc_unexpected_error;
532 END IF;
533
534 IF fnd_api.to_boolean(p_commit) THEN
535 COMMIT WORK;
536 END IF;
537
538 IF jtf_rsrc_req_cur%ISOPEN THEN
539 CLOSE jtf_rsrc_req_cur;
540 END IF;
541
542 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
543 EXCEPTION
544 WHEN fnd_api.g_exc_error THEN
545 ROLLBACK TO update_task_rsrc_req;
546
547 IF jtf_rsrc_req_cur%ISOPEN THEN
548 CLOSE jtf_rsrc_req_cur;
549 END IF;
550
551 x_return_status := fnd_api.g_ret_sts_error;
552 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
553 WHEN fnd_api.g_exc_unexpected_error THEN
554 ROLLBACK TO update_task_rsrc_req;
555
556 IF jtf_rsrc_req_cur%ISOPEN THEN
557 CLOSE jtf_rsrc_req_cur;
558 END IF;
559
560 x_return_status := fnd_api.g_ret_sts_unexp_error;
561 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
562 WHEN OTHERS THEN
563 ROLLBACK TO update_task_rsrc_req;
564
565 IF jtf_rsrc_req_cur%ISOPEN THEN
566 CLOSE jtf_rsrc_req_cur;
567 END IF;
568
569 x_return_status := fnd_api.g_ret_sts_unexp_error;
570
571 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
572 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
573 END IF;
574
575 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
576 END;
577
578 --Procedure to Delete the Task Resource Requirements
579 PROCEDURE delete_task_rsrc_req(
580 p_api_version IN NUMBER
581 , p_object_version_number IN NUMBER
582 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
583 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
584 , p_resource_req_id IN NUMBER
585 , x_return_status OUT NOCOPY VARCHAR2
586 , x_msg_count OUT NOCOPY NUMBER
587 , x_msg_data OUT NOCOPY VARCHAR2
588 ) IS
589 l_api_version CONSTANT NUMBER := 1.0;
590 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TASK_RSRC_REQ';
591 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
592 l_msg_data VARCHAR2(2000);
593 l_msg_count NUMBER;
594 l_resource_req_id jtf_task_rsc_reqs.resource_req_id%TYPE := p_resource_req_id;
595
596 CURSOR jtf_task_rsc_req_u_cur IS
597 SELECT 1
598 FROM jtf_task_rsc_reqs
599 WHERE resource_req_id = l_resource_req_id;
600
601 x CHAR;
602 BEGIN
603 SAVEPOINT delete_task_rsrc_req;
604 x_return_status := fnd_api.g_ret_sts_success;
605
606 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
607 RAISE fnd_api.g_exc_unexpected_error;
608 END IF;
609
610 IF fnd_api.to_boolean(p_init_msg_list) THEN
611 fnd_msg_pub.initialize;
612 END IF;
613
614 ---- if resource req is null, then it is an error
615 IF (l_resource_req_id IS NULL) THEN
616 fnd_message.set_name('JTF', 'JTF_TASK_DELETING_RES_REQ_ID');
617 fnd_message.set_token('RESOURCE_REQ_ID', p_resource_req_id);
618 fnd_msg_pub.ADD;
619 x_return_status := fnd_api.g_ret_sts_unexp_error;
620 RAISE fnd_api.g_exc_unexpected_error;
621 END IF;
622
623 ---- if resource req is NOT valid, then it is an error
624 OPEN jtf_task_rsc_req_u_cur;
625
626 FETCH jtf_task_rsc_req_u_cur
627 INTO x;
628
629 IF jtf_task_rsc_req_u_cur%NOTFOUND THEN
630 fnd_message.set_name('JTF', 'JTF_TASK_INV_RES_REQ_ID');
631 fnd_message.set_token('TASK_ID', p_resource_req_id);
632 fnd_msg_pub.ADD;
633 x_return_status := fnd_api.g_ret_sts_unexp_error;
634 RAISE fnd_api.g_exc_unexpected_error;
635 ELSE
636 jtf_task_resources_pub.lock_task_resources
637 (
638 p_api_version => 1.0
639 , p_init_msg_list => fnd_api.g_false
640 , p_commit => fnd_api.g_false
641 , p_resource_requirement_id => l_resource_req_id
642 , p_object_version_number => p_object_version_number
643 , x_return_status => x_return_status
644 , x_msg_data => x_msg_data
645 , x_msg_count => x_msg_count
646 );
647
648 IF NOT(x_return_status = fnd_api.g_ret_sts_success) THEN
649 x_return_status := fnd_api.g_ret_sts_unexp_error;
650 RAISE fnd_api.g_exc_unexpected_error;
651 END IF;
652
653 jtf_task_resources_pvt.delete_task_rsrc_req
654 (
655 p_api_version => l_api_version
656 , p_object_version_number => p_object_version_number
660 , x_return_status => l_return_status
657 , p_init_msg_list => fnd_api.g_false
658 , p_commit => fnd_api.g_false
659 , p_resource_req_id => l_resource_req_id
661 , x_msg_count => l_msg_count
662 , x_msg_data => l_msg_data
663 );
664
665 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
666 RAISE fnd_api.g_exc_error;
667 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
668 RAISE fnd_api.g_exc_unexpected_error;
669 END IF;
670 END IF;
671
672 IF jtf_task_rsc_req_u_cur%ISOPEN THEN
673 CLOSE jtf_task_rsc_req_u_cur;
674 END IF;
675
676 IF fnd_api.to_boolean(p_commit) THEN
677 COMMIT WORK;
678 END IF;
679
680 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
681 EXCEPTION
682 WHEN fnd_api.g_exc_error THEN
683 ROLLBACK TO delete_task_rsrc_req;
684
685 IF jtf_task_rsc_req_u_cur%ISOPEN THEN
686 CLOSE jtf_task_rsc_req_u_cur;
687 END IF;
688
689 x_return_status := fnd_api.g_ret_sts_error;
690 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
691 WHEN fnd_api.g_exc_unexpected_error THEN
692 ROLLBACK TO delete_task_rsrc_req;
693
694 IF jtf_task_rsc_req_u_cur%ISOPEN THEN
695 CLOSE jtf_task_rsc_req_u_cur;
696 END IF;
697
698 x_return_status := fnd_api.g_ret_sts_unexp_error;
699 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
700 WHEN OTHERS THEN
701 ROLLBACK TO delete_task_rsrc_req;
702
703 IF jtf_task_rsc_req_u_cur%ISOPEN THEN
704 CLOSE jtf_task_rsc_req_u_cur;
705 END IF;
706
707 x_return_status := fnd_api.g_ret_sts_unexp_error;
708 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
709 END;
710
711 --Procedure to get the Task Resource Req
712 PROCEDURE get_task_rsrc_req(
713 p_api_version IN NUMBER
714 , p_init_msg_list IN VARCHAR2 DEFAULT g_false
715 , p_commit IN VARCHAR2 DEFAULT g_false
716 , p_resource_req_id IN NUMBER
717 , p_resource_req_name IN VARCHAR2 DEFAULT NULL
718 , p_task_id IN NUMBER DEFAULT NULL
719 , p_task_name IN VARCHAR2 DEFAULT NULL
720 , p_task_type_id IN NUMBER DEFAULT NULL
721 , p_task_type_name IN VARCHAR2 DEFAULT NULL
722 , p_task_template_id IN NUMBER DEFAULT NULL
723 , p_task_template_name IN VARCHAR2 DEFAULT NULL
724 , p_sort_data IN jtf_task_resources_pub.sort_data
725 , p_query_or_next_code IN VARCHAR2 DEFAULT 'Q'
726 , p_start_pointer IN NUMBER
727 , p_rec_wanted IN NUMBER
728 , p_show_all IN VARCHAR2 DEFAULT 'Y'
729 , p_resource_type_code IN VARCHAR2
730 , p_required_units IN NUMBER
731 , p_enabled_flag IN VARCHAR2 DEFAULT jtf_task_utl.g_no
732 , x_return_status OUT NOCOPY VARCHAR2
733 , x_msg_count OUT NOCOPY NUMBER
734 , x_msg_data OUT NOCOPY VARCHAR2
735 , x_task_rsc_req_rec OUT NOCOPY jtf_task_resources_pub.task_rsc_req_tbl
736 , x_total_retrieved OUT NOCOPY NUMBER
737 , x_total_returned OUT NOCOPY NUMBER
738 ) IS
739 l_api_version CONSTANT NUMBER := 1.0;
740 l_api_name CONSTANT VARCHAR2(30) := 'GET_TASK_RSRC_REQ';
741 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
742 l_msg_data VARCHAR2(2000);
743 l_msg_count NUMBER;
744 l_resource_type_code jtf_task_rsc_reqs.resource_type_code%TYPE := p_resource_type_code;
745 l_task_id jtf_task_rsc_reqs.task_id%TYPE := p_task_id;
746 l_task_type_id jtf_task_rsc_reqs.task_type_id%TYPE := p_task_type_id;
747 l_task_template_id jtf_task_rsc_reqs.task_template_id%TYPE := p_task_template_id;
748 l_required_units NUMBER := p_required_units;
749 l_enabled_flag VARCHAR2(1) := p_enabled_flag;
750 l_task_name VARCHAR2(80) := p_task_name;
751 l_task_type_name VARCHAR2(30) := p_task_type_name;
752 l_task_template_name VARCHAR2(80) := p_task_template_name;
753 l_resource_req_id jtf_task_rsc_reqs.resource_req_id%TYPE := p_resource_req_id;
754 BEGIN
755 SAVEPOINT get_task_rsrc_req;
756 x_return_status := fnd_api.g_ret_sts_success;
757
758 -- standard call to check for call compatibility
759 IF (NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name)) THEN
760 RAISE fnd_api.g_exc_unexpected_error;
761 END IF;
762
763 -- initialize message list i p_init_msg_list is set to true
764 IF (fnd_api.to_boolean(p_init_msg_list)) THEN
765 fnd_msg_pub.initialize;
766 END IF;
767
768 -- required parameters to control records returned
769
773 fnd_msg_pub.ADD;
770 -- p_query_or_next_code should be Q or N
771 IF (p_query_or_next_code NOT IN('Q', 'N')) OR(p_query_or_next_code IS NULL) THEN
772 fnd_message.set_name('JTF', 'JTF_TK_INV_QRY_NXT');
774 RAISE fnd_api.g_exc_error;
775 END IF;
776
777 -- p_show_all should be Y or N
778 IF (p_show_all NOT IN('Y', 'N')) OR(p_show_all IS NULL) THEN
779 fnd_message.set_name('JTF', 'JTF_TK_INV_SHOW_ALL');
780 fnd_msg_pub.ADD;
781 RAISE fnd_api.g_exc_error;
782 END IF;
783
784 IF (p_show_all = 'N') THEN
785 IF (p_start_pointer IS NULL) THEN
786 fnd_message.set_name('JTF', 'JTF_TK_NULL_STRT_PTR');
787 fnd_msg_pub.ADD;
788 RAISE fnd_api.g_exc_error;
789 END IF;
790
791 IF (p_rec_wanted IS NULL) THEN
792 fnd_message.set_name('JTF', 'JTF_TK_NULL_REC_WANT');
793 fnd_msg_pub.ADD;
794 RAISE fnd_api.g_exc_error;
795 END IF;
796 END IF;
797
798 jtf_task_resources_pvt.get_task_rsrc_req(
799 p_api_version
800 , p_init_msg_list
801 , p_commit
802 , p_resource_req_id
803 , p_resource_req_name
804 , p_task_id
805 , p_task_name
806 , p_task_type_id
807 , p_task_type_name
808 , p_task_template_id
809 , p_task_template_name
810 , p_sort_data
811 , p_query_or_next_code
812 , p_start_pointer
813 , p_rec_wanted
814 , p_show_all
815 , p_resource_type_code
816 , p_required_units
817 , p_enabled_flag
818 , x_return_status
819 , x_msg_count
820 , x_msg_data
821 , x_task_rsc_req_rec
822 , x_total_retrieved
823 , x_total_returned
824 );
825 EXCEPTION
826 WHEN fnd_api.g_exc_error THEN
827 ROLLBACK TO get_task_rsrc_req;
828 x_return_status := fnd_api.g_ret_sts_error;
829 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
830 WHEN fnd_api.g_exc_unexpected_error THEN
831 ROLLBACK TO get_task_rsrc_req;
832 x_return_status := fnd_api.g_ret_sts_unexp_error;
833 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
834 WHEN OTHERS THEN
835 ROLLBACK TO get_task_rsrc_req;
836 x_return_status := fnd_api.g_ret_sts_unexp_error;
837
838 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
839 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
840 END IF;
841
842 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
843 END;
844
845 PROCEDURE lock_task_resources(
846 p_api_version IN NUMBER
847 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
848 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
849 , p_resource_requirement_id IN NUMBER
850 , p_object_version_number IN NUMBER
851 , x_return_status OUT NOCOPY VARCHAR2
852 , x_msg_data OUT NOCOPY VARCHAR2
853 , x_msg_count OUT NOCOPY NUMBER
854 ) IS
855 l_api_version CONSTANT NUMBER := 1.0;
856 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_TASK_RESOURCES';
857 resource_locked EXCEPTION;
858 PRAGMA EXCEPTION_INIT(resource_locked, -54);
859 BEGIN
860 SAVEPOINT lock_task_resources_pub;
861 x_return_status := fnd_api.g_ret_sts_success;
862
863 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
864 RAISE fnd_api.g_exc_unexpected_error;
865 END IF;
866
867 IF fnd_api.to_boolean(p_init_msg_list) THEN
868 fnd_msg_pub.initialize;
869 END IF;
870
871 x_return_status := fnd_api.g_ret_sts_success;
872 jtf_task_rsc_reqs_pkg.lock_row(
873 x_resource_req_id => p_resource_requirement_id
874 , x_object_version_number => p_object_version_number
875 );
876 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
877 EXCEPTION
878 WHEN resource_locked THEN
879 ROLLBACK TO lock_task_resources_pub;
880 fnd_message.set_name('JTF', 'JTF_TASK_RESOURCE_LOCKED');
881 fnd_message.set_token('P_LOCKED_RESOURCE', 'Resources');
882 x_return_status := fnd_api.g_ret_sts_unexp_error;
883 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
884 WHEN fnd_api.g_exc_unexpected_error THEN
885 ROLLBACK TO lock_task_resources_pub;
886 x_return_status := fnd_api.g_ret_sts_unexp_error;
887 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
888 WHEN OTHERS THEN
889 ROLLBACK TO lock_task_resources_pub;
890 fnd_message.set_name('JTF', 'JTF_TASK_UNKNOWN_ERROR');
891 fnd_message.set_token('P_TEXT', SQLCODE || SQLERRM);
892 x_return_status := fnd_api.g_ret_sts_unexp_error;
893 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
894 END;
895 END;