[Home] [Help]
PACKAGE BODY: APPS.GME_PICKING_PVT
Source
1 PACKAGE BODY gme_picking_pvt AS
2 /* $Header: GMEVPCKB.pls 120.16.12020000.3 2013/01/30 15:42:45 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_PICKING_PVT';
5 g_partially_allocated BOOLEAN := FALSE;
6 g_fully_allocated BOOLEAN := FALSE;
7 g_not_allocated BOOLEAN := FALSE;
8
9
10 /*************************************************************************************************/
11 /* Oracle Process Manufacturing Process Execution APIs */
12 /* Contents: GME Picking Procedures. */
13 /* File Name: GMEVPCKB.pls */
14 /* HISTORY:
15 /* Susruth D. Bug#5311713 Commented the return status and put the message in the log file */
16 /* GME_NO_MATERIALS_SELECTED */
17
18 -- HALUTHRA 22-SEP-2008 Bug 7383625
19 -- Added IF condition to populate detailed_quantity
20 -- in PROCEDURE process_line
21
22 -- A.Mishra 15-May-2009 Bug 8481421
23 -- The fix includes adding the code to also update the Quantity Tree along with
24 -- the Material reservation, which was missing initially.
25 -- Procedure Process_line is modified to add the code for updating the quantity tree by calling
26 -- the code from the INV side (inv_quantity_tree_pub.update_quantities)
27
28 -- G.Muratore 31-Aug-2010 Bug 9941121
29 -- Pass grouping rule id to create_move_order_hdr procedure.
30 -- Procedure: pick_material
31
32 -- G.Muratore 22-Jun-2011 Bug 12613813
33 -- Picking is now also considering the locator value.
34 -- Procedure: process_line
35
36 -- G.Muratore 02-DEC-2011 Bug 13076579
37 -- Code is restructured to only create move order header and lines for picking if the
38 -- open qty is more than the sum of non detailed qty across all open move order lines.
39 -- PROCEDURE: pick_material
40 /*************************************************************************************************/
41
42 PROCEDURE conc_picking (
43 err_buf OUT NOCOPY VARCHAR2
44 ,ret_code OUT NOCOPY VARCHAR2
45 ,p_organization_id IN NUMBER
46 ,p_all_batches IN VARCHAR2
47 , -- 1 = All, 2 = Backordered
48 p_include_pending IN VARCHAR2
49 ,p_include_wip IN VARCHAR2
50 ,p_from_batch IN VARCHAR2
51 ,p_to_batch IN VARCHAR2
52 ,p_oprn_no IN VARCHAR2
53 ,p_oprn_vers IN NUMBER
54 ,p_product_no IN VARCHAR2
55 ,p_ingredient_no IN VARCHAR2
56 ,p_days_forward IN NUMBER
57 ,p_from_req_date IN VARCHAR2
58 ,p_to_req_date IN VARCHAR2
59 ,p_pick_grouping_rule IN VARCHAR2
60 ,p_print_pick_slip IN VARCHAR2 DEFAULT 'N'
61 ,p_plan_tasks IN VARCHAR2 DEFAULT 'N'
62 ,p_sales_order IN VARCHAR2)
63 IS
64 l_api_name CONSTANT VARCHAR2 (30) := 'conc_picking';
65 l_return_status VARCHAR2 (1);
66 l_where VARCHAR2 (4000);
67 l_sql_stmt VARCHAR2 (4000);
68 l_msg_data VARCHAR2 (2000);
69 l_conc_request_id NUMBER;
70 l_msg_count NUMBER;
71
72 TYPE l_picking_tab IS TABLE OF gme_ingred_pick_vw%ROWTYPE
73 INDEX BY BINARY_INTEGER;
74
75 l_picking_tbl l_picking_tab;
76 l_mat_req_tbl gme_picking_pvt.mtl_req_tab;
77 build_where_err EXCEPTION;
78 pick_material_err EXCEPTION;
79
80 /* Bug 5212556 Added the following ref cursor etc */
81 TYPE pick_ref IS REF CURSOR;
82 l_pick_cursor pick_ref;
83 BEGIN
84 IF g_debug IS NOT NULL THEN
85 gme_debug.log_initialize ('ConcPicking');
86 END IF;
87 IF g_debug <= gme_debug.g_log_procedure THEN
88 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
89 END IF;
90 l_where := 'NVL(open_qty,0) > 0';
91 l_where := l_where || ' AND organization_id = :organization_id';
92 IF (TO_NUMBER(p_all_batches) = 2) THEN
93 l_where := l_where || ' AND NVL(backordered_qty,0) > 0';
94 END IF;
95 IF (NVL (p_include_pending, 'N') = 'Y') AND (NVL (p_include_wip, 'N') = 'Y') THEN
96 l_where := l_where || ' AND batch_status IN (1,2)';
97 ELSIF (NVL (p_include_pending, 'N') = 'Y') THEN
98 l_where := l_where || ' AND batch_status = 1';
99 ELSIF (NVL (p_include_wip, 'N') = 'Y') THEN
100 l_where := l_where || ' AND batch_status = 2';
101 ELSE
102 l_where := l_where || ' AND batch_status NOT IN (1,2)';
103 END IF;
104 /* Bug 5370563 oprn_no and oprn_vers can be null so added NVL */
105 l_where := l_where || ' AND LPAD(batch_no, 32, 0) >= LPAD(NVL(:from_batch, batch_no), 32, 0)'
106 || ' AND LPAD(batch_no, 32, 0) <= LPAD(NVL(:to_batch, batch_no), 32, 0)'
107 || ' AND NVL(oprn_no, '' '') LIKE NVL(:oprn_no, NVL(oprn_no, '' ''))'
108 || ' AND NVL(oprn_vers, -1) = NVL(:oprn_vers, NVL(oprn_vers, -1))'
109 || ' AND (:product_no IS NULL OR batch_id IN (SELECT DISTINCT batch_id FROM gme_material_details'
110 || ' WHERE organization_id = :organization_id'
111 || ' AND line_type = 1 AND inventory_item_id IN'
112 || ' (SELECT inventory_item_id FROM mtl_system_items_kfv WHERE organization_id = :organization_id'
113 || ' AND concatenated_segments LIKE :product_no)))'
114 || ' AND (:ingredient_no IS NULL OR batch_id IN (SELECT DISTINCT batch_id FROM gme_material_details'
115 || ' WHERE organization_id = :organization_id'
116 || ' AND line_type = -1 AND inventory_item_id IN'
117 || ' (SELECT inventory_item_id FROM mtl_system_items_kfv WHERE organization_id = :organization_id'
118 || ' AND concatenated_segments LIKE :ingredient_no)))'
119 || ' AND material_requirement_date <= SYSDATE + NVL(:days_forward, 100000)'
120 || ' AND material_requirement_date >= NVL(:from_req_date, material_requirement_date)'
121 || ' AND material_requirement_date <= NVL(:to_req_date, material_requirement_date)';
122 l_sql_stmt := 'SELECT * FROM gme_ingred_pick_vw WHERE ' || l_where;
123 IF g_debug <= gme_debug.g_log_statement THEN
124 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| l_sql_stmt);
125 END IF;
126 OPEN l_pick_cursor FOR l_sql_stmt
127 USING p_organization_id, p_from_batch, p_to_batch, p_oprn_no, p_oprn_vers,
128 p_product_no, p_organization_id, p_organization_id, p_product_no,
129 p_ingredient_no, p_organization_id, p_organization_id, p_ingredient_no,
130 p_days_forward, fnd_date.canonical_to_date(p_from_req_date),
131 fnd_date.canonical_to_date(p_to_req_date);
132 FETCH l_pick_cursor BULK COLLECT INTO l_picking_tbl;
133 CLOSE l_pick_cursor;
134 IF g_debug <= gme_debug.g_log_statement THEN
135 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':No. of records found = '|| l_picking_tbl.COUNT);
136 END IF;
137
138 FOR i IN 1 .. l_picking_tbl.COUNT LOOP
139 l_mat_req_tbl (i).organization_id :=
140 l_picking_tbl (i).organization_id;
141 l_mat_req_tbl (i).batch_id := l_picking_tbl (i).batch_id;
142 l_mat_req_tbl (i).material_detail_id :=
143 l_picking_tbl (i).material_detail_id;
144 l_mat_req_tbl (i).inventory_item_id :=
145 l_picking_tbl (i).inventory_item_id;
146 l_mat_req_tbl (i).revision := l_picking_tbl (i).revision;
147 l_mat_req_tbl (i).subinventory := l_picking_tbl (i).subinventory;
148 l_mat_req_tbl (i).locator_id := l_picking_tbl (i).locator_id;
149 l_mat_req_tbl (i).open_qty := l_picking_tbl (i).open_qty;
150 l_mat_req_tbl (i).dtl_um := l_picking_tbl (i).dtl_um;
151 l_mat_req_tbl (i).mtl_req_date :=
152 l_picking_tbl (i).material_requirement_date;
153 END LOOP;
154
155 gme_picking_pvt.pick_material
156 (p_mtl_req_tbl => l_mat_req_tbl
157 ,p_task_group_id => TO_NUMBER
158 (p_pick_grouping_rule)
159 ,p_print_pick_slip => p_print_pick_slip
160 ,p_plan_tasks => p_plan_tasks
161 ,x_return_status => l_return_status
162 ,x_conc_request_id => l_conc_request_id);
163
164 IF g_debug <= gme_debug.g_log_statement THEN
165 gme_debug.put_line ( g_pkg_name
166 || '.'
167 || l_api_name
168 || ':Return from pick_material is '
169 || l_return_status);
170 END IF;
171
172 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
173 RAISE pick_material_err;
174 END IF;
175
176 IF g_debug <= gme_debug.g_log_procedure THEN
177 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
178 END IF;
179 EXCEPTION
180 WHEN build_where_err OR pick_material_err THEN
181 gme_common_pvt.count_and_get (x_count => l_msg_count
182 ,x_data => l_msg_data);
183 raise_application_error (-20000, l_msg_data, TRUE);
184 WHEN OTHERS THEN
185 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
186
187 IF g_debug <= gme_debug.g_log_unexpected THEN
188 gme_debug.put_line ( 'When others exception in '
189 || g_pkg_name
190 || '.'
191 || l_api_name
192 || ' Error is '
193 || SQLERRM);
194 END IF;
195
196 gme_common_pvt.count_and_get (x_count => l_msg_count
197 ,x_data => l_msg_data);
198 raise_application_error (-20001, l_msg_data, TRUE);
199 END conc_picking;
200
201 -- Added p_called_by parameter to be used for Endeca view.
202 /* Bug 5212556 Added inventory_item_id */
203 FUNCTION get_open_qty (
204 p_organization_id IN NUMBER
205 ,p_batch_id IN NUMBER
206 ,p_material_detail_id IN NUMBER
207 ,p_inventory_item_id IN NUMBER
208 ,p_subinventory IN VARCHAR2
209 ,p_plan_qty IN NUMBER
210 ,p_wip_plan_qty IN NUMBER
211 ,p_actual_qty IN NUMBER
212 ,p_backordered_qty IN NUMBER
213 ,p_dtl_um IN VARCHAR2
214 ,p_called_by IN VARCHAR2 DEFAULT 'P')
215 RETURN NUMBER
216 IS
217 l_api_name CONSTANT VARCHAR2 (30) := 'get_open_qty';
218 l_open_qty NUMBER := 0;
219 l_return_status VARCHAR2 (1);
220 l_mtl_dtl_rec gme_material_details%ROWTYPE;
221 BEGIN
222 IF g_debug <= gme_debug.g_log_procedure THEN
223 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
224 || l_api_name);
225 END IF;
226
227 l_mtl_dtl_rec.organization_id := p_organization_id;
228 l_mtl_dtl_rec.batch_id := p_batch_id;
229 l_mtl_dtl_rec.material_detail_id := p_material_detail_id;
230 l_mtl_dtl_rec.inventory_item_id := p_inventory_item_id;
231 l_mtl_dtl_rec.subinventory := p_subinventory;
232 l_mtl_dtl_rec.plan_qty := p_plan_qty;
233 l_mtl_dtl_rec.wip_plan_qty := p_wip_plan_qty;
234 l_mtl_dtl_rec.actual_qty := p_actual_qty;
235 l_mtl_dtl_rec.backordered_qty := p_backordered_qty;
236 l_mtl_dtl_rec.dtl_um := p_dtl_um;
237
238
239 -- Added p_called_by parameter to be used for Endeca view.
240 gme_common_pvt.get_open_qty (p_mtl_dtl_rec => l_mtl_dtl_rec
241 -- ,p_called_by => 'P'
242 ,p_called_by => p_called_by
243 ,x_open_qty => l_open_qty
244 ,x_return_status => l_return_status);
245
246 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
247 RETURN 0;
248 ELSE
249 RETURN l_open_qty;
250 END IF;
251 EXCEPTION
252 WHEN OTHERS THEN
253 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
254
255 IF g_debug <= gme_debug.g_log_unexpected THEN
256 gme_debug.put_line ( 'When others exception in '
257 || g_pkg_name
258 || '.'
259 || l_api_name
260 || ' Error is '
261 || SQLERRM);
262 END IF;
263
264 RETURN 0;
265 END get_open_qty;
266
267 PROCEDURE pick_material (
268 p_mtl_req_tbl IN gme_picking_pvt.mtl_req_tab
269 ,p_task_group_id IN NUMBER
270 ,p_print_pick_slip IN VARCHAR2 DEFAULT 'N'
271 ,p_plan_tasks IN VARCHAR2 DEFAULT 'N'
272 ,x_return_status OUT NOCOPY VARCHAR2
273 ,x_conc_request_id OUT NOCOPY NUMBER)
274 IS
275 l_api_name CONSTANT VARCHAR2 (30) := 'pick_material';
276 l_count NUMBER := 0;
277 l_msg_count NUMBER;
278 l_move_order_header_id NUMBER;
279 l_conc_req_id NUMBER;
280 l_return_status VARCHAR2 (1);
281 l_msg_data VARCHAR2 (2000);
282 l_plan_tasks BOOLEAN;
283 l_mtl_dtl_tbl gme_common_pvt.material_details_tab;
284 l_out_mtl_dtl_tbl gme_common_pvt.material_details_tab;
285 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
286 l_trolin_rec inv_move_order_pub.trolin_rec_type;
287
288 CURSOR cur_pending_move_orders (
289 v_org_id NUMBER
290 ,v_batch_id NUMBER
291 ,v_material_detail_id NUMBER)
292 IS
293 SELECT l.line_id, l.header_id
294 FROM mtl_txn_request_lines l, mtl_txn_request_headers h
295 WHERE l.organization_id = v_org_id
296 AND transaction_source_type_id =
297 gme_common_pvt.g_txn_source_type
298 AND l.txn_source_id = v_batch_id
299 AND l.txn_source_line_id = v_material_detail_id
300 -- Bug 13076579 - exclude those that are already fully detailed.
301 AND l.quantity <> l.quantity_detailed
302 AND l.line_status NOT IN (5, 6)
303 AND h.header_id = l.header_id
304 AND h.move_order_type NOT IN
305 (gme_common_pvt.g_invis_move_order_type
306 ,inv_globals.g_move_order_put_away)
307 ORDER BY l.header_id, l.line_id;
308
309 -- Bug 13076579
310 l_index NUMBER;
311 l_hdr_created NUMBER;
312 l_mo_sum NUMBER;
313
314 -- Bug 13076579 - get the count of all open move orders to see what is still open.
315 CURSOR cur_pending_move_orders_cnt (
316 v_org_id NUMBER
317 ,v_batch_id NUMBER
318 ,v_material_detail_id NUMBER)
319 IS
320 SELECT count(1)
321 FROM mtl_txn_request_lines l, mtl_txn_request_headers h
322 WHERE l.organization_id = v_org_id
323 AND transaction_source_type_id =
324 gme_common_pvt.g_txn_source_type
325 AND l.txn_source_id = v_batch_id
326 AND l.txn_source_line_id = v_material_detail_id
327 AND l.line_status NOT IN (5, 6)
328 AND h.header_id = l.header_id
329 AND h.move_order_type NOT IN
330 (gme_common_pvt.g_invis_move_order_type
331 ,inv_globals.g_move_order_put_away);
332
333
334 -- Bug 13076579 - get the sum across all open move orders to see what is still open.
335 CURSOR cur_pending_move_orders_sum (
336 v_org_id NUMBER
337 ,v_batch_id NUMBER
338 ,v_material_detail_id NUMBER)
339 IS
340 SELECT NVL(sum(l.quantity - l.quantity_detailed), 0)
341 FROM mtl_txn_request_lines l, mtl_txn_request_headers h
342 WHERE l.organization_id = v_org_id
343 AND transaction_source_type_id =
344 gme_common_pvt.g_txn_source_type
345 AND l.txn_source_id = v_batch_id
346 AND l.txn_source_line_id = v_material_detail_id
347 AND l.line_status NOT IN (5, 6)
348 AND h.header_id = l.header_id
349 AND h.move_order_type NOT IN
350 (gme_common_pvt.g_invis_move_order_type
351 ,inv_globals.g_move_order_put_away)
352 GROUP BY l.txn_source_line_id;
353
354 TYPE pend_lines_tab IS TABLE OF cur_pending_move_orders%ROWTYPE
355 INDEX BY BINARY_INTEGER;
356
357 l_pend_lines_tbl pend_lines_tab;
358 no_materials_picked EXCEPTION;
359 create_move_order_err EXCEPTION;
360 setup_failure EXCEPTION;
361 process_line_err EXCEPTION;
362 print_pickslip_err EXCEPTION;
363 BEGIN
364 IF g_debug IS NOT NULL THEN
365 gme_debug.log_initialize ('PickMaterial');
366 END IF;
367
368 IF g_debug <= gme_debug.g_log_procedure THEN
369 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
370 || l_api_name);
371 END IF;
372
373 x_return_status := fnd_api.g_ret_sts_success;
374 l_count := p_mtl_req_tbl.COUNT;
375
376 IF g_debug <= gme_debug.g_log_statement THEN
377 gme_debug.put_line ( g_pkg_name
378 || '.'
379 || l_api_name
380 || ':Material Requirement count is '
381 || l_count);
382 END IF;
383
384 IF (l_count = 0) THEN
385 RAISE no_materials_picked;
386 END IF;
387
388 IF NOT gme_common_pvt.g_setup_done THEN
389 gme_common_pvt.g_setup_done :=
390 gme_common_pvt.setup
391 (p_org_id => p_mtl_req_tbl (1).organization_id);
392
393 IF NOT gme_common_pvt.g_setup_done THEN
394 RAISE setup_failure;
395 END IF;
396 END IF;
397
398 IF g_debug <= gme_debug.g_log_statement THEN
399 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':After setup');
400 END IF;
401
402 -- Bug 13076579 - Moved creation of MO hdr inside loop to avoid creating it unnecessarily.
403 /*
404 gme_move_orders_pvt.create_move_order_hdr
405 (p_organization_id => p_mtl_req_tbl (1).organization_id
406 ,p_move_order_type => gme_common_pvt.g_move_order_type
407 ,p_grouping_rule_id => p_task_group_id -- Bug 9941121
408 ,x_move_order_header_id => l_move_order_header_id
409 ,x_return_status => l_return_status);
410
411 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
412 RAISE create_move_order_err;
413 END IF;
414
415 IF g_debug <= gme_debug.g_log_statement THEN
416 gme_debug.put_line ( g_pkg_name
417 || '.'
418 || l_api_name
419 || ':Move order header created is '
420 || l_move_order_header_id);
421 END IF;
422 */
423
424 l_hdr_created := 0;
425 l_index := 0;
426
427 FOR i IN 1 .. l_count LOOP
428 OPEN cur_pending_move_orders_cnt (p_mtl_req_tbl (i).organization_id
429 ,p_mtl_req_tbl (i).batch_id
430 ,p_mtl_req_tbl (i).material_detail_id);
431
432 FETCH cur_pending_move_orders_cnt INTO l_mo_sum;
433 CLOSE cur_pending_move_orders_cnt;
434
435 IF (l_mo_sum > 0) THEN
436 -- Bug 13076579 - get the sum across all open move orders.
437 OPEN cur_pending_move_orders_sum (p_mtl_req_tbl (i).organization_id
438 ,p_mtl_req_tbl (i).batch_id
439 ,p_mtl_req_tbl (i).material_detail_id);
440
441 FETCH cur_pending_move_orders_sum INTO l_mo_sum;
442 CLOSE cur_pending_move_orders_sum;
443 END IF;
444
445 IF g_debug <= gme_debug.g_log_statement THEN
446 gme_debug.put_line ('iteration is '|| TO_CHAR(i));
447 gme_debug.put_line ('material_detail_id is '|| TO_CHAR(p_mtl_req_tbl (i).material_detail_id));
448 gme_debug.put_line ('open_qty is '|| TO_CHAR(p_mtl_req_tbl (i).open_qty));
449 gme_debug.put_line ('l_mo_sum is '|| TO_CHAR(l_mo_sum));
450 END IF;
451
452 -- Bug 13076579 - Do not create a move order if the existing MO already accounts for the open qty.
453 IF l_mo_sum < p_mtl_req_tbl (i).open_qty THEN
454 -- Bug 13076579 - Do not create a move order hdr if not needed.
455 IF l_hdr_created = 0 THEN
456 gme_move_orders_pvt.create_move_order_hdr
457 (p_organization_id => p_mtl_req_tbl (1).organization_id
458 ,p_move_order_type => gme_common_pvt.g_move_order_type
459 ,p_grouping_rule_id => p_task_group_id -- Bug 9941121
460 ,x_move_order_header_id => l_move_order_header_id
461 ,x_return_status => l_return_status);
462
463 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
464 RAISE create_move_order_err;
465 END IF;
466
467 IF g_debug <= gme_debug.g_log_statement THEN
468 gme_debug.put_line ( g_pkg_name
469 || '.'
470 || l_api_name
471 || ':Move order header created is '
472 || l_move_order_header_id);
473 END IF;
474 l_hdr_created := 1;
475 END IF;
476
477 l_index := l_index + 1;
478 l_mtl_dtl_tbl (l_index).inventory_item_id :=
479 p_mtl_req_tbl (i).inventory_item_id;
480 l_mtl_dtl_tbl (l_index).organization_id :=
481 p_mtl_req_tbl (i).organization_id;
482 l_mtl_dtl_tbl (l_index).plan_qty := p_mtl_req_tbl (i).open_qty - l_mo_sum; -- Account only for the delta.
483 l_mtl_dtl_tbl (l_index).dtl_um := p_mtl_req_tbl (i).dtl_um;
484 l_mtl_dtl_tbl (l_index).revision := p_mtl_req_tbl (i).revision;
485 l_mtl_dtl_tbl (l_index).batch_id := p_mtl_req_tbl (i).batch_id;
486 l_mtl_dtl_tbl (l_index).material_detail_id :=
487 p_mtl_req_tbl (i).material_detail_id;
488 l_mtl_dtl_tbl (l_index).material_requirement_date :=
489 p_mtl_req_tbl (i).mtl_req_date;
490 l_mtl_dtl_tbl (l_index).subinventory := p_mtl_req_tbl (i).subinventory;
491 l_mtl_dtl_tbl (l_index).locator_id := p_mtl_req_tbl (i).locator_id;
492 l_mtl_dtl_tbl (l_index).line_type := gme_common_pvt.g_line_type_ing;
493 END IF;
494 END LOOP;
495
496 -- Try to allocate any other move order lines that exist and are not allocated. These could
497 -- be move orders created during batch create or not fully detailed previously by picking.
498 FOR i IN 1 .. l_count LOOP
499 OPEN cur_pending_move_orders (p_mtl_req_tbl (i).organization_id
500 ,p_mtl_req_tbl (i).batch_id
501 ,p_mtl_req_tbl (i).material_detail_id);
502
503 FETCH cur_pending_move_orders
504 BULK COLLECT INTO l_pend_lines_tbl;
505
506 CLOSE cur_pending_move_orders;
507 END LOOP;
508
509 IF g_debug <= gme_debug.g_log_statement THEN
510 gme_debug.put_line
511 ( g_pkg_name
512 || '.'
513 || l_api_name
514 || ':No. of lines to create move_order_lines is '
515 || l_mtl_dtl_tbl.COUNT);
516 END IF;
517
518 gme_move_orders_pvt.create_move_order_lines
519 (p_move_order_header_id => l_move_order_header_id
520 ,p_move_order_type => gme_common_pvt.g_move_order_type
521 ,p_material_details_tbl => l_mtl_dtl_tbl
522 ,x_material_details_tbl => l_out_mtl_dtl_tbl
523 ,x_trolin_tbl => l_trolin_tbl
524 ,x_return_status => l_return_status);
525
526 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
527 RAISE create_move_order_err;
528 END IF;
529
530 l_count := l_trolin_tbl.COUNT;
531
532 IF g_debug <= gme_debug.g_log_statement THEN
533 gme_debug.put_line ( g_pkg_name
534 || '.'
535 || l_api_name
536 || ':No. Move order lines created is '
537 || l_count);
538 gme_debug.put_line ( g_pkg_name
539 || '.'
540 || l_api_name
541 || ':No. Pending Move order lines is '
542 || l_pend_lines_tbl.COUNT);
543 END IF;
544
545 FOR i IN 1 .. l_pend_lines_tbl.COUNT LOOP
546 l_count := l_count + 1;
547 l_trolin_tbl (l_count) :=
548 inv_trolin_util.query_row (l_pend_lines_tbl (i).line_id);
549 END LOOP;
550
551 FOR i IN 1 .. l_trolin_tbl.COUNT LOOP
552 gme_picking_pvt.process_line (p_mo_line_rec => l_trolin_tbl
553 (i)
554 ,p_grouping_rule_id => p_task_group_id
555 ,p_plan_tasks => p_plan_tasks
556 ,x_return_status => l_return_status);
557
558 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
559 RAISE process_line_err;
560 END IF;
561 END LOOP;
562
563 IF ( p_print_pick_slip = 'Y'
564 AND (g_fully_allocated OR g_partially_allocated) ) THEN
565 IF g_debug <= gme_debug.g_log_statement THEN
566 gme_debug.put_line ( g_pkg_name
567 || '.'
568 || l_api_name
569 || ':Calling print picklsip');
570 END IF;
571
572 IF (p_plan_tasks = 'Y') THEN
573 l_plan_tasks := TRUE;
574 ELSE
575 l_plan_tasks := FALSE;
576 END IF;
577
578 l_conc_req_id :=
579 inv_pr_pick_slip_number.print_pick_slip
580 (x_return_status => l_return_status
581 ,x_msg_data => l_msg_data
582 ,x_msg_count => l_msg_count
583 ,p_organization_id => p_mtl_req_tbl (1).organization_id
584 ,p_mo_request_number => l_move_order_header_id
585 ,p_plan_tasks => l_plan_tasks);
586
587
588 IF g_debug <= gme_debug.g_log_statement THEN
589 gme_debug.put_line ( g_pkg_name
590 || '.'
591 || l_api_name
592 || ':Print picklsip returns '
593 || l_return_status);
594 gme_debug.put_line ( g_pkg_name
595 || '.'
596 || l_api_name
597 || ':Print picklsip message '
598 || l_msg_data);
599 END IF;
600
601 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
602 RAISE print_pickslip_err;
603 END IF;
604 END IF;
605
606 IF (l_conc_req_id IS NOT NULL) THEN
607 IF (NOT (g_partially_allocated) AND NOT (g_not_allocated) ) THEN
608 gme_common_pvt.log_message
609 (p_message_code => 'GME_PICKED_ALL_PRINTED'
610 ,p_token1_name => 'MO_NUMBER'
611 ,p_token1_value => l_move_order_header_id
612 ,p_token2_name => 'CONC_REQUEST_ID'
613 ,p_token2_value => l_conc_req_id);
614 IF g_debug <= gme_debug.g_log_unexpected THEN
615 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
616 END IF;
617 ELSIF (g_partially_allocated OR g_fully_allocated) THEN
618 gme_common_pvt.log_message
619 (p_message_code => 'GME_PICKED_PARTIAL_PRINTED'
620 ,p_token1_name => 'MO_NUMBER'
621 ,p_token1_value => l_move_order_header_id
622 ,p_token2_name => 'CONC_REQUEST_ID'
623 ,p_token2_value => l_conc_req_id);
624 IF g_debug <= gme_debug.g_log_unexpected THEN
625 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
626 END IF;
627 END IF;
628 ELSE
629 IF (NOT (g_partially_allocated) AND NOT (g_not_allocated) ) THEN
630 gme_common_pvt.log_message
631 (p_message_code => 'GME_PICKED_ALL'
632 ,p_token1_name => 'MO_NUMBER'
633 ,p_token1_value => l_move_order_header_id);
634 IF g_debug <= gme_debug.g_log_unexpected THEN
635 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
636 END IF;
637 ELSIF (g_partially_allocated OR g_fully_allocated) THEN
638 gme_common_pvt.log_message
639 (p_message_code => 'GME_PICKED_PARTIAL'
640 ,p_token1_name => 'MO_NUMBER'
641 ,p_token1_value => l_move_order_header_id);
642 IF g_debug <= gme_debug.g_log_unexpected THEN
643 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
644 END IF;
645 ELSIF (NOT (g_partially_allocated) AND NOT (g_fully_allocated) ) THEN
646 gme_common_pvt.log_message
647 (p_message_code => 'GME_PICKED_NO_ALLOC'
648 ,p_token1_name => 'MO_NUMBER'
649 ,p_token1_value => l_move_order_header_id);
650 IF g_debug <= gme_debug.g_log_unexpected THEN
651 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
652 END IF;
653 END IF;
654 END IF;
655
656 IF g_debug <= gme_debug.g_log_procedure THEN
657 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
658 END IF;
659 EXCEPTION
660 WHEN no_materials_picked THEN
661 --Bug#5311713
662 FND_MESSAGE.SET_NAME('GME','GME_NO_MATERIALS_SELECTED');
663 FND_FILE.PUT_LINE(FND_FILE.log,FND_MESSAGE.GET);
664 --gme_common_pvt.log_message ('GME_NO_MATERIALS_SELECTED');
665 --x_return_status := fnd_api.g_ret_sts_error;
666 WHEN setup_failure THEN
667 x_return_status := fnd_api.g_ret_sts_error;
668 WHEN create_move_order_err OR process_line_err OR print_pickslip_err THEN
669 x_return_status := l_return_status;
670 WHEN OTHERS THEN
671 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
672
673 IF g_debug <= gme_debug.g_log_unexpected THEN
674 gme_debug.put_line ( 'When others exception in '
675 || g_pkg_name
676 || '.'
677 || l_api_name
678 || ' Error is '
679 || SQLERRM);
680 END IF;
681
682 x_return_status := fnd_api.g_ret_sts_unexp_error;
683 END pick_material;
684
685 PROCEDURE process_line (
686 p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
687 ,p_grouping_rule_id IN NUMBER
688 ,p_plan_tasks IN VARCHAR2 DEFAULT 'N'
689 ,x_return_status OUT NOCOPY VARCHAR2)
690 IS
691 l_api_name CONSTANT VARCHAR2 (30) := 'process_line';
692
693 -- Bug 8481421 - Variables added
694 l_res_ordered_index NUMBER; -- An index to the elements of the ordered and filtered reservations table.
695 l_prev_rsv_detailed_qty NUMBER; -- The existing qty detailed for a reservation.
696 l_prev_rsv_detailed_qty2 NUMBER; -- The existing qty2 detailed for a reservation.
697 l_reservation_detailed_qty NUMBER;
698 l_rsv_detailed_qty2 NUMBER;
699 l_primary_uom VARCHAR2(3); -- The primary UOM for the item
700 l_secondary_uom VARCHAR2(3); -- The secondary UOM for the item
701 l_api_return_status VARCHAR2(1); -- The return status of APIs called within the Process Line API.
702 l_revision_control_code NUMBER;
703 l_lot_control_code NUMBER;
704 l_serial_number_control_code NUMBER;
705 l_reservation_id NUMBER;
706 l_rsv_detailed_qty_conv NUMBER; -- The qty detailed for a reservation. (In reservation UOM)
707 l_is_serial_control BOOLEAN;
708 l_is_revision_control BOOLEAN;
709 l_is_lot_control BOOLEAN;
710 item_rec mtl_system_items%ROWTYPE;
711 l_mtl_dtl_rec gme_material_details%ROWTYPE;
712 l_qty_on_hand NUMBER;
713 l_qty_res_on_hand NUMBER;
714 l_qty_res NUMBER;
715 l_qty_sug NUMBER;
716 l_qty_att NUMBER;
717 l_qty_available_to_reserve NUMBER;
718 l_sec_qty_available_to_reserve NUMBER; -- The quantity which can still be reserved.
719 l_sec_qty_on_hand NUMBER; -- The org-wide quantity on-hand
720 l_sec_qty_res_on_hand NUMBER; -- The org-wide reservable quantity on-hand
721 l_sec_qty_res NUMBER; -- The org-wide quantity reserved
722 l_sec_qty_sug NUMBER; -- The org-wide quantity suggested
723 l_sec_qty_att NUMBER; -- The org-wide available to transact
724 l_sec_quantity_to_reserve NUMBER; -- The additional quantity which should be reserved.
725
726 -- End New variables for Bug 8481421
727
728 CURSOR cur_detailed_qty (v_move_order_line_id NUMBER)
729 IS
730 SELECT SUM (transaction_quantity) qty_detailed
731 ,SUM (secondary_transaction_quantity) sec_qty_detailed
732 FROM mtl_material_transactions_temp
733 WHERE move_order_line_id = v_move_order_line_id;
734
735 CURSOR cur_mmtt (v_move_order_line_id NUMBER)
736 IS
737 SELECT *
738 FROM mtl_material_transactions_temp
739 WHERE move_order_line_id = v_move_order_line_id;
740
741 l_count NUMBER := 0;
742 l_msg_count NUMBER;
743 l_qty_detailed NUMBER;
744 l_sec_qty_detailed NUMBER;
745 l_backordered_qty NUMBER;
746 l_pick_slip_number NUMBER;
747 l_msg_data VARCHAR2 (2000);
748 l_move_order_header_id NUMBER;
749 l_return_status VARCHAR2 (1);
750 l_plan_tasks BOOLEAN;
751 l_mo_line_rec inv_move_order_pub.trolin_rec_type;
752 l_resv_tbl gme_common_pvt.reservations_tab;
753 l_inv_resv_tbl inv_reservation_global.mtl_reservation_tbl_type;
754 create_suggestions_err EXCEPTION;
755 get_pick_slip_err EXCEPTION;
756 BEGIN
757 IF g_debug <= gme_debug.g_log_procedure THEN
758 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
759 || l_api_name);
760 END IF;
761
762 x_return_status := fnd_api.g_ret_sts_success;
763 gme_reservations_pvt.get_material_reservations
764 (p_organization_id => p_mo_line_rec.organization_id
765 ,p_batch_id => p_mo_line_rec.txn_source_id
766 ,p_material_detail_id => p_mo_line_rec.txn_source_line_id
767 ,x_return_status => l_return_status
768 ,x_reservations_tbl => l_resv_tbl);
769
770 IF g_debug <= gme_debug.g_log_statement THEN
771 gme_debug.put_line ( g_pkg_name
772 || '.'
773 || l_api_name
774 || ':No. of reservations is '
775 || l_resv_tbl.COUNT);
776 END IF;
777
778 FOR i IN 1 .. l_resv_tbl.COUNT LOOP
779 --
780 -- bug 10254285
781 -- Added NVL condition to handle the partial reservation case
782 --
783 -- Bug 12613813 - consider locator value also.
784 IF (NVL(l_resv_tbl (i).subinventory_code , '-1') <> p_mo_line_rec.to_subinventory_code) OR
785 (NVL(l_resv_tbl (i).locator_id , '-1') <> NVL(p_mo_line_rec.to_locator_id, '-1')) THEN
786 l_count := l_count + 1;
787 l_inv_resv_tbl (l_count).reservation_id :=
788 l_resv_tbl (i).reservation_id;
789 l_inv_resv_tbl (l_count).requirement_date :=
790 l_resv_tbl (i).requirement_date;
791 l_inv_resv_tbl (l_count).organization_id :=
792 l_resv_tbl (i).organization_id;
793 l_inv_resv_tbl (l_count).inventory_item_id :=
794 l_resv_tbl (i).inventory_item_id;
795 l_inv_resv_tbl (l_count).demand_source_type_id :=
796 l_resv_tbl (i).demand_source_type_id;
797 l_inv_resv_tbl (l_count).demand_source_header_id :=
798 l_resv_tbl (i).demand_source_header_id;
799 l_inv_resv_tbl (l_count).demand_source_line_id :=
800 l_resv_tbl (i).demand_source_line_id;
801 l_inv_resv_tbl (l_count).primary_uom_code :=
802 l_resv_tbl (i).primary_uom_code;
803 l_inv_resv_tbl (l_count).secondary_uom_code :=
804 l_resv_tbl (i).secondary_uom_code;
805 l_inv_resv_tbl (l_count).reservation_uom_code :=
806 l_resv_tbl (i).reservation_uom_code;
807 l_inv_resv_tbl (l_count).reservation_quantity :=
808 l_resv_tbl (i).reservation_quantity;
809 l_inv_resv_tbl (l_count).primary_reservation_quantity :=
810 l_resv_tbl (i).primary_reservation_quantity;
811 l_inv_resv_tbl (l_count).secondary_reservation_quantity :=
812 l_resv_tbl (i).secondary_reservation_quantity;
813 l_inv_resv_tbl (l_count).detailed_quantity :=
814 l_resv_tbl (i).detailed_quantity;
815 l_inv_resv_tbl (l_count).secondary_detailed_quantity :=
816 l_resv_tbl (i).secondary_detailed_quantity;
817 l_inv_resv_tbl (l_count).supply_source_type_id :=
818 l_resv_tbl (i).supply_source_type_id;
819 l_inv_resv_tbl (l_count).supply_source_header_id :=
820 l_resv_tbl (i).supply_source_header_id;
821 l_inv_resv_tbl (l_count).supply_source_line_id :=
822 l_resv_tbl (i).supply_source_line_id;
823 l_inv_resv_tbl (l_count).revision := l_resv_tbl (i).revision;
824 l_inv_resv_tbl (l_count).subinventory_code :=
825 l_resv_tbl (i).subinventory_code;
826 l_inv_resv_tbl (l_count).locator_id := l_resv_tbl (i).locator_id;
827 l_inv_resv_tbl (l_count).lot_number := l_resv_tbl (i).lot_number;
828 l_inv_resv_tbl (l_count).lpn_id := l_resv_tbl (i).lpn_id;
829 END IF;
830 END LOOP;
831
832 IF g_debug <= gme_debug.g_log_statement THEN
833 gme_debug.put_line
834 ( g_pkg_name
835 || '.'
836 || l_api_name
837 || ':No. of reservations passed to create suggestions is '
838 || l_count);
839 END IF;
840
841 IF (p_plan_tasks = 'Y') THEN
842 l_plan_tasks := TRUE;
843 ELSE
844 l_plan_tasks := FALSE;
845 END IF;
846
847 wms_engine_pvt.create_suggestions
848 (p_api_version => 1.0
849 ,p_init_msg_list => fnd_api.g_false
850 ,p_commit => fnd_api.g_false
851 ,p_validation_level => fnd_api.g_valid_level_none
852 ,x_return_status => l_return_status
853 ,x_msg_count => l_msg_count
854 ,x_msg_data => l_msg_data
855 ,p_transaction_temp_id => p_mo_line_rec.line_id
856 ,p_reservations => l_inv_resv_tbl
857 ,p_suggest_serial => fnd_api.g_false
858 ,p_simulation_mode => wms_engine_pvt.g_no_simulation
859 ,p_simulation_id => NULL
860 ,p_plan_tasks => l_plan_tasks
861 ,p_quick_pick_flag => 'N');
862
863 IF g_debug <= gme_debug.g_log_statement THEN
864 gme_debug.put_line ( g_pkg_name
865 || '.'
866 || l_api_name
867 || 'create suggestions returns '
868 || l_return_status);
869 gme_debug.put_line ( g_pkg_name
870 || '.'
871 || l_api_name
872 || 'create suggestions mesg '
873 || l_msg_data);
874 END IF;
875
876 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
877 RAISE create_suggestions_err;
878 END IF;
879
880 OPEN cur_detailed_qty (p_mo_line_rec.line_id);
881
882 FETCH cur_detailed_qty
883 INTO l_qty_detailed, l_sec_qty_detailed;
884
885 CLOSE cur_detailed_qty;
886
887 IF g_debug <= gme_debug.g_log_statement THEN
888 gme_debug.put_line ( g_pkg_name
889 || '.'
890 || l_api_name
891 || ':Qty allocated '
892 || l_qty_detailed);
893 gme_debug.put_line ( g_pkg_name
894 || '.'
895 || l_api_name
896 || ':Sec Qty allocated '
897 || l_sec_qty_detailed);
898 END IF;
899
900 l_mo_line_rec := p_mo_line_rec;
901
902 IF (NVL (l_qty_detailed, 0) > 0) THEN
903 IF g_debug <= gme_debug.g_log_statement THEN
904 gme_debug.put_line ( g_pkg_name
905 || '.'
906 || l_api_name
907 || ':Get mmtt recs and create pick slips ');
908 END IF;
909
910 l_mo_line_rec.quantity_detailed := l_qty_detailed;
911 l_mo_line_rec.secondary_quantity_detailed := l_sec_qty_detailed;
912
913 -- Bug 8481421 Let's update the qty tree to reflect the reservations suggested by WMS.
914 IF l_inv_resv_tbl.COUNT > 0 THEN
915 --initializing the index
916 l_res_ordered_index := l_inv_resv_tbl.FIRST;
917 LOOP
918 --item_rec is required to get all UOM quantities from MTL_SYSTEM_ITEMS
919 SELECT *
920 INTO item_rec
921 FROM MTL_SYSTEM_ITEMS
922 WHERE organization_id = l_inv_resv_tbl(l_res_ordered_index).organization_id
923 AND inventory_item_id = l_inv_resv_tbl(l_res_ordered_index).inventory_item_id;
924
925 l_primary_uom:= item_rec.primary_uom_code;
926 l_secondary_uom:= item_rec.secondary_uom_code;
927 l_revision_control_code:=item_rec.revision_qty_control_code;
928 l_lot_control_code:= item_rec.lot_control_code;
929 l_serial_number_control_code:= item_rec.serial_number_control_code;
930 -- convert revision/lot control indicators into boolean
931 IF l_revision_control_code = 2 THEN
932 l_is_revision_control := TRUE;
933 ELSE
934 l_is_revision_control := FALSE;
935 END IF;
936 --
937 IF l_lot_control_code = 2 THEN
938 l_is_lot_control := TRUE;
939 ELSE
940 l_is_lot_control := FALSE;
941 END IF;
942 --
943 IF l_serial_number_control_code = 2 THEN
944 l_is_serial_control := TRUE;
945 ELSE
946 l_is_serial_control := FALSE;
947 END IF;
948
949
950 l_reservation_id := l_inv_resv_tbl(l_res_ordered_index).reservation_id;
951 l_prev_rsv_detailed_qty := nvl(l_inv_resv_tbl(l_res_ordered_index).detailed_quantity,0);
952 l_prev_rsv_detailed_qty2 := nvl(l_inv_resv_tbl(l_res_ordered_index).secondary_detailed_quantity,0);
953
954 BEGIN
955 SELECT NVL(SUM(ABS(primary_quantity)), 0)
956 , NVL(SUM(ABS(secondary_transaction_quantity)), 0)
957 INTO l_reservation_detailed_qty
958 , l_rsv_detailed_qty2
959 FROM mtl_material_transactions_temp
960 WHERE organization_id = p_mo_line_rec.organization_id
961 AND reservation_id = l_reservation_id;
962 EXCEPTION
963 WHEN NO_DATA_FOUND THEN
964 l_reservation_detailed_qty := 0;
965 l_rsv_detailed_qty2 := 0;
966 END;
967
968 --update quantity tree
969 inv_quantity_tree_pub.update_quantities(
970 p_api_version_number => 1.0
971 , p_init_msg_lst => fnd_api.g_false
972 , x_return_status => l_api_return_status
973 , x_msg_count => l_msg_count
974 , x_msg_data => l_msg_data
975 , p_organization_id => p_mo_line_rec.organization_id
976 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
977 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
978 , p_is_revision_control => l_is_revision_control
979 , p_is_lot_control => l_is_lot_control
980 , p_is_serial_control => l_is_serial_control
981 , p_demand_source_type_id => l_inv_resv_tbl(l_res_ordered_index).demand_source_type_id
982 , p_demand_source_header_id => l_inv_resv_tbl(l_res_ordered_index).demand_source_header_id
983 , p_demand_source_line_id => l_inv_resv_tbl(l_res_ordered_index).demand_source_line_id
984 , p_demand_source_name => NULL
985 , p_revision => l_inv_resv_tbl(l_res_ordered_index).revision
986 , p_lot_number => l_inv_resv_tbl(l_res_ordered_index).lot_number
987 , p_lot_expiration_date => SYSDATE
988 , p_subinventory_code => l_inv_resv_tbl(l_res_ordered_index).subinventory_code
989 , p_locator_id => l_inv_resv_tbl(l_res_ordered_index).locator_id
990 , p_primary_quantity => -(l_reservation_detailed_qty - l_prev_rsv_detailed_qty)
991 , p_secondary_quantity => -(l_rsv_detailed_qty2 - l_prev_rsv_detailed_qty2)
992 , p_lpn_id => l_inv_resv_tbl(l_res_ordered_index).lpn_id
993 , p_quantity_type => inv_quantity_tree_pub.g_qr_same_demand
994 , x_qoh => l_qty_on_hand
995 , x_rqoh => l_qty_res_on_hand
996 , x_qr => l_qty_res
997 , x_qs => l_qty_sug
998 , x_att => l_qty_att
999 , x_atr => l_qty_available_to_reserve
1000 , p_grade_code => p_mo_line_rec.grade_code
1001 , x_sqoh => l_sec_qty_on_hand
1002 , x_srqoh => l_sec_qty_res_on_hand
1003 , x_sqr => l_sec_qty_res
1004 , x_sqs => l_sec_qty_sug
1005 , x_satt => l_sec_qty_att
1006 , x_satr => l_sec_qty_available_to_reserve
1007 );
1008 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1009 RAISE fnd_api.g_exc_unexpected_error;
1010 END IF;
1011
1012 --handle conversion to reservation UOM
1013 IF l_inv_resv_tbl(l_res_ordered_index).reservation_uom_code IS NULL THEN
1014 --when missing rsv UOM, assume primary UOM
1015 l_rsv_detailed_qty_conv := l_reservation_detailed_qty;
1016 ELSIF l_inv_resv_tbl(l_res_ordered_index).reservation_uom_code = l_primary_uom THEN
1017 --reservation UOM = primary UOM
1018 l_rsv_detailed_qty_conv := l_reservation_detailed_qty;
1019 ELSE
1020 l_rsv_detailed_qty_conv := inv_convert.inv_um_convert(
1021 item_id => p_mo_line_rec.inventory_item_id
1022 , PRECISION => NULL
1023 , from_quantity => l_reservation_detailed_qty
1024 , from_unit => l_primary_uom
1025 , to_unit => l_inv_resv_tbl(l_res_ordered_index).reservation_uom_code
1026 , from_name => NULL
1027 , to_name => NULL
1028 );
1029
1030 IF (l_rsv_detailed_qty_conv = -99999) THEN
1031 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
1032 fnd_message.set_token('UOM', l_primary_uom);
1033 fnd_message.set_token('ROUTINE', 'Pick Release process');
1034 fnd_msg_pub.ADD;
1035 RAISE fnd_api.g_exc_unexpected_error;
1036 END IF;
1037 END IF;
1038
1039 UPDATE mtl_reservations
1040 SET detailed_quantity = l_reservation_detailed_qty
1041 ,secondary_detailed_quantity = l_rsv_detailed_qty2
1042 WHERE reservation_id = l_reservation_id;
1043
1044 EXIT WHEN l_res_ordered_index = l_inv_resv_tbl.LAST;
1045 l_res_ordered_index:= l_inv_resv_tbl.NEXT(l_res_ordered_index);
1046 END LOOP;
1047 END IF;
1048 -- Bug 8481421 end of update quantity loop
1049
1050 FOR get_mmtt IN cur_mmtt (p_mo_line_rec.line_id) LOOP
1051 IF g_debug <= gme_debug.g_log_statement THEN
1052 gme_debug.put_line(g_pkg_name || '.' || l_api_name
1053 || ' Calling ASSIGNTT with temp id ' || get_mmtt.transaction_temp_id);
1054 END IF;
1055
1056
1057 -- Bug 6778259 - Assign user task properly.
1058 WMS_RULE_PVT.assigntt
1059 (p_api_version => 1.0,
1060 p_task_id => get_mmtt.transaction_temp_id,
1061 x_return_status => l_return_status,
1062 x_msg_count => l_msg_count,
1063 x_msg_data => l_msg_data
1064 );
1065
1066 IF g_debug <= gme_debug.g_log_statement THEN
1067 gme_debug.put_line(g_pkg_name || '.' || l_api_name
1068 || ':Return from ASSIGNTT IS ' || l_return_status);
1069
1070 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1071 gme_debug.put_line ( g_pkg_name
1072 || '.'
1073 || l_api_name
1074 || 'temp_id is '
1075 || get_mmtt.transaction_temp_id);
1076 gme_debug.put_line ( g_pkg_name
1077 || '.'
1078 || l_api_name
1079 || ':Message is '
1080 || l_msg_data);
1081 END IF;
1082 END IF;
1083
1084 -- Bug 6778259 - Use existing exception to return error.
1085 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1086 RAISE get_pick_slip_err;
1087 END IF;
1088
1089 IF get_mmtt.pick_slip_number IS NULL THEN
1090 IF g_debug <= gme_debug.g_log_statement THEN
1091 gme_debug.put_line
1092 ( g_pkg_name
1093 || '.'
1094 || l_api_name
1095 || ':Calling inv_pr_pick_slip_number.get_pick_slip_number');
1096 END IF;
1097
1098 inv_pr_pick_slip_number.get_pick_slip_number
1099 (p_pick_grouping_rule_id => p_grouping_rule_id
1100 ,p_org_id => p_mo_line_rec.organization_id
1101 ,p_wip_entity_id => p_mo_line_rec.txn_source_id
1102 ,p_rep_schedule_id => NULL
1103 ,p_operation_seq_num => p_mo_line_rec.txn_source_line_id
1104 ,p_dept_id => NULL
1105 ,p_push_or_pull => NULL
1106 ,p_supply_subinventory => get_mmtt.transfer_subinventory
1107 ,p_supply_locator_id => get_mmtt.transfer_to_location
1108 ,p_project_id => NULL
1109 ,p_task_id => NULL
1110 ,p_src_subinventory => get_mmtt.subinventory_code
1111 ,p_src_locator_id => get_mmtt.locator_id
1112 ,p_inventory_item_id => p_mo_line_rec.inventory_item_id
1113 ,p_revision => get_mmtt.revision
1114 ,p_lot_number => NULL
1115 ,x_pick_slip_number => l_pick_slip_number
1116 ,x_api_status => l_return_status
1117 ,x_error_message => l_msg_data);
1118
1119 IF g_debug <= gme_debug.g_log_statement THEN
1120 gme_debug.put_line
1121 ( g_pkg_name
1122 || '.'
1123 || l_api_name
1124 || ':Return from inv_pr_pick_slip_number.get_pick_slip_number '
1125 || l_return_status);
1126 gme_debug.put_line ( g_pkg_name
1127 || '.'
1128 || l_api_name
1129 || ':l_pick_slip_number is '
1130 || l_pick_slip_number);
1131 gme_debug.put_line ( g_pkg_name
1132 || '.'
1133 || l_api_name
1134 || ':Message is '
1135 || l_msg_data);
1136 END IF;
1137
1138 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1139 RAISE get_pick_slip_err;
1140 END IF;
1141
1142 UPDATE mtl_material_transactions_temp
1143 SET pick_slip_number = l_pick_slip_number,
1144 wip_entity_type = gme_common_pvt.g_wip_entity_type_batch
1145 WHERE transaction_temp_id = get_mmtt.transaction_temp_id;
1146 ELSE
1147 UPDATE mtl_material_transactions_temp
1148 SET wip_entity_type = gme_common_pvt.g_wip_entity_type_batch
1149 WHERE transaction_temp_id = get_mmtt.transaction_temp_id;
1150 END IF;
1151 END LOOP;
1152 --End of for loop for get_mmtt IN cur_mmtt (p_mo_line_rec.line_id)
1153
1154 IF (NVL (l_qty_detailed, 0) < p_mo_line_rec.quantity) THEN
1155 l_backordered_qty :=
1156 p_mo_line_rec.quantity - NVL (l_qty_detailed, 0);
1157 l_mo_line_rec.quantity := NVL (l_qty_detailed, 0);
1158 g_partially_allocated := TRUE;
1159 ELSE
1160 g_fully_allocated := TRUE;
1161 END IF;
1162 ELSE
1163 IF g_debug <= gme_debug.g_log_statement THEN
1164 gme_debug.put_line ( g_pkg_name
1165 || '.'
1166 || l_api_name
1167 || ':Close mo lines since no allocations');
1168 END IF;
1169
1170 l_backordered_qty := p_mo_line_rec.quantity;
1171 l_mo_line_rec.line_status := 5;
1172 g_not_allocated := TRUE;
1173 END IF;
1174
1175 IF g_debug <= gme_debug.g_log_statement THEN
1176 gme_debug.put_line ( g_pkg_name
1177 || '.'
1178 || l_api_name
1179 || 'Updating MO line');
1180 END IF;
1181
1182 inv_trolin_util.update_row (l_mo_line_rec);
1183
1184 IF g_debug <= gme_debug.g_log_statement THEN
1185 gme_debug.put_line ( g_pkg_name
1186 || '.'
1187 || l_api_name
1188 || 'After Updating MO line');
1189 END IF;
1190
1191 IF g_debug <= gme_debug.g_log_statement THEN
1192 gme_debug.put_line ( g_pkg_name
1193 || '.'
1194 || l_api_name
1195 || 'l_backordered_qty = '
1196 || l_backordered_qty);
1197 END IF;
1198
1199 IF (l_backordered_qty > 0) THEN
1200 UPDATE gme_material_details
1201 SET backordered_qty = l_backordered_qty
1202 WHERE material_detail_id = p_mo_line_rec.txn_source_line_id;
1203 END IF;
1204
1205 IF g_debug <= gme_debug.g_log_procedure THEN
1206 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1207 END IF;
1208 EXCEPTION
1209 WHEN create_suggestions_err OR get_pick_slip_err THEN
1210 x_return_status := l_return_status;
1211 WHEN OTHERS THEN
1212 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1213
1214 IF g_debug <= gme_debug.g_log_unexpected THEN
1215 gme_debug.put_line ( 'When others exception in '
1216 || g_pkg_name
1217 || '.'
1218 || l_api_name
1219 || ' Error is '
1220 || SQLERRM);
1221 END IF;
1222
1223 x_return_status := fnd_api.g_ret_sts_unexp_error;
1224 END process_line;
1225 END gme_picking_pvt;