[Home] [Help]
PACKAGE BODY: APPS.QPR_SPECIAL_ETL
Source
1 PACKAGE BODY QPR_SPECIAL_ETL AS
2 /* $Header: QPRUSPLB.pls 120.5 2008/01/04 13:14:44 bhuchand noship $ */
3
4 procedure collect_cost(
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY VARCHAR2,
7 p_from_date varchar2,
8 p_to_date varchar2,
9 p_instance_id number ) is
10 l_sql varchar2(10000);
11 date_from date;
12 date_to date;
13 l_rows number := 1000;
14 l_ctr number := 0;
15 l_cost_alloc_perc number := 0;
16 first_time number := 1;
17 l_dummy number;
18 b_data_present boolean := true;
19
20 l_user_id number := FND_GLOBAL.USER_ID;
21 d_sysdate date := sysdate;
22 l_login_id number := FND_GLOBAL.LOGIN_ID;
23 l_request_id number := FND_GLOBAL.conc_request_id;
24
25 c_measures SYS_REFCURSOR;
26 c_cost_data COST_REC_TYPE;
27 t_meas_id num_type;
28 t_ord_val num_type;
29 t_ord_meas num_type;
30 t_cost num_type;
31 t_cost_level char240_type;
32
33 cursor c_sales(p_date_from date, p_date_to date) is
34 select measure_value_id, ord_level_value
35 from qpr_measure_data where instance_id = p_instance_id
36 and measure_type_code = 'SALESDATA'
37 and time_level_value between p_date_from and p_date_to
38 and measure1_char in ('STANDARD','SERVICE');
39
40 cursor c_sales_mdl(p_date_from date, p_date_to date) is
41 select measure_value_id, ord_level_value
42 from qpr_measure_data where instance_id = p_instance_id
43 and measure_type_code = 'SALESDATA'
44 and time_level_value between p_date_from and p_date_to
45 and measure1_char not in ('STANDARD','SERVICE');
46
47 /********** Mapping info in qpr_plan_measures *********
48 price_plan_id = Request_id
49 price_plan_meas_grp_id = 999
50 price_plan_meas_grp_name = COST
51 date_attribute = booked_date
52 attribute_1 = inventory_item_id
53 attribute_2 = cost_level_value
54 attribute_3 = ord_level_value
55 attribute_4 = top_model_line_id
56 attribute_5 = link_to_line_id
57 attribute_6 = item_type_code
58 attribute_7 = component_code
59 attribute_8 = ato_line_id
60 attribute_9 = unit_cost
61 attribute_10 = unit list price
62 attribute_11 = ordered quantity
63 */
64 cursor c_kit is
65 select m2.attribute_3 ord, null, m2.attribute_2,
66 nvl(decode(m2.attribute_6,
67 'INCLUDED', 0,
68 'KIT', (case when (to_number(m2.attribute_9) <> 0) then
69 to_number(m2.attribute_9)
70 else
71 (select sum(m3.attribute_9)
72 from qpr_plan_measures m3
73 where m3.price_plan_id= l_request_id
74 and m3.price_plan_meas_grp_id = 999
75 and m3.attribute_6 = 'INCLUDED'
76 and m3.attribute_5 = m2.attribute_4)
77 end)
78 ) * (m2.attribute_11), 0) cost
79 from qpr_plan_measures m1, qpr_plan_measures m2
80 where m1.price_plan_id= l_request_id
81 and m1.price_plan_meas_grp_id = 999
82 and m2.price_plan_id= l_request_id
83 and m2.price_plan_meas_grp_id = 999
84 and m1.attribute_6 = 'KIT'
85 and m1.attribute_5 is null
86 and m2.attribute_4 = m1.attribute_3;
87
88 cursor c_ato(p_cost_mrg number) is
89 select m2.attribute_3 ord, null, m2.attribute_2,
90 nvl(decode(m2.attribute_6,
91 'OPTION', m2.attribute_9,
92 'CONFIG', 0,
93 (select
94 case when nvl(t.cost_to_alloc,0) = 0 then
95 -1 * p_cost_mrg * to_number( m2.attribute_10)
96 else
97 decode(t1.list_price_rev, 0 , 0,
98 (t.cost_to_alloc * m2.attribute_10 * m2.attribute_11
99 / t1.list_price_rev))
100 end
101 from
102 (select nvl(sum(c.attribute_9)/count(c.attribute_9) -
103 sum(o.attribute_9) , 0) cost_to_alloc,
104 o.attribute_8
105 from qpr_plan_measures c, qpr_plan_measures o
106 where o.price_plan_id= l_request_id
107 and o.price_plan_meas_grp_id = 999
108 and o.attribute_6 = 'OPTION'
109 and o.attribute_8 is not null
110 and o.attribute_9 <> 0
111 and o.attribute_8 = c.attribute_8(+)
112 and c.price_plan_id(+) = l_request_id
113 and c.price_plan_meas_grp_id(+) = 999
114 and c.attribute_6(+) = 'CONFIG'
115 group by o.attribute_8) t,
116 (select sum(m3.attribute_10 * m3.attribute_11) list_price_rev,
117 m3.attribute_8
118 from qpr_plan_measures m3
119 where m3.price_plan_id= l_request_id
120 and m3.price_plan_meas_grp_id = 999
121 and m3.attribute_6 in('ATOMODEL', 'ATOCLASS')
122 group by m3.attribute_8) t1
123 where t1.attribute_8 = m2.attribute_8
124 and t.attribute_8(+) = t1.attribute_8
125 )
126 ) * (m2.attribute_11) , 0) cost
127 from qpr_plan_measures m1, qpr_plan_measures m2
128 where m1.price_plan_id= l_request_id
129 and m1.price_plan_meas_grp_id = 999
130 and m2.price_plan_id= l_request_id
131 and m2.price_plan_meas_grp_id = 999
132 and m1.attribute_6 = 'ATOMODEL'
133 and m1.attribute_5 is null
134 and m2.attribute_8 = m1.attribute_8;
135
136 cursor c_pto(p_cost_mrg number) is
137 select m2.attribute_3 ord, null, m2.attribute_2,
138 nvl((case when (m2.attribute_6 = 'INCLUDED') then 0
139 when (m2.attribute_6 = 'OPTION') then to_number(m2.attribute_9)
140 when (m2.attribute_6 = 'CONFIG') then 0
141 when (m2.attribute_6 = 'PTOMODEL') or (m2.attribute_6 = 'PTOCLASS')
142 then
143 (select case when nvl(t.cost_to_alloc,0) = 0 then
144 -1* p_cost_mrg * to_number(m2.attribute_10)
145 else
146 decode(t1.list_price_rev, 0 , 0,
147 (t.cost_to_alloc * m2.attribute_10 *
148 m2.attribute_11 / t1.list_price_rev))
149 end
150 from
151 (select nvl(sum(c.attribute_9) , 0) cost_to_alloc , c.attribute_4
152 from qpr_plan_measures c
153 where c.price_plan_id = l_request_id
154 and c.price_plan_meas_grp_id = 999
155 and c.attribute_6 = 'INCLUDED'
156 group by c.attribute_4
157 ) t,
158 (select sum(m3.attribute_10 * m3.attribute_11) list_price_rev,
159 m3.attribute_4
160 from qpr_plan_measures m3
161 where m3.price_plan_id= l_request_id
162 and m3.price_plan_meas_grp_id = 999
163 and m3.attribute_6 in('PTOMODEL', 'PTOCLASS')
164 group by m3.attribute_4) t1
165 where t1.attribute_4 = m2.attribute_4
166 and t.attribute_4(+) = t1.attribute_4
167 )
168 else
169 (select case when nvl(t.cost_to_alloc,0) = 0 then
170 -1 * p_cost_mrg * to_number( m2.attribute_10)
171 else
172 decode(t1.list_price_rev, 0 , 0,
173 (t.cost_to_alloc * m2.attribute_10 *
174 m2.attribute_11 / t1.list_price_rev))
175 end
176 from
177 ( select nvl(sum(c.attribute_9)/count(c.attribute_9) -
178 sum(o.attribute_9) , 0) cost_to_alloc
179 , o.attribute_8
180 from qpr_plan_measures c, qpr_plan_measures o
181 where o.price_plan_id = l_request_id
182 and o.price_plan_meas_grp_id = 999
183 and o.attribute_6 = 'OPTION'
184 and o.attribute_8 is not null
185 and o.attribute_9 <> 0
186 and o.attribute_8 = c.attribute_8(+)
187 and c.price_plan_id(+) = l_request_id
188 and c.price_plan_meas_grp_id(+) = 999
189 and c.attribute_6(+) = 'CONFIG'
190 group by o.attribute_8) t,
191 (select sum(m3.attribute_10 * m3.attribute_11) list_price_rev,
192 m3.attribute_8
193 from qpr_plan_measures m3
194 where m3.price_plan_id= l_request_id
195 and m3.price_plan_meas_grp_id = 999
196 and m3.attribute_6 in('ATOMODEL', 'ATOCLASS')
197 group by m3.attribute_8) t1
198 where t1.attribute_8 = m2.attribute_8
199 and t.attribute_8(+) = t1.attribute_8
200 )
201 end
202 ) * m2.attribute_11 , 0) cost
203 from qpr_plan_measures m1, qpr_plan_measures m2
204 where m1.price_plan_id= l_request_id
205 and m1.price_plan_meas_grp_id = 999
206 and m2.price_plan_id= l_request_id
207 and m2.price_plan_meas_grp_id = 999
208 and m1.attribute_6 = 'PTOMODEL'
209 and m1.attribute_5 is null
210 and m2.attribute_4 = m1.attribute_4;
211
212 procedure handle_kit_items is
213 begin
214 fnd_file.put_line(fnd_file.log, 'Handle kit items...');
215 open c_kit;
216 loop
217 t_ord_val.delete;
218 t_meas_id.delete;
219 t_cost.delete;
220 t_cost_level.delete;
221
222 l_ctr := 0;
223
224 fetch c_kit bulk collect into
225 t_ord_val, t_meas_id,t_cost_level, t_cost
226 limit l_rows;
227 exit when t_ord_val.count=0;
228
229 fnd_file.put_line(fnd_file.log, 'Records to process:'||t_ord_val.count);
230
231 for i in 1..t_ord_val.count loop
232 if t_ord_meas.exists(t_ord_val(i)) then
233 t_meas_id(i) := t_ord_meas(t_ord_val(i));
234 l_ctr := l_ctr + 1;
235 end if;
236 end loop;
237
238 if l_ctr > 0 then
239 fnd_file.put_line(fnd_file.log, 'Updating kit items...');
240 FORALL I IN 1..t_ord_val.COUNT
241 update qpr_measure_data
242 set measure15_number = t_cost(i),
243 cos_level_value = t_cost_level(i),
244 LAST_UPDATE_DATE = d_sysdate,
245 LAST_UPDATED_BY = l_user_id,
246 LAST_UPDATE_LOGIN = l_login_id,
247 request_id = l_request_id
248 where measure_value_id = t_meas_id(i)
249 and t_meas_id(i) is not null;
250 else
251 fnd_file.put_line(fnd_file.log, 'No Sales data found');
252 end if;
253 end loop;
254 close c_kit;
255 exception
256 when OTHERS then
257 fnd_file.put_line(fnd_file.log, 'Error in kit item cost updation');
258 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
259 raise;
260 end handle_kit_items;
261
262 procedure handle_ato_items is
263 begin
264 fnd_file.put_line(fnd_file.log, 'Handle ato model items...');
265 open c_ato(l_cost_alloc_perc);
266 loop
267 t_ord_val.delete;
268 t_meas_id.delete;
269 t_cost.delete;
270 t_cost_level.delete;
271
272 l_ctr := 0;
273
274 fetch c_ato bulk collect into
275 t_ord_val, t_meas_id,t_cost_level, t_cost
276 limit l_rows;
277 exit when t_ord_val.count=0;
278
279 fnd_file.put_line(fnd_file.log, 'Records to process:'||t_ord_val.count);
280
281 for i in 1..t_ord_val.count loop
282 if t_ord_meas.exists(t_ord_val(i)) then
283 t_meas_id(i) := t_ord_meas(t_ord_val(i));
284 l_ctr := l_ctr + 1;
285 end if;
286 end loop;
287
288 if l_ctr > 0 then
289 fnd_file.put_line(fnd_file.log, 'Updating ato model items...');
290 FORALL I IN 1..t_ord_val.COUNT
291 update qpr_measure_data
292 set measure15_number = t_cost(i),
293 cos_level_value = t_cost_level(i),
294 LAST_UPDATE_DATE = d_sysdate,
295 LAST_UPDATED_BY = l_user_id,
296 LAST_UPDATE_LOGIN = l_login_id,
297 request_id = l_request_id
298 where measure_value_id = t_meas_id(i)
299 and t_meas_id(i) is not null;
300 else
301 fnd_file.put_line(fnd_file.log, 'No Sales data found');
302 end if;
303 end loop;
304 close c_ato;
305 exception
306 when OTHERS then
307 fnd_file.put_line(fnd_file.log, 'Error in ato model item cost updation');
308 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
309 raise;
310 end handle_ato_items;
311
312 procedure handle_pto_items is
313 begin
314 fnd_file.put_line(fnd_file.log, 'Handle pto model items...');
315 open c_pto(l_cost_alloc_perc);
316 loop
317 t_ord_val.delete;
318 t_meas_id.delete;
319 t_cost.delete;
320 t_cost_level.delete;
321
322 l_ctr := 0;
323
324 fetch c_pto bulk collect into
325 t_ord_val, t_meas_id,t_cost_level, t_cost
326 limit l_rows;
327 exit when t_ord_val.count=0;
328
329 fnd_file.put_line(fnd_file.log, 'Records to process:'||t_ord_val.count);
330
331 for i in 1..t_ord_val.count loop
332 if t_ord_meas.exists(t_ord_val(i)) then
333 t_meas_id(i) := t_ord_meas(t_ord_val(i));
334 l_ctr := l_ctr + 1;
335 end if;
336 end loop;
337
338 if l_ctr > 0 then
339 fnd_file.put_line(fnd_file.log, 'Updating pto model items...');
340 FORALL I IN 1..t_ord_val.COUNT
341 update qpr_measure_data
342 set measure15_number = t_cost(i),
343 cos_level_value = t_cost_level(i),
344 LAST_UPDATE_DATE = d_sysdate,
345 LAST_UPDATED_BY = l_user_id,
346 LAST_UPDATE_LOGIN = l_login_id,
347 request_id = l_request_id
348 where measure_value_id = t_meas_id(i)
349 and t_meas_id(i) is not null;
350 else
351 fnd_file.put_line(fnd_file.log, 'No Sales data found');
352 end if;
353 end loop;
354 close c_pto;
355 exception
356 when OTHERS then
357 fnd_file.put_line(fnd_file.log, 'Error in pto model item cost updation');
358 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
359 raise;
360 end handle_pto_items;
361
362 /* main method begins*/
363 begin
364 date_from := fnd_date.canonical_to_date(p_from_date);
365 date_to := fnd_date.canonical_to_date(p_to_date);
366
367 l_sql:= ' select ord_level_value, booked_date, cos_level_value, unit_cost, '||
368 ' unit_list_price, top_model_line_id, link_to_line_id, item_type_code, '||
369 ' inventory_item_id, component_code, ato_line_id , order_quantity' ||
370 ' from qpr_sr_cost_data_v'|| qpr_sr_util.get_dblink(p_instance_id)||
371 ' where booked_date between :1 and :2 ' ||
372 ' and item_type_code in (''STANDARD'',''SERVICE'')';
373
374 fnd_file.put_line(fnd_file.log, 'Process Standard and service items..');
375 open c_sales(date_from, date_to);
376 fetch c_sales bulk collect into t_meas_id, t_ord_val;
377 close c_sales;
378
379 if t_ord_val.count > 0 then
380 for j in t_ord_val.first..t_ord_val.last loop
381 t_ord_meas(t_ord_val(j)):= t_meas_id(j);
382 end loop;
383 t_meas_id.delete;
384 t_ord_val.delete;
385 end if;
386
387 if t_ord_meas.count > 0 then
388 fnd_file.put_line(fnd_file.log, 'Sales Records to update:' || t_ord_meas.count);
389 open c_measures for l_sql using date_from, date_to;
390 loop
391 c_cost_data.ord_level_value.delete;
392 c_cost_data.booked_date.delete;
393 c_cost_data.cos_level_value.delete;
394 c_cost_data.cost_value.delete;
395 c_cost_data.unit_list_price.delete;
396 c_cost_data.TOP_MODEL_LINE_ID.delete;
397 c_cost_data.LINK_TO_LINE_ID.delete;
398 c_cost_data.ITEM_TYPE_CODE.delete;
399 c_cost_data.INVENTORY_ITEM_ID.delete;
400 c_cost_data.COMPONENT_CODE.delete;
401 c_cost_data.ato_line_id.delete;
402 c_cost_data.ord_quantity.delete;
403
404 l_ctr := 0;
405
406 fetch c_measures bulk collect into
407 c_cost_data.ord_level_value,
408 c_cost_data.booked_date,
409 c_cost_data.cos_level_value,
410 c_cost_data.cost_value,
411 c_cost_data.unit_list_price,
412 c_cost_data.TOP_MODEL_LINE_ID,
413 c_cost_data.LINK_TO_LINE_ID,
414 c_cost_data.ITEM_TYPE_CODE,
415 c_cost_data.INVENTORY_ITEM_ID,
416 c_cost_data.COMPONENT_CODE,
417 c_cost_data.ato_line_id,
418 c_cost_data.ord_quantity
419 limit l_rows;
420
421 exit when c_cost_data.ord_level_value.count=0;
422 fnd_file.put_line(fnd_file.log,
423 'Iteration...Records to process:' ||c_cost_data.ord_level_value.count);
424
425 ----- Insert Dimension ----
426 FOR I IN 1..c_cost_data.ord_level_value.count LOOP
427 if first_time = 1 then
428 begin
429 select 1 into l_dummy
430 from qpr_dimension_values
431 where dim_code = 'COS'
432 and hierarchy_code = 'COSTING'
433 and level1_value = c_cost_data.cos_level_value(I)
434 and instance_id = p_instance_id;
435 fnd_file.put_line(fnd_file.log,'Cost dim present');
436 exception
437 WHEN NO_DATA_FOUND THEN
438 begin
439 fnd_file.put_line(fnd_file.log,
440 'Inserting Cost dim :' || c_cost_data.cos_level_value(I));
441 INSERT INTO
442 qpr_dimension_values(instance_id,
443 dim_value_id,
444 dim_code,
445 hierarchy_code,
446 level1_value,
447 level1_desc,
448 level2_value,
449 level2_desc,
450 level3_value,
451 level3_desc,
452 CREATION_DATE,
453 CREATED_BY,
454 LAST_UPDATE_DATE,
455 LAST_UPDATED_BY,
456 LAST_UPDATE_LOGIN,
457 REQUEST_ID) values
458 (p_instance_id,
459 qpr_dimension_values_s.nextval,
460 'COS',
461 'COSTING',
462 c_cost_data.cos_level_value(I),
463 c_cost_data.cos_level_value(I),
464 to_char(qpr_sr_util.get_null_pk),
465 qpr_sr_util.get_cost_type_desc,
466 to_char(qpr_sr_util.get_all_cos_pk),
467 qpr_sr_util.get_all_cos_desc,
468 d_sysdate,
469 l_user_id,
470 d_sysdate,
471 l_user_id,
472 l_login_id,
473 l_request_id);
474 exception
475 when others then
476 retcode := 2;
477 errbuf := FND_MESSAGE.GET;
478 fnd_file.put_line(fnd_file.log,
479 dbms_utility.format_error_backtrace);
480 return;
481 end;
482 end;
483 first_time :=0;
484 end if;
485 exit when first_time=0;
486 end loop;
487
488 fnd_file.put_line(fnd_file.log,'Associating order and measure value id...');
489 for i in c_cost_data.ord_level_value.first..
490 c_cost_data.ord_level_value.last loop
491 if t_ord_meas.exists(c_cost_data.ord_level_value(i)) then
492 c_cost_data.measure_val_id(i) := t_ord_meas(
493 c_cost_data.ord_level_value(i)) ;
494 l_ctr := l_ctr + 1;
495 end if;
496 end loop;
497
498 if l_ctr > 0 then
499 fnd_file.put_line(fnd_file.log,'Updating sales data with cost measure...');
500 FORALL I IN 1..c_cost_data.ord_level_value.COUNT
501 update qpr_measure_data
502 set measure15_number = (c_cost_data.cost_value(I) *
503 c_cost_data.ord_quantity(I)),
504 cos_level_value = c_cost_data.cos_level_value(I),
505 LAST_UPDATE_DATE = d_sysdate,
506 LAST_UPDATED_BY = l_user_id,
507 LAST_UPDATE_LOGIN = l_login_id,
508 request_id = l_request_id
509 where measure_value_id = c_cost_data.measure_val_id(i)
510 and c_cost_data.measure_val_id(i) is not null;
511 else
512 fnd_file.put_line(fnd_file.log, 'Sales data not found.No update done');
513 end if;
514 end loop;
515 close c_measures;
516 else
517 b_data_present := false;
518 end if;
519 t_ord_meas.delete;
520
521 commit;
522
523 fnd_file.put_line(fnd_file.log, 'Process Model and kit items..');
524
525 l_sql:= ' select ord_level_value, booked_date, cos_level_value, unit_cost, '||
526 ' unit_list_price, top_model_line_id, link_to_line_id, item_type_code, '||
527 ' inventory_item_id, component_code, ato_line_id , order_quantity' ||
528 ' from qpr_sr_cost_data_v'||qpr_sr_util.get_dblink(p_instance_id)||
529 ' where booked_date between :1 and :2 ' ||
530 ' and item_type_code not in (''STANDARD'',''SERVICE'')';
531
532 open c_sales_mdl(date_from, date_to);
533 fetch c_sales_mdl bulk collect into t_meas_id, t_ord_val;
534 close c_sales_mdl;
535
536 if t_ord_val.count > 0 then
537 for j in t_ord_val.first..t_ord_val.last loop
538 t_ord_meas(t_ord_val(j)):= t_meas_id(j);
539 end loop;
540 t_meas_id.delete;
541 t_ord_val.delete;
542 else
543 if b_data_present = false then
544 retcode := 1;
545 fnd_file.put_line(fnd_file.log,
546 'Salesdata measures not found.Cost measures cannot be updated.');
547 fnd_file.put_line(fnd_file.log,
548 'Try again after extracting Salesdata measures for given date range.');
549 return;
550 end if;
551 end if;
552
553 if t_ord_meas.count > 0 then
554 fnd_file.put_line(fnd_file.log, 'Sales Records to update:' || t_ord_meas.count);
555 open c_measures for l_sql using date_from, date_to;
556 loop
557 c_cost_data.ord_level_value.delete;
558 c_cost_data.booked_date.delete;
559 c_cost_data.cos_level_value.delete;
560 c_cost_data.cost_value.delete;
561 c_cost_data.unit_list_price.delete;
562 c_cost_data.TOP_MODEL_LINE_ID.delete;
563 c_cost_data.LINK_TO_LINE_ID.delete;
564 c_cost_data.ITEM_TYPE_CODE.delete;
565 c_cost_data.INVENTORY_ITEM_ID.delete;
566 c_cost_data.COMPONENT_CODE.delete;
567 c_cost_data.ato_line_id.delete;
568 c_cost_data.ord_quantity.delete;
569
570 l_ctr := 0;
571
572 fetch c_measures bulk collect into
573 c_cost_data.ord_level_value,
574 c_cost_data.booked_date,
575 c_cost_data.cos_level_value,
576 c_cost_data.cost_value,
577 c_cost_data.unit_list_price,
578 c_cost_data.TOP_MODEL_LINE_ID,
579 c_cost_data.LINK_TO_LINE_ID,
580 c_cost_data.ITEM_TYPE_CODE,
581 c_cost_data.INVENTORY_ITEM_ID,
582 c_cost_data.COMPONENT_CODE,
583 c_cost_data.ato_line_id,
584 c_cost_data.ord_quantity
585 limit l_rows;
586
587 exit when c_cost_data.ord_level_value.count=0;
588 fnd_file.put_line(fnd_file.log,
589 'Iteration...records to process:'||c_cost_data.ord_level_value.count);
590
591 ----- Insert Dimension ----
592 FOR I IN 1..c_cost_data.ord_level_value.count LOOP
593 if first_time = 1 then
594 begin
595 select 1 into l_dummy
596 from qpr_dimension_values
597 where dim_code = 'COS'
598 and hierarchy_code = 'COSTING'
599 and level1_value = c_cost_data.cos_level_value(I)
600 and instance_id = p_instance_id;
601 fnd_file.put_line(fnd_file.log,'Cost dim present');
602 exception
603 WHEN NO_DATA_FOUND THEN
604 begin
605 fnd_file.put_line(fnd_file.log,
606 'Inserting Cost dim :' || c_cost_data.cos_level_value(I));
607 INSERT INTO
608 qpr_dimension_values(instance_id,
609 dim_value_id,
610 dim_code,
611 hierarchy_code,
612 level1_value,
613 level1_desc,
614 level2_value,
615 level2_desc,
616 level3_value,
617 level3_desc,
618 CREATION_DATE,
619 CREATED_BY,
620 LAST_UPDATE_DATE,
621 LAST_UPDATED_BY,
622 LAST_UPDATE_LOGIN,
623 REQUEST_ID) values
624 (p_instance_id,
625 qpr_dimension_values_s.nextval,
626 'COS',
627 'COSTING',
628 c_cost_data.cos_level_value(I),
629 c_cost_data.cos_level_value(I),
630 to_char(qpr_sr_util.get_null_pk),
631 qpr_sr_util.get_cost_type_desc,
632 to_char(qpr_sr_util.get_all_cos_pk),
633 qpr_sr_util.get_all_cos_desc,
634 d_sysdate,
635 l_user_id,
636 d_sysdate,
637 l_user_id,
638 l_login_id,
639 l_request_id);
640 exception
641 when others then
642 retcode := 2;
643 errbuf := FND_MESSAGE.GET;
644 fnd_file.put_line(fnd_file.log,
645 dbms_utility.format_error_backtrace);
646 return;
647 end;
648 end;
649 first_time :=0;
650 end if;
651 exit when first_time=0;
652 end loop;
653
654 fnd_file.put_line(fnd_file.log, 'Inserting fact records in staging table');
655 forall i in c_cost_data.ord_level_value.first..
656 c_cost_data.ord_level_value.last
657 insert into qpr_plan_measures(price_plan_data_id,
658 price_plan_id,
659 price_plan_meas_grp_id,
660 price_plan_meas_grp_name,
661 date_attribute,
662 attribute_1,
663 attribute_2,
664 attribute_3,
665 attribute_4,
666 attribute_5,
667 attribute_6,
668 attribute_7,
669 attribute_8,
670 attribute_9,
671 attribute_10,
672 attribute_11,
673 CREATION_DATE,
674 CREATED_BY,
675 LAST_UPDATE_DATE,
676 LAST_UPDATED_BY,
677 LAST_UPDATE_LOGIN,
678 REQUEST_ID)
679 values(qpr_plan_measures_s.nextval,
680 l_request_id,
681 999, 'COST',
682 c_cost_data.booked_date(i),
683 c_cost_data.INVENTORY_ITEM_ID(i),
684 c_cost_data.cos_level_value(i),
685 c_cost_data.ord_level_value(i),
686 c_cost_data.TOP_MODEL_LINE_ID(i),
687 c_cost_data.LINK_TO_LINE_ID(i),
688 c_cost_data.ITEM_TYPE_CODE(i),
689 c_cost_data.COMPONENT_CODE(i),
690 c_cost_data.ato_line_id(i),
691 c_cost_data.cost_value(i),
692 c_cost_data.unit_list_price(i),
693 c_cost_data.ord_quantity(i),
694 d_sysdate,
695 l_user_id,
696 d_sysdate,
697 l_user_id,
698 l_login_id,
699 l_request_id);
700 end loop;
701 commit;
702 close c_measures;
703 fnd_file.put_line(fnd_file.log, 'Staging complete...');
704
705 fnd_file.put_line(fnd_file.log, 'Calculate costs for model/kit items...');
706 l_cost_alloc_perc := to_number(nvl(qpr_sr_util.read_parameter(
707 'QPR_MODEL_COST_MRG_PERC'),0))/100;
708 handle_kit_items;
709 handle_ato_items;
710 handle_pto_items;
711
712 commit;
713 t_ord_meas.delete;
714
715 fnd_file.put_line(fnd_file.log, 'Deleting staging table records ..');
716 begin
717 delete qpr_plan_measures temp
718 where temp.price_plan_meas_grp_id=999
719 and temp.price_plan_id = l_request_id;
720 fnd_file.put_line(fnd_file.log, 'Deleted '||sql%rowcount ||'records');
721 end;
722
723 commit;
724 end if;
725 exception
726 when OTHERS then
727 retcode := 2;
728 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
729 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
730 end collect_cost;
731
732
733 procedure collect_offadj(
734 errbuf OUT NOCOPY VARCHAR2,
735 retcode OUT NOCOPY VARCHAR2,
736 p_TRX_TYP_NAME VARCHAR2,
737 -- P_TRX_TYPE VARCHAR2,
738 P_h_reason_code VARCHAR2,
739 P_l_reason_code VARCHAR2,
740 p_from_trx_date VARCHAR2,
741 p_to_trx_date VARCHAR2,
742 p_from_date VARCHAR2,
743 p_to_date VARCHAR2,
744 p_instance_id number ) is
745 l_sql varchar2(500);
746 c_measures QPRREFCUR;
747 c_offadj_data OFFADJ_REC_TYPE;
748 l_rows natural :=1000;
749 --l_dummy number;
750 --l_next_seq number;
751 --first_time number:=1;
752 l_iterator number := 0;
753 date_from date;
754 date_to date;
755 date_to_trx date;
756 date_from_trx date;
757 -- who columns ---
758 l_user_id number := FND_GLOBAL.USER_ID;
759 d_sysdate date := sysdate;
760 l_login_id number := FND_GLOBAL.LOGIN_ID;
761 l_request_id number := FND_GLOBAL.conc_request_id;
762
763 begin
764
765 date_from := fnd_date.canonical_to_date(nvl(p_from_date, p_from_trx_date));
766 date_to := fnd_date.canonical_to_date(nvl(p_to_date, p_to_trx_date));
767 date_from_trx := fnd_date.canonical_to_date(p_from_trx_date);
768 date_to_trx := fnd_date.canonical_to_date(p_to_trx_date);
769
770 ----- Collect Data ---
771 l_sql:= ' SELECT '||
772 ' CUSTOMER_TRX_LINE_ID, TRX_TYP_NAME, SOLD_TO_CUSTOMER_ID,'||
773 ' ORG_ID, INVENTORY_ITEM_ID, H_REASON_CODE, L_REASON_CODE, ' ||
774 ' TRX_NUMBER || ''-'' || LINE_NUMBER,' ||
775 ' CUST_TRX_TYPE_ID, QUANTITY_ORDERED,'||
776 ' UNIT_SELLING_PRICE, EXTENDED_AMOUNT, TRX_DATE '||
777 ' from qpr_sr_offinv_ar_v'||qpr_sr_util.get_dblink(p_instance_id)||
778 ' where TRX_TYP_NAME = '||''''||p_trx_typ_name||''''||' and'||
779 -- ' TRX_TYPE = '||''''||p_trx_type||''''||' and'||
780 ' TRX_DATE between '||''''||date_from_trx||''''||' and '||''''||date_to_trx||'''';
781 if p_h_reason_code is not null then
782 l_sql:= l_sql|| ' and H_REASON_CODE = '||''''||p_h_reason_code||'''';
783 end if;
784 if p_l_reason_code is not null then
785 l_sql:= l_sql|| ' and L_REASON_CODE = '||''''||p_l_reason_code||'''';
786 end if;
787 fnd_file.put_line(fnd_file.log,'SQL: '||l_sql);
788
789 open c_measures for l_sql;
790 loop
791 c_offadj_data.level1_value.delete;
792 c_offadj_data.level2_value.delete;
793 c_offadj_data.level3_value.delete;
794 c_offadj_data.level4_value.delete;
795 c_offadj_data.level5_value.delete;
796 c_offadj_data.level6_value.delete;
797 c_offadj_data.level7_value.delete;
798 c_offadj_data.level8_value.delete;
799 c_offadj_data.level9_value.delete;
800 c_offadj_data.measure1_value.delete;
801 c_offadj_data.measure2_value.delete;
802 c_offadj_data.measure3_value.delete;
803 c_offadj_data.date_value.delete;
804
805 fetch c_measures bulk collect into
806 c_offadj_data.level1_value,
807 c_offadj_data.level2_value,
808 c_offadj_data.level3_value,
809 c_offadj_data.level4_value,
810 c_offadj_data.level5_value,
811 c_offadj_data.level6_value,
812 c_offadj_data.level7_value,
813 c_offadj_data.level8_value,
814 c_offadj_data.level9_value,
815 c_offadj_data.measure1_value,
816 c_offadj_data.measure2_value,
817 c_offadj_data.measure3_value,
818 c_offadj_data.date_value
819 limit l_rows;
820
821 exit when c_offadj_data.level1_value.count = 0;
822 l_iterator := l_iterator + 1;
823 fnd_file.put_line(fnd_file.log,'Iteration...' || l_iterator);
824 ----- Insert Dimension ----
825 fnd_file.put_line(fnd_file.log,
826 'Deleting overlapping dimension values...');
827 FORALL I IN 1..c_offadj_data.level1_value.count
828 delete qpr_dimension_values
829 where dim_code = 'OAD'
830 and hierarchy_code = 'OFFINVADJ'
831 and level1_value = c_offadj_data.level1_value(I)
832 and instance_id = p_instance_id;
833
834 fnd_file.put_line(fnd_file.log,
835 'Inserting Offinvoice Adjustment dimension values...');
836 FORALL I IN 1..c_offadj_data.level1_value.count
837 INSERT INTO qpr_dimension_values(instance_id,
838 dim_value_id,
839 dim_code,
840 hierarchy_code,
841 level1_value,
842 level1_desc,
843 level1_attribute1,
844 level1_attribute2,
845 level1_attribute3,
846 level1_attribute4,
847 level1_attribute5,
848 level2_value,
849 level2_desc,
850 level3_value,
851 level3_desc,
852 level4_value,
853 level4_desc,
854 level5_value,
855 level5_desc,
856 CREATION_DATE,
857 CREATED_BY,
858 LAST_UPDATE_DATE,
859 LAST_UPDATED_BY,
860 LAST_UPDATE_LOGIN,
861 REQUEST_ID) values
862 (p_instance_id,
863 qpr_dimension_values_s.nextval,
864 'OAD',
865 'OFFINVADJ',
866 c_offadj_data.level1_value(I), --level1 val
867 c_offadj_data.level2_value(I)||'-'||c_offadj_data.level8_value(I), --level1 desc
868 c_offadj_data.level2_value(I), --attr1:trx name
869 nvl(c_offadj_data.level7_value(I),
870 c_offadj_data.level6_value(I)), --attr2:reason code
871 null,null,null, -- attr3, attr4, attr5
872 'Rebate-'||c_offadj_data.level9_value(I), --level2 val
873 c_offadj_data.level2_value(I), --level2 desc
874 qpr_sr_util.get_oad_ar_cm_type_pk, --level3 val
875 qpr_sr_util.get_oad_ar_cm_type_desc, --level3 desc
876 qpr_sr_util.get_oad_ar_group_pk, --level4 val
877 qpr_sr_util.get_oad_ar_group_desc, --level4 desc
878 to_char(qpr_sr_util.get_all_oad_pk), --level5 (all) val
879 qpr_sr_util.get_all_oad_desc, --level5 (all) desc
880 d_sysdate,
881 l_user_id,
882 d_sysdate,
883 l_user_id,
884 l_login_id,
885 l_request_id);
886
887
888
889 ----- Update Measure data ---
890 fnd_file.put_line(fnd_file.log,'Staging Measure data...');
891 FORALL I IN
892 c_offadj_data.level1_value.FIRST..c_offadj_data.level1_value.LAST
893 insert into QPR_MEASURE_DATA(MEASURE_VALUE_ID,
894 INSTANCE_ID,
895 MEASURE_TYPE_CODE,
896 ADJ_LEVEL_VALUE,
897 TIME_LEVEL_VALUE,
898 CUS_LEVEL_VALUE,
899 PRD_LEVEL_VALUE,
900 ORG_LEVEL_VALUE,
901 MEASURE1_NUMBER ,
902 MEASURE2_NUMBER ,
903 MEASURE3_NUMBER ,
904 CREATION_DATE ,
905 CREATED_BY ,
906 LAST_UPDATE_DATE ,
907 LAST_UPDATED_BY ,
908 LAST_UPDATE_LOGIN ,
909 REQUEST_ID)
910 values (QPR_MEASURE_DATA_S.nextval,
911 -999,
912 'OFFADJDATA',
913 c_offadj_data.level1_value(I),
914 c_offadj_data.date_value(I),
915 c_offadj_data.level3_value(I),
916 c_offadj_data.level5_value(I),
917 c_offadj_data.level4_value(I),
918 c_offadj_data.measure1_value(I),
919 c_offadj_data.measure2_value(I),
920 c_offadj_data.measure3_value(I),
921 d_sysdate,
922 l_user_id,
923 d_sysdate,
924 l_user_id,
925 l_login_id,
926 l_request_id);
927 fnd_file.put_line(fnd_file.log,'No of rows processed: '||sql%rowcount);
928 commit;
929 end loop;
930
931 allocate_offinvoice_adj(errbuf, retcode,
932 date_from,
933 date_to,
934 p_instance_id );
935
936 exception
937 WHEN NO_DATA_FOUND THEN
938 retcode := 2;
939 errbuf := FND_MESSAGE.GET;
940 fnd_file.put_line(fnd_file.log,'Unexpected error '||substr(sqlerrm,1200));
941 end;
942
943 procedure allocate_offinvoice_adj(
944 errbuf OUT NOCOPY VARCHAR2,
945 retcode OUT NOCOPY VARCHAR2,
946 p_from_date date,
947 p_to_date date,
948 p_instance_id number ) is
949 cursor c_offadj is
950 select temp.ADJ_LEVEL_VALUE,
951 om_alloc.ord_level_value,
952 om_alloc.prd_level_value,
953 om_alloc.geo_level_value,
954 om_alloc.cus_level_value,
955 om_alloc.org_level_value,
956 om_alloc.rep_level_value,
957 om_alloc.chn_level_value,
958 om_alloc.vlb_level_value,
959 om_alloc.dsb_level_value,
960 om_alloc.time_level_value,
961 temp.MEASURE1_NUMBER, --qty_ordered
962 temp.MEASURE2_NUMBER, --unit_selling_price
963 temp.MEASURE3_NUMBER, --extended_amount
964 (select sum(om.measure2_number) from qpr_measure_data om where om.instance_id=p_instance_id
965 and om.measure_type_code = 'SALESDATA'
966 and om.time_level_value between p_from_date and p_to_date
967 and om.cus_level_value = temp.cus_level_value
968 and om.prd_level_value = nvl(temp.prd_level_value, om.prd_level_value)
969 and om.org_level_value = nvl(temp.org_level_value, om.org_level_value)
970 ) as total_amount,
971 om_alloc.measure1_number,
972 om_alloc.measure2_number
973 from qpr_measure_data temp, qpr_measure_data om_alloc
974 where temp.instance_id=-999
975 and temp.measure_type_code = 'OFFADJDATA'
976 and temp.request_id = fnd_global.conc_request_id
977 and om_alloc.instance_id=p_instance_id
978 and om_alloc.measure_type_code = 'SALESDATA'
979 and om_alloc.time_level_value between p_from_date and p_to_date
980 and om_alloc.cus_level_value = temp.cus_level_value
981 and om_alloc.prd_level_value = nvl(temp.prd_level_value, om_alloc.prd_level_value)
982 and om_alloc.org_level_value = nvl(temp.org_level_value, om_alloc.org_level_value);
983 c_offadj_data OFFADJ_REC_TYPE;
984 l_rows natural :=1000;
985
986 -- who columns ---
987 l_user_id number := FND_GLOBAL.USER_ID;
988 d_sysdate date := sysdate;
989 l_login_id number:= FND_GLOBAL.LOGIN_ID;
990 l_request_id number:= FND_GLOBAL.conc_request_id;
991
992 begin
993 fnd_file.put_line(fnd_file.log,'Allocation ');
994
995 open c_offadj ;
996 loop
997 c_offadj_data.level1_value.delete;
998 c_offadj_data.level2_value.delete;
999 c_offadj_data.level3_value.delete;
1000 c_offadj_data.level4_value.delete;
1001 c_offadj_data.level5_value.delete;
1002 c_offadj_data.level6_value.delete;
1003 c_offadj_data.level7_value.delete;
1004 c_offadj_data.level8_value.delete;
1005 c_offadj_data.level9_value.delete;
1006 c_offadj_data.level10_value.delete;
1007 c_offadj_data.date_value.delete;
1008 c_offadj_data.measure1_value.delete;
1009 c_offadj_data.measure2_value.delete;
1010 c_offadj_data.measure3_value.delete;
1011 c_offadj_data.measure4_value.delete;
1012 c_offadj_data.measure5_value.delete;
1013 c_offadj_data.measure6_value.delete;
1014
1015 fetch c_offadj bulk collect into
1016 c_offadj_data.level1_value,
1017 c_offadj_data.level2_value,
1018 c_offadj_data.level3_value,
1019 c_offadj_data.level4_value,
1020 c_offadj_data.level5_value,
1021 c_offadj_data.level6_value,
1022 c_offadj_data.level7_value,
1023 c_offadj_data.level8_value,
1024 c_offadj_data.level9_value,
1025 c_offadj_data.level10_value,
1026 c_offadj_data.date_value,
1027 c_offadj_data.measure1_value,
1028 c_offadj_data.measure2_value,
1029 c_offadj_data.measure3_value,
1030 c_offadj_data.measure4_value,
1031 c_offadj_data.measure5_value,
1032 c_offadj_data.measure6_value
1033 limit l_rows;
1034 fnd_file.put_line(fnd_file.log,
1035 'Number of rows fetched- '||c_offadj_data.level1_value.count);
1036 if c_offadj_data.level1_value.count>0 then
1037 fnd_file.put_line(fnd_file.log,'Deleting measures if exists ');
1038 begin
1039 forall I in 1..c_offadj_data.level1_value.count
1040 delete qpr_measure_data
1041 where instance_id=p_instance_id
1042 and measure_type_code= 'OFFADJDATA'
1043 and ord_level_value=c_offadj_data.level2_value(I)
1044 and adj_level_value=c_offadj_data.level1_value(I);
1045 fnd_file.put_line(fnd_file.log, 'Deleted '||
1046 sql%rowcount ||' records');
1047 exception
1048 when others then
1049 null;
1050 end;
1051 begin
1052 fnd_file.put_line(fnd_file.log,'Inserting measures ');
1053 forall I in 1..c_offadj_data.level1_value.count
1054 insert into QPR_MEASURE_DATA(
1055 MEASURE_VALUE_ID,
1056 MEASURE_TYPE_CODE,
1057 INSTANCE_ID,
1058 ORD_LEVEL_VALUE,
1059 PRD_LEVEL_VALUE,
1060 GEO_LEVEL_VALUE,
1061 CUS_LEVEL_VALUE,
1062 ORG_LEVEL_VALUE,
1063 REP_LEVEL_VALUE,
1064 CHN_LEVEL_VALUE,
1065 ADJ_LEVEL_VALUE,
1066 TIME_LEVEL_VALUE,
1067 MEASURE1_NUMBER ,
1068 CREATION_DATE ,
1069 CREATED_BY ,
1070 LAST_UPDATE_DATE ,
1071 LAST_UPDATED_BY ,
1072 LAST_UPDATE_LOGIN ,
1073 REQUEST_ID) values
1074 (QPR_MEASURE_DATA_S.nextval,
1075 'OFFADJDATA',
1076 p_instance_id,
1077 c_offadj_data.level2_value(I),
1078 c_offadj_data.level3_value(I),
1079 c_offadj_data.level4_value(I),
1080 c_offadj_data.level5_value(I),
1081 c_offadj_data.level6_value(I),
1082 c_offadj_data.level7_value(I),
1083 c_offadj_data.level8_value(I),
1084 c_offadj_data.level1_value(I),
1085 c_offadj_data.date_value(I),
1086 -1 * decode(nvl(c_offadj_data.measure4_value(I), 0), 0, 0,
1087 c_offadj_data.measure3_value(I)*
1088 c_offadj_data.measure6_value(I)/c_offadj_data.measure4_value(I)),
1089 -- When total gross revenue is null or 0, the off adjustment amount is 0, otherwise the allocated extended_amount.
1090 -- Allocation of extended amount: when the quantity from the AR table is not null, the extended amount is
1091 -- This extended amount is multiplied with the gross revenue
1092 -- of the order line and divided with the total gross amount.
1093 d_sysdate,
1094 l_user_id,
1095 d_sysdate,
1096 l_user_id,
1097 l_login_id,
1098 l_request_id);
1099 fnd_file.put_line(fnd_file.log, 'Inserted '||
1100 sql%rowcount ||' records');
1101 exception
1102 when others then
1103 errbuf := substr(SQLERRM,1,150);
1104 retcode := -1;
1105 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
1106 end;
1107 end if;
1108 commit;
1109
1110 exit when c_offadj%NOTFOUND;
1111
1112 end loop; --c_offadj
1113
1114 close c_offadj;
1115 begin
1116 delete qpr_measure_data temp
1117 where temp.instance_id=-999
1118 and temp.measure_type_code = 'OFFADJDATA'
1119 and temp.request_id = l_request_id;
1120 fnd_file.put_line(fnd_file.log, 'Deleted '|| sql%rowcount ||' temp records');
1121 end;
1122 commit;
1123 exception
1124 WHEN NO_DATA_FOUND THEN
1125 retcode := 2;
1126 errbuf := FND_MESSAGE.GET;
1127 fnd_file.put_line(fnd_file.log,'Unexpected error '||substr(sqlerrm,1200));
1128 End;
1129
1130 procedure consolidate_upd_sales_meas(
1131 errbuf OUT NOCOPY VARCHAR2,
1132 retcode OUT NOCOPY VARCHAR2,
1133 p_instance_id in number,
1134 p_from_date in varchar2,
1135 p_to_date in varchar2) is
1136
1137 l_offadj_rev number;
1138 l_margin_perc number;
1139 l_pocket_price number;
1140 l_pocket_price_rev number;
1141 lctr pls_integer;
1142 lrows number := 1000;
1143 date_from date := fnd_date.canonical_to_date(p_from_date);
1144 date_to date := fnd_date.canonical_to_date(p_to_date);
1145
1146 type sales_rec is record(MEASURE_VALUE_ID num_type,
1147 MEASURE1 num_type,
1148 MEASURE2 num_type,
1149 MEASURE3 num_type,
1150 MEASURE4 num_type,
1151 MEASURE5 num_type,
1152 MEASURE6 num_type);
1153
1154 cursor c_get_margin_det is
1155 select md.MEASURE_VALUE_ID,
1156 nvl(md.measure1_number,0) as order_qty,
1157 nvl(md.measure3_number,0) * nvl(md.measure1_number,0) as listpricerev,
1158 nvl(md.measure15_number,0) as cost,
1159 nvl(md2.offadj_amt, 0) as offadj_amt,
1160 nvl(md.measure2_number, 0 ) as gross_revenue,
1161 nvl(md1.freight_amount, 0) as freight_amount
1162 from qpr_measure_data md,
1163 (select sum(m1.measure1_number) freight_amount, m1.ord_level_Value,
1164 m1.time_level_Value
1165 from qpr_measure_data m1
1166 where m1.instance_id = p_instance_id
1167 and m1.measure_type_code = 'ADJUSTMENT'
1168 and m1.measure1_char = 'FREIGHT_CHARGE'
1169 and m1.time_level_value between date_from and date_to
1170 group by m1.ord_level_value, m1.time_level_value) md1,
1171 (select sum(m2.measure1_number) offadj_amt, m2.ord_level_value,
1172 m2.time_level_value
1173 from qpr_measure_data m2
1174 where m2.measure_type_code = 'OFFADJDATA'
1175 and m2.instance_id = p_instance_id
1176 and m2.TIME_LEVEL_VALUE between date_from and date_to
1177 group by m2.ORD_LEVEL_VALUE, m2.TIME_LEVEL_VALUE) md2
1178 where md.measure_type_code = 'SALESDATA'
1179 and md.instance_id = p_instance_id
1180 and md.time_level_value between date_from and date_to
1181 and md.ord_level_value = md1.ord_level_value(+)
1182 and md.time_level_value = md1.time_level_Value(+)
1183 and md.ord_level_value = md2.ord_level_value(+)
1184 and md.time_level_value = md2.time_level_Value(+);
1185
1186 rec_mrg_det sales_rec;
1187 rec_upd_det sales_rec;
1188 begin
1189 fnd_file.put_line(fnd_file.log,
1190 'Consolidating offinvoice adjustments & updating relevant sales measures ...');
1191 open c_get_margin_det;
1192 loop
1193 fetch c_get_margin_det bulk collect into rec_mrg_det limit lrows;
1194 exit when rec_mrg_det.measure_value_id.count = 0;
1195 lctr := 0;
1196 -- measure1 = order qty, measure2 =listprice rev, measure3 = cost
1197 -- measure4 = total off adj, measure5 = gross_rev
1198 -- measure6 = freight_amount
1199 for i in rec_mrg_det.measure_value_id.first..
1200 rec_mrg_det.measure_value_id.last loop
1201 l_offadj_rev := nvl(rec_mrg_det.measure4(i), 0);
1202 l_pocket_price_rev := rec_mrg_det.measure5(i) - (-1*l_offadj_rev);
1203 if rec_mrg_det.measure1(i) <> 0 then
1204 l_pocket_price := l_pocket_price_rev/rec_mrg_det.measure1(i);
1205 else
1206 l_pocket_price := 0;
1207 end if;
1208 if rec_mrg_det.measure2(i) <> 0 then
1209 l_margin_perc := 100 * (l_pocket_price_rev -
1210 (-1 * rec_mrg_det.measure3(i)))/rec_mrg_det.measure2(i);
1211 else
1212 l_margin_perc := 100;
1213 end if;
1214 rec_upd_det.measure_value_id(lctr) := rec_mrg_det.measure_value_id(i);
1215 rec_upd_det.measure1(lctr) := l_pocket_price;
1216 rec_upd_det.measure2(lctr) := l_offadj_rev;
1217 rec_upd_det.measure3(lctr) := l_margin_perc;
1218 rec_upd_det.measure4(lctr) := rec_mrg_det.measure6(i);
1219 lctr := lctr + 1;
1220 end loop;
1221
1222 fnd_file.put_line(fnd_file.log,
1223 'Updated Records=' || rec_upd_det.measure_value_id.count);
1224 forall i in rec_upd_det.measure_value_id.first..rec_upd_det.measure_value_id.last
1225 update qpr_measure_data
1226 set measure5_number = rec_upd_det.measure1(i),
1227 measure14_number = rec_upd_det.measure2(i),
1228 measure17_number = rec_upd_det.measure3(i),
1229 measure20_number = rec_upd_det.measure4(i),
1230 last_update_date = sysdate,
1231 last_updated_by = fnd_global.user_id,
1232 last_update_login = fnd_global.login_id,
1233 request_id = fnd_global.conc_request_id
1234 where measure_value_id = rec_upd_det.measure_value_id(i);
1235
1236 rec_upd_det.measure_value_id.delete;
1237 rec_upd_det.measure1.delete;
1238 rec_upd_det.measure2.delete;
1239 rec_upd_det.measure3.delete;
1240 rec_upd_det.measure4.delete;
1241
1242 rec_mrg_det.measure_value_id.delete;
1243 rec_mrg_det.measure1.delete;
1244 rec_mrg_det.measure2.delete;
1245 rec_mrg_det.measure3.delete;
1246 rec_mrg_det.measure4.delete;
1247 rec_mrg_det.measure5.delete;
1248 rec_mrg_det.measure6.delete;
1249 end loop;
1250
1251 close c_get_margin_det;
1252 commit;
1253 exception
1254 when OTHERS then
1255 retcode := 2;
1256 errbuf := 'ERROR: ' || substr(SQLERRM,1,1000);
1257 fnd_file.put_line(fnd_file.log,
1258 'Unable to update sales measures');
1259 fnd_file.put_line(fnd_file.log, 'ERROR: ' || substr(SQLERRM,1,1000));
1260 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1261 rollback;
1262 end consolidate_upd_sales_meas;
1263
1264 procedure update_pr_segment(
1265 errbuf OUT NOCOPY VARCHAR2,
1266 retcode OUT NOCOPY VARCHAR2,
1267 p_instance_id in number,
1268 p_from_date in varchar2,
1269 p_to_date in varchar2) is
1270
1271 cursor c_measures(p_date_from date, p_date_to date) is
1272 select measure_value_id,cus_level_value, geo_level_value, org_level_value,
1273 prd_level_value, chn_level_value ,rep_level_value , ord_level_value,
1274 time_level_value
1275 from qpr_measure_data
1276 where instance_id = p_instance_id
1277 and measure_type_code = 'SALESDATA'
1278 and time_level_value between p_date_from and p_date_to
1279 order by cus_level_value, geo_level_value, org_level_value,
1280 prd_level_value, chn_level_value ,rep_level_value;
1281
1282
1283 t_meas_val_id num_type;
1284 t_cus char240_type;
1285 t_geo char240_type;
1286 t_org char240_type;
1287 t_prd char240_type;
1288 t_chn char240_type;
1289 t_rep char240_type;
1290 t_ord char240_type;
1291 t_psg_val num_type;
1292 t_time date_type;
1293
1294 l_rows number := 1000;
1295 l_prev_cus varchar2(240);
1296 l_prev_geo varchar2(240);
1297 l_prev_org varchar2(240);
1298 l_prev_prd varchar2(240);
1299 l_prev_chn varchar2(240);
1300 l_prev_rep varchar2(240);
1301 l_pr_segment_id number;
1302 l_pr_segment_desc varchar2(240);
1303 l_pol_importance varchar2(30);
1304
1305 l_dummy number;
1306 date_from date;
1307 date_to date;
1308
1309 begin
1310 date_from := fnd_date.canonical_to_date(p_from_date);
1311 date_to := fnd_date.canonical_to_date(p_to_date);
1312
1313 open c_measures(date_from, date_to);
1314 loop
1315 fetch c_measures bulk collect into t_meas_val_id, t_cus, t_geo, t_org,
1316 t_prd, t_chn, t_rep, t_ord, t_time limit l_rows;
1317 exit when t_meas_val_id.count = 0;
1318 fnd_file.put_line(fnd_file.log, 'count: ' || t_meas_val_id.count);
1319 for i in t_meas_val_id.first..t_meas_val_id.last loop
1320
1321 if l_prev_cus = t_cus(i)
1322 and l_prev_geo = t_geo(i)
1323 and l_prev_org = t_org(i)
1324 and l_prev_prd = t_prd(i)
1325 and l_prev_chn = t_chn(i)
1326 and l_prev_rep = t_rep(i) then
1327 t_psg_val(i) := l_pr_segment_id;
1328 else
1329 qpr_policy_eval.get_pricing_segment_id(p_instance_id,
1330 null,
1331 null,
1332 t_prd(i),
1333 t_geo(i),
1334 t_cus(i),
1335 t_org(i),
1336 t_rep(i),
1337 t_chn(i),
1338 null,
1339 l_pr_segment_id,
1340 l_pol_importance);
1341 t_psg_val(i) := l_pr_segment_id;
1342 /*
1343 if l_pr_segment_id is not null then
1344 begin
1345 select 1 into l_dummy
1346 from qpr_dimension_values
1347 where dim_code = 'PSG'
1348 and hierarchy_code = 'PR_SEGMENT'
1349 and level1_value = l_pr_segment_id
1350 and instance_id = p_instance_id
1351 and rownum < 2;
1352 exception
1353 WHEN NO_DATA_FOUND THEN
1354 fnd_file.put_line(fnd_file.log,'inserting psg:' || l_pr_segment_id);
1355 begin
1356 select name into l_pr_segment_desc
1357 from qpr_pr_segments_vl
1358 where pr_segment_id = l_pr_segment_id
1359 and rownum < 2;
1360 exception
1361 when no_data_found then
1362 l_pr_segment_id := null;
1363 l_pr_segment_desc:= null;
1364 end;
1365 --TODO: All level to be added
1366 insert into qpr_dimension_values(DIM_VALUE_ID,
1367 INSTANCE_ID,
1368 DIM_CODE,
1369 HIERARCHY_CODE,
1370 LEVEL1_VALUE,
1371 LEVEL1_DESC,
1372 CREATION_DATE,
1373 CREATED_BY,
1374 LAST_UPDATE_DATE,
1375 LAST_UPDATED_BY,
1376 LAST_UPDATE_LOGIN)
1377 values(qpr_dimension_values_s.nextval,
1378 p_instance_id,
1379 'PSG',
1380 'PR_SEGMENT',
1381 l_pr_segment_id,
1382 l_pr_segment_desc,
1383 sysdate,
1384 fnd_global.user_id,
1385 sysdate,
1386 fnd_global.user_id,
1387 fnd_global.login_id);
1388 end;
1389 end if; */
1390
1391 end if;
1392
1393 l_prev_cus := t_cus(i);
1394 l_prev_geo := t_geo(i);
1395 l_prev_org := t_org(i);
1396 l_prev_prd := t_prd(i);
1397 l_prev_chn := t_chn(i);
1398 l_prev_rep := t_rep(i);
1399 end loop;
1400
1401 fnd_file.put_line(fnd_file.log,
1402 'updating pricing segments for measuretype:salesdata');
1403 forall i in t_meas_val_id.first..t_meas_val_id.last
1404 update qpr_measure_data set psg_level_value = t_psg_val(i),
1405 last_update_date = sysdate,
1406 last_updated_by = fnd_global.user_id,
1407 last_update_login = fnd_global.login_id,
1408 request_id = fnd_global.conc_request_id
1409 where measure_value_id = t_meas_val_id(i);
1410
1411 fnd_file.put_line(fnd_file.log,'Updating measuretypes:ADJUSTMENT,OFFADJDATA');
1412 forall i in t_meas_val_id.first..t_meas_val_id.last
1413 update qpr_measure_data set psg_level_value = t_psg_val(i),
1414 last_update_date = sysdate,
1415 last_updated_by = fnd_global.user_id,
1416 last_update_login = fnd_global.login_id,
1417 request_id = fnd_global.conc_request_id
1418 where instance_id = p_instance_id
1419 and measure_type_code in ('ADJUSTMENT', 'OFFADJDATA')
1420 and ord_level_value = t_ord(i)
1421 and time_level_value = t_time(i);
1422
1423 t_meas_val_id.delete;
1424 t_geo.delete;
1425 t_org.delete;
1426 t_prd.delete;
1427 t_chn.delete;
1428 t_rep.delete;
1429 t_psg_val.delete;
1430 t_ord.delete;
1431 t_time.delete;
1432 end loop;
1433 close c_measures;
1434
1435 exception
1436 when others then
1437 errbuf := sqlerrm;
1438 retcode := 2;
1439 fnd_file.put_line(fnd_file.log, 'Unable to update pricing segment');
1440 fnd_file.put_line(fnd_file.log, dbms_utility.format_error_backtrace);
1441 end update_pr_segment;
1442
1443
1444 END QPR_SPECIAL_ETL;