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)
Posted In Sage Line 500 ERP | V1 Document Management