SI EDI

From UG

Revision as of 04:09, 30 August 2013 by Alex (Talk | contribs)
Jump to: navigation, search


Contents

Info

Mantis

mantis parent: 0004242

mantis category: Sales Invoices EDI

Contacts

  • Jessie.Falls@ElizabethArden.com - Transportation Manager
  • Dawn.Byrum@data2logistics.com - Global Solutions Analyst
  • brandon.heaton@data2logistics.com - EDI Specialist

Schedule

August:

  • M: 19 - Dev phase; tests with vendor
  • Tu: 20 - Dev phase; tests with vendor
  • W: 21 - Dev phase; tests with vendor
  • Th: 22 - Dev phase; tests with vendor
  • Fri: 23 - All dev completed; QA/bugfix started; UAT demo;
  • M: 26 (Ukr holiday); SIT; UAT(Marc)
  • Tu: 27 - QA/changes/bugfix/tests with vendor; SIT;UAT(Marc)
  • W: 28 - QA/changes/bugfix/tests with vendor; SIT/User Guide/training
  • Th: 29 - QA/changes/bugfix/tests with vendor; SIT/User Guide/training
  • F: 30 - staging; User Guide/training

Sept:

  • M: 2 (US holiday) - staging
  • Tu: 3 - staging; Release at 7pm EST
  • W: 4 - in production; ihelp monitoring
  • Th: 5 - in production; ihelp monitoring
  • F: 6 - in production; ihelp monitoring

Phases

Arden agreed to staged approach. We can first convert invoices just for one MOT and or Jag office. And later turn on others.

Sorry, above is not true anymore. First release should cover all MOTs, all Jag offices.

See Also

  • Protocol 210 - pdf attached to parent mantis
    • This Draft Standard for Trial Use contains the format and establishes the data contents of the Motor Carrier Freight Details and Invoice Transaction Set (210) for use within the context of an Electronic Data Interchange (EDI) environment. The transaction set can be used to provide detail information for charges for services rendered by a motor carrier. It is used both as a motor carrier invoice to request payment or as details pertaining to motor freight shipment(s) charges.

Definitions

  • EDI - Electronic Data Interface
  • EDI partner - vendor that receives invoices electronically , validates them and then posts them into company's information system

Business Need

1) Immediate business need is to respond to Elizabeth Arden's request to start sending most invoices electronically (through EDI).

This would cover:

  • all MOTs and
  • all Jaguar offices

Exceptions:

  • invoices for Maintenance, Web Portal and Administration fees would still be send through regular mail

Arden has a new EDI partner company to support this: data2logistics.

Arden discontinued similar EDI with previous EDI partner Trendset permanently about 1 month ago.

2) Additional long term business need is to support sending invoices electronically for any Bill To party/Client that requests that. This means ability to integrate with any EDI vendor. Challenge is to support various protocols and data requirements.

Systems Analysis

Parameters in general

Number of parameters needs to be defined for each possible case of EDI integration for the purpose of sending sales invoices electronically.

Each case is defined by the following:

  • typical case is when one company that Jaguar is invoicing have contracted only one vendor for these types of transmissions
  • more complex case is when several companies are contracted
  • each case is also defined by:
    • list of Bill To parties (Invoice parameter)
    • possibly list of MOTs supported
    • possibly other parameters

Other parameters:

  • Case id. Values = {1,2,3,....}
  • Vendor. Values = {data2logistics, ... }
  • Content Protocol. Values = {XML, EDI, ...}
  • Transport.
    • protocol. Values = {FTP, ...}
    • connection credentials for inbound / outbound


Solution

Architecture

Since many Jaguar client companies would start/stop sending invoices through various EDI providers it would be nice to create generic solution to accommodate that.

Per discussion with Kostya, Misha, Alex solution should satisfy principles below.

  • create set of java interfaces that will cover different future scenarios:
    • different message protocols (XML, EDI, etc)
    • different transport protocols (FTP, etc)
    • different Bill To Company requirements
  • configuration/parameters:
    • should not be kept in DB
    • should be managed through both
      • UI based admin
      • XML format or plain text format (Java properties file as an option) stored on the server

