Development of a universal CRM System for financial accounting in International Freight Transportation and Customs Clearance: multilevel integration for all modes of transport

Introduction

International freight transportation, as the backbone of global trade, demands innovative solutions for managing various aspects of logistics, including cargo transport, customs documentation, cargo security, and coordination between carriers, freight forwarders, and clients. Managing this process becomes especially challenging when multiple types of transportation, from sea container shipments to road and air freight, need to be considered.

The complexity lies in the fact that each mode of transport has its unique characteristics. This paper focuses on the development of a universal CRM system that addresses the challenges of managing different types of international freight transport. We will discuss the system's technical aspects, architecture, data utilization, and process automation, as well as how the system adapts to the requirements of each type of transport operation.

Main part

Materials and Methods

System Architecture

The CRM system is developed based on a multi-layer architecture with a modular structure.

The main components include:

• Module for Sea Freight – for containerized and bulk cargo shipping.
• Module for Air Freight – for fast cargo transportation with tracking capabilities at all stages.
• Module for Road Freight – for land transportation of goods, including route management and traffic condition monitoring.
• Freight Forwarding Module – for overseeing all stages of cargo movement from port or airport to the final destination.
• Customs Clearance Module – for automating document handling at all customs checkpoints.

The technologies employed include:

• Frontend: Angular, HTML5, CSS3, Bootstrap for adaptive interfaces and user experience.
• Backend: Delphi for processing core business operations, REST API for communication with external systems.
• Database Management: MySQL for processing and storing large volumes of data regarding orders, cargo, and routes.
• Microservices Architecture: Utilizing Docker containers to ensure the system's modularity and flexibility, allowing it to adapt to various transportation modes.
• Integration with external systems via API for interacting with financial modules.

1. Technical Solutions for Different Transportation Modes

Sea Freight In sea freight, two types of cargo are of primary importance: containerized and bulk cargo. The CRM system must account for the unique aspects of each.

Fig. 1. Creation of sea freight in the CRM system

Variables and parameters related to sea freight:

• ContainerID – a unique identifier for the container used in sea transport. It is associated with cargo information and is used to track containers at all stages of the shipment process.
• VesselName – the name of the vessel carrying the cargo. This field allows the identification of the transport vehicle for sea freight.
• PortOfLoading – the loading port from which the cargo is dispatched. This parameter is essential for tracking the starting point of the sea freight route.
• PortOfDestination – the destination port to which the cargo is being transported. This represents the final point of the sea freight route.
• ContainerType – the type of container used for transporting the cargo. For instance, it may specify that the container is a 20-foot or 40-foot, refrigerated, or standard unit.
• CargoType – the type of cargo being transported by the vessel. This can include bulk cargo, containerized cargo, or hazardous materials.
• EstimatedTimeOfArrival (ETA) – the estimated time of arrival of the vessel at the destination port. This is a key parameter for coordinating logistics operations.
• DepartureTime – the departure time of the vessel from the loading port. This value enables the monitoring of the departure schedule.
• CurrentLocation – the real-time current location of the vessel or container, tracked using GPS. This parameter provides precise information about the cargo's location at a specific moment.
• ShippingLineID – the identifier of the shipping line managing the cargo transport. This is linked to the shipping company organizing the transportation.
• BillOfLadingNumber – the bill of lading number, which is a key document in sea freight. It confirms the receipt of the cargo by the shipping company and is used for its release at the destination port.
• FreightCost – the cost of sea freight, which includes transportation expenses, insurance, and other fees.
• CustomsClearanceStatus – the status of the cargo's customs clearance at the destination port. This variable indicates whether the cargo has passed inspection and is ready for further transportation.
• ShippingRoute – the shipping route of the vessel, including intermediate stopover ports. This parameter allows for planning and tracking the vessel's movement along the entire route.
• StorageConditions – the storage conditions of the cargo in the container. This parameter is especially important for goods that require specific temperature control or other storage conditions.
• HazardousMaterialsFlag – a flag indicating the presence of hazardous materials in the container. If this flag is activated, the system applies additional precautionary measures for handling and transporting the cargo.
• ContainerSealNumber – the container's seal number, which ensures the security and integrity of the cargo throughout the entire route.
• GrossWeight – the total weight of the container, including the weight of the cargo and the container itself. This parameter is used to calculate the vessel's load and for customs clearance.
• NetWeight – the weight of the cargo alone inside the container. This parameter is crucial for evaluating the cost of transportation and completing the necessary documentation.
• ShippingInstructions – instructions for transporting the cargo, which may include specific placement requirements on the vessel or special unloading conditions.
• Incoterms – international commercial terms that define the conditions for transporting and unloading the cargo, as well as the allocation of responsibility between the seller and the buyer.
• LoadingDock – the terminal or dock where the container is loaded onto the vessel.
• UnloadDock – the terminal or dock where the container is unloaded at the destination port.
• ContainerStatus – the current status of the container, such as "On Vessel," "In Transit," "Awaiting Unloading," etc.
• ShippingDuration – the duration of the sea freight. This parameter is calculated based on the departure time and the estimated time of arrival.
• FreightForwarderID – the identifier of the freight forwarder responsible for organizing the sea freight of the cargo.
• InsurancePolicyNumber – the number of the insurance policy covering risks associated with sea freight.
• DamageReport – a damage report that can be created if the cargo is damaged during transportation. This parameter is important for insurance claims.
• OverweightFlag – a flag indicating that the container exceeds the allowable weight. In such cases, additional penalties or restrictions may apply.
• CustomsInspectionStatus – the status of the container's inspection at customs, indicating whether the cargo has undergone inspection or has been released without examination.

