[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(
DECODE(POL.matching_basis
, 'AMOUNT', pol.amount
, --QUANTITY
nvl2(l_mau
, round(pol.quantity*pol.unit_price/l_mau) * l_mau
, round((pol.quantity*pol.unit_price),l_precision)) )) -- Bug# 5378134
INTO l_return_val
FROM po_lines_all pol
WHERE pol.po_header_id = p_doc_level_id
;
SELECT SUM(
DECODE(POL.matching_basis
, 'AMOUNT', pol.amount
, --QUANTITY
nvl2(l_mau
, round(pol.quantity*pol.unit_price/l_mau) * l_mau
, round((pol.quantity*pol.unit_price),l_precision)) )) -- Bug# 5378134
INTO l_return_val
FROM po_lines_archive_all pol
WHERE pol.po_header_id = p_doc_level_id
AND ( (p_doc_revision_num IS NULL and pol.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND POL.revision_num =
(SELECT max(POL2.revision_num)
FROM po_lines_archive_all pol2
WHERE pol2.po_line_id = pol.po_line_id
AND pol2.revision_num <= p_doc_revision_num)
)
)
;
SELECT DECODE(POL.matching_basis
, 'AMOUNT', pol.amount
, --QUANTITY
nvl2(l_mau
, round(pol.quantity*pol.unit_price/l_mau) * l_mau
, round((pol.quantity*pol.unit_price),l_precision)) ) -- Bug# 5378134
INTO l_return_val
FROM po_lines_all pol
WHERE pol.po_line_id = p_doc_level_id
;
SELECT DECODE(POL.matching_basis
, 'AMOUNT', pol.amount
, --QUANTITY
nvl2(l_mau
, round(pol.quantity*pol.unit_price/l_mau) * l_mau
, round((pol.quantity*pol.unit_price),l_precision)) ) -- Bug# 5378134
INTO l_return_val
FROM po_lines_archive_all pol
WHERE pol.po_line_id = p_doc_level_id
AND ( (p_doc_revision_num IS NULL and pol.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND POL.revision_num =
(SELECT max(POL2.revision_num)
FROM po_lines_archive_all pol2
WHERE pol2.po_line_id = pol.po_line_id
AND pol2.revision_num <= p_doc_revision_num)
)
)
;
SELECT DECODE(POLL.matching_basis
, 'AMOUNT', poll.amount - nvl(poll.amount_cancelled,0)
, --QUANTITY
nvl2(l_mau
, round((poll.quantity-nvl(poll.quantity_cancelled,0))
*poll.price_override/l_mau) * l_mau
, round(((poll.quantity-nvl(poll.quantity_cancelled,0))
*poll.price_override),l_precision) )) -- Bug# 5378134
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.line_location_id = p_doc_level_id
;
SELECT DECODE(POLL.matching_basis
, 'AMOUNT', poll.amount - nvl(poll.amount_cancelled,0)
, --QUANTITY
nvl2(l_mau
, round((poll.quantity-nvl(poll.quantity_cancelled,0))
*poll.price_override/l_mau) * l_mau
, round(((poll.quantity-nvl(poll.quantity_cancelled,0))
*poll.price_override),l_precision) )) -- Bug# 5378134
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.line_location_id = p_doc_level_id
AND ( (p_doc_revision_num IS NULL and poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND POLL.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT DECODE(POLL.matching_basis
, 'AMOUNT', pod.amount_ordered - nvl(pod.amount_cancelled,0)
, --QUANTITY
nvl2(l_mau
, round((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
*poll.price_override/l_mau) * l_mau
, round(((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
*poll.price_override),l_precision) )) -- Bug# 5378134
INTO l_return_val
FROM po_line_locations_all poll
, po_distributions_all pod
WHERE pod.po_distribution_id = p_doc_level_id
AND poll.line_location_id = pod.line_location_id
;
SELECT DECODE(POLL.matching_basis
, 'AMOUNT', pod.amount_ordered - nvl(pod.amount_cancelled,0)
, --QUANTITY
nvl2(l_mau
, round((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
*poll.price_override/l_mau) * l_mau
, round(((pod.quantity_ordered-nvl(pod.quantity_cancelled,0))
*poll.price_override),l_precision) )) -- Bug# 5378134
INTO l_return_val
FROM po_line_locations_archive_all poll
, po_distributions_archive_all pod
WHERE pod.po_distribution_id = p_doc_level_id
AND poll.line_location_id = pod.line_location_id
AND ( (p_doc_revision_num IS NULL
AND pod.latest_external_flag = 'Y'
AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND POD.revision_num =
(SELECT max(POD2.revision_num)
FROM po_distributions_archive_all pod2
WHERE pod2.po_distribution_id = pod.po_distribution_id
AND pod2.revision_num <= p_doc_revision_num)
AND POLL.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', poll.amount_received
, --QUANTITY
nvl2(l_mau
, round(poll.quantity_received*poll.price_override/l_mau) * l_mau
, round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_line_id
AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', poll.amount_received
, --QUANTITY
nvl2(l_mau
, round(poll.quantity_received*poll.price_override/l_mau) * l_mau
, round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_line_id = p_line_id
AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
AND ( (p_doc_revision_num IS NULL and poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', poll.amount_received
, --QUANTITY
nvl2(l_mau
, round(poll.quantity_received*poll.price_override/l_mau) * l_mau
, round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_header_id = p_header_id
AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', poll.amount_received
, --QUANTITY
nvl2(l_mau
, round(poll.quantity_received*poll.price_override/l_mau) * l_mau
, round((poll.quantity_received*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_header_id = p_header_id
AND nvl(poll.payment_type, 'NULL') IN ('RATE', 'LUMPSUM', 'MILESTONE') --Bug5391045
AND ( (p_doc_revision_num IS NULL and poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', pod.amount_delivered
, --QUANTITY
nvl2(l_mau
, round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
, round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_all poll
, po_distributions_all pod
WHERE poll.po_line_id = p_line_id
AND pod.line_location_id = poll.line_location_id
AND pod.distribution_type = 'STANDARD'
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', pod.amount_delivered
, --QUANTITY
nvl2(l_mau
, round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
, round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_archive_all poll
, po_distributions_archive_all pod
WHERE poll.po_line_id = p_line_id
AND pod.line_location_id = poll.line_location_id
AND pod.distribution_type = 'STANDARD'
AND ( (p_doc_revision_num IS NULL
AND poll.latest_external_flag = 'Y'
AND pod.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
AND pod.revision_num =
(SELECT max(POD2.revision_num)
FROM po_distributions_archive_all pod2
WHERE pod2.po_distribution_id = pod.po_distribution_id
AND pod2.revision_num <= p_doc_revision_num)
)
)
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', pod.amount_delivered
, --QUANTITY
nvl2(l_mau
, round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
, round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_all poll
, po_distributions_all pod
WHERE poll.po_header_id = p_header_id
AND pod.line_location_id = poll.line_location_id
AND pod.distribution_type = 'STANDARD'
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', pod.amount_delivered
, --QUANTITY
nvl2(l_mau
, round(pod.quantity_delivered*poll.price_override/l_mau) * l_mau
, round((pod.quantity_delivered*poll.price_override),l_precision)) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_archive_all poll
, po_distributions_archive_all pod
WHERE poll.po_header_id = p_header_id
AND pod.line_location_id = poll.line_location_id
AND pod.distribution_type = 'STANDARD'
AND ( (p_doc_revision_num IS NULL
AND poll.latest_external_flag = 'Y'
AND pod.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
AND pod.revision_num =
(SELECT max(POD2.revision_num)
FROM po_distributions_archive_all pod2
WHERE pod2.po_distribution_id = pod.po_distribution_id
AND pod2.revision_num <= p_doc_revision_num)
)
)
;
SELECT pol.org_id
INTO l_org_id
FROM po_lines_all pol
WHERE pol.po_line_id = p_line_id;
SELECT SUM(nvl(amount_billed,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type = 'STANDARD'
;
SELECT SUM(nvl(amount_billed,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type='STANDARD'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT poh.org_id
INTO l_org_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_header_id
;
SELECT SUM(nvl(amount_billed,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type = 'STANDARD'
;
SELECT SUM(nvl(amount_billed,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type='STANDARD'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT pol.org_id
INTO l_org_id
FROM po_lines_all pol
WHERE pol.po_line_id = p_line_id;
SELECT SUM(nvl(amount_financed,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type = 'PREPAYMENT'
;
SELECT SUM(nvl(amount_financed,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type='PREPAYMENT'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT poh.org_id
INTO l_org_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_header_id
;
SELECT SUM(nvl(amount_financed,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type = 'PREPAYMENT'
;
SELECT SUM(nvl(amount_financed,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type='PREPAYMENT'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT pol.org_id
INTO l_org_id
FROM po_lines_all pol
WHERE pol.po_line_id = p_line_id;
SELECT SUM(nvl(amount_recouped,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type = 'PREPAYMENT'
;
SELECT SUM(nvl(amount_recouped,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type='PREPAYMENT'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT poh.org_id
INTO l_org_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_header_id
;
SELECT SUM(nvl(amount_recouped,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type = 'PREPAYMENT'
;
SELECT SUM(nvl(amount_recouped,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type='PREPAYMENT'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT pol.org_id
INTO l_org_id
FROM po_lines_all pol
WHERE pol.po_line_id = p_line_id;
SELECT SUM(nvl(retainage_withheld_amount,0)
- nvl(retainage_released_amount,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type = 'STANDARD'
;
SELECT SUM(nvl(retainage_withheld_amount,0)
- nvl(retainage_released_amount,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type='STANDARD'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT poh.org_id
INTO l_org_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_header_id
;
SELECT SUM(nvl(retainage_withheld_amount,0)
- nvl(retainage_released_amount,0))
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type = 'STANDARD'
;
SELECT SUM(nvl(retainage_withheld_amount,0)
- nvl(retainage_released_amount,0))
INTO l_return_val
FROM po_line_locations_archive_all poll
WHERE poll.po_header_id = p_header_id
AND poll.shipment_type='STANDARD'
AND ( (p_doc_revision_num IS NULL AND poll.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND poll.revision_num =
(SELECT max(POLL2.revision_num)
FROM po_line_locations_archive_all poll2
WHERE poll2.line_location_id = poll.line_location_id
AND poll2.revision_num <= p_doc_revision_num)
)
)
;
SELECT pol.po_header_id
INTO l_header_id
FROM po_lines_all pol
WHERE pol.po_line_id = p_line_id
;
SELECT SUM(poll.quantity)
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type = 'STANDARD'
;
SELECT nvl(pol.quantity, 0)
INTO l_return_val
FROM po_lines_all pol
WHERE pol.po_line_id = p_line_id
;
SELECT SUM(
DECODE(poll.matching_basis
, 'AMOUNT', poll.amount-nvl(poll.amount_cancelled,0)
, --QUANTITY
nvl2(l_mau
, round((poll.quantity-nvl(poll.quantity_cancelled,0))
*poll.price_override/l_mau) * l_mau
, round(((poll.quantity-nvl(poll.quantity_cancelled,0))
*poll.price_override),l_precision) ) )) --Bug5391045
INTO l_return_val
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_line_id
AND poll.shipment_type = 'STANDARD'
;
SELECT SUM(pod.quantity_ordered)
INTO l_return_val
FROM po_distributions_all pod
WHERE pod.line_location_id = p_line_loc_id
;
SELECT SUM(pod.amount_ordered)
INTO l_return_val
FROM po_distributions_all pod
WHERE pod.line_location_id = p_line_loc_id
;
SELECT pol.po_header_id
INTO l_header_id
FROM po_lines_all pol
WHERE pol.po_line_id = p_doc_level_id;
SELECT poll.po_header_id
INTO l_header_id
FROM po_line_locations_all poll
WHERE poll.line_location_id = p_doc_level_id;
SELECT pod.po_header_id
INTO l_header_id
FROM po_distributions_all pod
WHERE pod.po_distribution_id = p_doc_level_id;
SELECT poh.currency_code, poh.org_id
INTO l_po_currency, l_org_id
FROM po_headers_all poh
WHERE poh.po_header_id = l_header_id
;
SELECT PO_DOCUMENT_TOTALS_GT_S.nextval INTO l_temp_table_key from dual;
INSERT INTO PO_DOCUMENT_TOTALS_GT
(
key,
line_location_id,
amount_based_flag,
shipment_type,
payment_type,
price,
quantity_total,
quantity_billed,
quantity_delivered,
quantity_financed,
quantity_recouped,
quantity_received,
quantity_shipped,
amount_total,
amount_billed,
amount_delivered,
amount_financed,
amount_recouped,
amount_received,
amount_shipped,
retainage_withheld_amount,
retainage_released_amount
)
SELECT
l_temp_table_key,
POLL.line_location_id,
DECODE(POLL.value_basis,
'FIXED PRICE', 'Y',
'RATE', 'Y',
'N') amount_based_flag,
POLL.shipment_type,
POLL.payment_type,
POLL.price_override,
SUM( (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) ),
SUM( nvl(POD.quantity_billed,0) ),
SUM( nvl(POD.quantity_delivered,0) ),
SUM( nvl(POD.quantity_financed,0) ),
SUM( nvl(POD.quantity_recouped,0) ),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_received),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_shipped),
SUM( (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) ),
SUM( nvl(POD.amount_billed,0) ),
SUM( nvl(POD.amount_delivered,0) ),
SUM( nvl(POD.amount_financed,0) ),
SUM( nvl(POD.amount_recouped,0) ),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_received),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_shipped),
SUM( nvl(POD.retainage_withheld_amount,0) ),
SUM( nvl(POD.retainage_released_amount,0) )
FROM
PO_LINE_LOCATIONS_ALL POLL,
PO_DISTRIBUTIONS_ALL POD
WHERE POD.po_distribution_id = l_distribution_id_tbl(i)
AND POD.line_location_id = POLL.line_location_id
GROUP BY POLL.line_location_id, POLL.value_basis, POLL.shipment_type,
POLL.payment_type, POLL.price_override, POLL.quantity_received, POLL.quantity_shipped,
POLL.amount_received, POLL.amount_shipped
;
PO_LOG.stmt(d_mod,d_position,'Inserted data - rowcount:',x_count);
INSERT INTO PO_DOCUMENT_TOTALS_GT
(
key,
line_location_id,
amount_based_flag,
shipment_type,
payment_type,
price,
quantity_total,
quantity_billed,
quantity_delivered,
quantity_financed,
quantity_recouped,
quantity_received,
quantity_shipped,
amount_total,
amount_billed,
amount_delivered,
amount_financed,
amount_recouped,
amount_received,
amount_shipped,
retainage_withheld_amount,
retainage_released_amount
)
SELECT
l_temp_table_key,
POLL.line_location_id,
DECODE(POLL.value_basis,
'FIXED PRICE', 'Y',
'RATE', 'Y',
'N') amount_based_flag,
POLL.shipment_type,
POLL.payment_type,
POLL.price_override,
SUM( (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) ),
SUM( nvl(POD.quantity_billed,0) ),
SUM( nvl(POD.quantity_delivered,0) ),
SUM( nvl(POD.quantity_financed,0) ),
SUM( nvl(POD.quantity_recouped,0) ),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_received),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.quantity_shipped),
SUM( (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) ),
SUM( nvl(POD.amount_billed,0) ),
SUM( nvl(POD.amount_delivered,0) ),
SUM( nvl(POD.amount_financed,0) ),
SUM( nvl(POD.amount_recouped,0) ),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_received),
DECODE(p_doc_level, g_doc_level_DISTRIBUTION, 0, POLL.amount_shipped),
SUM( nvl(POD.retainage_withheld_amount,0) ),
SUM( nvl(POD.retainage_released_amount,0) )
FROM
PO_LINE_LOCATIONS_ARCHIVE_ALL POLL,
PO_DISTRIBUTIONS_ARCHIVE_ALL POD
WHERE POD.po_distribution_id = l_distribution_id_tbl(i)
AND POD.revision_num = l_distribution_rev_num_tbl(i)
AND POD.line_location_id = POLL.line_location_id
AND ( (p_doc_revision_num IS NULL AND POLL.latest_external_flag = 'Y')
OR (p_doc_revision_num IS NOT NULL
AND POLL.revision_num =
(SELECT max(POLL2.revision_num)
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POLL2
WHERE POLL2.line_location_id = POLL.line_location_id
AND POLL2.revision_num <= p_doc_revision_num)
)
)
GROUP BY POLL.line_location_id, POLL.value_basis, POLL.shipment_type,
POLL.payment_type, POLL.price_override, POLL.quantity_received,
POLL.quantity_shipped, POLL.amount_received, POLL.amount_shipped
;
PO_LOG.stmt(d_mod,d_position,'Inserted data - rowcount:',x_count);
UPDATE PO_DOCUMENT_TOTALS_GT GTT
SET
GTT.amount_total = (GTT.quantity_total * GTT.price)
, GTT.amount_billed = (GTT.quantity_billed * GTT.price)
, GTT.amount_delivered = (GTT.quantity_delivered * GTT.price)
, GTT.amount_financed = (GTT.quantity_financed * GTT.price)
, GTT.amount_recouped = (GTT.quantity_recouped * GTT.price)
, GTT.amount_received = (GTT.quantity_received * GTT.price)
, GTT.amount_shipped = (GTT.quantity_shipped * GTT.price)
WHERE amount_based_flag = 'N'
;
UPDATE PO_DOCUMENT_TOTALS_GT GTT
SET
GTT.amount_total = nvl2(l_mau
, round(amount_total/l_mau) * l_mau
, round(amount_total, l_precision))
, GTT.amount_billed = nvl2(l_mau
, round(amount_billed/l_mau) * l_mau
, round(amount_billed, l_precision))
, GTT.amount_delivered = nvl2(l_mau
, round(amount_delivered/l_mau) * l_mau
, round(amount_delivered, l_precision))
, GTT.amount_financed = nvl2(l_mau
, round(amount_financed/l_mau) * l_mau
, round(amount_financed, l_precision))
, GTT.amount_recouped = nvl2(l_mau
, round(amount_recouped/l_mau) * l_mau
, round(amount_recouped, l_precision))
, GTT.amount_received = nvl2(l_mau
, round(amount_received/l_mau) * l_mau
, round(amount_received, l_precision))
, GTT.amount_shipped = nvl2(l_mau
, round(amount_shipped/l_mau) * l_mau
, round(amount_shipped, l_precision))
WHERE GTT.amount_based_flag = 'N'
;
PO_LOG.stmt(d_mod,d_position,'Updated amts - rowcount:',SQL%ROWCOUNT);
SELECT
SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN amount_total ELSE 0 END) amount_total_actuals,
SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN amount_total ELSE 0 END) amount_total_financing,
SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN amount_delivered ELSE 0 END) amount_delivered_actuals,
SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN amount_delivered ELSE 0 END) amount_delivered_financing,
SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN amount_received ELSE 0 END) amount_received_actuals,
SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN amount_received ELSE 0 END) amount_received_financing,
SUM(CASE WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN amount_shipped ELSE 0 END) amount_shipped_actuals,
SUM(CASE WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN amount_shipped ELSE 0 END) amount_shipped_financing,
SUM(amount_billed),
SUM(amount_financed),
SUM(amount_recouped),
SUM(retainage_withheld_amount),
SUM(retainage_released_amount)
INTO
l_amount_total_actuals,
l_amount_total_financing,
l_amount_delivered_actuals,
l_amount_delivered_financing,
l_amount_received_actuals,
l_amount_received_financing,
l_amount_shipped_actuals,
l_amount_shipped_financing,
l_amount_billed,
l_amount_financed,
l_amount_recouped,
l_retainage_withheld_amount,
l_retainage_released_amount
FROM PO_DOCUMENT_TOTALS_GT GTT
WHERE key = p_temp_table_key
;
SELECT
SUM(quantity_total),
SUM(quantity_delivered),
SUM(quantity_received),
SUM(quantity_shipped),
SUM(quantity_billed),
SUM(quantity_financed),
SUM(quantity_recouped)
INTO
l_quantity_total_actuals,
l_quantity_delivered_actuals,
l_quantity_received_actuals,
l_quantity_shipped_actuals,
l_quantity_billed,
l_quantity_financed,
l_quantity_recouped
FROM PO_DOCUMENT_TOTALS_GT GTT
WHERE GTT.key = p_temp_table_key
AND GTT.amount_based_flag = 'N'
AND nvl(GTT.payment_type, 'NULL') <> C_payment_type_RATE
;
SELECT
MAX(GTTSUM.qty_total_actuals),
MAX(GTTSUM.qty_total_financing),
MAX(GTTSUM.qty_delivered_actuals),
MAX(GTTSUM.qty_delivered_financing),
MAX(GTTSUM.qty_received_actuals),
MAX(GTTSUM.qty_received_financing),
MAX(GTTSUM.qty_shipped_actuals),
MAX(GTTSUM.qty_shipped_financing),
MAX(GTTSUM.qty_billed),
MAX(GTTSUM.qty_financed),
MAX(GTTSUM.qty_recouped)
INTO
l_quantity_total_actuals,
l_quantity_total_financing,
l_quantity_delivered_actuals,
l_quantity_delivered_financing,
l_quantity_received_actuals,
l_quantity_received_financing,
l_quantity_shipped_actuals,
l_quantity_shipped_financing,
l_quantity_billed,
l_quantity_financed,
l_quantity_recouped
FROM
( SELECT
GTT.line_location_id,
SUM(CASE
WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN GTT.quantity_total ELSE 0 END) qty_total_actuals,
SUM(CASE
WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN GTT.quantity_total ELSE 0 END) qty_total_financing,
SUM(CASE
WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN GTT.quantity_delivered ELSE 0 END) qty_delivered_actuals,
SUM(CASE
WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN GTT.quantity_delivered ELSE 0 END) qty_delivered_financing,
SUM(CASE
WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN GTT.quantity_received ELSE 0 END) qty_received_actuals,
SUM(CASE
WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN GTT.quantity_received ELSE 0 END) qty_received_financing,
SUM(CASE
WHEN GTT.shipment_type <> C_ship_type_PREPAYMENT
THEN GTT.quantity_shipped ELSE 0 END) qty_shipped_actuals,
SUM(CASE
WHEN GTT.shipment_type = C_ship_type_PREPAYMENT
THEN GTT.quantity_shipped ELSE 0 END) qty_shipped_financing,
SUM(GTT.quantity_billed) qty_billed,
SUM(GTT.quantity_financed) qty_financed,
SUM(GTT.quantity_recouped) qty_recouped
FROM PO_DOCUMENT_TOTALS_GT GTT
WHERE GTT.key = p_temp_table_key
AND GTT.amount_based_flag = 'N'
AND nvl(GTT.payment_type, 'NULL') = C_payment_type_MILESTONE
GROUP BY GTT.line_location_id
) GTTSUM
;
DELETE FROM PO_DOCUMENT_TOTALS_GT
WHERE key = p_temp_table_key
;
PO_LOG.stmt(d_mod,d_position,'Deleted data - rowcount:',SQL%ROWCOUNT);