[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select tfr.mass_external_transfer_id,
bc.set_of_books_id,
tfr.book_type_code,
tfr.batch_name,
tfr.external_reference_num,
tfr.transaction_reference_num,
tfr.transaction_type,
tfr.from_asset_id,
tfr.to_asset_id,
tfr.transaction_status,
tfr.transaction_date_entered,
tfr.from_distribution_id,
tfr.from_location_id,
tfr.from_gl_ccid,
tfr.from_employee_id,
tfr.to_distribution_id,
tfr.to_location_id,
tfr.to_gl_ccid,
tfr.to_employee_id,
tfr.description,
tfr.transfer_units,
tfr.transfer_amount,
tfr.source_line_id,
tfr.post_batch_id,
tfr.attribute1,
tfr.attribute2,
tfr.attribute3,
tfr.attribute4,
tfr.attribute5,
tfr.attribute6,
tfr.attribute7,
tfr.attribute8,
tfr.attribute9,
tfr.attribute10,
tfr.attribute11,
tfr.attribute12,
tfr.attribute13,
tfr.attribute14,
tfr.attribute15,
tfr.attribute_category_code
from fa_mass_external_transfers tfr,
fa_book_controls bc
where tfr.book_type_code = p_book_type_code
and tfr.book_type_code = bc.book_type_code
and tfr.batch_name = p_batch_name
and tfr.transaction_status = 'POST'
and tfr.transaction_type in ('INTRA','TRANSFER'
)
and tfr.mass_external_transfer_id > px_max_mass_ext_transfer_id
and nvl(tfr.worker_id, 1) = p_request_number
order by tfr.mass_external_transfer_id;
l_last_update_login number(15) := fnd_global.login_id;
select distinct distribution_id
into l_from_distribution_id(i)
from fa_distribution_history
where book_type_code = l_book_type_code(i)
and asset_id = l_from_asset_id(i)
and code_combination_id = l_from_gl_ccid(i)
and location_id = l_from_location_id(i)
and nvl(assigned_to, -999) = nvl(l_from_employee_id(i), -999)
and date_ineffective is null;
l_trans_rec.who_info.last_update_date := l_creation_date;
l_trans_rec.who_info.last_updated_by := l_created_by;
l_trans_rec.who_info.last_update_login := l_last_update_login;
l_asset_dist_tbl.delete;
update fa_mass_external_transfers
set transaction_status = l_transaction_status(i)
where mass_external_transfer_id = l_mass_external_transfer_id(i);
update fa_mass_external_transfers
set transaction_status = l_transaction_status(i)
where mass_external_transfer_id = l_mass_external_transfer_id(i);
select tfr.mass_external_transfer_id,
tfr.book_type_code,
tfr.batch_name,
tfr.from_group_asset_id,
tfr.from_asset_id,
tfr.to_asset_id,
tfr.transaction_status,
tfr.transaction_date_entered,
tfr.from_distribution_id,
tfr.from_location_id,
tfr.from_gl_ccid,
tfr.from_employee_id,
tfr.to_distribution_id,
tfr.to_location_id,
tfr.to_gl_ccid,
tfr.to_employee_id,
tfr.source_line_id,
tfr.worker_id
from fa_mass_external_transfers tfr
where tfr.book_type_code = p_book_type_code
and tfr.batch_name = p_batch_name
and tfr.transaction_status = 'POST'
and tfr.transaction_type in ('INTRA', 'TRANSFER'
)
and tfr.worker_id is null;
update fa_mass_external_transfers
set worker_id = l_worker_id(i)
where mass_external_transfer_id = l_mass_external_transfer_id(i);
select nvl(b.period_counter_fully_retired,0)
from fa_books b
where b.asset_id = p_from_asset_id
and b.date_ineffective is null
and b.book_type_code = p_book_type_code;
select 1
from dual
where exists
( select 'x'
from fa_mass_update_batch_headers a
where a.status_code IN ('P', 'E', 'R', 'N', 'IP'
)
and a.book_type_code = p_book_type_code
and (a.event_code IN ('CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
'HR_MASS_TRANSFER', 'CHANGE_CATEGORY_LIFE',
'CHANGE_CATEGORY_LIFE_END_DATE'
) or
(a.event_code IN ('CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE',
'CHANGE_ASSET_CATEGORY'
) and
to_number(a.source_entity_key_value) = p_from_asset_id)
)
);
select asset_id,
book_type_code,
date_ineffective,
units_assigned,
code_combination_id,
location_id,
assigned_to
into l_from_asset_id,
l_book_type_code,
l_from_date_ineffective,
l_from_units_assigned,
l_from_gl_ccid,
l_from_location_id,
l_from_employee_id
from fa_distribution_history
where distribution_id = p_from_distribution_id;
select count(*)
into l_to_gl_ccid_exists
from gl_code_combinations
where code_combination_id = p_to_gl_ccid
and enabled_flag = 'Y'
and nvl(start_date_active, sysdate) <= sysdate
and nvl(end_date_active, sysdate + 1) > sysdate ;
select count(*)
into l_to_location_id_exists
from fa_locations
where location_id = p_to_location_id
and enabled_flag = 'Y'
and nvl(start_date_active, sysdate) <= sysdate
and nvl(end_date_active, sysdate + 1) > sysdate ;
select count(*)
into l_to_employee_id_exists
from per_periods_of_service s,
per_people_f p
where p.person_id = p_to_employee_id
and p.person_id = s.person_id
and trunc(sysdate) between p.effective_start_date
and p.effective_end_date
and s.actual_termination_date is null;
select count(*)
into l_check_prior_period
from fa_transaction_headers th,
fa_deprn_periods fadp
where th.asset_id = p_from_asset_id
and th.book_type_Code = p_book_type_code
and th.transaction_type_code = 'TRANSFER'
and th.transaction_date_entered < fadp.calendar_period_open_date
and th.date_effective > fadp.period_open_date
and p_transaction_date_entered < fadp.calendar_period_open_date
and fadp.book_type_Code = p_book_type_code
and fadp.period_close_date is null;