CieTrade Account Hierarchy Integration Guide

A guide for integrating with CieTrade APIs and transforming flat account data into a consolidated enterprise hierarchy.

The Problem

CieTrade's API provides flat account data - each facility is a separate account with no native parent/child relationship. Enterprise customers like UPS (1,538 facilities) appear as hundreds of separate accounts with no connection between them.

What CieTrade Provides

ListAccounts returns:
┌────────────────────────────────────────┐
│ account_id: 7088  │ "UPS - ILBED"      │  ← Separate account
│ account_id: 7089  │ "UPS - ILFRA"      │  ← Separate account
│ account_id: 7090  │ "UPS - ILGAN"      │  ← Separate account
│ ...               │ (1,538 UPS accounts)│
│ account_id: 7310  │ "McKesson DC1"     │  ← Separate account
│ account_id: 7314  │ "McKesson DC2"     │  ← Separate account
└────────────────────────────────────────┘

No way to know these belong to the same enterprise customer!

Illustrative IDs

The account_id values above are realistic examples of the 4-digit CPID format. They are not guaranteed to match exact production values.

The Solution

Build a 3-tier hierarchy by creating a parent account mapping layer on top of CieTrade data.

The Three Tiers

Tier	Source	Purpose	Example
1	You create	Parent Account (enterprise customer)	"UPS"
2	ListAccounts	CieTrade Account (CPID/facility)	"UPS - Bedford Park"
3	ListAccountLocations	Service Location (address)	Physical pickup address

Each tier connects to the one above, 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 CieTrade Account → Many Locations

• Used by most customers
• Single CieTrade account_id per parent
• Simple mapping

Complex (1:N:N)

1 Parent → Many CieTrade Accounts → Many Locations

• Used by UPS, McKesson, Sonepar, and other large enterprises
• Multiple CieTrade account_ids under one parent
• Needed when each facility has its own CieTrade account

CieTrade API Endpoints

CieTrade provides 10 API endpoints. Two are Master Data (accounts and locations) that form your hierarchy foundation. Eight are Transactional (services, billing, dispatch, trading) that join to the hierarchy.

Endpoint Overview

Endpoint	Type	Purpose	Join Pattern
ListAccounts	Master	Customer accounts (CPIDs)	Primary key
ListAccountLocations	Master	Service locations	FK: account_id
ListServices	Transactional	Scheduled services	account_id + location_name
ListBillingSheets	Transactional	Billing documents	account_id
ListBillingSheetCharges	Transactional	Line item charges	account_id + location_name
ListDispatchJobs	Transactional	Dispatch jobs	account_id + location_id
TradingInquiry	Transactional	Material trading	account_id + location_name
ListAccountsReceivable	Transactional	A/R snapshot	account_id
ListServiceExpenses	Transactional	Service expenses	service_id
ListWorksheets	Transactional	Operational data	account_id

Master Data Endpoints

ListAccounts

Returns all CieTrade customer accounts (CPIDs).

Field	Type	Description
account_id	integer	Primary key - use this to link locations
account_name	string	Customer/facility name
role_name	string	Account role classification
active_status	string	"Active" or "Inactive"

Record Volume: ~2,516 customer accounts in the Wasteology hierarchy (6,900+ total in CieTrade)

About ListAccounts fields

The API returns approximately 20 fields. The table above highlights the most relevant ones for building a hierarchy. account_id is the CPID (4-digit CieTrade-assigned identifier) that serves as the key link between CieTrade data and the Wasteology parent account hierarchy.

ListAccountLocations

Returns service locations linked to accounts.

Field	Type	Description
location_id	integer	Primary key
account_id	integer	Foreign key → ListAccounts.account_id
location_name	string	Location identifier
address	string	Street address
city	string	City
state	string	State abbreviation
zip	string	ZIP code

Record Volume: ~15,477 locations (across accounts in the Wasteology hierarchy)

The Native Join

-- CieTrade's native relationship (flat, no parent)
SELECT
    a.account_id,
    a.account_name,
    l.location_id,
    l.location_name,
    l.address, l.city, l.state, l.zip
FROM cietrade_accounts a              -- from ListAccounts
JOIN cietrade_locations l             -- from ListAccountLocations
    ON l.account_id = a.account_id;

---

Building the Hierarchy

Step 1: The Parent Account Mapping Layer

Wasteology maintains this hierarchy in three PostgreSQL tables in the portal schema:

• wg_parent_accounts — One row per enterprise customer (e.g., UPS, McKesson). Contains the parent_account_id and parent name.
• wg_accounts — One row per CieTrade CPID that has been assigned to a parent. The parent_account_id FK links each account to its parent.
• wg_account_locations — Mirrored location data from ListAccountLocations, linked to accounts via account_id.

This mapping is maintained manually. See Data Access for the API endpoint to query the full hierarchy.

Step 2: Join Everything Together

-- Consolidated hierarchy view
SELECT
    p.parent_account_id,
    p.parent_account_name,
    a.account_id AS cietrade_account_id,
    a.account_name AS facility_name,
    l.location_id,
    l.location_name,
    l.address, l.city, l.state, l.zip
FROM wg_parent_accounts p
JOIN wg_accounts a
    ON a.parent_account_id = p.parent_account_id
LEFT JOIN wg_account_locations l
    ON l.account_id = a.account_id
ORDER BY p.parent_account_name, a.account_name, l.location_name;

The Golden Rule of Joining

Critical Join Rule

When joining transactional data (services, billing, dispatch) to the hierarchy, always join on BOTH account_id AND location_name to ensure correct location matching.

-- Joining transaction data to hierarchy
SELECT
    p.parent_account_name,
    a.account_name,
    l.location_name,
    t.service_date,
    t.weight
FROM cietrade_services t              -- from ListServices
JOIN cietrade_accounts a
    ON a.account_id = t.account_id
JOIN cietrade_locations l
    ON l.account_id = t.account_id
    AND l.location_name = t.location_name    -- BOTH fields!
JOIN wg_accounts wa
    ON wa.account_id = a.account_id
JOIN wg_parent_accounts p
    ON p.parent_account_id = wa.parent_account_id;

Why both fields?

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

---

Edge Cases

UPS (Recommended Parent ID: 1197)

Attribute	Value
Pattern	1:N:N (1,538 CieTrade accounts)
Locations	1,859
Reason	Billing restrictions require separate account per facility
Special	Seasonal accounts (PEAK 2023, PEAK 2024) should be excluded

Example CieTrade Accounts to Map:

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

McKesson (Recommended Parent ID: 1209)

Attribute	Value
Pattern	1:N:N (66 CieTrade accounts)
Locations	157
Reason	Billing restrictions require separate account per location
Exclusion	"McKesson Direct" is billing-only, not a physical location

McKesson Direct

If you see an account named "McKesson Direct" - exclude it from your hierarchy. It's for billing purposes only and has no physical locations.

Sonepar

Attribute	Value
Pattern	1:N:N (10 CieTrade accounts)
Locations	332
Reason	Multiple regional accounts consolidated under one parent

Standard Customers

Most customers follow the simple 1:1:N pattern - one parent, one CieTrade account, multiple locations:

Customer	Pattern	Notes
BGIS	1:1:N	Single account, multiple locations
Regions Bank	1:1:N	Single account, multiple locations
CBRE	1:1:N	Single account, multiple locations
Ball Corp	1:1:N	Single account, multiple locations

---

Accounts to Exclude

When building your hierarchy, exclude these account types:

Vendor Accounts

CieTrade labels some vendors as "CUSTOMER" type. These should not be in your customer hierarchy:

Account Name	Reason
ABC SALVAGE	Vendor
ALL METALS RECYCLING	Vendor
GRANGER WASTE	Vendor
RUMPKE	Vendor

Special Exclusions

Account Name	Reason
McKesson Direct	Billing account only
UPS PEAK 2023	Seasonal/temporary
UPS PEAK 2024	Seasonal/temporary
TEST CUSTOMER	Test data

---

Record Volume Summary

Current production counts (as of 2026-03-20):

Entity	Count
Parent accounts	390
Accounts (CPIDs in hierarchy)	2,255
CieTrade customer accounts (total)	2,516
Locations	15,477

Notable Multi-CPID Parents

Parent	Accounts	Locations	Pattern
UPS Oasis Supply Corporation	1,538	1,859	1:N:N
TForce Freight	186	192	1:N:N
McKesson	66	157	1:N:N
REDBOX+	34	115	1:N:N
JUNK KING	20	64	1:N:N
Sonepar	10	332	1:N:N

---

Quick Reference

API Endpoints Summary

Endpoint	Purpose	Key Field
ListAccounts	Customer accounts (CPIDs)	account_id
ListAccountLocations	Service locations	account_id (FK)

Join Pattern

-- Basic hierarchy join
wg_parent_accounts.parent_account_id
    ← wg_accounts.parent_account_id (FK)
    → wg_accounts.account_id = ListAccounts.account_id
    → ListAccountLocations.account_id

Relationship Patterns

Pattern	Structure	Example
Standard (1:1:N)	1 Parent → 1 Account → N Locations	Most customers
Complex (1:N:N)	1 Parent → N Accounts → N Locations	UPS, McKesson, Sonepar

---

Summary

Key Takeaways

1. CieTrade is flat - No native parent account concept in the API
2. You build the hierarchy - Maintain parent account mapping in wg_parent_accounts and wg_accounts
3. Two patterns exist - Standard (1:1:N) for most, Complex (1:N:N) for enterprises
4. Always join on TWO fields - account_id AND location_name for transactions
5. Know your exclusions - Vendors, billing-only accounts, and seasonal accounts

---

Frequently Asked Questions

What is the unique identifier linking child accounts to parent accounts?

The cietrade_account_id (CPID) is the bridge between CieTrade API data and the Wasteology hierarchy. In wg_accounts, the parent_account_id foreign key links each CieTrade account to its parent in wg_parent_accounts.

Where do the IDs come from?

CPIDs are 4-digit identifiers assigned by CieTrade. They appear as account_id in ListAccounts responses. Parent account IDs (in wg_parent_accounts) are Wasteology-assigned and have no relation to CieTrade.

What is the criteria for linking a child account to a parent?

The mapping is manual. TJ decides which CieTrade accounts belong under which parent based on business knowledge (shared contracts, corporate ownership, billing relationships). There is no automated rule.

Does Wasteology have a live data source?

Yes. The CieTrade API is the source of truth for flat account and location data. A daily Prefect ETL job syncs ListAccounts and ListAccountLocations into PostgreSQL. The parent account hierarchy lives in SQL Server and is mirrored to PostgreSQL. See the Data Access page for the API endpoint.

Is CieTrade flat - no hierarchy at all?

Yes, completely flat. There is no parent_account_id field in any CieTrade API response. The entire three-tier hierarchy is a Wasteology construct layered on top of CieTrade data.

---

Last updated: 2026-04-21