[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ct.calendar_type , ct.number_per_fiscal_year , bk.life_in_months
into l_calendar_type , l_number_per_fiscal_year , l_life_in_months
from fa_calendar_types ct , fa_book_controls bc , fa_books bk
where ct.calendar_type = bc.deprn_calendar
and bk.book_type_code = p_book_type_code
and bk.date_ineffective is null
and bk.asset_id = p_asset_id
and bc.date_ineffective is null
and bc.book_type_code = p_book_type_code ;
SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code like p_book_type_code;
SELECT number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = p_calendar_type;
select fp_current_factor, fp_Reval_type
into l_current_rate , l_reval_type
from sys.dual
;
select fds.asset_id, fds.book_type_code, fds.period_counter + 1, abs(fds.ytd_deprn) ytd_deprn, fdp.fiscal_year
, fadd.current_units total_units
from fa_deprn_summary fds, fa_deprn_periods fdp
, fa_additions fadd
where fds.book_type_code = fp_book_type_code
and fdp.book_type_code = fds.book_type_code
and fdp.period_counter = fds.period_counter
-- and fds.period_counter <= fp_current_period_counter
and fds.asset_id = fp_asset_id
and fadd.asset_id = fp_asset_id
and fds.period_counter in ( select max(period_counter)
from fa_deprn_summary
where book_type_code = fds.book_type_code
and asset_id = fds.asset_id
)
;
select fdh.asset_id,
fdh.distribution_id,
fdp.period_counter latest_period_counter,
0 ytd_deprn,
fdp.period_num,
fdp.fiscal_year,
fdh.units_assigned,
nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
'ACTIVE' status,
(fdh.units_assigned/fadd.current_units) ytd_prorate_factor,
(fdh.units_assigned/fadd.current_units) normal_prorate_factor
from fa_deprn_periods fdp
, fa_distribution_history fdh
, fa_additions fadd
, fa_transaction_headers fth
where fth.book_type_code = cp_book_type_code
and fdp.book_type_code = fth.book_type_code
and fdh.asset_id = fth.asset_id
and fdh.transaction_header_id_out IS NULL
and fth.asset_id = cp_asset_id
and fadd.asset_id = fth.asset_id
and fth.transaction_type_code = 'RECLASS'
and fdp.period_counter in ( select distinct period_counter_created
from fa_adjustments
where book_type_code = fth.book_type_code
and asseT_id = fth.asset_id
and distribution_id = fdh.distribution_id
and transaction_header_id = fth.transaction_header_id
)
and not exists ( select distribution_id
from fa_deprn_detail
where asset_id = fth.asset_id
and book_type_code = fth.book_type_code
and distribution_id = fdh.distribution_id
)
union /** we need this for catchup **/
select fdd.asset_id,
fdd.distribution_id,
fdd.period_counter latest_period_counter,
fdd.ytd_deprn,
fdp.period_num,
fdp.fiscal_year,
fdh.units_assigned,
nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
'ACTIVE' status,
(fdh.units_assigned/cp_total_units) ytd_prorate_factor,
(fdh.units_assigned/cp_total_units) normal_prorate_factor
from fa_deprn_detail fdd
, fa_deprn_periods fdp
, fa_distribution_history fdh
where fdd.book_type_code = cp_book_type_code
and fdp.book_type_code = fdd.book_type_code
and fdp.period_counter = fdd.period_counter
and fdp.fiscal_year = cp_fiscal_year
and fdh.book_type_code = fdd.book_type_code
and fdh.asset_id = fdd.asset_id
and fdd.asset_id = cp_asset_id
and fdh.distribution_id = fdd.distribution_id
and fdh.transaction_header_id_out is null
and cp_total_units <> 0 -- avoid divide by zero issues
and ( fdd.asset_id, fdd.distribution_id, fdd.period_counter )
in ( select asset_id, distribution_id, max(period_counter)
from fa_deprn_detail
where book_type_code = fdd.book_type_code
and asset_id = fdd.asset_id
group by asset_id, distribution_id
);
l_prorate_dists_tab.delete;
SELECT fdh.distribution_id distribution_id,
fdh.units_assigned units_assigned,
nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
fdp.period_counter period_counter_created,
fdp.fiscal_year fiscal_year
FROM fa_distribution_history fdh,
fa_deprn_periods fdp
WHERE fdh.book_type_code = fp_book_type_code
AND fdh.asset_id = fp_asset_id
AND fdh.transaction_header_id_out IS NULL
AND fdp.book_type_code = fp_book_type_code
AND fdh.date_effective BETWEEN fdp.period_open_date AND nvl(fdp.period_close_date,sysdate);
SELECT fb.date_placed_in_service,
fb.period_counter_fully_reserved,
fb.life_in_months,
fb.depreciate_flag
FROM fa_books fb
WHERE fb.book_type_code = fp_book_type_code
AND fb.asset_id = fp_asset_id
AND fb.transaction_header_id_out IS NULL;
SELECT min(fds.period_counter)
FROM fa_deprn_summary fds
WHERE fds.book_type_code = fp_book_type_code
AND fds.asset_id = fp_asset_id
AND fds.deprn_source_code = 'DEPRN';
SELECT ct.number_per_fiscal_year
FROM fa_calendar_types ct, fa_book_controls bc
WHERE ct.calendar_type = bc.deprn_calendar
AND bc.book_type_code = fp_book_type_code;
l_prorate_dists_tab.delete;
SELECT fdh.distribution_id distribution_id,
fdh.units_assigned units_assigned,
nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
fdp.period_counter period_counter_created,
Null period_counter_closed ,
fdp.fiscal_year fiscal_year,'Y' Active_flag
FROM fa_distribution_history fdh,
fa_deprn_periods fdp
WHERE fdh.book_type_code = fp_book_type_code
AND fdh.asset_id = fp_asset_id
AND fdh.transaction_header_id_out IS NULL
AND fdp.book_type_code = fp_book_type_code
AND fdh.date_effective BETWEEN fdp.period_open_date AND nvl(fdp.period_close_date,sysdate)
UNION ALL
SELECT fdh.distribution_id distribution_id,
fdh.units_assigned units_assigned,
nvl(fdh.units_assigned,0) + nvl(fdh.transaction_units,0) units_active,
fdp2.period_counter period_counter_created,
fdp3.period_counter period_counter_closed,
fdp2.fiscal_year fiscal_year,'N' Active_flag
FROM fa_distribution_history fdh,
fa_deprn_periods fdp1,
fa_deprn_periods fdp2,
fa_deprn_periods fdp3
WHERE fdh.book_type_code = fp_book_type_code
AND fdh.asset_id = fp_asset_id
AND fdh.transaction_header_id_out IS Not NULL
AND fdp1.book_type_code = fdh.book_type_code
AND fdp1.period_counter = (select min(period_counter)
from fa_deprn_periods fdep
where book_type_code = fp_book_type_code
and fiscal_year=cp_fiscal_year)
AND fdp2.book_type_code = fdh.book_type_code
AND fdh.date_effective between fdp2.period_open_date and nvl(fdp2.period_close_date,fdh.date_effective)
AND fdp3.book_type_code = fdh.book_type_code
AND fdh.date_ineffective between fdp3.period_open_date and nvl(fdp3.period_close_date,fdh.date_ineffective);
SELECT fb.date_placed_in_service,
fb.period_counter_fully_reserved,
fb.life_in_months,
fb.depreciate_flag
FROM fa_books fb
WHERE fb.book_type_code = fp_book_type_code
AND fb.asset_id = fp_asset_id
AND fb.transaction_header_id_out IS NULL;
SELECT min(fds.period_counter)
FROM fa_deprn_summary fds
WHERE fds.book_type_code = fp_book_type_code
AND fds.asset_id = fp_asset_id
AND fds.deprn_source_code = 'DEPRN';
SELECT ct.number_per_fiscal_year
FROM fa_calendar_types ct, fa_book_controls bc
WHERE ct.calendar_type = bc.deprn_calendar
AND bc.book_type_code = fp_book_type_code;
l_prorate_dists_tab.delete;
SELECT segment_num, application_column_name
FROM fnd_id_flex_segments
WHERE application_id = 140
AND id_flex_code = 'CAT#'
AND enabled_flag = 'Y'
AND id_flex_num = x_chart_of_accounts_id
ORDER BY segment_num;
SELECT rowid,
adjustment_id,
book_type_code,
code_combination_id,
adjustment_type,
asset_id,
distribution_id,
period_counter
FROM igi_iac_adjustments
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter
AND adjustment_type IN ('COST','RESERVE','EXPENSE')
AND adjustment_id = c_adjustment_id for update;
SELECT adjustment_id,
transaction_header_id,
transaction_type_code
FROM igi_iac_transaction_headers
WHERE book_type_code = p_book_type_code
AND period_counter = p_period_counter
AND transaction_type_code in ('REVALUATION');
SELECT nvl(ASSET_COST_ACCOUNT_CCID, -1),
nvl(DEPRN_EXPENSE_ACCOUNT_CCID, -1),
nvl(DEPRN_RESERVE_ACCOUNT_CCID, -1),
bc.accounting_flex_structure
FROM FA_DISTRIBUTION_ACCOUNTS da,
FA_BOOK_CONTROLS bc
WHERE bc.book_type_code = p_book_type_code
AND da.book_type_code = bc.book_type_code
AND da.distribution_id = c_distribution_id;
SELECT code_combination_id
FROM fa_adjustments
WHERE book_type_code = p_book_type_code
AND asset_id = c_asset_id
AND distribution_id = c_distribution_id
AND adjustment_type = c_adjustment_type;
SELECT a.category_id
INTO l_category_id
FROM fa_asset_history a
,fa_distribution_history d
WHERE d.distribution_id = l_dist_id(l_loop_count)
AND a.asset_id = d.asset_id
AND d.date_effective >= a.date_effective
AND d.date_effective < nvl(a.date_ineffective,sysdate);
SELECT asset_cost_acct, deprn_expense_acct, deprn_reserve_acct,
asset_cost_account_ccid, reserve_account_ccid
INTO l_asset_cost_acct, l_dep_exp_acct, l_dep_res_acct,
l_asset_cost_account_ccid ,l_reserve_account_ccid
FROM fa_category_books
WHERE book_type_code = p_book_type_code
AND category_id = l_category_id;
SELECT accounting_flex_structure, flexbuilder_defaults_ccid
into l_flex_num, l_default_ccid
FROM fa_book_controls
WHERE book_type_code = p_book_type_code ;
Select calendar_period_close_date
into l_validation_date
From fa_deprn_periods
where book_type_code = p_book_type_code
and period_counter = p_period_counter;
UPDATE igi_iac_adjustments
SET code_combination_id= l_account_ccid
WHERE rowid=l_rowid;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Updated the adjusment with correct ccid' );