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.
PACKAGE: APPS.BSC_AW_CALENDAR

Source


1 package BSC_AW_CALENDAR AS
2 /*$Header: BSCAWCAS.pls 120.8 2006/01/30 15:54 vsurendr noship $*/
3 --program runtime parameters
4 g_debug boolean;
5 g_stmt varchar2(10000);
6 g_commands dbms_sql.varchar2_table;
7 g_init boolean;
8 ---types-----------------------------------------------------------
9 type periodicity_r is record(
10 db_column_name varchar2(40),
11 periodicity_id number,
12 periodicity_type number,--of 9=day, =1 is year
13 source varchar2(400),
14 dim_name varchar2(400), --this is the name of the dim in AW
15 aw_time_dim_name varchar2(400), --this is the corresponding AW time dim like aw_day or aw_month
16 aw_bsc_aw_rel_name varchar2(400), --given bsc period value, what is the aw time value
17 aw_aw_bsc_rel_name varchar2(400), --given aw time value, what is the bsc time value
18 property varchar2(2000)
19 );
20 type periodicity_tb is table of periodicity_r index by pls_integer;
21 --
22 type parent_child_r is record(
23 parent number,
24 parent_dim_name varchar2(300),
25 child number,
26 child_dim_name varchar2(300)
27 );
28 type parent_child_tb is table of parent_child_r index by pls_integer;
29 --
30 type misc_r is record(
31 object_name varchar2(300),  --used for olap table function
32 object_type varchar2(100),
33 datatype varchar2(40)
34 );
35 type misc_tb is table of misc_r index by pls_integer;
36 --
37 /*
38 end_period_relation_name is 2 axis. X is periods like day,month etc. Y is level names
39 given a month on X and Y is "day", rel gives end period
40 SQL> ------------------------------------------------------BSC_DBI_ENT.REL_BALANCE-----------------------------------------
41 ------------------------------------------------------------BSC_DBI_ENT--------------------------------------------------
42 BSC_DBI_ENT.LE <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH
43 <BSC_MONTH <BSC_MONTH
44 VELS           : 1.2004>  : 2.2004>  : 3.2004>  : 4.2004>  : 5.2004>  : 6.2004>  : 7.2004>  : 8.2004>  : 9.2004>  : 10.2004> :
45 11.2004> : 12.2004>
46 -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
47 ---------- ----------
48 DAY            <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:
49 <BSC_DAY:  <BSC_DAY:
50 31.2004>   60.2004>   91.2004>   121.2004>  152.2004>  182.2004>  213.2004>  244.2004>  274.2004>  305.2004>  335.2004>  366.2004>
51 
52 OR
53 QTR end periods as DAY
54 SQL> ----------BSC_DBI_ENT.REL_BALANCE----------
55 ----------------BSC_DBI_ENT----------------
56 <BSC_QUART <BSC_QUART <BSC_QUART <BSC_QUART
57 BSC_DBI_ENT.LE    ER:        ER:        ER:        ER:
58 VELS            1.2004>    2.2004>    3.2004>    4.2004>
59 -------------- ---------- ---------- ---------- ----------
60 DAY            <BSC_DAY:  <BSC_DAY:  <BSC_DAY:  <BSC_DAY:
61 91.2004>   182.2004>  274.2004>  366.2004>
62 
63 OR
64 QTR end period as MONTH
65 SQL> ----------BSC_DBI_ENT.REL_BALANCE----------
66 ----------------BSC_DBI_ENT----------------
67 <BSC_QUART <BSC_QUART <BSC_QUART <BSC_QUART
68 BSC_DBI_ENT.LE    ER:        ER:        ER:        ER:
69 VELS            1.2004>    2.2004>    3.2004>    4.2004>
70 -------------- ---------- ---------- ---------- ----------
71 MONTH          <BSC_MONTH <BSC_MONTH <BSC_MONTH <BSC_MONTH
72 : 3.2004>  : 6.2004>  : 9.2004>  : 12.2004>
73 
74 
75 */
76 type calendar_r is record(
77 calendar_id number,
78 property varchar2(1000),
79 periodicity periodicity_tb,
80 parent_child parent_child_tb,
81 dim_name varchar2(300),
82 relation_name varchar2(300),
83 denorm_relation_name varchar2(300), --used for balance aggregations during kpi load
84 levels_name varchar2(300), --this is parent.child format
85 end_period_relation_name varchar2(300),--this relation is used for balance measures
86 end_period_levels_name varchar2(300), --this is just the level names. also used for denorm relation
87 load_program varchar2(300),
88 kpi_for_dim bsc_aw_adapter_dim.kpi_for_dim_tb,
89 misc_object misc_tb
90 );
91 type calendar_tb is table of calendar_r index by pls_integer;
92 ---variables--------------------------------------------------------
93 --procedures-------------------------------------------------------
94 procedure create_calendar(p_calendar number,p_options varchar2,p_affected_kpi out nocopy dbms_sql.varchar2_table);
95 procedure create_calendar_objects(
96 p_calendar in out nocopy calendar_r);
97 procedure create_calendar_program(
98 p_calendar in out nocopy calendar_r
99 );
100 function get_periodicity_dim_name(p_periodicity periodicity_tb, p_periodicity_id number) return varchar2;
101 procedure normalize_per_relation(p_calendar in out nocopy calendar_r);
102 procedure dmp_calendar(p_calendar calendar_r);
103 procedure create_calendar_metadata(p_calendar calendar_r);
104 procedure get_kpi_for_calendar(p_calendar in out nocopy calendar_r);
105 procedure set_calendar_properties(p_calendar in out nocopy calendar_r);
106 procedure attach_workspace(p_options varchar2);
107 procedure get_calendar_current_year(p_calendar number,p_year out nocopy number);
108 procedure load_calendar(p_calendar number,p_options varchar2);
109 procedure get_all_lower_periodicities(
110 p_periodicity periodicity_r,
111 p_calendar calendar_r,
112 p_lower_periodicities in out nocopy periodicity_tb
113 );
114 procedure get_child_periodicities(
115 p_periodicity periodicity_r,
116 p_calendar calendar_r,
117 p_lower_periodicities out nocopy periodicity_tb
118 );
119 function get_periodicity_r(
120 p_periodicity_id number,
121 p_periodicities periodicity_tb
122 ) return periodicity_r;
123 procedure check_calendar_create(
124 p_calendar calendar_r,
125 p_recreate out nocopy varchar2,
126 p_affected_kpi out nocopy dbms_sql.varchar2_table);
127 procedure drop_calendar_objects(p_calendar_name varchar2,p_object_type varchar2,p_affected_kpi out nocopy dbms_sql.varchar2_table);
128 function get_calendar_name(p_calendar number) return varchar2 ;
129 procedure purge_calendar(p_calendar number,p_options varchar2) ;
130 procedure create_calendar(p_calendar number,p_affected_kpi out nocopy dbms_sql.varchar2_table);
131 function check_calendar_loaded(p_calendar number) return varchar2;
132 procedure load_calendar(p_calendar number);
133 procedure purge_calendar(p_calendar number);
134 procedure get_bsc_calendar_data(
135 p_calendar in out nocopy calendar_r);
136 procedure set_aw_object_names(p_calendar in out nocopy calendar_r);
137 procedure get_missing_periodicity(
138 p_calendar_dim varchar2,
139 p_periodicity_dim in out nocopy dbms_sql.varchar2_table,
140 p_lowest_level out nocopy dbms_sql.varchar2_table
141 );
142 function is_child_present(
143 p_parent varchar2,
144 p_periodicity_dim dbms_sql.varchar2_table,
145 p_parent_child parent_child_tb) return boolean;
146 procedure get_missing_level_down(
147 p_parent varchar2,
148 p_periodicity_dim dbms_sql.varchar2_table,
149 p_parent_child parent_child_tb,
150 p_missing_levels_in dbms_sql.varchar2_table,
151 p_missing_levels_out out nocopy dbms_sql.varchar2_table,
152 p_found in out nocopy boolean --indicates if child is found and its time to stop
153 );
154 procedure get_calendar_parent_child(p_calendar_dim varchar2,p_parent_child out nocopy parent_child_tb);
155 function is_parent_present(
156 p_child varchar2,
157 p_periodicity_dim dbms_sql.varchar2_table,
158 p_parent_child parent_child_tb) return boolean ;
159 procedure get_missing_level_up(
160 p_child varchar2,
161 p_periodicity_dim dbms_sql.varchar2_table,
162 p_parent_child parent_child_tb,
163 p_missing_levels_in dbms_sql.varchar2_table,
164 p_missing_levels_out out nocopy dbms_sql.varchar2_table,
165 p_found in out nocopy boolean --indicates if child is found and its time to stop
166 ) ;
167 procedure lock_calendar_objects(p_calendar number);
168 procedure get_calendar_objects_to_lock(p_calendar number,p_lock_objects out nocopy dbms_sql.varchar2_table);
169 procedure correct_calendar(p_calendar calendar_r,p_recreate out nocopy varchar2);
170 procedure create_calendar(p_calendar number,p_options varchar2);
171 function compare_pc_relations(p_pc_1 parent_child_tb,p_pc_2 parent_child_tb) return number;
172 procedure get_parent_periodicities(
173 p_periodicity periodicity_r,
174 p_calendar calendar_r,
175 p_upper_periodicities out nocopy periodicity_tb
176 );
177 procedure get_all_upper_periodicities(
178 p_periodicity periodicity_r,
179 p_calendar calendar_r,
180 p_upper_periodicities in out nocopy periodicity_tb
181 );
182 procedure get_calendar_periodicities(p_calendar_dim varchar2,p_periodicity out nocopy periodicity_tb);
183 procedure get_calendar(p_calendar_name varchar2,p_calendar out nocopy calendar_r);
184 procedure upgrade(p_new_version number,p_old_version number);
185 procedure reimplement_all_calendars;
186 procedure reimplement_calendar(p_calendar_id number);
187 --procedures-------------------------------------------------------
188 procedure init_all;
189 procedure log(p_message varchar2);
190 procedure log_n(p_message varchar2);
191 -------------------------------------------------------------------
192 
193 END BSC_AW_CALENDAR;