[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select bl.po_header_id,b.vendor_id,bl.po_line_id,bl.price_break_lookup_code
,bl.unit_price,bl.min_release_amount bl_min,bl.expiration_date
,b.min_release_amount b_min,b.amount_limit , b.global_agreement_flag
,0 line_qty,0 line_all_qty, gl.currency_code bl_func_cur_code, b.rate bl_rate
from po_lines_all bl
,po_headers_all b
,financials_system_params_all fsp
,gl_sets_of_books gl
where bl.item_id = p_item_id
and bl.price_break_lookup_code is not null
and bl.unit_meas_lookup_code = p_unit_meas_lookup_code
and b.org_id = fsp.org_id
and fsp.set_of_books_id = gl.set_of_books_id
and ((b.org_id = p_ship_to_ou_id and nvl(b.global_agreement_flag, 'N') = 'N')
or (b.global_agreement_flag = 'Y' and exists
(select 'enabled'
from po_ga_org_assignments pgoa
where pgoa.po_header_id = b.po_header_id
and pgoa.enabled_flag = 'Y'
and ((pgoa.purchasing_org_id in
(select tfh.start_org_id
from mtl_procuring_txn_flow_hdrs_v tfh,
financials_system_params_all fsp1,
financials_system_params_all fsp2
where p_creation_date between nvl(tfh.start_date, p_creation_date) and nvl(tfh.end_date, p_creation_date)
and fsp1.org_id = tfh.start_org_id
and fsp1.purch_encumbrance_flag = 'N'
and fsp2.org_id = tfh.end_org_id
and fsp2.purch_encumbrance_flag = 'N'
and tfh.end_org_id = p_ship_to_ou_id
and ((tfh.qualifier_code is null) or (tfh.qualifier_code = 1 and tfh.qualifier_value_id = p_category_id))
and ((tfh.organization_id is null) or (tfh.organization_id = p_ship_to_organization_id))
)
)
or (nvl(pgoa.purchasing_org_id, p_ship_to_ou_id) = p_ship_to_ou_id))
)))
and nvl(bl.cancel_flag, 'N') = 'N'
and Trunc(p_creation_date) <= nvl(bl.expiration_date, p_creation_date)
and p_creation_date >= bl.creation_date
and bl.po_header_id = b.po_header_id
and b.type_lookup_code = 'BLANKET'
and b.approved_flag in ('Y','R')
and nvl(b.cancel_flag, 'N') = 'N'
and Trunc(p_creation_date)
between nvl(b.start_date, Trunc(p_creation_date)) and nvl(b.end_date, p_creation_date);
select min(shipto_price) keep (dense_rank first order by nvl2(shipto_price, nvl(quantity, 0), null) desc nulls last, trunc(creation_date) desc) over () shipto_price,
min(generic_price) keep (dense_rank first order by nvl2(generic_price, nvl(quantity, 0), null) desc nulls last, trunc(creation_date) desc) over () generic_price
from
(select
(case when pb.ship_to_location_id = p_ship_to_location_id and (pb.quantity is null or
(p_price_break_lookup_code = 'NON CUMULATIVE' and p_quantity >= pb.quantity)
or (p_price_break_lookup_code = 'CUMULATIVE' and p_quantity + p_line_qty >= pb.quantity ))
then pb.price_override else null end) shipto_price,
(case when pb.line_location_id is not null and pb.ship_to_location_id is null and (pb.quantity is null or
(p_price_break_lookup_code = 'NON CUMULATIVE' and p_quantity >= pb.quantity)
or (p_price_break_lookup_code = 'CUMULATIVE' and p_quantity + p_line_all_qty >= pb.quantity))
then pb.price_override else null end) generic_price,
creation_date,
pb.quantity
from po_line_locations_all pb
where pb.po_line_id = p_po_line_id
and pb.shipment_type = 'PRICE BREAK'
and p_quantity * nvl(pb.price_override,p_unit_price) >= nvl(p_bl_min,0)
and p_quantity * nvl(pb.price_override,p_unit_price) >= nvl(p_b_min,0)
and trunc(nvl(p_need_by_date, p_creation_date)) between
trunc(nvl(pb.start_date, pb.creation_date)) and
nvl(pb.end_date, nvl(p_need_by_date, p_creation_date))
and (p_amount_limit is null or p_quantity * nvl(pb.price_override,p_unit_price) + p_blanket_amt <= p_amount_limit) );
select
sum(case when sll.approved_flag='Y' and sll.ship_to_location_id=p_ship_to_location_id then nvl(sd.quantity_ordered,0)-nvl(sd.quantity_cancelled,0) else 0 end) line_qty
,sum(case when sll.approved_flag='Y' then nvl(sd.quantity_ordered,0)-nvl(sd.quantity_cancelled,0) else 0 end) line_all_qty
,sum(sum(nvl(sll.price_override,0)*(nvl(sd.quantity_ordered,0)-nvl(sd.quantity_cancelled,0)))) over () blanket_amt
into rline_q, rline_qa, rline_a
from po_line_locations_all sll
,po_distributions_all sd
,po_lines_all pol
where pol.po_line_id = sll.po_line_id (+)
and pol.from_header_id = l_pbline.po_header_id
and pol.from_line_id = l_pbline.po_line_id
and sll.shipment_type (+) = 'STANDARD'
and nvl(sd.distribution_type,'-99') <> 'AGREEMENT'
and sll.line_location_id = sd.line_location_id(+)
and sd.creation_date(+) < p_creation_date;
select
sum(case when rll.approved_flag='Y' and rll.ship_to_location_id=p_ship_to_location_id then
nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0) else 0 end) line_qty
,sum(case when rll.approved_flag='Y' then
nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0) else 0 end) line_all_qty
,sum(sum(nvl(rll.price_override,0)*(nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0)))) over () blanket_amt
into rline_q, rline_qa, rline_a
from po_line_locations_all rll
,po_distributions_all rd
where rll.po_header_id = l_pbline.po_header_id
and rll.po_line_id = l_pbline.po_line_id
and rll.shipment_type = 'BLANKET'
and nvl(rd.distribution_type,'-99') <> 'AGREEMENT'
and rll.line_location_id = rd.line_location_id(+)
and rd.creation_date(+) < p_creation_date;