Accounting Queries

1. Executive Summary

This document catalogs every query operation in the accounting domain — the reads, searches, aggregations, and writes that the application executes against the transaction, account, fiscal_period, and accounting_job_execution_history tables. Each operation is described in a technology-neutral format: what parameters it accepts, which tables and joins it touches, what filters it applies, what values it computes, and what shape it returns.

The queries fall into four functional groups:

Group	Purpose	Key Operations
Transaction search & reporting	Power the Accounting Jobs Dashboard search panel and its four tab views (Top Level, Rev Level, Client Level, Transaction Detail).	`searchTransactions`, `searchTransactionsEnriched`, `getAggregatedTransactions`, `getRevLevelAggregatedTransactions`, `getRevenueLevelAggregatedTransactions`
Transaction lifecycle (writes)	Create, batch-insert, and delete transactions as part of accounting job execution.	`createTransaction`, `createTransactionsBatch`, `deleteBySource`, `deleteByBatch`, `deleteBySourceAndType`, `deleteBySourceAndRefDate`, `deleteBySourceAndPostingDateBefore`, `deleteBySourceAndPostingDateOnOrAfter`, `deleteBySourceAndPeriodFrom`
True-up support	Identify revenue references that need AR true-up adjustments and calculate their current balances.	`getTrueUpCandidates`, `getBalancesForRef`, `getTransactionContextForRef`, `getParentRefBalances`
Account, fiscal period, and job history	Manage the chart of accounts, control the active fiscal period, and record/query job execution history.	`getAllAccounts`, `searchAccounts`, `getCurrentPeriod`, `getPeriodByDate`, `setCurrentPeriod`, `getAllPeriods`, `createJobExecution`, `getLastExecutionByJob`, `getLastExecutions`

For the data model definitions of all referenced tables, see Accounting Data Model.

2. Key Queries

2.1 Transaction Search & Reporting

2.1.1 Search Transactions (Raw)

Operation: searchTransactions

Input Parameters:

classCd: string or string[] (optional) — transaction.class_cd; when an array, matches any value in the list
sourceCd: string or string[] (optional) — transaction.source_cd; when an array, matches any value in the list
sourceRef: string (optional) — transaction.source_ref; case-insensitive substring match
parentRevenueRef: string (optional) — transaction.rev_ref; case-insensitive substring match
accountId: integer (optional) — transaction.account_id; exact match
clientId: integer (optional) — transaction.client_id; exact match
utaEntityId: integer or integer[] (optional) — transaction.uta_entity_id; when an array, matches any value in the list
departmentId: integer (optional) — transaction.department_id; exact match
batchId: string (optional) — transaction.batch_id; case-insensitive substring match
periodRefFrom: string (optional) — transaction.posting_period_ref; lower bound (inclusive, >= comparison)
periodRefTo: string (optional) — transaction.posting_period_ref; upper bound (inclusive, <= comparison)
postingDtFrom: date (optional) — transaction.posting_dt; lower bound (inclusive) after PST conversion
postingDtTo: date (optional) — transaction.posting_dt; upper bound (inclusive) after PST conversion

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	No joins; all filters apply directly to `transaction` columns

Filters:

All provided parameters are combined with AND logic
Only non-null, non-empty parameters are applied
postingDtFrom / postingDtTo: date values converted to PST date strings before comparison
periodRefFrom / periodRefTo: compared against the denormalized transaction.posting_period_ref column

Computed Values:

None

Returns: Array of raw transaction rows matching all provided filters, ordered by transaction.transaction_id ascending, limited to 1,000 rows.

2.1.2 Search Transactions Enriched

Operation: searchTransactionsEnriched

Input Parameters:

All parameters from searchTransactions (section 2.1.1), plus:

accountClass: string (optional) — account.account_class; exact match
accountNumber: string (optional) — account.account_number; case-insensitive substring match

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	LEFT	`party` (as `client_party`)	`transaction.client_id = client_party.party_id`	Resolve client display name
`transaction`	LEFT	`department`	`transaction.department_id = department.department_id`	Resolve department name
`transaction`	LEFT	`account`	`transaction.account_id = account.account_id`	Resolve account name, class, and number; enable account-level filtering
`transaction`	LEFT	`uta_entity`	`transaction.uta_entity_id = uta_entity.uta_entity_id`	Resolve UTA entity name
`transaction`	LEFT	`fiscal_period`	`transaction.posting_period_id = fiscal_period.fiscal_period_id`	Resolve period reference; apply period-ref range filters against the joined table

