[Home] [Help]
PACKAGE BODY: APPS.GML_OPM_OM_CONV_MIG_PKG
Source
1 PACKAGE BODY GML_OPM_OM_CONV_MIG_PKG AS
2 /* $Header: GMLCONVB.pls 120.19 2007/02/23 22:09:26 plowe ship $ */
3
4 /*===========================================================================================================
5 -- PROCEDURE:
6 -- MIGRATE_OPM_OM_OPEN_LINES
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL procedure is used to OPM-OM open order lines
10 --
11 -- PARAMETERS:
12 -- p_migration_run_id This is used for message logging.
13 -- p_commit Commit flag.
14 -- x_failure_count count of the failed lines.An out parameter.
15 --
16 -- SYNOPSIS:
17 --
18 -- MIGRATE_OPM_OM_OPEN_LINES ( p_migration_run_id IN NUMBER
19 -- , p_commit IN VARCHAR2
20 -- , x_failure_count OUT NUMBER)
21 -- HISTORY
22 --=============================================================================================================*/
23
24 PROCEDURE Migrate_opm_om_open_lines( p_migration_run_id IN NUMBER
25 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
26 , x_failure_count OUT NOCOPY NUMBER) IS
27
28 /* Migration specific variables */
29 l_failure_count NUMBER := 0;
30 l_success_count NUMBER := 0;
31 l_table_name VARCHAR2(30) DEFAULT NULL;
32 l_opm_table_name VARCHAR2(30) DEFAULT NULL;
33
34
35 -- Local Variables.
36 l_msg_count NUMBER :=0;
37 l_msg_data VARCHAR2(2000);
38 l_return_status VARCHAR2(1);
39 l_IC$DEFAULT_LOCT VARCHAR2(255)DEFAULT NVL(FND_PROFILE.VALUE('IC$DEFAULT_LOCT'),' ') ;
40
41 l_wdd_rec wsh_delivery_details%rowtype;
42 l_mo_line_rec ic_txn_request_lines%rowtype;
43 l_order_line oe_order_lines_all%rowtype;
44 l_ic_mo_header_rec ic_txn_request_headers%rowtype;
45 l_mtl_mo_header_rec INV_Move_Order_PUB.Trohdr_Rec_Type := INV_Move_Order_PUB.G_MISS_TROHDR_REC;
46 l_mtl_mo_line_rec INV_Move_Order_PUB.Trolin_Rec_Type; /*(:= INV_Move_Order_PUB.G_MISS_TROLIN_TBL; */
47
48 /* MTL Move order header */
49
50 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type := INV_Move_Order_PUB.G_MISS_TROHDR_REC;
51 l_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
52 l_empty_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
53
54 /* MTL Move order line */
55 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
56 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
57 l_commit VARCHAR2(1) := FND_API.G_TRUE;
58 l_line_num Number := 0;
59 l_order_count NUMBER := 1;
60
61 /* Item Controls */
62 l_grade_control_flag VARCHAR2(1);
63 l_lot_divisible_flag VARCHAR2(1);
64 l_opm_noninv_ind NUMBER := 0; -- 5475003
65 l_dual_uom_control NUMBER;
66 l_primary_uom_code VARCHAR2(3);
67 l_secondary_uom_code VARCHAR2(3);
68 l_tracking_quantity_ind VARCHAR2(30);
69 l_lot_control_code NUMBER;
70 l_subinventory_ind_flag VARCHAR2(1);
71 l_lot_no VARCHAR2(30);
72 l_sublot_no VARCHAR2(30);
73 l_locator_id NUMBER;
74 l_mo_line_id NUMBER;
75 l_mo_header_id NUMBER;
76 l_line_status NUMBER;
77 l_detail_reservations NUMBER := 0;
78
79 l_inventory_location_id NUMBER := 0;
80
81 l_organization_id number; -- 5574631
82 l_failure_count1 NUMBER := 0; -- 5574631
83 l_inventory_item_id number; -- 5574631
84 l_subinventory_code VARCHAR2(10); -- 5574631
85 l_to_locator_id NUMBER; -- 5601081
86 l_grouping_rule_id NUMBER; -- 5601081
87 l_sales_order_id NUMBER; -- 5601081
88 l_delivery_detail_id NUMBER; -- 5601081
89 l_line_id NUMBER; -- 5601081
90
91 l_api_return_status VARCHAR2(1);
92 l_api_error_code NUMBER;
93 l_api_error_msg VARCHAR2(100);
94 l_message VARCHAR2(255);
95 l_demand_source_type NUMBER;
96 l_status BOOLEAN;
97
98 /* Reservation Related */
99
100 l_insert_rsv_rec inv_reservation_global.mtl_reservation_rec_type; -- Record for inserting reservations
101 l_qty_reserved NUMBER;
102 l_sec_qty_reserved NUMBER;
103 l_reservation_id NUMBER;
104 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
105 l_odm_lot_num VARCHAR2(80);
106 l_parent_lot_num VARCHAR2(80);
107 l_count NUMBER;
108
109 NON_INV_ITEM EXCEPTION;
110
111
112 CURSOR get_open_order_lines IS
113 SELECT header_id,
114 line_id,
115 inventory_item_id,
116 ship_from_org_id,
117 schedule_status_code,
118 'OE' source,
119 ordered_quantity qty -- added for debug only
120 FROM oe_order_lines_all ol,
121 mtl_parameters mtl
122 WHERE ol.ship_from_org_id = mtl.organization_id
123 AND mtl.process_enabled_flag = 'Y' AND
124 NVL(ol.shipping_interfaced_flag,'N')= 'N'
125 UNION ALL
126 SELECT wdd.source_header_id,
127 wdd.source_line_id,
128 wdd.inventory_item_id,
129 wdd.organization_id,
130 to_char(NULL) schedule_status_code,
131 'WDD' source,
132 0 qty
133 FROM wsh_delivery_details wdd,
134 mtl_parameters mtl
135 WHERE wdd.organization_id = mtl.organization_id
136 AND mtl.process_enabled_flag = 'Y'
137 AND wdd.released_status in ('B','R','S', 'X'); -- 5475003 need to include status X for non inv items
138
139 CURSOR get_allocations_for_line(p_line_id IN NUMBER) IS
140 SELECT *
141 FROM ic_tran_pnd
142 WHERE doc_type = 'OMSO'
143 AND line_id = p_line_id
144 AND delete_mark = 0
145 AND staged_ind = 0
146 AND completed_ind = 0
147 AND abs(round(trans_qty,5)) > 0
148 AND (lot_id >0 OR location <> l_IC$DEFAULT_LOCT);
149
150 CURSOR get_item_details(p_item_id IN NUMBER, p_organization_id IN NUMBER) IS
151 SELECT m.lot_divisible_flag,m.lot_control_code,m.tracking_quantity_ind, ic.NONINV_IND -- 5475003 rework
152 FROM mtl_system_items m, ic_item_mst ic -- 5475003 rework
153 WHERE inventory_item_id = p_item_id and organization_id = p_organization_id
154 AND m.segment1 = ic.item_no; -- 5475003 rework
155
156 CURSOR get_whse_details(p_whse_code IN VARCHAR2) IS
157 SELECT subinventory_ind_flag, organization_id -- 5574631
158 FROM ic_whse_mst
159 WHERE whse_code = p_whse_code;
160
161 CURSOR get_locator_id(p_whse_code IN VARCHAR2,p_location IN VARCHAR2) IS
162 SELECT locator_id, inventory_location_id -- 5576431
163 FROM ic_loct_mst
164 WHERE whse_code = p_whse_code
165 AND location = p_location;
166
167 -- 5576431
168
169 CURSOR get_subinv(p_inventory_location_id IN NUMBER) IS
170 SELECT subinventory_code
171 FROM mtl_item_locations
172 WHERE inventory_location_id = p_inventory_location_id;
173
174
175 CURSOR get_lot_sublot(p_item_id IN NUMBER,p_lot_id IN NUMBER) IS
176 SELECT lot_no,sublot_no
177 FROM ic_lots_mst
178 WHERE item_id = p_item_id
179 AND lot_id = p_lot_id;
180
181 CURSOR Get_Mo_Line(p_line_id IN NUMBER) IS
182 SELECT *
183 FROM ic_txn_request_lines
184 WHERE txn_source_line_id = p_line_id
185 AND line_status = 7; -- 7 is open , 5 is closed , 9 is cancelled
186
187 CURSOR get_delivery_details(p_mo_line_id IN NUMBER, p_line_id IN NUMBER) IS
188 SELECT delivery_detail_id
189 FROM wsh_delivery_details
190 WHERE move_order_line_id = p_mo_line_id
191 AND source_line_id = p_line_id;
192
193 CURSOR get_move_order_header(p_line_id IN NUMBER) IS
194 SELECT h.*
195 FROM ic_txn_request_headers h,
196 ic_txn_request_lines l
197 WHERE l.header_id = h.header_id
198 AND l.line_id = p_line_id;
199
200 CURSOR Cur_get_sch_sta_code(p_line_id IN NUMBER) IS
201 SELECT schedule_status_code
202 FROM oe_order_lines_all
203 WHERE line_id = p_line_id;
204
205
206 --5574631
207 CURSOR c_wsh_deliveres (p_line_id in number) is
208 select distinct wda.delivery_id
209 from wsh_delivery_details wdd,
210 wsh_new_deliveries wnd,
211 wsh_delivery_assignments wda
212 where wdd.source_line_id = p_line_id
213 and wdd.delivery_detail_id = wda.delivery_detail_id
214 and wda.delivery_id = wnd.delivery_id
215 and wnd.delivery_id is not null;
216
217 -- 5601081
218 CURSOR get_shipping_parameters(p_org_id IN number) IS
219 SELECT default_stage_locator_id, pick_grouping_rule_id
220 FROM wsh_shipping_parameters
221 WHERE organization_id = p_org_id;
222
223 -- 5601081
224 CURSOR get_sales_order_id(p_header_id IN number) IS
225 select sales_order_id
226 from oe_order_headers_all oe, mtl_sales_orders mtl
227 where oe.header_id = p_header_id and mtl.segment1 = oe.order_number;
228
229
230 BEGIN
231
232 /* Begin by logging a message that open order lines migration has started */
233 gma_common_logging.gma_migration_central_log (
234 p_run_id => p_migration_run_id
235 , p_log_level => FND_LOG.LEVEL_PROCEDURE
236 , p_app_short_name => 'GML'
237 , p_message_token => 'GML_MIGRATION_TABLE_STARTED'
238 , p_table_name => l_opm_table_name
239 , p_context => 'OPEN_ORDER_LINES');
240
241
242 /* Get all the open lines to be processed */
243 l_table_name := 'MTL_RESERVATIONS';
244 l_opm_table_name := 'IC_TRAN_PND';
245 GMI_RESERVATION_UTIL.println('START OF GML_OPM_OM_CONV_MIG_PKG.Migrate_opm_om_open_lines - run_id = ' || p_migration_run_id);
246 GMI_RESERVATION_UTIL.Println('IC DEFAULT_LOCT is ' || l_IC$DEFAULT_LOCT);
247
248 FOR open_order_line_rec IN get_open_order_lines LOOP
249 GMI_RESERVATION_UTIL.println('NNNNn - In get_open_order_lines loop which is the main loop');
250
251 BEGIN
252 /* Get the item controls for the order line */
253 OPEN get_item_details(open_order_line_rec.inventory_item_id,open_order_line_rec.ship_from_org_id);
254 FETCH get_item_details INTO l_lot_divisible_flag,l_lot_control_code,l_tracking_quantity_ind,l_opm_noninv_ind ;
255 CLOSE get_item_details;
256
257 GMI_RESERVATION_UTIL.println('after item controls '||'inventory_item_id : '||open_order_line_rec.inventory_item_id);
258 GMI_RESERVATION_UTIL.println('l_opm_noninv_ind : '||l_opm_noninv_ind );
259 GMI_RESERVATION_UTIL.println('l_lot_control_code : '||l_lot_control_code);
260 GMI_RESERVATION_UTIL.println('ordered_quantity : '||open_order_line_rec.qty);
261
262 IF l_opm_noninv_ind = 0 THEN -- 5475003 only check allocations if an inventory item (control from ic_item_mst)
263 /* -- NOTA BENE - we do not create move orders either for a non-inventory item in R12 (in 11i OPM we did ) */
264 /* Are there allocations for this? If so create reservations for these allocations */
265
266 GMI_RESERVATION_UTIL.println('line_id is '|| open_order_line_rec.line_id);
267 FOR allocations_for_line_rec IN get_allocations_for_line(open_order_line_rec.line_id)
268 LOOP
269
270 BEGIN
271 GMI_RESERVATION_UTIL.PrintLn('NNNN In allocations loop');
272
273 /* Fill in the reservation record */
274 l_insert_rsv_rec.reservation_id := NULL; -- cannot know
275 l_insert_rsv_rec.requirement_date := SYSDATE;
276 l_insert_rsv_rec.organization_id := open_order_line_rec.ship_from_org_id;
277 l_insert_rsv_rec.inventory_item_id := open_order_line_rec.inventory_item_id;
278 l_insert_rsv_rec.demand_source_type_id := 2; /* For the Sales Order line */
279 l_insert_rsv_rec.demand_source_name := NULL;
280 l_insert_rsv_rec.demand_source_header_id := allocations_for_line_rec.doc_id; --open_order_line_rec.header_id;
281 l_insert_rsv_rec.demand_source_line_id := open_order_line_rec.line_id;
282 l_insert_rsv_rec.demand_source_delivery := NULL;
283 l_insert_rsv_rec.primary_uom_code := l_primary_uom_code;
284
285 IF (l_tracking_quantity_ind = 'PS') THEN
286 l_insert_rsv_rec.secondary_uom_code := l_secondary_uom_code;
287 END IF;
288
289 l_insert_rsv_rec.primary_uom_id := NULL;
290 l_insert_rsv_rec.secondary_uom_id := NULL;
291 l_insert_rsv_rec.reservation_uom_code := NULL;
292 l_insert_rsv_rec.reservation_uom_id := NULL;
293 l_insert_rsv_rec.reservation_quantity := NULL;
294
295 l_insert_rsv_rec.primary_reservation_quantity := abs(trunc(allocations_for_line_rec.trans_qty,5)); -- 5616998
296
297 IF (l_tracking_quantity_ind = 'PS') THEN
298 l_insert_rsv_rec.secondary_reservation_quantity:= abs(trunc(allocations_for_line_rec.trans_qty2,5)); -- 5616998
299 ELSE
300 l_insert_rsv_rec.secondary_reservation_quantity := NULL; -- need to initialize this back else will fail
301 END IF;
302
303 --l_insert_rsv_rec.grade_code := NULL;
304 l_insert_rsv_rec.autodetail_group_id := NULL;
305 l_insert_rsv_rec.external_source_code := NULL;
306 l_insert_rsv_rec.external_source_line_id := NULL;
307 l_insert_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
308 l_insert_rsv_rec.supply_source_header_id := NULL;
309 l_insert_rsv_rec.supply_source_line_id := NULL;
310 l_insert_rsv_rec.supply_source_name := NULL;
311 l_insert_rsv_rec.supply_source_line_detail := NULL;
312 l_insert_rsv_rec.revision := NULL;
313
314 /* See if the From Whse is mapped as a subinventory - flag = Y*/
315 -- if so, organization_id represents the new organization under which the warehouse is mapped as subinventory.
316 OPEN get_whse_details(allocations_for_line_rec.whse_code);
317 FETCH get_whse_details INTO l_subinventory_ind_flag, l_organization_id; -- 5574631
318 CLOSE get_whse_details;
319
320 GMI_RESERVATION_UTIL.Println('subinventoryflag for the whse '|| l_subinventory_ind_flag);
321 GMI_RESERVATION_UTIL.Println('whse_code '|| allocations_for_line_rec.whse_code);
322 -- 5574631 start
323
324 IF l_subinventory_ind_flag = 'Y' then -- 5574631
325
326 GMI_RESERVATION_UTIL.PrintLn(' From Whse is mapped as a subinventory new organization_id is '|| l_organization_id);
327 -- From Whse is mapped as a subinventory, so need to
328 -- 1 update the SO line with new org (open_order_line_rec.ship_from_org_id) ;
329 -- 2 update any delivery details with new org;
330 -- 3 update wsh_new_deliveries (trips do NOT store orgs) with the new org (all because of GMD's requirements);
331 -- 4 use this new org id for the reservation to be created;
332 -- 5 update any mo lines with new org id -- 5731584
333
334 /* need to update the SO line with new org (open_order_line_rec.ship_from_org_id) */
335 GMI_RESERVATION_UTIL.PrintLn(' before update to ship_from_org_id of so line '|| open_order_line_rec.line_id);
336
337 UPDATE oe_order_lines_all
338 SET ship_from_org_id = l_organization_id
339 WHERE line_id = open_order_line_rec.line_id;
340
341
342 -- update any OPM mo lines with new org id so that new id will be used for migration down below -- 5731584
343 -- 5731584 start
344
345 UPDATE ic_txn_request_lines
346 SET organization_id = l_organization_id
347 WHERE txn_source_line_id = open_order_line_rec.line_id
348 AND line_status = 7;
349 -- 5731584 end
350
351
352 GMI_RESERVATION_UTIL.PrintLn(' before INV_OPM_Item_Migration.get_ODM_item ');
353
354
355 -- if here , then make sure item is in new org from sub inv - call get odm item to generate
356 -- as per Jatinder Gogna
357 l_failure_count1 := 0;
358 INV_OPM_Item_Migration.get_ODM_item (
359 p_migration_run_id => p_migration_run_id,
360 p_item_id => allocations_for_line_rec.item_id,
361 p_organization_id => l_organization_id,
362 p_mode => NULL,
363 p_commit => FND_API.G_TRUE,
364 x_inventory_item_id => l_inventory_item_id,
365 x_failure_count => l_failure_count1);
366
367 IF (l_failure_count1 > 0) THEN
368 -- Log Error
369 GMI_RESERVATION_UTIL.PrintLn(' Failed to get discrete item. Item id :'||to_char(allocations_for_line_rec.item_id));
370 GMA_COMMON_LOGGING.gma_migration_central_log (
371 p_run_id => p_migration_run_id,
372 p_log_level => FND_LOG.LEVEL_ERROR,
373 p_message_token => 'GMI_MIG_ITEM_MIG_FAILED',
374 p_table_name => 'IC_ITEM_CNV',
375 p_context => 'GET_ODM_ITEM',
376 p_param1 => allocations_for_line_rec.item_id,
377 p_param2 => l_organization_id,
378 p_param3 => NULL,
379 p_param4 => NULL,
380 p_param5 => NULL,
381 p_db_error => NULL,
382 p_app_short_name => 'GMI');
383 l_failure_count := l_failure_count + l_failure_count1;
384 raise FND_API.G_EXC_ERROR;
385 END IF; -- IF (l_failure_count1 > 0) THEN
386
387 /* possibly multiple delivery details are updated */
388 update wsh_delivery_details
389 set organization_id = l_organization_id
390 where source_line_id = open_order_line_rec.line_id;
391
392
393 GMI_RESERVATION_UTIL.PrintLn(' after update to wsh_delivery_details for organization_id '|| l_organization_id);
394 /* update the deliveries to reflect the new organization */
395
396 FOR wsh1 in c_wsh_deliveres (open_order_line_rec.line_id)
397 LOOP
398 GMI_RESERVATION_UTIL.PrintLn(' before update to wsh_delivery_details for organization_id '|| l_organization_id);
399
400 update wsh_new_deliveries
401 set organization_id = l_organization_id
402 where delivery_id = wsh1.delivery_id;
403
404 END LOOP; -- FOR wsh1 in c_wsh_delivery_details(open_order_line_rec.line_id)
405
406
407 l_insert_rsv_rec.organization_id := l_organization_id;
408
409 END IF; -- IF l_subinventory_ind_flag = 'Y' then --
410
411 -- 5574631 end
412
413
414 GMI_RESERVATION_UTIL.PrintLn('primary_reservation_quantity ' || l_insert_rsv_rec.primary_reservation_quantity );
415
416 l_insert_rsv_rec.subinventory_id := NULL;
417 GMI_RESERVATION_UTIL.Println('location '|| allocations_for_line_rec.location);
418
419 l_locator_id := NULL; -- need to initialize this back else will fail
420 l_inventory_location_id := NULL; -- need to initialize this back else will fail
421
422 IF(allocations_for_line_rec.location <> l_IC$DEFAULT_LOCT) THEN
423
424 OPEN get_locator_id(allocations_for_line_rec.whse_code,allocations_for_line_rec.location);
425 FETCH get_locator_id INTO l_locator_id, l_inventory_location_id; -- use l_inventory_location_id for get_odm_lot as per J Gogna -
426 CLOSE get_locator_id;
427
428 GMI_RESERVATION_UTIL.Println('location becomes locator_id '|| l_locator_id);
429 -- as per Jatinder Gogna to support part of bug fix 5595222
430 -- Create locator in discrete ( dynamic locator)
431 IF (l_locator_id is NULL) THEN
432 GMI_RESERVATION_UTIL.Println('location (l_locator_id) was NULL so calling inv_migrate_process_org.create_location');
433 l_failure_count1 := 0;
434 inv_migrate_process_org.create_location(
435 p_migration_run_id => p_migration_run_id,
436 p_organization_id => l_organization_id,
437 p_subinventory_code => allocations_for_line_rec.whse_code,
438 p_location => allocations_for_line_rec.location,
439 p_loct_desc => allocations_for_line_rec.location,
440 p_start_date_active => sysdate,
441 p_commit => FND_API.G_TRUE,
442 x_location_id => l_locator_id,
443 x_failure_count => l_failure_count1,
444 p_segment2 => NULL,
445 p_segment3 => NULL,
446 p_segment4 => NULL,
447 p_segment5 => NULL,
448 p_segment6 => NULL,
449 p_segment7 => NULL,
450 p_segment8 => NULL,
451 p_segment9 => NULL,
452 p_segment10 => NULL,
453 p_segment11 => NULL,
454 p_segment12 => NULL,
455 p_segment13 => NULL,
456 p_segment14 => NULL,
457 p_segment15 => NULL,
458 p_segment16 => NULL,
459 p_segment17 => NULL,
460 p_segment18 => NULL,
461 p_segment19 => NULL,
462 p_segment20 => NULL);
463
464 IF (l_failure_count1 > 0) THEN
465 -- Log error
466 GMI_RESERVATION_UTIL.Println('Unable to create the locator for dynamic OPM location :' || allocations_for_line_rec.whse_code ||', '||allocations_for_line_rec.location );
467 GMA_COMMON_LOGGING.gma_migration_central_log (
468 p_run_id => p_migration_run_id,
469 p_log_level => FND_LOG.LEVEL_ERROR,
470 p_message_token => 'GMI_LOC_CREATION_FAILED',
471 p_table_name => 'IC_LOCT_MST',
472 p_context => 'inv_migrate_process_org.create_location',
473 p_param1 => allocations_for_line_rec.whse_code,
474 p_param2 => allocations_for_line_rec.location,
475 p_param3 => NULL,
476 p_param4 => NULL,
477 p_param5 => NULL,
478 p_db_error => NULL,
479 p_app_short_name => 'GMI');
480 l_failure_count := l_failure_count + l_failure_count1;
481 raise FND_API.G_EXC_ERROR;
482 END IF; -- IF (l_failure_count1 > 0) THEN
483 GMI_RESERVATION_UTIL.Println('after calling inv_migrate_process_org.create_location - locator_id '|| l_locator_id);
484
485 END IF; -- IF (l_locator_id is NULL) THEN
486
487
488 l_insert_rsv_rec.locator_id := l_locator_id;
489
490 -- 5574631 need to get correct subinv for the locator_id just retrieved
491 OPEN get_subinv(l_locator_id);
492 FETCH get_subinv INTO l_subinventory_code;
493 CLOSE get_subinv;
494 l_insert_rsv_rec.subinventory_code := l_subinventory_code;
495
496
497 ELSE
498 l_insert_rsv_rec.locator_id := NULL;
499 l_insert_rsv_rec.subinventory_code := NULL;
500
501 END IF; -- IF(allocations_for_line_rec.location <> 'l_IC$DEFAULT_LOCT') THEN
502
503 GMI_RESERVATION_UTIL.Println('locator_id is '|| l_insert_rsv_rec.locator_id);
504 GMI_RESERVATION_UTIL.Println('Before the lot_id if');
505 GMI_RESERVATION_UTIL.Println('allocations_for_line_rec.lot id is '|| allocations_for_line_rec.lot_id);
506
507 IF(allocations_for_line_rec.lot_id > 0 ) THEN
508 GMI_RESERVATION_UTIL.Println('in the lot_id if and lot_id > 0 and = ' || allocations_for_line_rec.lot_id );
509
510 OPEN get_lot_sublot(allocations_for_line_rec.item_id,allocations_for_line_rec.lot_id);
511 FETCH get_lot_sublot INTO l_lot_no,l_sublot_no;
512 CLOSE get_lot_sublot;
513
514
515 -- 5574631
516
517
518 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
519 P_MIGRATION_RUN_ID => p_migration_run_id,
520 P_INVENTORY_ITEM_ID => open_order_line_rec.inventory_item_id,
521 P_LOT_NO => l_lot_no,
522 P_SUBLOT_NO => l_sublot_no,
523 P_ORGANIZATION_ID => l_organization_id, -- 5574631 instead of open_order_line_rec.ship_from_org_id,
524 P_LOCATOR_ID => l_inventory_location_id, -- 5574631 use instead of l_locator_id
525 P_COMMIT => FND_API.G_TRUE,
526 X_LOT_NUMBER => l_odm_lot_num,
527 X_PARENT_LOT_NUMBER => l_parent_lot_num,
528 X_FAILURE_COUNT => l_count
529 );
530
531 GMI_RESERVATION_UTIL.Println('get odm_lot: X_FAILURE_COUNT is '||l_count);
532
533 IF (l_count > 0)
534 THEN
535 -- Log Error
536 GMI_RESERVATION_UTIL.Println('Failed to migrate lot - OPM lot_id = '|| to_char(allocations_for_line_rec.lot_id));
537 GMI_RESERVATION_UTIL.Println('Failed to migrate lot - OPM lot_no = '|| l_lot_no);
538 GMA_COMMON_LOGGING.gma_migration_central_log (
539 p_run_id => p_migration_run_id,
540 p_log_level => FND_LOG.LEVEL_ERROR,
541 p_message_token => 'GMI_MIG_LOT_MIG_FAILED',
542 p_table_name => 'IC_LOTS_MST',
543 p_context => 'GET_ODM_LOT',
544 p_param1 => l_lot_no,
545 p_param2 => INV_GMI_Migration.lot(allocations_for_line_rec.lot_id),
546 p_param3 => l_organization_id,
547 p_param4 => l_locator_id,
548 p_param5 => NULL,
549 p_db_error => NULL,
550 p_app_short_name => 'GMI');
551 raise FND_API.G_EXC_ERROR;
552 ELSE
553 GMI_RESERVATION_UTIL.Println('get odm_lot succes: l_odm_lot_num is '||l_odm_lot_num);
554
555 END IF;
556 -- 5574631
557
558
559 ELSE
560 -- Check if item is not lot controlled. If not nullify the lot fields.
561 IF l_lot_control_code = 1 THEN
562 l_odm_lot_num := NULL;
563 l_parent_lot_num := NULL;
564 END IF;
565 END IF; -- IF(allocations_for_line_rec.lot_id > 0 ) THEN
566
567 GMI_RESERVATION_UTIL.Println('after the lot_id if');
568
569 l_insert_rsv_rec.lot_number := l_odm_lot_num;
570 l_insert_rsv_rec.lot_number_id := NULL;
571 l_insert_rsv_rec.pick_slip_number := NULL;
572 l_insert_rsv_rec.lpn_id := NULL;
573 l_insert_rsv_rec.attribute_category := NULL;
574 l_insert_rsv_rec.attribute1 := NULL;
575 l_insert_rsv_rec.attribute2 := NULL;
576 l_insert_rsv_rec.attribute3 := NULL;
577 l_insert_rsv_rec.attribute4 := NULL;
578 l_insert_rsv_rec.attribute5 := NULL;
579 l_insert_rsv_rec.attribute6 := NULL;
580 l_insert_rsv_rec.attribute7 := NULL;
581 l_insert_rsv_rec.attribute8 := NULL;
582 l_insert_rsv_rec.attribute9 := NULL;
583 l_insert_rsv_rec.attribute10 := NULL;
584 l_insert_rsv_rec.attribute11 := NULL;
585 l_insert_rsv_rec.attribute12 := NULL;
586 l_insert_rsv_rec.attribute13 := NULL;
587 l_insert_rsv_rec.attribute14 := NULL;
588 l_insert_rsv_rec.attribute15 := NULL;
589 l_insert_rsv_rec.ship_ready_flag := 2;
590 l_insert_rsv_rec.detailed_quantity := 0;
591
592
593 inv_reservation_pvt.print_rsv_rec(l_insert_rsv_rec);
594
595 GMI_RESERVATION_UTIL.PrintLn('about to call create_reservation');
596
597 fnd_msg_pub.initialize;
598 INV_RESERVATION_PUB.create_reservation(
599 p_api_version_number => 1.0
600 , p_init_msg_lst => fnd_api.g_false
601 , x_return_status => l_api_return_status
602 , x_msg_count => l_msg_count
603 , x_msg_data => l_msg_data
604 , p_rsv_rec => l_insert_rsv_rec
605 , p_serial_number => l_dummy_sn
606 , x_serial_number => l_dummy_sn
607 , p_partial_reservation_flag => fnd_api.g_true
608 , p_force_reservation_flag => fnd_api.g_false
609 , p_validation_flag => 'Q'
610 , x_quantity_reserved => l_qty_reserved
611 , x_secondary_quantity_reserved=> l_sec_qty_reserved
612 , x_reservation_id => l_reservation_id
613 );
614
615 -- Return an error if the create reservation call failed
616 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
617 GMI_RESERVATION_UTIL.PrintLn(' Create reservation failed');
618 GMI_RESERVATION_UTIL.PrintLn(l_msg_data);
619 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
620 FOR i in 1..l_msg_count LOOP
621
622 GMA_COMMON_LOGGING.gma_migration_central_log (
623 p_run_id => p_migration_run_id,
624 p_log_level => FND_LOG.LEVEL_ERROR,
625 p_message_token => 'GMI_UNEXPECTED_ERROR',
626 p_table_name => 'IC_TRAN_PND',
627 p_context => 'CREATE RESERVATION',
628 p_token1 => 'ERROR',
629 p_param1 => fnd_msg_pub.get_detail(i, NULL),
630 p_param2 => NULL,
631 p_param3 => NULL,
632 p_param4 => NULL,
633 p_param5 => NULL,
634 p_db_error => NULL,
635 p_app_short_name => 'GMI');
636 END LOOP;
637 -- insert more comprehensive data into gma log table.
638
639 GMA_COMMON_LOGGING.gma_migration_central_log (
640 p_run_id => p_migration_run_id,
641 p_log_level => FND_LOG.LEVEL_ERROR,
642 p_message_token => 'GMI_UNEXPECTED_ERROR',
643 p_table_name => 'IC_TRAN_PND',
644 p_context => 'CREATE RESERVATION',
645 p_param1 => INV_GMI_Migration.item(allocations_for_line_rec.item_id),
646 p_param2 => INV_GMI_Migration.lot(allocations_for_line_rec.lot_id),
647 p_param3 => allocations_for_line_rec.whse_code,
648 p_param4 => l_insert_rsv_rec.subinventory_code,
649 p_param5 => l_insert_rsv_rec.locator_id,
650 p_db_error => NULL,
651 p_app_short_name => 'GMI');
652
653 fnd_message.set_name('INV', 'INV_CREATE_RSV_FAILED');
654 fnd_msg_pub.ADD;
655 RAISE fnd_api.g_exc_unexpected_error;
656 ELSE
657 GMI_RESERVATION_UTIL.PrintLn(' Create reservation succeeded - Reservation_id : '|| l_reservation_id);
658 GMI_RESERVATION_UTIL.PrintLn(' l_qty_reserved : '|| l_qty_reserved);
659 GMI_RESERVATION_UTIL.PrintLn(' l_sec_qty_reserved : '|| l_sec_qty_reserved);
660 GMI_RESERVATION_UTIL.PrintLn(' Created at least one detailed reservation ');
661 /* Created atleast one detailed reservation */
662 l_detail_reservations := 1;
663 END IF; -- IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
664
665
666 EXCEPTION
667 --WHEN NON_INV_ITEM THEN
668 -- GMI_RESERVATION_UTIL.PrintLn('Non inventory item..doing nothing');
669 -- NULL;
670
671 WHEN FND_API.g_exc_unexpected_error THEN
672 l_failure_count := l_failure_count + 1;
673
674
675 WHEN OTHERS THEN
676 /* Failure count goes up by 1 */
677 l_failure_count := l_failure_count+1;
678 gma_common_logging.gma_migration_central_log (
679 p_run_id => p_migration_run_id
680 , p_log_level => FND_LOG.LEVEL_UNEXPECTED
681 , p_app_short_name =>'GML'
682 , p_message_token => 'GML_MIGRATION_DB_ERROR'
683 , p_db_error => sqlerrm
684 , p_table_name => 'IC_TRAN_PND'
685 , p_context => 'TRANSACTIONS');
686
687 END; /* Begin for allocations loop */
688
689 END LOOP; /* FOR allocations_for_line_rec IN get_allocations_for_line(open_order_line_rec.line_id) */
690
691 IF( l_detail_reservations = 0 ) THEN
692
693 /*IF open_order_line_rec.source = 'WDD' THEN
694 Open Cur_get_sch_sta_code;
695 Fetch Cur_get_sch_sta_code into open_order_line_rec.schedule_status_code;
696 Close Cur_get_sch_sta_code;
697 END IF;*/
698
699 IF(open_order_line_rec.schedule_status_code = 'SCHEDULED' AND l_lot_divisible_flag <> 'Y') THEN
700 /* once this is implemented please uncomment the cuRsor Cur_get_sch_sta_code above */
701 NULL;
702 END IF;
703 END IF; -- IF( l_detail_reservations = 0 ) THEN
704
705 ELSE
706 -- NON INV ITEM
707 GMI_RESERVATION_UTIL.PrintLn('Non inventory item..so doing nothing for reservations or move orders');
708
709 END IF; -- IF l_opm_noninv_ind = 0 THEN -- 5475003 only check if an inventory item
710
711
712 /****** If there've not been any detail reservations, create a High Level Reservation depending on Sheduling */
713
714 --- NOTA BENE - we do not create move orders for a non-inventory item in R12 (in 11i OPM did )
715
716 FOR move_order_line_rec IN get_mo_line(open_order_line_rec.line_id)
717 LOOP
718
719
720 BEGIN
721
722 IF l_opm_noninv_ind = 0 THEN -- -- 5475003 rework 10/19 - only create Move Orders if an inventory item (control from ic_item_mst)
723 /* - we do not create move orders for a non-inventory item in R12 (in 11i OPM we did ) */
724
725 GMI_RESERVATION_UTIL.PrintLn('In move_order_line_rec cursor and this is an inventory item - Sales Order line_id : ic_txn_request_lines line id '|| to_char(open_order_line_rec.line_id)||':'||to_char(move_order_line_rec.line_id));
726
727 /* Look for the move order header, has an mtl move order header been created for this already? */
728 OPEN get_move_order_header (move_order_line_rec.line_id);
729 FETCH get_move_order_header INTO l_ic_mo_header_rec;
730 CLOSE get_move_order_header;
731 GMI_RESERVATION_UTIL.PrintLn('in move order header id and attribute15 '|| to_char(l_ic_mo_header_rec.header_id)||' and '||l_ic_mo_header_rec.attribute15);
732
733 -- need to get location_id and pick_grouping rule_id from wsh_shipping_parameters
734 -- for grouping_rule_id for MO header and for to_locator_id for MO line -- 5601081
735 OPEN get_shipping_parameters(move_order_line_rec.organization_id);
736 FETCH get_shipping_parameters INTO l_to_locator_id,l_grouping_rule_id;
737 CLOSE get_shipping_parameters;
738 GMI_RESERVATION_UTIL.PrintLn('shipping params l_to_locator_id = ' || l_to_locator_id );
739
740 IF(l_ic_mo_header_rec.attribute15 IS NOT NULL) THEN
741
742 --l_mo_header_id := to_number(l_ic_mo_header_rec.header_id); -- 5111050 COMMENTED OUT
743 l_mo_header_id := to_number(l_ic_mo_header_rec.attribute15); -- 5111050
744
745 ELSE /* No move order header exists, create one. */
746
747 GMI_RESERVATION_UTIL.PrintLn('if here then l_ic_mo_header_rec.attribute15 is null');
748
749
750 l_trohdr_rec := l_empty_trohdr_rec;
751 --not initializing this was causing problems of unique constraints.
752 l_trohdr_rec.header_id := inv_transfer_order_pvt.get_next_header_id;
753 l_trohdr_rec.created_by := FND_GLOBAL.user_id;
754 l_trohdr_rec.request_number := l_ic_mo_header_rec.request_number;
755 l_trohdr_rec.creation_date := sysdate;
756 l_trohdr_rec.date_required := sysdate;
757 l_trohdr_rec.from_subinventory_code := l_ic_mo_header_rec.from_subinventory_code;
758 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
759 l_trohdr_rec.last_updated_by := FND_GLOBAL.user_id;
760 l_trohdr_rec.last_update_date := sysdate;
761 l_trohdr_rec.last_update_login := FND_GLOBAL.login_id;
762 l_trohdr_rec.organization_id := l_ic_mo_header_rec.organization_id;
763 l_trohdr_rec.status_date := sysdate;
764 l_trohdr_rec.to_subinventory_code := l_ic_mo_header_rec.to_subinventory_code;
765 --l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_ISSUE; -- not sure of this
766 l_trohdr_rec.db_flag := FND_API.G_TRUE;
767 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
768 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_PICK_WAVE; -- 5601081 type changed
769 l_trohdr_rec.grouping_rule_id := l_grouping_rule_id ; -- 5601081
770 GMI_RESERVATION_UTIL.PrintLn('before call to INV_Move_Order_PUB.Create_move_order_header');
771
772 fnd_msg_pub.initialize;
773 INV_Move_Order_PUB.Create_move_order_header ( p_api_version_number => 1.0 ,
774 p_init_msg_list => 'T',
775 p_commit => FND_API.G_FALSE,
776 p_return_values => FND_API.G_TRUE,
777 x_return_status => l_return_status,
778 x_msg_count => l_msg_count,
779 x_msg_data => l_msg_data,
780 p_trohdr_rec => l_trohdr_rec,
781 p_trohdr_val_rec => l_trohdr_val_rec,
782 x_trohdr_rec => l_trohdr_rec,
783 x_trohdr_val_rec => l_trohdr_val_rec
784 );
785
786 IF l_return_status <> fnd_api.g_ret_sts_success THEN
787 GMI_RESERVATION_UTIL.PrintLn(' Create_move_order_header failed ');
788 GMI_RESERVATION_UTIL.PrintLn(l_msg_data);
789 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
790 FOR i in 1..l_msg_count LOOP
791
792 GMA_COMMON_LOGGING.gma_migration_central_log (
793 p_run_id => p_migration_run_id,
794 p_log_level => FND_LOG.LEVEL_ERROR,
795 p_message_token => 'GMI_UNEXPECTED_ERROR',
796 p_table_name => 'mtl_txn_request_headers',
797 p_context => 'Create_move_order_header',
798 p_token1 => 'ERROR',
799 p_param1 => fnd_msg_pub.get_detail(i, NULL),
800 p_param2 => NULL,
801 p_param3 => NULL,
802 p_param4 => NULL,
803 p_param5 => NULL,
804 p_db_error => NULL,
805 p_app_short_name => 'GMI');
806 END LOOP;
807 -- insert more comprehensive data into gma log table.
808
809 GMA_COMMON_LOGGING.gma_migration_central_log (
810 p_run_id => p_migration_run_id,
811 p_log_level => FND_LOG.LEVEL_ERROR,
812 p_message_token => 'INV_ERROR_CREATING_MO',
813 p_table_name => 'mtl_txn_request_headers',
814 p_context => 'Create_move_order_header',
815 p_param1 => l_ic_mo_header_rec.request_number,
816 p_param2 => l_ic_mo_header_rec.organization_id,
817 p_param3 => NULL,
818 p_param4 => NULL,
819 p_param5 => NULL,
820 p_db_error => NULL,
821 p_app_short_name => 'INV');
822
823 fnd_message.set_name('INV', 'INV_ERROR_CREATING_MO');
824 fnd_msg_pub.ADD;
825 RAISE fnd_api.g_exc_unexpected_error;
826 ELSE
827 GMI_RESERVATION_UTIL.PrintLn(' Create_move_order_header succeeded - header_id : '|| l_trohdr_rec.header_id);
828 l_mo_header_id := l_trohdr_rec.header_id;
829 -- only do this if the create move order header above suceeded
830 /* Update attribute15 in ic_txn_request_headers */
831 GMI_RESERVATION_UTIL.PrintLn(' before update to attribute15 of in ic_txn_request_headers: '||to_char(l_trohdr_rec.header_id));
832
833 UPDATE ic_txn_request_headers
834 SET attribute15 = to_char(l_trohdr_rec.header_id)
835 WHERE header_id = l_ic_mo_header_rec.header_id;
836
837 GMI_RESERVATION_UTIL.PrintLn(' after update to attribute15 of in ic_txn_request_headers');
838
839 END IF; -- IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
840
841 END IF; -- IF(l_ic_mo_header_rec.attribute15 IS NOT NULL) THEN
842
843 /* Now Create a line */
844 GMI_RESERVATION_UTIL.PrintLn(' Now for lines - ic_txn_request_lines attribute15: '||move_order_line_rec.attribute15);
845
846 IF(move_order_line_rec.attribute15 IS NOT NULL) THEN
847 l_mo_line_id := to_number(move_order_line_rec.attribute15);
848
849 ELSE /* Create a move order line */
850
851 GMI_RESERVATION_UTIL.PrintLn('debug messages start for create move order line ********************************');
852 GMI_RESERVATION_UTIL.PrintLn('ic_txn_request_lines attribute15 = null ');
853 GMI_RESERVATION_UTIL.PrintLn('l_trohdr_rec.header_id : '|| l_trohdr_rec.header_id);
854 GMI_RESERVATION_UTIL.PrintLn('l_mo_header_id : '|| l_mo_header_id);
855 GMI_RESERVATION_UTIL.PrintLn('from_subinventory_code : '|| move_order_line_rec.from_subinventory_code);
856 GMI_RESERVATION_UTIL.PrintLn('inventory_item_id : '|| move_order_line_rec.inventory_item_id);
857 GMI_RESERVATION_UTIL.PrintLn('line_number : '|| l_line_num);
858 GMI_RESERVATION_UTIL.PrintLn('organization_id : '|| move_order_line_rec.organization_id);
859 GMI_RESERVATION_UTIL.PrintLn('quantity : '|| move_order_line_rec.quantity);
860 GMI_RESERVATION_UTIL.PrintLn('secondary_quantity : '|| move_order_line_rec.secondary_quantity);
861 GMI_RESERVATION_UTIL.PrintLn('to_subinventory_code : '|| move_order_line_rec.to_subinventory_code);
862 GMI_RESERVATION_UTIL.PrintLn('uom_code : '|| move_order_line_rec.uom_code);
863 GMI_RESERVATION_UTIL.PrintLn('secondary_uom : '|| move_order_line_rec.secondary_uom_code);
864 GMI_RESERVATION_UTIL.PrintLn('grade_code : '|| move_order_line_rec.qc_grade);
865 GMI_RESERVATION_UTIL.PrintLn('secondary_quantity_delivered : '|| move_order_line_rec.secondary_quantity_delivered);
866 GMI_RESERVATION_UTIL.PrintLn('secondary_quantity_detailed : '|| move_order_line_rec.secondary_quantity_detailed);
867
868 -- need to get sales_order_id from mtl_sales_orders for this order to populate field txn_source_id -- 5601081
869
870 OPEN get_sales_order_id (open_order_line_rec.header_id);
871 FETCH get_sales_order_id INTO l_sales_order_id;
872 CLOSE get_sales_order_id;
873 GMI_RESERVATION_UTIL.PrintLn('l_sales_order_id : '|| l_sales_order_id);
874
875 begin
876 -- need to get delivery_detail_id from wsh_delivery_details for this old ic MO in order to populate field txn_source_line_detail_id -- 5601081
877 OPEN get_delivery_details(move_order_line_rec.line_id,open_order_line_rec.line_id);
878 FETCH get_delivery_details INTO l_delivery_detail_id;
879 CLOSE get_delivery_details;
880 EXCEPTION
881 WHEN OTHERS THEN
882
883 GMI_RESERVATION_UTIL.PrintLn(' no data found for delivery details before create MO line so' );
884 GMI_RESERVATION_UTIL.PrintLn('not erroring out here');
885 NULL;
886
887 end;
888
889 GMI_RESERVATION_UTIL.PrintLn('l_delivery_detail_id : '|| l_delivery_detail_id);
890
891 l_line_num := l_line_num + 1;
892 --l_trolin_tbl(l_order_count).header_id := l_trohdr_rec.header_id; -- 5111050 commented out
893 l_trolin_tbl(l_order_count).header_id := l_mo_header_id; -- 5111050 5474923
894 l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
895 l_trolin_tbl(l_order_count).creation_date := sysdate;
896 l_trolin_tbl(l_order_count).date_required := sysdate;
897 l_trolin_tbl(l_order_count).from_subinventory_code := move_order_line_rec.from_subinventory_code;
898 l_trolin_tbl(l_order_count).inventory_item_id := move_order_line_rec.inventory_item_id;
899 l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
900 l_trolin_tbl(l_order_count).last_update_date := sysdate;
901 l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
902 l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
903 l_trolin_tbl(l_order_count).line_number := l_line_num;
904 l_trolin_tbl(l_order_count).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
905 l_trolin_tbl(l_order_count).organization_id := move_order_line_rec.organization_id; -- pal this needs to be right if suborg mapping
906 l_trolin_tbl(l_order_count).quantity := move_order_line_rec.quantity;
907 l_trolin_tbl(l_order_count).quantity_delivered := 0; --move_order_line_rec.quantity_delivered; -- 5601081
908 l_trolin_tbl(l_order_count).quantity_detailed := 0; --move_order_line_rec.quantity_detailed; -- 5601081
909 l_trolin_tbl(l_order_count).secondary_quantity := move_order_line_rec.secondary_quantity;
910 l_trolin_tbl(l_order_count).secondary_quantity_delivered := 0; --move_order_line_rec.secondary_quantity_delivered; -- 5601081
911 l_trolin_tbl(l_order_count).secondary_quantity_detailed := 0; -- move_order_line_rec.secondary_quantity_detailed; -- 5601081
912 l_trolin_tbl(l_order_count).to_locator_id := l_to_locator_id; -- 5601081
913
914 l_trolin_tbl(l_order_count).status_date := sysdate;
915 l_trolin_tbl(l_order_count).to_subinventory_code := move_order_line_rec.to_subinventory_code;
916 l_trolin_tbl(l_order_count).uom_code := move_order_line_rec.uom_code;
917 l_trolin_tbl(l_order_count).secondary_uom := move_order_line_rec.secondary_uom_code;
918
919 l_trolin_tbl(l_order_count).transaction_type_id := move_order_line_rec.transaction_type_id; -- 5601081
920 l_trolin_tbl(l_order_count).transaction_source_type_id := 2; -- 5601081
921 l_trolin_tbl(l_order_count).txn_source_line_id := open_order_line_rec.line_id; -- 5601081
922 l_trolin_tbl(l_order_count).txn_source_id := l_sales_order_id; -- 5601081
923 l_trolin_tbl(l_order_count).txn_source_line_detail_id := l_delivery_detail_id; -- 5601081
924
925 l_trolin_tbl(l_order_count).grade_code := move_order_line_rec.qc_grade; -- 5703365
926
927 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
928 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
929
930
931 fnd_msg_pub.initialize;
932 INV_Move_Order_PUB.Create_Move_Order_Lines (
933 p_api_version_number => 1.0 ,
934 p_init_msg_list => 'T',
935 p_commit => FND_API.G_FALSE,
936 x_return_status => l_return_status,
937 x_msg_count => l_msg_count,
938 x_msg_data => l_msg_data,
939 p_trolin_tbl => l_trolin_tbl,
940 p_trolin_val_tbl => l_trolin_val_tbl,
941 x_trolin_tbl => l_trolin_tbl,
942 x_trolin_val_tbl => l_trolin_val_tbl);
943
944 IF l_return_status <> fnd_api.g_ret_sts_success THEN
945 GMI_RESERVATION_UTIL.PrintLn(' Create_Move_Order_Lines failed ');
946 GMI_RESERVATION_UTIL.PrintLn(l_msg_data);
947 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
948 FOR i in 1..l_msg_count LOOP
949
950 GMA_COMMON_LOGGING.gma_migration_central_log (
951 p_run_id => p_migration_run_id,
952 p_log_level => FND_LOG.LEVEL_ERROR,
953 p_message_token => 'GMI_UNEXPECTED_ERROR',
954 p_table_name => 'mtl_txn_request_lines',
955 p_context => 'Create_Move_Order_Lines',
956 p_token1 => 'ERROR',
957 p_param1 => fnd_msg_pub.get_detail(i, NULL),
958 p_param2 => NULL,
959 p_param3 => NULL,
960 p_param4 => NULL,
961 p_param5 => NULL,
962 p_db_error => NULL,
963 p_app_short_name => 'GMI');
964 END LOOP;
965 -- insert more comprehensive data into gma log table.
966
967 GMA_COMMON_LOGGING.gma_migration_central_log (
968 p_run_id => p_migration_run_id,
969 p_log_level => FND_LOG.LEVEL_ERROR,
970 p_message_token => 'INV_ERROR_CREATING_MO',
971 p_table_name => 'mtl_txn_request_lines',
972 p_context => 'Create_Move_Order_Lines',
973 p_param1 => l_mo_header_id,
974 p_param2 => move_order_line_rec.inventory_item_id,
975 p_param3 => open_order_line_rec.line_id,
976 p_param4 => NULL,
977 p_param5 => NULL,
978 p_db_error => NULL,
979 p_app_short_name => 'INV');
980
981 fnd_message.set_name('INV', 'INV_ERROR_CREATING_MO');
982 fnd_msg_pub.ADD;
983 RAISE fnd_api.g_exc_unexpected_error;
984 ELSE
985 GMI_RESERVATION_UTIL.PrintLn(' Create_move_order_lines succeeded - line_id : '|| to_char(l_trolin_tbl(l_order_count).line_id));
986
987 /* Update OPM move order line attribute15 */
988
989 l_mo_line_id := l_trolin_tbl(l_order_count).line_id;
990
991 GMI_RESERVATION_UTIL.PrintLn(' before update of ic_txn_request_lines to attribute15 to line: '||to_char(l_mo_line_id));
992
993 UPDATE ic_txn_request_lines
994 SET attribute15 = to_char(l_trolin_tbl(l_order_count).line_id)
995 WHERE line_id = move_order_line_rec.line_id;
996
997 GMI_RESERVATION_UTIL.PrintLn(' after update to ic_txn_request_lines to attribute15 to line: '||to_char(l_mo_line_id));
998
999 l_line_id := l_trolin_tbl(l_order_count).line_id;
1000 -- 5601081 rework
1001 -- need to populate to_locator_id as Create_move_order_line API
1002 -- is not honoring to_locator_id input for MOL
1003
1004 UPDATE mtl_txn_request_lines
1005 SET to_locator_id = l_to_locator_id
1006 WHERE line_id = l_line_id;
1007 GMI_RESERVATION_UTIL.PrintLn(' newly created move_order_line :-' || l_line_id);
1008 GMI_RESERVATION_UTIL.PrintLn(' locator_id updated to :- ' || l_to_locator_id);
1009
1010 END IF; -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
1011
1012
1013
1014
1015 END IF; -- IF(move_order_line_rec.attribute15 IS NOT NULL) THEN
1016
1017 ELSE
1018 /* do not create discrete move order as this item is non inventory item. */
1019 NULL;
1020 END IF; -- IF l_opm_noninv_ind = 0 THEN -- 5475003 rework 10/19
1021
1022 /* UPDATE the WSH_DELIVERY_DETAILS with this new move order info */
1023 GMI_RESERVATION_UTIL.PrintLn(' before update of wsh_delivery_detail for move order line id');
1024
1025 -- need to incororate this into fix for non inv items and MOs
1026
1027 FOR delivery_detail_rec IN get_delivery_details(move_order_line_rec.line_id,open_order_line_rec.line_id)
1028 LOOP
1029 GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail delivery_detail_rec.delivery_detail_id '||to_char(delivery_detail_rec.delivery_detail_id));
1030 GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail old ic_txn_request_lines line_id '||to_char(move_order_line_rec.line_id));
1031 GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail open_order_line_rec.line_id '||to_char(open_order_line_rec.line_id));
1032 GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail l_opm_noninv_ind '||to_char(l_opm_noninv_ind ));
1033
1034 -- if non_inv item need to set dd to staged 5475003
1035 IF l_opm_noninv_ind = 1 THEN -- 5475003 rework - if here then this is non inventory item (as per OPM)
1036 UPDATE wsh_delivery_details
1037 SET released_status = 'Y', -- 5475003
1038 picked_quantity = move_order_line_rec.quantity, -- 5475003
1039 picked_quantity2 = move_order_line_rec.secondary_quantity -- 5475003
1040 WHERE delivery_detail_id = delivery_detail_rec.delivery_detail_id;
1041
1042 ELSE
1043 UPDATE wsh_delivery_details
1044 SET move_order_line_id = l_mo_line_id
1045 WHERE delivery_detail_id = delivery_detail_rec.delivery_detail_id;
1046
1047 END IF; -- IF l_opm_noninv_ind = 1 THEN -- 5475003
1048
1049
1050 GMI_RESERVATION_UTIL.PrintLn(' in loop after update');
1051
1052 END LOOP; /* FOR delivery_detail_rec IN get_delivery_details(move_order_line_rec.line_id,open_order_line_rec.line_id) */
1053
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056
1057 GMI_RESERVATION_UTIL.PrintLn(' in When Others of move order loop : sqlerrm '||sqlerrm);
1058 GMI_RESERVATION_UTIL.PrintLn(' NULL so not erroring out here in move order loop');
1059
1060 NULL;
1061
1062 END; /* Begin for Move Order Loop */
1063
1064
1065 END LOOP; /* FOR move_order_line_rec IN get_mo_line(open_order_line_rec.line_id) */
1066
1067
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 NULL;
1071 END; /* Begin for open_order_line_rec Loop */
1072
1073 END LOOP; /* FOR open_order_line_rec IN get_open_order_lines LOOP */
1074
1075 GMI_RESERVATION_UTIL.println('END OF GML_OPM_OM_CONV_MIG_PKG.Migrate_opm_om_open_lines OM MIGRATION RUN ');
1076
1077 /* End by logging a message that the migration has been succesful IF SO */
1078
1079 if L_FAILURE_COUNT < 1 THEN
1080 gma_common_logging.gma_migration_central_log (
1081 p_run_id => p_migration_run_id
1082 , p_log_level => FND_LOG.LEVEL_PROCEDURE
1083 , p_app_short_name => 'GML'
1084 , p_message_token => 'GML_MIGRATION_TABLE_SUCCESS'
1085 , p_table_name => NULL
1086 , p_context => 'OPEN_ORDER_LINES'
1087 , p_param1 => l_success_count
1088 , p_param2 => l_failure_count );
1089 else
1090
1091 gma_common_logging.gma_migration_central_log (
1092 p_run_id => p_migration_run_id
1093 , p_log_level => FND_LOG.LEVEL_PROCEDURE
1094 , p_app_short_name => 'GML'
1095 , p_message_token => 'GML_MIGRATION_TABLE_FAILURE'
1096 , p_table_name => NULL
1097 , p_context => 'OPEN_ORDER_LINES'
1098 , p_param1 => l_success_count
1099 , p_param2 => l_failure_count );
1100 end if; -- if L_FAILURE_COUNT < 1 THEN
1101
1102 --Lets save the changes now based on the commit parameter
1103 IF p_commit = FND_API.G_TRUE THEN
1104 COMMIT;
1105 END IF;
1106
1107
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 gma_common_logging.gma_migration_central_log (
1112 p_run_id => p_migration_run_id
1113 , p_log_level => FND_LOG.LEVEL_UNEXPECTED
1114 , p_app_short_name =>'GML'
1115 , p_message_token => 'GML_MIGRATION_DB_ERROR'
1116 , p_db_error => sqlerrm
1117 , p_table_name => NULL
1118 , p_context => 'OPEN_ORDER_LINES');
1119
1120 END Migrate_opm_om_open_lines;
1121
1122 END GML_OPM_OM_CONV_MIG_PKG;
1123