[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select deprn_method_code,
life_in_months
into l_deprn_method_code,
l_life_in_months
from fa_books
where book_type_code = px_asset_hdr_rec.book_type_code
and asset_id = px_asset_hdr_rec.asset_id
and transaction_header_id_out is null;
select fa_transaction_headers_s.nextval
into px_trans_rec.transaction_header_id
from dual;
l_asset_fin_mrc_tbl_adj.delete;
/*select fa_transaction_headers_s.nextval
into l_trans_rec.transaction_header_id
from dual; */
select dest_amortization_start_date
from fa_trx_references
where member_asset_id = p_asset_id
and book_type_code = p_book_type_code
order by dest_amortization_start_date desc;
select transaction_date_entered
from fa_transaction_headers
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and transaction_type_code in
('PARTIAL RETIREMENT','REINSTATEMENT')
order by transaction_date_entered desc;
/* select NVL(bk_extnd.prior_deprn_method,bk_old.deprn_method_code) deprn_method_code,
NVL(bk_extnd.prior_life_in_months,bk_old.life_in_months) life_in_months,
--bk_old.salvage_value,
--bk_old.period_counter_fully_reserved,
--bk_old.period_counter_life_complete,
NVL(bk_extnd.prior_basic_rate, bk_old.basic_rate) basic_rate,
NVL(bk_extnd.prior_adjusted_rate, bk_old.adjusted_rate) adjusted_rate,
bk_old.allowed_deprn_limit,
bk_old.deprn_limit_type,
bk_old.allowed_deprn_limit_amount
from fa_books bk_old, fa_books bk_extnd
where bk_old.book_type_code = p_book_type_code and
bk_old.asset_id = p_asset_id and
bk_old.extended_depreciation_period is null and
bk_extnd.book_type_code = p_book_type_code and
bk_extnd.asset_id = p_asset_id and
bk_extnd.extended_depreciation_period is not null and
bk_extnd.transaction_header_id_in = bk_old.transaction_header_id_out
order by bk_extnd.transaction_header_id_in desc;*/
SELECT FB.deprn_method_code
, FB.life_in_months
, FB.basic_rate
, FB.adjusted_rate
, FB.allowed_deprn_limit
, FB.deprn_limit_type
, FB.allowed_deprn_limit_amount
FROM FA_BOOKS FB
, FA_TRANSACTION_HEADERS FT
WHERE FB.BOOK_TYPE_CODE = p_book_type_code
AND FB.ASSET_ID = p_asset_id
AND FB.BOOK_TYPE_CODE = FT.BOOK_TYPE_CODE
AND FB.ASSET_ID = FT.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_OUT = FT.TRANSACTION_HEADER_ID
AND FT.TRANSACTION_KEY = 'ES'
UNION
SELECT FB.prior_deprn_method
, FB.prior_life_in_months
, FB.prior_basic_rate
, FB.prior_adjusted_rate
, FB.prior_deprn_limit
, FB.prior_deprn_limit_type
, FB.prior_deprn_limit_amount
FROM FA_BOOKS FB
WHERE FB.BOOK_TYPE_CODE = p_book_type_code
AND FB.ASSET_ID = p_asset_id
AND FB.date_ineffective IS NULL;
Select 1
from
fa_transaction_headers fath
, fa_deprn_periods fadp
, fa_retirements faret
, fa_conventions facon
where fath.book_type_code = p_book_type_code
and fath.asset_id = p_asset_id
and fath.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
and fath.book_type_code = fadp.book_type_code
and fath.date_effective between fadp.period_open_date and nvl(fadp.period_close_date,sysdate)
and fadp.period_close_date is null
and fath.transaction_header_id = faret.TRANSACTION_HEADER_ID_IN
and faret.TRANSACTION_HEADER_ID_OUT is null
and faret.retirement_prorate_convention = facon.prorate_convention_code
and faret.date_retired between facon.start_date and facon.end_date
and facon.prorate_date > fadp.CALENDAR_PERIOD_CLOSE_DATE
;
select greatest(calendar_period_open_date,
least(sysdate, calendar_period_close_date))
from fa_deprn_periods
where book_type_code = c_book_type_code
and period_close_date is null;
select TRX_REFERENCE_ID,
TRANSACTION_TYPE,
SRC_TRANSACTION_SUBTYPE,
DEST_TRANSACTION_SUBTYPE,
BOOK_TYPE_CODE,
SRC_ASSET_ID,
SRC_TRANSACTION_HEADER_ID,
DEST_ASSET_ID,
DEST_TRANSACTION_HEADER_ID,
MEMBER_ASSET_ID,
MEMBER_TRANSACTION_HEADER_ID,
SRC_AMORTIZATION_START_DATE,
DEST_AMORTIZATION_START_DATE,
RESERVE_TRANSFER_AMOUNT,
SRC_EXPENSE_AMOUNT,
DEST_EXPENSE_AMOUNT,
SRC_EOFY_RESERVE,
DEST_EOFY_RESERVE
from fa_trx_references
where TRX_REFERENCE_ID = px_trans_rec.trx_reference_id;
select fa_transaction_headers_s.nextval
into px_trans_rec.transaction_header_id
from dual;
px_trans_rec.who_info.last_update_date := sysdate;
select cp.start_date,
cp.end_date
into l_extend_calendar_pod,
l_extend_calendar_pcd
from fa_book_controls bc,
fa_fiscal_year fy,
fa_calendar_types ct,
fa_calendar_periods cp
where bc.book_type_code = px_asset_hdr_rec.book_type_code and
bc.deprn_calendar = ct.calendar_type and
cp.calendar_type = ct.calendar_type and
bc.fiscal_year_name = ct.fiscal_year_name and
fy.fiscal_year_name = ct.fiscal_year_name and
cp.period_num = 1 and
fy.fiscal_year = round (
(l_asset_fin_rec_adj.extended_depreciation_period -cp.period_num)/ct.number_per_fiscal_year) --bug 7719717
and cp.start_date = fy.start_date;
delete from fa_mc_adjustments
where asset_id = px_asset_hdr_rec.asset_id
and book_type_code = px_asset_hdr_rec.book_type_code
and source_type_code = 'DEPRECIATION'
and adjustment_type = 'EXPENSE'
and set_of_books_id = l_asset_hdr_rec.set_of_books_id;
delete from fa_adjustments
where asset_id = px_asset_hdr_rec.asset_id
and book_type_code = px_asset_hdr_rec.book_type_code
and source_type_code = 'DEPRECIATION'
and adjustment_type = 'EXPENSE';
adj_row.selection_mode := fa_std_types.FA_AJ_ACTIVE;
adj_row.last_update_date := sysdate;
select sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount))
into l_expense_amount
from fa_mc_adjustments
where asset_id = px_asset_hdr_rec.asset_id
and book_type_code = px_asset_hdr_rec.book_type_code
and source_type_code = 'DEPRECIATION'
and adjustment_type = 'EXPENSE'
and set_of_books_id = l_asset_hdr_rec.set_of_books_id;
select sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount))
into l_expense_amount
from fa_adjustments
where asset_id = px_asset_hdr_rec.asset_id
and book_type_code = px_asset_hdr_rec.book_type_code
and source_type_code = 'DEPRECIATION'
and adjustment_type = 'EXPENSE';
select fa_trx_references_s.nextval
into px_trans_rec.trx_reference_id
from dual;
MC_FA_UTILITIES_PKG.insert_books_rates
(p_set_of_books_id => l_asset_hdr_rec.set_of_books_id,
p_asset_id => l_asset_hdr_rec.asset_id,
p_book_type_code => l_asset_hdr_rec.book_type_code,
p_transaction_header_id => px_trans_rec.transaction_header_id,
p_invoice_transaction_id => px_inv_trans_rec.invoice_transaction_id,
p_exchange_date => px_trans_rec.transaction_date_entered,
p_cost => l_asset_fin_rec_adj_init.cost,
p_exchange_rate => l_exchange_rate,
p_avg_exchange_rate => l_avg_rate,
p_last_updated_by => px_trans_rec.who_info.last_updated_by,
p_last_update_date => px_trans_rec.who_info.last_update_date,
p_last_update_login => px_trans_rec.who_info.last_update_login,
p_complete => 'Y',
p_trigger => 'adj api',
p_currency_code => l_asset_hdr_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
select fa_transaction_headers_s.nextval
into l_src_trans_rec.transaction_header_id
from dual;
UPDATE FA_DEPRN_OVERRIDE
SET status = 'POSTED'
WHERE used_by = 'ADJUSTMENT'
AND status = 'SELECTED';