Billing Items Procedures

1. Executive Summary

Purpose

This document specifies every data mutation operation in the billing items domain. These procedures describe how billing items, their REV/PAY detail pairs, deductions, revenue items, and revenue schedules are created, updated, reversed, and posted to the general ledger. The billing items domain sits at the boundary between the deal/revenue world (upstream) and the cash processing world (downstream): data flows in from the deal engine as sales items and payment terms, gets transformed into revenue items and billing items, and ultimately becomes the receivables against which cash is applied on worksheets.

Scope

Covered:

• billing_item — header record for each expected payment from a buyer; created, revised, reversed, and deactivated by these procedures
• billing_item_detail — REV/PAY detail pairs; always created atomically two at a time alongside the parent header
• billing_item_deduction — deductions applied against a detail; created during billing item creation and managed in-place; copied forward during revenue sync rebilling
• revenue_items — aggregated revenue expectation from a deal; parent of billing items; follows same immutability-by-reversal pattern
• revenue_item_schedules — periodic recognition schedule entries under a revenue item; created during revenue item creation and posted to GL by the revenue recognition job

Not covered (documented separately):

• cash_receipt_application — downstream cash application against billing item details; see Cash Receipts Procedures
• write_off_packet / packet_receivable — write-off approval workflow; see Write-Offs Procedures
• participant_settlement — settlement of PAY amounts among a client's party; see Settlements Procedures
• payment_item — outbound payment lifecycle; see Settlements Procedures — Payment Items

Key mutation patterns in this domain:

• Immutability via reversal: Billing items and revenue items are never edited in place when financial amounts change. Instead, the original is deactivated (current_item_ind = false), a reversal record with negated amounts is created, and a new current record with the updated amounts replaces it. This preserves a complete audit trail.
• Atomic REV/PAY pair creation: Every billing item creation or reversal produces exactly two billing_item_detail rows (one REV, one PAY) within a single transaction.
• Two-way matching on sync: When payment terms change upstream, each incoming payment term is matched to an existing billing item by payment_term_ref. Only changed items are reversed and replaced; unchanged items are left untouched.
• Batch GL posting via accounting jobs: Revenue recognition and billing posting are executed as batch jobs that scan for eligible unposted records, create GL transactions, and mark the source records as posted.

---

2. Key Procedures

2.1 Create Revenue Item from Sales Item

Operation: createRevenueItemFromSalesItem

Trigger: Deal engine sync processes a new sales item (no existing revenue_items record with the same sales_item_ref).

Input Parameters:

• salesItemRef: String — external reference for correlation with the deal engine sales item
• utaEntityId: Integer — FK to uta_entity; the UTA legal entity
• agentGroupId: Integer — agent group identifier
• dealId: Integer — FK to deal; the parent deal
• clientEntityId: Integer — FK to party; the client party
• contractedPartyId: Integer — FK to party; contracted party (may differ from client for loan-outs)
• buyerEntityId: Integer — FK to party; the buyer party
• departmentId: Integer — FK to department
• currencyCd: String — revenue_items.currency_cd
• grossAmt: Decimal — total gross revenue; revenue_items.revenue_item_gross_amt
• utaCommissionPerc: Decimal — commission percentage; revenue_items.revenue_item_commission_perc
• utaCommissionAmt: Decimal — commission amount; revenue_items.revenue_item_commission_amt
• utaCommissionType: String — 'FLAT' or percentage-based; determines revenue_items.revenue_item_commission_flat_ind
• revenueStartDt: Date — revenue_items.revenue_item_start_dt
• revenueEndDt: Date — revenue_items.revenue_item_end_dt
• revRecStyleCd: String — recognition style I, M, or C; revenue_items.revenue_item_rec_style_cd
• actorId: String — identity of the user or system performing the operation

Creates a new revenue_items record and its associated revenue_item_schedules from an incoming deal engine sales item. Billing items are not created by this procedure — they are created in a separate step.

Step 1. Map Sales Item Fields to Revenue Item

• Source: Input parameters from the deal engine sync payload.
• Action: Construct the new revenue_items record for insertion.
• Logic:
  • Set revenue_item_commission_flat_ind = (utaCommissionType == 'FLAT').
  • Set current_item_ind = true.
  • Set revenue_item_status_cd from sales item status (default 'U').
  • Set revenue_item_date_status_cd from sales item (default 'U').
  • Copy all other fields directly from input parameters.

Step 2. Insert revenue_items Record

• Source: Mapped record from Step 1.
• Action: INSERT into revenue_items within a transaction.
• Logic:
  • Database generates revenue_item_id via serial PK.

Step 3. Generate Revenue Schedules

• Source: revenue_item_rec_style_cd, revenue_item_start_dt, revenue_item_end_dt, revenue_item_commission_amt from the new record.
• Action: Calculate the set of revenue_item_schedules entries based on recognition style.
• Logic:
  • If 'I' (Immediate): create a single schedule entry with revenue_amt = revenue_item_commission_amt and revenue_dt = revenue_item_start_dt.
  • If 'M' (Monthly): compute daily_rate = commission_amt / total_days. For each calendar month segment in the range, calculate days_in_segment * daily_rate. The last segment absorbs the rounding remainder. Set revenue_dt to the first day of that month segment.
  • If 'C' (Cash): no schedules generated — revenue is recognized when cash arrives.
  • All schedules created with revenue_item_posting_status_cd = 'U' and revenue_item_posting_dt = NULL.

NOTE

Revenue schedule generation uses UTC date arithmetic to avoid timezone-related date boundary issues when computing monthly segments.

Step 4. Insert revenue_item_schedules Records

• Source: Schedule entries calculated in Step 3.
• Action: Batch INSERT into revenue_item_schedules.
• Logic:
  • Each entry carries revenue_item_id from the new revenue item inserted in Step 2.

Side-effects:

• None beyond the inserted records. Billing items are not created by this procedure.

Postconditions:

• One revenue_items record exists with current_item_ind = true and the provided sales_item_ref.
• Zero or more revenue_item_schedules rows exist linked to the new revenue_item_id, depending on revenue_item_rec_style_cd.

---

2.2 Update Revenue Item (Immutability Pattern)

Operation: updateRevenueItem

Trigger: Deal engine sync detects changes to an existing revenue item's financial or date fields.

Input Parameters:

• originalItemId: Integer — PK (revenue_items.revenue_item_id) of the current revenue item being updated
• currentState.revenueItem: Object — changed field values from the sync payload
• currentState.paymentTerms: Array — full set of current payment terms from the sync payload
• actorId: String — identity of the actor

Updates a revenue item by creating a reversal of the original, a new replacement item with the changes applied, and cascading the changes down to billing items. This is the core immutability pattern used throughout the domain.

Step 1. Fetch Original Revenue Item and Schedules

• Source: revenue_items WHERE revenue_item_id = originalItemId.
• Action: Read the full current record including all associated revenue_item_schedules.
• Logic:
  • If record not found, reject the operation with an error.

