John Lewis EDI Invoice Generation

The purpose of this document is to explain in detail the business requirements and logic flows for generating an EDI Invoice file to be sent to John Lewis

The EDI Invoice is a standard step in an EDI connection and consists of providing pretty much the order information + a few more specifics regarding the actual invoice (like payment terms, Invoice number etc)

Every John Lewis EDI order should receive an invoice at the right time. For the time being we don’t need to employ our Invoicing functionality as all information needed is already on the order. The flow should be as follows - each Order Shipment for a John Lewis Order that is successfully completed should be tracked and once a specific number of days has passed it should be picked for Invoice generation.

Shipment should be tracked by `Order Shipment > Shipment Time` - only for `Completed` Shipments. Each `Shipment` is to be tracked on its own as we will have to generate an invoice for each one that is on a `Completed` status

Days delay for Invoice generation should be picked from `Account John Lewis > Invoice Generation Days Delay` (new field) and should always be treated in days (meaning it should be converted to seconds when used with the `Shipment Time` timestamp) - **<v1.3>** if Days Delay field is empty or 0 it is still viable, it should be treated as no delay needed and all other triggers to work from there **</v1.3>**

With the above two and the mapping below we can guarantee ourselves generation of the invoice at the right time with the right delay for the correct items. The only thing left is to make sure we generate only one invoice per Shipment

Suggested method - Create a new table John Lewis Invoice as dependent to  Order Shipment. Table should hold just a `Status` field so we can track what is happening with values `Pending`, `Completed` and `Error`. We should track (as per the above explanation for readiness and delay) each Shipment for the John Lewis account that doesn't have an Invoice record OR ANY such that have an Invoice record on a `Pending` Status:

• If we generate and upload an invoice to the ftp successfully we should put the John Lewis Invoice > Status to Completed
• If we don’t manage to generate an invoice due to missing information or failed upload to the FTP we should set the John Lewis Invoice > Status to Error and store an error with the correct message in table Order Error - <v1.3> the error should be with type “Invoice” </v1.3>

The John Lewis Invoice table should hold 1 to 1 connection

The FTP to which we should export the Invoice file is to be picked from Account John Lewis > EDI FTP Credentials and we should be looking for a path with label “InvoiceExport”

We are looking to export a file with a name “INV{{ShipmentID}}” where the ShipmentID is the id of the Order Shipment we’ve picked for export. The file should have no extension so just “INV0000001“ (if the ShipmentID we pick is less than 7 numbers we should always pad the front with zeroes)

