[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT fps.period_name, fps.start_date, fps.end_date
FROM gl_period_statuses fps
WHERE ledger_id = pn_ledger_id --set_of_books_id = l_set_of_books_id
AND application_id = 101
AND fps.start_date >= ld_start_date
AND fps.end_date <= ld_end_date
AND fps.adjustment_period_flag = 'N';
SELECT start_date
INTO ld_start_date
FROM gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_from
AND to_char(period_year) = pv_accounting_year;
SELECT end_date
INTO ld_end_date
FROM gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_to
AND to_char(period_year) = pv_accounting_year;
SELECT distinct faav.asset_id,
faav.asset_number,
faav.attribute_category_code,
faav.description,
bk.date_placed_in_service,
bk.book_type_code,
faav.current_units,
bk.deprn_method_code,
bk.life_in_months,
fncv.name,
bk.cost,
bk.salvage_value,
bk.unit_of_measure --UOM
,
bk.production_capacity,
fcb.asset_cost_acct,
fcb.impair_reserve_acct,
fcb.deprn_reserve_acct,
fbc.book_class,
faav.asset_key_ccid
FROM fa_additions_v faav
--, fa_asset_v fasv
,
fa_books bk,
fa_book_controls_sec fbc,
fa_category_books fcb,
fa_methods mth,
fa_distribution_history fdh,
gl_ledgers gl,
fa_transaction_history_trx_v fthv,
fnd_currencies_vl fncv
-- Check ledger
WHERE bk.date_ineffective IS NULL
AND bk.transaction_header_id_out IS NULL
AND faav.asset_id = bk.asset_id
AND bk.book_type_code = fbc.book_type_code
AND fbc.book_class IN ('CORPORATE', 'TAX')
AND fbc.gl_posting_allowed_flag = 'YES'
AND fbc.set_of_books_id = pn_ledger_id
AND fdh.asset_id = faav.asset_id
-- Check legal entity
AND EXISTS
(SELECT fdp.period_close_date
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = bk.book_type_code
AND fdp.calendar_period_close_date BETWEEN ld_start_date AND
ld_end_date
AND fdp.period_close_date IS NOT NULL)
AND fdh.date_effective <
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = bk.book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE)
AND (fdh.date_ineffective >
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = bk.book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE) OR fdh.date_ineffective IS NULL)
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE ja_cn_cfs_data_clt_pkg.get_balancing_segment(fdh.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
-- Check the asset type
AND faav.asset_type = 'CAPITALIZED'
--get category accounts
AND fcb.category_id = faav.asset_category_id
AND fcb.book_type_code = bk.book_type_code
-- get the book window elements.
AND bk.asset_id = faav.asset_id
AND bk.date_ineffective IS NULL
AND bk.book_type_code = fbc.book_type_code
AND bk.deprn_method_code = mth.method_code
AND (bk.life_in_months = mth.life_in_months OR
(bk.life_in_months IS NULL AND mth.life_in_months IS NULL))
--get the currency
AND gl.ledger_id = pn_ledger_id
AND gl.currency_code = fncv.currency_code
-- Check the asset added date
AND fthv.asset_id = faav.asset_id
AND fthv.transaction_type_code = 'ADDITION'
AND fthv.book_type_code = bk.book_type_code
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)) <= ld_end_date)
ORDER BY faav.asset_number;
SELECT concatenated_segment_delimiter
FROM fnd_id_flex_structures
WHERE id_flex_code = 'CAT#';
SELECT start_date, period_num
INTO ld_start_date, lv_accounting_period
FROM gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_from
AND to_char(period_year) = pv_accounting_year;
SELECT end_date
INTO ld_end_date
FROM gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_to
AND to_char(period_year) = pv_accounting_year;
SELECT application_column_name
INTO lv_nature_segment
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_num = pn_coa_id
AND id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_ACCOUNT';
SELECT dffa.attribute_column
into lv_usage_segment
FROM ja_cn_dff_assignments_v dffa
WHERE dffa.lookup_code = 'FAAU'
AND dffa.chart_of_accounts_id = pn_coa_id;
SELECT dffa.attribute_column
into lv_measure_segment
FROM ja_cn_dff_assignments_v dffa
WHERE dffa.lookup_code = 'FAUM'
AND dffa.chart_of_accounts_id = pn_coa_id;
SELECT COUNT(*)
INTO ln_full_retirement_count
FROM fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'FULL RETIREMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
SELECT COUNT(*)
INTO ln_reinstate_count
FROM fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'REINSTATEMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)) <=
ld_end_date);
SELECT greatest(date_effective)
INTO ld_retirement_date
FROM fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'FULL RETIREMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)) <=
ld_end_date);
SELECT greatest(date_effective)
INTO ld_reinstated_date
FROM fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'REINSTATEMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)) <=
ld_end_date);
SELECT MAX(dp.period_counter)
INTO ln_depreciation_counter
FROM fa_deprn_periods dp
WHERE dp.book_type_code = lv_book_type_code
--AND dp.calendar_period_close_date >= ld_end_date
AND dp.calendar_period_open_date <= ld_end_date
AND dp.period_close_date IS NOT NULL;
SELECT nvl(MAX(period_counter), 0)
INTO ln_real_deprn_counter
FROM fa_deprn_summary
WHERE asset_id = lv_asset_id
AND book_type_code = lv_book_type_code
AND deprn_source_code = 'DEPRN';
SELECT MIN(thv.transaction_header_id)
INTO ln_transaction_header_id
FROM fa_deprn_periods dp,
fa_transaction_history_trx_v thv,
fa_transaction_headers fthr
WHERE thv.book_type_code = dp.book_type_code
AND thv.asset_id = lv_asset_id
AND dp.book_type_code = lv_book_type_code
AND thv.transaction_type_code IN
('ADJUSTMENT',
'FULL RETIREMENT',
'PARTIAL RETIREMENT',
'REINSTATEMENT',
'REVALUATION')
AND thv.transaction_header_id = fthr.transaction_header_id
AND (fthr.transaction_key <> 'UA' OR
fthr.transaction_key IS NULL)
AND thv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)
AND greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date) > ld_end_date;
SELECT cost,
salvage_value,
adjusted_recoverable_cost,
deprn_method_code,
production_capacity,
date_placed_in_service
INTO ln_original_value,
ln_salvage_value,
ln_recoverable_cost,
lv_depn_meth_code,
ln_production_capacity,
ld_in_service_date
FROM fa_books
WHERE transaction_header_id_out = ln_transaction_header_id;
SELECT MIN(thv.transaction_header_id)
INTO ln_transaction_header_id
FROM fa_deprn_periods dp, fa_transaction_history_trx_v thv
WHERE asset_id = lv_asset_id
AND dp.book_type_code = lv_book_type_code
AND transaction_type_code IN ('UNIT ADJUSTMENT', 'RECLASS')
AND thv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)
AND greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date) > ld_end_date;
SELECT fhv.units, ckfv.concatenated_segments
INTO ln_asset_unit, lv_category_code
FROM fa_asset_history_v fhv, fa_categories_b_kfv ckfv
WHERE fhv.key = ln_transaction_header_id
AND fhv.transaction_header_id_out =
ln_transaction_header_id
AND fhv.category_id = ckfv.category_id;
SELECT deprn_reserve,
impairment_reserve,
ltd_production,
deprn_amount,
nvl(impairment_amount, 0)
INTO ln_deprn_reserve,
ln_impairment_rsv,
ln_life_production,
ln_monthly_deprn_amount,
ln_mth_impairment_amount
FROM fa_deprn_summary
WHERE asset_id = lv_asset_id
AND period_counter = ln_depreciation_counter
AND book_type_code = lv_book_type_code;
SELECT SUM(fdh.units_assigned)
INTO ln_assigned_unit
FROM fa_distribution_history fdh
WHERE fdh.asset_id = lv_asset_id
AND fdh.date_effective <
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE)
AND (fdh.date_ineffective >
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE) OR fdh.date_ineffective IS NULL)
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE ja_cn_cfs_data_clt_pkg.get_balancing_segment(fdh.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
GROUP BY fdh.asset_id;
SELECT MIN(fthv.transaction_header_id)
INTO ln_retire_tran_header_id
FROM fa_transaction_history_trx_v fthv, fa_retirements fr
WHERE fthv.book_type_code = lv_book_type_code
AND fthv.asset_id = lv_asset_id
AND fthv.transaction_type_code IN
('FULL RETIREMENT', 'PARTIAL RETIREMENT')
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)) BETWEEN
ld_start_date --from date
AND ld_end_date --to date)
)
AND fr.transaction_header_id_in = fthv.transaction_header_id
AND fr.status <> 'PENDING'
AND fr.asset_id = fthv.asset_id
AND (TRANSACTION_HEADER_ID_OUT IS NULL OR NOT EXISTS
(SELECT thv.transaction_header_id
FROM fa_transaction_history_trx_v thv
WHERE thv.transaction_header_id =
fr.transaction_header_id_out
AND thv.book_type_code = lv_book_type_code
AND thv.asset_id = lv_asset_id
AND thv.transaction_type_code = 'REINSTATEMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE,
dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE thv.book_type_code = dp.book_type_code
AND thv.date_effective BETWEEN
dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)) BETWEEN
ld_start_date --from date
AND ld_end_date --to date)
)));
SELECT fb.cost, fb.salvage_value
INTO ln_original_value, ln_salvage_value
FROM fa_books fb
WHERE fb.transaction_header_id_out = ln_retire_tran_header_id;
SELECT nvl(fds.deprn_reserve, 0),
nvl(fds.impairment_reserve, 0)
INTO ln_deprn_rsv_last, ln_impt_rsv_last
FROM fa_deprn_summary fds
WHERE fds.asset_id = lv_asset_id
AND fds.book_type_code = lv_book_type_code
AND fds.period_counter =
(SELECT MAX(period_counter)
FROM fa_deprn_summary fds2
WHERE fds2.asset_id = lv_asset_id
AND fds2.book_type_code = lv_book_type_code
AND fds2.period_counter < ln_depreciation_counter);
SELECT SUM(fdh.units_assigned)
INTO ln_asset_unit
FROM fa_distribution_history fdh
WHERE fdh.asset_id = lv_asset_id
AND fdh.date_effective <
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <=
ld_start_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE)
AND (fdh.date_ineffective >
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <=
ld_start_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE) OR fdh.date_ineffective IS NULL);
SELECT SUM(fdh.units_assigned)
INTO ln_assigned_unit
FROM fa_distribution_history fdh
WHERE fdh.asset_id = lv_asset_id
AND fdh.date_effective <
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <=
ld_start_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE)
AND (fdh.date_ineffective >
nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <=
ld_start_date
AND fdp.period_close_date IS NOT NULL),
SYSDATE) OR fdh.date_ineffective IS NULL)
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE ja_cn_cfs_data_clt_pkg.get_balancing_segment(fdh.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
GROUP BY fdh.asset_id;
SELECT COUNT(period_counter)
INTO ln_dep_month
FROM fa_deprn_summary
WHERE asset_id = lv_asset_id
AND period_counter <= ln_depreciation_counter
AND book_type_code = lv_book_type_code
AND deprn_source_code = 'DEPRN'
AND deprn_amount <> 0;
SELECT nvl(fdp.calendar_period_close_date, ld_in_service_date)
INTO ld_addition_date
FROM fa_deprn_periods fdp, fa_transaction_history_trx_v thv
WHERE thv.asset_id = lv_asset_id
AND fdp.book_type_code = lv_book_type_code
AND thv.book_type_code = fdp.book_type_code
AND thv.transaction_type_code = 'ADDITION'
AND thv.period_entered = fdp.period_name;
SELECT decode(lv_measure_segment,
'SEGMENT1',
faak.segment1,
'SEGMENT2',
faak.segment2,
'SEGMENT3',
faak.segment3,
'SEGMENT4',
faak.segment4,
'SEGMENT5',
faak.segment5,
'SEGMENT6',
faak.segment6,
'SEGMENT7',
faak.segment7,
'SEGMENT8',
faak.segment8,
'SEGMENT9',
faak.segment9,
'SEGMENT10',
faak.segment10),
decode(lv_usage_segment,
'SEGMENT1',
faak.segment1,
'SEGMENT2',
faak.segment2,
'SEGMENT3',
faak.segment3,
'SEGMENT4',
faak.segment4,
'SEGMENT5',
faak.segment5,
'SEGMENT6',
faak.segment6,
'SEGMENT7',
faak.segment7,
'SEGMENT8',
faak.segment8,
'SEGMENT9',
faak.segment9,
'SEGMENT10',
faak.segment10)
INTO lv_asset_measure, lv_asset_usage
FROM fa_asset_keywords faak
WHERE faak.code_combination_id = ln_asset_key_ccid;