[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inv.inventory_name,
ad.asset_number,
ad.description,
dh.units_assigned,
dh.location_id,
ad.tag_number,
bk.date_placed_in_service,
ad.serial_number,
ad.manufacturer_name,
ad.model_number,
ad.asset_category_id,
ad.asset_key_ccid
FROM fa_books bk,
fa_distribution_history dh,
fa_inventory inv,
fa_additions ad,
fa_book_controls_sec bc --Bug#3503643
WHERE ad.inventorial = 'YES'
AND ad.asset_id = bk.asset_id
AND bk.date_ineffective is null
AND bk.period_counter_fully_retired is null
AND bk.book_type_code = bc.book_type_code
AND inv.inventory_name = h_inventory_name
AND inv.start_date > bk.date_placed_in_service
AND bc.book_class = 'CORPORATE'
AND bc.book_type_code = dh.book_type_code
AND dh.asset_id = ad.asset_id
AND dh.date_ineffective is null
AND ad.asset_id not in
(
select itf.asset_id
from fa_inv_interface itf
where itf.inventory_name = inv.inventory_name
and itf.asset_id = ad.asset_id
);
select location_flex_structure,
asset_key_flex_structure,
category_flex_structure
into h_loc_structure,
h_key_structure,
h_cat_structure
from fa_system_controls;
select fcr.last_update_login
into h_login_id
from fnd_concurrent_requests fcr
where fcr.request_id = h_request_id;
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
insert into fa_invmiss_rep_itf (
request_id,
location,
category,
asset_key,
inventory_name,
asset_number,
description,
units_assigned,
serial_number,
tag_number,
manufacturer_name,
model_number,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
values (
h_request_id,
h_concat_loc,
h_concat_cat,
h_concat_key,
c_mainrec.inventory_name,
c_mainrec.asset_number,
c_mainrec.description,
c_mainrec.units_assigned,
c_mainrec.serial_number,
c_mainrec.tag_number,
c_mainrec.manufacturer_name,
c_mainrec.model_number,
user_id, sysdate, user_id , sysdate, h_login_id);
if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
fnd_message.set_token('TABLE','FA_INVMISS_REP_ITF',FALSE);
select inv.inventory_name, inv.asset_number, inv.asset_key_ccid,
inv.tag_number, inv.description, inv.model_number, inv.serial_number,
inv.manufacturer_name, inv.asset_category_id, inv.units,
inv.location_id, lu1.meaning status,
lu2.meaning unit_reconcile_mth_mean,
lu3.meaning loc_reconcile_mth_mean
from fa_inv_interface inv, fa_lookups lu1, fa_lookups lu2, fa_lookups lu3
where nvl(inv.inventory_name,'X') = nvl(h_inventory_name,
nvl(inv.inventory_name,'X'))
and nvl(inv.asset_category_id,-9999) = nvl(h_cat_ccid,
nvl(inv.asset_category_id,-9999))
and nvl(inv.location_id,-9999) = nvl(h_loc_ccid,
nvl(inv.location_id,-9999))
and inv.status = lu1.lookup_code(+)
and lu1.lookup_type(+) = 'INVENTORY STATUS'
and inv.unit_reconcile_mth = lu2.lookup_code(+)
and lu2.lookup_type(+) like 'INVENTORY UNIT METHOD%'
and inv.loc_reconcile_mth = lu3.lookup_code(+)
and lu3.lookup_type(+) like 'INVENTORY LOCATION METHOD%'
-- Bug# 7377673
and nvl(nvl(inv.asset_number, inv.tag_number), inv.serial_number) in
(select nvl(nvl(inv1.asset_number, inv1.tag_number), inv1.serial_number)
-- End Bug# 7377673
from fa_inv_interface inv1,
fa_additions_b ad,
fa_books bks,
fa_book_controls_sec bc
-- Bug# 7377673
where ( (inv1.asset_number = ad.asset_number)
or ( inv1.asset_number is null
and inv1.tag_number = ad.tag_number)
or ( inv1.asset_number is null
and inv1.tag_number is null
and inv1.serial_number = ad.serial_number)
)
-- End Bug# 7377673
and ad.asset_id = bks.asset_id
and bks.transaction_header_id_out is null
and bks.book_type_code = bc.book_type_code
union
-- Bug# 7377673
select nvl(nvl(inv2.asset_number, inv2.tag_number), inv2.serial_number)
-- End Bug# 7377673
from fa_inv_interface inv2
where not exists
(select 'x'
from fa_additions_b ad
-- Bug# 7377673
where ( ad.asset_number = inv2.asset_number
or ad.tag_number = inv2.tag_number
or ad.serial_number = inv2.serial_number)
-- End Bug# 7377673
)); --Bug#3503643
select location_flex_structure,
asset_key_flex_structure,
category_flex_structure
into h_loc_structure,
h_key_structure,
h_cat_structure
from fa_system_controls;
select fcr.last_update_login
into h_login_id
from fnd_concurrent_requests fcr
where fcr.request_id = h_request_id;
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
insert into fa_inv_compare_rep_itf (
REQUEST_ID, INVENTORY_NAME, ASSET_NUMBER, ASSET_KEY, TAG_NUMBER,
DESCRIPTION, MODEL_NUMBER, SERIAL_NUMBER, MANUFACTURER_NAME,
ASSET_CATEGORY, UNITS, LOCATION, STATUS, UNIT_RECONCILE_MTH_MEAN,
LOC_RECONCILE_MTH_MEAN, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE ) values (
h_request_id, c_comparerec.inventory_name,
c_comparerec.asset_number, h_concat_key, c_comparerec.tag_number,
c_comparerec.description, c_comparerec.model_number,
c_comparerec.serial_number, c_comparerec.manufacturer_name,
h_concat_cat, c_comparerec.units, h_concat_loc, c_comparerec.status,
c_comparerec.unit_reconcile_mth_mean,
c_comparerec.loc_reconcile_mth_mean, sysdate, user_id, h_login_id,
user_id, sysdate);
if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
fnd_message.set_token('TABLE','FA_INV_COMPARE_REP_ITF',FALSE);