Business Process Automation Data Structure

As the name suggests, this module in OTM mainly deals with technical configurations that can be done in OTM for various client specific requirements.

Contacts

Contact in OTM should be defined for any communication that you want to initiate from system to end users(via email, etc) or external systems(via integration, etc). Following are entities in OTM that would be associated to contact:
Users
External Systems
Locations
Service Providers
Involved Parties
Agent Notify Contact Actions

Tables:
CONTACT
CONTACT_COM_METHOD

External System

This is used to define any system(like middleware tool etc) to which you want to send outbound Glog XMLs. You will typically associate an XML profile to control what data you are sending to external system.

Tables:
EXTERNAL_SYSTEM
EXTERNAL_SYSTEM_OUT_XML

Agents

These are workflows that trigger based on events happening with in the OTM system. Agents can be of two types - standard OTM agents that come out of box or developers can create custom agents for client specfic requirements. Agents can be triggered using standard OTM events like ORDER - CREATED, etc or custom events created by developers. We will learn in detail about Agents in a future topic.

Tables:
AGENT
AGENT_ACTION
AGENT_ACTION_DETAILS
AGENT_EVENT
AGENT_EVENT_DETAILS

Sample Query to search for specific text within agent code:

SELECT aad.*
   FROM agent_action_details aad,
               agent ag
 WHERE upper(aad.action_parameters) like upper('%TEXT TO SEARCH%')
      AND aad.agent_gid = ag.agent_gid
      AND ag.is_active = 'Y' 

Sample Query to find all parent agents that raise a given event:

SELECT a.agent_gid,
       a.description,
       action_sequence
  FROM agent a,
       agent_action_details aad
 WHERE a.agent_gid = aad.agent_gid
   AND is_active = 'Y'
   AND aad.agent_action_gid IN ('RAISE EVENT', 'FOR EACH')
   AND action_parameters LIKE '%custom event text%'
  
Reports

Report in OTM can be developed with expertise in SQL and BI Publisher tool. We will discuss in detail in a future post on how to create a custom report in OTM.

Tables:
Note: All below tables exist in REPORTOWNER schema. They can be also be accessed from GLOGOWER schema with same name because OTM created PUBLIC synonyms with same names.

REPORT
REPORT_PARAMETER

Report Set is used to group reports. This is used in REPORT agent actions like PRINT DOCUMENT action, etc

REPORT_SET
REPORT_SET_DETAIL

Report log and parameters table hold information(like output file name, input parameter values, etc) about reports:

REPORT_LOG
REPORT_LOG_PARAMETER

Sample SQL to fetch Shipment ID based on the Output file name:

SELECT rlp.parameter_value
  FROM report_log_parameter rlp
 WHERE rlp.file_name = $GID
      AND rlp.parameter_name = 'P_SHIPMENT_ID'

Integration 

OTM Inbound and Outbound Integration transmissions are recorded for every transaction with XML files, Error Details, etc.

Tables:
I_TRANSMISSION - Transmission header table with details like transmission status, external system name(for outbound), etc
I_TRANSACTION - Transmission detail table at transaction(object) level that holds XML, XML Element Name( like TransOrder, OrderRelease, etc)
I_LOG - This table shows the error details for transactions

Sample query to find transaction error details:

select it.i_transmission_no,il.i_transaction_no,dbms_lob.substr(il.i_message_text,200)
  from I_TRANSACTION it,
       i_log il
 where it.insert_date > sysdate-1  
   and i_transmission_no = <Enter Transmission No>
   and element_name = 'ShipmentStatus'
   and transaction_code = 'I'
   and it.status = 'ERROR'
   and it.i_transaction_no = il.i_transaction_no

OTM Contract & Rate Management Data Structure

Service Provider

Service Provider/Carrier is the party who provides transportation services. Service Providers typically own the trucks, equipment(containers), trailers, other resources like drivers, etc.  They receive tender offer request from customers specifying the freight details(source location, destination location, weight and volume, shipment date ranges - start time, end time, etc). Once shipment is delivered, they issue invoice to the customer for freight cost settlements.

Tables:
SCAC - every Service Provider in OTM should be associated with a SCAC code - usually a four letter code standard code associated with that Carrier
SERVPROV - Service Provider details like Service Provider name, SCAC code, transport modes, cost allocation rules, invoice approval rules, etc. A LOCATION record is also created for every SERVPROV record.

If you are manufacturing company or any business not having your own infrastructure to move freight from one location to another, you will negotiate a contract with these carriers on the:

• Transport Mode(mode of transport like TL,LTL,PARCEL)
• Rate service(like 2 day shipping, 3 day shipping etc)
• Lanes to be covered(source/dest region combinations to be served)
• Rates and discounts on these lanes depending on your volume and so on

You create a Rate Offering, Rate service, Rate Record in the OTM with above details.

Rate Service

Rate Service defines how much time it takes for shipment to reach from source to destination location. For TL and LTL rate service type  is usually defined as SIMULATION with assumption that average truck speed would be - say 50 Mile per hour, etc. For PARCELS, rate service type may be defined with type as DAYDURATION specifying number to days for your lanes. For example FedX 1 DAY service, FedX 2 DAY service, etc. All these configurations would depend on your business needs.

Tables:
RATE_SERVICE_TYPE
RATE_SERVICE_PROFILE
RATE_SERVICE
RATE_SERVICE_SPEED
SERVICE_TIME: This is to see service times associated to a particular Rate Service ID.

Rate Distance

This is used to configure how you calculate distance between source/dest location on your shipment.
You can either use standard LOOKUP/ESTIMATE or you can use external(third party) distance engine like MILEMAKER or PCMILER to calculate distance between two zip codes, cities, etc

Tables:
RATE_DISTANCE
DISTANCE_EXTERNAL_ENGINE
DISTANCE_EXTERNAL_ENGINE_PARMS

Rate Offering

Rate Offering is the contract defined with carrier confirming below details :
Service Provider
Service times(Rate Service)
Transport Mode(TL, VESSEL, etc)
Base Rate Service and Distance Service engines(for LTL)
StopOff  Charges, Accessorial costs(like fuel surchage, etc)
Currency
Contract Expiration Date, etc

Tables:
RATE_OFFERING_TYPE
RATE_OFFERING
RATE_OFFERING_STOPS
RATE_OFFERING_REMARK
RATE_OFFERING_ACCESSORIAL
RATE_OFFERING_INV_PARTY

Rate Record

Rate record holds the cost details associated with the services provided by the carrier. Depending on the business requirements costs can be defined at container level, lane based, weight/volume, etc

Tables:
RATE_GEO
RATE_GEO_COST
RATE_GEO_COST_UNIT_BREAK
RATE_GEO_COST_WEIGHT_BREAK
RATE_GEO_COST_GROUP
RATE_GEO_ACCESSORIAL
RATE_GEO_REFNUM
RATE_GEO_REMARK

OTM Shipment Management Data Structure

Shipments

  

Shipment will have - Equipment(s), Shipment Ship Units within an Equipment, and Shipment Ship Unit lines within a Shipment Ship Unit.Note that at shipment level, ship units are always tied to equipment.

So, if a order release ship unit has quantity ‘100’, it may split across two equipments with say 60 quantity going in one equipment and other 40 going into second equipment during a bulk plan. Shipment Ship Unit Line will show this split information and is critical entity which is tied to a specific item, order release line, order release and order base. If we un-assign the shipment from order release, we are just breaking this link at this level.

Note: If secondary shipments are created from primary shipment, shipment ship unit lines are shared for all the shipments. They are not created for each secondary shipment.

If OTM is receiving ASN(Shipment) directly via Integration, we can populate CIN qualifier on the Ship Unit Line refnum and use standard shipment action to link ship unit to Order Base/PO.

In this example an order has two lines and there order configuration is one to one which means one line has one ship unit only.

This example will show link between ship units on the order and ship units on the shipment.

Here we can see that ship unit on the order for item 400000879017 got split into two containers and hence into two ship units on the shipment:

Sample Queries:

select * from order_release where order_release_gid = 'DOMAIN.TST3PL1029001-001'

select * from order_release_line where order_release_gid = 'DOMAIN.TST3PL1029001-001'

select * from ship_unit where order_release_gid = 'DOMAIN.TST3PL1029001-001'

select * from ship_unit_line where order_release_line_gid in (
select order_release_line_gid from order_release_line where order_release_gid = 'DOMAIN.TST3PL1029001-001')

select * from shipment where shipment_gid = 'DOMAIN.267199'

select * from shipment_s_equipment_join where shipment_gid = 'DOMAIN.267199'

