Notifications
Clear all
Topic starter
Hello, is anyone familiar with the tables containing claims information in CPS 10.1.3? I'm trying to build a custom report and i cannot seem to find the tables that are being used in the database diagram. I've written many reports out of the system for other purposes and none of them have been this complicated just to find the data. Any advice would be appreciated.
I guess just to give you a better picture of what i'm trying to write, i am attempting to build a report that shows unpaid claims. Basicaly any claims that have gone out to insurance but have not been paid yet.
Thanks in advance.
Posted : December 19, 2014 9:51 am
What exactly are you trying to pull for your report?
Posted : December 19, 2014 9:54 am
Topic starter
I'm not sure i follow your question fully, but basically when you go into the "Billing" screen from the main CPS menu and view either Paper or Electronic claims, i am trying to build a report to list of any claims from that screen that is unpaid. I've found a few tables such as EDIClaim and EDIClaimFile but none of the tables i have found show the Billing Status field. I have located the "BillingStatus" tablename within the MedLists table but cannot find where that data is kept on the billing end. I work in IT so i am not familiar with all of the billing functionality which might help.
Posted : December 19, 2014 10:04 am
We are on CPS12 so I don't know how different this piece would be but try looking for the table of PatientVisit. The column of ClaimStatus.
From the data model...
| ClaimStatus | int | YES | Claim status corresponding to the Code field of a MedLists row with tablename of SIClaimStatus. |
Hope this helps some.
Posted : December 19, 2014 10:23 am
Claim Status can be found in the PatientVisit Table, the number there will link to your Medlist table to get the name value. Hope this helps, you can also use this script to get started:::
SELECT *, (SELECT Description FROM dbo.MedLists AS ML WHERE (TableName = 'SIClaimStatus') AND (Code = CLAIMPRE.ClaimStatus)) AS ClaimStatusDesc
FROM(
SELECT ecf.Name AS EDIClaimFileName, mlFilingMethod.Description AS FilingMethod, CASE pv.FilingType WHEN 1 THEN 'Paper' WHEN 2 THEN 'Electronic' ELSE 'None' END AS FilingType,
ch.ClearinghouseName AS ClearingHouse, ecf.FiledBy, ic.Name AS FiledTo, ecf.SubmissionNumber, ecf.FileCreated AS DateCreated, ecf.CreatedBy, ecf.FileTransmitted AS DateTransmitted,
ec.Procedures AS ProceduresFiled, SUM(ec.Charges) AS AmountFiled, pvi.OrderForClaims, pv.PatientVisitId, dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
ISNULL(pv.TicketNumber, 'Deleted') AS TicketNumber, pv.Visit, pv.Entered, pv.FirstFiledDate, pv.LastFiledDate, ic.ListName AS InsuranceCarriers, ec.EDIClaimId, pv.ClaimStatus
FROM dbo.EDIClaim AS ec RIGHT OUTER JOIN
dbo.PatientVisit AS pv ON pv.PatientVisitId = ec.PatientVisitId LEFT OUTER JOIN
dbo.MedLists AS mlFilingMethod ON ec.FilingMethodMId = mlFilingMethod.MedListsId INNER JOIN
dbo.EDIClaimFile AS ecf ON ecf.EDIClaimFileId = ec.EDIClaimFileId INNER JOIN
dbo.Clearinghouse AS ch ON ch.ClearinghouseId = ecf.ClearinghouseId INNER JOIN
dbo.InsuranceCarriers AS ic ON ic.InsuranceCarriersId = ec.InsuranceCarriersId INNER JOIN
dbo.PatientProfile AS pp ON pp.PatientProfileId = pv.PatientProfileId INNER JOIN
dbo.PatientVisitInsurance AS pvi ON pvi.PatientVisitId = pv.PatientVisitId
GROUP BY ecf.Name, mlFilingMethod.Description, pv.FilingType, ch.ClearinghouseName, ecf.FiledBy, ic.Name, ecf.SubmissionNumber, ecf.FileCreated, ecf.CreatedBy, ecf.FileTransmitted, ec.Procedures, ec.Charges,
pvi.OrderForClaims, pv.PatientVisitId, pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix, pv.TicketNumber, pv.Visit, pv.Entered, pv.FirstFiledDate, pv.LastFiledDate, ic.ListName, ec.EDIClaimId, pv.ClaimStatus
) AS CLAIMPRE
Posted : December 19, 2014 11:24 am
So acantu, is this a script that we could run on the sql server management studio?
Posted : December 19, 2014 11:42 am
yes sir
Posted : December 19, 2014 11:45 am
Topic starter
Thanks guys, this is perfect! I have used the PatientVisit table on many occasions, but the billing department keeps telling me they "bring the charges in" so in my mind this data was being used to populate somewhere else. Thanks again, it is very much appreciated.
BTW... Acantu, you're query has a few errors for me so there must be some differences structure wise from 10 to 12, i will go through this tomorrow morning and see if i can see what they are. Thanks for the query!
Posted : December 21, 2014 6:34 pm
Clay, fyi, when your billing department tells you they "bring the charges in", they are referring to 'Charge Retrieval' which is to bring the charges in from the EMR or other outside sources such as a lab system. They aren't having to manually post the charge(s) in charge entry.
Posted : December 22, 2014 4:52 am
Topic starter
Thanks Keith! So what exactly happens when they retrieve them? If the data is already in the visit table, and stays there, what is retrieved?
Posted : December 23, 2014 6:24 am
Actually, the visit is "new" until the charges are retrieved or basically 'empty'. Maybe to give you a bigger picture, once an appointment is booked, there is a 'New' visit in Billing. This visit will remain New until 1) a copay is posted against it and at that time it changes to 'In Progress' or
2) Charges are retrieved.
(There is a report in the PM side named 'New visits missing charges' which basically gives you the ability to find visits that have had no charges posted to the visit.)
If a clinic is using the orders module in the EMR for charges, when the ofc starts their encounter, they have to make sure the checkbox "Associate to existing patient appt on this Clinical date" is checked (as long as the Provider and loc of care match up with the appt Provider and facility, it will be checked). The provider will add his order for the ofc visit and/or other procedures done that day. When the order is signed, either by the signing of the document or clinical list changes, the orders will pass the charges to the PM side. At this point, the charges will be available to be retrieved.
The billing staff will 'retrieve the charges' so the 'ticket' will now have the charge line items as well as diagnosis codes that were entered in orders.
Some other things we have realized, a New visit will not have a ticket number until a superbill is printed. Since we have implemented orders for visits, we are no longer printing a superbill so unless we post a copay, the visits will be in a New status with no ticket number until the charges are retrieved.
Hope this helps!
Linda Keith
Posted : December 23, 2014 6:57 am
Topic starter
Thanks Linda! Sorry for calling you keith btw 🙂
That actually is very helpful, i will hang on to that information.
Posted : December 23, 2014 7:09 am
