[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select type_lookup_code, revision_num
into l_document_type, l_revision_num
from po_headers_archive_all
where po_header_id = X_header_id and latest_external_flag = 'Y';
SELECT fc.minimum_accountable_unit,
fc.precision,
global_agreement_flag
INTO x_min_unit,
x_precision,
x_global_agree_flag
FROM fnd_currencies fc,
po_headers_archive_all pha
WHERE pha.po_header_id = X_header_id
AND pha.revision_num = X_revision_num
AND fc.currency_code = pha.currency_code;
SELECT sum ( round ( (decode(pol.quantity, null,
(pod.amount_ordered -
pod.amount_cancelled),
(( pod.quantity_ordered
- pod.quantity_cancelled )
* poll.price_override)
)
)
/ x_min_unit )
* x_min_unit )
into x_po_total
FROM po_distributions_archive_all pod,
po_line_locations_archive_all poll,
po_lines_archive_all pol
WHERE pod.line_location_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.from_header_id = X_header_id
AND poll.latest_external_flag ='Y'
AND pol.latest_external_Flag = 'Y'
AND pod.latest_external_flag = 'Y';
SELECT sum (decode(pol.quantity, null,
(pod.amount_ordered -
pod.amount_cancelled),
(( pod.quantity_ordered
- pod.quantity_cancelled )
* poll.price_override)))
into x_po_total
FROM po_distributions_archive_all pod,
po_line_locations_archive_all poll,
po_lines_archive_all pol
WHERE pod.line_location_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.from_header_id = X_header_id
AND poll.latest_external_flag ='Y'
AND pol.latest_external_Flag = 'Y'
AND pod.latest_external_flag = 'Y';
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled,0)),
(pll.quantity - nvl(pll.quantity_cancelled,0))
* nvl(pll.price_override,0)
)
,x_precision))
INTO x_po_total
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_header_id = x_header_id
AND PLL.LATEST_EXTERNAL_FLAG= 'Y'
AND PLL.shipment_type in ('BLANKET','SCHEDULED');
select sum(round(
decode(pll.quantity,
null,
(pll.amount - nvl(pll.amount_cancelled, 0)),
(pll.quantity - nvl(pll.quantity_cancelled, 0))
* nvl(pll.price_override,0)
)
/ x_min_unit)
* x_min_unit)
INTO x_po_total
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
WHERE PLL.po_header_id = x_header_id
AND PLL.LATEST_EXTERNAL_FLAG= 'Y'
AND PLL.shipment_type in ('BLANKET','SCHEDULED');
select org_id
into x_org_id
from po_headers_all
where po_header_id = x_header_id;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_archive_all pha
WHERE pha.po_header_id = X_header_id
AND pha.revision_num = X_revision_num
AND fc.currency_code = pha.currency_code;
select sum(round(
(plla1.quantity - nvl (plla1.quantity_cancelled, 0)) *
nvl(plla1.price_override, 0), x_precision)
)
INTO X_po_total
FROM po_line_locations_archive_all plla1
where po_header_id = X_header_id
and shipment_type in ('PLANNED')
and revision_num = (
SELECT max(plla2.revision_num)
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
WHERE plla2.revision_num <= X_revision_num
AND plla2.line_location_id = plla1.line_location_id );
select sum(round((plla1.quantity -
nvl(plla1.quantity_cancelled,0)) *
nvl(plla1.price_override,0)/x_min_unit)*
x_min_unit)
INTO X_po_total
FROM po_line_locations_archive_all plla1 --po_line_locations_archive
WHERE po_header_id = X_header_id
AND shipment_type IN ('PLANNED')
AND revision_num = (
SELECT max( plla2.revision_num )
FROM po_line_locations_archive_all plla2 --po_line_locations_archive
WHERE plla2.revision_num <= X_revision_num
AND plla2.line_location_id = plla1.line_location_id ) ;
SELECT BLANKET_TOTAL_AMOUNT
INTO X_po_total
FROM po_headers_archive_all
WHERE revision_num = X_revision_num
AND po_header_id = X_header_id;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_archive_all pha,
po_releases_archive_all pra
WHERE pha.po_header_id = pra.po_header_id
AND pha.LATEST_EXTERNAL_FLAG = 'Y'
AND pra.po_release_id = X_release_id
AND pra.revision_num = X_revision_num
AND fc.currency_code = pha.currency_code;
select sum(round(
decode(plla1.quantity,
null,
(plla1.amount - nvl(plla1.amount_cancelled,0)),
((plla1.quantity - nvl(plla1.quantity_cancelled,0)) *
nvl(plla1.price_override,0))
) ,x_precision))
into X_po_total
FROM po_line_locations_archive_all plla1
WHERE po_release_id = X_release_id
AND shipment_type IN ('BLANKET','SCHEDULED')
AND revision_num = (
SELECT max( plla2.revision_num )
FROM po_line_locations_archive_all plla2
WHERE plla2.revision_num <= X_revision_num
AND plla2.line_location_id = plla1.line_location_id ) ;
select sum(round(decode(plla1.quantity,
null,
(plla1.amount - nvl(plla1.amount_cancelled,0)),
((plla1.quantity -nvl(plla1.quantity_cancelled,0)) *
nvl(plla1.price_override,0)))/x_min_unit)*
x_min_unit)
into X_po_total
FROM po_line_locations_archive_all plla1
WHERE po_release_id = X_release_id
AND shipment_type IN ('BLANKET','SCHEDULED')
AND revision_num = (
SELECT max( plla2.revision_num )
FROM po_line_locations_archive_all plla2
WHERE plla2.revision_num <= X_revision_num
AND plla2.line_location_id = plla1.line_location_id ) ;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_archive_all poh
WHERE poh.revision_num = x_revision_num
AND poh.po_header_id = x_po_header_id
AND fc.currency_code = poh.currency_code;
select round(
decode(plaa1.quantity,
null,
plaa1.amount ,
(plaa1.quantity
* nvl(plaa1.unit_price,0)))
,x_precision)
INTO X_po_total
FROM po_lines_archive_all plaa1
where plaa1.po_line_id = x_po_line_id
and revision_num = (
SELECT max(plaa2.revision_num)
FROM po_lines_archive_all plaa2
WHERE plaa2.revision_num <= x_revision_num
AND plaa2.po_line_id = plaa1.po_line_id );
select round(
decode(plaa1.quantity,
null,
plaa1.amount ,
(plaa1.quantity
* nvl(plaa1.unit_price,0)
)
)/x_min_unit)*x_min_unit
INTO X_po_total
FROM po_lines_archive_all plaa1
WHERE plaa1.po_line_id = x_po_line_id
AND revision_num = (
SELECT max( plaa2.revision_num )
FROM po_lines_archive_all plaa2
WHERE plaa2.revision_num <= x_revision_num
AND plaa2.po_line_id = plaa1.po_line_id ) ;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM PO_HEADERS_ALL POH,
FND_CURRENCIES FC,
PO_RELEASES_ARCHIVE_ALL POR
WHERE POR.po_release_id = x_po_release_id
AND por.revision_num = x_revision_num
AND POH.po_header_id = POR.po_header_id
AND FC.CURRENCY_CODE = POH.CURRENCY_CODE;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_archive_all poh
WHERE poh.revision_num = x_revision_num
AND poh.po_header_id = x_po_header_id
AND fc.currency_code = poh.currency_code;
select sum(round((
decode(plla1.quantity,
null,
(plla1.amount - nvl(plla1.amount_cancelled, 0)),
(plla1.quantity - nvl(plla1.quantity_cancelled,0))
* nvl(plla1.price_override,0))),x_precision))
INTO X_po_total
FROM po_line_locations_archive_all plla1
where plla1.po_line_id = x_po_line_id
and shipment_type in ('STANDARD','PLANNED')
and revision_num = (
SELECT max(plla2.revision_num)
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
WHERE plla2.revision_num <= x_revision_num
AND plla2.line_location_id = plla1.line_location_id );
select sum(round((
decode(plla1.quantity,
null,
(plla1.amount - nvl(plla1.amount_cancelled, 0)),
(plla1.quantity - nvl(plla1.quantity_cancelled,0))
* nvl(plla1.price_override,0)))/x_min_unit)*x_min_unit)
INTO X_po_total
FROM po_line_locations_archive_all plla1
WHERE plla1.po_line_id = x_po_line_id
AND shipment_type in ('STANDARD','PLANNED')
AND revision_num = (
SELECT max( plla2.revision_num )
FROM po_line_locations_archive_all plla2
WHERE plla2.revision_num <= x_revision_num
AND plla2.line_location_id = plla1.line_location_id ) ;
select sum(round((
decode(plla1.quantity,
null,
(plla1.amount - nvl(plla1.amount_cancelled, 0)),
(plla1.quantity - nvl(plla1.quantity_cancelled,0))
* nvl(plla1.price_override,0))),x_precision))
INTO X_po_total
FROM po_line_locations_archive_all plla1
where plla1.po_line_id = x_po_line_id
and plla1.po_release_id = x_po_release_id
and shipment_type in ('BLANKET','SCHEDULED')
and revision_num = (
SELECT max(plla2.revision_num)
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
WHERE plla2.revision_num <= x_revision_num
AND plla2.line_location_id = plla1.line_location_id );
select sum(round((
decode(plla1.quantity,
null,
(plla1.amount - nvl(plla1.amount_cancelled, 0)),
(plla1.quantity - nvl(plla1.quantity_cancelled,0))
* nvl(plla1.price_override,0)))/x_min_unit)*x_min_unit)
INTO X_po_total
FROM po_line_locations_archive_all plla1
WHERE plla1.po_line_id = x_po_line_id
and plla1.po_release_id = x_po_release_id
AND shipment_type in ('BLANKET','SCHEDULED')
AND revision_num = (
SELECT max( plla2.revision_num )
FROM po_line_locations_archive_all plla2
WHERE plla2.revision_num <= x_revision_num
AND plla2.line_location_id = plla1.line_location_id ) ;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_all pha,
po_line_locations_archive_all poll
WHERE poll.line_location_id = x_po_line_location_id
AND poll.po_header_id = pha.po_header_id
AND fc.currency_code = pha.currency_code
AND poll.latest_external_flag='Y';
select round(
decode(plla1.quantity,
null, (plla1.amount - nvl(plla1.amount_cancelled, 0)),
(plla1.quantity - nvl(plla1.quantity_cancelled,0))* nvl(plla1.price_override,0)), x_precision)
INTO X_po_total
FROM po_line_locations_archive_all plla1
WHERE plla1.line_location_id = x_po_line_location_id
AND revision_num = (
SELECT max(plla2.revision_num)
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
WHERE plla2.revision_num <= X_revision_num
AND plla2.line_location_id = plla1.line_location_id );
select round(
decode(plla1.quantity,
null, (plla1.amount - nvl(plla1.amount_cancelled,0)),
(plla1.quantity - nvl(plla1.quantity_cancelled,0))* nvl(plla1.price_override,0)) / x_min_unit) * x_min_unit
INTO X_po_total
FROM po_line_locations_archive_all plla1
WHERE plla1.line_location_id = x_po_line_location_id
AND revision_num = (
SELECT max( plla2.revision_num )
FROM po_line_locations_archive_all plla2
WHERE plla2.revision_num <= X_revision_num
AND plla2.line_location_id = plla1.line_location_id ) ;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_all pha,
po_line_locations_archive_all poll
WHERE poll.line_location_id = p_po_line_location_id
AND poll.po_header_id = pha.po_header_id
AND fc.currency_code = pha.currency_code
AND poll.latest_external_flag='Y';
select round(DECODE(PLLA.matching_basis,
'AMOUNT', NVL(PLLA.amount, 0) - NVL(PLLA.amount_cancelled, 0),
'QUANTITY', (NVL(PLLA.quantity,0)- NVL(PLLA.quantity_cancelled,0)) *
NVL(PLLA.price_override, 0)),
x_precision)
INTO p_amount_ordered
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
WHERE plla.line_location_id = p_po_line_location_id
AND revision_num = (
SELECT max(plla2.revision_num)
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla2
WHERE plla2.revision_num <= p_revision_num
AND plla2.line_location_id = plla.line_location_id );
SELECT round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_received, 0),
'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)),
x_precision),
round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_billed, 0),
'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)),
x_precision)
INTO p_amount_received,
p_amount_billed
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.line_location_id = p_po_line_location_id;
select round((DECODE(PLLA.matching_basis,
'AMOUNT', NVL(PLLA.amount, 0) - NVL(PLLA.amount_cancelled, 0),
'QUANTITY', (NVL(PLLA.quantity,0)- nvl(PLLA.quantity_cancelled,0))
* NVL(PLLA.price_override, 0))
/ x_min_unit) * x_min_unit)
INTO p_amount_ordered
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLLA
WHERE plla.line_location_id = p_po_line_location_id
AND revision_num = (
SELECT max( plla2.revision_num )
FROM po_line_locations_archive_all plla2
WHERE plla2.revision_num <= p_revision_num
AND plla2.line_location_id = plla.line_location_id ) ;
SELECT round((DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_received, 0),
'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0))
/ x_min_unit) * x_min_unit),
round((DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_billed, 0),
'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0))
/ x_min_unit) * x_min_unit)
INTO p_amount_received,
p_amount_billed
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE pll.line_location_id = p_po_line_location_id;
select --sum(quantity_invoiced),
nvl(sum(amount), 0)
into p_amount_billed
from ap_invoice_lines_all
where po_line_location_id = p_po_line_location_id;
select revision_num
into l_revision_num
from po_releases_archive_all
where po_release_id = X_release_id
and latest_external_flag = 'Y';
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_archive_all pha
WHERE fc.currency_code = pha.currency_code
AND pha.po_header_id = p_po_header_id
AND pha.latest_external_flag='Y';
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_received, 0),
'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)),
x_precision))
INTO x_total_received
FROM po_line_locations_all pll
WHERE pll.po_header_id = p_po_header_id
AND pll.po_release_id is null;
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_received, 0),
'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0)),
x_precision))
INTO x_total_received
FROM po_line_locations_all pll
WHERE pll.po_release_id = p_po_release_id;
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_received, 0),
'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0))
/ x_min_unit) * x_min_unit)
INTO x_total_received
FROM po_line_locations_all pll
WHERE pll.po_header_id = p_po_header_id
AND pll.po_release_id is null;
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_received, 0),
'QUANTITY', NVL(PLL.quantity_received, 0)*NVL(PLL.price_override, 0))
/ x_min_unit) * x_min_unit)
INTO x_total_received
FROM po_line_locations_all pll
WHERE pll.po_release_id = p_po_release_id;
SELECT fc.minimum_accountable_unit,
fc.precision
INTO x_min_unit,
x_precision
FROM fnd_currencies fc,
po_headers_archive_all pha
WHERE fc.currency_code = pha.currency_code
AND pha.po_header_id = p_po_header_id
AND pha.latest_external_flag='Y';
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_billed, 0),
'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)),
x_precision))
INTO x_total_invoiced
FROM po_line_locations_all pll
WHERE pll.po_header_id = p_po_header_id
AND pll.po_release_id is null;
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_billed, 0),
'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0)),
x_precision))
INTO x_total_invoiced
FROM po_line_locations_all pll
WHERE pll.po_release_id = p_po_release_id;
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_billed, 0),
'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0))
/ x_min_unit) * x_min_unit)
INTO x_total_invoiced
FROM po_line_locations_all pll
WHERE pll.po_header_id = p_po_header_id
AND pll.po_release_id is null;
select SUM(round(DECODE(PLL.matching_basis,
'AMOUNT', NVL(PLL.amount_billed, 0),
'QUANTITY', NVL(PLL.quantity_billed, 0)*NVL(PLL.price_override, 0))
/ x_min_unit) * x_min_unit)
INTO x_total_invoiced
FROM po_line_locations_all pll
WHERE pll.po_release_id = p_po_release_id;
select --sum(quantity_invoiced),
nvl(sum(amount), 0)
into x_total_invoiced
from ap_invoice_lines_all
where (po_header_id = p_po_header_id and po_release_id = p_po_release_id and p_po_release_id is not null)
or (po_header_id = p_po_header_id and po_release_id is null and p_po_release_id is null);
select NVL(AI.payment_status_flag, 'N')
from AP_INVOICES_ALL AI,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_DISTRIBUTIONS_ALL POD
where AI.invoice_id = AID.invoice_id
and AID.po_distribution_id = POD.po_distribution_id
and POD.po_header_id = p_po_header_id
and POD.po_release_id is null;
select NVL(AI.payment_status_flag, 'N')
from AP_INVOICES_ALL AI,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_DISTRIBUTIONS_ALL POD
where AI.invoice_id = AID.invoice_id
and AID.po_distribution_id = POD.po_distribution_id
and POD.po_header_id = p_po_header_id
and POD.po_release_id = p_po_release_id;
select NVL(AI.payment_status_flag, 'N')
from AP_INVOICES_ALL AI,
AP_INVOICE_DISTRIBUTIONS_ALL AID,
PO_DISTRIBUTIONS_ALL POD
where AI.invoice_id = AID.invoice_id
and AID.po_distribution_id = POD.po_distribution_id
and POD.line_location_id = p_line_location_id;
select NVL(AI.payment_status_flag, 'N')
from AP_INVOICES_ALL AI,
AP_INVOICE_LINES_ALL AIL
where AI.invoice_id = AIL.invoice_id
and AIL.po_line_location_id = p_line_location_id;