Client Framework

Appearance

Deals, Sales Items and Payment Terms Procedures ​

1. Executive Summary ​

This document describes the step-by-step data mutation operations for the Deals, Sales Items and Payment Terms domain. It covers deal creation and party management, the sales block pipeline that transforms sales items and payment terms into revenue items and billing items, and direct sales pipeline operations.

Sales block pipeline — The primary workflow. Sales items and payment terms arrive in sales blocks (sales_block, sales_item, payment_term) and are processed into revenue_items and billing_item records.

Tables in Scope:

TableRole
dealCore commercial agreement
deal_partyParty-commission associations on a deal
sales_itemVersioned revenue line items (sales pipeline)
sales_item_partyPer-party commissions on a sales item
sales_meta_dataExtensible key-value metadata on sales items
sales_blockBatch container for sales pipeline processing
payment_termVersioned payment terms (sales pipeline)

Not covered (documented separately):

2. Key Procedures ​

2.1 Create Deal ​

Operation: createDeal

Trigger: User submits the Create Deal form with a deal name.

Input Parameters:

  • dealName: String (required) — deal.deal_name
  • dealReference: String (optional) — deal.deal_reference
  • dealStartDt: Date (optional) — deal.deal_start_dt
  • dealEndDt: Date (optional) — deal.deal_end_dt
  • activeInd: Boolean (optional) — deal.active_ind
  • actorId: String — identifier for the user performing the operation

Creates a new commercial agreement record in deal.

Step 1. Resolve Deal Reference ​

  • Source: Input parameters.
  • Action: Compute the deal_reference value.
  • Logic:
    • If dealReference is not provided, generate one using the pattern DEAL-{random_alphanumeric}.
    • If dealReference is provided, use it as-is.

Step 2. Insert Deal Row ​

  • Source: Resolved reference and all input parameters.
  • Action: INSERT one row into deal.
  • Logic:
    • Set deal_reference to the resolved reference from Step 1.
    • Set deal_name = dealName.
    • Set deal_start_dt = dealStartDt (if provided).
    • Set deal_end_dt = dealEndDt (if provided).
    • Set active_ind = activeInd (if provided).
    • Set created_by = actorId, created_dt = now().
    • Set updated_by = actorId, updated_dt = now().

Side-effects:

  • None beyond the deal row itself.

Postconditions:

  • One new deal row exists with the generated or provided deal_reference.
  • The new deal_id is returned to the caller.

2.2 Update Deal Details ​

Operation: updateDeal

Trigger: User edits deal fields and saves from the deal detail screen.

Input Parameters:

  • dealId: Integer (required) — deal.deal_id
  • dealName: String (optional) — deal.deal_name
  • dealReference: String (optional) — deal.deal_reference
  • dealStartDt: Date (optional) — deal.deal_start_dt
  • dealEndDt: Date (optional) — deal.deal_end_dt
  • activeInd: Boolean (optional) — deal.active_ind
  • actorId: String — identifier for the user performing the operation

Modifies an existing deal record in place.

Step 1. Update Deal Row ​

  • Source: deal (record matching dealId).
  • Action: UPDATE the deal row matching deal_id.
  • Logic:
    • Apply any provided field changes to deal_name, deal_reference, deal_start_dt, deal_end_dt, active_ind.
    • Set updated_dt = now().
    • Set updated_by = actorId.

Side-effects:

  • None beyond the deal row itself.

Postconditions:

  • The deal row now reflects the updated field values.
  • The updated deal record is returned to the caller.

2.3 Upsert Deal with Parties ​

Operation: upsertDealWithParties

Trigger: User saves a deal form that includes a party list, or an integration upserts a deal with its full party composition.

Input Parameters:

  • dealId: Integer (optional) — deal.deal_id; absent means create
  • dealName: String (required for create) — deal.deal_name
  • parties: Array of party objects — deal_party rows; each contains dealPartyId, partyId, partyRoleTypeCd, dealPartyCommissionFlatInd, dealPartyCommissionPerc, dealPartyCommissionAmt, bankAccountId, isDeleted
  • actorId: String — identifier for the user performing the operation

Creates or updates a deal and synchronizes its party list in a single transaction. The party list provided is treated as authoritative — parties not in the list are deleted.

