Client Framework

Appearance

Invoices and Statements Procedures ​

1. Executive Summary ​

This document specifies every data mutation operation in the Invoices and Statements domain. Each procedure is described at the data level: the tables written, the fields populated, the sequencing of inserts and updates, and the business rules that govern whether a mutation may proceed.

The domain encompasses two categories of outbound financial documents:

  • Invoices — formal payment requests created from billing items. Procedures cover invoice creation (including batching, number generation, and bridge-table linking), status transitions, bank detail management, and PDF generation.
  • Statements — summary PDF documents (client statements, settlement statements, deal statements, AR aging statements). Procedures cover data aggregation, PDF rendering, external storage upload, and metadata persistence.

Key tables mutated by this domain:

TableMutation Types
invoiceINSERT, UPDATE (status_cd)
invoice_number_sequenceINSERT, UPDATE (current_sequence)
invoice_bank_detailsINSERT, UPDATE
billing_item_documentINSERT
generated_statementINSERT

Data model reference: Invoices and Statements Data Model

Query reference: Invoices and Statements Queries

2. Key Procedures ​

2.1 Generate Invoices from Billing Items ​

Operation: generateInvoices

Trigger: User selects one or more billing item detail rows from the invoicing UI and submits the generate invoices form.

Input Parameters:

  • billingItemDetailIds: integer[] — IDs from billing_item_detail to include on the invoice(s)
  • invoiceTypeCd: string — 'COMMISSION' or 'TOTAL_DUE'; maps to invoice.invoice_type_cd
  • invoiceRecipientCd: string — 'CLIENT' or 'BUYER'; maps to invoice.invoice_recipient_cd
  • issueDate: string (date) — invoice issue date in YYYY-MM-DD format; maps to invoice.issue_date
  • billingTermsCd: string (optional) — override billing terms; maps to invoice.billing_terms_cd; defaults to 'DUE_RECEIPT'
  • multiClientInvoice: boolean (optional) — when true and recipient is 'BUYER' and entity is US, multiple clients may be combined; defaults to false
  • createdBy: string — identifier for the user performing the operation

This is the primary invoice creation procedure. It validates selected billing items, groups them into invoice batches by entity/currency/recipient rules, generates a sequential invoice number per batch, inserts each invoice header, and links billing items to invoices through the bridge table.

Step 1. Determine Expected Detail Type ​

  • Source: invoiceTypeCd from input parameters.
  • Action: Derive validation constraint (no database write).
  • Logic:
    • If invoiceTypeCd = 'COMMISSION': expected detail type is 'REV'.
    • If invoiceTypeCd = 'TOTAL_DUE': expected detail type is 'PAY'.

Step 2. Fetch and Validate Selected Billing Item Details ​

  • Source: billing_item_detail joined through billing_item to revenue_item to resolve entity, currency, client, buyer, and address information.
  • Action: READ billing_item_detail by the provided IDs; reject entire request if validation fails.
  • Logic:
    • If no records found for the provided IDs: reject with error — no billing item details found.
    • If any billing_item_detail.billing_item_detail_type_cd does not match the expected detail type from Step 1: reject with error — invoice type mismatch.

Step 3. Group Items into Invoice Batches ​

  • Source: Validated billing_item_detail records from Step 2.
  • Action: Partition records into batches (no database write); each batch becomes one invoice record.
  • Logic:
    • Build grouping key from: uta_entity_id, currency_cd (default 'USD' if null), recipient_party_id.
    • recipient_party_id for 'CLIENT' recipient: revenue_item.client_id. For 'BUYER' recipient: revenue_item.buyer_id.
    • UK entity (entity ID = 2): always append client_party_id to the key — one client per invoice, regardless of multiClientInvoice flag.
    • US entity, 'BUYER' recipient, multiClientInvoice = false: append client_party_id — one client per invoice.
    • US entity, 'BUYER' recipient, multiClientInvoice = true: do not append client_party_id — clients combined.
    • 'CLIENT' recipient: append recipient_party_id (which is the client) — one client per invoice implicitly.
    • Items missing recipient_party_id or uta_entity_id are silently skipped.
    • After grouping, count distinct client_party_id values per batch. If count > 1: set multi_client_ind = true for that batch.

Step 4. Calculate Due Date ​

  • Source: billingTermsCd from input (or default 'DUE_RECEIPT'), issueDate from input.
  • Action: Compute due_date (no database write).
  • Logic:
    • 'DUE_RECEIPT': due_date = issue_date (0 days offset).
    • 'NET_7': due_date = issue_date + 7 days.
    • 'NET_14': due_date = issue_date + 14 days.
    • 'NET_30': due_date = issue_date + 30 days.
    • 'NET_45': due_date = issue_date + 45 days.
    • 'NET_60': due_date = issue_date + 60 days.

Step 5. For Each Batch: Generate Invoice Number ​

  • Source: invoice_number_sequence for the batch's uta_entity_id. See 2.2 Invoice Number Generation.
  • Action: Call the invoice number generation procedure.
  • Logic:
    • Returns a formatted string such as UTA_US-2026-000001.

Step 6. For Each Batch: Calculate Totals ​

  • Source: billing_item_detail records in the batch.
  • Action: Compute aggregate amounts (no database write).
  • Logic:
    • total_gross_amt = SUM(billing_item_detail.billing_item_detail_gross_amt) across all items in the batch.
    • total_commission_amt = SUM(billing_item_detail.billing_item_detail_amt) across all items — populated only when invoiceTypeCd = 'COMMISSION'; set to NULL for 'TOTAL_DUE'.

Step 7. For Each Batch: INSERT into invoice ​

  • Source: Batch data assembled in Steps 3–6.
  • Action: INSERT one row into invoice.
  • Logic:
    • Set invoice.invoice_number = result from Step 5.
    • Set invoice.uta_entity_id = from batch.
    • Set invoice.issue_date = from input.
    • Set invoice.due_date = calculated in Step 4.
    • Set invoice.billing_terms_cd = from input, or 'DUE_RECEIPT' if not provided.
    • Set invoice.invoice_type_cd = from input.
    • Set invoice.invoice_recipient_cd = from input.
    • Set invoice.multi_client_ind = from batch grouping analysis.
    • Set invoice.currency_cd = from batch.
    • Set invoice.recipient_party_id = from batch.
    • Set invoice.recipient_address_id = primary auth_address_id from party_addresses for the recipient party.
    • Set invoice.contracted_party_id = from batch (populated for UK Total Due invoices).
    • Set invoice.contracted_address_id = primary auth_address_id from party_addresses for the contracted party.
    • Set invoice.total_gross_amt = calculated in Step 6.
    • Set invoice.total_commission_amt = calculated in Step 6 (or NULL).
    • Set invoice.status_cd = 'DRAFT'.
    • Set invoice.created_by = from input.
    • Returns the newly created invoice record including the generated invoice_id.
  • Source: Batch's billingItemDetailIds and the invoice_id returned in Step 7. See 2.3 Link Billing Items to Invoice.
  • Action: INSERT rows into billing_item_document.
  • Logic:
    • Determine document_type_cd: 'COMMISSION' maps to 'CI'; 'TOTAL_DUE' maps to 'BI'.
    • Insert bridge rows linking each billing item to the new invoice.

Side-effects:

  • Creates one invoice_number_sequence row per entity-year combination that does not yet exist, or increments invoice_number_sequence.current_sequence for existing rows.
  • Creates billing_item_document rows that prevent the same billing item details from being invoiced again under the same document type.

Postconditions:

  • One invoice record exists per batch, with status_cd = 'DRAFT'.
  • Each invoice's billing items are linked via billing_item_document rows.
  • invoice.invoice_number is globally unique, enforced by the uq_invoice_number constraint.

**PoC Artifact:** The `created_by` field defaults to `'SYSTEM'` in the PoC because user session resolution is not yet implemented. Production must resolve the actual user identity.

2.2 Invoice Number Generation ​

Operation: getNextInvoiceNumber

Trigger: Called internally by 2.1 Generate Invoices from Billing Items once per invoice batch, immediately before the invoice INSERT.

Input Parameters:

  • utaEntityId: integer — uta_entity.uta_entity_id for which to generate the next number

Generates the next sequential invoice number for a UTA entity. The number follows the format {prefix}-{year}-{sequence} where the sequence is zero-padded to 6 digits (e.g., UTA_US-2026-000001).

Step 1. Determine Current Year ​

  • Source: System clock.
  • Action: Capture current calendar year (no database read).
  • Logic:
    • current_year = current calendar year as an integer (e.g., 2026).

Step 2. Look Up Existing Sequence ​

  • Source: invoice_number_sequence.
  • Action: SELECT from invoice_number_sequence.
  • Logic:
    • Filter: invoice_number_sequence.uta_entity_id = input utaEntityId AND invoice_number_sequence.current_year = current_year.
    • Returns zero or one row.

Step 3a. If Sequence Exists: Increment ​

  • Source: Existing invoice_number_sequence row from Step 2.
  • Action: UPDATE invoice_number_sequence.
  • Logic:
    • Compute next_sequence = current_sequence + 1.
    • Set invoice_number_sequence.current_sequence = next_sequence.
    • Set invoice_number_sequence.updated_dt = current timestamp.
    • WHERE invoice_number_sequence.invoice_number_sequence_id = existing row's PK.
    • Return formatted number: {prefix}-{current_year}-{next_sequence zero-padded to 6 digits}.

Step 3b. If No Sequence Exists: Create New ​

  • Source: uta_entity table to resolve invoice_prefix.
  • Action: INSERT into invoice_number_sequence.
  • Logic:
    • Query uta_entity.invoice_prefix for the given utaEntityId. Fallback: first 2 uppercase characters of uta_entity.uta_entity_name, then 'XX' if unresolvable.
    • Set invoice_number_sequence.uta_entity_id = input utaEntityId.
    • Set invoice_number_sequence.current_year = current_year.
    • Set invoice_number_sequence.current_sequence = 1.
    • Set invoice_number_sequence.prefix = resolved prefix.
    • Set invoice_number_sequence.created_by = 'SYSTEM'.
    • Return formatted number: {prefix}-{current_year}-000001.

Side-effects:

  • Either increments invoice_number_sequence.current_sequence for an existing row or creates a new invoice_number_sequence row.

Postconditions:

  • invoice_number_sequence.current_sequence reflects the number just issued for this entity and year.
  • The returned formatted number is ready to be stored in invoice.invoice_number.

The PoC uses a read-then-write pattern (SELECT then UPDATE) rather than an atomic `UPDATE ... RETURNING` or a `SELECT FOR UPDATE`. Under concurrent access, two requests could read the same `current_sequence` and produce duplicate invoice numbers. The production implementation must use either a serializable transaction, `SELECT FOR UPDATE`, or a single atomic `UPDATE ... SET current_sequence = current_sequence + 1 RETURNING` statement.

2.3 Link Billing Items to Invoice ​

Operation: createBillingItemDocumentLinks

Trigger: Called internally by 2.1 Generate Invoices from Billing Items once per batch, immediately after the invoice INSERT.

Input Parameters:

  • billingItemDetailIds: integer[] — billing_item_detail.billing_item_detail_id values being invoiced
  • documentId: integer — the invoice.invoice_id of the newly created invoice
  • documentTypeCd: string — 'CI' (Commission Invoice) or 'BI' (Buyer Invoice)
  • createdBy: string — identifier for the user creating the links

Creates rows in the billing_item_document bridge table to associate billing items with an invoice. This procedure resolves billing_item_detail_id values to their parent billing_item_id values before inserting, since the bridge table links at the billing item level.

Step 1. Resolve Detail IDs to Billing Item IDs ​

  • Source: billing_item_detail joined to billing_item.
  • Action: SELECT to map each billing_item_detail_id to its parent billing_item.billing_item_id and billing_item.payment_term_ref.
  • Logic:
    • For each provided billing_item_detail_id, retrieve the parent billing_item_id.

Step 2. Deduplicate by Billing Item ​

  • Source: Result set from Step 1.
  • Action: De-duplicate (no database write).
  • Logic:
    • Since each billing_item has exactly one REV and one PAY detail, multiple detail IDs can resolve to the same billing item. Deduplicate the result set by billing_item_id.

Step 3. Batch INSERT into billing_item_document ​

  • Source: Deduplicated billing_item_id list from Step 2.
  • Action: INSERT one row per unique billing_item_id into billing_item_document.
  • Logic:
    • Set billing_item_document.billing_item_id = resolved parent billing item ID.
    • Set billing_item_document.document_type_cd = from input ('CI' or 'BI').
    • Set billing_item_document.document_id = from input (the invoice_id).
    • Set billing_item_document.payment_term_ref = billing_item.payment_term_ref from the parent billing item.
    • Set billing_item_document.created_by = from input.
    • Set billing_item_document.created_dt = current timestamp (database default).

Side-effects:

  • None beyond the rows inserted. The unique constraint uq_billing_item_document_item_type_doc on (billing_item_id, document_type_cd, document_id) will cause the insert to fail if the same billing item is already linked to this invoice with the same document type.

Postconditions:

  • Each unique billing item in the batch has a billing_item_document row linking it to the invoice.
  • The billing items are now considered "invoiced" for the relevant document type and will not appear in the available-for-invoicing list for that type.

The unique constraint `uq_billing_item_document_item_type_doc` on (`billing_item_id`, `document_type_cd`, `document_id`) prevents duplicate links. If the same billing item is already linked to this invoice with the same document type, the insert will fail with a constraint violation.

