Parties Queries

1. Executive Summary

This document catalogs the query operations for the parties domain — the five tables that form the master identity layer for every person and organization in UTA's financial operations. These queries power party lookup, role resolution, bank account selection, address retrieval, and deal-party association across the application.

The parties domain is the most broadly referenced in the system. Nearly every transactional screen — cash receipts, worksheets, settlements, payments, billing, tax, and write-offs — begins with a party lookup or resolves party context from a deal. The queries documented here are the foundation for those downstream operations.

Tables covered: party, party_role, party_bank_account, party_addresses, deal_party

Data model reference: Parties Data Model

2. Key Queries

2.1 Party Queries

2.1.1 Get All Parties

Operation: getAllParties

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	—	Base table only; no joins

Filters:

None

Computed Values:

None

Returns: Array of all party rows, including party_id, auth_party_id, first_name, last_name, company_name, full_name, display_name, and active_ind.

2.1.2 Get Party by ID

Operation: getPartyById

Input Parameters:

party_id: integer (required) — party.party_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	—	Base table only; no joins

Filters:

party.party_id = :party_id

Computed Values:

None

Returns: One party row matching the given primary key (same fields as 2.1.1), or undefined if not found.

2.1.3 Get Party by Auth Party ID

Operation: getPartyByAuthPartyId

Input Parameters:

auth_party_id: string (required) — party.auth_party_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	—	Base table only; no joins

Filters:

party.auth_party_id = :auth_party_id

Computed Values:

None

Returns: One party row matching the external identity system identifier (same fields as 2.1.1), or undefined if not found.

2.1.4 Get Parties by IDs (Batch)

Operation: getPartiesByIds

Input Parameters:

party_ids: array of integers (required) — party.party_id values

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	—	Base table only; no joins

Filters:

party.party_id IN (:party_ids)
Guard: if party_ids is empty, returns [] without executing the query

Computed Values:

None

Returns: Array of party rows matching the given primary keys (same fields as 2.1.1); returns an empty array if the input is empty.

2.1.5 Search Parties

Operation: searchParties

Input Parameters:

query: string (required) — free-text search term

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	—	Base table only; no joins

Filters:

party.display_name ILIKE '%:query%' OR
party.company_name ILIKE '%:query%' OR
party.first_name ILIKE '%:query%' OR
party.last_name ILIKE '%:query%'
Guard: if query is empty, returns [] without executing the query
Result limit: 20 rows

Computed Values:

label: party.display_name ?? party.company_name ?? TRIM(party.first_name + ' ' + party.last_name) ?? 'Unknown Party' — resolved at the server action layer (see Section 3.1)

Returns: Array of up to 20 party rows matching any name field, transformed at the server action layer into { value: party.party_id, label: <resolved display name> } options for dropdowns and typeahead components.

2.2 Party Role Queries

2.2.1 Get All Party Roles

Operation: getAllPartyRoles

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_role`	—	—	—	Base table only; no joins

Filters:

None

Computed Values:

None

Returns: Array of all party_role rows, including party_role_id, party_id, party_role_type_cd, party_role_start_dt, party_role_end_dt, and party_role_active_ind.

2.2.2 Get Party Role by ID

Operation: getPartyRoleById

Input Parameters:

party_role_id: integer (required) — party_role.party_role_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_role`	—	—	—	Base table only; no joins

Filters:

party_role.party_role_id = :party_role_id

Computed Values:

None

Returns: One party_role row matching the given primary key (same fields as 2.2.1), or undefined if not found.

2.2.3 Get Party Roles by Party ID

Operation: getPartyRolesByPartyId

Input Parameters:

party_id: integer (required) — party_role.party_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_role`	—	—	—	Base table only; no joins

Filters:

party_role.party_id = :party_id

Computed Values:

None

Returns: Array of party_role rows for the specified party (same fields as 2.2.1).

2.3 Party Bank Account Queries

2.3.1 Get All Party Bank Accounts

Operation: getAllPartyBankAccounts

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_bank_account`	—	—	—	Base table only; no joins

Filters:

None

Computed Values:

None

Returns: Array of all party_bank_account rows, including party_bank_account_id, party_id, party_bank_type_cd, bank_account_id, party_bank_account_start_dt, party_bank_account_end_dt, active_ind, and preferred_payment_method.

2.3.2 Get Party Bank Account by ID

Operation: getPartyBankAccountById

Input Parameters:

