[Home] [Help]
PACKAGE BODY: APPS.WMS_WORKFLOW_WRAPPERS
Source
1 PACKAGE BODY wms_workflow_wrappers as
2 /* $Header: WMSWFWRB.pls 120.6 2008/04/30 13:36:16 abaid ship $ */
3
4 g_pkg_name CONSTANT VARCHAR(30) := 'wms_workflow_wrappers';
5 g_return_status VARCHAR2(30) := NULL; --Bug 6116046
6 -- to turn off debugger, comment out the line 'dbms_output.put_line(msg);'
7
8 PROCEDURE mdebug(msg in varchar2)
9 IS
10 l_msg VARCHAR2(5100);
11 l_ts VARCHAR2(30);
12 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13 BEGIN
14 select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
15
16 l_msg:=l_ts||' '||msg;
17
18 inv_mobile_helper_functions.tracelog
19 (p_err_msg => l_msg,
20 p_module => 'wms_workflow_wrappers',
21 p_level => 4);
22
23 --dbms_output.put_line(msg);
24 null;
25 END;
26
27 -- This is the procedure called by LoadPick.
28 -- wf_wrapper in turn calls wf_start_workflow which kicks off the workflow
29 PROCEDURE wf_wrapper(p_api_version IN NUMBER
30 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
31 , p_commit IN VARCHAR2 := fnd_api.g_false
32 , x_return_status OUT NOCOPY VARCHAR2
33 , x_msg_count OUT NOCOPY NUMBER
34 , x_msg_data OUT NOCOPY VARCHAR2
35 , p_org_id IN NUMBER
36 , p_rsn_id IN NUMBER
37 , p_calling_program IN VARCHAR2
38 , p_tmp_id IN NUMBER DEFAULT NULL
39 , p_quantity_picked IN NUMBER DEFAULT NULL
40 , p_dest_sub IN VARCHAR2 DEFAULT NULL
41 , p_dest_loc IN NUMBER DEFAULT NULL
42 )
43 IS
44 l_api_name CONSTANT VARCHAR2(30) := 'wf_wrapper';
45 l_api_version CONSTANT NUMBER := 1.0;
46
47 lX_RETURN_STATUS VARCHAR2(250);
48 lX_MSG_DATA VARCHAR2(250);
49 lX_MSG_COUNT NUMBER;
50 lX_ORGANIZATION_ID NUMBER;
51 lX_SUBINVENTORY VARCHAR2(250);
52 lX_SUBINVENTORY_STATUS NUMBER;
53 lX_LOCATOR NUMBER;
54 lX_LOCATOR_STATUS NUMBER;
55 lX_LPN_ID NUMBER;
56 lX_LPN_STATUS NUMBER;
57 lX_INVENTORY_ITEM_ID NUMBER;
58 lX_REVISION VARCHAR2(250);
59 lX_LOT_NUMBER VARCHAR2(250);
60 lX_LOT_STATUS NUMBER;
61 lX_QUANTITY NUMBER;
62 lX_UOM_CODE VARCHAR2(250);
63 lX_PRIMARY_QUANTITY NUMBER;
64 lX_TRANSACTION_QUANTITY NUMBER;
65 lX_RESERVATION_ID NUMBER;
66
67 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
68 BEGIN
69 IF (l_debug = 1) THEN
70 mdebug('In workflow wrapper');
71 END IF;
72 -- Standard Start of API savepoint
73 SAVEPOINT wf_wrapper_PVT;
74 -- Standard call to check for call compatibility.
75 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
76 p_api_version ,
77 l_api_name ,
78 G_PKG_NAME )
79 THEN
80 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
81 FND_MSG_PUB.ADD;
82 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
83 END IF;
84
85 /* Comment this out because FND package not working in WMSTST
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87 IF FND_API.to_Boolean( p_init_msg_list ) THEN
88 -- FND_MSG_PUB.initialize;
89 END IF;
90 */
91
92 -- Initialize API return status to success
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 IF (l_debug = 1) THEN
96 mdebug('before calling wms_workflow_wrappers.wf_start_workflow');
97 END IF;
98
99 --p_dest_sub = sub suggested by system
100 --p_dest_loc = loc suggested by system
101 wms_workflow_wrappers.wf_start_workflow(
102 P_REASON_ID => p_rsn_id,
103 P_CALLING_PROGRAM_NAME => p_calling_program,
104 P_SOURCE_ORGANIZATION_ID => p_org_id,
105 P_REASON_NAME => NULL,
106 P_DESTINATION_ORGANIZATION_ID => NULL,
107 P_SOURCE_SUBINVENTORY => p_dest_sub,
108 P_SOURCE_SUBINVENTORY_STATUS => NULL,
109 P_DESTINATION_SUBINVENTORY => NULL,
110 P_DESTINATION_SUBINVENTORY_ST => NULL,
111 P_SOURCE_LOCATOR => p_dest_loc,
112 P_SOURCE_LOCATOR_STATUS => NULL,
113 P_DESTINATION_LOCATOR => NULL,
114 P_DESTINATION_LOCATOR_STATUS => NULL,
115 P_LPN_ID => NULL,
116 P_LPN_STATUS => NULL,
117 P_CONTENT_LPN_ID => NULL,
118 P_CONTENT_LPN_STATUS => NULL,
119 p_source_parent_lpn_id => NULL,
120 P_SOURCE_parent_LPN_STATUS => NULL,
121 P_SOURCE_OUTERMOST_LPN_ID => NULL,
122 P_SOURCE_OUTERMOST_LPN_STATUS => NULL,
123 p_dest_lpn_id => NULL,
124 p_dest_lpn_status => NULL,
125 p_dest_parent_lpn_id => NULL,
126 p_dest_parent_lpn_status => NULL,
127 P_DEST_OUTERMOST_LPN_ID => NULL,
128 P_DEST_OUTERMOST_LPN_STATUS => NULL,
129 P_INVENTORY_ITEM_ID => NULL,
130 P_REVISION => NULL,
131 P_LOT_NUMBER => NULL,
132 p_to_lot_number => NULL,
133 P_LOT_STATUS => NULL,
134 P_SERIAL_NUMBER => NULL,
135 p_to_serial_number => NULL,
136 P_SERIAL_NUMBER_STATUS => NULL,
137 P_PRIMARY_UOM => NULL,
138 P_TRANSACTION_UOM => NULL,
139 P_PRIMARY_QUANTITY => NULL,
140 P_TRANSACTION_QUANTITY => p_quantity_picked,
141 P_TRANSACTION_ACTION_ID => NULL,
142 P_TRANSACTION_SOURCE_TYPE_ID => NULL,
143 P_TRANSACTION_SOURCE => NULL,
144 P_RESERVATION_ID => NULL,
145 P_EQUIPMENT_ID => NULL,
146 P_USER_ID => FND_GLOBAL.user_id,
147 P_TASK_TYPE_ID => NULL,
148 P_TASK_ID => NULL,
149 p_txn_temp_id => p_tmp_id,
150 p_update_status_method => NULL,
151 P_PROGRAM_CONTROL_ARG1 => NULL,
152 P_PROGRAM_CONTROL_ARG2 => NULL,
153 P_PROGRAM_CONTROL_ARG3 => NULL,
154 P_PROGRAM_CONTROL_ARG4 => NULL,
155 P_PROGRAM_CONTROL_ARG5 => NULL,
156 P_PROGRAM_CONTROL_ARG6 => NULL
157 ,X_RETURN_STATUS => lX_RETURN_STATUS
158 ,X_MSG_DATA => lX_MSG_DATA
159 ,X_MSG_COUNT => lX_MSG_COUNT
160 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
161 ,X_SUBINVENTORY => lX_SUBINVENTORY
162 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
163 ,X_LOCATOR => lX_LOCATOR
164 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
165 ,X_LPN_ID => lX_LPN_ID
166 ,X_LPN_STATUS => lX_LPN_STATUS
167 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
168 ,X_REVISION => lX_REVISION
169 ,X_LOT_NUMBER => lX_LOT_NUMBER
170 ,X_LOT_STATUS => lX_LOT_STATUS
171 ,X_QUANTITY => lX_QUANTITY
172 ,X_UOM_CODE => lX_UOM_CODE
173 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
174 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
175 ,X_RESERVATION_ID => lX_RESERVATION_ID
176 );
177
178 x_return_status := lX_RETURN_STATUS; --Bug 6116046
179 --bug 6924639
180 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
181 IF (l_debug = 1) THEN
182 mdebug('call to startworkflow failed at 1');
183 END IF;
184
185 fnd_message.set_name('WMS', 'WMS_START_WORKFLOW_FAILED');
186 fnd_msg_pub.ADD;
187 RAISE fnd_api.g_exc_unexpected_error;
188 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
189 IF (l_debug = 1) THEN
190 mdebug('call to startworkflow failed at 2 ');
191 END IF;
192
193 fnd_message.set_name('WMS', 'WMS_START_WORKFLOW_FAILED');
194 fnd_msg_pub.ADD;
195 RAISE fnd_api.g_exc_error;
196 END IF;
197 --bug 6924639
198 /*
199 -- after API call, validate return status
200 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS AND x_return_status <> 'Y') THEN --bug 6924639 added condition to check for x_return_status<>'Y'
201 IF (l_debug = 1) THEN
202 mdebug('call to startworkflow failed ');
203 END IF;
204 FND_MESSAGE.SET_NAME('WMS', 'WMS_START_WORKFLOW_FAILED');
205 FND_MSG_PUB.ADD;
206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
207 END IF;
208 */
209 EXCEPTION
210 WHEN FND_API.G_EXC_ERROR THEN
211 IF (l_debug = 1) THEN
212 mdebug('expected error in '||l_api_name);
213 END IF;
214 ROLLBACK TO wf_wrapper_pvt;
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
217 ,p_data => x_msg_data);
218
219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
220 IF (l_debug = 1) THEN
221 mdebug('unexpected error in '||l_api_name);
222 END IF;
223 ROLLBACK TO wf_wrapper_pvt;
224 mdebug('ROLLBACK to wf_wrapper_pvt succeeded');
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
227 ,p_data => x_msg_data);
228
229 WHEN OTHERS THEN
230 IF (l_debug = 1) THEN
231 mdebug('others error in '||l_api_name);
232 END IF;
233 ROLLBACK TO wf_wrapper_pvt;
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
237 END IF;
238 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
239 , p_data => x_msg_data);
240 END wf_wrapper;
241
242
243
244 -- New workflow (replacing wms_txnreasons_pub.Start_Workflow)
245 -- if task_id is populated, then all the parameters will be
246 -- obtained from tables except for the following:
247 -- p_to_serial_number, p_to_lot_number, p_update_status_method (these are
248 -- all for Janet's API call)
249
250 PROCEDURE wf_start_workflow(
251 P_REASON_ID IN NUMBER,
252 P_CALLING_PROGRAM_NAME IN VARCHAR2,
253 P_SOURCE_ORGANIZATION_ID IN NUMBER,
254 P_REASON_NAME IN VARCHAR2 DEFAULT NULL,
255 P_DESTINATION_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
256 P_SOURCE_SUBINVENTORY IN VARCHAR2 DEFAULT NULL,
257 P_SOURCE_SUBINVENTORY_STATUS IN NUMBER DEFAULT NULL,
258 P_DESTINATION_SUBINVENTORY IN VARCHAR2 DEFAULT NULL,
259 P_DESTINATION_SUBINVENTORY_ST IN NUMBER DEFAULT NULL,
260 P_SOURCE_LOCATOR IN NUMBER DEFAULT NULL,
261 P_SOURCE_LOCATOR_STATUS IN NUMBER DEFAULT NULL,
262 P_DESTINATION_LOCATOR IN NUMBER DEFAULT NULL,
263 P_DESTINATION_LOCATOR_STATUS IN NUMBER DEFAULT NULL,
264 P_LPN_ID IN NUMBER DEFAULT NULL,
265 P_ONHAND_STATUS IN VARCHAR2 DEFAULT NULL, -- Added for Onhand material support --6633612
266 P_LPN_STATUS IN NUMBER DEFAULT NULL,
267 P_CONTENT_LPN_ID IN NUMBER DEFAULT NULL,
268 P_CONTENT_LPN_STATUS IN NUMBER DEFAULT NULL,
269 p_source_parent_lpn_id IN NUMBER DEFAULT NULL,
270 P_SOURCE_parent_LPN_STATUS IN NUMBER DEFAULT NULL,
271 P_SOURCE_OUTERMOST_LPN_ID IN NUMBER DEFAULT NULL,
272 P_SOURCE_OUTERMOST_LPN_STATUS IN NUMBER DEFAULT NULL,
273 p_dest_lpn_id IN NUMBER DEFAULT NULL,
274 p_dest_lpn_status IN NUMBER DEFAULT NULL,
275 p_dest_parent_lpn_id IN NUMBER DEFAULT NULL,
276 p_dest_parent_lpn_status IN NUMBER DEFAULT NULL,
277 P_DEST_OUTERMOST_LPN_ID IN NUMBER DEFAULT NULL,
278 P_DEST_OUTERMOST_LPN_STATUS IN NUMBER DEFAULT NULL,
279 P_INVENTORY_ITEM_ID IN NUMBER DEFAULT NULL,
280 P_REVISION IN VARCHAR2 DEFAULT NULL,
281 P_LOT_NUMBER IN VARCHAR2 DEFAULT NULL,
282 p_to_lot_number IN VARCHAR2 DEFAULT NULL,
283 P_LOT_STATUS IN NUMBER DEFAULT NULL,
284 P_SERIAL_NUMBER IN VARCHAR2 DEFAULT NULL,
285 p_to_serial_number IN VARCHAR2 DEFAULT NULL,
286 P_SERIAL_NUMBER_STATUS IN NUMBER DEFAULT NULL,
287 P_PRIMARY_UOM IN VARCHAR2 DEFAULT NULL,
288 P_TRANSACTION_UOM IN VARCHAR2 DEFAULT NULL,
289 P_PRIMARY_QUANTITY IN NUMBER DEFAULT NULL,
290 P_TRANSACTION_QUANTITY IN NUMBER DEFAULT NULL,
291 P_TRANSACTION_ACTION_ID IN NUMBER DEFAULT NULL,
292 P_TRANSACTION_SOURCE_TYPE_ID IN NUMBER DEFAULT NULL,
293 P_TRANSACTION_SOURCE IN NUMBER DEFAULT NULL,
294 P_RESERVATION_ID IN NUMBER DEFAULT NULL,
295 P_EQUIPMENT_ID IN NUMBER DEFAULT NULL,
296 P_USER_ID IN NUMBER DEFAULT NULL,
297 P_TASK_TYPE_ID IN NUMBER DEFAULT NULL,
298 P_TASK_ID IN NUMBER DEFAULT NULL,
299 p_txn_temp_id IN NUMBER DEFAULT NULL,
300 p_update_status_method IN VARCHAR2 DEFAULT NULL,
301 P_PROGRAM_CONTROL_ARG1 IN VARCHAR2 DEFAULT NULL,
302 P_PROGRAM_CONTROL_ARG2 IN VARCHAR2 DEFAULT NULL,
303 P_PROGRAM_CONTROL_ARG3 IN VARCHAR2 DEFAULT NULL,
304 P_PROGRAM_CONTROL_ARG4 IN VARCHAR2 DEFAULT NULL,
305 P_PROGRAM_CONTROL_ARG5 IN VARCHAR2 DEFAULT NULL,
306 P_PROGRAM_CONTROL_ARG6 IN VARCHAR2 DEFAULT NULL,
307 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
308 X_MSG_DATA OUT NOCOPY VARCHAR2,
309 X_MSG_COUNT OUT NOCOPY NUMBER,
310 X_ORGANIZATION_ID OUT NOCOPY NUMBER,
311 X_SUBINVENTORY OUT NOCOPY VARCHAR2,
312 X_SUBINVENTORY_STATUS OUT NOCOPY NUMBER,
313 X_LOCATOR OUT NOCOPY NUMBER,
314 X_LOCATOR_STATUS OUT NOCOPY NUMBER,
315 X_LPN_ID OUT NOCOPY NUMBER,
316 X_LPN_STATUS OUT NOCOPY NUMBER,
317 X_INVENTORY_ITEM_ID OUT NOCOPY NUMBER,
318 X_REVISION OUT NOCOPY VARCHAR2,
319 X_LOT_NUMBER OUT NOCOPY VARCHAR2,
320 X_LOT_STATUS OUT NOCOPY NUMBER,
321 X_QUANTITY OUT NOCOPY NUMBER,
322 X_UOM_CODE OUT NOCOPY VARCHAR2,
326 )
323 X_PRIMARY_QUANTITY OUT NOCOPY NUMBER,
324 X_TRANSACTION_QUANTITY OUT NOCOPY NUMBER,
325 X_RESERVATION_ID OUT NOCOPY NUMBER
327 IS
328
329 -- defining input variables and initializing them to null;
330 L_REASON_NAME VARCHAR2(250) := NULL;
331 L_SOURCE_ORGANIZATION_N VARCHAR2(250) := NULL;
332 L_DESTINATION_ORGANIZATION_ID NUMBER := NULL;
333 L_SOURCE_SUBINVENTORY VARCHAR2(250) := NULL;
334 L_SOURCE_SUBINVENTORY_STATUS NUMBER := NULL;
335 L_DESTINATION_SUBINVENTORY VARCHAR2(250) := NULL;
336 L_DESTINATION_SUBINVENTORY_ST NUMBER := NULL;
337 L_SOURCE_LOCATOR NUMBER := NULL;
338 L_SOURCE_LOCATOR_N VARCHAR2(250) :=NULL;
339 L_SOURCE_LOCATOR_STATUS NUMBER := NULL;
340 L_DESTINATION_LOCATOR NUMBER := NULL;
341 L_DESTINATION_LOCATOR_STATUS NUMBER := NULL;
342 L_LPN_ID NUMBER := NULL;
343 L_LPN_N VARCHAR2(250) :=NULL;
344 L_LPN_STATUS NUMBER := NULL;
345 L_CONTENT_LPN_ID NUMBER := NULL;
346 L_CONTENT_LPN_STATUS NUMBER := NULL;
347 L_source_parent_lpn_id NUMBER := NULL;
348 L_SOURCE_parent_LPN_STATUS NUMBER := NULL;
349 L_SOURCE_OUTERMOST_LPN_ID NUMBER := NULL;
350 L_SOURCE_OUTERMOST_LPN_STATUS NUMBER := NULL;
351 L_dest_lpn_id NUMBER := NULL;
352 L_dest_lpn_status NUMBER := NULL;
353 L_dest_parent_lpn_id NUMBER := NULL;
354 L_dest_parent_lpn_status NUMBER := NULL;
355 L_DEST_OUTERMOST_LPN_ID NUMBER := NULL;
356 L_DEST_OUTERMOST_LPN_STATUS NUMBER := NULL;
357 L_INVENTORY_ITEM_ID NUMBER := NULL;
358 L_INVENTORY_ITEM_NAME VARCHAR2(250) := NULL;
359 L_REVISION VARCHAR2(250) := NULL;
360 L_LOT_NUMBER VARCHAR2(250) := NULL;
361 L_to_lot_number VARCHAR2(250) := NULL;
362 L_LOT_STATUS NUMBER := NULL;
363 L_SERIAL_NUMBER VARCHAR2(250) := NULL;
364 L_to_serial_number VARCHAR2(250) := NULL;
365 L_SERIAL_NUMBER_STATUS NUMBER := NULL;
366 L_PRIMARY_UOM VARCHAR2(250) := NULL;
367 L_TRANSACTION_UOM VARCHAR2(250) := NULL;
368 L_PRIMARY_QUANTITY NUMBER := NULL;
369 L_TRANSACTION_QUANTITY NUMBER := NULL;
370 l_transaction_header_id NUMBER := NULL;
371 l_mo_line_id NUMBER := NULL;
372 L_TRANSACTION_ACTION_ID NUMBER := NULL;
373 L_TRANSACTION_SOURCE_TYPE_ID NUMBER := NULL;
374 L_TRANSACTION_SOURCE NUMBER := NULL;
375 L_RESERVATION_ID NUMBER := NULL;
376 L_EQUIPMENT_ID NUMBER := NULL;
377 L_USER_ID NUMBER := NULL;
378 l_user_name VARCHAR2(250) := NULL;
379 L_TASK_TYPE_ID NUMBER := NULL;
380 L_TASK_ID NUMBER := NULL;
381 l_txn_temp_id NUMBER := NULL;
382 L_update_status_method VARCHAR2(250) := NULL;
383 L_PROGRAM_CONTROL_ARG1 VARCHAR2(250) := NULL;
384 L_PROGRAM_CONTROL_ARG2 VARCHAR2(250) := NULL;
385 L_PROGRAM_CONTROL_ARG3 VARCHAR2(250) := NULL;
386 L_PROGRAM_CONTROL_ARG4 VARCHAR2(250) := NULL;
387 L_PROGRAM_CONTROL_ARG5 VARCHAR2(250) := NULL;
388 L_PROGRAM_CONTROL_ARG6 VARCHAR2(250) := NULL;
389
390
391 -- temp variables
392 l_transaction_temp_id NUMBER;
393 l_error NUMBER;
394
395 -- variables to create workflow process
396 l_workflow_name varchar2(250);
397 l_workflow_process varchar2(250);
398 l_sequence_number number ;
399 l_item_key varchar2(500);
400
401 -- debug variable
402
403 l_api_name CONSTANT VARCHAR2(30) := 'wf_start_workflow';
404
405 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
406 BEGIN
407 IF (l_debug = 1) THEN
408 mdebug('In Start_Workflow');
409 END IF;
410
411 -- Standard Start of API savepoint
412 SAVEPOINT wf_start_workflow_PVT;
413
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415
416 -- given the reason id, get reason name
417 IF p_reason_name IS NULL THEN
418 SELECT reason_name
419 INTO l_reason_name
420 FROM mtl_transaction_reasons
421 WHERE reason_id = p_reason_id;
422 ELSE
423 l_reason_name := p_reason_name;
424 END IF;
425 IF (l_debug = 1) THEN
426 mdebug('after gettting reason name');
427 END IF;
428 -- populate local variables from wdt table
429
430 --Now using mmtt id instead of task id
431 l_transaction_temp_id := p_txn_temp_id;
432 IF (l_debug = 1) THEN
433 mdebug('mmtt_id: '||l_transaction_temp_id );
434 END IF;
435
436 IF (p_task_id IS NOT NULL AND p_task_id>0) THEN
437 l_task_id := p_task_id;
438 IF (l_debug = 1) THEN
439 mdebug('l_task_id: '||l_task_id);
440 END IF;
441 ELSE
442 IF (l_debug = 1) THEN
443 mdebug('IN ELSE before : l_task_id: '||l_task_id);
444 END IF;
445 BEGIN
449 WHERE transaction_temp_id=l_transaction_temp_id;
446 SELECT task_id
447 INTO l_task_id
448 FROM wms_dispatched_tasks
450 IF (l_debug = 1) THEN
451 mdebug('IN ELSE after : l_task_id: '||l_task_id);
452 END IF;
453 EXCEPTION
454 WHEN no_data_found THEN
455 l_task_id := NULL;
456 END;
457
458 END IF;
459 IF (l_debug = 1) THEN
460 mdebug('after getting task id: '||l_task_id);
461 END IF;
462
463 -- from mmtt_id populate local variables obtained from mmtt table
464 SELECT subinventory_code, locator_id, transfer_organization,
465 wms_task_type, lpn_id, content_lpn_id, transfer_lpn_id,
466 inventory_item_id, revision, lot_number, serial_number,
467 primary_quantity, item_primary_uom_code,
468 transaction_quantity, transaction_uom,
469 transaction_header_id, transaction_action_id, transaction_source_type_id,
470 transaction_source_id,
471 reservation_id, move_order_line_id
472
473 INTO l_destination_subinventory, l_destination_locator,l_destination_organization_id,
474 l_task_type_id, l_lpn_id, l_content_lpn_id, l_dest_lpn_id,
475 l_inventory_item_id, l_revision, l_lot_number, l_serial_number,
476 l_primary_quantity, l_primary_uom,
477 l_transaction_quantity, l_transaction_uom,
478 l_transaction_header_id, l_transaction_action_id, l_transaction_source_type_id,
479 l_transaction_source,
480 l_reservation_id, l_mo_line_id
481 FROM mtl_material_transactions_temp
482 WHERE transaction_temp_id = l_transaction_temp_id;
483
484 IF (l_debug = 1) THEN
485 mdebug('after select from mmtt ');
486 END IF;
487
488 -- if the input variable is not null, populate the local
489 -- variable with the input variable
490 IF p_destination_organization_id IS NOT NULL THEN
491 l_destination_organization_id := p_destination_organization_id;
492 END IF;
493
494 IF p_source_subinventory IS NOT NULL THEN
495 l_source_subinventory := p_source_subinventory;
496 END IF;
497
498 IF p_destination_subinventory IS NOT NULL THEN
499 l_destination_subinventory := p_destination_subinventory;
500 END IF;
501
502 IF p_source_locator IS NOT NULL THEN
503 l_source_locator := p_source_locator;
504 END IF;
505
506 IF p_destination_locator IS NOT NULL THEN
507 l_destination_locator := p_destination_locator;
508 END IF;
509
510 IF p_lpn_id IS NOT NULL THEN
511 l_lpn_id := p_lpn_id;
512 END IF;
513
514 IF p_content_lpn_id IS NOT NULL THEN
515 l_content_lpn_id := p_content_lpn_id;
516 END IF;
517
518 l_error := 1;
519
520 -- get content lpn status,
521
522 IF (l_debug = 1) THEN
523 mdebug(' l_content_lpn_id '||l_content_lpn_id);
524 mdebug(' p_content_lpn_status '||p_content_lpn_status);
525 END IF;
526 IF l_content_lpn_id IS NOT NULL AND l_content_lpn_id > 0 THEN
527 IF p_content_lpn_status IS NULL THEN
528 BEGIN
529 SELECT status_id
530 INTO l_content_lpn_status
531 FROM wms_license_plate_numbers
532 WHERE lpn_id = l_content_lpn_id
533 AND organization_id = p_source_organization_id;
534 EXCEPTION
535 WHEN OTHERS THEN
536 IF (l_debug = 1) THEN
537 mdebug('Exception occurred '||Sqlerrm);
538 mdebug('while getting the content lpn status');
539 END IF;
540 END;
541
542 ELSE
543 l_content_lpn_status := p_content_lpn_status;
544 END IF;
545 END IF;
546
547 l_error := 2;
548
549 -- for source lpn: get lpn name, parent lpn id and outermost lpn id and status id
550 -- and then replace local variables with input parameters
551 -- where input parameters are not null
552 IF (l_debug = 1) THEN
553 mdebug('l_lpn_id '||l_lpn_id);
554 END IF;
555 IF l_lpn_id IS NOT NULL AND l_lpn_id > 0 THEN
556 BEGIN
557 SELECT license_plate_number, parent_lpn_id, outermost_lpn_id, status_id
558 INTO l_lpn_n, l_source_parent_lpn_id, l_source_outermost_lpn_id,
559 l_lpn_status
560 FROM wms_license_plate_numbers
561 WHERE lpn_id = l_lpn_id
562 AND organization_id = p_source_organization_id;
563 EXCEPTION
564 WHEN OTHERS THEN
565 IF (l_debug = 1) THEN
566 mdebug('Exception occurred '||Sqlerrm);
567 mdebug('for source lpn: get lpn name, parent lpn id and outermost lpn id and status id');
568 END IF;
569 END;
570
571 END IF;
572
573 IF p_source_parent_lpn_id IS NOT NULL THEN
574 l_source_parent_lpn_id := p_source_parent_lpn_id;
575 END IF;
576
577 IF p_source_outermost_lpn_id IS NOT NULL THEN
578 l_source_outermost_lpn_id := p_source_outermost_lpn_id;
579 END IF;
580
581 IF p_lpn_status IS NOT NULL THEN
582 l_lpn_status := p_lpn_status;
583 END IF;
584
585 l_error := 3;
586 -- get source parent lpn status id
587 IF l_source_parent_lpn_id IS NOT NULL THEN
588 IF p_source_parent_lpn_status IS NULL THEN
589 SELECT status_id
593 AND organization_id = p_source_organization_id;
590 INTO l_source_parent_lpn_status
591 FROM wms_license_plate_numbers
592 WHERE lpn_id = l_source_parent_lpn_id
594 ELSE
595 l_source_parent_lpn_status := p_source_parent_lpn_status;
596 END IF;
597 END IF;
598
599 l_error := 4;
600 -- get source outermost status id
601 IF l_source_outermost_lpn_id IS NOT NULL THEN
602 IF p_source_outermost_lpn_status IS NULL THEN
603 SELECT status_id
604 INTO l_source_outermost_lpn_status
605 FROM wms_license_plate_numbers
606 WHERE lpn_id = l_source_outermost_lpn_id
607 AND organization_id = p_source_organization_id;
608 ELSE
609 l_source_outermost_lpn_id := p_source_outermost_lpn_id;
610 END IF;
611 END IF;
612
613
614 IF p_dest_lpn_id IS NOT NULL THEN
615 l_dest_lpn_id := p_dest_lpn_id;
616 END IF;
617
618 l_error := 5;
619
620 -- for dest lpn: get parent lpn id and outermost lpn id and status id
621 -- and then replace local variables with input parameters
622 -- where input parameters are not null
623 IF l_dest_lpn_id IS NOT NULL THEN
624 SELECT parent_lpn_id, outermost_lpn_id, status_id
625 INTO l_dest_parent_lpn_id, l_dest_outermost_lpn_id,
626 l_dest_lpn_status
627 FROM wms_license_plate_numbers
628 WHERE lpn_id = l_dest_lpn_id
629 AND organization_id = p_source_organization_id;
630 END IF;
631
632 IF p_dest_parent_lpn_id IS NOT NULL THEN
633 l_dest_parent_lpn_id := p_dest_parent_lpn_id;
634 END IF;
635
636 IF p_dest_outermost_lpn_id IS NOT NULL THEN
637 l_dest_outermost_lpn_id := p_dest_outermost_lpn_id;
638 END IF;
639
640 IF p_dest_lpn_status IS NOT NULL THEN
641 l_dest_lpn_status := p_dest_lpn_status;
642 END IF;
643
644 l_error := 6;
645 -- get dest parent lpn status id
646 IF l_dest_parent_lpn_id IS NOT NULL THEN
647 IF p_dest_parent_lpn_status IS NULL THEN
648 SELECT status_id
649 INTO l_dest_parent_lpn_status
650 FROM wms_license_plate_numbers
651 WHERE lpn_id = l_dest_parent_lpn_id
652 AND organization_id = p_source_organization_id;
653 ELSE
654 l_dest_parent_lpn_status := p_dest_parent_lpn_status;
655 END IF;
656 END IF;
657
658 l_error := 7;
659
660 -- get dest outermost lpn status id
661 IF l_dest_outermost_lpn_id IS NOT NULL THEN
662 IF p_dest_outermost_lpn_status IS NULL THEN
663 SELECT status_id
664 INTO l_dest_outermost_lpn_status
665 FROM wms_license_plate_numbers
666 WHERE lpn_id = l_dest_outermost_lpn_id
667 AND organization_id = p_source_organization_id;
668 ELSE
669 l_dest_outermost_lpn_id := p_dest_outermost_lpn_id;
670 END IF;
671 END IF;
672 l_error := 8;
673 -- get source subinventory status
674 IF l_source_subinventory IS NOT NULL THEN
675 IF p_source_subinventory_status IS NULL THEN
676 SELECT status_id
677 INTO l_source_subinventory_status
678 FROM mtl_secondary_inventories
679 WHERE secondary_inventory_name = l_source_subinventory
680 AND organization_id = p_source_organization_id;
681 ELSE
682 l_source_subinventory_status := p_source_subinventory_status;
683 END IF;
684 END IF;
685 l_error := 9;
686 -- get destination subinventory status
687 IF (l_destination_subinventory IS NOT NULL AND
688 l_destination_organization_id IS NOT NULL) THEN
689 IF p_destination_subinventory_st IS NULL THEN
690 SELECT status_id
691 INTO l_destination_subinventory_st
692 FROM mtl_secondary_inventories
693 WHERE secondary_inventory_name = l_destination_subinventory
694 AND organization_id = l_destination_organization_id;
695 ELSE
696 l_destination_subinventory_st := p_destination_subinventory_st;
697 END IF;
698 END IF;
699 l_error := 10;
700 -- get source locator status
701 IF l_source_locator IS NOT NULL THEN
702 IF p_source_locator_status IS NULL THEN
703 SELECT status_id
704 INTO l_source_locator_status
705 FROM mtl_item_locations
706 WHERE inventory_location_id = l_source_locator
707 AND organization_id = p_source_organization_id;
708 ELSE
709 l_source_locator_status := p_source_locator_status;
710 END IF;
711 END IF;
712 l_error := 11;
713 -- get destination locator status
714 IF (l_destination_locator IS NOT NULL
715 AND l_destination_organization_id IS NOT NULL) THEN
716 IF p_destination_locator_status IS NULL THEN
717 SELECT status_id
718 INTO l_destination_locator_status
719 FROM mtl_item_locations
720 WHERE inventory_location_id = l_destination_locator
721 AND organization_id = l_destination_organization_id;
722 ELSE
723 l_destination_locator_status := p_destination_locator_status;
724 END IF;
725 END IF;
726 l_error := 12;
727
728 IF p_inventory_item_id IS NOT NULL THEN
729 l_inventory_item_id := p_inventory_item_id;
730 END IF;
731
732 IF p_serial_number IS NOT NULL THEN
733 l_serial_number := p_serial_number;
734 END IF;
738 END IF;
735
736 IF p_lot_number IS NOT NULL THEN
737 l_lot_number := p_lot_number;
739 l_error := 13;
740 -- get status id for serial and lot numbers
741 IF (l_inventory_item_id IS NOT NULL
742 AND l_serial_number IS NOT NULL) THEN
743 IF p_serial_number_status IS NULL THEN
744 SELECT status_id
745 INTO l_serial_number_status
746 FROM mtl_serial_numbers
747 WHERE serial_number = l_serial_number
748 AND inventory_item_id = l_inventory_item_id;
749 ELSE
750 l_serial_number_status := p_serial_number_status;
751 END IF;
752 END IF;
753
754 IF (l_inventory_item_id IS NOT NULL
755 AND l_lot_number IS NOT NULL) THEN
756 IF p_lot_status IS NULL THEN
757 SELECT status_id
758 INTO l_lot_status
759 FROM mtl_lot_numbers
760 WHERE lot_number = l_lot_number
761 AND inventory_item_id = l_inventory_item_id
762 AND organization_id = p_source_organization_id;
763 ELSE
764 l_lot_status := p_lot_status;
765 END IF;
766 END IF;
767 l_error := 14;
768 -- get inventory_item_name from inventory_item_id
769 IF (l_inventory_item_id IS NOT NULL) THEN
770 SELECT concatenated_segments
771 INTO l_inventory_item_name
772 FROM mtl_system_items_kfv
773 WHERE inventory_item_id = l_inventory_item_id
774 AND organization_id = p_source_organization_id;
775 END IF;
776 l_error := 15;
777 -- verify that the rest of the input parameters that are not
778 -- null are copied to the local variables
779
780
781 IF p_revision IS NOT NULL THEN
782 l_revision := p_revision;
783 END IF;
784
785
786 IF p_to_lot_number IS NOT NULL THEN
787 l_to_lot_number := p_to_lot_number;
788 END IF;
789
790
791 IF p_to_serial_number IS NOT NULL THEN
792 l_to_serial_number := p_to_serial_number;
793 END IF;
794
795 IF p_primary_uom IS NOT NULL THEN
796 l_primary_uom := p_primary_uom;
797 END IF;
798
799 IF p_transaction_uom IS NOT NULL THEN
800 l_transaction_uom := p_transaction_uom;
801 END IF;
802
803 IF p_primary_quantity IS NOT NULL THEN
804 l_primary_quantity := p_primary_quantity;
805 END IF;
806
807 IF p_transaction_quantity IS NOT NULL THEN
808 l_transaction_quantity := p_transaction_quantity;
809 END IF;
810
811 IF p_transaction_action_id IS NOT NULL THEN
812 l_transaction_action_id := p_transaction_action_id;
813 END IF;
814
815 IF p_transaction_source_type_id IS NOT NULL THEN
816 l_transaction_source_type_id := p_transaction_source_type_id;
817 END IF;
818
819 IF p_transaction_source IS NOT NULL THEN
820 l_transaction_source := p_transaction_source;
821 END IF;
822
823 IF p_reservation_id IS NOT NULL THEN
824 l_reservation_id := p_reservation_id;
825 END IF;
826
827 IF p_equipment_id IS NOT NULL THEN
828 l_equipment_id := p_equipment_id;
829 END IF;
830
831 IF p_user_id IS NOT NULL THEN
832 l_user_id := p_user_id;
833 END IF;
834
835 l_error := 16;
836 -- get user name from user_id
837 IF (l_user_id IS NOT NULL) THEN
838 SELECT user_name
839 INTO l_user_name
840 FROM fnd_user
841 WHERE user_id = l_user_id;
842 END IF;
843 l_error := 17;
844
845 -- get source org name
846 select organization_code
847 INTO l_source_organization_n
848 from mtl_parameters
849 where organization_id=p_source_organization_id;
850 l_error :=18;
851
852 -- get source locator name
853 IF (l_source_locator IS NOT NULL) THEN
854 select concatenated_segments
855 INTO l_source_locator_n
856 from mtl_item_locations_kfv
857 where inventory_location_id = l_source_locator
858 and organization_id = p_source_organization_id;
859 END IF;
860 l_error:=19;
861
862 IF p_task_type_id IS NOT NULL THEN
863 l_task_type_id := p_task_type_id;
864 END IF;
865
866 IF p_update_status_method IS NOT NULL THEN
867 l_update_status_method := p_update_status_method;
868 END IF;
869
870 IF p_program_control_arg1 IS NOT NULL THEN
871 l_program_control_arg1 := p_program_control_arg1;
872 END IF;
873
874 IF p_program_control_arg2 IS NOT NULL THEN
875 l_program_control_arg2 := p_program_control_arg2;
876 END IF;
877
878 IF p_program_control_arg3 IS NOT NULL THEN
879 l_program_control_arg3 := p_program_control_arg3;
880 END IF;
881
882 IF p_program_control_arg4 IS NOT NULL THEN
883 l_program_control_arg4 := p_program_control_arg4;
884 END IF;
885
886 IF p_program_control_arg5 IS NOT NULL THEN
887 l_program_control_arg5 := p_program_control_arg5;
888 END IF;
889
890 IF p_program_control_arg6 IS NOT NULL THEN
891 l_program_control_arg6 := p_program_control_arg6;
895 IF (l_debug = 1) THEN
892 END IF;
893
894 --check to see if local variables populated before calling workflow
896 mdebug('Checking the 47 input parameters...');
897 mdebug('P_reason_id: '||p_reason_id);
898 mdebug('P_CALLING_PROGRAM_NAME: '|| p_calling_program_name);
899 mdebug('P_source_organization_id: '|| p_source_organization_id);
900 mdebug('P_source_organization_name: '|| l_source_organization_n);
901 mdebug('L_REASON_NAME: '|| l_reason_name);
902 mdebug('L_DESTINATION_ORGANIZATION_ID: '||l_destination_organization_id);
903 mdebug('L_SOURCE_SUBINVENTORY: '||l_source_subinventory);
904 mdebug('L_SOURCE_SUBINVENTORY_STATUS: '||l_source_subinventory_status);
905 mdebug('L_DESTINATION_SUBINVENTORY: '||l_destination_subinventory);
906 mdebug('L_DESTINATION_SUBINVENTORY_ST: '||l_destination_subinventory_st);
907 mdebug('L_SOURCE_LOCATOR: '||l_source_locator);
908 mdebug('L_SOURCE_LOCATOR_NAME: '||l_source_locator_n);
909 mdebug('L_SOURCE_LOCATOR_STATUS: '||l_source_locator_status);
910 mdebug('L_DESTINATION_LOCATOR: '||L_DESTINATION_LOCATOR);
911 mdebug('L_DESTINATION_LOCATOR_STATUS: '||l_destination_locator_status);
912 mdebug('L_LPN_ID: '||l_lpn_id);
913 mdebug('L_LPN_NAME: '||l_lpn_n);
914 mdebug('L_LPN_STATUS: '||l_lpn_status);
915 mdebug('L_CONTENT_LPN_ID: '||l_content_lpn_id);
916 mdebug('L_CONTENT_LPN_STATUS: '||l_content_lpn_status);
917 mdebug('L_source_parent_lpn_id: '||l_source_parent_lpn_id);
918 mdebug('L_SOURCE_parent_LPN_STATUS: '||l_source_parent_lpn_status);
919 mdebug('L_SOURCE_OUTERMOST_LPN_ID: '||l_source_outermost_lpn_id);
920 mdebug('L_SOURCE_OUTERMOST_LPN_STATUS: '||l_source_outermost_lpn_status);
921 mdebug('L_dest_lpn_id: '||l_dest_lpn_id);
922 mdebug('L_dest_lpn_status: '||l_dest_lpn_status);
923 mdebug('L_dest_parent_lpn_id: '||l_dest_parent_lpn_id);
924 mdebug('L_dest_parent_lpn_status: '||l_dest_parent_lpn_status);
925 mdebug('L_DEST_OUTERMOST_LPN_ID: '||l_dest_outermost_lpn_id);
926 mdebug('L_DEST_OUTERMOST_LPN_STATUS: '||l_dest_outermost_lpn_status);
927 mdebug('L_INVENTORY_ITEM_ID: '||l_inventory_item_id);
928 mdebug('L_INVENTORY_ITEM_NAME: '||l_inventory_item_name);
929 mdebug('L_REVISION: '||l_revision);
930 mdebug('L_LOT_NUMBER: '||l_lot_number);
931 mdebug('L_to_lot_number: '||l_to_lot_number);
932 mdebug('L_LOT_STATUS: '||l_lot_status);
933 mdebug('L_SERIAL_NUMBER: '||l_serial_number);
934 mdebug('L_to_serial_number: '||l_to_serial_number);
935 mdebug('L_serial_number_status: '||l_serial_number_status);
936 mdebug('L_PRIMARY_UOM: '||l_primary_uom);
937 mdebug('L_TRANSACTION_UOM: '||l_transaction_uom);
938 mdebug('L_PRIMARY_QUANTITY: '||l_primary_quantity);
939 mdebug('L_TRANSACTION_QUANTITY: '||l_transaction_quantity);
940 mdebug('L_TRANSACTION_HEADER_ID: '||l_transaction_header_id);
941 mdebug('L_TRANSACTION_MO_LINE_ID: '||l_mo_line_id);
942 mdebug('L_TRANSACTION_ACTION_ID: '||l_transaction_action_id);
943 mdebug('L_TRANSACTION_SOURCE_TYPE_ID: '||l_transaction_source_type_id);
944 mdebug('L_TRANSACTION_SOURCE: '||l_transaction_source);
945 mdebug('L_RESERVATION_ID: '||l_reservation_id);
946 mdebug('L_EQUIPMENT_ID: '||l_equipment_id);
947 mdebug('L_USER_ID: '||l_user_id);
948 mdebug('L_USER_NAME: '||l_user_name);
949 mdebug('L_TASK_TYPE_ID: '||l_task_type_id);
950 mdebug('L_TASK_ID: '|| l_task_id);
951 mdebug('L_transaction_temp_id: '|| l_transaction_temp_id);
952 mdebug('L_update_status_method: '||l_update_status_method);
953 mdebug('l_program_control_arg1: '||l_program_control_arg1);
954 mdebug('l_program_control_arg2: '||l_program_control_arg2);
955 mdebug('l_program_control_arg3: '||l_program_control_arg3);
956 mdebug('l_program_control_arg4: '||l_program_control_arg4);
957 mdebug('l_program_control_arg5: '||l_program_control_arg5);
958 mdebug('l_program_control_arg6: '||l_program_control_arg6);
959 END IF;
960
961
962 -- calling workflow
963
964 -- make sure that reason name is not null
965 if (p_reason_id is null ) then
966 fnd_message.set_name('INV','INV_FIELD_INVALID');
967 fnd_msg_pub.ADD;
968 RAISE fnd_api.g_exc_error;
969 end if;
970
971 IF (l_debug = 1) THEN
972 mdebug('Before Select WORKFLOW_NAME, WORKFLOW_PROCESS ');
973 END IF;
974 -- get workflow_name and workflow_process from mtl_transaction_reasons.
975 -- This is needed to create the workflow process
976 SELECT WORKFLOW_NAME, WORKFLOW_PROCESS
977 INTO l_workflow_name, l_workflow_process
978 FROM MTL_TRANSACTION_REASONS
979 WHERE REASON_ID = P_REASON_ID ;
980
981 IF (l_debug = 1) THEN
982 mdebug('Workflow name is: '|| l_workflow_name);
983 mdebug('Workflow process: '|| l_workflow_process);
984 END IF;
985
986 -- generate item key using sequence number and concat with txnworkflow 'twflow'.
987 -- This is needed to create the workflow process
988 SELECT WMS_DISPATCHED_TASKS_S.nextval INTO l_sequence_number FROM DUAL ;
989
990 l_item_key := 'twflow' || l_sequence_number ;
991 IF (l_debug = 1) THEN
992 mdebug('Item key is: '|| l_item_key);
993 END IF;
994
995 -- initialize workflow
999
996 wf_engine.CreateProcess(itemtype => l_workflow_name,
997 itemkey => l_item_key,
998 process => l_workflow_process);
1000 -- set the attribute values of workflow with the local parameters
1001 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1002 itemkey => l_item_key,
1003 aname => 'PW_REASON_ID',
1004 avalue => P_REASON_ID);
1005
1006 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1007 itemkey => l_item_key,
1008 aname => 'PW_CALLING_PROGRAM_NAME',
1009 avalue => P_CALLING_PROGRAM_NAME);
1010
1011 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1012 itemkey => l_item_key,
1013 aname => 'PW_SOURCE_ORGANIZATION_ID',
1014 avalue => P_SOURCE_ORGANIZATION_ID);
1015 l_error:=100;
1016 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1017 itemkey => l_item_key,
1018 aname => 'PW_SOURCE_ORGANIZATION_N',
1019 avalue => L_SOURCE_ORGANIZATION_N);
1020 l_error:=101;
1021 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1022 itemkey => l_item_key,
1023 aname => 'PW_REASON_NAME',
1024 avalue => L_REASON_NAME);
1025
1026 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1027 itemkey => l_item_key,
1028 aname => 'PW_DESTINATION_ORGANIZATION_ID',
1029 avalue => L_DESTINATION_ORGANIZATION_ID);
1030
1031 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1032 itemkey => l_item_key,
1033 aname => 'PW_SOURCE_SUBINVENTORY',
1034 avalue => L_SOURCE_SUBINVENTORY);
1035
1036 wf_engine.setitemattrNumber(itemtype => l_workflow_name,
1037 itemkey => l_item_key,
1038 aname => 'PW_SOURCE_SUBINVENTORY_STATUS',
1039 avalue => L_SOURCE_SUBINVENTORY_STATUS);
1040
1041 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1042 itemkey => l_item_key,
1043 aname => 'PW_DESTINATION_SUBINVENTORY',
1044 avalue => L_DESTINATION_SUBINVENTORY);
1045
1046 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1047 itemkey => l_item_key,
1048 aname => 'PW_DESTINATION_SUBINVENTORY_ST',
1049 avalue => L_DESTINATION_SUBINVENTORY_ST);
1050
1051 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1052 itemkey => l_item_key,
1053 aname => 'PW_SOURCE_LOCATOR',
1054 avalue => L_SOURCE_LOCATOR);
1055
1056 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1057 itemkey => l_item_key,
1058 aname => 'PW_SOURCE_LOCATOR_N',
1059 avalue => L_SOURCE_LOCATOR_N);
1060
1061 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1062 itemkey => l_item_key,
1063 aname => 'PW_SOURCE_LOCATOR_STATUS',
1064 avalue => L_SOURCE_LOCATOR_STATUS);
1065
1066 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1067 itemkey => l_item_key,
1068 aname => 'PW_DESTINATION_LOCATOR',
1069 avalue => L_DESTINATION_LOCATOR);
1070
1071 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1072 itemkey => l_item_key,
1073 aname => 'PW_DESTINATION_LOCATOR_STATUS',
1074 avalue => L_DESTINATION_LOCATOR_STATUS);
1075
1076 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1077 itemkey => l_item_key,
1078 aname => 'PW_LPN_ID',
1079 avalue => L_LPN_ID);
1080
1081 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1082 itemkey => l_item_key,
1083 aname => 'PW_LPN_N',
1084 avalue => L_LPN_N);
1085
1086 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1087 itemkey => l_item_key,
1088 aname => 'PW_LPN_STATUS',
1089 avalue => L_LPN_STATUS);
1090
1091 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1092 itemkey => l_item_key,
1093 aname => 'PW_CONTENT_LPN_ID',
1094 avalue => L_CONTENT_LPN_ID);
1095
1096 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1097 itemkey => l_item_key,
1098 aname => 'PW_CONTENT_LPN_STATUS',
1099 avalue => L_CONTENT_LPN_STATUS);
1100
1101 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1102 itemkey => l_item_key,
1103 aname => 'PW_SOURCE_PARENT_LPN_ID',
1104 avalue => L_SOURCE_PARENT_LPN_ID);
1105
1106 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1107 itemkey => l_item_key,
1108 aname => 'PW_SOURCE_PARENT_LPN_STATUS',
1109 avalue => L_SOURCE_PARENT_LPN_STATUS);
1110
1111 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1112 itemkey => l_item_key,
1113 aname => 'PW_SOURCE_OUTERMOST_LPN_ID',
1114 avalue => L_SOURCE_OUTERMOST_LPN_ID);
1115
1116 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1117 itemkey => l_item_key,
1118 aname => 'PW_SOURCE_OUTERMOST_LPN_STATUS',
1119 avalue => L_SOURCE_OUTERMOST_LPN_STATUS);
1120
1121 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1122 itemkey => l_item_key,
1123 aname => 'PW_DEST_LPN_ID',
1124 avalue => L_DEST_LPN_ID);
1125
1126 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1127 itemkey => l_item_key,
1128 aname => 'PW_DEST_LPN_STATUS',
1129 avalue => L_DEST_LPN_STATUS);
1133 aname => 'PW_DEST_PARENT_LPN_ID',
1130
1131 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1132 itemkey => l_item_key,
1134 avalue => L_DEST_PARENT_LPN_ID);
1135
1136 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1137 itemkey => l_item_key,
1138 aname => 'PW_DEST_PARENT_LPN_STATUS',
1139 avalue => L_DEST_PARENT_LPN_STATUS);
1140
1141 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1142 itemkey => l_item_key,
1143 aname => 'PW_DEST_OUTERMOST_LPN_ID',
1144 avalue => L_DEST_OUTERMOST_LPN_ID);
1145
1146 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1147 itemkey => l_item_key,
1148 aname => 'PW_DEST_OUTERMOST_LPN_STATUS',
1149 avalue => L_DEST_OUTERMOST_LPN_STATUS);
1150
1151 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1152 itemkey => l_item_key,
1153 aname => 'PW_INVENTORY_ITEM_ID',
1154 avalue => L_INVENTORY_ITEM_ID);
1155
1156 wf_engine.setitemattrtext(itemtype => l_workflow_name,
1157 itemkey => l_item_key,
1158 aname => 'PW_INVENTORY_ITEM_NAME',
1159 avalue => L_INVENTORY_ITEM_NAME);
1160
1161 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1162 itemkey => l_item_key,
1163 aname => 'PW_REVISION',
1164 avalue => L_REVISION);
1165
1166 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1167 itemkey => l_item_key,
1168 aname => 'PW_LOT_NUMBER',
1169 avalue => L_LOT_NUMBER);
1170
1171 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1172 itemkey => l_item_key,
1173 aname => 'PW_TO_LOT_NUMBER',
1174 avalue => L_TO_LOT_NUMBER);
1175
1176 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1177 itemkey => l_item_key,
1178 aname => 'PW_LOT_STATUS',
1179 avalue => L_LOT_STATUS);
1180
1181 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1182 itemkey => l_item_key,
1183 aname => 'PW_SERIAL_NUMBER',
1184 avalue => L_SERIAL_NUMBER);
1185
1186 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1187 itemkey => l_item_key,
1188 aname => 'PW_TO_SERIAL_NUMBER',
1189 avalue => L_TO_SERIAL_NUMBER);
1190
1191 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1192 itemkey => l_item_key,
1193 aname => 'PW_SERIAL_NUMBER_STATUS',
1194 avalue => L_SERIAL_NUMBER_STATUS);
1195
1196 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1197 itemkey => l_item_key,
1198 aname => 'PW_PRIMARY_UOM',
1199 avalue => L_PRIMARY_UOM);
1200
1201 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1202 itemkey => l_item_key,
1203 aname => 'PW_TRANSACTION_UOM',
1204 avalue => L_TRANSACTION_UOM);
1205
1206 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1207 itemkey => l_item_key,
1208 aname => 'PW_PRIMARY_QUANTITY',
1209 avalue => L_PRIMARY_QUANTITY);
1210
1211 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1212 itemkey => l_item_key,
1213 aname => 'PW_TRANSACTION_QUANTITY',
1214 avalue => L_TRANSACTION_QUANTITY);
1215
1216 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1217 itemkey => l_item_key,
1218 aname => 'PW_TRANSACTION_HEADER_ID',
1219 avalue => L_TRANSACTION_HEADER_ID);
1220
1221 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1222 itemkey => l_item_key,
1223 aname => 'PW_TRANSACTION_MO_LINE_ID',
1224 avalue => L_MO_LINE_ID);
1225
1226 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1227 itemkey => l_item_key,
1228 aname => 'PW_TRANSACTION_ACTION_ID',
1229 avalue => L_TRANSACTION_ACTION_ID);
1230
1231 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1232 itemkey => l_item_key,
1233 aname => 'PW_TRANSACTION_SOURCE_TYPE_ID',
1234 avalue => L_TRANSACTION_SOURCE_TYPE_ID);
1235
1236 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1237 itemkey => l_item_key,
1238 aname => 'PW_TRANSACTION_SOURCE',
1239 avalue => L_TRANSACTION_SOURCE);
1240
1241 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1242 itemkey => l_item_key,
1243 aname => 'PW_RESERVATION_ID',
1244 avalue => L_RESERVATION_ID);
1245
1246 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1247 itemkey => l_item_key,
1248 aname => 'PW_EQUIPMENT_ID',
1249 avalue => L_EQUIPMENT_ID);
1250
1251 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1252 itemkey => l_item_key,
1253 aname => 'PW_USER_ID',
1254 avalue => L_USER_ID);
1255
1256 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1257 itemkey => l_item_key,
1258 aname => 'PW_USER_NAME',
1259 avalue => L_USER_NAME);
1260
1261 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1262 itemkey => l_item_key,
1263 aname => 'PW_TASK_TYPE_ID',
1264 avalue => L_TASK_TYPE_ID);
1265
1266 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1270
1267 itemkey => l_item_key,
1268 aname => 'PW_TASK_ID',
1269 avalue => L_TASK_ID);
1271 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1272 itemkey => l_item_key,
1273 aname => 'PW_TXN_TEMP_ID',
1274 avalue => L_transaction_temp_id);
1275
1276 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1277 itemkey => l_item_key,
1278 aname => 'PW_UPDATE_STATUS_METHOD',
1279 avalue => L_UPDATE_STATUS_METHOD);
1280
1281 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1282 itemkey => l_item_key,
1283 aname => 'PW_PROGRAM_CONTROL_ARG1',
1284 avalue => L_PROGRAM_CONTROL_ARG1);
1285
1286 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1287 itemkey => l_item_key,
1288 aname => 'PW_PROGRAM_CONTROL_ARG2',
1289 avalue => L_PROGRAM_CONTROL_ARG2);
1290
1291 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1292 itemkey => l_item_key,
1293 aname => 'PW_PROGRAM_CONTROL_ARG3',
1294 avalue => L_PROGRAM_CONTROL_ARG3);
1295
1296 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1297 itemkey => l_item_key,
1298 aname => 'PW_PROGRAM_CONTROL_ARG4',
1299 avalue => L_PROGRAM_CONTROL_ARG4);
1300
1301 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1302 itemkey => l_item_key,
1303 aname => 'PW_PROGRAM_CONTROL_ARG5',
1304 avalue => L_PROGRAM_CONTROL_ARG5);
1305
1306 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1307 itemkey => l_item_key,
1308 aname => 'PW_PROGRAM_CONTROL_ARG6',
1309 avalue => L_PROGRAM_CONTROL_ARG6);
1310
1311 -- start workflow
1312 IF (l_debug = 1) THEN
1313 mdebug('Before wf_engine.StartProcess of: ' || l_workflow_name);
1314 END IF;
1315 wf_engine.StartProcess (itemtype => l_workflow_name,
1316 itemkey => l_item_key);
1317
1318
1319
1320 -- on completion of the workflow, the output parameters are populated with the
1321 -- workflow attribute values
1322 X_RETURN_STATUS := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1323 itemkey => l_item_key,
1324 aname => 'XW_RETURN_STATUS');
1325
1326 X_MSG_DATA := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1327 itemkey => l_item_key,
1328 aname => 'XW_MSG_DATA');
1329
1330 X_MSG_COUNT := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1331 itemkey => l_item_key,
1332 aname => 'XW_MSG_COUNT');
1333
1334 X_ORGANIZATION_ID:= wf_engine.GetItemAttrNumber(itemtype=> l_workflow_name,
1335 itemkey => l_item_key,
1336 aname => 'XW_ORGANIZATION_ID');
1337
1338 X_SUBINVENTORY := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1339 itemkey => l_item_key,
1340 aname => 'XW_SUBINVENTORY');
1341
1342 X_SUBINVENTORY_STATUS:=wf_engine.GetItemAttrNumber(itemtype=> l_workflow_name,
1343 itemkey => l_item_key,
1344 aname => 'XW_SUBINVENTORY_STATUS');
1345
1346 X_LOCATOR := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1347 itemkey => l_item_key,
1348 aname => 'XW_LOCATOR');
1349
1350 X_LOCATOR_STATUS:= wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1351 itemkey => l_item_key,
1352 aname => 'XW_LOCATOR_STATUS');
1353
1354 X_LPN_ID := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1355 itemkey => l_item_key,
1356 aname => 'XW_LPN_ID');
1357
1358 X_LPN_STATUS := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1359 itemkey => l_item_key,
1360 aname => 'XW_LPN_STATUS');
1361
1362 X_INVENTORY_ITEM_ID:=wf_engine.GetItemAttrNumber(itemtype=> l_workflow_name,
1363 itemkey => l_item_key,
1364 aname => 'XW_INVENTORY_ITEM_ID');
1365
1366 X_REVISION := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1367 itemkey => l_item_key,
1368 aname => 'XW_REVISION');
1369
1370 X_LOT_NUMBER := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1371 itemkey => l_item_key,
1372 aname => 'XW_LOT_NUMBER');
1373
1374 X_LOT_STATUS := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1375 itemkey => l_item_key,
1376 aname => 'XW_LOT_STATUS');
1377
1378 X_QUANTITY := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1379 itemkey => l_item_key,
1380 aname => 'XW_QUANTITY');
1381
1382 X_UOM_CODE := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1383 itemkey => l_item_key,
1384 aname => 'XW_UOM_CODE');
1385
1386 X_PRIMARY_QUANTITY:=wf_engine.GetItemAttrNumber(itemtype=> l_workflow_name,
1387 itemkey => l_item_key,
1388 aname => 'XW_PRIMARY_QUANTITY');
1389
1390 X_TRANSACTION_QUANTITY:=wf_engine.GetItemAttrNumber(itemtype=> l_workflow_name,
1391 itemkey => l_item_key,
1392 aname => 'XW_TRANSACTION_QUANTITY');
1393
1394 X_RESERVATION_ID := wf_engine.GetItemAttrNumber(itemtype=> l_workflow_name,
1395 itemkey => l_item_key,
1396 aname => 'XW_RESERVATION_ID');
1397 BEGIN
1401 WHERE
1398 UPDATE wms_exceptions
1399 SET
1400 wf_item_key = l_item_key
1402 transaction_header_id = l_transaction_header_id;
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 IF (l_debug = 1) THEN
1406 mdebug('exception while updating the workflow item key ');
1407 END IF;
1408 END;
1409
1410
1411 EXCEPTION
1412
1413 WHEN FND_API.G_EXC_ERROR THEN
1414 IF (l_debug = 1) THEN
1415 mdebug('exception:FND_API.G_EXC_ERROR at l_error: '||l_error);
1416 END IF;
1417 ROLLBACK TO wf_start_workflow_pvt;
1418 x_return_status := FND_API.G_RET_STS_ERROR;
1419 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1420 ,p_data => x_msg_data);
1421
1422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1423 IF (l_debug = 1) THEN
1424 mdebug('exception: FND_API.G_EXC_UNEXPECTED_ERROR at l_error: '||l_error);
1425 END IF;
1426 ROLLBACK TO wf_start_workflow_pvt;
1427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1428 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1429 ,p_data => x_msg_data);
1430
1431 WHEN OTHERS THEN
1432 IF (l_debug = 1) THEN
1433 mdebug('exception: in when otheres at l_error: '||l_error);
1434 END IF;
1435 ROLLBACK TO wf_start_workflow_pvt;
1436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1437 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1438 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1439 END IF;
1440 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1441 , p_data => x_msg_data);
1442 END wf_start_workflow;
1443
1444
1445 PROCEDURE WF_SUGGEST_ALT_LOC (itemtype IN VARCHAR2,
1446 itemkey IN VARCHAR2,
1447 actid IN NUMBER,
1448 funcmode IN VARCHAR2,
1449 result OUT NOCOPY VARCHAR2)
1450 IS
1451
1452 -- local variables
1453 l_workflow_name VARCHAR2(250) ;
1454 l_item_key VARCHAR2(250) ;
1455 lp_api_version_number NUMBER := 1.0;
1456 lp_init_msg_lst VARCHAR2(250) := FND_API.G_FALSE;
1457 lp_commit VARCHAR2(250) := FND_API.G_FALSE;
1458 lx_return_status VARCHAR2(1) ;
1459 lx_msg_count NUMBER ;
1460 lx_msg_data VARCHAR2(250);
1461 lp_organization_id NUMBER;
1462 lp_task_id NUMBER;
1463 lp_qty_picked NUMBER := 0;
1464 lp_qty_uom VARCHAR2(3);
1465 lp_carton_id VARCHAR2(250) := NULL;
1466 lp_user_id VARCHAR2(250);
1467 lp_reason_id NUMBER;
1468 lp_mmtt_id NUMBER;
1469 lp_locator_id NUMBER;
1470 lp_sub_code VARCHAR2(10);
1471 lp_line_num NUMBER;
1472
1473
1474 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1475 BEGIN
1476 IF (l_debug = 1) THEN
1477 mdebug('In WMS_Suggest_Alt_Loc');
1478 END IF;
1479
1480 l_workflow_name := itemtype;
1481 l_item_key := itemkey;
1482
1483
1484 -- populating the local procedure variables with the corresponding attributes from workflow
1485 lp_organization_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1486 itemkey => l_item_key,
1487 aname => 'PW_SOURCE_ORGANIZATION_ID');
1488
1489 lp_task_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1490 itemkey => l_item_key,
1491 aname => 'PW_TASK_ID');
1492
1493 -- MRANA: added this to get the temp_id instead of querying WDT to get it
1494 -- ALSO ,. deleted the query to wdt to get the temp_id based on task_id (pw_task_id)
1495 lp_mmtt_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1496 itemkey => l_item_key,
1497 aname => 'PW_TXN_TEMP_ID');
1498
1499 lp_qty_picked := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1500 itemkey => l_item_key,
1501 aname => 'PW_TRANSACTION_QUANTITY');
1502
1503 lp_qty_uom := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1504 itemkey => l_item_key,
1505 aname => 'PW_TRANSACTION_UOM');
1506
1507 lp_carton_id := wf_engine.getItemAttrText(itemtype => l_workflow_name,
1508 itemkey => l_item_key,
1509 aname => 'PW_LPN_ID');
1510
1511 lp_user_id := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1512 itemkey => l_item_key,
1513 aname => 'PW_USER_ID');
1514
1515 lp_reason_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1516 itemkey => l_item_key,
1517 aname => 'PW_REASON_NAME');
1518 IF (l_debug = 1) THEN
1519 mdebug('before select temp id');
1520 END IF;
1521 -- get data to call suggest_alternate_locatoin
1522
1523 SELECT subinventory_code,locator_id, move_order_line_id
1524 INTO lp_sub_code,lp_locator_id, lp_line_num
1528 mdebug('before calling wms_txnrsn_actions_pub.suggest_alternate_location ');
1525 FROM mtl_material_transactions_temp
1526 WHERE transaction_temp_id=lp_mmtt_id;
1527 IF (l_debug = 1) THEN
1529 END IF;
1530
1531 g_return_status := FND_API.G_RET_STS_SUCCESS; --Bug 6116046
1532 mdebug('Setting g_return_status to success');
1533 wms_txnrsn_actions_pub.suggest_alternate_location
1534 (p_api_version_number =>lp_api_version_number
1535 , p_init_msg_lst =>lp_init_msg_lst
1536 , p_commit =>lp_commit
1537 , x_return_status =>lx_return_status
1538 , x_msg_count =>lx_msg_count
1539 , x_msg_data =>lx_msg_data
1540 , p_organization_id =>lp_organization_id
1541 , p_mmtt_id =>lp_mmtt_id
1542 , p_task_id =>lp_task_id
1543 , p_subinventory_code =>lp_sub_code
1544 , p_locator_id =>lp_locator_id
1545 , p_carton_id =>lp_carton_id
1546 , p_user_id =>lp_user_id
1547 , p_qty_picked =>lp_qty_picked
1548 , p_line_num =>lp_line_num
1549 );
1550
1551 IF (l_debug = 1) THEN
1552 mdebug('After calling wms_txnrsn_actions_pub.suggest_alternate_location');
1553 END IF;
1554 -- setting the workflow attributes with the output results of
1555 -- the API wms_txnrsn_actions_pub.Inadequate_Qty
1556 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1557 itemkey => l_item_key,
1558 aname => 'XW_RETURN_STATUS',
1559 avalue => lx_return_status);
1560
1561 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1562 itemkey => l_item_key,
1563 aname => 'XW_MSG_COUNT',
1564 avalue => lx_msg_count);
1565
1566 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1567 itemkey => l_item_key,
1568 aname => 'XW_MSG_DATA',
1569 avalue => lx_msg_data);
1570
1571 g_return_status := lx_return_status; --Bug 6116046
1572 mdebug('Setting g_returnstatus to success');
1573 -- check for errors
1574 fnd_msg_pub.count_and_get
1575 ( p_count => lx_msg_count
1576 , p_data => lx_msg_data
1577 );
1578
1579 IF (lx_msg_count = 0) THEN
1580 IF (l_debug = 1) THEN
1581 mdebug('Inadequate quantity successful');
1582 END IF;
1583 ELSIF (lx_msg_count = 1) THEN
1584 IF (l_debug = 1) THEN
1585 mdebug(replace(lx_msg_data,chr(0),' '));
1586 END IF;
1587 ELSE
1588 For I in 1..lx_msg_count LOOP
1589 lx_msg_data := fnd_msg_pub.get(I,'F');
1590 IF (l_debug = 1) THEN
1591 mdebug(replace(lx_msg_data,chr(0),' '));
1592 END IF;
1593 END LOOP;
1594 END IF;
1595
1596 EXCEPTION
1597
1598 WHEN fnd_api.g_exc_error THEN
1599 lx_return_status := fnd_api.g_ret_sts_error;
1600 --Bug 6116046 Begin
1601 mdebug('Setting g_return_status to fnd_api.g_ret_sts_error');
1602 g_return_status := fnd_api.g_ret_sts_error; --Bug 6116046
1603
1604 mdebug('exception: fnd_api.g_exc_error');
1605 mdebug('wf_suggest_alt_loc lx_return_status to ' || lx_return_status);
1606
1607 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1608 itemkey => l_item_key,
1609 aname => 'XW_RETURN_STATUS',
1610 avalue => lx_return_status);
1611
1612 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1613 itemkey => l_item_key,
1614 aname => 'XW_MSG_COUNT',
1615 avalue => lx_msg_count);
1616
1617 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1618 itemkey => l_item_key,
1619 aname => 'XW_MSG_DATA',
1620 avalue => lx_msg_data);
1621
1622 -- check for errors
1623 fnd_msg_pub.count_and_get
1624 ( p_count => lx_msg_count
1625 , p_data => lx_msg_data
1626 );
1627
1628 IF (lx_msg_count = 0) THEN
1629 IF (l_debug = 1) THEN
1630 mdebug('Inadequate quantity successful');
1631 END IF;
1632 ELSIF (lx_msg_count = 1) THEN
1633 IF (l_debug = 1) THEN
1634 mdebug(replace(lx_msg_data,chr(0),' '));
1635 END IF;
1636 ELSE
1637 For I in 1..lx_msg_count LOOP
1638 lx_msg_data := fnd_msg_pub.get(I,'F');
1639 IF (l_debug = 1) THEN
1640 mdebug(replace(lx_msg_data,chr(0),' '));
1641 END IF;
1642 END LOOP;
1643 END IF;
1644
1645 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1646 THEN
1647 fnd_msg_pub.add_exc_msg
1648 ( g_pkg_name
1649 , 'WMS_Inadequate_Quantity'
1650 );
1651 END IF;
1652
1653
1654 WHEN fnd_api.g_exc_unexpected_error THEN
1655 lx_return_status := fnd_api.g_ret_sts_unexp_error ;
1656
1657 mdebug('Setting g_return_status to fnd_api.g_ret_sts_unexp_error');
1658
1662 mdebug('wf_suggest_alt_loc lx_return_status to ' || lx_return_status);
1659 g_return_status := fnd_api.g_ret_sts_unexp_error; --Bug 6116046
1660
1661 mdebug('exception: fnd_api.g_exc_unexpected_error');
1663
1664 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1665 itemkey => l_item_key,
1666 aname => 'XW_RETURN_STATUS',
1667 avalue => lx_return_status);
1668
1669 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1670 itemkey => l_item_key,
1671 aname => 'XW_MSG_COUNT',
1672 avalue => lx_msg_count);
1673
1674 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1675 itemkey => l_item_key,
1676 aname => 'XW_MSG_DATA',
1677 avalue => lx_msg_data);
1678
1679 -- check for errors
1680 fnd_msg_pub.count_and_get
1681 ( p_count => lx_msg_count
1682 , p_data => lx_msg_data
1683 );
1684
1685 IF (lx_msg_count = 0) THEN
1686 IF (l_debug = 1) THEN
1687 mdebug('Inadequate quantity successful');
1688 END IF;
1689 ELSIF (lx_msg_count = 1) THEN
1690 IF (l_debug = 1) THEN
1691 mdebug(replace(lx_msg_data,chr(0),' '));
1692 END IF;
1693 ELSE
1694 For I in 1..lx_msg_count LOOP
1695 lx_msg_data := fnd_msg_pub.get(I,'F');
1696 IF (l_debug = 1) THEN
1697 mdebug(replace(lx_msg_data,chr(0),' '));
1698 END IF;
1699 END LOOP;
1700 END IF;
1701
1702 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1703 THEN
1704 fnd_msg_pub.add_exc_msg
1705 ( g_pkg_name
1706 , 'WMS_Inadequate_Quantity'
1707 );
1708 END IF;
1709
1710
1711
1712 WHEN OTHERS THEN
1713 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1714 THEN
1715 fnd_msg_pub.add_exc_msg
1716 ( g_pkg_name
1717 , 'WMS_Inadequate_Quantity'
1718 );
1719 END IF;
1720
1721
1722 mdebug('exception: fnd_api.g_exc_unexpected_error');
1723 mdebug('wf_suggest_alt_loc lx_return_status to ' || lx_return_status);
1724 g_return_status := fnd_api.g_ret_sts_unexp_error; --Bug 6116046
1725 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1726 itemkey => l_item_key,
1727 aname => 'XW_RETURN_STATUS',
1728 avalue => lx_return_status);
1729
1730 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1731 itemkey => l_item_key,
1732 aname => 'XW_MSG_COUNT',
1733 avalue => lx_msg_count);
1734
1735 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1736 itemkey => l_item_key,
1737 aname => 'XW_MSG_DATA',
1738 avalue => lx_msg_data);
1739
1740 -- check for errors
1741 fnd_msg_pub.count_and_get
1742 ( p_count => lx_msg_count
1743 , p_data => lx_msg_data
1744 );
1745
1746 IF (lx_msg_count = 0) THEN
1747 IF (l_debug = 1) THEN
1748 mdebug('Inadequate quantity successful');
1749 END IF;
1750 ELSIF (lx_msg_count = 1) THEN
1751 IF (l_debug = 1) THEN
1752 mdebug(replace(lx_msg_data,chr(0),' '));
1753 END IF;
1754 ELSE
1755 For I in 1..lx_msg_count LOOP
1756 lx_msg_data := fnd_msg_pub.get(I,'F');
1757 IF (l_debug = 1) THEN
1758 mdebug(replace(lx_msg_data,chr(0),' '));
1759 END IF;
1760 END LOOP;
1761 END IF;
1762
1763 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1764 THEN
1765 fnd_msg_pub.add_exc_msg
1766 ( g_pkg_name
1767 , 'WMS_Inadequate_Quantity'
1768 );
1769 END IF;
1770
1771 --Bug 6116046 End
1772
1773 END wf_suggest_alt_loc;
1774
1775
1776
1777
1778
1779
1780 /*==================================================================================*/
1781 -- This procedure does the following:
1782 -- - Creates a cycle count request when there is insufficient quantity
1783 -- to be picked.
1784
1785 PROCEDURE wf_Cycle_Count (itemtype IN VARCHAR2,
1786 itemkey IN VARCHAR2,
1787 actid IN NUMBER,
1788 funcmode IN VARCHAR2,
1789 result OUT NOCOPY VARCHAR2)
1790 IS
1791
1792 -- local variables
1793 x_return_status VARCHAR2(30);
1794 x_msg_count NUMBER;
1795 x_msg_data VARCHAR2(240);
1796
1797 l_workflow_name VARCHAR2(250);
1798 l_item_key VARCHAR2(250);
1799
1800 l_organization_id NUMBER;
1801 l_subinventory_code VARCHAR2(250);
1802 l_locator_id NUMBER;
1803 l_inventory_item_id NUMBER; --BUG #2867331
1804
1805 lmsg varchar(300); /*for debugging cycle count call*/
1806 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1807 BEGIN
1808 IF (l_debug = 1) THEN
1809 mdebug('In Cycle_Count');
1810 END IF;
1811 -- set itemtype and itemkey to local variables
1812 l_workflow_name := itemtype;
1813 l_item_key := itemkey;
1814
1818 itemkey => l_item_key,
1815
1816 -- set workflow attributes to local parameters
1817 l_organization_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1819 aname => 'PW_SOURCE_ORGANIZATION_ID');
1820
1821 l_subinventory_code := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
1822 itemkey => l_item_key,
1823 aname => 'PW_SOURCE_SUBINVENTORY');
1824
1825
1826 l_locator_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1827 itemkey => l_item_key,
1828 aname => 'PW_SOURCE_LOCATOR');
1829 --BUG #2867331
1830 l_inventory_item_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
1831 itemkey => l_item_key,
1832 aname => 'PW_INVENTORY_ITEM_ID');
1833 --Bug 6116046 Begin
1834 mdebug('g_return_status value is' || g_return_status);
1835
1836 IF (g_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1837 IF (l_debug = 1) THEN
1838 mdebug('Throwing exception as g_return_status is not success');
1839 END IF;
1840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1841 END IF;
1842 --Bug 6116046 End
1843
1844 IF (l_debug = 1) THEN
1845 mdebug('before calling wms_cycle_pvt.create_unscheduled_counts');
1846 mdebug('l_organization_id: '||l_organization_id);
1847 mdebug('l_subinventory_code: '||l_subinventory_code);
1848 mdebug('l_locator_id: '||l_locator_id);
1849 mdebug('l_inventory_item_id: '||l_inventory_item_id); --BUG #2867331
1850 END IF;
1851
1852 -- call a cycle count request for this location.
1853 wms_cycle_pvt.create_unscheduled_counts
1854 ( p_api_version => 1.0,
1855 p_init_msg_list => fnd_api.g_false,
1856 p_commit => fnd_api.g_false,
1857 x_return_status => x_return_status,
1858 x_msg_count => x_msg_count,
1859 x_msg_data => x_msg_data,
1860 p_organization_id => l_organization_id,
1861 p_subinventory => l_subinventory_code,
1862 p_locator_id => l_locator_id,
1863 p_inventory_item_id => l_inventory_item_id); --BUG #2867331
1864
1865 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1866 IF (l_debug = 1) THEN
1867 mdebug('wms_cycle_pvt.create_unscheduled_counts failed');
1868 END IF;
1869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1870 END IF;
1871 IF (l_debug = 1) THEN
1872 mdebug('after calling wms_cycle_pvt.create_unscheduled_counts');
1873 END IF;
1874 -- debugging for cycle count
1875
1876 for x in 1..x_msg_count loop
1877 lmsg := fnd_msg_pub.get;
1878 IF (l_debug = 1) THEN
1879 mdebug(x||':'||substr(lmsg, 0, 240));
1880 END IF;
1881 end loop;
1882
1883 -- set outputs
1884 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1885 itemkey => l_item_key,
1886 aname => 'XW_RETURN_STATUS',
1887 avalue => x_return_status);
1888
1889 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1890 itemkey => l_item_key,
1891 aname => 'XW_MSG_COUNT',
1892 avalue => x_msg_count);
1893
1894 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1895 itemkey => l_item_key,
1896 aname => 'XW_MSG_DATA',
1897 avalue => x_msg_data);
1898
1899 EXCEPTION
1900
1901 WHEN fnd_api.g_exc_error THEN
1902 IF (l_debug = 1) THEN
1903 mdebug('exc error in wf_cycle_count');
1904 END IF;
1905 x_return_status := fnd_api.g_ret_sts_error;
1906 fnd_msg_pub.count_and_get
1907 ( p_count => x_msg_count,
1908 p_data => x_msg_data
1909 );
1910
1911 --Bug 6116046 Begin
1912 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1913 itemkey => l_item_key,
1914 aname => 'XW_RETURN_STATUS',
1915 avalue => x_return_status);
1916
1917 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1918 itemkey => l_item_key,
1919 aname => 'XW_MSG_COUNT',
1920 avalue => x_msg_count);
1921
1922 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1923 itemkey => l_item_key,
1924 aname => 'XW_MSG_DATA',
1925 avalue => x_msg_data);
1926 --Bug 6116046 End
1927
1928 WHEN fnd_api.g_exc_unexpected_error THEN
1929 IF (l_debug = 1) THEN
1930 mdebug('unexpected error in wf_cycle_count');
1931 END IF;
1932 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1933 fnd_msg_pub.count_and_get
1934 ( p_count => x_msg_count,
1935 p_data => x_msg_data
1936 );
1937 --Bug 6116046 Begin
1938 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1939 itemkey => l_item_key,
1940 aname => 'XW_RETURN_STATUS',
1941 avalue => x_return_status);
1942
1943 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1944 itemkey => l_item_key,
1948 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1945 aname => 'XW_MSG_COUNT',
1946 avalue => x_msg_count);
1947
1949 itemkey => l_item_key,
1950 aname => 'XW_MSG_DATA',
1951 avalue => x_msg_data);
1952 --Bug 6116046 End
1953
1954
1955 WHEN OTHERS THEN
1956 IF (l_debug = 1) THEN
1957 mdebug('others error in wf_cycle_count');
1958 END IF;
1959 x_return_status := fnd_api.g_ret_sts_unexp_error;
1960 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1961 THEN
1962 fnd_msg_pub.add_exc_msg
1963 ( g_pkg_name
1964 , 'wf_cycle_count'
1965 );
1966 END IF;
1967 fnd_msg_pub.count_and_get
1968 ( p_count => x_msg_count,
1969 p_data => x_msg_data
1970 );
1971 --Bug 6116046 Begin
1972 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1973 itemkey => l_item_key,
1974 aname => 'XW_RETURN_STATUS',
1975 avalue => x_return_status);
1976
1977 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
1978 itemkey => l_item_key,
1979 aname => 'XW_MSG_COUNT',
1980 avalue => x_msg_count);
1981
1982 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
1983 itemkey => l_item_key,
1984 aname => 'XW_MSG_DATA',
1985 avalue => x_msg_data);
1986 --Bug 6116046 End
1987
1988 END wf_cycle_count ;
1989
1990
1991 --Checks to see if task manager is done. if 'Y' is returned, then
1992 -- the following variables are populated temporarily:
1993 -- PW_PROGRAM_ARG1 = header id
1994 -- XW_Return_Status can have value: 'Y','N' or 'E'
1995 -- if 'E' then error out.
1996
1997 PROCEDURE WF_is_task_processed (itemtype IN VARCHAR2,
1998 itemkey IN VARCHAR2,
1999 actid IN NUMBER,
2000 funcmode IN VARCHAR2,
2001 result OUT NOCOPY VARCHAR2)
2002 IS
2003
2004 x_return_status VARCHAR2(30);
2005 x_msg_count NUMBER;
2006 x_msg_data VARCHAR2(240);
2007 lx_processed VARCHAR2(10);
2008
2009 l_workflow_name VARCHAR2(250);
2010 l_item_key VARCHAR2(250);
2011 l_txn_header_id NUMBER;
2012
2013 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2014 BEGIN
2015 IF (l_debug = 1) THEN
2016 mdebug('In wf_is_task_processed');
2017 END IF;
2018
2019 -- set itemtype and itemkey to local variables
2020 l_workflow_name := itemtype;
2021 l_item_key := itemkey;
2022
2023
2024 l_txn_header_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2025 itemkey => l_item_key,
2026 aname => 'PW_TRANSACTION_HEADER_ID');
2027
2028 IF (l_debug = 1) THEN
2029 mdebug('txn header id '||l_txn_header_id);
2030 mdebug('before wms_task_utils_pvt.is_task_processed ');
2031 END IF;
2032
2033 wms_task_utils_pvt.is_task_processed
2034 ( x_processed => lx_processed,
2035 p_header_id => l_txn_header_id);
2036
2037 IF (l_debug = 1) THEN
2038 mdebug('after wms_task_utils_pvt.is_task_processed');
2039 mdebug('x_processed: '||lx_processed);
2040 END IF;
2041
2042 -- workflow will check whether x_processed is either 'Y','N' or 'E'
2043 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
2044 itemkey => l_item_key,
2045 aname => 'XW_RETURN_STATUS',
2046 avalue => lx_processed);
2047
2048 IF (Upper(lx_processed) = 'Y') THEN
2049 result :=wf_engine.eng_completed||':IS_TASK_PROCESS_YES';
2050
2051 ELSIF (Upper(lx_processed) = 'N') THEN
2052 result :=wf_engine.eng_completed||':IS_TASK_PROCESS_NO';
2053
2054 ELSE
2055 result :=wf_engine.eng_completed||':IS_TASK_PROCESS_ERROR';
2056 RAISE fnd_api.g_exc_error;
2057 END IF;
2058
2059 EXCEPTION
2060 WHEN fnd_api.g_exc_error THEN
2061 IF (l_debug = 1) THEN
2062 mdebug('exc error in wf_is_task_processed');
2063 END IF;
2064 x_return_status := fnd_api.g_ret_sts_error;
2065 fnd_msg_pub.count_and_get
2066 ( p_count => x_msg_count,
2067 p_data => x_msg_data
2068 );
2069
2070 WHEN fnd_api.g_exc_unexpected_error THEN
2071 IF (l_debug = 1) THEN
2072 mdebug('unexpected error in wf_is_task_processed');
2073 END IF;
2074 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2075 fnd_msg_pub.count_and_get
2076 ( p_count => x_msg_count,
2077 p_data => x_msg_data
2078 );
2079
2080
2081 WHEN OTHERS THEN
2082 IF (l_debug = 1) THEN
2083 mdebug('others error in wf_is_task_processed at');
2084 END IF;
2085 x_return_status := fnd_api.g_ret_sts_unexp_error;
2086 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2087 THEN
2088 fnd_msg_pub.add_exc_msg
2089 ( g_pkg_name
2090 , 'wf_is_task_processed'
2091 );
2092 END IF;
2093 fnd_msg_pub.count_and_get
2094 ( p_count => x_msg_count,
2095 p_data => x_msg_data
2096 );
2100
2097
2098 END wf_is_task_processed ;
2099
2101 PROCEDURE wf_generate_next_task (itemtype IN VARCHAR2,
2102 itemkey IN VARCHAR2,
2103 actid IN NUMBER,
2104 funcmode IN VARCHAR2,
2105 result OUT NOCOPY VARCHAR2)
2106 IS
2107
2108 lx_return_status VARCHAR2(30);
2109 lx_msg_count NUMBER;
2110 lx_msg_data VARCHAR2(240);
2111 lx_ret_code VARCHAR2(30);
2112 l_workflow_name VARCHAR2(250);
2113 l_item_key VARCHAR2(250);
2114
2115 l_header_id NUMBER;
2116 l_mo_line_id NUMBER;
2117 l_old_sub_code VARCHAR2(30);
2118 l_old_loc_id NUMBER;
2119 l_task_type_id NUMBER;
2120
2121 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2122 BEGIN
2123 IF (l_debug = 1) THEN
2124 mdebug('in wf_generate_next_task');
2125 END IF;
2126 -- set itemtype and itemkey to local variables
2127 l_workflow_name := itemtype;
2128 l_item_key := itemkey;
2129
2130 l_header_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2131 itemkey => l_item_key,
2132 aname =>
2133 'PW_TRANSACTION_HEADER_ID');
2134
2135 l_mo_line_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2136 itemkey => l_item_key,
2137 aname =>
2138 'PW_TRANSACTION_MO_LINE_ID');
2139
2140 l_old_sub_code := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
2141 itemkey => l_item_key,
2142 aname =>
2143 'PW_SOURCE_SUBINVENTORY');
2144
2145 l_old_loc_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2146 itemkey => l_item_key,
2147 aname =>
2148 'PW_SOURCE_LOCATOR');
2149
2150 l_task_type_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2151 itemkey => l_item_key,
2152 aname => 'PW_TASK_TYPE_ID');
2153
2154 -- call generate_next_task
2155 IF (l_debug = 1) THEN
2156 mdebug('before wms_task_utils_pvt.generate_next_task');
2157 mdebug('header_id '||l_header_id);
2158 mdebug('header_id '||l_mo_line_id);
2159 END IF;
2160
2161 wms_task_utils_pvt.generate_next_task
2162 ( x_return_status => lx_return_status,
2163 x_msg_count => lx_msg_count,
2164 x_msg_data => lx_msg_data,
2165 x_ret_code => lx_ret_code,
2166 p_old_header_id => l_header_id,
2167 p_mo_line_id => l_mo_line_id,
2168 p_old_sub_CODE => l_old_sub_code,
2169 p_old_loc_id => l_old_loc_id,
2170 p_wms_task_type => l_task_type_id );
2171
2172 IF (l_debug = 1) THEN
2173 mdebug('after wms_task_utils_pvt.generate_next_task');
2174 mdebug('x_ret_code: '||lx_ret_code);
2175 END IF;
2176
2177 IF (lx_return_status = fnd_api.g_ret_sts_success) THEN
2178 result :=wf_engine.eng_completed||':GEN_NEXT_TASK_YES';
2179 IF (l_debug = 1) THEN
2180 mdebug('In Generate Next Task -> Success');
2181 END IF;
2182 ELSE
2183 IF (Upper(lx_ret_code) = 'QTY_NOT_AVAIL') THEN
2184 result :=wf_engine.eng_completed||':GEN_NEXT_TASK_NO_QTY';
2185 IF (l_debug = 1) THEN
2186 mdebug('In Generate Next Task -> No Available Qty');
2187 END IF;
2188 ELSE
2189 result :=wf_engine.eng_completed||':GEN_NEXT_TASK_ERROR';
2190 IF (l_debug = 1) THEN
2191 mdebug('In Generate Next Task -> Error');
2192 END IF;
2193 END IF;
2194 END IF;
2195
2196 EXCEPTION
2197 WHEN fnd_api.g_exc_error THEN
2198 IF (l_debug = 1) THEN
2199 mdebug('exc error in wf_generate_next_task');
2200 END IF;
2201 lx_return_status := fnd_api.g_ret_sts_error;
2202 fnd_msg_pub.count_and_get
2203 ( p_count => lx_msg_count,
2204 p_data => lx_msg_data
2205 );
2206
2207 WHEN fnd_api.g_exc_unexpected_error THEN
2208 IF (l_debug = 1) THEN
2209 mdebug('unexpected error in wf_generate_next_task');
2210 END IF;
2211 lx_return_status := fnd_api.g_ret_sts_unexp_error ;
2212 fnd_msg_pub.count_and_get
2213 ( p_count => lx_msg_count,
2214 p_data => lx_msg_data
2215 );
2216
2217
2218 WHEN OTHERS THEN
2219 IF (l_debug = 1) THEN
2220 mdebug('others error in wf_generate_next_task');
2221 END IF;
2222 lx_return_status := fnd_api.g_ret_sts_unexp_error;
2223 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2224 THEN
2225 fnd_msg_pub.add_exc_msg
2226 ( g_pkg_name
2227 , 'wf_generate_next_task'
2228 );
2229 END IF;
2230 fnd_msg_pub.count_and_get
2231 ( p_count => lx_msg_count,
2232 p_data => lx_msg_data
2233 );
2234
2235 END wf_generate_next_task;
2236
2237 -- This procedure does nothing. It's just like a NOOP (a placeholder).
2238 -- It is associated with a high cost in workflow and will be sent to the
2239 -- background manager to be processed.
2240 PROCEDURE wf_send_to_bg(itemtype IN VARCHAR2,
2241 itemkey IN VARCHAR2,
2245
2242 actid IN NUMBER,
2243 funcmode IN VARCHAR2,
2244 result OUT NOCOPY VARCHAR2)
2246 IS
2247 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2248 BEGIN
2249 IF (l_debug = 1) THEN
2250 mdebug('In wf_send_to_bg');
2251 END IF;
2252 END wf_send_to_bg;
2253
2254 -- WMS_Insuff_Qty_Wrapper - This is a wrapper procedure that calls a workflow
2255 -- (given a reason id). As of now the only workflow called is 'Inadequate
2256 -- Quantity', with reason_id=184. This workflow needs as its input a task id,
2257 -- hence the parameter p_tsk_id. The parameter p_quantity_picked is the amount
2258 -- of item(s) that was actually picked by the user.
2259
2260
2261 PROCEDURE wms_insuff_qty_wrapper( p_api_version_number IN NUMBER
2262 , x_return_status OUT NOCOPY VARCHAR2
2263 , x_msg_count OUT NOCOPY NUMBER
2264 , x_msg_data OUT NOCOPY VARCHAR2
2265 , p_tsk_id IN NUMBER
2266 , p_user_id IN VARCHAR2
2267 , p_organization_id IN NUMBER
2268 , p_rsn_id IN NUMBER
2269 , p_quantity_picked IN NUMBER
2270 )
2271
2272 IS
2273 l_api_version CONSTANT NUMBER := 1.0;
2274 l_api_name CONSTANT VARCHAR2(30) := 'wms_insuff_qty_wrapper';
2275
2276 l_inventory_item_id NUMBER;
2277 l_subinventory_code VARCHAR2(250);
2278 l_transaction_temp_id NUMBER;
2279 l_locator_id NUMBER;
2280 l_transaction_uom VARCHAR2(3);
2281
2282 -- defining output variables
2283 lX_RETURN_STATUS VARCHAR2(250);
2284 lX_MSG_DATA VARCHAR2(250);
2285 lX_MSG_COUNT NUMBER;
2286 lX_REVISION VARCHAR2(250);
2287 lX_LOT_NUMBER VARCHAR2(250);
2288 lX_LOT_STATUS VARCHAR2(250);
2289 lX_SUBLOT_NUMBER VARCHAR2(250);
2290 lX_SUBLOT_STATUS VARCHAR2(250);
2291 lX_LPN_ID NUMBER;
2292 lX_LPN_STATUS VARCHAR2(250);
2293 lX_UOM_CODE VARCHAR2(250);
2294 lX_QUANTITY NUMBER;
2295 lX_INVENTORY_ITEM_ID NUMBER;
2296 lX_ORGANIZATION_ID NUMBER;
2297 lX_SUBINVENTORY VARCHAR2(250);
2298 lX_SUBINVENTORY_STATUS VARCHAR2(250);
2299 lX_LOCATOR NUMBER;
2300 lX_LOCATOR_STATUS VARCHAR2(250);
2301 lX_PRIMARY_QUANTITY NUMBER;
2302 lX_TRANSACTION_QUANTITY NUMBER;
2303 lX_NEXT_FORM VARCHAR2(250);
2304 lX_NEXT_MOBILE_FORM VARCHAR2(250);
2305 lX_NEXT_PLSQL_PROGRAM VARCHAR2(250);
2306 lX_RESERVATION_ID NUMBER;
2307 lX_IS_RESERVATION_SUCCESSFUL VARCHAR2(250);
2308 lX_IS_CYCLE_COUNT_SUCCESSFUL VARCHAR2(250);
2309
2310 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2311 BEGIN
2312 IF (l_debug = 1) THEN
2313 mdebug('In workflow wrapper');
2314 END IF;
2315 -- Set savepoint for this API
2316 SAVEPOINT wms_insuff_qty_PUB;
2317
2318 -- Initialize API return status to success
2319 x_return_status := fnd_api.g_ret_sts_success;
2320 IF p_tsk_id>0 THEN
2321 -- get necessary data to call workflow
2322 SELECT transaction_temp_id
2323 INTO l_transaction_temp_id
2324 FROM wms_dispatched_tasks
2325 WHERE task_id=p_tsk_id;
2326 IF (l_debug = 1) THEN
2327 mdebug('l_transaction_temp_id: '|| l_transaction_temp_id);
2328 END IF;
2329
2330 SELECT inventory_item_id, subinventory_code, transaction_uom, locator_id
2331 INTO l_inventory_item_id, l_subinventory_code, l_transaction_uom, l_locator_id
2332 FROM mtl_material_transactions_temp
2333 WHERE transaction_temp_id = l_transaction_temp_id;
2334
2335 IF (l_debug = 1) THEN
2336 mdebug('l_transaction_uom: '|| l_transaction_uom);
2337 mdebug('After the 2 select statements');
2338 END IF;
2339 wms_txnreasons_pub.Start_Workflow(
2340 P_REASON_ID => p_rsn_id
2341 ,P_REASON_NAME => NULL
2342 ,P_SOURCE_ORGANIZATION_ID => p_organization_id
2343 ,P_DESTINATION_ORGANIZATION_ID => NULL
2344 ,P_LPN_ID => NULL /* = carton_id */
2345 ,P_INVENTORY_ITEM_ID => l_inventory_item_id
2346 ,P_REVISION => NULL
2347 ,P_LOT_NUMBER => NULL
2348 ,P_LOT_STATUS => NULL
2349 ,P_SUBLOT_NUMBER => NULL
2350 ,P_SUBLOT_STATUS => NULL
2351 ,P_SOURCE_SUBINVENTORY => l_subinventory_code /* = subinventory_code from mmtt */
2352 ,P_SOURCE_SUBINVENTORY_STATUS => NULL
2353 ,P_DESTINATION_SUBINVENTORY => NULL
2354 ,P_DESTINATION_SUBINVENTORY_ST => NULL
2355 ,P_SOURCE_LOCATOR => l_locator_id
2356 ,P_SOURCE_LOCATOR_STATUS => NULL
2357 ,P_DESTINATION_LOCATOR => NULL
2358 ,P_DESTINATION_LOCATOR_STATUS => NULL
2359 ,P_SOURCE_IMMEDIATE_LPN_ID => NULL
2360 ,P_SOURCE_IMMEDIATE_LPN_STATUS => NULL
2361 ,P_SOURCE_TOPLEVEL_LPN_ID => NULL
2362 ,P_SOURCE_TOPLEVEL_LPN_STATUS => NULL
2363 ,P_DEST_IMMEDIATE_LPN_ID => NULL
2364 ,P_DEST_IMMEDIATE_LPN_STATUS => NULL
2365 ,P_DEST_TOPLEVEL_LPN_ID => NULL
2369 ,P_PRIMARY_UOM => NULL
2366 ,P_DEST_TOPLEVEL_LPN_STATUS => NULL
2367 ,P_SERIAL_NUMBER => NULL
2368 ,P_SERIAL_NUMBER_STATUS => NULL
2370 ,P_TRANSACTION_UOM => l_transaction_uom
2371 ,P_PRIMARY_QUANTITY => NULL
2372 ,P_TRANSACTION_QUANTITY => p_quantity_picked /* = quantity picked */
2373 ,P_TRANSACTION_ACTION_ID => NULL
2374 ,P_TRANSACTION_SOURCE_TYPE_ID => NULL
2375 ,P_TRANSACTION_SOURCE => NULL
2376 ,P_PARENT_TRANSACTION_SOURCE => NULL
2377 ,P_PARENT_TRANS_ACTION_ID => NULL
2378 ,P_PARENT_TRANS_SOURCE_TYPE_ID => NULL
2379 ,P_RESERVATION_ID => NULL
2380 ,P_EQUIPMENT_ID => NULL
2381 ,P_ROLE_ID => NULL
2382 ,P_EMPLOYEE_ID => p_user_id /* = user_id */
2383 ,P_TASK_TYPE_ID => NULL
2384 ,P_TASK_ID => p_tsk_id
2385 ,P_CALLING_PROGRAM_NAME => NULL
2386 ,P_EMAIL_ID => NULL
2387 ,P_PROGRAM_NAME => NULL
2388 ,P_RUN_MODE => NULL
2389 ,P_INIT_MSG_LST => NULL
2390 ,P_PROGRAM_CONTROL_ARG1 => NULL
2391 ,P_PROGRAM_CONTROL_ARG2 => NULL
2392 ,P_PROGRAM_CONTROL_ARG3 => NULL
2393 ,P_PROGRAM_CONTROL_ARG4 => NULL
2394 ,P_PROGRAM_CONTROL_ARG5 => NULL
2395 ,P_PROGRAM_CONTROL_ARG6 => NULL
2396 ,X_RETURN_STATUS => lX_RETURN_STATUS
2397 ,X_MSG_DATA => lX_MSG_DATA
2398 ,X_MSG_COUNT => lX_MSG_COUNT
2399 ,X_REVISION => lX_REVISION
2400 ,X_LOT_NUMBER => lX_LOT_NUMBER
2401 ,X_LOT_STATUS => lX_LOT_STATUS
2402 ,X_SUBLOT_NUMBER => lX_SUBLOT_NUMBER
2403 ,X_SUBLOT_STATUS => lX_SUBLOT_STATUS
2404 ,X_LPN_ID => lX_LPN_ID
2405 ,X_LPN_STATUS => lX_LPN_STATUS
2406 ,X_UOM_CODE => lX_UOM_CODE
2407 ,X_QUANTITY => lX_QUANTITY
2408 ,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
2409 ,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
2410 ,X_SUBINVENTORY => lX_SUBINVENTORY
2411 ,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
2412 ,X_LOCATOR => lX_LOCATOR
2413 ,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
2414 ,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
2415 ,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
2416 ,X_NEXT_FORM => lX_NEXT_FORM
2417 ,X_NEXT_MOBILE_FORM => lX_NEXT_MOBILE_FORM
2418 ,X_NEXT_PLSQL_PROGRAM => lX_NEXT_PLSQL_PROGRAM
2419 ,X_RESERVATION_ID => lX_RESERVATION_ID
2420 ,X_IS_RESERVATION_SUCCESSFUL => lX_IS_RESERVATION_SUCCESSFUL
2421 ,X_IS_CYCLE_COUNT_SUCCESSFUL => lX_IS_CYCLE_COUNT_SUCCESSFUL
2422 );
2423 END IF;
2424 EXCEPTION
2425
2426 WHEN FND_API.G_EXC_ERROR THEN
2427 --
2428 x_return_status := FND_API.G_RET_STS_ERROR;
2429 --
2430 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2431 ,p_data => x_msg_data);
2432 --
2433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2434 --
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436 --
2437 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2438 ,p_data => x_msg_data);
2439 --
2440 WHEN OTHERS THEN
2441 ROLLBACK TO wms_insuff_qty_PUB;
2442 --
2443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2444 --
2445 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2446 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2447 END IF;
2448 --
2449 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2450 , p_data => x_msg_data);
2451
2452
2453 END wms_insuff_qty_wrapper;
2454
2455
2456 /* PROCEDURE WMS_Inadequate_Quantity
2457 - This procedure is called when someone discovers
2458 an inadequate quantity of items in a location (which means there is a
2459 discrepancy between the number of items that are physically at the location
2460 and the number of items which the system _thinks_ is in the location).
2461 This procedure does the following:
2462 - updates the value from column quantity_detailed
2463 in the table mtl_txn_request_lines
2464 to quantity_detailed less quantity_picked
2465 - updates the values from columns reservation_quantity
2466 and primary_quantity
2467 in the table mtl_material_transaction_temp
2468 to reservation_quantity less quantity_picked and
2469 primary_quantity less quantity picked respectively
2470 - updates reservation_quantity and primary_reservation_quantity
2471 in the table mtl_reservations
2472 to reservation_quantity less quantity_picked
2473 and primary_reservation_quantity less quantity_picked
2474 - creates a new row in the table mtl_reservations. This row
2478 inventory_item_id and demand_source_header_id=9 (for cycle
2475 acts as a cycle count request.
2476 where . Note: The only way to check that this row is
2477 created is to query the table with the organization_id,
2479 count request).
2480 */
2481
2482 PROCEDURE WMS_Inadequate_Quantity (itemtype IN VARCHAR2,
2483 itemkey IN VARCHAR2,
2484 actid IN NUMBER,
2485 funcmode IN VARCHAR2,
2486 result OUT NOCOPY VARCHAR2)
2487 IS
2488
2489 -- local variables
2490 l_workflow_name VARCHAR2(250)
2491 ; l_item_key VARCHAR2(250)
2492 ; lp_api_version_number NUMBER := 1.0
2493 ; lp_init_msg_lst VARCHAR2(250) := FND_API.G_FALSE
2494 ; lp_commit VARCHAR2(250) := FND_API.G_FALSE
2495 ; lx_return_status VARCHAR2(1)
2496 ; lx_msg_count NUMBER
2497 ; lx_msg_data VARCHAR2(250)
2498 ; lp_organization_id NUMBER
2499 ; lp_task_id NUMBER
2500 ; lp_qty_picked NUMBER := 0
2501 ; lp_qty_uom VARCHAR2(3)
2502 ; lp_carton_id VARCHAR2(250) := NULL
2503 ; lp_user_id VARCHAR2(250)
2504 ; lp_reason_id NUMBER
2505 ; lp_mmtt_id NUMBER
2506 ; lp_locator_id NUMBER
2507 ; lp_sub_code VARCHAR2(10)
2508 ; lp_line_num NUMBER
2509 ;
2510
2511 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2512 BEGIN
2513 IF (l_debug = 1) THEN
2514 mdebug('In WMS_Inadequate_Quantity');
2515 END IF;
2516
2517 l_workflow_name := itemtype;
2518 l_item_key := itemkey;
2519
2520 -- populating the local procedure variables with the corresponding attributes from workflow
2521 lp_organization_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2522 itemkey => l_item_key,
2523 aname => 'PW_SOURCE_ORGANIZATION_ID');
2524
2525 lp_task_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2526 itemkey => l_item_key,
2527 aname => 'PW_TASK_ID');
2528
2529 lp_qty_picked := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2530 itemkey => l_item_key,
2531 aname => 'PW_TRANSACTION_QUANTITY');
2532
2533 lp_qty_uom := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
2534 itemkey => l_item_key,
2535 aname => 'PW_TRANSACTION_UOM');
2536
2537 lp_carton_id := wf_engine.getItemAttrText(itemtype => l_workflow_name,
2538 itemkey => l_item_key,
2539 aname => 'PW_LPN_ID');
2540
2541 lp_user_id := wf_engine.GetItemAttrText(itemtype => l_workflow_name,
2542 itemkey => l_item_key,
2543 aname => 'PW_EMPLOYEE_ID');
2544
2545 lp_reason_id := wf_engine.GetItemAttrNumber(itemtype => l_workflow_name,
2546 itemkey => l_item_key,
2547 aname => 'PW_REASON_NAME');
2548
2549
2550 IF (l_debug = 1) THEN
2551 mdebug('Before calling wms_txnrsn_actions_pub.Inadequate Quantity');
2552 END IF;
2553
2554
2555 -- get data to call suggest_alternate_locatoin
2556 --Get MMTT id from WMS_Dispatched_tasks
2557 SELECT transaction_temp_id
2558 INTO lp_mmtt_id
2559 FROM wms_dispatched_tasks
2560 WHERE task_id=lp_task_id;
2561 IF (l_debug = 1) THEN
2562 mdebug('lp_mmtt_id: '|| lp_mmtt_id);
2563 END IF;
2564
2565 SELECT subinventory_code,locator_id, move_order_line_id
2566 INTO lp_sub_code,lp_locator_id, lp_line_num
2567 FROM mtl_material_transactions_temp
2568 WHERE transaction_temp_id=lp_mmtt_id;
2569
2570 wms_txnrsn_actions_pub.suggest_alternate_location
2571 (p_api_version_number =>lp_api_version_number
2572 , p_init_msg_lst =>lp_init_msg_lst
2573 , p_commit =>lp_commit
2574 , x_return_status =>lx_return_status
2575 , x_msg_count =>lx_msg_count
2576 , x_msg_data =>lx_msg_data
2577 , p_organization_id =>lp_organization_id
2578 , p_mmtt_id =>lp_mmtt_id
2579 , p_task_id =>lp_task_id
2580 , p_subinventory_code =>lp_sub_code
2581 , p_locator_id =>lp_locator_id
2582 , p_carton_id =>lp_carton_id
2583 , p_user_id =>lp_user_id
2584 , p_qty_picked =>lp_qty_picked
2585 , p_line_num =>lp_line_num
2586 );
2587
2588 IF (l_debug = 1) THEN
2589 mdebug('After calling wms_txnrsn_actions_pub.suggest_alternate_location');
2590 END IF;
2591 -- setting the workflow attributes with the output results of
2592 -- the API wms_txnrsn_actions_pub.Inadequate_Qty
2593 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
2594 itemkey => l_item_key,
2595 aname => 'XW_RETURN_STATUS',
2596 avalue => lx_return_status);
2597
2598 wf_engine.SetItemAttrNumber(itemtype => l_workflow_name,
2599 itemkey => l_item_key,
2600 aname => 'XW_MSG_COUNT',
2601 avalue => lx_msg_count);
2602
2603 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
2604 itemkey => l_item_key,
2605 aname => 'XW_MSG_DATA',
2606 avalue => lx_msg_data);
2607
2608 -- check for errors
2609 fnd_msg_pub.count_and_get
2610 ( p_count => lx_msg_count
2611 , p_data => lx_msg_data
2612 );
2613
2614 IF (lx_msg_count = 0) THEN
2615 IF (l_debug = 1) THEN
2616 mdebug('Inadequate quantity successful');
2617 END IF;
2618 ELSIF (lx_msg_count = 1) THEN
2619 IF (l_debug = 1) THEN
2620 mdebug(replace(lx_msg_data,chr(0),' '));
2621 END IF;
2622 ELSE
2623 For I in 1..lx_msg_count LOOP
2624 lx_msg_data := fnd_msg_pub.get(I,'F');
2625 IF (l_debug = 1) THEN
2626 mdebug(replace(lx_msg_data,chr(0),' '));
2627 END IF;
2628 END LOOP;
2629 END IF;
2630
2631
2632
2633
2634 -- if successful, populate the workflow attribute XW_IS_RESERVATION_SUCCESSFUL
2635 -- with 'Y', otherwise populate with 'N'
2636 if (lx_return_status = fnd_api.g_ret_sts_success) then
2637 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
2638 itemkey => l_item_key,
2639 aname => 'XW_IS_RESERVATION_SUCCESSFUL',
2640 avalue => 'YES');
2641 else
2642 wf_engine.SetItemAttrText(itemtype => l_workflow_name,
2643 itemkey => l_item_key,
2644 aname => 'XW_IS_RESERVATION_SUCCESSFUL',
2645 avalue => 'NO');
2646 end if;
2647 EXCEPTION
2648
2649 WHEN fnd_api.g_exc_error THEN
2650 lx_return_status := fnd_api.g_ret_sts_error;
2651
2652 WHEN fnd_api.g_exc_unexpected_error THEN
2653 lx_return_status := fnd_api.g_ret_sts_unexp_error ;
2654
2655 WHEN OTHERS THEN
2656 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2657 THEN
2658 fnd_msg_pub.add_exc_msg
2659 ( g_pkg_name
2660 , 'WMS_Inadequate_Quantity'
2661 );
2662 END IF;
2663 END wms_inadequate_quantity;
2664
2665
2666 END wms_workflow_wrappers;
2667
2668