CJSmith dot me

I dump stuff I find useful here

V1 DBAuthorise grab a list of Purchase Orders

Published / by Chris Smith / Leave a Comment

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)

Leave a Reply