The following lines contain the word 'select', 'insert', 'update' or 'delete':
select transf_type_code into l_transf_type_code
from qpr_transf_groups_b
where transf_group_id = p_transf_group_id;
select TRANSF_HEADER_ID,
FROM_DIM_MEAS_CODE,
MEAS_CODE,
LIMIT_DIM_FLAG,
TO_DIM_CODE,
TO_LEVEL_ID,
TO_VALUE,
TO_VALUE_DESC
from qpr_transf_headers_b
where TRANSF_GROUP_ID=p_transf_group_id
and from_dim_meas_code is not null
and meas_code is not null
and to_dim_code is not null
and to_level_id is not null
and to_value is not null ;
select csd.MEASURE_VALUE_ID,
csd.INSTANCE_ID,
csd.PRD_LEVEL_VALUE,
csd.ORD_LEVEL_VALUE,
csd.MEASURE4_NUMBER,
qtr.LEVEL_VALUE_FROM,
qtr.LEVEL_VALUE_TO,
qtr.LIMIT_DIM_CODE,
qtr.LIMIT_DIM_LEVEL,
qtr.LIMIT_DIM_LEVEL_VALUE
from qpr_measure_data csd, qpr_transf_rules_b qtr
where csd.measure_type_code = p_from_dim_meas_code and
csd.instance_id=p_instance_id and
csd.time_level_value between p_from_date and p_to_date
and qtr.transf_header_id = p_transf_header_id and
((p_meas_type_code = '1' and csd.measure1_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure1_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure1_number)) or
(p_meas_type_code = '2' and csd.measure2_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure2_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure2_number)) or
(p_meas_type_code = '3' and csd.measure3_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure3_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure3_number)) or
(p_meas_type_code = '4' and csd.measure4_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure4_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure4_number)) or
(p_meas_type_code = '5' and csd.measure5_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure5_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure5_number)) or
(p_meas_type_code = '6' and csd.measure6_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure6_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure6_number)) or
(p_meas_type_code = '7' and csd.measure7_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure7_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure7_number)) or
(p_meas_type_code = '8' and csd.measure8_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure8_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure8_number)) or
(p_meas_type_code = '9' and csd.measure9_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure9_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure9_number)) or
(p_meas_type_code = '10' and csd.measure10_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure10_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure10_number)) or
(p_meas_type_code = '11' and csd.measure11_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure11_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure11_number)) or
(p_meas_type_code = '12' and csd.measure12_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure12_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure12_number)) or
(p_meas_type_code = '13' and csd.measure13_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure13_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure13_number)) or
(p_meas_type_code = '14' and csd.measure14_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure14_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure14_number)) or
(p_meas_type_code = '15' and csd.measure14_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure15_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure15_number)) or
(p_meas_type_code = '16' and csd.measure16_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure16_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure16_number)) or
(p_meas_type_code = '17' and csd.measure17_number between
nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure17_number)
and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure17_number)));
l_insert_measure number;
l_insert_measure:=0;
l_insert_measure:=1;
fnd_file.put_line(fnd_file.log, 'Insert needed');
l_insert_measure:=0;
select 1 into l_insert_measure
from qpr_dimension_values
where dim_code='PRD'
and hierarchy_code='PRODUCTCATEGORY'
and instance_id = p_instance_id and
level2_value = measure_rec.limit_dim_level_value;
select 1 into l_insert_measure
from qpr_dimension_values
where dim_code='PRD'
and hierarchy_code='PRODUCTFAMILY'
and instance_id = p_instance_id and
level2_value = measure_rec.limit_dim_level_value;
select 1 into l_insert_measure
from qpr_dimension_values
where dim_code='PRD'
and hierarchy_code='PRODUCTCATEGORY'
and instance_id = p_instance_id and
level1_value = measure_rec.limit_dim_level_value;
l_insert_measure:=1;
if l_insert_measure = 1 then
begin
select 1 into l_dummy
from qpr_dimension_values
where dim_code = transf_header_rec.to_dim_code
and hierarchy_code = nvl(decode(transf_header_rec.to_dim_code,
'DSB', 'DISC_BAND',
'VLB', 'VOL_BAND',
'MGB', 'MRGBAND', null), hierarchy_code)
and level1_value = transf_header_rec.to_value
and instance_id = p_instance_id
and rownum<2;
select qpr_dimension_values_s.nextval
into l_next_seq from dual ;
'Inserting band dim with id :'||l_next_seq);
INSERT INTO qpr_dimension_values(instance_id,
dim_value_id,
dim_code,
hierarchy_code,
level1_value,
level1_desc,
level2_value,
level2_desc,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID) values
(measure_rec.instance_id,
l_next_seq,
transf_header_rec.to_dim_code,
decode(transf_header_rec.to_dim_code,
'DSB', 'DISC_BAND',
'VLB', 'VOL_BAND',
'MGB', 'MRGBAND', null),
transf_header_rec.TO_VALUE,
transf_header_rec.TO_VALUE_DESC,
l_all_value,
l_all_desc,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
null);
update qpr_measure_data
set dsb_level_value = transf_header_rec.to_value,
measure7_number =
qpr_transformation.get_num(measure_rec.level_value_to),
measure8_number =
qpr_transformation.get_num(measure_rec.level_value_from),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where measure_value_id = measure_rec.measure_value_id;
update qpr_measure_data
set vlb_level_value = transf_header_rec.to_value,
measure11_number =
qpr_transformation.get_num(measure_rec.level_value_to),
measure12_number =
qpr_transformation.get_num(measure_rec.level_value_from),
measure9_number = measure_rec.measure4_number,
measure10_number = measure_rec.measure4_number,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where measure_value_id = measure_rec.measure_value_id;
update qpr_measure_data
set mgb_level_value = transf_header_rec.to_value,
measure18_number =
qpr_transformation.get_num(measure_rec.level_value_to),
measure19_number =
qpr_transformation.get_num(measure_rec.level_value_from),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where measure_value_id =
measure_rec.measure_value_id;
fnd_file.put_line(fnd_file.log, 'Updated '||
sql%rowcount ||' records');
select TRANSF_HEADER_ID,
FROM_DIM_MEAS_CODE,
FROM_DIM_HIER_CODE,
LIMIT_DIM_FLAG,
FROM_LEVEL_ID,
TO_LEVEL_ID,
TO_VALUE,
TO_VALUE_DESC
from qpr_transf_headers_b
where TRANSF_GROUP_ID=p_transf_group_id;
sql1_text := ' select a.dim_value_id as dim_value_id ' ||
' from qpr_dimension_values a, qpr_transf_rules_b b ';
c_dim_rec.dim_value_id.delete;
fnd_file.put_line(fnd_file.log, 'Delete');
update qpr_dimension_values
set
level2_value = nvl(decode(transf_header_rec.to_level_id, 2,
transf_header_rec.to_value, null), level2_value),
level2_desc = nvl(decode(transf_header_rec.to_level_id, 2,
transf_header_rec.to_value_desc, null), level2_desc),
level3_value = nvl(decode(transf_header_rec.to_level_id, 3,
transf_header_rec.to_value, null), level3_value),
level3_desc = nvl(decode(transf_header_rec.to_level_id, 3,
transf_header_rec.to_value_desc, null), level3_desc),
level4_value = nvl(decode(transf_header_rec.to_level_id, 4,
transf_header_rec.to_value, null), level4_value),
level4_desc = nvl(decode(transf_header_rec.to_level_id, 4,
transf_header_rec.to_value_desc, null), level4_desc),
level5_value = nvl(decode(transf_header_rec.to_level_id, 5,
transf_header_rec.to_value, null), level5_value),
level5_desc = nvl(decode(transf_header_rec.to_level_id, 5,
transf_header_rec.to_value_desc, null), level5_desc),
level6_value = nvl(decode(transf_header_rec.to_level_id, 6,
transf_header_rec.to_value, null), level6_value),
level6_desc = nvl(decode(transf_header_rec.to_level_id, 6,
transf_header_rec.to_value_desc, null), level6_desc),
level7_value = nvl(decode(transf_header_rec.to_level_id, 7,
transf_header_rec.to_value, null), level7_value),
level7_desc = nvl(decode(transf_header_rec.to_level_id, 7,
transf_header_rec.to_value_desc, null), level7_desc),
level8_value = nvl(decode(transf_header_rec.to_level_id, 8,
transf_header_rec.to_value, null), level8_value),
level8_desc = nvl(decode(transf_header_rec.to_level_id, 8,
transf_header_rec.to_value_desc, null), level8_desc),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where dim_value_id = c_dim_rec.dim_value_id(I);
fnd_file.put_line(fnd_file.log, 'Number of rows updated: '||sql%rowcount);
update qpr_dimension_values
set level2_value = transf_header_rec.to_value,
level2_desc = transf_header_rec.to_value_desc,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where dim_value_id = c_dim_rec.dim_value_id(I);
update qpr_dimension_values
set level3_value = transf_header_rec.to_value,
level3_desc = transf_header_rec.to_value_desc,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where dim_value_id = c_dim_rec.dim_value_id(I);