1 PACKAGE BODY ZPB_BUSAREA_MAINT AS
2 /* $Header: ZPBVBAMB.pls 120.36 2007/12/04 14:36:43 mbhat noship $ */
3
4 TYPE epb_curs_type is REF CURSOR;
5
6 -------------------------------------------------------------------------
7 -- GET_DEFAULT_BUS_AREA_NAME - Returns a default Business Area name
8 --
9 -------------------------------------------------------------------------
10 FUNCTION GET_DEFAULT_BUS_AREA_NAME return VARCHAR2
11 is
12 l_count NUMBER;
13 l_name VARCHAR2(100);
14 begin
15 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_EPB_BUSINESS_AREA');
16 l_name := FND_MESSAGE.GET;
17
18 select count(*)
19 into l_count
20 from ZPB_BUSINESS_AREAS_VL
21 where NAME = l_name;
22
23 if (l_count > 0) then
24 l_name := l_name||' '||l_count;
25 end if;
26 return l_name;
27 end GET_DEFAULT_BUS_AREA_NAME;
28
29 -------------------------------------------------------------------------
30 -- GET_PARENT_VERSION_TYPE - Returns the "Parent" version type
31 --
32 -- IN: p_version_type - The version type
33 --
34 -- OUT: The "parent" version type
35 -------------------------------------------------------------------------
36 FUNCTION GET_PARENT_VERSION_TYPE (p_version_type IN VARCHAR2)
37 return VARCHAR2
38 is
39 begin
40 if (p_version_type = 'T') then
41 return 'D';
42 elsif (p_version_type = 'D') then
43 return 'P';
44 elsif (p_version_type = 'P') then
45 return 'R';
46 else return null;
47 end if;
48 end GET_PARENT_VERSION_TYPE;
49
50
51
52 -------------------------------------------------------------------------
53 -- ADD_ATTRIBUTE - Adds an attribute to the Business Area version
54 --
55 -- IN: p_version_id - The version ID
56 -- p_logical_dim_id - Logical Dimension Id
57 -- p_attribute_id - The FEM Attribute ID
58 -------------------------------------------------------------------------
59 PROCEDURE ADD_ATTRIBUTE (p_version_id IN NUMBER,
60 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
61 p_attribute_id IN NUMBER)
62 is
63 begin
64 insert into ZPB_BUSAREA_ATTRIBUTES
65 (VERSION_ID,
66 LOGICAL_DIM_ID, -- "Consistent Dimension"
67 ATTRIBUTE_ID,
68 CREATION_DATE,
69 CREATED_BY,
70 LAST_UPDATE_LOGIN,
71 LAST_UPDATE_DATE,
72 LAST_UPDATED_BY)
73 values
74 (p_version_id,
75 p_logical_dim_id, -- "Consistent Dimension"
76 p_attribute_id,
77 sysdate,
78 FND_GLOBAL.USER_ID,
79 FND_GLOBAL.LOGIN_ID,
80 sysdate,
81 FND_GLOBAL.USER_ID);
82
83 end ADD_ATTRIBUTE;
84
85 -------------------------------------------------------------------------
86 -- ADD_CONDITION - Adds an attribute condition to the Business Area version
87 --
88 -- IN: p_version_id - The version ID
89 -- p_logical_dim_id - Logical Dimension Id
90 -- p_attribute_id - The FEM Attribute ID
91 -- p_value - The attribute value
92 -- p_value_set_id - The value set ID, for VS-enabled attributes
93 -- p_operation - The operator for the condition (default null)
94 -------------------------------------------------------------------------
95 PROCEDURE ADD_CONDITION (p_version_id IN NUMBER,
96 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
97 p_attribute_id IN NUMBER,
98 p_value IN VARCHAR2,
99 p_value_set_id IN NUMBER,
100 p_operation IN VARCHAR2)
101 is
102 begin
103 insert into ZPB_BUSAREA_CONDITIONS
104 (VERSION_ID,
105 LOGICAL_DIM_ID, -- "Consistent Dimension"
106 ATTRIBUTE_ID,
107 VALUE,
108 VALUE_SET_ID,
109 OPERATION,
110 CREATION_DATE,
111 CREATED_BY,
112 LAST_UPDATE_LOGIN,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY)
115 values
116 (p_version_id,
117 p_logical_dim_id, -- "Consistent Dimension"
118 p_attribute_id,
119 p_value,
120 p_value_set_id,
121 p_operation,
122 sysdate,
123 FND_GLOBAL.USER_ID,
124 FND_GLOBAL.LOGIN_ID,
125 sysdate,
126 FND_GLOBAL.USER_ID);
127 end ADD_CONDITION;
128
129 -------------------------------------------------------------------------
130 -- ADD_DATASET - Adds a dataset to the Business Area version
131 --
132 -- IN: p_version_id - The version ID
133 -- p_dataset_id - The FEM Dataset ID
134 -------------------------------------------------------------------------
135 PROCEDURE ADD_DATASET (p_version_id IN NUMBER,
136 p_dataset_id IN NUMBER)
137 is
138 begin
139 insert into ZPB_BUSAREA_DATASETS
140 (VERSION_ID,
141 DATASET_ID,
142 CREATION_DATE,
143 CREATED_BY,
144 LAST_UPDATE_LOGIN,
145 LAST_UPDATE_DATE,
146 LAST_UPDATED_BY)
147 values
148 (p_version_id,
149 p_dataset_id,
150 sysdate,
151 FND_GLOBAL.USER_ID,
152 FND_GLOBAL.LOGIN_ID,
153 sysdate,
154 FND_GLOBAL.USER_ID);
155 end ADD_DATASET;
156
157 -------------------------------------------------------------------------
158 -- ADD_DIMENSION - Adds a dimension to the Business Area version
159 --
160 -- IN: p_version_id - The version ID
161 -- p_func_dim_set_id - Functional Dimension Set Id
162 -- p_dimension_id - The FEM Dimension ID
163 -------------------------------------------------------------------------
164 PROCEDURE ADD_DIMENSION (p_version_id IN NUMBER,
165 p_func_dim_set_id IN NUMBER, -- "Consistent Dimension"
166 p_dimension_id IN NUMBER)
167 is
168 l_def_hier ZPB_BUSAREA_DIMENSIONS.DEFAULT_HIERARCHY_ID%type;
169 l_ledger ZPB_BUSAREA_LEDGERS.LEDGER_ID%type;
170 l_cal_dim_id FEM_XDIM_DIMENSIONS.DIMENSION_ID%type;
171 l_cal_dim_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
172 l_cal_dim_code FEM_XDIM_DIMENSIONS.MEMBER_DISPLAY_CODE_COL%type;
173 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
174 l_def_mbr_code FEM_XDIM_DIMENSIONS.DEFAULT_MEMBER_DISPLAY_CODE%type;
175 l_dim_table FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%type;
176 l_attr_table FEM_XDIM_DIMENSIONS.ATTRIBUTE_TABLE_NAME%type;
177 l_attr_id FEM_DIM_ATTRIBUTES_B.ATTRIBUTE_ID%type;
178 l_is_line ZPB_BUSAREA_DIMENSIONS.EPB_LINE_DIMENSION%type;
179 l_hier NUMBER;
180 l_count NUMBER;
181 l_command VARCHAR2(2000);
182 l_logical_dim_id ZPB_BUSAREA_DIMENSIONS.LOGICAL_DIM_ID%type;
183 l_aw_dim_name ZPB_BUSAREA_DIMENSIONS.AW_DIM_NAME%type;
184 l_aw_dim_prefix ZPB_BUSAREA_DIMENSIONS.AW_DIM_PREFIX%type;
185 l_member_b_table FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%type;
186 l_dim_type_code FEM_XDIM_DIMENSIONS.DIMENSION_TYPE_CODE%type;
187 l_len number;
188 l_suffix varchar2(1);
189 l_start_ascii_value number;
190 l_ascii_dim_count number;
191
192
193 cursor c_dim_hier_curs is
194 select HIERARCHY_OBJ_ID
195 from FEM_HIERARCHIES
196 where DIMENSION_ID = p_dimension_id
197 and PERSONAL_FLAG = 'N';
198 l_dim_hier c_dim_hier_curs%ROWTYPE;
199
200 l_curs EPB_CURS_TYPE;
201 begin
202 --
203 -- First step is to get the FEM default member/hier for the dimension
204 -- Need to use ledger picked for the business area, or else the
205 -- default ledger profile if ledgers are not picked yet
206 --
207 begin
208 select MIN(LEDGER_ID)
209 into l_ledger
210 from ZPB_BUSAREA_LEDGERS
211 where VERSION_ID = p_version_id;
212 exception
213 when no_data_found then
214 l_ledger := to_number(FND_PROFILE.VALUE_SPECIFIC('FEM_LEDGER',
215 FND_GLOBAL.USER_ID));
216 end;
217
218 select VALUE_SET_REQUIRED_FLAG,
219 decode(DIMENSION_TYPE_CODE, 'LINE', 'Y', 'N')
220 into l_vs_req, l_is_line
221 from FEM_XDIM_DIMENSIONS
222 where DIMENSION_ID = p_dimension_id;
223
224 if (l_vs_req = 'Y') then
225 begin
226 select DEFAULT_HIERARCHY_OBJ_ID
227 into l_def_hier
228 from FEM_GLOBAL_VS_COMBO_DEFS A,
229 FEM_VALUE_SETS_VL B,
230 FEM_LEDGERS_ATTR C,
231 FEM_DIM_ATTRIBUTES_B D,
232 FEM_DIM_ATTR_VERSIONS_B E
233 where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
234 and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
235 and E.DEFAULT_VERSION_FLAG = 'Y'
236 and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
237 and C.DIM_ATTRIBUTE_NUMERIC_MEMBER = A.GLOBAL_VS_COMBO_ID
238 and B.DIMENSION_ID = p_dimension_id
239 and B.VALUE_SET_ID = A.VALUE_SET_ID
240 and C.AW_SNAPSHOT_FLAG = 'N'
241 and C.LEDGER_ID = l_ledger;
242 exception
243 when no_data_found then
244 null;
245 end;
246 elsif (p_dimension_id = 1) then
247 --
248 -- For time, need to look at default member/hier for default calendar
249 --
250 select A.DIMENSION_ID,
251 A.MEMBER_COL,
252 A.MEMBER_DISPLAY_CODE_COL,
253 A.MEMBER_B_TABLE_NAME,
254 A.DEFAULT_MEMBER_DISPLAY_CODE,
255 A.ATTRIBUTE_TABLE_NAME
256 into l_cal_dim_id, l_cal_dim_col, l_cal_dim_code,
257 l_dim_table, l_def_mbr_code, l_attr_table
258 from FEM_XDIM_DIMENSIONS A,
259 FEM_DIMENSIONS_B B
260 where A.DIMENSION_ID = B.DIMENSION_ID
261 and B.DIMENSION_VARCHAR_LABEL = 'CALENDAR';
262
263 l_command := 'select A.DIM_ATTRIBUTE_NUMERIC_MEMBER
264 from '||l_attr_table||' A, FEM_DIM_ATTRIBUTES_B B,
265 FEM_DIM_ATTR_VERSIONS_B C, '||l_dim_table||' D
266 where A.'||l_cal_dim_col||' = D.'||l_cal_dim_col||'
267 and D.'||l_cal_dim_code||' = '''||l_def_mbr_code||'''
268 and A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
269 and A.ATTRIBUTE_ID = C.ATTRIBUTE_ID
270 and B.DIMENSION_ID = '||l_cal_dim_id||'
271 and B.ATTRIBUTE_VARCHAR_LABEL = ''DEFAULT_HIERARCHY''
272 and A.VERSION_ID = C.VERSION_ID
273 and C.DEFAULT_VERSION_FLAG = ''Y''';
274
275 open l_curs for l_command;
276 loop
277 fetch l_curs into l_def_hier;
278 exit when l_curs%NOTFOUND;
279 end loop;
280
281 end if;
282
283 -- "Consistent Dimension"
284 -- Get the Logical Dimension Id from the sequence
285 Select zpb_busarea_logical_dims_seq.nextval
286 into l_logical_dim_id
287 from dual;
288
289 -- "Consistent Dimension"
290 -- Generate the AW Logical Dimension Name and Dim Prefix
291
292 l_start_ascii_value := 65; -- start from B, not A
293 -- will also need to skip H
294
295 Begin
296 select count(LOGICAL_DIM_ID) + l_start_ascii_value
297 into l_ascii_dim_count
298 from ZPB_BUSAREA_DIMENSIONS
299 where VERSION_ID = p_version_id
300 and DIMENSION_ID = p_dimension_id;
301 Exception
302 When no_data_found then null;
303 end;
304
305 select member_b_table_name, dimension_type_code
306 into l_member_b_table, l_dim_type_code
307 from fem_xdim_dimensions
308 where dimension_id = p_dimension_id;
309
310 if(l_dim_type_code = 'LINE') then
311 l_aw_dim_prefix := 'DL';
312 else
313 l_aw_dim_prefix := concat('D', p_dimension_id);
314 end if;
315
316 GENERATE_AW_DIM_NAME(l_dim_type_code,
317 l_member_b_table,
318 l_aw_dim_name);
319
320 if (l_ascii_dim_count > 65) then
321 -- must skip the letter H as well
322 if (l_ascii_dim_count = 72) then
323 l_ascii_dim_count := 73;
324 end if;
325
326 l_suffix := nchr(l_ascii_dim_count);
327 l_aw_dim_prefix := concat(l_aw_dim_prefix, l_suffix);
328 l_aw_dim_name := l_aw_dim_name || '_' || l_suffix;
329
330 end if;
331
332
333 insert into ZPB_BUSAREA_DIMENSIONS
334 (VERSION_ID,
335 DIMENSION_ID,
336 LOGICAL_DIM_ID, -- "Consistent Dimension"
337 FUNC_DIM_SET_ID, -- "Consistent Dimension"
338 AW_DIM_NAME, -- "Consistent Dimension"
339 AW_DIM_PREFIX, -- "Consistent Dimension"
340 DEFAULT_HIERARCHY_ID,
341 USE_MEMBER_CONDITIONS,
342 EPB_LINE_DIMENSION,
343 CONDITIONS_INCL_DESC,
344 CONDITIONS_INCL_ANC,
345 CREATION_DATE,
346 CREATED_BY,
347 LAST_UPDATE_LOGIN,
348 LAST_UPDATE_DATE,
349 LAST_UPDATED_BY)
350 values
351 (p_version_id,
352 p_dimension_id,
353 l_logical_dim_id, -- "Consistent Dimension"
354 p_func_dim_set_id, -- "Consistent Dimension"
355 l_aw_dim_name, -- "Consistent Dimension"
356 l_aw_dim_prefix, -- "Consistent Dimension"
357 l_def_hier,
358 'N',
359 l_is_line,
360 'N',
361 'N',
362 sysdate,
363 FND_GLOBAL.USER_ID,
364 FND_GLOBAL.LOGIN_ID,
365 sysdate,
366 FND_GLOBAL.USER_ID);
367
368 if (p_dimension_id <> 7) then
369 l_count := 0;
370 for l_dim_hier in c_dim_hier_curs loop
371 l_hier := l_dim_hier.HIERARCHY_OBJ_ID;
372 l_count := l_count + 1;
373 ADD_HIERARCHY (p_version_id,
374 l_logical_dim_id, -- "Consistent Dimension"
375 l_hier);
376 end loop;
377
378 if (l_count = 1) then
379 update ZPB_BUSAREA_DIMENSIONS
380 set DEFAULT_HIERARCHY_ID = l_hier
381 where VERSION_ID = p_version_id
382 and DIMENSION_ID = p_dimension_id
383 and FUNC_DIM_SET_ID = p_func_dim_set_id -- "Consistent Dimension"
384 and DEFAULT_HIERARCHY_ID is null;
385 end if;
386 end if;
387
388 end ADD_DIMENSION;
389
390 -------------------------------------------------------------------------
391 -- ADD_HIERARCHY - Adds a hierarchy to the Business Area version
392 --
393 -- IN: p_version_id - The version ID
394 -- p_logical_dim_id - Logical Dimension Id
395 -- p_hierarchy_id - The FEM Hierarchy ID
396 -------------------------------------------------------------------------
397 PROCEDURE ADD_HIERARCHY (p_version_id IN NUMBER,
398 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
399 p_hierarchy_id IN NUMBER)
400 is
401 l_multi_top FEM_HIERARCHIES.MULTI_TOP_FLAG%type;
402 l_dimension_id FEM_HIERARCHIES.DIMENSION_ID%type;
403 l_hier_table FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%type;
404 l_dim_table FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%type;
405 l_member_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
406 l_has_pers FEM_XDIM_DIMENSIONS.HIER_EDITOR_MANAGED_FLAG%type;
407 l_command VARCHAR2(1000);
408 begin
409
410 insert into ZPB_BUSAREA_HIERARCHIES
411 (VERSION_ID,
412 LOGICAL_DIM_ID, -- "Consistent Dimension"
413 HIERARCHY_ID,
414 KEEP_VERSION,
415 INCLUDE_ALL_TOP_MEMBERS,
416 INCLUDE_ALL_LEVELS,
417 CREATION_DATE,
418 CREATED_BY,
419 LAST_UPDATE_LOGIN,
420 LAST_UPDATE_DATE,
421 LAST_UPDATED_BY)
422 values
423 (p_version_id,
424 p_logical_dim_id, -- "Consistent Dimension"
425 p_hierarchy_id,
426 'N',
427 'Y',
428 'Y',
429 sysdate,
430 FND_GLOBAL.USER_ID,
431 FND_GLOBAL.LOGIN_ID,
432 sysdate,
433 FND_GLOBAL.USER_ID);
434
435 -- "Consistent Dimension"
436 Begin
437 select MULTI_TOP_FLAG, DIMENSION_ID
438 into l_multi_top, l_dimension_id
439 from FEM_HIERARCHIES
440 where HIERARCHY_OBJ_ID = p_hierarchy_id;
441 Exception
442 When no_data_found then null;
443 End;
444
445 if (l_multi_top = FND_API.G_TRUE) then
446
447 select HIERARCHY_TABLE_NAME, HIER_EDITOR_MANAGED_FLAG
448 into l_dim_table, l_has_pers
449 from FEM_XDIM_DIMENSIONS
450 where DIMENSION_ID = l_dimension_id;
451
452 l_command := '
453 insert into ZPB_BUSAREA_HIER_MEMBERS
454 (VERSION_ID,
455 LOGICAL_DIM_ID, -- "Consistent Dimension"
456 HIERARCHY_ID,
457 MEMBER_ID,
458 VALUE_SET_ID,
459 CREATION_DATE,
460 CREATED_BY,
461 LAST_UPDATE_LOGIN,
462 LAST_UPDATE_DATE,
463 LAST_UPDATED_BY)
464 select
465 p_version_id,
466 p_logical_dim_id, -- "Consistent Dimension"
467 p_hierarchy_id,
468 A.PARENT_ID,
469 A.PARENT_VALUE_SET_ID
470 from '||l_dim_table||' A,
471 '||l_dim_table||' B
472 where A.PARENT_ID = A.CHILD_ID
473 and A.PARENT_DEPTH_NUM = 1
474 and A.PARENT_ID = B.'||l_member_col||'
475 and A.PARENT_VALUE_SET_ID = B.VALUE_SET_ID';
476 if (l_has_pers = 'Y') then
477 l_command := l_command||'
478 and B.ENABLED_FLAG = ''Y''
479 and B.PERSONAL_FLAG = ''N''';
480 end if;
481 execute immediate l_command;
482 end if;
483
484 end ADD_HIERARCHY;
485
486 -------------------------------------------------------------------------
487 -- ADD_HIERARCHY_MEMBER - Adds a top level member to the Business Area version
488 --
489 -- IN: p_version_id - The version ID
490 -- p_logical_dim_id - Logical Dimension Id
491 -- p_hierarchy_id - The FEM Hierarchy ID
492 -- p_hier_mbr_id - The FEM member ID
493 -- p_member_vset - The FEM member valueset ID (defaults to null)
494 -- p_hier_version - The FEM hierarchy version ID (defaults to null)
495 -------------------------------------------------------------------------
496 PROCEDURE ADD_HIERARCHY_MEMBER (p_version_id IN NUMBER,
497 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
498 p_hierarchy_id IN NUMBER,
499 p_member_id IN NUMBER,
500 p_member_vset IN NUMBER,
501 p_hier_version IN NUMBER)
502 is
503 begin
504 insert into ZPB_BUSAREA_HIER_MEMBERS
505 (VERSION_ID,
506 LOGICAL_DIM_ID, -- "Consistent Dimension"
507 HIERARCHY_ID,
508 MEMBER_ID,
509 VALUE_SET_ID,
510 HIER_VERSION_ID,
511 CREATION_DATE,
512 CREATED_BY,
513 LAST_UPDATE_LOGIN,
514 LAST_UPDATE_DATE,
515 LAST_UPDATED_BY)
516 values
517 (p_version_id,
518 p_logical_dim_id, -- "Consistent Dimension"
519 p_hierarchy_id,
520 p_member_id,
521 p_member_vset,
522 p_hier_version,
523 sysdate,
527 FND_GLOBAL.USER_ID);
524 FND_GLOBAL.USER_ID,
525 FND_GLOBAL.LOGIN_ID,
526 sysdate,
528
529 end ADD_HIERARCHY_MEMBER;
530
531 -------------------------------------------------------------------------
532 -- ADD_HIERARCHY_VERSION - Adds a hierarchy to the Business Area version
533 --
534 -- IN: p_version_id - The version ID
535 -- p_logical_dim_id - Logical Dimension Id
536 -- p_hierarchy_id - The FEM Hierarchy ID
537 -- p_hier_vers_id - The FEM Hierarchy Version ID
538 -------------------------------------------------------------------------
539 PROCEDURE ADD_HIERARCHY_VERSION (p_version_id IN NUMBER,
540 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
541 p_hierarchy_id IN NUMBER,
542 p_hier_vers_id IN NUMBER)
543 is
544 begin
545 insert into ZPB_BUSAREA_HIER_VERSIONS
546 (VERSION_ID,
547 LOGICAL_DIM_ID, -- "Consistent Dimension"
548 HIERARCHY_ID,
549 HIER_VERSION_ID,
550 INCLUDE_ALL_TOP_MEMBERS,
551 CREATION_DATE,
552 CREATED_BY,
553 LAST_UPDATE_LOGIN,
554 LAST_UPDATE_DATE,
555 LAST_UPDATED_BY)
556 values
557 (p_version_id,
558 p_logical_dim_id, -- "Consistent Dimension"
559 p_hierarchy_id,
560 p_hier_vers_id,
561 'Y',
562 sysdate,
563 FND_GLOBAL.USER_ID,
564 FND_GLOBAL.LOGIN_ID,
565 sysdate,
566 FND_GLOBAL.USER_ID);
567
568 end ADD_HIERARCHY_VERSION;
569
570 -------------------------------------------------------------------------
571 -- ADD_LEDGER - Adds a ledger to the Business Area version
572 --
573 -- IN: p_version_id - The version ID
574 -- p_ledger_id - The FEM Ledger ID
575 -------------------------------------------------------------------------
576 PROCEDURE ADD_LEDGER (p_version_id IN NUMBER,
577 p_ledger_id IN NUMBER)
578 is
579 begin
580 insert into ZPB_BUSAREA_LEDGERS
581 (VERSION_ID,
582 LEDGER_ID,
583 CREATION_DATE,
584 CREATED_BY,
585 LAST_UPDATE_LOGIN,
586 LAST_UPDATE_DATE,
587 LAST_UPDATED_BY)
588 values
589 (p_version_id,
590 p_ledger_id,
591 sysdate,
592 FND_GLOBAL.USER_ID,
593 FND_GLOBAL.LOGIN_ID,
594 sysdate,
595 FND_GLOBAL.USER_ID);
596 end ADD_LEDGER;
597
598 -------------------------------------------------------------------------
599 -- ADD_LEVEL - Adds a level to the Business Area version
600 --
601 -- IN: p_version_id - The version ID
602 -- p_logical_dim_id - Logical Dimension Id
603 -- p_level_id - The FEM Level ID
604 -- p_hierarchy_id - The Hierarchy to add the level to
605 -------------------------------------------------------------------------
606 PROCEDURE ADD_LEVEL (p_version_id IN NUMBER,
607 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
608 p_level_id IN NUMBER,
609 p_hierarchy_id IN NUMBER)
610 is
611 begin
612 insert into ZPB_BUSAREA_LEVELS
613 (VERSION_ID,
614 LOGICAL_DIM_ID, -- "Consistent Dimension"
615 LEVEL_ID,
616 HIERARCHY_ID,
617 CREATION_DATE,
618 CREATED_BY,
619 LAST_UPDATE_LOGIN,
620 LAST_UPDATE_DATE,
621 LAST_UPDATED_BY)
622 values
623 (p_version_id,
624 p_logical_dim_id, -- "Consistent Dimension"
625 p_level_id,
626 p_hierarchy_id,
627 sysdate,
628 FND_GLOBAL.USER_ID,
629 FND_GLOBAL.LOGIN_ID,
630 sysdate,
631 FND_GLOBAL.USER_ID);
632 end ADD_LEVEL;
633
634 -------------------------------------------------------------------------
635 -- CHANGE_HIER_VERS_INCL - Should be called anytime the user changes
636 -- what hierarchy versions are included in the
637 -- Business Area
638 --
639 -- IN: p_version_id - The version ID
640 -- p_logical_dim_id - Logical Dimension Id
641 -- p_hierarchy_id - The Hierarchy to add the level to
642 -------------------------------------------------------------------------
643 PROCEDURE CHANGE_HIER_VERS_INCL (p_version_id IN NUMBER,
644 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
645 p_hierarchy_id IN NUMBER)
646 is
647 l_incl_type ZPB_BUSAREA_HIERARCHIES.KEEP_VERSION%type;
648 l_number ZPB_BUSAREA_HIERARCHIES.NUMBER_OF_VERSIONS%type;
649 l_count NUMBER;
650
651 cursor hier_last_vers is
652 select OBJECT_DEFINITION_ID
653 from FEM_OBJECT_DEFINITION_B
654 where OBJECT_ID = p_hierarchy_id
655 and EFFECTIVE_START_DATE < sysdate
656 order by EFFECTIVE_END_DATE DESC;
657 begin
658 select KEEP_VERSION, NUMBER_OF_VERSIONS
659 into l_incl_type, l_number
660 from ZPB_BUSAREA_HIERARCHIES
664
661 where VERSION_ID = p_version_id
662 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
663 and HIERARCHY_ID = p_hierarchy_id;
665 if (l_incl_type= 'N' or l_incl_type = 'L') then
666 delete from ZPB_BUSAREA_HIER_VERSIONS
667 where VERSION_ID = p_version_id
668 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
669 and HIERARCHY_ID = p_hierarchy_id;
670
671 elsif (l_incl_type = 'S' and l_number is not null and l_number > 0) then
672 l_count := 1;
673 for each_vers in hier_last_vers loop
674 insert into ZPB_BUSAREA_HIER_VERSIONS
675 (VERSION_ID,
676 LOGICAL_DIM_ID, -- "Consistent Dimension"
677 HIERARCHY_ID,
678 HIER_VERSION_ID,
679 INCLUDE_ALL_TOP_MEMBERS,
680 LAST_UPDATE_LOGIN,
681 LAST_UPDATE_DATE,
682 LAST_UPDATED_BY,
683 CREATION_DATE,
684 CREATED_BY)
685 values
686 (p_version_id,
687 p_logical_dim_id, -- "Consistent Dimension"
688 p_hierarchy_id,
689 each_vers.OBJECT_DEFINITION_ID,
690 'Y',
691 FND_GLOBAL.LOGIN_ID,
692 sysdate,
693 FND_GLOBAL.USER_ID,
694 sysdate,
695 FND_GLOBAL.USER_ID);
696 l_count := l_count+1;
697 exit when (l_count > l_number);
698 end loop;
699 end if;
700 end CHANGE_HIER_VERS_INCL;
701
702 -------------------------------------------------------------------------
703 -- CLEAR_VERSION (private) - Clears the definition for a version to be empty
704 --
705 -- IN: p_version_id - The version ID
706 -------------------------------------------------------------------------
707 PROCEDURE CLEAR_VERSION (p_version_id IN NUMBER)
708 is
709 begin
710 delete from ZPB_BUSAREA_DIMENSIONS
711 where VERSION_ID = p_version_id;
712
713 delete from ZPB_BUSAREA_HIERARCHIES
714 where VERSION_ID = p_version_id;
715
716 delete from ZPB_BUSAREA_HIER_MEMBERS
717 where VERSION_ID = p_version_id;
718
719 delete from ZPB_BUSAREA_HIER_VERSIONS
720 where VERSION_ID = p_version_id;
721
722 delete from ZPB_BUSAREA_LEVELS
723 where VERSION_ID = p_version_id;
724
725 delete from ZPB_BUSAREA_ATTRIBUTES
726 where VERSION_ID = p_version_id;
727
728 delete from ZPB_BUSAREA_CONDITIONS
729 where VERSION_ID = p_version_id;
730
731 delete from ZPB_BUSAREA_LEDGERS
732 where VERSION_ID = p_version_id;
733
734 delete from ZPB_BUSAREA_DATASETS
735 where VERSION_ID = p_version_id;
736 end CLEAR_VERSION;
737
738 -------------------------------------------------------------------------
739 -- CREATE_BUSINESS_AREA - Creates a new empty Business Area
740 --
741 -- OUT: The created Business Area's ID
742 -------------------------------------------------------------------------
743 FUNCTION CREATE_BUSINESS_AREA
744 return NUMBER is
745 l_business_area_id ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
746 l_user_id FND_USER.USER_ID%type;
747 begin
748 l_user_id := FND_GLOBAL.USER_ID;
749
750 select ZPB_BUSINESS_AREAS_SEQ.nextval into l_business_area_id from dual;
751
752 insert into ZPB_BUSINESS_AREAS
753 (BUSINESS_AREA_ID,
754 BUSAREA_CREATED_BY,
755 DATA_AW,
756 ANNOTATION_AW,
757 CREATION_DATE,
758 CREATED_BY,
759 LAST_UPDATE_LOGIN,
760 LAST_UPDATE_DATE,
761 LAST_UPDATED_BY)
762 values
763 (l_business_area_id,
764 l_user_id,
765 'ZPBDATA'||l_business_area_id,
766 'ZPBANNOT'||l_business_area_id,
767 sysdate,
768 FND_GLOBAL.USER_ID,
769 FND_GLOBAL.LOGIN_ID,
770 sysdate,
771 FND_GLOBAL.USER_ID);
772
773 insert into ZPB_BUSAREA_USERS
774 (BUSINESS_AREA_ID,
775 USER_ID,
776 CREATION_DATE,
777 CREATED_BY,
778 LAST_UPDATE_LOGIN,
779 LAST_UPDATE_DATE,
780 LAST_UPDATED_BY)
781 values
782 (l_business_area_id,
783 l_user_id,
784 sysdate,
785 FND_GLOBAL.USER_ID,
786 FND_GLOBAL.LOGIN_ID,
787 sysdate,
788 FND_GLOBAL.USER_ID);
789
790 return l_business_area_id;
791 end CREATE_BUSINESS_AREA;
792
793 -------------------------------------------------------------------------
794 -- CREATE_EMPTY_VERSION - Creates a new, empty version for a Business Area. If
795 -- the version already exists, it will be overwritten
796 -- (cleared). If you want to create a version with a
797 -- default definition, use COPY_VERSION instead.
798 --
799 -- IN: p_business_area_id - The Business Area ID of the version
800 -- p_version_type - The version type ('P', 'D', 'T', 'R')
801 --
802 -- OUT: The created Business Area version's ID
803 -------------------------------------------------------------------------
804 FUNCTION CREATE_EMPTY_VERSION (p_business_area_id IN NUMBER,
808 l_version_name ZPB_BUSAREA_VERSIONS.NAME%type;
805 p_version_type IN VARCHAR2)
806 return NUMBER is
807 l_version_id ZPB_BUSAREA_VERSIONS.VERSION_ID%type;
809 l_version_desc ZPB_BUSAREA_VERSIONS.DESCRIPTION%type;
810 l_version_curr ZPB_BUSAREA_VERSIONS.CURRENCY_ENABLED%type;
811 l_version_inter ZPB_BUSAREA_VERSIONS.INTERCOMPANY_ENABLED%type;
812 l_parent_version_type ZPB_BUSAREA_VERSIONS.VERSION_TYPE%type;
813 l_ver_fdr_obj_def_id ZPB_BUSAREA_VERSIONS.FUNC_DIM_SET_OBJ_DEF_ID%type; -- "Consistent Dimension"
814 begin
815 begin
816 select VERSION_ID
817 into l_version_id
818 from ZPB_BUSAREA_VERSIONS
819 where BUSINESS_AREA_ID = p_business_area_id
820 and VERSION_TYPE = p_version_type;
821 exception
822 when no_data_found
823 then l_version_id := null;
824 end;
825
826 --
827 -- Get the name and description of the "parent" version to default this
828 -- version to:
829 --
830 l_parent_version_type := GET_PARENT_VERSION_TYPE(p_version_type);
831 begin
832
833 select
834 NAME,
835 DESCRIPTION,
836 CURRENCY_ENABLED,
837 INTERCOMPANY_ENABLED,
838 FUNC_DIM_SET_OBJ_DEF_ID -- "Consistent Dimension"
839 into
840 l_version_name,
841 l_version_desc,
842 l_version_curr,
843 l_version_inter,
844 l_ver_fdr_obj_def_id -- "Consistent Dimension"
845 from
846 ZPB_BUSAREA_VERSIONS
847 where
848 BUSINESS_AREA_ID = p_business_area_id
849 and VERSION_TYPE = l_parent_version_type;
850
851 exception
852 when no_data_found then
853 l_version_name := GET_DEFAULT_BUS_AREA_NAME;
854 l_version_desc := null;
855 l_version_curr := 'N';
856 l_version_inter := 'N';
857 l_ver_fdr_obj_def_id := null; -- "Consistent Dimension"
858 end;
859
860 if (l_version_id is not null) then
861 CLEAR_VERSION(l_version_id);
862
863 update ZPB_BUSAREA_VERSIONS set
864 NAME = l_version_name,
865 DESCRIPTION = l_version_desc,
866 CURRENCY_ENABLED = l_version_curr,
867 INTERCOMPANY_ENABLED = l_version_inter,
868 FUNC_DIM_SET_OBJ_DEF_ID= l_ver_fdr_obj_def_id, -- "Consistent Dimension"
869 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
870 LAST_UPDATE_DATE = sysdate,
871 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
872 where BUSINESS_AREA_ID = p_business_area_id
873 and VERSION_TYPE = p_version_type;
874
875 else
876 select ZPB_BUSAREA_VERSIONS_SEQ.nextval into l_version_id from dual;
877
878 insert into ZPB_BUSAREA_VERSIONS
879 (VERSION_ID,
880 BUSINESS_AREA_ID,
881 VERSION_TYPE,
882 NAME,
883 DESCRIPTION,
884 CURRENCY_ENABLED,
885 INTERCOMPANY_ENABLED,
886 FUNC_DIM_SET_OBJ_DEF_ID, -- "Consistent Dimension"
887 CREATION_DATE,
888 CREATED_BY,
889 LAST_UPDATE_LOGIN,
890 LAST_UPDATE_DATE,
891 LAST_UPDATED_BY)
892 values
893 (l_version_id,
894 p_business_area_id,
895 p_version_type,
896 l_version_name,
897 l_version_desc,
898 l_version_curr,
899 l_version_inter,
900 l_ver_fdr_obj_def_id, -- "Consistent Dimension"
901 sysdate,
902 FND_GLOBAL.USER_ID,
903 FND_GLOBAL.LOGIN_ID,
904 sysdate,
905 FND_GLOBAL.USER_ID);
906 end if;
907
908 return l_version_id;
909 end CREATE_EMPTY_VERSION;
910
911 -------------------------------------------------------------------------
912 -- COPY_VERSION - Copies one version to another. If the version that is to be
913 -- copied to does not exist, this function will create it.
914 -- Otherwise, it will overwrite that version's definition.
915 -- Returns the version ID of the version that was created or
916 -- overwritten.
917 --
918 -- IN: p_from_busarea_id - The Business Area ID that the version to copy
919 -- from is associated with
920 -- p_from_version_type - The version type of the version to copy from
921 -- p_to_busarea_id - The Business Area ID that the version to copy
922 -- to is associated with
923 -- p_to_version_type - The version type of the version to copy to
924 --
925 -- OUT: The ID of the version that was copied to
926 -------------------------------------------------------------------------
927 FUNCTION COPY_VERSION (p_from_busarea_id IN NUMBER,
928 p_from_version_type IN VARCHAR2,
929 p_to_busarea_id IN NUMBER,
930 p_to_version_type IN VARCHAR2)
931 return NUMBER is
932 l_from_version_id ZPB_BUSAREA_VERSIONS.VERSION_ID%type;
933 l_to_version_id ZPB_BUSAREA_VERSIONS.VERSION_ID%type;
934 l_version_name ZPB_BUSAREA_VERSIONS.NAME%type;
938 l_version_line_name ZPB_BUSAREA_VERSIONS.LINE_HIERARCHY_NAME%type;
935 l_version_desc ZPB_BUSAREA_VERSIONS.DESCRIPTION%type;
936 l_version_curr ZPB_BUSAREA_VERSIONS.CURRENCY_ENABLED%type;
937 l_version_inter ZPB_BUSAREA_VERSIONS.INTERCOMPANY_ENABLED%type;
939 l_version_line_desc ZPB_BUSAREA_VERSIONS.LINE_HIERARCHY_DESC%type;
940 l_parent_version_type ZPB_BUSAREA_VERSIONS.VERSION_TYPE%type;
941 l_ver_fdr_obj_def_id ZPB_BUSAREA_VERSIONS.FUNC_DIM_SET_OBJ_DEF_ID%type; -- "Consistent Dimension"
942
943 begin
944
945 select
946 VERSION_ID,
947 CURRENCY_ENABLED,
948 INTERCOMPANY_ENABLED,
949 LINE_HIERARCHY_NAME,
950 LINE_HIERARCHY_DESC,
951 FUNC_DIM_SET_OBJ_DEF_ID -- "Consistent Dimension"
952 into
953 l_from_version_id,
954 l_version_curr,
955 l_version_inter,
956 l_version_line_name,
957 l_version_line_desc,
958 l_ver_fdr_obj_def_id -- "Consistent Dimension"
959 from
960 ZPB_BUSAREA_VERSIONS
961 where
962 BUSINESS_AREA_ID = p_from_busarea_id
963 and VERSION_TYPE = p_from_version_type;
964
965 begin
966 select VERSION_ID
967 into l_to_version_id
968 from ZPB_BUSAREA_VERSIONS
969 where BUSINESS_AREA_ID = p_to_busarea_id
970 and VERSION_TYPE = p_to_version_type;
971 exception
972 when no_data_found then
973 l_to_version_id := CREATE_EMPTY_VERSION(p_to_busarea_id,
974 p_to_version_type);
975 end;
976
977 if (p_from_busarea_id <> p_to_busarea_id) then
978 --
979 -- Get the name from the "parent" draft:
980 --
981 l_parent_version_type := GET_PARENT_VERSION_TYPE(p_to_version_type);
982 begin
983 select NAME, DESCRIPTION
984 into l_version_name, l_version_desc
985 from ZPB_BUSAREA_VERSIONS
986 where VERSION_TYPE = l_parent_version_type
987 and BUSINESS_AREA_ID = p_to_busarea_id;
988 exception
989 when no_data_found then
990 l_version_name := GET_DEFAULT_BUS_AREA_NAME;
991 l_version_desc := null;
992 end;
993 else
994 select NAME, DESCRIPTION
995 into l_version_name, l_version_desc
996 from ZPB_BUSAREA_VERSIONS
997 where VERSION_ID = l_from_version_id;
998 end if;
999
1000 CLEAR_VERSION(l_to_version_id);
1001
1002 update ZPB_BUSAREA_VERSIONS set
1003 NAME = l_version_name,
1004 DESCRIPTION = l_version_desc,
1005 CURRENCY_ENABLED = l_version_curr,
1006 INTERCOMPANY_ENABLED = l_version_inter,
1007 FUNC_DIM_SET_OBJ_DEF_ID = l_ver_fdr_obj_def_id, -- "Consistent Dimension"
1008 LINE_HIERARCHY_NAME = l_version_line_name,
1009 LINE_HIERARCHY_DESC = l_version_line_desc,
1010 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1011 LAST_UPDATE_DATE = sysdate,
1012 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
1013 where VERSION_ID = l_to_version_id;
1014
1015 insert into ZPB_BUSAREA_DIMENSIONS
1016 (VERSION_ID,
1017 DIMENSION_ID,
1018 FUNC_DIM_SET_ID, -- "Consistent Dimension"
1019 LOGICAL_DIM_ID, -- "Consistent Dimension"
1020 AW_DIM_NAME, -- "Consistent Dimension"
1021 AW_DIM_PREFIX, -- "Consistent Dimension"
1022 DEFAULT_HIERARCHY_ID,
1023 USE_MEMBER_CONDITIONS,
1024 EPB_LINE_DIMENSION,
1025 LINE_HIERARCHY,
1026 CONDITIONS_INCL_ANC,
1027 CONDITIONS_INCL_DESC,
1028 CREATION_DATE,
1029 CREATED_BY,
1030 LAST_UPDATE_LOGIN,
1031 LAST_UPDATE_DATE,
1032 LAST_UPDATED_BY)
1033 select l_to_version_id,
1034 DIMENSION_ID,
1035 FUNC_DIM_SET_ID, -- "Consistent Dimension"
1036 LOGICAL_DIM_ID,-- "Consistent Dimension"
1037 AW_DIM_NAME, -- "Consistent Dimension"
1038 AW_DIM_PREFIX, -- "Consistent Dimension"
1039 DEFAULT_HIERARCHY_ID,
1040 USE_MEMBER_CONDITIONS,
1041 EPB_LINE_DIMENSION,
1042 LINE_HIERARCHY,
1043 CONDITIONS_INCL_ANC,
1044 CONDITIONS_INCL_DESC,
1045 sysdate,
1046 FND_GLOBAL.USER_ID,
1047 FND_GLOBAL.LOGIN_ID,
1048 sysdate,
1049 FND_GLOBAL.USER_ID
1050 from ZPB_BUSAREA_DIMENSIONS
1051 where VERSION_ID = l_from_version_id;
1052
1053 insert into ZPB_BUSAREA_HIERARCHIES
1054 (VERSION_ID,
1055 LOGICAL_DIM_ID, -- "Consistent Dimension"
1056 HIERARCHY_ID,
1057 KEEP_VERSION,
1058 NUMBER_OF_VERSIONS,
1059 INCLUDE_ALL_TOP_MEMBERS,
1060 INCLUDE_ALL_LEVELS,
1061 CREATION_DATE,
1062 CREATED_BY,
1063 LAST_UPDATE_LOGIN,
1064 LAST_UPDATE_DATE,
1065 LAST_UPDATED_BY)
1066 select l_to_version_id,
1067 LOGICAL_DIM_ID,-- "Consistent Dimension"
1068 HIERARCHY_ID,
1069 KEEP_VERSION,
1070 NUMBER_OF_VERSIONS,
1071 INCLUDE_ALL_TOP_MEMBERS,
1072 INCLUDE_ALL_LEVELS,
1073 sysdate,
1074 FND_GLOBAL.USER_ID,
1078 from ZPB_BUSAREA_HIERARCHIES
1075 FND_GLOBAL.LOGIN_ID,
1076 sysdate,
1077 FND_GLOBAL.USER_ID
1079 where VERSION_ID = l_from_version_id;
1080
1081 insert into ZPB_BUSAREA_HIER_MEMBERS
1082 (VERSION_ID,
1083 LOGICAL_DIM_ID, -- "Consistent Dimension"
1084 HIERARCHY_ID,
1085 MEMBER_ID,
1086 VALUE_SET_ID,
1087 HIER_VERSION_ID,
1088 CREATION_DATE,
1089 CREATED_BY,
1090 LAST_UPDATE_LOGIN,
1091 LAST_UPDATE_DATE,
1092 LAST_UPDATED_BY)
1093 select l_to_version_id,
1094 LOGICAL_DIM_ID,-- "Consistent Dimension"
1095 HIERARCHY_ID,
1096 MEMBER_ID,
1097 VALUE_SET_ID,
1098 HIER_VERSION_ID,
1099 sysdate,
1100 FND_GLOBAL.USER_ID,
1101 FND_GLOBAL.LOGIN_ID,
1102 sysdate,
1103 FND_GLOBAL.USER_ID
1104 from ZPB_BUSAREA_HIER_MEMBERS
1105 where VERSION_ID = l_from_version_id;
1106
1107 insert into ZPB_BUSAREA_HIER_VERSIONS
1108 (VERSION_ID,
1109 LOGICAL_DIM_ID, -- "Consistent Dimension"
1110 HIERARCHY_ID,
1111 HIER_VERSION_ID,
1112 INCLUDE_ALL_TOP_MEMBERS,
1113 CREATION_DATE,
1114 CREATED_BY,
1115 LAST_UPDATE_LOGIN,
1116 LAST_UPDATE_DATE,
1117 LAST_UPDATED_BY)
1118 select l_to_version_id,
1119 LOGICAL_DIM_ID,-- "Consistent Dimension"
1120 HIERARCHY_ID,
1121 HIER_VERSION_ID,
1122 INCLUDE_ALL_TOP_MEMBERS,
1123 sysdate,
1124 FND_GLOBAL.USER_ID,
1125 FND_GLOBAL.LOGIN_ID,
1126 sysdate,
1127 FND_GLOBAL.USER_ID
1128 from ZPB_BUSAREA_HIER_VERSIONS
1129 where VERSION_ID = l_from_version_id;
1130
1131 insert into ZPB_BUSAREA_LEVELS
1132 (VERSION_ID,
1133 LOGICAL_DIM_ID, -- "Consistent Dimension"
1134 HIERARCHY_ID,
1135 LEVEL_ID,
1136 CREATION_DATE,
1137 CREATED_BY,
1138 LAST_UPDATE_LOGIN,
1139 LAST_UPDATE_DATE,
1140 LAST_UPDATED_BY)
1141 select l_to_version_id,
1142 LOGICAL_DIM_ID,-- "Consistent Dimension"
1143 HIERARCHY_ID,
1144 LEVEL_ID,
1145 sysdate,
1146 FND_GLOBAL.USER_ID,
1147 FND_GLOBAL.LOGIN_ID,
1148 sysdate,
1149 FND_GLOBAL.USER_ID
1150 from ZPB_BUSAREA_LEVELS
1151 where VERSION_ID = l_from_version_id;
1152
1153 insert into ZPB_BUSAREA_ATTRIBUTES
1154 (VERSION_ID,
1155 LOGICAL_DIM_ID, -- "Consistent Dimension"
1156 ATTRIBUTE_ID,
1157 CREATION_DATE,
1158 CREATED_BY,
1159 LAST_UPDATE_LOGIN,
1160 LAST_UPDATE_DATE,
1161 LAST_UPDATED_BY)
1162 select l_to_version_id,
1163 LOGICAL_DIM_ID,-- "Consistent Dimension"
1164 ATTRIBUTE_ID,
1165 sysdate,
1166 FND_GLOBAL.USER_ID,
1167 FND_GLOBAL.LOGIN_ID,
1168 sysdate,
1169 FND_GLOBAL.USER_ID
1170 from ZPB_BUSAREA_ATTRIBUTES
1171 where VERSION_ID = l_from_version_id;
1172
1173 insert into ZPB_BUSAREA_CONDITIONS
1174 (VERSION_ID,
1175 LOGICAL_DIM_ID, -- "Consistent Dimension"
1176 ATTRIBUTE_ID,
1177 VALUE,
1178 VALUE_SET_ID,
1179 OPERATION,
1180 CREATION_DATE,
1181 CREATED_BY,
1182 LAST_UPDATE_LOGIN,
1183 LAST_UPDATE_DATE,
1184 LAST_UPDATED_BY)
1185 select l_to_version_id,
1186 LOGICAL_DIM_ID,-- "Consistent Dimension"
1187 ATTRIBUTE_ID,
1188 VALUE,
1189 VALUE_SET_ID,
1190 OPERATION,
1191 sysdate,
1192 FND_GLOBAL.USER_ID,
1193 FND_GLOBAL.LOGIN_ID,
1194 sysdate,
1195 FND_GLOBAL.USER_ID
1196 from ZPB_BUSAREA_CONDITIONS
1197 where VERSION_ID = l_from_version_id;
1198
1199 insert into ZPB_BUSAREA_DATASETS
1200 (VERSION_ID,
1201 DATASET_ID,
1202 CREATION_DATE,
1203 CREATED_BY,
1204 LAST_UPDATE_LOGIN,
1205 LAST_UPDATE_DATE,
1206 LAST_UPDATED_BY)
1207 select l_to_version_id,
1208 DATASET_ID,
1209 sysdate,
1210 FND_GLOBAL.USER_ID,
1211 FND_GLOBAL.LOGIN_ID,
1212 sysdate,
1213 FND_GLOBAL.USER_ID
1214 from ZPB_BUSAREA_DATASETS
1215 where VERSION_ID = l_from_version_id;
1216
1217 insert into ZPB_BUSAREA_LEDGERS
1218 (VERSION_ID,
1219 LEDGER_ID,
1220 CREATION_DATE,
1221 CREATED_BY,
1222 LAST_UPDATE_LOGIN,
1223 LAST_UPDATE_DATE,
1224 LAST_UPDATED_BY)
1225 select l_to_version_id,
1226 LEDGER_ID,
1227 sysdate,
1228 FND_GLOBAL.USER_ID,
1229 FND_GLOBAL.LOGIN_ID,
1230 sysdate,
1231 FND_GLOBAL.USER_ID
1232 from ZPB_BUSAREA_LEDGERS
1233 where VERSION_ID = l_from_version_id;
1234
1235 if (p_to_version_type = 'P') then
1236 update ZPB_BUSINESS_AREAS
1237 set PUBLISH_DATE = sysdate,
1238 PUBLISHED_BY = FND_GLOBAL.USER_ID
1242 set REFRESH_DATE = sysdate,
1239 where BUSINESS_AREA_ID = p_to_busarea_id;
1240 elsif (p_to_version_type = 'R') then
1241 update ZPB_BUSINESS_AREAS
1243 REFRESHED_BY = FND_GLOBAL.USER_ID
1244 where BUSINESS_AREA_ID = p_to_busarea_id;
1245 end if;
1246
1247 return l_to_version_id;
1248 end COPY_VERSION;
1249
1250 -------------------------------------------------------------------------
1251 -- DELETE_BUSINESS_AREA_CR - Submits a conc. req. to delete a Business Area
1252 --
1253 -- IN: p_business_area_id - The Business Area ID
1254 --
1255 -- OUT: concurrent request number
1256 -------------------------------------------------------------------------
1257 FUNCTION DELETE_BUSINESS_AREA_CR (p_business_area_id IN NUMBER)
1258 return NUMBER is
1259 l_ba_name ZPB_BUSAREA_VERSIONS.NAME%type;
1260 l_errbuf VARCHAR2(1000);
1261 l_retcode VARCHAR2(1);
1262 l_retVal NUMBER;
1263
1264 begin
1265 -- update the status field so that that UI knows
1266 -- that this BA is in the process of being deleted
1267 update ZPB_BUSINESS_AREAS
1268 set STATUS = 'D'
1269 where BUSINESS_AREA_ID = p_business_area_id;
1270
1271 begin
1272 select NAME
1273 into l_ba_name
1274 from ZPB_BUSINESS_AREAS_VL
1275 where BUSINESS_AREA_ID = p_business_area_id;
1276 exception
1277 when no_data_found then
1278 l_ba_name := '';
1279 DELETE_BUSINESS_AREA(l_errbuf, l_retcode, p_business_area_id);
1280 l_retVal := 0;
1281 end;
1282
1283 if (length(l_ba_name) > 0)
1284 then
1285 FND_MESSAGE.CLEAR;
1286 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_BUSAREA_DELETE');
1287 FND_MESSAGE.SET_TOKEN('NAME', l_ba_name);
1288 l_retVal := FND_REQUEST.SUBMIT_REQUEST ('ZPB',
1289 'ZPB_BA_DELETE',
1290 FND_MESSAGE.GET,
1291 null,
1292 null,
1293 p_business_area_id);
1294 commit;
1295 end if;
1296
1297 return l_retVal;
1298
1299 end DELETE_BUSINESS_AREA_CR;
1300
1301
1302 -------------------------------------------------------------------------
1303 -- DELETE_BUSINESS_AREA - Deletes a Business Area, including all versions
1304 --
1305 -- IN: p_business_area_id - The Business Area ID
1306 -------------------------------------------------------------------------
1307 PROCEDURE DELETE_BUSINESS_AREA (ERRBUF OUT NOCOPY VARCHAR2,
1308 RETCODE OUT NOCOPY VARCHAR2,
1309 p_business_area_id IN NUMBER)
1310 is
1311 l_snapshot_id ZPB_BUSINESS_AREAS.SNAPSHOT_OBJECT_ID%type;
1312 l_aw ZPB_BUSINESS_AREAS.DATA_AW%type;
1313 l_msg_count NUMBER;
1314 l_msg_data VARCHAR2(1000);
1315 l_ret_status VARCHAR2(1);
1316 l_folder_count NUMBER;
1317 l_refreshed_count NUMBER;
1318
1319 cursor l_versions_curs is
1320 select VERSION_ID
1321 from ZPB_BUSAREA_VERSIONS
1322 where BUSINESS_AREA_ID = p_business_area_id;
1323 l_versions l_versions_curs%ROWTYPE;
1324
1325 cursor l_writeback_tasks_curs is
1326 select TASK_SEQ
1327 from ZPB_WRITEBACK_TASKS
1328 where BUSINESS_AREA_ID = p_business_area_id;
1329 l_tasks l_writeback_tasks_curs%ROWTYPE;
1330
1331 cursor l_cycles_curs is
1332 select ANALYSIS_CYCLE_ID
1333 from ZPB_ANALYSIS_CYCLES
1334 where BUSINESS_AREA_ID = p_business_area_id;
1335 l_cycles l_cycles_curs%ROWTYPE;
1336
1337 cursor l_aws_curs is
1338 select ZPB_AW.GET_SCHEMA||'.'||PERSONAL_AW AW_NAME
1339 from ZPB_USERS
1340 where BUSINESS_AREA_ID = p_business_area_id
1341 UNION
1342 select ZPB_AW.GET_SCHEMA||'.'||DATA_AW AW_NAME
1343 from ZPB_BUSINESS_AREAS
1344 where BUSINESS_AREA_ID = p_business_area_id
1345 UNION
1346 select ZPB_AW.GET_SCHEMA||'.'||ANNOTATION_AW AW_NAME
1347 from ZPB_BUSINESS_AREAS
1348 where BUSINESS_AREA_ID = p_business_area_id
1349 UNION
1350 select ZPB_AW.GET_SCHEMA||'.SQTEMP'||p_business_area_id from dual;
1351
1352 l_aws l_aws_curs%ROWTYPE;
1353
1354 cursor l_session_curs(l_aw_name VARCHAR2) is
1355 select 'alter system kill session '''||s.sid||','||s.serial#||'''' cmd
1356 from v$session s,
1357 v$lock l,
1358 dba_aws a
1359 where l.type='AW' and
1360 l.id1=2 and
1361 l.id2 >= 1000 and
1362 a.aw_number=l.id2 and
1363 s.sid=l.sid and
1364 a.aw_name = l_aw_name and
1365 a.owner = zpb_aw.get_schema;
1366 begin
1367 select SNAPSHOT_OBJECT_ID
1368 into l_snapshot_id
1369 from ZPB_BUSINESS_AREAS
1370 where BUSINESS_AREA_ID = p_business_area_id;
1371
1372 if (l_snapshot_id is not null) then
1373 FEM_FOLDERS_UTL_PKG.ASSIGN_USER_TO_FOLDER
1377 P_WRITE_FLAG => 'Y',
1374 (P_API_VERSION => 1.0,
1375 P_USER_ID => FND_GLOBAL.USER_ID,
1376 P_FOLDER_ID => 1100,
1378 X_MSG_COUNT => l_msg_count,
1379 X_MSG_DATA => ERRBUF,
1380 X_RETURN_STATUS => RETCODE);
1381
1382 FEM_OBJECT_CATALOG_UTIL_PKG.DELETE_OBJECT
1383 (X_MSG_COUNT => l_msg_count,
1384 X_MSG_DATA => ERRBUF,
1385 X_RETURN_STATUS => RETCODE,
1386 P_API_VERSION => 1.0,
1387 P_COMMIT => FND_API.G_FALSE,
1388 P_OBJECT_ID => l_snapshot_id);
1389 end if;
1390
1391 -- b 4616073 finds and purges all workflows for any ACID or Instance for this Business Area
1392
1393 select COUNT(*)
1394 into l_refreshed_count
1395 from ZPB_BUSAREA_VERSIONS
1396 WHERE VERSION_TYPE = 'R'
1397 and BUSINESS_AREA_ID = p_business_area_id;
1398
1399 -- if you're deleting a BA that hasn't been refreshed
1400 -- save yourself a lot of time by skipping work that
1401 -- isn't necessary
1402 if (l_refreshed_count > 0) then
1403 zpb_wfmnt.PurgeWF_BusinessArea(p_business_area_id);
1404
1405 ZPB_OLAP_VIEWS_PKG.REMOVE_BUSAREA_VIEWS(p_business_area_id);
1406
1407 delete from ZPB_STATUS_SQL
1408 where QUERY_PATH like 'oracle/apps/zpb/BusArea'||p_business_area_id||'/%';
1409
1410 delete from ZPB_ACCOUNT_STATES
1411 where BUSINESS_AREA_ID = p_business_area_id;
1412
1413 delete from ZPB_METASCOPE_ATTRIBUTES
1414 where BUSINESS_AREA_ID = p_business_area_id;
1415
1416 delete from ZPB_METASCOPE_HIERARCHIES
1417 where BUSINESS_AREA_ID = p_business_area_id;
1418
1419 delete from ZPB_METASCOPE_LEVELS
1420 where BUSINESS_AREA_ID = p_business_area_id;
1421
1422 delete from ZPB_SHADOW_USERS
1423 where BUSINESS_AREA_ID = p_business_area_id;
1424
1425 for l_tasks in l_writeback_tasks_curs loop
1426 delete from ZPB_WRITEBACK_TRANSACTION
1427 where TASK_SEQ = l_tasks.TASK_SEQ;
1428 end loop;
1429
1430 delete from ZPB_WRITEBACK_TASKS
1431 where BUSINESS_AREA_ID = p_business_area_id;
1432
1433 delete from ZPB_EXCP_RESULTS
1434 where TASK_ID in (select B.TASK_ID from ZPB_ANALYSIS_CYCLES A, ZPB_ANALYSIS_CYCLE_TASKS B
1435 where A.BUSINESS_AREA_ID = p_business_area_id
1436 AND A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID);
1437
1438 for l_cycles in l_cycles_curs loop
1439 delete from ZPB_AC_PARAM_VALUES
1440 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1441 delete from ZPB_ANALYSIS_CYCLE_INSTANCES
1442 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1443 delete from ZPB_ANALYSIS_CYCLE_TASKS
1444 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1445 delete from ZPB_CYCLE_COMMENTS
1446 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1447 delete from ZPB_CYCLE_DATASETS
1448 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1449 delete from ZPB_CYCLE_MODEL_DIMENSIONS
1450 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1451 delete from ZPB_CYCLE_RELATIONSHIPS
1452 where PUBLISHED_AC_ID = l_cycles.ANALYSIS_CYCLE_ID
1453 or EDITABLE_AC_ID = l_cycles.ANALYSIS_CYCLE_ID
1454 or TMP_AC_ID = l_cycles.ANALYSIS_CYCLE_ID;
1455 delete from ZPB_DATA_INITIALIZATION_DEFS
1456 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1457 delete from ZPB_DC_OBJECTS
1458 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID
1459 or AC_INSTANCE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1460 delete from ZPB_SOLVE_ALLOCATION_DEFS
1461 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1462 delete from ZPB_SOLVE_INPUT_LEVELS
1463 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1464 delete from ZPB_SOLVE_MEMBER_DEFS
1465 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1466 delete from ZPB_SOLVE_OUTPUT_LEVELS
1467 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1468 delete from ZPB_SOLVE_PROCESS_MAPS
1469 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1470 delete from ZPB_SOLVE_PROCESS_MEMBERS
1471 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1472 delete from ZPB_SOLVE_STEP_DIMHIERS
1473 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1474 delete from ZPB_VIEW_LIST
1475 where INSTANCE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1476 delete from ZPB_METASCOPE_CONTROLLEDCALCS
1477 where INSTANCE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1478 delete from ZPB_ANALYSIS_CYCLES
1479 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1480 delete from ZPB_BUSINESS_PROCESS_SCOPE
1481 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1482 delete from ZPB_CYCLE_CURRENCIES
1483 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1484 delete from ZPB_SOLVE_INPUT_SELECTIONS
1485 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1486 delete from ZPB_SOLVE_OUTPUT_SELECTIONS
1487 where ANALYSIS_CYCLE_ID = l_cycles.ANALYSIS_CYCLE_ID;
1488 end loop;
1489
1493 into l_aw
1490 ZPB_METADATA_PKG.CLEANBUSAREA(p_business_area_id);
1491
1492 select DATA_AW
1494 from ZPB_BUSINESS_AREAS
1495 where BUSINESS_AREA_ID = p_business_area_id;
1496
1497 ZPB_BUILD_METADATA.DROP_CWM2_METADATA(l_aw);
1498
1499 for l_aws in l_aws_curs loop
1500 for each in l_session_curs(l_aws.AW_NAME) loop
1501 execute immediate each.cmd;
1502 end loop;
1503 begin
1504 ZPB_AW.EXECUTE ('aw delete '||l_aws.AW_NAME);
1505 exception
1506 when others then
1507 ZPB_LOG.LOG_PLSQL_EXCEPTION
1508 ('zpb_busarea_maint.delete_business_area', 4);
1509 end;
1510 end loop;
1511
1512 end if; -- end if refreshed
1513
1514 for l_versions in l_versions_curs loop
1515 CLEAR_VERSION (l_versions.VERSION_ID);
1516 delete from ZPB_BUSAREA_VERSIONS
1517 where VERSION_ID = l_versions.VERSION_ID;
1518 end loop;
1519
1520 delete from ZPB_BUSAREA_COMMENTS
1521 where BUSINESS_AREA_ID = p_business_area_id;
1522
1523 delete from ZPB_MEASURE_SCOPE_EXEMPT_USERS
1524 where USER_ID in (select A.USER_ID from ZPB_MEASURE_SCOPE_EXEMPT_USERS A, ZPB_USERS B
1525 where B.BUSINESS_AREA_ID = p_business_area_id AND A.USER_ID = B.USER_ID);
1526
1527 delete from ZPB_USERS
1528 where BUSINESS_AREA_ID = p_business_area_id;
1529
1530 delete from ZPB_BUSAREA_USERS
1531 where BUSINESS_AREA_ID = p_business_area_id;
1532
1533 delete from ZPB_BUSINESS_AREAS
1534 where BUSINESS_AREA_ID = p_business_area_id;
1535
1536 -- Bug 5007134
1537 -- Delete the business area path in the bibeans repository
1538 -- Bug 5068930 - but only if the BA has been repos to delete
1539 begin
1540 select count(*)
1541 into l_folder_count
1542 from BISM_OBJECTS
1543 where OBJECT_NAME = 'BusArea'||p_business_area_id and
1544 OBJECT_TYPE_ID = 100;
1545 exception
1546 when no_data_found then
1547 l_folder_count := 0;
1548 end;
1549
1550 if (l_folder_count > 0) then
1551 zpb_bism.delete_bism_folder_wo_security('oracle/apps/zpb/BusArea' ||
1552 p_business_area_id, FND_GLOBAL.USER_ID);
1553 end if;
1554
1555 end DELETE_BUSINESS_AREA;
1556
1557 -------------------------------------------------------------------------
1558 -- LOGIN - Called when a user logs in to a Business Area
1559 --
1560 -- IN: p_business_area_id - The Business Area that the user logged in
1561 -- under
1562 -----------------------------------------------------------------------
1563 PROCEDURE LOGIN (p_business_area_id IN NUMBER)
1564 is
1565 begin
1566 update ZPB_USERS
1567 set LAST_BUSAREA_LOGIN = 'N',
1568 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1569 LAST_UPDATE_DATE = sysdate
1570 where USER_ID = FND_GLOBAL.USER_ID
1571 and BUSINESS_AREA_ID <> p_business_area_id;
1572
1573 update ZPB_USERS
1574 set LAST_BUSAREA_LOGIN = 'Y',
1575 LAST_LOGIN_DATE = sysdate,
1576 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1577 LAST_UPDATE_DATE = sysdate,
1578 SHADOW_ID = FND_GLOBAL.USER_ID
1579 where USER_ID = FND_GLOBAL.USER_ID
1580 and BUSINESS_AREA_ID = p_business_area_id;
1581 end LOGIN;
1582
1583 -------------------------------------------------------------------------
1584 -- REFRESH - Submits a conc. req. to refresh a Business Area into EPB
1585 --
1586 -- IN: p_business_area_id - The Business Area ID
1587 -------------------------------------------------------------------------
1588 FUNCTION REFRESH (p_business_area_id IN NUMBER)
1589 return NUMBER is
1590 l_ba_name ZPB_BUSAREA_VERSIONS.NAME%type;
1591 l_desc FND_CONCURRENT_REQUESTS.DESCRIPTION%type;
1592 begin
1593 select NAME
1594 into l_ba_name
1595 from ZPB_BUSINESS_AREAS_VL
1596 where BUSINESS_AREA_ID = p_business_area_id;
1597
1598 FND_MESSAGE.CLEAR;
1599 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_BUSAREA_REFRESH');
1600 FND_MESSAGE.SET_TOKEN('NAME', l_ba_name);
1601 return FND_REQUEST.SUBMIT_REQUEST ('ZPB',
1602 'ZPB_MD_WRTBK',
1603 FND_MESSAGE.GET,
1604 null,
1605 null,
1606 p_business_area_id);
1607 end REFRESH;
1608
1609
1610 -------------------------------------------------------------------------
1611 -- REMOVE_ATTRIBUTE - Removes an attribute from the Business Area version
1612 --
1613 -- IN: p_version_id - The version ID
1614 -- p_logical_dim_id - Logical Dim Id
1615 -- p_attribute_id - The FEM Attribute ID
1616 -------------------------------------------------------------------------
1617 PROCEDURE REMOVE_ATTRIBUTE (p_version_id IN NUMBER,
1618 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
1619 p_attribute_id IN NUMBER)
1620 is
1621 begin
1622 delete from ZPB_BUSAREA_ATTRIBUTES
1623 where VERSION_ID = p_version_id
1627
1624 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1625 and ATTRIBUTE_ID = p_attribute_id;
1626 end REMOVE_ATTRIBUTE;
1628 -------------------------------------------------------------------------
1629 -- REMOVE_CONDITION - Removes an attribute condition from the Business
1630 -- Area version
1631 --
1632 -- IN: p_version_id - The version ID
1633 -- p_logical_dim_id - Logical Dim Id
1634 -- p_attribute_id - The FEM Attribute ID
1635 -- p_value - The attribute value
1636 -- p_value_set_id - The value set ID, for VS-enabled attributes
1637 -- p_operation - The operation of the condition
1638 -------------------------------------------------------------------------
1639 PROCEDURE REMOVE_CONDITION (p_version_id IN NUMBER,
1640 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
1641 p_attribute_id IN NUMBER,
1642 p_value IN VARCHAR2,
1643 p_operation IN VARCHAR2,
1644 p_value_set_id IN NUMBER)
1645
1646 is
1647 begin
1648 if (p_value_set_id is not null) then
1649 delete from ZPB_BUSAREA_CONDITIONS
1650 where VERSION_ID = p_version_id
1651 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1652 and ATTRIBUTE_ID = p_attribute_id
1653 and nvl(TRIM(VALUE), '*') = nvl(p_value, '*')
1654 and VALUE_SET_ID = p_value_set_id
1655 and OPERATION = p_operation;
1656
1657 else
1658 delete from ZPB_BUSAREA_CONDITIONS
1659 where VERSION_ID = p_version_id
1660 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1661 and ATTRIBUTE_ID = p_attribute_id
1662 and nvl(TRIM(VALUE), '*') = nvl(p_value, '*')
1663 and OPERATION = p_operation;
1664 end if;
1665 end REMOVE_CONDITION;
1666 -------------------------------------------------------------------------
1667 -- REMOVE_DATASET - Removes a dataset from the Business Area version
1668 --
1669 -- IN: p_version_id - The version ID
1670 -- p_dataset_id - The FEM Dataset ID
1671 -------------------------------------------------------------------------
1672 PROCEDURE REMOVE_DATASET (p_version_id IN NUMBER,
1673 p_dataset_id IN NUMBER)
1674 is
1675 begin
1676 delete from ZPB_BUSAREA_DATASETS
1677 where VERSION_ID = p_version_id
1678 and DATASET_ID = p_dataset_id;
1679 end REMOVE_DATASET;
1680
1681 -------------------------------------------------------------------------
1682 -- REMOVE_DIMENSION - Removes a dimension from the Business Area version
1683 --
1684 -- IN: p_version_id - The version ID
1685 -- p_logical_dim_id - The FEM Dimension ID
1686 -------------------------------------------------------------------------
1687 PROCEDURE REMOVE_DIMENSION (p_version_id IN NUMBER,
1688 p_logical_dim_id IN NUMBER) -- "Consistent Dimension"
1689 is
1690
1691 -- "Consistent Dimension"
1692 cursor c_dim_hier_curs is
1693 select HIERARCHY_ID
1694 from ZPB_BUSAREA_HIERARCHIES
1695 where VERSION_ID = p_version_id
1696 AND LOGICAL_DIM_ID = p_logical_dim_id;
1697
1698 l_dim_hier c_dim_hier_curs%ROWTYPE;
1699
1700 -- "Consistent Dimension"
1701 cursor c_dim_attr_curs is
1702 select ATTRIBUTE_ID
1703 from ZPB_BUSAREA_ATTRIBUTES
1704 where VERSION_ID = p_version_id
1705 AND LOGICAL_DIM_ID = p_logical_dim_id;
1706
1707 l_dim_attr c_dim_attr_curs%ROWTYPE;
1708
1709 begin
1710 for l_dim_hier in c_dim_hier_curs loop
1711 REMOVE_HIERARCHY (p_version_id,
1712 p_logical_dim_id, -- "Consistent Dimension"
1713 l_dim_hier.HIERARCHY_ID);
1714 end loop;
1715
1716 for l_dim_attr in c_dim_attr_curs loop
1717 REMOVE_ATTRIBUTE (p_version_id,
1718 p_logical_dim_id, -- "Consistent Dimension"
1719 l_dim_attr.ATTRIBUTE_ID);
1720 end loop;
1721
1722 delete from ZPB_BUSAREA_DIMENSIONS
1723 where VERSION_ID = p_version_id
1724 and LOGICAL_DIM_ID = p_logical_dim_id; -- "Consistent Dimension"
1725
1726 end REMOVE_DIMENSION;
1727
1728 -------------------------------------------------------------------------
1729 -- REMOVE_HIERARCHY - Removes a hierarchy from the Business Area version
1730 --
1731 -- IN: p_version_id - The version ID
1732 -- p_logical_dim_id - Logical Dim Id
1733 -- p_hierarchy_id - The FEM Hierarchy ID
1734 -------------------------------------------------------------------------
1735 PROCEDURE REMOVE_HIERARCHY (p_version_id IN NUMBER,
1736 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
1737 p_hierarchy_id IN NUMBER)
1738 is
1739 l_def_hier ZPB_BUSAREA_DIMENSIONS.DEFAULT_HIERARCHY_ID%type;
1740 begin
1741 delete from ZPB_BUSAREA_HIERARCHIES
1742 where VERSION_ID = p_version_id
1743 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1744 and HIERARCHY_ID = p_hierarchy_id;
1745
1746 delete from ZPB_BUSAREA_LEVELS
1750
1747 where VERSION_ID = p_version_id
1748 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1749 and HIERARCHY_ID = p_hierarchy_id;
1751 delete from ZPB_BUSAREA_HIER_MEMBERS
1752 where VERSION_ID = p_version_id
1753 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1754 and HIERARCHY_ID = p_hierarchy_id;
1755
1756 --
1757 -- Clean out the default hierarchy if the removed hier is the def one
1758 --
1759 begin
1760
1761 -- "Consistent Dimension"
1762 select DEFAULT_HIERARCHY_ID
1763 into l_def_hier
1764 from ZPB_BUSAREA_DIMENSIONS
1765 where VERSION_ID = p_version_id
1766 and LOGICAL_DIM_ID = p_logical_dim_id;
1767
1768 exception when NO_DATA_FOUND then
1769 l_def_hier := null;
1770 end;
1771
1772 if (l_def_hier = p_hierarchy_id) then
1773
1774 -- "Consistent Dimension"
1775 update ZPB_BUSAREA_DIMENSIONS
1776 set DEFAULT_HIERARCHY_ID = null
1777 where VERSION_ID = p_version_id
1778 and LOGICAL_DIM_ID = p_logical_dim_id;
1779
1780 end if;
1781
1782 end REMOVE_HIERARCHY;
1783
1784 -------------------------------------------------------------------------
1785 -- REMOVE_HIERARCHY_MEMBER - Removes a top level member to the
1786 -- Business Area version
1787 --
1788 -- IN: p_version_id - The version ID
1789 -- p_logical_dim_id - Logical Dim Id
1790 -- p_hierarchy_id - The FEM Hierarchy ID
1791 -- p_member_id - The FEM member ID
1792 -- p_member_vset - The FEM member valueset ID (defaults to null)
1793 -- p_hier_version - The FEM hierarchy version ID (defaults to null)
1794 -------------------------------------------------------------------------
1795 PROCEDURE REMOVE_HIERARCHY_MEMBER (p_version_id IN NUMBER,
1796 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
1797 p_hierarchy_id IN NUMBER,
1798 p_member_id IN NUMBER,
1799 p_member_vset IN NUMBER,
1800 p_hier_version IN NUMBER := null)
1801 is
1802 begin
1803 if (p_member_vset is not null) then
1804 if (p_hier_version is not null) then
1805 delete from ZPB_BUSAREA_HIER_MEMBERS
1806 where VERSION_ID = p_version_id
1807 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1808 and HIERARCHY_ID = p_hierarchy_id
1809 and MEMBER_ID = p_member_id
1810 and VALUE_SET_ID = p_member_vset
1811 and HIER_VERSION_ID = p_hier_version;
1812 else
1813 delete from ZPB_BUSAREA_HIER_MEMBERS
1814 where VERSION_ID = p_version_id
1815 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1816 and HIERARCHY_ID = p_hierarchy_id
1817 and MEMBER_ID = p_member_id
1818 and VALUE_SET_ID = p_member_vset;
1819 end if;
1820 elsif (p_hier_version is not null) then
1821 delete from ZPB_BUSAREA_HIER_MEMBERS
1822 where VERSION_ID = p_version_id
1823 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1824 and HIERARCHY_ID = p_hierarchy_id
1825 and MEMBER_ID = p_member_id
1826 and HIER_VERSION_ID = p_hier_version;
1827 else
1828 delete from ZPB_BUSAREA_HIER_MEMBERS
1829 where VERSION_ID = p_version_id
1830 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1831 and HIERARCHY_ID = p_hierarchy_id
1832 and MEMBER_ID = p_member_id;
1833 end if;
1834
1835 end REMOVE_HIERARCHY_MEMBER;
1836
1837 -------------------------------------------------------------------------
1838 -- REMOVE_HIERARCHY_VERSION - Removes a hierarchy to the Business Area version
1839 --
1840 -- IN: p_version_id - The version ID
1841 -- p_logical_dim_id - Logical Dim Id
1842 -- p_hierarchy_id - The FEM Hierarchy ID
1843 -- p_hier_vers_id - The FEM Hierarchy Version ID
1844 -------------------------------------------------------------------------
1845 PROCEDURE REMOVE_HIERARCHY_VERSION (p_version_id IN NUMBER,
1846 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
1847 p_hierarchy_id IN NUMBER,
1848 p_hier_vers_id IN NUMBER)
1849 is
1850 begin
1851 delete from ZPB_BUSAREA_HIER_VERSIONS
1852 where VERSION_ID = p_version_id
1853 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1854 and HIERARCHY_ID = p_hierarchy_id
1855 and nvl(HIER_VERSION_ID,-1) = nvl(p_hier_vers_id, -1);
1856 end REMOVE_HIERARCHY_VERSION;
1857
1858 -------------------------------------------------------------------------
1859 -- REMOVE_LEDGER - Removes a ledger from the Business Area version
1860 --
1861 -- IN: p_version_id - The version ID
1862 -- p_ledger_id - The FEM Ledger ID
1863 -------------------------------------------------------------------------
1864 PROCEDURE REMOVE_LEDGER (p_version_id IN NUMBER,
1865 p_ledger_id IN NUMBER)
1866 is
1867 begin
1871 end REMOVE_LEDGER;
1868 delete from ZPB_BUSAREA_LEDGERS
1869 where VERSION_ID = p_version_id
1870 and LEDGER_ID = p_ledger_id;
1872
1873 -------------------------------------------------------------------------
1874 -- REMOVE_LEVEL - Removes a level from the Business Area version
1875 --
1876 -- IN: p_version_id - The version ID
1877 -- p_logical_dim_id - Logical Dim Id
1878 -- p_hierarchy_id - The FEM Hierarchy ID
1879 -- p_level_id - The FEM Level ID
1880 -------------------------------------------------------------------------
1881 PROCEDURE REMOVE_LEVEL (p_version_id IN NUMBER,
1882 p_logical_dim_id IN NUMBER, -- "Consistent Dimension"
1883 p_hierarchy_id IN NUMBER,
1884 p_level_id IN NUMBER)
1885 is
1886 begin
1887 delete from ZPB_BUSAREA_LEVELS
1888 where VERSION_ID = p_version_id
1889 and LOGICAL_DIM_ID = p_logical_dim_id -- "Consistent Dimension"
1890 and HIERARCHY_ID = p_hierarchy_id
1891 and LEVEL_ID = p_level_id;
1892 end REMOVE_LEVEL;
1893
1894 -------------------------------------------------------------------------
1895 -- ADD_USER - Adds a user to the Business Area users table
1896 --
1897 -- IN: p_business_area_id - The business area ID
1898 -- p_user_id - The user ID
1899 -------------------------------------------------------------------------
1900 PROCEDURE ADD_USER (p_business_area_id IN NUMBER,
1901 p_user_id IN NUMBER)
1902 is
1903 begin
1904 insert into ZPB_BUSAREA_USERS
1905 (BUSINESS_AREA_ID,
1906 USER_ID,
1907 CREATION_DATE,
1908 CREATED_BY,
1909 LAST_UPDATE_LOGIN,
1910 LAST_UPDATE_DATE,
1911 LAST_UPDATED_BY)
1912 values
1913 (p_business_area_id,
1914 p_user_id,
1915 sysdate,
1916 FND_GLOBAL.USER_ID,
1917 FND_GLOBAL.LOGIN_ID,
1918 sysdate,
1919 FND_GLOBAL.USER_ID);
1920
1921 end ADD_USER;
1922
1923 -------------------------------------------------------------------------
1924 -- REMOVE_USER - Removes a user from the Business Area users
1925 --
1926 -- IN: p_business_area_id - The version ID
1927 -- p_user_id - The user ID
1928 -------------------------------------------------------------------------
1929 PROCEDURE REMOVE_USER (p_business_area_id IN NUMBER,
1930 p_user_id IN NUMBER)
1931 is
1932 begin
1933 delete from ZPB_BUSAREA_USERS
1934 where BUSINESS_AREA_ID = p_business_area_id
1935 and USER_ID = p_user_id;
1936 end REMOVE_USER;
1937
1938 -------------------------------------------------------------------------
1939 -- FDR_LEDGER_PREPOPULATE - Prepopulates the Ledger for a given FDR
1940 -- - Added for "Consistent Dimension" Project
1941 --
1942 -- IN: p_version_id - The version ID
1943 -- p_fdr_obj_def_id - FDR Object Definition Id
1944 -- p_return_status - return status
1945 -------------------------------------------------------------------------
1946 PROCEDURE FDR_LEDGER_PREPOPULATE (p_version_id IN NUMBER,
1947 p_fdr_obj_def_id IN NUMBER,
1948 p_return_status OUT NOCOPY VARCHAR2)
1949 IS
1950
1951 l_ledger_id NUMBER;
1952 l_gvsc_attr_id NUMBER;
1953
1954 CURSOR c_get_frd_ledgers IS
1955 select distinct DATA_LOC.LEDGER_ID
1956 from FEM_FUNC_DIM_SET_MAPS FDR_MAP,
1957 FEM_FUNC_DIM_SETS_B FDR_SET,
1958 FEM_DATA_LOCATIONS DATA_LOC,
1959 FEM_OBJECT_CATALOG_B OBJ,
1960 FEM_OBJECT_DEFINITION_B OBJ_DEF,
1961 FEM_LEDGERS_ATTR LEDGER_ATTR
1962 where FDR_MAP.FUNC_DIM_SET_ID = FDR_SET.FUNC_DIM_SET_ID
1963 and FDR_SET.FUNC_DIM_SET_OBJ_DEF_ID = p_fdr_obj_def_id
1964 and DATA_LOC.TABLE_NAME = FDR_MAP.TABLE_NAME
1965 and OBJ_DEF.OBJECT_DEFINITION_ID = FDR_SET.FUNC_DIM_SET_OBJ_DEF_ID
1966 and OBJ.OBJECT_ID = OBJ_DEF.OBJECT_ID
1967 and LEDGER_ATTR.LEDGER_ID = DATA_LOC.LEDGER_ID
1968 and LEDGER_ATTR.ATTRIBUTE_ID = l_gvsc_attr_id
1969 and LEDGER_ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER = OBJ.LOCAL_VS_COMBO_ID
1970 and NOT EXISTS (select BA_LEDGER.LEDGER_ID
1971 from ZPB_BUSAREA_LEDGERS BA_LEDGER
1972 where BA_LEDGER.VERSION_ID = p_version_id
1973 and BA_LEDGER.LEDGER_ID = DATA_LOC.LEDGER_ID);
1974
1975 BEGIN
1976
1977 -- The attribute Id of GLOBAL_VS_COMBO attribute
1978 -- belonging to the LEDGER dimension is being hard coded here.
1979 l_gvsc_attr_id := 10047;
1980
1981 p_return_status := 'F';
1982
1983 For c_get_frd_ledgers_rec in c_get_frd_ledgers loop
1984
1985 l_ledger_id := c_get_frd_ledgers_rec.ledger_id;
1986 ADD_LEDGER(p_version_id, l_ledger_id);
1987
1988 end loop;
1989
1990 p_return_status := 'S';
1991
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 p_return_status:= 'F';
1995
1996 END FDR_LEDGER_PREPOPULATE;
1997
1998
1999
2000 -------------------------------------------------------------------------
2001 -- FDR_DIM_PREPOPULATE - Prepopulates the Dimensions for a given FDR
2005 -- p_fdr_obj_def_id - FDR Object Definition Id
2002 -- - Added for "Consistent Dimension" Project
2003 --
2004 -- IN: p_version_id - The version ID
2006 -- p_return_status - return status
2007 -------------------------------------------------------------------------
2008 PROCEDURE FDR_DIM_PREPOPULATE (p_version_id IN NUMBER,
2009 p_fdr_obj_def_id IN NUMBER,
2010 p_return_status OUT NOCOPY VARCHAR2)
2011 IS
2012
2013 l_func_dim_set_id NUMBER;
2014 l_dimension_id NUMBER;
2015 l_invalid_dims NUMBER;
2016 l_count NUMBER;
2017 l_invalid_dim_list VARCHAR2(500);
2018
2019 CURSOR c_get_fdr_dims IS
2020 select FUNC_DIM_SET_ID, DIMENSION_ID
2021 from FEM_FUNC_DIM_SETS_B
2022 where FUNC_DIM_SET_OBJ_DEF_ID = p_fdr_obj_def_id;
2023
2024 CURSOR c_get_invalid_dims IS
2025 select A.FUNC_DIM_SET_ID, A.DIMENSION_ID, A.FUNC_DIM_SET_NAME, B.DESCRIPTION
2026 from FEM_FUNC_DIM_SETS_VL A, FEM_XDIM_DIMENSIONS_VL B
2027 where A.FUNC_DIM_SET_OBJ_DEF_ID = p_fdr_obj_def_id
2028 and A.DIMENSION_ID in (2, 5, 6, 112, 113)
2029 and A.DIMENSION_ID = B.DIMENSION_ID;
2030
2031 BEGIN
2032 p_return_status := 'F';
2033 l_invalid_dims := 0;
2034
2035 For c_get_invalid_dims_rec in c_get_invalid_dims loop
2036
2037 l_invalid_dims := 1;
2038 l_invalid_dim_list := l_invalid_dim_list || c_get_invalid_dims_rec.DESCRIPTION || ', ';
2039 End loop;
2040 -- strip off extra comma if there is one
2041 if (l_invalid_dims = 1) then
2042 -- if the last character of list of dimensions is a comma, get rid of it
2043 if (substr(l_invalid_dim_list, length(l_invalid_dim_list)) = ',') then
2044 l_invalid_dim_list := substr(l_invalid_dim_list, 1, length(l_invalid_dim_list)-1);
2045 end if;
2046 p_return_status := 'F:ZPB_BA_INV_FDR_SUPDIM:' || l_invalid_dim_list;
2047 end if;
2048 if(l_invalid_dims = 0) then
2049
2050 For c_get_fdr_dims_rec in c_get_fdr_dims loop
2051
2052 l_func_dim_set_id := c_get_fdr_dims_rec.FUNC_DIM_SET_ID;
2053 l_dimension_id := c_get_fdr_dims_rec.DIMENSION_ID;
2054
2055 select count(*)
2056 into l_count
2057 from ZPB_BUSAREA_DIMENSIONS
2058 where DIMENSION_ID = l_dimension_id
2059 and VERSION_ID = p_version_id;
2060
2061 -- if the dimension is not already in the BA, add it
2062 -- if it is already there then do a test
2063 -- if the dimension occurs twice in the FDR (not a 1-to-1 mapping)
2064 -- then copy the dimensions into the BA
2065 -- else update ZPB_BUSAREA_DIMENSIONS to have that dim point to the FDR
2066 if (l_count = 0) then
2067 ADD_DIMENSION(p_version_id,
2068 l_func_dim_set_id,
2069 l_dimension_id);
2070 else
2071 select count(*)
2072 into l_count
2073 from FEM_FUNC_DIM_SETS_B
2074 where FUNC_DIM_SET_OBJ_DEF_ID = p_fdr_obj_def_id
2075 and DIMENSION_ID = l_dimension_id;
2076
2077 if (l_count > 1) then
2078 ADD_DIMENSION(p_version_id,
2079 l_func_dim_set_id,
2080 l_dimension_id);
2081 else
2082 update ZPB_BUSAREA_DIMENSIONS
2083 set FUNC_DIM_SET_ID = l_func_dim_set_id
2084 where VERSION_ID = p_version_id
2085 and DIMENSION_ID = l_dimension_id;
2086
2087 end if;
2088
2089 end if;
2090
2091 end loop;
2092
2093 p_return_status := 'S';
2094
2095 end if;
2096
2097 EXCEPTION
2098 WHEN OTHERS THEN
2099 p_return_status:= 'F';
2100
2101 END FDR_DIM_PREPOPULATE;
2102
2103
2104
2105 -------------------------------------------------------------------------
2106 -- FDR_DATASET_PREPOPULATE - Prepopulates the Dimensions for a given FDR
2107 -- - Added for "Consistent Dimension" Project
2108 --
2109 -- IN: p_version_id - The version ID
2110 -- p_fdr_obj_def_id - FDR Object Definition Id
2111 -- p_return_status - return status
2112 -------------------------------------------------------------------------
2113 PROCEDURE FDR_DATASET_PREPOPULATE (p_version_id IN NUMBER,
2114 p_fdr_obj_def_id IN NUMBER,
2115 p_return_status OUT NOCOPY VARCHAR2)
2116 IS
2117
2118 l_dataset_id NUMBER;
2119
2120 CURSOR c_get_frd_datasets IS
2121 select distinct DATA_LOC.DATASET_CODE
2122 from FEM_FUNC_DIM_SET_MAPS FDR_MAP,
2123 FEM_FUNC_DIM_SETS_B FDR_SET,
2124 FEM_DATA_LOCATIONS DATA_LOC
2125 where FDR_MAP.FUNC_DIM_SET_ID = FDR_SET.FUNC_DIM_SET_ID
2126 and FDR_SET.FUNC_DIM_SET_OBJ_DEF_ID = p_fdr_obj_def_id
2127 and DATA_LOC.TABLE_NAME = FDR_MAP.TABLE_NAME
2128 and NOT EXISTS (select BA_DS.DATASET_ID
2129 from ZPB_BUSAREA_DATASETS BA_DS
2130 where BA_DS.VERSION_ID = p_version_id
2131 and BA_DS.DATASET_ID = DATA_LOC.DATASET_CODE);
2132 BEGIN
2133
2134 p_return_status := 'F';
2135
2136 For c_get_frd_datasets_rec in c_get_frd_datasets loop
2137
2138 l_dataset_id := c_get_frd_datasets_rec.DATASET_CODE;
2139
2140 ADD_DATASET(p_version_id, l_dataset_id);
2141
2145
2142 end loop;
2143
2144 p_return_status := 'S';
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 p_return_status:= 'F';
2149
2150 END FDR_DATASET_PREPOPULATE;
2151
2152 -------------------------------------------------------------------------
2153 -- FDR_PREPOPULATE - Prepopulates the Ledgers, Dimensions and Datasets
2154 -- for a given FDR
2155 -- - Added for "Consistent Dimension" Project
2156 --
2157 -- IN: p_version_id - The version ID
2158 -- p_fdr_obj_def_id - FDR Object Definition Id
2159 -- p_return_status - return status
2160 -------------------------------------------------------------------------
2161 PROCEDURE FDR_PREPOPULATE (p_version_id IN NUMBER,
2162 p_fdr_obj_def_id IN NUMBER,
2163 p_return_status OUT NOCOPY VARCHAR2)
2164
2165 IS
2166
2167 l_prepop_ledger_status VARCHAR2(500);
2168 l_prepop_dim_status VARCHAR2(500);
2169 l_prepop_dataset_status VARCHAR2(500);
2170
2171 BEGIN
2172
2173 p_return_status := 'F';
2174
2175 FDR_LEDGER_PREPOPULATE(p_version_id,
2176 p_fdr_obj_def_id,
2177 l_prepop_ledger_status);
2178 if (l_prepop_ledger_status <> 'S') then
2179 p_return_status := l_prepop_ledger_status;
2180 return;
2181 end if;
2182
2183 FDR_DIM_PREPOPULATE(p_version_id,
2184 p_fdr_obj_def_id,
2185 l_prepop_dim_status);
2186 if (l_prepop_dim_status <> 'S') then
2187 p_return_status := l_prepop_dim_status;
2188 return;
2189 end if;
2190
2191 FDR_DATASET_PREPOPULATE(p_version_id,
2192 p_fdr_obj_def_id,
2193 l_prepop_dataset_status);
2194 if (l_prepop_dataset_status <> 'S') then
2195 p_return_status := l_prepop_dataset_status;
2196 return;
2197 end if;
2198
2199 if(l_prepop_ledger_status = 'S' AND
2200 l_prepop_dim_status = 'S' AND
2201 l_prepop_dataset_status= 'S') then
2202
2203 p_return_status := 'S';
2204
2205 end if;
2206
2207 EXCEPTION
2208 WHEN OTHERS THEN
2209 p_return_status:= 'F';
2210
2211 END FDR_PREPOPULATE;
2212
2213
2214 -------------------------------------------------------------------------
2215 -- HANDLE_FDR_REMOVAL - Handles Removal of a FDR from a BA definition
2216 -- - Added for "Consistent Dimension" Project
2217 --
2218 -- IN: p_version_id - The version ID
2219 -- p_return_status - return status
2220 -------------------------------------------------------------------------
2221 PROCEDURE HANDLE_FDR_REMOVAL (p_version_id IN NUMBER,
2222 p_return_status OUT NOCOPY VARCHAR2)
2223 IS
2224
2225 l_dup_dims_exists NUMBER;
2226
2227 CURSOR c_get_dup_dims IS
2228 select BA_DIMS.DIMENSION_ID, count(BA_DIMS.DIMENSION_ID)
2229 from
2230 ZPB_BUSAREA_DIMENSIONS BA_DIMS
2231 where BA_DIMS.VERSION_ID = p_version_id
2232 group by BA_DIMS.DIMENSION_ID
2233 having count(BA_DIMS.DIMENSION_ID) > 1;
2234
2235
2236 BEGIN
2237
2238 -- NOTE: we could set this up to allow removal of an FDR
2239 -- if there is no refreshed version even if there
2240 -- are dimensions that are dupes. We could
2241 -- just remove all the dimensions that are in the FDR
2242 l_dup_dims_exists := -99;
2243 p_return_status := 'F';
2244
2245 for c_get_dup_dims_rec in c_get_dup_dims loop
2246 l_dup_dims_exists := c_get_dup_dims_rec.DIMENSION_ID;
2247 exit;
2248 end loop;
2249
2250 if (l_dup_dims_exists = -99) then
2251
2252 update ZPB_BUSAREA_VERSIONS
2253 set FUNC_DIM_SET_OBJ_DEF_ID = NULL
2254 where VERSION_ID = p_version_id;
2255
2256 update ZPB_BUSAREA_DIMENSIONS
2257 set FUNC_DIM_SET_ID = NULL
2258 where VERSION_ID = p_version_id;
2259
2260 p_return_status := 'S';
2261
2262 else
2263 p_return_status := 'F:ZPB_BA_INV_FDR_NOREM';
2264
2265 end if;
2266
2267
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 p_return_status:= 'F';
2271
2272 END HANDLE_FDR_REMOVAL;
2273
2274
2275 -------------------------------------------------------------------------
2276 -- HANDLE_FDR_CHANGES - Handles changes in the FDR of a BA
2277 -- - Added for "Consistent Dimension" Project
2278 --
2279 -- IN: p_version_id - The version ID
2280 -- p_fdr_obj_def_id_old - Old FDR Object Definition Id
2281 -- p_fdr_obj_def_id_new - New FDR Object Definition Id
2282 -- p_return_status - return status
2283 -------------------------------------------------------------------------
2284 PROCEDURE HANDLE_FDR_CHANGES (p_version_id IN NUMBER,
2285 p_fdr_obj_def_id_old IN NUMBER,
2286 p_fdr_obj_def_id_new IN NUMBER,
2287 p_return_status OUT NOCOPY VARCHAR2)
2288 IS
2289
2290 BEGIN
2291
2292 p_return_status := 'F';
2293
2294 if ((p_fdr_obj_def_id_old is null) AND
2295 (p_fdr_obj_def_id_new is null)) then
2296
2297 p_return_status := 'S';
2298
2299 elsif ((p_fdr_obj_def_id_old is null) AND
2300 (p_fdr_obj_def_id_new is not null)) then
2301
2302 FDR_PREPOPULATE(p_version_id,
2303 p_fdr_obj_def_id_new,
2304 p_return_status);
2305
2306 elsif ((p_fdr_obj_def_id_old is not null) AND
2307 (p_fdr_obj_def_id_new is null)) then
2308
2309 HANDLE_FDR_REMOVAL(p_version_id, p_return_status);
2310
2311 else
2312
2313 if (p_fdr_obj_def_id_old = p_fdr_obj_def_id_new) then
2314
2315 p_return_status := 'S';
2316
2317 else
2318
2319 HANDLE_FDR_REMOVAL(p_version_id, p_return_status);
2320
2321 if (p_return_status = 'S') then
2322
2323 FDR_PREPOPULATE(p_version_id,
2324 p_fdr_obj_def_id_new,
2325 p_return_status);
2326 end if;
2327
2328 end if;
2329 end if;
2330
2331 EXCEPTION
2332 WHEN OTHERS THEN
2333 p_return_status:= 'F';
2334
2335 END HANDLE_FDR_CHANGES;
2336
2337
2338 -------------------------------------------------------------------------
2339 -- GENERATE_AW_DIM_NAME - Generates the AW name of a dimension
2340 -- - Added for "Consistent Dimension" Project
2341 --
2342 -- IN: p_dim_type_code - FEM Dimension Type Code
2343 -- p_member_b_table - FEM XDIM Member B Table
2344 -- OUT: p_aw_dim_name - ZPB AW Dimension Name
2345 -------------------------------------------------------------------------
2346 PROCEDURE GENERATE_AW_DIM_NAME (p_dim_type_code IN VARCHAR2,
2350
2347 p_member_b_table IN VARCHAR2,
2348 p_aw_dim_name OUT NOCOPY VARCHAR2)
2349 IS
2351 l_aw_dim_name VARCHAR2(30);
2352 l_length NUMBER;
2353 l_schema VARCHAR2(30);
2354
2355 BEGIN
2356
2357 if(p_dim_type_code = 'LINE') then
2358
2359 l_aw_dim_name := 'LINE_ITEMS';
2360
2361 else
2362
2363 l_aw_dim_name := p_member_b_table;
2364 l_schema := zpb_aw.get_schema;
2365
2366 if (instr(l_aw_dim_name, l_schema) = 1) then
2367 l_aw_dim_name := substr(l_aw_dim_name,5);
2368 end if;
2369
2370 if (instr(l_aw_dim_name, 'FEM_') = 1) then
2371 l_aw_dim_name := substr(l_aw_dim_name,5);
2372 end if;
2373
2374 l_length := length(l_aw_dim_name);
2375
2376 if (instr(l_aw_dim_name,'_B') = (l_length - 1)) then
2377
2378 l_aw_dim_name := substr(l_aw_dim_name,1,l_length - 2);
2379
2380 elsif (instr(l_aw_dim_name,'_VL') = (l_length - 2)) then
2381
2382 l_aw_dim_name := substr(l_aw_dim_name,1,l_length - 3);
2383
2384 end if;
2385
2386 end if;
2387
2388 p_aw_dim_name := l_aw_dim_name;
2389
2390 EXCEPTION
2391 WHEN OTHERS THEN
2392 null;
2393
2394 END GENERATE_AW_DIM_NAME;
2395
2396 END ZPB_BUSAREA_MAINT;
[Home] [Help]