Accounting Procedures

1. Executive Summary

This document specifies every step-by-step data mutation in the accounting domain of UTA Client Payments. It describes the eight batch accounting jobs that generate double-entry transactions, the fiscal period management operations, and the GL posting lifecycle. Each procedure is documented at the data level: which tables are read, which rows are inserted or updated, what values are set, and in what order.

All transactions are created by batch jobs — never directly by users. Each job follows a common pattern: clean up prior runs, reset source records, select eligible source data, compute posting dates, generate balanced debit/credit transaction pairs, batch-insert them into the transaction table, and mark source records as posted. Jobs are triggered from the Accounting Jobs Dashboard by selecting an effective date and one or more job types.

Job Summary

Job Code	Name	Source Records	Transaction Class	Account Pair	Status
REV	Revenue Recognition	revenue_item_schedule	REV	Revenue (13) / Deferred (1)	Implemented
BILL	Billing Posting	billing_item_detail	AR	AR (4) / Unbilled (6)	Implemented
CR	Cash Receipt Posting	cash_receipt	CASH	Bank (varies) / Client Trust (2)	Implemented
APP	Cash Application Posting	cash_receipt_application	CASH + AR	Client Trust (varies) / AR (4)	Implemented
PO	Payout Posting	payment_item	CASH	Client Trust (2) / Bank (varies)	Implemented
TRUE	AR True-Up	Computed from transaction	AR + REV	Unbilled (6) / Deferred (1)	Implemented
FX	FX Adjustment	Currency conversion events	FX	TBD	Not implemented
CL	Client Ledger	cash_receipt_client_ledger entries	CL	TBD	Not implemented

**PoC Artifact:**

Account IDs (1, 2, 4, 6, 7, 13) are hardcoded in the PoC. The production system must resolve accounts dynamically from the account table using account_class and account_number.

---

2. Key Procedures

2.1 Job Orchestration (Run Jobs)

Operation: runJobs

Trigger: User submits the Accounting Jobs form with an effective date and one or more selected job types.

Input Parameters:

• job_types: String[] (required) — one or more of: REV, BILL, CR, APP, PO, FX, TRUE, CL
• actor_id: String (optional) — identifier for the user or system executing the jobs; defaults to SYSTEM

The master orchestration procedure that manages fiscal period assignment and dispatches individual job procedures in sequence.

Step 1. Dispatch Each Selected Job

• Source: The job_types array provided as input.
• Action: For each job type in order, record start time, invoke the appropriate sub-procedure (sections 2.2–2.8), record end time and status.
• Logic:
  • Record the start timestamp for the job.
  • Dispatch to the procedure for the selected job code (REV → 2.2, BILL → 2.3, CR → 2.4, APP → 2.5, PO → 2.6, TRUE → 2.7, FX/CL → 2.8).
  • Record the end timestamp and outcome (SUCCESS or FAILED).

Step 2. Record Job Execution History

• Source: Job result data from Step 2.
• Action: INSERT one row into accounting_job_execution_history per dispatched job.
• Logic:
  • Set job_cd = the job type code (e.g., REV).
  • Set effective_dt = as_of_date converted to a PST date string.
  • Set started_at = the recorded start timestamp.
  • Set completed_at = the recorded end timestamp.
  • Set status_cd = 'SUCCESS' or 'FAILED' based on whether the job threw an error.
  • Set result_summary = JSON object with job-specific metrics (e.g., { "processedCount": 42, "batchIds": [...] }) or error details on failure.
  • Set created_by = actor_id.

Side-effects:

• Sets fiscal_period.current_ind = true on the period matching as_of_date; all other periods set to false.
• Inserts one accounting_job_execution_history row per dispatched job.

Postconditions:

• The fiscal_period row whose date range contains as_of_date has current_ind = true.
• Each dispatched job has a corresponding accounting_job_execution_history record with status_cd of SUCCESS or FAILED.
• An array of per-job results and a summary string are returned to the caller.

IMPORTANT

Jobs are executed sequentially, not in parallel. A failure in one job does not prevent subsequent jobs from running — each job's success or failure is recorded independently.

---

2.2 Revenue Recognition Job (REV)

Operation: runRevJob

Trigger: User selects REV in the Accounting Jobs form and submits; dispatched by the runJobs orchestrator (section 2.1).

Input Parameters:

• as_of_date: Date (required) — only revenue schedules with revenue_dt <= as_of_date are eligible
• actor_id: String (required) — identifier for the user or system initiating the job

Recognizes revenue by converting unposted revenue schedules into double-entry transactions that move amounts from the Deferred Revenue account to the Revenue account.

Step 2. Fetch Eligible Schedules

