EDI with Trendset

From UG

(Difference between revisions)

Jump to: navigation, search

Revision as of 20:32, 31 October 2011

Info

0002996: [EDI with Trendset] .... <project>

! Please note that there are a lot of useful email communication posted into Mantis notes under 2996

Requirements from Elizabeth Arden

Core requirements

Core requirements are summarized in the following letter from Arden.

Letter from Tammy

From: Mowbray, Tammy M [1]

Sent: Friday, June 24, 2011 4:59 PM

To: Robert Link; Marc Selter; Philippe Delorme; Denise Guastella

Cc: Falls, Jessie

Subject: FREIGHT BILL AUDIT AND PAYMENT TRANSITION TO TRENDSET

All,

As many of you are aware, EA will be transitioning our freight bill audit and payment process to Trendset, a 3rd party freight bill payment company. We will be transitioning the business in phases and Elizabeth Arden would like for Jaguar invoices for imports, domestic inland and non Zug export (Australia, New Zealand, South Africa and Puerto Rico) to transition on 8/1/11 via EDI. Elizabeth Arden requests that Jaguar work directly with Trendset to start EDI testing immediately and the direct contact information is listed below. Trendset has also sent EDI specifications to Jaguar.

Since Jaguar is one of EA's biggest carriers and therefore will have a largest impact on this transition, I wanted to advise of a few key elements that need to be taken into consideration to ensure the transition is seamless.

1. Paper Invoices: Will continue to be sent to EA until Jaguar can complete EDI testing with Trendset and receive final approval to start from EA.

2. Inland Domestic Invoices: Will need to receive one per shipment vs current consolidated invoices (or have the two IT teams work out). EA will need to have a record of each shipment and cost per each shipment to be visible in TS, no exceptions. This is required for EA financial reporting.

3. Main reference fields: Will need Jaguar to transmit the "load number" as the bill of lading number for domestic inbound shipments, and the "ct ref. number" as the bill of lading number for both imports and exports.

4. Invoice Timing: Need invoices sent asap to avoid gaps in accrual process. Please advise how this works for each type of shipment (imports, exports and domestic inland). When will the EDI transmission be sent, at shipment time, receipt time or when?

Please advise of any questions or concerns regarding the 8/1 go live EDI date.

TRENDSET INFORMATION:

TRENDSET EDI CONTACT: Lisa Shampine at 864-297-9255, or email lisa.shampine@trendsetinc.com.

Once the EDI process starts, your contact person at Trendset for questions concerning freight payments will be Trendset Carrier Services at 864-527-4395 or carriersupport@trendsetinc.com.

Payment information is also available upon registration, on their carrier website www.trendsetcarriers.com <http://www.trendsetcarriers.com/> [^] .

Regards,

Tammy Mowbray Director of Transportation

Implementation requirements from Trendset

Implementation requirements from Trendset are very well summarized in the following letter from Lisa.

From: Lisa Shampine [2]

Sent: Wed 6/15/2011 1:58 PM

To: Alex Dobrovolsky

Subject: EDI with Elizabeth Arden

Hello from the E-Commerce team at Trendset, Inc. Trendset would like to receive EDI for our mutual client Elizabeth Arden. Attached you will find a list of valid EDI locations for Elizabeth Arden and Trendset's 210 specifications.

Receiver ID - TRN200115

Receiver ID Qualifier - ZZ

Location Code - See attached

Location Code Qualifier - ZZ (Location codes should be placed in the N104 of the shipper segment for prepaid and 3rd party shipments, and in the N104 of the consignee segment for collect shipments)

Type of invoices to be sent EDI - Prepaid, Collect, 3rd Party

If you have round trip or multi stop shipments please include those in your test files.

Segment Terminator - "~" (HEX 7E)

During testing the ISA15 should be a "T". Once approved for production it should be changed to a "P".

- - All invoices transmitted during testing will not be loaded into the Trendset production database unless otherwise notified by the carrier to do so. Please continue to send paper until all testing has been completed and a production notification has been sent.

We will need the following information prior to receiving your first test file.

1. Your company's SCAC Code
2. Name and email of person to received EDI Rejection notices
3. Transmission method (VAN, FTP)

4. Will you have any multi stop invoices?

5. Your sender id and qualifier

Please let us know if you have any questions. Thanks and Have a Great Day!

Lisa Shampine

EDI Analyst

Trendset Inc

864-527-4392

lisa.shampine@trendsetinc.com<mailto:brian.adams@trendsetinc.com>

EDI TO TRENDSET

Business Process

Current Process that JFS handles EA billing

Mantis: 3172

1) Trucking Company send an operator a list of shipments (CTs) that delivered on the day before (daily)

2) Jaguar will group CTs together in the master> Misc tab> Trucking Biling Manifest(each day for the billing between Jewels and JG)

3) Jaguar checks the shipments that delivered the day before (group) (from the list that the Trucker gave to them) and enter all necessary information such as rate, min, max info, fuel...etc

4) Jaguar generates a Trucking Billing Manifest and save the spreadsheet on their local machine, then Jaguar adjusts information (fuel, weight...etc) then sends it to the Trucker to review (This process is between Jaguar and the trucker for the billing purpose). Please note that this process is nothing to do with Arden.

5) Once trucker review the numbers until it is correct, then trucker send it back to Jaguar for the cost that they will bill Jaguar. At this point Jaguar will use this information as the based information and mark up the cost on top of what Jaguar get billed to Arden to make some profit. Jaguar manually calculates and marks up number for Elizabeth Arden. This spreadsheet is worth for one day of delivery. Jaguar holds on the spreadsheet with the mark up price each day and wait to combine with 5 days of spreadsheets to send to Arden one time.

6) Jaguar has to repeat the process 1) -5) for each day for the shipment that delivered each day. They save these spreadsheet on their local machine (Monday to Friday worth of data)

7) Next Monday, Jaguar has to invoice Elizabeth Arden, Jaguar creates a sale invoice from Invoice tab. This invoice is worth for 5 days shipments from the week before. The total cost will cover all 5 days shipments.

8) Jaguar submit the invoice (1 invoice covered for 5 days of shipments) and the Trucking Billing Manifest (5 days) together to Arden. (Invoice is the cost that Arden has to pay, and Trucking Billing Manifest is the detail of shipments that shipped for this invoice; back up detail information for the invoice)

New Process that has to be changed

As we will transmit the invoice and the trucking billing manifest via EDI daily. We will need to map these info together. Trucking Billing Manifest, JFS has to only generate one spreadsheet for daily shipments (1 excel spreadsheet = 1 day of shipments:1 excel for 1 Master);

This spreadsheet consists of all the shipments and all the loads that picked up and delivered the day before(daily)
JFS has to be able to manual edit data(mark up detail cost) on the spreadsheet before uploading to the system then transmit the information via EDI with the Sale Invoice.
System will automatically create an invoice per load. If this spreadsheet consist of 10 loads, that means 10 invoices will be automatically generated by the system
Each load consists of one or more shipments (CTs)
The part before uploading the spreadsheet to the system, it still involves manual process for JFS to prepare final data on the spreadsheet on local machine before uploading.
Once the spreadsheet is ready, JFS can upload the spreadsheet to the system and system can map the data from the uploaded spreadsheet.
After uploading spreadsheet, JFS should be able to review the info that was uploaded then ask system to generate invoices from the master.
Transmit to trendset via EDI

Requirements

see example about mapping from Trendset at note 0010498 from 2996
See attached file TRENDSET-EDI210.doc on the mantis 2996 for the schema
Additional info that need to map and submit to Trendset:

Invoice # from 210 map to Jaguar Invoice (sale invoice): Invoice Number ie. US20572
Pro# from 210 map to Jaguar Invoice number with an alpha Character appended to the end. ie. US20572a, US20572b, US 20572c...etc (each alpha character presents each load.
BOL# form 210 map to Jaguar load number

Step of invoice process

