Key Procedures and Functions

This document outlines the core logic for Revenue and Billing updates, referencing specific database tables and columns.

Revenue Service - Update Revenue Item

Function: updateRevenueItem

Input Parameters:

• actorId: String identifier for the user/system performing the update
• originalItemId: Integer - revenue_items.revenue_item_id
• currentState: Object containing:
  • revenueItem: Partial object with fields from revenue_items table:
    • revenue_item_name
    • revenue_item_gross_amt
    • revenue_item_commission_amt
    • revenue_item_commission_perc
    • revenue_item_start_dt
    • revenue_item_end_dt
    • revenue_item_rec_style_cd
    • revenue_item_status_cd
    • revenue_item_date_status_cd
  • paymentTerms: Array of objects from payment_term table:
    • payment_term_id
    • payment_term_ref
    • name
    • payment_party_id
    • gross_amt
    • due_dt

This process handles the full update of a revenue item, including reversals, new item creation, and triggering the billing update process.

1. Check for Changes

• Action: Compare currentState.revenueItem fields against the original revenue_items record.
• Logic:
  • If NO changes are detected in revenue_items fields:
    • Skip creation of Reversal Revenue Item, Reversal Schedules, Billing Item Reversals, New Revenue Item, and New Revenue Schedules.
    • Proceed directly to Step 6: Create New Billing Items (passing the original revenue_item_id).
  • If changes ARE detected:
    • Proceed to Step 2.

2. Create Reversal Revenue Item (If Changes Detected)

• Source: revenue_items table (record matching originalItemId).
• Action: Create a new record in revenue_items.
• Logic:
  • Copy all fields from the original item.
  • Negate revenue_item_gross_amt and revenue_item_commission_amt (multiply by -1).
  • Set current_item_ind = false.
  • Set created_by and updated_by to actorId.

3. Create Reversal Revenue Schedules (If Changes Detected)

• Source: revenue_item_schedules table (records linked to originalItemId).
• Action: Create new records in revenue_item_schedules.
• Logic:
  • For each schedule, copy fields.
  • Link to the new Reversal Revenue Item (revenue_item_id).
  • Negate revenue_amt.
  • Set revenue_item_posting_status_cd = 'U'.
  • Set revenue_item_posting_dt = NULL.

4. Create Billing Item Reversals (If Changes Detected)

• Source: billing_item table (records linked to originalItemId where current_item_ind is true).
• Action: For each active billing item, call BillingService.createBillingItemReversal.
• Logic:
  • Billing Item Reversal:
    • Create a new billing_item record.
    • Copy fields from original.
    • Set revenue_item_id to the Reversal Revenue Item ID (from Step 2).
    • Set current_item_ind = false.
    • Set open_item_ind = false.
    • Toggle billing_item_status_cd (if 'U' then 'X', else 'U').
  • Detail Reversals:
    • For each billing_item_detail linked to the original billing item:
      • Create a new billing_item_detail record linked to the reversal billing item.
      • Negate billing_item_detail_gross_amt, billing_item_detail_net_amt, billing_item_detail_billing_amt, billing_item_detail_tax_amt, billing_item_detail_total_amt.
      • Set posting_status_cd = 'U', posting_dt = NULL.
  • Deduction Reversals:
    • For each billing_item_deduction linked to the original detail:
      • Create a new billing_item_deduction record linked to the reversal detail.
      • Negate billing_item_deduction_amt.

5. Create New Revenue Item (If Changes Detected)

• Source: currentState.revenueItem combined with original item data.
• Action: Create a new record in revenue_items.
• Logic:
  • Copy original item fields.
  • Apply changes from currentState.revenueItem.
  • Set current_item_ind = true.

6. Create New Revenue Schedules (If Changes Detected)

• Action: Generate and insert revenue_item_schedules records.
• Logic:
  • Based on revenue_item_rec_style_cd ('I' for Immediate, 'M' for Monthly).
  • Distribute revenue_item_commission_amt across the schedule dates.

7. Create New Billing Items

• Action: Call BillingService.createNewBillingItem.
• Input: paymentTerms (from input), newRevenueItemId (from Step 5, or originalItemId if no changes), originalBillingItems, commissionPerc.
• Logic: See "Billing Service - Create New Billing Item" section below.

8. Update Original Revenue Item (If Changes Detected)

