APPS.BSC_AW_LOAD_DIM dependencies on BSC_OLAP_OBJECT

Line 157: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;

153: procedure load_aw_dim(p_dim varchar2,p_run_id number,p_job_name varchar2,p_options varchar2) is
154: l_initial_load_pgm varchar2(300);
155: l_inc_load_pgm varchar2(300);
156: l_dim_property varchar2(4000);
157: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
158: l_denorm_src varchar2(4000);
159: l_dim_level_delete dim_level_delete_tv;
160: l_dim_delete_flag boolean;
161: Begin

Line 175: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_oo);

171: end if;
172: log_n('load_aw_dim, p_dim='||p_dim||', p_run_id='||p_run_id||', p_job_name='||p_job_name||
173: ', p_options='||p_options);
174: l_oo.delete;
175: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_oo);
176: for i in 1..l_oo.count loop
177: if l_oo(i).object_type='dml program' then
178: if l_oo(i).olap_object_type='dml program initial load' then
179: l_initial_load_pgm:=l_oo(i).object;

Line 240: --if this is a job, send success message to pipe...update bsc_olap_object saying operation_flag='loaded'

236: --
237: set_kpi_limit_variables(p_dim);
238: bsc_aw_management.commit_aw;--will release the locks on the dim objects in 10g
239: bsc_aw_management.detach_workspace;
240: --if this is a job, send success message to pipe...update bsc_olap_object saying operation_flag='loaded'
241: mark_dim_loaded(p_dim);
242: --
243: if p_run_id is not null then
244: bsc_aw_utility.send_pipe_message(p_job_name,'status=success');

Line 279: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;

275: p_dim_level_delete in out nocopy dim_level_delete_tv,
276: p_delete_flag out nocopy boolean) is
277: --
278: l_level varchar2(300);
279: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
280: Begin
281: p_delete_flag:=false;
282: p_dim_level_delete('ALL').delete_name:='ALL';
283: p_dim_level_delete('ALL').delete_values.delete;

Line 286: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);

282: p_dim_level_delete('ALL').delete_name:='ALL';
283: p_dim_level_delete('ALL').delete_values.delete;
284: --get the dim levels
285: l_oo.delete;
286: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
287: --
288: if l_oo.count>0 then
289: for i in 1..l_oo.count loop
290: bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved

Line 300: bsc_aw_md_api.get_bsc_olap_object(null,'recursive level',p_dim,'dimension',l_oo);

296: --
297: if nvl(bsc_aw_utility.get_parameter_value(p_dim_property,'recursive',','),'N')='Y' then
298: l_level:=l_oo(1).object;
299: l_oo.delete;
300: bsc_aw_md_api.get_bsc_olap_object(null,'recursive level',p_dim,'dimension',l_oo);
301: for i in 1..l_oo.count loop
302: bsc_aw_utility.merge_value(p_dim_level_delete('ALL').delete_values,l_oo(i).object);--ALL will hold the levels involved
303: load_delete_dim_level_value(l_oo(i).object,upper(l_level),p_dim_level_delete);
304: end loop;

Line 427: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;

423: purge dim levels
424: */
425: procedure purge_dim(p_dim varchar2) is
426: --
427: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
428: l_kpi_list dbms_sql.varchar2_table;
429: Begin
430: if g_debug then
431: log_n('Purge dim '||p_dim);

Line 441: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_olap_object);

437: for i in 1..l_kpi_list.count loop
438: bsc_aw_load_kpi.purge_kpi(l_kpi_list(i));
439: end loop;
440: --
441: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_olap_object);
442: --purge variables
443: for i in 1..l_olap_object.count loop
444: if l_olap_object(i).object_type='limit cube' or l_olap_object(i).object_type='filter cube' then
445: bsc_aw_dbms_aw.execute('clear all from '||l_olap_object(i).olap_object);

Line 515: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;

511: we also set the aggregate marker for the kpi dimset to true
512: */
513: procedure set_kpi_limit_variables(p_dim varchar2) is
514: --
515: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
516: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
517: l_dim_limit_cube varchar2(300);
518: l_kpi_limit_cubes dbms_sql.varchar2_table;
519: l_kpi_aggregate_markers dbms_sql.varchar2_table;

Line 516: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;

512: */
513: procedure set_kpi_limit_variables(p_dim varchar2) is
514: --
515: l_olap_object_relation bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
516: l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
517: l_dim_limit_cube varchar2(300);
518: l_kpi_limit_cubes dbms_sql.varchar2_table;
519: l_kpi_aggregate_markers dbms_sql.varchar2_table;
520: l_kpi_reset_cubes dbms_sql.varchar2_table;

