Billing for Virtual Group

From UG

Revision as of 20: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

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

Template

• Selected Shipment - checkbox, default is uncheck
• Extra columns - listbox, default value is 0

Additional information:

• Extra columns - listbox, default value is 0, possible values: {0,1,2,3}

Spreadsheet

It is groupped by Truck company per tab - see below.

Spreadsheet: Manifest Section

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

• Vertically it has a number of columns. See column definitions below.

• CT#

• Vendor - CT#Shipper

• P.O. - Commodity#PO

• Item - Commodity#SKU

• Origin Port

City, State from CT#Export Pick Up

Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet

• Dest. Port

COMPANY-CITY-NOTE from CT#Export Delivery To

Note: print once per CT on the first line if there are multiples lines per CT on a spreadsheet

• Actual Pick Up Date

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

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

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