Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Billing tables in CPS 10.1.3

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

12 Posts

3 Users

0 Reactions

129 Views

RSS

Clay Guthrie

(@clayguthrie)

Posts: 22

Eminent Member

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

acantu

(@acantu)

Posts: 722

Prominent Member

What exactly are you trying to pull for your report?

Posted : December 19, 2014 9:54 am

Clay Guthrie

(@clayguthrie)

Posts: 22

Eminent Member

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

lkeith

(@lkeith)

Posts: 457

Honorable Member

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

acantu

(@acantu)

Posts: 722

Prominent Member

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

lkeith

(@lkeith)

Posts: 457

Honorable Member

So acantu, is this a script that we could run on the sql server management studio?

Posted : December 19, 2014 11:42 am

acantu

(@acantu)

Posts: 722

Prominent Member

yes sir

Posted : December 19, 2014 11:45 am

Clay Guthrie

(@clayguthrie)

Posts: 22

Eminent Member

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

lkeith

(@lkeith)

Posts: 457

Honorable Member

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

Clay Guthrie

(@clayguthrie)

Posts: 22

Eminent Member

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

lkeith

(@lkeith)

Posts: 457

Honorable Member

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

Clay Guthrie

(@clayguthrie)

Posts: 22

Eminent Member

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

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed