Client Framework

Appearance

Assignments Queries ​

1. Executive Summary ​

This document catalogs the query operations that power the assignments domain. The assignments domain supports two categories of data access:

  1. CRUD and lifecycle queries — creating, reading, updating, and deactivating assignment and history records. These operate directly on the assignment and assignment_history tables, joined to the users table for display-name enrichment.

  2. Analytical and gap-detection queries — finding unassigned entities, computing responsibility coverage across the hierarchy, resolving the responsible user for a given entity, and building the full responsibility chain. These are read-heavy operations that join the assignment tables against the broader financial data model (billing_item, billing_item_detail, deal, party, department, revenue_items, cash_receipt, cash_receipt_split, cash_receipt_worksheet, cash_receipt_application, payment_item, and cash_receipt_reference).

The most architecturally significant pattern in this domain is the hierarchy resolution query, which walks from the most specific entity level (Level 4: Sales Item / Payment Term) to the broadest (Level 1: Department), returning the first active responsibility match. This pattern is expressed both as an iterative per-level lookup (service-layer resolution) and as a set-based CTE computation (unassigned-entity gap detection).

Tables covered: assignment, assignment_history

Data model reference: Assignments Data Model

2. Key Queries ​

2.1 Assignment CRUD Lookups ​

2.1.1 Get Assignment by ID ​

Operation: getAssignmentById

Input Parameters:

  • assignmentId: UUID (required) — assignment.assignment_id

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignment———Base record lookup; no joins needed

Filters:

  • assignment.assignment_id = :assignmentId

Computed Values:

  • None

Returns: One assignment row with all fields, or null if no record matches the given assignment_id.

2.1.2 Get Assignment Display by ID ​

Operation: getAssignmentDisplayById

Input Parameters:

  • assignmentId: UUID (required) — assignment.assignment_id

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignmentLEFTusersassignment.assigned_to_user_id = users.user_idEnrich with assignee display name and email

Filters:

  • assignment.assignment_id = :assignmentId

Computed Values:

  • assigned_to_user_name: users.first_name || ' ' || users.last_name — full name of the assigned user

Returns: One enriched assignment display row including all assignment fields plus assigned_to_user_name and assigned_to_user_email, or null if not found.

2.1.3 Get Assignments for User ​

Operation: getAssignmentsForUser

Input Parameters:

  • userId: Integer (required) — assignment.assigned_to_user_id
  • assignmentTypeCd: VARCHAR (optional) — assignment.assignment_type_cd; filter to TASK or RESPONSIBILITY
  • taskStatusCd: VARCHAR (optional) — assignment.task_status_cd; filter to OPEN, WORKING, WAITING, COMPLETE, or CANCELLED
  • isActiveInd: Boolean (optional) — assignment.is_active_ind

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignmentLEFTusersassignment.assigned_to_user_id = users.user_idEnrich with assignee display name and email

Filters:

  • assignment.assigned_to_user_id = :userId — always applied
  • If assignmentTypeCd provided: assignment.assignment_type_cd = :assignmentTypeCd
  • If taskStatusCd provided: assignment.task_status_cd = :taskStatusCd
  • If isActiveInd provided: assignment.is_active_ind = :isActiveInd

Computed Values:

  • assigned_to_user_name: users.first_name || ' ' || users.last_name — full name of the assigned user

Returns: Array of enriched assignment display rows for the given user, ordered by assignment.created_dt descending.

2.1.4 Get Assignments for Entity ​

Operation: getAssignmentsForEntity

Input Parameters:

  • entityTypeCd: VARCHAR (required) — assignment.entity_type_cd; e.g., DEAL, CLIENT, CASH_RECEIPT
  • entityId: Integer (conditional) — assignment.entity_id; required for integer-keyed entity types
  • entityReference: VARCHAR (conditional) — assignment.entity_reference; required for string-referenced entity types
  • metaDataTypeCd: VARCHAR (conditional) — assignment.meta_data_type_cd; required when entity_type_cd = META_DATA_PAIR
  • metaDataValue: VARCHAR (conditional) — assignment.meta_data_value; required when entity_type_cd = META_DATA_PAIR

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignmentLEFTusersassignment.assigned_to_user_id = users.user_idEnrich with assignee display name and email

