[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ LEADING (POLL) PUSH_SUBQ */
pod.po_distribution_id po_distribution_id,
poll.line_location_id line_location_id,
DECODE(poll.matching_basis,
'AMOUNT', 1,
0) service_flag,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
distribution_quantity,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered) quantity_ordered,
DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0))
shipment_quantity,
DECODE(poll.matching_basis,
'AMOUNT', 1,
NVL(poll.price_override, pol.unit_price))
po_price,
NVL(poll.match_option,'P') match_option,
pol.category_id category_id,
poh.currency_code currency_code,
NVL(NVL(pod.rate,poh.rate),1) currency_rate,
poh.rate_type curr_conv_type,
pod.rate_date currency_conv_date
FROM po_distributions pod, -- Using single org view PO_DISTRIBUTIONS to support MOAC
po_line_locations_all poll,
po_lines_all pol,
po_headers_all poh,
po_vendors pov,
mtl_categories_kfv mca,
mtl_default_sets_view mds
WHERE pol.po_header_id = poh.po_header_id
AND poh.pcard_id IS NULL
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
AND poh.vendor_id = pov.vendor_id
AND ((p_vendor_id IS NOT NULL AND pov.vendor_id = p_vendor_id)
OR
p_vendor_id IS NULL)
AND ((p_vendor_from IS NOT NULL AND pov.vendor_name >= p_vendor_from)
OR
p_vendor_from IS NULL)
AND ((p_vendor_to IS NOT NULL AND pov.vendor_name <= p_vendor_to)
OR
p_vendor_to IS NULL)
AND pol.category_id = mca.category_id
AND ((p_category_id IS NOT NULL AND mca.category_id = p_category_id)
OR
p_category_id IS NULL)
AND (p_category_from IS NULL
OR
(mca.concatenated_segments >= p_category_from AND p_category_from IS NOT NULL))
AND (p_category_to IS NULL
OR
(mca.concatenated_segments <= p_category_to AND p_category_to IS NOT NULL))
AND mds.structure_id = mca.structure_id
AND mds.functional_area_id = 2
AND poll.shipment_type <> 'PREPAYMENT'
AND (p_closed_pos = 'Y'
OR
(poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
AND pod.destination_type_code = 'EXPENSE'
AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
)
OR
(p_online_accruals = 'Y'
AND poll.accrue_on_receipt_flag = 'Y'
AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
DECODE(poll.matching_basis,
'AMOUNT', poll.amount_billed - poll.amount_received,
poll.quantity_billed - poll.quantity_received) <> 0)
))
--{BUG#6366287: Only accrue if Ordered Quantity - Cancelled Quantity is > 0
AND DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
AND DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
--}
AND EXISTS
(SELECT /*+ PUSH_SUBQ NO_UNNEST */
'Get a receipt/match for this shipment'
FROM rcv_transactions rvt
WHERE rvt.po_line_location_id = poll.line_location_id
AND rvt.transaction_type IN ('RECEIVE','MATCH')
AND rvt.transaction_date <= l_end_date
)
ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
SELECT /*+ LEADING (POLL) PUSH_SUBQ */
pod.po_distribution_id po_distribution_id,
poll.line_location_id line_location_id,
DECODE(poll.matching_basis,
'AMOUNT', 1,
0) service_flag,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
distribution_quantity,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered) quantity_ordered,
DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0))
shipment_quantity,
DECODE(poll.matching_basis,
'AMOUNT', 1,
NVL(poll.price_override, pol.unit_price))
po_price,
NVL(poll.match_option,'P') match_option,
pol.category_id category_id,
poh.currency_code currency_code,
NVL(NVL(pod.rate,poh.rate),1) currency_rate,
poh.rate_type curr_conv_type,
pod.rate_date currency_conv_date
FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
,po_line_locations_all poll
,po_lines_all pol
,po_headers_all poh
WHERE pol.po_header_id = poh.po_header_id
AND poh.pcard_id IS NULL
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
AND poll.shipment_type <> 'PREPAYMENT'
AND (p_closed_pos = 'Y'
OR
(poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
AND pod.destination_type_code = 'EXPENSE'
AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
)
OR
(p_online_accruals = 'Y'
AND poll.accrue_on_receipt_flag = 'Y'
AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
DECODE(poll.matching_basis,
'AMOUNT', poll.amount_billed - poll.amount_received,
poll.quantity_billed - poll.quantity_received) <> 0)
))
AND DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
AND DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
AND EXISTS
(SELECT /*+ PUSH_SUBQ NO_UNNEST */
'Get a receipt/match for this shipment'
FROM rcv_transactions rvt
WHERE rvt.po_line_location_id = poll.line_location_id
AND rvt.transaction_type IN ('RECEIVE','MATCH')
AND rvt.transaction_date <= l_end_date
)
ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
SELECT /*+ LEADING (POLL) PUSH_SUBQ */
pod.po_distribution_id po_distribution_id,
poll.line_location_id line_location_id,
DECODE(poll.matching_basis,
'AMOUNT', 1,
0) service_flag,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
distribution_quantity,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered) quantity_ordered,
DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0))
shipment_quantity,
DECODE(poll.matching_basis,
'AMOUNT', 1,
NVL(poll.price_override, pol.unit_price))
po_price,
NVL(poll.match_option,'P') match_option,
pol.category_id category_id,
poh.currency_code currency_code,
NVL(NVL(pod.rate,poh.rate),1) currency_rate,
poh.rate_type curr_conv_type,
pod.rate_date currency_conv_date
FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
,po_line_locations_all poll
,po_lines_all pol
,po_headers_all poh
,po_vendors pov
WHERE pol.po_header_id = poh.po_header_id
AND poh.pcard_id IS NULL
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
AND poh.vendor_id = pov.vendor_id
AND pov.vendor_id = p_vendor_id
AND poll.shipment_type <> 'PREPAYMENT'
AND (p_closed_pos = 'Y'
OR
(poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
AND pod.destination_type_code = 'EXPENSE'
AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
)
OR
(p_online_accruals = 'Y'
AND poll.accrue_on_receipt_flag = 'Y'
AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
DECODE(poll.matching_basis,
'AMOUNT', poll.amount_billed - poll.amount_received,
poll.quantity_billed - poll.quantity_received) <> 0)
))
AND DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
AND DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
AND EXISTS
(SELECT /*+ PUSH_SUBQ NO_UNNEST */
'Get a receipt/match for this shipment'
FROM rcv_transactions rvt
WHERE rvt.po_line_location_id = poll.line_location_id
AND rvt.transaction_type IN ('RECEIVE','MATCH')
AND rvt.transaction_date <= l_end_date
)
ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
SELECT /*+ LEADING (POLL) PUSH_SUBQ */
pod.po_distribution_id po_distribution_id,
poll.line_location_id line_location_id,
DECODE(poll.matching_basis,
'AMOUNT', 1,
0) service_flag,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
distribution_quantity,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered) quantity_ordered,
DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0))
shipment_quantity,
DECODE(poll.matching_basis,
'AMOUNT', 1,
NVL(poll.price_override, pol.unit_price))
po_price,
NVL(poll.match_option,'P') match_option,
pol.category_id category_id,
poh.currency_code currency_code,
NVL(NVL(pod.rate,poh.rate),1) currency_rate,
poh.rate_type curr_conv_type,
pod.rate_date currency_conv_date
FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
,po_line_locations_all poll
,po_lines_all pol
,po_headers_all poh
,po_vendors pov
WHERE pol.po_header_id = poh.po_header_id
AND poh.pcard_id IS NULL
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
AND poh.vendor_id = pov.vendor_id
AND ((p_vendor_from IS NOT NULL AND pov.vendor_name >= p_vendor_from)
OR
p_vendor_from IS NULL)
AND ((p_vendor_to IS NOT NULL AND pov.vendor_name <= p_vendor_to)
OR
p_vendor_to IS NULL)
AND poll.shipment_type <> 'PREPAYMENT'
AND (p_closed_pos = 'Y'
OR
(poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
AND pod.destination_type_code = 'EXPENSE'
AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
)
OR
(p_online_accruals = 'Y'
AND poll.accrue_on_receipt_flag = 'Y'
AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
DECODE(poll.matching_basis,
'AMOUNT', poll.amount_billed - poll.amount_received,
poll.quantity_billed - poll.quantity_received) <> 0)
))
AND DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
AND DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
AND EXISTS
(SELECT /*+ PUSH_SUBQ NO_UNNEST */
'Get a receipt/match for this shipment'
FROM rcv_transactions rvt
WHERE rvt.po_line_location_id = poll.line_location_id
AND rvt.transaction_type IN ('RECEIVE','MATCH')
AND rvt.transaction_date <= l_end_date
)
ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
SELECT /*+ LEADING (POLL) PUSH_SUBQ */
pod.po_distribution_id po_distribution_id,
poll.line_location_id line_location_id,
DECODE(poll.matching_basis,
'AMOUNT', 1,
0) service_flag,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
distribution_quantity,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered) quantity_ordered,
DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0))
shipment_quantity,
DECODE(poll.matching_basis,
'AMOUNT', 1,
NVL(poll.price_override, pol.unit_price))
po_price,
NVL(poll.match_option,'P') match_option,
pol.category_id category_id,
poh.currency_code currency_code,
NVL(NVL(pod.rate,poh.rate),1) currency_rate,
poh.rate_type curr_conv_type,
pod.rate_date currency_conv_date
FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
,po_line_locations_all poll
,po_lines_all pol
,po_headers_all poh
,mtl_categories_kfv mca
,mtl_default_sets_view mds
WHERE pol.po_header_id = poh.po_header_id
AND poh.pcard_id IS NULL
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
AND pol.category_id = mca.category_id
AND mca.category_id = p_category_id
AND mds.structure_id = mca.structure_id
AND mds.functional_area_id = 2
AND poll.shipment_type <> 'PREPAYMENT'
AND (p_closed_pos = 'Y'
OR
(poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
AND pod.destination_type_code = 'EXPENSE'
AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
)
OR
(p_online_accruals = 'Y'
AND poll.accrue_on_receipt_flag = 'Y'
AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
DECODE(poll.matching_basis,
'AMOUNT', poll.amount_billed - poll.amount_received,
poll.quantity_billed - poll.quantity_received) <> 0)
))
AND DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
AND DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
AND EXISTS
(SELECT /*+ PUSH_SUBQ NO_UNNEST */
'Get a receipt/match for this shipment'
FROM rcv_transactions rvt
WHERE rvt.po_line_location_id = poll.line_location_id
AND rvt.transaction_type IN ('RECEIVE','MATCH')
AND rvt.transaction_date <= l_end_date
)
ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
SELECT /*+ LEADING (POLL) PUSH_SUBQ */
pod.po_distribution_id po_distribution_id,
poll.line_location_id line_location_id,
DECODE(poll.matching_basis,
'AMOUNT', 1,
0) service_flag,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
distribution_quantity,
DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered) quantity_ordered,
DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0))
shipment_quantity,
DECODE(poll.matching_basis,
'AMOUNT', 1,
NVL(poll.price_override, pol.unit_price))
po_price,
NVL(poll.match_option,'P') match_option,
pol.category_id category_id,
poh.currency_code currency_code,
NVL(NVL(pod.rate,poh.rate),1) currency_rate,
poh.rate_type curr_conv_type,
pod.rate_date currency_conv_date
FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
,po_line_locations_all poll
,po_lines_all pol
,po_headers_all poh
,mtl_categories_kfv mca
,mtl_default_sets_view mds
WHERE pol.po_header_id = poh.po_header_id
AND poh.pcard_id IS NULL
AND poll.po_line_id = pol.po_line_id
AND pod.line_location_id = poll.line_location_id
AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
AND pol.category_id = mca.category_id
AND (p_category_from IS NULL
OR
(mca.concatenated_segments >= p_category_from AND p_category_from IS NOT NULL))
AND (p_category_to IS NULL
OR
(mca.concatenated_segments <= p_category_to AND p_category_to IS NOT NULL))
AND mds.structure_id = mca.structure_id
AND mds.functional_area_id = 2
AND poll.shipment_type <> 'PREPAYMENT'
AND (p_closed_pos = 'Y'
OR
(poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
AND pod.destination_type_code = 'EXPENSE'
AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
)
OR
(p_online_accruals = 'Y'
AND poll.accrue_on_receipt_flag = 'Y'
AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
DECODE(poll.matching_basis,
'AMOUNT', poll.amount_billed - poll.amount_received,
poll.quantity_billed - poll.quantity_received) <> 0)
))
AND DECODE (poll.matching_basis,
'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
AND DECODE(poll.matching_basis,
'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
AND EXISTS
(SELECT /*+ PUSH_SUBQ NO_UNNEST */
'Get a receipt/match for this shipment'
FROM rcv_transactions rvt
WHERE rvt.po_line_location_id = poll.line_location_id
AND rvt.transaction_type IN ('RECEIVE','MATCH')
AND rvt.transaction_date <= l_end_date
)
ORDER by poll.line_location_id ; /* Order by Clause added for bug 8675502*/
INSERT INTO cst_per_end_accruals_temp (
shipment_id,
distribution_id,
category_id,
match_option,
quantity_received,
quantity_billed,
accrual_quantity,
encum_quantity,
unit_price,
accrual_amount,
encum_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date
)
VALUES (
l_acr_shipment_id_tbl(l_ctr),
l_acr_dist_id_tbl(l_ctr),
l_acr_category_id_tbl(l_ctr),
l_acr_match_option_tbl(l_ctr),
l_acr_qty_received_tbl(l_ctr),
l_acr_qty_billed_tbl(l_ctr),
l_acr_accrual_qty_tbl(l_ctr),
l_acr_encum_qty_tbl(l_ctr),
l_acr_unit_price_tbl(l_ctr),
l_acr_accrual_amount_tbl(l_ctr),
l_acr_encum_amount_tbl(l_ctr),
l_acr_cur_code_tbl(l_ctr),
l_acr_cur_conv_type_tbl(l_ctr),
l_acr_cur_conv_rate_tbl(l_ctr),
l_acr_cur_conv_date_tbl(l_ctr)
);
DELETE FROM cst_per_end_accruals_temp
WHERE shipment_id IN (SELECT shipment_id
FROM cst_per_end_accruals_temp
GROUP BY shipment_id
HAVING SUM(accrual_amount) < NVL(p_min_accrual_amount, 0)
);
SELECT transaction_id,
shipment_header_id,
currency_code,
NVL(currency_conversion_rate, 1) currency_conversion_rate,
currency_conversion_date,
currency_conversion_type
FROM rcv_transactions
WHERE po_line_location_id = x_accrual_rec.shipment_id
AND ((transaction_type = 'RECEIVE' AND parent_transaction_id = -1)
OR
(transaction_type = 'MATCH'))
AND transaction_date <= p_end_date
AND (p_transaction_id IS NULL
OR
(transaction_id = p_transaction_id AND p_transaction_id IS NOT NULL))
ORDER BY shipment_header_id, transaction_date;
SELECT po_distribution_id,
DECODE (p_service_flag,
1, amount_ordered - NVL(amount_cancelled, 0),
quantity_ordered - NVL(quantity_cancelled, 0)) distribution_quantity
FROM po_distributions_all
WHERE line_location_id = x_accrual_rec.shipment_id;
g_dist_nqd_tbl.DELETE;
g_dist_nqd_tbl.DELETE;
SELECT rt.transaction_id,
rt.po_line_location_id,
rt.po_distribution_id,
rt.transaction_type,
DECODE(p_service_flag,
1, rt.amount,
rt.source_doc_quantity) quantity,
rt.parent_transaction_id
FROM rcv_transactions rt
WHERE rt.transaction_date <= p_end_date
START WITH rt.po_line_location_id = p_line_location_id
AND ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
OR
(rt.transaction_type = 'MATCH'))
CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id;
SELECT rt.transaction_id,
rt.po_line_location_id,
rt.po_distribution_id,
rt.transaction_type,
DECODE(p_service_flag,
1, rt.amount,
rt.source_doc_quantity) quantity,
rt.parent_transaction_id
FROM rcv_transactions rt
WHERE rt.transaction_date <= p_end_date
START WITH rt.shipment_header_id = p_rcv_shipment_id
AND rt.po_line_location_id = p_line_location_id
AND ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
OR
(rt.transaction_type = 'MATCH'))
AND (p_rcv_txn_id IS NULL
OR
(rt.transaction_id = p_rcv_txn_id AND p_rcv_txn_id IS NOT NULL))
CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id;
SELECT rt.transaction_type
INTO l_parent_type
FROM rcv_transactions rt
WHERE rt.transaction_id = l_shipment_rec.parent_transaction_id;
SELECT rt.transaction_type
INTO l_parent_type
FROM rcv_transactions rt
WHERE rt.transaction_id = l_txn_rec.parent_transaction_id;
SELECT NVL(DECODE(p_service_flag,
1, SUM(aida.amount),
SUM(decode(aida.corrected_invoice_dist_id,
null,aida.quantity_invoiced,
nvl(aida.corrected_quantity,aida.quantity_invoiced))
)),
0)
INTO l_quantity_invoiced
FROM ap_invoice_distributions_all aida,
ap_invoices_all aia
WHERE aida.po_distribution_id = p_dist_id
AND aida.line_type_lookup_code NOT IN ('IPV', 'ERV', 'PREPAY','REC_TAX')
AND aida.accounting_date <= p_end_date
AND aida.posted_flag = 'Y'
AND aia.invoice_id = aida.invoice_id
AND aia.invoice_type_lookup_code <> 'PREPAYMENT';
SELECT NVL(DECODE(p_service_flag,
1, SUM(aida.amount),
SUM(decode(aida.corrected_invoice_dist_id,
null,aida.quantity_invoiced,
nvl(aida.corrected_quantity,aida.quantity_invoiced))
* DECODE(pol.item_id,null,1,inv_convert.inv_um_convert(pol.item_id,
10,
NULL,
NULL,
NULL,
aida.matched_uom_lookup_code,
NVL(pol.unit_meas_lookup_code, poll.unit_meas_lookup_code))))),
0)
INTO l_quantity_invoiced
FROM ap_invoice_distributions_all aida,
ap_invoices_all aia,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod
WHERE aida.po_distribution_id = pod.po_distribution_id
AND (p_rcv_txn_id IS NULL OR aida.rcv_transaction_id = p_rcv_txn_id)
AND aida.line_type_lookup_code NOT IN ('IPV', 'ERV', 'PREPAY','REC_TAX')
AND aida.accounting_date <= p_end_date
AND aia.invoice_id = aida.invoice_id
AND aia.invoice_type_lookup_code <> 'PREPAYMENT'
AND aida.posted_flag = 'Y'
AND pod.po_distribution_id = p_dist_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = pod.line_location_id;