[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 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 'Y'
from fa_books
where group_asset_id = p_asset_id
and book_type_code = p_book_type_code
and 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
FROM FA_METHODS
WHERE METHOD_CODE = p_deprn_method
AND LIFE_IN_MONTHS = p_life_in_months;
SELECT formula_actual,
formula_displayed,
formula_parsed
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');
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_Calling_Fn => 'fa_asset_val_pvt.validate_life');
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);
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(*)
into l_tag_number_count
from fa_mass_additions
where tag_number = p_tag_number
and mass_addition_id <> nvl (p_mass_addition_id, -999);
/* 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(1)
into l_count
from dual
where exists (
select 1
from fa_transaction_headers th
where th.book_type_code = p_book
and th.asset_id = p_asset_id
and (th.transaction_subtype = 'AMORTIZED' OR th.transaction_key = 'UA')
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(1)
into l_count
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' OR transaction_key = 'UA'));
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 book_type_code = p_book
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 = ( select asset_category_id
from fa_additions_b
where asset_id = p_asset_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_amount, 0) -
nvl(outbk.allowed_deprn_limit_amount, 0))
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 not in ('TRANSFER OUT', 'TRANSFER IN',
'TRANSFER', 'TRANSFER IN/VOID',
'RECLASS', 'UNIT ADJUSTMENT',
'REINSTATEMENT', 'ADDITION/VOID',
'CIP ADDITION/VOID')
and th.transaction_header_id <> p_transaction_header_id
and decode(th.transaction_type_code,
'ADDITION', inbk.date_placed_in_service,
'CIP ADDITION', inbk.date_placed_in_service,
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);
select sum(inbk.cost - nvl(outbk.cost, 0))
, sum(inbk.salvage_value - nvl(outbk.salvage_value, 0))
, sum(nvl(inbk.allowed_deprn_limit_amount, 0) -
nvl(outbk.allowed_deprn_limit_amount, 0))
from fa_transaction_headers th,
fa_books_mrc_v inbk,
fa_books_mrc_v 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 not in ('TRANSFER OUT', 'TRANSFER IN',
'TRANSFER', 'TRANSFER IN/VOID',
'RECLASS', 'UNIT ADJUSTMENT',
'REINSTATEMENT', 'ADDITION/VOID',
'CIP ADDITION/VOID')
and th.transaction_header_id <> p_transaction_header_id
and decode(th.transaction_type_code,
'ADDITION', inbk.date_placed_in_service,
'CIP ADDITION', inbk.date_placed_in_service,
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);
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 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;