Filters:

  • assignment.entity_type_cd = :entityTypeCd — always applied
  • If entityId provided: assignment.entity_id = :entityId
  • If entityReference provided: assignment.entity_reference = :entityReference
  • If metaDataTypeCd provided: assignment.meta_data_type_cd = :metaDataTypeCd
  • If metaDataValue provided: assignment.meta_data_value = :metaDataValue

Computed Values:

  • assigned_to_user_name: users.first_name || ' ' || users.last_name — full name of the assigned user

Returns: Array of enriched assignment display rows for the specified entity (all assignment types), ordered by assignment.created_dt descending.

2.2 Responsibility Lookup ​

2.2.1 Find Active Responsibility ​

Operation: findActiveResponsibility

Input Parameters:

  • entityTypeCd: VARCHAR (required) — assignment.entity_type_cd
  • entityId: Integer (conditional) — assignment.entity_id; for integer-keyed entity types
  • entityReference: VARCHAR (conditional) — assignment.entity_reference; for string-referenced entity types
  • metaDataTypeCd: VARCHAR (conditional) — assignment.meta_data_type_cd; for META_DATA_PAIR entities
  • metaDataValue: VARCHAR (conditional) — assignment.meta_data_value; for META_DATA_PAIR entities

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignmentLEFTusersassignment.assigned_to_user_id = users.user_idEnrich with assignee display name and email

Filters:

  • assignment.entity_type_cd = :entityTypeCd — always applied
  • If entityId provided: assignment.entity_id = :entityId
  • If entityReference provided: assignment.entity_reference = :entityReference
  • If metaDataTypeCd provided: assignment.meta_data_type_cd = :metaDataTypeCd
  • If metaDataValue provided: assignment.meta_data_value = :metaDataValue
  • assignment.assignment_type_cd = 'RESPONSIBILITY' — always applied
  • assignment.is_active_ind = true — always applied

Computed Values:

  • assigned_to_user_name: users.first_name || ' ' || users.last_name — full name of the assigned user

Returns: A single enriched assignment display row for the active responsibility at the given entity, or null if no active responsibility exists.

2.3 Sibling Task Lookup ​

2.3.1 Find Sibling Tasks ​

Operation: findSiblingTasks

Input Parameters:

  • entityTypeCd: VARCHAR (required) — assignment.entity_type_cd
  • entityId: Integer (conditional) — assignment.entity_id; for integer-keyed entity types
  • entityReference: VARCHAR (conditional) — assignment.entity_reference; for string-referenced entity types
  • excludeAssignmentId: UUID (required) — assignment.assignment_id; the assignment to exclude (typically the one just completed)

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignment———Base record lookup; no joins needed

Filters:

  • assignment.entity_type_cd = :entityTypeCd — always applied
  • If entityId provided: assignment.entity_id = :entityId
  • If entityReference provided: assignment.entity_reference = :entityReference
  • assignment.assignment_type_cd = 'TASK' — always applied
  • assignment.assignment_id != :excludeAssignmentId — always applied

Computed Values:

  • None

Returns: Array of assignment rows (without user enrichment) for all task assignments on the same entity, excluding the specified assignment; the caller filters further by checking whether each sibling's task_status_cd permits a transition to CANCELLED.

2.4 Hierarchy Resolution ​

2.4.1 Hierarchy Resolution (Walk-Up Query) ​

Operation: resolveResponsibleUser

Input Parameters:

  • salesItemRef: VARCHAR (optional) — assignment.entity_reference at SALES_ITEM level; Level 4
  • paymentTermRef: VARCHAR (optional) — assignment.entity_reference at PAYMENT_TERM level; Level 4
  • metaData: Array (optional) — array of {typeCd, value} pairs matched against assignment.meta_data_type_cd + assignment.meta_data_value; Level 3
  • dealReference: VARCHAR (optional) — assignment.entity_reference at DEAL level; Level 3
  • clientId: Integer (optional) — assignment.entity_id at CLIENT level; Level 2
  • buyerId: Integer (optional) — assignment.entity_id at BUYER level; Level 2
  • departmentId: Integer (optional) — assignment.entity_id at DEPARTMENT level; Level 1

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignmentLEFTusersassignment.assigned_to_user_id = users.user_idEnrich matched result with assignee name

