AcumaticaSQLQueriesAndFunctions/BranchManagers.sql

IF OBJECT_ID ('dbo.BranchManagers','V') IS NOT NULL DROP VIEW dbo.BranchManagers
GO 
CREATE VIEW dbo.BranchManagers
AS
WITH ParentChildren AS (
    -- Anchor member: find the initial record with the description 'Operations Branch Leaders'
    SELECT 
        CompanyID, WorkGroupID, Description, ParentWGID, SortOrder, AccessRights, WaitTime, 
        BypassEscalation, UseCalendarTime, CreatedByID, CreatedByScreenID, CreatedDateTime, 
        LastModifiedByID, LastModifiedByScreenID, LastModifiedDateTime, 0 AS Level 
    FROM dbo.EPCompanyTree
    WHERE Description = 'Operations Branch Leaders'

    UNION ALL 

    -- Recursive member: find children of the current level, limit recursion to levels < 3
    SELECT 
        child.CompanyID, child.WorkGroupID, child.Description, child.ParentWGID, child.SortOrder, 
        child.AccessRights, child.WaitTime, child.BypassEscalation, child.UseCalendarTime, 
        child.CreatedByID, child.CreatedByScreenID, child.CreatedDateTime, child.LastModifiedByID, 
        child.LastModifiedByScreenID, child.LastModifiedDateTime, parent.Level + 1 AS Level
    FROM dbo.EPCompanyTree AS child
    INNER JOIN ParentChildren AS parent ON child.ParentWGID = parent.WorkGroupID
    WHERE parent.Level < 2  -- Limit recursion to levels less than 3
),
children AS (
    SELECT * 
    FROM ParentChildren p
    WHERE p.Description != 'Operations Branch Leaders'
      AND p.Level < 3
),
-- Replace ISNUMERIC with pattern matching
FolderIsManager AS (
    SELECT 
        c.CompanyID, c.Level, c.Description, c.WorkGroupID, c.ParentWGID, m.ContactID
    FROM children c
    INNER JOIN dbo.EPCompanyTreeMember m ON c.CompanyID = m.CompanyID AND c.WorkGroupID = m.WorkGroupID
    WHERE c.Level = 1 
      AND c.Description LIKE '%[^0-9]%'  -- Description is not numeric
      AND m.Active = 1
),
FolderManagerBranches AS (
    SELECT 
        a.CompanyID, 
        a.BAccountID AS EmployeeBAccountID,
        a.AcctCD AS EmployeeCD, 
        a.AcctName AS EmployeeName, 
        u.Username, 
        u.PKID, 
        b.BranchID AS BranchTableID,
        b.BranchCD AS BranchTableCD,
        br.BAccountID AS BAccountTableID,
        br.AcctCD AS BAccountTableCD
    FROM FolderIsManager m
    INNER JOIN dbo.Contact con ON con.CompanyID = m.CompanyID AND con.ContactID = m.ContactID
    INNER JOIN dbo.Users u ON u.CompanyID = con.CompanyID AND con.UserID = u.PKID 
    INNER JOIN dbo.BAccount a ON m.CompanyID = a.CompanyID AND m.ContactID = a.DefContactID 
    INNER JOIN children c ON c.CompanyID = m.CompanyID AND c.ParentWGID = m.WorkGroupID
    INNER JOIN dbo.Branch b ON b.CompanyID = c.CompanyID AND b.BranchCD = c.Description
    INNER JOIN dbo.BAccount br ON b.CompanyID = br.CompanyID AND b.BAccountID = br.BAccountID 
),
FolderIsNotManager AS (
    SELECT 
        c.CompanyID, c.Level, c.Description, c.WorkGroupID, c.ParentWGID
    FROM children c
    WHERE c.Level IN (1, 2)
      AND c.Description NOT LIKE '%[^0-9]%'  -- Description is numeric
),
NotFolderManagerBranches AS (
    SELECT 
        a.CompanyID, 
        a.BAccountID AS EmployeeBAccountID,
        a.AcctCD AS EmployeeCD, 
        a.AcctName AS EmployeeName, 
        u.Username, 
        u.PKID, 
        b.BranchID AS BranchTableID, 
        b.BranchCD AS BranchTableCD, 
        br.BAccountID AS BAccountTableID, 
        br.AcctCD AS BAccountTableCD
    FROM FolderIsNotManager f
    INNER JOIN dbo.EPCompanyTreeMember m ON f.CompanyID = m.CompanyID AND m.WorkGroupID = f.WorkGroupID
    INNER JOIN dbo.Contact c ON m.CompanyID = c.CompanyID AND m.ContactID = c.ContactID 
    INNER JOIN dbo.BAccount a ON a.CompanyID = c.CompanyID AND a.DefContactID = c.ContactID 
    INNER JOIN dbo.Users u ON u.CompanyID = c.CompanyID AND u.PKID = c.UserID 
    INNER JOIN dbo.Branch b ON b.CompanyID = f.CompanyID AND f.Description = b.BranchCD 
    INNER JOIN dbo.BAccount br ON b.CompanyID = br.CompanyID AND br.BAccountID = b.BAccountID 
)
SELECT * FROM FolderManagerBranches
UNION
SELECT * FROM NotFolderManagerBranches