• Target: revenue_items table (record matching originalItemId).
• Action: Update record.
• Logic:
  • Set current_item_ind = false.

---

Billing Service - Create New Billing Item

Function: createNewBillingItem

Input Parameters:

• actorId: String identifier for the user/system performing the operation
• paymentTerms: Array of objects from payment_term table
• newRevenueItemId: Integer - revenue_items.revenue_item_id
• originalBillingItems: Array of objects from billing_item table
• commissionPerc: Decimal - commission percentage from revenue_items.revenue_item_commission_perc

This process reconciles the new payment terms with existing billing items to preserve history (deductions, cash receipts) where possible.

1. Process Payment Terms (2-Way Match)

Iterate through each paymentTerm provided in the input.

• Calculate Amounts:

  • grossAmt = payment_term.gross_amt (if null, 0)
  • REV Detail:
    • billing_item_detail_gross_amt = grossAmt
    • billing_item_detail_percent = commissionPerc
    • billing_item_detail_billing_amt = grossAmt * commissionPerc
  • PAY Detail:
    • billing_item_detail_gross_amt = grossAmt
    • billing_item_detail_percent = 1 - commissionPerc
    • billing_item_detail_billing_amt = grossAmt * (1 - commissionPerc)

• Find Match:

  • Look for an existing record in originalBillingItems where payment_term_ref matches payment_term.payment_term_ref.

• Check for Changes (If Match Found):

  • Compare paymentTerm fields and calculated amounts against the matched billing_item and its details.
  • Logic:
    • If revenue_item_id, billing_item_name, billing_item_due_dt, collection_party_id, collection_style_cd, and amounts match:
      • NO CHANGE: Skip creation of new billing item. Keep original active. Continue to next payment term.
    • If ANY difference is detected:
      • CHANGE DETECTED: Proceed to create new billing item and mark original for deactivation.

• Create New Billing Item:

  • Insert into billing_item.
  • Fields:
    • revenue_item_id: newRevenueItemId
    • payment_term_ref: payment_term.payment_term_ref
    • billing_item_due_dt: payment_term.due_dt
    • billing_item_due_dt_status_cd: 'U' (Default)
    • billing_item_aging_dt: If match found, use match's billing_item_aging_dt, else payment_term.due_dt.
    • collection_style_cd:
      • If match has override (collection_style_override_ind = true), use match's collection_style_cd.
      • Else, if payment_term.payment_party_id == buyer_id, set to 'BUYER', else 'CLIENT'.
    • current_item_ind: true
    • open_item_ind: true (temporarily, updated in Step 3).

• Create Details:

  • Insert billing_item_detail for 'REV' type.
  • Insert billing_item_detail for 'PAY' type.

• If Match Found (Preserve History):

  • Copy Deductions:
    • For both 'REV' and 'PAY' types, find corresponding details in the matched original item.
    • Copy all billing_item_deduction records to the new details.
  • Migrate Cash Receipts:
    • Update cash_receipt_application table.
    • Change billing_item_detail_id from the original detail ID to the new detail ID.

2. Handle Unmatched Original Items

Identify originalBillingItems whose payment_term_ref was NOT present in the input paymentTerms.

• Action: Create "Zero" Billing Items to maintain audit trail or allow for future reconciliation.
• Logic:
  • Create new billing_item copying the original unmatched item.
  • Set revenue_item_id = newRevenueItemId.
  • Set current_item_ind = true.
  • Create Zero Details:
    • Create 'REV' and 'PAY' billing_item_detail records.
    • Set all amounts (gross, net, billing, tax, total) to 0.00.
  • Mark original item for deactivation.

3. Update Open Item Status

For ALL newly created billing items (matched and unmatched):

• Action: Determine if the item is fully paid.
• Logic:
  • Fetch cash_receipt_application sums for both 'REV' and 'PAY' details.
  • Compare applied cash to billing_item_detail_total_amt.
  • isRevPaid: abs(applied - total) < 0.01
  • isPayPaid: abs(applied - total) < 0.01
  • Update: Set billing_item.open_item_ind = NOT (isRevPaid AND isPayPaid).

4. Deactivate Replaced Original Items

• Target: billing_item table (original items that were matched and changed, or unmatched).
• Action: Update records.
• Logic:
  • Set current_item_ind = false.