DB

Some related parameters are kept in Invoice table

Add another table. Attributes:

  • inv id
  • EDI Vendor id
  • EDI Status
  • transmit date
  • status date

Table is named as Shipment Invoice Transmission.

EDI Status

This is attribute of the table above.

Takes values:

  • New - until successful transmission
  • Transmitted - after successful transmission
  • Accepted - accepted by EDI Vendor and forwarded to Bill To party
  • Rejected - rejected by EDI vendor

Test Messages

  • Create and send several test EDI files to data2logistics.
  • Write by hand some examples first and email to vendor. Later produce examples as they are generated by CT2 code.
  • Send through e-mail first and later again post to FTP defined by vendor.
  • Samples should be of recent 2013 invoices.
  • Provide one for each mode: air, ocean, trucking.

Parameters

How to store them - see #Architecture.

List of parameters for data2logistics

Admin

Some params are defined through Admin section of CT2.

Admin params
  • List of Bill To for EDI (these define what invoices are to be sent to given EDI vendor)
  • Email notifications list
  • Transmission:
    • how often
    • switch (enable/disable transmission options)
Transmit Older Invoices

Admin should have such panel.

Goal is to be able to transmit invoices issued before release of this module.

To do that in admin add 5-10 pairs of Transmit From Date + associated Bill To (or allow user select ho many pairs he wants). Plus "Schedule" button. On submit system would schedule this job to be run on general schedule OR run it right away.

  • Transmit From Date is a date from which to search/transmit invoices. It is based on Created On Invoice date.
Regenerate and Resend Rejected Invoices checkbox

Add "Regenerate&Resend Rejected Invoices next time the process will run" checkbox.

Add note: Will be automatically be unchecked after process is finished.

When it is checked system will:

  • re-generate all EDI files for Invoices flagged as Rejected
  • will resend them
Config file params
  • Inbound folder credentials (address, login, pwd)
  • Outbound folder credentials (address, login, pwd)
Bill To values list

Per Marc initial list is this:

  • EA Domestic Portal – Roanoke
  • Elizabeth Arden – Roanoke
  • Elizabeth Arden Int'l Sarl C/O 4 Pole SA – Cointrin-Geneva
  • EA USA Domestic – Roanoke

Creating Invoice

User experience of creating invoice that is to be transmitted through EDI would be the same with few exceptions, See below.

  • After user selects "Bill To Party" system will compare it with a list of Bill To parties registered in the CT2 as Bill To Parties for EDI (it is defined by user in #Admin). If there is a match system will do the following - see 1,2 below.
  • 1) Will add a prominent note to the Invoice Form (say in red below Inv Date field). It should say: 
Please Note: 
* This invoice is identified to be transmitted through EDI 
based on selected Bill To Party.
* As a result of this some fields are not editable on this form. 
To edit them go to appropriate CT Editor Tab (Gen, Imp, Exp or Com)
  • 2) Make all fields on Invoice Form read-only except:
    • File ref#:
    • Invoice Comments:
    • Bill To
    • fields on Table Of Charges (TOC) section

Above is required to:

  • make sure that data on template/sales pdf is identical to data on CT Editor/DB
  • to EDI-transmit proper info (can not be transmuted data for some fields such as Airline - it is to be code based not plain text as on template. Same problem with Shipper field, etc)

Translating invoice info into EDI format

Invoice info is to be translated into EDI formats.

data2solutions that will accept all EDI messages for Arden communicated these requirements - see below.

Protocols

  • 110 Air Freight Invoice
  • 210 Motor Carrier Freight Details and Invoice
  • 310 Ocean Freight Invoice

See #See Also sec.

Ardens List

