[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function insert_dim_values return val_out_type is
rec_dim_out val_out_type;
insert into qpr_dimensions(PRICE_PLAN_DIM_ID, PRICE_PLAN_ID,
DIM_CODE, DIM_PPA_CODE,
DIM_SEQ_NUM, MAND_DIM_FLAG,
TIME_DIM_FLAG , LOB_ENABLED_FLAG ,
DIM_SHORT_NAME , DIM_LONG_NAME ,
DIM_PLURAL_NAME , NATIVE_KEY_FLAG ,
MEASURE_DIM_FLAG , SPARSE_FLAG ,
LOWEST_LVL , LIST_PRICE_PLAN_FLAG ,
TEMPLATE_FLAG, INCLUDE_FLAG,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
PROGRAM_ID, REQUEST_ID)
values(qpr_dimensions_s.nextval, g_new_pp_id,
r_dim_val.DIM_PPA_CODE(i) ||
to_char(g_new_pp_id)|| '_D',
r_dim_val.DIM_PPA_CODE(i),
r_dim_val.DIM_SEQ_NUM(i),
r_dim_val.MAND_DIM_FLAG(i),
r_dim_val.TIME_DIM_FLAG(i),
r_dim_val.LOB_ENABLED_FLAG(i),
r_dim_val.DIM_SHORT_NAME(i),
r_dim_val.DIM_LONG_NAME(i),
r_dim_val.DIM_PLURAL_NAME(i),
r_dim_val.NATIVE_KEY_FLAG(i),
r_dim_val.MEASURE_DIM_FLAG(i),
r_dim_val.SPARSE_FLAG(i),
r_dim_val.LOWEST_LVL(i),
r_dim_val.LIST_PP_FLAG(i),
'N', 'Y',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id,
g_prg_id, g_request_id)
returning PRICE_PLAN_DIM_ID, DIM_CODE bulk collect into rec_dim_out;
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING DIMENSIONS...');
end insert_dim_values;
function insert_dim_attr_values(p_ppdim_id in number)
return num_type is
l_dim_attr_id number;
insert into qpr_dim_attributes(DIM_ATTR_ID, PRICE_PLAN_DIM_ID,
PRICE_PLAN_ID,
ATTR_PPA_CODE, ATTR_SHORT_NAME,
ATTR_LONG_NAME, ATTR_PLURAL_NAME,
ATTR_CLASSIFICATION, ATTR_DATA_TYPE,
DEFAULT_ORDER_FLAG,TEMPLATE_FLAG,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_dim_attributes_s.nextval, p_ppdim_id,
g_new_pp_id, r_dim_attr_val.ATTR_PPA_CODE(i),
r_dim_attr_val.ATTR_SHORT_NAME(i),
r_dim_attr_val.ATTR_LONG_NAME(i),
r_dim_attr_val.ATTR_PLURAL_NAME(i),
r_dim_attr_val.ATTR_CLASSIFICATION(i),
r_dim_attr_val.ATTR_DATA_TYPE(i),
r_dim_attr_val.DEFAULT_ORDER_FLAG(i), 'N',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
returning DIM_ATTR_ID, ATTR_PPA_CODE bulk collect into rec_dim_attr_val;
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING DIMENSION ATTRIBUTES..');
end insert_dim_attr_values;
function insert_hier_values(p_ppdim_id in number) return val_out_type is
rec_hier_out val_out_type;
insert into qpr_hierarchies(HIERARCHY_ID, PRICE_PLAN_DIM_ID,
HIERARCHY_PPA_CODE, PRICE_PLAN_ID,
HIER_SHORT_NAME, HIER_LONG_NAME,
HIER_PLURAL_NAME, HIER_TYPE_CODE,
HIER_DEFAULT_ORDER, DEFAULT_FLAG,
CALENDAR_CODE,TEMPLATE_FLAG,
CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_hierarchies_s.nextval, p_ppdim_id,
r_dim_hier_val.HIERARCHY_PPA_CODE(i),
g_new_pp_id, r_dim_hier_val.HIER_SHORT_NAME(i),
r_dim_hier_val.HIER_LONG_NAME(i),
r_dim_hier_val.HIER_PLURAL_NAME(i),
r_dim_hier_val.HIER_TYPE_CODE(i),
r_dim_hier_val.HIER_DEFAULT_ORDER(i),
r_dim_hier_val.DEFAULT_FLAG(i),
r_dim_hier_val.CALENDAR_CODE(i),'N',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
returning HIERARCHY_ID, HIERARCHY_PPA_CODE bulk collect into rec_hier_out;
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING HIERARCHIES...');
end insert_hier_values;
function insert_hier_lvl_val(p_hier_id in number) return num_type is
rec_lvl_out val_out_type;
insert into qpr_hier_levels(HIERARCHY_LEVEL_ID, HIERARCHY_ID,
LEVEL_PPA_CODE, LEVEL_SEQ_NUM,
PRICE_PLAN_ID, LVL_SHORT_NAME,
LVL_LONG_NAME, LVL_PLURAL_NAME,
MAPPING_VIEW_NAME, MAP_COLUMN,
USER_MAPPING_VIEW_NAME,
USER_MAP_COLUMN, TEMPLATE_FLAG,
INCLUDE_FLAG,CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_hier_levels_s.nextval, p_hier_id,
r_hier_lvl_val.LEVEL_PPA_CODE(i),
r_hier_lvl_val.LEVEL_SEQ_NUM(i),
g_new_pp_id, r_hier_lvl_val.LVL_SHORT_NAME(i),
r_hier_lvl_val.LVL_LONG_NAME(i),
r_hier_lvl_val.LVL_PLURAL_NAME(i),
r_hier_lvl_val.MAPPING_VIEW_NAME(i),
r_hier_lvl_val.MAP_COLUMN(i),
r_hier_lvl_val.USER_MAPPING_VIEW_NAME(i),
r_hier_lvl_val.USER_MAP_COLUMN(i),'N','Y',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
returning HIERARCHY_LEVEL_ID, LEVEL_PPA_CODE bulk collect into rec_lvl_out;
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING HIERARCHY LEVELS...');
end insert_hier_lvl_val;
procedure insert_lvl_attributes is
begin
forall i in r_lvl_attr_val.LEVEL_ATTR_ID.first..
r_lvl_attr_val.LEVEL_ATTR_ID.last
insert into qpr_lvl_attributes(LEVEL_ATTR_ID, HIERARCHY_LEVEL_ID,
DIM_ATTR_ID,
PRICE_PLAN_ID, MAPPING_VIEW_NAME,
MAP_COLUMN, USER_MAPPING_VIEW_NAME,
USER_MAP_COLUMN, TEMPLATE_FLAG,
INCLUDED_FLAG,CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_lvl_attributes_s.nextval,
r_lvl_attr_val.HIERARCHY_LEVEL_ID(i),
r_lvl_attr_val.DIM_ATTR_ID(i),
g_new_pp_id,
r_lvl_attr_val.MAPPING_VIEW_NAME(i),
r_lvl_attr_val.MAP_COLUMN(i),
r_lvl_attr_val.USER_MAPPING_VIEW_NAME(i),
r_lvl_attr_val.USER_MAP_COLUMN(i),'N','Y',
g_sys_date,
g_user_id, g_sys_date, g_user_id,g_login_id, g_prg_appl_id,
g_prg_id, g_request_id);
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING LEVEL ATTRIBUTES...');
end insert_lvl_attributes;
function insert_cube_data return val_out_type is
r_cub_val_out val_out_type;
insert into qpr_cubes(CUBE_ID, CUBE_CODE, CUBE_PPA_CODE, PRICE_PLAN_ID,
CUBE_SHORT_NAME, CUBE_LONG_NAME,CUBE_PLURAL_NAME,
CUBE_AUTO_SOLVE_FLAG, DEFAULT_DATA_TYPE,
PARTITION_HIER,PARTITION_LEVEL, SPARSE_TYPE_CODE,
USE_GLOBAL_INDEX_FLAG, AGGMAP_NAME,
AGGMAP_CACHE_STORE, AGGMAP_CACHE_NA,
TEMPLATE_FLAG,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_cubes_s.nextval,
-- 'QPR_'||r_cub_val.CUBE_PPA_CODE(i) || to_char(g_new_pp_id)
-- || '_CUBE',
r_cub_val.CUBE_CODE(i),
r_cub_val.CUBE_PPA_CODE(i), g_new_pp_id,
r_cub_val.CUBE_SHORT_NAME(i),
r_cub_val.CUBE_LONG_NAME(i),
r_cub_val.CUBE_PLURAL_NAME(i),
r_cub_val.CUBE_AUTO_SOLVE_FLAG(i),
r_cub_val.DEFAULT_DATA_TYPE(i),
r_cub_val.PARTITION_HIER(i),
r_cub_val.PARTITION_LEVEL(i),
r_cub_val.SPARSE_TYPE_CODE(i),
r_cub_val.USE_GLOBAL_INDEX_FLAG(i),
r_cub_val.AGGMAP_NAME(i), r_cub_val.AGGMAP_CACHE_STORE(i),
r_cub_val.AGGMAP_CACHE_NA(i),'N',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
returning CUBE_ID, CUBE_CODE bulk collect into r_cub_val_out;
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE DATA...');
end insert_cube_data;
function insert_cub_meas(p_cube_id in number,p_from_pp_id in number,
p_cube_code in varchar2)
return num_type is
t_meas_old_new_ids num_type;
insert into qpr_measures(MEASURE_ID, CUBE_ID, PRICE_PLAN_ID,
MEASURE_PPA_CODE, MEAS_CREATION_SEQ_NUM,
MEAS_SHORT_NAME, MEAS_LONG_NAME,
MEAS_PLURAL_NAME, MEAS_TYPE, MEAS_DATA_TYPE,
MEAS_AUTO_SOLVE, CAL_MEAS_EXPRESSION_TEXT,
MAPPING_VIEW_NAME, MAP_COLUMN,
USER_MAPPING_VIEW_NAME, USER_MAP_COLUMN,
AGGMAP_NAME,
MEAS_FOLD_SHORT_NAME, MEAS_FOLD_LONG_NAME,
MEAS_FOLD_PLURAL_NAME,TEMPLATE_FLAG,
INCLUDE_FLAG,
CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_measures_s.nextval, p_cube_id, g_new_pp_id,
r_cub_meas_val.MEASURE_PPA_CODE(i),
r_cub_meas_val.MEAS_CREATION_SEQ_NUM(i),
r_cub_meas_val.MEAS_SHORT_NAME(i),
r_cub_meas_val.MEAS_LONG_NAME(i),
r_cub_meas_val.MEAS_PLURAL_NAME(i),
r_cub_meas_val.MEAS_TYPE(i),
r_cub_meas_val.MEAS_DATA_TYPE(i),
r_cub_meas_val.MEAS_AUTO_SOLVE(i),
r_cub_meas_val.CAL_MEAS_EXPRESSION_TEXT(i),
r_cub_meas_val.MAPPING_VIEW_NAME(i),
r_cub_meas_val.MAP_COLUMN(i),
r_cub_meas_val.USER_MAPPING_VIEW_NAME(i),
r_cub_meas_val.USER_MAP_COLUMN(i),
r_cub_meas_val.AGGMAP_NAME(i),
s_folder_name,
s_folder_name,
s_folder_name, 'N', 'Y',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
returning MEASURE_ID, MEASURE_PPA_CODE bulk collect into r_meas_val_out;
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE MEASURES...');
end insert_cub_meas;
function insert_cub_dims(p_cube_id in number,
p_old_new_dimid in num_type,p_cube_def_agg in varchar2)
return num_type is
l_old_dim_id number;
insert into qpr_cube_dims(CUBE_DIM_ID, CUBE_ID,
PRICE_PLAN_ID, PRICE_PLAN_DIM_ID,
AGGMAP_NAME, DIM_OPCODE,
DIM_SEQ_NUM,
MAPPING_VIEW_NAME, MAP_COLUMN,
USER_MAPPING_VIEW_NAME, USER_MAP_COLUMN,
SET_LEVEL_FLAG, DIM_EXPRESSION,
DIM_EXPRESSION_TYPE, WEIGHTED_MEASURE_FLAG,
WEIGHT_MEASURE_NAME, WNAFILL,
DIVIDE_BY_ZERO_FLAG, DECIMAL_OVERFLOW_FLAG,
NASKIP_FLAG,TEMPLATE_FLAG,CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_cube_dims_s.nextval, p_cube_id, g_new_pp_id,
r_cub_dims_val.PRICE_PLAN_DIM_ID(i),
nvl(r_cub_dims_val.AGGMAP_NAME(i), p_cube_def_agg),
r_cub_dims_val.DIM_OPCODE(i),
r_cub_dims_val.DIM_SEQ_NUM(i),
r_cub_dims_val.MAPPING_VIEW_NAME(i),
r_cub_dims_val.MAP_COLUMN(i),
r_cub_dims_val.USER_MAPPING_VIEW_NAME(i),
r_cub_dims_val.USER_MAP_COLUMN(i),
r_cub_dims_val.SET_LEVEL_FLAG(i),
r_cub_dims_val.DIM_EXPRESSION(i),
r_cub_dims_val.DIM_EXPRESSION_TYPE(i),
r_cub_dims_val.WEIGHTED_MEASURE_FLAG(i),
r_cub_dims_val.WEIGHT_MEASURE_NAME(i),
r_cub_dims_val.WNAFILL(i),
r_cub_dims_val.DIVIDE_BY_ZERO_FLAG(i),
r_cub_dims_val.DECIMAL_OVERFLOW_FLAG(i),
r_cub_dims_val.NASKIP_FLAG(i),'N',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id)
returning CUBE_DIM_ID bulk collect into t_new_cub_dims;
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE DIMENSIONS...');
end insert_cub_dims;
function insert_meas_aggr(p_old_new_cub_dim in num_type,
p_old_new_meas in num_type ) return val_out_type is
l_old_meas_id number;
insert into qpr_meas_aggrs(MEAS_AGG_ID, MEASURE_ID, PRICE_PLAN_ID,
CUBE_DIM_ID, AGGMAP_NAME, AGGMAP_CACHE_STORE,
AGGMAP_CACHE_NA, DIM_OPCODE,
SET_LEVEL_FLAG, OVERRIDE_FLAG,
DIM_EXPRESSION, DIM_EXPRESSION_TYPE,
WEIGHTED_MEASURE_FLAG, WEIGHT_MEASURE_NAME,
WNAFILL, DIVIDE_BY_ZERO_FLAG,
DECIMAL_OVERFLOW_FLAG, NASKIP_FLAG,
TEMPLATE_FLAG,CREATION_DATE,CREATED_BY,
LAST_UPDATE_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
PROGRAM_ID, REQUEST_ID)
values(qpr_meas_aggrs_s.nextval,
r_cub_meas_aggr.MEASURE_ID(i), g_new_pp_id,
r_cub_meas_aggr.CUBE_DIM_ID(i),
r_cub_meas_aggr.AGGMAP_NAME(i),
r_cub_meas_aggr.AGGMAP_CACHE_STORE(i),
r_cub_meas_aggr.AGGMAP_CACHE_NA(i),
r_cub_meas_aggr.DIM_OPCODE(i),
r_cub_meas_aggr.SET_LEVEL_FLAG(i),
r_cub_meas_aggr.OVERRIDE_FLAG(i),
r_cub_meas_aggr.DIM_EXPRESSION(i),
r_cub_meas_aggr.DIM_EXPRESSION_TYPE(i),
r_cub_meas_aggr.WEIGHTED_MEASURE_FLAG(i),
r_cub_meas_aggr.WEIGHT_MEASURE_NAME(i),
r_cub_meas_aggr.WNAFILL(i),
r_cub_meas_aggr.DIVIDE_BY_ZERO_FLAG(i),
r_cub_meas_aggr.DECIMAL_OVERFLOW_FLAG(i),
r_cub_meas_aggr.NASKIP_FLAG(i), 'N',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id);
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING MEASURE AGGREGATION...');
end insert_meas_aggr;
procedure insert_set_level(p_old_new_cub_dim in num_type,
p_old_new_meas in num_type,
p_r_meas_set_lvl in val_out_type) is
l_old_dim_id number;
insert into qpr_set_levels(SET_DIM_LEVEL_ID, CUBE_DIM_ID, MEASURE_ID,
PRICE_PLAN_ID,
LEVEL_SHORT_NAME, DIM_EXPRESSION_TYPE,
AGGMAP_NAME,TEMPLATE_FLAG,CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
REQUEST_ID)
values(qpr_set_levels_s.nextval,
r_cub_set_lvl.CUBE_DIM_ID(i),
r_cub_set_lvl.MEASURE_ID(i), g_new_pp_id,
r_cub_set_lvl.LEVEL_SHORT_NAME(i),
r_cub_set_lvl.DIM_EXPRESSION_TYPE(i),
r_cub_set_lvl.AGGMAP_NAME(i), 'N',
g_sys_date, g_user_id, g_sys_date, g_user_id,
g_login_id, g_prg_appl_id, g_prg_id, g_request_id);
fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CUBE SET LEVEL... ');
end insert_set_level;
r_dim_attr_val.DIM_ATTR_ID.delete;
r_dim_attr_val.ATTR_PPA_CODE.delete;
r_dim_attr_val.ATTR_SHORT_NAME.delete;
r_dim_attr_val.ATTR_LONG_NAME.delete;
r_dim_attr_val.ATTR_PLURAL_NAME.delete;
r_dim_attr_val.ATTR_CLASSIFICATION.delete;
r_dim_attr_val.ATTR_DATA_TYPE.delete;
r_dim_attr_val.DEFAULT_ORDER_FLAG.delete;
r_hier_lvl_val.HIERARCHY_LEVEL_ID.delete;
r_hier_lvl_val.LEVEL_PPA_CODE.delete;
r_hier_lvl_val.LEVEL_SEQ_NUM.delete;
r_hier_lvl_val.LVL_SHORT_NAME.delete;
r_hier_lvl_val.LVL_LONG_NAME.delete;
r_hier_lvl_val.LVL_PLURAL_NAME.delete;
r_hier_lvl_val.MAPPING_VIEW_NAME.delete;
r_hier_lvl_val.MAP_COLUMN.delete;
r_hier_lvl_val.USER_MAPPING_VIEW_NAME.delete;
r_hier_lvl_val.USER_MAP_COLUMN.delete;
r_lvl_attr_val.LEVEL_ATTR_ID.delete;
r_lvl_attr_val.HIERARCHY_LEVEL_ID.delete;
r_lvl_attr_val.DIM_ATTR_ID.delete;
r_lvl_attr_val.MAPPING_VIEW_NAME.delete;
r_lvl_attr_val.MAP_COLUMN.delete;
r_lvl_attr_val.USER_MAPPING_VIEW_NAME.delete;
r_lvl_attr_val.USER_MAP_COLUMN.delete;
r_dim_hier_val.HIERARCHY_ID.delete;
r_dim_hier_val.HIERARCHY_PPA_CODE.delete;
r_dim_hier_val.HIER_SHORT_NAME.delete;
r_dim_hier_val.HIER_LONG_NAME.delete;
r_dim_hier_val.HIER_PLURAL_NAME.delete;
r_dim_hier_val.HIER_TYPE_CODE.delete;
r_dim_hier_val.HIER_DEFAULT_ORDER.delete;
r_dim_hier_val.DEFAULT_FLAG.delete;
r_dim_hier_val.CALENDAR_CODE.delete;
r_dim_val.SRC_PRICE_PLAN_DIM_ID.delete;
r_dim_val.TMPL_PRICE_PLAN_DIM_ID.delete;
r_dim_val.DIM_CODE.delete;
r_dim_val.DIM_PPA_CODE.delete;
r_dim_val.DIM_SEQ_NUM.delete;
r_dim_val.DIM_SHORT_NAME.delete;
r_dim_val.DIM_LONG_NAME.delete;
r_dim_val.DIM_PLURAL_NAME.delete;
r_dim_val.TIME_DIM_FLAG.delete;
r_dim_val.NATIVE_KEY_FLAG.delete;
r_dim_val.MEASURE_DIM_FLAG.delete;
r_dim_val.SPARSE_FLAG.delete;
r_dim_val.MAND_DIM_FLAG.delete;
r_dim_val.LOB_ENABLED_FLAG.delete;
r_dim_val.LOWEST_LVL.delete;
r_dim_val.LIST_PP_FLAG.delete;
r_cub_meas_val.MEASURE_ID.delete;
r_cub_meas_val.MEASURE_PPA_CODE.delete;
r_cub_meas_val.MEAS_CREATION_SEQ_NUM.delete;
r_cub_meas_val.MEAS_SHORT_NAME.delete;
r_cub_meas_val.MEAS_LONG_NAME.delete;
r_cub_meas_val.MEAS_PLURAL_NAME.delete;
r_cub_meas_val.MEAS_TYPE.delete;
r_cub_meas_val.MEAS_DATA_TYPE.delete;
r_cub_meas_val.MEAS_AUTO_SOLVE.delete;
r_cub_meas_val.CAL_MEAS_EXPRESSION_TEXT.delete;
r_cub_meas_val.MAPPING_VIEW_NAME.delete;
r_cub_meas_val.MAP_COLUMN.delete;
r_cub_meas_val.USER_MAPPING_VIEW_NAME.delete;
r_cub_meas_val.USER_MAP_COLUMN.delete;
r_cub_meas_val.AGGMAP_NAME.delete;
r_cub_meas_val.MEAS_FOLD_SHORT_NAME.delete;
r_cub_meas_val.MEAS_FOLD_LONG_NAME.delete;
r_cub_meas_val.MEAS_FOLD_PLURAL_NAME.delete;
r_cub_dims_val.CUBE_DIM_ID.delete;
r_cub_dims_val.PRICE_PLAN_DIM_ID.delete;
r_cub_dims_val.AGGMAP_NAME.delete;
r_cub_dims_val.DIM_OPCODE.delete;
r_cub_dims_val.DIM_SEQ_NUM.delete;
r_cub_dims_val.MAPPING_VIEW_NAME.delete;
r_cub_dims_val.MAP_COLUMN.delete;
r_cub_dims_val.USER_MAPPING_VIEW_NAME.delete;
r_cub_dims_val.USER_MAP_COLUMN.delete;
r_cub_dims_val.SET_LEVEL_FLAG.delete;
r_cub_dims_val.DIM_EXPRESSION.delete;
r_cub_dims_val.DIM_EXPRESSION_TYPE.delete;
r_cub_dims_val.WEIGHTED_MEASURE_FLAG.delete;
r_cub_dims_val.WEIGHT_MEASURE_NAME.delete;
r_cub_dims_val.WNAFILL.delete;
r_cub_dims_val.DIVIDE_BY_ZERO_FLAG.delete;
r_cub_dims_val.DECIMAL_OVERFLOW_FLAG.delete;
r_cub_dims_val.NASKIP_FLAG.delete;
r_cub_meas_aggr.MEASURE_ID.delete;
r_cub_meas_aggr.CUBE_DIM_ID.delete;
r_cub_meas_aggr.AGGMAP_NAME.delete;
r_cub_meas_aggr.AGGMAP_CACHE_STORE.delete;
r_cub_meas_aggr.AGGMAP_CACHE_NA.delete;
r_cub_meas_aggr.DIM_OPCODE.delete;
r_cub_meas_aggr.SET_LEVEL_FLAG.delete;
r_cub_meas_aggr.OVERRIDE_FLAG.delete;
r_cub_meas_aggr.DIM_EXPRESSION.delete;
r_cub_meas_aggr.DIM_EXPRESSION_TYPE.delete;
r_cub_meas_aggr.WEIGHTED_MEASURE_FLAG.delete;
r_cub_meas_aggr.WEIGHT_MEASURE_NAME.delete;
r_cub_meas_aggr.WNAFILL.delete;
r_cub_meas_aggr.DIVIDE_BY_ZERO_FLAG.delete;
r_cub_meas_aggr.DECIMAL_OVERFLOW_FLAG.delete;
r_cub_meas_aggr.NASKIP_FLAG.delete;
r_cub_int_maggr.MEASURE_ID.delete;
r_cub_int_maggr.CUBE_DIM_ID.delete;
r_cub_int_maggr.AGGMAP_NAME.delete;
r_cub_int_maggr.AGGMAP_CACHE_STORE.delete;
r_cub_int_maggr.AGGMAP_CACHE_NA.delete;
r_cub_int_maggr.DIM_OPCODE.delete;
r_cub_int_maggr.SET_LEVEL_FLAG.delete;
r_cub_int_maggr.OVERRIDE_FLAG.delete;
r_cub_int_maggr.DIM_EXPRESSION.delete;
r_cub_int_maggr.DIM_EXPRESSION_TYPE.delete;
r_cub_int_maggr.WEIGHTED_MEASURE_FLAG.delete;
r_cub_int_maggr.WEIGHT_MEASURE_NAME.delete;
r_cub_int_maggr.WNAFILL.delete;
r_cub_int_maggr.DIVIDE_BY_ZERO_FLAG.delete;
r_cub_int_maggr.DECIMAL_OVERFLOW_FLAG.delete;
r_cub_int_maggr.NASKIP_FLAG.delete;
r_cub_set_lvl.CUBE_DIM_ID.delete;
r_cub_set_lvl.MEASURE_ID.delete;
r_cub_set_lvl.LEVEL_SHORT_NAME.delete;
r_cub_set_lvl.DIM_EXPRESSION_TYPE.delete;
r_cub_set_lvl.AGGMAP_NAME.delete;
r_cub_set_lvl.SET_LEVEL_FLAG.delete;
r_int_set_lvl.CUBE_DIM_ID.delete;
r_int_set_lvl.MEASURE_ID.delete;
r_int_set_lvl.LEVEL_SHORT_NAME.delete;
r_int_set_lvl.DIM_EXPRESSION_TYPE.delete;
r_int_set_lvl.AGGMAP_NAME.delete;
r_int_set_lvl.SET_LEVEL_FLAG.delete;
r_cub_val.SRC_CUBE_ID.delete;
r_cub_val.TMPL_CUBE_ID.delete;
r_cub_val.CUBE_PPA_CODE.delete;
r_cub_val.CUBE_CODE.delete;
r_cub_val.CUBE_SHORT_NAME.delete;
r_cub_val.CUBE_LONG_NAME.delete;
r_cub_val.CUBE_PLURAL_NAME.delete;
r_cub_val.CUBE_AUTO_SOLVE_FLAG.delete;
r_cub_val.DEFAULT_DATA_TYPE.delete;
r_cub_val.PARTITION_HIER.delete;
r_cub_val.PARTITION_LEVEL.delete;
r_cub_val.SPARSE_TYPE_CODE.delete;
r_cub_val.USE_GLOBAL_INDEX_FLAG.delete;
r_cub_val.AGGMAP_NAME.delete;
r_cub_val.AGGMAP_CACHE_STORE.delete;
r_cub_val.AGGMAP_CACHE_NA.delete;
select src.PRICE_PLAN_DIM_ID, tmpl.PRICE_PLAN_DIM_ID, tmpl.DIM_CODE,
tmpl.DIM_PPA_CODE, tmpl.DIM_SEQ_NUM, tmpl.MAND_DIM_FLAG,
tmpl.TIME_DIM_FLAG, tmpl.LOB_ENABLED_FLAG, tmpl.DIM_SHORT_NAME,
tmpl.DIM_LONG_NAME, tmpl.DIM_PLURAL_NAME,
tmpl.NATIVE_KEY_FLAG,
tmpl.MEASURE_DIM_FLAG, tmpl.SPARSE_FLAG , tmpl.LOWEST_LVL,
tmpl.LIST_PRICE_PLAN_FLAG
bulk collect into r_dim_val
from qpr_dimensions src, qpr_dimensions tmpl
where src.PRICE_PLAN_ID = p_from_pp_id
and tmpl.PRICE_PLAN_ID = g_src_pplan_id
and src.DIM_PPA_CODE = tmpl.DIM_PPA_CODE
and nvl(tmpl.INCLUDE_FLAG, 'Y') = 'Y';
select PRICE_PLAN_DIM_ID, null, DIM_CODE, DIM_PPA_CODE, DIM_SEQ_NUM,
MAND_DIM_FLAG, TIME_DIM_FLAG, LOB_ENABLED_FLAG, DIM_SHORT_NAME,
DIM_LONG_NAME, DIM_PLURAL_NAME, NATIVE_KEY_FLAG,
MEASURE_DIM_FLAG, SPARSE_FLAG , LOWEST_LVL, LIST_PRICE_PLAN_FLAG
bulk collect into r_dim_val
from qpr_dimensions
where PRICE_PLAN_ID = g_src_pplan_id
and nvl(INCLUDE_FLAG, 'Y') = 'Y';
rec_dim_ids := insert_dim_values;
s_lvl_sql := 'select LEVEL_ATTR_ID, HIERARCHY_LEVEL_ID,DIM_ATTR_ID,';
select DIM_ATTR_ID, ATTR_PPA_CODE, ATTR_SHORT_NAME, ATTR_LONG_NAME,
ATTR_PLURAL_NAME, ATTR_CLASSIFICATION, ATTR_DATA_TYPE,
DEFAULT_ORDER_FLAG
bulk collect into r_dim_attr_val
from qpr_dim_attributes
where PRICE_PLAN_ID = g_src_pplan_id
and PRICE_PLAN_DIM_ID = l_pplan_dim_id;
t_old_new_dim_attr := insert_dim_attr_values(rec_dim_ids.ID(i));
select HIERARCHY_ID,HIERARCHY_PPA_CODE,HIER_SHORT_NAME, HIER_LONG_NAME,
HIER_PLURAL_NAME, HIER_TYPE_CODE, HIER_DEFAULT_ORDER,DEFAULT_FLAG,
CALENDAR_CODE
bulk collect into r_dim_hier_val
from qpr_hierarchies
where PRICE_PLAN_ID = g_src_pplan_id
and PRICE_PLAN_DIM_ID = l_pplan_dim_id;
rec_hier_ids := insert_hier_values(rec_dim_ids.ID(i));
select HIERARCHY_LEVEL_ID, LEVEL_PPA_CODE, LEVEL_SEQ_NUM,
LVL_SHORT_NAME, LVL_LONG_NAME, LVL_PLURAL_NAME,
MAPPING_VIEW_NAME, MAP_COLUMN , USER_MAPPING_VIEW_NAME,
USER_MAP_COLUMN
bulk collect into r_hier_lvl_val
from qpr_hier_levels
where PRICE_PLAN_ID = g_src_pplan_id
and HIERARCHY_ID = r_dim_hier_val.HIERARCHY_ID(j)
and nvl(INCLUDE_FLAG, 'Y') = 'Y';
t_old_new_lvl := insert_hier_lvl_val(rec_hier_ids.ID(j));
insert_lvl_attributes;
t_old_new_lvl.delete;
t_old_new_dim_attr.delete;
rec_hier_ids.ID.delete;
rec_hier_ids.PPA_CODE.delete;
rec_dim_ids.id.delete;
rec_dim_ids.PPA_CODE.delete;
s_sql := 'select MEASURE_ID, CUBE_DIM_ID,AGGMAP_NAME, AGGMAP_CACHE_STORE,';
s_sql := ' select a.CUBE_DIM_ID,a.MEASURE_ID, a.LEVEL_SHORT_NAME ,' ;
s_sql := ' select b.CUBE_DIM_ID, a.MEASURE_ID, ' ;
select src.CUBE_ID, tmpl.CUBE_ID , src.CUBE_PPA_CODE,
tmpl.CUBE_CODE || g_new_pp_id || '_C' ,
tmpl.CUBE_SHORT_NAME, tmpl.CUBE_LONG_NAME, tmpl.CUBE_PLURAL_NAME,
tmpl.CUBE_AUTO_SOLVE_FLAG, tmpl.DEFAULT_DATA_TYPE, tmpl.PARTITION_HIER,
tmpl.PARTITION_LEVEL,tmpl.SPARSE_TYPE_CODE, tmpl.USE_GLOBAL_INDEX_FLAG,
tmpl.AGGMAP_NAME, tmpl.AGGMAP_CACHE_STORE, tmpl.AGGMAP_CACHE_NA
bulk collect into r_cub_val
from qpr_cubes src, qpr_cubes tmpl
where src.PRICE_PLAN_ID = p_from_pp_id
and tmpl.PRICE_PLAN_ID = g_src_pplan_id and
src.CUBE_PPA_CODE = tmpl.CUBE_PPA_CODE
order by tmpl.cube_id;
select CUBE_ID, null , CUBE_PPA_CODE,
replace(CUBE_CODE, p_from_pp_id, g_new_pp_id), CUBE_SHORT_NAME,
CUBE_LONG_NAME, CUBE_PLURAL_NAME, CUBE_AUTO_SOLVE_FLAG,
DEFAULT_DATA_TYPE, PARTITION_HIER,PARTITION_LEVEL,SPARSE_TYPE_CODE,
USE_GLOBAL_INDEX_FLAG,AGGMAP_NAME,AGGMAP_CACHE_STORE,AGGMAP_CACHE_NA
bulk collect into r_cub_val
from qpr_cubes where PRICE_PLAN_ID = p_from_pp_id
order by cube_id;
rec_cub_ids := insert_cube_data;
select MEASURE_ID, MEASURE_PPA_CODE, MEAS_CREATION_SEQ_NUM,
MEAS_SHORT_NAME, MEAS_LONG_NAME, MEAS_PLURAL_NAME, MEAS_TYPE,
MEAS_DATA_TYPE, MEAS_AUTO_SOLVE,
CAL_MEAS_EXPRESSION_TEXT,
MAPPING_VIEW_NAME, MAP_COLUMN, USER_MAPPING_VIEW_NAME,
USER_MAP_COLUMN, AGGMAP_NAME,MEAS_FOLD_SHORT_NAME,
MEAS_FOLD_LONG_NAME, MEAS_FOLD_PLURAL_NAME
bulk collect into r_cub_meas_val
from qpr_measures
where PRICE_PLAN_ID = g_src_pplan_id
and CUBE_ID = l_ref_cub_id
and nvl(INCLUDE_FLAG, 'Y') = 'Y';
t_old_new_meas := insert_cub_meas( rec_cub_ids.ID(i),p_from_pp_id,
rec_cub_ids.PPA_CODE(i));
select CUBE_DIM_ID, PRICE_PLAN_DIM_ID, AGGMAP_NAME,
DIM_OPCODE, DIM_SEQ_NUM,MAPPING_VIEW_NAME,MAP_COLUMN,
USER_MAPPING_VIEW_NAME,USER_MAP_COLUMN,SET_LEVEL_FLAG,
DIM_EXPRESSION,DIM_EXPRESSION_TYPE,WEIGHTED_MEASURE_FLAG,
WEIGHT_MEASURE_NAME, WNAFILL,DIVIDE_BY_ZERO_FLAG,
DECIMAL_OVERFLOW_FLAG,NASKIP_FLAG
bulk collect into r_cub_dims_val
from qpr_cube_dims
where PRICE_PLAN_ID = g_src_pplan_id
and CUBE_ID = l_ref_cub_id;
t_old_new_cub_dim := insert_cub_dims(rec_cub_ids.ID(i),
p_t_old_new_dim,
r_cub_val.AGGMAP_NAME(i));
r_meas_set_lvl := insert_meas_aggr(t_old_new_cub_dim,t_old_new_meas);
insert_set_level(t_old_new_cub_dim, t_old_new_meas,
r_meas_set_lvl);
r_meas_set_lvl.ID.delete;
r_meas_set_lvl.PPA_CODE.delete;
t_old_new_meas.delete;
t_old_new_cub_dim.delete;
rec_cub_ids.ID.delete;
rec_cub_ids.PPA_CODE.delete;
select INSTANCE_ID, AW_TYPE_CODE, AW_CODE, AW_STATUS_CODE, START_DATE,
END_DATE,BASE_UOM_CODE,
CURRENCY_CODE
from qpr_price_plans_b
where PRICE_PLAN_ID = pp_id
and rownum = 1;
select LANGUAGE,SOURCE_LANG, NAME, DESCRIPTION
from qpr_price_plans_tl
where PRICE_PLAN_ID = pp_id;
select DIM_CODE,HIERARCHY_ID,LEVEL_ID,OPERATOR,SCOPE_VALUE,
SCOPE_VALUE_DESC
from qpr_scopes
where parent_entity_type = 'DATAMART'
and parent_id = p_from_pp_id;
select hsecs into l_start_time from v$timer;
insert into QPR_PRICE_PLANS_B(PRICE_PLAN_ID, INSTANCE_ID, AW_TYPE_CODE,
AW_STATUS_CODE, AW_CODE, AW_CREATED_FLAG,
START_DATE, END_DATE,BASE_UOM_CODE,
CURRENCY_CODE, AW_XML,
TEMPLATE_FLAG,CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
PROGRAM_ID, REQUEST_ID)
values(qpr_price_plans_s.nextval,
rec_price_plan.INSTANCE_ID,
rec_price_plan.AW_TYPE_CODE,
null,
'QPR' || to_char(qpr_price_plans_s.currval),
'N', rec_price_plan.START_DATE, rec_price_plan.END_DATE,
rec_price_plan.BASE_UOM_CODE,
rec_price_plan.CURRENCY_CODE,
empty_clob(), 'N',
g_sys_date,g_user_id, g_sys_date,
g_user_id, g_login_id, g_prg_appl_id, g_prg_id,
g_request_id)
returning PRICE_PLAN_ID into g_new_pp_id;
insert into QPR_PRICE_PLANS_TL(PRICE_PLAN_ID, LANGUAGE, SOURCE_LANG,
NAME, DESCRIPTION, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
PROGRAM_ID, REQUEST_ID)
values(g_new_pp_id, rec_pp_tl.LANGUAGE, rec_pp_tl.SOURCE_LANG,
p_new_aw_name, p_new_aw_name, g_sys_date,
g_user_id, g_sys_date, g_user_id, g_login_id,
g_prg_appl_id, g_prg_id, g_request_id);
insert into QPR_SCOPES(SCOPE_ID,PARENT_ENTITY_TYPE, PARENT_ID,
DIM_CODE, HIERARCHY_ID, LEVEL_ID,
OPERATOR, SCOPE_VALUE, SCOPE_VALUE_DESC,
CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID,
PROGRAM_ID, REQUEST_ID)
values(qpr_scopes_s.nextval, 'DATAMART', g_new_pp_id,
rec_scope.dim_code, rec_scope.hierarchy_id, rec_scope.level_id,
rec_scope.operator, rec_scope.scope_value,
rec_scope.scope_value_desc, g_sys_date,
g_user_id, g_sys_date, g_user_id, g_login_id,
g_prg_appl_id, g_prg_id, g_request_id);
select hsecs into l_end_time from v$timer;