Invoices and Statements Queries

1. Executive Summary

This document catalogs every query operation used by the Invoices and Statements domain. The operations cover three primary table groups:

invoice and invoice_bank_details — invoice creation, retrieval, status management, number sequencing, and bank detail resolution for PDF payment instructions.
generated_statement — metadata persistence and retrieval for all generated PDF statement documents (client statements, settlement statements, deal statements, AR aging statements, and invoice documents).

Each query is presented in a technology-neutral format, describing the tables and joins involved, filter conditions, computed values, and return shape. The goal is to provide a complete reference that can inform future system design, data migration, or API contracts without coupling to a specific ORM or database dialect.

Queries are organized into four groups:

Invoice Queries — fetching billing items for invoicing, creating invoices, managing status, generating invoice numbers, and resolving bank details.
Client & Settlement Statement Queries — aggregating payment items and settlement data for client and settlement statement PDF generation.
Generated Statement Queries — persisting and retrieving generated PDF statement metadata.
Deal Statement & AR Aging Queries — aggregating deal-level revenue items, receivables, and cash applications for deal and revenue-item-focused settlement statements, plus AR aging bucket computation.

Data model reference: Invoices and Statements Data Model

2. Key Queries

2.1 Invoice Queries

2.1.1 Get Billing Item Details Available for Invoicing

Operation: getBillingItemDetailsForInvoicing

Input Parameters:

client_id: Integer (optional) — sales_item.client_id; filter by client party on the revenue item
buyer_id: Integer (optional) — sales_item.buyer_id; filter by buyer party on the revenue item
uta_entity_id: Integer (optional) — sales_item.uta_entity_id; filter by UTA entity on the revenue item
deal_id: Integer (optional) — deal.deal_id; filter by deal
billing_item_detail_type_cd: String (optional) — billing_item_detail.billing_item_detail_type_cd; filter by detail type (REV or PAY)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal`	INNER	`sales_item`	`sales_item.deal_id = deal.deal_id`	Revenue items for each deal
`deal`	INNER	`billing_item`	`billing_item.revenue_item_id = sales_item.sales_item_id`	Receivables under each revenue item
`deal`	INNER	`billing_item_detail`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	REV and PAY detail rows
`deal`	LEFT	`party` (as `buyer`)	`sales_item.buyer_id = buyer.party_id`	Buyer name resolution
`deal`	LEFT	`party` (as `client`)	`sales_item.client_id = client.party_id`	Client name resolution
`deal`	LEFT	`party` (as `contracted`)	`sales_item.contracted_party_id = contracted.party_id`	Contracted party name resolution
`deal`	LEFT	`party_addresses` (as `buyer_address`)	`buyer.party_id = buyer_address.party_id AND buyer_address.primary_ind = true`	Primary address for buyer
`deal`	LEFT	`party_addresses` (as `client_address`)	`sales_item.client_id = client_address.party_id AND client_address.primary_ind = true`	Primary address for client
`deal`	LEFT	`party_addresses` (as `contracted_address`)	`sales_item.contracted_party_id = contracted_address.party_id AND contracted_address.primary_ind = true`	Primary address for contracted party
`deal`	LEFT	`uta_entity`	`sales_item.uta_entity_id = uta_entity.uta_entity_id`	Entity name for display
`deal`	LEFT	`cv_booking_project_deal`	`cv_booking_project_deal.deal_id = deal.deal_id`	Legacy booking project link
`deal`	LEFT	`cv_booking_project_header`	`cv_booking_project_header.booking_project_header_id = cv_booking_project_deal.booking_project_header_id`	Project name for display

Filters:

NOT EXISTS (SELECT 1 FROM billing_item_document WHERE billing_item_document.billing_item_id = billing_item.billing_item_id AND billing_item_document.document_type_cd = CASE WHEN billing_item_detail.billing_item_detail_type_cd = 'REV' THEN 'CI' WHEN billing_item_detail.billing_item_detail_type_cd = 'PAY' THEN 'BI' END) — excludes details already linked to a matching document type
If client_id provided: sales_item.client_id = :client_id
If buyer_id provided: sales_item.buyer_id = :buyer_id
If uta_entity_id provided: sales_item.uta_entity_id = :uta_entity_id
If deal_id provided: deal.deal_id = :deal_id
If billing_item_detail_type_cd provided: billing_item_detail.billing_item_detail_type_cd = :billing_item_detail_type_cd

Computed Values:

None; all values are read directly from columns

Returns: Array of rows, one per available billing item detail, each containing uta_entity_id, uta_entity_name, client/buyer/contracted party IDs and names, deal and revenue item identifiers, and billing_item_detail_id, billing_item_detail_type_cd, billing_item_detail_gross_amt, and billing_item_detail_amt; ordered by billing_item_detail.billing_item_detail_id ascending.

2.1.2 Get Billing Item Details by IDs

Operation: getBillingItemDetailsByIds

Input Parameters:

billing_item_detail_ids: Integer[] (required) — billing_item_detail.billing_item_detail_id; array of detail IDs to retrieve

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal`	INNER	`sales_item`	`sales_item.deal_id = deal.deal_id`	Revenue item context
`deal`	INNER	`billing_item`	`billing_item.revenue_item_id = sales_item.sales_item_id`	Receivable rows
`deal`	INNER	`billing_item_detail`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Target detail rows
`deal`	LEFT	`party` (as `buyer`)	`sales_item.buyer_id = buyer.party_id`	Buyer name
`deal`	LEFT	`party` (as `client`)	`sales_item.client_id = client.party_id`	Client name
`deal`	LEFT	`party` (as `contracted`)	`sales_item.contracted_party_id = contracted.party_id`	Contracted party name
`deal`	LEFT	`party_addresses` (as `buyer_address`)	`buyer.party_id = buyer_address.party_id AND buyer_address.primary_ind = true`	Primary buyer address
`deal`	LEFT	`party_addresses` (as `client_address`)	`sales_item.client_id = client_address.party_id AND client_address.primary_ind = true`	Primary client address
`deal`	LEFT	`party_addresses` (as `contracted_address`)	`sales_item.contracted_party_id = contracted_address.party_id AND contracted_address.primary_ind = true`	Primary contracted party address
`deal`	LEFT	`uta_entity`	`sales_item.uta_entity_id = uta_entity.uta_entity_id`	Entity name
`deal`	LEFT	`cv_booking_project_deal`	`cv_booking_project_deal.deal_id = deal.deal_id`	Booking project link
`deal`	LEFT	`cv_booking_project_header`	`cv_booking_project_header.booking_project_header_id = cv_booking_project_deal.booking_project_header_id`	Project name

Filters:

