Data Models
A data model defines where data for a report comes from and how that data is retrieved and how the data is structured. If the existing data model cannot give you all the data that you need in your report, then you can either copy and edit an existing data model or create a new one.
Refer to the Part I Model Data for Pixel-Perfect Reports section of the Designing and Publishing Pixel-Perfect Reports in Oracle Analytics Server guide.
Notes
Below are some of points to remember about the data model components while creating the data model.
- Event triggers will not be useful for the Logistics cloud as the customers will not have the access to create or edit PL/SQL packages, procedures.
- Lists of values will only be useful if you are trying to run the reports from the Oracle Analytics Publisher. The LOVs cannot be used from Transportation and Global Trade Management Cloud application.
- Bursting definitions are not used by Transportation and Global Trade Management Cloud for the distribution of reports to different destinations. Transportation and Global Trade Management Cloud uses its own infrastructure to distribute the reports to email and printers.
- Custom metadata is not supported for reports integrated to Transportation and Global Trade Management Cloud.
- Although there are so many options for creating the Data Sets, Transportation and Global Trade Management Cloud only supports data sets created using SQL queries.
Utility Packages
Transportation and Global Trade Management Cloud provides a number of PL/SQL
functions/procedures that can be used in Oracle Analytics Publisher Data Models. The
following table summarizes the available procedures and functions. For more information,
please review the create_rpt scripts in
OTM_INSTALL_DIR>\glog\oracle\script8.
| Package | Procedure/Function | Description | Parameters | Returns |
|---|---|---|---|---|
| vpd | set_user | Sets the user for VPD data security | Transportation and Global Trade Management Cloud user GID | -- |
| set_user_r | Sets the user and user role for VPD data security |
Transportation and Global Trade Management Cloud user GID Transportation and Global Trade Management Cloud user role GID |
-- | |
| set_user_fct | Sets the user for VPD data security. Designed for use as the first query in a Data Template | Transportation and Global Trade Management Cloud user GID | true | |
| set_user_r_fct | Sets the user and user role for VPD data security. Designed for use as the first query in a Data Template |
Transportation and Global Trade Management Cloud user GID Transportation and Global Trade Management Cloud user role GID |
true | |
| get_gl_user | Returns the current user for VPD data security | -- | Transportation and Global Trade Management Cloud user GID | |
| fnc_set_user_r | Sets the user and user role for VPD data security |
Transportation and Global Trade Management Cloud user GID Transportation and Global Trade Management Cloud user role GID |
true | |
| utc | get_local_date | Converts a UTC timestamp to a location time zone |
UTC Timestamp Location GID |
Timestamp in the location’s time zone |
| get_utc_date | Converts a local timestamp to a UTC timestamp based on a location time zone |
Local Timestamp Location GID |
UTC Timestamp | |
| get_time_zone | Returns the time zone for a location | Location GID | Time Zone | |
| rpt_general | p_insert_log | Logs a record to REPORT_LOG |
Unique Filename Report GID Report Job # User Domain up to 3 (name, value) pairs |
-- |
| f_uom_base | Returns the default storage type for a unit of measure | Unit of measure | Default storage type | |
| f_date_diff | Returns a readable string representing the duration difference of two dates |
Ending date Starting date |
Duration string | |
| f_remove_domain | Strips the domain from a GID | GID | XID | |
| f_format_address | Returns a formatted address for a location | Location GID | Formatted, multi-line address for the location | |
| f_corporation | Returns the corporation for a location | Location GID | Corporation GID | |
| f_location_refnum | Returns the value of a specific location reference number |
Location GID Reference number qualifier |
Reference number value | |
| rpt_order | f_ob_refnum | Returns the value of a specific order base reference number |
Order Base GID Reference number qualifier |
Reference number value |
| rpt_ship | f_commodity | Returns the commodity name for a specific item | Item GID | Commodity name |
| f_ob_party_location | Returns the location for a specific order base involved party |
Order Base GID Involved party qualifier |
Location GID | |
| f_or_party_location | Returns the location for a specific order release involved party |
Order Release GID Involved party qualifier |
Location GID | |
| f_order_base_gid | Returns the order base associated with an order release | Order Release GID | Order Base GID | |
| f_party_address | Returns a formatted address for an order release involved party |
Order Release GID Involved party qualifier |
Formatted, multi-line address for the location | |
| f_tender_accepted_by | Returns the carrier that accepted a shipment tender | Shipment GID | Service Provider GID | |
| f_packaging_form_code | Returns the packaging form code for a ship unit specification | Ship Unit Specification GID | Packaging Form Code GID | |
| f_capacity_rate_offering | Returns the rate offering for a capacity usage | Capacity Usage GID | Rate Offering GID | |
| f_capacity_time_period | Returns the time period type for a capacity limit | Capacity Limit GID | Time Period Type | |
| f_lane_source | Returns the source for a lane | XLane GID | Source | |
| f_lane_destination | Returns the destination for a lane | XLane GID | Destination | |
| f_equipment_type_name | Returns the name for an equipment type | Equipment Type GID | Equipment Type Name | |
| f_sellside_cost | Returns the sell-side cost for a shipment | Shipment GID | Sell-side cost | |
| f_transport_mode_name | Returns the transport mode for a shipment | Shipment GID | Transport Mode | |
| f_get_ship_inv_party_addr | Returns a formatted address for a shipment involved party |
Shipment GID Involved Party Qualifier |
Formatted, multi-line address for the involved party | |
| f_get_country_name | Returns the origin or destination country name for a shipment |
Shipment GID ‘O’ for origin, ‘D’ for destination |
Country name | |
| f_get_pol | Returns the port of lading for a shipment | Shipment GID | Port of lading | |
| rpt_invoice | f_party_location | Returns an involved party location on the invoice |
Invoice GID Involved Party Qualifier |
Involved party location |
| f_party_address | Returns a formatted address for an invoice involved party |
Invoice GID Involved Party Qualifier |
Formatted, multi-line address for the involved party | |
| rpt_servprov | f_servprov_gid | Returns the Service Provider GID for a particular alias |
Service Provider Alias Service Provider Alias Qualifier |
Service Provider GID |
| f_alias | Returns the Service Provider alias |
Service Provider GID Service Provider Alias Qualifier |
Service Provider Alias |
Parameters
Adding parameters to your data model enables users to interact with data when they submit
or view reports. Transportation and Global Trade Management Cloud passes standard and ad
hoc parameters to the report generator (If external report generators do not support
these parameters, their content type should be set to None.). The
following table summarizes the standard parameters passed to every report.
| Name | Description | Comments |
|---|---|---|
| P_REPORT_GID | The requested report | External systems are responsible for mapping the Transportation and Global Trade Management Cloud report GID to a valid report. Alternatively, the report URL can embed the mapping within a request parameter. |
| P_DBCONN_TYPE | The data schema | OLTP for the Transportation and Global Trade Management Cloud transactional database; ODS for the offline analytical database; ARCHIVE for the archived data. |
| P_GL_USER | The user requesting the report | This may be used by external systems to enforce VPD data security on report queries. |
| P_ROLE_ID | The role of the requesting user | This may be used by external systems to enforce VPD data security on report queries. |
| P_DOMAIN | The domain of the requesting user | |
| P_LANGUAGE | The ISO language code requested for the report | For embedded reports, XLIFF translations are automatically applied. |
| P_COUNTRY | The ISO country code requested for the report | For embedded reports, XLIFF translations are automatically applied. |
| P_DISPLAY_NAME | A user-readable name for the report. | |
| P_DATE_FORMAT | The Oracle date format for both input parameters and output fields. |
For input parameters, use the For output parameters, use the
This should be reserved for fields that are date-only. |
| P_DATE_TIME_FORMAT | The Oracle timestamp format for both input parameters and output fields. |
For input parameters, use the
function. For output fields, use the
This should be reserved for fields that have both date and time. |
The above defined list of standard parameters can be used in any of the report you are designing. If you define any of these standard parameters as a parameter for the report, you do not have to define it as a parameter when defining the report in Transportation and Global Trade Management Cloud.
You will have to define any other parameter required for the report apart from the standard parameters as a report parameter when defining the report definition in Transportation and Global Trade Management Cloud.
Data and Timestamp Handling
Report designers should take special care when writing queries involving DATE columns. This includes:
Converting date parameters for where clause comparison: All date
parameters are sent as strings, formatted according to your date preferences. To compare
one to a data field, the designer should use Oracle’s TO_DATE
function, applying the standard P_DATE_FORMAT parameter:
where accessorial_cost.effective_date > TO_DATE(:P_EARLIEST_DATE, :P_DATE_FORMAT)
and accessorial_cost.effective_date < TO_DATE(:P_LATEST_DATE, :P_DATE_FORMAT)
Converting timestamp parameters for where clause comparison: Like dates, timestamp parameters are sent as strings, formatted according to your date/time preferences (Note that the reporting has never applied user time preference. Time preference is assumed to be HH24:MI:SS.). To compare one to a timestamp field, the designer should use Oracle’s TO_DATE function, applying the standard P_DATE_TIME_FORMAT parameter:
where shipment.start_time > TO_DATE(:P_EARLIEST_START, :P_DATE_TIME_FORMAT)
and shipment.start_time < TO_DATE(:P_EARLIEST_START, :P_DATE_TIME_FORMAT)
Applying date and time preferences: Depending on the use case,
designers may want to apply user preferences to dates displayed on the final report. If
so, the SQL query should convert selected date and timestamp values using Oracle’s
TO_CHAR function:
select TO_CHAR(accessorial_cost.effective_date, :P_DATE_FORMAT) …
select TO_CHAR(shipment.start_time, :P_DATE_TIME_FORMAT) …
Accounting for UTC storage: Nearly all timestamp fields in
Transportation and Global Trade Management Cloud are converted to UTC before persisting
to the database. A report designer who simply queries
shipment.start_time, for example, receives the time in UTC. To
convert the stored time to the application or report server’s time zone, use the
vpd.gmt_offset function:
select TO_CHAR(shipment.start_time-(vpd.gmt_offset/24), :P_DATE_TIME_FORMAT) …Accounting for UTC storage: To compare timestamp fields to some
offset of current time, designers can either convert the field or use
vpd.gmt_sysdate. This function returns the current time in UTC.
E.g. to query shipments starting in the next three days:
select shipment.gid
where shipment.start_time > vpd.gmt_sysdate
and shipment.start_time < vpd.gmt_sysdate+3