Billing for Virtual Group

From UG

(Difference between revisions)
Jump to: navigation, search
(→Spreadsheet)
(→History)
 
(16 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 ==
+
-
 
+
-
* Misc Tab in [[Master Trucking]].
+
-
* This can be replaced the existing one or separated for another menu, so that will be two options to create billing manifest. Just in case they change their mind, so at least we still have the existing one.
+
-
 
+
-
'''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
+
-
 
+
-
[[Category: Trucking Billing Manifest (M) (xls)]]
+
-
 
+
-
== Example and Typical Workflow ==
+
-
 
+
-
* User creates a virtual group by classifiers
+
-
* User reviews the result then click next
+
-
* User selects '''Generate Billing'''  <strike>'''Generate Billing on Pick up Trucker''' or '''Generate Billing on Delivery Trucker''' </strike> option from the menu
+
-
* User clicks '''Apply'''
+
-
* Pop-up window shows Group#XXXX has been generated and Master#XXXX has been generated. See [[#Figure 1. Assign GRP and MASTER]]
+
-
* 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 selects "Pick Up Trucker" or "Delivery Trucker"
+
-
* User completes the form and clicks "Generate Report" button.
+
-
* System generates spreadsheet and opens it to the user. See example on [[#Figure 3. Billing Trucking Manifest for Virtual Group]]
+
-
** 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.
+
-
 
+
-
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]] for viewing CTs below
+
-
 
+
-
This is the HTML template from Misc Tab under a Master
+
-
 
+
-
See [[#Figure 3. Billing Trucking Manifest for Virtual Group]] for billing spreadsheet below
+
-
This is a xls spreadsheet that user can enter Rate,...etc, and the number will calculated automatically
+
To add this feature you need to add 2 components:
 +
* [[#Wizard Pop Up]]
 +
* [[#Trucking Billing Manifest for NATP]]
-
=== Template ===
 
-
* '''Selected Shipment''' - [[checkbox]], default is ''uncheck''
 
-
* '''Extra columns''' - [[listbox]], default value is 0
 
-
* '''Reset Form'''  button
 
-
* '''Generate Result''' button
 
-
* '''Pick Up Trucker''' radio // map to [[CT#Pick Up Trucker]]
 
-
* '''Delivery Trucker''' radio // [[CT#Delivery Trucker]]
 
 +
=== Wizard Pop Up ===
-
Additional information:
+
After user selects billing option on [[Virtual_Groups_for_NATP#General_Wizard_Step_3]] system will:
-
* '''Extra columns''' - [[listbox]], default value is 0, possible values: {0,1,2,3}
+
* create a [[CT Group]] out of all CTs in Virtual Group
-
* '''Reset Form''' button - should clear the data and uncheck the CTs from the Shipments table
+
** MOT = Truck Domestic
-
* '''Generate Result''' button - - should generate spreadsheet
+
** 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]]
-
=== Hard Coded Conditions ===
+
=== Trucking Billing Manifest for NATP ===
-
<strike>
+
Master Trucking needs additional tab: NATP.
-
Based on Pick-up Trucker
+
-
* This billing is generated based on Pick-up Trucker ONLY
+
See [[#Figure 2. Billing for Virtual Group]]
-
* Consider Trucker companies that handle the loads from pick up location to warehouse
+
-
Based on Delivery Trucker
+
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 billing is generated based on Delivery Trucker ONLY
+
See what we have now on Misc tab here: [[Trucking Billing Manifest (Developer Guide)]]
-
* Consider Trucker companies that handle the loads from warehouse to destination
+
-
</strike>
+
-
=== Spreadsheet ===
+
==== Template ====
-
==== Tabs based grouping====
+
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]]
-
Based on what user selected on Template system will group per [[CT#Pick Up Trucker]] or per [[CT#Delivery Trucker]].
+
==== Xls ====
-
==== Each Tab ====
+
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]]
-
See Example on Figure 3.
+
==== Rate Fuel Choices ====
-
* This is a table.
+
* 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)]]
-
* Horizontally:
+
* per unique origin / destination pair
-
** It consists of as many sections as there are CTs (originally selected by user)
+
** new idea
-
** Each CT section has as many lines as there are in commod table for this CT
+
** 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
-
** Header for each section: print column headers
+
** business reason: rates could be defined by origin/destination in the future for some companies
-
** Divide section by From one location To another location (''' you need to group by all existing From / To pairs among selected CTs''')
+
** see [[#Figure 4. Rate fuel per unique origin and destination pair]]
-
** Body for each section: print one line per CT
+
-
** Each section provides Rate, Minimum Rate, Maximum Rate, and Fuel for before each table
+
-
** Footer for each section: print totals
+
-
* Vertically it has a number of columns and each line has the following fields - See column definitions below.
+
==== Generate For Choices ====
-
** '''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
+
-
** '''Plts'''
+
-
*** Mapping: [[Commodity#Grand Total: Plts]]
+
-
** '''Pkgs on Plts'''
+
-
*** Mapping: [[Commodity#Grand Total: Pkgs On Plts]] 
+
-
** '''Loose Pkgs'''
+
-
*** Mapping: [[Commodity#Grand Total: Loose Pkgs]] 
+
-
** '''Gross Weight'''
+
-
*** Mapping: [[Commodity#Grand Total: Gross Kg]]
+
-
** '''Rate '''
+
-
*** Mapping: From user input on the spreadsheet for Rate field
+
-
** '''Freight Total  '''
+
-
*** 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!'''
+
-
** '''Fuel'''
+
-
*** Mapping: From user input on the spreadsheet for Fuel field
+
-
** '''Fuel Total  ''' 
+
-
*** Calculation field = ''Freight Total '' x ''Fuel''
+
-
** '''Extra 1 '''
+
-
*** Mapping: none
+
-
*** Default value:  0.00
+
-
** '''Extra 2 '''
+
-
*** Mapping: none
+
-
*** Default value:  0.00
+
-
** '''Total  '''
+
-
*** Calculation field = ''Freight Total '' + ''Fuel Total  '' + ''Extra 1 '' + ''Extra 2 ''
+
-
* Default for each section for calculation fields (user input)
+
-
** '''Rate:''' - default value is 0.00
+
-
** '''Minimum Rate:''' - default value is 0.00
+
-
** '''Maximum Rate:''' - default value is 0.00
+
-
** '''Fuel:''' - default value is 0.0000
+
-
* Sorted by: CT#
+
-
* each total is simply a sum of all numbers above it
+
-
* CT formulas examples for line 15:
+
-
** Freight Total // =IF(015/100*B9<75,75,(015/100*B9>600,600),O15/100*B9)
+
-
*** note: 75 here is a "Minimal Rate"
+
-
*** note: 600 here is a "Maximu, Rate"
+
-
** Fuel Total  // =Q15*B12
+
-
** Total        // ==Q15+S15 If there is Extra column, it should add values from extra columns as well
+
-
== Misc ==
+
* 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
 +
* All Delivery Truckers
 +
** same idea as above
 +
* Selected Pick Up Trucker
 +
** on template system produces dropdown with all unique truckers across selected CTs
 +
** user may select one
 +
** xls has only CTs that have selected trucker
 +
* Selected Delivery Trucker
 +
** same idea as above
-
* Please note that it is not required to have option to save generated version into the system (as for pdfs)
+
Validation
-
* Please note that this spreadsheet consists of many column (wide spreadsheet), please use window pane to fix column CT#. If not, please repeat column CT# after Total column
+
----------
 +
Each CT must have Pick Up and Delivery Trucker set.
 +
If not show error pop-up and block xls generation.
== Figures ==
== Figures ==
Line 184: Line 91:
[[File:TruckingBillingManifestVT.JPG]]
[[File:TruckingBillingManifestVT.JPG]]
-
=== Figure 3. Billing Trucking Manifest for Virtual Group ===
+
=== Figure 3. Rate fuel per trucker (one trucker per spreadsheet)===
-
[[File:TruckingBillingManifestVT1.JPG]]
+
<!--[[File:TruckingBillingManifestVT1.JPG]]-->
 +
[[File:Rate fuel per trucker (one trucker per spreadsheet).JPG]]
-
=== Figure 4. Billing Trucking Manifest for Virtual Group (cont')===
+
=== Figure 4. Rate fuel per unique origin and destination pair ===
-
[[File:TruckingBillingManifestVT2.JPG]]
+
-
=== Change request ===
+
[[File:Rate fuel per origin destination (one trucker per spreadsheet).JPG]]
-
TBD
+
-
'''Approval '''
+
== History ==
-
* By: Marc
+
=== m1905 ===
-
* Update the change to the original Spec: No
+
-
== Sales Invoice ==
+
* 0001905: (*NATP) Billing for Virtual Group (add this new feature)
-
TBD
+
* see this wiki
 +
* spec updated? Y

Current revision as of 01:10, 17 April 2010


Contents

[edit] General

[edit] Business Requirements

See New Arden Trucking Project NATP (main)#Billing

[edit] Specs

To add this feature you need to add 2 components:


[edit] Wizard Pop Up

After user selects billing option on Virtual_Groups_for_NATP#General_Wizard_Step_3 system will:

[edit] Trucking Billing Manifest for NATP

Master Trucking needs additional tab: NATP.

See #Figure 2. Billing for Virtual Group

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.

See what we have now on Misc tab here: Trucking Billing Manifest (Developer Guide)

[edit] Template

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

[edit] Xls

It consists of same components as xls for Misc but there are some important differences:

[edit] Rate Fuel Choices

  • 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

[edit] Generate For Choices

  • 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
  • All Delivery Truckers
    • same idea as above
  • Selected Pick Up Trucker
    • on template system produces dropdown with all unique truckers across selected CTs
    • user may select one
    • xls has only CTs that have selected trucker
  • Selected Delivery Trucker
    • same idea as above 
Validation
----------
Each CT must have Pick Up and Delivery Trucker set.
If not show error pop-up and block xls generation.

[edit] Figures

[edit] Figure 1. Assign GRP and MASTER

File:PopUpAssignedMasterVT.JPG

[edit] Figure 2. Billing for Virtual Group

File:TruckingBillingManifestVT.JPG

[edit] Figure 3. Rate fuel per trucker (one trucker per spreadsheet)

File:Rate fuel per trucker (one trucker per spreadsheet).JPG

[edit] Figure 4. Rate fuel per unique origin and destination pair

File:Rate fuel per origin destination (one trucker per spreadsheet).JPG

[edit] History

[edit] m1905

  • 0001905: (*NATP) Billing for Virtual Group (add this new feature)
  • see this wiki
  • spec updated? Y
Retrieved from "https://wiki.jaguarfreight.com/Billing_for_Virtual_Group"
Personal tools