[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 ('INTER', 'ADJUSTMENT'
)
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;
select b.source_line_id
from fa_asset_invoices b
where b.source_line_id in (
select a.source_line_id
from fa_asset_invoices a
start with a.source_Line_id = p_src_line_id
connect by prior a.source_line_id = a.prior_source_line_id
and prior a.asset_id = a.asset_id
)
and b.date_ineffective is null;
l_last_update_login number(15) := fnd_global.login_id;
l_src_trans_rec.who_info.last_update_date := l_creation_date;
l_src_trans_rec.who_info.last_updated_by := l_created_by;
l_src_trans_rec.who_info.last_update_login := l_last_update_login;
l_dest_trans_rec.who_info.last_update_date := l_creation_date;
l_dest_trans_rec.who_info.last_updated_by := l_created_by;
l_dest_trans_rec.who_info.last_update_login := l_last_update_login;
l_inv_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,
bks1.group_asset_id, -- from_group_asset_id
bks2.group_asset_id -- to_group_asset_id
from fa_books bks1,
fa_books bks2,
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 ('INTER', 'ADJUSTMENT'
)
and tfr.mass_external_transfer_id > l_max_mass_ext_transfer_id
and bks1.book_type_code = p_book_type_code
and bks1.asset_id = tfr.from_asset_id
and bks1.date_ineffective is null
and bks2.book_type_code = p_book_type_code
and bks2.asset_id = tfr.to_asset_id
and bks2.date_ineffective is null
order by tfr.mass_external_transfer_id;
select tfr.mass_external_transfer_id,
tfr.book_type_code,
tfr.batch_name,
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.from_group_asset_id,
tfr.to_group_asset_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 ('INTER', 'ADJUSTMENT'
)
and tfr.worker_id is null;
update fa_mass_external_transfers
set from_group_asset_id = l_from_group_asset_id_tbl(i),
to_group_asset_id = l_to_group_asset_id_tbl(i)
where mass_external_transfer_id = l_mass_ext_transfer_id_tbl(i);
update fa_mass_external_transfers
set worker_id = l_worker_id
where mass_external_transfer_id = l_mass_external_transfer_id;
update fa_mass_external_transfers tfr
set tfr.worker_id = l_worker_id
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 ('INTER', 'ADJUSTMENT'
)
and tfr.from_group_asset_id = l_dep_group_asset_id(i)
and tfr.worker_id is null
returning tfr.to_group_asset_id, tfr.to_asset_id bulk collect
into l_sub_to_group_asset_id, l_sub_to_asset_id;
update fa_mass_external_transfers tfr
set tfr.worker_id = l_worker_id
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 ('INTER', 'ADJUSTMENT'
)
and tfr.to_group_asset_id = l_dep_group_asset_id(i)
and tfr.worker_id is null
returning tfr.from_group_asset_id,tfr.from_asset_id bulk collect
into l_sub_from_group_asset_id, l_sub_from_asset_id;
update fa_mass_external_transfers tfr
set tfr.worker_id = l_worker_id
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 ('INTER', 'ADJUSTMENT'
)
and tfr.from_asset_id = l_dep_asset_id(i)
and tfr.worker_id is null
returning tfr.to_group_asset_id, tfr.to_asset_id bulk collect
into l_sub_to_group_asset_id, l_sub_to_asset_id;
update fa_mass_external_transfers tfr
set tfr.worker_id = l_worker_id
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 ('INTER', 'ADJUSTMENT'
)
and tfr.to_asset_id = l_dep_asset_id(i)
and tfr.worker_id is null
returning tfr.from_group_asset_id,tfr.from_asset_id bulk collect
into l_sub_from_group_asset_id, l_sub_from_asset_id;
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) in
(p_from_asset_id, p_to_asset_id
))
)
);
select Mass_External_Transfer_ID
from fa_mass_external_transfers
where transaction_status in ('DELETE','POSTED')
for update nowait;
Delete from fa_mass_external_transfers
where mass_external_transfer_id = LV_Mass_External_Transfer_ID;