FS 338875 KOO-GAP-SCR2 GAP Export Interface
Knights of Old
GAP Export Interface
CALIDUS ePOD
5th December 2016 - 3.0
Reference: FS 338875 KOO-GAP-SCR2
Contents
Functional Overview
Client Requirement
As part of the Knights of Old GAP contract, add an export in GAP ANSI X12 format at job completion.
The GAP contract is owned by KOO, but every part of the process is performed by GAP or 3rd parties.
The GAP warehouse in Rugby loads the trailers by handballing of cartons and creates ASNs in their system. This is likely the full quantity of product required for the week in France. Each carton is uniquely labelled.
A French 3PL TSA goes to the GAP warehouse and collects the trailer and trunks the load to Paris. The Paris depot is owned and operated by French 3PL MCE, which unloads the trailer. This is updated onto Empirica, KOO's WMS, which is being updated by MCE. This initiates an 861 "Arrival Scan at Pooler" message from Empirica to TrueCommerce (an EDI hub) and on to GAP. The unloaded cartons are sorted into stores. These store cartons are then palletised. These cartons are then loaded onto vehicles, physically scanning each item being loaded. This is updated onto Empirica, KOO's WMS, which is being updated by MCE, which generates an 861 "Pooler Outbound Scan from their Facility" message from Empirica to True Commerce and on to GAP. Empirica is also updated with a load number and each job is routed onto specific days (fixed route/milk runs) and assigned to a trailer. All cartons for a particular store are loaded onto the same load. This also generates messages to CALIDUS ePOD for the execution of the jobs.
MCE then organises the final leg delivery of the cartons to the GAP stores. There are 3 MCE depots:
- Paris - 8 devices
- Lyon - 2 devices
- Strasbourg - 2 devices.
The Paris depot trunks to the other two depots, not controlled by C-ePOD.
Solution Overview
When the job is completed in C-ePOD, there is no required update back to Empirica. A message will be created from C-ePOD to TrueComemrce, consisting of a formatted 861 message is ANSI X12 format.
Notes:
- A single file will be created per order.
- The file will be passed as FTP of flat-file transfer to TrueCommerce.
- The file will be formatted in ANSI X12 format, minus the start/end and sending tags.
- Where orders are completed with cartons with several BOL references or reason codes, these will be ordered and grouped by reference and reason code.
- All cartons delivered without a reason code will be sent as reason code "07"
- Only reason codes "01" and "04" need to be mapped as detail reason codes - all others are not applicable.
- Job Reason codes should be created to identify when the driver is unable to deliver (for example, the site is closed and locked). This will cancel the order and will NOT send a message to GAP. This will then be redelivery attempted, by re-planning the order onto another trip.
Scope
An implementation of the latest version of the CALIDUS ePOD application is required for this contract.
This will be a separate instance of CALIDUS ePOD, hosted on the existing Partnerlink servers. Although this will utilise the Partnerlink servers and database, the application will be distinct and separate and the data will be kept in a separate schema, , to ensure that no issues are generated to the Partnerlink contract.
CALIDUS Portal TTM is not required.
The mobile devices that will be used are M3 SM10 devices, with 1D barcode scanners. 12 are required.
This solution assumes that the Store Barcode is constructed in the same manner for all stores. For example, if the store code is "01234" the store barcode will be "GPS3PL0000000001234". If this is not the case, then this process will fail when the message is processed by GAP.
The Container (Carton) ID passed to C-ePOD must be the full 20-numeric code i.e. including the leading 00.
The True Commerce connection details have not yet been provided and must be before implementation of this change. For testing and initial implementation Knights of Old will set up an FTP site for the generated files.
Set-up
Pre-requisites
Menu Structure
Data
A new Export Configuration is required:
- ID: "GAP"
- Description: "GAP 861"
- Type: "GAP861"
- Transfer Type: "FTP"
- FTP Server: TBC (Knights of Old to host initially).
- User: TBC (Knights of Old to host initially).
- Password: TBC (Knights of Old to host initially).
- Job Types: "D"
- Direction: "Outbound"
Note: Trailers will be mapped as Vehicles. These need not be pre-created within CALIDUS ePOD - these will be created if required when the loads are created.
Reason Codes mapped for Job and Detail - no clause codes will be created as they are not required.
- JOB Cancellation Reason Codes
- "NA" - "No Access to Store"
- DETAIL cancellation Reason Codes
- "01" - "Damaged Carton"
- "04" - "Quality Problem (e.g. Carton open, missing items)"
Site/Job Group Configuration:
- The site linked to the Export Configuration created above.
Functional Description
Data Export
There is no requirement to update Empirica with completed jobs. However, GAP must be updated through the development of a new 861 EDI message.
An Export Configuration will be created for the new message and assigned to the job group. This will be configured for the new type "GAP 861". The settings will be as in the Data section above. This is visible and may be amended in the Admin screen. This will be saved as XF Config ID "GAP".
The Admin screen will be modified to allow the "GAP 861" export type to be specified from the drop-down list.
An Automated Export process will be scheduled to run every few minutes on the server, explicitly for the GAP database. This process will pick up the jobs completed by C-ePOD and not yet exported that have been completed but not cancelled.
The mapping of the message is defined in the 861 business case, replicated in part here, mapping the various fields required:
| Segment | Element | Inbound 861 Mandatory / Optional | Store Delivery 861 Description | C-ePOD Mapping |
|---|---|---|---|---|
| BRA (Pos. 020) | BRA01 | Mandatory | A reference number defined for a particular transaction set or as specified. | Calculated store barcode as "GPS3PL0000000" + EPL_CUSTOMER_CODE |
| Beginning Segment | Mandatory | Gap store bar code if BRA04 = "4" | ||
| BRA02 | Mandatory | Date (YYMMDD) | EPL_START_PLANNED_DATE, converted | |
| BRA03 | Mandatory | "00" - Original | "00" | |
| BRA04 | Mandatory | "4" - Pooler Scan at the Store (Pool Delivery 861) | "4" | |
| DTM (Pos. 070) | DTM01 | Mandatory | "242" - Actual Scan Start Date and Time | "242" |
| Scan Start Date & Time | DTM02 | Mandatory | Scan Start Date (CCYYMMDD) | EPL_ARRIVAL_DATE |
| DTM03 | Mandatory | Scan Start Time of first carton scanned (Local Time HHMM Format) | EPL_ARRIVAL_TIME, converted | |
| DTM (Pos. 080) | DTM01 | Mandatory | "243" - Actual Scan Complete Date and Time | "243" |
| Scan Complete Date & Time | DTM02 | Mandatory | Scan Complete Date or last carton scanned (CCYYMMDD) | EPL_ACTUAL_END_DATE |
| DTM03 | Mandatory | Scan Complete Time (Local Time HHMM) Format | EPL_ACTUAL_END_TIME | |
| TD1 (Pos. 090) | TD101 | Optional | "CTN" - Carton | "CTN" |
| Carton Count | TD102 | Optional | The total carton count | Count of EPOD_CONTAINER records |
| TD5 (Pos. 100) | TD504 | Mandatory | "M" - Motor or "A" - Air or "O" - Ocean | "M" |
| Ship Mode | ||||
| TD3 (Pos. 110) | TD301 | Mandatory | "TL" - Qualifier for trailer | "TL" |
| Equipment Detail | TD302 | Mandatory | Enter your SCAC | "KOOG" |
| TD303 | Mandatory | Enter store number | EPL_VEHICLE_ID | |
| TD308 | Optional | Any two-digit numeric string (not used by application) | N/A | |
| TD309 | Optional | Any numeric string (not used by application) | N/A | |
| N1 (Pos. 130) | N101 | Mandatory | "2L" - Qualifier for corporation | "7P" |
| Pooler ID | N103 | Mandatory | "92" - Mandatory Qualifier | "92" |
| N104 | Mandatory | The Gap assigned pooler id - 9 characters | "000020569" | |
| Note: This should be pulled from the 856 in the ISA segment (Pos. 005). Only provide the Gap assigned pooler id |
||||
| N1 (Pos. 130) | N101 | "SN" - Store | "SN" | |
| Store | N103 | "92" Mandatory qualifier | "92" | |
| N104 | Gap store number | EPL_CUSTOMER_CODE | ||
| Note: This should be pulled from the ASN 856 in the N1 segment (Pos. 220). | ||||
| Note: From this point forwards, this section can repeat based on the BOL and Reason Codes assigned to Containers/Cartons. See notes later.
| ||||
| RCD (Pos. 010) | RCD01 | Mandatory | A unique sequential number assigned by the pooler | Date/Time + incrementing counter for each file created for the export run in format YYYYMMDDHHMMC e.g. 2016102608391 |
| Carton Summary | RCD06 | Mandatory | Quantity in question. Number of cartons received. | Count of EPOD_CONTAINER records |
| C00101 | Mandatory | "CT" - Carton | "CT" | |
| RCD08 | Mandatory | "01" - Damaged Carton | Based on reason codes against EPOD_CONTAINER. Only "01 and "04" will be mapped. | |
| "02" - Quantity Short | ||||
| "03" - Quantity Over | ||||
| "04" - Quality Problem (e.g. Carton open, missing items) | ||||
| "05" - Incorrect Product - out of area carton | ||||
| "07" - Good Carton, Good Condition | Use this is no reason code is specified. | |||
| REF (Pos. 070) | REF01 | "BM" - Memo Bill of Lading Number | "BM" | |
| Memo Bill of Lading | REF02 | Memo Bol Number | EPL_DESCRIPTION_LONG of EPOD_CONTAINER | |
| Note: This should be pulled from the 856 in the REF segment (Pos. 260). | ||||
| MAN (Pos. 180) | MAN101 | Mandatory | "AI" - Qualifier for Carton ID | "AI" |
| Carton Identifier | MAN102 | Mandatory | 20 digit carton code scanned from the carton label | EPL_CONTAINER_ID of EPOD_CONTAINER |
Each field is delimited by the <BEL> character in the final file format.
"RCD", "REF" and "MAN" sections repeat in the file, denoted by the Master BOL and Reason Code. Containers should be sorted on EPL_DESCRIPTION_LONG (Master BOL) and EPL_REASON_CODE (with blank reasons first). "RCD" and "REF" sections should be reproduced every time the reason code and master BOL change.
Example: Assuming a job with the following characteristics:
- Job planned to be delivered to store "02717" on 2nd August.
- Arrived on site on 20th September at 11:18
- Completed on 20th September at 11:28
- 28 planned cartons to be delivered:
- 11 cartons with master BOL reference 000000027542998, of which 2 have reason code "01", 3 have reason code "04" and 6 have no reason code.
- 17 cartons with master BOL reference 000000027542999, of which 1 has reason code "01", 2 have reason code "04" and 14 have no reason code.
The final format is likely to resemble the following:
BRABELGPS3PL000000002717BEL20160802BEL00BEL4
DTMBEL242BEL20160920BEL1118
DTMBEL243BEL20160920BEL1128
TD1BELCTNBEL28
TD5BELBELBELBELM
TD3BELTLBELKOOGBELAB12XYZ
N1BEL7PBELBEL92BEL000020569
N1BELSNBELBEL92BEL02717
RCDBEL1BELBELBELBELBEL2BELCTBEL01
REFBELBMBEL000000027542998
MANBELAIBEL00004200270364233246
MANBELAIBEL00004200270364218038
RCDBEL2BELBELBELBELBEL3BELCTBEL04
REFBELBMBEL000000027542998
MANBELAIBEL00004200270364221595
MANBELAIBEL00004200270364221601
MANBELAIBEL00004200270364228426
RCDBEL3BELBELBELBELBEL6BELCTBEL07
REFBELBMBEL000000027542998
MANBELAIBEL00004200270364229768
MANBELAIBEL00004200270364230641
MANBELAIBEL00004200270364230900
MANBELAIBEL00004200270364231242
MANBELAIBEL00004200270364231662
MANBELAIBEL00004200270364234359
RCDBEL4BELBELBELBELBEL1BELCTBEL01
REFBELBMBEL000000027542999
MANBELAIBEL00004200270364234656
RCDBEL5BELBELBELBELBEL2BELCTBEL04
REFBELBMBEL000000027542999
MANBELAIBEL00004200270364237183
MANBELAIBEL00004200270364237657
RCDBEL6BELBELBELBELBEL14BELCTBEL07
REFBELBMBEL000000027542999
MANBELAIBEL00004200270364238272
MANBELAIBEL00004200270364238371
MANBELAIBEL00004200270364238746
MANBELAIBEL00004200270364240060
MANBELAIBEL00004200270364240466
MANBELAIBEL00004200270364242248
MANBELAIBEL00004200270364247427
MANBELAIBEL00004200270364248394
MANBELAIBEL00004200270364251271
MANBELAIBEL00004200270364254715
MANBELAIBEL00004200270364255323
MANBELAIBEL00004200270364290966
MANBELAIBEL00004200270364291550
MANBELAIBEL00004301081008780587
The file produced will be saved as a text file named as follows:
- EPOD_GAP_GAP861_YYYYMMDD_HHMMSS_C.txt
Where
- "EPOD" is fixed text.
- "GAP" is the recipient from the interface configuration.
- "GAP861" is from the type of the interface configuration.
- YYYYMMDD is the current date, matching the date used in the "RCD" section of the file.
- HHMM is the current time, matching the time used in the "RCD" section of the file.
- C is an incrementing counter for each file produced.
The file will be configured to be FTP's to the True Commerce site. Note: The True Commerce connection details have not yet been provided and must be before implementation of this change.
Note:
- A single file will be created per order.
- The file will be passed as FTP of flat-file transfer to True Commerce.
- The file will be formatted in ANSI X12 format, minus the start/end and sending tags.
- Where orders are completed with cartons with several BOL references or reason codes, these will be ordered and grouped by reference and reason code.
- All cartons delivered without a reason code will be sent as reason code "07"
- Only reason codes "01" and "04" need to be mapped as detail reason codes - all others are not applicable.
- Job Reason codes should be created to identify when the driver is unable to deliver (for example, the site is closed and locked). This will cancel the order and will NOT send a message to GAP. This will then be re-delivery attempted, by re-planning the order onto another trip in the external system.
Development Details
Export:
- The new process of creating an EPOD_XF_CONTROL record will be followed in this case.
- Filename may need extending to support the requirements shown above, specifically relating to the counter and the file extension.
- Job Types should be checked when generating the XF_CONTROL records, or when generating the messages.
- Cancelled jobs should NOT have a message generated for GAP 861 messages, based on the type "GAP861".
The Admin XF Config screen should be extended to:
- display the User and Password if the type is FTP.
- The type of "GAP861" should be added to EPOD_LIST_ITEMS.
- Export Job Types should save correctly.
- It should be possible to identify whether cancelled jobs are exported.
- Recipient should be allowed to be set if the type is "GAP861"
All other changes are explained in detail above.
Appendix A: TEST PLAN
| Test Script / Scenario Reference | GAP Export Interface | Call Number(s): 338875 KOO-GAP-SCR2 |
| Test Script / Scenario Description | Tests covering the export of jobs to GAP. | PASS / ISSUES / FAIL |
| Menu Access | N/A | |
| Pre-requisites | Configured as per specification | Tested By: |
| Test Objective | To test that: messages are sent as expected; messages are formatted as expected. | Date: |
| Step | Action | Result | Remarks | P/F |
| 1 | Export | |||
| Several jobs should be created with multiple containers across 2 BOLs (EPL_LONG_DESCRIPTION) | ||||
| 1.01 | Cancel a Job. | No message is sent. | ||
| 1.02 | Complete a job with all containers successfully delivered. | A single 861 file is created and sent, named correctly, formatted correctly, with the correct data, as per specification. 2 BOLs are shown, with all the containers for the BOLs at status "07". | ||
| 1.03 | Complete a job with 1 cancelled container with reason code "01". | A single 861 file is created and sent, named correctly, formatted correctly, with the correct data, as per specification. 2 sections for BOL1 are shown, with 1 container at status "01" and all others at status "07". All containers for BOL2 should be at status "07". | ||
| 1.04 | Complete a job with several cancelled containers with reason code "01" for BOL1 and BOL2, and several with reason code "04" for BOL2, the rest successfully delivered. | A single 861 file is created and sent, named correctly, formatted correctly, with the correct data, as per specification. 2 sections for BOL1 are shown, with the correct number of containers at status "01" and all others at status "07". 3 sections for BOL2 are shown, with the correct number of containers at status "01" and "04" and all others at status "07". | ||
| 1.05 | Complete a job with all containers cancelled using the two reason codes. | A single 861 file is created and sent, named correctly, formatted correctly, with the correct data, as per specification. 2 sections for BOL1 and BOL2 are shown, with the correct number of containers at status "01" and "04" only. | ||
| 1.06 | Complete several jobs with all containers delivered successfully before the export run. | Several 861 files are created and sent. Each file is named with a counting sequence. Each is formatted correctly with all correct data as per specification and multiple rows for both BOLs at status "01" and "04". |
Appendix B: Document References
B.1 References
| Ref No | Document Title & ID | Version | Date |
| 1 | FS 338835 KOO-GAP-SCR1 GAP Scan Store Barcode on Arrival | 0.1 | 25/10/2016 |
| 2 | 861_4010 Business Case 02252016.xls | N/A | N/A |
B.2 Glossary
| Term | Definition |
|---|---|
| EPOD | Electronic Proof of Delivery. The OBS EPOD system is CALIDUS ePOD. |
| CALIDUS eSERV | The OBS mobile system to complete Service functionality in the field. This is part of the CALIDUS ePOD system. |
| PDA | The mobile device on which the C-ePOD system will run in the field. This can be a Phone, EDA or industrial PDA, running Android. |
| DAL | Data Access Layer. A mechanism for accessing data by the system that is removed from the application, allowing for simplified access and providing protection to the data, as only approved DAL methods can be used to modify it. |
| GPS | Global Positioning System. A mechanism of retrieving accurate positioning information in the form of Latitude and Longitude (Lat-Long) co-ordinates from a device. |
| GPRS, 3G, HSDPA, Data Service | All terms referring to mobile device network connectivity, and the speed at which the device connects to the internet. |
B.3 Authorised By
Ken Hayes | KOO Representative | _____________________________ |
Matt Turner | OBSL Representative | _____________________________ |
