Catch Weight Package

 

Overview

“Catch weight” refers to scenarios in which a distributor stocks an item according to an inventory UOM, such as a case, and prices the item according to a unit price UOM, such as pounds or kilograms. Catch weight items differ from standard, or “even weight” items, which may use a price UOM that is different than the inventory UOM. In the case of a standard weight item, each container has for pricing reasons the same (standardized) weight, whereas for catch weight itmems, each container of the item can have a different weight.
 
As containers are received from vendors, and when they are issued and shipped to customers, it is necessary to account for both the number of containers, which normally serve as a quantity ordered, while also capturing the actual weight, as the value of these transactions will be based on weight rather than quantity.
 
This document describes a number of enhancements to screens in the xTuple desktop client that will allow the system to record catch weight in critical transactions.
 

Workflow Analysis

Item

The item screen will be modified to allow the user to flag an item to use catch weight. For catch weight items, the item master will use the container (CS) as the inventory UOM. The admin will define a conversion ratio for weight per container (LB or KG) and set this as the price UOM. The user will enter a standard weight for the item (by container), which will be used for estimating weight when there is no QOH.

Item Site

A new field will be added to the item site record to store and display the item's average weight by item site. The item site's average weight will be used on purchase orders and sales orders for the item, to calculate an estimated price.

Purchasing

The item source screen currently allows only an inventory UOM for items. For catch weight items, the item source will be extended to store a price UOM for the vendor (LB or KG), and a price per that UOM. The system will generate an estimated price per case based on the vendor's price per pound and the current average weight of the item at that item site.

Receiving

For receipts, the actual received weight (catch weight) will be recorded. The purchase price will be updated by multiplying the vendor's price per pound by the catch weight. A function on the receipt screen will update the item's average weight at the item site based on actual received weights.

Vouchering

For vouchers, the system will display the cost based on the values recorded at the time of receipt. The vouchering step is an opportunity to compare the catch weight with the weight on the Vendor's invoice, which may be different.

Sales

For sales order items, the user will select a quantity of containers, and the system will calculate the sales price based on the price per pound multiplied by the current average weight of the item at the item site. The sales order item screen will display additional information to allow the user to easily see now many containers must be purchased to meet a certain number of pounds.

Shipping

When the item is issued to shipping, the user must enter the weight value of each line (the user may weigh the items at the shipping dock and enter an actual weight). A function on the shipping screen will update the item's average weight at the item site based on actual shipped weights.

Invoicing

The price charged to the customer will be recalculated when the invoice is generated, based on the actual weight of the items on the shipment times the price per pound. The invoice form will be updated to display the quantity of cases and the weight for each line.

New Terms and Concepts

Even Weight (aka Standard Weight)
– The current method that xTuple uses to track weight. Value is stored at the item level.
Average Weight
– New method for storing weights
– Value can change over time as items are received and shipped
– Average weight must be stored per itemsite
Catch Weight
– The labeled or measured weight of the items as they are received
– This value is recorded at the time of receipt and passed to the voucher
Drop Weight
– The labeled or measured weight of the items as they are shipped
– This value is recorded at the time of shipment and passed to the invoice
Actual Weight
– A term that refers to either a Catch Weight or a Drop Weight

 

UI Modifications

Item screen

Required Modifications
– Add a check box labeled “Use Catch Weight”

Item Site screen

Required Modifications
If item uses catch weight:
• Add a field in the header area, below Ranking
◦ Label: “Avg. Weight”
◦ Value: numeric (stored in cwitemsite table)
◦ Not editable (see function below)
• Add label “UOM” after Avg. Weight field
◦ display Unit Price UOM defined on the item
• A function will update the item's average weight based on actual received weights.
Formula:
( (Current QOH x Current Avg. Wgt.) + Received Weight ) / (Current QOH + Received Qty)

Item Source screen

The vendor is likely going to sell by the case, no modification to header is needed.

Item Source Price screen

