Create a Search that Calculates the Aging of Serialized Inventory from Receipt to Fulfillment of Quantities

 

Currently, there is no standard report that would generate an aging on Serialized Inventory Items which is discussed under Enhancement 134988. This also holds true for other types of Inventory Items.  An alternate solution is to create a search that would calculate the age of Serialized Inventory quantities. Here are the steps that we need to do:

 

1. Navigate to Reports>Saved Searches>All Saved Searches>New

2. Select Search Type = Item

3. Enter the desired title of the Search under the Search Title field.

4. Navigate to Criteria tab and mark the checkbox next to the Use Expressions field

5. Navigate to Criteria tab>Standard subtab>Filter column and add the following:

 

a. Costing Method = Serialized

b. Transaction Fields…>Date = is not empty

c. Transaction Fields…>Type = is any of Item Fulfillment, Item Receipt

d. Transaction Fields…>Account Type = is any of Cost of Goods Sold, Other Current Asset

e. Inventory Number Fields…>Is On Hand = is false

f. Serial/Lot Number = is not empty

g. Formula (Numeric) | Formula: case when REGEXP_LIKE ({transaction.serialnumbers}, {serialnumber}) then 1 else 0 end | Formula (Numeric): equal to | Value: 1

 

6. Navigate to Results tab:

 

a. Sort By: Name

b. Then By: Transaction: Serial/Lot Numbers

c. Then By: Serial/Lot Number

 

7. Navigate to Results tab>Columns subtab>Field column and add the following:

 

a. Name | Summary Type: Group

b. Serial/Lot Numbers | Summary Type: Group | Summary Label: Serial Number

c. Transaction Fields…>Serial/Lot Numbers

d. Transaction Fields…>Date

e. Transaction Fields…>Number

f. Transaction Fields…>Type

g. Formula (Numeric) | Summary Type: Sum | Formula: round((case when {transaction.type} = 'Item Fulfillment' then {transaction.trandate} else {today} end) - (case when {transaction.type} = 'Item Receipt' then {transaction.trandate} else {today} end),0) | Custom Label: No. of Days Sold and Shipped from Receipt | Summary Label: No. of Days Sold and Shipped from Receipt

 

8. Hit Save & Run button

 

Note: The calculation of the serial number aging is limited to Item Receipts and Item Fulfillments and the Serial Numbers are all fulfilled in the system.