• Source: revenue_item_schedule joined to revenue_item.
• Action: SELECT rows for processing.
• Logic:
  • Filter: revenue_item_posting_status_cd = 'U' and revenue_dt <= as_of_date.
  • Join to revenue_item to obtain sales_item_ref, uta_entity_id, department_id, client_id.
  • Load all fiscal_period rows into memory for in-memory date-to-period matching.

Step 3. Generate Transaction Pairs and Batch Insert

• Source: Each eligible revenue_item_schedule row with computed posting date.
• Action: INSERT two rows into transaction per schedule (one Revenue side, one Deferred side), batched in chunks of 1,000.
• Logic:
  • Generate a unique batch_id in format YYYYMMDDHHMMSS + zero-padded 6-digit sequence number.
  • Revenue side: class_cd = 'REV', source_cd = 'REV', account_id = 13, type_cd = 'C' (positive amounts) or 'D' (negative), trans_amt = revenue_amt * -1.
  • Deferred side: class_cd = 'REV', source_cd = 'REV', account_id = 1, type_cd = 'D' (positive amounts) or 'C' (negative), trans_amt = revenue_amt.
  • For negative amounts (reversals), type_cd values are inverted and reverse_ind = true.
  • Set source_id = revenue_item_schedule_id, source_ref = revenue_item.sales_item_ref, rev_ref = revenue_item.sales_item_ref.
  • Set transaction_ref_dt = revenue_dt, gl_status_cd = 'U', gl_posting_dt = null.
  • All currency fields default to USD.

Step 4. Mark Source Records as Posted

• Source: Each processed revenue_item_schedule row.
• Action: UPDATE revenue_item_schedule.
• Logic:
  • Set revenue_item_posting_status_cd = 'P'.
  • Set revenue_item_posting_dt = the computed posting_dt.
  • Set updated_by = actor_id.

Side-effects:

• Inserts balanced debit/credit transaction rows (two per schedule) with gl_status_cd = 'U'.
• Prior transaction rows with source_cd = 'REV' and posting_dt >= as_of_date are deleted before insertion (idempotency).

Postconditions:

• Each eligible revenue_item_schedule has revenue_item_posting_status_cd = 'P' and revenue_item_posting_dt set.
• Balanced transaction pairs exist in the transaction table for every processed schedule.

---

2.3 Billing Posting Job (BILL)

Operation: runBillJob

Trigger: User selects BILL in the Accounting Jobs form and submits; dispatched by the runJobs orchestrator (section 2.1).

Input Parameters:

• as_of_date: Date (required) — only billing item details whose parent billing_item.billing_item_due_dt <= as_of_date are eligible
• actor_id: String (required) — identifier for the user or system initiating the job

Posts billing items to the AR subledger by moving amounts from the Unbilled account to the AR (Accounts Receivable) account.

Step 1. Fetch Eligible Billing Item Details

• Source: billing_item_detail joined to billing_item and revenue_item.
• Action: SELECT rows for processing.
• Logic:
  • Filter: billing_item_detail.posting_status_cd = 'U' and billing_item.billing_item_due_dt <= as_of_date.
  • The parent billing_item must be active.
  • Join to billing_item for billing_item_due_dt, uta_entity_id, department_id, client_id, payment_term_ref.
  • Join to revenue_item for sales_item_ref.
  • Load all fiscal_period rows into memory.

Step 2. Generate Transaction Pairs and Batch Insert

• Source: Each eligible billing_item_detail row with computed posting date.
• Action: INSERT two rows into transaction per detail (one AR side, one Unbilled side), batched in chunks of 1,000.
• Logic:
  • Generate a unique batch_id.
  • AR side: class_cd = 'AR', source_cd = 'BILL', account_id = 4, type_cd = 'D' (positive) or 'C' (negative), trans_amt = billing_item_detail_amt.
  • Unbilled side: class_cd = 'AR', source_cd = 'BILL', account_id = 6, type_cd = 'C' (positive) or 'D' (negative), trans_amt = billing_item_detail_amt * -1.
  • For negative amounts, type_cd values are inverted and reverse_ind = true.
  • Set source_id = billing_item_detail_id, source_ref = billing_item.payment_term_ref, rev_ref = revenue_item.sales_item_ref.
  • Set transaction_ref_dt = billing_item.billing_item_due_dt.

Step 3. Mark Source Records as Posted

• Source: Each processed billing_item_detail row.
• Action: UPDATE billing_item_detail.
• Logic:
  • Set posting_status_cd = 'P'.
  • Set posting_dt = the computed posting date.

Side-effects:

• Inserts balanced debit/credit transaction rows (two per billing item detail) with gl_status_cd = 'U'.
• Prior transaction rows with source_cd = 'BILL' and posting_dt >= as_of_date are deleted before insertion (idempotency).

Postconditions:

• Each eligible billing_item_detail has posting_status_cd = 'P' and posting_dt set.
• Balanced transaction pairs exist in the transaction table for every processed detail.

---

2.4 Cash Receipt Posting Job (CR)

Operation: runCrJob

Trigger: User selects CR in the Accounting Jobs form and submits; dispatched by the runJobs orchestrator (section 2.1).

Input Parameters:

• as_of_date: Date (required) — only receipts with deposit_date <= as_of_date are eligible
• actor_id: String (required) — identifier for the user or system initiating the job

Posts incoming cash receipts to the Cash subledger by recording the deposit into the bank account and the corresponding liability to the Client Trust account.

Step 1. Fetch Eligible Cash Receipts

• Source: cash_receipt joined to bank_account.
• Action: SELECT rows for processing.
• Logic:
  • Filter: cash_receipt.posting_status_cd = 'U' and deposit_date <= as_of_date.
  • Join to bank_account to obtain uta_account_id for the bank account side of the transaction.
  • Load all fiscal_period rows into memory.

Step 2. Generate Transaction Pairs and Batch Insert

• Source: Each eligible cash_receipt row with computed posting date.
• Action: INSERT two rows into transaction per receipt (one Bank side, one Client Trust side), batched in chunks of 1,000.
• Logic:
  • Generate a unique batch_id.
  • Bank side: class_cd = 'CASH', source_cd = 'CR', account_id = bank_account.uta_account_id (default 7), type_cd = 'D', trans_amt = original_receipt_amt.
  • Client Trust side: class_cd = 'CASH', source_cd = 'CR', account_id = 2, type_cd = 'C', trans_amt = original_receipt_amt * -1.
  • Set source_id = cash_receipt_id, source_ref = bank_ref_id or cash_receipt_ref (first non-null).
  • Set transaction_ref_dt = deposit_date, trans_currency_cd = original_currency_cd.

**PoC Artifact:**

The CR job hardcodes uta_entity_id = 1, department_id = null, and client_id = null on all transactions. The production system should resolve these from the receipt's associated entity and split references.

Step 3. Mark Source Records as Posted

• Source: Each processed cash_receipt row.
• Action: UPDATE cash_receipt.
• Logic:
  • Set posting_status_cd = 'P'.
  • Set posting_dt = the computed posting date.

Side-effects:

• Inserts balanced debit/credit transaction rows (two per receipt) with gl_status_cd = 'U'.
• Prior transaction rows with source_cd = 'CR' and posting_dt >= as_of_date are deleted before insertion (idempotency).

Postconditions:

• Each eligible cash_receipt has posting_status_cd = 'P' and posting_dt set.
• Balanced transaction pairs exist in the transaction table for every processed receipt.

---

2.5 Cash Application Posting Job (APP)

Operation: runAppJob

Trigger: User selects APP in the Accounting Jobs form and submits; dispatched by the runJobs orchestrator (section 2.1).

Input Parameters:

• as_of_date: Date (required) — only worksheets with approved_dt <= as_of_date (status A) or returned_dt <= as_of_date (status R) and created_dt <= as_of_date are eligible
• actor_id: String (required) — identifier for the user or system initiating the job

Posts the cash application entries from worksheets that have been approved (status A) or returned (status R). Generates the subledger entries that reflect cash moving from the Client Trust account to reduce the AR balance.

Step 1. Fetch Eligible Applications

• Source: cash_receipt_application joined through cash_receipt_worksheet, cash_receipt_split, cash_receipt, bank_account, billing_item_detail, billing_item, revenue_item.
• Action: SELECT rows for processing.
• Logic:
  • Join through cash_receipt_worksheet where posting_status_cd is not 'P'.
  • Filter: worksheet cash_receipt_worksheet_status_cd is 'A' (Approved) with approved_dt <= as_of_date, OR 'R' (Returned) with returned_dt <= as_of_date.
  • Additionally filter: cash_receipt_worksheet.created_dt <= as_of_date.
  • Load all fiscal_period rows into memory.

Step 2. Group Applications by Worksheet

• Source: The fetched cash_receipt_application rows.
• Action: Group applications in memory by cash_receipt_worksheet_id.
• Logic:
  • Each worksheet group will share one batch_id, ensuring the entire worksheet posts as one balanced batch.

Step 3. Generate Transaction Pairs Per Worksheet Group and Batch Insert