Filters:

Same AND-combined logic as searchTransactions
periodRefFrom / periodRefTo filter against fiscal_period.period_ref (joined table) rather than the denormalized transaction.posting_period_ref
accountClass: filters against account.account_class (exact match)
accountNumber: filters against account.account_number (case-insensitive substring match)

Computed Values:

None; display names are resolved through the joins, not computed

Returns: Array of enriched transaction rows ordered by transaction.transaction_id ascending, limited to 1,000 rows; each row includes all transaction columns plus client_name (from party.display_name), department_name, account_name (from account.account_full_name), uta_entity_name, parent_revenue_ref (from transaction.rev_ref), period_ref, account_class, and account_number.

2.1.3 Get Aggregated Transactions

Operation: getAggregatedTransactions

Input Parameters:

Same filter parameters as searchTransactions (section 2.1.1); periodRefFrom / periodRefTo filters apply against the joined fiscal_period.period_ref.

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	LEFT	`account`	`transaction.account_id = account.account_id`	Resolve account class and number for grouping
`transaction`	LEFT	`fiscal_period`	`transaction.posting_period_id = fiscal_period.fiscal_period_id`	Resolve period reference for grouping and range filtering

Filters:

Same AND-combined logic as searchTransactions
periodRefFrom / periodRefTo applied against fiscal_period.period_ref

Computed Values:

total_amount: CAST(SUM(transaction.trans_amt) AS FLOAT) — grouped sum of transaction amounts

Returns: Array of aggregation rows grouped by fiscal_period.period_ref, transaction.source_cd, account.account_class, and account.account_number; each row includes period_ref (defaulting to 'Unknown' when null), source_cd, account_class (defaulting to 'Unknown'), account_number (defaulting to 'Unknown'), and total_amount (defaulting to 0); no row limit.

2.1.4 Get Rev Level Aggregated Transactions

Operation: getRevLevelAggregatedTransactions

Input Parameters:

Same filter parameters as searchTransactions (section 2.1.1) with the following matching differences:

parentRevenueRef: exact match (not substring) in this variant
sourceRef: exact match (not substring) in this variant
batchId: exact match (not substring) in this variant

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	LEFT	`account`	`transaction.account_id = account.account_id`	Resolve account class and number for grouping
`transaction`	LEFT	`fiscal_period`	`transaction.posting_period_id = fiscal_period.fiscal_period_id`	Resolve period reference for grouping and range filtering
`transaction`	LEFT	`party` (as `client_party`)	`transaction.client_id = client_party.party_id`	Resolve client display name for grouping

Filters:

Same AND-combined logic as searchTransactions
parentRevenueRef, sourceRef, and batchId use exact equality (not ILIKE) in this variant

Computed Values:

total_amount: CAST(SUM(transaction.trans_amt) AS FLOAT) — grouped sum of transaction amounts

Returns: Array of aggregation rows grouped by fiscal_period.period_ref, transaction.source_cd, party.display_name, transaction.rev_ref, account.account_class, and account.account_number; each row includes period_ref, source_cd, client_name, parent_revenue_ref, account_class, account_number, and total_amount, all defaulting to 'Unknown' / '' / 0 when null; no row limit.

2.1.5 Get Revenue Level Aggregated Transactions

Operation: getRevenueLevelAggregatedTransactions

Input Parameters:

Same filter parameters as searchTransactions (section 2.1.1); all string filters use case-insensitive substring matching.

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	LEFT	`account`	`transaction.account_id = account.account_id`	Resolve account class and number for grouping
`transaction`	LEFT	`fiscal_period`	`transaction.posting_period_id = fiscal_period.fiscal_period_id`	Enable period-ref range filtering against joined table

Filters:

Same AND-combined logic as searchTransactions

Computed Values:

total_amount: CAST(SUM(transaction.trans_amt) AS FLOAT) — grouped sum of transaction amounts

Returns: Array of aggregation rows grouped by transaction.rev_ref, account.account_class, and account.account_number; each row includes parent_revenue_ref, account_class (defaulting to 'Unknown'), account_number (defaulting to 'Unknown'), and total_amount (defaulting to 0); no row limit.

