eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_AW_MD_WRAPPER dependencies on BSC_OLAP_OBJECT

Line 27: update bsc_olap_object set operation_flag='recreate' where object=p_kpi and object_type='kpi' and context=g_context;

23: p_kpi varchar2
24: ) is
25: Begin
26: default_context_if_null;
27: update bsc_olap_object set operation_flag='recreate' where object=p_kpi and object_type='kpi' and context=g_context;
28: Exception when others then
29: log_n('Exception in mark_kpi_recreate '||sqlerrm);
30: raise;
31: End;

Line 39: delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;

35: default_context_if_null;
36: if g_debug then
37: log('Drop metadata for Dim '||p_dim_name);
38: end if;
39: delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
40: --with the concept of level groups, we do not remove snowflake implementation
41: --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from
42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43: --and context=g_context)

Line 41: --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from

37: log('Drop metadata for Dim '||p_dim_name);
38: end if;
39: delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
40: --with the concept of level groups, we do not remove snowflake implementation
41: --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from
42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43: --and context=g_context)
44: --and context=g_context;
45: --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from

Line 42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name

38: end if;
39: delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
40: --with the concept of level groups, we do not remove snowflake implementation
41: --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from
42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43: --and context=g_context)
44: --and context=g_context;
45: --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from
46: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name

Line 45: --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from

41: --delete bsc_olap_object_relation where parent_object_type='dimension' and parent_object in (select object from
42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43: --and context=g_context)
44: --and context=g_context;
45: --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from
46: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
47: --and context=g_context)
48: --and context=g_context;
49: delete bsc_olap_object where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;

Line 46: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name

42: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
43: --and context=g_context)
44: --and context=g_context;
45: --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from
46: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
47: --and context=g_context)
48: --and context=g_context;
49: delete bsc_olap_object where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
50: Exception when others then

Line 49: delete bsc_olap_object where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;

45: --delete bsc_olap_object where parent_object_type='dimension' and parent_object in (select object from
46: --bsc_olap_object where object_type='dimension level' and parent_object_type='dimension' and parent_object=p_dim_name
47: --and context=g_context)
48: --and context=g_context;
49: delete bsc_olap_object where parent_object_type='dimension' and parent_object=p_dim_name and context=g_context;
50: Exception when others then
51: log_n('Exception in drop_dim '||sqlerrm);
52: raise;
53: End;

Line 218: delete bsc_olap_object_relation where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;

214: if g_debug then
215: log('drop kpi metadata '||p_kpi||bsc_aw_utility.get_time);
216: end if;
217: if p_kpi is not null then
218: delete bsc_olap_object_relation where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
219: delete bsc_olap_object_relation where object=p_kpi and object_type='kpi' and relation_object=p_kpi and relation_object_type='kpi' and
220: relation_type='dimension kpi';
221: delete bsc_olap_object where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
222: end if;

Line 219: delete bsc_olap_object_relation where object=p_kpi and object_type='kpi' and relation_object=p_kpi and relation_object_type='kpi' and

215: log('drop kpi metadata '||p_kpi||bsc_aw_utility.get_time);
216: end if;
217: if p_kpi is not null then
218: delete bsc_olap_object_relation where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
219: delete bsc_olap_object_relation where object=p_kpi and object_type='kpi' and relation_object=p_kpi and relation_object_type='kpi' and
220: relation_type='dimension kpi';
221: delete bsc_olap_object where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
222: end if;
223: Exception when others then

Line 221: delete bsc_olap_object where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;

217: if p_kpi is not null then
218: delete bsc_olap_object_relation where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
219: delete bsc_olap_object_relation where object=p_kpi and object_type='kpi' and relation_object=p_kpi and relation_object_type='kpi' and
220: relation_type='dimension kpi';
221: delete bsc_olap_object where parent_object_type='kpi' and parent_object=p_kpi and context=g_context;
222: end if;
223: Exception when others then
224: log_n('Exception in drop_kpi '||sqlerrm);
225: raise;

