Billing for Virtual Group
From UG
(Difference between revisions)
(→Template) |
(→Spreadsheet) |
||
| Line 78: | Line 78: | ||
** it consists of as many sections as there are CTs (originally selected by user) | ** 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 | ** each CT section has as many lines as there are in commod table for this CT | ||
| - | ** footer: print totals | + | ** 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. | * Vertically it has a number of columns and each line has the following fields - See column definitions below. | ||
Revision as of 21:23, 1 April 2010
Contents |
Business Requirements
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.
Location
- Misc Tab in Master Trucking.
NOTE: Billing can be generated after defining Truck virtual group from Virtual Group and assigning virtual group to group and master. Then user can generate billing based on pick-up truckers or delivery truckers
Example and Typical Workflow
- User creates a virtual group by classifiers
- 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.
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
Template
- Selected Shipment - checkbox, default is uncheck
- Extra columns - listbox, default value is 0
- Reset Form button
- Generate Result button
Additional information:
- Extra columns - listbox, default value is 0, possible values: {0,1,2,3}
- Reset Form button - should clear the data and uncheck the CTs from the Shipments table
- Generate Result button - - should generate spreadsheet
Spreadsheet
It is groupped by Truck company per tab - see below.
Hard Coded Conditions
Based on Pick-up Trucker
- This billing is generated based on Pick-up Trucker ONLY
- Consider Trucker companies that handle the loads from pick up location to warehouse
Based on Delivery Trucker
- This billing is generated based on Delivery Trucker ONLY
- Consider Trucker companies that handle the loads from warehouse to destination
Spreadsheet
See Example on Figure 3.
- 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
- 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 fromCt#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
- 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
- Freight Total // =IF(E31/100*F31<75,75,E31/100*F31)
- 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 Manifest_Section and Billing_Section on two spreadsheet tabs or one tab (one below another).
Figures
Figure 1. Assign GRP and MASTER
Figure 2. Billing for Virtual Group
Figure 2.
Figure 3.
Figure 4.
Change request
Change Request March 8, 2010
- Mantis 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 fromCt#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#
- 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
Figure 2. Trucking Billing Manifest Spreadsheet Section 2
Approval
- By: Marc
- Update the change to the original Spec: No
Billing Trucking Manifest for Virtual Group
See example below.
Sales Invoice
TBD
