Billing for Virtual Group

From UG

Contents 1 Business Requirements 2 Location 3 Example and Typical Workflow 3.1 User Interface 3.2 Template 3.3 Spreadsheet 3.4 Hard Coded Conditions 3.5 Spreadsheet 4 Misc 5 Figures 5.1 Figure 1. Assign GRP and MASTER 5.2 Figure 2. Billing for Virtual Group 5.3 Figure 3. Billing Trucking Manifest for Virtual Group 5.4 Change request 6 Sales Invoice

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. 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 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

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
  • Header for each section: print column headers
  • Divide section by From one location To another location
  • 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 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

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. Billing Trucking Manifest for Virtual Group

Change request

TBD

Approval

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

Sales Invoice

TBD