Your Transportation Management Data Template
Your Transportation Management Data Template
- Recommended attributes to collect
- Key activities to track
- Extraction guidance for SAP Transportation Management
Transportation Management Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The name of a specific business event or step that occurred within the transportation process. | ||
| Description The Activity Name describes a single task or milestone in the shipment's lifecycle, such as 'Shipment Planned', 'Goods Picked Up', or 'Goods Delivered'. These activities form the nodes in the discovered process map. Analyzing the sequence and frequency of activities helps identify the standard process flow, deviations, bottlenecks, and rework loops. It is the backbone of process discovery and conformance checking in transportation management. Why it matters This attribute defines the steps in the transportation process, allowing for the visualization and analysis of the end-to-end shipment flow. Where to get This is typically derived from event codes or status changes recorded in the SAP TM execution tracking event handler. It may be sourced from tables that log business object status changes or specific event messages. Examples Shipment PlannedCarrier AssignedGoods Picked UpGoods DeliveredProof Of Delivery Received | |||
| Event Time EventTime | The timestamp indicating when a specific activity or event occurred. | ||
| Description Event Time provides the precise date and time for each activity in the transportation process. This temporal data is fundamental for calculating cycle times, durations, and waiting times between different process steps. In analysis, this attribute is used to sequence events correctly, build a chronological log, and perform all time-based performance analysis. It is critical for identifying delays, measuring process efficiency, and evaluating performance against schedules or service level agreements. Why it matters The timestamp is essential for ordering events chronologically and calculating all performance metrics, such as cycle times and on-time delivery. Where to get Sourced from the event handler tables in SAP TM, such as /SAPTRX/EH_EVMSG, which store timestamps for reported events. Examples 2023-04-15T10:00:00Z2023-04-16T14:30:00Z2023-04-20T09:15:00Z | |||
| Shipment ShipmentId | The unique identifier for a single movement of goods, which serves as the central case for analysis. | ||
| Description The Shipment ID is the primary key that groups all related transportation activities and events. It represents a single, end-to-end transportation process, from the initial request to the final proof of delivery and payment. In process mining, this attribute is essential for constructing the case view, allowing analysts to trace the complete journey of each shipment. It enables the analysis of process variants, cycle times, and outcomes associated with individual shipments, providing a comprehensive view of transportation performance. Why it matters This is the fundamental case identifier, linking all events and data points to a specific transportation order, which is crucial for end-to-end process analysis. Where to get This identifier is the Freight Order number or Freight Booking number in SAP TM. It can be found in tables like /SCMTMS/D_TORROT and /SCMTMS/D_TRQROT. Examples 610000432161000056786100009876 | |||
| Carrier Name CarrierName | The name of the transportation carrier or logistics service provider assigned to the shipment. | ||
| Description The Carrier Name identifies the company responsible for physically moving the goods. This is a critical dimension for performance analysis, as different carriers may have varying levels of service, cost, and reliability. In process mining, this attribute is used to segment and compare performance across various carriers. It allows for the creation of carrier scorecards, monitoring on-time delivery rates per carrier, comparing costs, and identifying which carriers are most frequently associated with delays or disputes. This insight is vital for strategic carrier management and procurement. Why it matters Enables performance comparison across different carriers, which is key for optimizing carrier selection, cost, and service levels. Where to get This information is stored on the Freight Order or Freight Booking document. It is linked via the business partner function for the carrier. Examples DHL ExpressFedEx FreightMaersk LineUnion Pacific Railroad | |||
| End Time EndTime | The timestamp indicating when a specific activity was completed. | ||
| Description While StartTime indicates the beginning of an event, EndTime marks its conclusion. This is particularly useful for activities that have a duration, such as 'Customs Clearance' or 'Goods Loading'. The difference between EndTime and StartTime represents the processing time of that activity. In process mining analysis, having both a start and end time enables more granular analysis of activity durations, allowing for the separation of processing time from waiting time. This helps pinpoint exactly where inefficiencies lie, whether within the execution of a task or in the handoffs between tasks. Why it matters Allows for the precise calculation of activity processing times, distinguishing active work time from waiting time between events. Where to get Like StartTime, this is sourced from event handler tables. It may correspond to a separate 'completed' event for a given activity. Examples 2023-04-15T11:30:00Z2023-04-16T15:00:00Z2023-04-20T09:45:00Z | |||
| Is On-Time Delivery IsOnTimeDelivery | A boolean flag indicating if the goods were delivered on or before the requested delivery date. | ||
| Description This is a calculated attribute that compares the timestamp of the 'Goods Delivered' activity with the 'Requested Delivery Date'. It evaluates to true if the delivery was on time or early, and false if it was late. This attribute simplifies performance analysis by creating a clear binary outcome for each shipment. It is the basis for the On-Time Delivery Performance Dashboard and the corresponding KPI. It allows analysts to easily filter for late shipments and perform root cause analysis to understand the drivers of poor performance. Why it matters Directly measures adherence to delivery commitments, simplifying the calculation and visualization of the critical on-time delivery KPI. Where to get This attribute is not stored in SAP TM. It must be calculated during data transformation by comparing the actual delivery event timestamp to the Requested Delivery Date. Examples truefalse | |||
| Mode of Transport ModeOfTransport | The method of transportation used for the shipment, such as road, rail, air, or sea. | ||
| Description This attribute specifies the transportation mode. Different modes have distinct cost structures, transit times, and process complexities. For example, an international air freight shipment will have different activities, like customs clearance, compared to a domestic truckload shipment. Analyzing processes by transport mode allows for tailored improvement initiatives. It is also critical for cost analysis, especially for identifying the usage of premium freight (e.g., expedited air) versus standard, more economical modes. This segmentation is key to understanding and controlling transportation spending. Why it matters Enables analysis of cost, time, and process variations based on the transportation mode, which is crucial for cost optimization and identifying premium freight usage. Where to get This is a key field on the Freight Order or Freight Booking, often labeled as Transportation Mode or MOT. Examples 01 (Road)02 (Rail)04 (Air)05 (Sea) | |||
| Requested Delivery Date RequestedDeliveryDate | The delivery date and time requested by the customer or stipulated in the sales order. | ||
| Description The Requested Delivery Date is the target deadline for the 'Goods Delivered' activity. It serves as the primary benchmark for measuring on-time delivery performance. This date is crucial for calculating the 'On-Time Delivery Rate' KPI. By comparing the actual delivery timestamp with this requested date, organizations can determine if they met their service level agreement. Analyzing deviations helps identify systemic causes of delays, such as carrier performance issues, planning delays, or customs hold-ups. Why it matters This is the baseline for measuring on-time delivery performance, a critical KPI for customer satisfaction and operational excellence. Where to get This date is typically transferred from the preceding sales order or stock transport order from SAP ERP. It is stored in the date/time fields of the Freight Unit or Freight Order. Examples 2023-05-20T17:00:00Z2023-06-01T12:00:00Z2023-06-15T23:59:59Z | |||
| Total Freight Cost TotalFreightCost | The total calculated or invoiced cost for the shipment. | ||
| Description Total Freight Cost represents the financial impact of a shipment. This can be the cost calculated by the transportation management system or the final amount from the carrier's invoice after settlement. This attribute allows for the financial dimension to be integrated into process analysis. Analysts can identify the most expensive process variants, calculate the cost of delays or reroutes, and compare the cost-effectiveness of different carriers or transport modes. It is fundamental for dashboards focused on transportation spend and cost-to-serve analysis. Why it matters Adds a financial dimension to the process, enabling analysis of transportation spend, cost per shipment, and the financial impact of process inefficiencies. Where to get Sourced from the Freight Settlement Document in SAP TM after charge calculation has been performed. It can be found in tables like /SCMTMS/D_SFIRHD. Examples 250.751200.005430.50 | |||
| Delay Reason DelayReason | A code or text explaining the reason for a delay in the shipment. | ||
| Description The Delay Reason attribute captures the cause of a deviation from the planned schedule, such as 'Bad Weather', 'Customs Hold', or 'Carrier Delay'. This information is often reported manually or via an event message from the carrier. This is a powerful attribute for root cause analysis. It allows analysts to move beyond simply knowing a shipment was late to understanding why. By categorizing and quantifying delay reasons, organizations can identify recurring problems and focus their improvement efforts on the most impactful areas, like improving carrier communication or optimizing routes to avoid congestion. Why it matters Explains the 'why' behind shipment delays, enabling powerful root cause analysis to reduce exceptions and improve on-time performance. Where to get This can be captured as an event code and reason in SAP TM's event management framework when an 'Arrival/Departure Delayed' event is reported. Examples WEATHER_DELAYCUSTOMS_INSPECTIONPORT_CONGESTIONMECHANICAL_FAILURE | |||
| Destination Country DestinationCountry | The country where the shipment is being delivered. | ||
| Description This attribute identifies the final destination country for the shipment, derived from the address of the last delivery location. Similar to the origin country, analyzing by destination country is crucial for understanding regional logistics challenges. It can reveal patterns such as which destinations are most prone to delivery delays or have higher last-mile delivery costs. This insight is valuable for network optimization, setting realistic delivery expectations, and managing international trade compliance. Why it matters Enables analysis of process performance by destination, highlighting issues with specific delivery regions, customs authorities, or last-mile carriers. Where to get This is part of the address information for the destination location of the final stop in the Freight Order. Examples FRCAGBJP | |||
| Dispute Reason DisputeReason | The reason provided when a freight invoice is disputed. | ||
| Description When a carrier's invoice does not match the expected cost calculated in SAP TM, a dispute is created. This attribute captures the reason for that discrepancy, such as 'Incorrect Rate', 'Duplicate Invoice', or 'Accessorial Charge Not Approved'. Analyzing dispute reasons is critical for the Freight Bill Error and Dispute Rate dashboard. It helps identify the root causes of billing inaccuracies, whether they stem from master data issues, carrier errors, or operational failures. This insight allows the organization to take targeted actions to reduce disputes, which in turn speeds up the payment cycle and reduces administrative overhead. Why it matters Provides the root cause for freight billing errors, enabling targeted actions to reduce invoice disputes and improve the settlement process. Where to get This information is captured in the dispute management case associated with a Freight Settlement Document. Examples Rate MismatchIncorrect WeightUnapproved Demurrage ChargeDuplicate Invoice | |||
| Is Disputed IsDisputed | A boolean flag indicating if the freight invoice for the shipment resulted in a dispute. | ||
| Description This calculated flag identifies shipments that have an associated freight settlement dispute. It evaluates to true if a dispute was created, and false otherwise. It can be derived from the presence of a 'Freight Dispute Created' activity or a non-empty 'Dispute Reason'. This attribute simplifies the analysis of the freight settlement process. It allows for easy calculation of the Freight Billing Discrepancy Rate KPI and helps in filtering for all disputed shipments. Analysts can then investigate the common characteristics of these shipments, such as carriers, lanes, or charge types, to find the root cause of billing errors. Why it matters Simplifies the identification of problematic freight settlements, enabling quick calculation of the dispute rate and root cause analysis of billing errors. Where to get This attribute is not stored in SAP TM. It must be calculated based on the existence of a dispute case linked to the Freight Settlement Document. Examples truefalse | |||
| Last Data Update LastDataUpdate | The timestamp of the most recent data extraction or refresh for the process mining dataset. | ||
| Description This attribute indicates the freshness of the data being analyzed. It reflects the date and time when the data was last pulled from the source systems, like SAP TM, into the process mining tool. This is a critical metadata element for any dashboard or analysis, as it provides context on how current the insights are. It helps users understand if they are looking at real-time information or a snapshot from a previous period, which is essential for making timely and informed decisions. Why it matters Provides crucial context about the timeliness of the data, ensuring users are aware of how current the analysis is. Where to get This is typically generated and stored by the ETL (Extract, Transform, Load) tool or data pipeline during the data ingestion process. Examples 2023-10-27T08:00:00Z2023-10-26T08:00:00Z | |||
| Origin Country OriginCountry | The country from which the shipment originates. | ||
| Description This attribute identifies the starting country for the transportation journey. It is derived from the address of the initial pickup location. Analyzing the process by origin country helps to identify regional differences in performance, cost, and complexity. It can highlight countries with longer customs clearance times, higher carrier costs, or more frequent delays. This geographical segmentation is essential for managing a global supply chain and tailoring logistics strategies to specific regions. Why it matters Allows for geographical analysis of shipment performance, helping to identify regional bottlenecks, cost drivers, or process variations. Where to get This is part of the address information for the source location of the first stop in the Freight Order. Examples DEUSCNMX | |||
| Scheduled Pickup Date ScheduledPickupDate | The planned date and time for the carrier to pick up the goods from the shipping point. | ||
| Description The Scheduled Pickup Date is the agreed-upon time for the start of the physical transportation. It is a key milestone in the execution phase of the shipment. This attribute is essential for monitoring the timeliness of the first leg of the journey. By comparing the scheduled pickup time with the actual 'Goods Picked Up' event timestamp, companies can calculate pickup delays. This helps in evaluating the performance and reliability of both the shipping location and the carrier. Why it matters Serves as the baseline for measuring pickup timeliness, helping to identify delays at the very beginning of the shipment's physical journey. Where to get This is a planned date/time stored on the stop sequence of the Freight Order in SAP TM. Examples 2023-05-18T09:00:00Z2023-05-30T14:00:00Z2023-06-12T10:30:00Z | |||
| Shipment Cycle Time ShipmentCycleTime | The total duration of the shipment from the initial request to the final proof of delivery. | ||
| Description This is a calculated metric representing the end-to-end cycle time for a shipment. It is typically calculated as the difference between the timestamp of 'Proof of Delivery Received' and 'Transportation Request Received'. Shipment Cycle Time is a primary KPI for measuring the overall efficiency of the transportation process. It provides a high-level view of performance, and when analyzed over time or across different dimensions like carrier or route, it can reveal trends and major bottlenecks. It is essential for the End-to-End Shipment Cycle Time dashboard. Why it matters Measures the total end-to-end duration of the transportation process, providing a key indicator of overall process velocity and efficiency. Where to get This is a calculated metric, derived by subtracting the start time of the first event from the end time of the last event for each case. Examples 5 days 4 hours12 days 8 hours22 hours 30 minutes | |||
| Shipment Status ShipmentStatus | The current overall status of the shipment at the time of data extraction. | ||
| Description Shipment Status provides a snapshot of where the shipment is in its lifecycle, such as 'In Planning', 'In Execution', 'Delivered', or 'Cancelled'. It represents the last known state of the case. This attribute is useful for filtering and analyzing shipments based on their outcome. For example, an analyst can focus on only completed shipments to measure end-to-end cycle time or analyze only cancelled shipments to understand the root causes of cancellations. It provides important context for interpreting the process flow. Why it matters Provides a high-level outcome for each shipment, allowing for filtering and analysis based on whether a shipment was completed, cancelled, or is still in transit. Where to get This is the overall status field on the header of the Freight Order or Freight Booking document in SAP TM. Examples In PlanningReady for TransportationIn ExecutionExecutedCancelled | |||
| Source System SourceSystem | The system from which the event data originated. | ||
| Description This attribute identifies the source system that generated the data, for example, SAP TM, SAP ERP, or an external carrier portal. It provides context about the data's origin and helps in understanding data integration points. In a complex landscape, knowing the source system is crucial for data validation, troubleshooting integration issues, and understanding how different systems contribute to the overall process. For instance, it can differentiate between an event reported by an internal system versus one reported by a third-party logistics provider. Why it matters Identifies the origin of the data, which is crucial for data governance, troubleshooting, and understanding process orchestration across multiple systems. Where to get This information is often stored as part of the system log or can be derived based on the interface or user who created the data entry. Examples SAP_TM_P01SAP_ERP_E01CARRIER_PORTAL_XYZ | |||
| User Name UserName | The user ID or name of the person who executed an activity. | ||
| Description This attribute identifies the employee or user responsible for performing a manual activity, such as 'Shipment Planned' or 'Invoice Verified'. It links process steps to the individuals or teams who performed them. Analyzing activities by user helps in understanding workload distribution, identifying training needs, and recognizing top performers. It is also crucial for compliance and audit purposes, providing a clear record of who did what and when. This can reveal variations in how different users perform the same task. Why it matters Attributes manual process steps to specific users, enabling analysis of workload, individual performance, and training opportunities. Where to get Typically found in the change log or document header tables for transportation documents, such as the 'Created By' or 'Changed By' fields (e.g., ERNAM, AENAM). Examples CB998030SMITHJPLANNER_EU | |||
Transportation Management Activities
| Activity | Description | ||
|---|---|---|---|
| Carrier Assigned | This activity indicates that a specific transportation service provider, or carrier, has been selected and assigned to the shipment. This can occur through manual assignment, automated planning, or a tendering process. | ||
| Why it matters Assigning a carrier is a critical step before a shipment can be confirmed and executed. Tracking this helps in analyzing carrier selection processes and ensuring timely booking. Where to get This is captured when the carrier field (party with role 'Carrier') is populated in the Freight Order or Freight Booking. For tendering, it is when a Freight Request for Quotation is awarded. Capture Identify the timestamp when the carrier partner function is first populated or confirmed in the Freight Order. Event type inferred | |||
| Freight Settlement Created | This activity marks the creation of the Freight Settlement Document (FSD), which calculates the transportation costs to be paid to the carrier. This is the primary document used for accruals and invoice verification. | ||
| Why it matters This begins the financial settlement phase of the transportation process. Analyzing the time from delivery to settlement creation helps identify bottlenecks in financial processing. Where to get This is an explicit event captured by the creation of a Freight Settlement Document. The creation timestamp is available in the header table for this document, /SCMTMS/D_SFIRHD. Capture Use the creation date and time of the Freight Settlement Document. Event type explicit | |||
| Goods Delivered | This activity represents the arrival of the shipment at the final destination and the completion of the physical transportation. It is captured when the carrier reports that the goods have been unloaded at the consignee's location. | ||
| Why it matters This is the most important milestone for measuring on-time delivery performance and customer satisfaction. It also typically serves as the trigger for invoicing and settlement processes. Where to get This is recorded as an 'Arrival at Destination' or 'Unloading End' execution event in SAP TM, updating the execution status of the Freight Order. The data is often received from the carrier. Capture Use the timestamp of the reported 'Arrival at Destination' execution event for the final stop of the Freight Order. Event type explicit | |||
| Goods Picked Up | This activity marks the start of the physical transportation of the goods. It is captured when the carrier reports that the goods have been loaded and have departed from the shipper's location. | ||
| Why it matters This is a key execution milestone that signifies the beginning of the in-transit phase. It is critical for tracking on-time performance and providing accurate shipment visibility. Where to get This event is typically recorded in SAP Event Management, which is integrated with SAP TM, as a 'Departure' or 'Loading End' event. It updates the execution status of the Freight Order. It can also be recorded directly in the Freight Order. Capture Use the timestamp of the reported 'Departure' execution event for the origin location of the Freight Order. Event type explicit | |||
| Payment Processed | This final activity confirms that the carrier has been paid for the transportation services. This event typically occurs in the connected ERP system after the verified invoice is received from SAP TM. | ||
| Why it matters This marks the true end of the end-to-end transportation lifecycle. It is essential for measuring overall process duration and on-time payment KPIs. Where to get This information resides in the ERP system (e.g., S/4HANA Finance). It can be linked back to the Freight Settlement Document via the invoice number. A status update may be sent back to SAP TM, or the data must be sourced from ERP tables like BKPF and BSEG. Capture Source the payment clearing date from the ERP system, linking it to the shipment via the invoice reference. Event type explicit | |||
| Proof Of Delivery Received | This activity occurs when the signed Proof of Delivery (POD) document is received from the carrier and recorded in the system. This confirms the recipient's acceptance of the goods. | ||
| Why it matters Receiving the POD is a critical prerequisite for invoicing the customer and settling freight charges with the carrier. Delays here directly impact the cash-to-cash cycle. Where to get This is typically inferred from a status change on the Freight Order, such as the POD status being set to 'Received'. The change in this status field provides the timestamp for the activity. Capture Capture the timestamp when the Proof of Delivery status on the Freight Order is updated to 'Received'. Event type inferred | |||
| Shipment Cancelled | This activity represents the cancellation of a shipment after it has been requested or planned. It serves as a terminal endpoint for processes that do not proceed to execution. | ||
| Why it matters Tracking cancellations helps identify reasons for process failure, such as changes in customer demand or planning errors. High cancellation rates may indicate underlying issues in order management or planning. Where to get This is inferred from a change in the lifecycle status of the Forwarding Order or Freight Order to 'Cancelled'. The timestamp of this status change marks the event. Capture Capture the timestamp when the document's lifecycle status is set to 'Cancelled'. Event type inferred | |||
| Shipment Planned | This activity signifies that the transportation requirements have been consolidated into a concrete shipment plan. It is captured when a Freight Order (for road/rail) or Freight Booking (for sea/air) is created and saved, containing the planned route and resources. | ||
| Why it matters Completing this milestone is essential for initiating logistics execution. The cycle time to this activity from the request is a key KPI for measuring planning efficiency. Where to get This event corresponds to the creation of a Freight Order or Freight Booking document. It can also be inferred from a status change on the document, such as the planning status moving to 'Planned'. The relevant tables are typically /SCMTMS/D_TORROT for header data. Capture Use the creation timestamp of the Freight Order or Freight Booking document. Event type explicit | |||
| Transportation Request Received | This activity marks the initiation of the transportation process, representing the creation of a transportation requirement. In SAP TM, this is typically captured by the creation of a Forwarding Order (FWO) or an Order-based Transportation Requirement (OTR). | ||
| Why it matters This is the primary start event for the end-to-end shipment process. Analyzing the time from this activity to planning completion is crucial for measuring planning efficiency. Where to get This event is recorded upon the creation of a Forwarding Order (document type FWO) or other transportation requirement documents. The creation timestamp in the header table, such as /SCMTMS/D_TORROT, serves as the event time. Capture Capture the creation date and time of the Forwarding Order or Transportation Requirement document. Event type explicit | |||
| Customs Cleared | This activity indicates that the shipment has been approved by customs authorities and is clear to proceed. This event is a major milestone for international logistics, as it removes a significant potential bottleneck. | ||
| Why it matters Customs clearance is often a source of major delays in global supply chains. Tracking the duration and success rate of this activity is key to improving international transit times. Where to get This is captured as a 'Customs Clearance' execution event, typically reported by a customs broker or an external visibility provider and updated in the Freight Order's execution tracking. Capture Use the timestamp of the reported 'Customs Clearance' execution event associated with the Freight Order. Event type explicit | |||
| Customs Declaration Filed | For international shipments, this activity represents the submission of required customs documentation to authorities. It is a prerequisite for obtaining clearance to export or import goods. | ||
| Why it matters Timely filing of customs declarations is crucial to avoid delays at borders. This activity helps monitor the efficiency of the customs brokerage process. Where to get This event can be captured as a specific execution event reported by a customs broker or from a status change in an integrated customs management system, like SAP GTS. The Freight Order is updated accordingly. Capture Capture the timestamp of the 'Customs Declaration Filed' execution event or a corresponding status change. Event type explicit | |||
| Freight Dispute Created | This event indicates that a discrepancy has been found between the carrier's invoice and the calculated costs in the Freight Settlement Document. This triggers a dispute resolution workflow. | ||
| Why it matters Disputes delay carrier payments and require administrative effort to resolve. Tracking this activity helps identify the root causes of billing inaccuracies, such as incorrect rates or accessorial charges. Where to get This can be inferred from a status change on the Freight Settlement Document, where a 'Dispute' status is set. It may also involve the creation of a separate dispute case document. Capture Capture the timestamp when the dispute status is set on the Freight Settlement Document. Event type inferred | |||
| Invoice Verified | This activity signifies that the carrier's invoice has been successfully matched against the Freight Settlement Document and is approved for payment. It is the final step before the payment process is initiated in the ERP system. | ||
| Why it matters This activity completes the audit and verification process for freight costs. Delays at this stage can lead to late payments to carriers, potentially harming relationships. Where to get This is inferred from a status change on the Freight Settlement Document, such as 'Ready for Accrual' or 'Transferred for Posting', indicating it has been sent to the ERP system for payment. Capture Track the timestamp when the FSD's lifecycle status changes to indicate it is ready for payment or has been posted. Event type inferred | |||
| Shipment Confirmed By Carrier | This event represents the carrier's agreement to execute the transportation as planned. The confirmation is typically received via an EDI message, a carrier portal update, or manual entry, which updates the status of the Freight Order. | ||
| Why it matters Carrier confirmation provides a commitment to the shipment plan, reducing uncertainty. Delays in receiving this confirmation can put the entire shipment schedule at risk. Where to get This is inferred from a change in the confirmation status field of the Freight Order or Freight Booking. A specific status value, like 'Confirmed', will be set in the document's status management. Capture Track changes to the confirmation status fields within the Freight Order document. Event type inferred | |||
| Shipment Rerouted | This activity signifies an unplanned change to the shipment's route or schedule after it has been confirmed. This could be due to disruptions, customer requests, or logistical issues. | ||
| Why it matters Reroutes often lead to increased costs and delays. Identifying the frequency and reasons for rerouting helps to improve planning accuracy and operational stability. Where to get This event is inferred by detecting significant changes to the stages, locations, or dates in a Freight Order after its execution has begun. Change documents (tables CDHDR and CDPOS) can be analyzed for this purpose. Capture Detect changes to key routing fields in the Freight Order document after the 'Shipment Confirmed by Carrier' event. Event type inferred | |||
Extraction Guides
Steps
- Identify and Activate CDS View APIs: Log in to your SAP S/4HANA system. Use the transaction
/IWFND/MAINT_SERVICEto verify that the necessary OData services for the core CDS views are active. Key services to check for include those related to I_TransportationOrder, C_TranspOrdExecutionEvent, I_ForwardingOrder, and I_FreightSettlementDocument. If not active, work with your SAP administrator to add and activate them from the backend system. - Establish API Connectivity: Obtain the root URL for the OData services from your SAP administrator. Set up a client application, such as a Python script using the requests library, a commercial ETL tool, or a tool like Postman, to connect to the API. You will need to configure authentication, typically using Basic Authentication (username and password) or OAuth 2.0 for a more secure connection.
- Define Extraction Scope: Determine the scope of your data extraction. Define a specific date range, for example, the last 6 months. Identify the relevant organizational units to filter on, such as Company Code, or specific document types like Freight Order Type, to ensure the data is relevant to the process you are analyzing.
- Construct Base Shipment Query: Begin by querying the I_TransportationOrder CDS view. This view serves as the central object for shipments, also known as Freight Orders or Freight Bookings. Use the OData
$selectparameter to choose the columns you need, including the recommended attributes like CarrierName, ModeOfTransport, and RequestedDeliveryDate. Apply a$filterto limit the data by date and other scope parameters. - Query Execution and Transportation Events: Use the C_TranspOrdExecutionEvent CDS view to extract key logistics events. Filter this view for specific
EventCodevalues that correspond to activities such as 'Goods Picked Up' (DEPARTURE) and 'Goods Delivered' (ARRIVAL_AT_DESTINATION). Join this data back to your base shipment data using the TransportationOrder UUID. - Query Related Business Objects: To capture the full process, you must query other related CDS views. Query I_ForwardingOrder for the 'Transportation Request Received' activity, and I_FreightSettlementDocument for financial activities like 'Freight Settlement Created', 'Freight Dispute Created', and 'Invoice Verified'.
- Extract Status-Based Activities: Many activities are not discrete events but rather status changes on the primary document. For activities like 'Carrier Assigned', 'Shipment Confirmed By Carrier', and 'Shipment Cancelled', you will query the I_TransportationOrder view and use the
LastChangeDateTimeassociated with the relevant status field as the event time. This requires careful mapping of status codes to business activities. - Union and Transform Data: Consolidate the data extracted from all sources into a single event log structure. For each activity extracted, create a row containing the required columns: ShipmentId, ActivityName, and EventTime. Ensure all recommended attributes from the base shipment query are present on every row for that shipment. This step is conceptually equivalent to a SQL
UNION ALLoperation. - Handle Complex and External Events: For activities like 'Customs Cleared' or 'Payment Processed', the data may reside in specific fields or even external systems. The query logic provided uses standard fields as a proxy. You may need to adjust the query to join with customs-specific CDS views or correlate with a separate ERP extraction for payment data based on the Freight Settlement Document number.
- Format for ProcessMind: Save the final consolidated dataset as a CSV file. Ensure the column headers match the requirements for upload, for instance, mapping your
ShipmentIdcolumn to theCaseIdfield in the tool's settings. Verify that timestamps are in a consistent format, preferably UTC (YYYY-MM-DDTHH:MM:SSZ).
Configuration
- Core CDS Views: The extraction primarily relies on I_TransportationOrder (for freight orders), C_TranspOrdExecutionEvent (for execution events), I_ForwardingOrder (for transportation requests), and I_FreightSettlementDocument (for settlement).
- Date Range Filtering: It is critical to apply a date filter to manage data volume. Use the
CreationDateTimeon the I_TransportationOrder or theActualBusinessDateTimeon C_TranspOrdExecutionEvent. A range of 3 to 6 months is recommended for an initial analysis. - Document Type Filtering: To focus the analysis, filter on specific document types using the
TransportationOrderTypefield. This helps separate different logistics scenarios, such as standard outbound shipments versus stock transfers. - Organizational Filters: If applicable, filter by
PurchasingOrganizationor other org-level fields to limit the data to a specific business unit or region. - API Pagination: For large datasets, use the OData parameters
$topand$skipto retrieve data in smaller pages. This prevents API timeouts and reduces memory consumption on the client side. A page size of 1000 to 5000 rows is often effective. - Prerequisites: The user or service account performing the extraction requires authorizations to the underlying OData services (authorization object S_SERVICE) and the business data within the CDS views. The corresponding services must be activated in the SAP Gateway (
/IWFND/MAINT_SERVICE).
a Sample Query sql
WITH Shipments AS (
SELECT
TransportationOrder AS ShipmentId,
CarrierName,
TranspOrdExecutionBlockRsnCode,
TransportationMode AS ModeOfTransport,
ShippingType AS ShipmentType,
CreationDateTime AS ShipmentCreationTime,
LastChangeDateTime,
LifeCycleStatus,
ConfirmationStatus,
OverallExecutionStatus,
DeliveryOverallStatus,
PlannedDepDateTimeReqByCust AS RequestedDeliveryDate,
TotalChargesAmountInTransacCrcy AS TotalFreightCost
FROM I_TransportationOrder
WHERE
CreationDateTime >= '[START_DATE]' AND CreationDateTime <= '[END_DATE]'
-- AND TransportationOrderType IN ('[Your_Type_1]', '[Your_Type_2]')
)
SELECT
fwo.PurchasingDocument AS ShipmentId,
'Transportation Request Received' AS ActivityName,
fwo.CreationDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM I_ForwardingOrder AS fwo
LEFT JOIN Shipments s ON fwo.PurchasingDocument = s.ShipmentId
WHERE fwo.CreationDateTime >= '[START_DATE]' AND fwo.CreationDateTime <= '[END_DATE]'
UNION ALL
SELECT
s.ShipmentId,
'Shipment Planned' AS ActivityName,
s.ShipmentCreationTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
UNION ALL
SELECT
s.ShipmentId,
'Carrier Assigned' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Best approximation using last change time
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.CarrierName IS NOT NULL
UNION ALL
SELECT
s.ShipmentId,
'Shipment Confirmed By Carrier' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Best approximation using last change time
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.ConfirmationStatus = 'C' -- 'Confirmed'
UNION ALL
SELECT
s.ShipmentId,
'Goods Picked Up' AS ActivityName,
evt.ActualBusinessDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM C_TranspOrdExecutionEvent AS evt
JOIN Shipments s ON evt.TransportationOrder = s.ShipmentId
WHERE evt.EventCode = 'DEPARTURE'
UNION ALL
SELECT
s.ShipmentId,
'Customs Declaration Filed' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Approximation based on status change
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.CustomsRelevance IS NOT NULL -- This is a proxy, system config dependent
UNION ALL
SELECT
s.ShipmentId,
'Customs Cleared' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Approximation based on status change
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.CustomsOverallStatus = '4' -- 'Customs Cleared'
UNION ALL
SELECT
s.ShipmentId,
'Shipment Rerouted' AS ActivityName,
s.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.TranspOrdExecutionBlockRsnCode = '[Your Rerouting Reason Code]'
UNION ALL
SELECT
s.ShipmentId,
'Goods Delivered' AS ActivityName,
evt.ActualBusinessDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM C_TranspOrdExecutionEvent AS evt
JOIN Shipments s ON evt.TransportationOrder = s.ShipmentId
WHERE evt.EventCode = 'ARRIVAL_AT_DESTINATION'
UNION ALL
SELECT
s.ShipmentId,
'Proof Of Delivery Received' AS ActivityName,
s.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.DeliveryOverallStatus = 'C' -- 'POD Received'
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Freight Settlement Created' AS ActivityName,
fsd.CreationDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Freight Dispute Created' AS ActivityName,
fsd.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
WHERE fsd.DisputeStatus = '1' -- 'In Dispute'
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Invoice Verified' AS ActivityName,
fsd.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
WHERE fsd.OverallLifeCycleStatus = '06' -- 'Posted for Accruals'
UNION ALL
SELECT
s.ShipmentId,
'Shipment Cancelled' AS ActivityName,
s.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.LifeCycleStatus = 'X' -- 'Cancelled'
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Payment Processed' AS ActivityName,
fsd.LastChangeDateTime AS EventTime, -- Approximation, actual payment is in ERP
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
WHERE fsd.InvoiceProcessingStatus = 'C' -- 'Completely Invoiced'Steps
- Identify and Activate CDS View APIs: Log in to your SAP S/4HANA system. Use the transaction
/IWFND/MAINT_SERVICEto verify that the necessary OData services for the core CDS views are active. Key services to check for include those related to I_TransportationOrder, C_TranspOrdExecutionEvent, I_ForwardingOrder, and I_FreightSettlementDocument. If not active, work with your SAP administrator to add and activate them from the backend system. - Establish API Connectivity: Obtain the root URL for the OData services from your SAP administrator. Set up a client application, such as a Python script using the requests library, a commercial ETL tool, or a tool like Postman, to connect to the API. You will need to configure authentication, typically using Basic Authentication (username and password) or OAuth 2.0 for a more secure connection.
- Define Extraction Scope: Determine the scope of your data extraction. Define a specific date range, for example, the last 6 months. Identify the relevant organizational units to filter on, such as Company Code, or specific document types like Freight Order Type, to ensure the data is relevant to the process you are analyzing.
- Construct Base Shipment Query: Begin by querying the I_TransportationOrder CDS view. This view serves as the central object for shipments, also known as Freight Orders or Freight Bookings. Use the OData
$selectparameter to choose the columns you need, including the recommended attributes like CarrierName, ModeOfTransport, and RequestedDeliveryDate. Apply a$filterto limit the data by date and other scope parameters. - Query Execution and Transportation Events: Use the C_TranspOrdExecutionEvent CDS view to extract key logistics events. Filter this view for specific
EventCodevalues that correspond to activities such as 'Goods Picked Up' (DEPARTURE) and 'Goods Delivered' (ARRIVAL_AT_DESTINATION). Join this data back to your base shipment data using the TransportationOrder UUID. - Query Related Business Objects: To capture the full process, you must query other related CDS views. Query I_ForwardingOrder for the 'Transportation Request Received' activity, and I_FreightSettlementDocument for financial activities like 'Freight Settlement Created', 'Freight Dispute Created', and 'Invoice Verified'.
- Extract Status-Based Activities: Many activities are not discrete events but rather status changes on the primary document. For activities like 'Carrier Assigned', 'Shipment Confirmed By Carrier', and 'Shipment Cancelled', you will query the I_TransportationOrder view and use the
LastChangeDateTimeassociated with the relevant status field as the event time. This requires careful mapping of status codes to business activities. - Union and Transform Data: Consolidate the data extracted from all sources into a single event log structure. For each activity extracted, create a row containing the required columns: ShipmentId, ActivityName, and EventTime. Ensure all recommended attributes from the base shipment query are present on every row for that shipment. This step is conceptually equivalent to a SQL
UNION ALLoperation. - Handle Complex and External Events: For activities like 'Customs Cleared' or 'Payment Processed', the data may reside in specific fields or even external systems. The query logic provided uses standard fields as a proxy. You may need to adjust the query to join with customs-specific CDS views or correlate with a separate ERP extraction for payment data based on the Freight Settlement Document number.
- Format for ProcessMind: Save the final consolidated dataset as a CSV file. Ensure the column headers match the requirements for upload, for instance, mapping your
ShipmentIdcolumn to theCaseIdfield in the tool's settings. Verify that timestamps are in a consistent format, preferably UTC (YYYY-MM-DDTHH:MM:SSZ).
Configuration
- Core CDS Views: The extraction primarily relies on I_TransportationOrder (for freight orders), C_TranspOrdExecutionEvent (for execution events), I_ForwardingOrder (for transportation requests), and I_FreightSettlementDocument (for settlement).
- Date Range Filtering: It is critical to apply a date filter to manage data volume. Use the
CreationDateTimeon the I_TransportationOrder or theActualBusinessDateTimeon C_TranspOrdExecutionEvent. A range of 3 to 6 months is recommended for an initial analysis. - Document Type Filtering: To focus the analysis, filter on specific document types using the
TransportationOrderTypefield. This helps separate different logistics scenarios, such as standard outbound shipments versus stock transfers. - Organizational Filters: If applicable, filter by
PurchasingOrganizationor other org-level fields to limit the data to a specific business unit or region. - API Pagination: For large datasets, use the OData parameters
$topand$skipto retrieve data in smaller pages. This prevents API timeouts and reduces memory consumption on the client side. A page size of 1000 to 5000 rows is often effective. - Prerequisites: The user or service account performing the extraction requires authorizations to the underlying OData services (authorization object S_SERVICE) and the business data within the CDS views. The corresponding services must be activated in the SAP Gateway (
/IWFND/MAINT_SERVICE).
a Sample Query sql
WITH Shipments AS (
SELECT
TransportationOrder AS ShipmentId,
CarrierName,
TranspOrdExecutionBlockRsnCode,
TransportationMode AS ModeOfTransport,
ShippingType AS ShipmentType,
CreationDateTime AS ShipmentCreationTime,
LastChangeDateTime,
LifeCycleStatus,
ConfirmationStatus,
OverallExecutionStatus,
DeliveryOverallStatus,
PlannedDepDateTimeReqByCust AS RequestedDeliveryDate,
TotalChargesAmountInTransacCrcy AS TotalFreightCost
FROM I_TransportationOrder
WHERE
CreationDateTime >= '[START_DATE]' AND CreationDateTime <= '[END_DATE]'
-- AND TransportationOrderType IN ('[Your_Type_1]', '[Your_Type_2]')
)
SELECT
fwo.PurchasingDocument AS ShipmentId,
'Transportation Request Received' AS ActivityName,
fwo.CreationDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM I_ForwardingOrder AS fwo
LEFT JOIN Shipments s ON fwo.PurchasingDocument = s.ShipmentId
WHERE fwo.CreationDateTime >= '[START_DATE]' AND fwo.CreationDateTime <= '[END_DATE]'
UNION ALL
SELECT
s.ShipmentId,
'Shipment Planned' AS ActivityName,
s.ShipmentCreationTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
UNION ALL
SELECT
s.ShipmentId,
'Carrier Assigned' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Best approximation using last change time
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.CarrierName IS NOT NULL
UNION ALL
SELECT
s.ShipmentId,
'Shipment Confirmed By Carrier' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Best approximation using last change time
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.ConfirmationStatus = 'C' -- 'Confirmed'
UNION ALL
SELECT
s.ShipmentId,
'Goods Picked Up' AS ActivityName,
evt.ActualBusinessDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM C_TranspOrdExecutionEvent AS evt
JOIN Shipments s ON evt.TransportationOrder = s.ShipmentId
WHERE evt.EventCode = 'DEPARTURE'
UNION ALL
SELECT
s.ShipmentId,
'Customs Declaration Filed' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Approximation based on status change
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.CustomsRelevance IS NOT NULL -- This is a proxy, system config dependent
UNION ALL
SELECT
s.ShipmentId,
'Customs Cleared' AS ActivityName,
s.LastChangeDateTime AS EventTime, -- Approximation based on status change
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.CustomsOverallStatus = '4' -- 'Customs Cleared'
UNION ALL
SELECT
s.ShipmentId,
'Shipment Rerouted' AS ActivityName,
s.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.TranspOrdExecutionBlockRsnCode = '[Your Rerouting Reason Code]'
UNION ALL
SELECT
s.ShipmentId,
'Goods Delivered' AS ActivityName,
evt.ActualBusinessDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM C_TranspOrdExecutionEvent AS evt
JOIN Shipments s ON evt.TransportationOrder = s.ShipmentId
WHERE evt.EventCode = 'ARRIVAL_AT_DESTINATION'
UNION ALL
SELECT
s.ShipmentId,
'Proof Of Delivery Received' AS ActivityName,
s.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.DeliveryOverallStatus = 'C' -- 'POD Received'
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Freight Settlement Created' AS ActivityName,
fsd.CreationDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Freight Dispute Created' AS ActivityName,
fsd.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
WHERE fsd.DisputeStatus = '1' -- 'In Dispute'
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Invoice Verified' AS ActivityName,
fsd.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
WHERE fsd.OverallLifeCycleStatus = '06' -- 'Posted for Accruals'
UNION ALL
SELECT
s.ShipmentId,
'Shipment Cancelled' AS ActivityName,
s.LastChangeDateTime AS EventTime,
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
s.TotalFreightCost
FROM Shipments AS s
WHERE s.LifeCycleStatus = 'X' -- 'Cancelled'
UNION ALL
SELECT
fsd.SourceDocument AS ShipmentId,
'Payment Processed' AS ActivityName,
fsd.LastChangeDateTime AS EventTime, -- Approximation, actual payment is in ERP
s.CarrierName,
s.ModeOfTransport,
s.RequestedDeliveryDate,
fsd.TotalAmount AS TotalFreightCost
FROM I_FreightSettlementDocument AS fsd
JOIN Shipments s ON fsd.SourceDocument = s.ShipmentId
WHERE fsd.InvoiceProcessingStatus = 'C' -- 'Completely Invoiced'Steps
- Define Data Requirements: Begin by confirming the exact logic for each of the 15 required activities and all attributes. This involves mapping each activity to specific status changes, creation events, or document linkages within your SAP TM system. For example, determine the exact execution status that signifies 'Goods Picked Up'.
- Access ABAP Development Environment: Log in to the SAP GUI and go to the ABAP Editor using transaction code
SE38. Enter a name for your new program, for example,ZTM_PROCESS_EXTRACT, and click 'Create'. Provide a title and set the program type to 'Executable Program'. - Create Selection Screen: In the program, define the selection screen that users will see. This should include parameters for filtering the data, such as a date range for the freight order creation date (
SO_CREDT), and select-options for document types (SO_TYPE) or carriers (SO_CARR). - Define Output Structure: Define an internal table structure that matches the required event log format. This structure should include fields for
ShipmentId,ActivityName,EventTime,EndTime,CarrierName,RequestedDeliveryDate,ModeOfTransport,TotalFreightCost, andIsOnTimeDelivery. - Implement Data Selection Logic: Write the main ABAP logic to select data for each of the 15 activities. This typically involves querying multiple SAP TM tables. Start by selecting the primary shipment documents, such as Freight Orders, within the specified date range.
- Extract Planning and Execution Events: For each selected shipment document (e.g., Freight Order), query related tables and business objects to find timestamps for different events. Use table
/SCMTMS/D_TORROTfor Freight Order creation ('Shipment Planned'), carrier assignment ('Carrier Assigned'), and execution status changes that correspond to 'Goods Picked Up' and 'Goods Delivered'. The creation of the preceding Forwarding Order (/SCMTMS/D_TRQROT) can signify 'Transportation Request Received'. - Extract Carrier Confirmation & Rerouting: Query status tables or change documents (CDHDR/CDPOS) associated with the Freight Order to identify when the
CONFIRMATIONstatus was set, which captures 'Shipment Confirmed By Carrier'. Similarly, look for significant changes in routing information to capture 'Shipment Rerouted'. - Extract Customs Events: If using SAP Global Trade Services (GTS) or a similar module, the customs events ('Customs Declaration Filed', 'Customs Cleared') are often tracked via linked documents. The logic must read the document flow (
/SCMTMS/D_TORDOC) of the Freight Order to find these related documents and their creation or status change times. - Extract Settlement and Dispute Events: Query the Freight Settlement Document header table (
/SCMTMS/D_SFIRHD) and its related items and statuses to capture 'Freight Settlement Created', 'Freight Dispute Created', and 'Invoice Verified' events. Link these back to the originalShipmentId. - Consolidate Data: For each event found, create a new record in your final internal table. Populate the
ActivityNamewith the corresponding activity, theEventTimewith the timestamp of the event, and theShipmentIdwith the Freight Order or Freight Booking ID. Fill in the other attributes from the source documents. - Format and Export File: Once the final internal table is fully populated, format the data into a flat file. Use the
OPEN DATASET,LOOP AT... TRANSFER..., andCLOSE DATASETcommands to write the contents of the internal table to a CSV or TXT file on the SAP application server (accessible via transaction AL11). - Schedule and Retrieve: The ABAP program can be scheduled to run as a background job (transaction SM36) to extract data automatically. The generated file can then be downloaded from the application server to a local machine using transaction
CG3Yfor upload into ProcessMind.
Configuration
- Date Range: The selection screen should include a mandatory date range for the creation date of the core transportation document (e.g., Freight Order). For initial analysis, a range of 3 to 6 months is recommended to ensure a representative dataset without causing excessive runtime.
- Document Type Filter: Provide a filter for the Transportation Document Type (e.g., Freight Order, Freight Booking). This is crucial for focusing the analysis on specific processes like road freight versus ocean freight.
- Carrier Filter: Allow users to filter by specific carriers. This helps in analyzing the performance of individual logistics partners.
- Organizational Filters: Include filters for relevant organizational units, such as Purchasing Organization or Company Code, to narrow the extraction to specific business areas.
- Performance Optimization: For large datasets, ensure the ABAP code uses indexed database fields in
WHEREclauses, avoids nested loops where possible, and selects only the necessary fields from the database to minimize memory consumption. - Prerequisites: The user running the report needs authorization to execute ABAP programs. The developer requires
S_DEVELOPauthorization. The program itself requires authorizations for file system access on the application server (objectS_DATASET) and access to the relevant TM database tables and BOPF objects.
a Sample Query abap
REPORT ztm_process_extract.
*&---------------------------------------------------------------------*
*& Data Structures
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_s_event_log,
shipmentid TYPE /scmtms/tor_id,
activityname TYPE string,
eventtime TYPE timestamp,
endtime TYPE timestamp,
carriername TYPE string,
requesteddeliverydate TYPE timestamp,
modeoftransport TYPE /scmtms/mot,
totalfreightcost TYPE /scmtms/chargeable_weight,
isontimedelivery TYPE abap_bool,
END OF ty_s_event_log.
DATA: lt_event_log TYPE TABLE OF ty_s_event_log.
*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: so_credt FOR sy-datum OBLIGATORY.
SELECT-OPTIONS: so_torid FOR /scmtms/d_torrot-tor_id.
SELECT-OPTIONS: so_carr FOR /scmtms/d_torrot-lsp_id.
*&---------------------------------------------------------------------*
*& Main Logic
*&---------------------------------------------------------------------*
START-OF-SELECTION.
DATA: ls_event_log TYPE ty_s_event_log.
" Select base data: Freight Orders (Shipments)
SELECT tor_id, tor_cat, lsp_id, mot_code, cretd_tst, btd_tstmp_end
FROM /scmtms/d_torrot
INTO TABLE @DATA(lt_tor_root)
WHERE CAST( cretd_tst AS DATS ) IN @so_credt
AND tor_id IN @so_torid
AND lsp_id IN @so_carr.
IF lt_tor_root IS INITIAL.
MESSAGE 'No data found for the given selection.' TYPE 'S' DISPLAY LIKE 'E'.
RETURN.
ENDIF.
" Loop through each shipment to build the event log
LOOP AT lt_tor_root INTO DATA(ls_tor_root).
" 1. Transportation Request Received (Approximation from FWO creation)
SELECT SINGLE created_at_utc FROM /scmtms/d_trqrot
WHERE db_key IN ( SELECT trq_root_key FROM /scmtms/d_tordoc
WHERE parent_key = @ls_tor_root-db_key AND bo_node_cat = 'TRQ_ROOT' )
INTO @DATA(lv_trq_created_ts).
IF sy-subrc = 0.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Transportation Request Received'.
ls_event_log-eventtime = lv_trq_created_ts.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 2. Shipment Planned
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Shipment Planned'.
ls_event_log-eventtime = ls_tor_root-cretd_tst.
APPEND ls_event_log TO lt_event_log.
" 3. Carrier Assigned
IF ls_tor_root-lsp_id IS NOT INITIAL.
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Carrier Assigned'.
ls_event_log-eventtime = ls_tor_root-cretd_tst. " Approximated with creation time
APPEND ls_event_log TO lt_event_log.
ENDIF.
" Get statuses from /SCMTMS/D_TORSTA
SELECT status, tstmp FROM /scmtms/d_torsta
INTO TABLE @DATA(lt_statuses)
WHERE parent_key = @ls_tor_root-db_key.
" 4. Shipment Confirmed By Carrier
READ TABLE lt_statuses WITH KEY status = 'CONFIRMED' INTO DATA(ls_conf_status).
IF sy-subrc = 0.
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Shipment Confirmed By Carrier'.
ls_event_log-eventtime = ls_conf_status-tstmp.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 5. Goods Picked Up
READ TABLE lt_statuses WITH KEY status = 'LOADING_END' INTO DATA(ls_pickup_status). " Example status
IF sy-subrc = 0.
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Goods Picked Up'.
ls_event_log-eventtime = ls_pickup_status-tstmp.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 6. Customs Declaration Filed (Placeholder logic)
" 7. Customs Cleared (Placeholder logic)
" 8. Shipment Rerouted (Placeholder logic, needs change documents)
" 9. Goods Delivered
READ TABLE lt_statuses WITH KEY status = 'UNLOADING_END' INTO DATA(ls_delivery_status). " Example status
IF sy-subrc = 0.
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Goods Delivered'.
ls_event_log-eventtime = ls_delivery_status-tstmp.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 10. Proof Of Delivery Received
READ TABLE lt_statuses WITH KEY status = 'POD_RECEIVED' INTO DATA(ls_pod_status). " Example status
IF sy-subrc = 0.
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Proof Of Delivery Received'.
ls_event_log-eventtime = ls_pod_status-tstmp.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 11. Freight Settlement Created
SELECT SINGLE created_at_utc FROM /scmtms/d_sfirhd
WHERE db_key IN ( SELECT sfir_root_key FROM /scmtms/d_tordoc
WHERE parent_key = @ls_tor_root-db_key AND bo_node_cat = 'SFIR_ROOT' )
INTO @DATA(lv_fsd_created_ts).
IF sy-subrc = 0.
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Freight Settlement Created'.
ls_event_log-eventtime = lv_fsd_created_ts.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 12. Freight Dispute Created (Needs logic based on dispute status in SFIR)
" 13. Invoice Verified (Needs logic based on invoice status in SFIR)
" 14. Shipment Cancelled
READ TABLE lt_statuses WITH KEY status = 'CANCELLED' INTO DATA(ls_cancel_status).
IF sy-subrc = 0.
CLEAR ls_event_log.
ls_event_log-shipmentid = ls_tor_root-tor_id.
ls_event_log-activityname = 'Shipment Cancelled'.
ls_event_log-eventtime = ls_cancel_status-tstmp.
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 15. Payment Processed (Data is typically in the ERP system, not TM)
ENDLOOP.
" Final step: Write lt_event_log to a file on the application server
DATA(lv_filepath) = '/usr/sap/trans/[YourInstance]/[YourFileName].csv'.
OPEN DATASET lv_filepath FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
IF sy-subrc = 0.
" Write header
DATA(lv_header) = 'ShipmentId,ActivityName,EventTime'.
TRANSFER lv_header TO lv_filepath.
" Write data
LOOP AT lt_event_log INTO ls_event_log.
DATA(lv_line) = |{ ls_event_log-shipmentid },{ ls_event_log-activityname },{ ls_event_log-eventtime }|.
TRANSFER lv_line TO lv_filepath.
ENDLOOP.
CLOSE DATASET lv_filepath.
ELSE.
MESSAGE 'Error opening file on application server.' TYPE 'E'.
ENDIF.
ENDFOR.