[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_books_rates
(p_set_of_books_id in number,
p_asset_id in number,
p_book_type_code in varchar2,
p_transaction_header_id in number,
p_invoice_transaction_id in number,
p_exchange_date in date,
p_cost in number,
p_exchange_rate in number,
p_avg_exchange_rate in number,
p_last_updated_by in number,
p_last_update_date in date,
p_last_update_login in number,
p_complete in varchar2,
p_trigger in varchar2,
p_currency_code in varchar2,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
BEGIN
INSERT
INTO fa_mc_books_rates(set_of_books_id,
asset_id,
book_type_code,
transaction_header_id,
invoice_transaction_id,
transaction_date_entered,
cost,
exchange_rate,
avg_exchange_rate,
last_updated_by,
last_update_date,
last_update_login,
complete)
VALUES(p_set_of_books_id,
p_asset_id,
p_book_type_code,
p_transaction_header_id,
p_invoice_transaction_id,
p_exchange_date,
p_cost,
p_exchange_rate,
p_avg_exchange_rate,
p_last_updated_by,
p_last_update_date,
p_last_update_login,
p_complete);
-- this gets executed from insert_books_rates call in
-- fa_mc_asset_invoices_aid trigger when mass additions
-- creates an asset out of merged children
-- since all the invoice lines will have the same
-- invoice_transaction_id this exception is raised and
-- update the exchange_rate with weighted rate calculation
-- int_debug.print('DUP_VAL_ON_INDEX found');
UPDATE fa_mc_books_rates a
SET a.exchange_rate = decode(a.cost + p_cost,
0, 1,
(a.cost * a.exchange_rate +
p_cost * p_exchange_rate) /
(a.cost + p_cost)),
a.avg_exchange_rate = decode(a.cost + p_cost,
0, 1,
(a.cost * a.avg_exchange_rate +
p_cost * p_exchange_rate) /
(a.cost + p_cost)),
a.last_updated_by = p_last_updated_by,
a.last_update_date = p_last_update_date,
a.last_update_login = p_last_update_login,
a.complete = p_complete,
a.cost = a.cost + p_cost
WHERE a.set_of_books_id = p_set_of_books_id
AND a.asset_id = p_asset_id
AND a.book_type_code = p_book_type_code
AND nvl(a.transaction_header_id,0) =
nvl(p_transaction_header_id,0)
AND nvl(a.invoice_transaction_id,0) =
nvl(p_invoice_transaction_id,0);
SELECT exchange_rate, avg_exchange_rate, complete
INTO p_exchange_rate, p_avg_exchange_rate, p_complete
FROM fa_mc_books_rates
WHERE set_of_books_id = p_set_of_books_id
AND transaction_header_id = p_transaction_header_id;