2.2 Transaction Lifecycle (Writes)

2.2.1 Create Transaction

Operation: createTransaction

Input Parameters:

data: object (required) — a complete transaction record excluding transaction_id (auto-generated); all fields from the Accounting Data Model transaction table

Tables & Joins:

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

Filters:

None

Computed Values:

None

Returns: The single newly created transaction row, including the auto-generated transaction.transaction_id.

2.2.2 Create Transactions Batch

Operation: createTransactionsBatch

Input Parameters:

transactions: transaction[] (required) — array of complete transaction records excluding transaction_id; empty arrays are short-circuited and produce no database call

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	Batch INSERT target; no joins

Filters:

None

Computed Values:

None

Returns: Array of newly created transaction rows, each including its auto-generated transaction.transaction_id.

NOTE

PoC Artifact: The service layer chunks inserts into batches of 1,000 rows to avoid stack overflow on large datasets. An optional database transaction context is accepted for use within larger atomic operations.

2.2.3 Delete By Source

Operation: deleteBySource

Input Parameters:

sourceCd: string (required) — transaction.source_cd; the source job code (e.g., REV, BILL)
sourceId: integer (required) — transaction.source_id; the source record identifier

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	DELETE target; no joins

Filters:

transaction.source_cd = :sourceCd
transaction.source_id = :sourceId
Combined with AND

Computed Values:

None

Returns: Nothing (void).

2.2.4 Delete By Batch

Operation: deleteByBatch

Input Parameters:

batchId: string (required) — transaction.batch_id; the batch identifier

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	DELETE target; no joins

Filters:

transaction.batch_id = :batchId

Computed Values:

None

Returns: Nothing (void).

2.2.5 Delete By Source And Type

Operation: deleteBySourceAndType

Input Parameters:

sourceCd: string (required) — transaction.source_cd
sourceId: integer (required) — transaction.source_id
typeCd: string (required) — transaction.type_cd; D (debit) or C (credit)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	DELETE target; no joins

Filters:

transaction.source_cd = :sourceCd
transaction.source_id = :sourceId
transaction.type_cd = :typeCd
Combined with AND

Computed Values:

None

Returns: Nothing (void).

2.2.6 Delete By Source And Ref Date

Operation: deleteBySourceAndRefDate

Input Parameters:

sourceCd: string (required) — transaction.source_cd
sourceId: integer (required) — transaction.source_id
refDate: date (required) — transaction.transaction_ref_dt; the transaction reference date, converted to a PST date string before comparison

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	DELETE target; no joins

Filters:

transaction.source_cd = :sourceCd
transaction.source_id = :sourceId
transaction.transaction_ref_dt = :refDate (after PST date string conversion)
Combined with AND

Computed Values:

None

Returns: Nothing (void).

2.2.7 Delete By Source And Posting Date Before

Operation: deleteBySourceAndPostingDateBefore

Input Parameters:

sourceCd: string (required) — transaction.source_cd
date: date (required) — cutoff date; transactions with transaction.posting_dt strictly before this date are deleted, after PST conversion

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	DELETE target; no joins

Filters:

transaction.source_cd = :sourceCd
transaction.posting_dt < :date (after PST conversion)
Combined with AND

Computed Values:

None

Returns: Nothing (void).

2.2.8 Delete By Source And Posting Date On Or After

Operation: deleteBySourceAndPostingDateOnOrAfter

Input Parameters:

sourceCd: string (required) — transaction.source_cd; the source job code (e.g., REV, BILL, CR, APP, PO)
date: date (required) — cutoff date; transactions with transaction.posting_dt >= this date are deleted, after PST conversion

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	DELETE target; no joins

Filters:

transaction.source_cd = :sourceCd
transaction.posting_dt >= :date (after PST conversion)
Combined with AND

Computed Values:

None

Returns: Integer count of deleted rows.

NOTE

This operation executes within its own database transaction and captures the count by collecting the IDs of deleted rows. Used as the cleanup step at the start of every accounting job to remove transactions that will be regenerated.

2.2.9 Delete By Source And Period From

Operation: deleteBySourceAndPeriodFrom

Input Parameters:

