APPS.QPR_LOAD_DIM_DATA SQL Statements

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

Line: 14

procedure insert_dimension_data(
	p_instance_id in number,
	p_dim_code in varchar2,
	p_hier_code in varchar2,
	c_dim_data_rec in out nocopy QPR_LOAD_DIM_DATA.DIM_DATA_REC_TYPE);

Line: 20

/*procedure delete_duplicate_data(
	p_instance_id in number);

Line: 35

select distinct dim_code dim, hier_code hier
from qpr_dim_sources
where instance_id = l_dim_src_instance_id
and instance_type = l_inst_type;

Line: 42

select distinct hier_code hier
from qpr_dim_sources
where instance_id = l_dim_src_instance_id
and dim_code = p_dim_code
and instance_type = l_inst_type;

Line: 63

	select hsecs into l_start_time from v$timer;

Line: 71

	select instance_type into l_inst_type
	from qpr_instances
	where instance_id = p_instance_id;

Line: 75

	select count(*) into l_check_instance
	from qpr_dim_sources
	where instance_id = p_instance_id;

Line: 113

	select hsecs into l_end_time from v$timer;

Line: 125

procedure delete_duplicate_data (p_instance_id in number)
is
Begin
	delete from qpr_dimension_values a
	where a.instance_id = p_instance_id
	and a.rowid >
	ANY
	(select b.rowid
	from qpr_dimension_values b
	where a.dim_code = b.dim_code
	and a.hierarchy_code = b.hierarchy_code
	and a.instance_id = b.instance_id
	and a.level1_value = b.level1_value);

Line: 170

procedure delete_arrays(l_ctr number) is
begin
if l_ctr is null then
        c_dim_data_rec.level1_value.delete;

Line: 174

        c_dim_data_rec.level1_desc.delete;

Line: 175

        c_dim_data_rec.level1_attribute1.delete;

Line: 176

        c_dim_data_rec.level1_attribute2.delete;

Line: 177

        c_dim_data_rec.level1_attribute3.delete;

Line: 178

        c_dim_data_rec.level1_attribute4.delete;

Line: 179

        c_dim_data_rec.level1_attribute5.delete;

Line: 180

        c_dim_data_rec.level2_value.delete;

Line: 181

        c_dim_data_rec.level2_desc.delete;

Line: 182

        c_dim_data_rec.level2_attribute1.delete;

Line: 183

        c_dim_data_rec.level2_attribute2.delete;

Line: 184

        c_dim_data_rec.level2_attribute3.delete;

Line: 185

        c_dim_data_rec.level2_attribute4.delete;

Line: 186

        c_dim_data_rec.level2_attribute5.delete;

Line: 187

        c_dim_data_rec.level3_value.delete;

Line: 188

        c_dim_data_rec.level3_desc.delete;

Line: 189

        c_dim_data_rec.level3_attribute1.delete;

Line: 190

        c_dim_data_rec.level3_attribute2.delete;

Line: 191

        c_dim_data_rec.level3_attribute3.delete;

Line: 192

        c_dim_data_rec.level3_attribute4.delete;

Line: 193

        c_dim_data_rec.level3_attribute5.delete;

Line: 194

        c_dim_data_rec.level4_value.delete;

Line: 195

        c_dim_data_rec.level4_desc.delete;

Line: 196

        c_dim_data_rec.level4_attribute1.delete;

Line: 197

        c_dim_data_rec.level4_attribute2.delete;

Line: 198

        c_dim_data_rec.level4_attribute3.delete;

Line: 199

        c_dim_data_rec.level4_attribute4.delete;

Line: 200

        c_dim_data_rec.level4_attribute5.delete;

Line: 201

        c_dim_data_rec.level5_value.delete;

Line: 202

        c_dim_data_rec.level5_desc.delete;

Line: 203

        c_dim_data_rec.level5_attribute1.delete;

Line: 204

        c_dim_data_rec.level5_attribute2.delete;

Line: 205

        c_dim_data_rec.level5_attribute3.delete;

Line: 206

        c_dim_data_rec.level5_attribute4.delete;

Line: 207

        c_dim_data_rec.level5_attribute5.delete;

Line: 208

        c_dim_data_rec.level6_value.delete;

Line: 209

        c_dim_data_rec.level6_desc.delete;

Line: 210

        c_dim_data_rec.level6_attribute1.delete;

Line: 211

        c_dim_data_rec.level6_attribute2.delete;

Line: 212

        c_dim_data_rec.level6_attribute3.delete;

Line: 213

        c_dim_data_rec.level6_attribute4.delete;

Line: 214

        c_dim_data_rec.level6_attribute5.delete;

Line: 215

        c_dim_data_rec.level7_value.delete;

Line: 216

        c_dim_data_rec.level7_desc.delete;

Line: 217

        c_dim_data_rec.level7_attribute1.delete;

Line: 218

        c_dim_data_rec.level7_attribute2.delete;

Line: 219

        c_dim_data_rec.level7_attribute3.delete;

Line: 220

        c_dim_data_rec.level7_attribute4.delete;

Line: 221

        c_dim_data_rec.level7_attribute5.delete;

Line: 222

        c_dim_data_rec.level8_value.delete;

Line: 223

        c_dim_data_rec.level8_desc.delete;

Line: 224

        c_dim_data_rec.level8_attribute1.delete;

Line: 225

        c_dim_data_rec.level8_attribute2.delete;

Line: 226

        c_dim_data_rec.level8_attribute3.delete;

Line: 227

        c_dim_data_rec.level8_attribute4.delete;

Line: 228

        c_dim_data_rec.level8_attribute5.delete;

Line: 229

        c_dim_data_rec.check_date.delete;

Line: 231

        c_dim_data_rec.level1_value.delete(l_ctr);

Line: 232

        c_dim_data_rec.level1_desc.delete(l_ctr);

Line: 233

        c_dim_data_rec.level1_attribute1.delete(l_ctr);

Line: 234

        c_dim_data_rec.level1_attribute2.delete(l_ctr);

Line: 235

        c_dim_data_rec.level1_attribute3.delete(l_ctr);

Line: 236

        c_dim_data_rec.level1_attribute4.delete(l_ctr);

Line: 237

        c_dim_data_rec.level1_attribute5.delete(l_ctr);

Line: 238

        c_dim_data_rec.level2_value.delete(l_ctr);

Line: 239

        c_dim_data_rec.level2_desc.delete(l_ctr);

Line: 240

        c_dim_data_rec.level2_attribute1.delete(l_ctr);

Line: 241

        c_dim_data_rec.level2_attribute2.delete(l_ctr);

Line: 242

        c_dim_data_rec.level2_attribute3.delete(l_ctr);

Line: 243

        c_dim_data_rec.level2_attribute4.delete(l_ctr);

Line: 244

        c_dim_data_rec.level2_attribute5.delete(l_ctr);

Line: 245

        c_dim_data_rec.level3_value.delete(l_ctr);

Line: 246

        c_dim_data_rec.level3_desc.delete(l_ctr);

Line: 247

        c_dim_data_rec.level3_attribute1.delete(l_ctr);

Line: 248

        c_dim_data_rec.level3_attribute2.delete(l_ctr);

Line: 249

        c_dim_data_rec.level3_attribute3.delete(l_ctr);

Line: 250

        c_dim_data_rec.level3_attribute4.delete(l_ctr);

Line: 251

        c_dim_data_rec.level3_attribute5.delete(l_ctr);

Line: 252

        c_dim_data_rec.level4_value.delete(l_ctr);

Line: 253

        c_dim_data_rec.level4_desc.delete(l_ctr);

Line: 254

        c_dim_data_rec.level4_attribute1.delete(l_ctr);

Line: 255

        c_dim_data_rec.level4_attribute2.delete(l_ctr);

Line: 256

        c_dim_data_rec.level4_attribute3.delete(l_ctr);

Line: 257

        c_dim_data_rec.level4_attribute4.delete(l_ctr);

Line: 258

        c_dim_data_rec.level4_attribute5.delete(l_ctr);

Line: 259

        c_dim_data_rec.level5_value.delete(l_ctr);

Line: 260

        c_dim_data_rec.level5_desc.delete(l_ctr);

Line: 261

        c_dim_data_rec.level5_attribute1.delete(l_ctr);

Line: 262

        c_dim_data_rec.level5_attribute2.delete(l_ctr);

Line: 263

        c_dim_data_rec.level5_attribute3.delete(l_ctr);

Line: 264

        c_dim_data_rec.level5_attribute4.delete(l_ctr);

Line: 265

        c_dim_data_rec.level5_attribute5.delete(l_ctr);

Line: 266

        c_dim_data_rec.level6_value.delete(l_ctr);

Line: 267

        c_dim_data_rec.level6_desc.delete(l_ctr);

Line: 268

        c_dim_data_rec.level6_attribute1.delete(l_ctr);

Line: 269

        c_dim_data_rec.level6_attribute2.delete(l_ctr);

Line: 270

        c_dim_data_rec.level6_attribute3.delete(l_ctr);

Line: 271

        c_dim_data_rec.level6_attribute4.delete(l_ctr);

Line: 272

        c_dim_data_rec.level6_attribute5.delete(l_ctr);

Line: 273

        c_dim_data_rec.level7_value.delete(l_ctr);

Line: 274

        c_dim_data_rec.level7_desc.delete(l_ctr);

Line: 275

        c_dim_data_rec.level7_attribute1.delete(l_ctr);

Line: 276

        c_dim_data_rec.level7_attribute2.delete(l_ctr);

Line: 277

        c_dim_data_rec.level7_attribute3.delete(l_ctr);

Line: 278

        c_dim_data_rec.level7_attribute4.delete(l_ctr);

Line: 279

        c_dim_data_rec.level7_attribute5.delete(l_ctr);

Line: 280

        c_dim_data_rec.level8_value.delete(l_ctr);

Line: 281

        c_dim_data_rec.level8_desc.delete(l_ctr);

Line: 282

        c_dim_data_rec.level8_attribute1.delete(l_ctr);

Line: 283

        c_dim_data_rec.level8_attribute2.delete(l_ctr);

Line: 284

        c_dim_data_rec.level8_attribute3.delete(l_ctr);

Line: 285

        c_dim_data_rec.level8_attribute4.delete(l_ctr);

Line: 286

        c_dim_data_rec.level8_attribute5.delete(l_ctr);

Line: 287

        c_dim_data_rec.check_date.delete(l_ctr);

Line: 289

end delete_arrays;

Line: 297

	l_sql := 'select ';

Line: 303

		select count(nvl(user_value_column, value_column)) into l_level_defined
		from qpr_dim_sources
		where instance_id = l_dim_src_instance_id
		and instance_type = l_inst_type
		and dim_code = p_dim_code
		and hier_code = p_hier_code
		and nvl(user_level_number, level_number) = l_count;

Line: 314

			select count(nvl(user_attr_column, attr_column)) into l_attr_defined
			from qpr_dim_sources
			where instance_id = l_dim_src_instance_id
			and instance_type = l_inst_type
			and dim_code = p_dim_code
			and hier_code = p_hier_code
			and nvl(user_level_number, level_number) = l_count;

Line: 327

					select
					nvl(user_value_column, value_column),
					nvl(user_desc_column, desc_column),
					nvl(user_attr_column, attr_column)
					into
					l_value_column,
					l_desc_column,
					l_attr_column
					from qpr_dim_sources
					where instance_id = l_dim_src_instance_id
					and instance_type = l_inst_type
					and dim_code = p_dim_code
					and hier_code = p_hier_code
					and nvl(user_level_number, level_number) = l_count
					and nvl(user_attr_number, attr_number) = l_attr_num;

Line: 377

				select
				nvl(user_value_column, value_column),
				nvl(user_desc_column, desc_column)
				into
				l_value_column,
				l_desc_column
				from qpr_dim_sources
				where instance_id = l_dim_src_instance_id
				and instance_type = l_inst_type
				and dim_code = p_dim_code
				and hier_code = p_hier_code
				and nvl(user_level_number, level_number) = l_count;

Line: 439

		select
		nvl(user_value_column, value_column)
		into
		l_value_column
		from qpr_dim_sources
		where instance_id = l_dim_src_instance_id
		and instance_type = l_inst_type
		and dim_code = p_dim_code
		and hier_code = p_hier_code
		and nvl(user_level_number, level_number) = 999;

Line: 458

	select distinct nvl(user_view_name, view_name)
	into l_source_view_name
	from qpr_dim_sources
	where instance_id = l_dim_src_instance_id
	and instance_type = l_inst_type
	and dim_code = p_dim_code
	and hier_code = p_hier_code;

Line: 476

		select nvl(user_value_column, value_column)
		into l_date_column
		from qpr_dim_sources
		where instance_id = l_dim_src_instance_id
		and instance_type = l_inst_type
		and dim_code = p_dim_code
		and hier_code = p_hier_code
		and nvl(user_level_number, level_number) = 998
                and rownum < 2;

Line: 500

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

Line: 501

                delete_arrays(null);

Line: 573

                                delete_arrays(i);

Line: 579

                insert_dimension_data(p_instance_id, p_dim_code, p_hier_code, c_dim_data_rec);

Line: 587

procedure insert_dimension_data(
	p_instance_id in number,
	p_dim_code in varchar2,
	p_hier_code in varchar2,
	c_dim_data_rec in out nocopy QPR_LOAD_DIM_DATA.DIM_DATA_REC_TYPE)
is

l_request_id number;

Line: 605

		delete from QPR_DIMENSION_VALUES
		where dim_code = p_dim_code
		and hierarchy_code = p_hier_code
		and instance_id = p_instance_id
		and level1_value = c_dim_data_rec.level1_value(I);

Line: 612

				'Deleted record count:' || sql%rowcount);

Line: 614

	fnd_file.put_line(fnd_file.log,'Inserting dimension data for: '||p_dim_code||'_'||p_hier_code);

Line: 617

		INSERT INTO QPR_DIMENSION_VALUES
		(dim_value_id,
		instance_id,
		dim_code,
		hierarchy_code,
		level1_value,
		level1_desc,
		level1_attribute1,
		level1_attribute2,
		level1_attribute3,
		level1_attribute4,
		level1_attribute5,
		level2_value,
		level2_desc,
		level2_attribute1,
		level2_attribute2,
		level2_attribute3,
		level2_attribute4,
		level2_attribute5,
		level3_value,
		level3_desc,
		level3_attribute1,
		level3_attribute2,
		level3_attribute3,
		level3_attribute4,
		level3_attribute5,
		level4_value,
		level4_desc,
		level4_attribute1,
		level4_attribute2,
		level4_attribute3,
		level4_attribute4,
		level4_attribute5,
		level5_value,
		level5_desc,
		level5_attribute1,
		level5_attribute2,
		level5_attribute3,
		level5_attribute4,
		level5_attribute5,
		level6_value,
		level6_desc,
		level6_attribute1,
		level6_attribute2,
		level6_attribute3,
		level6_attribute4,
		level6_attribute5,
		level7_value,
		level7_desc,
		level7_attribute1,
		level7_attribute2,
		level7_attribute3,
		level7_attribute4,
		level7_attribute5,
		level8_value,
		level8_desc,
		level8_attribute1,
		level8_attribute2,
		level8_attribute3,
		level8_attribute4,
		level8_attribute5,
		check_date,
		creation_date,
		created_by,
		last_update_date,
		last_updated_by,
		last_update_login,
		program_application_id,
		program_id,
		program_login_id,
		request_id)
		values
		(QPR_DIMENSION_VALUES_S.nextval,
		p_instance_id,
		p_dim_code,
		p_hier_code,
		c_dim_data_rec.level1_value(I),
		c_dim_data_rec.level1_desc(I),
		c_dim_data_rec.level1_attribute1(I),
		c_dim_data_rec.level1_attribute2(I),
		c_dim_data_rec.level1_attribute3(I),
		c_dim_data_rec.level1_attribute4(I),
		c_dim_data_rec.level1_attribute5(I),
		c_dim_data_rec.level2_value(I),
		c_dim_data_rec.level2_desc(I),
		c_dim_data_rec.level2_attribute1(I),
		c_dim_data_rec.level2_attribute2(I),
		c_dim_data_rec.level2_attribute3(I),
		c_dim_data_rec.level2_attribute4(I),
		c_dim_data_rec.level2_attribute5(I),
		c_dim_data_rec.level3_value(I),
		c_dim_data_rec.level3_desc(I),
		c_dim_data_rec.level3_attribute1(I),
		c_dim_data_rec.level3_attribute2(I),
		c_dim_data_rec.level3_attribute3(I),
		c_dim_data_rec.level3_attribute4(I),
		c_dim_data_rec.level3_attribute5(I),
		c_dim_data_rec.level4_value(I),
		c_dim_data_rec.level4_desc(I),
		c_dim_data_rec.level4_attribute1(I),
		c_dim_data_rec.level4_attribute2(I),
		c_dim_data_rec.level4_attribute3(I),
		c_dim_data_rec.level4_attribute4(I),
		c_dim_data_rec.level4_attribute5(I),
		c_dim_data_rec.level5_value(I),
		c_dim_data_rec.level5_desc(I),
		c_dim_data_rec.level5_attribute1(I),
		c_dim_data_rec.level5_attribute2(I),
		c_dim_data_rec.level5_attribute3(I),
		c_dim_data_rec.level5_attribute4(I),
		c_dim_data_rec.level5_attribute5(I),
		c_dim_data_rec.level6_value(I),
		c_dim_data_rec.level6_desc(I),
		c_dim_data_rec.level6_attribute1(I),
		c_dim_data_rec.level6_attribute2(I),
		c_dim_data_rec.level6_attribute3(I),
		c_dim_data_rec.level6_attribute4(I),
		c_dim_data_rec.level6_attribute5(I),
		c_dim_data_rec.level7_value(I),
		c_dim_data_rec.level7_desc(I),
		c_dim_data_rec.level7_attribute1(I),
		c_dim_data_rec.level7_attribute2(I),
		c_dim_data_rec.level7_attribute3(I),
		c_dim_data_rec.level7_attribute4(I),
		c_dim_data_rec.level7_attribute5(I),
		c_dim_data_rec.level8_value(I),
		c_dim_data_rec.level8_desc(I),
		c_dim_data_rec.level8_attribute1(I),
		c_dim_data_rec.level8_attribute2(I),
		c_dim_data_rec.level8_attribute3(I),
		c_dim_data_rec.level8_attribute4(I),
		c_dim_data_rec.level8_attribute5(I),
		c_dim_data_rec.check_date(I),
		sysdate,
		fnd_global.user_id,
		sysdate,
		fnd_global.user_id,
		fnd_global.conc_login_id,
		fnd_global.prog_appl_id,
		fnd_global.conc_program_id,
		null,
		l_request_id);

Line: 765

       fnd_file.put_line(fnd_file.log, 'UNEXCPECTED ERROR IN INSERT_DIMENSION_DATA:'||sqlerrm);

Line: 768

       fnd_file.put_line(fnd_file.log, 'UNEXCPECTED ERROR IN INSERT_DIMENSION_DATA:'||sqlerrm);