Step 2. Detect Changes

• Source: Compare each field in currentState.revenueItem against the original revenue_items record.
• Action: Produce a change set of modified fields.
• Logic:
  • Fields compared: revenue_item_name, revenue_item_gross_amt, revenue_item_commission_amt, revenue_item_start_dt, revenue_item_end_dt, revenue_item_commission_perc, revenue_item_rec_style_cd, revenue_item_status_cd, revenue_item_date_status_cd.
  • If no revenue item field changes detected: skip Steps 3–10 and proceed to the conditional branch (billing item sync only).

Step 3. Create Reversal Revenue Item

• Source: Original revenue_items record.
• Action: INSERT into revenue_items a negated copy.
• Logic:
  • Set revenue_item_gross_amt = original.revenue_item_gross_amt * -1.
  • Set revenue_item_commission_amt = original.revenue_item_commission_amt * -1.
  • Set current_item_ind = false.
  • All other fields copied from original.

Step 4. Create Reversal Revenue Schedules

• Source: All revenue_item_schedules rows where revenue_item_id = originalItemId.
• Action: INSERT negated copies into revenue_item_schedules.
• Logic:
  • Set revenue_amt = original.revenue_amt * -1 for each schedule.
  • Set revenue_item_id = the reversal revenue item ID from Step 3.
  • Set revenue_item_posting_status_cd = 'U' and revenue_item_posting_dt = NULL.

Step 5. Create Billing Item Reversals

• Source: All billing_item rows WHERE revenue_item_id = originalItemId AND current_item_ind = true.
• Action: For each current billing item, invoke Create Billing Item Reversal (2.3).
• Logic:
  • Link reversal billing items to the reversal revenue item ID from Step 3.

Step 6. Create New Revenue Item

• Source: Original revenue_items record merged with the changed field values.
• Action: INSERT into revenue_items.
• Logic:
  • Set current_item_ind = true.
  • Original fields overwritten by changed values from currentState.revenueItem.

Step 7. Generate and Insert New Revenue Schedules

• Source: New revenue item from Step 6.
• Action: Apply the same schedule generation logic as Steps 3–4 of Create Revenue Item (2.1), using the new item's data.
• Logic:
  • Insert all generated schedules linked to the new revenue_item_id.

Step 8. Create New Billing Items from Payment Terms

• Source: currentState.paymentTerms from the sync payload and the new revenue item ID from Step 6.
• Action: Invoke Create New Billing Items from Payment Terms — Two-Way Match (2.5).
• Logic:
  • Pass the new revenue_item_id as the link target for newly created billing items.

Step 9. Deactivate Original Revenue Item

• Source: revenue_items WHERE revenue_item_id = originalItemId.
• Action: UPDATE revenue_items SET current_item_ind = false.
• Logic:
  • All other fields remain unchanged.

IMPORTANT

The immutability pattern means that after an update, three revenue item records exist for the sales_item_ref: the deactivated original (current_item_ind = false), the reversal with negated amounts (current_item_ind = false), and the new replacement (current_item_ind = true). Only the replacement is visible in active queries.

Conditional branch — no revenue changes detected (Step 2 produces empty change set): When no revenue item fields changed but payment terms may have changed:

• Fetch current billing items for the original revenue item ID.
• Invoke Create New Billing Items from Payment Terms — Two-Way Match (2.5) using the original revenue item ID (not a new one).
• The two-way match logic handles creating, reversing, and replacing only the billing items whose payment terms actually changed.

Side-effects:

• Original revenue_items record has current_item_ind set to false.
• Each original current billing_item under the original revenue item is deactivated via 2.3.
• Reversal records created for both revenue_items and revenue_item_schedules.

Postconditions:

• The original revenue_items record has current_item_ind = false.
• A reversal revenue_items record exists with negated amounts and current_item_ind = false.
• A new replacement revenue_items record exists with current_item_ind = true and the updated field values.
• Corresponding replacement billing items exist under the new revenue item.

---

2.3 Create Billing Item Reversal

Operation: createBillingItemReversal

Trigger: Called internally during revenue item update (2.2), billing item revision (2.4), and two-way match (2.5).

Input Parameters:

• originalBillingItem: Object — the full billing_item record being reversed, including its details and deductions
• context.createdDt: Timestamp (optional) — timestamp override for audit fields
• actorId: String — identity of the actor

Creates a negated copy of a billing item and all its details and deductions. The reversal billing item is not current and not open — it exists solely as an offsetting audit record.

Step 1. Create Reversal Billing Item Header

• Source: Original billing_item record.
• Action: INSERT into billing_item a copy of the original with modifications.
• Logic:
  • Set current_item_ind = false.
  • Set open_item_ind = false.
  • Set billing_item_status_cd: if original was 'U', reversal gets 'X'; otherwise reversal gets 'U'.
  • All other header fields copied from original.

Step 2. Fetch Original Billing Item Details

• Source: billing_item_detail WHERE billing_item_id = original.billing_item_id.
• Action: Read both the REV and PAY detail records.
• Logic:
  • Both detail types must be present.

Step 3. Create Reversal Details

• Source: Each billing_item_detail row from Step 2 (REV and PAY).
• Action: For each original detail, INSERT a new billing_item_detail row.
• Logic:
  • Set billing_item_id = reversal billing item ID from Step 1.
  • Set billing_item_detail_gross_amt = original.billing_item_detail_gross_amt * -1.
  • Set billing_item_detail_percent = original (unchanged).
  • Set billing_item_detail_amt = original.billing_item_detail_amt * -1.
  • Set billing_item_detail_tax_amt = original.billing_item_detail_tax_amt * -1.
  • Set billing_item_detail_total_amt = original.billing_item_detail_total_amt * -1.
  • Set posting_status_cd = 'U'.
  • Set posting_dt = NULL.

Step 4. Create Reversal Deductions

• Source: billing_item_deduction WHERE billing_item_detail_id = original_detail.billing_item_detail_id (for each detail).
• Action: For each deduction record, INSERT a new billing_item_deduction row.
• Logic:
  • Set billing_item_detail_id = the corresponding reversal detail ID from Step 3.
  • Set billing_item_deduction_amt = original.billing_item_deduction_amt * -1.
  • All other fields (billing_item_deduction_type_cd, billing_item_deduction_update_net_ind, comment) copied from original.

IMPORTANT

The reversal does NOT migrate cash receipt applications. Cash applications remain linked to the original detail IDs. Migration only occurs when a replacement billing item is created (see Revise Billing Item (2.4) and Two-Way Match (2.5)).

Side-effects:

• None. This procedure only creates records; it does not update or delete existing records.

Postconditions:

• One new billing_item row exists with current_item_ind = false and open_item_ind = false.
• Two new billing_item_detail rows (REV and PAY) exist with negated amounts linked to the reversal billing item.
• Negated copies of all original deductions exist linked to the new reversal details.

---

2.4 Revise Billing Item

