[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 = mc_rec.status;
SELECT category_flex_structure
FROM fa_system_controls;
SELECT mc.mass_change_id,
mc.book_type_code,
mc.transaction_date_entered,
mc.concurrent_request_id,
mc.status,
mc.asset_type,
mc.category_id,
mc.from_asset_number,
mc.to_asset_number,
mc.from_date_placed_in_service,
mc.to_date_placed_in_service,
mc.from_convention,
mc.to_convention,
mc.from_method_code,
mc.to_method_code,
mc.from_life_in_months,
mc.to_life_in_months,
mc.from_bonus_rule,
mc.to_bonus_rule,
mc.date_effective,
mc.from_basic_rate,
mc.to_basic_rate,
mc.from_adjusted_rate,
mc.to_adjusted_rate,
mc.from_production_capacity,
mc.to_production_capacity,
mc.from_uom,
mc.to_uom,
mc.from_group_association,
mc.to_group_association,
mc.from_group_asset_id,
mc.to_group_asset_id,
gad1.asset_number,
gad2.asset_number,
mc.change_fully_rsvd_assets,
mc.amortize_flag,
mc.created_by,
mc.creation_date,
mc.last_updated_by,
mc.last_update_login,
mc.last_update_date,
mc.from_salvage_type,
mc.to_salvage_type,
mc.from_percent_salvage_value,
mc.to_percent_salvage_value,
mc.from_salvage_value,
mc.to_salvage_value,
mc.from_deprn_limit_type,
mc.to_deprn_limit_type,
mc.from_deprn_limit,
mc.to_deprn_limit,
mc.from_deprn_limit_amount,
mc.to_deprn_limit_amount
FROM fa_mass_changes mc,
fa_additions_b gad1,
fa_additions_b gad2
WHERE mass_change_id = X_Mass_Change_Id
AND mc.from_group_asset_id = gad1.asset_id(+)
AND mc.to_group_asset_id = gad2.asset_id(+);
SELECT ad.asset_id,
ad.asset_number,
ad.description,
ad.asset_type,
bk1.book_type_code,
ad.asset_category_id,
NULL,
bk1.prorate_convention_code,
bk2.prorate_convention_code,
bk1.deprn_method_code,
bk2.deprn_method_code,
bk1.life_in_months,
bk2.life_in_months,
NULL,
NULL,
bk1.basic_rate,
bk2.basic_rate,
NULL,
NULL,
bk1.adjusted_rate,
bk2.adjusted_rate,
NULL,
NULL,
bk1.bonus_rule,
bk2.bonus_rule,
bk1.production_capacity,
bk2.production_capacity,
bk1.unit_of_measure,
bk2.unit_of_measure,
gad1.asset_number,
gad2.asset_number,
bk1.salvage_type,
bk2.salvage_type,
bk1.percent_salvage_value,
bk2.percent_salvage_value,
bk1.salvage_value,
bk2.salvage_value,
bk1.deprn_limit_type,
bk2.deprn_limit_type,
bk1.allowed_deprn_limit,
bk2.allowed_deprn_limit,
bk1.allowed_deprn_limit_amount,
bk2.allowed_deprn_limit_amount
FROM fa_books bk1,
fa_books bk2,
fa_additions ad,
fa_additions_b gad1,
fa_additions_b gad2,
fa_transaction_headers th
WHERE th.mass_transaction_id = mc_rec.mass_change_id
AND th.member_transaction_header_id is null -- exclude the spawned adjustments on groups
AND ad.asset_id = th.asset_id
AND bk1.asset_id = th.asset_id
AND bk2.asset_id = th.asset_id
AND bk1.book_type_code = th.book_type_code
AND bk2.book_type_code = th.book_type_code
AND bk1.transaction_header_id_out = th.transaction_header_id
AND bk2.transaction_header_id_in = th.transaction_header_id
AND bk1.group_asset_id = gad1.asset_id(+)
AND bk2.group_asset_id = gad2.asset_id(+)
ORDER BY ad.asset_number;
a_tbl.delete;
mc_rec.last_updated_by,
mc_rec.last_update_login,
mc_rec.last_update_date,
mc_rec.from_salvage_type,
mc_rec.to_salvage_type,
mc_rec.from_percent_salvage_value,
mc_rec.to_percent_salvage_value,
mc_rec.from_salvage_value,
mc_rec.to_salvage_value,
mc_rec.from_deprn_limit_type,
mc_rec.to_deprn_limit_type,
mc_rec.from_deprn_limit,
mc_rec.to_deprn_limit,
mc_rec.from_deprn_limit_amount,
mc_rec.to_deprn_limit_amount;
Delete rows previously inserted into the interface table with the same
request id, if there is any.
===========================================================================*/
DELETE FROM fa_mass_changes_itf
WHERE request_id = h_request_id;
Insert review records into the interface table.
===========================================================================*/
if g_print_debug then
fa_debug_pkg.add('FARX_CR.Review_Change',
'getting cat structure',
'');
/* Insert asset records into the interface table, FA_MASS_CHANGES_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_CHG_UTILS_PKG.Insert_Itf(
X_Report_Type => 'REVIEW',
X_Request_Id => h_request_id,
X_Mass_Change_Id => X_Mass_Change_Id,
X_Asset_Rec => a_tbl(i),
X_Last_Update_Date => mc_rec.last_update_date,
X_Last_Updated_By => mc_rec.last_updated_by,
X_Created_By => mc_rec.created_by,
X_Creation_Date => mc_rec.creation_date,
X_Last_Update_Login => mc_rec.last_update_login,
p_log_level_rec => null
);
a_tbl.delete;
/* 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_CHG_UTILS_PKG.Insert_Itf(
X_Report_Type => 'REVIEW',
X_Request_Id => h_request_id,
X_Mass_Change_Id => X_Mass_Change_Id,
X_Asset_Rec => a_tbl(i),
X_Last_Update_Date => mc_rec.last_update_date,
X_Last_Updated_By => mc_rec.last_updated_by,
X_Created_By => mc_rec.created_by,
X_Creation_Date => mc_rec.creation_date,
X_Last_Update_Login => mc_rec.last_update_login,
p_log_level_rec => null
);
a_tbl.delete;
a_tbl.delete;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_changes_itf
WHERE request_id = h_request_id;
a_tbl.delete;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_changes_itf
WHERE request_id = h_request_id;