1 PACKAGE BODY inv_wip_picking_pvt AS
2 /* $Header: INVVWPKB.pls 120.10 2006/08/24 22:12:02 qxliu noship $ */
3
4 -- Conc mode identification
5 g_conc_mode BOOLEAN := FALSE;
6 g_trace_on NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),2);
7
8 -- Allocation Status - Used both at Header Level (Pick_Release) and Lines Level (Process_Line)
9 g_completely_allocated VARCHAR2(1) := 'S';
10 g_partially_allocated VARCHAR2(1) := 'P';
11 g_not_allocated VARCHAR2(1) := 'N';
12
13 -- Global table to store allocation status at the move order line level
14 TYPE g_mo_line_stat_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
15 g_mo_line_stat_tbl g_mo_line_stat_type;
16
17 -- Bug 5469486: add a separate counter for lines where MO line not created
18 g_mol_fail_count NUMBER;
19
20 -- Global variable for tracking WIP patch level
21 -- Bug 4288399, moved to spec
22 --g_wip_patch_level NUMBER := -999;
23
24 -- Forward declarations
25 PROCEDURE process_line(
26 p_mo_line_rec IN OUT NOCOPY inv_move_order_pub.trolin_rec_type
27 , p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
28 , p_grouping_rule_id IN NUMBER DEFAULT NULL
29 , p_plan_tasks IN BOOLEAN
30 , p_call_wip_api IN BOOLEAN --Added bug 4634522
31 , x_return_status OUT NOCOPY VARCHAR2
32 , x_msg_count OUT NOCOPY NUMBER
33 , x_msg_data OUT NOCOPY VARCHAR2
34 , x_detail_rec_count OUT NOCOPY NUMBER
35 );
36
37 PROCEDURE update_mmtt_for_wip(
38 x_return_status OUT NOCOPY VARCHAR2
39 , p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
40 , p_grouping_rule_id IN NUMBER
41 );
42
43 FUNCTION get_mo_alloc_stat RETURN VARCHAR2;
44 -- End forward declarations
45
46 PROCEDURE print_debug(p_message IN VARCHAR2, p_module IN VARCHAR2) IS
47 BEGIN
48 inv_log_util.trace(p_message, g_pkg_name || '.' || p_module,3);
49 END print_debug;
50
51
52 --
53 -- pre patchset I version
54 --
55 PROCEDURE release_pick_batch
56 ( p_mo_header_rec IN INV_Move_Order_PUB.Trohdr_Rec_Type
57 , p_mo_line_rec_tbl IN INV_Move_Order_PUB.Trolin_Tbl_Type
58 , p_auto_detail_flag IN VARCHAR2
59 , p_auto_pick_confirm_flag IN VARCHAR2
60 , p_allow_partial_pick IN VARCHAR2
61 , p_commit IN VARCHAR2
62 , p_init_msg_lst IN VARCHAR2
63 , x_return_status OUT NOCOPY VARCHAR2
64 , x_msg_count OUT NOCOPY NUMBER
65 , x_msg_data OUT NOCOPY VARCHAR2
66 ) IS
67
68 l_conc_req_id NUMBER;
69 l_api_return_status VARCHAR2(1);
70 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
71 l_mo_header_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
72
73 -- Bug 4288399
74 l_wip_error_table INV_WIP_Picking_PVT.Trolin_ErrTbl_Type;
75
76 BEGIN
77 IF (l_debug = 1) THEN
78 print_debug('Pre-I version of pick release called', 'RELEASE_PICK_BATCH');
79 END IF;
80
81 g_wip_patch_level := 1158;
82 l_mo_header_rec := p_mo_header_rec;
83
84 --
85 -- Pre 11.5.9 WIP uses 2 move order types (G_MOVE_ORDER_WIP_ISSUE and G_MOVE_ORDER_BACKFLUSH),
86 -- but Inventory 11.5.9 has changed to a single type (G_MOVE_ORDER_MFG_PICK)
87 --
88 IF l_mo_header_rec.move_order_type = INV_GLOBALS.G_MOVE_ORDER_BACKFLUSH THEN
89 l_mo_header_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_MFG_PICK;
90 IF (l_debug = 1) THEN
91 print_debug('Changed MO type from ' || INV_GLOBALS.G_MOVE_ORDER_BACKFLUSH ||' to ' || l_mo_header_rec.move_order_type, 'RELEASE_PICK_BATCH');
92 END IF;
93 END IF;
94
95 release_pick_batch(
96 p_mo_header_rec => l_mo_header_rec
97 , p_mo_line_rec_tbl => p_mo_line_rec_tbl
98 , p_auto_detail_flag => p_auto_detail_flag
99 , p_auto_pick_confirm_flag => p_auto_pick_confirm_flag
100 , p_allow_partial_pick => p_allow_partial_pick
101 , p_print_pick_slip => FND_API.G_FALSE
102 , p_plan_tasks => FALSE
103 , p_commit => p_commit
104 , p_init_msg_lst => p_init_msg_lst
105 , x_return_status => l_api_return_status
106 , x_msg_count => x_msg_count
107 , x_msg_data => x_msg_data
108 , x_conc_req_id => l_conc_req_id
109 , x_mo_line_errrec_tbl => l_wip_error_table -- Bug 4288399
110 );
111
112 IF (l_debug = 1) THEN
113 print_debug('Return Status from release_pick_batch (main): ' || l_api_return_status, 'RELEASE_PICK_BATCH');
114 END IF;
115
116 --
117 -- Pre 11.5.9 WIP code does not recognize status 'N'
118 --
119 IF l_api_return_status = 'N' THEN
120 l_api_return_status := 'P';
121 END IF;
122
123 x_return_status := l_api_return_status;
124 EXCEPTION
125 WHEN OTHERS THEN
126 IF (l_debug = 1) THEN
127 print_debug('Error: ' || SQLCODE || ', ' || SQLERRM, 'RELEASE_PICK_BATCH');
128 END IF;
129 x_return_status := fnd_api.g_ret_sts_error;
130 END release_pick_batch;
131
132
133
134 --
135 -- patchset I version
136 --
137 PROCEDURE release_pick_batch
138 ( p_mo_header_rec IN OUT NOCOPY inv_move_order_pub.trohdr_rec_type
139 , p_mo_line_rec_tbl IN inv_move_order_pub.trolin_tbl_type
140 , p_auto_detail_flag IN VARCHAR2
141 , p_auto_pick_confirm_flag IN VARCHAR2
142 , p_allow_partial_pick IN VARCHAR2
143 , p_print_pick_slip IN VARCHAR2
144 , p_plan_tasks IN BOOLEAN
145 , p_commit IN VARCHAR2
146 , p_init_msg_lst IN VARCHAR2
147 , x_return_status OUT NOCOPY VARCHAR2
148 , x_msg_count OUT NOCOPY NUMBER
149 , x_msg_data OUT NOCOPY VARCHAR2
150 , x_conc_req_id OUT NOCOPY NUMBER
151 , x_mo_line_errrec_tbl OUT NOCOPY INV_WIP_Picking_PVT.Trolin_ErrTbl_Type -- Bug 4288399
152 ) IS
153
154 l_api_version_number NUMBER := 1.0;
155 l_api_return_status VARCHAR2(1);
156 l_commit VARCHAR2(1) := fnd_api.g_false;
157 l_temp BOOLEAN;
158 l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
159 l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
160 l_organization_id NUMBER;
161 l_auto_pick_confirm VARCHAR2(1) := fnd_api.g_false;
162 l_entity_type NUMBER;
163 l_max_batch NUMBER;
164 l_counter NUMBER;
165 l_last_rec NUMBER;
166 l_last_batch_rec NUMBER;
167 l_rec_count NUMBER;
168 l_index NUMBER;
169 l_line_tbl inv_move_order_pub.trolin_tbl_type;
170 l_is_wms_org BOOLEAN := FALSE;
171 l_mo_allocation_status VARCHAR2(1) := g_not_allocated;
172 l_req_msg VARCHAR2(255); -- Informational message from device integration API
173
174 -- Bug 4288399
175 l_wip_error_table INV_WIP_Picking_PVT.Trolin_ErrTbl_Type;
176 l_wip_error_table_cnt NUMBER := 0;
177 l_msgcnt NUMBER;
178 l_msg_data VARCHAR2(2000);
179
180 l_savept_exists BOOLEAN := FALSE;
181 l_wip_err_count NUMBER;
182 l_err_start_index NUMBER;
183
184 CURSOR c_get_entity_type(p_wip_entity_id IN NUMBER, p_org_id IN NUMBER) IS
185 SELECT entity_type
186 FROM wip_entities
187 WHERE wip_entity_id = p_wip_entity_id
188 AND organization_id = p_org_id;
189
190 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
191 BEGIN
192
193 IF NVL(g_wip_patch_level,-999) = -999 THEN
194 g_wip_patch_level := 1159;
195 END IF;
196
197 l_organization_id := p_mo_header_rec.organization_id;
198 IF (l_debug = 1) THEN
199 print_debug('***************** Start of WIP pick release ****************', 'RELEASE_PICK_BATCH');
200 print_debug('Org ID: '|| TO_CHAR(l_organization_id), 'RELEASE_PICK_BATCH');
201 END IF;
202 g_mo_line_stat_tbl.DELETE;
203 g_mol_fail_count := 0;
204
205 SAVEPOINT inv_wip_pick_release;
206 l_savept_exists := TRUE;
207
208 -- Initialize message list.
209 IF fnd_api.to_boolean(p_init_msg_lst) THEN
210 fnd_msg_pub.initialize;
211 END IF;
212
213 -- Validate parameter for allowing partial pick release
214 IF p_allow_partial_pick NOT IN (fnd_api.g_true, fnd_api.g_false) THEN
215 IF (l_debug = 1) THEN
216 print_debug('Error: invalid partial pick parameter','RELEASE_PICK_BATCH');
217 END IF;
218 fnd_message.set_name('INV', 'INV_INVALID_PARTIAL_PICK_PARAM');
219 fnd_msg_pub.ADD;
220 RAISE fnd_api.g_exc_unexpected_error;
221 END IF;
222
223 IF (l_debug = 1) THEN
224 print_debug('p_allow_partial_pick is '|| p_allow_partial_pick, 'RELEASE_PICK_BATCH');
225 END IF;
226
227 --Bug 4288399, if its not a concurrent mode, the profile is set to -1
228 --so, changed the check below from 0 to -1
229 IF NVL(fnd_profile.VALUE('CONC_REQUEST_ID'), -1) <> -1 THEN
230 g_conc_mode := TRUE;
231 ELSE
232 g_conc_mode := FALSE;
233 END IF;
234
235 l_is_wms_org := inv_install.adv_inv_installed(l_organization_id);
236
237 -- Initialize API return status to success
238 x_return_status := fnd_api.g_ret_sts_success;
239
240 -- Determine whether or not to automatically pick confirm
241 IF p_auto_pick_confirm_flag <> fnd_api.g_miss_char THEN
242 IF (p_auto_pick_confirm_flag NOT IN (fnd_api.g_true, fnd_api.g_false) ) THEN
243 IF (l_debug = 1) THEN
244 print_debug('Error: Invalid auto_pick_confirm flag', 'RELEASE_PICK_BATCH');
245 END IF;
246 fnd_message.set_name('INV', 'INV_AUTO_PICK_CONFIRM_PARAM');
247 fnd_msg_pub.ADD;
248 RAISE fnd_api.g_exc_unexpected_error;
249 ELSE
250 l_auto_pick_confirm := p_auto_pick_confirm_flag;
251 END IF;
252 ELSE
253 -- Retrieve the org-level parameter for auto-pick confirm
254 BEGIN
255 -- The parameter is for whether pick confirm is required or not,
256 -- so the auto-pick confirm flag is the opposite of this.
257 SELECT DECODE(NVL(mo_pick_confirm_required, 2), 1, fnd_api.g_false, 2, fnd_api.g_true, fnd_api.g_true)
258 INTO l_auto_pick_confirm
259 FROM mtl_parameters
260 WHERE organization_id = l_organization_id;
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 IF (l_debug = 1) THEN
264 print_debug('Error: Invalid auto_pick_confirm flag', 'RELEASE_PICK_BATCH');
265 END IF;
266 fnd_message.set_name('INV', 'INV-NO ORG INFORMATION');
267 fnd_msg_pub.ADD;
268 RAISE fnd_api.g_exc_unexpected_error;
269 END;
270 END IF;
271
272 IF (l_debug = 1) THEN
273 print_debug('Auto Pick Confirm flag: '|| l_auto_pick_confirm, 'RELEASE_PICK_BATCH');
274 END IF;
275
276 -- Bug 2666620: Grouping Rule ID has to be stamped
277 -- Bug 2844622: Skip this check if WIP patch level is below 11.5.9
278 IF g_wip_patch_level >= 1159 THEN
279 IF nvl(p_mo_header_rec.grouping_rule_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
280 IF (l_debug = 1) THEN
281 print_debug('No Pick Slip Grouping Rule ID specified', 'RELEASE_PICK_BATCH');
282 END IF;
283 fnd_message.set_name('INV', 'INV_NO_PICK_SLIP_NUMBER');
284 fnd_msg_pub.ADD;
285 RAISE fnd_api.g_exc_unexpected_error;
286 END IF;
287 ELSE
288 IF (l_debug = 1) THEN
289 print_debug('WIP patch level is ' || to_char(g_wip_patch_level) ||
290 ' so skipping grouping_rule_id check.', 'RELEASE_PICK_BATCH');
291 END IF;
292 END IF;
293
294 IF (l_debug = 1) THEN
295 print_debug('Calling Create Move Order Header', 'RELEASE_PICK_BATCH');
296 END IF;
297
298 inv_move_order_pub.create_move_order_header(
299 p_api_version_number => l_api_version_number
300 , p_init_msg_list => fnd_api.g_false
301 , p_return_values => fnd_api.g_true
302 , p_commit => l_commit
303 , x_return_status => l_api_return_status
304 , x_msg_count => x_msg_count
305 , x_msg_data => x_msg_data
306 , p_trohdr_rec => p_mo_header_rec
307 , p_trohdr_val_rec => l_trohdr_val_rec
308 , x_trohdr_rec => p_mo_header_rec
309 , x_trohdr_val_rec => l_trohdr_val_rec
310 );
311
312 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
313 IF (l_debug = 1) THEN
314 print_debug('Error occurred in Create_Move_Order_Header', 'RELEASE_PICK_BATCH');
315 END IF;
316 RAISE fnd_api.g_exc_unexpected_error;
317 ELSE
318 IF (l_debug = 1) THEN
319 print_debug('Created MO Header successfully: '|| p_mo_header_rec.header_id, 'RELEASE_PICK_BATCH');
320 END IF;
321 END IF;
322
323 IF p_mo_line_rec_tbl.COUNT > 0 THEN
324 IF (l_debug = 1) THEN
325 print_debug('Number of MO Lines to create: '|| TO_CHAR(p_mo_line_rec_tbl.COUNT), 'RELEASE_PICK_BATCH');
326 END IF;
327 ELSE
328 IF (l_debug = 1) THEN
329 print_debug('No MO Lines to create!', 'RELEASE_PICK_BATCH');
330 END IF;
331 RAISE fnd_api.g_exc_unexpected_error;
332 END IF;
333
334 --
335 -- Bug 2844622: Skip performance changes (breaking up move order lines
336 -- into batches) if WIP patch level is below 11.5.9
337 --
338 IF g_wip_patch_level >= 1159 THEN
339 l_max_batch := to_number(fnd_profile.value('INV_COMPONENT_PICK_BATCH_SIZE'));
340 IF (l_debug = 1) THEN
341 print_debug('Max batch size: ' || l_max_batch, 'RELEASE_PICK_BATCH');
342 END IF;
343 IF l_max_batch IS NULL or l_max_batch <= 0 THEN
344 l_max_batch := 20;
345 IF (l_debug = 1) THEN
346 print_debug('using default batch size of 20', 'RELEASE_PICK_BATCH');
347 END IF;
348 END IF;
349
350 l_last_rec := p_mo_line_rec_tbl.LAST;
351 l_counter := p_mo_line_rec_tbl.FIRST;
352 WHILE l_counter <= l_last_rec LOOP
353 IF (l_debug = 1) THEN
354 print_debug('Rec Counter: ' || l_counter, 'RELEASE_PICK_BATCH');
355 END IF;
356 l_last_batch_rec := l_counter + l_max_batch-1;
357 IF l_last_batch_rec >= l_last_rec THEN
358 l_last_batch_rec := l_last_rec;
359 -- find the end of the current ship set. All the lines for this
360 -- shipset should be processed in the same commit cycle
361 ELSIF p_mo_line_rec_tbl(l_last_batch_rec).ship_set_id IS NOT NULL THEN
362 LOOP
363 EXIT WHEN l_last_batch_rec = l_last_rec;
364
365 IF p_mo_line_rec_tbl(l_last_batch_rec+1).ship_set_id IS NULL
366 OR (p_mo_line_rec_tbl(l_last_batch_rec).ship_set_id
367 <> p_mo_line_rec_tbl(l_last_batch_rec+1).ship_set_id)
368 THEN
369 -- last_batch_rec is the last line in this shipset, so exit
370 EXIT;
371 END IF;
372
373 l_last_batch_rec := l_last_batch_rec + 1;
374 END LOOP;
375 END IF;
376
377 IF (l_debug = 1) THEN
378 print_debug('Last batch record:'||l_last_batch_rec, 'RELEASE_PICK_BATCH');
379 END IF;
380
381 --copy move order lines into table to pass to create MO lines and pick release;
382 l_rec_count := l_counter;
383 l_index := 1;
384 LOOP
385 EXIT WHEN l_rec_count > l_last_batch_rec;
386 l_line_tbl(l_index) := p_mo_line_rec_tbl(l_rec_count);
387 l_line_tbl(l_index).header_id := p_mo_header_rec.header_id;
388 l_line_tbl(l_index).line_number := l_rec_count;
389 l_index := l_index + 1;
390 l_rec_count:= l_rec_count + 1;
391 END LOOP;
392
393 IF (l_debug = 1) THEN
394 print_debug('Calling create_move_order_lines', 'RELEASE_PICK_BATCH');
395 END IF;
396
397 IF NOT l_savept_exists THEN
398 SAVEPOINT inv_wip_pick_release;
399 l_savept_exists := TRUE;
400 END IF;
401
402 inv_move_order_pub.create_move_order_lines(
403 p_api_version_number => l_api_version_number
404 , p_init_msg_list => fnd_api.g_true
405 , p_return_values => fnd_api.g_true
406 , p_commit => l_commit
407 , x_return_status => l_api_return_status
408 , x_msg_count => x_msg_count
409 , x_msg_data => x_msg_data
410 , p_trolin_tbl => l_line_tbl
411 , p_trolin_val_tbl => l_trolin_val_tbl
412 , x_trolin_tbl => l_line_tbl
413 , x_trolin_val_tbl => l_trolin_val_tbl
414 );
415
416 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
417 IF (l_debug = 1) THEN
418 print_debug('Error occurred in Create_Move_Order_Lines = '||l_api_return_status
419 ,'RELEASE_PICK_BATCH');
420 END IF;
421 -- Bug 4288399, Filtering the errored records and continuing
422 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
423 RAISE fnd_api.g_exc_unexpected_error;
424 ELSIF (g_conc_mode) THEN
425 l_wip_err_count := 0;
426 l_err_start_index := l_wip_error_table.COUNT;
427 IF (l_debug = 1) THEN
428 print_debug('Conc_mode is True', 'RELEASE_PICK_BATCH');
429 print_debug('Number of records currently in error table: ' || l_err_start_index,
430 'RELEASE_PICK_BATCH');
431 END IF;
432 FOR i in 1..l_line_tbl.COUNT LOOP
433 IF l_line_tbl(i).return_status = fnd_api.g_ret_sts_error THEN
434 IF (l_debug = 1) THEN
435 print_debug('Delete Line '||l_line_tbl(i).line_id, 'RELEASE_PICK_BATCH');
436 END IF;
437
438 -- Bug 5469486: handle the case where line ID is g_miss_num
439 IF l_line_tbl(i).line_id = fnd_api.g_miss_num THEN
440 g_mol_fail_count := g_mol_fail_count + 1;
441 ELSE
442 g_mo_line_stat_tbl(l_line_tbl(i).line_id) := g_not_allocated;
443 END IF;
444
445 l_wip_error_table_cnt := l_wip_error_table.COUNT + 1;
446 l_wip_err_count := l_wip_err_count + 1;
447 l_wip_error_table(l_wip_error_table_cnt).line_id := l_line_tbl(i).line_id;
448 l_wip_error_table(l_wip_error_table_cnt).header_id := l_line_tbl(i).header_id;
449 l_wip_error_table(l_wip_error_table_cnt).organization_id := l_line_tbl(i).organization_id;
450 l_wip_error_table(l_wip_error_table_cnt).inventory_item_id := l_line_tbl(i).inventory_item_id;
451 l_wip_error_table(l_wip_error_table_cnt).txn_source_id := l_line_tbl(i).txn_source_id;
452 l_wip_error_table(l_wip_error_table_cnt).txn_source_line_id := l_line_tbl(i).txn_source_line_id;
453 l_wip_error_table(l_wip_error_table_cnt).from_subinventory_code := l_line_tbl(i).from_subinventory_code;
454 l_wip_error_table(l_wip_error_table_cnt).to_subinventory_code := l_line_tbl(i).to_subinventory_code;
455 l_wip_error_table(l_wip_error_table_cnt).line_status := l_line_tbl(i).line_status;
456 l_wip_error_table(l_wip_error_table_cnt).lot_number := l_line_tbl(i).lot_number;
457 l_wip_error_table(l_wip_error_table_cnt).from_locator_id := l_line_tbl(i).from_locator_id;
458 l_wip_error_table(l_wip_error_table_cnt).to_locator_id := l_line_tbl(i).to_locator_id;
459 l_wip_error_table(l_wip_error_table_cnt).project_id := l_line_tbl(i).project_id;
460 l_wip_error_table(l_wip_error_table_cnt).task_id := l_line_tbl(i).task_id;
461 l_wip_error_table(l_wip_error_table_cnt).revision := l_line_tbl(i).revision;
462 l_wip_error_table(l_wip_error_table_cnt).transaction_type_id := l_line_tbl(i).transaction_type_id;
463 l_wip_error_table(l_wip_error_table_cnt).primary_quantity := l_line_tbl(i).primary_quantity;
464 l_wip_error_table(l_wip_error_table_cnt).to_organization_id := l_line_tbl(i).to_organization_id;
465 l_wip_error_table(l_wip_error_table_cnt).uom_code := l_line_tbl(i).uom_code;
466 l_wip_error_table(l_wip_error_table_cnt).lpn_id := l_line_tbl(i).lpn_id;
467 l_wip_error_table(l_wip_error_table_cnt).to_lpn_id := l_line_tbl(i).to_lpn_id;
468 l_wip_error_table(l_wip_error_table_cnt).return_status := l_line_tbl(i).return_status;
469 l_wip_error_table(l_wip_error_table_cnt).ship_set_id := l_line_tbl(i).ship_set_id;
470 l_wip_error_table(l_wip_error_table_cnt).required_quantity := l_line_tbl(i).required_quantity;
471 l_wip_error_table(l_wip_error_table_cnt).quantity := l_line_tbl(i).quantity;
472 l_wip_error_table(l_wip_error_table_cnt).quantity_delivered := l_line_tbl(i).quantity_delivered;
473 l_wip_error_table(l_wip_error_table_cnt).quantity_detailed := l_line_tbl(i).quantity_detailed;
474 l_wip_error_table(l_wip_error_table_cnt).line_number := l_line_tbl(i).line_number;
475 l_wip_error_table(l_wip_error_table_cnt).creation_date := l_line_tbl(i).creation_date;
476 l_wip_error_table(l_wip_error_table_cnt).date_required := l_line_tbl(i).date_required;
477 l_wip_error_table(l_wip_error_table_cnt).to_organization_id := l_line_tbl(i).to_organization_id;
478 l_wip_error_table(l_wip_error_table_cnt).ship_model_id := l_line_tbl(i).ship_model_id;
479 l_line_tbl.DELETE(i);
480 l_trolin_val_tbl.DELETE(i);
481 END IF;
482 END LOOP;
483 FND_MSG_PUB.Count_And_Get(p_encoded => 'T', p_count => l_msgcnt, p_data => l_msg_data);
484 IF (l_debug = 1) THEN
485 print_debug('Msg count: ' || l_msgcnt, 'RELEASE_PICK_BATCH');
486 print_debug('WIP err table count: ' || l_wip_error_table.count, 'RELEASE_PICK_BATCH');
487 END IF;
488 FOR x IN 1..l_msgcnt LOOP
489 IF (l_debug = 1) THEN
490 print_debug('Msg number: ' || x, 'RELEASE_PICK_BATCH');
491 END IF;
492 l_msg_data := fnd_msg_pub.get(x, 'F');
493 IF (l_debug = 1) THEN
494 print_debug('Error message: ' || l_msg_data, 'RELEASE_PICK_BATCH');
495 END IF;
496 IF (l_debug = 1) THEN
497 print_debug(' Errored Line Details wip_entity_id ='||l_wip_error_table(x).txn_source_id||
498 ', operation_sec_num ='||l_wip_error_table(x).txn_source_line_id||
499 ', inventory_item_id ='||l_wip_error_table(x).inventory_item_id,
500 'RELEASE_PICK_BATCH');
501 END IF;
502 IF l_msgcnt = l_wip_err_count THEN
503 l_wip_error_table(l_err_start_index + x).error_message := l_msg_data;
504 END IF;
505 END LOOP;
506 ELSE
507 IF (l_debug = 1) THEN
508 print_debug('Conc_mode is False', 'RELEASE_PICK_BATCH');
509 END IF;
510 RAISE fnd_api.g_exc_unexpected_error;
511 END IF;
512 ELSE
513 IF (l_debug = 1) THEN
514 print_debug('Created MO Lines successfully', 'RELEASE_PICK_BATCH');
515 END IF;
516 END IF;
517
518 IF (l_debug = 1) THEN
519 print_debug('MO line creation complete, before calling pick release..',
520 'RELEASE_PICK_BATCH');
521 END IF;
522
523 IF p_auto_detail_flag = fnd_api.g_true AND l_line_tbl.COUNT > 0 THEN
524 IF (l_debug = 1) THEN
525 print_debug('Calling pick_release', 'RELEASE_PICK_BATCH');
526 END IF;
527 l_api_return_status := fnd_api.g_ret_sts_success;
528 pick_release(
529 x_return_status => l_api_return_status
530 , x_msg_count => x_msg_count
531 , x_msg_data => x_msg_data
532 , p_commit => l_commit
533 , p_init_msg_lst => fnd_api.g_true
534 , p_mo_line_tbl => l_line_tbl
535 , p_allow_partial_pick => p_allow_partial_pick
536 , p_grouping_rule_id => p_mo_header_rec.grouping_rule_id
537 , p_plan_tasks => p_plan_tasks
538 , p_call_wip_api => TRUE
539 );
540
541 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
542 IF (l_debug = 1) THEN
543 print_debug('Error occurred in Pick_Release', 'RELEASE_PICK_BATCH');
544 END IF;
545 RAISE fnd_api.g_exc_unexpected_error;
546 END IF;
547 END IF; -- end if auto detail flag is true
548
549 COMMIT;
550 l_savept_exists := FALSE;
551
552 l_counter := l_last_batch_rec + 1;
553 l_line_tbl.DELETE;
554 inv_quantity_tree_pvt.clear_quantity_cache;
555 END LOOP;
556 ELSE
557 -- WIP is 11.5.8 or lower
558 l_line_tbl := p_mo_line_rec_tbl;
559
560 FOR ii IN l_line_tbl.FIRST..l_line_tbl.LAST
561 LOOP
562 l_line_tbl(ii).header_id := p_mo_header_rec.header_id;
563 l_line_tbl(ii).line_number := ii;
564 END LOOP;
565
566 inv_move_order_pub.create_move_order_lines(
567 p_api_version_number => l_api_version_number
568 , p_init_msg_list => fnd_api.g_false
569 , p_return_values => fnd_api.g_true
570 , p_commit => l_commit
571 , x_return_status => l_api_return_status
572 , x_msg_count => x_msg_count
573 , x_msg_data => x_msg_data
574 , p_trolin_tbl => l_line_tbl
575 , p_trolin_val_tbl => l_trolin_val_tbl
576 , x_trolin_tbl => l_line_tbl
577 , x_trolin_val_tbl => l_trolin_val_tbl
578 );
579
580 IF l_api_return_status <> FND_API.G_RET_STS_SUCCESS THEN
581 print_debug ('Error occurred in INV_Move_Order_PUB.Create_Move_Order_Lines', 'RELEASE_PICK_BATCH');
582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
583 ELSE
584 print_debug ('Created MO Lines successfully', 'RELEASE_PICK_BATCH');
585 END IF;
586
587 IF p_auto_detail_flag = FND_API.G_TRUE THEN
588 pick_release(
589 x_return_status => l_api_return_status
590 , x_msg_count => x_msg_count
591 , x_msg_data => x_msg_data
592 , p_commit => l_commit
593 , p_init_msg_lst => fnd_api.g_false
594 , p_mo_line_tbl => l_line_tbl
595 , p_allow_partial_pick => p_allow_partial_pick
596 , p_grouping_rule_id => NULL
597 , p_plan_tasks => p_plan_tasks
598 , p_call_wip_api => TRUE
599 );
600
601 IF l_api_return_status <> FND_API.G_RET_STS_SUCCESS AND
602 l_api_return_status <> 'P' THEN
603 print_debug ('Error occurred in Pick_Release', 'RELEASE_PICK_BATCH');
604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
605 ELSIF l_api_return_status = 'P' THEN
606 print_debug ('One or more lines failed to allocate fully', 'RELEASE_PICK_BATCH');
607 x_return_status := 'P';
608 ELSE
609 print_debug ('Pick Release successful.', 'RELEASE_PICK_BATCH');
610 END IF;
611 END IF; -- end if auto detail flag is true
612 END IF; -- end if wip patch level >= 1159
613
614 l_mo_allocation_status := get_mo_alloc_stat();
615 g_mo_line_stat_tbl.DELETE;
616 g_mol_fail_count := 0;
617
618 IF NOT l_savept_exists THEN
619 SAVEPOINT inv_wip_pick_release;
620 l_savept_exists := TRUE;
621 END IF;
622
623 -- Printing the Pick Slip
624 IF p_print_pick_slip = FND_API.G_TRUE AND l_mo_allocation_status <> g_not_allocated THEN
625 x_conc_req_id := inv_pr_pick_slip_number.print_pick_slip(
626 x_return_status => l_api_return_status
627 , x_msg_data => x_msg_data
628 , x_msg_count => x_msg_count
629 , p_organization_id => l_organization_id
630 , p_mo_request_number => p_mo_header_rec.request_number
631 , p_plan_tasks => p_plan_tasks
632 );
633
634 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
635 IF (l_debug = 1) THEN
636 print_debug('Unable to submit Pick Slip Report Request','RELEASE_PICK_BATCH');
637 END IF;
638 RAISE fnd_api.g_exc_unexpected_error;
639 END IF;
640 IF (l_debug = 1) THEN
641 print_debug('Pick Slip Report Request ID = ' || x_conc_req_id,'RELEASE_PICK_BATCH');
642 END IF;
643 END IF;
644
645 -- Delete the pick slip number cache if WIP patch level is 11.5.9 or higher
646 IF g_wip_patch_level >= 1159 THEN
647 inv_pr_pick_slip_number.delete_wip_ps_tbl;
648 END IF;
649
650 IF l_is_wms_org THEN
651 --
652 -- Call Device Integration API to send the details of this
653 -- Pick Release Wave to devices, based on configuration.
654 -- All the Move Order Lines should have the same MO Header ID.
655 -- So using the FIRST line's Header ID.
656 -- Note: We don't check for the return condition of this API as
657 -- we let the Pick Release process to complete whether or not
658 -- Device Integration succeeds.
659 --
660 wms_device_integration_pvt.device_request(
661 p_bus_event => wms_device_integration_pvt.wms_be_pick_release
662 , p_call_ctx => wms_device_integration_pvt.dev_req_user
663 , p_task_trx_id => p_mo_header_rec.header_id
664 , x_request_msg => l_req_msg
665 , x_return_status => l_api_return_status
666 , x_msg_count => x_msg_count
667 , x_msg_data => x_msg_data
668 );
669 IF (l_debug = 1) THEN
670 print_debug('Device_API Return Status = '|| l_api_return_status ||
671 ' : Request Msg = ' || l_req_msg, 'RELEASE_PICK_BATCH');
672 END IF;
673 END IF;
674
675 -- Standard call to commit
676 IF p_commit = fnd_api.g_true THEN
677 COMMIT;
678 l_savept_exists := FALSE;
679 END IF;
680
681 x_return_status := l_mo_allocation_status;
682
683 -- Bug 4288399, returning the table of errored records back to the calling program.
684 x_mo_line_errrec_tbl := l_wip_error_table;
685
686 EXCEPTION
687 WHEN OTHERS THEN
688 x_return_status := fnd_api.g_ret_sts_error;
689 IF l_debug = 1 THEN
690 print_debug ('Release_pick_batch Error: ' || SQLERRM, 'RELEASE_PICK_BATCH');
691 END IF;
692 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
693 IF (l_savept_exists) THEN
694 ROLLBACK TO inv_wip_pick_release;
695 END IF;
696 IF g_conc_mode THEN
697 l_temp := fnd_concurrent.set_completion_status('ERROR', '');
698 END IF;
699 END release_pick_batch;
700
701
702
703 PROCEDURE pick_release(
704 x_return_status OUT NOCOPY VARCHAR2
705 , x_msg_count OUT NOCOPY NUMBER
706 , x_msg_data OUT NOCOPY VARCHAR2
707 , p_commit IN VARCHAR2
708 , p_init_msg_lst IN VARCHAR2
709 , p_mo_line_tbl IN inv_move_order_pub.trolin_tbl_type
710 , p_allow_partial_pick IN VARCHAR2
711 , p_grouping_rule_id IN NUMBER
712 , p_plan_tasks IN BOOLEAN
713 , p_call_wip_api IN BOOLEAN
714 ) IS
715
716 l_api_version_number NUMBER := 1.0;
717 l_line_index NUMBER; -- The index of the line in the table being processed
718 l_mo_line inv_move_order_pub.trolin_rec_type;
719 l_organization_id NUMBER; -- The OrgID to use (based on the MO Lines Passed in).
720 l_mo_header_id NUMBER; -- Move Order Header ID
721 l_mo_type NUMBER; -- The type of the move order (Should be only 5)
722 l_mo_number VARCHAR2(30); -- The move order number
723 l_api_return_status VARCHAR2(1);
724 l_processed_row_count NUMBER := 0; -- The number of rows which have been processed.
725 l_detail_rec_count NUMBER := 0;
726 l_quantity NUMBER;
727 l_transaction_quantity NUMBER;
728 l_primary_quantity NUMBER;
729 l_wip_alloc_qty NUMBER;
730 l_line_status NUMBER;
731 l_disable_cartonization VARCHAR2(1);
732 l_wip_entity_name VARCHAR2(240);
733 l_item_number VARCHAR2(40);
734
735 -- Used for processing WIP pick sets (ship_set_id)
736 l_cur_ship_set_id NUMBER := NULL;
737 l_set_index NUMBER;
738 l_start_index NUMBER;
739 l_set_process NUMBER;
740 l_start_process NUMBER;
741
742 l_tree_id NUMBER;
743 l_revision_control_code NUMBER;
744 l_lot_control_code NUMBER;
745
746 l_is_wms_org BOOLEAN := FALSE;
747
748 TYPE quantity_tree_tbl_type IS TABLE OF NUMBER
749 INDEX BY BINARY_INTEGER;
750
751 l_quantity_tree_tbl quantity_tree_tbl_type;
752 l_qtree_backup_tbl quantity_tree_tbl_type;
753
754 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
755
756 -- Bug 4349602: save all MOL IDs in current batch
757 TYPE l_molid_tbltyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
758 l_mol_id_tbl l_molid_tbltyp;
759 l_mol_id_index NUMBER;
760
761 BEGIN
762 -- Set savepoint for this API
763 IF (l_debug = 1) THEN
764 print_debug('Inside Pick_Release', 'PICK_RELEASE');
765 END IF;
766 SAVEPOINT pick_release;
767 -- Initialize API return status to success
768 x_return_status := fnd_api.g_ret_sts_success;
769
770 -- Initialize message list.
771 IF fnd_api.to_boolean(p_init_msg_lst) THEN
772 fnd_msg_pub.initialize;
773 END IF;
774
775 --Added bug 4634522
776 IF (WIP_CONSTANTS.DMF_PATCHSET_LEVEL = WIP_CONSTANTS.DMF_PATCHSET_I_VALUE) THEN
777 g_wip_patch_level := 1159;
778 ELSIF (WIP_CONSTANTS.DMF_PATCHSET_LEVEL = WIP_CONSTANTS.DMF_PATCHSET_J_VALUE) THEN
779 g_wip_patch_level := 11510;
780 END IF;
781 --end of fix for bug 4634522
782
783 IF p_mo_line_tbl.COUNT = 0 THEN
784 IF (l_debug = 1) THEN
785 print_debug('No Lines to pick', 'PICK_RELEASE');
786 END IF;
787 fnd_message.set_name('INV', 'INV_NO_LINES_TO_PICK');
788 fnd_msg_pub.ADD;
789 RAISE fnd_api.g_exc_unexpected_error;
790 END IF;
791
792 -- Bug 2038564: Clearing Qty Tree
793 inv_quantity_tree_pvt.clear_quantity_cache;
794
795 -- Validate that all move order lines are from the same org, that all lines
796 -- have a status of pre-approved (7) or approved (3), and that all of the move
797 -- order lines are of type Manufacturing Component Pick (5)
798 l_line_index := p_mo_line_tbl.FIRST;
799 l_mol_id_index := 1;
800 l_organization_id := p_mo_line_tbl(l_line_index).organization_id;
801 l_is_wms_org := inv_install.adv_inv_installed(l_organization_id);
802
803 --Bug 4288399, Printing Org ID as l_organization_id
804 IF (l_debug = 1) THEN
805 print_debug('MO Line count = ' || p_mo_line_tbl.COUNT ||
806 ' : Org ID = ' || l_organization_id, 'PICK_RELEASE');
807 END IF;
808
809 -- Bug 2666620: Moved it outside of LOOP so that it is done only once.
810 -- Verify that the move order type is of type Manufacturing Component Pick (5)
811 BEGIN
812 SELECT header_id, move_order_type, request_number
813 INTO l_mo_header_id, l_mo_type, l_mo_number
814 FROM mtl_txn_request_headers
815 WHERE header_id = p_mo_line_tbl(l_line_index).header_id;
816 EXCEPTION
817 WHEN NO_DATA_FOUND THEN
818 IF (l_debug = 1) THEN
819 print_debug('Error: Move Order Header not found', 'PICK_RELEASE');
820 END IF;
821 fnd_message.set_name('INV', 'INV_NO_HEADER_FOUND');
822 fnd_message.set_token('MO_LINE_ID', TO_CHAR(p_mo_line_tbl(l_line_index).line_id));
823 fnd_msg_pub.ADD;
824 RAISE fnd_api.g_exc_unexpected_error;
825 END;
826
827 IF l_mo_type <> inv_globals.g_move_order_mfg_pick THEN
828 IF (l_debug = 1) THEN
829 print_debug('Error: Trying to release non WIP move order', 'PICK_RELEASE');
830 END IF;
831 fnd_message.set_name('INV', 'INV_NOT_WIP_MOVE_ORDER');
832 fnd_message.set_token('MO_NUMBER', l_mo_number);
833 fnd_msg_pub.ADD;
834 RAISE fnd_api.g_exc_unexpected_error;
835 END IF;
836
837 LOOP
838 l_mo_line := p_mo_line_tbl(l_line_index);
839
840 IF (l_mo_line.return_status NOT IN (fnd_api.g_ret_sts_unexp_error, fnd_api.g_ret_sts_error) ) THEN
841 -- Verify that the lines are all for the same organization
842 IF l_mo_line.organization_id <> l_organization_id THEN
843 IF (l_debug = 1) THEN
844 print_debug('Error: Trying to pick for different org', 'PICK_RELEASE');
845 END IF;
846 fnd_message.set_name('INV', 'INV_PICK_DIFFERENT_ORG');
847 fnd_msg_pub.ADD;
848 RAISE fnd_api.g_exc_unexpected_error;
849 END IF;
850
851 -- Verify that the line status is approved or pre-approved
852 IF (l_mo_line.line_status NOT IN (3,7)) THEN
853 IF (l_debug = 1) THEN
854 print_debug('Error: Invalid Move Order Line Status', 'PICK_RELEASE');
855 END IF;
856 fnd_message.set_name('INV', 'INV_PICK_LINE_STATUS');
857 fnd_msg_pub.ADD;
858 RAISE fnd_api.g_exc_unexpected_error;
859 END IF;
860
861 -- Carton Grouping ID has to be stamped.
862 -- Bug 2844622: Skip this check if WIP patch level is below 11.5.9
863 IF g_wip_patch_level >= 1159 THEN
864 IF (l_is_wms_org AND NVL(l_mo_line.carton_grouping_id, fnd_api.g_miss_num) = fnd_api.g_miss_num) THEN
865 IF (l_debug = 1) THEN
866 print_debug('Error: No Carton Grouping ID specified', 'PICK_RELEASE');
867 END IF;
868 fnd_message.set_name('WMS', 'WMS_NO_CARTON_GROUP_ID');
869 fnd_msg_pub.ADD;
870 RAISE fnd_api.g_exc_unexpected_error;
871 END IF;
872 ELSE
873 IF (l_debug = 1) THEN
874 print_debug('WIP Patch Level = ' || g_wip_patch_level ||' so skipping Carton Group ID check', 'PICK_RELEASE');
875 END IF;
876 END IF;
877 -- Bug 2666620: End of Code Changes
878 l_mol_id_tbl(l_mol_id_index) := l_mo_line.line_id;
879 l_mol_id_index := l_mol_id_index + 1;
880 END IF; -- end if MO line status is error
881
882 -- We should create the quantity tree here so that we can
883 -- a) lock the tree
884 -- b) use the tree id to backup the quantity tree for ship
885 -- set and ship model id;
886 -- we only want to call create_tree once per org/item;
887 -- This should not be a performance hit as long as ARU
888 -- 1625268 has been applied.
889
890 IF NOT (l_quantity_tree_tbl.EXISTS(l_mo_line.inventory_item_id)) THEN
891 IF (l_debug = 1) THEN
892 print_debug('Creating Qty Tree for Item '|| TO_CHAR(l_mo_line.inventory_item_id), 'PICK_RELEASE');
893 END IF;
894
895 BEGIN
896 SELECT revision_qty_control_code, lot_control_code
897 INTO l_revision_control_code, l_lot_control_code
898 FROM mtl_system_items
899 WHERE organization_id = l_organization_id
900 AND inventory_item_id = l_mo_line.inventory_item_id;
901 EXCEPTION
902 WHEN NO_DATA_FOUND THEN
903 IF (l_debug = 1) THEN
904 print_debug('No Item Info found', 'PICK_RELEASE');
905 END IF;
906 RAISE fnd_api.g_exc_unexpected_error;
907 END;
908
909 inv_quantity_tree_pvt.create_tree
910 ( p_api_version_number => 1.0
911 , p_init_msg_lst => fnd_api.g_false
912 , x_return_status => l_api_return_status
913 , x_msg_count => x_msg_count
914 , x_msg_data => x_msg_data
915 , p_organization_id => l_organization_id
916 , p_inventory_item_id => l_mo_line.inventory_item_id
917 , p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
918 , p_is_revision_control => (l_revision_control_code = 2)
919 , p_is_lot_control => (l_lot_control_code = 2)
920 , p_is_serial_control => FALSE
921 , p_asset_sub_only => FALSE
922 , p_include_suggestion => FALSE
923 , p_demand_source_type_id => -99
924 , p_demand_source_header_id => -99
925 , p_demand_source_line_id => -99
926 , p_demand_source_delivery => NULL
927 , p_demand_source_name => NULL
928 , p_lot_expiration_date => SYSDATE
929 , x_tree_id => l_tree_id
930 , p_exclusive => inv_quantity_tree_pvt.g_exclusive
931 , p_pick_release => inv_quantity_tree_pvt.g_pick_release_yes
932 );
933
934 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
935 RAISE fnd_api.g_exc_error;
936 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
937 RAISE fnd_api.g_exc_unexpected_error;
938 END IF;
939
940 l_quantity_tree_tbl(l_mo_line.inventory_item_id) := l_tree_id;
941 END IF; -- not exist in quantity tree tbl
942
943 EXIT WHEN l_line_index = p_mo_line_tbl.LAST;
944 l_line_index := p_mo_line_tbl.NEXT(l_line_index);
945 END LOOP;
946
947 IF (l_debug = 1) THEN
948 print_debug('Validations complete, starting pick release', 'PICK_RELEASE');
949 END IF;
950 l_line_index := p_mo_line_tbl.FIRST;
951
952 LOOP
953 l_mo_line := p_mo_line_tbl(l_line_index);
954 IF (l_debug = 1) THEN
955 print_debug('Loop index: ' || TO_CHAR(l_line_index), 'PICK_RELEASE');
956 print_debug('MO line : ' || TO_CHAR(l_mo_line.line_id), 'PICK_RELEASE');
957 print_debug('Item : ' || TO_CHAR(l_mo_line.inventory_item_id), 'PICK_RELEASE');
958 print_debug('Quantity : ' || TO_CHAR(l_mo_line.quantity), 'PICK_RELEASE');
959 END IF;
960
961 IF (l_mo_line.return_status <> fnd_api.g_ret_sts_unexp_error
962 AND l_mo_line.return_status <> fnd_api.g_ret_sts_error)
963 THEN
964 IF l_mo_line.ship_set_id IS NOT NULL
965 AND (l_cur_ship_set_id IS NULL OR l_cur_ship_set_id <> l_mo_line.ship_set_id)
966 THEN
967 SAVEPOINT shipset;
968 l_cur_ship_set_id := l_mo_line.ship_set_id;
969 l_start_index := l_line_index;
970 l_start_process := l_processed_row_count;
971 l_qtree_backup_tbl.DELETE;
972 IF (l_debug = 1) THEN
973 print_debug('Start Pick Set: '|| TO_CHAR(l_cur_ship_set_id), 'PICK_RELEASE');
974 END IF;
975 ELSIF l_cur_ship_set_id IS NOT NULL AND l_mo_line.ship_set_id IS NULL THEN
976 IF (l_debug = 1) THEN
977 print_debug('End of Shipset: '|| TO_CHAR(l_cur_ship_set_id), 'PICK_RELEASE');
978 END IF;
979 l_cur_ship_set_id := NULL;
980 l_qtree_backup_tbl.DELETE;
981 END IF;
982
983 IF (l_mo_line.ship_set_id IS NOT NULL)
984 AND NOT (l_qtree_backup_tbl.EXISTS(l_mo_line.inventory_item_id)) THEN
985 IF (l_debug = 1) THEN
986 print_debug('Backing up qty tree: '|| TO_CHAR(l_tree_id), 'PICK_RELEASE');
987 END IF;
988 l_tree_id := l_quantity_tree_tbl(l_mo_line.inventory_item_id);
989 inv_quantity_tree_pvt.backup_tree(x_return_status => l_api_return_status, p_tree_id => l_tree_id);
990
991 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
992 RAISE fnd_api.g_exc_error;
993 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
994 RAISE fnd_api.g_exc_unexpected_error;
995 END IF;
996
997 l_qtree_backup_tbl(l_mo_line.inventory_item_id) := l_tree_id;
998 END IF; -- Pick set NOT NULL
999
1000 -- Call the Pick Release Process_Line API on the current Move Order Line
1001 process_line(
1002 p_mo_line_rec => l_mo_line
1003 , p_allow_partial_pick => p_allow_partial_pick
1004 , p_grouping_rule_id => p_grouping_rule_id
1005 , p_plan_tasks => p_plan_tasks
1006 , p_call_wip_api => p_call_wip_api --Added bug 4634522
1007 , x_return_status => l_api_return_status
1008 , x_msg_count => x_msg_count
1009 , x_msg_data => x_msg_data
1010 , x_detail_rec_count => l_detail_rec_count
1011 );
1012 IF (l_debug = 1) THEN
1013 print_debug('Process Line Return Status = '|| l_api_return_status ||
1014 ' : Detail Rec Count = ' || to_char(l_detail_rec_count), 'PICK_RELEASE');
1015 END IF;
1016
1017 IF l_api_return_status <> fnd_api.g_ret_sts_success AND p_allow_partial_pick = fnd_api.g_false THEN
1018 fnd_message.set_name('INV', 'INV_COULD_NOT_PICK_FULL');
1019 fnd_msg_pub.ADD;
1020 RAISE fnd_api.g_exc_unexpected_error;
1021 END IF;
1022
1023 g_mo_line_stat_tbl(l_mo_line.line_id) := l_api_return_status;
1024
1025 SELECT quantity, line_status
1026 INTO l_quantity, l_line_status
1027 FROM mtl_txn_request_lines
1028 WHERE line_id = l_mo_line.line_id;
1029
1030 SELECT NVL(SUM(transaction_quantity), 0), NVL(SUM(primary_quantity), 0)
1031 INTO l_transaction_quantity, l_primary_quantity
1032 FROM mtl_material_transactions_temp
1033 WHERE move_order_line_id = l_mo_line.line_id;
1034
1035 -- If the total allocated quantity is less than the requested
1036 -- quantity update the move order line to change the
1037 -- requested quantity to be equal to the allocated quantity.
1038
1039 IF (l_transaction_quantity < l_quantity)
1040 OR (l_transaction_quantity = 0 AND l_quantity = 0) THEN
1041 -- For shipsets, if any of the lines fail to allocate completely,
1042 -- rollback all allocations
1043
1044 IF l_cur_ship_set_id IS NOT NULL THEN
1045 IF (l_debug = 1) THEN
1046 print_debug('Rollback for pick set: '|| TO_CHAR(l_cur_ship_set_id), 'PICK_RELEASE');
1047 END IF;
1048 ROLLBACK TO shipset;
1049 l_set_index := l_start_index;
1050 l_set_process := l_start_process;
1051
1052 -- loop through all move order lines for this ship set
1053 LOOP
1054 l_mo_line := p_mo_line_tbl(l_set_index);
1055 IF (l_debug = 1) THEN
1056 print_debug(TO_CHAR(l_set_process) || ' Rolling back allocations for MO line: '
1057 ||TO_CHAR(l_mo_line.line_id), 'PICK_RELEASE');
1058 END IF;
1059
1060 g_mo_line_stat_tbl(l_mo_line.line_id) := g_not_allocated;
1061
1062 IF l_qtree_backup_tbl.EXISTS(l_mo_line.inventory_item_id) THEN
1063 l_tree_id := l_qtree_backup_tbl(l_mo_line.inventory_item_id);
1064 IF (l_debug = 1) THEN
1065 print_debug('Restoring Quantity Tree: '|| TO_CHAR(l_tree_id), 'PICK_RELEASE');
1066 END IF;
1067
1068 inv_quantity_tree_pvt.restore_tree
1069 ( x_return_status => l_api_return_status
1070 , p_tree_id => l_tree_id
1071 );
1072
1073 IF (l_api_return_status = fnd_api.g_ret_sts_error) THEN
1074 IF (l_debug = 1) THEN
1075 print_debug('Error in Restore_Tree', 'Pick_Release');
1076 END IF;
1077 RAISE fnd_api.g_exc_error;
1078 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
1079 IF (l_debug = 1) THEN
1080 print_debug('Unexpected error in Restore_tree', 'PICK_RELEASE');
1081 END IF;
1082 RAISE fnd_api.g_exc_unexpected_error;
1083 END IF;
1084
1085 -- delete entry, so we don't restore tree more than once
1086 l_qtree_backup_tbl.DELETE(l_mo_line.inventory_item_id);
1087 END IF;
1088
1089 -- close the move order line
1090 UPDATE mtl_txn_request_lines
1091 SET quantity = 0, quantity_detailed = 0, line_status = 5
1092 WHERE line_id = l_mo_line.line_id;
1093
1094 -- Exit if there are no more move order lines to detail
1095 -- or when the next move order is not for the same ship set.
1096 -- l_set_index should always be equal to the last line
1097 -- in the current ship set, so that the logic at the
1098 -- end of the outer loop works correctly.
1099 EXIT WHEN p_mo_line_tbl.LAST = l_set_index;
1100 l_set_index := p_mo_line_tbl.NEXT(l_set_index);
1101
1102 IF NVL(p_mo_line_tbl(l_set_index).ship_set_id, -1) <> l_cur_ship_set_id THEN
1103 l_set_index := p_mo_line_tbl.PRIOR(l_set_index);
1104 EXIT;
1105 END IF;
1106
1107 l_set_process := l_set_process + 1;
1108 END LOOP;
1109
1110 -- At the end of this loop, l_mo_line and l_set_index
1111 -- point to the last line for this ship set.
1112 l_line_index := l_set_index;
1113 l_cur_ship_set_id := NULL;
1114 l_processed_row_count := l_set_process;
1115 l_detail_rec_count := 0;
1116
1117 --
1118 -- Bug 2501138:
1119 -- Set txn qty to 0, so that we don't invoke
1120 -- wip_picking_pub.allocate_material
1121 --
1122 l_transaction_quantity := 0;
1123 l_primary_quantity := 0;
1124 l_qtree_backup_tbl.DELETE;
1125 IF (l_debug = 1) THEN
1126 print_debug('Finished rolling back all lines in shipset', 'PICK_RELEASE');
1127 END IF;
1128 ELSE
1129 UPDATE mtl_txn_request_lines
1130 SET quantity = l_transaction_quantity
1131 WHERE line_id = l_mo_line.line_id;
1132
1133 IF (l_transaction_quantity = 0) THEN
1134 UPDATE mtl_txn_request_lines
1135 SET line_status = 5
1136 WHERE line_id = l_mo_line.line_id;
1137 END IF;
1138 END IF; -- cur ship set id
1139 END IF; -- transaction quantity < quantity or transaction quantity is 0
1140
1141
1142 l_processed_row_count := l_processed_row_count + 1;
1143 IF (l_debug = 1) THEN
1144 print_debug('Processed row : ' || TO_CHAR(l_processed_row_count), 'PICK_RELEASE');
1145 print_debug('MO line ID : ' || TO_CHAR(l_mo_line.line_id), 'PICK_RELEASE');
1146 print_debug('Return Status : ' || l_api_return_status, 'PICK_RELEASE');
1147 print_debug('Dtl rec count : ' || TO_CHAR(l_detail_rec_count), 'PICK_RELEASE');
1148 END IF;
1149
1150 l_detail_rec_count := 0;
1151
1152 -- Update WIP with allocated qty if alloc qty > 0
1153 IF p_call_wip_api AND l_transaction_quantity > 0 THEN
1154 IF (l_debug = 1) THEN
1155 print_debug('Updating WIP with Allocated Qty: '|| l_primary_quantity, 'PICK_RELEASE');
1156 END IF;
1157 wip_picking_pub.allocate_material(
1158 p_wip_entity_id => l_mo_line.txn_source_id
1159 , p_operation_seq_num => l_mo_line.txn_source_line_id
1160 , p_inventory_item_id => l_mo_line.inventory_item_id
1161 , p_repetitive_schedule_id => l_mo_line.reference_id
1162 , p_primary_quantity => l_primary_quantity
1163 , x_quantity_allocated => l_wip_alloc_qty
1164 , x_return_status => l_api_return_status
1165 , x_msg_data => x_msg_data
1166 );
1167
1168 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1169 IF (l_debug = 1) THEN
1170 print_debug('Error in wip_picking_pub.allocate_material', 'PICK_RELEASE');
1171 print_debug('WIP entity ID: ' || TO_CHAR(l_mo_line.txn_source_id), 'PICK_RELEASE');
1172 print_debug('Op seq num : ' || TO_CHAR(l_mo_line.txn_source_line_id), 'PICK_RELEASE');
1173 print_debug('Rep sch ID : ' || TO_CHAR(l_mo_line.reference_id), 'PICK_RELEASE');
1174 print_debug('Item ID : ' || TO_CHAR(l_mo_line.inventory_item_id), 'PICK_RELEASE');
1175 END IF;
1176 RAISE fnd_api.g_exc_error;
1177 END IF;
1178 END IF;
1179 END IF; -- mo line return status <> ERROR
1180
1181 EXIT WHEN l_line_index = p_mo_line_tbl.LAST;
1182 l_line_index := p_mo_line_tbl.NEXT(l_line_index);
1183 END LOOP; -- end looping through MO lines
1184
1185 IF (l_debug = 1) THEN
1186 print_debug('Done looping through MO lines', 'PICK_RELEASE');
1187 END IF;
1188
1189 -- Bug 4349602: Deleting Move Order Lines which are not allocated
1190 BEGIN
1191 IF (l_debug = 1) THEN
1192 print_debug('Deleting closed MOLs..','PICK_RELEASE');
1193 END IF;
1194 FORALL ii IN l_mol_id_tbl.FIRST..l_mol_id_tbl.LAST
1195 DELETE FROM mtl_txn_request_lines mtrl
1196 WHERE line_status = 5
1197 AND line_id = l_mol_id_tbl(ii);
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 IF (l_debug = 1) THEN
1201 print_debug('Error in Deleting Move Order Lines: ' || sqlerrm
1202 ,'PICK_RELEASE');
1203 END IF;
1204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1205 END;
1206
1207 IF l_is_wms_org THEN
1208 -- Call cartonization API to assign task types and split and merge tasks.
1209 -- Bug 2666620: Cartonization is not disabled and Packaging Mode is set
1210 IF (l_debug = 1) THEN
1211 print_debug('Calling Cartonization Engine', 'PICK_RELEASE');
1212 END IF;
1213
1214 -- Bug 2844622: Disable cartonization if WIP patch level is below 11.5.9
1215 IF g_wip_patch_level >= 1159 THEN
1216 l_disable_cartonization := 'N';
1217 ELSE
1218 l_disable_cartonization := 'Y';
1219 END IF;
1220
1221 wms_cartnzn_wrap.cartonize
1222 ( p_api_version => l_api_version_number
1223 , p_init_msg_list => fnd_api.g_false
1224 , p_commit => fnd_api.g_false
1225 , p_validation_level => fnd_api.g_valid_level_full
1226 , x_return_status => l_api_return_status
1227 , x_msg_count => x_msg_count
1228 , x_msg_data => x_msg_data
1229 , p_out_bound => 'Y'
1230 , p_org_id => l_organization_id
1231 , p_move_order_header_id => l_mo_header_id
1232 , p_disable_cartonization => l_disable_cartonization
1233 , p_packaging_mode => wms_cartnzn_wrap.mfg_pr_pkg_mode
1234 );
1235
1236 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1237 IF (l_debug = 1) THEN
1238 print_debug('Cartonization returned with an error status: '||l_api_return_status, 'Pick_Release');
1239 END IF;
1240 RAISE fnd_api.g_exc_unexpected_error;
1241 END IF;
1242 END IF;
1243
1244 -- Standard call to commit
1245 IF p_commit = fnd_api.g_true THEN
1246 COMMIT;
1247 END IF;
1248
1249 IF (l_debug = 1) THEN
1250 print_debug('x_return_status = '|| x_return_status, 'PICK_RELEASE');
1251 END IF;
1252
1253 EXCEPTION
1254 WHEN fnd_api.g_exc_error THEN
1255 ROLLBACK TO pick_release;
1256 inv_quantity_tree_pvt.clear_quantity_cache;
1257 x_return_status := fnd_api.g_ret_sts_error;
1258 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1259 WHEN OTHERS THEN
1260 ROLLBACK TO pick_release;
1261 inv_quantity_tree_pvt.clear_quantity_cache;
1262 x_return_status := fnd_api.g_ret_sts_unexp_error;
1263 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1264 END pick_release;
1265
1266
1267
1268 --
1269 -- Name
1270 -- PROCEDURE Process_Line
1271 --
1272 -- Package
1273 -- INV_Pick_Release_PVT
1274 --
1275 -- Purpose
1276 -- Pick releases the move order line passed in. Any necessary validation is
1277 -- assumed to have been done by the caller.
1278 --
1279 -- Input Parameters
1280 -- p_mo_line_rec
1281 -- The Move Order Line record to pick release
1282 -- p_allow_partial_pick
1283 -- TRUE if the pick release process should continue after a line fails to
1284 -- be detailed completely.
1285 -- FALSE if the process should stop and roll back all changes if a line
1286 -- cannot be fully detailed.
1287 --
1288 -- Output Parameters
1289 -- x_return_status
1290 -- if the process succeeds, the value is
1291 -- FND_API.G_RET_STS_SUCCESS;
1292 -- if there is an expected error, the value is
1293 -- fnd_api.g_ret_sts_error;
1294 -- if there is an unexpected error, the value is
1295 -- fnd_api.g_ret_sts_unexp_error;
1296 -- x_msg_count
1297 -- if there is one or more errors, the number of error messages
1298 -- in the buffer
1299 -- x_msg_data
1300 -- if there is one and only one error, the error message
1301 --
1302 -- (See FND_API package for more details about the above output parameters)
1303 --
1304
1305 PROCEDURE process_line
1306 ( p_mo_line_rec IN OUT NOCOPY inv_move_order_pub.trolin_rec_type
1307 , p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
1308 , p_grouping_rule_id IN NUMBER
1309 , p_plan_tasks IN BOOLEAN
1310 , p_call_wip_api IN BOOLEAN --Added bug 4634522
1311 , x_return_status OUT NOCOPY VARCHAR2
1312 , x_msg_count OUT NOCOPY NUMBER
1313 , x_msg_data OUT NOCOPY VARCHAR2
1314 , x_detail_rec_count OUT NOCOPY NUMBER
1315 ) IS
1316 -- Empty record for calling Create_Suggestions
1317 l_demand_rsvs_ordered inv_reservation_global.mtl_reservation_tbl_type;
1318 l_request_number NUMBER; -- MO Header number
1319 l_primary_uom VARCHAR2(3); -- The primary UOM for the item
1320 l_quantity_detailed NUMBER; -- The quantity for the current MO which was detailed in Primary UOM
1321 l_num_detail_recs NUMBER; -- The number of MO Line details for this MO Line.
1322 l_mol_allocation_status VARCHAR2(1); -- A flag indicating the status of the MOL Allocation.
1323 l_quantity_detailed_conv NUMBER; -- The quantity detailed for the current MO in UOM of MO
1324 l_api_return_status VARCHAR2(1); -- The return status of APIs called within the Process Line API.
1325 l_count NUMBER;
1326 l_message VARCHAR2(255);
1327 l_allocate_quantity NUMBER;
1328 l_use_pick_set_flag VARCHAR2(1);
1329
1330 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1331 BEGIN
1332 IF (l_debug = 1) THEN
1333 print_debug('Inside Process_Line', 'PROCESS_LINE');
1334 print_debug('WIP Entity ID: '|| TO_CHAR(p_mo_line_rec.txn_source_id), 'PROCESS_LINE');
1335 print_debug('Op seq num : '|| TO_CHAR(p_mo_line_rec.txn_source_line_id), 'PROCESS_LINE');
1336 print_debug('Rep sch ID : '|| TO_CHAR(p_mo_line_rec.reference_id), 'PROCESS_LINE');
1337 print_debug('Item ID : '|| TO_CHAR(p_mo_line_rec.inventory_item_id), 'PROCESS_LINE');
1338 print_debug('MO line ID : '|| TO_CHAR(p_mo_line_rec.line_id), 'PROCESS_LINE');
1339 print_debug('Unit num : '|| p_mo_line_rec.unit_number, 'PROCESS_LINE');
1340 print_debug('Project ID : '|| TO_CHAR(p_mo_line_rec.project_id), 'PROCESS_LINE');
1341 print_debug('Task ID : '|| TO_CHAR(p_mo_line_rec.task_id), 'PROCESS_LINE');
1342 END IF;
1343 SAVEPOINT process_line_pvt;
1344 x_detail_rec_count := 0;
1345 l_num_detail_recs := 0;
1346 -- Initialize API return status to success
1347 x_return_status := fnd_api.g_ret_sts_success;
1348
1349 -- Bug 4880578: return if MO line has 0 qty
1350 IF NVL(p_mo_line_rec.quantity,0) = 0 THEN
1351 IF (l_debug = 1) THEN
1352 print_debug('MO line has 0 qty: '||p_mo_line_rec.quantity,'PROCESS_LINE');
1353 END IF;
1354 x_return_status := g_not_allocated;
1355 RETURN;
1356 END IF;
1357
1358 l_allocate_quantity := nvl(p_mo_line_rec.quantity,0)
1359 - nvl(p_mo_line_rec.quantity_detailed, 0);
1360
1361 IF (l_debug = 1) THEN
1362 print_debug('Quantity to detail: '||l_allocate_quantity,'PROCESS_LINE');
1363 END IF;
1364
1365 -- Return success immediately if the line is already fully detailed
1366 IF l_allocate_quantity <= 0 THEN
1367 IF (l_debug = 1) THEN
1368 print_debug('MO line is already fully detailed', 'PROCESS_LINE');
1369 END IF;
1370 RETURN;
1371 END IF;
1372
1373 IF p_mo_line_rec.ship_set_id IS NOT NULL THEN
1374 l_use_pick_set_flag := 'Y';
1375 ELSE
1376 l_use_pick_set_flag := 'N';
1377 END IF;
1378
1379 -- Bug 2844622: Skip this callback if WIP patch level is below 11.5.9
1380 IF g_wip_patch_level >= 1159 AND p_call_wip_api THEN -- bug 4634522.Added p_call_wip_api
1381 --calling WIP callback API
1382 IF (l_debug = 1) THEN
1383 print_debug('Calling pre_allocate_material', 'PROCESS_LINE');
1384 END IF;
1385
1386 wip_picking_pub.pre_allocate_material
1387 ( p_wip_entity_id => p_mo_line_rec.txn_source_id
1388 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1389 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1390 , p_repetitive_schedule_id => p_mo_line_rec.reference_id
1391 , p_use_pickset_flag => l_use_pick_set_flag
1392 , p_allocate_quantity => l_allocate_quantity
1393 , x_return_status => l_api_return_status
1394 , x_msg_data => x_msg_data
1395 );
1396 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1397 IF (l_debug = 1) THEN
1398 print_debug('Error in pre_allocate_material','PROCESS_LINE');
1399 END IF;
1400 RAISE fnd_api.g_exc_unexpected_error;
1401 END IF;
1402 ELSE
1403 IF (l_debug = 1) THEN
1404 print_debug('WIP Patch Level = ' || g_wip_patch_level || ', so skipping pre_allocate_material', 'PROCESS_LINE');
1405 END IF;
1406 END IF;
1407
1408 IF (l_debug = 1) THEN
1409 print_debug('Calling Create Suggestions for MOLine: '|| p_mo_line_rec.line_id, 'PROCESS_LINE');
1410 END IF;
1411 inv_ppengine_pvt.create_suggestions
1412 ( p_api_version => 1.0
1413 , p_init_msg_list => fnd_api.g_false
1414 , p_commit => fnd_api.g_false
1415 , x_return_status => l_api_return_status
1416 , x_msg_count => x_msg_count
1417 , x_msg_data => x_msg_data
1418 , p_transaction_temp_id => p_mo_line_rec.line_id
1419 , p_reservations => l_demand_rsvs_ordered
1420 , p_suggest_serial => 'T'
1421 , p_plan_tasks => p_plan_tasks
1422 );
1423
1424 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1425 IF (l_debug = 1) THEN
1426 print_debug('Ret stat: '|| l_api_return_status || ', INV detailing failed', 'PROCESS_LINE');
1427 END IF;
1428 fnd_msg_pub.count_and_get(p_count => l_count, p_data => l_message, p_encoded => 'F');
1429
1430 IF (l_count = 0) THEN
1431 IF (l_debug = 1) THEN
1432 print_debug('No message from detailing engine', 'PROCESS_LINE');
1433 END IF;
1434 ELSIF (l_count = 1) THEN
1435 IF (l_debug = 1) THEN
1436 print_debug(l_message, 'PROCESS_LINE');
1437 END IF;
1438 ELSE
1439 FOR i IN 1 .. l_count LOOP
1440 l_message := fnd_msg_pub.get(i, 'F');
1441 IF (l_debug = 1) THEN
1442 print_debug(l_message, 'PROCESS_LINE');
1443 END IF;
1444 END LOOP;
1445
1446 fnd_msg_pub.delete_msg();
1447 END IF;
1448
1449 BEGIN
1450 SELECT request_number
1451 INTO l_request_number
1452 FROM mtl_txn_request_lines_v
1453 WHERE line_id = p_mo_line_rec.line_id;
1454 EXCEPTION
1455 WHEN NO_DATA_FOUND THEN
1456 RAISE fnd_api.g_exc_unexpected_error;
1457 END;
1458
1459 fnd_message.set_name('INV', 'INV_DETAILING_FAILED');
1460 fnd_message.set_token('LINE_NUM', TO_CHAR(p_mo_line_rec.line_number));
1461 fnd_message.set_token('MO_NUMBER', l_request_number);
1462 fnd_msg_pub.ADD;
1463 RAISE fnd_api.g_exc_unexpected_error;
1464 END IF;
1465
1466 IF (l_debug = 1) THEN
1467 print_debug('After calling Create Suggestions: Ret Status = '|| l_api_return_status, 'PROCESS_LINE');
1468 END IF;
1469
1470 -- Update the detailed quantity (and if possible, the sourcing information)
1471 SELECT NVL(SUM(primary_quantity), 0), COUNT(*)
1472 INTO l_quantity_detailed, l_num_detail_recs
1473 FROM mtl_material_transactions_temp
1474 WHERE move_order_line_id = p_mo_line_rec.line_id;
1475
1476 IF (l_debug = 1) THEN
1477 print_debug('Qty detailed = '|| l_quantity_detailed || ' : Num of Details = ' || l_num_detail_recs, 'PROCESS_LINE');
1478 END IF;
1479
1480 -- If the move order line is not fully detailed, update the return status as appropriate.
1481 IF l_quantity_detailed < p_mo_line_rec.primary_quantity AND p_allow_partial_pick = fnd_api.g_false THEN
1482 fnd_message.set_name('INV', 'INV_COULD_NOT_PICK_FULL');
1483 fnd_msg_pub.ADD;
1484 RAISE fnd_api.g_exc_unexpected_error;
1485 END IF;
1486
1487 IF (l_num_detail_recs = 0) THEN
1488 p_mo_line_rec.quantity_detailed := NVL(l_quantity_detailed, 0) + NVL(p_mo_line_rec.quantity_delivered, 0);
1489 IF (l_debug = 1) THEN
1490 print_debug('Updating Move Order Line', 'PROCESS_LINE');
1491 END IF;
1492 inv_trolin_util.update_row(p_mo_line_rec);
1493 l_mol_allocation_status := g_not_allocated;
1494 ELSIF l_num_detail_recs > 0 THEN
1495 IF l_quantity_detailed < p_mo_line_rec.primary_quantity THEN
1496 l_mol_allocation_status := g_partially_allocated;
1497 ELSE
1498 l_mol_allocation_status := g_completely_allocated;
1499 END IF;
1500 -- Calculate the quantity detailed in the UOM of the move order line
1501 SELECT primary_uom_code INTO l_primary_uom
1502 FROM mtl_system_items
1503 WHERE organization_id = p_mo_line_rec.organization_id
1504 AND inventory_item_id = p_mo_line_rec.inventory_item_id;
1505
1506 IF (l_primary_uom <> p_mo_line_rec.uom_code) THEN
1507 l_quantity_detailed_conv := inv_convert.inv_um_convert
1508 ( item_id => p_mo_line_rec.inventory_item_id
1509 , PRECISION => NULL
1510 , from_quantity => l_quantity_detailed
1511 , from_unit => l_primary_uom
1512 , to_unit => p_mo_line_rec.uom_code
1513 , from_name => NULL
1514 , to_name => NULL
1515 );
1516 IF (l_debug = 1) THEN
1517 print_debug('After calling convert', 'PROCESS_LINE');
1518 END IF;
1519
1520 -- Update the Move Order Line with the quantity which was detailed.
1521 IF (l_quantity_detailed_conv = -99999) THEN
1522 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
1523 fnd_message.set_token('UOM', p_mo_line_rec.uom_code);
1524 fnd_message.set_token('ROUTINE', 'Pick Release process');
1525 fnd_msg_pub.ADD;
1526 RAISE fnd_api.g_exc_unexpected_error;
1527 END IF;
1528 ELSE
1529 l_quantity_detailed_conv := l_quantity_detailed;
1530 END IF;
1531
1532 -- bug 4634522 .begin
1533 IF (l_mol_allocation_status = g_partially_allocated) THEN
1534 IF (l_debug = 1) THEN
1535 print_debug(' p_mo_line_rec.quantit '||p_mo_line_rec.quantity || ' l_quantity_detailed_conv'||l_quantity_detailed_conv||
1536 'p_mo_line_rec.quantity_delivered '||p_mo_line_rec.quantity_delivered ||' p_mo_line_rec.detailed_quantity '||
1537 p_mo_line_rec.quantity_detailed , 'PROCESS_LINE');
1538 END IF;
1539
1540 wip_picking_pub.unallocate_material(
1541 p_wip_entity_id => p_mo_line_rec.txn_source_id
1542 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1543 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1544 , p_repetitive_schedule_id => p_mo_line_rec.reference_id
1545 , p_primary_quantity => p_mo_line_rec.quantity- ( NVL(l_quantity_detailed_conv, 0) + NVL(p_mo_line_rec.quantity_delivered, 0) ) --pass backordered qty
1546 , x_return_status => l_api_return_status
1547 , x_msg_data => x_msg_data
1548 );
1549
1550 IF (l_debug = 1) THEN
1551 print_debug('wip_picking_pub.unallocate_material returned '|| l_api_return_status , 'PROCESS_LINE');
1552 END IF;
1553 END IF; -- end if fix for bug 4634522
1554
1555 p_mo_line_rec.quantity_detailed := NVL(l_quantity_detailed_conv, 0) + NVL(p_mo_line_rec.quantity_delivered, 0);
1556 p_mo_line_rec.pick_slip_date := SYSDATE;
1557 IF (l_debug = 1) THEN
1558 print_debug('Detailed Qty = '|| p_mo_line_rec.quantity_detailed, 'PROCESS_LINE');
1559 print_debug('Updating Move Order Line', 'PROCESS_LINE');
1560 END IF;
1561 inv_trolin_util.update_row(p_mo_line_rec);
1562 update_mmtt_for_wip(x_return_status, p_mo_line_rec, p_grouping_rule_id);
1563 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1564 print_debug('Error occurred while updating MMTT with WIP Attributes','PROCESS_LINE');
1565 RAISE fnd_api.g_exc_unexpected_error;
1566 END IF;
1567 END IF; -- if l_num_detail_rec > 0
1568
1569 -- If the line was only partially detailed and the API was about to return success,
1570 -- set the return status to 'P' (Partial) or 'N' (None) instead.
1571 x_detail_rec_count := l_num_detail_recs;
1572 x_return_status := l_mol_allocation_status;
1573 IF l_debug = 1 THEN
1574 print_debug('Return status: ' || l_mol_allocation_status,'PROCESS_LINE');
1575 END IF;
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 ROLLBACK TO process_line_pvt;
1579 IF l_debug = 1 THEN
1580 print_debug('Error in process line: ' || sqlcode || ', ' || sqlerrm,'PROCESS_LINE');
1581 END IF;
1582 x_return_status := g_not_allocated;
1583 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1584 END process_line;
1585
1586 PROCEDURE update_mol_for_wip
1587 ( x_return_status OUT NOCOPY VARCHAR2
1588 , x_msg_count OUT NOCOPY NUMBER
1589 , x_msg_data OUT NOCOPY VARCHAR2
1590 , p_move_order_line_id IN NUMBER
1591 , p_op_seq_num IN NUMBER
1592 ) IS
1593 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1594 BEGIN
1595 x_return_status := fnd_api.g_ret_sts_success;
1596 IF (l_debug = 1) THEN
1597 print_debug('p_move_order_line_id => '|| p_move_order_line_id, 'update_mol_for_wip');
1598 print_debug('p_op_seq_num => '|| p_op_seq_num, 'update_mol_for_wip');
1599 END IF;
1600
1601 UPDATE mtl_txn_request_lines mol
1602 SET mol.txn_source_line_id = p_op_seq_num
1603 WHERE mol.line_id = p_move_order_line_id
1604 AND EXISTS( SELECT ''
1605 FROM mtl_txn_request_headers moh
1606 WHERE moh.header_id = mol.header_id
1607 AND move_order_type = 5);
1608
1609 IF SQL%NOTFOUND THEN
1610 IF (l_debug = 1) THEN
1611 print_debug('No move order lines being updated', 'update_mol_for_wip');
1612 END IF;
1613 x_return_status := fnd_api.g_ret_sts_error;
1614 RETURN;
1615 END IF;
1616
1617 UPDATE mtl_material_transactions_temp mmtt
1618 SET mmtt.trx_source_line_id = p_op_seq_num
1619 WHERE mmtt.move_order_line_id = p_move_order_line_id
1620 AND EXISTS( SELECT ''
1621 FROM mtl_txn_request_headers moh, mtl_txn_request_lines mol
1622 WHERE mol.line_id = mmtt.move_order_line_id
1623 AND mol.header_id = moh.header_id
1624 AND move_order_type = 5);
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 x_return_status := fnd_api.g_ret_sts_unexp_error;
1628 END update_mol_for_wip;
1629
1630 PROCEDURE update_mmtt_for_wip(
1631 x_return_status OUT NOCOPY VARCHAR2
1632 , p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
1633 , p_grouping_rule_id IN NUMBER
1634 ) IS
1635 l_wip_entity_type NUMBER;
1636 l_repetitive_line_id NUMBER;
1637 l_department_id NUMBER;
1638 l_department_code bom_departments.department_code%TYPE;
1639 l_push_vs_pull VARCHAR2(4);
1640 l_pick_slip_number NUMBER;
1641 l_index NUMBER := 0;
1642 l_msg_data VARCHAR2(2000);
1643 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1644
1645 CURSOR c_mmtt IS
1646 SELECT transaction_temp_id, revision, subinventory_code, locator_id, transfer_subinventory, transfer_to_location, pick_slip_number
1647 FROM mtl_material_transactions_temp
1648 WHERE move_order_line_id = p_mo_line_rec.line_id;
1649 BEGIN
1650 get_wip_attributes(
1651 x_return_status => x_return_status
1652 , x_wip_entity_type => l_wip_entity_type
1653 , x_push_vs_pull => l_push_vs_pull
1654 , x_repetitive_line_id => l_repetitive_line_id
1655 , x_department_id => l_department_id
1656 , x_department_code => l_department_code
1657 , x_pick_slip_number => l_pick_slip_number
1658 , p_wip_entity_id => p_mo_line_rec.txn_source_id
1659 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1660 , p_rep_schedule_id => p_mo_line_rec.reference_id
1661 , p_organization_id => p_mo_line_rec.organization_id
1662 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1663 , p_transaction_type_id => p_mo_line_rec.transaction_type_id
1664 , p_get_pick_slip_number => FALSE
1665 );
1666
1667 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1668 print_debug('Error Occurred while getting WIP Attributes','UPDATE_MMTT_FOR_WIP');
1669 RAISE fnd_api.g_exc_unexpected_error;
1670 END IF;
1671
1672 FOR c_mmtt_rec IN c_mmtt LOOP
1673 l_index := l_index + 1;
1674
1675 IF c_mmtt_rec.pick_slip_number IS NULL THEN
1676 IF g_wip_patch_level >= 1159 THEN
1677 inv_pr_pick_slip_number.get_pick_slip_number(
1678 p_pick_grouping_rule_id => p_grouping_rule_id
1679 , p_org_id => p_mo_line_rec.organization_id
1680 , p_wip_entity_id => p_mo_line_rec.txn_source_id
1681 , p_rep_schedule_id => p_mo_line_rec.reference_id
1682 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1683 , p_dept_id => l_department_id
1684 , p_push_or_pull => l_push_vs_pull
1685 , p_supply_subinventory => c_mmtt_rec.transfer_subinventory
1686 , p_supply_locator_id => c_mmtt_rec.transfer_to_location
1687 , p_project_id => p_mo_line_rec.project_id
1688 , p_task_id => p_mo_line_rec.task_id
1689 , p_src_subinventory => c_mmtt_rec.subinventory_code
1690 , p_src_locator_id => c_mmtt_rec.locator_id
1691 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1692 , p_revision => c_mmtt_rec.revision
1693 , p_lot_number => NULL
1694 , x_pick_slip_number => l_pick_slip_number
1695 , x_api_status => x_return_status
1696 , x_error_message => l_msg_data
1697 );
1698
1699 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1700 IF (l_debug = 1) THEN
1701 print_debug('Error occurred in getting the Pick Slip Number: '|| l_msg_data, 'UPDATE_WITH_PICK_SLIP');
1702 END IF;
1703 fnd_message.set_name('INV','INV_NO_PICK_SLIP_NUMBER');
1704 fnd_msg_pub.add;
1705 RAISE fnd_api.g_exc_unexpected_error;
1706 END IF;
1707 ELSE
1708 BEGIN
1709 SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL INTO l_pick_slip_number FROM DUAL;
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 fnd_message.set_name('INV','INV_NO_PICK_SLIP_NUMBER');
1713 fnd_msg_pub.add;
1714 RAISE fnd_api.g_exc_unexpected_error;
1715 END;
1716 END IF;
1717 ELSE
1718 l_pick_slip_number := c_mmtt_rec.pick_slip_number;
1719 END IF;
1720
1721 IF p_mo_line_rec.transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_WIP_ISSUE THEN
1722 UPDATE mtl_material_transactions_temp
1723 SET transaction_source_id = p_mo_line_rec.txn_source_id
1724 , trx_source_line_id = p_mo_line_rec.txn_source_line_id
1725 , demand_source_header_id = p_mo_line_rec.txn_source_id
1726 , demand_source_line = p_mo_line_rec.txn_source_line_id
1727 , transaction_source_type_id = inv_globals.g_sourcetype_wip
1728 , transaction_type_id = p_mo_line_rec.transaction_type_id
1729 , transaction_action_id = inv_globals.g_action_issue
1730 , wip_entity_type = l_wip_entity_type
1731 , repetitive_line_id = l_repetitive_line_id
1732 , operation_seq_num = p_mo_line_rec.txn_source_line_id
1733 , department_id = l_department_id
1734 , department_code = l_department_code
1735 , lock_flag = 'N'
1736 , primary_switch = l_index
1737 , wip_supply_type = 1
1738 , negative_req_flag = SIGN(transaction_quantity)
1739 , required_flag = '1'
1740 , pick_slip_number = l_pick_slip_number
1741 WHERE transaction_temp_id = c_mmtt_rec.transaction_temp_id;
1742 ELSIF p_mo_line_rec.transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_REPL_SUBXFR THEN
1743 UPDATE mtl_material_transactions_temp
1744 SET transaction_source_id = p_mo_line_rec.txn_source_id
1745 , trx_source_line_id = p_mo_line_rec.txn_source_line_id
1746 , demand_source_header_id = p_mo_line_rec.txn_source_id
1747 , demand_source_line = p_mo_line_rec.txn_source_line_id
1748 , transaction_source_type_id = inv_globals.g_sourcetype_inventory
1749 , transaction_type_id = p_mo_line_rec.transaction_type_id
1750 , transaction_action_id = inv_globals.g_action_subxfr
1751 , wip_entity_type = l_wip_entity_type
1752 , wip_supply_type = NULL -- Bug#2057540
1753 , pick_slip_number = l_pick_slip_number
1754 WHERE transaction_temp_id = c_mmtt_rec.transaction_temp_id;
1755 END IF;
1756 END LOOP;
1757 EXCEPTION
1758 WHEN OTHERS THEN
1759 IF (l_debug = 1) THEN
1760 print_debug('Exception Occurred: Code = ' || SQLCODE || ' : Error '|| SQLERRM, 'UPDATE_MMTT_FOR_WIP');
1761 END IF;
1762 x_return_status := fnd_api.g_ret_sts_unexp_error;
1763 END update_mmtt_for_wip;
1764
1765 PROCEDURE get_wip_attributes(
1766 x_return_status OUT NOCOPY VARCHAR2
1767 , x_wip_entity_type OUT NOCOPY NUMBER
1768 , x_push_vs_pull OUT NOCOPY VARCHAR2
1769 , x_repetitive_line_id OUT NOCOPY NUMBER
1770 , x_department_id OUT NOCOPY NUMBER
1771 , x_department_code OUT NOCOPY VARCHAR2
1772 , x_pick_slip_number OUT NOCOPY NUMBER
1773 , p_wip_entity_id IN NUMBER
1774 , p_operation_seq_num IN NUMBER
1775 , p_rep_schedule_id IN NUMBER
1776 , p_organization_id IN NUMBER
1777 , p_inventory_item_id IN NUMBER
1778 , p_transaction_type_id IN NUMBER
1779 , p_get_pick_slip_number IN BOOLEAN
1780 ) IS
1781
1782 CURSOR c_wip_entity_type IS
1783 SELECT entity_type
1784 FROM wip_entities
1785 WHERE wip_entity_id = p_wip_entity_id;
1786
1787 CURSOR c_repetitive_line_id IS
1788 SELECT line_id
1789 FROM wip_repetitive_schedules
1790 WHERE repetitive_schedule_id = p_rep_schedule_id
1791 AND organization_id = p_organization_id
1792 AND wip_entity_id = p_wip_entity_id;
1793
1794 CURSOR c_push_vs_pull IS
1795 SELECT decode(wip_supply_type,1,'PUSH',2,'PULL',3,'PULL')
1796 FROM wip_requirement_operations
1797 WHERE p_rep_schedule_id IS null
1798 AND wip_entity_id = p_wip_entity_id
1799 AND inventory_item_id = p_inventory_item_id
1800 AND operation_seq_num = p_operation_seq_num
1801 AND organization_id = p_organization_id
1802 UNION ALL
1803 SELECT decode(wip_supply_type,1,'PUSH',2,'PULL',3,'PULL')
1804 FROM wip_requirement_operations
1805 WHERE p_rep_schedule_id IS NOT NULL
1806 AND wip_entity_id = p_wip_entity_id
1807 AND inventory_item_id = p_inventory_item_id
1808 AND operation_seq_num = p_operation_seq_num
1809 AND organization_id = p_organization_id
1810 AND repetitive_schedule_id = p_rep_schedule_id;
1811
1812 CURSOR c_discrete_dept IS
1813 SELECT wo.department_id, bd.department_code
1814 FROM wip_operations wo, bom_departments bd
1815 WHERE wo.wip_entity_id = p_wip_entity_id
1816 AND wo.organization_id = p_organization_id
1817 AND wo.operation_seq_num = p_operation_seq_num
1818 AND bd.department_id = wo.department_id;
1819
1820 CURSOR c_repetitive_dept IS
1821 SELECT wo.department_id, bd.department_code
1822 FROM wip_operations wo, bom_departments bd
1823 WHERE wo.wip_entity_id = p_wip_entity_id
1824 AND wo.organization_id = p_organization_id
1825 AND wo.operation_seq_num = p_operation_seq_num
1826 AND wo.repetitive_schedule_id = p_rep_schedule_id
1827 AND bd.department_id = wo.department_id;
1828
1829 CURSOR c_flow_dept IS
1830 SELECT bos.department_id, bd.department_code
1831 FROM bom_departments bd
1832 , bom_operation_sequences bos
1833 , bom_operational_routings bor
1834 , wip_flow_schedules wfs
1835 WHERE wfs.wip_entity_id = p_wip_entity_id
1836 AND wfs.organization_id = p_organization_id
1837 AND bor.assembly_item_id = wfs.primary_item_id
1838 AND bor.organization_id = wfs.organization_id
1839 AND (bor.alternate_routing_designator = wfs.alternate_routing_designator
1840 OR (wfs.alternate_routing_designator IS NULL
1841 AND bor.alternate_routing_designator IS NULL))
1842 AND bos.routing_sequence_id = bor.routing_sequence_id
1843 AND bos.operation_type = 1
1844 AND bos.effectivity_date >= SYSDATE
1845 AND bd.department_id = bos.department_id;
1846
1847 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1848
1849 BEGIN
1850 x_return_status := fnd_api.g_ret_sts_success;
1851
1852 -- Entity type
1853 OPEN c_wip_entity_type;
1854 FETCH c_wip_entity_type INTO x_wip_entity_type;
1855 IF c_wip_entity_type%NOTFOUND THEN
1856 IF (l_debug = 1) THEN
1857 print_debug('Couldnt determine Entity Type for EntityID = '|| p_wip_entity_id, 'UPDATE_MMTT_FOR_WIP');
1858 END IF;
1859 CLOSE c_wip_entity_type;
1860 RAISE fnd_api.g_exc_unexpected_error;
1861 END IF;
1862 CLOSE c_wip_entity_type;
1863
1864 -- Departments
1865 IF x_wip_entity_type IN (1,5,6) THEN
1866 OPEN c_discrete_dept;
1867 FETCH c_discrete_dept INTO x_department_id, x_department_code;
1868 CLOSE c_discrete_dept;
1869 ELSIF x_wip_entity_type = 2 THEN
1870 OPEN c_repetitive_dept;
1871 FETCH c_repetitive_dept INTO x_department_id, x_department_code;
1872 CLOSE c_repetitive_dept;
1873 ELSIF x_wip_entity_type = 4 THEN
1874 OPEN c_flow_dept;
1875 FETCH c_flow_dept INTO x_department_id, x_department_code;
1876 CLOSE c_flow_dept;
1877 END IF;
1878
1879 -- Repetitive Line ID
1880 IF p_transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_WIP_ISSUE THEN
1881 IF x_wip_entity_type = 2 THEN
1882 IF p_rep_schedule_id IS NULL THEN
1883 IF (l_debug = 1) THEN
1884 print_debug('Repetitive Schedule ID cannot be null for Entity Type 2', 'UPDATE_MMTT_FOR_WIP');
1885 END IF;
1886 RAISE fnd_api.g_exc_unexpected_error;
1887 ELSE
1888 OPEN c_repetitive_line_id;
1889 FETCH c_repetitive_line_id INTO x_repetitive_line_id;
1890 IF c_repetitive_line_id%NOTFOUND THEN
1891 IF (l_debug = 1) THEN
1892 print_debug('Unable to determine RepLineID for RepSchID '||p_rep_schedule_id, 'UPDATE_MMTT_FOR_WIP');
1893 END IF;
1894 RAISE fnd_api.g_exc_unexpected_error;
1895 END IF;
1896 CLOSE c_repetitive_line_id;
1897 END IF;
1898 END IF;
1899 END IF;
1900
1901 -- Supply Type
1902 IF x_wip_entity_type IN (1, 2, 5, 6) THEN
1903 OPEN c_push_vs_pull;
1904 FETCH c_push_vs_pull INTO x_push_vs_pull;
1905 CLOSE c_push_vs_pull;
1906 ELSIF x_wip_entity_type = 4 THEN
1907 x_push_vs_pull := 'PULL';
1908 END IF;
1909
1910 -- Pick Slip Number
1911 IF p_get_pick_slip_number THEN
1912 SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL INTO x_pick_slip_number FROM DUAL;
1913 END IF;
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 x_return_status := fnd_api.g_ret_sts_error;
1917 END get_wip_attributes;
1918
1919 FUNCTION get_mo_alloc_stat RETURN VARCHAR2 IS
1920 l_mo_alloc_stat VARCHAR2(1) := g_not_allocated;
1921 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1922 l_first_line_id NUMBER := 0;
1923 l_last_line_id NUMBER := 0;
1924 l_cur_line_id NUMBER := 0;
1925
1926 l_num_success NUMBER := 0;
1927 l_num_partial NUMBER := 0;
1928 l_num_no_alloc NUMBER := 0;
1929 BEGIN
1930 l_first_line_id := g_mo_line_stat_tbl.FIRST;
1931 l_last_line_id := g_mo_line_stat_tbl.LAST;
1932
1933 IF l_debug = 1 THEN
1934 print_debug('First line ID: ' || to_char(l_first_line_id) || ', Last line ID: ' || to_char(l_last_line_id), 'GET_MO_ALLOC_STAT');
1935 END IF;
1936
1937 l_cur_line_id := l_first_line_id;
1938 LOOP
1939 IF l_debug = 1 THEN
1940 print_debug('Line ID: ' || to_char(l_cur_line_id) || ', Line alloc stat: ' || g_mo_line_stat_tbl(l_cur_line_id), 'GET_MO_ALLOC_STAT');
1941 END IF;
1942
1943 IF g_mo_line_stat_tbl(l_cur_line_id) = g_completely_allocated THEN
1944 l_num_success := l_num_success + 1;
1945 ELSIF g_mo_line_stat_tbl(l_cur_line_id) = g_partially_allocated THEN
1946 l_num_partial := l_num_partial + 1;
1947 ELSIF g_mo_line_stat_tbl(l_cur_line_id) = g_not_allocated THEN
1948 l_num_no_alloc := l_num_no_alloc + 1;
1949 END IF;
1950
1951 IF l_cur_line_id = l_last_line_id THEN
1952 EXIT;
1953 END IF;
1954
1955 l_cur_line_id := g_mo_line_stat_tbl.NEXT(l_cur_line_id);
1956 END LOOP;
1957
1958 -- Bug 5469486: Add the number of lines where MOL creation failed
1959 l_num_no_alloc := l_num_no_alloc + NVL(g_mol_fail_count,0);
1960
1961 IF l_debug = 1 THEN
1962 print_debug('Line status counts:: Successes: ' || l_num_success ||', Partial: ' || l_num_partial ||', None: ' || l_num_no_alloc, 'GET_MO_ALLOC_STAT');
1963 END IF;
1964
1965 IF l_num_success > 0 AND l_num_partial = 0 AND l_num_no_alloc = 0 THEN
1966 l_mo_alloc_stat := g_completely_allocated;
1967 ELSIF l_num_partial > 0 OR (l_num_success > 0 AND l_num_no_alloc > 0) THEN
1968 l_mo_alloc_stat := g_partially_allocated;
1969 ELSE
1970 l_mo_alloc_stat := g_not_allocated;
1971 END IF;
1972
1973 IF l_debug = 1 THEN
1974 print_debug('Overall status: ' || l_mo_alloc_stat, 'GET_MO_ALLOC_STAT');
1975 END IF;
1976
1977 RETURN l_mo_alloc_stat;
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 IF l_debug = 1 THEN
1981 print_debug('Error: ' || SQLCODE || ', ' || SQLERRM , 'GET_MO_ALLOC_STAT');
1982 END IF;
1983 RETURN g_not_allocated;
1984 END get_mo_alloc_stat;
1985
1986 END inv_wip_picking_pvt;
[Home] [Help]