Thursday, May 2, 2019

Saved Search or Report for Lot Quantity on Hand and Estimated Value as of Date

This saved search relies on the location average cost where the lot is located, which can give an estimated lot value

To create the saved search:

1. Navigate to Lists > Search > Saved Searches > New and select Item

2. At the Criteria tab > Standard subtab, enter the following filters:
---a. Is lot numbered item -- yes
---b. Inactive -- no
---c. Inventory number fields... On Hand - is greater than 0
---d. Inventory number fields... Location - select any location
---e. Inventory Location - select any location

3. At the Results tab > Columns subtab, enter the following fields:
---a. Name
---b. Serial/Lot Number
---c. Inventory number fields... On Hand ---- Function: Round to Hundredths --- Custom Label: Lot On Hand
---d. Location Average Cost --- Function: Round to Hundredths --- Custom Label: Average Cost
---e. Formula (numeric) --- Function: Round to Hundredths --- then enter the formula below in the formula textbox:

NVL(({locationaveragecost}*{inventorynumber.quantityonhand}),0)

--- Custom Label: Est. Total Lot Cost


4. At the Available Filters tab, enter the following filters:
---a. Inventory Number fields.... Location --- check the Show in Footer box --- Label: Lot on Hand
---b. Inventory Location -- check the Show in Footer box

5. Edit the Search Title

6. Hit Save and Run


Result: This will list the lot numbers with quantity on hand in a particular location, including location average cost and estimated average cost per lot

NOTE: The Available filters fields in #4 should be set to the SAME location to allow the correct location average cost to populate. 

This search is an alternative for the following lot numbered related enhancement:
Enhancement 137091 -
Lists > Accounting > Lot Numbered Items: ability to have an Average Cost per Lot Number

 

1 comment: