[Home] [Help]
PACKAGE BODY: APPS.WMS_WIP_XDOCK_PVT
Source
1 PACKAGE BODY wms_wip_xdock_pvt AS
2 /* $Header: WMSWIPCB.pls 120.4 2005/12/29 23:57:11 vpalaniv ship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_WIP_XDock_Pvt';
6 g_header_printed BOOLEAN := FALSE;
7 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
8
9 PROCEDURE mydebug(msg IN VARCHAR2) IS
10 l_msg VARCHAR2(5100);
11 l_ts VARCHAR2(30);
12 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
13 BEGIN
14 SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MM:SS')
15 INTO l_ts
16 FROM DUAL;
17
18 l_msg := l_ts || ' ' || msg;
19
20 IF (g_header_printed = FALSE) THEN
21 inv_mobile_helper_functions.tracelog(p_err_msg => '$Header: WMSWIPCB.pls 120.4 2005/12/29 23:57:11 vpalaniv ship $', p_module => g_pkg_name || ' - ' || 'wms_cross_dock_pvt', p_level => 4);
22 g_header_printed := TRUE;
23 END IF;
24
25 inv_mobile_helper_functions.tracelog(p_err_msg => g_user_name || ': ' || l_msg, p_module => 'wms_cross_dock_pvt', p_level => 4);
26 --dbms_output.put_line(msg);
27 NULL;
28 END mydebug;
29
30 PROCEDURE wip_chk_crossdock(
31 p_org_id IN NUMBER
32 , p_lpn IN NUMBER := NULL
33 , x_ret OUT NOCOPY NUMBER
34 , x_return_status OUT NOCOPY VARCHAR2
35 , x_msg_count OUT NOCOPY NUMBER
36 , x_msg_data OUT NOCOPY VARCHAR2
37 , p_move_order_line_id IN NUMBER DEFAULT NULL -- added for ATF_J
38 ) IS
39 l_wip_id NUMBER;
40 l_wip_item NUMBER;
41 l_wip_qty NUMBER;
42 l_wip_uom VARCHAR2(3);
43 l_count NUMBER;
44 l_ret NUMBER;
45 l_lpn_qty NUMBER;
46 l_new_qty NUMBER;
47 l_org_id NUMBER;
48 l_inventory_item_id NUMBER;
49 l_qty NUMBER;
50 l_uom VARCHAR2(3);
51 l_lpn_id NUMBER;
52 l_project_id NUMBER := NULL;
53 l_task_id NUMBER := NULL;
54 l_reference VARCHAR2(240) := NULL;
55 l_reference_type_code NUMBER := NULL;
56 l_reference_id NUMBER := NULL;
57 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
58 l_lot_number VARCHAR2(80);
59 l_revision VARCHAR2(3);
60 l_header_id NUMBER := NULL;
61 l_sub VARCHAR2(10) := NULL;
62 l_loc NUMBER := NULL;
63 l_to_sub VARCHAR2(10) := NULL;
64 l_to_loc NUMBER := NULL;
65 l_inspection_status NUMBER := NULL;
66 l_txn_source_id NUMBER;
67 l_transaction_type_id NUMBER;
68 l_transaction_source_type_id NUMBER;
69 l_line_id NUMBER;
70 l_partial_alloc_qty NUMBER;
71 l_return_status VARCHAR2(1);
72 l_trip_stop_id NUMBER;
73 b_no_more_lines BOOLEAN;
74 l_shipping_attr wsh_interface.changedattributetabtype;
75 b_wip_not_fulfilled BOOLEAN;
76 l_msg_count NUMBER;
77 l_msg_data VARCHAR2(240);
78 --b_no_more_lines boolean;
79 l_cnt1 NUMBER;
80 l_insp_cnt NUMBER := 0;
81 l_wip_entity_id NUMBER;
82 l_operation_seq_num NUMBER;
83 l_repetitive_schedule_id NUMBER;
84 l_primary_uom VARCHAR2(3);
85 l_wip_issue_flag VARCHAR2(1) := NULL;
86 l_subinventory_code VARCHAR2(50) := NULL;
87 l_locator_id NUMBER := NULL;
88 indx NUMBER := 0;
89 l_quantity_allocated NUMBER := 0;
90
91 CURSOR wip_csr IS
92 SELECT w.wip_entity_id wip_entity_id
93 , w.quantity_backordered requested_quantity
94 , w.inventory_item_id inventory_item_id
95 , w.repetitive_schedule_id
96 , w.operation_seq_num
97 , w.wip_issue_flag
98 , w.subinventory_code
99 , w.locator_id
100 FROM wip_material_shortages_v w, mtl_txn_request_lines l
101 WHERE l.lpn_id = l_lpn_id
102 AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- added for ATF_J
103 AND l.organization_id = l_org_id
104 AND w.organization_id = l_org_id
105 AND NVL(l.project_id, -999) = NVL(w.project_id, -999)
106 AND NVL(l.task_id, -999) = NVL(w.task_id, -999)
107 AND w.inventory_item_id = l.inventory_item_id
108 AND l.backorder_delivery_detail_id IS NULL
109 AND(l.quantity_detailed IS NULL
110 OR l.quantity_delivered IS NULL
111 OR l.quantity_delivered = 0) ;
112
113 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
114 l_tmp_line_id NUMBER;
115 BEGIN
116 l_return_status := fnd_api.g_ret_sts_success;
117
118 IF (l_debug = 1) THEN
119 mydebug('In WIP crossdock check api...');
120 END IF;
121
122 l_lpn_id := p_lpn;
123 l_org_id := p_org_id;
124 l_ret := 1;
125 l_count := 0;
126 l_partial_alloc_qty := 0;
127 b_no_more_lines := FALSE;
128 b_wip_not_fulfilled := FALSE;
129 -- b_no_more_lines :=FALSE;
130 l_cnt1 := 0;
131 l_insp_cnt := 0;
132 -- First check to see if there are any crossdock opportunities
133 -- at all
134 wms_task_dispatch_put_away.crdk_wip_info_table.DELETE;
135 wms_task_dispatch_put_away.crdk_wip_table_index := 0;
136
137 BEGIN
138 SELECT 1
139 INTO l_count
140 FROM DUAL
141 WHERE EXISTS(
142 SELECT 1
143 FROM wip_material_shortages_v b, mtl_txn_request_lines l
144 WHERE l.lpn_id = l_lpn_id
145 AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- added for ATF_J
146 AND l.organization_id = l_org_id
147 AND b.organization_id = l.organization_id
148 AND NVL(l.project_id, -999) = NVL(b.project_id, -999)
149 AND NVL(l.task_id, -999) = NVL(b.task_id, -999)
150 AND b.inventory_item_id = l.inventory_item_id
151 AND l.backorder_delivery_detail_id IS NULL
152 AND(l.quantity_detailed IS NULL
153 OR l.quantity_detailed = 0));
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 l_count := 0;
157 END;
158
159 IF (l_debug = 1) THEN
160 mydebug('l_count ' || l_count);
161 END IF;
162
163 -- Checking to see if material is rejected
164 BEGIN
165 SELECT 1
166 INTO l_insp_cnt
167 FROM DUAL
168 WHERE EXISTS(
169 SELECT 1
170 FROM mtl_txn_request_lines
171 WHERE lpn_id = l_lpn_id
172 AND line_id = NVL(p_move_order_line_id, line_id) -- added for ATF_J
173 AND organization_id = l_org_id
174 AND NVL(inspection_status, 2) = 3);
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 l_insp_cnt := 0;
178 END;
179
180 IF (l_debug = 1) THEN
181 mydebug('l_insp_cnt ' || l_insp_cnt);
182 END IF;
183
184 IF (l_count > 0
185 AND l_insp_cnt = 0) THEN
186 IF (l_debug = 1) THEN
187 mydebug('CrossDock Opp exists and material has not been rejected');
188 END IF;
189
190 OPEN wip_csr;
191
192 LOOP
193 IF (l_debug = 1) THEN
194 mydebug('Fetching from wip csr');
195 END IF;
196
197 FETCH wip_csr
198 INTO l_wip_id
199 , l_wip_qty
200 , l_wip_item
201 , l_repetitive_schedule_id
202 , l_operation_seq_num
203 , l_wip_issue_flag
204 , l_subinventory_code
205 , l_locator_id;
206
207 EXIT WHEN wip_csr%NOTFOUND;
208
209 -- get loc and sub if trip stip id is null
210 -- else call api to get staging sub and loc
211 -- IF b_no_more_lines THEN
212 -- EXIT;
213 -- END IF;
214 IF (l_debug = 1) THEN
215 mydebug('WIP Id:' || l_wip_id);
216 END IF;
217
218 b_wip_not_fulfilled := FALSE;
219 l_ret := 0;
220
221 --IF l_cnt1>=l_count THEN
222 -- mydebug('No More MOLs');
223 --EXIT;
224 --END IF;
225 SELECT default_crossdock_subinventory
226 , default_crossdock_locator_id
227 INTO l_to_sub
228 , l_to_loc
229 FROM mtl_parameters
230 WHERE organization_id = l_org_id;
231
232 IF ((l_to_sub IS NULL)
233 OR(l_to_loc IS NULL)) THEN
234 SELECT default_stage_subinventory
235 , default_stage_locator_id
236 INTO l_to_sub
237 , l_to_loc
238 FROM wsh_shipping_parameters
239 WHERE organization_id = l_org_id;
240 END IF;
241
242 -- Check against mo lines
243
244 -- comment the below line for back flush testing
245 --l_wip_issue_flag := 'Y';
246 IF (l_wip_issue_flag <> 'Y') THEN
247 IF (l_debug = 1) THEN
248 mydebug(' Shortage is not for a wip issue ');
249 END IF;
250
251 IF ((l_subinventory_code IS NOT NULL)
252 AND(l_locator_id IS NOT NULL)) THEN
253 IF (l_debug = 1) THEN
254 mydebug(' subinventory AND LOC is not null on the line');
255 END IF;
256
257 l_to_sub := l_subinventory_code;
258 l_to_loc := l_locator_id;
259 ELSE
260 IF (l_debug = 1) THEN
261 mydebug(' subinventory is null on the line');
262 mydebug(' using shipping parameters sub and loc ');
263 END IF;
264 END IF;
265 END IF;
266
267 IF (l_debug = 1) THEN
268 mydebug('Staging Sub' || l_to_sub);
269 END IF;
270
271 l_partial_alloc_qty := 0;
272
273 -- OPEN lpn_csr;
274 LOOP
275 BEGIN
276 SELECT mol.organization_id
277 , mol.inventory_item_id
278 , mol.quantity
279 , mol.uom_code
280 , mol.lot_number
281 , mol.revision
282 , mol.project_id
283 , mol.task_id
284 , mol.REFERENCE
285 , mol.reference_type_code
286 , mol.reference_id
287 , mol.header_id
288 , mol.txn_source_id
289 , mol.transaction_type_id
290 , mol.transaction_source_type_id
291 , mol.from_subinventory_code
292 , mol.from_locator_id
293 , mol.inspection_status
294 , mol.line_id
295 , msi.primary_uom_code
296 INTO l_org_id
297 , l_inventory_item_id
298 , l_qty
299 , l_uom
300 , l_lot_number
301 , l_revision
302 , l_project_id
303 , l_task_id
304 , l_reference
305 , l_reference_type_code
306 , l_reference_id
307 , l_header_id
308 , l_txn_source_id
309 , l_transaction_type_id
310 , l_transaction_source_type_id
311 , l_sub
312 , l_loc
313 , l_inspection_status
314 , l_line_id
315 , l_primary_uom
316 FROM mtl_txn_request_lines mol, mtl_system_items msi
317 WHERE lpn_id = l_lpn_id
318 AND line_id = NVL(p_move_order_line_id, line_id) -- added for ATF_J
319 AND mol.organization_id = l_org_id
320 AND mol.inventory_item_id = l_wip_item
321 AND mol.backorder_delivery_detail_id IS NULL
322 AND mol.inventory_item_id = msi.inventory_item_id
323 AND mol.organization_id = msi.organization_id
324 AND ROWNUM = 1;
325
326 IF (l_debug = 1) THEN
327 mydebug('MOL:' || l_line_id);
328 END IF;
329
330 l_cnt1 := l_cnt1 + 1;
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 IF (l_debug = 1) THEN
334 mydebug('No data');
335 END IF;
336
337 --b_no_more_lines:=TRUE;
338 /*IF b_wip_not_fulfilled THEN
339 mark_wip(p_line_id => l_line_id,
340 p_wip_entity_id =>l_wip_id,
341 p_operation_seq_num =>l_operation_seq_num,
342 p_inventory_item_id=>l_inventory_item_id,
343 p_repetitive_schedule_id =>l_repetitive_schedule_id,
344 p_primary_quantity =>l_partial_alloc_qty,
345 x_quantity_allocated => l_quantity_allocated,
346 x_return_status =>l_return_status,
347 x_msg_data =>l_msg_data,
348 p_primary_uom =>l_primary_uom,
349 p_uom =>l_uom);
350
351 l_partial_alloc_qty :=0;
352 END IF;
353 */
354 EXIT;
355 END;
356
357 l_lpn_qty := l_qty;
358
359 -- Compare qty
360 -- if lpn_qty is more than delivery qty, call pick release
361 -- for this delivery detail, continue loop
362 -- else, split delivery line and pick release the line that
363 -- we can satisfy
364
365 -- Check to see if the UOMs are the same. if not, convert
366 IF (l_debug = 1) THEN
367 mydebug('in 2nd loop');
368 END IF;
369
370 IF l_uom <> l_primary_uom THEN
371 --convert qty to same uom
372 IF (l_debug = 1) THEN
373 mydebug('Converting Qty');
374 END IF;
375
376 l_wip_qty :=
377 inv_convert.inv_um_convert(
378 item_id => l_inventory_item_id
379 , PRECISION => NULL
380 , from_quantity => l_wip_qty
381 , from_unit => l_primary_uom
382 , to_unit => l_uom
383 , from_name => NULL
384 , to_name => NULL
385 );
386
387 IF (l_debug = 1) THEN
388 mydebug('Converted Qty ' || l_wip_qty);
389 END IF;
390 END IF;
391
392 IF l_lpn_qty > l_wip_qty THEN
393 IF (l_debug = 1) THEN
394 mydebug('MOL>WIP');
395 END IF;
396
397 -- update mol with new qty
398 --UPDATE mtl_txn_request_lines SET
399 --quantity=l_wip_qty,BACKORDER_DELIVERY_DETAIL_ID=l_wip_id
400 --,to_subinventory_code=l_to_sub,to_locator_id=l_to_loc, crossdock_type=2
401 --WHERE line_id=l_line_id;
402
403 -- Mark del detail as submitted
404 IF (l_debug = 1) THEN
405 mydebug('calling mark_wip');
406 END IF;
407
408 mark_wip(
409 p_line_id => l_line_id
410 , p_wip_entity_id => l_wip_id
411 , p_operation_seq_num => l_operation_seq_num
412 , p_inventory_item_id => l_inventory_item_id
413 , p_repetitive_schedule_id => l_repetitive_schedule_id
414 , p_primary_quantity => l_wip_qty
415 , x_quantity_allocated => l_quantity_allocated
416 , x_return_status => l_return_status
417 , x_msg_data => l_msg_data
418 , p_primary_uom => l_primary_uom
419 , p_uom => l_uom
420 );
421
422 IF (l_debug = 1) THEN
423 mydebug('after calling mark_wip');
424 END IF;
425
426 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
427 fnd_message.set_name('WMS', 'WMS_TD_WIP_ERROR');
428 fnd_msg_pub.ADD;
429 RAISE fnd_api.g_exc_unexpected_error;
430 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
431 fnd_message.set_name('WMS', 'WMS_TD_WIP_ERROR');
432 fnd_msg_pub.ADD;
433 RAISE fnd_api.g_exc_error;
434 ELSIF(l_return_status = 'L') THEN
435 IF (l_debug = 1) THEN
436 mydebug('Unable to lock the record');
437 mydebug(' Getting next shortage ');
438 END IF;
439
440 EXIT;
441 END IF;
442
443 IF (l_debug = 1) THEN
444 mydebug('qty allocated is ' || l_quantity_allocated);
445 END IF;
446
447 IF (l_quantity_allocated = 0) THEN
448 EXIT;
449 ELSE
450 IF (l_debug = 1) THEN
451 mydebug('Updating the old mo line');
452 END IF;
453
454 UPDATE mtl_txn_request_lines
455 SET quantity = l_quantity_allocated
456 , backorder_delivery_detail_id = l_wip_id
457 , to_subinventory_code = l_to_sub
458 , to_locator_id = l_to_loc
459 , crossdock_type = 2
460 WHERE line_id = l_line_id;
461 END IF;
462
463 IF (l_debug = 1) THEN
464 mydebug('updating crdk_wip_table ');
465 END IF;
466
467 wms_task_dispatch_put_away.crdk_wip_table_index :=
468 wms_task_dispatch_put_away.crdk_wip_table_index + 1;
469 indx := wms_task_dispatch_put_away.crdk_wip_table_index;
470 wms_task_dispatch_put_away.crdk_wip_info_table(indx).move_order_line_id := l_line_id;
471 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_entity_id := l_wip_id;
472 wms_task_dispatch_put_away.crdk_wip_info_table(indx).operation_seq_num := l_operation_seq_num;
473 wms_task_dispatch_put_away.crdk_wip_info_table(indx).repetitive_schedule_id := l_repetitive_schedule_id;
474 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_issue_flag := l_wip_issue_flag;
475
476 -- reduce lpn_qty
477 --l_lpn_qty:=l_lpn_qty-l_wip_qty;
478 IF (l_debug = 1) THEN
479 mydebug('creating new lines..');
480 END IF;
481
482 -- create new mo line
483 wms_task_dispatch_put_away.create_mo_line(
484 p_org_id => l_org_id
485 , p_inventory_item_id => l_inventory_item_id
486 , p_qty => l_lpn_qty - l_quantity_allocated
487 , p_uom => l_uom
488 , p_lpn => l_lpn_id
489 , p_project_id => l_project_id
490 , p_task_id => l_task_id
491 , p_reference => l_reference
492 , p_reference_type_code => l_reference_type_code
493 , p_reference_id => l_reference_id
494 , p_header_id => l_header_id
495 , p_lot_number => l_lot_number
496 , p_revision => l_revision
497 , p_inspection_status => l_inspection_status
498 , p_txn_source_id => l_txn_source_id
499 , p_transaction_type_id => l_transaction_type_id
500 , p_transaction_source_type_id => l_transaction_source_type_id
501 , x_return_status => l_return_status
502 , x_msg_count => l_msg_count
503 , x_msg_data => l_msg_data
504 , x_line_id => l_tmp_line_id
505 );
506
507 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
508 fnd_message.set_name('WMS', 'WMS_TD_CMOL_ERROR');
509 fnd_msg_pub.ADD;
510 RAISE fnd_api.g_exc_unexpected_error;
511 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
512 fnd_message.set_name('WMS', 'WMS_TD_CMOL_ERROR');
513 fnd_msg_pub.ADD;
514 RAISE fnd_api.g_exc_error;
515 END IF;
516 -- Need to fetch next del line
517 -- EXIT;
518 ELSIF l_lpn_qty < l_wip_qty THEN
519 IF (l_debug = 1) THEN
520 mydebug('MOL<DEL');
521 END IF;
522
523 -- update mol with new qty
524 -- Try to get next mol
525 --UPDATE mtl_txn_request_lines SET
526 -- BACKORDER_DELIVERY_DETAIL_ID=l_wip_id
527 -- ,to_subinventory_code=l_to_sub,to_locator_id=l_to_loc, crossdock_type=2
528 --WHERE line_id=l_line_id;
529 -- Call update shipping API with back order action
530
531 -- l_wip_qty = l_wip_qty - l_lpn_qty;
532 IF (l_debug = 1) THEN
533 mydebug('Calling wip');
534 END IF;
535
536 mark_wip(
537 p_line_id => l_line_id
538 , p_wip_entity_id => l_wip_id
539 , p_operation_seq_num => l_operation_seq_num
540 , p_inventory_item_id => l_inventory_item_id
541 , p_repetitive_schedule_id => l_repetitive_schedule_id
542 , p_primary_quantity => l_lpn_qty
543 , x_quantity_allocated => l_quantity_allocated
544 , x_return_status => l_return_status
545 , x_msg_data => l_msg_data
546 , p_primary_uom => l_primary_uom
547 , p_uom => l_uom
548 );
549
550 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
551 fnd_message.set_name('WMS', 'WMS_TD_WIP_ERROR');
552 fnd_msg_pub.ADD;
553 RAISE fnd_api.g_exc_unexpected_error;
554 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
555 fnd_message.set_name('WMS', 'WMS_TD_WIP_ERROR');
556 fnd_msg_pub.ADD;
557 RAISE fnd_api.g_exc_error;
558 ELSIF(l_return_status = 'L') THEN
559 IF (l_debug = 1) THEN
560 mydebug('Unable to lock the record');
561 mydebug(' Getting next shortage ');
562 END IF;
563
564 EXIT;
565 END IF;
566
567 IF (l_debug = 1) THEN
568 mydebug('qty allocated ' || l_quantity_allocated);
569 END IF;
570
571 IF (l_quantity_allocated = 0) THEN
572 EXIT;
573 END IF;
574
575 IF (l_lpn_qty > l_quantity_allocated) THEN
576 IF (l_debug = 1) THEN
577 mydebug(' l_lpn_qty > l_quantity_allocated ');
578 END IF;
579
580 IF (l_debug = 1) THEN
581 mydebug('updating the old mol');
582 END IF;
583
584 UPDATE mtl_txn_request_lines
585 SET backorder_delivery_detail_id = l_wip_id
586 , quantity = l_quantity_allocated
587 , to_subinventory_code = l_to_sub
588 , to_locator_id = l_to_loc
589 , crossdock_type = 2
590 WHERE line_id = l_line_id;
591
592 IF (l_debug = 1) THEN
593 mydebug('updating the crdk_wip_table');
594 END IF;
595
596 wms_task_dispatch_put_away.crdk_wip_table_index :=
597 wms_task_dispatch_put_away.crdk_wip_table_index + 1;
598 indx :=
599 wms_task_dispatch_put_away.crdk_wip_table_index;
600 wms_task_dispatch_put_away.crdk_wip_info_table(indx).move_order_line_id := l_line_id;
601 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_entity_id := l_wip_id;
602 wms_task_dispatch_put_away.crdk_wip_info_table(indx).operation_seq_num := l_operation_seq_num;
603 wms_task_dispatch_put_away.crdk_wip_info_table(indx).repetitive_schedule_id := l_repetitive_schedule_id;
604 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_issue_flag := l_wip_issue_flag;
605
606 IF (l_debug = 1) THEN
607 mydebug('creating a mol for the rest ');
608 END IF;
609
610 wms_task_dispatch_put_away.create_mo_line(
611 p_org_id => l_org_id
612 , p_inventory_item_id => l_inventory_item_id
613 , p_qty => l_lpn_qty - l_quantity_allocated
614 , p_uom => l_uom
615 , p_lpn => l_lpn_id
616 , p_project_id => l_project_id
617 , p_task_id => l_task_id
618 , p_reference => l_reference
619 , p_reference_type_code => l_reference_type_code
620 , p_reference_id => l_reference_id
621 , p_header_id => l_header_id
622 , p_lot_number => l_lot_number
623 , p_revision => l_revision
624 , p_inspection_status => l_inspection_status
625 , p_txn_source_id => l_txn_source_id
626 , p_transaction_type_id => l_transaction_type_id
627 , p_transaction_source_type_id => l_transaction_source_type_id
628 , x_return_status => l_return_status
629 , x_msg_count => l_msg_count
630 , x_msg_data => l_msg_data
631 , x_line_id => l_tmp_line_id
632 );
633
634 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
635 fnd_message.set_name('WMS', 'WMS_TD_CMOL_ERROR');
636 fnd_msg_pub.ADD;
637 RAISE fnd_api.g_exc_unexpected_error;
638 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
639 fnd_message.set_name('WMS', 'WMS_TD_CMOL_ERROR');
640 fnd_msg_pub.ADD;
641 RAISE fnd_api.g_exc_error;
642 END IF;
643
644 IF (l_debug = 1) THEN
645 mydebug(' exiting to get the next shortage ');
646 END IF;
647
648 EXIT;
649 END IF;
650
651 IF (l_debug = 1) THEN
652 mydebug(' qty allocated is same as lpn_qty ');
653 mydebug(' update old mol ');
654 END IF;
655
656 UPDATE mtl_txn_request_lines
657 SET backorder_delivery_detail_id = l_wip_id
658 , to_subinventory_code = l_to_sub
659 , to_locator_id = l_to_loc
660 , crossdock_type = 2
661 WHERE line_id = l_line_id;
662
663 l_wip_qty := l_wip_qty - l_lpn_qty;
664
665 IF (l_debug = 1) THEN
666 mydebug(' setting b_wip_not_fulfilled to true ');
667 END IF;
668
669 b_wip_not_fulfilled := TRUE;
670
671 IF (l_debug = 1) THEN
672 mydebug(' updating the crdk_wip_table ');
673 END IF;
674
675 wms_task_dispatch_put_away.crdk_wip_table_index :=
676 wms_task_dispatch_put_away.crdk_wip_table_index + 1;
677 indx := wms_task_dispatch_put_away.crdk_wip_table_index;
678 wms_task_dispatch_put_away.crdk_wip_info_table(indx).move_order_line_id := l_line_id;
679 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_entity_id := l_wip_id;
680 wms_task_dispatch_put_away.crdk_wip_info_table(indx).operation_seq_num := l_operation_seq_num;
681 wms_task_dispatch_put_away.crdk_wip_info_table(indx).repetitive_schedule_id := l_repetitive_schedule_id;
682 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_issue_flag := l_wip_issue_flag;
683 --l_lpn_qty:=0;
684 ELSIF l_lpn_qty = l_wip_qty THEN
685 IF (l_debug = 1) THEN
686 mydebug('MOL=DEL');
687 END IF;
688
689 IF (l_debug = 1) THEN
690 mydebug(' calling mark_wip ');
691 END IF;
692
693 -- Mark wip as allocated
694 mark_wip(
695 p_line_id => l_line_id
696 , p_wip_entity_id => l_wip_id
697 , p_operation_seq_num => l_operation_seq_num
698 , p_inventory_item_id => l_inventory_item_id
699 , p_repetitive_schedule_id => l_repetitive_schedule_id
700 , p_primary_quantity => l_wip_qty
701 , x_quantity_allocated => l_quantity_allocated
702 , x_return_status => l_return_status
703 , x_msg_data => l_msg_data
704 , p_primary_uom => l_primary_uom
705 , p_uom => l_uom
706 );
707
708 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
709 fnd_message.set_name('WMS', 'WMS_TD_WIP_ERROR');
710 fnd_msg_pub.ADD;
711 RAISE fnd_api.g_exc_unexpected_error;
712 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
713 fnd_message.set_name('WMS', 'WMS_TD_WIP_ERROR');
714 fnd_msg_pub.ADD;
715 RAISE fnd_api.g_exc_error;
716 ELSIF(l_return_status = 'L') THEN
717 IF (l_debug = 1) THEN
718 mydebug('Unable to lock the record');
719 mydebug(' Getting next shortage ');
720 END IF;
721
722 EXIT;
723 END IF;
724
725 IF (l_debug = 1) THEN
726 mydebug(' qty allocated ' || l_quantity_allocated);
727 END IF;
728
729 IF (l_quantity_allocated = 0) THEN
730 EXIT;
731 END IF;
732
733 IF (l_lpn_qty > l_quantity_allocated) THEN
734 IF (l_debug = 1) THEN
735 mydebug('l_lpn_qty > l_quantity_allocated ');
736 mydebug('updating the old mol ');
737 END IF;
738
739 UPDATE mtl_txn_request_lines
740 SET backorder_delivery_detail_id = l_wip_id
741 , quantity = l_quantity_allocated
742 , to_subinventory_code = l_to_sub
743 , to_locator_id = l_to_loc
744 , crossdock_type = 2
745 WHERE line_id = l_line_id;
746
747 IF (l_debug = 1) THEN
748 mydebug('updating the crdk_wip_table ');
749 END IF;
750
751 wms_task_dispatch_put_away.crdk_wip_table_index :=
752 wms_task_dispatch_put_away.crdk_wip_table_index + 1;
753 indx :=
754 wms_task_dispatch_put_away.crdk_wip_table_index;
755 wms_task_dispatch_put_away.crdk_wip_info_table(indx).move_order_line_id := l_line_id;
756 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_entity_id := l_wip_id;
757 wms_task_dispatch_put_away.crdk_wip_info_table(indx).operation_seq_num := l_operation_seq_num;
758 wms_task_dispatch_put_away.crdk_wip_info_table(indx).repetitive_schedule_id := l_repetitive_schedule_id;
759 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_issue_flag := l_wip_issue_flag;
760
761 IF (l_debug = 1) THEN
762 mydebug('creating new mol for the rest ');
763 END IF;
764
765 wms_task_dispatch_put_away.create_mo_line(
766 p_org_id => l_org_id
767 , p_inventory_item_id => l_inventory_item_id
768 , p_qty => l_lpn_qty - l_quantity_allocated
769 , p_uom => l_uom
770 , p_lpn => l_lpn_id
771 , p_project_id => l_project_id
772 , p_task_id => l_task_id
773 , p_reference => l_reference
774 , p_reference_type_code => l_reference_type_code
775 , p_reference_id => l_reference_id
776 , p_header_id => l_header_id
777 , p_lot_number => l_lot_number
778 , p_revision => l_revision
779 , p_inspection_status => l_inspection_status
780 , p_txn_source_id => l_txn_source_id
781 , p_transaction_type_id => l_transaction_type_id
782 , p_transaction_source_type_id => l_transaction_source_type_id
783 , x_return_status => l_return_status
784 , x_msg_count => l_msg_count
785 , x_msg_data => l_msg_data
786 , x_line_id => l_tmp_line_id
787 );
788
789 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
790 fnd_message.set_name('WMS', 'WMS_TD_CMOL_ERROR');
791 fnd_msg_pub.ADD;
792 RAISE fnd_api.g_exc_unexpected_error;
793 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
794 fnd_message.set_name('WMS', 'WMS_TD_CMOL_ERROR');
795 fnd_msg_pub.ADD;
796 RAISE fnd_api.g_exc_error;
797 END IF;
798
799 IF (l_debug = 1) THEN
800 mydebug(' exiting to get the next shortage ');
801 END IF;
802
803 EXIT;
804 END IF;
805
806 IF (l_debug = 1) THEN
807 mydebug('qty allocated equals lpn_qty ');
808 mydebug('updating the old mol ');
809 END IF;
810
811 UPDATE mtl_txn_request_lines
812 SET backorder_delivery_detail_id = l_wip_id
813 , to_subinventory_code = l_to_sub
814 , to_locator_id = l_to_loc
815 , crossdock_type = 2
816 WHERE line_id = l_line_id;
817
818 IF (l_debug = 1) THEN
819 mydebug('updating the crdk_wip_table_index ');
820 END IF;
821
822 wms_task_dispatch_put_away.crdk_wip_table_index :=
823 wms_task_dispatch_put_away.crdk_wip_table_index + 1;
824 indx := wms_task_dispatch_put_away.crdk_wip_table_index;
825 wms_task_dispatch_put_away.crdk_wip_info_table(indx).move_order_line_id := l_line_id;
826 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_entity_id := l_wip_id;
827 wms_task_dispatch_put_away.crdk_wip_info_table(indx).operation_seq_num := l_operation_seq_num;
828 wms_task_dispatch_put_away.crdk_wip_info_table(indx).repetitive_schedule_id := l_repetitive_schedule_id;
829 wms_task_dispatch_put_away.crdk_wip_info_table(indx).wip_issue_flag := l_wip_issue_flag;
830
831 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
832 fnd_message.set_name('WMS', 'WMS_TD_MK_DEL_ERROR');
833 fnd_msg_pub.ADD;
834 RAISE fnd_api.g_exc_unexpected_error;
835 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
836 fnd_message.set_name('WMS', 'WMS_TD_MK_DEL_ERROR');
837 fnd_msg_pub.ADD;
838 RAISE fnd_api.g_exc_error;
839 END IF;
840 -- Qty's match. Pick release the delivery detail
841 --l_lpn_qty:=0;
842 END IF;
843
844 IF (b_wip_not_fulfilled = FALSE) THEN
845 -- This lpn qty has been consumed, get the next line
846 IF (l_debug = 1) THEN
847 mydebug('Del not fulfilled');
848 END IF;
849
850 EXIT;
851 END IF;
852 END LOOP;
853 END LOOP;
854
855 CLOSE wip_csr;
856 ELSE
857 IF (l_debug = 1) THEN
858 mydebug('No CrossDock Opp for WIP');
859 END IF;
860
861 l_ret := 1;
862 END IF;
863
864 x_ret := l_ret;
865 x_return_status := l_return_status;
866 EXCEPTION
867 WHEN fnd_api.g_exc_error THEN
868 x_ret := 2;
869 x_return_status := l_return_status;
870 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
871 WHEN fnd_api.g_exc_unexpected_error THEN
872 x_ret := 2;
873 x_return_status := l_return_status;
874 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
875 WHEN OTHERS THEN
876 x_ret := 2;
877 x_return_status := l_return_status;
878 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
879 END wip_chk_crossdock;
880
881 PROCEDURE wip_complete_crossdock(
882 p_org_id IN NUMBER
883 , p_temp_id IN NUMBER
884 , p_wip_id IN NUMBER
885 , p_inventory_item_id IN NUMBER
886 , x_return_status OUT NOCOPY VARCHAR2
887 , x_msg_count OUT NOCOPY NUMBER
888 , x_msg_data OUT NOCOPY VARCHAR2
889 ) IS
890 /*
891 ,p_del_id NUMBER
892 ,p_mo_line_id NUMBER
893 , p_item_id NUMBER
894 ,x_return_status OUT VARCHAR2
895 */
896 l_cnt_lpn_id NUMBER;
897 l_msg_cnt NUMBER;
898 -- l_msg_data VARCHAR2(240);
899 l_org_id NUMBER;
900 l_item_id NUMBER;
901 l_ret NUMBER;
902 l_temp_id NUMBER;
903 l_del_id NUMBER;
904 l_mo_line_id NUMBER;
905 l_demand_source_type NUMBER;
906 l_mso_header_id NUMBER; -- The MTL_SALES_ORDERS
907 --header ID, which should be derived from the OE header ID
908 -- and used for reservation queries.
909 l_shipping_attr wsh_interface.changedattributetabtype;
910 l_update_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
911 l_demand_info wsh_inv_delivery_details_v%ROWTYPE;
912 l_prim_qty NUMBER;
913 l_prim_uom VARCHAR2(3);
914 l_sub VARCHAR2(10);
915 l_loc NUMBER;
916 l_return_status VARCHAR2(1);
917 l_api_return_status VARCHAR2(1);
918 l_org_wide_res_id NUMBER;
919 l_qty_succ_reserved NUMBER;
920 l_msg_data VARCHAR2(2400);
921 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
922 l_source_header_id NUMBER;
923 l_source_line_id NUMBER;
924 l_rev VARCHAR2(3);
925 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
926 l_lot VARCHAR2(80);
927 l_lot_count NUMBER;
928 l_lot_control_code NUMBER;
929 l_serial_control_code NUMBER;
930 l_serial_trx_id NUMBER;
931 l_transaction_type_id NUMBER;
932 l_action_flag VARCHAR2(1);
933 l_serial_temp_id NUMBER;
934 l_serial_number VARCHAR2(30);
935 l_order_source_id NUMBER;
936 l_label_status VARCHAR2(2000);
937 l_lpn_del_detail_id NUMBER;
938 l_new_temp_id NUMBER;
939 l_new_txn_hdr_id NUMBER;
940 l_txn_ret NUMBER := 0;
941 l_wip_issue_flag VARCHAR2(1) := 'Y';
942 line_id NUMBER := NULL;
943 item_id NUMBER := NULL;
944 l_wip_entity_id NUMBER := NULL;
945 l_operation_seq_num NUMBER := NULL;
946 l_repetitive_schedule_id NUMBER := NULL;
947 l_primary_quantity NUMBER := 0;
948 l_transaction_quantity NUMBER := 0;
949 l_mtl_lots_temp_rec mtl_transaction_lots_temp%ROWTYPE;
950 l_mtl_srl_temp_rec mtl_serial_numbers_temp%ROWTYPE;
951 l_returnstatus VARCHAR2(1);
952 l_lpn_id NUMBER := NULL;
953 l_transfer_lpn_id NUMBER := NULL;
954 l_content_lpn_id NUMBER := NULL;
955 l_bflow_exist NUMBER;
956
957 CURSOR serial_csr IS
958 SELECT fm_serial_number
959 FROM mtl_serial_numbers_temp
960 WHERE transaction_temp_id = l_serial_temp_id;
961
962 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
963 BEGIN
964 l_return_status := fnd_api.g_ret_sts_success;
965
966 IF (l_debug = 1) THEN
967 mydebug('in WIP Complete cdock');
968 END IF;
969
970 l_org_id := p_org_id;
971 l_temp_id := p_temp_id;
972 l_ret := 0;
973
974 -- Need to create a new MMTT line and call TM here!
975 /*
976 SELECT mtl_material_transactions_s.NEXTVAL
977 INTO l_new_temp_id
978 FROM dual;
979
980 SELECT mtl_material_transactions_s.NEXTVAL
981 INTO l_new_txn_hdr_id
982 FROM dual;
983 */
984
985 --mydebug('l_new_temp_id = ' || l_new_temp_id);
986 BEGIN
987 SELECT move_order_line_id
988 , DECODE(wip_supply_type, 1, 'Y', 'N')
989 , demand_source_header_id
990 , repetitive_line_id
991 , operation_seq_num
992 , NVL(primary_quantity, 0)
993 , NVL(transaction_quantity, 0)
994 , lpn_id
995 , content_lpn_id
996 , transfer_lpn_id
997 INTO line_id
998 , l_wip_issue_flag
999 , l_wip_entity_id
1000 , l_repetitive_schedule_id
1001 , l_operation_seq_num
1002 , l_primary_quantity
1003 , l_transaction_quantity
1004 , l_lpn_id
1005 , l_content_lpn_id
1006 , l_transfer_lpn_id
1007 FROM mtl_material_transactions_temp
1008 WHERE transaction_temp_id = l_temp_id
1009 AND ROWNUM < 2;
1010 EXCEPTION
1011 WHEN NO_DATA_FOUND THEN
1012 IF (l_debug = 1) THEN
1013 mydebug('Cannot find the mmtt ');
1014 END IF;
1015 END;
1016
1017 IF (l_debug = 1) THEN
1018 mydebug('l_wip_entity_id ' || l_wip_entity_id);
1019 mydebug('l_operation_seq_num ' || l_operation_seq_num);
1020 mydebug('l_repetitive_schedule_id' || l_repetitive_schedule_id);
1021 mydebug('l_wip_issue_flag' || l_wip_issue_flag);
1022 END IF;
1023
1024 IF (l_wip_issue_flag = 'Y') THEN
1025 IF (l_debug = 1) THEN
1026 mydebug('wip isuue - before Insert into MMTT..');
1027 END IF;
1028
1029 insert_new_mmtt_row_like(p_txn_temp_id => l_temp_id, x_new_temp_id => l_new_temp_id, x_new_hdr_id => l_new_txn_hdr_id);
1030
1031 IF (l_debug = 1) THEN
1032 mydebug('l_new_temp_id = ' || l_new_temp_id);
1033 mydebug('after Insert into MMTT..');
1034 END IF;
1035
1036 l_primary_quantity := (-1) * l_primary_quantity;
1037 l_transaction_quantity := (-1) * l_transaction_quantity;
1038
1039 -- call sajus api here
1040 IF (l_debug = 1) THEN
1041 mydebug('calling wms_wip_integration.update_mmtt_for_wip without ');
1042 END IF;
1043
1044 -- Bug 2375076 -- Removed the move order line iod from the parameter list
1045 BEGIN
1046 wms_wip_integration.update_mmtt_for_wip(
1047 p_transaction_temp_id => l_new_temp_id
1048 , p_wip_entity_id => l_wip_entity_id
1049 , p_operation_seq_num => l_operation_seq_num
1050 , p_repetitive_schedule_id => l_repetitive_schedule_id
1051 , p_transaction_type_id => inv_globals.g_type_xfer_order_wip_issue
1052 );
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 IF (l_debug = 1) THEN
1056 mydebug('wms_wip_integration.update_mmtt_for_wip failed ');
1057 END IF;
1058 END;
1059
1060 IF (l_debug = 1) THEN
1061 mydebug('after calling wms_wip_integration.update_mmtt_for_wip');
1062 END IF;
1063
1064 IF (l_debug = 1) THEN
1065 mydebug(' making the qunatities neagative for wip issue');
1066 mydebug(' move order line is set to null');
1067 mydebug(' transaction_status = 1 ');
1068 END IF;
1069
1070 --bug 2074100 fix
1071 --If the lpn is not compltely used for this issue
1072 --the quantity is dropped lose and hence we should issue
1073 --loose,
1074 --other wise content lpn id should be populated so that
1075 -- after the isuue the lpn context is automatically changed
1076 -- to defined but not used
1077 IF (l_content_lpn_id IS NULL) THEN
1078 IF (l_debug = 1) THEN
1079 mydebug(' l_content_lpn_id IS NULL ');
1080 END IF;
1081
1082 IF (l_lpn_id = l_transfer_lpn_id) THEN
1083 IF (l_debug = 1) THEN
1084 mydebug('setting l_content_lpn_id := ' || l_lpn_id);
1085 END IF;
1086
1087 l_content_lpn_id := l_lpn_id;
1088 END IF;
1089
1090 IF ((l_lpn_id IS NOT NULL)
1091 AND(l_transfer_lpn_id IS NULL)) THEN
1092 l_lpn_id := NULL;
1093 l_content_lpn_id := NULL;
1094 l_transfer_lpn_id := NULL;
1095
1096 IF (l_debug = 1) THEN
1097 mydebug(' lpn_id is set to NULL ');
1098 mydebug(' content_lpn_id is set to NULL');
1099 mydebug(' transfer_lpn_id is set to NULL');
1100 END IF;
1101 END IF;
1102 END IF;
1103
1104 IF (l_debug = 1) THEN
1105 mydebug(' lpn_id ' || l_lpn_id);
1106 mydebug(' content_lpn_id ' || l_content_lpn_id);
1107 mydebug(' transfer_lpn_id ' || l_transfer_lpn_id);
1108 END IF;
1109
1110 UPDATE mtl_material_transactions_temp
1111 SET move_order_line_id = NULL
1112 , transaction_status = 1
1113 , primary_quantity = l_primary_quantity
1114 , transaction_quantity = l_transaction_quantity
1115 --, lpn_id = l_lpn_id
1116 --, content_lpn_id = l_content_lpn_id
1117 --, transfer_lpn_id = l_transfer_lpn_id
1118 , wms_task_type = NULL -- bug fix 3233053
1119 WHERE transaction_temp_id = l_new_temp_id;
1120
1121 IF (l_debug = 1) THEN
1122 mydebug('update - complete');
1123 END IF;
1124
1125 -- UPDATE mtl_material_transactions_temp
1126 -- SET
1127 --transaction_source_type_id = 5,
1128 --transaction_type_id = 35,
1129 --transaction_action_id = 1,
1130 --move_order_line_id = NULL,
1131 ---transaction_status = 1
1132 -- WHERE transaction_temp_id = l_new_temp_id;
1133 IF (l_debug = 1) THEN
1134 mydebug('after updating _mmtt move_order_line_id, transaction_status ');
1135 END IF;
1136
1137 -- Bug 2829872, triggering label printing for Manufacturing Cross-Dock.
1138 BEGIN
1139 SELECT 1
1140 INTO l_bflow_exist
1141 FROM mfg_lookups
1142 WHERE lookup_type = 'WMS_BUSINESS_FLOW'
1143 AND lookup_code = 37;
1144
1145 IF (l_debug = 1) THEN
1146 mydebug('Calling label printing API for Manufacturing Cross-Dock');
1147 mydebug('Transaction temp id: ' || l_new_temp_id);
1148 END IF;
1149
1150 inv_label.print_label_wrap(
1151 x_return_status => l_return_status
1152 , x_msg_count => l_msg_cnt
1153 , x_msg_data => l_msg_data
1154 , x_label_status => l_label_status
1155 , p_business_flow_code => 37
1156 , p_transaction_id => l_new_temp_id
1157 );
1158
1159 IF (l_debug = 1) THEN
1160 mydebug('Return Status: ' || l_return_status);
1161 END IF;
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 NULL;
1165 END;
1166
1167 UPDATE mtl_material_transactions_temp
1168 SET lpn_id = l_lpn_id
1169 , content_lpn_id = l_content_lpn_id
1170 , transfer_lpn_id = l_transfer_lpn_id
1171 WHERE transaction_temp_id = l_new_temp_id;
1172
1173 IF (l_debug = 1) THEN
1174 mydebug('After insert into MMTT');
1175 mydebug('Calling txn proc');
1176 mydebug('Hdr' || l_new_txn_hdr_id);
1177 END IF;
1178
1179 -- Call the txn processor
1180 wms_wip_integration.wip_processor(p_txn_hdr_id => l_new_txn_hdr_id, p_business_flow_code => inv_label.wms_bf_wip_pick_drop
1181 , x_return_status => x_return_status);
1182
1183 IF (l_debug = 1) THEN
1184 mydebug('After Calling WIP txn proc STATUS' || x_return_status);
1185 END IF;
1186
1187 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1188 fnd_message.set_name('WMS', 'WMS_TD_TXNMGR_ERROR');
1189 fnd_msg_pub.ADD;
1190 RAISE fnd_api.g_exc_unexpected_error;
1191 END IF;
1192 --Changed call above to WIP
1193 --l_txn_ret:=inv_lpn_trx_pub.PROCESS_LPN_TRX(p_trx_hdr_id=>l_new_txn_hdr_id,
1194 -- p_commit=> fnd_api.g_false,
1195 --x_proc_msg =>l_msg_data
1196 -- );
1197 --mydebug('After Calling txn proc');
1198 --mydebug('l_txn_ret : ' || l_txn_ret);
1199 --mydebug ('Txn Message'||l_msg_data);
1200
1201 --COMMIT;
1202 --IF l_txn_ret<>0 THEN
1203 --FND_MESSAGE.SET_NAME('WMS','WMS_TD_TXNMGR_ERROR' );
1204 --fND_MSG_PUB.ADD;
1205 -- RAISE FND_API.g_exc_unexpected_error;
1206 --END IF;
1207 ELSE
1208 -- Bug 2829872, triggering label printing for Manufacturing Cross-Dock.
1209 BEGIN
1210 SELECT 1
1211 INTO l_bflow_exist
1212 FROM mfg_lookups
1213 WHERE lookup_type = 'WMS_BUSINESS_FLOW'
1214 AND lookup_code = 37;
1215
1216 IF (l_debug = 1) THEN
1217 mydebug('Calling label printing API for Manufacturing Cross-Dock for pull component');
1218 mydebug('Transaction temp id: ' || l_temp_id);
1219 END IF;
1220
1221 inv_label.print_label_wrap(
1222 x_return_status => l_return_status
1223 , x_msg_count => l_msg_cnt
1224 , x_msg_data => l_msg_data
1225 , x_label_status => l_label_status
1226 , p_business_flow_code => 37
1227 , p_transaction_id => l_temp_id
1228 );
1229
1230 IF (l_debug = 1) THEN
1231 mydebug('Return Status: ' || l_return_status);
1232 END IF;
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 NULL;
1236 END;
1237
1238 IF (l_debug = 1) THEN
1239 mydebug(' Back FLUSH ');
1240 END IF;
1241 --Nothing to do
1242 /* Back FLUSH */
1243
1244 --UPDATE mtl_material_transactions_temp
1245 -- SET
1246 -- transaction_source_type_id = 5,
1247 -- transaction_type_id = 35,
1248 -- transaction_action_id = 1,
1249 -- move_order_line_id = NULL,
1250 -- transaction_status = 1
1251 -- WHERE transaction_temp_id = l_new_temp_id;
1252 END IF;
1253
1254 x_return_status := fnd_api.g_ret_sts_success;
1255 EXCEPTION
1256 WHEN fnd_api.g_exc_error THEN
1257 x_return_status := fnd_api.g_ret_sts_error;
1258 -- Get message count and data
1259 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1260 WHEN fnd_api.g_exc_unexpected_error THEN
1261 x_return_status := fnd_api.g_ret_sts_unexp_error;
1262 -- Get message count and data
1263 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1264 WHEN OTHERS THEN
1265 x_return_status := fnd_api.g_ret_sts_unexp_error;
1266 fnd_message.set_name('WMS', 'WMS_TD_CCDOCK_ERROR');
1267 fnd_msg_pub.ADD;
1268 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1269 END wip_complete_crossdock;
1270
1271 PROCEDURE mark_wip(
1272 p_line_id IN NUMBER
1273 , p_wip_entity_id IN NUMBER
1274 , p_operation_seq_num IN NUMBER
1275 , p_inventory_item_id IN NUMBER
1276 , p_repetitive_schedule_id IN NUMBER
1277 , p_primary_quantity IN NUMBER
1278 , x_quantity_allocated OUT NOCOPY NUMBER
1279 , x_return_status OUT NOCOPY VARCHAR2
1280 , x_msg_data OUT NOCOPY VARCHAR2
1281 , p_primary_uom IN VARCHAR2
1282 , p_uom VARCHAR2
1283 ) IS
1284 l_ret VARCHAR2(1);
1285 l_wip_id NUMBER;
1286 l_operation_seq_num NUMBER;
1287 l_inventory_item_id NUMBER;
1288 l_repetitive_schedule_id NUMBER;
1289 l_wip_qty NUMBER;
1290 l_uom VARCHAR2(3);
1291 l_primary_uom VARCHAR2(3);
1292 l_msg_count NUMBER;
1293 l_msg_data VARCHAR2(240);
1294 l_return_status VARCHAR2(1);
1295 l_qty_allocated NUMBER;
1296 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1297 BEGIN
1298 IF (l_debug = 1) THEN
1299 mydebug('in mark wip as allocated');
1300 END IF;
1301
1302 l_wip_id := p_wip_entity_id;
1303 l_operation_seq_num := p_operation_seq_num;
1304 l_inventory_item_id := p_inventory_item_id;
1305 l_repetitive_schedule_id := p_repetitive_schedule_id;
1306 l_wip_qty := p_primary_quantity;
1307 l_uom := p_uom;
1308 l_primary_uom := l_primary_uom;
1309 l_wip_id := p_wip_entity_id;
1310
1311 IF l_uom <> l_primary_uom THEN
1312 IF (l_debug = 1) THEN
1313 mydebug('UOM is different');
1314 END IF;
1315
1316 l_wip_qty :=
1317 inv_convert.inv_um_convert(
1318 item_id => l_inventory_item_id
1319 , PRECISION => NULL
1320 , from_quantity => l_wip_qty
1321 , from_unit => l_uom
1322 , to_unit => l_primary_uom
1323 , from_name => NULL
1324 , to_name => NULL
1325 );
1326 END IF;
1327
1328 IF (l_debug = 1) THEN
1329 mydebug('Before calling wip allocate mtl');
1330 END IF;
1331
1332 wip_picking_pub.allocate_material(
1333 p_wip_entity_id => l_wip_id
1334 , p_operation_seq_num => l_operation_seq_num
1335 , p_inventory_item_id => l_inventory_item_id
1336 , p_repetitive_schedule_id => l_repetitive_schedule_id
1337 , p_primary_quantity => l_wip_qty
1338 , x_quantity_allocated => l_qty_allocated
1339 , x_return_status => l_return_status
1340 , x_msg_data => l_msg_data
1341 );
1342
1343 IF (l_debug = 1) THEN
1344 mydebug('return status' || l_ret);
1345 END IF;
1346
1347 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1348 fnd_message.set_name('WMS', 'WMS_TD_UPD_SHP_ERROR');
1349 fnd_msg_pub.ADD;
1350 RAISE fnd_api.g_exc_unexpected_error;
1351 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1352 fnd_message.set_name('WMS', 'WMS_TD_UPD_SHP_ERROR');
1353 fnd_msg_pub.ADD;
1354 RAISE fnd_api.g_exc_error;
1355 END IF;
1356
1357 --x_ret:=l_ret;
1358 x_return_status := l_return_status;
1359 x_quantity_allocated := l_qty_allocated;
1360
1361 IF (l_debug = 1) THEN
1362 mydebug('returned quantity allocated ' || l_qty_allocated);
1363 END IF;
1364
1365 IF ((l_wip_qty - l_qty_allocated) = 0) THEN
1366 IF (l_debug = 1) THEN
1367 mydebug('whole shortage has been removed');
1368 END IF;
1369
1370 RETURN;
1371 END IF;
1372 EXCEPTION
1373 WHEN fnd_api.g_exc_error THEN
1374 x_return_status := fnd_api.g_ret_sts_error;
1375 --x_return_status:=l_return_status;
1376 -- Get message count and data
1377 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
1378 WHEN fnd_api.g_exc_unexpected_error THEN
1379 x_return_status := fnd_api.g_ret_sts_unexp_error;
1380 -- x_return_status:=l_return_status;
1381 -- Get message count and data
1382 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
1383 WHEN OTHERS THEN
1384 x_return_status := fnd_api.g_ret_sts_unexp_error;
1385 --x_return_status:=l_return_status;
1386 fnd_message.set_name('WMS', 'WMS_TD_MW_ERROR');
1387 fnd_msg_pub.ADD;
1388 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
1389 END mark_wip;
1390
1391 PROCEDURE insert_new_mmtt_row_like(p_txn_temp_id IN NUMBER, x_new_temp_id OUT NOCOPY NUMBER, x_new_hdr_id OUT NOCOPY NUMBER) IS
1392 SUBTYPE mmtt_type IS mtl_material_transactions_temp%ROWTYPE;
1393
1394 mmtt_row mmtt_type;
1395
1396 SUBTYPE mtlt_type IS mtl_transaction_lots_temp%ROWTYPE;
1397
1398 lot_row mtlt_type;
1399
1400 SUBTYPE msnt_type IS mtl_serial_numbers_temp%ROWTYPE;
1401
1402 ser_row msnt_type;
1403 l_new_txn_hdr_id NUMBER := -1;
1404 new_txn_temp_id NUMBER := -1;
1405 ser_transaction_temp_id NUMBER;
1406 v_lot_control_code NUMBER := -1;
1407 v_serial_control_code NUMBER := -1;
1408 v_allocate_serial_flag VARCHAR2(1) := 'X';
1409
1410 CURSOR mtlt(txn_tmp_id NUMBER) IS
1411 SELECT *
1412 FROM mtl_transaction_lots_temp
1413 WHERE transaction_temp_id = txn_tmp_id;
1414
1415 CURSOR msnt(txn_tmp_id NUMBER) IS
1416 SELECT *
1417 FROM mtl_serial_numbers_temp
1418 WHERE transaction_temp_id = txn_tmp_id;
1419
1420 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1421 BEGIN
1422 SELECT mtl_material_transactions_s.NEXTVAL
1423 INTO new_txn_temp_id
1424 FROM DUAL;
1425
1426 x_new_temp_id := new_txn_temp_id;
1427
1428 SELECT mtl_material_transactions_s.NEXTVAL
1429 INTO l_new_txn_hdr_id
1430 FROM DUAL;
1431
1432 x_new_hdr_id := l_new_txn_hdr_id;
1433
1434 SELECT *
1435 INTO mmtt_row
1436 FROM mtl_material_transactions_temp
1437 WHERE transaction_temp_id = p_txn_temp_id;
1438
1439 mmtt_row.transaction_temp_id := new_txn_temp_id;
1440 mmtt_row.transaction_header_id := l_new_txn_hdr_id;
1441
1442 /*************NOW Updating MTLT and MSNT *************************/
1443 SELECT lot_control_code
1444 , serial_number_control_code
1445 INTO v_lot_control_code
1446 , v_serial_control_code
1447 FROM mtl_system_items
1448 WHERE inventory_item_id = mmtt_row.inventory_item_id
1449 AND organization_id = mmtt_row.organization_id;
1450
1451 SELECT allocate_serial_flag
1452 INTO v_allocate_serial_flag
1453 FROM mtl_parameters
1454 WHERE organization_id = mmtt_row.organization_id;
1455
1456 /*****LOT controlled only **********/
1457 IF (v_lot_control_code = 2
1458 AND v_serial_control_code IN(1, 6)) THEN
1459 IF (l_debug = 1) THEN
1460 mydebug(' LOT controlled only ');
1461 END IF;
1462
1463 OPEN mtlt(p_txn_temp_id);
1464
1465 LOOP
1466 FETCH mtlt
1467 INTO lot_row;
1468
1469 EXIT WHEN mtlt%NOTFOUND;
1470
1471 IF (l_debug = 1) THEN
1472 mydebug('child row with temp id ' || lot_row.transaction_temp_id);
1473 END IF;
1474
1475 lot_row.transaction_temp_id := new_txn_temp_id;
1476 inv_rcv_common_apis.insert_mtlt(lot_row);
1477 lot_row := NULL;
1478 END LOOP;
1479
1480 IF mtlt%ISOPEN THEN
1481 CLOSE mtlt;
1482 END IF;
1483 /********* serial Controlled only **************/
1484 ELSIF(v_lot_control_code = 1
1485 AND v_serial_control_code NOT IN(1, 6)) THEN
1486 IF (l_debug = 1) THEN
1487 mydebug(' Serial controlled only ');
1488 END IF;
1489
1490 IF (v_allocate_serial_flag = 'Y') THEN
1491 IF (l_debug = 1) THEN
1492 mydebug(' allocate_serial_flag is Y ');
1493 END IF;
1494
1495 OPEN msnt(p_txn_temp_id);
1496
1497 LOOP
1498 FETCH msnt
1499 INTO ser_row;
1500
1501 EXIT WHEN msnt%NOTFOUND;
1502
1503 IF (l_debug = 1) THEN
1504 mydebug('child row with temp id ' || ser_row.transaction_temp_id);
1505 END IF;
1506
1507 ser_row.transaction_temp_id := new_txn_temp_id;
1508 inv_rcv_common_apis.insert_msnt(ser_row);
1509 ser_row := NULL;
1510 END LOOP;
1511
1512 IF msnt%ISOPEN THEN
1513 CLOSE msnt;
1514 END IF;
1515 END IF;
1516 /********* LOT and serial Controlled **************/
1517 ELSIF(v_lot_control_code = 2
1518 AND v_serial_control_code NOT IN(1, 6)) THEN
1519 IF (l_debug = 1) THEN
1520 mydebug(' Both lot and Serial controlled ');
1521 END IF;
1522
1523 IF (v_allocate_serial_flag = 'N') THEN
1524 /*******************same as LOT CONTROLLED ONLY***********/
1525 IF (l_debug = 1) THEN
1526 mydebug(' allocate_serial_flag is N ');
1527 END IF;
1528
1529 OPEN mtlt(p_txn_temp_id);
1530
1531 LOOP
1532 FETCH mtlt
1533 INTO lot_row;
1534
1535 EXIT WHEN mtlt%NOTFOUND;
1536
1537 IF (l_debug = 1) THEN
1538 mydebug('child row with temp id ' || lot_row.transaction_temp_id);
1539 END IF;
1540
1541 lot_row.transaction_temp_id := new_txn_temp_id;
1542 inv_rcv_common_apis.insert_mtlt(lot_row);
1543 lot_row := NULL;
1544 END LOOP;
1545
1546 IF mtlt%ISOPEN THEN
1547 CLOSE mtlt;
1548 END IF;
1549 --END IF;
1550 ELSE
1551 /*Need to split both lot and serial tables*/
1552 IF (l_debug = 1) THEN
1553 mydebug(' allocate_serial_flag is Y ');
1554 END IF;
1555
1556 OPEN mtlt(p_txn_temp_id);
1557
1558 LOOP
1559 FETCH mtlt
1560 INTO lot_row;
1561
1562 EXIT WHEN mtlt%NOTFOUND;
1563
1564 /***********Serial Stuff *****************************/
1565 IF (l_debug = 1) THEN
1566 mydebug('child lot row with temp id ' || lot_row.transaction_temp_id);
1567 END IF;
1568
1569 SELECT mtl_material_transactions_s.NEXTVAL
1570 INTO ser_transaction_temp_id
1571 FROM DUAL;
1572
1573 OPEN msnt(lot_row.serial_transaction_temp_id);
1574
1575 LOOP
1576 FETCH msnt
1577 INTO ser_row;
1578
1579 EXIT WHEN msnt%NOTFOUND;
1580
1581 IF (l_debug = 1) THEN
1582 mydebug('child lot ser row with temp id ' || ser_row.transaction_temp_id);
1583 END IF;
1584
1585 SELECT mtl_material_transactions_s.NEXTVAL
1586 INTO ser_row.transaction_temp_id
1587 FROM DUAL;
1588
1589 ser_row.transaction_temp_id := ser_transaction_temp_id;
1590 inv_rcv_common_apis.insert_msnt(ser_row);
1591 /* Swapped the stmts, so that serial_transaction_temp_id is
1592 * correctly stamped in MTLT*/
1593 lot_row.serial_transaction_temp_id := ser_row.transaction_temp_id;
1594 ser_row := NULL;
1595 END LOOP;
1596
1597 IF msnt%ISOPEN THEN
1598 CLOSE msnt;
1599 END IF;
1600
1601 /***********Serial Stuff *****************************/
1602 lot_row.transaction_temp_id := new_txn_temp_id;
1603 inv_rcv_common_apis.insert_mtlt(lot_row);
1604 lot_row := NULL;
1605 END LOOP;
1606
1607 IF mtlt%ISOPEN THEN
1608 CLOSE mtlt;
1609 END IF;
1610 END IF;
1611 END IF;
1612
1613 IF (l_debug = 1) THEN
1614 mydebug(' inserting the new row into mmtt using ' || 'wms_task_dispatch_engine.insert_mmtt ');
1615 END IF;
1616
1617 wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec => mmtt_row);
1618 END insert_new_mmtt_row_like;
1619 END wms_wip_xdock_pvt;