| Table of Contents | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
Questions/Design Decisions
Excel Design Doc:
how do I set up?
how do I capture relevant data
how to run the routine, and how do I create invoice/report
Receiving
QS: In GF is there a way to differentiate the received package is a pallet or carton?
A checkbox on the warehouse receipt header ‘Floor Loaded Container Received’
Fulfillment
Use Sales Order Sales Type to determine if a shipment is B2B or B2C.
Use the Sales Order Source field to determine if an order is manually entered or automatically created by routines. If the Source field is blank then the order is manually created by the user.
Or Create a new boolean field “Created by System”. All process that automatically creates an order will need to set this field to True, to indicate that the order is automatically created.
Create a picklist field “Special Order Type” with values: “B2C Crowdfunding”, “Transfers”, “Tradeshows”, and “Removals” to identify the following orders
B2C Crowdfunding Additional CSV Upload
Transfers, Tradeshows, Removals
Calculate “B2B Automated Order” only when Division = Breaking Game
Calculate “B2B Automated Line Item(s)” only when Division = Breaking Game
Design
Table Structure - Service Fee Setup
Field Name | Type |
|---|---|
Group | Picklist(Receiving, Fulfillment) |
Service Fee Type* | Picklist(See below Service Fee Type Picklist) |
Service Item | Lookup(GF Item) |
Rate Type | Picklist(Fix Amount, Unit) |
Rate | Currency |
Minimum Charge | Currency |
Pallet Fee per Unit | For B2B fulfillment, pallet packaging |
Limited to Division | Lookup(Division) |
Service Fee Type Picklist
Pallet Receiving - Single SKU
Pallet Receiving - Multiple SKU
Carton Receiving - Single SKU
Carton Receiving - Multiple SKU
Floor Loaded Container Receiving
B2C Automated Order Fulfillment
B2C Automated Order Fulfillment - Line Item
B2C Manual Order Fulfillment
B2C Manual Order Fulfillment - Line Item
B2C Crowdfunding Additional Order Fulfillment
B2B Automated Order Fulfillment
B2B Automated Order Fulfillment - Line Item
B2B Manual Order Fulfillment
B2B Manual Order Fulfillment - Line Item
B2B Order Fulfillment Pallet Packaging
Transfers Order Fulfillment
Tradeshows Order Fulfillment
Removals Order Fulfillment
Table - Service Charge Calculation
(Do we need this? There should be another routine that creates invoices after the line data are reviewed)
...
Field Name
...
Type
...
Customer
...
Start Date
...
Date
...
End Date
...
Date
...
Total Billable Service Fee Amount
...
Currency
...
Overview
Ad Magic handles inventory management for their clients, offering order fulfillment services for a fee. Currently, they calculate these fees by collecting data from various sources like orders and warehouse processes. However, this method is time-consuming and prone to errors. The goal is to make this process more efficient and reliable.
Setups
Based on Miguel’s file Blackbox Invoicing Flow for GF v2 091323.xlsx
Add Setup Fields to Company Setup
Field Name | Field Help |
|---|---|
Pallet Received Single SKU Service Rate | Per pallet charge |
Pallet Received Multiple SKU Service Rate | Additional charge per SKU > 1 SKU per pallet |
Carton Received Single SKU Service Rate | Per carton charge |
Carton Received Multiple SKU Service Rate | Additional charge per SKU > 1 SKU per carton |
Floor Loaded Container Received Service Rate | Additional charge for floor loaded container |
B2C Automated Order Service Rate | Per order charge for imported B2C order |
B2C Automated Line Item(s) Service Rate | Additional charge per line for imported B2C order |
B2C Manual Order Service Rate | Per order charge for manually entered B2C order |
B2C Manual Line Item(s) Service Rate | Additional charge per line for manually entered B2C order |
B2C Crowdfunding Additional Upload Service Rate | Additional charge for crowdfunding order upload |
B2B Automated Order Service Rate | Per order charge for imported B2B order, only apply to Breaking Game |
B2B Automated Line Item(s) Service Rate | Additional charge per line for imported B2B order |
B2B Manual Order Service Rate | Per order charge for manually entered B2B order |
B2B Manual Line Item(s) Service Rate | Additional charge per line for manually entered B2B order |
B2B Pallet Service Rate | Per pallet charge |
Transfers, Tradeshows and Removals Service Rate | Hourly rate for time spent on transfer, tradeshow and removal order fulfillment. |
Transfers, Tradeshows and Removals Order Minumum Rate | Minumum rate charged to fulfill transfer, tradeshow and removal order. |
Service Charge Item | Default service item when creating invoice. |
Blackbox Service Invoice GL Ledger | Default to Blackbox |
Blackbox Service Invoice Warehouse | Default to Blackbox |
New Table: Carton/Package Count
Field Name | Field Help |
|---|---|
Single SKU Pallets | Use to calculate total charge for signle SKU pallets received. |
Single SKU Cartons | Use to calculate total charge for signle SKU cartons received. |
Mixed Pallet | Use to calculate total charge for mixed SKU pallets received. |
SKUs in Mixed Pallet | Use to calculate total charge for mixed SKU pallets received. |
Mixed Carton | Use to calculate total charge for mixed SKU cartons received. |
SKUs in Mixed Carton | Use to calculate total charge for mixed SKU cartons received. |
Floor Loaded Container | If checked, Floor Load Container Received service charge will apply. |
Designer/Vendor | Lookup to Account table (Item Designer/Vendor) |
Whse Receipt | Warehouse Receipt |
Service Charge Calculation Line |
Add New Fields to Whse Shipment
Field Name | Field Help |
|---|---|
Number of Hours Spent | Record hours spent to fulfill transfer, tradeshow and removal order. |
Data Capture
Warehouse Receipt
Use the ‘Carton/Pallet Count’ button on the Warehouse Receipt page to open the data entry form to capture pallets, cartons, and SKU information.
...
Warehouse Shipment
Fields will be available for users to capture hours spent on fulfilling the Transfers, Tradeshows, and Removal orders in the Warehouse Shipment header.
For B2B shipments, the system retrieves the total pallet count from the Warehouse Shipment. The "Total Pallet" field will be used unless "Pallets Override" is populated.
Sales Order
The "Crowdfunding Additional Upload" checkbox was added to indicate whether a crowdfunding additional upload service charge needs to be applied to this order.
The "Transfer/Tradeshow/Removal" checkbox field was added to indicate whether the transfer/tradeshow/removal additional service charge should be applied to this order.
Service Charge Calculation
Field Name | Field Help |
|---|---|
Customer | Lookup to the Account table |
End of Month | Last date of the month |
Year/Month | For example, 2023/08 |
Total Billable Service Charge Amount | Roll-up summary of Amount from Service Charge Calculation Line |
No. of Lines | Number of Service Charge Calculation Lines created |
Service Charge Calculation Line
Field Name | Type |
|---|---|
Service Charge Calculation | Master-Detail (Service Charge Calculation) |
Line No. | |
Service |
Lookip(Service Fee Setup)
Group | Receiving, Fulfillment |
Description | Service Description |
Quantity | |
Unit Rate | |
Amount | |
Document Posting Date | Posting of the Warehouse Shipment |
or Warehouse Receipt | |
Warehouse Shipment | |
Warehouse Receipt |
Month-End Process Flow
A new button on Navigate to the Service Charge Calculation list page “Calculate Service Charge”
A popup window will allow the user to enter the data range, “Start Date” and “End Date”
The system will query Posted Warehouse Shipment and Warehouse Receipt with lines whose Posting Date >= Start Date and tab
Click the Calculate Service Charge button
Month End defaults to the previous month’s last date.
The system will process the below transactions in the month:
For each Whse. Receipts
Criteria:
Document Status = Posted, or Partially Invoiced, Fully Invoiced
Warehouse__r.Charge Warehouse Service Fee = true
Service Charge Calculation Line = null
Posting Date <= End
The system will loop through the lines
For Receiving (Warehouse Receipt)
If the Warehouse Receipt has the field “Floor Loaded Container” checked then the “Floor Loaded Container Receiving“ service fee setup will be used for fee calculation.
If the Warehouse Receipt only contains one SKU and the package type is Pallet then the “Pallet Receiving - Single SKU” service fee setup will be used for fee calculation.
If the Warehouse Receipt receipt only contains multiple SKUs and the package type is Pallet, then the “Pallet Receiving - Multiple SKU” service fee setup will be applied for fee calculation.
If the Warehouse Receipt receipt only contains one SKU and the package type is Carton then the “Carton Receiving - Single SKU” service fee setup will be applied for fee calculation.
If the Warehouse Receipt receipt only contains multiple SKUs and the package type is Carton, then the “Carton Receiving - Multiple SKU” service fee setup will be applied for fee calculation.
For Fulfillment (Warehouse Shipment)
If Sales Order’s Sales Type = ‘B2C’
AND if Special Order Type = ‘B2C Crowdfunding’, then the “B2C Crowdfunding Additional Order Fulfillment” service fee setup will be applied for fee calculation.
AND if the Sales Order Source is ‘Blank’,
Then the “B2C Manual Order Fulfillment” and “B2C Manual Order Fulfillment - Line Item“ service fee setups will be applied for fee calculation.
The processing fee of the order = Rate setup in “B2C Manual Order Fulfillment” + Total Lines * “B2C Manual Order Fulfillment - Line Item”
AND if the Sales Order Souce is not ‘Blank’
Then the “B2C Automated Order Fulfillment” and “B2C Automated Order Fulfillment - Line Item“ service fee setups will be applied for fee calculation.
The processing fee of the order = Rate setup in “B2C Automated Order Fulfillment” + Total Lines * “B2C Automated Order Fulfillment - Line Item”
If Sales Order’s Sales Type = ‘B2B’
AND if Special Order Type = ‘Transfers’, then the “Transfers Order Fulfillment” service fee setup will be applied for fee calculation.
If Special Order Type = ‘Tradeshows’, then the “Tradeshows Order Fulfillment” service fee setup will be applied for fee calculation.
If Special Order Type = ‘Removals’, then the “Removals Order Fulfillment” service fee setup will be applied for fee calculation.
NOTE: For the above Special Order type, the customer is been charged hourly. How does this type of order get entered? In the current system, how do you capture hours spent?
AND if the Sales Order Source is ‘Blank’
Then the “B2B Manual Order Fulfillment” and “B2B Manual Order Fulfillment - Line Item“ service fee setups will be applied for fee calculation.
The processing fee of the order = Rate setup in “B2B Manual Order Fulfillment” + Total Lines * “B2B Manual Order Fulfillment - Line Item”
AND if the Sales Order Source is not ‘Blank’ AND Sales Order Division is “Breaking Game”
Then the “B2B Automated Order Fulfillment” and “B2B Automated Order Fulfillment - Line Item“ service fee setups will be applied for fee calculation.
The processing fee of the order = Rate setup in “B2B Manual Order Fulfillment” + Total Lines * “B2B Manual Order Fulfillment - Line Item”
NOTE: If shipped using pallets then an additional service fee “B2B Order Fulfillment Pallet Packaging” will also be added to the total order fee.
The processing fee of the order = The processing fee of the order + Rate setup in “B2B Order Fulfillment Pallet Packaging” * number of pallets used.
- Create a button when clicking, create Sales Invoices for each customer, and group lines by Service Fee Type.
of Month Date
Calculate per pallet charge
Calculate additional charge if pallets have more than 1 SKU
Calculations
Single SKU Pallets
Service Charge = Single SKU Pallets * Single SKU Pallet Service Rate
Mixed SKU Pallets
Service Charge = Mixed Pallets * Single SKU Pallet Service Rate + ((SKUs in Mixed Pallets - 1) * Mixed Pallets * Multiple SKU Pallet Service Rate)
Calculate per carton charge
Calculate additional charge for cartons that have more than 1 SKU
Calculations
Single SKU Cartons
Service Charge = Single SKU Cartons * Single SKU Carton Service Rate
Mixed SKU Cartons
Service Charge = Mixed Cartons * Single SKU Carton Service Rate + ((SKUs in Mixed Cartons - 1) * Mixed Cartons * Multiple SKU Carton Service Rate)
Calculate additional Floor Loaded Container service charges if applicable.
A flat Floor Loaded Container service charge will be applied.
If the container contains Mixed SKU Cartons, then
Floor Loaded Container Service Fee + Mixed SKU Cartons service fee
The system will create one Service Charge Calculation record per vendor/designer per month.
Vendor/Designer is from the Carton/Pallet Count table.
The system will create one Service Charge Calculation Line per Warehouse Receipt.
For each Whse. Shipments
Criteria:
Document Status = Posted, or Partially Invoiced, Fully Invoiced
Warehouse__r.Charge Warehouse Service Fee = true
Service Charge Calculation Line = null
Posting Date <= End of Month Date
Determine whether the shipment is B2B or B2C based on the Sales Order Sales Type.
A picklist field “Method” in the Sales Order indicates whether an order was manually entered or imported by routines.
Calculate per order charge
Calculate the total line charge
For B2B, calculate per pallet charge if applicable
Calculation
Order Service Rate includes 1 unit line item.
Service Fee = Order Service Fee + ((Quantity - 1) * Line Item Service Fee)
Calculate B2C crowdfunding additional charge if applicable
The “Crowdfunding Additional Upload” checkbox on the Sales Order indicates whether an order is a crowdfunding additional upload or not.
Calculate additional Transfer, Tradeshow, and Removal order fulfillment fees if applicable.
The “Transfer/Tradeshow/Removal” checkbox on the Sales Order indicates whether the order is for transfer/tradeshow/removal or not.
The system will create one Service Charge Calculation record per vendor/designer per month.
If a shipment contains multiple items, and the items belong to a different vendor/designer, then multiple Service Charge Calculation records will be created.
Users run reports and review data
Click the Create Sales Invoice button to generate Sales Invoices
The system will create one invoice per vendor/designer per month.
For all Service Charge Calculation with:
Sales Invoice = null
End of Month <= End of Month parameter specified on the confirmation page.
Default Warehouse and GL Ledger to BlackBox on all Sales Invoices
There will be one invoice line per service group (Receiving/Fulfillment), and the invoice line item will default to the 'Service Charge Item' set up in the Company Setup.
Open Questions
Internal Excel Design Doc: