[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_count
from fa_additions_b
where asset_number = upper(p_asset_number);
select count(*)
into l_count
from fa_mass_additions
where asset_number = p_asset_number
and queue_name = 'POST'; -- fix for bug 3433702
select count(*)
into l_count
from dual
where nvl(substr(p_asset_number, 1,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 2,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 3,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 4,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 5,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 6,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 7,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 8,1), '0') between '0' and '9'
and nvl(substr(p_asset_number, 9,1), '0') between '0' and '9'
and nvl(substr(p_asset_number,10,1), '0') between '0' and '9'
and nvl(substr(p_asset_number,11,1), '0') between '0' and '9'
and nvl(substr(p_asset_number,12,1), '0') between '0' and '9'
and nvl(substr(p_asset_number,13,1), '0') between '0' and '9'
and nvl(substr(p_asset_number,14,1), '0') between '0' and '9'
and nvl(substr(p_asset_number,15,1), '0') between '0' and '9';
select 1
from fnd_id_flex_segments
where application_id = 140
and id_flex_code = 'KEY#'
and id_flex_num = fa_cache_pkg.fazsys_record.asset_key_flex_structure
and required_flag = 'Y';
select count(*)
into l_is_asset_key_valid
from fa_asset_keywords
where code_combination_id = p_asset_key_ccid
and enabled_flag = 'Y';
select count(*)
into l_exists
from fa_books
where book_type_code = p_book_type_code
and asset_id = p_asset_id
and rownum <= 1;
select count(*)
into l_exists
from fa_books bks
where exists
(
select 'X'
from fa_book_controls bc
where bc.book_type_code = p_book_type_code
and bc.distribution_source_book = bks.book_type_code
)
and bks.asset_id = p_asset_id;
select count(*)
into l_exists
from fa_books
where book_type_code = p_book_type_code
and asset_id = p_asset_id
and rownum <= 1;
select date_placed_in_service
into l_dpis
from fa_books
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_asset_hdr_rec.asset_id
and transaction_header_id_out is null;
SELECT 1
INTO x_same_period
FROM fa_calendar_periods fcp, fa_book_controls fbc
WHERE fbc.book_type_code = p_asset_hdr_rec.book_type_code
AND fcp.calendar_type = fbc.deprn_calendar
AND p_trans_rec.transaction_date_entered BETWEEN
fcp.start_date AND fcp.end_date
AND l_dpis BETWEEN
fcp.start_date AND fcp.end_date;
select count(*)
into l_rowcount
from per_periods_of_service s, per_people_f p
where p.person_id = s.person_id
and trunc(p_date) between
p.effective_start_date and p.effective_end_date
and nvl(s.actual_termination_date,p_date) >= p_date
and p.person_id = p_assigned_to;
select count(*)
into l_is_location_valid
from fa_locations
where location_id = p_location_ccid
and enabled_flag = 'Y';
SELECT TH.TRANSACTION_HEADER_ID
FROM FA_TRANSACTION_HEADERS TH
WHERE TH.ASSET_ID = p_asset_id
AND TH.BOOK_TYPE_CODE = p_book_type_code
AND TH.TRANSACTION_TYPE_CODE IN ('ADJUSTMENT', 'GROUP ADJUSTMENT', 'REVALUATION', 'TAX')
AND NVL(TH.AMORTIZATION_START_DATE, TH.TRANSACTION_DATE_ENTERED)
< p_date_placed_in_service
UNION
SELECT TH.TRANSACTION_HEADER_ID
FROM FA_TRANSACTION_HEADERS TH,
FA_RETIREMENTS RET
WHERE TH.ASSET_ID = p_asset_id
AND TH.BOOK_TYPE_CODE = p_book_type_code
AND TH.TRANSACTION_TYPE_CODE IN
('FULL RETIREMENT', 'PARTIAL RETIREMENT')
AND NVL(TH.AMORTIZATION_START_DATE, TH.TRANSACTION_DATE_ENTERED)
< p_date_placed_in_service
AND RET.ASSET_ID = TH.ASSET_ID
AND RET.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
AND RET.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
AND RET.TRANSACTION_HEADER_ID_OUT IS NULL;
select prorate_date
into l_prorate_date
from fa_conventions
where prorate_convention_code = p_prorate_convention_code
and p_date_placed_in_service between start_date and end_date;
select 'x'
into l_check_prorate_date
from fa_calendar_periods cp,
fa_book_controls bc
where bc.book_type_code = p_book_type_code
and bc.prorate_calendar = cp.calendar_type
and l_prorate_date between cp.start_date and cp.end_date;
select MAX(transaction_date_entered),
MAX(date_effective)
into l_prior_transaction_date,
l_prior_date_effective
from fa_transaction_headers
where asset_id = p_asset_id
and book_type_code = p_book_type_code;
select count(*)
into l_count
from fa_deprn_periods pdp,
fa_deprn_periods adp
where pdp.book_type_code = p_book_type_code
and pdp.book_type_code = adp.book_type_code
and pdp.period_counter > adp.period_counter
and l_prior_date_effective between pdp.period_open_date
and nvl(pdp.period_close_date, to_date('31-12-4712','DD-MM-YYYY'))
and x_amortization_start_date between
adp.calendar_period_open_date and adp.calendar_period_close_date;
select MAX(transaction_date_entered) -- date_effective
into l_prior_transaction_date -- l_prior_date_effective
from fa_transaction_headers
where asset_id = p_asset_id
and book_type_code = p_book_type_code
and transaction_type_code in
('REVALUATION');
SELECT DISTINCT
method_id,
name,
deprn_basis_rule,
depreciate_lastyear_flag,
stl_method_flag,
exclude_salvage_value_flag,
polish_adj_calc_basis_flag,
guarantee_rate_method_flag
FROM FA_METHODS
WHERE METHOD_CODE = p_deprn_method
AND LIFE_IN_MONTHS = p_life_in_months;
SELECT formula_actual,
formula_displayed,
formula_parsed,
original_rate,
revised_rate,
guarantee_rate
FROM FA_FORMULAS
WHERE method_id = p_method_id;
select FA_METHODS_S.NEXTVAL
into l_method_id
from sys.dual;
fa_debug_pkg.add(l_calling_fn, 'inserting', 'new method', p_log_level_rec => p_log_level_rec);
FA_METHODS_PKG.Insert_Row(
X_Rowid => l_rowid,
X_Method_Id => l_method_id,
X_Method_Code => p_deprn_method,
X_Life_In_Months => p_lim,
X_Depreciate_Lastyear_Flag => l_dep_last_year_flag, -- 'YES',
X_STL_Method_Flag => l_stl_method_flag, -- 'YES'
X_Rate_Source_Rule => p_rate_source_rule, -- 'CALCULATED',
X_Deprn_Basis_Rule => l_deprn_basis_rule, -- 'COST',
X_Prorate_Periods_Per_Year => NULL,
X_Name => l_method_name,
X_Last_Update_Date => p_curr_date,
X_Last_Updated_By => p_user_id,
X_Created_By => p_user_id,
X_Creation_Date => p_curr_date,
X_Last_Update_Login => -1,
X_Attribute1 => null,
X_Attribute2 => null,
X_Attribute3 => null,
X_Attribute4 => null,
X_Attribute5 => null,
X_Attribute6 => null,
X_Attribute7 => null,
X_Attribute8 => null,
X_Attribute9 => null,
X_Attribute10 => null,
X_Attribute11 => null,
X_Attribute12 => null,
X_Attribute13 => null,
X_Attribute14 => null,
X_Attribute15 => null,
X_Attribute_Category_Code => null,
X_Exclude_Salvage_Value_Flag => l_exclude_sal_flag,
X_Polish_Adj_Calc_Basis_Flag => l_polish_adj_calc_basis_flag,
X_Guarantee_Rate_Method_Flag => l_guarantee_rate_method_flag,
X_Calling_Fn => 'fa_asset_val_pvt.validate_life', p_log_level_rec => p_log_level_rec);
FA_FORMULAS_PKG.insert_row
(X_ROWID => l_rowid,
X_METHOD_ID => l_method_id,
X_FORMULA_ACTUAL => l_formula_actual,
X_FORMULA_DISPLAYED => l_formula_displayed,
X_FORMULA_PARSED => l_formula_parsed,
X_CREATION_DATE => p_curr_date,
X_CREATED_BY => p_user_id,
X_LAST_UPDATE_DATE => p_curr_date,
X_LAST_UPDATED_BY => p_user_id,
X_LAST_UPDATE_LOGIN => -1,
X_ORIGINAL_RATE => l_original_rate,
X_REVISED_RATE => l_revised_rate,
X_GUARANTEE_RATE => l_guarantee_rate, p_log_level_rec => p_log_level_rec);
select count(*)
into l_is_valid_payables_ccid
from gl_code_combinations
where code_combination_id = px_payables_ccid
and chart_of_accounts_id = p_gl_chart_id
and enabled_flag = 'Y'
and summary_flag = 'N'
and detail_posting_allowed_flag = 'Y';
select count(*)
into l_is_valid_expense_ccid
from gl_code_combinations
where code_combination_id = p_expense_ccid
and chart_of_accounts_id = p_gl_chart_id
and enabled_flag = 'Y'
and account_type = 'E'
and summary_flag = 'N'
and detail_posting_allowed_flag = 'Y';
select count(*)
into l_is_valid_vendor_id
from po_vendors
where vendor_id = p_po_vendor_id;
select count(*)
into l_is_valid_uom
from mtl_units_of_measure
where unit_of_measure = p_unit_of_measure
and nvl(disable_date, sysdate+1) > sysdate;
select count(*)
into l_tag_number_count
from fa_additions_b
where tag_number = p_tag_number;
select count(1)
into l_count
from fa_books bk
where bk.book_type_code= p_book
and bk.asset_id= p_asset_id
and (bk.rate_Adjustment_factor <> 1 OR
(bk.rate_adjustment_factor = 1 and
exists
(select 'YES' -- and amortized before.
from fa_transaction_headers th,
fa_methods mt
where th.book_type_code = bk.book_type_code
and th.asset_id = bk.asset_id
and (th.transaction_subtype = 'AMORTIZED'
OR th.transaction_key = 'UA')
and th.transaction_header_id = bk.transaction_header_id_in
and mt.method_code = bk.deprn_method_code
and mt.rate_source_rule IN ('TABLE','FLAT','PRODUCTION')
and th.transaction_header_id > (select max(th2.transaction_header_id)
from fa_transaction_headers th2
where th2.book_type_code = p_book
and th2.asset_id = p_asset_id
and th2.transaction_key = 'ES'))));
select count(*)
into l_count
from fa_books bk
where bk.book_type_code= p_book
and bk.asset_id= p_asset_id
and (bk.rate_Adjustment_factor <> 1 OR
(bk.rate_adjustment_factor = 1 and
exists
(select 'YES'
from fa_transaction_headers th,
fa_methods mt
where th.book_type_code = bk.book_type_code
and th.asset_id = bk.asset_id
and (th.transaction_subtype = 'AMORTIZED'
OR th.transaction_key = 'UA')
and th.transaction_header_id = bk.transaction_header_id_in
and mt.method_code = bk.deprn_method_code
and mt.rate_source_rule IN ('TABLE','FLAT','PRODUCTION','CALCULATED','FORMULA'))));
select count(1)
into l_rolled_back_imp
from dual
where exists (
select 1 from fa_transaction_headers
where book_type_code = p_book
and asset_id = p_asset_id
and transaction_subtype = 'AMORTIZED' and transaction_key = 'RM');
select count(*)
into l_count
from fa_transaction_headers
where book_type_code = p_book
and asset_id = p_asset_id
-- and transaction_type_code = 'ADJUSTMENT' bug 5326226
and (transaction_subtype = 'AMORTIZED' OR transaction_key = 'UA');
Bug 2407786 - This is the consolidated select stmnt
*/
/*
select count(*)
into l_count
from fa_books bk
where bk.book_type_code = p_book
and bk.asset_id = p_asset_id
and (bk.rate_Adjustment_factor <> 1 OR
(bk.rate_adjustment_factor = 1 and
exists (select 'YES' -- and amortized before.
from fa_transaction_headers th,
fa_methods mt
where th.book_type_code = bk.book_type_code
and th.asset_id = bk.asset_id
and th.transaction_type_code = 'ADJUSTMENT'
and (th.transaction_subtype = 'AMORTIZED' OR th.transaction_key = 'UA')
and th.transaction_header_id = bk.transaction_header_id_in
and mt.method_code = bk.deprn_method_code
and mt.rate_source_rule IN ('TABLE','FLAT','PRODUCTION'))));
SELECT count(*)
INTO l_count
FROM fa_transaction_headers
WHERE book_type_code = p_book
AND asset_id = p_asset_id
AND transaction_key like 'U%';
SELECT count(*)
INTO l_count
FROM fa_deprn_summary
WHERE book_type_code = p_book
AND asset_id = p_asset_id
AND deprn_source_code = 'BOOKS'
AND period_counter = l_last_pc;
SELECT count(*)
INTO l_count
FROM fa_transaction_headers th
WHERE th.asset_id = p_asset_id
AND th.book_type_code = p_book
AND th.transaction_type_code = 'ADDITION'
AND th.date_effective > l_period_rec.period_open_date;
select count(*)
into l_count
FROM FA_BOOKS BK
WHERE BK.ASSET_ID = p_asset_id
AND BK.PERIOD_COUNTER_FULLY_RETIRED IS NOT NULL
AND BK.DATE_INEFFECTIVE IS NULL
AND BK.BOOK_TYPE_CODE = p_book
AND rownum < 2;
select count(*)
into l_count
from fa_mass_additions
where book_type_code = p_book
and add_to_asset_id = p_asset_id
and posting_status not in ('POSTED','MERGED','SPLIT','DELETE')
and rownum < 2;
select count(*)
into l_count
from fa_additions
where asset_id = p_asset_id
and rownum < 2;
select count(*)
into l_count
from fa_retirements
where book_type_code = p_book
and asset_id = p_asset_id
and status in ('PENDING','REINSTATE');
select count(*)
into l_count
from fa_lookups_b
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select count(*)
into l_count
from fa_distribution_history
where asset_id = p_asset_id
and distribution_id = p_dist_id;
select distinct transaction_header_id
into v_xfr_out_thid
from fa_transaction_headers thd
where thd.asset_id = p_Asset_Id
and thd.TRANSACTION_TYPE_CODE = 'TRANSFER OUT'
and thd.book_type_code = p_book
and thd.transaction_header_id > p_Transaction_Header_Id_In
and rownum = 1;
select count(*)
into l_count1
from fa_adjustments adj,
fa_distribution_history dh
where adj.asset_id = p_asset_id
and adj.asset_id = dh.asset_id
and adj.distribution_id = dh.distribution_id
and dh.transaction_header_id_in = v_xfr_out_thid
and adj.transaction_header_id <> v_xfr_out_thid;
select count(*)
into l_count2
from fa_deprn_detail dd,
fa_distribution_history dh
where dd.asset_id = p_asset_id
and dd.asset_id = dh.asset_id
and dd.distribution_id = dh.distribution_id
and dh.transaction_header_id_in = v_xfr_out_thid;
select count(1)
into l_count
from fa_books
where book_type_code = l_corp_book
and asset_id = p_parent_asset_id
and date_ineffective is null;
SELECT count(w.warranty_id) INTO l_count
FROM fa_warranties w
WHERE w.warranty_id = p_warranty_id
AND p_date_placed_in_service between
nvl (w.start_date, p_date_placed_in_service) and
nvl (w.end_date, p_date_placed_in_service);
SELECT count(w.warranty_id) INTO l_count
FROM gl_sets_of_books glsob,
fa_book_controls bc,
fa_warranties w
WHERE w.warranty_id = p_warranty_id
AND bc.book_type_code = p_book_type_code
AND bc.set_of_books_id = glsob.set_of_books_id
AND glsob.currency_code =
nvl(w.currency_code, glsob.currency_code);
select category_type
from fa_categories_b
where category_id = p_category_id;
select currency_code
from gl_sets_of_books sob,
fa_book_controls bc,
fa_books bk
where bk.asset_id = p_asset_id
and bk.date_ineffective is null
and bk.book_type_code = bc.book_type_code
and bc.set_of_books_id = sob.set_of_books_id;
select count(1)
into l_count
from fa_leases
where lease_id = p_lease_id;
select currency_code
into l_lease_currency
from fa_leases
where lease_id = p_lease_id;
select count(1)
into l_count
from fa_lookups_b
where lookup_type = 'PROPERTY TYPE'
and lookup_code = p_property_type_code;
select count(1)
into l_count
from fa_lookups_b
where lookup_type = '1245/1250 PROPERTY'
and lookup_code = p_1245_1250_code;
select 1
into l_count
from dual
where exists (select 'X'
from FA_BOOKS
where ASSET_ID = p_group_asset_id
and BOOK_TYPE_CODE = p_book_type_code);
SELECT count(1)
INTO l_disabled
FROM fa_books
WHERE asset_id = p_group_asset_id
AND book_type_code = p_book_type_code
AND disabled_flag = 'Y'
AND transaction_header_id_out is null;
SELECT count(1)
INTO l_has_members
FROM fa_books
WHERE group_asset_id = p_group_asset_id
AND book_type_code = p_book_type_code
AND transaction_header_id_out is null
AND period_counter_fully_retired is null;
select count(transaction_header_id_in)
into l_member_count
from fa_books
where group_asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id_out is null;
SELECT SUM(inbk_cost - NVL(outbk_cost, 0)) ,
SUM(inbk_salvage_value - NVL(outbk_salvage_value, 0)) ,
SUM(NVL(inbk_allowed_deprn_limit_amt, 0) - NVL(outbk_allowed_deprn_limit_amt, 0))
from ( SELECT inbk.cost inbk_cost
,outbk.cost outbk_cost
,inbk.salvage_value inbk_salvage_value
,outbk.salvage_value outbk_salvage_value
,inbk.allowed_deprn_limit_amount inbk_allowed_deprn_limit_amt
,outbk.allowed_deprn_limit_amount outbk_allowed_deprn_limit_amt
FROM fa_transaction_headers th
,fa_books inbk
,fa_books outbk
WHERE inbk.asset_id = p_asset_id
AND inbk.book_type_code = p_book_type_code
AND outbk.asset_id (+) = p_asset_id -- inbk.asset_id
AND outbk.book_type_code (+) = p_book_type_code -- inbk.book_type_code
AND inbk.transaction_header_id_in = th.transaction_header_id
AND outbk.transaction_header_id_out(+) = th.transaction_header_id
AND th.asset_id = p_asset_id
AND th.book_type_code = p_book_type_code
AND th.transaction_type_code NOT IN ('TRANSFER OUT', 'TRANSFER IN' , 'TRANSFER' , 'TRANSFER IN/VOID'
,'RECLASS' , 'UNIT ADJUSTMENT', 'REINSTATEMENT', 'ADDITION/VOID'
,'CIP ADDITION/VOID'
,'ADDITION' ,'CIP ADDITION')
AND th.transaction_header_id <> p_transaction_header_id
AND DECODE(th.transaction_subtype,
'EXPENSED', inbk.date_placed_in_service,
NVL(th.amortization_start_date, th.transaction_date_entered))
<= p_transaction_date
AND NOT EXISTS
(SELECT 'Exclude Retirement which reinstatement exists'
FROM fa_retirements ret
,fa_transaction_headers reith
WHERE ret.transaction_header_id_in = th.transaction_header_id
AND ret.transaction_header_id_out = reith.transaction_header_id
AND NVL(reith.amortization_start_date, reith.transaction_date_entered)
<= p_transaction_date)
UNION ALL
SELECT inbk.cost inbk_cost
,outbk.cost outbk_cost
,inbk.salvage_value inbk_salvage_value
,outbk.salvage_value outbk_salvage_value
,inbk.allowed_deprn_limit_amount inbk_allowed_deprn_limit_amt
,outbk.allowed_deprn_limit_amount outbk_allowed_deprn_limit_amt
FROM fa_transaction_headers th
,fa_books inbk
,fa_books outbk
WHERE inbk.asset_id = p_asset_id
AND inbk.book_type_code = p_book_type_code
AND outbk.asset_id (+) = p_asset_id
AND outbk.book_type_code (+) = p_book_type_code
AND inbk.transaction_header_id_in = th.transaction_header_id
AND DECODE(th.transaction_type_code
,'ADDITION' , to_number(NULL)
,'CIP ADDITION', to_number(NULL)
, outbk.transaction_header_id_out(+))
= th.transaction_header_id
AND th.asset_id = p_asset_id
AND th.book_type_code = p_book_type_code
AND th.transaction_type_code IN ('ADDITION' ,'CIP ADDITION')
AND th.transaction_header_id <> p_transaction_header_id
AND inbk.date_placed_in_service <= p_transaction_date
AND NOT EXISTS
(SELECT 'Exclude Retirement which reinstatement exists'
FROM fa_retirements ret
,fa_transaction_headers reith
WHERE ret.transaction_header_id_in = th.transaction_header_id
AND ret.transaction_header_id_out = reith.transaction_header_id
AND NVL(reith.amortization_start_date, reith.transaction_date_entered)
<= p_transaction_date)) ;
SELECT SUM(inbk_cost - NVL(outbk_cost, 0)) ,
SUM(inbk_salvage_value - NVL(outbk_salvage_value, 0)) ,
SUM(NVL(inbk_allowed_deprn_limit_amt, 0) - NVL(outbk_allowed_deprn_limit_amt, 0))
from ( SELECT inbk.cost inbk_cost
,outbk.cost outbk_cost
,inbk.salvage_value inbk_salvage_value
,outbk.salvage_value outbk_salvage_value
,inbk.allowed_deprn_limit_amount inbk_allowed_deprn_limit_amt
,outbk.allowed_deprn_limit_amount outbk_allowed_deprn_limit_amt
FROM fa_transaction_headers th
,fa_mc_books inbk
,fa_mc_books outbk
WHERE inbk.asset_id = p_asset_id
AND inbk.book_type_code = p_book_type_code
AND outbk.asset_id (+) = p_asset_id -- inbk.asset_id
AND outbk.book_type_code (+) = p_book_type_code -- inbk.book_type_code
AND inbk.transaction_header_id_in = th.transaction_header_id
AND outbk.transaction_header_id_out(+) = th.transaction_header_id
AND th.asset_id = p_asset_id
AND th.book_type_code = p_book_type_code
AND th.transaction_type_code NOT IN ('TRANSFER OUT', 'TRANSFER IN' , 'TRANSFER' , 'TRANSFER IN/VOID'
,'RECLASS' , 'UNIT ADJUSTMENT', 'REINSTATEMENT', 'ADDITION/VOID'
,'CIP ADDITION/VOID'
,'ADDITION' ,'CIP ADDITION')
AND th.transaction_header_id <> p_transaction_header_id
AND DECODE(th.transaction_subtype,
'EXPENSED', inbk.date_placed_in_service,
NVL(th.amortization_start_date, th.transaction_date_entered))
<= p_transaction_date
AND NOT EXISTS
(SELECT 'Exclude Retirement which reinstatement exists'
FROM fa_retirements ret
,fa_transaction_headers reith
WHERE ret.transaction_header_id_in = th.transaction_header_id
AND ret.transaction_header_id_out = reith.transaction_header_id
AND NVL(reith.amortization_start_date, reith.transaction_date_entered)
<= p_transaction_date)
UNION ALL
SELECT inbk.cost inbk_cost
,outbk.cost outbk_cost
,inbk.salvage_value inbk_salvage_value
,outbk.salvage_value outbk_salvage_value
,inbk.allowed_deprn_limit_amount inbk_allowed_deprn_limit_amt
,outbk.allowed_deprn_limit_amount outbk_allowed_deprn_limit_amt
FROM fa_transaction_headers th
,fa_mc_books inbk
,fa_mc_books outbk
WHERE inbk.asset_id = p_asset_id
AND inbk.book_type_code = p_book_type_code
AND outbk.asset_id (+) = p_asset_id
AND outbk.book_type_code (+) = p_book_type_code
AND inbk.transaction_header_id_in = th.transaction_header_id
AND DECODE(th.transaction_type_code
,'ADDITION' , to_number(NULL)
,'CIP ADDITION', to_number(NULL)
, outbk.transaction_header_id_out(+))
= th.transaction_header_id
AND th.asset_id = p_asset_id
AND th.book_type_code = p_book_type_code
AND th.transaction_type_code IN ('ADDITION' ,'CIP ADDITION')
AND th.transaction_header_id <> p_transaction_header_id
AND inbk.date_placed_in_service <= p_transaction_date
AND NOT EXISTS
(SELECT 'Exclude Retirement which reinstatement exists'
FROM fa_retirements ret
,fa_transaction_headers reith
WHERE ret.transaction_header_id_in = th.transaction_header_id
AND ret.transaction_header_id_out = reith.transaction_header_id
AND NVL(reith.amortization_start_date, reith.transaction_date_entered)
<= p_transaction_date)) ;
select count(*)
into l_mrc_count
from fa_mc_book_controls
where book_type_code = p_book_type_code
and enabled_flag = 'Y';
select 'Y'
from fa_super_group_rules
where super_group_id = p_new_super_group_id
and book_type_code = p_book_type_code
and used_flag = 'Y';
select date_placed_in_service
from fa_books
where asset_id = p_group_asset_id
and book_type_code = p_book_type_code
and transaction_header_id_out is null;
select 'x'
from FA_Transaction_Headers
where Asset_ID = l_asset_id
and Book_type_Code = l_book_type_code
and Transaction_Type_Code = 'REVALUATION';
select count(*)
into l_mrc_count
from fa_mc_book_controls
where book_type_code = p_asset_hdr_rec.book_type_code
and enabled_flag = 'Y';
select count('1')
from fa_methods fm
where fm.method_code = p_prior_deprn_method;
select count(1)
from fa_methods fm
where fm.method_code = p_method
and fm.life_in_months = p_life_in_months;
select count(1)
from fa_methods fm
,fa_flat_rates ffr
where fm.method_code = p_method
and ffr.method_id = fm.method_id
and ffr.basic_rate = p_prior_basic_rate
and ffr.adjusted_rate = p_prior_adjusted_rate;
select fcp.end_date
from fa_fiscal_year ffy
,fa_book_controls fbc
,fa_calendar_periods fcp
,fa_calendar_types fct
where ffy.fiscal_year_name = fbc.fiscal_year_name
and ffy.fiscal_year_name = fct.fiscal_year_name
and fbc.book_type_code = p_book_type_code
and fcp.calendar_type = fct.calendar_type
and fct.calendar_type = fbc.deprn_calendar
and fcp.start_date >= ffy.start_date
and fcp.end_date <= ffy.end_date
and (ffy.fiscal_year * fct.number_per_fiscal_year + fcp.period_num) = p_period_ctr;
select fdp.calendar_period_close_date
from fa_book_controls fbc
,fa_deprn_periods fdp
where fbc.book_type_code = fdp.book_type_code
and fdp.period_counter = fbc.last_period_counter+1
and fbc.book_type_code = p_book_type_code;
select fcp.end_date
from fa_fiscal_year ffy
,fa_book_controls fbc
,fa_calendar_periods fcp
,fa_calendar_types fct
where ffy.fiscal_year_name = fbc.fiscal_year_name
and ffy.fiscal_year_name = fct.fiscal_year_name
and fbc.book_type_code = p_book_type_code
and fcp.calendar_type = fct.calendar_type
and fct.calendar_type = fbc.deprn_calendar
and fcp.start_date >= ffy.start_date
and fcp.end_date <= ffy.end_date
and (ffy.fiscal_year * fct.number_per_fiscal_year + fcp.period_num) = p_period_ctr;
select fcp.end_date
from fa_fiscal_year ffy
,fa_book_controls fbc
,fa_calendar_periods fcp
,fa_calendar_types fct
where ffy.fiscal_year_name = fbc.fiscal_year_name
and ffy.fiscal_year_name = fct.fiscal_year_name
and fbc.book_type_code = p_book_type_code
and fcp.calendar_type = fct.calendar_type
and fct.calendar_type = fbc.deprn_calendar
and fcp.start_date >= ffy.start_date
and fcp.end_date <= ffy.end_date
and (ffy.fiscal_year * fct.number_per_fiscal_year + fcp.period_num) = p_period_ctr;
select fdp.calendar_period_close_date
from fa_book_controls fbc
,fa_deprn_periods fdp
where fbc.book_type_code = fdp.book_type_code
and fdp.period_counter = fbc.last_period_counter+1
and fbc.book_type_code = p_book_type_code;
select ff.original_rate
,ff.revised_rate
,ff.guarantee_rate
from fa_formulas ff
,fa_methods fm
where ff.method_id = fm.method_id
and fm.method_code = p_method_code;
select over_depreciate_option
from fa_books
where asset_id = c_asset_id
and book_type_code = c_book_type_code
and transaction_header_id_out is null;
select 1
from fa_books
where group_asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and transaction_header_id_out is null;
select 'POSTED'
from fa_mc_impairments
where status = 'POSTED'
and (asset_id = p_asset_id or cash_generating_unit_id = (select cash_generating_unit_id
from fa_mc_books bk
where bk.asset_id = p_asset_id
and bk.book_type_code = p_book
and bk.date_ineffective is null))
and book_type_code = p_book
AND PERIOD_COUNTER_IMPAIRED = l_period_rec.period_counter
AND set_of_books_id = p_set_of_books_id;
select 'POSTED'
from fa_impairments
where status = 'POSTED'
and (asset_id = p_asset_id or cash_generating_unit_id = (select cash_generating_unit_id
from fa_books bk
where bk.asset_id = p_asset_id
and bk.book_type_code = p_book
and bk.date_ineffective is null))
and book_type_code = p_book
AND PERIOD_COUNTER_IMPAIRED = l_period_rec.period_counter;
update fa_impairments
set status = 'NEW'
where status = 'PREVIEWED'
and (asset_id = p_asset_id
or cash_generating_unit_id = (select cash_generating_unit_id
from fa_books bk
where bk.asset_id = p_asset_id
and bk.book_type_code = p_book
and bk.date_ineffective is null))
and book_type_code = p_book
and period_counter_impaired = l_period_rec.period_counter;
delete from fa_mc_impairments
where status = 'PREVIEWED'
and (asset_id = p_asset_id
or cash_generating_unit_id = (select cash_generating_unit_id
from fa_books bk
where bk.asset_id = p_asset_id
and bk.book_type_code = p_book
and bk.date_ineffective is null))
and book_type_code = p_book
and period_counter_impaired = l_period_rec.period_counter;
select currency_code
from gl_sets_of_books sob
where sob.set_of_books_id = p_asset_hdr_rec.set_of_books_id;
select count(1) from dual
where exists
(select 'x' from fa_books
where book_type_code = p_asset_hdr_rec.book_type_code
and group_asset_id = h_asset_id
and transaction_header_id_out is null
and depreciate_flag = 'NO');
select count(1) from dual
where exists
(select 'x' from fa_deprn_summary
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_asset_hdr_rec.asset_id
and deprn_source_code = 'BOOKS'
and deprn_reserve > 0);
select nvl(sum(decode(debit_credit_flag,'DR',adjustment_amount,-1*adjustment_amount)),0) ret_adj_amount
from fa_transaction_headers th -- member
, fa_transaction_headers gth -- group
, fa_adjustments aj
where th.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and th.source_transaction_header_id = gth.transaction_header_id
and gth.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and gth.member_transaction_header_id = p_asset_retire_rec.detail_info.transaction_header_id_in
and aj.asset_id = th.asset_id
and aj.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and aj.transaction_header_id = th.transaction_header_id;
select nvl(sum(bk.adjusted_cost),0) mbr_sum_adj_cost
from fa_books bk
where bk.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and bk.transaction_header_id_out is null
and bk.group_asset_id = p_asset_fin_rec.group_asset_id;
select bk.cost,nvl(bk.salvage_value,0)
from fa_books bk
where bk.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and bk.asset_id = p_asset_retire_rec.detail_info.asset_id
and bk.transaction_header_id_out = p_asset_retire_rec.detail_info.transaction_header_id_in;
select ad.asset_id, ad.asset_number,
ret.transaction_header_id_in
from fa_retirements ret,
fa_additions_b ad
where ret.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and ad.asset_id = ret.asset_id
and ret.transaction_header_id_in =
(select max(far.transaction_header_id_in)
from fa_retirements far,
fa_books bk
where far.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and far.asset_id = bk.asset_id
and bk.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and bk.group_asset_id = p_asset_fin_rec.group_asset_id
and bk.transaction_header_id_out is null
and far.status = 'PROCESSED');
select bk.cost
from fa_books bk
where bk.book_type_code = p_asset_retire_rec.detail_info.book_type_code
and bk.asset_id = p_asset_fin_rec.group_asset_id
and bk.transaction_header_id_out is null;
select fdp.calendar_period_close_date
from fa_book_controls fbc
,fa_deprn_periods fdp
where fbc.book_type_code = fdp.book_type_code
and fdp.period_counter = fbc.last_period_counter+1
and fbc.book_type_code = p_book_type_code;
select fcp.end_date
,ffy.fiscal_year
,fcp.period_num
,fct.number_per_fiscal_year
from fa_fiscal_year ffy
,fa_book_controls fbc
,fa_calendar_periods fcp
,fa_calendar_types fct
where ffy.fiscal_year_name = fbc.fiscal_year_name
and ffy.fiscal_year_name = fct.fiscal_year_name
and fbc.book_type_code = p_book_type_code
and fcp.calendar_type = fct.calendar_type
and fct.calendar_type = fbc.deprn_calendar
and fcp.start_date >= ffy.start_date
and fcp.end_date <= ffy.end_date
and (fcp.period_num + (fct.number_per_fiscal_year * ffy.fiscal_year)) = p_period; -- Bug 9131620
select 1
into l_count
from dual
where exists (select 'X'
from fa_additions_b
where asset_id = p_asset_id
and asset_type = 'GROUP');
select count(1)
from fa_deprn_detail fdd,
fa_distribution_history fdh
where fdd.distribution_id = fdh.distribution_id
and fdd.book_type_code = p_book_type_code
and fdd.asset_id = p_asset_Id
and fdd.deprn_source_code = 'B'
and fdh.transaction_header_id_out is not null;
SELECT count(*)
INTO l_imp_exists
FROM
FA_TRANSACTION_HEADERS FATH,
FA_DEPRN_PERIODS FADP,
FA_FISCAL_YEAR FAFY
WHERE FATH.BOOK_TYPE_CODE = p_asset_hdr_rec.BOOK_TYPE_CODE
AND FAFY.FISCAL_YEAR_NAME = l_fiscal_year
AND FATH.ASSET_ID = p_asset_hdr_rec.ASSET_ID
AND FADP.BOOK_TYPE_CODE = FATH.BOOK_TYPE_CODE
AND NVL(FATH.TRANSACTION_KEY,'XX') = 'IM'
AND FATH.DATE_EFFECTIVE BETWEEN FADP.period_open_date AND NVL(FADP.period_close_date, SYSDATE)
AND FAFY.FISCAL_YEAR = FADP.FISCAL_YEAR
AND NVL (p_trans_rec.AMORTIZATION_START_DATE,p_trans_rec.TRANSACTION_DATE_ENTERED) < NVL(l_cal_period_open_date, SYSDATE)
AND NVL (p_trans_rec.AMORTIZATION_START_DATE,p_trans_rec.TRANSACTION_DATE_ENTERED) <= NVL(FAFY.END_DATE, SYSDATE);
SELECT 'Y'
INTO l_restrict_trx
FROM FA_BOOKS FABK
WHERE FABK.ASSET_ID = p_asset_id
AND FABK.BOOK_TYPE_CODE = p_book_type_code
AND NOT EXISTS (SELECT 1 FROM FA_BOOKS FABK1
WHERE FABK1.ASSET_ID = p_asset_id
AND FABK1.BOOK_TYPE_CODE = p_book_type_code
AND FABK1.DEPRECIATE_FLAG = 'YES')
AND NOT EXISTS (SELECT 1 FROM FA_DEPRN_SUMMARY FADS
WHERE FADS.ASSET_ID = p_asset_id
AND FADS.BOOK_TYPE_CODE = p_book_type_code
AND FADS.DEPRN_SOURCE_CODE = 'BOOKS'
AND FADS.DEPRN_RESERVE > 0 );
select deprn_reserve, ytd_deprn
from fa_deprn_summary
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and deprn_source_code = 'BOOKS';
select rate_source_rule, deprn_basis_rule
from fa_methods mth
where mth.method_code = p_method_code;
select method_code, rate_source_rule, deprn_basis_rule
from fa_methods mth, fa_books bk
where mth.method_code = bk.deprn_method_code
and bk.asset_id = p_group_asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null;
select method_code, rate_source_rule, deprn_basis_rule
from fa_methods mth, fa_books bk
where mth.method_code = bk.deprn_method_code
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book
and bk.transaction_header_id_out is null;
select 'Y'
from fa_books bk
where bk.group_asset_id = p_asset_hdr_rec.asset_id
and bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.transaction_header_id_out is null;
select 'x'
from fa_books bk,
fa_book_controls bc,
fa_methods mth
where bc.distribution_source_book = p_asset_hdr_rec.book_type_code
and bc.book_class = 'TAX'
and bk.book_type_code = bc.book_type_code
and bk.asset_id = p_asset_hdr_rec.asset_id
and bk.transaction_header_id_out is null
and bk.deprn_method_code = mth.method_code
and mth.rate_source_rule = 'PRODUCTION'
and l_rate_source_rule <> 'PRODUCTION';
select 'Y'
from fa_books bk
where bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id= p_asset_hdr_rec.asset_id
and (bk.rate_Adjustment_factor <> 1 OR
(bk.rate_adjustment_factor = 1 and
exists
(select 'YES'
from fa_transaction_headers th,
fa_methods mt
where th.book_type_code = bk.book_type_code
and th.asset_id = bk.asset_id
and (th.transaction_subtype = 'AMORTIZED'
OR th.transaction_key = 'UA')
and th.transaction_header_id = bk.transaction_header_id_in
and mt.method_code = bk.deprn_method_code
and mt.rate_source_rule IN ('TABLE','FLAT','PRODUCTION','CALCULATED','FORMULA'))));
SELECT bal_seg_value_option_code,bal_seg_column_name
INTO l_bal_seg_value_option_code,l_bal_seg_col_name
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
query_str := 'SELECT ''Y''
FROM gl_ledger_segment_values glsv
WHERE glsv.segment_value in ( SELECT ' || l_bal_seg_col_name || '
FROM gl_code_combinations gcc
WHERE chart_of_accounts_id = :h_gl_chart_id
AND code_combination_id = :h_expense_ccid
AND enabled_flag = ''Y'')
AND nvl(END_DATE,:h_val_date) <= :h_val_date
AND ledger_id = :h_ledger_id';
select mt.method_code, mt.method_id, mt.deprn_basis_rule,ru.rule_name
from fa_methods mt, fa_deprn_basis_rules ru
where mt.deprn_basis_rule_id = ru.deprn_basis_rule_id(+)
and mt.method_code = p_method_code;
select bk.asset_id, bk.tracking_method,bk.member_rollup_flag,bk.deprn_method_code
from fa_books bk
where bk.book_type_code = p_asset_hdr_rec.book_type_code
and bk.asset_id = p_grp_asset_id
and transaction_header_id_out is null;
select count(1)
from dual
where exists
(
select 1
from fa_retirements
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_asset_hdr_rec.asset_id
and status = 'PROCESSED'
);
select count(1)
from dual
where exists
(
select 1
from fa_books bk
where book_type_code = p_asset_hdr_rec.book_type_code
and group_asset_id = p_asset_hdr_rec.asset_id
and transaction_header_id_out is null
);
select count(1)
from dual
where exists
(
select 1
from fa_books bk
where book_type_code = p_asset_hdr_rec.book_type_code
and group_asset_id = p_group_asset_id
and asset_id <> p_asset_hdr_rec.asset_id
and transaction_header_id_out is null
);
select adjusted_cost
from fa_books
where book_type_code = p_asset_hdr_rec.book_type_code
and asset_id = p_asset_hdr_rec.asset_id
and transaction_header_id_out is null;