Line 294: get bsc olap object table data given the parent object and parent type

290: raise;
291: End;
292:
293: /*
294: get bsc olap object table data given the parent object and parent type
295: access paths
296: 1. par obj specified. par obj type specified. obj not specified. obj type not specified.
297: 2. par obj specified. par obj type specified. obj type specified. obj not specified
298: 3. obj specified. obj type specified

Line 303: procedure get_bsc_olap_object(

299: 4. obj specified. obj type specified. parent obj type specified
300: 5. obj specified. obj type specified. parent obj specified. parent obj type specified
301: 6. obj null, obj type not null, parent obj null, par obj type not null
302: */
303: procedure get_bsc_olap_object(
304: p_object varchar2,
305: p_type varchar2,
306: p_parent_object varchar2,
307: p_parent_type varchar2,

Line 308: p_bsc_olap_object out nocopy bsc_olap_object_tb

304: p_object varchar2,
305: p_type varchar2,
306: p_parent_object varchar2,
307: p_parent_type varchar2,
308: p_bsc_olap_object out nocopy bsc_olap_object_tb
309: ) is
310: --
311: cursor c1 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
312: and context=g_context order by object_type;

Line 311: cursor c1 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type

307: p_parent_type varchar2,
308: p_bsc_olap_object out nocopy bsc_olap_object_tb
309: ) is
310: --
311: cursor c1 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
312: and context=g_context order by object_type;
313: cursor c2 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
314: and object_type=p_type and context=g_context order by object_type;
315: cursor c3 is select * from bsc_olap_object where object=p_object and object_type=p_type

Line 313: cursor c2 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type

309: ) is
310: --
311: cursor c1 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
312: and context=g_context order by object_type;
313: cursor c2 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
314: and object_type=p_type and context=g_context order by object_type;
315: cursor c3 is select * from bsc_olap_object where object=p_object and object_type=p_type
316: and context=g_context order by object_type;
317: cursor c4 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object_type=p_parent_type

Line 315: cursor c3 is select * from bsc_olap_object where object=p_object and object_type=p_type

311: cursor c1 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
312: and context=g_context order by object_type;
313: cursor c2 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
314: and object_type=p_type and context=g_context order by object_type;
315: cursor c3 is select * from bsc_olap_object where object=p_object and object_type=p_type
316: and context=g_context order by object_type;
317: cursor c4 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object_type=p_parent_type
318: and context=g_context order by object_type;
319: cursor c5 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object=p_parent_object

Line 317: cursor c4 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object_type=p_parent_type

313: cursor c2 is select * from bsc_olap_object where parent_object=p_parent_object and parent_object_type=p_parent_type
314: and object_type=p_type and context=g_context order by object_type;
315: cursor c3 is select * from bsc_olap_object where object=p_object and object_type=p_type
316: and context=g_context order by object_type;
317: cursor c4 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object_type=p_parent_type
318: and context=g_context order by object_type;
319: cursor c5 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object=p_parent_object
320: and parent_object_type=p_parent_type and context=g_context order by object_type;
321: cursor c6 is select * from bsc_olap_object where object_type=p_type and parent_object_type=p_parent_type and context=g_context;

Line 319: cursor c5 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object=p_parent_object

315: cursor c3 is select * from bsc_olap_object where object=p_object and object_type=p_type
316: and context=g_context order by object_type;
317: cursor c4 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object_type=p_parent_type
318: and context=g_context order by object_type;
319: cursor c5 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object=p_parent_object
320: and parent_object_type=p_parent_type and context=g_context order by object_type;
321: cursor c6 is select * from bsc_olap_object where object_type=p_type and parent_object_type=p_parent_type and context=g_context;
322: Begin
323: default_context_if_null;

Line 321: cursor c6 is select * from bsc_olap_object where object_type=p_type and parent_object_type=p_parent_type and context=g_context;

