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