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