2. Air Freight For air freight, the key aspects are the speed of order processing and the ability to track the cargo accurately. The system employs a flight scheduling mechanism and calculates delivery times, taking into account customs clearance and cargo transshipment at intermediate points. Variables such as FlightNumber, LoadingAirport, and CustomsClearanceStatus allow for tracking each stage of cargo movement.

3. Road Freight Road freight requires consideration of various variables, such as road conditions, weather, and international regulations. The CRM includes modules that account for these factors, enabling route planning based on real-time data on transportation infrastructure and potential delays. Variables like RouteStatus, DriverID, and RoadCondition are used to calculate delivery times and manage risks.

Fig. 2. Creation of a Road Transportation Request in the CRM system

Key variables and parameters related to road transportation:

TransportationType – Type of transportation:

Description: Specifies the type of transportation, e.g., "Road Transportation."

Usage: This parameter indicates that the shipment will be conducted via road transport.

Code example:

Parameters.ParamByName('TransportationType').Value := 'Road Transportation';

VehicleType – Type of vehicle:

Description: Describes the type of vehicle used, such as a truck, van, or dump truck.

Usage: Defines the specific type of vehicle used for transporting the cargo.

Code example:

Parameters.ParamByName('VehicleType').Value := 'Truck';

DriverName – Driver's name:

Description: Contains the name of the driver operating the vehicle.

Usage: The system records the driver’s information for tracking and monitoring the transport.

Code example:

Parameters.ParamByName('DriverName').Value := 'John Doe';

LicensePlate – Vehicle license plate:

Description: License plate number of the vehicle performing the transportation.

Usage: This variable identifies the vehicle.

Code example:

Parameters.ParamByName('LicensePlate').Value := 'A123BC';

DepartureLocation – Departure location:

Description: The place from which the road transportation starts.

Usage: Indicates the location where the cargo is loaded onto the vehicle.

Code example:

Parameters.ParamByName('DepartureLocation').Value := 'Moscow, Warehouse #5';

ArrivalLocation – Destination location:

Description: The place where the cargo will be delivered.

Usage: This variable records the final delivery point.

Code example:

Parameters.ParamByName('ArrivalLocation').Value := 'Saint Petersburg, Warehouse #12';

