[Home] [Help]
PACKAGE BODY: APPS.INV_TRX_RELIEF_C_PVT
Source
1 PACKAGE BODY inv_trx_relief_c_pvt AS
2 /* $Header: INVRSV8B.pls 120.5 2008/04/18 11:40:09 aambulka ship $*/
3 --
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_TRX_RELIEF_C_PVT';
5 TYPE query_cur_ref_type IS REF CURSOR; --3347075
6 PROCEDURE debug_print(p_message IN VARCHAR2, p_level IN NUMBER := 9)
7 IS
8 --Bug 3559328: Performance bug fix. The fnd_profile.value call was put in
9 --debug_print. So, this gets called everytime we try to print to the debug
10 --file. Removing the call from here.
11 --l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
12 BEGIN
13 -- IF (l_debug = 1) THEN
14 INV_LOG_UTIL.TRACE(p_message, 'INV_TRX_RELIEF_C_PVT', p_level);
15 -- END IF;
16 END debug_print;
17
18 --
19 -- This procedure should be called only by TrxRsvRelief in inldqc.ppc
20 PROCEDURE rsv_relief
21 ( x_return_status OUT nocopy VARCHAR2, -- return status
22 x_msg_count OUT nocopy NUMBER,
23 x_msg_data OUT nocopy VARCHAR2,
24 x_ship_qty OUT nocopy NUMBER, -- shipped quantity
25 x_userline OUT nocopy VARCHAR2, -- user line number
26 x_demand_class OUT nocopy VARCHAR2, -- demand class
27 x_mps_flag OUT nocopy NUMBER, -- mrp installed or not (1 yes, 0 no)
28 p_organization_id IN NUMBER, -- org id
29 p_inventory_item_id IN NUMBER, -- inventory item id
30 p_subinv IN VARCHAR2, -- subinventory
31 p_locator IN NUMBER, -- locator id
32 p_lotnumber IN VARCHAR2, -- lot number
33 p_revision IN VARCHAR2, -- revision
34 p_dsrc_type IN NUMBER, -- demand source type
35 p_header_id IN NUMBER, -- demand source header id
36 p_dsrc_name IN VARCHAR2, -- demand source name
37 p_dsrc_line IN NUMBER, -- demand source line id
38 p_dsrc_delivery IN NUMBER, -- demand source delivery
39 p_qty_at_puom IN NUMBER, -- primary quantity
40 p_lpn_id IN NUMBER
41 )
42 IS
43 l_primary_qty NUMBER;
44 l_reservation_id NUMBER;
45 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
46 l_total_qty_to_relieve NUMBER;
47 l_qty_to_relieve NUMBER;
48 l_qty_reserved NUMBER;
49 l_dummy_serial_numbers inv_reservation_global.serial_number_tbl_type;
50 l_qty_relieved NUMBER;
51 l_remain_qty NUMBER;
52 l_ship_qty NUMBER;
53 l_msg_count NUMBER;
54 l_msg_data VARCHAR2(240);
55 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
56 l_relieve_all VARCHAR2(1);
57 l_api_name CONSTANT VARCHAR2(30) := 'rsv_relief';
58 l_stmt VARCHAR2(10) := '0';
59 -- begin declaration for 3347075
60 l_rsv_cur2 query_cur_ref_type;
61 l_demand_source VARCHAR2(2000);
62 l_miss_char VARCHAR2(1);
63 l_miss_num NUMBER;
64 l_supply_src_type_id NUMBER := 13;
65 -- end declaration for 3347075
66
67 -- INVCONV BEGIN
68 l_secondary_qty_relieved NUMBER; -- INVCONV
69 l_secondary_remain_qty NUMBER; -- INVCONV
70 -- INVCONV END
71
72 CURSOR l_mps_flag_cur IS
73 SELECT 1 FROM dual WHERE exists (SELECT NULL FROM mrp_parameters);
74
75 CURSOR l_oe_cur IS
76 select to_char(line_number), demand_class_code
77 from oe_order_lines_all
78 where line_id = p_dsrc_line;
79
80 /* CURSOR l_rsv_cur2 IS
81 SELECT
82 reservation_id
83 , primary_reservation_quantity - NVL(detailed_quantity,0)
84 FROM mtl_reservations
85 WHERE organization_id = p_organization_id
86 AND inventory_item_id = p_inventory_item_id
87 AND supply_source_type_id = 13 -- Inventory
88 AND ((subinventory_code = p_subinv) OR
89 (subinventory_code IS NULL AND p_subinv IS NULL))
90 AND ((locator_id = p_locator) OR
91 (locator_id IS NULL AND p_locator IS NULL))
92 AND ((lot_number = p_lotnumber) OR
93 (lot_number IS NULL AND p_lotnumber IS NULL))
94 AND ((revision = p_revision) OR
95 (revision IS NULL AND p_revision IS NULL))
96 AND ((lpn_id = p_lpn_id) OR (lpn_id IS NULL))
97 AND demand_source_type_id = p_dsrc_type
98 AND ((demand_source_header_id = p_header_id) OR
99 (demand_source_header_id IS NULL AND (p_header_id = 0 or p_header_id IS NULL)))
100 AND ((demand_source_name = p_dsrc_name) OR
101 (demand_source_name IS NULL AND p_dsrc_name IS NULL))
102 AND ((p_dsrc_type NOT IN (2,8,12))
103 OR (p_dsrc_type IN (2,8,12)
104 AND ((demand_source_line_id = p_dsrc_line) OR
105 (demand_source_line_id IS NULL
106 AND p_dsrc_line IS NULL))
107 AND ((demand_source_delivery = p_dsrc_delivery) OR
108 (demand_source_delivery IS NULL
109 AND p_dsrc_delivery IS NULL))))
110 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
111 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE; */
112 -- commented the above for bug 3347075
113 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
114 BEGIN
115 l_stmt := '1';
116 -- Begin changes for bug 3347075
117 l_miss_char := fnd_api.g_miss_char;
118 l_miss_num := fnd_api.g_miss_num;
119 -- End changes for bug 3347075
120 x_return_status := fnd_api.g_ret_sts_success;
121
122 IF (p_qty_at_puom IS NULL OR p_qty_at_puom < 0) THEN
123 x_return_status := fnd_api.g_ret_sts_success;
124 RETURN;
125 END IF;
126
127 l_stmt := '2';
128
129 SAVEPOINT trx_relieve_sa;
130
131 l_total_qty_to_relieve := p_qty_at_puom;
132
133 l_stmt := '3';
134
135 -- check if MRP is installed (might need to be changed)
136 OPEN l_mps_flag_cur;
137 FETCH l_mps_flag_cur INTO x_mps_flag;
138 IF l_mps_flag_cur%notfound THEN
139 x_mps_flag := 0;
140 END IF;
141 CLOSE l_mps_flag_cur;
142
143 l_stmt := '4';
144
145 -- open the appropriate cursor
146 -- begin changes for bug 3347075
147 -- OPEN l_rsv_cur2;
148 IF (p_dsrc_type IN (2,8,12)) THEN
149 IF p_header_id <> l_miss_num AND p_header_id IS NOT NULL
150 AND p_dsrc_line <> l_miss_num AND p_dsrc_line IS NOT NULL
151 AND p_dsrc_type <> l_miss_num AND p_dsrc_type IS NOT NULL THEN
152
153 OPEN l_rsv_cur2 FOR ' SELECT
154 reservation_id
155 , primary_reservation_quantity - NVL(detailed_quantity,0)
156 FROM mtl_reservations
157 WHERE demand_source_header_id = :demand_source_header_id
158 AND demand_source_line_id = :demand_source_line_id
159 AND demand_source_type_id = :demand_source_type_id
160 AND supply_source_type_id = :supply_source_type_id
161 AND (:demand_source_delivery = :l_miss_num
162 OR :demand_source_delivery IS NULL
163 AND demand_source_delivery IS NULL
164 OR :demand_source_delivery = demand_source_delivery)
165 AND (:organization_id = :l_miss_num
166 OR :organization_id IS NULL
167 AND organization_id IS NULL
168 OR :organization_id = organization_id)
169 AND (:inventory_item_id = :l_miss_num
170 OR :inventory_item_id IS NULL
171 AND inventory_item_id IS NULL
172 OR :inventory_item_id = inventory_item_id)
173 AND (:subinventory_code = :l_miss_char
174 OR :subinventory_code IS NULL
175 AND subinventory_code IS NULL
176 OR :subinventory_code = subinventory_code)
177 AND (:locator_id = :l_miss_num
178 OR :locator_id IS NULL
179 AND locator_id IS NULL
180 OR :locator_id = locator_id)
181 AND (:lot_number = :l_miss_char
182 OR :lot_number IS NULL
183 AND lot_number IS NULL
184 OR :lot_number = lot_number)
185 AND (:revision = :l_miss_char
186 OR :revision IS NULL
187 AND revision IS NULL
188 OR :revision = revision)
189 AND (:lpn_id = :l_miss_num
190 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
191 AND (:demand_source_name = :l_miss_char
192 OR :demand_source_name IS NULL
193 AND demand_source_name IS NULL
194 OR :demand_source_name = demand_source_name)
195 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
196 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
197 USING p_header_id
198 , p_dsrc_line
199 , p_dsrc_type
200 , l_supply_src_type_id
201 , p_dsrc_delivery
202 , l_miss_num
203 , p_dsrc_delivery
204 , p_dsrc_delivery
205 , p_organization_id
206 , l_miss_num
207 , p_organization_id
208 , p_organization_id
209 , p_inventory_item_id
210 , l_miss_num
211 , p_inventory_item_id
212 , p_inventory_item_id
213 , p_subinv
214 , l_miss_char
215 , p_subinv
216 , p_subinv
217 , p_locator
218 , l_miss_num
219 , p_locator
220 , p_locator
221 , p_lotnumber
222 , l_miss_char
223 , p_lotnumber
224 , p_lotnumber
225 , p_revision
226 , l_miss_char
227 , p_revision
228 , p_revision
229 , p_lpn_id
230 , l_miss_num
231 , p_lpn_id
232 , p_dsrc_name
233 , l_miss_char
234 , p_dsrc_name
235 , p_dsrc_name ;
236
237 ELSIF p_organization_id <> l_miss_num AND p_organization_id IS NOT NULL
238 AND p_inventory_item_id <> l_miss_num AND p_inventory_item_id IS NOT NULL THEN
239
240 OPEN l_rsv_cur2 FOR ' SELECT
241 reservation_id
242 , primary_reservation_quantity - NVL(detailed_quantity,0)
243 FROM mtl_reservations
244 WHERE organization_id = :organization_id
245 AND inventory_item_id = :inventory_item_id
246 AND supply_source_type_id = :supply_source_type_id
247 AND (:demand_source_header_id = :l_miss_num
248 OR :demand_source_header_id IS NULL
249 AND demand_source_header_id IS NULL
250 OR :demand_source_header_id = demand_source_header_id)
251 AND (:demand_source_line_id = :l_miss_num
252 OR :demand_source_line_id IS NULL
253 AND demand_source_line_id IS NULL
254 OR :demand_source_line_id = demand_source_line_id)
255 AND (:demand_source_type_id = :l_miss_num
256 OR :demand_source_type_id IS NULL
257 AND demand_source_type_id IS NULL
258 OR :demand_source_type_id = demand_source_type_id)
259 AND (:demand_source_delivery = :l_miss_num
260 OR :demand_source_delivery IS NULL
261 AND demand_source_delivery IS NULL
262 OR :demand_source_delivery = demand_source_delivery)
263 AND (:subinventory_code = :l_miss_char
264 OR :subinventory_code IS NULL
265 AND subinventory_code IS NULL
266 OR :subinventory_code = subinventory_code)
267 AND (:locator_id = :l_miss_num
268 OR :locator_id IS NULL
269 AND locator_id IS NULL
270 OR :locator_id = locator_id)
271 AND (:lot_number = :l_miss_char
272 OR :lot_number IS NULL
273 AND lot_number IS NULL
274 OR :lot_number = lot_number)
275 AND (:revision = :l_miss_char
276 OR :revision IS NULL
277 AND revision IS NULL
278 OR :revision = revision)
279 AND (:lpn_id = :l_miss_num
280 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
281 AND (:demand_source_name = :l_miss_char
282 OR :demand_source_name IS NULL
283 AND demand_source_name IS NULL
284 OR :demand_source_name = demand_source_name)
285 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
286 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
287 USING p_organization_id
288 , p_inventory_item_id
289 , l_supply_src_type_id
290 , p_header_id
291 , l_miss_num
292 , p_header_id
293 , p_header_id
294 , p_dsrc_line
295 , l_miss_num
296 , p_dsrc_line
297 , p_dsrc_line
298 , p_dsrc_type
299 , l_miss_num
300 , p_dsrc_type
301 , p_dsrc_type
302 , p_dsrc_delivery
303 , l_miss_num
304 , p_dsrc_delivery
305 , p_dsrc_delivery
306 , p_subinv
307 , l_miss_char
308 , p_subinv
309 , p_subinv
310 , p_locator
311 , l_miss_num
312 , p_locator
313 , p_locator
314 , p_lotnumber
315 , l_miss_char
316 , p_lotnumber
317 , p_lotnumber
318 , p_revision
319 , l_miss_char
320 , p_revision
321 , p_revision
322 , p_lpn_id
323 , l_miss_num
324 , p_lpn_id
325 , p_dsrc_name
326 , l_miss_char
327 , p_dsrc_name
328 , p_dsrc_name ;
329 ELSE
330 OPEN l_rsv_cur2 FOR ' SELECT
331 reservation_id
332 , primary_reservation_quantity - NVL(detailed_quantity,0)
333 FROM mtl_reservations
334 WHERE (:organization_id = :l_miss_num
335 OR :organization_id IS NULL
336 AND organization_id IS NULL
337 OR :organization_id = organization_id)
338 AND (:inventory_item_id = :l_miss_num
339 OR :inventory_item_id IS NULL
340 AND inventory_item_id IS NULL
341 OR :inventory_item_id = inventory_item_id)
342 AND supply_source_type_id = :supply_source_type_id
343 AND (:demand_source_header_id = :l_miss_num
344 OR :demand_source_header_id IS NULL
345 AND demand_source_header_id IS NULL
346 OR :demand_source_header_id = demand_source_header_id)
347 AND (:demand_source_line_id = :l_miss_num
348 OR :demand_source_line_id IS NULL
349 AND demand_source_line_id IS NULL
350 OR :demand_source_line_id = demand_source_line_id)
351 AND (:demand_source_type_id = :l_miss_num
352 OR :demand_source_type_id IS NULL
353 AND demand_source_type_id IS NULL
354 OR :demand_source_type_id = demand_source_type_id)
355 AND (:demand_source_delivery = :l_miss_num
356 OR :demand_source_delivery IS NULL
357 AND demand_source_delivery IS NULL
358 OR :demand_source_delivery = demand_source_delivery)
359 AND (:subinventory_code = :l_miss_char
360 OR :subinventory_code IS NULL
361 AND subinventory_code IS NULL
362 OR :subinventory_code = subinventory_code)
363 AND (:locator_id = :l_miss_num
364 OR :locator_id IS NULL
365 AND locator_id IS NULL
366 OR :locator_id = locator_id)
367 AND (:lot_number = :l_miss_char
368 OR :lot_number IS NULL
369 AND lot_number IS NULL
370 OR :lot_number = lot_number)
371 AND (:revision = :l_miss_char
372 OR :revision IS NULL
373 AND revision IS NULL
374 OR :revision = revision)
375 AND (:lpn_id = :l_miss_num
376 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
377 AND (:demand_source_name = :l_miss_char
378 OR :demand_source_name IS NULL
379 AND demand_source_name IS NULL
380 OR :demand_source_name = demand_source_name)
381 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
382 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
383 USING p_organization_id
384 , l_miss_num
385 , p_organization_id
386 , p_organization_id
387 , p_inventory_item_id
388 , l_miss_num
389 , p_inventory_item_id
390 , p_inventory_item_id
391 , l_supply_src_type_id
392 , p_header_id
393 , l_miss_num
394 , p_header_id
395 , p_header_id
396 , p_dsrc_line
397 , l_miss_num
398 , p_dsrc_line
399 , p_dsrc_line
400 , p_dsrc_type
401 , l_miss_num
402 , p_dsrc_type
403 , p_dsrc_type
404 , p_dsrc_delivery
405 , l_miss_num
406 , p_dsrc_delivery
407 , p_dsrc_delivery
408 , p_subinv
409 , l_miss_char
410 , p_subinv
411 , p_subinv
412 , p_locator
413 , l_miss_num
414 , p_locator
415 , p_locator
416 , p_lotnumber
417 , l_miss_char
418 , p_lotnumber
419 , p_lotnumber
420 , p_revision
421 , l_miss_char
422 , p_revision
423 , p_revision
424 , p_lpn_id
425 , l_miss_num
426 , p_lpn_id
427 , p_dsrc_name
428 , l_miss_char
429 , p_dsrc_name
430 , p_dsrc_name ;
431
432 END IF;
433 ELSE
434 --Bug 4376838 adding p_header_id <> 0, since for misc. issue p_header_id gets passed as 0.
435 IF p_header_id <> l_miss_num AND p_header_id IS NOT NULL AND p_header_id <> 0
436 AND p_dsrc_type <> l_miss_num AND p_dsrc_type IS NOT NULL THEN
437 /* Bug 6072316 - For the below SQL, modified the where clause for the columns subinventory_code, locator_id,
438 lot_number, revision and the order by clause */
439
440 OPEN l_rsv_cur2 FOR ' SELECT
441 reservation_id
442 , primary_reservation_quantity - NVL(detailed_quantity,0)
443 FROM mtl_reservations
444 WHERE demand_source_header_id = :demand_source_header_id
445 AND demand_source_type_id = :demand_source_type_id
446 AND supply_source_type_id = :supply_source_type_id
447 AND (:organization_id = :l_miss_num
448 OR :organization_id IS NULL
449 AND organization_id IS NULL
450 OR :organization_id = organization_id)
451 AND (:inventory_item_id = :l_miss_num
452 OR :inventory_item_id IS NULl
453 AND inventory_item_id IS NULL
454 OR :inventory_item_id = inventory_item_id)
455 AND (:subinventory_code = :l_miss_char
456 OR :subinventory_code = subinventory_code
457 OR subinventory_code IS NULL)
458 AND (:locator_id = :l_miss_num
459 OR :locator_id = locator_id
460 OR locator_id IS NULL)
461 AND (:lot_number = :l_miss_char
462 OR :lot_number = lot_number
463 OR lot_number IS NULL)
464 AND (:revision = :l_miss_char
465 OR :revision = revision
466 OR revision IS NULL)
467 AND (:lpn_id = :l_miss_num
468 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
469 AND (:demand_source_name = :l_miss_char
470 OR :demand_source_name IS NULL
471 AND demand_source_name IS NULL
472 OR :demand_source_name = demand_source_name)
473 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
474 ORDER BY lpn_id, revision, lot_number, subinventory_code, locator_id FOR UPDATE '
475 USING p_header_id
476 , p_dsrc_type
477 , l_supply_src_type_id
478 , p_organization_id
479 , l_miss_num
480 , p_organization_id
481 , p_organization_id
482 , p_inventory_item_id
483 , l_miss_num
484 , p_inventory_item_id
485 , p_inventory_item_id
486 , p_subinv
487 , l_miss_char
488 , p_subinv
489 , p_locator
490 , l_miss_num
491 , p_locator
492 , p_lotnumber
493 , l_miss_char
494 , p_lotnumber
495 , p_revision
496 , l_miss_char
497 , p_revision
498 , p_lpn_id
499 , l_miss_num
500 , p_lpn_id
501 , p_dsrc_name
502 , l_miss_char
503 , p_dsrc_name
504 , p_dsrc_name ;
505 ELSIF p_organization_id <> l_miss_num AND p_organization_id IS NOT NULL
506 AND p_inventory_item_id <> l_miss_num AND p_inventory_item_id IS NOT NULL THEN
507
508 --Bug 4376838 adding :demand_source_header_id = 0, since for misc. issue p_header_id gets passed as 0.
509 /* Bug 6921615 - For the below SQL, modified the where clause for the columns subinventory_code, locator_id,
510 lot_number, revision and the order by clause */
511 OPEN l_rsv_cur2 FOR ' SELECT
512 reservation_id
513 , primary_reservation_quantity - NVL(detailed_quantity,0)
514 FROM mtl_reservations
515 WHERE organization_id = :organization_id
516 AND inventory_item_id = :inventory_item_id
517 AND supply_source_type_id = :supply_source_type_id
518 AND (:demand_source_header_id = :l_miss_num
519 OR :demand_source_header_id IS NULL OR :demand_source_header_id = 0
520 AND demand_source_header_id IS NULL
521 OR :demand_source_header_id = demand_source_header_id)
522 AND (:demand_source_type_id = :l_miss_num
523 OR :demand_source_type_id IS NULL
524 AND demand_source_type_id IS NULL
525 OR :demand_source_type_id = demand_source_type_id)
526 AND (:subinventory_code = :l_miss_char
527 OR :subinventory_code = subinventory_code
528 OR subinventory_code IS NULL)
529 AND (:locator_id = :l_miss_num
530 OR :locator_id = locator_id
531 OR locator_id IS NULL)
532 AND (:lot_number = :l_miss_char
533 OR :lot_number = lot_number
534 OR lot_number IS NULL)
535 AND (:revision = :l_miss_char
536 OR :revision = revision
537 OR revision IS NULL)
538 AND (:lpn_id = :l_miss_num
539 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
540 AND (:demand_source_name = :l_miss_char
541 OR :demand_source_name IS NULL
542 AND demand_source_name IS NULL
543 OR :demand_source_name = demand_source_name)
544 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
545 ORDER BY lpn_id, revision, lot_number, subinventory_code, locator_id FOR UPDATE '
546 USING p_organization_id
547 , p_inventory_item_id
548 , l_supply_src_type_id
549 , p_header_id
550 , l_miss_num
551 , p_header_id
552 , p_header_id
553 , p_header_id --Added for bug 4376838
554 , p_dsrc_type
555 , l_miss_num
556 , p_dsrc_type
557 , p_dsrc_type
558 , p_subinv
559 , l_miss_char
560 , p_subinv
561 , p_locator
562 , l_miss_num
563 , p_locator
564 , p_lotnumber
565 , l_miss_char
566 , p_lotnumber
567 , p_revision
568 , l_miss_char
569 , p_revision
570 , p_lpn_id
571 , l_miss_num
572 , p_lpn_id
573 , p_dsrc_name
574 , l_miss_char
575 , p_dsrc_name
576 , p_dsrc_name ;
577 ELSE
578 --Bug 4376838 adding :demand_source_header_id = 0, since for misc. issue p_header_id gets passed as 0.
579 OPEN l_rsv_cur2 FOR ' SELECT
580 reservation_id
581 , primary_reservation_quantity - NVL(detailed_quantity,0)
582 FROM mtl_reservations
583 WHERE supply_source_type_id = :supply_source_type_id
584 AND (:demand_source_header_id = :l_miss_num
585 OR :demand_source_header_id IS NULL OR :demand_source_header_id = 0
586 AND demand_source_header_id IS NULL
587 OR :demand_source_header_id = demand_source_header_id)
588 AND (:demand_source_type_id = :l_miss_num
589 OR :demand_source_type_id IS NULL
590 AND demand_source_type_id IS NULL
591 OR :demand_source_type_id = demand_source_type_id)
592 AND (:organization_id = :l_miss_num
593 OR :organization_id IS NULL
594 AND organization_id IS NULL
595 OR :organization_id = organization_id)
596 AND (:inventory_item_id = :l_miss_num
597 OR :inventory_item_id IS NULl
598 AND inventory_item_id IS NULL
599 OR :inventory_item_id = inventory_item_id)
600 AND (:subinventory_code = :l_miss_char
601 OR :subinventory_code IS NULL
602 AND subinventory_code IS NULL
603 OR :subinventory_code = subinventory_code)
604 AND (:locator_id = :l_miss_num
605 OR :locator_id IS NULL
606 AND locator_id IS NULL
607 OR :locator_id = locator_id)
608 AND (:lot_number = :l_miss_char
609 OR :lot_number IS NULL
610 AND lot_number IS NULL
611 OR :lot_number = lot_number)
612 AND (:revision = :l_miss_char
613 OR :revision IS NULL
614 AND revision IS NULL
615 OR :revision = revision)
616 AND (:lpn_id = :l_miss_num
617 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
618 AND (:demand_source_name = :l_miss_char
619 OR :demand_source_name IS NULL
620 AND demand_source_name IS NULL
621 OR :demand_source_name = demand_source_name)
622 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
623 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
624 USING l_supply_src_type_id
625 , p_header_id
626 , l_miss_num
627 , p_header_id
628 , p_header_id
629 , p_header_id --Added for Bug 4376838
630 , p_dsrc_type
631 , l_miss_num
632 , p_dsrc_type
633 , p_dsrc_type
634 , p_organization_id
635 , l_miss_num
636 , p_organization_id
637 , p_organization_id
638 , p_inventory_item_id
639 , l_miss_num
640 , p_inventory_item_id
641 , p_inventory_item_id
642 , p_subinv
643 , l_miss_char
644 , p_subinv
645 , p_subinv
646 , p_locator
647 , l_miss_num
648 , p_locator
649 , p_locator
650 , p_lotnumber
651 , l_miss_char
652 , p_lotnumber
653 , p_lotnumber
654 , p_revision
655 , l_miss_char
656 , p_revision
657 , p_revision
658 , p_lpn_id
659 , l_miss_num
660 , p_lpn_id
661 , p_dsrc_name
662 , l_miss_char
663 , p_dsrc_name
664 , p_dsrc_name ;
665 END IF;
666 END IF;
667 -- end changes for bug 3347075
668 l_stmt := '5';
669
670 -- Added debug messages
671 IF (l_debug = 1) THEN
672 debug_print('l total qty to relieve ' || l_total_qty_to_relieve);
673 END IF;
674 -- fetch reservation records and do relief
675 WHILE (l_total_qty_to_relieve is not null AND
676 l_total_qty_to_relieve > 0) LOOP
677
678 l_stmt := '6';
679
680 FETCH l_rsv_cur2 INTO
681 l_reservation_id,
682 l_qty_reserved;
683 IF l_rsv_cur2%notfound THEN
684 l_reservation_id := NULL;
685 END IF;
686
687 l_stmt := '9';
688
689 IF (l_debug = 1) THEN
690 debug_print('Inside l_rsv_cur2 cursor');
691 debug_print(' reservation id ' ||l_reservation_id || ' qty reserved = primary - detailed ' || l_qty_reserved);
692 END IF;
693
694 -- exit the loop if no more reservation to relieve
695 IF l_reservation_id IS NULL THEN
696 EXIT;
697 END IF;
698
699 l_stmt := '10';
700
701 -- call reservation api to relieve the reservation
702 l_rsv_rec.reservation_id := l_reservation_id;
703
704 l_stmt := '11';
705
706 IF l_total_qty_to_relieve > l_qty_reserved THEN
707 l_qty_to_relieve := l_qty_reserved;
708 ELSE
709 l_qty_to_relieve := l_total_qty_to_relieve;
710 END IF;
711
712 l_stmt := '12';
713
714 IF l_qty_to_relieve = l_qty_reserved THEN
715 l_relieve_all := fnd_api.g_true;
716 IF (l_debug = 1) THEN
717 debug_print(' relieve all');
718 END IF;
719 ELSE
720 l_relieve_all := fnd_api.g_false;
721 IF (l_debug = 1) THEN
722 debug_print('dont relieve all');
723 END IF;
724 END IF;
725
726 l_stmt := '13';
727 IF (l_debug = 1) THEN
728 debug_print(' before calling relieve rsv. Qty to relieve
729 = ' || l_qty_to_relieve || ' relieve all ' || l_relieve_all);
730 END IF;
731
732 inv_reservation_pvt.relieve_reservation
733 (p_api_version_number => 1.0,
734 p_init_msg_lst => fnd_api.g_false,
735 x_return_status => l_return_status,
736 x_msg_count => l_msg_count,
737 x_msg_data => l_msg_data,
738 p_rsv_rec => l_rsv_rec,
739 p_primary_relieved_quantity => l_qty_to_relieve,
740 p_secondary_relieved_quantity => NULL, -- INVCONV
741 p_relieve_all => l_relieve_all,
742 p_original_serial_number => l_dummy_serial_numbers,
743 p_validation_flag => fnd_api.g_true,
744 x_primary_relieved_quantity => l_qty_relieved,
745 x_secondary_relieved_quantity => l_secondary_qty_relieved, -- INVCONV
746 x_primary_remain_quantity => l_remain_qty,
747 x_secondary_remain_quantity => l_secondary_remain_qty -- INVCONV
748 );
749
750 IF l_return_status = fnd_api.g_ret_sts_error THEN
751 RAISE fnd_api.g_exc_error;
752 END IF ;
753
754 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
755 RAISE fnd_api.g_exc_unexpected_error;
756 END IF;
757
758 l_stmt := '14';
759
760 IF (l_debug = 1) THEN
761 debug_print(' after calling relieve rsv. Qty relieved = ' ||
762 l_qty_relieved || ' primary remaining qty = ' ||
763 l_remain_qty);
764 END IF;
765
766 l_total_qty_to_relieve := l_total_qty_to_relieve - l_qty_relieved;
767
768 IF (l_debug = 1) THEN
769 debug_print (' total qty TO relieve ' || l_total_qty_to_relieve);
770 END IF;
771 END LOOP;
772
773 if (p_dsrc_type in (inv_reservation_global.g_source_type_oe,
774 inv_reservation_global.g_source_type_internal_ord)) then
775 l_stmt := '15';
776
777 open l_oe_cur ;
778 fetch l_oe_cur into
779 x_userline,
780 x_demand_class;
781 end if;
782
783 l_stmt := '16';
784
785 -- BUG 2666911. From now on, the x_ship_qty will be used to pass the
786 -- actual relieved quantity. Change has been made in the relieve MRP API
787 -- to check for the ship quantity and call the MPR relief interface
788 -- whether the reservation quantity is relieved or not.
789 -- The x_ship_qty will be henceforth be used to compare the actual ship
790 -- quantity with the relieved quantity to see if the quantity tree
791 -- validation has to be done or not.
792
793 x_ship_qty := p_qty_at_puom - l_total_qty_to_relieve;
794
795 IF (l_debug = 1) THEN
796 debug_print (' p_qty_at_uom ' || p_qty_at_puom || ' L-total-qty-to-relieve ' || l_total_qty_to_relieve);
797 debug_print (' Ship qty after calling l_oe_cur ' || x_ship_qty);
798 END IF;
799
800 l_stmt := '17';
801
802 x_return_status := l_return_status;
803
804 EXCEPTION
805 WHEN fnd_api.g_exc_error THEN
806 ROLLBACK TO trx_relieve_sa;
807 x_return_status := fnd_api.g_ret_sts_error;
808
809 l_stmt := 'Stmt' || l_stmt;
810 fnd_message.set_name('INV', 'INV-Request failed');
811 fnd_message.set_token('ENTITY',l_api_name);
812 fnd_message.set_token('ERRORCODE',l_stmt);
813 fnd_msg_pub.add;
814
815 -- Get message count and data
816 fnd_msg_pub.count_and_get
817 ( p_count => x_msg_count
818 , p_data => x_msg_data
819 );
820 WHEN fnd_api.g_exc_unexpected_error THEN
821 ROLLBACK TO trx_relieve_sa;
822 x_return_status := fnd_api.g_ret_sts_unexp_error ;
823
824 l_stmt := 'Stmt' || l_stmt;
825 fnd_message.set_name('INV', 'INV-Request failed');
826 fnd_message.set_token('ENTITY',l_api_name);
827 fnd_message.set_token('ERRORCODE',l_stmt);
828 fnd_msg_pub.add;
829
830 -- Get message count and data
831 fnd_msg_pub.count_and_get
832 ( p_count => x_msg_count
833 , p_data => x_msg_data
834 );
835 WHEN OTHERS THEN
836 ROLLBACK TO trx_relieve_sa;
837 x_return_status := fnd_api.g_ret_sts_unexp_error ;
838
839 l_stmt := 'Stmt' || l_stmt;
840 fnd_message.set_name('INV', 'INV-Request failed');
841 fnd_message.set_token('ENTITY',l_api_name);
842 fnd_message.set_token('ERRORCODE',l_stmt);
843 fnd_msg_pub.add;
844
845 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
846 THEN
847 fnd_msg_pub.add_exc_msg
848 ( g_pkg_name
849 , l_api_name
850 );
851 END IF;
852 -- Get message count and data
853 fnd_msg_pub.count_and_get
854 ( p_count => x_msg_count
855 , p_data => x_msg_data
856 );
857
858 END rsv_relief;
859
860 -- INVCONV NOTE
861 -- This is an overload of rsv_relief to handle input/output of secondary quantities
862 PROCEDURE rsv_relief
863 ( x_return_status OUT nocopy VARCHAR2, -- return status
864 x_msg_count OUT nocopy NUMBER,
865 x_msg_data OUT nocopy VARCHAR2,
866 x_ship_qty OUT nocopy NUMBER, -- shipped quantity
867 x_secondary_ship_qty OUT nocopy NUMBER, -- secondary shipped quantity INVCONV
868 x_userline OUT nocopy VARCHAR2, -- user line number
869 x_demand_class OUT nocopy VARCHAR2, -- demand class
870 x_mps_flag OUT nocopy NUMBER, -- mrp installed or not (1 yes, 0 no)
871 p_organization_id IN NUMBER, -- org id
872 p_inventory_item_id IN NUMBER, -- inventory item id
873 p_subinv IN VARCHAR2, -- subinventory
874 p_locator IN NUMBER, -- locator id
875 p_lotnumber IN VARCHAR2, -- lot number
876 p_revision IN VARCHAR2, -- revision
877 p_dsrc_type IN NUMBER, -- demand source type
878 p_header_id IN NUMBER, -- demand source header id
879 p_dsrc_name IN VARCHAR2, -- demand source name
880 p_dsrc_line IN NUMBER, -- demand source line id
881 p_dsrc_delivery IN NUMBER, -- demand source delivery
882 p_qty_at_puom IN NUMBER, -- primary quantity
883 p_qty_at_suom IN NUMBER, -- secondary quantity INVCONV
884 p_lpn_id IN NUMBER,
885 p_transaction_id IN NUMBER DEFAULT NULL -- Bug 3517647: Passing transaction id
886 )
887 IS
888 l_primary_qty NUMBER;
889 l_reservation_id NUMBER;
890 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
891 l_total_qty_to_relieve NUMBER;
892 l_total_secondary_to_relieve NUMBER; -- INVCONV
893 l_qty_to_relieve NUMBER;
894 l_secondary_to_relieve NUMBER; -- INVCONV
895 l_qty_reserved NUMBER;
896 l_secondary_qty_reserved NUMBER; -- INVCONV
897 l_original_serial_numbers inv_reservation_global.serial_number_tbl_type;
898 l_qty_relieved NUMBER;
899 l_secondary_qty_relieved NUMBER; -- INVCONV
900 l_remain_qty NUMBER;
901 l_secondary_remain_qty NUMBER; -- INVCONV
902 l_ship_qty NUMBER;
903 l_msg_count NUMBER;
904 l_msg_data VARCHAR2(240);
905 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
906 l_relieve_all VARCHAR2(1);
907 l_api_name CONSTANT VARCHAR2(30) := 'rsv_relief';
908 l_stmt VARCHAR2(10) := '0';
909 -- begin declaration for 3347075
910 l_rsv_cur2 query_cur_ref_type;
911 l_demand_source VARCHAR2(2000);
912 l_miss_char VARCHAR2(1);
913 l_miss_num NUMBER;
914 l_supply_src_type_id NUMBER := 13;
915 -- end declaration for 3347075
916 l_tracking_quantity_ind VARCHAR2(30); -- INVCONV
917 l_serial_number_table inv_reservation_global.serial_number_tbl_type;
918
919 CURSOR l_mps_flag_cur IS
920 SELECT 1 FROM dual WHERE exists (SELECT NULL FROM mrp_parameters);
921
922 CURSOR l_oe_cur IS
923 select to_char(line_number), demand_class_code
924 from oe_order_lines_all
925 where line_id = p_dsrc_line;
926
927 -- INVCONV BEGIN
928 CURSOR l_tracking_qty_cur IS
929 select tracking_quantity_ind
930 from mtl_system_items
931 where organization_id = p_organization_id and inventory_item_id = p_inventory_item_id;
932 -- INVCONV END
933
934 /* CURSOR l_rsv_cur2 IS
935 SELECT
936 reservation_id
937 , primary_reservation_quantity - NVL(detailed_quantity,0)
938 FROM mtl_reservations
939 WHERE organization_id = p_organization_id
940 AND inventory_item_id = p_inventory_item_id
941 AND supply_source_type_id = 13 -- Inventory
942 AND ((subinventory_code = p_subinv) OR
943 (subinventory_code IS NULL AND p_subinv IS NULL))
944 AND ((locator_id = p_locator) OR
945 (locator_id IS NULL AND p_locator IS NULL))
946 AND ((lot_number = p_lotnumber) OR
947 (lot_number IS NULL AND p_lotnumber IS NULL))
948 AND ((revision = p_revision) OR
949 (revision IS NULL AND p_revision IS NULL))
950 AND ((lpn_id = p_lpn_id) OR (lpn_id IS NULL))
951 AND demand_source_type_id = p_dsrc_type
952 AND ((demand_source_header_id = p_header_id) OR
953 (demand_source_header_id IS NULL AND (p_header_id = 0 or p_header_id IS NULL)))
954 AND ((demand_source_name = p_dsrc_name) OR
955 (demand_source_name IS NULL AND p_dsrc_name IS NULL))
956 AND ((p_dsrc_type NOT IN (2,8,12))
957 OR (p_dsrc_type IN (2,8,12)
958 AND ((demand_source_line_id = p_dsrc_line) OR
959 (demand_source_line_id IS NULL
960 AND p_dsrc_line IS NULL))
961 AND ((demand_source_delivery = p_dsrc_delivery) OR
962 (demand_source_delivery IS NULL
963 AND p_dsrc_delivery IS NULL))))
964 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
965 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE; */
966 -- commented the above for bug 3347075
967 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
968 BEGIN
969 l_stmt := '1';
970 -- Begin changes for bug 3347075
971 l_miss_char := fnd_api.g_miss_char;
972 l_miss_num := fnd_api.g_miss_num;
973 -- End changes for bug 3347075
974 x_return_status := fnd_api.g_ret_sts_success;
975
976 IF (p_qty_at_puom IS NULL OR p_qty_at_puom < 0) THEN
977 x_return_status := fnd_api.g_ret_sts_success;
978 RETURN;
979 END IF;
980
981 l_stmt := '2';
982
983 SAVEPOINT trx_relieve_sa;
984
985 -- INVCONV BEGIN
986 OPEN l_tracking_qty_cur;
987 FETCH l_tracking_qty_cur INTO l_tracking_quantity_ind;
988 IF l_tracking_qty_cur%notfound THEN
989 RAISE fnd_api.g_exc_error;
990 END IF;
991 -- INVCONV END
992
993 l_total_qty_to_relieve := p_qty_at_puom;
994
995 -- INVCONV BEGIN
996 IF l_tracking_quantity_ind = 'PS' THEN
997 l_total_secondary_to_relieve := p_qty_at_suom; -- INVCONV
998 END IF;
999 -- INVCONV END
1000
1001 l_stmt := '3';
1002
1003 -- check if MRP is installed (might need to be changed)
1004 OPEN l_mps_flag_cur;
1005 FETCH l_mps_flag_cur INTO x_mps_flag;
1006 IF l_mps_flag_cur%notfound THEN
1007 x_mps_flag := 0;
1008 END IF;
1009 CLOSE l_mps_flag_cur;
1010
1011 l_stmt := '4';
1012
1013
1014 -- open the appropriate cursor
1015 -- begin changes for bug 3347075
1016 -- OPEN l_rsv_cur2;
1017 -- Bug 4764790: Need to relieve for CMRO jobs based on the
1018 -- relieve reservations flag in MMTT
1019
1020 IF (p_dsrc_type IN (2,8,12)) THEN
1021 IF p_header_id <> l_miss_num AND p_header_id IS NOT NULL
1022 AND p_dsrc_line <> l_miss_num AND p_dsrc_line IS NOT NULL
1023 AND p_dsrc_type <> l_miss_num AND p_dsrc_type IS NOT NULL THEN
1024
1025 OPEN l_rsv_cur2 FOR ' SELECT
1026 reservation_id
1027 , primary_reservation_quantity - NVL(detailed_quantity,0)
1028 , NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
1029 FROM mtl_reservations
1030 WHERE demand_source_header_id = :demand_source_header_id
1031 AND demand_source_line_id = :demand_source_line_id
1032 AND demand_source_type_id = :demand_source_type_id
1033 AND supply_source_type_id = :supply_source_type_id
1034 AND (:demand_source_delivery = :l_miss_num
1035 OR :demand_source_delivery IS NULL
1036 AND demand_source_delivery IS NULL
1037 OR :demand_source_delivery = demand_source_delivery)
1038 AND (:organization_id = :l_miss_num
1039 OR :organization_id IS NULL
1040 AND organization_id IS NULL
1041 OR :organization_id = organization_id)
1042 AND (:inventory_item_id = :l_miss_num
1043 OR :inventory_item_id IS NULL
1044 AND inventory_item_id IS NULL
1045 OR :inventory_item_id = inventory_item_id)
1046 AND (:subinventory_code = :l_miss_char
1047 OR :subinventory_code IS NULL
1048 AND subinventory_code IS NULL
1049 OR :subinventory_code = subinventory_code)
1050 AND (:locator_id = :l_miss_num
1051 OR :locator_id IS NULL
1052 AND locator_id IS NULL
1053 OR :locator_id = locator_id)
1054 AND (:lot_number = :l_miss_char
1055 OR :lot_number IS NULL
1056 AND lot_number IS NULL
1057 OR :lot_number = lot_number)
1058 AND (:revision = :l_miss_char
1059 OR :revision IS NULL
1060 AND revision IS NULL
1061 OR :revision = revision)
1062 AND (:lpn_id = :l_miss_num
1063 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
1064 AND (:demand_source_name = :l_miss_char
1065 OR :demand_source_name IS NULL
1066 AND demand_source_name IS NULL
1067 OR :demand_source_name = demand_source_name)
1068 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
1069 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
1070 USING p_header_id
1071 , p_dsrc_line
1072 , p_dsrc_type
1073 , l_supply_src_type_id
1074 , p_dsrc_delivery
1075 , l_miss_num
1076 , p_dsrc_delivery
1077 , p_dsrc_delivery
1078 , p_organization_id
1079 , l_miss_num
1080 , p_organization_id
1081 , p_organization_id
1082 , p_inventory_item_id
1083 , l_miss_num
1084 , p_inventory_item_id
1085 , p_inventory_item_id
1086 , p_subinv
1087 , l_miss_char
1088 , p_subinv
1089 , p_subinv
1090 , p_locator
1091 , l_miss_num
1092 , p_locator
1093 , p_locator
1094 , p_lotnumber
1095 , l_miss_char
1096 , p_lotnumber
1097 , p_lotnumber
1098 , p_revision
1099 , l_miss_char
1100 , p_revision
1101 , p_revision
1102 , p_lpn_id
1103 , l_miss_num
1104 , p_lpn_id
1105 , p_dsrc_name
1106 , l_miss_char
1107 , p_dsrc_name
1108 , p_dsrc_name ;
1109
1110 ELSIF p_organization_id <> l_miss_num AND p_organization_id IS NOT NULL
1111 AND p_inventory_item_id <> l_miss_num AND p_inventory_item_id IS NOT NULL THEN
1112
1113 OPEN l_rsv_cur2 FOR ' SELECT
1114 reservation_id
1115 , primary_reservation_quantity - NVL(detailed_quantity,0)
1116 , NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
1117 FROM mtl_reservations
1118 WHERE organization_id = :organization_id
1119 AND inventory_item_id = :inventory_item_id
1120 AND supply_source_type_id = :supply_source_type_id
1121 AND (:demand_source_header_id = :l_miss_num
1122 OR :demand_source_header_id IS NULL
1123 AND demand_source_header_id IS NULL
1124 OR :demand_source_header_id = demand_source_header_id)
1125 AND (:demand_source_line_id = :l_miss_num
1126 OR :demand_source_line_id IS NULL
1127 AND demand_source_line_id IS NULL
1128 OR :demand_source_line_id = demand_source_line_id)
1129 AND (:demand_source_type_id = :l_miss_num
1130 OR :demand_source_type_id IS NULL
1131 AND demand_source_type_id IS NULL
1132 OR :demand_source_type_id = demand_source_type_id)
1133 AND (:demand_source_delivery = :l_miss_num
1134 OR :demand_source_delivery IS NULL
1135 AND demand_source_delivery IS NULL
1136 OR :demand_source_delivery = demand_source_delivery)
1137 AND (:subinventory_code = :l_miss_char
1138 OR :subinventory_code IS NULL
1139 AND subinventory_code IS NULL
1140 OR :subinventory_code = subinventory_code)
1141 AND (:locator_id = :l_miss_num
1142 OR :locator_id IS NULL
1143 AND locator_id IS NULL
1144 OR :locator_id = locator_id)
1145 AND (:lot_number = :l_miss_char
1146 OR :lot_number IS NULL
1147 AND lot_number IS NULL
1148 OR :lot_number = lot_number)
1149 AND (:revision = :l_miss_char
1150 OR :revision IS NULL
1151 AND revision IS NULL
1152 OR :revision = revision)
1153 AND (:lpn_id = :l_miss_num
1154 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
1155 AND (:demand_source_name = :l_miss_char
1156 OR :demand_source_name IS NULL
1157 AND demand_source_name IS NULL
1158 OR :demand_source_name = demand_source_name)
1159 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
1160 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
1161 USING p_organization_id
1162 , p_inventory_item_id
1163 , l_supply_src_type_id
1164 , p_header_id
1165 , l_miss_num
1166 , p_header_id
1167 , p_header_id
1168 , p_dsrc_line
1169 , l_miss_num
1170 , p_dsrc_line
1171 , p_dsrc_line
1172 , p_dsrc_type
1173 , l_miss_num
1174 , p_dsrc_type
1175 , p_dsrc_type
1176 , p_dsrc_delivery
1177 , l_miss_num
1178 , p_dsrc_delivery
1179 , p_dsrc_delivery
1180 , p_subinv
1181 , l_miss_char
1182 , p_subinv
1183 , p_subinv
1184 , p_locator
1185 , l_miss_num
1186 , p_locator
1187 , p_locator
1188 , p_lotnumber
1189 , l_miss_char
1190 , p_lotnumber
1191 , p_lotnumber
1192 , p_revision
1193 , l_miss_char
1194 , p_revision
1195 , p_revision
1196 , p_lpn_id
1197 , l_miss_num
1198 , p_lpn_id
1199 , p_dsrc_name
1200 , l_miss_char
1201 , p_dsrc_name
1202 , p_dsrc_name ;
1203 ELSE
1204 OPEN l_rsv_cur2 FOR ' SELECT
1205 reservation_id
1206 , primary_reservation_quantity - NVL(detailed_quantity,0)
1207 , NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
1208 FROM mtl_reservations
1209 WHERE (:organization_id = :l_miss_num
1210 OR :organization_id IS NULL
1211 AND organization_id IS NULL
1212 OR :organization_id = organization_id)
1213 AND (:inventory_item_id = :l_miss_num
1214 OR :inventory_item_id IS NULL
1215 AND inventory_item_id IS NULL
1216 OR :inventory_item_id = inventory_item_id)
1217 AND supply_source_type_id = :supply_source_type_id
1218 AND (:demand_source_header_id = :l_miss_num
1219 OR :demand_source_header_id IS NULL
1220 AND demand_source_header_id IS NULL
1221 OR :demand_source_header_id = demand_source_header_id)
1222 AND (:demand_source_line_id = :l_miss_num
1223 OR :demand_source_line_id IS NULL
1224 AND demand_source_line_id IS NULL
1225 OR :demand_source_line_id = demand_source_line_id)
1226 AND (:demand_source_type_id = :l_miss_num
1227 OR :demand_source_type_id IS NULL
1228 AND demand_source_type_id IS NULL
1229 OR :demand_source_type_id = demand_source_type_id)
1230 AND (:demand_source_delivery = :l_miss_num
1231 OR :demand_source_delivery IS NULL
1232 AND demand_source_delivery IS NULL
1233 OR :demand_source_delivery = demand_source_delivery)
1234 AND (:subinventory_code = :l_miss_char
1235 OR :subinventory_code IS NULL
1236 AND subinventory_code IS NULL
1237 OR :subinventory_code = subinventory_code)
1238 AND (:locator_id = :l_miss_num
1239 OR :locator_id IS NULL
1240 AND locator_id IS NULL
1241 OR :locator_id = locator_id)
1242 AND (:lot_number = :l_miss_char
1243 OR :lot_number IS NULL
1244 AND lot_number IS NULL
1245 OR :lot_number = lot_number)
1246 AND (:revision = :l_miss_char
1247 OR :revision IS NULL
1248 AND revision IS NULL
1249 OR :revision = revision)
1250 AND (:lpn_id = :l_miss_num
1251 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
1252 AND (:demand_source_name = :l_miss_char
1253 OR :demand_source_name IS NULL
1254 AND demand_source_name IS NULL
1255 OR :demand_source_name = demand_source_name)
1256 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
1257 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
1258 USING p_organization_id
1259 , l_miss_num
1260 , p_organization_id
1261 , p_organization_id
1262 , p_inventory_item_id
1263 , l_miss_num
1264 , p_inventory_item_id
1265 , p_inventory_item_id
1266 , l_supply_src_type_id
1267 , p_header_id
1268 , l_miss_num
1269 , p_header_id
1270 , p_header_id
1271 , p_dsrc_line
1272 , l_miss_num
1273 , p_dsrc_line
1274 , p_dsrc_line
1275 , p_dsrc_type
1276 , l_miss_num
1277 , p_dsrc_type
1278 , p_dsrc_type
1279 , p_dsrc_delivery
1280 , l_miss_num
1281 , p_dsrc_delivery
1282 , p_dsrc_delivery
1283 , p_subinv
1284 , l_miss_char
1285 , p_subinv
1286 , p_subinv
1287 , p_locator
1288 , l_miss_num
1289 , p_locator
1290 , p_locator
1291 , p_lotnumber
1292 , l_miss_char
1293 , p_lotnumber
1294 , p_lotnumber
1295 , p_revision
1296 , l_miss_char
1297 , p_revision
1298 , p_revision
1299 , p_lpn_id
1300 , l_miss_num
1301 , p_lpn_id
1302 , p_dsrc_name
1303 , l_miss_char
1304 , p_dsrc_name
1305 , p_dsrc_name ;
1306
1307 END IF;
1308 ELSE
1309 IF p_header_id <> l_miss_num AND p_header_id IS NOT NULL
1310 AND p_dsrc_type <> l_miss_num AND p_dsrc_type IS NOT NULL THEN
1311
1312 OPEN l_rsv_cur2 FOR ' SELECT
1313 reservation_id
1314 , primary_reservation_quantity - NVL(detailed_quantity,0)
1315 , NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
1316 FROM mtl_reservations
1317 WHERE demand_source_header_id = :demand_source_header_id
1318 AND demand_source_type_id = :demand_source_type_id
1319 AND supply_source_type_id = :supply_source_type_id
1320 AND (:organization_id = :l_miss_num
1321 OR :organization_id IS NULL
1322 AND organization_id IS NULL
1323 OR :organization_id = organization_id)
1324 AND (:inventory_item_id = :l_miss_num
1325 OR :inventory_item_id IS NULl
1326 AND inventory_item_id IS NULL
1327 OR :inventory_item_id = inventory_item_id)
1328 AND (:subinventory_code = :l_miss_char
1329 OR :subinventory_code IS NULL
1330 AND subinventory_code IS NULL
1331 OR :subinventory_code = subinventory_code)
1332 AND (:locator_id = :l_miss_num
1333 OR :locator_id IS NULL
1334 AND locator_id IS NULL
1335 OR :locator_id = locator_id)
1336 AND (:lot_number = :l_miss_char
1337 OR :lot_number IS NULL
1338 AND lot_number IS NULL
1339 OR :lot_number = lot_number)
1340 AND (:revision = :l_miss_char
1341 OR :revision IS NULL
1342 AND revision IS NULL
1343 OR :revision = revision)
1344 AND (:lpn_id = :l_miss_num
1345 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
1346 AND (:demand_source_name = :l_miss_char
1347 OR :demand_source_name IS NULL
1348 AND demand_source_name IS NULL
1349 OR :demand_source_name = demand_source_name)
1350 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
1351 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
1352 USING p_header_id
1353 , p_dsrc_type
1354 , l_supply_src_type_id
1355 , p_organization_id
1356 , l_miss_num
1357 , p_organization_id
1358 , p_organization_id
1359 , p_inventory_item_id
1360 , l_miss_num
1361 , p_inventory_item_id
1362 , p_inventory_item_id
1363 , p_subinv
1364 , l_miss_char
1365 , p_subinv
1366 , p_subinv
1367 , p_locator
1368 , l_miss_num
1369 , p_locator
1370 , p_locator
1371 , p_lotnumber
1372 , l_miss_char
1373 , p_lotnumber
1374 , p_lotnumber
1375 , p_revision
1376 , l_miss_char
1377 , p_revision
1378 , p_revision
1379 , p_lpn_id
1380 , l_miss_num
1381 , p_lpn_id
1382 , p_dsrc_name
1383 , l_miss_char
1384 , p_dsrc_name
1385 , p_dsrc_name ;
1386 ELSIF p_organization_id <> l_miss_num AND p_organization_id IS NOT NULL
1387 AND p_inventory_item_id <> l_miss_num AND p_inventory_item_id IS NOT NULL THEN
1388
1389 OPEN l_rsv_cur2 FOR ' SELECT
1390 reservation_id
1391 , primary_reservation_quantity - NVL(detailed_quantity,0)
1392 , NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
1393 FROM mtl_reservations
1394 WHERE organization_id = :organization_id
1395 AND inventory_item_id = :inventory_item_id
1396 AND supply_source_type_id = :supply_source_type_id
1397 AND (:demand_source_header_id = :l_miss_num
1398 OR :demand_source_header_id IS NULL
1399 AND demand_source_header_id IS NULL
1400 OR :demand_source_header_id = demand_source_header_id)
1401 AND (:demand_source_type_id = :l_miss_num
1402 OR :demand_source_type_id IS NULL
1403 AND demand_source_type_id IS NULL
1404 OR :demand_source_type_id = demand_source_type_id)
1405 AND (:subinventory_code = :l_miss_char
1406 OR :subinventory_code IS NULL
1407 AND subinventory_code IS NULL
1408 OR :subinventory_code = subinventory_code)
1409 AND (:locator_id = :l_miss_num
1410 OR :locator_id IS NULL
1411 AND locator_id IS NULL
1412 OR :locator_id = locator_id)
1413 AND (:lot_number = :l_miss_char
1414 OR :lot_number IS NULL
1415 AND lot_number IS NULL
1416 OR :lot_number = lot_number)
1417 AND (:revision = :l_miss_char
1418 OR :revision IS NULL
1419 AND revision IS NULL
1420 OR :revision = revision)
1421 AND (:lpn_id = :l_miss_num
1422 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
1423 AND (:demand_source_name = :l_miss_char
1424 OR :demand_source_name IS NULL
1425 AND demand_source_name IS NULL
1426 OR :demand_source_name = demand_source_name)
1427 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
1428 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
1429 USING p_organization_id
1430 , p_inventory_item_id
1431 , l_supply_src_type_id
1432 , p_header_id
1433 , l_miss_num
1434 , p_header_id
1435 , p_header_id
1436 , p_dsrc_type
1437 , l_miss_num
1438 , p_dsrc_type
1439 , p_dsrc_type
1440 , p_subinv
1441 , l_miss_char
1442 , p_subinv
1443 , p_subinv
1444 , p_locator
1445 , l_miss_num
1446 , p_locator
1447 , p_locator
1448 , p_lotnumber
1449 , l_miss_char
1450 , p_lotnumber
1451 , p_lotnumber
1452 , p_revision
1453 , l_miss_char
1454 , p_revision
1455 , p_revision
1456 , p_lpn_id
1457 , l_miss_num
1458 , p_lpn_id
1459 , p_dsrc_name
1460 , l_miss_char
1461 , p_dsrc_name
1462 , p_dsrc_name ;
1463 ELSE
1464
1465 OPEN l_rsv_cur2 FOR ' SELECT
1466 reservation_id
1467 , primary_reservation_quantity - NVL(detailed_quantity,0)
1468 , NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
1469 FROM mtl_reservations
1470 WHERE supply_source_type_id = :supply_source_type_id
1471 AND (:demand_source_header_id = :l_miss_num
1472 OR :demand_source_header_id IS NULL
1473 AND demand_source_header_id IS NULL
1474 OR :demand_source_header_id = demand_source_header_id)
1475 AND (:demand_source_type_id = :l_miss_num
1476 OR :demand_source_type_id IS NULL
1477 AND demand_source_type_id IS NULL
1478 OR :demand_source_type_id = demand_source_type_id)
1479 AND (:organization_id = :l_miss_num
1480 OR :organization_id IS NULL
1481 AND organization_id IS NULL
1482 OR :organization_id = organization_id)
1483 AND (:inventory_item_id = :l_miss_num
1484 OR :inventory_item_id IS NULl
1485 AND inventory_item_id IS NULL
1486 OR :inventory_item_id = inventory_item_id)
1487 AND (:subinventory_code = :l_miss_char
1488 OR :subinventory_code IS NULL
1489 AND subinventory_code IS NULL
1490 OR :subinventory_code = subinventory_code)
1491 AND (:locator_id = :l_miss_num
1492 OR :locator_id IS NULL
1493 AND locator_id IS NULL
1494 OR :locator_id = locator_id)
1495 AND (:lot_number = :l_miss_char
1496 OR :lot_number IS NULL
1497 AND lot_number IS NULL
1498 OR :lot_number = lot_number)
1499 AND (:revision = :l_miss_char
1500 OR :revision IS NULL
1501 AND revision IS NULL
1502 OR :revision = revision)
1503 AND (:lpn_id = :l_miss_num
1504 OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
1505 AND (:demand_source_name = :l_miss_char
1506 OR :demand_source_name IS NULL
1507 AND demand_source_name IS NULL
1508 OR :demand_source_name = demand_source_name)
1509 AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
1510 ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
1511 USING l_supply_src_type_id
1512 , p_header_id
1513 , l_miss_num
1514 , p_header_id
1515 , p_header_id
1516 , p_dsrc_type
1517 , l_miss_num
1518 , p_dsrc_type
1519 , p_dsrc_type
1520 , p_organization_id
1521 , l_miss_num
1522 , p_organization_id
1523 , p_organization_id
1524 , p_inventory_item_id
1525 , l_miss_num
1526 , p_inventory_item_id
1527 , p_inventory_item_id
1528 , p_subinv
1529 , l_miss_char
1530 , p_subinv
1531 , p_subinv
1532 , p_locator
1533 , l_miss_num
1534 , p_locator
1535 , p_locator
1536 , p_lotnumber
1537 , l_miss_char
1538 , p_lotnumber
1539 , p_lotnumber
1540 , p_revision
1541 , l_miss_char
1542 , p_revision
1543 , p_revision
1544 , p_lpn_id
1545 , l_miss_num
1546 , p_lpn_id
1547 , p_dsrc_name
1548 , l_miss_char
1549 , p_dsrc_name
1550 , p_dsrc_name ;
1551 END IF;
1552 END IF;
1553 -- end changes for bug 3347075
1554 l_stmt := '5';
1555
1556 -- Added debug messages
1557 IF (l_debug = 1) THEN
1558 debug_print('l total qty to relieve ' || l_total_qty_to_relieve);
1559 END IF;
1560 -- fetch reservation records and do relief
1561 WHILE (l_total_qty_to_relieve is not null AND
1562 l_total_qty_to_relieve > 0) LOOP
1563
1564 l_stmt := '6';
1565
1566 FETCH l_rsv_cur2 INTO
1567 l_reservation_id,
1568 l_qty_reserved,
1569 l_secondary_qty_reserved; -- INVCONV
1570 IF l_rsv_cur2%notfound THEN
1571 l_reservation_id := NULL;
1572 END IF;
1573
1574 l_stmt := '9';
1575
1576 IF (l_debug = 1) THEN
1577 debug_print('Inside l_rsv_cur2 cursor');
1578 debug_print(' reservation id ' ||l_reservation_id || ' qty reserved = primary - detailed ' || l_qty_reserved);
1579 debug_print('secondary qty reserved ' || l_secondary_qty_reserved); --INVCONV
1580 END IF;
1581
1582 -- exit the loop if no more reservation to relieve
1583 IF l_reservation_id IS NULL THEN
1584 EXIT;
1585 END IF;
1586
1587 l_stmt := '10';
1588
1589 -- call reservation api to relieve the reservation
1590 l_rsv_rec.reservation_id := l_reservation_id;
1591
1592 l_stmt := '11';
1593
1594 IF l_total_qty_to_relieve > l_qty_reserved THEN
1595 l_qty_to_relieve := l_qty_reserved;
1596 -- INVCONV BEGIN
1597 IF l_tracking_quantity_ind = 'PS' THEN
1598 l_secondary_to_relieve := l_secondary_qty_reserved;
1599 END IF;
1600 -- INVCONV END
1601 ELSE
1602 l_qty_to_relieve := l_total_qty_to_relieve;
1603 -- INVCONV BEGIN
1604 IF l_tracking_quantity_ind = 'PS' THEN
1605 l_secondary_to_relieve := l_total_secondary_to_relieve;
1606 END IF;
1607 -- INVCONV END
1608 END IF;
1609
1610 l_stmt := '12';
1611
1612 IF l_qty_to_relieve = l_qty_reserved THEN
1613 l_relieve_all := fnd_api.g_true;
1614 IF (l_debug = 1) THEN
1615 debug_print(' relieve all');
1616 END IF;
1617 ELSE
1618 l_relieve_all := fnd_api.g_false;
1619 IF (l_debug = 1) THEN
1620 debug_print('dont relieve all');
1621 END IF;
1622 --Bug 4764790: Query the serials for that reservation id
1623 --that are being transacted and pass them to
1624 -- relieve_reservations API - only if not all are
1625 -- relieved. If relieve all is true, we will unreserve
1626 --the serials anyways
1627
1628 BEGIN
1629 SELECT msn.inventory_item_id, msn.serial_number bulk collect INTO
1630 l_serial_number_table FROM
1631 mtl_serial_numbers msn, mtl_unit_transactions mut WHERE
1632 msn.reservation_id = l_reservation_id AND msn.current_organization_id =
1633 p_organization_id AND msn.inventory_item_id = p_inventory_item_id
1634 AND mut.transaction_id = p_transaction_id AND msn.serial_number =
1635 mut.serial_number AND msn.inventory_item_id = mut.inventory_item_id
1636 AND msn.current_organization_id = mut.organization_id;
1637 EXCEPTION WHEN no_data_found THEN
1638 IF (l_debug = 1) THEN
1639 debug_print('No serial numbers reserved for this transaction.' || l_total_qty_to_relieve);
1640 END IF;
1641 END;
1642
1643 IF (l_serial_number_table.COUNT > 0) THEN
1644 l_original_serial_numbers := l_serial_number_table;
1645 END IF;
1646 -- End Bug Fix 4764790
1647
1648 END IF;
1649
1650 l_stmt := '13';
1651 IF (l_debug = 1) THEN
1652 debug_print(' before calling relieve rsv. Qty to relieve = ' || l_qty_to_relieve || ' relieve all ' || l_relieve_all);
1653 debug_print(' before calling relieve rsv. Secondary to relieve = ' || l_secondary_to_relieve ); -- INVCONV
1654 END IF;
1655
1656 inv_reservation_pvt.relieve_reservation
1657 (p_api_version_number => 1.0,
1658 p_init_msg_lst => fnd_api.g_false,
1659 x_return_status => l_return_status,
1660 x_msg_count => l_msg_count,
1661 x_msg_data => l_msg_data,
1662 p_rsv_rec => l_rsv_rec,
1663 p_primary_relieved_quantity => l_qty_to_relieve,
1664 p_secondary_relieved_quantity => NULL, -- INVCONV
1665 p_relieve_all => l_relieve_all,
1666 p_original_serial_number => l_original_serial_numbers,
1667 p_validation_flag => fnd_api.g_true,
1668 x_primary_relieved_quantity => l_qty_relieved,
1669 x_secondary_relieved_quantity => l_secondary_qty_relieved, -- INVCONV
1670 x_primary_remain_quantity => l_remain_qty,
1671 x_secondary_remain_quantity => l_secondary_remain_qty -- INVCONV
1672 );
1673
1674 IF l_return_status = fnd_api.g_ret_sts_error THEN
1675 RAISE fnd_api.g_exc_error;
1676 END IF ;
1677
1678 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1679 RAISE fnd_api.g_exc_unexpected_error;
1680 END IF;
1681
1682 l_stmt := '14';
1683
1684 IF (l_debug = 1) THEN
1685 debug_print(' after calling relieve rsv. Qty relieved = ' || l_qty_relieved || ' primary remaining qty = ' || l_remain_qty);
1686 END IF;
1687
1688 l_total_qty_to_relieve := l_total_qty_to_relieve - l_qty_relieved;
1689 -- INVCONV BEGIN
1690 IF l_tracking_quantity_ind = 'PS' THEN
1691 l_total_secondary_to_relieve := l_total_secondary_to_relieve - l_secondary_qty_relieved;
1692 END IF;
1693 -- INVCONV END
1694
1695 IF (l_debug = 1) THEN
1696 debug_print (' total qty TO relieve ' || l_total_qty_to_relieve);
1697 debug_print (' total secondary TO relieve ' || l_total_secondary_to_relieve); -- INVCONV
1698 END IF;
1699 END LOOP;
1700
1701 if (p_dsrc_type in (inv_reservation_global.g_source_type_oe,
1702 inv_reservation_global.g_source_type_internal_ord)) then
1703 l_stmt := '15';
1704
1705 open l_oe_cur ;
1706 fetch l_oe_cur into
1707 x_userline,
1708 x_demand_class;
1709 end if;
1710
1711 l_stmt := '16';
1712
1713 -- BUG 2666911. From now on, the x_ship_qty will be used to pass the
1714 -- actual relieved quantity. Change has been made in the relieve MRP API
1715 -- to check for the ship quantity and call the MPR relief interface
1716 -- whether the reservation quantity is relieved or not.
1717 -- The x_ship_qty will be henceforth be used to compare the actual ship
1718 -- quantity with the relieved quantity to see if the quantity tree
1719 -- validation has to be done or not.
1720
1721 x_ship_qty := p_qty_at_puom - l_total_qty_to_relieve;
1722
1723 --INVCONV BEGIN
1724 IF l_tracking_quantity_ind = 'PS' THEN
1725 x_secondary_ship_qty := p_qty_at_suom - l_total_secondary_to_relieve;
1726 END IF;
1727 --INVCONV END
1728
1729 IF (l_debug = 1) THEN
1730 debug_print (' p_qty_at_uom ' || p_qty_at_puom || ' L-total-qty-to-relieve ' || l_total_qty_to_relieve);
1731 debug_print (' Ship qty after calling l_oe_cur ' || x_ship_qty);
1732 debug_print (' Secondary Ship qty after calling l_oe_cur ' || x_secondary_ship_qty); -- INVCONV
1733 END IF;
1734
1735 l_stmt := '17';
1736
1737 x_return_status := l_return_status;
1738
1739 EXCEPTION
1740 WHEN fnd_api.g_exc_error THEN
1741 ROLLBACK TO trx_relieve_sa;
1742 x_return_status := fnd_api.g_ret_sts_error;
1743
1744 l_stmt := 'Stmt' || l_stmt;
1745 fnd_message.set_name('INV', 'INV-Request failed');
1746 fnd_message.set_token('ENTITY',l_api_name);
1747 fnd_message.set_token('ERRORCODE',l_stmt);
1748 fnd_msg_pub.add;
1749
1750 -- Get message count and data
1751 fnd_msg_pub.count_and_get
1752 ( p_count => x_msg_count
1753 , p_data => x_msg_data
1754 );
1755 WHEN fnd_api.g_exc_unexpected_error THEN
1756 ROLLBACK TO trx_relieve_sa;
1757 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1758
1759 l_stmt := 'Stmt' || l_stmt;
1760 fnd_message.set_name('INV', 'INV-Request failed');
1761 fnd_message.set_token('ENTITY',l_api_name);
1762 fnd_message.set_token('ERRORCODE',l_stmt);
1763 fnd_msg_pub.add;
1764
1765 -- Get message count and data
1766 fnd_msg_pub.count_and_get
1767 ( p_count => x_msg_count
1768 , p_data => x_msg_data
1769 );
1770 WHEN OTHERS THEN
1771 ROLLBACK TO trx_relieve_sa;
1772 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1773
1774 l_stmt := 'Stmt' || l_stmt;
1775 fnd_message.set_name('INV', 'INV-Request failed');
1776 fnd_message.set_token('ENTITY',l_api_name);
1777 fnd_message.set_token('ERRORCODE',l_stmt);
1778 fnd_msg_pub.add;
1779
1780 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1781 THEN
1782 fnd_msg_pub.add_exc_msg
1783 ( g_pkg_name
1784 , l_api_name
1785 );
1786 END IF;
1787 -- Get message count and data
1788 fnd_msg_pub.count_and_get
1789 ( p_count => x_msg_count
1790 , p_data => x_msg_data
1791 );
1792
1793 END rsv_relief;
1794
1795 FUNCTION rsv_relieve(p_transaction_header_id NUMBER) RETURN NUMBER IS
1796
1797 CURSOR trc1 IS
1798 SELECT A.ORGANIZATION_ID,
1799 A.INVENTORY_ITEM_ID,
1800 NVL(A.TRANSACTION_SOURCE_ID, 0) TRANSACTION_SOURCE_ID,
1801 A.TRANSACTION_SOURCE_TYPE_ID,
1802 A.TRX_SOURCE_DELIVERY_ID,
1803 A.TRX_SOURCE_LINE_ID,
1804 A.REVISION,
1805 DECODE(C.LOT_CONTROL_CODE, 2, B.LOT_NUMBER, A.LOT_NUMBER) LOT_NUMBER,
1806 A.SUBINVENTORY_CODE,
1807 A.LOCATOR_ID,
1808 DECODE (C.LOT_CONTROL_CODE, 2,
1809 ABS(NVL(B.PRIMARY_QUANTITY,0)),
1810 A.PRIMARY_QUANTITY *(-1)) PRIMARY_QUANTITY,
1811 A.TRANSACTION_SOURCE_NAME,
1812 FND_DATE.DATE_TO_CANONICAL(A.TRANSACTION_DATE) TRANSACTION_DATE
1813 FROM MTL_SYSTEM_ITEMS C,
1814 MTL_TRANSACTION_LOTS_TEMP B,
1815 MTL_MATERIAL_TRANSACTIONS_TEMP A
1816 WHERE A.TRANSACTION_HEADER_ID = p_transaction_header_id
1817 AND A.ORGANIZATION_ID = C.ORGANIZATION_ID
1818 AND A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
1819 AND B.TRANSACTION_TEMP_ID (+) = A.TRANSACTION_TEMP_ID
1820 AND A.PRIMARY_QUANTITY < 0
1821 ORDER BY A.TRANSACTION_SOURCE_TYPE_ID,
1822 A.TRANSACTION_SOURCE_ID,
1823 A.TRANSACTION_SOURCE_NAME,
1824 A.TRX_SOURCE_LINE_ID,
1825 A.TRX_SOURCE_DELIVERY_ID,
1826 A.INVENTORY_ITEM_ID,
1827 A.ORGANIZATION_ID;
1828
1829 l_api_return_status VARCHAR2(1);
1830 o_msg_count NUMBER;
1831 o_msg_data VARCHAR2(2000);
1832 o_ship_qty NUMBER;
1833 o_userline VARCHAR2(40);
1834 o_demand_class VARCHAR2(30);
1835 o_mps_flag NUMBER;
1836 BEGIN
1837
1838 SAVEPOINT TRXRELIEF;
1839
1840 FOR rec1 in trc1 LOOP
1841 inv_trx_relief_c_pvt.rsv_relief
1842 (x_return_status => L_api_return_status,
1843 x_msg_count => o_msg_count,
1844 x_msg_data => o_msg_data,
1845 x_ship_qty => o_ship_qty,
1846 x_userline => o_userline,
1847 x_demand_class => o_demand_class,
1848 x_mps_flag => o_mps_flag,
1849 p_organization_id => rec1.organization_id,
1850 p_inventory_item_id => rec1.inventory_item_id,
1851 p_subinv => rec1.subinventory_code,
1852 p_locator => rec1.locator_id,
1853 p_lotnumber => rec1.lot_number,
1854 p_revision => rec1.revision,
1855 p_dsrc_type => rec1.transaction_source_type_id,
1856 p_header_id => rec1.transaction_source_id,
1857 p_dsrc_name => rec1.transaction_source_name,
1858 p_dsrc_line => rec1.trx_source_line_id,
1859 p_dsrc_delivery => rec1.trx_source_delivery_id,
1860 p_qty_at_puom => abs(rec1.primary_quantity)
1861 );
1862
1863 IF l_api_return_status <> 'S' THEN
1864 ROLLBACK TO TRXRELIEF;
1865 RETURN -1;
1866 END IF;
1867
1868 IF ((o_ship_qty <> 0) AND
1869 (rec1.transaction_source_type_id = 2 OR rec1.transaction_source_type_id = 8) AND
1870 (o_mps_flag <> 0))
1871 THEN
1872
1873 IF(NOT inv_txn_manager_pub.mrp_ship_order(
1874 rec1.trx_source_line_id,
1875 rec1.inventory_item_id,
1876 o_ship_qty,
1877 fnd_global.user_id,
1878 rec1.organization_id,
1879 o_userline,
1880 rec1.transaction_date,
1881 o_demand_class) ) THEN
1882 ROLLBACK TO TRXRELIEF;
1883 RETURN -1;
1884 END IF;
1885 END IF;
1886 END LOOP;
1887 RETURN 0;
1888 EXCEPTION
1889 WHEN OTHERS THEN
1890 ROLLBACK TO TRXRELIEF;
1891 RETURN -1;
1892 END rsv_relieve;
1893
1894 END inv_trx_relief_c_pvt;