Relationship Patterns

The account hierarchy supports two relationship patterns depending on the customer's structure and billing requirements.

Pattern Comparison

Pattern	Structure	When to Use
Standard (1:1:N)	1 Parent → 1 Account → N Locations	Single CPID with multiple service addresses
Complex (1:N:N)	1 Parent → N Accounts → N Locations	Multiple CPIDs requiring consolidated reporting

Standard Pattern (1:1:N)

The standard pattern is used for customers with a single CieTrade counterparty (CPID) but multiple service locations.

1:1:N • Standard

BGIS

🏢Parent

📁Account

📍

📍

📍

📍

1Account

•

45Locations

View:

1:1:N pattern - 1 parent, 1 account, N locations

Standard Pattern Example

UPS Corporate

|

UPS Region East

|

Location A

Location B

Location C

Characteristics

• One parent account represents the customer
• Single CieTrade CPID linked via wg_accounts.cietrade_account_id
• Multiple locations linked to that account
• Simple, direct reporting path

Example Customers

Customer	Parent ID	Structure	Notes
BGIS	varies	1:1:N	Standard template reference
Regions Bank	varies	1:1:N	Standard template reference
CBRE - VDOT	varies	1:1:N	Standard template reference
Sonepar	1195	1:1:N	-
Ball Corp	1356	1:1:N	-

Complex Pattern (1:N:N)

The complex pattern is used when billing restrictions require separate CieTrade accounts (CPIDs) for each facility, but consolidated reporting is still needed.

1:N:N • Complex

UPS

🏢Parent

📁

📁

📁

📍

📍

📍

📍

20Accounts

•

1,500Locations

View:

1:N:N pattern - 1 parent, N accounts, N locations each

Complex Pattern Example

Waste Management

|

WM Northeast

|

Site 1

Site 2

WM Southeast

|

Site 3

Site 4

Site 5

Characteristics

• One parent account for the enterprise customer
• Multiple CieTrade CPIDs under that parent
• Each CPID has its own locations
• Enables consolidated reporting across all CPIDs

Example Customers

Customer	Parent ID	Accounts	Locations	Reason
UPS	1197	20+	1,500+	Billing restrictions require separate account for each facility
McKesson	1209	Multiple	Varies	Billing restrictions require separate account for each location

When to Use 1:N:N

Use the complex pattern when:

• Billing restrictions require separate accounts per facility
• The customer has multiple CieTrade CPIDs
• Consolidated enterprise reporting is needed

Determining the Pattern

Use this query to identify which pattern a customer uses:

-- Hierarchy summary: identify relationship patterns
SELECT
    p.parent_account_id,
    p.parent_account_name,
    COUNT(DISTINCT a.account_id) AS account_count,
    CASE
        WHEN COUNT(DISTINCT a.account_id) = 1 THEN '1:1:N (Standard)'
        WHEN COUNT(DISTINCT a.account_id) > 1 THEN '1:N:N (Complex)'
        ELSE 'No accounts'
    END AS hierarchy_pattern
FROM portal.wg_parent_accounts p
LEFT JOIN portal.wg_accounts a
    ON a.parent_account_id = p.parent_account_id
GROUP BY p.parent_account_id, p.parent_account_name
ORDER BY hierarchy_pattern, p.parent_account_name;

Find All Complex (1:N:N) Hierarchies

-- Find all 1:N:N hierarchies (multiple accounts per parent)
SELECT
    p.parent_account_id,
    p.parent_account_name,
    COUNT(DISTINCT a.account_id) AS account_count,
    COUNT(DISTINCT l.location_id) AS total_locations,
    CAST(STRING_AGG(CAST(a.account_name AS VARCHAR(MAX)), ', ')
         WITHIN GROUP (ORDER BY a.account_name) AS VARCHAR(MAX)) AS account_names
FROM portal.wg_parent_accounts p
JOIN portal.wg_accounts a
    ON a.parent_account_id = p.parent_account_id
LEFT JOIN portal.wg_account_locations l
    ON l.account_id = a.account_id
GROUP BY p.parent_account_id, p.parent_account_name
HAVING COUNT(DISTINCT a.account_id) > 1
ORDER BY account_count DESC;

Find All Standard (1:1:N) Hierarchies

-- Find all 1:1:N hierarchies (single account, multiple locations)
SELECT
    p.parent_account_id,
    p.parent_account_name,
    a.account_name,
    a.cietrade_account_id AS cpid,
    COUNT(l.location_id) AS location_count
FROM portal.wg_parent_accounts p
JOIN portal.wg_accounts a
    ON a.parent_account_id = p.parent_account_id
LEFT JOIN portal.wg_account_locations l
    ON l.account_id = a.account_id
GROUP BY p.parent_account_id, p.parent_account_name, a.account_name, a.cietrade_account_id
HAVING COUNT(DISTINCT a.account_id) = 1
ORDER BY location_count DESC;

Real-World Examples

UPS Hierarchy Detail

-- UPS hierarchy: all accounts and locations
SELECT
    p.parent_account_name,
    a.account_id,
    a.account_name AS facility_name,
    a.cietrade_account_id AS cpid,
    COUNT(l.location_id) AS location_count
FROM portal.wg_parent_accounts p
JOIN portal.wg_accounts a
    ON a.parent_account_id = p.parent_account_id
LEFT JOIN portal.wg_account_locations l
    ON l.account_id = a.account_id
WHERE p.parent_account_id = 1197  -- UPS
GROUP BY p.parent_account_name, a.account_id, a.account_name, a.cietrade_account_id
ORDER BY a.account_name;

Example Facilities:

• ILBED - Bedford Park
• ILFRA - Franklin Park Hub
• ILGAN - Morgan Street Center
• ILJOL - Joliet
• IL53A - Rockford Air Cargo Facility

McKesson Hierarchy Detail

-- McKesson hierarchy: all accounts and locations
SELECT
    p.parent_account_name,
    a.account_id,
    a.account_name AS facility_name,
    a.cietrade_account_id AS cpid,
    COUNT(l.location_id) AS location_count
FROM portal.wg_parent_accounts p
JOIN portal.wg_accounts a
    ON a.parent_account_id = p.parent_account_id
LEFT JOIN portal.wg_account_locations l
    ON l.account_id = a.account_id
WHERE p.parent_account_id = 1209  -- McKesson
GROUP BY p.parent_account_name, a.account_id, a.account_name, a.cietrade_account_id
ORDER BY a.account_name;

McKesson Direct

McKesson Direct (CPID 8544) is excluded from standard sync operations because it is a billing account only, not a physical location.