2.4 Update Invoice Status ​

Operation: updateInvoiceStatus

Trigger: User transitions an invoice's lifecycle state (e.g., issues an invoice, marks it paid, or voids it).

Input Parameters:

  • invoiceId: integer — invoice.invoice_id of the invoice to update
  • statusCd: string — the target status: 'DRAFT', 'ISSUED', 'PAID', or 'VOID'
  • updatedBy: string — identifier for the user performing the update

Transitions an invoice's status_cd to a new value within the allowed lifecycle.

Step 1. UPDATE invoice ​

  • Source: invoice record identified by invoiceId.
  • Action: UPDATE invoice.
  • Logic:
    • Set invoice.status_cd = from input.
    • Set invoice.updated_by = from input.
    • Set invoice.updated_dt = current timestamp.
    • WHERE invoice.invoice_id = input invoiceId.

Side-effects:

  • None beyond the status update on invoice.

Postconditions:

  • invoice.status_cd reflects the new target status.
  • PAID and VOID are terminal states; no further transitions are permitted.

The PoC does not enforce valid status transitions at the service layer. `updateInvoiceStatus` accepts any target status. Production should enforce the allowed-transitions table as preconditions:

  • 'DRAFT' may transition to 'ISSUED' or 'VOID'.
  • 'ISSUED' may transition to 'PAID' or 'VOID'.
  • 'PAID' and 'VOID' are terminal states (no further transitions).

2.5 Add or Update Invoice Bank Details ​

Operation: upsertInvoiceBankDetails

Trigger: Administrator creates or updates the payment instruction record for a UTA entity and currency combination, typically before generating invoice PDFs for that entity.

Input Parameters:

  • utaEntityId: integer — uta_entity.uta_entity_id these bank details belong to
  • currencyCd: string — currency these instructions apply to; maps to invoice_bank_details.currency_cd
  • bankName: string — bank name; maps to invoice_bank_details.bank_name
  • bankAddressId: integer (optional) — FK to address for the bank's physical address
  • accountNumber: string — bank account number; maps to invoice_bank_details.account_number
  • accountName: string (optional) — account holder name for display
  • abaRoutingNumber: string (optional) — US ABA routing number (9 digits)
  • swiftCode: string (optional) — SWIFT/BIC code (8–11 chars)
  • iban: string (optional) — IBAN (up to 34 chars)
  • sortCode: string (optional) — UK sort code (6 digits)
  • isActive: boolean — whether these details are currently active; maps to invoice_bank_details.is_active

Creates or updates payment instruction records for a UTA entity and currency combination. These records are used solely for display on invoice PDFs; they are distinct from bank_account records used for cash receipt and outbound payment processing.

Step 1. INSERT or UPDATE invoice_bank_details ​

  • Source: Input parameters.
  • Action: INSERT into or UPDATE invoice_bank_details depending on whether a record already exists for (utaEntityId, currencyCd).
  • Logic:
    • For INSERT: populate all provided fields plus created_by and created_dt.
    • For UPDATE: update the existing row matching invoice_bank_details.uta_entity_id = utaEntityId AND invoice_bank_details.currency_cd = currencyCd. Set all mutable fields plus updated_by and updated_dt.

Side-effects:

  • None beyond the INSERT or UPDATE on invoice_bank_details.

Postconditions:

  • Exactly one invoice_bank_details row exists for the given (uta_entity_id, currency_cd) combination.
  • When is_active = true, this record will be used when generating invoice PDFs for this entity and currency.

The unique constraint `uq_invoice_bank_entity_currency` on (`uta_entity_id`, `currency_cd`) ensures at most one bank detail record per entity-currency pair. An upsert pattern (INSERT ... ON CONFLICT UPDATE) is the recommended production approach.

2.6 Generate Invoice PDF ​

Operation: generateInvoicePdf

Trigger: User requests a PDF download for an existing invoice.

Input Parameters:

  • invoiceId: integer — invoice.invoice_id of the invoice to render
  • recipientAddress: string (optional) — pre-resolved recipient address text
  • contractedPartyAddress: string (optional) — pre-resolved contracted party address (UK Total Due invoices only)

Produces a PDF buffer for a given invoice by aggregating data from multiple tables. No database mutations occur — the generated PDF is returned as an in-memory buffer for immediate download or transmission.

Step 1. Fetch Invoice Display Data ​

  • Source: invoice joined to uta_entity, party (recipient and contracted), and code_master (status, type, and terms descriptions).
  • Action: SELECT to retrieve enriched invoice header.
  • Logic:
    • If no invoice record exists for invoiceId: reject with error.

Step 2. Fetch Line Items ​

  • Source: billing_item_detail linked to this invoice through billing_item_document where billing_item_document.document_id = invoiceId.
  • Action: SELECT billing item detail rows with deal, revenue item, and party context.
  • Logic:
    • If no line items found: reject with error.

Step 3. Fetch UTA Entity Details ​

  • Source: uta_entity joined to address (entity address) and uta_entity_contact_method joined to contact_method (phone, fax, email).
  • Action: SELECT entity name, address, logo path, and contact information for the PDF header.
  • Logic:
    • If no uta_entity record found: reject with error.

Step 4. Fetch Bank Details ​

  • Source: invoice_bank_details joined to address (bank address).
  • Action: SELECT payment instruction details for the PDF footer.
  • Logic:
    • Filter: invoice_bank_details.uta_entity_id = invoice's uta_entity_id AND invoice_bank_details.currency_cd = invoice's currency_cd AND invoice_bank_details.is_active = true.
    • If no matching active record exists, the PDF is generated without payment instructions.

Step 5. Render PDF ​

  • Source: Assembled data from Steps 1–4 plus any pre-resolved addresses from input.
  • Action: Pass to PDF rendering engine; return binary buffer.
  • Logic:
    • No database mutation occurs during rendering.

Side-effects:

  • None. No database rows are written.

Postconditions:

  • A binary PDF buffer is returned to the caller.
  • No invoice status change occurs; the invoice remains in its current status.

**PoC Artifact:** The PDF rendering uses React-based server-side rendering to produce the PDF buffer. The production implementation may use a different PDF generation strategy.

2.7 Generate Client Statement ​

Operation: generateClientStatement

Trigger: User requests a client statement PDF for a given client and date range from the Statements UI.

Input Parameters:

  • clientId: integer — party.party_id of the client
  • dateFrom: date — start of the statement period; maps to generated_statement.date_from
  • dateTo: date — end of the statement period; maps to generated_statement.date_to
  • currencyCd: string (optional) — currency filter; defaults to 'USD'
  • generatedBy: string (optional) — user initiating generation; defaults to 'system'
  • excludedPaymentItemIds: integer[] (optional) — payment_item.payment_item_id values to exclude; defaults to empty
  • saveToStorage: boolean (optional) — whether to persist the PDF and metadata; defaults to true

