1 PACKAGE BODY csf_requests_pvt AS
2 /* $Header: CSFVREQB.pls 120.5 2008/02/07 12:50:40 venjayar ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_REQUESTS_PVT';
4
5 PROCEDURE create_scheduler_request(
6 p_api_version IN NUMBER
7 , p_init_msg_list IN VARCHAR2
8 , p_commit IN VARCHAR2
9 , x_return_status OUT NOCOPY VARCHAR2
10 , x_msg_count OUT NOCOPY NUMBER
11 , x_msg_data OUT NOCOPY VARCHAR2
12 , p_name IN VARCHAR2
13 , p_object_id IN NUMBER
14 , p_spares_likelihood IN NUMBER
15 , p_resource_tbl IN resource_tbl_type
16 , p_status_id IN NUMBER
17 , p_route_based_flag IN VARCHAR2
18 , p_changed_option_start IN DATE
19 , p_changed_option_end IN DATE
20 , p_changed_planned_start IN DATE
21 , p_changed_planned_end IN DATE
22 , p_disabled_access_hours_flag IN VARCHAR2
23 , p_set_plan_task_confirmed IN VARCHAR2
24 , p_parent_id IN NUMBER
25 , x_request_id OUT NOCOPY NUMBER
26 ) IS
27 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SCHEDULER_REQUEST';
28 l_api_version CONSTANT NUMBER := 1.0;
29
30 l_request_task_id NUMBER;
31 BEGIN
32 -- Standard start of API savepoint
33 SAVEPOINT createschedulerrequest;
34
35 -- Standard call to check for call compatibility
36 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
37 RAISE fnd_api.g_exc_unexpected_error;
38 END IF;
39
40 -- Initialize message list if p_init_msg_list is set to TRUE
41 IF fnd_api.to_boolean(p_init_msg_list) THEN
42 fnd_msg_pub.initialize;
43 END IF;
44
45 -- Initialize API return status to success
46 x_return_status := fnd_api.g_ret_sts_success;
47
48 INSERT INTO csf_r_sched_requests(
49 sched_request_id
50 , sched_request_name
51 , spares_likelihood
52 , parent_request_id
53 , planmode -- this column is obsolete; will be removed
54 , route_based_flag
55 , target_status_id
56 , changed_option_start
57 , changed_option_end
58 , changed_planned_start
59 , changed_planned_end
60 , disabled_access_hours_flag
61 , customer_conf_rcvd
62 , created_by
63 , creation_date
64 , last_updated_by
65 , last_update_date
66 , object_version_number
67 )
68 VALUES (
69 csf_r_sched_requests_s1.NEXTVAL
70 , p_name
71 , p_spares_likelihood
72 , p_parent_id
73 , -1 -- dummy value for obsolete but mandatory column
74 , p_route_based_flag
75 , p_status_id
76 , p_changed_option_start
77 , p_changed_option_end
78 , p_changed_planned_start
79 , p_changed_planned_end
80 , p_disabled_access_hours_flag
81 , p_set_plan_task_confirmed
82 , fnd_global.user_id
83 , SYSDATE
84 , fnd_global.user_id
85 , SYSDATE
86 , 1
87 )
88 RETURNING sched_request_id INTO x_request_id;
89
90 -- insert task into the csf_r_request_tasks table
91 INSERT INTO csf_r_request_tasks(
92 request_task_id
93 , sched_request_id
94 , task_id
95 , object_version_number
96 )
97 VALUES (
98 csf_r_request_tasks_s1.NEXTVAL
99 , x_request_id
100 , p_object_id
101 , 1
102 )
103 RETURNING request_task_id INTO l_request_task_id;
104
105 create_resource_results(
106 p_api_version => 1
107 , p_init_msg_list => fnd_api.g_false
108 , p_commit => fnd_api.g_false
109 , x_return_status => x_return_status
110 , x_msg_count => x_msg_count
111 , x_msg_data => x_msg_data
112 , p_request_task_id => l_request_task_id
113 , p_resource_tbl => p_resource_tbl
114 );
115
116 -- Standard check of p_commit
117 IF fnd_api.to_boolean(p_commit) THEN
118 COMMIT WORK;
119 END IF;
120 EXCEPTION
121 WHEN fnd_api.g_exc_error THEN
122 ROLLBACK TO createschedulerrequest;
123 x_return_status := fnd_api.g_ret_sts_error;
124 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
125 WHEN fnd_api.g_exc_unexpected_error THEN
126 ROLLBACK TO createschedulerrequest;
127 x_return_status := fnd_api.g_ret_sts_unexp_error;
128 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
129 WHEN OTHERS THEN
130 ROLLBACK TO createschedulerrequest;
131 x_return_status := fnd_api.g_ret_sts_unexp_error;
132 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
133 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
134 END IF;
135 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
136 END create_scheduler_request;
137
138 PROCEDURE create_resource_results(
139 p_api_version IN NUMBER
140 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
141 , p_commit IN VARCHAR2 DEFAULT NULL
142 , x_return_status OUT NOCOPY VARCHAR2
143 , x_msg_count OUT NOCOPY NUMBER
144 , x_msg_data OUT NOCOPY VARCHAR2
145 , p_request_task_id IN VARCHAR2
146 , p_resource_tbl IN resource_tbl_type
147 ) IS
148 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_RESULTS';
149 l_api_version CONSTANT NUMBER := 1.0;
150 BEGIN
151 -- Standard start of API savepoint
152 SAVEPOINT createresourceresults;
153
154 -- Standard call to check for call compatibility
155 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
156 RAISE fnd_api.g_exc_unexpected_error;
157 END IF;
158
159 -- Initialize message list if p_init_msg_list is set to TRUE
160 IF fnd_api.to_boolean(p_init_msg_list) THEN
161 fnd_msg_pub.initialize;
162 END IF;
163
164 IF p_resource_tbl IS NOT NULL AND p_resource_tbl.COUNT > 0 THEN
165 FOR i IN 1 .. p_resource_tbl.LAST LOOP
166 INSERT INTO csf_r_resource_results
167 (
168 resource_result_id
169 , request_task_id
170 , resource_id
171 , resource_type
172 , territory_rank
173 , territory_id
174 , preferred_resources_flag
175 , planwin_start
176 , planwin_end
177 , object_version_number
178 )
179 VALUES (
180 csf_r_resource_results_s1.NEXTVAL
181 , p_request_task_id
182 , p_resource_tbl(i).resource_id
183 , p_resource_tbl(i).resource_type
184 , NVL(p_resource_tbl(i).territory_rank, '1')
185 , NVL(p_resource_tbl(i).territory_id, '-1')
186 , p_resource_tbl(i).preferred_resources_flag
187 , NVL(p_resource_tbl(i).planwin_start, SYSDATE)
188 , NVL(p_resource_tbl(i).planwin_end, SYSDATE)
189 , 1
190 );
191 END LOOP;
192 END IF;
193
194 -- Initialize API return status to success
195 x_return_status := fnd_api.g_ret_sts_success;
196
197 EXCEPTION
198 WHEN fnd_api.g_exc_error THEN
199 ROLLBACK TO createresourceresults;
200 x_return_status := fnd_api.g_ret_sts_error;
201 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
202 WHEN fnd_api.g_exc_unexpected_error THEN
203 ROLLBACK TO createresourceresults;
204 x_return_status := fnd_api.g_ret_sts_unexp_error;
205 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
206 WHEN OTHERS THEN
207 ROLLBACK TO createresourceresults;
208 x_return_status := fnd_api.g_ret_sts_unexp_error;
209 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
210 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
211 END IF;
212 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
213 END create_resource_results;
214
215 PROCEDURE create_plan_option(
216 p_api_version IN NUMBER
217 , p_init_msg_list IN VARCHAR2
218 , p_commit IN VARCHAR2
219 , x_return_status OUT NOCOPY VARCHAR2
220 , x_msg_count OUT NOCOPY NUMBER
221 , x_msg_data OUT NOCOPY VARCHAR2
222 , p_request_task_id IN NUMBER
223 , p_scheduled_start_date IN DATE
224 , p_scheduled_end_date IN DATE
225 , p_resource_id IN NUMBER
226 , p_resource_type IN VARCHAR2
227 , p_cost IN NUMBER
228 , p_terr_id IN NUMBER
229 , p_win_to_promis_id IN NUMBER
230 , p_spares_cost IN NUMBER
231 , p_spares_date IN DATE
232 , x_plan_option_id OUT NOCOPY NUMBER
233 ) IS
234 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PLAN_OPTION';
235 l_api_version CONSTANT NUMBER := 1.0;
236
237 CURSOR c_resource(
238 p_request_task_id NUMBER
239 , p_resource_id NUMBER
240 , p_resource_type VARCHAR2
241 , p_spares_date DATE
242 , p_spares_cost NUMBER
243 ) IS
244 SELECT rr.resource_result_id
245 , so.spares_option_id
246 , csf_r_plan_options_s1.NEXTVAL plan_option_id
247 FROM csf_r_resource_results rr, csf_r_spares_options so
248 WHERE rr.request_task_id = p_request_task_id
249 AND rr.resource_id = p_resource_id
250 AND rr.resource_type = p_resource_type
251 AND so.resource_result_id(+) = rr.resource_result_id
252 AND so.availability_date(+) = p_spares_date
253 AND so.COST(+) = p_spares_cost;
254
255 l_resource_rec c_resource%ROWTYPE;
256 BEGIN
257 -- Standard start of API savepoint
258 SAVEPOINT createplanoption;
259
260 -- Standard call to check for call compatibility
261 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
262 RAISE fnd_api.g_exc_unexpected_error;
263 END IF;
264
265 -- Initialize message list if p_init_msg_list is set to TRUE
266 IF fnd_api.to_boolean(p_init_msg_list) THEN
267 fnd_msg_pub.initialize;
268 END IF;
269
270 -- Initialize API return status to success
271 x_return_status := fnd_api.g_ret_sts_success;
272
273 OPEN c_resource(p_request_task_id, p_resource_id, p_resource_type, p_spares_date, p_spares_cost);
274 FETCH c_resource INTO l_resource_rec;
275 IF c_resource%NOTFOUND THEN
276 CLOSE c_resource;
277 RAISE NO_DATA_FOUND;
278 END IF;
279 CLOSE c_resource;
280
281 IF l_resource_rec.spares_option_id IS NULL THEN
282 INSERT INTO csf_r_spares_options
283 (
284 spares_option_id
285 , resource_result_id
286 , availability_date
287 , COST
288 , object_version_number
289 )
290 VALUES (
291 csf_r_spares_options_s1.NEXTVAL
292 , l_resource_rec.resource_result_id
293 , p_spares_date
294 , p_spares_cost
295 , 1
296 )
297 RETURNING spares_option_id INTO l_resource_rec.spares_option_id;
298 END IF;
299
300 INSERT INTO csf_r_plan_options
301 (
302 plan_option_id
303 , resource_result_id
304 , spares_option_id
305 , scheduled_start_date
306 , scheduled_end_date
307 , COST
308 , win_to_promis_id
309 , object_version_number
310 )
311 VALUES (
312 l_resource_rec.plan_option_id
313 , l_resource_rec.resource_result_id
314 , l_resource_rec.spares_option_id
315 , p_scheduled_start_date
316 , p_scheduled_end_date
317 , p_cost
318 , p_win_to_promis_id
319 , 1
320 );
321
322 -- output plan option id
323 x_plan_option_id := l_resource_rec.plan_option_id;
324
325 -- Standard check of p_commit
326 IF fnd_api.to_boolean(p_commit) THEN
327 COMMIT WORK;
328 END IF;
329 EXCEPTION
330 WHEN fnd_api.g_exc_error THEN
331 ROLLBACK TO createplanoption;
332 x_return_status := fnd_api.g_ret_sts_error;
333 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
334 WHEN fnd_api.g_exc_unexpected_error THEN
335 ROLLBACK TO createplanoption;
336 x_return_status := fnd_api.g_ret_sts_unexp_error;
337 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
338 WHEN OTHERS THEN
339 ROLLBACK TO createplanoption;
340 x_return_status := fnd_api.g_ret_sts_unexp_error;
341
342 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
343 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
344 END IF;
345
346 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
347 END create_plan_option;
348
349 PROCEDURE create_plan_option_task(
350 p_api_version IN NUMBER
351 , p_init_msg_list IN VARCHAR2
352 , p_commit IN VARCHAR2
353 , x_return_status OUT NOCOPY VARCHAR2
354 , x_msg_count OUT NOCOPY NUMBER
355 , x_msg_data OUT NOCOPY VARCHAR2
356 , p_plan_option_id IN NUMBER
357 , p_task_id IN NUMBER
358 , p_scheduled_start_date IN DATE
359 , p_scheduled_end_date IN DATE
360 , p_travel_time IN NUMBER
361 , p_task_assign_id IN NUMBER
362 , p_trip_id IN NUMBER
363 ) IS
364 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PLAN_OPTION_TASK';
365 l_api_version CONSTANT NUMBER := 1.0;
366 BEGIN
367 -- Standard start of API savepoint
368 SAVEPOINT createplanoptiontask;
369
370 -- Standard call to check for call compatibility
371 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
372 RAISE fnd_api.g_exc_unexpected_error;
373 END IF;
374
375 -- Initialize message list if p_init_msg_list is set to TRUE
376 IF fnd_api.to_boolean(p_init_msg_list) THEN
377 fnd_msg_pub.initialize;
381 x_return_status := fnd_api.g_ret_sts_success;
378 END IF;
379
380 -- Initialize API return status to success
382
383 INSERT INTO csf_r_plan_option_tasks
384 (
385 plan_option_task_id
386 , plan_option_id
387 , task_id
388 , scheduled_start_date
389 , scheduled_end_date
390 , travel_time
391 , task_assignment_id
392 , object_capacity_id
393 , object_version_number
394 )
395 VALUES (
396 csf_r_plan_option_tasks_s1.NEXTVAL
397 , p_plan_option_id
398 , p_task_id
399 , p_scheduled_start_date
400 , p_scheduled_end_date
401 , p_travel_time
402 , p_task_assign_id
403 , p_trip_id
404 , 1
405 );
406
407 -- Standard check of p_commit
408 IF fnd_api.to_boolean(p_commit) THEN
409 COMMIT WORK;
410 END IF;
411 EXCEPTION
412 WHEN fnd_api.g_exc_error THEN
413 ROLLBACK TO createplanoptiontask;
414 x_return_status := fnd_api.g_ret_sts_error;
415 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
416 WHEN fnd_api.g_exc_unexpected_error THEN
417 ROLLBACK TO createplanoptiontask;
418 x_return_status := fnd_api.g_ret_sts_unexp_error;
419 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
420 WHEN OTHERS THEN
421 ROLLBACK TO createplanoptiontask;
422 x_return_status := fnd_api.g_ret_sts_unexp_error;
423
424 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
425 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
426 END IF;
427
428 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
429 END create_plan_option_task;
430
431 PROCEDURE create_message(
432 p_api_version IN NUMBER
433 , p_init_msg_list IN VARCHAR2
434 , p_commit IN VARCHAR2
435 , x_return_status OUT NOCOPY VARCHAR2
436 , x_msg_count OUT NOCOPY NUMBER
437 , x_msg_data OUT NOCOPY VARCHAR2
438 , p_request_id IN NUMBER
439 , p_name IN VARCHAR2
440 , p_type IN VARCHAR2
441 , x_message_id OUT NOCOPY NUMBER
442 ) IS
443 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MESSAGE';
444 l_api_version CONSTANT NUMBER := 1.0;
445
446 CURSOR c_message(p_request_id NUMBER) IS
447 SELECT request_task_id
448 , csf_r_messages_s1.NEXTVAL message_id
449 FROM csf_r_request_tasks
450 WHERE sched_request_id = p_request_id
451 AND ROWNUM = 1; -- there should be one task per request
452
453 l_rec c_message%ROWTYPE;
454 BEGIN
455 -- Standard start of API savepoint
456 SAVEPOINT createmessage;
457
458 -- Standard call to check for call compatibility
459 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
460 RAISE fnd_api.g_exc_unexpected_error;
461 END IF;
462
463 -- Initialize message list if p_init_msg_list is set to TRUE
464 IF fnd_api.to_boolean(p_init_msg_list) THEN
465 fnd_msg_pub.initialize;
466 END IF;
467
468 -- Initialize API return status to success
469 x_return_status := fnd_api.g_ret_sts_success;
470
471 OPEN c_message(p_request_id);
472 FETCH c_message INTO l_rec;
473 IF c_message%NOTFOUND THEN
474 CLOSE c_message;
475 RAISE NO_DATA_FOUND;
476 END IF;
477 CLOSE c_message;
478
479 INSERT INTO csf_r_messages
480 (
481 message_id
482 , request_task_id
483 , name
484 , type
485 , object_version_number
486 )
487 VALUES (
488 l_rec.message_id
489 , l_rec.request_task_id
490 , p_name
491 , p_type
492 , 1
493 );
494
495 x_message_id := l_rec.message_id;
496
497 -- Standard check of p_commit
498 IF fnd_api.to_boolean(p_commit) THEN
499 COMMIT WORK;
500 END IF;
501
502 EXCEPTION
503 WHEN fnd_api.g_exc_error THEN
504 ROLLBACK TO createmessage;
505 x_return_status := fnd_api.g_ret_sts_error;
506 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
507 WHEN fnd_api.g_exc_unexpected_error THEN
508 ROLLBACK TO createmessage;
509 x_return_status := fnd_api.g_ret_sts_unexp_error;
510 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
511 WHEN OTHERS THEN
512 ROLLBACK TO createmessage;
513 x_return_status := fnd_api.g_ret_sts_unexp_error;
514
515 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
516 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
517 END IF;
518
522 PROCEDURE create_message_token(
519 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
520 END create_message;
521
523 p_api_version IN NUMBER
524 , p_init_msg_list IN VARCHAR2
525 , p_commit IN VARCHAR2
526 , x_return_status OUT NOCOPY VARCHAR2
527 , x_msg_count OUT NOCOPY NUMBER
528 , x_msg_data OUT NOCOPY VARCHAR2
529 , p_message_id IN NUMBER
530 , p_name IN VARCHAR2
531 , p_value IN VARCHAR2
532 ) IS
533 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MESSAGE_TOKEN';
534 l_api_version CONSTANT NUMBER := 1.0;
535 BEGIN
536 -- Standard start of API savepoint
537 SAVEPOINT createmessagetoken;
538
539 -- Standard call to check for call compatibility
540 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
541 RAISE fnd_api.g_exc_unexpected_error;
542 END IF;
543
544 -- Initialize message list if p_init_msg_list is set to TRUE
545 IF fnd_api.to_boolean(p_init_msg_list) THEN
546 fnd_msg_pub.initialize;
547 END IF;
548
549 -- Initialize API return status to success
550 x_return_status := fnd_api.g_ret_sts_success;
551
552 INSERT INTO csf_r_message_tokens
553 (
554 message_token_id
555 , message_id
556 , NAME
557 , VALUE
558 , object_version_number
559 )
560 VALUES (
561 csf_r_message_tokens_s1.NEXTVAL
562 , p_message_id
563 , SUBSTR(p_name, 1, 60)
564 , SUBSTR(p_value, 1, 4000)
565 , 1
566 );
567
568 -- Standard check of p_commit
569 IF fnd_api.to_boolean(p_commit) THEN
570 COMMIT WORK;
571 END IF;
572 EXCEPTION
573 WHEN fnd_api.g_exc_error THEN
574 ROLLBACK TO createmessagetoken;
575 x_return_status := fnd_api.g_ret_sts_error;
576 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
577 --
578 WHEN fnd_api.g_exc_unexpected_error THEN
579 ROLLBACK TO createmessagetoken;
580 x_return_status := fnd_api.g_ret_sts_unexp_error;
581 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
582 --
583 WHEN OTHERS THEN
584 ROLLBACK TO createmessagetoken;
585 x_return_status := fnd_api.g_ret_sts_unexp_error;
586
587 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
588 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
589 END IF;
590
591 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
592 END create_message_token;
593
594 PROCEDURE create_plan_options(
595 p_api_version IN NUMBER
596 , p_init_msg_list IN VARCHAR2
597 , p_commit IN VARCHAR2
598 , x_return_status OUT NOCOPY VARCHAR2
599 , x_msg_count OUT NOCOPY NUMBER
600 , x_msg_data OUT NOCOPY VARCHAR2
601 , p_request_task_id IN NUMBER
602 , p_scheduled_start_date_tbl IN jtf_date_table
603 , p_scheduled_end_date_tbl IN jtf_date_table
604 , p_resource_id_tbl IN jtf_number_table
605 , p_resource_type_tbl IN jtf_varchar2_table_100
606 , p_cost_tbl IN jtf_number_table
607 , p_terr_id_tbl IN jtf_number_table
608 , p_win_to_promis_id_tbl IN jtf_number_table
609 , p_spares_cost_tbl IN jtf_number_table
610 , p_spares_date_tbl IN jtf_date_table
611 , x_plan_option_id_tbl OUT NOCOPY jtf_number_table
612 ) IS
613 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PLAN_OPTIONS';
614 l_api_version CONSTANT NUMBER := 1.0;
615
616 CURSOR c_resource(
617 p_request_task_id NUMBER
618 , p_resource_id NUMBER
619 , p_resource_type VARCHAR2
620 , p_spares_date DATE
621 , p_spares_cost NUMBER
622 ) IS
623 SELECT rr.resource_result_id
624 , so.spares_option_id
625 , csf_r_plan_options_s1.NEXTVAL plan_option_id
626 FROM csf_r_resource_results rr, csf_r_spares_options so
627 WHERE rr.request_task_id = p_request_task_id
628 AND rr.resource_id = p_resource_id
629 AND rr.resource_type = p_resource_type
630 AND so.resource_result_id(+) = rr.resource_result_id
631 AND so.availability_date(+) = p_spares_date
632 AND so.COST(+) = p_spares_cost;
633
634 l_resource_rec c_resource%ROWTYPE;
635
636 j PLS_INTEGER;
637 BEGIN
638 -- Standard start of API savepoint
639 SAVEPOINT createplanoptions;
640
641 -- Standard call to check for call compatibility
642 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
643 RAISE fnd_api.g_exc_unexpected_error;
644 END IF;
645
646 -- Initialize message list if p_init_msg_list is set to TRUE
650
647 IF fnd_api.to_boolean(p_init_msg_list) THEN
648 fnd_msg_pub.initialize;
649 END IF;
651 -- Initialize API return status to success
652 x_return_status := fnd_api.g_ret_sts_success;
653 x_plan_option_id_tbl := jtf_number_table();
654 IF p_resource_id_tbl IS NOT NULL AND p_resource_id_tbl.COUNT > 0 THEN
655
656 j := p_resource_id_tbl.FIRST;
657 WHILE j IS NOT NULL LOOP
658 OPEN c_resource(p_request_task_id, p_resource_id_tbl(j), p_resource_type_tbl(j), p_spares_date_tbl(j), p_spares_cost_tbl(j));
659 FETCH c_resource INTO l_resource_rec;
660 IF c_resource%NOTFOUND THEN
661 CLOSE c_resource;
662 RAISE NO_DATA_FOUND;
663 END IF;
664 CLOSE c_resource;
665
666 IF l_resource_rec.spares_option_id IS NULL THEN
667 INSERT INTO csf_r_spares_options
668 (
669 spares_option_id
670 , resource_result_id
674 )
671 , availability_date
672 , COST
673 , object_version_number
675 VALUES (
676 csf_r_spares_options_s1.NEXTVAL
677 , l_resource_rec.resource_result_id
678 , p_spares_date_tbl(j)
679 , p_spares_cost_tbl(j)
680 , 1
681 )
682 RETURNING spares_option_id INTO l_resource_rec.spares_option_id;
683 END IF;
684
685 INSERT INTO csf_r_plan_options
686 (
687 plan_option_id
688 , resource_result_id
689 , spares_option_id
690 , scheduled_start_date
691 , scheduled_end_date
692 , COST
693 , win_to_promis_id
694 , object_version_number
695 )
696 VALUES (
697 l_resource_rec.plan_option_id
698 , l_resource_rec.resource_result_id
699 , l_resource_rec.spares_option_id
700 , p_scheduled_start_date_tbl(j)
701 , p_scheduled_end_date_tbl(j)
702 , p_cost_tbl(j)
703 , p_win_to_promis_id_tbl(j)
704 , 1
705 );
706
707 x_plan_option_id_tbl.extend;
708 x_plan_option_id_tbl(j) := l_resource_rec.plan_option_id;
709 j := p_resource_id_tbl.NEXT(j);
710 END LOOP;
711 END IF;
712 -- Standard check of p_commit
713 IF fnd_api.to_boolean(p_commit) THEN
714 COMMIT WORK;
715 END IF;
716 EXCEPTION
717 WHEN fnd_api.g_exc_error THEN
718 ROLLBACK TO createplanoptions;
719 x_return_status := fnd_api.g_ret_sts_error;
720 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
721 WHEN fnd_api.g_exc_unexpected_error THEN
722 ROLLBACK TO createplanoptions;
723 x_return_status := fnd_api.g_ret_sts_unexp_error;
724 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
725 WHEN OTHERS THEN
726 ROLLBACK TO createplanoptions;
727 x_return_status := fnd_api.g_ret_sts_unexp_error;
728
729 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
730 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
731 END IF;
732 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
733 END create_plan_options;
734
735 PROCEDURE create_multi_trips_request(
736 p_api_version IN NUMBER
737 , p_init_msg_list IN VARCHAR2
738 , p_commit IN VARCHAR2
739 , x_return_status OUT NOCOPY VARCHAR2
740 , x_msg_count OUT NOCOPY NUMBER
741 , x_msg_data OUT NOCOPY VARCHAR2
742 , p_parent_req_name IN VARCHAR2
743 , p_child_req_name IN VARCHAR2
744 , p_trip_tbl IN object_tbl_type
745 , p_resource_tbl IN resource_tbl_type
746 , x_sched_request_id OUT NOCOPY NUMBER
747 ) IS
748 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MULTI_TRIPS_REQUEST';
749 l_api_version CONSTANT NUMBER := 1.0;
750
751 l_index PLS_INTEGER;
752 l_child_request_id NUMBER;
753 BEGIN
754 -- Standard start of API savepoint
755 SAVEPOINT create_opttrips_request;
756
757 -- Standard call to check for call compatibility
758 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
759 RAISE fnd_api.g_exc_unexpected_error;
760 END IF;
761
762 -- Initialize message list if p_init_msg_list is set to TRUE
763 IF fnd_api.to_boolean(p_init_msg_list) THEN
764 fnd_msg_pub.initialize;
765 END IF;
766
767 -- Initialize API return status to success
768 x_return_status := fnd_api.g_ret_sts_success;
769
770 -- Create the Parent Request
771 create_scheduler_request(
772 p_api_version => 1
773 , x_return_status => x_return_status
774 , x_msg_data => x_msg_data
775 , x_msg_count => x_msg_count
776 , p_name => p_parent_req_name
777 , p_object_id => -1
778 , x_request_id => x_sched_request_id
779 );
780 IF x_return_status = fnd_api.g_ret_sts_error THEN
781 RAISE fnd_api.g_exc_error;
782 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
783 RAISE fnd_api.g_exc_unexpected_error;
784 END IF;
785
786 /**
787 * Create the Child Requests.
788 *
789 * In case the Trips Table is provided (P_TRIP_TBL), then the caller
790 * wants the Child Request action to be undertaken individually on
791 * each trip. Thus there will be as many child requests created
792 * as the number of trips in the P_TRIP_TBL.
793 *
794 * In case the Resource Table is provided (P_RESOURCE_TBL), then
795 * the caller wants the Child Request action to be undertaken as
796 * a whole on all the resources. Thus there will be a single
797 * request created with all the resource information.
798 *
799 * This can be generalized in the future by adding another
800 * parameter P_INDIVIDUAL_REQUESTS (BOOLEAN) so that the caller
801 * can have more control on how the requests are created rather
802 * than the API deciding based on the parameters.
803 */
804 IF p_trip_tbl IS NOT NULL AND p_trip_tbl.COUNT > 0 THEN
805 -- In case Trips Table is provided,
806 l_index := p_trip_tbl.FIRST;
807 WHILE l_index IS NOT NULL LOOP
808 create_scheduler_request(
809 p_api_version => 1
810 , x_return_status => x_return_status
811 , x_msg_data => x_msg_data
812 , x_msg_count => x_msg_count
813 , p_name => p_child_req_name
814 , p_object_id => p_trip_tbl(l_index)
815 , p_parent_id => x_sched_request_id
816 , x_request_id => l_child_request_id
817 );
818 l_index := p_trip_tbl.next(l_index);
819 END LOOP;
820 ELSIF p_resource_tbl IS NOT NULL AND p_resource_tbl.COUNT > 0 THEN
821 create_scheduler_request(
822 p_api_version => 1
823 , x_return_status => x_return_status
824 , x_msg_data => x_msg_data
825 , x_msg_count => x_msg_count
826 , p_name => p_child_req_name
827 , p_object_id => -1
828 , p_resource_tbl => p_resource_tbl
829 , p_parent_id => x_sched_request_id
830 , x_request_id => l_child_request_id
831 );
832 END IF;
833
834 -- Standard check of p_commit
835 IF fnd_api.to_boolean(p_commit) THEN
836 COMMIT WORK;
837 END IF;
838 EXCEPTION
839 WHEN fnd_api.g_exc_error THEN
840 ROLLBACK TO create_opttrips_request;
841 x_return_status := fnd_api.g_ret_sts_error;
842 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
843 WHEN fnd_api.g_exc_unexpected_error THEN
844 ROLLBACK TO create_opttrips_request;
845 x_return_status := fnd_api.g_ret_sts_unexp_error;
846 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
847 WHEN OTHERS THEN
848 ROLLBACK TO create_opttrips_request;
849 x_return_status := fnd_api.g_ret_sts_unexp_error;
850
851 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
852 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
853 END IF;
854 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
855 END create_multi_trips_request;
856 END csf_requests_pvt;
[Home] [Help]