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