[Home] [Help]
PACKAGE BODY: APPS.GME_PICKING_PVT
Source
1 PACKAGE BODY gme_picking_pvt AS
2 /* $Header: GMEVPCKB.pls 120.7.12010000.2 2008/11/06 03:30:08 srpuri 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 /* Oracle Process Manufacturing Process Execution APIs */
11 /* Contents: GME Picking Procedures. */
12 /* File Name: GMEVPCKB.pls */
13 /* HISTORY:
14 /* Susruth D. Bug#5311713 Commented the return status and put the message in the log file */
15 /* GME_NO_MATERIALS_SELECTED */
16
17 -- HALUTHRA 22-SEP-2008 Bug 7383625
18 -- Added IF condition to populate detailed_quantity
19 -- in PROCEDURE process_line
20
21 /*************************************************************************************************/
22
23 PROCEDURE conc_picking (
24 err_buf OUT NOCOPY VARCHAR2
25 ,ret_code OUT NOCOPY VARCHAR2
26 ,p_organization_id IN NUMBER
27 ,p_all_batches IN VARCHAR2
28 , -- 1 = All, 2 = Backordered
29 p_include_pending IN VARCHAR2
30 ,p_include_wip IN VARCHAR2
31 ,p_from_batch IN VARCHAR2
32 ,p_to_batch IN VARCHAR2
33 ,p_oprn_no IN VARCHAR2
34 ,p_oprn_vers IN NUMBER
35 ,p_product_no IN VARCHAR2
36 ,p_ingredient_no IN VARCHAR2
37 ,p_days_forward IN NUMBER
38 ,p_from_req_date IN VARCHAR2
39 ,p_to_req_date IN VARCHAR2
40 ,p_pick_grouping_rule IN VARCHAR2
41 ,p_print_pick_slip IN VARCHAR2 DEFAULT 'N'
42 ,p_plan_tasks IN VARCHAR2 DEFAULT 'N'
43 ,p_sales_order IN VARCHAR2)
44 IS
45 l_api_name CONSTANT VARCHAR2 (30) := 'conc_picking';
46 l_return_status VARCHAR2 (1);
47 l_where VARCHAR2 (4000);
48 l_sql_stmt VARCHAR2 (4000);
49 l_msg_data VARCHAR2 (2000);
50 l_conc_request_id NUMBER;
51 l_msg_count NUMBER;
52
53 TYPE l_picking_tab IS TABLE OF gme_ingred_pick_vw%ROWTYPE
54 INDEX BY BINARY_INTEGER;
55
56 l_picking_tbl l_picking_tab;
57 l_mat_req_tbl gme_picking_pvt.mtl_req_tab;
58 build_where_err EXCEPTION;
59 pick_material_err EXCEPTION;
60
61 /* Bug 5212556 Added the following ref cursor etc */
62 TYPE pick_ref IS REF CURSOR;
63 l_pick_cursor pick_ref;
64 BEGIN
65 IF g_debug IS NOT NULL THEN
66 gme_debug.log_initialize ('ConcPicking');
67 END IF;
68 IF g_debug <= gme_debug.g_log_procedure THEN
69 gme_debug.put_line ('Entering api ' || g_pkg_name || '.' || l_api_name);
70 END IF;
71 l_where := 'NVL(open_qty,0) > 0';
72 l_where := l_where || ' AND organization_id = :organization_id';
73 IF (TO_NUMBER(p_all_batches) = 2) THEN
74 l_where := l_where || ' AND NVL(backordered_qty,0) > 0';
75 END IF;
76 IF (NVL (p_include_pending, 'N') = 'Y') AND (NVL (p_include_wip, 'N') = 'Y') THEN
77 l_where := l_where || ' AND batch_status IN (1,2)';
78 ELSIF (NVL (p_include_pending, 'N') = 'Y') THEN
79 l_where := l_where || ' AND batch_status = 1';
80 ELSIF (NVL (p_include_wip, 'N') = 'Y') THEN
81 l_where := l_where || ' AND batch_status = 2';
82 ELSE
83 l_where := l_where || ' AND batch_status NOT IN (1,2)';
84 END IF;
85 /* Bug 5370563 oprn_no and oprn_vers can be null so added NVL */
86 l_where := l_where || ' AND LPAD(batch_no, 32, 0) >= LPAD(NVL(:from_batch, batch_no), 32, 0)'
87 || ' AND LPAD(batch_no, 32, 0) <= LPAD(NVL(:to_batch, batch_no), 32, 0)'
88 || ' AND NVL(oprn_no, '' '') LIKE NVL(:oprn_no, NVL(oprn_no, '' ''))'
89 || ' AND NVL(oprn_vers, -1) = NVL(:oprn_vers, NVL(oprn_vers, -1))'
90 || ' AND (:product_no IS NULL OR batch_id IN (SELECT DISTINCT batch_id FROM gme_material_details'
91 || ' WHERE organization_id = :organization_id'
92 || ' AND line_type = 1 AND inventory_item_id IN'
93 || ' (SELECT inventory_item_id FROM mtl_system_items_kfv WHERE organization_id = :organization_id'
94 || ' AND concatenated_segments LIKE :product_no)))'
95 || ' AND (:ingredient_no IS NULL OR batch_id IN (SELECT DISTINCT batch_id FROM gme_material_details'
96 || ' WHERE organization_id = :organization_id'
97 || ' AND line_type = -1 AND inventory_item_id IN'
98 || ' (SELECT inventory_item_id FROM mtl_system_items_kfv WHERE organization_id = :organization_id'
99 || ' AND concatenated_segments LIKE :ingredient_no)))'
100 || ' AND material_requirement_date <= SYSDATE + NVL(:days_forward, 100000)'
101 || ' AND material_requirement_date >= NVL(:from_req_date, material_requirement_date)'
102 || ' AND material_requirement_date <= NVL(:to_req_date, material_requirement_date)';
103 l_sql_stmt := 'SELECT * FROM gme_ingred_pick_vw WHERE ' || l_where;
104 IF g_debug <= gme_debug.g_log_statement THEN
105 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| l_sql_stmt);
106 END IF;
107 OPEN l_pick_cursor FOR l_sql_stmt
108 USING p_organization_id, p_from_batch, p_to_batch, p_oprn_no, p_oprn_vers,
109 p_product_no, p_organization_id, p_organization_id, p_product_no,
110 p_ingredient_no, p_organization_id, p_organization_id, p_ingredient_no,
111 p_days_forward, fnd_date.canonical_to_date(p_from_req_date),
112 fnd_date.canonical_to_date(p_to_req_date);
113 FETCH l_pick_cursor BULK COLLECT INTO l_picking_tbl;
114 CLOSE l_pick_cursor;
115 IF g_debug <= gme_debug.g_log_statement THEN
116 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':No. of records found = '|| l_picking_tbl.COUNT);
117 END IF;
118
119 FOR i IN 1 .. l_picking_tbl.COUNT LOOP
120 l_mat_req_tbl (i).organization_id :=
121 l_picking_tbl (i).organization_id;
122 l_mat_req_tbl (i).batch_id := l_picking_tbl (i).batch_id;
123 l_mat_req_tbl (i).material_detail_id :=
124 l_picking_tbl (i).material_detail_id;
125 l_mat_req_tbl (i).inventory_item_id :=
126 l_picking_tbl (i).inventory_item_id;
127 l_mat_req_tbl (i).revision := l_picking_tbl (i).revision;
128 l_mat_req_tbl (i).subinventory := l_picking_tbl (i).subinventory;
129 l_mat_req_tbl (i).locator_id := l_picking_tbl (i).locator_id;
130 l_mat_req_tbl (i).open_qty := l_picking_tbl (i).open_qty;
131 l_mat_req_tbl (i).dtl_um := l_picking_tbl (i).dtl_um;
132 l_mat_req_tbl (i).mtl_req_date :=
133 l_picking_tbl (i).material_requirement_date;
134 END LOOP;
135
136 gme_picking_pvt.pick_material
137 (p_mtl_req_tbl => l_mat_req_tbl
138 ,p_task_group_id => TO_NUMBER
139 (p_pick_grouping_rule)
140 ,p_print_pick_slip => p_print_pick_slip
141 ,p_plan_tasks => p_plan_tasks
142 ,x_return_status => l_return_status
143 ,x_conc_request_id => l_conc_request_id);
144
145 IF g_debug <= gme_debug.g_log_statement THEN
146 gme_debug.put_line ( g_pkg_name
147 || '.'
148 || l_api_name
149 || ':Return from pick_material is '
150 || l_return_status);
151 END IF;
152
153 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
154 RAISE pick_material_err;
155 END IF;
156
157 IF g_debug <= gme_debug.g_log_procedure THEN
158 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
159 END IF;
160 EXCEPTION
161 WHEN build_where_err OR pick_material_err THEN
162 gme_common_pvt.count_and_get (x_count => l_msg_count
163 ,x_data => l_msg_data);
164 raise_application_error (-20000, l_msg_data, TRUE);
165 WHEN OTHERS THEN
166 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
167
168 IF g_debug <= gme_debug.g_log_unexpected THEN
169 gme_debug.put_line ( 'When others exception in '
170 || g_pkg_name
171 || '.'
172 || l_api_name
173 || ' Error is '
174 || SQLERRM);
175 END IF;
176
177 gme_common_pvt.count_and_get (x_count => l_msg_count
178 ,x_data => l_msg_data);
179 raise_application_error (-20001, l_msg_data, TRUE);
180 END conc_picking;
181
182 /* Bug 5212556 Added inventory_item_id */
183 FUNCTION get_open_qty (
184 p_organization_id IN NUMBER
185 ,p_batch_id IN NUMBER
186 ,p_material_detail_id IN NUMBER
187 ,p_inventory_item_id IN NUMBER
188 ,p_subinventory IN VARCHAR2
189 ,p_plan_qty IN NUMBER
190 ,p_wip_plan_qty IN NUMBER
191 ,p_actual_qty IN NUMBER
192 ,p_backordered_qty IN NUMBER
193 ,p_dtl_um IN VARCHAR2)
194 RETURN NUMBER
195 IS
196 l_api_name CONSTANT VARCHAR2 (30) := 'get_open_qty';
197 l_open_qty NUMBER := 0;
198 l_return_status VARCHAR2 (1);
199 l_mtl_dtl_rec gme_material_details%ROWTYPE;
200 BEGIN
201 IF g_debug <= gme_debug.g_log_procedure THEN
202 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
203 || l_api_name);
204 END IF;
205
206 l_mtl_dtl_rec.organization_id := p_organization_id;
207 l_mtl_dtl_rec.batch_id := p_batch_id;
208 l_mtl_dtl_rec.material_detail_id := p_material_detail_id;
209 l_mtl_dtl_rec.inventory_item_id := p_inventory_item_id;
210 l_mtl_dtl_rec.subinventory := p_subinventory;
211 l_mtl_dtl_rec.plan_qty := p_plan_qty;
212 l_mtl_dtl_rec.wip_plan_qty := p_wip_plan_qty;
213 l_mtl_dtl_rec.actual_qty := p_actual_qty;
214 l_mtl_dtl_rec.backordered_qty := p_backordered_qty;
215 l_mtl_dtl_rec.dtl_um := p_dtl_um;
216 gme_common_pvt.get_open_qty (p_mtl_dtl_rec => l_mtl_dtl_rec
217 ,p_called_by => 'P'
218 ,x_open_qty => l_open_qty
219 ,x_return_status => l_return_status);
220
221 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
222 RETURN 0;
223 ELSE
224 RETURN l_open_qty;
225 END IF;
226 EXCEPTION
227 WHEN OTHERS THEN
228 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
229
230 IF g_debug <= gme_debug.g_log_unexpected THEN
231 gme_debug.put_line ( 'When others exception in '
232 || g_pkg_name
233 || '.'
234 || l_api_name
235 || ' Error is '
236 || SQLERRM);
237 END IF;
238
239 RETURN 0;
240 END get_open_qty;
241
242 PROCEDURE pick_material (
243 p_mtl_req_tbl IN gme_picking_pvt.mtl_req_tab
244 ,p_task_group_id IN NUMBER
245 ,p_print_pick_slip IN VARCHAR2 DEFAULT 'N'
246 ,p_plan_tasks IN VARCHAR2 DEFAULT 'N'
247 ,x_return_status OUT NOCOPY VARCHAR2
248 ,x_conc_request_id OUT NOCOPY NUMBER)
249 IS
250 l_api_name CONSTANT VARCHAR2 (30) := 'pick_material';
251 l_count NUMBER := 0;
252 l_msg_count NUMBER;
253 l_move_order_header_id NUMBER;
254 l_conc_req_id NUMBER;
255 l_return_status VARCHAR2 (1);
256 l_msg_data VARCHAR2 (2000);
257 l_plan_tasks BOOLEAN;
258 l_mtl_dtl_tbl gme_common_pvt.material_details_tab;
259 l_out_mtl_dtl_tbl gme_common_pvt.material_details_tab;
260 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
261 l_trolin_rec inv_move_order_pub.trolin_rec_type;
262
263 CURSOR cur_pending_move_orders (
264 v_org_id NUMBER
265 ,v_batch_id NUMBER
266 ,v_material_detail_id NUMBER)
267 IS
268 SELECT l.line_id, l.header_id
269 FROM mtl_txn_request_lines l, mtl_txn_request_headers h
270 WHERE l.organization_id = v_org_id
271 AND transaction_source_type_id =
272 gme_common_pvt.g_txn_source_type
273 AND l.txn_source_id = v_batch_id
274 AND l.txn_source_line_id = v_material_detail_id
275 AND l.line_status NOT IN (5, 6)
276 AND h.header_id = l.header_id
277 AND h.move_order_type NOT IN
278 (gme_common_pvt.g_invis_move_order_type
279 ,inv_globals.g_move_order_put_away)
280 ORDER BY l.header_id, l.line_id;
281
282 TYPE pend_lines_tab IS TABLE OF cur_pending_move_orders%ROWTYPE
283 INDEX BY BINARY_INTEGER;
284
285 l_pend_lines_tbl pend_lines_tab;
286 no_materials_picked EXCEPTION;
287 create_move_order_err EXCEPTION;
288 setup_failure EXCEPTION;
289 process_line_err EXCEPTION;
290 print_pickslip_err EXCEPTION;
291 BEGIN
292 IF g_debug IS NOT NULL THEN
293 gme_debug.log_initialize ('PickMaterial');
294 END IF;
295
296 IF g_debug <= gme_debug.g_log_procedure THEN
297 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
298 || l_api_name);
299 END IF;
300
301 x_return_status := fnd_api.g_ret_sts_success;
302 l_count := p_mtl_req_tbl.COUNT;
303
304 IF g_debug <= gme_debug.g_log_statement THEN
305 gme_debug.put_line ( g_pkg_name
306 || '.'
307 || l_api_name
308 || ':Material Requirement count is '
309 || l_count);
310 END IF;
311
312 IF (l_count = 0) THEN
313 RAISE no_materials_picked;
314 END IF;
315
316 IF NOT gme_common_pvt.g_setup_done THEN
317 gme_common_pvt.g_setup_done :=
318 gme_common_pvt.setup
319 (p_org_id => p_mtl_req_tbl (1).organization_id);
320
321 IF NOT gme_common_pvt.g_setup_done THEN
322 RAISE setup_failure;
323 END IF;
324 END IF;
325
326 IF g_debug <= gme_debug.g_log_statement THEN
327 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':After setup');
331 (p_organization_id => p_mtl_req_tbl (1).organization_id
328 END IF;
329
330 gme_move_orders_pvt.create_move_order_hdr
332 ,p_move_order_type => gme_common_pvt.g_move_order_type
333 ,x_move_order_header_id => l_move_order_header_id
334 ,x_return_status => l_return_status);
335
336 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
337 RAISE create_move_order_err;
338 END IF;
339
340 IF g_debug <= gme_debug.g_log_statement THEN
341 gme_debug.put_line ( g_pkg_name
342 || '.'
343 || l_api_name
344 || ':Move order header created is '
345 || l_move_order_header_id);
346 END IF;
347
348 FOR i IN 1 .. l_count LOOP
349 l_mtl_dtl_tbl (i).inventory_item_id :=
350 p_mtl_req_tbl (i).inventory_item_id;
351 l_mtl_dtl_tbl (i).organization_id :=
352 p_mtl_req_tbl (i).organization_id;
353 l_mtl_dtl_tbl (i).plan_qty := p_mtl_req_tbl (i).open_qty;
354 l_mtl_dtl_tbl (i).dtl_um := p_mtl_req_tbl (i).dtl_um;
355 l_mtl_dtl_tbl (i).revision := p_mtl_req_tbl (i).revision;
356 l_mtl_dtl_tbl (i).batch_id := p_mtl_req_tbl (i).batch_id;
357 l_mtl_dtl_tbl (i).material_detail_id :=
358 p_mtl_req_tbl (i).material_detail_id;
359 l_mtl_dtl_tbl (i).material_requirement_date :=
360 p_mtl_req_tbl (i).mtl_req_date;
361 l_mtl_dtl_tbl (i).subinventory := p_mtl_req_tbl (i).subinventory;
362 l_mtl_dtl_tbl (i).locator_id := p_mtl_req_tbl (i).locator_id;
363 l_mtl_dtl_tbl (i).line_type := gme_common_pvt.g_line_type_ing;
364 END LOOP;
365
366 /* Try to allocate any other move order lines that exist and are not allocated
367 These could be move orders created during batch create */
368 FOR i IN 1 .. l_count LOOP
369 OPEN cur_pending_move_orders (p_mtl_req_tbl (i).organization_id
370 ,p_mtl_req_tbl (i).batch_id
371 ,p_mtl_req_tbl (i).material_detail_id);
372
373 FETCH cur_pending_move_orders
374 BULK COLLECT INTO l_pend_lines_tbl;
375
376 CLOSE cur_pending_move_orders;
377 END LOOP;
378
379 IF g_debug <= gme_debug.g_log_statement THEN
380 gme_debug.put_line
381 ( g_pkg_name
382 || '.'
383 || l_api_name
384 || ':No. of lines to create move_order_lines is '
385 || l_mtl_dtl_tbl.COUNT);
386 END IF;
387
388 gme_move_orders_pvt.create_move_order_lines
389 (p_move_order_header_id => l_move_order_header_id
390 ,p_move_order_type => gme_common_pvt.g_move_order_type
391 ,p_material_details_tbl => l_mtl_dtl_tbl
392 ,x_material_details_tbl => l_out_mtl_dtl_tbl
393 ,x_trolin_tbl => l_trolin_tbl
394 ,x_return_status => l_return_status);
395
396 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
397 RAISE create_move_order_err;
398 END IF;
399
400 l_count := l_trolin_tbl.COUNT;
401
402 IF g_debug <= gme_debug.g_log_statement THEN
403 gme_debug.put_line ( g_pkg_name
404 || '.'
405 || l_api_name
406 || ':No. Move order lines created is '
407 || l_count);
408 gme_debug.put_line ( g_pkg_name
409 || '.'
410 || l_api_name
411 || ':No. Pending Move order lines is '
412 || l_pend_lines_tbl.COUNT);
413 END IF;
414
415 FOR i IN 1 .. l_pend_lines_tbl.COUNT LOOP
416 l_count := l_count + 1;
417 l_trolin_tbl (l_count) :=
418 inv_trolin_util.query_row (l_pend_lines_tbl (i).line_id);
419 END LOOP;
420
421 FOR i IN 1 .. l_trolin_tbl.COUNT LOOP
422 gme_picking_pvt.process_line (p_mo_line_rec => l_trolin_tbl
423 (i)
424 ,p_grouping_rule_id => p_task_group_id
425 ,p_plan_tasks => p_plan_tasks
426 ,x_return_status => l_return_status);
427
428 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
429 RAISE process_line_err;
430 END IF;
431 END LOOP;
432
433 IF ( p_print_pick_slip = 'Y'
434 AND (g_fully_allocated OR g_partially_allocated) ) THEN
435 IF g_debug <= gme_debug.g_log_statement THEN
436 gme_debug.put_line ( g_pkg_name
437 || '.'
438 || l_api_name
439 || ':Calling print picklsip');
440 END IF;
441
445 l_plan_tasks := FALSE;
442 IF (p_plan_tasks = 'Y') THEN
443 l_plan_tasks := TRUE;
444 ELSE
446 END IF;
447
448 l_conc_req_id :=
449 inv_pr_pick_slip_number.print_pick_slip
450 (x_return_status => l_return_status
451 ,x_msg_data => l_msg_data
452 ,x_msg_count => l_msg_count
453 ,p_organization_id => p_mtl_req_tbl (1).organization_id
454 ,p_mo_request_number => l_move_order_header_id
455 ,p_plan_tasks => l_plan_tasks);
456
457 IF g_debug <= gme_debug.g_log_statement THEN
458 gme_debug.put_line ( g_pkg_name
459 || '.'
460 || l_api_name
461 || ':Print picklsip returns '
462 || l_return_status);
463 gme_debug.put_line ( g_pkg_name
464 || '.'
465 || l_api_name
466 || ':Print picklsip message '
467 || l_msg_data);
468 END IF;
469
470 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
471 RAISE print_pickslip_err;
472 END IF;
473 END IF;
474
475 IF (l_conc_req_id IS NOT NULL) THEN
476 IF (NOT (g_partially_allocated) AND NOT (g_not_allocated) ) THEN
477 gme_common_pvt.log_message
478 (p_message_code => 'GME_PICKED_ALL_PRINTED'
479 ,p_token1_name => 'MO_NUMBER'
480 ,p_token1_value => l_move_order_header_id
481 ,p_token2_name => 'CONC_REQUEST_ID'
482 ,p_token2_value => l_conc_req_id);
483 IF g_debug <= gme_debug.g_log_unexpected THEN
484 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
485 END IF;
486 ELSIF (g_partially_allocated OR g_fully_allocated) THEN
487 gme_common_pvt.log_message
488 (p_message_code => 'GME_PICKED_PARTIAL_PRINTED'
489 ,p_token1_name => 'MO_NUMBER'
490 ,p_token1_value => l_move_order_header_id
491 ,p_token2_name => 'CONC_REQUEST_ID'
492 ,p_token2_value => l_conc_req_id);
493 IF g_debug <= gme_debug.g_log_unexpected THEN
494 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
495 END IF;
496 END IF;
497 ELSE
498 IF (NOT (g_partially_allocated) AND NOT (g_not_allocated) ) THEN
499 gme_common_pvt.log_message
500 (p_message_code => 'GME_PICKED_ALL'
501 ,p_token1_name => 'MO_NUMBER'
502 ,p_token1_value => l_move_order_header_id);
503 IF g_debug <= gme_debug.g_log_unexpected THEN
504 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
505 END IF;
506 ELSIF (g_partially_allocated OR g_fully_allocated) THEN
507 gme_common_pvt.log_message
508 (p_message_code => 'GME_PICKED_PARTIAL'
509 ,p_token1_name => 'MO_NUMBER'
510 ,p_token1_value => l_move_order_header_id);
511 IF g_debug <= gme_debug.g_log_unexpected THEN
512 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
513 END IF;
514 ELSIF (NOT (g_partially_allocated) AND NOT (g_fully_allocated) ) THEN
515 gme_common_pvt.log_message
516 (p_message_code => 'GME_PICKED_NO_ALLOC'
517 ,p_token1_name => 'MO_NUMBER'
518 ,p_token1_value => l_move_order_header_id);
519 IF g_debug <= gme_debug.g_log_unexpected THEN
520 gme_debug.put_line(fnd_msg_pub.get(FND_MSG_PUB.G_LAST, 'F'));
521 END IF;
522 END IF;
523 END IF;
524
525 IF g_debug <= gme_debug.g_log_procedure THEN
526 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
527 END IF;
528 EXCEPTION
529 WHEN no_materials_picked THEN
530 --Bug#5311713
531 FND_MESSAGE.SET_NAME('GME','GME_NO_MATERIALS_SELECTED');
532 FND_FILE.PUT_LINE(FND_FILE.log,FND_MESSAGE.GET);
533 --gme_common_pvt.log_message ('GME_NO_MATERIALS_SELECTED');
534 --x_return_status := fnd_api.g_ret_sts_error;
535 WHEN setup_failure THEN
536 x_return_status := fnd_api.g_ret_sts_error;
537 WHEN create_move_order_err OR process_line_err OR print_pickslip_err THEN
538 x_return_status := l_return_status;
539 WHEN OTHERS THEN
540 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
541
542 IF g_debug <= gme_debug.g_log_unexpected THEN
543 gme_debug.put_line ( 'When others exception in '
544 || g_pkg_name
545 || '.'
546 || l_api_name
550
547 || ' Error is '
548 || SQLERRM);
549 END IF;
551 x_return_status := fnd_api.g_ret_sts_unexp_error;
552 END pick_material;
553
554 PROCEDURE process_line (
555 p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
556 ,p_grouping_rule_id IN NUMBER
557 ,p_plan_tasks IN VARCHAR2 DEFAULT 'N'
558 ,x_return_status OUT NOCOPY VARCHAR2)
559 IS
560 l_api_name CONSTANT VARCHAR2 (30) := 'process_line';
561
562 CURSOR cur_detailed_qty (v_move_order_line_id NUMBER)
563 IS
564 SELECT SUM (transaction_quantity) qty_detailed
565 ,SUM (secondary_transaction_quantity) sec_qty_detailed
566 FROM mtl_material_transactions_temp
567 WHERE move_order_line_id = v_move_order_line_id;
568
569 CURSOR cur_mmtt (v_move_order_line_id NUMBER)
570 IS
571 SELECT *
572 FROM mtl_material_transactions_temp
573 WHERE move_order_line_id = v_move_order_line_id;
574
575 l_count NUMBER := 0;
576 l_msg_count NUMBER;
577 l_qty_detailed NUMBER;
578 l_sec_qty_detailed NUMBER;
579 l_backordered_qty NUMBER;
580 l_pick_slip_number NUMBER;
581 l_msg_data VARCHAR2 (2000);
582 l_move_order_header_id NUMBER;
583 l_return_status VARCHAR2 (1);
584 l_plan_tasks BOOLEAN;
585 l_mo_line_rec inv_move_order_pub.trolin_rec_type;
586 l_resv_tbl gme_common_pvt.reservations_tab;
587 l_inv_resv_tbl inv_reservation_global.mtl_reservation_tbl_type;
588 create_suggestions_err EXCEPTION;
589 get_pick_slip_err EXCEPTION;
590 BEGIN
591 IF g_debug <= gme_debug.g_log_procedure THEN
592 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
593 || l_api_name);
594 END IF;
595
596 x_return_status := fnd_api.g_ret_sts_success;
597 gme_reservations_pvt.get_material_reservations
598 (p_organization_id => p_mo_line_rec.organization_id
599 ,p_batch_id => p_mo_line_rec.txn_source_id
600 ,p_material_detail_id => p_mo_line_rec.txn_source_line_id
601 ,x_return_status => l_return_status
602 ,x_reservations_tbl => l_resv_tbl);
603
604 IF g_debug <= gme_debug.g_log_statement THEN
605 gme_debug.put_line ( g_pkg_name
606 || '.'
607 || l_api_name
608 || ':No. of reservations is '
609 || l_resv_tbl.COUNT);
610 END IF;
611
612 FOR i IN 1 .. l_resv_tbl.COUNT LOOP
613 IF (l_resv_tbl (i).subinventory_code <>
614 p_mo_line_rec.to_subinventory_code) THEN
615 l_count := l_count + 1;
616 l_inv_resv_tbl (l_count).reservation_id :=
617 l_resv_tbl (i).reservation_id;
618 l_inv_resv_tbl (l_count).requirement_date :=
619 l_resv_tbl (i).requirement_date;
620 l_inv_resv_tbl (l_count).organization_id :=
621 l_resv_tbl (i).organization_id;
622 l_inv_resv_tbl (l_count).inventory_item_id :=
623 l_resv_tbl (i).inventory_item_id;
624 l_inv_resv_tbl (l_count).demand_source_type_id :=
625 l_resv_tbl (i).demand_source_type_id;
626 l_inv_resv_tbl (l_count).demand_source_header_id :=
627 l_resv_tbl (i).demand_source_header_id;
628 l_inv_resv_tbl (l_count).demand_source_line_id :=
629 l_resv_tbl (i).demand_source_line_id;
630 l_inv_resv_tbl (l_count).primary_uom_code :=
631 l_resv_tbl (i).primary_uom_code;
632 l_inv_resv_tbl (l_count).secondary_uom_code :=
633 l_resv_tbl (i).secondary_uom_code;
634 l_inv_resv_tbl (l_count).reservation_uom_code :=
635 l_resv_tbl (i).reservation_uom_code;
636 l_inv_resv_tbl (l_count).reservation_quantity :=
637 l_resv_tbl (i).reservation_quantity;
638 l_inv_resv_tbl (l_count).primary_reservation_quantity :=
639 l_resv_tbl (i).primary_reservation_quantity;
640 l_inv_resv_tbl (l_count).secondary_reservation_quantity :=
641 l_resv_tbl (i).secondary_reservation_quantity;
642 l_inv_resv_tbl (l_count).detailed_quantity :=
643 l_resv_tbl (i).detailed_quantity;
644 l_inv_resv_tbl (l_count).secondary_detailed_quantity :=
645 l_resv_tbl (i).secondary_detailed_quantity;
646 l_inv_resv_tbl (l_count).supply_source_type_id :=
647 l_resv_tbl (i).supply_source_type_id;
648 l_inv_resv_tbl (l_count).supply_source_header_id :=
652 l_inv_resv_tbl (l_count).revision := l_resv_tbl (i).revision;
649 l_resv_tbl (i).supply_source_header_id;
650 l_inv_resv_tbl (l_count).supply_source_line_id :=
651 l_resv_tbl (i).supply_source_line_id;
653 l_inv_resv_tbl (l_count).subinventory_code :=
654 l_resv_tbl (i).subinventory_code;
655 l_inv_resv_tbl (l_count).locator_id := l_resv_tbl (i).locator_id;
656 l_inv_resv_tbl (l_count).lot_number := l_resv_tbl (i).lot_number;
657 l_inv_resv_tbl (l_count).lpn_id := l_resv_tbl (i).lpn_id;
658 END IF;
659 END LOOP;
660
661 IF g_debug <= gme_debug.g_log_statement THEN
662 gme_debug.put_line
663 ( g_pkg_name
664 || '.'
665 || l_api_name
666 || ':No. of reservations passed to create suggestions is '
667 || l_count);
668 END IF;
669
670 IF (p_plan_tasks = 'Y') THEN
671 l_plan_tasks := TRUE;
672 ELSE
673 l_plan_tasks := FALSE;
674 END IF;
675
676 wms_engine_pvt.create_suggestions
677 (p_api_version => 1.0
678 ,p_init_msg_list => fnd_api.g_false
679 ,p_commit => fnd_api.g_false
680 ,p_validation_level => fnd_api.g_valid_level_none
681 ,x_return_status => l_return_status
682 ,x_msg_count => l_msg_count
683 ,x_msg_data => l_msg_data
684 ,p_transaction_temp_id => p_mo_line_rec.line_id
685 ,p_reservations => l_inv_resv_tbl
686 ,p_suggest_serial => fnd_api.g_false
687 ,p_simulation_mode => wms_engine_pvt.g_no_simulation
688 ,p_simulation_id => NULL
689 ,p_plan_tasks => l_plan_tasks
690 ,p_quick_pick_flag => 'N');
691
692 IF g_debug <= gme_debug.g_log_statement THEN
693 gme_debug.put_line ( g_pkg_name
694 || '.'
695 || l_api_name
696 || 'create suggestions returns '
697 || l_return_status);
698 gme_debug.put_line ( g_pkg_name
699 || '.'
700 || l_api_name
701 || 'create suggestions mesg '
702 || l_msg_data);
703 END IF;
704
705 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
706 RAISE create_suggestions_err;
707 END IF;
708
709 OPEN cur_detailed_qty (p_mo_line_rec.line_id);
710
711 FETCH cur_detailed_qty
712 INTO l_qty_detailed, l_sec_qty_detailed;
713
714 CLOSE cur_detailed_qty;
715
716 IF g_debug <= gme_debug.g_log_statement THEN
717 gme_debug.put_line ( g_pkg_name
718 || '.'
719 || l_api_name
720 || ':Qty allocated '
721 || l_qty_detailed);
722 gme_debug.put_line ( g_pkg_name
723 || '.'
724 || l_api_name
725 || ':Sec Qty allocated '
726 || l_sec_qty_detailed);
727 END IF;
728
729 l_mo_line_rec := p_mo_line_rec;
730
731 IF (NVL (l_qty_detailed, 0) > 0) THEN
732 IF g_debug <= gme_debug.g_log_statement THEN
733 gme_debug.put_line ( g_pkg_name
734 || '.'
735 || l_api_name
736 || ':Get mmtt recs and create pick slips ');
737 END IF;
738
739 l_mo_line_rec.quantity_detailed := l_qty_detailed;
740 l_mo_line_rec.secondary_quantity_detailed := l_sec_qty_detailed;
741
742 FOR get_mmtt IN cur_mmtt (p_mo_line_rec.line_id) LOOP
743 --Bug# 7383625 :Added IF condition to populate detailed_quantity
744 IF get_mmtt.reservation_id is not NULL then
745 update mtl_reservations
746 set detailed_quantity = get_mmtt.primary_quantity
747 where reservation_id = get_mmtt.reservation_id;
748 END IF;
749
750 IF g_debug <= gme_debug.g_log_statement THEN
751 gme_debug.put_line(g_pkg_name || '.' || l_api_name
752 || ' Calling ASSIGNTT with temp id ' || get_mmtt.transaction_temp_id);
753 END IF;
754
755 -- Bug 6778259 - Assign user task properly.
756 WMS_RULE_PVT.assigntt
757 (p_api_version => 1.0,
758 p_task_id => get_mmtt.transaction_temp_id,
759 x_return_status => l_return_status,
760 x_msg_count => l_msg_count,
761 x_msg_data => l_msg_data
762 );
763
764 IF g_debug <= gme_debug.g_log_statement THEN
768 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
765 gme_debug.put_line(g_pkg_name || '.' || l_api_name
766 || ':Return from ASSIGNTT IS ' || l_return_status);
767
769 gme_debug.put_line ( g_pkg_name
770 || '.'
771 || l_api_name
772 || 'temp_id is '
773 || get_mmtt.transaction_temp_id);
774 gme_debug.put_line ( g_pkg_name
775 || '.'
776 || l_api_name
777 || ':Message is '
778 || l_msg_data);
779 END IF;
780 END IF;
781
782 -- Bug 6778259 - Use existing exception to return error.
783 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
784 RAISE get_pick_slip_err;
785 END IF;
786
787 IF get_mmtt.pick_slip_number IS NULL THEN
788 IF g_debug <= gme_debug.g_log_statement THEN
789 gme_debug.put_line
790 ( g_pkg_name
791 || '.'
792 || l_api_name
793 || ':Calling inv_pr_pick_slip_number.get_pick_slip_number');
794 END IF;
795
796 inv_pr_pick_slip_number.get_pick_slip_number
797 (p_pick_grouping_rule_id => p_grouping_rule_id
798 ,p_org_id => p_mo_line_rec.organization_id
799 ,p_wip_entity_id => p_mo_line_rec.txn_source_id
800 ,p_rep_schedule_id => NULL
801 ,p_operation_seq_num => p_mo_line_rec.txn_source_line_id
802 ,p_dept_id => NULL
803 ,p_push_or_pull => NULL
804 ,p_supply_subinventory => get_mmtt.transfer_subinventory
805 ,p_supply_locator_id => get_mmtt.transfer_to_location
806 ,p_project_id => NULL
807 ,p_task_id => NULL
808 ,p_src_subinventory => get_mmtt.subinventory_code
809 ,p_src_locator_id => get_mmtt.locator_id
810 ,p_inventory_item_id => p_mo_line_rec.inventory_item_id
811 ,p_revision => get_mmtt.revision
812 ,p_lot_number => NULL
813 ,x_pick_slip_number => l_pick_slip_number
814 ,x_api_status => l_return_status
815 ,x_error_message => l_msg_data);
816
817 IF g_debug <= gme_debug.g_log_statement THEN
818 gme_debug.put_line
819 ( g_pkg_name
820 || '.'
821 || l_api_name
822 || ':Return from inv_pr_pick_slip_number.get_pick_slip_number '
823 || l_return_status);
824 gme_debug.put_line ( g_pkg_name
825 || '.'
826 || l_api_name
827 || ':l_pick_slip_number is '
828 || l_pick_slip_number);
829 gme_debug.put_line ( g_pkg_name
830 || '.'
831 || l_api_name
832 || ':Message is '
833 || l_msg_data);
834 END IF;
835
836 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
837 RAISE get_pick_slip_err;
838 END IF;
839
840 UPDATE mtl_material_transactions_temp
841 SET pick_slip_number = l_pick_slip_number,
842 wip_entity_type = gme_common_pvt.g_wip_entity_type_batch
843 WHERE transaction_temp_id = get_mmtt.transaction_temp_id;
844 ELSE
845 UPDATE mtl_material_transactions_temp
846 SET wip_entity_type = gme_common_pvt.g_wip_entity_type_batch
847 WHERE transaction_temp_id = get_mmtt.transaction_temp_id;
848 END IF;
849 END LOOP;
850
851 IF (NVL (l_qty_detailed, 0) < p_mo_line_rec.quantity) THEN
852 l_backordered_qty :=
853 p_mo_line_rec.quantity - NVL (l_qty_detailed, 0);
854 l_mo_line_rec.quantity := NVL (l_qty_detailed, 0);
855 g_partially_allocated := TRUE;
856 ELSE
857 g_fully_allocated := TRUE;
858 END IF;
859 ELSE
860 IF g_debug <= gme_debug.g_log_statement THEN
861 gme_debug.put_line ( g_pkg_name
862 || '.'
863 || l_api_name
864 || ':Close mo lines since no allocations');
865 END IF;
866
867 l_backordered_qty := p_mo_line_rec.quantity;
868 l_mo_line_rec.line_status := 5;
869 g_not_allocated := TRUE;
870 END IF;
871
872 IF g_debug <= gme_debug.g_log_statement THEN
873 gme_debug.put_line ( g_pkg_name
874 || '.'
875 || l_api_name
876 || 'Updating MO line');
877 END IF;
878
879 inv_trolin_util.update_row (l_mo_line_rec);
880
881 IF g_debug <= gme_debug.g_log_statement THEN
882 gme_debug.put_line ( g_pkg_name
883 || '.'
884 || l_api_name
885 || 'After Updating MO line');
886 END IF;
887
888 IF g_debug <= gme_debug.g_log_statement THEN
889 gme_debug.put_line ( g_pkg_name
890 || '.'
891 || l_api_name
892 || 'l_backordered_qty = '
893 || l_backordered_qty);
894 END IF;
895
896 IF (l_backordered_qty > 0) THEN
897 UPDATE gme_material_details
898 SET backordered_qty = l_backordered_qty
899 WHERE material_detail_id = p_mo_line_rec.txn_source_line_id;
900 END IF;
901
902 IF g_debug <= gme_debug.g_log_procedure THEN
903 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
904 END IF;
905 EXCEPTION
906 WHEN create_suggestions_err OR get_pick_slip_err THEN
907 x_return_status := l_return_status;
908 WHEN OTHERS THEN
909 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
910
911 IF g_debug <= gme_debug.g_log_unexpected THEN
912 gme_debug.put_line ( 'When others exception in '
913 || g_pkg_name
914 || '.'
915 || l_api_name
916 || ' Error is '
917 || SQLERRM);
918 END IF;
919
920 x_return_status := fnd_api.g_ret_sts_unexp_error;
921 END process_line;
922 END gme_picking_pvt;