EDI with Trendset

From UG

Current revision as of 14:08, 31 October 2013

[edit] Info

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

Link to Requirements/SOWs: 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.

Trendset contact:

Freight Payment:

• carriersupport@trendsetinc.com

Implementation:

• lisa.shampine@trendsetinc.com

Payment Information

• http://www.trendsetcarriers.com

[edit] Requirements from Elizabeth Arden

[edit] Core requirements

Core requirements are summarized in the following letter from Arden.

[edit] 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

[edit] 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>

[edit] EDI TO TRENDSET

[edit] Business Process

[edit] 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)

[edit] 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

[edit] 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

• See #Invoice Mapping

• 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 submission file

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

[edit] 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)

• Mapping: CT_bo#Load_Number

• 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.

[edit] 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

Questions regard Trendset System, contact:

• carriersupport@trendsetinc.com // 864-527-4395

[edit] Test environment

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

[edit] Production environment

[edit] 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

[edit] Admin

[edit] E-mail Notification

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

[edit] 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.

[edit] 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.

[edit] SOW 1

0003250: [EDI with Trendset] Add validations and functionality to handle various invoicing errors (SOW1)

[edit] Add Validation: during the upload for Trucking Billing Manifest and reject the upload if found

• Freight Total and Fuel Total cannot be zero
• Load Number from the uploading spreadsheet cannot be duplicated with load number that has already transmitted to Trendset
• Load Number on Spreadsheet to upload MUST match with the actual Load number in the system (CT > GenTab >Load Number), not allow to upload if the load number on spreadsheet do not match what system has
• CTs to upload MUST be a part of that particular master
• Validate these columns on Manifest in order

• CT#
• Load#
• From
• Ttl Plts
• Ttl Pkgs on Plts
• Ttl Loose Pkgs
• Gross Weight
• Freight Total
• Fuel Total
• Total
• Origin City
• Origin State
• Origin Zip
• Origin Country
• To
• Dest City
• Dest State
• Destination Zip
• Destination Country

• Display Error pop-up message if validation is failed and reject the upload

[edit] Add Functionality to handle various invoicing errors

Use cases:

• CT was part of Master (Manifest) by mistake and was billed by mistake
• CT was omitted and has to be added to Master (Manifest) and billed
• Incorrect amounts posted for specific CTs
• CT was a part of the load and master then later the CT has been removed from the master and a different load has been assigned (case of a CT is a part of the load did not get picked up as it was supposed to)

• User has to issue credit note against that particular invoice

• System should automatically discover what CTs are in the load

Steps to handle use cases:

• Generate credit note invoice for the correct amount, load and CTs that are a part of the load and transmit to Trendset

• This will help on Trendset end to understand about the incorrect invoice and cancel it out
• Provide "Credit Note Issue" button
• Pop-up review read-only information for the credit note such as "specific charge code can be use", "container field contains load#", etc
• Provide ability for Auto and manual generate invoice and credit note
• Question: should we use the standard credit note issue process to receive an approval before it can be generated and transmitted?
  • Answer: yes. all credit notes either from manual or auto generates should still require approval process from the team leader (the current process we have for the credit note for manual CN generate)

• Generate invoice with correct CTs, load# and amount and send transmit to Trendset

• Provide ability for operator to generate invoice from a master Inv tab and transmit

• Remove CT from the master

• Removed CT should be removed from Trucking Billing Manifest review

NOTE:

• System should not allow to generate partial amount credit note as it can confuse Trendset

[edit] Change name "Trendset" to "Inv" tab

• This tab consists of:

• Upload revised Manifest
  • Manifest HTML view Table to review after the upload successfully (same as what currently have)
    • This should have all required information to make a decision about "Generate&Transmit"
• Merge Operation of "Generate" and "Transmit" invoices
  • This button should be locked once user clicks "Generate and Transmit" (Allow to click one time)
• Table of Invoice generated (Similar to invoice table in CT level)
• Ability to generate "Issue New Invoice" from master should be provided just for "Trendset Enabled Masters"
• Ability to generate/transmit/credit note invoice from CTs applies to CT level on Inv tab for only shipment that has "Trendset Enabled feature" with a fix template for specific charge code...etc

[edit] Remove "Issue Credit Note" button from P/L tab

• Keep P/L tab alone as a report
• Issue credit note should be provided under Inv tab in Master

[edit] Log

• All actions MUST be log on the CT update log , Master log, and system log

[edit] Additional information for Trendset Credit Note issue

• How Trendset will know which Credit Note issue against which invoice as CyberTrax generate Credit note with incremental number of invoice which is not the same as the original invoice number?

• Trendset can accept Credit note invoices as long as CyberTrax send a CA in the B308 for 210 mapping file, and all of the dollar amounts are negative

[edit] 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.

[edit] SOW 3

* 0003332: [EDI with Trendset] store files on our ftp for investigation needed

As Trendset takes the files from the FTP and empty it, the files are no longer with us

• Request to add an ability to store files that are transmitted to Trendset for future investigation need

[edit] SOW 4

* 0003363: [EDI with Trendset] Truck Dom: change mapping 210 from the ‘shipper’ and ‘consignee’ info to the ‘pick up’ and ‘deliver to’ location

• Do not change the mapping for the CyberTrax invoice (pdf) - keep the way it is
• Change the mapping on XML file to send to Trendset only, so that Trendset can show Pick-up and Delivery location on the invoice that they send to EA the way EA would like to see.
• Solution:

• Should be a solution that we can manage through admin so this can be easier to know what data we send to Trendset (mapping)
  • 1) Provide a checkbox/option for XML mapping

[ ] Pick up Location and Delivery Location to Shipper and Consignee(Trendset) for XML
[ ] Shipper and Consignee to Shipper and Consignee(Trendset) for XML

• • OR 2) Provide a dropdown list option for what to map to Shipper and Consignee for XML
  • OR other better solution, please advice and provide feedback

• Note to CT2Support team to update this information to help page

[edit] SOW 5

* 0003418: [EDI with Trendset] Sending the test invoices to receiver ID TRN200115TST

• Changing admin part for trendset test/production properties
• This information will be stored in application properties files
• In Trendset admin it will be in read-only mode

[edit] SOW 6

0003529: Mapping changes needed on 210

• Change the mapping on Ansi(XML) file we send to Trendset for weight
  • Grab and pass LB weight from from Invoice field
    • Kgs not needed for trucking domestic Invoices

[edit] SOW 7

0003540: Changes need for Mapping on 210, UI & BP - SOW 7

There was a request received from Trendset for Jaguar to change the R304 element we send to Trendset for all invoices that are being considered as a FTL (full trailer load) move, which is according to a specific business processes.

Being this specific BP is NOT captured anywhere in CT, the following changes were suggested to accommodate this change.

• After operator uploads trucking manifest and before they generate/transmit invoices to Trendset, provide them with someway to select the load(s) that were moved as FTL.
  • Operator can select 1 or more loads from the manifest that went FTL.

• If load is selected as moved FTL, then change mapping of the R304 element sent on our Ansi(XML) from LT to M

• If load is NOT selected as moved FTL, then no mapping changes needed and we send R304 element as LT

[edit] SOW 7 Solution

Changes are being made on UI, Inv Tab, after user uploads manifest, HTML view will now have check box option for user to manually set.

• Add new column named FTL

• Add check box option for user to manually check
  • Default to un-check

• No change for manifest needed
  • No change for system validation of upload of data needed, as there are no changes being made to the manifest

• If check box is set for any load send Ansi(XML) file's R304 element = M

• If check box is unset for any load send Ansi(XML) file R304 element = LT

[edit] UI Change IE

[edit] SOW 8

0003543: Change: Make FTL ck on UI from SOW 7 Read Only after user Generates & Transmits Invoice

While testing SOW7, something was noticed, that was not considered before hand, about the check boxes...After user sets the ck box to note that it was a FTL load and then transmits & generates the invoice, that ck box still stays checked and the user can uncheck the box. Or if they leave the master record & go back to view the inv tab, the value of if ck box set - is not found as the ck box is able to be set & unset.

[edit] Change need for Inv Tab Check Box after user sets values and generates & transmits invoices

• Check boxes should become read only and keep ck box set value.
  • If chec kbox wasn't checked, read only value = empty check box.
  • If chec kbox was checked, read only value = Checked check box.

• This change should happen after user transmits & generates invoices.

• When new/revised manifest is uploaded, check boxes should become editable again until invoice is generated & transmitted

[edit] SOW 9

0003578: Mapping changes needed on 210 for Trendsets B304 element = SOW 9

Current mapping of B304 = PP and this needs to changed according to Shipper/Pickup Location addresses - CC or PP.

• Change mapping on Ansi(XML) file we send to Trendset for B304 element (Shipment Method of Payment):
  • If shipper (pickup location) = Elizabeth Arden, send B304 = PP, else send B304 = CC
  • Making the default B304 element = CC

[edit] SOW 9 Solution

Based upon EA not yet to advise which of their shipping addresses are to have B304 = PP, it was agreed to make a setting in Admin > Acc >> Trendset Admin. Here the user(s) can set and select which Elizabeth Arden shipper (aka pickup location) address, is to have shipment method of payment(B304) = PP. Than, any/all other Ansi files will send B304 = CC.

[edit] SOW 10

0003498:System rejects file upload due to mismatch info and user has to go to manually update record with info provided by system.

• Upon system validating columns on trucking manifest at the moment of upload and reject it once mismatch info is found. System must show detail for the mismatch info between the manifest and the system with Load and CT number. See Reject: Mismatch info is found
• System must display Error pop-up message if validation is failed and rejected the upload for which info is not matching along with load and CT number to help user to update record(s) accordingly without trying to search what info that is mismatch.
• There are 2 scenarios that can happen that can cause mismatch information:

• The shipper incorrectly entering the packaging details at time of record creation.
• The packing details change after the record is approved - IE. weight increases or decreases.
• Upon those 2 scenarios, solution will help when:
  • System provides mismatched detail between system and uploaded manifest so that:
    • User can manually update records that are mismatched if info from the manifest is up to date for correction.
    • User can re-create a new manifest and revise then upload
  • Additional feature for updating information is available to auto-update:
    • Provide functionality to update system with the new information for particular fields to ask user:
      • Would you like to update these mismatch information to the system? [Y][N] with detail provided. This would be only for the packaging details:
        • # of pallets
        • # Loose cartons
        • Weight
    • This feature should allow only an authorized user(s).
  • Admin for managing authorized user(s) is needed. This can be done under Trendset admin

[edit] SOW 11 Transmit Loading charge invoice to Trendset

Mantis: 3824

Elizabeth Arden requests to submit Loading charges via EDI to trendset.

[edit] Current Process

Operator transmits Freight and Fuel charges to Trendset by EDI. Other charges are created manually from CT2 and submit to Elizabeth Arden by Mail/Fax.

Each day, Jewels will send manifest spreadsheet(s) and email list of shipments to EA DOM team. All shipments that were moved to Roanoke VA by Jewels, lists will be emailed to Jaguar called Manifest (consolidation sheets) and the other list of shipments that were moved elsewhere (tri-states area) called POD. See example of manifest and POD below.

[edit] Manifest: Shipments that were moved to Roanoke, VA

One sheet per truck, it consists of a list of shipments that were moved in the same truck with a trailer ID. If there are 2 trucks that moved shipments to VA on one day then Jewels will send 2 manifest sheets to Jaguar with 2 different trailer ID. See example below. Each sheet describes which shipments were moved in a truck. Loading charges apply to these shipments 250 USD per Truck. These CTs are a part of one master. For this master, we will bill to EA for 2 trucks as a lump sum 500 USD and submit paper invoice to EA by mail/fax. See method of current billing for this charges below.

• Consolidate shipments (Sheet 1)

• Consolidate shipments (Sheet 2)

[edit] POD: List of shipments that were moved in tri-states area

Jewels will email a list of CT records that were moved in tri-states area. Loading charges are not applied to this list. These CT records are a part of one master that can be the same master as the ones that moved to Roanoke, VA. These CT records, we do not invoice EA for loading charges.

[edit] Method of billing for Loading Charges

