When I run "Database Maintenance" I get "Claim payments with mismatched dates fixed:" and the number of fixed dates. I don't see any difference in dates before and after in the claim or the payment. I'm not really sure what dates are being fixed. Apparently it must be something we do wrong always because we always get this message every few days. Does anybody have any idea what it is?
Jorge Bonilla DMD
Database Maintenance
-
jordansparks
- Site Admin
- Posts: 5770
- Joined: Sun Jun 17, 2007 3:59 pm
- Location: Salem, Oregon
- Contact:
Oh, let's see. I'll just talk about the issue the best I can. I understand that it will be over the head of most users, but I know Jorge will grasp it. In a big complex database like this, many assumptions are made. In this case, it is always assumed that claimproc.DateCP is the same as claimpayment.CheckDate. This assumption helps with running reports without having to join tables. Because the assumptions and relationships are so complex, I frequently make notes about them in the code. Here are the relevant descriptions of those two fields:
claimproc.DateCP: This is the date that is used for payment reports and tracks the payment date. Always exactly matches the date of the ClaimPayment it's attached to. See the note under Ledgers.ComputePayments. This will eventually not be used for aging. The ProcDate will instead be used. See ProcDate.
claimpayment.CheckDate: Date the check was entered into this system, not the date on the check.
Now I wrote both of those descriptions many years ago, and there are nuances surrounding each of them. For example, the claimpayment.CheckDate really does mean the common date of the check in the system. When it says "not the date on the check" it just means not the date that the patient wrote on the check before they mailed it in. Reviewing the above two decriptions, it is crystal clear to me that one assumption about our database is that those two dates must always exactly match if the claimproc is attached to the claimpayment in question.
I noticed a few months ago that the dates of some entries did not match. In a situation like that, the first thing I do is add a section to the database maintenance tool to enforce the assumptions in order to make the data cleaner. The second phase is to determine if the date mismatch is a historical problem or an ongoing problem. I think we can now safely say it's an ongoing problem. Finally, we have to track down why it's happening. That could take some time. Probably a misplaced semiquote somewhere. I'll put it down as a bug and begin looking for where the mismatch is happening.
claimproc.DateCP: This is the date that is used for payment reports and tracks the payment date. Always exactly matches the date of the ClaimPayment it's attached to. See the note under Ledgers.ComputePayments. This will eventually not be used for aging. The ProcDate will instead be used. See ProcDate.
claimpayment.CheckDate: Date the check was entered into this system, not the date on the check.
Now I wrote both of those descriptions many years ago, and there are nuances surrounding each of them. For example, the claimpayment.CheckDate really does mean the common date of the check in the system. When it says "not the date on the check" it just means not the date that the patient wrote on the check before they mailed it in. Reviewing the above two decriptions, it is crystal clear to me that one assumption about our database is that those two dates must always exactly match if the claimproc is attached to the claimpayment in question.
I noticed a few months ago that the dates of some entries did not match. In a situation like that, the first thing I do is add a section to the database maintenance tool to enforce the assumptions in order to make the data cleaner. The second phase is to determine if the date mismatch is a historical problem or an ongoing problem. I think we can now safely say it's an ongoing problem. Finally, we have to track down why it's happening. That could take some time. Probably a misplaced semiquote somewhere. I'll put it down as a bug and begin looking for where the mismatch is happening.
Jordan Sparks, DMD
http://www.opendental.com
http://www.opendental.com