Taşıma Yönetimi Veri Template'inuz

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 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'

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.