DBA Data[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;