The EDI file is a string of information consisting of different segments marked with a signature 3 letter tag, followed by information and closed with a single quote at the end ( ' ) before the next segment starts. Each segment has subsegments and parameters. Subsegments are separated with a plus ( + ) and parameters are separated by a colon ( : ). As we don’t need to understand all segments, subsegments and parameters for the invoice we are aiming to export fixed string with specific tags which are explained with examples and how they should look in the mapping below

Example file

UNB+UNOA:3+2243877030000:14+5023949000004:14+230216:1605+1'
UNH+INV000001+INVOIC:D:01B:UN:EAN011'
BGM+388+INV000001+9+NA'
DTM+137:20230216:102'
PAI+::42'
RFF+ON:444005'
DTM+171:20130612:102'
NAD+BY+5023949000004::9'
RFF+VA:GB232457280'
NAD+SU+2243877030000::9'
RFF+VA:GB835502930'
NAD+DP+5023949643131::9'
RFF+VA:GB232457280'
TAX+7+VAT+++:::20+S'
MOA+124:22.8'
CUX+2:GBP:4'
PAT+1++5:3:M:1'
LIN+1++5420019615968:SRV'
QTY+47:1'
MOA+203:114'
PRI+AAA:114:CA'
TAX+7+VAT+++:::20+S'
MOA+124:22.8'
UNS+S'
CNT+2:1'
MOA+86:136.8'
MOA+125:114'
MOA+176:22.8'
TAX+7+VAT+++:::20+S'
MOA+124:22.8'
UNT+30+INV000001'
UNZ+1+1’

File Mapping: Mapping is done based on standard forms of EDI handling. It is split in 3 sections Header/Body/Footer where expectation is values in Body can be replicated for each line to be added

{{buyerGln}} - the GLN is a global identification number provided by organisations like GS1 (essentially a barcode for companies and their locations). These are used in different places in EDI messages and are basically constants but we can use them as placeholders for any future use or ease of change everywhere at the same time. JL’s GLN is “5023949000004” {{dateTimeUNB}} is to be in format YYMMDD:HHMI and is to be the date and time of preparation of the message {{controlReference}} - up to 14 characters alphanumeric unique ID for the message generated by us | | UNH | UNH+INV000001+INVOIC:D:01B:UN:EAN011' | UNH+{{uniqueMessageId}}+INVOIC:D:01B:UN:EAN011' | | {{uniqueMessageId}} - up to 14 characters alphanumeric unique ID for the message generated by us (I believe it can be the same as {{controlReference}} )

This is essentially the first header segment or in other words the first segment of the actual message | | BGM | BGM+388+INV000001+9+NA' | BGM+388+{{invoiceNumber}}+9+NA' | | {{invoiceNumber}} - this is the unique ID for each invoice. To achieve both uniqueness and general ease of customising it we want to pick the id of the Order Shipment we’ve picked to create the invoice and add a prefix from Account John Lewis > EDI Invoice Prefix. In case such prefix is not present we are to just use the value from the ID. As mentioned above when successfully creating an Invoice with the specific number we should also store a record in the Invoice table with this value here being the {{invoiceNumber}} and status “normal” BGM signifies the beginning of the message (or generally regarded as the first “header” segment in EDI) | | DTM | DTM+137:202302161000:102' | DTM+137:{{dateTime137}}:203' | | {{dateTime137}} - The 137 DTM signifies the date time of the creation of the message. Pretty much the same as the {{dateTimeUNB}} above but it has to be in the format of YYYYMMDDHHMI | | PAI | PAI+::42' | PAI+::42' | | Hardcoded | | RFF | RFF+ON:444005' | RFF+ON:{{marketplaceOrderId}}' | Orders > Marketplace Order ID | {{marketplaceOrderId}} - straight up mapping | | DTM | DTM+171:20130612:102' | DTM+171:{{orderCreatedTime}}:102' | Orders > Order Created Time | {{orderCreatedTime}} - straight up mapping Should be in the format YYYYMMDD | | NAD | NAD+BY+5023949000004::9' | NAD+BY+{{buyerGln}}::9' | | {{buyerGln}} - please see explanation above Segment specifying address of parties by their respective GLNs (Global Location Number) - this is for the Buyer (in this case John Lewis as the MOR) | | RFF | RFF+VA:GB232457280' | RFF+VA:{{buyerVat}}' | | {{buyerVat}} - the VAT registered number of the buyer in UK - this is John Lewis' number which is “GB232457280“ | | NAD | NAD+DP+1200109897603::9' | NAD+DP+{{sellerGln}}::9' | | {{sellerGln}} - please see explanation above Segment specifying address of parties by their respective GLNs (Global Location Number) - this is for the Delivery Party | | RFF | RFF+VA:GB835502930' | RFF+VA:{{sellerVat}}' | Account John Lewis > Seller VAT Number | {{sellerVat}} - straight up mapping | | NAD | NAD+SU+1200109897603::9' | NAD+SU+{{sellerGln}}::9' | | {{sellerGln}} - please see explanation above Segment specifying address of parties by their respective GLNs (Global Location Number) - this is for the Supplier Party | | RFF | RFF+VA:GB232457280' | RFF+VA:{{sellerVat}}' | Account John Lewis > Seller VAT Number | {{sellerVat}} - straight up mapping | | TAX | TAX+7+VAT+++:::20+S' | TAX+7+VAT+++:::{{maxPercent}}+S' | | {{maxPercent}} - the biggest Product in Order > Marketplace VAT Percent from all products selected for this invoice | | MOA | MOA+124:22.8' | MOA+124:{{totalVat}}' | | The sum of all Product in Order > Marketplace VAT Item Price for all products participating in the shipment. This includes multiplying by the correct quantity when there are more than one lines shipped from the same product in an order Example: we have an order with 2 ordered products (SKU1 and SKU2) each x2 qty. In the shipment we’ve picked we have both lines for SKU1 and 1 line from SKU2. This means that we have to pick the Marketplace VAT Item Price from SKU1 and multiply by 2 and pick the value for SKU2 and multiply only by 1 - sum the two results to get the totalVat | | CUX | CUX+2:GBP:4' | CUX+2:GBP:4' | | Hardcoded | | PAT | PAT+1++5:3:M:1' | PAT+1++5:3:{{periodType}}:{{numberOfPeriods}}' | | {{periodType}} - Account John Lewis > Invoice Payment Terms Period Type (new field) - we want this to be an enum field with the following options: • Day (D) • Month (M) • Working days (WD) • Year (Y) The user should see the label values where we should pass the value in brackets in the EDI {{numberOfPeriods}} - Account John Lewis > Invoice Payment Terms Number of Periods (new field) - free text field which is to be picked and sent as is - should be numeric value |

Again to confirm - header is to appear once in every message we generate

The Body fields are to be replicated for each Product (each Ordered product, not each Order Item Line in Hemi! Lines are to be combined per product and just to flag once again - only those that are part of the shipment should be selected) <v1.3> In a case where we have multiple shipment rows for the same SKU each one is to be exported as a separate body (LIN) repetition with all necessary fields and quantity for that specific row <v/1.3>

Each segment repeated from the Body should follow the same pattern - meaning if we have multiple Products that we are exporting we should repeat the following for each SKU: LIN’QTY’MOA’PRI’TAX’MOA'

So to sum it up - we have 2 triggers for an invoice generation - A Completed Shipment that has its days passed and doesn’t have a record John Lewis Invoice section OR a record on a status Pending

Once an invoice has been picked for generation it should apply the above selection and validations and create a file with the afore mentioned naming