CieTrade Account Hierarchy for CRM

A quick reference for understanding how CieTrade data is organized in the Wasteology portal.

The Problem

CieTrade has no native parent account concept - each facility is a separate CPID. Enterprise customers like UPS (1,500+ facilities) need consolidated reporting and management under a single parent entity.

The Solution

A 3-tier portal hierarchy that groups CieTrade data under parent accounts for enterprise-level visibility and control.

The Three Tiers

Tier	Table	Purpose	Example
1	wg_parent_accounts	Top-level customer	"UPS"
2	wg_accounts	Links to CieTrade CPID	"UPS - Bedford Park"
3	wg_account_locations	Service addresses	Physical pickup locations

Each tier connects to the one above it, creating a hierarchy that rolls up data from individual service locations to enterprise parent accounts.

Two Relationship Patterns

Standard (1:1:N)

1 Parent → 1 Account → Many Locations

• Used by most customers
• Single CieTrade CPID per parent
• Simple setup for single-location or franchise customers

Complex (1:N:N)

1 Parent → Many Accounts → Many Locations

• Used by UPS, McKesson, large enterprises
• Multiple CieTrade CPIDs under one parent
• Needed when billing requires separate accounts per facility or region

Key Enterprise Parent IDs

Customer	Parent ID	Pattern	Account Count
UPS	1197	1:N:N	1,500+
McKesson	1209	1:N:N	200+
Goodwill KY	1002	1:1:N	1

How to Join CieTrade Data

Use the unified view portal.vw_wg_accounts - it joins all three tiers automatically:

SELECT
    v.parent_account_name,
    v.account_name,
    v.location_name,
    t.order_date,
    t.revenue
FROM cietrade.orders t
JOIN portal.vw_wg_accounts v
  ON v.cietrade_account_id = t.account_id
  AND v.location_name = t.location_name
WHERE v.parent_account_id = 1197  -- UPS example

Critical: Always join on BOTH cietrade_account_id AND location_name to ensure correct location matching.

Why Both Fields?

• cietrade_account_id links to the CieTrade CPID
• location_name disambiguates when one CPID has multiple service addresses
• Without both, you may get duplicate or incorrect location assignments

---

Edge Cases

UPS

(Parent Account ID: 1197)

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

McKesson

(Parent Account ID: 1209)

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

This account is for "Billing Purposes" only, not a physical location. It should never appear in location sync.

Goodwill Industries of Kentucky

(Parent Account ID: 1002)

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):

• 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

Standard Hierarchy Examples

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	-

---

Exclusion List

The following CPIDs are excluded from standard sync operations in get_missing_locations.sql:

Vendor Accounts

(Dual-labeled as CUSTOMER in CieTrade)

CPID	Account Name	Reason
188	ABC SALVAGE	Vendor
2020	ALL METALS RECYCLING	Vendor
4086	GRANGER WASTE	Vendor
1985	RUMPKE	Vendor
...	(30+ additional vendors)	Vendor

Special Cases

CPID	Account Name	Reason
8544	McKesson Direct	Billing account only
7093	35 Landsdowne	Duplicate location
8244	SPRINGHILL SUITES FISHERS	Duplicate location
3573	WESTROCK	Duplicate account
7640	DAWN HENDREN	Not a real account
3095	STACEY HARRALSON	Not a real account
4105	TEST CUSTOMER	Test data
7098	UPS PEAK 2023	Seasonal
7885	UPS PEAK 2024	Seasonal

---

Quick Reference

Common Parent Account IDs

Parent	ID	Hierarchy
UPS	1197	1:N:N
McKesson	1209	1:N:N
Goodwill Industries of Kentucky	1002	1:1:N
Sonepar	1195	1:1:N
Ball Corp	1356	1:1:N

---

Summary

Key Takeaways

1. The hierarchy bridges CieTrade and portal - Parent Account → Account (CPID) → Location
2. Two patterns exist - Standard (1:1:N) for most customers, Complex (1:N:N) for enterprises like UPS/McKesson
3. Always join on TWO fields - cietrade_account_id AND location_name (The Golden Rule)
4. Use the unified view - portal.vw_wg_accounts joins all three tiers for you
5. Know your exclusions - Vendors, test accounts, and seasonal accounts are filtered from sync

---

Need More Detail?

See the full Account Hierarchy Documentation for:

• Detailed table schemas
• Synchronization process
• Complex relationship patterns
• Data quality considerations