[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT fth.transaction_header_id,
greatest(greatest(fdp.calendar_period_open_date,
least(sysdate,
fdp.calendar_period_close_date)),
fdp.calendar_period_open_date) transaction_date,
fth.transaction_type_code,
fa.asset_id,
fa.asset_number,
fa.description,
fth.transaction_name,
fth.book_type_code
FROM fa_retirements fr,
fa_transaction_headers fth,
fa_additions fa,
fa_books fb,
fa_book_controls_sec fbcs,
fa_distribution_history fdh,
fa_deprn_periods fdp
--gl_code_combinations gcc
WHERE (fth.transaction_type_code = 'FULL RETIREMENT' OR
fth.transaction_type_code = 'PARTIAL RETIREMENT')
AND fth.asset_id = fa.asset_id
AND fr.asset_id = fth.asset_id
AND fdh.asset_id = fa.asset_id
AND fa.asset_id = fb.asset_id
AND fb.transaction_header_id_out = fr.transaction_header_id_in
AND fa.asset_type = 'CAPITALIZED'
AND fth.book_type_code = fbcs.book_type_code
--AND fdh.book_type_code = fbcs.book_type_code
AND fth.book_type_code = fdp.book_type_code
AND (fbcs.book_class = 'CORPORATE' OR fbcs.book_class = 'TAX')
AND fbcs.gl_posting_allowed_flag = 'YES'
--AND fdh.code_combination_id = gcc.code_combination_id
AND fth.date_effective BETWEEN fdp.period_open_date AND
nvl(fdp.period_close_date, SYSDATE)
AND fth.book_type_code = fdp.book_type_code
AND fdh.date_effective < nvl(fdp.period_close_date, SYSDATE)
AND (fdh.date_ineffective > fdp.period_open_date OR
fdh.date_ineffective IS NULL)
--AND gcc.segment1 IN (SELECT segment_value
-- FROM gl_ledger_norm_seg_vals
-- WHERE legal_entity_id = ln_legal_entity_id
-- AND ledger_id = ln_ledger_id)
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 = ln_ledger_id
AND jclllbg.Legal_Entity_Id = ln_legal_entity_id)
AND fbcs.set_of_books_id = ln_ledger_id
AND greatest(greatest(fdp.calendar_period_open_date,
least(SYSDATE, fdp.calendar_period_close_date))) BETWEEN
ld_date_from AND ld_date_to
ORDER BY transaction_date, fth.transaction_header_id;
SELECT fr.date_retired,
fr.units,
fr.cost_retired / fb.cost retired_factor,
fr.cost_retired,
fr.proceeds_of_sale,
fr.cost_of_removal,
fds.period_counter,
fb.salvage_value,
(SELECT fbb.salvage_value
FROM fa_books fbb
WHERE fbb.asset_id = ln_asset_id
AND fbb.book_type_code = lv_book_type_code
AND fbb.transaction_header_id_in =
fb.transaction_header_id_out) salvage_before,
fds.deprn_reserve,
fds.deprn_amount,
fds.impairment_reserve,
fds.impairment_amount
FROM fa_retirements fr,
fa_books fb,
fa_books fbb,
fa_deprn_summary fds,
fa_deprn_periods fdp
WHERE fr.asset_id = ln_asset_id
AND fr.transaction_header_id_in = ln_transaction_header_id
AND fds.book_type_code = lv_book_type_code
AND fb.asset_id = fr.asset_id
AND fb.asset_id = fbb.asset_id
AND fds.asset_id = fr.asset_id
AND fb.book_type_code = fbb.book_type_code
AND fr.book_type_code = fb.book_type_code
AND fds.book_type_code = fr.book_type_code
AND fdp.book_type_code = fds.book_type_code
AND fb.transaction_header_id_out = fbb.transaction_header_id_out
AND fb.transaction_header_id_out = fr.transaction_header_id_in
AND fr.date_retired BETWEEN fdp.calendar_period_open_date AND
fdp.calendar_period_close_date
AND fds.period_counter = fdp.period_counter;
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 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 period_year = pv_accounting_year;
SELECT period_num
INTO lv_period_name
FROM gl_period_statuses gp
WHERE period_year = pv_accounting_year
AND application_id = 101
AND ledger_id = pn_ledger_id
AND gp.adjustment_period_flag = 'N'
AND v_row.transaction_date BETWEEN gp.start_date AND
gp.end_date;
SELECT max(fdp.period_close_date)
INTO ld_dp_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) <= v_row.transaction_date;
SELECT SUM(fdh.units_assigned)
INTO ln_total_unit
FROM fa_distribution_history fdh
WHERE fdh.asset_id = v_row.asset_id
AND fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
AND (fdh.date_ineffective > nvl(ld_dp_close_date, 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 = v_row.asset_id
AND fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
AND (fdh.date_ineffective > nvl(ld_dp_close_date, 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);
SELECT fds.deprn_reserve, fds.impairment_reserve
INTO ln_last_deprn_reserve, ln_last_impai_reserve
FROM fa_deprn_summary fds
WHERE fds.asset_id = v_row.asset_id
AND fds.book_type_code = v_row.book_type_code
AND fds.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE asset_id = v_row.asset_id
AND book_type_code = v_row.book_type_code
AND period_counter < v_retire_row.period_counter);