Introduction
The inland reconciliation process captures all intermodal flow events as Load/Discharge moves and assigns them to relevant OTTs.
The process is different compared to Ocean OTTs, because RKEM was designed particularly with liner operations in mind and less emphasis on managing the intermodal part. As the system is currently designed, it is not possible to identify the intended destination for empty inland moves, simply by reading the RKEM data. In order to still include these moves in ROCK, a logic is required to assign the load/discharge events to OTTs. When inland discharge events are eventually received, the destination is revealed and the system can re-align itself.
This section describes the current logic in ROCK, used to reconcile moves to OTTs, including a best-bet logic when destination is unknown and a related workaround.
Unknown Inland Destination
Evident from the following picture of an empty inland equipment move is that nowhere is the destination given. The routing section is reserved for GSIS related data.
Due to the shortcomings of planned destination in intermodal RKEM moves, the equipment community designed a workaround to enable help the reconciliation process. The designed workaround requires users to update each relevant empty intermodal flow move in RKEM with a reference to the planned OTT number in ROCK. To hold this data, it is agreed to use the "Booking" field, which is normally only used on full container flow moves. This enables the reconciliation process to directly add the numbers directly to the associated OTT. This workaround requires a systematic change in the daily operations of depots/terminals, and has successfully been implemented in NEU LOC.
If the booking code is not specified, then ROCK uses a best-bet logic instead, described in the following sections.
High Level Flow Description
| Database Flow 1 | Backend Flow 1 | Database Flow 2 |
|---|---|---|
|
|
|
Database Flow 1
| Step | Description |
|---|---|
| Setup Traceability | Traceability of process is prepared. Each time Reconciliation runs, it is done on a fresh batch from CDS. This step ensures we keep data for the latest X iterations and assign to an ID. |
Set Scope of Process | The starting point for the data is based on the RKEM data. The process takes the earliest LoadDate received in the RKEM batch (approximately 6 months back). From this value, a week is added and @LimitDateFrom value starts on a monday. This effectively cuts the "tail" off the delta data from RKEM and restricts the amount of OTTs to be considered. |
Get Inland Deltas (Part 1) | The delta data is filtered to only consider meaningful RKEM data
|
Get Inland Deltas (Part 2) | The delta data where Origin or Destination is 5 characters should be fixed in RKEM, but logic will assume sitescodes so we can still include the moves. Based on historical flow patterns between pools, the logic will assign a sitecode to
|
Get Inland OTTs | All existing inland OTTs (that are not infleet/outfleet) are prepared for the process, including the container details and flow projection of OTT. |
Assign CaseTypes | Each delta is assigned to a "CaseType", which is a category that steers which data is needed to assign it correctly to an OTT.
|
Traceability v2: Take Snapshot for Traceability | As all inland deltas have now been filtered, the traceability feature takes a full snapshot of the deltas. |
Traceability v1: Mark Deltas as Processed | All deltas are marked with flag IsProcessed=1 (the process will put some of them back to 0 later) |
Aggregate Deltas | Each delta is a group of units with shared values in origin/destination/timestamps/containerstatus and more. This is already done to prevent having excessive amount of data to complete simple operations. For inland reconciliation logic we will aggregated deltas again on shared values, to further simplify the processing. Aggregation is done on: ReconciliationStatus, ContainerTypeId, OttNumberMatched, LoadSiteCode, DischargeSiteCode, LoadDate, DischargeDate, CaseType |
Traceability v2: Capture Aggregated IDs | As part of the aggregation of deltas, a new ID is "AggregatedId" emerges. This id is assigned to the traceability snapshot, to enable tracking |
Prepare Baseline CaseType: Booking Code Known | To assign deltas to an already known OTT, the baseline only needs to include the OTT, which is being referred to |
| Prepare Baseline CaseType: Best Bet | To assign deltas to an unknown destination, the baseline will need to make some assumptions based on historical patterns. The historical patterns are partly based on data from OTTs and partly based on data from a system ETS (Equipment TimeSlice). The baseline prepared by bundling all relevant destinations for the delta, and existing OTTs. As an additional step, any OTT already planned out of the origin, which is not already covered by historical patterns, is also to the baseline. |
| Prepare Baseline CaseType: Completed OTTs | To assign deltas to OTTs when origin/destination is known is more simple. All OTTs with Origin/destination pair and same load date is considered in the baseline. |
| Prepare Baseline CaseType: Loaded and Not Reconciled | When best bet logic assigns numbers to one OTT and later removes or reassigns to another, then the first OTT needs to be cleared. These OTTs are also added to the baseline, which the backend will handle. |
Backend Flow 1
| Step | Description |
|---|---|
| Assignment Logic | The assignment logic takes the baseline and processes each batch by updating existing- and creating new OTTs. First, each batch is ordered:
After ordering is done, each delta is processed individually by trying to assign the units to OTTs. The result of this step is a List of pending events to either Update or Create new OTTs. |
| Create and Update OTTs | The baseline specifies OTTs relevant to the inland delta. The relevant OTTs are selected and re-ordered by considering user-created OTTs before system-created OTTs. For each OTT, the planned amount (COALESCE(A,S,0)) is compared to the units to distribute. While iterating each OTT, units are assigned to OTTs one by one, by “filling up” the units (e.g. if 100 units to assign and one OTT has 60 planned, 60 is assigned and 40 remained). After each delta has been processed to update existing OTTs – if any volume was not assigned to any existing OTT, then new OTTs must be created. Creation is done to the most-likely destination, using the patterns from the baseline. The LoadDate is set to Monday at 12:00 and DischargeDate to Friday at 12:00 Newly created OTTs are saved to ROCK database using a batch procedure (SP: dbo.SaveBatchOTTs). Updated OTTs are saved to ROCK database using a batch procedure (SP: dbo.UpdateBatchOTTs). |
Traceability v2: Newly Created OTTs | Newly Created OTTs are specifically sent back to the database |
Database Flow 2
| Step | Description |
|---|---|
| Traceability v1: Flag Not Processed | All deltas that were not included in the latest update list of create list are captured and set to not-processed: dbo.OTTreconciliationInlandProcess_NotProcessed |
| Traceability v2: Update Baseline | Based on data available, the traceability batch can now be flagged with operations applied
|
Attachments:
image2018-11-28_15-16-11.png (image/png)
image2018-11-28_15-23-53.png (image/png)
delta.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
delta.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)