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