[Home] [Help]
PACKAGE BODY: APPS.QPR_COPY_PRICE_PLAN
Source
1 PACKAGE BODY QPR_COPY_PRICE_PLAN AS
2 /* $Header: QPRUCPPB.pls 120.1 2007/11/06 11:15:29 bhuchand noship $ */
3
4 NO_PPLAN_ID exception;
5 NO_PPLAN_DATA exception;
6 PRAGMA EXCEPTION_INIT(NO_PPLAN_DATA, -100);
7 ERR_IN_DEFN exception;
8
9 g_request_id number;
10 g_src_pplan_id number := 0;
11 g_new_pp_id number := 0;
12 g_sys_date date;
13 g_user_id number;
14 g_login_id number;
15 g_prg_appl_id number;
16 g_prg_id number;
17 g_copy_det_frm_tmpl varchar2(1);
18
19 type char1_type is table of varchar2(1) index by PLS_INTEGER;
20 type char30_type is table of varchar2(30) index by PLS_INTEGER;
21 type char60_type is table of varchar2(50) index by PLS_INTEGER;
22 type char240_type is table of varchar2(240) index by PLS_INTEGER;
23 type char1000_type is table of varchar2(1000) index by PLS_INTEGER;
24 type num_type is table of number index by PLS_INTEGER;
25 type char_type is table of varchar2(50) index by varchar2(30);
26
27 type val_out_type is record (ID num_type, PPA_CODE char60_type);
28
29 type dim_rec_type is record(SRC_PRICE_PLAN_DIM_ID num_type,
30 TMPL_PRICE_PLAN_DIM_ID num_type,
31 DIM_CODE char60_type,
32 DIM_PPA_CODE char30_type,
33 DIM_SEQ_NUM num_type,
34 MAND_DIM_FLAG char1_type,
35 TIME_DIM_FLAG char1_type,
36 LOB_ENABLED_FLAG char1_type,
37 DIM_SHORT_NAME char30_type,
38 DIM_LONG_NAME char240_type,
39 DIM_PLURAL_NAME char240_type,
40 NATIVE_KEY_FLAG char1_type,
41 MEASURE_DIM_FLAG char1_type,
42 SPARSE_FLAG char1_type,
43 LOWEST_LVL char30_type,
44 LIST_PP_FLAG char1_type);
45
46 type dim_attr_rec_type is record(DIM_ATTR_ID num_type,
47 ATTR_PPA_CODE char30_type,
48 ATTR_SHORT_NAME char30_type,
49 ATTR_LONG_NAME char240_type,
50 ATTR_PLURAL_NAME char240_type,
51 ATTR_CLASSIFICATION char240_type,
52 ATTR_DATA_TYPE char30_type,
53 DEFAULT_ORDER_FLAG char1_type);
54
55 type dim_hier_type is record(HIERARCHY_ID num_type,
56 HIERARCHY_PPA_CODE char30_type,
57 HIER_SHORT_NAME char30_type,
58 HIER_LONG_NAME char240_type,
59 HIER_PLURAL_NAME char240_type,
60 HIER_TYPE_CODE char30_type,
61 HIER_DEFAULT_ORDER char240_type,
62 DEFAULT_FLAG char1_type,
63 CALENDAR_CODE char30_type);
64
65 type hier_lvl_type is record(HIERARCHY_LEVEL_ID num_type,
66 LEVEL_PPA_CODE char30_type,
67 LEVEL_SEQ_NUM num_type,
68 LVL_SHORT_NAME char30_type,
69 LVL_LONG_NAME char240_type,
70 LVL_PLURAL_NAME char240_type,
71 MAPPING_VIEW_NAME char30_type,
72 MAP_COLUMN char30_type,
73 USER_MAPPING_VIEW_NAME char30_type,
74 USER_MAP_COLUMN char30_type);
75
76 type lvl_attr_type is record(LEVEL_ATTR_ID num_type,
77 HIERARCHY_LEVEL_ID num_type,
78 DIM_ATTR_ID num_type,
79 MAPPING_VIEW_NAME char30_type,
80 MAP_COLUMN char30_type,
81 USER_MAPPING_VIEW_NAME char30_type,
82 USER_MAP_COLUMN char30_type);
83
84 type cub_rec_type is record(SRC_CUBE_ID num_type,
85 TMPL_CUBE_ID num_type,
86 CUBE_PPA_CODE char30_type,
87 CUBE_CODE char60_type,
88 CUBE_SHORT_NAME char30_type,
89 CUBE_LONG_NAME char240_type,
90 CUBE_PLURAL_NAME char240_type,
91 CUBE_AUTO_SOLVE_FLAG char1_type,
92 DEFAULT_DATA_TYPE char30_type,
93 PARTITION_HIER char30_type,
94 PARTITION_LEVEL char30_type,
95 SPARSE_TYPE_CODE char30_type,
96 USE_GLOBAL_INDEX_FLAG char1_type,
97 AGGMAP_NAME char30_type,
98 AGGMAP_CACHE_STORE char30_type,
99 AGGMAP_CACHE_NA char30_type);
100
101 type cub_meas_type is record(MEASURE_ID num_type,
102 MEASURE_PPA_CODE char30_type,
103 MEAS_CREATION_SEQ_NUM num_type,
104 MEAS_SHORT_NAME char30_type,
105 MEAS_LONG_NAME char240_type,
106 MEAS_PLURAL_NAME char240_type,
107 MEAS_TYPE char30_type,
108 MEAS_DATA_TYPE char30_type,
109 MEAS_AUTO_SOLVE char30_type,
110 CAL_MEAS_EXPRESSION_TEXT char1000_type,
111 MAPPING_VIEW_NAME char30_type,
112 MAP_COLUMN char30_type,
113 USER_MAPPING_VIEW_NAME char30_type,
114 USER_MAP_COLUMN char30_type,
115 AGGMAP_NAME char30_type,
116 MEAS_FOLD_SHORT_NAME char30_type,
117 MEAS_FOLD_LONG_NAME char240_type,
118 MEAS_FOLD_PLURAL_NAME char240_type);
119
120 type cub_dims_type is record(CUBE_DIM_ID num_type, PRICE_PLAN_DIM_ID num_type,
121 AGGMAP_NAME char30_type,
122 DIM_OPCODE char30_type, DIM_SEQ_NUM num_type,
123 MAPPING_VIEW_NAME char30_type,
124 MAP_COLUMN char30_type,
125 USER_MAPPING_VIEW_NAME char30_type,
126 USER_MAP_COLUMN char30_type,
127 SET_LEVEL_FLAG char1_type,
128 DIM_EXPRESSION char240_type,
129 DIM_EXPRESSION_TYPE char30_type,
130 WEIGHTED_MEASURE_FLAG char1_type,
131 WEIGHT_MEASURE_NAME char30_type,
132 WNAFILL char30_type,
133 DIVIDE_BY_ZERO_FLAG char1_type,
134 DECIMAL_OVERFLOW_FLAG char1_type,
135 NASKIP_FLAG char1_type);
136
137 type cub_meas_aggr_type is record(MEASURE_ID num_type,
138 CUBE_DIM_ID num_type,
139 AGGMAP_NAME char30_type,
140 AGGMAP_CACHE_STORE char30_type,
141 AGGMAP_CACHE_NA char30_type,
142 DIM_OPCODE char30_type,
143 SET_LEVEL_FLAG char1_type,
144 OVERRIDE_FLAG char1_type,
145 DIM_EXPRESSION char240_type,
146 DIM_EXPRESSION_TYPE char30_type,
147 WEIGHTED_MEASURE_FLAG char1_type,
148 WEIGHT_MEASURE_NAME char30_type,
149 WNAFILL char30_type,
150 DIVIDE_BY_ZERO_FLAG char1_type,
151 DECIMAL_OVERFLOW_FLAG char1_type,
152 NASKIP_FLAG char1_type);
153
154 type cub_set_lvl_type is record(CUBE_DIM_ID num_type,
155 MEASURE_ID num_type,
156 LEVEL_SHORT_NAME char30_type,
157 DIM_EXPRESSION_TYPE char30_type,
158 AGGMAP_NAME char30_type,
159 SET_LEVEL_FLAG char1_type);
160 -- The following are records that hold data for individual PP tables for given
161 -- src price plan id.
162 r_dim_val dim_rec_type;
163 r_dim_attr_val dim_attr_rec_type;
164 r_dim_hier_val dim_hier_type;
165 r_hier_lvl_val hier_lvl_type;
166 r_lvl_attr_val lvl_attr_type;
167 r_cub_val cub_rec_type;
168 r_cub_meas_val cub_meas_type;
169 r_cub_dims_val cub_dims_type;
170 r_cub_meas_aggr cub_meas_aggr_type;
171 r_cub_int_maggr cub_meas_aggr_type;
172 r_cub_set_lvl cub_set_lvl_type;
173 r_int_set_lvl cub_set_lvl_type;
174
175 function insert_dim_values return val_out_type is
176 rec_dim_out val_out_type;
177 begin
178 forall i in r_dim_val.SRC_PRICE_PLAN_DIM_ID.first..
179 r_dim_val.SRC_PRICE_PLAN_DIM_ID.last
180 insert into qpr_dimensions(PRICE_PLAN_DIM_ID, PRICE_PLAN_ID,
181 DIM_CODE, DIM_PPA_CODE,
182 DIM_SEQ_NUM, MAND_DIM_FLAG,
183 TIME_DIM_FLAG , LOB_ENABLED_FLAG ,
184 DIM_SHORT_NAME , DIM_LONG_NAME ,
185 DIM_PLURAL_NAME , NATIVE_KEY_FLAG ,
186 MEASURE_DIM_FLAG , SPARSE_FLAG ,
187 LOWEST_LVL , LIST_PRICE_PLAN_FLAG ,
188 TEMPLATE_FLAG, INCLUDE_FLAG,
189 CREATION_DATE, CREATED_BY,
190 LAST_UPDATE_DATE, LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
192 PROGRAM_ID, REQUEST_ID)
193 values(qpr_dimensions_s.nextval, g_new_pp_id,
194 r_dim_val.DIM_PPA_CODE(i) ||
195 to_char(g_new_pp_id)|| '_D',
196 r_dim_val.DIM_PPA_CODE(i),
197 r_dim_val.DIM_SEQ_NUM(i),
198 r_dim_val.MAND_DIM_FLAG(i),
199 r_dim_val.TIME_DIM_FLAG(i),
200 r_dim_val.LOB_ENABLED_FLAG(i),
201 r_dim_val.DIM_SHORT_NAME(i),
202 r_dim_val.DIM_LONG_NAME(i),
203 r_dim_val.DIM_PLURAL_NAME(i),
204 r_dim_val.NATIVE_KEY_FLAG(i),
205 r_dim_val.MEASURE_DIM_FLAG(i),
206 r_dim_val.SPARSE_FLAG(i),
207 r_dim_val.LOWEST_LVL(i),
208 r_dim_val.LIST_PP_FLAG(i),
209 'N', 'Y',
210 g_sys_date, g_user_id, g_sys_date, g_user_id,
211 g_login_id, g_prg_appl_id,
212 g_prg_id, g_request_id)
213 returning PRICE_PLAN_DIM_ID, DIM_CODE bulk collect into rec_dim_out;
214
215 return(rec_dim_out);
216 exception
217 when OTHERS then
218 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING DIMENSIONS...');
219 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
220 raise;
221 end insert_dim_values;
222
223 function insert_dim_attr_values(p_ppdim_id in number)
224 return num_type is
225 l_dim_attr_id number;
226 t_old_new_dim_attr num_type;
227 rec_dim_attr_val val_out_type;
228 begin
229 forall i in r_dim_attr_val.ATTR_PPA_CODE.first..
230 r_dim_attr_val.ATTR_PPA_CODE.last
231 insert into qpr_dim_attributes(DIM_ATTR_ID, PRICE_PLAN_DIM_ID,
232 PRICE_PLAN_ID,
233 ATTR_PPA_CODE, ATTR_SHORT_NAME,
234 ATTR_LONG_NAME, ATTR_PLURAL_NAME,
235 ATTR_CLASSIFICATION, ATTR_DATA_TYPE,
236 DEFAULT_ORDER_FLAG,TEMPLATE_FLAG,
237 CREATION_DATE, CREATED_BY,
238 LAST_UPDATE_DATE, LAST_UPDATED_BY,
239 LAST_UPDATE_LOGIN,
240 PROGRAM_APPLICATION_ID, PROGRAM_ID,
241 REQUEST_ID)
242 values(qpr_dim_attributes_s.nextval, p_ppdim_id,
243 g_new_pp_id, r_dim_attr_val.ATTR_PPA_CODE(i),
244 r_dim_attr_val.ATTR_SHORT_NAME(i),
245 r_dim_attr_val.ATTR_LONG_NAME(i),
246 r_dim_attr_val.ATTR_PLURAL_NAME(i),
247 r_dim_attr_val.ATTR_CLASSIFICATION(i),
248 r_dim_attr_val.ATTR_DATA_TYPE(i),
249 r_dim_attr_val.DEFAULT_ORDER_FLAG(i), 'N',
250 g_sys_date, g_user_id, g_sys_date, g_user_id,
251 g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
252 returning DIM_ATTR_ID, ATTR_PPA_CODE bulk collect into rec_dim_attr_val;
253
254 for i in r_dim_attr_val.ATTR_PPA_CODE.first..
255 r_dim_attr_val.ATTR_PPA_CODE.last loop
256 l_dim_attr_id := r_dim_attr_val.DIM_ATTR_ID(i);
257 t_old_new_dim_attr(l_dim_attr_id) := rec_dim_attr_val.ID(i);
258 end loop;
259
260 return(t_old_new_dim_attr);
261 exception
262 when OTHERS then
263 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING DIMENSION ATTRIBUTES..');
264 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
265 raise;
266 end insert_dim_attr_values;
267
268 function insert_hier_values(p_ppdim_id in number) return val_out_type is
269 rec_hier_out val_out_type;
270 begin
271 forall i in r_dim_hier_val.HIERARCHY_PPA_CODE.first..
272 r_dim_hier_val.HIERARCHY_PPA_CODE.last
273 insert into qpr_hierarchies(HIERARCHY_ID, PRICE_PLAN_DIM_ID,
274 HIERARCHY_PPA_CODE, PRICE_PLAN_ID,
275 HIER_SHORT_NAME, HIER_LONG_NAME,
276 HIER_PLURAL_NAME, HIER_TYPE_CODE,
277 HIER_DEFAULT_ORDER, DEFAULT_FLAG,
278 CALENDAR_CODE,TEMPLATE_FLAG,
279 CREATION_DATE,
280 CREATED_BY, LAST_UPDATE_DATE,
281 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
282 PROGRAM_APPLICATION_ID, PROGRAM_ID,
283 REQUEST_ID)
284 values(qpr_hierarchies_s.nextval, p_ppdim_id,
285 r_dim_hier_val.HIERARCHY_PPA_CODE(i),
286 g_new_pp_id, r_dim_hier_val.HIER_SHORT_NAME(i),
287 r_dim_hier_val.HIER_LONG_NAME(i),
288 r_dim_hier_val.HIER_PLURAL_NAME(i),
289 r_dim_hier_val.HIER_TYPE_CODE(i),
290 r_dim_hier_val.HIER_DEFAULT_ORDER(i),
291 r_dim_hier_val.DEFAULT_FLAG(i),
292 r_dim_hier_val.CALENDAR_CODE(i),'N',
293 g_sys_date, g_user_id, g_sys_date, g_user_id,
294 g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
295 returning HIERARCHY_ID, HIERARCHY_PPA_CODE bulk collect into rec_hier_out;
296
297 return(rec_hier_out);
298 exception
299 when OTHERS then
300 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING HIERARCHIES...');
301 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
302 raise;
303 end insert_hier_values;
304
305 function insert_hier_lvl_val(p_hier_id in number) return num_type is
306 rec_lvl_out val_out_type;
307 t_old_new_lvl num_type;
308 begin
309 forall i in r_hier_lvl_val.HIERARCHY_LEVEL_ID.first..
310 r_hier_lvl_val.HIERARCHY_LEVEL_ID.last
311 insert into qpr_hier_levels(HIERARCHY_LEVEL_ID, HIERARCHY_ID,
312 LEVEL_PPA_CODE, LEVEL_SEQ_NUM,
313 PRICE_PLAN_ID, LVL_SHORT_NAME,
314 LVL_LONG_NAME, LVL_PLURAL_NAME,
315 MAPPING_VIEW_NAME, MAP_COLUMN,
316 USER_MAPPING_VIEW_NAME,
317 USER_MAP_COLUMN, TEMPLATE_FLAG,
318 INCLUDE_FLAG,CREATION_DATE,
319 CREATED_BY, LAST_UPDATE_DATE,
320 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
321 PROGRAM_APPLICATION_ID, PROGRAM_ID,
322 REQUEST_ID)
323 values(qpr_hier_levels_s.nextval, p_hier_id,
324 r_hier_lvl_val.LEVEL_PPA_CODE(i),
325 r_hier_lvl_val.LEVEL_SEQ_NUM(i),
326 g_new_pp_id, r_hier_lvl_val.LVL_SHORT_NAME(i),
327 r_hier_lvl_val.LVL_LONG_NAME(i),
328 r_hier_lvl_val.LVL_PLURAL_NAME(i),
329 r_hier_lvl_val.MAPPING_VIEW_NAME(i),
330 r_hier_lvl_val.MAP_COLUMN(i),
331 r_hier_lvl_val.USER_MAPPING_VIEW_NAME(i),
332 r_hier_lvl_val.USER_MAP_COLUMN(i),'N','Y',
333 g_sys_date, g_user_id, g_sys_date, g_user_id,
334 g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
335 returning HIERARCHY_LEVEL_ID, LEVEL_PPA_CODE bulk collect into rec_lvl_out;
336
337 for i in r_hier_lvl_val.HIERARCHY_LEVEL_ID.first..
338 r_hier_lvl_val.HIERARCHY_LEVEL_ID.last loop
339 t_old_new_lvl(r_hier_lvl_val.HIERARCHY_LEVEL_ID(i)) :=
340 rec_lvl_out.ID(i);
341 end loop;
342 return(t_old_new_lvl);
343 exception
344 when OTHERS then
345 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING HIERARCHY LEVELS...');
346 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
347 raise;
348 end insert_hier_lvl_val;
349
350 procedure insert_lvl_attributes is
351 begin
352 forall i in r_lvl_attr_val.LEVEL_ATTR_ID.first..
353 r_lvl_attr_val.LEVEL_ATTR_ID.last
354 insert into qpr_lvl_attributes(LEVEL_ATTR_ID, HIERARCHY_LEVEL_ID,
355 DIM_ATTR_ID,
356 PRICE_PLAN_ID, MAPPING_VIEW_NAME,
357 MAP_COLUMN, USER_MAPPING_VIEW_NAME,
358 USER_MAP_COLUMN, TEMPLATE_FLAG,
359 INCLUDED_FLAG,CREATION_DATE,
360 CREATED_BY, LAST_UPDATE_DATE,
361 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
362 PROGRAM_APPLICATION_ID, PROGRAM_ID,
363 REQUEST_ID)
364 values(qpr_lvl_attributes_s.nextval,
365 r_lvl_attr_val.HIERARCHY_LEVEL_ID(i),
366 r_lvl_attr_val.DIM_ATTR_ID(i),
367 g_new_pp_id,
368 r_lvl_attr_val.MAPPING_VIEW_NAME(i),
369 r_lvl_attr_val.MAP_COLUMN(i),
370 r_lvl_attr_val.USER_MAPPING_VIEW_NAME(i),
371 r_lvl_attr_val.USER_MAP_COLUMN(i),'N','Y',
372 g_sys_date,
373 g_user_id, g_sys_date, g_user_id,g_login_id, g_prg_appl_id,
374 g_prg_id, g_request_id);
375 exception
376 when OTHERS then
377 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING LEVEL ATTRIBUTES...');
378 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
379 raise;
380 end insert_lvl_attributes;
381
382 function insert_cube_data return val_out_type is
383 r_cub_val_out val_out_type;
384 begin
385 forall i in r_cub_val.CUBE_CODE.first..r_cub_val.CUBE_CODE.last
386 insert into qpr_cubes(CUBE_ID, CUBE_CODE, CUBE_PPA_CODE, PRICE_PLAN_ID,
387 CUBE_SHORT_NAME, CUBE_LONG_NAME,CUBE_PLURAL_NAME,
388 CUBE_AUTO_SOLVE_FLAG, DEFAULT_DATA_TYPE,
389 PARTITION_HIER,PARTITION_LEVEL, SPARSE_TYPE_CODE,
390 USE_GLOBAL_INDEX_FLAG, AGGMAP_NAME,
391 AGGMAP_CACHE_STORE, AGGMAP_CACHE_NA,
392 TEMPLATE_FLAG,
393 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
394 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
395 PROGRAM_APPLICATION_ID, PROGRAM_ID,
396 REQUEST_ID)
397 values(qpr_cubes_s.nextval,
398 -- 'QPR_'||r_cub_val.CUBE_PPA_CODE(i) || to_char(g_new_pp_id)
399 -- || '_CUBE',
400 r_cub_val.CUBE_CODE(i),
401 r_cub_val.CUBE_PPA_CODE(i), g_new_pp_id,
402 r_cub_val.CUBE_SHORT_NAME(i),
403 r_cub_val.CUBE_LONG_NAME(i),
404 r_cub_val.CUBE_PLURAL_NAME(i),
405 r_cub_val.CUBE_AUTO_SOLVE_FLAG(i),
406 r_cub_val.DEFAULT_DATA_TYPE(i),
407 r_cub_val.PARTITION_HIER(i),
408 r_cub_val.PARTITION_LEVEL(i),
409 r_cub_val.SPARSE_TYPE_CODE(i),
410 r_cub_val.USE_GLOBAL_INDEX_FLAG(i),
411 r_cub_val.AGGMAP_NAME(i), r_cub_val.AGGMAP_CACHE_STORE(i),
412 r_cub_val.AGGMAP_CACHE_NA(i),'N',
413 g_sys_date, g_user_id, g_sys_date, g_user_id,
414 g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
415 returning CUBE_ID, CUBE_CODE bulk collect into r_cub_val_out;
416
417 return(r_cub_val_out);
418 exception
419 when OTHERS then
420 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE DATA...');
421 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
422 raise;
423 end insert_cube_data;
424
425 function substitute_cube_code(p_calc_exp in varchar2) return varchar2 is
426 s_new_expression varchar2(1000);
427 s_final_expression varchar2(1000);
428 s_old_expression varchar2(1000);
429 s_cube_code varchar2(30);
430 l_oper_pos number;
431 l_plus_oper number;
432 l_minus_oper number;
433 l_div_oper number;
434 l_mult_oper number;
435 l_ctr number:= 0;
436 begin
437 s_old_expression := p_calc_exp;
438 s_final_expression := '';
439 loop
440 l_oper_pos := 0;
441
442 l_plus_oper := instrb(s_old_expression, '+', 1);
443 l_minus_oper := instrb(s_old_expression, '-', 1);
444 l_mult_oper := instrb(s_old_expression, '*', 1);
445 l_div_oper := instrb(s_old_expression, '/', 1);
446
447 if (l_plus_oper = 0) and (l_minus_oper = 0) and (l_mult_oper = 0 )
448 and (l_div_oper = 0) then
449 l_oper_pos := -1;
450 else
451 l_oper_pos := l_plus_oper;
452
453 if l_minus_oper > 0 and (l_oper_pos > l_minus_oper or l_oper_pos = 0) then
454 l_oper_pos := l_minus_oper;
455 end if;
456
457 if l_mult_oper > 0 and (l_oper_pos > l_mult_oper or l_oper_pos = 0) then
458 l_oper_pos := l_mult_oper;
459 end if;
460
461 if l_div_oper > 0 and (l_oper_pos > l_div_oper or l_oper_pos = 0) then
462 l_oper_pos := l_div_oper;
463 end if;
464 end if;
465
466 if l_oper_pos = -1 then
467 s_new_expression := s_old_expression;
468 else
469 s_new_expression := substr(s_old_expression, 1, l_oper_pos);
470 end if;
471
472 if instrb(s_new_expression, '.', 1) > 0 then
473 for j in r_cub_val.CUBE_PPA_CODE.first..r_cub_val.CUBE_PPA_CODE.last loop
474 s_cube_code := substr(r_cub_val.CUBE_CODE(j), 1,
475 instrb(r_cub_val.CUBE_CODE(j), g_new_pp_id || '_C') -1);
476 if trim(substr(s_new_expression, 1, instrb(s_new_expression, '.')-1)) =
477 s_cube_code then
478 s_final_expression := s_final_expression || r_cub_val.CUBE_CODE(j) ;
479 s_final_expression := s_final_expression || substr(s_new_expression,
480 instrb(s_new_expression, '.', 1));
481 exit;
482 end if;
483 end loop;
484 else
485 s_final_expression := s_final_expression || s_new_expression;
486 end if;
487
488 exit when (l_oper_pos = -1);
489
490 s_old_expression := substr(s_old_expression, l_oper_pos+1);
491 end loop;
492 fnd_file.put_line(fnd_file.log,s_final_expression);
493 return(s_final_expression);
494 end substitute_cube_code;
495
496 function insert_cub_meas(p_cube_id in number,p_from_pp_id in number,
497 p_cube_code in varchar2)
498 return num_type is
499 t_meas_old_new_ids num_type;
500 r_meas_val_out val_out_type;
501 s_calc_exp varchar2(1000);
502 s_folder_name varchar2(30) := substrb(p_cube_code, 1,
503 Instrb(p_cube_code, '_C')-1);
504 begin
505 for i in r_cub_meas_val.MEASURE_ID.first..r_cub_meas_val.MEASURE_ID.last loop
506 s_calc_exp := r_cub_meas_val.CAL_MEAS_EXPRESSION_TEXT(i);
507 if g_copy_det_frm_tmpl = 'Y' then
508 -- for j in r_cub_val.CUBE_PPA_CODE.first..r_cub_val.CUBE_PPA_CODE.last loop
509 -- s_calc_exp := replace(s_calc_exp,
510 -- substrb(r_cub_val.CUBE_CODE(j), 1,
511 -- (Instrb(r_cub_val.CUBE_CODE(j),s_srch_str)-1)),
512 -- r_cub_val.CUBE_CODE(j));
513 -- end loop;
514 if s_calc_exp is not null then
515 s_calc_exp := substitute_cube_code(s_calc_exp);
516 end if;
517 else
518 if s_calc_exp is not null then
519 s_calc_exp := replace(s_calc_exp, p_from_pp_id, g_new_pp_id);
520 end if;
521 end if;
522 r_cub_meas_val.CAL_MEAS_EXPRESSION_TEXT(i) := s_calc_exp;
523 end loop;
524
525 forall i in r_cub_meas_val.MEASURE_ID.first..r_cub_meas_val.MEASURE_ID.last
526 insert into qpr_measures(MEASURE_ID, CUBE_ID, PRICE_PLAN_ID,
527 MEASURE_PPA_CODE, MEAS_CREATION_SEQ_NUM,
528 MEAS_SHORT_NAME, MEAS_LONG_NAME,
529 MEAS_PLURAL_NAME, MEAS_TYPE, MEAS_DATA_TYPE,
530 MEAS_AUTO_SOLVE, CAL_MEAS_EXPRESSION_TEXT,
531 MAPPING_VIEW_NAME, MAP_COLUMN,
532 USER_MAPPING_VIEW_NAME, USER_MAP_COLUMN,
533 AGGMAP_NAME,
534 MEAS_FOLD_SHORT_NAME, MEAS_FOLD_LONG_NAME,
535 MEAS_FOLD_PLURAL_NAME,TEMPLATE_FLAG,
536 INCLUDE_FLAG,
537 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
538 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
539 PROGRAM_APPLICATION_ID, PROGRAM_ID,
540 REQUEST_ID)
541 values(qpr_measures_s.nextval, p_cube_id, g_new_pp_id,
542 r_cub_meas_val.MEASURE_PPA_CODE(i),
543 r_cub_meas_val.MEAS_CREATION_SEQ_NUM(i),
544 r_cub_meas_val.MEAS_SHORT_NAME(i),
545 r_cub_meas_val.MEAS_LONG_NAME(i),
546 r_cub_meas_val.MEAS_PLURAL_NAME(i),
547 r_cub_meas_val.MEAS_TYPE(i),
548 r_cub_meas_val.MEAS_DATA_TYPE(i),
549 r_cub_meas_val.MEAS_AUTO_SOLVE(i),
550 r_cub_meas_val.CAL_MEAS_EXPRESSION_TEXT(i),
551 r_cub_meas_val.MAPPING_VIEW_NAME(i),
552 r_cub_meas_val.MAP_COLUMN(i),
553 r_cub_meas_val.USER_MAPPING_VIEW_NAME(i),
554 r_cub_meas_val.USER_MAP_COLUMN(i),
555 r_cub_meas_val.AGGMAP_NAME(i),
556 s_folder_name,
557 s_folder_name,
558 s_folder_name, 'N', 'Y',
559 g_sys_date, g_user_id, g_sys_date, g_user_id,
560 g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
561 returning MEASURE_ID, MEASURE_PPA_CODE bulk collect into r_meas_val_out;
562
563 for i in r_cub_meas_val.MEASURE_ID.first..r_cub_meas_val.MEASURE_ID.last loop
564 t_meas_old_new_ids(r_cub_meas_val.MEASURE_ID(i)) := r_meas_val_out.ID(i);
565 end loop;
566 return t_meas_old_new_ids;
567 exception
568 when OTHERS then
569 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE MEASURES...');
570 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
571 raise;
572 end insert_cub_meas;
573
574 function insert_cub_dims(p_cube_id in number,
575 p_old_new_dimid in num_type,p_cube_def_agg in varchar2)
576 return num_type is
577 l_old_dim_id number;
578 t_old_new_cub_dim num_type;
579 t_new_cub_dims num_type;
580 begin
581 for i in r_cub_dims_val.CUBE_DIM_ID.first..r_cub_dims_val.CUBE_DIM_ID.last
582 loop
583 l_old_dim_id := r_cub_dims_val.PRICE_PLAN_DIM_ID(i);
584 r_cub_dims_val.PRICE_PLAN_DIM_ID(i) := p_old_new_dimid(l_old_dim_id);
585 end loop;
586
587 forall i in r_cub_dims_val.CUBE_DIM_ID.first..r_cub_dims_val.CUBE_DIM_ID.last
588 insert into qpr_cube_dims(CUBE_DIM_ID, CUBE_ID,
589 PRICE_PLAN_ID, PRICE_PLAN_DIM_ID,
590 AGGMAP_NAME, DIM_OPCODE,
591 DIM_SEQ_NUM,
592 MAPPING_VIEW_NAME, MAP_COLUMN,
593 USER_MAPPING_VIEW_NAME, USER_MAP_COLUMN,
594 SET_LEVEL_FLAG, DIM_EXPRESSION,
595 DIM_EXPRESSION_TYPE, WEIGHTED_MEASURE_FLAG,
596 WEIGHT_MEASURE_NAME, WNAFILL,
597 DIVIDE_BY_ZERO_FLAG, DECIMAL_OVERFLOW_FLAG,
598 NASKIP_FLAG,TEMPLATE_FLAG,CREATION_DATE,
599 CREATED_BY, LAST_UPDATE_DATE,
600 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
601 PROGRAM_APPLICATION_ID, PROGRAM_ID,
602 REQUEST_ID)
603 values(qpr_cube_dims_s.nextval, p_cube_id, g_new_pp_id,
604 r_cub_dims_val.PRICE_PLAN_DIM_ID(i),
605 nvl(r_cub_dims_val.AGGMAP_NAME(i), p_cube_def_agg),
606 r_cub_dims_val.DIM_OPCODE(i),
607 r_cub_dims_val.DIM_SEQ_NUM(i),
608 r_cub_dims_val.MAPPING_VIEW_NAME(i),
609 r_cub_dims_val.MAP_COLUMN(i),
610 r_cub_dims_val.USER_MAPPING_VIEW_NAME(i),
611 r_cub_dims_val.USER_MAP_COLUMN(i),
612 r_cub_dims_val.SET_LEVEL_FLAG(i),
613 r_cub_dims_val.DIM_EXPRESSION(i),
614 r_cub_dims_val.DIM_EXPRESSION_TYPE(i),
615 r_cub_dims_val.WEIGHTED_MEASURE_FLAG(i),
616 r_cub_dims_val.WEIGHT_MEASURE_NAME(i),
617 r_cub_dims_val.WNAFILL(i),
618 r_cub_dims_val.DIVIDE_BY_ZERO_FLAG(i),
619 r_cub_dims_val.DECIMAL_OVERFLOW_FLAG(i),
620 r_cub_dims_val.NASKIP_FLAG(i),'N',
621 g_sys_date, g_user_id, g_sys_date, g_user_id,
622 g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
623 returning CUBE_DIM_ID bulk collect into t_new_cub_dims;
624
625 for i in r_cub_dims_val.CUBE_DIM_ID.first..
626 r_cub_dims_val.CUBE_DIM_ID.last loop
627 t_old_new_cub_dim(r_cub_dims_val.CUBE_DIM_ID(i)) := t_new_cub_dims(i);
628 end loop;
629 return(t_old_new_cub_dim);
630 exception
631 when OTHERS then
632 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE DIMENSIONS...');
633 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
634 raise;
635 end insert_cub_dims;
636
637 function insert_meas_aggr(p_old_new_cub_dim in num_type,
638 p_old_new_meas in num_type ) return val_out_type is
639 l_old_meas_id number;
640 l_old_dim_id number;
641 l_ctr number := 0;
642 l_rec_ctr number := 0;
643 j pls_integer;
644 r_meas_set_lvl val_out_type;
645 begin
646 if g_copy_det_frm_tmpl = 'N' then
647 for i in r_cub_meas_aggr.MEASURE_ID.first..r_cub_meas_aggr.MEASURE_ID.last
648 loop
649 l_old_meas_id := r_cub_meas_aggr.MEASURE_ID(i);
650 r_cub_meas_aggr.MEASURE_ID(i):= p_old_new_meas(l_old_meas_id);
651 l_old_dim_id := r_cub_meas_aggr.CUBE_DIM_ID(i);
652 r_cub_meas_aggr.CUBE_DIM_ID(i) := p_old_new_cub_dim(l_old_dim_id);
653 -- this array is needed to insert values in set_levels
654 if r_cub_meas_aggr.SET_LEVEL_FLAG(i) = 'Y' then
655 l_ctr := l_ctr + 1;
656 -- t_meas_set_lvl(l_ctr) := r_cub_meas_aggr.MEASURE_ID(i);
657 r_meas_set_lvl.ID(l_ctr) := r_cub_meas_aggr.MEASURE_ID(i);
658 r_meas_set_lvl.PPA_CODE(l_ctr) := r_cub_meas_aggr.AGGMAP_NAME(i);
659 end if;
660 end loop;
661 else
662 -- loop thro all dimensions of the cube
663 -- and insert the no. of recs from measure agg for cube for all cube dims;
664 for i in r_cub_int_maggr.MEASURE_ID.first..r_cub_int_maggr.MEASURE_ID.last
665 loop
666 l_old_meas_id := r_cub_int_maggr.MEASURE_ID(i);
667 if r_cub_int_maggr.SET_LEVEL_FLAG(i) = 'Y' then
668 l_ctr := l_ctr + 1;
669 --t_meas_set_lvl(l_ctr) := p_old_new_meas(l_old_meas_id);
670 r_meas_set_lvl.ID(l_ctr) := p_old_new_meas(l_old_meas_id);
671 r_meas_set_lvl.PPA_CODE(l_ctr) := r_cub_int_maggr.AGGMAP_NAME(i);
672 end if;
673 j := p_old_new_cub_dim.first;
674 loop
675 exit when j is null;
676 l_rec_ctr := l_rec_ctr + 1;
677 r_cub_meas_aggr.MEASURE_ID(l_rec_ctr) := p_old_new_meas(l_old_meas_id);
678 r_cub_meas_aggr.CUBE_DIM_ID(l_rec_ctr) := p_old_new_cub_dim(j);
679 r_cub_meas_aggr.AGGMAP_NAME(l_rec_ctr) := r_cub_int_maggr.AGGMAP_NAME(i);
680 r_cub_meas_aggr.AGGMAP_CACHE_STORE(l_rec_ctr) :=
681 r_cub_int_maggr.AGGMAP_CACHE_STORE(i);
682 r_cub_meas_aggr.AGGMAP_CACHE_NA(l_rec_ctr) :=
683 r_cub_int_maggr.AGGMAP_CACHE_NA(i);
684 r_cub_meas_aggr.DIM_OPCODE(l_rec_ctr) := r_cub_int_maggr.DIM_OPCODE(i);
685 r_cub_meas_aggr.SET_LEVEL_FLAG(l_rec_ctr) :=
686 r_cub_int_maggr.SET_LEVEL_FLAG(i);
687 r_cub_meas_aggr.OVERRIDE_FLAG(l_rec_ctr) :=
688 r_cub_int_maggr.OVERRIDE_FLAG(i);
689 r_cub_meas_aggr.DIM_EXPRESSION(l_rec_ctr) :=
690 r_cub_int_maggr.DIM_EXPRESSION(i);
691 r_cub_meas_aggr.DIM_EXPRESSION_TYPE(l_rec_ctr):=
692 r_cub_int_maggr.DIM_EXPRESSION_TYPE(i);
693 r_cub_meas_aggr.WEIGHTED_MEASURE_FLAG(l_rec_ctr):=
694 r_cub_int_maggr.WEIGHTED_MEASURE_FLAG(i);
695 r_cub_meas_aggr.WEIGHT_MEASURE_NAME(l_rec_ctr):=
696 r_cub_int_maggr.WEIGHT_MEASURE_NAME(i);
697 r_cub_meas_aggr.WNAFILL(l_rec_ctr) :=
698 r_cub_int_maggr.WNAFILL(i);
699 r_cub_meas_aggr.DIVIDE_BY_ZERO_FLAG(l_rec_ctr) :=
700 r_cub_int_maggr.DIVIDE_BY_ZERO_FLAG(i);
701 r_cub_meas_aggr.DECIMAL_OVERFLOW_FLAG(l_rec_ctr) :=
702 r_cub_int_maggr.DECIMAL_OVERFLOW_FLAG(i);
703 r_cub_meas_aggr.NASKIP_FLAG(l_rec_ctr) :=
704 r_cub_int_maggr.NASKIP_FLAG(i);
705 j := p_old_new_cub_dim.next(j);
706 end loop;
707 end loop;
708 end if;
709
710 forall i in r_cub_meas_aggr.MEASURE_ID.first..r_cub_meas_aggr.MEASURE_ID.last
711 insert into qpr_meas_aggrs(MEAS_AGG_ID, MEASURE_ID, PRICE_PLAN_ID,
712 CUBE_DIM_ID, AGGMAP_NAME, AGGMAP_CACHE_STORE,
713 AGGMAP_CACHE_NA, DIM_OPCODE,
714 SET_LEVEL_FLAG, OVERRIDE_FLAG,
715 DIM_EXPRESSION, DIM_EXPRESSION_TYPE,
716 WEIGHTED_MEASURE_FLAG, WEIGHT_MEASURE_NAME,
717 WNAFILL, DIVIDE_BY_ZERO_FLAG,
718 DECIMAL_OVERFLOW_FLAG, NASKIP_FLAG,
719 TEMPLATE_FLAG,CREATION_DATE,CREATED_BY,
720 LAST_UPDATE_DATE,LAST_UPDATED_BY,
721 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
722 PROGRAM_ID, REQUEST_ID)
723 values(qpr_meas_aggrs_s.nextval,
724 r_cub_meas_aggr.MEASURE_ID(i), g_new_pp_id,
725 r_cub_meas_aggr.CUBE_DIM_ID(i),
726 r_cub_meas_aggr.AGGMAP_NAME(i),
727 r_cub_meas_aggr.AGGMAP_CACHE_STORE(i),
728 r_cub_meas_aggr.AGGMAP_CACHE_NA(i),
729 r_cub_meas_aggr.DIM_OPCODE(i),
730 r_cub_meas_aggr.SET_LEVEL_FLAG(i),
731 r_cub_meas_aggr.OVERRIDE_FLAG(i),
732 r_cub_meas_aggr.DIM_EXPRESSION(i),
733 r_cub_meas_aggr.DIM_EXPRESSION_TYPE(i),
734 r_cub_meas_aggr.WEIGHTED_MEASURE_FLAG(i),
735 r_cub_meas_aggr.WEIGHT_MEASURE_NAME(i),
736 r_cub_meas_aggr.WNAFILL(i),
737 r_cub_meas_aggr.DIVIDE_BY_ZERO_FLAG(i),
738 r_cub_meas_aggr.DECIMAL_OVERFLOW_FLAG(i),
739 r_cub_meas_aggr.NASKIP_FLAG(i), 'N',
740 g_sys_date, g_user_id, g_sys_date, g_user_id,
741 g_login_id, g_prg_appl_id, g_prg_id, g_request_id);
742
743 return(r_meas_set_lvl);
744 exception
745 when OTHERS then
746 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING MEASURE AGGREGATION...');
747 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
748 raise;
749 end insert_meas_aggr;
750
751 procedure insert_set_level(p_old_new_cub_dim in num_type,
752 p_old_new_meas in num_type,
753 p_r_meas_set_lvl in val_out_type) is
754 l_old_dim_id number;
755 l_old_meas_id number;
756 l_rec_ctr number := 0;
757 begin
758 if g_copy_det_frm_tmpl = 'N' then
759 for i in r_cub_set_lvl.CUBE_DIM_ID.first..r_cub_set_lvl.CUBE_DIM_ID.last
760 loop
761 l_old_dim_id := r_cub_set_lvl.CUBE_DIM_ID(i);
762 r_cub_set_lvl.CUBE_DIM_ID(i) := p_old_new_cub_dim(l_old_dim_id);
763 l_old_meas_id := r_cub_set_lvl.MEASURE_ID(i);
764 if l_old_meas_id is not null then
765 r_cub_set_lvl.MEASURE_ID(i) := p_old_new_meas(l_old_meas_id);
766 else
767 r_cub_set_lvl.MEASURE_ID(i) := null;
768 end if;
769 end loop;
770 else
771 for i in r_int_set_lvl.CUBE_DIM_ID.first..r_int_set_lvl.CUBE_DIM_ID.last
772 loop
773 if r_int_set_lvl.SET_LEVEL_FLAG(i) = 'Y' then
774 l_rec_ctr := l_rec_ctr + 1;
775 l_old_dim_id := r_int_set_lvl.CUBE_DIM_ID(i);
776 r_cub_set_lvl.CUBE_DIM_ID(l_rec_ctr) := p_old_new_cub_dim(l_old_dim_id);
777 r_cub_set_lvl.MEASURE_ID(l_rec_ctr) := null;
778 r_cub_set_lvl.LEVEL_SHORT_NAME(l_rec_ctr) :=
779 r_int_set_lvl.LEVEL_SHORT_NAME(i);
780 r_cub_set_lvl.DIM_EXPRESSION_TYPE(l_rec_ctr) :=
781 r_int_set_lvl.DIM_EXPRESSION_TYPE(i);
782 r_cub_set_lvl.AGGMAP_NAME(l_rec_ctr) :=
783 r_int_set_lvl.AGGMAP_NAME(i);
784 end if;
785 end loop;
786 if p_r_meas_set_lvl.ID.count > 0 then
787 for i in p_r_meas_set_lvl.ID.first..p_r_meas_set_lvl.ID.last loop
788 for j in r_int_set_lvl.CUBE_DIM_ID.first..r_int_set_lvl.CUBE_DIM_ID.last
789 loop
790 l_rec_ctr := l_rec_ctr + 1;
791 l_old_dim_id := r_int_set_lvl.CUBE_DIM_ID(j);
792 r_cub_set_lvl.CUBE_DIM_ID(l_rec_ctr) := p_old_new_cub_dim(l_old_dim_id);
793 r_cub_set_lvl.MEASURE_ID(l_rec_ctr) := p_r_meas_set_lvl.ID(i);
794 r_cub_set_lvl.LEVEL_SHORT_NAME(l_rec_ctr) :=
795 r_int_set_lvl.LEVEL_SHORT_NAME(j);
796 r_cub_set_lvl.DIM_EXPRESSION_TYPE(l_rec_ctr) :=
797 r_int_set_lvl.DIM_EXPRESSION_TYPE(j);
798 r_cub_set_lvl.AGGMAP_NAME(l_rec_ctr) :=
799 p_r_meas_set_lvl.PPA_CODE(i);
800 end loop;
801 end loop;
802 end if;
803 end if;
804 forall i in r_cub_set_lvl.MEASURE_ID.first..r_cub_set_lvl.MEASURE_ID.last
805 insert into qpr_set_levels(SET_DIM_LEVEL_ID, CUBE_DIM_ID, MEASURE_ID,
806 PRICE_PLAN_ID,
807 LEVEL_SHORT_NAME, DIM_EXPRESSION_TYPE,
808 AGGMAP_NAME,TEMPLATE_FLAG,CREATION_DATE,
809 CREATED_BY, LAST_UPDATE_DATE,
810 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
811 PROGRAM_APPLICATION_ID, PROGRAM_ID,
812 REQUEST_ID)
813 values(qpr_set_levels_s.nextval,
814 r_cub_set_lvl.CUBE_DIM_ID(i),
815 r_cub_set_lvl.MEASURE_ID(i), g_new_pp_id,
816 r_cub_set_lvl.LEVEL_SHORT_NAME(i),
817 r_cub_set_lvl.DIM_EXPRESSION_TYPE(i),
818 r_cub_set_lvl.AGGMAP_NAME(i), 'N',
819 g_sys_date, g_user_id, g_sys_date, g_user_id,
820 g_login_id, g_prg_appl_id, g_prg_id, g_request_id);
821 exception
822 when OTHERS then
823 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE SET LEVEL... ');
824 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
825 raise;
826 end insert_set_level;
827
828 procedure clean_dimattrval is
829 begin
830 r_dim_attr_val.DIM_ATTR_ID.delete;
831 r_dim_attr_val.ATTR_PPA_CODE.delete;
832 r_dim_attr_val.ATTR_SHORT_NAME.delete;
833 r_dim_attr_val.ATTR_LONG_NAME.delete;
834 r_dim_attr_val.ATTR_PLURAL_NAME.delete;
835 r_dim_attr_val.ATTR_CLASSIFICATION.delete;
836 r_dim_attr_val.ATTR_DATA_TYPE.delete;
837 r_dim_attr_val.DEFAULT_ORDER_FLAG.delete;
838 end clean_dimattrval;
839
840 procedure clean_hierlvlval is
841 begin
842 r_hier_lvl_val.HIERARCHY_LEVEL_ID.delete;
843 r_hier_lvl_val.LEVEL_PPA_CODE.delete;
844 r_hier_lvl_val.LEVEL_SEQ_NUM.delete;
845 r_hier_lvl_val.LVL_SHORT_NAME.delete;
846 r_hier_lvl_val.LVL_LONG_NAME.delete;
847 r_hier_lvl_val.LVL_PLURAL_NAME.delete;
848 r_hier_lvl_val.MAPPING_VIEW_NAME.delete;
849 r_hier_lvl_val.MAP_COLUMN.delete;
850 r_hier_lvl_val.USER_MAPPING_VIEW_NAME.delete;
851 r_hier_lvl_val.USER_MAP_COLUMN.delete;
852 end clean_hierlvlval;
853
854 procedure clean_lvlattrval is
855 begin
856 r_lvl_attr_val.LEVEL_ATTR_ID.delete;
857 r_lvl_attr_val.HIERARCHY_LEVEL_ID.delete;
858 r_lvl_attr_val.DIM_ATTR_ID.delete;
859 r_lvl_attr_val.MAPPING_VIEW_NAME.delete;
860 r_lvl_attr_val.MAP_COLUMN.delete;
861 r_lvl_attr_val.USER_MAPPING_VIEW_NAME.delete;
862 r_lvl_attr_val.USER_MAP_COLUMN.delete;
863 end clean_lvlattrval;
864
865 procedure clean_hierval is
866 begin
867 r_dim_hier_val.HIERARCHY_ID.delete;
868 r_dim_hier_val.HIERARCHY_PPA_CODE.delete;
869 r_dim_hier_val.HIER_SHORT_NAME.delete;
870 r_dim_hier_val.HIER_LONG_NAME.delete;
871 r_dim_hier_val.HIER_PLURAL_NAME.delete;
872 r_dim_hier_val.HIER_TYPE_CODE.delete;
873 r_dim_hier_val.HIER_DEFAULT_ORDER.delete;
874 r_dim_hier_val.DEFAULT_FLAG.delete;
875 r_dim_hier_val.CALENDAR_CODE.delete;
876 end clean_hierval;
877
878 procedure clean_dimval is
879 begin
880 r_dim_val.SRC_PRICE_PLAN_DIM_ID.delete;
881 r_dim_val.TMPL_PRICE_PLAN_DIM_ID.delete;
882 r_dim_val.DIM_CODE.delete;
883 r_dim_val.DIM_PPA_CODE.delete;
884 r_dim_val.DIM_SEQ_NUM.delete;
885 r_dim_val.DIM_SHORT_NAME.delete;
886 r_dim_val.DIM_LONG_NAME.delete;
887 r_dim_val.DIM_PLURAL_NAME.delete;
888 r_dim_val.TIME_DIM_FLAG.delete;
889 r_dim_val.NATIVE_KEY_FLAG.delete;
890 r_dim_val.MEASURE_DIM_FLAG.delete;
891 r_dim_val.SPARSE_FLAG.delete;
892 r_dim_val.MAND_DIM_FLAG.delete;
893 r_dim_val.LOB_ENABLED_FLAG.delete;
894 r_dim_val.LOWEST_LVL.delete;
895 r_dim_val.LIST_PP_FLAG.delete;
896 end clean_dimval;
897
898 procedure clean_cubmeasval is
899 begin
900 r_cub_meas_val.MEASURE_ID.delete;
901 r_cub_meas_val.MEASURE_PPA_CODE.delete;
902 r_cub_meas_val.MEAS_CREATION_SEQ_NUM.delete;
903 r_cub_meas_val.MEAS_SHORT_NAME.delete;
904 r_cub_meas_val.MEAS_LONG_NAME.delete;
905 r_cub_meas_val.MEAS_PLURAL_NAME.delete;
906 r_cub_meas_val.MEAS_TYPE.delete;
907 r_cub_meas_val.MEAS_DATA_TYPE.delete;
908 r_cub_meas_val.MEAS_AUTO_SOLVE.delete;
909 r_cub_meas_val.CAL_MEAS_EXPRESSION_TEXT.delete;
910 r_cub_meas_val.MAPPING_VIEW_NAME.delete;
911 r_cub_meas_val.MAP_COLUMN.delete;
912 r_cub_meas_val.USER_MAPPING_VIEW_NAME.delete;
913 r_cub_meas_val.USER_MAP_COLUMN.delete;
914 r_cub_meas_val.AGGMAP_NAME.delete;
915 r_cub_meas_val.MEAS_FOLD_SHORT_NAME.delete;
916 r_cub_meas_val.MEAS_FOLD_LONG_NAME.delete;
917 r_cub_meas_val.MEAS_FOLD_PLURAL_NAME.delete;
918 end clean_cubmeasval;
919
920
921 procedure clean_cubdimsval is
922 begin
923 r_cub_dims_val.CUBE_DIM_ID.delete;
924 r_cub_dims_val.PRICE_PLAN_DIM_ID.delete;
925 r_cub_dims_val.AGGMAP_NAME.delete;
926 r_cub_dims_val.DIM_OPCODE.delete;
927 r_cub_dims_val.DIM_SEQ_NUM.delete;
928 r_cub_dims_val.MAPPING_VIEW_NAME.delete;
929 r_cub_dims_val.MAP_COLUMN.delete;
930 r_cub_dims_val.USER_MAPPING_VIEW_NAME.delete;
931 r_cub_dims_val.USER_MAP_COLUMN.delete;
932 r_cub_dims_val.SET_LEVEL_FLAG.delete;
933 r_cub_dims_val.DIM_EXPRESSION.delete;
934 r_cub_dims_val.DIM_EXPRESSION_TYPE.delete;
935 r_cub_dims_val.WEIGHTED_MEASURE_FLAG.delete;
936 r_cub_dims_val.WEIGHT_MEASURE_NAME.delete;
937 r_cub_dims_val.WNAFILL.delete;
938 r_cub_dims_val.DIVIDE_BY_ZERO_FLAG.delete;
939 r_cub_dims_val.DECIMAL_OVERFLOW_FLAG.delete;
940 r_cub_dims_val.NASKIP_FLAG.delete;
941 end clean_cubdimsval;
942
943 procedure clean_measaggr is
944 begin
945 r_cub_meas_aggr.MEASURE_ID.delete;
946 r_cub_meas_aggr.CUBE_DIM_ID.delete;
947 r_cub_meas_aggr.AGGMAP_NAME.delete;
948 r_cub_meas_aggr.AGGMAP_CACHE_STORE.delete;
949 r_cub_meas_aggr.AGGMAP_CACHE_NA.delete;
950 r_cub_meas_aggr.DIM_OPCODE.delete;
951 r_cub_meas_aggr.SET_LEVEL_FLAG.delete;
952 r_cub_meas_aggr.OVERRIDE_FLAG.delete;
953 r_cub_meas_aggr.DIM_EXPRESSION.delete;
954 r_cub_meas_aggr.DIM_EXPRESSION_TYPE.delete;
955 r_cub_meas_aggr.WEIGHTED_MEASURE_FLAG.delete;
956 r_cub_meas_aggr.WEIGHT_MEASURE_NAME.delete;
957 r_cub_meas_aggr.WNAFILL.delete;
958 r_cub_meas_aggr.DIVIDE_BY_ZERO_FLAG.delete;
959 r_cub_meas_aggr.DECIMAL_OVERFLOW_FLAG.delete;
960 r_cub_meas_aggr.NASKIP_FLAG.delete;
961
962
963 r_cub_int_maggr.MEASURE_ID.delete;
964 r_cub_int_maggr.CUBE_DIM_ID.delete;
965 r_cub_int_maggr.AGGMAP_NAME.delete;
966 r_cub_int_maggr.AGGMAP_CACHE_STORE.delete;
967 r_cub_int_maggr.AGGMAP_CACHE_NA.delete;
968 r_cub_int_maggr.DIM_OPCODE.delete;
969 r_cub_int_maggr.SET_LEVEL_FLAG.delete;
970 r_cub_int_maggr.OVERRIDE_FLAG.delete;
971 r_cub_int_maggr.DIM_EXPRESSION.delete;
972 r_cub_int_maggr.DIM_EXPRESSION_TYPE.delete;
973 r_cub_int_maggr.WEIGHTED_MEASURE_FLAG.delete;
974 r_cub_int_maggr.WEIGHT_MEASURE_NAME.delete;
975 r_cub_int_maggr.WNAFILL.delete;
976 r_cub_int_maggr.DIVIDE_BY_ZERO_FLAG.delete;
977 r_cub_int_maggr.DECIMAL_OVERFLOW_FLAG.delete;
978 r_cub_int_maggr.NASKIP_FLAG.delete;
979 end clean_measaggr;
980
981 procedure clean_setlvl is
982 begin
983 r_cub_set_lvl.CUBE_DIM_ID.delete;
984 r_cub_set_lvl.MEASURE_ID.delete;
985 r_cub_set_lvl.LEVEL_SHORT_NAME.delete;
986 r_cub_set_lvl.DIM_EXPRESSION_TYPE.delete;
987 r_cub_set_lvl.AGGMAP_NAME.delete;
988 r_cub_set_lvl.SET_LEVEL_FLAG.delete;
989
990 r_int_set_lvl.CUBE_DIM_ID.delete;
991 r_int_set_lvl.MEASURE_ID.delete;
992 r_int_set_lvl.LEVEL_SHORT_NAME.delete;
993 r_int_set_lvl.DIM_EXPRESSION_TYPE.delete;
994 r_int_set_lvl.AGGMAP_NAME.delete;
995 r_int_set_lvl.SET_LEVEL_FLAG.delete;
996 end clean_setlvl;
997
998 procedure clean_cubval is
999 begin
1000 r_cub_val.SRC_CUBE_ID.delete;
1001 r_cub_val.TMPL_CUBE_ID.delete;
1002 r_cub_val.CUBE_PPA_CODE.delete;
1003 r_cub_val.CUBE_CODE.delete;
1004 r_cub_val.CUBE_SHORT_NAME.delete;
1005 r_cub_val.CUBE_LONG_NAME.delete;
1006 r_cub_val.CUBE_PLURAL_NAME.delete;
1007 r_cub_val.CUBE_AUTO_SOLVE_FLAG.delete;
1008 r_cub_val.DEFAULT_DATA_TYPE.delete;
1009 r_cub_val.PARTITION_HIER.delete;
1010 r_cub_val.PARTITION_LEVEL.delete;
1011 r_cub_val.SPARSE_TYPE_CODE.delete;
1012 r_cub_val.USE_GLOBAL_INDEX_FLAG.delete;
1013 r_cub_val.AGGMAP_NAME.delete;
1014 r_cub_val.AGGMAP_CACHE_STORE.delete;
1015 r_cub_val.AGGMAP_CACHE_NA.delete;
1016 end clean_cubval;
1017
1018 function copy_dim_data(p_from_pp_id in number,
1019 p_t_old_new_dim out nocopy num_type) return number is
1020 l_ctr PLS_INTEGER;
1021 l_old_lvlid number;
1022 l_old_dim_attr number;
1023 l_ret number := 0;
1024 l_pplan_dim_id number;
1025 s_lvl_sql varchar2(5000);
1026 s_temp_sql varchar2(10000);
1027 s_sql varchar2(30000);
1028
1029 c_get_lvl_attr SYS_REFCURSOR;
1030
1031 -- the following table types/records stores the newly inserted values into
1032 -- each of the qpr_ tables. They are 2 types: 1. Contains the unique id
1033 -- generated as key and PPA_code as the values.
1034 -- 2.Contains the old unique id and corresponding new id of the inserted recs
1035 rec_dim_ids val_out_type;
1036 rec_hier_ids val_out_type;
1037 t_old_new_dim_attr num_type;
1038 t_old_new_lvl num_type;
1039 begin
1040 fnd_file.put_line(fnd_file.log, 'Copying dimensions...');
1041
1042 if g_copy_det_frm_tmpl = 'Y' then
1043 select src.PRICE_PLAN_DIM_ID, tmpl.PRICE_PLAN_DIM_ID, tmpl.DIM_CODE,
1044 tmpl.DIM_PPA_CODE, tmpl.DIM_SEQ_NUM, tmpl.MAND_DIM_FLAG,
1045 tmpl.TIME_DIM_FLAG, tmpl.LOB_ENABLED_FLAG, tmpl.DIM_SHORT_NAME,
1046 tmpl.DIM_LONG_NAME, tmpl.DIM_PLURAL_NAME,
1047 tmpl.NATIVE_KEY_FLAG,
1048 tmpl.MEASURE_DIM_FLAG, tmpl.SPARSE_FLAG , tmpl.LOWEST_LVL,
1049 tmpl.LIST_PRICE_PLAN_FLAG
1050 bulk collect into r_dim_val
1051 from qpr_dimensions src, qpr_dimensions tmpl
1052 where src.PRICE_PLAN_ID = p_from_pp_id
1053 and tmpl.PRICE_PLAN_ID = g_src_pplan_id
1054 and src.DIM_PPA_CODE = tmpl.DIM_PPA_CODE
1055 and nvl(tmpl.INCLUDE_FLAG, 'Y') = 'Y';
1056 else
1057 select PRICE_PLAN_DIM_ID, null, DIM_CODE, DIM_PPA_CODE, DIM_SEQ_NUM,
1058 MAND_DIM_FLAG, TIME_DIM_FLAG, LOB_ENABLED_FLAG, DIM_SHORT_NAME,
1059 DIM_LONG_NAME, DIM_PLURAL_NAME, NATIVE_KEY_FLAG,
1060 MEASURE_DIM_FLAG, SPARSE_FLAG , LOWEST_LVL, LIST_PRICE_PLAN_FLAG
1061 bulk collect into r_dim_val
1062 from qpr_dimensions
1063 where PRICE_PLAN_ID = g_src_pplan_id
1064 and nvl(INCLUDE_FLAG, 'Y') = 'Y';
1065 end if;
1066
1067 s_sql := '';
1068
1069 if r_dim_val.DIM_CODE.count = 0 then
1070 fnd_file.put_line(fnd_file.log, 'No dimensions to copy..');
1071 return(-2);
1072 end if;
1073
1074 rec_dim_ids := insert_dim_values;
1075
1076 s_lvl_sql := 'select LEVEL_ATTR_ID, HIERARCHY_LEVEL_ID,DIM_ATTR_ID,';
1077 s_lvl_sql := s_lvl_sql || ' MAPPING_VIEW_NAME, MAP_COLUMN, ';
1078 s_lvl_sql := s_lvl_sql || ' USER_MAPPING_VIEW_NAME, USER_MAP_COLUMN ';
1079 s_lvl_sql := s_lvl_sql || 'from qpr_lvl_attributes where PRICE_PLAN_ID=:1';
1080 s_lvl_sql := s_lvl_sql || ' and nvl(INCLUDED_FLAG, ''Y'') = ''Y''' ;
1081 s_lvl_sql := s_lvl_sql || ' and HIERARCHY_LEVEL_ID IN (' ;
1082
1083 for i in rec_dim_ids.ID.first..rec_dim_ids.ID.last loop
1084 if g_copy_det_frm_tmpl = 'Y' then
1085 p_t_old_new_dim(r_dim_val.TMPL_PRICE_PLAN_DIM_ID(i)) := rec_dim_ids.ID(i);
1086 else
1087 p_t_old_new_dim(r_dim_val.SRC_PRICE_PLAN_DIM_ID(i)) := rec_dim_ids.ID(i);
1088 end if;
1089
1090 fnd_file.put_line(fnd_file.log,
1091 'Copying dimension attributes for ' || rec_dim_ids.PPA_CODE(i));
1092 -- Note: when details are not copied from template then tmpl_price_plan_dim_id
1093 -- will be null.
1094 l_pplan_dim_id := nvl(r_dim_val.TMPL_PRICE_PLAN_DIM_ID(i) ,
1095 r_dim_val.SRC_PRICE_PLAN_DIM_ID(i)) ;
1096 select DIM_ATTR_ID, ATTR_PPA_CODE, ATTR_SHORT_NAME, ATTR_LONG_NAME,
1097 ATTR_PLURAL_NAME, ATTR_CLASSIFICATION, ATTR_DATA_TYPE,
1098 DEFAULT_ORDER_FLAG
1099 bulk collect into r_dim_attr_val
1100 from qpr_dim_attributes
1101 where PRICE_PLAN_ID = g_src_pplan_id
1102 and PRICE_PLAN_DIM_ID = l_pplan_dim_id;
1103
1104 if r_dim_attr_val.DIM_ATTR_ID.count > 0 then
1105 t_old_new_dim_attr := insert_dim_attr_values(rec_dim_ids.ID(i));
1106 else
1107 fnd_file.put_line(fnd_file.log, 'No dimension attributes to copy..');
1108 end if;
1109
1110 clean_dimattrval;
1111
1112 fnd_file.put_line(fnd_file.log,
1113 'Copying hierarchies for ' || rec_dim_ids.PPA_CODE(i));
1114
1115 select HIERARCHY_ID,HIERARCHY_PPA_CODE,HIER_SHORT_NAME, HIER_LONG_NAME,
1116 HIER_PLURAL_NAME, HIER_TYPE_CODE, HIER_DEFAULT_ORDER,DEFAULT_FLAG,
1117 CALENDAR_CODE
1118 bulk collect into r_dim_hier_val
1119 from qpr_hierarchies
1120 where PRICE_PLAN_ID = g_src_pplan_id
1121 and PRICE_PLAN_DIM_ID = l_pplan_dim_id;
1122
1123 if r_dim_hier_val.HIERARCHY_ID.count > 0 then
1124 rec_hier_ids := insert_hier_values(rec_dim_ids.ID(i));
1125 for j in rec_hier_ids.ID.first..rec_hier_ids.ID.last loop
1126 fnd_file.put_line(fnd_file.log,
1127 'Copying levels for hierarchy ' || rec_hier_ids.PPA_CODE(j));
1128
1129 select HIERARCHY_LEVEL_ID, LEVEL_PPA_CODE, LEVEL_SEQ_NUM,
1130 LVL_SHORT_NAME, LVL_LONG_NAME, LVL_PLURAL_NAME,
1131 MAPPING_VIEW_NAME, MAP_COLUMN , USER_MAPPING_VIEW_NAME,
1132 USER_MAP_COLUMN
1133 bulk collect into r_hier_lvl_val
1134 from qpr_hier_levels
1135 where PRICE_PLAN_ID = g_src_pplan_id
1136 and HIERARCHY_ID = r_dim_hier_val.HIERARCHY_ID(j)
1137 and nvl(INCLUDE_FLAG, 'Y') = 'Y';
1138
1139 if r_hier_lvl_val.HIERARCHY_LEVEL_ID.count = 0 then
1140 fnd_file.put_line(fnd_file.log,
1141 'No level to copy for hierarchy...' );
1142 l_ret := -1;
1143 else
1144 t_old_new_lvl := insert_hier_lvl_val(rec_hier_ids.ID(j));
1145 end if;
1146
1147 clean_hierlvlval;
1148
1149 if t_old_new_lvl.count > 0 then
1150 -- NOTE: here we can't use the for loop since the index is a number and indicates
1151 -- level id that might not be consecutive. So when for loop is used then all ids
1152 -- within the given range is taken and hence we will have more ids than intended.
1153 l_ctr := t_old_new_lvl.first;
1154 s_temp_sql := '';
1155 loop
1156 exit when l_ctr is null;
1157 s_temp_sql := s_temp_sql || l_ctr ;
1158 if l_ctr <> t_old_new_lvl.last then
1159 s_temp_sql := s_temp_sql || ',';
1160 end if;
1161 l_ctr := t_old_new_lvl.next(l_ctr);
1162 end loop;
1163
1164 s_sql := s_lvl_sql || s_temp_sql || ')' ;
1165
1166 fnd_file.put_line(fnd_file.log, 'Copying attributes for all levels...');
1167
1168 open c_get_lvl_attr for s_sql using g_src_pplan_id;
1169 fetch c_get_lvl_attr bulk collect into r_lvl_attr_val;
1170 close c_get_lvl_attr;
1171
1172 if r_lvl_attr_val.LEVEL_ATTR_ID.count > 0 then
1173 for k in r_lvl_attr_val.LEVEL_ATTR_ID.first..
1174 r_lvl_attr_val.LEVEL_ATTR_ID.last loop
1175 l_old_lvlid := r_lvl_attr_val.HIERARCHY_LEVEL_ID(k);
1176 l_old_dim_attr := r_lvl_attr_val.DIM_ATTR_ID(k);
1177 r_lvl_attr_val.HIERARCHY_LEVEL_ID(k) := t_old_new_lvl(l_old_lvlid);
1178 r_lvl_attr_val.DIM_ATTR_ID(k) := t_old_new_dim_attr(l_old_dim_attr);
1179 end loop;
1180 insert_lvl_attributes;
1181 else
1182 fnd_file.put_line(fnd_file.log, 'No level attributes to copy..');
1183 end if;
1184
1185 clean_lvlattrval;
1186 end if;
1187 t_old_new_lvl.delete;
1188 end loop;
1189 t_old_new_dim_attr.delete;
1190 else
1191 fnd_file.put_line(fnd_file.log, 'No Hierarchies to copy...');
1192 l_ret := -1;
1193 end if;
1194 clean_hierval;
1195 rec_hier_ids.ID.delete;
1196 rec_hier_ids.PPA_CODE.delete;
1197 end loop;
1198 clean_dimval;
1199 rec_dim_ids.id.delete;
1200 rec_dim_ids.PPA_CODE.delete;
1201 return(l_ret);
1202 exception
1203 when OTHERS then
1204 fnd_file.put_line(fnd_file.log,'ERROR COPYING DIMENSION RELATED DATA...');
1205 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1206 raise;
1207 return(-1);
1208 end copy_dim_data;
1209
1210 function get_cube_meas_aggrs_sql return varchar2 is
1211 s_sql varchar2(5000);
1212 begin
1213 s_sql := 'select MEASURE_ID, CUBE_DIM_ID,AGGMAP_NAME, AGGMAP_CACHE_STORE,';
1214 s_sql := s_sql || ' AGGMAP_CACHE_NA, DIM_OPCODE,';
1215 s_sql := s_sql || ' SET_LEVEL_FLAG, OVERRIDE_FLAG, DIM_EXPRESSION, ';
1216 s_sql := s_sql || 'DIM_EXPRESSION_TYPE,WEIGHTED_MEASURE_FLAG, ';
1217 s_sql := s_sql || 'WEIGHT_MEASURE_NAME, WNAFILL,DIVIDE_BY_ZERO_FLAG, ';
1218 s_sql := s_sql || ' DECIMAL_OVERFLOW_FLAG, NASKIP_FLAG ';
1219 s_sql := s_sql || ' from qpr_meas_aggrs where PRICE_PLAN_ID = :1 ';
1220
1221 return(s_sql);
1222 end get_cube_meas_aggrs_sql;
1223
1224 function get_cube_set_lvl_sql return varchar2 is
1225 s_sql varchar2(5000);
1226 begin
1227 if g_copy_det_frm_tmpl = 'N' then
1228 s_sql := ' select a.CUBE_DIM_ID,a.MEASURE_ID, a.LEVEL_SHORT_NAME ,' ;
1229 s_sql := s_sql || ' a.DIM_EXPRESSION_TYPE,a.AGGMAP_NAME, null';
1230 s_sql := s_sql || ' from qpr_set_levels a';
1231 s_sql := s_sql || ' where a.PRICE_PLAN_ID = :1 ';
1232 else
1233 s_sql := ' select b.CUBE_DIM_ID, a.MEASURE_ID, ' ;
1234 s_sql := s_sql || 'a.LEVEL_SHORT_NAME,a.DIM_EXPRESSION_TYPE,a.AGGMAP_NAME,';
1235 s_sql := s_sql || 'b.SET_LEVEL_FLAG from qpr_set_levels a, ' ;
1236 s_sql := s_sql || ' qpr_cube_dims b where a.PRICE_PLAN_ID = :1 ';
1237 s_sql := s_sql || ' and b.PRICE_PLAN_ID = a.PRICE_PLAN_ID ' ;
1238 s_sql := s_sql || ' and b.CUBE_DIM_ID = a.CUBE_DIM_ID ';
1239 end if;
1240
1241 return(s_sql);
1242 end get_cube_set_lvl_sql;
1243
1244 function copy_cube_data(p_from_pp_id in number,
1245 p_t_old_new_dim in num_type) return number is
1246 l_ctr pls_integer;
1247 l_ret number := 0;
1248 l_ref_cub_id number;
1249 s_sql varchar2(30000);
1250 s_meas_sql varchar2(5000);
1251 s_cub_dims_sql varchar2(5000);
1252 s_get_cmagg_sql varchar2(5000);
1253 s_get_set_lvl_sql varchar2(5000);
1254
1255 rec_cub_ids val_out_type;
1256 t_old_new_meas num_type;
1257 t_old_new_cub_dim num_type;
1258 ---t_meas_set_lvl num_type;
1259 r_meas_set_lvl val_out_type;
1260
1261 c_get_cub_meas_agg SYS_REFCURSOR;
1262 c_get_set_level SYS_REFCURSOR;
1263 begin
1264 fnd_file.put_line(fnd_file.log, 'Copying cube data...');
1265
1266 if g_copy_det_frm_tmpl = 'Y' then
1267 select src.CUBE_ID, tmpl.CUBE_ID , src.CUBE_PPA_CODE,
1268 tmpl.CUBE_CODE || g_new_pp_id || '_C' ,
1269 tmpl.CUBE_SHORT_NAME, tmpl.CUBE_LONG_NAME, tmpl.CUBE_PLURAL_NAME,
1270 tmpl.CUBE_AUTO_SOLVE_FLAG, tmpl.DEFAULT_DATA_TYPE, tmpl.PARTITION_HIER,
1271 tmpl.PARTITION_LEVEL,tmpl.SPARSE_TYPE_CODE, tmpl.USE_GLOBAL_INDEX_FLAG,
1272 tmpl.AGGMAP_NAME, tmpl.AGGMAP_CACHE_STORE, tmpl.AGGMAP_CACHE_NA
1273 bulk collect into r_cub_val
1274 from qpr_cubes src, qpr_cubes tmpl
1275 where src.PRICE_PLAN_ID = p_from_pp_id
1276 and tmpl.PRICE_PLAN_ID = g_src_pplan_id and
1277 src.CUBE_PPA_CODE = tmpl.CUBE_PPA_CODE
1278 order by tmpl.cube_id;
1279 else
1280 select CUBE_ID, null , CUBE_PPA_CODE,
1281 replace(CUBE_CODE, p_from_pp_id, g_new_pp_id), CUBE_SHORT_NAME,
1282 CUBE_LONG_NAME, CUBE_PLURAL_NAME, CUBE_AUTO_SOLVE_FLAG,
1283 DEFAULT_DATA_TYPE, PARTITION_HIER,PARTITION_LEVEL,SPARSE_TYPE_CODE,
1284 USE_GLOBAL_INDEX_FLAG,AGGMAP_NAME,AGGMAP_CACHE_STORE,AGGMAP_CACHE_NA
1285 bulk collect into r_cub_val
1286 from qpr_cubes where PRICE_PLAN_ID = p_from_pp_id
1287 order by cube_id;
1288 end if;
1289 if r_cub_val.src_cube_id.count = 0 then
1290 fnd_file.put_line(fnd_file.log, 'No cube to copy ...');
1291 return(-1);
1292 else
1293 rec_cub_ids := insert_cube_data;
1294 end if;
1295
1296 s_get_cmagg_sql := get_cube_meas_aggrs_sql;
1297 s_get_set_lvl_sql := get_cube_set_lvl_sql;
1298
1299 for i in rec_cub_ids.ID.first..rec_cub_ids.ID.last loop
1300 fnd_file.put_line(fnd_file.log,
1301 'Copying data for cube ' || rec_cub_ids.PPA_CODE(i));
1302 if g_copy_det_frm_tmpl = 'Y' then
1303 l_ref_cub_id := r_cub_val.TMPL_CUBE_ID(i);
1304 else
1305 l_ref_cub_id := r_cub_val.SRC_CUBE_ID(i);
1306 end if;
1307 -- ****** COPYING CUBE MEASURES ***********
1308 fnd_file.put_line(fnd_file.log, 'Copying cube measures... ' );
1309
1310 select MEASURE_ID, MEASURE_PPA_CODE, MEAS_CREATION_SEQ_NUM,
1311 MEAS_SHORT_NAME, MEAS_LONG_NAME, MEAS_PLURAL_NAME, MEAS_TYPE,
1312 MEAS_DATA_TYPE, MEAS_AUTO_SOLVE,
1313 CAL_MEAS_EXPRESSION_TEXT,
1314 MAPPING_VIEW_NAME, MAP_COLUMN, USER_MAPPING_VIEW_NAME,
1315 USER_MAP_COLUMN, AGGMAP_NAME,MEAS_FOLD_SHORT_NAME,
1316 MEAS_FOLD_LONG_NAME, MEAS_FOLD_PLURAL_NAME
1317 bulk collect into r_cub_meas_val
1318 from qpr_measures
1319 where PRICE_PLAN_ID = g_src_pplan_id
1320 and CUBE_ID = l_ref_cub_id
1321 and nvl(INCLUDE_FLAG, 'Y') = 'Y';
1322
1323 if r_cub_meas_val.MEASURE_ID.count = 0 then
1324 fnd_file.put_line(fnd_file.log, 'No Measures to copy');
1325 l_ret := -1;
1326 else
1327 t_old_new_meas := insert_cub_meas( rec_cub_ids.ID(i),p_from_pp_id,
1328 rec_cub_ids.PPA_CODE(i));
1329 end if;
1330 clean_cubmeasval;
1331
1332 -- The cube measure ids are concatenated to be used in querying the measure
1333 -- aggregation values for this cube.
1334 s_meas_sql := '';
1335 l_ctr := t_old_new_meas.first;
1336 loop
1337 exit when l_ctr is null;
1338 s_meas_sql := s_meas_sql || l_ctr ;
1339 if l_ctr <> t_old_new_meas.last then
1340 s_meas_sql := s_meas_sql || ',' ;
1341 end if;
1342 l_ctr := t_old_new_meas.next(l_ctr);
1343 end loop;
1344
1345 -- ********* COPYING CUBE DIMENSIONS *********
1346 fnd_file.put_line(fnd_file.log, 'Copying cube dimensions...');
1347
1348 select CUBE_DIM_ID, PRICE_PLAN_DIM_ID, AGGMAP_NAME,
1349 DIM_OPCODE, DIM_SEQ_NUM,MAPPING_VIEW_NAME,MAP_COLUMN,
1350 USER_MAPPING_VIEW_NAME,USER_MAP_COLUMN,SET_LEVEL_FLAG,
1351 DIM_EXPRESSION,DIM_EXPRESSION_TYPE,WEIGHTED_MEASURE_FLAG,
1352 WEIGHT_MEASURE_NAME, WNAFILL,DIVIDE_BY_ZERO_FLAG,
1353 DECIMAL_OVERFLOW_FLAG,NASKIP_FLAG
1354 bulk collect into r_cub_dims_val
1355 from qpr_cube_dims
1356 where PRICE_PLAN_ID = g_src_pplan_id
1357 and CUBE_ID = l_ref_cub_id;
1358
1359 if r_cub_dims_val.CUBE_DIM_ID.count = 0 then
1360 fnd_file.put_line(fnd_file.log, 'No Cube dimensions to copy...');
1361 l_ret := 0;
1362 else
1363 t_old_new_cub_dim := insert_cub_dims(rec_cub_ids.ID(i),
1364 p_t_old_new_dim,
1365 r_cub_val.AGGMAP_NAME(i));
1366 end if;
1367
1368 clean_cubdimsval;
1369 -- the cube dims value are concatenated for querying
1370 -- values in set_levels
1371 s_cub_dims_sql := '';
1372 l_ctr := t_old_new_cub_dim.first;
1373 loop
1374 exit when l_ctr is null;
1375 s_cub_dims_sql := s_cub_dims_sql || l_ctr ;
1376 if l_ctr <> t_old_new_cub_dim.last then
1377 s_cub_dims_sql := s_cub_dims_sql || ',' ;
1378 end if;
1379 l_ctr := t_old_new_cub_dim.next(l_ctr);
1380 end loop;
1381
1382 if t_old_new_meas.count > 0 and t_old_new_cub_dim.count > 0 then
1383 -- ******** Copying measure aggregation *******
1384 s_sql := s_get_cmagg_sql;
1385 s_sql := s_sql || ' and MEASURE_ID in (' || s_meas_sql || ')';
1386
1387 -- Note: when reading from template we collect to a different rec.
1388 -- since template does not contain entry for all dimensions. We must
1389 -- loop thro to insert all dimension & measure combinations
1390 if g_copy_det_frm_tmpl = 'Y' then
1391 open c_get_cub_meas_agg for s_sql using g_src_pplan_id;
1392 fetch c_get_cub_meas_agg bulk collect into r_cub_int_maggr;
1393 close c_get_cub_meas_agg;
1394 else
1395 open c_get_cub_meas_agg for s_sql using g_src_pplan_id;
1396 fetch c_get_cub_meas_agg bulk collect into r_cub_meas_aggr;
1397 close c_get_cub_meas_agg;
1398 end if;
1399
1400 if r_cub_meas_aggr.MEASURE_ID.count > 0
1401 or r_cub_int_maggr.MEASURE_ID.count > 0 then
1402 fnd_file.put_line(fnd_file.log, 'Copying cube measure aggregation...');
1403 r_meas_set_lvl := insert_meas_aggr(t_old_new_cub_dim,t_old_new_meas);
1404 else
1405 fnd_file.put_line(fnd_file.log,
1406 'No specific measure aggregation defined for this cube');
1407 end if;
1408 clean_measaggr;
1409
1410 -- ************ COPYING SET LEVEL ***********
1411
1412 s_sql := s_get_set_lvl_sql;
1413 s_sql := s_sql || ' and a.CUBE_DIM_ID in (' || s_cub_dims_sql || ')';
1414
1415 if g_copy_det_frm_tmpl = 'Y' then
1416 open c_get_set_level for s_sql using g_src_pplan_id;
1417 fetch c_get_set_level bulk collect into r_int_set_lvl;
1418 close c_get_set_level;
1419 else
1420 open c_get_set_level for s_sql using g_src_pplan_id;
1421 fetch c_get_set_level bulk collect into r_cub_set_lvl;
1422 close c_get_set_level;
1423 end if;
1424
1425 if r_cub_set_lvl.CUBE_DIM_ID.count > 0
1426 or r_int_set_lvl.CUBE_DIM_ID.count > 0 then
1427 fnd_file.put_line(fnd_file.log, 'Copying cube set level...');
1428 insert_set_level(t_old_new_cub_dim, t_old_new_meas,
1429 r_meas_set_lvl);
1430 else
1431 fnd_file.put_line(fnd_file.log,
1432 'No levels set for this cube dimensions/measures');
1433 end if;
1434 clean_setlvl;
1435 end if;
1436 r_meas_set_lvl.ID.delete;
1437 r_meas_set_lvl.PPA_CODE.delete;
1438 t_old_new_meas.delete;
1439 t_old_new_cub_dim.delete;
1440 end loop;
1441 clean_cubval;
1442
1443 rec_cub_ids.ID.delete;
1444 rec_cub_ids.PPA_CODE.delete;
1445 return(l_ret);
1446 exception
1447 when OTHERS then
1448 fnd_file.put_line(fnd_file.log, 'ERROR COPYING CUBE RELATED DATA...');
1449 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1450 raise;
1451 return(-1);
1452 end copy_cube_data;
1453
1454 procedure copy_price_plan( errbuf out nocopy varchar2,
1455 retcode out nocopy varchar2,
1456 p_from_pp_id in number,
1457 p_new_aw_name in varchar2,
1458 p_copy_det_frm_tmpl in varchar2) is
1459 bfound boolean := false;
1460 l_start_time number;
1461 l_end_time number;
1462 l_ret_dim number;
1463 l_ret_cube number;
1464 l_tmpl_plan_id number;
1465
1466 t_old_new_dim num_type;
1467
1468 cursor c_get_price_plan(pp_id number) is
1469 select INSTANCE_ID, AW_TYPE_CODE, AW_CODE, AW_STATUS_CODE, START_DATE,
1470 END_DATE,BASE_UOM_CODE,
1471 CURRENCY_CODE
1472 from qpr_price_plans_b
1473 where PRICE_PLAN_ID = pp_id
1474 and rownum = 1;
1475
1476 cursor c_get_price_plan_tl(pp_id number) is
1477 select LANGUAGE,SOURCE_LANG, NAME, DESCRIPTION
1478 from qpr_price_plans_tl
1479 where PRICE_PLAN_ID = pp_id;
1480
1481 cursor c_scopes is
1482 select DIM_CODE,HIERARCHY_ID,LEVEL_ID,OPERATOR,SCOPE_VALUE,
1483 SCOPE_VALUE_DESC
1484 from qpr_scopes
1485 where parent_entity_type = 'DATAMART'
1486 and parent_id = p_from_pp_id;
1487
1488 begin
1489 fnd_file.put_line(fnd_file.log, 'Starting to copy...');
1490 select hsecs into l_start_time from v$timer;
1491 fnd_file.put_line(fnd_file.log, 'Start time :'||
1492 to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
1493 fnd_profile.get('CONC_REQUEST_ID', g_request_id);
1494 g_sys_date := sysdate;
1495 g_user_id := fnd_global.user_id;
1496 g_login_id := fnd_global.conc_login_id;
1497 g_prg_appl_id := fnd_global.prog_appl_id;
1498 g_prg_id := fnd_global.conc_program_id;
1499
1500 g_src_pplan_id := p_from_pp_id;
1501 g_copy_det_frm_tmpl := p_copy_det_frm_tmpl;
1502
1503 if g_src_pplan_id = 0 or g_src_pplan_id is null then
1504 raise NO_PPLAN_ID;
1505 end if;
1506
1507 for rec_price_plan in c_get_price_plan(p_from_pp_id) loop
1508 bfound := true;
1509 if p_copy_det_frm_tmpl = 'Y' then
1510 l_tmpl_plan_id := qpr_sr_util.g_datamart_tmpl_id;
1511 g_src_pplan_id := l_tmpl_plan_id;
1512 end if;
1513
1514 fnd_file.put_line(fnd_file.log,'Copying from price plan id:' || g_src_pplan_id);
1515
1516 insert into QPR_PRICE_PLANS_B(PRICE_PLAN_ID, INSTANCE_ID, AW_TYPE_CODE,
1517 AW_STATUS_CODE, AW_CODE, AW_CREATED_FLAG,
1518 START_DATE, END_DATE,BASE_UOM_CODE,
1519 CURRENCY_CODE, AW_XML,
1520 TEMPLATE_FLAG,CREATION_DATE,
1521 CREATED_BY, LAST_UPDATE_DATE,LAST_UPDATED_BY,
1522 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
1523 PROGRAM_ID, REQUEST_ID)
1524 values(qpr_price_plans_s.nextval,
1525 rec_price_plan.INSTANCE_ID,
1526 rec_price_plan.AW_TYPE_CODE,
1527 null,
1528 'QPR' || to_char(qpr_price_plans_s.currval),
1529 'N', rec_price_plan.START_DATE, rec_price_plan.END_DATE,
1530 rec_price_plan.BASE_UOM_CODE,
1531 rec_price_plan.CURRENCY_CODE,
1532 empty_clob(), 'N',
1533 g_sys_date,g_user_id, g_sys_date,
1534 g_user_id, g_login_id, g_prg_appl_id, g_prg_id,
1535 g_request_id)
1536 returning PRICE_PLAN_ID into g_new_pp_id;
1537
1538 for rec_pp_tl in c_get_price_plan_tl(p_from_pp_id) loop
1539 insert into QPR_PRICE_PLANS_TL(PRICE_PLAN_ID, LANGUAGE, SOURCE_LANG,
1540 NAME, DESCRIPTION, CREATION_DATE,
1541 CREATED_BY, LAST_UPDATE_DATE,LAST_UPDATED_BY,
1542 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
1543 PROGRAM_ID, REQUEST_ID)
1544 values(g_new_pp_id, rec_pp_tl.LANGUAGE, rec_pp_tl.SOURCE_LANG,
1545 p_new_aw_name, p_new_aw_name, g_sys_date,
1546 g_user_id, g_sys_date, g_user_id, g_login_id,
1547 g_prg_appl_id, g_prg_id, g_request_id);
1548 end loop;
1549
1550 for rec_scope in c_scopes loop
1551 insert into QPR_SCOPES(SCOPE_ID,PARENT_ENTITY_TYPE, PARENT_ID,
1552 DIM_CODE, HIERARCHY_ID, LEVEL_ID,
1553 OPERATOR, SCOPE_VALUE, SCOPE_VALUE_DESC,
1554 CREATION_DATE,
1555 CREATED_BY, LAST_UPDATE_DATE,LAST_UPDATED_BY,
1556 LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
1557 PROGRAM_ID, REQUEST_ID)
1558 values(qpr_scopes_s.nextval, 'DATAMART', g_new_pp_id,
1559 rec_scope.dim_code, rec_scope.hierarchy_id, rec_scope.level_id,
1560 rec_scope.operator, rec_scope.scope_value,
1561 rec_scope.scope_value_desc, g_sys_date,
1562 g_user_id, g_sys_date, g_user_id, g_login_id,
1563 g_prg_appl_id, g_prg_id, g_request_id);
1564 end loop;
1565 end loop;
1566
1567 if bfound=false then
1568 raise NO_PPLAN_DATA;
1569 end if;
1570
1571 fnd_file.put_line(fnd_file.log, 'Created new price plan:' || g_new_pp_id);
1572
1573 l_ret_dim := copy_dim_data(p_from_pp_id,t_old_new_dim);
1574
1575 if l_ret_dim <> -2 then
1576 l_ret_cube := copy_cube_data(p_from_pp_id,t_old_new_dim);
1577 end if;
1578
1579 commit;
1580 select hsecs into l_end_time from v$timer;
1581 fnd_file.put_line(fnd_file.log, 'End time :'|| to_char(sysdate,
1582 'MM/DD/YYYY:HH:MM:SS'));
1583 fnd_file.put_line(fnd_file.log, 'Time taken for loading(sec):' ||
1584 (l_end_time - l_start_time)/100);
1585 if l_ret_dim = -1 or l_ret_cube = -1 then
1586 raise ERR_IN_DEFN;
1587 end if;
1588 exception
1589 when NO_PPLAN_DATA then
1590 retcode := 2;
1591 errbuf := 'ERROR: ' || substr(SQLERRM,1,1000) ;
1592 fnd_file.put_line(fnd_file.log, 'SOURCE PRICEPLAN/DATAMART DATA NOT FOUND');
1593 rollback;
1594 when NO_PPLAN_ID then
1595 retcode := 2;
1596 errbuf := 'ERROR: SOURCE PRICE PLAN ID NOT MENTIONED';
1597 fnd_file.put_line(fnd_file.log,
1598 'UNABLE TO COPY PRICEPLAN/DATMART DEFINITION');
1599 rollback;
1600 when ERR_IN_DEFN then
1601 retcode := 1;
1602 fnd_file.put_line(fnd_file.log,
1603 'New priceplan/datamart definition is malformed.' ||
1604 'Check the log and source priceplan/datamart definition');
1605 when OTHERS then
1606 retcode := 1;
1607 errbuf := 'ERROR: ' || substr(SQLERRM,1,1000);
1608 fnd_file.put_line(fnd_file.log,
1609 'UNABLE TO COPY PRICEPLAN/DATMART DEFINITION');
1610 fnd_file.put_line(fnd_file.log, 'ERROR: ' || substr(SQLERRM,1,1000));
1611 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1612 rollback;
1613 end copy_price_plan;
1614
1615 END;
1616