Item Tracking Report showing materials used in past work orders

Overview

This article outlines how to create a custom report showing items/materials used in past work orders.

Solution

By default, the inventory history for an item only shows the work order# but not the actual part# for the item.

In order to see this information, it is necessary to create a custom report using the Advanced Query Builder as follows:

1. Launch the Advanced Query Builder by navigating to Utility > Advanced Query Builder
2. Supply a name for the new filter, e.g. Item Tracking Report.
3. Paste the following query in the SQL Statement tab
   

   SELECT 
     h.doc_no AS WO_Number,
     h.ASSEMBLY_CODE,   ih.descript AS Assembly_Name,
     d.sequence,  d.item_code,
     id.descript AS Parts_Name,   
     d.item_std_qty,  d.item_std_rate,
     d.actual_qty,  d.actual_rate
   FROM
     ASSEMBLY_WO_HEADER h
     INNER JOIN Items ih WITH (NOLOCK) ON (h.ASSEMBLY_CODE = ih.itemno)
     INNER JOIN ASSEMBLY_WO_DETAILS d WITH (NOLOCK) ON (h.ASSEMBLY_WO_HEADER_ID = d.ASSEMBLY_WO_HEADER_ID)
     INNER JOIN Items id WITH (NOLOCK) ON (d.item_code = id.itemno)
   WHERE
     assembly_code LIKE :code
   ORDER BY
     h.doc_no,
     d.SEQUENCE

4. Click on Update SQL then open the Browser Settings tab.
5. Configure the Browser Settings tab as shown below:
   
6. Click Find and enter the filtering criteria in the Value column when prompted to enter a value for the code parameter.
7. It is recommended to uncheck Remember Parameter Values as it would retrieve the value from the default values set in the build which can in most cases be empty.   
   
   You can use % in the parameter to used as a wildcard. The example in the above screenshot will retrieve all items having ASSEMBLY_CODE values starting with 'PB'.
8. Click OK to generate the Item Tracking report.
   

assembly_code LIKE :code

assembly_code LIKE 'PB%'

Back to top