Operators will choose one CT record from the list on the manifest sheets (in the same master number) and create an invoice for loading charges; this CT is a part of a master and this charge will be invoice under one CT for charge code: 1601 and description describes number of trucks such as 2 trucks on what day (Date) and USD currency and cost USD. Each Truck cost 250, so 2 trucks will be 500 USD on one line of charge coded. See example below.

Example: Selecting a CT number from the list on the manifest sheets. Create an invoice from the CT record on CyberTrax. From invoice below, the information from that particular CT record was derived from the system: File Ref, CT Ref, P.O. Numbers, Shipper, Consignee, Packages with pallets, cartons on plts, and cartons (loose), Weight/cube in kg(lb), cbm (cft). Charge code # is an input with description entered USD amount. See example below.

Notes: One master consists of CT records that are moved to Roanoke, VA and to tri-states area. So loading charges are not applied to the ones that are moved in tri-states area. Only apply to the CT records that are moved to VA. Manifest sheets are described the list of CT records that were moved to Roanoke, VA and got charged for the loading charges for a particular master.

Cons: For his method for loading charges billing - invoice for the current process:

• There is no way to trace back to all CT records in the system that are a part of this specific charges as it does not list what CT records are a part of this shipment movement to Roanoke. Only one way to trace the charges for what shipments that moved in these trucks is from the manifest sheets that Jewels sent to Jaguar.

• This invoice only lists the charges for how many trucks and on what day.
• This invoice was created from one of the CTs with that specific info for that particular CT record. Shipper and consignee are not JEWELS and EA Roanoke. In order to submit invoice to Trendset for this charge for correct shipper, consignee, total of pallets, weights with charges for number of trucks and date we will have to re-design the method of submitting information to Trendset as well as in order to trace the cost back to shipment records in the system. List of CT records are for internal not for client. This will benefit Jaguar in term of audit. See new process below.

[edit] New Process

[edit] Submit to Trendset

Elizabeth Arden requests to submit an invoice for loading charges as a lump sum (the same way that we manually submitted before). The only changes are to submit this charges to Trendset via EDI with requirement below:

• Shipper: Jewels (not hardcode)
• Consignee = Elizabeth Arden (not hardcode)
• Total of pallets loaded = total of pallets loaded (calculated from all CT records that are a part of this loading charges)

• This is mapped from PACKAGES field on the invoice: Pallets: Cartons on plts:, Cartons (loose):

• Total number of pounds (LBS)

• This is mapped from WEIGHT/CUBE field on the invoice: Kg (lb), cbm (cft)

• Loading charges as a lump sum cost for all trucks for that master on the same day.

• on the invoice use: CC# 1601 Description: Loading Charges X trucks mm-dd-yr in USD currency and amount with sub total and total invoice values

• No need to submit CT reference, PO Number, Load number...etc.

[edit] Solutions

Solution 1:

• create an invoice from a master
• have ability to select shipper, consignee
• have ability to tag CT number for this charges and calculate ttl of pallets, cartons, loose, weight, etc
• submit required info to Trendset
• display File ref (choose 1), CT Ref (to fit in), PO Number (to fit in), Shipper = Jewels(shipper), Consignee = EA Roanoke (consignee), Total packages, total weights, CC, Description, cost, sub-total, total invoice

• • Question: File ref - same for all CTs in one master?

• Comment: list CT number somewhere on the invoice?
• This invoice will appear under the master as well as each CT that is a part of this charges.

Solution 2:

• create an invoice from a CT that JG created for the truck only
• The info for total pkg and weight are entered by Jaguar operator on the cont Tab - derived from here
• no way to list CTs that are a part of this loading charges at the moment, we may need to create ability to list CTs on comment? as this CT does not attach to any master - no way for system to list CT unless we provide ability somehow.
• Need to provide ability to select consignee and shipper if it is different for billing.
• submit to trendset for the required info
• Question: is this CT set for file ref-> for gms? File ref is mandatory but I don't see that user set for this record?

Solution 3: --> This solution is selected to be implemented.