Filters:

  • Per iteration: assignment.entity_type_cd = level-specific entity type code
  • Per iteration: entity key filter (entity_id, entity_reference, or meta_data_type_cd + meta_data_value) matching the context value for that level
  • assignment.assignment_type_cd = 'RESPONSIBILITY' — applied at every level
  • assignment.is_active_ind = true — applied at every level

NOTE

This is not a single SQL statement. It is a structured sequence of up to seven findActiveResponsibility calls (see Section 2.2.1), each targeting a different hierarchy level in order. The algorithm short-circuits on the first match and returns immediately without checking lower levels.

Resolution order: Level 4 SALES_ITEM → Level 4 PAYMENT_TERM → Level 3 META_DATA_PAIR (one call per item in the array) → Level 3 DEAL → Level 2 CLIENT → Level 2 BUYER → Level 1 DEPARTMENT.

Computed Values:

  • assigned_to_user_name: users.first_name || ' ' || users.last_name — from the matched assignment record

Returns: One resolution record containing the matched user's assigned_to_user_id, assigned_to_user_name, resolved_from_entity_type_cd, resolved_from_level (1–4), and assignment_id, or null/empty if no active responsibility was found at any level.

2.4.2 Responsibility Chain ​

Operation: buildResponsibilityChain

Input Parameters:

  • entityTypeCd: VARCHAR (required) — the entity type to start resolution from (e.g., SALES_ITEM, DEAL, CLIENT, DEPARTMENT)
  • entityId: Integer (conditional) — assignment.entity_id for integer-keyed starting entities
  • entityReference: VARCHAR (conditional) — assignment.entity_reference for string-referenced starting entities

Tables & Joins:

Phase 1 — parent entity resolution (tables queried depend on the starting entity type):

Base TableJoinJoined TableConditionPurpose
department———Starting entity or ancestor lookup
party——party.party_id = client or buyer IDResolve display name for CLIENT/BUYER levels
billing_item——billing_item.client_id or buyer_idResolve parent entities from billing context
deal——deal.deal_id = billing item dealResolve deal-level ancestor
revenue_items——revenue_items.revenue_item_idResolve sales-item-level ancestor

Phase 2 — per-level responsibility lookup: calls findActiveResponsibility (see Section 2.2.1) once per resolved ancestor level; no additional joins beyond those used in that query.

Filters:

  • Phase 1: entity-type-specific filters to resolve the starting entity and all ancestor entities
  • Phase 2: same filters as findActiveResponsibility applied once per hierarchy level; does NOT short-circuit — all levels are queried regardless of match

Computed Values:

  • effective_user_id: the assigned_to_user_id from the most specific level that has an active assignment
  • effective_user_name: display name of the effective responsible user
  • effective_level: integer (1–4) indicating which hierarchy level produced the effective assignment
  • effective_entity_type_cd: the entity_type_cd of the effective assignment

Returns: A chain result containing an ordered array of level records (Level 1 broadest to Level 4 most specific), each with level, entity_type_cd, entity key, entity_label, the assignment record if one exists, and an is_selected_level flag; plus top-level effective_user_id, effective_user_name, effective_level, and effective_entity_type_cd.

2.5 Assignment History ​

2.5.1 Get History for Assignment ​

Operation: getHistoryForAssignment

Input Parameters:

  • assignmentId: UUID (required) — assignment_history.assignment_id

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
assignment_historyLEFTusers (as from_user)assignment_history.from_user_id = from_user.user_idResolve display name of previous assignee
assignment_historyLEFTusers (as to_user)assignment_history.to_user_id = to_user.user_idResolve display name of new assignee
assignment_historyLEFTusers (as action_user)assignment_history.action_by_user_id = action_user.user_idResolve display name of acting user

Filters:

  • assignment_history.assignment_id = :assignmentId

Computed Values:

  • from_user_name: from_user.first_name || ' ' || from_user.last_name — display name of the previous assignee
  • to_user_name: to_user.first_name || ' ' || to_user.last_name — display name of the new assignee
  • action_by_user_name: action_user.first_name || ' ' || action_user.last_name — display name of the person who performed the action

Returns: Array of history entry rows for the given assignment ordered by assignment_history.action_dt descending, each including assignment_history_id, assignment_id, action_cd, from_user_id, to_user_id, from_status_cd, to_status_cd, comment_text, action_by_user_id, action_dt, and the three computed display name fields.