317: cursor c4 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object_type=p_parent_type
318: and context=g_context order by object_type;
319: cursor c5 is select * from bsc_olap_object where object=p_object and object_type=p_type and parent_object=p_parent_object
320: and parent_object_type=p_parent_type and context=g_context order by object_type;
321: cursor c6 is select * from bsc_olap_object where object_type=p_type and parent_object_type=p_parent_type and context=g_context;
322: Begin
323: default_context_if_null;
324: if g_debug and bsc_aw_utility.g_debug_level='all' then
325: log(' get_bsc_olap_object object='||p_object||' type='||p_type||' parent_object='||p_parent_object||' po_type='||

Line 325: log(' get_bsc_olap_object object='||p_object||' type='||p_type||' parent_object='||p_parent_object||' po_type='||

321: cursor c6 is select * from bsc_olap_object where object_type=p_type and parent_object_type=p_parent_type and context=g_context;
322: Begin
323: default_context_if_null;
324: if g_debug and bsc_aw_utility.g_debug_level='all' then
325: log(' get_bsc_olap_object object='||p_object||' type='||p_type||' parent_object='||p_parent_object||' po_type='||
326: p_parent_type);
327: end if;
328: if p_object is null and p_type is null and p_parent_object is not null and p_parent_type is not null then --given the parent, get the objects
329: open c1;

Line 331: fetch c1 bulk collect into p_bsc_olap_object;

327: end if;
328: if p_object is null and p_type is null and p_parent_object is not null and p_parent_type is not null then --given the parent, get the objects
329: open c1;
330: loop
331: fetch c1 bulk collect into p_bsc_olap_object;
332: exit when c1%notfound;
333: end loop;
334: close c1;
335: elsif p_object is null and p_type is not null and p_parent_object is not null and p_parent_type is not null then

Line 338: fetch c2 bulk collect into p_bsc_olap_object;

334: close c1;
335: elsif p_object is null and p_type is not null and p_parent_object is not null and p_parent_type is not null then
336: open c2;
337: loop
338: fetch c2 bulk collect into p_bsc_olap_object;
339: exit when c2%notfound;
340: end loop;
341: close c2;
342: elsif p_object is not null and p_type is not null and p_parent_object is null and p_parent_type is null then

Line 345: fetch c3 bulk collect into p_bsc_olap_object;

341: close c2;
342: elsif p_object is not null and p_type is not null and p_parent_object is null and p_parent_type is null then
343: open c3;
344: loop
345: fetch c3 bulk collect into p_bsc_olap_object;
346: exit when c3%notfound;
347: end loop;
348: close c3;
349: elsif p_object is not null and p_type is not null and p_parent_object is null and p_parent_type is not null then

Line 352: fetch c4 bulk collect into p_bsc_olap_object;

348: close c3;
349: elsif p_object is not null and p_type is not null and p_parent_object is null and p_parent_type is not null then
350: open c4;
351: loop
352: fetch c4 bulk collect into p_bsc_olap_object;
353: exit when c4%notfound;
354: end loop;
355: close c4;
356: elsif p_object is not null and p_type is not null and p_parent_object is not null and p_parent_type is not null then

Line 359: fetch c5 bulk collect into p_bsc_olap_object;

355: close c4;
356: elsif p_object is not null and p_type is not null and p_parent_object is not null and p_parent_type is not null then
357: open c5;
358: loop
359: fetch c5 bulk collect into p_bsc_olap_object;
360: exit when c5%notfound;
361: end loop;
362: close c5;
363: elsif p_object is null and p_type is not null and p_parent_object is null and p_parent_type is not null then

Line 366: fetch c6 bulk collect into p_bsc_olap_object;

362: close c5;
363: elsif p_object is null and p_type is not null and p_parent_object is null and p_parent_type is not null then
364: open c6;
365: loop
366: fetch c6 bulk collect into p_bsc_olap_object;
367: exit when c6%notfound;
368: end loop;
369: close c6;
370: end if;

Line 372: log_n('Exception in get_bsc_olap_object '||sqlerrm);

368: end loop;
369: close c6;
370: end if;
371: Exception when others then
372: log_n('Exception in get_bsc_olap_object '||sqlerrm);
373: raise;
374: End;
375:
376: /*

Line 385: procedure get_bsc_olap_object_relation(

381: obj not specified. rel type not specified. par obj specified
382: obj not specified. rel type specified. par obj specified
383: obj specified. rel type not specified. par obj not specified
384: */
385: procedure get_bsc_olap_object_relation(
386: p_object varchar2,
387: p_object_type varchar2,
388: p_relation_type varchar2,
389: p_parent_object varchar2,

Line 391: p_bsc_olap_object_relation out nocopy bsc_olap_object_relation_tb

387: p_object_type varchar2,
388: p_relation_type varchar2,
389: p_parent_object varchar2,
390: p_parent_object_type varchar2,
391: p_bsc_olap_object_relation out nocopy bsc_olap_object_relation_tb
392: ) is
393: --
394: cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
395: and parent_object=p_parent_object and parent_object_type=p_parent_object_type

Line 394: cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type

390: p_parent_object_type varchar2,
391: p_bsc_olap_object_relation out nocopy bsc_olap_object_relation_tb
392: ) is
393: --
394: cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
395: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
396: and context=g_context;
397: cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type

Line 397: cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type

393: --
394: cursor c1 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
395: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
396: and context=g_context;
397: cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
399: and relation_type=p_relation_type and context=g_context;
400: cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
401: and relation_type=p_relation_type and context=g_context;

Line 400: cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type

396: and context=g_context;
397: cursor c2 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
399: and relation_type=p_relation_type and context=g_context;
400: cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
401: and relation_type=p_relation_type and context=g_context;
402: cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object
403: and parent_object_type=p_parent_object_type
404: and context=g_context;

Line 402: cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object

398: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
399: and relation_type=p_relation_type and context=g_context;
400: cursor c3 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
401: and relation_type=p_relation_type and context=g_context;
402: cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object
403: and parent_object_type=p_parent_object_type
404: and context=g_context;
405: cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type
406: and parent_object=p_parent_object and parent_object_type=p_parent_object_type

Line 405: cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type

401: and relation_type=p_relation_type and context=g_context;
402: cursor c4 is select * from bsc_olap_object_relation where parent_object=p_parent_object
403: and parent_object_type=p_parent_object_type
404: and context=g_context;
405: cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type
406: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
407: and context=g_context;
408: cursor c6 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
409: and context=g_context;

Line 408: cursor c6 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type

404: and context=g_context;
405: cursor c5 is select * from bsc_olap_object_relation where relation_type=p_relation_type
406: and parent_object=p_parent_object and parent_object_type=p_parent_object_type
407: and context=g_context;
408: cursor c6 is select * from bsc_olap_object_relation where object=p_object and object_type=p_object_type
409: and context=g_context;
410: --
411: Begin
412: default_context_if_null;

Line 414: log(' get_bsc_olap_object_relation object='||p_object||' type='||p_object_type||' rel_type='||

410: --
411: Begin
412: default_context_if_null;
413: if g_debug and bsc_aw_utility.g_debug_level='all' then
414: log(' get_bsc_olap_object_relation object='||p_object||' type='||p_object_type||' rel_type='||
415: p_relation_type||' par_obj='||p_parent_object||' par_obj_type='||p_parent_object_type);
416: end if;
417: if p_object is not null and p_relation_type is null and p_parent_object is not null then
418: open c1;

Line 420: fetch c1 bulk collect into p_bsc_olap_object_relation;

416: end if;
417: if p_object is not null and p_relation_type is null and p_parent_object is not null then
418: open c1;
419: loop
420: fetch c1 bulk collect into p_bsc_olap_object_relation;
421: exit when c1%notfound;
422: end loop;
423: close c1;
424: elsif p_object is not null and p_relation_type is not null and p_parent_object is not null then

Line 427: fetch c2 bulk collect into p_bsc_olap_object_relation;

423: close c1;
424: elsif p_object is not null and p_relation_type is not null and p_parent_object is not null then
425: open c2;
426: loop
427: fetch c2 bulk collect into p_bsc_olap_object_relation;
428: exit when c2%notfound;
429: end loop;
430: close c2;
431: elsif p_object is not null and p_relation_type is not null and p_parent_object is null then

Line 434: fetch c3 bulk collect into p_bsc_olap_object_relation;

430: close c2;
431: elsif p_object is not null and p_relation_type is not null and p_parent_object is null then
432: open c3;
433: loop
434: fetch c3 bulk collect into p_bsc_olap_object_relation;
435: exit when c3%notfound;
436: end loop;
437: close c3;
438: elsif p_object is null and p_relation_type is null and p_parent_object is not null then

Line 441: fetch c4 bulk collect into p_bsc_olap_object_relation;

437: close c3;
438: elsif p_object is null and p_relation_type is null and p_parent_object is not null then
439: open c4;
440: loop
441: fetch c4 bulk collect into p_bsc_olap_object_relation;
442: exit when c4%notfound;
443: end loop;
444: close c4;
445: elsif p_object is null and p_relation_type is not null and p_parent_object is not null then

Line 448: fetch c5 bulk collect into p_bsc_olap_object_relation;

444: close c4;
445: elsif p_object is null and p_relation_type is not null and p_parent_object is not null then
446: open c5;
447: loop
448: fetch c5 bulk collect into p_bsc_olap_object_relation;
449: exit when c5%notfound;
450: end loop;
451: close c5;
452: elsif p_object is not null and p_relation_type is null and p_parent_object is null then

Line 455: fetch c6 bulk collect into p_bsc_olap_object_relation;

451: close c5;
452: elsif p_object is not null and p_relation_type is null and p_parent_object is null then
453: open c6;
454: loop
455: fetch c6 bulk collect into p_bsc_olap_object_relation;
456: exit when c6%notfound;
457: end loop;
458: close c6;
459: end if;

Line 461: log_n('Exception in get_bsc_olap_object_relation '||sqlerrm);

457: end loop;
458: close c6;
459: end if;
460: Exception when others then
461: log_n('Exception in get_bsc_olap_object_relation '||sqlerrm);
462: raise;
463: End;
464:
465: procedure create_kpi(p_kpi bsc_aw_adapter_kpi.kpi_r) is

Line 471: --=======bsc olap objects====================

467: default_context_if_null;
468: if g_debug then
469: log('create kpi metadata '||p_kpi.kpi||bsc_aw_utility.get_time);
470: end if;
471: --=======bsc olap objects====================
472: insert_olap_object(p_kpi.kpi,'kpi',null,null,p_kpi.kpi,'kpi','calendar='||p_kpi.calendar||',parent kpi='||p_kpi.parent_kpi);
473: for i in 1..p_kpi.dim_set.count loop
474: create_kpi(p_kpi.kpi,p_kpi.dim_set(i));
475: end loop;

Line 491: l_bsc_olap_object_relation bsc_olap_object_relation_tb;

487: procedure create_kpi(p_kpi varchar2,p_dim_set bsc_aw_adapter_kpi.dim_set_r) is
488: l_property varchar2(4000);
489: l_comp_added dbms_sql.varchar2_table; --so we insert into oo distinct composite names
490: l_dimset_name_property varchar2(400);
491: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
492: Begin
493: default_context_if_null;
494: --=======bsc olap objects====================
495: --we need to have all the objects entered in bsc_olap_object that needs to be dropped

Line 494: --=======bsc olap objects====================

490: l_dimset_name_property varchar2(400);
491: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
492: Begin
493: default_context_if_null;
494: --=======bsc olap objects====================
495: --we need to have all the objects entered in bsc_olap_object that needs to be dropped
496: --dimension set
497: --we just need to knoow if there are targets or not, if there are targets we load, aggregate, load targets and again aggregate
498: --base dim set will be useful for target dimsets, for regular dimsets, this is will be null.

Line 495: --we need to have all the objects entered in bsc_olap_object that needs to be dropped

491: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
492: Begin
493: default_context_if_null;
494: --=======bsc olap objects====================
495: --we need to have all the objects entered in bsc_olap_object that needs to be dropped
496: --dimension set
497: --we just need to knoow if there are targets or not, if there are targets we load, aggregate, load targets and again aggregate
498: --base dim set will be useful for target dimsets, for regular dimsets, this is will be null.
499: --in BSCAWLKB.pls, we have to see the base dim set for a target dimset. only then can we copy data from target cubes to actual cubes

Line 669: --=======bsc olap object relations===========

665: insert_olap_object(p_dim_set.z_s_view(i).s_view,'relational view',null,null,p_kpi,'kpi',l_dimset_name_property||'zero code');
666: end if;
667: end loop;
668: --
669: --=======bsc olap object relations===========
670: /*
671: we enter the following
672: given a dim set, the dimensions. for each dim, the levels. for each dim set, the cubes and the programs.
673: we also say if the level has zero code

Line 927: l_bsc_olap_object_relation.delete;

923: end loop;
924: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
925: p_dim_set.dim_set_name,'dimension set','base table dim set',p_kpi,'kpi',l_property);
926: --for each base table, create an entry that will hold the current load set id (in change_vector column)
927: l_bsc_olap_object_relation.delete;
928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used

Line 928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',

924: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
925: p_dim_set.dim_set_name,'dimension set','base table dim set',p_kpi,'kpi',l_property);
926: --for each base table, create an entry that will hold the current load set id (in change_vector column)
927: l_bsc_olap_object_relation.delete;
928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
932: if l_bsc_olap_object_relation.count=0 then

Line 929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);

925: p_dim_set.dim_set_name,'dimension set','base table dim set',p_kpi,'kpi',l_property);
926: --for each base table, create an entry that will hold the current load set id (in change_vector column)
927: l_bsc_olap_object_relation.delete;
928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
932: if l_bsc_olap_object_relation.count=0 then
933: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',

Line 932: if l_bsc_olap_object_relation.count=0 then

928: get_bsc_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table','base table change vector',
929: p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',l_bsc_olap_object_relation);
930: --if the entry is new, create one, we enter 0 as the current change vector value
931: --these base table entries need to be periodically validated and cleaned up. a base table may no longer be used
932: if l_bsc_olap_object_relation.count=0 then
933: insert_olap_object_relation(p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',
934: '0','change vector','base table change vector',p_dim_set.inc_data_source(i).base_tables(j).base_table_name,'base table',null);
935: end if;
936: end if;

Line 956: insert into bsc_olap_object(object,object_type,olap_object,olap_object_type,property1,parent_object,parent_object_type,

952: ) is
953: Begin
954: default_context_if_null;
955: if p_object is not null then
956: insert into bsc_olap_object(object,object_type,olap_object,olap_object_type,property1,parent_object,parent_object_type,
957: CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
958: values (p_object,p_object_type,p_olap_object,p_olap_object_type,p_property1,p_parent_object,p_parent_object_type,
959: sysdate,sysdate,g_who,g_who,g_who,g_context);
960: end if;

Line 985: l_bsc_olap_object bsc_olap_object_tb;

981: p_parent_object_type varchar2,
982: p_property1 varchar2
983: ) is
984: --
985: l_bsc_olap_object bsc_olap_object_tb;
986: Begin
987: default_context_if_null;
988: get_bsc_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,l_bsc_olap_object);
989: if l_bsc_olap_object.count>0 then

Line 988: get_bsc_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,l_bsc_olap_object);

984: --
985: l_bsc_olap_object bsc_olap_object_tb;
986: Begin
987: default_context_if_null;
988: get_bsc_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,l_bsc_olap_object);
989: if l_bsc_olap_object.count>0 then
990: null;
991: /*update is risky here. we can have multiple rows of data in l_bsc_olap_object. so which rows do we update here?*/
992: else

Line 989: if l_bsc_olap_object.count>0 then

985: l_bsc_olap_object bsc_olap_object_tb;
986: Begin
987: default_context_if_null;
988: get_bsc_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,l_bsc_olap_object);
989: if l_bsc_olap_object.count>0 then
990: null;
991: /*update is risky here. we can have multiple rows of data in l_bsc_olap_object. so which rows do we update here?*/
992: else
993: insert_olap_object(p_object,p_object_type,p_olap_object,p_olap_object_type,p_parent_object,p_parent_object_type,p_property1);

Line 991: /update is risky here. we can have multiple rows of data in l_bsc_olap_object. so which rows do we update here?/

987: default_context_if_null;
988: get_bsc_olap_object(p_object,p_object_type,p_parent_object,p_parent_object_type,l_bsc_olap_object);
989: if l_bsc_olap_object.count>0 then
990: null;
991: /*update is risky here. we can have multiple rows of data in l_bsc_olap_object. so which rows do we update here?*/
992: else
993: insert_olap_object(p_object,p_object_type,p_olap_object,p_olap_object_type,p_parent_object,p_parent_object_type,p_property1);
994: end if;
995: Exception when others then

Line 1013: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,

1009: ) is
1010: Begin
1011: default_context_if_null;
1012: if p_object is not null then
1013: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1014: relation_type,parent_object,parent_object_type,property1,
1015: CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1016: values (p_object,p_object_type,p_relation_object,p_relation_object_type,
1017: p_relation_type,p_parent_object,p_parent_object_type,p_property1,

Line 1047: l_bsc_olap_object_relation bsc_olap_object_relation_tb;

1043: p_parent_object_type varchar2,
1044: p_property1 varchar2
1045: ) is
1046: --
1047: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
1048: Begin
1049: default_context_if_null;
1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then