• Operator manually creates a CT record for a Truck shipment through shipper portal with PO Issued by "Test". Operator created a shipment as a shipper
• Operator manually approves a CT record for a Truck shipment through planner portal (all PO Issued by is Test, that is all CT records for the truck itself.
• CT record (truck CT) is sent to TMS for operator to tender and route it to carrier (truck company)
• Trucker will receive it as this is a message that Jaguar requests for pick up this consolidated shipment at the warehouse and deliver to the destination (Shipper and consignee addresses).
• Trucker goes to pick up the shipments and collect all the consolidated shipments together with CT numbers and send the list of CT numbers with trailer ID (truck ID) information to Jaguar operator via email. See spreadsheet from #Manifest: Shipments that were moved to Roanoke, VA for consolidated shipments. Each Spreadsheet is for consolidated CT in one truck. If trucker sends more than 1 spreadsheets for the same days that means there is more than 1 truck that moved shipments with more than 1 consolidated shipments.
• Manifest spreadsheet that trucker sent to Jaguar will list total of quantity, total of packages, etc. Jaguar operator will update the truck CT accordingly on Cont Tab.

• Operator will enter the date of shipment and trailer ID in PO field.
• Operator will enter the "Consol" in SKU field
• Operator will enter the date of shipment on Item Description as well
• Operator will update quantity, Ttl # of plts, Ttl pkgs on plts, Loose pkgs,Ttl GW(LB) per info that was received from the trucker

• Group CTs that were created for Truck together. Each CT is for a truck not for shipments details.

• Note: This CT has been created to use for a truck not for shipments detail. This has information for shipper, consignee, as well as total packages, total weights that were move on this CT (truck). This information can be used and to submit to Trendset

• Create a master from the truck CTs grouping with ability to flag as a truck master in order to use Loading charges billing functionality.

• Flag can be on Master General Tab: check box with label: Loading Charges Invoices

• To submit information to Trendset for loading charges per truck with amount of Loading charge per CT per truck: 250 per truck

• Functionality should allows operator to enter number of trucks and how much per truck.
• System is calculating number of truck multiply amount per truck for the total loading charges to send to Trendset
• Most situation are 1 Truck CT, 1 truck, 1 shipper, 1 consignee,1 load number 250 USD
• Some situation is more than 1 Truck CT (ie. 2 Truck CTs) that are moved in 1 same truck but delivered to different locations (different consignees) with the same load number 250 USD
  • 

• Create spreadsheet or display html and derive information from the Truck CT with shipper, consignee, total packages, weights, Etc on the master for loading charges invoices.
• Upload spreadsheet or review info from HTML

• Ability to edit amount of loading charge per truck but default value display 250 USD per truck.
• Default value can be set on the admin >Acc> Trendset admin > Loading Charge: <250>

• Ability to generate invoices all at once with transmit to trendset
• Ability to generate and transmit all invoices to Trendset at once from the master. Provide a button to submit.
• Ability to create credit note invoice and resubmit invoice - same as current functionality.
• Ability to enter the date of truck move on the invoice
• Submit pdf/image to trendset via EDI (FTP)

• Trendset created a folder for images separately from the outbound folder for the files

• Mapping information:

• Create Bill to party: manage in admin for loading charges invoice
  • Controler header (ISA and GS segments)
  • Shipment heading
  • Shipment Detail
  • Shipment Summary
  • Control Trailer
  • See sample from Lisa below

[edit] EDI

Template below is from Lisa (Trendset)

Montira:

Based on our discussions here, we’re going to ask you to send these as an accessorial only invoice. Example is below:

ISA*00*          *00*          *02*JAFR           *ZZ*TRN200115TST   *121020*0935*U*00400*000007318*0*T*>
GS*IM*JAFR*TRN200115TST*20121020*0935*7318*X*004010
ST*210*073180001
B3**163457TEST*163457TEST*CC*L*20121020*100000**20121018*035*JAFR*20121018
G62*11*20121018
G62*35*20121018
R3*JAFR*O**M
N1*SH*JEWELS TRANSPORTATION INC.
N3*241 ORATON STREET
N4*NEWARK*NJ*07102
N1*CN*ELIZABETH ARDEN LOGISTICS
N3*1751 BLUE HILLS DRIVE
N4*ROANOKE*VA*24012
N1*BT*ELIZABETH ARDEN LOGISTICS*ZZ*ELIZ-01
N3*1751 BLUE HILLS DRIVE
N4*ROANOKE*VA*24012
LX*1
L5*1*LOADING CHARGE
L0*1*4*LD*****4*PCS                                //Send the number of loading charges that is included in the amount in the L104
L1*1*25000*PL*100000****LDG             //Send the total amount of loading charges you’re charging.      
L3*****100000******4*L
SE*20*073180001
GE*1*7318
IEA*1*000007318

Use your test ID and send me some samples, so we can have EA review.

Thanks!

Lisa Shampine

Additional requirement from Trendset 1

• Send weights as below:

• Send a weight in the invoice. That weight is 1 if actual weight is empty/zero. It will be sent in the L004 with the qualifier G in the L005.
• It will also need to be repeated in the L301 with the qualifier in the L302. Again, the weight should default to 1 in order for us to rate these.
• The qualifier for this invoice (loading charges) can be "G" for all scenarios.
  • NOTE: Send actual weight if there is any. If there is an actual weight, we need to send that. If for some reason we don’t have a weight (since these are accessorial only), please enter a weight of 1.

Weight = 20 -->

L0*1***20.0*G****PCS****~

L3*20.0*G***25000*******1*L~


Weight = 0 -->

L0*1***1.0*G****PCS****~

L3*1.0*G***25000*******1*L~

• Send N9 segment as well. Trendset will load this number to the invoice# field (N901 and N902).

Additional requirement from Trendset 2

• Send quantity on the Loading Charges invoice. This represents how many trucks are being charged loading charges.
• Send this in the L008 with the qualifier of TRU or PCS (either one is acceptable.)

L0*1***948.01*G***2*TRU****~

• See email communication at: 3824

[edit] Invoice in CT2

[edit] Questions

1) what will affect to the P/L :

