[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT period_name
FROM fa_deprn_periods
WHERE Book_Type_Code = cp_bookType
AND period_counter = TO_NUMBER(cp_period);
SELECT
sob.name,
sc.Company_Name,
sc.Category_Flex_Structure,
sc.Location_Flex_Structure,
sc.asset_key_flex_structure,
bc.Accounting_Flex_Structure,
ct.fiscal_year_name,
sob.Currency_Code
FROM
fa_system_controls sc,
fa_book_controls bc,
gl_sets_of_books sob,
fa_calendar_types ct
WHERE
bc.Book_Type_Code = cp_bookType
AND sob.Set_Of_Books_ID = BC.Set_Of_Books_ID
AND bc.deprn_calendar = ct.calendar_type;
SELECT DISTINCT a.asset_id,
a.book_type_code,
a.date_placed_in_service,
a.life_in_months,
nvl(a.cost,0) cost,
nvl(a.adjusted_cost,0) adjusted_cost,
nvl(a.original_cost,0) original_cost,
nvl(a.salvage_value,0) salvage_value,
nvl(a.adjusted_recoverable_cost, 0) adjusted_recoverable_cost,
nvl(a.recoverable_cost,0) recoverable_cost,
a.deprn_start_date,
a.cost_change_flag,
a.rate_adjustment_factor,
a.depreciate_flag,
a.fully_rsvd_revals_counter,
a.period_counter_fully_reserved,
a.period_counter_fully_retired ,
ad.asset_number,
ad.description,
b.deprn_reserve,
b.ytd_deprn
FROM fa_books a,
fa_additions ad,
fa_deprn_summary b
WHERE a.book_type_code = p_book
AND ad.asset_id = a.asset_id
AND a.date_ineffective IS NULL
AND b.asset_id = a.asset_id
AND b.book_type_code = a.book_type_code
AND b.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE book_type_code = a.book_type_code
AND asset_id = a.asset_id);
select a.asset_id ,
a.book_type_code,
a.date_placed_in_service,
a.life_in_months,
nvl(a.cost,0) cost,
nvl(a.adjusted_cost,0) adjusted_cost,
nvl(a.original_cost,0) original_cost,
nvl(a.salvage_value,0) salvage_value,
nvl(a.adjusted_recoverable_cost, 0) adjusted_recoverable_cost,
nvl(a.recoverable_cost,0) recoverable_cost,
a.deprn_start_date,
a.cost_change_flag,
a.rate_adjustment_factor,
a.depreciate_flag,
a.fully_rsvd_revals_counter,
a.period_counter_fully_reserved,
a.period_counter_fully_retired,
ad.asset_number,
ad.description,
ad.asset_category_id,
b.period_counter,
b.deprn_reserve,
b.ytd_deprn
from fa_books a ,
fa_deprn_summary b,
fa_additions ad
Where a.book_type_code = p_book
and a.asset_id = p_asset_id
and ad.asset_id = a.asset_id
and a.book_type_code = b.book_type_code
and b.asset_id = a.asset_id
and b.period_counter = (select max(period_counter)
from fa_deprn_summary
where book_type_code =p_book
and asset_id =p_asset_id)
and a.date_ineffective is null;
Select book_type_code
from igi_mhc_book_controls
where book_type_code in (Select book_type_code
from fa_book_controls
where book_class ='TAX'
and distribution_source_book =p_book)
Order by book_type_code;
Select distinct asset_id,
nvl(original_cost,0) original_cost,
nvl(new_asset_cost,0) new_asset_cost,
nvl(new_salvage_value,0) new_salvage_value,
original_life,
current_life,
nvl(old_accum_deprn,0) old_accum_deprn,
nvl(new_accum_deprn,0) new_accum_deprn,
nvl(old_reval_reserve,0) old_reval_reserve,
nvl(new_reval_reserve,0) new_reval_reserve,
nvl(new_curr_yr_expense,0) new_curr_yr_expense,
nvl(new_backlog_deprn,0) new_backlog_deprn
From igi_mhc_reval_summary_pl_v
Where book_type_code = cp_tax_book
and asset_id = p_asset_id
and period_counter = (select Max(period_counter) from igi_mhc_reval_summary_pl_v
Where book_type_code = cp_tax_book
and asset_id = p_asset_id)
and reval_mode = 'INDEXED'
and active_flag= 'Y'
and Run_mode = 'L';
Select category_id
From fa_category_books
Where book_type_code = p_book;
SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code = cp_book_type_code;
SELECT number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = cp_calendar_type;
SELECT period_num_for_catchup
FROM igi_iac_book_controls
WHERE book_type_code = cp_book_type_code;
delete from IGI_IMP_IAC_EXCEP_REP_ITF ;
INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD ,
FISCAL_YEAR_NAME ,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX )
VALUES (
p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'P',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months ,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost ,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value );
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD ,
FISCAL_YEAR_NAME ,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX )
values (
p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'K',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months ,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost ,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value );
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
values
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'M',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value );
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
values
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'L',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code, -- mh, l_concat_cat,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months,
l_concat_cat, -- mh, l_concat_loc,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value );
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
values
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'D',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value);
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
values
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'S',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value );
INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
VALUES
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'L',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value);
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
values
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
C_Tax_Book_Info_Rec.book_type_code,
l_period_name,
l_fiscal_year_name,
l_message,
'L',
C_Mhca_Book_Info_Rec.asset_number,
C_Mhca_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
C_Mhca_Book_Info_Rec.date_placed_in_service,
C_Corp_Book_Info_Rec.life_in_months,
C_Mhca_Book_Info_Rec.life_in_months,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
C_Mhca_Book_Info_Rec.original_cost,
C_Corp_Book_Info_Rec.salvage_value,
C_Mhca_Book_Info_Rec.salvage_value);
INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD ,
FISCAL_YEAR_NAME ,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX )
VALUES (
p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
NULL,
l_period_name,
l_fiscal_year_name,
l_message,
'P',
C_Corp_Book_Info_Rec.asset_number,
C_Corp_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
NULL,
C_Corp_Book_Info_Rec.life_in_months,
NULL,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
NULL,
C_Corp_Book_Info_Rec.salvage_value,
NULL);
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD ,
FISCAL_YEAR_NAME ,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX )
values
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
NULL,
l_period_name,
l_fiscal_year_name,
l_message,
'K',
C_Corp_Book_Info_Rec.asset_number,
C_Corp_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
NULL,
C_Corp_Book_Info_Rec.life_in_months ,
NULL ,
l_concat_cat, -- l_concat_loc,
C_Corp_Book_Info_Rec.original_cost,
NULL ,
C_Corp_Book_Info_Rec.salvage_value,
NULL);
insert into IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
values
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
null,
l_period_name,
l_fiscal_year_name,
l_message,
'M',
C_Corp_Book_Info_Rec.asset_number,
C_Corp_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
NULL,
C_Corp_Book_Info_Rec.life_in_months,
NULL,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
NULL,
C_Corp_Book_Info_Rec.salvage_value,
NULL);
INSERT INTO IGI_IMP_IAC_EXCEP_REP_ITF (
request_id,
sob_book,
organisation_name,
corp_book,
tax_book,
PERIOD,
FISCAL_YEAR_NAME,
warning_message ,
warning_message_code ,
ASSET_NUMBER,
ASSET_DESCRIPTION ,
FUNCTIONAL_CURRENCY_CODE,
DPIS_CORP ,
DPIS_TAX ,
ASSET_LIFE_CORP ,
ASSET_LIFE_TAX ,
CONCAT_CATEGORY ,
HIST_COST_CORP ,
HIST_COST_TAX ,
SALVAGE_VALUE_CORP ,
SALVAGE_VALUE_TAX)
VALUES
(p_request_id,
l_sob_name,
l_company_name,
C_Corp_Book_Info_Rec.book_type_code,
null,
l_period_name,
l_fiscal_year_name,
l_message,
'L',
C_Corp_Book_Info_Rec.asset_number,
C_Corp_Book_Info_Rec.description,
l_currency_code,
C_Corp_Book_Info_Rec.date_placed_in_service,
null,
C_Corp_Book_Info_Rec.life_in_months,
null,
l_concat_cat,
C_Corp_Book_Info_Rec.original_cost,
null,
C_Corp_Book_Info_Rec.salvage_value,
null);