Billing Items Data Model

1. Executive Summary

The billing items data model represents the expected payments from buyers against deals. Each billing item captures the gross amount owed by a buyer for a client's services under a specific deal, and splits that amount into two detail lines: one for UTA's commission (REV) and one for the client's payout share (PAY). This REV/PAY split is the foundational concept that drives the entire downstream cash processing workflow -- from worksheet applications through settlement and outbound payment.

Billing items also track deductions (tax withholding, bank charges, discounts, etc.) that reduce the collectible amount, carry write-off status for receivables deemed uncollectable, and link to documents (invoices, credit memos, client letters) through a bridge table.

Scope

Tables covered in this document:

Table	Purpose
billing_item	Header record for a single expected payment from a buyer
billing_item_detail	REV/PAY split amounts; exactly two rows per billing item
billing_item_deduction	Deductions applied against a billing item detail
billing_item_document	Bridge table linking billing items to documents (invoices, credit memos, client letters)
revenue_items	Aggregated revenue expectation from a deal; parent of billing items
revenue_item_schedules	Periodic revenue recognition schedule entries

Not covered in this document:

• cash_receipt_application -- downstream cash application against billing item details (see Cash Receipts data model)
• cash_receipt_application_deduction -- deduction amounts applied at cash application time
• write_off_packet / packet_receivable -- write-off approval workflow (see Write-Offs data model)
• participant_settlement -- settlement of PAY amounts among a client's party
• payment_term -- deal engine payment term source data

Position in the Financial Flow

