[Home] [Help]
PACKAGE BODY: APPS.GME_MOVE_ORDERS_PVT
Source
1 PACKAGE BODY gme_move_orders_pvt AS
2 /* $Header: GMEVMOVB.pls 120.19.12020000.2 2012/07/26 15:50:05 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_MOVE_ORDERS_PVT';
5
6 /*************************************************************************************************/
7 /* Oracle Process Manufacturing Process Execution APIs */
8 /* */
9 /* File Name: GMEVMOVB.pls */
10 /* Contents: GME move order related procedures. */
11 /* HISTORY */
12 /* SivakumarG 06-MAR-2006 Bug#5078853 */
13 /* update_move_order_lines procedure modified material line and send to the inv api */
14 /* to process the move order line.
15 /* Swapna K 11-OCT-2007 Bug#6446877 */
16 /* update_move_order_lines procedure is changed to fetch the alloc uom of the material */
17 /* line and send to the inv api to process the move order line. */
18
19 -- G. Muratore 16-OCT-2009 Bug 9028327
20 -- Add code to update primary and secondary if necessary.
21 -- PROCEDURE update_move_order_lines
22
23 -- S. Kommineni 17-OCT-2009 Bug 9028327/9030411
24 -- Add same code as previous fix to update UOM, primary and secondary if necessary.
25 -- PROCEDURE update_move_order_lines
26
27 -- G. Muratore 04-FEB-2010 Bug 9268209
28 -- Populate carton grouping id for wms orgs for real move orders only.
29 -- PROCEDURE create_move_order_lines
30
31 -- G. Muratore 31-AUG-2010 Bug 9941121
32 -- Populate grouping_rule_id, in move order record, with the value passed into the new parameter.
33 -- PROCEDURE create_move_order_hdr
34
35 -- G. Muratore 02-DEC-2011 Bug 13076579
36 -- The code was changed to pass back the total quantity already detailed not the overall quantity
37 -- of the move order. This procedure name is misleading. This only gets called by get_open_qty.
38 -- get_open_qty then uses the value returned to subtract it from the plan or wip plan.
39 -- PROCEDURE get_pending_move_order_qty
40 /*************************************************************************************************/
41 PROCEDURE create_move_order_hdr (
42 p_organization_id IN NUMBER
43 ,p_move_order_type IN NUMBER
44 ,p_grouping_rule_id IN NUMBER DEFAULT 0
45 ,x_move_order_header_id OUT NOCOPY NUMBER
46 ,x_return_status OUT NOCOPY VARCHAR2)
47 IS
48 l_api_name CONSTANT VARCHAR2 (30) := 'create_move_order_hdr';
49 l_return_status VARCHAR2 (1);
50 l_msg_count NUMBER;
51 l_msg_data VARCHAR2 (2000);
52 l_in_trohdr_rec inv_move_order_pub.trohdr_rec_type;
53 l_in_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
54 l_out_trohdr_rec inv_move_order_pub.trohdr_rec_type;
55 l_out_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
56 create_mo_hdr_err EXCEPTION;
57 BEGIN
58 IF g_debug <= gme_debug.g_log_procedure THEN
59 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
60 || l_api_name);
61 END IF;
62
63 x_return_status := fnd_api.g_ret_sts_success;
64 l_in_trohdr_rec.organization_id := p_organization_id;
65 l_in_trohdr_rec.move_order_type := p_move_order_type;
66 l_in_trohdr_rec.operation := inv_globals.g_opr_create;
67 l_in_trohdr_rec.request_number := fnd_api.g_miss_char;
68 l_in_trohdr_rec.header_id := fnd_api.g_miss_num;
69 l_in_trohdr_rec.creation_date := gme_common_pvt.g_timestamp;
70 l_in_trohdr_rec.created_by := gme_common_pvt.g_user_ident;
71 l_in_trohdr_rec.last_update_date := gme_common_pvt.g_timestamp;
72 l_in_trohdr_rec.last_updated_by := gme_common_pvt.g_user_ident;
73
74 -- Bug 9941121 - Initialize grouping_rule_id properly.
75 l_in_trohdr_rec.grouping_rule_id := p_grouping_rule_id;
76
77 IF (g_debug IS NOT NULL) THEN
78 gme_debug.put_line
79 ('Calling inv_move_order_pub.create_move_order_header');
80 END IF;
81
82 inv_move_order_pub.create_move_order_header
83 (p_api_version_number => 1.0
84 ,p_init_msg_list => fnd_api.g_false
85 ,p_return_values => fnd_api.g_false
86 ,p_commit => fnd_api.g_false
87 ,x_return_status => l_return_status
88 ,x_msg_count => l_msg_count
89 ,x_msg_data => l_msg_data
90 ,p_trohdr_rec => l_in_trohdr_rec
91 ,p_trohdr_val_rec => l_in_trohdr_val_rec
92 ,x_trohdr_rec => l_out_trohdr_rec
93 ,x_trohdr_val_rec => l_out_trohdr_val_rec
94 ,p_validation_flag => inv_move_order_pub.g_validation_yes);
95
96 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
97 RAISE create_mo_hdr_err;
98 ELSE
99 IF g_debug <= gme_debug.g_log_statement THEN
100 gme_debug.put_line ('Header_id is ' || l_out_trohdr_rec.header_id);
101 gme_debug.put_line ( 'request_number is '
102 || l_out_trohdr_rec.request_number);
103 END IF;
104
105 x_move_order_header_id := l_out_trohdr_rec.header_id;
106 END IF;
107
108 IF g_debug <= gme_debug.g_log_procedure THEN
109 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
110 END IF;
111 EXCEPTION
112 WHEN create_mo_hdr_err THEN
113 IF (g_debug <= gme_debug.g_log_error) THEN
114 gme_debug.put_line
115 ( 'inv_move_order_pub.create_move_order_header returns '
116 || l_return_status);
117 gme_debug.put_line ('error message is ' || l_msg_data);
118 END IF;
119
120 x_return_status := l_return_status;
121 WHEN OTHERS THEN
122 IF g_debug <= gme_debug.g_log_unexpected THEN
123 gme_debug.put_line ( 'When others exception in '
124 || g_pkg_name
125 || '.'
126 || l_api_name
127 || ' Error is '
128 || SQLERRM);
129 END IF;
130
131 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
132 x_return_status := fnd_api.g_ret_sts_unexp_error;
133 END create_move_order_hdr;
134
135 PROCEDURE create_move_order_lines (
136 p_move_order_header_id IN NUMBER
137 ,p_move_order_type IN NUMBER
138 ,p_material_details_tbl IN gme_common_pvt.material_details_tab
139 ,x_material_details_tbl OUT NOCOPY gme_common_pvt.material_details_tab
140 ,x_trolin_tbl OUT NOCOPY inv_move_order_pub.trolin_tbl_type
141 ,x_return_status OUT NOCOPY VARCHAR2)
142 IS
143 l_api_name CONSTANT VARCHAR2 (30) := 'create_move_order_lines';
144 l_return_status VARCHAR2 (1);
145 l_msg_count NUMBER;
146 l_count NUMBER;
147 l_temp_qty NUMBER;
148 l_txn_enabled_flag VARCHAR2 (10);
149 l_sec_uom_code VARCHAR2 (10);
150 l_msg_data VARCHAR2 (2000);
151 l_item_no VARCHAR2 (2000);
152 l_material_details_tbl gme_common_pvt.material_details_tab;
153 l_in_trolin_tbl inv_move_order_pub.trolin_tbl_type;
154 l_in_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
155 l_out_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
156 create_mo_line_err EXCEPTION;
157
158 CURSOR cur_item_mst (v_org_id NUMBER, v_inventory_item_id NUMBER)
159 IS
160 SELECT mtl_transactions_enabled_flag, secondary_uom_code, segment1
161 FROM mtl_system_items_b
162 WHERE organization_id = v_org_id
163 AND inventory_item_id = v_inventory_item_id;
164
165 -- 9268209
166 CURSOR cur_wms_enabled (v_org_id NUMBER)
167 IS
168 SELECT wms_enabled_flag
169 FROM mtl_parameters
170 WHERE organization_id = v_org_id;
171
172 l_wms_enabled_flag VARCHAR2(1);
173
174 BEGIN
175 IF g_debug <= gme_debug.g_log_procedure THEN
176 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
177 || l_api_name);
178 END IF;
179
180 x_return_status := fnd_api.g_ret_sts_success;
181
182 FOR i IN 1 .. p_material_details_tbl.COUNT LOOP
183 IF (p_material_details_tbl (i).line_type = -1) THEN
184 OPEN cur_item_mst (p_material_details_tbl (i).organization_id
185 ,p_material_details_tbl (i).inventory_item_id);
186
187 FETCH cur_item_mst
188 INTO l_txn_enabled_flag, l_sec_uom_code, l_item_no;
189
190 CLOSE cur_item_mst;
191
192 IF (l_txn_enabled_flag = 'Y' AND NVL(p_material_details_tbl (i).phantom_type,0) = 0) THEN
193 l_count := l_material_details_tbl.COUNT + 1;
194 l_material_details_tbl (l_count) := p_material_details_tbl (i);
195 l_in_trolin_tbl (l_count).operation :=
196 inv_globals.g_opr_create;
197 l_in_trolin_tbl (l_count).header_id := p_move_order_header_id;
198 l_in_trolin_tbl (l_count).inventory_item_id :=
199 l_material_details_tbl (l_count).inventory_item_id;
200 l_in_trolin_tbl (l_count).organization_id :=
201 l_material_details_tbl (l_count).organization_id;
202 l_in_trolin_tbl (l_count).quantity :=
203 l_material_details_tbl (l_count).plan_qty;
204 l_in_trolin_tbl (l_count).uom_code :=
205 l_material_details_tbl (l_count).dtl_um;
206 l_in_trolin_tbl (l_count).revision :=
207 l_material_details_tbl (l_count).revision;
208 l_in_trolin_tbl (l_count).txn_source_id :=
209 l_material_details_tbl (l_count).batch_id;
210 l_in_trolin_tbl (l_count).txn_source_line_id :=
211 l_material_details_tbl (l_count).material_detail_id;
212 l_in_trolin_tbl (l_count).date_required :=
213 l_material_details_tbl (l_count).material_requirement_date;
214 l_in_trolin_tbl (l_count).creation_date :=
215 gme_common_pvt.g_timestamp;
216 l_in_trolin_tbl (l_count).created_by :=
217 gme_common_pvt.g_user_ident;
218 l_in_trolin_tbl (l_count).last_update_date :=
219 gme_common_pvt.g_timestamp;
220 l_in_trolin_tbl (l_count).last_updated_by :=
221 gme_common_pvt.g_user_ident;
222 l_in_trolin_tbl (l_count).transaction_type_id :=
223 gme_common_pvt.g_backflush_transfer;
224 l_in_trolin_tbl (l_count).transaction_source_type_id :=
225 gme_common_pvt.g_txn_source_type;
226
227 /* Populate below values only for explicit move orders */
228 IF (p_move_order_type <> gme_common_pvt.g_invis_move_order_type) THEN
229 l_in_trolin_tbl (l_count).to_subinventory_code :=
230 l_material_details_tbl (l_count).subinventory;
231 l_in_trolin_tbl (l_count).to_locator_id :=
232 l_material_details_tbl (l_count).locator_id;
233
234 -- 9268209 - Populate carton grouping id for wms orgs.
235 OPEN cur_wms_enabled (p_material_details_tbl (i).organization_id);
236
237 FETCH cur_wms_enabled
238 INTO l_wms_enabled_flag;
239
240 CLOSE cur_wms_enabled;
241
242 IF NVL(l_wms_enabled_flag,'N') = 'Y' THEN
243 select WSH_DELIVERY_GROUP_S.nextval into
244 l_in_trolin_tbl (l_count).carton_grouping_id from dual;
245 END IF;
246
247 END IF;
248 IF (l_sec_uom_code IS NOT NULL) THEN
249 IF (l_material_details_tbl (l_count).dtl_um <> l_sec_uom_code) THEN
250 l_temp_qty := inv_convert.inv_um_convert
251 (item_id => l_material_details_tbl (l_count).inventory_item_id
252 ,PRECISION => gme_common_pvt.g_precision
253 ,from_quantity => l_material_details_tbl (l_count).plan_qty
254 ,from_unit => l_material_details_tbl (l_count).dtl_um
255 ,to_unit => l_sec_uom_code
256 ,from_name => NULL
257 ,to_name => NULL);
258 IF l_temp_qty < 0 THEN
259 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
260 fnd_message.set_token ('ITEM_NO', l_item_no);
261 fnd_message.set_token ('FROM_UOM', l_material_details_tbl (l_count).dtl_um);
262 fnd_message.set_token ('TO_UOM', l_sec_uom_code);
263 fnd_msg_pub.add;
264 l_temp_qty := NULL;
265 END IF;
266 ELSE
267 l_temp_qty := l_material_details_tbl (l_count).plan_qty;
268 END IF;
269 l_in_trolin_tbl (l_count).secondary_quantity := l_temp_qty;
270 l_in_trolin_tbl (l_count).secondary_uom := l_sec_uom_code;
271 END IF;
272 ELSE
273 x_material_details_tbl (x_material_details_tbl.COUNT + 1) :=
274 p_material_details_tbl (i);
275 END IF;
276 ELSE
277 x_material_details_tbl (x_material_details_tbl.COUNT + 1) :=
278 p_material_details_tbl (i);
279 END IF;
280 END LOOP;
281
282 IF g_debug <= gme_debug.g_log_statement THEN
283 gme_debug.put_line
284 ( 'Calling inv_move_order_pub.create_move_order_lines with no. of lines = '
285 || l_in_trolin_tbl.COUNT);
286 END IF;
287 /* Bug 4866700 added check to call only if records are there in table */
288 IF (l_in_trolin_tbl.COUNT > 0) THEN
289 inv_move_order_pub.create_move_order_lines
290 (p_api_version_number => 1.0
291 ,p_init_msg_list => fnd_api.g_false
292 ,p_return_values => fnd_api.g_false
293 ,p_commit => fnd_api.g_false
294 ,x_return_status => l_return_status
295 ,x_msg_count => l_msg_count
296 ,x_msg_data => l_msg_data
297 ,p_trolin_tbl => l_in_trolin_tbl
298 ,p_trolin_val_tbl => l_in_trolin_val_tbl
299 ,x_trolin_tbl => x_trolin_tbl
300 ,x_trolin_val_tbl => l_out_trolin_val_tbl
301 ,p_validation_flag => inv_move_order_pub.g_validation_yes);
302
303 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
304 RAISE create_mo_line_err;
305 ELSE
306 IF g_debug <= gme_debug.g_log_statement THEN
307 gme_debug.put_line ( 'No. of move order lines created = '
308 || x_trolin_tbl.COUNT);
309 END IF;
310
311 /* Populate out structure only for invisible move order */
312 IF (p_move_order_type = gme_common_pvt.g_invis_move_order_type) THEN
313 FOR i IN 1 .. l_material_details_tbl.COUNT LOOP
314 l_material_details_tbl (i).move_order_line_id :=
315 x_trolin_tbl (i).line_id;
316 x_material_details_tbl (x_material_details_tbl.COUNT + 1) :=
317 l_material_details_tbl (i);
318 END LOOP;
319 END IF;
320 END IF;
321 END IF;
322 IF g_debug <= gme_debug.g_log_procedure THEN
323 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
324 END IF;
325 EXCEPTION
326 WHEN create_mo_line_err THEN
327 IF g_debug <= gme_debug.g_log_error THEN
328 gme_debug.put_line
329 ( 'inv_move_order_pub.create_move_order_lines returns '
330 || l_return_status);
331 gme_debug.put_line ('message count is ' || l_msg_count);
332
333 FOR i IN 1 .. l_msg_count LOOP
334 l_msg_data :=
335 fnd_msg_pub.get (p_msg_index => i
336 ,p_encoded => 'T');
337 gme_debug.put_line ('error message is ' || l_msg_data);
338 END LOOP;
339 END IF;
340
341 x_return_status := l_return_status;
342 WHEN OTHERS THEN
343 IF g_debug <= gme_debug.g_log_unexpected THEN
344 gme_debug.put_line ( 'When others exception in '
345 || g_pkg_name
346 || '.'
347 || l_api_name
348 || ' Error is '
349 || SQLERRM);
350 END IF;
351
352 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
353 x_return_status := fnd_api.g_ret_sts_unexp_error;
354 END create_move_order_lines;
355
356 PROCEDURE create_batch_move_order (
357 p_batch_header_rec IN gme_batch_header%ROWTYPE
358 ,p_material_details_tbl IN gme_common_pvt.material_details_tab
359 ,x_return_status OUT NOCOPY VARCHAR2)
360 IS
361 l_api_name CONSTANT VARCHAR2 (30) := 'create_batch_move_order';
362 l_return_status VARCHAR2 (1);
363 l_from_uom VARCHAR2 (3);
364 l_to_uom VARCHAR2 (3);
365 l_item_no VARCHAR2 (2000);
366 l_msg_data VARCHAR2 (2000);
367 l_mtl_txns_enabled_flag mtl_system_items_kfv.mtl_transactions_enabled_flag%TYPE;
368 l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
369 l_primary_uom_code mtl_system_items_kfv.primary_uom_code%TYPE;
370
371 l_mtl_dtl_tbl gme_common_pvt.material_details_tab;
372 l_mtl_dtl_tbl_out gme_common_pvt.material_details_tab;
373 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
374 l_is_revision_control BOOLEAN;
375 l_is_lot_control BOOLEAN;
376 l_is_serial_control BOOLEAN;
377 l_plan_qty_prim NUMBER;
378 l_count NUMBER;
379 l_msg_count NUMBER;
380 l_move_order_header_id NUMBER;
381 l_qoh NUMBER;
382 l_rqoh NUMBER;
383 l_qr NUMBER;
384 l_qs NUMBER;
385 l_att NUMBER;
386 l_atr NUMBER;
387 l_sqoh NUMBER;
388 l_srqoh NUMBER;
389 l_sqr NUMBER;
390 l_sqs NUMBER;
391 l_satt NUMBER;
392 l_satr NUMBER;
393 l_diff_qty NUMBER;
394 CURSOR cur_get_item_info (v_org_id NUMBER, v_inventory_item_id NUMBER)
395 IS
396 SELECT mtl_transactions_enabled_flag, concatenated_segments, primary_uom_code
397 FROM mtl_system_items_kfv
398 WHERE inventory_item_id = v_inventory_item_id
399 AND organization_id = v_org_id;
400
401 create_mo_err EXCEPTION;
402 uom_conversion_err EXCEPTION;
403 unable_to_query_tree EXCEPTION;
404 BEGIN
405 IF g_debug <= gme_debug.g_log_procedure THEN
406 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
407 || l_api_name);
408 END IF;
409
410 x_return_status := fnd_api.g_ret_sts_success;
411 FOR i IN 1 .. p_material_details_tbl.COUNT LOOP
412
413 IF g_debug <= gme_debug.g_log_procedure THEN
414 gme_debug.put_line ('Iteration of loop is '||i);
415 gme_debug.put_line ('line_type is '||p_material_details_tbl (i).line_type);
416 gme_debug.put_line ('subinventory is '||p_material_details_tbl (i).subinventory);
417 gme_debug.put_line ('material_requirement_date is '||TO_CHAR(p_material_details_tbl (i).material_requirement_date,'DD-MON-YYYY HH24:MI:SS'));
418 gme_debug.put_line ('g_move_order_timefence is '||NVL(gme_common_pvt.g_move_order_timefence, 99999));
419 END IF;
420
421 IF ( p_material_details_tbl (i).line_type = -1
422 AND p_material_details_tbl (i).subinventory IS NOT NULL
423 AND p_material_details_tbl (i).material_requirement_date <
424 (SYSDATE + NVL (gme_common_pvt.g_move_order_timefence, 0) ) ) THEN
425 IF g_debug <= gme_debug.g_log_procedure THEN
426 gme_debug.put_line ('point 1');
427 END IF;
428
429 OPEN cur_get_item_info
430 (p_material_details_tbl (i).organization_id
431 ,p_material_details_tbl (i).inventory_item_id);
432
433 FETCH cur_get_item_info
434 INTO l_mtl_txns_enabled_flag, l_concatenated_segments, l_primary_uom_code;
435
436 CLOSE cur_get_item_info;
437 IF (l_mtl_txns_enabled_flag = 'Y') THEN
438 gme_transactions_pvt.query_quantities
439 (x_return_status => l_return_status
440 ,x_msg_count => l_msg_count
441 ,x_msg_data => l_msg_data
442 ,p_organization_id => p_material_details_tbl
443 (i).organization_id
444 ,p_inventory_item_id => p_material_details_tbl
445 (i).inventory_item_id
446 ,p_tree_mode => gme_common_pvt.g_tree_transaction_mode
447 ,p_grade_code => NULL
448 ,p_demand_source_header_id => p_material_details_tbl
449 (i).batch_id
450 ,p_demand_source_line_id => p_material_details_tbl
451 (i).material_detail_id
452 ,p_revision => p_material_details_tbl
453 (i).revision
454 ,p_lot_number => NULL
455 ,p_subinventory_code => p_material_details_tbl
456 (i).subinventory
457 ,p_locator_id => NULL
458 ,x_qoh => l_qoh
459 ,x_rqoh => l_rqoh
460 ,x_qr => l_qr
461 ,x_qs => l_qs
462 ,x_att => l_att
463 ,x_atr => l_atr
464 ,x_sqoh => l_sqoh
465 ,x_srqoh => l_srqoh
466 ,x_sqr => l_sqr
467 ,x_sqs => l_sqs
468 ,x_satt => l_satt
469 ,x_satr => l_satr);
470 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
471 IF g_debug <= gme_debug.g_log_statement THEN
472 gme_debug.put_line ( 'item = '
473 || l_concatenated_segments);
474 gme_debug.put_line
475 ( 'subinventory = '
476 || p_material_details_tbl (i).subinventory);
477 gme_debug.put_line ('l_att = ' || l_att);
478 END IF;
479 IF (l_primary_uom_code <>
480 p_material_details_tbl (i).dtl_um) THEN
481 l_plan_qty_prim :=
482 inv_convert.inv_um_convert
483 (item_id => p_material_details_tbl (i).inventory_item_id
484 ,PRECISION => gme_common_pvt.g_precision
485 ,from_quantity => p_material_details_tbl (i).plan_qty
486 ,from_unit => p_material_details_tbl (i).dtl_um
487 ,to_unit => l_primary_uom_code
488 ,from_name => NULL
489 ,to_name => NULL);
490
491 IF (l_plan_qty_prim < 0) THEN
492 l_item_no := l_concatenated_segments;
493 l_from_uom := p_material_details_tbl (i).dtl_um;
494 l_to_uom := l_primary_uom_code;
495 RAISE uom_conversion_err;
496 END IF;
497 ELSE
498 l_plan_qty_prim := p_material_details_tbl (i).plan_qty;
499 END IF;
500 IF (l_att < l_plan_qty_prim) THEN
501 l_count := l_mtl_dtl_tbl.COUNT + 1;
502 l_mtl_dtl_tbl (l_count) := p_material_details_tbl (i);
503 l_diff_qty := l_plan_qty_prim - l_att;
504
505 IF (l_diff_qty > l_plan_qty_prim) THEN
506 l_diff_qty := l_plan_qty_prim;
507 END IF;
508 IF (l_primary_uom_code <>
509 p_material_details_tbl (i).dtl_um) THEN
510 l_mtl_dtl_tbl (l_count).plan_qty :=
511 inv_convert.inv_um_convert
512 (item_id => p_material_details_tbl
513 (i).inventory_item_id
514 ,PRECISION => gme_common_pvt.g_precision
515 ,from_quantity => l_diff_qty
516 ,from_unit => l_primary_uom_code
517 ,to_unit => p_material_details_tbl
518 (i).dtl_um
519 ,from_name => NULL
520 ,to_name => NULL);
521 ELSE
522 l_mtl_dtl_tbl (l_count).plan_qty := l_diff_qty;
523 END IF;
524 END IF;
525 ELSE
526 RAISE unable_to_query_tree;
527 END IF;
528 END IF;
529 END IF;
530 END LOOP;
531 IF (l_count > 0) THEN
532 gme_move_orders_pvt.create_move_order_hdr
533 (p_organization_id => p_batch_header_rec.organization_id
534 ,p_move_order_type => gme_common_pvt.g_move_order_type
535 ,x_move_order_header_id => l_move_order_header_id
536 ,x_return_status => l_return_status);
537
538 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
539 RAISE create_mo_err;
540 ELSE
541 gme_move_orders_pvt.create_move_order_lines
542 (p_move_order_header_id => l_move_order_header_id
543 ,p_move_order_type => gme_common_pvt.g_move_order_type
544 ,p_material_details_tbl => l_mtl_dtl_tbl
545 ,x_material_details_tbl => l_mtl_dtl_tbl_out
546 ,x_trolin_tbl => l_trolin_tbl
547 ,x_return_status => l_return_status);
548
549 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
550 RAISE create_mo_err;
551 END IF;
552 END IF;
553 END IF;
554
555 IF g_debug <= gme_debug.g_log_procedure THEN
556 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
557 END IF;
558 EXCEPTION
559 WHEN create_mo_err OR unable_to_query_tree THEN
560 x_return_status := l_return_status;
561 WHEN uom_conversion_err THEN
562 x_return_status := fnd_api.g_ret_sts_error;
563 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
564 fnd_message.set_token ('ITEM_NO', l_item_no);
565 fnd_message.set_token ('FROM_UOM', l_from_uom);
566 fnd_message.set_token ('TO_UOM', l_to_uom);
567 WHEN OTHERS THEN
568 IF g_debug <= gme_debug.g_log_unexpected THEN
569 gme_debug.put_line ( 'When others exception in '
570 || g_pkg_name
571 || '.'
572 || l_api_name
573 || ' Error is '
574 || SQLERRM);
575 END IF;
576
577 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
578 x_return_status := fnd_api.g_ret_sts_unexp_error;
579 END create_batch_move_order;
580
581 PROCEDURE get_move_order_lines (
582 p_organization_id IN NUMBER
583 ,p_batch_id IN NUMBER
584 ,p_material_detail_id IN NUMBER
585 ,x_mo_line_tbl OUT NOCOPY gme_common_pvt.mo_lines_tab
586 ,x_return_status OUT NOCOPY VARCHAR2)
587 IS
588 l_api_name CONSTANT VARCHAR2 (30) := 'get_move_order_lines';
589
590 CURSOR cur_move_order_lines
591 IS
592 SELECT l.*
593 FROM mtl_txn_request_lines l, mtl_txn_request_headers h
594 WHERE l.organization_id = p_organization_id
595 AND transaction_source_type_id =
596 gme_common_pvt.g_txn_source_type
597 AND l.txn_source_id = p_batch_id
598 AND l.txn_source_line_id = p_material_detail_id
599 AND l.line_status NOT IN (5, 6)
600 AND h.header_id = l.header_id
601 AND h.move_order_type NOT IN
602 (gme_common_pvt.g_invis_move_order_type
603 ,inv_globals.g_move_order_put_away)
604 ORDER BY l.header_id, l.line_id;
605 BEGIN
606 IF g_debug <= gme_debug.g_log_procedure THEN
607 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
608 || l_api_name);
609 END IF;
610
611 x_return_status := fnd_api.g_ret_sts_success;
612
613 OPEN cur_move_order_lines;
614
615 FETCH cur_move_order_lines
616 BULK COLLECT INTO x_mo_line_tbl;
617
618 CLOSE cur_move_order_lines;
619
620 IF g_debug <= gme_debug.g_log_procedure THEN
621 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name||' No of lines '||x_mo_line_tbl.count);
622 END IF;
623 EXCEPTION
624 WHEN OTHERS THEN
625 IF g_debug <= gme_debug.g_log_unexpected THEN
626 gme_debug.put_line ( 'When others exception in '
627 || g_pkg_name
628 || '.'
629 || l_api_name
630 || ' Error is '
631 || SQLERRM);
632 END IF;
633
634 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
635 x_return_status := fnd_api.g_ret_sts_unexp_error;
636 END get_move_order_lines;
637
638 PROCEDURE delete_move_order_lines (
639 p_organization_id IN NUMBER
640 ,p_batch_id IN NUMBER
641 ,p_material_detail_id IN NUMBER
642 ,p_invis_move_line_id IN NUMBER DEFAULT NULL
643 ,p_invis_move_header_id IN NUMBER DEFAULT NULL
644 ,x_return_status OUT NOCOPY VARCHAR2)
645 IS
646 l_api_name CONSTANT VARCHAR2 (30) := 'delete_move_order_lines';
647 l_return_status VARCHAR2 (1);
648 l_row NUMBER;
649 l_count NUMBER;
650 l_curr_header_id NUMBER := 0;
651 l_msg_count NUMBER;
652 l_msg_data VARCHAR2 (2000);
653 l_trohdr_rec inv_move_order_pub.trohdr_rec_type;
654 l_out_trohdr_rec inv_move_order_pub.trohdr_rec_type;
655 l_out_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
656 l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
657 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
658 l_old_trolin_tbl inv_move_order_pub.trolin_tbl_type;
659 l_out_trolin_tbl inv_move_order_pub.trolin_tbl_type;
660 l_line_tbl gme_common_pvt.mo_lines_tab;
661 delete_mo_line_err EXCEPTION;
662 delete_mo_hdr_err EXCEPTION;
663 get_mo_line_err EXCEPTION;
664
665 CURSOR cur_move_order_lines (v_header_id NUMBER)
666 IS
667 SELECT COUNT (1)
668 FROM mtl_txn_request_lines
669 WHERE header_id = v_header_id;
670 BEGIN
671 IF g_debug <= gme_debug.g_log_procedure THEN
672 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
673 || l_api_name);
674 END IF;
675
676 x_return_status := fnd_api.g_ret_sts_success;
677 gme_move_orders_pvt.get_move_order_lines
678 (p_organization_id => p_organization_id
679 ,p_batch_id => p_batch_id
680 ,p_material_detail_id => p_material_detail_id
681 ,x_mo_line_tbl => l_line_tbl
682 ,x_return_status => l_return_status);
683
684 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
685 RAISE get_mo_line_err;
686 END IF;
687
688 FOR i IN 1 .. l_line_tbl.COUNT LOOP
689 l_trolin_tbl (i).operation := inv_globals.g_opr_delete;
690 l_trolin_tbl (i).line_id := l_line_tbl (i).line_id;
691 l_trolin_tbl (i).header_id := l_line_tbl (i).header_id;
692 END LOOP;
693
694 IF (p_invis_move_line_id IS NOT NULL) THEN
695 l_row := l_trolin_tbl.COUNT + 1;
696 l_trolin_tbl (l_row).operation := inv_globals.g_opr_delete;
697 l_trolin_tbl (l_row).line_id := p_invis_move_line_id;
698 l_trolin_tbl (l_row).header_id := p_invis_move_header_id;
699 END IF;
700
701 IF (l_trolin_tbl.count = 0) THEN
702 IF (g_debug <= gme_debug.g_log_statement) THEN
703 gme_debug.put_line('No move order lines to delete');
704 END IF;
705 RETURN;
706 END IF;
707 IF (g_debug <= gme_debug.g_log_statement) THEN
708 gme_debug.put_line
709 ( 'Calling inv_move_order_pub.process_move_order_line in '
710 || l_api_name);
711 END IF;
712
713 inv_move_order_pub.process_move_order_line
714 (p_api_version_number => 1.0
715 ,p_init_msg_list => fnd_api.g_false
716 ,p_return_values => fnd_api.g_false
717 ,p_commit => fnd_api.g_false
718 ,x_return_status => l_return_status
719 ,x_msg_count => l_msg_count
720 ,x_msg_data => l_msg_data
721 ,p_trolin_tbl => l_trolin_tbl
722 ,p_trolin_old_tbl => l_old_trolin_tbl
723 ,x_trolin_tbl => l_out_trolin_tbl);
724
725 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
726 RAISE delete_mo_line_err;
727 END IF;
728
729 FOR i IN 1 .. l_trolin_tbl.COUNT LOOP
730 IF (l_trolin_tbl (i).header_id IS NOT NULL) THEN
731 IF (l_curr_header_id <> l_trolin_tbl (i).header_id) THEN
732 l_curr_header_id := l_trolin_tbl (i).header_id;
733
734 OPEN cur_move_order_lines (l_trolin_tbl (i).header_id);
735
736 FETCH cur_move_order_lines
737 INTO l_count;
738
739 CLOSE cur_move_order_lines;
740
741 IF (l_count = 0) THEN
742 l_trohdr_rec.header_id := l_trolin_tbl (i).header_id;
743 l_trohdr_rec.operation := inv_globals.g_opr_delete;
744
745 IF (g_debug <= gme_debug.g_log_statement) THEN
746 gme_debug.put_line
747 ( 'Calling inv_move_order_pub.process_move_order in '
748 || l_api_name);
749 END IF;
750
751 inv_move_order_pub.process_move_order
752 (p_api_version_number => 1.0
753 ,p_init_msg_list => fnd_api.g_false
754 ,p_return_values => fnd_api.g_false
755 ,p_commit => fnd_api.g_false
756 ,x_return_status => l_return_status
757 ,x_msg_count => l_msg_count
758 ,x_msg_data => l_msg_data
759 ,p_trohdr_rec => l_trohdr_rec
760 ,x_trohdr_rec => l_out_trohdr_rec
761 ,x_trohdr_val_rec => l_out_trohdr_val_rec
762 ,x_trolin_tbl => l_out_trolin_tbl
763 ,x_trolin_val_tbl => l_trolin_val_tbl);
764
765 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
766 RAISE delete_mo_hdr_err;
767 END IF;
768 END IF;
769 END IF;
770 END IF;
771 END LOOP;
772
773 IF g_debug <= gme_debug.g_log_procedure THEN
774 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
775 END IF;
776 EXCEPTION
777 WHEN get_mo_line_err THEN
778 x_return_status := l_return_status;
779 WHEN delete_mo_line_err THEN
780 IF (g_debug IS NOT NULL) THEN
781 gme_debug.put_line
782 ( 'inv_move_order_pub.process_move_order_line returns '
783 || l_return_status);
784 gme_debug.put_line ('error message is ' || l_msg_data);
785 END IF;
786
787 x_return_status := l_return_status;
788 WHEN delete_mo_hdr_err THEN
789 IF (g_debug IS NOT NULL) THEN
790 gme_debug.put_line
791 ( 'inv_move_order_pub.process_move_order returns '
792 || l_return_status);
793 gme_debug.put_line ('error message is ' || l_msg_data);
794 END IF;
795
796 x_return_status := l_return_status;
797 WHEN OTHERS THEN
798 IF g_debug <= gme_debug.g_log_unexpected THEN
799 gme_debug.put_line ( 'When others exception in '
800 || g_pkg_name
801 || '.'
802 || l_api_name
803 || ' Error is '
804 || SQLERRM);
805 END IF;
806
807 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
808 x_return_status := fnd_api.g_ret_sts_unexp_error;
809 END delete_move_order_lines;
810
811
812 PROCEDURE update_move_order_lines (
813 p_batch_id IN NUMBER
814 ,p_material_detail_id IN NUMBER
815 ,p_new_qty IN NUMBER := NULL
816 ,p_new_date IN DATE := NULL
817 ,p_invis_move_line_id IN NUMBER DEFAULT NULL
818 ,x_return_status OUT NOCOPY VARCHAR2)
819 IS
820 l_api_name CONSTANT VARCHAR2 (30) := 'update_move_order_lines';
821 l_return_status VARCHAR2 (1);
822 l_cnt NUMBER;
823 l_curr_qty NUMBER;
824 l_diff_qty NUMBER;
825 l_msg_count NUMBER;
826 l_msg_data VARCHAR2 (2000);
827 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
828 l_old_trolin_tbl inv_move_order_pub.trolin_tbl_type;
829 l_out_trolin_tbl inv_move_order_pub.trolin_tbl_type;
830
831 CURSOR cur_move_order_lines
832 IS
833 SELECT l.*
834 FROM mtl_txn_request_lines l, mtl_txn_request_headers h
835 WHERE transaction_source_type_id =
836 gme_common_pvt.g_txn_source_type
837 AND l.txn_source_id = p_batch_id
838 AND l.txn_source_line_id = p_material_detail_id
839 AND l.line_status NOT IN (5, 6)
840 AND h.header_id = l.header_id
841 AND h.move_order_type NOT IN
842 (gme_common_pvt.g_invis_move_order_type
843 ,inv_globals.g_move_order_put_away)
844 ORDER BY l.creation_date DESC;
845
846 TYPE line_tab IS TABLE OF mtl_txn_request_lines%ROWTYPE
847 INDEX BY BINARY_INTEGER;
848
849 -- Bug 9028327 - Add cursor and local vars.
850 CURSOR cur_item_mst (v_org_id NUMBER, v_inventory_item_id NUMBER)
851 IS
852 SELECT primary_uom_code, secondary_uom_code, tracking_quantity_ind
853 FROM mtl_system_items_b
854 WHERE organization_id = v_org_id
855 AND inventory_item_id = v_inventory_item_id;
856
857 l_prim_uom_code VARCHAR2 (4);
858 l_sec_uom_code VARCHAR2 (4);
859 l_inventory_item_id NUMBER;
860 l_organization_id NUMBER;
861 l_tracking_quantity_ind VARCHAR2(30);
862
863 l_line_tbl line_tab;
864 process_mo_line_err EXCEPTION;
865 /*Bug#6446877 Cursor to fetch the allocation uom for the line */
866 CURSOR get_line_uom IS
867 select dtl_um, inventory_item_id, organization_id
868 from gme_material_details
869 where material_detail_id = p_material_detail_id;
870 l_line_uom VARCHAR2(4);
871 BEGIN
872 IF g_debug <= gme_debug.g_log_procedure THEN
873 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
874 || l_api_name);
875 END IF;
876
877 x_return_status := fnd_api.g_ret_sts_success;
878 -- Bug 9028327 - Fetch some additional columns.
879 OPEN get_line_uom;
880 FETCH get_line_uom INTO l_line_uom, l_inventory_item_id, l_organization_id;
881 CLOSE get_line_uom;
882
883 -- Bug 9028327 - Fetch data from item master table for use later.
884 OPEN cur_item_mst (l_organization_id
885 ,l_inventory_item_id);
886 FETCH cur_item_mst INTO l_prim_uom_code, l_sec_uom_code, l_tracking_quantity_ind ;
887 CLOSE cur_item_mst;
888
889 IF (p_invis_move_line_id IS NOT NULL) THEN
890 l_trolin_tbl (1).operation := inv_globals.g_opr_update;
891 l_trolin_tbl (1).line_id := p_invis_move_line_id;
892 l_trolin_tbl (1).uom_code := l_line_uom;
893
894 IF (p_new_qty IS NOT NULL) THEN
895 l_trolin_tbl (1).quantity := p_new_qty;
896 /*Bug#6446877 assign the fetched uom to the trolin_tbl record */
897
898 -- Bug 9028327 - Add code to update primary and secondary if necessary.
899 IF (l_prim_uom_code <> l_line_uom) THEN
900 l_trolin_tbl (1).primary_quantity := inv_convert.inv_um_convert
901 ( item_id => l_inventory_item_id,
902 lot_number => NULL,
903 organization_id => l_organization_id,
904 PRECISION => NULL,
905 from_quantity => l_trolin_tbl (1).quantity,
906 from_unit => l_line_uom,
907 to_unit => l_prim_uom_code,
908 from_name => NULL,
909 to_name => NULL
910 );
911 ELSE
912 l_trolin_tbl (1).primary_quantity := p_new_qty;
913 END IF;
914
915 IF (l_tracking_quantity_ind = 'PS') THEN
916 IF (l_sec_uom_code = l_line_uom) THEN
917 l_trolin_tbl (1).secondary_quantity := p_new_qty;
918 ELSE
919 l_trolin_tbl (1).secondary_quantity := inv_convert.inv_um_convert
920 ( item_id => l_inventory_item_id,
921 lot_number => NULL,
922 organization_id => l_organization_id,
923 PRECISION => NULL,
924 from_quantity => l_trolin_tbl (1).primary_quantity,
925 from_unit => l_prim_uom_code,
926 to_unit => l_sec_uom_code,
927 from_name => NULL,
928 to_name => NULL
929 );
930 END IF;
931 END IF; -- Bug 9028327
932 END IF;
933
934 IF (p_new_date IS NOT NULL) THEN
935 l_trolin_tbl (1).date_required := p_new_date;
936 END IF;
937
938 ELSE
939 OPEN cur_move_order_lines;
940
941 FETCH cur_move_order_lines
942 BULK COLLECT INTO l_line_tbl;
943
944 CLOSE cur_move_order_lines;
945
946 IF (p_new_qty IS NOT NULL) THEN
947 FOR i IN 1 .. l_line_tbl.COUNT LOOP
948 l_curr_qty := NVL (l_curr_qty, 0) + l_line_tbl (i).quantity;
949 END LOOP;
950
951 --l_diff_qty := l_curr_qty - p_new_qty;
952 --Bug#5078853
953 l_diff_qty := p_new_qty - l_curr_qty;
954
955 IF (l_diff_qty < 0) THEN
956 /*user is trying to decrease the qty in batch*/
957 FOR i IN 1 .. l_line_tbl.COUNT LOOP
958 l_cnt := l_trolin_tbl.COUNT + 1;
959
960 IF (l_diff_qty >= 0) THEN
961 EXIT;
962 END IF;
963
964 IF (l_line_tbl (l_cnt).quantity <= ABS (l_diff_qty) ) THEN
965 l_trolin_tbl (l_cnt).operation :=
966 inv_globals.g_opr_delete;
967 l_trolin_tbl (l_cnt).line_id := l_line_tbl (i).line_id;
968 l_trolin_tbl (l_cnt).header_id :=
969 l_line_tbl (i).header_id;
970 l_diff_qty := l_diff_qty + l_line_tbl (i).quantity;
971 ELSE
972 l_trolin_tbl (l_cnt).operation :=
973 inv_globals.g_opr_update;
974 l_trolin_tbl (l_cnt).line_id := l_line_tbl (i).line_id;
975 l_trolin_tbl (l_cnt).header_id :=
976 l_line_tbl (i).header_id;
977 --Bug#5078853 replaced l_trolin_tbl (i).quantity
978 l_trolin_tbl (l_cnt).quantity :=
979 l_line_tbl (i).quantity + l_diff_qty;
980
981 -- Bug 9028327/9030411 - Add code to update UOM.
982 l_trolin_tbl (l_cnt).uom_code := l_line_uom;
983
984 -- Bug 9028327/9030411 - Add code to update primary and secondary if necessary.
985 IF (l_prim_uom_code <> l_line_uom) THEN
986 l_trolin_tbl (l_cnt).primary_quantity := inv_convert.inv_um_convert
987 ( item_id => l_inventory_item_id,
988 lot_number => NULL,
989 organization_id => l_organization_id,
990 PRECISION => NULL,
991 from_quantity => l_trolin_tbl (l_cnt).quantity,
992 from_unit => l_line_uom,
993 to_unit => l_prim_uom_code,
994 from_name => NULL,
995 to_name => NULL
996 );
997 ELSE
998 l_trolin_tbl (l_cnt).primary_quantity := l_trolin_tbl (l_cnt).quantity;
999 END IF;
1000
1001 IF (l_tracking_quantity_ind = 'PS') THEN
1002 IF (l_sec_uom_code = l_line_uom) THEN
1003 l_trolin_tbl (l_cnt).secondary_quantity := l_trolin_tbl (l_cnt).quantity;
1004 ELSE
1005 l_trolin_tbl (l_cnt).secondary_quantity := inv_convert.inv_um_convert
1006 ( item_id => l_inventory_item_id,
1007 lot_number => NULL,
1008 organization_id => l_organization_id,
1009 PRECISION => NULL,
1010 from_quantity => l_trolin_tbl (l_cnt).primary_quantity,
1011 from_unit => l_prim_uom_code,
1012 to_unit => l_sec_uom_code,
1013 from_name => NULL,
1014 to_name => NULL
1015 );
1016 END IF;
1017 END IF; -- Bug 9028327/9030411
1018
1019 IF (p_new_date IS NOT NULL) THEN
1020 l_trolin_tbl (l_cnt).date_required := p_new_date;
1021 END IF;
1022
1023 l_line_tbl (i).REFERENCE := ' ';
1024 END IF;
1025 END LOOP;
1026 ELSIF (l_diff_qty > 0) THEN
1027 /*user is trying to increase the qty in batch*/
1028 l_trolin_tbl (1).operation := inv_globals.g_opr_update;
1029 l_trolin_tbl (1).line_id := l_line_tbl (1).line_id;
1030 l_trolin_tbl (1).header_id := l_line_tbl (1).header_id;
1031 --Bug#5078853 replaced l_trolin_tbl (i).quantity
1032 l_trolin_tbl (1).quantity :=
1033 l_line_tbl (1).quantity + l_diff_qty;
1034
1035 -- Bug 9028327/9030411 - Add code to update UOM.
1036 l_trolin_tbl (1).uom_code := l_line_uom;
1037
1038 -- Bug 9028327/9030411 - Add code to update UOM, primary and secondary if necessary.
1039 IF (l_prim_uom_code <> l_line_uom) THEN
1040 l_trolin_tbl (1).primary_quantity := inv_convert.inv_um_convert
1041 ( item_id => l_inventory_item_id,
1042 lot_number => NULL,
1043 organization_id => l_organization_id,
1044 PRECISION => NULL,
1045 from_quantity => l_trolin_tbl (1).quantity,
1046 from_unit => l_line_uom,
1047 to_unit => l_prim_uom_code,
1048 from_name => NULL,
1049 to_name => NULL
1050 );
1051 ELSE
1052 l_trolin_tbl (1).primary_quantity := l_trolin_tbl (1).quantity;
1053 END IF;
1054
1055 IF (l_tracking_quantity_ind = 'PS') THEN
1056 IF (l_sec_uom_code = l_line_uom) THEN
1057 l_trolin_tbl (1).secondary_quantity := l_trolin_tbl (1).quantity;
1058 ELSE
1059 l_trolin_tbl (1).secondary_quantity := inv_convert.inv_um_convert
1060 ( item_id => l_inventory_item_id,
1061 lot_number => NULL,
1062 organization_id => l_organization_id,
1063 PRECISION => NULL,
1064 from_quantity => l_trolin_tbl (1).primary_quantity,
1065 from_unit => l_prim_uom_code,
1066 to_unit => l_sec_uom_code,
1067 from_name => NULL,
1068 to_name => NULL
1069 );
1070 END IF;
1071 END IF; -- Bug 9028327/9030411
1072
1073 IF (p_new_date IS NOT NULL) THEN
1074 l_trolin_tbl (1).date_required := p_new_date;
1075 END IF;
1076
1077 l_line_tbl (1).REFERENCE := ' ';
1078 END IF;
1079 END IF;
1080
1081 IF (p_new_date IS NOT NULL) THEN
1082 FOR i IN 1 .. l_line_tbl.COUNT LOOP
1083 IF (nvl(l_line_tbl (i).REFERENCE,' ') <> ' ') THEN
1084 l_cnt := l_trolin_tbl.COUNT + 1;
1085 l_trolin_tbl (l_cnt).operation := inv_globals.g_opr_update;
1086 l_trolin_tbl (l_cnt).line_id := l_line_tbl (i).line_id;
1087 l_trolin_tbl (l_cnt).header_id := l_line_tbl (i).header_id;
1088 l_trolin_tbl (l_cnt).date_required := p_new_date;
1089 END IF;
1090 END LOOP;
1091 END IF;
1092 END IF;
1093
1094 IF (g_debug IS NOT NULL) THEN
1095 gme_debug.put_line
1096 ( 'Calling inv_move_order_pub.process_move_order_line in '
1097 || l_api_name);
1098 END IF;
1099
1100 inv_move_order_pub.process_move_order_line
1101 (p_api_version_number => 1.0
1102 ,p_init_msg_list => fnd_api.g_false
1103 ,p_return_values => fnd_api.g_false
1104 ,p_commit => fnd_api.g_false
1105 ,x_return_status => l_return_status
1106 ,x_msg_count => l_msg_count
1107 ,x_msg_data => l_msg_data
1108 ,p_trolin_tbl => l_trolin_tbl
1109 ,p_trolin_old_tbl => l_old_trolin_tbl
1110 ,x_trolin_tbl => l_out_trolin_tbl);
1111
1112 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1113 RAISE process_mo_line_err;
1114 END IF;
1115
1116 IF g_debug <= gme_debug.g_log_procedure THEN
1117 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1118 END IF;
1119 EXCEPTION
1120 WHEN process_mo_line_err THEN
1121 IF (g_debug IS NOT NULL) THEN
1122 gme_debug.put_line
1123 ( 'inv_move_order_pub.process_move_order_line returns '
1124 || l_return_status);
1125 gme_debug.put_line ('error message is ' || l_msg_data);
1126 END IF;
1127
1128 x_return_status := l_return_status;
1129 WHEN OTHERS THEN
1130 IF g_debug <= gme_debug.g_log_unexpected THEN
1131 gme_debug.put_line ( 'When others exception in '
1132 || g_pkg_name
1133 || '.'
1134 || l_api_name
1135 || ' Error is '
1136 || SQLERRM);
1137 END IF;
1138
1139 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1140 x_return_status := fnd_api.g_ret_sts_unexp_error;
1141 END update_move_order_lines;
1142
1143 FUNCTION pending_move_orders_exist (
1144 p_organization_id IN NUMBER
1145 ,p_batch_id IN NUMBER
1146 ,p_material_detail_id IN NUMBER)
1147 RETURN BOOLEAN
1148 IS
1149 l_api_name CONSTANT VARCHAR2 (30) := 'pending_move_orders_exist';
1150 l_exists BOOLEAN := FALSE;
1151 l_dummy NUMBER;
1152
1153 -- Namit S. Bug4917629 Modified query to not select from sysdual.
1154 -- This is for perf reasons to reduce the sharable memory.
1155
1156 CURSOR cur_move_order_lines
1157 IS
1158 SELECT 1
1159 FROM mtl_txn_request_lines l, mtl_txn_request_headers h
1160 WHERE l.txn_source_id = p_batch_id
1161 AND l.txn_source_line_id = p_material_detail_id
1162 AND l.organization_id = p_organization_id
1163 AND l.line_status NOT IN (5, 6)
1164 AND h.header_id = l.header_id
1165 AND h.move_order_type = gme_common_pvt.g_move_order_type
1166 AND ROWNUM = 1;
1167 BEGIN
1168 IF g_debug <= gme_debug.g_log_procedure THEN
1169 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1170 || l_api_name);
1171 END IF;
1172
1173 OPEN cur_move_order_lines;
1174
1175 FETCH cur_move_order_lines
1176 INTO l_dummy;
1177
1178 IF (cur_move_order_lines%FOUND) THEN
1179 l_exists := TRUE;
1180 END IF;
1181
1182 CLOSE cur_move_order_lines;
1183
1184 IF g_debug <= gme_debug.g_log_procedure THEN
1185 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1186 END IF;
1187
1188 RETURN l_exists;
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 IF g_debug <= gme_debug.g_log_unexpected THEN
1192 gme_debug.put_line ( 'When others exception in '
1193 || g_pkg_name
1194 || '.'
1195 || l_api_name
1196 || ' Error is '
1197 || SQLERRM);
1198 END IF;
1199
1200 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1201 RETURN FALSE;
1202 END pending_move_orders_exist;
1203
1204 PROCEDURE get_pending_move_order_qty (
1205 p_mtl_dtl_rec IN gme_material_details%ROWTYPE
1206 ,x_pending_qty OUT NOCOPY NUMBER
1207 ,x_return_status OUT NOCOPY VARCHAR2)
1208 IS
1209 l_api_name CONSTANT VARCHAR2 (30) := 'get_pending_move_order_qty';
1210 l_return_status VARCHAR2 (1);
1211 l_primary_uom VARCHAR2 (3);
1212 l_secondary_uom VARCHAR2 (3);
1213 l_from_uom VARCHAR2 (3);
1214 l_to_uom VARCHAR2 (3);
1215 l_item_no VARCHAR2 (2000);
1216 l_temp_qty NUMBER;
1217 l_line_tbl gme_common_pvt.mo_lines_tab;
1218
1219 CURSOR cur_item_uoms (v_org_id NUMBER, v_inventory_item_id NUMBER)
1220 IS
1221 SELECT primary_uom_code, secondary_uom_code, concatenated_segments
1222 FROM mtl_system_items_kfv
1223 WHERE organization_id = v_org_id
1224 AND inventory_item_id = v_inventory_item_id;
1225
1226 get_mo_line_err EXCEPTION;
1227 uom_conv_error EXCEPTION;
1228 BEGIN
1229 IF g_debug <= gme_debug.g_log_procedure THEN
1230 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1231 || l_api_name);
1232 END IF;
1233
1234 x_return_status := fnd_api.g_ret_sts_success;
1235 gme_move_orders_pvt.get_move_order_lines
1236 (p_organization_id => p_mtl_dtl_rec.organization_id
1237 ,p_batch_id => p_mtl_dtl_rec.batch_id
1238 ,p_material_detail_id => p_mtl_dtl_rec.material_detail_id
1239 ,x_mo_line_tbl => l_line_tbl
1240 ,x_return_status => l_return_status);
1241
1242 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1243 RAISE get_mo_line_err;
1244 END IF;
1245
1246 FOR i IN 1 .. l_line_tbl.COUNT LOOP
1247 IF (p_mtl_dtl_rec.dtl_um = l_line_tbl (i).uom_code) THEN
1248 -- Bug 13076579 Use detailed qty.
1249 -- l_temp_qty := l_line_tbl (i).quantity;
1250 l_temp_qty := l_line_tbl (i).quantity_detailed;
1251 ELSE
1252 OPEN cur_item_uoms (p_mtl_dtl_rec.organization_id
1253 ,p_mtl_dtl_rec.inventory_item_id);
1254
1255 FETCH cur_item_uoms
1256 INTO l_primary_uom, l_secondary_uom, l_item_no;
1257
1258 CLOSE cur_item_uoms;
1259
1260 IF (p_mtl_dtl_rec.dtl_um = l_secondary_uom) THEN
1261 l_temp_qty := l_line_tbl (i).secondary_quantity;
1262 ELSIF (p_mtl_dtl_rec.dtl_um = l_primary_uom) THEN
1263 l_temp_qty := l_line_tbl (i).primary_quantity;
1264 ELSE
1265 -- Bug 13076579 Use detailed qty.
1266 l_temp_qty :=
1267 inv_convert.inv_um_convert
1268 (item_id => l_line_tbl (i).inventory_item_id
1269 ,PRECISION => gme_common_pvt.g_precision
1270 -- ,from_quantity => l_line_tbl (i).quantity_detailed
1271 ,from_quantity => l_line_tbl (i).quantity
1272 ,from_unit => l_line_tbl (i).uom_code
1273 ,to_unit => p_mtl_dtl_rec.dtl_um
1274 ,from_name => NULL
1275 ,to_name => NULL);
1276
1277 IF (l_temp_qty < 0) THEN
1278 l_from_uom := l_line_tbl (i).uom_code;
1279 l_to_uom := p_mtl_dtl_rec.dtl_um;
1280 RAISE uom_conv_error;
1281 END IF;
1282 END IF;
1283 END IF;
1284
1285 x_pending_qty := NVL (x_pending_qty, 0) + l_temp_qty;
1286 END LOOP;
1287
1288 IF g_debug <= gme_debug.g_log_procedure THEN
1289 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1290 END IF;
1291 EXCEPTION
1292 WHEN uom_conv_error THEN
1293 x_return_status := fnd_api.g_ret_sts_error;
1294 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1295 fnd_message.set_token ('ITEM_NO', l_item_no);
1296 fnd_message.set_token ('FROM_UOM', l_from_uom);
1297 fnd_message.set_token ('TO_UOM', l_to_uom);
1298 fnd_msg_pub.ADD;
1299 WHEN get_mo_line_err THEN
1300 x_return_status := l_return_status;
1301 WHEN OTHERS THEN
1302 IF g_debug <= gme_debug.g_log_unexpected THEN
1303 gme_debug.put_line ( 'When others exception in '
1304 || g_pkg_name
1305 || '.'
1306 || l_api_name
1307 || ' Error is '
1308 || SQLERRM);
1309 END IF;
1310
1311 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1312 x_return_status := fnd_api.g_ret_sts_unexp_error;
1313 END get_pending_move_order_qty;
1314
1315 PROCEDURE delete_batch_move_orders (
1316 p_organization_id IN NUMBER
1317 ,p_batch_id IN NUMBER
1318 ,p_delete_invis IN VARCHAR2 := 'F'
1319 ,x_return_status OUT NOCOPY VARCHAR2)
1320 IS
1321 l_api_name CONSTANT VARCHAR2 (30) := 'delete_batch_move_orders';
1322
1323 CURSOR cur_batch_lines
1324 IS
1325 SELECT h.batch_id, h.move_order_header_id, d.material_detail_id
1326 ,d.move_order_line_id
1327 FROM gme_batch_header h, gme_material_details d
1328 WHERE h.organization_id = p_organization_id
1329 AND h.batch_id = p_batch_id
1330 AND d.batch_id = h.batch_id
1331 AND d.line_type = gme_common_pvt.g_line_type_ing;
1332
1333 TYPE lines_tab IS TABLE OF cur_batch_lines%ROWTYPE
1334 INDEX BY BINARY_INTEGER;
1335
1336 l_lines_tbl lines_tab;
1337 l_invis_line_id NUMBER;
1338 l_invis_header_id NUMBER;
1339 l_return_status VARCHAR2 (1);
1340 del_mo_lines_err EXCEPTION;
1341 BEGIN
1342 IF g_debug <= gme_debug.g_log_procedure THEN
1343 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1344 || l_api_name);
1345 END IF;
1346
1347 x_return_status := fnd_api.g_ret_sts_success;
1348
1349 OPEN cur_batch_lines;
1350
1351 FETCH cur_batch_lines
1352 BULK COLLECT INTO l_lines_tbl;
1353
1354 CLOSE cur_batch_lines;
1355
1356 FOR i IN 1 .. l_lines_tbl.COUNT LOOP
1357 IF (p_delete_invis = fnd_api.g_true) THEN
1358 l_invis_line_id := l_lines_tbl (i).move_order_line_id;
1359 l_invis_header_id := l_lines_tbl (i).move_order_header_id;
1360 END IF;
1361
1362 delete_move_order_lines
1363 (p_organization_id => p_organization_id
1364 ,p_batch_id => l_lines_tbl (i).batch_id
1365 ,p_material_detail_id => l_lines_tbl (i).material_detail_id
1366 ,p_invis_move_line_id => l_invis_line_id
1367 ,p_invis_move_header_id => l_invis_header_id
1368 ,x_return_status => l_return_status);
1369
1370 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1371 RAISE del_mo_lines_err;
1372 END IF;
1373 END LOOP;
1374
1375 IF g_debug <= gme_debug.g_log_procedure THEN
1376 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1377 END IF;
1378 EXCEPTION
1379 WHEN del_mo_lines_err THEN
1380 x_return_status := l_return_status;
1381 WHEN OTHERS THEN
1382 IF g_debug <= gme_debug.g_log_unexpected THEN
1383 gme_debug.put_line ( 'When others exception in '
1384 || g_pkg_name
1385 || '.'
1386 || l_api_name
1387 || ' Error is '
1388 || SQLERRM);
1389 END IF;
1390
1391 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1392 x_return_status := fnd_api.g_ret_sts_unexp_error;
1393 END delete_batch_move_orders;
1394 END gme_move_orders_pvt;