1 PACKAGE BODY WMS_OP_INBOUND_PVT AS
2 /*$Header: WMSOPIBB.pls 120.15.12000000.2 2007/03/15 23:08:15 mchemban ship $*/
3
4 g_version_printed BOOLEAN := FALSE;
5 g_pkg_name VARCHAR2(30) := 'WMS_OP_INBOUND_PVT';
6
7 G_OP_TYPE_LOAD CONSTANT NUMBER := wms_globals.g_op_type_load;
8 G_OP_TYPE_DROP CONSTANT NUMBER:= wms_globals.G_OP_TYPE_DROP;
9 G_OP_TYPE_SORT CONSTANT NUMBER:= wms_globals.G_OP_TYPE_SORT;
10 G_OP_TYPE_CONSOLIDATE CONSTANT NUMBER:= wms_globals.G_OP_TYPE_CONSOLIDATE;
11 G_OP_TYPE_PACK CONSTANT NUMBER:= wms_globals.G_OP_TYPE_PACK;
12 G_OP_TYPE_LOAD_SHIP CONSTANT NUMBER:= wms_globals.G_OP_TYPE_LOAD_SHIP;
13 G_OP_TYPE_SHIP CONSTANT NUMBER:= wms_globals.G_OP_TYPE_SHIP;
14 G_OP_TYPE_CYCLE_COUNT CONSTANT NUMBER := wms_globals.G_OP_TYPE_CYCLE_COUNT;
15 G_OP_TYPE_INSPECT CONSTANT NUMBER := wms_globals.G_OP_TYPE_INSPECT;
16 G_OP_TYPE_CROSSDOCK CONSTANT NUMBER:= wms_globals.G_OP_TYPE_CROSSDOCK;
17 g_wms_task_type_inspect CONSTANT NUMBER:= wms_globals.g_wms_task_type_inspect;
18 g_wms_task_type_putaway CONSTANT NUMBER:= wms_globals.g_wms_task_type_putaway;
19
20 G_ACTION_RECEIPT CONSTANT NUMBER := inv_globals.g_action_receipt ;
21 G_ACTION_INTRANSITRECEIPT CONSTANT NUMBER := inv_globals.G_ACTION_INTRANSITRECEIPT;
22 G_ACTION_SUBXFR CONSTANT NUMBER := inv_globals.g_action_subxfr;
23 G_SOURCETYPE_MOVEORDER CONSTANT NUMBER := inv_globals.g_sourcetype_moveorder;
24 G_SOURCETYPE_PURCHASEORDER CONSTANT NUMBER := inv_globals.G_SOURCETYPE_PURCHASEORDER;
25 G_SOURCETYPE_INTREQ CONSTANT NUMBER := inv_globals.G_SOURCETYPE_INTREQ;
26 G_SOURCETYPE_RMA CONSTANT NUMBER := inv_globals.G_SOURCETYPE_RMA;
27 G_SOURCETYPE_inventory CONSTANT NUMBER := inv_globals.G_SOURCETYPE_inventory;
28 G_TYPE_TRANSFER_ORDER_SUBXFR CONSTANT NUMBER := inv_globals.g_type_transfer_order_subxfr;
29
30 G_TO_STATUS_CLOSED CONSTANT NUMBER := inv_globals.g_to_status_closed;
31 g_task_status_loaded CONSTANT NUMBER:= 4;
32
33
34 PROCEDURE print_debug(p_err_msg IN VARCHAR2, p_module_name IN VARCHAR2, p_level IN NUMBER) IS
35 BEGIN
36 IF NOT g_version_printed THEN
37 inv_mobile_helper_functions.tracelog(p_err_msg => '$Header: WMSOPIBB.pls 120.15.12000000.2 2007/03/15 23:08:15 mchemban ship $', p_module => g_pkg_name, p_level => 9);
38 g_version_printed := TRUE;
39 END IF;
40
41 -- dbms_output.put_line(p_err_msg);
42
43 inv_log_util.trace(p_err_msg, g_pkg_name || '.' || p_module_name,p_level);
44 END print_debug;
45
46
47 /**
48 * <b> Init</b>:
49 * <p>This API is the document handler for Inbound document records and is called from
50 * Init_op_plan_instance. This API createduplicates child the MMTT/MTLT records and
51 * nulls out the relevant fields on parent MMTT record. </p>
52 * @param x_return_status -Return Status
53 * @param x_msg_data -Returns Message Data
54 * @param x_msg_count -Returns the message count
55 * @param x_source_task_id -Returns the Source Task Id of the child document record created.
56 * @param x_error_code -Returns Appropriate error code in case of any error.
57 * @param p_source_task_id -Identifier of the document record.
58 * @param p_document_rec -Record Type of MMTT
59 * @param p_operation_type_id -Operation Type id of the first operation
60 *
61 **/
62 PROCEDURE INIT(
63 x_return_status OUT NOCOPY VARCHAR2
64 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
65 , x_msg_count OUT NOCOPY NUMBER
66 , x_source_task_id OUT NOCOPY NUMBER
67 , x_error_code OUT NOCOPY NUMBER
68 , p_source_task_id IN NUMBER
69 , p_document_rec IN mtl_material_transactions_temp%ROWTYPE
70 , p_operation_type_id IN NUMBER
71 , p_revert_loc_capacity IN BOOLEAN DEFAULT FALSE
72 , p_subsequent_op_plan_id IN NUMBER DEFAULT NULL
73 ) IS
74
75 CURSOR c_item_details(v_inventory_item_id NUMBER,v_organization_id NUMBER) IS
76 SELECT nvl(lot_control_code,1)lot_control_code,nvl(serial_number_control_code,1) serial_number_control_code
77 FROM mtl_system_items_b
78 WHERE inventory_item_id = v_inventory_item_id
79 AND organization_id = v_organization_id;
80
81 CURSOR c_mtlt_rec IS
82 SELECT *
83 FROM mtl_transaction_lots_temp
84 WHERE transaction_temp_id=p_source_task_id;
85
86 l_module_name VARCHAR2(30) := 'INIT';
87 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
88 l_progress NUMBER;
89
90 l_mtlt_rec MTL_TRANSACTION_LOTS_TEMP%ROWTYPE;
91 l_item_details_rec c_item_details%ROWTYPE;
92 l_wms_task_type NUMBER;
93 l_insert_lot NUMBER;
94 l_ser_trx_id NUMBER;
95 l_proc_msg VARCHAR2(200);
96
97 l_return_status VARCHAR2(1);
98 l_msg_count NUMBER;
99 l_msg_data fnd_new_messages.message_text%TYPE;
100
101
102 BEGIN
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104 l_progress := 10;
105
106
107 IF (l_debug=1) THEN
108 print_debug('p_source_task_id ==>'||p_source_task_id,l_module_name,3);
109 print_debug('p_operation_type_id ==>'||p_operation_type_id,l_module_name,3);
110 print_debug('p_subsequent_op_plan_id ==>'||p_subsequent_op_plan_id,l_module_name,3);
111 IF(p_revert_loc_capacity = TRUE)THEN
112 print_debug('p_revert_loc_capacity ==> T',l_module_name,3);
113 ELSE
114 print_debug('p_revert_loc_capacity ==> F',l_module_name,3);
115 END IF;
116 END IF;
117
118 /*If p_source_task_id is null then return error status and return with appropriate error code.*/
119 IF (p_source_task_id IS NULL) THEN /*Do we need to check this here as we are doing this check in
120 Public API*/
121 IF (l_debug=1) THEN
122 print_debug('Source task Id is null',l_module_name,1);
123 END IF;
124 RAISE FND_API.G_EXC_ERROR;
125 END IF;
126
127 l_progress:=20;
128
129 /*If the item is a Lot Controlled Item, Query MTL_TRANSACTIONS_LOT_TEM (MTLT) based on
130 P_Source_task_ID and poplate the PL/SQL record variable.*/
131 /*Checking if the MTLTs exist for a Lot Controlled Item.If there are no records found throw an Invalid
132 Document Error as the Assumption made is that rules will always create MTLTs for a lot controlled item
133 --Have to finalise on this and will change if required*/
134
135 OPEN c_item_details(p_document_rec.INVENTORY_ITEM_ID,p_document_rec.organization_id);
136
137 FETCH c_item_details INTO l_item_details_rec;
138
139 IF (c_item_details%NOTFOUND) THEN
140
141 IF (l_debug=1) THEN
142 print_debug('Item -Org combnation not found',l_module_name,1);
143 END IF;
144 RAISE FND_API.G_EXC_ERROR;
145
146 END IF;
147
148 CLOSE c_item_details;
149
150 l_progress:=30;
151 /*Check if item is lot Control*/
152 IF (l_item_details_rec.lot_control_code=2) THEN
153
154 IF (l_debug=1) THEN
155 print_debug('Item is lot Controlled',l_module_name,9);
156 END IF;
157
158 OPEN c_mtlt_rec;
159
160 FETCH c_mtlt_rec INTO l_mtlt_rec;
161
162 IF (c_mtlt_rec%NOTFOUND) THEN
163 IF (l_debug=1) THEN
164 print_debug('NO MTLT record exists for Lot Control Item',l_module_name,1);
165 END IF;
166 /* Throwing exception for this condition as of now as Invalid document record*/
167 RAISE FND_API.G_EXC_ERROR;
168
169 END IF;
170
171 CLOSE c_mtlt_rec;
172
173 l_progress:=40;
174
175 END IF;
176
177 /*Create child document record.
178 We need to NULL out the destination subinventory and locator for the child MMTT record,
179 because they will be suggested by ATF determination methods.
180 The actual columns need to be NULL out are: subinventory_code and locator_ID,
181 this is because of the following:
182 1. The first child MMTT record cannot be a Move order transfer (inventory) transaction.
183 2. If the first child MMTT record is for a delivery transaction we should NULL out these two fields
184 and ATF determination methods will stamp these columns later.
185 3. If the first child MMTT record is for a receiving transfer we should NULL out these two fields
186 and ATF determination methods will stamp the transfer_subinventory and
187 transfer_to_location columns later.
188 */
189
190 /*Calling API INV_TRX_UTIL_PUB.copy_insert_line_trx to create the child MMTT. This is a new API
191 in INVTRUS.pls,hence dependency with version 115. */
192
193 IF (l_debug=1) THEN
194 print_debug('Calling INV_TRX_UTIL_PUB.copy_insert_line_trx to create child MMTT',l_module_name,9);
195 END IF;
196
197 l_progress:=50;
198 /*IF p_operation_type_id='INSPECT' then
199 /*
200 This is the case where operation plan only has one 'Inspect' operation.
201 MMTT should have a system task type of 'Inspect'.
202 Update MMTT.WMS_TASK_TYPE to 'Inspect' for both parent and child tasks.*/
203
204 IF (p_operation_type_id=G_OP_TYPE_INSPECT) THEN
205 IF (l_debug=1) THEN
206 print_debug('Operation is inspect,hence setting task type to Inspect',l_module_name,9);
207 END IF;
208
209 l_wms_task_type:=WMS_GLOBALS.g_wms_task_type_inspect;
210 ELSE
211 l_wms_task_type:=p_document_rec.WMS_TASK_TYPE;
212
213 END IF;
214 /*
215 {{
216 Operation plan only has one crossdock operation. Should verify from control board
217 that the parent task has inbound crossdock plan and child has outbound plan.
218
219 }}
220
221 */
222 IF (p_operation_type_id = g_op_type_crossdock AND
223 p_subsequent_op_plan_id IS NOT NULL) -- this is necessary because this file is dual maintained for 11.5.10
224 THEN
225
226 IF (l_debug=1) THEN
227 print_debug('Stamp subsequent OP plan ID '||p_subsequent_op_plan_id||' to child task. ',l_module_name,9);
228 END IF;
229
230 INV_TRX_UTIL_PUB.copy_insert_line_trx
231 (
232 x_return_status => l_return_status
233 ,x_msg_data => l_msg_data
234 ,x_msg_count => l_msg_count
235 ,x_new_txn_temp_id => x_source_task_id
236 ,p_transaction_temp_id => p_source_task_id
237 ,p_organization_id => p_document_rec.organization_id
238 ,p_subinventory_code => FND_API.G_MISS_CHAR
239 ,p_locator_id => FND_API.G_MISS_NUM
240 ,p_parent_line_id => p_source_task_id
241 ,p_wms_task_type => l_wms_task_type
242 ,p_operation_plan_id => p_subsequent_op_plan_id
243 );
244
245 ELSE
246
247 INV_TRX_UTIL_PUB.copy_insert_line_trx
248 (
249 x_return_status => l_return_status
250 ,x_msg_data => l_msg_data
251 ,x_msg_count => l_msg_count
252 ,x_new_txn_temp_id => x_source_task_id
253 ,p_transaction_temp_id => p_source_task_id
254 ,p_organization_id => p_document_rec.organization_id
255 ,p_subinventory_code => FND_API.G_MISS_CHAR
256 ,p_locator_id => FND_API.G_MISS_NUM
257 ,p_parent_line_id => p_source_task_id
258 ,p_wms_task_type => l_wms_task_type
259 );
260
261 END IF;
262
263 l_progress:=60;
264 IF (l_debug=1) THEN
265 print_debug('Return status is'||l_return_status,l_module_name,9);
266 END IF;
267
268 IF (l_return_status=fnd_api.g_ret_sts_error) THEN
269 IF (l_debug=1) THEN
270 print_debug('Error obtained while creating child record',l_module_name,9);
271 END IF;
272 RAISE FND_API.G_EXC_ERROR;
273
274 ELSIF (l_return_status<>fnd_api.g_ret_sts_success) THEN
275
276 IF (l_debug=1) THEN
277 print_debug('unexpected error while creating child record',l_module_name,9);
278 END IF;
279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
280
281 END IF;
282
283
284 IF (x_source_task_id IS NULL) THEN
285 IF (l_debug=1) THEN
286 print_debug('Cheild record could not be created',l_module_name,1);
287
288 END IF;
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291 l_progress:=70;
292
293 /* If Lot Controllled Item then
294 Set the transaction_temp_id of the MTLT record variable to the
295 transaction_temp_id of the duplicated MMTT record.
296 */
297 IF (l_item_details_rec.lot_control_code=2) THEN
298 l_mtlt_rec.transaction_temp_id:=x_source_task_id;
299 END IF;
300
301 l_progress:=80;
302
303
304 /*Update MMTT record where transaction_temp_ID = P_source_task_ID.
305 Null out move_order_line_ID and lpn_ID.*/
306
307 UPDATE mtl_material_transactions_temp
308 SET lpn_id = NULL,
309 move_order_line_id = NULL,
310 wms_task_type = l_wms_task_type
311 WHERE transaction_temp_id = p_source_task_id;
312
313 IF (l_debug=1) THEN
314 print_debug('Updated Parent MMTT nulling LPN Id,MOL Id',l_module_name,9);
315 END IF;
316
317 l_progress:=90;
318
319 /*and Call INV_TRX_UTIL_PUB.INSERT_LOT_TRX passing the appropriate parameters
320 from the record variable to insert MTLT record.*/
321
322 IF (l_debug=1) THEN
323 print_debug('Calling Insert_lot_trx to insert child MTLT records',l_module_name,9);
324 END IF;
325
326 IF (l_item_details_rec.lot_control_code=2) THEN
327
328 l_insert_lot:=INV_TRX_UTIL_PUB.INSERT_LOT_TRX
329 (p_trx_tmp_id => x_source_task_id,
330 p_user_id => FND_GLOBAL.USER_ID ,
331 p_lot_number => l_mtlt_rec.lot_number ,
332 p_trx_qty => l_mtlt_rec.transaction_quantity,
333 p_pri_qty => l_mtlt_rec.primary_quantity,
334 p_exp_date => l_mtlt_rec.LOT_EXPIRATION_DATE,
335 p_description => l_mtlt_rec.DESCRIPTION,
336 p_vendor_name => l_mtlt_rec.VENDOR_NAME ,
337 p_supplier_lot_number => l_mtlt_rec.SUPPLIER_LOT_NUMBER,
338 p_origination_date => l_mtlt_rec.ORIGINATION_DATE,
339 p_date_code => l_mtlt_rec.DATE_CODE,
340 p_grade_code => l_mtlt_rec.GRADE_CODE,
341 p_change_date => l_mtlt_rec.CHANGE_DATE,
342 p_maturity_date => l_mtlt_rec.MATURITY_DATE,
343 p_status_id => l_mtlt_rec.STATUS_ID ,
344 p_retest_date => l_mtlt_rec.RETEST_DATE,
345 p_age => l_mtlt_rec.age,
346 p_item_size => l_mtlt_rec.item_size,
347 p_color => l_mtlt_rec.color,
348 p_volume => l_mtlt_rec.volume,
349 p_volume_uom => l_mtlt_rec.volume_uom,
350 p_place_of_origin => l_mtlt_rec.place_of_origin,
351 p_best_by_date => l_mtlt_rec.best_by_date,
352 p_length => l_mtlt_rec.length,
353 p_length_uom => l_mtlt_rec.length_uom,
354 p_recycled_content => l_mtlt_rec.recycled_content,
355 p_thickness => l_mtlt_rec.thickness,
356 p_thickness_uom => l_mtlt_rec.thickness_uom,
357 p_width => l_mtlt_rec.width,
358 p_width_uom => l_mtlt_rec.width_uom,
359 p_curl_wrinkle_fold => l_mtlt_rec.curl_wrinkle_fold,
360 p_lot_attribute_category => l_mtlt_rec.lot_attribute_category,
361 p_c_attribute1 => l_mtlt_rec.c_attribute1,
362 p_c_attribute2 => l_mtlt_rec.c_attribute2,
363 p_c_attribute3 => l_mtlt_rec.c_attribute3,
364 p_c_attribute4 => l_mtlt_rec.c_attribute4,
365 p_c_attribute5 => l_mtlt_rec.c_attribute5,
366 p_c_attribute6 => l_mtlt_rec.c_attribute6,
367 p_c_attribute7 => l_mtlt_rec.c_attribute7,
368 p_c_attribute8 => l_mtlt_rec.c_attribute8,
369 p_c_attribute9 => l_mtlt_rec.c_attribute9,
370 p_c_attribute10 => l_mtlt_rec.c_attribute10,
371 p_c_attribute11 => l_mtlt_rec.c_attribute11,
372 p_c_attribute12 => l_mtlt_rec.c_attribute12,
373 p_c_attribute13 => l_mtlt_rec.c_attribute13,
374 p_c_attribute14 => l_mtlt_rec.c_attribute14,
375 p_c_attribute15 => l_mtlt_rec.c_attribute15,
376 p_c_attribute16 => l_mtlt_rec.c_attribute16,
377 p_c_attribute17 => l_mtlt_rec.c_attribute17,
378 p_c_attribute18 => l_mtlt_rec.c_attribute18,
379 p_c_attribute19 => l_mtlt_rec.c_attribute19,
380 p_c_attribute20 => l_mtlt_rec.c_attribute20,
381 p_d_attribute1 => l_mtlt_rec.d_attribute1,
382 p_d_attribute2 => l_mtlt_rec.d_attribute2,
383 p_d_attribute3 => l_mtlt_rec.d_attribute3,
384 p_d_attribute4 => l_mtlt_rec.d_attribute4,
385 p_d_attribute5 => l_mtlt_rec.d_attribute5,
386 p_d_attribute6 => l_mtlt_rec.d_attribute6,
387 p_d_attribute7 => l_mtlt_rec.d_attribute7,
388 p_d_attribute8 => l_mtlt_rec.d_attribute8,
389 p_d_attribute9 => l_mtlt_rec.d_attribute9,
390 p_d_attribute10 => l_mtlt_rec.d_attribute10,
391 p_n_attribute1 => l_mtlt_rec.n_attribute1,
392 p_n_attribute2 => l_mtlt_rec.n_attribute2,
393 p_n_attribute3 => l_mtlt_rec.n_attribute3,
394 p_n_attribute4 => l_mtlt_rec.n_attribute4,
395 p_n_attribute5 => l_mtlt_rec.n_attribute5,
396 p_n_attribute6 => l_mtlt_rec.n_attribute6,
397 p_n_attribute7 => l_mtlt_rec.n_attribute7,
398 p_n_attribute8 => l_mtlt_rec.n_attribute8,
399 p_n_attribute9 => l_mtlt_rec.n_attribute9,
400 p_n_attribute10 => l_mtlt_rec.n_attribute10,
401 x_ser_trx_id => l_ser_trx_id,
402 x_proc_msg => l_proc_msg,
403 p_territory_code => l_mtlt_rec.territory_code,
404 p_vendor_id => l_mtlt_rec.vendor_id);
405
406 IF (l_insert_lot<>0) THEN
407 IF (l_debug=1) THEN
408 print_debug('Failed to insert lots',l_module_name,1);
409 END IF;
410 RAISE FND_API.G_EXC_ERROR;
411 END IF;
412 END IF;
413
414 IF p_revert_loc_capacity THEN
415
416 l_progress:=100;
417
418 IF l_debug=1 THEN
419 print_debug('Suggested locator capacity of the Rules suggested locaotr needs to be reverted',l_module_name,9);
420 END IF;
421
422 IF ( p_document_rec.locator_id IS NOT NULL) THEN
423 inv_loc_wms_utils.revert_loc_suggested_cap_nauto
424 (
425 x_return_status => l_return_status
426 , x_msg_count => l_msg_count
427 , x_msg_data => l_msg_data
428 , p_organization_id => p_document_rec.organization_id
429 , p_inventory_location_id => p_document_rec.locator_id
430 , p_inventory_item_id => p_document_rec.inventory_item_id
431 , p_primary_uom_flag => 'Y'
432 , p_transaction_uom_code => NULL
433 , p_quantity => p_document_rec.primary_quantity
434 );
435 END IF;
436
437 l_progress:=110;
438
439 IF (l_debug=1) THEN
440 print_debug('Return status is'||l_return_status,l_module_name,9);
441 END IF;
442
443 IF (l_return_status<>fnd_api.g_ret_sts_success) THEN
444
445 IF (l_debug=1) THEN
446 print_debug('Error obtained while reverting locator capacity',l_module_name,9);
447 print_debug('Error msg'||x_msg_data,l_module_name,9);
448 END IF;
449
450 END IF;
451
452 END IF;
453
454 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
455
456 EXCEPTION
457 WHEN fnd_api.g_exc_error THEN
458 IF (l_debug=1) THEN
459 print_debug('Error obatined at'||l_progress,l_module_name,1);
460 END IF;
461 x_return_status:=FND_API.G_RET_STS_ERROR;
462 /*Message or error code to be populated for Operation PLan Instance Id Null*/
463
464 WHEN OTHERS THEN
465
466 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
467 IF (l_debug=1) THEN
468 print_debug('Unexpected Error'||SQLERRM||'at '||l_progress,l_module_name,3);
469 END IF;
470 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
471 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
472 END IF;
473 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
474
475 END;
476
477 /**
478 * <b> Activate</b>:
479 * <p>This API is the document handler for Inbound document records and is called from
480 * Activate_operation_instance. This API updates MMTT records and
481 * with the suggested subinventory,locator </p>
482 * @param x_return_status -Return Status
483 * @param x_msg_data -Returns Message Data
484 * @param x_msg_count -Returns the message count
485 * @param x_error_code -Returns Appropriate error code in case of any error.
486 * @param p_source_task_id -Identifier of the document record.
487 * @param p_update_param_rec -Record Type of WMS_ATF_RUNTIME_PUB_APIS.DEST_PARAM_REC_TYPE
488 *
489 **/
490 PROCEDURE ACTIVATE(
491 x_return_status OUT NOCOPY VARCHAR2
492 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
493 , x_msg_count OUT NOCOPY NUMBER
494 , x_error_code OUT NOCOPY NUMBER
495 , p_source_task_id IN NUMBER
496 , p_update_param_rec IN DEST_PARAM_REC_TYPE
497 , p_document_rec IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
498 )IS
499
500 /* CURSOR c_locator_type(v_location_id NUMBER,v_org_id NUMBER) IS
501 SELECT Nvl(inventory_location_type, 3)
502 FROM mtl_item_locations
503 WHERE inventory_location_id=v_location_id
504 AND organization_id=v_org_id;
505 */
506 CURSOR c_sub_type(v_sub_code VARCHAR2,v_org_id NUMBER) IS
507 SELECT Nvl(subinventory_type, 1)
508 FROM mtl_secondary_inventories
509 WHERE secondary_inventory_name=v_sub_code
510 AND organization_id=v_org_id;
511
512 CURSOR c_orig_sugges(v_txn_temp_id NUMBER) IS
513 SELECT nvl(transfer_subinventory,subinventory_code) subinventory_code
514 ,nvl(transfer_to_location,locator_id) locator_id
515 FROM mtl_material_transactions_temp
516 WHERE transaction_temp_id=v_txn_temp_id;
517
518 l_sub_type NUMBER := -1;
519 l_orig_sugges c_orig_sugges%ROWTYPE;
520
521 l_module_name VARCHAR2(30) := 'ACTIVATE';
522 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
523 l_progress NUMBER;
524
525 l_msg_data fnd_new_messages.message_text%TYPE;
526 l_return_status VARCHAR2(1);
527 l_msg_count NUMBER;
528
529 BEGIN
530 x_return_status := FND_API.G_RET_STS_SUCCESS;
531 l_progress := 10;
532
533 /*Whenever we update transfer_to_location and transfer_subinventory, we need to aware that we are
534 * dealing with three types of MMTTS:
535 * 1.Receiving transfer: Transaction type 'PO receipt, interorg transfer receipt,
536 RMA receipt', destination locator type 'receiving'.
537 For this type of MMTT, destination is populated in 'transfer_ sub/locator' fields.
538 2.Receiving deliver: Transaction type 'PO receipt', destination locator type 'inventory'.
539 For this type of MMTT, destination is populated in 'sub/locator' fields.
540 3.Inventory transfer: Transaction type 'Move order transfer'. For this type of MMTT,
541 destination is populated in 'transfer_ sub/locator' fields.
542 Transaction type is referred in the document for simplicity sake,
543 check in the code should be based on transaction_action_ID and transaction_source_type_ID.
544 Query for destination locator is actually simpler, if transfer_to_location and
545 transfer_subinventory are NULL, we know that destination is stored in subinventory_code and
546 locator_ID columns.
547 1. Query locator_type for P_UPDATE_PARAM_REC.LOCATOR_ID
548 2. Update MMTT with P_UPDATE_PARAM_REC
549 */
550
551 /*Fetching the subinventory Type*/
552 /* Checking if the dest sub,locator and cartonization Id have been suggested. Returning success
553 * if they arent populated.
554 */
555 IF p_update_param_rec.SUG_SUB_CODE IS NOT NULL OR p_update_param_rec.SUG_LOCATION_ID IS NOT NULL
556 OR p_update_param_rec.cartonization_id IS NOT NULL THEN
557
558 l_progress:=25;
559
560 OPEN c_sub_type(p_update_param_rec.sug_sub_code,p_document_rec.organization_id);
561
562 FETCH c_sub_type INTO l_sub_type;
563
564 CLOSE c_sub_type;
565
566 IF (l_sub_type=-1 OR l_sub_type NOT IN (1,2)) THEN
567 IF (l_debug=1) THEN
568 print_debug('Invalid Sub',l_module_name,1);
569 END IF;
570
571 RAISE FND_API.G_EXC_ERROR;
572 END IF;
573
574 l_progress:=27;
575
576 /*Deliver transaction updating sub_code and locator_id ,else transfer sub,transfer sub,transfer locator */
577 IF (l_debug=1) THEN
578 print_debug('l_sub_type = '||l_sub_type,l_module_name,1);
579 print_debug('p_document_rec.transaction_action_id = '||p_document_rec.transaction_action_id,l_module_name,1);
580 print_debug('p_document_rec.transaction_source_type_id = '||p_document_rec.transaction_source_type_id,l_module_name,1);
581
582 END IF;
583
584 IF ((l_sub_type<>2)AND ((p_document_rec.transaction_action_id=G_ACTION_INTRANSITRECEIPT) OR
585 (p_document_rec.transaction_action_id=G_ACTION_RECEIPT AND p_document_rec.transaction_source_type_id IN (G_SOURCETYPE_PURCHASEORDER,G_SOURCETYPE_RMA)))) THEN
586
587 IF (l_debug=1) THEN
588 print_debug('Deliver transaction, update sub/loc.',l_module_name,1);
589 END IF;
590
591 UPDATE MTL_material_transactions_temp
592 SET subinventory_code = p_update_param_rec.sug_sub_code,
593 locator_id = p_update_param_rec.sug_location_id,
594 cartonization_id = p_update_param_rec.cartonization_id
595 WHERE transaction_temp_id = p_source_task_id
596 AND organization_id = p_document_rec.organization_id;
597
598 ELSE
599 IF (l_debug=1) THEN
600 print_debug('Transfer transaction, update transfer sub/loc.',l_module_name,1);
601 END IF;
602
603 UPDATE MTL_material_transactions_temp
604 SET transfer_subinventory = p_update_param_rec.sug_sub_code,
605 transfer_to_location = p_update_param_rec.sug_location_id,
606 cartonization_id = p_update_param_rec.cartonization_id
607 WHERE transaction_temp_id = p_source_task_id
608 AND organization_id = p_document_rec.organization_id;
609
610 END IF;
611 l_progress:=30;
612
613 END IF;
614
615 /*If the suggestion is not system suggested locator then Update suggested capacity*/
616 l_progress:=40;
617
618 OPEN c_orig_sugges(p_document_rec.parent_line_id);
619
620 FETCH c_orig_sugges INTO l_orig_sugges;
621
622 CLOSE c_orig_sugges;
623
624 l_progress:=50;
625 IF (l_debug=1) THEN
626 print_debug('Original suggested Sub'||l_orig_sugges.subinventory_code,l_module_name,9);
627 print_debug('Original suggested locator'||l_orig_sugges.locator_id,l_module_name,9);
628
629 END IF;
630
631 IF l_orig_sugges.subinventory_code<>p_update_param_rec.sug_sub_code OR
632 l_orig_sugges.locator_id<>p_update_param_rec.sug_location_id THEN
633
634 IF (l_debug=1) THEN
635 print_debug('Suggested Capacity of Locator needs to be updated',l_module_name,9);
636 END IF;
637
638 l_progress:=60;
639
640 IF (p_update_param_rec.sug_location_id IS NOT NULL) THEN
641 inv_loc_wms_utils.update_loc_sugg_cap_wo_empf(
642 x_return_status => l_return_status
643 , x_msg_count => l_msg_count
644 , x_msg_data => l_msg_data
645 , p_organization_id => p_document_rec.organization_id
646 , p_inventory_location_id => p_update_param_rec.sug_location_id
647 , p_inventory_item_id => p_document_rec.inventory_item_id
648 , p_primary_uom_flag => 'Y'
649 , p_transaction_uom_code => NULL
650 , p_quantity => p_document_rec.primary_quantity
651 );
652 END IF;
653
654 IF (l_debug=1) THEN
655 print_debug('Return status is'||l_return_status,l_module_name,9);
656 END IF;
657
658 IF (l_return_status<>fnd_api.g_ret_sts_success) THEN
659
660 IF (l_debug=1) THEN
661 print_debug(' error while updating suggested locator capacity',l_module_name,9);
662 END IF;
663
664 END IF;
665
666 END IF;
667
668 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
669
670 EXCEPTION
671 WHEN fnd_api.g_exc_error THEN
672 IF (l_debug=1) THEN
673 print_debug('Error obatined at'||l_progress,l_module_name,1);
674 END IF;
675 x_return_status:=FND_API.G_RET_STS_ERROR;
676 /*Message or error code to be populated for Operation PLan Instance Id Null*/
677
678 WHEN OTHERS THEN
679
680 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
681 IF (l_debug=1) THEN
682 print_debug('Unexpected Error'||SQLERRM||'at '||l_progress,l_module_name,3);
683 END IF;
684 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
685 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
686 END IF;
687 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
688
689 END activate;
690
691
692 -- The revert_crossdock API reverts a sales order or wip crossdock by
693 -- 1. Null out crossdock related data on MOL
694 -- 2. notify wip or wsh that material should still be backordered.
695 -- Ideally this api should belong to WMSCRDKB.pls,
696 -- put it here because WMSCRDKB.pls is tripple maintained and
697 -- I try to avoid introducing a dependency on WSH_INTERFACE_EXT_GRP
698
699 PROCEDURE revert_crossdock
700 (x_return_status OUT NOCOPY VARCHAR2
701 , x_msg_count OUT NOCOPY NUMBER
702 , x_msg_data OUT NOCOPY VARCHAR2
703 , p_move_order_line_id IN NUMBER
704 , p_crossdock_type IN NUMBER
705 , p_backorder_delivery_detail_id IN NUMBER
706 , p_repetitive_line_id IN NUMBER
707 , p_operation_seq_number IN NUMBER
708 , p_inventory_item_id IN NUMBER
709 , p_primary_quantity IN NUMBER
710 )
711 IS
712 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
713 l_return_status VARCHAR2(1);
714 l_msg_count NUMBER;
715 l_msg_data VARCHAR2(400);
716 l_progress NUMBER;
717 l_module_name VARCHAR2(30) := 'REVERT_CROSSDOCK';
718
719 l_detail_info_tab WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
720 l_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
721 l_out_Rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
722 l_api_version_number NUMBER := 1.0;
723
724 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
725 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
726 l_organization_id NUMBER;
727 l_cur_rel_status VARCHAR2(1);
728
729 BEGIN
730 IF (l_debug = 1) THEN
731 print_debug('Entered. ', l_module_name,1);
732 print_debug(' p_backorder_delivery_detail_id = '||p_backorder_delivery_detail_id, l_module_name,1);
733 print_debug(' p_crossdock_type = '||p_crossdock_type, l_module_name,1);
734 print_debug(' p_move_order_line_id = '||p_move_order_line_id, l_module_name,1);
735 print_debug(' p_repetitive_line_id = '||p_repetitive_line_id, l_module_name,1);
736 print_debug(' p_operation_seq_number = '||p_operation_seq_number, l_module_name,1);
737 print_debug(' p_inventory_item_id = '||p_inventory_item_id, l_module_name,1);
738 print_debug(' p_primary_quantity = '||p_primary_quantity, l_module_name,1);
739
740 END IF;
741
742 l_progress := 10;
743
744 x_return_status := FND_API.g_ret_sts_success;
745 SAVEPOINT sp_revert_crossdock;
746
747 l_progress := 20;
748
749 UPDATE mtl_txn_request_lines
750 SET backorder_delivery_detail_id = NULL
751 , to_subinventory_code = NULL
752 , to_locator_id = NULL
753 , crossdock_type = NULL
754 WHERE line_id = p_move_order_line_id
755 returning organization_id INTO l_organization_id;
756
757 l_progress := 30;
758
759 IF p_crossdock_type = 2 THEN
760 l_progress := 40;
761
762 IF (l_debug = 1) THEN
763 print_debug('Before calling wms_wip_integration.unallocate_material.', l_module_name,1);
764 END IF;
765
766 wms_wip_integration.unallocate_material
767 (
768 p_wip_entity_id => p_backorder_delivery_detail_id
769 , p_operation_seq_num => p_operation_seq_number
770 , p_inventory_item_id => p_inventory_item_id
771 , p_repetitive_schedule_id => p_repetitive_line_id
772 , p_primary_quantity => p_primary_quantity
773 , x_return_status => l_return_status
774 , x_msg_data => l_msg_data
775 );
776
777 l_progress := 50;
778
779 IF l_return_status <>FND_API.g_ret_sts_success THEN
780 IF (l_debug=1) THEN
781 print_debug('wms_wip_integration.unallocate_material returned with x_return_status = '||l_return_status, l_module_name,1);
782 END IF;
783
784 RAISE FND_API.G_EXC_ERROR;
785
786 END IF;
787
788 ELSE
789 IF (l_debug = 1) THEN
790 print_debug('revert sales order crossdock.', l_module_name,1);
791 END IF;
792
793 BEGIN
794 SELECT released_status
795 INTO l_cur_rel_status
796 FROM wsh_delivery_details
797 WHERE delivery_detail_id = p_backorder_delivery_detail_id;
798
799 IF (l_debug = 1) THEN
800 print_debug('current released_status = '||l_cur_rel_status,l_module_name,1);
801 END IF;
802 EXCEPTION
803 WHEN OTHERS THEN
804 IF (l_debug = 1) THEN
805 print_debug('Unable to query WDD.', l_module_name,1);
806 END IF;
807 l_cur_rel_status := 'S';
808 END;
809
810 l_progress := 60;
811
812 IF l_cur_rel_status = 'S' THEN
813 l_detail_info_tab(1).delivery_detail_id := p_backorder_delivery_detail_id;
814 l_detail_info_tab(1).released_status := 'R';
815 l_detail_info_tab(1).move_order_line_id := NULL;
816
817 l_in_rec.caller := 'WMS_XDOCK_WMSOPIBB';
818 l_in_rec.action_code := 'UPDATE';
819
820 l_return_status := fnd_api.g_ret_sts_success;
821 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
822 ( p_api_version_number => 1.0
823 , p_init_msg_list => fnd_api.g_false
824 , p_commit => fnd_api.g_false
825 , x_return_status => l_return_status
826 , x_msg_count => l_msg_count
827 , x_msg_data => l_msg_data
828 , p_detail_info_tab => l_detail_info_tab
829 , p_in_rec => l_in_rec
830 , x_out_rec => l_out_rec
831 );
832
833 IF l_return_status <> fnd_api.g_ret_sts_success
834 THEN
835 IF (l_debug = 1)
836 THEN
837 print_debug
838 ( 'Error status from WSH_INTERFACE_GRP.Create_Update_Delivery_Detail: '
839 || l_return_status
840 , l_module_name, 1
841 );
842 END IF;
843
844 IF x_return_status = fnd_api.g_ret_sts_error
845 THEN
846 RAISE fnd_api.g_exc_error;
847 ELSE
848 RAISE fnd_api.g_exc_unexpected_error;
849 END IF;
850 ELSE
851 IF (l_debug = 1) THEN
852 print_debug('Successfully updated the WDD record to status ''R'''
853 , l_module_name, 1);
854 END IF;
855 END IF;
856 END IF; --IF l_cur_rel_status = 'S' THEN
857
858 l_progress := 70;
859
860 --{{
861 -- Cancel an operation plan for crossdocking to shipping should delete the reservation.
862 --}}
863 l_rsv_rec.organization_id := l_organization_id;
864 l_rsv_rec.inventory_item_id := p_inventory_item_id;
865 l_rsv_rec.demand_source_line_detail := p_backorder_delivery_detail_id;
866 l_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_rcv; -- receiving 27
867
868 IF (l_debug = 1) THEN
869 print_debug('Before calling inv_reservation_pvt.delete_reservation. ', l_module_name,1);
870 print_debug('p_api_version_number = '||l_api_version_number, l_module_name,1);
871 print_debug('p_init_msg_lst = '||FND_API.g_false, l_module_name,1);
872 print_debug('l_rsv_rec.organization_id = '||l_rsv_rec.organization_id, l_module_name,1);
873 print_debug('l_rsv_rec.inventory_item_id = '||l_rsv_rec.inventory_item_id, l_module_name,1);
874 print_debug('l_rsv_rec.demand_source_line_detail = '||l_rsv_rec.demand_source_line_detail, l_module_name,1);
875 print_debug('l_rsv_rec.supply_source_type_id = '||l_rsv_rec.supply_source_type_id, l_module_name,1);
876 print_debug('p_validation_flag = NULL', l_module_name,1);
877
878 END IF;
879 inv_reservation_pvt.delete_reservation
880 (
881 p_api_version_number=> l_api_version_number
882 , p_init_msg_lst => FND_API.g_false
883 , x_return_status => l_return_status
884 , x_msg_count => l_msg_count
885 , x_msg_data => l_msg_data
886 , p_rsv_rec => l_rsv_rec
887 , p_original_serial_number => l_original_serial_number
888 , p_validation_flag => NULL
889 );
890 IF (l_debug = 1) THEN
891 print_debug('After calling inv_reservation_pvt.delete_reservation. ', l_module_name,1);
892 print_debug('x_return_status = '||l_return_status, l_module_name,1);
893 print_debug('x_msg_count = '||l_msg_count, l_module_name,1);
894 print_debug('x_msg_data = '||l_msg_data, l_module_name,1);
895 END IF;
896
897 IF l_return_status <>FND_API.g_ret_sts_success THEN
898 IF (l_debug=1) THEN
899 print_debug('inv_reservation_pvt.delete_reservation. returned with x_return_status = '||l_return_status, l_module_name,1);
900 END IF;
901 RAISE FND_API.G_EXC_ERROR;
902 END IF;
903
904 l_progress := 80;
905
906 END IF;
907
908 IF (l_debug = 1) THEN
909 print_debug('Before exiting. ', l_module_name,1);
910 print_debug(' x_return_status = '||x_return_status, l_module_name,1);
911 print_debug(' x_msg_count = '||x_msg_count, l_module_name,1);
912 print_debug(' x_msg_data = '||x_msg_data, l_module_name,1);
913 END IF;
914 EXCEPTION
915
916 WHEN fnd_api.g_exc_error THEN
917 IF (l_debug=1) THEN
918 print_debug('fnd_api.g_exc_error. l_progress = '|| l_progress, l_module_name,1);
919 END IF;
920 x_return_status:=FND_API.G_RET_STS_ERROR;
921 ROLLBACK TO sp_revert_crossdock;
922
923 WHEN fnd_api.g_exc_unexpected_error THEN
924
925 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
926 IF (l_debug=1) THEN
927 print_debug('fnd_api.g_exc_unexpected_error. l_progress = '|| l_progress, l_module_name,1);
928 END IF;
929
930 ROLLBACK TO sp_revert_crossdock;
931
932 WHEN OTHERS THEN
933 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
934 IF (l_debug=1) THEN
935 print_debug('fnd_api.g_exc_unexpected_error. l_progress = '|| l_progress, l_module_name,1);
936 END IF;
937
938 ROLLBACK TO sp_revert_crossdock;
939
940 END revert_crossdock;
941
942
943 /**
944 * <b> Complete </b>:
945 * <p>This API is the document handler for Inbound document records and is called from
946 * Complete_operation_instance.
947 *
948 * This API is the document handler for inbound document records and it is called from Complete_operation_instance.
949 * This API handles both situations where current operation is the last step and current operation is not the last step of a plan.
950 * It maintains correct states for document tables (MMTT, MTRL, crossdock related tables etc.) for both cases.
951 * @param x_return_status -Return Status
952 * @param x_msg_data -Returns Message Data
953 * @param x_msg_count -Returns the message count
954 * @param x_source_task_id -Returns the transaction_temp_ID for the MMTT record created for the next operation
955 * @param x_error_code -Returns Appropriate error code in case of any error.
956 * @param p_source_task_id -Identifier of the document record.
957 * @param p_document_rec -Record Type of MMTT
958 * @param p_operation_type_id -Operation Type id of the current operation.
959 * @param p_next_operation_type_id -Operation Type id of the nextt operation.
960 * @param p_sug_to_sub_code -Suggested subinventory code in WOOI
961 * @param p_sug_to_locator_id -Suggested locator id in WOOI
962 * @param p_is_last_operation_flag - Flag to indicate if the current operation is the last step in the plan
963 *
964 **/
965 PROCEDURE complete
966 (
967 x_return_status OUT NOCOPY VARCHAR2
968 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
969 , x_msg_count OUT NOCOPY NUMBER
970 , x_source_task_id OUT NOCOPY NUMBER
971 , x_error_code OUT NOCOPY NUMBER
972 , p_source_task_id IN NUMBER
973 , p_document_rec IN mtl_material_transactions_temp%ROWTYPE
974 , p_operation_type_id IN NUMBER
975 , p_next_operation_type_id IN NUMBER
976 , p_sug_to_sub_code IN VARCHAR2 DEFAULT NULL
977 , p_sug_to_locator_id IN NUMBER DEFAULT NULL
978 , p_is_last_operation_flag IN VARCHAR2
979 , p_subsequent_op_plan_id IN NUMBER DEFAULT NULL
980 ) IS
981
982 l_module_name VARCHAR2(30) := 'COMPLETE';
983 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
984 l_progress NUMBER;
985 l_return_status VARCHAR2(1);
986 l_msg_count NUMBER;
987 l_msg_data VARCHAR2(400);
988
989 l_mol_uom_code VARCHAR2(3);
990 l_qty_in_mol_uom NUMBER;
991 l_dest_locator_type NUMBER;
992 l_dest_subinventory_type NUMBER;
993
994 l_new_txn_action_id NUMBER;
995 l_new_txn_source_type_id NUMBER;
996 l_new_txn_type_id NUMBER;
997 l_new_wms_task_type_id NUMBER;
998 l_new_subinventory_code VARCHAR2(10);
999 l_new_locator_id NUMBER;
1000 l_new_transfer_to_sub VARCHAR2(10);
1001 l_new_transfer_to_loc NUMBER;
1002 l_new_lpn_id NUMBER;
1003 l_lot_control_code NUMBER;
1004 l_serial_control_code NUMBER;
1005 l_backorder_delivery_detail_id NUMBER;
1006 l_locator_type NUMBER;
1007 l_subinventory_type NUMBER;
1008 -- new local variables for workflow support
1009 l_wf NUMBER;
1010
1011 CURSOR c_mmtt_data_rec IS
1012 SELECT
1013 mmtt.lpn_id,
1014 mmtt.transfer_lpn_id,
1015 mmtt.content_lpn_id,
1016 mmtt.operation_plan_id,
1017 Nvl(msi.subinventory_type, 1) subinventory_type,
1018 mmtt.move_order_line_id,
1019 mmtt.operation_seq_num,
1020 mmtt.repetitive_line_id,
1021 mmtt.primary_quantity,
1022 mmtt.inventory_item_id,
1023 mmtt.reason_id,
1024 mol.crossdock_type,
1025 mol.backorder_delivery_detail_id,
1026 mmtt.transfer_to_location,
1027 mmtt.locator_id
1028 FROM mtl_material_transactions_temp mmtt,
1029 mtl_secondary_inventories msi,
1030 mtl_txn_request_lines mol
1031 WHERE mmtt.transaction_temp_id = p_source_task_id
1032 AND Nvl(mmtt.transfer_subinventory, mmtt.subinventory_code) = msi.secondary_inventory_name (+)
1033 AND mmtt.organization_id = msi.organization_id (+)
1034 AND mol.line_id = mmtt.move_order_line_id;
1035
1036 /*
1037 CURSOR c_locator_type(v_location_id NUMBER,v_org_id NUMBER) IS
1038 SELECT inventory_location_type
1039 FROM mtl_item_locations
1040 WHERE inventory_location_id=v_location_id
1041 AND organization_id=v_org_id;
1042 */
1043
1044 CURSOR c_subinventory_type(v_subinventory_code VARCHAR2, v_org_id NUMBER) IS
1045 SELECT subinventory_type
1046 FROM mtl_secondary_inventories
1047 WHERE secondary_inventory_name = v_subinventory_code
1048 AND organization_id = v_org_id;
1049
1050 l_mmtt_data_rec c_mmtt_data_rec%ROWTYPE;
1051
1052 BEGIN
1053
1054
1055 x_return_status := FND_API.G_RET_STS_SUCCESS;
1056 l_progress := 10;
1057
1058 IF (l_debug=1) THEN
1059 print_debug('Entered. ',l_module_name,1);
1060 print_debug('p_source_task_id => '||p_source_task_id,l_module_name,1);
1061 print_debug('p_document_rec.transaction_temp_id => '||p_document_rec.transaction_temp_id,l_module_name,1);
1062 print_debug('p_operation_type_id => '||p_operation_type_id,l_module_name,1);
1063 print_debug('p_next_operation_type_id => '||p_next_operation_type_id,l_module_name,1);
1064 print_debug('p_is_last_operation_flag => '||p_is_last_operation_flag,l_module_name,1);
1065 print_debug('p_subsequent_op_plan_id ==>'||p_operation_type_id,l_module_name,3);
1066
1067 END IF;
1068
1069 -- validate input parameters
1070
1071 IF p_source_task_id IS NULL THEN
1072 IF (l_debug=1) THEN
1073 print_debug('Invalid input param. p_source_task_id Cannot be NULL.',l_module_name,4);
1074 END IF;
1075 RAISE FND_API.G_EXC_ERROR;
1076 END IF;
1077
1078 IF p_operation_type_id IS NULL THEN
1079 IF (l_debug=1) THEN
1080 print_debug('Invalid input param. p_operation_type_id Cannot be NULL.',l_module_name,4);
1081 END IF;
1082 RAISE FND_API.G_EXC_ERROR;
1083 END IF;
1084
1085 IF p_document_rec.transaction_temp_id IS NULL THEN
1086 IF (l_debug=1) THEN
1087 print_debug('Invalid input param. p_document_rec Cannot be NULL.',l_module_name,4);
1088 END IF;
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091
1092 IF p_is_last_operation_flag NOT IN ('N', 'Y') THEN
1093 IF (l_debug=1) THEN
1094 print_debug('Invalid input param. p_is_last_operation_flag has to be either Y or N.',l_module_name,4);
1095 END IF;
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098
1099 l_progress := 15;
1100
1101 OPEN c_mmtt_data_rec;
1102
1103 FETCH c_mmtt_data_rec INTO l_mmtt_data_rec;
1104 IF c_mmtt_data_rec%notfound THEN
1105 IF (l_debug=1) THEN
1106 print_debug('Invalid p_source_task_id : '||p_source_task_id,l_module_name,1);
1107 RAISE fnd_api.G_EXC_ERROR;
1108 END IF;
1109 END IF;
1110 CLOSE c_mmtt_data_rec;
1111
1112
1113 l_progress := 20;
1114
1115 IF p_is_last_operation_flag = 'Y' THEN
1116 -- Logic to handle document tables if the current operation is the last step in an plan.
1117 -- It also handles the case where operation plan is not stamped on MMTT.
1118
1119 IF (l_debug=1) THEN
1120 print_debug('Logic to handle last operation within the plan.',l_module_name,4);
1121 END IF;
1122
1123
1124 IF (l_debug=1) THEN
1125 print_debug('p_document_rec.parent_line_id = '||p_document_rec.parent_line_id,l_module_name,4);
1126 END IF;
1127
1128 -- Since this is the last step, we need to delete the parent (original) MMTT record.
1129 -- When operation_plan_id is not stamped on MMTT, parent_line_id will also be NULL,
1130 -- in which case delete parent MMTT will not be necessary.
1131
1132 IF p_document_rec.parent_line_id IS NOT NULL THEN
1133 IF (l_debug=1) THEN
1134 print_debug('p_document_rec.parent_line_id is not null, therefore need to delete parent MMTT record',l_module_name,4);
1135 print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
1136
1137 print_debug('p_transaction_temp_id => '||p_document_rec.parent_line_id,l_module_name,4);
1138 END IF;
1139
1140 l_progress := 30;
1141
1142 inv_trx_util_pub.delete_transaction
1143 (x_return_status => l_return_status
1144 , x_msg_data => l_msg_data
1145 , x_msg_count => l_msg_count
1146 , p_transaction_temp_id => p_document_rec.parent_line_id
1147 , p_update_parent => FALSE
1148 );
1149
1150 l_progress := 40;
1151
1152 IF (l_debug=1) THEN
1153 print_debug('After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
1154
1155 print_debug('l_return_status => '||l_return_status,l_module_name,4);
1156 print_debug('l_msg_data => '||l_msg_data,l_module_name,4);
1157 print_debug('l_msg_count => '||l_msg_count,l_module_name,4);
1158 END IF;
1159
1160 IF l_return_status <>FND_API.g_ret_sts_success THEN
1161 IF (l_debug=1) THEN
1162 print_debug('inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
1163 END IF;
1164
1165 RAISE FND_API.G_EXC_ERROR;
1166 END IF;
1167
1168 END IF; -- p_document_rec.parent_line_id IS NOT NULL
1169
1170 IF p_operation_type_id = g_op_type_drop THEN
1171 -- current operation type is drop
1172 IF (l_debug=1) THEN
1173 print_debug('Current operation type is DROP '||l_return_status,l_module_name,4);
1174 END IF;
1175
1176
1177 -- This is the last step in the operation plan,
1178 -- therefore need to call complete_crossdock
1179
1180 l_progress := 42;
1181
1182 SELECT backorder_delivery_detail_id
1183 INTO l_backorder_delivery_detail_id
1184 FROM mtl_txn_request_lines
1185 WHERE line_id = p_document_rec.move_order_line_id;
1186
1187 l_progress := 44;
1188
1189 IF (l_debug=1) THEN
1190 print_debug('l_backorder_delivery_detail_id = '||l_backorder_delivery_detail_id, l_module_name,4);
1191 END IF;
1192
1193 IF l_backorder_delivery_detail_id IS NOT NULL THEN
1194
1195
1196 -- Although it appears to be the last step, it is not necessary the last step within
1197 -- a plan. It could be that this plan has been aborted.
1198
1199 -- So we need to check for two things:
1200 -- 1. If the current drop to subinventory is not inventory sub, we should by pass
1201 -- complete_crossdock call.
1202 -- 2. If the current drop to sub is inventory sub, BUT the current drop is for an
1203 -- aborted plan, and the aborted operation was NOT the last step in the plan.
1204 -- However it is not practical to check aborted operations since that requires
1205 -- querying WOOIH. So we will make sure abort does NOT happen at the last step.
1206 -- It actually makes sense that abort at the last step does not buy us much.
1207 -- Then we should not call complete_crossdock, rather should call revert_crossdock.
1208 -- 3. Need to do one final check for reservable and lpn-controlled. (Or should it be
1209 -- taken care of by frond end?)
1210
1211 -- THE ABOVE WAS THE ORIGINAL DISCUSSION
1212 -- Now it gets simplified to:
1213 -- Call complete_crossdock if it is storage sub. call revert_crossdock it is receivint sub.
1214
1215 IF (l_debug=1) THEN
1216 print_debug('l_mmtt_data_rec.subinventory_type = '||l_mmtt_data_rec.subinventory_type , l_module_name,4);
1217 print_debug('l_mmtt_data_rec.operation_plan_id = '||l_mmtt_data_rec.operation_plan_id, l_module_name,4);
1218 END IF;
1219
1220 --{{
1221 -- When operation plan is aborted before dropping to inventory
1222 -- should retain the crossdock. And load/drop the LPN again
1223 -- should continue the crossdocking
1224 --}}
1225
1226 IF l_mmtt_data_rec.subinventory_type = 2 THEN
1227 -- Only call crossdock related stuff if not rcv sub
1228 -- revert the crossdock if dropping to rcv sub and last step.
1229 -- It means plan has been aborted.
1230 NULL;
1231
1232 ELSE
1233
1234
1235 IF (l_debug=1) THEN
1236 print_debug(' Move order line has been crossdocked. And drop to sub is storage. l_backorder_delivery_detail_id = '||l_backorder_delivery_detail_id, l_module_name,4);
1237 print_debug(' Before calling WMS_Cross_Dock_Pvt.complete_crossdock with following parameters: ', l_module_name,4);
1238 print_debug('p_org_id => '|| p_document_rec.organization_id, l_module_name,4);
1239 print_debug('p_temp_id => '|| p_source_task_id, l_module_name,4);
1240 END IF;
1241
1242 l_progress := 46;
1243
1244 WMS_Cross_Dock_Pvt.complete_crossdock
1245 (p_org_id => p_document_rec.organization_id
1246 , p_temp_id => p_source_task_id
1247 , x_return_status => l_return_status
1248 , x_msg_count => l_msg_count
1249 , x_msg_data => l_msg_data
1250 );
1251
1252 l_progress := 48;
1253
1254 IF (l_debug=1) THEN
1255 print_debug(' After calling WMS_Cross_Dock_Pvt.complete_crossdock. ', l_module_name,4);
1256 print_debug('l_return_status => '|| l_return_status, l_module_name,4);
1257 print_debug('l_msg_count => '|| l_msg_count, l_module_name,4);
1258 print_debug('l_msg_data => '|| l_msg_data, l_module_name,4);
1259 END IF;
1260
1261 IF l_return_status <>FND_API.g_ret_sts_success THEN
1262 IF (l_debug=1) THEN
1263 print_debug('WMS_Cross_Dock_Pvt.complete_crossdock finished with error. l_return_status = ' || l_return_status,l_module_name,4);
1264 END IF;
1265
1266 RAISE FND_API.G_EXC_ERROR;
1267 END IF;
1268
1269 -- END IF; -- IF l_mmtt_data_rec.operation_plan_id IS NULL
1270
1271
1272 END IF; -- IF l_mmtt_data_rec.subinventory_type = 2
1273
1274
1275 END IF; -- IF l_backorder_delivery_detail_id IS NOT NULL
1276
1277
1278 -- Multi-step putaway workflow support
1279 IF (l_debug=1) THEN
1280 print_debug('Checking if we need to call workflow wrapper',l_module_name,4);
1281 END IF;
1282
1283 IF l_mmtt_data_rec.subinventory_type = 1 THEN -- inventory sub
1284 IF (l_debug=1) THEN
1285 print_debug('Dropping to inventory sub',l_module_name,4);
1286 END IF;
1287
1288 IF (l_mmtt_data_rec.reason_id IS NOT NULL AND
1289 Nvl(l_mmtt_data_rec.transfer_to_location,l_mmtt_data_rec.locator_id) <> p_sug_to_locator_id) THEN
1290 IF (l_debug=1) THEN
1291 print_debug('We are in last operation, dropping subinv type is inventory, and reason id is not null',l_module_name,4);
1292 print_debug('We are going to do workflow processing...',l_module_name,4);
1293 END IF;
1294
1295 l_wf := 0;
1296
1297 BEGIN
1298 SELECT 1
1299 INTO l_wf
1300 FROM mtl_transaction_reasons
1301 WHERE reason_id = l_mmtt_data_rec.reason_id
1302 AND workflow_name IS NOT NULL
1303 AND workflow_name <> ' '
1304 AND workflow_process IS NOT NULL
1305 AND workflow_process <> ' ';
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND THEN
1308 l_wf := 0;
1309 END;
1310
1311 IF l_wf > 0 THEN
1312
1313 IF (l_debug = 1) THEN
1314 print_debug('WF exists for this reason code: ' || l_mmtt_data_rec.reason_id,l_module_name,4);
1315 print_debug('Calling workflow wrapper FOR location',l_module_name,4);
1316 print_debug('dest sub: ' || p_sug_to_sub_code,l_module_name,4);
1317 print_debug('dest loc: ' || p_sug_to_locator_id,l_module_name,4);
1318 END IF;
1319
1320 -- Calling Workflow
1321 wms_workflow_wrappers.wf_wrapper(
1322 p_api_version => 1.0
1323 , p_init_msg_list => fnd_api.g_false
1324 , p_commit => fnd_api.g_false
1325 , x_return_status => x_return_status
1326 , x_msg_count => x_msg_count
1327 , x_msg_data => x_msg_data
1328 , p_org_id => p_document_rec.organization_id
1329 , p_rsn_id => l_mmtt_data_rec.reason_id
1330 , p_calling_program => 'complete - for loc discrepancy - loose'
1331 , p_tmp_id => p_source_task_id
1332 , p_quantity_picked => NULL
1333 , p_dest_sub => p_sug_to_sub_code
1334 , p_dest_loc => p_sug_to_locator_id
1335 );
1336
1337 IF (l_debug = 1) THEN
1338 print_debug('After Calling WF Wrapper',l_module_name,4);
1339 END IF;
1340
1341 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1342 IF (l_debug = 1) THEN
1343 print_debug('Error callinf WF wrapper',l_module_name,4);
1344 END IF;
1345
1346 fnd_message.set_name('WMS', 'WMS_WORK_FLOW_FAIL');
1347 fnd_msg_pub.ADD;
1348 RAISE fnd_api.g_exc_unexpected_error;
1349 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1350 IF (l_debug = 1) THEN
1351 print_debug('Error calling WF wrapper',l_module_name,4);
1352 END IF;
1353
1354 fnd_message.set_name('WMS', 'WMS_WORK_FLOW_FAIL');
1355 fnd_msg_pub.ADD;
1356 RAISE fnd_api.g_exc_error;
1357 END IF;
1358 END IF; -- END IF (l_wf > 0)
1359 END IF; -- END IF reason id is not null
1360 END IF; -- END IF inventory sub
1361
1362 -- First need to delete current MMTT record.
1363 -- Only delete MMTT for Receiving transfer or receiving deliver.
1364 -- Inventory TM will delete MMTT for inventory and WIP putaway.
1365
1366 IF (l_debug=1) THEN
1367
1368 print_debug('p_document_rec.TRANSACTION_ACTION_ID = '||p_document_rec.TRANSACTION_ACTION_ID,l_module_name,4);
1369 print_debug('p_document_rec.TRANSACTION_SOURCE_TYPE_ID = '||p_document_rec.TRANSACTION_SOURCE_TYPE_ID,l_module_name,4);
1370 END IF;
1371
1372 IF (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
1373 AND p_document_rec.transaction_source_type_id = g_sourcetype_purchaseorder
1374 -- PO receipt (1, 27)
1375 ) OR
1376 (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
1377 AND p_document_rec.transaction_source_type_id = g_sourcetype_rma
1378 -- RMA receipt (12, 27)
1379 ) OR
1380 (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
1381 AND p_document_rec.transaction_source_type_id = g_sourcetype_moveorder
1382 -- Consolidated inbound move order receipt (4, 27)
1383 ) OR
1384 (p_document_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
1385 AND p_document_rec.transaction_source_type_id = g_sourcetype_inventory
1386 -- Intransit shipment receipt (13, 12)
1387 ) OR
1388 (p_document_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
1389 AND p_document_rec.transaction_source_type_id = G_SOURCETYPE_INTREQ
1390 ) THEN
1391
1392 -- Before deleting MMTT need to update quantity_detailed on move order line
1393 -- And need to check if UOM on MOL and MMTT match.
1394
1395 IF (l_debug=1) THEN
1396 print_debug('This is a receipt transaction, need to delete MMTT. '||l_return_status,l_module_name,4);
1397 END IF;
1398
1399 l_progress := 49;
1400
1401 SELECT uom_code
1402 INTO l_mol_uom_code
1403 FROM mtl_txn_request_lines
1404 WHERE line_id = p_document_rec.move_order_line_id;
1405
1406 l_progress := 50;
1407
1408 IF (l_debug=1) THEN
1409
1410 print_debug('l_mol_uom_code = '||l_mol_uom_code,l_module_name,4);
1411 print_debug('p_document_rec.transaction_uom = '||p_document_rec.transaction_uom,l_module_name,4);
1412 END IF;
1413
1414 l_qty_in_mol_uom := p_document_rec.transaction_quantity;
1415
1416 -- If UOM on MOL does not match that on MMTT,
1417 -- need to convert MMTT quantity to that of MOL's UOM
1418 IF l_mol_uom_code <> p_document_rec.transaction_uom THEN
1419 IF (l_debug=1) THEN
1420 print_debug('MMTT and MOL UOM do not match, need to convert quantity.',l_module_name,4);
1421 print_debug('Before calling INV_Convert.inv_um_convert with parameters:', l_module_name,4);
1422 print_debug('item_id => '||p_document_rec.inventory_item_id, l_module_name,4);
1423 print_debug('from_quantity => '||p_document_rec.transaction_quantity, l_module_name,4);
1424 print_debug('from_unit => '||p_document_rec.transaction_uom, l_module_name,4);
1425 print_debug('to_unit => '||l_mol_uom_code, l_module_name,4);
1426 END IF;
1427
1428 l_progress := 60;
1429
1430 l_qty_in_mol_uom := INV_Convert.inv_um_convert
1431 (item_id => p_document_rec.inventory_item_id,
1432 precision => null,
1433 from_quantity => p_document_rec.transaction_quantity,
1434 from_unit => p_document_rec.transaction_uom,
1435 to_unit => l_mol_uom_code,
1436 from_name => null,
1437 to_name => null);
1438
1439 l_progress := 70;
1440
1441 IF (l_debug=1) THEN
1442 print_debug('Drop for receiving txn - After calling INV_Convert.inv_um_convert. l_qty_in_mol_uom = '||l_qty_in_mol_uom, l_module_name,4);
1443
1444 END IF;
1445
1446 END IF; -- IF l_mol_uom_code <> p_document_rec.transaction_uom
1447
1448 l_progress := 80;
1449
1450 UPDATE mtl_txn_request_lines
1451 SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom,
1452 lpn_id =(SELECT Nvl(mmtt.transfer_lpn_id, mmtt.content_lpn_id)
1453 FROM mtl_material_transactions_temp mmtt
1454 WHERE mmtt.transaction_temp_id = p_document_rec.transaction_temp_id),
1455 wms_process_flag = 1 -- Bug 4657716
1456 WHERE line_id = p_document_rec.move_order_line_id;
1457
1458 l_progress := 90;
1459
1460
1461 IF (l_debug=1) THEN
1462
1463 print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
1464
1465 print_debug('p_transaction_temp_id => '||p_source_task_id,l_module_name,4);
1466 END IF;
1467
1468 inv_trx_util_pub.delete_transaction
1469 (x_return_status => l_return_status
1470 , x_msg_data => l_msg_data
1471 , x_msg_count => l_msg_count
1472 , p_transaction_temp_id => p_source_task_id
1473 , p_update_parent => FALSE
1474 );
1475
1476 IF (l_debug=1) THEN
1477 print_debug('Drop - After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
1478
1479 print_debug('l_return_status => '||l_return_status,l_module_name,4);
1480 print_debug('l_msg_data => '||l_msg_data,l_module_name,4);
1481 print_debug('l_msg_count => '||l_msg_count,l_module_name,4);
1482 END IF;
1483
1484 IF l_return_status <>FND_API.g_ret_sts_success THEN
1485 IF (l_debug=1) THEN
1486 print_debug('inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
1487 END IF;
1488
1489 RAISE FND_API.G_EXC_ERROR;
1490 END IF;
1491
1492 END IF; -- p_document_rec.transaction_action_id = G_ACTION_RECEIPT
1493
1494
1495
1496 -- If we are dropping to an inventory locator
1497 -- we need to close move order line and update quantity_delivered.
1498
1499 l_progress := 110;
1500
1501 IF (l_debug=1) THEN
1502 print_debug('p_document_rec.TRANSFER_SUBINVENTORY= '||p_document_rec.transfer_subinventory,l_module_name,4);
1503 print_debug('p_document_rec.subinventory_code = '||p_document_rec.subinventory_code,l_module_name,4);
1504 END IF;
1505
1506 -- Destination sub/locator can be populated in
1507 -- eith transfer_ fields or sub/loc fields.
1508
1509 IF p_document_rec.transfer_subinventory IS NOT NULL THEN
1510
1511 SELECT subinventory_type
1512 INTO l_dest_subinventory_type
1513 FROM mtl_secondary_inventories
1514 WHERE secondary_inventory_name = p_document_rec.transfer_subinventory
1515 AND organization_id = p_document_rec.organization_id;
1516 ELSE
1517
1518 SELECT subinventory_type
1519 INTO l_dest_subinventory_type
1520 FROM mtl_secondary_inventories
1521 WHERE secondary_inventory_name = p_document_rec.subinventory_code
1522 AND organization_id = p_document_rec.organization_id;
1523
1524 END IF; -- IF p_document_rec.transfer_subinventory IS NOT NULL
1525
1526 l_progress := 120;
1527
1528 IF (l_debug=1) THEN
1529 print_debug('l_dest_subinventory_type = '||l_dest_subinventory_type,l_module_name,4);
1530 END IF;
1531
1532 IF l_dest_subinventory_type IS NULL OR
1533 l_dest_subinventory_type = 1 THEN -- NULL or 1 considered inventory sub
1534
1535 IF (l_debug=1) THEN
1536 print_debug('Dropping into an inventory sub, need to close MOL',l_module_name,4);
1537 END IF;
1538
1539 IF l_mol_uom_code IS NULL THEN
1540 IF (l_debug=1) THEN
1541 print_debug('Need to query MOL.uom_code',l_module_name,4);
1542 END IF;
1543
1544 l_progress := 130;
1545
1546 -- if MOL UOM has not been queried previously
1547 SELECT uom_code
1548 INTO l_mol_uom_code
1549 FROM mtl_txn_request_lines
1550 WHERE line_id = p_document_rec.move_order_line_id;
1551
1552 l_progress := 140;
1553
1554 IF (l_debug=1) THEN
1555
1556 print_debug('l_mol_uom_code = '||l_mol_uom_code,l_module_name,4);
1557 print_debug('p_document_rec.transaction_uom = '||p_document_rec.transaction_uom,l_module_name,4);
1558 END IF;
1559
1560 l_qty_in_mol_uom := p_document_rec.transaction_quantity;
1561
1562 -- If UOM on MOL does not match that on MMTT,
1563 -- need to convert MMTT quantity to that of MOL's UOM
1564 IF l_mol_uom_code <> p_document_rec.transaction_uom THEN
1565 IF (l_debug=1) THEN
1566 print_debug('MMTT and MOL UOM do not match, need to convert quantity.',l_module_name,4);
1567 print_debug('Before calling INV_Convert.inv_um_convert with parameters:', l_module_name,4);
1568 print_debug('item_id => '||p_document_rec.inventory_item_id, l_module_name,4);
1569 print_debug('from_quantity => '||p_document_rec.transaction_quantity, l_module_name,4);
1570 print_debug('from_unit => '||p_document_rec.transaction_uom, l_module_name,4);
1571 print_debug('to_unit => '||l_mol_uom_code, l_module_name,4);
1572 END IF;
1573
1574 l_progress := 150;
1575
1576 l_qty_in_mol_uom := INV_Convert.inv_um_convert
1577 (item_id => p_document_rec.inventory_item_id,
1578 precision => null,
1579 from_quantity => p_document_rec.transaction_quantity,
1580 from_unit => p_document_rec.transaction_uom,
1581 to_unit => l_mol_uom_code,
1582 from_name => null,
1583 to_name => null);
1584
1585 l_progress := 160;
1586
1587 IF (l_debug=1) THEN
1588 print_debug('Drop to INV - after calling INV_Convert.inv_um_convert. l_qty_in_mol_uom = '||l_qty_in_mol_uom, l_module_name,4);
1589
1590 END IF;
1591
1592 END IF; -- IF l_mol_uom_code <> p_document_rec.transaction_uom
1593
1594 END IF; --IF l_mol_uom_code IS NULL
1595
1596 IF (l_debug=1) THEN
1597 print_debug('Before update MOL.quantity_delivered. l_qty_in_mol_uom = '||l_qty_in_mol_uom, l_module_name,4);
1598 END IF;
1599
1600 l_progress := 170;
1601 UPDATE mtl_txn_request_lines
1602 SET line_status = g_to_status_closed,
1603 quantity_delivered = Nvl(quantity_delivered, 0) + l_qty_in_mol_uom --,
1604 -- lpn_id =(SELECT Nvl(mmtt.transfer_lpn_id, mmtt.content_lpn_id)
1605 -- FROM mtl_material_transactions_temp mmtt
1606 -- WHERE mmtt.transaction_temp_id = p_document_rec.transaction_temp_id)
1607 WHERE line_id = p_document_rec.move_order_line_id;
1608 -- No need to update lpn_id when closing the move_order_line,
1609 -- not a big deal, just be consistent with earlier patchset
1610 l_progress := 180;
1611
1612 END IF; -- IF l_dest_subinventory_type IS NULL OR
1613
1614
1615
1616 ELSIF p_operation_type_id = g_op_type_inspect THEN
1617 -- current operation type is inspect
1618 IF (l_debug=1) THEN
1619 print_debug('Current operation type is INSPECT '||l_return_status,l_module_name,4);
1620 END IF;
1621
1622
1623 -- First need to delete current MMTT record.
1624 -- Before deleting MMTT need to update quantity_detailed on move order line
1625 -- And need to check if UOM on MOL and MMTT match.
1626
1627 l_progress := 190;
1628
1629 SELECT uom_code
1630 INTO l_mol_uom_code
1631 FROM mtl_txn_request_lines
1632 WHERE line_id = p_document_rec.move_order_line_id;
1633
1634 l_progress := 200;
1635
1636 IF (l_debug=1) THEN
1637
1638 print_debug('l_mol_uom_code = '||l_mol_uom_code,l_module_name,4);
1639 print_debug('p_document_rec.transaction_uom = '||p_document_rec.transaction_uom,l_module_name,4);
1640 END IF;
1641
1642 l_qty_in_mol_uom := p_document_rec.transaction_quantity;
1643
1644 -- If UOM on MOL does not match that on MMTT,
1645 -- need to convert MMTT quantity to that of MOL's UOM
1646
1647 IF l_mol_uom_code <> p_document_rec.transaction_uom THEN
1648 IF (l_debug=1) THEN
1649 print_debug('MMTT and MOL UOM do not match, need to convert quantity.',l_module_name,4);
1650 print_debug('Before calling INV_Convert.inv_um_convert with parameters:', l_module_name,4);
1651 print_debug('item_id => '||p_document_rec.inventory_item_id, l_module_name,4);
1652 print_debug('from_quantity => '||p_document_rec.transaction_quantity, l_module_name,4);
1653 print_debug('from_unit => '||p_document_rec.transaction_uom, l_module_name,4);
1654 print_debug('to_unit => '||l_mol_uom_code, l_module_name,4);
1655 END IF;
1656
1657 l_progress := 210;
1658
1659 l_qty_in_mol_uom := INV_Convert.inv_um_convert
1660 (item_id => p_document_rec.inventory_item_id,
1661 precision => null,
1662 from_quantity => p_document_rec.transaction_quantity,
1663 from_unit => p_document_rec.transaction_uom,
1664 to_unit => l_mol_uom_code,
1665 from_name => null,
1666 to_name => null);
1667
1668 l_progress := 220;
1669
1670 IF (l_debug=1) THEN
1671 print_debug('Inspect - After calling INV_Convert.inv_um_convert. l_qty_in_mol_uom = '||l_qty_in_mol_uom, l_module_name,4);
1672
1673 END IF;
1674
1675 END IF; -- IF l_mol_uom_code <> p_document_rec.transaction_uom
1676
1677 l_progress := 230;
1678
1679 UPDATE mtl_txn_request_lines
1680 SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom
1681 WHERE line_id = p_document_rec.move_order_line_id;
1682
1683 l_progress := 240;
1684
1685
1686 IF (l_debug=1) THEN
1687
1688 print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
1689
1690 print_debug('p_transaction_temp_id => '||p_source_task_id,l_module_name,4);
1691 END IF;
1692
1693 inv_trx_util_pub.delete_transaction
1694 (x_return_status => l_return_status
1695 , x_msg_data => l_msg_data
1696 , x_msg_count => l_msg_count
1697 , p_transaction_temp_id => p_source_task_id
1698 , p_update_parent => FALSE
1699 );
1700
1701 l_progress := 242;
1702
1703 IF (l_debug=1) THEN
1704 print_debug('Inspect - After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
1705
1706 print_debug('l_return_status => '||l_return_status,l_module_name,4);
1707 print_debug('l_msg_data => '||l_msg_data,l_module_name,4);
1708 print_debug('l_msg_count => '||l_msg_count,l_module_name,4);
1709 END IF;
1710
1711 IF l_return_status <>FND_API.g_ret_sts_success THEN
1712 IF (l_debug=1) THEN
1713 print_debug('inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
1714 END IF;
1715
1716 RAISE FND_API.G_EXC_ERROR;
1717 END IF;
1718
1719
1720 ELSE -- p_operation_type_id is not DROP or INSPECT
1721 IF (l_debug=1) THEN
1722 print_debug('Invalid operation type for last step, it can only be DROP or INSPECT. p_operation_type_id = '||p_operation_type_id,l_module_name,4);
1723 END IF;
1724
1725 RAISE FND_API.G_EXC_ERROR;
1726
1727 END IF; -- p_operation_type_id = g_op_type_drop
1728
1729
1730 ELSE -- p_is_last_operation_flag <> 'Y'
1731
1732 l_progress := 244;
1733
1734 IF (l_debug=1) THEN
1735 print_debug('Current operation is not the last step within the plan.',l_module_name,4);
1736 END IF;
1737
1738 -- Only if current operation is drop we need to manipulate MMTT.
1739 -- For load we don't need to do anything, inspect can only be the last step.
1740
1741 IF p_operation_type_id = g_op_type_drop THEN
1742
1743 IF (l_debug=1) THEN
1744 print_debug('Current operation is drop.',l_module_name,4);
1745 END IF;
1746
1747 -- Mainly we need to create new child MMTT record for the next operation.
1748 -- We need to create the new child MMTT record with correct transaction_type_id, transaction_action_id, and transaction_source_type_ID.
1749 -- This will be based on the destination locator type of the previous child MMTT record.
1750 -- If the previous MMTT drops to an inventory locator, we need to create a move order transfer MMTT, otherwise, PO receipt.
1751
1752 -- Also, for a new inventory transfer MMTT record, we need to set the source subinventory and locator.
1753
1754 -- It is not necessary to populate source subinventory and locator for receiving transfer MMTT record.
1755 -- But it is probably cleaner to do so, which requires passing 'is_in_inventory' flag as an input parameter. We might want to do this later.
1756
1757
1758
1759 -- Based on destination subinventory type of the current MMTT
1760 -- we decide transaction type for the new MMTT.
1761 -- If the destination sub for current MMTT is receiving,
1762 -- the new MMTT will be a receipt and transaction type will be
1763 -- carried over from the current MMTT;
1764 -- If the destination sub for current MMTT is inventory,
1765 -- the new MMTT will be a move order transfer transaction.
1766 --
1767 -- Destination sub/locator can be populated in
1768 -- eith transfer_ fields or sub/loc fields.
1769
1770 IF (l_debug=1) THEN
1771 print_debug('p_document_rec.transfer_subinventory = ' || p_document_rec.transfer_subinventory,l_module_name,4);
1772 print_debug('p_document_rec.organization_id = ' || p_document_rec.organization_id,l_module_name,4);
1773 print_debug('p_document_rec.subinventory_code = ' || p_document_rec.subinventory_code,l_module_name,4);
1774
1775 END IF;
1776
1777 IF p_document_rec.transfer_subinventory IS NOT NULL THEN
1778
1779 SELECT subinventory_type
1780 INTO l_dest_subinventory_type
1781 FROM mtl_secondary_inventories
1782 WHERE secondary_inventory_name = p_document_rec.transfer_subinventory
1783 AND organization_id = p_document_rec.organization_id;
1784 ELSE
1785
1786 SELECT subinventory_type
1787 INTO l_dest_subinventory_type
1788 FROM mtl_secondary_inventories
1789 WHERE secondary_inventory_name = p_document_rec.subinventory_code
1790 AND organization_id = p_document_rec.organization_id;
1791
1792 END IF; -- IF p_document_rec.transfer_subinventory IS NOT NULL
1793
1794 l_progress := 250;
1795
1796 IF (l_debug=1) THEN
1797 print_debug('l_dest_subinventory_type = '||l_dest_subinventory_type,l_module_name,4);
1798 END IF;
1799
1800 IF l_dest_subinventory_type IS NULL OR
1801 l_dest_subinventory_type = 1 THEN
1802 -- Current MMTT drops to an invenotry sub
1803 IF (l_debug=1) THEN
1804 print_debug('Current MMTT drops to inventory ',l_module_name,4);
1805 END IF;
1806
1807 l_new_txn_action_id := g_action_subxfr;
1808 l_new_txn_source_type_id := g_sourcetype_moveorder;
1809 l_new_txn_type_id := g_type_transfer_order_subxfr;
1810 l_new_subinventory_code := Nvl(p_document_rec.transfer_subinventory,
1811 p_document_rec.subinventory_code);
1812 l_new_locator_id := Nvl(p_document_rec.transfer_to_location,
1813 p_document_rec.locator_id);
1814 l_new_transfer_to_loc := NULL;
1815 l_new_transfer_to_sub := NULL;
1816
1817
1818 ELSE -- current MMTT drops to an receiving sub
1819 IF (l_debug=1) THEN
1820 print_debug('Current MMTT drops to receiving ',l_module_name,4);
1821 END IF;
1822
1823 l_new_txn_action_id := p_document_rec.transaction_action_id;
1824 l_new_txn_source_type_id := p_document_rec.transaction_source_type_ID;
1825 l_new_txn_type_id := p_document_rec.transaction_type_id;
1826 l_new_subinventory_code := fnd_api.g_miss_char;
1827 l_new_locator_id := fnd_api.g_miss_num;
1828 l_new_transfer_to_loc := fnd_api.g_miss_num;
1829 l_new_transfer_to_sub := fnd_api.g_miss_char;
1830
1831 END IF; -- l_dest_subinventory_type IS NULL
1832
1833
1834 -- Inventory putaway might have a transfer_lpn_id,
1835 -- In this case this LPN becomes the lpn_id for next MMTT.
1836
1837 l_new_lpn_id := Nvl(l_mmtt_data_rec.transfer_lpn_id, l_mmtt_data_rec.content_lpn_id);
1838
1839 UPDATE mtl_txn_request_lines
1840 SET lpn_id = Nvl(l_mmtt_data_rec.transfer_lpn_id, l_mmtt_data_rec.content_lpn_id)
1841 WHERE line_id = p_document_rec.move_order_line_id;
1842
1843 l_progress := 258;
1844
1845 IF p_next_operation_type_id = g_op_type_inspect THEN
1846 IF (l_debug=1) THEN
1847 print_debug('Next operation is inspect, need to set WMS_TASK_TYPE to inspect ',l_module_name,4);
1848 END IF;
1849
1850 l_new_wms_task_type_id := g_wms_task_type_inspect;
1851 ELSE
1852
1853 l_new_wms_task_type_id := p_document_rec.wms_task_type;
1854
1855 END IF; -- IF p_next_operation_type_id = g_op_type_inspect
1856
1857 IF (l_debug=1) THEN
1858
1859 print_debug('Before calling inv_trx_util_pub.copy_insert_line_trx with following parameters: ',l_module_name,4);
1860
1861 print_debug('p_transaction_temp_id => '||p_source_task_id,l_module_name,4);
1862 print_debug('p_subinventory_code => '||l_new_subinventory_code,l_module_name,4);
1863 print_debug('p_locator_id => '||l_new_locator_id,l_module_name,4);
1864 print_debug('p_to_subinventory_code => '||l_new_transfer_to_sub,l_module_name,4);
1865 print_debug('p_to_locator_id => '||l_new_transfer_to_loc,l_module_name,4);
1866 print_debug('p_txn_type_id => '||l_new_txn_type_id,l_module_name,4);
1867 print_debug('p_txn_action_id => '||l_new_txn_action_id,l_module_name,4);
1868 print_debug('p_txn_source_type_id => '||l_new_txn_source_type_id,l_module_name,4);
1869 print_debug('p_wms_task_type => '||l_new_wms_task_type_id,l_module_name,4);
1870 print_debug('p_lpn_id => '||l_new_lpn_id, l_module_name,4);
1871 print_debug('p_transfer_lpn_id => '|| fnd_api.g_miss_num, l_module_name,4);
1872 print_debug('p_content_lpn_id => '|| fnd_api.g_miss_num, l_module_name,4);
1873
1874 END IF;
1875
1876 l_progress := 260;
1877
1878 /*
1879 {{
1880 When completing the drop operation right before the crossdock operation
1881 need to stamp subsequent outbound operation plan ID to the last task
1882
1883 }}
1884
1885 */
1886 IF (p_next_operation_type_id = g_op_type_crossdock AND
1887 p_subsequent_op_plan_id IS NOT NULL) -- this is necessary because this file is dual maintained for 11.5.10
1888 THEN
1889
1890 INV_TRX_UTIL_PUB.copy_insert_line_trx
1891 (
1892 x_return_status => l_return_status
1893 ,x_msg_data => l_msg_data
1894 ,x_msg_count => l_msg_count
1895 ,x_new_txn_temp_id => x_source_task_id
1896 ,p_transaction_temp_id => p_source_task_id
1897 ,p_subinventory_code => l_new_subinventory_code
1898 ,p_locator_id => l_new_locator_id
1899 ,p_to_subinventory_code => l_new_transfer_to_sub
1900 ,p_to_locator_id => l_new_transfer_to_loc
1901 ,p_txn_type_id => l_new_txn_type_id
1902 ,p_txn_action_id => l_new_txn_action_id
1903 ,p_txn_source_type_id => l_new_txn_source_type_id
1904 ,p_wms_task_type => l_new_wms_task_type_id
1905 ,p_lpn_id => l_new_lpn_id
1906 ,p_transfer_lpn_id => fnd_api.g_miss_num -- null out transfer_lpn_id
1907 ,p_content_lpn_id => fnd_api.g_miss_num -- null out content_lpn_id
1908 ,p_operation_plan_id => p_subsequent_op_plan_id
1909 ,p_transaction_status => 2 -- Bug 5156015
1910 );
1911
1912 IF (l_debug=1) THEN
1913 print_debug('Stamp subsequent OP plan ID '||p_subsequent_op_plan_id||' to child task. ',l_module_name,9);
1914 END IF;
1915
1916 ELSE
1917 INV_TRX_UTIL_PUB.copy_insert_line_trx
1918 (
1919 x_return_status => l_return_status
1920 ,x_msg_data => l_msg_data
1921 ,x_msg_count => l_msg_count
1922 ,x_new_txn_temp_id => x_source_task_id
1923 ,p_transaction_temp_id => p_source_task_id
1924 ,p_subinventory_code => l_new_subinventory_code
1925 ,p_locator_id => l_new_locator_id
1926 ,p_to_subinventory_code => l_new_transfer_to_sub
1927 ,p_to_locator_id => l_new_transfer_to_loc
1928 ,p_txn_type_id => l_new_txn_type_id
1929 ,p_txn_action_id => l_new_txn_action_id
1930 ,p_txn_source_type_id => l_new_txn_source_type_id
1931 ,p_wms_task_type => l_new_wms_task_type_id
1932 ,p_lpn_id => l_new_lpn_id
1933 ,p_transfer_lpn_id => fnd_api.g_miss_num -- null out transfer_lpn_id
1934 ,p_content_lpn_id => fnd_api.g_miss_num -- null out content_lpn_id
1935 ,p_transaction_status => 2 -- Bug 5156015
1936 );
1937 END IF;
1938
1939 l_progress := 270;
1940
1941 IF (l_debug=1) THEN
1942 print_debug('After calling inv_trx_util_pub.copy_insert_line_trx: ',l_module_name,4);
1943
1944 print_debug('l_return_status => '||l_return_status,l_module_name,4);
1945 print_debug('l_msg_data => '||l_msg_data,l_module_name,4);
1946 print_debug('l_msg_count => '||l_msg_count,l_module_name,4);
1947 print_debug('x_source_task_id => '||x_source_task_id,l_module_name,4);
1948
1949 END IF;
1950
1951 IF l_return_status <>fnd_api.g_ret_sts_success THEN
1952 IF (l_debug=1) THEN
1953 print_debug('Error occured while calling inv_trx_util_pub.copy_insert_line_trx',l_module_name,4);
1954 END IF;
1955 RAISE FND_API.G_EXC_ERROR;
1956 END IF;
1957
1958 -- Get item lot serial control
1959 -- Need to link MTLT or MSNT to this new MMTT record
1960 -- if item is lot or serial controlled
1961
1962 l_progress := 280;
1963
1964 SELECT lot_control_code,
1965 serial_number_control_code
1966 INTO l_lot_control_code,
1967 l_serial_control_code
1968 FROM mtl_system_items_b
1969 WHERE inventory_item_id = p_document_rec.inventory_item_id
1970 AND organization_id = p_document_rec.organization_id;
1971
1972 l_progress := 290;
1973
1974 IF (l_debug=1) THEN
1975 print_debug('l_lot_control_code = '||l_lot_control_code,l_module_name,4);
1976 print_debug('l_serial_control_code = '||l_serial_control_code,l_module_name,4);
1977 END IF;
1978
1979 IF l_lot_control_code = 2 THEN
1980 IF (l_debug=1) THEN
1981 print_debug('Item is lot controlled, need to link MTLT to new MMTT.',l_module_name,4);
1982 END IF;
1983
1984 l_progress := 300;
1985
1986 UPDATE mtl_transaction_lots_temp
1987 SET transaction_temp_id = x_source_task_id
1988 WHERE transaction_temp_id = p_source_task_id;
1989
1990 l_progress := 310;
1991
1992 ELSIF l_serial_control_code NOT IN (1, 6) THEN
1993 IF (l_debug=1) THEN
1994 print_debug('Item is serial controlled and not lot controlled, need to link MSNT to new MMTT.',l_module_name,4);
1995 END IF;
1996
1997 l_progress := 320;
1998
1999 UPDATE mtl_serial_numbers_temp
2000 SET transaction_temp_id = x_source_task_id
2001 WHERE transaction_temp_id = p_source_task_id;
2002
2003 l_progress := 330;
2004
2005 END IF; --IF l_lot_control_code <> 1
2006
2007 -- At last, delete current MMTT if
2008 -- it is for a receiving transaction.
2009 -- Inventory and WIP putaway will rely on inventory TM
2010 -- to delete the MMTT.
2011
2012 IF (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
2013 AND p_document_rec.transaction_source_type_id = g_sourcetype_purchaseorder
2014 -- PO receipt (1, 27)
2015 ) OR
2016 (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
2017 AND p_document_rec.transaction_source_type_id = g_sourcetype_rma
2018 -- RMA receipt (12, 27)
2019 ) OR
2020 (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
2021 AND p_document_rec.transaction_source_type_id = g_sourcetype_moveorder
2022 -- Consolidated inbound move order receipt (4, 27)
2023 ) OR
2024 (p_document_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
2025 AND p_document_rec.transaction_source_type_id = g_sourcetype_inventory
2026 -- Intransit shipment receipt (13, 12)
2027 ) OR
2028 (p_document_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
2029 AND p_document_rec.transaction_source_type_id = G_SOURCETYPE_INTREQ
2030 ) THEN
2031
2032 IF (l_debug=1) THEN
2033 print_debug('This is a receipt transaction, need to delete MMTT. '||l_return_status,l_module_name,4);
2034 END IF;
2035
2036 IF (l_debug=1) THEN
2037
2038 print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
2039
2040 print_debug('p_transaction_temp_id => '||p_source_task_id,l_module_name,4);
2041 END IF;
2042
2043 inv_trx_util_pub.delete_transaction
2044 (x_return_status => l_return_status
2045 , x_msg_data => l_msg_data
2046 , x_msg_count => l_msg_count
2047 , p_transaction_temp_id => p_source_task_id
2048 , p_update_parent => FALSE
2049 );
2050
2051 IF (l_debug=1) THEN
2052 print_debug('Not last operation - Drop - After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
2053
2054 print_debug('l_return_status => '||l_return_status,l_module_name,4);
2055 print_debug('l_msg_data => '||l_msg_data,l_module_name,4);
2056 print_debug('l_msg_count => '||l_msg_count,l_module_name,4);
2057 END IF;
2058
2059 IF l_return_status <>FND_API.g_ret_sts_success THEN
2060 IF (l_debug=1) THEN
2061 print_debug('Not last operation - Drop - inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
2062 END IF;
2063
2064 RAISE FND_API.G_EXC_ERROR;
2065 END IF;
2066
2067 END IF; -- IF (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
2068
2069
2070 END IF; -- p_operation_type_id = g_op_type_drop
2071
2072 END IF; -- p_is_last_operation_flag = 'Y'
2073
2074
2075 IF p_operation_type_id = g_op_type_drop
2076 AND p_document_rec.transaction_action_id <> g_action_subxfr
2077 THEN
2078 -- Need to revert the suggested capacity of the destination locator
2079 -- Do not need to do this for MO sub transfer - inventory TM handles this.
2080 IF (l_debug=1) THEN
2081 print_debug('Before calling inv_loc_wms_utils.revert_loc_suggested_cap_nauto with parameters:'||l_return_status,l_module_name,4);
2082 print_debug('p_document_rec.organization_id = '|| p_document_rec.organization_id,l_module_name,4);
2083 print_debug('p_document_rec.transfer_to_location = '|| p_document_rec.transfer_to_location,l_module_name,4);
2084 print_debug('p_document_rec.locator_i = '|| p_document_rec.locator_id,l_module_name,4);
2085 print_debug('p_document_rec.inventory_item_id = '|| p_document_rec.inventory_item_id,l_module_name,4);
2086 print_debug('p_document_rec.transaction_uom = '|| p_document_rec.transaction_uom,l_module_name,4);
2087 print_debug('p_document_rec.transaction_quantity = '|| p_document_rec.transaction_quantity ,l_module_name,4);
2088 END IF;
2089
2090 IF (Nvl(p_document_rec.transfer_to_location, p_document_rec.locator_id) IS NOT NULL) THEN
2091 --inv_loc_wms_utils.revert_loc_suggested_cap_nauto commented for bug 5920044.
2092 inv_loc_wms_utils.revert_loc_suggested_capacity --bug#5920044.
2093 (
2094 x_return_status => l_return_status
2095 , x_msg_count => l_msg_count
2096 , x_msg_data => l_msg_data
2097 , p_organization_id => p_document_rec.organization_id
2098 , p_inventory_location_id => Nvl(p_document_rec.transfer_to_location, p_document_rec.locator_id)
2099 , p_inventory_item_id => p_document_rec.inventory_item_id
2100 , p_primary_uom_flag => 'N'
2101 , p_transaction_uom_code => p_document_rec.transaction_uom
2102 , p_quantity => p_document_rec.transaction_quantity
2103 );
2104 END IF;
2105
2106 IF (l_debug=1) THEN
2107 print_debug('Return status is'||l_return_status,l_module_name,9);
2108 END IF;
2109
2110 IF (l_return_status=fnd_api.g_ret_sts_error) THEN
2111 IF (l_debug=1) THEN
2112 print_debug('Error obtained while reverting suggested locator capacity',l_module_name,9);
2113 END IF;
2114 -- Bug 5369010: do not raise exception if locator capacity API fails
2115 -- RAISE FND_API.G_EXC_ERROR;
2116
2117 ELSIF (l_return_status<>fnd_api.g_ret_sts_success) THEN
2118
2119 IF (l_debug=1) THEN
2120 print_debug('unexpected error while reverting suggested locator capacity',l_module_name,9);
2121 END IF;
2122 -- Bug 5369010: do not raise exception if locator capacity API fails
2123 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2124
2125 END IF;
2126
2127 END IF;
2128
2129 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2130
2131 IF (l_debug=1) THEN
2132 print_debug('x_return_status => '||x_return_status,l_module_name,1);
2133 print_debug('x_msg_data => '||x_msg_data,l_module_name,1);
2134 print_debug('x_msg_count => '||x_msg_count,l_module_name,1);
2135 print_debug('x_error_code => '||x_error_code,l_module_name,1);
2136 print_debug('x_source_task_id => '||x_source_task_id,l_module_name,1);
2137
2138 print_debug('Exited. ',l_module_name,1);
2139 END IF;
2140
2141
2142 EXCEPTION
2143 WHEN fnd_api.g_exc_error THEN
2144 IF (l_debug=1) THEN
2145 print_debug('Error (fnd_api.g_exc_error) occured at'||l_progress,l_module_name,1);
2146 END IF;
2147 x_return_status:=FND_API.G_RET_STS_ERROR;
2148 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2149
2150 IF c_mmtt_data_rec%isopen THEN
2151 CLOSE c_mmtt_data_rec;
2152 END IF;
2153
2154
2155 WHEN fnd_api.g_exc_unexpected_error THEN
2156
2157 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2158 IF (l_debug=1) THEN
2159 print_debug('Unexpected Error (fnd_api.g_exc_unexpected_error) occured at '||l_progress,l_module_name,3);
2160 END IF;
2161 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2162 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
2163 END IF;
2164 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2165
2166 IF c_mmtt_data_rec%isopen THEN
2167 CLOSE c_mmtt_data_rec;
2168 END IF;
2169
2170 WHEN OTHERS THEN
2171
2172 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2173 IF (l_debug=1) THEN
2174 print_debug('Other Error occured at '||l_progress,l_module_name,1);
2175 IF SQLCODE IS NOT NULL THEN
2176 print_debug('With SQL error : ' || SQLERRM(SQLCODE), l_module_name,1);
2177 END IF;
2178 END IF;
2179
2180 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2181 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
2182 END IF;
2183 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2184
2185 IF c_mmtt_data_rec%isopen THEN
2186 CLOSE c_mmtt_data_rec;
2187 END IF;
2188
2189 END complete;
2190
2191
2192
2193 /**
2194 * <b> Cleanup </b>:
2195 * <p>This API is the document handler for Inbound document records and is called from
2196 * Cleanup_Operation_Instance and Rollback_Operation_Instance </p>
2197 *
2198 *
2199 * This API clears the destination subinventory, locator, and drop to LPN
2200 * if ATF suggested these data.
2201 *
2202 *
2203 *
2204 *
2205 * @param x_return_status -Return Status
2206 * @param x_msg_data -Returns Message Data
2207 * @param x_msg_count -Returns the message count
2208 * @param p_source_task_id -Identifier of the document record.
2209 *
2210 **/
2211 PROCEDURE cleanup
2212 (
2213 x_return_status OUT NOCOPY VARCHAR2
2214 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
2215 , x_msg_count OUT NOCOPY NUMBER
2216 , p_source_task_id IN NUMBER
2217 )
2218 IS
2219
2220 l_module_name VARCHAR2(30) := 'CLEANUP';
2221 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
2222 l_progress NUMBER;
2223
2224 l_msg_count NUMBER;
2225 l_msg_data VARCHAR2(400);
2226 l_return_status VARCHAR2(1);
2227
2228 CURSOR c_mmtt_data_rec IS
2229 SELECT
2230 transfer_subinventory,
2231 transfer_to_location,
2232 subinventory_code,
2233 locator_id,
2234 cartonization_id,
2235 transaction_action_id,
2236 transaction_source_type_id,
2237 organization_id,
2238 inventory_item_id,
2239 primary_quantity
2240 FROM mtl_material_transactions_temp
2241 WHERE transaction_temp_id = p_source_task_id;
2242
2243 l_mmtt_data_rec c_mmtt_data_rec%ROWTYPE;
2244
2245 l_mol_lpn_id NUMBER;
2246
2247 BEGIN
2248 x_return_status := FND_API.G_RET_STS_SUCCESS;
2249 l_progress := 10;
2250
2251 SAVEPOINT sp_op_inbound_cleanup;
2252
2253 IF (l_debug=1) THEN
2254 print_debug('Entered. ',l_module_name,1);
2255 print_debug('p_source_task_id => '||p_source_task_id,l_module_name,1);
2256 END IF;
2257
2258 l_progress := 20;
2259
2260 OPEN c_mmtt_data_rec;
2261
2262 FETCH c_mmtt_data_rec INTO l_mmtt_data_rec;
2263 IF c_mmtt_data_rec%notfound THEN
2264 IF (l_debug=1) THEN
2265 print_debug('Invalid p_source_task_id : '||p_source_task_id,l_module_name,1);
2266 RAISE fnd_api.G_EXC_ERROR;
2267 END IF;
2268 END IF;
2269
2270 CLOSE c_mmtt_data_rec;
2271
2272 l_progress := 30;
2273
2274 SELECT mol.lpn_id
2275 INTO l_mol_lpn_id
2276 FROM mtl_txn_request_lines mol
2277 WHERE mol.line_id =
2278 (SELECT move_order_line_id
2279 FROM mtl_material_transactions_temp
2280 WHERE transaction_temp_id = p_source_task_id);
2281
2282 l_progress := 34;
2283
2284 IF (l_debug=1) THEN
2285 print_debug('l_mol_lpn_id = '||l_mol_lpn_id,l_module_name,1);
2286 END IF;
2287
2288 IF l_debug=1 THEN
2289 print_debug('Suggested locator capacity of the Rules suggested locaotr needs to be reverted',l_module_name,4);
2290 END IF;
2291
2292 IF (Nvl(l_mmtt_data_rec.transfer_to_location, l_mmtt_data_rec.locator_id) IS NOT NULL) THEN
2293 inv_loc_wms_utils.revert_loc_suggested_cap_nauto
2294 (
2295 x_return_status => l_return_status
2296 , x_msg_count => l_msg_count
2297 , x_msg_data => l_msg_data
2298 , p_organization_id => l_mmtt_data_rec.organization_id
2299 , p_inventory_location_id => Nvl(l_mmtt_data_rec.transfer_to_location, l_mmtt_data_rec.locator_id)
2300 , p_inventory_item_id => l_mmtt_data_rec.inventory_item_id
2301 , p_primary_uom_flag => 'Y'
2302 , p_transaction_uom_code => NULL
2303 , p_quantity => l_mmtt_data_rec.primary_quantity
2304 );
2305 END IF;
2306
2307 l_progress:=35;
2308
2309 IF (l_debug=1) THEN
2310 print_debug('Return status is'||l_return_status,l_module_name,9);
2311 END IF;
2312
2313 IF (l_return_status<>fnd_api.g_ret_sts_success) THEN
2314
2315 IF (l_debug=1) THEN
2316 print_debug('Error obtained while reverting locator capacity',l_module_name,9);
2317 print_debug('Error msg'||x_msg_data,l_module_name,9);
2318 END IF;
2319
2320 END IF;
2321
2322
2323
2324 IF l_mmtt_data_rec.transfer_to_location IS NOT NULL THEN
2325
2326 -- This cleans up drop operation of an inventory or receiving transfer, in which case transfer sub/loc
2327 -- columns are populated.
2328 l_progress := 40;
2329 IF (l_debug=1) THEN
2330 print_debug('Null out transfer_subinventory : ' || l_mmtt_data_rec.transfer_subinventory || ' transfer_to_location : '||l_mmtt_data_rec.transfer_to_location ||' and cartonization_id : '|| l_mmtt_data_rec.cartonization_id,l_module_name,1);
2331 END IF;
2332
2333 -- If MMTT.operation_plan_ID is null we don't need to cleanup
2334 -- transfer_to_location, transfer_subinventory, and cartonization_id,
2335 -- which were NOT suggested by ATF.
2336
2337 UPDATE mtl_material_transactions_temp
2338 SET transfer_subinventory = Decode(operation_plan_id, NULL, transfer_subinventory, NULL),
2339 transfer_to_location = Decode(operation_plan_id, NULL, transfer_to_location, NULL),
2340 cartonization_id = Decode(operation_plan_id, NULL, cartonization_id, NULL),
2341 transfer_lpn_id = NULL,
2342 content_lpn_id = NULL,
2343 lpn_id = l_mol_lpn_id,
2344 wms_task_type = Decode(wms_task_type, -1, g_wms_task_type_putaway, wms_task_type) -- revert it back to putaway if it has been set to -1 from complete_putaway
2345 WHERE transaction_temp_id = p_source_task_id;
2346
2347 l_progress := 50;
2348
2349 ELSE -- IF l_mmtt_data_rec.transfer_to_location IS NULL
2350
2351 -- This cleans up drop operation of a receiving deliver, in which case sub/loc
2352 -- columns are not populated.
2353 -- We shouldn't cleanup sub/loc fields if this is an inventory transfer
2354
2355 IF l_mmtt_data_rec.transaction_action_id <> 2 THEN
2356
2357 l_progress := 60;
2358 IF (l_debug=1) THEN
2359 print_debug('Null out subinventory_code : ' || l_mmtt_data_rec.subinventory_code || ' locator_id : '||l_mmtt_data_rec.locator_id ||' and cartonization_id : '|| l_mmtt_data_rec.cartonization_id,l_module_name,1);
2360 END IF;
2361
2362 -- If MMTT.operation_plan_ID is null we don't need to cleanup
2363 -- locator_ID, subinventory_code, and cartonization_id,
2364 -- which were NOT suggested by ATF.
2365
2366 UPDATE mtl_material_transactions_temp
2367 SET subinventory_code = Decode(operation_plan_id, NULL, subinventory_code, NULL),
2368 locator_id = Decode(operation_plan_id, NULL, locator_id, NULL),
2369 cartonization_id = Decode(operation_plan_id, NULL, cartonization_id, NULL),
2370 transfer_lpn_id = NULL,
2371 content_lpn_id = NULL,
2372 lpn_id = l_mol_lpn_id,
2373 wms_task_type = Decode(wms_task_type, -1, g_wms_task_type_putaway, wms_task_type) -- revert it back to putaway if it has been set to -1 from complete_putaway
2374 WHERE transaction_temp_id = p_source_task_id;
2375
2376 l_progress := 70;
2377 END IF; -- IF l_mmtt_data_rec.transaction_action_id <> 2
2378
2379 END IF; -- IF l_mmtt_data_rec.transfer_to_location IS NOT NULL
2380
2381 IF (l_debug=1) THEN
2382 print_debug('Before calling wms_task_dispatch_put_away.putaway_cleanup with following parameters: ',l_module_name,4);
2383 print_debug('p_temp_id => '|| p_source_task_id,l_module_name,4);
2384 print_debug('p_org_id => '|| l_mmtt_data_rec.organization_id,l_module_name,4);
2385 END IF;
2386
2387 wms_task_dispatch_put_away.putaway_cleanup
2388 ( p_temp_id => p_source_task_id
2389 , p_org_id => l_mmtt_data_rec.organization_id
2390 , x_return_status => l_return_status
2391 , x_msg_count => l_msg_count
2392 , x_msg_data => l_msg_data
2393 );
2394
2395 l_progress := 80;
2396
2397 IF (l_debug=1) THEN
2398 print_debug('After calling wms_task_dispatch_put_away.putaway_cleanup. ',l_module_name,4);
2399 print_debug('x_return_status => '|| l_return_status,l_module_name,4);
2400 print_debug('x_msg_count => '|| l_msg_count,l_module_name,4);
2401 print_debug('x_msg_data => '|| l_msg_data,l_module_name,4);
2402 END IF;
2403
2404 IF l_return_status <>FND_API.g_ret_sts_success THEN
2405 IF (l_debug=1) THEN
2406 print_debug('wms_task_dispatch_put_away.putaway_cleanup finished with error. l_return_status = ' || l_return_status,l_module_name,4);
2407 END IF;
2408
2409 RAISE FND_API.G_EXC_ERROR;
2410 END IF;
2411
2412
2413 IF (l_debug=1) THEN
2414 print_debug('x_return_status => '||x_return_status,l_module_name,1);
2415 print_debug('x_msg_data => '||x_msg_data,l_module_name,1);
2416 print_debug('x_msg_count => '||x_msg_count,l_module_name,1);
2417
2418 print_debug('Exited. ',l_module_name,1);
2419 END IF;
2420
2421 EXCEPTION
2422 WHEN fnd_api.g_exc_error THEN
2423 IF (l_debug=1) THEN
2424 print_debug('Error (fnd_api.g_exc_error) occured at'||l_progress,l_module_name,1);
2425 END IF;
2426 x_return_status:=FND_API.G_RET_STS_ERROR;
2427 IF c_mmtt_data_rec%isopen THEN
2428 CLOSE c_mmtt_data_rec;
2429 END IF;
2430
2431 ROLLBACK TO sp_op_inbound_cleanup;
2432
2433
2434 WHEN fnd_api.g_exc_unexpected_error THEN
2435
2436 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2437 IF (l_debug=1) THEN
2438 print_debug('Unexpected Error (fnd_api.g_exc_unexpected_error) occured at '||l_progress,l_module_name,3);
2439 END IF;
2440 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2441 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
2442 END IF;
2443 IF c_mmtt_data_rec%isopen THEN
2444 CLOSE c_mmtt_data_rec;
2445 END IF;
2446
2447 ROLLBACK TO sp_op_inbound_cleanup;
2448
2449 WHEN OTHERS THEN
2450
2451 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2452 IF (l_debug=1) THEN
2453 print_debug('Other Error occured at '||l_progress,l_module_name,1);
2454 IF SQLCODE IS NOT NULL THEN
2455 print_debug('With SQL error : ' || SQLERRM(SQLCODE), l_module_name,1);
2456 END IF;
2457 END IF;
2458 IF c_mmtt_data_rec%isopen THEN
2459 CLOSE c_mmtt_data_rec;
2460 END IF;
2461
2462 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2463 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
2464 END IF;
2465
2466 ROLLBACK TO sp_op_inbound_cleanup;
2467
2468 END cleanup;
2469
2470
2471
2472 /**
2473 * <b> Cancel </b>:
2474 * <p>This API is the document handler for Inbound document records and is called from
2475 * Cancel_Operation_Plan </p>
2476 *
2477 *
2478 * This API deletes the parent MMTT record, deletes the child MMTT record,
2479 * update and close the move order line as appropriate.
2480 *
2481 *
2482 * @param x_return_status -Return Status
2483 * @param x_msg_data -Returns Message Data
2484 * @param x_msg_count -Returns the message count
2485 * @param p_source_task_id -Identifier of the document record.
2486 *
2487 **/
2488 PROCEDURE cancel
2489 (
2490 x_return_status OUT NOCOPY VARCHAR2
2491 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
2492 , x_msg_count OUT NOCOPY NUMBER
2493 , p_source_task_id IN NUMBER
2494 , p_retain_mmtt IN VARCHAR2 DEFAULT 'N'
2495 , p_mmtt_error_code IN VARCHAR2 DEFAULT NULL
2496 , p_mmtt_error_explanation IN VARCHAR2 DEFAULT NULL
2497 )
2498 IS
2499 l_module_name VARCHAR2(30) := 'CANCEL';
2500 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
2501 l_progress NUMBER;
2502 l_return_status VARCHAR2(1);
2503 l_msg_count NUMBER;
2504 l_msg_data VARCHAR2(400);
2505
2506 l_qty_in_mol_uom NUMBER;
2507 l_subinventory_code VARCHAR2(10);
2508 l_locator_id NUMBER;
2509
2510 CURSOR c_mmtt_data_rec IS
2511 SELECT
2512 mmtt.transaction_temp_id,
2513 mmtt.move_order_line_id,
2514 mmtt.parent_line_id,
2515 mmtt.transaction_action_id,
2516 mmtt.transaction_source_type_id,
2517 mmtt.transaction_uom,
2518 mmtt.transaction_quantity,
2519 mmtt.inventory_item_id,
2520 mol.uom_code mol_uom_code,
2521 mol.backorder_delivery_detail_id,
2522 mol.crossdock_type,
2523 mmtt.repetitive_line_id,
2524 mmtt.operation_seq_num,
2525 mmtt.primary_quantity
2526 FROM mtl_material_transactions_temp mmtt,
2527 mtl_txn_request_lines mol
2528 WHERE transaction_temp_id = p_source_task_id
2529 AND mmtt.move_order_line_id = mol.line_id;
2530
2531 CURSOR c_parent_mmtt_rec(v_txn_temp_id NUMBER) IS
2532 SELECT
2533 nvl(transfer_to_location,locator_id) locator_id,
2534 primary_quantity,
2535 organization_id,
2536 inventory_item_id
2537 FROM mtl_material_transactions_temp
2538 WHERE transaction_temp_id=v_txn_temp_id;
2539
2540
2541 CURSOR c_wdt_status IS
2542 SELECT status
2543 FROM wms_dispatched_tasks
2544 WHERE transaction_temp_id=p_source_task_id
2545 AND task_type=g_wms_task_type_putaway;
2546
2547 l_mmtt_data_rec c_mmtt_data_rec%ROWTYPE;
2548 l_parent_mmtt_rec c_parent_mmtt_rec%ROWTYPE;
2549 l_wdt_status NUMBER:=0;
2550
2551 BEGIN
2552
2553 x_return_status := FND_API.G_RET_STS_SUCCESS;
2554 l_progress := 10;
2555
2556 SAVEPOINT sp_op_inbound_cancel;
2557
2558 IF (l_debug=1) THEN
2559 print_debug('Entered. ',l_module_name,1);
2560 print_debug('p_source_task_id => '||p_source_task_id,l_module_name,1);
2561 print_debug('p_retain_mmtt => '||p_retain_mmtt,l_module_name,1);
2562 print_debug('p_mmtt_error_explanation => '||p_mmtt_error_explanation,l_module_name,1);
2563 print_debug('p_mmtt_error_code => '||p_mmtt_error_code,l_module_name,1);
2564 END IF;
2565
2566 l_progress := 20;
2567
2568
2569 OPEN c_mmtt_data_rec;
2570
2571 FETCH c_mmtt_data_rec INTO l_mmtt_data_rec;
2572 IF c_mmtt_data_rec%notfound THEN
2573 IF (l_debug=1) THEN
2574 print_debug('Invalid p_source_task_id : '||p_source_task_id,l_module_name,1);
2575 RAISE fnd_api.G_EXC_ERROR;
2576 END IF;
2577 END IF;
2578
2579 CLOSE c_mmtt_data_rec;
2580
2581 OPEN c_parent_mmtt_rec(l_mmtt_data_rec.parent_line_id);
2582
2583 FETCH c_parent_mmtt_rec INTO l_parent_mmtt_rec;
2584
2585 IF c_parent_mmtt_rec%NOTFOUND THEN
2586
2587 IF (l_debug=1) THEN
2588 print_debug('No parent MMTT record found',l_module_name,9);
2589 END IF;
2590
2591 CLOSE c_parent_mmtt_rec;
2592
2593 RAISE fnd_api.G_EXC_ERROR;
2594
2595 END IF;
2596
2597 CLOSE c_parent_mmtt_rec;
2598
2599
2600 l_progress := 30;
2601
2602 IF (l_debug=1) THEN
2603 print_debug('l_mmtt_data_rec.transaction_temp_id = '|| l_mmtt_data_rec.transaction_temp_id,l_module_name,4);
2604 print_debug('l_mmtt_data_rec.move_order_line_id = '|| l_mmtt_data_rec.move_order_line_id,l_module_name,4);
2605 print_debug('l_mmtt_data_rec.parent_line_id = '|| l_mmtt_data_rec.parent_line_id,l_module_name,4);
2606 print_debug('l_mmtt_data_rec.transaction_action_id = '|| l_mmtt_data_rec.transaction_action_id,l_module_name,4);
2607 print_debug('l_mmtt_data_rec.transaction_source_type_id = '|| l_mmtt_data_rec.transaction_source_type_id,l_module_name,4);
2608 print_debug('l_mmtt_data_rec.parent_line_id = '|| l_mmtt_data_rec.parent_line_id,l_module_name,4);
2609 print_debug('l_mmtt_data_rec.backorder_delivery_detail_id = '|| l_mmtt_data_rec.backorder_delivery_detail_id,l_module_name,4);
2610
2611
2612 print_debug('Delete parent - Before calling INV_TRX_UTIL_PUB.Delete_transaction with following parameters: ' ,l_module_name,4);
2613 print_debug('p_transaction_temp_id => '|| l_mmtt_data_rec.parent_line_id,l_module_name,4);
2614 print_debug('p_update_parent => '|| 'FALSE',l_module_name,4);
2615
2616 END IF;
2617
2618 l_progress := 40;
2619
2620 -- delete parent mmtt record
2621
2622 INV_TRX_UTIL_PUB.Delete_transaction
2623 (x_return_status => l_return_status,
2624 x_msg_data => l_msg_data,
2625 x_msg_count => l_msg_count,
2626 p_transaction_temp_id => l_mmtt_data_rec.parent_line_id,
2627 p_update_parent => FALSE);
2628
2629 l_progress := 50;
2630
2631 IF (l_debug=1) THEN
2632 print_debug('Delete parent - After calling INV_TRX_UTIL_PUB.Delete_transaction.' ,l_module_name,4);
2633 print_debug('x_return_status => '|| l_return_status,l_module_name,4);
2634 print_debug('x_msg_data => '|| l_msg_data,l_module_name,4);
2635 print_debug('x_msg_count => '|| l_msg_count,l_module_name,4);
2636 END IF;
2637
2638 IF l_return_status <>FND_API.g_ret_sts_success THEN
2639 IF (l_debug=1) THEN
2640 print_debug('Delete parent - inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
2641 END IF;
2642
2643 RAISE FND_API.G_EXC_ERROR;
2644 END IF;
2645
2646
2647 -- Need to convert MMTT transaction quantity to the UOM of MOL if UOM
2648 -- does not match on MMTT and MOL
2649
2650 l_qty_in_mol_uom := l_mmtt_data_rec.transaction_quantity;
2651
2652 IF l_mmtt_data_rec.transaction_uom <> l_mmtt_data_rec.mol_uom_code THEN
2653 IF (l_debug=1) THEN
2654 print_debug('MMTT and MOL UOM do not match, need to convert quantity.',l_module_name,4);
2655 print_debug('Before calling INV_Convert.inv_um_convert with parameters:', l_module_name,4);
2656 print_debug('item_id => '||l_mmtt_data_rec.inventory_item_id, l_module_name,4);
2657 print_debug('from_quantity => '||l_mmtt_data_rec.transaction_quantity, l_module_name,4);
2658 print_debug('from_unit => '||l_mmtt_data_rec.transaction_uom, l_module_name,4);
2659 print_debug('to_unit => '||l_mmtt_data_rec.mol_uom_code, l_module_name,4);
2660 END IF;
2661
2662 l_progress := 60;
2663
2664 l_qty_in_mol_uom := INV_Convert.inv_um_convert
2665 (item_id => l_mmtt_data_rec.inventory_item_id,
2666 precision => null,
2667 from_quantity => l_mmtt_data_rec.transaction_quantity,
2668 from_unit => l_mmtt_data_rec.transaction_uom,
2669 to_unit => l_mmtt_data_rec.mol_uom_code,
2670 from_name => null,
2671 to_name => null);
2672
2673 l_progress := 70;
2674
2675 IF (l_debug=1) THEN
2676 print_debug(' After calling INV_Convert.inv_um_convert. l_qty_in_mol_uom = '||l_qty_in_mol_uom, l_module_name,4);
2677
2678 END IF;
2679
2680 END IF; -- IF l_mmtt_data_rec.transaction_uom <> l_mmtt_data_rec.mol_uom_code
2681
2682 OPEN c_wdt_status;
2683
2684 FETCH c_wdt_status INTO l_wdt_status;
2685
2686 CLOSE c_wdt_status;
2687
2688 IF (l_mmtt_data_rec.transaction_action_id = G_ACTION_RECEIPT
2689 AND l_mmtt_data_rec.transaction_source_type_id = g_sourcetype_purchaseorder
2690 -- PO receipt (1, 27)
2691 ) OR
2692 (l_mmtt_data_rec.transaction_action_id = G_ACTION_RECEIPT
2693 AND l_mmtt_data_rec.transaction_source_type_id = g_sourcetype_rma
2694 -- RMA receipt (12, 27)
2695 ) OR
2696 (l_mmtt_data_rec.transaction_action_id = G_ACTION_RECEIPT
2697 AND l_mmtt_data_rec.transaction_source_type_id = g_sourcetype_moveorder
2698 -- Consolidated inbound move order receipt (4, 27)
2699 ) OR
2700 (l_mmtt_data_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
2701 AND l_mmtt_data_rec.transaction_source_type_id = g_sourcetype_inventory
2702 -- Intransit shipment receipt (13, 12)
2703 ) OR
2704 (l_mmtt_data_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
2705 AND l_mmtt_data_rec.transaction_source_type_id = G_SOURCETYPE_INTREQ
2706 ) THEN
2707
2708 -- The MMTT being cancelled is a receiving deliver or receiving transfer,
2709 -- therefore shouldn't close move order line, only update quantity_detailed.
2710
2711 IF (l_debug=1) THEN
2712 print_debug('This is a receipt transaction, need NOT close MOL. '||l_return_status,l_module_name,4);
2713 END IF;
2714
2715 l_progress := 80;
2716
2717 IF p_retain_mmtt = 'Y' AND l_wdt_status = g_task_status_loaded THEN
2718 IF (l_debug=1) THEN
2719 print_debug('Task is loaded... do not update quantity_detailed. '||l_return_status,l_module_name,4);
2720 END IF;
2721 ELSE
2722 UPDATE mtl_txn_request_lines
2723 SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom
2724 WHERE line_id = l_mmtt_data_rec.move_order_line_id;
2725
2726 IF (l_debug=1) THEN
2727 print_debug('Updated quantity_detailed. '||l_return_status,l_module_name,4);
2728 END IF;
2729 END IF;
2730
2731 l_progress := 90;
2732
2733 ELSE
2734 -- The MMTT being cancelled is an inventory transfer,
2735 -- therefore need to close move order line, and update quantity_detailed.
2736
2737 IF (l_debug=1) THEN
2738 print_debug('This is an inventory transfer transaction, need to close MOL. '||l_return_status,l_module_name,4);
2739 END IF;
2740
2741 l_progress := 100;
2742
2743 UPDATE mtl_txn_request_lines
2744 SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom,
2745 line_status = g_to_status_closed
2746 WHERE line_id = l_mmtt_data_rec.move_order_line_id;
2747
2748 l_progress := 110;
2749
2750 END IF; -- IF (p_document_rec.transaction_action_id = G_ACTION_RECEIPT
2751
2752
2753 IF p_retain_mmtt = 'Y' AND l_wdt_status = g_task_status_loaded THEN
2754 --{{
2755 -- when cancel operation plan caused by change of management
2756 -- retain child MMTT, update MMTT's sub/loc/error, putaway page will
2757 -- handle that later.
2758 --}}
2759 IF (l_debug=1) THEN
2760 print_debug('Do not delete child MMTT, retain and update sub/loc/error',l_module_name,4);
2761 END IF;
2762
2763 l_subinventory_code := NULL;
2764 l_locator_id := NULL;
2765 BEGIN
2766 SELECT wlpn.subinventory_code
2767 , wlpn.locator_id
2768 INTO l_subinventory_code
2769 , l_locator_id
2770 FROM wms_license_plate_numbers wlpn
2771 , mtl_material_transactions_temp mmtt
2772 WHERE mmtt.lpn_id = wlpn.lpn_id
2773 AND mmtt.transaction_temp_id = p_source_task_id;
2774 EXCEPTION
2775 WHEN OTHERS THEN
2776 l_subinventory_code := NULL;
2777 l_locator_id := NULL;
2778 END;
2779
2780 IF l_subinventory_code IS NOT NULL
2781 AND l_locator_id IS NOT NULL THEN
2782 IF l_debug = 1 THEN
2783 print_debug('Updating MMTT:'||l_subinventory_code||':'||l_locator_id,l_module_name,4);
2784 END IF;
2785 UPDATE mtl_material_transactions_temp mmtt
2786 SET (subinventory_code,
2787 locator_id,
2788 error_code,
2789 error_explanation,
2790 parent_line_id,
2791 operation_plan_id) =
2792 (SELECT
2793 l_subinventory_code,
2794 l_locator_id,
2795 p_mmtt_error_code,
2796 p_mmtt_error_explanation,
2797 NULL,
2798 NULL
2799 FROM dual
2800 )
2801 WHERE mmtt.transaction_temp_id = p_source_task_id;
2802 ELSE
2803 IF l_debug = 1 THEN
2804 print_debug('Sub/Loc cannot be null for loaded LPN:'||l_subinventory_code||':'||l_locator_id,l_module_name,4);
2805 END IF;
2806 fnd_message.set_name('WMS','WMS_UNLD_TASK_INFO_ERR');
2807 fnd_msg_pub.ADD;
2808 RAISE fnd_api.g_exc_error;
2809 END IF;
2810 ELSE
2811 IF (l_debug=1) THEN
2812 print_debug('Delete child - Before calling INV_TRX_UTIL_PUB.Delete_transaction with following parameters: ' ,l_module_name,4);
2813 print_debug('p_transaction_temp_id => '|| p_source_task_id,l_module_name,4);
2814 print_debug('p_update_parent => '|| 'FALSE',l_module_name,4);
2815
2816 END IF;
2817
2818 l_progress := 120;
2819
2820 -- delete current mmtt record
2821
2822 INV_TRX_UTIL_PUB.Delete_transaction
2823 (x_return_status => l_return_status,
2824 x_msg_data => l_msg_data,
2825 x_msg_count => l_msg_count,
2826 p_transaction_temp_id => p_source_task_id,
2827 p_update_parent => FALSE);
2828
2829 l_progress := 130;
2830
2831 IF (l_debug=1) THEN
2832 print_debug('Delete child - After calling INV_TRX_UTIL_PUB.Delete_transaction.' ,l_module_name,4);
2833 print_debug('x_return_status => '|| l_return_status,l_module_name,4);
2834 print_debug('x_msg_data => '|| l_msg_data,l_module_name,4);
2835 print_debug('x_msg_count => '|| l_msg_count,l_module_name,4);
2836 END IF;
2837
2838 IF l_return_status <>FND_API.g_ret_sts_success THEN
2839 IF (l_debug=1) THEN
2840 print_debug('Delete child - inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
2841 END IF;
2842
2843 RAISE FND_API.G_EXC_ERROR;
2844 END IF;
2845
2846 END IF;
2847
2848 IF l_debug=1 THEN
2849 print_debug('Suggested locator capacity of the Rules suggested locaotr needs to be reverted',l_module_name,9);
2850 END IF;
2851
2852 IF (l_parent_mmtt_rec.locator_id IS NOT NULL) THEN
2853 inv_loc_wms_utils.revert_loc_suggested_cap_nauto
2854 (
2855 x_return_status => l_return_status
2856 , x_msg_count => l_msg_count
2857 , x_msg_data => l_msg_data
2858 , p_organization_id => l_parent_mmtt_rec.organization_id
2859 , p_inventory_location_id => l_parent_mmtt_rec.locator_id
2860 , p_inventory_item_id => l_parent_mmtt_rec.inventory_item_id
2861 , p_primary_uom_flag => 'Y'
2862 , p_transaction_uom_code => NULL
2863 , p_quantity => l_parent_mmtt_rec.primary_quantity
2864 );
2865 END IF;
2866
2867 l_progress:=110;
2868
2869 IF (l_debug=1) THEN
2870 print_debug('Return status is'||l_return_status,l_module_name,9);
2871 END IF;
2872
2873 IF (l_return_status<>fnd_api.g_ret_sts_success) THEN
2874
2875 IF (l_debug=1) THEN
2876 print_debug('Error while reverting locator capacity',l_module_name,9);
2877 print_debug('Error message'||l_msg_data,l_module_name,9);
2878 END IF;
2879
2880
2881 END IF;
2882
2883 --{{
2884 -- Cancel operation plan should revert the crossdocking, i.e. update shipping,
2885 -- update MOL, remove reservation.
2886 --}}
2887
2888 IF l_mmtt_data_rec.backorder_delivery_detail_id IS NOT NULL THEN
2889 IF (l_debug=1) THEN
2890 print_debug('Before calling revert_crossdock with following params:', l_module_name,4);
2891 print_debug('p_move_order_line_id => '|| l_mmtt_data_rec.move_order_line_id, l_module_name,4);
2892 print_debug('p_move_order_line_id => '|| l_mmtt_data_rec.move_order_line_id, l_module_name,4);
2893 print_debug('p_crossdock_type => '|| l_mmtt_data_rec.crossdock_type, l_module_name,4);
2894 print_debug('p_backorder_delivery_detail_id => '|| l_mmtt_data_rec.backorder_delivery_detail_id, l_module_name,4);
2895 print_debug('p_repetitive_line_id => '|| l_mmtt_data_rec.repetitive_line_id, l_module_name,4);
2896 print_debug('p_operation_seq_number => '|| l_mmtt_data_rec.operation_seq_num, l_module_name,4);
2897 print_debug('p_inventory_item_id => '|| l_mmtt_data_rec.inventory_item_id, l_module_name,4);
2898 print_debug('p_primary_quantity => '|| l_mmtt_data_rec.primary_quantity, l_module_name,4);
2899 END IF;
2900
2901 revert_crossdock
2902 (x_return_status => l_return_status
2903 , x_msg_count => l_msg_count
2904 , x_msg_data => l_msg_data
2905 , p_move_order_line_id => l_mmtt_data_rec.move_order_line_id
2906 , p_crossdock_type => l_mmtt_data_rec.crossdock_type
2907 , p_backorder_delivery_detail_id => l_mmtt_data_rec.backorder_delivery_detail_id
2908 , p_repetitive_line_id => l_mmtt_data_rec.repetitive_line_id
2909 , p_operation_seq_number => l_mmtt_data_rec.operation_seq_num
2910 , p_inventory_item_id => l_mmtt_data_rec.inventory_item_id
2911 , p_primary_quantity => l_mmtt_data_rec.primary_quantity
2912 );
2913
2914 IF l_return_status <>FND_API.g_ret_sts_success THEN
2915 IF (l_debug=1) THEN
2916 print_debug('revert_crossdock finished with error. l_return_status = ' || l_return_status,l_module_name,4);
2917 END IF;
2918
2919 RAISE FND_API.G_EXC_ERROR;
2920 END IF;
2921
2922 END IF;
2923
2924 IF (l_debug=1) THEN
2925 print_debug('x_return_status => '||x_return_status,l_module_name,1);
2926 print_debug('x_msg_data => '||x_msg_data,l_module_name,1);
2927 print_debug('x_msg_count => '||x_msg_count,l_module_name,1);
2928
2929 print_debug('Exited. ',l_module_name,1);
2930 END IF;
2931
2932 EXCEPTION
2933 WHEN fnd_api.g_exc_error THEN
2934 IF (l_debug=1) THEN
2935 print_debug('Error (fnd_api.g_exc_error) occured at'||l_progress,l_module_name,1);
2936 END IF;
2937 x_return_status:=FND_API.G_RET_STS_ERROR;
2938 ROLLBACK TO sp_op_inbound_cancel;
2939 IF c_mmtt_data_rec%isopen THEN
2940 CLOSE c_mmtt_data_rec;
2941 END IF;
2942
2943
2944 WHEN fnd_api.g_exc_unexpected_error THEN
2945
2946 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2947 IF (l_debug=1) THEN
2948 print_debug('Unexpected Error (fnd_api.g_exc_unexpected_error) occured at '||l_progress,l_module_name,3);
2949 END IF;
2950 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2951 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
2952 END IF;
2953 ROLLBACK TO sp_op_inbound_cancel;
2954 IF c_mmtt_data_rec%isopen THEN
2955 CLOSE c_mmtt_data_rec;
2956 END IF;
2957
2958 WHEN OTHERS THEN
2959
2960 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2961 IF (l_debug=1) THEN
2962 print_debug('Other Error occured at '||l_progress,l_module_name,1);
2963 IF SQLCODE IS NOT NULL THEN
2964 print_debug('With SQL error : ' || SQLERRM(SQLCODE), l_module_name,1);
2965 END IF;
2966 END IF;
2967
2968 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2969 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
2970 END IF;
2971
2972 ROLLBACK TO sp_op_inbound_cancel;
2973 IF c_mmtt_data_rec%isopen THEN
2974 CLOSE c_mmtt_data_rec;
2975 END IF;
2976
2977 END cancel;
2978
2979
2980 /**
2981 * <b> Abort </b>:
2982 * <p> This API is the document handler for Inbound document records and is called from
2983 * Abort_Operation_Plan </p>
2984 *
2985 *
2986 * This API deletes the parent MMTT record, clear several fields of the child MMTT record,
2987 *
2988 *
2989 * @param x_return_status -Return Status
2990 * @param x_msg_data -Returns Message Data
2991 * @param x_msg_count -Returns the message count
2992 * @param p_source_task_id -Identifier of the document record.
2993 * @param p_document_rec -MMTT PL/SQL record
2994 *
2995 **/
2996 PROCEDURE ABORT
2997 (
2998 x_return_status OUT NOCOPY VARCHAR2
2999 , x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
3000 , x_msg_count OUT NOCOPY NUMBER
3001 , p_document_rec IN mtl_material_transactions_temp%ROWTYPE
3002 , p_plan_orig_sub_code IN VARCHAR2
3003 , p_plan_orig_loc_id IN NUMBER
3004 , p_for_manual_drop IN BOOLEAN DEFAULT FALSE
3005 )
3006 IS
3007 l_progress NUMBER;
3008 l_module_name VARCHAR2(30) := 'ABORT';
3009 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
3010 l_return_status VARCHAR2(1);
3011 l_msg_count NUMBER;
3012 l_msg_data VARCHAR2(400);
3013
3014 BEGIN
3015
3016 x_return_status := FND_API.G_RET_STS_SUCCESS;
3017 l_progress := 10;
3018 IF (l_debug=1) THEN
3019 print_debug('Entered. ',l_module_name,1);
3020 print_debug('p_document_rec.transaction_temp_id => ' ||p_document_rec.transaction_temp_id ,l_module_name,1);
3021 print_debug('p_document_rec.parent_line_id => ' ||p_document_rec.parent_line_id ,l_module_name,1);
3022 print_debug('p_document_rec.locator_id => ' ||p_document_rec.locator_id ,l_module_name,1);
3023 print_debug('p_document_rec.transfer_to_location => ' ||p_document_rec.transfer_to_location ,l_module_name,1);
3024 print_debug('p_document_rec.organization_id => '||p_document_rec.organization_id,l_module_name,4);
3025 print_debug('p_document_rec.primary_quantity => '||p_document_rec.primary_quantity,l_module_name,4);
3026 print_debug('p_document_rec.inventory_item_id => '||p_document_rec.inventory_item_id,l_module_name,4);
3027 print_debug('p_plan_orig_sub_code => ' || p_plan_orig_sub_code,l_module_name,1);
3028 print_debug('p_plan_orig_loc_id => ' || p_plan_orig_loc_id,l_module_name,1);
3029 IF(p_for_manual_drop = TRUE) THEN
3030 print_debug('p_for_manual_drop => TRUE',l_module_name, 4);
3031 ELSE
3032 print_debug('p_for_manual_drop => FALSE',l_module_name,4);
3033 END IF;
3034
3035 END IF;
3036
3037 SAVEPOINT sp_op_inbound_abort;
3038
3039 l_progress:=20;
3040 /* null out the Operation Plan Id ,parent Line Id and delete the Parent transaction temp Id*/
3041 /*Call table hanlder to delete MMTT/MSNT/MTLT for the Parent MMTT*/
3042 INV_TRX_UTIL_PUB.Delete_transaction
3043 (x_return_status => x_return_status,
3044 x_msg_data => x_msg_data,
3045 x_msg_count => x_msg_count,
3046 p_transaction_temp_id => p_document_rec.parent_line_id,
3047 p_update_parent => FALSE);
3048
3049 IF (l_debug=1) THEN
3050 print_debug('Return status from Delete Transaction'||x_return_status,l_module_name,9);
3051 END IF;
3052
3053
3054 IF (x_return_status<>FND_API.G_RET_STS_SUCCESS) THEN
3055
3056 RAISE FND_API.G_EXC_ERROR;
3057 END IF;
3058
3059 l_progress:=30;
3060
3061
3062 IF (p_plan_orig_loc_id IS NOT NULL) THEN
3063
3064 IF l_debug=1 THEN
3065 print_debug('Call inv_loc_wms_utils.revert_loc_suggested_cap_nauto to revert capactity:',l_module_name,4);
3066 print_debug('p_organization_id => '||p_document_rec.organization_id,l_module_name,4);
3067 print_debug('p_inventory_location_id => '||p_plan_orig_loc_id,l_module_name,4);
3068 print_debug('p_inventory_item_id => '||p_document_rec.inventory_item_id,l_module_name,4);
3069 print_debug('p_quantity => '||p_document_rec.primary_quantity,l_module_name,4);
3070 print_debug('p_primary_uom_flag => '|| 'Y',l_module_name,4);
3071 print_debug('p_transaction_uom_code => '|| '',l_module_name,4);
3072
3073 END IF;
3074
3075 IF(p_for_manual_drop = TRUE) THEN
3076 IF l_debug=1 THEN
3077 print_debug('inv_loc_wms_utils.Call revert_loc_suggested_capacity',l_module_name,4);
3078 END IF;
3079 inv_loc_wms_utils.revert_loc_suggested_capacity
3080 (
3081 x_return_status => l_return_status
3082 , x_msg_count => l_msg_count
3083 , x_msg_data => l_msg_data
3084 , p_organization_id => p_document_rec.organization_id
3085 , p_inventory_location_id => p_plan_orig_loc_id
3086 , p_inventory_item_id => p_document_rec.inventory_item_id
3087 , p_primary_uom_flag => 'Y'
3088 , p_transaction_uom_code => NULL
3089 , p_quantity => p_document_rec.primary_quantity
3090 );
3091 ELSE
3092 IF l_debug=1 THEN
3093 print_debug('inv_loc_wms_utils.Call revert_loc_suggested_cap_nauto',l_module_name,4);
3094 END IF;
3095 inv_loc_wms_utils.revert_loc_suggested_cap_nauto
3096 (
3097 x_return_status => l_return_status
3098 , x_msg_count => l_msg_count
3099 , x_msg_data => l_msg_data
3100 , p_organization_id => p_document_rec.organization_id
3101 , p_inventory_location_id => p_plan_orig_loc_id
3102 , p_inventory_item_id => p_document_rec.inventory_item_id
3103 , p_primary_uom_flag => 'Y'
3104 , p_transaction_uom_code => NULL
3105 , p_quantity => p_document_rec.primary_quantity
3106 );
3107
3108 END IF;
3109
3110 IF l_debug=1 THEN
3111 print_debug('After calling inv_loc_wms_utils.revert_loc_suggested_cap_nauto:',l_module_name,4);
3112 print_debug('x_return_status => '||l_return_status,l_module_name,4);
3113 print_debug('x_msg_data => '||l_msg_data,l_module_name,4);
3114 print_debug('x_msg_count => '||l_msg_count,l_module_name,4);
3115 END IF;
3116
3117 IF l_return_status <>FND_API.g_ret_sts_success THEN
3118 IF (l_debug=1) THEN
3119 print_debug('inv_loc_wms_utils.revert_loc_suggested_cap_nauto finished with error. l_return_status = ' || l_return_status,l_module_name,4);
3120 END IF;
3121
3122 -- Bug 5369010: do not raise exception if locator capacity API fails
3123 -- RAISE FND_API.G_EXC_ERROR;
3124 END IF;
3125
3126 END IF;
3127
3128
3129
3130 IF (l_debug=1) THEN
3131 print_debug('Updating parent Line id and Operation plan id for Src Document record',l_module_name,9);
3132 print_debug('Also update destination sub/loc Src Document record to that of the original task. ',l_module_name,9);
3133 END IF;
3134
3135 IF p_document_rec.locator_id IS NULL THEN
3136
3137 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
3138 SET operation_plan_id = NULL ,
3139 parent_line_id = NULL,
3140 subinventory_code = p_plan_orig_sub_code,
3141 locator_id = p_plan_orig_loc_id
3142 WHERE transaction_temp_id = p_document_rec.transaction_temp_id;
3143
3144 ELSIF p_document_rec.transfer_to_location IS NULL THEN
3145
3146 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
3147 SET operation_plan_id = NULL ,
3148 parent_line_id = NULL,
3149 transfer_subinventory = p_plan_orig_sub_code,
3150 transfer_to_location = p_plan_orig_loc_id
3151 WHERE transaction_temp_id = p_document_rec.transaction_temp_id;
3152
3153 END IF;
3154
3155
3156 IF (l_debug=1) THEN
3157 print_debug('x_return_status => '||x_return_status,l_module_name,1);
3158 print_debug('x_msg_data => '||x_msg_data,l_module_name,1);
3159 print_debug('x_msg_count => '||x_msg_count,l_module_name,1);
3160
3161 print_debug('Exited. ',l_module_name,1);
3162 END IF;
3163
3164 EXCEPTION
3165 WHEN fnd_api.g_exc_error THEN
3166 IF (l_debug=1) THEN
3167 print_debug('Error (fnd_api.g_exc_error) occured at '||l_progress,l_module_name,1);
3168 END IF;
3169 x_return_status:=FND_API.G_RET_STS_ERROR;
3170 ROLLBACK TO sp_op_inbound_abort;
3171
3172 WHEN fnd_api.g_exc_unexpected_error THEN
3173
3174 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
3175 IF (l_debug=1) THEN
3176 print_debug('Unexpected Error (fnd_api.g_exc_unexpected_error) occured at '||l_progress,l_module_name,3);
3177 END IF;
3178
3179 ROLLBACK TO sp_op_inbound_abort;
3180
3181 WHEN OTHERS THEN
3182
3183 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
3184 IF (l_debug=1) THEN
3185 print_debug('Other Error occured at '||l_progress,l_module_name,1);
3186 IF SQLCODE IS NOT NULL THEN
3187 print_debug('With SQL error : ' || SQLERRM(SQLCODE), l_module_name,1);
3188 END IF;
3189 END IF;
3190
3191 ROLLBACK TO sp_op_inbound_abort;
3192
3193 END abort;
3194
3195 END WMS_OP_INBOUND_PVT;