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