Szállítmányozási adatsablon

WITH SHIPMENTS_BASE AS (
    SELECT 
        S.SHIPMENT_GID AS ShipmentId,
        SP.SERVPROV_NAME AS CarrierName,
        S.TOTAL_ACTUAL_COST AS ShipmentCost,
        TM.TRANSPORT_MODE_NAME AS TransportationMode,
        (
            SELECT MAX(ORL.LATE_DELIVERY_DATE)
            FROM SHIPMENT_S_SHIP_UNIT SSU 
            JOIN S_SHIP_UNIT SU ON SSU.S_SHIP_UNIT_GID = SU.S_SHIP_UNIT_GID
            JOIN ORDER_RELEASE ORL ON SU.ORDER_RELEASE_GID = ORL.ORDER_RELEASE_GID
            WHERE SSU.SHIPMENT_GID = S.SHIPMENT_GID
        ) AS RequestedDeliveryDate,
        S.DOMAIN_NAME
    FROM SHIPMENT S
    LEFT JOIN SERVPROV SP ON S.SERVPROV_GID = SP.SERVPROV_GID
    LEFT JOIN TRANSPORT_MODE TM ON S.TRANSPORT_MODE_GID = TM.TRANSPORT_MODE_GID
    WHERE S.INSERT_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD') + 1
    AND S.DOMAIN_NAME = :P_DOMAIN_NAME
)
-- 1. Transportation Request Received
SELECT 
    SB.ShipmentId,
    'Transportation Request Received' AS ActivityName,
    MIN(ORL.INSERT_DATE) AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_S_SHIP_UNIT SSU ON SB.ShipmentId = SSU.SHIPMENT_GID
JOIN S_SHIP_UNIT SU ON SSU.S_SHIP_UNIT_GID = SU.S_SHIP_UNIT_GID
JOIN ORDER_RELEASE ORL ON SU.ORDER_RELEASE_GID = ORL.ORDER_RELEASE_GID
WHERE SB.DOMAIN_NAME = ORL.DOMAIN_NAME
GROUP BY SB.ShipmentId, SB.CarrierName, SB.RequestedDeliveryDate, SB.ShipmentCost, SB.TransportationMode

UNION ALL

-- 2. Shipment Planned
SELECT 
    SB.ShipmentId, 
    'Shipment Planned' AS ActivityName,
    SS.STATUS_CHANGE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_STATUS SS ON SB.ShipmentId = SS.SHIPMENT_GID
WHERE SS.STATUS_CODE_GID = 'PLANNING_PLANNED FINAL'

UNION ALL

-- 3. Carrier Assigned
SELECT 
    SB.ShipmentId, 
    'Carrier Assigned' AS ActivityName,
    ST.UPDATE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_TENDER ST ON SB.ShipmentId = ST.SHIPMENT_GID
WHERE ST.TENDER_STATUS_GID = 'TENDER_ACCEPTED'

UNION ALL

-- 4. Shipment Booked
SELECT 
    SB.ShipmentId, 
    'Shipment Booked' AS ActivityName,
    SS.STATUS_CHANGE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_STATUS SS ON SB.ShipmentId = SS.SHIPMENT_GID
WHERE SS.STATUS_CODE_GID = 'SECURE RESOURCES_BOOKED'

UNION ALL

-- 5. Pickup Appointment Scheduled
SELECT 
    SB.ShipmentId,
    'Pickup Appointment Scheduled' AS ActivityName,
    SST.APPOINTMENT_START_TIME AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_STOP SST ON SB.ShipmentId = SST.SHIPMENT_GID
WHERE SST.STOP_NUM = 1 AND SST.APPOINTMENT_START_TIME IS NOT NULL

UNION ALL

-- 6. Goods Picked Up
SELECT 
    SB.ShipmentId,
    'Goods Picked Up' AS ActivityName,
    SST.ACTUAL_DEPARTURE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_STOP SST ON SB.ShipmentId = SST.SHIPMENT_GID
WHERE SST.STOP_NUM = 1 AND SST.ACTUAL_DEPARTURE_DATE IS NOT NULL

UNION ALL

-- 7. Shipment In Transit
SELECT 
    SB.ShipmentId,
    'Shipment In Transit' AS ActivityName,
    SST.ACTUAL_DEPARTURE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_STOP SST ON SB.ShipmentId = SST.SHIPMENT_GID
WHERE SST.STOP_NUM = 1 AND SST.ACTUAL_DEPARTURE_DATE IS NOT NULL

UNION ALL

-- 8. Customs Cleared
SELECT 
    SB.ShipmentId,
    'Customs Cleared' AS ActivityName,
    SE.EVENT_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    SE.EVENT_REMARK_TEXT AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_EVENT SE ON SB.ShipmentId = SE.SHIPMENT_GID
WHERE SE.EVENT_CODE = '[Your Customs Cleared Event Code]'

UNION ALL

