Billing for Virtual Group

From UG

Revision as of 23:36, 16 April 2010 by Alex (Talk | contribs)

(diff) ← Older revision | Current revision (diff) | Newer revision → (diff)

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.
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 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 ~~Generate Billing on Pick up Trucker or Generate Billing on Delivery Trucker~~ 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

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

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

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

Tabs based grouping

Based on what user selected on Template system will group per CT#Pick Up Trucker or per CT#Delivery Trucker.

Each Tab

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
- Header for each section: print column headers
- Divide section by From one location To another location ( you need to group by all existing From / To pairs among selected CTs)
- 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.
- 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
- 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

Please note that it is not required to have option to save generated version into the system (as for pdfs)
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

Figures

Figure 1. Assign GRP and MASTER

Figure 2. Billing for Virtual Group

Figure 3. Rate fuel per trucker (one trucker per spreadsheet)

Figure 4. Rate fuel per unique origin and destination pair

Change request

TBD

Approval

By: Marc
Update the change to the original Spec: No

Sales Invoice

TBD