[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mr.mass_reclass_id,
mr.book_type_code,
mr.transaction_date_entered,
mr.concurrent_request_id,
mr.status,
mr.asset_type,
mr.location_id,
mr.employee_id,
mr.asset_key_id,
mr.from_cost,
mr.to_cost,
mr.from_asset_number,
mr.to_asset_number,
mr.from_date_placed_in_service,
mr.to_date_placed_in_service,
mr.from_category_id,
mr.to_category_id,
mr.segment1_low, mr.segment2_low, mr.segment3_low, mr.segment4_low,
mr.segment5_low, mr.segment6_low, mr.segment7_low, mr.segment8_low,
mr.segment9_low, mr.segment10_low, mr.segment11_low, mr.segment12_low,
mr.segment13_low, mr.segment14_low, mr.segment15_low, mr.segment16_low,
mr.segment17_low, mr.segment18_low, mr.segment19_low, mr.segment20_low,
mr.segment21_low, mr.segment22_low, mr.segment23_low, mr.segment24_low,
mr.segment25_low, mr.segment26_low, mr.segment27_low, mr.segment28_low,
mr.segment29_low, mr.segment30_low,
mr.segment1_high, mr.segment2_high, mr.segment3_high, mr.segment4_high,
mr.segment5_high, mr.segment6_high, mr.segment7_high, mr.segment8_high,
mr.segment9_high, mr.segment10_high, mr.segment11_high, mr.segment12_high,
mr.segment13_high, mr.segment14_high, mr.segment15_high, mr.segment16_high,
mr.segment17_high, mr.segment18_high, mr.segment19_high, mr.segment20_high,
mr.segment21_high, mr.segment22_high, mr.segment23_high, mr.segment24_high,
mr.segment25_high, mr.segment26_high, mr.segment27_high, mr.segment28_high,
mr.segment29_high, mr.segment30_high,
mr.include_fully_rsvd_flag,
mr.copy_cat_desc_flag,
mr.inherit_deprn_rules_flag,
mr.amortize_flag,
mr.created_by,
mr.creation_date,
mr.last_updated_by,
mr.last_update_login,
mr.last_update_date
FROM fa_mass_reclass mr
WHERE mass_reclass_id = p_mass_reclass_Id;
SELECT ad.asset_id,
ad.asset_number,
ad.asset_category_id,
dh.assigned_to
FROM gl_code_combinations gc,
fa_distribution_history dh,
fa_book_controls bc,
fa_books bk,
fa_additions_b ad
WHERE ad.asset_type = nvl(mr_rec.asset_type, ad.asset_type)
AND ad.asset_number >= nvl(mr_rec.from_asset_number, ad.asset_number)
AND ad.asset_number <= nvl(mr_rec.to_asset_number, ad.asset_number)
AND nvl(ad.asset_key_ccid, -9999) = nvl(mr_rec.asset_key_id,
nvl(ad.asset_key_ccid, -9999))
AND ad.asset_category_id = nvl(mr_rec.from_category_id, ad.asset_category_id)
AND bk.book_type_code = mr_rec.book_type_code
AND bk.book_type_code = bc.book_type_code
-- corp book should be currently effective.
AND nvl(bc.date_ineffective, sysdate+1) > sysdate
AND bk.asset_id = ad.asset_id
AND nvl(bk.disabled_flag, 'N') = 'N' --HH
AND bk.date_ineffective IS NULL -- pick the most recent row.
-- dpis, exp acct, employee, location, cost range: selection criteria
-- for corporate book only.
AND bk.date_placed_in_service >= nvl(mr_rec.from_dpis,
bk.date_placed_in_service)
AND bk.date_placed_in_service <= nvl(mr_rec.to_dpis,
bk.date_placed_in_service)
AND bk.cost >= nvl(mr_rec.from_cost, bk.cost)
AND bk.cost <= nvl(mr_rec.to_cost, bk.cost)
AND dh.asset_id = ad.asset_id
AND nvl(dh.assigned_to, -9999) = nvl(mr_rec.employee_id, nvl(dh.assigned_to, -9999))
AND dh.location_id = nvl(mr_rec.location_id, dh.location_id)
AND dh.date_ineffective IS NULL -- pick only the active distributions.
AND dh.code_combination_id = gc.code_combination_id
-- more check is done on retired asset in reclass validation engine.
-- more check is done on reserved asset in Check_Criteria function.
AND bk.period_counter_fully_retired IS NULL
-- cannot avoid the use of OR, since gc.segment1 can be null.
-- cannot use nvl(gc.segment1, 'NULL') for comparison, since
-- the value 'NULL' may fall between the range accidentally.
-- may break the OR to UNION later.
-- rule-based optimizer transforms OR to UNION ALL automatically
-- when it sees it being more efficient. since the columns
-- in OR are not indexed, transforming to UNION ALL has
-- no gain in performance and using OR is unavoidable here
-- for the correctness of the program.
AND ((gc.segment1 between nvl(mr_rec.segment1_low, gc.segment1)
and nvl(mr_rec.segment1_high, gc.segment1)) OR
(mr_rec.segment1_low IS NULL and mr_rec.segment1_high IS NULL))
AND ((gc.segment2 between nvl(mr_rec.segment2_low, gc.segment2)
and nvl(mr_rec.segment2_high, gc.segment2)) OR
(mr_rec.segment2_low IS NULL and mr_rec.segment2_high IS NULL))
AND ((gc.segment3 between nvl(mr_rec.segment3_low, gc.segment3)
and nvl(mr_rec.segment3_high, gc.segment3)) OR
(mr_rec.segment3_low IS NULL and mr_rec.segment3_high IS NULL))
AND ((gc.segment4 between nvl(mr_rec.segment4_low, gc.segment4)
and nvl(mr_rec.segment4_high, gc.segment4)) OR
(mr_rec.segment4_low IS NULL and mr_rec.segment4_high IS NULL))
AND ((gc.segment5 between nvl(mr_rec.segment5_low, gc.segment5)
and nvl(mr_rec.segment5_high, gc.segment5)) OR
(mr_rec.segment5_low IS NULL and mr_rec.segment5_high IS NULL))
AND ((gc.segment6 between nvl(mr_rec.segment6_low, gc.segment6)
and nvl(mr_rec.segment6_high, gc.segment6)) OR
(mr_rec.segment6_low IS NULL and mr_rec.segment6_high IS NULL))
AND ((gc.segment7 between nvl(mr_rec.segment7_low, gc.segment7)
and nvl(mr_rec.segment7_high, gc.segment7)) OR
(mr_rec.segment7_low IS NULL and mr_rec.segment7_high IS NULL))
AND ((gc.segment8 between nvl(mr_rec.segment8_low, gc.segment8)
and nvl(mr_rec.segment8_high, gc.segment8)) OR
(mr_rec.segment8_low IS NULL and mr_rec.segment8_high IS NULL))
AND ((gc.segment9 between nvl(mr_rec.segment9_low, gc.segment9)
and nvl(mr_rec.segment9_high, gc.segment9)) OR
(mr_rec.segment9_low IS NULL and mr_rec.segment9_high IS NULL))
AND ((gc.segment10 between nvl(mr_rec.segment10_low, gc.segment10)
and nvl(mr_rec.segment10_high, gc.segment10)) OR
(mr_rec.segment10_low IS NULL and mr_rec.segment10_high IS NULL))
AND ((gc.segment11 between nvl(mr_rec.segment11_low, gc.segment11)
and nvl(mr_rec.segment11_high, gc.segment11)) OR
(mr_rec.segment11_low IS NULL and mr_rec.segment11_high IS NULL))
AND ((gc.segment12 between nvl(mr_rec.segment12_low, gc.segment12)
and nvl(mr_rec.segment12_high, gc.segment12)) OR
(mr_rec.segment12_low IS NULL and mr_rec.segment12_high IS NULL))
AND ((gc.segment13 between nvl(mr_rec.segment13_low, gc.segment13)
and nvl(mr_rec.segment13_high, gc.segment13)) OR
(mr_rec.segment13_low IS NULL and mr_rec.segment13_high IS NULL))
AND ((gc.segment14 between nvl(mr_rec.segment14_low, gc.segment14)
and nvl(mr_rec.segment14_high, gc.segment14)) OR
(mr_rec.segment14_low IS NULL and mr_rec.segment14_high IS NULL))
AND ((gc.segment15 between nvl(mr_rec.segment15_low, gc.segment15)
and nvl(mr_rec.segment15_high, gc.segment15)) OR
(mr_rec.segment15_low IS NULL and mr_rec.segment15_high IS NULL))
AND ((gc.segment16 between nvl(mr_rec.segment16_low, gc.segment16)
and nvl(mr_rec.segment16_high, gc.segment16)) OR
(mr_rec.segment16_low IS NULL and mr_rec.segment16_high IS NULL))
AND ((gc.segment17 between nvl(mr_rec.segment17_low, gc.segment17)
and nvl(mr_rec.segment17_high, gc.segment17)) OR
(mr_rec.segment17_low IS NULL and mr_rec.segment17_high IS NULL))
AND ((gc.segment18 between nvl(mr_rec.segment18_low, gc.segment18)
and nvl(mr_rec.segment18_high, gc.segment18)) OR
(mr_rec.segment18_low IS NULL and mr_rec.segment18_high IS NULL))
AND ((gc.segment19 between nvl(mr_rec.segment19_low, gc.segment19)
and nvl(mr_rec.segment19_high, gc.segment19)) OR
(mr_rec.segment19_low IS NULL and mr_rec.segment19_high IS NULL))
AND ((gc.segment20 between nvl(mr_rec.segment20_low, gc.segment20)
and nvl(mr_rec.segment20_high, gc.segment20)) OR
(mr_rec.segment20_low IS NULL and mr_rec.segment20_high IS NULL))
AND ((gc.segment21 between nvl(mr_rec.segment21_low, gc.segment21)
and nvl(mr_rec.segment21_high, gc.segment21)) OR
(mr_rec.segment21_low IS NULL and mr_rec.segment21_high IS NULL))
AND ((gc.segment22 between nvl(mr_rec.segment22_low, gc.segment22)
and nvl(mr_rec.segment22_high, gc.segment22)) OR
(mr_rec.segment22_low IS NULL and mr_rec.segment22_high IS NULL))
AND ((gc.segment23 between nvl(mr_rec.segment23_low, gc.segment23)
and nvl(mr_rec.segment23_high, gc.segment23)) OR
(mr_rec.segment23_low IS NULL and mr_rec.segment23_high IS NULL))
AND ((gc.segment24 between nvl(mr_rec.segment24_low, gc.segment24)
and nvl(mr_rec.segment24_high, gc.segment24)) OR
(mr_rec.segment24_low IS NULL and mr_rec.segment24_high IS NULL))
AND ((gc.segment25 between nvl(mr_rec.segment25_low, gc.segment25)
and nvl(mr_rec.segment25_high, gc.segment25)) OR
(mr_rec.segment25_low IS NULL and mr_rec.segment25_high IS NULL))
AND ((gc.segment26 between nvl(mr_rec.segment26_low, gc.segment26)
and nvl(mr_rec.segment26_high, gc.segment26)) OR
(mr_rec.segment26_low IS NULL and mr_rec.segment26_high IS NULL))
AND ((gc.segment27 between nvl(mr_rec.segment27_low, gc.segment27)
and nvl(mr_rec.segment27_high, gc.segment27)) OR
(mr_rec.segment27_low IS NULL and mr_rec.segment27_high IS NULL))
AND ((gc.segment28 between nvl(mr_rec.segment28_low, gc.segment28)
and nvl(mr_rec.segment28_high, gc.segment28)) OR
(mr_rec.segment28_low IS NULL and mr_rec.segment28_high IS NULL))
AND ((gc.segment29 between nvl(mr_rec.segment29_low, gc.segment29)
and nvl(mr_rec.segment29_high, gc.segment29)) OR
(mr_rec.segment29_low IS NULL and mr_rec.segment29_high IS NULL))
AND ((gc.segment30 between nvl(mr_rec.segment30_low, gc.segment30)
and nvl(mr_rec.segment30_high, gc.segment30)) OR
(mr_rec.segment30_low IS NULL and mr_rec.segment30_high IS NULL))
AND ad.asset_id > px_max_asset_id
AND MOD(ad.asset_id, p_total_requests) = (p_request_number - 1)
ORDER BY ad.asset_id;
l_trans_rec.who_info.last_update_date := sysdate;
SELECT calendar_period_open_date,
calendar_period_close_date
FROM fa_deprn_periods
WHERE book_type_code = X_Corp_Book
AND period_close_date IS NULL;
SELECT bk.book_type_code
FROM fa_book_controls bc, fa_books bk
WHERE bk.asset_id = X_Asset_Id
AND bk.date_ineffective IS NULL
AND bk.book_type_code = bc.book_type_code
AND bc.book_class IN ('CORPORATE', 'TAX')
AND nvl(bc.date_ineffective, sysdate+1) > sysdate;
SELECT 'NOT RESERVED'
FROM fa_books bk
WHERE bk.asset_id = X_Asset_Id
AND bk.book_type_code = l_book
AND bk.date_ineffective IS NULL
AND bk.period_counter_fully_reserved IS NULL;
SELECT 'RESERVED'
FROM fa_books bk
WHERE bk.asset_id = X_Asset_Id
AND bk.book_type_code = l_book
AND bk.date_ineffective IS NULL
AND bk.period_counter_fully_reserved IS NOT NULL;