Produces a client statement PDF summarizing all posted payment transactions for a client within a date range. When saveToStorage = true, the procedure uploads the PDF to object storage and persists metadata in generated_statement.

Step 1. Generate Statement Reference ​

  • Source: System clock and random UUID.
  • Action: Construct a unique reference string (no database write).
  • Logic:
    • Format: CS-{timestamp}-{8-char UUID segment} (e.g., CS-1709400000000-A1B2C3D4).

Step 2. Fetch Client Info ​

  • Source: party table.
  • Action: SELECT party by clientId.
  • Logic:
    • If no party record found: reject with error.

Step 3. Fetch UTA Entity Info ​

  • Source: uta_entity joined to address and uta_entity_contact_method joined to contact_method.
  • Action: SELECT default entity header information for the PDF.
  • Logic:
    • Retrieve entity name, address, phone, and fax for the PDF header.

Step 4. Aggregate Transactions ​

  • Source: payment_item.
  • Action: SELECT posted payment items for the client within the date range.
  • Logic:
    • Filter: payment_item.client_id = clientId, payment_item.payment_item_posting_status_cd = 'P', payment_item.posting_dt between dateFrom and dateTo.
    • Exclude any payment_item_id values present in excludedPaymentItemIds.
    • Group by posting date and deal. For each group, accumulate:
      • Gross amount: SUM of payment_item.payment_item_amt across all items in the group.
      • Agency commission: SUM of payment_item.payment_item_amt where payment_item.payment_item_type_cd = 'S'.
      • Third-party payouts: individual amounts where payment_item.payment_item_type_cd = 'P'; party name resolved via payment_item.payment_party_id joined to party.
      • Net to client: gross - commission + payout_total (payouts are negative amounts).

Step 5. Calculate Totals ​

  • Source: Aggregated transaction groups from Step 4.
  • Action: Compute statement-level totals (no database write).
  • Logic:
    • total_gross = SUM(gross_amount) across all transaction groups.
    • total_commission = SUM(agency_commission) across all transaction groups.
    • total_net = SUM(net_amount) across all transaction groups.

Step 6. Generate PDF ​

  • Source: Client info, UTA entity info, transaction groups, and totals from Steps 2–5.
  • Action: Pass assembled statement data to PDF rendering engine; receive binary buffer.
  • Logic:
    • No database mutation occurs during rendering.

Step 7. Persist to Storage and Database (conditional on saveToStorage = true) ​

  • Source: PDF buffer from Step 6, statement reference from Step 1.
  • Action: Upload to object storage; INSERT into generated_statement.
  • Logic:
    • Upload PDF buffer to object storage using key format: statements/client/{clientId}/{date_range}.pdf. Receive s3_bucket and s3_key.
    • INSERT into generated_statement:
      • Set generated_statement.statement_type_cd = 'CLIENT_STATEMENT'.
      • Set generated_statement.statement_ref = from Step 1.
      • Set generated_statement.client_id = input clientId.
      • Set generated_statement.date_from = input dateFrom.
      • Set generated_statement.date_to = input dateTo.
      • Set generated_statement.currency_cd = input currencyCd.
      • Set generated_statement.s3_bucket = from upload result.
      • Set generated_statement.s3_key = from upload result.
      • Set generated_statement.file_name = {statementRef}.pdf or from PDF engine.
      • Set generated_statement.file_size_bytes = byte length of the PDF buffer.
      • Set generated_statement.generation_status_cd = 'COMPLETED'.
      • Set generated_statement.metadata = JSON: { totalGross, totalCommission, totalNet, transactionCount, excludedPaymentItemIds }.
      • Set generated_statement.generated_by = input generatedBy.
      • Set generated_statement.created_by = input generatedBy.
      • Set generated_statement.updated_by = input generatedBy.

Side-effects:

  • When saveToStorage = true: creates one generated_statement row and stores one PDF in object storage.

Postconditions:

  • When saveToStorage = true: generated_statement.generation_status_cd = 'COMPLETED' and the PDF is retrievable via s3_bucket + s3_key.
  • When saveToStorage = false: no database records are created; the PDF buffer is returned directly to the caller.

2.8 Generate Settlement Statement ​

Operation: generateSettlementStatement

Trigger: User requests a settlement statement PDF for a given client and date range from the Statements UI.

Input Parameters:

  • clientId: integer — party.party_id of the client
  • dateFrom: date — start of the statement period; maps to generated_statement.date_from
  • dateTo: date — end of the statement period; maps to generated_statement.date_to
  • currencyCd: string (optional) — currency filter; defaults to 'USD'
  • generatedBy: string (optional) — user initiating generation; defaults to 'system'
  • saveToStorage: boolean (optional) — whether to persist; defaults to true

Produces a settlement statement PDF showing how PAY amounts were divided among a client's party members for deals within a date range.

Step 1. Generate Statement Reference ​

  • Source: System clock and random UUID.
  • Action: Construct a unique reference string (no database write).
  • Logic:
    • Format: SS-{timestamp}-{8-char UUID segment}.

Step 2. Fetch Client Info ​

  • Source: party table.
  • Action: SELECT party by clientId.
  • Logic:
    • Same as 2.7 Step 2. If no record found: reject with error.

Step 3. Fetch UTA Entity Info ​

  • Source: uta_entity joined to address and uta_entity_contact_method joined to contact_method.
  • Action: SELECT default entity header information.
  • Logic:

Step 4. Aggregate Settlement Engagements ​

  • Source: participant_settlement_item joined to participant_settlement, payment_item, and deal.
  • Action: SELECT settlement data for the client within the date range.
  • Logic:
    • Filter: participant_settlement_item.payment_party_id = input clientId, payment_item.posting_dt between dateFrom and dateTo.
    • Group results by deal.deal_id to produce engagement-level summaries.
    • For each engagement, accumulate:
      • Guarantee: SUM(payment_item.payment_item_amt) across all items for the deal.
      • Commission due: derived from participant_settlement_item.participant_settlement_commission_amt if a flat amount is set, otherwise computed as payment_item.payment_item_amt * participant_settlement_item.participant_settlment_commission_perc.
      • Net gross: guarantee + overage - backout.

Step 5. Calculate Totals ​

  • Source: Engagement-level summaries from Step 4.
  • Action: Compute statement-level totals (no database write).
  • Logic:
    • total_guarantee = SUM(guarantee) across all engagements.
    • total_net_gross = SUM(net_gross) across all engagements.
    • total_commission_due = SUM(commission_due) across all engagements.
    • net_amount = total_net_gross - total_commission_due.

