[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT min(plc.unit_price *
decode(sign(poa_savings_np.get_currency_conv_rate(phc.currency_code,
p_edw_global_currency_code,
p_rate_date,
p_edw_global_rate_type
)
),-1,null,
poa_savings_np.get_currency_conv_rate(phc.currency_code,
p_edw_global_currency_code,
p_rate_date,
p_edw_global_rate_type
)
)
)
INTO v_lowest_price
FROM po_headers_all phc
, po_lines_all plc
WHERE phc.type_lookup_code = 'BLANKET'
and phc.po_header_id = plc.po_header_id
and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
and p_creation_date between nvl(phc.start_date, p_creation_date)
and nvl(phc.end_date, p_creation_date)
and plc.item_id = p_item_id
and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
= nvl(p_item_revision, '-1')
and trunc(p_creation_date) <= nvl(plc.expiration_date, p_creation_date)
and (
(nvl(phc.global_agreement_flag,'N') = 'N'
and phc.org_id = p_ship_to_ou
)
or
(phc.global_agreement_flag = 'Y'
and exists
(select 'enabled'
from po_ga_org_assignments poga
where poga.po_header_id = phc.po_header_id
and poga.enabled_flag = 'Y'
and ((poga.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.qualifier_code is null) or
(tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id)
)
and tfh.end_org_id = p_ship_to_ou
and (
(tfh.organization_id = p_ship_to_organization_id) or
(tfh.organization_id is null)
)
)
)
or poga.purchasing_org_id = p_ship_to_ou
)
)
)
);
SELECT po_line_id,
poa_savings_np.get_currency_conv_rate(phc.currency_code,
p_edw_global_currency_code,
p_rate_date,
p_edw_global_rate_type
) conv_rate
FROM po_lines_all plc,
po_headers_all phc
WHERE phc.po_header_id = plc.po_header_id
and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
and p_creation_date between nvl(phc.start_date, p_creation_date)
and nvl(phc.end_date, p_creation_date)
and plc.item_id = p_item_id
and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
= nvl(p_item_revision, '-1')
and plc.price_break_lookup_code = 'NON CUMULATIVE'
and trunc(p_creation_date) <= nvl(plc.expiration_date, p_creation_date)
and (
(nvl(phc.global_agreement_flag,'N') = 'N'
and phc.org_id = p_ship_to_ou
)
or
(phc.global_agreement_flag = 'Y'
and exists
(select 'enabled'
from po_ga_org_assignments poga
where poga.po_header_id = phc.po_header_id
and poga.enabled_flag = 'Y'
and ((poga.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.qualifier_code is null) or
(tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id)
)
and tfh.end_org_id = p_ship_to_ou
and (
(tfh.organization_id = p_ship_to_organization_id) or
(tfh.organization_id is null)
)
)
)
or poga.purchasing_org_id = p_ship_to_ou
)
)
)
);
SELECT MIN(price_override) INTO v_price_override FROM (
SELECT price_override
FROM po_line_locations_all psc
WHERE psc.shipment_type = 'PRICE BREAK'
and psc.po_line_id = v_line_id
and psc.ship_to_location_id = p_ship_to_location_id
and psc.po_release_id is null
and Nvl(psc.quantity,0) <= p_quantity
and nvl(trunc(p_need_by_date), p_creation_date)
between nvl(psc.start_date, nvl(p_need_by_date, p_creation_date))
and nvl(psc.end_date, nvl(p_need_by_date, p_creation_date))
order by Nvl(psc.quantity,0) desc, Trunc(psc.creation_date) desc,
psc.price_override ASC) WHERE ROWNUM = 1;
SELECT MIN(price_override) INTO v_price_override_null FROM (
SELECT price_override
FROM po_line_locations_all psc
WHERE psc.shipment_type = 'PRICE BREAK'
and psc.po_line_id = v_line_id
and psc.ship_to_location_id IS null
and psc.po_release_id is null
and Nvl(psc.quantity,0) <= p_quantity
and nvl(trunc(p_need_by_date), p_creation_date)
between nvl(psc.start_date, nvl(p_need_by_date, p_creation_date))
and nvl(psc.end_date, nvl(p_need_by_date, p_creation_date))
order by Nvl(psc.quantity,0) desc, Trunc(psc.creation_date) desc,
psc.price_override ASC) WHERE ROWNUM = 1;
SELECT distinct psc.po_header_id, phc.currency_code
FROM po_headers_all phc
, po_lines_all plc
, po_line_locations_all psc
WHERE psc.shipment_type = 'PRICE BREAK'
and phc.po_header_id = plc.po_header_id
and plc.po_line_id = psc.po_line_id
and psc.po_header_id = phc.po_header_id
and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
and p_creation_date between nvl(phc.start_date, p_creation_date)
and nvl(phc.end_date, p_creation_date)
and plc.item_id = p_item_id
and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
= nvl(p_item_revision, '-1')
and psc.ship_to_location_id = p_ship_to_location_id
and psc.po_release_id is null
and plc.price_break_lookup_code = 'CUMULATIVE'
and trunc(p_creation_date) <= nvl(plc.expiration_date, p_creation_date)
and nvl(phc.global_agreement_flag, 'N') = 'N'
and phc.org_id = p_ship_to_ou;
SELECT distinct psc.po_header_id, phc.currency_code
FROM po_headers_all phc
, po_lines_all plc
, po_line_locations_all psc
WHERE psc.shipment_type = 'PRICE BREAK'
and phc.po_header_id = plc.po_header_id
and plc.po_line_id = psc.po_line_id
and psc.po_header_id = phc.po_header_id
and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
and p_creation_date between nvl(phc.start_date, p_creation_date)
and nvl(phc.end_date, p_creation_date)
and nvl(phc.currency_code, nvl(p_currency_code, '-1'))
= nvl(p_currency_code, '-1')
and plc.item_id = p_item_id
and nvl(plc.item_revision, nvl(p_item_revision, '-1'))
= nvl(p_item_revision, '-1')
and psc.ship_to_location_id is null
and psc.po_release_id is null
and plc.price_break_lookup_code = 'CUMULATIVE'
and nvl(phc.global_agreement_flag, 'N') = 'N'
and phc.org_id = p_ship_to_ou;
SELECT plc.po_line_id
FROM po_lines_all plc
WHERE plc.po_header_id = v_blanket_id
and plc.item_id = p_item_id
and plc.price_break_lookup_code = 'CUMULATIVE'
and plc.unit_meas_lookup_code = p_unit_meas_lookup_code
and nvl(plc.item_revision, nvl(p_item_revision, '-1')) = nvl(p_item_revision, '-1');
SELECT sum(nvl(pod.quantity_ordered,0)) INTO v_total_qty_released
FROM po_releases_all por
, po_distributions_all pod
WHERE por.po_header_id = v_blanket_id
and pod.po_release_id = por.po_release_id
and pod.po_line_id = v_po_line_id
and pod.creation_date < p_creation_date
and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT min(psc.price_override) INTO v_lowest_price
FROM po_line_locations_all psc
WHERE Nvl(psc.quantity,0) <= Nvl(v_total_qty_released,0) + p_quantity
and psc.po_release_id is null
AND psc.ship_to_location_id = p_ship_to_location_id
and psc.po_line_id = v_po_line_id;
SELECT min(psc.price_override) INTO v_lowest_price
FROM po_line_locations_all psc
WHERE Nvl(psc.quantity,0) <= Nvl(v_total_qty_released,0) + p_quantity
and psc.po_release_id is null
AND psc.ship_to_location_id IS NULL
and psc.po_line_id = v_po_line_id;
SELECT min(plc.unit_price) INTO v_lowest_price
FROM po_lines_all plc
WHERE plc.po_line_id = v_po_line_id;
select poa_savings_np.get_currency_conv_rate(v_currency_code,
p_edw_global_currency_code,
p_rate_date,
p_edw_global_rate_type
)
into v_conv_rate
from dual;