1 PACKAGE BODY FEM_REFRESH_UTIL_PKG AS
2 -- $Header: fem_refresh_utl.plb 120.2 2006/07/10 23:35:02 rflippo noship $
3 /*=======================================================================+
4 Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | fem_refresh_utl.sql
9 |
10 | DESCRIPTION
11 | The Refresh Utility package contains procedures called by the Refresh
12 | engine when it is returning a database to its install state.
13 |
14 | Procedure List:
15 | del_obsolete_seed_data - this procedure calls all of the delete statements
16 | for removing obsolete seeded data from the database. This seed data
17 | would otherwise get put back in by the refresh when it calls the ldt
18 | files to repopulate. The reason for this being that the ldt files
19 | for backports are targeted - i.e, the main ldt file still contains the
20 | obsolete rows.
21 |
22 | MODIFICATION HISTORY
23 | Rob Flippo 07/06/2005 Created
24 | Rob Flippo 04/24/2006 Bug5002331 Remove refrence to
25 | DIM_PROPERTIES in delete statements
26 | for FEM_ADMIN_DIMESTUP_TASKS
27 | Rob Flippo 07/10/2006 Bug#5237422 Add deletes for attributes
28 | previously made optional
29 |
30 *=======================================================================*/
31
32
33 PROCEDURE del_obsolete_seed_data (x_status OUT NOCOPY VARCHAR2) IS
34
35 v_count number;
36
37 ------------------------------------------------------------------
38 -- For bug#5237422
39 v_ledger_dim_id number;
40 v_natrl_dim_id number;
41 v_cctr_dim_id number;
42 v_finelem_dim_id number;
43 v_lnitem_dim_id number;
44
45 cursor c_ldgattr (p_dimid IN NUMBER) is
46 select attribute_id from fem_dim_attributes_b
47 where dimension_id = p_dimid
48 and attribute_varchar_label in ('LEDGER_CHANNEL_IS_POP_FLAG'
49 ,'LEDGER_CUSTOMER_IS_POP_FLAG'
50 ,'LEDGER_ENTITY_IS_POP_FLAG'
51 ,'LEDGER_FIN_ELEM_IS_POP_FLAG'
52 ,'LEDGER_GEOGRAPHY_IS_POP_FLAG'
53 ,'LEDGER_LINE_ITEM_IS_POP_FLAG'
54 ,'LEDGER_NAT_ACCT_IS_POP_FLAG'
55 ,'LEDGER_PRODUCT_IS_POP_FLAG'
56 ,'LEDGER_PROJECT_IS_POP_FLAG'
57 ,'LEDGER_TASK_IS_POP_FLAG'
58 ,'LEDGER_USER_DIM10_IS_POP_FLAG'
59 ,'LEDGER_USER_DIM1_IS_POP_FLAG'
60 ,'LEDGER_USER_DIM2_IS_POP_FLAG'
61 ,'LEDGER_USER_DIM3_IS_POP_FLAG'
62 ,'LEDGER_USER_DIM4_IS_POP_FLAG'
63 ,'LEDGER_USER_DIM5_IS_POP_FLAG'
64 ,'LEDGER_USER_DIM6_IS_POP_FLAG'
65 ,'LEDGER_USER_DIM7_IS_POP_FLAG'
66 ,'LEDGER_USER_DIM8_IS_POP_FLAG'
67 ,'LEDGER_USER_DIM9_IS_POP_FLAG'
68 ,'LEDGER_CCTR_IS_POP_FLAG' );
69
70 cursor c_natattr (p_dimid IN NUMBER) is
71 select attribute_id from fem_dim_attributes_b
72 where dimension_id = p_dimid
73 and attribute_varchar_label in ('FINANCIAL_CATEGORY_FLAG');
74
75 cursor c_cctrattr (p_dimid IN NUMBER) is
76 select attribute_id from fem_dim_attributes_b
77 where dimension_id = p_dimid
78 and attribute_varchar_label in ('HIDDEN_FLAG');
79
80 cursor c_feattr (p_dimid IN NUMBER) is
81 select attribute_id from fem_dim_attributes_b
82 where dimension_id = p_dimid
83 and attribute_varchar_label in ('CONSOLIDATION_FLAG');
84
85 cursor c_lnattr (p_dimid IN NUMBER) is
86 select attribute_id from fem_dim_attributes_b
87 where dimension_id = p_dimid
88 and attribute_varchar_label in ('HIDDEN_FLAG','BUDGET_ALLOWED_FLAG');
89 ------------------------------------------------------------------
90
91
92 BEGIN
93
94
95 -- fem4040716_upd.sql
96 delete from fem_sic_hier
97 where hierarchy_obj_def_id = 1501
98 and child_id = 1
99 and parent_id = 75;
100
101 delete from fem_sic_hier
102 where hierarchy_obj_def_id = 1501
103 and child_id = 5
104 and parent_id = 76;
105
106 --fem4196148_drp.sql
107 BEGIN
108
109 select count(*)
110 into v_count
111 from fem_tab_columns_b
112 where table_name='FEM_CREDIT_LIMITS'
113 and column_name = 'CREDIT_LIMIT_TYPE';
114
115 IF v_count > 0 THEN
116
117 delete from fem_tab_columns_b
118 where table_name='FEM_CREDIT_LIMITS'
119 and column_name in ('CREDIT_LIMIT_ID','CREDIT_LIMIT_TYPE');
120
121 delete from fem_tab_columns_tl
122 where table_name='FEM_CREDIT_LIMITS'
123 and column_name in ('CREDIT_LIMIT_ID','CREDIT_LIMIT_TYPE');
124
125 END IF;
126 END;
127
128 -- fem4299195_del.sql
129 delete from fem_folders_tl
130 where folder_id in (1200, 1300);
131
132 delete from fem_folders_b
133 where folder_id in (1200, 1300);
134
135
136 --fem4453456.sql
137 delete from FEM_TAB_COLUMNS_B
138 where table_name = 'FEM_CREDIT_LIMITS'
139 and column_name = 'CREDIT_LIMIT_AMOUNT';
140
141 delete from FEM_INT_COLUMN_MAP
142 where object_type_code = 'SOURCE_DATA_LOADER'
143 and target_column_name = 'CREDIT_LIMIT_AMOUNT'
144 and interface_column_name = 'CREDIT_LIMIT_AMOUNT';
145
146 --fem_attr_del.sql
147 /**********************************************************************
148 * FEM.C delete statements
149 * Per Bug#3884353 Integration: Attribute Removal
150 * and bug#4044974 DATAMODEL CHANGE TO SUPPORT DEFAULT HIERARCHY
151 * FOR CAL PERIOD DIMENSION
152
153 **********************************************************************
154
155 /****************************
156 Calendar
157 ***************************/
158 delete from fem_calendars_attr
159 where version_id in
160 (select version_id from fem_dim_attr_Versions_b
161 where attribute_id in (select attribute_id
162 from fem_dim_attributes_b A1, fem_dimensions_b D1
163 where A1.dimension_id = D1.dimension_id
164 and D1.dimension_varchar_label = 'CALENDAR'
165 and A1.attribute_varchar_label in ('DEFAULT_CAL_PERIOD'
166 )));
167
168 delete from fem_dim_attr_versions_tl where version_id in
169 (select version_id from fem_dim_attr_Versions_b
170 where attribute_id in (select attribute_id
171 from fem_dim_attributes_b A1, fem_dimensions_b D1
172 where A1.dimension_id = D1.dimension_id
173 and D1.dimension_varchar_label = 'CALENDAR'
174 and A1.attribute_varchar_label in ('DEFAULT_CAL_PERIOD'
175 )));
176
177 delete from fem_dim_attr_versions_b
178 where attribute_id in (select attribute_id
179 from fem_dim_attributes_b A1, fem_dimensions_b D1
180 where A1.dimension_id = D1.dimension_id
181 and D1.dimension_varchar_label = 'CALENDAR'
182 and A1.attribute_varchar_label in ('DEFAULT_CAL_PERIOD'
183 ));
184
185 delete from fem_dim_attributes_tl
186 where attribute_id in (select attribute_id
187 from fem_dim_attributes_b A1, fem_dimensions_b D1
188 where A1.dimension_id = D1.dimension_id
189 and D1.dimension_varchar_label = 'CALENDAR'
190 and A1.attribute_varchar_label in ('DEFAULT_CAL_PERIOD'
191 ));
192
193 delete from fem_dim_attributes_priv
194 where attribute_id in (select attribute_id
195 from fem_dim_attributes_b A1, fem_dimensions_b D1
196 where A1.dimension_id = D1.dimension_id
197 and D1.dimension_varchar_label = 'CALENDAR'
198 and A1.attribute_varchar_label in ('DEFAULT_CAL_PERIOD'
199 ));
200
201 delete from fem_dim_attributes_b
202 where attribute_id in (select attribute_id
203 from fem_dim_attributes_b A1, fem_dimensions_b D1
204 where A1.dimension_id = D1.dimension_id
205 and D1.dimension_varchar_label = 'CALENDAR'
206 and A1.attribute_varchar_label in ('DEFAULT_CAL_PERIOD'
207 ,'DIVISIBLE_FLAG'));
208
209
210 /****************************
211 Financial Element
212 ***************************/
213 delete from fem_fin_elems_attr
214 where version_id in
215 (select version_id from fem_dim_attr_Versions_b
216 where attribute_id in (select attribute_id
217 from fem_dim_attributes_b A1, fem_dimensions_b D1
218 where A1.dimension_id = D1.dimension_id
219 and D1.dimension_varchar_label = 'FINANCIAL_ELEMENT'
220 and A1.attribute_varchar_label in ('FE_DATA_GROUPING_CODE'
221 ,'DIVISIBLE_FLAG')));
222
223 delete from fem_dim_attr_versions_tl where version_id in
224 (select version_id from fem_dim_attr_Versions_b
225 where attribute_id in (select attribute_id
226 from fem_dim_attributes_b A1, fem_dimensions_b D1
227 where A1.dimension_id = D1.dimension_id
228 and D1.dimension_varchar_label = 'FINANCIAL_ELEMENT'
229 and A1.attribute_varchar_label in ('FE_DATA_GROUPING_CODE'
230 ,'DIVISIBLE_FLAG')));
231
232 delete from fem_dim_attr_versions_b
233 where attribute_id in (select attribute_id
234 from fem_dim_attributes_b A1, fem_dimensions_b D1
235 where A1.dimension_id = D1.dimension_id
236 and D1.dimension_varchar_label = 'FINANCIAL_ELEMENT'
237 and A1.attribute_varchar_label in ('FE_DATA_GROUPING_CODE'
238 ,'DIVISIBLE_FLAG'));
239
240 delete from fem_dim_attributes_tl
241 where attribute_id in (select attribute_id
242 from fem_dim_attributes_b A1, fem_dimensions_b D1
243 where A1.dimension_id = D1.dimension_id
244 and D1.dimension_varchar_label = 'FINANCIAL_ELEMENT'
245 and A1.attribute_varchar_label in ('FE_DATA_GROUPING_CODE'
246 ,'DIVISIBLE_FLAG'));
247
248 delete from fem_dim_attributes_priv
249 where attribute_id in (select attribute_id
250 from fem_dim_attributes_b A1, fem_dimensions_b D1
251 where A1.dimension_id = D1.dimension_id
252 and D1.dimension_varchar_label = 'FINANCIAL_ELEMENT'
253 and A1.attribute_varchar_label in ('FE_DATA_GROUPING_CODE'
254 ,'DIVISIBLE_FLAG'));
255
256 delete from fem_dim_attr_grps
257 where attribute_id in (select attribute_id
258 from fem_dim_attributes_b A1, fem_dimensions_b D1
259 where A1.dimension_id = D1.dimension_id
260 and D1.dimension_varchar_label = 'FINANCIAL_ELEMENT'
261 and A1.attribute_varchar_label in ('FE_DATA_GROUPING_CODE'
262 ,'DIVISIBLE_FLAG'));
263
264 delete from fem_dim_attributes_b
265 where attribute_id in (select attribute_id
266 from fem_dim_attributes_b A1, fem_dimensions_b D1
267 where A1.dimension_id = D1.dimension_id
268 and D1.dimension_varchar_label = 'FINANCIAL_ELEMENT'
269 and A1.attribute_varchar_label in ('FE_DATA_GROUPING_CODE'
270 ,'DIVISIBLE_FLAG'));
271
272
273 /****************************
274 Line Item
275 ***************************/
276 delete from fem_ln_items_attr
277 where version_id in
278 (select version_id from fem_dim_attr_Versions_b
279 where attribute_id in (select attribute_id
280 from fem_dim_attributes_b A1, fem_dimensions_b D1
281 where A1.dimension_id = D1.dimension_id
282 and D1.dimension_varchar_label = 'LINE_ITEM'
283 and A1.attribute_varchar_label in ('BAL_SHEET_MODEL_CODE'
284 ,'TP_METHOD_CODE'
285 ,'NEW_TIMING_PCT'
286 ,'WEIGHTING_AVG_PERIOD'
287 ,'ACTIVATION_FLAG'
288 ,'OFFSET_CCTR_ORG'
289 )));
290
291
292 delete from fem_dim_attr_versions_tl where version_id in
293 (select version_id from fem_dim_attr_Versions_b
294 where attribute_id in (select attribute_id
295 from fem_dim_attributes_b A1, fem_dimensions_b D1
296 where A1.dimension_id = D1.dimension_id
297 and D1.dimension_varchar_label = 'LINE_ITEM'
298 and A1.attribute_varchar_label in ('BAL_SHEET_MODEL_CODE'
299 ,'TP_METHOD_CODE'
300 ,'NEW_TIMING_PCT'
301 ,'WEIGHTING_AVG_PERIOD'
302 ,'ACTIVATION_FLAG'
303 ,'OFFSET_CCTR_ORG'
304 )));
305
306 delete from fem_dim_attr_versions_b
307 where attribute_id in (select attribute_id
308 from fem_dim_attributes_b A1, fem_dimensions_b D1
309 where A1.dimension_id = D1.dimension_id
310 and D1.dimension_varchar_label = 'LINE_ITEM'
311 and A1.attribute_varchar_label in ('BAL_SHEET_MODEL_CODE'
312 ,'TP_METHOD_CODE'
313 ,'NEW_TIMING_PCT'
314 ,'WEIGHTING_AVG_PERIOD'
315 ,'ACTIVATION_FLAG'
316 ,'OFFSET_CCTR_ORG'
317 ));
318
319 delete from fem_dim_attributes_tl
320 where attribute_id in (select attribute_id
321 from fem_dim_attributes_b A1, fem_dimensions_b D1
322 where A1.dimension_id = D1.dimension_id
323 and D1.dimension_varchar_label = 'LINE_ITEM'
324 and A1.attribute_varchar_label in ('BAL_SHEET_MODEL_CODE'
325 ,'TP_METHOD_CODE'
326 ,'NEW_TIMING_PCT'
327 ,'WEIGHTING_AVG_PERIOD'
328 ,'ACTIVATION_FLAG'
329 ,'OFFSET_CCTR_ORG'
330 ));
331
332 delete from fem_dim_attributes_priv
333 where attribute_id in (select attribute_id
334 from fem_dim_attributes_b A1, fem_dimensions_b D1
335 where A1.dimension_id = D1.dimension_id
336 and D1.dimension_varchar_label = 'LINE_ITEM'
337 and A1.attribute_varchar_label in ('BAL_SHEET_MODEL_CODE'
338 ,'TP_METHOD_CODE'
339 ,'NEW_TIMING_PCT'
340 ,'WEIGHTING_AVG_PERIOD'
341 ,'ACTIVATION_FLAG'
342 ,'OFFSET_CCTR_ORG'
343 ));
344
345 delete from fem_dim_attr_grps
346 where attribute_id in (select attribute_id
347 from fem_dim_attributes_b A1, fem_dimensions_b D1
348 where A1.dimension_id = D1.dimension_id
349 and D1.dimension_varchar_label = 'LINE_ITEM'
350 and A1.attribute_varchar_label in ('BAL_SHEET_MODEL_CODE'
351 ,'TP_METHOD_CODE'
352 ,'NEW_TIMING_PCT'
353 ,'WEIGHTING_AVG_PERIOD'
354 ,'ACTIVATION_FLAG'
355 ,'OFFSET_CCTR_ORG'
356 ));
357
358 delete from fem_dim_attributes_b
359 where attribute_id in (select attribute_id
360 from fem_dim_attributes_b A1, fem_dimensions_b D1
364 ,'TP_METHOD_CODE'
361 where A1.dimension_id = D1.dimension_id
362 and D1.dimension_varchar_label = 'LINE_ITEM'
363 and A1.attribute_varchar_label in ('BAL_SHEET_MODEL_CODE'
365 ,'NEW_TIMING_PCT'
366 ,'WEIGHTING_AVG_PERIOD'
367 ,'ACTIVATION_FLAG'
368 ,'OFFSET_CCTR_ORG'
369 ));
370
371
372 /****************************
373 Product
374 ***************************/
375 delete from fem_products_attr where version_id in
376 (select version_id from fem_dim_attr_Versions_b
377 where attribute_id in (select attribute_id
378 from fem_dim_attributes_b A1, fem_dimensions_b D1
379 where A1.dimension_id = D1.dimension_id
380 and D1.dimension_varchar_label = 'PRODUCT'
381 and A1.attribute_varchar_label in ('HIDDEN_FLAG')));
382
383
384 delete from fem_dim_attr_versions_tl where version_id in
385 (select version_id from fem_dim_attr_Versions_b
386 where attribute_id in (select attribute_id
387 from fem_dim_attributes_b A1, fem_dimensions_b D1
391
388 where A1.dimension_id = D1.dimension_id
389 and D1.dimension_varchar_label = 'PRODUCT'
390 and A1.attribute_varchar_label in ('HIDDEN_FLAG')));
392 delete from fem_dim_attr_versions_b
393 where attribute_id in (select attribute_id
394 from fem_dim_attributes_b A1, fem_dimensions_b D1
395 where A1.dimension_id = D1.dimension_id
396 and D1.dimension_varchar_label = 'PRODUCT'
397 and A1.attribute_varchar_label in ('HIDDEN_FLAG'));
398
399 delete from fem_dim_attributes_tl
400 where attribute_id in (select attribute_id
401 from fem_dim_attributes_b A1, fem_dimensions_b D1
402 where A1.dimension_id = D1.dimension_id
403 and D1.dimension_varchar_label = 'PRODUCT'
404 and A1.attribute_varchar_label in ('HIDDEN_FLAG'));
405
406 delete from fem_dim_attributes_priv
407 where attribute_id in (select attribute_id
408 from fem_dim_attributes_b A1, fem_dimensions_b D1
409 where A1.dimension_id = D1.dimension_id
410 and D1.dimension_varchar_label = 'PRODUCT'
411 and A1.attribute_varchar_label in ('HIDDEN_FLAG'));
412
413 delete from fem_dim_attr_grps
414 where attribute_id in (select attribute_id
415 from fem_dim_attributes_b A1, fem_dimensions_b D1
416 where A1.dimension_id = D1.dimension_id
417 and D1.dimension_varchar_label = 'PRODUCT'
418 and A1.attribute_varchar_label in ('HIDDEN_FLAG'));
419
420 delete from fem_dim_attributes_b
421 where attribute_id in (select attribute_id
422 from fem_dim_attributes_b A1, fem_dimensions_b D1
423 where A1.dimension_id = D1.dimension_id
424 and D1.dimension_varchar_label = 'PRODUCT'
425 and A1.attribute_varchar_label in ('HIDDEN_FLAG'));
426
427
428 /****************************
429 Natural Account
430 ***************************/
431 delete from fem_nat_accts_attr where version_id in
432 (select version_id from fem_dim_attr_Versions_b
433 where attribute_id in (select attribute_id
434 from fem_dim_attributes_b A1, fem_dimensions_b D1
435 where A1.dimension_id = D1.dimension_id
436 and D1.dimension_varchar_label = 'NATURAL_ACCOUNT'
437 and A1.attribute_varchar_label in ('HIDDEN_FLAG','ACTIVATION_FLAG')));
438
439
440 delete from fem_dim_attr_versions_tl where version_id in
441 (select version_id from fem_dim_attr_Versions_b
442 where attribute_id in (select attribute_id
443 from fem_dim_attributes_b A1, fem_dimensions_b D1
444 where A1.dimension_id = D1.dimension_id
445 and D1.dimension_varchar_label = 'NATURAL_ACCOUNT'
446 and A1.attribute_varchar_label in ('HIDDEN_FLAG','ACTIVATION_FLAG')));
447
448 delete from fem_dim_attr_versions_b
449 where attribute_id in (select attribute_id
450 from fem_dim_attributes_b A1, fem_dimensions_b D1
451 where A1.dimension_id = D1.dimension_id
452 and D1.dimension_varchar_label = 'NATURAL_ACCOUNT'
453 and A1.attribute_varchar_label in ('HIDDEN_FLAG','ACTIVATION_FLAG'));
454
455 delete from fem_dim_attributes_tl
456 where attribute_id in (select attribute_id
457 from fem_dim_attributes_b A1, fem_dimensions_b D1
458 where A1.dimension_id = D1.dimension_id
459 and D1.dimension_varchar_label = 'NATURAL_ACCOUNT'
460 and A1.attribute_varchar_label in ('HIDDEN_FLAG','ACTIVATION_FLAG'));
461
462 delete from fem_dim_attributes_priv
463 where attribute_id in (select attribute_id
464 from fem_dim_attributes_b A1, fem_dimensions_b D1
465 where A1.dimension_id = D1.dimension_id
466 and D1.dimension_varchar_label = 'NATURAL_ACCOUNT'
467 and A1.attribute_varchar_label in ('HIDDEN_FLAG','ACTIVATION_FLAG'));
468
469 delete from fem_dim_attr_grps
470 where attribute_id in (select attribute_id
471 from fem_dim_attributes_b A1, fem_dimensions_b D1
472 where A1.dimension_id = D1.dimension_id
473 and D1.dimension_varchar_label = 'NATURAL_ACCOUNT'
474 and A1.attribute_varchar_label in ('HIDDEN_FLAG','ACTIVATION_FLAG'));
475
476 delete from fem_dim_attributes_b
477 where attribute_id in (select attribute_id
478 from fem_dim_attributes_b A1, fem_dimensions_b D1
479 where A1.dimension_id = D1.dimension_id
480 and D1.dimension_varchar_label = 'NATURAL_ACCOUNT'
481 and A1.attribute_varchar_label in ('HIDDEN_FLAG','ACTIVATION_FLAG'));
482
483 /****************************
484 Ledger
485 ***************************/
486 delete from fem_ledgers_attr where version_id in
487 (select version_id from fem_dim_attr_Versions_b
488 where attribute_id in (select attribute_id
489 from fem_dim_attributes_b A1, fem_dimensions_b D1
490 where A1.dimension_id = D1.dimension_id
491 and D1.dimension_varchar_label = 'LEDGER'
492 and A1.attribute_varchar_label in ('EPB_DEFAULT_LEDGER_FLAG')));
493
494 delete from fem_dim_attr_versions_tl where version_id in
495 (select version_id from fem_dim_attr_Versions_b
496 where attribute_id in (select attribute_id
497 from fem_dim_attributes_b A1, fem_dimensions_b D1
498 where A1.dimension_id = D1.dimension_id
499 and D1.dimension_varchar_label = 'LEDGER'
500 and A1.attribute_varchar_label in ('EPB_DEFAULT_LEDGER_FLAG')));
501
502 delete from fem_dim_attr_versions_b
503 where attribute_id in (select attribute_id
504 from fem_dim_attributes_b A1, fem_dimensions_b D1
505 where A1.dimension_id = D1.dimension_id
506 and D1.dimension_varchar_label = 'LEDGER'
507 and A1.attribute_varchar_label in ('EPB_DEFAULT_LEDGER_FLAG'));
508
509 delete from fem_dim_attributes_tl
510 where attribute_id in (select attribute_id
511 from fem_dim_attributes_b A1, fem_dimensions_b D1
512 where A1.dimension_id = D1.dimension_id
513 and D1.dimension_varchar_label = 'LEDGER'
514 and A1.attribute_varchar_label in ('EPB_DEFAULT_LEDGER_FLAG'));
515
516 delete from fem_dim_attributes_priv
517 where attribute_id in (select attribute_id
518 from fem_dim_attributes_b A1, fem_dimensions_b D1
519 where A1.dimension_id = D1.dimension_id
520 and D1.dimension_varchar_label = 'LEDGER'
524 where attribute_id in (select attribute_id
521 and A1.attribute_varchar_label in ('EPB_DEFAULT_LEDGER_FLAG'));
522
523 delete from fem_dim_attr_grps
525 from fem_dim_attributes_b A1, fem_dimensions_b D1
526 where A1.dimension_id = D1.dimension_id
527 and D1.dimension_varchar_label = 'LEDGER'
528 and A1.attribute_varchar_label in ('EPB_DEFAULT_LEDGER_FLAG'));
529
530 delete from fem_dim_attributes_b
531 where attribute_id in (select attribute_id
532 from fem_dim_attributes_b A1, fem_dimensions_b D1
533 where A1.dimension_id = D1.dimension_id
534 and D1.dimension_varchar_label = 'LEDGER'
535 and A1.attribute_varchar_label in ('EPB_DEFAULT_LEDGER_FLAG'));
536
537
538 --fem_dmstptsk_del.sql
539 delete from fem_admin_dimsetup_tasks
540 where dimsetup_task in
541 ('DIM_ASSOCIATIONS');
542
543
544 --fem_enttype_del.sql
545 delete from fem_entity_types_tl
546 where entity_type_code in ('CONSOLIDATION','ELIMINATION','OPERATING');
547 delete from fem_entity_types_b
548 where entity_type_code in ('CONSOLIDATION','ELIMINATION','OPERATING');
549
550
551
552 --fem_obs_seeddata.sql
553 delete from fem_ext_Acct_types_attr where attribute_id in
554 (select attribute_id from fem_dim_attributes_b
558 where attribute_id in (select attribute_id from fem_dim_attributes_b
555 where attribute_varchar_label in ('DEBIT_SIGN','LOAD_SIGN'));
556
557 delete from fem_dim_attr_Versions_vl
559 where attribute_Varchar_label in ('DEBIT_SIGN','LOAD_SIGN'));
560
561 delete from fem_dim_attributes_vl where attribute_varchar_label in ('DEBIT_SIGN','LOAD_SIGN');
562
563 delete from fem_ln_items_attr where attribute_id in
564 (select attribute_id from fem_dim_attributes_b
565 where attribute_Varchar_label = 'STATISTICS_TYPE_FLAG');
566
567 delete from fem_dim_attr_Versions_vl
568 where attribute_id in
569 (select attribute_id from fem_dim_attributes_b
570 where attribute_Varchar_label = 'STATISTICS_TYPE_FLAG');
571 delete from fem_dim_attributes_vl
572 where attribute_varchar_label = 'STATISTICS_TYPE_FLAG';
573
574
575
576 ----------------------------------------------------------
577 -- bug#5237422
578 begin
579
580 select dimension_id
581 into v_ledger_dim_id
582 from fem_dimensions_b
583 where dimension_varchar_label = 'LEDGER';
584
585 select dimension_id
586 into v_natrl_dim_id
587 from fem_dimensions_b
588 where dimension_varchar_label = 'NATURAL_ACCOUNT';
589
590 select dimension_id
591 into v_cctr_dim_id
592 from fem_dimensions_b
593 where dimension_varchar_label = 'COMPANY_COST_CENTER_ORG';
594
595 select dimension_id
596 into v_finelem_dim_id
597 from fem_dimensions_b
598 where dimension_varchar_label = 'FINANCIAL_ELEMENT';
599
600 select dimension_id
601 into v_lnitem_dim_id
602 from fem_dimensions_b
603 where dimension_varchar_label = 'LINE_ITEM';
604
605
606 /* Obsolete Ledger Dimension Attributes */
607 for attr in c_ldgattr (v_ledger_dim_id) loop
608
609 delete from fem_dsnp_ledgers_attr
610 where attribute_id = attr.attribute_id;
611
612 delete from fem_ledgers_attr
613 where attribute_id = attr.attribute_id;
614
615 delete from fem_dim_attr_versions_tl
616 where version_id in (select version_id
617 from fem_dim_attr_Versions_b
618 where attribute_id = attr.attribute_id);
619
620 delete from fem_dim_attr_versions_b
621 where attribute_id = attr.attribute_id;
622
623 delete from fem_dim_attributes_tl
624 where attribute_id = attr.attribute_id;
625
626 delete from fem_dim_attributes_b
627 where attribute_id = attr.attribute_id;
628
629 delete from fem_dim_attr_grps
630 where attribute_id=attr.attribute_id;
631
632 delete from fem_dim_attributes_priv
633 where attribute_id = attr.attribute_id;
634
635 end loop;
636
637
638 /* Obsolete Natural Account Dimension Attributes */
639 for attr in c_natattr (v_natrl_dim_id) loop
640
641 delete from fem_dsnp_ledgers_attr
642 where attribute_id = attr.attribute_id;
643
644 delete from fem_ledgers_attr
645 where attribute_id = attr.attribute_id;
646
647 delete from fem_dim_attr_versions_tl
648 where version_id in (select version_id
649 from fem_dim_attr_Versions_b
650 where attribute_id = attr.attribute_id);
651
652 delete from fem_dim_attr_versions_b
653 where attribute_id = attr.attribute_id;
654
655 delete from fem_dim_attributes_tl
656 where attribute_id = attr.attribute_id;
657
658 delete from fem_dim_attributes_b
659 where attribute_id = attr.attribute_id;
660
661 delete from fem_dim_attr_grps
662 where attribute_id=attr.attribute_id;
663
664 delete from fem_dim_attributes_priv
665 where attribute_id = attr.attribute_id;
666
667 end loop;
668
669
670 /* Obsolete CCTR Dimension Attributes */
671 for attr in c_cctrattr (v_cctr_dim_id) loop
672
673 delete from fem_dsnp_ledgers_attr
674 where attribute_id = attr.attribute_id;
675
676 delete from fem_ledgers_attr
677 where attribute_id = attr.attribute_id;
678
679 delete from fem_dim_attr_versions_tl
680 where version_id in (select version_id
681 from fem_dim_attr_Versions_b
682 where attribute_id = attr.attribute_id);
683
684 delete from fem_dim_attr_versions_b
685 where attribute_id = attr.attribute_id;
686
687 delete from fem_dim_attributes_tl
688 where attribute_id = attr.attribute_id;
689
690 delete from fem_dim_attributes_b
691 where attribute_id = attr.attribute_id;
692
693 delete from fem_dim_attr_grps
694 where attribute_id=attr.attribute_id;
695
696 delete from fem_dim_attributes_priv
697 where attribute_id = attr.attribute_id;
698
699 end loop;
700
701
702 /* Obsolete Fin Elem Dimension Attributes */
703 for attr in c_feattr (v_finelem_dim_id) loop
704
705 delete from fem_dsnp_ledgers_attr
706 where attribute_id = attr.attribute_id;
707
708 delete from fem_ledgers_attr
709 where attribute_id = attr.attribute_id;
710
711 delete from fem_dim_attr_versions_tl
712 where version_id in (select version_id
713 from fem_dim_attr_Versions_b
714 where attribute_id = attr.attribute_id);
715
716 delete from fem_dim_attr_versions_b
717 where attribute_id = attr.attribute_id;
718
719 delete from fem_dim_attributes_tl
720 where attribute_id = attr.attribute_id;
721
722 delete from fem_dim_attributes_b
723 where attribute_id = attr.attribute_id;
724
725 delete from fem_dim_attr_grps
726 where attribute_id=attr.attribute_id;
727
728 delete from fem_dim_attributes_priv
729 where attribute_id = attr.attribute_id;
730
731 end loop;
732
733
734 /* Obsolete Line Item Dimension Attributes */
735 for attr in c_lnattr (v_lnitem_dim_id) loop
736
740 delete from fem_ledgers_attr
737 delete from fem_dsnp_ledgers_attr
738 where attribute_id = attr.attribute_id;
739
741 where attribute_id = attr.attribute_id;
742
743 delete from fem_dim_attr_versions_tl
744 where version_id in (select version_id
745 from fem_dim_attr_Versions_b
746 where attribute_id = attr.attribute_id);
747
748 delete from fem_dim_attr_versions_b
749 where attribute_id = attr.attribute_id;
750
751 delete from fem_dim_attributes_tl
752 where attribute_id = attr.attribute_id;
753
754 delete from fem_dim_attributes_b
755 where attribute_id = attr.attribute_id;
756
757 delete from fem_dim_attr_grps
758 where attribute_id=attr.attribute_id;
759
760 delete from fem_dim_attributes_priv
761 where attribute_id = attr.attribute_id;
762
763 end loop;
764
765
766 /* FEM Data Type */
767 delete from fem_data_types_tl
768 where fem_data_type_code = 'VARCHAR2';
769
770 delete from fem_data_types_b
771 where fem_data_type_code = 'VARCHAR2';
772
773 delete from fem_data_types_attr
774 where fem_data_type_code = 'VARCHAR2';
775
776 end;
777
778 ----------------------------------------------------------
779
780
781 END del_obsolete_seed_data;
782
783 END fem_refresh_util_pkg;
[Home] [Help]