billing_item_detail.billing_item_detail_id IN (:billing_item_detail_ids) — exact match on provided IDs

Computed Values:

None

Returns: Array of rows with the same shape as 2.1.1, hydrating the selected details with full party, deal, and address data for use during invoice generation.

2.1.3 Get Billing Item Details by Invoice ID

Operation: getBillingItemDetailsByInvoiceId

Input Parameters:

invoice_id: Integer (required) — billing_item_document.document_id; the invoice whose line items are to be retrieved

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal`	INNER	`sales_item`	`sales_item.deal_id = deal.deal_id`	Revenue item context
`deal`	INNER	`billing_item`	`billing_item.revenue_item_id = sales_item.sales_item_id`	Receivable rows
`deal`	INNER	`billing_item_document`	`billing_item_document.billing_item_id = billing_item.billing_item_id`	Bridge to the invoice
`deal`	INNER	`billing_item_detail`	`billing_item_detail.billing_item_id = billing_item.billing_item_id AND billing_item_detail.billing_item_detail_type_cd = CASE WHEN billing_item_document.document_type_cd = 'CI' THEN 'REV' WHEN billing_item_document.document_type_cd = 'BI' THEN 'PAY' END`	Detail type matched to document type
`deal`	LEFT	`party` (as `buyer`)	`sales_item.buyer_id = buyer.party_id`	Buyer name
`deal`	LEFT	`party` (as `client`)	`sales_item.client_id = client.party_id`	Client name
`deal`	LEFT	`party` (as `contracted`)	`sales_item.contracted_party_id = contracted.party_id`	Contracted party name
`deal`	LEFT	`party_addresses` (as `buyer_address`)	`buyer.party_id = buyer_address.party_id AND buyer_address.primary_ind = true`	Primary buyer address
`deal`	LEFT	`party_addresses` (as `client_address`)	`sales_item.client_id = client_address.party_id AND client_address.primary_ind = true`	Primary client address
`deal`	LEFT	`party_addresses` (as `contracted_address`)	`sales_item.contracted_party_id = contracted_address.party_id AND contracted_address.primary_ind = true`	Primary contracted party address
`deal`	LEFT	`uta_entity`	`sales_item.uta_entity_id = uta_entity.uta_entity_id`	Entity name
`deal`	LEFT	`cv_booking_project_deal`	`cv_booking_project_deal.deal_id = deal.deal_id`	Booking project link
`deal`	LEFT	`cv_booking_project_header`	`cv_booking_project_header.booking_project_header_id = cv_booking_project_deal.booking_project_header_id`	Project name

Filters:

billing_item_document.document_id = :invoice_id — restricts results to the given invoice

Computed Values:

None

Returns: Array of rows with the same shape as 2.1.1, representing all billing item details currently linked to the specified invoice.

2.1.4 Create Invoice

Operation: createInvoice

Input Parameters:

invoice_number: String (required) — generated invoice number (e.g., UTA_US-2026-000001)
uta_entity_id: Integer (required) — FK to uta_entity
issue_date: Date (required) — invoice issue date
due_date: Date (required) — calculated payment deadline (see Section 3.1)
billing_terms_cd: String (optional) — invoice.billing_terms_cd; payment terms code; defaults to DUE_RECEIPT
invoice_type_cd: String (required) — invoice.invoice_type_cd; COMMISSION or TOTAL_DUE
invoice_recipient_cd: String (required) — invoice.invoice_recipient_cd; CLIENT or BUYER
multi_client_ind: Boolean (required) — invoice.multi_client_ind; whether multiple clients are combined
currency_cd: String (required) — invoice.currency_cd; invoice currency
recipient_party_id: Integer (required) — FK to party
recipient_address_id: String (optional) — invoice.recipient_address_id; auth address reference for the recipient
contracted_party_id: Integer (optional) — FK to party; used for UK Total Due invoices
contracted_address_id: String (optional) — invoice.contracted_address_id; auth address reference for the contracted party
total_gross_amt: Decimal (required) — invoice.total_gross_amt; transmitted as string to preserve precision
total_commission_amt: Decimal (optional) — invoice.total_commission_amt; populated for COMMISSION invoices only; transmitted as string
status_cd: String (required) — always DRAFT on creation
created_by: String (optional) — invoice.created_by; creating user

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`invoice`	—	—	—	INSERT target; no joins required

Filters:

None (insert operation)

Computed Values:

None at query level; total_gross_amt and total_commission_amt are calculated upstream by summing billing_item_detail amounts before this operation is called

Returns: One row representing the newly created invoice record, including the system-assigned invoice_id and all inserted field values.

2.1.5 Create Billing Item Document Links

Operation: createBillingItemDocumentLinks

Input Parameters:

billing_item_detail_ids: Integer[] (required) — billing_item_detail.billing_item_detail_id; detail IDs to resolve to parent billing items
document_id: Integer (required) — billing_item_document.document_id; the invoice.invoice_id being linked
document_type_cd: String (required) — billing_item_document.document_type_cd; CI (Commission Invoice) or BI (Buyer Invoice)
created_by: String (required) — billing_item_document.created_by; creating user

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail`	INNER	`billing_item`	`billing_item_detail.billing_item_id = billing_item.billing_item_id`	Resolve detail IDs to parent billing item IDs
`billing_item_document`	—	—	—	INSERT target for the resolved billing item + document pairs

Filters:

billing_item_detail.billing_item_detail_id IN (:billing_item_detail_ids) — restrict resolution to selected details

Computed Values:

Deduplication: the resolved billing_item_id values are deduplicated (grouped) so that only one billing_item_document row is inserted per unique billing item, even if multiple details from the same billing item were selected

Returns: No data returned; side effect is one or more rows inserted into billing_item_document linking each resolved billing_item_id to the specified document_id and document_type_cd.

2.1.6 Get Invoice by ID

Operation: getInvoiceById

Input Parameters:

invoice_id: Integer (required) — invoice.invoice_id; primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`invoice`	—	—	—	Single-table primary key lookup; no joins

Filters:

invoice.invoice_id = :invoice_id

Computed Values:

None

Returns: One row from invoice with all stored fields, or null if no record matches the given invoice_id.

2.1.7 Get Invoice Display by ID

Operation: getInvoiceDisplayById

Input Parameters:

