[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT psp.sup_perf_pk_key,
NVL(psp.price_g, 0),
NVL(psp.qty_ordered_b - psp.qty_cancelled_b, 0),
NVL(psp.qty_received_b, 0),
NVL(psp.qty_rejected_b, 0),
NVL((psp.qty_late_receipt_b + psp.qty_early_receipt_b +
psp.qty_past_due_b), 0),
psp.date_dim_fk_key
FROM edw_time_m cal,
edw_items_m item,
edw_trd_partner_m tp,
poa_edw_sup_perf_f psp
WHERE psp.item_fk_key = item.irev_item_revision_pk_key
AND item.item_item_name = p_item_name
AND psp.date_dim_fk_key = cal.cday_cal_day_pk_key
AND psp.supplier_site_fk_key = tp.tplo_tpartner_loc_pk_key
AND (tp.tprt_name = p_cons_supplier_name
OR 'ALL' = UPPER(p_cons_supplier_name))
AND cal.cday_cal_day_pk <> 'NA_EDW'
AND cal.day_julian_day IS NOT NULL
AND cal.day_julian_day <> 0
AND to_date(cal.day_julian_day,'J') BETWEEN p_start_date AND p_end_date;
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 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)
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)
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 SUM(psp.price_g * (psp.qty_ordered_b - psp.qty_cancelled_b)) /
SUM(psp.qty_ordered_b - psp.qty_cancelled_b),
SUM(psp.qty_early_receipt_b + psp.qty_late_receipt_b +
psp.qty_past_due_b) / SUM(psp.qty_ordered_b - psp.qty_cancelled_b)
INTO v_pref_purch_price,
v_pref_pct_del_excp
FROM edw_time_m cal,
edw_items_m item,
edw_trd_partner_m tp,
poa_edw_sup_perf_f psp
WHERE psp.item_fk_key = item.irev_item_revision_pk_key
AND item.item_item_name = p_item_name
AND psp.date_dim_fk_key = cal.cday_cal_day_pk_key
AND psp.supplier_site_fk_key = tp.tplo_tpartner_loc_pk_key
AND tp.tprt_name = p_pref_supplier_name
AND NVL(psp.qty_ordered_b - psp.qty_cancelled_b, 0) <> 0
AND cal.cday_cal_day_pk <> 'NA_EDW'
AND cal.day_julian_day IS NOT NULL
AND cal.day_julian_day <> 0
AND to_date(cal.day_julian_day,'J') BETWEEN p_start_date AND p_end_date;
SELECT SUM(psp.qty_rejected_b)/SUM(psp.qty_received_b)
INTO v_pref_pct_defect
FROM edw_time_m cal,
edw_items_m item,
edw_trd_partner_m tp,
poa_edw_sup_perf_f psp
WHERE psp.item_fk_key = item.irev_item_revision_pk_key
AND item.item_item_name = p_item_name
AND psp.date_dim_fk_key = cal.cday_cal_day_pk_key
AND psp.supplier_site_fk_key = tp.tplo_tpartner_loc_pk_key
AND tp.tprt_name = p_pref_supplier_name
AND NVL(psp.qty_received_b, 0) <> 0
AND cal.cday_cal_day_pk <> 'NA_EDW'
AND cal.day_julian_day IS NOT NULL
AND cal.day_julian_day <> 0
AND to_date(cal.day_julian_day,'J') BETWEEN p_start_date AND p_end_date;