[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATIONS_INTERFACE
Source
1 PACKAGE BODY INV_RESERVATIONS_INTERFACE as
2 /* $Header: INVRSV5B.pls 120.2 2006/10/06 06:35:50 bradha noship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name constant varchar2(50) := 'INV_RESERVATIONS_INTERFACE';
6
7 PROCEDURE debug (p_message IN VARCHAR2,p_module_name IN VARCHAR2,p_level IN NUMBER) IS
8 BEGIN
9 inv_log_util.TRACE (p_message,g_pkg_name||'.'||p_module_name, p_level);
10 END debug;
11
12 /*
13 ** ===========================================================================
14 ** Procedure:
15 ** rsv_interface_manager
16 **
17 ** Description:
18 ** rsv interface manager processes reservations requests in
19 ** background.
20 ** Applications in need of reservations processing such as
21 ** Create Reservations, Update Reservations, Delete Reservations and
22 ** Transfer Reservations can write their specific requests with details such
23 ** as item, organization, demand, supply, inventory controls and quantity
24 ** information into MTL_RESERVATIONS_INTERFACE table.
25 ** rsv interface manager thru another program, rsv
26 ** batch processor, processes records from MTL_RESERVATIONS_INTERFACE table
27 ** into MTL_RESERVATIONS table, one or more reservation batch id(s) at a time.
28 ** A reservation batch id consists of one or more reservations processing
29 ** requests in MTL_RESERVATIONS_INTERFACE table. Processing includes data
30 ** validation, executions of appropriate reservation APIs, thereby writing
31 ** into MTL_RESERVATIONS table and finally deleting successfuly processed
32 ** records from MTL_RESERVATIONS_INTERFACE table.
33 **
34 ** Input Parameters:
35 ** p_api_version_number
36 ** parameter to compare API version
37 ** p_init_msg_lst
38 ** flag indicating if message list should be initialized
39 ** p_form_mode
40 ** 'Y','y' - called from form
41 ** 'N','n' - not called from form
42 **
43 ** Output Parameters:
44 ** x_errbuf
45 ** mandatory concurrent program parameter
46 ** x_retcode
47 ** mandatory concurrent program parameter
48 **
49 ** Tables Used:
50 ** MTL_RESERVATIONS_INTERFACE for Read and Update.
51 **
52 ** Current Version 1.0
53 ** Initial Version 1.0
54 ** ===========================================================================
55 */
56
57 PROCEDURE rsv_interface_manager(
58 x_errbuf OUT NOCOPY VARCHAR2
59 , x_retcode OUT NOCOPY NUMBER
60 , p_api_version_number IN NUMBER DEFAULT 1.0
61 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
62 , p_form_mode IN VARCHAR2 DEFAULT 'N') as
63
64 -- Constants
65 c_max_numof_lines constant number := 3;
66 c_max_string_size constant number := 32000;
67 c_delimiter constant varchar2(1) := ':';
68 c_api_name constant varchar2(30):= 'rsv_interface_manager';
69 c_api_version_number constant number := 1.0;
70
71 -- Variables
72 l_curr_batch_id number;
73 l_curr_numof_lines number := 0;
74 l_numof_lines number := 0;
75 l_batch_array_arg varchar2(32000) := NULL;
76 l_batch_arg varchar2(10) := NULL;
77 l_conc_status boolean;
78
79 l_return_status varchar2(1);
80 l_msg_count number;
81 l_msg_data varchar2(1000);
82
83 -- Cursor
84 cursor mric is
85 select
86 reservation_batch_id
87 , count(*) total_num
88 from mtl_reservations_interface mri1
89 where mri1.transaction_mode = 3 /* Background */
90 and mri1.row_status_code = 1 /* Active */
91 and mri1.error_code is null /* No errors */
92 and mri1.error_explanation is null /* No errors */
93 and mri1.lock_flag = 2 /* No */
94 and not exists(
95 select 1 from mtl_reservations_interface mri2
96 where mri1.reservation_batch_id = mri2.reservation_batch_id
97 and mri2.transaction_mode = 3
98 and (mri2.row_status_code <> 1 or
99 mri2.error_code is not null or
100 mri2.error_explanation is not null or
101 mri2.lock_flag = 1)
102 )
103 group by reservation_batch_id;
104
105 -- Cursor Rowtype
106 mric_row mric%rowtype;
107 begin
108
109 /*
110 ** Standard call to check for call compatibility
111 */
112 if not fnd_api.compatible_api_call(
113 c_api_version_number
114 , p_api_version_number
115 , c_api_name
116 , g_pkg_name) then
117 raise fnd_api.g_exc_unexpected_error;
118 end if;
119
120 /*
121 ** Initialize message list
122 */
123 if fnd_api.to_boolean(p_init_msg_lst) then
124 fnd_msg_pub.initialize;
125 end if;
126
127 /*
128 ** Initialize return status to success
129 */
130 l_return_status := fnd_api.g_ret_sts_success;
131
132 if (p_form_mode in ('N','n')) then
133 fnd_message.set_name('INV', 'INV_RSV_MANAGER');
134 fnd_file.put_line(fnd_file.log,fnd_message.get);
135 fnd_file.put_line(fnd_file.log,' ');
136 end if;
137
138 /*
139 ** ========================================================================
140 ** To achieve efficiency, the batch processor is designed to process 1 or
141 ** more batches at a time.
142 ** Remember, a batch is composed of 1 or more lines. The maximum number
143 ** of lines a batch processor can process is pre-set in this program.
144 **
145 ** Loop thru cursor, fetching a batch id and its number of lines, each time.
146 ** Group batches together until the maximum number of lines limit is reached
147 ** or there are no more batches. Call the batch processor, passing the
148 ** grouped batches. Repeat this process while you are still in the loop.
149 ** ========================================================================
150 */
151
152 for mric_row in mric
153 loop
154 l_curr_batch_id := mric_row.reservation_batch_id;
155 l_curr_numof_lines := mric_row.total_num;
156
157 if (l_curr_numof_lines > c_max_numof_lines) then
158 l_batch_arg := to_char(l_curr_batch_id) || c_delimiter;
159
160 -- dbms_output.put_line(l_batch_arg);
161
162 if (p_form_mode in ('N','n')) then
163 -- kgm_msg
164 fnd_message.set_name('INV', 'INV_RSV_BATCHES');
165 fnd_message.set_token('BATCHES',l_batch_arg);
166 fnd_message.set_token('LINES',to_char(l_curr_numof_lines));
167 fnd_file.put_line(fnd_file.log,fnd_message.get);
168 fnd_file.put_line(fnd_file.log,' ');
169
170 /*
171 fnd_file.put(fnd_file.log,'Reservation Batches submitted - ');
172 fnd_file.put_line(fnd_file.log,l_batch_arg);
173
174 fnd_file.put(fnd_file.log,'Total number of request lines - ');
175 fnd_file.put_line(fnd_file.log,to_char(l_curr_numof_lines));
176 fnd_file.put_line(fnd_file.log,' ');
177 */
178 end if;
179
180 -- Submit request
181 -- rsv_interface_batch_processor
182 -- (
183 -- l_batch_arg
184 -- , 3 /* Background */
185 -- , 2 /* No Partial processing */
186 -- , 'Y' /* Commit */
187 -- )
188 rsv_interface_batch_processor(
189 p_api_version_number => 1.0
190 , p_init_msg_lst => fnd_api.g_false
191 , p_reservation_batches => l_batch_arg
192 , p_process_mode => 3 /* Background */
193 , p_partial_batch_process_flag => 2 /* No Partial Processing */
194 , p_commit_flag => 'Y'/* Commit */
195 , p_form_mode => 'N'/* Not from form */
196 , x_return_status => l_return_status
197 , x_msg_count => l_msg_count
198 , x_msg_data => l_msg_data);
199
200 elsif (((l_curr_numof_lines + l_numof_lines) > c_max_numof_lines) or
201 (length(l_batch_array_arg ||
202 to_char(l_curr_batch_id) ||
203 c_delimiter) > c_max_string_size)) then
204
205 -- dbms_output.put_line(l_batch_array_arg);
206
207 if (p_form_mode in ('N','n')) then
208 -- kgm_msg
209 fnd_message.set_name('INV', 'INV_RSV_BATCHES');
210 fnd_message.set_token('BATCHES',l_batch_array_arg);
211 fnd_message.set_token('LINES',to_char(l_numof_lines));
212 fnd_file.put_line(fnd_file.log,fnd_message.get);
213 fnd_file.put_line(fnd_file.log,' ');
214
215 /*
216 fnd_file.put(fnd_file.log,'Reservation Batches submitted - ');
217 fnd_file.put_line(fnd_file.log,l_batch_array_arg);
218
219 fnd_file.put(fnd_file.log,'Total number of request lines - ');
220 fnd_file.put_line(fnd_file.log,to_char(l_numof_lines));
221 fnd_file.put_line(fnd_file.log,' ');
222 */
223 end if;
224
225 -- Submit request
226 -- rsv_interface_batch_processor
227 -- (
228 -- l_batch_array_arg
229 -- , 3 /* Background */
230 -- , 2 /* No Partial processing */
231 -- , 'Y' /* Commit */
232 -- )
233
234 rsv_interface_batch_processor(
235 p_api_version_number => 1.0
236 , p_init_msg_lst => fnd_api.g_false
237 , p_reservation_batches => l_batch_array_arg
238 , p_process_mode => 3 /* Background */
239 , p_partial_batch_process_flag => 2 /* No Partial Processing */
240 , p_commit_flag => 'Y'/* Commit */
241 , p_form_mode => 'N'/* Not from form */
242 , x_return_status => l_return_status
243 , x_msg_count => l_msg_count
244 , x_msg_data => l_msg_data);
245
246 -- Reset
247 l_batch_array_arg := NULL;
248 l_numof_lines := 0;
249
250 l_batch_array_arg := to_char(l_curr_batch_id) ||
251 c_delimiter;
252
253 l_numof_lines := l_curr_numof_lines;
254
255 else
256 l_batch_array_arg := l_batch_array_arg ||
257 to_char(l_curr_batch_id) ||
258 c_delimiter;
259
260 l_numof_lines := l_numof_lines + l_curr_numof_lines;
261
262 end if;
263 end loop;
264
265 /*
266 ** If needed, call for the last time.
267 */
268 if (l_batch_array_arg is not null) then
269
270 -- dbms_output.put_line(l_batch_array_arg);
271
272 if (p_form_mode in ('N', 'n')) then
273 -- kgm_msg
274 fnd_message.set_name('INV', 'INV_RSV_BATCHES');
275 fnd_message.set_token('BATCHES',l_batch_array_arg);
276 fnd_message.set_token('LINES',to_char(l_numof_lines));
277 fnd_file.put_line(fnd_file.log,fnd_message.get);
278 fnd_file.put_line(fnd_file.log, ' ');
279
280 /*
281 fnd_file.put(fnd_file.log,'Reservation Batches submitted - ');
282 fnd_file.put_line(fnd_file.log,l_batch_array_arg);
283
284 fnd_file.put(fnd_file.log,'Total number of request lines - ');
285 fnd_file.put_line(fnd_file.log,to_char(l_numof_lines));
286 fnd_file.put_line(fnd_file.log, ' ');
287 */
288 end if;
289
290 -- Submit request
291 -- rsv_interface_batch_processor
292 -- (
293 -- l_batch_array_arg
294 -- , 3 /* Background */
295 -- , 2 /* No Partial processing */
296 -- , 'Y' /* Commit */
297 -- )
298
299 rsv_interface_batch_processor(
300 p_api_version_number => 1.0
301 , p_init_msg_lst => fnd_api.g_false
302 , p_reservation_batches => l_batch_array_arg
303 , p_process_mode => 3 /* Background */
304 , p_partial_batch_process_flag => 2 /* No Partial Processing */
305 , p_commit_flag => 'Y'/* Commit */
306 , p_form_mode => 'N'/* Not from form */
307 , x_return_status => l_return_status
308 , x_msg_count => l_msg_count
309 , x_msg_data => l_msg_data);
310 end if;
311
312 l_conc_status := fnd_concurrent.set_completion_status('NORMAL','NORMAL');
313
314 exception
315 when fnd_api.g_exc_error then
316 l_return_status := fnd_api.g_ret_sts_error;
317
318 fnd_msg_pub.count_and_get(
319 p_count => l_msg_count
320 , p_data => l_msg_data
321 , p_encoded => 'F');
322
323 if (p_form_mode in ('N','n')) then
324 print_error(l_msg_count);
325 end if;
326
327 l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
328
329 when fnd_api.g_exc_unexpected_error then
330 l_return_status := fnd_api.g_ret_sts_unexp_error;
331
332 fnd_msg_pub.count_and_get(
333 p_count => l_msg_count
334 , p_data => l_msg_data
335 , p_encoded => 'F');
336
337 if (p_form_mode in ('N','n')) then
338 print_error(l_msg_count);
339 end if;
340
341 l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
342
343 when others then
344 l_return_status := fnd_api.g_ret_sts_unexp_error;
345
346 if (fnd_msg_pub.check_msg_level
347 (fnd_msg_pub.g_msg_lvl_unexp_error))then
348 fnd_msg_pub.add_exc_msg(g_pkg_name,c_api_name);
349 end if;
350
351 fnd_msg_pub.count_and_get(
352 p_count => l_msg_count
353 , p_data => l_msg_data
354 , p_encoded => 'F');
355
356 if (p_form_mode in ('N','n')) then
357 print_error(l_msg_count);
358 end if;
359
360 l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
361
362 end rsv_interface_manager;
363
364 /*
365 ** ===========================================================================
366 ** Procedure:
367 ** rsv_interface_batch_processor
368 **
369 ** Description:
370 ** Applications in need of reservations processing such as
371 ** Create Reservations, Update Reservations, Delete Reservations and
372 ** Transfer Reservations can write their specific requests with details such
373 ** as item, organization, demand, supply, inventory controls and quantity
374 ** information into MTL_RESERVATIONS_INTERFACE table.
375 ** rsv interface batch processor, processes records from
376 ** MTL_RESERVATIONS_INTERFACE table into MTL_RESERVATIONS table, one or more
377 ** reservation batch id(s) at a time. A reservation batch id consists of one
378 ** or more reservations processing requests in MTL_RESERVATIONS_INTERFACE table.
379 ** A reservations request in MTL_RESERVATIONS_INTERFACE table is uniquely
380 ** determined by a reservations interface id.
381 ** rsv interface batch processor in turn calls another program,
382 ** rsv interface line processor repetitively, passing each time a
383 ** reservations interafce id under the current reservations batch id.
384 ** reservations interface line processor performs the actual reservations
385 ** processing.
386 ** rsv interface batch processor deletes successfully processed
387 ** rows from MTL_RESERVATIONS_INTERFACE table.
388 **
389 ** Input Parameters:
390 ** p_api_version_number
391 ** parameter to compare API version
392 ** p_init_msg_lst
393 ** flag indicating if message list should be initialized
394 ** p_reservation_batches
395 ** reservation batch ids stringed together and separated by
396 ** delimiter.Eg: 163:716:987:
397 ** p_process_mode
398 ** 1 = Online 2 = Concurrent 3 = Background
399 ** p_partial_batch_processing_flag
400 ** 1 - If a line in reservation batch fails, continue
401 ** 2 - If a line in reservation batch fails, exit
402 ** p_commit_flag
403 ** 'Y','y' - Commit
404 ** not('Y','y') - Do not commit
405 ** p_form_mode
406 ** 'Y','y' - called from form
407 ** 'N','n' - not called from form
408 **
409 ** Output Parameters:
410 ** x_return_status
411 ** return status indicating success, error, unexpected error
412 ** x_msg_count
413 ** number of messages in message list
414 ** x_msg_data
415 ** if the number of messages in message list is 1, contains
416 ** message text
417 **
418 ** Tables Used:
419 ** MTL_RESERVATIONS_INTERFACE for Read, Update and Delete.
420 **
421 ** Current Version 1.0
422 ** Initial Version 1.0
423 ** ===========================================================================
424 */
425
426 PROCEDURE rsv_interface_batch_processor (
427 p_api_version_number IN NUMBER
428 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
429 , p_reservation_batches IN VARCHAR2
430 , p_process_mode IN NUMBER DEFAULT 1
431 , p_partial_batch_process_flag IN NUMBER DEFAULT 1
432 , p_commit_flag IN VARCHAR2 DEFAULT 'Y'
433 , p_form_mode IN VARCHAR2 DEFAULT 'N'
434 , x_return_status OUT NOCOPY VARCHAR2
435 , x_msg_count OUT NOCOPY NUMBER
436 , x_msg_data OUT NOCOPY VARCHAR2) as
437
438 -- Constants
439 c_delimiter constant varchar2(1) := ':';
440 c_api_name constant varchar2(30):= 'rsv_interface_batch_processor';
441 c_api_version_number constant number := 1.0;
442
443 -- Variables
444 l_position number;
445 l_batch_id number;
446 l_interface_id number;
447 l_return_status varchar2(1);
448 l_error_code number;
449 l_error_text varchar2(3000); -- Bug 5529609
450 l_delete_rows boolean;
451 l_reservation_batches varchar2(32000);
452 l_conc_status boolean;
453
454 -- Cursor
455 cursor mric(batch_id number,
456 process_mode number) is
457 select reservation_interface_id
458 from mtl_reservations_interface mri
459 where mri.reservation_batch_id = batch_id
460 and mri.row_status_code = 1 /* Active */
461 and mri.transaction_mode = process_mode
462 and mri.error_code is null
463 and mri.error_explanation is null
464 and mri.lock_flag = 1 /* Yes */;
465
466 -- Cursor Rowtype
467 mric_row mric%rowtype;
468 begin
469 /*
470 ** Standard call to check for call compatibility
471 */
472 if not fnd_api.compatible_api_call(
473 c_api_version_number
474 , p_api_version_number
475 , c_api_name
476 , g_pkg_name) then
477 raise fnd_api.g_exc_unexpected_error;
478 end if;
479
480 /*
481 ** Initialize message list
482 */
483 if fnd_api.to_boolean(p_init_msg_lst) then
484 fnd_msg_pub.initialize;
485 end if;
486
487 /*
488 ** Initialize return status to success
489 */
490 x_return_status := fnd_api.g_ret_sts_success;
491
492 l_reservation_batches := rtrim(ltrim(p_reservation_batches));
493
494 while (0=0) loop
495 /*
496 ** Following lines of logic unstrings batch ids from stringed batch ids
497 */
498 l_position := instr(nvl(l_reservation_batches,'A'),c_delimiter);
499
500 if (l_position = 0)then
501 -- Out of loop; No more batches to process;
502 exit;
503 end if;
504
505 l_batch_id := to_number(substr(l_reservation_batches, 1, l_position - 1));
506
507 if (l_position = length(l_reservation_batches)) then
508 -- for last batch
509 l_reservation_batches := NULL;
510 else
511 l_reservation_batches := substr(l_reservation_batches,l_position + 1);
512 end if;
513
514 -- Record this point
515 savepoint alpha;
516
517 -- Lock requests for reservation batch id
518 update mtl_reservations_interface
519 set lock_flag = 1 /* Yes */
520 where reservation_batch_id = l_batch_id
521 and row_status_code = 1 /* Active */
522 and lock_flag = 2 /* No */
523 and transaction_mode = p_process_mode
524 and error_code is null
525 and error_explanation is null;
526
527 if (SQL%ROWCOUNT <> 0) then
528 l_delete_rows := true;
529
530 for mric_row in mric(l_batch_id, p_process_mode) loop
531
532 -- Initialize
533 l_interface_id := mric_row.reservation_interface_id;
534 l_return_status := fnd_api.g_ret_sts_success;
535 l_error_code := null;
536 l_error_text := null;
537
538 if (p_form_mode in ('N','n')) then
539 -- kgm_msg
540 fnd_message.set_name('INV', 'INV_RSV_BATCH_INTERFACE');
541 fnd_message.set_token('BATCH_ID',to_char(l_batch_id));
542 fnd_message.set_token('INTERFACE_ID',to_char(l_interface_id));
543 fnd_file.put_line(fnd_file.log,fnd_message.get);
544 fnd_file.put_line(fnd_file.log,' ');
545
546 /*
547 fnd_file.put(fnd_file.log,'Reservation Batch - ');
548 fnd_file.put(fnd_file.log, to_char(l_batch_id));
549 fnd_file.put(fnd_file.log,', Reservation Interface - ');
550 fnd_file.put(fnd_file.log, to_char(l_interface_id));
551 fnd_file.put_line(fnd_file.log, ' submitted ');
552 fnd_file.put_line(fnd_file.log,' ');
553 */
554 end if;
555
556 -- Call rsv_interface_line_processor
557 rsv_interface_line_processor (
558 p_api_version_number => 1.0
559 , p_init_msg_lst => fnd_api.g_false
560 , p_reservation_interface_id => l_interface_id
561 , p_form_mode => p_form_mode
562 , x_error_code => l_error_code
563 , x_error_text => l_error_text
564 , x_return_status => l_return_status
565 , x_msg_count => x_msg_count
566 , x_msg_data => x_msg_data);
567
568 /*
569 ** If partial batch processing flag has a value of 2, then all
570 ** requests of a batch have to be successfully processed. Even if
571 ** one fails, processing shouldn't continue for the others. However,
572 ** for a value of 1, processing should continue for others when one
573 ** fails.
574 */
575
576 if ((l_return_status <> fnd_api.g_ret_sts_success or
577 l_error_text is not null or
578 l_error_code is not null) and
579 (p_partial_batch_process_flag = 2))then
580
581 -- Rollback
582 rollback to savepoint alpha;
583
584 -- Stamp error. rollback should have unlocked rows.
585 update mtl_reservations_interface
586 set
587 row_status_code = 3 /* Error */
588 , error_code = l_error_code
589 , error_explanation = substrb(l_error_text,1,240) -- Bug 5529609
590 where reservation_interface_id = l_interface_id;
591
592 l_delete_rows := false;
593
594 exit;
595 end if;
596
597 if ((l_return_status <> fnd_api.g_ret_sts_success or
598 l_error_text is not null or
599 l_error_code is not null) and
600 (p_partial_batch_process_flag = 1))then
601
602 -- Stamp error. Unlock rows.
603 update mtl_reservations_interface
604 set
605 row_status_code = 3 /* Error */
606 , lock_flag = 2 /* No */
607 , error_code = l_error_code
608 , error_explanation = substrb(l_error_text,1,240) -- Bug 5529609
609 where reservation_interface_id = l_interface_id;
610 end if;
611
612 end loop;
613
614 -- Delete processed rows
615 if (l_delete_rows = true) then
616 delete mtl_reservations_interface
617 where reservation_batch_id = l_batch_id
618 and transaction_mode = p_process_mode
619 and row_status_code = 2 /* Completed */
620 and error_code is null
621 and error_explanation is null;
622 end if;
623
624 -- Commit only if explicitly told to do so
625 if (p_commit_flag in ('Y', 'y')) then
626 commit;
627 end if;
628
629 end if; /* (SQL%ROWCOUNT <> 0) */
630 end loop;
631
632 -- l_conc_status := fnd_concurrent.set_completion_status('NORMAL','NORMAL');
633
634 exception
635 when fnd_api.g_exc_error then
636 x_return_status := fnd_api.g_ret_sts_error;
637
638 fnd_msg_pub.count_and_get(
639 p_count => x_msg_count
640 , p_data => x_msg_data
641 , p_encoded => 'F');
642
643 if (p_form_mode in ('N','n')) then
644 print_error(x_msg_count);
645 end if;
646
647 --l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
648
649 when fnd_api.g_exc_unexpected_error then
650 x_return_status := fnd_api.g_ret_sts_unexp_error;
651
652 fnd_msg_pub.count_and_get(
653 p_count => x_msg_count
654 , p_data => x_msg_data
655 , p_encoded => 'F');
656
657 if (p_form_mode in ('N','n')) then
658 print_error(x_msg_count);
659 end if;
660
661 --l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
662
663 when others then
664 x_return_status := fnd_api.g_ret_sts_unexp_error;
665
666 if (fnd_msg_pub.check_msg_level
667 (fnd_msg_pub.g_msg_lvl_unexp_error))then
668 fnd_msg_pub.add_exc_msg(g_pkg_name,c_api_name);
669 end if;
670
671 fnd_msg_pub.count_and_get(
672 p_count => x_msg_count
673 , p_data => x_msg_data
674 , p_encoded => 'F');
675
676 if (p_form_mode in ('N','n')) then
677 print_error(x_msg_count);
678 end if;
679
680 --l_conc_status := fnd_concurrent.set_completion_status('ERROR','ERROR');
681 end rsv_interface_batch_processor;
682
683 /*
684 ** ===========================================================================
685 ** Procedure:
686 ** rsv_interface_line_processor
687 **
688 ** Description:
689 ** Applications in need of reservations processing such as
690 ** Create Reservations, Update Reservations, Delete Reservations and
691 ** Transfer Reservations can write their specific requests with details such
692 ** as item, organization, demand, supply, inventory controls and quantity
693 ** information into MTL_RESERVATIONS_INTERFACE table.
694 ** rsv interface line processor processes the reservations
695 ** request line in MTL_RESERVATIONS_INTERFACE, pointed by a given
696 ** reservations interface id. Processing includes data validation and
697 ** performing the requested reservation function by executing the appropriate
698 ** reservations API.
699 **
700 ** Input Parameters:
701 ** p_api_version_number
702 ** parameter to compare API version
703 ** p_init_msg_lst
704 ** flag indicating if message list should be initialized
705 ** p_reservation interface id
706 ** identifies reservations request line in
707 ** MTL_RESERVATIONS_INTERFACE table.
708 ** p_form_mode
709 ** 'Y','y' - called from form
710 ** 'N','n' - not called from form
711 **
712 ** Output Parameters:
713 ** x_error_code
714 ** error code
715 ** x_error_text
716 ** error explanation text
717 ** x_return_status
718 ** return status indicating success, error, unexpected error
719 ** x_msg_count
720 ** number of messages in message list
721 ** x_msg_data
722 ** if the number of messages in message list is 1, contains
723 ** message text
724 **
725 ** Tables Used:
726 ** MTL_RESERVATIONS_INTERFACE for Read and Update.
727 **
728 ** Current Version 1.0
729 ** Initial Version 1.0
730 ** ===========================================================================
731 */
732
733 PROCEDURE rsv_interface_line_processor (
734 p_api_version_number IN NUMBER
735 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
736 , p_reservation_interface_id IN NUMBER
737 , p_form_mode IN VARCHAR2 DEFAULT 'N'
738 , x_error_code OUT NOCOPY NUMBER
739 , x_error_text OUT NOCOPY VARCHAR2
740 , x_return_status OUT NOCOPY VARCHAR2
741 , x_msg_count OUT NOCOPY NUMBER
742 , x_msg_data OUT NOCOPY VARCHAR2) as
743
744 -- Constants
745 c_api_name constant varchar2(30):= 'rsv_interface_line_processor';
746 c_api_version_number constant number := 1.0;
747
748 -- Variables
749
750 /*
751 ** Structures that will be loaded and passed to reservation APIs
752 */
753 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
754 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
755
756 /*
757 ** Variables to hold fetched data from mtl_reservations_interface
758 */
759 l_requirement_date date;
760 l_organization_id number;
761 l_inventory_item_id number;
762 l_demand_source_type_id number;
763 l_demand_source_name varchar2(30);
764 l_demand_source_header_id number;
765 l_demand_source_line_id number;
766 l_primary_uom_code varchar2(3);
767 l_primary_uom_id number;
768 l_secondary_uom_code varchar2(3); -- INVCONV
769 l_secondary_uom_id number; -- INVCONV
770 l_reservation_uom_code varchar2(3);
771 l_reservation_uom_id number;
772 l_reservation_quantity number;
773 l_primary_rsv_quantity number;
774 l_secondary_rsv_quantity number; -- INVCONV
775 l_supply_source_type_id number;
776 l_supply_source_name varchar2(30);
777 l_supply_source_header_id number;
778 l_supply_source_line_id number;
779 l_supply_source_line_detail number;
780 l_revision varchar2(3);
781 l_subinventory_code varchar2(10);
782 l_subinventory_id number;
783 l_locator_id number;
784 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
785 l_lot_number varchar2(80);
786 l_lot_number_id number;
787 l_lpn_id number;
788
789 l_to_organization_id number;
790 l_to_demand_source_type_id number;
791 l_to_demand_source_name varchar2(30);
792 l_to_demand_source_header_id number;
793 l_to_demand_source_line_id number;
794 l_to_supply_source_type_id number;
795 l_to_supply_source_name varchar2(30);
796 l_to_supply_source_header_id number;
797 l_to_supply_source_line_id number;
798 l_to_supply_source_line_detail number;
799 l_to_revision varchar2(3);
800 l_to_subinventory_code varchar2(10);
801 l_to_subinventory_id number;
802 l_to_locator_id number;
803 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
804 l_to_lot_number varchar2(80);
805 l_to_lot_number_id number;
806 l_to_lpn_id number;
807
808 l_reservation_action_code number;
809 l_validation_flag number;
810 l_partial_quantities_allowed number;
811 l_ship_ready_flag number;
812
813 /*
814 ** Return status of reservation APIs
815 */
816 l_return_status varchar2(1);
817
818 /*
819 ** Other output parameters of reservation APIs
820 */
821 l_serial_number inv_reservation_global.serial_number_tbl_type;
822 l_partial_reservation_flag varchar2(1);
823 l_quantity_reserved number;
824 l_secondary_quantity_reserved number;
825 l_quantity_transferred number;
826 l_reservation_id number;
827 l_attribute_category varchar2(30);
828 l_attribute1 varchar2(150);
829 l_attribute2 varchar2(150);
830 l_attribute3 varchar2(150);
831 l_attribute4 varchar2(150);
832 l_attribute5 varchar2(150);
833 l_attribute6 varchar2(150);
834 l_attribute7 varchar2(150);
835 l_attribute8 varchar2(150);
836 l_attribute9 varchar2(150);
837 l_attribute10 varchar2(150);
838 l_attribute11 varchar2(150);
839 l_attribute12 varchar2(150);
840 l_attribute13 varchar2(150);
841 l_attribute14 varchar2(150);
842 l_attribute15 varchar2(150);
843 l_oe_line_subinventory varchar2(10); --Bug 3357096
844 l_tmp_quantity number := NULL; --Bug 3384601
845
846 -- Exception
847 INVALID_ACTION_CODE exception;
848
849 begin
850 /*
851 ** Standard call to check for call compatibility
852 */
853 if not fnd_api.compatible_api_call(
854 c_api_version_number
855 , p_api_version_number
856 , c_api_name
857 , g_pkg_name) then
858 raise fnd_api.g_exc_unexpected_error;
859 end if;
860
861 /*
862 ** Initialize message list
863 */
864 if fnd_api.to_boolean(p_init_msg_lst) then
865 fnd_msg_pub.initialize;
866 end if;
867
868 /*
869 ** Initialize return status to success
870 */
871 x_return_status := fnd_api.g_ret_sts_success;
872 x_error_code := NULL;
873 x_error_text := NULL;
874
875 /*
876 ** Fetch record from mtl_reservations_interface for the given reservation
877 ** interface id.
878 */
879 -- INVCONV - Incorporate secondary columns into select
880 select
881 requirement_date
882 , organization_id
883 , inventory_item_id
884 , demand_source_type_id
885 , demand_source_name
886 , demand_source_header_id
887 , demand_source_line_id
888 , primary_uom_code
889 , primary_uom_id
890 , secondary_uom_code
891 , secondary_uom_id
892 , reservation_uom_code
893 , reservation_uom_id
894 , reservation_quantity
895 , primary_reservation_quantity
896 , secondary_reservation_quantity
897 , supply_source_type_id
898 , supply_source_name
899 , supply_source_header_id
900 , supply_source_line_id
901 , supply_source_line_detail
902 , revision
903 , subinventory_code
904 , subinventory_id
905 , locator_id
906 , lot_number
907 , lot_number_id
908 , to_organization_id
909 , to_demand_source_type_id
910 , to_demand_source_name
911 , to_demand_source_header_id
912 , to_demand_source_line_id
913 , to_supply_source_type_id
914 , to_supply_source_name
915 , to_supply_source_header_id
916 , to_supply_source_line_id
917 , to_supply_source_line_detail
918 , to_revision
919 , to_subinventory_code
920 , to_subinventory_id
921 , to_locator_id
922 , to_lot_number
923 , to_lot_number_id
924 , reservation_action_code
925 , validation_flag
926 , partial_quantities_allowed
927 , ship_ready_flag
928 , lpn_id
929 , to_lpn_id
930 , attribute_category
931 , attribute1
932 , attribute2
933 , attribute3
934 , attribute4
935 , attribute5
936 , attribute6
937 , attribute7
938 , attribute8
939 , attribute9
940 , attribute10
941 , attribute11
942 , attribute12
943 , attribute13
944 , attribute14
945 , attribute15
946 into
947 l_requirement_date
948 , l_organization_id
949 , l_inventory_item_id
950 , l_demand_source_type_id
951 , l_demand_source_name
952 , l_demand_source_header_id
953 , l_demand_source_line_id
954 , l_primary_uom_code
955 , l_primary_uom_id
956 , l_secondary_uom_code
957 , l_secondary_uom_id
958 , l_reservation_uom_code
959 , l_reservation_uom_id
960 , l_reservation_quantity
961 , l_primary_rsv_quantity
962 , l_secondary_rsv_quantity
963 , l_supply_source_type_id
964 , l_supply_source_name
965 , l_supply_source_header_id
966 , l_supply_source_line_id
967 , l_supply_source_line_detail
968 , l_revision
969 , l_subinventory_code
970 , l_subinventory_id
971 , l_locator_id
972 , l_lot_number
973 , l_lot_number_id
974 , l_to_organization_id
975 , l_to_demand_source_type_id
976 , l_to_demand_source_name
977 , l_to_demand_source_header_id
978 , l_to_demand_source_line_id
979 , l_to_supply_source_type_id
980 , l_to_supply_source_name
981 , l_to_supply_source_header_id
982 , l_to_supply_source_line_id
983 , l_to_supply_source_line_detail
984 , l_to_revision
985 , l_to_subinventory_code
986 , l_to_subinventory_id
987 , l_to_locator_id
988 , l_to_lot_number
989 , l_to_lot_number_id
990 , l_reservation_action_code
991 , l_validation_flag
992 , l_partial_quantities_allowed
993 , l_ship_ready_flag
994 , l_lpn_id
995 , l_to_lpn_id
996 , l_attribute_category
997 , l_attribute1
998 , l_attribute2
999 , l_attribute3
1000 , l_attribute4
1001 , l_attribute5
1002 , l_attribute6
1003 , l_attribute7
1004 , l_attribute8
1005 , l_attribute9
1006 , l_attribute10
1007 , l_attribute11
1008 , l_attribute12
1009 , l_attribute13
1010 , l_attribute14
1011 , l_attribute15
1012 from mtl_reservations_interface
1013 where reservation_interface_id = p_reservation_interface_id
1014 and row_status_code = 1 /* Active */
1015 and error_code is null
1016 and error_explanation is null;
1017
1018 /*
1019 ** Populate local structures with fetched data.
1020 */
1021 l_rsv_rec.reservation_id := NULL;
1022 l_rsv_rec.requirement_date := l_requirement_date;
1023 l_rsv_rec.organization_id := l_organization_id;
1024 l_rsv_rec.inventory_item_id := l_inventory_item_id;
1025 l_rsv_rec.demand_source_type_id := l_demand_source_type_id;
1026 l_rsv_rec.demand_source_name := l_demand_source_name;
1027 l_rsv_rec.demand_source_header_id := l_demand_source_header_id;
1028 l_rsv_rec.demand_source_line_id := l_demand_source_line_id;
1029 l_rsv_rec.demand_source_delivery := NULL;
1030 l_rsv_rec.primary_uom_code := l_primary_uom_code;
1031 l_rsv_rec.primary_uom_id := l_primary_uom_id;
1032 l_rsv_rec.secondary_uom_code := l_secondary_uom_code; -- INVCONV
1033 l_rsv_rec.secondary_uom_id := l_secondary_uom_id; -- INVCONV
1034 l_rsv_rec.reservation_uom_code := l_reservation_uom_code;
1035 l_rsv_rec.reservation_uom_id := l_reservation_uom_id;
1036 l_rsv_rec.reservation_quantity := l_reservation_quantity;
1037 l_rsv_rec.primary_reservation_quantity := l_primary_rsv_quantity;
1038 l_rsv_rec.secondary_reservation_quantity := l_secondary_rsv_quantity; -- INVCONV
1039 l_rsv_rec.autodetail_group_id := NULL;
1040 l_rsv_rec.external_source_code := NULL;
1041 l_rsv_rec.external_source_line_id := NULL;
1042 l_rsv_rec.supply_source_type_id := l_supply_source_type_id;
1043 l_rsv_rec.supply_source_name := l_supply_source_name;
1044 l_rsv_rec.supply_source_header_id := l_supply_source_header_id;
1045 l_rsv_rec.supply_source_line_id := l_supply_source_line_id;
1046 l_rsv_rec.supply_source_line_detail := l_supply_source_line_detail;
1047 l_rsv_rec.revision := l_revision;
1048 l_rsv_rec.subinventory_code := l_subinventory_code;
1049 l_rsv_rec.subinventory_id := l_subinventory_id;
1050 l_rsv_rec.locator_id := l_locator_id;
1051 l_rsv_rec.lot_number := l_lot_number;
1052 l_rsv_rec.lot_number_id := l_lot_number_id;
1053 l_rsv_rec.pick_slip_number := NULL;
1054 l_rsv_rec.lpn_id := l_lpn_id;
1055 l_rsv_rec.attribute_category := l_attribute_category;
1056 l_rsv_rec.attribute1 := l_attribute1;
1057 l_rsv_rec.attribute2 := l_attribute2;
1058 l_rsv_rec.attribute3 := l_attribute3;
1059 l_rsv_rec.attribute4 := l_attribute4;
1060 l_rsv_rec.attribute5 := l_attribute5;
1061 l_rsv_rec.attribute6 := l_attribute6;
1062 l_rsv_rec.attribute7 := l_attribute7;
1063 l_rsv_rec.attribute8 := l_attribute8;
1064 l_rsv_rec.attribute9 := l_attribute9;
1065 l_rsv_rec.attribute10 := l_attribute10;
1066 l_rsv_rec.attribute11 := l_attribute11;
1067 l_rsv_rec.attribute12 := l_attribute12;
1068 l_rsv_rec.attribute13 := l_attribute13;
1069 l_rsv_rec.attribute14 := l_attribute14;
1070 l_rsv_rec.attribute15 := l_attribute15;
1071 l_rsv_rec.ship_ready_flag := l_ship_ready_flag;
1072
1073 l_to_rsv_rec.reservation_id := NULL;
1074 l_to_rsv_rec.requirement_date := l_requirement_date;
1075 l_to_rsv_rec.organization_id := l_to_organization_id;
1076 l_to_rsv_rec.inventory_item_id := l_inventory_item_id;
1077 l_to_rsv_rec.demand_source_type_id := l_to_demand_source_type_id;
1078 l_to_rsv_rec.demand_source_name := l_to_demand_source_name;
1079 l_to_rsv_rec.demand_source_header_id := l_to_demand_source_header_id;
1080 l_to_rsv_rec.demand_source_line_id := l_to_demand_source_line_id;
1081 l_to_rsv_rec.demand_source_delivery := NULL;
1082 l_to_rsv_rec.primary_uom_code := l_primary_uom_code;
1083 l_to_rsv_rec.primary_uom_id := l_primary_uom_id;
1084 l_to_rsv_rec.secondary_uom_code := l_secondary_uom_code; -- INVCONV
1085 l_to_rsv_rec.secondary_uom_id := l_secondary_uom_id; -- INVCONV
1086 l_to_rsv_rec.reservation_uom_code := l_reservation_uom_code;
1087 l_to_rsv_rec.reservation_uom_id := l_reservation_uom_id;
1088 l_to_rsv_rec.reservation_quantity := l_reservation_quantity;
1089 l_to_rsv_rec.primary_reservation_quantity := l_primary_rsv_quantity;
1090 l_to_rsv_rec.secondary_reservation_quantity := l_secondary_rsv_quantity; -- INVCONV
1091 l_to_rsv_rec.autodetail_group_id := NULL;
1092 l_to_rsv_rec.external_source_code := NULL;
1093 l_to_rsv_rec.external_source_line_id := NULL;
1094 l_to_rsv_rec.supply_source_type_id := l_to_supply_source_type_id;
1095 l_to_rsv_rec.supply_source_name := l_to_supply_source_name;
1096 l_to_rsv_rec.supply_source_header_id := l_to_supply_source_header_id;
1097 l_to_rsv_rec.supply_source_line_id := l_to_supply_source_line_id;
1098 l_to_rsv_rec.supply_source_line_detail := l_to_supply_source_line_detail;
1099 l_to_rsv_rec.revision := l_to_revision;
1100 l_to_rsv_rec.subinventory_code := l_to_subinventory_code;
1101 l_to_rsv_rec.subinventory_id := l_to_subinventory_id;
1102 l_to_rsv_rec.locator_id := l_to_locator_id;
1103 l_to_rsv_rec.lot_number := l_to_lot_number;
1104 l_to_rsv_rec.lot_number_id := l_to_lot_number_id;
1105 l_to_rsv_rec.pick_slip_number := NULL;
1106 l_to_rsv_rec.lpn_id := l_to_lpn_id;
1107 l_to_rsv_rec.attribute_category := l_attribute_category;
1108 l_to_rsv_rec.attribute1 := l_attribute1;
1109 l_to_rsv_rec.attribute2 := l_attribute2;
1110 l_to_rsv_rec.attribute3 := l_attribute3;
1111 l_to_rsv_rec.attribute4 := l_attribute4;
1112 l_to_rsv_rec.attribute5 := l_attribute5;
1113 l_to_rsv_rec.attribute6 := l_attribute6;
1114 l_to_rsv_rec.attribute7 := l_attribute7;
1115 l_to_rsv_rec.attribute8 := l_attribute8;
1116 l_to_rsv_rec.attribute9 := l_attribute9;
1117 l_to_rsv_rec.attribute10 := l_attribute10;
1118 l_to_rsv_rec.attribute11 := l_attribute11;
1119 l_to_rsv_rec.attribute12 := l_attribute12;
1120 l_to_rsv_rec.attribute13 := l_attribute13;
1121 l_to_rsv_rec.attribute14 := l_attribute14;
1122 l_to_rsv_rec.attribute15 := l_attribute15;
1123 l_to_rsv_rec.ship_ready_flag := l_ship_ready_flag;
1124
1125 -- Bug 3357096. The subinventory code cannot be different if the sub is specified at the order line
1126 -- and the user is creating a new record for the order line with a different subinventory.
1127 If (l_reservation_action_code = 1) then
1128 If (l_rsv_rec.demand_source_type_id in (2,8)) and (l_rsv_rec.supply_source_type_id = 13) and
1129 (l_rsv_rec.subinventory_code is not null) then
1130 select subinventory into l_oe_line_subinventory from oe_order_lines_all where
1131 line_id = l_rsv_rec.demand_source_line_id;
1132
1133 If (l_oe_line_subinventory is not null) and (l_oe_line_subinventory <> l_rsv_rec.subinventory_code) then
1134 fnd_message.set_name('INV','INV_INVALID_SUBINVENTORY');
1135 fnd_msg_pub.add;
1136 raise fnd_api.g_exc_error;
1137 End if;
1138 End if;
1139 End if;
1140
1141 -- Bug:3384601 - Uom Conversions are not happening when RESERVATION_UOM_CODE is populated in MTL_RESERVATIONS_INTERFACE
1142 -- and processed by the Reservation Interface Manager. It was creating reservations always in primary_uom of the item.
1143
1144 -- Bug 3475862 added below code for update reservation too, also, calling inv_cache to get the primary_uom_code for
1145 -- better performance.
1146 If (l_reservation_action_code in (1,2)) then
1147 IF l_rsv_rec.primary_reservation_quantity <= 0 OR l_rsv_rec.reservation_quantity <= 0 THEN
1148 debug('Primary Reservation Quantity or Reservation Quantity should not be equal to lessa than zero',c_api_name,1);
1149 fnd_message.set_name('INV', 'INV_GREATER_THAN_ZERO');
1150 fnd_msg_pub.ADD;
1151 RAISE fnd_api.g_exc_error;
1152 END IF;
1153
1154 IF ( inv_cache.set_item_rec(l_rsv_rec.organization_id, l_rsv_rec.inventory_item_id) ) THEN
1155 l_rsv_rec.primary_uom_code := inv_cache.item_rec.primary_uom_code;
1156 END IF;
1157
1158 IF l_rsv_rec.reservation_uom_code IS NOT NULL THEN
1159 IF l_rsv_rec.primary_uom_code = l_rsv_rec.reservation_uom_code THEN
1160 l_tmp_quantity := l_rsv_rec.reservation_quantity;
1161 ELSE
1162 -- Convert reservation quantity in reservation uom
1163 -- to primary quantity in primary uom.
1164 l_tmp_quantity := inv_convert.inv_um_convert(
1165 item_id => l_rsv_rec.inventory_item_id
1166 , PRECISION => NULL -- use default precision
1167 , from_quantity => l_rsv_rec.reservation_quantity
1168 , from_unit => l_rsv_rec.reservation_uom_code
1169 , to_unit => l_rsv_rec.primary_uom_code
1170 , from_name => NULL -- from uom name
1171 , to_name => NULL -- to uom name
1172 );
1173 IF l_tmp_quantity = -99999 THEN
1174 -- conversion failed
1175 debug('Cannot Convert to Primary UOM',c_api_name,1);
1176 fnd_message.set_name('INV', 'CAN-NOT-CONVERT-TO-PRIMARY-UOM');
1177 fnd_msg_pub.ADD;
1178 RAISE fnd_api.g_exc_error;
1179 END IF;
1180 END IF;
1181 END IF;
1182
1183 debug('Primary_reservation quantity = '||l_tmp_quantity,c_api_name,1);
1184 l_rsv_rec.primary_reservation_quantity := l_tmp_quantity;
1185
1186 -- Bug 3475862, copying the converted quantity to l_to_rsv_rec.primary_reservation_quantity too.
1187 -- For update reservation this qty will be used to update the record.
1188 If (l_reservation_action_code = 2) then
1189 l_to_rsv_rec.primary_reservation_quantity := l_tmp_quantity;
1190 END IF;
1191 END IF;
1192 -- Bug:3384601 -- End of code changes
1193 /*
1194 ** Since the table column datatype doesn't match the parameter datatype,
1195 ** we have to do this.
1196 ** l_partial_quantities 1=Yes; Not(1)=No;
1197 */
1198 if (l_partial_quantities_allowed = 1) then
1199 l_partial_reservation_flag := fnd_api.g_true;
1200 else
1201 l_partial_reservation_flag := fnd_api.g_false;
1202 end if;
1203
1204 /*
1205 ** Initialize parameter return status
1206 */
1207 l_return_status := fnd_api.g_ret_sts_success;
1208
1209 /*
1210 ** For not(insert) requests quantity should be a value of don't care
1211 ** for a successful retrieval of source record.
1212 */
1213 if (l_reservation_action_code in (2,3,4)) then
1214 l_rsv_rec.reservation_quantity := fnd_api.g_miss_num;
1215 l_rsv_rec.primary_reservation_quantity := fnd_api.g_miss_num;
1216 l_rsv_rec.secondary_reservation_quantity:= fnd_api.g_miss_num; -- INVCONV
1217 end if;
1218
1219 /*
1220 ** Call appropriate API based on action code
1221 ** 1. Create reservation
1222 ** 2. Update reservation
1223 ** 3. Delete reservation
1224 ** 4. Transfer reservation
1225 */
1226
1227 if (l_reservation_action_code = 1) then
1228 -- Create reservation
1229
1230 inv_reservation_pub.create_reservation(
1231 p_api_version_number => 1.0
1232 , p_init_msg_lst => fnd_api.g_false
1233 , x_return_status => l_return_status
1234 , x_msg_count => x_msg_count
1235 , x_msg_data => x_msg_data
1236 , p_rsv_rec => l_rsv_rec
1237 , p_serial_number => l_serial_number
1238 , x_serial_number => l_serial_number
1239 , p_partial_reservation_flag => l_partial_reservation_flag
1240 , p_force_reservation_flag => fnd_api.g_false
1241 , p_validation_flag => fnd_api.g_true
1242 , x_quantity_reserved => l_quantity_reserved
1243 , x_secondary_quantity_reserved => l_secondary_quantity_reserved --INVCONV
1244 , x_reservation_id => l_reservation_id);
1245
1246 if (l_return_status = fnd_api.g_ret_sts_success) then
1247 x_return_status := l_return_status;
1248
1249 if (p_form_mode in ('N','n')) then
1250 -- kgm_msg
1251 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_SUCCESS');
1252 fnd_message.set_token('INTERFACE_ID',
1253 to_char(p_reservation_interface_id));
1254 fnd_file.put_line(fnd_file.log,fnd_message.get);
1255 fnd_file.put_line(fnd_file.log,' ');
1256
1257 /*
1258 fnd_message.set_name('INV', 'RSV_INTERFACE_SUCCESS');
1259 fnd_file.put_line(fnd_file.log,fnd_message.get);
1260 fnd_file.put(fnd_file.log, 'Reservation Interface Id: ');
1261 fnd_file.put_line(fnd_file.log,
1262 to_char(p_reservation_interface_id));
1263
1264 fnd_file.put(fnd_file.log, 'Quantity Reserved: ');
1265 fnd_file.put_line(fnd_file.log,
1266 to_char(l_quantity_reserved));
1267
1268 fnd_file.put(fnd_file.log, 'Secondary Quantity Reserved: '); --INVCONV
1269 fnd_file.put_line(fnd_file.log,
1270 to_char(l_secondary_quantity_reserved)); -- INVCONV
1271
1272 fnd_file.put(fnd_file.log, 'Reservation Id: ');
1273 fnd_file.put_line(fnd_file.log,
1274 to_char(l_reservation_id));
1275 fnd_file.put_line(fnd_file.log,' ');
1276 */
1277 end if;
1278
1279 elsif (l_return_status = fnd_api.g_ret_sts_error) then
1280 raise fnd_api.g_exc_error;
1281 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1282 raise fnd_api.g_exc_unexpected_error;
1283 end if;
1284
1285 elsif (l_reservation_action_code = 2) then
1286 -- Update reservation
1287 -- Adeed the extra parameter for call to update_reservations
1288 -- Bug Number 3392957
1289 inv_reservation_pub.update_reservation(
1290 p_api_version_number => 1.0
1291 , p_init_msg_lst => fnd_api.g_false
1292 , x_return_status => l_return_status
1293 , x_msg_count => x_msg_count
1294 , x_msg_data => x_msg_data
1295 , p_original_rsv_rec => l_rsv_rec
1296 , p_to_rsv_rec => l_to_rsv_rec
1297 , p_original_serial_number => l_serial_number
1298 , p_to_serial_number => l_serial_number
1299 , p_validation_flag => fnd_api.g_true
1300 , p_check_availability => fnd_api.g_true
1301 );
1302
1303 if (l_return_status = fnd_api.g_ret_sts_success) then
1304 x_return_status := l_return_status;
1305
1306 if (p_form_mode in ('N','n')) then
1307 -- kgm_msg
1308 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_SUCCESS');
1309 fnd_message.set_token('INTERFACE_ID',
1310 to_char(p_reservation_interface_id));
1311 fnd_file.put_line(fnd_file.log,fnd_message.get);
1312 fnd_file.put_line(fnd_file.log,' ');
1313
1314 /*
1315 fnd_message.set_name('INV', 'RSV_INTERFACE_SUCCESS');
1316 fnd_file.put_line(fnd_file.log,fnd_message.get);
1317 fnd_file.put(fnd_file.log, 'Reservation Interface Id: ');
1318 fnd_file.put_line(fnd_file.log,
1319 to_char(p_reservation_interface_id));
1320 fnd_file.put_line(fnd_file.log,' ');
1321 */
1322 end if;
1323
1324 elsif (l_return_status = fnd_api.g_ret_sts_error) then
1325 raise fnd_api.g_exc_error;
1326 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1327 raise fnd_api.g_exc_unexpected_error;
1328 end if;
1329
1330 elsif (l_reservation_action_code = 3) then
1331 -- Delete reservation
1332 l_rsv_rec.reservation_id := fnd_api.g_miss_num;
1333
1334 inv_reservation_pub.delete_reservation(
1335 p_api_version_number => 1.0
1336 , p_init_msg_lst => fnd_api.g_false
1337 , x_return_status => l_return_status
1338 , x_msg_count => x_msg_count
1339 , x_msg_data => x_msg_data
1340 , p_rsv_rec => l_rsv_rec
1341 , p_serial_number => l_serial_number);
1342
1343 if (l_return_status = fnd_api.g_ret_sts_success) then
1344 x_return_status := l_return_status;
1345
1346 if (p_form_mode in ('N','n')) then
1347 -- kgm_msg
1348 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_SUCCESS');
1349 fnd_message.set_token('INTERFACE_ID',
1350 to_char(p_reservation_interface_id));
1351 fnd_file.put_line(fnd_file.log,fnd_message.get);
1352 fnd_file.put_line(fnd_file.log,' ');
1353
1354 /*
1355 fnd_message.set_name('INV', 'RSV_INTERFACE_SUCCESS');
1356 fnd_file.put_line(fnd_file.log,fnd_message.get);
1357 fnd_file.put(fnd_file.log, 'Reservation Interface Id: ');
1358 fnd_file.put_line(fnd_file.log,
1359 to_char(p_reservation_interface_id));
1360 fnd_file.put_line(fnd_file.log,' ');
1361 */
1362 end if;
1363
1364 elsif (l_return_status = fnd_api.g_ret_sts_error) then
1365 raise fnd_api.g_exc_error;
1366 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1367 raise fnd_api.g_exc_unexpected_error;
1368 end if;
1369
1370 elsif (l_reservation_action_code = 4) then
1371 -- Transfer reservation
1372
1373 l_rsv_rec.reservation_id := fnd_api.g_miss_num;
1374
1375 inv_reservation_pub.transfer_reservation(
1376 p_api_version_number => 1.0
1377 , p_init_msg_lst => fnd_api.g_false
1378 , x_return_status => l_return_status
1379 , x_msg_count => x_msg_count
1380 , x_msg_data => x_msg_data
1381 , p_is_transfer_supply => fnd_api.g_true
1382 , p_original_rsv_rec => l_rsv_rec
1383 , p_to_rsv_rec => l_to_rsv_rec
1384 , p_original_serial_number => l_serial_number
1385 , p_to_serial_number => l_serial_number
1386 , p_validation_flag => fnd_api.g_true
1387 , x_to_reservation_id => l_reservation_id);
1388
1389 if (l_return_status = fnd_api.g_ret_sts_success) then
1390 x_return_status := l_return_status;
1391
1392 if (p_form_mode in ('N','n')) then
1393 -- kgm_msg
1394 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_SUCCESS');
1395 fnd_message.set_token('INTERFACE_ID',
1396 to_char(p_reservation_interface_id));
1397 fnd_file.put_line(fnd_file.log,fnd_message.get);
1398 fnd_file.put_line(fnd_file.log,' ');
1399
1400 /*
1401 fnd_message.set_name('INV', 'RSV_INTERFACE_SUCCESS');
1402 fnd_file.put_line(fnd_file.log,fnd_message.get);
1403 fnd_file.put(fnd_file.log, 'Reservation Interface Id: ');
1404 fnd_file.put_line(fnd_file.log,
1405 to_char(p_reservation_interface_id));
1406 fnd_file.put_line(fnd_file.log,' ');
1407 */
1408 end if;
1409
1410 elsif (l_return_status = fnd_api.g_ret_sts_error) then
1411 raise fnd_api.g_exc_error;
1412 elsif (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1413 raise fnd_api.g_exc_unexpected_error;
1414 end if;
1415
1416 else
1417 -- Invalid or unsuitable action code here
1418
1419 raise INVALID_ACTION_CODE;
1420
1421 end if;
1422
1423 if (l_return_status = fnd_api.g_ret_sts_success) then
1424 update mtl_reservations_interface
1425 set row_status_code = 2 /* Completed */
1426 where reservation_interface_id = p_reservation_interface_id;
1427 end if;
1428
1429 exception
1430 when NO_DATA_FOUND then
1431 x_return_status := fnd_api.g_ret_sts_error;
1432
1433 -- kgm_msg
1434 if (p_form_mode in ('N','n')) then
1435 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_ERROR');
1436 fnd_message.set_token('INTERFACE_ID',
1437 to_char(p_reservation_interface_id));
1438 fnd_file.put_line(fnd_file.log,fnd_message.get);
1439 end if;
1440
1441 -- Add message to message list
1442 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_NOT_FOUND');
1443 fnd_msg_pub.add;
1444
1445 fnd_msg_pub.count_and_get(
1446 p_count => x_msg_count
1447 , p_data => x_msg_data
1448 , p_encoded => 'F');
1449
1450 -- Load error code and text
1451 x_error_code := 1;
1452 x_error_text := fnd_msg_pub.get(1, 'F');
1453
1454 if (p_form_mode in ('N','n')) then
1455 print_error(x_msg_count);
1456 end if;
1457
1458 when INVALID_ACTION_CODE then
1459 x_return_status := fnd_api.g_ret_sts_error;
1460
1461 if (p_form_mode in ('N','n')) then
1462 -- kgm_msg
1463 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_ERROR');
1464 fnd_message.set_token('INTERFACE_ID',
1465 to_char(p_reservation_interface_id));
1466 fnd_file.put_line(fnd_file.log,fnd_message.get);
1467 end if;
1468
1469 -- Add message to message list
1470 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_INVALID_CODE');
1471 fnd_msg_pub.add;
1472
1473 fnd_msg_pub.count_and_get(
1474 p_count => x_msg_count
1475 , p_data => x_msg_data
1476 , p_encoded => 'F');
1477
1478 -- Load error code and text
1479 x_error_code := 1;
1480 x_error_text := fnd_msg_pub.get(1, 'F');
1481
1482 if (p_form_mode in ('N','n')) then
1483 print_error(x_msg_count);
1484 end if;
1485
1486 when fnd_api.g_exc_error then
1487 x_return_status := fnd_api.g_ret_sts_error;
1488
1489 if (p_form_mode in ('N','n')) then
1490 -- kgm_msg
1491 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_ERROR');
1492 fnd_message.set_token('INTERFACE_ID',
1493 to_char(p_reservation_interface_id));
1494 fnd_file.put_line(fnd_file.log,fnd_message.get);
1495 end if;
1496
1497 fnd_msg_pub.count_and_get(
1498 p_count => x_msg_count
1499 , p_data => x_msg_data
1500 , p_encoded => 'F');
1501
1502 -- Load error code and text
1503 x_error_code := 1;
1504 x_error_text := fnd_msg_pub.get(1, 'F');
1505
1506 if (p_form_mode in ('N','n')) then
1507 print_error(x_msg_count);
1508 end if;
1509
1510 when fnd_api.g_exc_unexpected_error then
1511 x_return_status := fnd_api.g_ret_sts_unexp_error;
1512
1513 if (p_form_mode in ('N','n')) then
1514 -- kgm_msg
1515 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_ERROR');
1516 fnd_message.set_token('INTERFACE_ID',
1517 to_char(p_reservation_interface_id));
1518 fnd_file.put_line(fnd_file.log,fnd_message.get);
1519 end if;
1520
1521 fnd_msg_pub.count_and_get(
1522 p_count => x_msg_count
1523 , p_data => x_msg_data
1524 , p_encoded => 'F');
1525
1526 -- Load error code and text
1527 x_error_code := 1;
1528 x_error_text := fnd_msg_pub.get(1, 'F');
1529
1530 if (p_form_mode in ('N','n')) then
1531 print_error(x_msg_count);
1532 end if;
1533
1534 when others then
1535 x_return_status := fnd_api.g_ret_sts_unexp_error;
1536
1537 if (p_form_mode in ('N','n')) then
1538 -- kgm_msg
1539 fnd_message.set_name('INV', 'INV_RSV_INTERFACE_ERROR');
1540 fnd_message.set_token('INTERFACE_ID',
1541 to_char(p_reservation_interface_id));
1542 fnd_file.put_line(fnd_file.log,fnd_message.get);
1543 end if;
1544
1545 if (fnd_msg_pub.check_msg_level
1546 (fnd_msg_pub.g_msg_lvl_unexp_error))then
1547 fnd_msg_pub.add_exc_msg(g_pkg_name,c_api_name);
1548 end if;
1549
1550 fnd_msg_pub.count_and_get(
1551 p_count => x_msg_count
1552 , p_data => x_msg_data
1553 , p_encoded => 'F');
1554
1555 -- Load error code and text
1556 x_error_code := 1;
1557 x_error_text := fnd_msg_pub.get(1, 'F');
1558
1559 if (p_form_mode in ('N','n')) then
1560 print_error(x_msg_count);
1561 end if;
1562
1563 end rsv_interface_line_processor;
1564
1565 /*
1566 ** ===========================================================================
1567 ** Procedure:
1568 ** print_error
1569 **
1570 ** Description:
1571 ** Writes message text in log files.
1572 **
1573 ** Input Parameters:
1574 ** p_msg_count
1575 **
1576 ** Output Parameters:
1577 ** None
1578 **
1579 ** Tables Used:
1580 ** None
1581 **
1582 ** ===========================================================================
1583 */
1584 PROCEDURE print_error (p_msg_count IN NUMBER)
1585 is
1586 l_msg_data VARCHAR2(2000);
1587 begin
1588 if p_msg_count = 0 then
1589 null;
1590 else
1591 for i in 1..p_msg_count loop
1592 l_msg_data := fnd_msg_pub.get(i, 'F');
1593 fnd_file.put_line(fnd_file.log, l_msg_data);
1594 end loop;
1595
1596 fnd_file.put_line(fnd_file.log, ' ');
1597 end if;
1598
1599 fnd_msg_pub.initialize;
1600
1601 exception
1602 when others then
1603 fnd_file.put_line(fnd_file.log, sqlerrm);
1604 end print_error;
1605
1606 END INV_RESERVATIONS_INTERFACE;