Tuesday, November 13, 2018

Create a saved search for Sales Orders - Pending Approval to show items on Back Order

Create a transaction saved search for Sales Order - Pending Approval and compare it with the quantity available from the selected location, if using Multi-Location Inventory.

1. Navigate to Reports > Saved Searches > All Saved Searches > New.
2. Click Transactions.
3. Under Criteria tab > Standard subtab, set the following:
 Type = Sales Order
 Main Line = is False
 Tax Line = False
 Shipping Line = False
 Status = is any of Sales Order: Pending Approval
4. Also, add the following formula under Criteria tab > Standard subtab:
 Filter = Formula (Text)
 Formula = case when {location} = {item.inventorylocation} then 1 else 0 end
 Description = is 1
5. Under Criteria tab > Summary subtab:
 Summary Type = Sum
 Field = Formula (Numeric)
 Description = is greater than or equal to 0
 Formula = sum({quantity}) - (max(nvl({item.locationquantityavailable},0)))
6. Under the Results tab, remove all fields and set the following:
 a. Customer Name:
    Field = Name
    Summary Type = Group
 b. Sales Order Number:
    Field = Number
    Summary Type = Group
 c. Item Name/Number:
    Field = Item
    Summary Type = Group
 d. Quantity Ordered:
    Field = Quantity
    Summary Type = Sum
 e. Location in the Sales Order:
    Field = Location
    Summary Type = Group
 f. Available Quantity on the Location:
    Field = Item: Location Available
    Summary Type = Maximum
 g. Quantity that can be Committed:
    Field = Formula (Numeric)
    Summary Type = Sum
    Formula = sum({quantity}) - (max(nvl({item.locationquantityavailable},0)))
7. Save and Run

 - Formula in #6.g. (Quantity that can be Committed) is calculated as the difference between the Quantity Ordered and the Available Quantity on that Location, as shown in the item record.
 - The formula in step #5 is calculating the Quantity Ordered less the Quantity Available. If Quantity Ordered is greater than or equal to the Quantity Available for that location, it means that that the items would be on back order once the order is approved.



1 comment:

  1. I don't believe this functions with orders that have kits.