[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_document_ap_values(
p_api_version IN NUMBER
, p_line_loc_changes_rec IN OUT NOCOPY PO_AP_LINE_LOC_REC_TYPE
, p_dist_changes_rec IN OUT NOCOPY PO_AP_DIST_REC_TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_document_ap_values';
SAVEPOINT update_document_ap_values_SP;
UPDATE po_line_locations_all pll
SET quantity_billed =
DECODE(p_line_loc_changes_rec.quantity_billed
, NULL, quantity_billed
, nvl(quantity_billed, 0) + p_line_loc_changes_rec.quantity_billed),
amount_billed =
DECODE(p_line_loc_changes_rec.amount_billed
, NULL, amount_billed
, nvl(amount_billed, 0) + p_line_loc_changes_rec.amount_billed),
quantity_financed =
DECODE(p_line_loc_changes_rec.quantity_financed
, NULL, quantity_financed
, nvl(quantity_financed, 0) + p_line_loc_changes_rec.quantity_financed),
amount_financed =
DECODE(p_line_loc_changes_rec.amount_financed
, NULL, amount_financed
, nvl(amount_financed, 0) + p_line_loc_changes_rec.amount_financed),
quantity_recouped =
DECODE(p_line_loc_changes_rec.quantity_recouped
, NULL, quantity_recouped
, nvl(quantity_recouped, 0) + p_line_loc_changes_rec.quantity_recouped),
amount_recouped =
DECODE(p_line_loc_changes_rec.amount_recouped
, NULL, amount_recouped
, nvl(amount_recouped, 0) + p_line_loc_changes_rec.amount_recouped),
retainage_withheld_amount =
DECODE(p_line_loc_changes_rec.retainage_withheld_amt
, NULL, retainage_withheld_amount
, nvl(retainage_withheld_amount, 0) + p_line_loc_changes_rec.retainage_withheld_amt),
retainage_released_amount =
DECODE(p_line_loc_changes_rec.retainage_released_amt
, NULL, retainage_released_amount
, nvl(retainage_released_amount, 0) + p_line_loc_changes_rec.retainage_released_amt),
last_update_login = nvl(p_line_loc_changes_rec.last_update_login, last_update_login),
request_id = nvl(p_line_loc_changes_rec.request_id, request_id) ,
last_update_date = SYSDATE, -- Bug 10021100
last_updated_by = FND_GLOBAL.USER_ID
WHERE pll.line_location_id = p_line_loc_changes_rec.po_line_location_id
OR (pll.shipment_type = 'PLANNED'
AND pll.line_location_id =
(SELECT pll2.source_shipment_id
FROM po_line_locations pll2
WHERE pll2.shipment_type = 'SCHEDULED'
AND pll2.line_location_id = p_line_loc_changes_rec.po_line_location_id)
)
;
PO_LOG.stmt(d_mod,d_position,'Line Locations updated: ' || SQL%ROWCOUNT || '; fnd_global.user_id: ' || fnd_global.user_id);
UPDATE po_distributions_all pod
SET quantity_billed =
DECODE(p_dist_changes_rec.quantity_billed_tbl(i)
, NULL, quantity_billed
, nvl(quantity_billed, 0) + p_dist_changes_rec.quantity_billed_tbl(i)),
amount_billed =
DECODE(p_dist_changes_rec.amount_billed_tbl(i)
, NULL, amount_billed
, nvl(amount_billed, 0) + p_dist_changes_rec.amount_billed_tbl(i)),
quantity_financed =
DECODE(p_dist_changes_rec.quantity_financed_tbl(i)
, NULL, quantity_financed
, nvl(quantity_financed, 0) + p_dist_changes_rec.quantity_financed_tbl(i)),
amount_financed =
DECODE(p_dist_changes_rec.amount_financed_tbl(i)
, NULL, amount_financed
, nvl(amount_financed, 0) + p_dist_changes_rec.amount_financed_tbl(i)),
quantity_recouped =
DECODE(p_dist_changes_rec.quantity_recouped_tbl(i)
, NULL, quantity_recouped
, nvl(quantity_recouped, 0) + p_dist_changes_rec.quantity_recouped_tbl(i)),
amount_recouped =
DECODE(p_dist_changes_rec.amount_recouped_tbl(i)
, NULL, amount_recouped
, nvl(amount_recouped, 0) + p_dist_changes_rec.amount_recouped_tbl(i)),
retainage_withheld_amount =
DECODE(p_dist_changes_rec.retainage_withheld_amt_tbl(i)
, NULL, retainage_withheld_amount
, nvl(retainage_withheld_amount, 0) + p_dist_changes_rec.retainage_withheld_amt_tbl(i)),
retainage_released_amount =
DECODE(p_dist_changes_rec.retainage_released_amt_tbl(i)
, NULL, retainage_released_amount
, nvl(retainage_released_amount, 0) + p_dist_changes_rec.retainage_released_amt_tbl(i)),
last_update_login = nvl(p_dist_changes_rec.last_update_login_tbl(i), last_update_login),
request_id = nvl(p_dist_changes_rec.request_id_tbl(i), request_id),
last_update_date = SYSDATE, -- Bug 10021100
last_updated_by = FND_GLOBAL.USER_ID
WHERE pod.po_distribution_id = p_dist_changes_rec.po_distribution_id_tbl(i)
OR (pod.distribution_type = 'PLANNED'
AND pod.po_distribution_id =
(SELECT pod2.source_distribution_id
FROM po_distributions pod2
WHERE pod2.distribution_type = 'SCHEDULED'
AND pod2.po_distribution_id = p_dist_changes_rec.po_distribution_id_tbl(i)))
;
PO_LOG.stmt(d_mod,d_position,'Distributions updated: ' || SQL%ROWCOUNT || '; fnd_global.user_id: ' || fnd_global.user_id);
ROLLBACK TO update_document_ap_values_SP;
END; --update_document_ap_values
SELECT PO_SESSION_GT_S.nextval
INTO l_gt_key
FROM dual;
INSERT INTO PO_SESSION_GT GTT(
key,
num1, -- Shipment Id on the current Invoice
num2, -- Line Id on the current Invoice
num3, -- CurrenT Invoice Amount at Shipment Level
num6, -- Retainage Rate for the Line
num7 -- Maximum Retainage Amount for the Line
)
SELECT
l_gt_key,
p_line_location_id_tbl(i),
pl.po_line_id,
p_line_loc_match_amt_tbl(i),
pl.retainage_rate,
pl.max_retainage_amount
FROM po_lines_all pl,
po_line_locations_all pll
where pl.po_line_id = pll.po_line_id
and pll.line_location_id = p_line_location_id_tbl(i);
UPDATE PO_SESSION_GT GTO
SET GTO.NUM4 = (SELECT SUM(GTI.NUM3)
FROM PO_SESSION_GT GTI
WHERE GTI.num2 = GTO.num2);
UPDATE PO_SESSION_GT GTO
SET GTO.NUM5 = (SELECT SUM(Nvl(pll.retainage_withheld_amount,0))
FROM po_line_locations_all PLL
WHERE PLL.po_line_id = GTO.num2);
UPDATE PO_SESSION_GT GTO
SET GTO.NUM8 = (SELECT GTI.NUM4*GTI.NUM6/100
FROM PO_SESSION_GT GTI
WHERE GTI.num2 = GTO.num2);
FOR CREC IN (SELECT * FROM PO_SESSION_GT GTT WHERE GTT.key = l_gt_key ORDER BY GTT.num1)
LOOP
--#1: Check if the Max retainage Amount is defined and is less than the total retained amount calculated
-- In this case we need to retain only the difference betweem Max. Retained Amount and Already Retained Amount
IF CREC.NUM7 IS NOT NULL AND
CREC.NUM7 >= 0 AND
CREC.NUM8 >= 0 AND
CREC.NUM5+CREC.NUM8 > CREC.NUM7 THEN
UPDATE PO_SESSION_GT GTO
SET GTO.NUM9 = CREC.NUM7-CREC.NUM5
WHERE GTO.num1 = CREC.num1;
UPDATE PO_SESSION_GT GTO
SET GTO.NUM9 = -CREC.NUM5
WHERE GTO.num1 = CREC.num1 ;
UPDATE PO_SESSION_GT GTO
SET GTO.NUM9 = CREC.NUM8
WHERE GTO.num1 = CREC.num1;
UPDATE PO_SESSION_GT GTO
SET GTO.NUM10 = (SELECT (GTI.NUM3/GTI.NUM4)*GTI.NUM9
FROM PO_SESSION_GT GTI
WHERE GTI.num1 = GTO.num1);
SELECT GTT.NUM10
BULK COLLECT INTO x_amount_to_retain_tbl
FROM PO_SESSION_GT GTT
WHERE GTT.key = l_gt_key
ORDER BY GTT.num1 --input and output tbls have same ordering
;
DELETE FROM PO_SESSION_GT GTT WHERE GTT.key = l_gt_key;
SELECT pll.amount,
pll.amount_cancelled
INTO x_ship_amt_ordered,
x_ship_amt_cancelled
FROM po_line_locations pll,
rcv_transactions rt
WHERE rt.po_line_location_id = pll.line_location_id
AND rt.transaction_id = p_receive_transaction_id;
SELECT pod.amount_ordered,
pod.amount_cancelled
INTO x_dist_amt_ordered,
x_dist_amt_cancelled
FROM po_distributions pod
WHERE pod.po_distribution_id = p_po_distribution_id;
PROCEDURE update_po_ship_amounts (p_api_version IN NUMBER,
p_po_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
p_ship_amt_billed IN PO_LINE_LOCATIONS_ALL.amount_billed%TYPE,
x_ret_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_po_ship_amounts';
UPDATE po_line_locations_all
SET amount_billed = nvl(amount_billed,0) + nvl(p_ship_amt_billed,0)
WHERE line_location_id = p_po_line_location_id;
PROCEDURE update_po_dist_amounts (p_api_version IN NUMBER,
p_po_distribution_id IN PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE,
p_dist_amt_billed IN PO_DISTRIBUTIONS_ALL.amount_billed%TYPE,
x_ret_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_po_dist_amounts';
UPDATE po_distributions_all
SET amount_billed = nvl(amount_billed,0) + nvl(p_dist_amt_billed,0)
WHERE po_distribution_id = p_po_distribution_id;
UPDATE po_line_locations_all
SET final_match_flag = p_final_match_flag
WHERE line_location_id = p_entity_id_tbl(i);