Audience: EBS architects, consultants, and business analysts for asset planning and tracking

Article Summary:

Understanding the asset integrations, including the Enterprise Command Center and troubleshooting transactions with Blitz Report.

Prerequisites:

1. Download and install the free version of Blitz Report, this provides full access for up to 30 reports

2. Or you can use Blitz Report on our demo instance

About Oracle Asset Tracking

Oracle Asset Tracking (OAT) enables tracking of assets from inception to retirement, by providing support for transactions in each lifecycle phase of the asset. Assets can be acquired, built, installed, put in service, tracked for movements, maintained, and retired using different functions available in Oracle Asset Tracking and Oracle Enterprise Asset Management modules of the Asset Lifecycle Management suite of products.

OAT is designed to deploy and track internal products and assets at internal or customer sites while providing the ability to capture financial transactions with automation. You can also track inventory items after they have been installed and link financial transactions to the physical movement of equipment.

OAT integrates with Inventory, Purchasing, Assets, Payables, Projects, and Installed Base, and stores the information collected from each related module.

Oracle Installed Base integration

The Oracle Installed Base (OIB) platform uses the Service Fulfillment Manager application to integrate Purchasing, Payables, Inventory, Projects, and Fixed Assets. The OAT module reduces data entry by using configurable business rules to send messages to all other related applications to perform the appropriate update when a business event occurs. It uses OIB as its data repository. Every transaction that OAT tracks are also recorded in the install base transaction table and each record affected by such transactions is recorded in the instance and instance history tables.

All setups are done using the CRM administrator role, including the setups for locations and parties.

Asset Processing

OAT allows the interfacing of assets directly to Assets by using concurrent programs. After an asset is created, any physical movement to the associated item automatically updates the financial records based on the synchronization flag set for the instance. This reduces the amount of manual intervention required to maintain your financial system.

For example, when a depreciable item is received, run the Create Assets: Interface Inventory Transactions to Fixed Asset program to create an asset. After the Mass Additions concurrent program completes, the asset is linked with the item. Following on for example, if the item is moved to a different organization, OAT checks for necessary changes for asset location, and asset category if applicable. OAT updates the asset record, which may involve retiring an existing asset and creating a new asset in the new organization.

OAT also maintains the history of all the transactions performed. This gives you total visibility of all items and their related assets.

When OAT creates a transaction that has a financial, asset-related impact, the transaction is stored in a transaction table with an OIB status of Pending.

Name of the Program or Report Description
Create Assets: Interface Inventory transactions to Fixed Asset This is a concurrent program with the following features:

  • Creates an asset or cost adjustment when a depreciable item is received either in Oracle Inventory or Oracle Projects
  • Creates an asset or cost adjustment when a normal item is issued to a field location directly without using projects
Create Asset Headers in Oracle Projects – Normal Items Program This concurrent program creates an asset header for capitalized project expenditures. Asset creation is based on item, serial (if serialized item), asset category, date placed into service, and product classification.

Note: This program was formerly called Asset Header for Normal Item Report.

Interface In-Service Transactions to Oracle Projects – Normal Items This concurrent program should be run after deployment transactions on a project instance. It writes records into the PA interface table PA_TRANSACTIONS_INTERFACE_ALL and changes the status of the In-Service transactions to Interfaced to PA
Interface Move Transactions to Oracle Assets This concurrent program processes any item moves like subinventory transfer, inter-organization transfer, miscellaneous issues, and physical inventory adjustments for which an asset already exists.

Note: This program was formerly called Interface Move and Misc Transactions to Fixed Assets – Report

Interface Unit Adjustments to Oracle Assets – Normal Items When Oracle Purchasing receives a normal/non-serialized item into a project, a fixed asset is created. When another receipt happens for the same item into the same project this concurrent program ensures the receipt is in sync for both value and quantity. Oracle Asset Tracking and Oracle Projects adjust the existing fixed asset only when the second receipt happens within the same fixed asset accounting period of the first receipt.

Asset Processing Options

OAT processes three types of asset transaction, using the following processing units:

Identifying asset processing errors

Failing transactions need to be identified and resolved, the install base status is left at ‘PENDING’ until the situation is resolved. You can use Blitz Report to analyze errors in the related transactions arising from modules Inventory or Projects.