party_bank_account_id: integer (required) — party_bank_account.party_bank_account_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_bank_account`	—	—	—	Base table only; no joins

Filters:

party_bank_account.party_bank_account_id = :party_bank_account_id

Computed Values:

None

Returns: One party_bank_account row matching the given primary key (same fields as 2.3.1), or undefined if not found.

2.3.3 Get Party Bank Accounts by Party ID

Operation: getPartyBankAccountsByPartyId

Input Parameters:

party_id: integer (required) — party_bank_account.party_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_bank_account`	—	—	—	Base table only; no joins

Filters:

party_bank_account.party_id = :party_id

Computed Values:

None

Returns: Array of party_bank_account rows for the specified party (same fields as 2.3.1).

2.4 Party Addresses Queries

2.4.1 Get All Party Addresses

Operation: getAllPartyAddresses

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_addresses`	—	—	—	Base table only; no joins

Filters:

None

Computed Values:

None

Returns: Array of all party_addresses rows, including party_addresses_id, party_id, party_addresses_type_cd, primary_ind, and auth_address_id.

2.4.2 Get Party Address by ID

Operation: getPartyAddressById

Input Parameters:

party_addresses_id: integer (required) — party_addresses.party_addresses_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_addresses`	—	—	—	Base table only; no joins

Filters:

party_addresses.party_addresses_id = :party_addresses_id

Computed Values:

None

Returns: One party_addresses row matching the given primary key (same fields as 2.4.1), or undefined if not found.

2.4.3 Get Party Addresses by Party ID

Operation: getPartyAddressesByPartyId

Input Parameters:

party_id: integer (required) — party_addresses.party_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_addresses`	—	—	—	Base table only; no joins

Filters:

party_addresses.party_id = :party_id

Computed Values:

None

Returns: Array of party_addresses rows for the specified party (same fields as 2.4.1).

NOTE

PoC Artifact: In the PoC, this query is stubbed to return an empty array because the party_addresses schema does not yet expose a party_id column in a form that the ORM can join on. The production implementation must filter by party_id once the schema is finalized.

2.5 Deal Party Queries

These queries link parties to deals. They are among the most complex in the domain because they join across deal_party, party, code_master, party_bank_account, and bank_account.

2.5.1 Get Deal Parties by Deal ID

Operation: getDealPartiesByDealId

Input Parameters:

deal_id: integer (required) — deal_party.deal_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	LEFT	`party`	`party.party_id = deal_party.party_id`	Resolve party name fields
`deal_party`	LEFT	`code_master` (as `partyRole`)	`deal_party.party_role_type_cd = partyRole.code_master_cd AND partyRole.code_master_type = 'PARTY_ROLE_TYPE_CD'`	Resolve human-readable role name from code master

Filters:

deal_party.deal_id = :deal_id

Computed Values:

None

Returns: Array of deal party rows for the specified deal, each including deal_party_id, deal_id, party_id, party_role_type_cd, party_role_name (from code_master.code_master_desc), deal_party_commission_flat_ind, deal_party_commission_perc, deal_party_commission_amt, bank_account_id, first_name, last_name, company_name, and display_name.

2.5.2 Get Deal Parties for Multiple Deals (Batch)

Operation: getDealPartiesForDealIds

Input Parameters:

deal_ids: array of integers (required) — deal_party.deal_id values

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	LEFT	`party`	`party.party_id = deal_party.party_id`	Resolve party name fields
`deal_party`	LEFT	`code_master` (as `partyRole`)	`deal_party.party_role_type_cd = partyRole.code_master_cd AND partyRole.code_master_type = 'PARTY_ROLE_TYPE_CD'`	Resolve human-readable role name from code master

Filters:

deal_party.deal_id IN (:deal_ids)

Computed Values:

None

Returns: Array of deal party rows across all specified deals (same fields as 2.5.1); the application layer groups results by deal_id to associate parties with their respective deals.

2.5.3 Get Deals by Client ID

Operation: getDealsByClientId

Input Parameters:

client_id: integer (required) — party.party_id of the client

Tables & Joins (Step 1 — identify deal IDs):

Base Table	Join	Joined Table	Condition	Purpose
`deal`	INNER	`deal_party`	`deal.deal_id = deal_party.deal_id`	Filter deals to those where the client participates

Filters (Step 1):

deal_party.party_id = :client_id

Tables & Joins (Step 2 — fetch deal details with aggregates):

Base Table	Join	Joined Table	Condition	Purpose
`deal`	LEFT	`deal_engine_sales_item`	`deal.deal_id = deal_engine_sales_item.deal_id`	Aggregate financial summary per deal

Filters (Step 2):

deal.deal_id IN (deal IDs from Step 1)

Computed Values:

sales_item_count: COUNT(deal_engine_sales_item.deal_engine_sales_item_id) grouped by deal.deal_id
total_gross_amt: COALESCE(SUM(deal_engine_sales_item.gross_amt), 0) grouped by deal.deal_id
total_commission_amt: COALESCE(SUM(deal_engine_sales_item.uta_commission_amt), 0) grouped by deal.deal_id

Returns: Array of deal rows (ordered by deal.deal_id DESC) enriched with computed financial aggregates and a parties array populated via the batch deal party query (2.5.2), each row including deal_id, deal_reference, deal_name, deal_start_dt, deal_end_dt, active_ind, sales_item_count, total_gross_amt, total_commission_amt, and parties.

2.5.4 Get Deal Party Roles by Deal ID (Statement Context)

Operation: getDealPartyRoles

Input Parameters:

deal_id: integer (required) — deal_party.deal_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	LEFT	`party`	`party.party_id = deal_party.party_id`	Resolve party display name

Filters:

deal_party.deal_id = :deal_id

Computed Values:

None — role filtering (CLIENT, BUYER, LOANOUT) is applied in the application layer after retrieval

Returns: Array of { role: deal_party.party_role_type_cd, party_name: party.display_name } rows for the deal; the calling context extracts specific roles to resolve client, buyer, and loanout party names for statement generation.

2.5.5 Get Deal Payees with Commission (Statement Context)

Operation: getDealPayees

Input Parameters:

deal_id: integer (required) — deal_party.deal_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	LEFT	`party`	`party.party_id = deal_party.party_id`	Resolve party name fields
`deal_party`	LEFT	`bank_account`	`bank_account.bank_account_id = deal_party.bank_account_id`	Resolve deal-level bank account name

Filters:

deal_party.deal_id = :deal_id
Ordering: deal_party.deal_party_commission_perc DESC

Computed Values:

percent: deal_party.deal_party_commission_perc parsed to a numeric type in the application layer
amount: deal_party.deal_party_commission_amt parsed to a numeric type in the application layer

Returns: Array of payee rows ordered by commission percentage descending, each including party_id, party_name (party.display_name), party_company (party.company_name), role (deal_party.party_role_type_cd), percent, amount, and bank_name (bank_account.bank_account_name).

2.5.6 Get Settlement Default Parties with Active Bank Accounts

Operation: getSettlementDefaultParties

Input Parameters:

deal_id: integer (required) — deal_party.deal_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	INNER	`party`	`party.party_id = deal_party.party_id`	Resolve party name; INNER ensures only parties with valid records are returned
`deal_party`	LEFT	`party_bank_account`	`party_bank_account.party_id = party.party_id AND party_bank_account.active_ind = true`	Resolve the party's currently active bank account link
`deal_party`	LEFT	`bank_account`	`bank_account.bank_account_id = party_bank_account.bank_account_id`	Resolve bank account name and number for display

Filters:

deal_party.deal_id = :deal_id

Computed Values:

bank_account_name: if bank_account.bank_account_name is not null, formatted as "{bank_account_name} (...{last 4 characters of bank_account_no})", otherwise falls back to bank_account.bank_account_no (see Section 3.2)

Returns: Array of deal party rows enriched with active bank account details, each including party_id, party_name (party.display_name), party_role_type_cd, deal_party_commission_flat_ind, deal_party_commission_perc, deal_party_commission_amt, bank_account_id, and the computed bank_account_name.

IMPORTANT

This query resolves bank accounts through the party_bank_account bridge table filtered by active_ind = true, not from the deal-level deal_party.bank_account_id override. This is distinct from 2.5.5, which reads the deal-level bank override. The settlement creation flow uses this query to pre-populate the settlement form with the party's currently active account.

2.5.7 Search Deals by Party Name

Operation: searchDealsByPartyName

Input Parameters:

query: string (required) — free-text search term; minimum length 2 characters

Tables & Joins (Step 1 — find matching deals):

Base Table	Join	Joined Table	Condition	Purpose
`deal`	LEFT	`deal_party`	`deal.deal_id = deal_party.deal_id`	Expose party records to filter on
`deal`	LEFT	`party`	`deal_party.party_id = party.party_id`	Match search term against party display name

Filters (Step 1, combined with OR):

deal.deal_name ILIKE '%:query%'
deal.deal_reference ILIKE '%:query%'
party.display_name ILIKE '%:query%'
CAST(deal.deal_id AS TEXT) LIKE '%:query%'
Uses SELECT DISTINCT to prevent duplicate deals from matching multiple party rows
Result limit: 20 rows

Tables & Joins (Step 2 — fetch party names for matched deals):

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	INNER	`party`	`deal_party.party_id = party.party_id`	Collect all party display names for the matched deals

Filters (Step 2):

deal_party.deal_id IN (deal IDs from Step 1)

Computed Values:

client_names: unique array of party.display_name values aggregated per deal_id in the application layer

Returns: Array of up to 20 deal search result rows, each including deal_id, deal_name, deal_reference, and client_names (deduplicated array of party display names for the deal).

2.5.8 Get Client Party from Billing Item Context

Operation: getClientPartyForBillingItem

Input Parameters:

Implicit — executed as part of a billing item query; the billing item filter varies by calling context

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	LEFT	`sales_item`	`sales_item.sales_item_id = billing_item.revenue_item_id`	Traverse from billing item to its parent revenue/sales item
`billing_item`	LEFT	`deal`	`deal.deal_id = sales_item.deal_id`	Traverse from sales item to its deal
`billing_item`	LEFT	`deal_party`	`deal_party.deal_id = deal.deal_id AND deal_party.party_role_type_cd = 'CLIENT'`	Locate the CLIENT role participant on the deal
`billing_item`	LEFT	`party`	`party.party_id = deal_party.party_id`	Resolve the client's display name

Filters:

Applied at the billing item level; varies by calling context (AR aging, deal statement, cash worksheet)

Computed Values:

None

Returns: The client_name (party.display_name) and client_id (party.party_id) for the party holding the CLIENT role on the deal associated with the billing item.

2.5.9 Filter Sales Items by Client Party

Operation: filterSalesItemsByClientParty

Input Parameters:

client_id: integer (required) — party.party_id of the client to filter by

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_engine_sales_item`	INNER	`deal_party`	`deal_engine_sales_item.deal_id = deal_party.deal_id AND deal_party.party_id = :client_id`	Restrict sales items to deals where the specified party participates; INNER ensures only matching deals are returned