Operation: reviseBillingItem

Trigger: User directly edits a billing item's amounts, dates, or other fields through the UI (the "Save" action on a billing item form).

Input Parameters:

• originalBillingItemId: Integer — PK (billing_item.billing_item_id) of the billing item to revise
• newValues: Object — new billing item header fields plus revDetail and payDetail data with updated amounts
• actorId: String — identity of the user

Replaces a billing item with updated values. Creates a reversal of the original, creates a new replacement with the provided values, migrates cash receipt applications from the original details to the new details, copies deductions, and recalculates the open item indicator.

Step 1. Fetch Original Billing Item and Details

• Source: billing_item WHERE billing_item_id = originalBillingItemId; billing_item_detail WHERE billing_item_id = originalBillingItemId.
• Action: Read the full billing item and both REV and PAY detail records.
• Logic:
  • If not found, reject the operation.
  • Both REV and PAY details must be present.

Step 2. Create Reversal

• Source: Original billing item and its details and deductions from Step 1.
• Action: Invoke Create Billing Item Reversal (2.3).
• Logic:
  • In this variant, deduction reversal is performed inline within the same transaction context.

Step 3. Create New Billing Item Header

• Source: newValues input plus context fields from the original.
• Action: INSERT into billing_item.
• Logic:
  • Set current_item_ind = true.
  • Set all header fields from newValues.

Step 4. Create New Details and Migrate Applications

• Source: newValues.revDetail and newValues.payDetail; original billing_item_detail IDs from Step 1.
• Action: For each detail type (REV, PAY): INSERT new billing_item_detail, migrate cash applications, and copy deductions.
• Logic:
  • INSERT new billing_item_detail with posting_status_cd = 'U' and posting_dt = NULL.
  • UPDATE cash_receipt_application SET billing_item_detail_id = newDetailId WHERE billing_item_detail_id = originalDetailId.
  • For each billing_item_deduction on the original detail: INSERT a copy linked to the new detail ID.

Step 5. Recalculate Open Item Indicator

• Source: New billing_item_detail records and their associated cash_receipt_application totals.
• Action: Invoke Update Billing Item Open Status (2.9) for the new billing item.
• Logic:
  • Query total cash applied from cash_receipt_application joined to cash_receipt_worksheet with cash_receipt_worksheet_status_cd IN ('A', 'S') and current_item_ind = true.
  • is_open = NOT (ABS(rev_cash_applied - rev_total_amt) < 0.01 AND ABS(pay_cash_applied - pay_total_amt) < 0.01).
  • UPDATE billing_item SET open_item_ind = is_open.

Step 6. Deactivate Original

• Source: billing_item WHERE billing_item_id = originalBillingItemId.
• Action: UPDATE billing_item SET current_item_ind = false.
• Logic:
  • All other fields on the original remain unchanged.

Side-effects:

• All cash_receipt_application records that pointed to the original detail IDs now point to the new detail IDs.
• Original billing_item has current_item_ind = false.

Postconditions:

• The original billing_item has current_item_ind = false.
• A reversal billing_item exists with negated detail amounts and current_item_ind = false.
• A new replacement billing_item exists with current_item_ind = true and the updated field values.
• All prior cash applications reference the new detail IDs.

---

2.5 Create New Billing Items from Payment Terms (Two-Way Match)

Operation: createBillingItemsFromPaymentTermsTwoWayMatch

Trigger: Called during revenue item creation (initial billing), revenue item update (2.2), or payment-term-only changes where no revenue item fields changed.

Input Parameters:

• paymentTerms: Array — full set of current payment terms from the sync payload
• newRevenueItemId: Integer — revenue_items.revenue_item_id to link new billing items to
• originalBillingItems: Array — current billing_item records for comparison; matched by payment_term_ref
• commissionPerc: Decimal — commission rate from the revenue item; used for REV/PAY amount calculation
• context.createdDt: Timestamp (optional) — timestamp override for audit fields
• context.contractedPartyId: Integer — contracted party for tax lookups
• actorId: String — identity of the actor

The primary billing item creation and synchronization procedure. Processes a set of payment terms against existing billing items, using payment_term_ref to match incoming terms to existing items. Creates new billing items for new or changed terms, reverses replaced originals, handles unmatched originals, and migrates cash applications.

Step 1. Process Each Payment Term

• Source: Each entry in the paymentTerms input array.

• Action: For each payment term, calculate amounts, match against originals, and create/reverse as needed.

• Logic:

  1a. Calculate REV and PAY amounts.

  • Determine collection style: if paymentPartyId == buyerId, style is BUYER; otherwise CLIENT. If the matched original has collection_style_override_ind = true, preserve the overridden style from the original.
  • REV: billing_item_detail_gross_amt = grossAmt, billing_item_detail_percent = commissionPerc, billing_item_detail_amt = gross * percent.
  • PAY (BUYER style): billing_item_detail_gross_amt = grossAmt, billing_item_detail_percent = 1 - commissionPerc, billing_item_detail_amt = gross * percent.
  • PAY (CLIENT style): all amounts = 0.

  1b. Match to original billing item.

  • Find the entry in originalBillingItems where billing_item.payment_term_ref = paymentTerm.paymentTermRef.

  1c. If matched — detect changes.

  • Compare: billing_item_name, billing_item_due_dt, billing_item_due_dt_status_cd, collection_party_id (if no override), collection_style_cd (if no override), REV detail gross/amt/percent, PAY detail gross/amt/percent, revenue_item_id.
  • Amount comparison epsilon: 0.005. Percent comparison epsilon: 0.0001.
  • If no changes detected: skip this payment term entirely (original remains active).
  • If changes detected: proceed to reverse and replace.

  1d. If matched with changes — reverse original.

  • Condition: Only if original.revenue_item_id == newRevenueItemId (same revenue context).
  • Action: Invoke Create Billing Item Reversal (2.3). Mark original billing item ID for deactivation.

  1e. Create new billing item header.

  • INSERT into billing_item.
  • Inherit deal_id, uta_entity_id, agent_group_id, buyer_id, client_id, department_id, currency_cd, service_country_cd, service_state_cd from matched original or first original in the set.
  • Set billing_item_status_cd = 'U', current_item_ind = true, open_item_ind = true.
  • Preserve billing_item_aging_dt from matched original if available; otherwise default to due_dt.
  • Tax fields (vat_pass_through_rate, vat_pass_through_amt, expected_invoice_total, tax_calculated_dt, tax_stale_ind) set to NULL initially.

  1f. Create REV and PAY details.

  • INSERT two billing_item_detail records using amounts from step 1a.
  • Set posting_status_cd = 'U' and posting_dt = NULL on both.

  1g. If matched — copy deductions and migrate cash applications.

  • Copy billing_item_deduction records from original REV detail to new REV detail.
  • Copy billing_item_deduction records from original PAY detail to new PAY detail.
  • UPDATE cash_receipt_application SET billing_item_detail_id = newDetailId WHERE billing_item_detail_id = originalDetailId (for both REV and PAY detail IDs).

  1h. Calculate and persist tax deductions.

  • Condition: Tax service available AND contracted party ID resolvable.
  • Delete stale copied tax-auto deductions (those with billing_item_deduction.created_by = 'TAX_AUTO_RECEIVABLE').
  • Invoke Calculate and Persist Tax Deductions (2.13) for the new billing item.

**PoC Artifact:**

The tax deduction recalculation (step 1h) deletes and recreates tax-auto deductions rather than updating in place. A production implementation should consider a more targeted update approach.

Step 2. Handle Unmatched Originals

• Source: Original billing items whose billing_item.payment_term_ref does not appear in the incoming paymentTerms array.
• Action: For each unmatched original, reverse the original, create a zero-amount replacement, and mark original for deactivation.
• Logic:
  • Invoke Create Billing Item Reversal (2.3) for the unmatched original.
  • INSERT a new billing_item linked to newRevenueItemId with zero-amount REV and PAY details.
  • Mark the original billing_item_id for deactivation.

Step 3. Update Open Item Status for New Items

• Source: All newly created billing_item records.
• Action: For each new item, invoke Update Billing Item Open Status (2.9).
• Logic:
  • Sets open_item_ind based on current applied cash totals.

Step 4. Deactivate Replaced Originals

• Source: All original billing_item_id values marked for deactivation in Steps 1d and 2.
• Action: UPDATE billing_item SET current_item_ind = false for each ID in the replaced set.
• Logic:
  • All other fields on deactivated records remain unchanged.

IMPORTANT

The two-way match is critical for correctness. Payment terms that haven't changed produce no mutations. Only changed or new terms create new billing items. Removed terms produce zero-amount replacements. This prevents unnecessary churn of billing item IDs and preserves cash application history.

Side-effects:

• All cash_receipt_application records for matched and changed billing items are re-pointed to new detail IDs.
• Original billing items that were reversed or replaced have current_item_ind = false.
• Tax deductions on PAY details are refreshed for all new items (where tax service is available).

Postconditions:

• Every payment term in the input array has a corresponding billing_item with current_item_ind = true and open_item_ind correctly set.
• Removed payment terms have zero-amount replacement billing items linked to newRevenueItemId.
• Unchanged payment terms retain their original billing items untouched.

---

2.6 Create Billing Items from Payment Terms (Initial Creation)

Operation: createBillingItemsFromPaymentTermsInitial

Trigger: First-time billing item creation after a new revenue item is inserted by Create Revenue Item (2.1).

Input Parameters:

• paymentTerms: Array — array of payment terms from the sync payload
• revenueItemId: Integer — newly created revenue_items.revenue_item_id
• commissionPerc: Decimal — commission rate from the revenue item
• context: Object — deal, entity, party, currency, department, and tax jurisdiction context fields
• actorId: String — identity of the actor

Creates billing items for a brand-new revenue item that has no prior billing history. Simpler than the two-way match because there are no existing items to compare against.

Step 1. Process Each Payment Term

• Source: Each entry in the paymentTerms input array.

• Action: For each payment term, determine collection style, calculate amounts, and insert records.

• Logic:

  1a. Determine collection style.

  • If paymentPartyId == context.buyerId, style is BUYER; else CLIENT.

  1b. Calculate REV and PAY amounts.

  • Same formulas as 2.5 step 1a.

  1c. Insert billing_item header.

  • Set billing_item_status_cd = 'U', current_item_ind = true, open_item_ind = true.
  • Populate all context fields from the context input object.

  1d. Insert REV and PAY details.

  • Same as 2.5 step 1f.

  1e. Update open item status.

  • Invoke Update Billing Item Open Status (2.9).

  1f. Calculate and persist tax deductions.

  • Same as 2.5 step 1h, but without the deletion of prior tax-auto deductions (none exist for brand-new billing items).

NOTE

This procedure is wrapped in a transaction. If a transaction context is provided by the caller, it joins that transaction; otherwise it creates its own.

Side-effects:

• None beyond the inserted records. No existing records are modified.

Postconditions:

• One billing_item row with current_item_ind = true exists for each payment term in the input.
• Each billing item has two billing_item_detail rows (REV and PAY) with amounts calculated from the commission rate and collection style.
• Tax deductions exist on PAY details where the tax service was available and returned non-zero amounts.

---

2.7 Manage Billing Item Deductions (In-Place)

Operation: manageBillingItemDeductions

Trigger: User creates, updates, or deletes deductions on a billing item through the Manage Deductions dialog. Deductions are saved directly on the existing billing item without triggering a rebill cycle.

Input Parameters:

• billingItemId: Integer — billing_item.billing_item_id of the parent billing item
• deductions: Array — array of deduction records; existing ones include billing_item_deduction_id; new ones do not
• actorId: String — identity of the user

Directly creates, updates, and deletes billing_item_deduction records on an existing billing item without creating a reversal. This is the standard approach for all deduction management — deductions do not follow the immutability pattern.

Step 1. Fetch All Existing Deductions

• Source: billing_item_detail WHERE billing_item_id = billingItemId; billing_item_deduction for each detail.
• Action: Read all existing deduction rows grouped by detail.
• Logic:
  • All deductions across both REV and PAY details are loaded.

Step 2. Identify Changes

• Source: deductions input vs. existing deduction rows from Step 1.
• Action: Classify each input record as create, update, or delete.
• Logic:
  • Input records with a billing_item_deduction_id = update.
  • Input records without a billing_item_deduction_id = create.
  • Existing deductions whose billing_item_deduction_id does not appear in the input = delete.

Step 3. Delete Removed Deductions

• Source: billing_item_deduction rows identified for deletion in Step 2.
• Action: DELETE FROM billing_item_deduction for each removed billing_item_deduction_id.
• Logic:
  • Only deductions not present in the input are deleted.

Step 4. Update Modified Deductions

• Source: Input records with an existing billing_item_deduction_id.
• Action: UPDATE billing_item_deduction with new billing_item_deduction_type_cd, billing_item_deduction_update_net_ind, billing_item_deduction_amt, and comment values.
• Logic:
  • Each update targets its own billing_item_deduction_id.

Step 5. Create New Deductions

• Source: Input records without a billing_item_deduction_id.
• Action: INSERT into billing_item_deduction for each new deduction.
• Logic:
  • Link to the appropriate billing_item_detail_id.

NOTE

This procedure does NOT recalculate billing_item_detail_amt. Deductions no longer affect billing_item_detail_amt — they are tracked separately and affect only balance calculations.

Side-effects:

• None beyond the direct inserts, updates, and deletes on billing_item_deduction. No cash applications are migrated. No reversal records are created.

Postconditions:

• billing_item_deduction rows for the billing item reflect the input deduction set.
• The billing item header and detail amounts are unchanged.

---

2.8 Update Billing Item Open Status

Operation: updateBillingItemOpenStatus

