[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,
fa.tag_number,
fth.transaction_name,
fth.transaction_key,
fth.book_type_code
FROM 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 IN
('ADDITION', 'ADJUSTMENT', 'RECLASS', 'REINSTATEMENT',
'REVALUATION', 'TRANSFER', 'UNIT ADJUSTMENT',
'RESERVE ADJUSTMENT')
AND fth.asset_id = fa.asset_id
AND fdh.asset_id = fa.asset_id
AND fb.asset_id = fa.asset_id
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 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 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 fixed #bug 10365688
--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 current_cost
INTO lv_after(1)
FROM fa_transaction_history_books_v
WHERE (key = v_row.transaction_header_id)
AND transaction_type = 'Addition';
SELECT SUM(fdh.units_assigned)
INTO ln_total_unit_before
FROM fa_distribution_history fdh
WHERE fdh.asset_id = v_row.asset_id
AND fdh.transaction_header_id_in < v_row.transaction_header_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_before
FROM fa_distribution_history fdh
WHERE fdh.asset_id = v_row.asset_id
AND fdh.transaction_header_id_in < v_row.transaction_header_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 units
INTO lv_before(1)
FROM (
SELECT fahv.units units
FROM fa_asset_history_v fahv, fa_categories_b fcb
WHERE (fahv.key = v_row.transaction_header_id)
AND fahv.category_id = fcb.category_id
ORDER BY fahv.transaction_header_id_in)
WHERE ROWNUM = 1;
SELECT units
INTO lv_after(1)
FROM (
SELECT fahv.units units
FROM fa_asset_history_v fahv, fa_categories_b fcb
WHERE (fahv.key = v_row.transaction_header_id)
AND fahv.category_id = fcb.category_id
ORDER BY fahv.transaction_header_id_in DESC)
WHERE ROWNUM = 1;
SELECT category
INTO lv_before(1)
FROM (
SELECT fcb.concatenated_segments category
FROM fa_asset_history_v fahv, fa_categories_b_kfv fcb
WHERE (fahv.key = v_row.transaction_header_id)
AND fahv.category_id = fcb.category_id
ORDER BY fahv.transaction_header_id_in)
WHERE ROWNUM = 1;
SELECT category
INTO lv_after(1)
FROM (
SELECT fcb.concatenated_segments category
FROM fa_asset_history_v fahv, fa_categories_b_kfv fcb
WHERE (fahv.key = v_row.transaction_header_id)
AND fahv.category_id = fcb.category_id
ORDER BY fahv.transaction_header_id_in DESC)
WHERE ROWNUM = 1;
SELECT concatenated_segment_delimiter
INTO lv_delimiter
FROM fnd_id_flex_structures
WHERE id_flex_code = 'CAT#';
SELECT cost_retired
INTO lv_after(1)
FROM fa_retirements_v
WHERE 'REINSTATEMENT' = 'REINSTATEMENT'
AND transaction_header_id_out = v_row.transaction_header_id;
SELECT current_cost
INTO lv_before(1)
FROM (
SELECT current_cost
FROM fa_transaction_history_books_v
WHERE key = v_row.transaction_header_id
ORDER BY transaction_header_id_in)
WHERE ROWNUM = 1;
SELECT current_cost
INTO lv_after(1)
FROM (
SELECT current_cost
FROM fa_transaction_history_books_v
WHERE key = v_row.transaction_header_id
ORDER BY transaction_header_id_in DESC)
WHERE ROWNUM = 1;
SELECT trans_units,
full_name,
location,
ccid
INTO lv_before(1), lv_before(2), lv_before(3), lv_before(4)
FROM (
SELECT fdi.trans_units,
pp.full_name,
fl.concatenated_segments location,
gcc.concatenated_segments ccid
FROM fa_distribution_inquiry_v fdi,
gl_code_combinations_kfv gcc,
per_people_x pp,
fa_locations_kfv fl
WHERE (transaction_header_id_key = v_row.transaction_header_id)
AND fdi.assigned_to = pp.person_id(+)
AND fdi.location_id = fl.location_id(+)
AND fdi.code_combination_id = gcc.code_combination_id(+)
ORDER BY transaction_header_id)
WHERE ROWNUM = 1;
SELECT trans_units,
full_name,
location,
ccid
INTO lv_after(1), lv_after(2), lv_after(3), lv_after(4)
FROM (
SELECT fdi.trans_units,
pp.full_name,
fl.concatenated_segments location,
gcc.concatenated_segments ccid
FROM fa_distribution_inquiry_v fdi,
gl_code_combinations_kfv gcc,
per_people_x pp,
fa_locations_kfv fl
WHERE (transaction_header_id_key = v_row.transaction_header_id)
AND fdi.assigned_to = pp.person_id(+)
AND fdi.location_id = fl.location_id(+)
AND fdi.code_combination_id = gcc.code_combination_id(+)
ORDER BY transaction_header_id DESC)
WHERE ROWNUM = 1;
SELECT current_cost,
salvage_value,
revaluation_ceiling,
deprn_method_code,
life,
adjusted_rate,
production_capacity,
unit_of_measure,
prorate_convention_code,
date_placed_in_service,
depreciate_flag,
bonus_rule,
ceiling_name
INTO lv_before(1), lv_before(2), lv_before(3), lv_before(4),
lv_before(5), lv_before(6), lv_before(7), lv_before(8),
lv_before(9), lv_before(10), lv_before(11), lv_before(12),
lv_before(13)
FROM (
SELECT current_cost,
salvage_value,
revaluation_ceiling,
deprn_method_code,
life,
adjusted_rate,
production_capacity,
unit_of_measure,
prorate_convention_code,
date_placed_in_service,
depreciate_flag,
bonus_rule,
ceiling_name
FROM fa_transaction_history_books_v
WHERE (key = v_row.transaction_header_id)
ORDER BY transaction_header_id_in)
WHERE ROWNUM = 1;
SELECT current_cost,
salvage_value,
revaluation_ceiling,
deprn_method_code,
life,
adjusted_rate,
production_capacity,
unit_of_measure,
prorate_convention_code,
date_placed_in_service,
depreciate_flag,
bonus_rule,
ceiling_name
INTO lv_after(1), lv_after(2), lv_after(3), lv_after(4),
lv_after(5), lv_after(6), lv_after(7), lv_after(8),
lv_after(9), lv_after(10), lv_after(11), lv_after(12),
lv_after(13)
FROM (
SELECT current_cost,
salvage_value,
revaluation_ceiling,
deprn_method_code,
life,
adjusted_rate,
production_capacity,
unit_of_measure,
prorate_convention_code,
date_placed_in_service,
depreciate_flag,
bonus_rule,
ceiling_name
FROM fa_transaction_history_books_v
WHERE (key = v_row.transaction_header_id)
ORDER BY transaction_header_id_in DESC)
WHERE ROWNUM = 1;
SELECT fds1.impairment_reserve,
fds.impairment_reserve
INTO lv_before(1), lv_after(1)
FROM fa_deprn_summary fds,
fa_deprn_summary fds1,
fa_deprn_periods fdp
WHERE fds.asset_id = v_row.asset_id and fds1.asset_id = v_row.asset_id
AND fdp.book_type_code = v_row.book_type_code
AND fdp.book_type_code = fds.book_type_code
AND fdp.book_type_code = fds1.book_type_code
AND v_row.transaction_date BETWEEN fdp.calendar_period_open_date AND
fdp.calendar_period_close_date
AND fds.period_counter = fdp.period_counter
AND fds1.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 < fdp.period_counter);
SELECT adjustment_amount
INTO lv_after(1)
FROM fa_financial_inquiry_upl_v
WHERE (transaction_header_id = v_row.transaction_header_id)
AND ROWNUM = 1;