AcumaticaSQLQueriesAndFunctions/CustomerData.sql

IF OBJECT_ID ('dbo.CustomerData','V') IS NOT NULL DROP VIEW dbo.CustomerData
GO 
CREATE VIEW dbo.CustomerData
AS
SELECT b.CompanyID, 
	   b.BAccountID as SurrogateBAKey, 
	   b.AcctCD as CustomerID, 
	   b.AcctName as CustomerName,
	   p.AcctCD as ParentAccountID,
	   bke.ValueString as OwnerID,
	   count(children.BAccountID) as CountOfChildrenAccounts,
	   STRING_AGG(children.AcctCD, ', ') AS ChildrenIDList,
       CAST(CASE 
           WHEN COUNT(children.BAccountID) > 0 THEN 1 
           ELSE 0 
       END AS BIT) AS IsParent,
       CAST(CASE 
           WHEN MAX(CASE WHEN b.ParentBAccountID IS NOT NULL THEN 1 ELSE 0 END) = 1 THEN 1 
           ELSE 0 
       END AS BIT) AS IsChild
From BAccount b 
left JOIN  BAccount p on p.CompanyID = b.CompanyID and p.BAccountID = b.ParentBAccountID
LEFT join BAccount children on children.CompanyID = b.CompanyID and b.BAccountID = children.ParentBAccountID  and b.[type] = children.[type] 
LEFT JOIN BAccountKvExt bke ON bke.CompanyID = b.CompanyID AND bke.RecordID = b.NoteID AND bke.FieldName = 'AttributeOWNER'
where b.[type] = 'CU'
group by b.CompanyID, 
	   b.BAccountID, 
	   b.AcctCD,
	   b.AcctName,
	   p.AcctCD,
	   bke.ValueString