[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_number,
faav.asset_id,
faav.tag_number,
faav.model_number,
flk.concatenated_segments,
fb.book_type_code --- corporate book code, the asset in tax book cannot be assigned.
FROM fa_additions_v faav,
fa_books fb,
fa_distribution_history fdh,
fa_locations_kfv flk,
fa_book_controls_sec fbc,
fa_transaction_history_trx_v fthv
WHERE fb.date_ineffective IS NULL
AND fb.transaction_header_id_out IS NULL
--AND faav.asset_id = 109098
AND faav.asset_id = fb.asset_id
AND fb.book_type_code = fbc.book_type_code
AND fbc.book_class IN ('CORPORATE', 'TAX')
AND fbc.gl_posting_allowed_flag = 'YES'
-- Check ledger
AND fbc.set_of_books_id = pn_ledger_id
--AND fbc.set_of_books_id = 566
AND flk.location_id = fdh.location_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 = fb.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 = fb.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 = fb.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'
-- Check the asset added date
AND fthv.asset_id = faav.asset_id
AND fthv.transaction_type_code = 'ADDITION'
AND fthv.book_type_code = fb.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, flk.concatenated_segments;
SELECT start_date, period_num
INTO ld_start_date, ln_period_num
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 MAX(fdp.period_close_date)
INTO ld_dp_period_close_date
FROM fa_deprn_periods fdp
WHERE greatest(greatest(fdp.calendar_period_open_date,
least(sysdate, fdp.calendar_period_close_date)),
fdp.calendar_period_open_date) BETWEEN ld_start_date AND
ld_end_date
AND fdp.period_close_date IS NOT NULL;
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_corp_book
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_corp_book
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_corp_book
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_corp_book
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);