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.
PACKAGE BODY: APPS.QPR_TRANSFORMATION

Source


1 PACKAGE BODY QPR_TRANSFORMATION AS
2 /* $Header: QPRUTRNB.pls 120.1 2008/01/10 10:00:03 kdhabali noship $ */
3 
4 FUNCTION get_null return varchar2 is
5 begin
6 	return '*';
7 end;
8 
9 FUNCTION get_y return varchar2 is
10 begin
11 	return 'Y';
12 end;
13 
14 FUNCTION get_n return varchar2 is
15 begin
16 	return 'N';
17 end;
18 
19 function get_num(p_char varchar2) return number is
20 begin
21 	return(to_number(p_char));
22 exception
23 	when others then
24 		return(null);
25 end;
26 
27 procedure transform_dimdim_process(
28                         errbuf              OUT nocopy VARCHAR2,
29                         retcode             OUT nocopy VARCHAR2,
30                         p_transf_group_id     IN  NUMBER,
31                         p_instance_id     IN  NUMBER
32                         );
33 
34 procedure transform_measdim_process(
35                         errbuf              OUT nocopy VARCHAR2,
36                         retcode             OUT nocopy VARCHAR2,
37                         p_transf_group_id     IN  NUMBER,
38                         p_instance_id     IN  NUMBER,
39                         p_from_date in date,
40                         p_to_date in date);
41 
42 procedure transform_process(
43                         errbuf              OUT  nocopy VARCHAR2,
44                         retcode             OUT nocopy VARCHAR2,
45                         p_transf_group_id     IN  NUMBER,
46                         p_instance_id     IN  NUMBER,
47                         p_from_date in varchar2 default null,
48                         p_to_date in varchar2 default null) is
49 l_transf_type_code varchar2(10);
50 date_from date;
51 date_to date;
52 
53 begin
54 	select transf_type_code into l_transf_type_code
55 	from qpr_transf_groups_b
56 	where transf_group_id = p_transf_group_id;
57 	fnd_file.put_line(fnd_file.log, 'Transf type code:' || l_transf_type_code);
58 	if nvl(l_transf_type_code, '*') = 'DIMDIM' then
59 
60 		transform_dimdim_process(errbuf, retcode, p_transf_group_id,p_instance_id);
61 
62 	elsif nvl(l_transf_type_code, '*') = 'MEASDIM' then
63 
64                 date_from := fnd_date.canonical_to_date(p_from_date);
65                 date_to := fnd_date.canonical_to_date(p_to_date);
66 		transform_measdim_process(errbuf, retcode, p_transf_group_id,
67                                           p_instance_id,date_from, date_to);
68 
69 	end if;
70 
71 EXCEPTION
72      when others then
73 		errbuf := substr(SQLERRM,1,150);
74 		retcode := -1;
75 	fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
76 end;
77 
78 
79 procedure transform_measdim_process(
80                         errbuf              OUT nocopy VARCHAR2,
81                         retcode             OUT nocopy VARCHAR2,
82                         p_transf_group_id     IN  NUMBER,
83                         p_instance_id     IN  NUMBER,
84                         p_from_date in date,
85                         p_to_date in date) is
86 
87 CURSOR GET_TRANSF_HEADER (p_transf_group_id number) is
88 select TRANSF_HEADER_ID,
89 	FROM_DIM_MEAS_CODE,
90 	MEAS_CODE,
91 	LIMIT_DIM_FLAG,
92 	TO_DIM_CODE,
93 	TO_LEVEL_ID,
94 	TO_VALUE,
95 	TO_VALUE_DESC
96 from qpr_transf_headers_b
97 where TRANSF_GROUP_ID=p_transf_group_id
98 and from_dim_meas_code is not null
99 and meas_code is not null
100 and to_dim_code is not null
101 and to_level_id is not null
102 and to_value is not null ;
103 
104 CURSOR GET_MEASURE (p_transf_header_id number,
105 p_from_dim_meas_code varchar2,
106 p_meas_type_code varchar2,
107 p_instance_id number, p_from_date date, p_to_date date) is
108 select csd.MEASURE_VALUE_ID,
109 csd.INSTANCE_ID,
110 csd.PRD_LEVEL_VALUE,
111 csd.ORD_LEVEL_VALUE,
112 csd.MEASURE4_NUMBER,
113 qtr.LEVEL_VALUE_FROM,
114 qtr.LEVEL_VALUE_TO,
115 qtr.LIMIT_DIM_CODE,
116 qtr.LIMIT_DIM_LEVEL,
117 qtr.LIMIT_DIM_LEVEL_VALUE
118 from qpr_measure_data csd, qpr_transf_rules_b qtr
119 where csd.measure_type_code = p_from_dim_meas_code and
120 csd.instance_id=p_instance_id and
121 csd.time_level_value between p_from_date and p_to_date
122 and qtr.transf_header_id = p_transf_header_id and
123 ((p_meas_type_code = '1' and csd.measure1_number between
124   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure1_number)
125 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure1_number)) or
126 (p_meas_type_code = '2' and csd.measure2_number between
127   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure2_number)
128 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure2_number)) or
129 (p_meas_type_code = '3' and csd.measure3_number between
130   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure3_number)
131 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure3_number)) or
132 (p_meas_type_code = '4' and csd.measure4_number between
133   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure4_number)
134 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure4_number)) or
135 (p_meas_type_code = '5' and csd.measure5_number between
136   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure5_number)
137 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure5_number)) or
138 (p_meas_type_code = '6' and csd.measure6_number between
139   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure6_number)
140 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure6_number)) or
141 (p_meas_type_code = '7' and csd.measure7_number between
142   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure7_number)
143 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure7_number)) or
144 (p_meas_type_code = '8' and csd.measure8_number between
145   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure8_number)
146 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure8_number)) or
147 (p_meas_type_code = '9' and csd.measure9_number between
148   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure9_number)
149 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure9_number)) or
150 (p_meas_type_code = '10' and csd.measure10_number between
151   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure10_number)
152 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure10_number)) or
153 (p_meas_type_code = '11' and csd.measure11_number between
154   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure11_number)
155 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure11_number)) or
156 (p_meas_type_code = '12' and csd.measure12_number between
157   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure12_number)
158 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure12_number)) or
159 (p_meas_type_code = '13' and csd.measure13_number between
160   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure13_number)
161 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure13_number)) or
162 (p_meas_type_code = '14' and csd.measure14_number between
163   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure14_number)
164 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure14_number)) or
165 (p_meas_type_code = '15' and csd.measure14_number between
166   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure15_number)
167 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure15_number)) or
168 (p_meas_type_code = '16' and csd.measure16_number between
169   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure16_number)
170 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure16_number)) or
171 (p_meas_type_code = '17' and csd.measure17_number between
172   nvl(qpr_transformation.get_num(qtr.level_value_from), csd.measure17_number)
173 	and nvl(qpr_transformation.get_num(qtr.level_value_to),csd.measure17_number)));
174 
175 l_insert_measure number;
176 l_next_seq number;
177 l_dummy number;
178 l_all_desc varchar2(240);
179 l_all_value varchar2(240);
180 begin
181 fnd_file.put_line(fnd_file.log, 'Inside procedure transform_measdim_process');
182 
183 for transf_header_rec in get_transf_header(p_transf_group_id) loop
184 
185    fnd_file.put_line(fnd_file.log, 'Transf Header ID: '||
186 		transf_header_rec.TRANSF_HEADER_ID);
187 
188    for measure_rec in get_measure(transf_header_rec.transf_header_id,
189 					transf_header_rec.from_dim_meas_code,
190 					transf_header_rec.meas_code,
191                                       p_instance_id, p_from_date,p_to_date) loop
192    	fnd_file.put_line(fnd_file.log, 'Order Line: '||
193 		measure_rec.ord_level_value);
194 	l_insert_measure:=0;
195 	fnd_file.put_line(fnd_file.log, 'Limit Dim flag: '||
196 		transf_header_rec.limit_dim_flag);
197 	if nvl(transf_header_rec.limit_dim_flag,'N') = 'Y' then
198 	fnd_file.put_line(fnd_file.log, 'Limit Dim code: '||
199 		measure_rec.limit_dim_code);
200 		if measure_rec.limit_dim_code='PRD' then
201 	-- Only Product dimension supported now.
202 	fnd_file.put_line(fnd_file.log, 'Limit Dim level: '||
203 		measure_rec.limit_dim_level);
204 		if measure_rec.limit_dim_level = 'ITEM' then
205 	fnd_file.put_line(fnd_file.log, 'Limit Dim level value: '||
206 		measure_rec.limit_dim_level_value);
207 	fnd_file.put_line(fnd_file.log, 'Limit Dim measure value: '||
208 		measure_rec.prd_level_value);
209 			if measure_rec.prd_level_value = measure_rec.limit_dim_level_value then
210 				l_insert_measure:=1;
211 				fnd_file.put_line(fnd_file.log, 'Insert needed');
212 			end if;
213 		else
214 			l_insert_measure:=0;
215 			begin
216 			if measure_rec.limit_dim_level = 'PRODUCT_CATEGORY' then
217 				select 1 into l_insert_measure
218 				from qpr_dimension_values
219 				where  dim_code='PRD'
220 				and hierarchy_code='PRODUCTCATEGORY'
221 				and instance_id = p_instance_id and
222 				level2_value = measure_rec.limit_dim_level_value;
223 			elsif measure_rec.limit_dim_level = 'PRODUCT_FAMILY' then
224 				select 1 into l_insert_measure
225 				from qpr_dimension_values
226 				where  dim_code='PRD'
227 				and hierarchy_code='PRODUCTFAMILY'
228 				and instance_id = p_instance_id and
229 				level2_value = measure_rec.limit_dim_level_value;
230 			elsif measure_rec.limit_dim_level = 'ITEM' then
231 				select 1 into l_insert_measure
232 				from qpr_dimension_values
233 				where  dim_code='PRD'
234 				and hierarchy_code='PRODUCTCATEGORY'
235 				and instance_id = p_instance_id and
236 				level1_value = measure_rec.limit_dim_level_value;
237 			end if;
238 			exception
239 			when others then null;
240 			end;
241 		end if;
242 		end if;
243 	else
244 		l_insert_measure:=1;
245 	end if;
246 	if l_insert_measure = 1 then
247 		begin
248 			select 1 into l_dummy
249 			from qpr_dimension_values
250 			where dim_code = transf_header_rec.to_dim_code
251 			and hierarchy_code = nvl(decode(transf_header_rec.to_dim_code,
252 				'DSB', 'DISC_BAND',
253 				'VLB', 'VOL_BAND',
254 				'MGB', 'MRGBAND',  null), hierarchy_code)
255 			and level1_value = transf_header_rec.to_value
256 			and instance_id = p_instance_id
257 			and rownum<2;
258 			if l_dummy = 1 then
259 				fnd_file.put_line(fnd_file.log, 'Band exists in Band Dim with value:'||
260 					transf_header_rec.to_value);
261 			end if;
262 		exception
263 		     WHEN NO_DATA_FOUND THEN
264 			begin
265 			  select qpr_dimension_values_s.nextval
266 				into l_next_seq from dual ;
267 			 if transf_header_rec.to_dim_code = 'DSB' then
268 				l_all_desc := qpr_sr_util.get_all_dsb_desc;
269 				l_all_value := qpr_sr_util.get_all_dsb_pk;
270 			 elsif transf_header_rec.to_dim_code = 'VLB' then
271 				l_all_desc := qpr_sr_util.get_all_vlb_desc;
272 				l_all_value := qpr_sr_util.get_all_vlb_pk;
273 			 elsif transf_header_rec.to_dim_code = 'MGB' then
274 				l_all_desc := qpr_sr_util.get_all_mgb_desc;
275 				l_all_value := qpr_sr_util.get_all_mgb_pk;
276 			 end if;
277 			 fnd_file.put_line(fnd_file.log,
278 			'Inserting band dim with id :'||l_next_seq);
279 			 INSERT INTO qpr_dimension_values(instance_id,
280 					dim_value_id,
281 					dim_code,
282 					hierarchy_code,
283 					level1_value,
284 					level1_desc,
285 					level2_value,
286 					level2_desc,
287 					CREATION_DATE,
288 					CREATED_BY,
289 					LAST_UPDATE_DATE,
290 					LAST_UPDATED_BY,
291 					LAST_UPDATE_LOGIN,
292 					REQUEST_ID) values
293 					(measure_rec.instance_id,
294 					l_next_seq,
295 					transf_header_rec.to_dim_code,
296 					decode(transf_header_rec.to_dim_code,
297 						'DSB', 'DISC_BAND',
298 						'VLB', 'VOL_BAND',
299 						'MGB', 'MRGBAND',  null),
300 					transf_header_rec.TO_VALUE,
301 					transf_header_rec.TO_VALUE_DESC,
302 					l_all_value,
303 					l_all_desc,
304 					sysdate,
305 					FND_GLOBAL.USER_ID,
306 					sysdate,
307 					FND_GLOBAL.USER_ID,
308 					FND_GLOBAL.LOGIN_ID,
309 					null);
310 			exception
311 			     when others then
312 					errbuf := substr(SQLERRM,1,150);
313 					retcode := -1;
314 				fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
315 			end;
316 		end;
317 		begin
318 			if transf_header_rec.to_dim_code = 'DSB' then
319 				update qpr_measure_data
320 				set dsb_level_value = transf_header_rec.to_value,
321 				measure7_number =
322 				qpr_transformation.get_num(measure_rec.level_value_to),
323 				measure8_number =
324 				qpr_transformation.get_num(measure_rec.level_value_from),
325 				last_update_date = sysdate,
326 				last_updated_by = fnd_global.user_id,
327 				last_update_login = fnd_global.login_id
328 				where measure_value_id = measure_rec.measure_value_id;
329 			elsif transf_header_rec.to_dim_code = 'VLB' then
330 				update qpr_measure_data
331 				set vlb_level_value = transf_header_rec.to_value,
332 				measure11_number =
333 				qpr_transformation.get_num(measure_rec.level_value_to),
334 				measure12_number =
335 				qpr_transformation.get_num(measure_rec.level_value_from),
336 				measure9_number = measure_rec.measure4_number,
337 				measure10_number = measure_rec.measure4_number,
338 				last_update_date = sysdate,
339 				last_updated_by = fnd_global.user_id,
340 				last_update_login = fnd_global.login_id
341 				where measure_value_id = measure_rec.measure_value_id;
342 			elsif transf_header_rec.to_dim_code = 'MGB' then
343 				update qpr_measure_data
344 				set mgb_level_value = transf_header_rec.to_value,
345 				measure18_number =
346 				qpr_transformation.get_num(measure_rec.level_value_to),
347 				measure19_number =
348 				qpr_transformation.get_num(measure_rec.level_value_from),
349 				last_update_date = sysdate,
350 				last_updated_by = fnd_global.user_id,
351 				last_update_login = fnd_global.login_id
352 				where measure_value_id =
353 					measure_rec.measure_value_id;
354 			end if;
355 			fnd_file.put_line(fnd_file.log, 'Updated '||
356 				sql%rowcount ||' records');
357 		exception
358 		WHEN NO_DATA_FOUND THEN
359 				errbuf := substr(SQLERRM,1,150);
360 				retcode := -1;
361 			fnd_file.put_line(fnd_file.log,
362 				substr(SQLERRM, 1, 1000));
363 		end;
364 	end if;
365    end loop;
366 end loop;
367 EXCEPTION
368      when others then
369 		errbuf := substr(SQLERRM,1,150);
370 		retcode := -1;
371 	fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
372 end;
373 
374 function get_attribute_qstring(lvl_no number, attr_no number) return varchar2
375 is
376 sql1 varchar2(1000);
377 begin
378    sql1 :=
379 	'and '||
380 	'('||
381 	  '(b.attribute'||lvl_no||'_from is null and b.attribute'||lvl_no||'_to is null) '||
382 	 'or ('||
383 	   'nvl(b.attribute'||lvl_no||'_number_flag,qpr_transformation.get_n) '||
384 	   '= qpr_transformation.get_y  ' ||
385 	   'and nvl(qpr_transformation.get_num(a.level' ||
386 		attr_no || '_attribute'||lvl_no||'),0)'||
387 	   ' between ' ||
388 	     'nvl(qpr_transformation.get_num(b.attribute'||lvl_no||'_from), '||
389 		'nvl(qpr_transformation.get_num(a.level' ||
390 			attr_no || '_attribute'||lvl_no||'),0))  '||
391 	   'and'||
392 	     ' nvl(qpr_transformation.get_num(b.attribute'||lvl_no||'_to), '||
393 		'nvl(qpr_transformation.get_num(a.level' ||
394 			attr_no || '_attribute'||lvl_no||'),0)) '||
395 	'or '||
396 	   'nvl(b.attribute'||lvl_no||'_number_flag,qpr_transformation.get_n) '||
397 	   '= qpr_transformation.get_n  ' ||
398 	   'and nvl(a.level' || attr_no || '_attribute'||lvl_no||
399 		',qpr_transformation.get_null)'||
400 	   ' between  ' ||
401 	     'nvl(b.attribute'||lvl_no||'_from, nvl(a.level' || attr_no ||
402 		  '_attribute'||lvl_no||',qpr_transformation.get_null))  ' ||
403 	   'and '||
404 	     'nvl(b.attribute'||lvl_no||'_to, nvl(a.level' || attr_no ||
405 		  '_attribute'||lvl_no||',qpr_transformation.get_null))'||
406 	 ') ' ||
407 	') ' ;
408    return(sql1);
409 end;
410 
411 procedure transform_dimdim_process(
412                         errbuf              OUT nocopy VARCHAR2,
413                         retcode             OUT nocopy VARCHAR2,
414                         p_transf_group_id     IN  NUMBER,
415                         p_instance_id     IN  NUMBER) is
416 
417 l_level_pk number;
418 l_created_by_refresh_num number;
419 l_last_refresh_num number;
420 l_first number;
421 l_exist number;
422 sql1_text varchar2(5000);
423 
424 CURSOR GET_TRANSF_HEADER (p_transf_group_id number) is
425 select TRANSF_HEADER_ID,
426 	FROM_DIM_MEAS_CODE,
427 	FROM_DIM_HIER_CODE,
428 	LIMIT_DIM_FLAG,
429 	FROM_LEVEL_ID,
430 	TO_LEVEL_ID,
431 	TO_VALUE,
432 	TO_VALUE_DESC
433 from qpr_transf_headers_b
434 where TRANSF_GROUP_ID=p_transf_group_id;
435 
436 l_rows natural :=1000;
437 c_dim_data QPRTRANS;
438 c_dim_rec DIM_REC;
439 cl number;
440 
441 BEGIN
442 
443 for transf_header_rec in get_transf_header(p_transf_group_id) loop
444 
445 	fnd_file.put_line(fnd_file.log, 'Transf Header ID: '||
446 		transf_header_rec.TRANSF_HEADER_ID);
447 
448 	l_first:=0;
449 	sql1_text := ' select a.dim_value_id as dim_value_id ' ||
450 		    ' from qpr_dimension_values a, qpr_transf_rules_b b ';
451 	sql1_text := sql1_text|| ' where a.dim_code = '|| '''' ||
452 		replace(transf_header_rec.from_dim_meas_code, '''', '''''') || '''';
453 	sql1_text := sql1_text|| ' and a.hierarchy_code = '|| '''' ||
454 		replace(transf_header_rec.from_dim_hier_code, '''', '''''') || '''';
455 	sql1_text := sql1_text|| ' and b.transf_header_id = '||transf_header_rec.transf_header_id;
456 	sql1_text := sql1_text|| ' and a.instance_id = '||p_instance_id;
457 	cl := transf_header_rec.from_level_id;
458 	if cl>5 then
459 		fnd_file.put_line(fnd_file.log, 'Error in setup');
460 		exit;
461 	end if;
462         sql1_text := sql1_text ||
463 	' and '||
464 	'('||
465 	   'b.level_value_from is null '||
466 	   'or  ' ||
467 	   '('||
468 		'nvl(b.level_value_number_flag , qpr_transformation.get_n) '||
469 		'= qpr_transformation.get_n and ' ||
470 		'((b.level_value_like_flag =  qpr_transformation.get_y and '||
471 		'a.level' || cl || '_value like b.level_value_from) ' ||
472 		'or (b.level_value_like_flag = qpr_transformation.get_n '||
473 		'and a.level' || cl || '_value >= b.level_value_from ' ||
474 		'and (b.level_value_to is null or ' ||
475 		'a.level'||cl||'_value <=  b.level_value_to)))'||
476    	   ')'||
477 	   ' or ' ||
478 	   '('||
479 		'nvl(b.level_value_number_flag , qpr_transformation.get_n) '||
480 		'= qpr_transformation.get_y and '||
481 		'qpr_transformation.get_num(a.level' || cl || '_value) >= '||
482 		'qpr_transformation.get_num(b.level_value_from ) and ' ||
483 		'(qpr_transformation.get_num(b.level_value_to) is null or '||
484 		'qpr_transformation.get_num(a.level' || cl || '_value) <= '||
485 		'qpr_transformation.get_num(b.level_value_to))'||
486 	   ')'||
487 	')';
488         sql1_text := sql1_text ||
489 	'and '||
490 	'('||
491 	  'b.level_desc_from is null '||
492 	  'or ' ||
493 	  '('||
494 	   'b.level_value_like_flag = qpr_transformation.get_y '||
495 	   'and a.level' || cl || '_desc like b.level_desc_from'||
496 	  ') '||
497 	  ' or '||
498 	  '('||
499 	    'b.level_value_like_flag=qpr_transformation.get_n and '||
500 	    'a.level' || cl || '_desc >= b.level_desc_from and ' ||
501 		'(b.level_desc_to is null or '||
502 	    'a.level' || cl || '_desc <= b.level_desc_to)'||
503 	  ')'||
504 	')  ';
505         sql1_text := sql1_text || get_attribute_qstring(1,cl);
506         sql1_text := sql1_text || get_attribute_qstring(2,cl);
507         sql1_text := sql1_text || get_attribute_qstring(3, cl);
508         sql1_text := sql1_text || get_attribute_qstring(4,cl);
509         sql1_text := sql1_text || get_attribute_qstring(5,cl);
510 	fnd_file.put_line(fnd_file.log, 'SQL: '||sql1_text);
511 
512 	open c_dim_data for sql1_text;
513 	fnd_file.put_line(fnd_file.log, 'Cursor Opened');
514 
515 	loop
516 	c_dim_rec.dim_value_id.delete;
517 	fnd_file.put_line(fnd_file.log, 'Delete');
518 	fetch c_dim_data bulk collect into c_dim_rec.dim_value_id limit l_rows;
519 		fnd_file.put_line(fnd_file.log, 'Fetch');
520 		for I in 1..c_dim_rec.dim_value_id.count
521 		loop
522 		fnd_file.put_line(fnd_file.log, 'Dim_value_id: '||
523 					c_dim_rec.dim_value_id(I));
524 		begin
525 		update qpr_dimension_values
526 		set
527 		level2_value = nvl(decode(transf_header_rec.to_level_id, 2,
528 				transf_header_rec.to_value, null), level2_value),
529 		level2_desc = nvl(decode(transf_header_rec.to_level_id, 2,
530 				transf_header_rec.to_value_desc, null), level2_desc),
531 		level3_value = nvl(decode(transf_header_rec.to_level_id, 3,
532 				transf_header_rec.to_value, null), level3_value),
533 		level3_desc = nvl(decode(transf_header_rec.to_level_id, 3,
534 				transf_header_rec.to_value_desc, null), level3_desc),
535 		level4_value = nvl(decode(transf_header_rec.to_level_id, 4,
536 				transf_header_rec.to_value, null), level4_value),
537 		level4_desc = nvl(decode(transf_header_rec.to_level_id, 4,
538 				transf_header_rec.to_value_desc, null), level4_desc),
539 		level5_value = nvl(decode(transf_header_rec.to_level_id, 5,
540 				transf_header_rec.to_value, null), level5_value),
541 		level5_desc = nvl(decode(transf_header_rec.to_level_id, 5,
542 				transf_header_rec.to_value_desc, null), level5_desc),
543 		level6_value = nvl(decode(transf_header_rec.to_level_id, 6,
544 				transf_header_rec.to_value, null), level6_value),
545 		level6_desc = nvl(decode(transf_header_rec.to_level_id, 6,
546 				transf_header_rec.to_value_desc, null), level6_desc),
547 		level7_value = nvl(decode(transf_header_rec.to_level_id, 7,
548 				transf_header_rec.to_value, null), level7_value),
549 		level7_desc = nvl(decode(transf_header_rec.to_level_id, 7,
550 				transf_header_rec.to_value_desc, null), level7_desc),
551 		level8_value = nvl(decode(transf_header_rec.to_level_id, 8,
552 				transf_header_rec.to_value, null), level8_value),
553 		level8_desc = nvl(decode(transf_header_rec.to_level_id, 8,
554 				transf_header_rec.to_value_desc, null), level8_desc),
555 		last_update_date = sysdate,
556 		last_updated_by = fnd_global.user_id,
557 		last_update_login = fnd_global.login_id
558 		where dim_value_id = c_dim_rec.dim_value_id(I);
559 		fnd_file.put_line(fnd_file.log, 'Number of rows updated: '||sql%rowcount);
560 		exception
561 		     when others then
562 				errbuf := substr(SQLERRM,1,150);
563 				retcode := -1;
564 			fnd_file.put_line(fnd_file.log,
565 				substr(SQLERRM, 1, 1000));
566 		end;
567 /*
568 		if transf_header_rec.to_level_id = 2 then
569 			begin
570 			update qpr_dimension_values
571 			set level2_value = transf_header_rec.to_value,
572 			level2_desc = transf_header_rec.to_value_desc,
573 			last_update_date = sysdate,
574 			last_updated_by = fnd_global.user_id,
575 			last_update_login = fnd_global.login_id
576 			where dim_value_id = c_dim_rec.dim_value_id(I);
577 			exception
578 			     when others then
579 					errbuf := substr(SQLERRM,1,150);
580 					retcode := -1;
581 				fnd_file.put_line(fnd_file.log,
582 					substr(SQLERRM, 1, 1000));
583 			end;
584 		end if;
585 		if transf_header_rec.to_level_id = 3 then
586 			begin
587 			update qpr_dimension_values
588 			set level3_value = transf_header_rec.to_value,
589 			level3_desc = transf_header_rec.to_value_desc,
590 			last_update_date = sysdate,
591 			last_updated_by = fnd_global.user_id,
592 			last_update_login = fnd_global.login_id
593 			where dim_value_id = c_dim_rec.dim_value_id(I);
594 			exception
595 			     when others then
596 					errbuf := substr(SQLERRM,1,150);
597 					retcode := -1;
598 				fnd_file.put_line(fnd_file.log,
599 					substr(SQLERRM, 1, 1000));
600 			end;
601 		end if;*/
602 		end loop;
603 	exit when c_dim_data%NOTFOUND;
604 
605 	end loop;
606 
607 	close c_dim_data;
608 	commit;
609 end loop; --get_transf_header
610 
611 EXCEPTION
612      when others then
613 		errbuf := substr(SQLERRM,1,150);
614 		retcode := -1;
615 	fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
616 
617 END; --transformation process
618 
619 END QPR_TRANSFORMATION ;