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