Trigger: Called internally after any operation that changes cash applications or billing item details, including Revise Billing Item (2.4), Two-Way Match (2.5), Initial Creation (2.6), and worksheet status transitions.

Input Parameters:

• billingItemId: Integer — PK (billing_item.billing_item_id) of the billing item to recalculate
• actorId: String — identity of the caller

Recalculates the open_item_ind flag on a billing item based on the total cash and deductions applied to its REV and PAY details.

Step 1. Fetch REV and PAY Details

• Source: billing_item_detail WHERE billing_item_id = billingItemId.
• Action: Read both the REV and PAY detail records.
• Logic:
  • Both detail types are required for the calculation.

Step 2. Calculate Total Applied Amount per Detail

• Source: cash_receipt_application joined to cash_receipt_worksheet.
• Action: For each detail, sum all applied cash and deduction amounts from approved worksheets.
• Logic:
  • Sum of cash_receipt_application.cash_receipt_amt_applied WHERE billing_item_detail_id = detail_id, joined to cash_receipt_worksheet WHERE cash_receipt_worksheet_status_cd IN ('A', 'S') AND current_item_ind = true.
  • Plus sum of cash_receipt_application_deduction.deduction_amt_applied for the same detail with the same worksheet join conditions.

Step 3. Determine Open Status

• Source: Totals from Step 2 and billing_item_detail_total_amt for each detail.
• Action: Compute the boolean open status.
• Logic:
  • is_rev_paid = ABS(rev_applied_total - rev_total_amt) < 0.01.
  • is_pay_paid = ABS(pay_applied_total - pay_total_amt) < 0.01.
  • is_open = NOT (is_rev_paid AND is_pay_paid).

Step 4. Update Billing Item

• Source: Computed is_open value from Step 3.
• Action: UPDATE billing_item SET open_item_ind = is_open WHERE billing_item_id = billingItemId.
• Logic:
  • Only open_item_ind is updated; all other fields remain unchanged.

WARNING

The tolerance (epsilon = 0.01) used here is slightly different from the system-wide comparison epsilon (0.005). This is intentional to handle minor rounding differences in cash application amounts.

Side-effects:

• None beyond the single UPDATE to billing_item.open_item_ind.

Postconditions:

• billing_item.open_item_ind reflects whether the billing item has outstanding balance based on currently approved worksheets.

---

2.9 Billing Job (GL Posting)

Operation: runBillingJob

Trigger: Accounting job execution with job type 'BILL', initiated by the job scheduler.

Input Parameters:

• asOfDate: Date — cutoff date for eligible records; only records with confirmed due dates on or before this date are processed
• actorId: String — defaults to 'SYSTEM'

A batch job that posts unposted REV billing item details to the general ledger by creating AR and Unbilled Revenue transaction pairs.

Step 1. Fetch Eligible Unposted Details

• Source: billing_item_detail joined to billing_item and revenue_items.
• Action: SELECT all eligible REV details for processing.
• Logic:
  • billing_item_detail_type_cd = 'REV'.
  • posting_status_cd = 'U'.
  • billing_item_due_dt_status_cd = 'C' (confirmed due date only).
  • billing_item_due_dt <= asOfDate.
  • billing_item_detail.created_dt <= asOfDate.

IMPORTANT

Only REV details with confirmed due dates (billing_item_due_dt_status_cd = 'C') are eligible for billing. Unconfirmed due dates are excluded from the billing job.

Step 2. Create Transaction Pairs

• Source: Each eligible detail from Step 1 with its posting date as today.
• Action: INSERT two transaction rows per detail (AR and Unbilled Revenue).
• Logic:
  • AR Transaction (Account 4): class_cd = 'AR', source_cd = 'BILL', source_id = billing_item_detail_id, trans_amt = billing_item_detail_amt, type_cd = 'D' (debit) if positive or 'C' (credit) if negative.
  • Unbilled Revenue Transaction (Account 6): class_cd = 'AR', source_cd = 'BILL', trans_amt = billing_item_detail_amt * -1, type_cd = 'C' (credit) if original positive or 'D' (debit) if negative.
  • Both: gl_status_cd = 'U', source_ref = billing_item.payment_term_ref, rev_ref = revenue_items.sales_item_ref.

Step 3. Batch Insert Transactions

• Source: Transaction pairs constructed in Step 2.
• Action: INSERT into transaction in batches of 1,000 rows.
• Logic:
  • Batching prevents excessive memory use for large posting runs.

Step 4. Update Posted Details

• Source: Each billing_item_detail processed in Steps 1-3.
• Action: UPDATE billing_item_detail SET posting_status_cd = 'P', posting_dt = <calculated_posting_dt>.
• Logic:
  • One update per processed detail record.

NOTE

The billing job only processes REV details. PAY details are not posted by this job — PAY GL posting occurs downstream when payment items are sent to the bank and confirmed.

Side-effects:

• transaction rows are created for each posted detail.
• billing_item_detail.posting_status_cd transitions from 'U' to 'P' for all processed rows.

Postconditions:

• All eligible REV details with confirmed due dates on or before asOfDate have posting_status_cd = 'P' and a non-null posting_dt.
• Corresponding transaction pairs (AR debit + Unbilled Revenue credit) exist in the transaction table.

---

2.10 Revenue Recognition Job (GL Posting)

Operation: runRevenueRecognitionJob

Trigger: Accounting job execution with job type 'REV', initiated by the job scheduler.

Input Parameters:

• asOfDate: Date — cutoff date; only schedule entries with revenue_dt <= asOfDate are processed
• actorId: String — defaults to 'SYSTEM'

A batch job that posts unposted revenue item schedules to the general ledger by creating Revenue and Deferred Revenue transaction pairs.

Step 1. Fetch Eligible Unposted Schedules

• Source: revenue_item_schedules joined to revenue_items.
• Action: SELECT all schedule entries where revenue_item_posting_status_cd = 'U' AND revenue_dt <= asOfDate.
• Logic:
  • All unposted schedules with a revenue date on or before the cutoff are eligible.

Step 2. Create Transaction Pairs

• Source: Each eligible schedule from Step 1 with its posting date as today.
• Action: INSERT two transaction rows per schedule (Revenue and Deferred Revenue).
• Logic:
  • Revenue Transaction (Account 13): class_cd = 'REV', source_cd = 'REV', trans_amt = revenue_amt * -1 (credit for positive revenue), type_cd = 'C' if positive or 'D' if negative.
  • Deferred Revenue Transaction (Account 1): trans_amt = revenue_amt (debit for positive revenue), type_cd = 'D' if positive or 'C' if negative.

Step 3. Batch Insert Transactions

• Source: Transaction pairs constructed in Step 2.
• Action: INSERT into transaction in batches of 1,000 rows.
• Logic:
  • Batching prevents excessive memory use for large posting runs.

Step 4. Update Posted Schedules

