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_REGRESSION_ANALYSIS

Source


1 PACKAGE BODY QPR_REGRESSION_ANALYSIS AS
2 /* $Header: QPRURGRB.pls 120.11 2008/04/16 13:08:27 kdhabali ship $ */
3 
4 
5 procedure log_debug(text varchar2) is
6 begin
7 	fnd_file.put_line( fnd_file.log, text);
8 end;
9 
10 procedure out_debug(text varchar2) is
11 begin
12 	fnd_file.put_line(fnd_file.output, text);
13 end;
14 
15 function validate_function(
16 		l_function in varchar2,
17 		l_parameter in varchar2) return boolean is
18 l_sql	varchar2(1000);
19 o_val	number;
20 begin
21 	l_sql := 'select ' || l_function || '(';
22 	if (l_parameter is not null and l_function <> 'sqrt') then
23 		l_sql := l_sql || l_parameter || ',';
24 	end if;
25 
26 	l_sql := l_sql || '2) from dual';
27 	execute immediate l_sql into o_val;
28 	return true;
29 exception
30 	when others then
31 		log_debug ('The Transformation Function defined in profile is not valid.');
32 		return false;
33 end;
34 
35 procedure reg_transf(
36 		i_pp_id in number,
37 		i_item_id in number,
38 		i_psg_id in number,
39 		i_value in number,
40 		o_value in out nocopy number) is
41 l_transf varchar2(300);
42 l_sql varchar2(1000);
43 begin
44 	select replace(log_transf,'<num>',i_value)
45 	into l_transf
46 	from qpr_regression_result
47 	where price_plan_id = i_pp_id
48 	and product_id = i_item_id
49 	and pr_segment_id = i_psg_id;
50 
51 	l_sql := 'select '||l_transf||' from dual';
52 	execute immediate l_sql into o_value;
53 
54 exception
55 	when NO_DATA_FOUND then
56 	o_value := 0;
57 end;
58 
59 procedure reg_antitransf(
60 		i_pp_id in number,
61 		i_item_id in number,
62 		i_psg_id in number,
63 		i_value in number,
64 		o_value in out nocopy number) is
65 l_antitransf varchar2(300);
66 l_sql varchar2(1000);
67 begin
68 	select replace(antilog_transf,'<num>',i_value)
69 	into l_antitransf
70 	from qpr_regression_result
71 	where price_plan_id = i_pp_id
72 	and product_id = i_item_id
73 	and pr_segment_id = i_psg_id;
74 
75 	l_sql := 'select '||l_antitransf||' from dual';
76 	execute immediate l_sql into o_value;
77 
78 exception
79 	when NO_DATA_FOUND then
80 	o_value := 0;
81 
82 end;
83 
84 procedure do_regress(
85 	errbuf		out nocopy varchar2,
86 	retcode		out nocopy varchar2,
87 	p_price_plan_id	in number,
88 	p_start_date	in varchar2,
89 	p_end_date	in varchar2,
90 	p_i_prd_id	in varchar2,
91 	p_f_prd_id	in varchar2,
92 	p_i_psg_id	in varchar2,
93 	p_f_psg_id	in varchar2)
94 is
95 
96 cursor c_scopes (l_pp_id number) is
97 select
98 sc.dim_code dim_code, hier.hierarchy_ppa_code hier_code,
99 hl.level_seq_num lvl_num, sc.scope_value lvl_value,
100 sc.operator op_sign
101 from
102 qpr_scopes sc, qpr_dimensions dim,
103 qpr_hierarchies hier, qpr_hier_levels hl
104 where dim.price_plan_id = 1
105 and hier.price_plan_id = 1
106 and hl.price_plan_id = 1
107 and sc.dim_code = dim.dim_ppa_code
108 and sc.hierarchy_id = hier.hierarchy_id
109 and sc.level_id = hl.hierarchy_level_id
110 and sc.parent_entity_type='DATAMART'
111 and sc.parent_id = p_price_plan_id;
112 
113 
114 l_uom		varchar2(3);
115 l_curr		varchar2(15);
116 l_start_dt	date;
117 l_end_dt	date;
118 date_from	date;
119 date_to		date;
120 l_instance_id	number;
121 l_log		varchar2(30) := '';
122 l_display_log	varchar2(30) := '';
123 l_alog		varchar2(30) := '';
124 l_base		number := 10;
125 l_request_id	number;
126 l_pp_name	varchar2(240);
127 l_x		varchar2(300);
128 l_y		varchar2(300);
129 l_transf_func	varchar2(240);
130 l_atransf_func	varchar2(240);
131 l_parameter_to_func	varchar2(240);
132 l_bool_transf	boolean;
133 l_bool_atransf	boolean;
134 l_scope_exists	number := 0;
135 l_dim_clause	varchar2(30);
136 
137 l_rows		natural := 1000;
138 l_sql		varchar2(30000);
139 
140 c_regr_data	QPRREGRDATA;
141 c_regr_data_rec	REGR_DATA_REC_TYPE;
142 
143 BEGIN
144 
145 	date_from := fnd_date.canonical_to_date(p_start_date);
146 	date_to := fnd_date.canonical_to_date(p_end_date);
147 
148 	select BASE_UOM_CODE, CURRENCY_CODE,
149 	START_DATE, END_DATE, INSTANCE_ID, name
150 	into l_uom, l_curr, l_start_dt, l_end_dt, l_instance_id, l_pp_name
151 	from qpr_price_plans_vl
152 	where price_plan_id = p_price_plan_id;
153 
154    -- Date Checks
155 	if (date_from > date_to) then
156 		log_debug ('The start date is after the end date.') ;
157 		return;
158 	end if;
159 
160 	if (date_from > l_start_dt) then
161 		l_start_dt := date_from;
162 	end if;
163 
164 	if (l_end_dt is not null) then
165 		if (date_to < l_end_dt) then
166 			l_end_dt := date_to;
167 		end if;
168 	else -- if l_end_dt is null
169 		l_end_dt := date_to;
170 	end if;
171    --
172 
173 	l_transf_func := qpr_sr_util.read_parameter('QPR_TRANSF_FUNC');
174 	case
175 		when l_transf_func = 'ln' then
176 			l_atransf_func := 'exp';
177 			l_parameter_to_func := '';
178 		when l_transf_func = 'log' then
179 			l_atransf_func := 'power';
180 			l_parameter_to_func := qpr_sr_util.read_parameter('QPR_PARAM_FUNC');
181 		when l_transf_func = 'sqrt' then
182 			l_atransf_func := 'power';
183 			l_parameter_to_func := '2';
184 		when l_transf_func = 'power' then
185 			l_atransf_func := 'log';
186 			l_parameter_to_func := qpr_sr_util.read_parameter('QPR_PARAM_FUNC');
187 		when l_transf_func = 'exp' then
188 			l_atransf_func := 'ln';
189 			l_parameter_to_func := '';
190 		else
191 			l_atransf_func := qpr_sr_util.read_parameter('QPR_ATRANSF_FUNC');
192 			l_parameter_to_func := qpr_sr_util.read_parameter('QPR_PARAM_FUNC');
193 	end case;
194 
195 	l_bool_transf := validate_function(l_transf_func, l_parameter_to_func);
196 	l_bool_atransf := validate_function(l_atransf_func, l_parameter_to_func);
197 
198 	if (l_bool_transf = false or l_bool_atransf = false) then
199 		retcode := 2;
200 		return;
201 	end if;
202 
203 	l_log := l_transf_func || '(';
204 	l_alog := l_atransf_func || '(';
205 	if (l_parameter_to_func is not null) then
206 		if (l_transf_func <> 'sqrt') then
207 			l_log := l_log || l_parameter_to_func || ',';
208 		end if;
209 		l_alog := l_alog || l_parameter_to_func || ',';
210 	end if;
211 	l_display_log := l_log || '<num>)';
212 	l_alog := l_alog || '<num>)';
213 
214 	log_debug ('Transformation Function for Regression: '||l_display_log);
215 
216     -- Dependant Variable
217 	l_y := l_log || 'measure4_number'; -- Unit Selling Price
218 	l_y := l_y || '* qpr_sr_util.ods_curr_conversion(null,'''|| l_curr||''' , null, time_level_value, instance_id)';
219 	l_y := l_y || '/qpr_sr_util.ods_uom_conv(prd_level_value, measure_uom, '''||l_uom||''', instance_id))';
220     -- Independant Variable
221 	l_x := l_log || 'measure1_number'; -- Ordered Quantity
222 	l_x := l_x || '* qpr_sr_util.ods_uom_conv(prd_level_value, measure_uom, '''||l_uom||''', instance_id))';
223 
224 --	log_debug('X: '||l_x);
225 --	log_debug('Y: '||l_y);
226 
227 	l_sql := 'select prd_level_value, psg_level_value, ';
228 	l_sql := l_sql || ' regr_slope('||l_y||','||l_x||'), ';
229 	l_sql := l_sql || ' regr_intercept('||l_y||','||l_x||'), ';
230 	l_sql := l_sql || ' regr_r2('||l_y||','||l_x||'), ';
231 	l_sql := l_sql || ' regr_count('||l_y||','||l_x||') ';
232 	l_sql := l_sql || ' from qpr_measure_data where measure_type_code=''SALESDATA'' ';
233 	l_sql := l_sql || ' and instance_id = '|| l_instance_id ||' and time_level_value between :1 and :2 ';
234 
235 	if (p_i_prd_id is not null) then
236 		l_sql := l_sql || ' and prd_level_value >= to_number('||p_i_prd_id||') ';
237 	end if;
238 	if (p_f_prd_id is not null) then
239 		l_sql := l_sql || ' and prd_level_value <= to_number('||p_f_prd_id||') ';
240 	end if;
241 	if (p_i_psg_id is not null) then
242 		l_sql := l_sql || ' and psg_level_value >= to_number('||p_i_psg_id||') ';
243 	end if;
244 	if (p_f_psg_id is not null) then
245 		l_sql := l_sql || ' and psg_level_value <= to_number('||p_f_psg_id||') ';
246 	end if;
247 
248 	l_sql := l_sql || ' and nvl(measure1_number,0) > 0 ';
249 	l_sql := l_sql || ' and nvl(measure4_number,0) > 0 ';
250 
251 	l_sql := l_sql || ' and qpr_sr_util.ods_curr_conversion(null,''';
252 	l_sql := l_sql || l_curr||''' , null, time_level_value, instance_id) > 0 ';
253 	l_sql := l_sql || ' and qpr_sr_util.ods_uom_conv(prd_level_value, measure_uom, '''||l_uom||''', instance_id) > 0 ';
254 
255 
256 	begin
257 		select 1 into l_scope_exists
258 		from qpr_scopes
259 		where parent_entity_type = 'DATAMART'
260 		and parent_id = p_price_plan_id
261 		and rownum < 2;
262 	exception
263 		when NO_DATA_FOUND then
264 		l_scope_exists := 0;
265 	end;
266 
267 	if (l_scope_exists = 1) then
268 
269 	for l_scope_rec in c_scopes (p_price_plan_id) loop
270 		case
271 			when l_scope_rec.dim_code = 'ORG' then l_dim_clause := 'org_level_value';
272 			when l_scope_rec.dim_code = 'REP' then l_dim_clause := 'rep_level_value';
273 			when l_scope_rec.dim_code = 'PSG' then l_dim_clause := 'psg_level_value';
274 			when l_scope_rec.dim_code = 'PRD' then l_dim_clause := 'prd_level_value';
275 			when l_scope_rec.dim_code = 'CUS' then l_dim_clause := 'cus_level_value';
276 			when l_scope_rec.dim_code = 'CHN' then l_dim_clause := 'chn_level_value';
277 			when l_scope_rec.dim_code = 'GEO' then l_dim_clause := 'geo_level_value';
278 		end case;
279 		l_sql := l_sql || ' and ' || l_dim_clause;
280 		l_sql := l_sql || ' in (select level1_value from qpr_dimension_values where ';
281 		l_sql := l_sql || ' instance_id = ' || l_instance_id;
282 		l_sql := l_sql || ' and dim_code = ''' || l_scope_rec.dim_code;
283 		l_sql := l_sql || ''' and hierarchy_code = ''' || l_scope_rec.hier_code;
284 		l_sql := l_sql || ''' and level'||l_scope_rec.lvl_num||'_value ';
285 		l_sql := l_sql || l_scope_rec.op_sign || ' ''' || l_scope_rec.lvl_value || ''') ';
286 	end loop;
287 
288 	end if; -- end l_scope_exists
289 
290 
291 	l_sql := l_sql || ' group by prd_level_value, psg_level_value ';
292 
293 	log_debug ('SQL: '||l_sql);
294 	log_debug ('Dates: '||l_start_dt||' to '||l_end_dt);
295 
296 	out_debug('Regression Analysis on Datamart: '||l_pp_name|| ' on '||sysdate);
297 	out_debug('Transformation Function for Regression: '||l_display_log);
298 	out_debug('Transformation Function for Recommended Price Derivation: '||l_alog);
299 	out_debug(rpad('-',63,'-'));
300 	out_debug(rpad('| Item',9)||rpad('| Pr-Seg',10)||
301 		rpad('| Slope',12)||rpad('| Intercept',12)||
302 		rpad('| R-Square',10)||rpad('| Count',9)||'|');
303 	out_debug(rpad('-',63,'-'));
304 
305 	open c_regr_data for l_sql using l_start_dt, l_end_dt;
306 	loop
307 		log_debug('Clearing Cursor');
308 		c_regr_data_rec.product_id.delete;
309 		c_regr_data_rec.pr_segment_id.delete;
310 		c_regr_data_rec.regression_slope.delete;
311 		c_regr_data_rec.regression_intercept.delete;
312 		c_regr_data_rec.regression_r2.delete;
313 		c_regr_data_rec.regression_count.delete;
314 
315 		log_debug('Fetching Data');
316 		fetch c_regr_data bulk collect into
317 		c_regr_data_rec.product_id,
318 		c_regr_data_rec.pr_segment_id,
319 		c_regr_data_rec.regression_slope,
320 		c_regr_data_rec.regression_intercept,
321 		c_regr_data_rec.regression_r2,
322 		c_regr_data_rec.regression_count
323 		limit l_rows;
324 
325 		fnd_profile.get('CONC_REQUEST_ID', l_request_id);
326 
327 		log_debug ('Count: '||c_regr_data_rec.product_id.count);
328 
332 			and pr_segment_id = c_regr_data_rec.pr_segment_id(I)
329 		forall I in 1..c_regr_data_rec.product_id.count
330 			delete from QPR_REGRESSION_RESULT
331 			where product_id = c_regr_data_rec.product_id(I)
333 			and price_plan_id = p_price_plan_id;
334 
335 		forall I in 1..c_regr_data_rec.product_id.count
336 			INSERT INTO QPR_REGRESSION_RESULT
337 			(regression_result_id, price_plan_id,
338 			product_id, pr_segment_id,
339 			regression_slope, regression_intercept,
340 			regression_r2, regression_count,
341 			log_transf, antilog_transf,
342 			creation_date, created_by,
343 			last_update_date, last_updated_by,
344 			last_update_login, program_application_id,
345 			program_id, program_login_id,
346 			request_id)
347 			values
348 			(QPR_REGRESSION_RESULT_S.nextval, p_price_plan_id,
349 			c_regr_data_rec.product_id(I), c_regr_data_rec.pr_segment_id(I),
350 			c_regr_data_rec.regression_slope(I), c_regr_data_rec.regression_intercept(I),
351 			c_regr_data_rec.regression_r2(I), c_regr_data_rec.regression_count(I),
352 			l_display_log, l_alog,
353 			sysdate, fnd_global.user_id,
354 			sysdate, fnd_global.user_id,
355 			fnd_global.conc_login_id, fnd_global.prog_appl_id,
356 			fnd_global.conc_program_id, null,
357 			l_request_id);
358 
359 		for I in 1..c_regr_data_rec.product_id.count
360 		loop
361 			out_debug('| '||rpad(c_regr_data_rec.product_id(I),7)||
362 				'| '||rpad(c_regr_data_rec.pr_segment_id(I),8)||
363 				'| '||rpad(round(c_regr_data_rec.regression_slope(I),3),10)||
364 				'| '||rpad(round(c_regr_data_rec.regression_intercept(I),3),10)||
365 				'| '||rpad(round(c_regr_data_rec.regression_r2(I),3),8)||
366 				'| '||rpad(c_regr_data_rec.regression_count(I),7)||'|');
367 		end loop;
368 
372 
369 		log_debug('No of rows processed: '||sql%rowcount);
370 
371 		commit;
373 		exit when c_regr_data%NOTFOUND;
374 	end loop;
375 	close c_regr_data;
376 
377 	out_debug(rpad('-',63,'-'));
378 
379 
380 EXCEPTION
381 	WHEN OTHERS THEN
382 		log_debug('Unexcpected Error in Regression Analysis:'||sqlerrm);
383 		retcode := 2;
384 
385 END; -- do_regress
386 
387 END QPR_REGRESSION_ANALYSIS ;
388