Required Modifications:
If item uses catch weight,
◦ Hide “Discount” radio button (removes Discount options) Cannot discount a catch weight item?
In this context discount refers to setting a purchase price based on a list cost for an item. In other words, it applies to purchasing the item from the vendor, not to the price you charge when you sell your customers. For customers you can discount the item using price lists. 
◦ Display these fields, replace value in [ ] with actual value from item record
Price per [Unit Price UOM] (eg, LB): [entered by user]
Ratio [UOM per UOM] (eg LB per CS): [Avg. Weight from cwitemsite]
Net out package weight? [Checkbox]
Price per [Inv UOM] (eg. CS): [calculated, not editable]
Formula for Price per CS: Est.
Price per LB times Avg. Weight = Price per inventory UOM
If user selects “Net out package weight”, formula is this:
Price per LB x (Avg. Weight – Package Weight) = Price per CS
Note: Package Weight is stored on the item record.
“Standardize field labels: anything calculated using avg weight is estimated e.g., Est.Price per CS” or if space is an issue, marked in some fashion (asterisk with a note at the bottom indicating this is only an estimate) or perhaps colored text”
Understood.

Purchase Order Item

Required Modifications:
• Under Qty Ordered, display the average weight per container
◦ Label: (eg. “LB per CS”) (Est or AVG)
◦ Value: [Avg. Weight from cwitemsite]
• Display total weight of all containers in the PO Item.
◦ Label: Total LB: (Est.)
◦ Value: Order Qty. x Avg. Weight
• In the price groupbox, display the vendor price per LB.
◦ Label: Price per LB
◦ Value: Price per LB (value from cwitemsrcp)
◦ This will be an editable field.
• The Unit Price field will be calculated
◦ Label: Price per [Inv. UOM] (e.g CS) (Est.)
◦ Value:
▪ Price per LB x Avg. Weight.
▪ If item source record is set to net out package weight, then
Unit Price = Price per LB x (Avg. Weight – Package Weight) (Est.)
PO Form: make sure the form shows both CS qty, LB per CS, Total LB, and Price per LB.

Enter Order Receipts

Required Modifications
• Hide “Receive All” button (since it allows you to skip the EnterReceipt screen)
Have different receive all functionality that does not skip enter receipt screen yet helps user receive all?
Receive All makes assumptions about the receipts—that qty is 100%, etc. It does not open the screens that allow you to enter specific information about each line. We need to suppress this option in order to make sure the actual weight is entered.
• Add “Recd. Wgt” column to display the weight received
• Add “Wgt. UOM” to display the Item unit price UOM
• For non-catch-weight items, display “NA” in above fields
• New function to post the Recd Wgt to the Avg Weight table (cwitemsite)
Required Modifications
• If Item uses Catch Weight:
◦ Under “Qty. to Receive”, Add new field
▪ Label: “Wgt. to Receive”
▪ Value: numeric (user enters total weight of the receipt)
▪ Both qty and wgt are required fields
◦ Add label “Wgt. UOM:” beneath the Wgt. to Receive field
▪ Display unit price UOM from item
◦ Hide “Unit Purchase Cost” field
◦ Insert new field
▪ Label: Vendor Price per [Price UOM] (eg. LB)
▪ Value: (display value from cwitemsrcp table)
◦ Calculate “Extended Purchase Cost” (recv_purchcost) based on Wgt. to Receive multiplied
by Vendor Price per LB.

Voucher

Unit price is the price per case, calculated by dividing actual weight received by qty received

Voucher Item

Required Modifications
In the uninvoiced receipts and returns box, add column for Wgt.
• Column Name: Wgt.
• Value: total actual weight of receipt

Sales Order Item

Required Modifications
– Under Qty Ordered, add a field for weight per case
– Label: [Std or Avg] [Price UOM] per [Inv. UOM]: (eg.  Avg LB per CS) (Est.)
– Value: Avg. Weight from cwitemsite
– Add another field for total weight
– Label: [Std or Avg] [Price UOM] per [Inv. UOM]: (eg.  Avg LB per CS) (Est.)
– Value: Avg. Weight from cwitemsite
– Add another field for total weight
– Label: Total [Price UOM] (eg. Total LB)
– Value: order qty x Std or Avg. Weight (Est.)
If item uses Catch Weight
– Calculate Extended Price based on Net Unit Price multiplied by AVG CASE WEIGHT (Est.)
On Costs History, display “Recv. Weight” column and Cost per [Price UOM] (eg. Price
per LB”) column (Recv Cost/Recv. Weight)

