[Home] [Help]
PACKAGE BODY: APPS.WMS_CROSS_DOCK_PVT
Source
1 PACKAGE BODY WMS_Cross_Dock_Pvt AS
2 /* $Header: WMSCRDKB.pls 120.30.12020000.2 2012/07/04 06:33:19 abasheer ship $ */
3
4 -- Global constant holding the package name
5 TYPE num_tb IS TABLE OF NUMBER INDEX BY LONG; --9695544-made these global
6 l_mol_criteria_tb num_tb; --9695544
7
8 TYPE ser_num_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; --9816695
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_Cross_Dock_Pvt';
12
13
14 PROCEDURE mydebug(msg in varchar2)
15 IS
16 l_msg VARCHAR2(5100);
17 l_ts VARCHAR2(30);
18 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
19 BEGIN
20
21 select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
22 l_msg:=l_ts||' '||msg;
23
24
25 inv_mobile_helper_functions.tracelog
26 (p_err_msg => l_msg,
27 p_module => 'wms_cross_dock_pvt',
28 p_level => 4);
29 -- dbms_output.put_line(msg);
30 --INSERT INTO amintemp1 VALUES (msg);
31 null;
32 END mydebug ;
33
34
35
36 --9695544-Added the following procedure.
37 procedure insert_xdock_criteria( p_move_order_line_id IN NUMBER
38 , p_crossdock_criterion_id IN NUMBER
39 , x_return_status OUT nocopy VARCHAR2
40 , x_msg_data OUT nocopy VARCHAR2
41 , x_msg_count OUT nocopy NUMBER
42 ) IS
43 BEGIN
44 l_mol_criteria_tb( p_move_order_line_id) := p_crossdock_criterion_id ;
45 END insert_xdock_criteria;
46
47 --bug#9816695,added following function
48 /* Local procedure to insert record in MTL_SERIAL_NUMBERS_TEMP
49 input :
50 lpn_id, serial_numbers(pl/sql table),inventory_item_id,transaction_temp_id
51
52 output : Success or Failur
53
54 This will also update MSN with transaction_temp_id as group mark id
55 */
56 PROCEDURE insert_xdock_msnt
57 (
58 p_org_id IN NUMBER,
59 p_item_id IN NUMBER,
60 p_transaction_temp_id IN NUMBER ,
61 p_serial_number_table IN ser_num_tab ,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2
65 )
66 IS
67 l_user_id NUMBER;
68 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
69 BEGIN
70 -- Initialize API return status to success
71 x_return_status := fnd_api.g_ret_sts_success;
72
73 l_user_id := fnd_global.user_id;
74
75 IF (l_debug = 1) THEN
76 mydebug('insert_xdock_msnt:Entered. ' );
77 mydebug('insert_xdock_msnt:p_org_id:'||p_org_id );
78 mydebug('insert_xdock_msnt:p_item_id:'||p_item_id );
79 mydebug('insert_xdock_msnt:p_user_id:'||l_user_id );
80 mydebug('insert_xdock_msnt:p_transaction_temp_id:'||p_transaction_temp_id );
81 mydebug('insert_xdock_msnt:p_serial_number_table.count():' || p_serial_number_table.count);
82 END IF;
83
84 FORALL i IN 1 .. p_serial_number_table.COUNT
85 INSERT INTO mtl_serial_numbers_temp
86 (
87 transaction_temp_id ,
88 last_update_date ,
89 last_updated_by ,
90 creation_date ,
91 created_by ,
92 fm_serial_number ,
93 to_serial_number ,
94 serial_prefix ,
95 vendor_serial_number ,
96 vendor_lot_number ,
97 parent_serial_number ,
98 origination_date ,
99 end_item_unit_number ,
100 territory_code ,
101 time_since_new ,
102 cycles_since_new ,
103 time_since_overhaul ,
104 cycles_since_overhaul ,
105 time_since_repair ,
106 cycles_since_repair ,
107 time_since_visit ,
108 cycles_since_visit ,
109 time_since_mark ,
110 cycles_since_mark ,
111 number_of_repairs ,
112 serial_attribute_category ,
113 c_attribute1 ,
114 c_attribute2 ,
115 c_attribute3 ,
116 c_attribute4 ,
117 c_attribute5 ,
118 c_attribute6 ,
119 c_attribute7 ,
120 c_attribute8 ,
121 c_attribute9 ,
122 c_attribute10 ,
123 c_attribute11 ,
124 c_attribute12 ,
125 c_attribute13 ,
126 c_attribute14 ,
127 c_attribute15 ,
128 c_attribute16 ,
129 c_attribute17 ,
130 c_attribute18 ,
131 c_attribute19 ,
132 c_attribute20 ,
133 d_attribute1 ,
134 d_attribute2 ,
135 d_attribute3 ,
136 d_attribute4 ,
137 d_attribute5 ,
138 d_attribute6 ,
139 d_attribute7 ,
140 d_attribute8 ,
141 d_attribute9 ,
142 d_attribute10 ,
143 n_attribute1 ,
144 n_attribute2 ,
145 n_attribute3 ,
146 n_attribute4 ,
147 n_attribute5 ,
148 n_attribute6 ,
149 n_attribute7 ,
150 n_attribute8 ,
151 n_attribute9 ,
152 n_attribute10
153 )
154 (
155 SELECT
156 p_transaction_temp_id ,
157 sysdate ,
158 l_user_id ,
159 sysdate ,
160 l_user_id ,
161 serial_number ,
162 serial_number ,
163 1 ,
164 vendor_serial_number ,
165 vendor_lot_number ,
166 parent_serial_number ,
167 origination_date ,
168 end_item_unit_number ,
169 territory_code ,
170 time_since_new ,
171 cycles_since_new ,
172 time_since_overhaul ,
173 cycles_since_overhaul ,
174 time_since_repair ,
175 cycles_since_repair ,
176 time_since_visit ,
177 cycles_since_visit ,
178 time_since_mark ,
179 cycles_since_mark ,
180 number_of_repairs ,
181 serial_attribute_category ,
182 c_attribute1 ,
183 c_attribute2 ,
184 c_attribute3 ,
185 c_attribute4 ,
186 c_attribute5 ,
187 c_attribute6 ,
188 c_attribute7 ,
189 c_attribute8 ,
190 c_attribute9 ,
191 c_attribute10 ,
192 c_attribute11 ,
193 c_attribute12 ,
194 c_attribute13 ,
195 c_attribute14 ,
196 c_attribute15 ,
197 c_attribute16 ,
198 c_attribute17 ,
199 c_attribute18 ,
200 c_attribute19 ,
201 c_attribute20 ,
202 d_attribute1 ,
203 d_attribute2 ,
204 d_attribute3 ,
205 d_attribute4 ,
206 d_attribute5 ,
207 d_attribute6 ,
208 d_attribute7 ,
209 d_attribute8 ,
210 d_attribute9 ,
211 d_attribute10 ,
212 n_attribute1 ,
213 n_attribute2 ,
214 n_attribute3 ,
215 n_attribute4 ,
216 n_attribute5 ,
217 n_attribute6 ,
218 n_attribute7 ,
219 n_attribute8 ,
220 n_attribute9 ,
221 n_attribute10
222 FROM mtl_serial_numbers msn
223 WHERE msn.inventory_item_id = p_item_id
224 AND msn.current_organization_id = p_org_id
225 AND msn.serial_number = p_serial_number_table(i)
226 );
227
228
229 IF (l_debug = 1) THEN
230 mydebug('insert_xdock_msnt:Done with inserting MSNTs. ' );
231 END IF;
232
233
234 FORALL i IN 1 .. p_serial_number_table.COUNT
235 UPDATE mtl_serial_numbers
236 SET group_mark_id = p_transaction_temp_id,
237 last_update_date = sysdate,
238 last_updated_by = l_user_id
239 WHERE inventory_item_id = p_item_id
240 AND current_organization_id = p_org_id
241 AND serial_number = p_serial_number_table (i);
242
243 IF (l_debug = 1) THEN
244 mydebug('insert_xdock_msnt:Done with updating MSN. ' );
245 mydebug('insert_xdock_msnt:Exiting..');
246 END IF;
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 x_return_status := fnd_api.g_ret_sts_unexp_error;
251 IF (l_debug = 1) THEN
252 mydebug('insert_xdock_msnt:EXCEPTION !!!. ' );
253 END IF;
254 END insert_xdock_msnt;
255
256 --
257 -- Checkcrossdock logic will first cal rules engine to derive crossdock criteria,
258 -- and stamp the crossdock criteria to move order line. It wil call pegging API to
259 -- peg the move order to a set of demands. For outbound shipment demand without a
260 -- delivery, the demand will be merged into existing delivery or a new delivery
261 -- will be created. For shipment demand, destination sub/loc will be determined based
262 -- on delivery based consolidation. Destination sub/loc will be stamped on to move
263 -- order line so that rules engine will honor that for generating putaway tasks.
264 --
265 -- This API will handle both opportunistic crossdock and planned crossdock
266 -- gxiao 4/18/05
267 -- {{********************** check_crossdock ************************************}}
268 --
269
270 -- original procedure name is 'crossdock'. add new procedure 'check_crossdock'
271 -- TODO: how to handle the dual maintainance: break it or add version
272
273 PROCEDURE crossdock
274 (p_org_id IN NUMBER ,
275 p_lpn IN NUMBER ,
276 x_ret OUT NOCOPY NUMBER ,
277 x_return_status OUT NOCOPY VARCHAR2 ,
278 x_msg_count OUT NOCOPY NUMBER ,
279 x_msg_data OUT NOCOPY VARCHAR2 ,
280 p_move_order_line_id IN NUMBER DEFAULT NULL
281 ) IS
282
283 l_api_name CONSTANT VARCHAR2(30) := 'check_crossdock';
284 l_progress VARCHAR2(10);
285 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
286
287 l_org_id NUMBER;
288 l_lpn_id NUMBER;
289 l_line_id NUMBER;
290 l_backorder_delivery_detail_id NUMBER;
291 l_wip_supply_type NUMBER;
292 l_wip_entity_id NUMBER;
293 l_operation_sequence_number NUMBER;
294 l_repetitive_schedule_id NUMBER;
295
296 l_reference VARCHAR2(240);
297 l_reference_id NUMBER;
298 l_location_id NUMBER;
299 l_ship_to_location_id NUMBER;
300
301 l_xdock_flag NUMBER;
302 l_default_xdock_criteria_id NUMBER;
303 l_default_xdock_sub VARCHAR2(10);
304 l_default_xdock_loc_id NUMBER;
305 l_default_ship_staging_sub VARCHAR2(10);
306 l_default_ship_staging_loc_id NUMBER;
307
308
309
310 l_to_sub_code VARCHAR2(10);
311 l_to_loc_id NUMBER;
312 l_to_zone_id NUMBER;
313
314 l_xdock_type NUMBER;
315
316 l_inventory_item_id NUMBER;
317 l_project_id NUMBER;
318 l_task_id NUMBER;
319 l_uom_code VARCHAR2(3);
320 l_item_type VARCHAR2(30);
321 l_uom_class VARCHAR2(10);
322 l_user_id NUMBER;
323 l_vendor_id NUMBER;
324
325 l_xdock_criterion_id NUMBER;
326 l_xdock_criteria wms_crossdock_criteria%ROWTYPE;
327 l_dock_appointment_id NUMBER;
328 l_dock_start_time DATE;
329 l_dock_end_time DATE;
330 l_expected_delivery_time DATE;
331 l_dummy1 DATE;
332 l_dummy2 DATE;
333 l_dummy3 DATE;
334 l_source_type_id NUMBER;
335 l_source_header_id NUMBER;
336 l_source_line_id NUMBER;
337
338 l_matched_delivery_id NUMBER;
339 l_matched_dock_appointment_id NUMBER;
340 l_matched_dock_start_time DATE;
341 l_matched_dock_end_time DATE;
342 l_matched_expected_del_time DATE;
343
344
345 -- Crossdock Criteria time interval values
346 -- INTERVAL DAY TO SECOND type stores the number of days and seconds
347 l_xdock_window_interval INTERVAL DAY TO SECOND;
348 l_buffer_interval INTERVAL DAY TO SECOND;
349 l_processing_interval INTERVAL DAY TO SECOND;
350
351 l_return_type VARCHAR2(10);
352 l_sequence_number NUMBER;
353
354 --l_sequence_number wms_selection_criteria_txn.sequence_number%type;
355 --l_return_type_code wms_selection_criteria_txn.return_type_code%type;
356 --l_return_type_id wms_selection_criteria_txn.return_type_id%type;
357 l_index NUMBER;
358 l_cache BOOLEAN;
359
360 l_attr_tab wsh_integration.grp_attr_tab_type;
361 l_action_rec wsh_integration.action_rec_type;
362 l_target_rec wsh_integration.grp_attr_rec_type;
363 l_matched_entities wsh_util_core.id_tab_type;
364 l_out_rec wsh_integration.out_rec_type;
365 l_group_info wsh_integration.grp_attr_tab_type;
366
367 l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
368 l_action_out_rec wsh_glbl_var_strct_grp.dd_action_out_rec_type;
369 l_defaults_rec wsh_glbl_var_strct_grp.dd_default_parameters_rec_type;
370 -- l_detail_id_tab wsh_util_core.id_tab_type;
371 l_rec_attr_tab WSH_GLBL_VAR_STRCT_GRP.delivery_details_attr_tbl_type;
372
373 l_delivery_id NUMBER;
374
375 l_txn_src_type_id NUMBER;
376
377 l_return_status VARCHAR2(1);
378 l_msg_count NUMBER;
379 l_msg_data VARCHAR2(240);
380 l_message VARCHAR2(240);
381 l_lpn_context NUMBER; --Bug# 13043984
382
383 --e_return_excp EXCEPTION;
384
385 -- create cursor matching the condition:
386 -- 1. the MTRL is within the LPN
387 -- 2. the MTRL has not been pegged to demand by planned crossdocking
388 -- 3. there has not been any MMTT generated for this MTRL
389 -- 4. this MTRL does not require inspection or has been accepted }}
390 CURSOR c_mol_opportunistic IS
391 SELECT mtrl.line_id,
392 mtrl.inventory_item_id,
393 msi.item_type,
394 mtrl.project_id,
395 mtrl.task_id,
396 mtrl.uom_code,
397 muom.uom_class,
398 mtrl.last_update_login,
399 mtrl.reference,
400 mtrl.reference_id,
401 mtrl.transaction_source_type_id
402 FROM mtl_txn_request_lines mtrl,
403 mtl_system_items msi,
404 mtl_units_of_measure muom
405 WHERE mtrl.lpn_id = l_lpn_id -- the MTRL is within the LPN
406 AND (mtrl.line_id = p_move_order_line_id OR p_move_order_line_id IS NULL)
407 AND mtrl.backorder_delivery_detail_id IS NULL -- this LPN has not crossdocked yet
408 AND (mtrl.quantity_detailed = 0 OR mtrl.quantity_detailed IS NULL) -- no MMTT is created for this LPN
409 AND (inspection_status = 2 OR inspection_status IS NULL)
410 AND wms_process_flag = 1
411 AND msi.inventory_item_id = mtrl.inventory_item_id
412 AND msi.organization_id = mtrl.organization_id
413 AND mtrl.uom_code = muom.uom_code;
414
415 -- MRTL which has been pegged to demand by either planned or opportunistic crossdocking
416 CURSOR c_mol_opp_and_planned IS
417 SELECT line_id,
418 inventory_item_id,
419 backorder_delivery_detail_id,
420 crossdock_type,
421 to_subinventory_code,
422 to_locator_id,
423 wip_supply_type,
424 wip_entity_id,
425 operation_seq_num,
426 repetitive_schedule_id,
427 transaction_source_type_id
428 FROM
429 mtl_txn_request_lines mtrl
430 WHERE mtrl.lpn_id = l_lpn_id -- the MTRL is within the LPN
431 AND mtrl.line_id = NVL(p_move_order_line_id, mtrl.line_id)
432 AND mtrl.backorder_delivery_detail_id IS NOT NULL -- also including lines planned crossdocked
433 AND (mtrl.quantity_detailed = 0 OR mtrl.quantity_detailed IS NULL) -- no MMTT is created for this LPN
434 AND NVL(inspection_status, 2) = 2
435 AND wms_process_flag = 1
436
437 --BUG 5194761: If case of Item Load, p_move_order_line will be passed
438 --and we need to pick up the MOL that may be split by the crossdock API.
439 --So make use of the mtrl.reference_detail_id
440 UNION
441 SELECT line_id,
442 inventory_item_id,
443 backorder_delivery_detail_id,
444 crossdock_type,
445 to_subinventory_code,
446 to_locator_id,
447 wip_supply_type,
448 wip_entity_id,
449 operation_seq_num,
450 repetitive_schedule_id,
451 transaction_source_type_id
452 FROM
453 mtl_txn_request_lines mtrl
454 WHERE mtrl.lpn_id = l_lpn_id -- the MTRL is within the LPN
455 AND p_move_order_line_id IS NOT NULL
456 AND mtrl.reference_detail_id = p_move_order_line_id
457 AND mtrl.backorder_delivery_detail_id IS NOT NULL -- also including lines planned crossdocked
458 AND (mtrl.quantity_detailed = 0 OR mtrl.quantity_detailed IS NULL) -- no MMTT is created for this LPN
459 AND NVL(inspection_status, 2) = 2
460 AND wms_process_flag = 1;
461
462
463
464 BEGIN
465
466 IF (l_debug = 1) THEN
467 mydebug('***Calling check_crossdock API with the following parameters***');
468 mydebug('p_org_id: ==================> ' || p_org_id);
469 mydebug('p_lpn: =====================> ' || p_lpn);
470 mydebug('p_move_order_line_id: ======> ' || p_move_order_line_id);
471 END IF;
472
473 -- Set the savepoint
474 SAVEPOINT check_crossdock_sp;
475 l_progress := '10';
476
477 -- Initialize message list to clear any existing messages
478 -- fnd_msg_pub.initialize;
479 l_progress := '20';
480 l_mol_criteria_tb.DELETE; --9695544
481
482 -- Initialize API return status to success
483 x_return_status := fnd_api.g_ret_sts_success;
484 x_ret := 1;
485 l_progress := '30';
486
487 l_lpn_id := p_lpn;
488 l_org_id := p_org_id;
489
490 -- get org level crossdock related info
491 -- 1. opportunistic crossdock enabled flag
492 -- 2. default crossdock criteria
493 -- 3. default mfg staging sub/loc
494 BEGIN
495 SELECT Nvl(mp.crossdock_flag, 2),
496 mp.default_crossdock_criteria_id,
497 mp.default_crossdock_subinventory, -- default wip crossdocking sub
498 mp.default_crossdock_locator_id, -- default wip crossdocking loc
499 wsp.default_stage_subinventory,
500 wsp.default_stage_locator_id
501 INTO l_xdock_flag,
502 l_default_xdock_criteria_id,
503 l_default_xdock_sub, -- default wip crossdocking sub
504 l_default_xdock_loc_id,-- default wip crossdocking loc
505 l_default_ship_staging_sub,
506 l_default_ship_staging_loc_id
507 FROM mtl_parameters mp, wsh_shipping_parameters wsp
508 WHERE mp.organization_id = wsp.organization_id (+)
509 AND mp.organization_id = l_org_id;
510 EXCEPTION
511 WHEN OTHERS THEN
512 IF (l_debug = 1) THEN
513 mydebug('Exit from check_crossdock, failed to get org level data. ' );
514 END IF;
515
516 RETURN;
517 END;
518
519 IF (l_debug = 1) THEN
520 mydebug('l_xdock_flag = '||l_xdock_flag );
521 mydebug('l_default_xdock_criteria_id = '||l_default_xdock_criteria_id );
522 mydebug('l_default_xdock_sub = '||l_default_xdock_sub );
523 mydebug('l_default_xdock_loc_id = '||l_default_xdock_loc_id );
524 mydebug('l_default_ship_staging_sub = '||l_default_ship_staging_sub );
525
526 mydebug('l_default_ship_staging_loc_id = '||l_default_ship_staging_loc_id );
527
528 END IF;
529
530 l_progress := '50';
531
532 -- If opportunistic crossdock enabled, then
533 -- loop through MTRL records that match the following condition:
534 -- 1. the MTRL is within the LPN
535 -- 2. the MTRL has not been pegged to demand by planned crossdocking
536 -- 3. there has not been any MMTT generated for this MTRL
537 -- 4. this MTRL does not require inspection or has been accepted
538
539 IF l_xdock_flag = 1 THEN
540
541 --{{
542 -- When org level opportunistics crossdock is enabled should crossdock the received
543 -- move order line to demand.
544 --
545 -- When org level opportunistics crossdock is NOT enabled don't do crossdock,
546 -- however for lines that have been pegged, should merge/create delivery
547 -- and suggest staging lane based on delivery.
548 --}}
549
550 IF (l_debug = 1) THEN
551 mydebug('Opportunistics crossdock enabled. ' );
552 END IF;
553
554 BEGIN
555 SELECT location_id
556 INTO l_location_id
557 FROM rcv_supply
558 WHERE lpn_id = p_lpn
559 AND ROWNUM<2;
560 EXCEPTION
561 WHEN OTHERS THEN
562 IF (l_debug = 1) THEN
563 mydebug('Failed to get location_id from rcv_supply. ' );
564 END IF;
565
566 END;
567
568 IF (l_debug = 1) THEN
569 mydebug('l_location_id = '|| l_location_id);
570 END IF;
571
572 OPEN c_mol_opportunistic;
573 -- enter MTRL loop including only opportunistic
574 LOOP
575 FETCH c_mol_opportunistic INTO
576 l_line_id,
577 l_inventory_item_id,
578 l_item_type,
579 l_project_id,
580 l_task_id,
581 l_uom_code,
582 l_uom_class,
583 l_user_id,
584 l_reference,
585 l_reference_id,
586 l_txn_src_type_id;
587
588
589 EXIT WHEN c_mol_opportunistic%notfound;
590
591 IF (l_debug = 1) THEN
592 mydebug('l_line_id = '||l_line_id );
593 mydebug('l_inventory_item_id = '|| l_inventory_item_id);
594 mydebug('l_item_type = '|| l_item_type);
595 mydebug('l_project_id = '|| l_project_id);
596 mydebug('l_task_id = '|| l_task_id);
597 mydebug('l_uom_code = '|| l_uom_code);
598 mydebug('l_uom_class = '|| l_uom_class);
599 mydebug('l_user_id = '|| l_user_id);
600 mydebug('l_reference = '|| l_reference);
601 mydebug('l_reference_id = '|| l_reference_id);
602 mydebug('l_txn_src_type_id = '|| l_txn_src_type_id);
603 END IF;
604
605 l_progress := '60';
606
607
608 -- {{ call wms_rules_workbench_pvt.get_unit_of_measure }}
609 -- {{ call wms_rules_workbench_pvt.get_vendor_id }}
610 IF (l_debug = 1) THEN
611 mydebug('***Calling wms_rules_workbench_pvt.get_vendor_id***');
612 END IF;
613
614 l_vendor_id := wms_rules_workbench_pvt.get_vendor_id(l_reference, l_reference_id);
615
616 l_progress := '100';
617
618
619
620 -- TODO: check with ANIL on the parameters
621 -- {{ call wms_rules_workbench_pvt.cross_dock_search to get crossdock_criteria_id }}
622 IF (l_debug = 1) THEN
623 mydebug('***Calling wms_rules_workbench_pvt.cross_dock_search with the following parameters***');
624 mydebug('p_rule_type_code: ==========> 10');
625 mydebug('p_organization_id: =========> ' || p_org_id);
626 mydebug('p_customer_id: =============> ' || NULL);
627 mydebug('p_inventory_item_id: =======> ' || l_inventory_item_id);
628 mydebug('p_item_type: ===============> ' || l_item_type);
629 mydebug('p_vendor_id: ===============> ' || l_vendor_id);
630 mydebug('p_location_id: =============> ' || l_location_id);
631 mydebug('p_project_id: ==============> ' || l_project_id);
632 mydebug('p_task_id: =================> ' || l_task_id);
633 mydebug('p_user_id: =================> ' || l_user_id);
634 mydebug('p_uom_code: ================> ' || l_uom_code);
635 mydebug('p_uom_class: ===============> ' || l_uom_class);
636 mydebug('p_date_type: ===============> ' || NULL);
637 mydebug('p_from_date: ===============> ' || NULL);
638 mydebug('p_to_date: =================> ' || NULL);
639 mydebug('p_criterion_type: ==========> 1');
640 END IF;
641
642 wms_rules_workbench_pvt.cross_dock_search(
643 p_rule_type_code => 10, -- supply_initiated_crossdock
644 p_organization_id => l_org_id,
645 p_customer_id => NULL, -- opportunistic crossdock
646 p_inventory_item_id => l_inventory_item_id,
647 -- p_category_id => l_category_id,
648 p_item_type => l_item_type,
649 p_vendor_id => l_vendor_id,
650 p_location_id => l_location_id,
651 p_project_id => l_project_id,
652 p_task_id => l_task_id,
653 p_user_id => l_user_id,
654 p_uom_code => l_uom_code,
655 p_uom_class => l_uom_class,
656 -- p_date_type => NULL,
657 -- p_from_date => NULL,
658 -- p_to_date => NULL,
659 -- p_criterion_type => 1, --Opportunistic (1) or Planned (2)
660 x_return_type => l_return_type,
661 x_return_type_id => l_xdock_criterion_id, --criterion_id
662 x_sequence_number => l_sequence_number,
663 x_return_status => l_return_status
664 );
665
666 -- Bug 4576491
667 IF l_xdock_criterion_id IS NULL THEN
668 l_xdock_criterion_id := l_default_xdock_criteria_id;
669 END IF;
670
671 IF (l_debug = 1) THEN
672 mydebug('***After calling wms_rules_workbench_pvt.cross_dock_search ***');
673 mydebug('l_return_type = '||l_return_type);
674 mydebug('l_xdock_criterion_id = '||l_xdock_criterion_id);
675 mydebug('l_sequence_number = '||l_sequence_number);
676 mydebug('l_return_status = '||l_return_status);
677 END IF;
678
679 IF (l_txn_src_type_id = 5) THEN --WIP
680 mydebug('l_xdock_criterion_id='||l_xdock_criterion_id);
681 mydebug('l_line_id='|| l_line_id);
682
683 l_mol_criteria_tb(l_line_id) := l_xdock_criterion_id;
684 END IF;
685
686 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
687 FND_MESSAGE.SET_NAME('WMS','WMS_XDOK_SEARCH_ERROR' );
688 FND_MSG_PUB.ADD;
689 RAISE FND_API.g_exc_unexpected_error;
690
691 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
692 FND_MESSAGE.SET_NAME('WMS','WMS_XDOK_SEARCH_ERROR');
693 FND_MSG_PUB.ADD;
694 RAISE FND_API.g_exc_error;
695 END IF;
696
697 l_progress := '110';
698
699 -- {{ call wms_xdock_pegging_pub.opportunistic_cross_dock API, performing opportunistic
700 -- crossdock pegging to fulfill a move order line supply that has been received.
701 -- this API will find a set of demands that thas MTRL record can satisfy according to
702 -- crossdock criteria. The splitting of WDD lines, creation and splitting of reservations,
703 -- splitting and updating of MOL will all be done in the API. }}
704
705 IF (l_debug = 1) THEN
706 mydebug('***Calling wms_xdock_pegging_pub.opportunistic_cross_dock with the following parameters***');
707 mydebug('p_organization_id: =========> ' || l_org_id);
708 mydebug('p_move_order_line_id:=======> ' || l_line_id);
709 mydebug('p_crossdock_criterion_id: ==> ' || l_xdock_criterion_id);
710 END IF;
711
712 -- Bug 4576491
713 IF l_xdock_criterion_id IS NOT NULL THEN
714 -- Bug# 4662186
715 -- Cache crossdock criteria data first before calling the pegging API
716 IF (l_debug = 1) THEN
717 mydebug('***Calling wms_xdock_pegging_pub.set_crossdock_criteria***');
718 END IF;
719 l_cache := wms_xdock_pegging_pub.set_crossdock_criteria(l_xdock_criterion_id);
720
721 wms_xdock_pegging_pub.Opportunistic_Cross_Dock
722 (p_organization_id => l_org_id,
723 p_move_order_line_id => l_line_id,
724 p_crossdock_criterion_id => l_xdock_criterion_id,
725 x_return_status => l_return_status,
726 x_msg_count => l_msg_count,
727 x_msg_data => l_msg_data);
728
729 IF (l_debug = 1) THEN
730 mydebug('***After calling wms_xdock_pegging_pub.Opportunistic_Cross_Dock ***');
731 mydebug('l_return_status = '||l_return_status);
732 mydebug('l_msg_count = '||l_msg_count);
733 mydebug('l_msg_data = '||l_msg_data);
734 END IF;
735
736 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
737 FND_MESSAGE.SET_NAME('WMS','WMS_OPP_XDOK_ERROR' );
738 FND_MSG_PUB.ADD;
739 RAISE FND_API.g_exc_unexpected_error;
740
741 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
742 FND_MESSAGE.SET_NAME('WMS','WMS_OPP_XDOK_ERROR');
743 FND_MSG_PUB.ADD;
744 RAISE FND_API.g_exc_error;
745 END IF;
746
747 l_progress := '120';
748
749 END IF; -- l_xdock_criterion_id is not null
750
751 -- {{ end MTRL loop }}
752 END LOOP;
753
754 CLOSE c_mol_opportunistic;
755
756 -- Bug# 4662186
757 -- Clear the crossdock criteria cache when pegging has completed
758 IF (l_debug = 1) THEN
759 mydebug('***Calling wms_xdock_pegging_pub.clear_crossdock_cache***');
760 END IF;
761 l_cache := wms_xdock_pegging_pub.clear_crossdock_cache;
762
763 l_progress := '130';
764 END IF;
765
766 -- Now start creating and merging delivery, and determine staging lane.
767 -- Open another MTRL cursor loop for those lines that get crossdocked (including
768 -- new MTRL created by the split). This new cursor takes into consideration of planned
769 -- crossdock.
770
771 OPEN c_mol_opp_and_planned;
772
773 -- enter MTRL loop including both opportunistic and planned crossdock
774 LOOP
775 FETCH c_mol_opp_and_planned INTO
776 l_line_id,
777 l_inventory_item_id,
778 l_backorder_delivery_detail_id,
779 l_xdock_type,
780 l_to_sub_code,
781 l_to_loc_id,
782 l_wip_supply_type,
783 l_wip_entity_id,
784 l_operation_sequence_number,
785 l_repetitive_schedule_id,
786 l_txn_src_type_id;
787
788 EXIT WHEN c_mol_opp_and_planned%NOTFOUND;
789
790 -- at least one line is crossdocked for this LPN
791 x_ret := 0;
792
793 -- {{ merge/create delivery only applicable for sales order or internal
794 -- order demand. }}
795
796 IF (l_xdock_type = 2) THEN
797 --{{
798 -- When crossdock to WIP work order, get staging locator based on following logic
799 -- 1. First try to get staging sub/loc from the job
800 -- 2. If can't find from job for a pull job, get from wip parameter
801 --}}
802 BEGIN
803 -- first get the sub and loc from wip_requirement_operations_v
804
805 SELECT nvl(nvl(supply_subinventory, mp.default_crossdock_subinventory), wp.default_pull_supply_subinv),
806 nvl(nvl(supply_locator_id, mp.default_crossdock_locator_id), wp.default_pull_supply_locator_id)
807 INTO l_to_sub_code,
808 l_to_loc_id
809 FROM wip_requirement_operations wro,
810 mtl_txn_request_lines mtrl,
811 mtl_parameters mp,
812 wip_parameters wp
813 WHERE wro.organization_id = l_org_id
814 AND mp.organization_id = l_org_id
815 AND wp.organization_id = l_org_id
816 AND mtrl.line_id = l_line_id
817 AND wro.inventory_item_id = mtrl.inventory_item_id
818 AND wro.wip_entity_id = mtrl.wip_entity_id
819 AND nvl(wro.operation_seq_num, -1) = Nvl(mtrl.operation_seq_num, nvl(wro.operation_seq_num, -1))
820 AND nvl(wro.repetitive_schedule_id, -1) = Nvl(mtrl.repetitive_schedule_id, nvl(wro.repetitive_schedule_id, -1));
821
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824 NULL;
825 WHEN OTHERS THEN
826 IF (l_debug = 1) THEN
827 mydebug('Unexpected error, skip this move order line.');
828 END IF;
829
830 GOTO loop_end;
831 END;
832
833 -- if for pull job and if sub and loc is NULL, use wip_parameters
834 -- 9/30/05: Also do this for WIP push
835 IF (l_to_sub_code IS NULL OR l_to_loc_id IS NULL) THEN
836
837 BEGIN
838 SELECT default_pull_supply_subinv,
839 default_pull_supply_locator_id
840 INTO l_to_sub_code,
841 l_to_loc_id
842 FROM wip_parameters
843 WHERE organization_id = l_org_id;
844
845 EXCEPTION
846 WHEN NO_DATA_FOUND THEN
847 NULL;
848 WHEN OTHERS THEN
849 IF (l_debug = 1) THEN
850 mydebug('Unexpected error, skip this move order line.');
851 END IF;
852
853 GOTO loop_end;
854 END;
855
856 END IF;
857
858
859 ELSE
860 /* Changes for Bug# 13043984 start */
861 IF (l_debug = 1) THEN
862 mydebug('SO flow...l_backorder_delivery_detail_id:'||l_backorder_delivery_detail_id);
863 END IF;
864 --get l_lpn_context
865 SELECT lpn_context
866 INTO l_lpn_context
867 FROM wms_license_plate_numbers
868 WHERE lpn_id = l_lpn_id;
869
870 IF (l_debug = 1) THEN
871 mydebug('LPN Context: ' || l_lpn_context);
872 END IF;
873 IF l_lpn_context IN (2, 3) THEN
874 -- Check for the detail_id for holds. If hold exists, skip the record -- Bug# 13043984
875 IF (l_debug = 1) THEN
876 mydebug('Calling wsh_details_validations.check_credit_holds.for l_backorder_delivery_detail_id='||l_backorder_delivery_detail_id);
877 END IF;
878
879 wsh_details_validations.check_credit_holds( p_detail_id=> l_backorder_delivery_detail_id
880 ,p_activity_type=> 'PICK'
881 ,p_source_code=> 'OE'
882 ,x_return_status=> l_return_status);
883
884 IF (l_debug = 1) THEN
885 mydebug('l_return_status:-'||l_return_status);
886 END IF;
887
888 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
889 IF (l_debug = 1) THEN
890 mydebug('Current Backorder/Scheduled SO line is on Hold.');
891 GOTO loop_end;
892 END IF;
893 END IF;
894 END IF;
895 /* Changes for Bug# 13043984 end */
896
897 BEGIN
898 SELECT delivery_id
899 INTO l_delivery_id
900 FROM wsh_delivery_assignments_v
901 WHERE delivery_detail_id = l_backorder_delivery_detail_id;
902 EXCEPTION
903 WHEN OTHERS THEN
904 NULL;
905 END;
906
907 IF (l_debug = 1) THEN
908 mydebug('l_delivery_id = '|| l_delivery_id);
909 END IF;
910
911 IF(l_delivery_id IS NULL) THEN
912
913 -- sales order crossdock
914 IF (l_debug = 1) THEN
915 mydebug('Delivery detail is not yet assigned to delivery.');
916 END IF;
917
918 l_progress := '140';
919 IF (l_txn_src_type_id = 5) THEN
920
921 IF (l_debug = 1) THEN
922 mydebug('l_line_id'||l_line_id||',count:'|| l_mol_criteria_tb.count);
923 mydebug('l_mol_criteria_tb(l_line_id)'||l_mol_criteria_tb(l_line_id));
924 END IF;
925
926 l_xdock_criterion_id := l_mol_criteria_tb(l_line_id);
927 IF (l_debug = 1) THEN
928 mydebug('WIP MOL. Xdock_criterial_id stored is: '||l_xdock_criterion_id);
929 END IF;
930
931 IF (l_xdock_criterion_id IS NOT NULL) THEN
932 IF (l_debug = 1) THEN
933 mydebug('Retrieving the l_expected_delivery_time');
934 END IF;
935
936 BEGIN
937 SELECT 2
938 , inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id)
939 , wdd.source_line_id
940 INTO l_source_type_id, l_source_header_id, l_source_line_id
941 FROM wsh_delivery_details wdd
942 WHERE wdd.delivery_detail_id = l_backorder_delivery_detail_id;
943 EXCEPTION
944 WHEN OTHERS THEN
945 IF (l_debug = 1) THEN
946 mydebug('Error retrieving SO info! Skip this mol!');
947 GOTO loop_end;
948 END IF;
949 END;
950
951 IF (l_debug = 1) THEN
952 mydebug('Calling wms_xdock_pegging_pub.get_expected_time');
953 mydebug(' p_source_type_id => '|| l_source_type_id);
954 mydebug(' p_source_header_id => '||l_source_header_id);
955 mydebug(' p_source_line_id => '||l_source_line_id);
956 mydebug(' p_source_line_detail_=> '||l_backorder_delivery_detail_id);
957 mydebug(' p_supply_or_demand => '||2);
958 mydebug(' p_crossdock_criterion=> '||l_xdock_criterion_id);
959 END IF;
960
961 wms_xdock_pegging_pub.get_expected_time
962 ( p_source_type_id => l_source_type_id
963 ,p_source_header_id => l_source_header_id
964 ,p_source_line_id => l_source_line_id
965 ,p_source_line_detail_id => l_backorder_delivery_detail_id
966 ,p_supply_or_demand => 2
967 ,p_crossdock_criterion_id => l_xdock_criterion_id
968 ,x_return_status => l_return_status
969 ,x_msg_count => l_msg_count
970 ,x_msg_data => l_msg_data
971 ,x_dock_start_time => l_dummy1
972 ,x_dock_mean_time => l_dummy2
973 ,x_dock_end_time => l_dummy3
974 ,x_expected_time => l_expected_delivery_time);
975
976 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
977 IF (l_debug = 1) THEN
978 mydebug('Unexpected error, skip this move order line.');
979 END IF;
980
981 GOTO loop_end;
982 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
983 IF (l_debug = 1) THEN
984 mydebug('Expected error, skip this move order line.');
985 END IF;
986
987 GOTO loop_end;
988 END IF;
989
990 IF (l_debug = 1) THEN
991 mydebug('After calling get_expected_time. l_expected_delivery_time = '||l_expected_delivery_time);
992 END IF;
993 END IF;--IF (l_xdock_criterion_id IS NOT NULL) THEN
994
995 ELSE
996 -- query crossdock criteria BT, OPT and crossdocking wondow
997 BEGIN
998 SELECT crossdock_criteria_id,
999 demand_ship_date
1000 INTO l_xdock_criterion_id,
1001 l_expected_delivery_time
1002 FROM mtl_reservations
1003 WHERE demand_source_line_detail = l_backorder_delivery_detail_id
1004 AND supply_source_type_id = inv_reservation_global.g_source_type_rcv
1005 AND organization_id = l_org_id
1006 AND inventory_item_id = l_inventory_item_id;
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 IF (l_debug = 1) THEN
1010 mydebug('Unexpected error, skip this move order line.');
1011 END IF;
1012
1013 GOTO loop_end;
1014 END;
1015 END IF;
1016
1017 l_progress := '150';
1018 IF (l_debug = 1) THEN
1019 mydebug(' l_xdock_criterion_id = '||l_xdock_criterion_id);
1020 END IF;
1021
1022 --For WIP, this maybe NULL. In this case, skip all logic
1023 --AND simply create the delivery
1024 IF (l_xdock_criterion_id IS NOT NULL) THEN
1025
1026 -- {{ get BT, OPT, and crossdocking_window from cached values }}
1027 l_xdock_criteria := wms_xdock_pegging_pub.get_crossdock_criteria(l_xdock_criterion_id);
1028
1029 IF (l_debug = 1) THEN
1030 mydebug('Crossdock Window: ' ||
1031 l_xdock_criteria.window_interval || ' ' ||
1032 l_xdock_criteria.window_uom);
1033 mydebug('Buffer Time: ' ||
1034 l_xdock_criteria.buffer_interval || ' ' ||
1035 l_xdock_criteria.buffer_uom);
1036 mydebug('Order Processing Time: ' ||
1037 l_xdock_criteria.processing_interval || ' ' ||
1038 l_xdock_criteria.processing_uom);
1039 END IF;
1040
1041 l_progress := '160';
1042
1043 -- crossdock window intertal
1044 l_xdock_window_interval := NUMTODSINTERVAL
1045 (l_xdock_criteria.window_interval,
1046 l_xdock_criteria.window_uom);
1047
1048 -- Buffer Time Interval
1049 -- The buffer time interval and UOM should either both be NULL or not NULL.
1050 l_buffer_interval := NUMTODSINTERVAL
1051 (NVL(l_xdock_criteria.buffer_interval, 0),
1052 NVL(l_xdock_criteria.buffer_uom, 'HOUR'));
1053
1054 -- Order Processing Time Interval
1055 -- The order processing time interval and UOM should either both be NULL or not NULL.
1056 l_processing_interval := NUMTODSINTERVAL
1057 (NVL(l_xdock_criteria.processing_interval, 0),
1058 NVL(l_xdock_criteria.processing_uom, 'HOUR'));
1059
1060 IF (l_debug = 1) THEN
1061 mydebug('Crossdock Window interval: ' || l_xdock_window_interval);
1062 mydebug('Buffer Time interval: ' || l_buffer_interval);
1063 mydebug('Order Processing Time interval: ' || l_processing_interval);
1064 END IF;
1065
1066
1067 -- {{ Merge/create delivery if there is no delivery for the WDD --
1068 -- only available for sales order or internal order demand }}
1069
1070 l_progress := '170';
1071
1072 -- {{ get possible matching deliveries }}
1073
1074 l_attr_tab(1).entity_id := l_backorder_delivery_detail_id;
1075 l_attr_tab(1).entity_type := 'DELIVERY_DETAIL';
1076 l_target_rec.entity_type := 'DELIVERY';
1077 l_action_rec.action := 'MATCH_GROUPS';
1078 l_action_rec.caller := 'WMS_CHECK_CROSSDOCK';
1079 l_action_rec.output_format_type := 'ID_TAB';
1080
1081 l_progress := '190';
1082
1083 -- {{ call wsh_delivery_autocreate.find_matching_groups}}
1084
1085 IF (l_debug = 1) THEN
1086 mydebug('***Calling wsh_integration.find_matching_groups***');
1087 mydebug('l_attr_tab(1).entity_id = ' ||l_backorder_delivery_detail_id);
1088 mydebug('l_attr_tab(1).entity_type = ' ||l_attr_tab(1).entity_type);
1089 mydebug('l_target_rec.entity_type = ' ||l_target_rec.entity_type);
1090 mydebug('l_action_rec.action = ' ||l_action_rec.action);
1091 mydebug('l_action_rec.caller = ' ||l_action_rec.caller);
1092 mydebug('l_action_rec.output_format_type = ' ||l_action_rec.output_format_type);
1093 END IF;
1094
1095 wsh_integration.find_matching_groups
1096 (p_attr_tab => l_attr_tab,
1097 p_action_rec => l_action_rec,
1098 p_target_rec => l_target_rec,
1099 p_group_tab => l_group_info,
1100 x_matched_entities => l_matched_entities,
1101 x_out_rec => l_out_rec,
1102 x_return_status => l_return_status);
1103
1104 IF (l_debug = 1) THEN
1105 mydebug('***After calling wsh_delivery_autocreate.find_matching_groups***');
1106 mydebug('x_return_status = '||l_return_status);
1107 mydebug('l_matched_entities.count = '||l_matched_entities.count);
1108 END IF;
1109
1110
1111 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1112 IF (l_debug = 1) THEN
1113 mydebug('Unexpected error, skip this move order line.');
1114 END IF;
1115
1116 GOTO loop_end;
1117 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1118 IF (l_debug = 1) THEN
1119 mydebug('Expected error, skip this move order line.');
1120 END IF;
1121
1122 GOTO loop_end;
1123 END IF;
1124
1125 l_progress := '200';
1126
1127 -- {{ loop those deliveries returned by WSH API
1128 -- check whether the delivery satisfies the crossdocking window and related
1129 -- time constraints) based on crossdock criteria;
1130 FOR l_index IN 1..l_matched_entities.COUNT LOOP
1131 -- {{ call wms_xdock_pegging_pub.get_expected_delivery_time}}
1132
1133 IF (l_debug = 1) THEN
1134 mydebug('***Calling wms_xdock_pegging_pub.get_expected_delivery_time***');
1135 mydebug('p_delivery_id = '||l_matched_entities(l_index));
1136 mydebug('p_crossdock_criterion_id = '||l_xdock_criterion_id);
1137 END IF;
1138
1139 wms_xdock_pegging_pub.get_expected_delivery_time
1140 (p_delivery_id => l_matched_entities(l_index),
1141 p_crossdock_criterion_id => l_xdock_criterion_id,
1142 x_return_status => l_return_status,
1143 x_msg_count => l_msg_count,
1144 x_msg_data => l_msg_data,
1145 x_dock_appointment_id => l_matched_dock_appointment_id,
1146 x_dock_start_time => l_matched_dock_start_time,
1147 x_dock_end_time => l_matched_dock_end_time,
1148 x_expected_time => l_matched_expected_del_time);
1149
1150 IF (l_debug = 1) THEN
1151 mydebug('***After calling wms_xdock_pegging_pub.get_expected_delivery_time***');
1152 mydebug('x_dock_appointment_id = '||l_matched_dock_appointment_id);
1153 mydebug('x_dock_start_time = '||l_matched_dock_start_time);
1154 mydebug('x_dock_end_time = '||l_matched_dock_end_time);
1155 mydebug('x_expected_time = '||l_matched_expected_del_time);
1156 END IF;
1157
1158 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1159 IF (l_debug = 1) THEN
1160 mydebug('Unexpected error, skip this Delivery.');
1161 END IF;
1162
1163 GOTO delivery_loop_end;
1164 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1165 IF (l_debug = 1) THEN
1166 mydebug('Expected error, skip this Delivery.');
1167 END IF;
1168
1169 GOTO delivery_loop_end;
1170 END IF;
1171
1172 l_progress := '210';
1173
1174 -- {{ check whether this delivery found satisfies the crossdocking window based on
1175 -- crossdock criteria;
1176
1177 --the delivery will be eligible for merging if expected shipment
1178 -- date derived from delivery level fall into the crossdocking window.
1179 IF l_matched_expected_del_time IS NOT NULL THEN
1180
1181 IF ( l_matched_expected_del_time - l_buffer_interval- l_processing_interval
1182 < SYSDATE + l_xdock_window_interval AND
1183 l_matched_expected_del_time - l_buffer_interval- l_processing_interval > SYSDATE)
1184 THEN
1185
1186 IF (l_debug = 1) THEN
1187 mydebug('Found matching delivery '|| l_matched_entities(l_index) || ' with expected ship time : '||l_matched_expected_del_time);
1188 END IF;
1189
1190 l_matched_delivery_id := l_matched_entities(l_index);
1191 EXIT;
1192 END IF;
1193
1194 ELSE
1195 -- {{ if expected shipment date cannot be derived from delivery, the delivery will
1196 -- be eligible for merging if the current WDD's expected shipment date falls between
1197 -- the range of expected shipment dates of all WDDsfor this delivery. }}
1198
1199 IF (l_expected_delivery_time < l_matched_dock_end_time)
1200 AND
1201 (l_expected_delivery_time > l_matched_dock_start_time)
1202 THEN
1203 IF (l_debug = 1) THEN
1204 mydebug('Found matching delivery '||l_matched_entities(l_index) || ' with dock appointment: '||l_matched_dock_start_time||' '||l_matched_dock_end_time);
1205 END IF;
1206 l_matched_delivery_id := l_matched_entities(l_index);
1207 EXIT;
1208 END IF;
1209
1210 END IF;
1211 <<delivery_loop_end>>
1212 NULL;
1213
1214 END LOOP;
1215 ELSE --(l_xdock_criterion_id IS NULL) THEN
1216 l_matched_delivery_id := NULL;
1217 END IF;--IF (l_xdock_criterion_id IS NOT NULL) THEN
1218
1219 l_progress := '220';
1220
1221 l_action_prms.caller := 'WMS_CHECK_CROSSDOCK';
1222 l_rec_attr_tab(1).delivery_detail_id := l_backorder_delivery_detail_id;
1223
1224 -- {{ if there is a matching delivery, merge WDD to this matching delivery }}
1225 IF (l_matched_delivery_id IS NOT NULL) THEN
1226 l_action_prms.action_code := 'ASSIGN';
1227 -- l_detail_id_tab(1) := l_backorder_delivery_detail_id;
1228 l_action_prms.delivery_id := l_matched_delivery_id;
1229 ELSE
1230 -- {{ if there is no matching delivery call, create a new record in wsh_new_deliveries }}
1231 l_action_prms.action_code := 'AUTOCREATE-DEL';
1232 END IF;
1233 l_progress := '230';
1234
1235
1236 -- {{ call wsh_delivery_autocreate.delivery_detail_action }}
1237
1238 IF (l_debug = 1) THEN
1239 mydebug('***Calling wsh_delivery_details_grp.delivery_detail_action***');
1240 mydebug('l_rec_attr_tab(1).delivery_detail_id = '||l_rec_attr_tab(1).delivery_detail_id );
1241 mydebug('l_action_prms.action_code = '||l_action_prms.action_code);
1242 mydebug('l_action_prms.delivery_id = '||l_action_prms.delivery_id);
1243 END IF;
1244
1245 wsh_delivery_details_grp.delivery_detail_action
1246 (
1247 -- Standard Parameters
1248 p_api_version_number => 1.0,
1249 p_init_msg_list => fnd_api.g_false,
1250 p_commit => fnd_api.g_false,
1251 x_return_status => l_return_status,
1252 x_msg_count => l_msg_count,
1253 x_msg_data => l_msg_data,
1254 -- Procedure specific Parameters
1255 p_rec_attr_tab => l_rec_attr_tab,
1256 p_action_prms => l_action_prms,
1257 x_defaults => l_defaults_rec,
1258 x_action_out_rec => l_action_out_rec
1259 );
1260
1261 IF (l_debug = 1) THEN
1262 mydebug('***After calling wsh_delivery_details_grp.delivery_detail_action***');
1263 mydebug('x_return_status = '||l_return_status);
1264 mydebug('x_msg_count = '||l_msg_count);
1265 mydebug('x_msg_data = '||l_msg_data);
1266 END IF;
1267
1268
1269
1270 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1271 IF (l_debug = 1) THEN
1272 mydebug('Unexpected error, skip this move order line.');
1273 END IF;
1274
1275 GOTO loop_end;
1276 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1277 IF (l_debug = 1) THEN
1278 mydebug('Expected error, skip this move order line.');
1279 END IF;
1280
1281 GOTO loop_end;
1282 END IF;
1283
1284 l_progress := '240';
1285
1286 END IF; -- end l_delivery_id is null
1287
1288 -- {{ determine staging lane and stamp MTRL }}
1289
1290 -- {{ call wms_op_dest_sys_apis.get_staging_loc_for_delivery }}
1291 IF (l_debug = 1) THEN
1292 mydebug('***Calling wms_op_dest_sys_apis.get_staging_loc_for_delivery with the following parameters***');
1293 mydebug('p_call_mode: ===============> ' || 1);
1294 mydebug('p_task_type: ===============> ' || 1);
1295 mydebug('p_task_id: =================> ' || NULL);
1296 mydebug('p_locator_id: ==============> ' || NULL);
1297 mydebug('p_mol_id: ==================> ' || l_line_id);
1298 END IF;
1299
1300 wms_op_dest_sys_apis.get_staging_loc_for_delivery
1301 (
1302 x_return_status => l_return_status,
1303 x_message => l_message,
1304 x_locator_id => l_to_loc_id,
1305 x_zone_id => l_to_zone_id,
1306 x_subinventory_code => l_to_sub_code,
1307 p_call_mode => 1,
1308 p_task_type => 1,
1309 p_task_id => NULL,
1310 p_locator_id => NULL,
1311 p_mol_id => l_line_id
1312 );
1313
1314 IF (l_debug = 1) THEN
1315 mydebug('***After calling wms_op_dest_sys_apis.get_staging_loc_for_delivery with the following parameters***');
1316 mydebug('x_return_status = ' || l_return_status);
1317 mydebug('x_message = ' || l_message);
1318 mydebug('x_locator_id = ' || l_to_loc_id);
1319 mydebug('x_zone_id = ' || l_to_zone_id);
1320 mydebug('x_subinventory_code = ' || l_to_sub_code);
1321 END IF;
1322
1323 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1324 IF (l_debug = 1) THEN
1325 mydebug('Unexpected error, skip this move order line.');
1326 END IF;
1327
1328 GOTO loop_end;
1329 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1330 IF (l_debug = 1) THEN
1331 mydebug('Expected error, skip this move order line.');
1332 END IF;
1333
1334 GOTO loop_end;
1335 END IF;
1336
1337
1338 l_progress := '250';
1339
1340 END IF; -- end sales order or WIP crossdock IF
1341
1342 IF (l_xdock_type = 2) THEN
1343
1344 UPDATE mtl_txn_request_lines
1345 SET to_subinventory_code = l_to_sub_code,
1346 to_locator_id = l_to_loc_id
1347 WHERE line_id = l_line_id;
1348
1349 ELSE
1350
1351 UPDATE mtl_txn_request_lines
1352 SET to_subinventory_code = Nvl(l_to_sub_code, l_default_ship_staging_sub),
1353 to_locator_id = Nvl(l_to_loc_id, l_default_ship_staging_loc_id)
1354 WHERE line_id = l_line_id;
1355
1356
1357 END IF;
1358
1359 l_progress := '260';
1360
1361 -- {{ end MTRL loop }}
1362
1363 <<loop_end>>
1364 NULL; -- this is necessary for the goto label
1365 END LOOP;
1366
1367 CLOSE c_mol_opp_and_planned;
1368
1369 l_progress := '270';
1370
1371 IF (l_debug = 1) THEN
1372 mydebug('***End of check_crossdock***');
1373 END IF;
1374
1375
1376 EXCEPTION
1377 WHEN FND_API.G_EXC_ERROR THEN
1378
1379 IF (c_mol_opportunistic%ISOPEN) THEN
1380 CLOSE c_mol_opportunistic;
1381 END IF;
1382
1383 IF (c_mol_opp_and_planned%ISOPEN) THEN
1384 CLOSE c_mol_opp_and_planned;
1385 END IF;
1386
1387 ROLLBACK TO check_crossdock_sp;
1388 x_return_status := fnd_api.g_ret_sts_error;
1389 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1390 p_data => x_msg_data);
1391 IF (l_debug = 1) THEN
1392 mydebug('Exiting check_crossdock - Execution error: ' ||
1393 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
1394 END IF;
1395
1396 IF SQLCODE IS NOT NULL THEN
1397 IF (l_debug = 1) THEN
1398 mydebug(' With SQL error: ' || SQLERRM(SQLCODE));
1399 END IF;
1400
1401 END IF;
1402
1403 -- TODO: check cursor close
1404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1405
1406 IF (c_mol_opportunistic%ISOPEN) THEN
1407 CLOSE c_mol_opportunistic;
1408 END IF;
1409
1410 IF (c_mol_opp_and_planned%ISOPEN) THEN
1411 CLOSE c_mol_opp_and_planned;
1412 END IF;
1413
1414 ROLLBACK TO check_crossdock_sp;
1415 x_return_status := fnd_api.g_ret_sts_unexp_error;
1416 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1417 p_data => x_msg_data);
1418 IF (l_debug = 1) THEN
1419 mydebug('Exiting Opportunistic_Cross_Dock - Unexpected error: ' ||
1420 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
1421 END IF;
1422
1423 IF SQLCODE IS NOT NULL THEN
1424 IF (l_debug = 1) THEN
1425 mydebug(' With SQL error: ' || SQLERRM(SQLCODE));
1426 END IF;
1427
1428 END IF;
1429
1430 WHEN OTHERS THEN
1431 IF (c_mol_opportunistic%ISOPEN) THEN
1432 CLOSE c_mol_opportunistic;
1433 END IF;
1434
1435 IF (c_mol_opp_and_planned%ISOPEN) THEN
1436 CLOSE c_mol_opp_and_planned;
1437 END IF;
1438
1439 ROLLBACK TO check_crossdock_sp;
1440 x_return_status := fnd_api.g_ret_sts_unexp_error;
1441 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1442 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1443 END IF;
1444 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1445 p_data => x_msg_data);
1446 IF (l_debug = 1) THEN
1447 mydebug('Exiting Opportunistic_Cross_Dock - Others exception: ' ||
1448 l_progress ||' '|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'));
1449 END IF;
1450
1451 IF SQLCODE IS NOT NULL THEN
1452 IF (l_debug = 1) THEN
1453 mydebug(' With SQL error: ' || SQLERRM(SQLCODE));
1454 END IF;
1455
1456 END IF;
1457
1458 END crossdock;
1459
1460
1461 PROCEDURE complete_crossdock
1462 ( p_org_id IN NUMBER
1463 ,p_temp_id IN NUMBER
1464 , x_return_status OUT NOCOPY VARCHAR2
1465 , x_msg_count OUT NOCOPY NUMBER
1466 , x_msg_data OUT NOCOPY VARCHAR2
1467 )
1468
1469 IS
1470
1471 /*
1472 ,p_del_id NUMBER
1473 ,p_mo_line_id NUMBER
1474 , p_item_id NUMBER
1475 ,x_return_status OUT VARCHAR2
1476 */
1477 l_cnt_lpn_id NUMBER;
1478 l_msg_cnt NUMBER;
1479 -- l_msg_data VARCHAR2(240);
1480 l_org_id NUMBER;
1481 l_item_id NUMBER;
1482 l_ret NUMBER;
1483 l_temp_id NUMBER;
1484 l_del_id NUMBER;
1485 l_mo_line_id NUMBER;
1486 l_demand_source_type NUMBER;
1487 l_mso_header_id NUMBER; -- The MTL_SALES_ORDERS
1488 --header ID, which should be derived from the OE header ID
1489 -- and used for reservation queries.
1490
1491 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
1492
1493 l_update_rsv_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
1494 l_demand_info wsh_inv_delivery_details_v%ROWTYPE;
1495 l_prim_qty NUMBER;
1496 l_primary_temp_qty NUMBER;
1497 l_prim_uom VARCHAR2(3);
1498 l_sub VARCHAR2(10);
1499 l_loc NUMBER;
1500 l_return_status VARCHAR2(1);
1501 l_api_return_status VARCHAR2(1);
1502 l_org_wide_res_id NUMBER ;
1503 l_qty_succ_reserved NUMBER;
1504 l_msg_data VARCHAR2(2400);
1505 l_msg_count NUMBER;
1506
1507 l_dummy_sn INV_Reservation_Global.Serial_Number_Tbl_Type;
1508
1509 l_source_header_id NUMBER;
1510 l_source_line_id NUMBER;
1511 l_rev varchar2(3);
1512 l_lot VARCHAR2(30);
1513 l_lot_count NUMBER;
1514 l_lot_control_code NUMBER;
1515 l_serial_control_code NUMBER;
1516 l_serial_trx_id NUMBER;
1517 l_transaction_type_id NUMBER;
1518 l_action_flag VARCHAR2(1);
1519 l_serial_temp_id NUMBER;
1520 l_transfer_lpn_id NUMBER;
1521 l_serial_number VARCHAR2(30);
1522 l_transaction_source_type_id NUMBER;
1523 l_txn_supply_source_id NUMBER;
1524 l_query_rsv_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
1525 l_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
1526 l_rsv_tbl_count NUMBER;
1527 l_error_code NUMBER;
1528
1529 l_order_source_id NUMBER;
1530
1531 l_label_status VARCHAR2(2000);
1532
1533 l_lpn_del_detail_id NUMBER;
1534 l_crossdock_type NUMBER;
1535 l_wdd_req_qty NUMBER; --9695544
1536 l_wdd_staged_qty NUMBER := 0; --9695544
1537 l_ser_num_tab ser_num_tab; --9816695
1538 l_new_txn_id NUMBER ; --9816695
1539 l_user_id NUMBER := fnd_global.user_id;
1540 l_InvPCInRecType wsh_integration.invpcinrectype; --9816695
1541
1542 -- Release 12 (K): LPN Synchronization/Convergence
1543 -- Types needed for WSH_WMS_LPN_GRP.Delivery_Detail_Action
1544 l_curr_lpn_id NUMBER;
1545 l_lpn_rec WMS_Data_Type_Definitions_PUB.LPNRecordType;
1546 l_wsh_lpn_id_tbl WSH_Util_Core.id_tab_type;
1547 l_wsh_del_det_id_tbl WSH_Util_Core.id_tab_type;
1548 l_wsh_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
1549 l_wsh_defaults WSH_GLBL_VAR_STRCT_GRP.dd_default_parameters_rec_type;
1550 l_wsh_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
1551
1552 -- Patchset J change
1553 CURSOR msn_serial_csr (l_lpn_id NUMBER) IS
1554 SELECT serial_number
1555 FROM mtl_serial_numbers
1556 WHERE lpn_id = l_lpn_id;
1557
1558 cursor serial_csr IS
1559 SELECT fm_serial_number
1560 FROM mtl_serial_numbers_temp
1561 WHERE transaction_temp_id=l_serial_temp_id ;
1562
1563 CURSOR parent_lpn_cur ( p_innermost_lpn_id NUMBER ) IS
1564 SELECT lpn_id, license_plate_number, parent_lpn_id, organization_id, subinventory_code, locator_id,
1565 tare_weight, tare_weight_uom_code, gross_weight, gross_weight_uom_code,
1566 container_volume, container_volume_uom, content_volume, content_volume_uom_code
1567 FROM wms_license_plate_numbers
1568 START WITH lpn_id = p_innermost_lpn_id
1569 CONNECT BY lpn_id = PRIOR parent_lpn_id;
1570
1571 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1572 BEGIN
1573
1574 l_return_status:= FND_API.G_RET_STS_SUCCESS;
1575
1576 -- patchset J change to set the local variable with the patch level
1577
1578 IF (l_debug = 1) THEN
1579 mydebug('in Complete cdock');
1580 END IF;
1581 l_org_id:=p_org_id;
1582 l_temp_id:=p_temp_id;
1583 l_ret:=0;
1584 -- l_del_id:=p_del_id;
1585 -- l_mo_line_id:=p_mo_line_id;
1586 --l_item_id:=p_item_id;
1587
1588 l_return_status:=fnd_api.g_ret_sts_success;
1589 IF (l_debug = 1) THEN
1590 mydebug('Check if crossdock is necessary..');
1591 END IF;
1592
1593 BEGIN
1594 SELECT
1595 l.line_id, l.backorder_delivery_detail_id,l.inventory_item_id,Nvl(crossdock_type,1)
1596 ,l.transaction_source_type_id,l.txn_source_id
1597 INTO l_mo_line_id,l_del_id,l_item_id,l_crossdock_type,l_transaction_source_type_id,l_txn_supply_source_id
1598 FROM mtl_txn_request_lines l, mtl_material_transactions_temp t, wsh_delivery_details_ob_grp_v wdd
1599 WHERE t.transaction_temp_id=l_temp_id
1600 AND l.backorder_delivery_detail_id = wdd.delivery_detail_id
1601 AND t.move_order_line_id=l.line_id
1602 AND exists (
1603 select 1 from oe_order_lines_all oel
1604 where oel.line_id = wdd.source_line_id
1605 and nvl(oel.project_id,-9999) = nvl(l.project_id,-9999)
1606 and nvl(oel.task_id,-9999) = nvl(l.task_id,-9999)
1607 );
1608
1609 EXCEPTION
1610 WHEN no_data_found THEN
1611
1612 SELECT
1613 l.line_id, l.backorder_delivery_detail_id,l.inventory_item_id,Nvl(crossdock_type,1)
1614 ,l.transaction_source_type_id,l.txn_source_id
1615 INTO l_mo_line_id,l_del_id,l_item_id,l_crossdock_type,l_transaction_source_type_id,l_txn_supply_source_id
1616 FROM mtl_txn_request_lines l, mtl_material_transactions_temp t
1617 WHERE t.transaction_temp_id=l_temp_id
1618 AND t.move_order_line_id=l.line_id;
1619
1620 END;
1621
1622 IF (l_debug = 1) THEN
1623 mydebug('Transaction source type id is : '||l_transaction_source_type_id);
1624 END IF;
1625
1626 IF l_del_id IS NULL THEN
1627 IF (l_debug = 1) THEN
1628 mydebug('No Crossdocking necessary');
1629 END IF;
1630 x_return_status:=l_return_status;
1631 x_msg_data:='No Crossdocking necessary';
1632
1633 ELSE
1634 IF (l_debug = 1) THEN
1635 mydebug('Cross Docked!');
1636 END IF;
1637 IF l_crossdock_type=2 THEN
1638 IF (l_debug = 1) THEN
1639 mydebug('Cross Docked FOR WIP2!');
1640 END IF;
1641 wms_wip_xdock_pvt.wip_complete_crossdock
1642 ( p_org_id =>l_org_id
1643 , p_temp_id =>l_temp_id
1644 , p_wip_id=>l_del_id
1645 , p_inventory_item_id=>l_item_id
1646 , x_return_status=>l_return_status
1647 , x_msg_count =>l_msg_cnt
1648 , x_msg_data =>l_msg_data
1649 );
1650 IF (l_debug = 1) THEN
1651 mydebug('After WIP Complete crossdock API');
1652 END IF;
1653 x_return_status:=l_return_status;
1654 x_msg_data :=l_msg_data;
1655 RETURN;
1656 END if;
1657 IF (l_debug = 1) THEN
1658 mydebug('Cross Docked FOR SO!');
1659 mydebug('Get relevant info');
1660 END IF;
1661
1662 --Get info from MMTT
1663 SELECT t.primary_quantity,t.inventory_item_id,t.subinventory_code,
1664 t.locator_id,t.revision,t.transaction_type_id,t.transfer_lpn_id,
1665 t.content_lpn_id,i.primary_uom_code,i.lot_control_code,
1666 i.serial_number_control_code
1667 INTO l_prim_qty ,l_item_id,l_sub,
1668 l_loc,l_rev,l_transaction_type_id,
1669 l_transfer_lpn_id,l_cnt_lpn_id,l_prim_uom
1670 ,l_lot_control_code, l_serial_control_code
1671 FROM mtl_material_transactions_temp t,
1672 mtl_system_items i
1673 WHERE t.transaction_temp_id=l_temp_id
1674 AND t.organization_id=l_org_id
1675 AND t.organization_id =i.organization_id
1676 AND t.inventory_item_id=i.inventory_item_id;
1677 -- AND wlpn.lpn_id = t.lpn_id; BUG 4666710
1678
1679 IF l_transfer_lpn_id IS NULL THEN
1680 l_transfer_lpn_id:=l_cnt_lpn_id;
1681 END IF;
1682 IF (l_debug = 1) THEN
1683 mydebug ('lpn id'||l_transfer_lpn_id);
1684 END IF;
1685 IF l_lot_control_code>1 THEN
1686
1687 -- Get lot info. Will always be only one lot
1688 SELECT lot_number,serial_transaction_temp_id INTO l_lot,
1689 l_serial_temp_id
1690 FROM mtl_transaction_lots_temp
1691 WHERE (transaction_temp_id in (select TRANSACTION_TEMP_ID from mtl_material_transactions_temp where PARENT_LINE_ID=l_temp_id)
1692 or transaction_temp_id = l_temp_id ) -- 11701595
1693 and rownum=1; -- Modified for Bug#8489892
1694
1695 mydebug('l_serial_temp_id is '||l_serial_temp_id);
1696 ELSE
1697 l_lot:=NULL;
1698
1699 END IF;
1700
1701 IF (l_debug = 1) THEN
1702 mydebug ('l_lot_control_code = '||l_lot_control_code);
1703 mydebug ('l_lot = '||l_lot);
1704 END IF;
1705
1706
1707 -- {{
1708 -- Remove reservation related calls from complete_crossdock if source is receiving
1709 -- but creation/transfer of reservation should still happen correctly.
1710 -- }}
1711
1712 --IF(l_lpn_context <> 3 )THEN
1713 IF (l_transaction_source_type_id = inv_reservation_global.g_source_type_wip) THEN
1714 IF (l_debug = 1) THEN
1715 mydebug('Create Rsv');
1716 END IF;
1717
1718 -- Create Reservation
1719 SELECT * INTO l_demand_info
1720 from wsh_inv_delivery_details_v
1721 WHERE delivery_detail_id=l_del_id;
1722
1723
1724 -- Compute the MTL_SALES_ORDERS header ID to use when dealing with reservations.
1725 l_mso_header_id := INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER(l_demand_info.oe_header_id);
1726
1727 IF (l_debug = 1) THEN
1728 mydebug('HdrID:'||l_mso_header_id);
1729 END IF;
1730 IF l_mso_header_id IS NULL THEN
1731 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_GET_MSO_HEADER');
1732 FND_MSG_PUB.Add;
1733 RAISE fnd_api.g_exc_unexpected_error;
1734 END IF;
1735 IF (l_debug = 1) THEN
1736 mydebug('Get Dem src');
1737 END IF;
1738 -- get damand source type
1739 select Nvl(order_source_id,1)
1740 into l_order_source_id
1741 from oe_order_lines_all
1742 where line_id = l_demand_info.oe_line_id;
1743
1744 IF (l_debug = 1) THEN
1745 mydebug('dem src'||l_demand_source_type);
1746 mydebug('Qty'||l_prim_qty);
1747 mydebug('UOM'||l_prim_uom);
1748 END IF;
1749
1750 IF (l_debug = 1) THEN
1751 mydebug ('lot control code '||l_lot_control_code);
1752 mydebug ('l_lot '||l_lot);
1753 END IF;
1754
1755 -- See if reservation already exists. If a reservation already exists
1756 -- for a given supply source id then transfer reservation else create
1757 -- reservation
1758 l_query_rsv_rec.organization_id := l_org_id;
1759 l_query_rsv_rec.inventory_item_id := l_item_id;
1760 IF (l_transaction_source_type_id = inv_reservation_global.g_source_type_wip) THEN
1761 -- LPN coming from WIP
1762 l_query_rsv_rec.supply_source_header_id := l_txn_supply_source_id;
1763 l_query_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_wip;
1764 ELSE -- LPN coming from Receiving
1765
1766 -- Bug# 3281512 - Performance Fixes
1767 -- Take the decode out of the query and just use an IF condition
1768 -- to decide if we should go against transaction_id or interface_transaction_id
1769 -- in the rcv_transactions_table depending on if we are on
1770 -- patchset J or higher.
1771
1772 BEGIN
1773 SELECT Nvl(po_header_id, -1)
1774 INTO l_query_rsv_rec.supply_source_header_id
1775 FROM rcv_transactions
1776 -- patchset j changes
1777 WHERE transaction_id = l_txn_supply_source_id;
1778 EXCEPTION
1779 WHEN OTHERS THEN
1780 l_query_rsv_rec.supply_source_header_id := -1;
1781 END;
1782
1783 l_query_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_po;
1784 END IF;
1785 l_query_rsv_rec.demand_source_header_id := l_mso_header_id;
1786 l_query_rsv_rec.demand_source_line_id := l_demand_info.oe_line_id;
1787
1788 -- Call query reservation
1789 inv_reservation_pub.query_reservation
1790 (p_api_version_number => 1.0,
1791 p_init_msg_lst => fnd_api.g_false,
1792 x_return_status => l_return_status,
1793 x_msg_count => l_msg_count,
1794 x_msg_data => l_msg_data,
1795 p_query_input => l_query_rsv_rec,
1796 p_lock_records => fnd_api.g_true,
1797 p_sort_by_req_date => inv_reservation_global.g_query_req_date_asc,
1798 x_mtl_reservation_tbl => l_reservation_tbl,
1799 x_mtl_reservation_tbl_count => l_rsv_tbl_count,
1800 x_error_code => l_error_code);
1801
1802 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1803 x_msg_count := l_msg_count;
1804 x_msg_data := l_msg_data;
1805 x_return_status := l_return_status;
1806 IF (l_debug = 1) THEN
1807 mydebug('Error in Query Reservation');
1808 END IF;
1809 FND_MESSAGE.SET_NAME('INV','INV_QRY_RSV_FAILED');
1810 FND_MSG_PUB.Add;
1811 RAISE fnd_api.g_exc_unexpected_error;
1812 END IF;
1813
1814
1815 IF (l_rsv_tbl_count > 0) THEN
1816 l_primary_temp_qty := l_prim_qty;
1817
1818 FOR i IN 1 .. l_rsv_tbl_count LOOP
1819 l_update_rsv_rec := l_reservation_tbl(i);
1820 l_update_rsv_rec.demand_source_delivery := NULL;
1821 l_update_rsv_rec.primary_uom_code := l_prim_uom;
1822 l_update_rsv_rec.primary_uom_id := NULL;
1823 l_update_rsv_rec.reservation_uom_code := NULL;
1824 l_update_rsv_rec.reservation_uom_id := NULL;
1825 l_update_rsv_rec.reservation_quantity := NULL;
1826
1827 IF (l_primary_temp_qty >
1828 l_reservation_tbl(i).primary_reservation_quantity) THEN
1829 l_update_rsv_rec.primary_reservation_quantity :=
1830 l_reservation_tbl(i).primary_reservation_quantity;
1831 l_primary_temp_qty := l_primary_temp_qty -
1832 l_reservation_tbl(i).primary_reservation_quantity;
1833 ELSE
1834 l_update_rsv_rec.primary_reservation_quantity :=
1835 l_primary_temp_qty;
1836 l_primary_temp_qty := 0;
1837 END IF;
1838
1839 l_update_rsv_rec.supply_source_type_id := INV_Reservation_GLOBAL.g_source_type_inv;
1840 l_update_rsv_rec.supply_source_header_id := NULL;
1841 l_update_rsv_rec.supply_source_line_id := NULL;
1842 l_update_rsv_rec.supply_source_name := NULL;
1843 l_update_rsv_rec.supply_source_line_detail := NULL;
1844
1845 l_update_rsv_rec.subinventory_code := l_sub;
1846 l_update_rsv_rec.subinventory_id := NULL;
1847 l_update_rsv_rec.locator_id := l_loc;
1848
1849 IF l_lot_control_code>1 THEN
1850 l_update_rsv_rec.lot_number := l_lot; -- Bug 7712653
1851 END IF;
1852 -- Bug# 2771182
1853 -- Pass the LPN ID into the reservation record when transferring reservation
1854 l_update_rsv_rec.lpn_id := l_transfer_lpn_id;
1855
1856 inv_reservation_pub.transfer_reservation
1857 (p_api_version_number => 1.0,
1858 p_init_msg_lst => fnd_api.g_false,
1859 x_return_status => l_return_status,
1860 x_msg_count => l_msg_count,
1861 x_msg_data => l_msg_data,
1862 p_is_transfer_supply => fnd_api.g_true,
1863 p_original_rsv_rec => l_reservation_tbl(i),
1864 p_to_rsv_rec => l_update_rsv_rec,
1865 p_original_serial_number => l_dummy_sn,
1866 p_to_serial_number => l_dummy_sn,
1867 p_validation_flag => fnd_api.g_true,
1868 x_to_reservation_id => l_org_wide_res_id);
1869
1870 -- Return an error if the transfer reservation call failed
1871 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1872 IF (l_debug = 1) THEN
1873 mydebug('error in transfer reservation');
1874 END IF;
1875 FND_MESSAGE.SET_NAME('INV','INV_TRANSFER_RSV_FAILED');
1876 FND_MSG_PUB.Add;
1877 RAISE fnd_api.g_exc_unexpected_error;
1878 END IF;
1879
1880 IF (l_primary_temp_qty <= 0) THEN
1881 exit;
1882 END IF;
1883 END LOOP;
1884
1885 ELSE
1886 -- l_demand_source_type:=2;
1887 l_update_rsv_rec.reservation_id := NULL; -- cannot know
1888 l_update_rsv_rec.requirement_date := Sysdate;
1889 l_update_rsv_rec.organization_id := l_org_id;
1890 l_update_rsv_rec.inventory_item_id := l_item_id;
1891
1892 If l_order_source_id = 10 then
1893 l_update_rsv_rec.demand_source_type_id :=
1894 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INTERNAL_ORD; -- Internal Order
1895 ELSE
1896 l_update_rsv_rec.demand_source_type_id :=
1897 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE; -- Order Entry
1898 end if;
1899 -- bug 2808892
1900 --l_update_rsv_rec.demand_source_type_id := inv_globals.G_SourceType_SalesOrder;
1901 --INV_Reservation_Global.g_source_type_oe; -- order entry
1902 l_update_rsv_rec.demand_source_name := NULL;
1903 l_update_rsv_rec.demand_source_header_id := l_mso_header_id;
1904 l_update_rsv_rec.demand_source_line_id := l_demand_info.oe_line_id;
1905 l_update_rsv_rec.demand_source_delivery := NULL;
1906 l_update_rsv_rec.primary_uom_code := l_prim_uom;
1907 l_update_rsv_rec.primary_uom_id := NULL;
1908 l_update_rsv_rec.reservation_uom_code := NULL;
1909 l_update_rsv_rec.reservation_uom_id := NULL;
1910 l_update_rsv_rec.reservation_quantity := NULL;
1911 l_update_rsv_rec.primary_reservation_quantity := l_prim_qty;
1912 l_update_rsv_rec.autodetail_group_id := NULL;
1913 l_update_rsv_rec.external_source_code := NULL;
1914 l_update_rsv_rec.external_source_line_id := NULL;
1915 l_update_rsv_rec.supply_source_type_id :=
1916 INV_Reservation_GLOBAL.g_source_type_inv;
1917 l_update_rsv_rec.supply_source_header_id := NULL;
1918 l_update_rsv_rec.supply_source_line_id := NULL;
1919 l_update_rsv_rec.supply_source_name := NULL;
1920 l_update_rsv_rec.supply_source_line_detail := NULL;
1921
1922 l_update_rsv_rec.revision := l_rev;
1923 l_update_rsv_rec.subinventory_code := l_sub;
1924 l_update_rsv_rec.subinventory_id := NULL;
1925 l_update_rsv_rec.locator_id := l_loc;
1926 l_update_rsv_rec.lot_number := l_lot;
1927 l_update_rsv_rec.lot_number_id := NULL;
1928 l_update_rsv_rec.pick_slip_number := NULL;
1929 -- Bug# 2771182
1930 -- Pass the LPN ID into the reservation record when creating reservation
1931 l_update_rsv_rec.lpn_id := l_transfer_lpn_id;
1932 l_update_rsv_rec.attribute_category := NULL;
1933 l_update_rsv_rec.attribute1 := NULL;
1934 l_update_rsv_rec.attribute2 := NULL;
1935 l_update_rsv_rec.attribute3 := NULL;
1936 l_update_rsv_rec.attribute4 := NULL;
1937 l_update_rsv_rec.attribute5 := NULL;
1938 l_update_rsv_rec.attribute6 := NULL;
1939 l_update_rsv_rec.attribute7 := NULL;
1940 l_update_rsv_rec.attribute8 := NULL;
1941 l_update_rsv_rec.attribute9 := NULL;
1942 l_update_rsv_rec.attribute10 := NULL;
1943 l_update_rsv_rec.attribute11 := NULL;
1944 l_update_rsv_rec.attribute12 := NULL;
1945 l_update_rsv_rec.attribute13 := NULL;
1946 l_update_rsv_rec.attribute14 := NULL;
1947 l_update_rsv_rec.attribute15 := NULL;
1948 l_update_rsv_rec.ship_ready_flag := NULL;
1949 l_update_rsv_rec.detailed_quantity := 0;
1950
1951 IF (l_debug = 1) THEN
1952 mydebug('create new reservation');
1953 END IF;
1954 inv_quantity_tree_pvt.clear_quantity_cache ;
1955 INV_Reservation_PUB.Create_Reservation
1956 (
1957 p_api_version_number => 1.0
1958 , p_init_msg_lst => fnd_api.g_false
1959 , x_return_status => l_api_return_status
1960 , x_msg_count => l_msg_cnt
1961 , x_msg_data => l_msg_data
1962 , p_rsv_rec => l_update_rsv_rec
1963 , p_serial_number => l_dummy_sn
1964 , x_serial_number => l_dummy_sn
1965 , p_partial_reservation_flag => fnd_api.g_true
1966 , p_force_reservation_flag => fnd_api.g_false
1967 , p_validation_flag => fnd_api.g_true
1968 , x_quantity_reserved => l_qty_succ_reserved
1969 , x_reservation_id => l_org_wide_res_id
1970 );
1971
1972
1973
1974 fnd_msg_pub.count_and_get
1975 ( p_count => l_msg_cnt
1976 , p_data => l_msg_data
1977 );
1978
1979 IF (l_msg_cnt = 0) THEN
1980 IF (l_debug = 1) THEN
1981 mydebug('Successful');
1982 END IF;
1983 ELSIF (l_msg_cnt = 1) THEN
1984 IF (l_debug = 1) THEN
1985 mydebug('Not Successful');
1986 mydebug(replace(l_msg_data,chr(0),' '));
1987 END IF;
1988 ELSE
1989 IF (l_debug = 1) THEN
1990 mydebug('Not Successful2');
1991 END IF;
1992 For I in 1..l_msg_cnt LOOP
1993 l_msg_data := fnd_msg_pub.get(I,'F');
1994 IF (l_debug = 1) THEN
1995 mydebug(replace(l_msg_data,chr(0),' '));
1996 END IF;
1997 END LOOP;
1998 END IF;
1999
2000
2001 -- Return an error if the create reservation call failed
2002 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2003 IF (l_debug = 1) THEN
2004 mydebug('error in create reservation');
2005 END IF;
2006 -- ROLLBACK TO Process_Line_PVT;
2007 FND_MESSAGE.SET_NAME('WMS','WMS_TD_CR_RSV_ERROR');
2008 FND_MSG_PUB.Add;
2009 RAISE fnd_api.g_exc_unexpected_error;
2010 END IF;
2011 END IF;
2012
2013
2014 -- Fix for Bug 2344419
2015 -- Changes made to the shipping process as part of the change
2016 -- management project require that the reservation be marked as
2017 -- staged. Am calling an API to update the reservation thusly..
2018
2019 IF (l_debug = 1) THEN
2020 mydebug('Upd Reservation as having been staged');
2021 mydebug('Rsv Id:'||l_org_wide_res_id);
2022 mydebug('Calling API to update rsv as staged...');
2023 END IF;
2024 inv_staged_reservation_util.update_staged_flag
2025 ( x_return_status =>l_return_status,
2026 x_msg_count =>l_msg_cnt,
2027 x_msg_data =>l_msg_data,
2028 p_reservation_id =>l_org_wide_res_id,
2029 p_staged_flag =>'Y');
2030
2031 END IF; -- l_lpn_context
2032
2033
2034 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2035 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_RSV_ERROR' );
2036 FND_MSG_PUB.ADD;
2037 RAISE FND_API.g_exc_unexpected_error;
2038
2039 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2040 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_RSV_ERROR');
2041 FND_MSG_PUB.ADD;
2042 RAISE FND_API.G_EXC_ERROR;
2043 END IF;
2044
2045 IF (l_debug = 1) THEN
2046 mydebug('After calling API to update rsv as staged');
2047 END IF;
2048
2049 IF (l_debug = 1) THEN
2050 mydebug('Upd shipping');
2051 END IF;
2052
2053 -- UPDATE SHIPPING
2054 -- TEST should modify to get from demand info
2055 select oe_header_id, oe_line_id
2056 into l_source_header_id, l_source_line_id
2057 from wsh_inv_delivery_details_v
2058 WHERE delivery_detail_id=l_del_id;
2059
2060 /*9695544.added below query*/
2061 SELECT requested_quantity INTO l_wdd_req_qty
2062 FROM Wsh_delivery_details
2063 WHERE delivery_detail_id=l_del_id;
2064
2065 l_wdd_staged_qty := 0 ; --9695544
2066
2067 IF ((l_serial_control_code>1 AND l_serial_control_code<>6)
2068 OR l_lot_control_code > 1) THEN
2069 l_action_flag:='M';
2070 ELSE
2071 l_action_flag:='U';
2072 END IF;
2073 -- Call update shipping
2074 l_shipping_attr(1).source_header_id := l_source_header_id;
2075 l_shipping_attr(1).source_line_id := l_source_line_id;
2076 l_shipping_attr(1).ship_from_org_id := l_org_id;
2077 l_shipping_attr(1).subinventory := l_sub;
2078 l_shipping_attr(1).revision := l_rev;
2079 l_shipping_attr(1).locator_id := l_loc;
2080 l_shipping_attr(1).released_status := 'Y';
2081 l_shipping_attr(1).delivery_detail_id := l_del_id;
2082 l_shipping_attr(1).transfer_lpn_id := l_transfer_lpn_id;
2083 l_shipping_attr(1).action_flag := l_action_flag;
2084 l_shipping_attr(1).lot_number := l_lot;
2085 l_shipping_attr(1).order_quantity_uom:=l_prim_uom;
2086
2087 l_InvPCInRecType.source_code := 'INV';
2088 l_InvPCInRecType.api_version_number := 1.0;
2089
2090
2091
2092 if( l_lot_control_code > 1 and l_serial_control_code not in ( 1, 6) ) then
2093
2094 -- get serial info for lot and ser controlled item
2095 IF (l_debug = 1) THEN
2096 mydebug('Lot and Serial Controlled');
2097 END IF;
2098
2099 IF l_transfer_lpn_id = l_cnt_lpn_id THEN
2100 IF (l_debug = 1) THEN
2101 mydebug('Entire LPN being crossdocked.');
2102 END IF;
2103 /*9816695, we will insert msnt , update MSN and update shipping with these serials . */
2104 OPEN msn_serial_csr (l_transfer_lpn_id);
2105 FETCH msn_serial_csr BULK COLLECT INTO l_ser_num_tab;
2106 CLOSE msn_serial_csr;
2107 IF (l_debug = 1) THEN
2108 mydebug('Fetched Serials,count:'||l_ser_num_tab.COUNT);
2109 END IF;
2110
2111 SELECT mtl_material_transactions_s.NEXTVAL INTO l_new_txn_id FROM DUAL;
2112
2113 IF (l_debug = 1) THEN
2114 mydebug('new txn id :'||l_new_txn_id);
2115 END IF;
2116
2117 insert_xdock_msnt
2118 (
2119 p_org_id => p_org_id,
2120 p_item_id => l_item_id,
2121 p_transaction_temp_id => l_new_txn_id,
2122 p_serial_number_table => l_ser_num_tab,
2123 x_return_status => l_return_status,
2124 x_msg_count => x_msg_count,
2125 x_msg_data => x_msg_data
2126 );
2127 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2128 RAISE FND_API.g_exc_unexpected_error;
2129 END IF;
2130
2131 l_wdd_staged_qty := l_wdd_staged_qty + l_ser_num_tab.COUNT; --9695544
2132 l_shipping_attr(1).lot_number := l_lot;
2133 l_shipping_attr(1).ordered_quantity := l_ser_num_tab.COUNT;
2134 l_shipping_attr(1).picked_quantity := l_ser_num_tab.COUNT;
2135 l_shipping_attr(1).pending_quantity := l_wdd_req_qty - l_wdd_staged_qty; --9695544
2136 l_return_status := '';
2137
2138 l_InvPCInRecType.transaction_temp_id := l_new_txn_id;
2139
2140 WSH_INTEGRATION.Set_Inv_PC_Attributes
2141 ( p_in_attributes => l_InvPCInRecType,
2142 x_return_status => l_return_status,
2143 x_msg_count => x_msg_count,
2144 x_msg_data => x_msg_data );
2145 IF (l_debug = 1) THEN
2146 mydebug('After Set_Inv_PC_Attributes Ret status:'||l_return_status);
2147 END IF;
2148 WSH_INTERFACE.Update_Shipping_Attributes
2149 (p_source_code => 'INV',
2150 p_changed_attributes => l_shipping_attr,
2151 x_return_status => l_return_status
2152 );
2153
2154 IF (l_debug = 1) THEN mydebug('after update shipping attributes'); END IF;
2155
2156 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2157 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR' );
2158 FND_MSG_PUB.ADD;
2159 RAISE FND_API.g_exc_unexpected_error;
2160
2161 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2162 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR');
2163 FND_MSG_PUB.ADD;
2164 RAISE FND_API.G_EXC_ERROR;
2165 END IF;
2166
2167 ELSE
2168
2169 IF (l_debug = 1) THEN
2170 mydebug('Entire LPN not being crossdocked');
2171 END IF;
2172 OPEN serial_csr;
2173 FETCH serial_csr BULK COLLECT INTO l_ser_num_tab;
2174 CLOSE serial_csr;
2175
2176 IF (l_debug = 1) THEN
2177 mydebug('Fetched Serials,count:'||l_ser_num_tab.COUNT);
2178 END IF;
2179
2180 SELECT mtl_material_transactions_s.NEXTVAL INTO l_new_txn_id FROM DUAL;
2181 IF (l_debug = 1) THEN
2182 mydebug('new txn id :'||l_new_txn_id);
2183 END IF;
2184
2185 IF (l_debug = 1) THEN
2186 mydebug('new txn id :'||l_new_txn_id);
2187 END IF;
2188
2189 insert_xdock_msnt
2190 (
2191 p_org_id => p_org_id,
2192 p_item_id => l_item_id,
2193 p_transaction_temp_id => l_new_txn_id,
2194 p_serial_number_table => l_ser_num_tab,
2195 x_return_status => l_return_status,
2196 x_msg_count => x_msg_count,
2197 x_msg_data => x_msg_data
2198 );
2199 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2200 RAISE FND_API.g_exc_unexpected_error;
2201 END IF;
2202
2203 l_wdd_staged_qty := l_wdd_staged_qty + l_ser_num_tab.COUNT; --9695544
2204 l_shipping_attr(1).lot_number := l_lot;
2205 l_shipping_attr(1).ordered_quantity := l_ser_num_tab.COUNT;
2206 l_shipping_attr(1).picked_quantity := l_ser_num_tab.COUNT;
2207 l_shipping_attr(1).pending_quantity := l_wdd_req_qty - l_wdd_staged_qty; --9695544
2208 l_return_status := '';
2209
2210 l_InvPCInRecType.transaction_temp_id := l_new_txn_id; -- l_serial_temp_id; -- bug13938452
2211 WSH_INTEGRATION.Set_Inv_PC_Attributes
2212 ( p_in_attributes => l_InvPCInRecType,
2213 x_return_status => l_return_status,
2214 x_msg_count => x_msg_count,
2215 x_msg_data => x_msg_data );
2216 IF (l_debug = 1) THEN
2217 mydebug('After Set_Inv_PC_Attributes Ret status:'||l_return_status);
2218 END IF;
2219
2220 WSH_INTERFACE.Update_Shipping_Attributes
2221 (p_source_code => 'INV',
2222 p_changed_attributes => l_shipping_attr,
2223 x_return_status => l_return_status
2224 );
2225
2226
2227 IF (l_debug = 1) THEN
2228 mydebug('after update shipping attributes');
2229 END IF;
2230
2231
2232 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2233 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR' );
2234 FND_MSG_PUB.ADD;
2235 RAISE FND_API.g_exc_unexpected_error;
2236
2237 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2238 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR');
2239 FND_MSG_PUB.ADD;
2240 RAISE FND_API.G_EXC_ERROR;
2241 END IF;
2242
2243 END IF;
2244
2245 ELSIF ( l_lot_control_code = 1 and l_serial_control_code not in (1, 6)
2246 ) THEN
2247 -- get serial info for ser controlled item
2248 IF (l_debug = 1) THEN
2249 mydebug('Serial Controlled only');
2250 END IF;
2251 l_serial_temp_id:=l_temp_id;
2252
2253 IF l_transfer_lpn_id = l_cnt_lpn_id THEN
2254 IF (l_debug = 1) THEN
2255 mydebug('Entire LPN being crossdocked. MSNT will not be present. ');
2256 END IF;
2257
2258 /*9816695, we will insert msnt , update MSN and update shipping with these serials. */
2259 OPEN msn_serial_csr (l_transfer_lpn_id);
2260 FETCH msn_serial_csr BULK COLLECT INTO l_ser_num_tab;
2261 CLOSE msn_serial_csr;
2262 IF (l_debug = 1) THEN
2263 mydebug('Fetched Serials,count:'||l_ser_num_tab.COUNT);
2264 END IF;
2265
2266 SELECT mtl_material_transactions_s.NEXTVAL INTO l_new_txn_id FROM DUAL;
2267 IF (l_debug = 1) THEN
2268 mydebug('new txn id :'||l_new_txn_id);
2269 END IF;
2270
2271 insert_xdock_msnt
2272 (
2273 p_org_id => p_org_id,
2274 p_item_id => l_item_id,
2275 p_transaction_temp_id => l_new_txn_id,
2276 p_serial_number_table => l_ser_num_tab,
2277 x_return_status => l_return_status,
2278 x_msg_count => x_msg_count,
2279 x_msg_data => x_msg_data
2280 );
2281
2282 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2283 RAISE FND_API.g_exc_unexpected_error;
2284 END IF;
2285
2286 l_wdd_staged_qty := l_wdd_staged_qty + l_ser_num_tab.COUNT; --9695544
2287 l_shipping_attr(1).ordered_quantity := l_ser_num_tab.COUNT;
2288 l_shipping_attr(1).picked_quantity := l_ser_num_tab.COUNT;
2289 l_shipping_attr(1).pending_quantity := l_wdd_req_qty - l_wdd_staged_qty; --9695544
2290 l_return_status := '';
2291
2292 l_InvPCInRecType.transaction_temp_id := l_new_txn_id;
2293 WSH_INTEGRATION.Set_Inv_PC_Attributes
2294 ( p_in_attributes => l_InvPCInRecType,
2295 x_return_status => l_return_status,
2296 x_msg_count => x_msg_count,
2297 x_msg_data => x_msg_data );
2298 IF (l_debug = 1) THEN
2299 mydebug('After Set_Inv_PC_Attributes Ret status:'||l_return_status);
2300 END IF;
2301
2302
2303 WSH_INTERFACE.Update_Shipping_Attributes
2304 (p_source_code => 'INV',
2305 p_changed_attributes => l_shipping_attr,
2306 x_return_status => l_return_status
2307 );
2308
2309
2310 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2311 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR' );
2312 FND_MSG_PUB.ADD;
2313 RAISE FND_API.g_exc_unexpected_error;
2314
2315 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2316 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR');
2317 FND_MSG_PUB.ADD;
2318 RAISE FND_API.G_EXC_ERROR;
2319 END IF;
2320
2321
2322 IF (l_debug = 1) THEN
2323 mydebug('after update shipping attributes');
2324 END IF;
2325
2326
2327 ELSE
2328 IF (l_debug = 1) THEN
2329 mydebug('Entire LPN not being crossdocked');
2330 END IF;
2331 OPEN serial_csr;
2332 FETCH serial_csr BULK COLLECT INTO l_ser_num_tab;
2333 CLOSE serial_csr;
2334
2335
2336 IF (l_debug = 1) THEN
2337 mydebug('Fetched Serials,count:'||l_ser_num_tab.COUNT);
2338 END IF;
2339
2340 SELECT mtl_material_transactions_s.NEXTVAL INTO l_new_txn_id FROM DUAL;
2341 IF (l_debug = 1) THEN
2342 mydebug('new txn id :'||l_new_txn_id);
2343 END IF;
2344
2345 insert_xdock_msnt
2346 (
2347 p_org_id => p_org_id,
2348 p_item_id => l_item_id,
2349 p_transaction_temp_id => l_new_txn_id,
2350 p_serial_number_table => l_ser_num_tab,
2351 x_return_status => l_return_status,
2352 x_msg_count => x_msg_count,
2353 x_msg_data => x_msg_data
2354 );
2355 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2356 RAISE FND_API.g_exc_unexpected_error;
2357 END IF;
2358
2359 l_wdd_staged_qty := l_wdd_staged_qty + l_ser_num_tab.COUNT; --9695544
2360 l_shipping_attr(1).ordered_quantity := l_ser_num_tab.COUNT;
2361 l_shipping_attr(1).picked_quantity := l_ser_num_tab.COUNT;
2362 l_shipping_attr(1).pending_quantity := l_wdd_req_qty - l_wdd_staged_qty; --9695544
2363 l_return_status := '';
2364
2365 l_InvPCInRecType.transaction_temp_id := l_new_txn_id;
2366 WSH_INTEGRATION.Set_Inv_PC_Attributes
2367 ( p_in_attributes => l_InvPCInRecType,
2368 x_return_status => l_return_status,
2369 x_msg_count => x_msg_count,
2370 x_msg_data => x_msg_data );
2371
2372 IF (l_debug = 1) THEN
2373 mydebug('After Set_Inv_PC_Attributes Ret status:'||l_return_status);
2374 END IF;
2375
2376 WSH_INTERFACE.Update_Shipping_Attributes
2377 (p_source_code => 'INV',
2378 p_changed_attributes => l_shipping_attr,
2379 x_return_status => l_return_status
2380 );
2381
2382
2383 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2384 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR' );
2385 FND_MSG_PUB.ADD;
2386 RAISE FND_API.g_exc_unexpected_error;
2387
2388 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2389 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR');
2390 FND_MSG_PUB.ADD;
2391 RAISE FND_API.G_EXC_ERROR;
2392 END IF;
2393
2394
2395 IF (l_debug = 1) THEN
2396 mydebug('after update shipping attributes');
2397 END IF;
2398
2399 END IF;
2400 /*9816695-End of changes */
2401 ELSE
2402 IF (l_debug = 1) THEN
2403 mydebug('No Ser cnt');
2404 END IF;
2405 -- no serial control
2406 IF (l_debug = 1) THEN
2407 mydebug('no serial control');
2408 END IF;
2409 l_shipping_attr(1).ordered_quantity := l_prim_qty;
2410 l_shipping_attr(1).picked_quantity := l_prim_qty; -- added for bug 3872182
2411
2412 IF (l_debug = 1) THEN
2413 mydebug('release status = ' || l_shipping_attr(1).released_status);
2414 mydebug('delivery_detail_id ' || l_shipping_attr(1).delivery_detail_id);
2415 mydebug('action flag is ' || l_shipping_attr(1).action_flag);
2416 mydebug('about to call update shipping attributes');
2417 END IF;
2418
2419
2420
2421
2422
2423 WSH_INTERFACE.Update_Shipping_Attributes
2424 (p_source_code => 'INV',
2425 p_changed_attributes => l_shipping_attr,
2426 x_return_status => l_return_status
2427 );
2428
2429
2430 fnd_msg_pub.count_and_get
2431 ( p_count => l_msg_cnt
2432 , p_data => l_msg_data
2433 );
2434
2435 IF (l_msg_cnt = 0) THEN
2436 IF (l_debug = 1) THEN
2437 mydebug('Successful');
2438 END IF;
2439 ELSIF (l_msg_cnt = 1) THEN
2440 IF (l_debug = 1) THEN
2441 mydebug('Not Successful');
2442 mydebug(replace(l_msg_data,chr(0),' '));
2443 END IF;
2444 ELSE
2445 IF (l_debug = 1) THEN
2446 mydebug('Not Successful2');
2447 END IF;
2448 For I in 1..l_msg_cnt LOOP
2449 l_msg_data := fnd_msg_pub.get(I,'F');
2450 IF (l_debug = 1) THEN
2451 mydebug(replace(l_msg_data,chr(0),' '));
2452 END IF;
2453 END LOOP;
2454 END IF;
2455
2456 IF (l_debug = 1) THEN
2457 mydebug('return status'|| l_return_status);
2458 END IF;
2459
2460 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2461 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR' );
2462 FND_MSG_PUB.ADD;
2463 RAISE FND_API.g_exc_unexpected_error;
2464
2465 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2466 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR');
2467 FND_MSG_PUB.ADD;
2468 RAISE FND_API.G_EXC_ERROR;
2469 END IF;
2470
2471 IF (l_debug = 1) THEN
2472 mydebug('after update shipping attributes');
2473 END IF;
2474
2475 END IF;
2476
2477
2478 -- Have to get delivery detail id
2479 IF (l_debug = 1) THEN
2480 mydebug('Getting del detail id...');
2481 END IF;
2482 begin
2483 SELECT delivery_detail_id INTO l_lpn_del_detail_id
2484 FROM wsh_delivery_details_ob_grp_v
2485 WHERE lpn_id=l_transfer_lpn_id
2486 AND ROWNUM=1;
2487 EXCEPTION
2488 WHEN NO_DATA_FOUND THEN
2489 FND_MESSAGE.SET_NAME('WMS', 'WMS_TD_DEL_LPN_ERROR');
2490 FND_MSG_PUB.ADD;
2491 RAISE FND_API.G_EXC_ERROR;
2492 END;
2493
2494 IF (l_debug = 1) THEN
2495 mydebug('Update LPN context to picked');
2496 END IF;
2497
2498 -- LPN Sync Project, after creating the inner LPN need
2499 -- to create the outer LPNs an well
2500 l_wsh_action_prms.caller := 'WMS';
2501 l_wsh_action_prms.action_code := 'PACK';
2502 l_curr_lpn_id := l_transfer_lpn_id;
2503 l_ret := 0;
2504
2505 FOR parent_lpn_rec IN parent_lpn_cur( l_transfer_lpn_id ) LOOP
2506 IF ( l_debug = 1 ) THEN
2507 mydebug('Got parent LPN record lpnid='||parent_lpn_rec.lpn_id||' plpnid='||parent_lpn_rec.parent_lpn_id||' curlpn='||l_curr_lpn_id||' lret='||l_ret);
2508 END IF;
2509
2510 IF ( parent_lpn_rec.lpn_id = l_transfer_lpn_id ) THEN
2511 -- If this is the innermost LPN create the LPN, if there is a parent
2512 -- LPN it will be created in the next cursor pass through the packing
2513 WMS_Container_PVT.Modify_LPN_Wrapper (
2514 p_api_version => '1.0'
2515 , x_return_status => l_return_status
2516 , x_msg_count => l_msg_cnt
2517 , x_msg_data => l_msg_data
2518 , p_caller => 'WMS_COMPLETE_CROSSDOCK'
2519 , p_lpn_id => l_transfer_lpn_id
2520 , p_lpn_context => wms_globals.lpn_context_picked );
2521
2522 -- store current LPNs for use in next pass. This will be used to set l_curr_lpn_id
2523 l_lpn_rec.lpn_id := parent_lpn_rec.lpn_id;
2524
2525 ELSE
2526 -- Check to see if the LPN already exist in WDD will be used to determine if
2527 -- we need to continue creating heirarchy in the next loop
2528 -- should only be checked if there is another loop (lpn has a parent)
2529 IF ( parent_lpn_rec.parent_lpn_id IS NOT NULL ) THEN
2530 BEGIN
2531 SELECT 1 INTO l_ret
2532 FROM wsh_delivery_details
2533 WHERE lpn_id = parent_lpn_rec.lpn_id;
2534 EXCEPTION
2535 WHEN NO_DATA_FOUND THEN
2536 IF (l_debug = 1) THEN
2537 mydebug('LPN not in WDD');
2538 END IF;
2539 l_ret := 0;
2540 END;
2541 END IF;
2542
2543 -- Not the innermost LPN. Get the previous passes parent LPN's attibutes to
2544 -- give to shipping
2545 l_lpn_rec.lpn_id := parent_lpn_rec.lpn_id;
2546 l_lpn_rec.license_plate_number := parent_lpn_rec.license_plate_number;
2547 l_lpn_rec.organization_id := parent_lpn_rec.organization_id;
2548 l_lpn_rec.subinventory_code := parent_lpn_rec.subinventory_code;
2549 l_lpn_rec.locator_id := parent_lpn_rec.locator_id;
2550 l_lpn_rec.tare_weight := parent_lpn_rec.tare_weight;
2551 l_lpn_rec.tare_weight_uom_code := parent_lpn_rec.tare_weight_uom_code;
2552 l_lpn_rec.gross_weight := parent_lpn_rec.gross_weight;
2553 l_lpn_rec.gross_weight_uom_code := parent_lpn_rec.gross_weight_uom_code;
2554 l_lpn_rec.container_volume := parent_lpn_rec.container_volume;
2555 l_lpn_rec.container_volume_uom := parent_lpn_rec.container_volume_uom;
2556 l_lpn_rec.content_volume := parent_lpn_rec.content_volume;
2557 l_lpn_rec.content_volume_uom_code := parent_lpn_rec.content_volume_uom_code;
2558
2559 -- Translate LPN attribues to wsh data type
2560 l_wsh_action_prms.lpn_rec := WMS_Container_PVT.To_DeliveryDetailsRecType(l_lpn_rec);
2561
2562 -- Pack previous cursor passes LPN into it's parent in WDD
2563 -- Parent LPN will be created in WDD if it does not already exist
2564 l_wsh_lpn_id_tbl(1) := l_curr_lpn_id;
2565
2566 IF (l_debug = 1) THEN
2567 mydebug('Call to WSH Delivery_Detail_Action to pack LPN heirarchy');
2568 END IF;
2569 WSH_WMS_LPN_GRP.Delivery_Detail_Action (
2570 p_api_version_number => 1.0
2571 , p_init_msg_list => fnd_api.g_false
2572 , p_commit => fnd_api.g_false
2573 , x_return_status => x_return_status
2574 , x_msg_count => x_msg_count
2575 , x_msg_data => x_msg_data
2576 , p_lpn_id_tbl => l_wsh_lpn_id_tbl
2577 , p_del_det_id_tbl => l_wsh_del_det_id_tbl
2578 , p_action_prms => l_wsh_action_prms
2579 , x_defaults => l_wsh_defaults
2580 , x_action_out_rec => l_wsh_action_out_rec );
2581
2582 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2583 IF (l_debug = 1) THEN
2584 mydebug('Delivery_Detail_Action failed');
2585 END IF;
2586 RAISE fnd_api.g_exc_error;
2587 ELSIF (l_debug = 1) THEN
2588 mydebug('Done with call to WSH Create_Update_Containers');
2589 END IF;
2590 END IF;
2591
2592 -- LPN already exists in WDD, thus so should it's parent LPNs, exit
2593 IF ( l_ret = 1 ) THEN
2594 EXIT;
2595 ELSE -- Store current parent LPN's lpn_id as current lpn for next pass
2596 l_curr_lpn_id := l_lpn_rec.lpn_id;
2597 END IF;
2598 END LOOP;
2599
2600
2601 IF (l_debug = 1) THEN
2602 mydebug('Calling Print Label with del detail id:'||l_lpn_del_detail_id);
2603 END IF;
2604 -- Calling the print label function
2605
2606 inv_label.PRINT_LABEL_WRAP
2607 (
2608 x_return_status=>l_return_status
2609 , x_msg_count=>l_msg_cnt
2610 , x_msg_data=>l_msg_data
2611 , x_label_status=>l_label_status
2612 , p_business_flow_code=>6
2613 , p_transaction_id=>l_lpn_del_detail_id
2614 ) ;
2615
2616
2617 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2618 FND_MESSAGE.SET_NAME('INV','INV_RCV_CRT_PRINT_LAB_FAIL');
2619 FND_MSG_PUB.ADD;
2620 RAISE FND_API.g_exc_unexpected_error;
2621
2622 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2623 FND_MESSAGE.SET_NAME('INV','INV_RCV_CRT_PRINT_LAB_FAIL');
2624 FND_MSG_PUB.ADD;
2625 l_return_status :=FND_API.g_ret_sts_success;
2626 END IF;
2627
2628 x_return_status:=l_return_status;
2629
2630
2631 -- End of crossdock loop
2632 END IF;
2633
2634 -- Bug 2465491 The API completes successfully so returns success
2635 x_return_status:= FND_API.g_ret_sts_success;
2636
2637 EXCEPTION
2638 WHEN FND_API.G_EXC_ERROR THEN
2639 x_return_status := FND_API.G_RET_STS_ERROR;
2640 -- Get message count and data
2641 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2642
2643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2645 -- Get message count and data
2646 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2647
2648 WHEN OTHERS THEN
2649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2650 FND_MESSAGE.SET_NAME('WMS','WMS_TD_CCDOCK_ERROR' );
2651 FND_MSG_PUB.ADD;
2652 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2653
2654
2655 END complete_crossdock;
2656
2657
2658 PROCEDURE mark_delivery
2659 (p_del_id IN NUMBER
2660 , x_ret OUT NOCOPY VARCHAR2
2661 , x_return_status OUT NOCOPY VARCHAR2
2662 , x_msg_count OUT NOCOPY NUMBER
2663 , x_msg_data OUT NOCOPY VARCHAR2)
2664 IS
2665 l_ret VARCHAR2(1);
2666 l_del_id NUMBER;
2667 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
2668 l_msg_count NUMBER;
2669 l_msg_data VARCHAR2(240);
2670 l_return_status VARCHAR2(1);
2671
2672 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2673 BEGIN
2674
2675 IF (l_debug = 1) THEN
2676 mydebug('in mark delivery as submitted');
2677 END IF;
2678 l_del_id:=p_del_id;
2679
2680 l_shipping_attr(1).released_status := 'S';
2681 l_shipping_attr(1).delivery_detail_id := l_del_id;
2682 l_shipping_attr(1).action_flag := 'U';
2683
2684 IF (l_debug = 1) THEN
2685 mydebug('Before calling update shipping');
2686 END IF;
2687
2688 WSH_INTERFACE.Update_Shipping_Attributes
2689 (p_source_code => 'INV',
2690 p_changed_attributes => l_shipping_attr,
2691 x_return_status => l_return_status
2692 );
2693 IF (l_debug = 1) THEN
2694 mydebug('return status'||l_ret);
2695 END IF;
2696
2697 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2698 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR' );
2699 FND_MSG_PUB.ADD;
2700 RAISE FND_API.g_exc_unexpected_error;
2701
2702 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2703 FND_MESSAGE.SET_NAME('WMS','WMS_TD_UPD_SHP_ERROR');
2704 FND_MSG_PUB.ADD;
2705 RAISE FND_API.G_EXC_ERROR;
2706 END IF;
2707
2708
2709
2710 x_ret:=l_ret;
2711 x_return_status:=l_return_status;
2712
2713 EXCEPTION
2714 WHEN FND_API.G_EXC_ERROR THEN
2715 x_ret := FND_API.G_RET_STS_ERROR;
2716 x_return_status:=l_return_status;
2717 -- Get message count and data
2718 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => l_msg_data);
2719
2720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2721 x_ret := FND_API.G_RET_STS_UNEXP_ERROR ;
2722 x_return_status:=l_return_status;
2723 -- Get message count and data
2724 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => l_msg_data);
2725
2726 WHEN OTHERS THEN
2727 x_ret := FND_API.G_RET_STS_UNEXP_ERROR ;
2728 x_return_status:=l_return_status;
2729 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MD_ERROR');
2730 FND_MSG_PUB.ADD;
2731 fnd_msg_pub.count_and_get
2732 ( p_count => x_msg_count
2733 , p_data => x_msg_data
2734 );
2735
2736
2737
2738 END mark_delivery;
2739
2740 --New private api to cancel wip crossdock tasks.
2741 PROCEDURE cancel_wip_crossdock_task(p_transaction_temp_id IN NUMBER
2742 ,p_move_order_line_id IN NUMBER
2743 ,x_return_status OUT nocopy VARCHAR2
2744 ,x_msg_data OUT nocopy VARCHAR2
2745 ,x_msg_count OUT nocopy NUMBER
2746 )
2747 IS
2748 l_wdt_status NUMBER := 0;
2749 BEGIN
2750 mydebug('CANCEL_WIP_CROSSDOCK_TASK: Entering...');
2751 mydebug('CANCEL_WIP_CROSSDOCK_TASK: MMTT ID: '||p_transaction_temp_id);
2752 mydebug('CANCEL_WIP_CROSSDOCK_TASK: MOL ID: '||p_move_order_line_id);
2753
2754 x_return_status := fnd_api.g_ret_sts_success;
2755
2756 --Check to see if the task is loaded. If task is loaded and return an
2757 --error!!!
2758
2759 BEGIN
2760 SELECT status
2761 INTO l_wdt_status
2762 FROM wms_dispatched_tasks
2763 WHERE transaction_temp_id = p_transaction_temp_id
2764 AND task_type = wms_globals.g_wms_task_type_putaway;
2765 EXCEPTION
2766 WHEN no_data_found THEN
2767 l_wdt_status := 0;
2768 END;
2769
2770 IF l_wdt_status = 4 THEN
2771 x_return_status := fnd_api.g_ret_sts_error;
2772 mydebug('Task is loaded... Cannot be canceled ...');
2773 RETURN;
2774 END IF;
2775
2776 --Update MOL
2777 UPDATE mtl_txn_request_lines
2778 SET backorder_delivery_detail_id = NULL
2779 , crossdock_type = 1
2780 , quantity_detailed = (quantity - Nvl(quantity_delivered,0))
2781 WHERE line_id = p_move_order_line_id;
2782
2783 --Delete MMTT
2784 INV_TRX_UTIL_PUB.Delete_transaction
2785 (x_return_status => x_return_status,
2786 x_msg_data => x_msg_data,
2787 x_msg_count => x_msg_count,
2788 p_transaction_temp_id => p_transaction_temp_id,
2789 p_update_parent => FALSE);
2790
2791 mydebug('CANCEL_WIP_CROSSDOCK_TASK: Exiting...: '||x_return_status);
2792 EXCEPTION
2793 WHEN OTHERS THEN
2794 mydebug('CANCEL_WIP_CROSSDOCK_TASK:ERROR! Unexpected Error:'||SQLCODE);
2795 x_return_status := fnd_api.g_ret_sts_unexp_error;
2796 fnd_msg_pub.count_and_get(p_count => x_msg_count
2797 , p_data => x_msg_data);
2798
2799 END cancel_wip_crossdock_task;
2800
2801
2802 --New api to be called from WMS Control Board.
2803 PROCEDURE cancel_crossdock_task(p_transaction_temp_id IN NUMBER
2804 , x_return_status OUT nocopy VARCHAR2
2805 , x_msg_data OUT nocopy VARCHAR2
2806 , x_msg_count OUT nocopy NUMBER
2807 )
2808
2809 IS
2810 l_txn_src_type_id NUMBER;
2811 l_error_code NUMBER;
2812 l_move_order_line_id NUMBER;
2813 l_backorder_delivery_detail_id NUMBER;
2814
2815 BEGIN
2816 x_return_status := fnd_api.g_ret_sts_success;
2817
2818 mydebug('CANCEL_CROSSDOCK_TASK: Enter...');
2819 mydebug('CANCEL_CROSSDOCK_TASK: MMTT ID: '||p_transaction_temp_id);
2820
2821 --Query the MMTT using the p_transaction_temp_id
2822
2823 SELECT mmtt.transaction_source_type_id
2824 , mmtt.move_order_line_id
2825 , mtrl.backorder_delivery_detail_id
2826 INTO l_txn_src_type_id
2827 , l_move_order_line_id
2828 , l_backorder_delivery_detail_id
2829 FROM mtl_material_transactions_temp mmtt
2830 , mtl_txn_request_lines mtrl
2831 , mtl_txn_request_headers mtrh
2832 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
2833 AND mmtt.move_order_line_id = mtrl.line_id
2834 AND mtrh.header_id = mtrl.header_id
2835 AND mtrh.move_order_type = 6
2836 AND mtrl.line_status = 7;
2837
2838 mydebug('CANCEL_CROSSDOCK_TASK: TxnSourceID: '||l_txn_src_type_id);
2839 mydebug('CANCEL_CROSSDOCK_TASK: MOLID: '||l_move_order_line_id);
2840 mydebug('CANCEL_CROSSDOCK_TASK: BODDID: '||l_backorder_delivery_detail_id);
2841
2842 IF l_backorder_delivery_detail_id IS NULL THEN
2843 mydebug('CANCEL_CROSSDOCK_TASK: ERROR! Not a Crossdock Task');
2844 RAISE fnd_api.g_exc_error;
2845 END IF;
2846
2847 --Check the transaction source type.
2848 --If the transaction type is WIP then write a new api to take care of
2849 --task cancellation ELSE call cancel_operation_plan api.
2850
2851 mydebug('CANCEL_CROSSDOCK_TASK: Call the appropriate API...');
2852
2853 IF l_txn_src_type_id = inv_reservation_global.g_source_type_wip THEN
2854 mydebug('CANCEL_CROSSDOCK_TASK: Cancelling Crossdock task for a WIP LPN');
2855 cancel_wip_crossdock_task(p_transaction_temp_id => p_transaction_temp_id
2856 ,p_move_order_line_id => l_move_order_line_id
2857 ,x_return_status => x_return_status
2858 ,x_msg_data => x_msg_data
2859 ,x_msg_count => x_msg_count);
2860 ELSE
2861 mydebug('CANCEL_CROSSDOCK_TASK: Cancelling Crossdock task for a RCV LPN');
2862 wms_atf_runtime_pub_apis.cancel_operation_plan(x_return_status => x_return_status
2863 ,x_msg_data => x_msg_data
2864 ,x_msg_count => x_msg_count
2865 ,x_error_code => l_error_code
2866 ,p_source_task_id => p_transaction_temp_id
2867 ,p_activity_type_id=> 1
2868 --,p_mmtt_error_code => p_mmtt_error_code
2869 --,p_mmtt_error_explanation => p_mmtt_error_explanation
2870 );
2871 END IF;
2872
2873 mydebug('CANCEL_CROSSDOCK_TASK: x_return_status: '||x_return_status);
2874 mydebug('CANCEL_CROSSDOCK_TASK: Exiting...');
2875
2876 EXCEPTION
2877 WHEN no_data_found THEN
2878 mydebug('CANCEL_CROSSDOCK_TASK: ERROR! Invalid Transaction Temp ID: '||p_transaction_temp_id);
2879 x_return_status := fnd_api.g_ret_sts_error;
2880 fnd_msg_pub.count_and_get(p_count => x_msg_count
2881 , p_data => x_msg_data);
2882 WHEN fnd_api.g_exc_error THEN
2883 mydebug('CANCEL_CROSSDOCK_TASK: ERROR! Error raised by the API: '||SQLCODE);
2884 x_return_status := fnd_api.g_ret_sts_error;
2885 fnd_msg_pub.count_and_get(p_count => x_msg_count
2886 , p_data => x_msg_data);
2887 WHEN OTHERS THEN
2888 mydebug('CANCEL_CROSSDOCK_TASK: ERROR! Unexpected Error: '||SQLCODE);
2889 x_return_status := fnd_api.g_ret_sts_unexp_error;
2890 fnd_msg_pub.count_and_get(p_count => x_msg_count
2891 , p_data => x_msg_data);
2892
2893 END cancel_crossdock_task;
2894
2895
2896 END WMS_Cross_Dock_Pvt;
2897