Deal (agreed gross revenue)
  |-- Revenue Item (aggregated revenue expectation)
       |-- Billing Item (specific payment expected from buyer)    <-- THIS DOCUMENT
            |-- Billing Item Detail: REV (UTA's commission)
            |-- Billing Item Detail: PAY (client's share)
            |-- Billing Item Deductions (tax, withholding, etc.)
            |-- Billing Item Documents (invoices, credit memos, etc.)
                 |
                 v
            Cash Receipt Worksheet (applies cash to REV and PAY details)
                 |-- Settlement (divides PAY among client's party)
                      |-- Outbound Payment (sent to bank)

IMPORTANT

Billing items are the receivables against which cash is applied. They are the bridge between the deal/revenue world (upstream) and the cash processing world (downstream). Every cash receipt application points to a billing_item_detail row.

---

2. Data Model

Entity Relationship Diagram

erDiagram
    DEAL ||--o{ REVENUE_ITEMS : "has"
    REVENUE_ITEMS ||--o{ BILLING_ITEM : "generates"
    REVENUE_ITEMS ||--o{ REVENUE_ITEM_SCHEDULES : "scheduled as"

    BILLING_ITEM ||--|| BILLING_ITEM_DETAIL_REV : "has exactly one"
    BILLING_ITEM ||--|| BILLING_ITEM_DETAIL_PAY : "has exactly one"
    BILLING_ITEM ||--o{ BILLING_ITEM_DOCUMENT : "may have"
    BILLING_ITEM_DETAIL_REV ||--o{ BILLING_ITEM_DEDUCTION : "may have"
    BILLING_ITEM_DETAIL_PAY ||--o{ BILLING_ITEM_DEDUCTION : "may have"

    BILLING_ITEM_DETAIL_REV ||--o{ CASH_RECEIPT_APPLICATION : "applied against"
    BILLING_ITEM_DETAIL_PAY ||--o{ CASH_RECEIPT_APPLICATION : "applied against"

    BILLING_ITEM_DETAIL_REV ||--o| WRITE_OFF_PACKET : "may be linked to"

    BILLING_ITEM }o--|| PARTY_CLIENT : "for client"
    BILLING_ITEM }o--|| PARTY_BUYER : "billed to"
    BILLING_ITEM }o--|| PARTY_CONTRACTED : "contracted with"
    BILLING_ITEM }o--|| PARTY_COLLECTION : "collected from"
    BILLING_ITEM }o--|| UTA_ENTITY : "billed by"
    BILLING_ITEM }o--|| DEPARTMENT : "belongs to"

    REVENUE_ITEMS }o--|| DEAL : "derived from"
    REVENUE_ITEMS }o--|| PARTY_CLIENT : "for client"
    REVENUE_ITEMS }o--|| PARTY_BUYER : "from buyer"
    REVENUE_ITEMS }o--|| UTA_ENTITY : "owned by"

    BILLING_ITEM {
        serial billing_item_id PK
        varchar billing_item_status_cd
        integer revenue_item_id FK
        varchar payment_term_ref
        integer deal_id FK
        integer uta_entity_id FK
        integer department_id FK
        integer client_id FK
        integer contracted_party_id FK
        integer buyer_id FK
        integer collection_party_id FK
        varchar collection_style_cd
        boolean collection_style_override_ind
        varchar currency_cd
        varchar service_country_cd
        date billing_item_due_dt
        date billing_item_aging_dt
        boolean current_item_ind
        boolean open_item_ind
    }

    BILLING_ITEM_DETAIL_REV {
        serial billing_item_detail_id PK
        integer billing_item_id FK
        varchar billing_item_detail_type_cd "REV"
        decimal billing_item_detail_gross_amt
        decimal billing_item_detail_percent
        decimal billing_item_detail_amt
        decimal billing_item_detail_tax_amt
        decimal billing_item_detail_total_amt
        varchar posting_status_cd
        varchar write_off_status_cd
    }

    BILLING_ITEM_DETAIL_PAY {
        serial billing_item_detail_id PK
        integer billing_item_id FK
        varchar billing_item_detail_type_cd "PAY"
        decimal billing_item_detail_gross_amt
        decimal billing_item_detail_percent
        decimal billing_item_detail_amt
        decimal billing_item_detail_tax_amt
        decimal billing_item_detail_total_amt
        varchar posting_status_cd
    }

    BILLING_ITEM_DEDUCTION {
        serial billing_item_deduction_id PK
        integer billing_item_detail_id FK
        varchar billing_item_deduction_type_cd
        boolean billing_item_deduction_update_net_ind
        decimal billing_item_deduction_amt
        varchar comment
    }

    BILLING_ITEM_DOCUMENT {
        serial billing_item_document_id PK
        integer billing_item_id FK
        varchar document_type_cd
        integer document_id
        varchar payment_term_ref
    }

    REVENUE_ITEMS {
        serial revenue_item_id PK
        integer uta_entity_id FK
        varchar sales_item_ref
        varchar revenue_item_status_cd
        integer deal_id FK
        integer client_id FK
        integer buyer_id FK
        decimal revenue_item_gross_amt
        decimal revenue_item_commission_perc
        decimal revenue_item_commission_amt
        varchar revenue_item_rec_style_cd
        boolean current_item_ind
    }

    REVENUE_ITEM_SCHEDULES {
        serial revenue_item_schedule_id PK
        integer revenue_item_id FK
        decimal revenue_amt
        date revenue_dt
        varchar revenue_item_posting_status_cd
    }

NOTE

The ER diagram shows BILLING_ITEM_DETAIL_REV and BILLING_ITEM_DETAIL_PAY as separate entities for clarity. In the physical database, both are rows in the single billing_item_detail table differentiated by billing_item_detail_type_cd.

---

billing_item

The header record representing a single expected payment from a buyer. Each billing item is associated with a revenue item and a deal, and carries the context needed to determine collection responsibility, currency, tax jurisdiction, and aging.

Field	Type	Required	Default	Description
billing_item_id	serial	Yes	Auto	Primary key.
billing_item_status_cd	varchar(50)	No	--	Current lifecycle status. Values: U (Unbilled), B (Billed), X (Skipped), C (Cancelled). Code master type: BILLING_ITEM_STATUS_CD.
revenue_item_id	integer	No	--	FK to revenue_items. Links this billing item to its parent revenue expectation.
payment_term_ref	varchar(100)	No	--	Reference identifier from the deal engine's payment term. Used to correlate billing items with their source payment term during sync operations.
deal_id	integer	No	--	FK to deal. The deal under which this billing item was generated.
uta_entity_id	integer	No	--	FK to uta_entity. The UTA legal entity responsible for billing. Determines billing entity for tax jurisdiction mapping (e.g., UK Agency, US entity).
department_id	integer	No	--	FK to department. The UTA department associated with this billing item.
client_id	integer	No	--	FK to party (Client type). The talent/artist whose services generated this receivable.
contracted_party_id	integer	No	--	FK to party (Contracted type). The party contractually bound in the deal. May differ from client_id for loan-out corporations or legal entities.
buyer_id	integer	No	--	FK to party (Buyer type). The entity expected to pay (studio, brand, network, etc.).
agent_group_id	integer	No	--	Identifier for the agent group associated with this billing item.
collection_party_id	integer	No	--	FK to party. The party from whom payment will be collected. Determined by collection_style_cd unless overridden.
collection_style_cd	varchar(50)	No	--	How payment is collected. C = Client (UTA collects from client), B = Buyer (UTA collects from buyer). Code master type: COLLECTION_STYLE_CD.
collection_style_override_ind	boolean	No	--	When true, the collection style was manually overridden by a user and should not be recalculated during sync.
billing_item_name	varchar(500)	No	--	Descriptive name for the billing item, typically inherited from the payment term.
currency_cd	varchar(50)	No	--	Currency of the billing item amounts. Code master type: CURRENCY_CD.
service_country_cd	varchar(2)	No	--	ISO 3166-1 alpha-2 country code where the service was performed. Used for tax jurisdiction determination.
service_state_cd	varchar(10)	No	--	State or province code for sub-national tax jurisdiction.
vat_pass_through_rate	decimal(7,4)	No	--	VAT rate applied as pass-through (Artist Fee VAT -- buyer pays on top of gross, passes through to artist).
vat_pass_through_amt	decimal(15,2)	No	--	Calculated VAT pass-through amount.
expected_invoice_total	decimal(15,2)	No	--	Expected total invoice amount including VAT pass-through.
tax_calculated_dt	timestamp	No	--	Timestamp when tax was last calculated for this billing item.
tax_stale_ind	boolean	No	false	When true, indicates that underlying tax data has changed and the tax estimate should be recalculated.
billing_item_due_dt	date	No	--	Date when the payment is due from the buyer.
billing_item_due_dt_status_cd	varchar(50)	No	--	Whether the due date is confirmed. U = Unconfirmed, C = Confirmed. Code master type: BILLING_ITEM_DATE_STATUS_CD.
billing_item_aging_dt	date	No	--	Date used for aging calculations. Defaults to billing_item_due_dt unless manually adjusted by a user.
current_item_ind	boolean	No	--	When true, this is the active version of the billing item. Set to false when a billing item is superseded by a newer version (rebilling) or deactivated.
open_item_ind	boolean	No	--	When true, the billing item has outstanding balance (not fully paid). Calculated as: false when both REV and PAY detail total amounts have been fully applied within tolerance (epsilon = 0.01).

IMPORTANT

The current_item_ind and open_item_ind flags work together to determine visibility in receivable searches. Only rows with current_item_ind = true represent active billing items. The open_item_ind flag is recalculated automatically whenever cash applications change.

NOTE

The collection_style_cd field directly impacts PAY amounts. When collection style is CLIENT (meaning UTA collects only commission from the client directly), PAY detail amounts are set to zero because UTA is not handling the client's payout -- the buyer pays the client directly.

---

billing_item_detail

Each billing item has exactly two detail records: one with billing_item_detail_type_cd = 'REV' (UTA's commission) and one with 'PAY' (the client's share). These are the rows against which cash is applied on worksheets.

Field	Type	Required	Default	Description
billing_item_detail_id	serial	Yes	Auto	Primary key. Referenced by cash_receipt_application.billing_item_detail_id for cash applications.
billing_item_id	integer	No	--	FK to billing_item. Parent billing item header.
billing_item_detail_type_cd	varchar(50)	No	--	REV (Commission/Revenue) or PAY (Payout). Code master type: BILLING_ITEM_DETAIL_TYPE_CD.
billing_item_detail_gross_amt	decimal(15,2)	No	--	The gross amount to be collected for this detail line. For REV: equals the deal gross. For PAY: equals the deal gross (unless collection style is CLIENT, in which case it is zero).
billing_item_detail_percent	decimal(5,4)	No	--	The percentage applied to gross to compute the detail amount. For REV: the commission rate. For PAY: 1 - commission rate (or 0 if collection style is CLIENT).
billing_item_detail_amt	decimal(15,2)	No	--	Calculated: billing_item_detail_gross_amt * billing_item_detail_percent. This is the base receivable amount before tax.
billing_item_detail_tax_amt	decimal(15,2)	No	--	Tax amount computed from billing_item_detail_amt. Initially zero; populated when tax calculations run.
billing_item_detail_total_amt	decimal(15,2)	No	--	Total collectible amount: billing_item_detail_amt + billing_item_detail_tax_amt. This is the target amount for cash application.
posting_status_cd	varchar(50)	No	--	GL posting status. U = Unposted, P = Posted, X = Skipped. Code master type: POSTING_STATUS_CD.
posting_dt	date	No	--	Date when the detail was posted to the general ledger.
write_off_packet_id	uuid	No	--	FK to write_off_packet. Links to the write-off approval packet when this detail has been written off. Null if not written off.
write_off_status_cd	varchar(50)	No	'NOT_WRITTEN_OFF'	Write-off lifecycle status. Values: NOT_WRITTEN_OFF, WRITTEN_OFF, RECOVERED.
write_off_dt	timestamp	No	--	Date when the detail was written off.
recovered_dt	timestamp	No	--	Date when a previously written-off detail was recovered.
exclude_from_cecl_ind	boolean	No	false	When true, excludes this detail from CECL (Current Expected Credit Losses) regulatory reporting. Set to true when written off.

IMPORTANT

The formula billing_item_detail_amt = billing_item_detail_gross_amt * billing_item_detail_percent is enforced at creation time. Deductions do NOT affect billing_item_detail_amt -- they are tracked separately in billing_item_deduction and applied at the cash application level.

WARNING

Only REV-type details can be written off. PAY details remain unchanged when a write-off occurs -- the company absorbs the loss. This is enforced at the application level, not by a database constraint.

---

billing_item_deduction

Deductions reduce the effective collectible amount for a billing item detail. Multiple deductions can be applied to a single detail. Deductions are tracked separately from the base amounts and are applied at the cash receipt application level.

Field	Type	Required	Default	Description
billing_item_deduction_id	serial	Yes	Auto	Primary key.
billing_item_detail_id	integer	No	--	FK to billing_item_detail. The detail line this deduction applies to.
billing_item_deduction_type_cd	varchar(50)	No	--	Deduction category. Code master type: BILLING_ITEM_DEDUCTION_TYPE_CD. See Code Master Values section for full list.
billing_item_deduction_update_net_ind	boolean	No	true	When true, this deduction affects the net collectible amount calculation. When false, the deduction is informational only.
billing_item_deduction_amt	decimal(15,2)	No	--	The deduction amount. Positive values reduce the collectible amount.
comment	varchar(500)	No	--	Free-text comment describing the deduction rationale.

NOTE

Deductions have a balance concept tracked at the DTO/presentation layer (not in the database). The deduction_applied_amt is the sum of cash_receipt_application_deduction.deduction_amt_applied records linked through applications, and deduction_balance = billing_item_deduction_amt - deduction_applied_amt. This allows partial application of deductions across multiple worksheets.

---

billing_item_document

Bridge table linking billing items to documents (invoices, credit memos, client letters, etc.). The document_type_cd discriminates which document table document_id references. Each billing item can have multiple documents of different types.

Field	Type	Required	Default	Description
billing_item_document_id	serial	Yes	Auto	Primary key.
billing_item_id	integer	Yes	--	FK to billing_item. The parent billing item.
document_type_cd	varchar(50)	Yes	--	Document type discriminator. Values: CI (Commission Invoice), BI (Buyer Invoice), CM (Credit Memo), CL (Client Letter). Code master type: DOCUMENT_TYPE_CD.
document_id	integer	Yes	--	Polymorphic FK to the document table implied by document_type_cd (e.g., invoice.invoice_id for CI/BI).
payment_term_ref	varchar(100)	No	--	Reference to the payment term from the deal engine. Used for correlation during document generation.

NOTE

The document_type_cd implies which billing_item_detail type is relevant: CI (Commission Invoice) corresponds to the REV detail, and BI (Buyer Invoice) corresponds to the PAY detail. The document_id is a polymorphic foreign key -- referential integrity is enforced at the application level, not by a database constraint.

---

revenue_items

The aggregated revenue expectation from a deal. A revenue item represents the total expected revenue from a specific sales engagement and serves as the parent container for one or more billing items.

Field	Type	Required	Default	Description
revenue_item_id	serial	Yes	Auto	Primary key. Referenced by billing_item.revenue_item_id.
uta_entity_id	integer	No	--	FK to uta_entity. The UTA legal entity.
agent_group_id	integer	No	--	Identifier for the associated agent group.
sales_item_ref	varchar(255)	Yes	--	External reference to the sales item in the deal engine. Used for correlation during sync. Indexed for search performance.
revenue_item_name	varchar(500)	No	--	Descriptive name for the revenue item.
revenue_item_status_cd	varchar(1)	Yes	--	Lifecycle status. U = Unconfirmed, C = Confirmed, M = Missed. Code master type: REVENUE_ITEM_STATUS_CD.
revenue_item_date_status_cd	varchar(1)	Yes	--	Whether dates are confirmed. U = Unconfirmed, C = Confirmed. Code master type: REVENUE_ITEM_DATE_STATUS_CD.
deal_id	integer	No	--	FK to deal. The parent deal.
client_id	integer	No	--	FK to party (Client type).
contracted_party_id	integer	No	--	FK to party (Contracted type).
buyer_id	integer	No	--	FK to party (Buyer type).
department_id	integer	No	--	FK to department.
currency_cd	varchar(3)	No	--	Currency of the revenue amounts.
revenue_item_gross_amt	decimal(19,2)	No	--	Total gross revenue expected from the deal.
revenue_item_commission_flat_ind	boolean	No	--	When true, the commission is a flat dollar amount rather than a percentage of gross.
revenue_item_commission_perc	decimal(5,4)	No	--	Commission rate as a decimal (e.g., 0.1000 = 10%). Used when revenue_item_commission_flat_ind = false.
revenue_item_commission_amt	decimal(19,2)	No	--	The commission amount (UTA's revenue). Either calculated from gross * perc or the flat amount.
revenue_item_start_dt	date	No	--	Start date of the revenue period.
revenue_item_end_dt	date	No	--	End date of the revenue period.
revenue_item_rec_style_cd	varchar(1)	No	--	Revenue recognition style. I = Immediate, M = Monthly, C = Cash. Code master type: REVENUE_ITEM_REC_STYLE_CD.
current_item_ind	boolean	No	true	When true, this is the active version of the revenue item.

IMPORTANT

The integrity rule for REV details across billing items is: for all billing_item_detail records of type REV belonging to billing items under a given revenue item, the sum of billing_item_detail_gross_amt must equal revenue_item_gross_amt, and the sum of billing_item_detail_amt must equal revenue_item_commission_amt.

NOTE

Revenue items use higher decimal precision (decimal(19,2)) than billing items (decimal(15,2)) because revenue items represent aggregate deal-level amounts that may be larger.

---

revenue_item_schedules

Periodic entries that break a revenue item's total into scheduled recognition periods. Each schedule row represents a specific date and amount for revenue recognition posting.

Field	Type	Required	Default	Description
revenue_item_schedule_id	serial	Yes	Auto	Primary key.
revenue_item_id	integer	No	--	FK to revenue_items. Parent revenue item.
revenue_amt	decimal(19,2)	No	--	Revenue amount for this schedule period.
revenue_dt	date	No	--	Date of this revenue recognition period.
revenue_item_posting_status_cd	varchar(1)	No	--	GL posting status. P = Posted, U = Unposted. Code master type: POSTING_STATUS_CD.
revenue_item_posting_dt	date	No	--	Date when this schedule entry was posted to the general ledger.

---

3. Status Lifecycle

billing_item.billing_item_status_cd

Status	Code	Description	Allowed Transitions
Unbilled	U	Billing item has been created but not yet billed to the buyer. Initial state for all new billing items.	B, X, C
Billed	B	Billing item has been sent to the buyer for payment.	C
Skipped	X	Billing item was intentionally skipped and will not be billed.	-- (terminal)
Cancelled	C	Billing item has been cancelled.	-- (terminal)

stateDiagram-v2
    [*] --> U : Billing item created
    U --> B : Billed to buyer
    U --> X : Skipped
    U --> C : Cancelled
    B --> C : Cancelled after billing

    state "U (Unbilled)" as U
    state "B (Billed)" as B
    state "X (Skipped)" as X
    state "C (Cancelled)" as C

Transition details:

From	To	Trigger	Preconditions	Side Effects
--	U	Billing item created from revenue item sync	Revenue item exists with payment terms	REV and PAY details created; current_item_ind = true; open_item_ind = true
U	B	Manual status update or billing process	--	--
U	X	Skipped during billing	--	Used as reversal status when original was U
U	C	Cancellation	--	--
B	C	Cancellation after billing	--	--

NOTE

During rebilling (when a revenue item is updated from the deal engine), the original billing item's status is flipped as part of the reversal: if the original was U, the reversal copy is set to X; if the original was anything else, the reversal copy is set to U. The original is then deactivated (current_item_ind = false).

---

billing_item_detail.write_off_status_cd

Status	Code	Description	Allowed Transitions
Not Written Off	NOT_WRITTEN_OFF	Normal state. Detail is active and collectible.	WRITTEN_OFF
Written Off	WRITTEN_OFF	Detail has been deemed uncollectable. Excluded from CECL reporting.	RECOVERED
Recovered	RECOVERED	A previously written-off detail has been recovered.	-- (terminal)

stateDiagram-v2
    [*] --> NOT_WRITTEN_OFF : Detail created
    NOT_WRITTEN_OFF --> WRITTEN_OFF : Write-off packet approved
    WRITTEN_OFF --> RECOVERED : Recovery processed

    state "NOT_WRITTEN_OFF" as NOT_WRITTEN_OFF
    state "WRITTEN_OFF" as WRITTEN_OFF
    state "RECOVERED" as RECOVERED

Transition details:

From	To	Trigger	Preconditions	Side Effects
--	NOT_WRITTEN_OFF	Detail created	--	Default state
NOT_WRITTEN_OFF	WRITTEN_OFF	Write-off packet reaches final approval	Detail type must be REV; write-off packet fully approved	write_off_dt set; write_off_packet_id linked; exclude_from_cecl_ind = true
WRITTEN_OFF	RECOVERED	Recovery processed	Detail was previously written off	recovered_dt set; exclude_from_cecl_ind may be reset

WARNING

Only REV-type details can transition through write-off states. PAY details always remain NOT_WRITTEN_OFF. The company absorbs the loss on the commission side, and the client's payout obligation is unaffected by a write-off.

---

billing_item_detail.posting_status_cd

Status	Code	Description	Allowed Transitions
Unposted	U	Detail has not been posted to the general ledger.	P, X
Posted	P	Detail has been posted to the general ledger.	-- (terminal)
Skipped	X	Detail was skipped for GL posting.	-- (terminal)

---

revenue_items.revenue_item_status_cd

Status	Code	Description	Allowed Transitions
Unconfirmed	U	Revenue item exists but amounts/dates are not yet confirmed.	C, M
Confirmed	C	Revenue item amounts and dates are confirmed.	-- (terminal)
Missed	M	Revenue was expected but not realized.	-- (terminal)

stateDiagram-v2
    [*] --> U : Revenue item created
    U --> C : Confirmed
    U --> M : Marked as missed

    state "U (Unconfirmed)" as U
    state "C (Confirmed)" as C
    state "M (Missed)" as M

NOTE

The Final code attribute on REVENUE_ITEM_STATUS_CD indicates whether the status represents a terminal state. C (Confirmed) and M (Missed) are both final; U (Unconfirmed) is not.

---

revenue_item_schedules.revenue_item_posting_status_cd

Status	Code	Description	Allowed Transitions
Unposted	U	Schedule entry has not been posted.	P
Posted	P	Schedule entry has been posted to GL.	-- (terminal)

---

4. Validation & Database Constraints

Unique Constraints

Table	Constraint	Columns	Business Rule
billing_item_document	uq_billing_item_document_item_type_doc	billing_item_id, document_type_cd, document_id	Prevents duplicate document links -- a specific document of a given type can only be linked to a billing item once.

Database Indexes

Table	Index	Columns	Purpose
billing_item_detail	billing_item_detail_billing_item_idx	billing_item_id	Fast lookup of details by parent billing item.
billing_item_detail	billing_item_detail_type_idx	billing_item_detail_type_cd	Filter by REV/PAY type.
billing_item_detail	billing_item_detail_item_type_idx	billing_item_id, billing_item_detail_type_cd	Composite index for common query pattern (get REV/PAY details for a billing item).
billing_item_document	billing_item_document_billing_item_idx	billing_item_id	Fast lookup of documents by parent billing item.
billing_item_document	billing_item_document_doc_type_doc_id_idx	document_type_cd, document_id	Fast lookup of billing items by document reference.
revenue_items	revenue_items_sales_item_ref_idx	sales_item_ref	Fast lookup by external reference during sync.
revenue_items	revenue_items_current_item_idx	current_item_ind	Filter for active revenue items.

Required Fields (Database-Level NOT NULL)

Table	Required Fields
revenue_items	sales_item_ref, revenue_item_status_cd, revenue_item_date_status_cd
billing_item_document	billing_item_id, document_type_cd, document_id
All others	No database-level NOT NULL constraints beyond auto-generated PKs. Application-level validation enforces required fields at creation time.

Structural Invariants

Exactly Two Details Per Billing Item. Every billing_item must have exactly two billing_item_detail rows: one with billing_item_detail_type_cd = 'REV' and one with 'PAY'. This is an application-level invariant enforced by the service layer during creation, not by a database constraint. All creation paths produce both details atomically within a transaction. Querying code should defensively handle the case where a detail is missing.

Revenue Item Integrity. For all current (current_item_ind = true) billing_item_detail rows of type REV belonging to billing items under a given revenue_item_id: the sum of billing_item_detail_gross_amt must equal revenue_items.revenue_item_gross_amt, and the sum of billing_item_detail_amt must equal revenue_items.revenue_item_commission_amt. This invariant is maintained during revenue sync operations (create, update, rebill).

Calculated Field Rules

Formula	Description
billing_item_detail_amt = billing_item_detail_gross_amt * billing_item_detail_percent	Base receivable amount.
billing_item_detail_total_amt = billing_item_detail_amt + billing_item_detail_tax_amt	Total collectible amount.
open_item_ind = NOT (REV fully applied AND PAY fully applied)	Open item flag (tolerance: 0.01).

IMPORTANT

"Fully applied" means the absolute difference between cash applied and billing_item_detail_total_amt is less than 0.01 (epsilon). Cash applied is the sum of cash_receipt_application.cash_receipt_amt_applied for the given billing_item_detail_id.

Collection Style Impact on PAY

When collection_style_cd = 'C' (Client):

• PAY detail: gross_amt = 0, percent = 0, amt = 0, tax_amt = 0, total_amt = 0

When collection_style_cd = 'B' (Buyer):

• PAY detail: gross_amt = deal gross, percent = 1 - commission rate, amt = gross * (1 - commission rate)

IMPORTANT

The default collection style is derived from the payment term's payment party: if the payment party matches the buyer, style is BUYER; otherwise, style is CLIENT. This default can be manually overridden (collection_style_override_ind = true), in which case the override survives revenue sync operations.

---

5. Code Master Values

BILLING_ITEM_STATUS_CD

Code	Description	Behavior / When Used
U	Unbilled	Default status for newly created billing items. Indicates the item has not been billed to the buyer.
B	Billed	Item has been sent to the buyer. Typically set after an invoice or billing run.
X	Skipped	Item intentionally not billed. Also used as the status for reversal copies when the original was U.
C	Cancelled	Item cancelled. Terminal state.

No code attributes defined.

---

BILLING_ITEM_DETAIL_TYPE_CD

Code	Description	Behavior / When Used
REV	Commission	UTA's revenue/commission portion. Always created with commission rate applied to gross. Subject to write-off. GL posts at worksheet Apply (D to P).
PAY	Payout	Client's share for outbound payment. Created with 1 - commission rate applied to gross (or zero if collection style is CLIENT). Cannot be written off. GL posts only after bank confirms payment.

No code attributes defined.

---

BILLING_ITEM_DEDUCTION_TYPE_CD

Code	Description	Behavior / When Used
T	Tax	General tax deduction.
W	Withholding	General withholding deduction.
B	Bank Charge	Bank fees or charges deducted from the collectible amount.
D	Discount	Discount applied to the billing item.
R	Reimbursement	Reimbursement adjustment.
C	Client Request	Deduction requested by the client.
DP	Direct Payment	Direct payment deduction (e.g., buyer paid client directly for a portion).
WH_US_NRA	US NRA Withholding (30%)	US non-resident alien withholding tax. Applied to PAY details when the contracted party is a non-resident alien without a Central Withholding Agreement (CWA).
WH_UK_FEU	UK FEU Withholding (20%)	UK Foreign Entertainers Unit withholding. Applied when UK tax year cumulative earnings (actual or expected) exceed the personal allowance threshold.
VAT_ARTIST	VAT on Artist Fee (20%)	UK VAT on the artist fee component. Applied when the billing entity is UK Agency, the show is in the UK, the client is UK-based, and is VAT registered.
VAT_COMM	VAT on Commission (20%)	UK VAT on the commission component. Applied when the billing entity is UK Agency and the client is UK-based.

No code attributes defined.

NOTE

International tax deduction types (WH_US_NRA, WH_UK_FEU, VAT_ARTIST, VAT_COMM) are auto-calculated by the tax engine at billing item creation time and stored as estimates. The worksheet recalculates authoritatively at load time, so billing-item-level tax deductions are informational until applied.

---

DOCUMENT_TYPE_CD

Code	Description	Behavior / When Used
CI	Commission Invoice	Invoice for UTA's commission (REV billing item detail). Generated during billing runs.
BI	Buyer Invoice	Invoice sent to the buyer for the full amount (PAY billing item detail). Generated during billing runs.
CM	Credit Memo	Credit memo issued against a billing item. Used for adjustments or corrections.
CL	Client Letter	Letter sent to the client related to the billing item.

No code attributes defined.

---

COLLECTION_STYLE_CD

Code	Description	Behavior / When Used
C	Client	UTA collects only its commission from the client. PAY detail amounts are set to zero (buyer pays client directly).
B	Buyer	UTA collects the full amount from the buyer, then pays out the client's share. PAY detail amounts are calculated normally.

No code attributes defined.

---

BILLING_ITEM_DATE_STATUS_CD

Code	Description	Behavior / When Used
U	Unconfirmed	Due date is estimated or not yet confirmed. Default for new billing items.
C	Confirmed	Due date has been confirmed.

No code attributes defined.

---

POSTING_STATUS_CD

Used by billing_item_detail.posting_status_cd and revenue_item_schedules.revenue_item_posting_status_cd.

Code	Description	Behavior / When Used
U	Unposted	Entry has not been posted to the general ledger.
P	Posted	Entry has been posted to the general ledger. posting_dt is set.
X	Skipped	Entry was intentionally skipped for GL posting.

No code attributes defined.

---

REVENUE_ITEM_STATUS_CD

Code	Description	Behavior / When Used
U	Unconfirmed	Revenue amounts and dates are not yet confirmed. Active, editable state. Code attribute: Final = F.
C	Confirmed	Revenue is confirmed. Terminal state. Code attribute: Final = T.
M	Missed	Revenue was expected but not realized. Terminal state. Code attribute: Final = T.

---

REVENUE_ITEM_DATE_STATUS_CD

Code	Description	Behavior / When Used
U	Unconfirmed	Revenue dates are estimated.
C	Confirmed	Revenue dates are confirmed.

No code attributes defined.

---

REVENUE_ITEM_REC_STYLE_CD

Code	Description	Behavior / When Used
I	Immediate	Revenue recognized immediately when earned.
M	Monthly	Revenue recognized on a monthly schedule. Generates revenue_item_schedules entries.
C	Cash	Revenue recognized when cash is received.

No code attributes defined.

---

6. Cross-References

Document	Relationship
Cash Receipts	cash_receipt_application.billing_item_detail_id references billing_item_detail. Cash applications are the downstream consumer of billing item detail receivables.
Settlements	Settlements divide PAY applications among deal parties. participant_settlement links back through cash_receipt_application to PAY billing item details.
Settlements	Payment items are created from settlement payouts. The payment lifecycle determines whether PAY applications and their billing item details can be reversed.
Write-Offs	billing_item_detail.write_off_packet_id references write_off_packet. Write-off packets contain packet_receivable rows that reference billing_item_detail_id.
Deals, Sales Items & Payment Terms	billing_item.deal_id, client_id, buyer_id, contracted_party_id, collection_party_id reference deal and party tables. revenue_items.deal_id links revenue to deals.

Key Integration Points

Integration	Direction	Description
Revenue Sync	Inbound	Revenue items and billing items are created/updated when deal engine payment terms change. The sync process handles: new term creation, amount updates, rebilling with reversal/replacement, and deactivation of removed terms.
Cash Worksheet	Outbound	Billing item details are searched and added to cash receipt worksheets. The billing_item_detail_id is the FK on cash_receipt_application.
Tax Calculation	Bidirectional	Tax deductions are estimated at billing item creation and recalculated authoritatively at worksheet load time. billing_item.tax_calculated_dt and tax_stale_ind track staleness.
AR Aging	Read	AR aging reports query billing items by billing_item_aging_dt, current_item_ind, and open_item_ind to produce aging buckets.
Write-Off	Outbound	REV billing item details are linked to write-off packets via write_off_packet_id. Write-off approval updates write_off_status_cd.
Document Generation	Outbound	Billing items are linked to generated documents (invoices, credit memos, client letters) via the billing_item_document bridge table.