1 PACKAGE inv_reservation_pvt AUTHID CURRENT_USER as
2 /* $Header: INVRSV3S.pls 120.8.12020000.3 2013/02/18 18:26:01 avrose ship $*/
3
4 /**** {{ R12 Enhanced reservations code changes }}****/
5 --Procedure convert_missing_to_null
6 PROCEDURE convert_missing_to_null
7 (p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
8 , x_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type);
9 /*** End R12 ***/
10
11 -- Procedure
12 -- query_reservation
13 -- Description
14 -- This procedure returns all reservations that satisfy the user
15 -- specified criteria.
16 -- Input Parameters
17 -- p_query_input
18 -- used to specify query criteria
19 -- p_lock_records
20 -- fnd_api.g_true or fnd_api.g_false (default).
21 -- Specify whether to lock matching records
22 -- p_sort_by_req_date
23 -- Specify whether to sort the return records by requirement date
24 -- see INVRSVGS.pls for details
25 -- p_cancel_order_mode
26 -- Specify whether to sort the return records by ship_ready_flag
27 -- and detailed quantity during cancellation of orders
28 --
29 -- Output Parameters
30 -- x_error_code
31 -- This error code is only meaningful if x_return_status equals
32 -- fnd_api.g_ret_sts_error.
33 -- see INVRSVGS.pls for error code definition
34 PROCEDURE query_reservation
35 (
36 p_api_version_number IN NUMBER
37 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
38 , x_return_status OUT NOCOPY VARCHAR2
39 , x_msg_count OUT NOCOPY NUMBER
40 , x_msg_data OUT NOCOPY VARCHAR2
41 , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
42 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
43 , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
44 , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
45 , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
46 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
47 , x_error_code OUT NOCOPY NUMBER
48 );
49
50 /**** {{ R12 Enhanced reservations code changes }}****/
51 PROCEDURE query_reservation (
52 p_api_version_number IN NUMBER
53 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
54 , x_return_status OUT NOCOPY VARCHAR2
55 , x_msg_count OUT NOCOPY NUMBER
56 , x_msg_data OUT NOCOPY VARCHAR2
57 , p_query_input IN inv_reservation_global.mtl_reservation_rec_type
58 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
59 , p_sort_by_req_date IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
60 , p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
61 , p_serial_number_table IN inv_reservation_global.rsv_serial_number_table
62 , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
63 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
64 , x_serial_number_table OUT NOCOPY inv_reservation_global.rsv_serial_number_table
65 , x_serial_number_table_count OUT NOCOPY NUMBER
66 , x_error_code OUT NOCOPY NUMBER
67 );
68
69 /*** End R12 ***/
70 --
71 -- INVCONV - add out parameter x_secondary_quantity_reserved
72 PROCEDURE create_reservation
73 (
74 p_api_version_number IN NUMBER
75 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
76 , x_return_status OUT NOCOPY VARCHAR2
77 , x_msg_count OUT NOCOPY NUMBER
78 , x_msg_data OUT NOCOPY VARCHAR2
79 , p_rsv_rec
80 IN inv_reservation_global.mtl_reservation_rec_type
81 , p_serial_number
82 IN inv_reservation_global.serial_number_tbl_type
83 , x_serial_number
84 OUT NOCOPY inv_reservation_global.serial_number_tbl_type
85 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
86 , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
87 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
88 , p_over_reservation_flag IN NUMBER DEFAULT 0
89 , x_quantity_reserved OUT NOCOPY NUMBER
90 , x_secondary_quantity_reserved OUT NOCOPY NUMBER
91 , x_reservation_id OUT NOCOPY NUMBER
92 /**** {{ R12 Enhanced reservations code changes }}****/
93 , p_partial_rsv_exists IN BOOLEAN DEFAULT FALSE
94 /*** End R12 ***/
95 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
96 );
97
98 --This procedure is a overlodaed procedure
99 --Here the partial reservations are honoured.
100 -- INVCONV - Incorporate secondary_quantity_reserved as an OUT parameter
101 PROCEDURE update_reservation
102 (
103 p_api_version_number IN NUMBER
104 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
105 , x_return_status OUT NOCOPY VARCHAR2
106 , x_msg_count OUT NOCOPY NUMBER
107 , x_msg_data OUT NOCOPY VARCHAR2
108 , x_quantity_reserved OUT NOCOPY NUMBER
109 , x_secondary_quantity_reserved OUT NOCOPY NUMBER
110 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
111 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
112 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
113 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
114 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
115 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
116 , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
117 , p_over_reservation_flag IN NUMBER DEFAULT 0
118 );
119
120 --This procedure updates the reservation and will in turn call the
121 -- overloaded update_reservation
122 PROCEDURE update_reservation
123 (
124 p_api_version_number IN NUMBER
125 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_msg_count OUT NOCOPY NUMBER
128 , x_msg_data OUT NOCOPY VARCHAR2
129 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
130 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
131 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
132 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
133 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
134 , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
135 , p_over_reservation_flag IN NUMBER DEFAULT 0
136 );
137
138
139 --
140 PROCEDURE delete_reservation
141 (
142 p_api_version_number IN NUMBER
143 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
144 , x_return_status OUT NOCOPY VARCHAR2
145 , x_msg_count OUT NOCOPY NUMBER
146 , x_msg_data OUT NOCOPY VARCHAR2
147 , p_rsv_rec
148 IN inv_reservation_global.mtl_reservation_rec_type
149 , p_original_serial_number
150 IN inv_reservation_global.serial_number_tbl_type
151 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
152 );
153 --
154 -- INVCONV - Incorporate secondary quantities into signature
155 PROCEDURE relieve_reservation
156 (
157 p_api_version_number IN NUMBER
158 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
159 , x_return_status OUT NOCOPY VARCHAR2
160 , x_msg_count OUT NOCOPY NUMBER
161 , x_msg_data OUT NOCOPY VARCHAR2
162 , p_rsv_rec
163 IN inv_reservation_global.mtl_reservation_rec_type
164 , p_primary_relieved_quantity IN NUMBER
165 , p_secondary_relieved_quantity IN NUMBER
166 , p_relieve_all IN VARCHAR2 DEFAULT fnd_api.g_true
167 , p_original_serial_number
168 IN inv_reservation_global.serial_number_tbl_type
169 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
170 , x_primary_relieved_quantity OUT NOCOPY NUMBER
171 , x_secondary_relieved_quantity OUT NOCOPY NUMBER
172 , x_primary_remain_quantity OUT NOCOPY NUMBER
173 , x_secondary_remain_quantity OUT NOCOPY NUMBER
174 );
175
176
177 PROCEDURE transfer_reservation
178 (
179 p_api_version_number IN NUMBER
180 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
181 , x_return_status OUT NOCOPY VARCHAR2
182 , x_msg_count OUT NOCOPY NUMBER
183 , x_msg_data OUT NOCOPY VARCHAR2
184 , p_original_rsv_rec
185 IN inv_reservation_global.mtl_reservation_rec_type
186 , p_to_rsv_rec
187 IN inv_reservation_global.mtl_reservation_rec_type
188 , p_original_serial_number
189 IN inv_reservation_global.serial_number_tbl_type
190 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
191 , p_over_reservation_flag IN NUMBER DEFAULT 0
192 , x_reservation_id OUT NOCOPY NUMBER
193 );
194
195 /**** {{ R12 Enhanced reservations code changes }}****/
196 -- Overloaded this API as the original transfer reservation did not have
197 -- the to serial number table as an input
198 PROCEDURE transfer_reservation
199 (
200 p_api_version_number IN NUMBER
201 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
202 , x_return_status OUT NOCOPY VARCHAR2
203 , x_msg_count OUT NOCOPY NUMBER
204 , x_msg_data OUT NOCOPY VARCHAR2
205 , p_original_rsv_rec
206 IN inv_reservation_global.mtl_reservation_rec_type
207 , p_to_rsv_rec
208 IN inv_reservation_global.mtl_reservation_rec_type
209 , p_original_serial_number
210 IN inv_reservation_global.serial_number_tbl_type
211 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
212 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
213 , p_over_reservation_flag IN NUMBER DEFAULT 0
214 , x_reservation_id OUT NOCOPY NUMBER
215 );
216 /*** End R12 ***/
217
218 --
219 /*
220 ** ----------------------------------------------------------------------
221 ** For Order Management(OM) use only. Please read below:
222 ** MUST PASS DEMAND SOURCE HEADER ID AND DEMAND SOURCE LINE ID
223 ** ----------------------------------------------------------------------
224 ** This API has been written exclusively for Order Management, who query
225 ** reservations extensively.
226 ** The generic query reservation API, query_reservation(see signature above)
227 ** builds a dynamic SQL to satisfy all callers as it does not know what the
228 ** search criteria is, at design time.
229 ** The dynamic SQL consumes soft parse time, which reduces performance.
230 ** An excessive use of query_reservation contributes to performance
231 ** degradation because of soft parse times.
232 ** Since we know what OM would always use to query reservations
233 ** - demand source header and demand source line, a new API
234 ** with static SQL would be be effective, with reduced performance impact.
235 ** ----------------------------------------------------------------------
236 ** Since OM has been using query_reservation before this, the signature of the
237 ** new API below remains the same to cause minimal impact.
238 ** ----------------------------------------------------------------------
239 */
240
241 PROCEDURE query_reservation_om_hdr_line
242 (
243 p_api_version_number IN NUMBER
244 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
245 , x_return_status OUT NOCOPY VARCHAR2
246 , x_msg_count OUT NOCOPY NUMBER
247 , x_msg_data OUT NOCOPY VARCHAR2
248 , p_query_input
249 IN inv_reservation_global.mtl_reservation_rec_type
250 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
251 , p_sort_by_req_date
252 IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
253 , p_cancel_order_mode
254 IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
255 , x_mtl_reservation_tbl
256 OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
257 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
258 , x_error_code OUT NOCOPY NUMBER
259 );
260
261 /*
262 ** ---------------------------------------------------------------------------
263 ** procedure : upd_reservation_pup
264 ** description : This procedure updates the reservations when LPN consolidation
265 ** or spilt happens in staging.
266 **
267 ** i/p :
268 ** p_organization_id
269 ** identifier of organization in which reservation needs to be update
270 ** p_demand_source_header_id
271 ** source header id for which item is reserved
272 ** p_demand_source_line_id
273 ** source line id for which item is reserved
274 ** p_from_subinventory_code
275 ** Subinventory where item was before merge/split of LPN
276 ** p_from_locator_id
277 ** Locator id where item was before merge/split of LPN
278 ** p_to_subinventory_code
279 ** Subinventory where item is after merge/split of LPN
280 ** p_to_locator_id
281 ** Locator id where item is after merge/split of LPN
282 ** p_inventory_item_id
283 ** Item id
284 ** p_revision
285 ** Revision for revision controlled item
286 ** p_lot_number
287 ** Lot number for lot controlled item
288 ** p_quantity
289 ** Quantity
290 ** p_uom
291 ** Unit of measure
292 **
293 ** o/p:
294 ** x_return_status
295 ** return status indicating success, error, unexpected error
296 ** x_msg_count
297 ** number of messages in message list
298 ** x_msg_data
299 ** if the number of messages in message list is 1, contains
300 ** message text
301 ** ---------------------------------------------------------------------------
302 */
303
304 PROCEDURE UPD_RESERVATION_PUP ( x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2,
307 p_commit IN VARCHAR2 := FND_API.g_false,
308 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
309 p_organization_id IN NUMBER,
310 p_demand_source_header_id IN NUMBER,
311 p_demand_source_line_id IN NUMBER,
312 p_from_subinventory_code IN VARCHAR2,
313 p_from_locator_id IN NUMBER,
314 p_to_subinventory_code IN VARCHAR2,
315 p_to_locator_id IN NUMBER,
316 p_inventory_item_id IN NUMBER,
317 p_revision IN VARCHAR2,
318 p_lot_number IN VARCHAR2,
319 p_quantity IN NUMBER,
320 p_uom IN VARCHAR2,
321 p_validation_flag IN VARCHAR2 := fnd_api.g_false,
322 p_force_reservation_flag IN VARCHAR2 := fnd_api.g_false
323 );
324
325 /*
326 ** ---------------------------------------------------------------------------
327 ** procedure : Upd_Reservation_PUP_New
328 ** description : This procedure updates the reservations when LPN consolidation
329 ** or spilt happens in staging.
330 **
331 ** i/p :
332 ** p_organization_id
333 ** identifier of organization in which reservation needs to be update
334 ** p_demand_source_header_id
335 ** source header id for which item is reserved
336 ** p_demand_source_line_id
337 ** source line id for which item is reserved
338 ** p_from_subinventory_code
339 ** Subinventory where item was before merge/split of LPN
340 ** p_from_locator_id
341 ** Locator id where item was before merge/split of LPN
342 ** p_to_subinventory_code
343 ** Subinventory where item is after merge/split of LPN
344 ** p_to_locator_id
345 ** Locator id where item is after merge/split of LPN
346 ** p_inventory_item_id
347 ** Item id
348 ** p_revision
349 ** Revision for revision controlled item
350 ** p_lot_number
351 ** Lot number for lot controlled item
352 ** p_quantity
353 ** Quantity
354 ** p_uom
355 ** Unit of measure
356 ** p_lpn_id
357 ** lpn_id of the LPN for the new reservation line
358 ** p_requirement_date
359 ** Requirement Date
360 ** o/p:
361 ** x_return_status
362 ** return status indicating success, error, unexpected error
363 ** x_msg_count
364 ** number of messages in message list
365 ** x_msg_data
366 ** if the number of messages in message list is 1, contains
367 ** message text
368 ** ---------------------------------------------------------------------------
369 */
370 PROCEDURE Upd_Reservation_PUP_New(
371 x_return_status OUT NOCOPY VARCHAR2
372 , x_msg_count OUT NOCOPY NUMBER
373 , x_msg_data OUT NOCOPY VARCHAR2
374 , p_commit IN VARCHAR2 := fnd_api.g_false
375 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
376 , p_organization_id IN NUMBER
377 , p_demand_source_header_id IN NUMBER
378 , p_demand_source_line_id IN NUMBER
379 , p_from_subinventory_code IN VARCHAR2
380 , p_from_locator_id IN NUMBER
381 , p_to_subinventory_code IN VARCHAR2
382 , p_to_locator_id IN NUMBER
383 , p_inventory_item_id IN NUMBER
384 , p_revision IN VARCHAR2
385 , p_lot_number IN VARCHAR2
386 , p_quantity IN NUMBER
387 , p_uom IN VARCHAR2
388 , p_lpn_id IN NUMBER := NULL
389 , p_validation_flag IN VARCHAR2 := fnd_api.g_false
390 , p_force_reservation_flag IN VARCHAR2 := fnd_api.g_false
391 , p_requirement_date IN DATE DEFAULT NULL -- bug 2879208
392 , p_source_lpn_id IN NUMBER := NULL -- Bug 4016953/3871066
393 , p_demand_source_name IN VARCHAR2 DEFAULT NULL -- RTV Project
394 );
395
396 /*
397 ** ---------------------------------------------------------------------------
398 ** procedure : TRANSFER_LPN_TRX_RESERVATION
399 ** description : This procedure is called by the transaction manager to transfer a
400 ** reservation when LPN consolidation or spilt happens in staging..
401 **
402 ** i/p :
403 ** p_transaction_temp_id
404 ** temp id of line in mtl_material_transaction_temp being processed
405 ** p_organization_id
406 ** identifier of organization in which reservation needs to be update
407 ** p_lpn_id
408 ** lpn being split of consolidated and is in need for a reservation transfer
409 ** p_from_subinventory_code
410 ** Subinventory where item was before merge/split of LPN
411 ** p_from_locator_id
412 ** Locator id where item was before merge/split of LPN
413 ** p_to_subinventory_code
414 ** Subinventory where item is after merge/split of LPN
415 ** p_to_locator_id
416 ** Locator id where item is after merge/split of LPN
417 ** p_inventory_item_id
418 ** Item id
419 ** p_revision
420 ** Revision for revision controlled item
421 ** p_lot_number
422 ** Lot number for lot controlled item
423 ** p_trx_quantity
424 ** Quantity
425 ** p_trx_uom
426 ** Unit of measure
427 **
428 ** o/p:
429 ** x_return_status
430 ** return status indicating success, error, unexpected error
431 ** x_msg_count
432 ** number of messages in message list
433 ** x_msg_data
434 ** if the number of messages in message list is 1, contains
435 ** message text
436 ** ---------------------------------------------------------------------------
437 */
438
439 Procedure TRANSFER_LPN_TRX_RESERVATION
440 ( x_return_status OUT NOCOPY VARCHAR2,
441 x_msg_count OUT NOCOPY NUMBER,
442 x_msg_data OUT NOCOPY VARCHAR2,
443 p_commit IN VARCHAR2 := FND_API.g_false,
444 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
445 p_transaction_temp_id IN NUMBER := 0,
446 p_organization_id IN NUMBER,
447 p_lpn_id IN NUMBER,
448 p_from_subinventory_code IN VARCHAR2,
449 p_from_locator_id IN NUMBER,
450 p_to_subinventory_code IN VARCHAR2,
451 p_to_locator_id IN NUMBER,
452 p_inventory_item_id IN NUMBER := NULL,
453 p_revision IN VARCHAR2 := NULL,
454 p_lot_number IN VARCHAR2 := NULL,
455 p_trx_quantity IN NUMBER := NULL,
456 p_trx_uom IN VARCHAR2 := NULL
457 );
458 --These Procedures are used for storing and deleting reservations
459 --if onhand and availability fails after do_check.
460 PROCEDURE Insert_rsv_temp( p_organization_id NUMBER
461 ,p_inventory_item_id NUMBER
462 ,p_primary_reservation_quantity NUMBER
463 ,p_tree_id NUMBER
464 ,p_reservation_id NUMBER
465 ,x_return_status OUT NOCOPY VARCHAR2
466 ,p_demand_source_line_id NUMBER
467 ,p_demand_source_header_id NUMBER
468 ,p_demand_source_name VARCHAR2);
469 PROCEDURE Do_check_for_commit( p_api_version_number IN NUMBER
470 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
471 , x_return_status OUT NOCOPY VARCHAR2
472 , x_msg_count OUT NOCOPY NUMBER
473 , x_msg_data OUT NOCOPY VARCHAR2
474 ,x_failed_rsv_temp_tbl OUT NOCOPY inv_reservation_global.mtl_failed_rsv_tbl_type);
475
476 PROCEDURE print_rsv_rec(p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type);
477
478
479 PROCEDURE convert_quantity(x_return_status OUT NOCOPY VARCHAR2, px_rsv_rec IN OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type);
480
481 /**** {{ R12 Enhanced reservations code changes }}****/
482 /* This API will take a set of parameters and return the wip entity type and the job type as output. */
483
484 PROCEDURE get_wip_entity_type
485 (
486 p_api_version_number IN NUMBER
487 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
488 , x_return_status OUT NOCOPY VARCHAR2
489 , x_msg_count OUT NOCOPY NUMBER
490 , x_msg_data OUT NOCOPY VARCHAR2
491 , p_organization_id IN NUMBER DEFAULT NULL
492 , p_item_id IN NUMBER DEFAULT NULL
493 , p_source_type_id IN NUMBER DEFAULT INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP
494 , p_source_header_id IN NUMBER
495 , p_source_line_id IN NUMBER
496 , p_source_line_detail IN NUMBER
497 , x_wip_entity_type OUT NOCOPY NUMBER
498 , x_wip_job_type OUT NOCOPY VARCHAR2
499 );
500
501 /*===============================================================================*
502 | Procedure |
503 | update_serial_rsv_quantity |
504 | Description |
505 | This procedure is to update the serial_reservation_quantity in |
506 | mtl_reservations when the serial number's group mark id and reservation_id |
507 | is mark or serial number is unmark |
508 | Input Parameters |
509 | p_reservation_id |
510 | Reservation ID stamps with the serial number |
511 | p_update_serial_qty |
512 | the quantity that needs to update for serial_reservation_quantity in |
513 | mtl_reservations |
514 | if the serial is marked and reserved, the value of p_update_serial_qty is |
515 | positive because the serial_reservation_quantity should be increased. |
516 | if the serial is unmarked and unreserved, the value of p_update_serial_qty |
517 | is negative because the serial_reservation_quantity should be decrased. |
518 *================================================================================*/
519
520 PROCEDURE update_serial_rsv_quantity(
521 x_return_status OUT NOCOPY VARCHAR2
522 ,x_msg_count OUT NOCOPY NUMBER
523 ,x_msg_data OUT NOCOPY VARCHAR2
524 ,p_reservation_id IN NUMBER
525 ,p_update_serial_qty IN NUMBER);
526
527 /*=================================================================================*
528 | Procedure |
529 | is_serial_number_reserved |
530 | Description |
531 | This procedure checks whether a serial or a group of serials passed to |
532 | this API have been reserved or not. This API would return all the |
533 | serials with the reservation ID tied to the serial number |
534 | Input Parameters |
535 | p_api_version_number |
536 | API version number |
537 | p_init_msg_lst |
538 | Whether initialize the error message list or not |
539 | Should be fnd_api.g_false or fnd_api.g_true |
540 | p_serial_number_tbl |
541 | table of serials to check whether the serials have been reserved or not |
542 | Output Parameters |
543 | x_return_status |
544 | return status indicating success, error, unexpected error |
545 | x_msg_count |
546 | number of messages in message list |
547 | x_msg_data |
548 | if the number of messages in message list is 1, contains |
549 | message text |
550 | x_serial_number_tbl |
551 | the table of serials with the reservation ID tied to it |
552 | |
553 *==================================================================================*/
554
555 PROCEDURE is_serial_number_reserved
556 (
557 p_api_version_number IN NUMBER
558 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
559 , x_return_status OUT NOCOPY VARCHAR2
560 , x_msg_count OUT NOCOPY NUMBER
561 , x_msg_data OUT NOCOPY VARCHAR2
562 , p_serial_number_tbl IN inv_reservation_global.serial_number_tbl_type
563 , x_serial_number_tbl OUT NOCOPY inv_reservation_global.rsv_serial_number_table
564 );
565
566 /*=================================================================================*
567 | Procedure |
568 | is_serial_reserved |
569 | Description |
570 | This procedure checks whether a serial or a group of serials passed to |
571 | this API have been reserved or not. This API would return all the |
572 | serials along with the entire reservation tied to the serial number if the |
573 | serial number is reserved |
574 | Input Parameters |
575 | p_api_version_number |
576 | API version number |
577 | p_init_msg_lst |
578 | Whether initialize the error message list or not |
579 | Should be fnd_api.g_false or fnd_api.g_true |
580 | p_serial_number_tbl |
581 | table of serials to check whether the serials have been reserved or not |
582 | Output Parameters |
583 | x_return_status |
584 | return status indicating success, error, unexpected error |
585 | x_msg_count |
586 | number of messages in message list |
587 | x_msg_data |
588 | if the number of messages in message list is 1, contains |
589 | message text |
590 | x_serial_number_tbl |
591 | the table of serials with the reservation ID tied to it |
592 | x_mtl_reservation_tbl |
593 | the table of reservation record with the serial number that is reserved |
594 | |
595 *==================================================================================*/
596
597 PROCEDURE is_serial_reserved
598 (
599 p_api_version_number IN NUMBER
600 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
601 , x_return_status OUT NOCOPY VARCHAR2
602 , x_msg_count OUT NOCOPY NUMBER
603 , x_msg_data OUT NOCOPY VARCHAR2
604 , p_serial_number_tbl IN inv_reservation_global.serial_number_tbl_type
605 , x_serial_number_tbl OUT NOCOPY inv_reservation_global.rsv_serial_number_table
606 , x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
607 );
608
609 PROCEDURE Transfer_Reservation_SubXfer
610 ( p_api_version_number IN NUMBER DEFAULT 1.0
611 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
612 , x_return_status OUT NOCOPY VARCHAR2
613 , x_msg_count OUT NOCOPY NUMBER
614 , x_msg_data OUT NOCOPY VARCHAR2
615 , p_Inventory_Item_Id IN Number
616 , p_Organization_id IN Number
617 , p_original_Reservation_Id IN Number
618 , p_From_Serial_Number IN Varchar2
619 , p_to_SubInventory IN Varchar2
620 , p_To_Locator_Id IN Number
621 , p_to_serial_number IN Varchar2
622 , p_validation_flag IN VARCHAR2
623 , x_to_reservation_id OUT NOCOPY NUMBER
624 );
625
626 /*=================================================================================*
627 | Procedure |
628 | transfer_serial_rsv_in_LPN |
629 | Description |
630 | This procedure is to transfer serial reservation where the serial is in lpn |
631 | and there is no lpn in the reservation. If the p_outermost_lpn_id is passed |
632 | ,then transfer all the serial reservations where the serial is in the inner |
633 | lpn. If p_lpn_id is passed, then transfer the reservation that has any serials |
634 | reserved in the LPN and no LPN is reserved in the same reservation. |
635 | Either p_outermost_lpn_id or p_lpn_id will be populated. |
636 | Input Parameters |
637 | p_organization_id |
638 | Organization id of the reservation to transfer |
639 | p_inventory_item_id |
640 | Invnetory item id of the reservation to transfer |
641 | p_lpn_id |
642 | LPN id to check if any serials reserved in it |
643 | p_outermost_lpn_id |
644 | The outermost lpn to check if any serials reserved in all the lpns in the |
645 | outermost lpn |
646 | Output Parameters |
647 | x_return_status |
648 | return status indicating success, error, unexpected error |
649 | x_msg_count |
650 | number of messages in message list |
651 | x_msg_data |
652 | if the number of messages in message list is 1, contains |
653 | message text |
654 | |
655 *==================================================================================*/
656 PROCEDURE transfer_serial_rsv_in_LPN
657 (
658 x_return_status OUT NOCOPY VARCHAR2
659 , x_msg_count OUT NOCOPY NUMBER
660 , x_msg_data OUT NOCOPY VARCHAR2
661 , p_organization_id IN NUMBER
662 , p_inventory_item_id IN NUMBER DEFAULT NULL
663 , p_lpn_id IN NUMBER
664 , p_outermost_lpn_id IN NUMBER
665 , p_to_subinventory_code IN VARCHAR2
666 , p_to_locator_id IN NUMBER
667 );
668
669 /*** End R12 ***/
670
671 /*=============================================================================
672 |Procedure |
673 | get_demand_reservable_qty |
674 | Description
675 | helper procedure called from update_reservation and
676 | transfer_reservation to get available to reserve qty
677 | for the demand source
678 |Input parameters:
679 |-- all demand information from the from record such as demand type,
680 |header, line and line detail
681 |-- all demand information from the to record such as demand type,
682 |header, line and line detail
683 |-- from primary reservation qty
684 |-- to primary reservation qty
685 |-- to record's item information such as organization id, item id, uom
686 |code, project and task information
687 |
688 |Output Parameters:
689 |x_reservable_qty - The available quantity to reserve for the demand
690 |record currently being reserved
691 x_|qty_available - The available quantity for the document being reserved
692 =============================================================================*/
693
694
695 PROCEDURE get_demand_reservable_qty
696 ( x_return_status OUT NOCOPY VARCHAR2
697 , x_msg_count OUT NOCOPY NUMBER
698 , x_msg_data OUT NOCOPY VARCHAR2
699 , p_fm_demand_source_type_id IN NUMBER
700 , p_fm_demand_source_header_id IN NUMBER
701 , p_fm_demand_source_line_id IN NUMBER
702 , p_fm_demand_source_line_detail IN NUMBER
703 , p_fm_primary_reservation_qty IN NUMBER
704 , p_fm_secondary_reservation_qty IN NUMBER
705 , p_to_demand_source_type_id IN NUMBER
706 , p_to_demand_source_header_id IN NUMBER
707 , p_to_demand_source_line_id IN NUMBER
708 , p_to_demand_source_line_detail IN NUMBER
709 , p_to_primary_reservation_qty IN NUMBER
710 , p_to_organization_id IN NUMBER
711 , p_to_inventory_item_id IN NUMBER
712 , p_to_primary_uom_code IN VARCHAR
713 , p_to_project_id IN NUMBER
714 , p_to_task_id IN NUMBER
715 , x_reservable_qty OUT NOCOPY NUMBER
716 , x_qty_available OUT NOCOPY NUMBER
717 , x_reservable_qty2 OUT NOCOPY NUMBER
718 , x_qty_available2 OUT NOCOPY NUMBER
719 );
720
721
722 /*=============================================================================
723 |Procedure |
724 | get_supply_reservable_qty |
725 | Description
726 | helper procedure called from update_reservation and
727 | transfer_reservation to get available to reserve qty
728 | for the supply source
729 |Input parameters:
730 | all supply information from the from record such as supply type,
731 |header, line and line detail
732 | all supply information from the to record such as supply type,
733 |header, line and line detail
734 |-- from primary reservation qty
735 |-- to primary reservation qty
736 |-- to record's item information such as organization id, item id, uom
737 |code, project and task information
738 |
739 |Output Parameters:
740 |x_reservable_qty - The available quantity to reserve for the supply
741 |record currently being reserved
742 x_|qty_available - The available quantity for the document being reserved
743 =============================================================================*/
744
745 PROCEDURE get_supply_reservable_qty
746 ( x_return_status OUT NOCOPY VARCHAR2
747 , x_msg_count OUT NOCOPY NUMBER
748 , x_msg_data OUT NOCOPY VARCHAR2
749 , p_fm_supply_source_type_id IN NUMBER
750 , p_fm_supply_source_header_id IN NUMBER
751 , p_fm_supply_source_line_id IN NUMBER
752 , p_fm_supply_source_line_detail IN NUMBER
753 , p_fm_primary_reservation_qty IN NUMBER
754 , p_to_supply_source_type_id IN NUMBER
755 , p_to_supply_source_header_id IN NUMBER
756 , p_to_supply_source_line_id IN NUMBER
757 , p_to_supply_source_line_detail IN NUMBER
758 , p_to_primary_reservation_qty IN NUMBER
759 , p_to_organization_id IN NUMBER
760 , p_to_inventory_item_id IN NUMBER
761 , p_to_revision IN VARCHAR2
762 , p_to_lot_number IN VARCHAR2
763 , p_to_subinventory_code IN VARCHAR2
764 , p_to_locator_id IN NUMBER
765 , p_to_lpn_id IN NUMBER
766 , p_to_project_id IN NUMBER
767 , p_to_task_id IN NUMBER
768 , x_reservable_qty OUT NOCOPY NUMBER
769 , x_qty_available OUT NOCOPY NUMBER
770 );
771
772 PROCEDURE get_ship_qty_tolerance
773 (
774 p_api_version_number IN NUMBER
775 , p_init_msg_lst IN VARCHAR2 Default Fnd_API.G_False
776 , x_return_status OUT NOCOPY VARCHAR2
777 , x_msg_count OUT NOCOPY NUMBER
778 , x_msg_data OUT NOCOPY VARCHAR2
779 , p_demand_type_id IN NUMBER
780 , p_demand_header_id IN NUMBER
781 , p_demand_line_id IN NUMBER
782 , x_reservation_margin_above OUT NOCOPY NUMBER -- INVCONV
783 );
784
785 FUNCTION lot_divisible
786 (p_inventory_item_id IN NUMBER
787 , p_organization_id IN NUMBER)
788 RETURN BOOLEAN;
789
790
791 END inv_reservation_pvt;
[Home] [Help]