• Source: Each worksheet group and its member cash_receipt_application rows.
• Action: INSERT two rows into transaction per application (one Client Trust side, one AR side), all sharing one batch_id per worksheet group, batched in chunks of 1,000.
• Logic:
  • Generate one batch_id for the entire worksheet group.
  • For each application:
    • Determine driver date: if worksheet status is 'A', driver date = approved_dt; if 'R', driver date = returned_dt.
    • Compute posting_dt: if created_dt < driver_date, set to first day of the driver date's month; otherwise set to created_dt.
    • Client Trust side: class_cd = 'CASH', source_cd = 'APP', account_id = bank_account.uta_account_id (default 2), type_cd = 'D' if positive or 'C' if negative, trans_amt = abs(cash_receipt_amt_applied).
    • AR side: class_cd = 'AR', source_cd = 'APP', account_id = 4, type_cd = 'C' if positive or 'D' if negative, trans_amt = cash_receipt_amt_applied * -1.
    • For negative amounts, reverse_ind = true.
    • Set source_id = cash_receipt_application_id, source_ref = billing_item.payment_term_ref, rev_ref = revenue_item.sales_item_ref.
    • Set client_id = billing_item.client_id on the AR side only; Trust side client_id is null.

IMPORTANT

All applications belonging to the same cash_receipt_worksheet_id share a single batch_id. This ensures the entire worksheet posts as one balanced batch.

Step 4. Mark Source Worksheets as Posted

• Source: Each processed cash_receipt_worksheet.
• Action: UPDATE cash_receipt_worksheet.
• Logic:
  • Set posting_status_cd = 'P'.
  • Set posting_dt = the computed posting date for the worksheet.

Side-effects:

• Inserts balanced debit/credit transaction rows (two per application), grouped by worksheet under a shared batch_id, with gl_status_cd = 'U'.
• Prior transaction rows with source_cd = 'APP' and posting_dt >= as_of_date are deleted before insertion (idempotency).

Postconditions:

• Each eligible cash_receipt_worksheet has posting_status_cd = 'P' and posting_dt set.
• Balanced transaction pairs exist in the transaction table for every processed application, grouped into per-worksheet batches.

---

2.6 Payout Posting Job (PO)

Operation: runPoJob

Trigger: User selects PO in the Accounting Jobs form and submits; dispatched by the runJobs orchestrator (section 2.1).

Input Parameters:

• as_of_date: Date (required) — only payment items with created_dt <= as_of_date are eligible
• actor_id: String (required) — identifier for the user or system initiating the job

Posts payment items (payouts) to the Cash subledger after they have been created. Records the outflow from Client Trust to the bank account used for the payment.

Step 1. Fetch Eligible Payment Items

• Source: payment_item joined through participant_settlement_item, participant_settlement, cash_receipt_application, billing_item_detail, billing_item, revenue_item, bank_account.
• Action: SELECT rows for processing.
• Logic:
  • Filter: payment_item.posting_status_cd = 'U' and created_dt <= as_of_date.
  • Join through the settlement chain to resolve bank_account.uta_account_id (for the Bank side), billing_item.payment_term_ref, and revenue_item.sales_item_ref.
  • Group results by payment_item_id to collect potentially multiple payment_term_ref and sales_item_ref values.
  • Load all fiscal_period rows into memory.

Step 2. Generate Transaction Pairs and Batch Insert

• Source: Each eligible payment_item row with computed posting date.
• Action: INSERT two rows into transaction per payment item (one Client Trust side, one Bank side), batched in chunks of 1,000.
• Logic:
  • Generate a unique batch_id.
  • Client Trust side: class_cd = 'CASH', source_cd = 'PO', account_id = 2, type_cd = 'D', trans_amt = payment_item_amt.
  • Bank side: class_cd = 'CASH', source_cd = 'PO', account_id = bank_account.uta_account_id (default 7), type_cd = 'C', trans_amt = payment_item_amt * -1.
  • Set source_id = payment_item_id.
  • Set source_ref = first payment_term_ref if single; first sales_item_ref if multiple.
  • Set rev_ref = first sales_item_ref.
  • Set transaction_ref_dt = payment_date, trans_currency_cd = payment_item_currency_cd.

Step 3. Mark Source Records as Posted

• Source: Each processed payment_item row.
• Action: UPDATE payment_item.
• Logic:
  • Set posting_status_cd = 'P'.
  • Set posting_dt = the computed posting date.

Side-effects:

• Inserts balanced debit/credit transaction rows (two per payment item) with gl_status_cd = 'U'.
• Prior transaction rows with source_cd = 'PO' and posting_dt >= as_of_date are deleted before insertion (idempotency).

Postconditions:

• Each eligible payment_item has posting_status_cd = 'P' and posting_dt set.
• Balanced transaction pairs exist in the transaction table for every processed payment item.

WARNING

In the PoC, the PO job processes all unposted payment items regardless of bank execution status. The production system must enforce the two-stage payment lifecycle: PAY payout transactions should only post to GL after the bank confirms the payment has been sent (payment_execution_status_cd in ACKNOWLEDGED or PAID). This is the "don't post what hasn't left the building" rule.

