Billing for Virtual Group

From UG

(Difference between revisions)
Jump to: navigation, search
(→Spreadsheet)
(→History)
 
(51 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 Form'''  button
+
-
* '''Generate Result''' 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 ====
-
* '''Reset Form''' button - should clear the data and uncheck the CTs from the Shipments table
+
-
* '''Generate Result''' button - - should generate spreadsheet
+
-
=== 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
+
-
** divide section by From one location To another location
+
-
** 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.
+
-
** '''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 196: 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

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