EDI with Trendset

From UG

Revision as of 21:34, 20 December 2011 by Alex (Talk | contribs)
Jump to: navigation, search


Contents

Info

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

Link to SOW: EDI with Trendset

Link to User Guide: UG:Trendset_USER_GUIDE

Mantis category: Acc:Trendset

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

Scope:

This wiki covers Trendset EDI project.

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
  • Ability for upload, generate invoices, credit notes issue, and transmit invoices to trendset (Trendset Location)
    • Trendset tab should be available for only Master that use this feature
  • 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.
    • Show Total number of Freight Total, Fuel Total, and Total amount as these info can be easily review before generating invoices
    • Each column can be sortable decending/ascending
    • View result - default should be descending for load number
    • Add Hyperlink for every CT# to link to CT# edit page
  • 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
    • These 2 columns should only appear on those tabs for the shipments that are submitted through trendset

It should show those 2 for only shipment that submitted throught trendset.

  • Invoice
  • Re-upload spreadsheet in case of wrong file
  • [TBD]
  • E-mail notification to users when it passed/failed for transmitting to Trendset
  • 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 submission file
  • Who submits the file
  • Date and Time to submit the file
  • status of submitting the file

Invoice Mapping

  • Mapping for automatically invoices creation
  • Overall mapping is the same as usual manually invoice generation
  • Bill to party
  • Control this from Admin for the bill to party address
  • File Ref
  • Mapping: CT_bo#Import_Ref
  • Import Ref is taken from one of load's shipments as same load number = same Import Ref#
  • Container No(s)
  • CC#, Description,Currency, Amount
  • Mapping 1st line: 1505 (CC) Domestic Trucking (Desc) USD (currency) Amount (number)
  • Mapping 2nd line: 1506 (CC) Trucking Fuel Surcharge (Desc) USD (currency) Amount (number)
  • Sub-Total
  • calculation result (formula)
  • Total invoice
  • calculation result (formula)
  • Invoice amount should be consistent between the number on spreadsheet and on the Profit and Loss as well as invoices.

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

File:Step1and2.JPG

Step 3

File:Step3.JPG

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

File:SystemAutomateInv.JPG


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

Admin

File:TrendsetAdmin.JPG

E-mail Notification

  • Should be done in Config so the change can be made with out the release required.

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.

 To view this invoice online, please follow this link: US22159 <invoice# hyperlink>
 This invoice is for the load number: LD000000000234 <load number> 
 This load consists of CT11111, CT11112, CT11113 <CTs number hyperlinks>


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


 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 Accept 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>
 This invoice is for the load number: LD000000000234 <load number>
 This load consists of CT11111, CT11112, CT11113 <CTs hyperlinks>


 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.


SOW 1

  • Ability for system to automatically produce CR to against the sales invoices that system automatically generated and already transmitted to Trendset
  • Button on Master P/L tab
  • This button is only affected only for invoices that transmitted through EDI to Trendset
  • Ability for users/operators to tell the system to generate the CR for one, few or CR for all invoices
  • Ability for users/operators to revise and reupload the correct spreadsheet with correct value/cost and regenerate invoice(s)
  • Spreadsheet with all loads to reupload (system will check for the duplication)
  • Spreadsheet with the loads that are needed to re-generate invoices for the correct value/cost
  • This will affect both Internal P/L balance as well as Trendset receive balance (As right now it is the credit note that is done manually is not affected CR to Trendset, this could cause the problem for P/L balance to be zero but the Trendset does not know the CR.
  • Use cases
  • 1) Upload incorrect spreadsheet before generating invoices --> Revise and upload
  • 2) Upload incorrect spreadsheet and generate incorrect invoices --> provide abilities to:
    • Generate CR and Regenerate invoices to transmit to Trendset
      • One,a few, or all invoices to make the balance to zero before transmit new fresh invoices so that the wrong invoices will not be submitted
      • Ability for users to upload the whole new spreadsheet that include correct value as well or to upload only the new adjust value for the invoices that has CR issuing.
      • Transmit button: transmit all invoices that do not have credit note
  • 3) Upload incorrect spreadsheet and generate incorrect invoices and transmit incorrect invoices
    • Ability to generate CR with new invoices to transmit to Trendset
  • CR: how Trendset will know which CR against which invoices
  • TBD - mapping solution

SOW 2

* 0003296: [EDI with Trendset] Send Freight total and Fuel Total in separated lines instead of one total amount

Change Request for Transaction Set 210, Motor Carrier Freight Details:

  • Sending Freight and Fuel amount separately to be able to display on Trendset end invoices submit to Elizabeth Arden
  • Add LT in the R304 for the LTL mode(Trucking): R3*JAFR*O**LT
  • This will help Trendset to rate correctly

Mapping to:

  • Freight goes on 1 line item; it does not require a charge code.The code for Freight is 400.
  • Fuel goes on a separate line item; the charge code (which goes in the L108) will be FUE.

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

Mantis

  • Credit Note (Master) - All invoices in Master
  • See 3211
  • Spec is updated: N

Mantis

  • Hyperlink for CTs# on View upload
  • See 3224
  • Spec is updated: N

Mantis

  • Each column should be sortable (Descending and ascending)and view result - default should be descending for load number
  • See 3243
  • Spec is updated: Y

Mantis

  • Invoice: Maping File Ref# field on invoice with GenTab>B.Import Ref#.
  • See 3248
  • Spec is updated: Y

User Guide

Section 1

Figure 1. Generate Spreadsheet from Misc tab in Master Level

File:TruckBillManifestForMaster.JPG

  • User checks value calculation and submit to Jewels Transportation to review all the rates.
  • If the number is incorrect, adjust and resend to Jewels Transportation to sign off/approve on it
  • Jewels approves then user will reuse the spreadsheet and mark up the rate in order to bill EA
  • Remove the Rate and Fuel columns before submit it to EA. See #Figure 2. Final Spreadsheet

Figure 2. Final Spreadsheet

File:UploadButton.JPG

  • Once user clicks "Upload Revised Manifest" then clicks "Brownse" for a file to upload then clicks "Upload"

File:Upload.JPG

Figure 3. HTML view for upload value

File:ViewHTML.JPG

  • Once reviews and everything looks good then clicks "Generate Invoices"

Section 2

User Guide Status and SOW covered

  • Up to date? N
  • SOW Covered: SOW X
Retrieved from "https://wiki.jaguarfreight.com/EDI_with_Trendset"
Personal tools