[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT psp.po_shipment_id,
NVL(psp.purchase_price, 0) *
DECODE(gl_currency_api.rate_exists(psp.currency_code,
p_currency_code, psp.rate_date,
psp.rate_type),
'Y',
gl_currency_api.get_rate(psp.currency_code, p_currency_code,
psp.rate_date, psp.rate_type),
1),
NVL(psp.quantity_purchased, 0), NVL(psp.quantity_ordered, 0),
NVL(psp.quantity_received, 0), NVL(psp.quantity_rejected, 0),
NVL((psp.quantity_received_late + psp.quantity_received_early +
psp.quantity_past_due), 0),
psp.date_dimension
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND psp.date_dimension BETWEEN p_start_date AND p_end_date
AND (psp.supplier_id = p_cons_supplier_id
OR ALL_SUPPLIER = p_cons_supplier_id)
ORDER BY psp.currency_code;
SELECT psp.po_shipment_id,
NVL(psp.purchase_price, 0) *
DECODE(gl_currency_api.rate_exists(psp.currency_code,
p_currency_code, psp.rate_date,
psp.rate_type),
'Y',
gl_currency_api.get_rate(psp.currency_code, p_currency_code,
psp.rate_date, psp.rate_type),
1),
NVL(psp.quantity_purchased, 0), NVL(psp.quantity_ordered, 0),
NVL(psp.quantity_received, 0), NVL(psp.quantity_rejected, 0),
NVL((psp.quantity_received_late + psp.quantity_received_early +
psp.quantity_past_due), 0),
psp.date_dimension
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND psp.date_dimension BETWEEN p_start_date AND p_end_date
AND (psp.supplier_id = p_cons_supplier_id
OR ALL_SUPPLIER = p_cons_supplier_id)
AND (psp.org_id in (SELECT id FROM bis_operating_units_v
WHERE responsibility_id in
(SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND sysdate BETWEEN start_date
AND NVL(end_date, sysdate+1)))
OR psp.org_id IS NULL)
ORDER BY psp.currency_code;
SELECT psc2.price_override *
DECODE(gl_currency_api.rate_exists(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
'Y',
gl_currency_api.get_rate(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
1) blanket_price
FROM po_headers_all phc2,
po_headers_all phc1,
po_lines_all plc2,
po_lines_all plc1,
po_line_locations_all psc2,
po_line_locations_all psc1
WHERE psc1.line_location_id = v_cons_shipment_id
AND plc1.po_line_id = psc1.po_line_id
AND plc1.po_header_id = psc1.po_header_id
AND phc1.po_header_id = psc1.po_header_id
AND plc2.item_id = plc1.item_id
AND NVL(plc2.item_revision, NULL_VALUE) =
NVL(plc1.item_revision, NULL_VALUE)
AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
AND phc2.po_header_id = plc2.po_header_id
AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
AND NVL(phc2.end_date, v_cons_date)
AND NVL(phc2.currency_code, MAGIC_STRING) =
NVL(phc1.currency_code, MAGIC_STRING)
AND psc2.po_line_id = plc2.po_line_id
AND psc2.po_header_id = plc2.po_header_id
AND psc2.shipment_type = 'PRICE BREAK'
AND psc2.po_release_id IS NULL
AND psc2.quantity <= psc1.quantity
AND (psc2.ship_to_location_id = psc1.ship_to_location_id
OR psc2.ship_to_location_id IS NULL)
AND phc2.vendor_id = p_pref_supplier_id
ORDER BY psc2.quantity desc, blanket_price asc;
SELECT psc2.price_override *
DECODE(gl_currency_api.rate_exists(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
'Y',
gl_currency_api.get_rate(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
1) blanket_price
FROM po_headers_all phc2,
po_headers_all phc1,
po_lines_all plc2,
po_lines_all plc1,
po_line_locations_all psc2,
po_line_locations_all psc1
WHERE psc1.line_location_id = v_cons_shipment_id
AND plc1.po_line_id = psc1.po_line_id
AND plc1.po_header_id = psc1.po_header_id
AND phc1.po_header_id = psc1.po_header_id
AND plc2.item_id = plc1.item_id
AND NVL(plc2.item_revision, NULL_VALUE) =
NVL(plc1.item_revision, NULL_VALUE)
AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
AND phc2.po_header_id = plc2.po_header_id
AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
AND NVL(phc2.end_date, v_cons_date)
AND NVL(phc2.currency_code, MAGIC_STRING) =
NVL(phc1.currency_code, MAGIC_STRING)
AND psc2.po_line_id = plc2.po_line_id
AND psc2.po_header_id = plc2.po_header_id
AND psc2.shipment_type = 'PRICE BREAK'
AND psc2.po_release_id IS NULL
AND psc2.quantity <= psc1.quantity
AND (psc2.ship_to_location_id = psc1.ship_to_location_id
OR psc2.ship_to_location_id IS NULL)
AND phc2.vendor_id = p_pref_supplier_id
AND (phc2.org_id in (SELECT id FROM bis_operating_units_v
WHERE responsibility_id in
(SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND sysdate BETWEEN start_date
AND NVL(end_date, sysdate+1)))
OR phc2.org_id IS NULL)
ORDER BY psc2.quantity desc, blanket_price asc;
SELECT plc2.unit_price *
DECODE(gl_currency_api.rate_exists(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
'Y',
gl_currency_api.get_rate(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
1) blanket_price
FROM po_headers_all phc2,
po_headers_all phc1,
po_lines_all plc2,
po_lines_all plc1,
po_line_locations_all psc1
WHERE psc1.line_location_id = v_cons_shipment_id
AND plc1.po_line_id = psc1.po_line_id
AND plc1.po_header_id = psc1.po_header_id
AND phc1.po_header_id = psc1.po_header_id
AND plc2.item_id = plc1.item_id
AND NVL(plc2.item_revision, NULL_VALUE) =
NVL(plc1.item_revision, NULL_VALUE)
AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
AND phc2.po_header_id = plc2.po_header_id
AND phc2.type_lookup_code = 'BLANKET'
AND phc2.vendor_id = p_pref_supplier_id
AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
AND NVL(phc2.end_date, v_cons_date)
AND NVL(phc2.currency_code, MAGIC_STRING) =
NVL(phc1.currency_code, MAGIC_STRING)
ORDER BY blanket_price;
SELECT plc2.unit_price *
DECODE(gl_currency_api.rate_exists(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
'Y',
gl_currency_api.get_rate(phc2.currency_code,
p_currency_code,
NVL(phc2.rate_date, phc2.creation_date),
NVL(phc2.rate_type, 'Corporate')),
1) blanket_price
FROM po_headers_all phc2,
po_headers_all phc1,
po_lines_all plc2,
po_lines_all plc1,
po_line_locations_all psc1
WHERE psc1.line_location_id = v_cons_shipment_id
AND plc1.po_line_id = psc1.po_line_id
AND plc1.po_header_id = psc1.po_header_id
AND phc1.po_header_id = psc1.po_header_id
AND plc2.item_id = plc1.item_id
AND NVL(plc2.item_revision, NULL_VALUE) =
NVL(plc1.item_revision, NULL_VALUE)
AND NVL(plc2.unit_meas_lookup_code, MAGIC_STRING) =
NVL(plc1.unit_meas_lookup_code, MAGIC_STRING)
AND phc2.po_header_id = plc2.po_header_id
AND phc2.type_lookup_code = 'BLANKET'
AND v_cons_date BETWEEN NVL(phc2.start_date, v_cons_date)
AND NVL(phc2.end_date, v_cons_date)
AND NVL(phc2.currency_code, MAGIC_STRING) =
NVL(phc1.currency_code, MAGIC_STRING)
AND phc2.vendor_id = p_pref_supplier_id
AND (phc2.org_id in (SELECT id FROM bis_operating_units_v
WHERE responsibility_id in
(SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND sysdate BETWEEN start_date
AND NVL(end_date, sysdate+1)))
OR phc2.org_id IS NULL)
ORDER BY blanket_price;
SELECT SUM(psp.purchase_price *
DECODE(gl_currency_api.rate_exists(psp.currency_code,
p_currency_code,
psp.rate_date,
psp.rate_type),
'Y',
gl_currency_api.get_rate(psp.currency_code,
p_currency_code,
psp.rate_date, psp.rate_type),
1) *
psp.quantity_purchased) /
SUM(psp.quantity_purchased)
INTO v_pref_purch_price
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND psp.supplier_id = p_pref_supplier_id
AND NVL(psp.quantity_purchased, 0) <> 0
AND psp.date_dimension BETWEEN p_start_date AND p_end_date;
SELECT SUM(psp.purchase_price *
DECODE(gl_currency_api.rate_exists(psp.currency_code,
p_currency_code,
psp.rate_date,
psp.rate_type),
'Y',
gl_currency_api.get_rate(psp.currency_code,
p_currency_code,
psp.rate_date, psp.rate_type),
1) *
psp.quantity_purchased) /
SUM(psp.quantity_purchased)
INTO v_pref_purch_price
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND NVL(psp.quantity_purchased, 0) <> 0
AND psp.date_dimension BETWEEN p_start_date AND p_end_date
AND psp.supplier_id = p_pref_supplier_id
AND (psp.org_id in (SELECT id FROM bis_operating_units_v
WHERE responsibility_id in
(SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND sysdate BETWEEN start_date
AND NVL(end_date, sysdate+1)))
OR psp.org_id IS NULL);
SELECT SUM(psp.quantity_rejected)/SUM(psp.quantity_received)
INTO v_pref_pct_defect
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND NVL(psp.quantity_received, 0) <> 0
AND psp.date_dimension BETWEEN p_start_date AND p_end_date
AND psp.supplier_id = p_pref_supplier_id;
SELECT SUM(psp.quantity_rejected)/SUM(psp.quantity_received)
INTO v_pref_pct_defect
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND NVL(psp.quantity_received, 0) <> 0
AND psp.date_dimension BETWEEN p_start_date AND p_end_date
AND psp.supplier_id = p_pref_supplier_id
AND (psp.org_id in (SELECT id FROM bis_operating_units_v
WHERE responsibility_id in
(SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND sysdate BETWEEN start_date
AND NVL(end_date, sysdate+1)))
OR psp.org_id IS NULL);
SELECT SUM(psp.quantity_received_late + psp.quantity_received_early +
psp.quantity_past_due)/SUM(psp.quantity_purchased)
INTO v_pref_pct_del_excp
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND NVL(psp.quantity_purchased, 0) <> 0
AND psp.date_dimension BETWEEN p_start_date AND p_end_date
AND psp.supplier_id = p_pref_supplier_id;
SELECT SUM(psp.quantity_received_late + psp.quantity_received_early +
psp.quantity_past_due)/SUM(psp.quantity_purchased)
INTO v_pref_pct_del_excp
FROM poa_bis_supplier_performance psp
WHERE psp.item_id = p_item_id
AND NVL(psp.quantity_purchased, 0) <> 0
AND psp.date_dimension BETWEEN p_start_date AND p_end_date
AND psp.supplier_id = p_pref_supplier_id
AND (psp.org_id in (SELECT id FROM bis_operating_units_v
WHERE responsibility_id in
(SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND sysdate BETWEEN start_date
AND NVL(end_date, sysdate+1)))
OR psp.org_id IS NULL);