This chapter covers the following topics:
This chapter describes the Deployment Transaction APIs for Oracle Asset Tracking.
You can use external deployment systems (such as bar code readers and scanners) along with Oracle Asset Tracking. Oracle Asset Tracking has the ability to integrate with the external systems through these APIs. Call these APIs to update Oracle Asset Tracking when the item is installed, uninstalled, placed in service, moved, or retired.
The topics in this section are as follows:
The APIs provided for Oracle Asset Tracking are organized into the following package:
CSE_DEPLOYMENT_GRP
This package contains the procedure PROCESS_TRANSACTION, which can perform the deployment transactions that are listed in the following table:
| Name | Description |
|---|---|
| Install | The prerequisite is that you must have an instance with the operational status code In-Process/Not Used for the item you are trying to install. This procedure creates and updates the item instance with the operational status Installed, location type code from Project/Internal Site to HZ_locations. If there is an existing instance with operational status code Installed, then it updates the same or otherwise it will create a new instance. |
| Un-Install | The prerequisite is that you must have an instance with the operational status code Installed for the item you are trying to uninstall. This procedure updates the item instance with operational status code Not Used, location type code from HZ_locations to Project. |
| Put in Service | The prerequisite is that you must have an instance with the operational status code Installed for the item you are trying to put into service. This procedure creates and updates the item instance with the operational status code In-Service. If there is an existing instance with operational status code In-Service, then it updates the same or otherwise it will create a new instance. This procedure creates expenditure items in the projects interface table. The Installed Base transaction created by this action will be used to create a project asset for Normal Items. |
| Take Out of Service | The prerequisite is that you must have an instance with the operational status code In-Service for the item you are trying to put out of service. This procedure creates and updates the item instance with the operational status code Out-of-Service. If there is an existing instance with operational status code Out-of-Service, then it updates the same or otherwise it will create a new instance. |
| Move | The prerequisite is that you must have an instance with the operational status code Out-of-Service for the item you are trying to move. This procedure creates and updates the network location of the item instance with the operational status code Out-of-Service. If there is an existing instance with operational status code Out-of-Service, network location as From Network Location, then it updates the network location with To Network Location or otherwise it will create a new instance with operational status code Out-of-Service, network location as To Network Location. The Installed Base transaction created by this action will be used to interface move transactions to Oracle Fixed Assets. |
| Project Transfer | The prerequisite is that project transfer cannot be done for items that are already put in service. This procedure transfer items that are received into a project location to another project location. Item instances that are just received in to project or Installed in a location can be transferred. |
| Retirement | This procedure manually retires items that are capitalized. You can retire an item instance operationally (the instance is expired), or both operationally and functionally (the corresponding fixed asset is also retired from the books). |
Contains one single routine that handles all of the previously mentioned deployment actions.
PROCESS_TRANSACTION
The following table describes the IN parameters associated with this API.
| Parameter | Data Type | Required | Description |
|---|---|---|---|
| p_instance_tbl | cse_deployment_grp.txn_instances_tbl | Yes | This PL/SQL structure contains the instance information for the transaction in context. |
| p_dest_location_tbl | cse_deployment_grp.dest_location_tbl | Yes | This PL/SQL structure contains the destination location information for the item instance being transacted. The location information should be populated in accordance with the transaction action in context. |
| p_ext_attrib_values_tbl | cse_deployment_grp.txn_ext_attrib_values_tbl | Yes | This PL/SQL structure contains any references to extended attribute values that require an update along with the instance update. |
| p_txn_tbl | cse_deployment_grp.transaction_tbl | Yes | This PL/SQL structure holds the transaction action type and the transaction reference data that needed to be populated in Installed Base transactions. |
The following table describes the OUT parameters associated with this API.
| Parameter | Data Type | Description |
|---|---|---|
| x_return_status | VARCHAR2 | Returns the status of the transaction: FND_API.G_RET_STS_SUCCESS - successful FND_API.G_RET_STS_ERROR - error FND_API.G_RET_STS_UNEXP_ERROR - unexpected error |
| x_error_msg | VARCHAR2 | The error message |
Project Transfer
set serverout on
declare
l_location_type_code varchar2(30);
l_location_id number;
l_instance_usage_code varchar2(30);
l_operational_status_code varchar2(30);
l_instance_tbl cse_deployment_grp.txn_instances_tbl;
l_dest_location_tbl cse_deployment_grp.dest_location_tbl;
l_ext_attrib_values_tbl cse_deployment_grp.txn_ext_attrib_values_tbl;
l_txn_tbl cse_deployment_grp.transaction_tbl;
l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
l_error_message varchar2(2000);
begin
l_instance_tbl(1).instance_id := 1302711;
select serial_number,
lot_number,
inventory_item_id,
operational_status_code,
location_type_code,
location_id,
instance_usage_code,
operational_status_code
into l_instance_tbl(1).serial_number,
l_instance_tbl(1).lot_number,
l_instance_tbl(1).inventory_item_id,
l_instance_tbl(1).operational_status_code,
l_location_type_code,
l_location_id,
l_instance_usage_code,
l_operational_status_code
from csi_item_instances
where instance_id = l_instance_tbl(1).instance_id;
IF l_location_type_code = 'PROJECT' THEN
l_instance_tbl(1).last_pa_project_id := 5773;
l_instance_tbl(1).last_pa_project_task_id := 248906;
ELSE
l_instance_tbl(1).last_pa_project_id := 5773;
l_instance_tbl(1).last_pa_project_task_id := 248906;
END IF;
l_txn_tbl(1).transaction_id := fnd_api.g_miss_num;
l_txn_tbl(1).transaction_date := sysdate;
l_txn_tbl(1).source_transaction_date := sysdate;
l_txn_tbl(1).transaction_type_id := 152;
l_txn_tbl(1).txn_sub_type_id := 3;
l_txn_tbl(1).source_group_ref_id := fnd_api.g_miss_num;
l_txn_tbl(1).source_group_ref := fnd_api.g_miss_char;
l_txn_tbl(1).source_header_ref_id := fnd_api.g_miss_num;
l_txn_tbl(1).source_header_ref := fnd_api.g_miss_char;
l_txn_tbl(1).transacted_by := fnd_api.g_miss_num;
l_txn_tbl(1).transaction_quantity := 1;
l_txn_tbl(1).operational_flag := 'Y';
l_txn_tbl(1).financial_flag := 'Y';
l_dest_location_tbl(1).parent_tbl_index := 1;
l_dest_location_tbl(1).location_type_code := l_location_type_code;
l_dest_location_tbl(1).location_id := l_location_id;
l_dest_location_tbl(1).instance_usage_code := l_instance_usage_code;
l_dest_location_tbl(1).operational_status_code := l_operational_status_code;
IF l_location_type_code = 'PROJECT' THEN
l_dest_location_tbl(1).pa_project_id := 8093;
l_dest_location_tbl(1).pa_project_task_id := 249903;
l_dest_location_tbl(1).last_pa_project_id := fnd_api.g_miss_num;
l_dest_location_tbl(1).last_pa_project_task_id := fnd_api.g_miss_num;
ELSE
l_dest_location_tbl(1).last_pa_project_id := 8093;
l_dest_location_tbl(1).last_pa_project_task_id := 249903;
l_dest_location_tbl(1).pa_project_id := fnd_api.g_miss_num;
l_dest_location_tbl(1).pa_project_task_id := fnd_api.g_miss_num;
END IF;
cse_deployment_grp.process_transaction (
p_instance_tbl => l_instance_tbl,
p_dest_location_tbl => l_dest_location_tbl,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_txn_tbl => l_txn_tbl,
x_return_status => l_return_status,
x_error_msg => l_error_message);
if l_return_status <> fnd_api.g_ret_sts_success then
dbms_output.put_line(l_error_message);
end if;
end;
/
Asset Retirement
set serverout on
declare
l_instance_tbl cse_deployment_grp.txn_instances_tbl;
l_dest_location_tbl cse_deployment_grp.dest_location_tbl;
l_ext_attrib_values_tbl cse_deployment_grp.txn_ext_attrib_values_tbl;
l_txn_tbl cse_deployment_grp.transaction_tbl;
l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
l_error_message varchar2(2000);
begin
l_instance_tbl(1).instance_id := 1605848;
l_instance_tbl(1).asset_id := 108431;
select serial_number,
inventory_item_id,
operational_status_code
into l_instance_tbl(1).serial_number,
l_instance_tbl(1).inventory_item_id,
l_instance_tbl(1).operational_status_code
from csi_item_instances
where instance_id = l_instance_tbl(1).instance_id;
l_instance_tbl(1).lot_number := fnd_api.g_miss_char;
l_instance_tbl(1).inventory_revision := fnd_api.g_miss_char;
l_instance_tbl(1).last_pa_project_id := fnd_api.g_miss_num;
l_instance_tbl(1).last_pa_project_task_id := fnd_api.g_miss_num;
l_instance_tbl(1).unit_of_measure := fnd_api.g_miss_char;
l_instance_tbl(1).active_start_date := fnd_api.g_miss_date;
l_instance_tbl(1).active_end_date := fnd_api.g_miss_date;
l_instance_tbl(1).instance_status_id := fnd_api.g_miss_num;
l_txn_tbl(1).transaction_id := fnd_api.g_miss_num;
l_txn_tbl(1).transaction_date := sysdate;
l_txn_tbl(1).source_transaction_date := sysdate;
l_txn_tbl(1).transaction_type_id := 104;
l_txn_tbl(1).txn_sub_type_id := fnd_api.g_miss_num;
l_txn_tbl(1).source_group_ref_id := fnd_api.g_miss_num;
l_txn_tbl(1).source_group_ref := fnd_api.g_miss_char;
l_txn_tbl(1).source_header_ref_id := fnd_api.g_miss_num;
l_txn_tbl(1).source_header_ref := fnd_api.g_miss_char;
l_txn_tbl(1).transacted_by := fnd_api.g_miss_num;
l_txn_tbl(1).transaction_quantity := 1;
l_txn_tbl(1).proceeds_of_sale := 10;
l_txn_tbl(1).cost_of_removal := 10;
-- use this flag to expire the item instance
l_txn_tbl(1).operational_flag := 'Y';
-- use this flag to retire the fixed asset
l_txn_tbl(1).financial_flag := 'Y';
cse_deployment_grp.process_transaction (
p_instance_tbl => l_instance_tbl,
p_dest_location_tbl => l_dest_location_tbl,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_txn_tbl => l_txn_tbl,
x_return_status => l_return_status,
x_error_msg => l_error_message);
if l_return_status <> fnd_api.g_ret_sts_success then
dbms_output.put_line(l_error_message);
end if;
end;
/