[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lu_prev.meaning,
lu_curr.meaning
FROM fa_lookups lu_prev,
fa_lookups lu_curr
WHERE lu_prev.lookup_type = 'MASS_TRX_STATUS' AND
lu_prev.lookup_code = 'PREVIEW'
AND lu_curr.lookup_type = 'MASS_TRX_STATUS' AND
lu_curr.lookup_code = mr_rec.status;
SELECT category_flex_structure
FROM fa_system_controls;
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 = X_Mass_Reclass_Id;
SELECT ad.asset_id,
ad.asset_number,
ad.description,
ad.asset_category_id
FROM gl_code_combinations gc,
fa_distribution_history dh,
fa_book_controls bc,
fa_books bk,
fa_additions 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
-- 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))
-- 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
ORDER BY ad.asset_number;
a_tbl.delete;
Delete rows previously inserted into the interface table with the same
request id, if there is any.
=========================================================================*/
DELETE FROM fa_mass_reclass_itf
WHERE request_id = h_request_id;
Validate assets and insert preview records into the interface table.
=========================================================================*/
IF (mr_rec.redefault_flag = 'YES') THEN
-- Depreciation rules will be redefaulted.
-- Reset g_deprn_count before loading the cache table.
FA_LOAD_TBL_PKG.g_deprn_count := 0;
/* Loop all the qualified assets, and insert all the validated assets
into the interface table, fa_mass_reclass_itf. */
OPEN mass_reclass_assets;
-- (At every 200 assets, the records stored in a_tbl, will be inserted
-- into the interface table.)
Store_Results(X_Get_New_Rules => 'NO',
X_Cat_Flex_Struct => h_cat_flex_struct);
/* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
at every 200 assets. */
-- If g_asset_count(number of valid assets) = 200, insert all the 200
-- asset records in a_tbl(1..a_index) into the interface table,
-- re-initialize the pl/sql table, a_tbl, and reset g_asset_count
-- and a_index to 0. Commit changes at every 200 assets as well.
IF (g_asset_count = h_commit_level) THEN
FOR i IN 1 .. a_index LOOP
if (g_print_debug) then
fa_debug_pkg.add('FARX_RP.Preview_Reclass',
'Preview - inserting asset into itf-table',
a_tbl(a_index).asset_id );
FA_MASS_REC_UTILS_PKG.Insert_Itf(
X_Report_Type => 'PREVIEW',
X_Request_Id => h_request_id,
X_Mass_Reclass_Id => X_Mass_Reclass_Id,
X_Asset_Rec => a_tbl(i),
X_New_Category => h_new_concat_cat,
X_Last_Update_Date => mr_rec.last_update_date,
X_Last_Updated_By => mr_rec.last_updated_by,
X_Created_By => mr_rec.created_by,
X_Creation_Date => mr_rec.creation_date,
X_Last_Update_Login => mr_rec.last_update_login
);
a_tbl.delete;
/* Insert the remaining valid asset records into the interface table. */
-- Up to a_index - 1, to account for the extra increment taken for a_index
-- when no more rows were found in the cursor loop.
FOR i IN 1 .. (a_index - 1) LOOP
FA_MASS_REC_UTILS_PKG.Insert_Itf(
X_Report_Type => 'PREVIEW',
X_Request_Id => h_request_id,
X_Mass_Reclass_Id => X_Mass_Reclass_Id,
X_Asset_Rec => a_tbl(i),
X_New_Category => h_new_concat_cat,
X_Last_Update_Date => mr_rec.last_update_date,
X_Last_Updated_By => mr_rec.last_updated_by,
X_Created_By => mr_rec.created_by,
X_Creation_Date => mr_rec.creation_date,
X_Last_Update_Login => mr_rec.last_update_login
);
a_tbl.delete;
Update the status of the mass reclass to 'PREVIEWED'
(This step is now handled in SRS report(FASRCPVW.rdf), which is fired
after the RX report request.)
=========================================================================*/
/*
UPDATE fa_mass_reclass
SET status = 'PREVIEWED'
WHERE mass_reclass_id = X_Mass_Reclass_Id
AND status = 'PREVIEW';
a_tbl.delete;
/* A fatal error has occurred. Update status to 'FAILED_PRE'. */
ROLLBACK WORK;
UPDATE fa_mass_reclass
SET status = 'FAILED_PRE'
WHERE mass_reclass_id = X_Mass_Reclass_Id;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_reclass_itf
WHERE request_id = h_request_id;
a_tbl.delete;
/* A fatal error has occurred. Update status to 'FAILED_PRE'. */
ROLLBACK WORK;
UPDATE fa_mass_reclass
SET status = 'FAILED_PRE'
WHERE mass_reclass_id = X_Mass_Reclass_Id;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_reclass_itf
WHERE request_id = h_request_id;
SELECT bk.book_type_code
FROM fa_book_controls bc, fa_books bk
WHERE bk.asset_id = a_tbl(a_index).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
ORDER BY bk.book_type_code;
SELECT TH.book_type_code
FROM FA_BOOK_CONTROLS BC,
FA_TRANSACTION_HEADERS TH
WHERE TH.transaction_type_code||'' IN ('ADDITION','CIP ADDITION')
AND TH.asset_id = a_tbl(a_index).asset_id
AND BC.book_type_code = TH.book_type_code
AND nvl(BC.date_ineffective, sysdate + 1) > sysdate
GROUP BY TH.book_type_code
ORDER BY MIN(TH.date_effective);
SELECT category_flex_structure
FROM fa_system_controls;
SELECT date_placed_in_service, depreciate_flag
FROM FA_BOOKS
WHERE asset_id = a_tbl(a_index).asset_id
AND book_type_code = h_book_type_code
AND date_ineffective IS NULL;
SELECT prorate_convention_code, ceiling_name, deprn_method_code,
life_in_months, basic_rate, adjusted_rate,
bonus_rule, production_capacity, unit_of_measure,
depreciate_flag, allowed_deprn_limit, allowed_deprn_limit_amount,
percent_salvage_value
FROM FA_BOOKS
WHERE asset_id = a_tbl(a_index).asset_id
AND book_type_code = h_book_type_code
AND date_ineffective IS NULL;
SELECT 'Y'
FROM FA_DEPRN_PERIODS dp,
FA_TRANSACTION_HEADERS th
WHERE th.book_type_code = X_Book_Type_Code
AND th.asset_id = X_Asset_Id
AND th.transaction_type_code||'' in ('ADDITION', 'CIP ADDITION')
AND dp.book_type_code = X_Book_Type_Code
AND dp.period_open_date <= th.date_effective
AND nvl(dp.period_close_date, sysdate) > th.date_effective
AND dp.period_close_date IS NULL;
SELECT greatest(dp.calendar_period_open_date,
least(trunc(sysdate), dp.calendar_period_close_date))
FROM FA_DEPRN_PERIODS dp
WHERE dp.book_type_code = X_Book_Type_Code
AND dp.period_close_date is null;
SELECT date_placed_in_service
FROM FA_BOOKS bk
WHERE bk.asset_id = X_Asset_Id
AND bk.book_type_code = X_Book_Type_Code
AND date_ineffective IS NULL;