• Answer: P/L functionality continues working the same no problems - Marc

2) what will affect to Acc reports?

• Answer: Acc functionality continues working the same no problems - Marc

3) what will affect the GM split?

• Answer: GM functionality continues working the same no problems - Marc

4) how to show this invoice? to all CTs that are applied to this charge?

• Answer: We choose solution 3, so each invoice will be per truck as we create 1 CT per truck. And this can be submitted similar to Freight and Fuel by uploading spreadsheet with info and cost per Truck. Jaguar will group CTs for each truck in the same day together (VA) then create a master. Invoice can be generated the same way as manifest for freight and fuel but information to upload to the system and submit to trendset is different. But this functionality can be done from Misc tab from the master.

5) Create this invoice from Master - cost any problem for operator and Acc? as we do not have to create from one CT any more, we can create from Misc tab and able to select CTs that are a part of loading charges

6) This invoice will appear to all CTs that are a part of this charges

[edit] SOW 12 EDI Plus for Elizabeth Arden

0003870: [EDI with Trendset] SOW 12 EDI Plus for Elizabeth Arden

Mantis: 3870

[edit] Business Requirement

Elizabeth Arden request Jaguar to participate in EDI plus program to Trendset as on behalf of Trendset mutual client, Elizabeth Arden

EDI Plus is where, in addition to sending invoices to Trendset via EDI (as we are currently), we would send images of our invoice and backup documents as either .tif or .pdf documents. to Trendset extended from the ANSI X.12 210. This is an addition to the current process, not a replacement.

• Process: In additional to current process, we will supplement our EDI invoice submission by sending the invoice and backup docs as either a.tif or a.pdf document as well as the ANSI X.12 210. We will submit the created invoice pdf file through our existing ftp connection. This will allow Trendest to match the pdf file to the EDI invoice in the system in a timely fashion.
• Ability to control time to submit pdf file such as within 24 hours of sending the EDI. This can be done in Admin >Acc >Trendset admin.
• NOTE: For pdf files submission, test files and real files are in the same ftp folder, this can cause some confusion.

• Solution: Trendset created a TESTIMAGES folder for test pdf files only so that would stop test files from flowing into Trendset system and trying to match against invoices that aren’t there. Images folder for live pdf files on production.

[edit] SOW 13 Send indicator to R310 segment of NC for $0.02 as No Charge Invs

Mantis: 3957

