Multi-Entity Time Ledger WIP Reconciliation and Batch Settlement Script

Role: Staff Database Performance Engineer specializing in financial ledger integrity and high-volume reconciliation pipelines.

Context

• Target database engine: {{database_engine}}
• WIP transaction source: {{wip_ledger_table}}
• Settlement header master table: {{billing_header_table}}
• Multi-entity partitioning attribute: {{subsidiary_id_column}}
• Batch commit chunking limit: {{settlement_batch_size}}
• Discrepancy currency tolerance: {{reconciliation_tolerance}}

Task

Develop an idempotent, performance-tuned {{database_engine}} reconciliation procedure that matches unbilled WIP in {{wip_ledger_table}} against draft billing items in {{billing_header_table}}, settles matched balances in chunks of {{settlement_batch_size}}, and logs rounding variances exceeding {{reconciliation_tolerance}} by {{subsidiary_id_column}}.

Method

1. Define transaction boundaries, isolation levels, and explicit error handling structures tailored for {{database_engine}}.
2. Create temporary staging tables to capture candidate unbilled ledger rows without table lock escalation.
3. Compute running totals and cumulative disbursements partitioned by project, fee-earner, and {{subsidiary_id_column}}.
4. Join draft billing lines against WIP balances, calculating net billable amount, sales tax offset, and discount absorption.
5. Evaluate discrepancy deltas; flag records where absolute discrepancy exceeds {{reconciliation_tolerance}} into an audit ledger.
6. Execute chunked atomic updates in batch loops of {{settlement_batch_size}} to prevent rollback segment exhaustion.
7. Apply status transitions to reconciled records in {{wip_ledger_table}} and insert balanced reconciliation headers into {{billing_header_table}}.
8. Return execution performance telemetry including duration, rows settled, and discrepancy exception counts.

Constraints

• MUST be written as an idempotent, re-runnable procedural script in {{database_engine}}.
• MUST use explicit transaction management (BEGIN TRANSACTION, COMMIT, ROLLBACK) with fine-grained error capture.
• MUST NOT leave open cursors or table-level exclusive locks during batch processing.
• MUST flag all variances greater than {{reconciliation_tolerance}} without halting the batch run.

Output format

1. Procedure Definition: Full DDL script creating the stored procedure with explicit parameters.
2. Table DDL Prerequisites: Minimal schema setup for audit and staging structures.
3. Execution Harness: Sample driver script demonstrating parameter invocation and return verification.

Self-review

• Ensure transaction batch looping contains explicit exit conditions to prevent infinite execution.
• Verify deadlocking risks are mitigated via deterministic index ordering across {{subsidiary_id_column}}.
• Validate that rounding logic aligns precisely with the {{reconciliation_tolerance}} boundary test.