[Home] [Help]
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
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,
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,
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,
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,
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;
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;
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
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';
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
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
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
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
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
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,
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),
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:
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';