Data Flow to Transportation Intelligence

Note: You need OAS and ODI to use Transportation Intelligence (TI). All the dashboards and detailed reports are rendered using OAS and the data is extracted using ODI.

The data from Oracle Transportation Management flows to the operational database. Starting in 6.2 ROD is optional. The ETL (Extraction, Transformation and Loading) process extracts the data from Oracle Transportation Management or replicated database, transforms and loads this data to a separate HD database schema. This is done using ODI (Oracle Data Integrator). You can manually execute or schedule the ETL process using process management. You can view the data loads and data load rejects using the Data Management page. You must be a DBA to access both these pages.

Load Data to HD

The following statuses determine if the ETL process should extract this data and load these objects and all related data to the HDOWNER schema for TI. The status is NOT_READY_TO_LOAD when the business object is created. You need to create an automation agent to set these statuses. If you have an active agent, once the status is changed to ready to load, the ETL process will run. Refer to the Administration Guide on the Oracle Help Center for details on setting up these automation agents.

OBJECT TYPE	STATUS_TYPE_GID	STATUS_VALUE_GID	CONSIDERED BY ETL
ORDER_BASE	OB_HD_LOAD_STATUS	OB_NOT_READY_TO_LOAD	N
ORDER_BASE	OB_HD_LOAD_STATUS	OB_READY_TO_LOAD	Y
ORDER_RELEASE	OR_HD_LOAD_STATUS	OR_NOT_READY_TO_LOAD	N
ORDER_RELEASE	OR_HD_LOAD_STATUS	OR_READY_TO_LOAD	Y
SHIPMENT	SHIPMENT_HD_LOAD_STATUS	SHIPMENT_NOT_READY_TO_LOAD	N
SHIPMENT	SHIPMENT_HD_LOAD_STATUS	SHIPMENT_READY_TO_LOAD	Y

Loading of Invoices

Invoices are loaded based on the status of the shipment associated to the invoice. The shipment should have a SHIPMENT_READY_TO_LOAD status value as detailed in the table above. In addition to this condition at least one of the following should fall in the date range of the previous load time and the current load time in order to load the invoice:

Shipment insert date or the shipment update date
Invoice insert date or invoice update date
Shipment_Status insert date or Shipment_Status update date of the associated shipment

The exception is stand-alone invoices which are loaded into TI based on the glog.fti.standAloneInvoice property.

Loading of Order Movements

Order movements are loaded if the following conditions are met:

The Network Routable option on the order movement is NOT selected.

OR
The order movement status type of OM_PLANNING is any one of the following status values:
- OM_PLANNING_NEW
- OM_PLANNING_UNSCHEDULED
- OM_PLANNING_PLANNED - FAILED

Unload Data from HD

You can unload (soft delete) data from HD. There are several agent actions intended for use with Transportation Intelligence. This is similar to the Unload Object from HD available via power data. The available agent actions for unloading data from Transportation Intelligence are as follows:

Uploading Targets via CSV

You can add targets to OTM via a CSV file and then load the targets to the TI database. For an updated list of all targets along with all other dimensions and facts in the presentation layer, refer to the FTI x.x - Fact, Dimension, and Target List spreadsheet available from the My Oracle Support note: Oracle Transportation Management and Global Trade Management Version 6.x Documentation and Training Resources (Doc ID 796594.1).

Uploading Targets:

Create a CSV file containing your targets. This CSV file must be in the format as expected by the OTM database. To determine this format, use CSV Export to export the E_FTI_TARGETS table.
Edit the CSV file as necessary.
Note: There is no validation for any of the columns in the CSV during the Upload Target CSV process. So you must enter the correct value in the target_name column. The value entered into the target_name column must exactly match the name of the target when it is viewed via TI Ad Hoc Query. If you misspell a target name, then a new target is created and the existing target is not updated. For example, the order release target of Average Actual Transit Time must have a target_name of AVERAGE ACTUAL TRANSIT TIME in the E_FTI_TARGETS CSV file.
Run Upload Target CSV from power data to load the updated CSV files containing the targets into the OTM database.
In process management, use Run Targets to load the target data from the OTM database into the TI database.

Limitation: Unable to set target value for a specific quarter. When target values are set for a quarter the same values are also shown in summation for the month targets.