Issue: Invoice cannot be created with zero amount. However, there is some invoice that we need to bill for zero amount but system does not allow this. So EA DOM team has been billed for $0.02 amount for invoices with Freight for $0.01 and Fuel for $0.01. This has no way for EA to know whether they need to pay or not. However, we need to implement a functionality to identify this amount invoice to let Trendset know EA does not have to pay this charge. This also will cause some issue with P/L. This needs to be revisited for the requirement.

IDEA/Issue/question:

• Create a check box to indicate No Charge invoice
• Send indicator to R310 segment of NC for $0.02 as No Charge Invs
• This will cause issue for P/L internally. This needs to be asked what to do.
• Can we not create this invoice from the first place???? EA DOM Team needs to clarify this.

[edit] Solution Architect Notes

[edit] Add validation: Freight Total and Fuel Total Can not be 0

Validate these columns on manifest at the moment of upload and reject upload if found

[edit] Add functionality to handle various invoicing errors

Use cases:

• CT was part of Master (Manifest) by mistake and was billed by mistake
• CT was omitted and has to be added to Master (Manifest) and billed
• Incorrect amounts posted for specific CTs

[edit] Merge operation of "Generate" and "Transmit" (invoices)

There is no reason to have a gap in time between these 2 operations. If better review is needed after or before "Generate Inv&Transmit from Manifest" is pressed then system can provide info in more convenient form. But it is believed that Manifest HTML view has all required info to make a decision about "Generate&Transmit".

[edit] Solution 1

Do not allow to re-upload manifests after invoices has been generated.

• Use standard Inv/CN capability
  • Credit note functionality should be standard to allow for partial credit and editing TOC
  • Ability to create invoices from Master should be added (just for "Trendset enabled Masters")
  • Ability to create/transmit invoices/cr notes from CTs should be added (just for "Trendset enabled Masters") OPTIONAL-NEED MORE RESEARCH
  • For uniformity rename "Trendset" tab into "Inv" tab
  • Trendset invoices should comply with "Trendset format" such as "only specific CC could be used", "CONTAINER field contains load#"

• Allow "Generate Inv&Transmit from Manifest" only one time. All errors should be fixed by using standard Inv/CN functionality
  • move issue Cr Note button from Master P/L tab to Master Inv tab

• Involve Sa who dev Inv/CN

[edit] Solution 2

Allow to re-upload manifests after invoices has been generated.

Let operator upload revised Manifest. System should be smart enough to analyze and discover what CNs, Invs to issue and will issue them automatically. Must first post it into "Preview screen".

[edit] Solution 3

Allow to re-upload manifests after invoices has been generated.

Create another non standard but convenient interface to handle invoicing errors.

[edit] History

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

[edit] Mantis

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

[edit] Mantis

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

[edit] Mantis

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

[edit] 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

[edit] Mantis

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

[edit] Mantis

• Send Freight total and Fuel Total in separated lines instead of one total amount SOW 2
• See 3296
• Spec is update: Y

[edit] Mantis

• store files on our ftp for investigation needed SOW 3
• See 3332
• Spec is updated: Y

[edit] Mantis

• Send Pick-up and Delivery location in shipper and consignee mapping 210 in the file ONLY to Trendset.SOW 4

• CyberTrax- invoice mapping for shipper consignee stay the same!

• See 3363
• Spec is update: Y

[edit] Mantis

• Sending the test invoices to receiver ID TRN200115TST
• See 3418
• Spec is updated: Y

[edit] User Guide

[edit] Section 1

• User will create a master with all the CTs that delivered on the same day in one master
• User will create a spreadsheet that include CTs in the master with Rate Fuel Min Max value calculated by generating it from Master>Misc tab. See #Figure 1. Generate Spreadsheet from Misc tab in Master Level

[edit] Figure 1. Generate Spreadsheet from Misc tab in Master Level

• 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

[edit] Figure 2. Final Spreadsheet

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

• Once the spreadsheet has been uploaded, it will display in HTML format for users to review the value. See #Figure 3. HTML view for upload value

[edit] Figure 3. HTML view for upload value

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

[edit] Section 2

[edit] User Guide Status and SOW covered

• Up to date? N
• SOW Covered: SOW X