[Home] [Help]
PACKAGE BODY: APPS.WIP_SO_RESERVATIONS
Source
1 PACKAGE BODY WIP_SO_RESERVATIONS as
2 /* $Header: wipsorvb.pls 120.7.12000000.2 2007/02/23 22:30:49 kboonyap ship $ */
3
4
5 -- ---------------------------------------------------------------------------
6 --
7 -- PROCEDURE allocate_completion_to_so
8 --
9 -- ---------------------------------------------------------------------------
10
11 PROCEDURE allocate_completion_to_so (
12 p_organization_id IN NUMBER,
13 p_wip_entity_id IN NUMBER,
14 p_inventory_item_id IN NUMBER,
15 p_transaction_header_id IN NUMBER,
16 p_table_type IN VARCHAR2,--either 'MMTT' or 'WLC'
17 p_primary_quantity IN NUMBER, --lpn passed to inv's transfer_reservation
18 p_lpn_id IN NUMBER, --override quantity in table.
19 p_lot_number IN VARCHAR2,
20 p_txn_temp_id IN NUMBER := NULL,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2) IS
24
25 l_routine_name VARCHAR2(30) := 'ALLOCATE_COMPLETION_TO_SO';
26 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
27 l_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
28 l_rsv_tbl_count NUMBER;
29 l_transaction_tbl transaction_temp_tbl_type;
30 l_return_status VARCHAR(1);
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR(2000);
33 l_error_code NUMBER;
34 l_reservation_found BOOLEAN;
35 l_reservation_index NUMBER;
36 i NUMBER;
37 l_query_reservation VARCHAR2(1);
38
39 l_object_id NUMBER;
40 l_lotcount NUMBER := 0;
41
42
43
44 BEGIN
45
46 SAVEPOINT allocate_completion_to_so_0;
47 fnd_msg_pub.initialize;
48
49 -------------------
50 -- Get reservation
51 -------------------
52 l_reservation_rec.organization_id := p_organization_id;
53 l_reservation_rec.supply_source_header_id := p_wip_entity_id;
54 l_reservation_rec.inventory_item_id := p_inventory_item_id;
55 -- l_reservation_rec.demand_source_type_id :=
56 -- inv_reservation_global.g_source_type_oe;
57 l_reservation_rec.supply_source_type_id :=
58 inv_reservation_global.g_source_type_wip;
59
60 -- query reservations against this particular WIP job.
61 -- tell API to lock rows in mtl_reservations.
62 -- records are returned based on requirement date ascending.
63
64 l_reservation_rec.lpn_id:= null ; /* Fix for Bug#4575108 */
65
66 inv_reservation_pub.query_reservation(
67 p_api_version_number => 1.0,
68 p_init_msg_lst => fnd_api.g_false,
69 x_return_status => l_return_status,
70 x_msg_count => l_msg_count,
71 x_msg_data => l_msg_data,
72 p_query_input => l_reservation_rec,
73 p_lock_records => fnd_api.g_true,
74 p_sort_by_req_date => inv_reservation_global.g_query_req_date_asc,
75 x_mtl_reservation_tbl => l_reservation_tbl,
76 x_mtl_reservation_tbl_count => l_rsv_tbl_count,
77 x_error_code => l_error_code);
78
79 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
80 x_msg_count := l_msg_count;
81 x_msg_data := l_msg_data;
82 x_return_status := l_return_status;
83 RAISE g_need_to_rollback_exception;
84 END IF;
85
86
87 ----------------------
88 -- Get form txn lines
89 ----------------------
90 IF(p_table_type = 'MMTT') THEN
91 get_transaction_lines(
92 p_transaction_header_id => p_transaction_header_id,
93 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
94 p_txn_temp_id => p_txn_temp_id,
95 x_return_status => l_return_status,
96 x_transaction_tbl => l_transaction_tbl);
97 ELSE -- get lines from wip_lpn_completions table
98 get_transaction_lines(
99 p_header_id => p_transaction_header_id,
100 p_primary_quantity => p_primary_quantity,
101 p_lpn_id => p_lpn_id,
102 p_lot_number => p_lot_number,
103 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
104 p_transaction_action_id => WIP_CONSTANTS.CPLASSY_ACTION,
105 x_return_status => l_return_status,
106 x_transaction_tbl => l_transaction_tbl);
107 END IF;
108
109 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
110 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
111 RAISE fnd_api.g_exc_error;
112 ELSE
113 RAISE fnd_api.g_exc_unexpected_error;
114 END IF;
115 END IF;
116
117 mmtt_count := l_transaction_tbl.COUNT;
118 reservation_count := l_rsv_tbl_count;
119
120 -------------------------------------
121 -- Match completion with reservation
122 -------------------------------------
123 FOR i in 1 .. l_transaction_tbl.COUNT LOOP
124
125 l_reservation_found := false;
126
127 -------------
128 -- validation
129 -------------
130 -- if item under lot control, there must be lot number
131 IF (l_transaction_tbl(i).lot_control_code = WIP_CONSTANTS.LOT
132 AND l_transaction_tbl(i).lot_number IS NULL) THEN
133 fnd_message.set_name('WIP', 'WIP_NO_LOT_NUMBER');
134 fnd_msg_pub.add;
135 RAISE fnd_api.g_exc_error;
136 END IF;
137
138 IF (l_transaction_tbl(i).primary_quantity IS NULL
139 OR l_transaction_tbl(i).transaction_quantity IS NULL) THEN
140 fnd_message.set_name('WIP', 'WIP_ZERO_TRANSACTION_QUANTITY');
141 fnd_msg_pub.add;
142 RAISE fnd_api.g_exc_error;
143 END IF;
144
145 -- Sales order specified in the form line
146 IF (l_transaction_tbl(i).demand_source_header_id IS NOT NULL) THEN
147 FOR j in 1 .. l_rsv_tbl_count LOOP
148 l_reservation_index := j;
149 l_reservation_found :=
150 validate_txn_line_against_rsv(
151 p_transaction_rec => l_transaction_tbl(i),
152 p_reservation_rec => l_reservation_tbl(j),
153 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
154 x_return_status => l_return_status,
155 x_query_reservation => l_query_reservation);
156
157 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
158 IF (l_reservation_found) THEN
159 IF (l_query_reservation = 'Y') THEN
160 inv_reservation_pub.query_reservation(
161 p_api_version_number => 1.0,
162 p_init_msg_lst => fnd_api.g_false,
163 x_return_status => l_return_status,
164 x_msg_count => l_msg_count,
165 x_msg_data => l_msg_data,
166 p_query_input => l_reservation_rec,
167 p_lock_records => fnd_api.g_true,
168 p_sort_by_req_date => inv_reservation_global.g_query_req_date_asc,
169 x_mtl_reservation_tbl => l_reservation_tbl,
170 x_mtl_reservation_tbl_count => l_rsv_tbl_count,
171 x_error_code => l_error_code);
172
173 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
174 x_msg_count := l_msg_count;
175 x_msg_data := l_msg_data;
176 x_return_status := l_return_status;
177 RAISE g_need_to_rollback_exception;
178 ELSE
179 l_reservation_index := 1;
180 END IF;
181 END IF;
182 EXIT;
183 END IF;
184 ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
185 RAISE fnd_api.g_exc_error;
186 ELSE
187 RAISE fnd_api.g_exc_unexpected_error;
188 END IF;
189 END LOOP; -- l_rsv_tbl_count
190
191 IF(l_reservation_found) THEN
192 IF(l_transaction_tbl(i).lot_number is NOT null
193 AND l_transaction_tbl(i).lot_control_code = WIP_CONSTANTS.LOT)THEN
194 l_lotcount := 0 ;
195
196 SELECT count(1)
197 INTO l_lotcount
198 FROM MTL_LOT_NUMBERS
199 WHERE INVENTORY_ITEM_ID = l_transaction_tbl(i).inventory_item_id
200 AND ORGANIZATION_ID = l_transaction_tbl(i).organization_id
201 AND LOT_NUMBER = l_transaction_tbl(i).lot_number;
202
203 IF(l_lotcount=0)THEN
204 INV_LOT_API_PUB.InsertLot(
205 p_api_version => 1.0,
206 p_init_msg_list => 'F',
207 p_commit => 'F',
208 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
209 p_inventory_item_id => l_transaction_tbl(i).inventory_item_id,
210 p_organization_id => l_transaction_tbl(i).organization_id,
211 p_lot_number => l_transaction_tbl(i).lot_number,
212 p_expiration_date => l_transaction_tbl(i).lot_expiration_date,
213 p_transaction_temp_id => l_transaction_tbl(i).transaction_temp_id,
214 p_transaction_Action_id => WIP_CONSTANTS.CPLASSY_ACTION,
215 p_transfer_organization_id => NULL,
216 x_object_id => l_object_id,
217 x_return_status => l_return_status,
218 x_msg_count => l_msg_count,
219 x_msg_data => l_msg_data );
220
221 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
222 x_msg_count := l_msg_count;
223 x_msg_data := l_msg_data;
224 x_return_status := l_return_status;
225 RAISE g_need_to_rollback_exception;
226 END IF;
227 END IF;
228 END IF;
229
230 transfer_reservation(
231 p_transaction_rec => l_transaction_tbl(i),
232 p_reservation_rec => l_reservation_tbl(l_reservation_index),
233 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
234 x_return_status => l_return_status);
235
236 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
237 -- we have already validated that txn line primary quantity
238 -- is less than or equal to reservation quantity
239 l_reservation_tbl(l_reservation_index).primary_reservation_quantity :=
240 l_reservation_tbl(l_reservation_index).primary_reservation_quantity -
241 l_transaction_tbl(i).primary_quantity;
242 l_transaction_tbl(i).primary_quantity := 0;
243 ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
244 RAISE fnd_api.g_exc_error;
245 ELSE
246 RAISE fnd_api.g_exc_unexpected_error;
247 END IF;
248
249 -- form line has sales order specified, but not found in
250 -- mtl_reservations
251 ELSE
252 /* ER 4163405: Replacing message WIP_SALES_ORDER_INCONSISTENCY with
253 the new message WIP_NO_SUPPLY_RESERVATIONS */
254 fnd_message.set_name('WIP', 'WIP_NO_SUPPLY_RESERVATIONS');
255 fnd_msg_pub.add;
256 RAISE fnd_api.g_exc_error;
257 END IF; -- reservation found for this completion
258 ELSE -- demand source not specified
259 WHILE (l_transaction_tbl(i).primary_quantity > 0) LOOP
260 FOR j in 1 .. l_rsv_tbl_count LOOP
261 l_reservation_index := j;
262 l_reservation_found :=
263 validate_txn_line_against_rsv(
264 p_transaction_rec => l_transaction_tbl(i),
265 p_reservation_rec => l_reservation_tbl(j),
266 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
267 x_return_status => l_return_status,
268 x_query_reservation => l_query_reservation);
269
270 IF(l_return_status = fnd_api.g_ret_sts_success) THEN
271 IF(l_reservation_found) THEN
272 EXIT;
273 END IF;
274 ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
275 RAISE fnd_api.g_exc_error;
276 ELSE
277 RAISE fnd_api.g_exc_unexpected_error;
278 END IF;
279 END LOOP; -- for each line in reservation
280
281 IF(l_reservation_found) THEN
282 /* Start of fix for bug 2861429: Create a new lot for the
283 * assembly if a lot with lot number
284 *l_transaction_tbl(i).lot_number does not exist. */
285
286 IF(l_transaction_tbl(i).lot_number is NOT null
287 AND
288 l_transaction_tbl(i).lot_control_code = WIP_CONSTANTS.LOT )THEN
289 l_lotcount := 0 ;
290
291 SELECT count(1)
292 INTO l_lotcount
293 FROM MTL_LOT_NUMBERS
294 WHERE INVENTORY_ITEM_ID = l_transaction_tbl(i).inventory_item_id
295 AND ORGANIZATION_ID = l_transaction_tbl(i).organization_id
296 AND LOT_NUMBER = l_transaction_tbl(i).lot_number;
297
298 IF(l_lotcount=0)THEN
299 INV_LOT_API_PUB.InsertLot(
300 p_api_version => 1.0,
301 p_init_msg_list => 'F',
302 p_commit => 'F',
303 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
304 p_inventory_item_id => l_transaction_tbl(i).inventory_item_id,
305 p_organization_id => l_transaction_tbl(i).organization_id,
306 p_lot_number => l_transaction_tbl(i).lot_number,
307 p_expiration_date => l_transaction_tbl(i).lot_expiration_date,
308 p_transaction_temp_id => l_transaction_tbl(i).transaction_temp_id,
309 p_transaction_Action_id => WIP_CONSTANTS.CPLASSY_ACTION,
310 p_transfer_organization_id => NULL,
311 x_object_id => l_object_id,
312 x_return_status => l_return_status,
313 x_msg_count => l_msg_count,
314 x_msg_data => l_msg_data );
315
316 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
317 x_msg_count := l_msg_count;
318 x_msg_data := l_msg_data;
319 x_return_status := l_return_status;
320 RAISE g_need_to_rollback_exception;
321 END IF;
322 END IF;
323 END IF;
324
325 /* End of fix for bug 2861429 */
326 transfer_reservation(
327 p_transaction_rec => l_transaction_tbl(i),
328 p_reservation_rec => l_reservation_tbl(l_reservation_index),
329 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
330 x_return_status => l_return_status);
331
332 IF(l_return_status = fnd_api.g_ret_sts_success) THEN
333 IF(l_transaction_tbl(i).primary_quantity <
334 l_reservation_tbl(l_reservation_index).primary_reservation_quantity) THEN
335 l_reservation_tbl(l_reservation_index).primary_reservation_quantity :=
336 l_reservation_tbl(l_reservation_index).primary_reservation_quantity -
337 l_transaction_tbl(i).primary_quantity;
338 l_transaction_tbl(i).primary_quantity := 0;
339 ELSE
340 l_transaction_tbl(i).primary_quantity :=
341 l_transaction_tbl(i).primary_quantity -
342 l_reservation_tbl(l_reservation_index).primary_reservation_quantity;
343 l_reservation_tbl(l_reservation_index).primary_reservation_quantity := 0;
344 END IF;
345 ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
346 RAISE fnd_api.g_exc_error;
347 ELSE
348 RAISE fnd_api.g_exc_unexpected_error;
349 END IF;
350 ELSE -- no reservation found
351 EXIT; -- there is no reservation matching this form line.
352 -- proceed to the next line.
353 END IF; -- reservation found for this completion?
354 END LOOP; -- while this completion line still has quantity
355 END IF; -- demand source specified in completion form line?
356 END LOOP; -- loop through each completion form line
357 x_return_status := fnd_api.g_ret_sts_success;
358
359 EXCEPTION
360 WHEN g_need_to_rollback_exception THEN
361 ROLLBACK TO SAVEPOINT allocate_completion_to_so_0;
362
363 WHEN fnd_api.g_exc_error THEN
364 x_return_status := fnd_api.g_ret_sts_error;
365
366 fnd_msg_pub.count_and_get(
367 p_encoded => fnd_api.g_false,
368 p_count => x_msg_count,
369 p_data => x_msg_data);
370
371 ROLLBACK TO SAVEPOINT allocate_completion_to_so_0;
372
373 WHEN fnd_api.g_exc_unexpected_error THEN
374 x_return_status := fnd_api.g_ret_sts_unexp_error;
375
376 fnd_msg_pub.count_and_get(
377 p_encoded => fnd_api.g_false,
378 p_count => x_msg_count,
379 p_data => x_msg_data);
380
381 ROLLBACK TO SAVEPOINT allocate_completion_to_so_0;
382
383 WHEN OTHERS THEN
384 x_return_status := fnd_api.g_ret_sts_unexp_error;
385 IF(fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
386 fnd_msg_pub.add_exc_msg (
387 g_package_name,
388 l_routine_name);
389 END IF;
390
391 fnd_msg_pub.count_and_get(
392 p_encoded => fnd_api.g_false,
393 p_count => x_msg_count,
394 p_data => x_msg_data);
395
396 ROLLBACK TO SAVEPOINT allocate_completion_to_so_0;
397 END allocate_completion_to_so;
398
399
400 --by default use the MMTT table
401 PROCEDURE allocate_completion_to_so (
402 p_organization_id IN NUMBER,
403 p_wip_entity_id IN NUMBER,
404 p_inventory_item_id IN NUMBER,
405 p_transaction_header_id IN NUMBER,
406 p_txn_temp_id IN NUMBER := NULL,
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2) IS
410 BEGIN
411 allocate_completion_to_so(p_organization_id => p_organization_id,
412 p_wip_entity_id => p_wip_entity_id,
413 p_inventory_item_id => p_inventory_item_id,
414 p_transaction_header_id => p_transaction_header_id,
415 p_table_type => 'MMTT',
416 p_primary_quantity => null,
417 p_lpn_id => null,
418 p_lot_number => null,
419 p_txn_temp_id => p_txn_temp_id,
420 x_return_status => x_return_status,
421 x_msg_count => x_msg_count,
422 x_msg_data => x_msg_data);
423 END allocate_completion_to_so;
424
425
426
427 -- ---------------------------------------------------------------------------
428 --
429 -- PROCEDURE return_reservation_to_wip
430 --
431 -- HISTORY:
432 -- 02-MAR-2006 spondalu ER 4163405: If pri_qty > rsv_qty, we will set rsv_qty = pri_qty
433 -- to allow return for a qty more than the reserved qty. This can
434 -- happen if the rest is coming from unreserved on-hand.
435 -- Replaced message WIP_SALES_ORDER_INCONSISTENCY with message
436 -- WIP_NO_INVENTORY_RESERVATIONS and changed the logic that throws
437 -- this error to allow for partial return of reservations.
438 --
439 -- ---------------------------------------------------------------------------
440 PROCEDURE return_reservation_to_wip (
441 p_organization_id IN NUMBER,
442 p_wip_entity_id IN NUMBER,
443 p_inventory_item_id IN NUMBER,
444 p_transaction_header_id IN NUMBER,
445 p_txn_temp_id IN NUMBER := NULL,
446 x_return_status OUT NOCOPY VARCHAR2,
447 x_msg_count OUT NOCOPY NUMBER,
448 x_msg_data OUT NOCOPY VARCHAR2) IS
449
450 l_routine_name VARCHAR2(30) := 'RETURN_RESERVATION_TO_WIP';
451 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
452 l_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
453 l_rsv_tbl_count NUMBER;
454 l_transaction_tbl transaction_temp_tbl_type;
455 l_return_status VARCHAR(1);
456 l_msg_count NUMBER;
457 l_msg_data VARCHAR(2000);
458 l_error_code NUMBER;
459 l_valid_reservation BOOLEAN; /* ER 4163405 */
460 l_reservation_found BOOLEAN;
461 l_reservation_index NUMBER;
462 i NUMBER;
463 l_query_reservation VARCHAR2(1);
464 l_wip_entity_type number; /* Bug#4472589 */
465
466 BEGIN
467 SAVEPOINT return_reservation_to_wip_0;
468 fnd_msg_pub.initialize;
469
470 /* Fix for Bug#4472589. Sales Order functionality not present for EAM WO
471 * therefore no need to execute this procedure. just return back with valid
472 * status
473 * Must find record in wip_entities table in following sql
474 */
475
476 select we.entity_type
477 into l_wip_entity_type
478 from wip_entities we
479 where we.wip_entity_id = p_wip_entity_id ;
480
481 if l_wip_entity_type = WIP_CONSTANTS.EAM then
482 x_return_status := fnd_api.g_ret_sts_success;
483 return ;
484 end if ;
485
486 -------------------
487 -- Get reservation
488 -------------------
489 l_reservation_rec.organization_id := p_organization_id;
490 l_reservation_rec.inventory_item_id := p_inventory_item_id;
491 --l_reservation_rec.demand_source_type_id := inv_reservation_global.g_source_type_oe;
492 l_reservation_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv; -- not wip
493
494 -- query reservations for a particular inventory item
495 -- tell API to lock rows in mtl_reservations.
496 -- records are returned based on requirement date ascending.
497 inv_reservation_pub.query_reservation(
498 p_api_version_number => 1.0,
499 p_init_msg_lst => fnd_api.g_false,
500 x_return_status => l_return_status,
501 x_msg_count => l_msg_count,
502 x_msg_data => l_msg_data,
503 p_query_input => l_reservation_rec,
504 p_lock_records => fnd_api.g_true,
505 p_sort_by_req_date => inv_reservation_global.g_query_req_date_asc,
506 x_mtl_reservation_tbl => l_reservation_tbl,
507 x_mtl_reservation_tbl_count => l_rsv_tbl_count,
508 x_error_code => l_error_code);
509
510 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
511 x_msg_count := l_msg_count;
512 x_msg_data := l_msg_data;
513 x_return_status := l_return_status;
514 RETURN;
515 END IF;
516
517 ----------------------
518 -- Get form txn lines
519 ----------------------
520 get_transaction_lines(
521 p_transaction_header_id => p_transaction_header_id,
522 p_transaction_type => WIP_CONSTANTS.WASSY_RETURN,
523 p_txn_temp_id => p_txn_temp_id,
524 x_return_status => l_return_status,
525 x_transaction_tbl => l_transaction_tbl);
526
527 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
528 IF(l_return_status = fnd_api.g_ret_sts_error) THEN
529 RAISE fnd_api.g_exc_error;
530 ELSE
531 RAISE fnd_api.g_exc_unexpected_error;
532 END IF;
533 END IF;
534
535 -------------------------------------
536 -- Process completion record one by one
537 -------------------------------------
538 l_valid_reservation := FALSE; /* ER 4163405 */
539 FOR i in 1 .. l_transaction_tbl.COUNT LOOP
540 -- if item under lot control, there must be lot number
541 IF(l_transaction_tbl(i).lot_control_code = WIP_CONSTANTS.LOT
542 AND l_transaction_tbl(i).lot_number IS NULL) THEN
543 fnd_message.set_name('WIP', 'WIP_NO_LOT_NUMBER');
544 fnd_msg_pub.add;
545 RAISE fnd_api.g_exc_error;
546 END IF;
547
548 IF(l_transaction_tbl(i).demand_source_header_id IS NULL) THEN
549 l_valid_reservation := TRUE; /* ER 4163405 */
550 EXIT;-- since form lines are ordered by demand_source_header_id
551 -- once we reach a line that has no header id, all following
552 -- lines have no header id, so we are done.
553 END IF;
554
555 FOR j in 1 .. l_rsv_tbl_count LOOP
556 l_reservation_index := j;
557 l_reservation_found :=
558 validate_txn_line_against_rsv(
559 p_transaction_rec => l_transaction_tbl(i),
560 p_reservation_rec => l_reservation_tbl(j),
561 p_transaction_type => WIP_CONSTANTS.WASSY_RETURN,
562 x_return_status => l_return_status,
563 x_query_reservation => l_query_reservation);
564
565 IF(l_return_status = fnd_api.g_ret_sts_success) THEN
566 IF(l_reservation_found) THEN
567 EXIT;
568 END IF;
569 ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
570 RAISE fnd_api.g_exc_error;
571 ELSE
572 RAISE fnd_api.g_exc_unexpected_error;
573 END IF;
574 END LOOP;
575
576 IF(l_reservation_found)THEN
577
578 /* ER 4163405: If primary qty > reservation qty. we set reservation qty := txn qty.
579 This condition is true when we are trying to return from open quantity also. */
580 l_valid_reservation := TRUE;
581 if(-l_transaction_tbl(i).primary_quantity
582 > l_reservation_tbl(l_reservation_index).primary_reservation_quantity) then
583 l_transaction_tbl(i).primary_quantity
584 := -l_reservation_tbl(l_reservation_index).primary_reservation_quantity;
585 end if;
586
587 transfer_reservation(
588 p_transaction_rec => l_transaction_tbl(i),
589 p_reservation_rec => l_reservation_tbl(l_reservation_index),
590 p_transaction_type => WIP_CONSTANTS.WASSY_RETURN,
591 x_return_status => l_return_status);
592
593 IF(l_return_status = fnd_api.g_ret_sts_success) THEN
594 -- we have already validated that txn line primary quantity
595 -- is less than or equal to reservation quantity
596 l_reservation_tbl(l_reservation_index).primary_reservation_quantity :=
597 l_reservation_tbl(l_reservation_index).primary_reservation_quantity +
598 l_transaction_tbl(i).primary_quantity;
599 l_transaction_tbl(i).primary_quantity := 0;
600 ELSIF (l_return_status = fnd_api.g_ret_sts_error) THEN
601 RAISE fnd_api.g_exc_error;
602 ELSE
603 RAISE fnd_api.g_exc_unexpected_error;
604 END IF;
605 -- form line has sales order specified, but not found in
606 -- mtl_reservations
607 END IF; -- reservation found for this return
608 END LOOP; -- loop through each return form line
609
610
611 /* ER 4163405: We throw error WIP_SALES_ORDER_INCONSISTENCY if none of the
612 transaction records containing demand information could result in a
613 return of reservation. Since it is now possible that only some of the
614 transaction quantity could be reserved to the sales order, it is better
615 to make a consolidated check for all transaction records. Also, replaced
616 error message WIP_SALES_ORDER_INCONSISTENCY with the new message
617 WIP_NO_INVENTORY_RESERVATIONS for clarity. */
618
619 if(l_valid_reservation) then
620 x_return_status := fnd_api.g_ret_sts_success;
621 else
622 fnd_message.set_name('WIP', 'WIP_NO_INVENTORY_RESERVATIONS');
623 fnd_msg_pub.add;
624 RAISE fnd_api.g_exc_error;
625 end if;
626
627 EXCEPTION
628 WHEN g_need_to_rollback_exception THEN
629 ROLLBACK TO SAVEPOINT return_reservation_to_wip_0;
630
631 WHEN fnd_api.g_exc_error THEN
632 x_return_status := fnd_api.g_ret_sts_error;
633
634 fnd_msg_pub.count_and_get(
635 p_encoded => fnd_api.g_false,
636 p_count => x_msg_count,
637 p_data => x_msg_data);
638
639 ROLLBACK TO SAVEPOINT return_reservation_to_wip_0;
640
641 WHEN fnd_api.g_exc_unexpected_error THEN
642 x_return_status := fnd_api.g_ret_sts_unexp_error;
643
644 fnd_msg_pub.count_and_get(
645 p_encoded => fnd_api.g_false,
646 p_count => x_msg_count,
647 p_data => x_msg_data);
648
649 ROLLBACK TO SAVEPOINT return_reservation_to_wip_0;
650
651 WHEN OTHERS THEN
652 x_return_status := fnd_api.g_ret_sts_unexp_error;
653 IF(fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
654 fnd_msg_pub.add_exc_msg (
655 g_package_name,
656 l_routine_name);
657 END IF;
658
659 fnd_msg_pub.count_and_get(
660 p_encoded => fnd_api.g_false,
661 p_count => x_msg_count,
662 p_data => x_msg_data);
663
664 ROLLBACK TO SAVEPOINT return_reservation_to_wip_0;
665 END return_reservation_to_wip;
666
667
668 PROCEDURE transfer_flow_lines(p_transaction_tbl IN transaction_temp_tbl_type,
669 p_table_type IN VARCHAR2, --either 'WLC' or 'MMTT'
670 p_table_line_id IN NUMBER,
671 x_return_status OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2) is
674
675 l_line_rec OE_Order_PUB.Line_Rec_Type;
676 l_line_id NUMBER;
677 l_requirement_date DATE;
678 l_primary_uom_code VARCHAR2(3);
679 l_primary_open_quantity NUMBER;
680 l_routine_name VARCHAR2(30) := 'TRANSFER_FLOW_LINES';
681 l_reservation_qty NUMBER;
682 l_oe_header_id NUMBER;
683 l_so_type NUMBER;
684 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
685 l_expiration_date DATE := NULL;
686 l_reservation_id NUMBER;--
687 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;--
688 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;--
689 l_quantity_reserved NUMBER;--
690 l_lotcount NUMBER := 0;--
691 l_return_status VARCHAR2(1);
692 l_msg_count NUMBER;
693 l_msg_data VARCHAR2(2000);
694 l_object_id NUMBER;
695 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
696 l_params wip_logger.param_tbl_t;
697 l_wip_cfg_rsv_level NUMBER;
698 l_skip_flag BOOLEAN; /* Bug 2976994 */
699 begin
700 x_return_status := fnd_api.g_ret_sts_success;
701 SAVEPOINT transfer_flow_lines_0;
702 if(l_logLevel <= wip_constants.trace_logging) then
703 l_params(1).paramName := 'not logging params';
704 l_params(1).paramValue := null;
705
706 wip_logger.entryPoint(p_procName => 'wip_so_reservations.transfer_flow_lines',
707 p_params => l_params,
708 x_returnStatus => l_return_status);
709 end if;
710 -------------------------------------
711 -- Loop through each completion
712 -------------------------------------
713 FOR i in 1 .. p_transaction_tbl.COUNT LOOP
714
715 -- If the completion line does not have sales order specified,
716 -- then skip this line.
717 -- Also since get_transaction_lines sort the lines with
718 -- demand source header id, the rest of lines won't have sales
719 -- order specified either. We are done.
720 IF (p_transaction_tbl(i).demand_source_header_id IS NULL) THEN
721 if(l_logLevel <= wip_constants.full_logging) then
722 wip_logger.log('no SO found', l_return_status);
723 end if;
724 EXIT;
725 END IF;
726
727
728 -- validation
729 -- if item under lot control, there must be lot number
730 IF (p_transaction_tbl(i).lot_control_code = WIP_CONSTANTS.LOT AND
731 p_transaction_tbl(i).lot_number IS NULL) THEN
732 if(l_logLevel <= wip_constants.full_logging) then
733 wip_logger.log('no lot', l_return_status);
734 end if;
735 fnd_message.set_name('WIP', 'WIP_NO_LOT_NUMBER');
736 fnd_msg_pub.add;
737 RAISE fnd_api.g_exc_error;
738 END IF;
739
740 IF (p_transaction_tbl(i).primary_quantity IS NULL OR
741 p_transaction_tbl(i).transaction_quantity IS NULL OR
742 p_transaction_tbl(i).primary_quantity = 0 OR
743 p_transaction_tbl(i).transaction_quantity = 0) THEN
744 if(l_logLevel <= wip_constants.full_logging) then
745 wip_logger.log('no qty', l_return_status);
746 end if;
747 fnd_message.set_name('WIP', 'WIP_ZERO_TRANSACTION_QUANTITY');
748 fnd_msg_pub.add;
749 RAISE fnd_api.g_exc_error;
750 END IF;
751
752 ----------------------------------
753 -- Get sales order open quantity
754 ----------------------------------
755
756 BEGIN
757
758 l_skip_flag := FALSE; /* Bug 2976994 */
759
760 -- we shouldn't be locking oe_order_lines_all directly
761 -- this is tempoary until ONT provides a locking api
762 SELECT line_id
763 INTO l_line_id
764 FROM oe_order_lines_all
765 WHERE line_id = p_transaction_tbl(i).demand_source_line_id
766 FOR UPDATE;
767
768
769 SELECT requirement_date,
770 primary_uom_code,
771 primary_open_quantity
772 INTO l_requirement_date,
773 l_primary_uom_code,
774 l_primary_open_quantity
775 FROM wip_open_demands_v
776 WHERE organization_id = p_transaction_tbl(i).organization_id
777 AND inventory_item_id = p_transaction_tbl(i).inventory_item_id
778 AND demand_source_header_id = p_transaction_tbl(i).demand_source_header_id
779 AND demand_source_line_id = p_transaction_tbl(i).demand_source_line_id
780 AND primary_open_quantity > 0;
781 --FOR UPDATE;
782 EXCEPTION
783 -- when there is more than one row, this an internal error.
784 -- open demand should be uniquely identified by a demand_source_line_id
785 -- WHEN TOO_MANY_ROWS THEN
786
787 -- where there is no row returned, the demand information specified
788 -- in the form is incorrect. error out and provide a good explanation
789 -- to the user.
790 WHEN NO_DATA_FOUND THEN
791 /* Fix for bug 2976994: Instead of flagging error, we complete the
792 flow schedule to inventory without reserving. */
793 l_skip_flag := TRUE;
794 if(l_logLevel <= wip_constants.full_logging) then
795 wip_logger.log('Bad SO info or no open demand. Skipping reservation.', l_return_status);
796 end if;
797 /* fnd_message.set_name('WIP', 'WIP_INVALID_SO_TXN_INFO');
798 fnd_msg_pub.add;
799 RAISE fnd_api.g_exc_error; */
800 END;
801
802 if (l_skip_flag = FALSE) then
803 /* Bug 2976994: There is indeed open demand for the order line */
804
805 ----------------------------------
806 -- Check completion quantity
807 -- against open quantity
808 ----------------------------------
809 -- ** now we allow overcompletion against sales order
810 -- the over completed qty will not be reserved
811 l_reservation_qty := p_transaction_tbl(i).primary_quantity;
812 if(l_logLevel <= wip_constants.full_logging) then
813 wip_logger.log('rsv qty:' || l_reservation_qty, l_return_status);
814 end if;
815 IF (p_transaction_tbl(i).primary_quantity > l_primary_open_quantity) THEN
816 l_reservation_qty := l_primary_open_quantity;
817 /*
818 fnd_message.set_name('WIP', 'QUANTITY_ERROR');
819 fnd_message.set_token(
820 token => 'ORDER_QUANTITY',
821 value => l_primary_open_quantity);
822 fnd_msg_pub.add;
823 RAISE fnd_api.g_exc_error;
824 */
825 END IF;
826
827
828 -----------------------------------
829 -- Create reservation in inventory
830 -----------------------------------
831
832 -- first determine if the sales order is an internal order
833 inv_salesorder.get_oeheader_for_salesorder(p_transaction_tbl(i).demand_source_header_id,
834 l_oe_header_id,
835 x_return_status);
836 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
837 if(l_logLevel <= wip_constants.full_logging) then
838 wip_logger.log('inv_salesorder errorred', l_return_status);
839 end if;
840 RAISE fnd_api.g_exc_error;
841 END IF;
842
843 SELECT source_document_type_id
844 INTO l_so_type
845 FROM oe_order_headers_all
846 WHERE header_id = l_oe_header_id;
847
848
849 l_reservation_rec.requirement_date := l_requirement_date;
850 l_reservation_rec.organization_id := p_transaction_tbl(i).organization_id;
851 l_reservation_rec.inventory_item_id := p_transaction_tbl(i).inventory_item_id;
852
853
854 IF (l_so_type = 10) THEN
855 l_reservation_rec.demand_source_type_id := inv_reservation_global.g_source_type_internal_ord;
856 ELSE
857 l_reservation_rec.demand_source_type_id := inv_reservation_global.g_source_type_oe;
858 END IF;
859
860 l_reservation_rec.demand_source_name := NULL;
861 l_reservation_rec.demand_source_delivery := NULL;
862 l_reservation_rec.demand_source_header_id := p_transaction_tbl(i).demand_source_header_id;
863 l_reservation_rec.demand_source_line_id := p_transaction_tbl(i).demand_source_line_id;
864 l_reservation_rec.primary_uom_code := l_primary_uom_code;
865 l_reservation_rec.primary_uom_id := NULL;
866 l_reservation_rec.reservation_uom_code := NULL;
867 l_reservation_rec.reservation_uom_id := NULL;
868 l_reservation_rec.reservation_quantity := NULL;
869 l_reservation_rec.primary_reservation_quantity := l_reservation_qty;
870 --p_transaction_tbl(i).primary_quantity;
871 l_reservation_rec.detailed_quantity := NULL;
872 l_reservation_rec.autodetail_group_id := NULL;
873 l_reservation_rec.external_source_code := NULL;
874 l_reservation_rec.external_source_line_id := NULL;
875 l_reservation_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
876 l_reservation_rec.supply_source_header_id := p_transaction_tbl(i).wip_entity_id;
877 l_reservation_rec.supply_source_line_id := NULL;
878 l_reservation_rec.supply_source_name := NULL;
879 l_reservation_rec.supply_source_line_detail := NULL;
880 l_reservation_rec.subinventory_code := NULL;
881 l_reservation_rec.subinventory_id := NULL;
882 l_reservation_rec.locator_id := NULL;
883 l_reservation_rec.revision := p_transaction_tbl(i).revision;
884
885 -- 3115629 Read the profile and set the values of subinventory / locator
886 l_wip_cfg_rsv_level := fnd_profile.value('WIP:CONFIGURATION_RESERVATION_LEVEL');
887 IF (l_wip_cfg_rsv_level is null) then
888 l_wip_cfg_rsv_level := 2;
889 END IF;
890
891 IF (l_wip_cfg_rsv_level = 2 or p_table_type = 'WLC') then
892 l_reservation_rec.subinventory_code := p_transaction_tbl(i).subinventory_code;
893 l_reservation_rec.subinventory_id := NULL;
894 l_reservation_rec.locator_id := p_transaction_tbl(i).locator_id;
895 END IF;
896
897
898 l_reservation_rec.lot_number := p_transaction_tbl(i).lot_number;
899 l_reservation_rec.lot_number_id := NULL;
900 l_reservation_rec.pick_slip_number := NULL;
901 l_reservation_rec.lpn_id := p_transaction_tbl(i).lpn_id;
902 l_reservation_rec.attribute_category := NULL;
903 l_reservation_rec.attribute1 := NULL;
904 l_reservation_rec.attribute2 := NULL;
905 l_reservation_rec.attribute3 := NULL;
906 l_reservation_rec.attribute4 := NULL;
907 l_reservation_rec.attribute5 := NULL;
908 l_reservation_rec.attribute6 := NULL;
909 l_reservation_rec.attribute7 := NULL;
910 l_reservation_rec.attribute8 := NULL;
911 l_reservation_rec.attribute9 := NULL;
912 l_reservation_rec.attribute10 := NULL;
913 l_reservation_rec.attribute11 := NULL;
914 l_reservation_rec.attribute12 := NULL;
915 l_reservation_rec.attribute13 := NULL;
916 l_reservation_rec.attribute14 := NULL;
917 l_reservation_rec.attribute15 := NULL;
918 l_reservation_rec.ship_ready_flag := NULL;
919
920 l_expiration_date := p_transaction_tbl(i).lot_expiration_date;
921
922 if(l_reservation_rec.lot_number is NOT null) AND
923 (p_transaction_tbl(i).lot_control_code = WIP_CONSTANTS.LOT) AND
924 (p_table_type = 'MMTT') then
925
926 l_lotcount := 0 ;
927
928 SELECT count(1)
929 INTO l_lotcount
930 FROM MTL_LOT_NUMBERS
931 WHERE INVENTORY_ITEM_ID = l_reservation_rec.inventory_item_id
932 AND ORGANIZATION_ID = l_reservation_rec.organization_id
933 AND LOT_NUMBER = l_reservation_rec.lot_number;
934
935 if (l_lotcount=0) then
936 if(l_logLevel <= wip_constants.full_logging) then
937 wip_logger.log('inserting log', l_return_status);
938 end if;
939
940 INV_LOT_API_PUB.InsertLot(p_api_version => 1.0,
941 p_init_msg_list => 'F',
942 p_commit => 'F',
943 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
944 p_inventory_item_id => l_reservation_rec.inventory_item_id,
945 p_organization_id => l_reservation_rec.organization_id,
946 p_lot_number => l_reservation_rec.lot_number,
947 p_expiration_date => l_expiration_date,
948 p_transaction_temp_id => p_table_line_id,
949 p_transaction_Action_id => wip_constants.cplassy_action,
950 p_transfer_organization_id => NULL,
951 x_object_id => l_object_id,
952 x_return_status => l_return_status,
953 x_msg_count => l_msg_count,
954 x_msg_data => l_msg_data );
955
956 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
957 x_msg_count := l_msg_count;
958 x_msg_data := l_msg_data;
959 x_return_status := l_return_status;
960 RAISE g_need_to_rollback_exception;
961 END IF;
962 end if;
963 end if ;
964
965 -- Fix for Bug#2268499
966 inv_quantity_tree_grp.clear_quantity_cache ;
967
968 if(l_logLevel <= wip_constants.full_logging) then
969 wip_logger.log('creating reservation', l_return_status);
970 wip_logger.log('item' || l_reservation_rec.inventory_item_id, l_return_status);
971 wip_logger.log('sub' || l_reservation_rec.subinventory_code, l_return_status);
972 wip_logger.log('loc' || l_reservation_rec.locator_id, l_return_status);
973 wip_logger.log('qty' || l_reservation_rec.primary_reservation_quantity, l_return_status);
974 wip_logger.log('lpn' || l_reservation_rec.lpn_id, l_return_status);
975 end if;
976 inv_reservation_pub.create_reservation(p_api_version_number => 1.0,
977 p_init_msg_lst => fnd_api.g_false,
978 x_return_status => l_return_status,
979 x_msg_count => l_msg_count,
980 x_msg_data => l_msg_data,
981 p_rsv_rec => l_reservation_rec,
982 p_serial_number => l_serial_number_tbl,
983 x_serial_number => l_to_serial_number_tbl,
984 p_partial_reservation_flag => fnd_api.g_false,
985 p_force_reservation_flag => fnd_api.g_false,
986 p_validation_flag => fnd_api.g_true,
987 p_partial_rsv_exists => TRUE, -- Bug 4166956
988 x_quantity_reserved => l_quantity_reserved,
989 x_reservation_id => l_reservation_id);
990
991
992 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
993 if(l_logLevel <= wip_constants.full_logging) then
994 wip_logger.log('reservation creation failed:' || l_msg_data, l_return_status);
995 end if;
996 x_msg_count := l_msg_count;
997 x_msg_data := l_msg_data;
998 x_return_status := l_return_status;
999 RAISE g_need_to_rollback_exception;
1000 END IF;
1001
1002 end if; /* Bug 2976994: Continue from here if skip_flag = TRUE */
1003
1004 END LOOP; -- loop through each completion form line
1005
1006 x_return_status := fnd_api.g_ret_sts_success;
1007 if (l_logLevel <= wip_constants.trace_logging) then
1008 wip_logger.exitPoint(p_procName => 'wip_so_reservations.transfer_flow_lines',
1009 p_procReturnStatus => x_return_status,
1010 p_msg => 'succeeded',
1011 x_returnStatus => l_return_status); --discard logging return status
1012 end if;
1013 exception
1014 WHEN g_need_to_rollback_exception THEN
1015 x_return_status := fnd_api.g_ret_sts_error;
1016 rollback to transfer_flow_lines_0;
1017 if (l_logLevel <= wip_constants.trace_logging) then
1018 wip_logger.exitPoint(p_procName => 'wip_so_reservations.transfer_flow_lines',
1019 p_procReturnStatus => x_return_status,
1020 p_msg => 'error1',
1021 x_returnStatus => l_return_status); --discard logging return status
1022 end if;
1023
1024 WHEN fnd_api.g_exc_error THEN
1025 x_return_status := fnd_api.g_ret_sts_error;
1026 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1027 p_count => x_msg_count,
1028 p_data => x_msg_data);
1029 rollback to transfer_flow_lines_0;
1030 if (l_logLevel <= wip_constants.trace_logging) then
1031 wip_logger.exitPoint(p_procName => 'wip_so_reservations.transfer_flow_lines',
1032 p_procReturnStatus => x_return_status,
1033 p_msg => 'error2',
1034 x_returnStatus => l_return_status); --discard logging return status
1035 end if;
1036
1037 WHEN fnd_api.g_exc_unexpected_error THEN
1038 x_return_status := fnd_api.g_ret_sts_unexp_error;
1039 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data);
1042 rollback to transfer_flow_lines_0;
1043 if (l_logLevel <= wip_constants.trace_logging) then
1044 wip_logger.exitPoint(p_procName => 'wip_so_reservations.transfer_flow_lines',
1045 p_procReturnStatus => x_return_status,
1046 p_msg => 'error3',
1047 x_returnStatus => l_return_status); --discard logging return status
1048 end if;
1049
1050 WHEN OTHERS THEN
1051 x_return_status := fnd_api.g_ret_sts_unexp_error;
1052 IF(fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1053 fnd_msg_pub.add_exc_msg (g_package_name,
1054 l_routine_name);
1055 END IF;
1056
1057 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1058 p_count => x_msg_count,
1059 p_data => x_msg_data);
1060
1061 rollback to transfer_flow_lines_0;
1062 if (l_logLevel <= wip_constants.trace_logging) then
1063 wip_logger.exitPoint(p_procName => 'wip_so_reservations.transfer_flow_lines',
1064 p_procReturnStatus => x_return_status,
1065 p_msg => 'error4',
1066 x_returnStatus => l_return_status); --discard logging return status
1067 end if;
1068 end transfer_flow_lines;
1069
1070
1071
1072 PROCEDURE complete_flow_sched_to_so (p_header_id IN NUMBER,
1073 p_lpn_id IN NUMBER,
1074 p_primary_quantity IN NUMBER, --lpn passed to inv's transfer_reservation
1075 p_lot_number IN VARCHAR2,
1076 x_return_status OUT NOCOPY VARCHAR2,
1077 x_msg_count OUT NOCOPY NUMBER,
1078 x_msg_data OUT NOCOPY VARCHAR2) IS
1079
1080 l_routine_name VARCHAR2(30) := 'COMPLETE_FLOW_SCHED_TO_SO';
1081 l_transaction_tbl transaction_temp_tbl_type;
1082 l_return_status VARCHAR2(1);
1083 l_msg_count NUMBER;
1084 l_msg_data VARCHAR2(2000);
1085 l_params wip_logger.param_tbl_t;
1086 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1087 begin
1088 x_return_status := fnd_api.g_ret_sts_success;
1089 if(l_logLevel <= wip_constants.trace_logging) then
1090 l_params(1).paramName := 'p_header_id';
1091 l_params(1).paramValue := p_header_id;
1092 l_params(2).paramName := 'p_lpn_id';
1093 l_params(2).paramValue := p_lpn_id;
1094 l_params(3).paramName := 'p_primary_quantity';
1095 l_params(3).paramValue := p_primary_quantity;
1096 l_params(4).paramName := 'p_lot_number';
1097 l_params(4).paramValue := p_lot_number;
1098
1099 wip_logger.entryPoint(p_procName => 'wip_so_reservations.complete_flow_sched_to_so',
1100 p_params => l_params,
1101 x_returnStatus => l_return_status);
1102 end if;
1103
1104 savepoint complete_flow_sched_to_so_1;
1105 get_transaction_lines(p_header_id => p_header_id,
1106 p_primary_quantity => p_primary_quantity,
1107 p_lpn_id => p_lpn_id,
1108 p_lot_number => p_lot_number,
1109 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
1110 p_transaction_action_id => WIP_CONSTANTS.CPLASSY_ACTION,
1111 x_return_status => l_return_status,
1112 x_transaction_tbl => l_transaction_tbl);
1113 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1114 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1115 RAISE fnd_api.g_exc_error;
1116 ELSE
1117 RAISE fnd_api.g_exc_unexpected_error;
1118 END IF;
1119 END IF;
1120
1121 if(l_logLevel <= wip_constants.full_logging) then
1122 wip_logger.log(l_transaction_tbl.count || ' lines fetched for so rsv xfer', l_return_status);
1123 end if;
1124
1125 transfer_flow_lines(p_transaction_tbl => l_transaction_tbl,
1126 p_table_type => 'WLC',
1127 p_table_line_id => p_header_id,
1128 x_return_status => l_return_status,
1129 x_msg_data => l_msg_data,
1130 x_msg_count => l_msg_count);
1131
1132 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1133 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1134 RAISE fnd_api.g_exc_error;
1135 ELSE
1136 RAISE fnd_api.g_exc_unexpected_error;
1137 END IF;
1138 END IF;
1139
1140 if (l_logLevel <= wip_constants.trace_logging) then
1141 wip_logger.exitPoint(p_procName => 'wip_so_reservations.complete_flow_sched_to_so',
1142 p_procReturnStatus => x_return_status,
1143 p_msg => 'succeeded',
1144 x_returnStatus => l_return_status); --discard logging return status
1145 end if;
1146 EXCEPTION
1147 WHEN g_need_to_rollback_exception THEN
1148 if(l_logLevel <= wip_constants.trace_logging) then
1149 wip_logger.exitPoint(p_procName => 'wip_so_reservations.complete_flow_sched_to_so',
1150 p_procReturnStatus => x_return_status,
1151 p_msg => 'error1',
1152 x_returnStatus => l_return_status); --discard logging return status
1153 end if;
1154 rollback to complete_flow_sched_to_so_1;
1155 WHEN fnd_api.g_exc_error THEN
1156 x_return_status := fnd_api.g_ret_sts_error;
1157 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1158 p_count => x_msg_count,
1159 p_data => x_msg_data);
1160 if(x_msg_data is null and l_msg_data is not null) then
1161 x_msg_data := l_msg_data;
1162 x_msg_count := l_msg_count;
1163 end if;
1164 rollback to complete_flow_sched_to_so_1;
1165 if(l_logLevel <= wip_constants.trace_logging) then
1166 wip_logger.exitPoint(p_procName => 'wip_so_reservations.complete_flow_sched_to_so',
1167 p_procReturnStatus => x_return_status,
1168 p_msg => 'error2',
1169 x_returnStatus => l_return_Status); --discard logging return status
1170 end if;
1171 WHEN fnd_api.g_exc_unexpected_error THEN
1172 x_return_status := fnd_api.g_ret_sts_unexp_error;
1173 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1174 p_count => x_msg_count,
1175 p_data => x_msg_data);
1176 rollback to complete_flow_sched_to_so_1;
1177 if(l_logLevel <= wip_constants.trace_logging) then
1178 wip_logger.exitPoint(p_procName => 'wip_so_reservations.complete_flow_sched_to_so',
1179 p_procReturnStatus => x_return_Status,
1180 p_msg => 'error3',
1181 x_returnStatus => l_return_Status); --discard logging return status
1182 end if;
1183 WHEN OTHERS THEN
1184 x_return_status := fnd_api.g_ret_sts_unexp_error;
1185 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1186 fnd_msg_pub.add_exc_msg (g_package_name,
1187 l_routine_name);
1188 END IF;
1189 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1190 p_count => x_msg_count,
1191 p_data => x_msg_data);
1192 rollback to complete_flow_sched_to_so_1;
1193 if(l_logLevel <= wip_constants.trace_logging) then
1194 wip_logger.exitPoint(p_procName => 'wip_so_reservations.complete_flow_sched_to_so',
1195 p_procReturnStatus => x_return_Status,
1196 p_msg => 'error4',
1197 x_returnStatus => l_return_Status); --discard logging return status
1198 end if;
1199 END complete_flow_sched_to_so;
1200
1201 -- ---------------------------------------------------------------------------
1202 --
1203 -- PROCEDURE complete_flow_sched_to_so
1204 --
1205 -- ---------------------------------------------------------------------------
1206 /*Bug 5676680: Added one extra parameter p_transaction_temp_id*/
1207 PROCEDURE complete_flow_sched_to_so (
1208 p_transaction_header_id IN NUMBER,
1209 p_transaction_temp_id IN NUMBER DEFAULT NULL,
1210 x_return_status OUT NOCOPY VARCHAR2,
1211 x_msg_count OUT NOCOPY NUMBER,
1212 x_msg_data OUT NOCOPY VARCHAR2) IS
1213
1214 l_routine_name VARCHAR2(30) := 'COMPLETE_FLOW_SCHED_TO_SO';
1215 --l_reservation_rec_default inv_reservation_global.mtl_reservation_rec_type;
1216 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
1217 l_transaction_tbl transaction_temp_tbl_type;
1218 l_return_status VARCHAR2(1);
1219 l_msg_count NUMBER;
1220 l_msg_data VARCHAR2(2000);
1221 l_requirement_date DATE;
1222 l_primary_uom_code VARCHAR2(3);
1223 l_primary_open_quantity NUMBER;
1224 l_quantity_reserved NUMBER;
1225 l_reservation_id NUMBER;
1226 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1227 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1228 l_line_rec OE_Order_PUB.Line_Rec_Type;
1229 l_line_id NUMBER;
1230 l_oe_header_id NUMBER;
1231 l_so_type NUMBER;
1232
1233 l_cp_transaction_id NUMBER;
1234 l_trx_action_id NUMBER;
1235 l_trx_temp_id NUMBER;
1236 l_expiration_date DATE := NULL;
1237 l_object_id NUMBER;
1238 l_lotcount NUMBER := 0;
1239
1240 l_reservation_qty NUMBER;
1241
1242 /*Bug 5676680: Added one extra parameter p_transaction_temp_id in below
1243 cursor and changed where clause to use this new parameter
1244 */
1245 cursor get_group_recs (p_transaction_header_id NUMBER,
1246 p_transaction_temp_id NUMBER) IS
1247 select completion_transaction_id,
1248 transaction_action_id,
1249 transaction_temp_id
1250 from mtl_material_transactions_temp
1251 where transaction_header_id = p_transaction_header_id
1252 and transaction_temp_id = nvl(p_transaction_temp_id,transaction_temp_id);
1253
1254 BEGIN
1255
1256
1257 SAVEPOINT complete_flow_sched_to_so_0;
1258
1259 fnd_msg_pub.initialize;
1260
1261 ----------------------
1262 -- Get form txn lines
1263 ----------------------
1264
1265 -- added because get_transaction_lines() expects the completion_transaction_id.
1266 -- the transaction_header_id in Flow and WOL is guaranteed to be unique.
1267
1268 -- get the parent record of the group sharing the same header id
1269 l_trx_action_id := -1;
1270
1271 /*Bug 5676680: Added one extra parameter p_transaction_temp_id*/
1272 open get_group_recs (p_transaction_header_id, p_transaction_temp_id);
1273 loop
1274 fetch get_group_recs into l_cp_transaction_id, l_trx_action_id,l_trx_temp_id;
1275 exit when l_trx_action_id = WIP_CONSTANTS.CPLASSY_ACTION
1276 or get_group_recs%NOTFOUND;
1277
1278 end loop;
1279 close get_group_recs;
1280
1281 -- this method should only be called for completion transactions
1282 if (l_trx_action_id <> WIP_CONSTANTS.CPLASSY_ACTION) then
1283 return;
1284 end if;
1285
1286 get_transaction_lines(
1287 p_transaction_header_id => l_cp_transaction_id,
1288 p_transaction_type => WIP_CONSTANTS.WASSY_COMPLETION,
1289 x_return_status => l_return_status,
1290 x_transaction_tbl => l_transaction_tbl);
1291
1292
1293 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1294 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1295 RAISE fnd_api.g_exc_error;
1296 ELSE
1297 RAISE fnd_api.g_exc_unexpected_error;
1298 END IF;
1299 END IF;
1300
1301 transfer_flow_lines(p_transaction_tbl => l_transaction_tbl,
1302 p_table_type => 'MMTT',
1303 p_table_line_id => l_trx_temp_id,
1304 x_return_status => x_return_status,
1305 x_msg_data => l_msg_data,
1306 x_msg_count => l_msg_count);
1307
1308
1309 EXCEPTION
1310 WHEN g_need_to_rollback_exception THEN
1311 ROLLBACK TO SAVEPOINT complete_flow_sched_to_so_0;
1312 /* Fix for Bug3035884 . Added following procedure call */
1313 fnd_msg_pub.count_and_get(
1314 p_encoded => fnd_api.g_false,
1315 p_count => x_msg_count,
1316 p_data => x_msg_data);
1317
1318 WHEN fnd_api.g_exc_error THEN
1319
1320 x_return_status := fnd_api.g_ret_sts_error;
1321
1322 fnd_msg_pub.count_and_get(
1323 p_encoded => fnd_api.g_false,
1324 p_count => x_msg_count,
1325 p_data => x_msg_data);
1326
1327 ROLLBACK TO SAVEPOINT complete_flow_sched_to_so_0;
1328
1329 WHEN fnd_api.g_exc_unexpected_error THEN
1330
1331 x_return_status := fnd_api.g_ret_sts_unexp_error;
1332
1333 fnd_msg_pub.count_and_get(
1334 p_encoded => fnd_api.g_false,
1335 p_count => x_msg_count,
1336 p_data => x_msg_data);
1337
1338 ROLLBACK TO SAVEPOINT complete_flow_sched_to_so_0;
1339
1340 WHEN OTHERS THEN
1341
1342 x_return_status := fnd_api.g_ret_sts_unexp_error;
1343 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1344 fnd_msg_pub.add_exc_msg (
1345 g_package_name,
1346 l_routine_name);
1347 END IF;
1348
1349 fnd_msg_pub.count_and_get(
1350 p_encoded => fnd_api.g_false,
1351 p_count => x_msg_count,
1352 p_data => x_msg_data);
1353
1354 ROLLBACK TO SAVEPOINT complete_flow_sched_to_so_0;
1355
1356
1357 END complete_flow_sched_to_so;
1358
1359
1360
1361 -- ---------------------------------------------------------------------------
1362 --
1363 -- PROCEDURE split_order_line
1364 --
1365 -- ---------------------------------------------------------------------------
1366 PROCEDURE split_order_line(
1367 p_old_demand_source_line_id IN NUMBER,
1368 p_new_demand_source_line_id IN NUMBER,
1369 x_return_status OUT NOCOPY VARCHAR2,
1370 x_msg_count OUT NOCOPY NUMBER,
1371 x_msg_data OUT NOCOPY VARCHAR2) IS
1372
1373 l_routine_name VARCHAR2(30) := 'SPLIT_ORDER_LINE';
1374
1375 BEGIN
1376 UPDATE WIP_FLOW_SCHEDULES
1377 SET demand_source_line = to_char(p_new_demand_source_line_id)
1378 WHERE to_number(demand_source_line) = p_old_demand_source_line_id;
1379
1380 x_return_status := fnd_api.g_ret_sts_success;
1381
1382 EXCEPTION
1383 WHEN OTHERS THEN
1384 x_return_status := fnd_api.g_ret_sts_unexp_error;
1385 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1386 fnd_msg_pub.add_exc_msg(g_package_name, l_routine_name);
1387 END IF;
1388 fnd_msg_pub.count_and_get(
1389 p_count => x_msg_count,
1390 p_data => x_msg_data);
1391
1392 END split_order_line;
1393
1394
1395
1396
1397 -- ---------------------------------------------------------------------------
1398 --
1399 -- PROCEDURE get_transaction_lines
1400 --
1401 -- This procedure returns a table of transaction records from
1402 -- mtl_material_transactions_temp (and lots_temp if lot
1403 -- controlled)
1404 -- Used to match with reservation.
1405 --
1406 --
1407 -- Internal helper
1408 -- ---------------------------------------------------------------------------
1409 -- note: using WIP_CONSTANTS.NO_LOT in the group by expression causes
1410 -- ORA-3113. So I am hardcoding the number instead.
1411 -- HISTORY:
1412 -- 02-MAR-2006 spondalu ER 4163405: For returns of lot-controlled assemblies,
1413 -- qty was coming as +ve due to bug in cursor c_transaction_lines.
1414 -- Corrected that.
1415 --
1416
1417 PROCEDURE get_transaction_lines (
1418 p_transaction_header_id IN NUMBER,
1419 p_transaction_type IN NUMBER,
1420 p_txn_temp_id IN NUMBER := NULL,
1421 x_return_status OUT NOCOPY VARCHAR2,
1422 x_transaction_tbl OUT NOCOPY transaction_temp_tbl_type) IS
1423
1424 CURSOR c_transaction_lines (cp_transaction_header_id NUMBER,
1425 cp_transaction_action_id NUMBER) IS
1426 SELECT mmtt.demand_source_header_id,
1427 mmtt.demand_source_line,
1428 mmtt.organization_id,
1429 mmtt.inventory_item_id,
1430 mmtt.revision,
1431 mmtt.subinventory_code,
1432 mmtt.locator_id,
1433 msi.lot_control_code ,
1434 mtlt.lot_number,
1435 mmtt.transaction_source_id,
1436 mmtt.transaction_uom,
1437 mmtt.transaction_date,
1438 sum(decode(msi.lot_control_code,
1439 2 /*WIP_CONSTANTS.LOT*/, mtlt.primary_quantity*sign(mmtt.primary_quantity), /* ER 4163405 */
1440 mmtt.primary_quantity)),
1441 sum(decode(msi.lot_control_code,
1442 2 /*WIP_CONSTANTS.LOT*/, mtlt.transaction_quantity*sign(mmtt.transaction_quantity),
1443 mmtt.transaction_quantity)),
1444 mmtt.demand_class,
1445 mtlt.lot_expiration_date,
1446 mmtt.transaction_temp_id,
1447 null --never an lpn associated with a non-lpn completion
1448 FROM MTL_SYSTEM_ITEMS MSI,
1449 MTL_TRANSACTION_LOTS_TEMP MTLT,
1450 MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1451 WHERE mmtt.completion_transaction_id = cp_transaction_header_id
1452 AND mmtt.transaction_action_id = cp_transaction_action_id
1453 AND mmtt.transaction_source_type_id = 5 /* Job or Schedule */
1454 AND mtlt.transaction_temp_id (+) = mmtt.transaction_temp_id
1455 AND MMTT.inventory_item_id = msi.inventory_item_id
1456 AND MMTT.organization_id = msi.organization_id
1457 AND (p_txn_temp_id IS NULL OR
1458 mmtt.transaction_temp_id = p_txn_temp_id)
1459 GROUP BY
1460 mmtt.demand_source_header_id,
1461 mmtt.demand_source_line,
1462 mmtt.organization_id,
1463 mmtt.inventory_item_id,
1464 mmtt.revision,
1465 mmtt.subinventory_code,
1466 mmtt.locator_id,
1467 msi.lot_control_code,
1468 mtlt.lot_number,
1469 mmtt.transaction_source_id,
1470 mmtt.transaction_uom,
1471 mmtt.transaction_date,
1472 mmtt.demand_class,
1473 mtlt.lot_expiration_date,
1474 mmtt.transaction_temp_id
1475 ORDER BY
1476 mmtt.transaction_temp_id,
1477 mmtt.demand_source_header_id,
1478 mmtt.demand_source_line;
1479
1480
1481 l_routine_name VARCHAR2(30) := 'GET_TRANSACTION_LINES';
1482 l_counter NUMBER := 0;
1483 l_transaction_rec transaction_temp_rec_type;
1484 l_transaction_action_id NUMBER := 0;
1485
1486 BEGIN
1487 x_return_status := fnd_api.g_ret_sts_unexp_error;
1488
1489 IF (p_transaction_type = WIP_CONSTANTS.WASSY_COMPLETION) THEN
1490 l_transaction_action_id := WIP_CONSTANTS.CPLASSY_ACTION; /* 31, completion */
1491 ELSE
1492 l_transaction_action_id := WIP_CONSTANTS.RETASSY_ACTION; /* 32, return */
1493 END IF;
1494
1495 OPEN c_transaction_lines(p_transaction_header_id, l_transaction_action_id);
1496 LOOP
1497 FETCH c_transaction_lines INTO l_transaction_rec;
1498 EXIT WHEN c_transaction_lines%NOTFOUND;
1499 l_counter := l_counter + 1;
1500 x_transaction_tbl(l_counter) := l_transaction_rec;
1501 END LOOP;
1502 CLOSE c_transaction_lines;
1503 x_return_status := fnd_api.g_ret_sts_success;
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 IF (c_transaction_lines%ISOPEN) THEN
1507 CLOSE c_transaction_lines;
1508 END IF;
1509 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1510 fnd_msg_pub.add_exc_msg(g_package_name, l_routine_name);
1511 END IF;
1512
1513 END get_transaction_lines;
1514
1515
1516 -- ---------------------------------------------------------------------------
1517 --
1518 -- PROCEDURE get_transaction_lines
1519 --
1520 -- This procedure returns a table of transaction records from
1521 -- wip_lpn_completions
1522 -- Used to match with reservation.
1523 --
1524 --
1525 -- Internal helper
1526 -- ---------------------------------------------------------------------------
1527 -- note: using WIP_CONSTANTS.NO_LOT in the group by expression causes
1528 -- ORA-3113. So I am hardcoding the number instead.
1529
1530 PROCEDURE get_transaction_lines (
1531 p_header_id IN NUMBER,
1532 p_transaction_type IN NUMBER,
1533 p_transaction_action_id IN NUMBER,
1534 p_primary_quantity IN NUMBER,
1535 p_lpn_id IN NUMBER,
1536 p_lot_number IN VARCHAR2,
1537 x_return_status OUT NOCOPY VARCHAR2,
1538 x_transaction_tbl OUT NOCOPY transaction_temp_tbl_type) IS
1539
1540 CURSOR c_transaction_lines (cp_header_id NUMBER,
1541 cp_transaction_action_id NUMBER) IS
1542 SELECT wlc.demand_source_header_id, --to_number(null),
1543 wlc.demand_source_line,--null,
1544 wlc.organization_id,
1545 wlc.inventory_item_id,
1546 decode(msi.revision_qty_control_code,2,wlc.bom_revision,null),
1547 wlc.subinventory_code,
1548 wlc.locator_id,
1549 msi.lot_control_code ,
1550 wlcl.lot_number,
1551 wlc.wip_entity_id,
1552 wlc.transaction_uom,
1553 wlc.transaction_date,
1554 p_primary_quantity,
1555 p_primary_quantity,
1556 null,
1557 WLCL.lot_expiration_date,
1558 null,
1559 p_lpn_id
1560 FROM MTL_SYSTEM_ITEMS MSI,
1561 WIP_LPN_COMPLETIONS_LOTS WLCL,
1562 WIP_LPN_COMPLETIONS WLC
1563 WHERE wlc.header_id = cp_header_id
1564 AND wlc.transaction_source_type_id = 5 /* Job or Schedule */
1565 AND wlcl.header_id (+) = wlc.header_id
1566 AND wlcl.lot_number (+) = p_lot_number
1567 AND wlc.transaction_action_id = cp_transaction_action_id
1568 AND wlc.inventory_item_id = msi.inventory_item_id
1569 AND wlc.organization_id = msi.organization_id
1570 GROUP BY
1571 wlc.demand_source_header_id,
1572 wlc.demand_source_line,
1573 wlc.organization_id,
1574 wlc.inventory_item_id,
1575 decode(msi.revision_qty_control_code,2,wlc.bom_revision,null),
1576 wlc.subinventory_code,
1577 wlc.locator_id,
1578 msi.lot_control_code,
1579 wlcl.lot_number,
1580 wlc.wip_entity_id,
1581 wlc.transaction_uom,
1582 wlc.primary_quantity,
1583 wlc.transaction_date,
1584 WLCL.lot_expiration_date;
1585
1586
1587 l_routine_name VARCHAR2(30) := 'GET_TRANSACTION_LINES';
1588 l_counter NUMBER := 0;
1589 l_transaction_rec transaction_temp_rec_type;
1590 l_transaction_action_id NUMBER := 0;
1591
1592 BEGIN
1593 x_return_status := fnd_api.g_ret_sts_unexp_error;
1594
1595 OPEN c_transaction_lines(p_header_id, p_transaction_action_id);
1596 LOOP
1597 FETCH c_transaction_lines INTO l_transaction_rec;
1598 EXIT WHEN c_transaction_lines%NOTFOUND;
1599 l_counter := l_counter + 1;
1600 x_transaction_tbl(l_counter) := l_transaction_rec;
1601 END LOOP;
1602 CLOSE c_transaction_lines;
1603 x_return_status := fnd_api.g_ret_sts_success;
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606 IF (c_transaction_lines%ISOPEN) THEN
1607 CLOSE c_transaction_lines;
1608 END IF;
1609 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1610 fnd_msg_pub.add_exc_msg(g_package_name, l_routine_name);
1611 END IF;
1612
1613 END get_transaction_lines;
1614
1615
1616
1617
1618
1619 -- ---------------------------------------------------------------------------
1620 --
1621 -- FUNCTION validate_txn_line_against_rsv
1622 --
1623 --
1624 -- Internal helper
1625 -- HISTORY
1626 -- 02-MAR-2006 spondalu ER 4163405: Included lot-checking when comparing
1627 -- transaction line against reservation line. Removed
1628 -- Error WIP_OVER_RETURN since if pri_qty > rsv_qty,
1629 -- then the difference could be from free on-hand.
1630 -- ---------------------------------------------------------------------------
1631 FUNCTION validate_txn_line_against_rsv(
1632 p_transaction_rec IN transaction_temp_rec_type,
1633 p_reservation_rec IN inv_reservation_global.mtl_reservation_rec_type,
1634 p_transaction_type IN NUMBER,
1635 x_return_status OUT NOCOPY VARCHAR2,
1636 x_query_reservation OUT NOCOPY VARCHAR2 )
1637 RETURN BOOLEAN IS
1638
1639 l_routine_name VARCHAR2(30) := 'VALIDATE_TXN_LINE_AGAINST_RSV';
1640 l_oe_header_id NUMBER;
1641 l_so_type NUMBER;
1642 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
1643 l_requirement_date DATE;
1644 l_primary_uom_code VARCHAR2(3);
1645 l_primary_open_quantity NUMBER;
1646 l_line_id NUMBER;
1647 l_msg_count NUMBER;
1648 l_dummy_sn INV_Reservation_Global.Serial_Number_Tbl_Type;
1649 l_return_status varchar2(1);
1650 l_api_return_status varchar2(1);
1651 l_msg_data VARCHAR(2000);
1652 x_msg_count NUMBER;
1653 x_msg_data VARCHAR(2000);
1654 l_quantity_reserved NUMBER;
1655 l_reservation_id NUMBER;
1656 l_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
1657 l_rsv_tbl_count NUMBER;
1658 l_error_code NUMBER;
1659
1660
1661
1662
1663 BEGIN
1664 x_return_status := fnd_api.g_ret_sts_success;
1665 x_query_reservation := 'N';
1666
1667
1668 IF (p_transaction_rec.demand_source_header_id IS NOT NULL) THEN
1669 IF (p_transaction_type = WIP_CONSTANTS.WASSY_COMPLETION AND
1670 ((p_transaction_rec.demand_source_header_id <> p_reservation_rec.demand_source_header_id) or
1671 (p_transaction_rec.demand_source_line_id <> p_reservation_rec.demand_source_line_id) or
1672 (p_transaction_rec.wip_entity_id <> nvl(p_reservation_rec.supply_source_header_id,-1)) or
1673 (p_transaction_rec.lot_control_code = WIP_CONSTANTS.LOT AND /* ER 4163405 */
1674 p_transaction_rec.lot_number <> p_reservation_rec.lot_number))) THEN
1675 RETURN FALSE;
1676 END IF;
1677
1678 IF (p_transaction_type = WIP_CONSTANTS.WASSY_RETURN AND
1679 ((p_transaction_rec.demand_source_header_id <> p_reservation_rec.demand_source_header_id) or
1680 (p_transaction_rec.demand_source_line_id <> p_reservation_rec.demand_source_line_id) or
1681 (p_transaction_rec.lot_control_code = WIP_CONSTANTS.LOT AND /* ER 4163405 */
1682 p_transaction_rec.lot_number <> p_reservation_rec.lot_number))) THEN
1683 RETURN FALSE;
1684 END IF;
1685
1686 IF (p_transaction_rec.demand_source_line_id IS NULL) THEN
1687 RETURN FALSE;
1688 END IF;
1689
1690 -- quantity is only checked when the transaction record has
1691 -- a sales order line specified.
1692 -- in the allocation case, quantity is not checked.
1693
1694 IF (p_transaction_type = WIP_CONSTANTS.WASSY_COMPLETION
1695 AND p_transaction_rec.primary_quantity >
1696 p_reservation_rec.primary_reservation_quantity) THEN
1697
1698 -- Check for overcompletion tolerance.
1699 -- If allowed , get details from MMTT record p_transaction_rec . ( Make sure that all details are present)
1700 -- call create_reservations
1701 -- call query_reservation and populate reservation pl/sql table.
1702
1703
1704 -- first determine if the sales order is an internal order
1705
1706 inv_salesorder.get_oeheader_for_salesorder(p_transaction_rec.demand_source_header_id,
1707 l_oe_header_id,
1708 x_return_status);
1709 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1710 RAISE fnd_api.g_exc_error;
1711 END IF;
1712
1713 SELECT source_document_type_id
1714 INTO l_so_type
1715 FROM oe_order_headers_all
1716 WHERE header_id = l_oe_header_id;
1717
1718
1719 ----------------------------------
1720 -- Get sales order open quantity
1721 ----------------------------------
1722
1723 BEGIN
1724
1725 -- we shouldn't be locking oe_order_lines_all directly
1726 -- this is tempoary until ONT provides a locking api
1727 SELECT line_id
1728 INTO l_line_id
1729 FROM oe_order_lines_all
1730 WHERE line_id = p_transaction_rec.demand_source_line_id ;
1731 -- FOR UPDATE;
1732
1733
1734 SELECT requirement_date,
1735 primary_uom_code,
1736 primary_open_quantity
1737 INTO l_requirement_date,
1738 l_primary_uom_code,
1739 l_primary_open_quantity
1740 FROM wip_open_demands_v
1741 WHERE organization_id = p_transaction_rec.organization_id
1742 AND inventory_item_id = p_transaction_rec.inventory_item_id
1743 AND demand_source_header_id
1744 = p_transaction_rec.demand_source_header_id
1745 AND demand_source_line_id
1746 = p_transaction_rec.demand_source_line_id;
1747 --FOR UPDATE;
1748 EXCEPTION
1749 -- when there is more than one row, this an internal error.
1750 -- open demand should be uniquely identified by a demand_source_line_id
1751 -- WHEN TOO_MANY_ROWS THEN
1752 -- where there is no row returned, the demand information specified
1753 -- in the form is incorrect. error out and provide a good explanation
1754 -- to the user.
1755 WHEN NO_DATA_FOUND THEN
1756 fnd_message.set_name('WIP', 'WIP_INVALID_SO');
1757 fnd_msg_pub.add;
1758 RAISE fnd_api.g_exc_error;
1759
1760 END;
1761
1762 l_reservation_rec.requirement_date := l_requirement_date ;
1763 l_reservation_rec.organization_id := p_transaction_rec.organization_id;
1764 l_reservation_rec.inventory_item_id := p_transaction_rec.inventory_item_id;
1765
1766 IF (l_so_type = 10) THEN
1767 l_reservation_rec.demand_source_type_id := inv_reservation_global.g_source_type_internal_ord;
1768 ELSE
1769 l_reservation_rec.demand_source_type_id := inv_reservation_global.g_source_type_oe;
1770 END IF;
1771
1772 l_reservation_rec.demand_source_name := NULL;
1773 l_reservation_rec.demand_source_delivery := NULL;
1774 l_reservation_rec.demand_source_header_id := p_transaction_rec.demand_source_header_id;
1775 l_reservation_rec.demand_source_line_id := p_transaction_rec.demand_source_line_id;
1776 l_reservation_rec.primary_uom_code := l_primary_uom_code;
1777 l_reservation_rec.primary_uom_id := NULL;
1778 l_reservation_rec.reservation_uom_code := NULL;
1779 l_reservation_rec.reservation_uom_id := NULL;
1780 l_reservation_rec.reservation_quantity := NULL;
1781 l_reservation_rec.primary_reservation_quantity := p_transaction_rec.primary_quantity -
1782 p_reservation_rec.primary_reservation_quantity;
1783
1784 if ( p_reservation_rec.primary_reservation_quantity >0 ) then
1785 l_reservation_rec.primary_reservation_quantity := p_transaction_rec.primary_quantity;
1786 end if ;
1787
1788
1789 l_reservation_rec.detailed_quantity := NULL;
1790 l_reservation_rec.autodetail_group_id := NULL;
1791 l_reservation_rec.external_source_code := NULL;
1792 l_reservation_rec.external_source_line_id := NULL;
1793 l_reservation_rec.supply_source_type_id := inv_reservation_global.g_source_type_wip;
1794 l_reservation_rec.supply_source_header_id := p_transaction_rec.wip_entity_id;
1795 l_reservation_rec.supply_source_line_id := NULL;
1796 l_reservation_rec.supply_source_name := NULL;
1797 l_reservation_rec.supply_source_line_detail := NULL;
1798 l_reservation_rec.revision := p_transaction_rec.revision;
1799 l_reservation_rec.subinventory_code := NULL;
1800 l_reservation_rec.subinventory_id := NULL;
1801 l_reservation_rec.locator_id := NULL;
1802 l_reservation_rec.lot_number := p_transaction_rec.lot_number;
1803 l_reservation_rec.lot_number_id := NULL;
1804 l_reservation_rec.pick_slip_number := NULL;
1805 l_reservation_rec.lpn_id := NULL;
1806 l_reservation_rec.attribute_category := NULL;
1807 l_reservation_rec.attribute1 := NULL;
1808 l_reservation_rec.attribute2 := NULL;
1809 l_reservation_rec.attribute3 := NULL;
1810 l_reservation_rec.attribute4 := NULL;
1811 l_reservation_rec.attribute5 := NULL;
1812 l_reservation_rec.attribute6 := NULL;
1813 l_reservation_rec.attribute7 := NULL;
1814 l_reservation_rec.attribute8 := NULL;
1815 l_reservation_rec.attribute9 := NULL;
1816 l_reservation_rec.attribute10 := NULL;
1817 l_reservation_rec.attribute11 := NULL;
1818 l_reservation_rec.attribute12 := NULL;
1819 l_reservation_rec.attribute13 := NULL;
1820 l_reservation_rec.attribute14 := NULL;
1821 l_reservation_rec.attribute15 := NULL;
1822 l_reservation_rec.ship_ready_flag := NULL;
1823
1824 if ( p_reservation_rec.primary_reservation_quantity >0 ) then
1825
1826 INV_Reservation_PUB.Update_Reservation
1827 (
1828 p_api_version_number => 1.0
1829 , p_init_msg_lst => fnd_api.g_false
1830 , x_return_status => l_api_return_status
1831 , x_msg_count => l_msg_count
1832 , x_msg_data => l_msg_data
1833 , p_original_rsv_rec => p_reservation_rec
1834 , p_to_rsv_rec => l_reservation_rec
1835 , p_original_serial_number => l_dummy_sn
1836 , p_to_serial_number => l_dummy_sn
1837 , p_validation_flag => fnd_api.g_true
1838 );
1839 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1840 FND_MESSAGE.SET_NAME('INV','INV_UPD_RSV_FAILED');
1841 FND_MSG_PUB.Add;
1842 RAISE fnd_api.g_exc_unexpected_error;
1843 END IF ;
1844 else
1845
1846 inv_reservation_pub.create_reservation(
1847 p_api_version_number => 1.0,
1848 p_init_msg_lst => fnd_api.g_false,
1849 x_return_status => l_return_status,
1850 x_msg_count => l_msg_count,
1851 x_msg_data => l_msg_data,
1852 p_rsv_rec => l_reservation_rec,
1853 p_serial_number => l_dummy_sn,
1854 x_serial_number => l_dummy_sn,
1855 p_partial_reservation_flag => fnd_api.g_false,
1856 p_force_reservation_flag => fnd_api.g_false,
1857 p_validation_flag => fnd_api.g_true,
1858 x_quantity_reserved => l_quantity_reserved,
1859 x_reservation_id => l_reservation_id);
1860
1861
1862 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1863 x_msg_count := l_msg_count;
1864 x_msg_data := l_msg_data;
1865 x_return_status := l_return_status;
1866 RAISE g_need_to_rollback_exception;
1867 END IF;
1868 END IF ;
1869
1870 /* fnd_message.set_name('WIP', 'WIP_OVER_COMPLETE');
1871 fnd_msg_pub.add;
1872 RAISE fnd_api.g_exc_error; */
1873 x_query_reservation := 'Y';
1874
1875 END IF; -- check transaction type
1876
1877 /* fix for bug 1821610 */
1878
1879 ELSE /* demand_source_header_id is NULL */
1880
1881 IF (p_reservation_rec.primary_reservation_quantity = 0) THEN
1882 RETURN FALSE;
1883 END IF;
1884
1885
1886 END IF; -- if p_transaction_rec_.demand_source_header_id IS NOT NULL
1887
1888
1889 IF (p_transaction_type = WIP_CONSTANTS.WASSY_COMPLETION
1890 and p_reservation_rec.subinventory_code IS NOT NULL
1891 and p_transaction_rec.subinventory_code <>
1892 p_reservation_rec.subinventory_code) THEN
1893 fnd_message.set_name('WIP', 'WIP_INVALID_SUBINV');
1894 fnd_message.set_token('TXN_SUB', p_transaction_rec.subinventory_code);
1895 fnd_message.set_token('SO_SUB', p_reservation_rec.subinventory_code);
1896 fnd_msg_pub.add;
1897 RAISE fnd_api.g_exc_error;
1898 END IF;
1899
1900
1901 RETURN TRUE;
1902 EXCEPTION
1903 WHEN fnd_api.g_exc_error THEN
1904 x_return_status := fnd_api.g_ret_sts_error;
1905 return FALSE;
1906
1907 WHEN fnd_api.g_exc_unexpected_error THEN
1908 x_return_status := fnd_api.g_ret_sts_unexp_error;
1909 return FALSE;
1910
1911 WHEN OTHERS THEN
1912 x_return_status := fnd_api.g_ret_sts_unexp_error;
1913 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1914 fnd_msg_pub.add_exc_msg(g_package_name, l_routine_name);
1915 END IF;
1916 return FALSE;
1917
1918 END validate_txn_line_against_rsv;
1919
1920
1921 -- ---------------------------------------------------------------------------
1922 --
1923 -- PROCEDURE transfer_reservation
1924 --
1925 -- Internal helper
1926 -- ---------------------------------------------------------------------------
1927 PROCEDURE transfer_reservation(
1928 p_transaction_rec IN transaction_temp_rec_type,
1929 p_reservation_rec IN inv_reservation_global.mtl_reservation_rec_type,
1930 p_transaction_type IN NUMBER,
1931 x_return_status OUT NOCOPY VARCHAR2) IS
1932
1933 l_routine_name VARCHAR2(30) := 'TRANSFER_RESERVATION';
1934 l_to_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
1935 l_to_reservation_id NUMBER;
1936 l_quantity NUMBER;
1937 l_return_status VARCHAR(1);
1938 l_msg_count NUMBER;
1939 l_msg_data VARCHAR(2000);
1940 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
1941 l_to_serial_number inv_reservation_global.serial_number_tbl_type;
1942 l_wip_cfg_rsv_level NUMBER;
1943
1944 BEGIN
1945 IF(p_transaction_type = WIP_CONSTANTS.WASSY_COMPLETION) THEN
1946 l_quantity := p_transaction_rec.primary_quantity;
1947
1948 l_to_reservation_rec.supply_source_type_id :=
1949 inv_reservation_global.g_source_type_inv;
1950 l_to_reservation_rec.supply_source_header_id := p_transaction_rec.wip_entity_id;
1951 l_to_reservation_rec.supply_source_line_id := NULL;
1952 l_to_reservation_rec.supply_source_name := NULL;
1953 l_to_reservation_rec.supply_source_line_detail := NULL;
1954 l_to_reservation_rec.lot_number := p_transaction_rec.lot_number;
1955
1956 -- 3115629 Read the profile and set the values of subinventory / locator
1957 l_wip_cfg_rsv_level := fnd_profile.value('WIP:CONFIGURATION_RESERVATION_LEVEL');
1958 IF(l_wip_cfg_rsv_level is null) then
1959 l_wip_cfg_rsv_level := 2;
1960 END IF;
1961
1962 IF(l_wip_cfg_rsv_level = 2) then
1963 l_to_reservation_rec.subinventory_code := p_transaction_rec.subinventory_code;
1964 l_to_reservation_rec.locator_id := p_transaction_rec.locator_id;
1965 END IF;
1966
1967 -- Fixed Bug# 1821610. Populate item revision before calling inventory api.
1968 l_to_Reservation_rec.revision := p_transaction_rec.revision;
1969
1970 --for lpn completions
1971 l_to_reservation_rec.lpn_id := p_transaction_rec.lpn_id;
1972 ELSE
1973 l_quantity := - p_transaction_rec.primary_quantity;
1974
1975 l_to_reservation_rec.supply_source_type_id :=
1976 inv_reservation_global.g_source_type_wip;
1977 /* Fix for bug 4236074: The following line was commented in 115.8. Uncommented this */
1978 l_to_reservation_rec.supply_source_header_id := p_transaction_rec.wip_entity_id;
1979 l_to_reservation_rec.supply_source_line_id := NULL;
1980 l_to_reservation_rec.supply_source_name := NULL;
1981 l_to_reservation_rec.supply_source_line_detail := NULL;
1982 l_to_reservation_rec.lot_number := NULL;
1983 l_to_reservation_rec.subinventory_code := NULL;
1984 l_to_reservation_rec.locator_id := NULL;
1985 END IF;
1986
1987 IF(l_quantity > p_reservation_rec.primary_reservation_quantity) THEN
1988 l_to_reservation_rec.primary_reservation_quantity :=
1989 p_reservation_rec.primary_reservation_quantity;
1990 ELSE
1991 l_to_reservation_rec.primary_reservation_quantity := l_quantity;
1992 END IF;
1993
1994 inv_reservation_pub.transfer_reservation(
1995 p_api_version_number => 1.0,
1996 p_init_msg_lst => fnd_api.g_false,
1997 x_return_status => l_return_status,
1998 x_msg_count => l_msg_count,
1999 x_msg_data => l_msg_data,
2000 p_is_transfer_supply => fnd_api.g_true,
2001 p_original_rsv_rec => p_reservation_rec,
2002 p_to_rsv_rec => l_to_reservation_rec,
2003 p_original_serial_number => l_original_serial_number,
2004 p_to_serial_number => l_to_serial_number,
2005 p_validation_flag => fnd_api.g_true,
2006 x_to_reservation_id => l_to_reservation_id);
2007
2008 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
2009 x_return_status := l_return_status;
2010 RETURN;
2011 END IF;
2012 x_return_status := fnd_api.g_ret_sts_success;
2013
2014 EXCEPTION
2015 WHEN OTHERS THEN
2016 x_return_status := fnd_api.g_ret_sts_unexp_error;
2017
2018 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2019 fnd_msg_pub.add_exc_msg(g_package_name, l_routine_name);
2020 END IF;
2021
2022 END transfer_reservation;
2023
2024
2025 -- ---------------------------------------------------------------------------
2026 --
2027 -- PROCEDURE make_callback_to_workflow
2028 -- make callback to workflow when the first reservation to the order line is
2029 -- created(p_type = 'FIRST') or when the last reservation to the order line
2030 -- is deleted(p_type = 'LAST')
2031 --
2032 -- Internal helper
2033 -- ---------------------------------------------------------------------------
2034 PROCEDURE make_callback_to_workflow(
2035 p_organization_id IN NUMBER,
2036 p_inventory_item_id IN NUMBER,
2037 p_order_line_id IN NUMBER,
2038 p_type IN VARCHAR2,
2039 x_return_status OUT NOCOPY VARCHAR2,
2040 x_msg_count OUT NOCOPY NUMBER,
2041 x_msg_data OUT NOCOPY VARCHAR2)
2042 IS
2043 l_api_name CONSTANT VARCHAR2(40) := 'Make_Callback_To_Workflow';
2044 IS_ATO_ITEM VARCHAR(2);
2045 cnt NUMBER;
2046 BEGIN
2047
2048 x_return_status := FND_API.G_RET_STS_SUCCESS;
2049
2050 select msi.replenish_to_order_flag into IS_ATO_ITEM
2051 from mtl_system_items msi
2052 where msi.organization_id = p_organization_id
2053 and msi.inventory_item_id = p_inventory_item_id;
2054
2055 if (IS_ATO_ITEM = 'Y') then
2056 if (p_type = 'FIRST') then
2057 -- test if this is the first reservation
2058 -- if yes, then
2059 select count(*) into cnt
2060 from mtl_reservations
2061 where demand_source_line_id = p_order_line_id;
2062
2063 if (cnt = 1) then
2064 CTO_WIP_WORKFLOW_API_PK.first_wo_reservation_created(
2065 p_order_line_id,
2066 x_return_status,
2067 x_msg_count,
2068 x_msg_data);
2069 end if;
2070 elsif (p_type = 'LAST') then
2071 -- test if this is the last reservation
2072 -- if yes then
2073 select count(*) into cnt
2074 from mtl_reservations
2075 where demand_source_line_id = p_order_line_id;
2076
2077 if (cnt = 0) then
2078 CTO_WIP_WORKFLOW_API_PK.last_wo_reservation_deleted(
2079 p_order_line_id,
2080 x_return_status,
2081 x_msg_count,
2082 x_msg_data);
2083 end if;
2084 end if;
2085 end if;
2086
2087 EXCEPTION
2088 when others then
2089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2090 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2091 FND_MSG_PUB.Add_Exc_Msg(g_package_name, l_api_name);
2092 END IF;
2093 FND_MSG_PUB.Count_And_Get(
2094 p_count => x_msg_count,
2095 p_data => x_msg_data
2096 );
2097
2098 END make_callback_to_workflow;
2099
2100 PROCEDURE respond_to_change_order (
2101 p_org_id IN NUMBER,
2102 p_header_id IN NUMBER,
2103 p_line_id IN NUMBER,
2104 x_status OUT NOCOPY VARCHAR2,
2105 x_msg_count OUT NOCOPY NUMBER,
2106 x_msg_data OUT NOCOPY VARCHAR2)
2107 IS
2108 l_response_code NUMBER;
2109 l_wip_entity_id NUMBER;
2110
2111 cursor l_always_cursor IS
2112 SELECT wip_entity_id
2113 FROM wip_discrete_jobs wdj
2114 WHERE wdj.organization_id = p_org_id
2115 AND wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
2116 WIP_CONSTANTS.COMP_CHRG)
2117 AND wdj.wip_entity_id IN (SELECT wip_entity_id
2118 FROM wip_reservations_v
2119 WHERE demand_source_line_id = p_line_id
2120 AND demand_source_header_id = p_header_id
2121 AND organization_id = p_org_id);
2122
2123 -- Bug 4890958
2124 -- Perf Fix for SQL Rep ID 15027638
2125 -- ntungare Wed May 24 22:19:14 PDT 2006
2126 --
2127 /*
2128 cursor l_1to1_cursor IS
2129 SELECT wip_entity_id
2130 FROM wip_discrete_jobs wdj
2131 WHERE wdj.organization_id = p_org_id
2132 AND wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
2133 WIP_CONSTANTS.COMP_CHRG)
2134 AND wdj.wip_entity_id IN (SELECT wip_entity_id
2135 FROM wip_reservations_v
2136 WHERE demand_source_line_id = p_line_id
2137 AND demand_source_header_id = p_header_id
2138 AND organization_id = p_org_id)
2139 AND NOT EXISTS (SELECT 1
2140 FROM wip_reservations_v wrv1
2141 WHERE wrv1.demand_source_line_id = p_line_id
2142 AND wrv1.demand_source_header_id = p_header_id
2143 AND ((wrv1.organization_id <> p_org_id)
2144 OR (wrv1.wip_entity_id <> wdj.wip_entity_id)))
2145 AND NOT EXISTS (SELECT 1
2146 FROM wip_reservations_v wrv2
2147 WHERE wrv2.wip_entity_id = wdj.wip_entity_id
2148 AND wrv2.organization_id = wdj.organization_id
2149 AND ((wrv2.demand_source_header_id <> p_header_id)
2150 OR (wrv2.demand_source_line_id <> p_line_id)));
2151 */
2152
2153 Cursor l_1to1_cursor IS
2154 SELECT wdj.wip_entity_id
2155 FROM wip_discrete_jobs wdj, wip_reservations_v wrv
2156 WHERE wdj.organization_id = p_org_id
2157 AND wdj.organization_id = wrv.organization_id
2158 AND wdj.status_type IN (WIP_CONSTANTS.UNRELEASED,
2159 WIP_CONSTANTS.RELEASED,
2160 WIP_CONSTANTS.COMP_CHRG)
2161 AND wdj.wip_entity_id = wrv.wip_entity_id
2162 AND wrv.demand_source_line_id = p_line_id
2163 AND wrv.demand_source_header_id = p_header_id
2164 AND NOT EXISTS (SELECT 1
2165 FROM wip_reservations_v wrv1
2166 WHERE (wrv1.demand_source_line_id = p_line_id AND
2167 wrv1.demand_source_header_id = p_header_id AND
2168 (wrv1.organization_id <> p_org_id OR
2169 wrv1.wip_entity_id <> wdj.wip_entity_id))
2170 OR
2171 (wrv1.wip_entity_id = wdj.wip_entity_id AND
2172 wrv1.organization_id = wdj.organization_id AND
2173 (wrv1.demand_source_header_id <> p_header_id OR
2174 wrv1.demand_source_line_id <> p_line_id)));
2175
2176 BEGIN
2177 -- get the response_code from WIP_PARAMETERS TABLE
2178 SELECT so_change_response_type
2179 INTO l_response_code
2180 FROM wip_parameters
2181 WHERE organization_id = p_org_id;
2182
2183 IF (l_response_code = WIP_CONSTANTS.NEVER) THEN
2184 x_status := fnd_api.g_ret_sts_success;
2185 RETURN; -- reponse type is never
2186
2187 ELSIF (l_response_code = WIP_CONSTANTS.ALWAYS) THEN
2188 OPEN l_always_cursor; -- response type is always
2189
2190 ELSE
2191 OPEN l_1to1_cursor; -- response type is when linked 1-1
2192 END IF;
2193
2194 LOOP
2195 IF (l_response_code = WIP_CONSTANTS.ALWAYS) THEN
2196 FETCH l_always_cursor INTO
2197 l_wip_entity_id;
2198 EXIT WHEN l_always_cursor%NOTFOUND;
2199 ELSE
2200 FETCH l_1to1_cursor INTO
2201 l_wip_entity_id;
2202 EXIT WHEN l_1to1_cursor%NOTFOUND;
2203 END IF;
2204
2205
2206 -- call procedure to put the Job on Hold and release it if needed
2207 WIP_CHANGE_STATUS.PUT_JOB_ON_HOLD(l_wip_entity_id, p_org_id);
2208
2209 END LOOP;
2210
2211 -- set any job still in the wip interface to status hold
2212 UPDATE wip_job_schedule_interface
2213 SET status_type = WIP_CONSTANTS.HOLD,
2214 last_update_date = SYSDATE
2215 WHERE organization_id = p_org_id
2216 AND source_code = 'WICDOL'
2217 AND source_line_id = p_line_id;
2218
2219 IF (l_response_code = WIP_CONSTANTS.ALWAYS) THEN
2220 CLOSE l_always_cursor;
2221 ELSE
2222 CLOSE l_1to1_cursor;
2223 END IF;
2224 x_status := fnd_api.g_ret_sts_success;
2225
2226 EXCEPTION
2227 WHEN others THEN
2228 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2229 p_count => x_msg_count,
2230 p_data => x_msg_data);
2231 x_status := fnd_api.g_ret_sts_error;
2232 IF (l_response_code = WIP_CONSTANTS.ALWAYS) THEN
2233 CLOSE l_always_cursor;
2234 ELSE
2235 CLOSE l_1to1_cursor;
2236 END IF;
2237
2238
2239 END respond_to_change_order;
2240
2241 /*3017570*/
2242 -- ---------------------------------------------------------------------------
2243 --
2244 -- PROCEDURE get_move_transaction_lines
2245 --
2246 -- This procedure returns a table of move transaction records from
2247 -- wip_move_txn_interface
2248 -- Used to match with WIP reservation while processing scrap transactions.
2249 --
2250 --
2251 -- Internal helper
2252 -- ---------------------------------------------------------------------------
2253
2254 PROCEDURE get_move_transaction_lines (
2255 p_group_id IN NUMBER,
2256 p_wip_entity_id IN NUMBER,
2257 x_return_status OUT NOCOPY VARCHAR2,
2258 x_move_transaction_tbl OUT NOCOPY move_transaction_intf_tbl_type) IS
2259
2260 Cursor c_move_transaction_lines (p_group_id number, p_wip_entity_id number) is
2261 select wip_entity_id,
2262 transaction_id,
2263 transaction_type,
2264 organization_id,
2265 primary_item_id,
2266 fm_operation_seq_num,
2267 fm_intraoperation_step_type,
2268 to_operation_seq_num,
2269 to_intraoperation_step_type,
2270 primary_quantity,
2271 primary_uom,
2272 entity_type,
2273 repetitive_schedule_id,
2274 transaction_date
2275 from wip_move_txn_interface wmti
2276 where wmti.group_id = p_group_id
2277 and wmti.process_phase = 2
2278 and wmti.process_status = 2
2279 and wmti.wip_entity_id = p_wip_entity_id
2280 order by transaction_id;
2281
2282
2283 l_routine_name VARCHAR2(30) := 'GET_MOVE_TRANSACTION_LINES';
2284 l_counter NUMBER := 0;
2285 l_move_transaction_rec move_transaction_intf_rec_type;
2286
2287 BEGIN
2288 x_return_status := fnd_api.g_ret_sts_unexp_error;
2289
2290 /* dbms_output.put_line ('inside get_move_transaction_lines');
2291 dbms_output.put_line ('p_wip_entity_id: '|| p_wip_entity_id);
2292 dbms_output.put_line ('p_group_id : '|| p_group_id); */
2293
2294 OPEN c_move_transaction_lines(p_group_id, p_wip_entity_id);
2295 LOOP
2296 -- dbms_output.put_line ('inside the loop');
2297 FETCH c_move_transaction_lines INTO l_move_transaction_rec;
2298 EXIT WHEN c_move_transaction_lines%NOTFOUND;
2299 l_counter := l_counter + 1;
2300 x_move_transaction_tbl(l_counter) := l_move_transaction_rec;
2301 END LOOP;
2302 CLOSE c_move_transaction_lines;
2303 x_return_status := fnd_api.g_ret_sts_success;
2304 EXCEPTION
2305 WHEN OTHERS THEN
2306 IF (c_move_transaction_lines%ISOPEN) THEN
2307 CLOSE c_move_transaction_lines;
2308 END IF;
2309 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2310 fnd_msg_pub.add_exc_msg(g_package_name, l_routine_name);
2311 END IF;
2312
2313 END get_move_transaction_lines;
2314
2315 -- ---------------------------------------------------------------------------
2316 --
2317 -- PROCEDURE scrap_txn_relieve_rsv
2318 --
2319 -- This procedure processes the scrap transactions in a batch
2320 -- identified by group_id and modifies/deletes the reservations
2321 -- from the discrete jobs in the descending order of requirement date.
2322 --
2323 --
2324 -- Internal helper
2325 -- ---------------------------------------------------------------------------
2326
2327 Procedure scrap_txn_relieve_rsv ( p_group_id IN NUMBER,
2328 x_return_status OUT NOCOPY VARCHAR2,
2329 x_msg_count OUT NOCOPY NUMBER,
2330 x_msg_data OUT NOCOPY VARCHAR2) is
2331
2332 --Bug 4744367:Added check on wmti.entity_type so that for lot based jobs
2333 --scrap is not relieved.
2334 Cursor wmti_disc_jobs (p_group_id number) is
2335 select distinct wdj.wip_entity_id,
2336 wdj.organization_id,
2337 wdj.primary_item_id,
2338 wdj.start_quantity,
2339 wdj.quantity_completed,
2340 wdj.quantity_scrapped
2341 from wip_move_txn_interface wmti, wip_discrete_jobs wdj
2342 where wmti.group_id = p_group_id
2343 and wmti.wip_entity_id = wdj.wip_entity_id
2344 and wmti.organization_id = wdj.organization_id
2345 and wmti.process_phase = 2
2346 and wmti.process_status = 2
2347 and nvl(wmti.entity_type,1) <> 5
2348 order by wdj.wip_entity_id;
2349
2350 l_wip_entity_id Number;
2351 l_organization_id Number;
2352 l_primary_item_id Number;
2353 l_job_start_quantity Number;
2354 l_quantity_scrapped Number;
2355 l_quantity_completed Number;
2356 l_job_reservation_quantity Number;
2357 l_return_status Varchar2(1);
2358 l_move_transaction_tbl move_transaction_intf_tbl_type;
2359 l_job_unreserved_quantity Number;
2360 move_txn_count Number;
2361 l_scrap_primary_txn_quantity Number;
2362 l_msg_count NUMBER;
2363 l_msg_data VARCHAR2(2000);
2364 l_routine_name VARCHAR2(30) := 'SCRAP_TXN_RELIEVE_RSV' ;
2365
2366
2367
2368 Begin
2369 SAVEPOINT Relieve_Rsv_scrap_txn_sp;
2370
2371 fnd_msg_pub.initialize;
2372
2373 For current_job in wmti_disc_jobs(p_group_id)
2374 Loop
2375 l_wip_entity_id := current_job.wip_entity_id;
2376 l_organization_id := current_job.organization_id;
2377 l_primary_item_id := current_job.primary_item_id;
2378 l_job_start_quantity := current_job.start_quantity;
2379 l_quantity_scrapped := current_job.quantity_scrapped;
2380 l_quantity_completed := current_job.quantity_completed;
2381
2382 select nvl(sum(primary_quantity),0)
2383 into l_job_reservation_quantity
2384 from wip_reservations_v
2385 where wip_entity_id = l_wip_entity_id;
2386 /*
2387 dbms_output.put_line ('wip_entity_id: '||l_wip_entity_id );
2388 dbms_output.put_line ('l_job_start_quantity : '||l_job_start_quantity );
2389 dbms_output.put_line ('l_quantity_scrapped : '|| l_quantity_scrapped );
2390 dbms_output.put_line ('l_quantity_completed : '||l_quantity_completed );
2391 dbms_output.put_line ('l_job_reservation_quantity: '||l_job_reservation_quantity );
2392 */
2393
2394 If (l_job_reservation_quantity > 0 ) then
2395 l_job_unreserved_quantity := Greatest(l_job_start_quantity - l_quantity_completed
2396 - l_quantity_scrapped - l_job_reservation_quantity, 0);
2397
2398 -- dbms_output.put_line ('l_job_unreserved_quantity: '||l_job_unreserved_quantity );
2399
2400
2401 get_move_transaction_lines(p_group_id => p_group_id,
2402 p_wip_entity_id => l_wip_entity_id,
2403 x_return_status => l_return_status,
2404 x_move_transaction_tbl => l_move_transaction_tbl);
2405
2406 If (l_return_status <> fnd_api.g_ret_sts_success) then
2407 If (l_return_status = fnd_api.g_ret_sts_error) then
2408 RAISE fnd_api.g_exc_error;
2409 Else
2410 RAISE fnd_api.g_exc_unexpected_error;
2411 End if;
2412 End if;
2413
2414 move_txn_count := l_move_transaction_tbl.count;
2415
2416 -- dbms_output.put_line('transaction record count: '|| move_txn_count );
2417
2418 For i in 1 .. move_txn_count
2419 Loop
2420 If ((l_move_transaction_tbl(i).to_intraoperation_step_type = WIP_CONSTANTS.SCRAP) and
2421 (l_move_transaction_tbl(i).to_intraoperation_step_type <> l_move_transaction_tbl(i).fm_intraoperation_step_type)) then
2422 l_scrap_primary_txn_quantity := l_move_transaction_tbl(i).primary_quantity;
2423
2424 -- dbms_output.put_line ('l_scrap_primary_txn_quantity : '|| l_scrap_primary_txn_quantity );
2425
2426 If ( l_job_unreserved_quantity > l_scrap_primary_txn_quantity ) then
2427 l_job_unreserved_quantity := l_job_unreserved_quantity - l_scrap_primary_txn_quantity ;
2428 l_scrap_primary_txn_quantity := 0;
2429 Else
2430 l_scrap_primary_txn_quantity := l_scrap_primary_txn_quantity - l_job_unreserved_quantity;
2431 l_job_unreserved_quantity := 0;
2432 End if;
2433
2434 -- dbms_output.put_line ('after if condition --l_scrap_primary_txn_quantity : '|| l_scrap_primary_txn_quantity );
2435
2436 If (l_scrap_primary_txn_quantity > 0 ) then
2437 Relieve_wip_reservation( p_wip_entity_id => l_wip_entity_id,
2438 p_organization_id => l_organization_id,
2439 p_inventory_item_id => l_primary_item_id,
2440 p_primary_quantity => l_scrap_primary_txn_quantity,
2441 x_return_status => l_return_status,
2442 x_msg_count => l_msg_count,
2443 x_msg_data => l_msg_data );
2444 If (l_return_status <> fnd_api.g_ret_sts_success) then
2445 If (l_return_status = fnd_api.g_ret_sts_error) then
2446 RAISE fnd_api.g_exc_error;
2447 Else
2448 RAISE fnd_api.g_exc_unexpected_error;
2449 End if;
2450 End if;
2451 End if;
2452 End if;
2453 End Loop;
2454 End if;
2455 End Loop;
2456 Exception
2457 WHEN g_need_to_rollback_exception THEN
2458 ROLLBACK TO SAVEPOINT Relieve_Rsv_scrap_txn_sp;
2459
2460 WHEN fnd_api.g_exc_error THEN
2461
2462 x_return_status := fnd_api.g_ret_sts_error;
2463
2464 fnd_msg_pub.count_and_get(
2465 p_encoded => fnd_api.g_false,
2466 p_count => x_msg_count,
2467 p_data => x_msg_data);
2468
2469 ROLLBACK TO SAVEPOINT Relieve_Rsv_scrap_txn_sp;
2470
2471 WHEN fnd_api.g_exc_unexpected_error THEN
2472
2473 x_return_status := fnd_api.g_ret_sts_unexp_error;
2474
2475 fnd_msg_pub.count_and_get(
2476 p_encoded => fnd_api.g_false,
2477 p_count => x_msg_count,
2478 p_data => x_msg_data);
2479
2480 ROLLBACK TO SAVEPOINT Relieve_Rsv_scrap_txn_sp;
2481
2482 WHEN OTHERS THEN
2483
2484 x_return_status := fnd_api.g_ret_sts_unexp_error;
2485 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
2486 fnd_msg_pub.add_exc_msg (
2487 g_package_name,
2488 l_routine_name);
2489 END IF;
2490
2491 fnd_msg_pub.count_and_get(
2492 p_encoded => fnd_api.g_false,
2493 p_count => x_msg_count,
2494 p_data => x_msg_data);
2495
2496 ROLLBACK TO SAVEPOINT Relieve_Rsv_scrap_txn_sp;
2497
2498 End scrap_txn_relieve_rsv;
2499
2500 -- ---------------------------------------------------------------------------
2501 --
2502 -- PROCEDURE Relieve_wip_reservation
2503 --
2504 -- This procedure modifies/deletes the reservations
2505 -- from the discrete jobs in the descending order of requirement date.
2506 -- Internal helper
2507 -- ---------------------------------------------------------------------------
2508
2509 PROCEDURE Relieve_wip_reservation(
2510 p_wip_entity_id IN Number,
2511 p_organization_id IN Number,
2512 p_inventory_item_id IN Number,
2513 p_primary_quantity IN Number,
2514 x_return_status OUT NOCOPY VARCHAR2,
2515 x_msg_count OUT NOCOPY NUMBER,
2516 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2517
2518 l_routine_name VARCHAR2(30) := 'RELIEVE_WIP_RESERVATION';
2519 l_quantity_to_be_relieved Number;
2520 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
2521 l_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
2522 l_rsv_tbl_count NUMBER;
2523 l_return_status VARCHAR2(1);
2524 l_msg_count NUMBER;
2525 l_msg_data VARCHAR2(2000);
2526 l_to_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
2527 l_error_code NUMBER;
2528 l_line_reservation_quantity NUMBER;
2529 l_new_line_rsv_quantity NUMBER;
2530 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2531
2532
2533 BEGIN
2534 fnd_msg_pub.initialize;
2535 l_quantity_to_be_relieved := p_primary_quantity;
2536
2537 -- dbms_output.put_line('l_quantity_to_be_relieved : '||l_quantity_to_be_relieved );
2538
2539 l_reservation_rec.organization_id := p_organization_id;
2540 l_reservation_rec.supply_source_header_id := p_wip_entity_id;
2541 l_reservation_rec.inventory_item_id := p_inventory_item_id;
2542 l_reservation_rec.supply_source_type_id := inv_reservation_global.g_source_type_wip;
2543
2544
2545 -- query reservations against this particular WIP job.
2546 -- tell API to lock rows in mtl_reservations.
2547 -- records are returned based on requirement date ascending.
2548 inv_reservation_pub.query_reservation(
2549 p_api_version_number => 1.0,
2550 p_init_msg_lst => fnd_api.g_false,
2551 x_return_status => l_return_status,
2552 x_msg_count => l_msg_count,
2553 x_msg_data => l_msg_data,
2554 p_query_input => l_reservation_rec,
2555 p_lock_records => fnd_api.g_true,
2556 p_sort_by_req_date => inv_reservation_global.g_query_req_date_desc,
2557 x_mtl_reservation_tbl => l_reservation_tbl,
2558 x_mtl_reservation_tbl_count => l_rsv_tbl_count,
2559 x_error_code => l_error_code);
2560
2561 If (l_return_status <> fnd_api.g_ret_sts_success) then
2562 x_msg_count := l_msg_count;
2563 x_msg_data := l_msg_data;
2564 x_return_status := l_return_status;
2565 RAISE g_need_to_rollback_exception;
2566 End if;
2567
2568 -- dbms_output.put_line ('after query_reservation');
2569 -- dbms_output.put_line ('l_rsv_tbl_count : '|| l_rsv_tbl_count );
2570 For j in 1 .. l_rsv_tbl_count
2571 Loop
2572 l_line_reservation_quantity := l_reservation_tbl(j).primary_reservation_quantity;
2573
2574 -- dbms_output.put_line ('l_line_reservation_quantity :'|| l_line_reservation_quantity );
2575 If ( l_quantity_to_be_relieved >= l_line_reservation_quantity ) then
2576 --delete the reservation
2577 -- dbms_output.put_line ('about to delete the reservation');
2578 inv_reservation_pub.delete_reservation(
2579 p_api_version_number => 1.0,
2580 p_init_msg_lst => fnd_api.g_false,
2581 x_return_status => l_return_status,
2582 x_msg_count => l_msg_count,
2583 x_msg_data => l_msg_data,
2584 p_rsv_rec => l_reservation_tbl(j),
2585 p_serial_number => l_dummy_sn);
2586
2587 If (l_return_status <> fnd_api.g_ret_sts_success) then
2588 x_msg_count := l_msg_count;
2589 x_msg_data := l_msg_data;
2590 x_return_status := l_return_status;
2591 RAISE g_need_to_rollback_exception;
2592 End if;
2593
2594 l_quantity_to_be_relieved := l_quantity_to_be_relieved - l_line_reservation_quantity ;
2595 Else
2596 l_new_line_rsv_quantity := l_line_reservation_quantity - l_quantity_to_be_relieved ;
2597 l_quantity_to_be_relieved := 0;
2598 -- update the reservation
2599 l_to_reservation_rec := l_reservation_tbl(j);
2600 l_to_reservation_rec.primary_reservation_quantity := l_new_line_rsv_quantity;
2601 inv_reservation_pub.update_reservation(
2602 p_api_version_number => 1.0,
2603 p_init_msg_lst => fnd_api.g_false,
2604 x_return_status => l_return_status,
2605 x_msg_count => l_msg_count,
2606 x_msg_data => l_msg_data,
2607 p_original_rsv_rec => l_reservation_tbl(j),
2608 p_to_rsv_rec => l_to_reservation_rec,
2609 p_original_serial_number => l_dummy_sn,
2610 p_to_serial_number => l_dummy_sn,
2611 p_validation_flag => fnd_api.g_true);
2612 If (l_return_status <> fnd_api.g_ret_sts_success) then
2613 x_msg_count := l_msg_count;
2614 x_msg_data := l_msg_data;
2615 x_return_status := l_return_status;
2616 RAISE g_need_to_rollback_exception;
2617 End if;
2618 End if;
2619
2620 If (l_quantity_to_be_relieved = 0) then
2621 EXIT;
2622 End if;
2623 End loop;
2624
2625 x_return_status := fnd_api.g_ret_sts_success;
2626
2627 EXCEPTION
2628 WHEN OTHERS THEN
2629 x_return_status := fnd_api.g_ret_sts_unexp_error;
2630 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2631 fnd_msg_pub.add_exc_msg(g_package_name, l_routine_name);
2632 END IF;
2633 END Relieve_wip_reservation;
2634
2635 -- Fixed bug 5471890. Need to create PL/SQL wrapper when calling inventory
2636 -- reservation API since some environment failed to compile if we try to
2637 -- reference PL/SQL object from form directly.
2638 PROCEDURE update_row(p_item_revision IN VARCHAR2,
2639 p_reservation_id IN NUMBER,
2640 p_requirement_date IN DATE,
2641 p_demand_source_header_id IN NUMBER,
2642 p_demand_source_line_id IN NUMBER,
2643 p_primary_quantity IN NUMBER,
2644 p_wip_entity_id IN NUMBER,
2645 x_return_status OUT NOCOPY VARCHAR2) IS
2646
2647 l_msg_count NUMBER;
2648 l_msg_data VARCHAR2(240);
2649 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2650 l_rsv_array inv_reservation_global.mtl_reservation_tbl_type;
2651 l_size NUMBER;
2652 l_error_code NUMBER;
2653 BEGIN
2654 inv_reservation_form_pkg.query_reservation(
2655 p_api_version_number => 1.0,
2656 p_reservation_id => p_reservation_id,
2657 p_init_msg_lst => fnd_api.g_true,
2658 p_lock_records => fnd_api.g_true,
2659 p_sort_by_req_date => 1 /* no sort */,
2660 p_cancel_order_mode => 1,
2661 x_return_status => x_return_status,
2662 x_msg_count => l_msg_count,
2663 x_msg_data => l_msg_data,
2664 x_mtl_reservation_tbl => l_rsv_array,
2665 x_mtl_reservation_tbl_count => l_size,
2666 x_error_code => l_error_code);
2667
2668 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
2669 raise fnd_api.g_exc_unexpected_error;
2670 END IF;
2671
2672 inv_reservation_form_pkg.update_reservation(
2673 p_api_version_number => 1.0,
2674 p_init_msg_lst => fnd_api.g_true,
2675 p_from_reservation_id => p_reservation_id,
2676 p_from_requirement_date => NULL,
2677 p_from_organization_id => NULL,
2678 p_from_inventory_item_id => NULL,
2679 p_from_demand_type_id => NULL,
2680 p_from_demand_name => NULL,
2681 p_from_demand_header_id => NULL,
2682 p_from_demand_line_id => NULL,
2683 p_from_primary_uom_code => NULL,
2684 p_from_primary_uom_id => NULL,
2685 p_from_reservation_uom_code => NULL,
2686 p_from_reservation_uom_id => NULL,
2687 p_from_reservation_quantity => NULL,
2688 p_from_primary_rsv_quantity => NULL,
2689 p_from_autodetail_group_id => NULL,
2690 p_from_external_source_code => NULL,
2691 p_from_external_source_line => NULL,
2692 p_from_supply_type_id => NULL,
2693 p_from_supply_header_id => NULL,
2694 p_from_supply_line_id => NULL,
2695 p_from_supply_name => NULL,
2696 p_from_supply_line_detail => NULL,
2697 p_from_revision => NULL,
2698 p_from_subinventory_code => NULL,
2699 p_from_subinventory_id => NULL,
2700 p_from_locator_id => NULL,
2701 p_from_lot_number => NULL,
2702 p_from_lot_number_id => NULL,
2703 p_from_pick_slip_number => NULL,
2704 p_from_lpn_id => NULL,
2705 p_from_ship_ready_flag => NULL,
2706 p_to_requirement_Date => p_requirement_date,
2707 p_to_demand_type_id => l_rsv_array(1).demand_source_type_id,
2708 p_to_demand_name => l_rsv_array(1).demand_source_name,
2709 p_to_demand_header_id => p_demand_source_header_id,
2710 p_to_demand_line_id => p_demand_source_line_id,
2711 p_to_demand_delivery_id => l_rsv_array(1).demand_source_delivery,
2712 p_to_reservation_uom_code => l_rsv_array(1).reservation_uom_code,
2713 p_to_reservation_uom_id => l_rsv_array(1).reservation_uom_id,
2714 p_to_reservation_quantity => l_rsv_array(1).reservation_quantity,
2715 p_to_primary_rsv_quantity => p_primary_quantity,
2716 p_to_autodetail_group_id => l_rsv_array(1).autodetail_group_id,
2717 p_to_external_source_code => l_rsv_array(1).external_source_code,
2718 p_to_external_source_line => l_rsv_array(1).external_source_line_id,
2719 p_to_supply_type_id => l_rsv_array(1).supply_source_type_id,
2720 p_to_supply_header_id => p_wip_entity_id,
2721 p_to_supply_line_id => l_rsv_array(1).supply_source_line_id,
2722 p_to_supply_name => l_rsv_array(1).supply_source_name,
2723 p_to_supply_line_detail => l_rsv_array(1).supply_source_line_detail,
2724 p_to_revision => p_item_revision,
2725 p_to_subinventory_code => l_rsv_array(1).subinventory_code,
2726 p_to_subinventory_id => l_rsv_array(1).subinventory_id,
2727 p_to_locator_id => l_rsv_array(1).locator_id,
2728 p_to_lot_number => l_rsv_array(1).lot_number,
2729 p_to_lot_number_id => l_rsv_array(1).lot_number_id,
2730 p_to_pick_slip_number => l_rsv_array(1).pick_slip_number,
2731 p_to_lpn_id => l_rsv_array(1).lpn_id,
2732 p_to_ship_ready_flag => l_rsv_array(1).ship_ready_flag,
2733 p_to_attribute_category => l_rsv_array(1).attribute_category,
2734 p_to_attribute1 => l_rsv_array(1).attribute1,
2735 p_to_attribute2 => l_rsv_array(1).attribute2,
2736 p_to_attribute3 => l_rsv_array(1).attribute3,
2737 p_to_attribute4 => l_rsv_array(1).attribute4,
2738 p_to_attribute5 => l_rsv_array(1).attribute5,
2739 p_to_attribute6 => l_rsv_array(1).attribute6,
2740 p_to_attribute7 => l_rsv_array(1).attribute7,
2741 p_to_attribute8 => l_rsv_array(1).attribute8,
2742 p_to_attribute9 => l_rsv_array(1).attribute9,
2743 p_to_attribute10 => l_rsv_array(1).attribute10,
2744 p_to_attribute11 => l_rsv_array(1).attribute11,
2745 p_to_attribute12 => l_rsv_array(1).attribute12,
2746 p_to_attribute13 => l_rsv_array(1).attribute13,
2747 p_to_attribute14 => l_rsv_array(1).attribute14,
2748 p_to_attribute15 => l_rsv_array(1).attribute15,
2749 p_validation_flag => fnd_api.g_true,
2750 /* Changes for Inventory */
2751 p_from_serial_number_tbl => l_dummy_sn,
2752 p_from_crossDock_flag => NULL,
2753 p_to_serial_number_tbl => l_dummy_sn,
2754 p_to_crossDock_flag => NULL,
2755 /* End of Changes */
2756 x_return_status => x_return_status,
2757 x_msg_count => l_msg_count,
2758 x_msg_data => l_msg_data);
2759
2760 IF(x_return_status <> fnd_api.g_ret_sts_success) THEN
2761 raise fnd_api.g_exc_unexpected_error;
2762 END IF;
2763
2764 x_return_status := fnd_api.g_ret_sts_success;
2765 EXCEPTION
2766 WHEN others THEN
2767 x_return_status := fnd_api.g_ret_sts_unexp_error;
2768 END update_row;
2769
2770 PROCEDURE lock_row(p_reservation_id IN NUMBER,
2771 x_reservation_id OUT NOCOPY NUMBER,
2772 x_supply_source_header_id OUT NOCOPY NUMBER,
2773 x_organization_id OUT NOCOPY NUMBER,
2774 x_demand_source_header_id OUT NOCOPY NUMBER,
2775 x_primary_reservation_quantity OUT NOCOPY NUMBER,
2776 x_demand_source_line_id OUT NOCOPY NUMBER,
2777 x_size OUT NOCOPY NUMBER,
2778 x_return_status OUT NOCOPY VARCHAR2) IS
2779
2780 l_msg_count NUMBER;
2781 l_msg_data VARCHAR2(240);
2782 l_rsv_array inv_reservation_global.mtl_reservation_tbl_type;
2783 l_error_code NUMBER;
2784 BEGIN
2785 inv_reservation_form_pkg.query_reservation(
2786 p_api_version_number => 1.0,
2787 p_reservation_id => p_reservation_id,
2788 p_init_msg_lst => fnd_api.g_true,
2789 p_lock_records => fnd_api.g_true,
2790 p_sort_by_req_date => 1 /* no sort */,
2791 p_cancel_order_mode => 1,
2792 x_return_status => x_return_status,
2793 x_msg_count => l_msg_count,
2794 x_msg_data => l_msg_data,
2795 x_mtl_reservation_tbl => l_rsv_array,
2796 x_mtl_reservation_tbl_count => x_size,
2797 x_error_code => l_error_code);
2798
2799 -- Set OUT parameters
2800 x_reservation_id := l_rsv_array(1).reservation_id;
2801 x_supply_source_header_id := l_rsv_array(1).supply_source_header_id;
2802 x_organization_id := l_rsv_array(1).organization_id;
2803 x_demand_source_header_id := l_rsv_array(1).demand_source_header_id;
2804 x_primary_reservation_quantity := l_rsv_array(1).primary_reservation_quantity;
2805 x_demand_source_line_id := l_rsv_array(1).demand_source_line_id;
2806
2807 END lock_row;
2808
2809 END WIP_SO_RESERVATIONS;