[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure will select all the rows in fa_books for each asset being
converted and insert them into fa_mc_books. All amount columns will be
converted either with the exchange rate or a derived rate of
cost/primary_cur_cost based on the conversion basis of R or C.
The converted amounts will also be rounded using the precision and
minimum accountable unti of the reporting currency
************************************************************************ */
BEGIN
if (g_print_debug) then
fa_debug_pkg.add('convert_assets',
'Converting FA_BOOKS records',
'start');
INSERT INTO fa_mc_books(
set_of_books_id,
asset_id,
book_type_code,
transaction_header_id_in,
transaction_header_id_out,
adjusted_cost,
cost,
source_cost,
original_cost,
source_original_cost,
salvage_value,
adjustment_required_status,
retirement_pending_flag,
last_update_date,
last_updated_by,
itc_amount,
itc_basis,
recoverable_cost,
last_update_login,
reval_ceiling,
period_counter_fully_reserved,
unrevalued_cost,
allowed_deprn_limit_amount,
period_counter_lIFe_complete,
adjusted_recoverable_cost,
converted_flag,
annual_deprn_rounding_flag,
itc_amount_id,
retirement_id,
tax_request_id,
basic_rate,
adjusted_rate,
bonus_rule,
ceiling_name,
adjusted_capacity,
fully_rsvd_revals_counter,
idled_flag,
period_counter_capitalized,
period_counter_fully_retired,
production_capacity,
unit_of_measure,
percent_salvage_value,
allowed_deprn_limit,
annual_rounding_flag,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
date_placed_in_service,
date_effective,
deprn_start_date,
deprn_method_code,
life_in_months,
rate_adjustment_factor,
prorate_convention_code,
prorate_date,
cost_change_flag,
capitalize_flag,
depreciate_flag,
date_ineffective,
conversion_date,
original_deprn_start_date,
salvage_type,
deprn_limit_type
)
SELECT p_rsob_id,
bk.asset_id,
bk.book_type_code,
bk.transaction_header_id_in,
bk.transaction_header_id_out,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.adjusted_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.adjusted_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.adjusted_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.adjusted_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
bk.cost,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.original_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.original_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.original_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.original_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
bk.original_cost,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.salvage_value *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.salvage_value/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.salvage_value *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.salvage_value/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
bk.adjustment_required_status,
bk.retirement_pending_flag,
bk.last_update_date,
bk.last_updated_by,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.itc_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.itc_amount /
p_denominator_rate )
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.itc_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.itc_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
bk.itc_basis,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.recoverable_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.recoverable_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.recoverable_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.recoverable_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
bk.last_update_login,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.reval_ceiling *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.reval_ceiling/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.reval_ceiling *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.reval_ceiling/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
bk.period_counter_fully_reserved,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.unrevalued_cost *
(cr.cost/cr.primary_cur_cost)),
'R', bk.unrevalued_cost/
p_denominator_rate
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.unrevalued_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.unrevalued_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.allowed_deprn_limit_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.allowed_deprn_limit_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.allowed_deprn_limit_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.allowed_deprn_limit_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
bk.period_counter_lIFe_complete,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (bk.adjusted_recoverable_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.adjusted_recoverable_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (bk.adjusted_recoverable_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (bk.adjusted_recoverable_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
'Y',
bk.annual_deprn_rounding_flag,
bk.itc_amount_id,
bk.retirement_id,
bk.tax_request_id,
bk.basic_rate,
bk.adjusted_rate,
bk.bonus_rule,
bk.ceiling_name,
bk.adjusted_capacity,
bk.fully_rsvd_revals_counter,
bk.idled_flag,
bk.period_counter_capitalized,
bk.period_counter_fully_retired,
bk.production_capacity,
bk.unit_of_measure,
bk.percent_salvage_value,
bk.allowed_deprn_limit,
bk.annual_rounding_flag,
bk.global_attribute1,
bk.global_attribute2,
bk.global_attribute3,
bk.global_attribute4,
bk.global_attribute5,
bk.global_attribute6,
bk.global_attribute7,
bk.global_attribute8,
bk.global_attribute9,
bk.global_attribute10,
bk.global_attribute11,
bk.global_attribute12,
bk.global_attribute13,
bk.global_attribute14,
bk.global_attribute15,
bk.global_attribute16,
bk.global_attribute17,
bk.global_attribute18,
bk.global_attribute19,
bk.global_attribute20,
bk.global_attribute_category,
bk.date_placed_in_service,
bk.date_effective,
bk.deprn_start_date,
bk.deprn_method_code,
bk.life_in_months,
bk.rate_adjustment_factor,
bk.prorate_convention_code,
bk.prorate_date,
bk.cost_change_flag,
bk.capitalize_flag,
bk.depreciate_flag,
bk.date_ineffective,
bk.conversion_date,
bk.original_deprn_start_date,
bk.salvage_type,
bk.deprn_limit_type
FROM
fa_books bk,
fa_mc_conversion_rates cr
WHERE
cr.asset_id = bk.asset_id AND
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
bk.book_type_code = cr.book_type_code AND
cr.status = 'S';
This procedure will select all the rows in fa_asset_invoices for each
asset being converted and insert them into fa_mc_asset_invoices.
All amount columns will be
converted either with the exchange rate or a derived rate of
cost/primary_cur_cost based on the conversion basis of R or C.
The converted amounts will also be rounded using the precision and
minimum accountable unti of the reporting currency
************************************************************************ */
l_book_class varchar2(15);
SELECT book_class
INTO l_book_class
FROM fa_book_controls
WHERE book_type_code = p_book_type_code;
INSERT INTO fa_mc_asset_invoices(
set_of_books_id,
exchange_rate,
asset_id,
asset_invoice_id,
invoice_transaction_id_in,
fixed_assets_cost,
payables_cost,
unrevalued_cost,
po_vendor_id,
date_effective,
date_ineffective,
invoice_transaction_id_out,
deleted_flag,
po_number,
invoice_number,
payables_batch_name,
payables_code_combination_id,
feeder_system_name,
create_batch_date,
create_batch_id,
invoice_date,
post_batch_id,
invoice_id,
ap_distribution_line_number,
payables_units,
split_merged_code,
description,
parent_mass_addition_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category_code,
merged_code,
split_code,
merge_parent_mass_additions_id,
split_parent_mass_additions_id,
project_asset_line_id,
project_id,
task_id,
source_line_id,
invoice_distribution_id,
invoice_line_number,
po_distribution_id )
SELECT
p_rsob_id,
DECODE(cr.conversion_basis,
'C', cr.cost/cr.primary_cur_cost,
'R', decode(cr.exchange_rate,
NULL, p_numerator_rate/p_denominator_rate,
cr.exchange_rate),
p_numerator_rate/p_denominator_rate),
ai.asset_id,
ai.asset_invoice_id,
ai.invoice_transaction_id_in,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (ai.fixed_assets_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (ai.fixed_assets_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (ai.fixed_assets_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (ai.fixed_assets_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (ai.payables_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (ai.payables_cost /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (ai.payables_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (ai.payables_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (ai.unrevalued_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (ai.unrevalued_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (ai.unrevalued_cost *
(cr.cost/cr.primary_cur_cost)),
'R', (ai.unrevalued_cost/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
ai.po_vendor_id,
ai.date_effective,
ai.date_ineffective,
ai.invoice_transaction_id_out,
ai.deleted_flag,
ai.po_number,
ai.invoice_number,
ai.payables_batch_name,
ai.payables_code_combination_id,
ai.feeder_system_name,
ai.create_batch_date,
ai.create_batch_id,
ai.invoice_date,
ai.post_batch_id,
ai.invoice_id,
ai.ap_distribution_line_number,
ai.payables_units,
ai.split_merged_code,
ai.description,
ai.parent_mass_addition_id,
ai.last_update_date,
ai.last_updated_by,
ai.created_by,
ai.creation_date,
ai.last_update_login,
ai.attribute1,
ai.attribute2,
ai.attribute3,
ai.attribute4,
ai.attribute5,
ai.attribute6,
ai.attribute7,
ai.attribute8,
ai.attribute9,
ai.attribute10,
ai.attribute11,
ai.attribute12,
ai.attribute13,
ai.attribute14,
ai.attribute15,
ai.attribute_category_code,
ai.merged_code,
ai.split_code,
ai.merge_parent_mass_additions_id,
ai.split_parent_mass_additions_id,
ai.project_asset_line_id,
ai.project_id,
ai.task_id,
ai.source_line_id,
ai.invoice_distribution_id,
ai.invoice_line_number,
ai.po_distribution_id
FROM
fa_asset_invoices ai,
fa_mc_conversion_rates cr
WHERE
ai.asset_id = cr.asset_id AND
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
cr.status = 'S';
PROCEDURE insert_bks_rates(
p_rsob_id IN NUMBER,
p_book_type_code IN VARCHAR2,
p_numerator_rate IN NUMBER,
p_denominator_rate IN NUMBER,
p_precision IN NUMBER) IS
/* ************************************************************************
This procedure will insert a row for each asset converted to reporting
book. It will contain the transaction_header_id_in from the active row
fa_books at the time of conversion and has the rate that was used
to convert the asset transactions. This is the ratio of the cost in
reporting book divided by the cost in primary book. For assets that
have a cost of 0 we will use the exchange_rate as of the initial
conversion date for the Reporting Book
************************************************************************ */
BEGIN
if (g_print_debug) then
fa_debug_pkg.add('convert_assets',
'Inserting into FA_MC_BOOKS_RATES',
'start');
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)
SELECT p_rsob_id,
mcbk.asset_id,
p_book_type_code,
mcbk.transaction_header_id_in,
NULL,
bk.date_effective,
bk.cost,
DECODE(cr.conversion_basis,
'R', decode(cr.exchange_rate,
NULL, (p_numerator_rate/
p_denominator_rate),
decode(bk.cost,
0, cr.exchange_rate,
(mcbk.cost/bk.cost))),
decode(bk.cost,
0, (p_numerator_rate/
p_denominator_rate),
(mcbk.cost/bk.cost))),
DECODE(cr.conversion_basis,
'R', decode(cr.exchange_rate,
NULL, (p_numerator_rate/
p_denominator_rate),
decode(bk.cost,
0, cr.exchange_rate,
(mcbk.cost/bk.cost))),
decode(bk.cost,
0, (p_numerator_rate/
p_denominator_rate),
(mcbk.cost/bk.cost))),
mcbk.last_updated_by,
mcbk.last_update_date,
mcbk.last_update_login,
'Y'
FROM
fa_mc_books mcbk,
fa_books bk,
fa_mc_conversion_rates cr
WHERE
cr.status = 'S' AND
cr.asset_id = bk.asset_id AND
bk.asset_id = mcbk.asset_id AND
cr.set_of_books_id = p_rsob_id AND
bk.date_ineffective is NULL AND
bk.transaction_header_id_in =
mcbk.transaction_header_id_in AND
cr.book_type_code = p_book_type_code AND
bk.book_type_code = cr.book_type_code AND
mcbk.set_of_books_id = cr.set_of_books_id AND
mcbk.book_type_code = bk.book_type_code;
calling_fn => 'fa_mc_upg2_pkg.insert_books_rates');
END insert_bks_rates;
sheet accounts for the candidate assets. The first insert will insert
all rows in the fiscal year being converted and the second insert
inserts all prior years rows.
************************************************************************ */
BEGIN
if (g_print_debug) then
fa_debug_pkg.add('convert_assets',
'Converting FA_ADJUSTMENTS records',
'start');
INSERT INTO fa_mc_adjustments(
set_of_books_id,
transaction_header_id,
source_type_code,
adjustment_type,
debit_credit_flag,
code_combination_id,
book_type_code,
asset_id,
adjustment_amount,
distribution_id,
last_update_date,
last_updated_by,
last_update_login,
annualized_adjustment,
je_header_id,
je_line_num,
period_counter_adjusted,
period_counter_created,
asset_invoice_id,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
converted_flag)
SELECT
p_rsob_id,
aj.transaction_header_id,
aj.source_type_code,
aj.adjustment_type,
aj.debit_credit_flag,
aj.code_combination_id,
aj.book_type_code,
aj.asset_id,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (aj.adjustment_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.adjustment_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (aj.adjustment_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.adjustment_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
aj.distribution_id,
aj.last_update_date,
aj.last_updated_by,
aj.last_update_login,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (aj.annualized_adjustment *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.annualized_adjustment/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (aj.annualized_adjustment *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.annualized_adjustment/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
aj.je_header_id,
aj.je_line_num,
aj.period_counter_adjusted,
aj.period_counter_created,
aj.asset_invoice_id,
aj.global_attribute1,
aj.global_attribute2,
aj.global_attribute3,
aj.global_attribute4,
aj.global_attribute5,
aj.global_attribute6,
aj.global_attribute7,
aj.global_attribute8,
aj.global_attribute9,
aj.global_attribute10,
aj.global_attribute11,
aj.global_attribute12,
aj.global_attribute13,
aj.global_attribute14,
aj.global_attribute15,
aj.global_attribute16,
aj.global_attribute17,
aj.global_attribute18,
aj.global_attribute19,
aj.global_attribute20,
aj.global_attribute_category,
'Y'
FROM
fa_adjustments aj,
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
cr.asset_id = aj.asset_id AND
aj.book_type_code = cr.book_type_code AND
aj.period_counter_created between p_start_pc
and p_end_pc AND
cr.status = 'S';
INSERT INTO fa_mc_adjustments(
set_of_books_id,
transaction_header_id,
source_type_code,
adjustment_type,
debit_credit_flag,
code_combination_id,
book_type_code,
asset_id,
adjustment_amount,
distribution_id,
last_update_date,
last_updated_by,
last_update_login,
annualized_adjustment,
je_header_id,
je_line_num,
period_counter_adjusted,
period_counter_created,
asset_invoice_id,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
converted_flag)
SELECT
p_rsob_id,
aj.transaction_header_id,
aj.source_type_code,
aj.adjustment_type,
aj.debit_credit_flag,
aj.code_combination_id,
aj.book_type_code,
aj.asset_id,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (aj.adjustment_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.adjustment_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (aj.adjustment_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.adjustment_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
aj.distribution_id,
aj.last_update_date,
aj.last_updated_by,
aj.last_update_login,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (aj.annualized_adjustment *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.annualized_adjustment/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (aj.annualized_adjustment *
(cr.cost/cr.primary_cur_cost)),
'R', (aj.annualized_adjustment/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
aj.je_header_id,
aj.je_line_num,
aj.period_counter_adjusted,
aj.period_counter_created,
aj.asset_invoice_id,
aj.global_attribute1,
aj.global_attribute2,
aj.global_attribute3,
aj.global_attribute4,
aj.global_attribute5,
aj.global_attribute6,
aj.global_attribute7,
aj.global_attribute8,
aj.global_attribute9,
aj.global_attribute10,
aj.global_attribute11,
aj.global_attribute12,
aj.global_attribute13,
aj.global_attribute14,
aj.global_attribute15,
aj.global_attribute16,
aj.global_attribute17,
aj.global_attribute18,
aj.global_attribute19,
aj.global_attribute20,
aj.global_attribute_category,
'Y'
FROM
fa_adjustments aj,
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
cr.asset_id = aj.asset_id AND
aj.book_type_code = cr.book_type_code AND
aj.period_counter_created < p_start_pc AND
aj.adjustment_type IN ( 'COST',
'COST CLEARING',
'CIP COST',
'RESERVE',
'REVAL RESERVE',
'DEPRN ADJUST',
'INTERCO AP',
'INTERCO AR',
'PROCEEDS CLR',
'REMOVALCOST CLR') AND
cr.status = 'S';
SELECT
maj.rowid,
mrt.cost_retired,
mrt.rowid,
mrt.reval_reserve_retired,
mrt.proceeds_of_sale,
mrt.cost_of_removal,
maj.transaction_header_id
FROM
fa_mc_adjustments maj,
fa_deprn_periods dp,
fa_mc_retirements mrt,
fa_retirements rt,
fa_mc_conversion_rates cr
WHERE
cr.book_type_code = p_book_type_code AND
cr.set_of_books_id = p_rsob_id AND
cr.status = 'S' AND
cr.asset_id = rt.asset_id AND
mrt.retirement_id = rt.retirement_id AND
rt.book_type_code = cr.book_type_code AND
rt.date_effective >= dp.period_open_date AND
dp.book_type_code = rt.book_type_code AND
dp.period_counter = p_start_pc AND
nvl(mrt.nbv_retired,0) <> 0 AND
maj.set_of_books_id = p_rsob_id AND
maj.book_type_code = rt.book_type_code AND
maj.asset_id = rt.asset_id AND
maj.transaction_header_id =
rt.transaction_header_id_in AND
rt.transaction_header_id_out is NULL AND
maj.adjustment_type = 'NBV RETIRED';
SELECT
nvl(sum(decode(maj.debit_credit_flag,
'DR', maj.adjustment_amount,
'CR', -1 * maj.adjustment_amount)),0)
FROM
fa_mc_adjustments maj
WHERE
maj.set_of_books_id = p_rsob_id AND
maj.book_type_code = p_book_type_code AND
maj.transaction_header_id = l_trx_id AND
maj.adjustment_type = 'RESERVE';
UPDATE fa_mc_adjustments
SET adjustment_amount = l_nbv_retired
WHERE rowid = l_maj_rowid;
UPDATE fa_mc_retirements
SET gain_loss_amount = l_gain_loss,
nbv_retired = l_nbv_retired
WHERE rowid = l_mrt_rowid;
INSERT INTO fa_mc_retirements(
set_of_books_id,
retirement_id,
cost_retired,
status,
last_update_date,
last_updated_by,
cost_of_removal,
nbv_retired,
gain_loss_amount,
proceeds_of_sale,
itc_recaptured,
stl_deprn_amount,
last_update_login,
reval_reserve_retired,
unrevalued_cost_retired,
converted_flag,
book_type_code,
asset_id,
transaction_header_id_in,
transaction_header_id_out,
date_retired,
date_effective,
retirement_prorate_convention,
units,
gain_loss_type_code,
retirement_type_code,
itc_recapture_id,
reference_num,
sold_to,
trade_in_asset_id,
stl_method_code,
stl_life_in_months,
created_by,
creation_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category_code
)
SELECT p_rsob_id,
rt.retirement_id,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.cost_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.cost_retired/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.cost_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.cost_retired/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
rt.status,
rt.last_update_date,
rt.last_updated_by,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.cost_of_removal *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.cost_of_removal /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.cost_of_removal *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.cost_of_removal /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.nbv_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.nbv_retired /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.nbv_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.nbv_retired /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.gain_loss_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.gain_loss_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.gain_loss_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.gain_loss_amount /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.proceeds_of_sale *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.proceeds_of_sale/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.proceeds_of_sale *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.proceeds_of_sale /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.itc_recaptured *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.itc_recaptured/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.itc_recaptured *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.itc_recaptured /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.stl_deprn_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.stl_deprn_amount /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.stl_deprn_amount *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.stl_deprn_amount /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
rt.last_update_login,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.reval_reserve_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.reval_reserve_retired /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.reval_reserve_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.reval_reserve_retired /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C', (rt.unrevalued_cost_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.unrevalued_cost_retired /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C', (rt.unrevalued_cost_retired *
(cr.cost/cr.primary_cur_cost)),
'R', (rt.unrevalued_cost_retired /
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
'Y',
rt.book_type_code,
rt.asset_id,
rt.transaction_header_id_in,
rt.transaction_header_id_out,
rt.date_retired,
rt.date_effective,
rt.retirement_prorate_convention,
rt.units,
rt.gain_loss_type_code,
rt.retirement_type_code,
rt.itc_recapture_id,
rt.reference_num,
rt.sold_to,
rt.trade_in_asset_id,
rt.stl_method_code,
rt.stl_life_in_months,
rt.created_by,
rt.creation_date,
rt.attribute1,
rt.attribute2,
rt.attribute3,
rt.attribute4,
rt.attribute5,
rt.attribute6,
rt.attribute7,
rt.attribute8,
rt.attribute9,
rt.attribute10,
rt.attribute11,
rt.attribute12,
rt.attribute13,
rt.attribute14,
rt.attribute15,
rt.attribute_category_code
FROM
fa_retirements rt,
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code AND
cr.asset_id = rt.asset_id AND
rt.book_type_code = cr.book_type_code AND
cr.status = 'S';
SELECT dp.period_num
INTO l_period_num
FROM
fa_deprn_periods dp
WHERE dp.book_type_code = p_book_type_code AND
period_counter = p_end_pc;
inserts them into fa_mc_deprn_summary. First it selects and converts
the BOOKS row for all the assets. Then depending on the convert order of
of F or L it converts the DEPRN rows for the assets. When the convert_order
is F, all the DEPRN rows in current year are converted and when the
convert_order is L, only the last DEPRN in a prior fiscal year is
converted.
The rows in fa_deprn_summary are fetched one at a time and ordered by
asset_id and period_counter. The deprn_amount column for pc is
calculated using a ratio of cost in fa_mc_books * cost in fa_books/
deprn_amount in primary currency. deprn_reserve and ytd_deprn for the
first period_counter in the current year are also calulated the same way.
For subsequent period counters the deprn_amount for current period is
added to deprn_reserve of previous period. This is necessary to round
reserve correctly as it is then used in converting and rounding
fa_deprn_detail
************************************************************************ */
l_last_asset_id number;
SELECT
ds.book_type_code,
ds.asset_id,
ds.deprn_run_date,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.deprn_amount /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.deprn_amount /
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.ytd_deprn /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.ytd_deprn /
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.deprn_reserve/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.deprn_reserve/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.deprn_source_code,
mcbk.adjusted_cost,
ds.bonus_rate,
ds.ltd_production,
ds.period_counter,
ds.production,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_amortization/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_amortization/
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_amortization_basis/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_amortization_basis/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_deprn_expense/
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_reserve/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_reserve/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.ytd_production,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.ytd_reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.ytd_reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.prior_fy_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.prior_fy_expense/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.deprn_reserve
FROM
fa_deprn_summary ds,
fa_deprn_periods dp,
fa_mc_books mcbk,
fa_books bk,
fa_mc_conversion_rates cr
WHERE
ds.asset_id = mcbk.asset_id AND
cr.book_type_code = p_book_type_code AND
cr.set_of_books_id = p_rsob_id AND
bk.asset_id = cr.asset_id AND
bk.book_type_code = cr.book_type_code AND
mcbk.asset_id = bk.asset_id AND
mcbk.book_type_code = bk.book_type_code AND
mcbk.book_type_code = dp.book_type_code AND
ds.deprn_source_code = 'DEPRN' AND
dp.period_counter = ds.period_counter AND
dp.book_type_code = ds.book_type_code AND
nvl(dp.period_CLOSE_date, sysdate)
between bk.date_effective and
nvl(bk.date_ineffective, sysdate) AND
bk.transaction_header_id_in =
mcbk.transaction_header_id_in AND
mcbk.set_of_books_id = cr.set_of_books_id AND
cr.status = 'S' AND
ds.period_counter between p_start_pc and
p_end_pc
ORDER BY
ds.book_type_code,
ds.asset_id,
ds.period_counter;
INSERT INTO fa_mc_deprn_summary(set_of_books_id,
book_type_code,
asset_id,
deprn_run_date,
deprn_amount,
ytd_deprn,
deprn_reserve,
deprn_source_code,
adjusted_cost,
bonus_rate,
ltd_production,
period_counter,
production,
reval_amortization,
reval_amortization_basis,
reval_deprn_expense,
reval_reserve,
ytd_production,
ytd_reval_deprn_expense,
prior_fy_expense,
converted_flag)
SELECT
p_rsob_id,
ds.book_type_code,
ds.asset_id,
ds.deprn_run_date,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.deprn_amount /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.deprn_amount /
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.ytd_deprn /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.ytd_deprn /
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.deprn_reserve/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.deprn_reserve/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.deprn_source_code,
mcbk.adjusted_cost,
ds.bonus_rate,
ds.ltd_production,
ds.period_counter,
ds.production,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_amortization/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_amortization/
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_amortization_basis/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_amortization_basis/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_deprn_expense/
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_reserve/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_reserve/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.ytd_production,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.ytd_reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.ytd_reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.prior_fy_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.prior_fy_expense/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
'Y'
FROM
fa_deprn_summary ds,
fa_deprn_periods dp,
fa_mc_books mcbk,
fa_books bk,
fa_mc_conversion_rates cr
WHERE
ds.asset_id = mcbk.asset_id AND
ds.deprn_source_code = 'BOOKS' AND
cr.book_type_code = p_book_type_code AND
cr.set_of_books_id = p_rsob_id AND
cr.status = 'S' AND
bk.asset_id = cr.asset_id AND
mcbk.asset_id = bk.asset_id AND
mcbk.set_of_books_id = cr.set_of_books_id AND
mcbk.book_type_code = dp.book_type_code AND
mcbk.book_type_code = bk.book_type_code AND
bk.book_type_code = cr.book_type_code AND
dp.book_type_code = ds.book_type_code AND
nvl(dp.period_close_date, sysdate)
between bk.date_effective and
nvl(bk.date_ineffective, sysdate) AND
dp.period_counter = ds.period_counter + 1 AND
bk.transaction_header_id_in = mcbk.transaction_header_id_in;
'Inserted BOOKS rows');
select sum(decode(debit_credit_flag,
'DR', -1 * adjustment_amount,
adjustment_amount))
into l_ret_reserve
from fa_mc_adjustments
where asset_id = l_asset_id
and book_type_code = l_book_type_code
and set_of_books_id = p_rsob_id
and period_counter_created = l_period_counter
and source_type_code = 'RETIREMENT'
and adjustment_type = 'RESERVE';
INSERT INTO fa_mc_deprn_summary(set_of_books_id,
book_type_code,
asset_id,
deprn_run_date,
deprn_amount,
ytd_deprn,
deprn_reserve,
deprn_source_code,
adjusted_cost,
bonus_rate,
ltd_production,
period_counter,
production,
reval_amortization,
reval_amortization_basis,
reval_deprn_expense,
reval_reserve,
ytd_production,
ytd_reval_deprn_expense,
prior_fy_expense,
converted_flag)
VALUES(
p_rsob_id,
l_book_type_code,
l_asset_id,
l_deprn_run_date,
l_deprn_amount,
l_ytd_deprn,
l_deprn_reserve,
l_deprn_source_code,
l_adjusted_cost,
l_bonus_rate,
l_ltd_prod,
l_period_counter,
l_prod,
l_reval_amort,
l_reval_amort_basis,
l_reval_exp,
l_reval_reserve,
l_ytd_prod,
l_ytd_reval_exp,
l_prior_fy_exp,
'Y');
INSERT INTO fa_mc_deprn_summary(set_of_books_id,
book_type_code,
asset_id,
deprn_run_date,
deprn_amount,
ytd_deprn,
deprn_reserve,
deprn_source_code,
adjusted_cost,
bonus_rate,
ltd_production,
period_counter,
production,
reval_amortization,
reval_amortization_basis,
reval_deprn_expense,
reval_reserve,
ytd_production,
ytd_reval_deprn_expense,
prior_fy_expense,
converted_flag)
SELECT
p_rsob_id,
ds.book_type_code,
ds.asset_id,
ds.deprn_run_date,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.deprn_amount /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.deprn_amount /
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.ytd_deprn /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.ytd_deprn /
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.deprn_reserve/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.deprn_reserve/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.deprn_source_code,
mcbk.adjusted_cost,
ds.bonus_rate,
ds.ltd_production,
ds.period_counter,
ds.production,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_amortization/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_amortization/
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_amortization_basis/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_amortization_basis/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_deprn_expense/
decode(bk.cost,0,1,bk.cost)))/
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.reval_reserve/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.reval_reserve/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.ytd_production,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.ytd_reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.ytd_reval_deprn_expense/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (ds.prior_fy_expense/
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (ds.prior_fy_expense/
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
'Y'
FROM
fa_deprn_summary ds,
fa_deprn_periods dp,
fa_mc_books mcbk,
fa_books bk,
fa_mc_conversion_rates cr
WHERE
cr.book_type_code = p_book_type_code AND
cr.set_of_books_id = p_rsob_id AND
cr.status = 'S' AND
ds.period_counter = cr.last_period_counter AND
ds.deprn_source_code = 'DEPRN' AND
dp.book_type_code = mcbk.book_type_code AND
dp.period_counter = ds.period_counter AND
dp.book_type_code = ds.book_type_code AND
nvl(dp.period_CLOSE_date, sysdate)
between bk.date_effective and
nvl(bk.date_ineffective, sysdate) AND
bk.transaction_header_id_in =
mcbk.transaction_header_id_in AND
bk.asset_id = cr.asset_id AND
bk.asset_id = mcbk.asset_id AND
ds.asset_id = mcbk.asset_id AND
bk.book_type_code = cr.book_type_code AND
mcbk.book_type_code = bk.book_type_code AND
mcbk.set_of_books_id = cr.set_of_books_id;
-- cursor to select all rows from fa_deprn_detail
CURSOR dd_row IS
SELECT /*+ ordered leading(cr)
index(ds fa_mc_deprn_summary_u1)
index(dd fa_deprn_detail_n1)
index(dh fa_distribution_history_u1)
index(dp fa_deprn_periods_u3)
index(bk fa_books_n1)
index(mcbk fa_mc_books_u1)
index(ah fa_asset_history_n2) */
dd.book_type_code,
dd.asset_id,
dd.distribution_id,
dd.period_counter,
nvl(ds.deprn_reserve,0),
mcbk.cost,
to_char(nvl(dh.date_ineffective, sysdate+1),
'YYYYMMDD HH24:MI:SS'),
to_char(nvl(dp.period_close_date, sysdate),
'YYYYMMDD HH24:MI:SS'),
nvl(dh.units_assigned,0),
nvl(ah.units,0),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (dd.deprn_amount /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (dd.deprn_amount /
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost * (dd.ytd_deprn /
decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost * (dd.ytd_deprn /
decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost *(dd.deprn_adjustment_amount
/ decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost *(dd.deprn_adjustment_amount
/ decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
dd.deprn_source_code,
dd.deprn_expense_je_line_num,
dd.deprn_reserve_je_line_num,
dd.reval_amort_je_line_num,
dd.reval_reserve_je_line_num,
dd.je_header_id,
ds.reval_amortization,
ds.reval_deprn_expense,
ds.reval_reserve,
DECODE(p_mau,
NULL, ROUND(
(mcbk.cost *(dd.ytd_reval_deprn_expense
/ decode(bk.cost,0,1,bk.cost))),
p_precision),
ROUND(
(mcbk.cost *(dd.ytd_reval_deprn_expense
/ decode(bk.cost,0,1,bk.cost))) /
p_mau) * p_mau),
ds.deprn_amount,
dd.deprn_run_date
FROM
fa_mc_deprn_summary ds,
fa_deprn_detail dd,
fa_deprn_periods dp,
fa_books bk,
fa_mc_books mcbk,
fa_distribution_history dh,
fa_asset_history ah,
fa_mc_conversion_rates cr
WHERE
cr.status = 'S' AND
cr.asset_id = ds.asset_id AND
cr.book_type_code = p_book_type_code AND
ds.book_type_code = cr.book_type_code AND
ds.book_type_code = dd.book_type_code AND
cr.set_of_books_id = p_rsob_id AND
ds.set_of_books_id = cr.set_of_books_id AND
ds.asset_id = dd.asset_id AND
bk.book_type_code = ds.book_type_code AND
mcbk.set_of_books_id = ds.set_of_books_id AND
bk.transaction_header_id_in =
mcbk.transaction_header_id_in AND
ds.period_counter = dd.period_counter AND
dp.book_type_code = dd.book_type_code AND
dp.period_counter =
DECODE(ds.deprn_source_code,
'DEPRN', dd.period_counter,
'BOOKS', dd.period_counter + 1) AND
bk.book_type_code = dd.book_type_code AND
mcbk.book_type_code = bk.book_type_code AND
bk.asset_id = dd.asset_id AND
mcbk.asset_id = dd.asset_iD AND
nvl(dp.period_close_date, sysdate)
between bk.date_effective and
nvl(bk.date_ineffective, sysdate) AND
dh.distribution_id = dd.distribution_id AND
ah.asset_id = dd.asset_id AND
nvl(dp.period_close_date, sysdate)
between ah.date_effective and
nvl(ah.date_ineffective, sysdate)
order by
dd.book_type_code,
dd.period_counter,
dd.asset_id,
dd.distribution_id;
dbms_output.put_line('inserting set_of_books_id' || p_rsob_id);
dbms_output.put_line('inserting book_type_code' || p_book_type_code);
dbms_output.put_line('inserting asset_id ' || l_asset_id);
dbms_output.put_line('inserting period_counter' || l_period_counter);
dbms_output.put_line('inserting distribution_id' || l_distribution_id);
-- Now insert the row into FA_MC_DEPRN_DETAIL
INSERT INTO FA_MC_DEPRN_DETAIL(
set_of_books_id,
book_type_code,
asset_id,
period_counter,
distribution_id,
deprn_source_code,
deprn_run_date,
deprn_amount,
ytd_deprn,
deprn_reserve,
addition_cost_to_clear,
cost,
deprn_adjustment_amount,
deprn_expense_je_line_num,
deprn_reserve_je_line_num,
reval_amort_je_line_num,
reval_reserve_je_line_num,
je_header_id,
reval_amortization,
reval_deprn_expense,
reval_reserve,
ytd_reval_deprn_expense,
converted_flag)
VALUES(
p_rsob_id,
p_book_type_code,
l_asset_id,
l_period_counter,
l_distribution_id,
l_deprn_source_code,
l_deprn_run_date,
l_deprn_amount,
l_ytd_deprn,
l_part_deprn_rsv,
DECODE(l_deprn_source_code,
'B', l_part_cost, 0),
DECODE(l_deprn_source_code,
'D', l_part_cost, 0),
l_deprn_adjustment_amount,
l_deprn_expense_je_line_num,
l_deprn_reserve_je_line_num,
l_reval_amort_je_line_num,
l_reval_reserve_je_line_num,
l_je_header_id,
l_part_reval_amort,
l_part_reval_exp,
l_part_reval_rsv,
l_ytd_reval_deprn_expense,
'Y');
for the reporting book are first deleted. To preserve the trail as to
when the reporting book was first set up, fa_deprn_period rows will be
fetched using the first period counter in fa_mc_deprn_periods all the
way upto the current open period. This will ensure that the primary
book and reporting book have the same open periods after conversion
is completed.
************************************************************************ */
l_pc number;
SELECT min(period_counter)
INTO l_pc
FROM fa_mc_deprn_periods
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
DELETE FROM fa_mc_deprn_periods
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
INSERT INTO fa_mc_deprn_periods(
set_of_books_id,
book_type_code,
period_name,
period_counter,
fiscal_year,
period_num,
period_open_date,
period_close_date,
depreciation_batch_id,
retirement_batch_id,
reclass_batch_id,
transfer_batch_id,
addition_batch_id,
adjustment_batch_id,
deferred_deprn_batch_id,
calendar_period_open_date,
calendar_period_close_date,
cip_addition_batch_id,
cip_adjustment_batch_id,
cip_reclass_batch_id,
cip_retirement_batch_id,
cip_reval_batch_id,
cip_transfer_batch_id,
reval_batch_id,
deprn_adjustment_batch_id)
SELECT
p_rsob_id,
dp.book_type_code,
dp.period_name,
dp.period_counter,
dp.fiscal_year,
dp.period_num,
dp.period_open_date,
dp.period_close_date,
dp.depreciation_batch_id,
dp.retirement_batch_id,
dp.reclass_batch_id,
dp.transfer_batch_id,
dp.addition_batch_id,
dp.adjustment_batch_id,
dp.deferred_deprn_batch_id,
dp.calendar_period_open_date,
dp.calendar_period_close_date,
dp.cip_addition_batch_id,
dp.cip_adjustment_batch_id,
dp.cip_reclass_batch_id,
dp.cip_retirement_batch_id,
dp.cip_reval_batch_id,
dp.cip_transfer_batch_id,
dp.reval_batch_id,
dp.deprn_adjustment_batch_id
FROM
fa_deprn_periods dp
WHERE
dp.book_type_code = p_book_type_code AND
dp.period_counter between l_pc and p_end_pc;
SELECT book_class
INTO l_book_class
FROM fa_book_controls
WHERE book_type_code = p_book_type_code;
INSERT INTO fa_mc_deferred_deprn(
set_of_books_id,
corp_book_type_code,
tax_book_type_code,
asset_id,
distribution_id,
deferred_deprn_expense_ccid,
deferred_deprn_reserve_ccid,
deferred_deprn_expense_amount,
deferred_deprn_reserve_amount,
corp_period_counter,
tax_period_counter,
je_header_id,
expense_je_line_num,
reserve_je_line_num)
SELECT
p_rsob_id,
dd.corp_book_type_code,
p_book_type_code,
dd.asset_id,
dd.distribution_id,
dd.deferred_deprn_expense_ccid,
dd.deferred_deprn_reserve_ccid,
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C',(dd.deferred_deprn_expense_amount *
(cr.cost/cr.primary_cur_cost)),
'R',(dd.deferred_deprn_expense_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C',(dd.deferred_deprn_expense_amount *
(cr.cost/cr.primary_cur_cost)),
'R',(dd.deferred_deprn_expense_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
DECODE(p_mau,
NULL, ROUND(
DECODE(cr.conversion_basis,
'C',(dd.deferred_deprn_reserve_amount *
(cr.cost/cr.primary_cur_cost)),
'R',(dd.deferred_deprn_reserve_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate)
), p_precision),
ROUND(
DECODE(cr.conversion_basis,
'C',(dd.deferred_deprn_reserve_amount *
(cr.cost/cr.primary_cur_cost)),
'R',(dd.deferred_deprn_reserve_amount/
p_denominator_rate)
*
DECODE(cr.exchange_rate,
NULL, p_numerator_rate,
cr.exchange_rate))
/ p_mau) * p_mau),
dd.corp_period_counter,
dd.tax_period_counter,
dd.je_header_id,
dd.expense_je_line_num,
dd.reserve_je_line_num
FROM
fa_deferred_deprn dd,
fa_book_controls bc,
fa_mc_conversion_rates cr
WHERE
cr.book_type_code = p_book_type_code AND
cr.set_of_books_id = p_rsob_id AND
cr.status = 'S' AND
bc.book_type_code = cr.book_type_code AND
cr.asset_id = dd.asset_id AND
dd.corp_book_type_code = bc.distribution_source_book AND
dd.tax_book_type_code = bc.book_type_code;