• Source: Each revenue_item_schedules row processed in Steps 1-3.
• Action: UPDATE revenue_item_schedules SET revenue_item_posting_status_cd = 'P', revenue_item_posting_dt = <calculated_posting_dt>.
• Logic:
  • One update per processed schedule row.

Side-effects:

• transaction rows are created for each posted schedule.
• revenue_item_schedules.revenue_item_posting_status_cd transitions from 'U' to 'P' for all processed rows.

Postconditions:

• All revenue_item_schedules entries with revenue_dt <= asOfDate have revenue_item_posting_status_cd = 'P' and a non-null revenue_item_posting_dt.
• Corresponding transaction pairs (Revenue credit + Deferred Revenue debit) exist in the transaction table.

---

2.11 Deactivate Billing Item

Operation: deactivateBillingItem

Trigger: Called internally by other procedures (2.2, 2.4, 2.5) after a replacement billing item has been created.

Input Parameters:

• billingItemId: Integer — PK (billing_item.billing_item_id) of the billing item to deactivate
• actorId: String — identity of the actor

Marks a billing item as no longer current by setting current_item_ind = false.

Step 1. Update Billing Item

• Source: billing_item WHERE billing_item_id = billingItemId.
• Action: UPDATE billing_item SET current_item_ind = false, updated_by = actorId.
• Logic:
  • Only current_item_ind and updated_by are modified.

NOTE

Deactivation does not change open_item_ind, billing_item_status_cd, or any detail or deduction records. The billing item and all its children remain intact for historical queries.

Side-effects:

• None beyond the single field update.

Postconditions:

• billing_item.current_item_ind = false for the specified record.
• All associated billing_item_detail and billing_item_deduction records remain unchanged.

---

2.12 Calculate and Persist Tax Deductions

Operation: calculateAndPersistTaxDeductions

Trigger: Called during billing item creation (2.5, 2.6) and revenue sync rebilling (2.5).

Input Parameters:

• billingItemId: Integer — billing_item.billing_item_id of the header record to update
• payDetailId: Integer — billing_item_detail.billing_item_detail_id of the PAY detail for deduction attachment
• partyId: Integer — contracted party or client ID for tax profile lookup
• utaEntityId: Integer — FK to uta_entity; used for billing entity determination
• serviceCountryCd: String — billing_item.service_country_cd; used for jurisdiction resolution
• grossAmt: Decimal — gross amount for tax base calculation
• commissionRate: Decimal — commission rate from the revenue item
• currencyCd: String — billing_item.currency_cd; used for FX considerations
• existingDeductionTypes: Set (optional) — set of billing_item_deduction_type_cd values already present, used for the additive approach during revenue sync rebilling
• actorId: String — identity of the actor

Calculates withholding tax for a billing item and creates billing_item_deduction records on the PAY detail. Also sets VAT pass-through metadata on the billing item header.

Step 1. Resolve UTA Entity Name and Billing Entity Code

• Source: uta_entity record for utaEntityId.
• Action: Look up the entity name and map it to a billing entity code.
• Logic:
  • Map entity name to a code such as UK_AGENCY, US_ENTITY, etc.

Step 2. Build Deal Tax Context

• Source: All input parameters plus resolved billing entity code.
• Action: Assemble the full tax context object.
• Logic:
  • Context includes billing entity, gross amount, service location (serviceCountryCd), commission rate, and currency.

Step 3. Compute UK Cumulative Gross (UK Agency Only)

• Source: billing_item_detail joined to billing_item for the party across UK entities.
• Action: Sum billing_item_detail.billing_item_detail_gross_amt for the party within the current UK tax year (April 6 to April 5).
• Logic:
  • Condition: only when utaEntityId maps to a UK Agency entity.
  • Result sets ukYtdEarnings and ukExpectedYtdTotal on the deal tax context.
  • Purpose: determines whether proactive FEU withholding should apply even if actual earnings have not yet crossed the £12,570 threshold.

Step 4. Invoke Withholding Tax Calculation

• Source: Deal tax context from Steps 2 and 3.
• Action: Invoke the withholding tax calculation service.
• Logic:
  • Service evaluates applicable jurisdictions (US NRA, UK FEU, UK VAT) and returns a list of deduction results with type, amount, and update-net indicator.

Step 5. Persist Calculated Deductions

• Source: Each deduction result from Step 4.
• Action: For each non-zero deduction whose type is not in existingDeductionTypes: INSERT into billing_item_deduction.
• Logic:
  • Set billing_item_detail_id = payDetailId.
  • Set created_by = 'TAX_AUTO_RECEIVABLE' (used to identify stale auto-calculated deductions during revenue sync rebilling).
  • Set billing_item_deduction_type_cd, billing_item_deduction_amt, billing_item_deduction_update_net_ind, and comment from the calculation result.
  • Skip deductions with zero amount.
  • Skip deduction types already present in existingDeductionTypes (additive approach).

Step 6. Update Billing Item Header

• Source: Tax calculation results from Step 4.
• Action: UPDATE billing_item SET tax metadata fields.
• Logic:
  • Set tax_calculated_dt = NOW().
  • Set tax_stale_ind = false.
  • If VAT pass-through applies: set vat_pass_through_rate, vat_pass_through_amt, and expected_invoice_total from calculation results.
  • If no VAT pass-through: set expected_invoice_total = grossAmt.

WARNING

Tax calculation failure does not block billing item creation. Errors are logged but the billing item is created without tax deductions. The worksheet recalculates tax authoritatively at load time.

NOTE

Tax deductions created by this procedure are tagged with created_by = 'TAX_AUTO_RECEIVABLE' to distinguish them from manually entered deductions. This tag is used during revenue sync rebilling (2.5) to identify and replace stale auto-calculated deductions.

Side-effects:

• billing_item.tax_calculated_dt, tax_stale_ind, and VAT pass-through fields are updated on the billing item header.

Postconditions:

• billing_item_deduction rows exist on the PAY detail for each non-zero withholding or VAT deduction calculated.
• billing_item.tax_stale_ind = false and billing_item.tax_calculated_dt is set to the current timestamp.

---

3. Business Rules & Logic

3.1 Immutability Pattern

Business rule: Financial records in the billing items domain are never edited in place. When amounts or dates change, the original record is deactivated, a negated reversal is created as an audit offset, and a new current record replaces it. Deductions are an exception — they are managed in-place on the existing billing item without triggering the immutability pattern.

Data-level enforcement:

• Read: billing_item.current_item_ind — only true records are active and visible in normal queries.
• Guard: Mutation operations (revise, two-way match) operate only on records with current_item_ind = true. If the original is already deactivated, reject the operation.
• Write: After creating the replacement, set billing_item.current_item_ind = false on the original and true on the replacement. Reversal records always have current_item_ind = false and open_item_ind = false.