Route – Transportation route:

Description: Describes the route the vehicle will follow.

Usage: Specifies the path for the driver, including intermediate points.

Code example:

Parameters.ParamByName('Route').Value := 'Moscow - Tver - Saint Petersburg';

CargoType – Type of cargo:

Description: Describes the type of cargo being transported (e.g., construction materials, food products).

Usage: Important for accounting for the specific conditions required for the transport.

Code example:

Parameters.ParamByName('CargoType').Value := 'Food Products';

Weight – Cargo weight:

Description: The total weight of the cargo transported by the vehicle.

Usage: Important for calculating the load on the vehicle and route planning.

Code example:

Parameters.ParamByName('Weight').Value := 5000; // weight in kilograms

EstimatedArrivalTime – Estimated arrival time:

Description: The time when the cargo is expected to arrive at the destination.

Usage: Defines the expected arrival time to monitor deadlines.

Code example:

ascal

Копировать код

Parameters.ParamByName('EstimatedArrivalTime').Value := '2024-10-15 12:00:00';

FuelConsumption – Fuel consumption:

Description: A parameter indicating the fuel consumption during transportation.

Usage: Used for planning and calculating transportation costs.

Code example:

Parameters.ParamByName('FuelConsumption').Value := 15.5; // liters per 100 km

TransportationCost – Cost of transportation:

Description: The total cost of transportation, taking into account distance, fuel, and other expenses.

Usage: Used for calculating the total cost of the transportation service for the client.

Code example:

Parameters.ParamByName('TransportationCost').Value := 10000; // cost in rubles

DeliveryStatus – Delivery status:

Description: A variable for tracking the current delivery status, e.g., "In Transit," "Delivered," "Cancelled."

Usage: Allows tracking the current stage of the order.

Code example:

Parameters.ParamByName('DeliveryStatus').Value := 'In Transit';

PaymentStatus – Payment status:

Description: Contains information about the payment status for the delivery (e.g., "Paid," "Pending Payment").

Usage: Necessary for tracking financial transactions related to orders.

Code example:

Parameters.ParamByName('PaymentStatus').Value := 'Pending Payment';

DeliveryTime – Delivery time:

Description: Records the actual time of delivery of the cargo.

Usage: Enables tracking of the actual fulfillment time of the order.

Code example:

Parameters.ParamByName('DeliveryTime').Value := Now;

OrderID – Unique order identifier:

Description: The ID of the order associated with the road transportation.

Usage: Allows linking the road transportation to a specific order.

Code example:

Parameters.ParamByName('OrderID').Value := OrderID;

Table structures for road transportation:

Transportations – A table that records data for each transportation:

CREATE TABLE Transportations (

TransportationID UNIQUEIDENTIFIER PRIMARY KEY,

CargoUnitID UNIQUEIDENTIFIER,

TransportationType NVARCHAR(50),

VehicleType NVARCHAR(50),

DriverName NVARCHAR(100),

LicensePlate NVARCHAR(20),

Route NVARCHAR(255),

DepartureLocation NVARCHAR(255),

ArrivalLocation NVARCHAR(255),

EstimatedArrivalTime DATETIME,

FuelConsumption FLOAT,

TransportationCost DECIMAL(18, 2),

DeliveryStatus NVARCHAR(50),

PaymentStatus NVARCHAR(50)

);

Drivers – A table containing data about the drivers:

CREATE TABLE Drivers (

DriverID UNIQUEIDENTIFIER PRIMARY KEY,

Name NVARCHAR(100),

LicenseNumber NVARCHAR(50),

PhoneNumber NVARCHAR(20)

);

Vehicles – A table with information about the vehicles:

CREATE TABLE Vehicles (

VehicleID UNIQUEIDENTIFIER PRIMARY KEY,

VehicleType NVARCHAR(50),

LicensePlate NVARCHAR(20),

DriverID UNIQUEIDENTIFIER,

FuelEfficiency FLOAT,

Capacity FLOAT

);

