[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_price_adj_recs(
p_response_hdr_id in number,
p_pn_line_id in number,
p_src_ref_line_id in number,
p_src_ref_hdr_id in number,
p_src_id in number,
p_top_mdl_src_line_id in number default null,
p_mdl_qty in number default null
) is
cursor c_adj is
select EROSION_TYPE, EROSION_NAME, EROSION_DESC, EROSION_PER_UNIT,
erosion_amount
from qpr_pn_int_pr_adjs
where source_ref_line_id = p_src_ref_line_id
and source_ref_hdr_id = p_src_ref_hdr_id
and source_id = p_src_id;
select a.pn_line_id, 'ALL_' || erosion_type,
decode(erosion_type, 'ONINVOICE', qpr_sr_util.get_all_adj_pk,
'COST', qpr_sr_util.get_all_cos_pk,
'OFFINVOICE',
qpr_sr_util.get_all_oad_pk, qpr_sr_util.get_null_pk) erosion_name,
decode(erosion_type, 'ONINVOICE', qpr_sr_util.get_all_adj_desc,
'COST', qpr_sr_util.get_all_cos_desc,
'OFFINVOICE', qpr_sr_util.get_all_oad_desc, qpr_sr_util.get_null_desc)
erosion_desc,
decode(sum(l.ordered_qty), 0 ,0, sum(erosion_amount) * count(erosion_type)/sum(l.ordered_qty)),
sum(erosion_amount)
from qpr_pn_pr_details a, qpr_pn_lines l
where a.response_header_id = p_response_hdr_id
and a.pn_line_id = l.pn_line_id
group by a.erosion_type, a.pn_line_id;
select erosion_type,
erosion_name, erosion_desc, null,
sum(erosion_amount)
from qpr_pn_pr_details
where response_header_id = p_response_hdr_id
group by erosion_type,erosion_name, erosion_desc;
select
erosion_type, erosion_name, erosion_desc,
decode(nvl(p_mdl_qty,0), 0, 0, sum(a.erosion_amount)/p_mdl_qty) as unit_adj,
decode(nvl(p_mdl_qty,0), 0, 0, sum(a.erosion_amount)) as erosion_amount
from qpr_pn_pr_details a, qpr_pn_lines l
where l.parent_pn_line_id= p_top_mdl_src_line_id
and l.response_header_id = p_response_hdr_id
and a.pn_line_id = l.pn_line_id
group by erosion_type, erosion_name, erosion_desc;
b_insert boolean := true;
log_debug('Inserting line total adjustments per erosion type...');
insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
RESPONSE_HEADER_ID,
PN_LINE_ID,
EROSION_TYPE,
EROSION_NAME,
EROSION_DESC,
EROSION_PER_UNIT,
erosion_amount,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(qpr_pn_pr_details_s.nextval,
p_response_hdr_id,
t_line_id(i),
t_er_typ(i),
t_er_name(i),
t_er_desc(i),
t_er_val(i),
t_tot_er(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
t_line_id.delete;
t_er_typ.delete;
t_er_name.delete;
t_er_desc.delete;
t_er_val.delete;
t_tot_er.delete;
log_debug('Inserting header adjustment values by Rolling up line values..');
log_debug('inserting adjustments for rolled up model');
log_debug('inserting adjustment for quote line:' || p_src_ref_line_id);
insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
RESPONSE_HEADER_ID,
PN_LINE_ID,
EROSION_TYPE,
EROSION_NAME,
EROSION_DESC,
EROSION_PER_UNIT,
erosion_amount,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(qpr_pn_pr_details_s.nextval,
p_response_hdr_id,
p_pn_line_id,
t_er_typ(i),
t_er_name(i),
t_er_desc(i),
t_er_val(i),
t_tot_er(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
log_debug('Inserted ' || sql%rowcount || ' adjustment rows in qpr_pn_pr_details');
t_er_typ.delete;
t_er_name.delete;
t_er_desc.delete;
t_er_val.delete;
t_tot_er.delete;
end insert_price_adj_recs;
function insert_prices(
p_response_hdr_id in number,
p_pn_line_id in number,
p_src_ref_line_id in number default null,
p_src_ref_hdr_id in number default null,
p_src_id in number default null,
p_mdl_qty in number default null
) return number is
l_tot_erosion number := 0;
select pn_pr_type_id, price_type_name, derived_from_type,
erosion_type, column_name
from qpr_pn_pr_types
order by sequence_no;
select pr1.pn_pr_type_id, sum(pr1.amount) amount,
decode(sum(pr2.amount),0,0, 100 * sum(pr1.amount)/sum(pr2.amount))
percent_price,
decode(sum(l.ordered_qty), 0, 0, sum(pr1.amount)* count(l.pn_line_id)/sum(l.ordered_qty)) unit_price
from qpr_pn_prices pr1, qpr_pn_prices pr2, qpr_pn_lines l, qpr_pn_pr_types prt
where pr1.response_header_id = p_response_hdr_id
and pr1.response_header_id = l.response_header_id
and pr1.pn_line_id = l.pn_line_id
and pr1.response_header_id = pr2.response_header_id
and pr1.pn_line_id = pr2.pn_line_id
and pr2.pn_pr_type_id = prt.pn_pr_type_id
and prt.price_type_name = 'LISTPRICE'
group by pr1.pn_pr_type_id;
log_debug('Inserting header price values by rolling up line values');
insert into qpr_pn_prices(PN_PRICE_ID,
RESPONSE_HEADER_ID,
PN_LINE_ID,
PN_PR_TYPE_ID,
UNIT_PRICE,
AMOUNT,
PERCENT_PRICE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(
qpr_pn_prices_s.nextval,
p_response_hdr_id, null,
r_hdr_pric.pn_pr_type_id,
r_hdr_pric.unit_price,
r_hdr_pric.amount,
r_hdr_pric.percent_price,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
l_sql := ' select sum( ' || r_pr_types.column_name || ' * ordered_qty) '
|| ' from qpr_pn_int_lines '
|| ' where top_mdl_src_line_id = :1 and source_ref_hdr_id = :2 '
||' and source_id = :3 and pn_req_line_status_flag = ''I'' ' ;
l_sql := 'select ' || r_pr_types.column_name || ' ,ordered_qty '
|| ' from qpr_pn_int_lines '
|| ' where source_ref_line_id = :1 and source_ref_hdr_id = :2 '
|| ' and source_id = :3 and pn_req_line_status_flag = ''I'' and rownum < 2';
select nvl(sum(erosion_amount),0), nvl(sum(erosion_per_unit), 0)
into l_tot_erosion,l_unit_er
from qpr_pn_pr_details
where pn_line_id = p_pn_line_id
and erosion_type = r_pr_types.erosion_type;
insert into qpr_pn_prices(PN_PRICE_ID,
RESPONSE_HEADER_ID,
PN_LINE_ID,
PN_PR_TYPE_ID,
UNIT_PRICE,
AMOUNT,
PERCENT_PRICE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(
qpr_pn_prices_s.nextval,
p_response_hdr_id,
p_pn_line_id,
r_pr_types.pn_pr_type_id,
l_price, l_amount,l_price_perc,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
log_debug('Inserted Price Type = ' || r_pr_types.pn_pr_type_id
|| ' ;Unit Price = ' || l_price);
log_debug('Failed to insert prices');
end insert_prices;
procedure insert_policy_details(
p_deal_date in date,
p_pr_segment_id in varchar2,
p_vlb_id in varchar2,
p_pn_line_id in number,
p_list_price in number,
p_deal_curr in varchar2,
p_ordered_qty in number,
p_fetch_pol in boolean,
p_pol_ref_line_id in number default null
) is
rec_pn_pol_ins qpr_pn_policy_type;
select p.pn_price_id
from qpr_pn_prices p, qpr_pn_pr_types t
where p.pn_pr_type_id = t.pn_pr_type_id
and p.pn_line_id = p_pn_line_id
and t.erosion_type = p_policy_meas_type
and rownum < 2;
select p.policy_line_id, p.policy_id, pl.policy_type_code, pl.policy_measure_type_code,
pl.limit_value_type_code,
pl.ref_limit_value, null, null
from qpr_pn_policies p , qpr_pn_prices pr, qpr_policy_lines pl
where p.pn_price_id = pr.pn_price_id
and pr.pn_line_id = p_pol_ref_line_id
and p.policy_id = pl.policy_id
and p.policy_line_id = pl.policy_line_id;
g_t_pol_det.delete;
insert into qpr_pn_policies(PN_POLICY_ID,
PN_PRICE_ID,
POLICY_ID,
POLICY_LINE_ID,
POLICY_PRICE,
POLICY_AMOUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(qpr_pn_policies_s.nextval,
rec_pn_pol_ins.pn_price_id(i),
rec_pn_pol_ins.policy_id(i),
rec_pn_pol_ins.policy_line_id(i),
rec_pn_pol_ins.policy_price(i),
rec_pn_pol_ins.policy_amount(i),
sysdate,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
log_debug('Inserted ' || sql%rowcount || ' policy records');
end insert_policy_details;
select nvl(p.unit_price, 0) price, t.price_type_name
from qpr_pn_prices p, qpr_pn_pr_types t
where p.pn_line_id = p_pn_line_id
and p.pn_pr_type_id = t.pn_pr_type_id;
select nvl(sum(p.erosion_per_unit),0)
into l_cost
from qpr_pn_pr_details p
where p.pn_line_id = p_pn_line_id
and p.erosion_type = 'COST';
select nvl(min(p.policy_price) , 0) into l_floor_mrg
from qpr_pn_policies p, qpr_pn_prices pric, qpr_pn_pr_types t
where pric.pn_line_id = p_pn_line_id
and p.pn_price_id = pric.pn_price_id
and pric.pn_pr_type_id = t.pn_pr_type_id
and t.price_type_name = 'POCMARGIN';
procedure insert_model_lines(p_response_id number, p_deal_date date) is
cursor c_mdl_lines is
select *
from qpr_pn_lines
where response_header_id = p_response_id
and item_type_code in ('MDL', 'KIT');
select pn_line_id into l_line_id
from qpr_pn_lines
where source_ref_line_id = c_mdl_lines_rec.source_ref_line_id
and source_ref_hdr_id = c_mdl_lines_rec.source_ref_hdr_id
and source_id = c_mdl_lines_rec.source_id
and response_header_id = p_response_id
and item_type_code = 'DUMMY_PARENT'
and rownum < 2;
select decode(sum(nvl(pr.amount, 0)), 0, 0,
sum(nvl(l.line_pricing_score,0) * nvl(pr.amount,0))/
sum(nvl(pr.amount,0))) ,
sum(PROPOSED_PRICE * REVISED_OQ),
sum(RECOMMENDED_PRICE * REVISED_OQ),
sum(nvl(REGRESSION_INTERCEPT,0) *
(case when (qpr_sr_util.ods_uom_conv(
l.inventory_item_id,
l.UOM_CODE,
pp.base_uom_code, pp.instance_id, null) < 0) then
0 else qpr_sr_util.ods_uom_conv(
l.inventory_item_id,
l.UOM_CODE,
pp.base_uom_code, pp.instance_id, null) end)
* REVISED_OQ),
min(pp.base_uom_code), min(pp.currency_code)
into l_LINE_PRICING_SCORE ,
l_PROPOSED_PRICE,
l_recommended_price,
l_regression_intercept,
l_aw_uom, l_aw_curr
from qpr_pn_lines l, qpr_pn_prices pr, qpr_pn_pr_types prt,
qpr_price_plans_b pp
where l.parent_pn_line_id= c_mdl_lines_rec.source_ref_line_id
and l.response_header_id = p_response_id
and l.response_header_id = pr.response_header_id
and l.pn_line_id = pr.pn_line_id
and l.price_plan_id = pp.price_plan_id
and pr.pn_pr_type_id = prt.pn_pr_type_id
and prt.price_type_name = 'LISTPRICE';
update qpr_pn_lines set PROPOSED_PRICE = l_PROPOSED_PRICE,
RECOMMENDED_PRICE = l_recommended_price,
REGRESSION_SLOPE = l_regression_slope,
LINE_PRICING_SCORE = l_LINE_PRICING_SCORE
where pn_line_id = l_line_id;
insert_price_adj_recs(p_response_id, l_line_id,null, null, null,
c_mdl_lines_rec.source_ref_line_id,
c_mdl_lines_rec.revised_oq);
l_list_price := insert_prices(p_response_id, l_line_id,
c_mdl_lines_rec.source_ref_line_id,
c_mdl_lines_rec.source_ref_hdr_id,
c_mdl_lines_rec.source_id,
c_mdl_lines_rec.revised_oq);
insert_policy_details(null, null,null,
l_line_id,
l_list_price,
c_mdl_lines_rec.currency_code,
c_mdl_lines_rec.ordered_qty,
false,
c_mdl_lines_rec.pn_line_id);
log_debug('Update parent_pn_line_id for child lines of model...');
update qpr_pn_lines
set parent_pn_line_id = l_line_id
where (parent_pn_line_id= c_mdl_lines_rec.source_ref_line_id
or pn_line_id = c_mdl_lines_rec.pn_line_id)
and pn_line_id <> l_line_id
and response_header_id = p_response_id;
log_debug('No of lines updated: '||sql%rowcount);
log_debug('failed in inserting model line');
procedure insert_req_res_header_lines(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_src_ref_hdr_id in number,
p_int_header_id in number,
p_simulation in varchar2 ,
p_response_id out nocopy number,
p_is_deal_compliant out nocopy varchar2) is
cursor c_int_header is
select *
from qpr_pn_int_headers
where pn_int_header_id = p_int_header_id;
select *
from qpr_pn_int_lines
where source_ref_hdr_id = p_src_ref_hdr_id
and source_id = p_src_id
and pn_req_line_status_flag = 'I'
order by pn_int_line_id;
select * from qpr_pn_lines
where response_header_id = p_resp_hdr_id
and item_type_code <> 'DUMMY_PARENT';
insert into qpr_pn_request_hdrs_b (REQUEST_HEADER_ID,
REQUEST_STATUS,
PN_INT_HEADER_ID,
INSTANCE_ID,
CURRENCY_SHORT_DESC,
CURRENCY_LONG_DESC,
SOURCE_ID,
SOURCE_SHORT_DESC,
SOURCE_LONG_DESC,
SOURCE_REF_HDR_ID,
SOURCE_REF_HDR_SHORT_DESC,
SOURCE_REF_HDR_LONG_DESC,
CUSTOMER_ID,
CUSTOMER_SK,
CUSTOMER_SHORT_DESC,
CUSTOMER_LONG_DESC,
SALES_REP_ID,
SALES_REP_SK,
SALES_REP_SHORT_DESC,
SALES_REP_LONG_DESC,
SALES_REP_EMAIL,
SALES_CHANNEL_CODE,
SALES_CHANNEL_SK,
SALES_CHANNEL_SHORT_DESC,
SALES_CHANNEL_LONG_DESC,
FREIGHT_TERMS_SHORT_DESC,
FREIGHT_TERMS_LONG_DESC,
DEAL_EXPIRY_DATE,
DEAL_CREATION_DATE,
INVOICE_TO_PARTY_SITE_ID,
INVOICE_TO_PARTY_SITE_ADDRESS,
SIMULATION_FLAG,
COMMENTS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values (
qpr_pn_request_hdrs_s.nextval,'ACTIVE',
p_int_header_id,
l_deal_instance,
c_int_header_rec.CURRENCY_CODE,
c_int_header_rec.CURRENCY_LONG_DESC,
c_int_header_rec.SOURCE_ID,
c_int_header_rec.SOURCE_SHORT_DESC,
c_int_header_rec.SOURCE_LONG_DESC,
c_int_header_rec.SOURCE_REF_HEADER_ID,
c_int_header_rec.SOURCE_REF_HEADER_SHORT_DESC,
c_int_header_rec.SOURCE_REF_HEADER_LONG_DESC,
c_int_header_rec.CUSTOMER_ID,
nvl2(c_int_header_rec.CUSTOMER_ID,
'TRADING_PARTNER_L_'||c_int_header_rec.CUSTOMER_ID,
null),
c_int_header_rec.CUSTOMER_SHORT_DESC,
c_int_header_rec.CUSTOMER_LONG_DESC,
c_int_header_rec.SALES_REP_ID,
nvl2(c_int_header_rec.SALES_REP_ID,
'SALES_REP_L_'||c_int_header_rec.SALES_REP_ID, null),
c_int_header_rec.SALES_REP_SHORT_DESC,
c_int_header_rec.SALES_REP_LONG_DESC,
c_int_header_rec.SALES_REP_EMAIL_ADDRESS,
c_int_header_rec.SALES_CHANNEL_CODE,
nvl2(c_int_header_rec.SALES_CHANNEL_CODE,
'SALES_CHANNEL_L_'||c_int_header_rec.SALES_CHANNEL_CODE,null),
c_int_header_rec.SALES_CHANNEL_SHORT_DESC,
c_int_header_rec.SALES_CHANNEL_LONG_DESC,
c_int_header_rec.FREIGHT_TERMS_SHORT_DESC,
c_int_header_rec.FREIGHT_TERMS_LONG_DESC,
c_int_header_rec.PN_REQ_EXPIRY_DATE,
c_int_header_rec.PN_REQ_HEADER_CREATION_DATE,
c_int_header_rec.INVOICE_TO_PARTY_SITE_ID,
c_int_header_rec.INVOICE_TO_PARTY_SITE_ADDRESS,
p_simulation,
c_int_header_rec.COMMENTS,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID
) returning REQUEST_HEADER_ID into l_request_id;
log_debug('Inserted Request header: ' || l_request_id || '-'
|| l_reference_name);
insert into qpr_pn_request_hdrs_tl (
LANGUAGE,
REQUEST_HEADER_ID,
REFERENCE_NAME,
SOURCE_LANG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
select L.LANGUAGE_CODE ,l_request_id,
l_reference_name,
userenv('LANG'),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B');
log_debug('Inserted Request header TL values');
insert into qpr_pn_response_hdrs(
RESPONSE_HEADER_ID,
REQUEST_HEADER_ID,
OWNER_ID,
DEAL_HEADER_SCORE,
RESPONSE_STATUS,
PARENT_RESPONSE_ID,
DEAL_LAST_UPDATED_BY,
DEAL_LAST_UPDATE_DATE,
COMMENTS,
VERSION_NUMBER,
BOOKMARK_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values
(qpr_pn_response_hdrs_s.nextval,
l_request_id, fnd_global.user_id,
null,
'APPROVE_REQ',
null,
fnd_global.user_id,
sysdate,
null,
1,
'N',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID)
returning RESPONSE_HEADER_ID into l_response_id;
log_debug('Inserted Response Id: '||l_response_id);
log_debug('Inserting Pn_lines...');
log_debug('Inserting line: Source line id = '|| int_lines_rec.source_ref_line_id);
log_debug('Inserting rolled up model for model/kit line');
insert into qpr_pn_lines(PN_LINE_ID,
RESPONSE_HEADER_ID,
REQUEST_HEADER_ID,
PRICE_PLAN_ID,
SOURCE_REF_LINE_ID,
SOURCE_REQUEST_LINE_NUMBER,
SOURCE_REF_HDR_ID,
SOURCE_ID,
ORG_ID,
INVENTORY_ITEM_ID,
PAYMENT_TERM_ID,
PARENT_PN_LINE_ID,
GEOGRAPHY_ID,
UOM_CODE,
CURRENCY_CODE,
ITEM_TYPE_CODE, ORDERED_QTY,
COMPETITOR_PRICE,
PROPOSED_PRICE,
ORG_DIM_SK,
ORG_LONG_DESC,
ORG_SHORT_DESC,
COMPETITOR_NAME,
REVISED_OQ,
PRODUCT_DIM_SK,
INVENTORY_ITEM_SHORT_DESC,
INVENTORY_ITEM_LONG_DESC,
VOL_BAND_SK,
GEOGRAPHY_SK,
GEOGRAPHY_SHORT_DESC,
GEOGRAPHY_LONG_DESC,
PAYMENT_TERM_SHORT_DESC,
PAYMENT_TERM_LONG_DESC,
UOM_SHORT_DESC,
CURRENCY_SHORT_DESC,
COMMENTS, ADDITIONAL_INFORMATION,
SHIP_METHOD_CODE,
SHIP_METHOD_SHORT_DESC,
SHIP_METHOD_LONG_DESC,
DATAMART_NAME,
REGRESSION_SLOPE,
REGRESSION_INTERCEPT,
RECOMMENDED_PRICE,
PR_SEGMENT_ID,
PR_SEGMENT_SK,
ORIG_PAYMENT_TERM_ID,
ORIG_SHIP_METHOD_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY)
values(QPR_PN_LINES_S.nextval,
l_response_id,
l_request_id,
int_lines_rec.PRICE_PLAN_ID,
int_lines_rec.SOURCE_REF_LINE_ID,
l_line_num,
int_lines_rec.SOURCE_REF_HDR_ID,
int_lines_rec.SOURCE_ID,
int_lines_rec.ORG_ID,
int_lines_rec.INVENTORY_ITEM_ID,
int_lines_rec.PAYMENT_TERM_ID,
null,
int_lines_rec.GEOGRAPHY_ID,
int_lines_rec.UOM_CODE,
int_lines_rec.CURRENCY_CODE,
'DUMMY_PARENT',
int_lines_rec.ORDERED_QTY,
int_lines_rec.COMPETITOR_PRICE,
0,
nvl2(int_lines_rec.ORG_ID,
'OPERATING_UNIT_L_'||int_lines_rec.ORG_ID,null),
int_lines_rec.ORG_LONG_DESC,
int_lines_rec.ORG_SHORT_DESC,
int_lines_rec.COMPETITOR_NAME,
int_lines_rec.ORDERED_QTY,
'MODEL_L_'||int_lines_rec.INVENTORY_ITEM_ID,
int_lines_rec.INVENTORY_ITEM_SHORT_DESC,
int_lines_rec.INVENTORY_ITEM_LONG_DESC,
int_lines_rec.VOL_BAND_SK,
nvl2(int_lines_rec.GEOGRAPHY_ID,
'TRADING_PARTNER_SITE_L_'||int_lines_rec.GEOGRAPHY_ID,
null),
int_lines_rec.GEOGRAPHY_SHORT_DESC,
int_lines_rec.GEOGRAPHY_LONG_DESC,
int_lines_rec.PAYMENT_TERM_SHORT_DESC,
int_lines_rec.PAYMENT_TERM_LONG_DESC,
int_lines_rec.UOM_SHORT_DESC,
int_lines_rec.CURRENCY_SHORT_DESC,
int_lines_rec.COMMENTS,
int_lines_rec.ADDITIONAL_INFORMATION,
int_lines_rec.SHIP_METHOD_CODE,
int_lines_rec.SHIP_METHOD_SHORT_DESC,
int_lines_rec.SHIP_METHOD_LONG_DESC,
int_lines_rec.datamart_name,
0,
int_lines_rec.regression_intercept,
0,
int_lines_rec.pr_segment_id,
nvl2(int_lines_rec.pr_Segment_id,
'PR_SEGMENT_L_' || int_lines_rec.pr_segment_id, null),
int_lines_rec.PAYMENT_TERM_ID,
int_lines_rec.SHIP_METHOD_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID)
returning PN_LINE_ID into l_line_id;
log_debug('Inserted rolled up model' || l_line_id);
select base_uom_code, currency_code
into l_aw_uom, l_aw_curr
from qpr_price_plans_b
where price_plan_id = int_lines_rec.PRICE_PLAN_ID
and rownum < 2;
insert into qpr_pn_lines(PN_LINE_ID,
RESPONSE_HEADER_ID,
REQUEST_HEADER_ID,
PRICE_PLAN_ID,
SOURCE_REF_LINE_ID,
SOURCE_REQUEST_LINE_NUMBER,
SOURCE_REF_HDR_ID,
SOURCE_ID,
ORG_ID,
INVENTORY_ITEM_ID,
PAYMENT_TERM_ID,
PARENT_PN_LINE_ID,
GEOGRAPHY_ID,
UOM_CODE,
CURRENCY_CODE,
ITEM_TYPE_CODE, ORDERED_QTY,
COMPETITOR_PRICE,
PROPOSED_PRICE,
ORG_DIM_SK,
ORG_LONG_DESC,
ORG_SHORT_DESC,
COMPETITOR_NAME,
REVISED_OQ,
PRODUCT_DIM_SK,
INVENTORY_ITEM_SHORT_DESC,
INVENTORY_ITEM_LONG_DESC,
VOL_BAND_SK,
GEOGRAPHY_SK,
GEOGRAPHY_SHORT_DESC,
GEOGRAPHY_LONG_DESC,
PAYMENT_TERM_SHORT_DESC,
PAYMENT_TERM_LONG_DESC,
UOM_SHORT_DESC,
CURRENCY_SHORT_DESC,
COMMENTS, ADDITIONAL_INFORMATION,
SHIP_METHOD_CODE,
SHIP_METHOD_SHORT_DESC,
SHIP_METHOD_LONG_DESC,
DATAMART_NAME,
REGRESSION_SLOPE,
REGRESSION_INTERCEPT,
RECOMMENDED_PRICE,
PR_SEGMENT_ID,
PR_SEGMENT_SK,
ORIG_PAYMENT_TERM_ID,
ORIG_SHIP_METHOD_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY)
values(QPR_PN_LINES_S.nextval,
l_response_id,
l_request_id,
int_lines_rec.PRICE_PLAN_ID,
int_lines_rec.SOURCE_REF_LINE_ID,
int_lines_rec.SOURCE_REQUEST_LINE_NUMBER,
int_lines_rec.SOURCE_REF_HDR_ID,
int_lines_rec.SOURCE_ID,
int_lines_rec.ORG_ID,
int_lines_rec.INVENTORY_ITEM_ID,
int_lines_rec.PAYMENT_TERM_ID,
int_lines_rec.TOP_MDL_SRC_LINE_ID,
int_lines_rec.GEOGRAPHY_ID,
int_lines_rec.UOM_CODE,
int_lines_rec.CURRENCY_CODE,
int_lines_rec.ITEM_TYPE_CODE,
int_lines_rec.ORDERED_QTY,
int_lines_rec.COMPETITOR_PRICE,
int_lines_rec.PROPOSED_PRICE,
nvl2(int_lines_rec.ORG_ID,
'OPERATING_UNIT_L_'||int_lines_rec.ORG_ID,null),
int_lines_rec.ORG_LONG_DESC,
int_lines_rec.ORG_SHORT_DESC,
int_lines_rec.COMPETITOR_NAME,
int_lines_rec.ORDERED_QTY,
nvl2(int_lines_rec.INVENTORY_ITEM_ID,
'ITEM_L_'||int_lines_rec.INVENTORY_ITEM_ID, null),
int_lines_rec.INVENTORY_ITEM_SHORT_DESC,
int_lines_rec.INVENTORY_ITEM_LONG_DESC,
int_lines_rec.VOL_BAND_SK,
nvl2(int_lines_rec.GEOGRAPHY_ID,
'TRADING_PARTNER_SITE_L_'||int_lines_rec.GEOGRAPHY_ID,
null),
int_lines_rec.GEOGRAPHY_SHORT_DESC,
int_lines_rec.GEOGRAPHY_LONG_DESC,
int_lines_rec.PAYMENT_TERM_SHORT_DESC,
int_lines_rec.PAYMENT_TERM_LONG_DESC,
int_lines_rec.UOM_SHORT_DESC,
int_lines_rec.CURRENCY_SHORT_DESC,
int_lines_rec.COMMENTS,
int_lines_rec.ADDITIONAL_INFORMATION,
int_lines_rec.SHIP_METHOD_CODE,
int_lines_rec.SHIP_METHOD_SHORT_DESC,
int_lines_rec.SHIP_METHOD_LONG_DESC,
int_lines_rec.datamart_name,
int_lines_rec.regression_slope,
int_lines_rec.regression_intercept,
l_recommend_price,
int_lines_rec.pr_segment_id,
nvl2(int_lines_rec.pr_Segment_id,
'PR_SEGMENT_L_' || int_lines_rec.pr_segment_id, null),
int_lines_rec.PAYMENT_TERM_ID,
int_lines_rec.SHIP_METHOD_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID)
returning PN_LINE_ID into l_line_id;
log_debug('Inserted line: pn_line_id = ' || l_line_id);
insert_price_adj_recs( l_response_id, l_line_id,
int_lines_rec.source_ref_line_id,
int_lines_rec.source_ref_hdr_id,
int_lines_rec.source_id);
l_list_price := insert_prices(l_response_id, lines_rec.pn_line_id,
lines_rec.source_ref_line_id, lines_rec.source_ref_hdr_id,
lines_rec.source_id);
insert_policy_details(
c_int_header_rec.PN_REQ_HEADER_CREATION_DATE,
lines_rec.pr_segment_id,
lines_rec.vol_band_sk,
lines_rec.pn_line_id,
l_list_price,
lines_rec.currency_code,
lines_rec.ordered_qty,true);
update qpr_pn_lines set line_pricing_score = round(l_line_score, 2)
where pn_line_id = lines_rec.pn_line_id;
insert_price_adj_recs(l_response_id, null, null, null,null);
l_list_price := insert_prices(l_response_id, null);
insert_model_lines(l_response_id,
c_int_header_rec.PN_REQ_HEADER_CREATION_DATE);
select round(sum(nvl(l.line_pricing_score,0)*nvl(pr.amount,0))/
sum(nvl(pr.amount,0)), 2)
into l_total_score
from qpr_pn_lines l, qpr_pn_prices pr, qpr_pn_pr_types prt
where l.response_header_id = l_response_id
and pr.response_header_id= l.response_header_id
and pr.pn_line_id = l.pn_line_id
and pr.pn_pr_type_id = prt.pn_pr_type_id
and prt.price_type_name = 'LISTPRICE';
update qpr_pn_response_hdrs
set deal_header_score = round(l_total_score, 2)
where response_header_id = l_response_id;
update qpr_pn_response_hdrs
set response_status = l_response_status
where response_header_id = l_response_id;
end insert_req_res_header_lines;
procedure insert_price_int_adj_recs(p_source_ref_hdr_id in number,
p_source_ref_line_id in number,
p_src_id in number,
p_er_det_rec in qpr_deal_pvt.pn_aw_data_rec)
is
cursor c_offadj(p_sm_code varchar2, p_pt_code varchar2,
p_rbt_code varchar2,
p_sm_oad_val number, p_pt_oad_val number,
p_rbt_oad_val number) is
select er_type, er_name, er_desc, er_val, er_tot_val
from (
select 'OFFINVOICE' er_type,
decode(num, '1', nvl2(p_sm_code, substr(p_sm_code, 12), 'ShippingMethod-'),
'2', nvl2(p_pt_code, substr(p_pt_code, 12), 'PaymentTerm-'),
'3', substr(p_rbt_code, 11)) er_name,
decode(num, '1', l.ship_method_long_desc,
'2', l.payment_term_short_desc,
'3', qpr_sr_util.get_oad_ar_cm_type_desc) er_desc,
decode(nvl(l.ordered_qty,0), 0 , 0, decode(num, '1', p_sm_oad_val, '2', p_pt_oad_val,
'3', p_rbt_oad_val)/l.ordered_qty) er_val,
decode(nvl(l.ordered_qty, 0), 0 , 0, decode(num, '1', p_sm_oad_val, '2', p_pt_oad_val,
'3', p_rbt_oad_val)) er_tot_val
from qpr_pn_int_lines l,
(select rownum num from dual connect by level <=3)
where l.source_ref_hdr_id = p_source_ref_hdr_id
and l.source_ref_line_id = p_source_ref_line_id
and l.source_id = p_src_id)
where er_name is not null;
select pn_int_pr_adj_id,
l.ordered_qty
into l_adj_id, l_ord_qty
from qpr_pn_int_pr_adjs pr, qpr_pn_int_lines l
where pr.source_ref_hdr_id = p_source_ref_hdr_id
and pr.source_ref_line_id = p_source_ref_line_id
and pr.source_id = p_src_id
and pr.source_ref_line_id = l.source_ref_line_id
and pr.source_ref_hdr_id = l.source_ref_hdr_id
and pr.source_id = l.source_id
and erosion_type = 'COST'
and rownum < 2;
update qpr_pn_int_pr_adjs set erosion_per_unit = p_er_det_rec.unit_cost,
erosion_amount = p_er_det_rec.unit_cost * l_ord_qty
where pn_int_pr_adj_id = l_adj_id;
log_debug('updated cost: unit cost' || p_er_det_rec.unit_cost);
delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_source_ref_hdr_id
and source_ref_line_id = p_source_ref_line_id
and source_id = p_src_id
and erosion_type = 'ONINVOICE' and erosion_name = 'QPR_WHATIF';
select meaning into l_er_name
from qpr_lookups where lookup_type = 'QPR_DEAL_EROSIONS'
and lookup_code = 'WHATIF' and rownum < 2;
insert into qpr_pn_int_pr_adjs(PN_INT_PR_ADJ_ID,
SOURCE_REF_HDR_ID,
SOURCE_REF_LINE_ID,
SOURCE_ID,
EROSION_TYPE,
EROSION_NAME,
EROSION_DESC,
EROSION_PER_UNIT,
erosion_amount,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(qpr_pn_int_pr_adjs_s.nextval,
p_source_ref_hdr_id,
p_source_ref_line_id,
p_src_id,
'ONINVOICE',
'QPR_WHATIF',
l_er_name,
0, 0,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
log_debug('Inserted oninvoice modifier QPR_WHATIF for use in whatif');
delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_source_ref_hdr_id
and source_ref_line_id = p_source_ref_line_id
and source_id = p_src_id
and erosion_type = 'OFFINVOICE';
insert into qpr_pn_int_pr_adjs(PN_INT_PR_ADJ_ID,
SOURCE_REF_HDR_ID,
SOURCE_REF_LINE_ID,
SOURCE_ID,
EROSION_TYPE,
EROSION_NAME,
EROSION_DESC,
EROSION_PER_UNIT,
erosion_amount,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(qpr_pn_int_pr_adjs_s.nextval,
p_source_ref_hdr_id,
p_source_ref_line_id,
p_src_id,
t_er_typ(i),
t_er_name(i),
t_er_desc(i),
t_er_val(i),
t_tot_er(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
log_debug('Inserted Offinvoice modifier ' || t_er_name(i)
|| ': Erosion per unit=' || t_er_val(i));
t_er_typ.delete;
t_er_name.delete;
t_er_desc.delete;
t_er_val.delete;
t_tot_er.delete;
end insert_price_int_adj_recs;
select * from qpr_pn_pr_types order by sequence_no;
select nvl(sum(erosion_per_unit),0) unit_erosion
from qpr_pn_int_pr_adjs
where source_ref_line_id = p_src_ref_line_id
and source_ref_hdr_id = p_src_ref_hdr_id
and source_id = p_src_id
and erosion_type = p_erosion_type;
l_sql := 'select ' || r_pr_typ.column_name
|| ' from qpr_pn_int_lines'
|| ' where source_ref_hdr_id = :1 and source_ref_line_id = :2'
|| ' and pn_req_line_status_flag = ''I'' and source_id = :3 '
|| ' and rownum < 2';
select * from qpr_pn_int_lines
where source_ref_hdr_id = p_src_ref_hdr_id
and source_id = p_src_id
and pn_req_line_status_flag = 'I';
select * from qpr_pn_int_headers
where pn_int_header_id = p_pn_int_hdr_id
and instance_id = l_deal_instance
and rownum < 2;
select distinct price_plan_id
from qpr_pn_int_lines
where source_ref_hdr_id = p_hdr_id
and source_id = p_src_id;
select * from qpr_pn_int_lines
where source_ref_hdr_id = p_hdr_id
and source_id = p_src_id
and price_plan_id = nvl(p_price_plan_id, price_plan_id);
select nvl(regression_slope,0), nvl(regression_intercept ,0)
into l_slope, l_intercept
from qpr_regression_result
where price_plan_id = l_datamart_id
and product_id = int_lines_rec.inventory_item_id
and pr_segment_id = l_pr_segment_id;
update qpr_pn_int_lines
set price_plan_id = l_datamart_id,
datamart_name = l_aw_name,
vol_band_sk = l_vol_band,
pr_segment_id = l_pr_segment_id,
regression_slope = l_slope,
regression_intercept = l_intercept
where pn_int_line_id = int_lines_rec.pn_int_line_id;
log_debug('inserting/updating adjustment values for:'
|| g_t_aw_det(k).pn_line_id);
insert_price_int_adj_recs(p_src_ref_hdr_id,
g_t_aw_det(k).pn_line_id,
c_int_header_rec.source_id,
g_t_aw_det(k));
g_t_aw_det.delete;
select source_ref_header_id, pn_int_header_id, instance_id, source_id
from qpr_pn_int_headers
where request_id = l_request_id;
update qpr_pn_int_headers rih
set rih.request_id = l_request_id
where rih.source_ref_header_id between
nvl(f_source_ref_id, rih.source_ref_header_id)
and nvl(t_source_ref_id, rih.source_ref_header_id)
and ((reprocess = 'N' and rih.pn_req_header_status_flag = 'I')
or (reprocess = 'Y' and rih.pn_req_header_status_flag = 'F'));
select 1 into l_count_lines
from qpr_pn_int_lines
where source_ref_hdr_id = t_src_hdr_id(i)
and source_id = t_src_id(i)
and pn_req_line_status_flag = 'I' and rownum < 2;
insert_req_res_header_lines(errbuf, retcode,
t_src_hdr_id(i),
t_pn_int_hdr(i),
'N',
l_response_id,
l_deal_compliant);
update qpr_pn_int_headers
set request_id = null,
pn_req_header_status_flag = 'F'
where pn_int_header_id = t_pn_int_hdr(i);
delete qpr_pn_int_headers where pn_int_header_id = t_pn_int_hdr(i);
delete qpr_pn_int_lines where source_ref_hdr_id = t_src_hdr_id(i)
and source_id = t_src_id(i);
delete qpr_pn_int_pr_adjs where source_ref_hdr_id = t_src_hdr_id(i)
and source_id = t_src_id(i);
select source_ref_header_id, pn_int_header_id, instance_id
from qpr_pn_int_headers
where source_ref_header_id = p_quote_header_id
and source_id = p_source_id
and instance_id = p_instance_id
and pn_req_header_status_flag = 'I'
and rownum < 2;
select distinct rule_description
from qpr_pn_response_approvals
where response_header_id = p_resp_hdr_id;
select 1 into l_count_lines
from qpr_pn_int_lines
where source_ref_hdr_id = l_src_hdr_id
and source_id = p_source_id
and pn_req_line_status_flag = 'I' and rownum < 2;
insert_req_res_header_lines(errbuf, retcode,
l_src_hdr_id,
l_pn_int_hdr,p_simulation,
p_response_id,
p_is_deal_compliant
);
delete qpr_pn_int_headers where pn_int_header_id = l_pn_int_hdr;
delete qpr_pn_int_lines where source_ref_hdr_id = l_src_hdr_id
and source_id = p_source_id;
delete qpr_pn_int_pr_adjs where source_ref_hdr_id = l_src_hdr_id
and source_id = p_source_id;
select * from qpr_pn_lines
where response_header_id = p_response_hdr_id;
select pr.erosion_type, pr.erosion_name, pr.erosion_desc,
pr.erosion_per_unit,pr.erosion_amount, nl.pn_line_id
from qpr_pn_pr_details pr, qpr_pn_lines ol, qpr_pn_lines nl
where
pr.response_header_id = p_response_hdr_id
and pr.response_header_id = ol.response_header_id(+)
and pr.pn_line_id = ol.pn_line_id(+)
and nl.response_header_id(+) = p_new_resp_hdr_id
and ol.request_header_id = nl.request_header_id(+)
and ol.source_ref_line_id = nl.source_ref_line_id(+)
and ol.item_type_code = nl.item_type_code(+);
select o.pn_pr_type_id, o.unit_price, o.amount,o.percent_price,
nl.pn_line_id
from qpr_pn_prices o, qpr_pn_lines nl, qpr_pn_lines ol
where o.response_header_id = p_response_hdr_id
and ol.response_header_id(+) = o.response_header_id
and ol.pn_line_id(+) = o.pn_line_id
and nl.response_header_id(+) = p_new_resp_hdr_id
and ol.request_header_id = nl.request_header_id(+)
and ol.source_ref_line_id = nl.source_ref_line_id(+)
and ol.item_type_code = nl.item_type_code(+);
select
op.POLICY_ID,
op.POLICY_PRICE,
op.POLICY_AMOUNT,
op.policy_line_id,
n.pn_price_id
from qpr_pn_policies op,
qpr_pn_prices o, qpr_pn_lines ol,
qpr_pn_lines nl, qpr_pn_prices n
where op.pn_price_id = o.pn_price_id
and o.response_header_id = p_response_hdr_id
and ol.response_header_id = o.response_header_id
and ol.pn_line_id = o.pn_line_id
and nl.response_header_id = p_new_resp_hdr_id
and ol.request_header_id = nl.request_header_id
and ol.source_ref_line_id = nl.source_ref_line_id
and ol.item_type_code = nl.item_type_code
and n.pn_pr_type_id = o.pn_pr_type_id
and nl.response_header_id = n.response_header_id
and nl.pn_line_id = n.pn_line_id;
select request_header_id, version_number, deal_header_score,
description,comments, response_status
into l_request_hdr_id, l_version_no, l_hdr_score,
l_description, l_comments, l_response_stat
from qpr_pn_response_hdrs
where response_header_id = p_response_hdr_id
and rownum < 2;
select nvl(max(version_number), 0) into l_version_no
from qpr_pn_response_hdrs
where request_header_id = l_request_hdr_id;
insert into qpr_pn_response_hdrs(
RESPONSE_HEADER_ID,
REQUEST_HEADER_ID,
DEAL_HEADER_SCORE,
RESPONSE_STATUS,
PARENT_RESPONSE_ID,
DEAL_LAST_UPDATED_BY,
DEAL_LAST_UPDATE_DATE,
OWNER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
COMMENTS,
DESCRIPTION,
VERSION_NUMBER,
BOOKMARK_FLAG
) values
(qpr_pn_response_hdrs_s.nextval,
l_request_hdr_id,
l_hdr_score,
l_response_stat,
p_response_hdr_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
l_comments,
l_description,
l_version_no + 1,
'N')
returning RESPONSE_HEADER_ID into l_response_id;
insert into qpr_pn_lines(PN_LINE_ID,
RESPONSE_HEADER_ID,
REQUEST_HEADER_ID,
PRICE_PLAN_ID,
SOURCE_REF_LINE_ID,
SOURCE_REQUEST_LINE_NUMBER,
SOURCE_REF_HDR_ID,SOURCE_ID, ORG_ID,
INVENTORY_ITEM_ID,
PAYMENT_TERM_ID,
PARENT_PN_LINE_ID,
GEOGRAPHY_ID,
UOM_CODE,
CURRENCY_CODE,
ITEM_TYPE_CODE, ORDERED_QTY,
COMPETITOR_PRICE,
PROPOSED_PRICE,
ORG_DIM_SK,
ORG_LONG_DESC,
ORG_SHORT_DESC,
COMPETITOR_NAME,
REVISED_OQ,
PRODUCT_DIM_SK,
INVENTORY_ITEM_SHORT_DESC,
INVENTORY_ITEM_LONG_DESC,
VOL_BAND_SK,
GEOGRAPHY_SK,
GEOGRAPHY_SHORT_DESC,
GEOGRAPHY_LONG_DESC,
PAYMENT_TERM_SHORT_DESC,
PAYMENT_TERM_LONG_DESC,
UOM_SHORT_DESC,
CURRENCY_SHORT_DESC,
COMMENTS, ADDITIONAL_INFORMATION,
SHIP_METHOD_CODE,
SHIP_METHOD_SHORT_DESC,
SHIP_METHOD_LONG_DESC,
DATAMART_NAME,
PR_SEGMENT_ID,
PR_SEGMENT_SK,
RECOMMENDED_PRICE,
REGRESSION_SLOPE,
REGRESSION_INTERCEPT,
LINE_PRICING_SCORE,
ORIG_PAYMENT_TERM_ID,
ORIG_SHIP_METHOD_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY)
values(QPR_PN_LINES_S.nextval,
l_response_id,
lines_rec.REQUEST_HEADER_ID,
lines_rec.PRICE_PLAN_ID,
lines_rec.SOURCE_REF_LINE_ID,
lines_rec.SOURCE_REQUEST_LINE_NUMBER,
lines_rec.SOURCE_REF_HDR_ID,
lines_rec.SOURCE_ID,
lines_rec.ORG_ID,
lines_rec.INVENTORY_ITEM_ID,
lines_rec.PAYMENT_TERM_ID,
lines_rec.PARENT_PN_LINE_ID,
lines_rec.GEOGRAPHY_ID,
lines_rec.UOM_CODE,
lines_rec.CURRENCY_CODE,
lines_rec.ITEM_TYPE_CODE,
lines_rec.ORDERED_QTY,
lines_rec.COMPETITOR_PRICE,
lines_rec.PROPOSED_PRICE,
lines_rec.ORG_DIM_SK,
lines_rec.ORG_LONG_DESC,
lines_rec.ORG_SHORT_DESC,
lines_rec.COMPETITOR_NAME,
lines_rec.ORDERED_QTY,
lines_rec.PRODUCT_DIM_SK,
lines_rec.INVENTORY_ITEM_SHORT_DESC,
lines_rec.INVENTORY_ITEM_LONG_DESC,
lines_rec.VOL_BAND_SK,
lines_rec.GEOGRAPHY_SK,
lines_rec.GEOGRAPHY_SHORT_DESC,
lines_rec.GEOGRAPHY_LONG_DESC,
lines_rec.PAYMENT_TERM_SHORT_DESC,
lines_rec.PAYMENT_TERM_LONG_DESC,
lines_rec.UOM_SHORT_DESC,
lines_rec.CURRENCY_SHORT_DESC,
lines_rec.COMMENTS,
lines_rec.ADDITIONAL_INFORMATION,
lines_rec.SHIP_METHOD_CODE,
lines_rec.SHIP_METHOD_SHORT_DESC,
lines_rec.SHIP_METHOD_LONG_DESC,
lines_rec.datamart_name,
lines_rec.PR_SEGMENT_ID,
lines_rec.PR_SEGMENT_SK,
lines_rec.RECOMMENDED_PRICE,
lines_rec.REGRESSION_SLOPE,
lines_rec.REGRESSION_INTERCEPT,
lines_rec.LINE_PRICING_SCORE,
lines_rec.ORIG_PAYMENT_TERM_ID,
lines_rec.ORIG_SHIP_METHOD_CODE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
RESPONSE_HEADER_ID,
PN_LINE_ID,
EROSION_TYPE,
EROSION_NAME,
EROSION_DESC,
EROSION_PER_UNIT,
erosion_amount,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(qpr_pn_pr_details_s.nextval,
l_response_id,
t_line_id(i),
t_er_type(i),
t_er_name(i),
t_er_desc(i),
t_unit_val(i),
t_amnt(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
t_line_id.delete;
t_er_type.delete;
t_er_name.delete;
t_er_desc.delete;
t_unit_val.delete;
t_amnt.delete;
insert into qpr_pn_prices(PN_PRICE_ID,
RESPONSE_HEADER_ID,
PN_LINE_ID,
PN_PR_TYPE_ID,
UNIT_PRICE,
AMOUNT,
PERCENT_PRICE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(
qpr_pn_prices_s.nextval,
l_response_id,
t_line_id(i),
t_pr_typ_id(i),
t_unit_val(i), t_amnt(i), t_percent(i),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
t_line_id.delete;
t_unit_val.delete;
t_amnt.delete;
t_percent.delete;
t_pr_typ_id.delete;
insert into qpr_pn_policies(PN_POLICY_ID,
PN_PRICE_ID,
POLICY_ID,
POLICY_PRICE,
POLICY_AMOUNT,
POLICY_LINE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
values(qpr_pn_policies_s.nextval,
t_pr_typ_id(i),
t_pol_id(i),
t_unit_val(i),
t_amnt(i),
t_pol_line_id(i),
sysdate,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID);
t_pr_typ_id.delete;
t_pol_id.delete;
t_pol_line_id.delete;
t_unit_val.delete;
t_amnt.delete;
select l.uom_code, req.instance_id,l.currency_code
into l_uom_code, l_deal_instance, l_deal_currency
from qpr_pn_lines l, qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_b req
where l.pn_line_id = i_line_id
and l.response_header_id = i_response_header_id
and l.response_header_id = resp.response_header_id
and resp.request_header_id = req.request_header_id
and rownum < 2;
delete qpr_pn_policies where pn_policy_id in(
select pol.pn_policy_id
from qpr_pn_policies pol,qpr_pn_prices pr
where pr.pn_price_id = pol.pn_price_id
and pr.response_header_id = i_response_header_id
and pr.pn_line_id = i_line_id);
insert_policy_details(i_date, i_pr_segment_id, l_vol_band, i_line_id,
i_list_price,l_deal_currency, i_ordered_qty, true);
select nvl(min(p.policy_price) , 0) into l_floor_margin
from qpr_pn_policies p, qpr_pn_prices pric, qpr_pn_pr_types t
where pric.pn_line_id = i_line_id
and p.pn_price_id = pric.pn_price_id
and pric.pn_pr_type_id = t.pn_pr_type_id
and t.price_type_name = 'POCMARGIN';