Line 1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);

1046: --
1047: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
1048: Begin
1049: default_context_if_null;
1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then
1052: null;
1053: else
1054: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,

Line 1051: if l_bsc_olap_object_relation.count>0 then

1047: l_bsc_olap_object_relation bsc_olap_object_relation_tb;
1048: Begin
1049: default_context_if_null;
1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then
1052: null;
1053: else
1054: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1055: relation_type,parent_object,parent_object_type,property1,

Line 1054: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,

1050: get_bsc_olap_object_relation(p_object,p_object_type,p_relation_type,p_parent_object,p_parent_object_type,l_bsc_olap_object_relation);
1051: if l_bsc_olap_object_relation.count>0 then
1052: null;
1053: else
1054: insert into bsc_olap_object_relation(object,object_type,relation_object,relation_object_type,
1055: relation_type,parent_object,parent_object_type,property1,
1056: CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1057: values (p_object,p_object_type,p_relation_object,p_relation_object_type,
1058: p_relation_type,p_parent_object,p_parent_object_type,p_property1,

Line 1094: l_stmt:='update bsc_olap_object_relation set ';

1090: --
1091: l_stmt varchar2(8000);
1092: Begin
1093: default_context_if_null;
1094: l_stmt:='update bsc_olap_object_relation set ';
1095: for i in 1..p_set_columns.count loop
1096: l_stmt:=l_stmt||p_set_columns(i)||'='''||p_set_values(i)||''',';
1097: end loop;
1098: l_stmt:=substr(l_stmt,1,length(l_stmt)-1);

Line 1144: l_stmt:='update bsc_olap_object set ';

1140: --
1141: l_stmt varchar2(8000);
1142: Begin
1143: default_context_if_null;
1144: l_stmt:='update bsc_olap_object set ';
1145: for i in 1..p_set_columns.count loop
1146: l_stmt:=l_stmt||p_set_columns(i)||'='''||p_set_values(i)||''',';
1147: end loop;
1148: l_stmt:=l_stmt||'last_update_date=sysdate';

Line 1186: delete bsc_olap_object_relation

1182: ', p_relation_type='||p_relation_type||', p_relation_object='||p_relation_object||
1183: ', p_relation_object_type='||p_relation_object_type||', p_parent_object='||p_parent_object||
1184: ', p_parent_object_type='||p_parent_object_type||bsc_aw_utility.get_time);
1185: end if;
1186: delete bsc_olap_object_relation
1187: where object=nvl(p_object,object)
1188: and object_type=nvl(p_object_type,object_type)
1189: and relation_type=nvl(p_relation_type,relation_type)
1190: and relation_object=nvl(p_relation_object,relation_object)

Line 1218: delete bsc_olap_object

1214: log('delete_olap_object:object='||p_object||', object_type='||p_object_type||
1215: ', p_olap_object='||p_olap_object||', p_olap_object_type='||p_olap_object_type||
1216: ', p_parent_object='||p_parent_object||', p_parent_object_type='||p_parent_object_type||bsc_aw_utility.get_time);
1217: end if;
1218: delete bsc_olap_object
1219: where object=nvl(p_object,object)
1220: and object_type=nvl(p_object_type,object_type)
1221: and olap_object=nvl(p_olap_object,olap_object)
1222: and olap_object_type=nvl(p_olap_object_type,olap_object_type)

Line 1239: insert into bsc_olap_object(object,object_type,olap_object,olap_object_type,property1,parent_object,parent_object_type,

1235: ---workspace metadata
1236: procedure create_workspace(p_name varchar2) is
1237: Begin
1238: default_context_if_null;
1239: insert into bsc_olap_object(object,object_type,olap_object,olap_object_type,property1,parent_object,parent_object_type,
1240: CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CONTEXT)
1241: values (p_name,'aw workspace',p_name,'aw workspace','BSC AW Workspace','BSC','APPS',
1242: sysdate,sysdate,g_who,g_who,g_who,g_context);
1243: Exception when others then

Line 1251: delete bsc_olap_object where object=p_name;

1247:
1248: procedure drop_workspace(p_name varchar2) is
1249: Begin
1250: default_context_if_null;
1251: delete bsc_olap_object where object=p_name;
1252: Exception when others then
1253: log_n('Exception in drop_workspace '||sqlerrm);
1254: raise;
1255: End;

Line 1259: in bsc_olap_object, we will use property9 and 10 (9 for now) to store the load start time, end time

1255: End;
1256:
1257: /*
1258: managing info on loads, aggregations, locks etc
1259: in bsc_olap_object, we will use property9 and 10 (9 for now) to store the load start time, end time
1260: session id etc. it will be stored as
1261: load+start time=01:01:2000:12:12:12+end time=01:01:2000:12:12:12+session id=1022,aggregation+..
1262: groups are separated by comma. elements in each group are separated by +.
1263: this is read into a table of records format. then we can update, insert etc.

Line 1298: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT',60);

1294: */
1295:
1296: procedure analyze_md_tables is
1297: Begin
1298: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT',60);
1299: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT_RELATION',60);
1300: Exception when others then
1301: log_n('Exception in analyze_md_tables '||sqlerrm);
1302: raise;

Line 1299: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT_RELATION',60);

1295:
1296: procedure analyze_md_tables is
1297: Begin
1298: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT',60);
1299: bsc_aw_utility.analyze_table('BSC_OLAP_OBJECT_RELATION',60);
1300: Exception when others then
1301: log_n('Exception in analyze_md_tables '||sqlerrm);
1302: raise;
1303: End;