Account Creation Process

This guide covers how to create new accounts in the portal hierarchy.

Template Selection

Choose the appropriate SQL template based on your scenario:

Account Creation Templates

Option	Type	Description	Default
add_new_account_standard_hierarchy.sql	Template	Single CPID, multiple locations	BGIS, Regions Bank, CBRE
add_new_account_to_existing_parent.sql	Template	Add CPID to existing parent	New UPS facility
add_new_account_ups_hierarchy.sql	Template	Multiple CPIDs, one parent	UPS, McKesson

Standard Hierarchy Process (1:1:N)

Template: add_new_account_standard_hierarchy.sql

Steps

1. Check if account exists (avoids duplicates)
2. Insert parent account into portal.wg_parent_accounts
3. Get new parent_account_id using MAX(parent_account_id)
4. Insert account with FK to parent into portal.wg_accounts
5. Insert all locations from new_ct.addresses

SQL Pattern

Standard Hierarchy Insert Pattern

Key logic for inserting locations

-- Variables to set
DECLARE @PARENT_ACCOUNT_NAME VARCHAR(250) = 'Client Name';
DECLARE @CIETRADE_ACCOUNT_ID INT = 8535; -- CPID from CieTrade

-- Insert locations (key logic)
INSERT INTO portal.wg_account_locations (account_id, cietrade_address_id, location_name, ...)
SELECT @ACCOUNT_ID, address_id, location_name, ...
FROM new_ct.addresses
WHERE account_id = @CIETRADE_ACCOUNT_ID
  AND location_name NOT LIKE '%INVOICE%';

Complex Hierarchy Process (1:N:N)

Template: add_new_account_ups_hierarchy.sql

Use Case: Billing restrictions require separate accounts for each location (UPS, McKesson pattern).

Steps

1. Create single parent account
2. Find ALL matching CPIDs using name pattern
3. Insert ALL matching accounts in bulk
4. Insert locations for all accounts

Pre-validation Query

Pre-validation - Verify Matching Accounts

Run this FIRST to verify matching accounts

DECLARE @PARENT_ACCOUNT_NAME VARCHAR(250) = 'McKesson';
SELECT DISTINCT a.account_name, a.account_id AS CPID
FROM new_ct.accounts a
WHERE a.account_name LIKE '%' + @PARENT_ACCOUNT_NAME + '%';

Always Pre-validate

Run the pre-validation query first to verify the name pattern matches the expected accounts before proceeding with the bulk insert.

Adding to Existing Parent

Template: add_new_account_to_existing_parent.sql

Common Parent IDs

Parent	ID	Hierarchy
UPS	1197	1:N:N
McKesson	1209	1:N:N

Process: Skips parent creation, inserts account and locations under existing parent.

tip

When adding a new facility to UPS or McKesson, use add_new_account_to_existing_parent.sql with the appropriate parent ID.