Step 1. Upsert the Deal ​

  • Source: Input parameters.
  • Action: INSERT or UPDATE deal.
  • Logic:
    • If dealId is provided: update the existing deal row (apply field changes, set updated_dt = now()).
    • If dealId is absent: insert a new deal row (generate reference if not provided, set created_dt = now()).
    • Resolve the deal ID for subsequent steps.

Step 2. Fetch Existing Parties ​

  • Source: deal_party (all rows where deal_id = the resolved deal ID).
  • Action: SELECT all existing deal_party rows.
  • Logic:
    • Collect the set of existing deal_party_id values for comparison in Step 3.

Step 3. Process the Provided Party List ​

  • Source: parties input array.
  • Action: INSERT, UPDATE, or DELETE deal_party rows based on each party's state.
  • Logic:
    • Set deal_id = the resolved deal ID on every party.
    • Sanitize commission fields: blank or null deal_party_commission_perc defaults to '0'; blank or null deal_party_commission_amt defaults to '0'.
    • If the party has a dealPartyId AND isDeleted = true: DELETE the deal_party row.
    • If the party has a dealPartyId AND isDeleted = false: UPDATE the existing deal_party row with new values.
    • If the party has no dealPartyId AND isDeleted = false: INSERT a new deal_party row.

Step 4. Delete Orphaned Parties ​

  • Source: The set of existing deal_party_id values collected in Step 2.
  • Action: DELETE deal_party rows for any IDs not represented in the input.
  • Logic:
    • For each deal_party_id in the existing set that was not included or was flagged isDeleted in the input, delete the deal_party row.

IMPORTANT

The party list is authoritative. Any existing deal_party row not represented in the input (or marked isDeleted) will be deleted. This ensures the deal's party composition exactly matches the submitted list.

Side-effects:

  • All deal_party rows for the deal are reconciled to the submitted list within the transaction.

Postconditions:

  • The deal row reflects the upserted values.
  • All deal_party rows for the deal exactly match the provided party list.

2.4 Create Deal Party ​

Operation: createDealParty

Trigger: User adds a party to an existing deal from the deal parties management screen.

Input Parameters:

  • dealId: Integer (required) — deal_party.deal_id
  • partyId: Integer (required) — deal_party.party_id
  • partyRoleTypeCd: String (required) — deal_party.party_role_type_cd; must be a valid PARTY_ROLE_TYPE_CD code master value
  • dealPartyCommissionFlatInd: Boolean (required) — deal_party.deal_party_commission_flat_ind
  • dealPartyCommissionPerc: Decimal string (required) — deal_party.deal_party_commission_perc
  • dealPartyCommissionAmt: Decimal string (required) — deal_party.deal_party_commission_amt
  • bankAccountId: Integer (optional) — deal_party.bank_account_id
  • actorId: String — identifier for the user performing the operation

Inserts a single new deal_party row linking a party to a deal with its commission structure.

Step 1. Insert Deal Party Row ​

  • Source: Input parameters.
  • Action: INSERT one row into deal_party.
  • Logic:
    • Set deal_id = dealId.
    • Set party_id = partyId.
    • Set party_role_type_cd = partyRoleTypeCd.
    • Set deal_party_commission_flat_ind = dealPartyCommissionFlatInd.
    • Set deal_party_commission_perc = dealPartyCommissionPerc (decimal(7,4)).
    • Set deal_party_commission_amt = dealPartyCommissionAmt (decimal(15,2)).
    • Set bank_account_id = bankAccountId (if provided).
    • Set created_by = actorId, created_dt = now().
    • Set updated_by = actorId, updated_dt = now().

Side-effects:

  • None beyond the deal_party row itself.

Postconditions:

  • One new deal_party row exists linking the party to the deal.

2.5 Update Deal Party ​

Operation: updateDealParty

Trigger: User edits a party entry on a deal and saves.

Input Parameters:

  • dealPartyId: Integer (required) — deal_party.deal_party_id
  • Any subset of updatable fields: partyRoleTypeCd, dealPartyCommissionFlatInd, dealPartyCommissionPerc, dealPartyCommissionAmt, bankAccountId
  • actorId: String — identifier for the user performing the operation

Updates the deal_party row matching dealPartyId with the provided field changes.