Below is required by Arden.

  • a. Ultimate Consignee must be captured for International Shipments
  • b. Invoice Number
  • c. Bill of Lading number
  • d. LOAD NUMBER (required for inbound only)
  • e. Shipper Acct # (only required for small package carriers)
  • f. Shipper info including address (city, state, zip and county code)
  • g. Consignee Info including address (city, state, zip and county code)
  • h. Port of Entry (only required air / ocean int. shipments)
  • i. Port of Departure (only required air / ocean int. shipments)
  • j. Delivery Date
  • k. Ship Date
  • l. Invoice Date
  • m. Total # of pieces
  • n. Total Weight
  • o. Billed Weight
  • p. Mileage
  • q. Movement Type (Mode and any expedited options)
  • r. Container Size (20, 40, 40HC, LCL) (only required air / ocean int. shipments)
  • s. Terms / Incoterms
  • t. Carrier Name
  • u. Pro#
  • v. Freight Cost
  • w. Accessorial costs
  • Misc:
    • ISA Qualifier: *ZZ*
    • ISA Receiver ID: ELARUS
    • GS Receiver ID: ELARUS

Additional info about some fields:

A - This is in the event of a stop off scenario. Otherwise this is just the consignee.

P- Mileage should be included in the L7:11 field for both Ocean and Air when available.

Q- This is referencing any special service levels – Examples – 1 day Air, 2 day air, air priority, etc.

U- As stated above the Pro# should be included in a N9:03 field with a ‘PO’ qualifier in the N9:02 field.

V- These can be the codes from your system but we require a list of these codes and their definitions to define them within our system.

Original Mishas list

What Misha believes he is sending.

Shipper:. Shipper (a.k.a. GenTab.2) from GenTab of CT.

Consignee:. Consignee (a.k.a. GenTab.3) from GenTab of CT.

AIRLINE:. Airline (a.k.a. ExpTab.4) from ExpTab of CT.

AIRPORT OF DEPARTURE:. Airport Of Departure (a.k.a. ExpTab.11a) from ExpTab of CT.

Date Of Departure:. Actual Time of Departure (ATD a.k.a. ExpTab.11c) from ExpTab of CT.

AIRPORT OF DESTINATION:. Airport Of Destination (a.k.a. ExpTab.14a) from ExpTab of CT.

Estimated Date of Arrival:. Estimated Time of Arrival (ETA a.k.a. ExpTab.14b) from ExpTab of CT.

Packages:. Group of totals from Grand Total Table (below Commodity Table) on ContTab of CT:

Pallets. Reflects Ttl # of plts.

Cartons on Pallets. Reflects Ttl pkgs on plts.

Cartons (loose). Reflects Loose pkgs.

Weight/Volume:. Group of weight totals:

Fields

Below is a list of fields with all related info. Note:

  • Client's map/comment: below relates to #Ardens List
  • EDI below relates to: 110/210/310 protocols
  • Client in this case is Arden

We describe fields in the following format:

  • FieldName
    • CT2 map:
    • Required by EDI:
    • Required by Client:
    • Client's map/comment:
    • EDI segment/element:
    • CT2 group logic:
    • Note:
Invoice Fields General
  • Invoice Number
    • CT2 map: Invoice No
    • Required by EDI: Y
    • Required by Client: Y
    • Client's map/comment: b. Invoice Number
    • EDI segment/element: B302
    • CT2 group logic: n/a
  • Invoice Issuing Date
    • CT2 map: Invoice Date
    • Required by EDI:  ?
    • Required by Client: Y
    • Client's map/comment: l. Invoice Date
    • EDI segment/element: ?
    • CT2 group logic: n/a
  • Bill To Company Name
    • CT2 map: same
    • Required by EDI:  ?
    • Required by Client: N
    • Client's map/comment:
    • EDI segment/element:
    • CT2 group logic:
  • Bill To Address
    • CT2 map: same
    • Required by EDI:  ?
    • Required by Client: N
    • Client's map/comment: na
    • EDI segment/element:  ?
    • CT2 group logic: na
  • Invoice Comments
    • CT2 map: same
    • Required by EDI: N
    • Required by Client: N
    • Client's map/comment: na
    • EDI segment/element:  ?
    • CT2 group logic: na
  • Invoice Total Amount
    • CT2 map: same
    • Required by EDI: Y
    • Required by Client: Y
    • Client's map/comment: na
    • EDI segment/element:  ?
    • CT2 group logic: na
  • Invoice Currency
    • CT2 map: same
    • Required by EDI: Y
    • Required by Client: Y
    • Client's map/comment: na
    • EDI segment/element:  ?
    • CT2 group logic: na
Invoice TOC fields

For every line in TOC we are sending this set of fields:

  • CC#
    • CT2 map: same
    • Required by EDI:
    • Required by Client:  ?
    • Client's map/comment: na
    • EDI segment/element:
    • CT2 group logic: na
    • Note:
  • CC Description
    • CT2 map: same
    • Required by EDI:
    • Required by Client:  ?
    • Client's map/comment: na
    • EDI segment/element:
    • CT2 group logic: na
    • Note:
  • Qty
    • CT2 map: same
    • Required by EDI:
    • Required by Client:  ?
    • Client's map/comment: na
    • EDI segment/element:
    • CT2 group logic:
    • Note:
  • Rate
    • CT2 map: same
    • Required by EDI:
    • Required by Client:
    • Client's map/comment: na
    • EDI segment/element:
    • CT2 group logic:
    • Note:
  • Currency
    • CT2 map: same
    • Required by EDI:
    • Required by Client:
    • Client's map/comment: na
    • EDI segment/element:
    • CT2 group logic:
    • Note:
  • Amount
    • CT2 map: same
    • Required by EDI:
    • Required by Client:
    • Client's map/comment: na
    • EDI segment/element:
    • CT2 group logic:
    • Note: 
NOTE FROM EA: Above fields corresponds to these on #Ardens List:
- v. Freight Cost; 
- w. Accessorial costs
CT fields from pdf invoice

Misc:

  • CT# // c. Bill of Lading number //
  • Shipper Company and Address // f. Shipper info //
  • Consignee Company and Address // g. Consignee Info //
  • MOT // q. Movement Type //
  • Airline or Shipping Line or Pick Up Trucker // t. Carrier Name
  • Gen Tab. 14. Shipping Terms // s. Terms / Incoterms
  • Cont Tab // r. Container Size

Commodity:

  •  ?? // m. Total # of pieces //
  • TTl GW // n. Total Weight and o. Billed Weight //

Locations:

  • Port of Loading // h. Port of Entry
  • Port Of Discharge // i. Port of Departure

Dates:

  • Actual for Destination Door // j. Delivery Date
  • Actual for Origin Door // k. Ship Date
Additional CT fields
  • GRP# // d. LOAD NUMBER
Additional Ardens List fields
  • na? // e. Shipper Acct # (only required for small package carriers) //
  • optional // u. Pro#
  • optional // p. Mileage
  • Consignee company name and address // a. Ultimate Consignee //
Data2Log fields
  • ISA Qualifier: *ZZ*
  • ISA Receiver ID: ELARUS
  • GS Receiver ID: ELARUS

Sending Invoice for a CT group

This is covered in #Fields section.

Sending a Credit Note

  • there is indicator in B3 segment
  • and also "-" before amount

Discovering and sending invoices

  • Java process will wake up accordingly to defined in #Parameters schedule (for example once a day) and:
    • a) discover what invoices are subject to transmission to given EDI vendor
    • b) send to required destination using required protocol
  • two types of invoices will be sent:
    • all newly generated invoices since last transmission
    • all failed invoices


FTP

  • post files on their ftp site 
Address: freight.data2logistics.com
Username: JAFR1
Submission Folder: Inbound 
997 Folder: Outbound
Password: ***

Same site for prod and pre-prod.

Archive for outbound files

We need to keep copy of all outbound files we send.

Ideally separate folders for different EDI Vendors.

To be cleaned up every X months.

Status Updates

  • Rejected invoices will be handled automatically via the 997 (or the ‘functional acknowledgment’) that will be placed in the outbound folder of the FTP for Jaguar to retrieve
  • System will update status attribute of invoice in DB accordingly

Invoice reporting

Add to Search Invoices Report the below.

Filters:

  • EDI Vendor (name of the company that receives given invoice through EDI)
  • EDI status (accepted vs rejected)
  • date invoice was transmitted
  • date EDI status was received

