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