[Home] [Help]
PACKAGE BODY: APPS.INV_MIGRATE_PROCESS_ORG
Source
1 PACKAGE BODY INV_MIGRATE_PROCESS_ORG AS
2 /* $Header: INVPOMGB.pls 120.21 2008/02/19 06:49:15 rlnagara ship $ */
3
4 /*====================================================================
5 -- PROCEDURE:
6 -- sync_whse_subinventory
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL procedure is used to create the subinventory with the name
10 -- of whse for mtl organization id.
11 --
12 --
13 -- PARAMETERS:
14 -- P_migration_run_id - id to use to right to migration log
15 -- x_failure_count - Number of failures occurred.
16 --
17 -- SYNOPSIS:
18 -- sync_whse_subinventory (p_migartion_id => l_migration_id,
19 -- p_commit => 'T',
20 -- x_failure_count => l_failure_count );
21 --
22 -- HISTORY
23 -- 05-APR-2007 ACATALDO Bug 5727749 Remove calls to the central
24 -- logging API that are already handled
25 -- by the calling layer.
26 -- 06-APR-2007 ACATALDO Bug 5955262 - Used correct token in exception
27 -- block for migration table failure.
28 --====================================================================*/
29
30 PROCEDURE sync_whse_subinventory (P_migration_run_id IN NUMBER,
31 P_whse_code IN VARCHAR2,
32 P_whse_name IN VARCHAR2,
33 P_organization_id IN NUMBER,
34 P_commit IN VARCHAR2,
35 X_failure_count OUT NOCOPY NUMBER) IS
36
37 CURSOR Cur_check_subinventory IS
38 SELECT 1
39 FROM dual
40 WHERE EXISTS (SELECT 1
41 FROM mtl_secondary_inventories
42 WHERE secondary_inventory_name = P_whse_code
43 AND organization_id = P_organization_id);
44
45 CURSOR Cur_get_details(V_organization_id NUMBER) IS
46 SELECT *
47 FROM mtl_parameters
48 WHERE organization_id = V_organization_id;
49
50 --Local Variables
51 l_migrate_count NUMBER;
52 l_migration_id NUMBER;
53 l_locator_type NUMBER;
54 l_temp NUMBER;
55 l_rowid VARCHAR2(80);
56
57 --Row type declarations
58 l_details Cur_get_details%ROWTYPE;
59 BEGIN
60 l_migration_id := P_migration_run_id;
61 X_failure_count := 0;
62
63 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
64 -- logging API that are already handled by the calling layer.
65 -- GMA_COMMON_LOGGING.gma_migration_central_log (
66 -- p_run_id => l_migration_id,
67 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
68 -- p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
69 -- p_table_name => 'IC_WHSE_MST',
70 -- p_context => 'WHSE_SYNC',
71 -- p_app_short_name => 'GMA');
72
73 OPEN Cur_check_subinventory;
74 FETCH Cur_check_subinventory INTO l_temp;
75 IF (Cur_check_subinventory%NOTFOUND) THEN
76 CLOSE Cur_check_subinventory;
77 --Fetch some details from mtl_parameters table to pass the parameter values.
78 OPEN Cur_get_details(P_organization_id);
79 FETCH Cur_get_details INTO l_details;
80 CLOSE Cur_get_details;
81 IF (l_details.stock_locator_control_code = 4) THEN
82 l_locator_type := 5;
83 ELSE
84 l_locator_type := 1;
85 END IF;
86
87 --Now insert the warehouse into mtl_secondary_inventories table
88 mtl_secondary_inventories_pkg.insert_row (
89 x_rowid => l_rowid
90 , x_secondary_inventory_name => P_whse_code
91 , x_organization_id => P_organization_id
92 , x_last_update_date => SYSDATE
93 , x_last_updated_by => 0
94 , x_creation_date => SYSDATE
95 , x_created_by => 0
96 , x_last_update_login => 0
97 , x_description => p_whse_name
98 , x_disable_date => NULL
99 , x_inventory_atp_code => 1
100 , x_availability_type => 1
101 , x_reservable_type => 1
102 , x_locator_type => l_locator_type
103 , x_picking_order => NULL
104 , x_dropping_order => NULL
105 , x_material_account => l_details.material_account
106 , x_material_overhead_account => l_details.material_overhead_account
107 , x_resource_account => l_details.resource_account
108 , x_overhead_account => l_details.overhead_account
109 , x_outside_processing_account => l_details.outside_processing_account
110 , x_quantity_tracked => 1
111 , x_asset_inventory => 1
112 , x_source_type => NULL
113 , x_source_subinventory => NULL
114 , x_source_organization_id => NULL
115 , x_requisition_approval_type => NULL
116 , x_expense_account => l_details.expense_account
117 , x_encumbrance_account => l_details.encumbrance_account
118 , x_attribute_category => NULL
119 , x_attribute1 => NULL
120 , x_attribute2 => NULL
121 , x_attribute3 => NULL
122 , x_attribute4 => NULL
123 , x_attribute5 => NULL
124 , x_attribute6 => NULL
125 , x_attribute7 => NULL
126 , x_attribute8 => NULL
127 , x_attribute9 => NULL
128 , x_attribute10 => NULL
129 , x_attribute11 => NULL
130 , x_attribute12 => NULL
131 , x_attribute13 => NULL
132 , x_attribute14 => NULL
133 , x_attribute15 => NULL
134 , x_preprocessing_lead_time => NULL
135 , x_processing_lead_time => NULL
136 , x_postprocessing_lead_time => NULL
137 , x_demand_class => NULL
138 , x_project_id => NULL
139 , x_task_id => NULL
140 , x_subinventory_usage => NULL
141 , x_notify_list_id => NULL
142 , x_depreciable_flag => 2
143 , x_location_id => NULL
144 , x_status_id => 1
145 , x_default_loc_status_id => 1
146 , x_lpn_controlled_flag => 0
147 , x_default_cost_group_id => l_details.default_cost_group_id
148 , x_pick_uom_code => NULL
149 , x_cartonization_flag => 0
150 , x_planning_level => 2
151 , x_default_count_type_code => 2
152 , x_subinventory_type => 1
153 , x_enable_bulk_pick => 'N');
154 END IF;
155
156 IF (Cur_check_subinventory%ISOPEN) THEN
157 CLOSE Cur_check_subinventory;
158 END IF;
159
160 UPDATE IC_LOCT_MST
161 SET locator_id = inventory_location_id
162 WHERE whse_code = P_whse_code;
163
164 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
165 -- logging API that are already handled by the calling layer.
166 -- GMA_COMMON_LOGGING.gma_migration_central_log (
167 -- p_run_id => l_migration_id,
168 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
169 -- p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
170 -- p_table_name => 'IC_WHSE_MST',
171 -- p_context => 'WHSE_SYNC',
172 -- p_app_short_name => 'GMA');
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 x_failure_count := x_failure_count + 1;
177 GMA_COMMON_LOGGING.gma_migration_central_log (
178 p_run_id => l_migration_id,
179 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
180 p_message_token => 'GMA_MIGRATION_DB_ERROR',
181 p_table_name => 'IC_WHSE_MST',
182 p_context => 'WHSE_SYNC',
183 p_db_error => SQLERRM,
184 p_app_short_name => 'GMA');
185
186 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
187 -- logging API that are already handled by the calling layer.
188 -- GMA_COMMON_LOGGING.gma_migration_central_log (
189 -- p_run_id => l_migration_id,
190 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
191 -- p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
192 -- p_table_name => 'IC_WHSE_MST',
193 -- p_context => 'WHSE_SYNC',
194 -- p_app_short_name => 'GMA');
195
196 END sync_whse_subinventory;
197
198 /*====================================================================
199 -- PROCEDURE:
200 -- update_organization
201 --
202 -- DESCRIPTION:
203 -- This PL/SQL procedure is used to update the exisitng Organization
204 -- values .
205 --
206 --
207 -- PARAMETERS:
208 -- P_migration_run_id - id to use to right to migration log
209 -- x_failure_count - Number of failures occurred.
210 --
211 -- SYNOPSIS:
212 -- migrate_organization(p_migartion_id => l_migration_id,
213 -- p_commit => 'T',
214 -- x_failure_count => l_failure_count );
215 --
216 -- HISTORY
217 -- 05-APR-2007 ACATALDO Bug 5727749 - Initialized migrate counter to
218 -- avoid token showing in error log when a null
219 -- is passed.
220 -- 06-APR-2007 ACATALDO Bug 5955262 - Used correct token in exception
221 -- block for migration table failure.
222 --====================================================================*/
223
224 PROCEDURE update_organization (P_migration_run_id IN NUMBER,
225 P_commit IN VARCHAR2,
226 X_failure_count OUT NOCOPY NUMBER) IS
227 CURSOR Cur_get_orgn IS
228 SELECT organization_id, process_orgn_code
229 FROM mtl_parameters
230 WHERE process_enabled_flag = 'Y';
231
232 CURSOR Cur_get_oper_unit (V_orgn_code VARCHAR2)IS
233 SELECT a.co_code
234 FROM sy_orgn_mst a, gl_plcy_mst b
235 WHERE a.co_code = b.co_code
236 AND a.orgn_code = V_orgn_code
237 AND b.new_le_flag = 'Y';
238
239 CURSOR Cur_get_whse (V_organization_id NUMBER) IS
240 SELECT a.whse_code, a.whse_name
241 FROM ic_whse_mst a
242 WHERE mtl_organization_id = V_organization_id
243 AND NVL(subinventory_ind_flag, 'N') = 'N'
244 AND NVL(migrated_ind,0) = 0;
245
246 --Local Variables
247 l_migrate_count NUMBER;
248 l_failure_count NUMBER;
249 l_whse_code VARCHAR2(4);
250 l_whse_name VARCHAR2(240);
251 l_co_code VARCHAR2(4);
252 l_migration_id NUMBER;
253 l_legal_entity NUMBER;
254
255 SYNC_WHSE_ERROR EXCEPTION;
256 BEGIN
257 l_migration_id := P_migration_run_id;
258 X_failure_count := 0;
259 l_migrate_count := 0; /* Bug 5727749 */
260
261 GMA_COMMON_LOGGING.gma_migration_central_log (
262 p_run_id => l_migration_id,
263 p_log_level => FND_LOG.LEVEL_PROCEDURE,
264 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
265 p_table_name => 'MTL_PARAMETERS',
266 p_context => 'ORGANIZATION',
267 p_app_short_name => 'GMA');
268
269 /*Bug 5358112 - Setting the cost method to 1 and cost organization id to itself */
270 UPDATE mtl_parameters
271 SET lot_number_uniqueness = 2,
272 primary_cost_method = 1,
273 cost_organization_id = organization_id
274 WHERE process_enabled_flag = 'Y';
275
276 --Update some specific columns in the mtl_parameters table for existing organizations
277 FOR l_rec IN Cur_get_orgn LOOP
278 UPDATE mtl_parameters m
279 SET stock_locator_control_code = 4
280 WHERE organization_id = l_rec.organization_id
281 AND EXISTS (SELECT 1
282 FROM IC_WHSE_MST
283 WHERE loct_ctl > 0
284 AND mtl_organization_id = l_rec.organization_id);
285
286 OPEN Cur_get_oper_unit(l_rec.process_orgn_code);
287 FETCH Cur_get_oper_unit INTO l_co_code;
288 IF Cur_get_oper_unit%FOUND THEN
289 l_legal_entity := gmf_migration.get_legal_entity_id (p_co_code => l_co_code,
290 p_source_type => 'N');
291 /*Bug 5228725 - Added the org_information_context clause */
292 UPDATE hr_organization_information
293 SET org_information2 = l_legal_entity
294 WHERE organization_id = l_rec.organization_id
295 AND org_information_context = 'Accounting Information';
296 END IF;
297 CLOSE Cur_get_oper_unit;
298
299 /* Update the locator control for the existing subinventories to be determined at item level */
300 UPDATE mtl_secondary_inventories
301 SET locator_type = 5
302 WHERE organization_id = l_rec.organization_id
303 AND EXISTS (SELECT 1
304 FROM IC_WHSE_MST
305 WHERE loct_ctl > 0
306 AND mtl_organization_id = l_rec.organization_id);
307
308 /* Update secondary inventories table for any rows that had the default cost group id */
309 /* missing - due to an issue in the gmf_mtl_parameters_biur_tg trigger code - Bug 5553034*/
310 UPDATE mtl_secondary_inventories
311 SET default_cost_group_id = (SELECT default_cost_group_id
312 FROM mtl_parameters
313 WHERE organization_id = l_rec.organization_id)
314 WHERE default_cost_group_id IS NULL
315 AND organization_id = l_rec.organization_id
316 AND secondary_inventory_name <> 'AX_INTRANS';
317
318 OPEN Cur_get_whse (l_rec.organization_id);
319 FETCH Cur_get_whse INTO l_whse_code , l_whse_name;
320 IF Cur_get_whse%FOUND THEN
321 sync_whse_subinventory (P_migration_run_id => l_migration_id,
322 P_whse_code => l_whse_code,
323 P_whse_name => l_whse_name,
324 P_organization_id => l_rec.organization_id,
325 P_commit => FND_API.G_FALSE,
326 X_failure_count => l_failure_count);
327 IF l_failure_count > 0 THEN
328 CLOSE Cur_get_whse;
329 RAISE SYNC_WHSE_ERROR;
330 END IF;
331 UPDATE ic_whse_mst
332 SET migrated_ind = 1,
333 organization_id = mtl_organization_id
334 WHERE mtl_organization_id = l_rec.organization_id;
335 END IF;
336 CLOSE Cur_get_whse;
337 --Lets save the changes now based on the commit parameter
338 IF p_commit = FND_API.G_TRUE THEN
339 COMMIT;
340 END IF;
341 END LOOP;
342
343 GMA_COMMON_LOGGING.gma_migration_central_log (
344 p_run_id => l_migration_id,
345 p_log_level => FND_LOG.LEVEL_PROCEDURE,
346 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
347 p_table_name => 'MTL_PARAMETERS',
348 p_context => 'ORGANIZATION',
349 p_param1 => l_migrate_count,
350 p_param2 => X_failure_count,
351 p_app_short_name => 'GMA');
352
353 EXCEPTION
354 WHEN SYNC_WHSE_ERROR THEN
355 ROLLBACK;
356 x_failure_count := x_failure_count + 1;
357 WHEN OTHERS THEN
358 ROLLBACK;
359 x_failure_count := x_failure_count + 1;
360 GMA_COMMON_LOGGING.gma_migration_central_log (
361 p_run_id => l_migration_id,
362 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
363 p_message_token => 'GMA_MIGRATION_DB_ERROR',
364 p_table_name => 'MTL_PARAMETERS',
365 p_context => 'ORGANIZATION',
366 p_db_error => SQLERRM,
367 p_app_short_name => 'GMA');
368
369 GMA_COMMON_LOGGING.gma_migration_central_log (
370 p_run_id => l_migration_id,
371 p_log_level => FND_LOG.LEVEL_PROCEDURE,
372 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
373 p_table_name => 'MTL_PARAMETERS',
374 p_context => 'ORGANIZATION',
375 p_app_short_name => 'GMA');
376
377 END update_organization;
378
379 /*====================================================================
380 -- PROCEDURE:
381 -- create_location
382 --
383 -- DESCRIPTION:
384 -- This PL/SQL procedure is used to create the location in
385 -- Discrete tables .
386 --
387 --
388 -- PARAMETERS:
389 -- P_migration_run_id - id to use to right to migration log
390 -- p_organization_id --Organization id.
391 -- p_subinventory_code --Subinventory for location.
392 -- p_location --location name.
393 -- P_loct_desc --Location Name.
394 -- P_statrt_date_active - Start date
395 -- x_failure_count - Number of failures occurred.
396 -- SYNOPSIS:
397 -- create_location(P_migration_run_id => l_migration_id
398 -- p_organization_id => l_organization_id,
399 -- p_subinventory_code => l_subinventory_code,
400 -- p_location => l_location,
401 -- p_loct_desc => l_loct_desc,
402 -- p_start_date_active => l_start_date_active,
403 -- p_commit => 'Y',
404 -- x_failure_count => l_failure_count);
405 --
406 -- HISTORY
407 -- 06-APR-2007 ACATALDO Bug 5955262 - Used correct token in exception
408 -- block for migration table failure.
409 --====================================================================*/
410
411 PROCEDURE create_location (P_migration_run_id IN NUMBER,
412 P_organization_id IN NUMBER,
413 P_subinventory_code IN VARCHAR2,
414 P_location IN VARCHAR2,
415 P_loct_desc IN VARCHAR2,
416 P_start_date_active IN DATE,
417 P_commit IN VARCHAR2,
418 X_location_id OUT NOCOPY NUMBER,
419 X_failure_count OUT NOCOPY NUMBER,
420 P_disable_date IN DATE,
421 P_segment2 IN VARCHAR2,
422 P_segment3 IN VARCHAR2,
423 P_segment4 IN VARCHAR2,
424 P_segment5 IN VARCHAR2,
425 P_segment6 IN VARCHAR2,
426 P_segment7 IN VARCHAR2,
427 P_segment8 IN VARCHAR2,
428 P_segment9 IN VARCHAR2,
429 P_segment10 IN VARCHAR2,
430 P_segment11 IN VARCHAR2,
431 P_segment12 IN VARCHAR2,
432 P_segment13 IN VARCHAR2,
433 P_segment14 IN VARCHAR2,
434 P_segment15 IN VARCHAR2,
435 P_segment16 IN VARCHAR2,
436 P_segment17 IN VARCHAR2,
437 P_segment18 IN VARCHAR2,
438 P_segment19 IN VARCHAR2,
439 P_segment20 IN VARCHAR2) IS
440
441 --CURSORS
442
443 /* Bug 5529682 - Changed reference to mtl_item_locations_kfv to mtl_item_locations */
444 /* Bug 5607797 - Changed the following select to return the inventory location id instead of 1 */
445 CURSOR Cur_check_sub_location(V_location VARCHAR2) IS
446 SELECT inventory_location_id
447 FROM mtl_item_locations
448 WHERE segment1 = V_location
449 AND subinventory_code = P_subinventory_code
450 AND organization_id = p_organization_id;
451
452 /* Bug 5529682 - Changed reference to mtl_item_locations_kfv to mtl_item_locations */
453 CURSOR Cur_check_location(V_location VARCHAR2) IS
454 SELECT 1
455 FROM dual
456 WHERE EXISTS (SELECT 1
457 FROM mtl_item_locations
458 WHERE segment1 = V_location
459 AND organization_id = p_organization_id);
460
461 CURSOR Cur_get_id IS
462 SELECT mtl_item_locations_s.nextval
463 FROM dual;
464
465 --Local Variables
466 l_migration_id NUMBER;
467 l_organization_id NUMBER;
468 l_location_id NUMBER;
469 l_temp NUMBER;
470 l_loct_desc VARCHAR2(80);
471 l_start_date_active DATE;
472 l_migrate_count NUMBER(5) DEFAULT 0;
473 l_segment1 VARCHAR2(80);
474
475 --Exceptions
476 LOCATION_EXISTS EXCEPTION;
477 BEGIN
478 l_migration_id := P_migration_run_id;
479 X_failure_count := 0;
480
481 OPEN Cur_check_sub_location(P_location);
482 FETCH Cur_check_sub_location INTO X_location_id;
483 IF (Cur_check_sub_location%FOUND) THEN
484 CLOSE Cur_check_sub_location;
485 RAISE LOCATION_EXISTS;
486 ELSE
487 CLOSE Cur_check_sub_location;
488
489 /* Bug# 5529682 - If location not found with the exact name then try to see */
490 /* if there is a location defined with a concatenated subinventory */
491 OPEN Cur_check_sub_location(P_subinventory_code ||' '|| P_location);
492 FETCH Cur_check_sub_location INTO X_location_id;
493 IF (Cur_check_sub_location%FOUND) THEN
494 CLOSE Cur_check_sub_location;
495 RAISE LOCATION_EXISTS;
496 ELSE
497 CLOSE Cur_check_sub_location;
498 OPEN Cur_get_id;
499 FETCH Cur_get_id INTO l_location_id;
500 CLOSE Cur_get_id;
501 X_location_id := l_location_id;
502 END IF;
503 END IF;
504
505
506 OPEN Cur_check_location(P_location);
507 FETCH Cur_check_location INTO l_temp;
508 IF (Cur_check_location%FOUND) THEN
509 /* If the location exists under the org for a different subinventory */
510 /* then build a new one using the subinventory code */
511 l_segment1 := P_subinventory_code ||' '|| P_location;
512 ELSE
513 l_segment1 := p_location;
514 END IF;
515 CLOSE Cur_check_location;
516
517 IF (P_loct_desc IS NULL) THEN
518 l_loct_desc := P_location;
519 ELSE
520 l_loct_desc := P_loct_desc;
521 END IF;
522
523 IF (P_start_date_active IS NULL) THEN
524 l_start_date_active := SYSDATE;
525 ELSE
526 l_start_date_active := P_start_date_active;
527 END IF;
528
529 --Insert the location into mtl_item_locations table
530 INSERT INTO mtl_item_locations(
531 inventory_location_id,organization_id,description,descriptive_text,disable_date,picking_order,location_maximum_units,
532 subinventory_code,location_weight_uom_code,max_weight,volume_uom_code,max_cubic_area,segment1,segment2,segment3,segment4,
533 segment5,segment6,segment7,segment8,segment9,segment10,segment11,segment12,segment13,segment14,segment15,segment16,
534 segment17,segment18,segment19,segment20,summary_flag,enabled_flag,start_date_active,end_date_active,attribute_category,
535 attribute1,attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8,attribute9,attribute10,
536 attribute11,attribute12,attribute13,attribute14,attribute15,project_id,task_id,physical_location_id,pick_uom_code,
537 dimension_uom_code,length,width,height,locator_status,status_id,current_cubic_area,available_cubic_area,current_weight,
538 available_weight,location_current_units,location_available_units,suggested_cubic_area,empty_flag,mixed_items_flag,
539 dropping_order,location_suggested_units,availability_type,inventory_atp_code,reservable_type,inventory_item_id,
540 creation_date,created_by,last_update_date,last_updated_by)
541 VALUES (
542 l_location_id,p_organization_id,l_loct_desc,NULL,p_disable_date,NULL,NULL,p_subinventory_code,NULL,NULL,NULL,
543 NULL,l_segment1,p_segment2,p_segment3,p_segment4,p_segment5,p_segment6,p_segment7,p_segment8,p_segment9,p_segment10,
544 p_segment11,p_segment12,p_segment13,p_segment14,p_segment15,p_segment16,p_segment17,p_segment18,p_segment19,p_segment20,
545 'N','Y',l_start_date_active,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
546 NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,1,NULL,
547 SYSDATE,0,SYSDATE,0);
548
549 --Lets save the changes now based on the commit parameter
550 IF p_commit = FND_API.G_TRUE THEN
551 COMMIT;
552 END IF;
553
554 l_migrate_count := l_migrate_count + 1;
555
556
557 EXCEPTION
558 WHEN LOCATION_EXISTS THEN
559 NULL;
560
561 WHEN OTHERS THEN
562 x_failure_count := x_failure_count + 1;
563 GMA_COMMON_LOGGING.gma_migration_central_log (
564 p_run_id => l_migration_id,
565 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
566 p_message_token => 'GMA_MIGRATION_DB_ERROR',
567 p_table_name => 'IC_LOCT_MST',
568 p_context => 'LOCATION',
569 p_db_error => SQLERRM,
570 p_app_short_name => 'GMA');
571
572 GMA_COMMON_LOGGING.gma_migration_central_log (
573 p_run_id => l_migration_id,
574 p_log_level => FND_LOG.LEVEL_PROCEDURE,
575 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
576 p_table_name => 'IC_LOCT_MST',
577 p_context => 'LOCATION',
578 p_app_short_name => 'GMA');
579
580 END create_location;
581
582 /*====================================================================
583 -- PROCEDURE:
584 -- migrate_location
585 --
586 -- DESCRIPTION:
587 -- This PL/SQL procedure is used to migrate the locations to
588 -- Discrete tables .
589 --
590 --
591 -- PARAMETERS:
592 -- P_migration_run_id - id to use to right to migration log
593 -- p_organization_id --Organization id.
594 -- p_subinventory_code --Subinventory for location.
595 -- x_failure_count - Number of failures occurred.
596 --
597 -- SYNOPSIS:
598 -- migrate_location(P_migration_run_id => l_migration_id
599 -- p_organization_id => l_organization_id,
600 -- p_subinventory_code => l_subinventory_code,
601 -- p_commit => 'N',
602 -- x_failure_count => l_failure_count);
603 --
604 -- HISTORY
605 -- 05-APR-2007 ACATALDO Bug 5727749 Remove calls to the central
606 -- logging API that are already handled
607 -- by the calling layer.
608 -- 06-APR-2007 ACATALDO Bug 5955262 - Used correct token in exception
609 -- block for migration table failure.
610 --====================================================================*/
611
612 PROCEDURE migrate_location (P_migration_run_id IN NUMBER,
613 P_organization_id IN NUMBER,
614 P_subinventory_code IN VARCHAR2,
615 P_commit IN VARCHAR2,
616 X_location_id OUT NOCOPY NUMBER,
617 X_failure_count OUT NOCOPY NUMBER) IS
618 --CURSORS
619 -- Removed select of segment1 etc from kfv views. Thomas Daniel. B4712289
620 /* Bug 5529682 - Removed reference to mtl_item_locations_kfv */
621 /* duplicate checking is now being done using segment1 */
622 CURSOR Cur_get_location (V_location VARCHAR2) IS
623 SELECT il.*, l.delete_mark
624 FROM mtl_item_locations il, ic_loct_mst l
625 WHERE l.location <> V_location
626 AND l.whse_code = P_subinventory_code
627 AND l.inventory_location_id = il.inventory_location_id;
628
629 --Local Variables
630 l_migration_id NUMBER;
631 l_organization_id NUMBER;
632 l_location VARCHAR2(240);
633 l_loc VARCHAR2(240);
634 l_temp NUMBER;
635 l_location_id NUMBER;
636 l_failure_count NUMBER;
637 l_disable_date DATE;
638
639 --Exceptions
640 LOCATION_FAILED EXCEPTION;
641 BEGIN
642 l_migration_id := P_migration_run_id;
643 X_failure_count := 0;
644
645 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
646 -- logging API that are already handled by the calling layer.
647 -- GMA_COMMON_LOGGING.gma_migration_central_log (
648 -- p_run_id => l_migration_id,
649 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
650 -- p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
651 -- p_table_name => 'IC_LOCT_MST',
652 -- p_context => 'LOCATION',
653 -- p_app_short_name => 'GMA');
654
655 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
656 FOR l_rec IN Cur_get_location(l_location) LOOP
657
658 /* Bug 5529682 - Removed the concatenation of subinventory and location */
659 /* this is being done in the create_location procedure */
660
661 IF l_rec.delete_mark = 0 THEN
662 l_disable_date := NULL; --SYSDATE; Bug# 5451429 delete mark = 0 is not disabled.
663 ELSE
664 l_disable_date := SYSDATE; --NULL; Bug# 5451429 delete mark <> 0 is disabled.
665 END IF;
666
667 create_location (P_migration_run_id => l_migration_id,
668 P_organization_id => P_organization_id,
669 P_subinventory_code => P_subinventory_code,
670 P_location => l_rec.segment1,
671 P_disable_date => l_disable_date,
672 P_segment2 => l_rec.segment2,
673 P_segment3 => l_rec.segment3,
674 P_segment4 => l_rec.segment4,
675 P_segment5 => l_rec.segment5,
676 P_segment6 => l_rec.segment6,
677 P_segment7 => l_rec.segment7,
678 P_segment8 => l_rec.segment8,
679 P_segment9 => l_rec.segment9,
680 P_segment10 => l_rec.segment10,
681 P_segment11 => l_rec.segment11,
682 P_segment12 => l_rec.segment12,
683 P_segment13 => l_rec.segment13,
684 P_segment14 => l_rec.segment14,
685 P_segment15 => l_rec.segment15,
686 P_segment16 => l_rec.segment16,
687 P_segment17 => l_rec.segment17,
688 P_segment18 => l_rec.segment18,
689 P_segment19 => l_rec.segment19,
690 P_segment20 => l_rec.segment20,
691 P_loct_desc => l_rec.description,
692 P_start_date_active => l_rec.creation_date,
693 P_commit => FND_API.G_FALSE,
694 X_location_id => l_location_id,
695 X_failure_count => l_failure_count);
696 IF (l_failure_count > 0) THEN
697 RAISE LOCATION_FAILED;
698 ELSE
699 UPDATE ic_loct_mst
700 SET locator_id = l_location_id
701 WHERE location = l_rec.segment1
702 AND whse_code = p_subinventory_code;
703 END IF;
704 END LOOP;
705
706 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
707 -- logging API that are already handled by the calling layer.
708 -- GMA_COMMON_LOGGING.gma_migration_central_log (
709 -- p_run_id => l_migration_id,
710 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
711 -- p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
712 -- p_table_name => 'IC_LOCT_MST',
713 -- p_context => 'LOCATION',
714 -- p_app_short_name => 'GMA');
715
716 EXCEPTION
717 WHEN LOCATION_FAILED THEN
718 x_failure_count := l_failure_count;
719
720 WHEN OTHERS THEN
721 x_failure_count := x_failure_count + l_failure_count;
722 GMA_COMMON_LOGGING.gma_migration_central_log (
723 p_run_id => l_migration_id,
724 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
725 p_message_token => 'GMA_MIGRATION_DB_ERROR',
726 p_table_name => 'IC_LOCT_MST',
727 p_context => 'LOCATION',
728 p_db_error => SQLERRM,
729 p_app_short_name => 'GMA');
730
731 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
732 -- logging API that are already handled by the calling layer.
733 -- GMA_COMMON_LOGGING.gma_migration_central_log (
734 -- p_run_id => l_migration_id,
735 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
736 -- p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
737 -- p_table_name => 'IC_LOCT_MST',
738 -- p_context => 'LOCATION',
739 -- p_app_short_name => 'GMA');
740
741 END migrate_location;
742
743 /*====================================================================
744 -- PROCEDURE:
745 -- migrate_subinventory
746 --
747 -- DESCRIPTION:
748 -- This PL/SQL procedure is used to migrate the warehouses to
749 -- Discrete tables .
750 --
751 --
752 -- PARAMETERS:
753 -- P_migration_run_id - id to use to right to migration log
754 -- x_failure_count - Number of failures occurred.
755 --
756 -- SYNOPSIS:
757 -- migrate_subinventory(p_migartion_id => l_migration_id,
758 -- p_orgn_code => l_rec.orgn_code,
759 -- p_commit => 'T',
760 -- x_failure_count => l_failure_count );
761 --
762 -- HISTORY
763 -- 05-APR-2007 ACATALDO Bug 5727749 Remove calls to the central
764 -- logging API that are already handled
765 -- by the calling layer.
766 -- 06-APR-2007 ACATALDO Bug 5955262 - Used correct token in exception
767 -- block for migration table failure.
768 --====================================================================*/
769
770 PROCEDURE migrate_subinventory (P_migration_run_id IN NUMBER,
771 P_orgn_code IN VARCHAR2,
772 P_commit IN VARCHAR2,
773 X_failure_count OUT NOCOPY NUMBER) IS
774
775 --CURSORS
776 CURSOR Cur_get_warehouse IS
777 SELECT *
778 FROM ic_whse_mst_vw
779 WHERE subinventory_ind_flag = 'Y'
780 AND orgn_code = P_orgn_code
781 AND NVL(migrated_ind,0) = 0;
782
783 CURSOR Cur_get_orgn(V_orgn_code VARCHAR2) IS
784 SELECT organization_id
785 FROM sy_orgn_mst
786 WHERE orgn_code = V_orgn_code;
787
788 CURSOR Cur_check_subinventory(V_whse_code VARCHAR2, V_organization_id NUMBER) IS
789 SELECT 1
790 FROM dual
791 WHERE EXISTS (SELECT 1
792 FROM mtl_secondary_inventories
793 WHERE secondary_inventory_name = V_whse_code
794 AND organization_id = V_organization_id);
795
796 CURSOR Cur_get_details(V_orgn_id NUMBER) IS
797 SELECT *
798 FROM mtl_parameters
799 WHERE organization_id = V_orgn_id;
800
801 --Local Variables
802 l_migration_id NUMBER;
803 l_organization_id NUMBER;
804 l_cost_orgn_id NUMBER;
805 l_location_id NUMBER;
806 l_mtl_orgn_id NUMBER;
807 l_subinv_ind VARCHAR2(1);
808 l_temp NUMBER;
809 l_locator_type NUMBER;
810 l_rowid VARCHAR2(80);
811 l_failure_count NUMBER;
812
813 --Row type declarations
814 l_details Cur_get_details%ROWTYPE;
815
816 --Exceptions
817 ORGN_NOT_MIGRATED EXCEPTION;
818 MIG_LOCATION_ERROR EXCEPTION;
819 BEGIN
820 X_failure_count := 0;
821 l_migration_id := P_migration_run_id;
822
823 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
824 -- logging API that are already handled by the calling layer.
825 -- GMA_COMMON_LOGGING.gma_migration_central_log (
826 -- p_run_id => l_migration_id,
827 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
828 -- p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
829 -- p_table_name => 'IC_WHSE_MST',
830 -- p_context => 'WAREHOUSE',
831 -- p_app_short_name => 'GMA');
832
833 FOR l_rec IN Cur_get_warehouse LOOP
834 BEGIN
835 IF (l_rec.subinventory_ind_flag = 'Y') THEN
836 IF (l_rec.organization_id IS NULL) THEN
837 OPEN Cur_get_orgn(l_rec.orgn_code);
838 FETCH Cur_get_orgn INTO l_organization_id;
839 CLOSE Cur_get_orgn;
840 IF (l_organization_id IS NULL) THEN
841 RAISE ORGN_NOT_MIGRATED;
842 END IF;
843 ELSE
844 l_organization_id := l_rec.organization_id;
845 END IF;
846
847 --Fecth some details from mtl_parameters table to pass the parameter values.
848 OPEN Cur_get_details(l_organization_id);
849 FETCH Cur_get_details INTO l_details;
850 CLOSE Cur_get_details;
851
852 -- Check if the warehouse is location controlled
853 IF l_rec.loct_ctl > 0THEN
854 IF (l_details.stock_locator_control_code = 4) THEN
855 l_locator_type := 5;
856 ELSE
857 l_locator_type := 1;
858 END IF;
859 ELSE
860 l_locator_type := 1;
861 END IF;
862
863 OPEN Cur_check_subinventory(l_rec.whse_code, l_organization_id);
864 FETCH Cur_check_subinventory INTO l_temp;
865 IF (Cur_check_subinventory%FOUND) THEN
866 UPDATE mtl_secondary_inventories
867 SET locator_type = l_locator_type,
868 default_loc_status_id = 1
869 WHERE organization_id = l_organization_id
870 AND secondary_inventory_name = l_rec.whse_code;
871
872 GMA_COMMON_LOGGING.gma_migration_central_log (
873 p_run_id => l_migration_id,
874 p_log_level => FND_LOG.LEVEL_PROCEDURE,
875 p_message_token => 'GMA_WHSE_EXISTS',
876 p_table_name => 'IC_WHSE_MST',
877 p_context => 'WAREHOUSE',
878 p_token1 => 'WAREHOUSE',
879 p_param1 => l_rec.whse_code,
880 p_app_short_name => 'GMA');
881
882 UPDATE ic_loct_mst
883 SET locator_id = inventory_location_id
884 WHERE whse_code = l_rec.whse_code;
885 ELSE
886 --Now insert the warehouse into mtl_secondary_inventories table
887 mtl_secondary_inventories_pkg.insert_row (
888 x_rowid => l_rowid
889 , x_secondary_inventory_name => l_rec.whse_code
890 , x_organization_id => l_organization_id
891 , x_last_update_date => SYSDATE
892 , x_last_updated_by => 0
893 , x_creation_date => SYSDATE
894 , x_created_by => 0
895 , x_last_update_login => 0
896 , x_description => l_rec.whse_name
897 , x_disable_date => NULL
898 , x_inventory_atp_code => 1
899 , x_availability_type => 1
900 , x_reservable_type => 1
901 , x_locator_type => l_locator_type
902 , x_picking_order => NULL
903 , x_dropping_order => NULL
904 , x_material_account => l_details.material_account
905 , x_material_overhead_account => l_details.material_overhead_account
906 , x_resource_account => l_details.resource_account
907 , x_overhead_account => l_details.overhead_account
908 , x_outside_processing_account => l_details.outside_processing_account
909 , x_quantity_tracked => 1
910 , x_asset_inventory => 1
911 , x_source_type => NULL
912 , x_source_subinventory => NULL
913 , x_source_organization_id => NULL
914 , x_requisition_approval_type => NULL
915 , x_expense_account => l_details.expense_account
916 , x_encumbrance_account => l_details.encumbrance_account
917 , x_attribute_category => NULL
918 , x_attribute1 => NULL
919 , x_attribute2 => NULL
920 , x_attribute3 => NULL
921 , x_attribute4 => NULL
922 , x_attribute5 => NULL
923 , x_attribute6 => NULL
924 , x_attribute7 => NULL
925 , x_attribute8 => NULL
926 , x_attribute9 => NULL
927 , x_attribute10 => NULL
928 , x_attribute11 => NULL
929 , x_attribute12 => NULL
930 , x_attribute13 => NULL
931 , x_attribute14 => NULL
932 , x_attribute15 => NULL
933 , x_preprocessing_lead_time => NULL
934 , x_processing_lead_time => NULL
935 , x_postprocessing_lead_time => NULL
936 , x_demand_class => NULL
937 , x_project_id => NULL
938 , x_task_id => NULL
939 , x_subinventory_usage => NULL
940 , x_notify_list_id => NULL
941 , x_depreciable_flag => 2
942 , x_location_id => NULL
943 , x_status_id => 1
944 , x_default_loc_status_id => 1
945 , x_lpn_controlled_flag => 0
946 , x_default_cost_group_id => l_details.default_cost_group_id
947 , x_pick_uom_code => NULL
948 , x_cartonization_flag => 0
949 , x_planning_level => 2
950 , x_default_count_type_code => 2
951 , x_subinventory_type => 1
952 , x_enable_bulk_pick => 'N');
953
954 migrate_location(P_migration_run_id => l_migration_id,
955 P_organization_id => l_organization_id,
956 P_subinventory_code => l_rec.whse_code,
957 P_commit => FND_API.G_FALSE,
958 X_location_id => l_location_id,
959 X_failure_count => l_failure_count);
960 IF l_failure_count > 0 THEN
961 CLOSE Cur_check_subinventory;
962 RAISE MIG_LOCATION_ERROR;
963 END IF;
964 END IF;
965 CLOSE Cur_check_subinventory;
966
967 --Validate the existing inventory org for the warehouse
968 IF (l_rec.disable_warehouse_ind ='Y') THEN
969 UPDATE hr_organization_units
970 SET date_to = SYSDATE
971 WHERE organization_id = l_rec.mtl_organization_id;
972 END IF;
973
974 --Update the migrated ind for the warehouse.
975 UPDATE ic_whse_mst
976 SET organization_id = l_organization_id,
977 migrated_ind = 1
978 WHERE whse_code = l_rec.whse_code;
979
980 END IF; /* IF (l_rec.subinventory_ind_flag = 'Y') */
981 EXCEPTION
982 WHEN MIG_LOCATION_ERROR THEN
983 X_failure_count := X_failure_count + l_failure_count;
984 WHEN ORGN_NOT_MIGRATED THEN
985 x_failure_count := x_failure_count + 1;
986 GMA_COMMON_LOGGING.gma_migration_central_log (
987 p_run_id => l_migration_id,
988 p_log_level => FND_LOG.LEVEL_ERROR,
989 p_message_token => 'GMD_ORG_NOT_MIGRATED',
990 p_table_name => 'IC_WHSE_MST',
991 p_context => 'WAREHOUSE',
992 p_token1 => 'ORGANIZATION',
993 p_param1 => l_rec.orgn_code,
994 p_app_short_name => 'GMA');
995
996 WHEN OTHERS THEN
997 x_failure_count := x_failure_count + 1;
998
999 GMA_COMMON_LOGGING.gma_migration_central_log (
1000 p_run_id => l_migration_id,
1001 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1002 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1003 p_table_name => 'IC_WHSE_MST',
1004 p_context => 'WAREHOUSE',
1005 p_db_error => SQLERRM,
1006 p_app_short_name => 'GMA');
1007 END;
1008 END LOOP;
1009
1010 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
1011 -- logging API that are already handled by the calling layer.
1012 -- GMA_COMMON_LOGGING.gma_migration_central_log (
1013 -- p_run_id => l_migration_id,
1014 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
1015 -- p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1016 -- p_table_name => 'IC_WHSE_MST',
1017 -- p_context => 'WAREHOUSE',
1018 -- p_app_short_name => 'GMA');
1019
1020
1021 EXCEPTION
1022 WHEN OTHERS THEN
1023 x_failure_count := x_failure_count + 1;
1024
1025 GMA_COMMON_LOGGING.gma_migration_central_log (
1026 p_run_id => l_migration_id,
1027 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1028 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1029 p_table_name => 'IC_WHSE_MST',
1030 p_context => 'WAREHOUSE',
1031 p_db_error => SQLERRM,
1032 p_app_short_name => 'GMA');
1033
1034 -- Bug 5727749 T.Cataldo 5-April-2007 Remove calls to the central
1035 -- logging API that are already handled by the calling layer.
1036 -- GMA_COMMON_LOGGING.gma_migration_central_log (
1037 -- p_run_id => l_migration_id,
1038 -- p_log_level => FND_LOG.LEVEL_PROCEDURE,
1039 -- p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
1040 -- p_table_name => 'IC_WHSE_MST',
1041 -- p_context => 'WAREHOUSE',
1042 -- p_app_short_name => 'GMA');
1043
1044 END migrate_subinventory;
1045
1046 /*====================================================================
1047 -- PROCEDURE:
1048 -- create_organization
1049 --
1050 -- DESCRIPTION:
1051 -- This PL/SQL procedure is used to create organization at the
1052 -- discrete end.
1053 --
1054 -- PARAMETERS:
1055 -- P_migration_run_id - id to use to right to migration log
1056 -- x_failure_count - Number of failures occurred.
1057 --
1058 -- SYNOPSIS:
1059 --
1060 --
1061 -- HISTORY
1062 -- TDaniel Bug#5108912 - Removed the condition (AND p_plant_ind <> 0)
1063 -- for creating the inventory org.
1064 -- RLNAGARA Bug6607319 - Added Default Status for Material Status Migration ME
1065 --====================================================================*/
1066
1067 PROCEDURE create_organization (P_template_organization_id IN NUMBER,
1068 P_orgn_code IN VARCHAR2,
1069 P_orgn_name IN VARCHAR2,
1070 P_organization_code IN VARCHAR2,
1071 P_organization_name IN VARCHAR2,
1072 P_addr_id IN NUMBER,
1073 P_creation_date IN DATE,
1074 P_inventory_org_ind IN VARCHAR2,
1075 P_default_status_id IN VARCHAR2, --RLNAGARA Material Status Migration ME
1076 P_plant_ind IN NUMBER,
1077 P_migrate_as_ind IN NUMBER,
1078 P_process_enabled_ind IN VARCHAR2,
1079 p_delete_mark IN NUMBER,
1080 P_migration_run_id IN NUMBER,
1081 X_failure_count OUT NOCOPY NUMBER,
1082 X_organization_id OUT NOCOPY NUMBER) IS
1083 --CURSORS
1084 CURSOR Cur_get_organization_id (V_orgn_code VARCHAR2)IS
1085 SELECT m.organization_id
1086 FROM mtl_parameters m, ic_whse_mst w
1087 WHERE w.orgn_code = V_orgn_code
1088 AND w.mtl_organization_id = m.organization_id
1089 ORDER BY whse_code;
1090
1091 CURSOR Cur_get_address (V_addr_id NUMBER)IS
1092 SELECT *
1093 FROM sy_addr_mst
1094 WHERE addr_id = V_addr_id
1095 AND delete_mark = 0;
1096
1097 /* Bug 5358112 - Changed the cursor to refer to the base tables instead of */
1098 /* org organization definitions table */
1099 CURSOR Cur_get_orgn_def (V_orgn_id NUMBER)IS
1100 SELECT HOU.BUSINESS_GROUP_ID,
1101 DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION1), TO_NUMBER(NULL)) SET_OF_BOOKS_ID,
1102 DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)) OPERATING_UNIT,
1103 DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null) LEGAL_ENTITY
1104 FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2
1105 WHERE HOU.organization_id = V_orgn_id
1106 AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
1107 AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information';
1108
1109 CURSOR Cur_get_unit IS
1110 SELECT hr_organization_units_s.nextval
1111 FROM dual;
1112
1113 CURSOR Cur_org_code (v_org_code VARCHAR2, v_start_ch NUMBER) IS
1114 (SELECT substrb(v_org_code,1,v_start_ch)|| substrb(ltrim(to_char(to_number(rownum)-1,'099')), v_start_ch+1)
1115 FROM gl_sevt_ttl t1, gl_sevt_ttl t2
1116 WHERE rownum <= decode(v_start_ch, 0, 1000, 1, 100, 10)
1117 minus
1118 SELECT organization_code
1119 FROM mtl_parameters);
1120
1121 CURSOR Cur_get_orgn_values(V_orgn_id NUMBER) IS
1122 SELECT *
1123 FROM mtl_parameters
1124 WHERE organization_id = V_orgn_id;
1125
1126
1127 --LOCAL VARIABLES
1128 l_rowid VARCHAR2(80);
1129 l_orgn_code VARCHAR2(3);
1130 l_organization_name VARCHAR2(240);
1131 l_org_Id NUMBER;
1132 l_organization_Id NUMBER;
1133 l_master_organization_Id NUMBER;
1134 l_process_enabled_ind VARCHAR2(1);
1135 l_location_id NUMBER;
1136 l_dummy NUMBER;
1137 l_date_to DATE;
1138 l_return_status VARCHAR2(1);
1139 l_msg_count NUMBER(5);
1140 l_msg_data VARCHAR2(2000);
1141 l_cost_group_id NUMBER(15);
1142
1143 --Row type declarations
1144 l_addr Cur_get_address%ROWTYPE;
1145 l_orgn_def Cur_get_orgn_def%ROWTYPE;
1146 l_parameter Cur_get_orgn_values%ROWTYPE;
1147
1148 --Exceptions
1149 ORGN_MISSING EXCEPTION;
1150 COST_GROUP_SETUP_ERR EXCEPTION;
1151 l_def_business_group NUMBER(15) := FND_PROFILE.VALUE('ORG_ID');
1152 BEGIN
1153 X_failure_count := 0;
1154 IF (P_template_organization_id IS NOT NULL) THEN
1155 l_org_id := p_template_organization_id;
1156 ELSE
1157 OPEN Cur_get_organization_id(p_orgn_code);
1158 FETCH Cur_get_organization_id INTO l_org_id;
1159 CLOSE Cur_get_organization_id;
1160 END IF;
1161
1162 IF (NVL(p_inventory_org_ind, 'Y') = 'Y') THEN
1163 IF (l_org_id IS NULL) THEN
1164 RAISE ORGN_MISSING;
1165 END IF;
1166 END IF;
1167 -- Default the organization name
1168 IF p_organization_name IS NOT NULL THEN
1169 l_organization_name := p_organization_name;
1170 ELSE
1171 l_organization_name := p_orgn_code||':'||p_orgn_name;
1172 END IF;
1173
1174 --Fetch the organization definition.
1175 OPEN Cur_get_orgn_def(l_org_id);
1176 FETCH Cur_get_orgn_def INTO l_orgn_def;
1177 IF Cur_get_orgn_def%NOTFOUND THEN
1178 CLOSE Cur_get_orgn_def;
1179 RAISE ORGN_MISSING;
1180 END IF;
1181 CLOSE Cur_get_orgn_def;
1182
1183 IF (P_addr_id IS NOT NULL) THEN
1184 --Fetch the address for each organization and create a location for the same by calling the API.
1185 OPEN Cur_get_address(P_addr_id);
1186 FETCH Cur_get_address INTO l_addr;
1187 CLOSE Cur_get_address;
1188
1189 hr_location_api.create_location(p_effective_date => p_creation_date,
1190 p_location_code => p_orgn_code,
1191 p_description => p_orgn_name,
1192 p_address_line_1 => l_addr.addr1,
1193 p_address_line_2 => l_addr.addr2,
1194 p_address_line_3 => l_addr.addr3,
1195 p_town_or_city => l_addr.addr4,
1196 p_region_3 => l_addr.state_code,
1197 p_postal_code => l_addr.postal_code,
1198 p_country => l_addr.country_code,
1199 p_loc_information13 => l_addr.ora_addr4,
1200 p_region_1 => l_addr.province,
1201 p_region_2 => l_addr.county,
1202 p_style => 'OPM',
1203 p_business_group_id => NVL(l_orgn_def.business_group_id, l_def_business_group),
1204 p_location_id => l_location_id,
1205 p_object_version_number => l_dummy);
1206 END IF;
1207 --Create a row for the organization in hr_organization_units table.
1208 OPEN Cur_get_unit;
1209 FETCH Cur_get_unit INTO X_organization_id;
1210 CLOSE Cur_get_unit;
1211
1212 IF (p_migrate_as_ind = 3 OR (p_migrate_as_ind IS NULL AND p_delete_mark = 1)) THEN
1213 l_date_to := SYSDATE;
1214 END IF;
1215 hr_organization_units_pkg.insert_row(
1216 X_rowid =>l_rowid,
1217 X_organization_id =>X_organization_id,
1218 X_business_group_id =>NVL(l_orgn_def.business_group_id, l_def_business_group),
1219 X_cost_allocation_keyflex_id =>NULL,
1220 X_location_id =>l_location_id,
1221 X_soft_coding_keyflex_id =>NULL,
1222 X_date_from =>p_creation_date,
1223 X_name =>l_organization_name,
1224 X_comments =>NULL,
1225 X_date_to =>l_date_to,
1226 X_internal_external_flag =>'INT',
1227 X_internal_address_line =>NULL,
1228 X_type =>NULL,
1229 X_security_profile_id =>NULL,
1230 X_view_all_orgs =>NULL,
1231 X_attribute_category =>NULL,
1232 X_attribute1 =>NULL,
1233 X_attribute2 =>NULL,
1234 X_attribute3 =>NULL,
1235 X_attribute4 =>NULL,
1236 X_attribute5 =>NULL,
1237 X_attribute6 =>NULL,
1238 X_attribute7 =>NULL,
1239 X_attribute8 =>NULL,
1240 X_attribute9 =>NULL,
1241 X_attribute10 =>NULL,
1242 X_attribute11 =>NULL,
1243 X_attribute12 =>NULL,
1244 X_attribute13 =>NULL,
1245 X_attribute14 =>NULL,
1246 X_attribute15 =>NULL,
1247 X_attribute16 =>NULL,
1248 X_attribute17 =>NULL,
1249 X_attribute18 =>NULL,
1250 X_attribute19 =>NULL,
1251 X_attribute20 =>NULL);
1252 --Classify this organization as an inventory organization.
1253 IF (NVL(p_inventory_org_ind, 'Y') = 'Y') THEN
1254 INSERT INTO hr_organization_information(
1255 ORG_INFORMATION_ID,
1256 ORG_INFORMATION_CONTEXT,
1257 ORGANIZATION_ID,
1258 ORG_INFORMATION1,
1259 ORG_INFORMATION2,
1260 ORG_INFORMATION3,
1261 LAST_UPDATE_DATE,
1262 LAST_UPDATED_BY,
1263 CREATED_BY,
1264 CREATION_DATE)
1265 VALUES (
1266 hr_organization_information_s.nextval,
1267 'CLASS',
1268 X_organization_id,
1269 'INV',
1270 'Y',
1271 NULL,
1272 sysdate,
1273 0,
1274 0,
1275 sysdate);
1276 --Define the accounting information from the fiscal policy
1277 INSERT INTO hr_organization_information(
1278 ORG_INFORMATION_ID,
1279 ORG_INFORMATION_CONTEXT,
1280 ORGANIZATION_ID,
1281 ORG_INFORMATION1,
1282 ORG_INFORMATION2,
1283 ORG_INFORMATION3,
1284 LAST_UPDATE_DATE,
1285 LAST_UPDATED_BY,
1286 CREATED_BY,
1287 CREATION_DATE)
1288 VALUES (
1289 hr_organization_information_s.nextval,
1290 'Accounting Information',
1291 X_organization_id,
1292 l_orgn_def.set_of_books_id,
1293 l_orgn_def.legal_entity,
1294 l_orgn_def.operating_unit,
1295 sysdate,
1296 0,
1297 0,
1298 sysdate);
1299
1300 --Fetch the organization info from mtl_parameters and insert into the table.
1301 OPEN Cur_get_orgn_values(l_org_id);
1302 FETCH Cur_get_orgn_values INTO l_parameter;
1303 CLOSE Cur_get_orgn_values;
1304
1305 IF (p_migrate_as_ind IS NOT NULL) THEN
1306 l_orgn_code := p_organization_code;
1307 l_master_organization_id := l_parameter.master_organization_id;
1308 -- Bug 5352477 - Default the process enabled indicator if it is NULL
1309 l_process_enabled_ind := NVL(p_process_enabled_ind, 'Y');
1310 ELSE
1311 l_process_enabled_ind := 'Y';
1312 l_master_organization_id := l_parameter.master_organization_id;
1313 OPEN Cur_org_code (p_orgn_code, 2);
1314 FETCH Cur_org_code INTO l_orgn_code;
1315 IF Cur_org_code%NOTFOUND THEN
1316 CLOSE Cur_org_code;
1317 OPEN Cur_org_code (p_orgn_code, 1);
1318 FETCH Cur_org_code INTO l_orgn_code;
1319 IF Cur_org_code%NOTFOUND THEN
1320 CLOSE Cur_org_code;
1321 OPEN Cur_org_code (p_orgn_code, 0);
1322 FETCH Cur_org_code INTO l_orgn_code;
1323 CLOSE Cur_org_code;
1324 ELSE
1325 CLOSE Cur_org_code;
1326 END IF;
1327 ELSE
1328 CLOSE Cur_org_code;
1329 END IF;
1330 END IF;
1331
1332 /* Fetch the default cost group for the organization */
1333 INV_COST_GROUP_PVT.get_default_cost_group
1334 (x_return_status => l_return_status,
1335 x_msg_count => l_msg_count,
1336 x_msg_data => l_msg_data,
1337 x_cost_group_id => l_cost_group_id,
1338 p_material_account => l_parameter.Material_Account,
1339 p_material_overhead_account => l_parameter.Material_Overhead_Account,
1340 p_resource_account => l_parameter.Resource_Account,
1341 p_overhead_account => l_parameter.Overhead_Account,
1342 p_outside_processing_account => l_parameter.Outside_Processing_Account,
1343 p_expense_account => l_parameter.Expense_Account,
1344 p_encumbrance_account => l_parameter.Encumbrance_Account,
1345 p_average_cost_var_account => l_parameter.Average_Cost_Var_Account,
1346 p_organization_id => x_organization_id,
1347 p_cost_group => NULL
1348 );
1349
1350 IF (l_return_status <> 'S') then
1351 RAISE COST_GROUP_SETUP_ERR;
1352 END IF;
1353
1354 /* Bug 5358112 changed the primary cost method to 1 */
1355 INSERT INTO mtl_parameters(
1356 organization_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login,
1357 organization_code, master_organization_id, primary_cost_method, cost_organization_id,
1358 default_material_cost_id, calendar_exception_set_id, calendar_code, general_ledger_update_code,
1359 default_atp_rule_id, default_picking_rule_id, default_locator_order_value, default_subinv_order_value,
1360 negative_inv_receipt_code, stock_locator_control_code, material_account, material_overhead_account,
1361 matl_ovhd_absorption_acct, resource_account, purchase_price_var_account, ap_accrual_account,
1362 overhead_account, outside_processing_account, intransit_inv_account, interorg_receivables_account,
1363 interorg_price_var_account, interorg_payables_account, cost_of_sales_account, encumbrance_account,
1364 interorg_transfer_cr_account, matl_interorg_transfer_code, interorg_trnsfr_charge_percent,
1365 source_organization_id, source_subinventory, source_type, serial_number_type,
1366 auto_serial_alpha_prefix, start_auto_serial_number, auto_lot_alpha_prefix, lot_number_uniqueness,
1367 lot_number_generation, lot_number_zero_padding, lot_number_length, starting_revision,
1368 default_demand_class, encumbrance_reversal_flag, maintain_fifo_qty_stack_type,
1369 invoice_price_var_account, average_cost_var_account, sales_account, expense_account,
1370 serial_number_generation, mat_ovhd_cost_type_id, project_reference_enabled,
1371 pm_cost_collection_enabled, project_control_level, avg_rates_cost_type_id, txn_approval_timeout_period,
1372 borrpay_matl_var_account, borrpay_moh_var_account, borrpay_res_var_account, borrpay_osp_var_account,
1373 borrpay_ovh_var_account, org_max_weight, org_max_volume, org_max_weight_uom_code, org_max_volume_uom_code,
1374 mo_source_required, mo_pick_confirm_required, mo_approval_timeout_action, project_cost_account,
1375 process_enabled_flag, process_orgn_code, wsm_enabled_flag, default_cost_group_id, wms_enabled_flag, qa_skipping_insp_flag,default_status_id)
1376 VALUES (
1377 X_organization_id, l_parameter.last_update_date, l_parameter.last_updated_by, l_parameter.creation_date,
1378 l_parameter.created_by, l_parameter.last_update_login,l_orgn_code, l_master_organization_id, 1,
1379 X_organization_id, l_parameter.default_material_cost_id, l_parameter.calendar_exception_set_id,
1380 l_parameter.calendar_code, l_parameter.general_ledger_update_code, l_parameter.default_atp_rule_id,
1381 l_parameter.default_picking_rule_id, l_parameter.default_locator_order_value, l_parameter.default_subinv_order_value,
1382 l_parameter.negative_inv_receipt_code, 4, l_parameter.material_account,
1383 l_parameter.material_overhead_account, l_parameter.matl_ovhd_absorption_acct, l_parameter.resource_account,
1384 l_parameter.purchase_price_var_account, l_parameter.ap_accrual_account, l_parameter.overhead_account,
1385 l_parameter.outside_processing_account, l_parameter.intransit_inv_account, l_parameter.interorg_receivables_account,
1386 l_parameter.interorg_price_var_account, l_parameter.interorg_payables_account, l_parameter.cost_of_sales_account,
1387 l_parameter.encumbrance_account, l_parameter.interorg_transfer_cr_account, l_parameter.matl_interorg_transfer_code,
1388 l_parameter.interorg_trnsfr_charge_percent, l_parameter.source_organization_id, l_parameter.source_subinventory,
1389 l_parameter.source_type, l_parameter.serial_number_type, l_parameter.auto_serial_alpha_prefix, l_parameter.start_auto_serial_number,
1390 l_parameter.auto_lot_alpha_prefix, 2, l_parameter.lot_number_generation, l_parameter.lot_number_zero_padding,
1391 l_parameter.lot_number_length, l_parameter.starting_revision, l_parameter.default_demand_class, l_parameter.encumbrance_reversal_flag,
1392 l_parameter.maintain_fifo_qty_stack_type, l_parameter.invoice_price_var_account, l_parameter.average_cost_var_account, l_parameter.sales_account,
1393 l_parameter.expense_account, l_parameter.serial_number_generation, l_parameter.mat_ovhd_cost_type_id, l_parameter.project_reference_enabled,
1394 l_parameter.pm_cost_collection_enabled, l_parameter.project_control_level, l_parameter.avg_rates_cost_type_id, l_parameter.txn_approval_timeout_period,
1395 l_parameter.borrpay_matl_var_account, l_parameter.borrpay_moh_var_account, l_parameter.borrpay_res_var_account, l_parameter.borrpay_osp_var_account,
1396 l_parameter.borrpay_ovh_var_account, l_parameter.org_max_weight, l_parameter.org_max_volume, l_parameter.org_max_weight_uom_code,
1397 l_parameter.org_max_volume_uom_code, l_parameter.mo_source_required, l_parameter.mo_pick_confirm_required, l_parameter.mo_approval_timeout_action,
1398 l_parameter.project_cost_account,l_process_enabled_ind, l_parameter.organization_code, l_parameter.wsm_enabled_flag,
1399 l_cost_group_id, l_parameter.wms_enabled_flag, l_parameter.qa_skipping_insp_flag,p_default_status_id);
1400
1401
1402 /* Bug 5620938 - A default subinventory must be created for the organization */
1403 mtl_secondary_inventories_pkg.insert_row (
1404 x_rowid => l_rowid
1405 , x_secondary_inventory_name => l_orgn_code
1406 , x_organization_id => X_organization_id
1407 , x_last_update_date => SYSDATE
1408 , x_last_updated_by => 0
1409 , x_creation_date => SYSDATE
1410 , x_created_by => 0
1411 , x_last_update_login => 0
1412 , x_description => l_organization_name
1413 , x_disable_date => NULL
1414 , x_inventory_atp_code => 1
1415 , x_availability_type => 1
1416 , x_reservable_type => 1
1417 , x_locator_type => 5
1418 , x_picking_order => NULL
1419 , x_dropping_order => NULL
1420 , x_material_account => l_parameter.material_account
1421 , x_material_overhead_account => l_parameter.material_overhead_account
1422 , x_resource_account => l_parameter.resource_account
1423 , x_overhead_account => l_parameter.overhead_account
1424 , x_outside_processing_account => l_parameter.outside_processing_account
1425 , x_quantity_tracked => 1
1426 , x_asset_inventory => 1
1427 , x_source_type => NULL
1428 , x_source_subinventory => NULL
1429 , x_source_organization_id => NULL
1430 , x_requisition_approval_type => NULL
1431 , x_expense_account => l_parameter.expense_account
1432 , x_encumbrance_account => l_parameter.encumbrance_account
1433 , x_attribute_category => NULL
1434 , x_attribute1 => NULL
1435 , x_attribute2 => NULL
1436 , x_attribute3 => NULL
1437 , x_attribute4 => NULL
1438 , x_attribute5 => NULL
1439 , x_attribute6 => NULL
1440 , x_attribute7 => NULL
1441 , x_attribute8 => NULL
1442 , x_attribute9 => NULL
1443 , x_attribute10 => NULL
1444 , x_attribute11 => NULL
1445 , x_attribute12 => NULL
1446 , x_attribute13 => NULL
1447 , x_attribute14 => NULL
1448 , x_attribute15 => NULL
1449 , x_preprocessing_lead_time => NULL
1450 , x_processing_lead_time => NULL
1451 , x_postprocessing_lead_time => NULL
1452 , x_demand_class => NULL
1453 , x_project_id => NULL
1454 , x_task_id => NULL
1455 , x_subinventory_usage => NULL
1456 , x_notify_list_id => NULL
1457 , x_depreciable_flag => 2
1458 , x_location_id => NULL
1459 , x_status_id => 1
1460 , x_default_loc_status_id => 1
1461 , x_lpn_controlled_flag => 0
1462 , x_default_cost_group_id => l_cost_group_id
1463 , x_pick_uom_code => NULL
1464 , x_cartonization_flag => 0
1465 , x_planning_level => 2
1466 , x_default_count_type_code => 2
1467 , x_subinventory_type => 1
1468 , x_enable_bulk_pick => 'N');
1469
1470 /* Create back the link to hr locations to point to the created organization */
1471 IF p_addr_id IS NOT NULL THEN
1472 UPDATE hr_locations_all
1473 SET inventory_organization_id = X_organization_id
1474 WHERE location_code = p_orgn_code;
1475 END IF;
1476 END IF; /* IF (p_inventory_org_ind = 'Y' OR (p_inventory_org_ind IS NULL AND p_plant_ind <> 0)) */
1477
1478 EXCEPTION
1479 WHEN ORGN_MISSING THEN
1480 x_failure_count := x_failure_count + 1;
1481 GMA_COMMON_LOGGING.gma_migration_central_log (
1482 p_run_id => P_migration_run_id,
1483 p_log_level => FND_LOG.LEVEL_ERROR,
1484 p_message_token => 'GMA_TEMP_ORGN_MISSING_ERROR',
1485 p_table_name => 'SY_ORGN_MST',
1486 p_context => 'ORGANIZATION',
1487 p_token1 => 'ORGANIZATION',
1488 p_param1 => p_orgn_code,
1489 p_app_short_name => 'GMA');
1490
1491 WHEN COST_GROUP_SETUP_ERR THEN
1492 x_failure_count := x_failure_count + 1;
1493 GMA_COMMON_LOGGING.gma_migration_central_log (
1494 p_run_id => P_migration_run_id,
1495 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1496 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1497 p_table_name => 'SY_ORGN_MST',
1498 p_context => 'ORGANIZATION',
1499 p_db_error => p_orgn_code||'-'||l_msg_data,
1500 p_app_short_name => 'GMA');
1501
1502 WHEN OTHERS THEN
1503 x_failure_count := x_failure_count + 1;
1504
1505 GMA_COMMON_LOGGING.gma_migration_central_log (
1506 p_run_id => P_migration_run_id,
1507 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1508 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1509 p_table_name => 'SY_ORGN_MST',
1510 p_context => 'ORGANIZATION',
1511 p_db_error => SQLERRM,
1512 p_app_short_name => 'GMA');
1513 END create_organization;
1514
1515
1516 /*====================================================================
1517 -- PROCEDURE:
1518 -- migrate_organization
1519 --
1520 -- DESCRIPTION:
1521 -- This PL/SQL procedure is used to migrate the Organizations to
1522 -- Discrete tables .
1523 --
1524 --
1525 -- PARAMETERS:
1526 -- P_migration_run_id - id to use to right to migration log
1527 -- x_failure_count - Number of failures occurred.
1528 --
1529 -- SYNOPSIS:
1530 -- migrate_organization(p_migartion_id => l_migration_id,
1531 -- p_commit => 'T',
1532 -- x_failure_count => l_failure_count );
1533 --
1534 -- HISTORY
1535 -- 05-APR-2007 ACATALDO Bug 5727749 - Initialized migrate counter to
1536 -- avoid token showing in error log when a null
1537 -- is passed.
1538 -- 06-APR-2007 ACATALDO Bug 5955262 - Used correct token in exception
1539 -- block for migration table failure and
1540 -- warehouse insert error.
1541 -- 17-Dec-2007 RLNAGARA Bug 6607319 - Modified for Material Status ME
1542 --====================================================================*/
1543
1544 PROCEDURE migrate_organization (P_migration_run_id IN NUMBER,
1545 P_commit IN VARCHAR2,
1546 X_failure_count OUT NOCOPY NUMBER) IS
1547
1548 --CURSORS
1549 CURSOR Cur_get_organization IS
1550 SELECT *
1551 FROM sy_orgn_mst
1552 WHERE (migrate_as_ind <> 0 OR migrate_as_ind IS NULL)
1553 AND (orgn_code <> co_code or plant_ind > 0)
1554 AND NVL(migrated_ind,0) = 0;
1555
1556 --RLNAGARA Material Status Migration ME - Added the below cursor
1557 CURSOR Cur_get_status_id(v_status VARCHAR2) IS
1558 SELECT status_id
1559 FROM mtl_material_statuses
1560 WHERE status_code = v_status;
1561
1562 --LOCAL VARIABLES
1563 l_organization_id NUMBER;
1564 l_migration_id NUMBER;
1565 l_failure_count NUMBER;
1566 l_migrate_count NUMBER;
1567 l_default_status_id NUMBER; --RLNAGARA Material Status Migration ME
1568
1569 --Exceptions
1570 CREATE_ORGN_ERROR EXCEPTION;
1571 WHSE_CODE_ERROR EXCEPTION;
1572 --RLNAGARA Material Status Migration ME
1573 DEFAULT_STATUS_MISSING EXCEPTION;
1574 STATUS_ID_MISSING EXCEPTION;
1575 BEGIN
1576 l_migration_id := P_migration_run_id;
1577 X_failure_count := 0;
1578 l_migrate_count := 0; /* Bug 5727749 */
1579
1580
1581 GMA_COMMON_LOGGING.gma_migration_central_log (
1582 p_run_id => l_migration_id,
1583 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1584 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1585 p_table_name => 'SY_ORGN_MST',
1586 p_context => 'ORGANIZATION',
1587 p_app_short_name => 'GMA');
1588
1589 FOR l_rec IN Cur_get_organization LOOP
1590 BEGIN
1591 SAVEPOINT Organization_Setup;
1592
1593 --RLNAGARA Material Status Migration ME
1594 IF (l_rec.default_status IS NULL) THEN
1595 RAISE DEFAULT_STATUS_MISSING;
1596 ELSE
1597 OPEN Cur_get_status_id(l_rec.default_status);
1598 FETCH Cur_get_status_id INTO l_default_status_id;
1599 IF Cur_get_status_id%NOTFOUND THEN
1600 CLOSE Cur_get_status_id;
1601 RAISE STATUS_ID_MISSING;
1602 END IF;
1603 CLOSE Cur_get_status_id;
1604 END IF;
1605
1606 IF NVL(l_rec.migrate_as_ind, 1) IN (1,3) THEN
1607 create_organization (P_template_organization_id => l_rec.template_organization_id
1608 ,P_orgn_code => l_rec.orgn_code
1609 ,P_orgn_name => l_rec.orgn_name
1610 ,P_organization_code => l_rec.organization_code
1611 ,P_organization_name => l_rec.organization_name
1612 ,P_addr_id => l_rec.addr_id
1613 ,P_creation_date => l_rec.creation_date
1614 ,P_inventory_org_ind => l_rec.inventory_org_ind
1615 ,P_default_status_id => l_default_status_id --RLNAGARA Material Status Migration ME
1616 ,P_plant_ind => l_rec.plant_ind
1617 ,P_migrate_as_ind => l_rec.migrate_as_ind
1618 ,P_process_enabled_ind => l_rec.process_enabled_ind
1619 ,P_delete_mark => l_rec.delete_mark
1620 ,P_migration_run_id => p_migration_run_id
1621 ,X_organization_id => l_organization_id
1622 ,X_failure_count => l_failure_count);
1623 IF l_failure_count > 0 THEN
1624 RAISE CREATE_ORGN_ERROR;
1625 END IF;
1626 ELSE
1627 l_organization_id := l_rec.organization_id;
1628 --RLNAGARA Material Status Migration ME - Updating for already existing organizations
1629 UPDATE mtl_parameters
1630 SET default_status_id = l_default_status_id
1631 WHERE organization_id = l_rec.organization_id;
1632 END IF; /* IF NVL(l_rec.migrate_as_ind, 1) IN (1,3) */
1633
1634 UPDATE sy_orgn_mst_b
1635 SET organization_id = l_organization_id,
1636 migrated_ind = 1
1637 WHERE orgn_code = l_rec.orgn_code;
1638
1639 migrate_subinventory(P_migration_run_id => l_migration_id,
1640 P_orgn_code => l_rec.orgn_code,
1641 P_commit => FND_API.G_FALSE,
1642 X_failure_count => l_failure_count);
1643 IF (l_failure_count > 0) THEN
1644 RAISE WHSE_CODE_ERROR;
1645 END IF;
1646
1647 --Bases on the p_commit flag commit the transaction.
1648 IF p_commit = FND_API.G_TRUE THEN
1649 COMMIT;
1650 END IF;
1651 l_migrate_count := l_migrate_count + 1;
1652
1653 EXCEPTION
1654 WHEN CREATE_ORGN_ERROR THEN
1655 x_failure_count := x_failure_count + 1;
1656 ROLLBACK TO Organization_Setup;
1657 GMA_COMMON_LOGGING.gma_migration_central_log (
1658 p_run_id => l_migration_id,
1659 p_log_level => FND_LOG.LEVEL_ERROR,
1660 p_message_token => 'GMA_ORGN_MISSING_ERROR',
1661 p_table_name => 'SY_ORGN_MST',
1662 p_context => 'ORGANIZATION',
1663 p_token1 => 'ORGANIZATION',
1664 p_param1 => l_rec.orgn_code,
1665 p_app_short_name => 'GMA');
1666
1667 WHEN WHSE_CODE_ERROR THEN
1668 GMA_COMMON_LOGGING.gma_migration_central_log (
1669 p_run_id => l_migration_id,
1670 p_log_level => FND_LOG.LEVEL_ERROR,
1671 p_message_token => 'GMA_MIGRATION_FAIL',
1672 p_token1 => 'PARAM1',
1673 p_param1 => 'due to an error in migrating the W/H as a subinventory',
1674 p_context => 'ORGANIZATION',
1675 p_app_short_name => 'GMA');
1676
1677 --RLNAGARA Material Status Migration ME
1678 WHEN DEFAULT_STATUS_MISSING THEN
1679 x_failure_count := x_failure_count + 1;
1680 GMA_COMMON_LOGGING.gma_migration_central_log(
1681 p_run_id => P_migration_run_id,
1682 p_log_level => FND_LOG.LEVEL_ERROR,
1683 p_message_token => 'GMA_DEFAULT_STATUS_MISSING_ERROR',
1684 p_table_name => 'SY_ORGN_MST',
1685 p_context => 'ORGANIZATION',
1686 p_token1 => 'ORGANIZATION',
1687 p_param1 => l_rec.organization_code,
1688 p_app_short_name => 'GMA');
1689
1690 --RLNAGARA Material Status Migration ME
1691 WHEN STATUS_ID_MISSING THEN
1692 x_failure_count := x_failure_count + 1;
1693 GMA_COMMON_LOGGING.gma_migration_central_log(
1694 p_run_id => P_migration_run_id,
1695 p_log_level => FND_LOG.LEVEL_ERROR,
1696 p_message_token => 'GMD_MIG_STATUS_ID',
1697 p_table_name => 'MTL_MATERIAL_STATUSES',
1698 p_context => 'STAT',
1699 p_token1 => 'STAT',
1700 p_param1 => l_rec.default_status,
1701 p_app_short_name => 'GMA');
1702
1703 WHEN OTHERS THEN
1704 x_failure_count := x_failure_count + 1;
1705 ROLLBACK TO Organization_Setup;
1706 GMA_COMMON_LOGGING.gma_migration_central_log (
1707 p_run_id => l_migration_id,
1708 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1709 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1710 p_table_name => 'SY_ORGN_MST',
1711 p_context => 'ORGANIZATION',
1712 p_db_error => SQLERRM,
1713 p_app_short_name => 'GMA');
1714 END;
1715 END LOOP;
1716
1717 GMA_COMMON_LOGGING.gma_migration_central_log (
1718 p_run_id => l_migration_id,
1719 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1720 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1721 p_table_name => 'SY_ORGN_MST',
1722 p_context => 'ORGANIZATION',
1723 p_param1 => l_migrate_count,
1724 p_param2 => X_failure_count,
1725 p_app_short_name => 'GMA');
1726
1727
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 x_failure_count := X_failure_count + 1;
1731
1732 GMA_COMMON_LOGGING.gma_migration_central_log (
1733 p_run_id => l_migration_id,
1734 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1735 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1736 p_table_name => 'SY_ORGN_MST',
1737 p_context => 'ORGANIZATION',
1738 p_db_error => SQLERRM,
1739 p_app_short_name => 'GMA');
1740
1741 GMA_COMMON_LOGGING.gma_migration_central_log (
1742 p_run_id => l_migration_id,
1743 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1744 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
1745 p_table_name => 'SY_ORGN_MST',
1746 p_context => 'ORGANIZATION',
1747 p_app_short_name => 'GMA');
1748
1749 END migrate_organization;
1750
1751 END INV_MIGRATE_PROCESS_ORG;
1752