Billing for Virtual Group

From UG

Revision as of 21:25, 1 April 2010

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

• Sorted by: CT#
• 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!

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

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#

• 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