[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select source_request_line_number,
nvl((select name from qpr_pr_segments_all_vl
where pr_segment_id = l.pr_segment_id
and rownum < 2), l.pr_segment_id) pr_segment,
l.inventory_item_short_desc,
l.uom_code,
l.ordered_qty,
round(pr.listprice, p_precision),
round(l.proposed_price, p_precision),
l.revised_oq,
round(decode(pr.listpricerev, 0, 0, (100 * pd.totoninv/pr.listpricerev)), p_precision),
round(pr.invprice, p_precision),
round(l.recommended_price, p_precision),
round(pr.invpricerev, p_precision),
round(pd.totoffinv, p_precision),
round(pr.pocpricerev,p_precision ),
round(pd.unit_cost, p_precision),
round(pr.pocmarginamnt, p_precision),
round(pr.pocmarginperc, p_precision),
round(l.line_pricing_score, 2)
from qpr_pn_lines l,
(select response_header_id, pn_line_id,
sum(decode(pn_pr_type_id, 1, amount, 0 )) listpricerev,
sum(decode(pn_pr_type_id, 2, amount, 0 )) invpricerev,
sum(decode(pn_pr_type_id, 3, amount, 0 )) pocpricerev,
sum(decode(pn_pr_type_id, 4, amount, 0 )) pocmarginamnt,
sum(decode(pn_pr_type_id, 1, unit_price, 0 )) listprice,
sum(decode(pn_pr_type_id, 2, unit_price, 0 )) invprice,
sum(decode(pn_pr_type_id, 3, unit_price, 0 )) pocprice,
sum(decode(pn_pr_type_id, 4, unit_price, 0 )) pocmargin,
sum(decode(pn_pr_type_id, 2, percent_price, 0 )) invpriceperc,
sum(decode(pn_pr_type_id, 3, percent_price, 0 )) pocpriceperc,
sum(decode(pn_pr_type_id, 4, percent_price, 0 )) pocmarginperc
from qpr_pn_prices
where pn_line_id is not null
group by response_header_id, pn_line_id) pr,
(select response_header_id, pn_line_id,
sum(decode(erosion_type, 'ALL_COST', erosion_per_unit, 0 )) unit_cost,
sum(decode(erosion_type, 'ALL_ONINVOICE',erosion_per_unit, 0) ) unit_oninv,
sum(decode(erosion_type, 'ALL_OFFINVOICE', erosion_per_unit, 0 )) unit_offinv,
sum(decode(erosion_type, 'ALL_COST', erosion_amount, 0 )) totcost,
sum(decode(erosion_type, 'ALL_ONINVOICE',erosion_amount, 0) ) totoninv,
sum(decode(erosion_type, 'ALL_OFFINVOICE', erosion_amount, 0 )) totoffinv
from qpr_pn_pr_details
where pn_line_id is not null
and erosion_type like 'ALL_%'
group by response_header_id, pn_line_id) pd
where l.response_header_id = pd.response_header_id
and l.pn_line_id = pd.pn_line_id
and l.response_header_id = pr.response_header_id
and l.pn_line_id = pr.pn_line_id
and l.response_header_id = p_response_header_id
order by l.response_header_id, l.pn_line_id;
select round(resp.deal_header_score,2), resp.version_number,
req.reference_name, req.customer_long_desc,
req.sales_rep_long_desc, req.currency_short_desc,
(select meaning from qpr_lookups
where lookup_type = 'PN_STATUS'
and lookup_code = resp.response_status and rownum < 2),
resp.description, req.deal_creation_date , req.request_header_id
into l_hdr_score, l_version_no, l_ref_name,
l_cus_name, l_rep_name, l_hdr_curr, l_hdr_status,
l_description, l_req_date, l_request_hdr_id
from qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_vl req
where resp.response_header_id = p_response_header_id
and resp.request_header_id = req.request_header_id;
t_line_det.LINE_NUM.delete;
t_line_det.PR_SEG_DESC.delete;
t_line_det.PDT_DESC.delete;
t_line_det.UOM.delete;
t_line_det.ORD_QTY.delete;
t_line_det.LISTPRICE.delete;
t_line_det.PROPOSED_PRICE.delete;
t_line_det.REVISED_OQ.delete;
t_line_det.ONINV_PERC.delete;
t_line_det.INVPRICE.delete;
t_line_det.RECOMMEND_PRICE.delete;
t_line_det.INVPRICE_REV.delete;
t_line_det.TOTOFFADJ.delete;
t_line_det.POC_REV.delete;
t_line_det.UNIT_COST.delete;
t_line_det.MARGIN_AMT.delete;
t_line_det.MARGIN_PERC.delete;
t_line_det.LINE_SCORE.delete;
select round(resp.deal_header_score, 2), resp.version_number,
req.reference_name, req.customer_long_desc,
req.sales_rep_long_desc, req.currency_short_desc,
(select meaning from qpr_lookups
where lookup_type = 'PN_STATUS'
and lookup_code = resp.response_status and rownum < 2),
resp.description, req.deal_creation_date ,
resp.request_header_id
into l_hdr_score, l_version_no, l_ref_name,
l_cus_name, l_rep_name, l_hdr_curr, l_hdr_status,
l_description, l_req_date, l_request_hdr_id
from qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_vl req
where resp.response_header_id = l_resp_hdr_id
and resp.request_header_id = req.request_header_id;
select user_name into l_current_user
from fnd_user
where user_id = fnd_global.user_id;
select request_header_id, version_number
into l_request_hdr_id, l_version_no
from qpr_pn_response_hdrs
where response_header_id = p_response_id
and rownum < 2;
select item_key, end_date
into l_item_key, l_end_date
from
(select item_key, end_date
from wf_items where item_type = QPR_NTFN_ITM_TYPE
and item_key like (l_init_key || '%')
order by begin_date desc)
where rownum < 2;
select item_key into l_item_key
from wf_items
where item_type = QPR_NTFN_ITM_TYPE
and item_key like (l_init_key || '%')
and end_date is null
and rownum < 2;
select pa.activity_name into l_activity_name
from WF_ITEM_ACTIVITY_STATUSES act, wf_process_activities pa
where act.item_type = QPR_NTFN_ITM_TYPE
and act.item_key = l_item_key
and act.activity_status = 'NOTIFIED'
and act.process_activity = pa.instance_id
and rownum < 2;
select item_key into l_item_key
from wf_items
where item_type = QPR_NTFN_ITM_TYPE
and item_key like (l_init_key || '%')
and end_date is null
and rownum < 2;
select user_name into l_current_user
from fnd_user
where user_id = fnd_global.user_id;
select request_header_id, version_number
into l_request_hdr_id, l_version_no
from qpr_pn_response_hdrs
where response_header_id = p_response_id
and rownum < 2;
g_cb_usr_tbl.delete;
select user_name into l_current_user
from fnd_user
where user_id = fnd_global.user_id;
select request_header_id, version_number
into l_request_hdr_id, l_version_no
from qpr_pn_response_hdrs
where response_header_id = p_response_id
and rownum < 2;
g_apst_usr_tbl.delete;
select meaning into l_status from qpr_lookups where lookup_type = 'AME_STATUS'
and lookup_code = p_status;
select meaning into l_status from qpr_lookups where lookup_type = 'AME_STATUS'
and lookup_code = p_status;