Step 6. Generate PDF ​

  • Source: Assembled settlement data from Steps 2–5.
  • Action: Pass to PDF rendering engine; receive binary buffer.
  • Logic:
    • No database mutation occurs during rendering.

Step 7. Persist to Storage and Database (conditional on saveToStorage = true) ​

  • Source: PDF buffer from Step 6, statement reference from Step 1.
  • Action: Upload to object storage; INSERT into generated_statement.
  • Logic:
    • Upload PDF buffer using key format: statements/settlement/{clientId}/{date_range}.pdf.
    • INSERT into generated_statement:
      • Set generated_statement.statement_type_cd = 'SETTLEMENT_STATEMENT'.
      • Set generated_statement.statement_ref = from Step 1.
      • Set generated_statement.client_id = input clientId.
      • Set generated_statement.date_from = input dateFrom.
      • Set generated_statement.date_to = input dateTo.
      • Set generated_statement.currency_cd = input currencyCd.
      • Set generated_statement.s3_bucket and generated_statement.s3_key = from upload result.
      • Set generated_statement.file_name = {statementRef}.pdf or from PDF engine.
      • Set generated_statement.file_size_bytes = byte length of the PDF buffer.
      • Set generated_statement.generation_status_cd = 'COMPLETED'.
      • Set generated_statement.metadata = JSON: { totalGuarantee, totalNetGross, totalCommissionDue, netAmount, engagementCount }.
      • Set generated_statement.generated_by = input generatedBy.
      • Set generated_statement.created_by = input generatedBy.
      • Set generated_statement.updated_by = input generatedBy.

Side-effects:

  • When saveToStorage = true: creates one generated_statement row and stores one PDF in object storage.

Postconditions:

  • When saveToStorage = true: generated_statement.generation_status_cd = 'COMPLETED' and the PDF is retrievable via s3_bucket + s3_key.
  • When saveToStorage = false: no database records are created.

2.9 Generate Deal Statement ​

Operation: generateDealStatement

Trigger: User requests a deal statement PDF for a specific deal from the Statements UI.

Input Parameters:

  • dealId: integer — deal.deal_id of the deal
  • generatedBy: string (optional) — user initiating generation; defaults to 'system'
  • saveToStorage: boolean (optional) — whether to persist; defaults to true

Produces a deal statement PDF showing all revenue items, receivables, cash applications, and payee allocations for a specific deal.

Step 1. Generate Statement Reference ​

  • Source: System clock and random UUID.
  • Action: Construct a unique reference string (no database write).
  • Logic:
    • Format: DS-{timestamp}-{8-char UUID segment}.

Step 2. Fetch Deal Info ​

  • Source: deal joined to department, uta_entity, deal_party, and party.
  • Action: SELECT deal header information.
  • Logic:
    • Resolve: deal name, deal reference, dates, active indicator; client name (role 'CLIENT'), buyer name (role 'BUYER'), loanout name (role 'LOANOUT'); department name; UTA entity name.

Step 3. Fetch Deal Payees ​

  • Source: deal_party joined to party and bank_account.
  • Action: SELECT all parties with their commission percentages, commission amounts, roles, and bank information.
  • Logic:
    • Returns all deal_party rows for the deal with party and payment details resolved.

Step 4. Build Revenue Item Groups ​

  • Source: sales_item, billing_item, billing_item_detail, cash_receipt_application.
  • Action: SELECT and aggregate data per revenue item.
  • Logic:
    • For each sales_item belonging to the deal, fetch billing_item records where billing_item.revenue_item_id = sales_item.sales_item_id.
    • For each billing item, fetch billing_item_detail (REV and PAY rows).
    • For each detail, query cash_receipt_application to compute SUM(cash_receipt_application.cash_receipt_amt_applied) — the total cash applied against that detail.
    • Calculate payee allocations by multiplying the PAY cash applied amount by each payee's commission percentage.

Step 5. Calculate Deal Totals ​

  • Source: Revenue item groups from Step 4.
  • Action: Aggregate across all revenue item groups (no database write).
  • Logic:
    • Aggregate: total_gross, total_commission, total_cash_rev, total_payable, total_cash_pay.
    • Aggregate: total_primary_payee_amt, total_other_payees_amt.

Step 6. Generate PDF ​

  • Source: Assembled deal statement data from Steps 2–5.
  • Action: Pass to PDF rendering engine; receive binary buffer.
  • Logic:
    • No database mutation occurs during rendering.

Step 7. Persist to Storage and Database (conditional on saveToStorage = true) ​

  • Source: PDF buffer from Step 6, statement reference from Step 1.
  • Action: Upload to object storage; INSERT into generated_statement.
  • Logic:
    • Upload PDF buffer using key format: statements/deal/{dealId}/{statementRef}.pdf.
    • INSERT into generated_statement:
      • Set generated_statement.statement_type_cd = 'DEAL_STATEMENT'.
      • Set generated_statement.statement_ref = from Step 1.
      • Set generated_statement.client_id = NULL (deal statements are not client-specific).
      • Set generated_statement.currency_cd = deal currency (default 'USD').
      • Set generated_statement.s3_bucket and generated_statement.s3_key = from upload result.
      • Set generated_statement.file_name = {statementRef}.pdf or from PDF engine.
      • Set generated_statement.file_size_bytes = byte length of the PDF buffer.
      • Set generated_statement.generation_status_cd = 'COMPLETED'.
      • Set generated_statement.metadata = JSON: { dealId, dealName, dealReference, clientName, totalGross, totalCommission, totalCashRev, totalPayable, totalCashPay, totalPrimaryPayeeAmt, totalOtherPayeesAmt, revenueItemCount, receivableCount }.
      • Set generated_statement.generated_by = input generatedBy.
      • Set generated_statement.created_by = input generatedBy.
      • Set generated_statement.updated_by = input generatedBy.

Side-effects:

  • When saveToStorage = true: creates one generated_statement row and stores one PDF in object storage.

Postconditions:

  • When saveToStorage = true: generated_statement.generation_status_cd = 'COMPLETED' and the PDF is retrievable via s3_bucket + s3_key.
  • generated_statement.client_id is NULL for deal statements.

2.10 Generate Settlement Statement V2 ​

Operation: generateSettlementStatementV2

Trigger: User requests a revenue-item-level settlement statement from the Statements UI.

Input Parameters:

  • revenueItemId: integer — sales_item.sales_item_id of the revenue item
  • generatedBy: string (optional) — user initiating generation; defaults to 'system'
  • saveToStorage: boolean (optional) — whether to persist; defaults to true

Produces a revenue-item-focused settlement statement showing cash receipts, receivables, and distribution breakdown for a single revenue item.

Step 1. Generate Statement Reference ​

  • Source: System clock and random UUID.
  • Action: Construct a unique reference string (no database write).
  • Logic:
    • Format: SS-{timestamp}-{8-char UUID segment}.

