[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bc.book_type_code
FROM fa_books bk,
fa_book_controls bc
WHERE bc.distribution_source_book = l_book_type_code
AND bk.book_type_code = bc.book_type_code
AND bk.asset_id = l_asset_id
AND bk.transaction_header_id_out is null
AND bc.date_ineffective is null
ORDER BY bc.book_class,
bc.book_type_code;
SELECT p_psob_id AS sob_id,
1 AS index_id
FROM dual
UNION
SELECT set_of_books_id AS sob_id,
2 AS index_id
FROM fa_mc_book_controls
WHERE book_type_code = p_book_type_code
AND primary_set_of_books_id = p_psob_id
AND enabled_flag = 'Y'
ORDER BY 2;
select sum(units_assigned - nvl(transaction_units, 0))
into l_old_units
from fa_distribution_history
where asset_id = px_asset_hdr_rec.asset_id
and book_type_code = px_asset_hdr_rec.book_type_code
and date_ineffective IS NULL;
if not insert_txn_headers(l_trans_rec,
l_asset_hdr_rec
,p_log_level_rec => p_log_level_rec) then
raise error_found;
if not update_asset_history(l_trans_rec,
l_asset_hdr_rec,
px_asset_cat_rec_new,
l_asset_desc_rec_new
,p_log_level_rec => p_log_level_rec) then
raise error_found;
if not update_additions(l_trans_rec,
l_asset_hdr_rec,
px_asset_cat_rec_new,
l_asset_desc_rec_new
,p_log_level_rec => p_log_level_rec) then
raise error_found;
if not update_books(l_trans_rec,
l_asset_hdr_rec,
l_period_rec
,p_log_level_rec => p_log_level_rec) then
raise error_found;
if not update_dist_history(l_trans_rec,
l_asset_hdr_rec,
px_asset_dist_tbl
,p_log_level_rec => p_log_level_rec) then
raise error_found;
X_today => l_trans_rec.who_info.last_update_date,
X_old_cat_id => l_asset_cat_rec_old.category_id,
X_new_cat_id => px_asset_cat_rec_new.category_id,
X_asset_type => l_asset_type_rec_old.asset_type,
X_last_update_date => l_trans_rec.who_info.last_update_date,
X_last_updated_by => l_trans_rec.who_info.last_updated_by,
X_last_update_login =>l_trans_rec.who_info.last_update_login,
X_init_message_flag => 'NO'
,p_log_level_rec => p_log_level_rec) then
raise error_found;
select distribution_id,
units_assigned
from fa_distribution_history
where asset_id = l_asset_id
and nvl(assigned_to,-9999) = nvl(d_assigned_to,-9999)
and code_combination_id = d_expense_ccid
and location_id = d_location_ccid
and date_ineffective is null;
select units_assigned
from fa_distribution_history
where asset_id = l_asset_id
and distribution_id = d_distribution_id;
select group_asset_id
into l_group_asset_id
from fa_books
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and date_ineffective is null;
select count(*)
into l_count
from per_periods_of_service s, per_people_f p
where p.person_id = s.person_id
and trunc(sysdate) between
p.effective_start_date and p.effective_end_date
and nvl(s.actual_termination_date,sysdate) >= sysdate
and p.person_id = p_asset_dist_tbl(p_curr_index).assigned_to;
select current_units
into l_ad_units
from fa_additions
where asset_id = p_asset_hdr_rec.asset_id;
select units
into l_ah_units
from fa_asset_history
where asset_id = p_asset_hdr_rec.asset_id
and date_ineffective IS NULL;
select sum(units_assigned - nvl(transaction_units, 0))
into l_dh_units
from fa_distribution_history
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and date_ineffective IS NULL;
FUNCTION insert_txn_headers(px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
RETURN BOOLEAN IS
l_transaction_type_code px_trans_rec.transaction_type_code%TYPE;
FA_TRANSACTION_HEADERS_PKG.INSERT_ROW(
X_Rowid => l_rowid,
X_Transaction_Header_Id => l_txn_head_id,
X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Transaction_Type_Code => l_transaction_type_code,
X_Transaction_Date_Entered => px_trans_rec.transaction_date_entered,
X_Date_Effective => px_trans_rec.who_info.last_update_date,
X_Last_Update_Date => px_trans_rec.who_info.last_update_date,
X_Last_Updated_By => px_trans_rec.who_info.last_updated_by,
X_Transaction_Name => px_trans_rec.transaction_name,
X_Invoice_Transaction_Id => NULL,
X_Source_Transaction_Header_Id => px_trans_rec.source_transaction_header_id,
X_Mass_Reference_Id => px_trans_rec.mass_reference_id,
X_Last_Update_Login => px_trans_rec.who_info.last_update_login,
X_Transaction_Subtype => px_trans_rec.transaction_subtype,
X_Attribute1 => px_trans_rec.desc_flex.attribute1,
X_Attribute2 => px_trans_rec.desc_flex.attribute2,
X_Attribute3 => px_trans_rec.desc_flex.attribute3,
X_Attribute4 => px_trans_rec.desc_flex.attribute4,
X_Attribute5 => px_trans_rec.desc_flex.attribute5,
X_Attribute6 => px_trans_rec.desc_flex.attribute6,
X_Attribute7 => px_trans_rec.desc_flex.attribute7,
X_Attribute8 => px_trans_rec.desc_flex.attribute8,
X_Attribute9 => px_trans_rec.desc_flex.attribute9,
X_Attribute10 => px_trans_rec.desc_flex.attribute10,
X_Attribute11 => px_trans_rec.desc_flex.attribute11,
X_Attribute12 => px_trans_rec.desc_flex.attribute12,
X_Attribute13 => px_trans_rec.desc_flex.attribute13,
X_Attribute14 => px_trans_rec.desc_flex.attribute14,
X_Attribute15 => px_trans_rec.desc_flex.attribute15,
X_Attribute_Category_Code => px_trans_rec.desc_flex.attribute_category_code,
X_Transaction_Key => px_trans_rec.transaction_key,
X_Amortization_Start_Date => NULL,
X_Calling_Interface => px_trans_rec.calling_interface,
X_Mass_Transaction_ID => px_trans_rec.mass_transaction_id,
X_Return_status => l_success,
X_Event_ID => px_trans_rec.event_id,
X_calling_FN => 'FA_DISTRIBUTION_PVT.insert_txn_headers'
,p_log_level_rec => p_log_level_rec);
fa_srvr_msg.add_sql_error(calling_fn => 'FA_DISTRIBUTION_PVT.insert_txn_headers'
,p_log_level_rec => p_log_level_rec);
END insert_txn_headers;
FUNCTION update_asset_history(p_trans_rec IN FA_API_TYPES.trans_rec_type,
p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
p_asset_cat_rec_new IN FA_API_TYPES.asset_cat_rec_type,
p_asset_desc_rec_new IN FA_API_TYPES.asset_desc_rec_type,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
RETURN BOOLEAN IS
CURSOR ah_cur (l_asset_id in NUMBER) IS
select ah.rowid row_id,
asset_id,
category_id,
asset_type,
units,
date_effective,
date_ineffective,
transaction_header_id_in,
transaction_header_id_out,
last_update_date,
last_updated_by,
last_update_login
from fa_asset_history ah
where asset_id = l_asset_id
and date_ineffective is null;
select transaction_header_id_in
into l_txn_id
from fa_retirements
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and status = 'PENDING';
FA_ASSET_HISTORY_PKG.Update_Row
(X_Rowid => ah_rec.Row_Id,
X_Asset_Id => ah_rec.Asset_Id,
X_Category_Id => p_asset_cat_rec_new.category_id,
X_Asset_Type => ah_rec.Asset_Type,
X_Units => p_asset_desc_rec_new.current_units,
X_Date_Effective => ah_rec.Date_Effective,
X_Date_Ineffective => ah_rec.date_ineffective,
X_Transaction_Header_Id_In => ah_rec.Transaction_Header_Id_In,
X_Transaction_Header_Id_Out => ah_rec.transaction_header_id_out,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Return_Status => l_success,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history'
,p_log_level_rec => p_log_level_rec);
FA_ASSET_HISTORY_PKG.Update_Row
(X_Rowid => ah_rec.Row_Id,
X_Asset_Id => ah_rec.Asset_Id,
X_Category_Id => ah_rec.Category_Id,
X_Asset_Type => ah_rec.Asset_Type,
X_Units => ah_rec.Units,
X_Date_Effective => ah_rec.Date_Effective,
X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
X_Transaction_Header_Id_In => ah_rec.Transaction_Header_Id_In,
X_Transaction_Header_Id_Out => l_txn_id,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Return_Status => l_success,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history'
,p_log_level_rec => p_log_level_rec);
FA_ASSET_HISTORY_PKG.Insert_Row
(X_Rowid => l_rowid,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Category_Id => p_asset_cat_rec_new.category_id,
X_Asset_Type => ah_rec.asset_type,
X_Units => p_asset_desc_rec_new.current_units,
X_Date_Effective => p_trans_rec.who_info.last_update_date,
X_Date_Ineffective => NULL,
X_Transaction_Header_Id_In => l_txn_id,
X_Transaction_Header_Id_Out => NULL,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Return_Status => l_success,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history'
,p_log_level_rec => p_log_level_rec);
fa_srvr_msg.add_sql_error(calling_fn => 'FA_DISTRIBUTION_PVT.update_asset_history'
,p_log_level_rec => p_log_level_rec);
END update_asset_history;
FUNCTION update_additions(p_trans_rec IN FA_API_TYPES.trans_rec_type,
p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
p_asset_cat_rec_new IN FA_API_TYPES.asset_cat_rec_type,
p_asset_desc_rec_new IN FA_API_TYPES.asset_desc_rec_type,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
RETURN BOOLEAN IS
BEGIN
if (p_trans_rec.transaction_type_code in ('UNIT ADJUSTMENT','TRANSFER OUT')) then
update fa_additions_b
set current_units = p_asset_desc_rec_new.current_units,
last_update_date = p_trans_rec.who_info.last_update_date,
last_update_login = p_trans_rec.who_info.last_update_login,
last_updated_by = p_trans_rec.who_info.last_updated_by
where asset_id = p_asset_hdr_rec.asset_id;
update fa_additions_b
set asset_category_id = p_asset_cat_rec_new.category_id,
property_type_code = fa_cache_pkg.fazcat_record.property_type_code,
property_1245_1250_code = fa_cache_pkg.fazcat_record.property_1245_1250_code,
owned_leased = fa_cache_pkg.fazcat_record.owned_leased,
attribute1 = p_asset_cat_rec_new.desc_flex.attribute1,
attribute2 = p_asset_cat_rec_new.desc_flex.attribute2,
attribute3 = p_asset_cat_rec_new.desc_flex.attribute3,
attribute4 = p_asset_cat_rec_new.desc_flex.attribute4,
attribute5 = p_asset_cat_rec_new.desc_flex.attribute5,
attribute6 = p_asset_cat_rec_new.desc_flex.attribute6,
attribute7 = p_asset_cat_rec_new.desc_flex.attribute7,
attribute8 = p_asset_cat_rec_new.desc_flex.attribute8,
attribute9 = p_asset_cat_rec_new.desc_flex.attribute9,
attribute10 = p_asset_cat_rec_new.desc_flex.attribute10,
attribute11 = p_asset_cat_rec_new.desc_flex.attribute11,
attribute12 = p_asset_cat_rec_new.desc_flex.attribute12,
attribute13 = p_asset_cat_rec_new.desc_flex.attribute13,
attribute14 = p_asset_cat_rec_new.desc_flex.attribute14,
attribute15 = p_asset_cat_rec_new.desc_flex.attribute15,
attribute16 = p_asset_cat_rec_new.desc_flex.attribute16,
attribute17 = p_asset_cat_rec_new.desc_flex.attribute17,
attribute18 = p_asset_cat_rec_new.desc_flex.attribute18,
attribute19 = p_asset_cat_rec_new.desc_flex.attribute19,
attribute20 = p_asset_cat_rec_new.desc_flex.attribute20,
attribute21 = p_asset_cat_rec_new.desc_flex.attribute21,
attribute22 = p_asset_cat_rec_new.desc_flex.attribute22,
attribute23 = p_asset_cat_rec_new.desc_flex.attribute23,
attribute24 = p_asset_cat_rec_new.desc_flex.attribute24,
attribute25 = p_asset_cat_rec_new.desc_flex.attribute25,
attribute26 = p_asset_cat_rec_new.desc_flex.attribute26,
attribute27 = p_asset_cat_rec_new.desc_flex.attribute27,
attribute28 = p_asset_cat_rec_new.desc_flex.attribute28,
attribute29 = p_asset_cat_rec_new.desc_flex.attribute29,
attribute30 = p_asset_cat_rec_new.desc_flex.attribute30,
attribute_category_code = p_asset_cat_rec_new.desc_flex.attribute_category_code,
context = p_asset_cat_rec_new.desc_flex.context,
last_update_date = p_trans_rec.who_info.last_update_date,
last_update_login = p_trans_rec.who_info.last_update_login,
last_updated_by = p_trans_rec.who_info.last_updated_by
where asset_id = p_asset_hdr_rec.asset_id;
update fa_additions_b
set unit_adjustment_flag = 'NO'
where asset_id = p_asset_hdr_rec.asset_id
and unit_adjustment_flag = 'YES';
fa_srvr_msg.add_sql_error(calling_fn => 'FA_DISTRIBUTION_PVT.update_additions'
,p_log_level_rec => p_log_level_rec);
END update_additions;
FUNCTION update_books(p_trans_rec IN FA_API_TYPES.trans_rec_type,
p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
p_period_rec IN FA_API_TYPES.period_rec_type,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
RETURN BOOLEAN IS
l_cal_period_close_date DATE;
update fa_books fabk
set adjustment_required_status =
decode(l_allow_backdated_transfers,
'N',adjustment_required_status,
'TFR'),
annual_deprn_rounding_flag =
decode(l_allow_backdated_transfers,
'N', annual_deprn_rounding_flag,
'TFR')
where fabk.asset_id = p_asset_hdr_rec.asset_id
and fabk.book_type_code = p_asset_hdr_rec.book_type_code
and fabk.transaction_header_id_out is NULL;
update fa_mc_books fabk
set adjustment_required_status =
decode(l_allow_backdated_transfers,
'N',adjustment_required_status,
'TFR'),
annual_deprn_rounding_flag =
decode(l_allow_backdated_transfers,
'N', annual_deprn_rounding_flag,
'TFR')
where fabk.asset_id = p_asset_hdr_rec.asset_id
and fabk.book_type_code = p_asset_hdr_rec.book_type_code
and fabk.transaction_header_id_out is NULL;
fa_srvr_msg.add_sql_error(calling_fn => 'FA_DISTRIBUTION_PVT.update_books'
,p_log_level_rec => p_log_level_rec);
END update_books;
FUNCTION update_dist_history(p_trans_rec IN FA_API_TYPES.trans_rec_type,
p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
p_asset_dist_tbl IN FA_API_TYPES.asset_dist_tbl_type,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
RETURN BOOLEAN IS
CURSOR dh_cur (l_asset_id IN NUMBER, l_distribution_id IN NUMBER) IS
select dh.rowid row_id, dh.*
from fa_distribution_history dh
where asset_id = l_asset_id
and distribution_id = l_distribution_id
and date_ineffective is null;
select transaction_header_id_in
into l_Book_Header_Id
from fa_books
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and date_ineffective is null;
select retirement_id
into l_retirement_id
from fa_retirements
where asset_id = p_asset_hdr_rec.asset_id
and book_type_code = p_asset_hdr_rec.book_type_code
and status = 'PENDING';
FA_DISTRIBUTION_HISTORY_PKG.UPDATE_ROW
(X_Rowid => dh_rec.row_id,
X_Distribution_Id => dh_rec.distribution_id,
X_Book_Type_Code => dh_rec.book_type_code,
X_Asset_Id => dh_rec.asset_id,
X_Units_Assigned => dh_rec.units_assigned,
X_Date_Effective => dh_rec.date_effective,
X_Code_Combination_Id => dh_rec.code_combination_id,
X_Location_Id => dh_rec.location_id,
X_Transaction_Header_Id_In => dh_rec.transaction_header_id_in,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
X_Assigned_To => dh_rec.assigned_to,
X_Transaction_Header_Id_Out => p_trans_rec.transaction_header_id,
X_Transaction_Units => p_asset_dist_tbl(i).transaction_units,
X_Retirement_Id => l_ret_id,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history');
FA_DISTRIBUTION_HISTORY_PKG.INSERT_ROW
(X_Rowid => l_rowid,
X_Distribution_Id => l_distribution_id,
X_Book_Type_Code => dh_rec.book_type_code,
X_Asset_Id => dh_rec.asset_id,
X_Units_Assigned => p_asset_dist_tbl(i).units_assigned +
p_asset_dist_tbl(i).transaction_units,
X_Date_Effective => p_trans_rec.who_info.last_update_date,
X_Code_Combination_Id => dh_rec.code_combination_id,
X_Location_Id => dh_rec.location_id,
X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Date_Ineffective => NULL,
X_Assigned_To => dh_rec.assigned_to,
X_Transaction_Header_Id_Out => NULL,
X_Transaction_Units => NULL,
X_Retirement_Id => NULL,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history');
FA_DISTRIBUTION_HISTORY_PKG.UPDATE_ROW
(X_Rowid => dh_rec.row_id,
X_Distribution_Id => dh_rec.distribution_id,
X_Book_Type_Code => dh_rec.book_type_code,
X_Asset_Id => dh_rec.asset_id,
X_Units_Assigned => dh_rec.units_assigned,
X_Date_Effective => dh_rec.date_effective,
X_Code_Combination_id => dh_rec.code_combination_id,
X_Location_Id => dh_rec.location_id,
X_Transaction_Header_Id_In => dh_rec.transaction_header_id_in,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
X_Assigned_To => dh_rec.assigned_to,
X_Transaction_Header_Id_Out =>p_trans_rec.transaction_header_id,
X_Transaction_Units => p_asset_dist_tbl(i).transaction_units,
X_Retirement_Id => l_ret_id,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history');
FA_DISTRIBUTION_HISTORY_PKG.INSERT_ROW
(X_Rowid => l_rowid,
X_Distribution_Id => l_distribution_id,
X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
X_Asset_Id => p_asset_hdr_rec.asset_id,
X_Units_Assigned => p_asset_dist_tbl(i).transaction_units,
X_Date_Effective => p_trans_rec.who_info.last_update_date,
X_Code_Combination_Id => p_asset_dist_tbl(i).expense_ccid,
X_Location_Id => p_asset_dist_tbl(i).location_ccid,
X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
X_Date_Ineffective => NULL,
X_Assigned_To => p_asset_dist_tbl(i).assigned_to,
X_Transaction_Header_Id_Out => NULL,
X_Transaction_Units => NULL,
X_Retirement_Id => NULL,
X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history');
FA_TRANSFER_DETAILS_PKG.INSERT_ROW
(X_Rowid => l_rowid,
X_Transfer_Header_Id => p_trans_rec.transaction_header_id,
X_Distribution_Id => l_tfr_det_dist_id,
X_Book_Header_Id => l_book_header_id,
X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history'
,p_log_level_rec => p_log_level_rec);
fa_srvr_msg.add_sql_error(calling_fn => 'FA_DISTRIBUTION_PVT.update_dist_history'
,p_log_level_rec => p_log_level_rec);
END update_dist_history;