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 BODY: APPS.BSC_AOP_TPLATE

Source


1 PACKAGE BODY BSC_AOP_TPLATE AS
2 /* $Header: BSCUAOPB.pls 115.10 2003/06/20 22:24:09 meastmon ship $ */
3 
4 
5 G_PF_Tbl		BSC_TEMPLATE.Proj_Field_Tbl_Type;
6 G_PD_Tbl		BSC_TEMPLATE.Proj_Data_Tbl_Type;
7 G_Cal_Tbl		BSC_TEMPLATE.Cal_Tbl_Type;
8 
9 /*===========================================================================+
10 |
11 |   Name:          Create_Analysis_Options
12 |
13 |   Description:   To create analysis options
14 |
15 |   History:
16 |     	02-APR-1999   Alex Yang             Created.
17 |    12/22/1999	  Henry Camacho	Modified to Model 4.0
18 +============================================================================*/
19 
20 Function Create_Analysis_Options
21 Return Boolean
22 Is
23 	l_system_type		varchar2(60);
24 	is_cross_template	boolean := FALSE;
25 
26   	l_num_of_options 	number;
27   	l_num_of_data    	number;
28 
29 	l_debug_stmt		varchar2(2000) := 'DEBUG: ';
30 
31 Begin
32 
33   -- check template type (Tab or Cross)
34 
35   Select PROPERTY_VALUE
36   Into   l_system_type
37   From   BSC_SYS_INIT
38   Where  PROPERTY_CODE = 'MODEL_TYPE';
39 
40   if (l_system_type = '1') then
41     l_num_of_options := 4;
42     l_num_of_data    := 4;
43   else
44     l_num_of_options := 1;
45     l_num_of_data    := 1;
46   end if;
47 
48   G_PF_Tbl.Delete;
49   G_PD_Tbl.Delete;
50 
51   G_PF_Tbl(0).Measure_id     := 1;
52   G_PF_Tbl(0).Measure_col    := 'Amount';
53   G_PF_Tbl(0).Operation	     := 'SUM';
54   G_PF_Tbl(0).Type	     := 0;
55   G_PF_Tbl(0).Min_Actual     := 1000;
56   G_PF_Tbl(0).Max_Actual     := 2000;
57   G_PF_Tbl(0).Min_Plan 	     := 1000;
58   G_PF_Tbl(0).Max_Plan 	     := 2000;
59   G_PF_Tbl(0).Style	     := 1;
60   G_PF_Tbl(0).Insert_var     := TRUE;
61 
62   -- For Cross only
63 
64   G_PF_Tbl(1).Measure_id     := 2;
65   G_PF_Tbl(1).Measure_col    := 'Completed';
66   G_PF_Tbl(1).Operation	     := 'SUM';
67   G_PF_Tbl(1).Type	     := 0;
68   G_PF_Tbl(1).Min_Actual     := 0;
69   G_PF_Tbl(1).Max_Actual     := 1;
70   G_PF_Tbl(1).Min_Plan 	     := 0;
71   G_PF_Tbl(1).Max_Plan 	     := 1;
72   G_PF_Tbl(1).Style	     := 1;
73   G_PF_Tbl(1).Insert_var     := TRUE;
74 
75   G_PF_Tbl(2).Measure_id     := 3;
76   G_PF_Tbl(2).Measure_col    := 'Cost';
77   G_PF_Tbl(2).Operation	     := 'SUM';
78   G_PF_Tbl(2).Type	     := 0;
79   G_PF_Tbl(2).Min_Actual     := 10000;
80   G_PF_Tbl(2).Max_Actual     := 15000;
81   G_PF_Tbl(2).Min_Plan 	     := 10000;
82   G_PF_Tbl(2).Max_Plan 	     := 15000;
83   G_PF_Tbl(2).Style	     := 1;
84   G_PF_Tbl(2).Insert_var     := TRUE;
85 
86   G_PF_Tbl(3).Measure_id     := 4;
87   G_PF_Tbl(3).Measure_col    := 'XXX';
88   G_PF_Tbl(3).Operation	     := 'SUM';
89   G_PF_Tbl(3).Type	     := 0;
90   G_PF_Tbl(3).Min_Actual     := 1000;
91   G_PF_Tbl(3).Max_Actual     := 2000;
92   G_PF_Tbl(3).Min_Plan 	     := 1500;
93   G_PF_Tbl(3).Max_Plan 	     := 2000;
94   G_PF_Tbl(3).Style	     := 2;
95   G_PF_Tbl(3).Insert_var     := TRUE;
96 
97   -- Define MPROJ_DATA fields
98 
99   G_PD_Tbl(0).Dataset_id   := 1;
100   G_PD_Tbl(0).Measure1	   := 1;
101   G_PD_Tbl(0).Operation	   := NULL;
102   G_PD_Tbl(0).Measure2 	   := NULL;
103   G_PD_Tbl(0).Format 	   := 5;
104   G_PD_Tbl(0).Color_Method := 1;
105   G_PD_Tbl(0).Proj_Flag	   := 1;
106   G_PD_Tbl(0).name	   := 'Amount';
107   G_PD_Tbl(0).Help	   := 'Amount';
108   G_PD_Tbl(0).num_of_calc  := 11;
109 
110   -- for Cross only
111 
112   G_PD_Tbl(1).Dataset_id   := 2;
113   G_PD_Tbl(1).Measure1	   := 2;
114   G_PD_Tbl(1).operation	   := NULL;
115   G_PD_Tbl(1).Measure2 	   := NULL;
116   G_PD_Tbl(1).Format 	   := 0;
117   G_PD_Tbl(1).Color_Method := 1;
118   G_PD_Tbl(1).Proj_Flag	   := 1;
119   G_PD_Tbl(1).name	   := 'Completed';
120   G_PD_Tbl(1).Help	   := '% Completed';
121   G_PD_Tbl(1).num_of_calc  := 10;
122 
123   G_PD_Tbl(2).Dataset_id   := 3;
124   G_PD_Tbl(2).Measure1	   := 3;
125   G_PD_Tbl(2).Operation	   := NULL;
126   G_PD_Tbl(2).Measure2 	   := NULL;
127   G_PD_Tbl(2).Format 	   := 5;
128   G_PD_Tbl(2).Color_Method := 2;
129   G_PD_Tbl(2).Proj_Flag	   := 1;
130   G_PD_Tbl(2).name	   := 'Cost';
131   G_PD_Tbl(2).Help	   := 'Associated Cost by Project';
132   G_PD_Tbl(2).num_of_calc  := 8;
133 
134   G_PD_Tbl(3).Dataset_id   := 4;
135   G_PD_Tbl(3).Measure1	   := 4;
136   G_PD_Tbl(3).operation	   := NULL;
137   G_PD_Tbl(3).Measure2 	   := NULL;
138   G_PD_Tbl(3).Format 	   := 6;
139   G_PD_Tbl(3).Color_Method := 1;
140   G_PD_Tbl(3).Proj_Flag	   := 1;
141   G_PD_Tbl(3).name	   := 'XXXX';
142   G_PD_Tbl(3).Help	   := 'XXXX';
143   G_PD_Tbl(3).num_of_calc  := 7;
144 
145 
146   if (NOT create_option_relations(l_num_of_options, l_num_of_data)) then
147 	l_debug_stmt :=  bsc_apps.get_message('BSC_ERROR_CREATE_AO');
148 	Raise BSC_AOP_ERROR;
149   end if;
150 
151   Return(TRUE);
152 
153 EXCEPTION
154     WHEN BSC_AOP_ERROR THEN
155 	BSC_MESSAGE.Add(
156 		X_Message => l_debug_stmt,
157 		X_Source  => 'bsc_aop_tplate.create_analysis_options',
158 		X_Mode    => 'I');
159 
160 	Return(FALSE);
161 
162     WHEN OTHERS THEN
163 	BSC_MESSAGE.Add(
164 		X_Message => SQLERRM,
165 		X_Source => 'bsc_aop_tplate.create_analysis_options',
166 		X_Mode => 'I');
167 
168 	BSC_MESSAGE.Add(
169 		X_Message => l_debug_stmt,
170 		X_Source  => 'bsc_aop_tplate.create_analysis_options',
171 		x_type    => 3,
172 		X_Mode    => 'I');
173 
174 	Return(FALSE);
175 
176 End Create_Analysis_Options;
177 
178 
179 /*===========================================================================+
180 |
181 |   Name:          Create_Option_Relations
182 |
183 |   Description:   To configue analysis options
184 |
185 |   Parameters:
186 |	x_num_of_options	number of analysis options
187 |	x_num_of_data		number of data fields
188 |
189 |   History:
190 |     	02-APR-1999   Alex Yang             Created.
191 |    12/22/1999	  Henry Camacho	Modified to Model 4.0
192 +============================================================================*/
193 Function Create_Option_Relations(
194 		x_num_of_options	IN	Number,
195 		x_num_of_data		IN	Number
196 ) Return Boolean
197 Is
198 	l_debug_stmt		varchar2(2000);
199         l_sql			varchar2(32700);
200 Begin
201 
202   G_Cal_Tbl(0).Calculation   := 0;		G_Cal_Tbl(0).EV0     := 2;
203   G_Cal_Tbl(1).Calculation   := 1;     		G_Cal_Tbl(1).EV0     := 2;
204   G_Cal_Tbl(2).Calculation   := 2;		G_Cal_Tbl(2).EV0     := 2;
205   G_Cal_Tbl(3).Calculation   := 3;		G_Cal_Tbl(3).EV0     := 0;
206   G_Cal_Tbl(4).Calculation   := 4;	        G_Cal_Tbl(4).EV0     := 0;
207   G_Cal_Tbl(5).Calculation   := 5;	        G_Cal_Tbl(5).EV0     := 2;
208   G_Cal_Tbl(6).Calculation   := 6;	        G_Cal_Tbl(6).EV0     := 0;
209   G_Cal_Tbl(7).Calculation   := 7;	        G_Cal_Tbl(7).EV0     := 0;
210   G_Cal_Tbl(8).Calculation   := 8;	        G_Cal_Tbl(8).EV0     := 0;
211   G_Cal_Tbl(9).Calculation   := 9;	        G_Cal_Tbl(9).EV0     := 0;
212   G_Cal_Tbl(10).Calculation  := 10;		G_Cal_Tbl(10).EV0    := 0;
213   G_Cal_Tbl(11).Calculation  := 20;	        G_Cal_Tbl(11).EV0    := 0;
214 
215 
216 
217   For i_option in 0 .. (x_num_of_options -1)
218   Loop
219     l_debug_stmt := 'Insert Into BSC_SYS_MEASURES .. Measure_id=' ||
220 			to_char(G_PF_Tbl(i_option).Measure_id);
221     Insert Into BSC_SYS_MEASURES (
222 	MEASURE_ID,
223  	MEASURE_COL,
224  	OPERATION,
225  	TYPE,
226  	MIN_ACTUAL_VALUE,
227  	MAX_ACTUAL_VALUE,
228  	MIN_BUDGET_VALUE,
229  	MAX_BUDGET_VALUE,
230  	RANDOM_STYLE,
231         CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE  )
232     Values (
233   	G_PF_Tbl(i_option).Measure_id,
234   	G_PF_Tbl(i_option).Measure_col,
235   	G_PF_Tbl(i_option).Operation,
236   	G_PF_Tbl(i_option).Type,
237   	G_PF_Tbl(i_option).Min_Actual,
238   	G_PF_Tbl(i_option).Max_Actual,
239   	G_PF_Tbl(i_option).Min_Plan,
240   	G_PF_Tbl(i_option).Max_Plan,
241   	G_PF_Tbl(i_option).Style,
242         1,SYSDATE,1,SYSDATE
243     );
244 
245     -- create Data Set
246 
247     if (G_PF_Tbl(i_option).insert_var) then
248 
249 	l_debug_stmt := 'Insert Into BSC_DB_MEASURE_COLS_TL .. ' ||
250 			'field_n=' || G_PF_Tbl(i_option).Measure_col;
251 
252 	--FEM -Template Translable
253     	l_sql := 'INSERT INTO  BSC_DB_MEASURE_COLS_TL '||
254 		 ' (MEASURE_COL,LANGUAGE,SOURCE_LANG,HELP,MEASURE_GROUP_ID,PROJECTION_ID,MEASURE_TYPE) ' ||
255 		 'SELECT '||
256 			''''||G_PF_Tbl(i_option).Measure_col||''' AS MEASURE_COL, '||
257 			'FEM.LANGUAGE AS LANGUAGE, '||
258 			'FEM.SOURCE_LANG AS SOURCE_LANG, '||
259 	 		'SUBSTR(FEM.MEANING,1,50) AS HELP, '||
260  			'-1 AS MEASURE_GROUP_ID, '||
261 			'3 AS PROJECTION_ID, '||
262 			'NULL AS MEASURE_TYPE '||
263 		'FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM '||
264 		'WHERE FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_DATASET_NAME'' AND '||
265 		      'FEM.LOOKUP_CODE = '''||G_PF_Tbl(i_option).Measure_id||'''';
266         BSC_APPS.Execute_Immediate(l_sql);
267     end if;
268   End Loop; -- analysis option loop
269 
270 
271   For i_data In 0 .. (x_num_of_data -1)
272   Loop
273     l_debug_stmt := 'Insert Into BSC_SYS_DATASETS_B .. data_code=' ||
274 			to_char(G_PD_Tbl(i_data).Dataset_id);
275 
276     Insert Into BSC_SYS_DATASETS_B (
277 	DATASET_ID,
278 	MEASURE_ID1,
279  	OPERATION,
280  	MEASURE_ID2,
281  	FORMAT_ID,
282  	COLOR_METHOD,
283  	PROJECTION_FLAG,
284         CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE )
285     Values (
286   	G_PD_Tbl(i_data).Dataset_id,
287   	G_PD_Tbl(i_data).Measure1,
288   	G_PD_Tbl(i_data).Operation,
289   	G_PD_Tbl(i_data).Measure2,
290   	G_PD_Tbl(i_data).Format,
291   	G_PD_Tbl(i_data).Color_Method,
292   	G_PD_Tbl(i_data).Proj_Flag,
293         1,SYSDATE,1,SYSDATE
294     );
295 
296     l_debug_stmt := 'Insert Into BSC_SYS_DATASETS_TL .. data_code=' ||
297 			to_char(G_PD_Tbl(i_data).Dataset_id);
298 
299     l_sql := 'INSERT INTO BSC_SYS_DATASETS_TL (DATASET_ID, LANGUAGE, SOURCE_LANG,'||
300              ' NAME, HELP) '||
301   	     'SELECT '||
302 		G_PD_Tbl(i_data).Dataset_id||' AS DATASET_ID, '||
303 		'FEM.LANGUAGE AS LANGUAGE, '||
304 		'FEM.SOURCE_LANG AS SOURCE_LANG, '||
305 		'SUBSTR(FEM.MEANING,1,20) AS NAME, '||
306 		'SUBSTR(FEM.MEANING,1,80) AS HELP '||
307 	     'FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM '||
308 	     'WHERE FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_DATASET_NAME'' AND '||
309 		   'FEM.LOOKUP_CODE = '''||G_PD_Tbl(i_data).Dataset_id||'''';
310     BSC_APPS.Execute_Immediate(l_sql);
311 
312     For i_proj_calc In 0 .. (G_PD_Tbl(i_data).num_of_calc -1)
313     Loop
314 
315         l_debug_stmt := 'Insert Into BSC_SYS_DATASET_CALC .. data_code=' ||
316 			to_char(G_PD_Tbl(i_data).Dataset_id);
317 
318 	IF G_Cal_Tbl(i_proj_calc).EV0 = 0  THEN
319 		Insert Into BSC_SYS_DATASET_CALC (
320 			DATASET_ID,
321 			DISABLED_CALC_ID
322 	        )
323 		Values (
324 			G_PD_Tbl(i_data).Dataset_id,
325 			G_Cal_Tbl(i_proj_calc).Calculation
326      		);
327 	END IF;
328 
329     End loop; -- calculation field loop
330 
331   End Loop; -- analysis option data loop
332 
333   Return(TRUE);
334 
335 EXCEPTION
336 
337     WHEN OTHERS THEN
338 	BSC_MESSAGE.Add(
339 		X_Message => SQLERRM,
340 		X_Source => 'bsc_aop_tplate.create_option_relations',
341 		X_Mode => 'I');
342 
343 	BSC_MESSAGE.Add(
344 		X_Message => l_debug_stmt,
345 		X_Source  => 'bsc_template.create_crx_template',
346 		x_type    => 3,
347 		X_Mode    => 'I');
348 
349 	Return(FALSE);
350 
351 End Create_Option_Relations;
352 
353 
354 END BSC_AOP_TPLATE;