select * from s_equipment_s_ship_unit_join where s_equipment_gid in
(select s_equipment_gid from shipment_s_equipment_join where shipment_gid = 'DOMAIN.267199')

select * from shipment_stop_d where shipment_gid = 'DOMAIN.267199'

select * from s_ship_unit where s_ship_unit_gid in
(select s_ship_unit_gid from shipment_stop_d where shipment_gid = 'DOMAIN.267199')

select * from s_ship_unit_line where s_ship_unit_gid in
(select s_ship_unit_gid from shipment_stop_d where shipment_gid = 'DOMAIN.267199')

Below is the list of shipment related tables/views:

Shipment tables:

SHIPMENT - Shipment Header details
S_EQUIPMENT - Shipment Container(Equipment) details. Note incase of VESSEL shipments you will have multiple containers/equipment within the same shipment.
SHIPMENT_S_EQUIPMENT_JOIN - Shows link between Shipment and Equipments

Shipment Ship Unit tables:

S_SHIP_UNIT - Shipment Ship Unit data
S_SHIP_UNIT_REFNUM
S_SHIP_UNIT_REMARK
S_SHIP_UNIT_LINE - Shipment Shp Unit Line data
S_SHIP_UNIT_LINE_REFNUM
S_SHIP_UNIT_LINE_REMARK
S_EQUIPMENT_S_SHIP_UNIT_JOIN - Shipment Ship Units within a container
S_SHIP_UNIT_PIECE - When using load configuration feature, this table will show X,Y,Z location co-ordinates for the items placed in the container. (0,0,0) is starting point for front left corner of the container and Z co-ordinate runs on length direction of the container.This will also show the orientation details - if ship unit is placed lengthwise, width wise etc. 

Shipment Stop tables:

SHIPMENT_STOP - Shipment Stop level details like Arrival, Departure dates etc
SHIPMENT_STOP_D - Stop level shipment ship units and other details.

Equipments:

Equipments are nothing but physical containers like 53 FT, 40 FT containers in which cartons/pallets are shipped from one location to another location. These are usually shipped across various legs/transport modes(INTERMODAL). For example a container sealed in China might arrive from China supplier location to China port, same container might be loaded into a Ship(Vessel) and transported to US Port, same container is unloaded from Vessel and placed on a trailer(truck) and shipped to a warehouse in US. Please note that trucks will have two parts - Power Unit(Engine) and Trailer(that holds container). 

Table:
EQUIPMENT_GROUP - This table can hold the equipment name like 53_FTL along with dimensions.
EQUIPMENT_GROUP_PROFILE - This is logical grouping of equipment groups that can be used in various setups

Itinerary:

Itineraries define the scope for your shipment planning like what source and destinations regions are covered by your OTM panning configuration, what are the equipments that are feasible for that source/dest region combinations, what are the transport modes for that source/dest combinations, how many different legs exist between that source/dest regions, etc.

For example, if you want to import items from suppliers in CHINA to your company warehouse in USA, you might define a mult-leg itinerary with following configurations:
Leg1: Supplier Region to China Port Locations with LORRY(2T,5T,etc) as equipment group profile and TL as transport mode.
Leg2: China Port locations to US Port Locations with VESSEL as transport mode.
Leg3: US Port locations to US Warehouse Locations with US_GROUND(53FT, 40FT, etc) equipment group profile and TL as transport mode.

Identifying the itinerary is the first step that OTM Bulk Plan will perform. Note that you also need to define Rate Offering(Contract with carrier), Rate Records, etc for the source/dest region combinations, transport modes, etc.

Itinerary tables:
ITINERARY - Itinerary header with name, lane(source/dest) details
ITINERARY_DETAIL - Leg names and sequence within an Itinerary
LEG - Leg level details like transport mode, equipment group profile, etc

Shipment Tracking Events:

Once the goods are picked by the carrier and if the shipment is "in-transit", carrier can send events to OTM specifying the event location and event description(code). Below tables store the details:

IE_SHIPMENTSTATUS
SS_STATUS_HISTORY
BS_STATUS_CODE

Sample Query to fetch event details for a shipment:

select ies.i_transaction_no,shp.shipment_gid,
       ssh.event_location_gid,
       ssh.shipment_stop_num,
       to_char(ies.eventdate,'MM/DD/YYYY HH24:MI:SS'),
       ies.status_code_gid,
       bsc.description
  from shipment shp,
       ss_status_history ssh,
       ie_shipmentstatus ies,
       bs_status_code bsc
 where shp.shipment_gid = 'DOMAIN.554680'
   and shp.shipment_gid = ssh.shipment_gid
   and ies.i_transaction_no = ssh.i_transaction_no
   and ies.status_code_gid = bsc.bs_status_code_gid
ORDER BY IES.EVENTDATE

Shipment Tender:

Shipment tender info is captured in below tables:

select * from tender_collaboration where SHIPMENT_GID = 'DOM.SHP_ID'

This is key table with details like source location, destination location, expected response time, pickup time, delivery time, etc

select * from tender_collab_servprov where i_transaction_no =  <from above query>

This table will link the tender information to carrier involved, tender acceptance code, etc

select * from tender_collaboration_status where i_transaction_no =  <from above query>

This table shows the current shipment status associated to the tender.

Note that i_transaction_no is the unique reference for each tender. Response should be sent against the latest OUTSTANDING tender(i_transaction_no).

Standard OTM Views

To find Order Releases associated to shipment or vice-versa:
view_shipment_order_release

To find Order Base(PO) associated to shipment or vice-versa:
view_shipment_order_base

OTM Order Management Data Structure

Purchase Order

PO record will have the basic details like source location, destination location, INCO terms, Item number, Qty etc. Note that PO might not have exact weight/volume details that might be required for planning orders. Usually POs are created in the ERP systems well ahead of time and sent to OTM.

PO tables:

OB_ORDER_BASE - PO Header
OB_REFNUM - PO Header Reference number details
OB_REMARK - PO Header remarks
OB_ORDER_BASE_STATUS - PO Header status values
OB_INVOLVED_PARTY - PO involved party details
OB_LINE - PO Line
OB_LINE_REFNUM - PO Line reference numbers
OB_LINE_REMARK - PO Line level remarks
OB_LINE_STATUS  - PO Line level status values

Order Release:

Order Release(booking) in most cases is like a confirmed order for movement of items/goods from Point A to Point B on a specific date(date range) and this should also have the exact weight/volume of each ship unit required for transportation planning.  On the Order Release we can also have constraints defined by business like specific carrier request, itinerary request etc.

ORDER_RELEASE - Order Header
ORDER_RELEASE_REFNUM - Order Header Refnum
ORDER_RELEASE_REMARK - Order Header Remarks
ORDER_RELEASE_STATUS - Order Header Status
ORDER_RELEASE_INV_PARTY - Order Involved Party records
ORDER_RELEASE_LINE - Order Line with item details
ORDER_RELEASE_LINE_REFNUM - Order Line reference numbers
ORDER_RELEASE_LINE_REMARK - Order Line level remarks

SHIP_UNIT - Order Ship Unit
SHIP_UNIT_LINE  - Order Ship Unit Line
SHIP_UNIT_LINE_REFNUM - Ship Unit level reference numbers
SHIP_UNIT_LINE_REMARK - Ship Unit level remarks

Note that Order Ship Units are different from Shipment Ship Units. Order Ship Units are created from Order Release Line information based on 'Order Type' configuration on the Order Release. Most implementations have one to one mapping between order release line and order release ship unit. But in some cases like rainbow pallets etc. multiple items can go into single pallet(single ship unit). In this case we might see multiple ship unit lines under a single ship unit. Ship Unit is like a pallet that is actually planned to a shipment. Planning considers the weight and volume that exists against the order release ship units.

Order Movements

Order Movements are created from order releases and they honor the constraints put on the Order Release. One shipment is created for each Order Movement.

They can be created using ORDER RELEASE action or from the UI using Order Release > Actions menu.

Order Movements can be used to plan the different legs of the Order say Source location to HUB, HUB to final destination etc on different days by the planner. Say on Day1, planner want to consolidate all the orders coming from supplier to HUB location and on Day2 planner want to plan/consolidate different shipments going from HUB to inbound warehouses or customers.

Business use case: In XXX company, when supplier books the order, order release is created. If Items on the order release is having  ‘DAYS ON HAND’ refnum value less than 14 days, then Agent will update the Itinerary as DIRECT Itinerary instead of HUB Itinerary. Difference between Direct and HUB Itinieraray is that HUB itinerary will have two legs – source location to HUB(California HUB etc), California HUB to Destination location etc. Since this constraint is updated on the order release, once we call standard action ‘CREATE ORDER MOVEMENTS’ it will create two order movements in case of HUB Itinerary and users can plan these order movements. This will have better control of planning.

Below are the Order Movement related tables:

ORDER_MOVEMENT

ORDER_MOVEMENT_STATUS

ORDER_MOVEMENT_REFNUM

ORDER_MOVEMENT_REMARK

ORDER_MOVEMENT_INV_PARTY

Items

Item is like an individual unit that is sold by a manufacturer like a pencil box.

Table:

ITEM

Ship Unit Spec(Packaging Unit)

Ship Unit Spec - also referred to as Pallet or Transportation Handling Unit(THU) or Carton is similar to a box with specific dimensions - LxWxH. This is linked to the Ship Unit on an order. This is the unit that will be physically shipped and whose dimensions are critical for Order Planning.

Table:

SHIP_UNIT_SPEC 

Packaged Item:

Packaged item links your item to ship unit spec(pallet). Packaged Item is the entity mentioned on the Order Base(PO), Order Release, etc

This will have information like:

• Item ID
• Packaging Unit( which is Ship Unit Spec defined above in most cases)
• Package dimensions
• Ti/High – This term is used in logistic business to indicate number of cartons per layer(Ti) and number of layer(High) used on the pallet.

Table:

PACKAGED_ITEM

Notes: Items > Packaged Items > Ship Unit Spec > Equipment/Container > Shipment would be packaging hierarchy. But most implementations will have Item ID same as Packaged Item ID and use Ship Unit spec like a CARTON(box) with Ship Unit dimensions same as Ship Unit Spec dimensions. Ship Units go in Equipment.

Note: Please post corrections(if any) to 'learnotm@outlook.com' 

OTM Domains, Standard Schema and Data Dictionary

Domains:

OTM Data is organized into domains. Each domain is like grouping of data / business transactions based on the different business units with in the same organization.

If there is a corporation with logistics requirements across various business units like "Merchandise Shipping", "Food and Beverages", etc - you typically create one domain for each of these business units so that users and their roles can be tied to each business unit. User/role tied to a particular domain can only access data for their specific domain/business unit. Also advantage with domains is that customization specific to a business unit can be better controlled/maintained. For example, OTM Agents(workflows) with custom code can be developed specific to each domain so they they trigger only for transactions within that domain.

Domains can be created in hierarchical manner - like one parent domain at corporation level and child domains at business unit level under the same parent domain. With this structure you can maintain customization/features common to both the child domains at parent domain level. For example, you may define Item Numbers at parent domain level these items can be used on both the child domain transactions.

Each table in OTM will have DOMAIN_NAME as column to distinguish both setup/transactional data between domains. Also each table will have GID/XID columns for each business object. For example a PO will have ORDER_BASE_GID and ORDER_BASE_XID:

• ORDER_BASE_GID to represent a unique system generated/user entered identifier(primary key in most cases) that takes format "Domain Name"."PO Number" (For example,  DMN.12345 would be ORDER_BASE_GID value where DMN is the domain name and 12345 is the PO Number) 
• ORDER_BASE_XID to represent actual PO number that can be recognized by business users like free text value. Most of the cases business users might  use same value for both these GID/XID columns.

Standard OTM Schema:

GLOGOWNER Schema:  This schema is used to maintain all the transnational data like PO Data, Order Release data, Shipment data, etc.

REPORT OWNER Schema: This schema is used to store report definitions, oracle stored procedures that will be used by BI Publisher Query templates, etc. For objects created in this schema we should create a public synonym to make the object accessible to OTM application.

OTM Data Dictionary

Once you know the table name for a business object you can get more details from OTM Data Dictionary. In the OTM URL place "html/data_dict" next to "GC3" to access data dictionary

Example: www.otm-oracle.com/GC3/html/data_dict/

Note:  There is '*' against the columns to represent primary key combination and next to each column is Foreign Key information within the brackets.

Below example says - SHIP_UNIT_LINE table has SHIP_UNIT_GID, SHIP_UNIT_LINE_NO as primary key combination and SHIP_UNIT_LINE.SHIP_UNIT_GID references SHIP_UNIT.SHIP_UNIT_GID.



Note: Please post corrections(if any) to 'learnotm@outlook.com'