---

2.7 AR True-Up Job (TRUE)

Operation: runTrueJob

Trigger: User selects TRUE in the Accounting Jobs form and submits; dispatched by the runJobs orchestrator (section 2.1).

Input Parameters:

• as_of_date: Date (required) — determines the current fiscal period; true-up entries are scoped to that period
• actor_id: String (required) — identifier for the user or system initiating the job

Balances the Deferred Revenue and Unbilled AR accounts for each parent revenue reference (rev_ref). This is a period-end reconciliation that ensures the Deferred and Unbilled sides of revenue and billing activity are in balance.

Step 1. Determine Current Fiscal Period

• Source: fiscal_period table (already set by orchestrator).
• Action: Identify current_period_id — the fiscal_period_id where current_ind = true.
• Logic:
  • Read the fiscal_period row with current_ind = true to obtain current_period_id and posting_period_ref.

Step 2. Identify Candidate Revenue References

• Source: transaction table.
• Action: SELECT distinct rev_ref values.
• Logic:
  • Select all distinct rev_ref values from transaction where posting_period_id = current_period_id and rev_ref IS NOT NULL.

Step 3. Calculate Cumulative Balances Per Reference

• Source: transaction joined to account, filtered by rev_ref and all periods up to and including the current period.
• Action: Aggregate trans_amt by account for each candidate rev_ref.
• Logic:
  • Sum trans_amt grouped by account_id across all fiscal_period rows with fiscal_period_id <= current_period_id.
  • Join to account to classify each account as Deferred or Unbilled by account_class.
  • Compute sum_deferred and sum_unbilled (rounded to 2 decimal places).

Step 4. Determine True-Up State and Generate Adjustment

• Source: Computed sum_deferred and sum_unbilled for each rev_ref.
• Action: For out-of-balance references, INSERT two rows into transaction (one Unbilled side, one Deferred side).
• Logic:
  • State 1 (sum_deferred > 0 and sum_unbilled >= 0): Credit Deferred, Debit Unbilled by sum_deferred.
  • State 2 (sum_unbilled < 0 and sum_deferred <= 0): Credit Unbilled, Debit Deferred by abs(sum_unbilled).
  • State 3 (both zero, or one side is zero with the other on the correct side): no action — balanced.
  • State 4 (sum_deferred < 0 and sum_unbilled > 0, both on correct side but unequal): reduce the smaller absolute side.
  • State 5 (sum_deferred > 0 and sum_unbilled < 0, both backwards): balance toward the smaller absolute side.
  • Skip if balanced (State 3) or if computed adjustment amount is less than $0.01.
  • Retrieve context from an existing transaction for the same rev_ref to copy uta_entity_id, department_id, client_id, and transaction_ref_dt.
  • Generate a unique batch_id; set source_cd = 'TRUE', source_id = null, source_ref = rev_ref, posting_dt = as_of_date, posting_period_id = current_period_id.

Side-effects:

• Inserts balanced debit/credit transaction rows (two per out-of-balance rev_ref) with gl_status_cd = 'U'.
• No source records are updated — the TRUE job operates solely on the transaction table.
• All prior TRUE transactions for posting_period_id >= current_period_id are deleted before recalculation (idempotency).

Postconditions:

• For every rev_ref active in the current period, the cumulative Deferred and Unbilled balances are brought into alignment via corrective transaction entries.
• The TRUE job does not update any source record posting status fields.

NOTE

The TRUE job does not update any source records. Its source is the transaction table itself. The job is designed to be idempotent: it deletes all prior TRUE transactions for the current period before recalculating.

---

2.8 Unimplemented Jobs (FX and CL)

---

2.9 Set Current Fiscal Period

Operation: setCurrentPeriod

Input Parameters:fiscal_period to mark as current

Updates the fiscal_period table to mark a specific period as the active accounting period based on a given date.

Step 1. Find the Target Period

• Source: fiscal_period table.
• Action: SELECT the row whose date range contains as_of_date.
• Logic:
  • Query fiscal_period where period_start_dt <= as_of_date and period_end_dt >= as_of_date.
  • If no matching period is found, abort with an error — no mutations are made.

Step 2. Reset All Periods

• Source: fiscal_period table, all rows.
• Action: UPDATE all fiscal_period rows.
• Logic:
  • Set current_ind = false on every row.

Step 3. Mark Target Period as Current

• Source: The fiscal_period row identified in Step 1.
• Action: UPDATE the target fiscal_period row.
• Logic:
  • Set current_ind = true on the matching row.

Side-effects:

• All fiscal_period rows have current_ind set to false.
• The single matching fiscal_period row has current_ind set to true.

Postconditions:

• Exactly one fiscal_period row has current_ind = true — the row whose date range contains as_of_date.
• If no fiscal period matches, the procedure aborts with an error and no mutations occur.

NOTE

The current_cash_ind flag exists on fiscal_period but is not modified by this procedure. It serves a separate purpose for cash-specific period control and may be managed independently.

---

2.10 Record Job Execution History

Operation: recordJobExecution

Trigger: Called automatically by the runJobs orchestrator (section 2.1) after each individual job completes, whether successfully or with a failure. Not invoked directly by users.

Input Parameters:

• job_cd: String (required) — the job type code (REV, BILL, CR, APP, PO, FX, TRUE, CL)
• effective_dt: Date (required) — the as_of_date converted to a PST date string
• started_at: Timestamp (required) — when the job began
• completed_at: Timestamp (required) — when the job finished
• status_cd: String (required) — 'SUCCESS', 'FAILED', or 'RUNNING'
• result_summary: JSON (optional) — job-specific metrics or error details
• actor_id: String (required) — identifier for the user or system that initiated the run

Records every job execution — successful or failed — as an audit trail entry in accounting_job_execution_history.

Step 1. Insert Execution History Row

• Source: Job result data provided by the orchestrator.
• Action: INSERT one row into accounting_job_execution_history.
• Logic:
  • Set job_cd = provided job type code.
  • Set effective_dt = the effective date in PST date string format.
  • Set started_at = the job's start timestamp.
  • Set completed_at = the job's end timestamp.
  • Set status_cd = 'SUCCESS' or 'FAILED'.
  • Set result_summary = JSON object: on success, { "processedCount": N, "batchIds": [...] }; on failure, { "error": "..." }.
  • Set created_by = actor_id.

Side-effects:

• None beyond the INSERT into accounting_job_execution_history.

Postconditions:

• One accounting_job_execution_history row exists for the job execution.
• The most recent execution per job_cd is queryable via DISTINCT ON (job_cd) ORDER BY job_cd, started_at DESC (used by the Accounting Jobs Dashboard to show last-run status per job type).

---

2.11 Account Management (Chart of Accounts)

Operation: getAccounts / searchAccounts

Trigger: User opens the account selector or performs a chart-of-accounts lookup from any accounting screen.

Input Parameters:

• query: String (optional) — free-text search against account.account_full_name and account.account_number; if omitted, all accounts are returned

Provides read access to the GL chart of accounts stored in the account table. Accounting jobs reference these accounts by hardcoded account_id in the PoC; the production system must resolve accounts dynamically.

Step 1. Retrieve Accounts

• Source: account table.
• Action: SELECT rows from account.
• Logic:
  • If query is provided: filter where account_full_name ILIKE '%query%' OR account_number ILIKE '%query%'; limit to 20 results.
  • If no query: return all rows.

Side-effects:

• Read-only. No mutations.

Postconditions:

• Returns the matching account rows for display or selection.

IMPORTANT

The production system should validate that the target account_id has account.status_cd = 'A' before inserting transactions. The PoC does not perform this check. The following account mappings are used by the jobs in the PoC:

account_id	account_class	Purpose	Used By
1	Deferred	Deferred Revenue	REV, TRUE
2	Trust	Client Trust	CR, APP, PO
4	AR	Accounts Receivable	BILL, APP
6	Unbilled	Unbilled AR	BILL, TRUE
7	Cash	Default Bank/Cash Account	CR, PO (fallback)
13	Revenue	Revenue (Recognized)	REV

---

3. Business Rules & Logic

3.1 Double-Entry Accounting Rules

Business rule: Every accounting batch must be balanced — debits must equal credits within every batch_id. No unbalanced batch may enter the transaction table.

Data-level enforcement:

• Read: Each job generates transaction pairs in memory before inserting.
• Guard: No database-level constraint enforces batch balance; enforcement is at the application layer during batch creation.
• Write: Each job creates exactly two transaction rows per source record (one debit, one credit) sharing the same batch_id. The exception is the APP job, which creates two transactions per application but groups all applications from the same worksheet under one batch_id. The trans_amt values within a batch must sum to zero (one positive, one negative of equal absolute value).

---

3.2 Fiscal Period Constraints

Business rule: Transactions must reference a valid open fiscal period. Posting to a closed period is not permitted.

Data-level enforcement:

• Read: Each job loads all fiscal_period rows into memory before processing. fiscal_period.period_closed_dt indicates whether a period is closed.
• Guard: The production system must prevent posting to any fiscal_period where period_closed_dt is not null. The PoC does not enforce this check.
• Write: transaction.posting_period_id is resolved from the in-memory fiscal_period list by matching posting_dt to period_start_dt and period_end_dt. At most one fiscal_period should have current_ind = true at any time — enforced by the setCurrentPeriod procedure (section 2.9) which resets all rows before setting the target.