This set of variables and parameters covers most aspects of road transportation, ranging from the description of vehicles and drivers to routes and payment data.

4. Freight Forwarding

Freight forwarding requires control at every stage of the route, especially when cargo is transferred between different types of transport. The system manages the process of transferring cargo from sea to road or air transportation, updating the cargo status in real-time. Variables such as TransferPoint, FreightForwarderID, and TransportType are used for this purpose.

Fig. 3. Creation of a Cargo Unit

Data Structure for Cargo Units

Example code describing the cargo unit entity:

CREATE TABLE CargoUnits (

CargoUnitID UNIQUEIDENTIFIER PRIMARY KEY,

CargoUnitType NVARCHAR(100), -- Type of cargo unit (e.g., container, pallet)

Weight DECIMAL(18, 2), -- Weight of the cargo unit

Volume DECIMAL(18, 2), -- Volume of the cargo unit

Dimensions NVARCHAR(100), -- Dimensions (length, width, height)

ContentDescription NVARCHAR(255), -- Description of the contents

HazardousGoods BIT, -- Indicator for hazardous materials

TemperatureControl BIT -- Whether temperature control is required

);

Interaction with Transportation Types

Each cargo unit can be associated with a specific type of transportation through the transportation table:

CREATE TABLE Transportations (

TransportationID UNIQUEIDENTIFIER PRIMARY KEY,

CargoUnitID UNIQUEIDENTIFIER, -- Reference to the cargo unit

TransportationType NVARCHAR(50), -- Type of transportation (sea, road, air, etc.)

VehicleType NVARCHAR(50), -- Type of transport vehicle

DepartureLocation NVARCHAR(255), -- Departure point

ArrivalLocation NVARCHAR(255), -- Destination point

Status NVARCHAR(50) -- Transportation status (in transit, delivered, canceled, etc.)

);

Step 1: Creating an Order

procedure TOrderService.CreateOrder(CustomerID: TGuid; OrderDescription: string);

begin

with ADODataSet do

begin

CommandText := 'INSERT INTO Orders (CustomerID, Description, OrderDate, Status) ' +

'VALUES (:CustomerID, :Description, :OrderDate, :Status)';

Parameters.ParamByName('CustomerID').Value := CustomerID;

Parameters.ParamByName('Description').Value := OrderDescription;

Parameters.ParamByName('OrderDate').Value := Now;

Parameters.ParamByName('Status').Value := 'Created';

ExecSQL;

end;

end;

This code executes the creation of an order using an SQL statement to insert data into the order table.

Step 2: Creating a Cargo Unit

procedure TCargoService.CreateCargoUnit(OrderID: TGuid; CargoDescription: string; Weight: Double; CargoType: string);

begin

with ADODataSet do

begin

CommandText := 'INSERT INTO CargoUnits (OrderID, Description, Weight, CargoType) ' +

'VALUES (:OrderID, :Description, :Weight, :CargoType)';

Parameters.ParamByName('OrderID').Value := OrderID;

Parameters.ParamByName('Description').Value := CargoDescription;

Parameters.ParamByName('Weight').Value := Weight;

Parameters.ParamByName('CargoType').Value := CargoType;

ExecSQL;

end;

end;

This code creates a new cargo unit linked to the previously created order.

Step 3: Creating a Transportation Record

procedure TTransportationService.CreateTransportation(CargoUnitID: TGuid; TransportationType, VesselName, Route: string);

begin

with ADODataSet do

begin

CommandText := 'INSERT INTO Transportations (CargoUnitID, TransportationType, VesselName, Route, DepartureDate, Status) ' +

'VALUES (:CargoUnitID, :TransportationType, :VesselName, :Route, :DepartureDate, :Status)';

Parameters.ParamByName('CargoUnitID').Value := CargoUnitID;

Parameters.ParamByName('TransportationType').Value := TransportationType;

