|
Description
|
SQL Command (Drag select and copy to your clipboard.)
|
|
Reset the internal database modified date so that ThirdPartySync will force a refresh of all patient demographic data.
|
UPDATE CP_DP SET CP_DP.ReplicationModified=#1/1/1980#
|
|
Force all patients to have a receipt selection of "NO" in the Financial tab of the Patient General Information window.
|
UPDATE tblFinancial SET tblFinancial.Receipt = 0;
|
|
Force all patients to receive statements only when a minimum amount is due.
|
UPDATE tblFinancial SET tblFinancial.Statement = 2;
|
|
Initialize all patients to receive "Gentle" late notices, no receipts by default, and only "Minimum Due" patient statements.
|
UPDATE tblFinancial SET tblFinancial.LateNotice = 1, tblFinancial.Receipt = 0, tblFinancial.Statement = 2;
|
|
Set all diagnoses to an "Inactive" status.
|
UPDATE tblDiagnosis SET tblDiagnosis.Status = "Inactive";
|
|
Change all insurance payer types from Internet Disk Image to EClaims EDI Image.
|
UPDATE tblCarrier SET tblCarrier.FormType = 7
WHERE (((tblCarrier.FormType)=2));
|
|
Change all insurance policy form types from Internet Disk Image to EClaims EDI Image.
|
UPDATE tblThirdParty SET tblThirdParty.FormType = 7
WHERE (((tblThirdParty.FormType)=2));
|
|
Change all existing insurance claims Internet Disk Image to EClaims EDI Image.
|
UPDATE tblClaimCarrierDetail SET tblClaimCarrierDetail.FormType = 7
WHERE (((tblClaimCarrierDetail.FormType)=2));
|
|
Revert the form types of all insurance policies and insurance claims to the default form type defined in the insurance payer catalog. (Advanced: Make a backup of your data before executing any queries.)
|
UPDATE tblCarrier INNER JOIN (tblThirdParty INNER JOIN tblClaimCarrierDetail ON tblThirdParty.Created = tblClaimCarrierDetail.CarrierCreated) ON tblCarrier.ID = tblThirdParty.ID SET tblThirdParty.FormType = [tblCarrier].[FormType], tblClaimCarrierDetail.FormType = [tblCarrier].[FormType];
|
|
Change all insurance policy form types from Internet Disk Image to CMS-1500 type. Notice: These are two update queries.
|
UPDATE tblClaimCarrierDetail SET tblClaimCarrierDetail.FormType = 0
WHERE (((tblClaimCarrierDetail.FormType)=2));
UPDATE tblThirdParty SET tblThirdParty.FormType = 0
WHERE (((tblThirdParty.FormType)=2));
|
|
Change all form types from CMS-1500 to E-Claims EDI Image type. Notice: These are two update queries.
|
UPDATE tblClaimCarrierDetail SET tblClaimCarrierDetail.FormType = 7
WHERE (((tblClaimCarrierDetail.FormType)=0));
UPDATE tblThirdParty SET tblThirdParty.FormType = 7
WHERE (((tblThirdParty.FormType)=0));
|
|
In the insurance payer catalog, set form type to CMS-1500 where the form type is undefined.
|
UPDATE tblCarrier SET tblCarrier.FormType = 0
WHERE (((tblCarrier.FormType) Is Null));
|
|
In the patient's insurance policies, set form type to CMS-1500 where the form type is undefined.
|
UPDATE tblClaimCarrierDetail SET tblClaimCarrierDetail.FormType = 0
WHERE (((tblClaimCarrierDetail.FormType) Is Null));
|
|
For insurance claims that are created, but have no form type defined, set form type to CMS-1500.
|
UPDATE tblThirdParty SET tblThirdParty.FormType = 0
WHERE (((tblThirdParty.FormType) Is Null));
|
|
Remove all patients from the sign-in list of MyEMR and/or EMR Datacenter.
|
DELETE tblCheckIn.*
FROM tblCheckIn;
|
|
Mark a visit status as "Inactive" so that it doesn't appear in the Pocket PC frequency list.
|
UPDATE tblCPVVisitStatus SET tblCPVVisitStatus.Inactive = True
WHERE (((tblCPVVisitStatus.ID)="1/W-1W"));
|
|
Mark all patients to have a status of "Inactive".
|
UPDATE CP_DP SET CP_DP.STATUS = "Inactive";
|
|
Force all insurance payers to print the TIN/EIN number instead of the provider's SSN number.
|
UPDATE tblCarrier SET tblCarrier.TaxID = 1;
|
|
Force TOS = 1 for all service category transactions whose TOS is blank. Some users may elect TOS = 9 for "Other medical service" by modifying the SET portion of the query.
|
UPDATE (tblProcedure INNER JOIN tblTransaction ON tblProcedure.ID = tblTransaction.ID) INNER JOIN tblProcedureCategory ON tblProcedure.Category = tblProcedureCategory.ID SET tblTransaction.TOS = "1"
WHERE (((tblTransaction.TOS)="") AND ((tblProcedureCategory.Type)="S"));
|
|
ADVANCED MAINTENANCE: Delete all generated SOAP notes and report from the active database. This may be applicable only to a new user who has been using practice visits on a live database.
|
DELETE CP_DSIRTF.UID
FROM CP_DSIRTF
WHERE (((CP_DSIRTF.UID)<>0));
|
|
ADVANCED MAINTENANCE: Delete all symbolic subjective, palpation, assessment status and plan modality records from the active database. After running this query, the user must use the Start->All Programs->Software Motif->Database utility to run the action of "Create ActiveSync Findings" and re-synchronize their Pocket PC.
|
DELETE CP_DSD.RecordID
FROM CP_DSD
WHERE (((CP_DSD.RecordID)<>0));
|
|
Inactivate all procedures except accounting codes.
|
UPDATE tblProcedure SET tblProcedure.Status = "Inactive"
WHERE [tblProcedure].[Category]>18 And [tblProcedure].[Category]<90000000;
|
|
Change the EMR Datacenter plans of 1H, 3H, 5H, etc., as plans available to MyEMR for Windows / Pocket PC.
|
UPDATE tblCPVModality SET tblCPVModality.Owner = "*PUBLIC"
WHERE (((tblCPVModality.ID) Like "1*" Or (tblCPVModality.ID) Like "3*" Or (tblCPVModality.ID) Like "5*"));
|
|
Delete all procedure codes whose status is "Inactive". Do not run this without a data backup and the supervision of technical support. This should be used by new users only prior to entering any transactions.
|
DELETE tblProcedure.Status
FROM tblProcedure
WHERE (((tblProcedure.Status)="Inactive"));
|
|
Trim Diagnosis Catalog ID's
|
UPDATE tblDiagnosis SET tblDiagnosis.ID = Trim([tblDiagnosis].[ID]), tblDiagnosis.ICD = Trim([tblDiagnosis].[ICD]), tblDiagnosis.Brief = Trim([tblDiagnosis].[Brief]);
|
|
Trim Procedure Catalog ID's
|
UPDATE tblProcedure SET tblProcedure.CPT = Trim([tblProcedure].[CPT]), tblProcedure.ID = Trim([tblProcedure].[ID]), tblProcedure.Description = Trim([tblProcedure].[Description]);
|
|
Trim Insurance Payer city, company name and ZIP codes
|
UPDATE tblCarrier SET tblCarrier.City = Trim([tblCarrier].[City]), tblCarrier.Company = Trim([tblCarrier].[Company]), tblCarrier.Zip = Trim([tblCarrier].[Zip]);
|
|
Change "Bill To" to "Either" in the Procedure Catalog
|
UPDATE tblProcedure SET tblProcedure.BillTo = 2
WHERE (((tblProcedure.Category)>18));
|
|
Make all EMR Datacenter appointment plans & mapping available for MyEMR verbiage.
|
UPDATE tblCPVModality SET tblCPVModality.Owner = "*PUBLIC";
|
|
Change diagnosis full description from NULL to blank.
|
UPDATE tblDiagnosis SET tblDiagnosis.Full = ""
WHERE (((tblDiagnosis.Full) Is Null));
|
|
Turn off patient tracers for all insurance policies. The asterisk character may be substituted for the computer key ID of the insurance payer for surgical changes, for example, the "*" could be substituted for "BCBS" if you had an insurance payer with the computer key ID of "BCBS".
|
UPDATE tblThirdParty SET tblThirdParty.TracePat = 0
WHERE (([tblThirdParty].[ID]="*"));
|
|
Revert transactions to assigned type if the insurance policy assignment flag is checked.
|
UPDATE (tblTransaction INNER JOIN tblClaim ON tblTransaction.OnClaim = tblClaim.Created) INNER JOIN tblClaimCarrierDetail ON (tblClaim.CarrierCreated = tblClaimCarrierDetail.CarrierCreated) AND (tblClaim.CarrierDetail = tblClaimCarrierDetail.CarrierModified) SET tblTransaction.Assigned = True
WHERE (((tblTransaction.Assigned)=False) AND ((tblTransaction.AccountingType)="S") AND ((tblClaimCarrierDetail.BenAsn)=1)) OR (((tblTransaction.Assigned)=False) AND ((tblTransaction.AccountingType)="P") AND ((tblClaimCarrierDetail.BenAsn)=1));
|
|
Insert new item into the appointment resolution log
|
INSERT INTO tblLogGlossary ( Section, Type, Sequence, [Text], Action, ReplicationModified )
VALUES (0, 1 , 0 , "Canceled: Would not reschedule", 2, Now())
|
|
Remove any SOAP window records with future dates as the result of the computer time clock being set to a future date.
|
DELETE CP_DSD.Created
FROM CP_DSD
WHERE (((CP_DSD.Created)>Now()));
|
|
Delete appointment blackout for a provider for a day. Substitute XYZ with the provider ID, and substitute the date range of 12/30/2006 and 12/31/2006 with the desired date range. Note that 12/30/2006 and 12/31/2006 represent exactly midnight for an exact 24 hour range.
|
DELETE tblAppointment.Chart, tblAppointment.Provider, tblAppointment.Apt
FROM tblAppointment
WHERE (((tblAppointment.Chart)="%%Blackout") AND ((tblAppointment.Provider)="XYZ") AND ((tblAppointment.Apt)>#12/30/2006# And (tblAppointment.Apt)<#12/31/2006#));
|
|
If a claim has been deleted, return orphaned transactions to an unbatched status.
|
UPDATE tblTransaction LEFT JOIN tblClaim ON tblTransaction.OnClaim = tblClaim.Created SET tblTransaction.OnClaim = #1/1/1901#
WHERE (((tblTransaction.OnClaim) Is Not Null));
|
|
Delete blank Rich Text Format notes.
|
DELETE CP_DSIRTF.binNote
FROM CP_DSIRTF
WHERE (((CP_DSIRTF.binNote) Is Null));
|
|
Delete Rich Text Format notes with invalid audio/ink annotation values.
|
DELETE CP_DSIRTF.NoteAnnotateMask
FROM CP_DSIRTF
WHERE (((CP_DSIRTF.NoteAnnotateMask)>2));
|
|
Delete a patient's most recent objective spinal palpation record. Substitute the desired chart number in place of 2006-?????.
|
DELETE CP_DSD.Chart
FROM (SELECT TOP 1 CP_DSD.Chart, CP_DSD.Type, CP_DSD.ReplicationModified
FROM CP_DSD
WHERE (((CP_DSD.Chart)="2006-?????") AND ((CP_DSD.Type)="OP"))
ORDER BY CP_DSD.Chart, CP_DSD.Type, CP_DSD.ReplicationModified DESC)
|
|
5 queries to delete all transactions & history and claims and history.
|
zero_balances.txt
|
|
29 queries to delete all patient data except for customized verbiage
|
clear_queries.txt
|