Filters:

deal_party.party_id = :client_id (applied as part of the INNER JOIN condition)

Computed Values:

Varies by calling context; financial aggregations on deal_engine_sales_item fields are computed by the outer query

Returns: deal_engine_sales_item rows scoped to deals where the specified client is a participant; the deal_party join acts purely as a filter and contributes no selected fields.

3. Calculations & Formulas

3.1 Display Name Resolution

The display name resolution is applied consistently at the server action layer when returning party options for dropdowns and typeahead components.

text

label = party.display_name
     ?? party.company_name
     ?? TRIM(party.first_name + ' ' + party.last_name)
     ?? 'Unknown Party'

Source: party.display_name, party.company_name, party.first_name, party.last_name
Applied at: Server action layer (not at the database query level)
Example: A party with display_name = NULL, company_name = NULL, first_name = 'John', last_name = 'Smith' resolves to 'John Smith'

3.2 Bank Account Name Formatting (Settlement Context)

When displaying bank accounts in the settlement creation form, the account name is formatted to include a masked account number for identification.

text

IF bank_account.bank_account_name IS NOT NULL:
    formatted_name = "{bank_account_name} (...{LAST 4 CHARS OF bank_account_no})"
ELSE:
    formatted_name = bank_account.bank_account_no

Source: bank_account.bank_account_name, bank_account.bank_account_no
Applied at: Application layer within getSettlementDefaultParties (2.5.6)
Example: bank_account_name = 'John Smith Agency', bank_account_no = '000012345678' → 'John Smith Agency (...5678)'

3.3 Commission Structure Resolution

The deal_party commission fields use a flag-driven pattern to determine which commission value applies.

text

IF deal_party.deal_party_commission_flat_ind = true:
    commission_value = deal_party.deal_party_commission_amt    (fixed dollar amount)
ELSE:
    commission_value = deal_party.deal_party_commission_perc   (percentage of net)

Source: deal_party.deal_party_commission_flat_ind, deal_party.deal_party_commission_amt (decimal(15,2)), deal_party.deal_party_commission_perc (decimal(7,4))
Applied at: Application layer; raw flag and both amount fields are returned by the queries, and the consuming logic applies the flag check
Example: deal_party_commission_flat_ind = false, deal_party_commission_perc = '10.0000' → percentage-based commission of 10%

3.4 Deal Financial Aggregates

When fetching deals for a client (see 2.5.3), the following aggregates are computed at the database level, grouped by deal.deal_id.

text

sales_item_count    = COUNT(deal_engine_sales_item.deal_engine_sales_item_id)
total_gross_amt     = COALESCE(SUM(deal_engine_sales_item.gross_amt), 0)
total_commission_amt = COALESCE(SUM(deal_engine_sales_item.uta_commission_amt), 0)

