[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select MIN(aid.accounting_date)
into invoice_date
from po_distributions_all pod,
ap_invoice_distributions_all aid
where p_line_location_id = pod.line_location_id
AND aid.po_distribution_id = pod.po_distribution_id
AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
select MIN(aid.accounting_date - pod.creation_date)
into v_days
from po_distributions_all pod,
ap_invoice_distributions_all aid
where p_line_location_id = pod.line_location_id
AND aid.po_distribution_id = pod.po_distribution_id
AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
select SUM(aid.base_invoice_price_variance / nvl(pod.rate,nvl(poh.rate,1)))
into v_ipv
from po_distributions_all pod,
ap_invoice_distributions_all aid,
po_headers_all poh
where p_line_location_id = pod.line_location_id
AND aid.po_distribution_id = pod.po_distribution_id
AND poh.po_header_id = pod.po_header_id
AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT MIN(transaction_date)
INTO v_receipt_date
FROM rcv_transactions
WHERE po_line_location_id = p_line_location_id
AND transaction_type = 'RECEIVE';
SELECT MAX(last_update_date)
INTO v_max_rcv_trx_date
FROM rcv_transactions
WHERE po_line_location_id = p_line_location_id;
SELECT MAX(last_update_date)
INTO v_max_shp_line_date
FROM rcv_shipment_lines
WHERE po_line_location_id = p_line_location_id;
SELECT last_update_date
INTO v_line_loc_date
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id;
SELECT SUM(rsl.quantity_shipped *
poa_edw_util.get_uom_rate(rsl.item_id,
NULL, -- precision
NULL, -- from qty
'', -- from UOM code
'', -- to UOM code
rsl.unit_of_measure, -- from UOM name
p_shipment_uom)) -- to UOM name
INTO v_qty_shipped
FROM rcv_shipment_lines rsl
WHERE rsl.po_line_location_id = p_line_location_id;
SELECT SUM(pod.quantity_delivered)
INTO v_qty_delivered
FROM po_distributions_all pod
WHERE pod.line_location_id = p_line_location_id
AND nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
SELECT
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, rct.source_doc_quantity, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, rct.source_doc_quantity, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,rct.source_doc_quantity,0))),
sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,rct.source_doc_quantity))),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
- trunc(p_expected_date)),0,rct.source_doc_quantity,0)))),
sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', rct.source_doc_quantity, 0)),
sum(rct.source_doc_quantity),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 1, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, 1, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,1,0))),
sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,1))),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
- trunc(p_expected_date)),0,1,0)))),
sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', 1, 0)),
sum(1)
INTO
l_qty_rec_cnt_early, l_qty_rec_cnt_late, l_qty_rec_cnt_early_window, l_qty_rec_cnt_late_window, l_qty_rec_cnt_ondate, l_qty_rec_cnt_substitute,
l_qty_rec_cnt_all, l_qty_rec_num_early, l_qty_rec_num_late, l_qty_rec_num_early_window, l_qty_rec_num_late_window, l_qty_rec_num_ondate, l_qty_rec_num_substitute, l_qty_rec_num_all
FROM rcv_transactions rct
WHERE rct.po_line_location_id = p_line_location_id
AND rct.transaction_type = 'RECEIVE';
SELECT
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, rcor.source_doc_quantity, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, rcor.source_doc_quantity, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - p_expected_date),-1,rcor.source_doc_quantity,0))),
sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,rcor.source_doc_quantity))),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
- trunc(p_expected_date)),0,rct.source_doc_quantity,0)))),
sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', rcor.source_doc_quantity, 0)),
sum(rcor.source_doc_quantity)
INTO
l_qty_rec_cnt_cor_early, l_qty_rec_cnt_cor_late, l_qty_rec_cnt_cor_early_window, l_qty_rec_cnt_cor_late_window, l_qty_rec_cnt_cor_ondate, l_qty_rec_cnt_cor_substitute, l_qty_rec_cnt_cor_all
FROM rcv_transactions rcor,
rcv_transactions rct
WHERE rcor.po_line_location_id = p_line_location_id
AND rcor.transaction_type = 'CORRECT'
AND rct.transaction_id = rcor.parent_transaction_id
AND rct.transaction_type = 'RECEIVE';
SELECT pll.quantity - pll.quantity_cancelled - pll.quantity_received
INTO v_qty_pastdue
FROM po_line_locations_all pll
WHERE pll.line_location_id = p_line_location_id
AND pll.quantity - pll.quantity_cancelled - pll.quantity_received >= 0;
SELECT
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, rct.source_doc_quantity, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, rct.source_doc_quantity, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,rct.source_doc_quantity,0))),
sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,rct.source_doc_quantity))),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
- trunc(p_expected_date)),0,rct.source_doc_quantity,0)))),
sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', rct.source_doc_quantity, 0)),
sum(rct.source_doc_quantity),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 1, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, 1, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - trunc(p_expected_date)),-1,1,0))),
sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,1))),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
- trunc(p_expected_date)),0,1,0)))),
sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', 1, 0)),
sum(1)
INTO
l_qty_rec_cnt_early, l_qty_rec_cnt_late, l_qty_rec_cnt_early_window, l_qty_rec_cnt_late_window, l_qty_rec_cnt_ondate, l_qty_rec_cnt_substitute,
l_qty_rec_cnt_all, l_qty_rec_num_early, l_qty_rec_num_late, l_qty_rec_num_early_window, l_qty_rec_num_late_window, l_qty_rec_num_ondate, l_qty_rec_num_substitute, l_qty_rec_num_all
FROM rcv_transactions rct
WHERE rct.po_line_location_id = p_line_location_id
AND rct.transaction_type = 'RECEIVE';
SELECT
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 1, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))), 1, 1, 0)),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))), -1, 0, decode(sign(rct.transaction_date - p_expected_date), -1, 1, 0))),
sum(decode(sign(rct.transaction_date - (trunc(p_expected_date)+1)),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,1))),
sum(decode(sign(rct.transaction_date - (p_expected_date - nvl(p_days_early_allowed,0))),-1,0,decode(sign(rct.transaction_date - (p_expected_date + nvl(p_days_late_allowed,0))),1,0,decode(sign(trunc(rct.transaction_date)
- trunc(p_expected_date)),0,1,0)))),
sum(decode(rct.substitute_unordered_code, 'SUBSTITUTE', 1, 0)),
sum(1)
INTO
l_qty_rec_num_cor_early, l_qty_rec_num_cor_late, l_qty_rec_num_cor_early_window, l_qty_rec_num_cor_late_window, l_qty_rec_num_cor_ondate, l_qty_rec_num_cor_substitute, l_qty_rec_num_cor_all
FROM rcv_transactions rcor,
rcv_transactions rct
WHERE rcor.po_line_location_id = p_line_location_id
AND rcor.transaction_type = 'CORRECT'
AND rct.transaction_id = rcor.parent_transaction_id
AND rct.transaction_type = 'RECEIVE'
AND rcor.source_doc_quantity + rct.source_doc_quantity
< ZERO_TOLERANCE;
SELECT pol.item_id, pol.unit_meas_lookup_code, poh.currency_code,
pll.approved_date, pll.quantity, pll.need_by_date, pll.creation_date INTO
v_item_id, v_unit_meas_lookup_code, v_currency_code,
v_approved_date, v_quantity, v_need_by_date, v_creation_date
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll
WHERE pll.line_location_id = p_line_location_id
AND pll.po_line_id = pol.po_line_id
AND pol.po_header_id = poh.po_header_id;
SELECT min(lowest_price) into v_best_price from
(SELECT
DECODE( poh.currency_code,
v_currency_code,
DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price),
DECODE( poh.rate_type,
'User',
DECODE(gsob.currency_code,
v_currency_code,
poh.rate * DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price),
gl_currency_api.convert_amount_sql(
gsob.currency_code,
v_currency_code,
NVL(poh.rate_date, pll.creation_date),
NULL,
poh.rate * DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price))),
gl_currency_api.convert_amount_sql(
poh.currency_code,
v_currency_code,
NVL(poh.rate_date, pll.creation_date),
poh.rate_type,
DECODE(pll.shipment_type, 'PRICE BREAK', pll.price_override, 'PLANNED', pll.price_override, pol.unit_price))))
lowest_price
FROM gl_sets_of_books gsob,
financials_system_params_all fsp,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll
WHERE pol.item_id = v_item_id
AND pol.unit_meas_lookup_code = v_unit_meas_lookup_code
AND ((Nvl(pll.shipment_type,'PRICE BREAK') = 'PRICE BREAK'
AND v_approved_date BETWEEN NVL(poh.start_date, Nvl(pll.approved_date,poh.approved_date)) AND NVL(poh.end_date, v_approved_date)
AND pll.po_release_id IS NULL
AND Nvl(pll.quantity,0) <= v_quantity
AND Trunc(Nvl(v_need_by_date, v_creation_date))
BETWEEN Trunc(Nvl(pll.start_date, Nvl(v_need_by_date, v_creation_date))) AND Nvl(pll.end_date, Nvl(v_need_by_date, v_creation_date))
AND Trunc(v_creation_date) <= Nvl(pol.expiration_date,v_creation_date))
OR(pll.shipment_type = 'BLANKET'
AND v_approved_date BETWEEN NVL(poh.start_date, pll.approved_date) AND NVL(poh.end_date, v_approved_date)
AND pol.unit_price > 0)
OR(pll.shipment_type = 'STANDARD'
AND v_approved_date BETWEEN (pll.approved_date - 180) AND (pll.approved_date + 180)
AND pol.unit_price > 0)
OR(pll.shipment_type = 'PLANNED'
AND v_approved_date BETWEEN (pll.approved_date - 180) AND (pll.approved_date + 180)
AND pol.unit_price > 0))
AND pll.approved_flag(+) = 'Y'
AND pll.po_line_id(+) = pol.po_line_id
AND pol.po_header_id = poh.po_header_id
AND NVL(pll.org_id, fsp.org_id) = fsp.org_id
AND gsob.set_of_books_id = fsp.set_of_books_id)
WHERE lowest_price > 0;
SELECT pll.price_override
INTO v_best_price
FROM po_line_locations_all pll
WHERE line_location_id = p_line_location_id;
SELECT SUM(source_doc_quantity)
INTO v_txn_qty
FROM rcv_transactions
WHERE po_line_location_id = p_line_location_id
AND transaction_type = p_txn_type;
SELECT SUM(rcor.source_doc_quantity)
INTO v_correction_qty
FROM rcv_transactions rcor,
rcv_transactions rct
WHERE rcor.po_line_location_id = p_line_location_id
AND rcor.transaction_type = 'CORRECT'
AND rct.transaction_id = rcor.parent_transaction_id
AND rct.transaction_type = p_txn_type;