[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fy.fiscal_year
into l_first_fiscal_year
from fa_fiscal_year fy,
fa_mc_book_controls mbc,
fa_book_controls bc,
fa_mc_books bks
where bc.book_type_code = p_Book_Type_Code
and mbc.book_type_code = p_Book_Type_Code
and bc.fiscal_year_name = fy.fiscal_year_name
and mbc.set_of_books_id = p_set_of_books_id
and bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.transaction_header_id_out is null
and bks.set_of_books_id = p_set_of_books_id
and bks.date_placed_in_service between
fy.start_date and fy.end_date;
select dp.period_counter
into l_first_period_counter
from fa_mc_deprn_periods dp,
fa_mc_books bks
where dp.book_type_code = p_Book_Type_Code
and dp.set_of_books_id = p_set_of_books_id
and bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.transaction_header_id_out is null
and bks.set_of_books_id = p_set_of_books_id
and bks.date_placed_in_service between
dp.calendar_period_open_date and dp.calendar_period_close_date;
select period_counter
into l_open_period_counter
from fa_mc_deprn_periods
where book_type_code = p_Book_Type_Code
and period_close_date is null
and set_of_books_id = p_set_of_books_id;
select fy.fiscal_year
into l_first_fiscal_year
from fa_fiscal_year fy,
fa_book_controls bc,
fa_books bks
where bc.book_type_code = p_Book_Type_Code
and bc.fiscal_year_name = fy.fiscal_year_name
and bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.transaction_header_id_out is null
and bks.date_placed_in_service between
fy.start_date and fy.end_date;
select dp.period_counter
into l_first_period_counter
from fa_deprn_periods dp,
fa_books bks
where dp.book_type_code = p_Book_Type_Code
and bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.transaction_header_id_out is null
and bks.date_placed_in_service between
dp.calendar_period_open_date and dp.calendar_period_close_date;
select period_counter
into l_open_period_counter
from fa_deprn_periods
where book_type_code = p_Book_Type_Code
and period_close_date is null;
select adjusted_cost
into l_old_adjusted_cost
from fa_mc_books
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and transaction_header_id_out is null
and set_of_books_id = p_set_of_books_id;
select adjusted_cost
into l_old_adjusted_cost
from fa_books
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and transaction_header_id_out is null;
select transaction_type_code,
transaction_header_id
into l_transaction_type_code,
l_transaction_header_id
from fa_transaction_headers
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_ID
and transaction_type_code in ('ADJUSTMENT', 'PARTIAL RETIREMENT')
and transaction_header_id =
(
select max(transaction_header_id)
from fa_transaction_headers
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_ID
and transaction_type_code in ('ADJUSTMENT', 'PARTIAL RETIREMENT')
);
select deprn_reserve
into l_first_year_reserve
from fa_mc_deprn_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = l_first_year_period_counter
and set_of_books_id = p_set_of_books_id;
select deprn_reserve
into l_prev_year_reserve
from fa_mc_deprn_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = l_prev_year_period_counter
and set_of_books_id = p_set_of_books_id;
select polish_deprn_basis,
polish_adj_rec_cost
into l_prev_year_adjusted_cost,
l_prev_year_adj_rec_cost
from fa_mc_books_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = l_prev_year_period_counter
and set_of_books_id = p_set_of_books_id;
select min(period_counter)
into l_min_period_counter
from fa_mc_deprn_periods
where book_type_code = p_Book_Type_Code
and set_of_books_id = p_set_of_books_id;
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into l_prev_year_adjusted_cost,
l_prev_year_adj_rec_cost
from fa_mc_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.set_of_books_id = p_set_of_books_id
and bks.rowid =
(
select max(bks1.rowid)
from fa_mc_books bks1,
fa_mc_deprn_periods dp
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.book_type_code = dp.book_type_code
and bks1.set_of_books_id = p_set_of_books_id
and dp.period_counter = l_prev_year_period_counter
and dp.set_of_books_id = p_set_of_books_id
and bks1.date_effective <=
nvl(dp.period_close_date, sysdate)
);
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into l_prev_year_adjusted_cost,
l_prev_year_adj_rec_cost
from fa_mc_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.set_of_books_id = p_set_of_books_id
and bks.rowid =
(
select min(bks1.rowid)
from fa_mc_books bks1
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.set_of_books_id = p_set_of_books_id
);
select deprn_reserve
into l_first_year_reserve
from fa_deprn_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = l_first_year_period_counter;
select deprn_reserve
into l_prev_year_reserve
from fa_deprn_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = l_prev_year_period_counter;
select polish_deprn_basis,
polish_adj_rec_cost
into l_prev_year_adjusted_cost,
l_prev_year_adj_rec_cost
from fa_books_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = l_prev_year_period_counter;
select min(period_counter)
into l_min_period_counter
from fa_deprn_periods
where book_type_code = p_Book_Type_Code;
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into l_prev_year_adjusted_cost,
l_prev_year_adj_rec_cost
from fa_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.rowid =
(
select max(bks1.rowid)
from fa_books bks1,
fa_deprn_periods dp
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.book_type_code = dp.book_type_code
and dp.period_counter = l_prev_year_period_counter
and bks1.date_effective <=
nvl(dp.period_close_date, sysdate)
);
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into l_prev_year_adjusted_cost,
l_prev_year_adj_rec_cost
from fa_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.rowid =
(
select min(bks1.rowid)
from fa_books bks1
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
);
select adjusted_cost
into l_prev_year_adjusted_cost
from fa_mc_books
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and transaction_header_id_out is null
and set_of_books_id = p_set_of_books_id;
select adjusted_cost
into l_prev_year_adjusted_cost
from fa_books
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and transaction_header_id_out is null;
((l_transaction_type_code = 'ADJUSTMENT') and (l_pos_neg_adjustment is null)) or /* Brahma 6989831. This perticular statement is added for non cost adjustments like Dereciation checkbox unselected and selected again */
((l_transaction_type_code = 'PARTIAL RETIREMENT') and
(l_previous_year_retirement))) then
-- Determine the basis depending on the switch
if (l_switch_code in ('30', 'DM')) then
-- Set the basis to be the fully reservable cost
l_adjusted_cost := nvl(l_adjusted_recoverable_cost, 0);
select period_counter - 1
into x_bef_trxn_period_counter
from fa_mc_deprn_periods
where book_type_code = p_Book_Type_Code
and set_of_books_id = p_set_of_books_id
and l_amortization_start_date between
calendar_period_open_date and calendar_period_close_date;
select period_counter - 1
into x_bef_trxn_period_counter
from fa_deprn_periods
where book_type_code = p_Book_Type_Code
and l_amortization_start_date between
calendar_period_open_date and calendar_period_close_date;
select adjustment_amount,
debit_credit_flag
into x_adjustment_amount,
l_debit_credit_flag
from fa_mc_adjustments
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and transaction_header_id = p_transaction_header_id
and set_of_books_id = p_set_of_books_id
and source_type_code = 'ADJUSTMENT'
and adjustment_type = 'COST';
select adjustment_amount,
debit_credit_flag
into x_adjustment_amount,
l_debit_credit_flag
from fa_adjustments
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and transaction_header_id = p_transaction_header_id
and source_type_code = 'ADJUSTMENT'
and adjustment_type = 'COST';
select dp.period_counter - 1
into x_bef_trxn_period_counter
from fa_mc_deprn_periods dp,
fa_transaction_headers th
where th.transaction_header_id = p_transaction_header_id
and dp.book_type_code = p_Book_Type_Code
and dp.set_of_books_id = p_set_of_books_id
and th.date_effective between
dp.period_open_date and
nvl(dp.period_close_date, sysdate);
select dp.period_counter - 1
into x_bef_trxn_period_counter
from fa_deprn_periods dp,
fa_transaction_headers th
where th.transaction_header_id = p_transaction_header_id
and dp.book_type_code = p_Book_Type_Code
and th.date_effective between
dp.period_open_date and
nvl(dp.period_close_date, sysdate);
select dp.period_counter - 1
into x_bef_trxn_period_counter
from fa_mc_deprn_periods dp,
fa_transaction_headers th
where th.transaction_header_id = p_transaction_header_id
and dp.book_type_code = p_Book_Type_Code
and dp.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered between
dp.calendar_period_open_date and
dp.calendar_period_close_date;
select dp.period_counter - 1
into x_bef_trxn_period_counter
from fa_deprn_periods dp,
fa_transaction_headers th
where th.transaction_header_id = p_transaction_header_id
and dp.book_type_code = p_Book_Type_Code
and th.transaction_date_entered between
dp.calendar_period_open_date and
dp.calendar_period_close_date;
select polish_deprn_basis,
polish_adj_rec_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_mc_books_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = x_bef_trxn_period_counter
and set_of_books_id = p_set_of_books_id;
select min(period_counter)
into l_min_period_counter
from fa_mc_deprn_periods
where book_type_code = p_Book_Type_Code
and set_of_books_id = p_set_of_books_id;
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_mc_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.set_of_books_id = p_set_of_books_id
and bks.rowid =
(
select max(bks1.rowid)
from fa_mc_books bks1,
fa_mc_deprn_periods dp
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.book_type_code = dp.book_type_code
and bks1.set_of_books_id = p_set_of_books_id
and dp.period_counter = x_bef_trxn_period_counter
and dp.set_of_books_id = p_set_of_books_id
and bks1.date_effective <=
nvl(dp.period_close_date, sysdate)
);
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_mc_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.set_of_books_id = p_set_of_books_id
and bks.rowid =
(
select min(bks1.rowid)
from fa_mc_books bks1
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks.set_of_books_id = p_set_of_books_id
);
select deprn_reserve
into x_prev_reserve
from fa_mc_deprn_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = x_bef_trxn_period_counter
and set_of_books_id = p_set_of_books_id;
select polish_deprn_basis,
polish_adj_rec_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_books_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = x_bef_trxn_period_counter;
select min(period_counter)
into l_min_period_counter
from fa_deprn_periods
where book_type_code = p_Book_Type_Code;
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.rowid =
(
select max(bks1.rowid)
from fa_books bks1,
fa_deprn_periods dp
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.book_type_code = dp.book_type_code
and dp.period_counter = x_bef_trxn_period_counter
and bks1.date_effective <=
nvl(dp.period_close_date, sysdate)
);
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.rowid =
(
select min(bks1.rowid)
from fa_books bks1
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
);
select deprn_reserve
into x_prev_reserve
from fa_deprn_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = x_bef_trxn_period_counter;
select adjustment_amount,
debit_credit_flag
into x_retirement_amount,
l_debit_credit_flag
from fa_mc_adjustments
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and transaction_header_id = p_transaction_header_id
and source_type_code = 'RETIREMENT'
and adjustment_type = 'COST'
and set_of_books_id = p_set_of_books_id;
select dp.period_counter - 1
into x_bef_trxn_period_counter
from fa_mc_deprn_periods dp,
fa_transaction_headers th
where th.transaction_header_id = p_transaction_header_id
and dp.book_type_code = p_Book_Type_Code
and dp.set_of_books_id = p_set_of_books_id
and th.transaction_date_entered between
dp.calendar_period_open_date and
dp.calendar_period_close_date;
select adjustment_amount,
debit_credit_flag
into x_retirement_amount,
l_debit_credit_flag
from fa_adjustments
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and transaction_header_id = p_transaction_header_id
and source_type_code = 'RETIREMENT'
and adjustment_type = 'COST';
select dp.period_counter - 1
into x_bef_trxn_period_counter
from fa_deprn_periods dp,
fa_transaction_headers th
where th.transaction_header_id = p_transaction_header_id
and dp.book_type_code = p_Book_Type_Code
and th.transaction_date_entered between
dp.calendar_period_open_date and
dp.calendar_period_close_date;
select polish_deprn_basis,
polish_adj_rec_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_mc_books_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = x_bef_trxn_period_counter
and set_of_books_id = p_set_of_books_id;
select min(period_counter)
into l_min_period_counter
from fa_mc_deprn_periods
where book_type_code = p_Book_Type_Code
and set_of_books_id = p_set_of_books_id;
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_mc_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.set_of_books_id = p_set_of_books_id
and bks.rowid =
(
select max(bks1.rowid)
from fa_mc_books bks1,
fa_mc_deprn_periods dp
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.set_of_books_id = p_set_of_books_id
and bks1.book_type_code = dp.book_type_code
and dp.period_counter = x_bef_trxn_period_counter
and dp.set_of_books_id = p_set_of_books_id
and bks1.date_effective <=
nvl(dp.period_close_date, sysdate)
);
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_mc_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.set_of_books_id = p_set_of_books_id
and bks.rowid =
(
select min(bks1.rowid)
from fa_mc_books bks1
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.set_of_books_id = p_set_of_books_id
);
select polish_deprn_basis,
polish_adj_rec_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_books_summary
where book_type_code = p_Book_Type_Code
and asset_id = p_Asset_Id
and period_counter = x_bef_trxn_period_counter;
select min(period_counter)
into l_min_period_counter
from fa_deprn_periods
where book_type_code = p_Book_Type_Code;
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.rowid =
(
select max(bks1.rowid)
from fa_books bks1,
fa_deprn_periods dp
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
and bks1.book_type_code = dp.book_type_code
and dp.period_counter = x_bef_trxn_period_counter
and bks1.date_effective <=
nvl(dp.period_close_date, sysdate)
);
select bks.adjusted_cost,
bks.adjusted_recoverable_cost
into x_prev_basis,
x_prev_adj_rec_cost
from fa_books bks
where bks.book_type_code = p_Book_Type_Code
and bks.asset_id = p_Asset_Id
and bks.rowid =
(
select min(bks1.rowid)
from fa_books bks1
where bks1.book_type_code = p_Book_Type_Code
and bks1.asset_id = p_Asset_Id
);
select nvl(switch_code, 'XX')
into x_switch_code
from fa_mc_books_summary
where book_type_code = p_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter
and set_of_books_id = p_set_of_books_id;
select nvl(switch_code, 'XX')
into x_switch_code
from fa_books_summary
where book_type_code = p_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter;
select nvl(switch_code, 'XX')
into l_prev_switch_code
from fa_mc_books_summary
where book_type_code = p_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter - 1
and set_of_books_id = p_set_of_books_id;
select nvl(switch_code, 'XX')
into l_prev_switch_code
from fa_books_summary
where book_type_code = p_book_type_code
and asset_id = p_asset_id
and period_counter = p_period_counter - 1;