Patient Payments are exits after deleting a patient from Openemr

Hello Brady,

Steps to reproduce the issue :

1.Create a new patient in Openemr .( consider the patient id for the new patient is 2 )

if any new patient is created -> patient id will be generating using the following method :

    new patient id = select max(pid)+1 from patient_data ;

new patient id will be the next integer number of max value of patient id in the patient data table.

2.Need to create an encounter and fee-sheet for this newly created patient .

3.Fill the necessary ICD10 , CPT codes in the fee-sheet and process the billing for this patient .

4.Post a new payment for this encounter using insurance or patient payments.

5.Generate an invoice for the payment .

6.Now the billing and payment process has been completed for this newly created patient ( who is having 2 as patient id ).

7.Goto patient demographics and click on delete button. This will delete the patient id .

8.if we delete the patient ( who is having patient id as 2 ) from demographics screen it will only delete the demographics and encounter information not the billing and payment information from Openemr Database.

   Note : Consider the patient id 2 is the  maximum patient id in the patient_data table.After deleting this patient id the next maxmium patient id will be 1.

9.All the billing and payment details are mapped to patient using patient id field.

10.Now , Create a new patient in openemr .Here , the new patient id will be 2 ( which is same as previously deleted patient’s pid ) .

  The same patient id generation rule will apply .

         new patient id = select max(pid)+1 from patient_data ;

11.if we goes to generate any payment related report or billing process , then the newly created patient will automatically mapped to old patient’s data ( refer the point 9 ).

This how the deleted patient’s payment/ billing data are wrongly mapped to newly created patient .

Please check if this is a bug ? If yes , then we need to fix this .

Hi @visolveemr ,

This is definitely a bug and looks like will happen when delete the patient with the highest pid (then the next new patient will take this same pid and be mapped to any items that were not deleted).

I guess 2 routes to fix this(and maybe both should be pursued):

  1. Ensure remove everything from the database regarding the patient when delete a patient
  2. Do something special when the highest pid is deleted (maybe a placemaker mechanism or something so then when a new user is created, it does not use a prior pid).

-brady

might it be better to mark a patient as inactive?

@stephenwaite ,
That would be ideal. If did that, would need to ensure rest of codebase enforced this. This mechanism could also then be leveraged for other cool things (such as categorizing patient groups).
-brady

ok, is that what was envisioned with the status field?

how could this help categorize groups?

--
-- Table structure for table `patient_data`
--