Output fields:

  • EDI status (accepted vs rejected)
  • date invoice was transmitted
  • date EDI status was received

Dashboard

  • add option to Search Invoices Report to be a "dashboard report"
    • managed through Scheduled Reports Menu
    • 1st level - standard counter
    • 2nd level - HTML table (use existing table we have for this rep)
      • add "why rejected" column
    • 3rd level - download to xls
      • all existing fields plus "why rejected"
  • "why rejected" is a field that keeps reason for rejection that comes from EDI provider (when available)

Logging

Post into Log component.

Add "Sales Inv EDI" category

Post events related to:

For a/ create one entry

  • that summarizes under "Description" log field:
    • when process run
    • how many invoices/cr notes were discovered and sent
    • list of inv numbers
  • other log fields:
    • "User / Office" = Inv EDI process
    • "Operation" = sending

For b/ create one entry

  • that summarizes under "Description" log field:
    • when update was discovered
    • how many invoices/cr notes were received with success and how many with failed status
    • list of inv numbers with success and list with failed status
  • other log fields:
    • "User / Office" = Inv EDI process
    • "Operation" = updates

Log for Admin

Add standard logging for #Admin params.

Email Notifications

If at least one invoice is rejected then group of users defined in #Parameters should be notified through e-mail.

User Roles and Use Cases

User Roles

  • Admin -
  • Operator -
    • issue Inv or CN
    •  ! issue credit note only against "Accepted" invoices !
  • System process -
  • Accountant -
    • track invoices that have been created several transmission periods ago but still in "New" or in "Rejected"

. Report these cases to ihelp.

  • Ihelp engineer -

Use Cases

Initial Set Up

Changing parameters

Creating Invoice

Sending Invoice

Monitoring

Handling Failed Invoices

Figures




Initial SOWs

SOW 1 Create test message

mantis: 4249

spec: #Test Messages

SOW 2 Create Architecture

mantis: 4251

spec: #Architecture

SOW 3 Defining and editing all parameters

mantis: 0004262

spec: see #Parameters

SOW 4 Translating invoice info into EDI format

mantis: 4257

spec: see #Translating invoice info into EDI format

SOW 5 Discovering and sending invoices

mantis: 4258

spec: #Discovering and sending invoices

SOW 6 Receiving Status updates

mantis: 4259

spec: #Status Updates

SOW 7 Invoice reporting through Search Inv Report

mantis: 4260

spec: #Invoice reporting

SOW 8 Logging and Email Notifications

mantis: 4261

spec: #Logging and #Email Notifications

SOWs After UAT and QA started

SOW 9 Admin

mantis: 4742

spec:

1) Add "Sales Invoices EDI" item to "CT2 Main Menu > Acc"

2) Move "CT2 Main Menu > Admin > Acc > Trendset Admin" to "CT2 Main Menu > Acc > Sales Invoices EDI"

3) Add "Data2logistics Admin" to "CT2 Main Menu > Acc > Sales Invoices EDI"

4) Create #Admin page to manage: #Admin params under "Data2logistics Admin" (see above)

5) see #Transmit Older Invoices

SOW 10 Dashboard

mantis: 4273

spec:

SOW 11 Changes to Issue New Invoice Form

mantis: 4274

spec: see #Creating Invoice

SOW 12 Add EDI status column to Invoice table on Inv Tab

mantis: 4275

spec:

Add new column:

  • column is #EDI Status
  • Add to Sales inv / Credit Notes table on Inv Tab
  • add on both CT Editor and CT Viewer
  • add to the right of column "Currency"

SOW 13 Add validations to Invoice Form

mantis: 0004276

spec:

  • Similarly to SOW11 in case of Invoice for EDI add new logic to Invoice Form - validations.
  • Field is required if it is set as Required by EDI OR Required by Client - see #Fields section.

UI:

  • System would display say red star next to missing fields and NOTE would have additional bullet saying: Fields marked * are required. Please go back to CT Tabs to fill in required fields. Or any alternative design.
Retrieved from "https://wiki.jaguarfreight.com/SI_EDI"
Personal tools