V1 DBAuthorise can be setup to have multiple Purchase orders, so I use a Common Table Expression in a view for any time I need to get a list of Purchase Orders and Sage Company that is not in ~Accepted and State OK (1024).
user5 for us is the PO number and user7 the Sage Company Name, change if different on your own system.
I encountered a looping issue so use maxrecursion 10 to ensure there is nothing wrong. 10 because there may be multiple invoices per Purchase Order.
with cte (user5,user7) as ( select cast(left([user5], charindex(',',[user5]+',')-1) as varchar(10)) [user5],LEFT(user7,3) as user7 from
.[dbo].[DBAUTH] where stateNumber != '1024' and user5 != '' --and user7 = ' ' union all select cast(left([user5], charindex(',',[user5]+',')-1) as varchar(10)),LEFT(user7,3) as user7 from cte where RTRIM([user5]) !='' and [user5] IS NOT NULL and [user5] LIKE '%,%' ) select [user5],user7 from cte group by [user5],user7 option (maxrecursion 10)