Step 2. Fetch Revenue Item Info ​

  • Source: sales_item joined to deal, party (client), department, and uta_entity.
  • Action: SELECT revenue item context.
  • Logic:
    • Resolve: revenue item name, reference, gross amount, commission, currency, and related deal and client context.

Step 3. Build Revenue Item Group ​

  • Source: billing_item, cash_receipt_application, cash_receipt_worksheet, cash_receipt.
  • Action: SELECT and aggregate cash receipt data for the revenue item.
  • Logic:
    • Fetch all billing_item records where billing_item.revenue_item_id = input revenueItemId.
    • Build cash receipt groups from the billing items' cash applications, worksheets, and receipts.
    • Calculate receivable-level and cash-receipt-level totals.

Step 4. Build Distribution Summary ​

  • Source: Settlement data from Step 3.
  • Action: Summarize how settlement amounts are distributed across payees (no additional database writes).
  • Logic:
    • Aggregate payout amounts by payee from the settlement data.

Step 5. Generate PDF ​

  • Source: Assembled settlement V2 data from Steps 2–4.
  • Action: Pass to PDF rendering engine; receive binary buffer.
  • Logic:
    • No database mutation occurs during rendering.

Step 6. Persist to Storage and Database (conditional on saveToStorage = true) ​

  • Source: PDF buffer from Step 5, statement reference from Step 1.
  • Action: Upload to object storage; INSERT into generated_statement.
  • Logic:
    • Upload PDF buffer using key format: statements/settlement/{revenueItemId}/{statementRef}.pdf.
    • INSERT into generated_statement:
      • Set generated_statement.statement_type_cd = 'SETTLEMENT_STATEMENT'.
      • Set generated_statement.statement_ref = from Step 1.
      • Set generated_statement.generation_status_cd = 'COMPLETED'.
      • Set generated_statement.metadata = JSON containing revenue item details and totals.
      • Set generated_statement.generated_by = input generatedBy.
      • Set generated_statement.created_by = input generatedBy.
      • Set generated_statement.updated_by = input generatedBy.

Side-effects:

  • When saveToStorage = true: creates one generated_statement row and stores one PDF in object storage.

Postconditions:

  • When saveToStorage = true: generated_statement.generation_status_cd = 'COMPLETED' and the PDF is retrievable.
  • generated_statement.statement_type_cd = 'SETTLEMENT_STATEMENT' (same code as 2.8, distinguished by metadata content and context).

2.11 Generate AR Aging Statement ​

Operation: generateArAgingStatement

Trigger: User requests an accounts receivable aging report from the Statements UI, optionally filtered by client.

Input Parameters:

  • clientId: integer (optional) — party.party_id; if omitted, all clients are included
  • asOfDate: date (optional) — date used to calculate aging; defaults to today
  • openItemsOnly: boolean (optional) — if true, exclude fully paid items; defaults to true
  • generatedBy: string (optional) — user initiating generation; defaults to 'system'
  • saveToStorage: boolean (optional) — whether to persist; defaults to true

Produces an accounts receivable aging report showing open billing items grouped by aging buckets (Current, 1–30, 31–60, 61–90, 90+).

Step 1. Generate Statement Reference ​

  • Source: System clock and random UUID.
  • Action: Construct a unique reference string (no database write).
  • Logic:
    • Format: ARA-{timestamp}-{8-char UUID segment}.

Step 2. Aggregate AR Line Items ​

  • Source: billing_item, billing_item_detail, cash_receipt_application.
  • Action: SELECT billing item data and compute aging per item.
  • Logic:
    • For each billing_item (optionally filtered by client, optionally filtered to open items only):
      • Fetch billing_item_detail (REV and PAY) to determine original_amount from billing_item_detail.billing_item_detail_gross_amt.
      • Query cash_receipt_application to compute applied_amount = SUM(cash_receipt_application.cash_receipt_amt_applied) for each detail.
      • Calculate remaining_amount = original_amount - applied_amount.
      • If openItemsOnly = true: skip items where remaining_amount <= 0.
      • Compute days_past_due = (as_of_date - billing_item.billing_item_due_dt) in days.
      • Place remaining_amount into the appropriate aging bucket:
        • days_past_due <= 0: Current.
        • 1–30: 1–30 Days.
        • 31–60: 31–60 Days.
        • 61–90: 61–90 Days.
        • > 90: 90+ Days.

Step 3. Calculate Aging Summary ​

  • Source: Line items from Step 2.
  • Action: Sum each aging bucket across all line items (no database write).
  • Logic:
    • One total per bucket: current, 1–30, 31–60, 61–90, 90+.

Step 4. Calculate Statement Totals ​

  • Source: Line items from Step 2.
  • Action: Compute overall totals (no database write).
  • Logic:
    • total_original = SUM(original_amount) across all line items.
    • total_applied = SUM(applied_amount) across all line items.
    • total_outstanding = total_original - total_applied.

Step 5. Generate PDF ​

  • Source: Assembled AR aging data from Steps 2–4.
  • Action: Pass to PDF rendering engine; receive binary buffer.
  • Logic:
    • No database mutation occurs during rendering.

Step 6. Persist to Storage and Database (conditional on saveToStorage = true) ​

  • Source: PDF buffer from Step 5, statement reference from Step 1.
  • Action: Upload to object storage; INSERT into generated_statement.
  • Logic:
    • Upload PDF buffer using key format: statements/ar-aging/{statementRef}.pdf.
    • INSERT into generated_statement:
      • Set generated_statement.statement_type_cd = 'AR_AGING_STATEMENT'.
      • Set generated_statement.statement_ref = from Step 1.
      • Set generated_statement.client_id = input clientId (or NULL if not provided).
      • Set generated_statement.generation_status_cd = 'COMPLETED'.
      • Set generated_statement.metadata = JSON: { asOfDate, clientFilter, statusFilter, itemCount, totalOriginal, totalApplied, totalOutstanding, agingSummary: { current, days1to30, days31to60, days61to90, days90Plus } }.
      • Set generated_statement.generated_by = input generatedBy.
      • Set generated_statement.created_by = input generatedBy.
      • Set generated_statement.updated_by = input generatedBy.

Side-effects:

  • When saveToStorage = true: creates one generated_statement row and stores one PDF in object storage.

Postconditions:

  • When saveToStorage = true: generated_statement.generation_status_cd = 'COMPLETED' and the PDF is retrievable.
  • generated_statement.client_id is NULL when no client filter was applied.

2.12 Generate Invoice via Deal Statement Service ​

Operation: generateInvoiceFromDealStatement

Trigger: User requests a quick invoice PDF from the Statements UI by selecting billing items for a specific party.

