[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
else --it must be update mode (1)
return (p_id = FND_API.G_MISS_NUM);
else --it must be update mode (1)
return (p_code = FND_API.G_MISS_CHAR);
l_skip_update BOOLEAN := false;
l_skip_update := ((p_id is null) and (p_code is null));
else --it must be update mode (1)
l_skip_update := (((p_id is not null) and (p_id = FND_API.G_MISS_NUM)) OR
((p_id is null) and (p_code is not null) and (p_code = FND_API.G_MISS_CHAR)));
if (l_skip_update) then
p_id := FND_API.G_MISS_NUM;
return l_skip_update;
procedure update_row_error (
p_error_code NUMBER,
p_error_message VARCHAR2,
p_asset_rec MTL_EAM_ASSET_NUM_INTERFACE%ROWTYPE
) is
begin
UPDATE MTL_EAM_ASSET_NUM_INTERFACE SET
process_flag = 'E',
error_code = p_error_code,
error_message = p_error_message
WHERE interface_header_id = p_asset_rec.interface_header_id;
UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
SET meavi.error_number = 9999,
meavi.process_status = 'E',
meavi.error_message = 'Import of corresponding row in MTL_EAM_ASSET_NUM_INTERFACE failed'
WHERE meavi.interface_header_id = p_asset_rec.interface_header_id
AND meavi.process_status = 'P';
end update_row_error;
procedure update_remaining_row_status (p_batch_id NUMBER) is
begin
UPDATE MTL_EAM_ASSET_NUM_INTERFACE
SET process_flag = 'P',
interface_group_id = NULL
WHERE batch_id = p_batch_id
AND process_flag = 'R';
end update_remaining_row_status;
procedure update_row_calculated_values (
p_asset_rec MTL_EAM_ASSET_NUM_INTERFACE%ROWTYPE
) is
l_api_name constant varchar2(30) := 'update_row_calculated_values';
update mtl_eam_asset_num_interface set
owning_department_code = p_asset_rec.owning_department_code,
owning_department_id = p_asset_rec.owning_department_id,
asset_criticality_code = p_asset_rec.asset_criticality_code,
asset_criticality_id = p_asset_rec.asset_criticality_id,
fa_asset_number = p_asset_rec.fa_asset_number,
fa_asset_id = p_asset_rec.fa_asset_id,
location_codes = p_asset_rec.location_codes,
eam_location_id = p_asset_rec.eam_location_id,
current_status = p_asset_rec.current_status,
prod_organization_id = p_asset_rec.prod_organization_id,
prod_organization_code = p_asset_rec.prod_organization_code
where interface_header_id = p_asset_rec.interface_header_id;
update mtl_eam_asset_num_interface set
instance_number = (select instance_number from csi_item_instances where serial_number = p_asset_rec.serial_number)
where interface_header_id = p_asset_rec.interface_header_id;
end update_row_calculated_values;
select status into l_pn_installed
from fnd_product_installations
where application_id = 240;
SELECT current_status, gen_object_id
INTO l_msn_status, l_gen_object_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
and current_organization_id = p_current_organization_id
and inventory_item_id = p_inventory_item_id;
SELECT gen_object_id INTO l_parent_object_id
FROM mtl_serial_numbers
WHERE serial_number = p_parent_serial_number
and inventory_item_id = p_parent_inventory_item_id;
select 'Y' into l_hr_exists from dual
where exists
(select * from mtl_object_genealogy
where object_id = l_gen_object_id);
SELECT mog.start_date_active start_date_active,
mog.end_date_active
end_date_active, msn.serial_number
parent_serial_number
FROM mtl_object_genealogy mog, mtl_serial_numbers msn
WHERE mog.object_id = l_gen_object_id
and msn.gen_object_id = mog.parent_object_id
and mog.genealogy_type = 5;
inv_genealogy_pub.delete_eam_row(
p_api_version=>1.0,
p_object_id => l_gen_object_id,
p_start_date_active=>i.start_date_active,
p_end_date_active=>i.end_date_active,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
inv_genealogy_pub.delete_eam_row(
p_api_version=>1.0,
p_object_id => l_gen_object_id,
p_start_date_active=>i.start_date_active,
p_end_date_active=>i.end_date_active,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
inv_genealogy_pub.delete_eam_row(
p_api_version=>1.0,
p_object_id => l_gen_object_id,
p_start_date_active=>i.start_date_active,
p_end_date_active=>i.end_date_active,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
inv_genealogy_pub.delete_eam_row(
p_api_version=>1.0,
p_object_id => l_gen_object_id,
p_start_date_active=>i.start_date_active,
p_end_date_active=>i.end_date_active,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
inv_genealogy_pub.insert_genealogy(
p_api_version => 1.0,
p_object_type => 2,
p_parent_object_type => 2,
p_object_number => p_serial_number,
p_inventory_item_id => p_inventory_item_id,
p_org_id => p_current_organization_id,
p_parent_object_id => l_parent_object_id,
p_genealogy_origin => 3,
p_genealogy_type => 5,
p_start_date_active => p_start_date_active,
p_end_date_active => l_end_date_active,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
inv_genealogy_pub.update_genealogy(
p_api_version => 1.0,
p_object_type => 2,
p_object_number => p_serial_number,
p_inventory_item_id => p_inventory_item_id,
p_org_id => p_current_organization_id,
p_genealogy_origin => 3,
p_genealogy_type => 5,
p_end_date_active => l_end_date_active,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
no_rows_updated EXCEPTION;
SELECT *
FROM MTL_EAM_ASSET_NUM_INTERFACE
WHERE interface_group_id = p_interface_group_id
AND process_flag = 'R'
ORDER BY import_mode, import_scope, interface_header_id;
SELECT pn_location_id, serial_number, inventory_item_id
FROM CSI_ITEM_INSTANCES
WHERE pn_location_id in (child1, parent1);
select active_start_date, active_end_date, parent_location_id
into l_start_date_active, l_end_date_active, l_parent_location_id
from pn_locations_all
where location_id = asset_rec.pn_location_id
and active_start_date <= sysdate
and active_end_date >= sysdate;
select location_id into asset_rec.eam_location_id
from mtl_eam_locations
where location_codes = asset_rec.location_codes
and organization_id = asset_rec.current_organization_id;
'Now inserting asset...');
eam_assetnumber_pub.Insert_Asset_Number
(
p_api_version => l_api_version,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_inventory_item_id => asset_rec.inventory_item_id,
p_serial_number => asset_rec.serial_number,
p_instance_number => asset_rec.instance_number,
p_current_status => asset_rec.current_status,
p_descriptive_text => asset_rec.descriptive_text,
p_current_organization_id => asset_rec.current_organization_id,
p_wip_accounting_class_code => asset_rec.wip_accounting_class_code,
p_maintainable_flag => asset_rec.maintainable_flag,
p_owning_department_id => asset_rec.owning_department_id,
p_network_asset_flag => nvl(asset_rec. network_asset_flag,'N'),
p_fa_asset_id => asset_rec. fa_asset_id,
p_pn_location_id => asset_rec.pn_location_id,
p_eam_location_id => asset_rec.eam_location_id,
p_asset_criticality_code => to_char(asset_rec.asset_criticality_id),
p_category_id => asset_rec.category_id,
p_prod_organization_id => asset_rec. prod_organization_id,
p_equipment_item_id => asset_rec.equipment_item_id,
p_eqp_serial_number => asset_rec.eqp_serial_number,
p_active_start_date => asset_rec.active_start_date,
p_active_end_date => asset_rec.active_end_date,
p_operational_log_flag => asset_rec.operational_log_flag,
p_checkin_status => asset_rec.checkin_status,
p_supplier_warranty_exp_date => asset_rec.supplier_warranty_exp_date
);
l_error_message := 'Asset insertion failed with return status: '
|| l_return_status || ' and message: ' || l_data;
'Asset was inserted.');
l_error_message := 'Failed while trying to insert genealogy for PN';
l_error_message := 'Failed while trying to insert/update genealogy for PN';
eam_assetnumber_pub.update_asset_number
(
p_api_version => l_api_version,
x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_inventory_item_id => asset_rec.inventory_item_id,
p_serial_number => asset_rec.serial_number,
p_instance_number => asset_rec.instance_number,
p_current_organization_id => asset_rec.current_organization_id,
p_descriptive_text => asset_rec.descriptive_text,
p_category_id => asset_rec.category_id,
p_prod_organization_id => asset_rec. prod_organization_id,
p_equipment_item_id => asset_rec.equipment_item_id,
p_eqp_serial_number => asset_rec.eqp_serial_number,
p_pn_location_id => asset_rec.pn_location_id,
p_eam_location_id => asset_rec.eam_location_id,
p_fa_asset_id => asset_rec. fa_asset_id,
p_asset_criticality_code => l_asset_criticality_code,
p_wip_accounting_class_code => asset_rec.wip_accounting_class_code,
p_maintainable_flag => asset_rec.maintainable_flag,
p_network_asset_flag => nvl(asset_rec. network_asset_flag,'N'),
p_owning_department_id => asset_rec.owning_department_id,
p_current_status => asset_rec.current_status,
p_active_end_date => asset_rec.active_end_date,
p_operational_log_flag => asset_rec.operational_log_flag,
p_checkin_status => asset_rec.checkin_status,
p_supplier_warranty_exp_date => asset_rec.supplier_warranty_exp_date
);
l_error_message := 'Asset update failed with return status: '
|| l_return_status || ' and message: ' || l_data;
'Asset was updated');
update_row_calculated_values(asset_rec);
UPDATE MTL_EAM_ASSET_NUM_INTERFACE
SET process_flag = 'S'
WHERE interface_header_id = asset_rec.interface_header_id;
SELECT interface_line_id,
application_id,
descriptive_flexfield_name,
application_column_name,
association_id,
process_status,
error_number,
error_message
FROM mtl_eam_attr_val_interface
WHERE interface_header_id = asset_rec.interface_header_id;
UPDATE mtl_eam_attr_val_interface
SET application_id = meavi_tab(i).application_id,
descriptive_flexfield_name = meavi_tab(i).descriptive_flexfield_name,
application_column_name = meavi_tab(i).application_column_name,
association_id = meavi_tab(i).association_id,
process_status = meavi_tab(i).process_status,
error_number = meavi_tab(i).error_number,
error_message = meavi_tab(i).error_message
WHERE interface_line_id = meavi_tab(i).interface_line_id;
UPDATE MTL_EAM_ASSET_NUM_INTERFACE
SET process_flag = 'E',
error_code = l_error_code,
error_message = l_error_message
WHERE interface_header_id = asset_rec.interface_header_id;
update_row_calculated_values(asset_rec);
WHEN NO_DATA_FOUND or no_rows_updated THEN
if (l_exlog) then
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
'ERROR: ' || l_error_code || ' - ' || l_error_message);
update_row_error(l_error_code, l_error_message, asset_rec);
update_row_calculated_values(asset_rec);
update_remaining_row_status(asset_rec.batch_id);
update_row_error(9999, l_varchar2000, asset_rec);
update_row_calculated_values(asset_rec);
DELETE FROM MTL_EAM_ASSET_NUM_INTERFACE
WHERE process_flag = 'S'
and interface_group_id = p_interface_group_id;
DELETE FROM MTL_EAM_ASSET_NUM_INTERFACE
WHERE process_flag = 'S'
and interface_group_id = p_interface_group_id;
UPDATE MTL_EAM_ASSET_NUM_INTERFACE
SET process_flag = 'E',
error_message = l_error_message
WHERE interface_group_id = p_interface_group_id
and process_flag = 'R';