Principle	Data behavior
Never update amounts in place	Deactivate original, create negated reversal, create new record with updated values
current_item_ind tracks the active version	Only the latest version has current_item_ind = true
Reversal records are not current	Reversal records always have current_item_ind = false and open_item_ind = false
Cash applications migrate forward	cash_receipt_application.billing_item_detail_id is updated from old detail ID to new detail ID
Deductions are copied, not moved	Deductions are duplicated onto the new detail; reversal details also get negated copies

This pattern applies to both revenue_items and billing_item hierarchies.

---

3.2 REV/PAY Detail Pair Rule

Business rule: Every billing item must have exactly one REV detail (UTA's commission) and exactly one PAY detail (the client's share). This pair is always created atomically and is never split or merged.

Data-level enforcement:

• Read: billing_item_detail.billing_item_detail_type_cd — must be 'REV' or 'PAY'.
• Guard: All creation paths assert that both detail types exist on the original before creating reversals. If a detail is missing, the operation is rejected.
• Write: Every INSERT path for a billing item inserts exactly two billing_item_detail rows within the same database transaction.

---

3.3 REV/PAY Amount Calculation

Business rule: The REV detail carries UTA's commission and the PAY detail carries the client's share. When collection style is CLIENT, PAY amounts are zero because the buyer pays the client directly.

Data-level enforcement:

• Read: billing_item.collection_style_cd and billing_item.collection_style_override_ind.
• Guard: If collection_style_override_ind = true, preserve the existing collection_style_cd from the matched original during sync; do not recalculate from the payment term.
• Write: Set amounts as follows:

Detail	gross_amt	percent	amt	tax_amt	total_amt
REV	grossAmt	commissionPerc	gross * percent	0 (initially)	amt + tax
PAY (BUYER style)	grossAmt	1 - commissionPerc	gross * percent	0 (initially)	amt + tax
PAY (CLIENT style)	0	0	0	0	0

IMPORTANT

The formula billing_item_detail_amt = billing_item_detail_gross_amt * billing_item_detail_percent is invariant. Deductions do NOT affect billing_item_detail_amt — they are tracked separately in billing_item_deduction.

---

3.4 Open Item Indicator Logic

Business rule: billing_item.open_item_ind is true when the billing item has outstanding balance, and false when both REV and PAY details have been fully applied (within tolerance).

Data-level enforcement:

• Read: Sum of cash_receipt_application.cash_receipt_amt_applied plus sum of cash_receipt_application_deduction.deduction_amt_applied for each detail, joined to approved worksheets (cash_receipt_worksheet_status_cd IN ('A', 'S') AND current_item_ind = true).
• Guard: Recalculated by Update Billing Item Open Status (2.9) after any cash application change; never set manually.
• Write: UPDATE billing_item.open_item_ind:

is_rev_paid = ABS(rev_applied_total - rev_total_amt) < 0.01
is_pay_paid = ABS(pay_applied_total - pay_total_amt) < 0.01
open_item_ind = NOT (is_rev_paid AND is_pay_paid)

---

3.5 Billing Item Status Transitions

Business rule: Billing item status tracks the billing lifecycle from creation through optional cancellation. Reversal copies receive a modified status to distinguish them visually from originals.

Data-level enforcement:

• Read: billing_item.billing_item_status_cd.
• Guard: No backward transitions. 'X' and 'C' are terminal states.
• Write: Set status at creation and reversal time:

From	To	Trigger	Notes
—	'U'	Billing item created	Default for all new items
'U'	'B'	Billed to buyer	Manual or process-driven
'U'	'X'	Skipped	Intentional skip; also used for reversals when original was 'U'
'U'	'C'	Cancelled	Terminal
'B'	'C'	Cancelled after billing	Terminal

Reversal status logic: If the original had billing_item_status_cd = 'U', the reversal copy gets 'X'. Otherwise, the reversal copy gets 'U'. This provides a visual audit trail distinguishing reversals from originals.

---

3.6 Posting Eligibility Rules

Business rule: Only records meeting specific conditions are eligible for GL posting. Jobs are idempotent: they reset and re-post any previously posted records within the date window before proceeding.

Data-level enforcement:

Billing job ('BILL') — REV details:

• Read: billing_item_detail.posting_status_cd = 'U', billing_item_detail_type_cd = 'REV', billing_item_due_dt_status_cd = 'C', billing_item_due_dt <= asOfDate, billing_item_detail.created_dt <= asOfDate.
• Guard: Unconfirmed due dates (billing_item_due_dt_status_cd != 'C') are excluded.
• Write: posting_status_cd = 'P', posting_dt = <calculated> on eligible records.

Revenue recognition job ('REV') — revenue schedules:

• Read: revenue_item_schedules.revenue_item_posting_status_cd = 'U', revenue_dt <= asOfDate.
• Write: revenue_item_posting_status_cd = 'P', revenue_item_posting_dt = <calculated> on eligible records.

---

3.7 Revenue Schedule Generation Rules

Business rule: Revenue schedules are generated based on the recognition style specified on the revenue item. Only 'I' (Immediate) and 'M' (Monthly) styles produce schedule entries at creation time.

Data-level enforcement:

• Read: revenue_items.revenue_item_rec_style_cd.
• Write: INSERT into revenue_item_schedules:

Recognition Style	Schedule Behavior
'I' (Immediate)	Single entry: revenue_amt = commission_amt, revenue_dt = revenue_item_start_dt
'M' (Monthly)	Multiple entries: daily_rate = commission_amt / total_days; each month segment gets days_in_segment * daily_rate; last segment absorbs rounding
'C' (Cash)	No schedules generated; revenue recognized at cash application time

---

3.8 Collection Style Override Preservation

Business rule: When a user has manually overridden the collection style on a billing item, that override must survive upstream deal engine sync operations.

Data-level enforcement:

• Read: billing_item.collection_style_override_ind.
• Guard: In the two-way match (2.5), if the matched original has collection_style_override_ind = true, the collection_style_cd and collection_party_id from that original are copied to the new billing item without recalculation.
• Write: The new billing item inherits collection_style_override_ind = true and the overridden values from the matched original.

---

3.9 Tax Deduction Lifecycle at Billing Item Level

Business rule: Tax deductions on billing items are estimates only. The authoritative tax calculation occurs at the worksheet level when a worksheet is loaded. Billing-item-level deductions are informational until applied.

Data-level enforcement:

• Read: billing_item_deduction.created_by = 'TAX_AUTO_RECEIVABLE' identifies auto-calculated deductions. billing_item.tax_stale_ind indicates whether tax estimates are out of date.
• Guard: During revenue sync rebilling (2.5), stale auto-calculated deductions are deleted before recalculation. User-provided deductions take precedence (additive approach).
• Write: Lifecycle of tax deductions at the billing item level:
  1. Creation: Tax deductions auto-calculated and persisted on PAY detail with created_by = 'TAX_AUTO_RECEIVABLE'.
  2. Revenue sync: Stale auto-calculated deductions deleted; fresh calculations applied.
  3. Worksheet load: Tax recalculated authoritatively; billing-item-level deductions used as informational reference only.

---

