Votre modèle de données pour la gestion des transports

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'

WITH
base_tor AS (
    SELECT
        r.TOR_ID AS ShipmentId,
        r.TOR_ID AS SourceDocumentId,
        r.CREATED_AT AS RequestEventTime,
        r.CREATED_AT AS PlannedEventTime,
        r.CREATED_AT AS CarrierAssignedEventTime,
        r.CREATED_AT AS CarrierConfirmedEventTime,
        r.CREATED_AT AS PickupEventTime,
        r.CREATED_AT AS CustomsFiledEventTime,
        r.CREATED_AT AS CustomsClearedEventTime,
        r.CREATED_AT AS ReroutedEventTime,
        r.CREATED_AT AS DeliveredEventTime,
        r.CREATED_AT AS PodReceivedEventTime,
        r.CREATED_AT AS SettlementCreatedEventTime,
        r.CREATED_AT AS DisputeCreatedEventTime,
        r.CREATED_AT AS InvoiceVerifiedEventTime,
        r.CREATED_AT AS CancelledEventTime,
        r.CREATED_AT AS PaymentProcessedEventTime,
        r.CREATED_AT AS EndTime,
        CAST(NULL AS NVARCHAR(255)) AS CarrierName,
        CAST(NULL AS TIMESTAMP) AS RequestedDeliveryDate,
        CAST(NULL AS NVARCHAR(50)) AS ModeOfTransport,
        CAST(NULL AS DECIMAL(19, 2)) AS TotalFreightCost,
        CAST(NULL AS NVARCHAR(5)) AS IsOnTimeDelivery
    FROM "/SCMTMS/D_TORROT" r
    WHERE r.CREATED_AT >= TO_TIMESTAMP('[Start timestamp]')
      AND r.CREATED_AT < TO_TIMESTAMP('[End timestamp]')
      AND ('[Company code filter]' = '[Company code filter]' OR r.TOR_ID IS NOT NULL)
),
stop_data AS (
    SELECT
        s.TOR_ID AS ShipmentId,
        MIN(s.STOP_SEQ) AS FirstStopSequence,
        MAX(s.STOP_SEQ) AS LastStopSequence
    FROM "/SCMTMS/D_TOSTOP" s
    GROUP BY s.TOR_ID
),
source_data AS (
    SELECT
        b.ShipmentId,
        b.SourceDocumentId,
        b.RequestEventTime,
        b.PlannedEventTime,
        b.CarrierAssignedEventTime,
        b.CarrierConfirmedEventTime,
        b.PickupEventTime,
        b.CustomsFiledEventTime,
        b.CustomsClearedEventTime,
        b.ReroutedEventTime,
        b.DeliveredEventTime,
        b.PodReceivedEventTime,
        b.SettlementCreatedEventTime,
        b.DisputeCreatedEventTime,
        b.InvoiceVerifiedEventTime,
        b.CancelledEventTime,
        b.PaymentProcessedEventTime,
        b.EndTime,
        b.CarrierName,
        b.RequestedDeliveryDate,
        b.ModeOfTransport,
        b.TotalFreightCost,
        CASE
            WHEN b.DeliveredEventTime IS NOT NULL
             AND b.RequestedDeliveryDate IS NOT NULL
             AND b.DeliveredEventTime <= b.RequestedDeliveryDate THEN 'true'
            WHEN b.DeliveredEventTime IS NOT NULL
             AND b.RequestedDeliveryDate IS NOT NULL THEN 'false'
            ELSE NULL
        END AS IsOnTimeDelivery
    FROM base_tor b
    LEFT JOIN stop_data s
      ON s.ShipmentId = b.ShipmentId
),
event_log AS (
    SELECT ShipmentId, 'Transportation Request Received' AS ActivityName, RequestEventTime AS EventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE RequestEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Shipment Planned', PlannedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE PlannedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Carrier Assigned', CarrierAssignedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE CarrierAssignedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Shipment Confirmed By Carrier', CarrierConfirmedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE CarrierConfirmedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Goods Picked Up', PickupEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE PickupEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Customs Declaration Filed', CustomsFiledEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE CustomsFiledEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Customs Cleared', CustomsClearedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE CustomsClearedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Shipment Rerouted', ReroutedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE ReroutedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Goods Delivered', DeliveredEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE DeliveredEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Proof Of Delivery Received', PodReceivedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE PodReceivedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Freight Settlement Created', SettlementCreatedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE SettlementCreatedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Freight Dispute Created', DisputeCreatedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE DisputeCreatedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Invoice Verified', InvoiceVerifiedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE InvoiceVerifiedEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Shipment Cancelled', CancelledEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE CancelledEventTime IS NOT NULL
    UNION ALL
    SELECT ShipmentId, 'Payment Processed', PaymentProcessedEventTime, EndTime, CarrierName, RequestedDeliveryDate, ModeOfTransport, TotalFreightCost, IsOnTimeDelivery, SourceDocumentId FROM source_data WHERE PaymentProcessedEventTime IS NOT NULL
)
SELECT
    ShipmentId,
    ActivityName,
    EventTime,
    EndTime,
    CarrierName,
    RequestedDeliveryDate,
    ModeOfTransport,
    TotalFreightCost,
    IsOnTimeDelivery,
    SourceDocumentId
FROM event_log
WHERE ShipmentId IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY ShipmentId, EventTime, ActivityName;

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.