sourceCd: string (required) — transaction.source_cd; typically TRUE for the AR true-up job
periodId: integer (required) — fiscal_period.fiscal_period_id; transactions with transaction.posting_period_id >= this value are deleted

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	DELETE target; no joins

Filters:

transaction.source_cd = :sourceCd
transaction.posting_period_id >= :periodId
Combined with AND

Computed Values:

None

Returns: Integer count of deleted rows.

2.3 True-Up Support

2.3.1 Get True-Up Candidates

Operation: getTrueUpCandidates

Input Parameters:

currentPeriodId: integer (required) — fiscal_period.fiscal_period_id; the fiscal period to scan for revenue references

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	No joins; filters applied directly to `transaction`

Filters:

transaction.posting_period_id = :currentPeriodId
transaction.rev_ref IS NOT NULL
Combined with AND

Computed Values:

None

Returns: Array of distinct transaction.rev_ref string values for the given period, with null values excluded.

2.3.2 Get Balances For Ref

Operation: getBalancesForRef

Input Parameters:

ref: string (required) — transaction.rev_ref; the revenue reference to aggregate

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	LEFT	`account`	`transaction.account_id = account.account_id`	Filter by account class and return account classification

Filters:

account.account_class IN ('Deferred', 'Unbilled')
transaction.rev_ref = :ref
Combined with AND

Computed Values:

amount: CAST(SUM(transaction.trans_amt) AS FLOAT) — sum of transaction amounts per account

Returns: Array of balance rows grouped by transaction.account_id and account.account_class, one per account; each row contains account_id (defaulting to 0), account_class (defaulting to empty string), and amount (defaulting to 0).

2.3.3 Get Transaction Context For Ref

Operation: getTransactionContextForRef

Input Parameters:

ref: string (required) — transaction.rev_ref; the revenue reference to look up

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	No joins; filters applied directly to `transaction`

Filters:

transaction.rev_ref = :ref

Computed Values:

None

Returns: A single transaction row (or null if no match), limited to 1 row; the row's uta_entity_id, department_id, client_id, and transaction_ref_dt are used as dimensional context when creating true-up adjustment entries.

2.3.4 Get Parent Ref Balances

Operation: getParentRefBalances

Input Parameters:

parentRef: string (required) — transaction.rev_ref; the revenue reference to aggregate
periodIds: integer[] (required) — list of fiscal_period.fiscal_period_id values to include; empty arrays are short-circuited and return no results

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`transaction`	—	—	—	No joins; filters applied directly to `transaction`

Filters:

transaction.posting_period_id IN (:periodIds)
transaction.rev_ref = :parentRef
Combined with AND

Computed Values:

amount: CAST(SUM(transaction.trans_amt) AS FLOAT) — cumulative sum of transaction amounts per account across the specified periods

Returns: Array of balance rows grouped by transaction.account_id, one per account; each row contains account_id (defaulting to 0) and amount (defaulting to 0).

2.4 Account Domain

2.4.1 Get All Accounts

Operation: getAllAccounts

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`account`	—	—	—	No joins; returns all rows

Filters:

None; returns all rows

Computed Values:

None

Returns: Array of all account rows containing all columns from the table; used to populate account selection dropdowns and for in-memory lookups during accounting job execution.

2.4.2 Search Accounts

Operation: searchAccounts

Input Parameters:

query: string (required) — search term matched as a case-insensitive substring against both account.account_full_name and account.account_number

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`account`	—	—	—	No joins; filters applied directly to `account`

Filters:

account.account_full_name ILIKE '%' || :query || '%'
OR account.account_number ILIKE '%' || :query || '%'
The two conditions are combined with OR

Computed Values:

None

Returns: Array of matching account rows, limited to 20 results; used by the account autocomplete filter on the Accounting Jobs Dashboard.

2.5 Fiscal Period Domain

2.5.1 Get Current Period

Operation: getCurrentPeriod

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`fiscal_period`	—	—	—	No joins; filtered directly

Filters:

fiscal_period.current_ind = true

Computed Values:

None

Returns: A single fiscal_period row (or null if no period is marked as current), limited to 1 row; the currently active accounting period.

2.5.2 Get Period By Date

Operation: getPeriodByDate

Input Parameters:

date: date (required) — the date to look up, converted to a PST date string before comparison

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`fiscal_period`	—	—	—	No joins; filtered directly

Filters:

fiscal_period.period_start_dt <= :date (after PST conversion)
fiscal_period.period_end_dt >= :date (after PST conversion)
Combined with AND

Computed Values:

None

Returns: A single fiscal_period row (or null if no period covers the given date), limited to 1 row.

2.5.3 Get All Periods

Operation: getAllPeriods

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`fiscal_period`	—	—	—	No joins; returns all rows

Filters:

None; returns all rows

Computed Values:

None

Returns: Array of all fiscal_period rows ordered by fiscal_period.period_start_dt ascending; used by accounting jobs for in-memory period lookups to avoid per-record database queries.

2.5.4 Set Current Period

Operation: setCurrentPeriod

Input Parameters:

date: date (required) — the date whose containing fiscal period should become the current period; converted to a PST date string before use

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`fiscal_period`	—	—	—	Both SELECT and UPDATE target across three steps within a single database transaction

Filters:

Step 1 (find target): fiscal_period.period_start_dt <= :date AND fiscal_period.period_end_dt >= :date
Step 2 (reset all): no filter — updates all rows
Step 3 (set new): fiscal_period.fiscal_period_id = :targetId

Computed Values:

None

Returns: Nothing (void); throws an error if no fiscal period exists for the given date.

IMPORTANT

This operation executes all three steps within a single database transaction to ensure at most one period has current_ind = true at any time. The sequence is: (1) find target period, (2) set current_ind = false on all rows, (3) set current_ind = true on the target.

2.6 Job Execution History Domain

2.6.1 Create Job Execution

Operation: createJobExecution

Input Parameters:

jobCd: string (required) — accounting_job_execution_history.job_cd; the job type code (e.g., REV, BILL, CR, APP, PO, TRUE, CL, FX)
effectiveDt: date (required) — accounting_job_execution_history.effective_dt; the as-of date the job was run against
startedAt: timestamp (required) — accounting_job_execution_history.started_at; when the job began executing
completedAt: timestamp (optional) — accounting_job_execution_history.completed_at; null while running
statusCd: string (required) — accounting_job_execution_history.status_cd; RUNNING, SUCCESS, or FAILED
resultSummary: JSON (optional) — accounting_job_execution_history.result_summary; structured result object
createdBy: string (optional) — accounting_job_execution_history.created_by; defaults to SYSTEM

Tables & Joins:

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

Filters:

None

Computed Values:

None

Returns: The newly created accounting_job_execution_history row including the auto-generated primary key.

2.6.2 Get Last Execution By Job

Operation: getLastExecutionByJob

Input Parameters:

jobCd: string (required) — accounting_job_execution_history.job_cd; the job type code to query

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`accounting_job_execution_history`	—	—	—	No joins; filtered and sorted directly

Filters:

accounting_job_execution_history.job_cd = :jobCd

Computed Values:

None

Returns: A single accounting_job_execution_history row (or undefined if no execution exists), ordered by effective_dt descending then completed_at descending, limited to 1 row.

2.6.3 Get Last Executions

Operation: getLastExecutions

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`accounting_job_execution_history`	—	—	—	No joins; uses DISTINCT ON to return one row per job type

Filters:

None; returns one row per distinct accounting_job_execution_history.job_cd

Computed Values:

None

Returns: Array of summary rows, one per distinct job type, ordered by job_cd ascending then started_at descending (so DISTINCT ON picks the most recent); each row contains job_cd, effective_dt, and started_at.

3. Calculations & Formulas

3.1 Posting Date Determination

Every accounting job uses the same formula to determine a transaction's transaction.posting_dt. This is the single most important calculation in the accounting domain.

text

IF source_record.created_dt < driver_dt THEN
    posting_dt = first day of the fiscal_period containing driver_dt
    (fallback: YYYY-MM-01 of driver_dt's month if no fiscal_period found)
ELSE
    posting_dt = source_record.created_dt
END IF

Source: transaction.posting_dt, with driver dates sourced per job:

Job (`source_cd`)	Driver Date Source
`REV`	`revenue_item_schedules.revenue_dt`
`BILL`	`billing_item.billing_item_due_dt`
`CR`	`cash_receipt.deposit_date`
`APP`	`cash_receipt_worksheet.approved_dt` or `returned_dt` depending on worksheet status
`PO`	`payment_item.payment_date`

Precision: decimal(15,2) for amounts; dates stored as date type
Rationale: When a record is created before its business event date (e.g., a revenue schedule created in January for a February event), the posting lands in the future period. When the creation date is on or after the event date (late entry), the posting uses the creation date so it appears in the period when it was actually recorded.

3.2 Batch ID Generation

Each accounting job generates batch identifiers to group debit/credit pairs. All transactions in a batch must sum to zero.

text

batch_id = YYYYMMDDHHMMSS + zero_pad(sequence, 6)

Source: transaction.batch_id
Format: 14-character timestamp of the job execution start time concatenated with a zero-padded 6-digit counter incremented for each batch within the job run
Example: 20260301142530000001 is the first batch of a job started at 2026-03-01 14:25:30

3.3 Transaction Amount Sign Conventions

Each job type follows specific sign conventions for its debit and credit entries:

Job	Debit Side	Credit Side	Notes
`REV`	Deferred Revenue account: `+amount`	Revenue account: `-amount`	Revenue recognition moves from deferred to recognized
`BILL`	AR account: `+amount`	Unbilled account: `-amount`	Billing creates the receivable and offsets unbilled
`CR`	Cash/Bank account: `+amount`	Client Trust account: `-amount`	Cash inflow to bank, liability to client trust
`APP`	Client Trust account: `+amount` (if positive)	AR account: `-amount` (if positive)	Application offsets trust liability against receivable
`PO`	Client Trust account: `+amount`	Bank account: `-amount`	Payout offsets trust liability against cash outflow
`TRUE`	Varies by state	Varies by state	Balances Deferred and Unbilled accounts; see section 3.4

3.4 AR True-Up State Machine

The true-up job determines adjustment entries based on cumulative deferred and unbilled balances for each revenue reference.

text

Inputs:
  sum_deferred = SUM(trans_amt) for rev_ref across Deferred-class accounts through current period
  sum_unbilled = SUM(trans_amt) for rev_ref across Unbilled-class accounts through current period

State 1: sum_deferred > 0 AND sum_unbilled >= 0
  → Zero out deferred: Deferred gets -sum_deferred (Credit), Unbilled gets +sum_deferred (Debit)

State 2: sum_unbilled < 0 AND sum_deferred <= 0
  → Zero out unbilled: Unbilled gets -sum_unbilled (Credit), Deferred gets +sum_unbilled (Debit)

State 3: Both zero, OR one zero with the other on the "correct" side
  → No action; skip this rev_ref

State 4: sum_deferred < 0 AND sum_unbilled > 0
  → Mixed state: if |sum_deferred| < |sum_unbilled|, use State 1 logic; otherwise use State 2 logic

State 5: sum_deferred > 0 AND sum_unbilled < 0
  → Both backwards: if |sum_deferred| < |sum_unbilled|, zero unbilled toward deferred;
    otherwise zero deferred toward unbilled

Safety check: if both computed adjustment amounts are < 0.01, skip this rev_ref

Source: transaction.trans_amt, account.account_class (values 'Deferred' and 'Unbilled')
Precision: decimal(15,2)
Tolerance: 0.01 — revenue references producing adjustments below this threshold are skipped

3.5 Account Assignments by Job

Each job writes transactions to specific hard-coded account IDs:

Job	Account ID	Account Role
`REV`	13	Revenue
`REV`	1	Deferred Revenue
`BILL`	4	Accounts Receivable
`BILL`	6	Unbilled
`CR`	7 (default) or `cash_receipt.uta_account_id`	Cash / Bank
`CR`	2	Client Trust
`APP`	2 (default) or `cash_receipt_worksheet.uta_account_id`	Client Trust
`APP`	4	Accounts Receivable
`PO`	2	Client Trust
`PO`	7 (default) or lookup from bank relationship	Bank
`TRUE`	1	Deferred Revenue
`TRUE`	6	Unbilled

4. Cross-References

Document	Relationship
Accounting Data Model	Defines the schema for all four tables covered in this document: `transaction`, `account`, `fiscal_period`, `accounting_job_execution_history`.
Worksheets Data Model	The `APP` job queries `cash_receipt_application` records with `billing_item_detail_type_cd` = `REV`. Worksheet status (`A` = Approved, `R` = Returned) determines the driver date used in posting date calculation. The `CL` job queries `cash_receipt_client_ledger` records.
Settlements Data Model	The `PO` job queries `cash_receipt_payout` records derived from PAY-type settlement items. Payment execution status (`ACKNOWLEDGED` or `PAID`) determines when payout transactions can be created.
Cash Receipts Data Model	The `CR` job queries `cash_receipt` records. Receipt posting status and `cash_receipt.deposit_date` drive transaction creation. `transaction.source_id` links back to `cash_receipt.cash_receipt_id`.
Billing Items Data Model	The `BILL` job queries `billing_item` records. The `REV` job queries `revenue_item_schedules` records. Both use the respective driver date for posting date calculation.
Parties Data Model	The `party` table is joined in `searchTransactionsEnriched` (section 2.1.2) and `getRevLevelAggregatedTransactions` (section 2.1.4) to resolve `party.display_name` as the client display name.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Accounting Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 Transaction Search & Reporting ​

2.1.1 Search Transactions (Raw) ​

2.1.2 Search Transactions Enriched ​

2.1.3 Get Aggregated Transactions ​

2.1.4 Get Rev Level Aggregated Transactions ​

2.1.5 Get Revenue Level Aggregated Transactions ​

2.2 Transaction Lifecycle (Writes) ​

2.2.1 Create Transaction ​

2.2.2 Create Transactions Batch ​

2.2.3 Delete By Source ​

2.2.4 Delete By Batch ​

2.2.5 Delete By Source And Type ​

2.2.6 Delete By Source And Ref Date ​

2.2.7 Delete By Source And Posting Date Before ​

2.2.8 Delete By Source And Posting Date On Or After ​

2.2.9 Delete By Source And Period From ​

2.3 True-Up Support ​

2.3.1 Get True-Up Candidates ​

2.3.2 Get Balances For Ref ​

2.3.3 Get Transaction Context For Ref ​

2.3.4 Get Parent Ref Balances ​

2.4 Account Domain ​

2.4.1 Get All Accounts ​

2.4.2 Search Accounts ​

2.5 Fiscal Period Domain ​

2.5.1 Get Current Period ​

2.5.2 Get Period By Date ​

2.5.3 Get All Periods ​

2.5.4 Set Current Period ​

2.6 Job Execution History Domain ​

2.6.1 Create Job Execution ​

2.6.2 Get Last Execution By Job ​

2.6.3 Get Last Executions ​

3. Calculations & Formulas ​

3.1 Posting Date Determination ​

3.2 Batch ID Generation ​

3.3 Transaction Amount Sign Conventions ​

3.4 AR True-Up State Machine ​

3.5 Account Assignments by Job ​

4. Cross-References ​

Accounting Queries

1. Executive Summary

2. Key Queries

2.1 Transaction Search & Reporting

2.1.1 Search Transactions (Raw)

2.1.2 Search Transactions Enriched

2.1.3 Get Aggregated Transactions

2.1.4 Get Rev Level Aggregated Transactions

2.1.5 Get Revenue Level Aggregated Transactions

2.2 Transaction Lifecycle (Writes)

2.2.1 Create Transaction

2.2.2 Create Transactions Batch

2.2.3 Delete By Source

2.2.4 Delete By Batch

2.2.5 Delete By Source And Type

2.2.6 Delete By Source And Ref Date

2.2.7 Delete By Source And Posting Date Before

2.2.8 Delete By Source And Posting Date On Or After

2.2.9 Delete By Source And Period From

2.3 True-Up Support

2.3.1 Get True-Up Candidates

2.3.2 Get Balances For Ref

2.3.3 Get Transaction Context For Ref

2.3.4 Get Parent Ref Balances

2.4 Account Domain

2.4.1 Get All Accounts

2.4.2 Search Accounts

2.5 Fiscal Period Domain

2.5.1 Get Current Period

2.5.2 Get Period By Date

2.5.3 Get All Periods

2.5.4 Set Current Period

2.6 Job Execution History Domain

2.6.1 Create Job Execution

2.6.2 Get Last Execution By Job

2.6.3 Get Last Executions

3. Calculations & Formulas

3.1 Posting Date Determination

3.2 Batch ID Generation

3.3 Transaction Amount Sign Conventions

3.4 AR True-Up State Machine

3.5 Account Assignments by Job

4. Cross-References