Installed Base Transaction Source Description Installed Base Transaction Status Meaning
PO_RECEIPT_ INTO_INVENTORY Receipt of depreciable item into Inventory PENDING Needs further fixed asset/project accounting processing
PO_RECEIPT_ INTO_PROJECT Receipt of depreciable item into Projects PENDING Needs further fixed asset/project accounting processing
Miscellaneous/ Move Order Issue to an HZ Location Miscellaneous/move order issue to an HZ location PENDING Needs further fixed asset/project accounting processing

How to find out the root cause of the pending transactions?

Both concurrent programs normally provide a useful message about why the transaction failed in their output file. However, when you get an error message such as ‘Prior transactions are in pending status for this item instance. You need to process them first before processing this one.’ you will need to find out which is the ‘Prior transactions’.

We can create 2 Blitz Reports, one to check for assets movements in ‘PENDING’ status and the other to drill into the transaction details.

Query 1 – all pending asset transactions:

SELECT citd.transaction_id,
ct.transaction_type_id,
citd.instance_id,
citd.inventory_item_id,
citd.active_start_date,
citd.active_end_date,
msi.asset_creation_code,
ciih.old_location_id,
ciih.new_location_id,
citd.serial_number,
citd.lot_number,
ct.transaction_type_id,
citd.transaction_quantity,
citd.source_txn_type_name,
cia.fa_asset_id,
cia.asset_quantity,
cia.active_end_date ,
cia.fa_location_id IB_fa_location,
cia.fa_sync_flag,
cte.processed_flag,
cte.error_text,
cia.active_end_date
FROM csi_transactions ct,
csi_item_instances_h ciih,
csi_inst_txn_details_v citd,
csi_i_assets cia,
mtl_system_items_b msi,
csi_txn_errors cte
WHERE 1=1

AND ct.transaction_status_code = ‘PENDING’
AND ct.transaction_id = ciih.transaction_id
AND ciih.instance_id = citd.instance_id
AND ciih.transaction_id = citd.transaction_id
AND cia.instance_id (+) = citd.instance_id
AND msi.inventory_item_id = citd.inventory_item_id
AND msi.organization_id = citd.inv_master_organization_id
AND NOT EXISTS
(SELECT ‘x’
FROM csi_inst_txn_details_v citd2
WHERE citd2.instance_id = citd.instance_id
AND citd2.transaction_id < citd.transaction_id
AND citd2.transaction_status_code = ‘PENDING’
)
AND cte.transaction_id (+) = ct.transaction_id;

Query 2 – asset transaction drill down:

You can always go to the transaction’s related instances and find out which is the earlier pending transaction using the following Blitz Report query:

SELECT citd.instance_id,
citd.transaction_id,
citd.transaction_date,
citd.inv_material_transaction_id,
citd.txn_sub_type_id,
citd.source_txn_type_name,
cia.fa_asset_id,
cia.asset_quantity,
cia.active_end_date ,
cia.fa_location_id IB_fa_location,
cia.fa_sync_flag,
cia.active_end_date
FROM csi_inst_txn_details_v citd,
csi_i_assets cia,
csi_inst_txn_details_v citd2
WHERE 1=1

AND citd2.instance_id = citd.instance_id
AND cia.instance_id (+) = citd.instance_id
ORDER BY 1, 2;

Optionally, add a parameter to the Blitz Report, e.g. transaction ID as this will allow you to analyze the details of specific failing transactions.

We simply create a new report name for each query and then paste the SQL queries 1 & 2 respectively into the Blitz Report form. There is no need to register the program with system admin as Blitz Report provides this together with it’s version control number to ensure that reports are not duplicated and can be modified with audit/version history. Reports can be assigned to a user, module, site or a responsibility.

Example: for query 1 for all ‘PENDING’ transactions, give the report a name and paste the SQL into the SQL form and then add parameters if needed or leave the status in the query and skip the parameter creation.

Finally, we can categorize the report, for example Toolkit – Business Operations, this allows quick search whenever the report is subsequently used.

Repeat the process for Query 2 – drill down and add the transaction ID parameter using LOV type ‘custom’ or you can download these reports from our library and import into your Blitz Report application.

Blitz Report can be run from any form or menu and uses the EBS user interface, so there are is virtually no new functionality to learn. The business user simply clicks on the CSI Asset Tracking reports and runs the required report. These are processed as concurrent requests which allow the user to carry on with other tasks or they can schedule on a regular basis. The Excel report opens automatically with all the data types and column sizes preset to perfection. The asset user can configure the Blitz Report layouts using simple folder style capability, thereby avoiding disturbing critical IT resources.