DROP TABLE IF EXISTS `patient_data`;
CREATE TABLE `patient_data` (
  `id` bigint(20) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `language` varchar(255) NOT NULL default '',
  `financial` varchar(255) NOT NULL default '',
  `fname` varchar(255) NOT NULL default '',
  `lname` varchar(255) NOT NULL default '',
  `mname` varchar(255) NOT NULL default '',
  `DOB` date default NULL,
  `street` varchar(255) NOT NULL default '',
  `postal_code` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `state` varchar(255) NOT NULL default '',
  `country_code` varchar(255) NOT NULL default '',
  `drivers_license` varchar(255) NOT NULL default '',
  `ss` varchar(255) NOT NULL default '',
  `occupation` longtext,
  `phone_home` varchar(255) NOT NULL default '',
  `phone_biz` varchar(255) NOT NULL default '',
  `phone_contact` varchar(255) NOT NULL default '',
  `phone_cell` varchar(255) NOT NULL default '',
  `pharmacy_id` int(11) NOT NULL default '0',
  `status` varchar(255) NOT NULL default '',
  `contact_relationship` varchar(255) NOT NULL default '',
  `date` datetime default NULL,
  `sex` varchar(255) NOT NULL default '',
  `referrer` varchar(255) NOT NULL default '',
  `referrerID` varchar(255) NOT NULL default '',
  `providerID` int(11) default NULL,
  `ref_providerID` int(11) default NULL,
  `email` varchar(255) NOT NULL default '',
  `email_direct` varchar(255) NOT NULL default '',
  `ethnoracial` varchar(255) NOT NULL default '',
  `race` varchar(255) NOT NULL default '',
  `ethnicity` varchar(255) NOT NULL default '',
  `religion` varchar(40) NOT NULL default '',
  `interpretter` varchar(255) NOT NULL default '',
  `migrantseasonal` varchar(255) NOT NULL default '',
  `family_size` varchar(255) NOT NULL default '',
  `monthly_income` varchar(255) NOT NULL default '',
  `billing_note` text,
  `homeless` varchar(255) NOT NULL default '',
  `financial_review` datetime default NULL,
  `pubpid` varchar(255) NOT NULL default '',
  `pid` bigint(20) NOT NULL default '0',
  `genericname1` varchar(255) NOT NULL default '',
  `genericval1` varchar(255) NOT NULL default '',
  `genericname2` varchar(255) NOT NULL default '',
  `genericval2` varchar(255) NOT NULL default '',
  `hipaa_mail` varchar(3) NOT NULL default '',
  `hipaa_voice` varchar(3) NOT NULL default '',
  `hipaa_notice` varchar(3) NOT NULL default '',
  `hipaa_message` varchar(20) NOT NULL default '',
  `hipaa_allowsms` VARCHAR(3) NOT NULL DEFAULT 'NO',
  `hipaa_allowemail` VARCHAR(3) NOT NULL DEFAULT 'NO',
  `squad` varchar(32) NOT NULL default '',
  `fitness` int(11) NOT NULL default '0',
  `referral_source` varchar(30) NOT NULL default '',
  `usertext1` varchar(255) NOT NULL DEFAULT '',
  `usertext2` varchar(255) NOT NULL DEFAULT '',
  `usertext3` varchar(255) NOT NULL DEFAULT '',
  `usertext4` varchar(255) NOT NULL DEFAULT '',
  `usertext5` varchar(255) NOT NULL DEFAULT '',
  `usertext6` varchar(255) NOT NULL DEFAULT '',
  `usertext7` varchar(255) NOT NULL DEFAULT '',
  `usertext8` varchar(255) NOT NULL DEFAULT '',
  `userlist1` varchar(255) NOT NULL DEFAULT '',
  `userlist2` varchar(255) NOT NULL DEFAULT '',
  `userlist3` varchar(255) NOT NULL DEFAULT '',
  `userlist4` varchar(255) NOT NULL DEFAULT '',
  `userlist5` varchar(255) NOT NULL DEFAULT '',
  `userlist6` varchar(255) NOT NULL DEFAULT '',
  `userlist7` varchar(255) NOT NULL DEFAULT '',
  `pricelevel` varchar(255) NOT NULL default 'standard',
  `regdate`     date DEFAULT NULL COMMENT 'Registration Date',
  `contrastart` date DEFAULT NULL COMMENT 'Date contraceptives initially used',
  `completed_ad` VARCHAR(3) NOT NULL DEFAULT 'NO',
  `ad_reviewed` date DEFAULT NULL,
  `vfc` varchar(255) NOT NULL DEFAULT '',
  `mothersname` varchar(255) NOT NULL DEFAULT '',
  `guardiansname` TEXT,
  `allow_imm_reg_use` varchar(255) NOT NULL DEFAULT '',
  `allow_imm_info_share` varchar(255) NOT NULL DEFAULT '',
  `allow_health_info_ex` varchar(255) NOT NULL DEFAULT '',
  `allow_patient_portal` varchar(31) NOT NULL DEFAULT '',
  `deceased_date` datetime default NULL,
  `deceased_reason` varchar(255) NOT NULL default '',
  `soap_import_status` TINYINT(4) DEFAULT NULL COMMENT '1-Prescription Press 2-Prescription Import 3-Allergy Press 4-Allergy Import',
  `cmsportal_login` varchar(60) NOT NULL default '',
  `care_team` int(11) DEFAULT NULL,
  `county` varchar(40) NOT NULL default '',
  `industry` TEXT,
  `imm_reg_status` TEXT,
  `imm_reg_stat_effdate` TEXT,
  `publicity_code` TEXT,
  `publ_code_eff_date` TEXT,
  `protect_indicator` TEXT,
  `prot_indi_effdate` TEXT,
  `guardianrelationship` TEXT,
  `guardiansex` TEXT,
  `guardianaddress` TEXT,
  `guardiancity` TEXT,
  `guardianstate` TEXT,
  `guardianpostalcode` TEXT,
  `guardiancountry` TEXT,
  `guardianphone` TEXT,
  `guardianworkphone` TEXT,
  `guardianemail` TEXT,
  UNIQUE KEY `pid` (`pid`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;
-- --------------------------------------------------------

Hello Brady,

Thanks for the suggestions. As you mentioned in point #1 to remove pid from all the tables including billing, this may cause an issue while creating annual billing or payments related reports.
So we are planning to go by suggestions #2 i.e adding some placemaker to ignore the prior pid .
We have taken up this task and working on it. Will update once this is completed.

Thanks
ViSolve