Provide an ability for user to upload the spreadsheet
- This is implemented on Trendset Tab Master level
Provide an ability for user to review the uploaded information in HTML format before user clicks "generate Sale Invoice"
- This is implemented on Trendset Tab Master level
- Show Gross weight in both KG and LB
- Value for Gross Weight, Freight Total, Fuel Total, Total MUST be 2 decimal
- For the CTs in the same load -> show one line with all CTs# listed.
Provide an ability for user to click "generate Invoices"
- This is implemented on Trendset Tab Master level
System generates invoices from the upload spreadsheet (one invoice per one load)
- System checks a format of spreadsheet as well as all required fields
- Invoices will be created by system - this can be view under the CT level on Inv tab or Master level on Profit/Loss tab
Provide "Transmit" button - EDI to Trendset
- This is implemented with ANSI standard code submit to Trendset via FTP
Note: Trucking Billing Manifest info consist of CT#, Load#, Ttl Plts, Ttl Pkgs, Ttl Loose, Gross Weight, Freight total, Fuel, Total, From, Origin City, Origin State, Origin Zip, Origin Country, To, Dest city, Dest State, Dest zip, and Dest country
Data send via EDI should include Invoice#, BOL#, and Pro#. System should generate these data from the condition above.

CT level --> Inv Tab

There is 2 more columns added to Invoice table:
- Sent to trendset - Check box (blank - not sent, checked - sent to trendset)
- Trendset status - Accepted or Rejected

Master level --> Profit/Loss Tab

There is 2 more columns added to Invoice table:
- Sent to trendset - Check box (blank - not sent, checked - sent to trendset)
- Trendset status - Accepted or Rejected

Re-upload spreadsheet in case of wrong file

[TBD]

E-mail notification to users when it passed/failed for transmitting to Trendset

see #E-mail Notification

Admin requirements:

Admin > Acc > Trendset --> manage config
- Criteria - what CTs are invoiced through Trendset
  - E0.Client company - This can be one or multiselected
  - Export Pick up Trucker - This can be one or multiselected
  - Bill to party - To address the invoices
  - Connection (FTP)
    - Accept Trendset Updates with ability for Trendset updates frequency in seconds
    - Send to and receive from (address, Login, Password)
  - Email Notification
    - Jag User - this can be more than one person (option to select the users who will receive notification)
      - dropdown: User name and email address
    - Notify users when:
      - invoice failed/rejected [Y/N]
      - Invoice passed/accepted [Y/N]

Issue credit note (TBD - for how to submit it to Trendset) - Need to confirm with MO first
Log of submit file

Who submits the file
Date and Time to submit the file
status of submitting the file

Additional Information

FTP folders

The invoices JFS send to Trendset is placed in the OUTBOUND folder
The 997 messages is placed in the INBOUND folder.
- Each 997 message received, it should be deleted to avoid confusion.
- 997 is a standard ANSI X12 997 V 4010.
- Frequency: The 997s should be available a couple hours after JFS send data.
  - Note: It can take up to 6 hours, depending on how busy our system can get.
- 997s are meant to be reconciled in our system back to what JFS sent Trendset. They don’t acknowledge the actual invoices, they acknowledge the transaction sets JFS has sent to Trendset. Trendset advised us below:

It’s a fine distinction, but there is a difference.

ISA*00*          *00*          *ZZ*TRN200115      *02*JAFR           *111021*1301*U*00401*000000005*0*T*>
GS*FA*TRN200115*JAFR*20111021*1301*0005*X*004010
ST*997*0005
AK1*IM*001  //acknowledging the GS control number you sent us
AK2*210*0001  //acknowledging the ST control number in the above reference GS control envelope
AK5*A  //status of transaction set
AK9*A*1*1*1  //Transaction sets received in the GS envelope acknowledged.
SE*6*0005
ST*997*0006
AK1*IM*001
AK2*210*0001
AK5*A
AK9*A*1*1*1
SE*6*0006
GE*2*0005
IEA*1*000000005

Per Lisa's suggestion: In order to use 997s effectively, we need to have our system increment the GS control number sequentially each time we send  
Trendset a file. (By rights, the  ISA control number should increment also, but Trendset is not doing sequence checking on that envelope, and it has no  
effect on the 997 sent back.)

Question to Trendset: if we submit the same invoice information twice or more than 2 times, will it cost anything on your end? Even though it is the same file. Your system will check whether it is a same file? We are implementing the submission ability for operator to click transmit to Trendset. However, if the file is failed of course we have to re-submit it.Please let us know. Thank you.

Answer from Trendset: When we load the file in the system, we look to see if the Pro# (the value you’re sending us in the B302) is already there. If the pro# is already there, the loader checks to see if the invoice is in reject status. If the invoice is already there, but not rejected, the loader will not load the second transmission of the pro#. If the invoice is already there, but has been rejected for whatever reason, the loader will load the 2nd transmission of the invoice and over-write the original one.
Conclusion: no harm on Trendset end

Test environment

Trendset ftp: ftp.trendsetinc.com
Login: jafrftp
Password: jafrtsi

Production environment

Step by step

Step 1 and Step 2

Step 3

Step 4 --> this it the process that will be needed to submit via the system (EDI)

Please note that system should provide ability to upload, download, review, and transmit option

Admin

E-mail Notification

Rejected Notification from Trendset

 From: cybertrax@jaguarfreight.com <System e-mail> 
 Sent: Friday, October 28, 2011, 5:44 PM
 To: montira@jaguarfreight.com <this is the operator who transmitted the invoices>
 Subject: Trendset Reject notification for invoice US22159 <invoice number>

 Dear Montira <this is the operator who transmitted the invoices>,

 Invoice US22159 <invoice number> was rejected. Please review and re-transmit it.

 Rejected: <code message- translate to understandable message for operator to  
 understand why it is rejected>

 To view this invoice online, please follow this link: US22159 <invoice# hyperlink>

 Please do not reply to this message as it is not an actively monitored e-mail   
 address. --> <bold and Italic>

 Kind Regards,

 EA USA Domestic Team
 at
 Jaguar Freight Services
 10 Fifth Street 3rd floor
 Valley Stream, NY 11581
 Tel:  +1 516-239-1900
 Fax: +1 516-239-6300
 www.jaguarfreight.com

 Jaguar Freight does not provide cargo marine insurance unless specifically 
 requested and confirmed in writing.

 Please note that all business is transacted subject to our general business terms 
 and conditions, a copy of which is available upon request.

Accepted Notification from Trendset

From: cybertrax@jaguarfreight.com <System e-mail> 
 Sent: Friday, October 28, 2011, 5:44 PM
 To: montira@jaguarfreight.com <this is the operator who transmitted the invoices>
 Subject: Trendset notification for invoice US22159 <invoice number>

 Dear Montira <this is the operator who transmitted the invoices>,

 Invoice US22159 <invoice number> was accepted

 To view this invoice online, please follow this link: US22159 <invoice# hyperlink>

 Please do not reply to this message as it is not an actively monitored e-mail   address. --> <bold and Italic>

 Kind Regards,

 EA USA Domestic Team
 at
 Jaguar Freight Services
 10 Fifth Street 3rd floor
 Valley Stream, NY 11581
 Tel:  +1 516-239-1900
 Fax: +1 516-239-6300
 www.jaguarfreight.com

 Jaguar Freight does not provide cargo marine insurance unless specifically requested and confirmed in writing.

 Please note that all business is transacted subject to our general business terms and conditions, a copy of which is available upon request.

History

See xls attached to http://ct.jaguarfreight.com/mantis/view.php?id=3042

Mantis

Translate message for content email (passed/failed) notification
See 3212
Spec is updated: Y

@@ Line 190: / Line 190: @@
 :* There is 2 more columns added to Invoice table:
 :** Sent to trendset - Check box (blank - not sent, checked - sent to trendset)
 :** Trendset status - Accepted or Rejected
+* Re-upload spreadsheet in case of wrong file
+:* [TBD]
 * E-mail notification to users when it passed/failed for transmitting to Trendset
 :* see [[#E-mail Notification]]