Special Accounts

These accounts have unique hierarchy patterns or special business rules.

UPS (Parent Account ID: 1197)

UPS

Complex (1:N:N)

Parent ID1197

Accounts20

Locations1500

📄add_new_account_ups_hierarchy.sql

Billing restrictions require separate account for each location

UPS Details

Attribute	Value
Hierarchy Type	1:N:N (1 parent → 20+ accounts → N locations each)
Reason	Billing restrictions require separate account for each location
Template	add_new_account_ups_hierarchy.sql
Special Features	Dedicated export procedures: usp_export_spend_rebate_ups, usp_export_weight_ups
Exclusion	UPS Conveyor Recycling (account_id 7611) excluded from container profiles
Seasonal	UPS PEAK 2023 (7098), UPS PEAK 2024 (7885) excluded from standard sync

Example Facilities

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

UPS Hierarchy Query

View the complete UPS hierarchy structure:

-- 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;

---

McKesson (Parent Account ID: 1209)

McKesson

Complex (1:N:N)

Parent ID1209

Accounts15

Locations500

📄add_new_account_ups_hierarchy.sql

Billing restrictions require separate account for each location

McKesson Details

Attribute	Value
Hierarchy Type	1:N:N (same as UPS)
Reason	Billing restrictions require separate account for each location
Template	add_new_account_ups_hierarchy.sql
Special Account	McKesson Direct (CPID 8544) - excluded from sync

McKesson Direct

McKesson Direct (CPID 8544) is for "Billing Purposes" only, not a physical location. It should never appear in location sync.

McKesson Hierarchy Query

View the complete McKesson hierarchy structure:

-- 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;

---

Goodwill Industries of Kentucky (Parent Account ID: 1002)

Goodwill Industries of Kentucky

Standard (1:1:N)

Parent ID1002

Accounts1

Locations30

📄add_new_account_standard_hierarchy.sql

Dedicated ETL module in goodwill schema

Goodwill Details

Attribute	Value
Hierarchy Type	1:1:N (standard)
Special Features	Dedicated ETL module in goodwill schema
Custom Tables	goodwill.sales_history, goodwill.weight_history, goodwill.haul_rate

Business Rules (Stakeholder-Approved)

Goodwill-Specific Rules

These business rules have been approved by stakeholders and are implemented in the ETL module:

• Shively ADC should never have weight
• Exclude Open Top weight from KY West – The Outlet
• Exclude Open Top sales from KY West – The Outlet
• Do not count scheduled monthly services as hauls
• Exclude all Front Loads from haul and weight metrics
• Exclude all "Hand Pick Up" equipment types from haul and weight metrics

Custom ETL Module

Goodwill has a dedicated schema with custom ETL logic:

-- Custom tables in goodwill schema
goodwill.sales_history
goodwill.weight_history
goodwill.haul_rate

---

Standard Hierarchy Examples

These accounts follow the standard 1:1:N pattern (one parent → one account → multiple locations):

Customer	Parent ID	Structure	Template
BGIS	varies	1:1:N	add_new_account_standard_hierarchy.sql
Regions Bank	varies	1:1:N	add_new_account_standard_hierarchy.sql
CBRE - VDOT	varies	1:1:N	add_new_account_standard_hierarchy.sql
Sonepar	1195	1:1:N	add_new_account_standard_hierarchy.sql
Ball Corp	1356	1:1:N	add_new_account_standard_hierarchy.sql

Standard Hierarchy Pattern

Most customers follow the standard pattern:

Parent Account (1)
    └─ Account (1)
        ├─ Location 1
        ├─ Location 2
        ├─ Location 3
        └─ Location N

---

Quick Reference

When to Use Complex Hierarchy (1:N:N)

Use the complex pattern when:

• Billing restrictions require separate accounts per location
• Multiple CPIDs need consolidated reporting
• Each facility has unique accounting requirements

Examples: UPS, McKesson

When to Use Standard Hierarchy (1:1:N)

Use the standard pattern when:

• Single CPID covers all locations
• Consolidated billing is acceptable
• Locations share accounting structure

Examples: Most customers (BGIS, Regions Bank, CBRE, etc.)

Parent Account Lookup

Customer	Parent ID	Type
UPS	1197	Complex
McKesson	1209	Complex
Goodwill Industries of Kentucky	1002	Standard
Sonepar	1195	Standard
Ball Corp	1356	Standard

---

Related Documentation

• Creating New Accounts - Templates and procedures
• Hierarchy Patterns - Understanding 1:1:N vs 1:N:N
• Daily Sync Process - Keeping data up-to-date

---

Last updated: 2026-01-30