invoice_id: Integer (required) — invoice.invoice_id; primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`invoice`	LEFT	`uta_entity`	`invoice.uta_entity_id = uta_entity.uta_entity_id`	Resolve entity name
`invoice`	LEFT	`party` (as `recipient`)	`invoice.recipient_party_id = recipient.party_id`	Resolve recipient name
`invoice`	LEFT	`party` (as `contracted`)	`invoice.contracted_party_id = contracted.party_id`	Resolve contracted party name
`invoice`	LEFT	`code_master` (as `status_code`)	`status_code.code_master_type = 'INVOICE_STATUS_CD' AND status_code.code_master_cd = invoice.status_cd`	Resolve human-readable status description
`invoice`	LEFT	`code_master` (as `type_code`)	`type_code.code_master_type = 'INVOICE_TYPE_CD' AND type_code.code_master_cd = invoice.invoice_type_cd`	Resolve human-readable type description
`invoice`	LEFT	`code_master` (as `terms_code`)	`terms_code.code_master_type = 'BILLING_TERMS_CD' AND terms_code.code_master_cd = invoice.billing_terms_cd`	Resolve human-readable billing terms description
`billing_item_document`	—	—	Supplementary `COUNT(*)` subquery where `billing_item_document.document_id = :invoice_id`	Count of billing items linked to the invoice

Filters:

invoice.invoice_id = :invoice_id

Computed Values:

billing_item_count: COUNT(*) from billing_item_document where document_id = :invoice_id

Returns: One row representing the specified invoice enriched with resolved names for entity, recipient, contracted party, and code descriptions, plus the count of linked billing items.

2.1.8 List Invoices

Operation: listInvoices

Input Parameters:

status_cd: String (optional) — invoice.status_cd; filter by status (DRAFT, ISSUED, PAID, VOID)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`invoice`	LEFT	`uta_entity`	`invoice.uta_entity_id = uta_entity.uta_entity_id`	Entity name
`invoice`	LEFT	`party` (as `recipient`)	`invoice.recipient_party_id = recipient.party_id`	Recipient name
`invoice`	LEFT	`party` (as `contracted`)	`invoice.contracted_party_id = contracted.party_id`	Contracted party name
`invoice`	LEFT	`code_master` (as `status_code`)	`status_code.code_master_type = 'INVOICE_STATUS_CD' AND status_code.code_master_cd = invoice.status_cd`	Status description
`invoice`	LEFT	`code_master` (as `type_code`)	`type_code.code_master_type = 'INVOICE_TYPE_CD' AND type_code.code_master_cd = invoice.invoice_type_cd`	Type description
`invoice`	LEFT	`code_master` (as `terms_code`)	`terms_code.code_master_type = 'BILLING_TERMS_CD' AND terms_code.code_master_cd = invoice.billing_terms_cd`	Terms description

Filters:

If status_cd provided: invoice.status_cd = :status_cd

Computed Values:

None

Returns: Array of invoice rows each enriched with entity, recipient, and code descriptions, ordered by invoice.created_dt descending (newest first).

2.1.9 Update Invoice Status

Operation: updateInvoiceStatus

Input Parameters:

invoice_id: Integer (required) — invoice.invoice_id; the invoice to update
status_cd: String (required) — invoice.status_cd; new status code
updated_by: String (required) — invoice.updated_by; user performing the update

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`invoice`	—	—	—	UPDATE target; no joins required

Filters:

invoice.invoice_id = :invoice_id

Computed Values:

updated_dt: set to current timestamp at time of update

Returns: No data returned; side effect is invoice.status_cd and invoice.updated_dt updated on the specified row.

2.1.10 Get Next Invoice Number

Operation: getNextInvoiceNumber

Input Parameters:

uta_entity_id: Integer (required) — invoice_number_sequence.uta_entity_id; the UTA entity issuing the invoice

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`invoice_number_sequence`	—	—	`invoice_number_sequence.uta_entity_id = :uta_entity_id AND invoice_number_sequence.current_year = :current_year`	Locate existing sequence row for this entity and year
`invoice_number_sequence`	—	—	`invoice_number_sequence.invoice_number_sequence_id = :existing_id`	UPDATE to increment `current_sequence` when a row exists
`uta_entity`	—	—	`uta_entity.uta_entity_id = :uta_entity_id`	Fetch `invoice_prefix` when creating a new sequence row
`invoice_number_sequence`	—	—	—	INSERT new row when no sequence exists for the entity and year

Filters:

Step 1 (lookup): invoice_number_sequence.uta_entity_id = :uta_entity_id AND invoice_number_sequence.current_year = :current_year
Step 2a (increment): invoice_number_sequence.invoice_number_sequence_id = :existing_id (if row found)
Step 2b (create): uta_entity.uta_entity_id = :uta_entity_id (if no row found)

Computed Values:

current_year: extracted from current date (4-digit calendar year)
next_sequence: current_sequence + 1 on an existing row, or 1 for a newly created sequence
prefix: uta_entity.invoice_prefix; falls back to first 2 characters of uta_entity.uta_entity_name uppercased, then 'XX'
invoice_number: formatted as {prefix}-{current_year}-{next_sequence zero-padded to 6 digits} (e.g., UTA_US-2026-000001)

Returns: One formatted invoice number string representing the next unique sequential number for the given entity and year.

2.1.11 Get Invoice Bank Details

Operation: getInvoiceBankDetails

Input Parameters:

uta_entity_id: Integer (required) — invoice_bank_details.uta_entity_id; the issuing UTA entity
currency_cd: String (required) — invoice_bank_details.currency_cd; invoice currency

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`invoice_bank_details`	LEFT	`address` (as `bank_address`)	`invoice_bank_details.bank_address_id = bank_address.address_id`	Resolve bank branch address fields for PDF display

Filters:

invoice_bank_details.uta_entity_id = :uta_entity_id
invoice_bank_details.currency_cd = :currency_cd
invoice_bank_details.is_active = true

Computed Values:

None

Returns: One row containing bank payment instructions for the matched entity and currency (including bank_name, resolved address fields, account_number, account_name, aba_routing_number, swift_code, iban, sort_code), or null if no active record exists for the combination.

2.1.12 Get UTA Entity with Details

Operation: getUtaEntityWithDetails

Input Parameters:

uta_entity_id: Integer (required) — uta_entity.uta_entity_id; the UTA entity

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`uta_entity`	LEFT	`address`	`uta_entity.address_id = address.address_id`	Physical address of the entity for invoice PDF header
`uta_entity_contact_method`	INNER	`contact_method`	`uta_entity_contact_method.contact_method_id = contact_method.contact_method_id`	Contact details for invoice PDF header

Filters:

uta_entity.uta_entity_id = :uta_entity_id (entity + address query)
uta_entity_contact_method.uta_entity_id = :uta_entity_id (contact methods query)

Computed Values:

phone: first contact_method.contact_value where contact_method_type_cd = 'PHONE'
fax: first contact_method.contact_value where contact_method_type_cd = 'FAX'
email: first contact_method.contact_value where contact_method_type_cd = 'EMAIL'