Source: deal_engine_sales_item.deal_engine_sales_item_id, deal_engine_sales_item.gross_amt, deal_engine_sales_item.uta_commission_amt
Precision: decimal(15,2) for monetary amounts
Grouping: GROUP BY deal.deal_id, ordered by deal.deal_id DESC

4. Cross-References

Document	Relationship
Parties Data Model	Defines the schema, constraints, status lifecycles, and code master values for all tables covered in this queries document
Billing Items Data Model	`billing_item.client_id`, `billing_item.contracted_party_id`, `billing_item.buyer_id`, `billing_item.collection_party_id` → `party.party_id`; billing item queries join to `deal_party` via 2.5.8 to resolve client context
Deals, Sales Items & Payment Terms Data Model	`deal_party` bridges `deal` and `party`; deal queries depend on party queries for name resolution
Settlements Data Model	Settlement defaults are derived from `deal_party` commission structures via 2.5.6; settlement items reference `party.party_id` as the payment party
Cash Receipts Data Model	Receipt references and client ledger entries reference `party.party_id`; party typeahead during receipt creation uses 2.1.5
Tax Withholding Data Model	`party_tax_info.party_id` → `party.party_id`; tax calculation uses party identity resolved via 2.1.2 to determine jurisdiction rules

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Parties Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Party Queries ​

2.1.1 Get All Parties ​

2.1.2 Get Party by ID ​

2.1.3 Get Party by Auth Party ID ​

2.1.4 Get Parties by IDs (Batch) ​

2.1.5 Search Parties ​

2.2 Party Role Queries ​

2.2.1 Get All Party Roles ​

2.2.2 Get Party Role by ID ​

2.2.3 Get Party Roles by Party ID ​

2.3 Party Bank Account Queries ​

2.3.1 Get All Party Bank Accounts ​

2.3.2 Get Party Bank Account by ID ​

2.3.3 Get Party Bank Accounts by Party ID ​

2.4 Party Addresses Queries ​

2.4.1 Get All Party Addresses ​

2.4.2 Get Party Address by ID ​

2.4.3 Get Party Addresses by Party ID ​

2.5 Deal Party Queries ​

2.5.1 Get Deal Parties by Deal ID ​

2.5.2 Get Deal Parties for Multiple Deals (Batch) ​

2.5.3 Get Deals by Client ID ​

2.5.4 Get Deal Party Roles by Deal ID (Statement Context) ​

2.5.5 Get Deal Payees with Commission (Statement Context) ​

2.5.6 Get Settlement Default Parties with Active Bank Accounts ​

2.5.7 Search Deals by Party Name ​

2.5.8 Get Client Party from Billing Item Context ​

2.5.9 Filter Sales Items by Client Party ​

3. Calculations & Formulas ​

3.1 Display Name Resolution ​

3.2 Bank Account Name Formatting (Settlement Context) ​

3.3 Commission Structure Resolution ​

3.4 Deal Financial Aggregates ​

4. Cross-References ​

Parties Queries

1. Executive Summary

2. Key Queries

2.1 Party Queries

2.1.1 Get All Parties

2.1.2 Get Party by ID

2.1.3 Get Party by Auth Party ID

2.1.4 Get Parties by IDs (Batch)

2.1.5 Search Parties

2.2 Party Role Queries

2.2.1 Get All Party Roles

2.2.2 Get Party Role by ID

2.2.3 Get Party Roles by Party ID

2.3 Party Bank Account Queries

2.3.1 Get All Party Bank Accounts

2.3.2 Get Party Bank Account by ID

2.3.3 Get Party Bank Accounts by Party ID

2.4 Party Addresses Queries

2.4.1 Get All Party Addresses

2.4.2 Get Party Address by ID

2.4.3 Get Party Addresses by Party ID

2.5 Deal Party Queries

2.5.1 Get Deal Parties by Deal ID

2.5.2 Get Deal Parties for Multiple Deals (Batch)

2.5.3 Get Deals by Client ID

2.5.4 Get Deal Party Roles by Deal ID (Statement Context)

2.5.5 Get Deal Payees with Commission (Statement Context)

2.5.6 Get Settlement Default Parties with Active Bank Accounts

2.5.7 Search Deals by Party Name

2.5.8 Get Client Party from Billing Item Context

2.5.9 Filter Sales Items by Client Party

3. Calculations & Formulas

3.1 Display Name Resolution

3.2 Bank Account Name Formatting (Settlement Context)

3.3 Commission Structure Resolution

3.4 Deal Financial Aggregates

4. Cross-References