2.6 Gap Detection ​

2.6.1 Unassigned Entities by Type ​

Operation: getUnassignedEntitiesByType

Input Parameters:

  • entityTypeCd: VARCHAR (required) — which entity type to query; determines the query structure
  • departmentId: Integer (optional) — restrict responsibility-type results to a specific department.department_id
  • coverageLevel: Integer (optional) — filter on the nearest ancestor assignment level (0 = fully uncovered, 1 = department only, etc.)

Tables & Joins:

The join structure varies by entityTypeCd. All variants share a LEFT JOIN anti-pattern against assignment to identify entities with no matching active assignment.

Responsibility entity types (Level 1–4) additionally use a common CTE (open_rev_details) and a set of pre-computed assignment lookup CTEs:

Base TableJoinJoined TableConditionPurpose
billing_itemINNERbilling_item_detailbilling_item_detail.billing_item_id = billing_item.billing_item_id AND billing_item_detail.billing_item_detail_type_cd = 'REV'Build open REV details CTE; REV-only
billing_itemLEFTcash_receipt_application (subquery)Sum of applied amounts per detail on approved worksheetsREV balance calculation
billing_itemLEFTcash_receipt_application_deduction (subquery)Sum of deduction amounts per detail on approved worksheetsREV balance calculation
cash_receipt_worksheet——worksheet_status_cd IN ('A', 'S') AND current_item_ind = trueScope balance calc to approved/submitted worksheets
assignment——Per entity type: entity_type_cd, entity key, assignment_type_cd = 'RESPONSIBILITY', is_active_ind = trueAssignment lookup CTEs (one per hierarchy level)
Entity source tableLEFTassignmentAnti-join condition (see Filters)Identify entities without active assignment

Task entity types (CASH_RECEIPT, CASH_RECEIPT_SPLIT, PAYMENT) additionally join:

Base TableJoinJoined TableConditionPurpose
cash_receiptINNERcash_receipt_splitcash_receipt_split.cash_receipt_id = cash_receipt.cash_receipt_id AND split_status_cd != 'V'Receipt/split entity source
cash_receipt_splitLEFTcash_receipt_worksheetcash_receipt_worksheet.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_id AND current_item_ind = trueNeeds-work determination
cash_receipt_splitLEFTcash_receipt_referencecash_receipt_reference.cash_receipt_split_id = cash_receipt_split.cash_receipt_split_idReference-based coverage CTE
payment_itemLEFTpartyparty.party_id = payment_item.party_idPayment recipient display name
payment_itemLEFTdealdeal.deal_id = payment_item.deal_idPayment deal display name

Filters:

  • For responsibility types: billing_item.current_item_ind = true AND billing_item.open_item_ind = true — scope to open billing items
  • For responsibility types: billing_item_detail.billing_item_detail_type_cd = 'REV' — REV details only
  • Anti-join filter: assignment.assignment_id IS NULL — entities with no active assignment
  • For task type CASH_RECEIPT: cash_receipt.posting_status_cd != 'V'
  • For task types (CASH_RECEIPT, CASH_RECEIPT_SPLIT, PAYMENT): active task filter excludes assignments with task_status_cd IN ('COMPLETE', 'CANCELLED')
  • For task type PAYMENT: payment_item.payment_execution_status_cd IN ('WAITING', 'PENDING', 'FAILED') or null
  • If departmentId provided: restricts responsibility results to the given department
  • If coverageLevel provided: filters nearest_assignment_level = :coverageLevel

Computed Values:

  • rev_balance: billing_item_detail.billing_item_detail_total_amt - SUM(deduction_amt_applied) - SUM(cash_receipt_amt_applied) on approved/submitted worksheets — REV balance per open billing item detail
  • open_receivable_count: COUNT(DISTINCT billing_item_id) — number of distinct open billing items for this entity
  • open_receivable_amount: SUM(rev_balance) — total REV balance across all open billing items
  • nearest_assignment_level: CASE expression checking ancestor assignment lookup CTEs from most specific to broadest — integer coverage level (0–3) of the nearest assigned ancestor
  • nearest_assignment_entity_type_cd: entity type of the nearest assigned ancestor
  • nearest_assigned_user_name: display name of the person assigned at the nearest ancestor level
  • For receipt/split: worst_worksheet_status_rank: integer rank mapping of worst worksheet status across splits