Sales Order screen, items tab

– add column to show total weight for the line
– Column name: Wgt. (Est.)
– Value: order qty x avg weight

Issue to Shipping

Required Modifications:
• Add columns for weight
◦ Column name: “Wgt.” (Est.)
◦ Value: Inv. Qty x weight from cwshipitem
• Add column for weight UOM
◦ Column name: Wgt. UOM
◦ Value: Unit price UOM from item
• Hide “Issue Line” button
• Hide “Issue All” button

Issue Line to Shipping

Required Modifications
If item uses catch weight
• Add field:
◦ Label: “Wgt. to Issue” (Est.)
▪ populate this field with a value based on the Inv. Qty to issue x average weight
▪ Allow user to edit the value (Force?) Yes, if you prefer.
• Add label:
◦ Label: Pricing UOM:
◦ Value: [Unit Price UOM from item]
Where is this stored?
• When the user clicks the Issue button, the screen will save the weight value and UOM to the
cwshipitem table.
• Need triggers to delete/update the cwshipitem table if the main shipitem record is
deleted/updated.

Ship Order

Required Modifications
Add columns for “Wgt. UOM” and “Wgt.”
After stock is issued, populate the Wgt. for each line with the weight distributed.
Overload shipshipment function to create an invoice based on the shipped weight.
Shipping the order will update the item's average weight at the item site based on actual shipped weight.

Select for Billing

Required Modifications
• Add columns for Wgt. and Wgt. UOM

Invoice screen

Required Modifications:
• Add columns for Wgt. and Wgt. UOM

Invoice Item screen

Required Modifications
If item uses Catch Weight
– Under Qty Billed, add field for weight per container
– Label: [Price UOM] per [Inv. UOM] (eg. LB per CS)
– Value: Actual weight on shipitem / order qty
– Add field for total weight
– Label: Total [Price UOM] (eg. Total LB)
– Value: ACTUAL WEIGHT recorded on shipitem
- Calculate Extended Price based on Net Unit Price multiplied by ACTUAL WEIGHT

 

Database changes

New Tables

cwitem
  • cwitem_id
  • cwitem_item_id
  • cwitem_usecw (boolean)
cwitemsite
  • cwitemsite_id
  • cwitemsite_itemsite_id
  • cwitemsite_wgt

cwitemsrcp

  • cwitemsrcp_id
  • cwitemsrcp_itemsrcp_id
  • cwitemsrcp_qty
  • cwitemsrcp_uom
  • cwitemsrcp_netout_pkg bool
cwrecv
  • cwrecv_id
  • cwrecv_recv_id
  • cwrecv_qty
  • cwrecv_uom
cwshipitem
  • cwshipitem_id
  • cwshipitem_shipitem_id
  • cwshipitem_qty
  • cwshiptiem_uom
csinvc
  • cwinvcitem_id
  • cwinvcitem_invitem_id
  • cwinvcitem_qty
  • cwinvcitem_uom

New and Updated Functions

updateavgweight
When purchased items are received, if they are catch weight items, run the updateavgweight
function to update the average weight of the item at that itemsite.
( (Current QOH x Current Avg. Wgt.) + Received Weight ) /
(Current QOH + Received Qty)
 
distributevoucherline
When distributing payments to voucher lines, need a new version of this function that will
calculate the vendor price for the receipt based on the catch weight, rather than carrying over
the price from the PO.
 
shipshipment
This function creates the invoice. Need a new version of the function that will populate the price
on the invoice line by multiplying the Selling Unit Price by the drop weight, rather than the
price on the sales order.
 

Reports and Forms to Update

  • Receipt
  • Voucher
  • Check
  • Sales Order Acknowledgment
  • Pack/pick list
  • Invoice