[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_to_itf (
p_book in varchar2,
p_event_name in varchar2,
p_maint_date_from in date,
p_maint_date_to in date,
p_asset_number_from in varchar2,
p_asset_number_to in varchar2,
p_dpis_from in date,
p_dpis_to in date,
p_cat_id in number,
p_request_id in number,
retcode out nocopy varchar2,
errbuf out nocopy varchar2) is
h_cat_structure number;
select distinct me.asset_event_id,ad.asset_id,ad.asset_number,ad.description,ad.serial_number,
ad.tag_number, ad.manufacturer_name,ad.model_number,ad.asset_category_id,
ad.asset_key_ccid, dh.location_id location_id,
bk.cost asset_cost,bk.book_type_code,me.event_name,me.cost maint_cost,
me.maintenance_date,emp1.full_name warr_contact_name,emp1.employee_number warr_contact_number,
po.vendor_name,po.segment1 vendor_number, war.warranty_number,war.description war_desc,
war.start_date,war.end_date,emp2.full_name maint_contact_name,
emp2.employee_number maint_contact_number
from fa_additions ad,
fa_distribution_history dh,
po_vendors po,
per_people_x emp1,
per_people_x emp2,
fa_warranties war,
fa_add_warranties adw,
fa_books bk,
fa_maint_events me
where me.book_type_code = p_book and
me.event_name = nvl(p_event_name, me.event_name) and
me.maintenance_date between
nvl(p_maint_date_from,me.maintenance_date) and
nvl(p_maint_date_to,me.maintenance_date) and
me.asset_id = ad.asset_id and
ad.asset_number >= nvl(p_asset_number_from,ad.asset_number) and
ad.asset_number <= nvl(p_asset_number_to,ad.asset_number) and
ad.asset_category_id = nvl(p_cat_id, ad.asset_category_id) and
dh.asset_id = ad.asset_id and
dh.date_ineffective is NULL and
bk.asset_id = ad.asset_id and
bk.book_type_code = me.book_type_code and
bk.date_ineffective is NULL and
bk.date_placed_in_service between
nvl(p_dpis_from,bk.date_placed_in_service) and
nvl(p_dpis_to,bk.date_placed_in_service) and
adw.asset_id(+) = me.asset_id and
war.warranty_id(+) = adw.warranty_id and
emp1.person_id(+) = war.employee_id and
emp2.person_id(+) = me.employee_id and
po.vendor_id(+) = me.vendor_id;
select category_flex_structure,
location_flex_structure,
asset_key_flex_structure
into h_cat_structure,
h_loc_structure,
h_key_structure
from fa_system_controls;
select fcr.last_update_login,fcr.requested_by
into h_login_id,h_user_id
from fnd_concurrent_requests fcr
where fcr.request_id = h_request_id;
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
insert into fa_maint_rep_itf (
REQUEST_ID,BOOK_TYPE_CODE,ASSET_ID,ASSET_NUMBER,DESCRIPTION,SERIAL_NUMBER,TAG_NUMBER,
ASSET_KEY_FF,ASSET_COST,LOCATION_FF,CATEGORY_FF,EVENT_NAME,MAINTENANCE_COST,
MAINTENANCE_DATE,VENDOR_NAME,VENDOR_NUMBER,CONTACT_NAME,CONTACT_NUMBER,
WARRANTY_NUMBER,WARRANTY_DESC,WARRANTY_START_DATE,WARRANTY_END_DATE,
MANUFACTURER_NAME,MODEL_NUMBER,WARRANTY_CONTACT_NAME,WARRANTY_CONTACT_NUMBER,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN)
values (h_request_id,c_mainrec.book_type_code,c_mainrec.asset_id,c_mainrec.asset_number,
c_mainrec.description,c_mainrec.serial_number,c_mainrec.tag_number,
h_concat_key,c_mainrec.asset_cost,h_concat_loc,h_concat_cat,c_mainrec.event_name,
c_mainrec.maint_cost,c_mainrec.maintenance_date,c_mainrec.vendor_name,
c_mainrec.vendor_number,c_mainrec.maint_contact_name,c_mainrec.maint_contact_number,
c_mainrec.warranty_number,c_mainrec.war_desc,c_mainrec.start_date,
c_mainrec.end_date,c_mainrec.manufacturer_name,c_mainrec.model_number,
c_mainrec.warr_contact_name,c_mainrec.warr_contact_number,sysdate,h_user_id,
h_user_id,sysdate,h_login_id);
if (h_mesg_name = 'FA_SHARED_INSERT_FAILED') then
fnd_message.set_token('TABLE','FA_MAINT_REP_ITF');
end insert_to_itf;
select category_flex_structure
into h_cat_structure
from fa_system_controls;
select s.concatenated_segment_delimiter into delim
FROM fnd_id_flex_structures s, fnd_application a
WHERE s.application_id = a.application_id
AND s.id_flex_code = 'CAT#'
AND s.id_flex_num = h_cat_structure
AND a.application_short_name = 'OFA';
insert_to_itf (
p_book => h_book,
p_event_name => h_event_name,
p_maint_date_from => h_maint_date_from,
p_maint_date_to => h_maint_date_to,
p_asset_number_from => h_asset_number_from,
p_asset_number_to => h_asset_number_to,
p_dpis_from => h_dpis_from,
p_dpis_to => h_dpis_to,
p_cat_id => h_cat_ccid,
p_request_id => h_request_id,
retcode => retcode,
errbuf => errbuf);
procedure do_insert(
p_book in varchar2,
p_event_name in varchar2,
p_maint_date_from in date,
p_maint_date_to in date,
p_asset_number_from in varchar2,
p_asset_number_to in varchar2,
p_dpis_from in date,
p_dpis_to in date,
p_category_id in varchar2,
p_request_id in number,
p_retcode out nocopy number) is
h_maint_date_from date;
insert_to_itf(p_book => p_book,
p_event_name => p_event_name,
p_maint_date_from => h_maint_date_from,
p_maint_date_to => h_maint_date_to,
p_asset_number_from => p_asset_number_from,
p_asset_number_to => p_asset_number_to,
p_dpis_from => h_dpis_from,
p_dpis_to => h_dpis_to,
p_cat_id => h_cat_id,
p_request_id => p_request_id,
retcode => p_retcode,
errbuf => h_errbuf);
end do_insert;