[Home] [Help]
PACKAGE BODY: APPS.EC_MAPPING_UTILS
Source
1 PACKAGE BODY ec_mapping_utils AS
2 -- $Header: ECMAPUTB.pls 120.2 2005/09/29 10:50:29 arsriniv ship $
3
4 TYPE t_layout_record IS RECORD(
5 interface_column_id ece_interface_columns.interface_column_id%TYPE,
6 record_number ece_interface_columns.record_number%TYPE,
7 position ece_interface_columns.position%TYPE,
8 width ece_interface_columns.width%TYPE,
9 conversion_sequence ece_interface_columns.conversion_sequence%TYPE,
10 record_layout_code ece_interface_columns.record_layout_code%TYPE,
11 record_layout_qualifier ece_interface_columns.record_layout_qualifier%TYPE);
12
13 CURSOR c_external_levels_upg(xMap_ID NUMBER) IS
14 SELECT external_level_id
15 FROM ece_external_levels_upg
16 WHERE map_id = xMap_ID;
17
18 CURSOR c_interface_columns_upg(xMap_ID NUMBER) IS
19 SELECT eicu.interface_column_id interface_column_id
20 FROM ece_interface_cols_upg eicu,
21 ece_interface_tbls_upg eitu
22 WHERE eicu.interface_table_id = eitu.interface_table_id AND
23 eitu.map_id = xMap_ID;
24
25 CURSOR c_interface_tables_upg(xMap_ID NUMBER) IS
26 SELECT interface_table_id
27 FROM ece_interface_tbls_upg
28 WHERE map_id = xMap_ID;
29
30 CURSOR c_maps (xTransactionType ece_interface_tables.transaction_type%TYPE,
31 xMapType ece_mappings.map_type%TYPE) IS
32 SELECT map_id,
33 map_code
34 FROM ece_mappings
35 WHERE transaction_type = xTransactionType AND
36 map_type = NVL(xMapType,map_type);
37
38 CURSOR c_maps_upg(xTransactionType ece_interface_tables.transaction_type%TYPE,
39 xMapType ece_mappings.map_type%TYPE) IS
40 SELECT map_id,
41 map_code
42 FROM ece_mappings_upg
43 WHERE transaction_type = xTransactionType AND
44 map_type = NVL(xMapType,map_type);
45
46 CURSOR c_process_rules(xMap_ID NUMBER) IS
47 SELECT rule_type,
48 action_code
49 FROM ece_process_rules
50 WHERE map_id = xMap_ID;
51
52 CURSOR c_process_rules_upg(xMap_ID NUMBER) IS
53 SELECT rule_type,
54 action_code
55 FROM ece_process_rules_upg
56 WHERE map_id = xMap_ID;
57
58 CURSOR c_tran_stage_data_upg(xMap_ID NUMBER) IS
59 SELECT transtage_id
60 FROM ece_tran_stage_data_upg
61 WHERE map_id = xMap_ID;
62
63 --Return map_id based on map_code
64 FUNCTION ec_get_map_id(
65 xMapCode IN ece_mappings.map_code%TYPE,
66 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') RETURN NUMBER AS
67
68 iMap_ID NUMBER;
69
70 BEGIN
71 IF xUpgradeFlag = 'Y' THEN
72 SELECT map_id INTO iMap_ID
73 FROM ece_mappings_upg
74 WHERE map_code = xMapCode;
75 ELSE
76 SELECT map_id INTO iMap_ID
77 FROM ece_mappings
78 WHERE map_code = xMapCode;
79 END IF;
80
81 RETURN iMap_ID;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 NULL;
86
87 END ec_get_map_id;
88
89 --Return map_code based on map_id
90 FUNCTION ec_get_map_code(
91 xMap_ID IN NUMBER,
92 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') RETURN ece_mappings.map_code%TYPE AS
93
94 cMapCode ece_mappings.map_code%TYPE;
95
96 BEGIN
97 IF xUpgradeFlag = 'Y' THEN
98 SELECT map_code INTO cMapCode
99 FROM ece_mappings_upg
100 WHERE map_id = xMap_ID;
101 ELSE
102 SELECT map_code INTO cMapCode
103 FROM ece_mappings
104 WHERE map_id = xMap_ID;
105 END IF;
106
107 RETURN cMapCode;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 NULL;
112
113 END ec_get_map_code;
114
115 FUNCTION ec_get_upgrade_map_id(
116 xMap_ID IN NUMBER) RETURN NUMBER AS
117
118 iMap_ID NUMBER;
119
120 BEGIN
121 SELECT ecmu.map_id INTO iMap_ID
122 FROM ece_mappings_upg ecmu,
123 ece_mappings ecm
124 WHERE ecmu.map_code = ecm.map_code AND
125 ecm.map_id = xMap_ID;
126
127 RETURN iMap_ID;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 NULL;
132
133 END ec_get_upgrade_map_id;
134
135 FUNCTION ec_get_main_map_id(
136 xMap_ID IN NUMBER) RETURN NUMBER AS
137
138 iMap_ID NUMBER;
139
140 BEGIN
141 SELECT ecm.map_id INTO iMap_ID
142 FROM ece_mappings_upg ecmu,
143 ece_mappings ecm
144 WHERE ecm.map_code = ecmu.map_code AND
145 ecmu.map_id = xMap_ID;
146
147 RETURN iMap_ID;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 NULL;
152
153 END ec_get_main_map_id;
154
155 --This procedure tells you whether your transaction has seed data
156 --reconciliation pending or not.
157 --U = Reconciliation Pending
158 --N = Transaction Clean
159 FUNCTION ec_get_trans_upgrade_status(
160 xTransactionType IN ece_interface_tables.transaction_type%TYPE,
161 iMapId IN ece_interface_tables.map_id%TYPE) RETURN VARCHAR2 AS
162
163 cUpgradedFlag ece_interface_tables.upgraded_flag%TYPE;
164
165 BEGIN
166 SELECT NVL(upgraded_flag,'N') INTO cUpgradedFlag
167 FROM ece_interface_tables
168 WHERE transaction_type = xTransactionType AND
169 map_id = iMapId AND
170 ROWNUM = 1;
171
172 IF cUpgradedFlag = 'Y' THEN
173 RETURN 'U';
174 ELSE
175 RETURN 'N';
176 END IF;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 NULL;
181
182 END ec_get_trans_upgrade_status;
183
184 FUNCTION ec_get_trans_upgrade_status(
185 xTransactionType IN ece_interface_tables.transaction_type%TYPE) RETURN VARCHAR2 AS
186
187 cUpgradedFlag ece_interface_tables.upgraded_flag%TYPE;
188
189 BEGIN
190 SELECT NVL(upgraded_flag,'N') INTO cUpgradedFlag
191 FROM ece_interface_tables
192 WHERE transaction_type = xTransactionType AND
193 ROWNUM = 1;
194
195 IF cUpgradedFlag = 'Y' THEN
196 RETURN 'U';
197 ELSE
198 RETURN 'N';
199 END IF;
200
201 EXCEPTION
202 WHEN OTHERS THEN
203 NULL;
204
205 END ec_get_trans_upgrade_status;
206
207 PROCEDURE ec_upgrade_code_cat(
208 xInterface_Column_ID_Main IN NUMBER,
209 xInterface_Column_ID_Upg IN NUMBER) IS
210
211 iXref_Category_ID NUMBER;
212 iKey1 ece_interface_columns.xref_key1_source_column%TYPE;
213 iKey2 ece_interface_columns.xref_key2_source_column%TYPE;
214 iKey3 ece_interface_columns.xref_key3_source_column%TYPE;
215 iKey4 ece_interface_columns.xref_key4_source_column%TYPE;
216 iKey5 ece_interface_columns.xref_key5_source_column%TYPE;
217
218 BEGIN
219 SELECT xref_category_id,
220 xref_key1_source_column,
221 xref_key2_source_column,
222 xref_key3_source_column,
223 xref_key4_source_column,
224 xref_key5_source_column
225 INTO iXref_Category_ID,
226 iKey1,
227 iKey2,
228 iKey3,
229 iKey4,
230 iKey5
231 FROM ece_interface_columns
232 WHERE interface_column_id = xInterface_Column_ID_Main;
233
234 IF iXref_Category_ID IS NOT NULL THEN
235 UPDATE ece_interface_cols_upg
236 SET xref_category_id = iXref_Category_ID,
237 xref_key1_source_column = iKey1,
238 xref_key2_source_column = iKey2,
239 xref_key3_source_column = iKey3,
240 xref_key4_source_column = iKey4,
241 xref_key5_source_column = iKey5
242 WHERE interface_column_id = xInterface_Column_ID_Upg;
243 ec_debug.pl(0,'*Code Conversion Assignment found for this column and reconciled.');
244 ELSE
245 ec_debug.pl(0,'*No Code Conversion Assignment found for this column.');
246 END IF;
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 NULL;
251
252 END ec_upgrade_code_cat;
253
254 PROCEDURE ec_upgrade_column_rules(
255 xInterface_Column_ID_Main IN NUMBER,
256 xInterface_Column_ID_Upg IN NUMBER) IS
257
258 iCount NUMBER DEFAULT 0;
259
260 CURSOR c_column_rules(xInterface_Column_ID NUMBER) IS
261 SELECT column_rule_id,
262 interface_column_id,
263 sequence,
264 rule_type,
265 action_code
266 FROM ece_column_rules
267 WHERE interface_column_id = xInterface_Column_ID
268 ORDER BY sequence;
269
270 BEGIN
271 FOR v_column_rules IN c_column_rules(xInterface_Column_ID_Main) LOOP
272 UPDATE ece_column_rules
273 SET interface_column_id = xInterface_Column_ID_Upg
274 WHERE column_rule_id = v_column_rules.column_rule_id;
275 --v_column_rules.interface_column_id := xInterface_Column_ID_Upg;
276 ec_debug.pl(0,'*Column Rule Sequence: ' || v_column_rules.sequence || ', Rule Type: ' ||
277 v_column_rules.rule_type || ', Action Code: ' || v_column_rules.action_code ||
278 ' found and reconciled for this column.');
279 iCount := iCount + 1;
280 END LOOP;
281
282 IF iCount = 0 THEN
283 ec_debug.pl(0,'*This column has no Column Rules assigned.');
284 END IF;
285
286 END ec_upgrade_column_rules;
287
288 PROCEDURE ec_upgrade_layout(
289 xMap_ID_Upg IN NUMBER,
290 xUpgrade_Column_Rules_Flag IN VARCHAR2,
291 xUpgrade_Code_Cat_Flag IN VARCHAR2) IS
292
293 iCount NUMBER DEFAULT 0;
294 iMap_ID_Main NUMBER;
295 v_layout_record t_layout_record;
296 xDirection ece_interface_tables.direction%type;
297 xExt_Col_Count number;
298
299 /* Bug 2138714 Removed the nvl conditions on Record no,position,width,conversion_sequence */
300
301 CURSOR c_interface_columns_layout_upg(xMap_ID NUMBER) IS
302 SELECT eitu.interface_table_id interface_table_id,
303 eitu.interface_table_name interface_table_name,
304 eitu.output_level output_level,
305 eicu.interface_column_id interface_column_id,
306 eicu.interface_column_name interface_column_name,
307 eicu.record_number record_number,
308 eicu.position position,
309 eicu.width width,
310 eicu.conversion_sequence conversion_sequence,
311 NVL(eicu.record_layout_code,' ') record_layout_code,
312 NVL(eicu.record_layout_qualifier,' ') record_layout_qualifier
313 FROM ece_interface_cols_upg eicu,
314 ece_interface_tbls_upg eitu
315 WHERE eicu.interface_table_id = eitu.interface_table_id AND
316 eitu.map_id = xMap_ID AND
317 eicu.interface_column_name IS NOT NULL
318 ORDER BY TO_NUMBER(eitu.output_level),
319 eicu.interface_column_name;
320
321 CURSOR c_interface_columns_dups_upg(xMap_ID NUMBER) IS
322 SELECT TO_NUMBER(eitu.output_level) output_level,
323 eicu.record_number record_number,
324 eicu.position position,
325 eitu.map_id map_id,
326 COUNT(*) count
327 FROM ece_interface_cols_upg eicu,
328 ece_interface_tbls_upg eitu
329 WHERE eitu.map_id = xMap_ID AND
330 eitu.interface_table_id = eicu.interface_table_id AND
331 eicu.record_number IS NOT NULL AND --These lines are used to filter out unmapped
332 eicu.position IS NOT NULL --records which are not true duplicates.
333 GROUP BY eicu.record_number,
334 eicu.position,
335 eitu.map_id,
336 TO_NUMBER(eitu.output_level)
337 HAVING COUNT(*) > 1
338 ORDER BY TO_NUMBER(eitu.output_level),
339 eicu.record_number,
340 eicu.position;
341
342 CURSOR c_interface_tables_upg2(xMap_ID NUMBER) IS
343 SELECT eitu.interface_table_id interface_table_id,
344 eitu.output_level output_level,
345 eitu.map_id map_id
349 BEGIN
346 FROM ece_interface_tbls_upg eitu
347 WHERE eitu.map_id = xMap_ID;
348
350 iMap_ID_Main := ec_get_main_map_id(xMap_ID_Upg);
351
352 FOR v_interface_columns_layout_upg IN c_interface_columns_layout_upg(xMap_ID_Upg) LOOP
353 --Loop through ece_interface_cols_upg
354 BEGIN
355 --Look for matching Interface Column Name
356 /* Bug 2138714 Removed the nvl conditions on Record no,position,width,conversion_sequence */
357
358 SELECT eic.interface_column_id,
359 eic.record_number,
360 eic.position,
361 eic.width,
362 eic.conversion_sequence,
363 NVL(eic.record_layout_code,' '),
364 NVL(eic.record_layout_qualifier,' ')
365 INTO v_layout_record
366 FROM ece_interface_columns eic,
367 ece_interface_tables eit
368 WHERE eic.interface_table_id = eit.interface_table_id AND
369 eit.map_id = iMap_ID_Main AND
370 eit.output_level = v_interface_columns_layout_upg.output_level AND
371 eic.interface_column_name = v_interface_columns_layout_upg.interface_column_name;
372 --AND ROWNUM = 2;
373
374 ec_debug.pl(0,'*****Column: "' || v_interface_columns_layout_upg.interface_column_name || '"');
375
376 --Reconcile Column Rules?
377 IF xUpgrade_Column_Rules_Flag = 'Y' THEN
378 ec_debug.pl(0,'*Checking to see if any Column Rules need to be reconciled...');
379 ec_upgrade_column_rules(v_layout_record.interface_column_id,v_interface_columns_layout_upg.interface_column_id); --(Main,Upgrade)
380 ELSE
381 ec_debug.pl(0,'*Column Rule Reconciliation not enabled.');
382 END IF;
383
384 --Reconcile Code Conversion Categories?
385 IF xUpgrade_Code_Cat_Flag = 'Y' THEN
386 ec_debug.pl(0,'*Checking to see if any Code Conversion Assignments need to be reconciled...');
387 ec_upgrade_code_cat(v_layout_record.interface_column_id,v_interface_columns_layout_upg.interface_column_id); --(Main,Upgrade)
388 ELSE
389 ec_debug.pl(0,'*Code Conversion Assignment Reconciliation not enabled.');
390 END IF;
391
392 --Do the Record Number and Position match?
393 IF v_layout_record.record_number = v_interface_columns_layout_upg.record_number AND
394 v_layout_record.position = v_interface_columns_layout_upg.position THEN
395 ec_debug.pl(0,'*Record Number and Position for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
396 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' unchanged.');
397 ELSE
398 ec_debug.pl(0,'*Record Number and Position for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
399 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' changed to ' ||
400 v_interface_columns_layout_upg.record_number || '/' || v_interface_columns_layout_upg.position ||
401 '. old Record Number and Position: ' || v_layout_record.record_number || '/' || v_layout_record.position ||
402 ' will be preserved.');
403
404 UPDATE ece_interface_cols_upg
405 SET record_number = v_layout_record.record_number,
406 position = v_layout_record.position
407 WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
408 --v_interface_columns_layout_upg.record_number := v_layout_record.record_number;
409 --v_interface_columns_layout_upg.position := v_layout_record.position;
410 END IF;
411
412 --Does the Width Match?
413 IF v_layout_record.width = v_interface_columns_layout_upg.width THEN
414 ec_debug.pl(0,'*Width for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
415 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' unchanged.');
416 ELSE
417 ec_debug.pl(0,'*Width for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
418 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' changed to ' ||
419 v_interface_columns_layout_upg.width || '. old Width: ' || v_layout_record.width || ' will be preserved.');
420 UPDATE ece_interface_cols_upg
421 SET width = v_layout_record.width
422 WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
423 --v_interface_columns_layout_upg.width := v_layout_record.width;
424 END IF;
425
426 --Does Conversion Sequence Match?
427 IF v_layout_record.conversion_sequence= v_interface_columns_layout_upg.conversion_sequence THEN
428 ec_debug.pl(0,'*Conversion Sequence for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
432 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' changed to ' ||
429 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' unchanged.');
430 ELSE
431 ec_debug.pl(0,'*Conversion Sequence for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
433 v_interface_columns_layout_upg.conversion_sequence || '. old Conversion Sequence: ' ||
434 v_layout_record.conversion_sequence || ' will be preserved.');
435 UPDATE ece_interface_cols_upg
436 SET conversion_sequence = v_layout_record.conversion_sequence
437 WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
438 --v_interface_columns_layout_upg.conversion_sequence := v_layout_record.conversion_sequence;
439 END IF;
440
441 --Does Record Layout Code Match?
442 IF v_layout_record.record_layout_code= v_interface_columns_layout_upg.record_layout_code THEN
443 ec_debug.pl(0,'*Record Layout Code for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
444 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' unchanged.');
445 ELSE
446 ec_debug.pl(0,'*Record Layout Code for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
447 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' changed to ' ||
448 v_interface_columns_layout_upg.record_layout_code || '. old Record Layout Code: ' ||
449 v_layout_record.record_layout_code || ' will be preserved.');
450 UPDATE ece_interface_cols_upg
451 SET record_layout_code = v_layout_record.record_layout_code
452 WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
453 --v_interface_columns_layout_upg.record_layout_code := v_layout_record.record_layout_code;
454 END IF;
455
456 --Does Record Layout Qualifier Match?
457 IF v_layout_record.record_layout_qualifier= v_interface_columns_layout_upg.record_layout_qualifier THEN
458 ec_debug.pl(0,'*Record Layout Qualifier for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
459 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' unchanged.');
460 ELSE
461 ec_debug.pl(0,'*Record Layout Qualifier for Column: "' || v_interface_columns_layout_upg.interface_column_name ||
462 '" at Output Level: ' || v_interface_columns_layout_upg.output_level || ' changed to ' ||
463 v_interface_columns_layout_upg.record_layout_qualifier || '. old Record Layout Qualifier: ' ||
464 v_layout_record.record_layout_qualifier || ' will be preserved.');
465 UPDATE ece_interface_cols_upg
466 SET record_layout_qualifier = v_layout_record.record_layout_qualifier
467 WHERE interface_column_id = v_interface_columns_layout_upg.interface_column_id;
468 --v_interface_columns_layout_upg.record_layout_qualifier := v_layout_record.record_layout_qualifier;
469 END IF;
470
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 --This must be a new column in new seed data since no matching column name was found in
474 --old seed data.
475 ec_debug.pl(0,'*New column found: "' || v_interface_columns_layout_upg.interface_column_name || '".');
476
477 END;
478
479 END LOOP;
480
481 -- 2839189
482 -- The following preserves custom columns for Transactions using
483 -- the 11.0 architechure.
484 BEGIN
485 select direction
486 into xDirection
487 from ece_interface_tables
488 where map_id = iMap_ID_Main
489 and rownum=1;
490
491 IF xDirection='OUT' THEN
492
493 select count(*)
494 into xExt_Col_Count
495 from ece_interface_columns
496 where record_number like '_9__'
497 and map_id = iMap_ID_Main;
498
499 ec_debug.pl(0,'xExt_Col_Count',xExt_Col_Count);
500
501 IF xExt_Col_Count >0 THEN
502 FOR v_interface_tables_upg IN c_interface_tables_upg2(xMap_ID_Upg)
503 LOOP
504
505 INSERT INTO ece_interface_cols_upg(
506 interface_column_id,
507 interface_table_id,
508 interface_column_name,
509 base_table_name,
510 base_column_name,
511 table_name,
512 column_name,
513 record_number,
514 position,
515 width,
516 data_type,
517 conversion_sequence,
518 record_layout_code,
519 record_layout_qualifier,
520 conversion_group_id,
521 xref_category_allowed,
522 xref_category_id,
523 xref_key1_source_column,
527 xref_key5_source_column,
524 xref_key2_source_column,
525 xref_key3_source_column,
526 xref_key4_source_column,
528 external_level,
529 map_id,
530 creation_date,
531 created_by,
532 last_update_date,
533 last_updated_by,
534 last_update_login)
535 SELECT ece_interface_column_id_s.NEXTVAL,
536 v_interface_tables_upg.interface_table_id,
537 eic.interface_column_name,
538 eic.base_table_name,
539 eic.base_column_name,
540 eic.table_name,
541 eic.column_name,
542 eic.record_number,
543 eic.position,
544 eic.width,
545 eic.data_type,
546 eic.conversion_sequence,
547 eic.record_layout_code,
548 eic.record_layout_qualifier,
549 eic.conversion_group_id,
550 eic.xref_category_allowed,
551 eic.xref_category_id,
552 eic.xref_key1_source_column,
553 eic.xref_key2_source_column,
554 eic.xref_key3_source_column,
555 eic.xref_key4_source_column,
556 eic.xref_key5_source_column,
557 eic.external_level,
558 v_interface_tables_upg.map_id,
559 SYSDATE,
560 1,
561 SYSDATE,
562 1,
563 1
564 FROM ece_interface_columns eic,
565 ece_interface_tables eit
566 WHERE eic.map_id=iMap_ID_Main
567 AND eic.record_number like '_9__'
568 AND eic.interface_table_id = eit.interface_table_id
569 AND eic.map_id = eit.map_id
570 AND eit.output_level = v_interface_tables_upg.output_level
571 AND eic.interface_column_name not in(select interface_column_name
572 from ece_interface_cols_upg
573 where record_number like '_9__'
574 and map_id=v_interface_tables_upg.map_id);
575 END LOOP;
576 ec_debug.pl(0,'Custom Columns are presevered');
577 ELSE
578 ec_debug.pl(0,'No Custom Columns are present');
579 END IF;
580
581 END IF;
582 EXCEPTION
583 WHEN others THEN
584 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
585 END;
586
587 --Now that we're done looping through ece_interface_cols_upg table, let's check to see what Record Number
588 --and Positions are now duplicated because of Reconciliation Process
589 ec_debug.pl(0,'*****Following Record Numbers and Positions are duplicated due to reconciliation process.');
590 FOR v_interface_columns_dups_upg IN c_interface_columns_dups_upg(xMap_ID_Upg) LOOP
591 ec_debug.pl(0,'Output Level: ' || v_interface_columns_dups_upg.output_level ||
592 ', Record Number: ' || v_interface_columns_dups_upg.record_number ||
593 ', Position: ' || v_interface_columns_dups_upg.position || '.');
594 iCount := iCount + 1;
595 END LOOP;
596
597 IF iCount = 0 THEN
598 ec_debug.pl(0,'*****There were no duplicate Record Numbers and Positions.');
599 END IF;
600
601 END ec_upgrade_layout;
602
603 PROCEDURE ec_upgrade_process_rules(
604 xMap_ID IN NUMBER) IS --Upgrade Table Map_ID
605
606 iMap_ID_Main NUMBER;
607
608 BEGIN
609 iMap_ID_Main := ec_get_main_map_id(xMap_ID);
610
611 FOR v_process_rules IN c_process_rules(iMap_ID_Main) LOOP
612 UPDATE ece_process_rules_upg
613 SET action_code = v_process_rules.action_code
614 WHERE map_id = xMap_ID AND
615 rule_type = v_process_rules.rule_type;
616 END LOOP;
617 END ec_upgrade_process_rules;
618
619 --Copy data from ECE_COLUMN_RULES_UPG to ECE_COLUMN_RULES.
620 PROCEDURE ec_copy_column_rules(
621 xMap_ID IN NUMBER) AS
622
623 BEGIN
624 FOR v_interface_columns_upg IN c_interface_columns_upg(xMap_ID) LOOP
625 INSERT INTO ece_column_rules(
626 column_rule_id,
627 interface_column_id,
628 sequence,
629 rule_type,
630 action_code,
631 creation_date,
632 created_by,
633 last_update_date,
634 last_updated_by,
635 last_update_login,
636 request_id,
637 program_application_id,
638 program_id,
639 program_update_date)
643 rule_type,
640 SELECT column_rule_id,
641 interface_column_id,
642 sequence,
644 action_code,
645 creation_date,
646 created_by,
647 last_update_date,
648 last_updated_by,
649 last_update_login,
650 request_id,
651 program_application_id,
652 program_id,
653 program_update_date
654 FROM ece_column_rules_upg
655 WHERE interface_column_id = v_interface_columns_upg.interface_column_id;
656 END LOOP;
657
658 END ec_copy_column_rules;
659
660 --Copy data from ece_proc_mappings_upg to ECE_PROCEDURE_MAPPINGS and
661 -- from ECE_TRAN_STAGE_DATA_UPG to ECE_TRAN_STAGE_DATA.
662 PROCEDURE ec_copy_dynamic_actions(
663 xMap_ID IN NUMBER) AS
664
665 BEGIN
666 --Copy data in ece_proc_mappings_upg
667 FOR v_tran_stage_data_upg IN c_tran_stage_data_upg(xMap_ID) LOOP
668 INSERT INTO ece_procedure_mappings(
669 procmap_id,
670 transtage_id,
671 parameter_name,
672 action_type,
673 variable_level,
674 variable_name)
675 SELECT procmap_id,
676 transtage_id,
677 parameter_name,
678 action_type,
679 variable_level,
680 variable_name
681 FROM ece_proc_mappings_upg
682 WHERE transtage_id = v_tran_stage_data_upg.transtage_id;
683 END LOOP;
684
685 --Copy data in ECE_TRAN_STAGE_DATA_UPG
686 INSERT INTO ece_tran_stage_data(
687 transaction_type,
688 transaction_level,
689 stage,
690 seq_number,
691 action_type,
692 variable_level,
693 variable_name,
694 variable_value,
695 default_value,
696 previous_variable_level,
697 previous_variable_name,
698 sequence_name,
699 custom_procedure_name,
700 data_type,
701 function_name,
702 next_variable_name,
703 where_clause,
704 map_id,
705 transtage_id)
706 SELECT transaction_type,
707 transaction_level,
708 stage,
709 seq_number,
710 action_type,
711 variable_level,
712 variable_name,
713 variable_value,
714 default_value,
715 previous_variable_level,
716 previous_variable_name,
717 sequence_name,
718 custom_procedure_name,
719 data_type,
720 function_name,
721 next_variable_name,
722 where_clause,
723 map_id,
724 transtage_id
725 FROM ece_tran_stage_data_upg
726 WHERE map_id = xMap_ID;
727 END ec_copy_dynamic_actions;
728
729 PROCEDURE ec_copy_external_levels(
730 xMap_ID IN NUMBER) AS
731
732 BEGIN
733 INSERT INTO ece_external_levels(
734 external_level_id,
735 external_level,
736 description,
737 map_id,
738 transaction_type,
739 created_by,
740 creation_date,
741 last_update_login,
742 last_update_date,
743 last_updated_by,
744 start_element,
745 parent_level,
746 enabled_flag)
747 SELECT external_level_id,
748 external_level,
749 description,
750 map_id,
751 transaction_type,
752 created_by,
753 creation_date,
754 last_update_login,
755 last_update_date,
756 last_updated_by,
757 start_element,
758 parent_level,
759 enabled_flag
760 FROM ece_external_levels_upg
761 WHERE map_id = xMap_ID;
762 END ec_copy_external_levels;
763
764 PROCEDURE ec_copy_interface_columns(
765 xMap_ID IN NUMBER) AS
766
767 BEGIN
768 FOR v_interface_tables_upg IN c_interface_tables_upg(xMap_ID) LOOP
769 INSERT INTO ece_interface_columns(
770 interface_column_id,
771 interface_table_id,
772 interface_column_name,
773 base_table_name,
774 base_column_name,
775 table_name,
776 column_name,
777 record_number,
781 data_type,
778 position,
779 width,
780 conversion_sequence,
782 conversion_group_id,
783 xref_category_allowed,
784 xref_category_id,
785 xref_key1_source_column,
786 xref_key2_source_column,
787 xref_key3_source_column,
788 xref_key4_source_column,
789 xref_key5_source_column,
790 record_layout_code,
791 record_layout_qualifier,
792 data_loc_id,
793 created_by,
794 creation_date,
795 last_update_login,
796 last_update_date,
797 last_updated_by,
798 request_id,
799 program_application_id,
800 program_id,
801 program_update_date,
802 staging_column,
803 element_tag_name,
804 map_id,
805 external_level)
806 SELECT interface_column_id,
807 interface_table_id,
808 interface_column_name,
809 base_table_name,
810 base_column_name,
811 table_name,
812 column_name,
813 record_number,
814 position,
815 width,
816 conversion_sequence,
817 data_type,
818 conversion_group_id,
819 xref_category_allowed,
820 xref_category_id,
821 xref_key1_source_column,
822 xref_key2_source_column,
823 xref_key3_source_column,
824 xref_key4_source_column,
825 xref_key5_source_column,
826 record_layout_code,
827 record_layout_qualifier,
828 data_loc_id,
829 created_by,
830 creation_date,
831 last_update_login,
832 last_update_date,
833 last_updated_by,
834 request_id,
835 program_application_id,
836 program_id,
837 program_update_date,
838 staging_column,
839 element_tag_name,
840 map_id,
841 external_level
842 FROM ece_interface_cols_upg
843 WHERE interface_table_id = v_interface_tables_upg.interface_table_id;
844 END LOOP;
845 END ec_copy_interface_columns;
846
847 PROCEDURE ec_copy_interface_tables(
848 xMap_ID IN NUMBER) AS
849
850 BEGIN
851 INSERT INTO ece_interface_tables(
852 interface_table_id,
853 transaction_type,
854 output_level,
855 interface_table_name,
856 extension_table_name,
857 key_column_name,
858 start_number,
859 created_by,
860 creation_date,
861 last_update_login,
862 last_update_date,
863 last_updated_by,
864 request_id,
865 program_application_id,
866 program_id,
867 program_update_date,
868 flatfile_version,
869 direction,
870 primary_address_type,
871 parent_level,
872 installed_flag,
873 map_id,
874 enabled,
875 upgraded_flag)
876 SELECT interface_table_id,
877 transaction_type,
878 output_level,
879 interface_table_name,
880 extension_table_name,
881 key_column_name,
882 start_number,
883 created_by,
884 creation_date,
885 last_update_login,
886 last_update_date,
887 last_updated_by,
888 request_id,
889 program_application_id,
890 program_id,
891 program_update_date,
892 flatfile_version,
893 direction,
894 primary_address_type,
895 parent_level,
896 installed_flag,
897 map_id,
898 enabled,
899 upgraded_flag
900 FROM ece_interface_tbls_upg
901 WHERE map_id = xMap_ID;
902 END ec_copy_interface_tables;
903
904 PROCEDURE ec_copy_level_matrices(
905 xMap_ID IN NUMBER) AS
906
907 BEGIN
908 FOR v_external_levels IN c_external_levels_upg(xMap_ID) LOOP
909 INSERT INTO ece_level_matrices(
910 matrix_level_id,
911 external_level_id,
912 interface_table_id)
913 SELECT matrix_level_id,
914 external_level_id,
915 interface_table_id
919 END ec_copy_level_matrices;
916 FROM ece_level_matrices_upg
917 WHERE external_level_id = v_external_levels.external_level_id;
918 END LOOP;
920
921 PROCEDURE ec_copy_mappings(
922 xMap_ID IN NUMBER) AS
923
924 BEGIN
925 INSERT INTO ece_mappings(
926 map_id,
927 description,
928 map_type,
929 transaction_type,
930 root_element,
931 enabled,
932 map_code)
933 SELECT map_id,
934 description,
935 map_type,
936 transaction_type,
937 root_element,
938 enabled,
939 map_code
940 FROM ece_mappings_upg
941 WHERE map_id = xMap_ID;
942 END ec_copy_mappings;
943
944 PROCEDURE ec_copy_process_rules(
945 xMap_ID IN NUMBER) AS
946
947 BEGIN
948 INSERT INTO ece_process_rules(
949 process_rule_id,
950 transaction_type,
951 rule_type,
952 action_code,
953 creation_date,
954 created_by,
955 last_update_date,
956 last_updated_by,
957 last_update_login,
958 request_id,
959 program_application_id,
960 program_id,
961 program_update_date,
962 map_id)
963 SELECT process_rule_id,
964 transaction_type,
965 rule_type,
966 action_code,
967 creation_date,
968 created_by,
969 last_update_date,
970 last_updated_by,
971 last_update_login,
972 request_id,
973 program_application_id,
974 program_id,
975 program_update_date,
976 map_id
977 FROM ece_process_rules_upg
978 WHERE map_id = xMap_ID;
979 END ec_copy_process_rules;
980
981 --Copy all data for a given Map Code.
982 PROCEDURE ec_copy_map_data_by_mapcode(
983 xMapCode IN ece_mappings.map_code%TYPE) AS
984
985 iMap_ID NUMBER;
986
987 BEGIN
988 iMap_ID := ec_get_map_id(xMapCode,'Y');
989
990 ec_copy_dynamic_actions(iMap_ID);
991 ec_copy_process_rules(iMap_ID);
992 ec_copy_column_rules(iMap_ID);
993 ec_copy_interface_columns(iMap_ID);
994 ec_copy_interface_tables(iMap_ID);
995 ec_copy_level_matrices(iMap_ID);
996 ec_copy_external_levels(iMap_ID);
997 ec_copy_mappings(iMap_ID);
998 END ec_copy_map_data_by_mapcode;
999
1000 --Copy all data for a given Map ID.
1001 PROCEDURE ec_copy_map_data_by_mapid(
1002 xMap_ID IN NUMBER) AS
1003
1004 BEGIN
1005 ec_copy_dynamic_actions(xMap_ID);
1006 ec_copy_process_rules(xMap_ID);
1007 ec_copy_column_rules(xMap_ID);
1008 ec_copy_interface_columns(xMap_ID);
1009 ec_copy_interface_tables(xMap_ID);
1010 ec_copy_level_matrices(xMap_ID);
1011 ec_copy_external_levels(xMap_ID);
1012 ec_copy_mappings(xMap_ID);
1013 END ec_copy_map_data_by_mapid;
1014
1015 --Copies all maps/data for a given transaction, optionally by Map Type.
1016 PROCEDURE ec_copy_map_data_by_trans(
1017 xTransactionType IN ece_interface_tables.transaction_type%TYPE,
1018 xMapType IN ece_mappings.map_type%TYPE) AS
1019
1020 BEGIN
1021 FOR v_maps_upg IN c_maps_upg(xTransactionType,xMapType) LOOP
1022 ec_copy_map_data_by_mapid(v_maps_upg.map_id);
1023 END LOOP;
1024 END ec_copy_map_data_by_trans;
1025
1026 --Deletes entries in ECE_COLUMN_RULES.
1027 PROCEDURE ec_delete_column_rules(
1028 xMap_ID IN NUMBER,
1029 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1030
1031 BEGIN
1032 IF xUpgradeFlag = 'Y' THEN
1033 DELETE FROM ece_column_rules_upg
1034 WHERE interface_column_id IN (SELECT eic.interface_column_id
1035 FROM ece_interface_cols_upg eic,
1036 ece_interface_tbls_upg eit
1037 WHERE eic.interface_table_id = eit.interface_table_id AND
1038 eit.map_id = xMap_ID);
1039 ELSE
1040 DELETE FROM ece_column_rules
1041 WHERE interface_column_id IN (SELECT eic.interface_column_id
1042 FROM ece_interface_columns eic,
1043 ece_interface_tables eit
1044 WHERE eic.interface_table_id = eit.interface_table_id AND
1045 eit.map_id = xMap_ID);
1046 END IF;
1047 END ec_delete_column_rules;
1048
1049 --Deletes from ECE_TRAN_STAGE_DATA and ECE_PROCEDURE_MAPPINGS
1053
1050 PROCEDURE ec_delete_dynamic_action(
1051 xMap_ID IN NUMBER,
1052 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1054 BEGIN
1055 IF xUpgradeFlag = 'Y' THEN
1056 DELETE FROM ece_proc_mappings_upg
1057 WHERE transtage_id IN (SELECT transtage_id
1058 FROM ece_tran_stage_data_upg
1059 WHERE map_id = xMap_ID);
1060 DELETE FROM ece_tran_stage_data_upg
1061 WHERE map_id = xMap_ID;
1062 ELSE
1063 DELETE FROM ece_procedure_mappings
1064 WHERE transtage_id IN (SELECT transtage_id
1065 FROM ece_tran_stage_data
1066 WHERE map_id = xMap_ID);
1067 DELETE FROM ece_tran_stage_data
1068 WHERE map_id = xMap_ID;
1069 END IF;
1070 END ec_delete_dynamic_action;
1071
1072 --Deletes entries in ECE_EXTERNAL_LEVELS.
1073 PROCEDURE ec_delete_external_levels(
1074 xMap_ID IN NUMBER,
1075 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1076
1077 BEGIN
1078 IF xUpgradeFlag = 'Y' THEN
1079 DELETE FROM ece_external_levels_upg
1080 WHERE map_id = xMap_ID;
1081 ELSE
1082 DELETE FROM ece_external_levels
1083 WHERE map_id = xMap_ID;
1084 END IF;
1085 END ec_delete_external_levels;
1086
1087 --Deletes from ECE_INTERFACE_COLUMNS.
1088 --WARNING: If you're going to delete from ECE_COLUMN_RULES, do that first or the
1089 -- records in that table will become orphaned!
1090 PROCEDURE ec_delete_interface_columns(
1091 xMap_ID IN NUMBER,
1092 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1093 BEGIN
1094 IF xUpgradeFlag = 'Y' THEN
1095 DELETE FROM ece_interface_cols_upg
1096 WHERE interface_table_id IN (SELECT interface_table_id
1097 FROM ece_interface_tbls_upg
1098 WHERE map_id = xMap_ID);
1099 ELSE
1100 DELETE FROM ece_interface_columns
1101 WHERE interface_table_id IN (SELECT interface_table_id
1102 FROM ece_interface_tables
1103 WHERE map_id = xMap_ID);
1104 END IF;
1105 END ec_delete_interface_columns;
1106
1107 --Deletes entries in ECE_INTERFACE_TABLES.
1108 PROCEDURE ec_delete_interface_tables(
1109 xMap_ID IN NUMBER,
1110 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1111
1112 BEGIN
1113 IF xUpgradeFlag = 'Y' THEN
1114 DELETE FROM ece_interface_tbls_upg
1115 WHERE map_id = xMap_ID;
1116 ELSE
1117 DELETE FROM ece_interface_tables
1118 WHERE map_id = xMap_ID;
1119 END IF;
1120 END ec_delete_interface_tables;
1121
1122 --Deletes entries in ECE_LEVEL_MATRICES.
1123 PROCEDURE ec_delete_level_matrices(
1124 xMap_ID IN NUMBER,
1125 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1126
1127 BEGIN
1128 IF xUpgradeFlag = 'Y' THEN
1129 DELETE FROM ece_level_matrices_upg
1130 WHERE external_level_id IN (SELECT external_level_id
1131 FROM ece_external_levels_upg
1132 WHERE map_id = xMap_ID);
1133 ELSE
1134 DELETE FROM ece_level_matrices
1135 WHERE external_level_id IN (SELECT external_level_id
1136 FROM ece_external_levels
1137 WHERE map_id = xMap_ID);
1138 END IF;
1139 END ec_delete_level_matrices;
1140
1141 --Deletes entries in ECE_MAPPINGS.
1142 PROCEDURE ec_delete_mappings(
1143 xMap_ID IN NUMBER,
1144 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1145
1146 BEGIN
1147 IF xUpgradeFlag = 'Y' THEN
1148 DELETE FROM ece_mappings_upg
1149 WHERE map_id = xMap_ID;
1150 ELSE
1151 DELETE FROM ece_mappings
1152 WHERE map_id = xMap_ID;
1153 END IF;
1154 END ec_delete_mappings;
1155
1156 --Deletes entries in ECE_PROCESS_RULES.
1157 PROCEDURE ec_delete_process_rules(
1158 xMap_ID IN NUMBER,
1159 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1160
1161 BEGIN
1162 IF xUpgradeFlag = 'Y' THEN
1163 DELETE FROM ece_process_rules_upg
1164 WHERE map_id = xMap_ID;
1165 ELSE
1166 DELETE FROM ece_process_rules
1167 WHERE map_id = xMap_ID;
1168 END IF;
1169 END ec_delete_process_rules;
1170
1171 --Delete all data for a given Map Code.
1172 PROCEDURE ec_delete_map_data_by_mapcode(
1173 xMapCode IN ece_mappings.map_code%TYPE,
1174 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1175
1176 iMap_ID NUMBER;
1177
1178 BEGIN
1182 ec_delete_process_rules(iMap_ID,xUpgradeFlag);
1179 iMap_ID := ec_get_map_id(xMapCode,xUpgradeFlag);
1180
1181 ec_delete_dynamic_action(iMap_ID,xUpgradeFlag);
1183 ec_delete_column_rules(iMap_ID,xUpgradeFlag);
1184 ec_delete_interface_columns(iMap_ID,xUpgradeFlag);
1185 ec_delete_interface_tables(iMap_ID,xUpgradeFlag);
1186 ec_delete_level_matrices(iMap_ID,xUpgradeFlag);
1187 ec_delete_external_levels(iMap_ID,xUpgradeFlag);
1188 ec_delete_mappings(iMap_ID,xUpgradeFlag);
1189 END ec_delete_map_data_by_mapcode;
1190
1191 --Deletes all data for a given Map ID.
1192 PROCEDURE ec_delete_map_data_by_mapid(
1193 xMap_ID IN NUMBER,
1194 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1195
1196 BEGIN
1197 ec_delete_dynamic_action(xMap_ID,xUpgradeFlag);
1198 ec_delete_process_rules(xMap_ID,xUpgradeFlag);
1199 ec_delete_column_rules(xMap_ID,xUpgradeFlag);
1200 ec_delete_interface_columns(xMap_ID,xUpgradeFlag);
1201 ec_delete_interface_tables(xMap_ID,xUpgradeFlag);
1202 ec_delete_level_matrices(xMap_ID,xUpgradeFlag);
1203 ec_delete_external_levels(xMap_ID,xUpgradeFlag);
1204 ec_delete_mappings(xMap_ID,xUpgradeFlag);
1205 END ec_delete_map_data_by_mapid;
1206
1207 --Deletes all maps/data for a given transaction, optionally by Map Type.
1208 PROCEDURE ec_delete_map_data_by_trans(
1209 xTransactionType IN ece_interface_tables.transaction_type%TYPE,
1210 xMapType IN ece_mappings.map_type%TYPE,
1211 xUpgradeFlag IN VARCHAR2 DEFAULT 'N') AS
1212
1213 BEGIN
1214 IF xUpgradeFlag = 'Y' THEN
1215 FOR v_maps_upg IN c_maps_upg(xTransactionType,xMapType) LOOP
1216 ec_delete_map_data_by_mapid(v_maps_upg.map_id,xUpgradeFlag);
1217 END LOOP;
1218 ELSE
1219 FOR v_maps IN c_maps(xTransactionType,xMapType) LOOP
1220 ec_delete_map_data_by_mapid(v_maps.map_id,xUpgradeFlag);
1221 END LOOP;
1222 END IF;
1223 END ec_delete_map_data_by_trans;
1224
1225 PROCEDURE ec_migrate_map_to_production(
1226 xMapCode IN ece_mappings.map_code%TYPE,
1227 xTransExists IN BOOLEAN) AS
1228
1229 BEGIN
1230 --DELETE from PRODUCTION
1231 IF xTransExists = TRUE THEN
1232 ec_delete_map_data_by_mapcode(xMapCode,'N');
1233 END IF;
1234
1235 --COPY from UPGRADE
1236 ec_copy_map_data_by_mapcode(xMapCode);
1237
1238 --DELETE from UPGRADE
1239 ec_delete_map_data_by_mapcode(xMapCode,'Y');
1240
1241 END ec_migrate_map_to_production;
1242
1243 PROCEDURE ec_reconcile_seed(
1244 errbuf OUT NOCOPY VARCHAR2,
1245 retcode OUT NOCOPY VARCHAR2,
1246 transaction_type IN VARCHAR2,
1247 preserve_layout IN VARCHAR2,
1248 preserve_proc_rules IN VARCHAR2,
1249 preserve_col_rules IN VARCHAR2,
1250 preserve_cc_cat IN VARCHAR2,
1251 v_debug_mode IN NUMBER DEFAULT 3) IS
1252
1253 n_map_count NUMBER;
1254 n_staged_doc_count NUMBER;
1255 iMap_ID_Main NUMBER;
1256
1257 b_trans_exists BOOLEAN := FALSE;
1258
1259 stage_doc_exception EXCEPTION;
1260
1261 BEGIN
1262 ec_debug.enable_debug(v_debug_mode);
1263 ec_debug.push('ec_mapping_utils.ec_reconcile_seed');
1264 ec_debug.pl(0,'transaction_type: ',transaction_type);
1265 ec_debug.pl(0,'preserve_layout: ',preserve_layout);
1266 ec_debug.pl(0,'preserve_proc_rules: ',preserve_proc_rules);
1267 ec_debug.pl(0,'preserve_col_rules: ',preserve_col_rules);
1268 ec_debug.pl(0,'preserve_cc_cat: ',preserve_cc_cat);
1269 ec_debug.pl(0,'v_debug_mode: ',v_debug_mode);
1270
1271 FOR v_maps_upg IN c_maps_upg(transaction_type,NULL) LOOP --Loop through each map for this transaction.
1272 ec_debug.pl(0,'Processing Map: ' || v_maps_upg.map_code);
1273
1274 --Let's check to see if map exists in main tables...
1275 SELECT COUNT(*) INTO n_map_count
1276 FROM ece_mappings
1277 WHERE map_code = v_maps_upg.map_code;
1278
1279 IF n_map_count > 0 THEN
1280 b_trans_exists := TRUE;
1281 ELSE
1282 b_trans_exists := FALSE;
1283 END IF;
1284
1285 IF b_trans_exists = TRUE THEN --We only need to check the staging table if the map exists...
1286 --Let's check to see if there is any data in staging table w/ map_id in question.
1287 iMap_ID_Main := ec_get_main_map_id(v_maps_upg.map_id);
1288
1289 SELECT COUNT(*) INTO n_staged_doc_count
1290 FROM ece_stage
1291 WHERE map_id = iMap_ID_Main;
1292
1293 IF n_staged_doc_count > 0 THEN --There are documents in staging table still. Abort.
1294 RAISE stage_doc_exception;
1295 END IF;
1296 END IF;
1297
1298 IF preserve_layout = 'N' OR b_trans_exists = FALSE THEN --Do a straight copy if map doesn't already exist
1302 ELSE --Do preserve layout
1299 IF (preserve_col_rules = 'Y' OR preserve_cc_cat = 'Y') AND b_trans_exists = TRUE THEN
1300 ec_debug.pl(0,'*****Column Rules and Code Conversion Categories cannot be reconciled if Column Layout is not reconciled.');
1301 END IF;
1303 ec_upgrade_layout(
1304 v_maps_upg.map_id,
1305 preserve_col_rules,
1306 preserve_cc_cat);
1307
1308 IF preserve_col_rules = 'Y' THEN
1309 ec_delete_column_rules(v_maps_upg.map_id,'Y');
1310 END IF;
1311 END IF;
1312
1313 --Preserve Process Rule Actions?
1314 IF preserve_proc_rules = 'Y' AND b_trans_exists = TRUE THEN
1315 ec_upgrade_process_rules(v_maps_upg.map_id);
1316 END IF;
1317
1318 IF b_trans_exists = TRUE THEN
1319 ec_remap_tp_details(v_maps_upg.map_id);
1320 END IF;
1321
1322 ec_migrate_map_to_production(v_maps_upg.map_code,b_trans_exists);
1323
1324 END LOOP;
1325
1326 ec_debug.pop('ec_mapping_utils.ec_reconcile_seed');
1327 ec_debug.disable_debug;
1328 retcode := '0';
1329 COMMIT;
1330
1331 EXCEPTION
1332 WHEN stage_doc_exception THEN
1333 ec_debug.pl(0,'*****There are still documents in staging table for this map. Aborting Reconciliation.');
1334 retcode := 1;
1335 ec_debug.disable_debug;
1336 ROLLBACK;
1337
1338 WHEN OTHERS THEN
1339 --ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
1340 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1341
1342 retcode := 2;
1343 ec_debug.disable_debug;
1344 ROLLBACK;
1345
1346 END ec_reconcile_seed;
1347
1348 PROCEDURE ec_remap_tp_details(
1349 xMap_ID IN NUMBER) AS --Upgrade Table Map_ID
1350
1351 iMap_ID_Main NUMBER;
1352
1353 BEGIN
1354 iMap_ID_Main := ec_get_main_map_id(xMap_ID);
1355
1356 UPDATE ece_tp_details
1357 SET map_id = xMap_ID
1358 WHERE map_id = iMap_ID_Main;
1359 END ec_remap_tp_details;
1360
1361 END;
1362