Returns: Array of up to 200 unassigned entity rows ordered by open_receivable_amount descending (responsibility types) or by entity date descending (task types), each row including entity key and display name, department context, open receivable count and amount, nearest ancestor coverage level and user, and for task types additionally the entity amount, applied amount, processing status, and entity date.

Variant: Department (Level 1) ​

Operation: getUnassignedDepartments

Additional joins: department (base) INNER JOIN open_rev_details CTE on department_id. Anti-join: assignment LEFT JOIN on entity_type_cd = 'DEPARTMENT' AND entity_id = department.department_id.

Coverage always 0 (departments have no ancestors). Grouped by department.department_id, department.department_name.

All other aspects identical to 2.6.1.

Variant: Client (Level 2) ​

Operation: getUnassignedClients

Additional joins: open_rev_details CTE (base) INNER JOIN party on client_id; LEFT JOIN department; LEFT JOIN assigned_depts CTE on department_id. Anti-join: assignment on entity_type_cd = 'CLIENT' AND entity_id = party.party_id.

Coverage: 1 if assigned_depts match exists, else 0. Grouped by party.party_id, party.display_name.

All other aspects identical to 2.6.1.

Variant: Buyer (Level 2) ​

Operation: getUnassignedBuyers

Same structure as Client variant but joins on open_rev_details.buyer_id and anti-join uses entity_type_cd = 'BUYER'.

All other aspects identical to 2.6.1.

Variant: Deal (Level 3) ​

Operation: getUnassignedDeals

Additional joins: open_rev_details CTE (base) INNER JOIN deal on deal_id; LEFT JOIN department; LEFT JOIN assigned_clients CTE on client_id; LEFT JOIN assigned_buyers CTE on buyer_id; LEFT JOIN assigned_depts CTE on department_id. Anti-join: assignment on entity_type_cd = 'DEAL' AND entity_reference = deal.deal_reference.

Coverage: 2 if client or buyer match, 1 if department match, else 0. Grouped by deal.deal_id, deal.deal_reference, deal.deal_name.

All other aspects identical to 2.6.1.

Variant: Sales Item (Level 4) ​

Operation: getUnassignedSalesItems

Additional joins: open_rev_details CTE (base) INNER JOIN revenue_items on revenue_item_id AND current_item_ind = true; LEFT JOIN department; LEFT JOIN deal; LEFT JOIN assigned_deals CTE on deal_reference; LEFT JOIN assigned_clients CTE on client_id; LEFT JOIN assigned_buyers CTE on buyer_id; LEFT JOIN assigned_depts CTE on department_id. Anti-join: assignment on entity_type_cd = 'SALES_ITEM' AND entity_reference = revenue_items.sales_item_ref.

Coverage: 3 if deal match, 2 if client/buyer match, 1 if department match, else 0. Grouped by revenue_items.revenue_item_id, revenue_items.sales_item_ref, revenue_items.revenue_item_name.

All other aspects identical to 2.6.1.

Variant: Payment Term (Level 4) ​

Operation: getUnassignedPaymentTerms

Same structure as Sales Item variant but uses billing_item.payment_term_ref instead of revenue_items.sales_item_ref, and anti-join uses entity_type_cd = 'PAYMENT_TERM' AND entity_reference = billing_item.payment_term_ref.

All other aspects identical to 2.6.1.

Variant: Cash Receipt (Task) ​

Operation: getUnassignedCashReceipts

Additional joins: cash_receipt (base, posting_status_cd != 'V') INNER JOIN cash_receipt_split; LEFT JOIN cash_receipt_worksheet; LEFT JOIN cash_receipt_application (lateral subquery for sum of applied amounts); LEFT JOIN ref_coverage CTE (reference-based coverage derived from cash_receipt_reference joined against assignment lookup CTEs). Anti-join: assignment on entity_type_cd = 'CASH_RECEIPT' AND entity_id = cash_receipt.cash_receipt_id AND assignment_type_cd = 'TASK' AND is_active_ind = true AND task_status_cd NOT IN ('COMPLETE', 'CANCELLED').

"Needs work" HAVING filter: worst worksheet status rank < 4 OR |total_applied - net_receipt_amt| > 0.005.

