How to Remove Unwanted Uploaded Card Data

Problem

Card-holder data wrongly uploaded for some reason and need to be removed from the system.

Solution

Data needs to be backed-up and hard-deleted from the database. Setting date_deleted_dte to a non-null value doesn’t work since select queries in the application do not filter by this field.

Data needs to be deleted from the following tables:

yc096_person
yc070_party
tm019_upload_history

You would need to know the organization name for which data was uploaded, the username for the account which made the upload, and the date on which the upload was made, number of records uploaded.

---get foreign keys--
select * from tc006_organization where organization_name_txt = '<organization_name>'; --get <org_id>
select * from tc008_user where User_Id = '<username>'; --get <user_key>
date uploaded = <date_uploaded> --format = DD-MMM-YYYY
records uploaded = <upload_count>

--run this to delete--
delete from yc096_person where last_update_user_id = <user_key> and current_organization_id = <org_id> and TO_DATE(date_created_dte)='<date_uploaded>';
delete from yc070_party where party_id in (select person_id from yc096_person where last_update_user_id = <user_key> and current_organization_id = <org_id> and TO_DATE(date_created_dte)='<date_uploaded>');

--run this to delete from history--
delete from tm019_upload_history where
organization_id = (select organization_id from tc006_organization where ORGANIZATION_NAME_TXT = '<organization_name>') AND
TO_DATE(DATE_UPLOAD_DTE)='<date_uploaded>' and CARDHOLER_CNT_NUM = <upload_count> and
LAST_UPDATE_USER_ID = (select TC008_USER_ID from tc008_user where USER_ID = '<username>');