[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lu_rev.meaning,
lu_curr.meaning
FROM fa_lookups lu_rev,
fa_lookups lu_curr
WHERE lu_rev.lookup_type = 'MASS_TRX_STATUS' AND
lu_rev.lookup_code = 'COMPLETED'
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,
bk.book_type_code,
ah.category_id,
NULL, -- category in concatenated format.
bk.prorate_convention_code,
bk.ceiling_name,
bk.deprn_method_code,
bk.life_in_months,
NULL, -- in converted format.
bk.basic_rate,
NULL, -- in converted format.
bk.adjusted_rate,
NULL, -- in converted format.
bk.bonus_rule,
bk.production_capacity,
bk.unit_of_measure,
bk.depreciate_flag,
bk.allowed_deprn_limit,
NULL,
bk.allowed_deprn_limit_amount,
bk.percent_salvage_value,
NULL,
-- for cost account
decode(ah.asset_type, 'CIP', cb.wip_cost_account_ccid,
cb.asset_cost_account_ccid),
NULL,
-- for reserve account
decode(ah.asset_type, 'CIP', NULL, cb.reserve_account_ccid),
NULL
FROM fa_category_books cb,
fa_book_controls bc,
fa_books bk,
fa_asset_history ah,
fa_additions ad,
fa_transaction_headers th
/* mr_rec.conc_request_id will correspond to the request id
for the last time the transaction was "run" by the mass
reclass program.
BMR: this is no longer true - see BUG# 2371326
for rerunnability we will show all assets/trxs
WHERE th.mass_reference_id = mr_rec.conc_request_id
*/
WHERE th.mass_transaction_id = mr_rec.mass_reclass_id
AND ad.asset_id = th.asset_id
AND ah.asset_id = th.asset_id
-- transaction_type_code = 'RECLASS' if transaction is after the period
-- the asset was added.
-- use transaction_header_id comparison, since there could be more than
-- one transaction in the period the asset is added.
--AND ((th.transaction_type_code = 'RECLASS' AND
-- ah.transaction_header_id_in = th.transaction_header_id) OR
-- (th.transaction_type_code <> 'RECLASS' AND
-- ah.transaction_header_id_in < th.transaction_header_id AND
-- nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
-- th.transaction_header_id))
AND ah.transaction_header_id_in =
decode(th.transaction_type_code, 'RECLASS',
th.transaction_header_id, ah.transaction_header_id_in)
AND ah.transaction_header_id_in <= th.transaction_header_id
AND nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
th.transaction_header_id
-- Only corporate book is stored in fa_transaction_headers in case
-- of basic reclass only(without redefault.)
AND bk.asset_id = th.asset_id
AND bk.book_type_code = bc.book_type_code
AND bc.book_class IN ('CORPORATE', 'TAX')
AND bc.distribution_source_book = th.book_type_code
-- Get the book row at the time of reclass run. Need to figure out
-- the book row by comparing transaction_header_id's, since only
-- basic reclass transaction is recorded in fa_transaction_headers
-- table, when redefault is not performed.
AND bk.transaction_header_id_in < th.transaction_header_id
AND nvl(bk.transaction_header_id_out, th.transaction_header_id + 1) >
th.transaction_header_id
AND cb.category_id = mr_rec.to_category_id
AND cb.book_type_code = bk.book_type_code
ORDER BY ad.asset_number, bk.book_type_code;
SELECT ad.asset_id,
ad.asset_number,
ad.description,
bk.book_type_code,
ah.category_id,
-- for cost account
decode(ah.asset_type, 'CIP', cb.wip_cost_account_ccid,
cb.asset_cost_account_ccid),
-- for reserve account
decode(ah.asset_type, 'CIP', NULL, cb.reserve_account_ccid),
th.transaction_header_id
FROM fa_category_books cb,
fa_book_controls bc,
fa_books bk,
fa_asset_history ah,
fa_additions ad,
fa_transaction_headers th
/* mr_rec.conc_request_id will correspond to the request id
for the last time the transaction was "run" by the mass
reclass program.
BMR: this is no longer true - see BUG# 2371326
for rerunnability we will show all assets/trxs
WHERE th.mass_reference_id = mr_rec.conc_request_id
*/
WHERE th.mass_transaction_id = mr_rec.mass_reclass_id
-- there are two transactions, 'RECLASS' and 'ADJUSTMENT'
AND ((th.transaction_type_code||'' = 'RECLASS') OR
(th.transaction_subtype = 'RECLASS'))
AND ad.asset_id = th.asset_id
AND ah.asset_id = th.asset_id
-- transaction_type_code = 'RECLASS' if transaction is after the period
-- the asset was added.
-- use transaction_header_id comparison, since there could be more than
-- one transaction in the period the asset is added.
--AND ((th.transaction_type_code = 'RECLASS' AND
-- ah.transaction_header_id_in = th.transaction_header_id) OR
-- (th.transaction_type_code <> 'RECLASS' AND
-- ah.transaction_header_id_in < th.transaction_header_id AND
-- nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
-- th.transaction_header_id))
AND ah.transaction_header_id_in =
decode(th.transaction_type_code, 'RECLASS',
th.transaction_header_id, ah.transaction_header_id_in)
AND ah.transaction_header_id_in <= th.transaction_header_id
AND nvl(ah.transaction_header_id_out, th.transaction_header_id + 1) >
th.transaction_header_id
AND bk.asset_id = th.asset_id
AND bk.book_type_code = bc.book_type_code
AND bc.book_class IN ('CORPORATE', 'TAX')
AND bc.distribution_source_book = th.book_type_code
-- to select only one book row per book. selects all the currently
-- effective books.
AND bk.date_ineffective IS NULL
AND cb.category_id = mr_rec.to_category_id
AND cb.book_type_code = bk.book_type_code
ORDER BY ad.asset_number, bk.book_type_code;
SELECT bk.book_type_code,
bk.prorate_convention_code,
bk.ceiling_name,
bk.deprn_method_code,
bk.life_in_months,
bk.basic_rate,
bk.adjusted_rate,
bk.bonus_rule,
bk.production_capacity,
bk.unit_of_measure,
bk.depreciate_flag,
bk.allowed_deprn_limit,
bk.allowed_deprn_limit_amount,
bk.percent_salvage_value
FROM fa_books bk
WHERE bk.asset_id = a_tbl(a_index).asset_id
AND bk.book_type_code = a_tbl(a_index).book_type_code
-- Get the book row at the time of reclass run. Need to figure out
-- the book row by comparing transaction_header_id's, since only
-- basic reclass transaction is recorded in fa_transaction_headers
-- table, when redefault is not performed or when rules remain the same.
AND bk.transaction_header_id_in < h_rcl_thid
AND nvl(bk.transaction_header_id_out, h_rcl_thid + 1) > h_rcl_thid;
SELECT transaction_header_id
FROM fa_transaction_headers
-- BMR: BUG# 2371326
-- for rerunnability we will show all assets/trxs
-- WHERE mass_reference_id = mr_rec.conc_request_id
WHERE mass_transaction_id = mr_rec.mass_reclass_id
AND asset_id = a_tbl(a_index).asset_id
AND book_type_code = a_tbl(a_index).book_type_code
AND transaction_type_code||'' IN
('ADDITION', 'CIP ADDITION', 'ADJUSTMENT', 'CIP ADJUSTMENT','GROUP ADDITION','GROUP ADJUSTMENT');
SELECT bk.book_type_code,
bk.prorate_convention_code,
bk.ceiling_name,
bk.deprn_method_code,
bk.life_in_months,
bk.basic_rate,
bk.adjusted_rate,
bk.bonus_rule,
bk.production_capacity,
bk.unit_of_measure,
bk.depreciate_flag,
bk.allowed_deprn_limit,
bk.allowed_deprn_limit_amount,
bk.percent_salvage_value
FROM fa_books bk
WHERE bk.asset_id = a_tbl(a_index).asset_id
AND bk.book_type_code = a_tbl(a_index).book_type_code
AND bk.transaction_header_id_in = h_adj_thid;
SELECT accounting_flex_structure
FROM fa_book_controls
WHERE book_type_code = a_tbl(a_index).book_type_code;
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;
Insert review records into the interface table.
===========================================================================*/
/* Get category flex structure. */
OPEN get_cat_flex_struct;
/* Fetch asset-book pairs from mass_reclass_assets cursor and insert them
into the interface table, fa_mass_reclass_itf. */
IF (mr_rec.redefault_flag = 'NO') THEN
OPEN mass_reclass_assets1;
-- Update last asset processed and the asset count.
IF (a_tbl(a_index).asset_id <> h_last_asset OR
h_last_asset IS NULL) THEN
h_last_asset := a_tbl(a_index).asset_id;
/* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
at every 200 assets and re-initialize the counter and the asset table. */
-- If the 200th asset belongs to more than one book, only the information
-- for the first book of this asset will be inserted into the table.
-- The rest will be taken care of in the next insertion.
IF (g_asset_count = h_commit_level) THEN
FOR i IN 1 .. a_index LOOP
FA_MASS_REC_UTILS_PKG.Insert_Itf(
X_Report_Type => 'REVIEW',
X_Request_Id => h_request_id,
X_Mass_Reclass_Id => X_Mass_Reclass_Id,
X_Asset_Rec => a_tbl(i),
X_New_Category => NULL,
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;
-- insertion.
h_last_asset := NULL;
-- Update last asset processed and the asset count.
IF (a_tbl(a_index).asset_id <> h_last_asset OR
h_last_asset IS NULL) THEN
h_last_asset := a_tbl(a_index).asset_id;
/* Insert asset records into the interface table, FA_MASS_RECLASS_ITF,
at every 200 assets and re-initialize the counter and the asset table. */
-- If the 200th asset belongs to more than one book, only the information
-- for the first book of this asset will be inserted into the table.
-- The rest will be taken care of in the next insertion.
IF (g_asset_count = h_commit_level) THEN
FOR i IN 1 .. a_index LOOP
FA_MASS_REC_UTILS_PKG.Insert_Itf(
X_Report_Type => 'REVIEW',
X_Request_Id => h_request_id,
X_Mass_Reclass_Id => X_Mass_Reclass_Id,
X_Asset_Rec => a_tbl(i),
X_New_Category => NULL,
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;
-- insertion.
h_last_asset := NULL;
/* Insert the remaining 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 => 'REVIEW',
X_Request_Id => h_request_id,
X_Mass_Reclass_Id => X_Mass_Reclass_Id,
X_Asset_Rec => a_tbl(i),
X_New_Category => NULL,
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;
a_tbl.delete;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_reclass_itf
WHERE request_id = h_request_id;
a_tbl.delete;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_reclass_itf
WHERE request_id = h_request_id;