Step 1. Update Deal Party Row ​

  • Source: deal_party (record matching dealPartyId).
  • Action: UPDATE deal_party.
  • Logic:
    • Apply any provided field changes.
    • Set updated_by = actorId, updated_dt = now().

Side-effects:

  • None beyond the deal_party row itself.

Postconditions:

  • The deal_party row reflects the updated field values.

2.6 Delete Deal Party ​

Operation: deleteDealParty

Trigger: User removes a party from a deal.

Input Parameters:

  • dealPartyId: Integer (required) — deal_party.deal_party_id

Deletes the deal_party row matching dealPartyId.

Step 1. Delete Deal Party Row ​

  • Source: deal_party (record matching dealPartyId).
  • Action: DELETE from deal_party.
  • Logic:
    • Delete the row where deal_party_id = dealPartyId.

Side-effects:

  • None beyond the deleted deal_party row.

Postconditions:

  • The deal_party row no longer exists.

2.7 Process Revenue Update ​

Operation: processRevenueUpdate

Trigger: Called after a sales block is created, either immediately inline or as a deferred batch job.

Input Parameters:

  • salesBlockId: Integer (required) — sales_block.sales_block_id

Processes a sales block to generate or update revenue items and billing items downstream.

Step 1. Fetch Sales Item for Block ​

  • Source: sales_item (record linked to salesBlockId).
  • Action: SELECT the sales_item for the given block.
  • Logic:
    • Abort with error if no sales item found for the block.

Step 2. Run Three-Layer Validation ​

  • Source: sales_item, payment_term, deal, party, code_master, revenue_items.
  • Action: Execute validation layers in order. On failure, update block status and abort.
  • Logic:
    • Layer 1 (Data): Verify all required fields on sales_item are present and non-null. Verify gross_amt and uta_commission_amt are non-negative. Verify |SUM(payment_term.gross_amt) - sales_item.gross_amt| <= 0.01.
    • Layer 2 (Consistency): If a revenue_items row already exists for this sales_item_ref (with current_item_ind = true), verify that immutable fields match: sales_item_ref, deal_id, uta_entity_id, client_entity_id, buyer_entity_id, currency_cd.
    • Layer 3 (Referential): Verify all foreign key references exist: deal.deal_id, party.party_id for client, buyer, and payment parties; code master values for uta_commission_type, rev_rec_style_cd, revenue_date_status_cd, sales_item_status_cd, currency_cd, and each payment term's due_date_status_cd.
    • If any layer fails: UPDATE sales_block.process_status_cd = 'F' and set error_description. Abort.

