1 PACKAGE inv_reservation_pub AS
2 /* $Header: INVRSVPS.pls 120.4 2007/12/17 13:23:05 ckrishna ship $ */
3
4 ------------------------------------------------------------------------------
5 -- Note
6 -- APIs in this package conforms to the PLSQL Business Object API Coding
7 -- Standard.
8 ------------------------------------------------------------------------------
9
10 ------------------------------------------------------------------------------
11 -- Please refers to inv_reservation_global package spec for the definitions
12 -- of mtl_reservation_rec_type, mtl_reservation_rec_type and
13 -- serial_number_tbl_type
14 ------------------------------------------------------------------------------
15
16 ------------------------------------------------------------------------------
17 -- Procedures and Functions
18 ------------------------------------------------------------------------------
19 -- Procedure
20 -- create_reservation
21 --
22 -- Description
23 -- Create a material reservation for an item
24 --
25 -- Input Paramters
26 -- p_api_version_number API version number (current version is 1.0)
27 --
28 -- p_init_msg_lst Whether initialize the error message list or
29 -- not.
30 -- Should be fnd_api.g_false or fnd_api.g_true
31 --
32 -- p_rsv_rec Contains info to be used to create the
33 -- reservation
34 --
35 -- p_serial_number Contains serial numbers to be reserved
36 --
37 -- p_partial_reservation_flag If there is not enough quantity, whether or not
38 -- to reserve the amount that is available
39 -- Should be fnd_api.g_false or fnd_api.g_true
40 --
41 -- p_force_reservation_flag Whether or not to reserve without quantity
42 -- check.
43 -- Currently the api (public) will always ignore
44 -- this flag, and always does quantity check.
45 --
46 -- p_validation_flag Whether or not to reserve without validation.
47 -- Currently the api (public) will always ignore
48 -- this flag, and always does validation.
49 -- p_partial_rsv_exists This parameter was added to be passed in case
50 -- a partial reservation already exists.
51 -- If passed as true we will query reservations
52 -- and update them else new reservations are created.
53 --
54 -- Output Parameters
55 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
56 -- = fnd_api.g_ret_sts_exc_error, if an expected
57 -- error occurred
58 -- = fnd_api.g_ret_sts_unexp_error, if
59 -- an unexpected error occurred
60 --
61 -- x_msg_count Number of error message in the error message
62 -- list
63 --
64 -- x_msg_data If the number of error message in the error
65 -- message list is one, the error message
66 -- is in this output parameter
67 --
68 -- x_serial_number The serial numbers actually reserved if
69 -- succeeded
70 --
71 -- x_quantity_reserved The quantity actual reserved if succeeded
72 --
73 -- x_reservation_id The reservation id for the reservation created
74 -- if succeeded
75 -- Example
76 -- The following code creates a reservation of item id 149 in org 207
77 -- for demand source OE, order number 1234567 and line 3, with
78 -- supply from inventory, subinventory Stores. The item is not
79 -- under revision, lot or locator control
80 --
81 -- DECLARE
82 -- l_rsv inv_reservation_global.mtl_reservation_rec_type;
83 -- l_msg_count NUMBER;
84 -- l_msg_data VARCHAR2(240);
85 -- l_rsv_id NUMBER;
86 -- l_dummy_sn inv_reservation_global.serial_number_tbl_type;
87 -- l_status VARCHAR2(1);
88 -- BEGIN
89 -- l_rsv.reservation_id := NULL; -- cannot know
90 -- l_rsv.requirement_date := Sysdate+30;
91 -- l_rsv.organization_id := 207;
92 -- l_rsv.inventory_item_id := 149;
93
94 -- l_rsv.demand_source_type_id :=
95 -- inv_reservation_global.g_source_type_oe; -- order entry
96
97 -- l_rsv.demand_source_name := NULL;
98 -- l_rsv.demand_source_header_id := 1234567; -- oe order number
99 -- l_rsv.demand_source_line_id := 3; -- oe order line number
100 -- l_rsv.primary_uom_code := 'Ea';
101 -- l_rsv.primary_uom_id := NULL;
102 -- l_rsv.reservation_uom_code := NULL;
103 -- l_rsv.reservation_uom_id := NULL;
104 -- l_rsv.reservation_quantity := NULL;
105 -- l_rsv.primary_reservation_quantity := 35;
106 -- l_rsv.autodetail_group_id := NULL;
107 -- l_rsv.external_source_code := NULL;
108 -- l_rsv.external_source_line_id := NULL;
109
110 -- l_rsv.supply_source_type_id :=
111 -- inv_reservation_global.g_source_type_inv;
112
113 -- l_rsv.supply_source_header_id := NULL;
114 -- l_rsv.supply_source_line_id := NULL;
115 -- l_rsv.supply_source_name := NULL;
116 -- l_rsv.supply_source_line_detail := NULL;
117 -- l_rsv.revision := NULL;
118 -- l_rsv.subinventory_code := 'Stores';
119 -- l_rsv.subinventory_id := NULL;
120 -- l_rsv.locator_id := NULL;
121 -- l_rsv.lot_number := NULL;
122 -- l_rsv.lot_number_id := NULL;
123 -- l_rsv.pick_slip_number := NULL;
124 -- l_rsv.lpn_id := NULL;
125 -- l_rsv.attribute_category := NULL;
126 -- l_rsv.attribute1 := NULL;
127 -- l_rsv.attribute2 := NULL;
128 -- l_rsv.attribute3 := NULL;
129 -- l_rsv.attribute4 := NULL;
130 -- l_rsv.attribute5 := NULL;
131 -- l_rsv.attribute6 := NULL;
132 -- l_rsv.attribute7 := NULL;
133 -- l_rsv.attribute8 := NULL;
134 -- l_rsv.attribute9 := NULL;
135 -- l_rsv.attribute10 := NULL;
136 -- l_rsv.attribute11 := NULL;
137 -- l_rsv.attribute12 := NULL;
138 -- l_rsv.attribute13 := NULL;
139 -- l_rsv.attribute14 := NULL;
140 -- l_rsv.attribute15 := NULL;
141 --
142 -- inv_reservation_pub.create_reservation
143 -- (
144 -- p_api_version_number => 1.0
145 -- , p_init_msg_lst => fnd_api.g_ture
146 -- , x_return_status => l_status
147 -- , x_msg_count => l_msg_count
148 -- , x_msg_data => l_msg_data
149 -- , p_rsv_rec => l_rsv
150 -- , p_serial_number => l_dummy_sn
151 -- , x_serial_number => l_dummy_sn
152 -- , p_partial_reservation_flag => fnd_api.g_ture
153 -- , p_force_reservation_flag => fnd_api.g_false
154 -- , p_validation_flag => fnd_api.g_true
155 -- , x_quantity_reserved => l_qty
156 -- , x_reservation_id => l_rsv_id
157 -- );
158
159 -- IF l_status != fnd_api.g_ret_sts_success THEN
160 -- dbms_output.put_line('Quantity reserved: ' || To_char(l_qty));
161 -- ELSE
162 -- IF l_msg_count = 1 THEN
163 -- dbms_output.put_line('Error: '|| l_msg_data);
164 -- ELSE
165 -- FOR l_index IN 1..l_msg_count LOOP
166 -- fnd_msg_pub.get(l_msg_data);
167 -- dbms_output.put_line('Error: '|| l_msg_data);
168 -- END LOOP;
169 -- END IF;
170 -- END IF;
171 -- END;
172 --
173 PROCEDURE create_reservation
174 (
175 p_api_version_number IN NUMBER
176 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
177 , x_return_status OUT NOCOPY VARCHAR2
178 , x_msg_count OUT NOCOPY NUMBER
179 , x_msg_data OUT NOCOPY VARCHAR2
180 , p_rsv_rec
181 IN inv_reservation_global.mtl_reservation_rec_type
182 , p_serial_number
183 IN inv_reservation_global.serial_number_tbl_type
184 , x_serial_number
185 OUT NOCOPY inv_reservation_global.serial_number_tbl_type
186 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
187 , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
188 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
189 , p_over_reservation_flag IN NUMBER DEFAULT 0
190 , x_quantity_reserved OUT NOCOPY NUMBER
191 , x_reservation_id OUT NOCOPY NUMBER
192 , p_partial_rsv_exists IN BOOLEAN DEFAULT FALSE
193 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
194
195 );
196
197 -- INVCONV BEGIN
198 -- Create_Reservation OVERLOAD to incorporate secondary quantities
199 PROCEDURE create_reservation
200 (
201 p_api_version_number IN NUMBER
202 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
203 , x_return_status OUT NOCOPY VARCHAR2
204 , x_msg_count OUT NOCOPY NUMBER
205 , x_msg_data OUT NOCOPY VARCHAR2
206 , p_rsv_rec
207 IN inv_reservation_global.mtl_reservation_rec_type
208 , p_serial_number
209 IN inv_reservation_global.serial_number_tbl_type
210 , x_serial_number
211 OUT NOCOPY inv_reservation_global.serial_number_tbl_type
212 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
213 , p_force_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
214 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
215 , p_over_reservation_flag IN NUMBER DEFAULT 0
216 , x_quantity_reserved OUT NOCOPY NUMBER
217 , x_secondary_quantity_reserved OUT NOCOPY NUMBER
218 , x_reservation_id OUT NOCOPY NUMBER
219 , p_partial_rsv_exists IN BOOLEAN DEFAULT FALSE
220 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
221
222 );
223 -- INVCONV END
224
225 -- Procedure
226 -- update_reservation
227 --
228 -- Description
229 -- Update an existing reservation
230 --
231 -- Input Paramters
232 -- p_api_version_number API version number (current version is 1.0)
233 --
234 -- p_init_msg_lst Whether initialize the error message list or
235 -- not.
236 -- Should be fnd_api.g_false or fnd_api.g_true
237 --
238 -- p_original_rsv_rec Contains info for identifying the existing
239 -- reservation. If reservation id is passed (not
240 -- null and not equals to fnd_api.g_miss_num),
241 -- it is used to identify the existing reservation
242 -- and all other attributes in this record are
243 -- ignored. Otherwise, all attributes with values
244 -- not equals to fnd_api.g_miss_xxx are used to
245 -- identify the existing reservation.
246 --
247 -- p_to_rsv_rec Contains new values of the attributes to be
248 -- updated. If the value of an attribute of the
249 -- existing reservation needs update, the new
250 -- value of the attribute should be assigned
251 -- to the attribute in this record.
252 -- For attributes whose value are not to be
253 -- updated, the values of these attributes
254 -- in this record should be fnd_api.g_miss_xxx.
255 -- Notice that attributes of the record type
256 -- are initialized to fnd_api.g_miss_xxx.
257 -- So if you don't assign a value to an
258 -- attribute in this record, it is defaulted
259 -- to fnd_api.g_miss_xxx.
260 --
261 -- p_original_serial_number Contains serial numbers reserved by
262 -- the existing reservation and to be updated.
263 -- (currently not used)
264 --
265 -- p_to_serial_number Contains new serial numbers to be reserved
266 -- instead.
267 -- (currently not used)
268 -- p_partial_reservation_flag If there is not enough quantity, whether or not
269 -- to reserve the amount that is available
270 -- Should be fnd_api.g_false or fnd_api.g_true
271 --
272 --
273 -- p_validation_flag Whether or not to reserve without validation.
274 -- Currently the api (public) will always ignore
275 -- this flag, and always does validation.
276 --
277 -- p_check_availability This flag will check for availability before
278 -- updating the reservation
279 --
280 -- Output Parameters
281 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
282 -- = fnd_api.g_ret_sts_exc_error, if an expected
283 -- error occurred
284 -- = fnd_api.g_ret_sts_unexp_error, if an
285 -- unexpected error occurred
286 --
287 -- x_msg_count Number of error message in the error
288 -- message list
289 --
290 -- x_msg_data If the number of error message in the
291 -- error message list is one, the error
292 -- message is in this output parameter
293 --
294 --
295 -- This procedure will call the overloaded procedure update_reservation and all
296 -- the processing for update of the reservation will be done in the overlaoded procedure.
297 --
298 --
299
300 PROCEDURE update_reservation
301 (
302 p_api_version_number IN NUMBER
303 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
304 , x_return_status OUT NOCOPY VARCHAR2
305 , x_msg_count OUT NOCOPY NUMBER
306 , x_msg_data OUT NOCOPY VARCHAR2
307 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
308 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
309 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
310 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
311 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
312 , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
313 , p_over_reservation_flag IN NUMBER DEFAULT 0
314 );
315
316
317
318
319 -- Procedure
320 -- update_reservation(Overloaded procedure )
321 --
322 -- Description
323 -- Update an existing reservation (to be called from update_reservation)
327 --
324 --
325 -- Input Paramters
326 -- p_api_version_number API version number (current version is 1.0)
328 -- p_init_msg_lst Whether initialize the error message list or
329 -- not.
330 -- Should be fnd_api.g_false or fnd_api.g_true
331 --
332 -- p_original_rsv_rec Contains info for identifying the existing
333 -- reservation. If reservation id is passed (not
334 -- null and not equals to fnd_api.g_miss_num),
335 -- it is used to identify the existing reservation
336 -- and all other attributes in this record are
337 -- ignored. Otherwise, all attributes with values
338 -- not equals to fnd_api.g_miss_xxx are used to
339 -- identify the existing reservation.
340 --
341 -- p_to_rsv_rec Contains new values of the attributes to be
342 -- updated. If the value of an attribute of the
343 -- existing reservation needs update, the new
344 -- value of the attribute should be assigned
345 -- to the attribute in this record.
346 -- For attributes whose value are not to be
347 -- updated, the values of these attributes
348 -- in this record should be fnd_api.g_miss_xxx.
349 -- Notice that attributes of the record type
350 -- are initialized to fnd_api.g_miss_xxx.
351 -- So if you don't assign a value to an
352 -- attribute in this record, it is defaulted
353 -- to fnd_api.g_miss_xxx.
354 --
355 -- p_original_serial_number Contains serial numbers reserved by
356 -- the existing reservation and to be updated.
357 -- (currently not used)
358 --
359 -- p_to_serial_number Contains new serial numbers to be reserved
360 -- instead.
361 -- (currently not used)
362 -- p_partial_reservation_flag If there is not enough quantity, whether or not
363 -- to reserve the amount that is available
364 -- Should be fnd_api.g_false or fnd_api.g_true
365 --
366 -- p_check_availability This flag will check for availability before
367 -- updating the reservation
368 --
369 -- p_validation_flag Whether or not to reserve without validation.
370 -- Currently the api (public) will always ignore
371 -- this flag, and always does validation.
372 --
373 -- Output Parameters
374 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
375 -- = fnd_api.g_ret_sts_exc_error, if an expected
376 -- error occurred
377 -- = fnd_api.g_ret_sts_unexp_error, if an
378 -- unexpected error occurred
379 --
380 -- x_msg_count Number of error message in the error
381 -- message list
382 --
383 -- x_msg_data If the number of error message in the
384 -- error message list is one, the error
385 -- message is in this output parameter
386 --
387 -- x_quantity_reserved This is a out parameter to return the
388 -- quantity reserved
389 --
390 -- Example
391 -- The following code update a reservation of item 149 in
392 -- org 207, demand_source_type oe, demand_source_header_id 1234567
393 -- , demand_source_line_id 3. The update is to change
394 -- the primary_reservation_quantity to 30, and requirement
395 -- date to today + 60 days.
396 --
397 -- DECLARE
398 -- l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
399 -- l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
400 -- l_msg_count NUMBER;
401 -- l_msg_data VARCHAR2(240);
402 -- l_rsv_id NUMBER;
403 -- l_dummy_sn inv_reservation_global.serial_number_tbl_type;
404 -- l_status VARCHAR2(1);
405 -- l_quantity_reserved NUMBER;
406 -- BEGIN
407 -- -- find the existing reservation
408 -- l_rsv_old.organization_id := 207;
409 -- l_rsv_old.inventory_item_id := 149;
410 -- l_rsv_old.demand_source_type_id :=
411 -- inv_reservation_global.g_source_type_oe; -- order entry
412 --
413 -- l_rsv_old.demand_source_header_id := 1234567; -- oe order number
414 -- l_rsv_old.demand_source_line_id := 3; -- oe order line number
415 --
416 -- -- specify the new values
417 -- l_rsv_new.primary_reservation_quantity := 30;
418 -- l_rsv_new.requirement_date := Sysdate+60;
419 --
420 -- inv_reservation_pub.update_reservation
421 -- (
422 -- p_api_version_number => 1.0
423 -- , p_init_msg_lst => fnd_api.g_ture
424 -- , x_return_status => l_status
425 -- , x_msg_count => l_msg_count
426 -- , x_msg_data => l_msg_data
430 -- , p_original_serial_number => l_dummy_sn -- no serial contorl
427 -- , x_quantity_reserved => l_quantity_reserved
428 -- , p_original_rsv_rec => l_rsv_old
429 -- , p_to_rsv_rec => l_rsv_new
431 -- , p_to_serial_number => l_dummy_sn -- no serial control
432 -- , p_validation_flag => fnd_api.g_true
433 -- , p_partial_reservation_flag => fnd_api.g_galse
434 -- , p_check_availability => fnd_api.g_galse
435 -- );
436 --
437 -- IF l_status != fnd_api.g_ret_sts_success THEN
438 -- dbms_output.put_line('Update Done');
439 -- ELSE
440 -- IF l_msg_count = 1 THEN
441 -- dbms_output.put_line('Error: '|| l_msg_data);
442 -- ELSE
443 -- FOR l_index IN 1..l_msg_count LOOP
444 -- fnd_msg_pub.get(l_msg_data);
445 -- dbms_output.put_line('Error: '|| l_msg_data);
446 -- END LOOP;
447 -- END IF;
448 -- END IF;
449 -- END;
450 --
451 -- INVCONV
452 -- Change signature to incorporate secondary_quantity_reserved
453 PROCEDURE update_reservation
454 (
455 p_api_version_number IN NUMBER
456 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
457 , x_return_status OUT NOCOPY VARCHAR2
458 , x_msg_count OUT NOCOPY NUMBER
459 , x_msg_data OUT NOCOPY VARCHAR2
460 , x_quantity_reserved OUT NOCOPY NUMBER
461 , x_secondary_quantity_reserved OUT NOCOPY NUMBER
462 , p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
463 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
464 , p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
465 , p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
466 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
467 , p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
468 , p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
469 , p_over_reservation_flag IN NUMBER DEFAULT 0
470 );
471
472
473
474
475
476 -- Procedure
477 -- delete_reservation
478 --
479 -- Description
480 -- Delete an existing reservation
481 --
482 -- Input Paramters
483 -- p_api_version_number API version number (current version is 1.0)
484 --
485 -- p_init_msg_lst Whether initialize the error message list or
486 -- not. Should be fnd_api.g_false or
487 -- fnd_api.g_true
488 --
489 -- p_rsv_rec Contains info to be used to identify the
490 -- existing reservation
491 --
492 -- p_serial_number Contains serial numbers reserved by the
493 -- existing reservation
494 --
495 -- Output Parameters
496 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
497 -- = fnd_api.g_ret_sts_exc_error, if an expected
498 -- error occurred
499 -- = fnd_api.g_ret_sts_unexp_error, if an
500 -- unexpected error occurred
501 --
502 -- x_msg_count Number of error message in the error message
503 -- list
504 --
505 -- x_msg_data If the number of error message in the error
506 -- message list is one, the error message is
507 -- in this output parameter
508 --
509 -- Example
510 -- The following code delete a reservation of item id 149 in org 207
511 -- for demand source OE, order number 1234567 and line 3, with
512 -- supply from inventory, subinventory Stores.
513 --
514 -- DECLARE
515 -- l_rsv inv_reservation_global.mtl_reservation_rec_type;
516 -- l_msg_count NUMBER;
517 -- l_msg_data VARCHAR2(240);
518 -- l_rsv_id NUMBER;
519 -- l_dummy_sn inv_reservation_global.serial_number_tbl_type;
520 -- l_status VARCHAR2(1);
521 -- BEGIN
522 -- l_rsv.organization_id := 207;
523 -- l_rsv.inventory_item_id := 149;
524
525 -- l_rsv.demand_source_type_id :=
526 -- inv_reservation_global.g_source_type_oe; -- order entry
527
528 -- l_rsv.demand_source_header_id := 1234567; -- oe order number
529 -- l_rsv.demand_source_line_id := 3; -- oe order line number
530 -- l_rsv.supply_source_type_id :=
531 -- inv_reservation_global.g_source_type_inv;
532 --
533 -- l_rsv.subinventory_code := 'Stores';
534
535 -- inv_reservation_pub.delete_reservation
536 -- (
537 -- p_api_version_number => 1.0
538 -- , p_init_msg_lst => fnd_api.g_ture
539 -- , x_return_status => l_status
540 -- , x_msg_count => l_msg_count
541 -- , x_msg_data => l_msg_data
542 -- , p_rsv_rec => l_rsv
543 -- , p_serial_number => l_dummy_sn
544 -- );
545
546 -- IF l_status != fnd_api.g_ret_sts_success THEN
547 -- dbms_output.put_line('reservation deleted');
551 -- ELSE
548 -- ELSE
549 -- IF l_msg_count = 1 THEN
550 -- dbms_output.put_line('Error: '|| l_msg_data);
552 -- FOR l_index IN 1..l_msg_count LOOP
553 -- fnd_msg_pub.get(l_msg_data);
554 -- dbms_output.put_line('Error: '|| l_msg_data);
555 -- END LOOP;
556 -- END IF;
557 -- END IF;
558 -- END;
559 --
560 PROCEDURE delete_reservation
561 (
562 p_api_version_number IN NUMBER
563 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
564 , x_return_status OUT NOCOPY VARCHAR2
565 , x_msg_count OUT NOCOPY NUMBER
566 , x_msg_data OUT NOCOPY VARCHAR2
567 , p_rsv_rec
568 IN inv_reservation_global.mtl_reservation_rec_type
569 , p_serial_number
570 IN inv_reservation_global.serial_number_tbl_type
571 );
572 --
573 -- Procedure
574 -- relieve_reservation
575 -- Description
576 -- Relieve an existing reservation by the specified amount
577 -- Input Parameters
578 -- p_api_version_number API version number (current version is 1.0)
579 --
580 -- p_init_msg_lst Whether initialize the error message list or
581 -- not. Should be fnd_api.g_false or
582 -- fnd_api.g_true
583 --
584 -- p_rsv_rec Contains info to be used to identify the
585 -- existing reservation
586 --
587 -- p_primary_relieved_quantity Quantity to relieve in primary uom code
588 -- this parameter is required if
589 -- p_relieve_all = fnd_api.g_false
590 --
591 -- p_relieve_all If equals to fnd_api.g_true, the api
592 -- will relieve all quantity of the reservation
593 --
594 -- p_original_serial_number Contains serial numbers reserved by the
595 -- existing reservation
596 --
597 -- Output Parameters
598 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
599 -- = fnd_api.g_ret_sts_exc_error, if an expected
600 -- error occurred
601 -- = fnd_api.g_ret_sts_unexp_error, if an
602 -- unexpected error occurred
603 --
604 -- x_msg_count Number of error message in the error message
605 -- list
606 --
607 -- x_msg_data If the number of error message in the error
608 -- message list is one, the error message is
609 -- in this output parameter
610 --
611 -- x_primary_relieved_quantity quantity relieved by the api in primary uom
612 -- x_primary_remain_quantity the remain quantity of the reservation in
613 -- primary uom
614 PROCEDURE relieve_reservation
615 (
616 p_api_version_number IN NUMBER
617 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
618 , x_return_status OUT NOCOPY VARCHAR2
619 , x_msg_count OUT NOCOPY NUMBER
620 , x_msg_data OUT NOCOPY VARCHAR2
621 , p_rsv_rec
622 IN inv_reservation_global.mtl_reservation_rec_type
623 , p_primary_relieved_quantity IN NUMBER
624 , p_relieve_all IN VARCHAR2 DEFAULT fnd_api.g_true
625 , p_original_serial_number
626 IN inv_reservation_global.serial_number_tbl_type
627 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
628 , x_primary_relieved_quantity OUT NOCOPY NUMBER
629 , x_primary_remain_quantity OUT NOCOPY NUMBER
630 );
631
632 -- INVCONV BEGIN
633 -- OVERLOAD definition - incorporates secondary quantity
634 PROCEDURE relieve_reservation
635 (
636 p_api_version_number IN NUMBER
637 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
638 , x_return_status OUT NOCOPY VARCHAR2
639 , x_msg_count OUT NOCOPY NUMBER
640 , x_msg_data OUT NOCOPY VARCHAR2
641 , p_rsv_rec
642 IN inv_reservation_global.mtl_reservation_rec_type
643 , p_primary_relieved_quantity IN NUMBER
644 , p_secondary_relieved_quantity IN NUMBER
645 , p_relieve_all IN VARCHAR2 DEFAULT fnd_api.g_true
646 , p_original_serial_number
647 IN inv_reservation_global.serial_number_tbl_type
648 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
649 , x_primary_relieved_quantity OUT NOCOPY NUMBER
650 , x_secondary_relieved_quantity OUT NOCOPY NUMBER
651 , x_primary_remain_quantity OUT NOCOPY NUMBER
652 , x_secondary_remain_quantity OUT NOCOPY NUMBER
653 );
654
655 -- INVCONV END
656
657 -- Procedure
658 -- transfer_reservation
659 -- Description
660 -- Transfer an existing reservation from one demand source to another
661 -- or one supply source to another.
662 -- Note
663 -- If the target reservation exists, the transfer quantity is added to the
664 -- quantity of the target reservation, and if the transfer quantity equals
665 -- to all quantity reserved in the existing reservation (the from side of
666 -- the transfer), the existing reservation is deleted since we do not
667 -- keep a reservation of quantity 0. If the target reservation doesn't exist,
671 --
668 -- and not all quantity reserved is transferred, a new reservation is created
669 -- with the transfer quantity, and the transfer quantity is subtracted from
670 -- the existing reservation. The following table is a summary of this logic.
672 -- Condition Action
673 -- ---------------------------- -----------------------------------------
674 -- Transfer_All To_Side_Exists From_Side_Reservation To_Side_Reservation
675 -- ------------ -------------- --------------------- -------------------
676 -- true ture delete quantity added
677 -- false false quantity subtracted created
678 -- true false updated n/a
679 -- false ture quantity subtracted quantity added
680 --
681 -- Input Paramters
682 -- p_api_version_number API version number (current version is 1.0)
683 --
684 -- p_init_msg_lst Whether initialize the error message list or
685 -- not. Should be fnd_api.g_false or
686 -- fnd_api.g_true
687 --
688 -- p_original_rsv_rec Contains info for identifying the existing
689 -- reservation. If reservation id is passed
690 -- (not null and not equals to fnd_api.g_miss_num)
691 -- , it is used to identify the existing
692 -- reservationand all other attributes in this
693 -- ignored. Otherwise, all attributes with values
694 -- record are not equals to fnd_api.g_miss_xxx
695 -- are used to identify the existing reservation.
696 --
697 -- p_to_rsv_rec Contains new values of the attributes for the
698 -- target reservation of the transfer. If the
699 -- value of an attribute of the existing
700 -- reservation is different from the target
701 -- reservation , the new value should be assigned
702 -- to the attribute in this record. For attributes
703 -- whose value are the same, the values of these
704 -- attributes in this record should be
705 -- fnd_api.g_miss_xxx.
706 -- Notice that attributes of the record type are
707 -- initialized to fnd_api.g_miss_xxx. So if you
708 -- don't assign a value to an attribute in this
709 -- record, it is defaulted to fnd_api.g_miss_xxx.
710 -- One important aspect: if you want to transfer
711 -- all quantity reserved, you can leave the
712 -- attribute primary_reservation_quantity
713 -- and reservation_quantity as fnd_api.g_miss_xxx
714 -- and the api will transfer all
715 -- quantity reserved.
716 --
717 -- p_original_serial_number Contains serial numbers reserved by the
718 -- existing reservation and to be transferred.
719 -- (currently not used)
720 --
721 -- p_to_serial_number Contains new serial numbers to reserved
722 -- instead. (currently not used)
723 --
724 -- p_validation_flag Whether or not to reserve without validation.
725 -- Currently the api (public) will always ignore
726 -- this flag, and always does validation.
727 --
728 -- Output Parameters
729 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
730 -- = fnd_api.g_ret_sts_exc_error, if an expected
731 -- error occurred
732 -- = fnd_api.g_ret_sts_unexp_error, if an
733 -- unexpected error occurred
734 --
735 -- x_msg_count Number of error message in the error message
736 -- list
737 --
738 -- x_msg_data If the number of error message in the error
739 -- message list is one, the error message is
740 -- in this output parameter
741 --
742 -- x_reservation_id The reservation id for the target reservation
743 -- of the transfer. If the target reseration
744 -- does not exists before the transfer, and
745 -- all quantity reserved is transferred,
746 -- the reservation id of the target reservation
747 -- is same as that of the existing
748 -- reservation; otherwise, it is different.
749 -- Example
750 -- The following code transfer a reservation of item 149 in
751 -- org 207, demand_source_type oe, demand_source_header_id 1234567
752 -- , demand_source_line_id 3. It is a partial transfer of quantity of 5
753 -- and the new subinventory is FGI
754 --
755 -- DECLARE
756 -- l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
757 -- l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
758 -- l_msg_count NUMBER;
759 -- l_msg_data VARCHAR2(240);
760 -- l_rsv_id NUMBER;
761 -- l_dummy_sn inv_reservation_global.serial_number_tbl_type;
762 -- l_status VARCHAR2(1);
763 -- BEGIN
767 -- l_rsv_old.demand_source_type_id :=
764 -- -- find the existing reservation
765 -- l_rsv_old.organization_id := 207;
766 -- l_rsv_old.inventory_item_id := 149;
768 -- inv_reservation_global.g_source_type_oe; -- order entry
769 --
770 -- l_rsv_old.demand_source_header_id := 1234567; -- oe order number
771 -- l_rsv_old.demand_source_line_id := 3; -- oe order line number
772 --
773 -- -- specify the new values
774 -- l_rsv_new.primary_reservation_quantity := 5;
775 -- l_rsv_new.subinventory_code := 'FGI';
776 --
777 -- inv_reservation_pub.transfer_reservation
778 -- (
779 -- p_api_version_number => 1.0
780 -- , p_init_msg_lst => fnd_api.g_ture
781 -- , x_return_status => l_status
782 -- , x_msg_count => l_msg_count
783 -- , x_msg_data => l_msg_data
784 -- , p_original_rsv_rec => l_rsv_old
785 -- , p_to_rsv_rec => l_rsv_new
786 -- , p_original_serial_number => l_dummy_sn -- no serial contorl
787 -- , p_to_serial_number => l_dummy_sn -- no serial control
788 -- , p_validation_flag => fnd_api.g_true
789 -- , x_reservation_id => l_new_rsv_id
790 -- );
791 --
792 -- IF l_status != fnd_api.g_ret_sts_success THEN
793 -- dbms_output.put_line
794 -- ('the new reservation id is '|| to_char(l_new_rsv_id));
795 -- ELSE
796 -- IF l_msg_count = 1 THEN
797 -- dbms_output.put_line('Error: '|| l_msg_data);
798 -- ELSE
799 -- FOR l_index IN 1..l_msg_count LOOP
800 -- fnd_msg_pub.get(l_msg_data);
801 -- dbms_output.put_line('Error: '|| l_msg_data);
802 -- END LOOP;
803 -- END IF;
804 -- END IF;
805 -- END;
806 --
807 PROCEDURE transfer_reservation
808 (
809 p_api_version_number IN NUMBER
810 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
811 , x_return_status OUT NOCOPY VARCHAR2
812 , x_msg_count OUT NOCOPY NUMBER
813 , x_msg_data OUT NOCOPY VARCHAR2
814 , p_is_transfer_supply IN VARCHAR2 DEFAULT fnd_api.g_true
815 , p_original_rsv_rec
816 IN inv_reservation_global.mtl_reservation_rec_type
817 , p_to_rsv_rec
818 IN inv_reservation_global.mtl_reservation_rec_type
819 , p_original_serial_number
820 IN inv_reservation_global.serial_number_tbl_type
821 , p_to_serial_number
822 IN inv_reservation_global.serial_number_tbl_type
823 , p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
824 , p_over_reservation_flag IN NUMBER DEFAULT 0
825 , x_to_reservation_id OUT NOCOPY NUMBER
826 );
827
828
829 -- Procedure
830 -- query_reservation
831 -- Description
832 -- Query reservations based on specified criteria
833 -- Input Paramters
834 -- p_api_version_number API version number (current version is 1.0)
835 --
836 -- p_init_msg_lst Whether initialize the error message list
837 -- or not. Should be fnd_api.g_false or
838 -- fnd_api.g_true
839 --
840 -- p_query_input Contains info to be used to identify the
841 -- reservations.
842 -- p_lock_records
843 -- fnd_api.g_true or fnd_api.g_false (default).
844 -- Specify whether to lock matching records
845 --
846 -- p_sort_by_req_date
847 -- Specify whether to sort the return
848 -- records by requirement date
849 -- see INVRSVGS.pls for details
850 --
851 -- p_cancel_order_mode
852 -- If OM(OE) intends to cancel order and
853 -- want to query related reservations, they will
854 -- be returned in a specific order
855 --
856 -- Output Parameters
857 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
858 -- = fnd_api.g_ret_sts_exc_error, if an
859 -- expected error occurred
860 -- = fnd_api.g_ret_sts_unexp_error, if an
861 -- unexpected error occurred
862 --
863 -- x_msg_count Number of error message in the error message
864 -- list
865 --
866 -- x_msg_data If the number of error message in the
867 -- error message list is one, the error
868 -- message is in this output parameter
869 --
870 -- x_mtl_reservation_tbl Reservations that match the criteria
871 --
872 -- x_mtl_reservation_tbl_count The Number of records in x_mtl_reservation_tbl
873 --
874 -- x_error_code
875 -- This error code is only meaningful
876 -- if x_return_status equals
877 -- fnd_api.g_ret_sts_error.
878 -- see INVRSVGS.pls for error code definition
879 --
880 -- Example
881 -- The following code query all reservations for of item id 149 in org 207.
882 --
883 -- DECLARE
884 -- l_rsv inv_reservation_global.mtl_reservation_rec_type;
885 -- l_msg_count NUMBER;
886 -- l_msg_data VARCHAR2(240);
887 -- l_rsv_id NUMBER;
888 -- l_status VARCHAR2(1);
889 -- l_rsv_array inv_reservation_global.mtl_reservation_tbl_type;
890 -- l_size NUMBER;
891 -- BEGIN
892 -- l_rsv.organization_id := 207;
893 -- l_rsv.inventory_item_id := 149;
894 --
895 -- inv_reservation_pub.query_reservation
896 -- (
897 -- p_api_version_number => 1.0
898 -- , p_init_msg_lst => fnd_api.g_ture
899 -- , x_return_status => l_status
900 -- , x_msg_count => l_msg_count
901 -- , x_msg_data => l_msg_data
902 -- , p_query_input => l_rsv
903 -- , x_mtl_reservation_tbl => l_rsv_array
904 -- , x_mtl_reservation_tbl_count => l_size
905 -- );
906
907 -- IF l_status != fnd_api.g_ret_sts_success THEN
908 -- dbms_output.put_line
909 -- ('number of reservations found: '||to_char(l_size));
910 -- ELSE
911 -- IF l_msg_count = 1 THEN
912 -- dbms_output.put_line('Error: '|| l_msg_data);
913 -- ELSE
914 -- FOR l_index IN 1..l_msg_count LOOP
915 -- fnd_msg_pub.get(l_msg_data);
916 -- dbms_output.put_line('Error: '|| l_msg_data);
917 -- END LOOP;
918 -- END IF;
919 -- END IF;
920 -- END;
921 --
922 PROCEDURE query_reservation
923 (
924 p_api_version_number IN NUMBER
925 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
926 , x_return_status OUT NOCOPY VARCHAR2
927 , x_msg_count OUT NOCOPY NUMBER
928 , x_msg_data OUT NOCOPY VARCHAR2
929 , p_query_input
930 IN inv_reservation_global.mtl_reservation_rec_type
931 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
932 , p_sort_by_req_date
933 IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
934 , p_cancel_order_mode
935 IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
936 , x_mtl_reservation_tbl
937 OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
938 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
939 , x_error_code OUT NOCOPY NUMBER
940 );
941
942 /*
943 ** ----------------------------------------------------------------------
944 ** For Order Management(OM) use only. Please read below:
945 ** MUST PASS DEMAND SOURCE HEADER ID AND DEMAND SOURCE LINE ID
946 ** ----------------------------------------------------------------------
947 ** This API has been written exclusively for Order Management, who query
948 ** reservations extensively.
949 ** The generic query reservation API, query_reservation(see signature above)
950 ** builds a dynamic SQL to satisfy all callers as it does not know what the
951 ** search criteria is, at design time.
952 ** The dynamic SQL consumes soft parse time, which reduces performance.
953 ** An excessive use of query_reservation contributes to performance
954 ** degradation because of soft parse times.
955 ** Since we know what OM would always use to query reservations
956 ** - demand source header id and demand source line id, a new API
957 ** with static SQL would be be effective, with reduced performance impact.
958 ** ----------------------------------------------------------------------
959 ** Since OM has been using query_reservation before this, the signature of the
960 ** new API below remains the same to cause minimal impact.
961 ** ----------------------------------------------------------------------
962 */
963
964 PROCEDURE query_reservation_om_hdr_line
965 (
966 p_api_version_number IN NUMBER
967 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
968 , x_return_status OUT NOCOPY VARCHAR2
969 , x_msg_count OUT NOCOPY NUMBER
970 , x_msg_data OUT NOCOPY VARCHAR2
971 , p_query_input
972 IN inv_reservation_global.mtl_reservation_rec_type
973 , p_lock_records IN VARCHAR2 DEFAULT fnd_api.g_false
974 , p_sort_by_req_date
975 IN NUMBER DEFAULT inv_reservation_global.g_query_no_sort
976 , p_cancel_order_mode
977 IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
978 , x_mtl_reservation_tbl
979 OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
980 , x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
981 , x_error_code OUT NOCOPY NUMBER
982 );
983
984 END inv_reservation_pub ;
[Home] [Help]