Line 524: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'dim limit cube',p_dim,'dimension',l_olap_object_relation);

520: l_kpi_reset_cubes dbms_sql.varchar2_table;
521: l_statlen varchar2(200);
522: l_relation varchar2(200);
523: Begin
524: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'dim limit cube',p_dim,'dimension',l_olap_object_relation);
525: bsc_aw_md_api.get_bsc_olap_object(null,'relation',p_dim,'dimension',l_olap_object);
526: if l_olap_object.count>0 then
527: l_relation:=l_olap_object(1).olap_object;
528: else

Line 525: bsc_aw_md_api.get_bsc_olap_object(null,'relation',p_dim,'dimension',l_olap_object);

521: l_statlen varchar2(200);
522: l_relation varchar2(200);
523: Begin
524: bsc_aw_md_api.get_bsc_olap_object_relation(null,null,'dim limit cube',p_dim,'dimension',l_olap_object_relation);
525: bsc_aw_md_api.get_bsc_olap_object(null,'relation',p_dim,'dimension',l_olap_object);
526: if l_olap_object.count>0 then
527: l_relation:=l_olap_object(1).olap_object;
528: else
529: if g_debug then

Line 617: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;

613: we cannot update a relation before a dim. so when we get locks, we first get dim, then relations, then variables
614: */
615: procedure get_dim_objects_to_lock(p_dim varchar2,p_lock_objects out nocopy dbms_sql.varchar2_table) is
616: --
617: l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
618: l_kpi_limit_cubes dbms_sql.varchar2_table;
619: l_kpi_reset_cubes dbms_sql.varchar2_table;
620: l_kpi_aggregate_markers dbms_sql.varchar2_table;
621: l_objects dbms_sql.varchar2_table;

Line 623: l_bsc_olap_object.delete;

619: l_kpi_reset_cubes dbms_sql.varchar2_table;
620: l_kpi_aggregate_markers dbms_sql.varchar2_table;
621: l_objects dbms_sql.varchar2_table;
622: Begin
623: l_bsc_olap_object.delete;
624: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_bsc_olap_object);
625: for i in 1..l_bsc_olap_object.count loop
626: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;

Line 624: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_bsc_olap_object);

620: l_kpi_aggregate_markers dbms_sql.varchar2_table;
621: l_objects dbms_sql.varchar2_table;
622: Begin
623: l_bsc_olap_object.delete;
624: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_bsc_olap_object);
625: for i in 1..l_bsc_olap_object.count loop
626: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
628: end if;

Line 625: for i in 1..l_bsc_olap_object.count loop

621: l_objects dbms_sql.varchar2_table;
622: Begin
623: l_bsc_olap_object.delete;
624: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_bsc_olap_object);
625: for i in 1..l_bsc_olap_object.count loop
626: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
628: end if;
629: end loop;

Line 626: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then

622: Begin
623: l_bsc_olap_object.delete;
624: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_bsc_olap_object);
625: for i in 1..l_bsc_olap_object.count loop
626: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
628: end if;
629: end loop;
630: for i in 1..l_bsc_olap_object.count loop

Line 627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;

623: l_bsc_olap_object.delete;
624: bsc_aw_md_api.get_bsc_olap_object(null,null,p_dim,'dimension',l_bsc_olap_object);
625: for i in 1..l_bsc_olap_object.count loop
626: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
628: end if;
629: end loop;
630: for i in 1..l_bsc_olap_object.count loop
631: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then

Line 630: for i in 1..l_bsc_olap_object.count loop

626: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
628: end if;
629: end loop;
630: for i in 1..l_bsc_olap_object.count loop
631: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then
632: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
633: end if;
634: end loop;

Line 631: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then

627: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
628: end if;
629: end loop;
630: for i in 1..l_bsc_olap_object.count loop
631: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then
632: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
633: end if;
634: end loop;
635: for i in 1..l_bsc_olap_object.count loop

Line 632: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;

628: end if;
629: end loop;
630: for i in 1..l_bsc_olap_object.count loop
631: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then
632: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
633: end if;
634: end loop;
635: for i in 1..l_bsc_olap_object.count loop
636: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='variable' then

Line 635: for i in 1..l_bsc_olap_object.count loop

631: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then
632: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
633: end if;
634: end loop;
635: for i in 1..l_bsc_olap_object.count loop
636: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='variable' then
637: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
638: end if;
639: end loop;

Line 636: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='variable' then

632: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
633: end if;
634: end loop;
635: for i in 1..l_bsc_olap_object.count loop
636: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='variable' then
637: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
638: end if;
639: end loop;
640: --also, we have to lock the kpi limit cubes