4. Field Mapping & Transformation

4.1 Sales Item to Revenue Item

Source Table.Field	Target Table.Field	Transform
(input).salesItemRef	revenue_items.sales_item_ref	Copied as-is
(input).utaEntityId	revenue_items.uta_entity_id	Copied as-is
(input).agentGroupId	revenue_items.agent_group_id	Copied as-is
(input).name	revenue_items.revenue_item_name	Copied as-is
(input).salesItemStatusCd	revenue_items.revenue_item_status_cd	Copied as-is (default 'U')
(input).revenueDateStatusCd	revenue_items.revenue_item_date_status_cd	Copied as-is (default 'U')
(input).dealId	revenue_items.deal_id	Parsed to integer
(input).clientEntityId	revenue_items.client_id	Copied as-is
(input).contractedPartyId	revenue_items.contracted_party_id	Copied as-is
(input).buyerEntityId	revenue_items.buyer_id	Copied as-is
(input).departmentId	revenue_items.department_id	Copied as-is
(input).currencyCd	revenue_items.currency_cd	Copied as-is
(input).grossAmt	revenue_items.revenue_item_gross_amt	Copied as-is
(input).utaCommissionType	revenue_items.revenue_item_commission_flat_ind	true if 'FLAT', else false
(input).utaCommissionPerc	revenue_items.revenue_item_commission_perc	Copied as-is
(input).utaCommissionAmt	revenue_items.revenue_item_commission_amt	Copied as-is
(input).revenueStartDt	revenue_items.revenue_item_start_dt	Copied as-is
(input).revenueEndDt	revenue_items.revenue_item_end_dt	Copied as-is
(input).revRecStyleCd	revenue_items.revenue_item_rec_style_cd	Copied as-is
—	revenue_items.current_item_ind	Defaulted to true on creation

---

4.2 Payment Term to Billing Item

Source Table.Field	Target Table.Field	Transform
(input).paymentTermRef	billing_item.payment_term_ref	Copied as-is
(input).name	billing_item.billing_item_name	Copied as-is
(input).dueDt	billing_item.billing_item_due_dt	Copied as-is
(input).dueDt	billing_item.billing_item_aging_dt	Default to due_dt; preserved from matched original if available
(input).dueDateStatusCd	billing_item.billing_item_due_dt_status_cd	Copied as-is (default 'U')
(input).paymentPartyId	billing_item.collection_party_id	Copied as-is (unless collection style override is active)
(input).grossAmt	billing_item_detail.billing_item_detail_gross_amt	Used with commission rate to compute detail amounts
—	billing_item.billing_item_status_cd	Defaulted to 'U' on creation
—	billing_item.current_item_ind	Defaulted to true on creation
—	billing_item.open_item_ind	Defaulted to true initially; recalculated after creation

---

4.3 Original Billing Item to Reversal

Source Table.Field	Target Table.Field	Transform
billing_item.* (all header fields)	billing_item.*	Copied as-is except fields listed below
billing_item.billing_item_status_cd	billing_item.billing_item_status_cd	'U' → 'X'; any other value → 'U'
billing_item.current_item_ind	billing_item.current_item_ind	Forced to false
billing_item.open_item_ind	billing_item.open_item_ind	Forced to false
billing_item_detail.billing_item_detail_gross_amt	billing_item_detail.billing_item_detail_gross_amt	Negated (* -1)
billing_item_detail.billing_item_detail_percent	billing_item_detail.billing_item_detail_percent	Copied as-is (unchanged)
billing_item_detail.billing_item_detail_amt	billing_item_detail.billing_item_detail_amt	Negated (* -1)
billing_item_detail.billing_item_detail_tax_amt	billing_item_detail.billing_item_detail_tax_amt	Negated (* -1)
billing_item_detail.billing_item_detail_total_amt	billing_item_detail.billing_item_detail_total_amt	Negated (* -1)
billing_item_detail.posting_status_cd	billing_item_detail.posting_status_cd	Forced to 'U'
billing_item_detail.posting_dt	billing_item_detail.posting_dt	Forced to NULL
billing_item_deduction.billing_item_deduction_amt	billing_item_deduction.billing_item_deduction_amt	Negated (* -1)
billing_item_deduction.* (all other fields)	billing_item_deduction.*	Copied as-is

---

4.4 Original Revenue Item to Reversal

Source Table.Field	Target Table.Field	Transform
revenue_items.* (all fields)	revenue_items.*	Copied as-is except fields listed below
revenue_items.revenue_item_gross_amt	revenue_items.revenue_item_gross_amt	Negated (* -1)
revenue_items.revenue_item_commission_amt	revenue_items.revenue_item_commission_amt	Negated (* -1)
revenue_items.current_item_ind	revenue_items.current_item_ind	Forced to false
revenue_item_schedules.revenue_amt	revenue_item_schedules.revenue_amt	Negated (* -1) for each schedule entry
revenue_item_schedules.revenue_item_posting_status_cd	revenue_item_schedules.revenue_item_posting_status_cd	Forced to 'U'
revenue_item_schedules.revenue_item_posting_dt	revenue_item_schedules.revenue_item_posting_dt	Forced to NULL

---

5. Cross-References

Document	Relationship
Billing Items Data Model	Schema definitions, field types, constraints, status lifecycles, and code master values for all tables in this domain. Procedures in this document operate on those tables.
Billing Items Queries	Read operations including single-record lookups, search, flattened display view, AR aging, and receivable search for cash application.
Cash Receipts Procedures	Downstream consumers. Cash receipt applications create cash_receipt_application records that reference billing_item_detail.billing_item_detail_id. Worksheet Apply posts REV to GL.
Settlements Procedures	Settlements divide PAY applications among deal parties. Created during worksheet Applied status from PAY billing_item_detail records.
Settlements Procedures — Payment Items	Payment items created from settlement payouts. Payment lifecycle determines whether PAY billing_item_detail applications can be reversed.
Write-Offs Procedures	Write-off packets reference REV billing_item_detail records. Approval transitions billing_item_detail.write_off_status_cd.

Key Integration Points

Integration	Direction	Description
Deal Engine Sync	Inbound	Sales items and payment terms flow in. Triggers Create Revenue Item (2.1) for new items or Update Revenue Item (2.2) for updates.
Cash Receipt Application	Outbound	Billing item detail IDs are referenced by cash_receipt_application.billing_item_detail_id. Migration of these references occurs during Revise Billing Item (2.4) and Two-Way Match (2.5).
Tax Calculation	Bidirectional	Tax deductions estimated at creation via Calculate and Persist Tax Deductions (2.13); recalculated authoritatively at worksheet load.
Accounting Jobs (GL)	Outbound	Billing Job (2.10) posts REV details. Revenue Recognition Job (2.11) posts revenue schedules.
AR Aging Reports	Read	AR aging queries filter on billing_item.current_item_ind, billing_item.open_item_ind, and billing_item.billing_item_aging_dt to produce aging buckets.