[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mrc_converted_flag
into l_converted_flag
from fa_mc_book_controls
where book_type_code = p_book_type_code AND
set_of_books_id = p_rsob_id
for update of
mrc_converted_flag
NOWAIT;
SELECT
glsob.set_of_books_id,
mcbc.primary_set_of_books_id,
mcbc.currency_code,
mcbc.primary_currency_code,
mcbc.mrc_converted_flag,
mcbc.enabled_flag,
nvl(bc.mc_source_flag,'N')
FROM
fa_mc_book_controls mcbc,
gl_sets_of_books glsob,
fa_book_controls bc
WHERE
bc.book_type_code = p_book_type_code AND
mcbc.book_type_code = bc.book_type_code AND
bc.set_of_books_id = mcbc.primary_set_of_books_id AND
glsob.name = p_reporting_book AND
glsob.set_of_books_id = mcbc.set_of_books_id AND
glsob.mrc_sob_type_code = 'R';
SELECT distinct exchange_rate, conversion_basis
FROM
fa_mc_conversion_rates
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
SELECT count(*)
INTO l_count
FROM fa_mc_conversion_rates
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code AND
conversion_basis is null AND
status in ('L','F');
SELECT
glba.alc_init_date,
glba.alc_init_period,
glba.alc_initializing_rate_type,
glba.alc_initializing_rate_date,
glps.effective_period_num
FROM
gl_period_statuses glps,
gl_ledger_relationships glba
WHERE
glba.target_ledger_id = p_rsob_id AND
glba.source_ledger_id = p_psob_id AND
glps.application_id = 101 AND
glba.application_id = 101 AND
glps.ledger_id = p_psob_id AND
glba.relationship_type_code = 'SUBLEDGER' AND
glps.period_name = glba.alc_init_period;
SELECT
ps.period_name,
ps.period_type,
ps.PERIOD_YEAR
FROM
gl_period_statuses ps
WHERE
ps.application_id = 101 AND
ps.set_of_books_id = p_psob_id AND
ps.effective_period_num = (
SELECT min(ps2.effective_period_num)
FROM gl_period_statuses ps2
WHERE
ps2.application_id =
ps.application_id AND
ps2.set_of_books_id =
ps.set_of_books_id AND
ps2. closing_status in
('F', 'N') AND
ps2.effective_period_num > (
SELECT ps3.effective_period_num
FROM gl_period_statuses ps3,
gl_sets_of_books sb
WHERE ps3.application_id =
ps.application_id
AND ps3.set_of_books_id =
ps.set_of_books_id
AND ps3.period_name =
sb.latest_opened_period_name
AND sb.set_of_books_id =
ps.set_of_books_id));
SELECT ps.period_name, ps.end_date
FROM gl_period_statuses ps
WHERE ps.application_id = 101 AND
ps.set_of_books_id = p_psob_id AND
ps.effective_period_num = (
SELECT max(ps2.effective_period_num)
FROM gl_period_statuses ps2
WHERE ps2.application_id =
ps.application_id AND
ps2.set_of_books_id =
ps.set_of_books_id AND
ps2.effective_period_num <
l_first_period_num AND
ps2.adjustment_period_flag <> 'Y');
SELECT
dp.fiscal_year,
dp.period_counter,
dp2.period_counter
FROM
fa_deprn_periods dp,
fa_deprn_periods dp2,
fa_deprn_periods dp3,
fa_book_controls bc
WHERE
bc.book_type_code = p_book_type_code AND
dp3.period_name = l_prior_fa_period AND
dp3.book_type_code = bc.book_type_code AND
dp3.fiscal_year = dp.fiscal_year AND
dp.book_type_code = bc.book_type_code AND
/* BUG# 1483489 - need to dynamically get the period_num
-- bridgway 10/30/00
dp.period_num = 1 AND
*/
dp.period_num =
(select min(period_num)
from fa_deprn_periods dp4
where dp4.book_type_code = dp.book_type_code
and dp4.fiscal_year = dp.fiscal_year) AND
dp2.book_type_code = bc.book_type_code AND
dp2.period_close_date is NULL;
select ct.NUMBER_PER_FISCAL_YEAR
into l_fa_period_count
from fa_calendar_types ct,
fa_book_controls bc
where bc.book_type_code = p_book_type_code
and bc.deprn_calendar = ct.CALENDAR_TYPE;
select count(*)
into l_gl_period_count
from gl_period_statuses ps
where ps.application_id = 101
AND ps.set_of_books_id = p_psob_id
AND ps.period_year = l_period_year
AND ps.adjustment_period_flag <> 'Y';
SELECT
dp.fiscal_year,
dp.period_counter,
dp2.period_counter
INTO l_start_fy, X_start_pc, X_end_pc
FROM
fa_deprn_periods dp2,
fa_deprn_periods dp
WHERE dp.book_type_code = p_book_type_code
AND dp2.book_type_code = dp.book_type_code
AND dp2.period_close_date is null
AND dp.fiscal_year = dp2.fiscal_year
AND dp.period_num = 1;
This procedure is called in different modes - select, running, converted.
When called in select mode, inserts a new row into conversion history
and sets book controls also to S. This status will then be used in
transaction approval to prevent transactions in the Primary Book until
conversion is completed - status of C. The conversion_status is used to
prevent running conversion before selection. The conversion_status in
fa_mc_conversion_history and fa_mc_book_controls will be kept in synch.
*************************************************************************/
BEGIN
IF (p_mode = 'S') THEN
-- delete row from a previous run which is out of date
DELETE FROM fa_mc_conversion_history
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
INSERT INTO FA_MC_CONVERSION_HISTORY(
set_of_books_id,
book_type_code,
conversion_status,
period_counter_selected,
last_update_date)
VALUES(
p_rsob_id,
p_book_type_code,
p_mode,
p_end_pc,
sysdate);
UPDATE fa_mc_book_controls
SET conversion_status = p_mode
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
UPDATE fa_mc_conversion_history
SET conversion_status = p_mode,
period_counter_start = p_start_pc,
last_update_date = sysdate,
fixed_rate_conversion = p_fixed_conversion
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
UPDATE fa_mc_book_controls
SET conversion_status = p_mode
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
-- called when select program ends in error. delete the record from
-- conversion history to force rerun of selection program phase 1
-- rollback assets that have been inserted into rates table since
-- the last commit
FND_CONCURRENT.AF_ROLLBACK;
DELETE FROM fa_mc_conversion_history
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
UPDATE fa_mc_book_controls
SET conversion_status = NULL
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
UPDATE fa_mc_conversion_history
SET conversion_status = 'E'
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
UPDATE fa_mc_book_controls
SET conversion_status = 'E'
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
UPDATE fa_mc_conversion_history
SET conversion_status = p_mode,
period_counter_converted = p_end_pc,
last_update_date = sysdate
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
UPDATE fa_mc_book_controls
SET mrc_converted_flag = 'Y',
last_period_counter = p_end_pc - 1,
conversion_status = p_mode
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
This procedure selects all the assets in a Primary Book that need to be
converted for a given reporting book and inserts them into
fa_mc_conversion_rates. The assets selected are those that are not
fully retired as of the beginning of the fiscal year, represented by
p_start_pc, being converted. All other assets will be selected.
The assets are selected in two parts, those that have DEPRN rows in the
year being converted and those that have their last DEPRN row in a
prior year. LAST_PERIOD_COUNTER indicates the last period with a DEPRN row
for each asset and helps to avoid using max later on in conversion.
The assets are selected in a loop so that commit size does
not get too large to prevent running out of rollback segments.
************************************************************************ */
l_lock_status BOOLEAN;
DELETE FROM fa_mc_conversion_rates
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
INSERT INTO FA_MC_CONVERSION_RATES(
ASSET_ID,
SET_OF_BOOKS_ID,
BOOK_TYPE_CODE,
EXCHANGE_RATE,
COST,
PRIMARY_CUR_COST,
CONVERSION_BASIS,
STATUS,
LAST_PERIOD_COUNTER)
SELECT ad.asset_id,
p_rsob_id,
p_book_type_code,
NULL,
NULL,
bk.cost,
DECODE(p_fixed_rate,
'Y', 'R',
decode(bk.cost,
0, 'R',
NULL)),
'F',
ds.period_counter
FROM
fa_deprn_summary ds,
fa_mc_conversion_rates cr,
fa_books bk,
fa_additions ad
WHERE
bk.date_ineffective is NULL AND
bk.book_type_code = p_book_type_code AND
nvl(bk.period_counter_fully_retired, p_end_pc +1) >=
p_start_pc AND
bk.asset_id = ad.asset_id AND
cr.asset_id(+) = bk.asset_id AND
cr.set_of_books_id(+) = p_rsob_id AND
cr.book_type_code(+) = bk.book_type_code AND
cr.status is NULL AND
ds.asset_id = bk.asset_id AND
ds.book_type_code = bk.book_type_code AND
ds.period_counter = (
SELECT max(ds2.period_counter)
FROM fa_deprn_summary ds2
WHERE ds2.asset_id = ds.asset_id AND
ds2.book_type_code =
ds.book_type_code AND
ds2.period_counter between p_start_pc
and p_end_pc) AND
rownum+0 <= G_Max_Commit_Size;
-- select assets with last DEPRN row in prior Fiscal Year
INSERT INTO FA_MC_CONVERSION_RATES(
ASSET_ID,
SET_OF_BOOKS_ID,
BOOK_TYPE_CODE,
EXCHANGE_RATE,
COST,
PRIMARY_CUR_COST,
CONVERSION_BASIS,
STATUS,
LAST_PERIOD_COUNTER)
SELECT ad.asset_id,
p_rsob_id,
p_book_type_code,
NULL,
NULL,
bk.cost,
DECODE(p_fixed_rate,
'Y', 'R',
decode(bk.cost,
0, 'R',
NULL)),
'L',
ds.period_counter
FROM
fa_books bk,
fa_deprn_summary ds,
fa_additions ad,
fa_mc_conversion_rates cr
WHERE
bk.date_ineffective is NULL AND
bk.book_type_code = p_book_type_code AND
nvl(bk.period_counter_fully_retired, p_end_pc +1) >=
p_start_pc AND
bk.asset_id = ad.asset_id AND
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
cr.status is NULL AND
ds.asset_id = bk.asset_id AND
ds.book_type_code = bk.book_type_code AND
ds.period_counter = (
SELECT max(ds2.period_counter)
FROM fa_deprn_summary ds2
WHERE ds2.asset_id = ds.asset_id AND
ds2.book_type_code =
ds.book_type_code) AND
rownum+0 <= G_Max_Commit_Size;
UPDATE fa_mc_conversion_history
SET total_assets = X_total_assets
WHERE book_type_code = p_book_type_code AND
set_of_books_id = p_rsob_id;
SELECT
fc.precision, fc.minimum_accountable_unit
FROM
fnd_currencies fc
WHERE
fc.currency_code = p_to_currency;
checks to make sure that selection is not out of date(this won't happen
as we prevent transactions including depreciation from being run in the
Primary Book).
************************************************************************ */
invalid_select exception;
no_select exception;
SELECT period_counter_selected,
total_assets,
conversion_status
FROM fa_mc_conversion_history
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code AND
conversion_status in ('S', 'E', 'R');
SELECT count(*)
FROM fa_mc_conversion_rates
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code;
RAISE no_select;
RAISE invalid_select;
raise invalid_select;
WHEN invalid_select THEN
fa_srvr_msg.add_message (
calling_fn => 'fa_mc_upg1_pkg.check_preview_status',
name => 'FA_MRC_INVALID_SELECT',
token1 => 'BOOK',
value1 => p_book_type_code,
token2 => 'REPORTING_BOOK',
value2 => G_rbook_name);
WHEN no_select THEN
fa_srvr_msg.add_message (
calling_fn => 'fa_mc_upg1_pkg.check_preview_status',
name => 'FA_MRC_NO_SELECT',
token1 => 'BOOK',
value1 => p_book_type_code,
token2 => 'REPORTING_BOOK',
value2 => G_rbook_name);
Select assets which have DEPRN rows for the fiscal year being
being converted first denoted by F and THEN select the other
assets which have their last DEPRN row in prior fiscal year
denoted by status of L
************************************************************************ */
l_assets_to_convert NUMBER; -- assets to convert in this run
SELECT
count(*),
status
FROM
fa_mc_conversion_rates cr
WHERE
cr.set_of_books_id = p_rsob_id AND
cr.book_type_code = p_book_type_code
GROUP BY status;
-- select 1000 assets at a time and update the status to S to
-- indicate selected for conversion
WHILE (l_assets_processed <> l_assets_to_convert) LOOP
/*
IF (l_assets_to_convert > 0) THEN
create_drop_indexes('D');
UPDATE fa_mc_conversion_rates
SET STATUS = 'S'
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code AND
STATUS = 'F' AND
rownum <= G_Max_Commit_Size;
UPDATE fa_mc_conversion_rates
SET STATUS = 'S'
WHERE set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code AND
STATUS = 'L' AND
rownum <= G_Max_Commit_Size;
'Number of assets selected in this iteration',
l_commit_size);
fa_mc_upg2_pkg.insert_bks_rates(
p_rsob_id,
p_book_type_code,
p_numerator_rate,
p_denominator_rate,
p_precision);
-- all tables have been converted successfully for the assets selected
-- update the status to converted and commit and increment
-- assets processed with the numbers of assets converted
UPDATE fa_mc_conversion_rates
SET STATUS = DECODE(l_convert_order,
'F', 'CF',
'L', 'CL')
WHERE
set_of_books_id = p_rsob_id AND
book_type_code = p_book_type_code AND
STATUS = 'S';