[Home] [Help]
PACKAGE BODY: APPS.INV_MIGRATE_PROCESS_ORG
Source
1 PACKAGE BODY INV_MIGRATE_PROCESS_ORG AS
2 /* $Header: INVPOMGB.pls 120.22.12020000.2 2012/07/09 08:12:59 asugandh 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 -- Shaliu Chen Bug 13981860 -Add Unique Validation for organization code
1066
1067 --====================================================================*/
1068
1069 PROCEDURE create_organization (P_template_organization_id IN NUMBER,
1070 P_orgn_code IN VARCHAR2,
1071 P_orgn_name IN VARCHAR2,
1072 P_organization_code IN VARCHAR2,
1073 P_organization_name IN VARCHAR2,
1074 P_addr_id IN NUMBER,
1075 P_creation_date IN DATE,
1076 P_inventory_org_ind IN VARCHAR2,
1077 P_default_status_id IN VARCHAR2, --RLNAGARA Material Status Migration ME
1078 P_plant_ind IN NUMBER,
1079 P_migrate_as_ind IN NUMBER,
1080 P_process_enabled_ind IN VARCHAR2,
1081 p_delete_mark IN NUMBER,
1082 P_migration_run_id IN NUMBER,
1083 X_failure_count OUT NOCOPY NUMBER,
1084 X_organization_id OUT NOCOPY NUMBER) IS
1085 --CURSORS
1086 CURSOR Cur_get_organization_id (V_orgn_code VARCHAR2)IS
1087 SELECT m.organization_id
1088 FROM mtl_parameters m, ic_whse_mst w
1089 WHERE w.orgn_code = V_orgn_code
1090 AND w.mtl_organization_id = m.organization_id
1091 ORDER BY whse_code;
1092
1093 CURSOR Cur_get_address (V_addr_id NUMBER)IS
1094 SELECT *
1095 FROM sy_addr_mst
1096 WHERE addr_id = V_addr_id
1097 AND delete_mark = 0;
1098
1099 /* Bug 5358112 - Changed the cursor to refer to the base tables instead of */
1100 /* org organization definitions table */
1101 CURSOR Cur_get_orgn_def (V_orgn_id NUMBER)IS
1102 SELECT HOU.BUSINESS_GROUP_ID,
1103 DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION1), TO_NUMBER(NULL)) SET_OF_BOOKS_ID,
1104 DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)) OPERATING_UNIT,
1105 DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null) LEGAL_ENTITY
1106 FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2
1107 WHERE HOU.organization_id = V_orgn_id
1108 AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
1109 AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information';
1110
1111 CURSOR Cur_get_unit IS
1112 SELECT hr_organization_units_s.nextval
1113 FROM dual;
1114
1115 CURSOR Cur_org_code (v_org_code VARCHAR2, v_start_ch NUMBER) IS
1116 (SELECT substrb(v_org_code,1,v_start_ch)|| substrb(ltrim(to_char(to_number(rownum)-1,'099')), v_start_ch+1)
1117 FROM gl_sevt_ttl t1, gl_sevt_ttl t2
1118 WHERE rownum <= decode(v_start_ch, 0, 1000, 1, 100, 10)
1119 minus
1120 SELECT organization_code
1121 FROM mtl_parameters);
1122
1123 CURSOR Cur_get_orgn_values(V_orgn_id NUMBER) IS
1124 SELECT *
1125 FROM mtl_parameters
1126 WHERE organization_id = V_orgn_id;
1127 --Bug #13981860
1128 --Check if V_oranization_code is already exist in the mtl_parameters table
1129 CURSOR Cur_unique_validation(V_oranization_code VARCHAR2) IS
1130 SELECT 'code_exists'
1131 FROM MTL_PARAMETERS
1132 WHERE organization_code = V_oranization_code;
1133
1134
1135 --LOCAL VARIABLES
1136 l_rowid VARCHAR2(80);
1137 l_orgn_code VARCHAR2(3);
1138 l_organization_name VARCHAR2(240);
1139 l_org_Id NUMBER;
1140 l_organization_Id NUMBER;
1141 l_master_organization_Id NUMBER;
1142 l_process_enabled_ind VARCHAR2(1);
1143 l_location_id NUMBER;
1144 l_dummy NUMBER;
1145 l_date_to DATE;
1146 l_return_status VARCHAR2(1);
1147 l_msg_count NUMBER(5);
1148 l_msg_data VARCHAR2(2000);
1149 l_cost_group_id NUMBER(15);
1150 l_existing VARCHAR2(255);
1151
1152 --Row type declarations
1153 l_addr Cur_get_address%ROWTYPE;
1154 l_orgn_def Cur_get_orgn_def%ROWTYPE;
1155 l_parameter Cur_get_orgn_values%ROWTYPE;
1156
1157 --Exceptions
1158 ORGN_MISSING EXCEPTION;
1159 COST_GROUP_SETUP_ERR EXCEPTION;
1160 ORGN_CODE_EXISTING EXCEPTION;
1161 l_def_business_group NUMBER(15) := FND_PROFILE.VALUE('ORG_ID');
1162 BEGIN
1163 X_failure_count := 0;
1164 IF (P_template_organization_id IS NOT NULL) THEN
1165 l_org_id := p_template_organization_id;
1166 ELSE
1167 OPEN Cur_get_organization_id(p_orgn_code);
1168 FETCH Cur_get_organization_id INTO l_org_id;
1169 CLOSE Cur_get_organization_id;
1170 END IF;
1171
1172 IF (NVL(p_inventory_org_ind, 'Y') = 'Y') THEN
1173 IF (l_org_id IS NULL) THEN
1174 RAISE ORGN_MISSING;
1175 END IF;
1176 END IF;
1177 --BEGIN Bug #13981860
1178 --Add Unique Validation for organization code
1179 OPEN Cur_unique_validation(P_organization_code);
1180 FETCH Cur_unique_validation INTO l_existing;
1181 IF Cur_unique_validation%FOUND THEN
1182 CLOSE Cur_unique_validation;
1183 RAISE ORGN_CODE_EXISTING;
1184 ELSE
1185 CLOSE Cur_unique_validation;
1186 END IF;
1187 --END Bug #13981860
1188
1189 -- Default the organization name
1190 IF p_organization_name IS NOT NULL THEN
1191 l_organization_name := p_organization_name;
1192 ELSE
1193 l_organization_name := p_orgn_code||':'||p_orgn_name;
1194 END IF;
1195
1196 --Fetch the organization definition.
1197 OPEN Cur_get_orgn_def(l_org_id);
1198 FETCH Cur_get_orgn_def INTO l_orgn_def;
1199 IF Cur_get_orgn_def%NOTFOUND THEN
1200 CLOSE Cur_get_orgn_def;
1201 RAISE ORGN_MISSING;
1202 END IF;
1203 CLOSE Cur_get_orgn_def;
1204
1205 IF (P_addr_id IS NOT NULL) THEN
1206 --Fetch the address for each organization and create a location for the same by calling the API.
1207 OPEN Cur_get_address(P_addr_id);
1208 FETCH Cur_get_address INTO l_addr;
1209 CLOSE Cur_get_address;
1210
1211 hr_location_api.create_location(p_effective_date => p_creation_date,
1212 p_location_code => p_orgn_code,
1213 p_description => p_orgn_name,
1214 p_address_line_1 => l_addr.addr1,
1215 p_address_line_2 => l_addr.addr2,
1216 p_address_line_3 => l_addr.addr3,
1217 p_town_or_city => l_addr.addr4,
1218 p_region_3 => l_addr.state_code,
1219 p_postal_code => l_addr.postal_code,
1220 p_country => l_addr.country_code,
1221 p_loc_information13 => l_addr.ora_addr4,
1222 p_region_1 => l_addr.province,
1223 p_region_2 => l_addr.county,
1224 p_style => 'OPM',
1225 p_business_group_id => NVL(l_orgn_def.business_group_id, l_def_business_group),
1226 p_location_id => l_location_id,
1227 p_object_version_number => l_dummy);
1228 END IF;
1229 --Create a row for the organization in hr_organization_units table.
1230 OPEN Cur_get_unit;
1231 FETCH Cur_get_unit INTO X_organization_id;
1232 CLOSE Cur_get_unit;
1233
1234 IF (p_migrate_as_ind = 3 OR (p_migrate_as_ind IS NULL AND p_delete_mark = 1)) THEN
1235 l_date_to := SYSDATE;
1236 END IF;
1237 hr_organization_units_pkg.insert_row(
1238 X_rowid =>l_rowid,
1239 X_organization_id =>X_organization_id,
1240 X_business_group_id =>NVL(l_orgn_def.business_group_id, l_def_business_group),
1241 X_cost_allocation_keyflex_id =>NULL,
1242 X_location_id =>l_location_id,
1243 X_soft_coding_keyflex_id =>NULL,
1244 X_date_from =>p_creation_date,
1245 X_name =>l_organization_name,
1246 X_comments =>NULL,
1247 X_date_to =>l_date_to,
1248 X_internal_external_flag =>'INT',
1249 X_internal_address_line =>NULL,
1250 X_type =>NULL,
1251 X_security_profile_id =>NULL,
1252 X_view_all_orgs =>NULL,
1253 X_attribute_category =>NULL,
1254 X_attribute1 =>NULL,
1255 X_attribute2 =>NULL,
1256 X_attribute3 =>NULL,
1257 X_attribute4 =>NULL,
1258 X_attribute5 =>NULL,
1259 X_attribute6 =>NULL,
1260 X_attribute7 =>NULL,
1261 X_attribute8 =>NULL,
1262 X_attribute9 =>NULL,
1263 X_attribute10 =>NULL,
1264 X_attribute11 =>NULL,
1265 X_attribute12 =>NULL,
1266 X_attribute13 =>NULL,
1267 X_attribute14 =>NULL,
1268 X_attribute15 =>NULL,
1269 X_attribute16 =>NULL,
1270 X_attribute17 =>NULL,
1271 X_attribute18 =>NULL,
1272 X_attribute19 =>NULL,
1273 X_attribute20 =>NULL);
1274 --Classify this organization as an inventory organization.
1275 IF (NVL(p_inventory_org_ind, 'Y') = 'Y') THEN
1276 INSERT INTO hr_organization_information(
1277 ORG_INFORMATION_ID,
1278 ORG_INFORMATION_CONTEXT,
1279 ORGANIZATION_ID,
1280 ORG_INFORMATION1,
1281 ORG_INFORMATION2,
1282 ORG_INFORMATION3,
1283 LAST_UPDATE_DATE,
1284 LAST_UPDATED_BY,
1285 CREATED_BY,
1286 CREATION_DATE)
1287 VALUES (
1288 hr_organization_information_s.nextval,
1289 'CLASS',
1290 X_organization_id,
1291 'INV',
1292 'Y',
1293 NULL,
1294 sysdate,
1295 0,
1296 0,
1297 sysdate);
1298 --Define the accounting information from the fiscal policy
1299 INSERT INTO hr_organization_information(
1300 ORG_INFORMATION_ID,
1301 ORG_INFORMATION_CONTEXT,
1302 ORGANIZATION_ID,
1303 ORG_INFORMATION1,
1304 ORG_INFORMATION2,
1305 ORG_INFORMATION3,
1306 LAST_UPDATE_DATE,
1307 LAST_UPDATED_BY,
1308 CREATED_BY,
1309 CREATION_DATE)
1310 VALUES (
1311 hr_organization_information_s.nextval,
1312 'Accounting Information',
1313 X_organization_id,
1314 l_orgn_def.set_of_books_id,
1315 l_orgn_def.legal_entity,
1316 l_orgn_def.operating_unit,
1317 sysdate,
1318 0,
1319 0,
1320 sysdate);
1321
1322 --Fetch the organization info from mtl_parameters and insert into the table.
1323 OPEN Cur_get_orgn_values(l_org_id);
1324 FETCH Cur_get_orgn_values INTO l_parameter;
1325 CLOSE Cur_get_orgn_values;
1326
1327 IF (p_migrate_as_ind IS NOT NULL) THEN
1328 l_orgn_code := p_organization_code;
1329 l_master_organization_id := l_parameter.master_organization_id;
1330 -- Bug 5352477 - Default the process enabled indicator if it is NULL
1331 l_process_enabled_ind := NVL(p_process_enabled_ind, 'Y');
1332 ELSE
1333 l_process_enabled_ind := 'Y';
1334 l_master_organization_id := l_parameter.master_organization_id;
1335 OPEN Cur_org_code (p_orgn_code, 2);
1336 FETCH Cur_org_code INTO l_orgn_code;
1337 IF Cur_org_code%NOTFOUND THEN
1338 CLOSE Cur_org_code;
1339 OPEN Cur_org_code (p_orgn_code, 1);
1340 FETCH Cur_org_code INTO l_orgn_code;
1341 IF Cur_org_code%NOTFOUND THEN
1342 CLOSE Cur_org_code;
1343 OPEN Cur_org_code (p_orgn_code, 0);
1344 FETCH Cur_org_code INTO l_orgn_code;
1345 CLOSE Cur_org_code;
1346 ELSE
1347 CLOSE Cur_org_code;
1348 END IF;
1349 ELSE
1350 CLOSE Cur_org_code;
1351 END IF;
1352 END IF;
1353
1354 /* Fetch the default cost group for the organization */
1355 INV_COST_GROUP_PVT.get_default_cost_group
1356 (x_return_status => l_return_status,
1357 x_msg_count => l_msg_count,
1358 x_msg_data => l_msg_data,
1359 x_cost_group_id => l_cost_group_id,
1360 p_material_account => l_parameter.Material_Account,
1361 p_material_overhead_account => l_parameter.Material_Overhead_Account,
1362 p_resource_account => l_parameter.Resource_Account,
1363 p_overhead_account => l_parameter.Overhead_Account,
1364 p_outside_processing_account => l_parameter.Outside_Processing_Account,
1365 p_expense_account => l_parameter.Expense_Account,
1366 p_encumbrance_account => l_parameter.Encumbrance_Account,
1367 p_average_cost_var_account => l_parameter.Average_Cost_Var_Account,
1368 p_organization_id => x_organization_id,
1369 p_cost_group => NULL
1370 );
1371
1372 IF (l_return_status <> 'S') then
1373 RAISE COST_GROUP_SETUP_ERR;
1374 END IF;
1375
1376 /* Bug 5358112 changed the primary cost method to 1 */
1377 INSERT INTO mtl_parameters(
1378 organization_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login,
1379 organization_code, master_organization_id, primary_cost_method, cost_organization_id,
1380 default_material_cost_id, calendar_exception_set_id, calendar_code, general_ledger_update_code,
1381 default_atp_rule_id, default_picking_rule_id, default_locator_order_value, default_subinv_order_value,
1382 negative_inv_receipt_code, stock_locator_control_code, material_account, material_overhead_account,
1383 matl_ovhd_absorption_acct, resource_account, purchase_price_var_account, ap_accrual_account,
1384 overhead_account, outside_processing_account, intransit_inv_account, interorg_receivables_account,
1385 interorg_price_var_account, interorg_payables_account, cost_of_sales_account, encumbrance_account,
1386 interorg_transfer_cr_account, matl_interorg_transfer_code, interorg_trnsfr_charge_percent,
1387 source_organization_id, source_subinventory, source_type, serial_number_type,
1388 auto_serial_alpha_prefix, start_auto_serial_number, auto_lot_alpha_prefix, lot_number_uniqueness,
1389 lot_number_generation, lot_number_zero_padding, lot_number_length, starting_revision,
1390 default_demand_class, encumbrance_reversal_flag, maintain_fifo_qty_stack_type,
1391 invoice_price_var_account, average_cost_var_account, sales_account, expense_account,
1392 serial_number_generation, mat_ovhd_cost_type_id, project_reference_enabled,
1393 pm_cost_collection_enabled, project_control_level, avg_rates_cost_type_id, txn_approval_timeout_period,
1394 borrpay_matl_var_account, borrpay_moh_var_account, borrpay_res_var_account, borrpay_osp_var_account,
1395 borrpay_ovh_var_account, org_max_weight, org_max_volume, org_max_weight_uom_code, org_max_volume_uom_code,
1396 mo_source_required, mo_pick_confirm_required, mo_approval_timeout_action, project_cost_account,
1397 process_enabled_flag, process_orgn_code, wsm_enabled_flag, default_cost_group_id, wms_enabled_flag, qa_skipping_insp_flag,default_status_id)
1398 VALUES (
1399 X_organization_id, l_parameter.last_update_date, l_parameter.last_updated_by, l_parameter.creation_date,
1400 l_parameter.created_by, l_parameter.last_update_login,l_orgn_code, l_master_organization_id, 1,
1401 X_organization_id, l_parameter.default_material_cost_id, l_parameter.calendar_exception_set_id,
1402 l_parameter.calendar_code, l_parameter.general_ledger_update_code, l_parameter.default_atp_rule_id,
1403 l_parameter.default_picking_rule_id, l_parameter.default_locator_order_value, l_parameter.default_subinv_order_value,
1404 l_parameter.negative_inv_receipt_code, 4, l_parameter.material_account,
1405 l_parameter.material_overhead_account, l_parameter.matl_ovhd_absorption_acct, l_parameter.resource_account,
1406 l_parameter.purchase_price_var_account, l_parameter.ap_accrual_account, l_parameter.overhead_account,
1407 l_parameter.outside_processing_account, l_parameter.intransit_inv_account, l_parameter.interorg_receivables_account,
1408 l_parameter.interorg_price_var_account, l_parameter.interorg_payables_account, l_parameter.cost_of_sales_account,
1409 l_parameter.encumbrance_account, l_parameter.interorg_transfer_cr_account, l_parameter.matl_interorg_transfer_code,
1410 l_parameter.interorg_trnsfr_charge_percent, l_parameter.source_organization_id, l_parameter.source_subinventory,
1411 l_parameter.source_type, l_parameter.serial_number_type, l_parameter.auto_serial_alpha_prefix, l_parameter.start_auto_serial_number,
1412 l_parameter.auto_lot_alpha_prefix, 2, l_parameter.lot_number_generation, l_parameter.lot_number_zero_padding,
1413 l_parameter.lot_number_length, l_parameter.starting_revision, l_parameter.default_demand_class, l_parameter.encumbrance_reversal_flag,
1414 l_parameter.maintain_fifo_qty_stack_type, l_parameter.invoice_price_var_account, l_parameter.average_cost_var_account, l_parameter.sales_account,
1415 l_parameter.expense_account, l_parameter.serial_number_generation, l_parameter.mat_ovhd_cost_type_id, l_parameter.project_reference_enabled,
1416 l_parameter.pm_cost_collection_enabled, l_parameter.project_control_level, l_parameter.avg_rates_cost_type_id, l_parameter.txn_approval_timeout_period,
1417 l_parameter.borrpay_matl_var_account, l_parameter.borrpay_moh_var_account, l_parameter.borrpay_res_var_account, l_parameter.borrpay_osp_var_account,
1418 l_parameter.borrpay_ovh_var_account, l_parameter.org_max_weight, l_parameter.org_max_volume, l_parameter.org_max_weight_uom_code,
1419 l_parameter.org_max_volume_uom_code, l_parameter.mo_source_required, l_parameter.mo_pick_confirm_required, l_parameter.mo_approval_timeout_action,
1420 l_parameter.project_cost_account,l_process_enabled_ind, l_parameter.organization_code, l_parameter.wsm_enabled_flag,
1421 l_cost_group_id, l_parameter.wms_enabled_flag, l_parameter.qa_skipping_insp_flag,p_default_status_id);
1422
1423
1424 /* Bug 5620938 - A default subinventory must be created for the organization */
1425 mtl_secondary_inventories_pkg.insert_row (
1426 x_rowid => l_rowid
1427 , x_secondary_inventory_name => l_orgn_code
1428 , x_organization_id => X_organization_id
1429 , x_last_update_date => SYSDATE
1430 , x_last_updated_by => 0
1431 , x_creation_date => SYSDATE
1432 , x_created_by => 0
1433 , x_last_update_login => 0
1434 , x_description => l_organization_name
1435 , x_disable_date => NULL
1436 , x_inventory_atp_code => 1
1437 , x_availability_type => 1
1438 , x_reservable_type => 1
1439 , x_locator_type => 5
1440 , x_picking_order => NULL
1441 , x_dropping_order => NULL
1442 , x_material_account => l_parameter.material_account
1443 , x_material_overhead_account => l_parameter.material_overhead_account
1444 , x_resource_account => l_parameter.resource_account
1445 , x_overhead_account => l_parameter.overhead_account
1446 , x_outside_processing_account => l_parameter.outside_processing_account
1447 , x_quantity_tracked => 1
1448 , x_asset_inventory => 1
1449 , x_source_type => NULL
1450 , x_source_subinventory => NULL
1451 , x_source_organization_id => NULL
1452 , x_requisition_approval_type => NULL
1453 , x_expense_account => l_parameter.expense_account
1454 , x_encumbrance_account => l_parameter.encumbrance_account
1455 , x_attribute_category => NULL
1456 , x_attribute1 => NULL
1457 , x_attribute2 => NULL
1458 , x_attribute3 => NULL
1459 , x_attribute4 => NULL
1460 , x_attribute5 => NULL
1461 , x_attribute6 => NULL
1462 , x_attribute7 => NULL
1463 , x_attribute8 => NULL
1464 , x_attribute9 => NULL
1465 , x_attribute10 => NULL
1466 , x_attribute11 => NULL
1467 , x_attribute12 => NULL
1468 , x_attribute13 => NULL
1469 , x_attribute14 => NULL
1470 , x_attribute15 => NULL
1471 , x_preprocessing_lead_time => NULL
1472 , x_processing_lead_time => NULL
1473 , x_postprocessing_lead_time => NULL
1474 , x_demand_class => NULL
1475 , x_project_id => NULL
1476 , x_task_id => NULL
1477 , x_subinventory_usage => NULL
1478 , x_notify_list_id => NULL
1479 , x_depreciable_flag => 2
1480 , x_location_id => NULL
1481 , x_status_id => 1
1482 , x_default_loc_status_id => 1
1483 , x_lpn_controlled_flag => 0
1484 , x_default_cost_group_id => l_cost_group_id
1485 , x_pick_uom_code => NULL
1486 , x_cartonization_flag => 0
1487 , x_planning_level => 2
1488 , x_default_count_type_code => 2
1489 , x_subinventory_type => 1
1490 , x_enable_bulk_pick => 'N');
1491
1492 /* Create back the link to hr locations to point to the created organization */
1493 IF p_addr_id IS NOT NULL THEN
1494 UPDATE hr_locations_all
1495 SET inventory_organization_id = X_organization_id
1496 WHERE location_code = p_orgn_code;
1497 END IF;
1498 END IF; /* IF (p_inventory_org_ind = 'Y' OR (p_inventory_org_ind IS NULL AND p_plant_ind <> 0)) */
1499
1500 EXCEPTION
1501 WHEN ORGN_MISSING THEN
1502 x_failure_count := x_failure_count + 1;
1503 GMA_COMMON_LOGGING.gma_migration_central_log (
1504 p_run_id => P_migration_run_id,
1505 p_log_level => FND_LOG.LEVEL_ERROR,
1506 p_message_token => 'GMA_TEMP_ORGN_MISSING_ERROR',
1507 p_table_name => 'SY_ORGN_MST',
1508 p_context => 'ORGANIZATION',
1509 p_token1 => 'ORGANIZATION',
1510 p_param1 => p_orgn_code,
1511 p_app_short_name => 'GMA');
1512
1513 WHEN COST_GROUP_SETUP_ERR THEN
1514 x_failure_count := x_failure_count + 1;
1515 GMA_COMMON_LOGGING.gma_migration_central_log (
1516 p_run_id => P_migration_run_id,
1517 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1518 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1519 p_table_name => 'SY_ORGN_MST',
1520 p_context => 'ORGANIZATION',
1521 p_db_error => p_orgn_code||'-'||l_msg_data,
1522 p_app_short_name => 'GMA');
1523 --Bug #13981860
1524 WHEN ORGN_CODE_EXISTING THEN
1525 x_failure_count := x_failure_count + 1;
1526 GMA_COMMON_LOGGING.gma_migration_central_log (
1527 p_run_id => P_migration_run_id,
1528 p_log_level => FND_LOG.LEVEL_ERROR,
1529 p_message_token => 'INV_ALREADY_EXISTS',
1530 p_table_name => 'SY_ORGN_MST',
1531 p_context => 'ORGANIZATION',
1532 p_param1 => P_organization_code,
1533 p_token1 => 'ORGANIZATION',
1534 p_app_short_name => 'INV');
1535
1536 WHEN OTHERS THEN
1537 x_failure_count := x_failure_count + 1;
1538
1539 GMA_COMMON_LOGGING.gma_migration_central_log (
1540 p_run_id => P_migration_run_id,
1541 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1542 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1543 p_table_name => 'SY_ORGN_MST',
1544 p_context => 'ORGANIZATION',
1545 p_db_error => SQLERRM,
1546 p_app_short_name => 'GMA');
1547 END create_organization;
1548
1549
1550 /*====================================================================
1551 -- PROCEDURE:
1552 -- migrate_organization
1553 --
1554 -- DESCRIPTION:
1555 -- This PL/SQL procedure is used to migrate the Organizations to
1556 -- Discrete tables .
1557 --
1558 --
1559 -- PARAMETERS:
1560 -- P_migration_run_id - id to use to right to migration log
1561 -- x_failure_count - Number of failures occurred.
1562 --
1563 -- SYNOPSIS:
1564 -- migrate_organization(p_migartion_id => l_migration_id,
1565 -- p_commit => 'T',
1566 -- x_failure_count => l_failure_count );
1567 --
1568 -- HISTORY
1569 -- 05-APR-2007 ACATALDO Bug 5727749 - Initialized migrate counter to
1570 -- avoid token showing in error log when a null
1571 -- is passed.
1572 -- 06-APR-2007 ACATALDO Bug 5955262 - Used correct token in exception
1573 -- block for migration table failure and
1574 -- warehouse insert error.
1575 -- 17-Dec-2007 RLNAGARA Bug 6607319 - Modified for Material Status ME
1576 --- 18-MAR-2010 Kedar Bavadekar Bug 9143261
1577 --- Made changes in procedure migrate_organization to populate
1578 --- default material status since it will not be populated if org
1579 --- migration is done in pre-migration phase when migrating to 12.1
1580 --====================================================================*/
1581
1582 PROCEDURE migrate_organization (P_migration_run_id IN NUMBER,
1583 P_commit IN VARCHAR2,
1584 X_failure_count OUT NOCOPY NUMBER) IS
1585
1586 --CURSORS
1587 CURSOR Cur_get_organization IS
1588 SELECT *
1589 FROM sy_orgn_mst
1590 WHERE (migrate_as_ind <> 0 OR migrate_as_ind IS NULL)
1591 AND (orgn_code <> co_code or plant_ind > 0) ;
1592 /* AND NVL(migrated_ind,0) = 0; Fix for Bug#9143261 */
1593 /* Organizations can be migrated in pre-migration. In this case, material
1594 status will not be populated if customer is migrating to 12.1 +
1595 */
1596
1597 --RLNAGARA Material Status Migration ME - Added the below cursor
1598 CURSOR Cur_get_status_id(v_status VARCHAR2) IS
1599 SELECT status_id
1600 FROM mtl_material_statuses
1601 WHERE status_code = v_status;
1602
1603 --LOCAL VARIABLES
1604 l_organization_id NUMBER;
1605 l_migration_id NUMBER;
1606 l_failure_count NUMBER;
1607 l_migrate_count NUMBER;
1608 l_default_status_id NUMBER; --RLNAGARA Material Status Migration ME
1609
1610 --Exceptions
1611 CREATE_ORGN_ERROR EXCEPTION;
1612 WHSE_CODE_ERROR EXCEPTION;
1613 --RLNAGARA Material Status Migration ME
1614 DEFAULT_STATUS_MISSING EXCEPTION;
1615 STATUS_ID_MISSING EXCEPTION;
1616 BEGIN
1617 l_migration_id := P_migration_run_id;
1618 X_failure_count := 0;
1619 l_migrate_count := 0; /* Bug 5727749 */
1620
1621
1622 GMA_COMMON_LOGGING.gma_migration_central_log (
1623 p_run_id => l_migration_id,
1624 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1625 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
1626 p_table_name => 'SY_ORGN_MST',
1627 p_context => 'ORGANIZATION',
1628 p_app_short_name => 'GMA');
1629
1630 FOR l_rec IN Cur_get_organization LOOP
1631 BEGIN
1632 SAVEPOINT Organization_Setup;
1633
1634 --RLNAGARA Material Status Migration ME
1635 IF (l_rec.default_status IS NULL) THEN
1636 RAISE DEFAULT_STATUS_MISSING;
1637 ELSE
1638 OPEN Cur_get_status_id(l_rec.default_status);
1639 FETCH Cur_get_status_id INTO l_default_status_id;
1640 IF Cur_get_status_id%NOTFOUND THEN
1641 CLOSE Cur_get_status_id;
1642 RAISE STATUS_ID_MISSING;
1643 END IF;
1644 CLOSE Cur_get_status_id;
1645 END IF;
1646
1647 IF nvl(l_rec.migrated_ind, 0) = 0 THEN -- Fix for Bug#9143261
1648 IF NVL(l_rec.migrate_as_ind, 1) IN (1,3) THEN
1649 create_organization (P_template_organization_id => l_rec.template_organization_id
1650 ,P_orgn_code => l_rec.orgn_code
1651 ,P_orgn_name => l_rec.orgn_name
1652 ,P_organization_code => l_rec.organization_code
1653 ,P_organization_name => l_rec.organization_name
1654 ,P_addr_id => l_rec.addr_id
1655 ,P_creation_date => l_rec.creation_date
1656 ,P_inventory_org_ind => l_rec.inventory_org_ind
1657 ,P_default_status_id => l_default_status_id --RLNAGARA Material Status Migration ME
1658 ,P_plant_ind => l_rec.plant_ind
1659 ,P_migrate_as_ind => l_rec.migrate_as_ind
1660 ,P_process_enabled_ind => l_rec.process_enabled_ind
1661 ,P_delete_mark => l_rec.delete_mark
1662 ,P_migration_run_id => p_migration_run_id
1663 ,X_organization_id => l_organization_id
1664 ,X_failure_count => l_failure_count);
1665 IF l_failure_count > 0 THEN
1666 RAISE CREATE_ORGN_ERROR;
1667 END IF;
1668 ELSE
1669 l_organization_id := l_rec.organization_id;
1670 --RLNAGARA Material Status Migration ME - Updating for already existing organizations
1671 UPDATE mtl_parameters
1672 SET default_status_id = l_default_status_id
1673 WHERE organization_id = l_rec.organization_id;
1674 END IF; /* IF NVL(l_rec.migrate_as_ind, 1) IN (1,3) */
1675
1676 UPDATE sy_orgn_mst_b
1677 SET organization_id = l_organization_id,
1678 migrated_ind = 1
1679 WHERE orgn_code = l_rec.orgn_code;
1680
1681 migrate_subinventory(P_migration_run_id => l_migration_id,
1682 P_orgn_code => l_rec.orgn_code,
1683 P_commit => FND_API.G_FALSE,
1684 X_failure_count => l_failure_count);
1685 IF (l_failure_count > 0) THEN
1686 RAISE WHSE_CODE_ERROR;
1687 END IF;
1688 --Bases on the p_commit flag commit the transaction.
1689 -- Fix for Bug#9143261. Moved following statements if org is being
1690 -- migrated in in-line phase
1691 IF p_commit = FND_API.G_TRUE THEN
1692 COMMIT;
1693 END IF;
1694 l_migrate_count := l_migrate_count + 1;
1695 ELSE -- Fix for Bug#9143261. Added following statements in else clause
1696 IF l_rec.default_status IS NOT NULL THEN
1697
1698 UPDATE mtl_parameters
1699 SET default_status_id = l_default_status_id
1700 WHERE organization_id = l_rec.organization_id
1701 AND default_status_id IS NULL ;
1702
1703 --Bases on the p_commit flag commit the transaction.
1704 IF (p_commit = FND_API.G_TRUE AND SQL%ROWCOUNT > 0 ) THEN
1705 COMMIT;
1706 l_migrate_count := l_migrate_count + 1;
1707 END IF;
1708 END IF ;
1709 END IF ;
1710
1711 EXCEPTION
1712 WHEN CREATE_ORGN_ERROR THEN
1713 x_failure_count := x_failure_count + 1;
1714 ROLLBACK TO Organization_Setup;
1715 GMA_COMMON_LOGGING.gma_migration_central_log (
1716 p_run_id => l_migration_id,
1717 p_log_level => FND_LOG.LEVEL_ERROR,
1718 p_message_token => 'GMA_ORGN_MISSING_ERROR',
1719 p_table_name => 'SY_ORGN_MST',
1720 p_context => 'ORGANIZATION',
1721 p_token1 => 'ORGANIZATION',
1722 p_param1 => l_rec.orgn_code,
1723 p_app_short_name => 'GMA');
1724
1725 WHEN WHSE_CODE_ERROR THEN
1726 GMA_COMMON_LOGGING.gma_migration_central_log (
1727 p_run_id => l_migration_id,
1728 p_log_level => FND_LOG.LEVEL_ERROR,
1729 p_message_token => 'GMA_MIGRATION_FAIL',
1730 p_token1 => 'PARAM1',
1731 p_param1 => 'due to an error in migrating the W/H as a subinventory',
1732 p_context => 'ORGANIZATION',
1733 p_app_short_name => 'GMA');
1734
1735 --RLNAGARA Material Status Migration ME
1736 WHEN DEFAULT_STATUS_MISSING THEN
1737 x_failure_count := x_failure_count + 1;
1738 GMA_COMMON_LOGGING.gma_migration_central_log(
1739 p_run_id => P_migration_run_id,
1740 p_log_level => FND_LOG.LEVEL_ERROR,
1741 p_message_token => 'GMA_DEFAULT_STATUS_MISSING_ERROR',
1742 p_table_name => 'SY_ORGN_MST',
1743 p_context => 'ORGANIZATION',
1744 p_token1 => 'ORGANIZATION',
1745 p_param1 => l_rec.organization_code,
1746 p_app_short_name => 'GMA');
1747
1748 --RLNAGARA Material Status Migration ME
1749 WHEN STATUS_ID_MISSING THEN
1750 x_failure_count := x_failure_count + 1;
1751 GMA_COMMON_LOGGING.gma_migration_central_log(
1752 p_run_id => P_migration_run_id,
1753 p_log_level => FND_LOG.LEVEL_ERROR,
1754 p_message_token => 'GMD_MIG_STATUS_ID',
1755 p_table_name => 'MTL_MATERIAL_STATUSES',
1756 p_context => 'STAT',
1757 p_token1 => 'STAT',
1758 p_param1 => l_rec.default_status,
1759 p_app_short_name => 'GMA');
1760
1761 WHEN OTHERS THEN
1762 x_failure_count := x_failure_count + 1;
1763 ROLLBACK TO Organization_Setup;
1764 GMA_COMMON_LOGGING.gma_migration_central_log (
1765 p_run_id => l_migration_id,
1766 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1767 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1768 p_table_name => 'SY_ORGN_MST',
1769 p_context => 'ORGANIZATION',
1770 p_db_error => SQLERRM,
1771 p_app_short_name => 'GMA');
1772 END;
1773 END LOOP;
1774
1775 GMA_COMMON_LOGGING.gma_migration_central_log (
1776 p_run_id => l_migration_id,
1777 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1778 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
1779 p_table_name => 'SY_ORGN_MST',
1780 p_context => 'ORGANIZATION',
1781 p_param1 => l_migrate_count,
1782 p_param2 => X_failure_count,
1783 p_app_short_name => 'GMA');
1784
1785
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788 x_failure_count := X_failure_count + 1;
1789
1790 GMA_COMMON_LOGGING.gma_migration_central_log (
1791 p_run_id => l_migration_id,
1792 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
1793 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1794 p_table_name => 'SY_ORGN_MST',
1795 p_context => 'ORGANIZATION',
1796 p_db_error => SQLERRM,
1797 p_app_short_name => 'GMA');
1798
1799 GMA_COMMON_LOGGING.gma_migration_central_log (
1800 p_run_id => l_migration_id,
1801 p_log_level => FND_LOG.LEVEL_PROCEDURE,
1802 p_message_token => 'GMA_MIGRATION_TABLE_FAIL',
1803 p_table_name => 'SY_ORGN_MST',
1804 p_context => 'ORGANIZATION',
1805 p_app_short_name => 'GMA');
1806
1807 END migrate_organization;
1808
1809 END INV_MIGRATE_PROCESS_ORG;
1810