We have to review our active supplier list every 6 months and mark any that haven’t been used in the last 2 years as inactive.
Does anyone know of a BAQ or other report that can show “active” suppliers whose last transaction was before a particular date or null? In this case, the cutoff is 12/31/2023.
I would try a BAQ:
Query1 (InnerSubQuery)
Add Vendor table and Left Outer Join to PartTran on Company and VendorNum.
Add a Table Criteria to PartTran where TranSype BEGINS “PUR” (this will include PUR-STK, PUR-SUB, PUR-UNK, etc.).
Display Fields: All you need here is Vendor ID, Name if you want, and a calculated “LastTran” field: max(PartTran.SysDate)
That should give you the last transaction date in the system for each Vendor.
Query2 (Top Level)
Pull in Vendor again… then pull in Query 1 (inner join) and you can join those on VendorID, VendorNum… whatever you used previously.
Add a Parameter to your BAQ for “CutOffDate” or whatever you want to call it.
Then add a Table Criteria to Query1 here in your top level query where Calculated_LastTran <= @CutOffDate parameter.
You should now only get back a list of Vendors who’s last transaction were on or before your cutoff date.