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