Parameters.ParamByName('VesselName').Value := VesselName;

Parameters.ParamByName('Route').Value := Route;

Parameters.ParamByName('DepartureDate').Value := Now;

Parameters.ParamByName('Status').Value := 'In Transit';

ExecSQL;

end;

end;

This code creates a transportation record for the cargo unit, specifying the transport vehicle and route.

Step 4: Creating a Cargo Removal Request

procedure TCargoRemovalService.CreateCargoRemovalRequest(TransportationID: TGuid; RemovalPort: string; RemovalDate: TDateTime);

begin

with ADODataSet do

begin

CommandText := 'INSERT INTO CargoRemovalRequests (TransportationID, RemovalPort, RemovalDate, Status) ' +

'VALUES (:TransportationID, :RemovalPort, :RemovalDate, :Status)';

Parameters.ParamByName('TransportationID').Value := TransportationID;

Parameters.ParamByName('RemovalPort').Value := RemovalPort;

Parameters.ParamByName('RemovalDate').Value := RemovalDate;

Parameters.ParamByName('Status').Value := 'Pending';

ExecSQL;

end;

end;

This code creates a cargo removal request after the transportation is completed.

Table Structures:

Orders – Table of orders:

CREATE TABLE Orders (

OrderID UNIQUEIDENTIFIER PRIMARY KEY,

CustomerID UNIQUEIDENTIFIER,

Description NVARCHAR(255),

OrderDate DATETIME,

Status NVARCHAR(50)

);

CargoUnits – Table of cargo units:

CREATE TABLE CargoUnits (

CargoUnitID UNIQUEIDENTIFIER PRIMARY KEY,

OrderID UNIQUEIDENTIFIER,

Description NVARCHAR(255),

Weight FLOAT,

CargoType NVARCHAR(50)

);

Transportations – Table of transportations:

CREATE TABLE Transportations (

TransportationID UNIQUEIDENTIFIER PRIMARY KEY,

CargoUnitID UNIQUEIDENTIFIER,

TransportationType NVARCHAR(50),

VesselName NVARCHAR(100),

Route NVARCHAR(255),

DepartureDate DATETIME,

Status NVARCHAR(50)

);

CargoRemovalRequests – Table of cargo removal requests:

CREATE TABLE CargoRemovalRequests (

CargoRemovalRequestID UNIQUEIDENTIFIER PRIMARY KEY,

TransportationID UNIQUEIDENTIFIER,

RemovalPort NVARCHAR(100),

RemovalDate DATETIME,

Status NVARCHAR(50)

);

 This example demonstrates how to manage the process of creating orders, cargo units, transportation records, and cargo removal requests in a CRM system.

Automation of Processes and Integration with Financial Modules

Financial integration is a key aspect of a universal CRM system. The system must account for all transactions related to orders, transportation, and customs clearance, including the calculation of taxes, fees, and commissions. The code implements functions for the automatic calculation of transportation costs, including fuel expenses, driver salaries, customs fees, and potential penalties.

Example code for calculating transportation cost considering all variables:

function calculateTotalCost(order) {

let baseCost = order.distance * order.ratePerKm;

let fuelCost = baseCost * 0.1; // Considering fuel costs

let driverSalary = baseCost * 0.2;

let customsFees = calculateCustomsFees(order);

let totalCost = baseCost + fuelCost + driverSalary + customsFees;

return totalCost;

}

System Versatility

The primary feature of the system is its adaptability and universality for all types of transportation. Modules used for container tracking can also be applied to road or air transport. This is ensured through the use of a common database and microservices architecture, allowing the system to be flexibly configured for the needs of specific types of cargo.

Conclusion

The development of a universal CRM system for managing international freight operations requires accounting for various factors related to different types of transport and cargo specifications. The multi-layer architecture and modular approach allow for system integration across all modes of transportation, from sea container shipping to road and air transport. Financial integration and automated calculations make the system an effective tool for managing complex logistics operations.