Uw template voor Transportation Management-data.
Uw template voor Transportation Management-data.
- Aanbevolen attributen om vast te leggen
- Belangrijkste activiteiten om te volgen
- Richtlijnen voor data-extractie
Transport Management Attributes
| Naam | Omschrijving | ||
|---|---|---|---|
| Activiteitsnaam ActivityName | De naam van een specifiek `event` of mijlpaal die heeft plaatsgevonden binnen het transportproces voor een `shipment`. | ||
| Omschrijving Dit Het analyseren van de sequentie en duur van deze activiteiten is de kern van Het belang Het definieert de stappen in de proceskaart, waardoor visualisatie en analyse van de reis van de zending, bottlenecks en afwijkingen mogelijk zijn. Vindplaats Deze informatie is afgeleid van Voorbeelden Zending geplandGoederen afgehaaldDouane afgehandeldGoederen geleverd | |||
| Shipment ShipmentId | De unieke identificatie voor een enkele `shipment`, die dient als de primaire `case ID` voor het volgen van alle gerelateerde transportactiviteiten. | ||
| Omschrijving Het In Het belang Dit is de essentiële Vindplaats Dit is een Voorbeelden SH-0012345SH-0012346SH-0012347 | |||
| Tijdstip Gebeurtenis EventTime | De exacte datum en tijd waarop een specifieke transportactiviteit of `event` plaatsvond. | ||
| Omschrijving Deze De nauwkeurigheid van deze Het belang Deze Vindplaats Afkomstig van de Voorbeelden 2023-10-26T08:00:00Z2023-10-27T14:30:00Z2023-10-28T09:15:00Z | |||
| `Shipment` Kosten ShipmentCost | De totale vrachtkosten of het financiële bedrag dat in rekening wordt gebracht voor het transporteren van de `shipment`. | ||
| Omschrijving Dit Het analyseren van Het belang Levert de financiële Vindplaats Deze informatie wordt doorgaans gevonden in de financiële of kostensecties van een Voorbeelden 250.751200.00545.50 | |||
| Gewenste leverdatum RequestedDeliveryDate | De leverdatum en -tijd zoals aangevraagd door de klant of overeengekomen als de `service level agreement` (`SLA`) doelstelling. | ||
| Omschrijving Dit In Het belang Dit is de primaire benchmark voor het meten van Vindplaats Deze informatie wordt doorgaans op Voorbeelden 2023-11-15T17:00:00Z2023-11-20T12:00:00Z2023-11-22T09:00:00Z | |||
| Naam vervoerder CarrierName | De naam van de transport `carrier` of dienstverlener die verantwoordelijk is voor het verplaatsen van de `shipment`. | ||
| Omschrijving Dit Het analyseren van prestaties per Het belang Maakt prestatiebenchmarking mogelijk tussen verschillende vervoerders, wat cruciaal is voor het optimaliseren van vervoerderskeuze, onderhandelingen en kostenreductie. Vindplaats Gevonden in zendingdetails binnen OTM, gekoppeld aan de toegewezen vervoerder- of serviceproviderrecords. Het kan worden opgeslagen in tabellen gerelateerd aan zendingen en serviceproviders. Voorbeelden FedEx FreightUPS Supply Chain SolutionsMaersk LineKnight-Swift Transportation | |||
| Reden van vertraging DelayReason | Een code of beschrijving die de oorzaak verklaart van een vertraging in de voortgang van de zending. | ||
| Omschrijving Wanneer een zending vertraagd is, geeft dit attribute de reden aan, zoals 'Weer', 'Douane oponthoud', 'Havencongestie' of 'Mechanisch defect'. Deze informatie wordt vastgelegd via updates van vervoerders of handmatige invoer in OTM. Dit attribute is essentieel voor grondoorzaakanalyse van late leveringen. Door vertragingsredenen te categoriseren en te tellen, kunnen bedrijven de meest voorkomende problemen in hun toeleveringsketen identificeren. Dit maakt gerichte verbeterinitiatieven mogelijk, zoals het selecteren van betrouwbaardere vervoerders voor specifieke routes of het verbeteren van documentatie om de douaneafhandeling te versnellen. Het belang Biedt cruciaal inzicht in de diepere oorzaken van late Vindplaats Doorgaans vastgelegd in Voorbeelden WEATHER_DELAYCUSTOMS_INSPECTIONHAVENCONGESTIETRAFFIC | |||
| Transportation Mode TransportationMode | De transportmethode die gebruikt wordt voor de `shipment`, zoals vrachtwagen, trein, zee of lucht. | ||
| Omschrijving Dit In analyse is Het belang Maakt prestatie- en kostenanalyse mogelijk, gesegmenteerd per transportmodus, wat inzichten oplevert die specifiek zijn voor vrachtwagen-, lucht-, zee- of spoorlogistiek. Vindplaats Dit is een standaard Voorbeelden FTLLTLZEELUCHT | |||
| `Shipment` Status ShipmentStatus | De algehele huidige status van de `shipment` in zijn `lifecycle`, zoals 'Planned', 'In Transit' of 'Delivered'. | ||
| Omschrijving Dit Terwijl Het belang Biedt een overzicht op hoofdlijnen van de voortgang van een Vindplaats Beschikbaar op de hoofd-zendingrecord in OTM, en vertegenwoordigt de huidige status in de workflow. Voorbeelden GEPLANDBOOKEDIN_TRANSITGELEVERD | |||
| `Shipment` Type ShipmentType | Een classificatie van de zending, zoals per serviceniveau, type grondstof of business unit. | ||
| Omschrijving Dit Het gebruik van Het belang Maakt de segmentatie van analyse op basis van bedrijfscontext mogelijk, wat helpt te begrijpen of verschillende typen zendingen verschillende processen volgen of verschillende prestatieniveaus hebben. Vindplaats Dit is doorgaans een configureerbaar Voorbeelden StandaardVersneldInternationaalBinnenlands | |||
| Bronsysteem SourceSystem | Identificeert het systeem waaruit de event data afkomstig is, bijvoorbeeld Oracle Transportation Management. | ||
| Omschrijving Dit Het begrijpen van het bronsysteem is belangrijk voor Het belang Het biedt cruciale context over data-oorsprong, wat belangrijk is voor data-validatie en voor analyses in multi-systeemomgevingen. Vindplaats Dit is doorgaans een statische waarde die tijdens het data-extractieproces wordt toegevoegd om de herkomst van de records te labelen. Voorbeelden Oracle OTMOTM CloudSAP TM | |||
| Douane-agent CustomsAgent | De douane-expediteur of agent die verantwoordelijk is voor het beheren van het douaneafhandelingsproces voor de `shipment`. | ||
| Omschrijving Voor internationale zendingen identificeert dit attribuut de externe agent of het interne team dat belast is met de afhandeling van douanedocumentatie en -procedures. De prestaties van deze agent kunnen een aanzienlijke impact hebben op de totale transittijd. Het analyseren van de douaneafhandelingscyclustijd per douane-agent helpt hun prestaties te benchmarken. Het kan identificeren welke agenten het meest efficiënt zijn in specifieke landen of voor bepaalde type goederen. Dit inzicht is waardevol voor het selecteren van de beste douanepartners en voor het samenwerken met minder presterende agenten om hun processen te verbeteren. Het belang Maakt prestatiemeting van douane-expediteurs mogelijk, wat helpt bij het identificeren van de meest efficiënte partners en het verminderen van douanegerelateerde vertragingen. Vindplaats Deze informatie kan worden opgeslagen als een serviceprovider of partijrol op de internationale Voorbeelden Global Customs ServicesLivingston InternationalFlexport | |||
| End-to-end cyclustijd EndToEndCycleTime | De totale duur van de `shipment` vanaf de initiële transportaanvraag tot de uiteindelijke levering van goederen. | ||
| Omschrijving Deze berekende Dit Het belang Biedt één cruciale maatstaf voor de algehele procesnelheid, wat helpt bij het identificeren van langlopende Vindplaats Dit Voorbeelden 5 dagen 4 uur12 dagen 8 uur 30 minuten2 dagen 1 uur | |||
| Gebruikersnaam UserName | De naam of `ID` van de gebruiker die een specifieke activiteit heeft uitgevoerd of daarvoor verantwoordelijk is. | ||
| Omschrijving Dit In Het belang Zorgt voor verantwoording en maakt analyse van werkdruk en prestaties per individuele gebruiker of team mogelijk. Vindplaats Afkomstig van Voorbeelden JSMITHLOGISTICS_PLANNER_ASYSTEM_AUTO | |||
| Is omgeleid IsRerouted | Een vlag die aangeeft of de route van de zending is gewijzigd na de initiële boeking. | ||
| Omschrijving Dit Dit Het belang Helpt de frequentie en impact van operationele uitzonderingen zoals omleidingen te kwantificeren, en benadrukt inefficiënties in het plannings- en uitvoeringsproces. Vindplaats Deze Voorbeelden truefalse | |||
| Is Tijdige Levering IsOnTimeDelivery | Een berekende vlag die aangeeft of de zending is geleverd op of vóór de aangevraagde leveringsdatum. | ||
| Omschrijving Dit Dit Het belang Deze berekende Vindplaats Dit Voorbeelden truefalse | |||
| Laatste data-update LastDataUpdate | De timestamp die aangeeft wanneer de data van deze event voor het laatst werd ververst of geëxtraheerd uit het bronsysteem. | ||
| Omschrijving Dit Deze Het belang Geeft de actualiteit van de data aan, zodat gebruikers weten hoe actueel de analyse is en wanneer de volgende data-verversing wordt verwacht. Vindplaats Dit is doorgaans een systeem gegenereerde Voorbeelden 2023-11-01T02:00:00Z2023-11-02T02:00:00Z | |||
| Land van bestemming DestinationCountry | Het land waar de `shipment` gepland staat om geleverd te worden. | ||
| Omschrijving Dit Vergelijkbaar met de oorsprong is analyse per Het belang Cruciaal voor het analyseren van prestaties op specifieke handelsroutes en het begrijpen van uitdagingen met betrekking tot douane, vervoerders en regelgeving in bestemmingsmarkten. Vindplaats Afgeleid van de adresdetails van de bestemmingslocatie of ontvanger van de zending, opgeslagen in de OTM master data voor locaties. Voorbeelden CANFRAJPNBRA | |||
| Land van Herkomst OriginCountry | Het land waar de reis van de `shipment` begint. | ||
| Omschrijving Dit Het analyseren van processen per Het belang Maakt geografische analyse van het transportproces mogelijk, wat helpt bij het identificeren van regiospecifieke bottlenecks, kosten en prestatieverschillen. Vindplaats Afgeleid van de adresgegevens van de bronlocatie of afzender van de zending, die is opgeslagen in de locatie master data van OTM. Voorbeelden USADEUCHNMEX | |||
| Vrachtfactuurafwijking FreightBillDiscrepancy | Een vlag die aangeeft of er een afwijking is gevonden tijdens de controle van de vrachtfactuur. | ||
| Omschrijving Dit Dit Het belang Ondersteunt direct de Vrachtfactuurafwijkingenanalyse door zendingen met factureringsfouten te markeren, wat helpt om overbetalingen te verminderen en financiële controles te verbeteren. Vindplaats Dit is doorgaans een Voorbeelden truefalse | |||
Transport Management Activiteiten
| Activiteit | Omschrijving | ||
|---|---|---|---|
| Betaling Verwerkt | Dit is de laatste activiteit, die de succesvolle verwerking van de betaling aan de `carrier` voor hun diensten vertegenwoordigt. Dit `event` markeert de financiële afsluiting van de `shipment`. | ||
| Het belang Deze activiteit sluit de transport Vindplaats Afgeleid van de statuswijziging van de bijbehorende voucher of factuur naar een 'PAID' status. Deze data bevindt zich in de VOUCHER of BILL tabellen. Vastleggen Identificeer de timestamp wanneer de VOUCHER.VOUCHER_STATUS_ID verandert naar 'PAID'. Gebeurtenistype inferred | |||
| Bewijs van Levering Ontvangen | Dit vertegenwoordigt de ontvangst en registratie van een formele bevestiging dat de levering is voltooid, zoals een ondertekend document. Dit wordt vaak vastgelegd als een specifiek documenttype dat aan de `shipment` is gekoppeld. | ||
| Het belang
Vindplaats Dit kan een expliciet Vastleggen Gebruik de Gebeurtenistype explicit | |||
| Goederen afgehaald | Deze activiteit markeert het begin van het fysieke transport, wanneer de `carrier` de goederen vanaf de oorsprong heeft opgehaald. Dit `event` wordt vastgelegd wanneer de eerste `stop` van de `shipment` is gemarkeerd als 'DEPARTED'. | ||
| Het belang Dit is een kritieke mijlpaal die het begin van de Vindplaats Afgeleid van de statuswijziging van de eerste stop in de SHIPMENT_STOP tabel naar 'DEPARTED', of door een zendingstatus-event te volgen dat afhaling aangeeft. Vastleggen Gebruik de ACTUAL_DEPARTURE Gebeurtenistype inferred | |||
| Goederen geleverd | Deze activiteit vertegenwoordigt de succesvolle levering van de `shipment` aan de uiteindelijke bestemming, de geadresseerde. Dit wordt vastgelegd wanneer de laatste `stop` op de `shipment` is gemarkeerd met een daadwerkelijke aankomsttijd. | ||
| Het belang Dit is de primaire mijlpaal voor het meten van Vindplaats Afgeleid uit het vullen van het veld ACTUAL_ARRIVAL op de laatste stoprecord in de SHIPMENT_STOP tabel die is gekoppeld aan de zending. Vastleggen Gebruik de ACTUAL_ARRIVAL Gebeurtenistype inferred | |||
| Transportaanvraag Ontvangen | Deze activiteit markeert de creatie van een transportbehoefte binnen het systeem, doorgaans als een `Order Release`. Dit `event` wordt vastgelegd wanneer een nieuwe `Order Release` record wordt gecreëerd in Oracle Transportation Management, wat de initiële vraag naar `shipment` vertegenwoordigt. | ||
| Het belang Als startpunt van het proces is deze activiteit cruciaal voor het meten van de totale end-to-end cyclustijd en het begrijpen van vraagpatronen. Het helpt de tijd te analyseren die nodig is van aanvraag tot planning. Vindplaats Dit is een expliciet Vastleggen Gebruik de Gebeurtenistype explicit | |||
| Zending gepland | Deze activiteit vertegenwoordigt de succesvolle planning van één of meer `order releases` tot een uitvoerbare `shipment` door de OTM planning `engine`. Het wordt vastgelegd wanneer het systeem de status van een `shipment` wijzigt om aan te geven dat deze is gepland, bijvoorbeeld 'PLANNING_PLANNED FINAL'. | ||
| Het belang Dit is een belangrijke mijlpaal die de vraagfase scheidt van de uitvoeringsfase. Het analyseren van de tijd die naar deze activiteit leidt, helpt bij het identificeren van planningsefficiëntie en Vindplaats Afgeleid van de statusgeschiedenis van het Shipment object in de SHIPMENT tabel. Zoek naar een statuswijziging die planningvoltooiing aangeeft. Vastleggen Identificeer de timestamp wanneer de SHIPMENT.SHIPMENT_STATUS_VALUE_GID verandert naar een 'Planned' status. Gebeurtenistype inferred | |||
| `Shipment` Geannuleerd | Vertegenwoordigt de annulering van een `shipment` nadat deze is gepland, waarbij de `lifecycle` voortijdig wordt beëindigd. Dit wordt vastgelegd door een statuswijziging op het `Shipment object` naar een 'cancelled' status. | ||
| Het belang Het volgen van annuleringen is belangrijk voor het begrijpen van procesuitzonderingen en -fouten. Het helpt redenen voor annulering te identificeren, zoals wijzigingen in de vraag of planningsfouten. Vindplaats Afgeleid van de statusgeschiedenis van het Shipment object in de SHIPMENT tabel. Zoek naar een statuswijziging zoals 'PLANNING_CANCELED'. Vastleggen Identificeer de timestamp wanneer de SHIPMENT.SHIPMENT_STATUS_VALUE_GID verandert naar een 'Cancelled' status. Gebeurtenistype inferred | |||
| `Shipment` Geboekt | Vertegenwoordigt de formele boekingsbevestiging met de `carrier` na een succesvolle `tender`. Dit wordt doorgaans vastgelegd wanneer de `shipment status` wordt bijgewerkt naar een 'SECURE RESOURCES_BOOKED' of gelijkwaardige status. | ||
| Het belang Dit bevestigt de toezegging van de Vindplaats Afgeleid van een statuswijziging op het Shipment object in de SHIPMENT tabel, waarbij wordt bijgehouden wanneer het naar een bevestigde of geboekte status overgaat na toewijzing van de vervoerder. Vastleggen Identificeer de timestamp wanneer SHIPMENT.SHIPMENT_STATUS_VALUE_GID verandert naar een 'Booked' status. Gebeurtenistype inferred | |||
| `Shipment` In Transit | Geeft aan dat de goederen zijn afgehaald en nu actief onderweg zijn naar de bestemming. Dit is geen enkel tijdstip, maar het begin van deze staat wordt vastgelegd direct na de activiteit 'Goederen afgehaald'. | ||
| Het belang Deze activiteit markeert het begin van de Vindplaats Dit is hetzelfde Vastleggen Zelfde als 'Goederen Opgehaald': Gebruik de ACTUAL_DEPARTURE Gebeurtenistype inferred | |||
| Douane afgehandeld | Voor internationale zendingen vertegenwoordigt deze activiteit het punt waarop de goederen de douane succesvol zijn gepasseerd bij een grens of haven. Dit wordt vaak vastgelegd via een specifieke zendingsevent of statusupdate. | ||
| Het belang Douaneafhandeling is een veelvoorkomende bron van aanzienlijke vertragingen in internationale logistiek. Het volgen van deze activiteit helpt bij het meten en optimaliseren van de douaneafhandelingscyclustijden. Vindplaats Dit Vastleggen Identificeer de timestamp van een 'Customs Cleared' event uit de SHIPMENT_STATUS tabel. Gebeurtenistype explicit | |||
| Leveringsafspraak gepland | Deze activiteit markeert wanneer een specifieke datum en tijd voor levering is gepland met de geadresseerde. Dit `event` wordt vastgelegd vanuit afspraak `data` op de laatste `stop` van de `shipment`. | ||
| Het belang Dit is een belangrijke stap in de coördinatie van de uiteindelijke levering. Het analyseren van de tijd tussen aankomst en de geplande levering kan knelpunten in de Vindplaats Afgeleid uit het vullen van afspraakdatum- en tijdvelden voor de laatste stop (afleverlocatie) van de zending, te vinden in de SHIPMENT_STOP tabel. Vastleggen Gebruik de Gebeurtenistype inferred | |||
| Ophaalafspraak Gepland | Deze activiteit geeft aan dat een specifieke datum en tijd voor afhaling is gepland en vastgelegd. Dit wordt vaak vastgelegd vanuit afspraakgerelateerde `data` op het `stop level` van de `shipment`. | ||
| Het belang Het plannen van afspraken is een belangrijke coördinatiestap. Het analyseren hiervan helpt de efficiëntie van de planning en de impact ervan op de punctualiteit van de afhaling te begrijpen. Vindplaats Afgeleid uit het vullen van afspraakdatum- en tijdvelden voor de eerste stop (afhaalocatie) van de zending, te vinden in de SHIPMENT_STOP tabel. Vastleggen Gebruik de Gebeurtenistype inferred | |||
| Vervoerder toegewezen | Dit markeert het punt waarop een `shipment` succesvol is aangeboden aan en geaccepteerd door een specifieke `carrier`. Dit wordt vastgelegd door de wijziging in de `tender status` van de `shipment` naar 'TENDER_ACCEPTED' of een vergelijkbare staat te observeren. | ||
| Het belang Het volgen hiervan helpt bij het analyseren van de Vindplaats Afgeleid van de statusgeschiedenis in de SHIPMENT_TENDER tabel, specifiek zoekend naar de timestamp wanneer de status acceptatie door een vervoerder aangeeft. Vastleggen Gebruik de Gebeurtenistype inferred | |||
| Vrachtfactuur gecontroleerd | Deze activiteit markeert de voltooiing van het vrachtfactuurcontroleproces, waarbij de factuur van de `carrier` wordt afgestemd en geverifieerd tegen de `shipment`details. Het wordt vastgelegd wanneer de factuurstatus van een `shipment` overgaat naar 'VOUCHER_AUDITED' of een vergelijkbare staat. | ||
| Het belang Auditing is cruciaal voor financiële controle en het identificeren van factuurafwijkingen. Het analyseren van deze activiteit helpt het audit-naar-betaalproces te stroomlijnen en ondersteunt de KPI 'Vrachtfactuurafwijkingspercentage'. Vindplaats Afgeleid van de statuswijziging op het Bill object dat gekoppeld is aan de zending. Zoek in de BILL tabel naar statusupdates gerelateerd aan de voltooiing van de audit. Vastleggen Identificeer de timestamp wanneer de BILL.BILL_STATUS_VALUE_GID verandert naar een 'Audited' status. Gebeurtenistype inferred | |||
Extractie Guides
Stappen
- Toegang tot BI Publisher: Log in bij uw Oracle Transportation Management (OTM)-applicatie. Navigeer naar Business Process Automation, vervolgens naar Reporting, en selecteer Report Manager. Hiermee opent de Oracle Business Intelligence Publisher-interface.
- Een Datamodel Creëren: Klik in BI Publisher op de knop Nieuw en selecteer Datamodel. Hiermee opent de datamodeleditor waar u de databron voor uw rapport definieert.
- De SQL-query Definiëren: Klik binnen de datamodeleditor op het pluspictogram in het Data Sets diagram en selecteer SQL Query. Geef de dataset een naam, bijvoorbeeld 'OTM_Process_Mining_DS'. Selecteer uw OTM-database als Databron. Kopieer de complete SQL-query uit de sectie 'query' van dit document en plak deze in het tekstvak SQL Query.
- Parameters Toevoegen: De query gebruikt parameters voor het datumbereik en de domeinnaam (:P_START_DATE, :P_END_DATE, :P_DOMAIN_NAME). BI Publisher detecteert deze automatisch. U kunt hun eigenschappen configureren, zoals het instellen van het datatype op Datum voor de datumparameters.
- Het Datamodel Opslaan: Klik op het opslaan-pictogram. Kies een locatie in de BI Publisher-catalogus, geef een beschrijvende naam zoals 'OTM Shipment Process Extraction' en sla het datamodel op.
- Het Rapport Creëren: Navigeer terug naar de catalogus, klik op de knop Nieuw en selecteer Rapport. Selecteer in de rapportcreatie-wizard de optie 'Gebruik Datamodel' en zoek het zojuist opgeslagen datamodel.
- De Rapportlay-out Configureren: De wizard begeleidt u bij het creëren van een lay-out. Een eenvoudige 'Tabel'-lay-out is voldoende. Sleep alle kolommen uit uw dataset naar de tabel. Voer geen groepering of aggregatie uit.
- Het Rapport Opslaan: Zodra de lay-out is geconfigureerd, gaat u naar de laatste stap en slaat u het rapport op. Geef het een naam die vergelijkbaar is met het datamodel.
- Het Rapport Uitvoeren: Open het zojuist gemaakte rapport. U wordt gevraagd waarden in te voeren voor de parameters (Startdatum, Einddatum, Domeinnaam). Voer het gewenste bereik in.
- De Data Exporteren: Nadat het rapport is uitgevoerd en de data wordt weergegeven, klikt u op het menu Acties en selecteert u Exporteren. Kies het CSV-formaat. Dit downloadt een CSV-bestand met de event log.
- Voorbereiden op Upload: Open het gedownloade CSV-bestand. Controleer of de kolomheaders overeenkomen met de vereiste attributes:
ShipmentId,ActivityName,EventTimeen de aanbevolen attributes. Zorg ervoor dat de kolomEventTimeeen consistent datum/tijd-formaat heeft. Het bestand is nu klaar voor upload naar ProcessMind.
Configuratie
- Databron: De query moet worden uitgevoerd op het primaire operationele OTM-databaseschema, doorgaans met de naam GLOGOWNER.
- Rapportparameters:
P_START_DATE: Het begin van de rapportageperiode. Het formaat moet 'JJJJ-MM-DD' zijn.P_END_DATE: Het einde van de rapportageperiode. Het formaat moet 'JJJJ-MM-DD' zijn.P_DOMAIN_NAME: Het specifieke OTM-domein waarvoor data moet worden geëxtraheerd. Gebruik '[Uw Domeinnaam]'.
- Datumbereik: Voor een eerste analyse wordt aanbevolen data te extraheren voor een periode van 3 tot 6 maanden om datavolume en prestaties in balans te houden. Voor productieruns kunt u overwegen data in maandelijkse of kwartaalbatches te extraheren.
- Machtigingen: De OTM-gebruiker die het rapport uitvoert, heeft specifieke rollen nodig, zoals BI PUBLISHER DATA MODEL DEVELOPER en BI PUBLISHER REPORT DEVELOPER, om datamodellen en rapporten te maken en te wijzigen. Alleen-lezen toegang tot de onderliggende databasetabellen is ook vereist.
- Prestaties: De query verbindt verschillende grote tabellen. Voor omgevingen met een hoog datavolume wordt ten zeerste aanbevolen dit rapport buiten piekuren uit te voeren om de systeemprestaties niet te beïnvloeden. Het datumbereik moet zo smal mogelijk worden gehouden om ervoor te zorgen dat het rapport tijdig wordt voltooid.
a Voorbeeldquery config
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' Stappen
- Databaseverbinding Tot Stand Brengen: Verkrijg alleen-lezen inloggegevens en verbindingsdetails (host, poort, service-ID) voor de Oracle Transportation Management database. U heeft toegang nodig tot het
GLOGOWNERschema. - SQL Client Configureren: Stel een SQL client-tool, zoals Oracle SQL Developer, DBeaver of Toad, in met de databaseverbindingsdetails van de vorige stap.
- De Query Voorbereiden: Kopieer de complete SQL-query uit dit document naar de query-editor van uw SQL client.
- Query Parameters Instellen: Zoek de placeholder-waarden binnen de
WHERE-clausules van de query. U moet[START_DATE],[END_DATE]en[YOUR_DOMAIN_NAME]vervangen door geschikte waarden voor uw extractie. Stel bijvoorbeeld de datums in om een periode van 3-6 maanden te omvatten en specificeer het operationele domein dat u wilt analyseren. - De Query Uitvoeren: Voer de gewijzigde SQL-query uit. Afhankelijk van het datumbereik en de systeemgrootte kan dit enkele minuten duren om te voltooien.
- De Resultaten Controleren: Zodra de query is voltooid, voert u een snelle controle van de output in uw client uit om ervoor te zorgen dat rijen worden geretourneerd en dat belangrijke kolommen zoals
ShipmentId,ActivityNameenEventTimezijn gevuld. - Exporteren naar CSV: Exporteer de volledige resultatenset naar een CSV-bestand. Gebruik standaard CSV-instellingen: komma als scheidingsteken, dubbele aanhalingstekens voor tekst en UTF-8-codering.
- Kolomheaders Finaliseren: Controleer vóór het uploaden of de kolomheaders in het CSV-bestand exact overeenkomen met de vereiste attributen:
ShipmentId,ActivityName,EventTime,CarrierName,RequestedDeliveryDate,ShipmentCost,TransportationModeenDelayReason. - Uploaden naar ProcessMind: Het voorbereide CSV-bestand is nu klaar voor upload en analyse in ProcessMind.
Configuratie
- Databaseschema: De query is ontworpen om te worden uitgevoerd op het standaard Oracle Transportation Management databaseschema, dat doorgaans
GLOGOWNERheet. - Vereiste Autorizaties: Een databasegebruiker met alleen-lezen toegang tot de tabellen waarnaar in de query wordt verwezen, is vereist. Deze tabellen omvatten
SHIPMENT,SHIPMENT_STOP,ORDER_RELEASE,SHIPMENT_STATUS,SERVPROV,SHIPMENT_COST,DOCUMENT,BILLenVOUCHER. - Domeinfiltering: Het is cruciaal om te filteren op
DOMAIN_NAMEin een multi-tenant OTM-omgeving. De query bevat een placeholder[YOUR_DOMAIN_NAME]die moet worden geconfigureerd om data voor de juiste bedrijfsentiteit te extraheren. - Datumbereik: Om de prestaties en het datavolume te beheren, moet u een start- en einddatum opgeven. Filteren op een geïndexeerde kolom zoals
SHIPMENT.INSERT_DATEwordt aanbevolen. Een periode van 3 tot 6 maanden is typerend voor een eerste analyse. - Statuscode-aanpassing: De query gebruikt veelvoorkomende, standaard statuscodes (bijv.
PLANNING_PLANNED FINAL,TENDER_ACCEPTED). Uw OTM-implementatie kan aangepaste statuscodes gebruiken. Mogelijk moet u de waarden in deWHERE-clausules aanpassen aan de configuratie van uw systeem.
a Voorbeeldquery sql
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%';