-- 9. Delivery Appointment Scheduled
SELECT 
    SB.ShipmentId,
    'Delivery Appointment Scheduled' AS ActivityName,
    SST.APPOINTMENT_START_TIME AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_STOP SST ON SB.ShipmentId = SST.SHIPMENT_GID
WHERE SST.STOP_NUM = (SELECT MAX(SST2.STOP_NUM) FROM SHIPMENT_STOP SST2 WHERE SST2.SHIPMENT_GID = SB.ShipmentId) 
AND SST.APPOINTMENT_START_TIME IS NOT NULL

UNION ALL

-- 10. Goods Delivered
SELECT 
    SB.ShipmentId,
    'Goods Delivered' AS ActivityName,
    SST.ACTUAL_ARRIVAL_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_STOP SST ON SB.ShipmentId = SST.SHIPMENT_GID
WHERE SST.STOP_NUM = (SELECT MAX(SST2.STOP_NUM) FROM SHIPMENT_STOP SST2 WHERE SST2.SHIPMENT_GID = SB.ShipmentId) 
AND SST.ACTUAL_ARRIVAL_DATE IS NOT NULL

UNION ALL

-- 11. Proof of Delivery Received
SELECT 
    SB.ShipmentId,
    'Proof of Delivery Received' AS ActivityName,
    SE.EVENT_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    SE.EVENT_REMARK_TEXT AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT_EVENT SE ON SB.ShipmentId = SE.SHIPMENT_GID
WHERE SE.EVENT_CODE = '[Your POD Received Event Code]'

UNION ALL

-- 12. Freight Bill Audited
SELECT 
    SB.ShipmentId,
    'Freight Bill Audited' AS ActivityName,
    B.UPDATE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN BILL B ON SB.ShipmentId = B.SHIPMENT_GID
WHERE B.BILL_STATUS_GID = 'VOUCHER_AUDITED'

UNION ALL

-- 13. Payment Processed
SELECT 
    SB.ShipmentId,
    'Payment Processed' AS ActivityName,
    B.UPDATE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN BILL B ON SB.ShipmentId = B.SHIPMENT_GID
WHERE B.BILL_STATUS_GID = '[Your Payment Processed Bill Status]'

UNION ALL

-- 14. Shipment Cancelled
SELECT 
    SB.ShipmentId,
    'Shipment Cancelled' AS ActivityName,
    S.UPDATE_DATE AS EventTime,
    SB.CarrierName,
    SB.RequestedDeliveryDate,
    SB.ShipmentCost,
    SB.TransportationMode,
    NULL AS DelayReason
FROM SHIPMENTS_BASE SB
JOIN SHIPMENT S ON SB.ShipmentId = S.SHIPMENT_GID
WHERE S.SHIPMENT_STATUS_GID = 'SHIPMENT_CANCELLED'

WITH ShipmentBase AS (
  SELECT s.SHIPMENT_GID, s.SERVPROV_GID, s.LATE_DELIVERY_DATE, s.TRANSPORT_MODE_GID, sc.TotalShipmentCost
  FROM GLOGOWNER.SHIPMENT s
  LEFT JOIN (
    SELECT sc.SHIPMENT_GID, SUM(sc.COST) as TotalShipmentCost
    FROM GLOGOWNER.SHIPMENT_COST sc
    GROUP BY sc.SHIPMENT_GID
  ) sc ON s.SHIPMENT_GID = sc.SHIPMENT_GID
  WHERE s.INSERT_DATE BETWEEN TO_DATE('[START_DATE]', 'YYYY-MM-DD') AND TO_DATE('[END_DATE]', 'YYYY-MM-DD')
  AND s.DOMAIN_NAME = '[YOUR_DOMAIN_NAME]'
),
OrderReleaseInfo AS (
  SELECT ssu.SHIPMENT_GID, MIN(orl.INSERT_DATE) as Earliest_OR_Date
  FROM GLOGOWNER.S_SHIP_UNIT ssu
  JOIN GLOGOWNER.S_SHIP_UNIT_LINE ssul ON ssu.S_SHIP_UNIT_GID = ssul.S_SHIP_UNIT_GID
  JOIN GLOGOWNER.ORDER_RELEASE_LINE orl ON ssul.ORDER_RELEASE_LINE_GID = orl.ORDER_RELEASE_LINE_GID
  GROUP BY ssu.SHIPMENT_GID
)
-- 1. Transportation Request Received
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Transportation Request Received' AS "ActivityName",
  ori.Earliest_OR_Date AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN OrderReleaseInfo ori ON sb.SHIPMENT_GID = ori.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE ori.Earliest_OR_Date IS NOT NULL
UNION ALL
-- 2. Shipment Planned
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Shipment Planned' AS "ActivityName",
  ss.INSERT_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STATUS ss ON sb.SHIPMENT_GID = ss.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE ss.STATUS_CODE_GID = 'PLANNING_PLANNED FINAL'
