[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_txn_id IN NUMBER := NULL,
p_from_eam IN VARCHAR2 := NULL,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) is
l_api_name CONSTANT VARCHAR(30) := 'CREATE_EAM_GENEALOGY';
l_update_txn_id number;
l_update_txn_id := p_update_txn_id;
select serial_number_control_code into l_serial_control
from mtl_system_items
where inventory_item_id = p_inventory_item_id and
organization_id = p_organization_id;
select current_organization_id into l_organization_id
from mtl_serial_numbers
where serial_number = l_serial_number
and inventory_item_id = l_inventory_item_id;
select serial_number, inventory_item_id, current_organization_id
into l_serial_number, l_inventory_item_id, l_organization_id
from mtl_serial_numbers
where gen_object_id = p_object_id;
select current_organization_id into l_organization_id
from mtl_serial_numbers
where gen_object_id = p_object_id;
select gen_object_id, current_organization_id
into l_parent_object_id, l_parent_organization_id
from mtl_serial_numbers
where serial_number = l_parent_serial_number
and inventory_item_id = p_parent_inventory_item_id;
select current_organization_id,serial_number,inventory_item_id
into
l_parent_organization_id,
l_parent_serial_number,
l_parent_inventory_item_id
from mtl_serial_numbers
where gen_object_id = l_parent_object_id;
select msn.gen_object_id
into l_charge_object_id
from wip_discrete_jobs wdj, mtl_serial_numbers msn
where wdj.rebuild_serial_number = l_serial_number
and wdj.rebuild_item_id = l_inventory_item_id
and wdj.organization_id = l_organization_id
and msn.serial_number = wdj.asset_number
and msn.inventory_item_id = wdj.asset_group_id
and msn.current_organization_id = wdj.organization_id
and wdj.manual_rebuild_flag = 'N'
and wdj.status_type in (1,3,6);
inv_genealogy_pub.insert_genealogy(
p_api_version => l_api_version,
p_object_type => 2,
p_parent_object_type => 2,
p_object_number => l_serial_number,
p_inventory_item_id => l_inventory_item_id,
p_org_id => l_organization_id,
p_parent_object_id => l_parent_object_id,
p_parent_object_number => l_parent_serial_number,
p_parent_inventory_item_id => l_parent_inventory_item_id,
p_parent_org_id => l_parent_organization_id,
p_genealogy_origin => 3,
p_genealogy_type => 5,
p_start_date_active => p_start_date_active,
p_end_date_active => p_end_date_active,
p_origin_txn_id => null,
p_update_txn_id => null,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT cii.instance_id, mp.maint_organization_id,cii.instance_number
INTO l_parent_instance_id , l_maint_orgid,l_parent_instance_number
FROM csi_item_instances cii, mtl_parameters mp
WHERE cii.serial_number = l_parent_serial_number
AND cii.inventory_item_id = l_parent_inventory_item_id
AND cii.last_vld_organization_id = mp.organization_id
AND cii.last_vld_organization_id= l_organization_id ;
SELECT cii.instance_number, cii.instance_id
INTO l_reference, l_instance_id
FROM csi_item_instances cii
WHERE cii.serial_number = l_serial_number
AND cii.inventory_item_id = l_inventory_item_id
AND cii.last_vld_organization_id = l_organization_id;
eam_asset_log_pvt.insert_row(
p_event_date => p_start_date_active,
p_event_type => l_event_type,
p_event_id => 15,
p_organization_id => l_maint_orgid,
p_instance_id => l_parent_instance_id,
p_reference => l_reference,
p_ref_id => l_instance_id,
p_instance_number => l_parent_instance_number,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
eam_asset_log_pvt.insert_row(
p_event_date => p_start_date_active,
p_event_type => l_event_type,
p_event_id => 14,
p_organization_id => l_maint_orgid,
p_instance_id => l_instance_id,
p_reference => l_parent_instance_number,
p_ref_id => l_parent_instance_id,
p_instance_number => l_reference,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
PROCEDURE update_eam_genealogy(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_object_type IN NUMBER,
p_object_id IN NUMBER := NULL,
p_serial_number IN VARCHAR2 := NULL,
p_inventory_item_id IN NUMBER := NULL,
p_organization_id IN NUMBER := NULL,
p_subinventory IN VARCHAR2 := NULL,
p_locator_id IN NUMBER := NULL,
p_genealogy_origin IN NUMBER := NULL,
p_genealogy_type IN NUMBER := NULL,
p_end_date_active IN DATE := NULL,
p_from_eam IN VARCHAR2 := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) is
l_api_name CONSTANT VARCHAR(30) := 'UPDATE_EAM_GENEALOGY';
select gen_object_id,current_organization_id into l_object_id, l_organization_id
from mtl_serial_numbers
where serial_number = l_serial_number
and inventory_item_id = p_inventory_item_id;
select serial_number, inventory_item_id, current_organization_id
into l_serial_number, l_inventory_item_id, l_organization_id
from mtl_serial_numbers where
gen_object_id = p_object_id;
select current_organization_id into l_organization_id
from mtl_serial_numbers
where gen_object_id = p_object_id;
select restrict_subinventories_code
into l_sub_code
from mtl_system_items
where inventory_item_id = l_inventory_item_id
and organization_id = l_organization_id;
select 10 into l_dummy from dual
where exists
(select *
from mtl_item_sub_inventories
where inventory_item_id = l_inventory_item_id
and organization_id = l_organization_id
and secondary_inventory = l_subinventory);
select current_locator_id into l_locator_id
from mtl_serial_numbers
where serial_number = l_serial_number and
inventory_item_id = l_inventory_item_id and
current_organization_id = l_organization_id;
select maintenance_offset_account into l_dist_acct_id
from wip_eam_parameters
where organization_id = l_organization_id;
select revision into l_revision
from mtl_serial_numbers
where serial_number = l_serial_number and
inventory_item_id = l_inventory_item_id and
current_organization_id = l_organization_id;
select last_transaction_id
into l_txn_id
from mtl_serial_numbers
where serial_number = l_serial_number
and current_organization_id = l_organization_id and
inventory_item_id = l_inventory_item_id;
SELECT parent_object_id INTO l_parent_object_id
FROM mtl_object_genealogy
WHERE genealogy_type = 5
AND object_id = l_object_id
AND end_date_active IS NULL;
inv_genealogy_pub.update_genealogy(
p_api_version => l_api_version,
p_object_type => 2,
p_object_number => l_serial_number,
p_inventory_item_id => l_inventory_item_id,
p_org_id => l_organization_id,
p_genealogy_origin => 3,
p_genealogy_type => 5,
p_end_date_active => p_end_date_active,
p_update_txn_id => l_txn_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT cii.instance_id, mp.maint_organization_id
INTO l_instance_id , l_maint_orgid
FROM csi_item_instances cii, mtl_parameters mp
WHERE cii.serial_number = l_serial_number
AND cii.inventory_item_id = l_inventory_item_id
AND cii.last_vld_organization_id = mp.organization_id
AND cii.last_vld_organization_id= l_organization_id ;
SELECT cii.instance_number ,cii.instance_id
INTO l_reference, l_parent_instance_id
FROM csi_item_instances cii, mtl_serial_numbers msn
WHERE cii.serial_number = msn.serial_number
AND cii.inventory_item_id = msn.inventory_item_id
AND cii.last_vld_organization_id = msn.current_organization_id
AND msn.gen_object_id= l_parent_object_id ;
eam_asset_log_pvt.insert_row(
p_event_date => p_end_date_active,
p_event_type => l_event_type,
p_event_id => 17,
p_organization_id => l_maint_orgid,
p_instance_id => l_instance_id,
p_reference => l_reference,
p_ref_id => l_parent_instance_id,
p_instance_number => l_serial_number,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT cii.instance_id, cii.instance_number
INTO l_parent_instance_id, l_parent_instance_number
FROM csi_item_instances cii, mtl_serial_numbers msn
WHERE cii.serial_number = msn.serial_number
AND cii.inventory_item_id = msn.inventory_item_id
AND msn.gen_object_id = l_parent_object_id;
SELECT cii.instance_number
INTO l_reference
FROM csi_item_instances cii
WHERE cii.instance_id = l_instance_id
-- AND cii.inventory_item_id = l_inventory_item_id
AND cii.last_vld_organization_id = l_organization_id;
eam_asset_log_pvt.insert_row(
p_event_date => p_end_date_active,
p_event_type => l_event_type,
p_event_id => 16,
p_organization_id => l_maint_orgid,
p_instance_id => l_parent_instance_id,
p_reference => l_reference,
p_ref_id => l_instance_id,
p_instance_number => l_parent_instance_number,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
end update_eam_genealogy;