AcumaticaSQLQueriesAndFunctions/CustomerBusinessDevOwner.sql

DROP VIEW IF EXISTS CustomerBusinessDevOwner
GO 
Create View CustomerBusinessDevOwner
as
SELECT 
b.CompanyID, 
ROW_NUMBER() OVER(PARTITION BY c.CompanyID ORDER BY c.CompanyID) AS rc,
c.BAccountID as CustomerBAccountID,
e.BAccountID as EmployeeBAccountID , 
b.AcctCD as CustomerCD,
e.AcctCD as EmployeeCD, 
e.AcctName as EmployeeName 
from Customer c 
inner join BAccount b on c.CompanyID = b.CompanyID and c.BAccountID = b.BAccountID 
left JOIN BAccountKvExt bke on b.CompanyID = bke.CompanyID and  b.NoteID = bke.RecordID 
left join BAccount e on bke.CompanyID =e.CompanyID and bke.ValueString = e.AcctCD