[Home] [Help]
PACKAGE BODY: APPS.ZPB_DHMINTERFACE_GRP
Source
1 package body ZPB_DHMInterface_GRP as
2 /* $Header: ZPBGDHMB.pls 120.19 2007/12/04 14:35:10 mbhat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ZPB_DHMInterface_GRP';
5
6 TYPE epb_cur_type is REF CURSOR;
7
8 procedure convert_name (x_name IN OUT NOCOPY VARCHAR2)
9 is
10 i number;
11 j number;
12 nl varchar2(1) := fnd_global.local_chr(10);
13 begin
14 if (x_name is not null) then
15 i := 1;
16 j := instr (x_name, '''');
17 loop
18 exit when j=0;
19 x_name := substr(x_name, 1, j-1)||'\'||substr(x_name, j);
20 i := j+2;
21 j := instr (x_name, '''', i);
22 end loop;
23 -- convert carriage return to space
24 x_name := replace(x_name, nl, '\n');
25 end if;
26 end convert_name;
27
28 --
29 -- Get_Business_Area_Info
30 --
31 procedure Get_Business_Area_Info
32 (x_business_area_id OUT NOCOPY ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type,
33 x_ledger_id OUT NOCOPY FEM_LEDGERS_B.LEDGER_ID%type,
34 x_snapshot_id OUT NOCOPY NUMBER)
35 is
36 begin
37 x_business_area_id := sys_context('ZPB_CONTEXT', 'business_area_id');
38
39 select min(LEDGER_ID)
40 into x_ledger_id
41 from ZPB_BUSAREA_LEDGERS A,
42 ZPB_BUSAREA_VERSIONS B
43 where A.VERSION_ID = B.VERSION_ID
44 and B.BUSINESS_AREA_ID = x_business_area_id
45 and B.VERSION_TYPE = 'R';
46
47 select SNAPSHOT_OBJECT_ID
48 into x_snapshot_id
49 from ZPB_BUSINESS_AREAS
50 where BUSINESS_AREA_ID = x_business_area_id;
51 end Get_Business_Area_Info;
52
53 --
54 -- Export_Metadata:
55 --
56 procedure Transfer_To_DHM
57 (p_api_version IN NUMBER,
58 p_init_msg_list IN VARCHAR2,
59 p_commit IN VARCHAR2,
60 p_validation_level IN NUMBER,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2,
64 p_dimension_id IN NUMBER,
65 p_user_id IN NUMBER,
66 p_attr_id IN VARCHAR2)
67 is
68 l_api_name CONSTANT VARCHAR2(30) := 'Export_Dimension';
69 l_api_version CONSTANT NUMBER := 1.0;
70
71 l_dim_table_name VARCHAR2(30); -- Personal dimension table
72 l_dim_mbr_table VARCHAR2(30); -- FEM member table for dim
73 l_dim_mbr_tl_table VARCHAR2(30); -- FEM member transl. table for dim
74 l_dim_hier_table VARCHAR2(30); -- Personal hierarchy table name
75 l_dim_attr_table VARCHAR2(30); -- Dim attribute table name
76 l_dim_column VARCHAR2(30); -- The Dim ID column
77 l_dim_disp_col VARCHAR2(30); -- The display column
78 l_dim_name_col VARCHAR2(30); -- The dim name column
79 l_dim_desc_col VARCHAR2(30); -- The dim description column
80 l_dim_value_sets VARCHAR2(1); -- True if valuesets on dimension
81 l_dim_type VARCHAR2(30); -- The FEM Dimension Type Code
82
83 l_epb_dim VARCHAR2(30); -- The EPB ID (DMENTRY) of the dim
84 l_epb_dim_id VARCHAR2(30); -- The EPB/AttrID of the dim
85 l_dim_view VARCHAR2(30); -- The dim EPB view
86 l_dim_view_col VARCHAR2(30); -- The dim EPB view member column
87 l_dim_gid_col VARCHAR2(30); -- The dim GID column
88 l_dim_pgid_col VARCHAR2(30); -- The dim PGID column
89 l_dim_prnt_col VARCHAR2(30); -- The dim parent column
90 l_dim_lvlrel_col VARCHAR2(30); -- The dim levelRel column
91 l_dim_order_col VARCHAR2(30); -- The dim order column
92 l_aw VARCHAR2(30); -- The personal AW name
93 l_shrdAW VARCHAR2(30); -- The shared AW name
94 l_awQual VARCHAR2(30); -- The fully qualified pers AW name
95
96 l_attributes VARCHAR2(2000); -- List of attributes user can see
97 l_attr VARCHAR2(30); -- The attribute ID in Attrdim
98 l_attr_dimdim VARCHAR2(30); -- The attr ID in the DimDim
99 l_fem_attr NUMBER; -- The FEM attribute ID
100 l_attr_dim_id VARCHAR2(30); -- Attribute Dim ID
101 l_hiers VARCHAR2(2000); -- List of hiers user can see
102 l_hier VARCHAR2(30); -- The hierarchy ID
103 l_hierType VARCHAR2(30); -- The hierarchy type
104 l_levels VARCHAR2(2000); -- List of levels user can see
105 l_level VARCHAR2(30); -- The level ID
106 l_level_type VARCHAR2(30); -- The level Type (TIME only)
107 l_dims VARCHAR2(4000);
108 l_femHier VARCHAR2(30); -- The FEM hierarchy ID
109 l_femHierDef VARCHAR2(30); -- The FEM hier obj definition ID
110 l_folder NUMBER; -- The personal folder of the user
111 l_startDate DATE;
112 l_endDate DATE;
113 l_maxDate DATE;
114 l_value_set_id NUMBER;
115 l_apps_id NUMBER;
116 l_shdw_id NUMBER;
117 l_user_name FND_USER.USER_NAME%type;
118
119 i NUMBER;
120 j NUMBER;
121 k NUMBER;
122 m NUMBER;
123 l_max_gid NUMBER; -- TheLog of the Max GID number
124 l_max_gid2 NUMBER; -- The Max GID number in the ZPB hier view
125
126 l_value VARCHAR2(200);
127 l_value2 VARCHAR2(200);
128 l_command VARCHAR2(4000); -- Stores the dyn. sql statement
129
130 l_calendar_id FEM_HIERARCHIES.CALENDAR_ID%TYPE;
131 l_period_type FEM_HIERARCHIES.PERIOD_TYPE%TYPE;
132 l_multi_top FEM_HIERARCHIES.MULTI_TOP_FLAG%TYPE;
133 l_multi_vs FEM_HIERARCHIES.MULTI_VALUE_SET_FLAG%TYPE;
134
135 l_global_ecm ZPB_ECM.GLOBAL_ECM;
136 l_dim_ecm ZPB_ECM.DIMENSION_ECM;
137 l_dim_data ZPB_ECM.DIMENSION_DATA;
138 l_dim_time_ecm ZPB_ECM.DIMENSION_TIME_ECM;
139 l_dim_line_ecm ZPB_ECM.DIMENSION_LINE_ECM;
140 l_global_attr_ecm ZPB_ECM.GLOBAL_ATTR_ECM;
141
142 l_exp_dim_curs epb_cur_type;
143
144 l_time_dim_grp_key FEM_DIMENSION_GRPS_B.TIME_DIMENSION_GROUP_KEY%type;
145
146
147 l_aw_dim_name ZPB_BUSAREA_DIMENSIONS.AW_DIM_NAME%type;
148
149 cursor l_epb_line_attrs is
150 select distinct A.MEMBER_PRIV_TABLE_NAME,
151 A.MEMBER_B_TABLE_NAME,
152 A.MEMBER_COL,
153 B.ATTRIBUTE_ID,
154 B.ATTRIBUTE_VARCHAR_LABEL
155 from FEM_XDIM_DIMENSIONS A,
156 FEM_DIM_ATTRIBUTES_B B
157 where A.DIMENSION_ID = B.ATTRIBUTE_DIMENSION_ID
158 and B.DIMENSION_ID = 14
159 and B.ATTRIBUTE_VARCHAR_LABEL in
160 ('DEFAULT_AGG_METHOD',
161 'TIME_AGG_METHOD', 'BETTER_FLAG', 'DEFAULT_NUMBER_FORMAT');
162
163 cursor l_epb_time_attrs is
164 select distinct
165 B.ATTRIBUTE_ID,
166 B.ATTRIBUTE_VARCHAR_LABEL
167 from FEM_DIM_ATTRIBUTES_B B
168 where B.DIMENSION_ID = 1
169 and B.ATTRIBUTE_VARCHAR_LABEL in
170 ('CAL_PERIOD_END_DATE', 'CAL_PERIOD_START_DATE');
171
172 cursor l_epb_dim_attrs is
173 select a.ATTRIBUTE_ID,
174 decode (b.DIMENSION_TYPE_CODE, 'LINE', 'L', to_char(a.DIMENSION_ID))
175 DIMENSION_ID
176 from FEM_DIM_ATTRIBUTES_B a,
177 FEM_XDIM_DIMENSIONS b
178 where a.ATTRIBUTE_DIMENSION_ID = p_dimension_id
179 and a.DIMENSION_ID = b.DIMENSION_ID ;
180 begin
181 SAVEPOINT Export_Dimension_Grp;
182
183 if not FND_API.Compatible_API_Call (l_api_version,
184 p_api_version,
185 l_api_name,
186 G_PKG_NAME)
187 then
188 raise FND_API.G_EXC_UNEXPECTED_ERROR;
189 end if;
190
191 if (FND_API.TO_BOOLEAN (p_init_msg_list)) then
192 FND_MSG_PUB.INITIALIZE;
193 end if;
194
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 l_shdw_id := nvl(sys_context('ZPB_CONTEXT', 'shadow_id'),
198 fnd_global.user_id);
199 l_apps_id := nvl(sys_context('ZPB_CONTEXT', 'user_id'), fnd_global.user_id);
200
201 select USER_NAME
202 into l_user_name
203 from FND_USER
204 where USER_ID = l_shdw_id;
205
206 zpb_aw.execute
207 ('PERSONAL!MD.GLBL.CAT (PERSONAL!MD.GLBL.OBJ ''DHM'') = DB.DATE');
208
209 --
210 -- Get the table/column information from the xdim table
211 --
212 select
213 MEMBER_COL,
214 MEMBER_DISPLAY_CODE_COL,
215 MEMBER_B_TABLE_NAME,
216 MEMBER_TL_TABLE_NAME,
217 MEMBER_NAME_COL,
218 MEMBER_DESCRIPTION_COL,
219 ATTRIBUTE_TABLE_NAME,
220 PERSONAL_HIERARCHY_TABLE_NAME,
221 VALUE_SET_REQUIRED_FLAG,
222 MEMBER_PRIV_TABLE_NAME,
223 DIMENSION_TYPE_CODE
224 into
225 l_dim_column,
226 l_dim_disp_col,
227 l_dim_mbr_table,
228 l_dim_mbr_tl_table,
229 l_dim_name_col,
230 l_dim_desc_col,
231 l_dim_attr_table,
232 l_dim_hier_table,
233 l_dim_value_sets,
234 l_dim_table_name,
235 l_dim_type
236 from
237 FEM_XDIM_DIMENSIONS
238 where
239 DIMENSION_ID = p_dimension_id;
240
241 --
242 -- HACK: Waiting to hear from Rob whether this is FEM bug or something
243 -- I need to handle properly:
244 --
245 if (l_dim_table_name is null) then
246 return;
247 end if;
248
249 l_aw := zpb_aw.get_personal_aw(l_shdw_id);
250 l_shrdAw := zpb_aw.get_shared_aw;
251 l_awQual := zpb_aw.get_schema||'.'||l_aw||'!';
252 l_global_ecm := zpb_ecm.get_global_ecm(l_aw);
253 l_global_attr_ecm := zpb_ecm.get_global_attr_ecm(l_aw);
254
255 if (p_dimension_id >= 100 and p_attr_id is null) then
256 for each in l_epb_dim_attrs loop
257 if (zpb_aw.interpbool ('shw isValue('||l_awQual||l_global_ecm.DimDim||
258 ' ''AV.A'||each.attribute_id||'.D'||
259 each.dimension_id||''')')) then
260 l_epb_dim := 'AV.A'||each.attribute_id||'.D'||each.dimension_id;
261 exit;
262 end if;
263 end loop;
264
265 if (l_epb_dim is null) then
266 null;
267 --
268 -- DO SOMETHING!
269 --
270 end if;
271 elsif (p_attr_id is null) then
272 delete from FEM_DIM_ATTRIBUTES_PRIV where USER_ID = l_apps_id;
273
274 if (l_dim_type = 'LINE') then
275 l_epb_dim := zpb_aw.interp('shw lmt ('||l_awQual||l_global_ecm.DimDim
276 ||' to '||l_awQual||l_global_ecm.DimTypeRel
277 ||' eq ''LINE'')');
278 else
279
280
281 ZPB_BUSAREA_MAINT.GENERATE_AW_DIM_NAME(l_dim_type,
282 l_dim_mbr_table,
283 l_aw_dim_name);
284
285 l_epb_dim := zpb_aw.interp('shw lmt ('||l_awQual||l_global_ecm.DimDim
286 ||' to '||l_awQual||l_global_ecm.ExpObjVar
287 ||' eq '''||l_aw_dim_name||''')');
288 end if;
289 l_epb_dim_id := l_epb_dim;
290 else
291 l_epb_dim := p_attr_id;
292 l_epb_dim_id := zpb_aw.interp('shw lmt ('||l_awQual||
293 l_global_ecm.AttrDim||' to '||l_awQual||
294 l_global_attr_ecm.RangeDimRel||' eq '''||
295 p_attr_id||''')');
296 end if;
297
298 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_epb_dim, l_aw);
299 l_dim_data := zpb_ecm.get_dimension_data(l_epb_dim, l_aw);
300 l_dim_view := zpb_metadata_names.get_dimension_view(l_shrdAw,
301 'PERSONAL',
302 l_epb_dim_id);
303
304 l_dim_view_col := zpb_metadata_names.get_dimension_column(l_epb_dim_id);
305
306 if (l_dim_data.Type = 'TIME') then
307 l_dim_time_ecm := zpb_ecm.get_dimension_time_ecm(l_epb_dim, l_aw);
308 elsif (l_dim_data.Type = 'LINE') then
309 l_dim_line_ecm := zpb_ecm.get_dimension_line_ecm(l_epb_dim, l_aw);
310 end if;
311
312 zpb_aw.execute ('push oknullstatus '||l_awQual||l_dim_data.ExpObj||' '||
313 l_awQual||l_global_ecm.LangDim||' commas');
314 zpb_aw.execute ('oknullstatus = yes; commas = no');
315
316 if (l_dim_value_sets = 'Y') then
317 select A.VALUE_SET_ID
318 into l_value_set_id
319 from FEM_GLOBAL_VS_COMBO_DEFS A,
320 FEM_LEDGERS_ATTR C,
321 FEM_DIM_ATTRIBUTES_B D,
322 FEM_DIM_ATTR_VERSIONS_B E
323 where A.DIMENSION_ID = p_dimension_id
324 and A.GLOBAL_VS_COMBO_ID = C.DIM_ATTRIBUTE_NUMERIC_MEMBER
325 and D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
326 and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
327 and E.DEFAULT_VERSION_FLAG = 'Y'
328 and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
329 and C.VERSION_ID = E.VERSION_ID
330 and C.LEDGER_ID = (select min(LEDGER_ID)
331 from ZPB_BUSAREA_LEDGERS A,
332 ZPB_BUSAREA_VERSIONS B
333 where A.VERSION_ID = B.VERSION_ID
334 and B.VERSION_TYPE = 'R'
335 and B.BUSINESS_AREA_ID = sys_context('ZPB_CONTEXT',
336 'business_area_id'));
337 end if;
338 --
339 -- HACK: is this needed? Should this happen in startup?
340 --
341 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LangDim||' to '''||
342 FND_GLOBAL.CURRENT_LANGUAGE||'''');
343 --
344 -- Update the Dimension member security table:
345 --
346 l_command := 'delete from '||l_dim_table_name||' where USER_ID = '||
347 l_apps_id;
348 execute immediate l_command;
349
350 l_command := 'insert into '||l_dim_table_name||' (USER_ID, ';
351 if (l_dim_value_sets = 'Y') then
352 l_command := l_command||'VALUE_SET_ID, ';
353 end if;
354 l_command := l_command||l_dim_column||', CREATION_DATE, CREATED_BY,'||
355 'LAST_UPDATED_BY, LAST_UPDATE_DATE) select '||l_apps_id||', ';
356 if (l_dim_value_sets = 'Y') then
357 l_command := l_command||l_value_set_id||', ';
358 end if;
359 if (upper(l_dim_data.IsDataDim) = 'YES') then
360 l_command := l_command||'substr('||l_dim_view_col||', instr('||
361 l_dim_view_col||', ''_'')+1), ';
362 else
363 l_command := l_command||'substr('||l_dim_view_col||', instr('||
364 l_dim_view_col||', ''_'')+2), ';
365 end if;
366 l_command := l_command||'sysdate, '||
367 l_apps_id||', '||l_apps_id||', sysdate from '||l_dim_view;
368
369 -- BUG 5925855 make sure the dimension members are loaded
370 -- only from the correct value_set_id
371 -- BUG 6348339 only if not an attribute dimension
372 if (l_dim_value_sets = 'Y' and p_attr_id is null) then
373 l_command := l_command||' where substr('||l_dim_view_col||', 1, instr('||
374 l_dim_view_col||', ''_'')-1) = '||l_value_set_id;
375 end if;
376
377 execute immediate l_command;
378
379 --
380 -- Update the Attribute table
381 --
382 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.AttrDim||' to '||
383 l_awQual||l_global_attr_ecm.DomainDimRel||' eq '''||
384 l_epb_dim||'''');
385 l_attributes := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_awQual||
386 l_global_ecm.AttrDim||''' YES)');
387
388 if (l_attributes <> 'NA') then
389 i := 1;
390 loop
391 j := instr (l_attributes, ' ', i);
392 if (j = 0) then
393 l_attr := substr (l_attributes, i);
394 else
395 l_attr := substr (l_attributes, i, j-i);
396 i := j+1;
397 end if;
398
399 --
400 -- Strip off the characters at start of ID to get FEM ID
401 -- Characters are: dim nameFragment + 'A'
402 --
403 -- HACK, the If should go once these attributes are removed
404 --
405 if (instr (l_attr, 'CURTIME') = 0
406 and instr (l_attr, 'LEAFMBR') = 0
407 and instr (l_attr, 'APPVIEW') = 0) then
408 l_fem_attr :=
409 to_number(substr(l_attr, length(l_dim_ecm.NameFragment)+2));
410 --
411 -- Recursively export attribute dimensions this dimension is
412 -- dependent upon:
413 --
414 begin
415 select attribute_dimension_id
416 into l_attr_dim_id
417 from fem_dim_attributes_b
418 where attribute_id = l_fem_attr;
419
420 insert into FEM_DIM_ATTRIBUTES_PRIV
421 (USER_ID,
422 ATTRIBUTE_ID,
423 DIMENSION_ID,
424 CREATION_DATE,
425 CREATED_BY,
426 LAST_UPDATED_BY,
427 LAST_UPDATE_DATE,
428 LAST_UPDATE_LOGIN)
429 values (l_apps_id,
430 l_fem_attr,
431 p_dimension_id,
432 sysdate,
433 l_apps_id,
434 l_apps_id,
435 sysdate,
436 fnd_global.login_id);
437
438 l_attr_dimdim := zpb_aw.interp
439 ('shw '||l_awQual||l_global_attr_ecm.RangeDimRel||
440 ' ('||l_awQual||l_global_ecm.AttrDim||' '''||l_attr||''')');
441
442 if (l_attr_dim_id is not null) then
443 Transfer_To_DHM(1.0,
444 p_init_msg_list,
445 p_commit,
446 p_validation_level,
447 x_return_status,
448 x_msg_count,
449 x_msg_data,
450 l_attr_dim_id,
451 l_apps_id,
452 l_attr_dimdim);
453 end if;
454 exception
455 when no_data_found then
456 --
457 -- Means that attribute was deleted from FEM. Cant do much
458 -- Bug 4255373
459 --
460 null;
461 end;
462 end if;
463 exit when j=0;
464 end loop;
465 end if;
466
467 --
468 -- Do the "special" dimension attributes on line. Need to add to both
469 -- the table that states what attributes the user can see, as well as
470 -- add to the table stating what attribute values are visible.
471 --
472 if (l_dim_data.Type = 'LINE') then
473 for each in l_epb_line_attrs loop
474
475 if (each.ATTRIBUTE_VARCHAR_LABEL = 'DEFAULT_AGG_METHOD') then
476
477 l_command := 'delete from '||each.MEMBER_PRIV_TABLE_NAME||
478 ' where USER_ID = '||l_apps_id;
479 execute immediate l_command;
480
481 l_command := 'insert into '||each.MEMBER_PRIV_TABLE_NAME||
482 '(USER_ID, '||each.MEMBER_COL||',CREATION_DATE, CREATED_BY,'||
483 'LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) select '||
484 l_apps_id||','||each.MEMBER_COL||',sysdate,'||l_apps_id||', '||
485 l_apps_id||', sysdate, FND_GLOBAL.LOGIN_ID from '||
486 each.MEMBER_B_TABLE_NAME;
487 execute immediate l_command;
488 end if;
489
490 select count(*)
491 into i
492 from FEM_DIM_ATTRIBUTES_PRIV
493 where USER_ID = l_apps_id
494 and ATTRIBUTE_ID = each.ATTRIBUTE_ID;
495
496 if (i = 0) then
497 insert into FEM_DIM_ATTRIBUTES_PRIV
498 (USER_ID,
499 ATTRIBUTE_ID,
500 DIMENSION_ID,
501 CREATION_DATE,
502 CREATED_BY,
503 LAST_UPDATED_BY,
504 LAST_UPDATE_DATE,
505 LAST_UPDATE_LOGIN)
506 values
507 (l_apps_id,
508 each.ATTRIBUTE_ID,
509 14,
510 sysdate,
511 l_apps_id,
512 l_apps_id,
513 sysdate,
514 FND_GLOBAL.LOGIN_ID);
515 end if;
516 end loop;
517 elsif (l_dim_data.Type = 'TIME') then
518 for each in l_epb_time_attrs loop
519
520 select count(*)
521 into i
522 from FEM_DIM_ATTRIBUTES_PRIV
523 where USER_ID = l_apps_id
524 and ATTRIBUTE_ID = each.ATTRIBUTE_ID;
525
526 if (i = 0) then
527 insert into FEM_DIM_ATTRIBUTES_PRIV
528 (USER_ID,
529 ATTRIBUTE_ID,
530 DIMENSION_ID,
531 CREATION_DATE,
532 CREATED_BY,
533 LAST_UPDATED_BY,
534 LAST_UPDATE_DATE,
535 LAST_UPDATE_LOGIN)
536 values
537 (l_apps_id,
538 each.ATTRIBUTE_ID,
539 1,
540 sysdate,
541 l_apps_id,
542 l_apps_id,
543 sysdate,
544 FND_GLOBAL.LOGIN_ID);
545 end if;
546 end loop;
547 end if;
548
549
550 if (l_dim_hier_table is not null and l_dim_ecm.HierDim <> 'NA') then
551
552 l_dim_gid_col := zpb_metadata_names.get_dim_gid_column(l_epb_dim);
553 l_dim_pgid_col := zpb_metadata_names.get_dim_pgid_column(l_epb_dim);
554 l_dim_prnt_col := zpb_metadata_names.get_dim_parent_column(l_epb_dim);
555 l_dim_lvlRel_col := zpb_metadata_names.get_levelrel_column(l_epb_dim);
556 l_dim_order_col := zpb_metadata_names.get_dim_order_column(l_epb_dim);
557 --
558 -- Update the Dimension Group table
559 --
560 delete from FEM_DIMENSION_GRPS_PRIV where USER_ID = l_apps_id;
561
562 insert into FEM_DIMENSION_GRPS_PRIV
563 (USER_ID,
564 DIMENSION_GROUP_ID,
565 DIMENSION_ID,
566 CREATION_DATE,
567 CREATED_BY,
568 LAST_UPDATED_BY,
569 LAST_UPDATE_DATE,
570 LAST_UPDATE_LOGIN)
571 select distinct l_apps_id,
572 to_number(OBJECT_AW_NAME),
573 p_dimension_id,
574 sysdate,
575 l_apps_id,
576 l_apps_id,
577 sysdate,
578 fnd_global.login_id
579 from ZPB_LAB_LEVELS_SCOPE_V
580 where DIMENSION = l_dim_data.ExpObj
581 and OBJECT_SHORT_LABEL not like '%LV_%';
582
583 --
584 -- Update the hierarchy information:
585 --
586 -- Sort to ensure version hierarchies are after effective:
587 --
588 zpb_aw.execute ('sort '||l_awQual||l_dim_ecm.HierDim||' a '||
589 l_awQual||l_dim_ecm.HierDim);
590 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_awQual||
591 l_dim_ecm.HierDim||''')');
592 i := 1;
593 loop
594 exit when l_hiers = 'NA';
595 j := instr (l_hiers, ' ', i);
596 if (j = 0) then
597 l_hier := substr (l_hiers, i);
598 else
599 l_hier := substr (l_hiers, i, j-i);
600 i := j+1;
601 end if;
602
603 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' to '''||
604 l_hier||'''');
605
606 if (zpb_aw.interpbool ('shw exists('''||l_awQual||
607 l_dim_ecm.HierVersLdscVar||''')')) then
608 l_value2 :=zpb_aw.interp('shw '||l_awQual||
609 l_dim_ecm.HierVersLdscVar);
610 else
611 l_value2 := null;
612 end if;
613 if (l_value2 is null or l_value2 = 'NA') then
614 l_femHier :=
615 zpb_aw.interp ('shw '||l_awQual||l_dim_ecm.HierFEMIDVar);
616 else
617 l_femHier :=
618 zpb_aw.interp('shw '||l_awQual||l_dim_ecm.HierFEMIDVar||' ('||
619 l_awQual||l_dim_ecm.HierDim||' '''||
620 substr(l_hier, 1, instr(l_hier, 'V')-1)||''')');
621 if (upper(l_femHier) <> 'NA') then
622 zpb_aw.execute (l_awQual||l_dim_ecm.HierFEMIDVar||' = '''||
623 l_femHier||'''');
624 end if;
625 end if;
626
627 if (upper(l_femHier) = 'NA') then
628 --
629 -- Have to get an ID from FEM for the hierarchy
630 --
631 select to_char(FEM_OBJECT_ID_SEQ.nextVal) into l_femHier from dual;
632 zpb_aw.execute (l_awQual||l_dim_ecm.HierFEMIDVar||' = '''||
633 l_femHier||'''');
634 end if;
635
636 l_femHierDef :=
637 zpb_aw.interp ('shw '||l_awQual||l_dim_ecm.HierFEMDefIDVar);
638
639 if (upper(l_femHierDef) = 'NA') then
640 --
641 -- Have to get an ID from FEM for the hierarchy
642 --
643 select to_char(FEM_OBJECT_DEFINITION_ID_SEQ.nextVal)
644 into l_femHierDef from dual;
645 zpb_aw.execute (l_awQual||l_dim_ecm.HierFEMDefIDVar||' = '''||
646 l_femHierDef||'''');
647 end if;
648
649 if (zpb_aw.interp ('shw '||l_awQual||l_dim_ecm.HierTypeRel)
650 = 'VALUE_BASED') then
651 l_hierType := 'NO_GROUPS';
652 else
653 l_hierType := 'SEQUENCE_ENFORCED_SKIP_LEVEL';
654 end if;
655
656 --
657 -- Insert into the FEM_HIERARCHIES/OBJECT_CATALOG tables:
658 --
659
660 --
661 -- Gets/creates the folder for the user:
662 --
663 FEM_FOLDERS_UTL_PKG.GET_PERSONAL_FOLDER(l_apps_id, l_folder);
664
665 if (l_value2 is null or l_value2 = 'NA') then
666 l_value2 := zpb_aw.interp('shw '||l_awQual||l_dim_ecm.HierLdscVar);
667 FEM_OBJECT_CATALOG_PKG.INSERT_ROW
668 (l_value,
669 to_number(l_femHier),
670 'HIERARCHY',
671 l_folder,
672 null,
673 'W',
674 'USER',
675 1,
676 l_value2||' ('||l_user_name||')',
677 l_value2,
678 sysdate,
679 l_apps_id,
680 sysdate,
681 l_apps_id,
682 fnd_global.login_id);
683 end if;
684
685 --
686 -- Update the Definition table:
687 --
688 if (zpb_aw.interpbool ('shw exists('''||l_awQual||
689 l_dim_ecm.HierVersLdscVar||''')')) then
690 l_value2 := zpb_aw.interp('shw '||l_awQual||
691 l_dim_ecm.HierVersLdscVar);
692 else
693 l_value2 := null;
694 end if;
695 if (l_value2 is null or l_value2 = 'NA') then
696 FND_MESSAGE.CLEAR;
697 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_EFFECTIVE_VERSION');
698 l_value2 := FND_MESSAGE.GET;
699
700 begin
701 select EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
702 into l_startDate, l_endDate
703 from FEM_OBJECT_DEFINITION_B
704 where OBJECT_ID = to_number(l_hier)
705 and EFFECTIVE_START_DATE <= sysdate
706 and EFFECTIVE_END_DATE >= sysdate;
707
708 if (to_number(zpb_aw.interp('shw statlen(lmt('||l_awQual||
709 l_dim_ecm.HierDim||' to findchars('||l_awQual||
710 l_dim_ecm.HierDim||' '''||l_hier||'V'') gt 0))'))>0) then
711 l_startDate := sysdate;
712 end if;
713 exception
714 when others then
715 l_startDate := sysdate-31;
716 l_endDate := sysdate+31;
717 end;
718 else
719 k := substr(l_hier, 1, instr(l_hier, 'V')-1);
720 m := substr(l_hier, instr(l_hier, 'V')+1);
721 begin
722 select EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
723 into l_startDate, l_endDate
724 from FEM_OBJECT_DEFINITION_B
725 where OBJECT_DEFINITION_ID = m;
726 if (l_startDate < sysdate and l_endDate > sysdate) then
727 --
728 -- Need to make room for the effective version:
729 --
730 l_endDate := sysdate-1;
731 end if;
732 exception
733 when no_data_found then
734 l_startDate := null;
735 l_endDate := null;
736 end;
737 end if;
738
739 --
740 -- This should only be the case if the hierarchy was deleted
741 -- from FEM:
742 --
743 if (l_startDate = null) then
744 begin
745 select EFFECTIVE_END_DATE
746 into l_endDate
747 from FEM_OBJECT_DEFINITION_B
748 where OBJECT_ID = to_number(l_femHier);
749 l_startDate := l_endDate+1;
750 l_endDate := l_startDate+1;
751 exception
752 when no_data_found then
753 l_startDate := sysdate;
754 l_endDate := sysdate+1;
755 end;
756 end if;
757 FEM_OBJECT_DEFINITION_PKG.INSERT_ROW
758 (l_value,
759 to_number(l_femHierDef),
760 1,
761 to_number(l_femHier),
762 l_startDate,
763 l_endDate,
764 'USER',
765 'NOT_APPLICABLE',
766 'N',
767 null,
768 null,
769 null,
770 l_value2,
771 l_value2,
772 sysdate,
773 l_apps_id,
774 sysdate,
775 l_apps_id,
776 fnd_global.login_id);
777
778 insert into FEM_HIER_DEFINITIONS
779 (HIERARCHY_OBJ_DEF_ID,
780 CREATION_DATE,
781 CREATED_BY,
782 LAST_UPDATED_BY,
783 LAST_UPDATE_DATE,
784 LAST_UPDATE_LOGIN,
785 OBJECT_VERSION_NUMBER,
786 FLATTENED_ROWS_COMPLETION_CODE)
787 values
788 (to_number(l_femHierDef),
789 sysdate,
790 l_apps_id,
791 l_apps_id,
792 sysdate,
793 fnd_global.login_id,
794 1,
795 'COMPLETED');
796
797 --
798 -- FEM_HIER_VALUE_SETS
799 --
800 if (zpb_aw.interpbool ('shw exists('''||l_awQual||
801 l_dim_ecm.HierVersLdscVar||''')')) then
802 l_value2 := zpb_aw.interp('shw '||l_awQual||
803 l_dim_ecm.HierVersLdscVar);
804 else
805 l_value2 := null;
806 end if;
807 if (l_value2 is null or l_value2 = 'NA') then
808 if (l_dim_value_sets = 'Y') then
809 l_command :=
810 'insert into FEM_HIER_VALUE_SETS
811 (HIERARCHY_OBJ_ID,
812 VALUE_SET_ID,
813 CREATION_DATE,
814 CREATED_BY,
815 LAST_UPDATED_BY,
816 LAST_UPDATE_DATE,
817 LAST_UPDATE_LOGIN,
818 OBJECT_VERSION_NUMBER)
819 select distinct '||l_femHier||',
820 '||l_value_set_id||',
821 sysdate,
822 '||l_apps_id||',
823 '||l_apps_id||',
824 sysdate,
825 fnd_global.login_id,
826 1
827 from '||l_dim_view;
828
829 execute immediate l_command;
830 end if;
831
832 --
833 -- Insert into FEM_HIERARCHIES:
834 --
835 -- Go against original FEM for some information.
836 -- If it does not exist, then fill in with default
837 --
838 begin
839 select
840 MULTI_TOP_FLAG,
841 CALENDAR_ID,
842 PERIOD_TYPE,
843 MULTI_VALUE_SET_FLAG
844 into l_multi_top,
845 l_calendar_id,
846 l_period_type,
847 l_multi_vs
848 from FEM_HIERARCHIES
849 where HIERARCHY_OBJ_ID = to_number(l_hier);
850 exception
851 when others then
852 l_multi_top := 'Y';
853 l_calendar_id := null;
854 l_period_type := null;
855 l_multi_vs := 'Y';
856 end;
857
858 if (l_dim_data.Type = 'TIME' and
859 zpb_aw.interpbool('shw exists('''||l_awQual||
860 l_dim_time_ecm.CalendarVar||''')')) then
861 zpb_aw.execute('push '||l_awQual||l_dim_data.ExpObj);
862 zpb_aw.execute('lmt '||l_awQual||l_dim_data.ExpObj||' to '||
863 l_awQual||l_dim_ecm.HOrderVS);
864 zpb_aw.execute('lmt '||l_awQual||l_dim_data.ExpObj||' keep '||
865 l_awQual||l_dim_ecm.MemberTypeRel||'''SHARED''');
866 zpb_aw.execute('lmt '||l_awQual||l_dim_data.ExpObj||
867 ' keep first 1');
868 l_calendar_id := zpb_aw.interp('shw '||l_awQual||
869 l_dim_time_ecm.CalendarVar);
870 end if;
871
872 insert into FEM_HIERARCHIES
873 (HIERARCHY_OBJ_ID,
874 DIMENSION_ID,
875 HIERARCHY_TYPE_CODE,
876 GROUP_SEQUENCE_ENFORCED_CODE,
877 MULTI_TOP_FLAG,
878 FINANCIAL_CATEGORY_FLAG,
879 VALUE_SET_ID,
880 CALENDAR_ID,
881 PERIOD_TYPE,
882 PERSONAL_FLAG,
883 FLATTENED_ROWS_FLAG,
884 CREATION_DATE,
885 CREATED_BY,
886 LAST_UPDATED_BY,
887 LAST_UPDATE_DATE,
888 LAST_UPDATE_LOGIN,
889 HIERARCHY_USAGE_CODE,
890 MULTI_VALUE_SET_FLAG,
891 OBJECT_VERSION_NUMBER)
892 values
893 (to_number(l_femHier),
894 p_dimension_id,
895 'OPEN',
896 l_hierType,
897 l_multi_top,
898 'N',
899 l_value_set_id,
900 l_calendar_id,
901 l_period_type,
902 'Y',
903 'N',
904 sysdate,
905 l_apps_id,
906 l_apps_id,
907 sysdate,
908 fnd_global.login_id,
909 'PLANNING',
910 l_multi_vs,
911 1);
912 end if;
913
914 --
915 -- Insert into the FEM Personal Hierarchy table:
916 --
917 -- First determine if personal hierarchy needed:
918 --
919 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '||
920 l_awQual||l_dim_ecm.LevelPersVar||' eq YES');
921 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '||
922 l_awQual||l_dim_ecm.HorderVS);
923 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' keep '||
924 l_awQual||l_dim_ecm.LevelRel);
925 if (zpb_aw.interp('shw convert(statlen('||l_awQual||l_dim_data.ExpObj
926 ||') TEXT 0 no no)') <> '0') then
927 l_dim_view := zpb_metadata_names.get_dimension_view (l_aw,
928 'PERSONAL',
929 l_epb_dim_id,
930 l_hier);
931 else
932 l_dim_view := zpb_metadata_names.get_dimension_view (l_shrdAw,
933 'PERSONAL',
934 l_epb_dim_id,
935 l_hier);
936 end if;
937
938 l_command := 'select nvl(round(log(2, max('||l_dim_gid_col||
939 ') + 1))+1,0) gid from '||l_dim_view;
940
941 open l_exp_dim_curs for l_command;
942 fetch l_exp_dim_curs into l_max_gid;
943 close l_exp_dim_curs;
944
945 l_command := 'delete from '||l_dim_hier_table||
946 ' where HIERARCHY_OBJ_DEF_ID = '||to_number(l_femHierDef);
947 execute immediate l_command;
948
949 --
950 -- First populate the non-parent/non-leaf nodes of the tree
951 --
952 l_command := 'insert into '||l_dim_hier_table||'
953 (HIERARCHY_OBJ_DEF_ID,
954 PARENT_DEPTH_NUM,
955 PARENT_ID,
956 CHILD_ID,';
957 if (l_dim_value_sets = 'Y') then
958 l_command := l_command||'PARENT_VALUE_SET_ID, CHILD_VALUE_SET_ID,';
959 end if;
960 l_command := l_command||'
961 CHILD_DEPTH_NUM,
962 SINGLE_DEPTH_FLAG,
963 DISPLAY_ORDER_NUM,
964 CREATION_DATE,
965 CREATED_BY,
966 LAST_UPDATED_BY,
967 LAST_UPDATE_DATE,
968 LAST_UPDATE_LOGIN,
969 OBJECT_VERSION_NUMBER)
970 select '||to_number(l_femHierDef)||', '||
971 l_max_gid||' - round(log(2, '||l_dim_pgid_col||' + 1)), ';
972 if (l_dim_value_sets = 'Y') then
973 l_command := l_command||'
974 substr('||l_dim_prnt_col||', instr('||
975 l_dim_prnt_col||', ''_'')+1),
976 substr('||l_dim_view_col||', instr('||
977 l_dim_view_col||', ''_'')+1), '||
978 l_value_set_id||', '||l_value_set_id||', ';
979 else
980 l_command := l_command||l_dim_prnt_col||', '||
981 l_dim_view_col||', ';
982 end if;
983 l_command := l_command||
984 l_max_gid||' - round(log(2, '||l_dim_gid_col||' + 1)),
985 ''Y'', '||
986 l_dim_order_col||',
987 sysdate, '||
988 l_apps_id||', '||
989 l_apps_id||',
990 sysdate, '||
991 fnd_global.login_id||',
992 1 from '||l_dim_view||
993 ' where '||l_dim_pgid_col||
994 ' is not null and '||l_dim_order_col||' is not null';
995
996 execute immediate l_command;
997
998 --
999 -- Following populates the leaf nodes in the table
1000 --
1001 l_command := 'insert into '||l_dim_hier_table||'
1002 (HIERARCHY_OBJ_DEF_ID,
1003 PARENT_DEPTH_NUM,
1004 PARENT_ID,
1005 CHILD_ID,';
1006 if (l_dim_value_sets = 'Y') then
1007 l_command := l_command||'PARENT_VALUE_SET_ID, CHILD_VALUE_SET_ID,';
1008 end if;
1009 l_command := l_command||'
1010 CHILD_DEPTH_NUM,
1011 SINGLE_DEPTH_FLAG,
1012 DISPLAY_ORDER_NUM,
1013 CREATION_DATE,
1014 CREATED_BY,
1015 LAST_UPDATED_BY,
1016 LAST_UPDATE_DATE,
1017 LAST_UPDATE_LOGIN,
1018 OBJECT_VERSION_NUMBER)
1019 select '||to_number(l_femHierDef)||', '||
1020 l_max_gid||', ';
1021 if (l_dim_value_sets = 'Y') then
1022 l_command := l_command||'
1023 substr('||l_dim_view_col||', instr('||
1024 l_dim_view_col||', ''_'')+1),
1025 substr('||l_dim_view_col||', instr('||
1026 l_dim_view_col||', ''_'')+1), '||
1027 l_value_set_id||', '||l_value_set_id||', ';
1028 else
1029 l_command := l_command||l_dim_view_col||', '||
1030 l_dim_view_col||', ';
1031 end if;
1032 l_command := l_command||
1033 l_max_gid||',
1034 ''N'', '||
1035 l_dim_order_col||',
1036 sysdate, '||
1037 l_apps_id||', '||
1038 l_apps_id||',
1039 sysdate, '||
1040 fnd_global.login_id||',
1041 1 from '||l_dim_view||
1042 ' where '||l_dim_gid_col||' = 0 and '||
1043 l_dim_pgid_col||
1044 ' is not null and '||l_dim_order_col||' is not null';
1045 execute immediate l_command;
1046
1047 --
1048 -- The following populates the top-level nodes in the hierarchy
1049 --
1050 l_command := 'insert into '||l_dim_hier_table||'
1051 (HIERARCHY_OBJ_DEF_ID,
1052 PARENT_DEPTH_NUM,
1053 PARENT_ID,
1054 CHILD_ID,';
1055 if (l_dim_value_sets = 'Y') then
1056 l_command := l_command||'PARENT_VALUE_SET_ID, CHILD_VALUE_SET_ID,';
1057 end if;
1058 l_command := l_command||'
1059 CHILD_DEPTH_NUM,
1060 SINGLE_DEPTH_FLAG,
1061 DISPLAY_ORDER_NUM,
1062 CREATION_DATE,
1063 CREATED_BY,
1064 LAST_UPDATED_BY,
1065 LAST_UPDATE_DATE,
1066 LAST_UPDATE_LOGIN,
1067 OBJECT_VERSION_NUMBER)
1068 select '||to_number(l_femHierDef)||', 1, ';
1069 if (l_dim_value_sets = 'Y') then
1070 l_command := l_command||'
1071 substr('||l_dim_view_col||', instr('||l_dim_view_col||
1072 ', ''_'')+1),
1073 substr('||l_dim_view_col||', instr('||
1074 l_dim_view_col||', ''_'')+1),
1075 '||l_value_set_id||', '||l_value_set_id||', ';
1076 else
1077 l_command := l_command||l_dim_view_col||', '||
1078 l_dim_view_col||', ';
1079 end if;
1080 l_command := l_command||'
1081 1,
1082 ''Y'', '||
1083 l_dim_order_col||',
1084 sysdate, '||
1085 l_apps_id||', '||
1086 l_apps_id||',
1087 sysdate, '||
1088 fnd_global.login_id||',
1089 1 from '||l_dim_view||
1090 ' where '||l_dim_pgid_col||' is null and '||
1091 l_dim_order_col||' is not null';
1092
1093 execute immediate l_command;
1094
1095 exit when j=0;
1096 end loop;
1097 end if;
1098
1099 --
1100 -- Populate the FEM data tables for Personal members:
1101 --
1102 -- First populate the MEMBER_B table
1103 --
1104 l_dim_view := zpb_metadata_names.get_dimension_view (l_shrdAw,
1105 'PERSONAL',
1106 l_epb_dim_id);
1107 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to all');
1108 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' remove '||
1109 l_awQual||l_dim_ecm.MemberTypeRel||' ''SHARED''');
1110
1111 l_dims :=
1112 zpb_aw.interp ('shw joinchars(joincols(charlist ('||l_awQual||
1113 l_dim_data.ExpObj||') ''\'',\''''))');
1114
1115 if (l_dims <> ''',''') then
1116 l_dims := ''''||substr(l_dims, 1, length(l_dims) - 2);
1117
1118 --
1119 -- Special processing on the cal_periods table:
1120 --
1121 if (l_dim_data.Type = 'TIME') then
1122 l_command := 'insert into '||l_dim_mbr_table||'
1123 ('||l_dim_column||',
1124 DIMENSION_GROUP_ID,
1125 CALENDAR_ID,
1126 ENABLED_FLAG,
1127 PERSONAL_FLAG,
1128 CREATION_DATE,
1129 CREATED_BY,
1130 LAST_UPDATED_BY,
1131 LAST_UPDATE_DATE,
1132 LAST_UPDATE_LOGIN,
1133 READ_ONLY_FLAG,
1134 OBJECT_VERSION_NUMBER)
1135 select '||l_dim_view_col||',
1136 '||l_dim_lvlRel_col||',
1137 nvl('||
1138 zpb_metadata_names.get_dim_calendar_column(l_epb_dim_id)||',1),
1139 ''Y'',
1140 ''Y'',
1141 sysdate, '||
1142 l_apps_id||', '||
1143 l_apps_id||',
1144 sysdate, '||
1145 fnd_global.login_id||',
1146 ''N'',
1147 1 from '||l_dim_view||' where '||l_dim_view_col||
1148 ' in ('||l_dims||')';
1149 else
1150 l_command :='insert into '||l_dim_mbr_table||' ('||l_dim_column||', ';
1151 if (l_dim_value_sets = 'Y') then
1152 l_command := l_command||'VALUE_SET_ID, ';
1153 end if;
1154
1155 l_command := l_command||'
1156 DIMENSION_GROUP_ID, '||
1157 l_dim_disp_col||',
1158 ENABLED_FLAG,
1159 PERSONAL_FLAG,';
1160 if (l_dim_data.Type = 'TIME') then
1161 l_command := l_command||' CALENDAR_ID, ';
1162 end if;
1163 l_command := l_command||'
1164 CREATION_DATE,
1165 CREATED_BY,
1166 LAST_UPDATED_BY,
1167 LAST_UPDATE_DATE,
1168 LAST_UPDATE_LOGIN,
1169 READ_ONLY_FLAG,
1170 OBJECT_VERSION_NUMBER)
1171 select ';
1172 if (l_dim_value_sets = 'Y') then
1173 l_command := l_command||'substr('||l_dim_view_col||', instr('||
1174 l_dim_view_col||', ''_'')+1), '||l_value_set_id||', ';
1175 else
1176 l_command := l_command||l_dim_view_col||', ';
1177 end if;
1178
1179 l_command := l_command||'
1180 '||l_dim_lvlRel_col||',
1181 '||zpb_metadata_names.get_dim_code_column(l_epb_dim)||',
1182 ''Y'',
1183 ''Y'',';
1184 if (l_dim_data.Type = 'TIME') then
1185 l_command := l_command||'nvl('||
1186 zpb_metadata_names.get_dim_calendar_column(l_epb_dim_id)||',1),';
1187 end if;
1188 l_command := l_command||'
1189 sysdate, '||
1190 l_apps_id||', '||
1191 l_apps_id||',
1192 sysdate, '||
1193 fnd_global.login_id||',
1194 ''N'',
1195 1 from '||l_dim_view||' where '||l_dim_view_col||
1196 ' in ('||l_dims||')';
1197 end if;
1198
1199 execute immediate l_command;
1200
1201 --
1202 -- Populate the MEMBER_TL table for Personal members. Only populating
1203 -- for current language:
1204 --
1205
1206 l_command := 'insert into '||l_dim_mbr_tl_table||'
1207 ('||l_dim_column||', ';
1208 if (l_dim_value_sets = 'Y') then
1209 l_command := l_command||'VALUE_SET_ID, ';
1210 end if;
1211 l_command := l_command||'
1212 LANGUAGE,
1213 SOURCE_LANG, '||
1214 l_dim_name_col||', '||
1215 l_dim_desc_col||', ';
1216 if (l_dim_data.Type = 'TIME') then
1217 l_command := l_command||' CALENDAR_ID, DIMENSION_GROUP_ID, ';
1218 end if;
1219 l_command := l_command||'
1220 CREATION_DATE,
1221 CREATED_BY,
1222 LAST_UPDATED_BY,
1223 LAST_UPDATE_DATE,
1224 LAST_UPDATE_LOGIN)
1225 select ';
1226 if (l_dim_value_sets = 'Y') then
1227 l_command := l_command||'
1228 substr('||l_dim_view_col||', instr('||l_dim_view_col||', ''_'')+1),
1229 '||l_value_set_id||', ';
1230 else
1231 l_command := l_command||l_dim_view_col||', ';
1232 end if;
1233 l_command := l_command||
1234 ''''||FND_GLOBAL.CURRENT_LANGUAGE||''',
1235 '''||FND_GLOBAL.CURRENT_LANGUAGE||''', '||
1236 zpb_metadata_names.get_dim_short_name_column(l_epb_dim)||', '||
1237 zpb_metadata_names.get_dim_long_name_column(l_epb_dim)||', ';
1238 if (l_dim_data.Type = 'TIME') then
1239 l_command := l_command||'nvl('||
1240 zpb_metadata_names.get_dim_calendar_column(l_epb_dim_id)||',1), '||
1241 l_dim_lvlRel_col||', ';
1242 end if;
1243 l_command := l_command||'
1244 sysdate, '||
1245 l_apps_id||', '||
1246 l_apps_id||',
1247 sysdate, '||
1248 fnd_global.login_id||' from '||l_dim_view||' where '||l_dim_view_col||
1249 ' in ('||l_dims||')';
1250
1251 execute immediate l_command;
1252
1253 --
1254 -- Populate the attribute relations:
1255 --
1256 zpb_aw.execute ('call DHM.EXPORT.ATTRS ('||l_apps_id||' '||
1257 ''''||l_epb_dim||''' '''||l_dim_data.ExpObj||''' '''||
1258 l_dim_attr_table||''' '''||l_dim_column||''' '''||
1259 l_dim_value_sets||''')');
1260
1261 end if;
1262 --
1263 -- Populate the dimension groups table
1264 --
1265
1266 if (l_dim_ecm.HierDim <> 'NA' and zpb_aw.interp
1267 ('shw obj(dimmax '''||l_awQual||l_dim_ecm.HierDim||''')') <> '0') then
1268 zpb_aw.execute ('push '||l_awQual||l_dim_ecm.LevelDim);
1269 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '||l_awQual||
1270 l_dim_ecm.LevelPersVar||' eq yes');
1271 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||
1272 ' remove findchars ('||l_dim_ecm.LevelLdscVar||
1273 ' ''LV_'') gt 0');
1274
1275 l_levels := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_awQual||
1276 l_dim_ecm.LevelDim||''' yes)');
1277 if (l_levels <> 'NA') then
1278 i := 1;
1279 loop
1280 j := instr (l_levels, ' ', i);
1281 if (j = 0) then
1282 l_level := substr (l_levels, i);
1283 else
1284 l_level := substr (l_levels, i, j-i);
1285 i := j+1;
1286 end if;
1287
1288 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '''||
1289 l_level||'''');
1290 --
1291 -- The following handles the case where a hierarchy is added
1292 -- after a personal level is created:
1293 --
1294 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' to '||
1295 l_awQual||l_dim_ecm.LevelDepthVar||' ne NA');
1296
1297 l_value := null;
1298 l_level_type := null;
1299 l_time_dim_grp_key := null;
1300 if (l_dim_data.Type = 'TIME') then
1301 l_level_type := zpb_aw.interp('shw '||l_awQual||l_dim_time_ecm.TLvlTypeRel);
1302 select fem_time_dimension_group_key_s.nextval into
1303 l_time_dim_grp_key from dual;
1304 end if;
1305
1306 FEM_DIMENSION_GRPS_PKG.INSERT_ROW
1307 (l_value,
1308 l_level,
1309 l_time_dim_grp_key,
1310 p_dimension_id,
1311 zpb_aw.interp('shw '||l_awQual||l_dim_ecm.LevelDepthVar),
1312 l_level_type,
1313 'N',
1314 1,
1315 'Y',
1316 'Y',
1317 zpb_aw.interp('shw '||l_awQual||l_dim_ecm.LevelSdscVar),
1318 zpb_aw.interp('shw '||l_awQual||l_dim_ecm.LevelMdscVar),
1319 zpb_aw.interp('shw '||l_awQual||l_dim_ecm.LevelLdscVar),
1320 sysdate,
1321 l_apps_id,
1322 sysdate,
1323 l_apps_id,
1324 null);
1325
1326 exit when j=0;
1327 end loop;
1328 end if;
1329
1330 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' to '||l_awQual||
1331 l_dim_ecm.HierTypeRel||' eq ''LEVEL_BASED''');
1332 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' keep '||l_awQual||
1333 l_dim_ecm.HierVersLdscVar||' eq NA');
1334
1335 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_awQual||
1336 l_dim_ecm.HierDim||''' yes)');
1337 if (l_hiers <> 'NA') then
1338 i := 1;
1339 loop
1340 j := instr (l_hiers, ' ', i);
1341 if (j = 0) then
1342 l_hier := substr (l_hiers, i);
1343 else
1344 l_hier := substr (l_hiers, i, j-i);
1345 i := j+1;
1346 end if;
1347 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' to '''||
1348 l_hier||'''');
1349 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '||
1350 l_awQual||l_dim_ecm.HierLevelVS);
1351 l_femHier :=
1352 zpb_aw.interp ('shw '||l_awQual||l_dim_ecm.HierFEMIDVar||' ('||
1353 l_awQual||l_dim_ecm.HierDim||' '''||l_hier||''')');
1354 l_levels := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_awQual||
1355 l_dim_ecm.LevelDim||''' yes)');
1356 if (l_levels <> 'NA') then
1357 k := 1;
1358 loop
1359 m := instr (l_levels, ' ', k);
1360 if (m = 0) then
1361 l_level := substr (l_levels, k);
1362 else
1363 l_level := substr (l_levels, k, m-k);
1364 k := m+1;
1365 end if;
1366 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||
1367 ' to '''||l_level||'''');
1368 insert into FEM_HIER_DIMENSION_GRPS
1369 (DIMENSION_GROUP_ID,
1370 HIERARCHY_OBJ_ID,
1371 RELATIVE_DIMENSION_GROUP_SEQ,
1372 CREATION_DATE,
1373 CREATED_BY,
1374 LAST_UPDATED_BY,
1375 LAST_UPDATE_DATE,
1376 LAST_UPDATE_LOGIN,
1377 OBJECT_VERSION_NUMBER)
1378 values (l_level,
1379 l_femHier,
1380 zpb_aw.interp('shw '||l_awQual||
1381 l_dim_ecm.LevelDepthVar) + 1,
1382 sysdate,
1383 l_apps_id,
1384 l_apps_id,
1385 sysdate,
1386 fnd_global.login_id,
1387 1);
1388 exit when m=0;
1389 end loop;
1390
1391 end if;
1392 exit when j=0;
1393
1394 end loop;
1395 end if;
1396 zpb_aw.execute ('pop '||l_awQual||l_dim_ecm.LevelDim);
1397 end if;
1398
1399 zpb_aw.execute ('pop oknullstatus commas '||l_awQual||l_dim_data.ExpObj||
1400 ' '||l_awQual||l_global_ecm.LangDim);
1401
1402 if (FND_API.TO_BOOLEAN (p_commit)) then
1403 zpb_aw.execute ('upd');
1404 commit work;
1405 end if;
1406
1407 FND_MSG_PUB.COUNT_AND_GET
1408 (p_count => x_msg_count,
1409 p_data => x_msg_data);
1410 /*
1411 exception
1412 when FND_API.G_EXC_ERROR then
1413 ROLLBACK TO Export_Dimension_Grp;
1414 x_return_status := FND_API.G_RET_STS_ERROR;
1415 FND_MSG_PUB.COUNT_AND_GET
1416 (p_count => x_msg_count,
1417 p_data => x_msg_data);
1418
1419 when FND_API.G_EXC_UNEXPECTED_ERROR then
1420 ROLLBACK TO Export_Dimension_Grp;
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 FND_MSG_PUB.COUNT_AND_GET
1423 (p_count => x_msg_count,
1424 p_data => x_msg_data);
1425
1426 when OTHERS then
1427 ROLLBACK TO Export_Dimension_Grp;
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1429 if (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) then
1430 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1431 end if;
1432 FND_MSG_PUB.COUNT_AND_GET
1433 (p_count => x_msg_count,
1434 p_data => x_msg_data);
1435 */
1436 end Transfer_To_DHM;
1437
1438 --
1439 -- Import_Dimension:
1440 --
1441 procedure Transfer_To_EPB
1442 (p_api_version IN NUMBER,
1443 p_init_msg_list IN VARCHAR2,
1444 p_commit IN VARCHAR2,
1445 p_validation_level IN NUMBER,
1446 x_return_status OUT NOCOPY VARCHAR2,
1447 x_msg_count OUT NOCOPY NUMBER,
1448 x_msg_data OUT NOCOPY VARCHAR2,
1449 p_dimension_id IN NUMBER,
1450 p_user_id IN NUMBER,
1451 p_attr_id IN VARCHAR2)
1452 is
1453 l_api_name CONSTANT VARCHAR2(30) := 'Import_Dimension';
1454 l_api_version CONSTANT NUMBER := 1.0;
1455
1456 l_dim_table_name VARCHAR2(30); -- Personal dimension table
1457 l_dim_mbr_table VARCHAR2(30); -- FEM member table for dim
1458 l_dim_mbr_tl_table VARCHAR2(30); -- FEM member transl. table for dim
1459 l_dim_hier_table VARCHAR2(30); -- Personal hierarchy table name
1460 l_dim_attr_table VARCHAR2(30); -- Dim attribute table name
1461 l_dim_column VARCHAR2(30); -- The Dim ID column
1462 l_dim_disp_col VARCHAR2(30); -- The display column
1463 l_dim_name_col VARCHAR2(30); -- The dim name column
1464 l_dim_desc_col VARCHAR2(30); -- The dim description column
1465 l_dim_value_sets VARCHAR2(1); -- True if valuesets on dimension
1466 l_dim_type VARCHAR2(30); -- The FEM Dimension Type Code
1467
1468 l_epb_dim VARCHAR2(30); -- The EPB ID (DMENTRY) of the dim
1469 l_dim_view VARCHAR2(30); -- The dim EPB view
1470 l_dim_view_col VARCHAR2(30); -- The dim EPB view member column
1471 l_dim_gid_col VARCHAR2(30); -- The dim GID column
1472 l_dim_pgid_col VARCHAR2(30); -- The dim PGID column
1473 l_dim_prnt_col VARCHAR2(30); -- The dim parent column
1474 l_aw VARCHAR2(30); -- The personal AW name
1475 l_shrdAW VARCHAR2(30); -- The shared AW name
1476 l_awQual VARCHAR2(30); -- The fully qualified AW name
1477
1478 l_dim_mbr_id VARCHAR2(32); -- The dimension member id
1479 l_dim_mbr_id_list VARCHAR2(3200); -- The dimension member id list
1480 l_dim_mbr_dlt_list VARCHAR2(3200); -- Dimension members to delete
1481 l_last_dim_id VARCHAR2(32);
1482 l_dim_code VARCHAR2(150);
1483 l_dim_name VARCHAR2(150);
1484 l_dim_desc VARCHAR2(225);
1485 l_dim_calendar NUMBER;
1486 l_lang VARCHAR2(30);
1487 l_levels VARCHAR2(1000);
1488 l_level NUMBER;
1489 l_level_seq NUMBER;
1490 l_level_name VARCHAR2(60);
1491 l_level_desc VARCHAR2(60);
1492 l_level_code VARCHAR2(150);
1493 l_level_type VARCHAR2(30);
1494 l_num_periods_in_year NUMBER;
1495 l_parent VARCHAR2(30);
1496 l_child VARCHAR2(30);
1497 l_parent_depth NUMBER;
1498 l_child_depth NUMBER;
1499 l_order NUMBER;
1500 l_hier NUMBER;
1501 l_hiers VARCHAR2(1000);
1502 l_femHier NUMBER;
1503 l_last_hier NUMBER;
1504 l_hier_type VARCHAR2(16);
1505 l_apps_id NUMBER;
1506 l_shdw_id NUMBER;
1507 l_upd_date VARCHAR2(60);
1508
1509 l_view_changed BOOLEAN; -- True if view needs rebuilding
1510 l_gid_changed BOOLEAN; -- True if GID needs rebuilding
1511 l_value VARCHAR2(200);
1512 l_value2 VARCHAR2(200);
1513 l_command VARCHAR2(4000); -- Stores the dyn. sql statement
1514
1515 l_attr_id NUMBER;
1516 l_attr_abbrev VARCHAR2(30);
1517 l_attr_label VARCHAR2(30);
1518 l_attr_num_mbr NUMBER;
1519 l_attr_vs_id NUMBER;
1520 l_attr_var_mbr VARCHAR2(30);
1521 l_attr_num_val NUMBER;
1522 l_attr_var_val VARCHAR2(1000);
1523 l_attr_dat_val DATE;
1524 l_attr_val VARCHAR2(1000);
1525
1526 hi NUMBER;
1527 hj NUMBER;
1528 i NUMBER;
1529 j NUMBER;
1530
1531 l_global_ecm ZPB_ECM.GLOBAL_ECM;
1532 l_attr_ecm ZPB_ECM.GLOBAL_ATTR_ECM;
1533 l_dim_ecm ZPB_ECM.DIMENSION_ECM;
1534 l_dim_data ZPB_ECM.DIMENSION_DATA;
1535 l_dim_time_ecm ZPB_ECM.DIMENSION_TIME_ECM;
1536 l_dim_line_ecm ZPB_ECM.DIMENSION_LINE_ECM;
1537 l_dim_attr_ecm ZPB_ECM.ATTR_ECM;
1538
1539 l_imp_dim_curs epb_cur_type;
1540
1541
1542 l_aw_dim_name ZPB_BUSAREA_DIMENSIONS.AW_DIM_NAME%type;
1543
1544 cursor l_existing_levels is
1545 select DIMENSION_GROUP_ID
1546 from FEM_DIMENSION_GRPS_B
1547 where PERSONAL_FLAG = 'Y'
1548 and CREATED_BY = l_apps_id;
1549
1550
1551 cursor l_hier_grps is
1552 select DIMENSION_GROUP_ID
1553 from FEM_HIER_DIMENSION_GRPS
1554 where HIERARCHY_OBJ_ID =
1555 zpb_aw.interp ('shw '||l_dim_ecm.HierFEMIDVar)
1556 order by RELATIVE_DIMENSION_GROUP_SEQ;
1557 begin
1558 SAVEPOINT Import_Dimension_Grp;
1559
1560 if not FND_API.Compatible_API_Call (l_api_version,
1561 p_api_version,
1562 l_api_name,
1563 G_PKG_NAME)
1564 then
1565 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1566 end if;
1567
1568 if (FND_API.TO_BOOLEAN (p_init_msg_list)) then
1569 FND_MSG_PUB.INITIALIZE;
1570 end if;
1571
1572 x_return_status := FND_API.G_RET_STS_SUCCESS;
1573
1574 l_shdw_id := nvl(sys_context('ZPB_CONTEXT', 'shadow_id'),
1575 fnd_global.user_id);
1576 l_apps_id := nvl(sys_context('ZPB_CONTEXT', 'user_id'), fnd_global.user_id);
1577
1578 zpb_aw.execute ('commas = no');
1579
1580 l_upd_date := 'to_date ('''||
1581 zpb_aw.interp('shw PERSONAL!MD.GLBL.CAT (MD.GLBL.OBJ ''DHM'')')||
1582 ''', ''YYYY/MM/DD HH24:MI:SS'')';
1583
1584 zpb_aw.execute('PERSONAL!MD.GLBL.CAT (MD.GLBL.OBJ ''DHM'') = DB.DATE');
1585
1586 --
1587 -- Get the table/column information from the xdim table
1588 --
1589 select
1590 MEMBER_COL,
1591 MEMBER_DISPLAY_CODE_COL,
1592 MEMBER_B_TABLE_NAME,
1593 MEMBER_TL_TABLE_NAME,
1594 MEMBER_NAME_COL,
1595 MEMBER_DESCRIPTION_COL,
1596 ATTRIBUTE_TABLE_NAME,
1597 PERSONAL_HIERARCHY_TABLE_NAME,
1598 VALUE_SET_REQUIRED_FLAG,
1599 MEMBER_PRIV_TABLE_NAME,
1600 DIMENSION_TYPE_CODE
1601 into
1602 l_dim_column,
1603 l_dim_disp_col,
1604 l_dim_mbr_table,
1605 l_dim_mbr_tl_table,
1606 l_dim_name_col,
1607 l_dim_desc_col,
1608 l_dim_attr_table,
1609 l_dim_hier_table,
1610 l_dim_value_sets,
1611 l_dim_table_name,
1612 l_dim_type
1613 from
1614 FEM_XDIM_DIMENSIONS
1615 where
1616 DIMENSION_ID = p_dimension_id;
1617
1618 l_aw := zpb_aw.get_personal_aw(l_shdw_id);
1619 l_shrdAw := zpb_aw.get_shared_aw;
1620 l_awQual := zpb_aw.get_schema||'.'||l_aw||'!';
1621 l_global_ecm := zpb_ecm.get_global_ecm(l_aw);
1622 l_attr_ecm := zpb_ecm.get_global_attr_ecm(l_aw);
1623
1624 if (p_attr_id is null) then
1625 if (l_dim_type = 'LINE') then
1626 l_epb_dim := zpb_aw.interp('shw lmt ('||l_awQual||l_global_ecm.DimDim
1627 ||' to '||l_awQual||l_global_ecm.DimTypeRel
1628 ||' eq ''LINE'')');
1629 else
1630
1631
1632 ZPB_BUSAREA_MAINT.GENERATE_AW_DIM_NAME(l_dim_type,
1633 l_dim_mbr_table,
1634 l_aw_dim_name);
1635
1636 l_epb_dim := zpb_aw.interp('shw lmt ('||l_awQual||l_global_ecm.DimDim
1637 ||' to '||l_awQual||l_global_ecm.ExpObjVar
1638 ||' eq '''||l_aw_dim_name||''')');
1639 end if;
1640 else
1641 l_epb_dim := p_attr_id;
1642 end if;
1643
1644 l_view_changed := false;
1645 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_epb_dim, l_aw);
1646 l_dim_data := zpb_ecm.get_dimension_data(l_epb_dim, l_aw);
1647 l_dim_view := zpb_metadata_names.get_dimension_view(l_shrdAw,
1648 'PERSONAL',
1649 l_epb_dim);
1650 l_dim_view_col := zpb_metadata_names.get_dimension_column(l_epb_dim);
1651
1652 if (l_dim_data.Type = 'TIME') then
1653 l_dim_time_ecm := zpb_ecm.get_dimension_time_ecm(l_epb_dim, l_aw);
1654 elsif (l_dim_data.Type = 'LINE') then
1655 l_dim_line_ecm := zpb_ecm.get_dimension_line_ecm(l_epb_dim, l_aw);
1656 end if;
1657
1658 zpb_aw.execute ('push oknullstatus '||l_awQual||l_dim_data.ExpObj||' '||
1659 l_awQual||l_global_ecm.LangDim||' '||l_awQual||
1660 l_dim_ecm.LevelDim);
1661 zpb_aw.execute ('oknullstatus = yes; commas = no');
1662
1663 --
1664 -- Parse the MEMBER_B table for removed dimensions:
1665 --
1666 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' to all');
1667
1668 l_command := 'select '||l_dim_view_col||' from '||l_dim_view||
1669 ' minus select to_char(';
1670 if (l_dim_value_sets = 'Y') then
1671 l_command := l_command||' VALUE_SET_ID||''_''||';
1672 end if;
1673 l_command := l_command||l_dim_column||') from '||l_dim_mbr_table||
1674 ' where ENABLED_FLAG = ''Y''';
1675
1676 open l_imp_dim_curs for l_command;
1677 loop
1678 fetch l_imp_dim_curs into l_dim_mbr_id;
1679 exit when l_imp_dim_curs%NOTFOUND;
1680
1681 --
1682 -- Make a list of deleted dimension members (see bug 5493497):
1683 --
1684 l_dim_mbr_id_list := l_dim_mbr_id_list||''''|| l_dim_mbr_id||''''||' ';
1685 end loop;
1686 close l_imp_dim_curs;
1687
1688 -- bug 6333955
1689 -- remove from the list the members that are SHARED
1690 --
1691 -- bug 5493497/6119917 cannot delete in a loop
1692 -- delete the member(s) if any need to be deleted
1693 if (length(l_dim_mbr_id_list) > 0)
1694 then
1695 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '||l_dim_mbr_id_list);
1696
1697 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' keep '||l_awQual||l_dim_ecm.MemberTypeRel||' NE ''SHARED'' ');
1698
1699 l_dim_mbr_id_list := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_awQual||l_dim_data.ExpObj||''' YES)');
1700
1701 -- if there are any values to delete make a list of them
1702 l_dim_mbr_dlt_list := '';
1703 if (l_dim_mbr_id_list <> 'NA') then
1704 i := 1;
1705 loop
1706 j := instr (l_dim_mbr_id_list, ' ', i);
1707 if (j = 0) then
1708 l_dim_mbr_id := substr (l_dim_mbr_id_list, i);
1709 else
1710 l_dim_mbr_id := substr (l_dim_mbr_id_list, i, j-i);
1711 i := j+1;
1712 end if;
1713 l_dim_mbr_dlt_list := l_dim_mbr_dlt_list||'\'''|| l_dim_mbr_id||'\'' ';
1714 exit when j=0;
1715 end loop;
1716
1717 zpb_aw.execute ('call sc.pers.obj.mnt('''||l_awQual||l_dim_data.ExpObj||
1718 ''' ''mnt '||l_awQual||l_dim_data.ExpObj||' delete '||
1719 l_dim_mbr_dlt_list||''')');
1720 end if;
1721 end if;
1722
1723 --
1724 -- Look for removed DIMENSION_GROUPS
1725 --
1726 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '||
1727 l_awQual||l_dim_ecm.LevelPersVar||' eq yes');
1728 if (l_dim_ecm.LevelDim <> 'NA' and
1729 zpb_aw.interp ('shw statlen('||l_awQual||l_dim_ecm.LevelDim||')') <> '0')
1730 then
1731 open l_existing_levels;
1732 loop
1733 fetch l_existing_levels into l_level;
1734 exit when l_existing_levels%NOTFOUND;
1735
1736 if (zpb_aw.interpbool('shw isvalue('||l_awQual||l_dim_ecm.LevelDim||
1737 ' '''||l_level||''')')) then
1738 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||
1739 ' remove '''||l_level||'''');
1740 end if;
1741 end loop;
1742
1743 if (zpb_aw.interp('shw statlen ('||l_awQual||
1744 l_dim_ecm.LevelDim||')') <> 0) then
1745 zpb_aw.execute ('mnt '||l_awQual||l_dim_ecm.LevelDim||
1746 ' delete values('||l_awQual||l_dim_ecm.LevelDim||')');
1747 end if;
1748 end if;
1749
1750 --
1751 -- Update DIMENSION_GROUPS
1752 --
1753 l_command :=
1754 'select A.DIMENSION_GROUP_ID,
1755 A.DIMENSION_GROUP_SEQ,
1756 B.DIMENSION_GROUP_NAME,
1757 B.DESCRIPTION,
1758 A.DIMENSION_GROUP_DISPLAY_CODE
1759 from FEM_DIMENSION_GRPS_B A,
1760 FEM_DIMENSION_GRPS_TL B
1761 where A.DIMENSION_ID = '||p_dimension_id||'
1762 and A.PERSONAL_FLAG = ''Y''
1763 and A.DIMENSION_GROUP_ID = B.DIMENSION_GROUP_ID
1764 and A.CREATED_BY = '||l_apps_id||'
1765 and A.LAST_UPDATE_DATE > '||l_upd_date||'
1766 order by A.DIMENSION_GROUP_SEQ';
1767
1768 open l_imp_dim_curs for l_command;
1769 loop
1770 fetch l_imp_dim_curs into l_level, l_level_seq,
1771 l_level_name, l_level_desc, l_level_code;
1772 exit when l_imp_dim_curs%NOTFOUND;
1773
1774 if (not zpb_aw.interpbool ('shw isvalue('||l_awQual||l_dim_ecm.LevelDim||
1775 ' '''||l_level||''')')) then
1776 zpb_aw.execute ('mnt '||l_awQual||l_dim_ecm.LevelDim||' add '''||
1777 l_level||'''');
1778 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '''||
1779 l_level||'''');
1780 zpb_aw.execute (l_awQual||l_dim_ecm.LevelPersVar||' = YES');
1781 else
1782 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '''||
1783 l_level||'''');
1784 end if;
1785
1786 CONVERT_NAME (l_level_name);
1787 CONVERT_NAME (l_level_desc);
1788 CONVERT_NAME (l_level_code);
1789 zpb_aw.execute
1790 (l_awQual||l_dim_ecm.LevelMdscVar||' = '''||l_level_name||'''');
1791 zpb_aw.execute
1792 (l_awQual||l_dim_ecm.LevelLdscVar||' = '''||l_level_desc||'''');
1793 zpb_aw.execute
1794 (l_awQual||l_dim_ecm.LevelSdscVar||' = '''||l_level_code||'''');
1795 zpb_aw.execute (l_awQual||l_dim_ecm.LevelDepthVar||' = '||l_level_seq);
1796 if (l_dim_data.Type = 'TIME') then
1797 select B.NUMBER_ASSIGN_VALUE TIME_LVL_PERIODS
1798 into l_num_periods_in_year
1799 from FEM_DIMENSION_GRPS_B A,
1800 FEM_TIME_GRP_TYPES_ATTR B,
1801 FEM_DIM_ATTRIBUTES_B C,
1802 FEM_DIM_ATTR_VERSIONS_B D
1803 where A.DIMENSION_GROUP_ID = l_level
1804 AND A.TIME_GROUP_TYPE_CODE = B.TIME_GROUP_TYPE_CODE(+)
1805 AND B.ATTRIBUTE_ID = C.ATTRIBUTE_ID(+)
1806 AND C.ATTRIBUTE_VARCHAR_LABEL(+) = 'PERIODS_IN_YEAR'
1807 AND B.VERSION_ID = D.VERSION_ID(+)
1808 AND B.ATTRIBUTE_ID = D.ATTRIBUTE_ID(+)
1809 AND D.DEFAULT_VERSION_FLAG(+) = 'Y';
1810 l_level_type := zpb_aw.interp ('shw SQ.CONV.TIMELVLS('''||l_num_periods_in_year||''')');
1811 zpb_aw.execute (l_awQual||l_dim_time_ecm.TlvlTypeRel||' = '''||l_level_type||'''');
1812 end if;
1813 end loop;
1814 close l_imp_dim_curs;
1815
1816 --
1817 -- Read in the new dimension members:
1818 --
1819 if (l_dim_value_sets = 'Y') then
1820 l_command := 'select A.VALUE_SET_ID||''_''||A.'||l_dim_column||', ';
1821 else
1822 l_command := 'select to_char(A.'||l_dim_column||'), ';
1823 end if;
1824 if (l_dim_hier_table is not null) then
1825 l_command := l_command||' A.DIMENSION_GROUP_ID, ';
1826 end if;
1827 if (l_dim_data.Type = 'TIME') then
1828 l_command := l_command||'A.CALENDAR_ID, ';
1829 else
1830 l_command := l_command||'null, ';
1831 end if;
1832 l_command := l_command||'LANGUAGE, A.'||l_dim_disp_col||', B.'||
1833 l_dim_name_col||', B.'||l_dim_desc_col||' from '||
1834 l_dim_mbr_table||' A, '||l_dim_mbr_tl_table||' B where '||
1835 'A.PERSONAL_FLAG = ''Y'' and A.'||l_dim_column||
1836 ' = B.'||l_dim_column||' and A.CREATED_BY = '||l_apps_id||
1837 ' and A.LAST_UPDATE_DATE > '||l_upd_date||
1838 ' order by 1';
1839
1840 l_last_dim_id := null;
1841 l_level := null;
1842
1843 open l_imp_dim_curs for l_command;
1844 loop
1845
1846 if (l_dim_hier_table is not null) then
1847 fetch l_imp_dim_curs into l_dim_mbr_id, l_level,
1848 l_dim_calendar, l_lang, l_dim_code, l_dim_name, l_dim_desc;
1849 else
1850 fetch l_imp_dim_curs into l_dim_mbr_id, l_dim_calendar,
1851 l_lang, l_dim_code, l_dim_name, l_dim_desc;
1852 end if;
1853 exit when l_imp_dim_curs%NOTFOUND;
1854
1855 if (l_last_dim_id is null or l_last_dim_id <> l_dim_mbr_id) then
1856
1857 if(not zpb_aw.interpbool('shw isvalue('||l_awQual||l_dim_data.ExpObj||
1858 ' '''||l_dim_mbr_id||''')')) then
1859 zpb_aw.execute ('call sc.pers.obj.mnt('''||l_awQual||
1860 l_dim_data.ExpObj||''' ''mnt '||l_awQual||
1861 l_dim_data.ExpObj||' add \'''||
1862 l_dim_mbr_id||'\'''')');
1863 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '''||
1864 l_dim_mbr_id||'''');
1865 zpb_aw.execute (l_awQual||l_dim_ecm.MemberTypeRel||
1866 ' = ''PERSONAL''');
1867 zpb_aw.execute (l_awQual||l_dim_ecm.InHierVar||' = NO');
1868 -- Add read-only to shared AW to synch shared up (4733894):
1869 zpb_aw.execute ('mnt SHARED!'||l_dim_data.ExpObj||' add '''||
1870 l_dim_mbr_id||'''');
1871 if (l_dim_data.Type = 'LINE') then
1872 ZPB_AW.EXECUTE ('call PA.ADDPERSLINE('''||l_dim_mbr_id||''')');
1873 end if;
1874 else
1875 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '''||
1876 l_dim_mbr_id||'''');
1877 end if;
1878 if (l_level is not null) then
1879 zpb_aw.execute (l_awQual||l_dim_ecm.DfltLevelRel||' = '''||
1880 l_level||'''');
1881 end if;
1882
1883 l_last_dim_id := l_dim_mbr_id;
1884 else
1885 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '''||
1886 l_dim_mbr_id||'''');
1887 end if;
1888 CONVERT_NAME (l_dim_code);
1889 CONVERT_NAME (l_dim_name);
1890 CONVERT_NAME (l_dim_desc);
1891 if (l_dim_desc is not null) then
1892 l_dim_desc := ''''||l_dim_desc||'''';
1893 else
1894 l_dim_desc := 'NA';
1895 end if;
1896 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.LangDim||' to '''||
1897 l_lang||'''');
1898 zpb_aw.execute (l_awQual||l_dim_ecm.SdscVar||' = '''||l_dim_code||'''');
1899 zpb_aw.execute (l_awQual||l_dim_ecm.MdscVar||' = '''||l_dim_name||'''');
1900 zpb_aw.execute (l_awQual||l_dim_ecm.LdscVar||' = '||l_dim_desc);
1901 if (l_dim_data.Type = 'TIME' and
1902 zpb_aw.interpbool('shw exists('''||l_awQual||
1903 l_dim_time_ecm.CalendarVar||''')')) then
1904 zpb_aw.execute (l_awQual||l_dim_time_ecm.CalendarVar||' = '||
1905 l_dim_calendar);
1906 end if;
1907 end loop;
1908 close l_imp_dim_curs;
1909
1910 --
1911 -- Read in hierarchy information:
1912 --
1913 -- Update the levels in this hierarchy, if level-based
1914 --
1915 if (zpb_aw.interp ('shw obj(dimmax '''||l_awQual||l_dim_ecm.HierDim||
1916 ''')') <> '0') then
1917 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_awQual||
1918 l_dim_ecm.HierDim||''')');
1919 hi := 1;
1920 loop
1921 hj := instr (l_hiers, ' ', hi);
1922 if (hj = 0) then
1923 l_value := substr (l_hiers, hi);
1924 else
1925 l_value := substr (l_hiers, hi, hj-hi);
1926 hi := hj+1;
1927 end if;
1928
1929 if (instr(l_value, 'V') > 1) then
1930 l_femHier :=
1931 zpb_aw.interp('shw '||l_awQual||l_dim_ecm.HierFEMIDVar||' ('||
1932 l_awQual||l_dim_ecm.HierDim||' '''||
1933 substr(l_value, 1, instr(l_value, 'V')-1)||''')');
1934 else
1935 l_femHier := l_value;
1936 end if;
1937 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' to '''||
1938 l_femHier||'''');
1939
1940 l_hier_type :=
1941 zpb_aw.interp ('shw '||l_awQual||l_dim_ecm.HierTypeRel);
1942 if (l_hier_type = 'LEVEL_BASED') then
1943 l_levels := '';
1944 for each in l_hier_grps loop
1945 l_levels := l_levels||' '''||each.DIMENSION_GROUP_ID||'''';
1946 end loop;
1947 l_value2 := zpb_aw.interp ('shw values('||l_awQual||
1948 l_dim_ecm.HierLevelVS||')');
1949 zpb_aw.execute('lmt '||l_awQual||l_dim_ecm.HierLevelVS||' to '||
1950 l_levels);
1951 if (zpb_aw.interp ('shw values('||l_awQual||
1952 l_dim_ecm.HierLevelVS||')') <> l_value2) then
1953 l_view_changed := true;
1954 l_gid_changed := true;
1955 end if;
1956 zpb_aw.execute('lmt '||l_awQual||l_dim_ecm.LevelDim||
1957 ' to '||l_awQual||l_dim_ecm.HierLevelVS);
1958 end if;
1959
1960 --
1961 -- Search for removed members:
1962 --
1963 if (l_dim_hier_table is not null) then
1964 if (l_dim_value_sets = 'Y') then
1965
1966 l_command :=
1967 'select distinct A.CHILD_VALUE_SET_ID||''_''||A.CHILD_ID';
1968 else
1969 l_command := 'select distinct to_char(A.CHILD_ID)';
1970 end if;
1971 if (instr(l_value, 'V') > 1) then
1972 l_femHier :=
1973 zpb_aw.interp('shw '||l_awQual||l_dim_ecm.HierFEMIDVar||' ('||
1974 l_awQual||l_dim_ecm.HierDim||' '''||
1975 substr(l_value, 1, instr(l_value, 'V')-1)||''')'); else
1976 l_femHier := l_value;
1977 end if;
1978
1979 l_command := l_command||' from '||l_dim_hier_table||
1980 ' A, '||l_dim_mbr_table||' B, FEM_OBJECT_DEFINITION_B D where ';
1981 if (l_dim_value_sets = 'Y') then
1982 l_command := l_command||
1983 'A.CHILD_VALUE_SET_ID = B.VALUE_SET_ID and ';
1984 end if;
1985 l_command := l_command||'A.CHILD_ID = B.'||l_dim_column||
1986 ' and B.PERSONAL_FLAG = ''Y'''||
1987 ' and A.CREATED_BY = '||l_apps_id||
1988 ' and A.HIERARCHY_OBJ_DEF_ID = D.OBJECT_DEFINITION_ID'||
1989 ' and D.OBJECT_ID = '||l_femHier;
1990
1991 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||
1992 ' to '||l_awQual||l_dim_ecm.HorderVS);
1993 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||
1994 ' keep '||l_awQual||l_dim_ecm.MemberTypeRel||
1995 ' ne ''SHARED''');
1996 open l_imp_dim_curs for l_command;
1997 loop
1998 fetch l_imp_dim_curs into l_child;
1999 exit when l_imp_dim_curs%NOTFOUND;
2000
2001 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||
2002 ' remove '''||l_child||'''');
2003 end loop;
2004 if (zpb_aw.interp('shw statlen('||l_awQual||l_dim_data.ExpObj||
2005 ')') <> '0') then
2006 zpb_aw.execute (l_awQual||l_dim_ecm.InHierVar||' = no');
2007 zpb_aw.execute (l_awQual||l_dim_ecm.ParentRel||' = na');
2008 l_gid_changed := true;
2009 --
2010 -- Could have removed all members at a level:
2011 --
2012 l_view_changed := true;
2013 end if;
2014
2015 end if;
2016 exit when hj = 0;
2017 end loop;
2018 end if;
2019
2020 --
2021 -- Update the member-hierarchy information
2022 --
2023 if (l_dim_hier_table is not null) then
2024 if (l_dim_value_sets = 'Y') then
2025 l_command := 'select A.PARENT_VALUE_SET_ID||''_''||A.PARENT_ID, '||
2026 'A.CHILD_VALUE_SET_ID||''_''||A.CHILD_ID, ';
2027 else
2028 l_command := 'select to_char(A.PARENT_ID), to_char(A.CHILD_ID), ';
2029 end if;
2030 l_command := l_command||' min(A.PARENT_DEPTH_NUM)-1, '||
2031 'A.CHILD_DEPTH_NUM-1, A.DISPLAY_ORDER_NUM, D.OBJECT_ID from '||
2032 l_dim_hier_table||' A, '||l_dim_mbr_table||
2033 ' B, FEM_OBJECT_DEFINITION_B D where ';
2034 if (l_dim_value_sets = 'Y') then
2035 l_command := l_command||
2036 'A.CHILD_VALUE_SET_ID = B.VALUE_SET_ID and ';
2037 end if;
2038 l_command := l_command||'A.CHILD_ID = B.'||l_dim_column||
2039 ' and B.PERSONAL_FLAG = ''Y'''||
2040 ' and A.CREATED_BY = '||l_apps_id||
2041 ' and (A.PARENT_DEPTH_NUM <> A.CHILD_DEPTH_NUM or '||
2042 '(A.PARENT_DEPTH_NUM = A.CHILD_DEPTH_NUM and A.PARENT_DEPTH_NUM = 1))'
2043 ||' and A.HIERARCHY_OBJ_DEF_ID = D.OBJECT_DEFINITION_ID group by ';
2044 if (l_dim_value_sets = 'Y') then
2045 l_command := l_command||'A.PARENT_VALUE_SET_ID||''_''||A.PARENT_ID,'||
2046 ' A.CHILD_VALUE_SET_ID||''_''||A.CHILD_ID, ';
2047 else
2048 l_command := l_command||'A.PARENT_ID, A.CHILD_ID, ';
2049 end if;
2050 l_command := l_command||
2051 ' A.CHILD_DEPTH_NUM, D.OBJECT_ID, A.DISPLAY_ORDER_NUM order by 5,3,2';
2052
2053 l_last_hier := null;
2054 open l_imp_dim_curs for l_command;
2055 loop
2056 fetch l_imp_dim_curs into l_parent, l_child, l_parent_depth,
2057 l_child_depth, l_order, l_hier;
2058 exit when l_imp_dim_curs%NOTFOUND;
2059
2060 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '''||
2061 l_child||'''');
2062 if (l_last_hier is null or l_hier <> l_last_hier) then
2063 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.HierDim||' to '||
2064 l_awQual||l_dim_ecm.HierFEMIDVar||' eq '||l_hier);
2065
2066 l_hier_type :=
2067 zpb_aw.interp ('shw '||l_awQual||l_dim_ecm.HierTypeRel);
2068 l_last_hier := l_hier;
2069 end if;
2070
2071 zpb_aw.execute (l_awQual||l_dim_ecm.InHierVar||' = YES');
2072 zpb_aw.execute (l_awQual||l_dim_ecm.SibOrderVar||' = '||l_order||
2073 '-.5');
2074 if (l_parent_depth <> l_child_depth) then
2075 zpb_aw.execute (l_awQual||l_dim_ecm.ParentRel||' = '''||l_parent||
2076 '''');
2077 end if;
2078
2079 if (l_hier_type = 'LEVEL_BASED') then
2080 zpb_aw.execute (l_awQual||l_dim_ecm.LevelRel||' = '||
2081 l_dim_ecm.DfltLevelRel);
2082
2083 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '||
2084 l_awQual||l_dim_ecm.LevelRel||' '||
2085 l_awQual||l_dim_ecm.LevelRel);
2086 if (zpb_aw.interp ('shw convert(statlen('||l_awQual||
2087 l_dim_data.ExpObj||') TEXT 0 no no)') = '1') then
2088 l_view_changed := true;
2089 end if;
2090 else
2091 zpb_aw.execute('lmt '||l_awQual||l_dim_ecm.LevelDim||' to '||
2092 l_awQual||l_dim_ecm.HierLevelVS);
2093 l_value := zpb_aw.interp ('shw statlen('||l_awQual||
2094 l_dim_ecm.LevelDim||')');
2095 if (l_child_depth > to_number(l_value)) then
2096 --
2097 -- Create a fake level for value-based hierarchy:
2098 --
2099 select '999'||to_char(FEM_DIMENSION_GRPS_B_S.NEXTVAL)
2100 into l_level from dual;
2101 zpb_aw.execute ('mnt '||l_awQual||l_dim_ecm.LevelDim||
2102 ' add '''||l_level||'''');
2103 zpb_aw.execute('lmt '||l_awQual||l_dim_ecm.HierLevelVS||
2104 ' add '''||l_level||'''');
2105
2106 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||
2107 ' to '''||l_level||'''');
2108 zpb_aw.execute (l_awQual||l_dim_ecm.LevelPersVar||' = YES');
2109 zpb_aw.execute (l_awQual||l_dim_ecm.LevelLdscVar||' = ''H'||
2110 l_hier||'LV_'||l_level||'''');
2111 zpb_aw.execute (l_awQual||l_dim_ecm.LevelDepthVar||' = '||
2112 l_child_depth);
2113 l_view_changed := true;
2114 end if;
2115
2116 zpb_aw.execute(l_awQual||l_dim_ecm.LevelRel||' = lmt('||
2117 l_awQual||l_dim_ecm.LevelDim||' to '||
2118 l_dim_ecm.LevelDepthVar||' eq '||
2119 l_child_depth||')');
2120 end if;
2121
2122 --
2123 -- Update FamilyRel:
2124 --
2125 if (l_parent_depth <> l_child_depth) then
2126 zpb_aw.execute ('lmt '||l_awQual||l_dim_ecm.LevelDim||' to all');
2127 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '''||
2128 l_child||'''');
2129 zpb_aw.execute (l_awQual||l_dim_ecm.AncestorRel||' = '||
2130 l_awQual||l_dim_ecm.AncestorRel||' ('||l_awQual||
2131 l_dim_data.ExpObj||' '''||l_parent||''')');
2132 zpb_aw.execute (l_awQual||l_dim_ecm.AncestorRel||' ('||l_awQual||
2133 l_dim_ecm.LevelDim||' '||l_awQual||
2134 l_dim_ecm.LevelRel||') = '''||l_child||'''');
2135 end if;
2136 l_gid_changed := true;
2137
2138 -- dbms_output.put_line ('HIER: '||l_hier||' PARENT: '||l_parent||
2139 -- ' CHILD: '||l_child||' PDPTH: '||
2140 -- l_parent_depth||' CDPTH: '||l_child_depth);
2141
2142 end loop;
2143 close l_imp_dim_curs;
2144 end if;
2145
2146 --
2147 -- Update/Remove ATTRIBUTES
2148 --
2149 if (l_dim_value_sets = 'Y') then
2150 l_command := 'select D.VALUE_SET_ID||''_''||D.'||l_dim_column||', ';
2151 else
2152 l_command := 'select D.'||l_dim_column||', ';
2153 end if;
2154 l_command := l_command||'C.ATTRIBUTE_ID, C.ATTRIBUTE_VARCHAR_LABEL
2155 from FEM_DIM_ATTRIBUTES_B C, '||l_dim_mbr_table||' D,
2156 ZPB_BUSAREA_ATTRIBUTES E, ZPB_BUSAREA_VERSIONS F
2157 where C.DIMENSION_ID = '||p_dimension_id||'
2158 and D.PERSONAL_FLAG = ''Y''
2159 and D.CREATED_BY = '||l_apps_id||'
2160 and C.ATTRIBUTE_ID = E.ATTRIBUTE_ID
2161 and E.VERSION_ID = F.VERSION_ID
2162 and F.BUSINESS_AREA_ID =
2163 sys_context(''ZPB_CONTEXT'', ''business_area_id'')
2164 and F.VERSION_TYPE = ''R''
2165 and (C.ATTRIBUTE_ID, D.'||l_dim_column||') not in
2166 (select A.ATTRIBUTE_ID, B.'||l_dim_column||'
2167 from '||l_dim_attr_table||' A, '||l_dim_mbr_table||' B
2168 where
2169 (A.DIM_ATTRIBUTE_NUMERIC_MEMBER is not null OR
2170 A.DIM_ATTRIBUTE_VALUE_SET_ID is not null OR
2171 A.DIM_ATTRIBUTE_VARCHAR_MEMBER is not null OR
2172 A.NUMBER_ASSIGN_VALUE is not null OR
2173 A.VARCHAR_ASSIGN_VALUE is not null OR
2174 A.DATE_ASSIGN_VALUE is not null)
2175 and A.'||l_dim_column||' = B.'||l_dim_column||'
2176 and B.PERSONAL_FLAG = ''Y''
2177 and B.CREATED_BY = '||l_apps_id||')
2178 order by C.ATTRIBUTE_ID';
2179 open l_imp_dim_curs for l_command;
2180 loop
2181 fetch l_imp_dim_curs into l_dim_mbr_id, l_attr_id, l_attr_label;
2182 exit when l_imp_dim_curs%NOTFOUND;
2183 if (zpb_aw.interpbool('shw isvalue('||l_awQual||l_global_ecm.AttrDim||
2184 ' '''||l_epb_dim||'A'||l_attr_id||''') and isvalue('||
2185 l_awQual||l_dim_data.ExpObj||' '''||l_dim_mbr_id||
2186 ''')')) then
2187 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.AttrDim||' to '''||
2188 l_epb_dim||'A'||l_attr_id||'''');
2189 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '''||
2190 l_dim_mbr_id||'''');
2191 zpb_aw.execute('&joinchars('''||l_awQual||''' '||l_awQual||
2192 l_attr_ecm.ExpObjVar||') = NA');
2193 if (l_attr_label = 'CAL_PERIOD_END_DATE') then
2194 zpb_aw.execute (l_awQual||l_dim_time_ecm.EndDateVar||' = NA');
2195 zpb_aw.execute (l_awQual||l_dim_time_ecm.TimeSpanVar||' = NA');
2196 elsif (l_attr_label = 'CAL_PERIOD_START_DATE') then
2197 zpb_aw.execute (l_awQual||l_dim_time_ecm.TimeSpanVar||' = NA');
2198 elsif (l_attr_label = 'TIME_AGG_METHOD') then
2199 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.DimDim||' to '||
2200 l_awQual||l_global_ecm.DimTypeRel||' eq ''TIME''');
2201 zpb_aw.execute (l_awQual||l_dim_line_ecm.AggLdRel||' = NA');
2202 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.DimDim||' to all');
2203 elsif (l_attr_label = 'DEFAULT_AGG_METHOD') then
2204 zpb_aw.execute (l_awQual||l_dim_line_ecm.AggLineRel||' = NA');
2205 elsif (l_attr_label = 'DEFAULT_NUMBER_FORMAT') then
2206 zpb_aw.execute (l_awQual||l_dim_ecm.FmtStringVar||' = NA');
2207 elsif (l_attr_label = 'BETTER_FLAG') then
2208 zpb_aw.execute(l_awQual||l_dim_line_ecm.BetterWorseVar||' = NA');
2209 end if;
2210 end if;
2211 end loop;
2212 close l_imp_dim_curs;
2213
2214 --
2215 -- Read in attribute information
2216 --
2217 if (l_dim_attr_table is not null) then
2218 if (l_dim_value_sets = 'Y') then
2219 l_command := 'select A.VALUE_SET_ID||''_''||A.'||l_dim_column||', ';
2220 else
2221 l_command := 'select A.'||l_dim_column||', ';
2222 end if;
2223 l_command := l_command||
2224 'A.ATTRIBUTE_ID,
2225 C.ATTRIBUTE_VARCHAR_LABEL,
2226 A.DIM_ATTRIBUTE_NUMERIC_MEMBER,
2227 A.DIM_ATTRIBUTE_VALUE_SET_ID,
2228 A.DIM_ATTRIBUTE_VARCHAR_MEMBER,
2229 A.NUMBER_ASSIGN_VALUE,
2230 A.VARCHAR_ASSIGN_VALUE,
2231 A.DATE_ASSIGN_VALUE
2232 from '||l_dim_attr_table||' A, '||l_dim_mbr_table||' B,
2233 FEM_DIM_ATTRIBUTES_B C
2234 where A.'||l_dim_column||' = B.'||l_dim_column||'
2235 and B.PERSONAL_FLAG = ''Y''
2236 and B.CREATED_BY = '||l_apps_id||'
2237 and A.LAST_UPDATE_DATE > '||l_upd_date||'
2238 and A.ATTRIBUTE_ID = C.ATTRIBUTE_ID
2239 order by A.ATTRIBUTE_ID';
2240
2241 open l_imp_dim_curs for l_command;
2242 loop
2243 fetch l_imp_dim_curs into l_dim_mbr_id, l_attr_id, l_attr_label,
2244 l_attr_num_mbr, l_attr_vs_id, l_attr_var_mbr, l_attr_num_val,
2245 l_attr_var_val, l_attr_dat_val;
2246 exit when l_imp_dim_curs%NOTFOUND;
2247
2248 zpb_aw.execute ('lmt '||l_awQual||l_dim_data.ExpObj||' to '''||
2249 l_dim_mbr_id||'''');
2250
2251 if (l_attr_label = 'CAL_PERIOD_END_DATE') then
2252 zpb_aw.execute
2253 (l_awQual||l_dim_time_ecm.EndDateVar||' = to_date('''||
2254 to_char(l_attr_dat_val, 'YYYY/MM/DD')||''', ''YYYY/MM/DD'')');
2255 elsif (l_attr_label = 'CAL_PERIOD_START_DATE') then
2256 --
2257 -- Start date sets the time span variable, since end date has
2258 -- already be evaluated (end date has a lower attribute id)
2259 --
2260 zpb_aw.execute
2261 (l_awQual||l_dim_time_ecm.TimeSpanVar||' = ('||l_awQual||
2262 l_dim_time_ecm.EndDateVar||' - to_date('''||
2263 to_char(l_attr_dat_val, 'YYYY/MM/DD')||
2264 ''',''YYYY/MM/DD'')) * 86400000');
2265 elsif (l_attr_label = 'TIME_AGG_METHOD') then
2266 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.DimDim||' to '||
2267 l_awQual||l_global_ecm.DimTypeRel||' eq ''TIME''');
2268 zpb_aw.execute
2269 (l_awQual||l_dim_line_ecm.AggLdRel||' = DB.CONV.AGGMETH(''_V'||
2270 l_attr_var_mbr||''')');
2271 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.DimDim||' to all');
2272 elsif (l_attr_label = 'DEFAULT_AGG_METHOD') then
2273 zpb_aw.execute
2274 (l_awQual||l_dim_line_ecm.AggLineRel||
2275 ' = DB.CONV.AGGMETH(''_V'||l_attr_var_mbr||''')');
2276 elsif (l_attr_label = 'DEFAULT_NUMBER_FORMAT') then
2277 zpb_aw.execute
2278 (l_awQual||l_dim_ecm.FmtStringVar||' = '''||
2279 l_attr_var_val||'''');
2280 elsif (l_attr_label = 'BETTER_FLAG') then
2281 zpb_aw.execute
2282 (l_awQual||l_dim_line_ecm.BetterWorseVar||' = if '''||
2283 l_attr_var_mbr||''' eq ''Y'' then 1 else if '''||
2284 l_attr_var_mbr||''' eq ''N'' then -1 else NA');
2285 else
2286 --
2287 -- Line type: must update linetyperel, as well as extended
2288 -- account type attribute:
2289 --
2290 if (l_attr_label = 'EXTENDED_ACCOUNT_TYPE') then
2291 select A.DIM_ATTRIBUTE_VARCHAR_MEMBER
2292 into l_attr_var_mbr
2293 from FEM_EXT_ACCT_TYPES_ATTR A,
2294 FEM_DIM_ATTRIBUTES_B B
2295 where A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
2296 and B.ATTRIBUTE_VARCHAR_LABEL = 'BASIC_ACCOUNT_TYPE_CODE'
2297 and A.EXT_ACCOUNT_TYPE_CODE = l_attr_var_mbr
2298 and A.AW_SNAPSHOT_FLAG = 'N';
2299 zpb_aw.execute
2300 (l_awQual||l_dim_line_ecm.LineTypeRel||' = if '''||
2301 l_attr_var_mbr||''' eq ''EQUITY'' then ''OWNERS.EQUITY'' '||
2302 'else '''||l_attr_var_mbr||'''');
2303 end if;
2304 zpb_aw.execute ('lmt '||l_awQual||l_global_ecm.AttrDim||' to '''||
2305 l_epb_dim||'A'||l_attr_id||'''');
2306 l_dim_attr_ecm := ZPB_ECM.GET_ATTR_ECM(l_epb_dim||'A'||l_attr_id,
2307 l_attr_ecm, l_aw);
2308
2309 l_attr_abbrev := zpb_aw.interp
2310 ('shw '||l_awQual||l_attr_ecm.NameFragVar);
2311
2312 if (l_attr_abbrev <> 'NA') then
2313 l_attr_val := l_attr_abbrev||'.A'||l_attr_id||'_V'||
2314 nvl(to_char(l_attr_num_mbr),
2315 nvl(l_attr_var_mbr,
2316 nvl(to_char(l_attr_num_val),
2317 nvl(l_attr_var_val, to_char(l_attr_dat_val)))));
2318 --
2319 -- Add the new attribute to the personal as a personal member
2320 -- This should only happen in data/varchar/num attribute types
2321 --
2322 l_value := l_awQual||zpb_aw.interp('shw '||l_awQual||
2323 l_attr_ecm.RangeDimRel);
2324 if (not zpb_aw.interpbool('shw isvalue('||l_value||' '''||
2325 l_attr_val||''')')) then
2326 zpb_aw.execute ('mnt '||l_value||
2327 ' merge '''||l_attr_val||'''');
2328 zpb_aw.execute ('lmt '||l_value||' to '''||l_attr_val||'''');
2329 if (l_attr_num_val is not null or l_attr_var_val is not null
2330 or l_attr_dat_val is not null) then
2331 zpb_aw.execute(l_awQual||l_dim_attr_ecm.ldscvar||' = '''||
2332 nvl(to_char(l_attr_num_val),
2333 nvl(l_attr_var_val,
2334 to_char(l_attr_dat_val)))||'''');
2335 end if;
2336 zpb_aw.execute ('&joinchars('''||l_awQual||''' obj (property'
2337 ||' ''MEMBERTYPEREL'' '''||l_value||
2338 ''')) = ''PERSONAL''');
2339 end if;
2340 zpb_aw.execute('&joinchars('''||l_awQual||''' '||l_awQual||
2341 l_attr_ecm.ExpObjVar||') = '''||l_attr_val||'''');
2342 end if;
2343 end if;
2344 end loop;
2345
2346 end if;
2347
2348 --
2349 -- Fix the order structures:
2350 --
2351 zpb_aw.execute('call PA.SET.ORDER('''||zpb_aw.get_schema||'.'||
2352 l_aw||''' '''||l_dim_data.ExpObj||''')');
2353
2354 --
2355 -- If GID changed, update GID and Hierheight structures
2356 --
2357 if (l_gid_changed) then
2358 zpb_aw.execute('call DB.SET.GID('''||zpb_aw.get_schema||'.'||l_aw||
2359 ''' '''||l_dim_data.ExpObj||''')');
2360 end if;
2361
2362 --
2363 -- If levels changed, then we need to update the dim hierarchy table
2364 --
2365 if (l_view_changed) then
2366 ZPB_OLAP_VIEWS_PKG.CREATE_DIMENSION_VIEWS(l_aw, 'PERSONAL', l_epb_dim);
2367 zpb_aw.execute ('call DB.BUILD.LMAP('''||zpb_aw.get_schema||'.'||l_aw||
2368 ''' '''||l_epb_dim||''')');
2369
2370 -- create MD for personal hierarchy table
2371 zpb_metadata_pkg.build_dims(l_aw,
2372 zpb_aw.get_schema||'.'||l_shrdAw,
2373 'PERSONAL',
2374 l_epb_dim);
2375
2376 -- add scoping to newly created hierarchy and levels
2377 zpb_metadata_pkg.build_personal_dims(l_aw,
2378 zpb_aw.get_schema||'.'||l_shrdAw,
2379 'PERSONAL',
2380 l_epb_dim);
2381 end if;
2382
2383 zpb_aw.execute ('pop oknullstatus '||l_awQual||l_dim_data.ExpObj||' '||
2384 l_awQual||l_global_ecm.LangDim||' '||l_awQual||
2385 l_dim_ecm.LevelDim);
2386
2387 if (FND_API.TO_BOOLEAN (p_commit)) then
2388 zpb_aw.execute ('upd');
2389 commit work;
2390 end if;
2391
2392 FND_MSG_PUB.COUNT_AND_GET
2393 (p_count => x_msg_count,
2394 p_data => x_msg_data);
2395 /*
2396 exception
2397 when FND_API.G_EXC_ERROR then
2398 ROLLBACK TO Import_Dimension_Grp;
2399 x_return_status := FND_API.G_RET_STS_ERROR;
2400 FND_MSG_PUB.COUNT_AND_GET
2401 (p_count => x_msg_count,
2402 p_data => x_msg_data);
2403
2404 when FND_API.G_EXC_UNEXPECTED_ERROR then
2405 ROLLBACK TO Import_Dimension_Grp;
2406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2407 FND_MSG_PUB.COUNT_AND_GET
2408 (p_count => x_msg_count,
2409 p_data => x_msg_data);
2410
2411 when OTHERS then
2412 ROLLBACK TO Import_Dimension_Grp;
2413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2414
2415 if (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) then
2416 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2417 end if;
2418 FND_MSG_PUB.COUNT_AND_GET
2419 (p_count => x_msg_count,
2420 p_data => x_msg_data);
2421 */
2422 end Transfer_To_EPB;
2423
2424 end ZPB_DHMInterface_GRP;