|
|
(55 intermediate revisions not shown) |
Line 1: |
Line 1: |
- | [[Category:New Arden Trucking Project]] | + | [[Category:NATP Billing]] |
| + | |
| + | == General == |
| + | |
| + | * parent: http://mantis.jaguarfreight.com/mantis/view.php?id=1884 |
| | | |
| == Business Requirements == | | == Business Requirements == |
Line 5: |
Line 9: |
| See [[New Arden Trucking Project NATP (main)#Billing]] | | See [[New Arden Trucking Project NATP (main)#Billing]] |
| | | |
- | Trucking Billing Manifest for Virtual Group is a spreadsheet that is generated based on pick-up or delivery trucker. It will be an option to generate the billing, edit and send to the client or vendor.
| + | == Specs == |
| | | |
- | == Location ==
| + | To add this feature you need to add 2 components: |
| + | * [[#Wizard Pop Up]] |
| + | * [[#Trucking Billing Manifest for NATP]] |
| | | |
- | * Misc Tab in [[Master Trucking]].
| |
| | | |
- | '''NOTE:''' Billing can be generated after defining Truck virtual group from [http://mantis.jaguarfreight.com/wiki/Virtual_Groups_for_NATP Virtual Group] and assigning virtual group to group and master. Then user can generate billing based on pick-up truckers or delivery truckers
| + | === Wizard Pop Up === |
| | | |
- | [[Category: Trucking Billing Manifest (M) (xls)]] | + | After user selects billing option on [[Virtual_Groups_for_NATP#General_Wizard_Step_3]] system will: |
| | | |
- | == Example and Typical Workflow == | + | * create a [[CT Group]] out of all CTs in Virtual Group |
| + | ** MOT = Truck Domestic |
| + | ** Client = Client from Virtual Grp classifiers |
| + | * Create Trucking Master |
| + | * Add [[CT Group]] above to the Master |
| + | * present pop up as on [[#Figure 1. Assign GRP and MASTER]] |
| | | |
- | * User creates a virtual group by classifiers
| + | === Trucking Billing Manifest for NATP === |
- | * User reviews the result then click next
| + | |
- | * User selects '''Generate Billing on Pick up Trucker''' or '''Generate Billing on Delivery Trucker''' options from the menu
| + | |
- | * User clicks '''Apply'''
| + | |
- | * Pop-up window shows Group#XXXX has been generated and Master#XXXX has been generated
| + | |
- | * User clicks '''Ok'''
| + | |
- | * User will go to Master# record from quicklink or ListMs to generate Billing Manifest
| + | |
- | * User navigates to Master> Misc tab
| + | |
- | * User generates Billing Manifest
| + | |
- | * System brings up model window where user may select any subset of CTs that belong to the virtual group. See example on [[#Figure 1.]]
| + | |
- | * User selects some CTs or all CTs
| + | |
- | * User completes the form and clicks "Generate Report" button.
| + | |
- | * System generates spreadsheet and opens it to the user. See example on [[]] and [[]]
| + | |
- | ** See additional information about spreadsheet here: [[#Spreadsheet]]
| + | |
- | === User Interface === | + | |
| | | |
- | See [[#Figure 1. Assign GRP and MASTER ]] for pop-up window. This window is showing once user selected generating billing.
| + | Master Trucking needs additional tab: NATP. |
| | | |
- | System assigned a group and Master automatically once user clicks ''Generate Billing on Pick up Trucker'' or ''Generate Billing on Delivery Trucker''
| + | See [[#Figure 2. Billing for Virtual Group]] |
| | | |
- | See [[#Figure 2. Billing for Virtual Group]] for viewing CTs below
| + | This tab has same purpose as Misc tab - to generate Trucking Billing Manifest. The difference is in how it is done and output design. Overall one is just a variation of another. |
| | | |
- | This is the HTML template from Misc Tab under a Master
| + | See what we have now on Misc tab here: [[Trucking Billing Manifest (Developer Guide)]] |
| | | |
- | === Template === | + | ==== Template ==== |
- | * '''Selected Shipment''' - [[checkbox]], default is ''uncheck''
| + | |
- | * '''Extra columns''' - [[listbox]], default value is 0
| + | |
- | * '''Reset Button'''
| + | |
- | *
| + | |
| | | |
- | Additional information:
| + | NATP template: |
| + | * has CT/GRP list as Misc does |
| + | * does not have any other field present on Misc except "Extra Columns". Reason: System will produce xls with default values (as defined for Misc) and user will edit on xls |
| + | * has additional (not present on Misc) controls/choices - see [[#Generate For Choices]] and [[#Rate Fuel Choices]] |
| | | |
- | * '''Extra columns''' - [[listbox]], default value is 0, possible values: {0,1,2,3}
| + | ==== Xls ==== |
| | | |
- | === Spreadsheet ===
| + | It consists of same components as xls for Misc but there are some important differences: |
| + | * Section 1 and 2 are now located on same tab not 2 different tabs as for Misc |
| + | * user has some choices of layout - see [[#Generate For Choices]] and [[#Rate Fuel Choices]] |
| | | |
- | It is groupped by Truck company per tab - see below.
| + | ==== Rate Fuel Choices ==== |
| | | |
- | === Hard Coded Conditions ===
| + | * per Trucker |
| + | ** this is how it is done on Misc |
| + | ** in this case system creates one set of parameters (rates/fuel) per tab |
| + | ** see [[#Figure 3. Rate fuel per trucker (one trucker per spreadsheet)]] |
| | | |
- | Based on Pick-up Trucker
| + | * per unique origin / destination pair |
| + | ** new idea |
| + | ** for Section 2 system creates as many groups as there are unique origin destination pairs (with its own set of parameters (rates/fuel)) per tab |
| + | ** business reason: rates could be defined by origin/destination in the future for some companies |
| + | ** see [[#Figure 4. Rate fuel per unique origin and destination pair]] |
| | | |
- | * This billing is generated based on Pick-up Trucker ONLY
| + | ==== Generate For Choices ==== |
- | * Consider Trucker companies that handle the loads from pick up location to warehouse
| + | |
| | | |
- | Based on Delivery Trucker
| + | * All Pick Up Truckers |
- | | + | ** system must group all selected CTs per [[CT#Pick Up Trucker]] and display each group on a separate xls tab. Tab is to be named after given Trucker |
- | * This billing is generated based on Delivery Trucker ONLY | + | * All Delivery Truckers |
- | * Consider Trucker companies that handle the loads from warehouse to destination | + | ** same idea as above |
- | | + | * Selected Pick Up Trucker |
- | === Spreadsheet ===
| + | ** on template system produces dropdown with all unique truckers across selected CTs |
- | | + | ** user may select one |
- | See Example on Figure 3.
| + | ** xls has only CTs that have selected trucker |
- | | + | * Selected Delivery Trucker |
- | * This is a table. | + | ** same idea as above |
- | | + | |
- | * Horizontally:
| + | |
- | ** it consists of as many sections as there are CTs (originally selected by user)
| + | |
- | ** each CT section has as many lines as there are in commod table for this CT
| + | |
- | ** footer: print totals
| + | |
- | | + | |
- | * Vertically it has a number of columns and each line has the following fields - See column definitions below.
| + | |
- | ** '''CT#'''
| + | |
- | *** Mapping: [[Ct#CT_Num]]
| + | |
- | ** '''From'''
| + | |
- | *** Mapping: ''Company name'' from [[Ct#Export_Pick_Up]] If [[Ct#Export_Pick_Up]] is empty THEN Map [[Ct#Shipper]]
| + | |
- | *** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | ** '''Origin City'''
| + | |
- | *** Mapping: ''City'' from [[Ct#Export_Pick_Up]] If [[Ct#Export_Pick_Up]] is empty THEN Map [[Ct#Shipper]]
| + | |
- | *** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | ** '''Origin State'''
| + | |
- | *** Mapping: ''State'' from [[Ct#Export_Pick_Up]] If [[Ct#Export_Pick_Up]] is empty THEN Map [[Ct#Shipper]]
| + | |
- | *** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | ** '''To'''
| + | |
- | *** Mapping: ''Company name'' from[[Ct#Export_Delivery_To]] If [[Ct#Export_Delivery_To]] is empty THEN Map [[Ct#Consignee]]
| + | |
- | *** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | ** '''Destination City'''
| + | |
- | *** Mapping: ''City'' from [[Ct#Export_Delivery_To]] If [[Ct#Export_Delivery_To]] is empty THEN Map [[Ct#Consignee]]
| + | |
- | *** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | ** '''Destination State'''
| + | |
- | *** Mapping: ''State'' from [[Ct#Export_Delivery_To]] If [[Ct#Export_Delivery_To]] is empty THEN Map [[Ct#Consignee]]
| + | |
- | *** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | ** '''P.O.'''
| + | |
- | *** Mapping: [[Commodity#PO]]
| + | |
- | ** '''SKU'''
| + | |
- | *** Mapping: [[Commodity#SKU]]
| + | |
- | ** '''Actual Pick Up Date'''
| + | |
- | *** Mapping: [[CT#Actual Export Pick Up Date]]
| + | |
- | *** Note: for Truck-Ocean this date is on [[Container]]; and since each [[Commodity]] line is assigned to its own container - date would be shown for every spreadsheet line for such CT
| + | |
- | **'''Actual Delivery Date''' | + | |
- | *** Mapping: [[CT#Actual Delivery Date]] | + | |
- | *** Note: for Truck-Ocean this date is on [[Container]]; and since each [[Commodity]] line is assigned to its own container - date would be shown for every spreadsheet line for such CT | + | |
- | | + | |
- | | + | |
- | | + | |
- | ** Sorted by: CT# | + | |
- | | + | |
- | * Spreadsheet:Billing Section (Section 2)
| + | |
- | ** See Example on Figure 2.
| + | |
- | ** Insert Maximum Rate under Minimum Rate
| + | |
- | ** Formula for Freight Total will be:
| + | |
- | *** IF(''gross weight''/100*Rate < X) THEN X
| + | |
- | *** ELSE IF (''gross weight''/100*Rate > Y) THEN Y
| + | |
- | *** ELSE ''Gross Weight''/100*Rate
| + | |
- | | + | |
- | * '''! Please note that generated spreadsheet must contain formulas so that if number is changed by user it will re-calculate!'''
| + | |
- | | + | |
- | * Bug: Reset Form Button
| + | |
- | ** Reset Form button should clear the data and uncheck the CTs from the Shipments table. Right now it deletes all the CTs in the shipment table. This is a bug!!! It must ONLY uncheck CTs, not DELETE CTs!
| + | |
- | | + | |
- | === Spreadsheet: Billing Section ===
| + | |
- | | + | |
- | See example on [[#Figure 4]]
| + | |
- | | + | |
- | * This is a table. | + | |
- | | + | |
- | * Horizontally: | + | |
- | ** header: print column headers
| + | |
- | ** body: print one line per CT
| + | |
- | ** footer: print totals | + | |
- | | + | |
- | * Vertically each line has the following fields - see below.
| + | |
- | | + | |
- | * '''C-Trax''' - CT# | + | |
- | | + | |
- | * '''Plts''' - [[Commodity#Grand Total: Plts]] | + | |
- | | + | |
- | * '''Pkgs on Plts''' - [[Commodity#Grand Total: Pkgs On Plts]] | + | |
- | | + | |
- | * '''Loose Pkgs''' - [[Commodity#Grand Total: Loose Pkgs]]
| + | |
- | | + | |
- | * '''Gross Weight''' - [[Commodity#Grand Total: Gross Kg]]
| + | |
- | | + | |
- | * '''Rate ''' - from [[#Template]]
| + | |
- | | + | |
- | * '''Freight Total ''' = IF(''Gross Weight''/100*''Rate ''<75) THEN 75 ELSE ''Gross Weight''/100*''Rate ''
| + | |
- | | + | |
- | * '''Fuel ''' - from [[#Template]]
| + | |
- | | + | |
- | * '''Fuel Total ''' = ''Freight Total '' x ''Fuel''
| + | |
- | | + | |
- | * '''Extra 1 ''' - default to 0.00
| + | |
- | | + | |
- | * '''Extra 2 ''' - default to 0.00
| + | |
- | | + | |
- | * '''Total ''' = ''Freight Total '' + ''Fuel Total '' + ''Extra 1 '' + ''Extra 1 ''
| + | |
- | | + | |
- | * CT formulas examples for line 31:
| + | |
- | ** Freight Total // =IF(E31/100*F31<75,75,E31/100*F31)
| + | |
- | *** note: 75 here is a "Minimal Rate" from Template
| + | |
- | ** Fuel Total // =G31*H31
| + | |
- | ** Total // =G31+I31+J31+K31
| + | |
- | | + | |
- | * Totals Formulas (see line 39)
| + | |
- | ** each total is simply a sum of all numbers above it
| + | |
- | | + | |
- | * '''! Please note that generated spreadsheet must contain formulas so that if number is changed by user it will re-calculate! '''
| + | |
- | | + | |
- | == Misc ==
| + | |
- | | + | |
- | * Please note that it is not required to have option to save generated version into the system (as for pdfs)
| + | |
- | | + | |
- | * Business Analyst said it is up to developer how to display [[Trucking_Billing_Manifest(User_Guide)#Manifest_Section | Manifest_Section]] and [[Trucking_Billing_Manifest(User_Guide)#Billing_Section | Billing_Section]] on two spreadsheet tabs or one tab (one below another).
| + | |
| | | |
| + | Validation |
| + | ---------- |
| + | Each CT must have Pick Up and Delivery Trucker set. |
| + | If not show error pop-up and block xls generation. |
| | | |
| == Figures == | | == Figures == |
Line 192: |
Line 91: |
| [[File:TruckingBillingManifestVT.JPG]] | | [[File:TruckingBillingManifestVT.JPG]] |
| | | |
- | === Figure 2. ===
| + | === Figure 3. Rate fuel per trucker (one trucker per spreadsheet)=== |
- | | + | |
- | [[File:Trucking_Billing_Manifest_Template_ver_2.JPG]]
| + | |
- | | + | |
- | === Figure 3. === | + | |
- | | + | |
- | [[File:Trucking_Billing_Manifest_Section_A.JPG]]
| + | |
- | | + | |
- | === Figure 4. ===
| + | |
- | | + | |
- | [[File:Trucking Billing Manifest Section B.JPG]]
| + | |
- | | + | |
- | | + | |
- | === Change request ===
| + | |
- | | + | |
- | [[http://mantis.jaguarfreight.com/wiki/Trucking_Billing_Manifest_%28CR%29 Change Request]]
| + | |
- | | + | |
- | == Change Request March 8, 2010 ==
| + | |
- | | + | |
- | * Mantis [http://mantis.jaguarfreight.com/mantis/view.php?id=1844 1844]
| + | |
- | * Template section: Add Maximum Rate under Minimum Rate
| + | |
- | ** '''Maximum Rate''' - text box, default is 0.00
| + | |
- | ** Addition Information
| + | |
- | *** '''Maximum Rate'''- ''datatype/format:'' [[Datatypes#decimal]], max=100,000;2 digits after decimal, ''Required:'' N
| + | |
- | ** '''Fuel'''- textbox,update: default value is 0.0000 (4 decimal places)
| + | |
- | * Spreadsheet:Manifest Section (Section 1)
| + | |
- | ** See Example on Figure 1.
| + | |
- | ** This is a table
| + | |
- | ** Horizontally:
| + | |
- | *** It consists of as many sections as there are CTs (originally selected by user)
| + | |
- | *** Each CT section has as many lines as there are in commod table for this CT
| + | |
- | ** Vertically it has a number of columns:
| + | |
- | *** '''CT#'''
| + | |
- | **** Mapping: [[Ct#CT_Num]]
| + | |
- | *** '''From'''
| + | |
- | **** Mapping: ''Company name'' from [[Ct#Export_Pick_Up]] If [[Ct#Export_Pick_Up]] is empty THEN Map [[Ct#Shipper]]
| + | |
- | **** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | *** '''Origin City'''
| + | |
- | **** Mapping: ''City'' from [[Ct#Export_Pick_Up]] If [[Ct#Export_Pick_Up]] is empty THEN Map [[Ct#Shipper]]
| + | |
- | **** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | *** '''Origin State'''
| + | |
- | **** Mapping: ''State'' from [[Ct#Export_Pick_Up]] If [[Ct#Export_Pick_Up]] is empty THEN Map [[Ct#Shipper]]
| + | |
- | **** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | *** '''To'''
| + | |
- | **** Mapping: ''Company name'' from[[Ct#Export_Delivery_To]] If [[Ct#Export_Delivery_To]] is empty THEN Map [[Ct#Consignee]]
| + | |
- | **** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | *** '''Destination City'''
| + | |
- | **** Mapping: ''City'' from [[Ct#Export_Delivery_To]] If [[Ct#Export_Delivery_To]] is empty THEN Map [[Ct#Consignee]]
| + | |
- | **** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | *** '''Destination State'''
| + | |
- | **** Mapping: ''State'' from [[Ct#Export_Delivery_To]] If [[Ct#Export_Delivery_To]] is empty THEN Map [[Ct#Consignee]]
| + | |
- | **** Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet
| + | |
- | *** '''P.O.'''
| + | |
- | **** Mapping: [[Commodity#PO]]
| + | |
- | *** '''SKU'''
| + | |
- | **** Mapping: [[Commodity#SKU]]
| + | |
- | *** '''Actual Pick Up Date'''
| + | |
- | **** Mapping: [[CT#Actual Export Pick Up Date]]
| + | |
- | **** Note: for Truck-Ocean this date is on [[Container]]; and since each [[Commodity]] line is assigned to its own container - date would be shown for every spreadsheet line for such CT
| + | |
- | ***'''Actual Delivery Date'''
| + | |
- | **** Mapping: [[CT#Actual Delivery Date]]
| + | |
- | **** Note: for Truck-Ocean this date is on [[Container]]; and since each [[Commodity]] line is assigned to its own container - date would be shown for every spreadsheet line for such CT
| + | |
- | *** Sorted by: CT#
| + | |
- | | + | |
- | * Spreadsheet:Billing Section (Section 2)
| + | |
- | ** See Example on Figure 2.
| + | |
- | ** Insert Maximum Rate under Minimum Rate
| + | |
- | ** Formula for Freight Total will be:
| + | |
- | *** IF(''gross weight''/100*Rate < X) THEN X
| + | |
- | *** ELSE IF (''gross weight''/100*Rate > Y) THEN Y
| + | |
- | *** ELSE ''Gross Weight''/100*Rate
| + | |
- | | + | |
- | * '''! Please note that generated spreadsheet must contain formulas so that if number is changed by user it will re-calculate!'''
| + | |
- | | + | |
- | * Bug: Reset Form Button
| + | |
- | ** Reset Form button should clear the data and uncheck the CTs from the Shipments table. Right now it deletes all the CTs in the shipment table. This is a bug!!! It must ONLY uncheck CTs, not DELETE CTs!
| + | |
- | | + | |
- | == Figures == | + | |
- | | + | |
- | '''Figure 1. Trucking Billing Manifest Spreadsheet Section 1'''
| + | |
- | | + | |
- | [[File:TruckingBillingManifestForMasterSection1.JPG]]
| + | |
- | | + | |
- | | + | |
- | '''Figure 2. Trucking Billing Manifest Spreadsheet Section 2'''
| + | |
- | | + | |
- | [[File:TruckingBillingManifestForMasterSection2.JPG]]
| + | |
- | | + | |
| | | |
- | '''Approval '''
| + | <!--[[File:TruckingBillingManifestVT1.JPG]]--> |
| + | [[File:Rate fuel per trucker (one trucker per spreadsheet).JPG]] |
| | | |
- | * By: Marc
| + | === Figure 4. Rate fuel per unique origin and destination pair === |
- | * Update the change to the original Spec: No
| + | |
| | | |
- | == Billing Trucking Manifest for Virtual Group ==
| + | [[File:Rate fuel per origin destination (one trucker per spreadsheet).JPG]] |
| | | |
- | See example below.
| + | == History == |
| | | |
- | [[File:TruckingBillingManifestVT.JPG ]]
| + | === m1905 === |
- | [[File:Trucking Billing Manifest for virtual group.JPG]]
| + | |
| | | |
- | == Sales Invoice ==
| + | * 0001905: (*NATP) Billing for Virtual Group (add this new feature) |
- | TBD
| + | * see this wiki |
| + | * spec updated? Y |
Master Trucking needs additional tab: NATP.
This tab has same purpose as Misc tab - to generate Trucking Billing Manifest. The difference is in how it is done and output design. Overall one is just a variation of another.
It consists of same components as xls for Misc but there are some important differences: