When the Archive Data task fails, the problem can usually be fixed following the steps below:
First, identify the practice where the problematic claim belongs to. This way, you’ll be able to locate the specific ERA batch in Billing from where the faulty transactions were posted.
Transactions are considered faulty if they are listed in the transaction_detail table with claim_id of one claim, but with claim_detail_id belonging to another claim’s claim line. In such situations, the Archive Data task encounters a problem with duplicate primary keys and crashes.
Therefore, what we need to do is to fix transactions that have non-matching claim_id and claim_detail_id.
Next, we need to take a closer look at ERA and posted transactions.
The fix involves two steps.
Step 1. Determine the type of the error
Several event scenarios are possible here:
1) The simplest case – claim_id got mixed up.
Suggested fix: find the claim_id of the claims to which the claim line with this particular claim_detail_id belongs, and insert the correct claim_id of the claim corresponding to these claim lines for incorrect transactions
2) Two claims of the same patient have identical claim lines
In this situation, we have a patient with two claims that have identical claim lines (including dos, cpt, etc.), and ERA payments have been received for both these claims. Sometimes in such situations the two ERA payments are posted to different claims (we can see transactions with different claim_ids in transaction_detail), but to the same claim_detail_id. As a result, we have transactions with correct claim_ids, but the second claim (with the identical claim line) hasclaim_detail_id of the first claim.
Suggested fix: for the transactions in the second claim, specify the correct claim_detail_id of the second claim.
3) Transactions were sent to ERA to a non-existent claim line.
In some cases, payments are sent to ERA to a claim line that does not exist. Such situations can be recognized quite easily by reviewing ERA payments in Payment Application in Billing and then matching the CPT codes of the claim lines to which the ERA payments were posted against the CPT codes of the claim lines that are actually present in the patient’s claim.
Suggested Fix: simply delete the transactions sent to non-existent claim lines from the database (it’s unclear what else could be done about them - and they shouldn’t be there anyway).
4) One patient has two claims with identical claim lines. All correctly processed ERA transactions are present, but there are also some incorrect transactions with claim_id of the first claim and with claim_detail_id of the second claim
This situation is similar to the one described in 2), with the only exception that in this case we already have correctly processed ERA transactions – and we also have transactions posted with claim_id of the first claim andclaim_detail_id of the second claim. In this situation, all correct transactions have already been posted, so all we need to do is simply delete the incorrect transactions from the database.
5) In ERA, a payment to an existing claim line of a patient claim is listed among payments for a different claim of the patient.
This situation is very rare.
Suggested fix: specify correct claim_id in transaction_detail
Step 2. Restart the Archive Data task
Now that all incorrect transactions are fixed, you need to restart the Archive Data task that will move transactions from the transactions and transaction_detail tables to transactions_archive and transaction_detail_archive archive tables.
When restarting the Archive Data task, make sure to check if there are any other tasks running in the task manager.
You may find one of the following tasks running in the Task Manager: Create Billing Followup SummaryData, Create Patient Statements, Create ANSI Files, Update ANSI File Statistics, Process ERA Old, Archive ERA processing Details, Update Stats on Keys Tables, etc. Do not restart the Archive Data task while any of these tasks are running, as this may affect the performance and even cause deadlocks.
The Archive Data task usually takes about 7 minutes, so when restarting this task, make sure to select a time slot when there are no tasks running in task manager or scheduled to start in the next 10-15 minutes (the latter is just in case).