If you visit our library you can find the service template reports allowing you to review the sample output or to download the XML file for your own Blitz Report creation using the import utility. Typically, you would wish to extend the query to include the inventory org, subinventory, and locations. The table joins are available in the ECC Asset Tracking report, you can search our library for all of these reports and more.

 

What transactions are processed by the concurrent program ‘Create Asset: Interface Inventory Transaction to Oracle Assets’?

  • OM_SHIPMENT – when the item is a normal item and you want to capitalize the asset after OM shipment
  • WIP_ASSEMBLY_COMPLETION – when the item is a depreciating item
  • PO_RECEIPT_INTO_PROJECT – when the item is a depreciating item
  • PO_RECEIPT_INTO_INVENTORY – when the item is a depreciating item
  • MISC_RECEIPT – when the item is a depreciating item
  • PHYSICAL_INVENTORY – when the item is a depreciating item and physical inventory is a positive quantity
  • CYCLE_COUNT – when the item is a depreciating item  and cycle count is a positive quantity
  • ACCT_RECEIPT – when the item is a depreciating item
  • ACCT_ALIAS_RECEIPT – when the item is a depreciating item
  • ISSUE_TO_HZ_LOC – when the item is a normal item
  • MISC_ISSUE_HZ_LOC – when the item is a normal item

What transactions are processed by the concurrent program ‘Interface Move Transaction to Oracle Assets’?

Since this program handles all movements, the transaction type list is rather long, here are a few of the commonly used ones

  • ITEM_MOVE
  • PO_RECEIPT_INTO_INVENTORY
  • MOVE_ORDER_ISSUE_TO_PROJECT
  • SUBINVENTORY_TRANSFER
  • INTERORG_TRANSFER
  • MISC_ISSUE
  • MISC_RECEIPT
  • PHYSICAL_INVENTORY
  • CYCLE_COUNT
  • MISC_RECEIPT_FROM_PROJECT
  • MISC_ISSUE_TO_PROJECT
  • INTERNAL_SALES_ORDER
  • ACCT_ISSUE
  • ACCT_ALIAS_ISSUE
  • ISO_ISSUE
  • RETURN_TO_VENDOR
  • ACCT_RECEIPT
  • ACCT_ALIAS_RECEIPT
  • ISO_SHIPMENT
  • ISO_REQUISITION_RECEIPT
  • INTERORG_DIRECT_SHIP
  • INTERORG_TRANS_RECEIPT
  • INTERORG_TRANS_SHIPMENT

Refer to How to Troubleshoot Oracle Asset Tracking Pending Transactions (Doc ID 2423449.1) for the full list of transaction types.

Transaction integration diagram

Asset tracking process overview

 

Enterprise Command Center – Asset Tracking reports

The OAT data is synchronized with the ECC Asset Tracking dashboard. As mentioned earlier in this article, we provide an SQL version that can be run on the EBS side. The key advantage of this is that there are no data limitations and instead of CSV file format per ECC functionality, we instead provide a formatted Excel report. This can be reviewed and downloaded from our library.

User report layouts

Users can change their own report layout using folder type technology, hence removing or adding a column to the report can be done with just a few clicks, saving IT time.

Summary


When using OAT, you need to be mindful of the processing errors per the output files of the concurrent programs.

When a transaction is successfully interfaced to Oracle Assets, the transaction’s status will be updated from ‘PENDING’ to ‘COMPLETE’. When failed, it stays in ‘PENDING’. There are several reasons for transaction failures. The majority of cases are due to setups or timing:

1) OAT related setups are not completed/correct, e.g asset category, asset location, asset name.

2) The previous transaction for the same asset is still pending, e.g. asset is not created by the ‘create asset’ program so move transaction will not be processed.

3) Asset is updated from Oracle Asset which caused asset status, location out of sync with OAT record.

Analyze the PENDING records using Blitz Report and make the required corrections to data or setup per steps 1-3.

Here’s an example video on creating and running the asset tracking using Blitz report:

For more information, contact us for a live demo with your individual request. We think the best way to find out what Blitz Report can do for your business is a live demonstration. Depending on your location we can schedule an onsite appointment or a remote video call. Or in the meantime, you can review the short video overview below.

The Blitz Reportsoftware is free to use for your first 30 reports, you can plan your 30 minutes guided installation with the team at enginatics.com

About the author:

Glen Whelan is an ERP reporting specialist and has been working with Oracle E-Business Suite and its technologies since 1993 in numerous industries; with special interests in problem-solving, solution design with process simplicity, and implementation.