Step 3. Check for Existing Revenue Item ​

  • Source: revenue_items (row with sales_item_ref matching the block's sales item, current_item_ind = true).
  • Action: SELECT to determine create vs. update path.
  • Logic:
    • If a revenue_items row exists for this sales_item_ref: proceed to the Update path (Step 4a).
    • If no revenue_items row exists: proceed to the Create path (Step 4b).

Step 4a. Update Path — Update Existing Revenue Item ​

  • Source: Existing revenue_items row and updated sales_item data.
  • Action: Update the existing revenue_items row with changed field values.
  • Logic:
    • Apply changed fields: 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.
    • The revenue service handles versioning, schedule regeneration, and billing item updates internally.

Step 4b. Create Path — Create New Revenue Item ​

  • Source: sales_item fields from the block.
  • Action: INSERT into revenue_items and generate revenue schedules.
  • Logic:
    • Map sales_item fields to revenue_items fields (see Section 4.1).
    • Set current_item_ind = true.
    • Generate revenue_item_schedules based on rev_rec_style_cd.

Step 5. Create Billing Items (Create Path Only) ​

  • Source: payment_term rows for the block and the newly created revenue_items row.
  • Action: For each payment_term, INSERT one billing_item and two billing_item_detail rows.
  • Logic:
    • Map payment_term fields to billing_item (see Section 4.2).
    • For each billing_item, create exactly two billing_item_detail rows using the REV/PAY split calculation (see Section 4.3).
    • Set billing_item_status_cd = 'U', open_item_ind = true, current_item_ind = true.

Step 6. Update Sales Block Status ​

  • Source: sales_block (record matching salesBlockId).
  • Action: UPDATE sales_block.process_status_cd.
  • Logic:
    • Set process_status_cd = 'P' (Processed) on success.

Side-effects:

  • sales_block.process_status_cd is updated to 'P' on success or 'F' on failure.
  • On failure: sales_block.error_description is populated with the failure reason.

Postconditions:

  • sales_block.process_status_cd = 'P' on success.
  • On create path: one revenue_items row and associated schedules created; N billing_item rows and 2N billing_item_detail rows created.
  • On update path: the existing revenue_items row updated; downstream billing items updated by the revenue service.

2.8 Split Sales Item (Sales Pipeline) ​

Operation: splitSalesItemImmediate

Trigger: User initiates a payment term split from the Sales Pipeline screen.

Input Parameters:

  • paymentTermRef: String (required) — payment_term.payment_term_ref of the term to split
  • revenueItemId: Integer (required) — revenue_items.revenue_item_id of the associated revenue item
  • splitAmount: Decimal (required) — the portion to carve off; must be > 0 and < original term gross_amt
  • newItemDueDate: Date (required) — payment_term.due_dt for the versioned original's updated term

Splits a sales pipeline item directly, creating new revenue items and billing items for the split portion, and versioning the original with the remaining amount.

Step 1. Fetch and Validate Payment Term ​

  • Source: payment_term (record matching paymentTermRef).
  • Action: SELECT the payment term.
  • Logic:
    • Abort with error if not found.
    • Abort with error if splitAmount >= original gross_amt or splitAmount <= 0.

Step 2. Fetch Sales Item and Validate Single Term ​

  • Source: sales_item (via sales_block_id from the payment term).
  • Action: SELECT the sales item and verify the sales block has exactly one payment term.
  • Logic:
    • Abort with error if the sales item is not found.
    • Abort with error if the sales block's payment term count != 1.

Step 3. Calculate Derived Amounts ​

  • Source: Original gross_amt and uta_commission_perc from the sales item.
  • Action: Compute split amounts.
  • Logic:
    • remaining_amount = original gross_amt - splitAmount.
    • remaining_commission = remaining_amount * uta_commission_perc.
    • split_commission = splitAmount * uta_commission_perc.
    • Generate new references: sales_item_ref = REF-{random}, payment_term_ref = TERM-{random}.

Step 4. Create Split Portion (New Item) ​

  • Source: Original sales item fields and computed amounts (within transaction).
  • Action: INSERT sales_block, sales_item, payment_term for the split portion; then create revenue and billing items.
  • Logic:
    • Insert sales_block with process_status_cd = 'U'.
    • Insert sales_item with gross_amt = splitAmount, revenue_end_dt = today, new sales_item_ref, sales_item_ver = 1.
    • Insert payment_term with gross_amt = splitAmount, due_dt = today.
    • Create a new revenue_items row from the split sales item.
    • Create billing_item and billing_item_detail rows from the split payment term.

Step 5. Create Versioned Original (Remaining Amount) ​

  • Source: Original sales item fields and computed remaining amounts (within transaction).
  • Action: INSERT new sales_block, sales_item, payment_term rows for the versioned original; then update the existing revenue item.
  • Logic:
    • Insert new sales_block with process_status_cd = 'U'.
    • Insert new sales_item row: same sales_item_ref as original, sales_item_ver = original version + 1, gross_amt = remaining_amount, revenue_start_dt = today.
    • Insert new payment_term row: same payment_term_ref as original, payment_term_ver = original version + 1, gross_amt = remaining_amount, due_dt = newItemDueDate.
    • Update the existing revenue_items row with the remaining amount values.

NOTE

The sales pipeline split creates new rows for the versioned original rather than updating in place. Each version of a sales item exists as a separate row identified by sales_item_ref + sales_item_ver. This is the immutability pattern: once a sales block is created, its contents are never modified.

Side-effects:

  • The existing revenue_items row is updated with the remaining amount.
  • New revenue and billing items are created for the split portion.

Postconditions:

  • Two sales_block entries exist: one for the split portion, one for the versioned original.
  • Two sales_item rows share the original sales_item_ref (different sales_item_ver).
  • The split portion has its own new sales_item_ref.
  • Revenue and billing items reflect the split.

2.9 Update Sales Item (Sales Pipeline — Versioned) ​

Operation: updateSalesItem

Trigger: User edits a sales item from the Sales management screen, or a package operation modifies revenue items.

Input Parameters:

  • salesItemId: Integer (required) — sales_item.sales_item_id
  • Any subset of updatable fields from sales_item
  • paymentTerms: Array (optional) — updated payment terms with refs and amounts
  • actorId: String — identifier for the user performing the operation

Updates a sales item in the sales pipeline by creating a new version within a new sales block.

Step 1. Fetch Current Sales Item ​

  • Source: sales_item (record matching salesItemId).
  • Action: SELECT the current sales item row.
  • Logic:
    • Collect original field values for merging with updates.

Step 2. Create New Sales Block ​

  • Source: Input actorId.
  • Action: INSERT one row into sales_block (within transaction).
  • Logic:
    • Set process_status_cd = 'U'.

Step 3. Insert New Sales Item Version ​

  • Source: Original sales item fields merged with provided updates.
  • Action: INSERT one new row into sales_item.
  • Logic:
    • Set sales_block_id = new block's ID.
    • Set sales_item_ref = same as original (preserves identity across versions).
    • Set sales_item_ver = original version + 1.
    • Merge original field values with provided updates.
    • Recalculate uta_commission_amt = gross_amt * uta_commission_perc.

Step 4. Insert Payment Terms for New Version ​

  • Source: Provided paymentTerms input or original block's terms.
  • Action: INSERT payment_term rows into the new block.
  • Logic:
    • If paymentTerms provided: for each term with an existing ref, fetch the latest payment_term_ver and increment; for new terms, generate a TERM-{random} ref with version 1.
    • If paymentTerms not provided: copy all payment_term rows from the original sales_block_id into the new block, incrementing each payment_term_ver.

IMPORTANT

This procedure does NOT modify or delete the original sales_item row. The original remains as a historical record. The new version with the higher sales_item_ver becomes the current version. The concept of "current" is determined by finding the highest sales_item_ver for a given sales_item_ref.

Side-effects:

  • None to existing rows. The original sales_item and its payment_term rows are untouched.

Postconditions:

  • One new sales_block row exists with process_status_cd = 'U'.
  • One new sales_item row exists with sales_item_ver = original + 1.
  • N new payment_term rows exist in the new block.

2.10 Create Sales Item (Sales Pipeline — New) ​

Operation: createSalesItemInPipeline

Trigger: A new sales item is submitted directly to the sales pipeline.

Input Parameters:

  • All sales_item fields required for creation: entity refs, financial fields, status codes, dates
  • paymentTerms: Array (optional) — payment term specifications
  • actorId: String — identifier for the user performing the operation

Creates a brand-new sales item in the sales pipeline from scratch.

Step 1. Create New Sales Block ​

  • Source: Input actorId.
  • Action: INSERT one row into sales_block (within transaction).
  • Logic:
    • Set process_status_cd = 'U'.

Step 2. Insert Sales Item ​

  • Source: Input parameters.
  • Action: INSERT one row into sales_item.
  • Logic:
    • Set sales_block_id = new block's ID.
    • Generate sales_item_ref = REF-{random_alphanumeric}.
    • Set sales_item_ver = 1.
    • Set uta_commission_amt = gross_amt * uta_commission_perc.
    • Set all entity and status fields from input.

Step 3. Insert Payment Terms ​

  • Source: Provided paymentTerms array or default single-term logic.
  • Action: INSERT payment_term rows.
  • Logic:
    • If paymentTerms provided: insert each with a generated TERM-{random} ref, payment_term_ver = 1.
    • If paymentTerms not provided: create a single default term with full gross_amt, due_dt = today, due_date_status_cd = 'EST'.

Side-effects:

  • None beyond the inserted rows.

Postconditions:

  • One new sales_block row with process_status_cd = 'U'.
  • One new sales_item row with sales_item_ver = 1.
  • N new payment_term rows.

2.11 Sales Metadata Management ​

Operation: createSalesMetaData / updateSalesMetaData / deleteSalesMetaData

Trigger: System or user creates, modifies, or removes metadata entries associated with a sales item reference.

Input Parameters:

  • salesItemRef: String (required for create) — sales_meta_data.sales_item_ref
  • metaDataTypeCd: String (required for create) — sales_meta_data.meta_data_type_cd
  • metaDataValue: String (optional) — sales_meta_data.meta_data_value
  • metaDataDateValue: Date (optional) — sales_meta_data.meta_data_date_value
  • comment: String (optional) — sales_meta_data.comment
  • startDt: Date (optional) — sales_meta_data.start_dt
  • endDt: Date (optional) — sales_meta_data.end_dt
  • salesMetaDataId: Integer (required for update/delete by ID) — sales_meta_data.sales_meta_data_id
  • actorId: String (required for create) — identifier for the user performing the operation

CRUD operations on the sales_meta_data table, which stores extensible key-value pairs associated with a sales_item_ref.

Step 1. Create Metadata ​

  • Source: Input parameters.
  • Action: INSERT one row into sales_meta_data.
  • Logic:
    • Validate: sales_item_ref (min 1 char), meta_data_type_cd (min 1 char), created_by (min 1 char) are required.
    • Insert with all provided fields.

Step 2. Update Metadata ​

  • Source: sales_meta_data (record matching salesMetaDataId).
  • Action: UPDATE sales_meta_data.
  • Logic:
    • Validate input against update schema.
    • Apply provided field changes.

Step 3. Delete Metadata ​

  • Source: sales_meta_data.
  • Action: DELETE rows based on the specified granularity.
  • Logic:
    • By ID: delete the single row matching salesMetaDataId.
    • By sales item ref: delete all rows matching sales_item_ref.
    • By sales item ref and type: delete all rows matching both sales_item_ref and meta_data_type_cd.

Side-effects:

  • None beyond the sales_meta_data rows themselves.

Postconditions:

  • On create: one new sales_meta_data row exists.
  • On update: the row reflects the updated field values.
  • On delete: matching rows no longer exist.

2.12 Update Sales Block Status ​

Operation: updateSalesBlockStatus

Trigger: Called internally by processRevenueUpdate on success or failure, or manually by an administrator.

Input Parameters:

  • salesBlockId: Integer (required) — sales_block.sales_block_id
  • statusCd: String (required) — new process_status_cd value ('P' or 'F')
  • errorDescription: String (optional) — sales_block.error_description (used when statusCd = 'F')

Manually updates the processing status of a sales block.

Step 1. Update Sales Block Status ​

  • Source: sales_block (record matching salesBlockId).
  • Action: UPDATE sales_block.
  • Logic:
    • Set process_status_cd = statusCd.
    • Set error_description = errorDescription if statusCd = 'F'.

Side-effects:

  • None beyond the sales_block row itself.

Postconditions:

  • sales_block.process_status_cd reflects the new status.
  • sales_block.error_description is populated if status is 'F'.

3. Business Rules & Logic ​

3.1 Sales Item Immutability Pattern (Versioning) ​

Business rule: Sales items and payment terms in the sales pipeline are append-only. Once a row is written into a sales_block, it is never modified. Updates produce a new version row.

Data-level enforcement:

  • Read: Each row has a stable sales_item_ref (or payment_term_ref) that persists across all versions, and an incrementing sales_item_ver (or payment_term_ver).
  • Guard: The "current" version is determined by the highest sales_item_ver for a given sales_item_ref. No unique constraint prevents multiple version rows — the application selects the maximum.
  • Write: Updates insert new sales_item and payment_term rows in a new sales_block. The original rows remain as historical records.

3.2 Payment Terms Sum Constraint ​

Business rule: The sum of all payment terms for a sales item must equal the sales item's gross amount, within a tolerance of 0.01.

Data-level enforcement:

  • Read: SUM(payment_term.gross_amt) vs. sales_item.gross_amt.
  • Guard: If |SUM - gross_amt| > 0.01, the processing operation is rejected with an error message.
  • Write: Not applicable — this rule is a precondition.

3.3 Revenue Item Existence Check (Create vs. Update) ​

Business rule: When processing a sales block, if a revenue item already exists for the sales_item_ref, the system updates it rather than creating a duplicate.

Data-level enforcement:

  • Read: revenue_items where sales_item_ref = the sales item's ref and current_item_ind = true.
  • Guard: None — the check itself determines the code path.
  • Write: On existing: UPDATE revenue_items and cascade to billing items. On new: INSERT revenue_items, schedules, billing_item, and billing_item_detail.

This check is the mechanism by which repeated syncs update existing downstream records rather than creating duplicates.

3.4 Commission Percentage Rules ​

Business rule: Commission percentages are expressed as decimal fractions (not whole percentages). A 10% commission is stored as 0.10. Deal-party commissions use a wider decimal precision than sales-item-level commissions.

Data-level enforcement:

  • Read: deal_party.deal_party_commission_perc (decimal(7,4)); sales_item.uta_commission_perc (decimal(5,4)).
  • Guard: uta_commission_perc must be between 0 and 1 (inclusive). Enforced at the application validation layer.
  • Write:
    • If uta_commission_type = 'P': set uta_commission_amt = gross_amt * uta_commission_perc.
    • If uta_commission_type = 'F': use uta_commission_amt directly; uta_commission_perc is ignored.

3.5 Sales Block Processing Lifecycle ​

Business rule: Each sales block moves through a one-way processing lifecycle. Once processed or failed, the status does not change except by explicit administrator action.

Data-level enforcement:

  • Read: sales_block.process_status_cd.
  • Guard: No formal DB constraint prevents re-processing, but application logic treats 'P' blocks as final.
  • Write:
    • 'U' set at creation.
    • 'P' set after successful revenue and billing item generation.
    • 'F' set if any validation layer fails or processing throws an error; error_description records the reason.
    • 'X' set manually for blocks that should be intentionally skipped.

3.6 Single Payment Term Requirement for Split ​

Business rule: A sales item can only be split if it has exactly one payment term. Items with multiple payment terms cannot be split.

Data-level enforcement:

  • Read: Count of payment_term rows for the item's sales_block_id.
  • Guard: If count != 1, reject the split operation with an error message.
  • Write: Not applicable — this rule is a precondition.

4. Field Mapping & Transformation ​

4.1 Sales Item to Revenue Item Mapping ​

When a sales_item from the pipeline is processed into a revenue_items record by 2.7 Process Revenue Update:

Source Table.FieldTarget Table.FieldTransform
sales_item.uta_entity_idrevenue_items.uta_entity_idCopied as-is
sales_item.agent_group_idrevenue_items.agent_group_idCopied as-is
sales_item.sales_item_refrevenue_items.sales_item_refCopied as-is
sales_item.sales_item_status_cdrevenue_items.revenue_item_status_cdCopied as-is (defaults to 'U' if null)
sales_item.namerevenue_items.revenue_item_nameCopied as-is
sales_item.revenue_date_status_cdrevenue_items.revenue_item_date_status_cdCopied as-is (defaults to 'U' if null)
sales_item.deal_idrevenue_items.deal_idParsed to integer
sales_item.client_entity_idrevenue_items.client_idCopied as-is
sales_item.contracted_party_idrevenue_items.contracted_party_idCopied as-is
sales_item.buyer_entity_idrevenue_items.buyer_idCopied as-is
sales_item.department_idrevenue_items.department_idCopied as-is
sales_item.currency_cdrevenue_items.currency_cdCopied as-is
sales_item.gross_amtrevenue_items.revenue_item_gross_amtCopied as-is
sales_item.uta_commission_typerevenue_items.revenue_item_commission_flat_ind'FLAT' → true; else false
sales_item.uta_commission_percrevenue_items.revenue_item_commission_percCopied as-is
sales_item.uta_commission_amtrevenue_items.revenue_item_commission_amtCopied as-is
sales_item.revenue_start_dtrevenue_items.revenue_item_start_dtCopied as-is
sales_item.revenue_end_dtrevenue_items.revenue_item_end_dtCopied as-is
sales_item.rev_rec_style_cdrevenue_items.revenue_item_rec_style_cdCopied as-is
—revenue_items.current_item_indDefaulted to true

4.2 Payment Term to Billing Item Mapping ​

When a payment_term is processed into a billing_item by 2.7 Process Revenue Update:

Source Table.FieldTarget Table.FieldTransform
payment_term.payment_term_refbilling_item.payment_term_refCopied as-is
payment_term.due_dtbilling_item.billing_item_due_dtCopied as-is
payment_term.due_dtbilling_item.billing_item_aging_dtCopied as-is (same as due date)
payment_term.due_date_status_cdbilling_item.billing_item_due_dt_status_cdCopied as-is (defaults to 'U')
payment_term.namebilling_item.billing_item_nameCopied as-is
payment_term.payment_party_idbilling_item.collection_party_idCopied as-is
—billing_item.billing_item_status_cdDefaulted to 'U' (Unprocessed)
—billing_item.open_item_indDefaulted to true
—billing_item.current_item_indDefaulted to true
—billing_item.collection_style_cd'BUYER' if payment_party_id = buyer; else 'CLIENT'

Context fields carried from the sales item context onto each billing_item:

Source Table.FieldTarget Table.FieldTransform
sales_item.deal_idbilling_item.deal_idCopied as-is
sales_item.uta_entity_idbilling_item.uta_entity_idCopied as-is
sales_item.agent_group_idbilling_item.agent_group_idCopied as-is
sales_item.buyer_entity_idbilling_item.buyer_idCopied as-is
sales_item.client_entity_idbilling_item.client_idCopied as-is
sales_item.contracted_party_idbilling_item.contracted_party_idCopied as-is
sales_item.currency_cdbilling_item.billing_item_currency_cdCopied as-is
sales_item.department_idbilling_item.department_idCopied as-is
sales_item.service_country_cdbilling_item.service_country_cdCopied as-is
sales_item.service_state_cdbilling_item.service_state_cdCopied as-is

4.3 Billing Item Detail Split Calculation ​

For each billing_item, two billing_item_detail rows are created. Given gross_amt (payment term gross) and commission_perc (UTA commission percentage as decimal fraction):

REV detail (UTA's commission):

Source Table.FieldTarget Table.FieldTransform
—billing_item_detail.billing_item_detail_type_cdDefaulted to 'REV'
payment_term.gross_amtbilling_item_detail.billing_item_detail_gross_amtCopied as-is
sales_item.uta_commission_percbilling_item_detail.billing_item_detail_percentCopied as-is
—billing_item_detail.billing_item_detail_amtgross_amt * commission_perc
—billing_item_detail.billing_item_detail_tax_amtDefaulted to 0.00
—billing_item_detail.billing_item_detail_total_amt= billing_item_detail_amt
—billing_item_detail.posting_status_cdDefaulted to 'U'

PAY detail (client's share):

Source Table.FieldTarget Table.FieldTransform
—billing_item_detail.billing_item_detail_type_cdDefaulted to 'PAY'
payment_term.gross_amtbilling_item_detail.billing_item_detail_gross_amtCopied as-is
—billing_item_detail.billing_item_detail_percent1 - commission_perc
—billing_item_detail.billing_item_detail_amtgross_amt * (1 - commission_perc)
—billing_item_detail.billing_item_detail_tax_amtDefaulted to 0.00
—billing_item_detail.billing_item_detail_total_amt= billing_item_detail_amt
—billing_item_detail.posting_status_cdDefaulted to 'U'

NOTE

Tax amounts are initially zero and are populated later by the withholding tax service based on contracted_party_id, uta_entity_id, and service_country_cd.

4.4 Original to Split Sales Item Mapping ​

When a sales item is split via 2.8 Split Sales Item:

Source Table.FieldSplit-Off Item (New)Versioned Original (Remaining)
sales_item_refNEW reference generatedSAME as original
sales_item_ver1Original version + 1
gross_amtsplit_amountoriginal_gross - split_amount
uta_commission_amtsplit_amount * commission_percremaining * commission_perc
revenue_start_dtOriginal start date (preserved)Today (PST)
revenue_end_dtToday (PST)Original end date
All other entity and status fieldsCopied from originalCopied from original

5. Cross-References ​

DocumentRelationship
Deals, Sales Items and Payment Terms Data ModelSchema definitions, field types, constraints, code master values, and entity relationships for all tables in this domain.
Billing Items Data Modelbilling_item.deal_id → deal.deal_id. billing_item.payment_term_ref matches payment_term.payment_term_ref by value. revenue_items.sales_item_ref matches sales_item.sales_item_ref by value. Downstream tables created by 2.7 Process Revenue Update.
Parties Data Modeldeal_party.party_id → party.party_id. sales_item.client_entity_id, sales_item.buyer_entity_id, sales_item.contracted_party_id, and payment_term.payment_party_id all reference party.party_id.
Tax & Withholding Data Modelsales_item.service_country_cd and service_state_cd drive tax jurisdiction determination. Tax deductions are calculated at billing item creation time based on contracted_party_id, uta_entity_id, and service_country_cd.

Confidential. For internal use only.

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