View source for FS 371338 6281 EBB CTL Bespoke Import Interface

<div class="noprint">
{{#vardefine:Client|EBB}}
{{#vardefine:ClientName|EBB Paper}}
{{#vardefine:System|''CALIDUS'' TL TMS}}
{{#vardefine:SystemCode|CTLTMS}}
{{#vardefine:Doc_Title|Bespoke Import Interface}}
{{#vardefine:Version|2.1}}
{{#vardefine:Date|18th June 2020}}
{{#vardefine:Reference|371338 6281}}
{{#vardefine:Year|2020}}
{{#vardefine: Figure | 0 }}{{ #vardefine: Example | 0 }}
<!--
Sample Xref:
{{Xref
||Type=Figure
||Num={{ #vardefineecho: Figure| {{ #expr: {{ #var: Figure}} * 1 }} }}
||Text=Data Upload - Import screen.}}
-->
</div>
{{Doc_TitleNew
|Client={{#var:ClientName}}
|System={{#var:System}}
|Title={{#var:Doc_Title}}
|Reference=FS {{#var:Reference}}
|Version={{#var:Version}}
|Date={{#var:Date}}
|Year={{#var:Year}}
}} 

<!-- TOC --> 
<div class="noprint">
= FUNCTIONAL OVERVIEW  =
== Client Requirement  ==
An orders interface is required into CTL-TMS from the ERP system.


== Solution Overview  ==
The operation will expect to execute the following movement types:
* Radial delivery.
* Customer collection.
* Customer returns.
* Trunk movements.

Customer collections will be interfaced to CTL on a separate manifest per depot, but with a driver of "WAREHOUSE". {{Note}} The "obs_view" view MSA contains a column that identifies whether this is a customer collection (column CustColl).


There are also supplier direct to customer deliveries. As these are not executed through the EBB network, they will not be interfaced into CTL.

{{Note}} A version of this interface has already been written as part of the C-ePOD integration. This is not suitable for implementation into CTL (mainly because it is primarily driven from a file sent from the MSA TMS already in place). This, however, may be used as a template as to how orders are created.


The team do not amend orders - they either add new orders or they void the order and create a new one. However, the interface will be written in such a way as to update orders if they already exist, so that re-processing a failed order will not prevent this interface from working as expected.


{{Note}} The import of orders will be designed to utilise the static data created in CTL. Certain areas, such as DU/TU (Despatch/Delivery unit or transport unit) are used to determine the vehicle fill through a percentage BUE (base unit equivalent). The process will apply the product and UOM to the product type and TU type, which will represent a proportionate vehicle fill. 


A scheduled import process will be created on the system, running at a timed interval. EBB Paper have requested that this process runs as frequently as possible. The target for this will be every 3 minutes, but this must be evaluated during implementation for efficiency of the process and may need to be changed.

On the day of implementation of the live CTL system, the existing interface in C-ePOD will be disabled, as C-ePOD will now be receiving all orders and trips from CTL through the standard C-ePOD web-service interface.


The current C-ePOD order import is triggered by the transport plan. The process then retrieves the details of only the orders on that transport plan.
As CTL will be responsible for creating the transport plan, this will require a change in how this is triggered.

Extracting data for orders will be direct from the MSA data, taken from the "obs_view" view of data.

The view currently shows 14 days of orders.

It will not be efficient to retrieve all orders from the view and check whether they have changed. As this will be happening very regularly (the import is expected to look for orders every few minutes), the process must be efficient.

In order to make in the new order import work efficiently, the MSA view must change to show:
* Voided orders.
* Creation Date/Time.

This will be prototyped by EBB paper as a new view "obs_view" within the existing database for the purpose of evaluation of the new CTL interface. Note that voided orders may instead be through a different view.


Voided orders in the interface will be handled as follows:
* The order will be moved to CANCELLED status.
* The order will be automatically unplanned from all trips, as long as none of the trips have not yet started (i.e. not status EN-ROUTE or COMPLETED).

Any order that is cancelled after it has begun being transported by the operation (i.e. the order is on any trip that is EN-ROUTE or COMPLETED status) must be manually handled. In this case, this means manually un-planning the cancelled order from any trips that are no longer required and moving the product back to the depot that will store the order's product. The raising of this stock transfer movement will be at the discretion of the operation.

{{Warning}} Voided orders removed from trips will change the product that is delivered and also loaded/unloaded by the operation on the trunk trips. It is likely that the operational documents affected by this will require reproduction by the operation. This should be achieved when the order is taken off trips by the operation.


Opening times against delivery locations are maintained in the ERP. These delivery times must be maintained against the orders in CTL.

These delivery windows will be transposed directly against the customer orders. These will be visible against the orders and may then be used when planning.


The interface will search for all orders created since the last time that the interface ran. For each order found, the interface will create all details of the order, including:
* References - the order references, expected to be the CTL order number, the external order number and the customer's reference.
* Service level - the service level will be determined from the first few characters of the order reference. If the order is specified as customer collect, this will be identified as a different service level. The service level will identify whether the order may be automatically planned (through the scheduling engine, described in following sections).
* Instructions - any order-specific instructions.
*  Locations - the origin and destination (from and to) locations. Any locations that do not already exist will be created by this interface.
**   If AddrCode is "0000", then CUSTNMBR is the location AND the customer.
**   If AddrCode is NOT "0000", the CUSTNMBR is the Customer and AddrCode is the Location.
*  Scheduling - the collection and delivery windows for the order.
*  Contacts - any contacts for the order. As already mentioned, it is unlikely that there will be any contact information available to import.
*  Requirements/Charges - any ad-hoc charges associated with the order. This will be used to generate the revenue of the order and will be taken directly from the ERP gross profit (XTNDPRCE - EXTDCOST).
*  Transport Units/Products being delivered. This process will also create TU types and product types where required. Unit weights and dims will be stored on product types and UOMs. These will be created when orders are created, then not updated from that point. Note that the sequence of the products in the MSA view will be added and retained in CTL.
*  Customer - the customer and basic rate information will be defaulted in order to generate costs for trip. The customer will be identified through the CUSTNMBR and AddrCode fields in the view and will be automatically created if they do not already exist and updated if they do (including the address):
**   If AddrCode is "0000", then CUSTNMBR is the location AND the customer.
**   If AddrCode is NOT "0000", the CUSTNMBR is the Customer and AddrCode is the Location.
*  The sales territory (order group).
*  Parameters - consisting of:
**    Salesperson.
**    Loading location.
**    Order creation date.
**    Any other non-critical cross-reference data required for the order.



The collection and delivery windows will be set based on the order type (service level).

Most orders are day 1 for day 2 (i.e. customer deliveries INV and CAL).

Some orders specify a delivery date and time outside of this region.

Returns (COMP/RTN): the operation does not normally make a specific delivery run to collect a return, but instead plans them for when they are next visiting that location. This is usually within 3 days. The operation will manually plan these orders.

All exceptions will be handled and planned manually.

Stock Transfers (ISTIN) will be excluded from automatic scheduling and will be planned manually by the operation.

Customer (desk) collections will be excluded from automatic scheduling and will be planned manually by the operation.


The process will audit the success or failure of these order interfaces through the EDI Log screen, with a line for each order imported, a status (success or failure) and a showing the reason for any failure in detail. 

{{Note}} Voided orders will be audited through the EDI log, in that the description of the process will clearly state that this order has been voided.

Given that the interface is based on the creation date of the orders, if an order should fail, there will be no facility to get the order again. Therefore this auditing process must check the interface type that generated the message (in this case, then bespoke EBB orders interface) and allow for re-processing of the message. The interface should check for any messages marked to be reprocessed in the audit log and get the details of this order again in the next pass. The original audit record will be updated as reprocessed and a new audit record created for the new import. 

{{Note}} Failed orders will ''not'' automatically re-import - it is up to the CTL administrators to look for failures and mark them to be reprocessed, as this may require modifications to be made to the order in the ERP or the static data in CTL to allow the order to be successfully processed.


== Scope  ==
This change will be applied to system version 1.00 on test database EBBCTST) and once approved EPPCPRD.


== Impact ==
This change will be impacted by the following system functionality changes:
* 6231 - Service Levels ColDel offsets.
* 6230 - Order Charges.
* 6234 - EDI Log.


<!-- NEW PAGE --> 
= CONFIGURATION SET-UP  =
== Pre-requisites  ==
<!-- Think about dependent functionality or configuration that is required to support this deliverable and describe here. -->


== Menu Structure  ==
<!-- Describe how the menu system will change to accommodate this requirement if relevant. -->


== Data  ==
<!-- Document any configuration data set up necessary to enable or control the solution e.g. the new report will be added to the standing data to allow it to be selected from the standard reports form. The selection criteria for the report will be (insert selection criteria). -->


== Implementation Advice ==
<!-- Include any relevant notes that will help OBSL or Client implementer to successfully enable the solution on delivery. -->


<!-- NEW PAGE --> 
= FUNCTIONAL DESCRIPTION =
== Screen Changes ==
=== Transport Unit Types ===

A change must be made to the screen to allow maintenance of new parameters for transport unit types, in the same way that it does for other parameter screens. 

The new parameter type maintenance screen should be added to the menus.


=== Product Types ===

A new field PPT_DESCRIPTION2 should be added to the table, and added to the Product Type Maintenance screen.


=== Order Lines ===

New fields must be added to the order lines when creating or editing them in the Order Entry screen.
* OOL_PACK_QUANTITY
* OOL_CUST_ORD_REF
* OOL_DESCRIPTION2


== EDI Process ==

Order messages reprocessing:
* Retrieve data from local EDI log for order messages to be reprocessed.
* For each entry:
**  Get details of the order from the MSA view "obs_view".
**  Create or update standing data:
***    Locations.
****      Opening times.
***    Customer.
***    Drivers (N/A).
***    Vehicles (N/A).
***    DU Types.
***    Product Types.
***    Order Group (sales territory).
**  Create or update existing order in CTL.
***    Order Charges.
***    Parameters (Salesperson, Order Creation Date/Time, etc).
* Mark original EDI Log entry as re-processed.
* Assess success of update or creation of order and write an EDI log entry with an appropriate status and description. 

Order Messages Initial Creation:
* Retrieve data from MSA View "obs_view"for new orders since last run date/time, filtering against CREATDDT and TIME1 (indicating the date/time created). Note that the view will have multiple records per order if the order has multiple product lines. Note that the sequence of the products in the MSA view will be added and retained in CTL:
* For each order in MSA view "obs_view":
**  Create or update standing data:
***    Locations.
****      Opening times.
***    Customer.
***    Drivers (N/A).
***    Vehicles (N/A).
***    DU Types.
***    Product Types.
***    Order Group (sales territory).
**  Create or update existing order in CTL.
***    Order Charges.
***    Parameters (Salesperson, Order Creation Date/Time, etc).
* Assess success of update or creation of order and write an EDI log entry with an appropriate status and description. 

Voided Order Messages Reprocessing:
* Retrieve data from local EDI log for voided order messages to be reprocessed.
* For each entry:
**  Order set to CANCELLED status.
**  Order automatically unplanned from all non-started trips.
* Mark original EDI Log entry as re-processed.
* Assess success of cancellation of order and write an EDI log entry with an appropriate status and description. 

Voided Order Messages Initial Creation:
* Retrieve data from MSA View "obs_view"for voided orders that have been cancelled since the last run. In this case, this should select records using columns VOIDSTTS (indicating voided orders).
* For each order in MSA view "obs_view":
**  If the order is not cancelled, 
***    Set the order to CANCELLED status.
***    Order automatically unplanned from all non-started trips.
* Assess success of cancellation of order and write an EDI log entry with an appropriate status and description. 


== EDI Process Parameters ==

All parameters keyed to the Bespoke EBB Import EDI process.
* MSA Database parameters (where, log in, etc).
* Schedule/Interval settings.
* Parameters:
**  Allow Reprocessing - whether this message process supports re-processing of failed EDI messages from the EDI Log screen.


== Reference ==

MSA view "obs_view".

Data Types:

Obtained through following query:

 SELECT COLUMN_NAME, DATA_TYPE 
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE 
     TABLE_NAME = 'OBS_View'

{| class="wikitable" border="1"
|-bgcolor="silver"
!Column Name	!! Data Type
|-
|SOPNUMBE	|| char
|-
|SOPTYPE	|| smallint
|-
|DOCDATE	|| datetime
|-
|CREATDDT	|| datetime
|-
|minusday	|| datetime
|-
|Status	|| smallint
|-
|ReqShipDate	|| datetime
|-
|DelTime	|| varchar
|-
|ITEMNMBR	|| char
|-
|ITEMDESC	|| char
|-
|UserID	|| char
|-
|LOCNCODE	|| char
|-
|CUSTNMBR	|| char
|-
|CUSTNAME	|| char
|-
|LineComm	|| text
|-
|OrdComm	|| text
|-
|QUANTITY	|| numeric
|-
|CustColl	|| char
|-
|CustOrdNo	|| char
|-
|UOFM	|| char
|-
|ITEMSHWT	|| numeric
|-
|Size1	|| char
|-
|Size2	|| char
|-
|GSM	|| char
|-
|ebbFLTX_Sales_Comments	|| text
|-
|tcsFLST_Route	|| char
|-
|XTNDPRCE	|| numeric
|-
|EXTDCOST	|| numeric
|-
|DelIns	|| char
|-
|DelName	|| varchar
|-
|DelAdd1	|| char
|-
|DelAdd2	|| char
|-
|DelAdd3	|| char
|-
|DelCity	|| char
|-
|DelState	|| char
|-
|DelZip	|| char
|-
|InvAdd1	|| char
|-
|InvAdd2	|| char
|-
|InvAdd3	|| char
|-
|InvCountry	|| char
|-
|InvCity	|| char
|-
|InvState	|| char
|-
|InvZip	|| char
|-
|CredRate	|| char
|-
|SALSTERR	|| char
|-
|AddrCode	|| char
|-
|DelSiteID	|| char
|-
|LineNumber	|| int
|-
|SellPack	|| char
|-
|StartTime	|| datetime
|-
|Trailer	|| varchar
|-
|FinishTime	|| datetime
|-
|SignatureRequired	|| varchar
|-
|Transit	|| varchar
|-
|TailLift	|| varchar
|-
|HGV	|| varchar
|-
|ORD_COMMID	|| char
|-
|BinNo	|| varchar
|-
|NSflag	|| varchar
|-
|RTNON	|| char
|-
|IDTNo	|| varchar
|-
|VOIDSTTS	|| int
|-
|TIME1	|| datetime
|-
|MIC	|| char
|}


Data columns, per order and product:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| SOPNUMBE|| Customer's reference	 
|-
| SOPTYPE|| 	N/A 
|-
| DOCDATE|| 	N/A 
|-
| CREATDDT|| Store as created date of the order or store as order reference "Created Date"	 
|-
| minusday|| N/A	 
|-
| Status|| N/A	 
|-
| ReqShipDate|| This is the required delivery date, which will be used to set the collection and delivery windows	 
|-
| DelTime|| If provided, forms the delivery time. Also sets the service level to "TIMED".	 
|-
| ITEMNMBR|| Product Type	 
|-
| ITEMDESC|| Product Type Description/Line Description	 
|-
| UserID|| Store as order reference "Sales Contact"	 
|-
| LOCNCODE|| from location of the order	 
|-
| CUSTNMBR|| #1 Customer Code or loc to 	 
|-
| CUSTNAME|| Name of customer or location	 
|-
| LineComm|| #7 Line specific comments. 	 
|-
| OrdComm|| #4 instructions of the order 	 
|-
| QUANTITY|| The quantity	 
|-
| CustColl|| #6 Service level	 
|-
| CustOrdNo|| Customers order number (Delivery Ref) and against the order line.	 
|-
| UOFM|| TU Type	 
|-
| ITEMSHWT|| Line weight	 
|-
| Size1|| N/A	 
|-
| Size2|| N/A	 
|-
| GSM|| N/A	 
|-
| ebbFLTX_Sales_Comments|| #2 new descriptive field on order line	 
|-
| tcsFLST_Route|| Store as new order reference "Route"	 
|-
| XTNDPRCE|| #3 Order revenue (XTNDPRCE - EXTDCOST)	 
|-
| EXTDCOST|| #3 Order revenue (XTNDPRCE - EXTDCOST)	 
|-
| DelIns|| #4 instructions of the order	 
|-
| DelName|| loc to	 
|-
| DelAdd1|| loc to	 
|-
| DelAdd2|| loc to	 
|-
| DelAdd3|| loc to	 
|-
| DelCity|| loc to	 
|-
| DelState|| loc to	 
|-
| DelZip|| loc to	 
|-
| InvAdd1|| customer address	 
|-
| InvAdd2|| customer address	 
|-
| InvAdd3|| customer address	 
|-
| InvCountry|| customer address	 
|-
| InvCity|| 	customer address 
|-
| InvState|| customer address	 
|-
| InvZip|| customer address	 
|-
| CredRate|| N/A	 
|-
| SALSTERR|| Order Group	 
|-
| AddrCode|| #1	 
|-
| DelSiteID|| Delivering depot. N/A.
|-
| LineNumber|| N/A	 
|-
| SellPack|| #5 Must be stored against the product line and sent to EPOD.	 
|-
| StartTime|| N/A	 
|-
| Trailer|| N/A	 
|-
| FinishTime|| N/A	 
|-
| SignatureRequired|| N/A	 
|-
| Transit|| N/A	 
|-
| TailLift|| N/A	 
|-
| HGV|| N/A	 
|-
| ORD_COMMID|| Stored as a parameter and displayed per order on driver manifest.	 
|-
| BinNo|| Stored in a new field against order line	 
|-
| NSflag|| Stored in a new field against order line	 
|-
| RTNON|| N/A	 
|-
| IDTNo|| Only for ISTIN orders. Stored in the booking reference for the order.	 
|-
| VOIDSTTS|| Used to determine whether this is a voided order	 
|-
| TIME1|| The creation date/time of the order, down to a second.	 
|-
| MIC|| N/A	 
|}
Notes:
* #1 
**  If AddrCode is "0000", then CUSTNMBR is the location AND the customer.
**  If AddrCode is NOT "0000", the CUSTNMBR is the Customer and AddrCode
* #2 new descriptive field against line.
* #3 Order revenue: (XTNDPRCE - EXTDCOST), stored as a new order charge, as per specification 6230 - Order Charges.
* #4 instructions of the order - OrdComm combined with DelIns.
* #5 Must be stored against the product line and sent to EPOD.
* #6 If CustColl is "Y", explicitly set the service level to "COLL", otherwise use the first 3 characters of field "SOPNUMBE".
* #7 Stored in a new field against order line.

<!-- NEW PAGE --> 
= TECHNICAL NOTES =

== Screen Changes ==
=== Transport Unit Types ===

A change must be made to the screen to allow maintenance of new parameters for transport unit types, in the same way that it does for other parameter screens. 

The new parameter type maintenance screen should be added to the menus.


=== Product Types ===

A new field PPT_DESCRIPTION2 should be added to the table, and added to the Product Type Maintenance screen.


=== Order Lines ===

New fields must be added to the order lines when creating or editing them in the Order Entry screen.
* OOL_PACK_QUANTITY
* OOL_CUST_ORD_REF
* OOL_DESCRIPTION2
* OOL_DESCRIPTION3

{{Note}} Description 3 need not be added to the order entry screen.


== EDI Process Description ==

=== Order Creation ===

This section covers only the main order files - all other files will be created as required.

Table ORD_ORDER_HEADER:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| OOH_TMS_REF	|| generated
|-
| OOH_ORGANISATION_CODE	|| Fixed to "EBB"
|-
| OOH_CUSTOMER_CODE	|| From AddrCode or CUSTNMBR 
|-
| OOH_GROUP_CODE	|| SALSTERR 
|-
| OOH_SERVICE_LEVEL_CODE	|| From CustColl or first 3 characters of SOPNUMBE
|-
| OOH_STATUS	|| 100
|-
| OOH_OWNER_LOCATION_CODE	|| Derived as normal
|-
| OOH_COLLECT_LOCATION_CODE	|| Originating Depot LOCNCODE
|-
| OOH_DELIVER_LOCATION_CODE	|| From AddrCode or CUSTNMBR
|-
| OOH_SPECIAL_INSTRUCTIONS	|| OrdComm * DelIns 
|-
| OOH_ORDER_COMMENTS	|| N/A 
|-
| OOH_COLLECT_INSTRUCTIONS	|| N/A 
|-
| OOH_DELIVER_INSTRUCTIONS	|| OrdComm * DelIns 
|-
| OOH_COLLECT_DATETIME_FROM	|| Generated from service level and StartTime, DelTime
|-
| OOH_COLLECT_DATETIME_TO	|| Generated from service level and StartTime, DelTime 
|-
| OOH_DELIVER_DATETIME_FROM	|| Generated from service level and StartTime, DelTime 
|-
| OOH_DELIVER_DATETIME_TO	|| Generated from service level and StartTime, DelTime 
|-
| OOH_TEMPERATURE_TYPE_CODE	|| Fixed to "AMBIENT" 
|-
| OOH_VEHICLE_TYPE_CODE	|| N/A 
|-
| OOH_SPECIFIC_CARRIER_CODE	|| Derived as normal 
|-
| OOH_TAILLIFT_IND	|| 0
|-
| OOH_ACTIVE_IND	|| 1
|-
| OOH_CREATED_BY	|| EDI_PROCESS user
|-
| OOH_CREATED_DATE	|| Derived as normal 
|-
| OOH_LAST_UPDATED_BY	|| EDI_PROCESS user
|-
| OOH_LAST_UPDATED_DATE	|| Derived as normal 
|-
| OOH_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user
|-
| OOH_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| OOH_LAST_PROCESS_ID	|| Derived as normal
|-
| OOH_UPDATE_COUNTER	|| Derived as normal
|-
| OOH_ORDER_PLACED_DATE	|| CREATDDT
|-
| OOH_CUSTOMER_REF	|| SOPNUMBE
|-
| OOH_BOOKING_REF	|| IDTNO 
|-
| OOH_TEMPLATE_IND	|| 0
|-
| OOH_DELIVERY_REF	|| CustordNo
|-
| OOH_EXECUTION_STATUS	|| 0
|-
| OOH_DISTANCE	|| 0
|-
| OOH_DRIVE_TIME	|| 0
|-
| OOH_SHIPMENT_REF	||  
|-
| OOH_CONSOLIDATED_IND	|| Derived as normal - 0
|-
| OOH_SHIPMENT_IND	|| 0
|-
| OOH_COLLECT_NAME	|| From from loc location
|-
| OOH_COLLECT_ADDRESS_LINE1	|| From from loc location
|-
| OOH_COLLECT_ADDRESS_LINE2	|| From from loc location
|-
| OOH_COLLECT_ADDRESS_LINE3	|| From from loc location 
|-
| OOH_DELIVER_NAME	|| DelName
|-
| OOH_DELIVER_ADDRESS_LINE1	|| DelAdd1
|-
| OOH_DELIVER_ADDRESS_LINE2	|| DelAdd2
|-
| OOH_DELIVER_ADDRESS_LINE3	|| DelAdd3 
|-
| OOH_LOAD_REF	||  
|-
| OOH_LOAD_IND	|| 0
|-
| OOH_SHIPMENT_SEQUENCE	|| 0
|-
| OOH_TRIP_REF	||  
|-
| OOH_LOAD_VEHICLE_CODE	||  
|-
| OOH_COLLECT_STOP_ID	|| 0
|-
| OOH_DELIVER_STOP_ID	|| 0
|-
| OOH_COLLECT_TOWN	|| From from loc location
|-
| OOH_COLLECT_COUNTRY_CODE	|| From from loc location
|-
| OOH_DELIVER_TOWN	|| DelCity	DelState
|-
| OOH_DELIVER_COUNTRY_CODE	|| From to loc location
|-
| OOH_LOAD_SETOFF_DATETIME	|| Derived as normal 
|-
| OOH_LOAD_SHIPMENT_MINI	|| 0
|-
| OOH_LOAD_STOP_MINI	|| 0
|-
| OOH_SUPPLIER_CODE	||  
|-
| OOH_CONTACT_ATTEMPTS	|| 0
|-
| OOH_POD_IND	|| 0
|-
| OOH_POC_IND	|| 0
|-
| OOH_ACTUAL_COL_DATETIME_FROM	|| Derived as normal
|-
| OOH_ACTUAL_COL_DATETIME_TO	|| Derived as normal
|-
| OOH_ACTUAL_DEL_DATETIME_FROM	|| Derived as normal
|-
| OOH_ACTUAL_DEL_DATETIME_TO	|| Derived as normal
|-
| OOH_COL_BOOKING_REF	||  
|-
| OOH_OWNING_DEPOT_CODE	|| Derived as normal
|-
| OOH_PLANNED_TO_DEPOT_CODE	||  
|-
| OOH_AT_OWNING_DEPOT_IND	|| Derived as normal
|-
| OOH_POC_EXCEPTION_REASON	||  
|-
| OOH_POC_EXCEPTION_COMMENT	||  
|-
| OOH_POD_EXCEPTION_REASON	||  
|-
| OOH_POD_EXCEPTION_COMMENT	||  
|-
| OOH_POC_FULFILLED_IND	|| 0
|-
| OOH_POC_DEBRIEFED_IND	|| 0
|-
| OOH_POD_FULFILLED_IND	|| 0
|-
| OOH_POD_DEBRIEFED_IND	|| 0 
|-
| ''OOH_EXC_SCHED_ENG_IND''	|| A new field, defaulting to 0. 
|}

Notes:
* Service level - If field "CustColl" is "Y", explicitly set the service level to "COLL", otherwise "TIMED" if the DelTime is set, otherwise use the first 3 characters of field "SOPNUMBE", as follows:
**  RTN - Customer return.
**  INV - Customer orders.
**  CAL - Call-off.
**  IST - Stock transfer.
**  COM - Customer complaint return.
**  COLL - Customer collection (included here for reference).
**  TIMED - Orders with a specified DelTime (included here for reference).
* If service level doesn't exist, error.
* OOH_DELIVER_LOCATION_CODE and OOH_CUSTOMER_CODE:
**  If AddrCode is "0000", then CUSTNMBR is the location AND the customer.
**  If AddrCode is NOT "0000", the CUSTNMBR is the Customer and AddrCode is the location.
* Collection/Delivery Windows - this is achieved as shown in the Service Levels Col/Del Offsets specification, linked to task 6231. The exception is when the del time import field is set. In this case, the delivery from and to time should be set to the delivery time (i.e. a fixed time).
* New field OOH_EXC_SCHED_ENG_IND will be set based on the service level and the scheduling rules. This is achieved as shown in the Service Levels Col/Del Offsets specification, linked to task 6231.
* Order status - the order should be defaulted to unscheduled. However, determining the collection/delivery windows through the service level and schedule rules may result in the order being invalid, and would be set to this status by that process. This is specified in the Service Levels Col/Del Offsets specification, linked to task 6231.


File ORD_ORDER_LINE:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| OOL_TMS_REF || From the order header, as now 
|-
| OOL_LINE_NO || Generated as now. See #2.
|-
| OOL_PRODUCT_TYPE_CODE || ITEMNMBR 
|-
| OOL_TRANSPORT_UNIT_CODE || Default from product type 
|-
| OOL_QUANTITY || #1 
|-
| OOL_WEIGHT || OOL_QUANTITY x ITEMSHWT 
|-
| OOL_VOLUME || QUANTITY multiplied by volume from TU type 
|-
| OOL_HAZARDOUS_IND || 0 
|-
| OOL_BASE_UNIT_EQUIVALENT || Calculated from TU Type, QUANTITY x BUE 
|-
| OOL_ACTIVE_IND || 1 
|-
| OOL_CREATED_BY || EDI_PROCESS user 
|-
| OOL_CREATED_DATE || As now 
|-
| OOL_LAST_UPDATED_BY || EDI_PROCESS user 
|-
| OOL_LAST_UPDATED_DATE || As now 
|-
| OOL_LAST_ACTIVE_CHANGE_BY || EDI_PROCESS user 
|-
| OOL_LAST_ACTIVE_CHANGE_DATE || As now 
|-
| OOL_LAST_PROCESS_ID || As now 
|-
| OOL_UPDATE_COUNTER || As now 
|-
| OOL_WIDTH || As now 
|-
| OOL_HEIGHT || As now 
|-
| OOL_LENGTH || As now 
|-
| OOL_STACKABLE_IND || As now 
|-
| OOL_SHIPMENT_REF || As now 
|-
| OOL_DESCRIPTION || ITEMDESC 
|-
| OOL_QUANTITY_DELIVERED || As now 
|-
| OOL_ACTUAL_DESPATCHED_QTY || As now 
|-
| OOL_WEIGHT_DELIVERED || As now 
|-
| OOL_VOLUME_DELIVERED || As now 
|-
| OOL_BUE_DELIVERED || As now 
|-
| OOL_COLLECT_EXCEPTION_REASON || As now 
|-
| OOL_COLLECT_EXCEPTION_COMMENT || As now 
|-
| OOL_DELIVER_EXCEPTION_REASON || As now 
|-
| OOL_DELIVER_EXCEPTION_COMMENT || As now 
|-
| OOL_COLLECT_FULFILLED_IND || 0 
|-
| OOL_COLLECT_DEBRIEFED_IND || 0 
|-
| OOL_DELIVER_FULFILLED_IND || 0 
|-
| OOL_DELIVER_DEBRIEFED_IND || 0 
|-
| OOL_QUANTITY_COLLECTED || As now 
|-
| OOL_WEIGHT_COLLECTED || As now 
|-
| OOL_VOLUME_COLLECTED || As now 
|-
| OOL_BUE_COLLECTED || As now 
|-
| ''OOL_PACK_QUANTITY'' || New field OOL_PACK_QUANTITY required for pack size, to be sourced from SellPack. 
|-
| ''OOL_CUST_ORD_REF'' || New field OOL_CUST_ORD_REF required to hold the line-level customer order reference. 
|-
| ''OOL_DESCRIPTION2'' || New field required to hold the BinBay and NS Flag concatenated with a space. If the BinBay is not specified, the space is not required, so the result of the concatenation should be trimmed. 
|-
| ''OOL_DESCRIPTION3'' || New field required to hold the line comments. 
|}

Notes:
* #1 OOL_QUANTITY - the value in import field QUANTITY must be actioned by the conversion routines against the TU type associated to the product type. 
**  If the CONV_CODE parameter is set, change the DU type to this parameter value, then continue with the following rules.
**  If the CONV_TYPE parameter is "Multiply", multiply import field QUANTITY by the CONV_VALUE parameter value.
**  If the CONV_TYPE parameter is "Round", round the import field QUANTITY.
**  If the CONV_TYPE parameter is "Exclude", do not import the product line.
**  If the CONV_TYPE parameter is "As Received", put the value in unchanged.
* #2 The order lines will be added in the sequence that they are retrieved. The value of the Line Number from GP will not be stored, new line numbers will be generated per line added in a contiguous sequence.

File ADM_PARAMETER_VALUE:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| APV_ID	|| From ADM_PARAMETER_MASTER. 
|-
| APV_PARAM_CODE	|| "SALESPERSON" 
|-
| APV_ASSOCIATION	|| "ORD_ORDER_HEADER_VIEW" 
|-
| APV_KEY	|| from ORD_ORDER_HEADER.OOH_TMS_REF. 
|-
| APV_VALUE	|| from "UserID" 
|-
| APV_ACTIVE_IND	|| 1 
|-
| APV_CREATED_BY	|| EDI_PROCESS user 
|-
| APV_CREATED_DATE	|| Derived as normal 
|-
| APV_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| APV_LAST_UPDATED_DATE	|| Derived as normal 
|-
| APV_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| APV_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| APV_LAST_PROCESS_ID	|| Derived as normal 
|-
| APV_UPDATE_COUNTER	|| 1 
|-
| APV_COMPANY_CODE	 || N/A 
|}

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| APV_ID	|| From ADM_PARAMETER_MASTER. 
|-
| APV_PARAM_CODE	|| "ROUTE" 
|-
| APV_ASSOCIATION	|| "ORD_ORDER_HEADER_VIEW" 
|-
| APV_KEY	|| from ORD_ORDER_HEADER.OOH_TMS_REF. 
|-
| APV_VALUE	|| from "tcsFLST_Route" 
|-
| APV_ACTIVE_IND	|| 1 
|-
| APV_CREATED_BY	|| EDI_PROCESS user 
|-
| APV_CREATED_DATE	|| Derived as normal 
|-
| APV_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| APV_LAST_UPDATED_DATE	|| Derived as normal 
|-
| APV_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| APV_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| APV_LAST_PROCESS_ID	|| Derived as normal 
|-
| APV_UPDATE_COUNTER	|| 1 
|-
| APV_COMPANY_CODE	 || N/A 
|}

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| APV_ID	|| From ADM_PARAMETER_MASTER. 
|-
| APV_PARAM_CODE	|| "ORD_COMMID" 
|-
| APV_ASSOCIATION	|| "ORD_ORDER_HEADER_VIEW" 
|-
| APV_KEY	|| from ORD_ORDER_HEADER.OOH_TMS_REF. 
|-
| APV_VALUE	|| from "ORD_COMMID" 
|-
| APV_ACTIVE_IND	|| 1 
|-
| APV_CREATED_BY	|| EDI_PROCESS user 
|-
| APV_CREATED_DATE	|| Derived as normal 
|-
| APV_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| APV_LAST_UPDATED_DATE	|| Derived as normal 
|-
| APV_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| APV_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| APV_LAST_PROCESS_ID	|| Derived as normal 
|-
| APV_UPDATE_COUNTER	|| 1 
|-
| APV_COMPANY_CODE	 || N/A 
|}

Notes:
* Create ADM_PARAMETER_MASTER if it does not exist.


File GEO_LOCATION:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| GLO_CODE	|| See notes. 
|-
| GLO_ORGANISATION_CODE	|| "EBB" 
|-
| GLO_TYPE_CODE	|| "CUSTOMER" 
|-
| GLO_NAME	|| DelName 
|-
| GLO_ALTERNATE_NAME	 || N/A 
|-
| GLO_ADDRESS_LINE1	|| DelAdd1 
|-
| GLO_ADDRESS_LINE2	|| DelAdd2 
|-
| GLO_ADDRESS_LINE3	|| DelAdd3 
|-
| GLO_TOWN	|| DelCity 
|-
| GLO_COUNTY	|| DelState 
|-
| GLO_COUNTRY_CODE	|| Fixed "UK" 
|-
| GLO_POSTCODE	|| DelZip 
|-
| GLO_POSTAL_REGION	|| Derived as normal 
|-
| GLO_LATITUDE	|| See notes 
|-
| GLO_LONGITUDE	|| See notes 
|-
| GLO_LOADING_RATE_CODE	|| Fixed "GENERAL LOAD" 
|-
| GLO_UNLOADING_RATE_CODE	|| Fixed "GENERAL UNLOAD" 
|-
| GLO_DEFAULT_COL_LOC_CODE	|| N/A  
|-
| GLO_DEFAULT_DEL_LOC_CODE	|| N/A  
|-
| GLO_ZONE_CODE	|| Fixed "UK" 
|-
| GLO_TIMEZONE_CODE	|| Fixed "GMT" 
|-
| GLO_LINK_CODE	 || N/A 
|-
| GLO_ACTIVE_IND	|| 1 
|-
| GLO_CREATED_BY	|| EDI_PROCESS user 
|-
| GLO_CREATED_DATE	|| Derived as normal 
|-
| GLO_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| GLO_LAST_UPDATED_DATE	|| Derived as normal 
|-
| GLO_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| GLO_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| GLO_LAST_PROCESS_ID	|| Derived as normal 
|-
| GLO_UPDATE_COUNTER	|| Derived as normal 
|-
| GLO_DATA_FORMAT	|| ""  
|}

Notes:
* One for the customer location and the del address. 
**  Always check and create or update address for CUSTNMBR.
**  If AddrCode is NOT "0000", check and create or update address for AddrCode.
* From loc should always be one of the depots and should always be present. If not, error.
* Locations when created should be immediately geocoded.
* Do not fail if GLO_TYPE_CODE "CUSTOMER" does not exist.
* Do not fail if GLO_LOADING_RATE_CODE "GENERAL LOAD" does not exist.
* Do not fail if GLO_UNLOADING_RATE_CODE "GENERAL UNLOAD" does not exist.
* Do not fail if GLO_ZONE_CODE "UK" does not exist.
* Do not fail if GLO_TIMEZONE_CODE "GMT" does not exist.
* Do not fail if GLO_COUNTRY_CODE "UK" does not exist.


File BDM_GROUP:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| BDG_CODE	|| SALSTERR 
|-
| BDG_NAME	|| SALSTERR 
|-
| BDG_ASSOCIATION	|| Fixed "ORD_ORDER_HEADER" 
|-
| BDG_ACTIVE_IND	|| 1 
|-
| BDG_CREATED_BY	|| EDI_PROCESS user 
|-
| BDG_CREATED_DATE	|| Derived as normal 
|-
| BDG_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| BDG_LAST_UPDATED_DATE	|| Derived as normal 
|-
| BDG_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| BDG_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| BDG_LAST_PROCESS_ID	|| Derived as normal 
|-
| BDG_UPDATE_COUNTER	|| Derived as normal 
|}

Notes:
* Create the record if it doesn't exist.


File ACC_PAYMENT:
* See specification for 6230 - Order Charges.
* Write the value as Order revenue (XTNDPRCE - EXTDCOST).


File RES_TRANSPORT_UNIT:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| RTU_CODE	|| From UOFM 
|-
| RTU_DESCRIPTION	|| from UOFM 
|-
| RTU_BASE_UNIT_EQUIVALENT	|| 0.00015625 
|-
| RTU_VOLUME	|| 0.00015625 
|-
| RTU_VOLUME_COLLAPSED	|| 0 
|-
| RTU_WEIGHT	|| 0.0075 
|-
| RTU_WIDTH	|| 0.15 
|-
| RTU_LENGTH	|| 0.2 
|-
| RTU_HEIGHT	|| 0.0001 
|-
| RTU_VOLUME_RECALC_IND	|| 1 
|-
| RTU_WEIGHT_RECALC_IND	|| 1 
|-
| RTU_FOOTPRINT_RECALC_IND	|| 0 
|-
| RTU_ALLOW_DECIMALS_IND	|| 1 
|-
| RTU_CATEGORY_CODE	|| Fixed "PAPER" 
|-
| RTU_ASSET_TAGGED_IND	|| 0 
|-
| RTU_STACKABLE_UNIT_HEIGHT	|| 5000 
|-
| RTU_ACTIVE_IND	|| 1 
|-
| RTU_CREATED_BY	|| EDI_PROCESS user 
|-
| RTU_CREATED_DATE	|| Derived as normal 
|-
| RTU_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| RTU_LAST_UPDATED_DATE	|| Derived as normal 
|-
| RTU_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| RTU_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| RTU_LAST_PROCESS_ID	|| DataImport 
|-
| RTU_UPDATE_COUNTER	|| Derived as normal 
|}

Notes:
* Create the record if it doesn't exist. Do not update the record.
* Add new parameters to hold:
**  CONV_TYPE - values "Multiply", "Round", "Exclude", "As Received", default "Multiply".
**  CONV_VALUE - numeric, default 1000.
**  CONV_CODE - lookup on RES_TRANSPORT_UNIT, default "".


File PRD_PRODUCT_TYPE:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| PPT_CODE	|| from ITEMNMBR 
|-
| PPT_DESCRIPTION	|| From ITEMDESC 
|-
| PPT_TEMPERATURE_TYPE_CODE	|| Fixed "AMBIENT" 
|-
| PPT_HAZARDOUS_TEXT	 || N/A 
|-
| PPT_DEF_TRANSPORT_UNIT_CODE	|| From UOFM 
|-
| PPT_PERISHABLE_IND	|| 0 
|-
| PPT_TAINT_RISK_IND	|| 0 
|-
| PPT_ACTIVE_IND	|| 1 
|-
| PPT_CREATED_BY	|| EDI_PROCESS user 
|-
| PPT_CREATED_DATE	|| Derived as normal 
|-
| PPT_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| PPT_LAST_UPDATED_DATE	|| Derived as normal 
|-
| PPT_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| PPT_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| PPT_LAST_PROCESS_ID	|| Derived as normal 
|-
| PPT_UPDATE_COUNTER	|| Derived as normal 
|-
| PPT_GROUP_CODE	|| Fixed "PAPER" 
|-
| ''PPT_DESCRIPTION2'' || New field. Populated with ebbFLTX_Sales_Comments 
|}

Notes:
* Do not update this record once it has been initially created.
* Add column PPT_DESCRIPTION2 to hold value in column ebbFLTX_Sales_Comments.


File ORG_CUSTOMER:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| OCU_CODE	|| From CUSTNMBR 
|-
| OCU_NAME	|| From CUSTNMBR 
|-
| OCU_GROUP_CODE	|| "EBB" 
|-
| OCU_ORGANISATION_CODE	|| "EBB" 
|-
| OCU_VAT_COUNTRY_CODE	|| "UK" 
|-
| OCU_VAT_REG_NO	|| 11111111 
|-
| OCU_COMPANY_NUMBER	|| 11111111 
|-
| OCU_ACTIVE_IND	|| 1 
|-
| OCU_CREATED_BY	|| EDI_PROCESS user 
|-
| OCU_CREATED_DATE	|| Derived as normal 
|-
| OCU_LAST_UPDATED_BY	|| EDI_PROCESS user 
|-
| OCU_LAST_UPDATED_DATE	|| Derived as normal 
|-
| OCU_LAST_ACTIVE_CHANGE_BY	|| EDI_PROCESS user 
|-
| OCU_LAST_ACTIVE_CHANGE_DATE	|| Derived as normal 
|-
| OCU_LAST_PROCESS_ID	|| Derived as normal 
|-
| OCU_UPDATE_COUNTER	|| Derived as normal 
|-
| OCU_ACCOUNTING_CODE	|| 11111111 
|-
| OCU_VAT_CODE	|| "01" 
|-
| OCU_CURRENCY_CODE	|| "GBP" 
|-
| OCU_INVOICE_FREQUENCY	|| "M" 
|-
| OCU_PAYMENT_TERM_DAYS	|| 31 
|-
| OCU_TYPE	|| "CUSTOMER" 
|-
| OCU_CARRIER_CODE	||  
|}

Notes:
* From loc should always be one of the depots and should always be present. If not, error.
* Do not update this record once it has been initially created.
* Do not fail if OCU_GROUP_CODE "EBB" does not exist.
* Do not fail if OCU_ORGANISATION_CODE "EBB" does not exist.
* Do not fail if OCU_VAT_COUNTRY_CODE "UK" does not exist.
* Do not fail if OCU_VAT_CODE "01" does not exist.
* Do not fail if OCU_CURRENCY_CODE "GBP" does not exist.
* Do not fail if OCU_TYPE "CUSTOMER" does not exist.


=== Order Cancellation ===
* For each order in MSA view "obs_view":
**  Order set to CANCELLED status.
**  Order automatically unplanned from all non-started trips.


== EPOD Interface ==

=== CTMS-EPOD Job Interface ===

EPOD_LOAD:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| EPL_SITE_ID || As now 
|-
| EPL_LOAD_ID || As now 
|-
| EPL_DELETE_LOAD || #1 
|-
| EPL_LOAD_START_PLANNED_DATE || Arrival date of the first stop on this trip (or the departure date if no arrival) 
|-
| EPL_LOAD_START_PLANNED_TIME || Arrival time of the first stop on this trip (or the departure time if no arrival) 
|-
| EPL_LOAD_END_PLANNED_DATE || Arrival date of the last stop on this trip 
|-
| EPL_LOAD_END_PLANNED_TIME || Arrival time of the last stop on this trip 
|-
| EPL_LOAD_DISTANCE_PLANNED || Planned distance of the trip 
|-
| EPL_VEHICLE_ID || Assigned Vehicle ID  
|-
| EPL_USER_ID || Assigned driver ID 
|-
| EPL_TRAILER_ID || Assigned trailer ID of the first stop 
|-
| EPL_LOAD_INFORMATION || Trip instructions TTH_COMMENTS 
|-
| EPL_TIMEZONE || Blank 
|-
| EPL_STATUS || "P" 
|-
| EPL_ROUTE_CODE || Route code of this trip if generated from a fixed route. 
|}

Notes:
* #1 EPL_DELETE_LOAD - only used if the trip has been sent to EPOD, and then the trip has been deleted.


EPOD_JOB:

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| EPL_SITE_ID || As now 
|-
| EPL_LOAD_ID || As Now 
|-
| EPL_JOB_ID || omitted or nulled 
|-
| EPL_JOB_CODE || OOH_TMS_REF. #4 
|-
| EPL_JOB_TYPE || #1 
|-
| EPL_JOB_GROUP || #2 
|-
| EPL_CUST_REF || OOH_CUSTOMER_REF. #4 
|-
| EPL_JOB_INSTRUCTION || OOH_SPECIAL_INSTRUCTIONS 
|-
| EPL_OFFICE_INSTRUCTION || New order parameter ROUTE (from tcsFLST_Route) 
|-
| EPL_START_PLANNED_DATE || Arrival Date from this trip's stop 
|-
| EPL_START_PLANNED_TIME || Arrival Time from this trip's stop 
|-
| EPL_END_PLANNED_DATE || From Date from this trip's stop 
|-
| EPL_END_PLANNED_TIME || 0 
|-
| EPL_DISTANCE_PLANNED || 0 
|-
| EPL_CUSTOMER_CODE || Location ID of this stop 
|-
| EPL_CUSTOMER_NAME || From the location associated to this stop's location ID 
|-
| EPL_ADDRESS_1 || From the location associated to this stop's location ID 
|-
| EPL_ADDRESS_2 || From the location associated to this stop's location ID 
|-
| EPL_ADDRESS_3 || From the location associated to this stop's location ID 
|-
| EPL_ADDRESS_4 || From the location associated to this stop's location ID 
|-
| EPL_ADDRESS_5 || From the location associated to this stop's location ID 
|-
| EPL_POSTCODE || From the location associated to this stop's location ID, spaces removed 
|-
| EPL_CONTACT || Blank 
|-
| EPL_TELEPHONE || Blank 
|-
| EPL_EMAIL || Blank 
|-
| EPL_SO_NUMBER || blank 
|-
| EPL_EXT_REF || OOH_DELIVERY_REF. #4 
|-
| EPL_ORDER_DATE || OOH_ORDER_PLACED_DATE 
|-
| EPL_SALES_CONTACT || Order parameter SALESPERSON 
|-
| EPL_SERVICE_LEVEL || OOH_SERVICE_LEVEL_CODE 
|-
| EPL_JOB_STATUS || Omitted 
|-
| EPL_USER_ID || Omitted 
|-
| EPL_COL_DATE || Omitted 
|-
| EPL_SEQUENCE || Trip stop number 
|-
| EPL_LINKED_ID || Trip stop number 
|-
| EPL_UDF_JOBDETS || Omitted 
|-
| EPOD_JOB_ADDRESS || Omitted 
|-
| EPL_OWNER_NAME || OOH_GROUP_CODE 
|-
| EPL_TRAILER_ID || From the trip stop 
|-
| EPL_TIMEZONE || Omitted 
|-
| EPL_LOADING_TYPE || #3 
|-
| EPL_SWAP_VEHICLE || Blank 
|-
| EPL_ACCOUNT || Blank or omitted 
|-
| EPL_ORDER_TIME || Omitted 
|-
| EPL_EXPIRY_DATE || Omitted 
|-
| EPL_EXPIRY_TIME || Omitted 
|-
| EPL_PF_DEPOT || Omitted 
|-
| EPL_PF_TRACKING_NO || Omitted 
|-
| EPL_TRAVEL_PLANNED || Distance from the last stop to this stop 
|-
| EPL_WORK_PLANNED || Time from the last stop to this stop, in whole minutes 
|-
| EPOD_TIME_WINDOWS || Omitted 
|-
| EPL_LAT || Latitude of the location 
|-
| EPL_LONG || Longitude of the location 
|-
| EPL_LOAD_LOCATION || The alternate name of the from location of the order.  
|-
| EPL_COL_TIME || Omitted 
|-
| EPL_LOADER || Omitted 
|-
| EPL_SALES_TEL || Omitted 
|-
| EPL_HAULIER || Blank 
|-
| EPL_TRANSPORT_GROUP || Blank 
|}

Notes:
* #1 Job Type
**  starts with RTN - C else D.
* #2 Job Group:
**  C job type (RTN and COMP jobs) marked as COL, everything else as DEL except WAREHOUSE user (Desk) jobs, which are marked as "DESK"
**  INV jobs marked as DEL,
**  CAL jobs marked as DEL,
**  ISTIN jobs marked as DEL,
**  DESK jobs marked as DESK
* #3 - EPL_LOADING_TYPE - If loading at a fleet depot, set to "L". If unloading at a fleet depot, "U", else blank.
* #4 - Note: This is a change to current mapping and will require a configuration change in Portal and EPOD with regards job references. It may be that this change in configuration will require that the job references listed in these 3 fields are moved around to accommodate the mapping. Therefore the interface should be configurable for the following fields: EPL_CUST_REF, EPL_EXT_REF, EPL_SO_REF. EPL_JOB_CODE will always be populated with OOH_TMS_REF. However, the other field may be populated with any reasonable reference from ORD_ORDER_HEADER and should be configurable as such, through the field name with reasonable drop-down list of values to select from in the configuration.

Interface of EPOD_PRODUCTS should be generated from order lines, not order items.

The products records should be sourced from the ORD_ORDER_LINE. This should be parameter controlled.

{| class="wikitable" border="1"
|-bgcolor="silver"
! Column Name	!! Use 
|-
| EPL_SITE_ID	|| As now 
|-
| EPL_JOB_ID	|| omitted or blank 
|-
| EPL_CONTAINER_ID	|| '000000000000000' 
|-
| EPL_PRODUCT_CODE	|| OOL_PRODUCT_TYPE_CODE 
|-
| EPL_SEQUENCE	|| OOL_LINE_NO 
|-
| EPL_CUST_REF	|| from new field OOL_CUST_ORD_REF 
|-
| EPL_DESCRIPTION	|| OOL_DESCRIPTION, truncated to 40 characters 
|-
| EPL_PRODUCT_QTY_PLANNED	|| OOL_QUANTITY 
|-
| EPL_PRODUCT_QTY_ORDERED	|| OOL_QUANTITY 
|-
| EPL_ITEM_TYPE	|| TRUE 
|-
| EPL_UNIT_TYPE	|| OOL_TRANSPORT_UNIT_CODE 
|-
| EPL_DESCRIPTION_LONG	|| #1 
|-
| EPL_PRODUCT_WEIGHT	|| #2 
|-
| EPL_UNIT_PRICE	|| 0 or omitted 
|-
| EPL_UNIT_VAT	||0 or omitted 
|-
| EPL_POSITION	|| omitted 
|-
| EPL_DEPOT_TEST	|| omitted 
|-
| EPL_PRODUCT_QTY_CASE	|| From new field OOL_PACK_QUANTITY 
|}

Notes:
* #1 - OOL_DESCRIPTION concatenated with PRD_PRODUCT_TYPE PPT_DESCRIPTION2 (delimited with Cr\Lf).
* #2 - OOL_WEIGHT / OOL_QUANTITY


=== C-TMS-EPOD Standing Data Interface===

No modifications are necessary in C-TMS or C-ePOD.


=== EPOD-CTMS Interface ===

The only change here should be that the interface will update order lines, not order items. The process should check the new system parameter to see whether the this is the case.


<!-- NEW PAGE --> 
{{Doc_AppendixNew
||Appendix=A
||Estimate=N
||Glossary=CTLTMS
||Ref1=&nbsp;
||RefV1=&nbsp;
||RefDate1=&nbsp;
||REQ=0
||EST=0
||FS=0
||TS=0
||DEV=0
||ST=0
||IMP=0
||PM=0
||Client={{#var:Client}}
||Year={{#var:Year}}
||FSEST=Y
||Rev1=Matt Tipping
||Rev1Title=OBS Project Manager
||Rev2=Paul Griffiths
||Rev2Title=Customer Representative
}}</div> 
[[Category:{{#var:Client}} FS]]
[[Category:{{#var:SystemCode}}]]