Input Parameters:

  • partyId: integer — party.party_id of the target party (client or buyer)
  • targetType: string — 'CLIENT' or 'BUYER'
  • billingItemIds: integer[] — billing_item.billing_item_id values to include
  • isUkEntity: boolean (optional) — whether to use UK formatting and tax; defaults to false
  • generatedBy: string (optional) — user initiating generation; defaults to 'system'

**PoC Artifact:** This is a secondary invoice generation pathway implemented for quick PDF generation from the Statements UI. Unlike [2.1 Generate Invoices from Billing Items](#21-generate-invoices-from-billing-items), it does NOT create an `invoice` record or link via `billing_item_document`. It generates a PDF, optionally saves it, and records the result as a `generated_statement` row with `statement_type_cd = 'INVOICE'`. The production system should consolidate these two pathways.

Step 1. Generate Invoice Number ​

  • Source: System clock and random number.
  • Action: Construct a temporary invoice number (no database write, no use of invoice_number_sequence).
  • Logic:
    • Format: {prefix}-{last 4 digits of timestamp}-{random 6-digit number}. This is distinct from the formal invoice_number_sequence approach used in 2.2.

Step 2. Build Line Items ​

  • Source: billing_item joined to sales_item, deal, and billing_item_detail.
  • Action: SELECT billing item data for each provided billing_item_id.
  • Logic:
    • For each billing item, query billing_item_detail to find the relevant amount: 'REV' detail for 'CLIENT' invoices; 'PAY' detail for 'BUYER' invoices.
    • Construct a description line containing: deal name, date, revenue item reference, and due date.

Step 3. Calculate Totals with Tax ​

  • Source: Line item amounts from Step 2, isUkEntity flag from input.
  • Action: Apply tax adjustments to subtotal (no database write).
  • Logic:
    • UK entity: VAT at 20% added to subtotal.
    • US buyer invoice: WHT at 30% subtracted from subtotal.
    • All other cases: no tax adjustment.

Step 4. Generate PDF ​

  • Source: Entity info, bill-to party, line items, totals, and payment instructions from Steps 2–3.
  • Action: Pass to PDF rendering engine; receive binary buffer.
  • Logic:
    • No database mutation occurs during rendering.

Step 5. Persist (conditional on saveToStorage = true) ​

  • Source: PDF buffer from Step 4, invoice number from Step 1.
  • Action: INSERT into generated_statement.
  • Logic:
    • INSERT into generated_statement:
      • Set generated_statement.statement_type_cd = 'INVOICE'.
      • Set generated_statement.statement_ref = the generated invoice number from Step 1.
      • Set generated_statement.client_id = input partyId.
      • Set generated_statement.generation_status_cd = 'COMPLETED'.
      • Set generated_statement.generated_by = input generatedBy.

Side-effects:

  • When saveToStorage = true: creates one generated_statement row. No invoice record is created.

Postconditions:

  • A generated_statement row exists with statement_type_cd = 'INVOICE'.
  • No invoice record exists. No billing_item_document links are created. The billing items remain available for formal invoicing via 2.1.

3. Business Rules & Logic ​

3.1 Invoice Number Format and Uniqueness ​

Business rule: Every invoice must have a globally unique number that identifies the issuing entity, the calendar year, and a sequential counter within that entity-year combination.

Data-level enforcement:

  • Read: invoice_number_sequence.prefix, invoice_number_sequence.current_year, invoice_number_sequence.current_sequence for the given uta_entity_id.
  • Guard: Uniqueness enforced by the uq_invoice_number unique index on invoice.invoice_number. Concurrent number generation must use SELECT FOR UPDATE or an atomic increment to prevent duplicate sequence values.
  • Write: Set invoice.invoice_number = {prefix}-{year}-{sequence zero-padded to 6 digits} (e.g., UTA_US-2026-000001). Known prefixes: UTA_US, UTA_UK, ML, UTA_S, RC. Source: uta_entity.invoice_prefix.

3.2 Invoice Status Lifecycle ​

Business rule: An invoice progresses forward only through defined lifecycle states. Terminal states cannot be exited.

Data-level enforcement:

  • Read: invoice.status_cd before any transition attempt.
  • Guard: Reject the transition if the current status does not permit it. Allowed transitions:
    • 'DRAFT' → 'ISSUED' or 'VOID'.
    • 'ISSUED' → 'PAID' or 'VOID'.
    • 'PAID' and 'VOID' are terminal — no transition out of either is permitted.
  • Write: Set invoice.status_cd to the target status.

3.3 Which Billing Items Can Be Invoiced ​

Business rule: A billing item detail may only be invoiced once per invoice document type. Type consistency must be maintained across all details on a single invoice.

Data-level enforcement:

  • Read: billing_item_document to check for existing links with matching billing_item_id and document_type_cd.
  • Guard:
    • If a row exists in billing_item_document where billing_item_id matches and document_type_cd = 'CI' (for REV details) or 'BI' (for PAY details): reject the item — already invoiced.
    • If any selected billing_item_detail.billing_item_detail_type_cd does not match the expected type for the requested invoice_type_cd: reject the entire request.
    • 'COMMISSION' invoices require all 'REV' details. 'TOTAL_DUE' invoices require all 'PAY' details. Mixing types within a single invoice is not permitted.
  • Write: After invoice creation, INSERT billing_item_document rows to mark the billing items as invoiced.

3.4 Invoice Batching Rules ​

Business rule: A single invoice may only span one UTA entity, one currency, and one recipient party. UK entities impose the additional constraint of one client per invoice.

Data-level enforcement:

  • Read: billing_item_detail.uta_entity_id, billing_item_detail.currency_cd, revenue_item.client_id, revenue_item.buyer_id for each selected item.
  • Guard: Items with different entities or currencies are silently grouped into separate invoices rather than causing a validation failure. Items missing recipient_party_id or uta_entity_id are silently skipped.
  • Write: Set invoice.multi_client_ind:
    • UK entity (entity ID = 2): always false; client_party_id always included in grouping key.
    • US entity, 'BUYER' recipient, multiClientInvoice = false: false; one client per invoice.
    • US entity, 'BUYER' recipient, multiClientInvoice = true: true when distinct client_party_id count > 1.
    • 'CLIENT' recipient: one client per invoice implicitly; multi_client_ind reflects actual distinct client count.

3.5 Statement Reference Uniqueness ​

Business rule: Each generated statement must have a unique reference identifier displayed on the PDF and used for retrieval.

Data-level enforcement:

  • Read: No pre-insert uniqueness check is performed.
  • Guard: Uniqueness is enforced at the application layer during generation — the reference is prefixed by statement type (CS-, SS-, DS-, ARA-) and includes a timestamp and UUID segment to minimize collision risk. No database unique constraint exists on generated_statement.statement_ref.
  • Write: Set generated_statement.statement_ref = constructed reference string.

3.6 Statement Generation Rules by Type ​

Business rule: Each statement type draws from a distinct primary data source. The key input, date range requirement, and client filter requirement differ by type.

Data-level enforcement:

  • Read: Primary data source per type:
    • 'CLIENT_STATEMENT': payment_item where payment_item_posting_status_cd = 'P' and posting_dt in range, filtered by client_id.
    • 'SETTLEMENT_STATEMENT': participant_settlement_item joined to payment_item, filtered by payment_party_id and posting_dt in range.
    • 'DEAL_STATEMENT': sales_item → billing_item → billing_item_detail → cash_receipt_application, scoped to a single deal_id.
    • 'SETTLEMENT_STATEMENT' (V2): billing_item → cash_receipt_application → cash_receipt, scoped to a single revenue_item_id.
    • 'AR_AGING_STATEMENT': billing_item → billing_item_detail → cash_receipt_application, with optional client_id filter and as_of_date.
  • Guard: For client and settlement statements: reject if clientId resolves to no party record.
  • Write: generated_statement.statement_type_cd must be set to the correct code for the generated type.

3.7 Statement Metadata Structure ​

Business rule: The metadata JSONB column on generated_statement stores type-specific summary data for display and audit purposes. Its structure varies by statement type.

Data-level enforcement:

  • Read: Not enforced by a database schema constraint; the JSONB column accepts any valid JSON.
  • Guard: Each generation procedure is responsible for constructing the correct metadata shape.
  • Write: Set generated_statement.metadata to the appropriate JSON structure per type (see Section 2 procedure steps for the full field lists per type).

4. Field Mapping & Transformation ​

4.1 Billing Items to Invoice (invoice INSERT) ​

Source Table.FieldTarget Table.FieldTransform
revenue_item.uta_entity_idinvoice.uta_entity_idCopied as-is from the revenue item's owning entity.
revenue_item.currency_cdinvoice.currency_cdCopied as-is; defaults to 'USD' if null.
revenue_item.client_id (for 'CLIENT' recipient)invoice.recipient_party_idConditional: use client_id when invoice_recipient_cd = 'CLIENT'.
revenue_item.buyer_id (for 'BUYER' recipient)invoice.recipient_party_idConditional: use buyer_id when invoice_recipient_cd = 'BUYER'.
party_addresses.auth_address_idinvoice.recipient_address_idPrimary address of the recipient party (primary_ind = true).
revenue_item.contracted_party_idinvoice.contracted_party_idPopulated for UK Total Due invoices only.
party_addresses.auth_address_id (contracted)invoice.contracted_address_idPrimary address of the contracted party.
billing_item_detail.billing_item_detail_gross_amtinvoice.total_gross_amtSUM() across all details in the batch. Stored as decimal(15,2). Transmitted as string to preserve precision.
billing_item_detail.billing_item_detail_amtinvoice.total_commission_amtSUM() across details. Only for 'COMMISSION' invoices; NULL for 'TOTAL_DUE'. Transmitted as string.
—invoice.status_cdDefaulted to 'DRAFT' on creation.
—invoice.invoice_numberSystem-generated via invoice_number_sequence.

4.2 Billing Item Detail IDs to Bridge Table (billing_item_document INSERT) ​

Source Table.FieldTarget Table.FieldTransform
billing_item_detail.billing_item_idbilling_item_document.billing_item_idResolved from detail ID to parent billing item ID. Deduplicated before insert.
invoice.invoice_type_cdbilling_item_document.document_type_cd'COMMISSION' maps to 'CI'; 'TOTAL_DUE' maps to 'BI'.
invoice.invoice_idbilling_item_document.document_idThe newly created invoice's PK.
billing_item.payment_term_refbilling_item_document.payment_term_refCopied from the parent billing item.

4.3 Posted Payment Items to Client Statement Content ​

Source Table.FieldTarget Table.FieldTransform
payment_item.posting_dtStatement transaction dateGrouped by posting date + payment_item.deal_id.
payment_item.payment_item_amt (all types)Gross amountSUM() across all payment items in the group.
payment_item.payment_item_amt where payment_item_type_cd = 'S'Agency commissionSUM() of settlement-type items only.
payment_item.payment_item_amt where payment_item_type_cd = 'P'Third-party payoutsIndividual entries (negative amounts). Party name resolved via payment_item.payment_party_id joined to party.display_name.
deal.deal_referenceDeal referenceJoined via payment_item.deal_id.
deal.deal_nameProject nameJoined via payment_item.deal_id.
ComputedNet to clientgross - commission + payout_total (payouts already negative).

4.4 Settlement Items to Settlement Statement Content ​

Source Table.FieldTarget Table.FieldTransform
deal.deal_idEngagement groupGrouped by deal_id from payment_item.deal_id.
payment_item.payment_item_amtGuaranteeSUM() of payment amounts per engagement.
participant_settlement_item.participant_settlement_commission_amtCommission dueFlat amount when set directly.
payment_item.payment_item_amt * participant_settlement_item.participant_settlment_commission_percCommission dueComputed when no flat commission amount is set.
ComputedNet grossguarantee + overage - backout.

4.5 Billing Items to AR Aging Line Items ​

Source Table.FieldTarget Table.FieldTransform
billing_item_detail.billing_item_detail_gross_amtOriginal amountREV gross preferred; falls back to PAY gross.
cash_receipt_application.cash_receipt_amt_appliedApplied amountSUM() across all applications for each detail.
ComputedRemaining amountoriginal_amount - applied_amount.
billing_item.billing_item_due_dtDays past due(as_of_date - billing_item_due_dt) in days.
ComputedAging bucketRemaining amount placed in one bucket based on days_past_due: Current (<=0), 1–30, 31–60, 61–90, 90+.

5. Cross-References ​

DocumentRelationship
Invoices and Statements Data ModelSchema definitions, field types, constraints, and status lifecycles for all tables mutated by the procedures in this document.
Invoices and Statements QueriesAll read-only query operations referenced by the procedures in this document, including the eligibility check for invoiceable billing items and the data aggregation queries for statement generation.
Billing Items Data ModelDefines billing_item, billing_item_detail, and billing_item_document — the source data for invoice creation and the bridge table that links billing items to invoices.
Billing Items QueriesQueries for fetching billing item details used as input to invoice generation.
Cash Receipts Data ModelDefines cash_receipt_application which is queried during AR aging and deal statement generation to compute applied amounts against billing item details.
Settlements Data ModelDefines participant_settlement and participant_settlement_item — the data sources for settlement statement generation.
Parties Data ModelDefines party, party_addresses, and related tables referenced as recipients, clients, and contracted parties on invoices and statements.
Worksheets ProceduresCash application and settlement workflows that produce the payment_item, cash_receipt_application, and participant_settlement_item data consumed by statement generation.

Confidential. For internal use only.

Copyright © 2025, United Talent Agency. All rights reserved.