[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 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
and current_organization_id = p_parent_organization_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);
select current_status into l_current_status 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 current_subinventory_code into l_subinventory
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 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 last_transaction_id
into l_txn_id
from mtl_serial_numbers
where serial_number = l_serial_number;
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 => p_parent_object_id
, p_parent_object_number => p_parent_serial_number
, p_parent_inventory_item_id => p_parent_inventory_item_id
, p_parent_org_id => p_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 => l_txn_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
PROCEDURE update_rebuild_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
, 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_REBUILD_GENEALOGY';
select gen_object_id into l_object_id
from mtl_serial_numbers
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_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_subinventory_code into l_subinventory
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 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 last_transaction_id
into l_txn_id
from mtl_serial_numbers
where serial_number = l_serial_number;
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 => l_msg_count
, x_msg_data => l_msg_data);
end update_rebuild_genealogy;