Claim Submissions History Report (Excel)
- vickyp
- Katerina
Claim Submissions History Report shows the history of all submitted claims and received remittance advice transactions on both claim and activity levels. It can be used to cross check other reports or reports generated by other systems to make sure data is consistent across all data sources.
Data is grouped by months using Header.TransactionDate from the respective transaction. The report contains the following worksheets:
- CS Claims History
- CS Activities History
- RA Claims History
- RA Activities History
CS Claims History worksheet
The worksheet contains history of all claims submitted by the provider in Claim.Submission transactions.
The table below describes columns presented on this worksheet.
| Column | CS Field | Description |
|---|---|---|
| File Name | Name of Claim.Submission transaction. | |
| Submission Date | Header.TransactionDate | Transaction date of Claim.Submission transaction. |
| Sender ID | Header.SenderID | License number of a facility that is the sender of Claim.Submission transaction. |
| Sender Name | Organization name 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. |
| Receiver Name | Organisation name 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. |
| Payer Name | Organisation name of an insurance company that is the payer of a claim. | |
| 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. |
| Member ID | Claim.MemberID | Insurance card number of the patient. |
| MRN | Encounter.PatientID | Patient Medical Record Number assigned by the provider. |
| Encounter Start | Encounter.Start | Encounter start date and time. |
| Encounter End | Encounter.End | Encounter end date and time. If a claim has more than one encounter, Encounter.End from the first encounter is used. |
| Claimed Amount | Claim.Net | Net amount billed by the provider to the payer for a claim. |
| Is Resubmitted | Yes if a claim has been resubmitted in another transaction. In this case, the report is expected to have another claim with Resubmission Type on the next line. | |
| Resubmission Type | Resubmission.Type | Type of resubmission. If a claim is not a resubmission, then the column is empty. |
| Is Duplicate | False if a claim is not a duplicate; |
Data in the worksheet is sorted by:
- Submission Date, descending;
- Claim ID, ascending.
CS Activities History worksheet
The worksheet contains a history of all claim activities submitted by the provider in Claim.Submission transactions.
The table below describes columns presented on this worksheet.
| Column | CS Field | Description |
|---|---|---|
| File Name | Name of Claim.Submission transaction. | |
| Submission Date | Header.TransactionDate | Transaction date of Claim.Submission transaction. |
| Sender ID | Header.SenderID | License number of a facility that is the sender of Claim.Submission transaction. |
| Sender Name | Organisation name 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. |
| Receiver Name | Organisation name 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. |
| Payer Name | Organisation name of an insurance company that is the payer of a claim. | |
| 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. |
| Member ID | Claim.MemberID | Insurance card number of the patient. |
| MRN | Encounter.PatientID | Patient Medical Record Number assigned by the provider. |
| Encounter Start | Encounter.Start | Encounter Start Date and Time. If a claim has more than one encounter, then this value is taken from the earliest encounter. |
| Encounter End | Encounter.End | Encounter End Date and Time. |
| Activity ID | Activity.ID | Unique identifier of activity within a claim. |
| Code | Activity.Code | Activity code specified by activity type. |
| Description | Activity code description. | |
| Auth. Number | Activity.PriorAuthorizationID | Prior authorization number. |
| Service Date | Activity.Start | Start date of an activity. |
| Claimed Amount | Activity.Net | Net amount billed by the provider to the payer for an activity. |
| Ordering Clinician | Activity.OrderingClinician | Ordering clinician license. |
| Ordering Clinician Name | Name of the ordering clinician. | |
| Clinician | Activity.Clinician | Clinician license. |
| Clinician Name | Name of the clinician. | |
| Is Resubmitted | The column has the same value as a related claim on Claim Submissions History Report (Excel)#CS Claims History Worksheet. | |
| Resubmission Type | Resubmission.Type | The column has the same value as a related claim on Claim Submissions History Report (Excel)#CS Claims History Worksheet. |
| Is Duplicate | False if a related claim is not a duplicate; True if a relaed claim is a duplicate. |
Data in the worksheet is sorted by:
- Submission Date, descending;
- Claim ID, ascending;
- Activity ID, ascending.
RA Claims History worksheet
The worksheet contains a history of all claims submitted by the payer in Remittance.Advice transactions.
The table below describes columns presented on this worksheet.
| Column | RA Field | Description |
|---|---|---|
| File Name | Name of Remittance.Advice transaction. | |
| Submission Date | Header.TransactionDate | Transaction date of Remittance.Advice transaction. |
| Sender ID | Header.SenderID | License number of an insurance company or TPA that is the sender of Remittance.Advice transaction. |
| Sender Name | Organisation name of an insurance company or TPA that is the sender of Remittance.Advice transaction. | |
| Receiver ID | Header.ReceiverID | License number of a facility that is the receiver of Remittance.Advice transaction. |
| Receiver Name | Organisation name of a facility that is the receiver of 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. |
| Member ID | Claim.MemberID * | Insurance card number of the patient. |
| MRN | Encounter.PatientID * | Patient Medical Record Number assigned by the provider. |
| Encounter Start | Encounter.Start | Encounter Start Date and Time. This value is taken from the respective Claim.Submission transaction. If a claim has more than one encounter, then this value is taken from the earliest encounter. |
| Encounter End | Encounter.End | Encounter End Date and Time. This value is taken from the respective Claim.Submission transaction. |
| Processed Amount | Sum of all Activity.Net in a claim. | |
| Paid Amount | Sum of all Activity.PaymentAmount in a claim. | |
| Settlement Date | Claim.DateSettlement | Date the payer settles a claim. |
| Payment Reference | Claim.PaymentReference | Unique identifier for the payment transaction (cheque number, bank transfer number, payment voucher number). |
| Is Duplicate | False if a claim is not a duplicate; True if a claim is a duplicate. |
* The value is taken from the respective Claim.Submission transaction.
Data in the worksheet is sorted by:
- Submission Date, descending;
- Claim ID, ascending.
RA Activities History worksheet
The worksheet contains a history of all claim activities submitted by the payer in Remittance.Advice transactions.
The table below describes columns presented on this worksheet.
| Column | RA Field | Description |
|---|---|---|
| File Name | Name of Remittance.Advice transaction. | |
| Submission Date | Header.TransactionDate | Transaction date of Remittance.Advice transaction. |
| Sender ID | Header.SenderID | License number of an insurance company or TPA that is the sender of Remittance.Advice transaction. |
| Sender Name | Organisation name of an insurance company or TPA that is the sender of Remittance.Advice transaction. | |
| Receiver ID | Header.ReceiverID | License number of a facility that is the receiver of Remittance.Advice transaction. |
| Receiver Name | Organisation name of a facility that is the receiver of 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. |
| Member ID | Claim.MemberID * | Insurance card number of the patient. |
| MRN | Encounter.PatientID * | Patient Medical Record Number assigned by the provider. |
| Encounter Start | Encounter.Start | Encounter Start Date and Time. This value is taken from the respective Claim.Submission transaction. If a claim has more than one encounter, then this value is taken from the earliest encounter. |
| Encounter End | Encounter.End | Encounter End Date and Time. This value is taken from the respective Claim.Submission transaction. |
| Activity ID | Activity.ID | Unique identifier of activity within a claim. |
| Code | Activity.Code | Activity code specified by activity type. |
| Description | Activity code description. | |
| Auth. Number | Activity.PriorAuthorizationID * | Prior authorization number. |
| Service Date | Activity.Start | Start date of an activity. |
| Processed Amount | Activity.Net | Net amount billed by the provider to the payer for an activity. |
| Paid Amount | Activity.PaymentAmount | Amount paid by the payer for an activity. |
| Denial Code | Activity.DenialCode | Denial code for a rejected activity. |
| Denial Reason | Denial code description. | |
| Ordering Clinician | Activity.OrderingClinician | Ordering clinician license. Note: This value is taken from the respective Claim.Submission transaction. |
| Ordering Clinician Name | Name of the ordering clinician. | |
| Clinician | Activity.Clinician | Clinician license. Note: This value is taken from the respective Claim.Submission transaction. |
| Clinician Name | Name of the clinician. | |
| Settlement Date | Claim.DateSettlement | Date the payer settles a claim. |
| Payment Reference | Claim.PaymentReference | Unique identifier for the payment transaction (cheque number, bank transfer number, payment voucher number). |
| Is Duplicate | False if a related claim is not a duplicate; True if a related claim is a duplicate. |
* The value is taken from the respective Claim.Submission transaction.
Data in the worksheet is sorted by:
- Submission Date, descending;
- Claim ID, ascending.
- Activity ID in ascending order.