[Home] [Help]
PACKAGE BODY: APPS.QPR_DEAL_ETL
Source
1 PACKAGE BODY QPR_DEAL_ETL AS
2 /* $Header: QPRUDPRB.pls 120.34 2008/06/04 12:54:52 bhuchand ship $ */
3 l_header number := 0;
4 l_top_group number := 0;
5 l_cmp_group number := 0;
6 l_svc_group number := 0;
7
8 l_deal_instance number;
9
10 TYPE qpr_pn_policy_type is record(POLICY_ID num_type,
11 POLICY_LINE_ID num_type,
12 PN_PRICE_ID num_type,
13 POLICY_AMOUNT num_type,
14 POLICY_PRICE num_type
15 );
16
17 procedure log_debug(text varchar2) is
18 begin
19 fnd_file.put_line( fnd_file.log, text);
20
21 if (g_origin = 660 or g_origin = 697) then
22 qpr_deal_pvt.debug_ext_log(text, g_origin);
23 end if;
24 end;
25
26 procedure insert_price_adj_recs(
27 p_response_hdr_id in number,
28 p_pn_line_id in number,
29 p_src_ref_line_id in number,
30 p_src_ref_hdr_id in number,
31 p_src_id in number,
32 p_top_mdl_src_line_id in number default null,
33 p_mdl_qty in number default null
34 ) is
35 cursor c_adj is
36 select EROSION_TYPE, EROSION_NAME, EROSION_DESC, EROSION_PER_UNIT,
37 erosion_amount
38 from qpr_pn_int_pr_adjs
39 where source_ref_line_id = p_src_ref_line_id
40 and source_ref_hdr_id = p_src_ref_hdr_id
41 and source_id = p_src_id;
42
43 cursor c_tot_adj is
44 select a.pn_line_id, 'ALL_' || erosion_type,
45 decode(erosion_type, 'ONINVOICE', qpr_sr_util.get_all_adj_pk,
46 'COST', qpr_sr_util.get_all_cos_pk,
47 'OFFINVOICE',
48 qpr_sr_util.get_all_oad_pk, qpr_sr_util.get_null_pk) erosion_name,
49 decode(erosion_type, 'ONINVOICE', qpr_sr_util.get_all_adj_desc,
50 'COST', qpr_sr_util.get_all_cos_desc,
51 'OFFINVOICE', qpr_sr_util.get_all_oad_desc, qpr_sr_util.get_null_desc)
52 erosion_desc,
53 decode(sum(l.ordered_qty), 0 ,0, sum(erosion_amount) * count(erosion_type)/sum(l.ordered_qty)),
54 sum(erosion_amount)
55 from qpr_pn_pr_details a, qpr_pn_lines l
56 where a.response_header_id = p_response_hdr_id
57 and a.pn_line_id = l.pn_line_id
58 group by a.erosion_type, a.pn_line_id;
59
60 cursor c_hdr_adj is
61 select erosion_type,
62 erosion_name, erosion_desc, null,
63 sum(erosion_amount)
64 from qpr_pn_pr_details
65 where response_header_id = p_response_hdr_id
66 group by erosion_type,erosion_name, erosion_desc;
67
68 cursor c_model_adj is
69 select
70 erosion_type, erosion_name, erosion_desc,
71 decode(nvl(p_mdl_qty,0), 0, 0, sum(a.erosion_amount)/p_mdl_qty) as unit_adj,
72 decode(nvl(p_mdl_qty,0), 0, 0, sum(a.erosion_amount)) as erosion_amount
73 from qpr_pn_pr_details a, qpr_pn_lines l
74 where l.parent_pn_line_id= p_top_mdl_src_line_id
75 and l.response_header_id = p_response_hdr_id
76 and a.pn_line_id = l.pn_line_id
77 group by erosion_type, erosion_name, erosion_desc;
78
79
80 t_er_typ char240_type;
81 t_er_name char240_type;
82 t_er_desc char240_type;
83 t_er_val num_type;
84 t_tot_er num_type;
85 t_line_id num_type;
86
87 b_insert boolean := true;
88 l_ctr number := 0;
89 l_rows number := 1000;
90 begin
91 if p_pn_line_id is null then
92 log_debug('Inserting line total adjustments per erosion type...');
93 open c_tot_adj;
94 loop
95 fetch c_tot_adj bulk collect into t_line_id, t_er_typ, t_er_name,
96 t_er_desc,t_er_val, t_tot_er limit l_rows;
97 exit when t_line_id.count = 0;
98 forall i in t_line_id.first..t_line_id.last
99 insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
100 RESPONSE_HEADER_ID,
101 PN_LINE_ID,
102 EROSION_TYPE,
103 EROSION_NAME,
104 EROSION_DESC,
105 EROSION_PER_UNIT,
106 erosion_amount,
107 CREATION_DATE,
108 CREATED_BY,
109 LAST_UPDATE_DATE,
110 LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN)
112 values(qpr_pn_pr_details_s.nextval,
113 p_response_hdr_id,
114 t_line_id(i),
115 t_er_typ(i),
116 t_er_name(i),
117 t_er_desc(i),
118 t_er_val(i),
119 t_tot_er(i),
120 SYSDATE,
121 FND_GLOBAL.USER_ID,
122 SYSDATE,
123 FND_GLOBAL.USER_ID,
124 FND_GLOBAL.CONC_LOGIN_ID);
125 t_line_id.delete;
126 t_er_typ.delete;
127 t_er_name.delete;
128 t_er_desc.delete;
129 t_er_val.delete;
130 t_tot_er.delete;
131 end loop;
132 close c_tot_adj;
133
134 log_debug('Inserting header adjustment values by Rolling up line values..');
135 open c_hdr_adj;
136 fetch c_hdr_adj bulk collect into t_er_typ, t_er_name, t_er_desc,
137 t_er_val, t_tot_er;
138 close c_hdr_adj;
139 elsif p_top_mdl_src_line_id is not null then
140 log_debug('inserting adjustments for rolled up model');
141 open c_model_adj;
142 fetch c_model_adj bulk collect into t_er_typ, t_er_name, t_er_desc,
143 t_er_val, t_tot_er;
144 close c_model_adj;
145 elsif p_src_ref_line_id is not null then
146 log_debug('inserting adjustment for quote line:' || p_src_ref_line_id);
147 open c_adj;
148 fetch c_adj bulk collect into t_er_typ, t_er_name, t_er_desc,
149 t_er_val, t_tot_er;
150 close c_adj;
151 end if;
152
153 forall i in t_er_name.first..t_er_name.last
154 insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
155 RESPONSE_HEADER_ID,
156 PN_LINE_ID,
157 EROSION_TYPE,
158 EROSION_NAME,
159 EROSION_DESC,
160 EROSION_PER_UNIT,
161 erosion_amount,
162 CREATION_DATE,
163 CREATED_BY,
164 LAST_UPDATE_DATE,
165 LAST_UPDATED_BY,
166 LAST_UPDATE_LOGIN)
167 values(qpr_pn_pr_details_s.nextval,
168 p_response_hdr_id,
169 p_pn_line_id,
170 t_er_typ(i),
171 t_er_name(i),
172 t_er_desc(i),
173 t_er_val(i),
174 t_tot_er(i),
175 SYSDATE,
176 FND_GLOBAL.USER_ID,
177 SYSDATE,
178 FND_GLOBAL.USER_ID,
179 FND_GLOBAL.CONC_LOGIN_ID);
180 log_debug('Inserted ' || sql%rowcount || ' adjustment rows in qpr_pn_pr_details');
181
182 t_er_typ.delete;
183 t_er_name.delete;
184 t_er_desc.delete;
185 t_er_val.delete;
186 t_tot_er.delete;
187 exception
188 when others then
189 log_debug(dbms_utility.format_error_backtrace);
190 raise;
191 end insert_price_adj_recs;
192
193 function insert_prices(
194 p_response_hdr_id in number,
195 p_pn_line_id in number,
196 p_src_ref_line_id in number default null,
197 p_src_ref_hdr_id in number default null,
198 p_src_id in number default null,
199 p_mdl_qty in number default null
200 ) return number is
201
202 l_tot_erosion number := 0;
203 l_unit_er number := 0;
204 l_base_price number := 0;
205 l_ord_qty number := 0;
206 l_price number :=0;
207 l_amount number := 0;
208 l_prev_price number:= 0;
209 l_prev_amount number := 0;
210 l_sql varchar2(2000);
211 l_price_perc number := 0;
212
213 c_line SYS_REFCURSOR;
214
215 cursor c_price_types is
216 select pn_pr_type_id, price_type_name, derived_from_type,
217 erosion_type, column_name
218 from qpr_pn_pr_types
219 order by sequence_no;
220
221 cursor c_hdr_pric is
222 select pr1.pn_pr_type_id, sum(pr1.amount) amount,
223 decode(sum(pr2.amount),0,0, 100 * sum(pr1.amount)/sum(pr2.amount))
224 percent_price,
225 decode(sum(l.ordered_qty), 0, 0, sum(pr1.amount)* count(l.pn_line_id)/sum(l.ordered_qty)) unit_price
226 from qpr_pn_prices pr1, qpr_pn_prices pr2, qpr_pn_lines l, qpr_pn_pr_types prt
227 where pr1.response_header_id = p_response_hdr_id
228 and pr1.response_header_id = l.response_header_id
229 and pr1.pn_line_id = l.pn_line_id
230 and pr1.response_header_id = pr2.response_header_id
231 and pr1.pn_line_id = pr2.pn_line_id
232 and pr2.pn_pr_type_id = prt.pn_pr_type_id
233 and prt.price_type_name = 'LISTPRICE'
234 group by pr1.pn_pr_type_id;
235
236 begin
237
238 if p_pn_line_id is null then
239 log_debug('Inserting header price values by rolling up line values');
240 for r_hdr_pric in c_hdr_pric loop
241 insert into qpr_pn_prices(PN_PRICE_ID,
242 RESPONSE_HEADER_ID,
243 PN_LINE_ID,
244 PN_PR_TYPE_ID,
245 UNIT_PRICE,
246 AMOUNT,
247 PERCENT_PRICE,
248 CREATION_DATE,
249 CREATED_BY,
250 LAST_UPDATE_DATE,
251 LAST_UPDATED_BY,
252 LAST_UPDATE_LOGIN)
253 values(
254 qpr_pn_prices_s.nextval,
255 p_response_hdr_id, null,
256 r_hdr_pric.pn_pr_type_id,
257 r_hdr_pric.unit_price,
258 r_hdr_pric.amount,
259 r_hdr_pric.percent_price,
260 SYSDATE,
261 FND_GLOBAL.USER_ID,
262 SYSDATE,
263 FND_GLOBAL.USER_ID,
264 FND_GLOBAL.CONC_LOGIN_ID);
265 end loop;
266 else
267 for r_pr_types in c_price_types loop
268 if r_pr_types.column_name is not null then
269 if p_mdl_qty is not null then
270 log_debug('Calculating price for rolled up model');
271 l_ord_qty := p_mdl_qty;
272 l_sql := ' select sum( ' || r_pr_types.column_name || ' * ordered_qty) '
273 || ' from qpr_pn_int_lines '
274 || ' where top_mdl_src_line_id = :1 and source_ref_hdr_id = :2 '
275 ||' and source_id = :3 and pn_req_line_status_flag = ''I'' ' ;
276 open c_line for l_sql using p_src_ref_line_id, p_src_ref_hdr_id, p_src_id;
277 fetch c_line into l_amount;
278 close c_line;
279
280 if nvl(p_mdl_qty, 0) = 0 then
281 l_base_price := 0;
282 l_price := 0;
283 l_amount := 0;
284 else
285 l_base_price := l_amount/p_mdl_qty;
286 l_price := l_base_price;
287 end if;
288 else
289 log_debug('Calculate price for quote line: ' || p_src_ref_line_id);
290
291 l_sql := 'select ' || r_pr_types.column_name || ' ,ordered_qty '
292 || ' from qpr_pn_int_lines '
293 || ' where source_ref_line_id = :1 and source_ref_hdr_id = :2 '
294 || ' and source_id = :3 and pn_req_line_status_flag = ''I'' and rownum < 2';
295
296 open c_line for l_sql using p_src_ref_line_id, p_src_ref_hdr_id, p_src_id;
297 fetch c_line into l_base_price, l_ord_qty;
298 close c_line;
299
300 l_price := l_base_price ;
301 l_amount := l_base_price * l_ord_qty ;
302 end if;
303 else
304 begin
305 select nvl(sum(erosion_amount),0), nvl(sum(erosion_per_unit), 0)
306 into l_tot_erosion,l_unit_er
307 from qpr_pn_pr_details
308 where pn_line_id = p_pn_line_id
309 and erosion_type = r_pr_types.erosion_type;
310 end;
311
312 l_price := l_prev_price - l_unit_er;
313 l_amount := l_prev_amount - l_tot_erosion;
314 end if;
315 if l_base_price = 0 then
316 l_price_perc := 100;
317 else
318 l_price_perc := l_price * 100 / l_base_price;
319 end if;
320 l_prev_price := l_price;
321 l_prev_amount := l_amount;
322
323 insert into qpr_pn_prices(PN_PRICE_ID,
324 RESPONSE_HEADER_ID,
325 PN_LINE_ID,
326 PN_PR_TYPE_ID,
327 UNIT_PRICE,
328 AMOUNT,
329 PERCENT_PRICE,
330 CREATION_DATE,
331 CREATED_BY,
332 LAST_UPDATE_DATE,
333 LAST_UPDATED_BY,
334 LAST_UPDATE_LOGIN)
335 values(
336 qpr_pn_prices_s.nextval,
337 p_response_hdr_id,
338 p_pn_line_id,
339 r_pr_types.pn_pr_type_id,
340 l_price, l_amount,l_price_perc,
341 SYSDATE,
342 FND_GLOBAL.USER_ID,
343 SYSDATE,
344 FND_GLOBAL.USER_ID,
345 FND_GLOBAL.CONC_LOGIN_ID);
346 log_debug('Inserted Price Type = ' || r_pr_types.pn_pr_type_id
347 || ' ;Unit Price = ' || l_price);
348 end loop;
349 end if;
350 return (l_base_price);
351 exception
352 when others then
353 log_debug('Failed to insert prices');
354 log_debug(dbms_utility.format_error_backtrace);
355 raise;
356 end insert_prices;
357
358 procedure insert_policy_details(
359 p_deal_date in date,
360 p_pr_segment_id in varchar2,
361 p_vlb_id in varchar2,
362 p_pn_line_id in number,
363 p_list_price in number,
364 p_deal_curr in varchar2,
365 p_ordered_qty in number,
366 p_fetch_pol in boolean,
367 p_pol_ref_line_id in number default null
368 ) is
369
370 rec_pn_pol_ins qpr_pn_policy_type;
371 l_pol_price number;
372 l_pol_ctr number := 1;
373 a_null char(1);
374 l_sql varchar2(20000);
375 l_curr_conv number;
376
377 cursor c_pn_prices(p_policy_meas_type varchar2) is
378 select p.pn_price_id
379 from qpr_pn_prices p, qpr_pn_pr_types t
380 where p.pn_pr_type_id = t.pn_pr_type_id
381 and p.pn_line_id = p_pn_line_id
382 and t.erosion_type = p_policy_meas_type
383 and rownum < 2;
384
385 cursor c_pol_det is
386 select p.policy_line_id, p.policy_id, pl.policy_type_code, pl.policy_measure_type_code,
387 pl.limit_value_type_code,
388 pl.ref_limit_value, null, null
389 from qpr_pn_policies p , qpr_pn_prices pr, qpr_policy_lines pl
390 where p.pn_price_id = pr.pn_price_id
391 and pr.pn_line_id = p_pol_ref_line_id
392 and p.policy_id = pl.policy_id
393 and p.policy_line_id = pl.policy_line_id;
394
395 begin
396 if p_fetch_pol then
397 log_debug('getting policy details...');
398 qpr_policy_eval.get_policy_details(l_deal_instance,
399 p_pr_segment_id,
400 null,
401 p_deal_date,
402 p_vlb_id,
403 null, null,
404 g_t_pol_det);
405 else
406 log_debug('Getting policy details.. ');
407 open c_pol_det;
408 fetch c_pol_det bulk collect into g_t_pol_det;
409 close c_pol_det;
410 end if;
411
412 if g_t_pol_det is not null then
413 log_debug('policy rec count:' || g_t_pol_det.count);
414 log_debug('Evaluating policy values');
415 for i in 1..g_t_pol_det.count loop
416 rec_pn_pol_ins.POLICY_ID(l_pol_ctr) := g_t_pol_det(i).POLICY_ID;
417 rec_pn_pol_ins.POLICY_LINE_ID(l_pol_ctr) :=
418 g_t_pol_det(i).POLICY_LINE_ID;
419 for r_pn_pric in c_pn_prices(g_t_pol_det(i).POLICY_MEASURE_TYPE_CODE)
420 loop
421 rec_pn_pol_ins.PN_PRICE_ID(l_pol_ctr) := r_pn_pric.PN_PRICE_ID;
422 end loop;
423 if g_t_pol_det(i).LIMIT_VALUE_TYPE_CODE = 'AMOUNT' then
424 l_curr_conv := qpr_sr_util.ods_curr_conversion(null, p_deal_curr,
425 null, p_deal_date, l_deal_instance);
426 if g_t_pol_det(i).POLICY_MEASURE_TYPE_CODE = 'COST' then
427 l_pol_price := p_list_price -
428 (nvl(g_t_pol_det(i).REF_LIMIT_VALUE,0) * l_curr_conv);
429 else
430 l_pol_price := nvl(g_t_pol_det(i).REF_LIMIT_VALUE, 0) * l_curr_conv;
431 end if;
432 else
433 if g_t_pol_det(i).POLICY_MEASURE_TYPE_CODE = 'COST' then
434 l_pol_price := p_list_price *
435 nvl(g_t_pol_det(i).REF_LIMIT_VALUE, 0)/100;
436 else
437 l_pol_price := p_list_price - (p_list_price *
438 nvl(g_t_pol_det(i).REF_LIMIT_VALUE, 0)/100);
439 end if;
440 end if;
441 rec_pn_pol_ins.POLICY_PRICE(l_pol_ctr) := l_pol_price;
442 rec_pn_pol_ins.POLICY_AMOUNT(l_pol_ctr) := l_pol_price *
443 nvl(p_ordered_qty, 0);
444 log_debug('Policy id: ' || g_t_pol_det(i).policy_id ||
445 ', Policy line id = ' || g_t_pol_det(i).policy_line_id);
446 log_debug(' Policy Type: ' || g_t_pol_det(i).policy_type_code);
447 log_debug('Policy Measure Type: ' || g_t_pol_det(i).policy_measure_type_code);
448 log_debug('Policy amount:' || l_pol_price * nvl(p_ordered_qty, 0));
449 l_pol_ctr := l_pol_ctr +1;
450 end loop;
451 g_t_pol_det.delete;
452 else
453 log_debug('No policy details found');
454 end if;
455
456 if rec_pn_pol_ins.policy_id.count > 0 then
457 forall i in rec_pn_pol_ins.POLICY_ID.first..rec_pn_pol_ins.POLICY_ID.last
458 insert into qpr_pn_policies(PN_POLICY_ID,
459 PN_PRICE_ID,
460 POLICY_ID,
461 POLICY_LINE_ID,
462 POLICY_PRICE,
463 POLICY_AMOUNT,
464 CREATION_DATE,
465 CREATED_BY,
466 LAST_UPDATE_DATE,
467 LAST_UPDATED_BY,
468 LAST_UPDATE_LOGIN)
469 values(qpr_pn_policies_s.nextval,
470 rec_pn_pol_ins.pn_price_id(i),
471 rec_pn_pol_ins.policy_id(i),
472 rec_pn_pol_ins.policy_line_id(i),
473 rec_pn_pol_ins.policy_price(i),
474 rec_pn_pol_ins.policy_amount(i),
475 sysdate,
476 FND_GLOBAL.USER_ID,
477 SYSDATE,
478 FND_GLOBAL.USER_ID,
479 FND_GLOBAL.CONC_LOGIN_ID);
480 log_debug('Inserted ' || sql%rowcount || ' policy records');
481 end if;
482 exception
483 when others then
484 log_debug('Failed in policy fetching');
485 log_debug(dbms_utility.format_error_backtrace);
486 raise;
487 end insert_policy_details;
488
489 function score_calc(p_list_price in number,
490 p_unit_cost in number,
491 p_floor_margin in number,
492 p_line_margin in number,
493 p_inv_price in number,
494 p_recommend_price in number
495 ) return number is
496 l_ceiling_margin number;
497 l_pocmrg_score number;
498 l_inv_pr_score number;
499 l_pocmrg_score_wt number;
500 l_invpr_score_wt number;
501 l_line_score number;
502 begin
503
504 log_debug('unit list price: ' || p_list_price);
505 log_debug('Unit cost:' || p_unit_cost);
506 log_debug('floor margin per unit: ' || p_floor_margin);
507 log_debug('Actual margin per unit:' || p_line_margin);
508 log_debug('Unit Invoice price: ' || p_inv_price);
509 log_debug('Unit recommended price: ' || p_recommend_price);
510
511 l_ceiling_margin := p_list_price - p_unit_cost;
512
513 if l_ceiling_margin = 0 then
514 l_pocmrg_score := 10;
515 elsif l_ceiling_margin = p_floor_margin then
516 l_pocmrg_score := 0;
517 else
518 l_pocmrg_score := 10 - 9 * ((l_ceiling_margin - p_line_margin)
519 / (l_ceiling_margin - p_floor_margin));
520 end if;
521
522 if l_pocmrg_score < 0 then
523 l_pocmrg_score := 0;
524 elsif l_pocmrg_score > 10 then
525 l_pocmrg_score := 10;
526 end if;
527
528 log_debug('Margin part of score: ' || l_pocmrg_score);
529
530 if p_recommend_price = 0 then
531 l_inv_pr_score := 0;
532 else
533 l_inv_pr_score := 10 - ((p_inv_price/p_recommend_price) -1) * 10;
534 end if;
535
536 if l_inv_pr_score < 0 then
537 l_inv_pr_score := 0;
538 elsif l_inv_pr_score > 10 then
539 l_inv_pr_score := 10;
540 end if;
541
542 log_debug('Invoice Price part of score: ' || l_inv_pr_score);
543
544 l_pocmrg_score_wt := nvl(fnd_profile.value('QPR_MRG_SCORE_WT'),0);
545 l_invpr_score_wt := nvl(fnd_profile.value('QPR_INVPR_SCORE_WT'), 0);
546
547 l_line_score := (l_pocmrg_score_wt * l_pocmrg_score +
548 l_invpr_score_wt * l_inv_pr_score)
549 / (l_pocmrg_score_wt + l_invpr_score_wt);
550
551 log_debug('Final Score: ' || l_line_score);
552 return(l_line_score);
553
554 end score_calc;
555
556 function get_line_score(p_pn_line_id in number,
557 p_recommend_pric in number) return number is
558 l_line_score number := 0;
559 l_list_price number := 0;
560 l_inv_price number := 0;
561 l_margin number := 0;
562 l_floor_mrg number := 0;
563 l_cost number := 0;
564
565 cursor c_pric is
566 select nvl(p.unit_price, 0) price, t.price_type_name
567 from qpr_pn_prices p, qpr_pn_pr_types t
568 where p.pn_line_id = p_pn_line_id
569 and p.pn_pr_type_id = t.pn_pr_type_id;
570
571 begin
572 select nvl(sum(p.erosion_per_unit),0)
573 into l_cost
574 from qpr_pn_pr_details p
575 where p.pn_line_id = p_pn_line_id
576 and p.erosion_type = 'COST';
577
578 select nvl(min(p.policy_price) , 0) into l_floor_mrg
579 from qpr_pn_policies p, qpr_pn_prices pric, qpr_pn_pr_types t
580 where pric.pn_line_id = p_pn_line_id
581 and p.pn_price_id = pric.pn_price_id
582 and pric.pn_pr_type_id = t.pn_pr_type_id
583 and t.price_type_name = 'POCMARGIN';
584
585 for r_pric in c_pric loop
586 if r_pric.price_type_name = 'LISTPRICE' then
587 l_list_price := r_pric.price;
588 elsif r_pric.price_type_name = 'INVPRICE' then
589 l_inv_price := r_pric.price;
590 elsif r_pric.price_type_name = 'POCMARGIN' then
591 l_margin := r_pric.price;
592 end if;
593 end loop;
594
595
596 l_line_score := score_calc(l_list_price, l_cost, l_floor_mrg,
597 l_margin, l_inv_price, nvl(p_recommend_pric,0));
598
599 return(l_line_score);
600 end get_line_score;
601
602 procedure insert_model_lines(p_response_id number, p_deal_date date) is
603 cursor c_mdl_lines is
604 select *
605 from qpr_pn_lines
606 where response_header_id = p_response_id
607 and item_type_code in ('MDL', 'KIT');
608
609 l_LIST_PRICE number;
610 l_PROPOSED_PRICE number;
611 l_LINE_PRICING_SCORE number := 0;
612 l_line_id number;
613 l_recommended_price number;
614 l_regression_slope number;
615 l_regression_intercept number;
616 l_deal_uom_pp_conv number;
617 l_deal_curr_pp_conv number;
618 l_aw_uom qpr_price_plans_b.base_uom_code%type;
619 l_aw_curr qpr_price_plans_b.currency_code%type;
620 begin
621 for c_mdl_lines_rec in c_mdl_lines loop
622 begin
623 select pn_line_id into l_line_id
624 from qpr_pn_lines
625 where source_ref_line_id = c_mdl_lines_rec.source_ref_line_id
626 and source_ref_hdr_id = c_mdl_lines_rec.source_ref_hdr_id
627 and source_id = c_mdl_lines_rec.source_id
628 and response_header_id = p_response_id
629 and item_type_code = 'DUMMY_PARENT'
630 and rownum < 2;
631 exception
632 when others then
633 log_debug('Rolled up model not found. No processing done');
634 return;
635 end;
636
637 log_debug('Updating Model line-'|| l_line_id);
638
639 select decode(sum(nvl(pr.amount, 0)), 0, 0,
640 sum(nvl(l.line_pricing_score,0) * nvl(pr.amount,0))/
641 sum(nvl(pr.amount,0))) ,
642 sum(PROPOSED_PRICE * REVISED_OQ),
643 sum(RECOMMENDED_PRICE * REVISED_OQ),
644 sum(nvl(REGRESSION_INTERCEPT,0) *
645 (case when (qpr_sr_util.ods_uom_conv(
646 l.inventory_item_id,
647 l.UOM_CODE,
648 pp.base_uom_code, pp.instance_id, null) < 0) then
649 0 else qpr_sr_util.ods_uom_conv(
650 l.inventory_item_id,
651 l.UOM_CODE,
652 pp.base_uom_code, pp.instance_id, null) end)
653 * REVISED_OQ),
654 min(pp.base_uom_code), min(pp.currency_code)
655 into l_LINE_PRICING_SCORE ,
656 l_PROPOSED_PRICE,
657 l_recommended_price,
658 l_regression_intercept,
659 l_aw_uom, l_aw_curr
660 from qpr_pn_lines l, qpr_pn_prices pr, qpr_pn_pr_types prt,
661 qpr_price_plans_b pp
662 where l.parent_pn_line_id= c_mdl_lines_rec.source_ref_line_id
663 and l.response_header_id = p_response_id
664 and l.response_header_id = pr.response_header_id
665 and l.pn_line_id = pr.pn_line_id
666 and l.price_plan_id = pp.price_plan_id
667 and pr.pn_pr_type_id = prt.pn_pr_type_id
668 and prt.price_type_name = 'LISTPRICE';
669
670 if c_mdl_lines_rec.revised_oq is null or
671 c_mdl_lines_rec.revised_oq = 0 then
672 l_PROPOSED_PRICE := 0;
673 l_recommended_price := 0;
674 l_regression_slope := 0;
675 else
676 l_PROPOSED_PRICE:= l_PROPOSED_PRICE/c_mdl_lines_rec.revised_oq;
677 l_recommended_price := l_recommended_price/c_mdl_lines_rec.revised_oq;
678 l_deal_uom_pp_conv := qpr_sr_util.ods_uom_conv(
679 c_mdl_lines_rec.inventory_item_id,
680 c_mdl_lines_rec.UOM_CODE, l_aw_uom,
681 l_deal_instance,null);
682 l_deal_curr_pp_conv := qpr_sr_util.ods_curr_conversion(
683 c_mdl_lines_rec.currency_code,
684 l_aw_curr, null, p_deal_date,
685 l_deal_instance);
686 if l_deal_uom_pp_conv < 0 or l_deal_curr_pp_conv < 0 then
687 l_regression_slope := 0;
688 l_regression_intercept := 0;
689 else
690 l_regression_intercept := l_regression_intercept/
691 c_mdl_lines_rec.revised_oq * l_deal_uom_pp_conv;
692 l_regression_slope := ((l_recommended_price * l_deal_curr_pp_conv) -
693 l_regression_intercept)/
694 c_mdl_lines_rec.revised_oq * l_deal_uom_pp_conv;
695 end if;
696 end if;
697
698 update qpr_pn_lines set PROPOSED_PRICE = l_PROPOSED_PRICE,
699 RECOMMENDED_PRICE = l_recommended_price,
700 REGRESSION_SLOPE = l_regression_slope,
701 LINE_PRICING_SCORE = l_LINE_PRICING_SCORE
702 where pn_line_id = l_line_id;
703
704 log_debug('Recommended price:' || l_recommended_price);
705 log_debug('Line Score:' || l_line_pricing_score);
706
707 insert_price_adj_recs(p_response_id, l_line_id,null, null, null,
708 c_mdl_lines_rec.source_ref_line_id,
709 c_mdl_lines_rec.revised_oq);
710
711 l_list_price := insert_prices(p_response_id, l_line_id,
712 c_mdl_lines_rec.source_ref_line_id,
713 c_mdl_lines_rec.source_ref_hdr_id,
714 c_mdl_lines_rec.source_id,
715 c_mdl_lines_rec.revised_oq);
716
717 insert_policy_details(null, null,null,
718 l_line_id,
719 l_list_price,
720 c_mdl_lines_rec.currency_code,
721 c_mdl_lines_rec.ordered_qty,
722 false,
723 c_mdl_lines_rec.pn_line_id);
724
725 log_debug('Update parent_pn_line_id for child lines of model...');
726 update qpr_pn_lines
727 set parent_pn_line_id = l_line_id
728 where (parent_pn_line_id= c_mdl_lines_rec.source_ref_line_id
729 or pn_line_id = c_mdl_lines_rec.pn_line_id)
730 and pn_line_id <> l_line_id
731 and response_header_id = p_response_id;
732 log_debug('No of lines updated: '||sql%rowcount);
733 end loop;
734 exception
735 when others then
736 log_debug('failed in inserting model line');
737 log_debug(dbms_utility.format_error_backtrace);
738 raise;
739 end;
740
741 procedure insert_req_res_header_lines(
742 errbuf OUT NOCOPY VARCHAR2,
743 retcode OUT NOCOPY VARCHAR2,
744 p_src_ref_hdr_id in number,
745 p_int_header_id in number,
746 p_simulation in varchar2 ,
747 p_response_id out nocopy number,
748 p_is_deal_compliant out nocopy varchar2) is
749 cursor c_int_header is
750 select *
751 from qpr_pn_int_headers
752 where pn_int_header_id = p_int_header_id;
753
754 cursor c_int_lines(p_src_id number) is
755 select *
756 from qpr_pn_int_lines
757 where source_ref_hdr_id = p_src_ref_hdr_id
758 and source_id = p_src_id
759 and pn_req_line_status_flag = 'I'
760 order by pn_int_line_id;
761
762 cursor c_pn_lines(p_resp_hdr_id number) is
763 select * from qpr_pn_lines
764 where response_header_id = p_resp_hdr_id
765 and item_type_code <> 'DUMMY_PARENT';
766
767 l_request_id number;
768 l_response_id number;
769 l_total_score number;
770 l_line_id number;
771 l_reference_name varchar2(240);
772 l_line_score number;
773 l_sql varchar2(30000);
774 l_pr_segment number;
775 l_list_price number;
776 l_recommend_price number := 0;
777 l_transf_vol number := 0;
778 l_line_num varchar2(240);
779 l_Response_status varchar2(240);
780 l_return_status varchar2(10);
781 l_deal_uom_conv number;
782 l_deal_curr_conv number;
783 l_aw_uom qpr_price_plans_b.base_uom_code%type;
784 l_aw_curr qpr_price_plans_b.currency_code%type;
785 begin
786
787 log_debug('Populating deal tables...');
788
789 for c_int_header_rec in c_int_header loop
790 insert into qpr_pn_request_hdrs_b (REQUEST_HEADER_ID,
791 REQUEST_STATUS,
792 PN_INT_HEADER_ID,
793 INSTANCE_ID,
794 CURRENCY_SHORT_DESC,
795 CURRENCY_LONG_DESC,
796 SOURCE_ID,
797 SOURCE_SHORT_DESC,
798 SOURCE_LONG_DESC,
799 SOURCE_REF_HDR_ID,
800 SOURCE_REF_HDR_SHORT_DESC,
801 SOURCE_REF_HDR_LONG_DESC,
802 CUSTOMER_ID,
803 CUSTOMER_SK,
804 CUSTOMER_SHORT_DESC,
805 CUSTOMER_LONG_DESC,
806 SALES_REP_ID,
807 SALES_REP_SK,
808 SALES_REP_SHORT_DESC,
809 SALES_REP_LONG_DESC,
810 SALES_REP_EMAIL,
811 SALES_CHANNEL_CODE,
812 SALES_CHANNEL_SK,
813 SALES_CHANNEL_SHORT_DESC,
814 SALES_CHANNEL_LONG_DESC,
815 FREIGHT_TERMS_SHORT_DESC,
816 FREIGHT_TERMS_LONG_DESC,
817 DEAL_EXPIRY_DATE,
818 DEAL_CREATION_DATE,
819 INVOICE_TO_PARTY_SITE_ID,
820 INVOICE_TO_PARTY_SITE_ADDRESS,
821 SIMULATION_FLAG,
822 COMMENTS,
823 CREATION_DATE,
824 CREATED_BY,
825 LAST_UPDATE_DATE,
826 LAST_UPDATED_BY,
827 LAST_UPDATE_LOGIN
828 )
829 values (
830 qpr_pn_request_hdrs_s.nextval,'ACTIVE',
831 p_int_header_id,
832 l_deal_instance,
833 c_int_header_rec.CURRENCY_CODE,
834 c_int_header_rec.CURRENCY_LONG_DESC,
835 c_int_header_rec.SOURCE_ID,
836 c_int_header_rec.SOURCE_SHORT_DESC,
837 c_int_header_rec.SOURCE_LONG_DESC,
838 c_int_header_rec.SOURCE_REF_HEADER_ID,
839 c_int_header_rec.SOURCE_REF_HEADER_SHORT_DESC,
840 c_int_header_rec.SOURCE_REF_HEADER_LONG_DESC,
841 c_int_header_rec.CUSTOMER_ID,
842 nvl2(c_int_header_rec.CUSTOMER_ID,
843 'TRADING_PARTNER_L_'||c_int_header_rec.CUSTOMER_ID,
844 null),
845 c_int_header_rec.CUSTOMER_SHORT_DESC,
846 c_int_header_rec.CUSTOMER_LONG_DESC,
847 c_int_header_rec.SALES_REP_ID,
848 nvl2(c_int_header_rec.SALES_REP_ID,
849 'SALES_REP_L_'||c_int_header_rec.SALES_REP_ID, null),
850 c_int_header_rec.SALES_REP_SHORT_DESC,
851 c_int_header_rec.SALES_REP_LONG_DESC,
852 c_int_header_rec.SALES_REP_EMAIL_ADDRESS,
853 c_int_header_rec.SALES_CHANNEL_CODE,
854 nvl2(c_int_header_rec.SALES_CHANNEL_CODE,
855 'SALES_CHANNEL_L_'||c_int_header_rec.SALES_CHANNEL_CODE,null),
856 c_int_header_rec.SALES_CHANNEL_SHORT_DESC,
857 c_int_header_rec.SALES_CHANNEL_LONG_DESC,
858 c_int_header_rec.FREIGHT_TERMS_SHORT_DESC,
859 c_int_header_rec.FREIGHT_TERMS_LONG_DESC,
860 c_int_header_rec.PN_REQ_EXPIRY_DATE,
861 c_int_header_rec.PN_REQ_HEADER_CREATION_DATE,
862 c_int_header_rec.INVOICE_TO_PARTY_SITE_ID,
863 c_int_header_rec.INVOICE_TO_PARTY_SITE_ADDRESS,
864 p_simulation,
865 c_int_header_rec.COMMENTS,
866 SYSDATE,
867 FND_GLOBAL.USER_ID,
868 SYSDATE,
869 FND_GLOBAL.USER_ID,
870 FND_GLOBAL.CONC_LOGIN_ID
871 ) returning REQUEST_HEADER_ID into l_request_id;
872
873 l_reference_name :=
874 substr(nvl(c_int_header_rec.source_short_desc||': '||
875 c_int_header_rec.source_ref_header_short_desc, 'Null'),1,240);
876
877 log_debug('Inserted Request header: ' || l_request_id || '-'
878 || l_reference_name);
879 ------- insert into request_hdrs_tl ----
880 insert into qpr_pn_request_hdrs_tl (
881 LANGUAGE,
882 REQUEST_HEADER_ID,
883 REFERENCE_NAME,
884 SOURCE_LANG,
885 CREATION_DATE,
886 CREATED_BY,
887 LAST_UPDATE_DATE,
888 LAST_UPDATED_BY,
889 LAST_UPDATE_LOGIN)
890 select L.LANGUAGE_CODE ,l_request_id,
891 l_reference_name,
892 userenv('LANG'),
893 SYSDATE,
894 FND_GLOBAL.USER_ID,
895 SYSDATE,
896 FND_GLOBAL.USER_ID,
897 FND_GLOBAL.CONC_LOGIN_ID
898 from FND_LANGUAGES L
899 where L.INSTALLED_FLAG in ('I', 'B');
900
901 log_debug('Inserted Request header TL values');
902
903 --- insert qpr_pn_response_hdrs ----
904 insert into qpr_pn_response_hdrs(
905 RESPONSE_HEADER_ID,
906 REQUEST_HEADER_ID,
907 OWNER_ID,
908 DEAL_HEADER_SCORE,
909 RESPONSE_STATUS,
910 PARENT_RESPONSE_ID,
911 DEAL_LAST_UPDATED_BY,
912 DEAL_LAST_UPDATE_DATE,
913 COMMENTS,
914 VERSION_NUMBER,
915 BOOKMARK_FLAG,
916 CREATION_DATE,
917 CREATED_BY,
918 LAST_UPDATE_DATE,
919 LAST_UPDATED_BY,
920 LAST_UPDATE_LOGIN
921 ) values
922 (qpr_pn_response_hdrs_s.nextval,
923 l_request_id, fnd_global.user_id,
924 null,
925 'APPROVE_REQ',
926 null,
927 fnd_global.user_id,
928 sysdate,
929 null,
930 1,
931 'N',
932 SYSDATE,
933 FND_GLOBAL.USER_ID,
934 SYSDATE,
935 FND_GLOBAL.USER_ID,
936 FND_GLOBAL.CONC_LOGIN_ID)
937 returning RESPONSE_HEADER_ID into l_response_id;
938
939 log_debug('Inserted Response Id: '||l_response_id);
940
941 log_debug('Inserting Pn_lines...');
942 for int_lines_rec in c_int_lines(c_int_header_rec.SOURCE_ID) loop
943 log_debug('Inserting line: Source line id = '|| int_lines_rec.source_ref_line_id);
944
945 if int_lines_rec.ITEM_TYPE_CODE = 'MDL' or
946 int_lines_rec.ITEM_TYPE_CODE = 'KIT' then
947 log_debug('Inserting rolled up model for model/kit line');
948 l_line_num := substrb(int_lines_rec.SOURCE_REQUEST_LINE_NUMBER, 1,
949 instrb(int_lines_rec.SOURCE_REQUEST_LINE_NUMBER, '.' , 1, 1)- 1);
950 insert into qpr_pn_lines(PN_LINE_ID,
951 RESPONSE_HEADER_ID,
952 REQUEST_HEADER_ID,
953 PRICE_PLAN_ID,
954 SOURCE_REF_LINE_ID,
955 SOURCE_REQUEST_LINE_NUMBER,
956 SOURCE_REF_HDR_ID,
957 SOURCE_ID,
958 ORG_ID,
959 INVENTORY_ITEM_ID,
960 PAYMENT_TERM_ID,
961 PARENT_PN_LINE_ID,
962 GEOGRAPHY_ID,
963 UOM_CODE,
964 CURRENCY_CODE,
965 ITEM_TYPE_CODE, ORDERED_QTY,
966 COMPETITOR_PRICE,
967 PROPOSED_PRICE,
968 ORG_DIM_SK,
969 ORG_LONG_DESC,
970 ORG_SHORT_DESC,
971 COMPETITOR_NAME,
972 REVISED_OQ,
973 PRODUCT_DIM_SK,
974 INVENTORY_ITEM_SHORT_DESC,
975 INVENTORY_ITEM_LONG_DESC,
976 VOL_BAND_SK,
977 GEOGRAPHY_SK,
978 GEOGRAPHY_SHORT_DESC,
979 GEOGRAPHY_LONG_DESC,
980 PAYMENT_TERM_SHORT_DESC,
981 PAYMENT_TERM_LONG_DESC,
982 UOM_SHORT_DESC,
983 CURRENCY_SHORT_DESC,
984 COMMENTS, ADDITIONAL_INFORMATION,
985 SHIP_METHOD_CODE,
986 SHIP_METHOD_SHORT_DESC,
987 SHIP_METHOD_LONG_DESC,
988 DATAMART_NAME,
989 REGRESSION_SLOPE,
990 REGRESSION_INTERCEPT,
991 RECOMMENDED_PRICE,
992 PR_SEGMENT_ID,
993 PR_SEGMENT_SK,
994 ORIG_PAYMENT_TERM_ID,
995 ORIG_SHIP_METHOD_CODE,
996 CREATION_DATE,
997 CREATED_BY,
998 LAST_UPDATE_DATE,
999 LAST_UPDATE_LOGIN,
1000 LAST_UPDATED_BY)
1001 values(QPR_PN_LINES_S.nextval,
1002 l_response_id,
1003 l_request_id,
1004 int_lines_rec.PRICE_PLAN_ID,
1005 int_lines_rec.SOURCE_REF_LINE_ID,
1006 l_line_num,
1007 int_lines_rec.SOURCE_REF_HDR_ID,
1008 int_lines_rec.SOURCE_ID,
1009 int_lines_rec.ORG_ID,
1010 int_lines_rec.INVENTORY_ITEM_ID,
1011 int_lines_rec.PAYMENT_TERM_ID,
1012 null,
1013 int_lines_rec.GEOGRAPHY_ID,
1014 int_lines_rec.UOM_CODE,
1015 int_lines_rec.CURRENCY_CODE,
1016 'DUMMY_PARENT',
1017 int_lines_rec.ORDERED_QTY,
1018 int_lines_rec.COMPETITOR_PRICE,
1019 0,
1020 nvl2(int_lines_rec.ORG_ID,
1021 'OPERATING_UNIT_L_'||int_lines_rec.ORG_ID,null),
1022 int_lines_rec.ORG_LONG_DESC,
1023 int_lines_rec.ORG_SHORT_DESC,
1024 int_lines_rec.COMPETITOR_NAME,
1025 int_lines_rec.ORDERED_QTY,
1026 'MODEL_L_'||int_lines_rec.INVENTORY_ITEM_ID,
1027 int_lines_rec.INVENTORY_ITEM_SHORT_DESC,
1028 int_lines_rec.INVENTORY_ITEM_LONG_DESC,
1029 int_lines_rec.VOL_BAND_SK,
1030 nvl2(int_lines_rec.GEOGRAPHY_ID,
1031 'TRADING_PARTNER_SITE_L_'||int_lines_rec.GEOGRAPHY_ID,
1032 null),
1033 int_lines_rec.GEOGRAPHY_SHORT_DESC,
1034 int_lines_rec.GEOGRAPHY_LONG_DESC,
1035 int_lines_rec.PAYMENT_TERM_SHORT_DESC,
1036 int_lines_rec.PAYMENT_TERM_LONG_DESC,
1037 int_lines_rec.UOM_SHORT_DESC,
1038 int_lines_rec.CURRENCY_SHORT_DESC,
1039 int_lines_rec.COMMENTS,
1040 int_lines_rec.ADDITIONAL_INFORMATION,
1041 int_lines_rec.SHIP_METHOD_CODE,
1042 int_lines_rec.SHIP_METHOD_SHORT_DESC,
1043 int_lines_rec.SHIP_METHOD_LONG_DESC,
1044 int_lines_rec.datamart_name,
1045 0,
1046 int_lines_rec.regression_intercept,
1047 0,
1048 int_lines_rec.pr_segment_id,
1049 nvl2(int_lines_rec.pr_Segment_id,
1050 'PR_SEGMENT_L_' || int_lines_rec.pr_segment_id, null),
1051 int_lines_rec.PAYMENT_TERM_ID,
1052 int_lines_rec.SHIP_METHOD_CODE,
1053 SYSDATE,
1054 FND_GLOBAL.USER_ID,
1055 SYSDATE,
1056 FND_GLOBAL.USER_ID,
1057 FND_GLOBAL.CONC_LOGIN_ID)
1058 returning PN_LINE_ID into l_line_id;
1059 log_debug('Inserted rolled up model' || l_line_id);
1060 end if;
1061
1062 log_debug('Determining recommended price of the quote line');
1063
1064 if (int_lines_rec.REGRESSION_INTERCEPT <> 0
1065 or int_lines_rec.REGRESSION_SLOPE <> 0) then
1066 select base_uom_code, currency_code
1067 into l_aw_uom, l_aw_curr
1068 from qpr_price_plans_b
1069 where price_plan_id = int_lines_rec.PRICE_PLAN_ID
1070 and rownum < 2;
1071
1072 l_deal_uom_conv := qpr_sr_util.ods_uom_conv(
1073 int_lines_rec.inventory_item_id,
1074 int_lines_rec.UOM_CODE,
1075 l_aw_uom, l_deal_instance, null);
1076
1077 l_deal_curr_conv := qpr_sr_util.ods_curr_conversion(l_aw_curr,
1078 int_lines_rec.currency_code,
1079 null,
1080 c_int_header_rec.PN_REQ_HEADER_CREATION_DATE,
1081 l_deal_instance);
1082 if l_deal_uom_conv < 0 or l_deal_curr_conv < 0 then
1083 log_debug('Cannot determine uom/currency conversion between ' ||
1084 'deal unit and price plan units');
1085 l_recommend_price := 0;
1086 else
1087 qpr_regression_analysis.reg_transf
1088 (int_lines_rec.PRICE_PLAN_ID,
1089 int_lines_rec.INVENTORY_ITEM_ID,
1090 int_lines_rec.pr_segment_id,
1091 int_lines_rec.ORDERED_QTY *
1092 l_deal_uom_conv,
1093 l_transf_vol);
1094
1095 qpr_regression_analysis.reg_antitransf
1096 (int_lines_rec.PRICE_PLAN_ID,
1097 int_lines_rec.INVENTORY_ITEM_ID,
1098 int_lines_rec.pr_segment_id,
1099 (int_lines_rec.REGRESSION_INTERCEPT +
1100 int_lines_rec.REGRESSION_SLOPE *
1101 l_transf_vol),
1102 l_recommend_price);
1103 l_recommend_price := l_recommend_price * l_deal_curr_conv;
1104 end if;
1105 else
1106 l_recommend_price := 0;
1107 end if;
1108
1109 log_debug('Recommended_price = ' || l_recommend_price);
1110
1111 insert into qpr_pn_lines(PN_LINE_ID,
1112 RESPONSE_HEADER_ID,
1113 REQUEST_HEADER_ID,
1114 PRICE_PLAN_ID,
1115 SOURCE_REF_LINE_ID,
1116 SOURCE_REQUEST_LINE_NUMBER,
1117 SOURCE_REF_HDR_ID,
1118 SOURCE_ID,
1119 ORG_ID,
1120 INVENTORY_ITEM_ID,
1121 PAYMENT_TERM_ID,
1122 PARENT_PN_LINE_ID,
1123 GEOGRAPHY_ID,
1124 UOM_CODE,
1125 CURRENCY_CODE,
1126 ITEM_TYPE_CODE, ORDERED_QTY,
1127 COMPETITOR_PRICE,
1128 PROPOSED_PRICE,
1129 ORG_DIM_SK,
1130 ORG_LONG_DESC,
1131 ORG_SHORT_DESC,
1132 COMPETITOR_NAME,
1133 REVISED_OQ,
1134 PRODUCT_DIM_SK,
1135 INVENTORY_ITEM_SHORT_DESC,
1136 INVENTORY_ITEM_LONG_DESC,
1137 VOL_BAND_SK,
1138 GEOGRAPHY_SK,
1139 GEOGRAPHY_SHORT_DESC,
1140 GEOGRAPHY_LONG_DESC,
1141 PAYMENT_TERM_SHORT_DESC,
1142 PAYMENT_TERM_LONG_DESC,
1143 UOM_SHORT_DESC,
1144 CURRENCY_SHORT_DESC,
1145 COMMENTS, ADDITIONAL_INFORMATION,
1146 SHIP_METHOD_CODE,
1147 SHIP_METHOD_SHORT_DESC,
1148 SHIP_METHOD_LONG_DESC,
1149 DATAMART_NAME,
1150 REGRESSION_SLOPE,
1151 REGRESSION_INTERCEPT,
1152 RECOMMENDED_PRICE,
1153 PR_SEGMENT_ID,
1154 PR_SEGMENT_SK,
1155 ORIG_PAYMENT_TERM_ID,
1156 ORIG_SHIP_METHOD_CODE,
1157 CREATION_DATE,
1158 CREATED_BY,
1159 LAST_UPDATE_DATE,
1160 LAST_UPDATE_LOGIN,
1161 LAST_UPDATED_BY)
1162 values(QPR_PN_LINES_S.nextval,
1163 l_response_id,
1164 l_request_id,
1165 int_lines_rec.PRICE_PLAN_ID,
1166 int_lines_rec.SOURCE_REF_LINE_ID,
1167 int_lines_rec.SOURCE_REQUEST_LINE_NUMBER,
1168 int_lines_rec.SOURCE_REF_HDR_ID,
1169 int_lines_rec.SOURCE_ID,
1170 int_lines_rec.ORG_ID,
1171 int_lines_rec.INVENTORY_ITEM_ID,
1172 int_lines_rec.PAYMENT_TERM_ID,
1173 int_lines_rec.TOP_MDL_SRC_LINE_ID,
1174 int_lines_rec.GEOGRAPHY_ID,
1175 int_lines_rec.UOM_CODE,
1176 int_lines_rec.CURRENCY_CODE,
1177 int_lines_rec.ITEM_TYPE_CODE,
1178 int_lines_rec.ORDERED_QTY,
1179 int_lines_rec.COMPETITOR_PRICE,
1180 int_lines_rec.PROPOSED_PRICE,
1181 nvl2(int_lines_rec.ORG_ID,
1182 'OPERATING_UNIT_L_'||int_lines_rec.ORG_ID,null),
1183 int_lines_rec.ORG_LONG_DESC,
1184 int_lines_rec.ORG_SHORT_DESC,
1185 int_lines_rec.COMPETITOR_NAME,
1186 int_lines_rec.ORDERED_QTY,
1187 nvl2(int_lines_rec.INVENTORY_ITEM_ID,
1188 'ITEM_L_'||int_lines_rec.INVENTORY_ITEM_ID, null),
1189 int_lines_rec.INVENTORY_ITEM_SHORT_DESC,
1190 int_lines_rec.INVENTORY_ITEM_LONG_DESC,
1191 int_lines_rec.VOL_BAND_SK,
1192 nvl2(int_lines_rec.GEOGRAPHY_ID,
1193 'TRADING_PARTNER_SITE_L_'||int_lines_rec.GEOGRAPHY_ID,
1194 null),
1195 int_lines_rec.GEOGRAPHY_SHORT_DESC,
1196 int_lines_rec.GEOGRAPHY_LONG_DESC,
1197 int_lines_rec.PAYMENT_TERM_SHORT_DESC,
1198 int_lines_rec.PAYMENT_TERM_LONG_DESC,
1199 int_lines_rec.UOM_SHORT_DESC,
1200 int_lines_rec.CURRENCY_SHORT_DESC,
1201 int_lines_rec.COMMENTS,
1202 int_lines_rec.ADDITIONAL_INFORMATION,
1203 int_lines_rec.SHIP_METHOD_CODE,
1204 int_lines_rec.SHIP_METHOD_SHORT_DESC,
1205 int_lines_rec.SHIP_METHOD_LONG_DESC,
1206 int_lines_rec.datamart_name,
1207 int_lines_rec.regression_slope,
1208 int_lines_rec.regression_intercept,
1209 l_recommend_price,
1210 int_lines_rec.pr_segment_id,
1211 nvl2(int_lines_rec.pr_Segment_id,
1212 'PR_SEGMENT_L_' || int_lines_rec.pr_segment_id, null),
1213 int_lines_rec.PAYMENT_TERM_ID,
1214 int_lines_rec.SHIP_METHOD_CODE,
1215 SYSDATE,
1216 FND_GLOBAL.USER_ID,
1217 SYSDATE,
1218 FND_GLOBAL.USER_ID,
1219 FND_GLOBAL.CONC_LOGIN_ID)
1220 returning PN_LINE_ID into l_line_id;
1221
1222 log_debug('Inserted line: pn_line_id = ' || l_line_id);
1223
1224 insert_price_adj_recs( l_response_id, l_line_id,
1225 int_lines_rec.source_ref_line_id,
1226 int_lines_rec.source_ref_hdr_id,
1227 int_lines_rec.source_id);
1228
1229 end loop; -- lines loop
1230
1231
1232 for lines_rec in c_pn_lines(l_response_id) loop
1233
1234 l_list_price := insert_prices(l_response_id, lines_rec.pn_line_id,
1235 lines_rec.source_ref_line_id, lines_rec.source_ref_hdr_id,
1236 lines_rec.source_id);
1237
1238 insert_policy_details(
1239 c_int_header_rec.PN_REQ_HEADER_CREATION_DATE,
1240 lines_rec.pr_segment_id,
1241 lines_rec.vol_band_sk,
1242 lines_rec.pn_line_id,
1243 l_list_price,
1244 lines_rec.currency_code,
1245 lines_rec.ordered_qty,true);
1246 log_debug('Determine Line score');
1247 l_line_score := get_line_score(lines_rec.pn_line_id,
1248 lines_rec.recommended_price);
1249
1250 update qpr_pn_lines set line_pricing_score = round(l_line_score, 2)
1251 where pn_line_id = lines_rec.pn_line_id;
1252 end loop; -- 2nd lines loop
1253
1254 -- insert adjustment records for header and line total adjustment
1255 log_debug('Consolidating adjustment records');
1256 insert_price_adj_recs(l_response_id, null, null, null,null);
1257
1258 log_debug('Consolidating price records');
1259 l_list_price := insert_prices(l_response_id, null);
1260
1261 log_debug('Updating details for rolled up model of the quote');
1262 insert_model_lines(l_response_id,
1263 c_int_header_rec.PN_REQ_HEADER_CREATION_DATE);
1264
1265 log_debug('Header Score calc...');
1266 begin
1267 select round(sum(nvl(l.line_pricing_score,0)*nvl(pr.amount,0))/
1268 sum(nvl(pr.amount,0)), 2)
1269 into l_total_score
1270 from qpr_pn_lines l, qpr_pn_prices pr, qpr_pn_pr_types prt
1271 where l.response_header_id = l_response_id
1272 and pr.response_header_id= l.response_header_id
1273 and pr.pn_line_id = l.pn_line_id
1274 and pr.pn_pr_type_id = prt.pn_pr_type_id
1275 and prt.price_type_name = 'LISTPRICE';
1276 exception
1277 when others then
1278 l_total_score := 0;
1279 end;
1280
1281 log_debug('Header Score:' || round(l_total_score, 2));
1282
1283 update qpr_pn_response_hdrs
1284 set deal_header_score = round(l_total_score, 2)
1285 where response_header_id = l_response_id;
1286
1287 log_debug('Check compliance and fetch approvers if needed...');
1288 qpr_deal_approvals_pvt.init_approvals(l_response_id,
1289 fnd_global.user_id,
1290 p_is_deal_compliant,
1291 l_return_status);
1292 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1293 retcode := 2;
1294 errbuf := sqlerrm;
1295 FND_MESSAGE.Set_Name ('QPR','QPR_COMPLIACE_ERROR');
1296 FND_MSG_PUB.Add;
1297 log_debug('Error checking compliance');
1298 exit;
1299 else
1300 if p_is_deal_compliant = 'Y' then
1301 l_response_status := 'PEND_ACCEPT_NO_APPROVE';
1302 log_debug('Deal is complaint');
1303 else
1304 l_response_status := 'APPROVE_REQ';
1305 log_debug('Deal is non-compliant. Requires approval');
1306 end if;
1307
1308 update qpr_pn_response_hdrs
1309 set response_status = l_response_status
1310 where response_header_id = l_response_id;
1311 end if;
1312
1313 end loop; -- header loop
1314 p_response_id := l_response_id;
1315
1316 exception
1317 when others then
1318 errbuf := sqlerrm;
1319 retcode := 2;
1320 log_debug(sqlerrm);
1321 log_debug(dbms_utility.format_error_backtrace);
1322 end insert_req_res_header_lines;
1323
1324
1325 procedure insert_price_int_adj_recs(p_source_ref_hdr_id in number,
1326 p_source_ref_line_id in number,
1327 p_src_id in number,
1328 p_er_det_rec in qpr_deal_pvt.pn_aw_data_rec)
1329 is
1330 cursor c_offadj(p_sm_code varchar2, p_pt_code varchar2,
1331 p_rbt_code varchar2,
1332 p_sm_oad_val number, p_pt_oad_val number,
1333 p_rbt_oad_val number) is
1334 select er_type, er_name, er_desc, er_val, er_tot_val
1335 from (
1336 select 'OFFINVOICE' er_type,
1337 decode(num, '1', nvl2(p_sm_code, substr(p_sm_code, 12), 'ShippingMethod-'),
1338 '2', nvl2(p_pt_code, substr(p_pt_code, 12), 'PaymentTerm-'),
1339 '3', substr(p_rbt_code, 11)) er_name,
1340 decode(num, '1', l.ship_method_long_desc,
1341 '2', l.payment_term_short_desc,
1342 '3', qpr_sr_util.get_oad_ar_cm_type_desc) er_desc,
1343 decode(nvl(l.ordered_qty,0), 0 , 0, decode(num, '1', p_sm_oad_val, '2', p_pt_oad_val,
1344 '3', p_rbt_oad_val)/l.ordered_qty) er_val,
1345 decode(nvl(l.ordered_qty, 0), 0 , 0, decode(num, '1', p_sm_oad_val, '2', p_pt_oad_val,
1346 '3', p_rbt_oad_val)) er_tot_val
1347 from qpr_pn_int_lines l,
1348 (select rownum num from dual connect by level <=3)
1349 where l.source_ref_hdr_id = p_source_ref_hdr_id
1350 and l.source_ref_line_id = p_source_ref_line_id
1351 and l.source_id = p_src_id)
1352 where er_name is not null;
1353
1354 t_er_typ char240_type;
1355 t_er_name char240_type;
1356 t_er_desc char240_type;
1357 t_er_val num_type;
1358 t_tot_er num_type;
1359 l_ord_qty number;
1360 l_adj_id number;
1361 l_er_name varchar2(240);
1362
1363 begin
1364 if p_er_det_rec.pn_line_id is not null then
1365 if p_er_det_rec.get_cost_flag = 'Y' then
1366 begin
1367 select pn_int_pr_adj_id,
1368 l.ordered_qty
1369 into l_adj_id, l_ord_qty
1370 from qpr_pn_int_pr_adjs pr, qpr_pn_int_lines l
1371 where pr.source_ref_hdr_id = p_source_ref_hdr_id
1372 and pr.source_ref_line_id = p_source_ref_line_id
1373 and pr.source_id = p_src_id
1374 and pr.source_ref_line_id = l.source_ref_line_id
1375 and pr.source_ref_hdr_id = l.source_ref_hdr_id
1376 and pr.source_id = l.source_id
1377 and erosion_type = 'COST'
1378 and rownum < 2;
1379
1380 update qpr_pn_int_pr_adjs set erosion_per_unit = p_er_det_rec.unit_cost,
1381 erosion_amount = p_er_det_rec.unit_cost * l_ord_qty
1382 where pn_int_pr_adj_id = l_adj_id;
1383
1384 log_debug('updated cost: unit cost' || p_er_det_rec.unit_cost);
1385 exception
1386 when others then
1387 null;
1388 end;
1389 end if;
1390
1391 -- inserting oninvoice modifier: QPR_WHATIF --
1392 delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_source_ref_hdr_id
1393 and source_ref_line_id = p_source_ref_line_id
1394 and source_id = p_src_id
1395 and erosion_type = 'ONINVOICE' and erosion_name = 'QPR_WHATIF';
1396
1397 begin
1398 select meaning into l_er_name
1399 from qpr_lookups where lookup_type = 'QPR_DEAL_EROSIONS'
1400 and lookup_code = 'WHATIF' and rownum < 2;
1401 exception
1402 when no_data_found then
1403 l_er_name := 'What-If Analysis';
1404 end;
1405
1406 insert into qpr_pn_int_pr_adjs(PN_INT_PR_ADJ_ID,
1407 SOURCE_REF_HDR_ID,
1408 SOURCE_REF_LINE_ID,
1409 SOURCE_ID,
1410 EROSION_TYPE,
1411 EROSION_NAME,
1412 EROSION_DESC,
1413 EROSION_PER_UNIT,
1414 erosion_amount,
1415 CREATION_DATE,
1416 CREATED_BY,
1417 LAST_UPDATE_DATE,
1418 LAST_UPDATED_BY,
1419 LAST_UPDATE_LOGIN)
1420 values(qpr_pn_int_pr_adjs_s.nextval,
1421 p_source_ref_hdr_id,
1422 p_source_ref_line_id,
1423 p_src_id,
1424 'ONINVOICE',
1425 'QPR_WHATIF',
1426 l_er_name,
1427 0, 0,
1428 SYSDATE,
1429 FND_GLOBAL.USER_ID,
1430 SYSDATE,
1431 FND_GLOBAL.USER_ID,
1432 FND_GLOBAL.CONC_LOGIN_ID);
1433
1434 log_debug('Inserted oninvoice modifier QPR_WHATIF for use in whatif');
1435
1436 -- Insert offinvoice modifiers ----
1437 delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_source_ref_hdr_id
1438 and source_ref_line_id = p_source_ref_line_id
1439 and source_id = p_src_id
1440 and erosion_type = 'OFFINVOICE';
1441
1442 open c_offadj(p_er_det_rec.ship_method_code,
1443 p_er_det_rec.payment_term_code,
1444 p_er_det_rec.rebate_code,
1445 p_er_det_rec.ship_method_oad_val,
1446 p_er_det_rec.payment_term_oad_val,
1447 p_er_det_rec.rebate_oad_val);
1448 fetch c_offadj bulk collect into t_er_typ, t_er_name, t_er_desc,
1449 t_er_val, t_tot_er;
1450 close c_offadj;
1451
1452 forall i in t_er_name.first..t_er_name.last
1453 insert into qpr_pn_int_pr_adjs(PN_INT_PR_ADJ_ID,
1454 SOURCE_REF_HDR_ID,
1455 SOURCE_REF_LINE_ID,
1456 SOURCE_ID,
1457 EROSION_TYPE,
1458 EROSION_NAME,
1459 EROSION_DESC,
1460 EROSION_PER_UNIT,
1461 erosion_amount,
1462 CREATION_DATE,
1463 CREATED_BY,
1464 LAST_UPDATE_DATE,
1465 LAST_UPDATED_BY,
1466 LAST_UPDATE_LOGIN)
1467 values(qpr_pn_int_pr_adjs_s.nextval,
1468 p_source_ref_hdr_id,
1469 p_source_ref_line_id,
1470 p_src_id,
1471 t_er_typ(i),
1472 t_er_name(i),
1473 t_er_desc(i),
1474 t_er_val(i),
1475 t_tot_er(i),
1476 SYSDATE,
1477 FND_GLOBAL.USER_ID,
1478 SYSDATE,
1479 FND_GLOBAL.USER_ID,
1480 FND_GLOBAL.CONC_LOGIN_ID);
1481 for i in t_er_name.first..t_er_name.last loop
1482 log_debug('Inserted Offinvoice modifier ' || t_er_name(i)
1483 || ': Erosion per unit=' || t_er_val(i));
1484 end loop;
1485
1486 t_er_typ.delete;
1487 t_er_name.delete;
1488 t_er_desc.delete;
1489 t_er_val.delete;
1490 t_tot_er.delete;
1491
1492 end if;
1493
1494 exception
1495 when others then
1496 log_debug(dbms_utility.format_error_backtrace);
1497 raise;
1498 end insert_price_int_adj_recs;
1499
1500 function determine_line_price(p_src_ref_hdr_id in number,
1501 p_src_ref_line_id in number,
1502 p_src_id in number,
1503 p_price_type_name in varchar2)
1504 return number is
1505 cursor c_pr_type is
1506 select * from qpr_pn_pr_types order by sequence_no;
1507
1508 cursor c_adjs(p_erosion_type varchar2) is
1509 select nvl(sum(erosion_per_unit),0) unit_erosion
1510 from qpr_pn_int_pr_adjs
1511 where source_ref_line_id = p_src_ref_line_id
1512 and source_ref_hdr_id = p_src_ref_hdr_id
1513 and source_id = p_src_id
1514 and erosion_type = p_erosion_type;
1515
1516 c_line SYS_REFCURSOR;
1517 l_price number;
1518 l_sql varchar2(10000);
1519 begin
1520 for r_pr_typ in c_pr_type loop
1521 if r_pr_typ.derived_from_type is not null then
1522 for r1 in c_adjs(r_pr_typ.erosion_type) loop
1523 l_price := l_price - r1.unit_erosion;
1524 end loop;
1525 else
1526 l_sql := 'select ' || r_pr_typ.column_name
1527 || ' from qpr_pn_int_lines'
1528 || ' where source_ref_hdr_id = :1 and source_ref_line_id = :2'
1529 || ' and pn_req_line_status_flag = ''I'' and source_id = :3 '
1530 || ' and rownum < 2';
1531 open c_line for l_sql using p_src_ref_hdr_id, p_src_ref_line_id, p_src_id;
1532 fetch c_line into l_price;
1533 close c_line;
1534 end if;
1535 if p_price_type_name = r_pr_typ.price_type_name then
1536 exit;
1537 end if;
1538 end loop;
1539 return(l_price);
1540 end determine_line_price;
1541
1542 procedure do_deal_preprocess(errbuf out nocopy varchar2,
1543 retcode out nocopy varchar2,
1544 p_src_ref_hdr_id in number,
1545 p_pn_int_hdr_id in number) is
1546 cursor c_int_lines(p_src_id number) is
1547 select * from qpr_pn_int_lines
1548 where source_ref_hdr_id = p_src_ref_hdr_id
1549 and source_id = p_src_id
1550 and pn_req_line_status_flag = 'I';
1551
1552 cursor c_int_hdr is
1553 select * from qpr_pn_int_headers
1554 where pn_int_header_id = p_pn_int_hdr_id
1555 and instance_id = l_deal_instance
1556 and rownum < 2;
1557
1558 cursor c_line_aw(p_hdr_id number, p_src_id number) is
1559 select distinct price_plan_id
1560 from qpr_pn_int_lines
1561 where source_ref_hdr_id = p_hdr_id
1562 and source_id = p_src_id;
1563
1564 cursor c_pn_lines(p_hdr_id number, p_price_plan_id number, p_src_id number) is
1565 select * from qpr_pn_int_lines
1566 where source_ref_hdr_id = p_hdr_id
1567 and source_id = p_src_id
1568 and price_plan_id = nvl(p_price_plan_id, price_plan_id);
1569
1570
1571 l_transf_group_id number;
1572 l_aw_name varchar2(240);
1573 l_datamart_id number;
1574 l_uom_conversion_odm number;
1575 l_vol_band varchar2(240);
1576 l_sql varchar2(30000);
1577 l_line_ctr number;
1578 l_gross_rev number;
1579 l_inv_price number;
1580 l_pr_segment_id number;
1581 l_pol_importance_code varchar2(30);
1582 l_slope number;
1583 l_intercept number;
1584 begin
1585 log_debug('Determining prerequisites for line processing...');
1586 l_transf_group_id := to_number(nvl(fnd_profile.value(
1587 'QPR_VOL_BAND_DEAL'),0));
1588
1589 for c_int_header_rec in c_int_hdr loop
1590 for int_lines_rec in c_int_lines(c_int_header_rec.source_id) loop
1591 log_debug('Source Line Id: '||int_lines_rec.source_ref_line_id);
1592
1593 if int_lines_rec.uom_code is not null then
1594 l_uom_conversion_odm := qpr_sr_util.uom_conv(
1595 int_lines_rec.UOM_CODE,
1596 int_lines_rec.inventory_item_id,
1597 null);
1598 else
1599 l_uom_conversion_odm := 0;
1600 end if;
1601
1602 -- get pricing segment --
1603 log_debug('Finding Pricing Segment');
1604 qpr_policy_eval.get_pricing_segment_id(
1605 l_deal_instance,
1606 null,
1607 c_int_header_rec.pn_req_header_creation_date,
1608 int_lines_rec.inventory_item_id,
1609 int_lines_rec.geography_id,
1610 c_int_header_rec.customer_id,
1611 int_lines_rec.org_id,
1612 c_int_header_rec.sales_rep_id,
1613 c_int_header_rec.sales_channel_code,
1614 null,
1615 l_pr_segment_id,
1616 l_pol_importance_code );
1617 if nvl(l_pr_segment_id,0) = 0 then
1618 retcode := 2;
1619 FND_MESSAGE.Set_Name ('QPR','QPR_NO_PSG');
1620 FND_MESSAGE.Set_Token ('LINE_ID','int_lines_rec.source_ref_line_id');
1621 FND_MSG_PUB.Add;
1622 log_debug('No pricing segment found for line:'
1623 || int_lines_rec.source_ref_line_id);
1624 return;
1625 end if;
1626
1627 log_debug('Pricing Segment:' || l_pr_segment_id);
1628 --- Volume Band --
1629 log_debug('Finding volume band');
1630 log_debug('Volume band group used: '||l_transf_group_id);
1631
1632 if l_uom_conversion_odm < 0 then
1633 l_vol_band := null;
1634 else
1635 l_vol_band := qpr_deal_pvt.get_volume_band(errbuf, retcode,
1636 int_lines_rec.inventory_item_id,
1637 int_lines_rec.ordered_qty * l_uom_conversion_odm,
1638 l_transf_group_id);
1639 end if;
1640 -- assign aw --
1641 log_debug('Findind Datamart');
1642 l_datamart_id := qpr_deal_pvt.assign_aw(errbuf, retcode,
1643 l_deal_instance,
1644 int_lines_rec.inventory_item_id,
1645 int_lines_rec.org_id,
1646 c_int_header_rec.sales_rep_id,
1647 c_int_header_rec.customer_id,
1648 int_lines_rec.geography_id,
1649 c_int_header_rec.sales_channel_code,
1650 l_pr_segment_id,
1651 l_aw_name);
1652
1653 if l_datamart_id = 0 then
1654 retcode := 2;
1655 FND_MESSAGE.Set_Name ('QPR','QPR_NO_DATAMART');
1656 FND_MESSAGE.Set_Token ('LINE_ID','int_lines_rec.source_ref_line_id');
1657 FND_MSG_PUB.Add;
1658 log_debug('No datamart found for line:'
1659 || int_lines_rec.source_ref_line_id);
1660 return;
1661 end if;
1662
1663 log_debug('Finding regression slope and intercept');
1664 begin
1665 select nvl(regression_slope,0), nvl(regression_intercept ,0)
1666 into l_slope, l_intercept
1667 from qpr_regression_result
1668 where price_plan_id = l_datamart_id
1669 and product_id = int_lines_rec.inventory_item_id
1670 and pr_segment_id = l_pr_segment_id;
1671 exception
1672 when NO_DATA_FOUND then
1673 l_slope := 0;
1674 l_intercept := 0;
1675 end;
1676 log_debug('Slope:' || l_slope);
1677 log_debug('Intercept:' || l_intercept);
1678
1679 update qpr_pn_int_lines
1680 set price_plan_id = l_datamart_id,
1681 datamart_name = l_aw_name,
1682 vol_band_sk = l_vol_band,
1683 pr_segment_id = l_pr_segment_id,
1684 regression_slope = l_slope,
1685 regression_intercept = l_intercept
1686 where pn_int_line_id = int_lines_rec.pn_int_line_id;
1687 end loop; -- lines loop
1688
1689 log_debug('Get offinvoice adjustments and model cost for lines from datamart...');
1690 for aw_rec in c_line_aw(p_src_ref_hdr_id,c_int_header_rec.source_id) loop
1691 l_line_ctr := 1;
1692 log_debug('Getting details from datamart:' || aw_rec.price_plan_id);
1693 for lines_rec in c_pn_lines(p_src_ref_hdr_id,
1694 aw_rec.price_plan_id,
1695 c_int_header_rec.source_id) loop
1696 l_inv_price := determine_line_price(p_src_ref_hdr_id,
1697 lines_rec.source_ref_line_id, lines_rec.source_id, 'INVPRICE');
1698 l_gross_rev := l_inv_price * lines_rec.ordered_qty;
1699
1700 if l_line_ctr = 1 then
1701 g_t_aw_det := qpr_deal_pvt.pn_aw_tbl_type();
1702 end if;
1703 g_t_aw_det.extend;
1704 g_t_aw_det(l_line_ctr).PN_LINE_ID := lines_rec.SOURCE_REF_LINE_ID;
1705 g_t_aw_det(l_line_ctr).CUSTOMER_SK:= 'TRADING_PARTNER_L_' ||
1706 c_int_header_rec.CUSTOMER_ID;
1707 g_t_aw_det(l_line_ctr).PRODUCT_DIM_SK:= 'ITEM_L_' ||
1708 lines_rec.inventory_item_id;
1709 g_t_aw_det(l_line_ctr).PR_SEGMENT_SK := 'PR_SEGMENT_L_' ||
1710 lines_rec.pr_segment_id;
1711 g_t_aw_det(l_line_ctr).DEAL_CREATION_DATE:=
1712 c_int_header_rec.PN_REQ_HEADER_CREATION_DATE;
1713 g_t_aw_det(l_line_ctr).DEAL_CURRENCY:= c_int_header_rec.CURRENCY_CODE;
1714
1715 if lines_rec.payment_term_id is null then
1716 g_t_aw_det(l_line_ctr).PAYMENT_TERM_CODE:= null;
1717 else
1718 g_t_aw_det(l_line_ctr).PAYMENT_TERM_CODE:='OFF_TERM_L_PaymentTerm-'
1719 || (lines_rec.payment_term_id);
1720 end if;
1721 if lines_rec.ship_method_code is null then
1722 g_t_aw_det(l_line_ctr).SHIP_METHOD_CODE:= null;
1723 else
1724 g_t_aw_det(l_line_ctr).SHIP_METHOD_CODE:=
1725 'OFF_TERM_L_ShippingMethod-'
1726 || (lines_rec.ship_method_code);
1727 end if;
1728 g_t_aw_det(l_line_ctr).REBATE_CODE:= 'OFF_TYP_L_REBATE';
1729 g_t_aw_det(l_line_ctr).GROSS_REVENUE:= l_gross_rev;
1730 g_t_aw_det(l_line_ctr).PAYMENT_TERM_OAD_VAL:=0;
1731 g_t_aw_det(l_line_ctr).SHIP_METHOD_OAD_VAL:= 0;
1732 g_t_aw_det(l_line_ctr).REBATE_OAD_VAL:= 0;
1733 if (lines_rec.item_type_code = 'MDL') or
1734 (lines_rec.item_type_code = 'ATOCLASS') or
1735 (lines_rec.item_type_code = 'PTOCLASS') then
1736 g_t_aw_det(l_line_ctr).GET_COST_FLAG := 'Y';
1737 else
1738 g_t_aw_det(l_line_ctr).GET_COST_FLAG := 'N';
1739 end if;
1740 g_t_aw_det(l_line_ctr).UNIT_COST := 0;
1741
1742 l_line_ctr := l_line_ctr + 1;
1743 end loop;
1744
1745 qpr_deal_pvt.get_line_aw_details(errbuf, retcode,
1746 aw_rec.price_plan_id,
1747 l_deal_instance,
1748 g_t_aw_det);
1749
1750 if nvl(retcode,0) = 0 then
1751 log_debug('Fetched details from datamart');
1752 if g_t_aw_det.count > 0 then
1753 for k in 1..g_t_aw_det.count loop
1754 log_debug('inserting/updating adjustment values for:'
1755 || g_t_aw_det(k).pn_line_id);
1756 insert_price_int_adj_recs(p_src_ref_hdr_id,
1757 g_t_aw_det(k).pn_line_id,
1758 c_int_header_rec.source_id,
1759 g_t_aw_det(k));
1760 end loop;
1761 end if;
1762 end if;
1763
1764 g_t_aw_det.delete;
1765 end loop;
1766 end loop; --hdr
1767
1768 exception
1769 when others then
1770 retcode := 2;
1771 errbuf := sqlerrm;
1772 log_debug(dbms_utility.format_error_backtrace);
1773 end do_deal_preprocess;
1774
1775 -- called from concurrent program --
1776 procedure process_deal(
1777 errbuf OUT NOCOPY VARCHAR2,
1778 retcode OUT NOCOPY VARCHAR2,
1779 f_source_ref_id NUMBER,
1780 t_source_ref_id NUMBER,
1781 reprocess varchar2 default 'N',
1782 reload varchar2 default 'N')
1783 is
1784 l_request_id number;
1785 l_count number;
1786 l_response_id number;
1787 l_deal_compliant varchar2(1);
1788 t_src_hdr_id num_type;
1789 t_pn_int_hdr num_type;
1790 t_instance num_type;
1791 t_src_id num_type;
1792
1793 cursor i_header is
1794 select source_ref_header_id, pn_int_header_id, instance_id, source_id
1795 from qpr_pn_int_headers
1796 where request_id = l_request_id;
1797
1798 l_count_lines number := 0;
1799
1800 begin
1801 log_debug('Starting..');
1802
1803 l_request_id := fnd_global.conc_request_id;
1804
1805 update qpr_pn_int_headers rih
1806 set rih.request_id = l_request_id
1807 where rih.source_ref_header_id between
1808 nvl(f_source_ref_id, rih.source_ref_header_id)
1809 and nvl(t_source_ref_id, rih.source_ref_header_id)
1810 and ((reprocess = 'N' and rih.pn_req_header_status_flag = 'I')
1811 or (reprocess = 'Y' and rih.pn_req_header_status_flag = 'F'));
1812
1813 l_count := sql%rowcount;
1814 commit;
1815
1816 if l_count >0 then
1817 log_debug('Collecting headers to process..');
1818 open i_header;
1819 fetch i_header bulk collect
1820 into t_src_hdr_id, t_pn_int_hdr, t_instance, t_src_id;
1821 close i_header;
1822
1823 log_debug('No of headers to process-'||t_src_hdr_id.count);
1824
1825 for I in 1..t_src_hdr_id.count
1826 loop
1827 log_debug('Processing Header:' || t_src_hdr_id(i));
1828 l_deal_instance := t_instance(i);
1829 begin
1830 select 1 into l_count_lines
1831 from qpr_pn_int_lines
1832 where source_ref_hdr_id = t_src_hdr_id(i)
1833 and source_id = t_src_id(i)
1834 and pn_req_line_status_flag = 'I' and rownum < 2;
1835 exception
1836 when NO_DATA_FOUND then
1837 log_debug('No lines exist for the Header: '||t_pn_int_hdr(i));
1838 l_count_lines := 0;
1839 end;
1840 if (l_count_lines = 1) then
1841 savepoint deal_processing;
1842 do_deal_preprocess(errbuf, retcode, t_src_hdr_id(i),
1843 t_pn_int_hdr(i));
1844 if nvl(retcode,0) <> 2 then
1845 insert_req_res_header_lines(errbuf, retcode,
1846 t_src_hdr_id(i),
1847 t_pn_int_hdr(i),
1848 'N',
1849 l_response_id,
1850 l_deal_compliant);
1851 end if;
1852 if nvl(retcode, 0) = 2 then
1853 rollback to deal_processing;
1854
1855 update qpr_pn_int_headers
1856 set request_id = null,
1857 pn_req_header_status_flag = 'F'
1858 where pn_int_header_id = t_pn_int_hdr(i);
1859
1860 commit;
1861
1862 else
1863 delete qpr_pn_int_headers where pn_int_header_id = t_pn_int_hdr(i);
1864
1865 delete qpr_pn_int_lines where source_ref_hdr_id = t_src_hdr_id(i)
1866 and source_id = t_src_id(i);
1867
1868 delete qpr_pn_int_pr_adjs where source_ref_hdr_id = t_src_hdr_id(i)
1869 and source_id = t_src_id(i);
1870
1871 commit;
1872 end if;
1873 end if; -- Check for the existence of lines
1874 end loop;
1875 end if;
1876 exception
1877 WHEN NO_DATA_FOUND THEN
1878 retcode := 2;
1879 errbuf := FND_MESSAGE.GET;
1880 fnd_file.put_line( fnd_file.log, 'Deal not found in Interface tables');
1881 when others then
1882 retcode := 2;
1883 errbuf := FND_MESSAGE.GET;
1884 fnd_file.put_line( fnd_file.log, 'Unexpected error '||substr(sqlerrm,1200));
1885 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1886 end process_deal;
1887
1888 procedure process_deal_api(
1889 errbuf OUT NOCOPY VARCHAR2,
1890 retcode OUT NOCOPY VARCHAR2,
1891 p_instance_id in number,
1892 p_source_id in number,
1893 p_quote_header_id in number,
1894 p_simulation in varchar2 default 'Y',
1895 p_response_id out nocopy number,
1896 p_is_deal_compliant out nocopy varchar2,
1897 p_rules_desc out nocopy varchar2)
1898 is
1899 l_src_hdr_id number;
1900 l_pn_int_hdr number;
1901 l_count_lines number := 0;
1902
1903 cursor i_header is
1904 select source_ref_header_id, pn_int_header_id, instance_id
1905 from qpr_pn_int_headers
1906 where source_ref_header_id = p_quote_header_id
1907 and source_id = p_source_id
1908 and instance_id = p_instance_id
1909 and pn_req_header_status_flag = 'I'
1910 and rownum < 2;
1911
1912 cursor c_resp_app(p_resp_hdr_id number) is
1913 select distinct rule_description
1914 from qpr_pn_response_approvals
1915 where response_header_id = p_resp_hdr_id;
1916
1917 begin
1918 g_origin := p_source_id;
1919 log_debug('Starting..');
1920
1921 open i_header;
1922 fetch i_header
1923 into l_src_hdr_id, l_pn_int_hdr, l_deal_instance;
1924 close i_header;
1925
1926 begin
1927 begin
1928 select 1 into l_count_lines
1929 from qpr_pn_int_lines
1930 where source_ref_hdr_id = l_src_hdr_id
1931 and source_id = p_source_id
1932 and pn_req_line_status_flag = 'I' and rownum < 2;
1933 exception
1934 when NO_DATA_FOUND then
1935 FND_MESSAGE.Set_Name ('QPR','QPR_NO_LINES');
1936 FND_MESSAGE.Set_Token ('HEADER_ID','l_pn_int_hdr');
1937 FND_MSG_PUB.Add;
1938 log_debug('No lines exist for the Header: '||l_pn_int_hdr);
1939 retcode := 2;
1940 errbuf := 'No lines exist for quote';
1941 return;
1942 end;
1943
1944 do_deal_preprocess(errbuf, retcode, l_src_hdr_id,
1945 l_pn_int_hdr);
1946
1947 if nvl(retcode,0) <> 2 then
1948 insert_req_res_header_lines(errbuf, retcode,
1949 l_src_hdr_id,
1950 l_pn_int_hdr,p_simulation,
1951 p_response_id,
1952 p_is_deal_compliant
1953 );
1954 end if;
1955
1956 if nvl(retcode,0) <> 2 then
1957 if p_is_deal_compliant = 'N' then
1958 l_count_lines := 0;
1959 p_rules_desc := '';
1960 for rec_app in c_resp_app(p_response_id) loop
1961 p_rules_desc := p_rules_desc || rec_app.rule_description;
1962 l_count_lines := l_count_lines + 1;
1963 if l_count_lines > 9 then
1964 exit;
1965 else
1966 p_rules_desc := p_rules_desc || ',';
1967 end if;
1968 end loop;
1969 end if;
1970
1971 delete qpr_pn_int_headers where pn_int_header_id = l_pn_int_hdr;
1972
1973 delete qpr_pn_int_lines where source_ref_hdr_id = l_src_hdr_id
1974 and source_id = p_source_id;
1975
1976 delete qpr_pn_int_pr_adjs where source_ref_hdr_id = l_src_hdr_id
1977 and source_id = p_source_id;
1978
1979 end if;
1980 end;
1981 exception
1982 when others then
1983 retcode := 2;
1984 errbuf := FND_MESSAGE.GET;
1985 fnd_file.put_line( fnd_file.log, 'Unexpected error '||substr(sqlerrm,1200));
1986 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1987 end process_deal_api;
1988
1989 procedure create_deal_version(errbuf out nocopy varchar2,
1990 retcode out nocopy varchar2,
1991 p_response_hdr_id in number,
1992 p_new_resp_hdr_id out nocopy number) is
1993 cursor c_pn_lines is
1994 select * from qpr_pn_lines
1995 where response_header_id = p_response_hdr_id;
1996
1997 -- need hdr records also so outer join --
1998 -- join between lines ---
1999 -- request header id since the same source line id can exist in another request
2000 -- line_id to match lines
2001 -- item_type_code, since the model/dummy_parent have same source_ref_line_id
2002 cursor c_pn_pr_det(p_new_resp_hdr_id number) is
2003 select pr.erosion_type, pr.erosion_name, pr.erosion_desc,
2004 pr.erosion_per_unit,pr.erosion_amount, nl.pn_line_id
2005 from qpr_pn_pr_details pr, qpr_pn_lines ol, qpr_pn_lines nl
2006 where
2007 pr.response_header_id = p_response_hdr_id
2008 and pr.response_header_id = ol.response_header_id(+)
2009 and pr.pn_line_id = ol.pn_line_id(+)
2010 and nl.response_header_id(+) = p_new_resp_hdr_id
2011 and ol.request_header_id = nl.request_header_id(+)
2012 and ol.source_ref_line_id = nl.source_ref_line_id(+)
2013 and ol.item_type_code = nl.item_type_code(+);
2014
2015
2016 -- need hdr records also so outer join --
2017 cursor c_pn_prices(p_new_resp_hdr_id number) is
2018 select o.pn_pr_type_id, o.unit_price, o.amount,o.percent_price,
2019 nl.pn_line_id
2020 from qpr_pn_prices o, qpr_pn_lines nl, qpr_pn_lines ol
2021 where o.response_header_id = p_response_hdr_id
2022 and ol.response_header_id(+) = o.response_header_id
2023 and ol.pn_line_id(+) = o.pn_line_id
2024 and nl.response_header_id(+) = p_new_resp_hdr_id
2025 and ol.request_header_id = nl.request_header_id(+)
2026 and ol.source_ref_line_id = nl.source_ref_line_id(+)
2027 and ol.item_type_code = nl.item_type_code(+);
2028
2029 -- policies are only for lines ---
2030 cursor c_pol_price(p_new_resp_hdr_id number) is
2031 select
2032 op.POLICY_ID,
2033 op.POLICY_PRICE,
2034 op.POLICY_AMOUNT,
2035 op.policy_line_id,
2036 n.pn_price_id
2037 from qpr_pn_policies op,
2038 qpr_pn_prices o, qpr_pn_lines ol,
2039 qpr_pn_lines nl, qpr_pn_prices n
2040 where op.pn_price_id = o.pn_price_id
2041 and o.response_header_id = p_response_hdr_id
2042 and ol.response_header_id = o.response_header_id
2043 and ol.pn_line_id = o.pn_line_id
2044 and nl.response_header_id = p_new_resp_hdr_id
2045 and ol.request_header_id = nl.request_header_id
2046 and ol.source_ref_line_id = nl.source_ref_line_id
2047 and ol.item_type_code = nl.item_type_code
2048 and n.pn_pr_type_id = o.pn_pr_type_id
2049 and nl.response_header_id = n.response_header_id
2050 and nl.pn_line_id = n.pn_line_id;
2051
2052
2053 l_rows number := 1000;
2054 l_response_id number;
2055 l_request_hdr_id number;
2056 l_version_no number;
2057 l_hdr_score number;
2058 l_return_status varchar2(10);
2059 l_description QPR_PN_RESPONSE_HDRS.DESCRIPTION%type;
2060 l_comments QPR_PN_RESPONSE_HDRS.COMMENTS%type;
2061 l_response_stat QPR_PN_RESPONSE_HDRS.RESPONSE_STATUS%type;
2062 t_er_type char240_type;
2063 t_er_name char240_type;
2064 t_er_desc char240_type;
2065 t_unit_val num_type;
2066 t_amnt num_type;
2067 t_line_id num_type;
2068 t_pr_typ_id num_type;
2069 t_percent num_type;
2070 t_pol_id num_type;
2071 t_pol_line_id num_type;
2072 begin
2073 begin
2074 select request_header_id, version_number, deal_header_score,
2075 description,comments, response_status
2076 into l_request_hdr_id, l_version_no, l_hdr_score,
2077 l_description, l_comments, l_response_stat
2078 from qpr_pn_response_hdrs
2079 where response_header_id = p_response_hdr_id
2080 and rownum < 2;
2081
2082 select nvl(max(version_number), 0) into l_version_no
2083 from qpr_pn_response_hdrs
2084 where request_header_id = l_request_hdr_id;
2085
2086 insert into qpr_pn_response_hdrs(
2087 RESPONSE_HEADER_ID,
2088 REQUEST_HEADER_ID,
2089 DEAL_HEADER_SCORE,
2090 RESPONSE_STATUS,
2091 PARENT_RESPONSE_ID,
2092 DEAL_LAST_UPDATED_BY,
2093 DEAL_LAST_UPDATE_DATE,
2094 OWNER_ID,
2095 CREATION_DATE,
2096 CREATED_BY,
2097 LAST_UPDATE_DATE,
2098 LAST_UPDATED_BY,
2099 LAST_UPDATE_LOGIN,
2100 COMMENTS,
2101 DESCRIPTION,
2102 VERSION_NUMBER,
2103 BOOKMARK_FLAG
2104 ) values
2105 (qpr_pn_response_hdrs_s.nextval,
2106 l_request_hdr_id,
2107 l_hdr_score,
2108 l_response_stat,
2109 p_response_hdr_id,
2110 fnd_global.user_id,
2111 sysdate,
2112 fnd_global.user_id,
2113 SYSDATE,
2114 FND_GLOBAL.USER_ID,
2115 SYSDATE,
2116 FND_GLOBAL.USER_ID,
2117 FND_GLOBAL.CONC_LOGIN_ID,
2118 l_comments,
2119 l_description,
2120 l_version_no + 1,
2121 'N')
2122 returning RESPONSE_HEADER_ID into l_response_id;
2123 exception
2124 when NO_DATA_FOUND then
2125 retcode := 2;
2126 errbuf := sqlerrm || 'Source Response not found';
2127 return;
2128 end;
2129 for lines_rec in c_pn_lines loop
2130 insert into qpr_pn_lines(PN_LINE_ID,
2131 RESPONSE_HEADER_ID,
2132 REQUEST_HEADER_ID,
2133 PRICE_PLAN_ID,
2134 SOURCE_REF_LINE_ID,
2135 SOURCE_REQUEST_LINE_NUMBER,
2136 SOURCE_REF_HDR_ID,SOURCE_ID, ORG_ID,
2137 INVENTORY_ITEM_ID,
2138 PAYMENT_TERM_ID,
2139 PARENT_PN_LINE_ID,
2140 GEOGRAPHY_ID,
2141 UOM_CODE,
2142 CURRENCY_CODE,
2143 ITEM_TYPE_CODE, ORDERED_QTY,
2144 COMPETITOR_PRICE,
2145 PROPOSED_PRICE,
2146 ORG_DIM_SK,
2147 ORG_LONG_DESC,
2148 ORG_SHORT_DESC,
2149 COMPETITOR_NAME,
2150 REVISED_OQ,
2151 PRODUCT_DIM_SK,
2152 INVENTORY_ITEM_SHORT_DESC,
2153 INVENTORY_ITEM_LONG_DESC,
2154 VOL_BAND_SK,
2155 GEOGRAPHY_SK,
2156 GEOGRAPHY_SHORT_DESC,
2157 GEOGRAPHY_LONG_DESC,
2158 PAYMENT_TERM_SHORT_DESC,
2159 PAYMENT_TERM_LONG_DESC,
2160 UOM_SHORT_DESC,
2161 CURRENCY_SHORT_DESC,
2162 COMMENTS, ADDITIONAL_INFORMATION,
2163 SHIP_METHOD_CODE,
2164 SHIP_METHOD_SHORT_DESC,
2165 SHIP_METHOD_LONG_DESC,
2166 DATAMART_NAME,
2167 PR_SEGMENT_ID,
2168 PR_SEGMENT_SK,
2169 RECOMMENDED_PRICE,
2170 REGRESSION_SLOPE,
2171 REGRESSION_INTERCEPT,
2172 LINE_PRICING_SCORE,
2173 ORIG_PAYMENT_TERM_ID,
2174 ORIG_SHIP_METHOD_CODE,
2175 CREATION_DATE,
2176 CREATED_BY,
2177 LAST_UPDATE_DATE,
2178 LAST_UPDATE_LOGIN,
2179 LAST_UPDATED_BY)
2180 values(QPR_PN_LINES_S.nextval,
2181 l_response_id,
2182 lines_rec.REQUEST_HEADER_ID,
2183 lines_rec.PRICE_PLAN_ID,
2184 lines_rec.SOURCE_REF_LINE_ID,
2185 lines_rec.SOURCE_REQUEST_LINE_NUMBER,
2186 lines_rec.SOURCE_REF_HDR_ID,
2187 lines_rec.SOURCE_ID,
2188 lines_rec.ORG_ID,
2189 lines_rec.INVENTORY_ITEM_ID,
2190 lines_rec.PAYMENT_TERM_ID,
2191 lines_rec.PARENT_PN_LINE_ID,
2192 lines_rec.GEOGRAPHY_ID,
2193 lines_rec.UOM_CODE,
2194 lines_rec.CURRENCY_CODE,
2195 lines_rec.ITEM_TYPE_CODE,
2196 lines_rec.ORDERED_QTY,
2197 lines_rec.COMPETITOR_PRICE,
2198 lines_rec.PROPOSED_PRICE,
2199 lines_rec.ORG_DIM_SK,
2200 lines_rec.ORG_LONG_DESC,
2201 lines_rec.ORG_SHORT_DESC,
2202 lines_rec.COMPETITOR_NAME,
2203 lines_rec.ORDERED_QTY,
2204 lines_rec.PRODUCT_DIM_SK,
2205 lines_rec.INVENTORY_ITEM_SHORT_DESC,
2206 lines_rec.INVENTORY_ITEM_LONG_DESC,
2207 lines_rec.VOL_BAND_SK,
2208 lines_rec.GEOGRAPHY_SK,
2209 lines_rec.GEOGRAPHY_SHORT_DESC,
2210 lines_rec.GEOGRAPHY_LONG_DESC,
2211 lines_rec.PAYMENT_TERM_SHORT_DESC,
2212 lines_rec.PAYMENT_TERM_LONG_DESC,
2213 lines_rec.UOM_SHORT_DESC,
2214 lines_rec.CURRENCY_SHORT_DESC,
2215 lines_rec.COMMENTS,
2216 lines_rec.ADDITIONAL_INFORMATION,
2217 lines_rec.SHIP_METHOD_CODE,
2218 lines_rec.SHIP_METHOD_SHORT_DESC,
2219 lines_rec.SHIP_METHOD_LONG_DESC,
2220 lines_rec.datamart_name,
2221 lines_rec.PR_SEGMENT_ID,
2222 lines_rec.PR_SEGMENT_SK,
2223 lines_rec.RECOMMENDED_PRICE,
2224 lines_rec.REGRESSION_SLOPE,
2225 lines_rec.REGRESSION_INTERCEPT,
2226 lines_rec.LINE_PRICING_SCORE,
2227 lines_rec.ORIG_PAYMENT_TERM_ID,
2228 lines_rec.ORIG_SHIP_METHOD_CODE,
2229 SYSDATE,
2230 FND_GLOBAL.USER_ID,
2231 SYSDATE,
2232 FND_GLOBAL.USER_ID,
2233 FND_GLOBAL.CONC_LOGIN_ID);
2234 end loop;
2235
2236 open c_pn_pr_det(l_response_id);
2237 loop
2238 fetch c_pn_pr_det bulk collect into t_er_type, t_er_name, t_er_desc,
2239 t_unit_val, t_amnt, t_line_id
2240 limit l_rows;
2241 exit when t_line_id.count = 0;
2242 forall i in t_line_id.first..t_line_id.last
2243 insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
2244 RESPONSE_HEADER_ID,
2245 PN_LINE_ID,
2246 EROSION_TYPE,
2247 EROSION_NAME,
2248 EROSION_DESC,
2249 EROSION_PER_UNIT,
2250 erosion_amount,
2251 CREATION_DATE,
2252 CREATED_BY,
2253 LAST_UPDATE_DATE,
2254 LAST_UPDATED_BY,
2255 LAST_UPDATE_LOGIN)
2256 values(qpr_pn_pr_details_s.nextval,
2257 l_response_id,
2258 t_line_id(i),
2259 t_er_type(i),
2260 t_er_name(i),
2261 t_er_desc(i),
2262 t_unit_val(i),
2263 t_amnt(i),
2264 SYSDATE,
2265 FND_GLOBAL.USER_ID,
2266 SYSDATE,
2267 FND_GLOBAL.USER_ID,
2268 FND_GLOBAL.CONC_LOGIN_ID);
2269 t_line_id.delete;
2270 t_er_type.delete;
2271 t_er_name.delete;
2272 t_er_desc.delete;
2273 t_unit_val.delete;
2274 t_amnt.delete;
2275 end loop;
2276 close c_pn_pr_det;
2277
2278 open c_pn_prices(l_response_id);
2279 loop
2280 fetch c_pn_prices bulk collect into t_pr_typ_id, t_unit_val, t_amnt,
2281 t_percent, t_line_id
2282 limit l_rows;
2283 exit when t_line_id.count = 0;
2284 forall i in t_line_id.first.. t_line_id.last
2285 insert into qpr_pn_prices(PN_PRICE_ID,
2286 RESPONSE_HEADER_ID,
2287 PN_LINE_ID,
2288 PN_PR_TYPE_ID,
2289 UNIT_PRICE,
2290 AMOUNT,
2291 PERCENT_PRICE,
2292 CREATION_DATE,
2293 CREATED_BY,
2294 LAST_UPDATE_DATE,
2295 LAST_UPDATED_BY,
2296 LAST_UPDATE_LOGIN)
2297 values(
2298 qpr_pn_prices_s.nextval,
2299 l_response_id,
2300 t_line_id(i),
2301 t_pr_typ_id(i),
2302 t_unit_val(i), t_amnt(i), t_percent(i),
2303 SYSDATE,
2304 FND_GLOBAL.USER_ID,
2305 SYSDATE,
2306 FND_GLOBAL.USER_ID,
2307 FND_GLOBAL.CONC_LOGIN_ID);
2308 t_line_id.delete;
2309 t_unit_val.delete;
2310 t_amnt.delete;
2311 t_percent.delete;
2312 t_pr_typ_id.delete;
2313 end loop;
2314 close c_pn_prices;
2315
2316 open c_pol_price(l_response_id);
2317 loop
2318 fetch c_pol_price bulk collect into t_pol_id,
2319 t_unit_val, t_amnt, t_pol_line_id, t_pr_typ_id
2320 limit l_rows;
2321 exit when t_pr_typ_id.count = 0;
2322 forall i in t_pr_typ_id.first..t_pr_typ_id.last
2323 insert into qpr_pn_policies(PN_POLICY_ID,
2324 PN_PRICE_ID,
2325 POLICY_ID,
2326 POLICY_PRICE,
2327 POLICY_AMOUNT,
2328 POLICY_LINE_ID,
2329 CREATION_DATE,
2330 CREATED_BY,
2331 LAST_UPDATE_DATE,
2332 LAST_UPDATED_BY,
2333 LAST_UPDATE_LOGIN)
2334 values(qpr_pn_policies_s.nextval,
2335 t_pr_typ_id(i),
2336 t_pol_id(i),
2337 t_unit_val(i),
2338 t_amnt(i),
2339 t_pol_line_id(i),
2340 sysdate,
2341 FND_GLOBAL.USER_ID,
2342 SYSDATE,
2343 FND_GLOBAL.USER_ID,
2344 FND_GLOBAL.CONC_LOGIN_ID);
2345 t_pr_typ_id.delete;
2346 t_pol_id.delete;
2347 t_pol_line_id.delete;
2348 t_unit_val.delete;
2349 t_amnt.delete;
2350 end loop;
2351 close c_pol_price;
2352
2353 qpr_deal_approvals_pvt.synch_approvals(p_response_hdr_id,
2354 l_Response_id,
2355 l_return_status);
2356 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2357 retcode := 2;
2358 errbuf := 'Unable to copy approval history' || substr(sqlerrm, 200);
2359 p_new_resp_hdr_id := null;
2360 else
2361 p_new_resp_hdr_id := l_response_id;
2362 end if;
2363
2364 exception
2365 when OTHERS then
2366 retcode := 2;
2367 errbuf := sqlerrm;
2368 p_new_resp_hdr_id := null;
2369 end create_deal_version;
2370
2371
2372 procedure calculate_score(
2373 errbuf out nocopy varchar2,
2374 retcode out nocopy varchar2,
2375 i_response_header_id number,
2376 i_line_id number,
2377 i_date date,
2378 i_pr_segment_id number,
2379 i_inventory_item_id number,
2380 i_is_qty_changed in varchar2 default 'N',
2381 i_ordered_qty number,
2382 i_list_price number,
2383 i_unit_cost number,
2384 i_pock_margin number,
2385 i_inv_price number,
2386 i_recommended_price number,
2387 o_line_score out nocopy number)
2388 is
2389
2390 l_o_qty number := 0;
2391 l_floor_margin number := 0;
2392
2393 l_uom_code varchar2(240);
2394 l_uom_conversion_odm number := 0;
2395 l_transf_group_id number := 0;
2396 l_vol_band varchar2(240);
2397 l_pr_segment_id number;
2398 l_pol_importance_code varchar2(30);
2399 l_policy_price number := 0;
2400 l_min_pol_price number := 0;
2401 l_sql varchar2(30000);
2402 l_deal_currency varchar2(30);
2403 begin
2404
2405 if nvl(i_is_qty_changed, 'N') = 'Y' then
2406
2407 select l.uom_code, req.instance_id,l.currency_code
2408 into l_uom_code, l_deal_instance, l_deal_currency
2409 from qpr_pn_lines l, qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_b req
2410 where l.pn_line_id = i_line_id
2411 and l.response_header_id = i_response_header_id
2412 and l.response_header_id = resp.response_header_id
2413 and resp.request_header_id = req.request_header_id
2414 and rownum < 2;
2415
2416 if l_uom_code is not null then
2417 l_uom_conversion_odm := qpr_sr_util.uom_conv(l_uom_code,
2418 i_inventory_item_id, null);
2419 else
2420 l_uom_conversion_odm := 0;
2421 end if;
2422
2423 l_transf_group_id := to_number(nvl(fnd_profile.value('QPR_VOL_BAND_DEAL'),0));
2424
2425 if l_uom_conversion_odm < 0 then
2426 l_vol_band := null;
2427 else
2428 l_vol_band := qpr_deal_pvt.get_volume_band(errbuf, retcode,
2429 i_inventory_item_id,
2430 i_ordered_qty * l_uom_conversion_odm,
2431 l_transf_group_id);
2432 end if;
2433
2434 log_debug('Volume band: '|| l_vol_band);
2435
2436 delete qpr_pn_policies where pn_policy_id in(
2437 select pol.pn_policy_id
2438 from qpr_pn_policies pol,qpr_pn_prices pr
2439 where pr.pn_price_id = pol.pn_price_id
2440 and pr.response_header_id = i_response_header_id
2441 and pr.pn_line_id = i_line_id);
2442
2443 insert_policy_details(i_date, i_pr_segment_id, l_vol_band, i_line_id,
2444 i_list_price,l_deal_currency, i_ordered_qty, true);
2445
2446 end if;
2447
2448 select nvl(min(p.policy_price) , 0) into l_floor_margin
2449 from qpr_pn_policies p, qpr_pn_prices pric, qpr_pn_pr_types t
2450 where pric.pn_line_id = i_line_id
2451 and p.pn_price_id = pric.pn_price_id
2452 and pric.pn_pr_type_id = t.pn_pr_type_id
2453 and t.price_type_name = 'POCMARGIN';
2454
2455 o_line_score := score_calc(i_list_price, i_unit_cost,
2456 l_floor_margin, i_pock_margin,
2457 i_inv_price, i_recommended_price);
2458
2459 exception
2460 when others then
2461 o_line_score := 0;
2462 retcode := 2;
2463 errbuf := sqlerrm;
2464 end; -- calculate score
2465
2466
2467 END QPR_DEAL_ETL ;
2468