[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 = 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, -- Bug 6964615 start
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 -- Bug 6964615 start
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,
ad.asset_category_id,
bk.prorate_convention_code,
bk.deprn_method_code,
bk.life_in_months,
bk.bonus_rule,
bk.basic_rate,
bk.adjusted_rate,
bk.production_capacity,
bk.unit_of_measure,
bk.book_type_code,
gad.asset_number,
bk.salvage_type, -- Bug 6964615 start
bk.percent_salvage_value,
bk.salvage_value,
bk.deprn_limit_type,
bk.allowed_deprn_limit,
bk.allowed_deprn_limit_amount -- Bug 6964615 end
FROM fa_books bk,
fa_additions ad,
fa_additions_b gad,
fa_mass_changes mch
WHERE mch.mass_change_id = mc_rec.mass_change_id
AND ad.asset_type = nvl(mch.asset_type, ad.asset_type)
AND ad.asset_type <> 'CIP'
AND ad.asset_number >= nvl(mch.from_asset_number, ad.asset_number)
AND ad.asset_number <= nvl(mch.to_asset_number, ad.asset_number)
AND ad.asset_category_id = nvl(mch.category_id, ad.asset_category_id)
AND bk.book_type_code = mch.book_type_code
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.
AND bk.period_counter_fully_retired IS NULL
and nvl(bk.period_counter_fully_reserved,99) =
nvl(bk.period_counter_life_complete,99)
and nvl(bk.period_counter_fully_reserved, -1) =
decode(mch.change_fully_rsvd_assets, 'YES',
nvl(bk.period_counter_fully_reserved, -1), -1)
and bk.date_placed_in_service >=
nvl(mch.from_date_placed_in_service,
bk.date_placed_in_service)
and bk.date_placed_in_service <=
nvl(mch.to_date_placed_in_service,
bk.date_placed_in_service)
and bk.deprn_method_code =
nvl(mch.from_method_code,
bk.deprn_method_code)
and nvl(bk.life_in_months, -1) =
nvl(mch.from_life_in_months,
nvl(bk.life_in_months, -1))
and nvl(bk.basic_rate, -1) =
nvl(mch.from_basic_rate,
nvl(bk.basic_rate, -1))
and nvl(bk.adjusted_rate, -1) =
nvl(mch.from_adjusted_rate,
nvl(bk.adjusted_rate, -1))
and nvl(bk.production_capacity, -1) =
nvl(mch.from_production_capacity,
nvl(bk.production_capacity, -1))
and nvl(bk.unit_of_measure, -1) =
nvl(mch.from_uom,
nvl(bk.unit_of_measure, -1))
and bk.prorate_convention_code =
nvl(mch.from_convention,
bk.prorate_convention_code)
and nvl(bk.bonus_rule, -1) =
nvl(mch.from_bonus_rule,
nvl(bk.bonus_rule,-1))
and ((mch.from_group_association is null) or
(mch.from_group_association = 'STANDALONE' and
bk.group_asset_id is null) or
(mch.from_group_association = 'MEMBER' and
nvl(bk.group_asset_id, -99) = mch.from_group_asset_id))
AND bk.group_asset_id = gad.asset_id(+)
-- Bug 6964615
and nvl(bk.salvage_type, 'XX') =
nvl(mch.from_salvage_type,
nvl(bk.salvage_type, 'XX'))
and nvl(bk.salvage_value, -1) =
nvl(mch.from_salvage_value,
nvl(bk.salvage_value, -1))
and nvl(bk.percent_salvage_value, -1) =
nvl(mch.from_percent_salvage_value/100,
nvl(bk.percent_salvage_value, -1))
and nvl(bk.deprn_limit_type, 'XX') =
nvl(mch.from_deprn_limit_type,
nvl(bk.deprn_limit_type, 'XX'))
and nvl(bk.allowed_deprn_limit_amount, -1) =
nvl(mch.from_deprn_limit_amount,
nvl(bk.allowed_deprn_limit_amount, -1))
and nvl(bk.allowed_deprn_limit, -1) =
nvl(mch.from_deprn_limit/100,
nvl(bk.allowed_deprn_limit, -1))
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.
=========================================================================*/
if (g_print_debug) then
fa_debug_pkg.add('FARX_MCP.Preview_Change',
'before deleting rows from itf table',
'');
DELETE FROM fa_mass_changes_itf
WHERE request_id = h_request_id;
Validate assets and insert preview records into the interface table.
=========================================================================*/
-- Get category flex structure.
OPEN get_cat_flex_struct;
'Preview - inserting asset into itf-table at 200 loop',
a_tbl(a_index).asset_id );
FA_MASS_CHG_UTILS_PKG.Insert_Itf(
X_Report_Type => 'PREVIEW',
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
);
a_tbl.delete;
'asset inserted',
a_tbl(i).asset_id);
'book inserted',
a_tbl(i).book_type_code);
FA_MASS_CHG_UTILS_PKG.Insert_Itf(
X_Report_Type => 'PREVIEW',
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
);
a_tbl.delete;
Update the status of the mass change to 'PREVIEWED'
(This step is now handled in SRS report(FASRCPVW.rdf), which is fired
after the RX report request.)
=========================================================================*/
/*
UPDATE fa_mass_changes
SET status = 'PREVIEWED'
WHERE mass_change_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_changes
SET status = 'FAILED_PRE'
WHERE mass_change_id = X_Mass_Change_Id;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_changes_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_changes
SET status = 'FAILED_PRE'
WHERE mass_change_id = X_Mass_Change_Id;
/* Delete rows inserted into the interface table. */
DELETE FROM fa_mass_changes_itf
WHERE request_id = h_request_id;
SELECT category_flex_structure
FROM fa_system_controls;
SELECT prorate_convention_code,
deprn_method_code,
life_in_months,
basic_rate,
adjusted_rate,
bonus_rule,
production_capacity,
unit_of_measure,
ad.asset_number
FROM FA_BOOKS bk,
FA_ADDITIONS_B ad
WHERE bk.asset_id = a_tbl(a_index).asset_id
AND bk.book_type_code = h_book_type_code
AND bk.date_ineffective IS NULL
AND bk.group_asset_id = ad.asset_id(+);