1 PACKAGE BODY inv_wip_picking_pvt AS
2 /* $Header: INVVWPKB.pls 120.15 2012/02/10 11:43:32 rdudani ship $ */
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--commenting for 13702075,wms_be_pick_release is normal pick release process. We are using WIP component pick release here.
662 p_bus_event => wms_device_integration_pvt.WMS_BE_WIP_PICK_RELEASE --added for 13702075
663 , p_call_ctx => wms_device_integration_pvt.dev_req_user
664 , p_task_trx_id => p_mo_header_rec.header_id
665 , x_request_msg => l_req_msg
666 , x_return_status => l_api_return_status
667 , x_msg_count => x_msg_count
668 , x_msg_data => x_msg_data
669 );
670 IF (l_debug = 1) THEN
671 print_debug('Device_API Return Status = '|| l_api_return_status ||
672 ' : Request Msg = ' || l_req_msg, 'RELEASE_PICK_BATCH');
673 END IF;
674 END IF;
675
676 -- Standard call to commit
677 IF p_commit = fnd_api.g_true THEN
678 COMMIT;
679 l_savept_exists := FALSE;
680 END IF;
681
682 x_return_status := l_mo_allocation_status;
683
684 -- Bug 4288399, returning the table of errored records back to the calling program.
685 x_mo_line_errrec_tbl := l_wip_error_table;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 x_return_status := fnd_api.g_ret_sts_error;
690 /*Bug8585819,when pick_release raise exception,it is neccessary
691 to return the table of errored records back to the calling program.*/
692 x_mo_line_errrec_tbl := l_wip_error_table;
693 IF l_debug = 1 THEN
694 print_debug ('Release_pick_batch Error: ' || SQLERRM, 'RELEASE_PICK_BATCH');
695 END IF;
696 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
697 IF (l_savept_exists) THEN
698 ROLLBACK TO inv_wip_pick_release;
699 END IF;
700 IF g_conc_mode THEN
701 l_temp := fnd_concurrent.set_completion_status('ERROR', '');
702 END IF;
703 END release_pick_batch;
704
705
706
707 PROCEDURE pick_release(
708 x_return_status OUT NOCOPY VARCHAR2
709 , x_msg_count OUT NOCOPY NUMBER
710 , x_msg_data OUT NOCOPY VARCHAR2
711 , p_commit IN VARCHAR2
712 , p_init_msg_lst IN VARCHAR2
713 , p_mo_line_tbl IN inv_move_order_pub.trolin_tbl_type
714 , p_allow_partial_pick IN VARCHAR2
715 , p_grouping_rule_id IN NUMBER
716 , p_plan_tasks IN BOOLEAN
717 , p_call_wip_api IN BOOLEAN
718 ) IS
719
720 l_api_version_number NUMBER := 1.0;
721 l_line_index NUMBER; -- The index of the line in the table being processed
722 l_mo_line inv_move_order_pub.trolin_rec_type;
723 l_organization_id NUMBER; -- The OrgID to use (based on the MO Lines Passed in).
724 l_mo_header_id NUMBER; -- Move Order Header ID
725 l_mo_type NUMBER; -- The type of the move order (Should be only 5)
726 l_mo_number VARCHAR2(30); -- The move order number
727 l_api_return_status VARCHAR2(1);
728 l_processed_row_count NUMBER := 0; -- The number of rows which have been processed.
729 l_detail_rec_count NUMBER := 0;
730 l_quantity NUMBER;
731 l_detail_quantity NUMBER := 0; -- added for bug 9895550
732 l_transaction_quantity NUMBER;
733 l_primary_quantity NUMBER;
734 l_wip_alloc_qty NUMBER;
735 l_line_status NUMBER;
736 l_disable_cartonization VARCHAR2(1);
737 l_wip_entity_name VARCHAR2(240);
738 l_item_number VARCHAR2(40);
739
740 -- Used for processing WIP pick sets (ship_set_id)
741 l_cur_ship_set_id NUMBER := NULL;
742 l_set_index NUMBER;
743 l_start_index NUMBER;
744 l_set_process NUMBER;
745 l_start_process NUMBER;
746
747 l_tree_id NUMBER;
748 l_revision_control_code NUMBER;
749 l_lot_control_code NUMBER;
750
751 l_is_wms_org BOOLEAN := FALSE;
752
753 TYPE quantity_tree_tbl_type IS TABLE OF NUMBER
754 INDEX BY BINARY_INTEGER;
755
756 l_quantity_tree_tbl quantity_tree_tbl_type;
757 l_qtree_backup_tbl quantity_tree_tbl_type;
758
759 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
760
761 -- Bug 4349602: save all MOL IDs in current batch
762 TYPE l_molid_tbltyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
763 l_mol_id_tbl l_molid_tbltyp;
764 l_mol_id_index NUMBER;
765
766 BEGIN
767 -- Set savepoint for this API
768 IF (l_debug = 1) THEN
769 print_debug('Inside Pick_Release', 'PICK_RELEASE');
770 END IF;
771 SAVEPOINT pick_release;
772 -- Initialize API return status to success
773 x_return_status := fnd_api.g_ret_sts_success;
774
775 -- Initialize message list.
776 IF fnd_api.to_boolean(p_init_msg_lst) THEN
777 fnd_msg_pub.initialize;
778 END IF;
779
780 --Added bug 4634522
781 IF (WIP_CONSTANTS.DMF_PATCHSET_LEVEL = WIP_CONSTANTS.DMF_PATCHSET_I_VALUE) THEN
782 g_wip_patch_level := 1159;
783 ELSIF (WIP_CONSTANTS.DMF_PATCHSET_LEVEL = WIP_CONSTANTS.DMF_PATCHSET_J_VALUE) THEN
784 g_wip_patch_level := 11510;
785 END IF;
786 --end of fix for bug 4634522
787
788 IF p_mo_line_tbl.COUNT = 0 THEN
789 IF (l_debug = 1) THEN
790 print_debug('No Lines to pick', 'PICK_RELEASE');
791 END IF;
792 fnd_message.set_name('INV', 'INV_NO_LINES_TO_PICK');
793 fnd_msg_pub.ADD;
794 RAISE fnd_api.g_exc_unexpected_error;
795 END IF;
796
797 -- Bug 2038564: Clearing Qty Tree
798 inv_quantity_tree_pvt.clear_quantity_cache;
799
800 -- Validate that all move order lines are from the same org, that all lines
801 -- have a status of pre-approved (7) or approved (3), and that all of the move
802 -- order lines are of type Manufacturing Component Pick (5)
803 l_line_index := p_mo_line_tbl.FIRST;
804 l_mol_id_index := 1;
805 l_organization_id := p_mo_line_tbl(l_line_index).organization_id;
806 l_is_wms_org := inv_install.adv_inv_installed(l_organization_id);
807
808 --Bug 4288399, Printing Org ID as l_organization_id
809 IF (l_debug = 1) THEN
810 print_debug('MO Line count = ' || p_mo_line_tbl.COUNT ||
811 ' : Org ID = ' || l_organization_id, 'PICK_RELEASE');
812 END IF;
813
814 -- Bug 2666620: Moved it outside of LOOP so that it is done only once.
815 -- Verify that the move order type is of type Manufacturing Component Pick (5)
816 BEGIN
817 SELECT header_id, move_order_type, request_number
818 INTO l_mo_header_id, l_mo_type, l_mo_number
819 FROM mtl_txn_request_headers
820 WHERE header_id = p_mo_line_tbl(l_line_index).header_id;
821 EXCEPTION
822 WHEN NO_DATA_FOUND THEN
823 IF (l_debug = 1) THEN
824 print_debug('Error: Move Order Header not found', 'PICK_RELEASE');
825 END IF;
826 fnd_message.set_name('INV', 'INV_NO_HEADER_FOUND');
827 fnd_message.set_token('MO_LINE_ID', TO_CHAR(p_mo_line_tbl(l_line_index).line_id));
828 fnd_msg_pub.ADD;
829 RAISE fnd_api.g_exc_unexpected_error;
830 END;
831
832 IF l_mo_type <> inv_globals.g_move_order_mfg_pick THEN
833 IF (l_debug = 1) THEN
834 print_debug('Error: Trying to release non WIP move order', 'PICK_RELEASE');
835 END IF;
836 fnd_message.set_name('INV', 'INV_NOT_WIP_MOVE_ORDER');
837 fnd_message.set_token('MO_NUMBER', l_mo_number);
838 fnd_msg_pub.ADD;
839 RAISE fnd_api.g_exc_unexpected_error;
840 END IF;
841
842 LOOP
843 l_mo_line := p_mo_line_tbl(l_line_index);
844
845 IF (l_mo_line.return_status NOT IN (fnd_api.g_ret_sts_unexp_error, fnd_api.g_ret_sts_error) ) THEN
846 -- Verify that the lines are all for the same organization
847 IF l_mo_line.organization_id <> l_organization_id THEN
848 IF (l_debug = 1) THEN
849 print_debug('Error: Trying to pick for different org', 'PICK_RELEASE');
850 END IF;
851 fnd_message.set_name('INV', 'INV_PICK_DIFFERENT_ORG');
852 fnd_msg_pub.ADD;
853 RAISE fnd_api.g_exc_unexpected_error;
854 END IF;
855
856 -- Verify that the line status is approved or pre-approved
857 IF (l_mo_line.line_status NOT IN (3,7)) THEN
858 IF (l_debug = 1) THEN
859 print_debug('Error: Invalid Move Order Line Status', 'PICK_RELEASE');
860 END IF;
861 fnd_message.set_name('INV', 'INV_PICK_LINE_STATUS');
862 fnd_msg_pub.ADD;
863 RAISE fnd_api.g_exc_unexpected_error;
864 END IF;
865
866 -- Carton Grouping ID has to be stamped.
867 -- Bug 2844622: Skip this check if WIP patch level is below 11.5.9
868 IF g_wip_patch_level >= 1159 THEN
869 IF (l_is_wms_org AND NVL(l_mo_line.carton_grouping_id, fnd_api.g_miss_num) = fnd_api.g_miss_num) THEN
870 IF (l_debug = 1) THEN
871 print_debug('Error: No Carton Grouping ID specified', 'PICK_RELEASE');
872 END IF;
873 fnd_message.set_name('WMS', 'WMS_NO_CARTON_GROUP_ID');
874 fnd_msg_pub.ADD;
875 RAISE fnd_api.g_exc_unexpected_error;
876 END IF;
877 ELSE
878 IF (l_debug = 1) THEN
879 print_debug('WIP Patch Level = ' || g_wip_patch_level ||' so skipping Carton Group ID check', 'PICK_RELEASE');
880 END IF;
881 END IF;
882 -- Bug 2666620: End of Code Changes
883 l_mol_id_tbl(l_mol_id_index) := l_mo_line.line_id;
884 l_mol_id_index := l_mol_id_index + 1;
885 END IF; -- end if MO line status is error
886
887 -- We should create the quantity tree here so that we can
888 -- a) lock the tree
889 -- b) use the tree id to backup the quantity tree for ship
890 -- set and ship model id;
891 -- we only want to call create_tree once per org/item;
892 -- This should not be a performance hit as long as ARU
893 -- 1625268 has been applied.
894
895 IF NOT (l_quantity_tree_tbl.EXISTS(l_mo_line.inventory_item_id)) THEN
896 IF (l_debug = 1) THEN
897 print_debug('Creating Qty Tree for Item '|| TO_CHAR(l_mo_line.inventory_item_id), 'PICK_RELEASE');
898 END IF;
899
900 BEGIN
901 SELECT revision_qty_control_code, lot_control_code
902 INTO l_revision_control_code, l_lot_control_code
903 FROM mtl_system_items
904 WHERE organization_id = l_organization_id
905 AND inventory_item_id = l_mo_line.inventory_item_id;
906 EXCEPTION
907 WHEN NO_DATA_FOUND THEN
908 IF (l_debug = 1) THEN
909 print_debug('No Item Info found', 'PICK_RELEASE');
910 END IF;
911 RAISE fnd_api.g_exc_unexpected_error;
912 END;
913
914 inv_quantity_tree_pvt.create_tree
915 ( p_api_version_number => 1.0
916 , p_init_msg_lst => fnd_api.g_false
917 , x_return_status => l_api_return_status
918 , x_msg_count => x_msg_count
919 , x_msg_data => x_msg_data
920 , p_organization_id => l_organization_id
921 , p_inventory_item_id => l_mo_line.inventory_item_id
922 , p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
923 , p_is_revision_control => (l_revision_control_code = 2)
924 , p_is_lot_control => (l_lot_control_code = 2)
925 , p_is_serial_control => FALSE
926 , p_asset_sub_only => FALSE
927 , p_include_suggestion => FALSE
928 , p_demand_source_type_id => -99
929 , p_demand_source_header_id => -99
930 , p_demand_source_line_id => -99
931 , p_demand_source_delivery => NULL
932 , p_demand_source_name => NULL
933 , p_lot_expiration_date => SYSDATE
934 , x_tree_id => l_tree_id
935 , p_exclusive => inv_quantity_tree_pvt.g_exclusive
936 , p_pick_release => inv_quantity_tree_pvt.g_pick_release_yes
937 );
938
939 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
940 RAISE fnd_api.g_exc_error;
941 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
942 RAISE fnd_api.g_exc_unexpected_error;
943 END IF;
944
945 l_quantity_tree_tbl(l_mo_line.inventory_item_id) := l_tree_id;
946 END IF; -- not exist in quantity tree tbl
947
948 EXIT WHEN l_line_index = p_mo_line_tbl.LAST;
949 l_line_index := p_mo_line_tbl.NEXT(l_line_index);
950 END LOOP;
951
952 IF (l_debug = 1) THEN
953 print_debug('Validations complete, starting pick release', 'PICK_RELEASE');
954 END IF;
955 l_line_index := p_mo_line_tbl.FIRST;
956
957 LOOP
958 l_mo_line := p_mo_line_tbl(l_line_index);
959 IF (l_debug = 1) THEN
960 print_debug('Loop index: ' || TO_CHAR(l_line_index), 'PICK_RELEASE');
961 print_debug('MO line : ' || TO_CHAR(l_mo_line.line_id), 'PICK_RELEASE');
962 print_debug('Item : ' || TO_CHAR(l_mo_line.inventory_item_id), 'PICK_RELEASE');
963 print_debug('Quantity : ' || TO_CHAR(l_mo_line.quantity), 'PICK_RELEASE');
964 END IF;
965
966 IF (l_mo_line.return_status <> fnd_api.g_ret_sts_unexp_error
967 AND l_mo_line.return_status <> fnd_api.g_ret_sts_error)
968 THEN
969 IF l_mo_line.ship_set_id IS NOT NULL
970 AND (l_cur_ship_set_id IS NULL OR l_cur_ship_set_id <> l_mo_line.ship_set_id)
971 THEN
972 SAVEPOINT shipset;
973 l_cur_ship_set_id := l_mo_line.ship_set_id;
974 l_start_index := l_line_index;
975 l_start_process := l_processed_row_count;
976 l_qtree_backup_tbl.DELETE;
977 IF (l_debug = 1) THEN
978 print_debug('Start Pick Set: '|| TO_CHAR(l_cur_ship_set_id), 'PICK_RELEASE');
979 END IF;
980 ELSIF l_cur_ship_set_id IS NOT NULL AND l_mo_line.ship_set_id IS NULL THEN
981 IF (l_debug = 1) THEN
982 print_debug('End of Shipset: '|| TO_CHAR(l_cur_ship_set_id), 'PICK_RELEASE');
983 END IF;
984 l_cur_ship_set_id := NULL;
985 l_qtree_backup_tbl.DELETE;
986 END IF;
987
988 IF (l_mo_line.ship_set_id IS NOT NULL)
989 AND NOT (l_qtree_backup_tbl.EXISTS(l_mo_line.inventory_item_id)) THEN
990 IF (l_debug = 1) THEN
991 print_debug('Backing up qty tree: '|| TO_CHAR(l_tree_id), 'PICK_RELEASE');
992 END IF;
993 l_tree_id := l_quantity_tree_tbl(l_mo_line.inventory_item_id);
994 inv_quantity_tree_pvt.backup_tree(x_return_status => l_api_return_status, p_tree_id => l_tree_id);
995
996 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
997 RAISE fnd_api.g_exc_error;
998 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
999 RAISE fnd_api.g_exc_unexpected_error;
1000 END IF;
1001
1002 l_qtree_backup_tbl(l_mo_line.inventory_item_id) := l_tree_id;
1003 END IF; -- Pick set NOT NULL
1004
1005 -- Call the Pick Release Process_Line API on the current Move Order Line
1006 process_line(
1007 p_mo_line_rec => l_mo_line
1008 , p_allow_partial_pick => p_allow_partial_pick
1009 , p_grouping_rule_id => p_grouping_rule_id
1010 , p_plan_tasks => p_plan_tasks
1011 , p_call_wip_api => p_call_wip_api --Added bug 4634522
1012 , x_return_status => l_api_return_status
1013 , x_msg_count => x_msg_count
1014 , x_msg_data => x_msg_data
1015 , x_detail_rec_count => l_detail_rec_count
1016 );
1017 IF (l_debug = 1) THEN
1018 print_debug('Process Line Return Status = '|| l_api_return_status ||
1019 ' : Detail Rec Count = ' || to_char(l_detail_rec_count), 'PICK_RELEASE');
1020 END IF;
1021
1022 IF l_api_return_status <> fnd_api.g_ret_sts_success AND p_allow_partial_pick = fnd_api.g_false THEN
1023 fnd_message.set_name('INV', 'INV_COULD_NOT_PICK_FULL');
1024 fnd_msg_pub.ADD;
1025 RAISE fnd_api.g_exc_unexpected_error;
1026 END IF;
1027
1028 g_mo_line_stat_tbl(l_mo_line.line_id) := l_api_return_status;
1029
1030 SELECT quantity, NVL(quantity_detailed,0), line_status -- added for bug 9895550
1031 INTO l_quantity, l_detail_quantity, l_line_status -- added for bug 9895550
1032 FROM mtl_txn_request_lines
1033 WHERE line_id = l_mo_line.line_id;
1034
1035 SELECT NVL(SUM(transaction_quantity), 0), NVL(SUM(primary_quantity), 0)
1036 INTO l_transaction_quantity, l_primary_quantity
1037 FROM mtl_material_transactions_temp
1038 WHERE move_order_line_id = l_mo_line.line_id;
1039
1040 -- If the total allocated quantity is less than the requested
1041 -- quantity update the move order line to change the
1042 -- requested quantity to be equal to the allocated quantity.
1043
1044 IF (l_transaction_quantity < l_quantity)
1045 OR (l_transaction_quantity = 0 AND l_quantity = 0) THEN
1046 -- For shipsets, if any of the lines fail to allocate completely,
1047 -- rollback all allocations
1048
1049 print_debug('Value of g_pick_release_caller: '|| nvl(g_pick_release_caller,'null'), 'PICK_RELEASE'); -- added for bug 9895550
1050 print_debug('Value of g_old_detailed_qty: '|| g_old_detailed_qty, 'PICK_RELEASE'); -- added for bug 9895550
1051
1052 IF l_cur_ship_set_id IS NOT NULL
1053 AND g_pick_release_caller IS NULL -- added for bug 9895550
1054 THEN
1055 IF (l_debug = 1) THEN
1056 print_debug('Rollback for pick set: '|| TO_CHAR(l_cur_ship_set_id), 'PICK_RELEASE');
1057 END IF;
1058 ROLLBACK TO shipset;
1059 l_set_index := l_start_index;
1060 l_set_process := l_start_process;
1061
1062 -- loop through all move order lines for this ship set
1063 LOOP
1064 l_mo_line := p_mo_line_tbl(l_set_index);
1065 IF (l_debug = 1) THEN
1066 print_debug(TO_CHAR(l_set_process) || ' Rolling back allocations for MO line: '
1067 ||TO_CHAR(l_mo_line.line_id), 'PICK_RELEASE');
1068 END IF;
1069
1070 g_mo_line_stat_tbl(l_mo_line.line_id) := g_not_allocated;
1071
1072 IF l_qtree_backup_tbl.EXISTS(l_mo_line.inventory_item_id) THEN
1073 l_tree_id := l_qtree_backup_tbl(l_mo_line.inventory_item_id);
1074 IF (l_debug = 1) THEN
1075 print_debug('Restoring Quantity Tree: '|| TO_CHAR(l_tree_id), 'PICK_RELEASE');
1076 END IF;
1077
1078 inv_quantity_tree_pvt.restore_tree
1079 ( x_return_status => l_api_return_status
1080 , p_tree_id => l_tree_id
1081 );
1082
1083 IF (l_api_return_status = fnd_api.g_ret_sts_error) THEN
1084 IF (l_debug = 1) THEN
1085 print_debug('Error in Restore_Tree', 'Pick_Release');
1086 END IF;
1087 RAISE fnd_api.g_exc_error;
1088 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
1089 IF (l_debug = 1) THEN
1090 print_debug('Unexpected error in Restore_tree', 'PICK_RELEASE');
1091 END IF;
1092 RAISE fnd_api.g_exc_unexpected_error;
1093 END IF;
1094
1095 -- delete entry, so we don't restore tree more than once
1096 l_qtree_backup_tbl.DELETE(l_mo_line.inventory_item_id);
1097 END IF;
1098
1099 -- close the move order line
1100 UPDATE mtl_txn_request_lines
1101 SET quantity = 0, quantity_detailed = 0, line_status = 5 ,status_date =sysdate --BUG 6932648
1102 WHERE line_id = l_mo_line.line_id;
1103
1104 -- Exit if there are no more move order lines to detail
1105 -- or when the next move order is not for the same ship set.
1106 -- l_set_index should always be equal to the last line
1107 -- in the current ship set, so that the logic at the
1108 -- end of the outer loop works correctly.
1109 EXIT WHEN p_mo_line_tbl.LAST = l_set_index;
1110 l_set_index := p_mo_line_tbl.NEXT(l_set_index);
1111
1112 IF NVL(p_mo_line_tbl(l_set_index).ship_set_id, -1) <> l_cur_ship_set_id THEN
1113 l_set_index := p_mo_line_tbl.PRIOR(l_set_index);
1114 EXIT;
1115 END IF;
1116
1117 l_set_process := l_set_process + 1;
1118 END LOOP;
1119
1120 -- At the end of this loop, l_mo_line and l_set_index
1121 -- point to the last line for this ship set.
1122 l_line_index := l_set_index;
1123 l_cur_ship_set_id := NULL;
1124 l_processed_row_count := l_set_process;
1125 l_detail_rec_count := 0;
1126
1127 --
1128 -- Bug 2501138:
1129 -- Set txn qty to 0, so that we don't invoke
1130 -- wip_picking_pub.allocate_material
1131 --
1132 l_transaction_quantity := 0;
1133 l_primary_quantity := 0;
1134 l_qtree_backup_tbl.DELETE;
1135 IF (l_debug = 1) THEN
1136 print_debug('Finished rolling back all lines in shipset', 'PICK_RELEASE');
1137 END IF;
1138 ELSE
1139 UPDATE mtl_txn_request_lines
1140 SET quantity = l_transaction_quantity,
1141 primary_quantity = l_primary_quantity -- added for bug 9895550
1142 WHERE line_id = l_mo_line.line_id;
1143
1144 -- added for bug 9895550
1145 IF (l_cur_ship_set_id IS NOT NULL AND g_pick_release_caller = 'WMS-PICK-EXCEPTION' AND Nvl(g_old_detailed_qty,-999)>0) THEN
1146
1147 wip_picking_pub.unallocate_material(
1148 p_wip_entity_id => l_mo_line.txn_source_id
1149 , p_operation_seq_num => l_mo_line.txn_source_line_id
1150 , p_inventory_item_id => l_mo_line.inventory_item_id
1151 , p_repetitive_schedule_id => l_mo_line.reference_id
1152 , p_primary_quantity => (g_old_detailed_qty - l_detail_quantity) --pass backordered qty
1153 , x_return_status => l_api_return_status
1154 , x_msg_data => x_msg_data
1155 );
1156
1157 IF (l_debug = 1) THEN
1158 print_debug('wip_picking_pub.unallocate_material returned '|| l_api_return_status , 'PROCESS_LINE');
1159 END IF;
1160 END IF;
1161
1162
1163 IF (l_transaction_quantity = 0) THEN
1164 UPDATE mtl_txn_request_lines
1165 SET line_status = 5 ,status_date =sysdate --BUG 6932648
1166 WHERE line_id = l_mo_line.line_id;
1167 END IF;
1168 END IF; -- cur ship set id
1169 END IF; -- transaction quantity < quantity or transaction quantity is 0
1170
1171
1172 l_processed_row_count := l_processed_row_count + 1;
1173 IF (l_debug = 1) THEN
1174 print_debug('Processed row : ' || TO_CHAR(l_processed_row_count), 'PICK_RELEASE');
1175 print_debug('MO line ID : ' || TO_CHAR(l_mo_line.line_id), 'PICK_RELEASE');
1176 print_debug('Return Status : ' || l_api_return_status, 'PICK_RELEASE');
1177 print_debug('Dtl rec count : ' || TO_CHAR(l_detail_rec_count), 'PICK_RELEASE');
1178 END IF;
1179
1180 l_detail_rec_count := 0;
1181
1182 -- Update WIP with allocated qty if alloc qty > 0
1183 IF p_call_wip_api AND l_transaction_quantity > 0 THEN
1184 IF (l_debug = 1) THEN
1185 print_debug('Updating WIP with Allocated Qty: '|| l_primary_quantity, 'PICK_RELEASE');
1186 END IF;
1187 wip_picking_pub.allocate_material(
1188 p_wip_entity_id => l_mo_line.txn_source_id
1189 , p_operation_seq_num => l_mo_line.txn_source_line_id
1190 , p_inventory_item_id => l_mo_line.inventory_item_id
1191 , p_repetitive_schedule_id => l_mo_line.reference_id
1192 , p_primary_quantity => l_primary_quantity
1193 , x_quantity_allocated => l_wip_alloc_qty
1194 , x_return_status => l_api_return_status
1195 , x_msg_data => x_msg_data
1196 );
1197
1198 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1199 IF (l_debug = 1) THEN
1200 print_debug('Error in wip_picking_pub.allocate_material', 'PICK_RELEASE');
1201 print_debug('WIP entity ID: ' || TO_CHAR(l_mo_line.txn_source_id), 'PICK_RELEASE');
1202 print_debug('Op seq num : ' || TO_CHAR(l_mo_line.txn_source_line_id), 'PICK_RELEASE');
1203 print_debug('Rep sch ID : ' || TO_CHAR(l_mo_line.reference_id), 'PICK_RELEASE');
1204 print_debug('Item ID : ' || TO_CHAR(l_mo_line.inventory_item_id), 'PICK_RELEASE');
1205 END IF;
1206 RAISE fnd_api.g_exc_error;
1207 END IF;
1208 END IF;
1209 END IF; -- mo line return status <> ERROR
1210
1211 EXIT WHEN l_line_index = p_mo_line_tbl.LAST;
1212 l_line_index := p_mo_line_tbl.NEXT(l_line_index);
1213 END LOOP; -- end looping through MO lines
1214
1215 IF (l_debug = 1) THEN
1216 print_debug('Done looping through MO lines', 'PICK_RELEASE');
1217 END IF;
1218
1219 -- Bug 4349602: Deleting Move Order Lines which are not allocated
1220 BEGIN
1221 IF (l_debug = 1) THEN
1222 print_debug('Deleting closed MOLs..','PICK_RELEASE');
1223 END IF;
1224 FORALL ii IN l_mol_id_tbl.FIRST..l_mol_id_tbl.LAST
1225 DELETE FROM mtl_txn_request_lines mtrl
1226 WHERE line_status = 5
1227 AND line_id = l_mol_id_tbl(ii);
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 IF (l_debug = 1) THEN
1231 print_debug('Error in Deleting Move Order Lines: ' || sqlerrm
1232 ,'PICK_RELEASE');
1233 END IF;
1234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1235 END;
1236
1237 --Added for bug 13702075
1238 l_api_return_status := '';
1239 print_debug('SET wms_device_integration_pvt.WMS_CALL_DEVICE_REQUESTS BEFORE CALLING CARTONIZATION', 'PICK_RELEASE');
1240 IF wms_device_integration_pvt.wms_call_device_request IS NULL THEN
1241
1242 print_debug('BEFORE CALLING wms_device_integration_pvt.is_device_set_up()', 'PICK_RELEASE');
1243 wms_device_integration_pvt.is_device_set_up(
1244 l_organization_id,
1245 12, --added for 13702075, 12 is for wip pick release
1246 l_api_return_status);
1247 print_debug('AFTER CALLING wms_device_integration_pvt.is_device_set_up()', 'PICK_RELEASE');
1248 print_debug('VALUE OF wms_call_device_request IS ::::'||wms_device_integration_pvt.wms_call_device_request, 'PICK_RELEASE');
1249 END IF;
1250
1251 --Added for bug 13702075
1252
1253 IF l_is_wms_org THEN
1254 -- Call cartonization API to assign task types and split and merge tasks.
1255 -- Bug 2666620: Cartonization is not disabled and Packaging Mode is set
1256 IF (l_debug = 1) THEN
1257 print_debug('Calling Cartonization Engine', 'PICK_RELEASE');
1258 END IF;
1259
1260 -- Bug 2844622: Disable cartonization if WIP patch level is below 11.5.9
1261 IF g_wip_patch_level >= 1159 THEN
1262 l_disable_cartonization := 'N';
1263 ELSE
1264 l_disable_cartonization := 'Y';
1265 END IF;
1266
1267 wms_cartnzn_wrap.cartonize
1268 ( p_api_version => l_api_version_number
1269 , p_init_msg_list => fnd_api.g_false
1270 , p_commit => fnd_api.g_false
1271 , p_validation_level => fnd_api.g_valid_level_full
1272 , x_return_status => l_api_return_status
1273 , x_msg_count => x_msg_count
1274 , x_msg_data => x_msg_data
1275 , p_out_bound => 'Y'
1276 , p_org_id => l_organization_id
1277 , p_move_order_header_id => l_mo_header_id
1278 , p_disable_cartonization => l_disable_cartonization
1279 , p_packaging_mode => wms_cartnzn_wrap.mfg_pr_pkg_mode
1280 );
1281
1282 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1283 IF (l_debug = 1) THEN
1284 print_debug('Cartonization returned with an error status: '||l_api_return_status, 'Pick_Release');
1285 END IF;
1286 RAISE fnd_api.g_exc_unexpected_error;
1287 END IF;
1288 END IF;
1289
1290 -- Standard call to commit
1291 IF p_commit = fnd_api.g_true THEN
1292 COMMIT;
1293 END IF;
1294
1295 IF (l_debug = 1) THEN
1296 print_debug('x_return_status = '|| x_return_status, 'PICK_RELEASE');
1297 END IF;
1298
1299 EXCEPTION
1300 WHEN fnd_api.g_exc_error THEN
1301 ROLLBACK TO pick_release;
1302 inv_quantity_tree_pvt.clear_quantity_cache;
1303 x_return_status := fnd_api.g_ret_sts_error;
1304 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1305 WHEN OTHERS THEN
1306 ROLLBACK TO pick_release;
1307 inv_quantity_tree_pvt.clear_quantity_cache;
1308 x_return_status := fnd_api.g_ret_sts_unexp_error;
1309 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1310 END pick_release;
1311
1312
1313
1314 --
1315 -- Name
1316 -- PROCEDURE Process_Line
1317 --
1318 -- Package
1319 -- INV_Pick_Release_PVT
1320 --
1321 -- Purpose
1322 -- Pick releases the move order line passed in. Any necessary validation is
1323 -- assumed to have been done by the caller.
1324 --
1325 -- Input Parameters
1326 -- p_mo_line_rec
1327 -- The Move Order Line record to pick release
1328 -- p_allow_partial_pick
1329 -- TRUE if the pick release process should continue after a line fails to
1330 -- be detailed completely.
1331 -- FALSE if the process should stop and roll back all changes if a line
1332 -- cannot be fully detailed.
1333 --
1334 -- Output Parameters
1335 -- x_return_status
1336 -- if the process succeeds, the value is
1337 -- FND_API.G_RET_STS_SUCCESS;
1338 -- if there is an expected error, the value is
1339 -- fnd_api.g_ret_sts_error;
1340 -- if there is an unexpected error, the value is
1341 -- fnd_api.g_ret_sts_unexp_error;
1342 -- x_msg_count
1343 -- if there is one or more errors, the number of error messages
1344 -- in the buffer
1345 -- x_msg_data
1346 -- if there is one and only one error, the error message
1347 --
1348 -- (See FND_API package for more details about the above output parameters)
1349 --
1350
1351 PROCEDURE process_line
1352 ( p_mo_line_rec IN OUT NOCOPY inv_move_order_pub.trolin_rec_type
1353 , p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
1354 , p_grouping_rule_id IN NUMBER
1355 , p_plan_tasks IN BOOLEAN
1356 , p_call_wip_api IN BOOLEAN --Added bug 4634522
1357 , x_return_status OUT NOCOPY VARCHAR2
1358 , x_msg_count OUT NOCOPY NUMBER
1359 , x_msg_data OUT NOCOPY VARCHAR2
1360 , x_detail_rec_count OUT NOCOPY NUMBER
1361 ) IS
1362 -- Empty record for calling Create_Suggestions
1363 l_demand_rsvs_ordered inv_reservation_global.mtl_reservation_tbl_type;
1364 l_request_number NUMBER; -- MO Header number
1365 l_primary_uom VARCHAR2(3); -- The primary UOM for the item
1366 l_quantity_detailed NUMBER; -- The quantity for the current MO which was detailed in Primary UOM
1367 l_num_detail_recs NUMBER; -- The number of MO Line details for this MO Line.
1368 l_mol_allocation_status VARCHAR2(1); -- A flag indicating the status of the MOL Allocation.
1369 l_quantity_detailed_conv NUMBER; -- The quantity detailed for the current MO in UOM of MO
1370 l_api_return_status VARCHAR2(1); -- The return status of APIs called within the Process Line API.
1371 l_count NUMBER;
1372 l_message VARCHAR2(255);
1373 l_allocate_quantity NUMBER;
1374 l_use_pick_set_flag VARCHAR2(1);
1375
1376 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1377 BEGIN
1378 IF (l_debug = 1) THEN
1379 print_debug('Inside Process_Line', 'PROCESS_LINE');
1380 print_debug('WIP Entity ID: '|| TO_CHAR(p_mo_line_rec.txn_source_id), 'PROCESS_LINE');
1381 print_debug('Op seq num : '|| TO_CHAR(p_mo_line_rec.txn_source_line_id), 'PROCESS_LINE');
1382 print_debug('Rep sch ID : '|| TO_CHAR(p_mo_line_rec.reference_id), 'PROCESS_LINE');
1383 print_debug('Item ID : '|| TO_CHAR(p_mo_line_rec.inventory_item_id), 'PROCESS_LINE');
1384 print_debug('MO line ID : '|| TO_CHAR(p_mo_line_rec.line_id), 'PROCESS_LINE');
1385 print_debug('Unit num : '|| p_mo_line_rec.unit_number, 'PROCESS_LINE');
1386 print_debug('Project ID : '|| TO_CHAR(p_mo_line_rec.project_id), 'PROCESS_LINE');
1387 print_debug('Task ID : '|| TO_CHAR(p_mo_line_rec.task_id), 'PROCESS_LINE');
1388 END IF;
1389 SAVEPOINT process_line_pvt;
1390 x_detail_rec_count := 0;
1391 l_num_detail_recs := 0;
1392 -- Initialize API return status to success
1393 x_return_status := fnd_api.g_ret_sts_success;
1394
1395 -- Bug 4880578: return if MO line has 0 qty
1396 IF NVL(p_mo_line_rec.quantity,0) = 0 THEN
1397 IF (l_debug = 1) THEN
1398 print_debug('MO line has 0 qty: '||p_mo_line_rec.quantity,'PROCESS_LINE');
1399 END IF;
1400 x_return_status := g_not_allocated;
1401 RETURN;
1402 END IF;
1403
1404 l_allocate_quantity := nvl(p_mo_line_rec.quantity,0)
1405 - nvl(p_mo_line_rec.quantity_detailed, 0);
1406
1407 IF (l_debug = 1) THEN
1408 print_debug('Quantity to detail: '||l_allocate_quantity,'PROCESS_LINE');
1409 END IF;
1410
1411 -- Return success immediately if the line is already fully detailed
1412 IF l_allocate_quantity <= 0 THEN
1413 IF (l_debug = 1) THEN
1414 print_debug('MO line is already fully detailed', 'PROCESS_LINE');
1415 END IF;
1416 RETURN;
1417 END IF;
1418
1419 IF p_mo_line_rec.ship_set_id IS NOT NULL THEN
1420 l_use_pick_set_flag := 'Y';
1421 ELSE
1422 l_use_pick_set_flag := 'N';
1423 END IF;
1424
1425 -- Bug 2844622: Skip this callback if WIP patch level is below 11.5.9
1426 IF g_wip_patch_level >= 1159 AND p_call_wip_api THEN -- bug 4634522.Added p_call_wip_api
1427 --calling WIP callback API
1428 IF (l_debug = 1) THEN
1429 print_debug('Calling pre_allocate_material', 'PROCESS_LINE');
1430 END IF;
1431
1432 wip_picking_pub.pre_allocate_material
1433 ( p_wip_entity_id => p_mo_line_rec.txn_source_id
1434 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1435 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1436 , p_repetitive_schedule_id => p_mo_line_rec.reference_id
1437 , p_use_pickset_flag => l_use_pick_set_flag
1438 , p_allocate_quantity => l_allocate_quantity
1439 , x_return_status => l_api_return_status
1440 , x_msg_data => x_msg_data
1441 );
1442 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1443 IF (l_debug = 1) THEN
1444 print_debug('Error in pre_allocate_material','PROCESS_LINE');
1445 END IF;
1446 RAISE fnd_api.g_exc_unexpected_error;
1447 END IF;
1448 ELSE
1449 IF (l_debug = 1) THEN
1450 print_debug('WIP Patch Level = ' || g_wip_patch_level || ', so skipping pre_allocate_material', 'PROCESS_LINE');
1451 END IF;
1452 END IF;
1453
1454 IF (l_debug = 1) THEN
1455 print_debug('Calling Create Suggestions for MOLine: '|| p_mo_line_rec.line_id, 'PROCESS_LINE');
1456 END IF;
1457 inv_ppengine_pvt.create_suggestions
1458 ( p_api_version => 1.0
1459 , p_init_msg_list => fnd_api.g_false
1460 , p_commit => fnd_api.g_false
1461 , x_return_status => l_api_return_status
1462 , x_msg_count => x_msg_count
1463 , x_msg_data => x_msg_data
1464 , p_transaction_temp_id => p_mo_line_rec.line_id
1465 , p_reservations => l_demand_rsvs_ordered
1466 , p_suggest_serial => 'T'
1467 , p_plan_tasks => p_plan_tasks
1468 );
1469
1470 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1471 IF (l_debug = 1) THEN
1472 print_debug('Ret stat: '|| l_api_return_status || ', INV detailing failed', 'PROCESS_LINE');
1473 END IF;
1474 fnd_msg_pub.count_and_get(p_count => l_count, p_data => l_message, p_encoded => 'F');
1475
1476 IF (l_count = 0) THEN
1477 IF (l_debug = 1) THEN
1478 print_debug('No message from detailing engine', 'PROCESS_LINE');
1479 END IF;
1480 ELSIF (l_count = 1) THEN
1481 IF (l_debug = 1) THEN
1482 print_debug(l_message, 'PROCESS_LINE');
1483 END IF;
1484 ELSE
1485 FOR i IN 1 .. l_count LOOP
1486 l_message := fnd_msg_pub.get(i, 'F');
1487 IF (l_debug = 1) THEN
1488 print_debug(l_message, 'PROCESS_LINE');
1489 END IF;
1490 END LOOP;
1491
1492 fnd_msg_pub.delete_msg();
1493 END IF;
1494
1495 BEGIN
1496 SELECT request_number
1497 INTO l_request_number
1498 FROM mtl_txn_request_lines_v
1499 WHERE line_id = p_mo_line_rec.line_id;
1500 EXCEPTION
1501 WHEN NO_DATA_FOUND THEN
1502 RAISE fnd_api.g_exc_unexpected_error;
1503 END;
1504
1505 fnd_message.set_name('INV', 'INV_DETAILING_FAILED');
1506 fnd_message.set_token('LINE_NUM', TO_CHAR(p_mo_line_rec.line_number));
1507 fnd_message.set_token('MO_NUMBER', l_request_number);
1508 fnd_msg_pub.ADD;
1509 RAISE fnd_api.g_exc_unexpected_error;
1510 END IF;
1511
1512 IF (l_debug = 1) THEN
1513 print_debug('After calling Create Suggestions: Ret Status = '|| l_api_return_status, 'PROCESS_LINE');
1514 END IF;
1515
1516 -- Update the detailed quantity (and if possible, the sourcing information)
1517 SELECT NVL(SUM(primary_quantity), 0), COUNT(*)
1518 INTO l_quantity_detailed, l_num_detail_recs
1519 FROM mtl_material_transactions_temp
1520 WHERE move_order_line_id = p_mo_line_rec.line_id;
1521
1522 IF (l_debug = 1) THEN
1523 print_debug('Qty detailed = '|| l_quantity_detailed || ' : Num of Details = ' || l_num_detail_recs, 'PROCESS_LINE');
1524 END IF;
1525
1526 -- If the move order line is not fully detailed, update the return status as appropriate.
1527 IF l_quantity_detailed < p_mo_line_rec.primary_quantity AND p_allow_partial_pick = fnd_api.g_false THEN
1528 fnd_message.set_name('INV', 'INV_COULD_NOT_PICK_FULL');
1529 fnd_msg_pub.ADD;
1530 RAISE fnd_api.g_exc_unexpected_error;
1531 END IF;
1532
1533 IF (l_num_detail_recs = 0) THEN
1534 p_mo_line_rec.quantity_detailed := NVL(l_quantity_detailed, 0) + NVL(p_mo_line_rec.quantity_delivered, 0);
1535 IF (l_debug = 1) THEN
1536 print_debug('Updating Move Order Line', 'PROCESS_LINE');
1537 END IF;
1538 inv_trolin_util.update_row(p_mo_line_rec);
1539 l_mol_allocation_status := g_not_allocated;
1540 ELSIF l_num_detail_recs > 0 THEN
1541 IF l_quantity_detailed < p_mo_line_rec.primary_quantity THEN
1542 l_mol_allocation_status := g_partially_allocated;
1543 ELSE
1544 l_mol_allocation_status := g_completely_allocated;
1545 END IF;
1546 -- Calculate the quantity detailed in the UOM of the move order line
1547 SELECT primary_uom_code INTO l_primary_uom
1548 FROM mtl_system_items
1549 WHERE organization_id = p_mo_line_rec.organization_id
1550 AND inventory_item_id = p_mo_line_rec.inventory_item_id;
1551
1552 IF (l_primary_uom <> p_mo_line_rec.uom_code) THEN
1553 l_quantity_detailed_conv := inv_convert.inv_um_convert
1554 ( item_id => p_mo_line_rec.inventory_item_id
1555 , PRECISION => NULL
1556 , from_quantity => l_quantity_detailed
1557 , from_unit => l_primary_uom
1558 , to_unit => p_mo_line_rec.uom_code
1559 , from_name => NULL
1560 , to_name => NULL
1561 );
1562 IF (l_debug = 1) THEN
1563 print_debug('After calling convert', 'PROCESS_LINE');
1564 END IF;
1565
1566 -- Update the Move Order Line with the quantity which was detailed.
1567 IF (l_quantity_detailed_conv = -99999) THEN
1568 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
1569 fnd_message.set_token('UOM', p_mo_line_rec.uom_code);
1570 fnd_message.set_token('ROUTINE', 'Pick Release process');
1571 fnd_msg_pub.ADD;
1572 RAISE fnd_api.g_exc_unexpected_error;
1573 END IF;
1574 ELSE
1575 l_quantity_detailed_conv := l_quantity_detailed;
1576 END IF;
1577
1578 /*Bug7834526.We do not need the following call in Rel12 and up.
1579 -- bug 4634522 .begin
1580 IF (l_mol_allocation_status = g_partially_allocated) THEN
1581 IF (l_debug = 1) THEN
1582 print_debug(' p_mo_line_rec.quantit '||p_mo_line_rec.quantity || ' l_quantity_detailed_conv'||l_quantity_detailed_conv||
1583 'p_mo_line_rec.quantity_delivered '||p_mo_line_rec.quantity_delivered ||' p_mo_line_rec.detailed_quantity '||
1584 p_mo_line_rec.quantity_detailed , 'PROCESS_LINE');
1585 END IF;
1586
1587 wip_picking_pub.unallocate_material(
1588 p_wip_entity_id => p_mo_line_rec.txn_source_id
1589 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1590 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1591 , p_repetitive_schedule_id => p_mo_line_rec.reference_id
1592 , 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
1593 , x_return_status => l_api_return_status
1594 , x_msg_data => x_msg_data
1595 );
1596
1597 IF (l_debug = 1) THEN
1598 print_debug('wip_picking_pub.unallocate_material returned '|| l_api_return_status , 'PROCESS_LINE');
1599 END IF;
1600 END IF; -- end if fix for bug 4634522
1601 */
1602
1603 p_mo_line_rec.quantity_detailed := NVL(l_quantity_detailed_conv, 0) + NVL(p_mo_line_rec.quantity_delivered, 0);
1604 p_mo_line_rec.pick_slip_date := SYSDATE;
1605 IF (l_debug = 1) THEN
1606 print_debug('Detailed Qty = '|| p_mo_line_rec.quantity_detailed, 'PROCESS_LINE');
1607 print_debug('Updating Move Order Line', 'PROCESS_LINE');
1608 END IF;
1609 inv_trolin_util.update_row(p_mo_line_rec);
1610 update_mmtt_for_wip(x_return_status, p_mo_line_rec, p_grouping_rule_id);
1611 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1612 print_debug('Error occurred while updating MMTT with WIP Attributes','PROCESS_LINE');
1613 RAISE fnd_api.g_exc_unexpected_error;
1614 END IF;
1615 END IF; -- if l_num_detail_rec > 0
1616
1617 -- If the line was only partially detailed and the API was about to return success,
1618 -- set the return status to 'P' (Partial) or 'N' (None) instead.
1619 x_detail_rec_count := l_num_detail_recs;
1620 x_return_status := l_mol_allocation_status;
1621 IF l_debug = 1 THEN
1622 print_debug('Return status: ' || l_mol_allocation_status,'PROCESS_LINE');
1623 END IF;
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 ROLLBACK TO process_line_pvt;
1627 IF l_debug = 1 THEN
1628 print_debug('Error in process line: ' || sqlcode || ', ' || sqlerrm,'PROCESS_LINE');
1629 END IF;
1630 x_return_status := g_not_allocated;
1631 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
1632 END process_line;
1633
1634 PROCEDURE update_mol_for_wip
1635 ( x_return_status OUT NOCOPY VARCHAR2
1636 , x_msg_count OUT NOCOPY NUMBER
1637 , x_msg_data OUT NOCOPY VARCHAR2
1638 , p_move_order_line_id IN NUMBER
1639 , p_op_seq_num IN NUMBER
1640 ) IS
1641 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1642 BEGIN
1643 x_return_status := fnd_api.g_ret_sts_success;
1644 IF (l_debug = 1) THEN
1645 print_debug('p_move_order_line_id => '|| p_move_order_line_id, 'update_mol_for_wip');
1646 print_debug('p_op_seq_num => '|| p_op_seq_num, 'update_mol_for_wip');
1647 END IF;
1648
1649 UPDATE mtl_txn_request_lines mol
1650 SET mol.txn_source_line_id = p_op_seq_num
1651 WHERE mol.line_id = p_move_order_line_id
1652 AND EXISTS( SELECT ''
1653 FROM mtl_txn_request_headers moh
1654 WHERE moh.header_id = mol.header_id
1655 AND move_order_type = 5);
1656
1657 IF SQL%NOTFOUND THEN
1658 IF (l_debug = 1) THEN
1659 print_debug('No move order lines being updated', 'update_mol_for_wip');
1660 END IF;
1661 x_return_status := fnd_api.g_ret_sts_error;
1662 RETURN;
1663 END IF;
1664
1665 UPDATE mtl_material_transactions_temp mmtt
1666 SET mmtt.trx_source_line_id = p_op_seq_num
1667 WHERE mmtt.move_order_line_id = p_move_order_line_id
1668 AND EXISTS( SELECT ''
1669 FROM mtl_txn_request_headers moh, mtl_txn_request_lines mol
1670 WHERE mol.line_id = mmtt.move_order_line_id
1671 AND mol.header_id = moh.header_id
1672 AND move_order_type = 5);
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 x_return_status := fnd_api.g_ret_sts_unexp_error;
1676 END update_mol_for_wip;
1677
1678 PROCEDURE update_mmtt_for_wip(
1679 x_return_status OUT NOCOPY VARCHAR2
1680 , p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
1681 , p_grouping_rule_id IN NUMBER
1682 ) IS
1683 l_wip_entity_type NUMBER;
1684 l_repetitive_line_id NUMBER;
1685 l_department_id NUMBER;
1686 l_department_code bom_departments.department_code%TYPE;
1687 l_push_vs_pull VARCHAR2(4);
1688 l_pick_slip_number NUMBER;
1689 l_index NUMBER := 0;
1690 l_msg_data VARCHAR2(2000);
1691 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1692
1693 CURSOR c_mmtt IS
1694 SELECT transaction_temp_id, revision, subinventory_code, locator_id, transfer_subinventory, transfer_to_location, pick_slip_number
1695 FROM mtl_material_transactions_temp
1696 WHERE move_order_line_id = p_mo_line_rec.line_id;
1697 BEGIN
1698 get_wip_attributes(
1699 x_return_status => x_return_status
1700 , x_wip_entity_type => l_wip_entity_type
1701 , x_push_vs_pull => l_push_vs_pull
1702 , x_repetitive_line_id => l_repetitive_line_id
1703 , x_department_id => l_department_id
1704 , x_department_code => l_department_code
1705 , x_pick_slip_number => l_pick_slip_number
1706 , p_wip_entity_id => p_mo_line_rec.txn_source_id
1707 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1708 , p_rep_schedule_id => p_mo_line_rec.reference_id
1709 , p_organization_id => p_mo_line_rec.organization_id
1710 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1711 , p_transaction_type_id => p_mo_line_rec.transaction_type_id
1712 , p_get_pick_slip_number => FALSE
1713 );
1714
1715 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1716 print_debug('Error Occurred while getting WIP Attributes','UPDATE_MMTT_FOR_WIP');
1717 RAISE fnd_api.g_exc_unexpected_error;
1718 END IF;
1719
1720 FOR c_mmtt_rec IN c_mmtt LOOP
1721 l_index := l_index + 1;
1722
1723 IF c_mmtt_rec.pick_slip_number IS NULL THEN
1724 IF g_wip_patch_level >= 1159 THEN
1725 inv_pr_pick_slip_number.get_pick_slip_number(
1726 p_pick_grouping_rule_id => p_grouping_rule_id
1727 , p_org_id => p_mo_line_rec.organization_id
1728 , p_wip_entity_id => p_mo_line_rec.txn_source_id
1729 , p_rep_schedule_id => p_mo_line_rec.reference_id
1730 , p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1731 , p_dept_id => l_department_id
1732 , p_push_or_pull => l_push_vs_pull
1733 , p_supply_subinventory => c_mmtt_rec.transfer_subinventory
1734 , p_supply_locator_id => c_mmtt_rec.transfer_to_location
1735 , p_project_id => p_mo_line_rec.project_id
1736 , p_task_id => p_mo_line_rec.task_id
1737 , p_src_subinventory => c_mmtt_rec.subinventory_code
1738 , p_src_locator_id => c_mmtt_rec.locator_id
1739 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1740 , p_revision => c_mmtt_rec.revision
1741 , p_lot_number => NULL
1742 , x_pick_slip_number => l_pick_slip_number
1743 , x_api_status => x_return_status
1744 , x_error_message => l_msg_data
1745 );
1746
1747 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1748 IF (l_debug = 1) THEN
1749 print_debug('Error occurred in getting the Pick Slip Number: '|| l_msg_data, 'UPDATE_WITH_PICK_SLIP');
1750 END IF;
1751 fnd_message.set_name('INV','INV_NO_PICK_SLIP_NUMBER');
1752 fnd_msg_pub.add;
1753 RAISE fnd_api.g_exc_unexpected_error;
1754 END IF;
1755 ELSE
1756 BEGIN
1757 SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL INTO l_pick_slip_number FROM DUAL;
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760 fnd_message.set_name('INV','INV_NO_PICK_SLIP_NUMBER');
1761 fnd_msg_pub.add;
1762 RAISE fnd_api.g_exc_unexpected_error;
1763 END;
1764 END IF;
1765 ELSE
1766 l_pick_slip_number := c_mmtt_rec.pick_slip_number;
1767 END IF;
1768
1769 IF p_mo_line_rec.transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_WIP_ISSUE THEN
1770 UPDATE mtl_material_transactions_temp
1771 SET transaction_source_id = p_mo_line_rec.txn_source_id
1772 , trx_source_line_id = p_mo_line_rec.txn_source_line_id
1773 , demand_source_header_id = p_mo_line_rec.txn_source_id
1774 , demand_source_line = p_mo_line_rec.txn_source_line_id
1775 , transaction_source_type_id = inv_globals.g_sourcetype_wip
1776 , transaction_type_id = p_mo_line_rec.transaction_type_id
1777 , transaction_action_id = inv_globals.g_action_issue
1778 , wip_entity_type = l_wip_entity_type
1779 , repetitive_line_id = l_repetitive_line_id
1780 , operation_seq_num = p_mo_line_rec.txn_source_line_id
1781 , department_id = l_department_id
1782 , department_code = l_department_code
1783 , lock_flag = 'N'
1784 , primary_switch = l_index
1785 , wip_supply_type = 1
1786 , negative_req_flag = SIGN(transaction_quantity)
1787 , required_flag = '1'
1788 , pick_slip_number = l_pick_slip_number
1789 WHERE transaction_temp_id = c_mmtt_rec.transaction_temp_id;
1790 ELSIF p_mo_line_rec.transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_REPL_SUBXFR THEN
1791 UPDATE mtl_material_transactions_temp
1792 SET transaction_source_id = p_mo_line_rec.txn_source_id
1793 , trx_source_line_id = p_mo_line_rec.txn_source_line_id
1794 , demand_source_header_id = p_mo_line_rec.txn_source_id
1795 , demand_source_line = p_mo_line_rec.txn_source_line_id
1796 , transaction_source_type_id = inv_globals.g_sourcetype_inventory
1797 , transaction_type_id = p_mo_line_rec.transaction_type_id
1798 , transaction_action_id = inv_globals.g_action_subxfr
1799 , wip_entity_type = l_wip_entity_type
1800 , wip_supply_type = NULL -- Bug#2057540
1801 , pick_slip_number = l_pick_slip_number
1802 WHERE transaction_temp_id = c_mmtt_rec.transaction_temp_id;
1803 END IF;
1804 END LOOP;
1805 EXCEPTION
1806 WHEN OTHERS THEN
1807 IF (l_debug = 1) THEN
1808 print_debug('Exception Occurred: Code = ' || SQLCODE || ' : Error '|| SQLERRM, 'UPDATE_MMTT_FOR_WIP');
1809 END IF;
1810 x_return_status := fnd_api.g_ret_sts_unexp_error;
1811 END update_mmtt_for_wip;
1812
1813 PROCEDURE get_wip_attributes(
1814 x_return_status OUT NOCOPY VARCHAR2
1815 , x_wip_entity_type OUT NOCOPY NUMBER
1816 , x_push_vs_pull OUT NOCOPY VARCHAR2
1817 , x_repetitive_line_id OUT NOCOPY NUMBER
1818 , x_department_id OUT NOCOPY NUMBER
1819 , x_department_code OUT NOCOPY VARCHAR2
1820 , x_pick_slip_number OUT NOCOPY NUMBER
1821 , p_wip_entity_id IN NUMBER
1822 , p_operation_seq_num IN NUMBER
1823 , p_rep_schedule_id IN NUMBER
1824 , p_organization_id IN NUMBER
1825 , p_inventory_item_id IN NUMBER
1826 , p_transaction_type_id IN NUMBER
1827 , p_get_pick_slip_number IN BOOLEAN
1828 ) IS
1829
1830 CURSOR c_wip_entity_type IS
1831 SELECT entity_type
1832 FROM wip_entities
1833 WHERE wip_entity_id = p_wip_entity_id;
1834
1835 CURSOR c_repetitive_line_id IS
1836 SELECT line_id
1837 FROM wip_repetitive_schedules
1838 WHERE repetitive_schedule_id = p_rep_schedule_id
1839 AND organization_id = p_organization_id
1840 AND wip_entity_id = p_wip_entity_id;
1841
1842 CURSOR c_push_vs_pull IS
1843 SELECT decode(wip_supply_type,1,'PUSH',2,'PULL',3,'PULL')
1844 FROM wip_requirement_operations
1845 WHERE p_rep_schedule_id IS null
1846 AND wip_entity_id = p_wip_entity_id
1847 AND inventory_item_id = p_inventory_item_id
1848 AND operation_seq_num = p_operation_seq_num
1849 AND organization_id = p_organization_id
1850 UNION ALL
1851 SELECT decode(wip_supply_type,1,'PUSH',2,'PULL',3,'PULL')
1852 FROM wip_requirement_operations
1853 WHERE p_rep_schedule_id IS NOT NULL
1854 AND wip_entity_id = p_wip_entity_id
1855 AND inventory_item_id = p_inventory_item_id
1856 AND operation_seq_num = p_operation_seq_num
1857 AND organization_id = p_organization_id
1858 AND repetitive_schedule_id = p_rep_schedule_id;
1859
1860 CURSOR c_discrete_dept IS
1861 SELECT wo.department_id, bd.department_code
1862 FROM wip_operations wo, bom_departments bd
1863 WHERE wo.wip_entity_id = p_wip_entity_id
1864 AND wo.organization_id = p_organization_id
1865 AND wo.operation_seq_num = p_operation_seq_num
1866 AND bd.department_id = wo.department_id;
1867
1868 CURSOR c_repetitive_dept IS
1869 SELECT wo.department_id, bd.department_code
1870 FROM wip_operations wo, bom_departments bd
1871 WHERE wo.wip_entity_id = p_wip_entity_id
1872 AND wo.organization_id = p_organization_id
1873 AND wo.operation_seq_num = p_operation_seq_num
1874 AND wo.repetitive_schedule_id = p_rep_schedule_id
1875 AND bd.department_id = wo.department_id;
1876
1877 CURSOR c_flow_dept IS
1878 SELECT bos.department_id, bd.department_code
1879 FROM bom_departments bd
1880 , bom_operation_sequences bos
1881 , bom_operational_routings bor
1882 , wip_flow_schedules wfs
1883 WHERE wfs.wip_entity_id = p_wip_entity_id
1884 AND wfs.organization_id = p_organization_id
1885 AND bor.assembly_item_id = wfs.primary_item_id
1886 AND bor.organization_id = wfs.organization_id
1887 AND (bor.alternate_routing_designator = wfs.alternate_routing_designator
1888 OR (wfs.alternate_routing_designator IS NULL
1889 AND bor.alternate_routing_designator IS NULL))
1890 AND bos.routing_sequence_id = bor.routing_sequence_id
1891 AND bos.operation_type = 1
1892 AND bos.effectivity_date >= SYSDATE
1893 AND bd.department_id = bos.department_id;
1894
1895 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1896
1897 BEGIN
1898 x_return_status := fnd_api.g_ret_sts_success;
1899
1900 -- Entity type
1901 OPEN c_wip_entity_type;
1902 FETCH c_wip_entity_type INTO x_wip_entity_type;
1903 IF c_wip_entity_type%NOTFOUND THEN
1904 IF (l_debug = 1) THEN
1905 print_debug('Couldnt determine Entity Type for EntityID = '|| p_wip_entity_id, 'UPDATE_MMTT_FOR_WIP');
1906 END IF;
1907 CLOSE c_wip_entity_type;
1908 RAISE fnd_api.g_exc_unexpected_error;
1909 END IF;
1910 CLOSE c_wip_entity_type;
1911
1912 -- Departments
1913 IF x_wip_entity_type IN (1,5,6) THEN
1914 OPEN c_discrete_dept;
1915 FETCH c_discrete_dept INTO x_department_id, x_department_code;
1916 CLOSE c_discrete_dept;
1917 ELSIF x_wip_entity_type = 2 THEN
1918 OPEN c_repetitive_dept;
1919 FETCH c_repetitive_dept INTO x_department_id, x_department_code;
1920 CLOSE c_repetitive_dept;
1921 ELSIF x_wip_entity_type = 4 THEN
1922 OPEN c_flow_dept;
1923 FETCH c_flow_dept INTO x_department_id, x_department_code;
1924 CLOSE c_flow_dept;
1925 END IF;
1926
1927 -- Repetitive Line ID
1928 IF p_transaction_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_WIP_ISSUE THEN
1929 IF x_wip_entity_type = 2 THEN
1930 IF p_rep_schedule_id IS NULL THEN
1931 IF (l_debug = 1) THEN
1932 print_debug('Repetitive Schedule ID cannot be null for Entity Type 2', 'UPDATE_MMTT_FOR_WIP');
1933 END IF;
1934 RAISE fnd_api.g_exc_unexpected_error;
1935 ELSE
1936 OPEN c_repetitive_line_id;
1937 FETCH c_repetitive_line_id INTO x_repetitive_line_id;
1938 IF c_repetitive_line_id%NOTFOUND THEN
1939 IF (l_debug = 1) THEN
1940 print_debug('Unable to determine RepLineID for RepSchID '||p_rep_schedule_id, 'UPDATE_MMTT_FOR_WIP');
1941 END IF;
1942 RAISE fnd_api.g_exc_unexpected_error;
1943 END IF;
1944 CLOSE c_repetitive_line_id;
1945 END IF;
1946 END IF;
1947 END IF;
1948
1949 -- Supply Type
1950 IF x_wip_entity_type IN (1, 2, 5, 6) THEN
1951 OPEN c_push_vs_pull;
1952 FETCH c_push_vs_pull INTO x_push_vs_pull;
1953 CLOSE c_push_vs_pull;
1954 ELSIF x_wip_entity_type = 4 THEN
1955 x_push_vs_pull := 'PULL';
1956 END IF;
1957
1958 -- Pick Slip Number
1959 IF p_get_pick_slip_number THEN
1960 SELECT WSH_PICK_SLIP_NUMBERS_S.NEXTVAL INTO x_pick_slip_number FROM DUAL;
1961 END IF;
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 x_return_status := fnd_api.g_ret_sts_error;
1965 END get_wip_attributes;
1966
1967 FUNCTION get_mo_alloc_stat RETURN VARCHAR2 IS
1968 l_mo_alloc_stat VARCHAR2(1) := g_not_allocated;
1969 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1970 l_first_line_id NUMBER := 0;
1971 l_last_line_id NUMBER := 0;
1972 l_cur_line_id NUMBER := 0;
1973
1974 l_num_success NUMBER := 0;
1975 l_num_partial NUMBER := 0;
1976 l_num_no_alloc NUMBER := 0;
1977 BEGIN
1978 l_first_line_id := g_mo_line_stat_tbl.FIRST;
1979 l_last_line_id := g_mo_line_stat_tbl.LAST;
1980
1981 IF l_debug = 1 THEN
1982 print_debug('First line ID: ' || to_char(l_first_line_id) || ', Last line ID: ' || to_char(l_last_line_id), 'GET_MO_ALLOC_STAT');
1983 END IF;
1984
1985 l_cur_line_id := l_first_line_id;
1986 LOOP
1987 IF l_debug = 1 THEN
1988 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');
1989 END IF;
1990
1991 IF g_mo_line_stat_tbl(l_cur_line_id) = g_completely_allocated THEN
1992 l_num_success := l_num_success + 1;
1993 ELSIF g_mo_line_stat_tbl(l_cur_line_id) = g_partially_allocated THEN
1994 l_num_partial := l_num_partial + 1;
1995 ELSIF g_mo_line_stat_tbl(l_cur_line_id) = g_not_allocated THEN
1996 l_num_no_alloc := l_num_no_alloc + 1;
1997 END IF;
1998
1999 IF l_cur_line_id = l_last_line_id THEN
2000 EXIT;
2001 END IF;
2002
2003 l_cur_line_id := g_mo_line_stat_tbl.NEXT(l_cur_line_id);
2004 END LOOP;
2005
2006 -- Bug 5469486: Add the number of lines where MOL creation failed
2007 l_num_no_alloc := l_num_no_alloc + NVL(g_mol_fail_count,0);
2008
2009 IF l_debug = 1 THEN
2010 print_debug('Line status counts:: Successes: ' || l_num_success ||', Partial: ' || l_num_partial ||', None: ' || l_num_no_alloc, 'GET_MO_ALLOC_STAT');
2011 END IF;
2012
2013 IF l_num_success > 0 AND l_num_partial = 0 AND l_num_no_alloc = 0 THEN
2014 l_mo_alloc_stat := g_completely_allocated;
2015 ELSIF l_num_partial > 0 OR (l_num_success > 0 AND l_num_no_alloc > 0) THEN
2016 l_mo_alloc_stat := g_partially_allocated;
2017 ELSE
2018 l_mo_alloc_stat := g_not_allocated;
2019 END IF;
2020
2021 IF l_debug = 1 THEN
2022 print_debug('Overall status: ' || l_mo_alloc_stat, 'GET_MO_ALLOC_STAT');
2023 END IF;
2024
2025 RETURN l_mo_alloc_stat;
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 IF l_debug = 1 THEN
2029 print_debug('Error: ' || SQLCODE || ', ' || SQLERRM , 'GET_MO_ALLOC_STAT');
2030 END IF;
2031 RETURN g_not_allocated;
2032 END get_mo_alloc_stat;
2033
2034 END inv_wip_picking_pvt;
[Home] [Help]