[Home] [Help]
PACKAGE BODY: APPS.GME_MAKE_TO_ORDER_PVT
Source
1 PACKAGE BODY GME_MAKE_TO_ORDER_PVT AS
2 /* $Header: GMECRBTB.pls 120.5 2008/02/28 21:17:48 srpuri noship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMECRBTB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private utilities relating to OPM |
13 | reservation. |
14 | |
15 | |
16 | HISTORY |
17 | Dec, 2007 Srinivasulu Puri Created |
18 +=========================================================================+
19 API Name : GME_MAKE_TO_ORDER_PVT
20 Type : Private
21 Function : This package contains private procedures used to support
22 OPM make to order functionality.
23 The user defines a make to order rule and the conditions under
24 which it will be deployed. When an order line qualifies for
25 make to order processing, a GME Batch/FPO is created and then
26 the sales order line is reserved to it.
27 Pre-reqs : Set up of Make to Order Rules
28 Parameters: Per function
29
30 Current Vers : 1.0
31
32 */
33 g_debug VARCHAR2 (5) := NVL(fnd_profile.VALUE ('AFLOG_LEVEL'),-1);
34
35 PROCEDURE create_batch_for_order_line
36 (
37 -- errbuf OUT NOCOPY VARCHAR2
38 -- retcode OUT NOCOPY VARCHAR2
39 p_api_version IN NUMBER := 1.0
40 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
41 ,p_commit IN VARCHAR2 := fnd_api.g_false
42 ,p_so_line_id IN NUMBER
43 ) IS
44
45 -- Standard constants to be used to check for call compatibility.
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_api_name CONSTANT VARCHAR2(30):= 'create_batch_for_order_line';
48
49 -- Local Variables.
50 l_msg_count NUMBER :=0;
51 l_msg_data VARCHAR2(2000);
52 l_message VARCHAR2(2000);
53 i NUMBER;
54 j NUMBER;
55 l_return_status VARCHAR2(1);
56 l_batch_qty NUMBER;
57 l_batch_qty2 NUMBER;
58 l_so_line_id NUMBER;
59 l_res_qty_so_um NUMBER;
60
61 l_batch_header_rec GME_BATCH_HEADER%ROWTYPE;
62 l_out_batch_header_rec GME_BATCH_HEADER%ROWTYPE;
63 l_material_detail_rec GME_MATERIAL_DETAILS%ROWTYPE;
64 l_so_line_rec OE_ORDER_LINES_ALL%ROWTYPE;
65 l_item_rec MTL_SYSTEM_ITEMS_B%ROWTYPE;
66 l_so_batch_rsv_rec GML_BATCH_OM_UTIL.gme_om_reservation_rec;
67 l_pending_product_lots_rec GME_PENDING_PRODUCT_LOTS%ROWTYPE;
68 l_out_pending_product_lots_rec GME_PENDING_PRODUCT_LOTS%ROWTYPE;
69 l_rsv_rec INV_RESERVATION_GLOBAL.mtl_reservation_rec_type;
70 l_in_serial_num INV_RESERVATION_GLOBAL.serial_number_tbl_type;
71 l_out_serial_num INV_RESERVATION_GLOBAL.serial_number_tbl_type;
72 l_exception_material_tbl GME_COMMON_PVT.exceptions_tab;
73 l_rsv_tbl GME_COMMON_PVT.reservations_tab;
74 l_rsv_row_rec MTL_RESERVATIONS%ROWTYPE;
75 l_in_assignments_rec GME_MTO_RULE_ASSIGNMENTS%ROWTYPE;
76 l_mto_rules_rec GME_MTO_RULES%ROWTYPE;
77 l_mto_assignments_rec GME_MTO_RULE_ASSIGNMENTS%ROWTYPE;
78
79 l_item_id NUMBER;
80 l_primary_uom_code VARCHAR2(3);
81 l_total_primary_rsv_qty NUMBER;
82 l_ret_status NUMBER;
83 l_item_no VARCHAR2(80);
84 l_lot_no VARCHAR2(80) := NULL;
85 ll_lot_no VARCHAR2(80) := NULL;
86 l_firmed_ind NUMBER;
87 l_user_id NUMBER;
88 l_reserved_qty NUMBER;
89 l_reserved_qty2 NUMBER;
90 l_so_line_no NUMBER;
91 l_order_no NUMBER;
92
93
94 x_msg_count NUMBER :=0;
95 x_msg_data VARCHAR2(2000);
96 x_return_status VARCHAR2(1);
97
98 l_ship_from_org_id NUMBER;
99 l_inventory_item_id NUMBER;
100 l_temp BOOLEAN;
101
102 l_quantity_reserved NUMBER;
103 l_reservation_id NUMBER;
104 l_salesorder_id NUMBER;
105 l_count NUMBER:=0;
106 l_tree_id INTEGER;
107
108 l_qoh NUMBER;
109 l_rqoh NUMBER;
110 l_qr NUMBER;
111 l_qs NUMBER;
112 l_att NUMBER;
113 l_atr NUMBER;
114 l_sqoh NUMBER;
115 l_srqoh NUMBER;
116 l_sqr NUMBER;
117 l_sqs NUMBER;
118 l_satt NUMBER;
119 l_satr NUMBER;
120 l_lpn_id NUMBER;
121 l_atr_qty_in_so_um NUMBER := 0;
122
123 l_is_revision_control BOOLEAN := FALSE;
124 l_is_lot_control BOOLEAN := FALSE;
125 l_is_serial_control BOOLEAN := FALSE;
126
127 CURSOR cur_inv_reservations (p_line_id IN NUMBER) IS
128 SELECT mr.*
129 FROM mtl_reservations mr
130 WHERE demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
131 AND supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_inv
132 AND demand_source_line_id = p_line_id
133 ORDER BY mr.reservation_id;
134
135 CURSOR cur_prod_reservations (p_line_id IN NUMBER) IS
136 SELECT count(1)
137 FROM mtl_reservations mr
138 WHERE demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
139 AND supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_wip
140 AND demand_source_line_id = p_line_id
141 ORDER BY mr.reservation_id;
142
143 CURSOR cur_item(p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER) IS
144 SELECT *
145 FROM mtl_system_items_b m
146 WHERE m.inventory_item_id = p_inventory_item_id
147 AND m.organization_id = p_organization_id;
148
149 CURSOR get_so_line_rec(p_so_line_id IN NUMBER) IS
150 SELECT *
151 FROM oe_order_lines_all
152 WHERE line_id = p_so_line_id;
153
154 CURSOR get_batch_line(p_batch_id IN NUMBER, p_inventory_item_id IN NUMBER) IS
155 SELECT *
156 FROM gme_material_details
157 WHERE batch_id = p_batch_id
158 AND inventory_item_id = p_inventory_item_id
159 AND line_type = gme_common_pvt.g_line_type_prod;
160
161
162 CURSOR Get_order_info(p_so_line_id IN NUMBER) IS
163 SELECT ol.line_number, mtl.segment1,
164 oh.order_number
165 FROM oe_order_headers_all oh
166 ,oe_order_lines_all ol
167 ,oe_transaction_types_all ot
168 ,mtl_system_items mtl
169 WHERE ol.line_id = p_so_line_id
170 and ol.header_id = oh.header_id
171 and oh.order_type_id = ot.transaction_type_id
172 and mtl.inventory_item_id = ol.inventory_item_id;
173
174 BEGIN
175 l_so_line_id := p_so_line_id;
176 /*Int variables
177 =========================================*/
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 IF (g_debug <= gme_debug.g_log_statement) THEN
181 gme_debug.log_initialize ('OPM_MaketoOrder');
182 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' ENTER API ');
183 -- gme_debug.log_initialize ('OPM_MaketoOrder');
184 END IF;
185
186 /* Standard begin of API savepoint
187 ===========================================*/
188 FND_FILE.Put_Line(FND_FILE.LOG, 'start OPM processing ');
189 SAVEPOINT create_batch_for_so_line_SP;
190
191 /*Standard call to check for call compatibility.
192 ==============================================*/
193 IF NOT FND_API.compatible_api_call (
194 l_api_version,
195 p_api_version,
196 l_api_name,
197 g_pkg_name)
198 THEN
199 IF (g_debug <= gme_debug.g_log_statement) THEN
200 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' compatability call failure ');
201 END IF;
202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
203 END IF;
204
205 FND_FILE.Put_Line(FND_FILE.LOG, ' compatible OK ');
206 /* Check p_init_msg_list
207 =========================================*/
208 IF FND_API.to_boolean(p_init_msg_list)
209 THEN
210 FND_MSG_PUB.initialize;
211 END IF;
212
213 FND_FILE.Put_Line(FND_FILE.LOG, 'After Initialize ');
214 IF (g_debug <= gme_debug.g_log_statement) THEN
215 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' begin processing ');
216 END IF;
217
218 IF( NVL(p_so_line_id,0) = 0 ) THEN
219 IF (g_debug <= gme_debug.g_log_statement) THEN
220 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' sales order line id parameter is missing ');
221 END IF;
222 FND_MESSAGE.Set_Name('GMI','Missing');
223 FND_MESSAGE.Set_Token('MISSING', 'so_line_id');
224 FND_MSG_PUB.Add;
225 RAISE FND_API.G_EXC_ERROR;
226 END IF;
227 /* ============ IF this line is already reserved to production supply do not process further =========== */
228 IF (g_debug <= gme_debug.g_log_statement) THEN
229 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Check for existing reservations against GME PROD supply ');
230 END IF;
231 OPEN cur_prod_reservations(p_so_line_id);
232 FETCH cur_prod_reservations INTO l_count;
233 IF (cur_prod_reservations%NOTFOUND) or (l_count=0) THEN
234 IF g_debug <= gme_debug.g_log_procedure THEN
235 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' No existing reservations against PRODUCTION supply');
236 END IF;
237 CLOSE cur_prod_reservations;
238 ELSE
239 IF g_debug <= gme_debug.g_log_procedure THEN
240 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Reservations already against PRODUCTION supply so terminate here');
241 END IF;
242 CLOSE cur_prod_reservations;
243 RETURN;
244 END IF;
245
246 /* ============== Start of Order Line Processing ====================*/
247 IF (g_debug <= gme_debug.g_log_statement) THEN
248 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'Retrieve the sales order line for line_id '||p_so_line_id);
249 END IF;
250
251 OPEN get_so_line_rec(p_so_line_id);
252 FETCH get_so_line_rec INTO l_so_line_rec;
253
254 IF(get_so_line_rec%NOTFOUND) THEN
255 CLOSE get_so_line_rec;
256 IF (g_debug <= gme_debug.g_log_statement) THEN
257 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'RETURNING - sales order not found for for line_id '||p_so_line_id);
258 END IF;
259 RAISE FND_API.G_EXC_ERROR;
260 END IF;
261 CLOSE get_so_line_rec;
262
263 /* ============== Establish Item Characteristics ====================*/
264 IF (g_debug <= gme_debug.g_log_statement) THEN
265 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Retrieve Item for organization_id '||l_so_line_rec.ship_from_org_id);
266 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Retrieve Item for inventory_item_id '||l_so_line_rec.inventory_item_id);
267 END IF;
268 OPEN cur_item (l_so_line_rec.ship_from_org_id, l_so_line_rec.inventory_item_id);
269 FETCH cur_item INTO l_item_rec;
270 IF(cur_item%NOTFOUND) THEN
271 CLOSE cur_item;
272 IF (g_debug <= gme_debug.g_log_statement) THEN
273 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'Failure to retrieve inventory item ');
274 END IF;
275 RAISE FND_API.G_EXC_ERROR;
276 END IF;
277 CLOSE cur_item;
278
279 /* ============== Determine whether Make to Order Rule exists ===============*/
280 IF (g_debug <= gme_debug.g_log_statement) THEN
281 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Determine whether make to order rule exists ');
282 END IF;
283
284 l_in_assignments_rec.organization_id := l_so_line_rec.ship_from_org_id;
285 l_in_assignments_rec.inventory_item_id := l_so_line_rec.inventory_item_id;
286 l_in_assignments_rec.item_type := l_item_rec.item_type;
287 l_in_assignments_rec.customer_id := l_so_line_rec.sold_to_org_id;
288 l_in_assignments_rec.site_use_id := l_so_line_rec.ship_to_org_id;
289
290 GME_MAKE_TO_ORDER_PVT.retrieve_rule(
291 p_mto_assignments_rec => l_in_assignments_rec
292 ,x_mto_rules_rec => l_mto_rules_rec
293 ,x_mto_assignments_rec => l_mto_assignments_rec
294 ,x_return_status => l_return_status
295 ,x_msg_data => l_msg_data
296 ,x_msg_count => l_msg_count);
297
298 IF (g_debug <= gme_debug.g_log_statement) THEN
299 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Return status from Retrieve_Rule is '||l_return_status);
300 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Rule ID to work with is '||l_mto_assignments_rec.rule_id);
301 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Rule being honored is '||l_mto_rules_rec.rule_name);
302 END IF;
303
304 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
305 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Failure to Get_Rule so terminate processing for line '||p_so_line_id);
306 FND_MESSAGE.Set_Name('GMI','Missing');
307 FND_MESSAGE.Set_Token('MISSING', 'rule_id');
308 FND_MSG_PUB.Add;
309 RAISE FND_API.G_EXC_ERROR;
310 END IF;
311
312 IF (NVL(l_mto_rules_rec.rule_id,0) = 0) THEN
313 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'No MAKE to ORDER rule exists to cover order line '||p_so_line_id);
314 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'No Further Processing Required for order line '||p_so_line_id);
315 RETURN;
316 END IF;
317
318 /* ============== Start Processing Make to Order Rule ===============*/
319 IF (g_debug <= gme_debug.g_log_statement) THEN
320 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Start processing rule definition for '||l_mto_rules_rec.rule_name);
321 END IF;
322
323 /* ================== Assess the rule for checking available inventory =================*/
324 IF (g_debug <= gme_debug.g_log_statement) THEN
325 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Check Availability set to '||l_mto_rules_rec.check_availability);
326 END IF;
327 IF NVL(l_mto_rules_rec.check_availability,'N') = 'N' THEN
328 IF (g_debug <= gme_debug.g_log_statement) THEN
329 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' No need for Availabilty check create for full order qty');
330 END IF;
331 /* Create a batch for the full order line quantity regardless of onhand availability
332 - Relieve any reservations against inventory; must not duplicate demand
333 ==================================================================================*/
334 l_batch_qty := l_so_line_rec.ordered_quantity;
335 l_batch_qty2 := l_so_line_rec.ordered_quantity2;
336 IF (g_debug <= gme_debug.g_log_statement) THEN
337 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch quantity needed is '||l_batch_qty);
338 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Secondary qty needed is '||l_batch_qty2);
339 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Retrieve any existing inv reservations ');
340 END IF;
341 OPEN cur_inv_reservations (l_so_line_id);
342
343 FETCH cur_inv_reservations
344 BULK COLLECT INTO l_rsv_tbl;
345 CLOSE cur_inv_reservations;
346
347 l_count := 1;
348 IF (g_debug <= gme_debug.g_log_statement) THEN
349 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Loop thru existing reservations for'||l_rsv_tbl.COUNT);
350 END IF;
351 WHILE l_count <= l_rsv_tbl.COUNT LOOP
352 l_rsv_row_rec := l_rsv_tbl(l_count);
353 /* Relieve any reservations against INV */
354 IF (g_debug <= gme_debug.g_log_statement) THEN
355 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Relieve Reservation here for ID '||l_rsv_row_rec.reservation_id);
356 END IF;
357 gme_reservations_pvt.relieve_reservation
358 (p_reservation_id => l_rsv_row_rec.reservation_id
359 ,p_prim_relieve_qty => l_rsv_row_rec.primary_reservation_quantity
360 ,x_return_status => x_return_status);
361
362 IF g_debug <= gme_debug.g_log_statement THEN
363 gme_debug.put_line
364 ( g_pkg_name
365 || '.'
366 || l_api_name
367 || ' Return status from relieve_reservation is '
368 || x_return_status);
369 END IF;
370
371 IF x_return_status <> fnd_api.g_ret_sts_success THEN
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374 l_count := l_count + 1;
375 END LOOP;
376 END IF;
377
378 /* Deal with cases where check on availability is required
379 ========================================================= */
380 IF NVL(l_mto_rules_rec.check_availability,'N') = 'Y' THEN
381 IF (g_debug <= gme_debug.g_log_statement) THEN
382 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' NEED to check Inventory Availabilty ahead of creating batch');
383 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' start point is the order qty ' ||l_so_line_rec.ordered_quantity);
384 END IF;
385 /* Find out how much quantity needs to be manufactured to meet inventory shortfall */
386 /* step 1 - what is the current unreserved quantity
387 =======================================================*/
388 l_batch_qty := l_so_line_rec.ordered_quantity;
389 l_batch_qty2 := l_so_line_rec.ordered_quantity2;
390
391 begin
392 SELECT sum(mr.primary_reservation_quantity),primary_uom_code
393 into l_total_primary_rsv_qty, l_primary_uom_code
394 FROM mtl_reservations mr
395 WHERE demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
396 AND supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_inv
397 AND demand_source_line_id = l_so_line_rec.line_id
398 GROUP BY mr.primary_uom_code;
399 /* Deduct the reserved quantity from the ordered_quantity
400 * ========================================================= */
401 IF l_so_line_rec.order_quantity_uom = l_primary_uom_code THEN
402 l_batch_qty := l_so_line_rec.ordered_quantity - l_total_primary_rsv_qty;
403 ELSE
404 /* Align the UOM to match the order line */
405 l_res_qty_so_um :=
406 inv_convert.inv_um_convert(l_so_line_rec.inventory_item_id,
407 null,
408 l_so_line_rec.ship_from_org_id,
409 5,
410 l_total_primary_rsv_qty,
411 l_primary_uom_code,
412 l_so_line_rec.order_quantity_uom,
413 null,
414 null);
415 l_batch_qty := l_so_line_rec.ordered_quantity - l_res_qty_so_um;
416 END IF;
417 exception when no_data_found then
418 null;
419 end;
420
421 IF (g_debug <= gme_debug.g_log_statement) THEN
422 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Deduct qty already reserved, this is '||l_total_primary_rsv_qty);
423 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Now batch qty required stands at '||l_batch_qty);
424 END IF;
425 IF (l_batch_qty <= 0) THEN
426 IF (g_debug <= gme_debug.g_log_statement) THEN
427 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Full Order Qty already reserved to INV');
428 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' No need for batch creation Return here');
429 END IF;
430 RETURN;
431 END IF;
432
433
434 /* step 2 - Assess the available to reserve. Create batch for any INV defecit
435 ==============================================================================*/
436 IF l_item_rec.lot_control_code > 1 THEN
437 l_is_lot_control := TRUE;
438 END IF;
439
440 -- set controls for locator and serial
441 IF (g_debug <= gme_debug.g_log_statement) THEN
442 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Need to assess ATR level so invoke create tree');
443 END IF;
444
445 inv_quantity_tree_pvt.create_tree
446 (
447 p_api_version_number => 1.0
448 , p_init_msg_lst => fnd_api.g_true
449 , x_return_status => l_return_status
450 , x_msg_count => x_msg_count
451 , x_msg_data => x_msg_data
452 , p_organization_id => l_so_line_rec.ship_from_org_id
453 , p_inventory_item_id => l_so_line_rec.inventory_item_id
454 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
455 , p_is_revision_control => l_is_revision_control
456 , p_is_lot_control => l_is_lot_control
457 , p_is_serial_control => l_is_serial_control
458 , p_asset_sub_only => FALSE
459 , p_include_suggestion => TRUE
460 , p_demand_source_type_id => inv_reservation_global.g_source_type_oe
461 , p_demand_source_header_id => l_so_line_rec.header_id
462 , p_demand_source_line_id => l_so_line_rec.line_id
463 , p_demand_source_name => NULL
464 , p_demand_source_delivery => NULL
465 , p_lot_expiration_date => SYSDATE
466 , x_tree_id => l_tree_id
467 );
468
469 IF (g_debug <= gme_debug.g_log_statement) THEN
470 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' After create tree status returned is '||l_return_status);
471 END IF;
472
473 IF l_return_status = fnd_api.g_ret_sts_error THEN
474 RAISE fnd_api.g_exc_error;
475 END IF;
476
477 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
478 RAISE fnd_api.g_exc_unexpected_error;
479 END IF;
480
481 IF (g_debug <= gme_debug.g_log_statement) THEN
482 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Query tree for ATR next ');
483 END IF;
484 inv_quantity_tree_pvt.query_tree(
485 p_api_version_number => 1.0
486 , p_init_msg_lst => fnd_api.g_true
487 , x_return_status => l_return_status
488 , x_msg_count => l_msg_count
489 , x_msg_data => l_msg_data
490 , p_tree_id => l_tree_id
491 , p_revision => NULL
492 , p_lot_number => NULL
493 , p_subinventory_code => NULL
494 , p_locator_id => NULL
495 , x_qoh => l_qoh
496 , x_rqoh => l_rqoh
497 , x_qr => l_qr
498 , x_qs => l_qs
499 , x_att => l_att
500 , x_atr => l_atr
501 , x_sqoh => l_sqoh
502 , x_srqoh => l_srqoh
503 , x_sqr => l_sqr
504 , x_sqs => l_sqs
505 , x_satt => l_satt
506 , x_satr => l_satr
507 , p_lpn_id => l_lpn_id
508 );
509
510 IF (g_debug <= gme_debug.g_log_statement) THEN
511 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' After Query tree return status is '||l_return_status);
512 END IF;
513 IF l_return_status = fnd_api.g_ret_sts_error THEN
514 RAISE fnd_api.g_exc_error;
515 END IF;
516
517 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
518 RAISE fnd_api.g_exc_unexpected_error;
519 END IF;
520
521 IF (g_debug <= gme_debug.g_log_statement) THEN
522 gme_debug.put_line('l_atr ' || l_atr);
523 gme_debug.put_line('l_att ' || l_att);
524 gme_debug.put_line('l_qoh ' || l_qoh);
525 gme_debug.put_line('l_rqoh ' || l_rqoh);
526 gme_debug.put_line('l_qr ' || l_qr);
527 gme_debug.put_line('l_qs ' || l_qs);
528 gme_debug.put_line('l_satr ' || l_satr);
529 gme_debug.put_line('l_satt ' || l_satt);
530 gme_debug.put_line('l_sqoh ' || l_sqoh);
531 gme_debug.put_line('l_srqoh ' || l_srqoh);
532 gme_debug.put_line('l_sqr ' || l_sqr);
533 gme_debug.put_line('l_sqs ' || l_sqs);
534 END IF;
535 /* Deduct any ATR from the manufacture qty */
536 /* Convert atr quantity into Orderline UOM */
537 IF l_so_line_rec.order_quantity_uom <> l_primary_uom_code THEN
538 l_atr_qty_in_so_um := inv_convert.inv_um_convert(l_so_line_rec.inventory_item_id,
539 null,
540 l_so_line_rec.ship_from_org_id,
541 5,
542 l_atr,
543 l_primary_uom_code,
544 l_so_line_rec.order_quantity_uom,
545 null,
546 null);
547 l_atr := l_atr_qty_in_so_um;
548 END IF;
549 IF l_atr >= l_batch_qty THEN
550 /* Stock is available to reserve to meet the sales quantity so no further
551 * action required */
552 gme_debug.put_line('Stock is available to meet the demand so batch creation not necessary ');
553 END IF;
554 l_batch_qty := l_batch_qty - l_atr;
555 END IF; -- End of check availability
556
557 FND_FILE.Put_Line(FND_FILE.LOG, 'END of section for MUST check availability ');
558 FND_FILE.Put_Line(FND_FILE.LOG, ' batch qty now stands at '||l_batch_qty );
559 IF (g_debug <= gme_debug.g_log_statement) THEN
560 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' End of availability checking and batch qty now '||l_batch_qty);
561 END IF;
562 /* Now set up data and invoke create_batch
563 ==========================================*/
564 IF (l_batch_qty <= 0) THEN
565 IF (g_debug <= gme_debug.g_log_statement) THEN
566 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Entire requested qty aviaable in inventory so Return now');
567 END IF;
568 RETURN;
569 END IF;
570
571 IF(l_mto_rules_rec. firmed_ind = 'Y') THEN
572 l_firmed_ind := 1;
573 ELSE
574 l_firmed_ind := NULL;
575 END IF;
576
577 /* Now populate the batch record so you can call Create Batch API */
578
579 gme_debug.put_line('populate the batch record in readiness for create_batch API');
580 l_batch_header_rec.batch_type := l_mto_rules_rec.batch_type_to_create;
581 l_batch_header_rec.organization_id := l_so_line_rec.ship_from_org_id;
582 l_batch_header_rec.plan_start_date := SYSDATE;
583 l_batch_header_rec.update_inventory_ind := 'Y';
584 l_batch_header_rec.due_date := l_so_line_rec.request_date;
585 l_batch_header_rec.FIRMED_IND := l_firmed_ind;
586 l_batch_header_rec.plan_cmplt_date := l_so_line_rec.schedule_ship_date;
587 /* Batch Creation user. If diff. from the sales order user then set the user context to this one */
588 l_user_id := FND_GLOBAL.user_id;
589 FND_GLOBAL.apps_initialize(l_user_id,-1,-1);
590
591 IF l_mto_rules_rec.batch_creation_user is not NULL THEN
592 IF (l_mto_rules_rec.batch_creation_user <> l_user_id) THEN
593 IF (g_debug <= gme_debug.g_log_statement) THEN
594 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch creation user is '||l_mto_rules_rec.batch_creation_user);
595 END IF;
596 FND_GLOBAL.apps_initialize(l_mto_rules_rec.batch_creation_user,-1,-1);
597 END IF;
598 END IF;
599
600 gme_debug.put_line('Calling Create Batch API');
601 FND_FILE.Put_Line(FND_FILE.LOG, 'call Create Batch API ');
602 FND_FILE.Put_Line(FND_FILE.LOG, 'Batch qty needed is '||l_batch_qty);
603 IF (g_debug <= gme_debug.g_log_statement) THEN
604 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoke Create_Batch API here');
605 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch qty required is '||l_batch_qty);
606 END IF;
607 GME_API_PUB.create_batch(
608 p_api_version => 2.0,
609 p_validation_level => 100,
610 p_init_msg_list => fnd_api.g_true,
611 p_commit => fnd_api.g_false,
612 x_message_count => l_msg_count,
613 x_message_list => l_msg_data,
614 x_return_status => l_return_status,
615 p_org_code => NULL,
616 p_batch_header_rec => l_batch_header_rec,
617 x_batch_header_rec => l_out_batch_header_rec,
618 p_batch_size => l_batch_qty,
619 p_batch_size_uom => l_so_line_rec.order_quantity_uom,
620 p_creation_mode => 'PRODUCT',
621 p_recipe_id => NULL,
622 p_recipe_no => NULL,
623 p_recipe_version => NULL,
624 p_product_no => NULL,
625 p_item_revision => NULL,
626 p_product_id => l_so_line_rec.inventory_item_id,
627 p_ignore_qty_below_cap => fnd_api.g_true,
628 p_use_workday_cal => NULL,
629 p_contiguity_override => NULL,
630 p_use_least_cost_validity_rule => fnd_api.g_false,
631 x_exception_material_tbl => l_exception_material_tbl
632 );
633
634 IF (g_debug <= gme_debug.g_log_statement) THEN
635 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Return Status from Create_Batch API is '||l_return_status);
636 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Message from Create_Batch API is '||l_msg_data );
637 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch No from Create_Batch API is '||
638 l_out_batch_header_rec.batch_no);
639 END IF;
640 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
641 j := to_number(NVL(l_msg_count,0));
642 FOR i in 1..j
643 LOOP
644
645 l_message := fnd_msg_pub.get(p_encoded => FND_API.G_FALSE);
646 l_message := replace(l_message, chr(0), ' ');
647 gme_debug.put_line(l_message);
648 END LOOP;
649 RAISE fnd_api.g_exc_error;
650 END IF;
651
652 /* Now retrieve the batch product line - sales order line must reserve to this supply
653 ===================================================================================*/
654 IF (g_debug <= gme_debug.g_log_statement) THEN
655 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Retrieve product line - must reserve to this supply');
656 END IF;
657 OPEN get_batch_line(l_out_batch_header_rec.batch_id,l_so_line_rec.inventory_item_id);
658 FETCH get_batch_line INTO l_material_detail_rec;
659
660 IF(get_batch_line%NOTFOUND) THEN
661 CLOSE get_batch_line;
662 IF (g_debug <= gme_debug.g_log_statement) THEN
663 gme_debug.put_line('get_batch_line%NOTFOUND, returning from create_batch_for_order_line');
664 END IF;
665 RAISE fnd_api.g_exc_error;
666 END IF;
667 CLOSE get_batch_line;
668
669 /* Copy Attachments if the rule says so
670 ===================================== */
671 IF (g_debug <= gme_debug.g_log_statement) THEN
672 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' analyse copy attachments '||l_mto_rules_rec.copy_attachments);
673 END IF;
674 IF (NVL(l_mto_rules_rec.copy_attachments,'N') = 'Y') THEN
675 IF (g_debug <= gme_debug.g_log_statement) THEN
676 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoke copy attachments ');
677 END IF;
678 copy_attachments( p_so_line_id => l_so_line_id
679 , p_so_category_id => l_mto_rules_rec.sales_order_attachment
680 , p_batch_id => l_out_batch_header_rec.batch_id
681 , p_batch_category_id => l_mto_rules_rec.batch_attachment
682 , x_return_status => l_return_status);
683
684 IF (g_debug <= gme_debug.g_log_statement) THEN
685 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Return Status from copy attachments '||l_return_status);
686 END IF;
687 END IF;
688
689 /* Need to set up pending lots if the product is lot controlled and the rule requires lot generation
690 =======================================================================================================*/
691 IF (g_debug <= gme_debug.g_log_statement) THEN
692 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' analyse auto lot generation'||l_mto_rules_rec.auto_lot_generation);
693 END IF;
694 IF l_item_rec.SECONDARY_UOM_CODE is not null then
695 l_batch_qty2 := inv_convert.inv_um_convert(l_so_line_rec.inventory_item_id,
696 null,
697 l_so_line_rec.ship_from_org_id,
698 5,
699 l_batch_qty,
700 l_so_line_rec.order_quantity_uom,
701 l_item_rec.SECONDARY_UOM_CODE,
702 null,
703 null);
704 END IF;
705
706 IF l_item_rec.lot_control_code > 1 AND (l_mto_rules_rec.auto_lot_generation = 'Y') THEN
707 l_pending_product_lots_rec.material_detail_id := l_material_detail_rec.material_detail_id;
708 l_pending_product_lots_rec.sequence := null;
709 l_pending_product_lots_rec.revision := l_material_detail_rec.revision;
710 l_pending_product_lots_rec.lot_number := null;
711 l_pending_product_lots_rec.quantity := l_batch_qty;
712 l_pending_product_lots_rec.secondary_quantity := l_batch_qty2;
713
714 IF (g_debug <= gme_debug.g_log_statement) THEN
715 gme_debug.put_line(g_pkg_name||'.'||l_api_name||
716 ' Invoke create_pending_product_lot for line_id '||l_material_detail_rec.material_detail_id);
717 END IF;
718
719 GME_API_PUB.create_pending_product_lot
720 (p_api_version => 2.0
721 ,p_validation_level => gme_common_pvt.g_max_errors
722 ,p_init_msg_list => fnd_api.g_false
723 ,p_commit => fnd_api.g_false
724 ,x_message_count => l_msg_count
725 ,x_message_list => l_msg_data
726 ,x_return_status => l_return_status
727 ,p_batch_header_rec => l_out_batch_header_rec
728 ,p_org_code => NULL
729 ,p_create_lot => fnd_api.g_true
730 ,p_generate_lot => fnd_api.g_true
731 ,p_material_detail_rec => l_material_detail_rec
732 ,p_pending_product_lots_rec => l_pending_product_lots_rec
733 ,x_pending_product_lots_rec => l_out_pending_product_lots_rec);
734
735 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
736 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' FAILURE to generate pending lot ');
737 END IF;
738 END IF; -- End of pending lot generation
739
740 gme_debug.put_line('Reserve the batch Quantity to this order line');
741 /* Establish a reservation between the order line (demand) and batch product (supply)
742 ====================================================================================*/
743 FND_FILE.Put_Line(FND_FILE.LOG, ' start of Reservation Processing ');
744 l_rsv_rec:= NULL;
745 l_rsv_rec.requirement_date := l_so_line_rec.request_date;
746 l_rsv_rec.organization_id := l_so_line_rec.ship_from_org_id;
747 l_rsv_rec.inventory_item_id := l_so_line_rec.inventory_item_id;
748 l_rsv_rec.demand_source_type_id := 2;
749 l_rsv_rec.demand_source_header_id := INV_SALESORDER.get_salesorder_for_oeheader(l_so_line_rec.header_id);
750 l_rsv_rec.demand_source_line_id := l_so_line_rec.line_id;
751 l_rsv_rec.reservation_uom_code := l_so_line_rec.order_quantity_uom;
752 l_rsv_rec.reservation_quantity := l_batch_qty;
753 l_rsv_rec.secondary_reservation_quantity := l_batch_qty2;
754 l_rsv_rec.revision := l_material_detail_rec.revision;
755 l_rsv_rec.subinventory_code := NULL;
756 l_rsv_rec.locator_id := NULL;
757 l_rsv_rec.lot_number := NULL;
758 l_rsv_rec.lpn_id := NULL;
759 l_rsv_rec.demand_source_name := NULL;
760 l_rsv_rec.demand_source_delivery := NULL;
761 l_rsv_rec.primary_uom_code := NULL;
762 l_rsv_rec.primary_uom_id := NULL;
763 l_rsv_rec.secondary_uom_code := l_so_line_rec.ordered_quantity_uom2;
764 l_rsv_rec.secondary_uom_id := NULL;
765 l_rsv_rec.reservation_uom_id := NULL;
766 l_rsv_rec.ship_ready_flag := NULL;
767 l_rsv_rec.detailed_quantity := NULL;
768 l_rsv_rec.secondary_detailed_quantity := NULL;
769 l_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_wip;
770 l_rsv_rec.supply_source_header_id := l_out_batch_header_rec.batch_id;
771 l_rsv_rec.supply_source_line_id := l_material_detail_rec.material_detail_id;
772
773 IF (g_debug <= gme_debug.g_log_statement) THEN
774 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoke INV_RESERVATION_PUB.Create_Reservation ');
775 END IF;
776
777 inv_reservation_pub.create_reservation
778 (p_api_version_number => 1.0
779 ,p_init_msg_lst => fnd_api.g_false
780 ,x_return_status => l_return_status
781 ,x_msg_count => l_msg_count
782 ,x_msg_data => l_msg_data
783 ,p_rsv_rec => l_rsv_rec
784 ,p_serial_number => l_in_serial_num
785 ,x_serial_number => l_out_serial_num
786 ,p_partial_reservation_flag => fnd_api.g_true
787 ,p_validation_flag => fnd_api.g_true
788 ,x_quantity_reserved => l_reserved_qty
789 ,x_reservation_id => l_reservation_id
790 ,p_partial_rsv_exists => TRUE);
791
792 IF (g_debug <= gme_debug.g_log_statement) THEN
793 gme_debug.put_line ( g_pkg_name
794 || '.'
795 || l_api_name
796 || ' inv_reservation_pub.create_reservation returns status of '
797 || l_return_status
798 || ' for material_detail_id '
799 || l_material_detail_rec.material_detail_id
800 || ' qty reserved IS '
801 || l_reserved_qty );
802 END IF;
803
804 IF (l_return_status IN
805 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
806 RAISE FND_API.G_EXC_ERROR;
807 END IF;
808
809 /* Notify the customer sales representative of the batch creation
810 ===============================================================*/
811 IF (g_debug <= gme_debug.g_log_statement) THEN
812 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Notify_CSR for batch_id'||l_rsv_rec.supply_source_header_id);
813 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Notify_CSR for batch_line_id'||l_rsv_rec.supply_source_line_id);
814 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Notify_CSR for sales_line_id'||l_rsv_rec.demand_source_line_id);
815 END IF;
816 --FND_GLOBAL.apps_initialize(l_user_id,-1,-1);
817 GME_SUPPLY_RES_PVT.notify_CSR
818 ( P_Batch_id => l_rsv_rec.supply_source_header_id
819 , P_Batch_line_id => l_rsv_rec.supply_source_line_id
820 , P_So_line_id => l_rsv_rec.demand_source_line_id
821 , P_batch_trans_id => NULL
822 , P_organization_id => l_rsv_rec.organization_id
823 , P_action_code => 'NEW_BATCH_CREATED'
824 , X_return_status => l_return_status
825 , X_msg_cont => l_msg_count
826 , X_msg_data => l_msg_data );
827
828 IF g_debug <= gme_debug.g_log_procedure THEN
829 gme_debug.put_line ( g_pkg_name || '.'
830 || l_api_name
831 || ' after calling notify_CSR for create reservation status is '
832 || l_return_status );
833 END IF;
834
835 IF l_return_status IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) THEN
836 IF g_debug <= gme_debug.g_log_procedure THEN
837 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Exit after Notifications Failure ');
838 END IF;
839 RAISE FND_API.G_EXC_ERROR;
840 END IF;
841
842 /* Set the user context back to the original user */
843 IF (l_mto_rules_rec.batch_creation_user <> l_user_id) THEN
844 FND_GLOBAL.apps_initialize(l_user_id,-1,-1);
845 END IF;
846
847 /* Get the Order and Line Information */
848 OPEN Get_order_info(p_so_line_id);
849 FETCH Get_order_info INTO l_so_line_no,l_item_no,
850 l_order_no;
851 IF(Get_order_info%NOTFOUND) THEN
852 IF g_debug <= gme_debug.g_log_procedure THEN
853 gme_debug.put_line(g_pkg_name||'.'||l_api_name ||'Get_order_info%NOTFOUND');
854 END IF;
855 END IF;
856 CLOSE Get_order_info;
857 FND_FILE.Put_Line(FND_FILE.LOG, '***************************************************************************************** ');
858 FND_FILE.Put_Line(FND_FILE.LOG, 'Batch '||l_out_batch_header_rec.batch_no ||' created for order ' ||
859 l_order_no ||' line number '||l_so_line_no ||
860 ' item is '|| l_item_no ||' reservation id is '||l_reservation_id);
861 FND_FILE.Put_Line(FND_FILE.LOG,'Make to Order implemented for rule '||l_mto_rules_rec.rule_name);
862 FND_FILE.Put_Line(FND_FILE.LOG, '***************************************************************************************** ');
863
864 /* EXCEPTION HANDLING
865 ====================*/
866 EXCEPTION
867 WHEN FND_API.G_EXC_ERROR THEN
868 -- ROLLBACK TO SAVEPOINT create_batch_for_so_line_SP;
869 gme_debug.put_line('sqlcode : ' ||to_char(sqlcode));
870 gme_debug.put_line('sqlerr : '|| SUBSTRB(SQLERRM, 1, 150));
871 x_return_status := FND_API.G_RET_STS_ERROR;
872 -- errbuf := SUBSTRB(SQLERRM, 1, 150);
873 -- retcode := x_return_status;
874
875 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name
876 , l_api_name
877 );
878 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
879 , p_count => x_msg_count
880 , p_data => x_msg_data
881 );
882
883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
884 -- ROLLBACK TO SAVEPOINT create_batch_for_so_line_SP;
885 gme_debug.put_line('sqlcode : ' ||to_char(sqlcode));
886 gme_debug.put_line('sqlerr : '|| SUBSTRB(SQLERRM, 1, 150));
887
888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
889 -- errbuf := SUBSTRB(SQLERRM, 1, 150);
890 -- retcode := x_return_status;
891
892
893 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name
894 , l_api_name
895 );
896 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
897 , p_count => x_msg_count
898 , p_data => x_msg_data
899 );
900 WHEN OTHERS THEN
901 --ROLLBACK TO SAVEPOINT create_batch_for_so_line_SP;
902 gme_debug.put_line('sqlcode : ' ||to_char(sqlcode));
903 gme_debug.put_line('sqlerr : '|| SUBSTRB(SQLERRM, 1, 150));
904
905 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name
906 , l_api_name
907 );
908
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910 -- errbuf := SUBSTRB(SQLERRM, 1, 150);
911 -- retcode := x_return_status;
912
913 /* Get message count and data */
914 FND_MSG_PUB.count_and_get
915 ( p_count => x_msg_count
916 , p_data => x_msg_data
917 );
918
919 END create_batch_for_order_line;
920
921
922 PROCEDURE Copy_attachments ( p_so_category_id IN NUMBER
923 , p_so_line_id IN NUMBER
924 , p_batch_category_id IN NUMBER
925 , p_batch_id IN NUMBER
926 , x_return_status OUT NOCOPY VARCHAR2) IS
927
928 CURSOR get_attachments(p_so_category_id IN NUMBER
929 , p_so_line_id IN NUMBER) IS
930 SELECT *
931 FROM fnd_attached_docs_form_VL
932 WHERE function_name = decode(0,1,NULL,'OEXOEORD')
933 AND function_type = decode(0,1,NULL,'O')
934 AND (security_type = 4 OR publish_flag = 'Y')
935 AND category_id = p_so_category_id
936 AND ((entity_name = 'OE_ORDER_LINES') AND (pk1_value = p_so_line_id ));
937
938
939 BEGIN
940 gme_debug.put_line('Entering copy_attachemnts routine..');
941 FOR so_attchments_row IN get_attachments(p_so_category_id,p_so_line_id) LOOP
942
943 so_attchments_row.row_id := NULL;
944 so_attchments_row.document_id := NULL;
945 so_attchments_row.attached_document_id := so_attchments_row.ATTACHED_DOCUMENT_ID * -1;
946
947 FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW (
948 X_Rowid => so_attchments_row.row_id
949 ,X_attached_document_id => so_attchments_row.attached_document_id
950 ,X_document_id => so_attchments_row.document_id
951 ,X_creation_date => so_attchments_row.CREATION_DATE
952 ,X_created_by => so_attchments_row.CREATED_BY
953 ,X_last_update_date => so_attchments_row.LAST_UPDATE_DATE
954 ,X_last_updated_by => so_attchments_row.LAST_UPDATED_BY
955 ,X_last_update_login => so_attchments_row.LAST_UPDATE_LOGIN
956 ,X_seq_num => so_attchments_row.SEQ_NUM
957 ,X_entity_name => 'GME_BATCH_HEADER'
958 ,X_column1 => so_attchments_row.COLUMN1
959 ,X_pk1_value => p_batch_id
960 ,X_pk2_value => NULL
961 ,X_pk3_value => NULL
962 ,X_pk4_value => NULL
963 ,X_pk5_value => NULL
964 ,X_automatically_added_flag => so_attchments_row.AUTOMATICALLY_ADDED_FLAG
965 ,X_request_id => so_attchments_row.REQUEST_ID
966 ,X_program_application_id => so_attchments_row.PROGRAM_APPLICATION_ID
967 ,X_program_id => so_attchments_row.PROGRAM_ID
968 ,X_program_update_date => so_attchments_row.PROGRAM_UPDATE_DATE
969 ,X_Attribute_Category => so_attchments_row.ATTRIBUTE_CATEGORY
970 ,X_Attribute1 => so_attchments_row.ATTRIBUTE1
971 ,X_Attribute2 => so_attchments_row.ATTRIBUTE2
972 ,X_Attribute3 => so_attchments_row.ATTRIBUTE3
973 ,X_Attribute4 => so_attchments_row.ATTRIBUTE4
974 ,X_Attribute5 => so_attchments_row.ATTRIBUTE5
975 ,X_Attribute6 => so_attchments_row.ATTRIBUTE6
976 ,X_Attribute7 => so_attchments_row.ATTRIBUTE7
977 ,X_Attribute8 => so_attchments_row.ATTRIBUTE8
978 ,X_Attribute9 => so_attchments_row.ATTRIBUTE9
979 ,X_Attribute10 => so_attchments_row.ATTRIBUTE10
980 ,X_Attribute11 => so_attchments_row.ATTRIBUTE11
981 ,X_Attribute12 => so_attchments_row.ATTRIBUTE12
982 ,X_Attribute13 => so_attchments_row.ATTRIBUTE13
983 ,X_Attribute14 => so_attchments_row.ATTRIBUTE14
984 ,X_Attribute15 => so_attchments_row.ATTRIBUTE15
985 , X_datatype_id => so_attchments_row.DATATYPE_ID
986 , X_category_id => p_batch_category_id
987 , X_security_type => so_attchments_row.SECURITY_TYPE
988 , X_security_id => so_attchments_row.SECURITY_ID
989 , X_publish_flag => so_attchments_row.PUBLISH_FLAG
990 , X_image_type => so_attchments_row.IMAGE_TYPE
991 , X_storage_type => so_attchments_row.STORAGE_TYPE
992 , X_usage_type => so_attchments_row.USAGE_TYPE
993 , X_language => USERENV('LANG')
994 , X_description => so_attchments_row.DOCUMENT_DESCRIPTION
995 , X_file_name => so_attchments_row.FILE_NAME
996 , X_media_id => so_attchments_row.MEDIA_ID
997 , X_doc_attribute_Category => so_attchments_row.DOC_ATTRIBUTE_CATEGORY
998 , X_doc_attribute1 => so_attchments_row.DOC_ATTRIBUTE1
999 , X_doc_attribute2 => so_attchments_row.DOC_ATTRIBUTE2
1000 , X_doc_attribute3 => so_attchments_row.DOC_ATTRIBUTE3
1001 , X_doc_attribute4 => so_attchments_row.DOC_ATTRIBUTE4
1002 , X_doc_attribute5 => so_attchments_row.DOC_ATTRIBUTE5
1003 , X_doc_attribute6 => so_attchments_row.DOC_ATTRIBUTE6
1004 , X_doc_attribute7 => so_attchments_row.DOC_ATTRIBUTE7
1005 , X_doc_attribute8 => so_attchments_row.DOC_ATTRIBUTE8
1006 , X_doc_attribute9 => so_attchments_row.DOC_ATTRIBUTE9
1007 , X_doc_attribute11 => so_attchments_row.DOC_ATTRIBUTE11
1008 , X_doc_attribute12 => so_attchments_row.DOC_ATTRIBUTE12
1009 , X_doc_attribute13 => so_attchments_row.DOC_ATTRIBUTE13
1010 , X_doc_attribute14 => so_attchments_row.DOC_ATTRIBUTE14
1011 , X_doc_attribute15 => so_attchments_row.DOC_ATTRIBUTE15
1012 , X_create_doc => 'Y' --- Default is 'N'
1013 );
1014 x_return_status := FND_API.G_RET_STS_SUCCESS;
1015 END LOOP;
1016
1017 gme_debug.put_line('Exiting copy_attachments procedure without any errors');
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 GMI_RESERVATION_UTIL.Println('In others exception in copy_attachments');
1021 x_return_status := FND_API.G_RET_STS_ERROR;
1022 END Copy_attachments;
1023
1024 FUNCTION line_qualifies_for_MTO (
1025 p_line_id IN NUMBER
1026 )
1027 RETURN BOOLEAN
1028 IS
1029 l_return_status VARCHAR2 ( 1 );
1030 l_msg_count NUMBER;
1031 l_msg_data VARCHAR2 ( 240 );
1032 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
1033 l_api_name CONSTANT VARCHAR2(30):= 'line_qualifies_for MTO';
1034 l_in_assignments_rec GME_MTO_RULE_ASSIGNMENTS%ROWTYPE;
1035 l_mto_rules_rec GME_MTO_RULES%ROWTYPE;
1036 l_mto_assignments_rec GME_MTO_RULE_ASSIGNMENTS%ROWTYPE;
1037 l_order_line_rec OE_ORDER_LINES_ALL%ROWTYPE;
1038 l_item_type VARCHAR2(30);
1039
1040 CURSOR get_order_line_rec(p_line_id IN NUMBER) IS
1041 SELECT *
1042 FROM oe_order_lines_all
1043 WHERE line_id = p_line_id;
1044
1045 CURSOR cur_item(p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER) IS
1046 SELECT item_type
1047 FROM mtl_system_items_b m
1048 WHERE m.inventory_item_id = p_inventory_item_id
1049 AND m.organization_id = p_organization_id;
1050 BEGIN
1051 IF (g_debug <= gme_debug.g_log_statement) THEN
1052 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' ENTER FUNCTION '||l_api_name||' for line '||p_line_id);
1053 gme_debug.put_line ( '*** function call to check if order line qualifies for OPM make to order ***' );
1054 END IF;
1055
1056 /* ============== Retrieve Order line data ====================*/
1057 OPEN get_order_line_rec(p_line_id);
1058 FETCH get_order_line_rec INTO l_order_line_rec;
1059
1060
1061 IF(get_order_line_rec%NOTFOUND) THEN
1062 CLOSE get_order_line_rec;
1063 IF (g_debug <= gme_debug.g_log_statement) THEN
1064 gme_debug.put_line('get_order_line_rec%NOTFOUND, RETURN now');
1065 END IF;
1066 Return FALSE;
1067 END IF;
1068 CLOSE get_order_line_rec;
1069
1070 /* ============== Establish Item Characteristics ====================*/
1071 IF (g_debug <= gme_debug.g_log_statement) THEN
1072 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Retrieve Item for organization_id '||l_order_line_rec.ship_from_org_id);
1073 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Retrieve Item for inventory_item_id '||l_order_line_rec.inventory_item_id);
1074 END IF;
1075 OPEN cur_item (l_order_line_rec.ship_from_org_id, l_order_line_rec.inventory_item_id);
1076 FETCH cur_item INTO l_item_type;
1077 IF(cur_item%NOTFOUND) THEN
1078 CLOSE cur_item;
1079 IF (g_debug <= gme_debug.g_log_statement) THEN
1080 gme_debug.put_line('Failure to retrieve item; terminate processing');
1081 END IF;
1082 Return FALSE;
1083 END IF;
1084 CLOSE cur_item;
1085 /* ============== Retrieve Rule where one exists ====================*/
1086 l_in_assignments_rec.organization_id := l_order_line_rec.ship_from_org_id;
1087 l_in_assignments_rec.inventory_item_id := l_order_line_rec.inventory_item_id;
1088 l_in_assignments_rec.item_type := l_item_type;
1089 l_in_assignments_rec.customer_id := l_order_line_rec.sold_to_org_id;
1090 l_in_assignments_rec.site_use_id := l_order_line_rec.ship_to_org_id;
1091
1092 GME_MAKE_TO_ORDER_PVT.retrieve_rule(
1093 p_mto_assignments_rec => l_in_assignments_rec
1094 ,x_mto_rules_rec => l_mto_rules_rec
1095 ,x_mto_assignments_rec => l_mto_assignments_rec
1096 ,x_return_status => l_return_status
1097 ,x_msg_data => l_msg_data
1098 ,x_msg_count => l_msg_count);
1099
1100 IF (g_debug <= gme_debug.g_log_statement) THEN
1101 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Return status from Retrieve_Rule is '||l_return_status);
1102 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Rule ID to work with is '||l_mto_assignments_rec.rule_id);
1103 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Rule being honored is '||l_mto_rules_rec.rule_name);
1104 END IF;
1105
1106 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS ) or (NVL(l_mto_assignments_rec.rule_id,0) = 0) THEN
1107 Return FALSE;
1108 ELSE
1109 Return TRUE;
1110 END IF;
1111 END line_qualifies_for_MTO;
1112
1113 PROCEDURE retrieve_rule
1114 (
1115 p_mto_assignments_rec IN GME_MTO_RULE_ASSIGNMENTS%ROWTYPE
1116 , x_mto_rules_rec OUT NOCOPY GME_MTO_RULES%ROWTYPE
1117 , x_mto_assignments_rec OUT NOCOPY GME_MTO_RULE_ASSIGNMENTS%ROWTYPE
1118 , x_return_status OUT NOCOPY VARCHAR2
1119 , x_msg_count OUT NOCOPY NUMBER
1120 , x_msg_data OUT NOCOPY VARCHAR2
1121 ) IS
1122
1123 l_api_name CONSTANT VARCHAR2(30):= 'retrieve_rule';
1124 l_mto_assignments_rec GME_MTO_RULE_ASSIGNMENTS%ROWTYPE;
1125 l_mto_rules_rec GME_MTO_RULES%ROWTYPE;
1126
1127 Cursor get_assignment IS
1128 Select decode(site_use_id, null, 0, site_use_id) site_use_id
1129 , decode(customer_id, null, 0, customer_id) customer_id
1130 , decode(inventory_item_id, null, 0, inventory_item_id) item_id
1131 , decode(item_type, null, ' ', item_type) item_type
1132 , rule_assign_id
1133 , rule_id
1134 , organization_id
1135 From gme_mto_rule_assignments
1136 Where organization_id = p_mto_assignments_rec.organization_id
1137 and (inventory_item_id = p_mto_assignments_rec.inventory_item_id
1138 or inventory_item_id is null )
1139 and (item_type = p_mto_assignments_rec.item_type
1140 or item_type is null)
1141 and (customer_id = p_mto_assignments_rec.customer_id
1142 or customer_id is null)
1143 and (site_use_id = p_mto_assignments_rec.site_use_id
1144 or site_use_id is null)
1145 Order by
1146 3 desc
1147 , 1 desc
1148 , 2 desc
1149 , 4 desc
1150 ;
1151
1152 Cursor get_rule IS
1153 Select *
1154 From gme_mto_rules
1155 Where rule_id = l_mto_assignments_rec.rule_id;
1156
1157 BEGIN
1158 IF (g_debug <= gme_debug.g_log_statement) THEN
1159 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' ENTER API ');
1160 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' organization_id '||p_mto_assignments_rec.organization_id);
1161 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' inventory_item_id '||p_mto_assignments_rec.inventory_item_id);
1162 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' item_type '||p_mto_assignments_rec.item_type);
1163 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' customer_id '||p_mto_assignments_rec.customer_id);
1164 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' site_use_id '||p_mto_assignments_rec.site_use_id);
1165 END IF;
1166 x_return_status := FND_API.G_RET_STS_SUCCESS;
1167 /* get the most suitable rule first */
1168 GMI_reservation_Util.PrintLn('IN check Rule');
1169 Open get_assignment;
1170 Fetch get_assignment
1171 Into l_mto_assignments_rec.site_use_id
1172 , l_mto_assignments_rec.customer_id
1173 , l_mto_assignments_rec.inventory_item_id
1174 , l_mto_assignments_rec.item_type
1175 , l_mto_assignments_rec.rule_assign_id
1176 , l_mto_assignments_rec.rule_id
1177 , l_mto_assignments_rec.organization_id;
1178 IF(get_assignment%NOTFOUND) THEN
1179 CLOSE get_assignment;
1180 gme_debug.put_line('No rule assigment to match input criteria; returning from Retrieve Rule');
1181 RETURN;
1182 END IF;
1183 Close get_assignment;
1184
1185 x_mto_assignments_rec := l_mto_assignments_rec;
1186
1187 Open get_rule;
1188 Fetch get_rule into l_mto_rules_rec;
1189 IF(get_rule%NOTFOUND) THEN
1190 CLOSE get_rule;
1191 gme_debug.put_line('Error encountered on rule retrieval for Rule ID '||l_mto_assignments_rec.rule_id);
1192 RAISE FND_API.G_EXC_ERROR;
1193 END IF;
1194 Close get_rule;
1195
1196 x_mto_rules_rec := l_mto_rules_rec;
1197 END retrieve_rule;
1198
1199
1200 END GME_MAKE_TO_ORDER_PVT;