Returns: One row containing uta_entity_id, uta_entity_name, operating_currency_cd, resolved address fields, phone, fax, email, and logo_asset_path; or null if the entity is not found.

2.2 Client & Settlement Statement Queries

2.2.1 Get Client Info

Operation: getClientInfo

Input Parameters:

client_id: Integer (required) — party.party_id; party ID of the client

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	—	Single-table primary key lookup; no joins

Filters:

party.party_id = :client_id

Computed Values:

display_name: party.display_name; falls back to first_name + ' ' + last_name, then company_name, then 'Unknown'

Returns: One row containing party_id, display_name, first_name, last_name, company_name, and resolved primary address fields for use in statement headers.

2.2.2 Get Default UTA Entity

Operation: getDefaultUtaEntity

Input Parameters:

None; returns the first uta_entity row in the table

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`uta_entity`	LEFT	`address`	`uta_entity.address_id = address.address_id`	Physical address for statement header
`uta_entity_contact_method`	INNER	`contact_method`	`uta_entity_contact_method.contact_method_id = contact_method.contact_method_id`	Contact details for statement header

Filters:

LIMIT 1 on uta_entity — only the first entity row is returned
uta_entity_contact_method.uta_entity_id = :uta_entity_id — contact methods for the resolved entity

Computed Values:

phone: first contact_value where contact_method_type_cd = 'PHONE'
fax: first contact_value where contact_method_type_cd = 'FAX'

Returns: One row containing uta_entity_name, resolved address fields, phone, and fax for use in statement PDF headers.

2.2.3 Get Client Transactions

Operation: getClientTransactions

Input Parameters:

client_id: Integer (required) — payment_item.client_id; party ID of the client
date_from: Date (required) — start of statement period (inclusive), matched against payment_item.posting_dt
date_to: Date (required) — end of statement period (inclusive), matched against payment_item.posting_dt
currency_cd: String (required) — payment_item.currency_cd; currency filter (default USD)
excluded_payment_item_ids: Integer[] (optional) — payment_item.payment_item_id; payment items to exclude from the statement

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`payment_item`	—	`deal`	`deal.deal_id = payment_item.deal_id` (subquery per item)	Deal info for each payment item
`payment_item`	—	`party` (as `client`)	`party.party_id = :client_id` (single lookup)	Client name for statement header
`payment_item`	—	`party` (as `payee`)	`party.party_id = payment_item.payment_party_id` (per payout)	Payee name for payout rows

Filters:

payment_item.client_id = :client_id
payment_item.payment_item_posting_status_cd = 'P' — only posted items
payment_item.posting_dt >= :date_from
payment_item.posting_dt <= :date_to
payment_item.payment_item_id NOT IN (:excluded_payment_item_ids) — applied in-memory after query when list is provided

Computed Values:

Grouping key: {posting_dt}_{deal_id} — one transaction group per date-deal pair
gross_amount: sum of payment_item_amt for all payment items in the group
agency_commission: sum of payment_item_amt where payment_item_type_cd = 'S' (settlement items)
third_party_payouts: sum of negative payment_item_amt where payment_item_type_cd = 'P' (passthrough items)
payment_to_client: gross_amount - agency_commission + third_party_payouts
net_amount: equals payment_to_client

Returns: Array of transaction rows grouped by date-deal pair, each containing payment_item_id, posted_date, batch_number, payment_method, transaction_number, net_amount, deal_reference, client_name, project_name, invoice_number, memo, gross_amount, agency_commission, a payouts array of { payee_name, amount }, payment_to_client, and total_to_client; ordered by payment_item.posting_dt descending.

2.2.4 Get Settlement Engagements

Operation: getSettlementEngagements

Input Parameters:

client_id: Integer (required) — participant_settlement_item.payment_party_id; party ID of the payee
date_from: Date (required) — start of statement period, matched against payment_item.posting_dt
date_to: Date (required) — end of statement period, matched against payment_item.posting_dt

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`participant_settlement_item`	INNER	`participant_settlement`	`participant_settlement_item.participant_settlement_id = participant_settlement.participant_settlement_id`	Settlement header for each item
`participant_settlement_item`	INNER	`payment_item`	`participant_settlement_item.payment_item_id = payment_item.payment_item_id`	Posting date and deal linkage
`participant_settlement_item`	LEFT	`deal`	`payment_item.deal_id = deal.deal_id`	Deal info for grouping

Filters:

participant_settlement_item.payment_party_id = :client_id
payment_item.posting_dt >= :date_from
payment_item.posting_dt <= :date_to

Computed Values:

Grouping: results are grouped by deal_id into engagement objects
commission_amt (per item): participant_settlement_commission_amt; falls back to payment_item_amt * commission_perc
guarantee: sum of payment_item_amt per deal group
net_gross: guarantee + overage - backout per deal group
commission_due: sum of commission_amt per deal group

Returns: Array of engagement rows, one per deal group, each containing contract_id, show_date, venue, city, state, country, guarantee, overage, backout, net_gross, commission_due, and deposit_received; ordered by payment_item.posting_dt descending.

2.2.5 Get Settlement Items (Raw)

Operation: getSettlementItemsRaw

Input Parameters:

client_id: Integer (required) — participant_settlement_item.payment_party_id
date_from: Date (required) — start of statement period
date_to: Date (required) — end of statement period

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`participant_settlement_item`	INNER	`participant_settlement`	`participant_settlement_item.participant_settlement_id = participant_settlement.participant_settlement_id`	Settlement header
`participant_settlement_item`	INNER	`payment_item`	`participant_settlement_item.payment_item_id = payment_item.payment_item_id`	Posting date
`participant_settlement_item`	LEFT	`deal`	`payment_item.deal_id = deal.deal_id`	Deal info
`participant_settlement_item`	LEFT	`party`	`participant_settlement_item.payment_party_id = party.party_id`	Payee name resolution

Filters:

participant_settlement_item.payment_party_id = :client_id
payment_item.posting_dt >= :date_from
payment_item.posting_dt <= :date_to

Computed Values:

None; all values read directly from columns

Returns: Array of individual settlement item rows without deal-level grouping, each containing settlement_id, settlement_item_id, payment_party_id, payment_party_name, commission_flat_ind, commission_perc, commission_amt, payment_item_id, payment_item_amt, posting_dt, deal_id, deal_reference, deal_name, and settlement_status_cd.

2.2.6 Get Client Statement Preview

Operation: getClientStatementPreview

Input Parameters:

client_id: Integer (required) — party.party_id
date_from: Date (required) — start of period
date_to: Date (required) — end of period
currency_cd: String (optional) — currency (default USD)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`party`	—	—	`party.party_id = :client_id`	Client info (from 2.2.1)
`payment_item`	—	`deal`	`deal.deal_id = payment_item.deal_id`	Transactions (from 2.2.3)

Filters:

Identical filters to 2.2.1 and 2.2.3 combined

Computed Values:

total_gross: sum of gross_amount across all transactions
total_commission: sum of agency_commission across all transactions
total_net: sum of net_amount across all transactions

Returns: One object containing the client info from 2.2.1, the transaction array from 2.2.3, and a totals summary with total_gross, total_commission, and total_net.

2.3 Generated Statement Queries

2.3.1 List Generated Statements

Operation: listGeneratedStatements

Input Parameters:

client_id: Integer (optional) — generated_statement.client_id; filter by client
statement_type: String (optional) — generated_statement.statement_type_cd; filter by type (CLIENT_STATEMENT, SETTLEMENT_STATEMENT, AR_STATEMENT, INVOICE, DEAL_STATEMENT)
limit: Integer (optional) — page size; default 50
offset: Integer (optional) — pagination offset; default 0

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`generated_statement`	LEFT	`party`	`generated_statement.client_id = party.party_id`	Resolve client name for display

Filters:

generated_statement.generation_status_cd = 'COMPLETED' — always applied; only completed statements are returned
If client_id provided: generated_statement.client_id = :client_id
If statement_type provided: generated_statement.statement_type_cd = :statement_type

Computed Values:

client_name: party.display_name; falls back to 'Unknown'

Returns: Array of statement metadata rows, each containing generated_statement_id, statement_ref, statement_type_cd, client_id, client_name, date_from, date_to, currency_cd, file_name, file_size_bytes, generated_by, generated_dt, and metadata; ordered by generated_statement.generated_dt descending.

2.3.2 Download Statement

Operation: downloadStatement

Input Parameters:

statement_id: Integer (required) — generated_statement.generated_statement_id; primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`generated_statement`	—	—	—	Primary key lookup to retrieve storage coordinates

Filters:

generated_statement.generated_statement_id = :statement_id

Computed Values:

None at query level; the retrieved s3_key is passed to the object storage service to fetch the PDF binary

Returns: One object containing the PDF file as a binary buffer and the file name (using generated_statement.file_name or falling back to {statement_ref}.pdf).

2.3.3 Save Generated Statement

Operation: saveGeneratedStatement

Input Parameters:

statement_type_cd: String (required) — generated_statement.statement_type_cd
statement_ref: String (required) — generated_statement.statement_ref; unique reference identifier
client_id: Integer (optional) — generated_statement.client_id; party ID if applicable
date_from: Date (optional) — generated_statement.date_from; start of period covered
date_to: Date (optional) — generated_statement.date_to; end of period covered
currency_cd: String (optional) — generated_statement.currency_cd
s3_bucket: String (optional) — generated_statement.s3_bucket; object storage bucket
s3_key: String (optional) — generated_statement.s3_key; object storage key
file_name: String (optional) — generated_statement.file_name
file_size_bytes: Integer (optional) — generated_statement.file_size_bytes
generation_status_cd: String (optional) — generated_statement.generation_status_cd; typically 'COMPLETED'
metadata: JSONB (optional) — generated_statement.metadata; type-specific summary data (see Section 3.5)
generated_by: String (optional) — generated_statement.generated_by; user or process

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`generated_statement`	—	—	—	INSERT target; no joins required

Filters:

None (insert operation)

Computed Values:

None at query level; all values are passed in from the generation process

Returns: No data returned; side effect is one row inserted into generated_statement with all provided fields.

2.4 Deal Statement & AR Aging Queries

2.4.1 Get Deal Info

Operation: getDealInfo

Input Parameters:

deal_id: Integer (required) — deal.deal_id; primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal`	LEFT	`department`	`department.department_id` (default entity)	Department name
`deal`	LEFT	`uta_entity`	`uta_entity.uta_entity_id` (default entity)	Entity name
`deal_party`	LEFT	`party`	`party.party_id = deal_party.party_id`	Party names for client, buyer, and loanout roles

Filters:

deal.deal_id = :deal_id (deal + entity lookup)
deal_party.deal_id = :deal_id (party roles lookup)

Computed Values:

client_name: party.display_name where deal_party.party_role_type_cd = 'CLIENT'
buyer_name: party.display_name where deal_party.party_role_type_cd = 'BUYER'
loanout_name: party.display_name where deal_party.party_role_type_cd = 'LOANOUT'

Returns: One object containing deal_id, deal_name, deal_reference, deal_start_dt, deal_end_dt, active_ind, department_name, uta_entity_name, client_name, buyer_name, loanout_name, and currency_cd.

2.4.2 Get Deal Payees

Operation: getDealPayees

Input Parameters:

deal_id: Integer (required) — deal_party.deal_id; the deal

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	LEFT	`party`	`party.party_id = deal_party.party_id`	Party name and company
`deal_party`	LEFT	`bank_account`	`bank_account.bank_account_id = deal_party.bank_account_id`	Bank name for deal statement payee section

Filters:

deal_party.deal_id = :deal_id

Computed Values:

None

Returns: Array of payee rows, one per deal_party record, each containing party_id, party_name, party_company, role, percent, amount, and bank_name; ordered by deal_party.deal_party_commission_perc descending.

2.4.3 Get Revenue Items for Deal

Operation: getRevenueItemsForDeal

Input Parameters:

deal_id: Integer (required) — sales_item.deal_id
primary_payee: Object (required) — primary payee allocation object containing percent (for amount computation)
other_payees: Object[] (required) — other payee allocation objects each containing percent

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`sales_item`	—	`billing_item`	`billing_item.revenue_item_id = sales_item.sales_item_id` (subquery per item)	Receivables under each revenue item
`billing_item`	—	`billing_item_detail`	`billing_item_detail.billing_item_id = billing_item.billing_item_id` (subquery)	REV and PAY detail amounts
`billing_item_detail`	—	`cash_receipt_application`	`cash_receipt_application.billing_item_detail_id = billing_item_detail.billing_item_detail_id` (aggregate subquery)	Cash applied to each detail

Filters:

sales_item.deal_id = :deal_id
billing_item.revenue_item_id = :sales_item_id (per revenue item)
billing_item_detail.billing_item_id = :billing_item_id (per receivable)
cash_receipt_application.billing_item_detail_id = :detail_id (per detail)

Computed Values:

gross_amt: billing_item_detail.billing_item_detail_gross_amt from the REV or PAY detail
commission_amt: billing_item_detail.billing_item_detail_amt from the REV detail
commission_perc: billing_item_detail.billing_item_detail_percent from the REV detail
payable_amt: billing_item_detail.billing_item_detail_amt from the PAY detail
cash_applied_rev: SUM(cash_receipt_application.cash_receipt_amt_applied) for the REV detail
cash_applied_pay: SUM(cash_receipt_application.cash_receipt_amt_applied) for the PAY detail
primary_payee_amt: cash_applied_pay * primary_payee.percent
other_payees_amt: cash_applied_pay * SUM(other_payees[].percent)
Revenue item totals: sum of all receivable-level values per revenue item

Returns: Array of revenue item rows, each containing revenue_item_id, revenue_item_name, sales_item_ref, a receivables array with per-billing-item amounts, and totals (total_gross, total_commission, total_cash_rev, total_payable, total_cash_pay, total_primary_payee, total_other_payees).

2.4.4 Get AR Aging Line Items

Operation: getArAgingLineItems

Input Parameters:

as_of_date: Date (required) — reference date for aging bucket classification
open_items_only: Boolean (required) — when true, only includes items where billing_item.open_item_ind = true and remaining balance > 0
client_id: Integer (optional) — deal_party.party_id; filter to a specific client

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	LEFT	`sales_item`	`sales_item.sales_item_id = billing_item.revenue_item_id`	Revenue item and deal linkage
`billing_item`	LEFT	`deal`	`deal.deal_id = sales_item.deal_id`	Deal name and invoice number
`billing_item`	LEFT	`deal_party`	`deal_party.deal_id = deal.deal_id AND deal_party.party_role_type_cd = 'CLIENT'`	Client identification
`billing_item`	LEFT	`party`	`party.party_id = deal_party.party_id`	Client name
`billing_item`	—	`billing_item_detail`	`billing_item_detail.billing_item_id = :billing_item_id` (subquery)	Original amounts per detail
`billing_item_detail`	—	`cash_receipt_application`	`cash_receipt_application.billing_item_detail_id = :detail_id` (aggregate subquery)	Cash applied

Filters:

If open_items_only = true: billing_item.open_item_ind = true
If client_id provided: party.party_id = :client_id

Computed Values:

original_amount: billing_item_detail.billing_item_detail_gross_amt (REV preferred, PAY fallback)
applied_amount: SUM(cash_receipt_application.cash_receipt_amt_applied) across all details for the billing item
remaining_amount: original_amount - applied_amount
days_past_due: FLOOR((as_of_date - billing_item.billing_item_due_dt) / 1 day)
current_amount: remaining_amount if days_past_due <= 0
days_1_to_30: remaining_amount if 1 <= days_past_due <= 30
days_31_to_60: remaining_amount if 31 <= days_past_due <= 60
days_61_to_90: remaining_amount if 61 <= days_past_due <= 90
days_90_plus: remaining_amount if days_past_due > 90

Returns: Array of aging line item rows, each containing client_name, deal_name, invoice_number, invoice_date, due_date, original_amount, applied_amount, and one of the aging bucket amounts (current_amount, days_1_to_30, days_31_to_60, days_61_to_90, days_90_plus); ordered by billing_item.billing_item_due_dt descending, limited to 500 rows.

2.4.5 Get Revenue Item Info (Settlement V2)

Operation: getRevenueItemInfo

Input Parameters:

revenue_item_id: Integer (required) — sales_item.sales_item_id; primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`sales_item`	LEFT	`deal`	`deal.deal_id = sales_item.deal_id`	Deal name and dates
`sales_item`	LEFT	`party`	`party.party_id = sales_item.client_entity_id`	Client name
`sales_item`	LEFT	`department`	`department.department_id = sales_item.department_id`	Department name
`sales_item`	LEFT	`uta_entity`	`uta_entity.uta_entity_id = sales_item.uta_entity_id`	Entity name

Filters:

sales_item.sales_item_id = :revenue_item_id

Computed Values:

None

Returns: One row containing sales_item_id, name, sales_item_ref, gross_amt, commission_perc, commission_amt, currency_cd, status_cd, client_name, deal_name, deal_start_dt, deal_end_dt, department_name, uta_entity_name, and rev_rec_style_cd.

2.4.6 Build Cash Receipt Groups (Settlement V2)

Operation: buildCashReceiptGroups

Input Parameters:

billing_item_ids: Integer[] (required) — billing_item.billing_item_id; billing items belonging to the target revenue item

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item_detail`	—	—	`billing_item_detail.billing_item_id IN (:billing_item_ids)`	Resolve all detail IDs for the billing items
`cash_receipt_application`	—	—	`cash_receipt_application.billing_item_detail_id IN (:detail_ids)`	Applications against those details
`cash_receipt_worksheet`	LEFT	`cash_receipt_split`	`cash_receipt_worksheet.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id`	Link worksheet to split
`cash_receipt_split`	LEFT	`cash_receipt`	`cash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id`	Link split to receipt
`cash_receipt_payout`	LEFT	`party`	`party.party_id = cash_receipt_payout.payout_party_id`	Payee name for payout rows
`cash_receipt_payout`	LEFT	`bank_account`	`bank_account.bank_account_id = cash_receipt_payout.payment_party_bank_id`	Bank account name for payout rows

Filters:

billing_item_detail.billing_item_id IN (:billing_item_ids) — all details for the target billing items
cash_receipt_application.billing_item_detail_id IN (:detail_ids) — applications against those details
cash_receipt_worksheet.cash_receipt_worksheet_id IN (:worksheet_ids) — worksheets referenced by the applications
cash_receipt_payout.cash_receipt_worksheet_id = :worksheet_id — payouts per worksheet

Computed Values:

Grouping: results grouped by cash_receipt_id
receipt_date: cash_receipt.posting_dt; falls back to cash_receipt.deposit_date
net_receipt_amt: cash_receipt.net_receipt_amt
REV cash applied: sum of cash_receipt_amt_applied for REV detail applications
PAY cash applied: sum of cash_receipt_amt_applied for PAY detail applications
split_percent (per payee): payout_amount / payable_amt when payable_amt > 0

Returns: Array of cash receipt groups, each containing cash_receipt_id, cash_receipt_ref, net_receipt_amt, currency_cd, receipt_date, a receivables array with per-billing-item amounts and nested settlements (with party_id, party_name, split_percent, amount, bank_account_name, payment_date, currency_cd), and receivable_count.

2.4.7 Get Billing Items for Invoice (Statement Actions)

Operation: getBillingItemsForInvoice

Input Parameters:

party_id: Integer (required) — deal_party.party_id; the party (client or buyer)
target_type: String (required) — deal_party.party_role_type_cd; CLIENT or BUYER
deal_id: Integer (optional) — deal.deal_id; filter by specific deal

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`billing_item`	INNER	`sales_item`	`sales_item.sales_item_id = billing_item.revenue_item_id`	Revenue item context
`billing_item`	INNER	`deal`	`deal.deal_id = sales_item.deal_id`	Deal name and date
`billing_item`	INNER	`deal_party`	`deal_party.deal_id = deal.deal_id AND deal_party.party_id = :party_id AND deal_party.party_role_type_cd = :target_type`	Match party and role
`billing_item`	—	`billing_item_detail`	`billing_item_detail.billing_item_id = :billing_item_id` (subquery)	Amount by detail type

Filters:

deal_party.party_id = :party_id
deal_party.party_role_type_cd = :target_type
If deal_id provided: deal.deal_id = :deal_id
Items with zero billing_item_detail_amt are excluded

Computed Values:

amount: billing_item_detail.billing_item_detail_amt where billing_item_detail_type_cd = 'REV' for CLIENT target type, or = 'PAY' for BUYER target type

Returns: Array of billing item rows each containing billing_item_id, billing_item_name, deal_name, deal_date, revenue_item_id, due_date, amount, and currency_cd; ordered by billing_item.billing_item_id descending, limited to 100 rows.

2.4.8 Get Parties for Invoice

Operation: getPartiesForInvoice

Input Parameters:

target_type: String (required) — deal_party.party_role_type_cd; CLIENT or BUYER

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`deal_party`	INNER	`party`	`party.party_id = deal_party.party_id`	Party name for display

Filters:

deal_party.party_role_type_cd = :target_type

Computed Values:

deal_count: COUNT(DISTINCT deal_party.deal_id) per party

Returns: Array of party rows each containing party_id, display_name, and deal_count; ordered by deal_count descending, limited to 100 rows.

3. Calculations & Formulas

3.1 Due Date Calculation

text

due_date = issue_date + offset

where offset is determined by billing_terms_cd:
  DUE_RECEIPT  →  0 days   (due_date = issue_date)
  NET_7        →  7 days
  NET_14       →  14 days
  NET_30       →  30 days
  NET_45       →  45 days
  NET_60       →  60 days

Source: invoice.issue_date, invoice.billing_terms_cd
Precision: date (no time component)
Example: issue_date = 2026-03-01 + NET_30 → due_date = 2026-03-31

3.2 Invoice Total Calculation

text

total_gross_amt     = SUM( billing_item_detail.billing_item_detail_gross_amt )
                      for all selected billing_item_detail rows

total_commission_amt = SUM( billing_item_detail.billing_item_detail_amt )
                       for all selected billing_item_detail rows
                       (populated only when invoice_type_cd = 'COMMISSION')

Source: billing_item_detail.billing_item_detail_gross_amt, billing_item_detail.billing_item_detail_amt
Precision: decimal(15,2); transmitted as strings to preserve precision
Example: 3 REV details with gross amounts $10,000.00, $5,000.00, $2,500.00 → total_gross_amt = $17,500.00

3.3 Invoice Number Formatting

text

invoice_number = {prefix}-{year}-{sequence}

where:
  prefix   = invoice_number_sequence.prefix
             (derived from uta_entity.invoice_prefix)
  year     = current calendar year (4 digits)
  sequence = invoice_number_sequence.current_sequence incremented by 1,
             zero-padded to 6 digits

Example: UTA_US-2026-000001

Source: uta_entity.invoice_prefix, invoice_number_sequence.current_sequence, invoice_number_sequence.current_year
Uniqueness: enforced by uq_invoice_number constraint on invoice.invoice_number

3.4 AR Aging Bucket Classification

text

days_past_due = FLOOR( (as_of_date - billing_item.billing_item_due_dt) / 1 day )

remaining_amount = original_amount - applied_amount

Bucket assignment (mutually exclusive):
  days_past_due <= 0   →  current
  1 to 30              →  days_1_to_30
  31 to 60             →  days_31_to_60
  61 to 90             →  days_61_to_90
  > 90                 →  days_90_plus

Source: billing_item.billing_item_due_dt, billing_item_detail.billing_item_detail_gross_amt, cash_receipt_application.cash_receipt_amt_applied
Precision: decimal(15,2) for amounts; integer for days
Aggregation: aging summary totals = SUM() of each bucket column across all line items

3.5 Statement Metadata by Type

The generated_statement.metadata JSONB column stores type-specific summary data populated at generation time:

`statement_type_cd`	Metadata Fields
`CLIENT_STATEMENT`	`total_gross`, `total_commission`, `total_net`, `transaction_count`, `excluded_payment_item_ids`
`SETTLEMENT_STATEMENT`	`total_guarantee`, `total_overage`, `total_backout`, `total_net_gross`, `total_funds_received`, `total_commission_due`, `net_amount`, `engagement_count`; or for V2: `revenue_item_id`, `revenue_item_name`, `sales_item_ref`, `deal_name`, `client_name`, `total_settlement`
`DEAL_STATEMENT`	`deal_id`, `deal_name`, `deal_reference`, `client_name`, totals (`total_gross`, `total_commission`, `total_cash_rev`, `total_payable`, `total_cash_pay`), `revenue_item_count`, `receivable_count`
`AR_AGING_STATEMENT`	`as_of_date`, `client_filter`, `status_filter`, `item_count`, `total_original`, `total_applied`, `total_outstanding`, `aging_summary { current, days_1_to_30, days_31_to_60, days_61_to_90, days_90_plus }`
`INVOICE`	`invoice_type`, `party_name`, `line_item_count`, `subtotal`, `vat_amount`, `wht_amount`, `total_due`

3.6 Client Statement Totals

text

total_gross      = SUM( transactions[].gross_amount )
total_commission = SUM( transactions[].agency_commission )
total_net        = SUM( transactions[].net_amount )

Source: computed values from 2.2.3
Precision: decimal(15,2)

3.7 Settlement Statement Totals

text

total_guarantee      = SUM( engagements[].guarantee )
total_overage        = SUM( engagements[].overage )
total_backout        = SUM( engagements[].backout )
total_net_gross      = SUM( engagements[].net_gross )
total_funds_received = SUM( engagements[].deposit_received )
total_commission_due = SUM( engagements[].commission_due )
net_amount           = total_net_gross - total_commission_due
outstanding_balance  = net_amount - artist_payments

Source: computed values from 2.2.4
Precision: decimal(15,2)

3.8 Deal Statement Totals

text