IMPORTANT

The production system must enforce: no new transactions may be inserted into a fiscal period where period_closed_dt is not null.

---

4. Field Mapping & Transformation

4.1 Revenue Schedule to Transaction (REV Job)

Source Table.Field	Target Table.Field	Transform
—	transaction.class_cd	Constant 'REV'
—	transaction.source_cd	Constant 'REV'
revenue_item_schedule.revenue_item_schedule_id	transaction.source_id	Copied as-is
revenue_item.sales_item_ref	transaction.source_ref	Copied as-is
revenue_item.sales_item_ref	transaction.rev_ref	Copied as-is
—	transaction.batch_id	System-generated (YYYYMMDDHHMMSS + zero-padded 6-digit sequence)
—	transaction.account_id	13 (Revenue side) or 1 (Deferred side)
revenue_item_schedule.revenue_amt	transaction.type_cd	'C' if positive (Revenue side); 'D' if positive (Deferred side); inverted for negatives
revenue_item_schedule.revenue_amt	transaction.reverse_ind	true if amount is negative
revenue_item_schedule.revenue_amt	transaction.trans_amt	Revenue side: amount * -1; Deferred side: amount
—	transaction.trans_currency_cd	Default 'USD'
—	transaction.group_currency_cd	Default 'USD'
—	transaction.reporting_currency_cd	Default 'USD'
revenue_item_schedule.revenue_dt	transaction.transaction_ref_dt	Copied as-is
Computed	transaction.posting_dt	See Rule 3.2
Computed	transaction.posting_period_id	Fiscal period containing posting_dt
Computed	transaction.posting_period_ref	fiscal_period.period_ref for the matched period
revenue_item.uta_entity_id	transaction.uta_entity_id	Copied as-is
revenue_item.department_id	transaction.department_id	Copied as-is
revenue_item.client_id	transaction.client_id	Copied as-is
—	transaction.gl_status_cd	Constant 'U'
—	transaction.gl_posting_dt	null

---

4.2 Billing Item Detail to Transaction (BILL Job)

Source Table.Field	Target Table.Field	Transform
—	transaction.class_cd	Constant 'AR'
—	transaction.source_cd	Constant 'BILL'
billing_item_detail.billing_item_detail_id	transaction.source_id	Copied as-is
billing_item.payment_term_ref	transaction.source_ref	Copied as-is
revenue_item.sales_item_ref	transaction.rev_ref	Copied as-is (via billing_item join)
—	transaction.account_id	4 (AR side) or 6 (Unbilled side)
billing_item_detail.billing_item_detail_amt	transaction.type_cd	'D' if positive (AR side); 'C' if positive (Unbilled side); inverted for negatives
billing_item_detail.billing_item_detail_amt	transaction.reverse_ind	true if amount is negative
billing_item_detail.billing_item_detail_amt	transaction.trans_amt	AR side: amount; Unbilled side: amount * -1
billing_item.billing_item_due_dt	transaction.transaction_ref_dt	Copied as-is
Computed	transaction.posting_dt	See Rule 3.2
billing_item.uta_entity_id	transaction.uta_entity_id	Copied as-is
billing_item.department_id	transaction.department_id	Copied as-is
billing_item.client_id	transaction.client_id	Copied as-is

---

4.3 Cash Receipt to Transaction (CR Job)

Source Table.Field	Target Table.Field	Transform
—	transaction.class_cd	Constant 'CASH'
—	transaction.source_cd	Constant 'CR'
cash_receipt.cash_receipt_id	transaction.source_id	Copied as-is
cash_receipt.bank_ref_id or cash_receipt.cash_receipt_ref	transaction.source_ref	First non-null
bank_account.uta_account_id	transaction.account_id	Bank side: from receipt's bank account; Trust side: hardcoded 2
—	transaction.type_cd	Bank side: 'D'; Trust side: 'C'
cash_receipt.original_receipt_amt	transaction.trans_amt	Bank side: amount; Trust side: amount * -1
cash_receipt.original_currency_cd	transaction.trans_currency_cd	Copied as-is
cash_receipt.deposit_date	transaction.transaction_ref_dt	Copied as-is
Computed	transaction.posting_dt	See Rule 3.2
—	transaction.uta_entity_id	Hardcoded 1 (PoC artifact)
—	transaction.department_id	null (PoC artifact)
—	transaction.client_id	null (PoC artifact)

---

4.4 Cash Application to Transaction (APP Job)

