Tax Withholding Queries

1. Executive Summary

This document catalogs the key read operations, computed lookups, and aggregation queries that power the tax withholding domain. These queries service three layers of the system:

Party tax profile management — retrieving, versioning, and monitoring the master tax data stored in party_tax_info
Tax rate resolution — looking up effective-dated rates and thresholds from tax_rate_config at calculation time
Tax calculation auditing — recording and retrieving immutable audit snapshots from tax_calculation_audit
Tax form data aggregation — querying actual cash applications and deductions to populate IRS and HMRC tax forms stored in tax_form, and assembling filing packages in tax_filing_package

The queries range from simple single-table lookups (get a party's current tax profile) to multi-table aggregations that span the worksheet, application, deduction, and billing hierarchies (aggregate withholding totals for a tax period). Several queries involve computed values — YTD cumulative earnings, threshold comparisons, proactive withholding flags — that drive the jurisdiction-specific tax rules.

Related data model: Tax Withholding Data Model

2. Key Queries

2.1 Party Tax Profile Queries

2.1.1 Get Current Tax Profile by Party

Operation: getCurrentTaxProfileByParty

Input Parameters:

partyId: Integer (required) — party_tax_info.party_id

Tables & Joins:

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

Filters:

party_tax_info.party_id = :partyId
party_tax_info.current_item_ind = true
LIMIT 1

Computed Values:

None

Returns: One row (or null) representing the single active tax profile for the party, enriched with party.display_name.

2.1.2 Get All Tax Profiles by Party

Operation: getAllTaxProfilesByParty

Input Parameters:

partyId: Integer (required) — party_tax_info.party_id

Tables & Joins:

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

Filters:

party_tax_info.party_id = :partyId

Computed Values:

None

Returns: All tax profile rows for the party across all historical versions, ordered by creation date, each enriched with party.display_name.

2.1.3 Get Tax Profile by ID

Operation: getTaxProfileById

Input Parameters:

partyTaxInfoId: Integer (required) — party_tax_info.party_tax_info_id

Tables & Joins:

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

Filters:

party_tax_info.party_tax_info_id = :partyTaxInfoId
LIMIT 1

Computed Values:

None

Returns: One row (or null) for the specified tax profile record, enriched with party.display_name.

2.2 Compliance Monitoring Queries

2.2.1 Find Expiring CWAs

Operation: findExpiringCwas

Input Parameters:

daysAhead: Integer (optional, default 30) — number of days ahead to check for expiration

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_tax_info`	LEFT	`party`	`party_tax_info.party_id = party.party_id`	Resolve party name for alert display

Filters:

party_tax_info.current_item_ind = true
party_tax_info.has_cwa_approval_ind = true
party_tax_info.cwa_expiration_dt IS NOT NULL
party_tax_info.cwa_expiration_dt <= today + :daysAhead days
party_tax_info.cwa_expiration_dt >= today (not yet expired)

Computed Values:

days_until_expiration: CEIL((cwa_expiration_dt - CURRENT_DATE) / 86400000), floored at 0

Returns: Array of expiring CWA alerts, each with party_id, party_name, document type 'CWA', expiration_dt, and days_until_expiration, ordered ascending by days_until_expiration.

2.2.2 Find Expiring W-8 Forms

Operation: findExpiringW8s

Input Parameters:

daysAhead: Integer (optional, default 30) — number of days ahead to check for expiration

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party_tax_info`	LEFT	`party`	`party_tax_info.party_id = party.party_id`	Resolve party name for alert display

Filters:

party_tax_info.current_item_ind = true
party_tax_info.has_w8_on_file_ind = true
party_tax_info.w8_expiration_dt IS NOT NULL
party_tax_info.w8_expiration_dt <= today + :daysAhead days
party_tax_info.w8_expiration_dt >= today (not yet expired)

Computed Values:

days_until_expiration: CEIL((w8_expiration_dt - CURRENT_DATE) / 86400000), floored at 0

Returns: Array of expiring W-8 alerts, each with party_id, party_name, document type 'W8', expiration_dt, and days_until_expiration, ordered ascending by days_until_expiration.

2.3 Tax Rate Configuration Queries

2.3.1 Get Active Rate for Jurisdiction and Tax Type

Operation: getActiveRateConfig

Input Parameters:

jurisdictionCd: String (required) — tax_rate_config.jurisdiction_cd (e.g., 'US', 'GB')
taxTypeCd: String (required) — tax_rate_config.tax_type_cd (e.g., 'WITHHOLDING_NRA', 'WITHHOLDING_FEU')
asOfDate: Date (optional, defaults to current date) — the date for which the effective rate is needed

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`tax_rate_config`	—	—	—	Single-table lookup; no joins required

Filters:

tax_rate_config.jurisdiction_cd = :jurisdictionCd
tax_rate_config.tax_type_cd = :taxTypeCd
tax_rate_config.effective_from_dt <= :asOfDate
tax_rate_config.effective_to_dt IS NULL OR tax_rate_config.effective_to_dt >= :asOfDate
ORDER BY effective_from_dt DESC, LIMIT 1

Computed Values:

rate: CAST(rate AS DECIMAL) — parsed from stored string to numeric
threshold_amt: CAST(threshold_amt AS DECIMAL) — parsed from stored string to numeric

Returns: One row (or null) representing the effective rate configuration for the jurisdiction and tax type as of the given date, including rate, threshold_amt, currency_cd, and effective_from_dt.

2.3.2 Get All Active Rate Configurations

Operation: getAllActiveRateConfigs

Input Parameters:

None (uses current date as of-date)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`tax_rate_config`	—	—	—	Single-table scan; no joins required

Filters:

tax_rate_config.effective_to_dt IS NULL OR tax_rate_config.effective_to_dt >= today
ORDER BY jurisdiction_cd, tax_type_cd

Computed Values:

None

Returns: All currently active tax rate configuration rows ordered by jurisdiction and tax type.

2.3.3 Get Rate Configurations by Jurisdiction

Operation: getRateConfigsByJurisdiction

Input Parameters:

jurisdictionCd: String (required) — tax_rate_config.jurisdiction_cd

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`tax_rate_config`	—	—	—	Single-table scan; no joins required

Filters:

tax_rate_config.jurisdiction_cd = :jurisdictionCd
ORDER BY tax_type_cd ASC, effective_from_dt DESC

Computed Values:

None

Returns: All tax rate configuration rows for the specified jurisdiction, including historical (expired) entries, ordered by tax type and most recent effective date first.

2.3.4 Get Tax Year Range from Configuration

Operation: getTaxYearRange

Input Parameters:

jurisdictionCd: String (required) — tax_rate_config.jurisdiction_cd
asOfDate: Date (optional, defaults to current date)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`tax_rate_config`	—	—	—	Single-table lookup; no joins required

Filters:

tax_rate_config.jurisdiction_cd = :jurisdictionCd
tax_rate_config.tax_type_cd = 'TAX_YEAR'
tax_rate_config.effective_from_dt <= :asOfDate
tax_rate_config.effective_to_dt IS NULL OR tax_rate_config.effective_to_dt >= :asOfDate
ORDER BY effective_from_dt DESC, LIMIT 1

Computed Values:

start: parsed from effective_from_dt
end: parsed from effective_to_dt if set; otherwise December 31 of the effective_from_dt year

Returns: One row (or null) encoding the tax year date range for the jurisdiction as configured in tax_rate_config, used to determine which billing items fall within the current UK tax year for FEU proactive withholding.

NOTE

If no TAX_YEAR entry is found, the caller falls back to the calendar year (January 1 – December 31 of the current year). For UK, the configured entry represents April 6 – April 5 of the following year.

2.4 Tax Calculation Audit Queries

2.4.1 Get Audit Records by Context

Operation: getTaxAuditByContext

Input Parameters:

contextTypeCd: String (required) — tax_calculation_audit.context_type_cd (e.g., 'DEDUCTION_APPLIED', 'SPLIT_WARNING', 'WORKSHEET_PREVIEW')
contextId: Integer (required) — tax_calculation_audit.context_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`tax_calculation_audit`	—	—	—	Single-table scan; no joins required

Filters:

tax_calculation_audit.context_type_cd = :contextTypeCd
tax_calculation_audit.context_id = :contextId
ORDER BY calculated_dt DESC

Computed Values:

None

Returns: All audit records for the specified context type and context ID, ordered most recent first, including full input_json, result_json, party_tax_info_snapshot_json, and rate_config_snapshot_json payloads.

2.4.2 Get Audit Record by ID

Operation: getTaxAuditById

Input Parameters:

taxCalculationAuditId: Integer (required) — tax_calculation_audit.tax_calculation_audit_id

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`tax_calculation_audit`	—	—	—	Single-table lookup; no joins required

Filters:

tax_calculation_audit.tax_calculation_audit_id = :taxCalculationAuditId
LIMIT 1

Computed Values:

None

Returns: One audit record (or null) with the complete input, result, and point-in-time snapshots for the specified calculation audit event.

2.5 Tax Form Data Aggregation Queries

2.5.1 Get PAY Applications for Party in Tax Period

Operation: getPayApplicationsForParty

Input Parameters:

partyId: Integer (required) — billing_item.client_id
periodStart: Date (required) — start of the tax period (derived from getTaxPeriod)
periodEnd: Date (required) — end of the tax period (derived from getTaxPeriod)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	INNER	`cash_receipt_worksheet`	`cash_receipt_application.cash_receipt_worksheet_id = cash_receipt_worksheet.cash_receipt_worksheet_id`	Filter to active worksheets and access `applied_dt`
`cash_receipt_application`	INNER	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Confirm PAY detail type
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Access `client_id`, `uta_entity_id`
`billing_item`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Resolve deal name for lineage
`billing_item`	LEFT	`uta_entity`	`billing_item.uta_entity_id = uta_entity.uta_entity_id`	Resolve billing entity name

Filters:

billing_item.client_id = :partyId
billing_item_detail.billing_item_detail_type_cd = 'PAY'
cash_receipt_worksheet.cash_receipt_worksheet_status_cd IN ('P', 'T', 'A') — active (Applied, Settled, Approved) worksheets only
cash_receipt_application.reversal_of_application_id IS NULL — exclude reversal applications
cash_receipt_worksheet.applied_dt >= :periodStart
cash_receipt_worksheet.applied_dt <= :periodEnd

Computed Values:

application_amt: CAST(cash_receipt_application.cash_receipt_amt_applied AS DECIMAL)

Returns: Array of PAY application records within the tax period for the party, each with application amounts, deal name, billing item name, worksheet applied date, and entity name for form field population.

2.5.2 Get Withholding Deductions for Applications

Operation: getWithholdingDeductionsForApplications

Input Parameters:

applicationIds: Array of Integers (required) — cash_receipt_application_deduction.cash_receipt_application_id
deductionTypeCds: Array of Strings (required) — cash_receipt_application_deduction.billing_item_deduction_type_cd (e.g., ['WH_US_NRA', 'WH_UK_FEU'])

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application_deduction`	INNER	`cash_receipt_application`	`cash_receipt_application_deduction.cash_receipt_application_id = cash_receipt_application.cash_receipt_application_id`	Access worksheet ID for lineage
`cash_receipt_application`	INNER	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Navigate to billing item
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Access deal and billing item name
`billing_item`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Resolve deal name for lineage

Filters:

cash_receipt_application_deduction.cash_receipt_application_id IN (:applicationIds)
cash_receipt_application_deduction.billing_item_deduction_type_cd IN (:deductionTypeCds)

Computed Values:

deduction_amt: CAST(cash_receipt_application_deduction.deduction_amt_applied AS DECIMAL)

Returns: Array of withholding deduction records for the specified applications and deduction types, each with deduction amounts and deal/billing item lineage for source map construction.

2.5.3 Aggregate PAY Applications and Withholding Deductions for Period

Operation: aggregateForPeriod

Input Parameters:

partyId: Integer (required) — billing_item.client_id
periodStart: Date (required) — start of the tax period
periodEnd: Date (required) — end of the tax period
withholdingTypeCds: Array of Strings (required) — deduction type codes to aggregate (e.g., ['WH_US_NRA'])

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application`	INNER	`cash_receipt_worksheet`	`cash_receipt_application.cash_receipt_worksheet_id = cash_receipt_worksheet.cash_receipt_worksheet_id`	Filter to active worksheets; date range
`cash_receipt_application`	INNER	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Confirm PAY type
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Filter by `client_id`
`billing_item`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Deal name for lineage
`billing_item`	LEFT	`uta_entity`	`billing_item.uta_entity_id = uta_entity.uta_entity_id`	Entity name for lineage
`cash_receipt_application_deduction`	INNER	`cash_receipt_application`	`cash_receipt_application_deduction.cash_receipt_application_id = cash_receipt_application.cash_receipt_application_id`	Join deductions to applications

Filters:

billing_item.client_id = :partyId
billing_item_detail.billing_item_detail_type_cd = 'PAY'
cash_receipt_worksheet.cash_receipt_worksheet_status_cd IN ('P', 'T', 'A')
cash_receipt_application.reversal_of_application_id IS NULL
cash_receipt_worksheet.applied_dt >= :periodStart
cash_receipt_worksheet.applied_dt <= :periodEnd
cash_receipt_application_deduction.billing_item_deduction_type_cd IN (:withholdingTypeCds)

Computed Values:

total_gross: SUM(cash_receipt_amt_applied) across all PAY applications
total_withholding: SUM(deduction_amt_applied) across all matching deduction records

Returns: Aggregated totals (total_gross, total_withholding) plus the individual applications and deductions arrays for source lineage construction in tax form population.

2.5.4 Find Parties with Withholding in Period

Operation: getPartiesWithWithholding

Input Parameters:

periodStart: Date (required) — start of the tax period
periodEnd: Date (required) — end of the tax period
deductionTypeCds: Array of Strings (required) — withholding deduction type codes to match

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application_deduction`	INNER	`cash_receipt_application`	`cash_receipt_application_deduction.cash_receipt_application_id = cash_receipt_application.cash_receipt_application_id`	Navigate to worksheet
`cash_receipt_application`	INNER	`cash_receipt_worksheet`	`cash_receipt_application.cash_receipt_worksheet_id = cash_receipt_worksheet.cash_receipt_worksheet_id`	Filter to active worksheets and date range
`cash_receipt_application`	INNER	`billing_item_detail`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id`	Navigate to billing item
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Access `client_id`
`billing_item`	INNER	`party`	`billing_item.client_id = party.party_id`	Resolve party name

Filters:

cash_receipt_application_deduction.billing_item_deduction_type_cd IN (:deductionTypeCds)
cash_receipt_worksheet.cash_receipt_worksheet_status_cd IN ('P', 'T', 'A')
cash_receipt_application.reversal_of_application_id IS NULL
cash_receipt_worksheet.applied_dt >= :periodStart
cash_receipt_worksheet.applied_dt <= :periodEnd
SELECT DISTINCT on billing_item.client_id

Computed Values:

None

Returns: Distinct list of { party_id, party_name } entries for all parties that had withholding deductions applied within the tax period, used to auto-discover recipients for 1042-S form generation.

2.5.5 Aggregate 1042-S Forms for Tax Year

Operation: aggregate1042SFormsForYear

Input Parameters:

taxYear: Integer (required) — tax_form.tax_year

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`tax_form`	—	—	—	Single-table scan of generated forms; no joins required

Filters:

tax_form.form_code_cd = 'IRS_1042_S'
tax_form.tax_year = :taxYear
tax_form.status_cd IN ('GENERATED', 'FILED', 'SUBMITTED')

Computed Values:

total_gross: SUM(form_data_json->>'grossIncome') across all matched forms
total_withheld: SUM(form_data_json->>'federalTaxWithheld') across all matched forms
count: total number of 1042-S forms found
form_ids: array of tax_form_id values

Returns: Aggregate totals (count, total_gross, total_withheld) and the list of contributing form_ids for use by Form 1042 (annual return) and Form 1042-T (transmittal) generation.

2.5.6 Get Tax Liability by Semi-Monthly Period

Operation: getTaxLiabilityByPeriod

Input Parameters:

taxYear: Integer (required) — calendar year for which to compute IRS 1042 Section 1 deposit liability
deductionTypeCds: Array of Strings (required) — withholding deduction type codes to bucket (typically ['WH_US_NRA'])

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_application_deduction`	INNER	`cash_receipt_application`	`cash_receipt_application_deduction.cash_receipt_application_id = cash_receipt_application.cash_receipt_application_id`	Navigate to worksheet
`cash_receipt_application`	INNER	`cash_receipt_worksheet`	`cash_receipt_application.cash_receipt_worksheet_id = cash_receipt_worksheet.cash_receipt_worksheet_id`	Access `applied_dt` for period bucketing

Filters:

cash_receipt_application_deduction.billing_item_deduction_type_cd IN (:deductionTypeCds)
cash_receipt_worksheet.cash_receipt_worksheet_status_cd IN ('P', 'T', 'A')
cash_receipt_application.reversal_of_application_id IS NULL
cash_receipt_worksheet.applied_dt >= January 1 of :taxYear
cash_receipt_worksheet.applied_dt <= December 31 of :taxYear

Computed Values:

period1: SUM of deductions where DAY(applied_dt) is 1–7
period2: SUM of deductions where DAY(applied_dt) is 8–15
period3: SUM of deductions where DAY(applied_dt) is 16–22
period4: SUM of deductions where DAY(applied_dt) is 23–end of month
total: sum of all four period amounts per month

Returns: A map of month numbers (1–12) to PeriodTaxLiability objects, each containing the four semi-monthly deposit period amounts and a monthly total, for population of IRS Form 1042 Section 1.

2.6 Worksheet Tax Preview Queries

2.6.1 Get Tax Previews for Worksheet

Operation: getTaxPreviewsForWorksheet

This is a compound query sequence — not a single SQL statement — because it must resolve billing entity context, party profiles, UK cumulative earnings, and rate configuration before invoking the tax calculation engine. The sub-queries are documented in order of execution.

Input Parameters:

worksheetId: Integer (required) — cash_receipt_worksheet.cash_receipt_worksheet_id
billingItemIds: Array of Integers (required) — billing_item.billing_item_id values on the worksheet

Step 1: Billing Item Context

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	—	—	—	Read `uta_entity_id`, `client_id`, `contracted_party_id`, `service_country_cd`

Filters: billing_item.billing_item_id IN (:billingItemIds)

Step 2: Gross Amounts and Commission Rates

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail`	—	—	—	Read gross amount and commission percent from REV detail

Filters:

billing_item_detail.billing_item_id IN (:billingItemIds)
billing_item_detail.billing_item_detail_type_cd = 'REV'

Step 3: Split Amount for VAT Validation

Base Table	Join	Joined Table	Condition	Purpose
`cash_receipt_worksheet`	INNER	`cash_receipt_split`	`cash_receipt_worksheet.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id`	Retrieve split amount for UK Artist Fee VAT split validation

Filters: cash_receipt_worksheet.cash_receipt_worksheet_id = :worksheetId

Step 4: Billing Entity Name Resolution

Base Table	Join	Joined Table	Condition	Purpose
`uta_entity`	—	—	—	Resolve entity name to map to billing entity code (`US_AGENCY` / `UK_AGENCY`)

Filters: uta_entity.uta_entity_id IN (distinct entity IDs from billing items)

Step 5: Party Tax Profiles

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	—	Resolve party display name
`party_tax_info`	—	—	—	Read current tax profile for withholding context

Filters:

party.party_id IN (distinct party IDs from billing items)
party_tax_info.party_id IN (distinct party IDs)
party_tax_info.current_item_ind = true

Step 6: UK Cumulative Receivables (UK_AGENCY items only)

See query 2.6.2 Get UK Cumulative Receivables for Party.

Computed Values:

billing_entity_cd: mapped from uta_entity.uta_entity_name to 'US_AGENCY' or 'UK_AGENCY'
service_location_cd: mapped from billing_item.service_country_cd to trigger jurisdiction code
uk_ytd_earnings: ukCumulative.ytdTotal - currentGrossAmt (prior earnings only; never negative)
uk_expected_ytd_total: ukCumulative.ytdTotal (all current-year billing items for the party)
aware_of_exceeding_threshold: ukExpectedYtdTotal >= 12570

Returns: A map of billing_item_id → TaxDeductionResult, where each result contains deduction suggestions, compliance warnings, required forms, compliance alerts, and a display summary badge, derived from the tax calculation engine applied to each billing item's context.

2.6.2 Get UK Cumulative Receivables for Party

Operation: getUkCumulativeReceivables

Input Parameters:

partyId: Integer (required) — billing_item.client_id or billing_item.contracted_party_id
ukEntityIds: Array of Integers (required) — billing_item.uta_entity_id values that map to UK_AGENCY
currentBillingItemId: Integer (required) — the billing item currently being evaluated; used to flag is_current_item

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	INNER	`billing_item_detail`	`billing_item_detail.billing_item_id = billing_item.billing_item_id AND billing_item_detail.billing_item_detail_type_cd = 'REV'`	Read gross amount from REV detail
`billing_item`	LEFT	`deal`	`billing_item.deal_id = deal.deal_id`	Resolve deal name for display

Filters:

(billing_item.contracted_party_id = :partyId OR billing_item.client_id = :partyId)
billing_item.uta_entity_id IN (:ukEntityIds)
billing_item.billing_item_due_dt >= start of earliest relevant UK tax year
billing_item.billing_item_due_dt <= end of latest relevant UK tax year (typically current + prior year)

Computed Values:

ytd_total: SUM(billing_item_detail.billing_item_detail_gross_amt) for items falling within the current UK tax year only (determined by comparing billing_item_due_dt against the configured TAX_YEAR range from tax_rate_config)
tax_year_label: label string (e.g., '2025/26') indicating which UK tax year each item belongs to
is_current_item: billing_item_id = :currentBillingItemId

Returns: An object with receivables (full list of billing items spanning current and prior UK tax year, each tagged with tax_year_label and is_current_item) and ytd_total (current-year-only sum for FEU withholding threshold comparison and proactive withholding determination).

IMPORTANT

ytd_total uses billing_item_due_dt (revenue start date), not created_dt, to assign receivables to UK tax years. The current-year-only total is then passed as uk_expected_ytd_total to the withholding context, where aware_of_exceeding_threshold is set to true if uk_expected_ytd_total >= 12570. This drives proactive FEU withholding even when actual YTD actuals (cash_receipt_application records) are still below the threshold.

3. Calculations & Formulas

3.1 UK FEU Proactive Withholding Flag

text

uk_expected_ytd_total = SUM(billing_item_detail_gross_amt)
    WHERE billing_item.uta_entity_id IN (uk_entity_ids)
      AND (billing_item.contracted_party_id = partyId OR billing_item.client_id = partyId)
      AND billing_item_due_dt WITHIN current UK tax year
      AND billing_item_detail_type_cd = 'REV'

aware_of_exceeding_threshold = (uk_expected_ytd_total >= 12570)

Source: billing_item_detail.billing_item_detail_gross_amt, billing_item.billing_item_due_dt, tax_rate_config (TAX_YEAR entry for 'GB')
Precision: decimal(15,2)
Threshold: 12570.00 GBP (from tax_rate_config where tax_type_cd = 'PERSONAL_ALLOWANCE')
Example: Party has £5,000 in YTD actuals but £80,000 in total billing items for the tax year. uk_expected_ytd_total = £80,000 >= £12,570, so aware_of_exceeding_threshold = true and FEU is withheld proactively at 20%.

3.2 UK FEU Withholding Amount

text

current_gross_gbp = grossAmt                                  -- if fee is already GBP
current_gross_gbp = ROUND(grossAmt / fxRate, 2)               -- if fee is non-GBP

cumulative = uk_ytd_earnings + current_gross_gbp

IF (cumulative >= 12570) OR aware_of_exceeding_threshold:
    withholding_amt_gbp = ROUND(current_gross_gbp * 0.20)     -- nearest whole number

ELSE:
    withholding_amt_gbp = 0

IF fee is non-GBP:
    withholding_amt_original_currency = ROUND(withholding_amt_gbp * fxRate)

Source: party_tax_info.cwa_withholding_amt, tax_rate_config.rate (WITHHOLDING_FEU), tax_rate_config.threshold_amt (PERSONAL_ALLOWANCE)
Precision: Withholding rounded to nearest whole number (no decimal); currency conversion rounded to 2 decimal places
FX rate convention: Rate is expressed as currency units per 1 GBP (divide to convert to GBP; multiply to convert back)
Example: Fee of USD 10,000 at FX rate 1.25 (USD/GBP) = GBP 8,000 × 20% = GBP 1,600 withheld; converted back = USD 2,000

3.3 US NRA Withholding Amount

text

IF service_location != US AND service_location IS NOT NULL:
    withholding_amt = 0   -- Foreign Source Income; no US withholding

ELSE IF talent_residence = 'US':
    withholding_amt = 0   -- US Person; 1099-NEC reporting only

ELSE IF has_cwa_approval AND NOT cwa_expired:
    withholding_amt = ROUND(cwa_withholding_amt)   -- IRS-approved flat dollar amount

ELSE IF has_tax_treaty:
    withholding_amt = 0   -- Treaty exemption; Form 8233 required

ELSE:
    withholding_amt = ROUND(gross_amt * 0.30)   -- Standard 30% NRA rate

Source: party_tax_info.has_cwa_approval_ind, party_tax_info.cwa_withholding_amt, party_tax_info.has_tax_treaty_ind, tax_rate_config.rate (WITHHOLDING_NRA), billing_item.service_country_cd
Precision: Withholding rounded to nearest whole number (no decimal)
CWA amount: A flat dollar amount set by the IRS per engagement — not a percentage of gross
Example: US-source income of $50,000, non-resident alien with no CWA and no treaty: $50,000 × 0.30 = $15,000 withheld

3.4 UK VAT on Artist Fee (Pass-Through)

text

IF billing_entity = 'UK_AGENCY'
   AND talent_residence = 'GB'
   AND service_location = 'GB'
   AND vat_registered_ind = true:

    vat_amt = FLOOR(gross_amt * 0.20)
    expected_invoice_total = gross_amt + vat_amt

Source: party_tax_info.vat_registered_ind, billing_item.service_country_cd, tax_rate_config.rate (VAT_ARTIST_FEE)
Precision: Floored to whole number (not rounded)
Nature: Pass-through — charged to the buyer on top of the gross invoice. UTA collects it and passes it through to the client. Not a deduction against the billing item.
Example: Gross £10,000, UK resident + UK show + VAT registered: FLOOR(£10,000 × 0.20) = £2,000 VAT; buyer pays £12,000 total

3.5 UK VAT on Commission

text

IF billing_entity = 'UK_AGENCY'
   AND talent_residence = 'GB':

    commission_amt = ROUND(gross_amt * commission_rate, 2)
    commission_vat_amt = FLOOR(commission_amt * 0.20)

Source: billing_item_detail.billing_item_detail_percent (commission rate), tax_rate_config.rate (VAT_COMMISSION)
Precision: Commission floored to whole number after commission calculation
Nature: UTA's VAT obligation on its agency fee. Accounted for within the buyer's existing payment — NOT charged additionally to the buyer. Applied as a PAY deduction (type 'VAT_COMM'), reducing what the client receives.
Example: Gross £10,000, 10% commission = £1,000 commission; FLOOR(£1,000 × 0.20) = £200 commission VAT deducted from PAY

3.6 UK Tax Period Date Ranges

text

-- US: Calendar year
periodStart = January 1 of taxYear
periodEnd   = December 31 of taxYear

-- UK (GB): UK tax year (Apr 6 – Apr 5), quarterly subdivisions:
Q1: periodStart = April 6 of taxYear,     periodEnd = June 30 of taxYear
Q2: periodStart = July 1 of taxYear,      periodEnd = September 30 of taxYear
Q3: periodStart = October 1 of taxYear,   periodEnd = December 31 of taxYear
Q4: periodStart = January 1 of taxYear+1, periodEnd = April 5 of taxYear+1

-- Full UK tax year:
periodStart = April 6 of taxYear
periodEnd   = April 5 of taxYear+1

Source: tax_form.quarter_cd, tax_form.jurisdiction_cd, tax_form.tax_year
Used by: getPayApplicationsForParty, getWithholdingDeductionsForApplications, getPartiesWithWithholding, and all aggregation queries that filter by date range
Note: For HMRC quarterly forms (FEU1/FEU2), quarter_cd is required. For IRS annual forms (1042-S, 1042), quarter_cd is null and the full calendar year is used.

3.7 IRS Semi-Monthly Deposit Period Bucketing

text

period1 = SUM(deduction_amt) WHERE DAY(applied_dt) IN [1..7]
period2 = SUM(deduction_amt) WHERE DAY(applied_dt) IN [8..15]
period3 = SUM(deduction_amt) WHERE DAY(applied_dt) IN [16..22]
period4 = SUM(deduction_amt) WHERE DAY(applied_dt) IN [23..end_of_month]
total   = period1 + period2 + period3 + period4

Source: cash_receipt_worksheet.applied_dt, cash_receipt_application_deduction.deduction_amt_applied
Precision: decimal(15,2)
Used by: IRS Form 1042 Section 1 — monthly tax liability schedule required for annual return filing

4. Cross-References

Document	Relationship
Tax Withholding Data Model	Defines all tables queried in this document: `party_tax_info`, `tax_rate_config`, `tax_calculation_audit`, `tax_form`, `tax_filing_package`
Worksheets Data Model	`cash_receipt_worksheet`, `cash_receipt_application`, and `cash_receipt_application_deduction` are the source tables for tax form data aggregation; tax deduction amounts are stored in `cash_receipt_application_deduction` with type codes `WH_US_NRA`, `WH_UK_FEU`, `VAT_COMM`
Billing Items Data Model	`billing_item.client_id` and `billing_item.uta_entity_id` drive jurisdiction determination; `billing_item_detail.billing_item_detail_gross_amt` on the REV detail provides the gross basis; `billing_item.billing_item_due_dt` determines UK tax year assignment for proactive FEU withholding
Cash Receipts Data Model	`cash_receipt_split.split_amt` is read during worksheet tax previews to validate that the split amount includes expected Artist Fee VAT for UK transactions
Parties Data Model	`party.display_name` is joined to `party_tax_info` for all compliance alert and tax form queries; `party.party_id` is the link between the tax domain and party identity

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Tax Withholding Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Party Tax Profile Queries ​

2.1.1 Get Current Tax Profile by Party ​

2.1.2 Get All Tax Profiles by Party ​

2.1.3 Get Tax Profile by ID ​

2.2 Compliance Monitoring Queries ​

2.2.1 Find Expiring CWAs ​

2.2.2 Find Expiring W-8 Forms ​

2.3 Tax Rate Configuration Queries ​

2.3.1 Get Active Rate for Jurisdiction and Tax Type ​

2.3.2 Get All Active Rate Configurations ​

2.3.3 Get Rate Configurations by Jurisdiction ​

2.3.4 Get Tax Year Range from Configuration ​

2.4 Tax Calculation Audit Queries ​

2.4.1 Get Audit Records by Context ​

2.4.2 Get Audit Record by ID ​

2.5 Tax Form Data Aggregation Queries ​

2.5.1 Get PAY Applications for Party in Tax Period ​

2.5.2 Get Withholding Deductions for Applications ​

2.5.3 Aggregate PAY Applications and Withholding Deductions for Period ​

2.5.4 Find Parties with Withholding in Period ​

2.5.5 Aggregate 1042-S Forms for Tax Year ​

2.5.6 Get Tax Liability by Semi-Monthly Period ​

2.6 Worksheet Tax Preview Queries ​

2.6.1 Get Tax Previews for Worksheet ​

2.6.2 Get UK Cumulative Receivables for Party ​

3. Calculations & Formulas ​

3.1 UK FEU Proactive Withholding Flag ​

3.2 UK FEU Withholding Amount ​

3.3 US NRA Withholding Amount ​

3.4 UK VAT on Artist Fee (Pass-Through) ​

3.5 UK VAT on Commission ​

3.6 UK Tax Period Date Ranges ​

3.7 IRS Semi-Monthly Deposit Period Bucketing ​

4. Cross-References ​

Tax Withholding Queries

1. Executive Summary

2. Key Queries

2.1 Party Tax Profile Queries

2.1.1 Get Current Tax Profile by Party

2.1.2 Get All Tax Profiles by Party

2.1.3 Get Tax Profile by ID

2.2 Compliance Monitoring Queries

2.2.1 Find Expiring CWAs

2.2.2 Find Expiring W-8 Forms

2.3 Tax Rate Configuration Queries

2.3.1 Get Active Rate for Jurisdiction and Tax Type

2.3.2 Get All Active Rate Configurations

2.3.3 Get Rate Configurations by Jurisdiction

2.3.4 Get Tax Year Range from Configuration

2.4 Tax Calculation Audit Queries

2.4.1 Get Audit Records by Context

2.4.2 Get Audit Record by ID

2.5 Tax Form Data Aggregation Queries

2.5.1 Get PAY Applications for Party in Tax Period

2.5.2 Get Withholding Deductions for Applications

2.5.3 Aggregate PAY Applications and Withholding Deductions for Period

2.5.4 Find Parties with Withholding in Period

2.5.5 Aggregate 1042-S Forms for Tax Year

2.5.6 Get Tax Liability by Semi-Monthly Period

2.6 Worksheet Tax Preview Queries

2.6.1 Get Tax Previews for Worksheet

2.6.2 Get UK Cumulative Receivables for Party

3. Calculations & Formulas

3.1 UK FEU Proactive Withholding Flag

3.2 UK FEU Withholding Amount

3.3 US NRA Withholding Amount

3.4 UK VAT on Artist Fee (Pass-Through)

3.5 UK VAT on Commission

3.6 UK Tax Period Date Ranges

3.7 IRS Semi-Monthly Deposit Period Bucketing

4. Cross-References