total_gross             = SUM( revenue_items[].total_gross )
total_commission        = SUM( revenue_items[].total_commission )
total_cash_rev          = SUM( revenue_items[].total_cash_rev )
total_payable           = SUM( revenue_items[].total_payable )
total_cash_pay          = SUM( revenue_items[].total_cash_pay )
total_primary_payee_amt = SUM( revenue_items[].total_primary_payee )
total_other_payees_amt  = SUM( revenue_items[].total_other_payees )

Source: computed values from 2.4.3
Precision: decimal(15,2)

3.9 Invoice Tax Calculations (Statement-Based Generation)

text

subtotal = SUM( line_items[].amount )

UK Entity:
  vat_amount = subtotal * 0.20
  total_due  = subtotal + vat_amount

US Entity, BUYER target:
  wht_amount = subtotal * 0.30
  total_due  = subtotal - wht_amount

US Entity, CLIENT target:
  total_due  = subtotal

Source: billing_item_detail.billing_item_detail_amt, uta_entity jurisdiction, deal_party.party_role_type_cd
Precision: decimal(15,2)

4. Cross-References

Document	Relationship
Billing Items Data Model	`billing_item`, `billing_item_detail`, and `billing_item_document` are central to all invoice queries (2.1.1–2.1.5, 2.4.3, 2.4.4, 2.4.7). The `billing_item_document` bridge table links billing items to invoices via `document_id` = `invoice.invoice_id`.
Parties Data Model	`party`, `party_addresses`, and `deal_party` are joined extensively across invoice queries (2.1.1–2.1.3, 2.1.7, 2.1.12), statement queries (2.2.1, 2.2.3, 2.2.4, 2.2.5, 2.3.1), and deal queries (2.4.1, 2.4.2, 2.4.8).
Deals, Sales Items & Payment Terms Data Model	`deal`, `sales_item` (revenue items), and `cv_booking_project_header`/`cv_booking_project_deal` provide deal and project context for invoice line items and statement sections (2.1.1, 2.1.3, 2.4.1, 2.4.3, 2.4.5).
Cash Receipts Data Model	`cash_receipt`, `cash_receipt_split`, `cash_receipt_worksheet`, and `cash_receipt_application` are queried for cash-applied amounts in deal statements, settlement statements, and AR aging (2.2.3, 2.4.3, 2.4.4, 2.4.6).
Settlements Data Model	`participant_settlement` and `participant_settlement_item` drive settlement statement queries (2.2.4, 2.2.5). `cash_receipt_payout` provides payout data for Settlement V2 (2.4.6).
Worksheets Data Model	`cash_receipt_worksheet` links cash receipt splits to their applications and payouts, bridging receipt and settlement data in the Settlement V2 query (2.4.6).

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Invoices and Statements Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Invoice Queries ​

2.1.1 Get Billing Item Details Available for Invoicing ​

2.1.2 Get Billing Item Details by IDs ​

2.1.3 Get Billing Item Details by Invoice ID ​

2.1.4 Create Invoice ​

2.1.5 Create Billing Item Document Links ​

2.1.6 Get Invoice by ID ​

2.1.7 Get Invoice Display by ID ​

2.1.8 List Invoices ​

2.1.9 Update Invoice Status ​

2.1.10 Get Next Invoice Number ​

2.1.11 Get Invoice Bank Details ​

2.1.12 Get UTA Entity with Details ​

2.2 Client & Settlement Statement Queries ​

2.2.1 Get Client Info ​

2.2.2 Get Default UTA Entity ​

2.2.3 Get Client Transactions ​

2.2.4 Get Settlement Engagements ​

2.2.5 Get Settlement Items (Raw) ​

2.2.6 Get Client Statement Preview ​

2.3 Generated Statement Queries ​

2.3.1 List Generated Statements ​

2.3.2 Download Statement ​

2.3.3 Save Generated Statement ​

2.4 Deal Statement & AR Aging Queries ​

2.4.1 Get Deal Info ​

2.4.2 Get Deal Payees ​

2.4.3 Get Revenue Items for Deal ​

2.4.4 Get AR Aging Line Items ​

2.4.5 Get Revenue Item Info (Settlement V2) ​

2.4.6 Build Cash Receipt Groups (Settlement V2) ​

2.4.7 Get Billing Items for Invoice (Statement Actions) ​

2.4.8 Get Parties for Invoice ​

3. Calculations & Formulas ​

3.1 Due Date Calculation ​

3.2 Invoice Total Calculation ​

3.3 Invoice Number Formatting ​

3.4 AR Aging Bucket Classification ​

3.5 Statement Metadata by Type ​

3.6 Client Statement Totals ​

3.7 Settlement Statement Totals ​

3.8 Deal Statement Totals ​

3.9 Invoice Tax Calculations (Statement-Based Generation) ​

4. Cross-References ​

Invoices and Statements Queries

1. Executive Summary

2. Key Queries

2.1 Invoice Queries

2.1.1 Get Billing Item Details Available for Invoicing

2.1.2 Get Billing Item Details by IDs

2.1.3 Get Billing Item Details by Invoice ID

2.1.4 Create Invoice

2.1.5 Create Billing Item Document Links

2.1.6 Get Invoice by ID

2.1.7 Get Invoice Display by ID

2.1.8 List Invoices

2.1.9 Update Invoice Status

2.1.10 Get Next Invoice Number

2.1.11 Get Invoice Bank Details

2.1.12 Get UTA Entity with Details

2.2 Client & Settlement Statement Queries

2.2.1 Get Client Info

2.2.2 Get Default UTA Entity

2.2.3 Get Client Transactions

2.2.4 Get Settlement Engagements

2.2.5 Get Settlement Items (Raw)

2.2.6 Get Client Statement Preview

2.3 Generated Statement Queries

2.3.1 List Generated Statements

2.3.2 Download Statement

2.3.3 Save Generated Statement

2.4 Deal Statement & AR Aging Queries

2.4.1 Get Deal Info

2.4.2 Get Deal Payees

2.4.3 Get Revenue Items for Deal

2.4.4 Get AR Aging Line Items

2.4.5 Get Revenue Item Info (Settlement V2)

2.4.6 Build Cash Receipt Groups (Settlement V2)

2.4.7 Get Billing Items for Invoice (Statement Actions)

2.4.8 Get Parties for Invoice

3. Calculations & Formulas

3.1 Due Date Calculation

3.2 Invoice Total Calculation

3.3 Invoice Number Formatting

3.4 AR Aging Bucket Classification

3.5 Statement Metadata by Type

3.6 Client Statement Totals

3.7 Settlement Statement Totals

3.8 Deal Statement Totals

3.9 Invoice Tax Calculations (Statement-Based Generation)

4. Cross-References