Source Table.Field	Target Table.Field	Transform
—	transaction.class_cd	'CASH' (Trust side) or 'AR' (AR side)
—	transaction.source_cd	Constant 'APP'
cash_receipt_application.cash_receipt_application_id	transaction.source_id	Copied as-is
billing_item.payment_term_ref	transaction.source_ref	Copied as-is
revenue_item.sales_item_ref	transaction.rev_ref	Copied as-is
—	transaction.batch_id	One per worksheet; all applications in the same worksheet share it
bank_account.uta_account_id	transaction.account_id	Trust side: uta_account_id (default 2); AR side: 4
cash_receipt_application.cash_receipt_amt_applied	transaction.type_cd	Trust: 'D' if positive, 'C' if negative; AR: inverse
cash_receipt_application.cash_receipt_amt_applied	transaction.reverse_ind	true if amount is negative
cash_receipt_application.cash_receipt_amt_applied	transaction.trans_amt	Trust: abs(amount); AR: amount * -1
cash_receipt_worksheet.created_dt	transaction.transaction_ref_dt	Date portion only
Computed	transaction.posting_dt	Uses approved_dt or returned_dt as driver; see Rule 3.2
—	transaction.uta_entity_id	Hardcoded 1 (PoC artifact)
billing_item.department_id	transaction.department_id	Copied as-is
billing_item.client_id	transaction.client_id	AR side only; Trust side is null

---

4.5 Payment Item to Transaction (PO Job)

Source Table.Field	Target Table.Field	Transform
—	transaction.class_cd	Constant 'CASH'
—	transaction.source_cd	Constant 'PO'
payment_item.payment_item_id	transaction.source_id	Copied as-is
billing_item.payment_term_ref or revenue_item.sales_item_ref	transaction.source_ref	First payment_term_ref if single; first sales_item_ref if multiple
revenue_item.sales_item_ref	transaction.rev_ref	First available
—	transaction.account_id	Trust side: 2; Bank side: bank_account.uta_account_id (default 7)
—	transaction.type_cd	Trust side: 'D'; Bank side: 'C'
payment_item.payment_item_amt	transaction.trans_amt	Trust side: amount; Bank side: amount * -1
payment_item.payment_item_currency_cd	transaction.trans_currency_cd	Copied as-is; default 'USD'
payment_item.payment_date	transaction.transaction_ref_dt	Copied as-is
Computed	transaction.posting_dt	See Rule 3.2
payment_item.uta_entity_id	transaction.uta_entity_id	Copied as-is
payment_item.department_id	transaction.department_id	Copied as-is
payment_item.client_id	transaction.client_id	Copied as-is

---

5. Cross-References

Document	Relationship
Accounting Data Model	Schema definitions for transaction, account, fiscal_period, accounting_job_execution_history — source of truth for all field types, constraints, and status codes referenced in this document
Accounting Queries	Read operations, search queries, and aggregation logic for the accounting domain
Worksheets Data Model	cash_receipt_worksheet.posting_status_cd and cash_receipt_application records are the source for the APP job; worksheet status (A, R) determines when applications are eligible for GL posting
Cash Receipts Data Model	cash_receipt.posting_status_cd is updated by the CR job; cash_receipt.deposit_date is the driver date for CR transaction posting
Settlements Data Model	payment_item.posting_status_cd is updated by the PO job; settlement chain joins are required to resolve bank_account.uta_account_id for PO transactions
Billing Items Data Model	billing_item_detail.posting_status_cd is updated by the BILL job; revenue_item_schedule.revenue_item_posting_status_cd is updated by the REV job

Key Upstream Entities

Source Entity	Posting Status Field	Job That Posts It
revenue_item_schedule	revenue_item_posting_status_cd	REV
billing_item_detail	posting_status_cd	BILL
cash_receipt	posting_status_cd	CR
cash_receipt_worksheet	posting_status_cd	APP
payment_item	posting_status_cd	PO

GL Status Lifecycle on transaction

gl_status_cd	Meaning	When Set
U	Unposted	At transaction creation. Every new transaction starts here.
P	Posted	After successful transmission to the enterprise GL system.
X	Excluded	Manually excluded from GL transmission.
F	Failed	GL transmission attempted but failed.

NOTE

The PoC creates all transactions with gl_status_cd = 'U' and never advances them to P. The GL transmission pipeline (extracting U records, transmitting to the enterprise GL, and updating to P or F) is outside the scope of the PoC and must be implemented in the production system.

Source Code Lookup (source_cd) Reference

source_cd	Full Name	Source Entity	class_cd Used
REV	Revenue Job	revenue_item_schedule	REV
BILL	Billing Job	billing_item_detail	AR
CR	Cash Receipt	cash_receipt	CASH
APP	Cash Application	cash_receipt_application	CASH, AR
PO	Payouts	payment_item	CASH
FX	FX Adjustment	TBD	FX
TRUE	AR True-Up	Computed from transaction	AR, REV
CL	Client Ledger	cash_receipt_client_ledger	TBD