Reconciliation Report (Excel)

This report shows mapping between claims and remittance advices. Report shows all claims submitted by the provider no matter whether they have payer replies (corresponding Remittance.Advice transactions) or not.

Only latest claims are taken into consideration. That means all data except Paid Amount is shown from the latest claim vrsion: diagnoses, denial code, claimed amount, etc.
Claim duplicates are not reflected even if they are submitted by mistake.
Remittance advice duplicates are not counted too.

Claims Reconciliation Report

The report contains the only worksheet: Reconciliation Summary Report. Data aggregation is performed on the claim level.

Data in the worksheet is sorted by:

Sender ID
Claim ID
Submission Date.

Table below describes columns presented on this worksheet.

Column	Transaction Field	Description
First CS Date	Header.TransactionDate	Transaction date and time of the first Claim.Submission transaction.
Last CS Date	Header.TransactionDate	Transaction date and time of the latest Claim.Submission transaction where a claim is either correction or internal complaint. If a claim has no resubmission, then it equals First CS Date.
First RA Date	Header.TransactionDate	Transaction date and time of the first Remittance.Advice transaction.
Last RA Date	Header.TransactionDate	Transaction date and time of the latest Remittance.Advice transaction.
Claim ID	Claim.ID	Unique identifier assigned to a claim by the provider.
Claim ID Payer	Claim.IDPayer	Unique identifier assigned to a claim by the payer.
Pt Visit Date	Encounter.Start	Encounter start date.
Pt Visit Date Time	Encounter.Start	Encounter start date and time.
Sender ID	Header.SenderID	License number of a facility that is the sender of Claim.Submission transaction.
Reciever ID	Header.ReceiverID	License number of an insurance company or TPA that is the receiver of Claim.Submission transaction.
Payer ID	Claim.PayerID	License number of an insurance company that is the payer of a claim.
Provider ID	Claim.ProviderID	License number of the provider.
Member ID	Claim.MemberID	Insurance card number of the patient.
MRN	Encounter.PatientID	Patient Medical Record Number assigned by the provider.
Original Claimed Amount	Claim.Net	Net amount billed by the provider to the payer for a claim in the first Claim.Submission transaction.
Current Claimed Amount	Claim.Net	Net amount for a claim from the latest Claim.Submission transaction.
Patient Share	Claim.PatientShare	Any fee that the payer is expecting the provider to collect from the patient for a claim.
Pending Amount		Calculates as sum of Pending Amount for all activities in a claim.
Original Paid Amount		Amount paid by the payer for a claim in the first Remittance.Advice transaction. Calculates as sum of Paid Amount for all activities in a claim.
Paid Amount		Amount paid by the payer for a claim latest Remittance.Advice transaction. Calculates as sum of Paid Amount for all activities in a claim.
Denied Amount		Amount denied by the payer for a claim. Calculated as sum of (Current Claimed Amount – Paid Amount) for all activities in a claim.
Write Off Amount		Write Off Amount as per the latest Write Off Report. Calculated as sum of Write Off Amount for all activities in a claim.
Final Denied Amount		Final amount denied by the payer for a claim. Calculated as sum of (Denied Amount – Write Off Amount) for all activities in a claim.
Denied Clearing Error		Calculates as sum of Denied Clearing Error for all activities in a claim.
Processed Amount		Net amount for a claim according to the latest Remittance.Advice transaction. Calculates as sum of (Paid Amount + Denied Amount) for all activities in a claim.
Principal Diagnosis	Diagnosis.Code	Principal diagnosis code.
Diagnosis Text		Principal diagnosis description.
Secondary Diagnosis 1...N	Diagnosis.Code	Secondary diagnosis code. This value is taken from diagnosis that has Diagnosis.Type <> Principal.
Diagnosis Text		Secondary diagnosis description.
Payment Status		Paid — a claim is fully paid (Paid Amount >= Processed Amount); Partly Paid — a claim is partly paid (Paid Amount < Processed Amount); Denied — a claim is fully denied (Paid Amount = 0); In Process — remittance advice is not received for the claim: a new claim is submitted and no remittance advice is yet received; a claim resubmission (correction or internal complaint) is submitted and no remittance advice is yet received; in this case Paid Amount may have value from previous claim submissions; Written Off — a claim is written off using Write Off Registration.
Claim Status		Original — if this is the initial claim submission; Resubmission 1 — if this is the first claim resubmission; Resubmission N — if this is N claim resubmission.
Missed Take Back		Yes if the following conditions are met: the latest claim resubmission is correction; the previous claim cycle has Paid Amount > 0; there is no remittance advice with TKBK-001 or TKBK-002 denial code for corresponding activities. No otherwise.

Activities Reconciliation Report

The report contains only one worksheet: Reconciliation Summary Report. Data aggregation is performed on the activity level.

Table below describes columns in the report.

Column	Transaction Field	Description
First CS Date	Header.TransactionDate	Transaction date and time of the first Claim.Submission transaction.
Last CS Date	Header.TransactionDate	Transaction date and time of the latest Claim.Submission transaction where a claim is either correction or internal complaint. If a claim has no resubmission, then it equals First CS Date.
First RA Date	Header.TransactionDate	Transaction date and time of the first Remittance.Advice transaction.
Last RA Date	Header.TransactionDate	Transaction date and time of the latest Remittance.Advice transaction.
Rx Number	Observation.Value	Rx Number; value is taken for observation with Observation.Code = Prescription No.
Fill Counter	Observation.Value	Fill Counter; value is taken for observation with Observation.Code = Refills or Fill Counter.
Code	Activity.Code	Activity code specified by activity type.
Type		Activity type.
Description		Activity code description.
Claim ID	Claim.ID	Unique identifier assigned to a claim by the provider.
Claim ID Payer	Claim.IDPayer	Unique identifier assigned to a claim by the payer.
Activity ID	Activity.ID	Unique identifier of an activity within a claim.
Auth. Number	Activity.PriorAuthorizationID	Prior authorization number.
Auth. Approval Date		Authorization approval date. Always N/A until future versions.
Service Date	Activity.Start	Date of the provided service.
Service Date Time	Activity.Start	Date and time of the provided service.
Ord. Clinician License	Activity.OrderingClinician	License number of the clinician who ordered an activity or referred the patient for an activity.
Ord. Clinician Name		Full name of the clinician who ordered an activity or referred the patient for an activity.
Disp. Clinician License	Activity.Clinician	License number of the clinician who provided an activity (treatment or care) for the patient.
Disp. Clinician Name		Full name of the clinician who provided an activity (treatment or care) for the patient.
Sender ID	Header.SenderID	License number of a facility that is the sender of Claim.Submission transaction.
Receiver ID	Header.ReceiverID	License number of an insurance company or TPA that is the receiver of Claim.Submission transaction.
Payer ID	Claim.PayerID	License number of an insurance company that is the payer of a claim.
Provider ID	Claim.ProviderID	License number of the provider.
Member ID	Claim.MemberID	Insurance card number of the patient.
MRN	Encounter.PatientID	Patient Medical Record Number assigned by the provider.
Sales Price		Sales price for the drug. It has a value only if Activity.Type = 5.
Quantity	Activity.Quantity	Dispensed quantity (number of units) for an activity.
Original Claimed Amount	Activity.Net	Net amount for an activity from the first Claim.Submission transaction.
Current Claimed Amount	Activity.Net	Net amount for an activity from the latest Claim.Submission transaction.
Patient Share	Activity.PatientShare	Any fee that the payer is expecting the provider to collect from the patient for an activity.
Pending Amount		If the latest claim version does not have corresponding Remittance.Advice, then Pending Amount = Current Claimed Amount – Claimed Amount from the previous claim version. It is negative if Current Claimed Amount < Claimed Amount. Pending Amount = Claimed Amount if there is only initial claim submission without Remittance.Advice. Otherwise, if the latest claim version has corresponding Remittance.Advice, then Pending Amount = 0.00 to indicate that payer has processed the initial claim.
Original Paid Amount	Activity.PaymentAmount	Amount paid by the payer for an activity. Calculates as sum of Paid Amount in all related Remittance.Advice transactions for an activity (including those with TKBK-001 or TKBK-002 denial code).
Paid Amount	Activity.PaymentAmount	Corrected amount paid by the payer for an activity. It equals Paid Amount only from the latest Remittance.Advice transaction if the following conditions are met: the latest claim resubmission is correction; the previous claim cycle has Paid Amount > 0; there is no remittance advice with TKBK-001 or TKBK-002 denial code for corresponding activities. Otherwise, it equals Original Paid Amount.
Denied Amount		Amount denied by the payer for an activity. Calculated as Current Claimed Amount – Paid Amount.
Write Off Amount		Write Off Amount as per the latest Write Off Report.
Write Off Date		Date when the amount is written off as per the latest Write Off Report.
Final Denied Amount		Amount denied by the payer for an activity. Calculates as Denied Amount – Write Off Amount.
Denied Clearing Error		In case -0.50 <= Denied Amount < 0.00: Denied Clearing Error = Denied Amount, and Denied Amount = 0.00. Otherwise the column is empty.
Processed Amount		Net amount for an activity processed by the payer. Calculates as Paid Amount + Denied Amount.
Denial Code	Activity.DenialCode	Denial code for a rejected activity. Even if the latest Remittance.Advice transaction has Denial Code, it is not shown in the report if Paid Amount = Claimed Amount and Paid Amount <> 0. If the latest Remittance.Advice transaction has no Denial Code but Paid Amount <> Claimed Amount, then Denial Code is taken from the second to the latest remittance. If that has no Denial Report either, then Denial Code is taken from the previous remittance and so on. Take Back Denial Code is either TKBK-001 or TKBK-002. If an activity has a non-Take Back Denial Code and Take Back Denial Code, then a non-Take Back Denial Code is shown. If an activity has only a Take Back Denial Codes, then any of them is shown.
Denial Reason		Denial code description.
Principal Diagnosis	Diagnosis.Code	Principal diagnosis code.
Diagnosis Text		Principal diagnosis description.
Secondary Diagnosis 1...N	Diagnosis.Code	Secondary diagnosis code. This value is taken from diagnosis that has Diagnosis.Type <> Principal.
Diagnosis Text		Secondary diagnosis description.
Payment Status		Paid — a claim is fully paid (Paid Amount >= Processed Amount); Partly Paid — a claim is partly paid (Paid Amount < Processed Amount); Denied — a claim is fully denied (Paid Amount = 0); In Process — remittance advice is not received for the claim: a new claim is submitted and no remittance advice is yet received; a claim resubmission (correction or internal complaint) is submitted and no remittance advice is yet received; in this case Paid Amount may have value from previous claim submissions; Written Off — a claim is written off using Write Off Registration.
Claim Status		Original — if this is the initial claim submission; Resubmission 1 — if this is the first claim resubmission; Resubmission N — if this is N claim resubmission.
Missed Take Back		Yes if the following conditions are met: the latest claim resubmission is correction; the previous claim cycle has Paid Amount > 0; there is no remittance advice with TKBK-001 or TKBK-002 denial code for corresponding activities. No otherwise.