eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.QPR_TRANSFORMATION SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 54


	select transf_type_code into l_transf_type_code
	from qpr_transf_groups_b
	where transf_group_id = p_transf_group_id;

Line: 88


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 ;

Line: 108


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)));

Line: 175


l_insert_measure number;

Line: 194


	l_insert_measure:=0;

Line: 210


				l_insert_measure:=1;

Line: 211


				fnd_file.put_line(fnd_file.log, 'Insert needed');

Line: 214


			l_insert_measure:=0;

Line: 217


				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;

Line: 224


				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;

Line: 231


				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;

Line: 244


		l_insert_measure:=1;

Line: 246


	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;

Line: 265


			  select qpr_dimension_values_s.nextval
				into l_next_seq from dual ;

Line: 278


			'Inserting band dim with id :'||l_next_seq);

Line: 279


			 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);

Line: 319


				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;

Line: 330


				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;

Line: 343


				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;

Line: 355


			fnd_file.put_line(fnd_file.log, 'Updated '||
				sql%rowcount ||' records');

Line: 425


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;

Line: 449


	sql1_text := ' select a.dim_value_id as dim_value_id ' ||
		    ' from qpr_dimension_values a, qpr_transf_rules_b b ';

Line: 516


	c_dim_rec.dim_value_id.delete;

Line: 517


	fnd_file.put_line(fnd_file.log, 'Delete');

Line: 525


		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);

Line: 559


		fnd_file.put_line(fnd_file.log, 'Number of rows updated: '||sql%rowcount);

Line: 570


			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);

Line: 587


			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);