UNION ALL
-- 3. Carrier Assigned
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Carrier Assigned' AS "ActivityName",
  ss.INSERT_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STATUS ss ON sb.SHIPMENT_GID = ss.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE ss.STATUS_CODE_GID = 'TENDER_ACCEPTED'
UNION ALL
-- 4. Shipment Booked
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Shipment Booked' AS "ActivityName",
  ss.INSERT_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STATUS ss ON sb.SHIPMENT_GID = ss.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE ss.STATUS_CODE_GID = 'SECURE RESOURCES_BOOKED'
UNION ALL
-- 5. Pickup Appointment Scheduled
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Pickup Appointment Scheduled' AS "ActivityName",
  stp.APPT_START_TIME AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STOP stp ON sb.SHIPMENT_GID = stp.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE stp.STOP_NUM = 1 AND stp.APPT_START_TIME IS NOT NULL
UNION ALL
-- 6. Goods Picked Up
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Goods Picked Up' AS "ActivityName",
  stp.ACTUAL_DEPARTURE_TIME AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STOP stp ON sb.SHIPMENT_GID = stp.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE stp.STOP_NUM = 1 AND stp.ACTUAL_DEPARTURE_TIME IS NOT NULL
UNION ALL
-- 7. Shipment In Transit
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Shipment In Transit' AS "ActivityName",
  stp.ACTUAL_DEPARTURE_TIME AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STOP stp ON sb.SHIPMENT_GID = stp.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE stp.STOP_NUM = 1 AND stp.ACTUAL_DEPARTURE_TIME IS NOT NULL
UNION ALL
-- 8. Customs Cleared
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Customs Cleared' AS "ActivityName",
  se.EVENT_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  se.REMARK_TEXT AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_EVENT se ON sb.SHIPMENT_GID = se.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE se.EVENT_CODE_GID = '[Your Customs Cleared Event Code]'
UNION ALL
-- 9. Delivery Appointment Scheduled
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Delivery Appointment Scheduled' AS "ActivityName",
  stp.APPT_START_TIME AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STOP stp ON sb.SHIPMENT_GID = stp.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE stp.STOP_NUM = (SELECT MAX(s2.STOP_NUM) FROM GLOGOWNER.SHIPMENT_STOP s2 WHERE s2.SHIPMENT_GID = stp.SHIPMENT_GID)
AND stp.APPT_START_TIME IS NOT NULL
UNION ALL
-- 10. Goods Delivered
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Goods Delivered' AS "ActivityName",
  stp.ACTUAL_ARRIVAL_TIME AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STOP stp ON sb.SHIPMENT_GID = stp.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE stp.STOP_NUM = (SELECT MAX(s2.STOP_NUM) FROM GLOGOWNER.SHIPMENT_STOP s2 WHERE s2.SHIPMENT_GID = stp.SHIPMENT_GID)
AND stp.ACTUAL_ARRIVAL_TIME IS NOT NULL
UNION ALL
-- 11. Proof of Delivery Received
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Proof of Delivery Received' AS "ActivityName",
  d.INSERT_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.DOCUMENT d ON sb.SHIPMENT_GID = d.OBJECT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE d.OBJECT_TYPE_GID = 'SHIPMENT' AND d.DOCUMENT_TYPE_GID = 'POD'
UNION ALL
-- 12. Freight Bill Audited
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Freight Bill Audited' AS "ActivityName",
  b.UPDATE_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.BILL b ON sb.SHIPMENT_GID = b.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE b.APPROVAL_STATUS_GID = 'VOUCHER_AUDITED'
UNION ALL
-- 13. Payment Processed
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Payment Processed' AS "ActivityName",
  v.UPDATE_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.BILL b ON sb.SHIPMENT_GID = b.SHIPMENT_GID
JOIN GLOGOWNER.VOUCHER v ON b.BILL_GID = v.BILL_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE v.VOUCHER_STATUS_GID = 'VOUCHER_PAID' -- This status may vary based on configuration
UNION ALL
-- 14. Shipment Cancelled
SELECT
  sb.SHIPMENT_GID AS "ShipmentId",
  'Shipment Cancelled' AS "ActivityName",
  ss.INSERT_DATE AS "EventTime",
  sp.SERVPROV_NAME AS "CarrierName",
  sb.LATE_DELIVERY_DATE AS "RequestedDeliveryDate",
  sb.TotalShipmentCost AS "ShipmentCost",
  sb.TRANSPORT_MODE_GID AS "TransportationMode",
  CAST(NULL AS VARCHAR(255)) AS "DelayReason"
FROM ShipmentBase sb
JOIN GLOGOWNER.SHIPMENT_STATUS ss ON sb.SHIPMENT_GID = ss.SHIPMENT_GID
LEFT JOIN GLOGOWNER.SERVPROV sp ON sb.SERVPROV_GID = sp.SERVPROV_GID
WHERE ss.STATUS_CODE_GID LIKE '%CANCELLED%';