A Database Mainenence Bug ( at least for us :)

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
parljohn
Posts: 9
Joined: Tue Jan 11, 2011 6:56 pm

A Database Mainenence Bug ( at least for us :)

Post by parljohn » Wed Jan 09, 2013 6:49 am

We have been seeing ClaimPayments getting deleted from our system and haven't understood why.

Our process is to get an Insurance Payment posted as soon as we receive it and mark it as Partial so that another person or team can apply the payment to specific procedures.

I finally dug into the code and realized that the ClaimPaymentDeleteWithNoSplits function is deleting payments with no splits with no check against the Partial flag. It seems like its a flaw in logic to delete payments that are marked as Partial.

Can you please change this to leave our Partials alone as the flag itself represents a payment that is incomplete so the maintenance code shouldn't assume that it is an orphan. Thanks.
SUGGESTED FIX: (JUST ADDING FILTER IN WHERE CLAUSE ON Partial field IN BOTH SELECT AND DELETE Queries - highlighted below )

if(isCheck){
command="SELECT COUNT(*) FROM claimpayment WHERE NOT EXISTS("
+"SELECT * FROM claimproc WHERE claimpayment.ClaimPaymentNum=claimproc.ClaimPaymentNum AND claimpayment.IsPartial = 0)";
.......
command="DELETE FROM claimpayment WHERE ClaimPaymentNum NOT IN ("
+"SELECT ClaimPaymentNum FROM claimproc) "
+"AND claimpayment.DepositNum=0 AND claimpayment.IsPartial = 0";
long numberFixed=Db.NonQ(command);
if(numberFixed>0 || verbose) {
log+=Lans.g("FormDatabaseMaintenance","ClaimPayments with with no splits fixed: ")+numberFixed.ToString()+"\r\n";
}
......
command=@"SELECT deposit.DateDeposit,deposit.Amount,claimpayment.CarrierName,claimpayment.CheckDate,claimpayment.CheckAmt
FROM claimpayment,deposit
WHERE claimpayment.ClaimPaymentNum="+table.Rows["ClaimPaymentNum"].ToString()+@"
AND claimpayment.DepositNum=deposit.DepositNum AND claimpayment.IsPartial = 0";
DataTable temp=Db.GetTable(command);

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: A Database Mainenence Bug ( at least for us :)

Post by jordansparks » Wed Jan 09, 2013 2:09 pm

All orphaned claimpayments would match that criteria. I don't know of any way for the user to mark them as partial or not partial. I'm thinking about removing that section from db maint completely. I think it's an old section that had to be before the batch interface was built. A long time ago, the only way to get into a claim payment was through a claim that was on the payment. So orphaned claim payments had no manual fix. It think it is currently not an issue, since users can edit that list at will. We will remove that section.
Jordan Sparks, DMD
http://www.opendental.com

Post Reply