All other aspects identical to 2.6.1.

Variant: Cash Receipt Split (Task) ​

Operation: getUnassignedCashReceiptSplits

Same pattern as Cash Receipt variant at the split level, with an additional assigned_receipt_tasks CTE to inherit receipt-level task coverage. Split coverage = GREATEST(ref_coverage_level, receipt_task_inheritance_level). "Needs work" filter: worksheet status is null, or not in ('A', 'R'), or |applied - split_amt| > 0.005.

All other aspects identical to 2.6.1.

Variant: Payment (Task) ​

Operation: getUnassignedPayments

Additional joins: payment_item (base) LEFT JOIN party on payment_item.party_id; LEFT JOIN deal on payment_item.deal_id; LEFT JOIN department; LEFT JOIN assignment lookup CTEs on direct FK columns (deal_id, client_id, buyer_id, department_id). Anti-join: assignment on entity_type_cd = 'PAYMENT' AND entity_id = payment_item.payment_item_id AND assignment_type_cd = 'TASK' AND is_active_ind = true AND task_status_cd NOT IN ('COMPLETE', 'CANCELLED').

"Needs work" filter: payment_item.payment_execution_status_cd IN ('WAITING', 'PENDING', 'FAILED') or null.

All other aspects identical to 2.6.1.

2.6.2 Unassigned Summary (Count by Entity Type) ​

Operation: getUnassignedSummary

Input Parameters:

  • None

Tables & Joins:

Base TableJoinJoined TableConditionPurpose
departmentLEFTassignmententity_type_cd = 'DEPARTMENT', entity_id, RESPONSIBILITY, is_active_ind = trueDepartment unassigned count
billing_itemLEFTassignmententity_type_cd = 'CLIENT', entity_id, RESPONSIBILITY, is_active_ind = trueClient unassigned count
billing_itemLEFTassignmententity_type_cd = 'BUYER', entity_id, RESPONSIBILITY, is_active_ind = trueBuyer unassigned count
billing_item + dealLEFTassignmententity_type_cd = 'DEAL', entity_reference, RESPONSIBILITY, is_active_ind = trueDeal unassigned count
billing_item + revenue_itemsLEFTassignmententity_type_cd = 'SALES_ITEM', entity_reference, RESPONSIBILITY, is_active_ind = trueSales item unassigned count
billing_itemLEFTassignmententity_type_cd = 'PAYMENT_TERM', entity_reference, RESPONSIBILITY, is_active_ind = truePayment term unassigned count
cash_receipt + cash_receipt_split + cash_receipt_worksheetLEFTassignmententity_type_cd = 'CASH_RECEIPT', TASK, active, not complete/cancelledReceipt unassigned count
cash_receipt_split + cash_receipt_worksheetLEFTassignmententity_type_cd = 'CASH_RECEIPT_SPLIT', TASK, active, not complete/cancelledSplit unassigned count
payment_itemLEFTassignmententity_type_cd = 'PAYMENT', TASK, active, not complete/cancelledPayment unassigned count

Filters:

  • Anti-join filter on each block: assignment.assignment_id IS NULL
  • For responsibility types: open billing items (current_item_ind = true, open_item_ind = true)
  • For CASH_RECEIPT: posting_status_cd != 'V'; not all splits fully approved or balance mismatch
  • For CASH_RECEIPT_SPLIT: worksheet not approved/returned or balance mismatch
  • For PAYMENT: payment_execution_status_cd IN ('WAITING', 'PENDING', 'FAILED') or null

Computed Values:

  • count: COUNT(DISTINCT entity_id or entity_reference) — number of unassigned entities per block

Returns: Array of nine summary rows (one per entity type), each with entity_type_cd and count; entity types with no unassigned entities are backfilled with a count of 0.

3. Calculations & Formulas ​

3.1 REV Balance ​

Used by all responsibility-type unassigned entity queries to determine which billing items still have open receivable amounts.

text
rev_balance = billing_item_detail.billing_item_detail_total_amt
            - SUM(cash_receipt_application_deduction.deduction_amt_applied)   [on approved/submitted worksheets]
            - SUM(cash_receipt_application.cash_receipt_amt_applied)          [on approved/submitted worksheets]
  • Source: billing_item_detail.billing_item_detail_total_amt, cash_receipt_application.cash_receipt_amt_applied, cash_receipt_application_deduction.deduction_amt_applied
  • Scope: Only REV-type billing item details (billing_item_detail.billing_item_detail_type_cd = 'REV').
  • Worksheet filter: Only applications on approved (A) or submitted (S) worksheets where cash_receipt_worksheet.current_item_ind = true are counted.
  • Precision: decimal(15,2)
  • Interpretation: A positive rev_balance means the receivable is still partially or fully open.

3.2 Ancestor Coverage Level ​

For unassigned entities at Levels 2–4, the system computes the nearest ancestor that has an active responsibility assignment. This is expressed as an integer using a CASE expression that checks ancestor assignment lookup CTEs from most specific to broadest.

text
nearest_assignment_level =
  CASE
    WHEN assigned_deals match exists       THEN 3
    WHEN assigned_clients match exists
      OR assigned_buyers match exists      THEN 2
    WHEN assigned_depts match exists       THEN 1
    ELSE 0
  END
  • Source: Pre-computed assignment lookup CTEs (assigned_deals, assigned_clients, assigned_buyers, assigned_depts) filtered on assignment_type_cd = 'RESPONSIBILITY' and is_active_ind = true
  • Values: 0 = no ancestor has an active responsibility (fully uncovered); 1 = department-level responsibility exists; 2 = client or buyer-level responsibility exists; 3 = deal-level responsibility exists

3.3 Receipt/Split "Needs Work" Determination ​

For cash receipts, "needs work" is defined as:

text
(worst_worksheet_status_rank < 4)
OR (ABS(total_applied_amt - net_receipt_amt) > 0.005)

Where worst_worksheet_status_rank maps worksheet statuses to integers: A = 4, T = 3, P = 2, D = 1, null/other = 0.

For splits:

text
worksheet_status IS NULL
OR worksheet_status NOT IN ('A', 'R')
OR ABS(applied_amt - split_amt) > 0.005
  • Source: cash_receipt_worksheet.worksheet_status_cd, cash_receipt_application.cash_receipt_amt_applied, cash_receipt.net_receipt_amt, cash_receipt_split.split_amt
  • Tolerance: 0.005 — aligns with the system-wide balance comparison epsilon
  • Example: A receipt with one split whose worksheet is in status P (rank 2) satisfies worst_worksheet_status_rank < 4 and therefore "needs work."

3.4 Task Status Transition Validation ​

The service layer enforces forward-only task status transitions using a lookup table. This is not a database constraint but a service-layer validation that runs before any status update.

text
OPEN     → WORKING, CANCELLED
WORKING  → WAITING, COMPLETE, CANCELLED
WAITING  → WORKING, CANCELLED
COMPLETE → (none — terminal)
CANCELLED → (none — terminal)
  • Source: assignment.task_status_cd
  • Enforcement: Any transition not listed above is rejected before writing to the database.
  • Example: Attempting to transition COMPLETE → OPEN is rejected with an error.

4. Cross-References ​

DocumentRelationship
Assignments Data ModelFull schema definitions, status lifecycles, validation rules, and code master values for the assignment and assignment_history tables queried throughout this document.
Cash Receipts Data ModelUnassigned cash receipt and cash receipt split queries join against cash_receipt, cash_receipt_split, cash_receipt_worksheet, cash_receipt_application, and cash_receipt_reference tables defined in this document.
Billing Items Data ModelThe open_rev_details CTE used by all responsibility-type gap-detection queries operates on billing_item and billing_item_detail tables; REV balance calculation is derived from these tables.
Worksheets Data ModelWorksheet status codes (D, P, T, A, R) and the current_item_ind flag are used in REV balance calculations and "needs work" determination for receipts and splits.
Settlements Data ModelPayment origin type (SETTLEMENT) in the unassigned payment query is derived from payment_item.payment_item_type_cd = 'S' combined with the presence of a participant_settlement_item_id.
Deals, Sales Items & Payment Terms Data ModelDeal entity resolution in the responsibility chain and gap-detection queries joins deal by deal_id and matches assignment records by deal_reference; revenue_items is used for sales item entity resolution.
Parties Data ModelClient and buyer resolution uses party.party_id and party.display_name; the CLIENT and BUYER entity types in the assignment hierarchy reference party.party_id.

Confidential. For internal use only.

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