[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT book_type_code,
request_id,
functional_currency_code,
set_of_books_id,
fiscal_year_name,
period,
organization_name,
major_category,
minor_category,
concat_category,
category_id,
balancing_segment,
cost_center,
asset_id,
asset_number,
asset_description,
parent_no,
curr_reval_factor,
cumm_reval_factor,
asset_tag,
serial_no,
dpis ,
life_months,
stl_rate,
depreciation_method,
conc_asset_key,
conc_location,
sum(REVAL_COST),
sum(NET_REVAL_RESERVE),
sum(REVAL_RES_BLOG),
sum(REVAL_YTD_DEPRN),
sum(REVAL_DEPRN_RESERVE),
sum(OPER_EXP_BACKLOG),
sum(GENERAL_FUND),
sum(HIST_COST),
sum(HIST_YTD_DEPRN),
sum(HIST_ACC_DEPRN),
sum(OPER_ACCT)
FROM igi_imp_iac_itf
WHERE request_id = cp_request_id
AND book_type_code = cp_book_type_code
GROUP BY book_type_code,
request_id,
functional_currency_code,
set_of_books_id,
fiscal_year_name,
period,
organization_name,
major_category,
minor_category,
concat_category,
category_id,
balancing_segment,
cost_center,
asset_id,
asset_number,
asset_description,
parent_no,
curr_reval_factor,
cumm_reval_factor,
asset_tag,
serial_no,
dpis ,
life_months,
stl_rate,
depreciation_method,
conc_asset_key,
conc_location;
DELETE FROM igi_imp_iac_itf
WHERE book_type_code = p_book_type_code
AND request_id = p_request_id;
INSERT INTO IGI_IMP_IAC_ITF( distribution_id ,
request_id ,
set_of_books_id ,
asset_id,
category_id,
functional_currency_code ,
book_type_code ,
fiscal_year_name ,
period ,
cost_center ,
asset_number ,
asset_description ,
major_category ,
minor_category ,
concat_category ,
reval_cost ,
net_reval_reserve ,
reval_res_blog ,
reval_ytd_deprn ,
reval_deprn_reserve ,
oper_acct ,
oper_exp_backlog ,
general_fund ,
parent_no ,
curr_reval_factor ,
cumm_reval_factor,
asset_tag ,
balancing_segment ,
serial_no ,
dpis ,
life_months ,
stl_rate ,
depreciation_method ,
conc_asset_key ,
conc_location ,
cost_acct ,
iac_reval_resv_acct ,
deprn_res_acct ,
deprn_backlog_acct ,
gen_fund_acct ,
deprn_exp_acct ,
oper_exp_acct ,
hist_cost ,
hist_ytd_deprn ,
hist_acc_deprn,
organization_name,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
VALUES
( null ,
l_request_id(j),
l_set_of_books_id(j) ,
l_asset_id(j),
l_category_id(j),
l_functional_currency_code(j) ,
l_book_type_code(j) ,
l_fiscal_year_name(j),
l_period(j) ,
l_cost_center(j) ,
l_asset_number(j),
l_asset_description(j) ,
l_major_category(j),
l_minor_category(j),
l_concat_category(j),
l_reval_cost(j),
l_net_reval_reserve(j) ,
l_reval_res_blog(j),
l_reval_ytd_deprn(j) ,
l_reval_deprn_reserve(j) ,
l_oper_acct(j),
l_oper_exp_backlog(j),
l_general_fund(j),
l_parent_no(j),
l_curr_reval_factor(j) ,
l_cumm_reval_factor(j) ,
l_asset_tag(j) ,
l_balancing_segment(j) ,
l_serial_no(j) ,
l_dpis(j),
l_life_months(j) ,
NULL ,
l_depreciation_method(j) ,
l_conc_asset_key(j) ,
l_conc_location(j) ,
null,
null,
null,
null,
null,
null ,
null,
l_hist_cost(j),
l_hist_ytd_deprn(j) ,
l_hist_acc_deprn(j),
l_organization_name(j),
l_user_id,
sysdate,
l_login_id,
l_user_id,
sysdate) ;
SELECT distribution_source_book
FROM fa_book_controls
WHERE book_type_code=p_book_type_code;
SELECT YTD_HIST,YTD_MHCA,asset_number,
cost_hist,cost_mhca
FROM igi_imp_iac_interface ii
WHERE
ii.asset_id=p_asset_id AND
ii.book_type_code=p_book_type_code AND
ii.category_id=p_category_id ;
SELECT nvl(ii.Hist_Salvage_Value,0) Hist_Salvage_Value,
nvl(ii.Life_in_Months,0) Life_in_Months,
nvl(ct.Corp_Book,' ') Corp_Book,
nvl(ii.Cost_Hist * (dh.units_Assigned/ad.current_units),0) cost_hist,
nvl(ii.Cost_MHCA * (dh.units_Assigned/ad.current_units) ,0) cost_mhca,
nvl(ii.Deprn_Exp_Hist * (dh.units_Assigned/ad.current_units) ,0) deprn_exp_hist,
nvl(ii.Deprn_Exp_MHCA * (dh.units_Assigned/ad.current_units) ,0 ) deprn_exp_mhca,
nvl(ii.Accum_Deprn_Hist * (dh.units_Assigned/ad.current_units) ,0) Accum_Deprn_Hist,
nvl(ii.Accum_Deprn_MHCA * (dh.units_Assigned/ad.current_units) ,0) Accum_Deprn_MHCA,
nvl(ii.Reval_Reserve_Hist * (dh.units_Assigned/ad.current_units) ,0) Reval_Reserve_Hist,
nvl(ii.Reval_Reserve_MHCA * (dh.units_Assigned/ad.current_units) ,0) Reval_Reserve_MHCA,
nvl(ii.Backlog_Hist *(dh.units_Assigned/ad.current_units) ,0) Backlog_Hist,
nvl(ii.Backlog_MHCA *(dh.units_Assigned/ad.current_units) ,0) Backlog_MHCA,
nvl(ii.General_Fund_HIST * (dh.units_Assigned/ad.current_units) ,0) General_Fund_HIST,
nvl(ii.General_Fund_MHCA * (dh.units_Assigned/ad.current_units) ,0) General_Fund_MHCA,
nvl(ii.General_Fund_Per_Hist * (dh.units_Assigned/ad.current_units) ,0) General_Fund_Per_Hist,
nvl(ii.General_Fund_Per_Mhca * (dh.units_Assigned/ad.current_units) ,0) General_Fund_Per_Mhca,
nvl(ii.Operating_Account_Hist * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_Hist,
nvl(ii.Operating_Account_MHCA * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_MHCA,
nvl(ii.Operating_Account_YTD_Hist * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_YTD_Hist,
nvl(ii.Operating_Account_YTD_MHCA * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_YTD_MHCA,
nvl(ii.Operating_Account_Cost * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_Cost,
nvl(ii.Operating_Account_Backlog * (dh.units_Assigned/ad.current_units) ,0) Operating_Account_Backlog,
nvl(ii.NBV_Hist * (dh.units_Assigned/ad.current_units) ,0) NBV_Hist,
nvl(ii.NBV_MHCA * (dh.units_Assigned/ad.current_units) ,0) NBV_MHCA
FROM Igi_Imp_Iac_Interface ii,
Fa_Distribution_History dh,
Fa_Additions ad,
igi_imp_iac_controls ct
WHERE
ii.book_type_code=p_book_type_code AND
ii.CATEGORY_ID=NVL(P_CATEGORY_ID,ii.CATEGORY_ID) AND
ct.book_type_code = ii.book_type_code AND
dh.book_type_Code = ct.corp_book AND dh.asset_id = ii.asset_id AND
ad.asset_id = ii.asset_id AND
dh.distribution_id=p_distribution_id;
SELECT sc.Location_Flex_Structure,
bc.Accounting_Flex_Structure,
sc.asset_key_flex_structure,
sc.company_name,
sob.set_of_books_id,
sob.name,
dp.fiscal_year,
sc.Category_Flex_Structure,
sob.Currency_Code
FROM fa_system_controls sc,
gl_sets_of_books sob,
fa_book_controls bc,
fa_deprn_periods dp
WHERE
bc.Book_Type_Code = p_book_type_code AND
sob.Set_Of_Books_ID = bc.Set_Of_Books_ID AND
dp.book_type_code =bc.Book_Type_Code AND
dp.period_counter= p_max_period_counter-1;
SELECT max(period_counter)
FROM igi_imp_iac_controls
WHERE Book_Type_Code = p_book_type_code;
SELECT period_name
FROM fa_deprn_periods
WHERE Book_Type_Code = p_book_type_code AND
period_counter = p_max_period_counter-1;
SELECT dp.period_counter
FROM fa_deprn_periods dp,
fa_distribution_history dh
WHERE dh.date_ineffective between dp.period_open_date and dp.period_close_date AND
dp.book_type_code=p_book_type_code AND
dp.fiscal_year=p_fiscal_year AND
dh.book_type_code=p_dist_source_book AND
dh.distribution_id=p_distribution_id;
SELECT backlog_deprn_rsv_ccid ,
general_fund_ccid ,
operating_expense_ccid,
reval_rsv_ccid
FROM igi_iac_category_books
WHERE book_type_code=p_dist_source_book AND
category_id=p_category_id;
SELECT ad.asset_key_ccid,
ad.description,
ad.parent_asset_id,
ad.Tag_number,
ad.serial_number ,
bk.date_placed_in_service ,
bk.deprn_method_code
FROM fa_books bk,
fa_additions ad
WHERE ad.asset_id=p_asset_id AND
ad.asset_id = bk.asset_id AND
bk.transaction_header_id_out is NULL AND
bk.book_type_code = p_book_type_code ;
SELECT asset_cost_account_ccid ,
deprn_expense_account_ccid ,
deprn_reserve_account_ccid
FROM fa_distribution_accounts
WHERE distribution_id=p_distribution_id AND
book_type_code=p_book_type_code;
SELECT dp.calendar_period_open_date,
dp.period_counter,
dh.distribution_id,
dh.code_combination_id,
dh.date_ineffective,
dh.location_id
FROM fa_distribution_history dh,
fa_deprn_periods dp
WHERE dh.asset_id= p_asset_id AND
dh.book_type_code=p_dist_source_book AND
(nvl(dh.date_ineffective,dp.period_open_date)>=dp.period_open_date) AND
dp.Book_type_code=p_book_type_code AND
dp.fiscal_year=p_fiscal_year AND
dp.period_num=(SELECT min(period_num)
FROM fa_deprn_periods
WHERE fiscal_year=p_fiscal_year AND
book_type_code=p_book_type_code);
SELECT asset_id
FROM igi_imp_iac_interface
WHERE
book_type_code=p_book_type_code AND
category_id=p_category_id;
SELECT distinct category_id
FROM
igi_imp_iac_interface_ctrl
WHERE book_type_code=p_book_type_code AND
category_id=nvl(p_category_id,category_id);
SELECT ytd_deprn
FROM fa_deprn_detail
WHERE distribution_id=p_distribution_id AND
book_type_code =p_book_type_code AND
asset_id =p_asset_id AND
period_counter=p_period_counter ;
SELECT ytd_deprn
FROM fa_deprn_detail
WHERE distribution_id=p_distribution_id AND
book_type_code =p_book_type_code AND
asset_id =p_asset_id AND
period_counter=(select max(period_counter)
from fa_deprn_detail
where asset_id=p_asset_id
and distribution_id=p_distribution_id
and book_type_code =p_book_type_code);
SELECT ytd_deprn
FROM fa_deprn_summary
WHERE asset_id=p_asset_id AND
book_type_code=p_book_type_code AND
period_counter=p_max_period_counter-1;
SELECT ytd_deprn,fiscal_year
FROM fa_deprn_summary ds, fa_deprn_periods dp
WHERE ds.asset_id=p_asset_id AND
ds.book_type_code=p_book_type_code AND
ds.book_type_code = dp.book_type_code and
ds.period_counter= (select max(period_counter)
from fa_deprn_summary
where asset_id=p_asset_id
and book_type_code=p_book_type_code )
and ds.period_counter=dp.period_counter;
SELECT ah.category_id
FROM fa_asset_history ah,
fa_distribution_history dh
WHERE dh.book_type_code = cp_book_type_code AND
dh.asset_id = cp_asset_id AND
dh.distribution_id = cp_distribution_id AND
dh.asset_id = ah.asset_id AND
dh.date_ineffective BETWEEN
ah.date_effective AND nvl(ah.date_ineffective,sysdate);
Select depreciate_flag
From fa_books bk
Where bk.book_type_code = cp_book_type_code
and bk.asset_id =cp_asset_id
and bk.transaction_header_id_out is null;
INSERT INTO IGI_IMP_IAC_ITF( distribution_id ,
request_id ,
set_of_books_id ,
asset_id,
category_id,
functional_currency_code ,
book_type_code ,
fiscal_year_name ,
period ,
cost_center ,
asset_number ,
asset_description ,
major_category ,
minor_category ,
concat_category ,
reval_cost ,
net_reval_reserve ,
reval_res_blog ,
reval_ytd_deprn ,
reval_deprn_reserve ,
oper_acct ,
oper_exp_backlog ,
general_fund ,
parent_no ,
curr_reval_factor ,
cumm_reval_factor,
asset_tag ,
balancing_segment ,
serial_no ,
dpis ,
life_months ,
stl_rate ,
depreciation_method ,
conc_asset_key ,
conc_location ,
cost_acct ,
iac_reval_resv_acct ,
deprn_res_acct ,
deprn_backlog_acct ,
gen_fund_acct ,
deprn_exp_acct ,
oper_exp_acct ,
hist_cost ,
hist_ytd_deprn ,
hist_acc_deprn,
organization_name,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date)
VALUES
( l_deprn.distribution_id ,
p_request_id,
l_set_of_books_id ,
l_asset_id,
l_category_id,
l_currency_code ,
p_book_type_code ,
l_fiscal_year,
l_period_name ,
l_cost_center ,
l_asset_number ,
l_acct.description ,
l_major_category ,
l_minor_category ,
l_concat_category ,
l_main.cost_mhca ,
l_main.reval_reserve_mhca ,
l_main.backlog_mhca ,
l_ytd_mhca ,
l_main.Accum_Deprn_MHCA ,
l_main.operating_account_cost * -1, --Bug 3277826
l_main.operating_account_backlog * -1, --Bug 3277826
l_main.general_fund_mhca,
l_acct.parent_asset_id ,
l_current_reval_factor ,
l_cumulative_reval_factor ,
l_acct.tag_number ,
l_balancing_seg ,
l_acct.serial_number ,
l_acct.date_placed_in_service ,
l_main.life_in_months ,
NULL ,
l_acct.deprn_method_code ,
l_concat_asset_key ,
l_concat_location ,
l_asset_cost_acct,
l_reval_rsv_acct ,
l_deprn_reserve_acct ,
l_blog_deprn_rsv_acct,
l_general_fund_acct,
l_deprn_expense_acct ,
l_oper_expense_acct,
l_main.cost_hist,
l_ytd_hist ,
l_main.accum_deprn_hist,
l_organization_name,
l_user_id,
sysdate,
l_login_id,
l_user_id,
sysdate) ;