Line 637: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;

633: end if;
634: end loop;
635: for i in 1..l_bsc_olap_object.count loop
636: if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='variable' then
637: l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
638: end if;
639: end loop;
640: --also, we have to lock the kpi limit cubes
641: bsc_aw_adapter_dim.get_dim_kpi_limit_cubes(p_dim,l_kpi_limit_cubes,l_kpi_aggregate_markers,l_kpi_reset_cubes);

Line 695: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;

691: raise;
692: End;
693:
694: function check_dim_loaded(p_dim varchar2) return varchar2 is
695: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
696: Begin
697: if bsc_aw_utility.in_array(bsc_aw_adapter_dim.get_preloaded_dim_list,p_dim)=false then
698: bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);
699: if l_oo(1).operation_flag is not null and l_oo(1).operation_flag='loaded' then

Line 698: bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);

694: function check_dim_loaded(p_dim varchar2) return varchar2 is
695: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
696: Begin
697: if bsc_aw_utility.in_array(bsc_aw_adapter_dim.get_preloaded_dim_list,p_dim)=false then
698: bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);
699: if l_oo(1).operation_flag is not null and l_oo(1).operation_flag='loaded' then
700: return 'Y';
701: else
702: return 'N';

Line 713: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;

709: raise;
710: End;
711:
712: procedure mark_dim_loaded(p_dim varchar2) is
713: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
714: Begin
715: bsc_aw_md_api.update_olap_object(p_dim,'dimension',p_dim,'dimension',null,null,'operation_flag','loaded');
716: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
717: for i in 1..l_oo.count loop --mark snowflake impl as loaded

Line 716: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);

712: procedure mark_dim_loaded(p_dim varchar2) is
713: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
714: Begin
715: bsc_aw_md_api.update_olap_object(p_dim,'dimension',p_dim,'dimension',null,null,'operation_flag','loaded');
716: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',p_dim,'dimension',l_oo);
717: for i in 1..l_oo.count loop --mark snowflake impl as loaded
718: bsc_aw_md_api.update_olap_object(l_oo(i).object,'dimension',l_oo(i).object,'dimension',null,null,'operation_flag','loaded');
719: end loop;
720: Exception when others then

Line 729: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;

725: /* 5064802 cannot recreate dim programs. do this: load all dim once again and also load the values from bsc_aw_dim_delete table
726: from now on, hopefully, there will not be attempt to delete values that do not exist in dimensions. newly created dim will have robust strategy
727: to handle cases where dim delete values are not yet in aw dim*/
728: procedure upgrade_load_sync_all_dim is
729: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
730: l_dim varchar2(200);
731: l_dim_list dbms_sql.varchar2_table;
732: Begin
733: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',null,'dimension',l_oo);

Line 733: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',null,'dimension',l_oo);

729: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
730: l_dim varchar2(200);
731: l_dim_list dbms_sql.varchar2_table;
732: Begin
733: bsc_aw_md_api.get_bsc_olap_object(null,'dimension level',null,'dimension',l_oo);
734: for i in 1..l_oo.count loop
735: if bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',') is null then --this is not a calendar dim level
736: bsc_aw_md_api.get_dim_for_level(l_oo(i).object,l_dim);
737: bsc_aw_utility.merge_value(l_dim_list,l_dim);

Line 750: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;

746: raise;
747: End;
748:
749: procedure upgrade_load_sync_all_dim(p_dim varchar2) is
750: l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
751: l_dim_level_delete dim_level_delete_tv;
752: l_delete_flag boolean;
753: Begin
754: if g_debug then

Line 758: bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);

754: if g_debug then
755: log('upgrade_load_sync_all_dim '||p_dim);
756: end if;
757: /*add delete values into the dim levels */
758: bsc_aw_md_api.get_bsc_olap_object(p_dim,'dimension',p_dim,'dimension',l_oo);
759: if l_oo.count>0 then
760: load_dim_delete(p_dim,l_oo(1).property1,l_dim_level_delete,l_delete_flag);
761: if l_delete_flag then
762: merge_delete_values_to_levels(l_dim_level_delete);

Line 767: bsc_aw_md_api.get_bsc_olap_object(null,'dml program',p_dim,'dimension',l_oo);

763: end if;
764: end if;
765: /*refresh the dim */
766: l_oo.delete;
767: bsc_aw_md_api.get_bsc_olap_object(null,'dml program',p_dim,'dimension',l_oo);
768: for i in 1..l_oo.count loop
769: if l_oo(i).olap_object_type='dml program initial load' then
770: begin
771: bsc_aw_dbms_aw.execute('call '||l_oo(i).object);