InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:27:07 GMT20http://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxThanks!! That may be the best way to fix it. Or a variation at least.Wed, 06 Mar 2013 17:09:34 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxWhy not combine not updating the time in DOB with this?[code]IF UPDATE(DOB) BEGIN UPDATE P SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB) FROM dbo.Patient P INNER JOIN inserted i ON P.PatientID = i.PatientID where i.DOB <> cast(convert(varchar,i.DOB,112) as datetime) SET @error_var = @@ERROR --Error checking IF @error_var > 0 GOTO rollback_tran END[/code]Wed, 06 Mar 2013 16:56:18 GMTlnardozi 61862http://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxThats worth a shot. I'll do some testing. ThanksWed, 06 Mar 2013 16:09:14 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxYou would modify your proc to set CONTEXT_INFO before you did the UPDATE. SET CONTEXT_INFO 0x1256698456Then in your trigger you would check CONTEXT_INFO and if you found it had the same value you set in your proc you would skip the UPDATE:[code="sql"]SET @var = CONTEXT_INFO();IF @var != 0x1256698456BEGIN -- do update as usualEND[/code][u][url=http://msdn.microsoft.com/en-us/library/ms180125(v=sql.90).aspx]http://msdn.microsoft.com/en-us/library/ms180125(v=sql.90).aspx[/url][/u]Wed, 06 Mar 2013 15:55:19 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxHow do you determine the Context_info? I don't understand how I could use that to avoid the trigger?Wed, 06 Mar 2013 15:34:54 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxJust had this same question on another thread with a trigger and doing a large insert...almost verbatim from the other thread:One thing I can say is that if you were to alter your trigger to skip the update and you supplied the correct DOB in your INSERT statement you would save yourself a ton of I/O. You would need to do some impact analysis however to ensure a change like this would not compromise your data should other inserts or updates supply invalid values for that column thereby circumventing the usefulness of the trigger. If that were a concern there are other things you could do with CONTEXT_INFO to skip the work in the trigger for only your batch process.Wed, 06 Mar 2013 15:28:14 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxThat would be ideal. The problem is there are many places that update or insert into the patient table. And no one can tell me all the places. They may not all be in stored procs. So If I don't have the trigger it could be missed somewhere and cause other issues.Wed, 06 Mar 2013 15:22:56 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxSo, you update the DOB with the sproc UPDATE statement, and then the trigger updates the DOB too (after removing the time)? Uh, maybe just strip off the time before you do the sproc UPDATE and eliminate the trigger?Wed, 06 Mar 2013 15:20:43 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxSorry About that here is the code:[code="sql"]CREATE PROCEDURE dbo.PatientDataProvider_UpdatePatient @patient_id INT, @prefix VARCHAR(16), @first_name VARCHAR(32), @middle_name VARCHAR(32), @last_name VARCHAR(32), @suffix VARCHAR(16), @address_1 VARCHAR(128) = NULL, @address_2 VARCHAR(128) = NULL, @city VARCHAR(32) = NULL, @state VARCHAR(2) = NULL, @country VARCHAR(32) = NULL, @zip VARCHAR(9) = NULL, @gender VARCHAR(1) = 'U', @marital_status VARCHAR(1) = 'U', @home_phone VARCHAR(10) = NULL, @home_phone_x VARCHAR(10) = NULL, @work_phone VARCHAR(10) = NULL, @work_phone_x VARCHAR(10) = NULL, @dob DATETIME = NULL, @ssn CHAR(9) = NULL, @email VARCHAR(128) = NULL, @sendEmailCorrespondence BIT = 1, @guarantor_different BIT = 0, @employment_status CHAR(1) = NULL, @referrer_id INT = NULL, @primary_provider_id INT = NULL, @patient_referral_source_id INT = NULL, @patient_default_location_id INT = NULL, @employer_id INT = NULL, @medical_record_number VARCHAR(128) = NULL, @mobile_phone VARCHAR(10) = NULL, @mobile_phone_x VARCHAR(10) = NULL, @primary_care_physician_id INT = NULL, @modified_user_id INT = 0, @active BIT, @PhonecallRemindersEnabled BIT = 0, @EmergencyName VARCHAR(128) = NULL, @EmergencyPhone VARCHAR(10) = NULL, @EmergencyPhoneExt VARCHAR(10) = NULLASBEGIN IF @email IS NULL OR LTRIM(@email) = '' BEGIN SET @sendEmailCorrespondence = 0 ; END UPDATE dbo.Patient SET Prefix = @prefix, FirstName = @first_name, MiddleName = @middle_name, LastName = @last_name, Suffix = @suffix, AddressLine1 = @address_1, AddressLine2 = @address_2, City = @city, State = @state, Country = @country, ZipCode = @zip, Gender = @gender, MaritalStatus = @marital_status, HomePhone = @home_phone, HomePhoneExt = @home_phone_x, WorkPhone = @work_phone, WorkPhoneExt = @work_phone_x, DOB = @dob, SSN = @ssn, EmailAddress = RTRIM(LTRIM(@email)), SendEmailCorrespondence = @sendEmailCorrespondence, ResponsibleDifferentThanPatient = @guarantor_different, EmploymentStatus = @employment_status, ReferringPhysicianID = @referrer_id, PrimaryProviderID = @primary_provider_id, PatientReferralSourceID = @patient_referral_source_id, DefaultServiceLocationID = @patient_default_location_id, EmployerID = @employer_id, MedicalRecordNumber = @medical_record_number, MobilePhone = @mobile_phone, MobilePhoneExt = @mobile_phone_x, PrimaryCarePhysicianID = @primary_care_physician_id, ModifiedDate = GETDATE(), ModifiedUserID = @modified_user_id, Active = @Active, PhonecallRemindersEnabled = @PhonecallRemindersEnabled, EmergencyName = @EmergencyName, EmergencyPhone = @EmergencyPhone, EmergencyPhoneExt = @EmergencyPhoneExt WHERE PatientID = @patient_id ;END[/code]Wed, 06 Mar 2013 15:04:35 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxOne of my thoughts on the indexes was to Make the Primary Key PK_Patient the Clustered index and then make the Practice_patientid index only a nonclustered index on the practiceID.Wed, 06 Mar 2013 13:58:25 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxThese are the two indexes involved in the deadlock:[code="sql"]CREATE UNIQUE CLUSTERED INDEX [CI_Patient_PracticeID_PatientID] ON [dbo].[Patient] ( [PracticeID] ASC, [PatientID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOCREATE TABLE [dbo].[Patient]......CONSTRAINT [PK_Patient] PRIMARY KEY NONCLUSTERED ( [PatientID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/code]Wed, 06 Mar 2013 13:23:58 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxMy vote is for the huge covering index being the culprit here...Wed, 06 Mar 2013 13:18:17 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxI still don't think we have seen the code for object 1945422350.Wed, 06 Mar 2013 13:14:07 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxThe trigger is very simple. ( I hate that we have this trigger but unfortunately not on the books to fix everywhere).The only table involved is the Patient table. I was wondering if there was a conflict between the Primary Key Nonclusterindex and the clustered index.[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--===========================================================================-- TR -- IU -- PATIENT -- CHANGE TIME--===========================================================================CREATE TRIGGER [dbo].[tr_IU_Patient_ChangeTime] ON [dbo].[Patient]FOR INSERT, UPDATEASBEGIN DECLARE @error_var int SET @error_var = 0 DECLARE @proc_name sysname SET @proc_name = (SELECT name FROM sysobjects WHERE id = @@PROCID) DECLARE @CRLF char(2) SET @CRLF = CHAR(13) + CHAR(10) DECLARE @err_message nvarchar(255) IF UPDATE(DOB) BEGIN UPDATE P SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB) FROM dbo.Patient P INNER JOIN inserted i ON P.PatientID = i.PatientID SET @error_var = @@ERROR --Error checking IF @error_var > 0 GOTO rollback_tran END RETURN rollback_tran: IF @err_message IS NULL SET @err_message = 'Rolling back transaction - ' + @proc_name + ' - ' + CONVERT(varchar(30), GETDATE(), 121) ELSE SET @err_message = 'Rolling back transaction - ' + @proc_name + ' - ' + CONVERT(varchar(30), GETDATE(), 121) + @CRLF + @CRLF + @err_message IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION RAISERROR(@err_message, 16,1) RETURNENDGO[/code][code="sql"]CREATE TABLE [dbo].[Patient]( [PatientID] [int] IDENTITY(1,1) NOT NULL, [PracticeID] [int] NOT NULL, [ReferringPhysicianID] [int] NULL, [Prefix] [varchar](16) NOT NULL, [FirstName] [varchar](64) NOT NULL, [MiddleName] [varchar](64) NOT NULL, [LastName] [varchar](64) NOT NULL, [Suffix] [varchar](16) NOT NULL, [AddressLine1] [varchar](256) NULL, [AddressLine2] [varchar](256) NULL, [City] [varchar](128) NULL, [State] [varchar](2) NULL, [Country] [varchar](32) NULL, [ZipCode] [varchar](9) NULL, [Gender] [varchar](1) NULL, [MaritalStatus] [varchar](1) NULL, [HomePhone] [varchar](10) NULL, [HomePhoneExt] [varchar](10) NULL, [WorkPhone] [varchar](10) NULL, [WorkPhoneExt] [varchar](10) NULL, [DOB] [datetime] NULL, [SSN] [char](9) NULL, [EmailAddress] [varchar](256) NULL, [ResponsibleDifferentThanPatient] [bit] NULL, [ResponsiblePrefix] [varchar](16) NULL, [ResponsibleFirstName] [varchar](64) NULL, [ResponsibleMiddleName] [varchar](64) NULL, [ResponsibleLastName] [varchar](64) NULL, [ResponsibleSuffix] [varchar](16) NULL, [ResponsibleRelationshipToPatient] [varchar](1) NULL, [ResponsibleAddressLine1] [varchar](256) NULL, [ResponsibleAddressLine2] [varchar](256) NULL, [ResponsibleCity] [varchar](128) NULL, [ResponsibleState] [varchar](2) NULL, [ResponsibleCountry] [varchar](32) NULL, [ResponsibleZipCode] [varchar](9) NULL, [CreatedDate] [datetime] NOT NULL, [CreatedUserID] [int] NOT NULL, [ModifiedDate] [datetime] NOT NULL, [ModifiedUserID] [int] NOT NULL, [RecordTimeStamp] [timestamp] NOT NULL, [EmploymentStatus] [char](1) NULL, [InsuranceProgramCode] [char](2) NULL, [PatientReferralSourceID] [int] NULL, [PrimaryProviderID] [int] NULL, [DefaultServiceLocationID] [int] NULL, [EmployerID] [int] NULL, [MedicalRecordNumber] [varchar](128) NULL, [MobilePhone] [varchar](10) NULL, [MobilePhoneExt] [varchar](10) NULL, [PrimaryCarePhysicianID] [int] NULL, [VendorID] [varchar](50) NULL, [VendorImportID] [int] NULL, [CollectionCategoryID] [int] NULL, [Active] [bit] NOT NULL, [SendEmailCorrespondence] [bit] NULL, [PhonecallRemindersEnabled] [bit] NOT NULL, [EmergencyName] [varchar](128) NULL, [EmergencyPhone] [varchar](10) NULL, [EmergencyPhoneExt] [varchar](10) NULL, [PatientGuid] [uniqueidentifier] NOT NULL, [Ethnicity] [varchar](64) NULL, [Race] [varchar](64) NULL, [LicenseNumber] [varchar](64) NULL, [LicenseState] [varchar](2) NULL, [Language1] [varchar](64) NULL, [Language2] [varchar](64) NULL, CONSTRAINT [PK_Patient] PRIMARY KEY NONCLUSTERED ( [PatientID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOCREATE UNIQUE CLUSTERED INDEX [CI_Patient_PracticeID_PatientID] ON [dbo].[Patient] ( [PracticeID] ASC, [PatientID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Patient_FirstName] ON [dbo].[Patient] ( [FirstName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Patient_ModifiedDate_PracticeID] ON [dbo].[Patient] ( [ModifiedDate] DESC, [PracticeID] ASC)INCLUDE ( [PatientID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Patient_PracticeID_Active_FOR_GetPatients] ON [dbo].[Patient] ( [PracticeID] ASC, [Active] ASC, [LastName] ASC, [FirstName] ASC, [MiddleName] ASC)INCLUDE ( [PatientID],[AddressLine1],[AddressLine2],[City],[State],[ZipCode],[HomePhone],[SSN],[ResponsibleFirstName],[ResponsibleLastName],[MedicalRecordNumber],[DOB]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_Patient_PracticeID_DefaultServiceLocationID] ON [dbo].[Patient] ( [PracticeID] ASC, [DefaultServiceLocationID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO/****** Object: Index [IX_Patient_ReferringPhysicianID] Script Date: 03/06/2013 11:55:36 ******/CREATE NONCLUSTERED INDEX [IX_Patient_ReferringPhysicianID] ON [dbo].[Patient] ( [ReferringPhysicianID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO/****** Object: Index [IX_Patient_SSN] Script Date: 03/06/2013 11:55:36 ******/CREATE NONCLUSTERED INDEX [IX_Patient_SSN] ON [dbo].[Patient] ( [SSN] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO/****** Object: Index [UX_Patient_Guid] Script Date: 03/06/2013 11:55:36 ******/CREATE UNIQUE NONCLUSTERED INDEX [UX_Patient_Guid] ON [dbo].[Patient] ( [PatientGuid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_CollectionCategory] FOREIGN KEY([CollectionCategoryID])REFERENCES [dbo].[CollectionCategory] ([CollectionCategoryID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_CollectionCategory]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Doctor] FOREIGN KEY([PrimaryProviderID])REFERENCES [dbo].[Doctor] ([DoctorID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Doctor]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Employers] FOREIGN KEY([EmployerID])REFERENCES [dbo].[Employers] ([EmployerID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Employers]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_InsuranceProgram] FOREIGN KEY([InsuranceProgramCode])REFERENCES [dbo].[InsuranceProgram] ([InsuranceProgramCode])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_InsuranceProgram]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_PatientReferralSource] FOREIGN KEY([PatientReferralSourceID])REFERENCES [dbo].[PatientReferralSource] ([PatientReferralSourceID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_PatientReferralSource]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Practice] FOREIGN KEY([PracticeID])REFERENCES [dbo].[Practice] ([PracticeID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Practice]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_PrimaryCarePhysicianID] FOREIGN KEY([PrimaryCarePhysicianID])REFERENCES [dbo].[Doctor] ([DoctorID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_PrimaryCarePhysicianID]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_ReferringPhysician] FOREIGN KEY([ReferringPhysicianID])REFERENCES [dbo].[Doctor] ([DoctorID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_ReferringPhysician]GOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_ServiceLocationID] FOREIGN KEY([DefaultServiceLocationID])REFERENCES [dbo].[ServiceLocation] ([ServiceLocationID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_ServiceLocationID]GOALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]GOALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_CreatedUserID] DEFAULT (0) FOR [CreatedUserID]GOALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]GOALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_ModifiedUserID] DEFAULT (0) FOR [ModifiedUserID]GOALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_Active] DEFAULT ((1)) FOR [Active]GOALTER TABLE [dbo].[Patient] ADD DEFAULT ((1)) FOR [SendEmailCorrespondence]GOALTER TABLE [dbo].[Patient] ADD DEFAULT ((0)) FOR [PhonecallRemindersEnabled]GOALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_Guid] DEFAULT (newid()) FOR [PatientGuid]GO[/code]Wed, 06 Mar 2013 12:59:28 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxOooh, a GOTO, haven't seen one of those in a while :-DWhat else is going on in the TRIGGER? code?And can you post the tables and all indexes of the tables involved in the deadlock?Wed, 06 Mar 2013 12:28:56 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxBasically we are receiving XML from our customer and then taking that data and updating our records in multiple places. Here is the stored proc, it's really ugly.[code="sql"]IF EXISTS ( SELECT * FROM SYSOBJECTS WHERE Name = 'WebServiceDataProvider_UpdatePatient' AND TYPE = 'P' ) DROP PROCEDURE dbo.WebServiceDataProvider_UpdatePatientGO/*WebServiceDataProvider_UpdatePatient '<PatientUpdate> <PatientID>1</PatientID> <Practice> <PracticeID>1</PracticeID> </Practice> <ZipCode>92868</ZipCode> </PatientUpdate>'*/CREATE PROCEDURE [dbo].[WebServiceDataProvider_UpdatePatient] @updatePatientInfo XML , @userId INT = 0AS BEGIN SET NOCOUNT ON ; CREATE TABLE #CasesAffected ( CaseNodeID INT , PatientID INT , PatientCaseID INT , ExternalID VARCHAR(25) ) ; CREATE TABLE #PoliciesAffected ( CaseNodeID INT , RID INT , PatientCaseID INT , InsurancePolicyID INT , InsuranceCompanyPlanID INT , ExternalID VARCHAR(25) ) ; CREATE TABLE #AuthosAffected ( CaseNodeID INT , RID INT , PatientCaseID INT , AuthorizationID INT , InsurancePolicyID INT ) ; CREATE TABLE #ExistingPolicies ( InsurancePolicyID INT ) ; CREATE TABLE #GeneralInfo ( PatientID VARCHAR(MAX) , ExternalVendorID VARCHAR(MAX) , PatientExternalID VARCHAR(MAX) , PracticeID VARCHAR(MAX) , PracticeExternalID VARCHAR(MAX) , PracticeName VARCHAR(MAX) , Prefix VARCHAR(MAX) , FirstName VARCHAR(MAX) , MiddleName VARCHAR(MAX) , LastName VARCHAR(MAX) , Suffix VARCHAR(MAX) , SocialSecurityNumber VARCHAR(MAX) , DateofBirth VARCHAR(MAX) , Age VARCHAR(MAX) , Gender VARCHAR(MAX) , MedicalRecordNumber VARCHAR(MAX) , MaritalStatus VARCHAR(MAX) , ReferralSource VARCHAR(MAX) , AddressLine1 VARCHAR(MAX) , AddressLine2 VARCHAR(MAX) , City VARCHAR(MAX) , State VARCHAR(MAX) , Country VARCHAR(MAX) , ZipCode VARCHAR(MAX) , HomePhone VARCHAR(MAX) , HomePhoneExt VARCHAR(MAX) , WorkPhone VARCHAR(MAX) , WorkPhoneExt VARCHAR(MAX) , MobilePhone VARCHAR(MAX) , MobilePhoneExt VARCHAR(MAX) , EmailAddress VARCHAR(MAX) , Note VARCHAR(MAX) , CollectionCategoryName VARCHAR(MAX) , GuarantorDifferentThanPatient VARCHAR(MAX) , GuarantorPrefix VARCHAR(MAX) , GuarantorFirstName VARCHAR(MAX) , GuarantorMiddleName VARCHAR(MAX) , GuarantorLastName VARCHAR(MAX) , GuarantorSuffix VARCHAR(MAX) , RelationshiptoGuarantor VARCHAR(MAX) , GuarantorAddressLine1 VARCHAR(MAX) , GuarantorAddressLine2 VARCHAR(MAX) , GuarantorCity VARCHAR(MAX) , GuarantorState VARCHAR(MAX) , GuarantorCountry VARCHAR(MAX) , GuarantorZipCode VARCHAR(MAX) , EmploymentStatus VARCHAR(MAX) , EmployerID VARCHAR(MAX) , EmployerName VARCHAR(MAX) , EmployerAddressLine1 VARCHAR(MAX) , EmployerAddressLine2 VARCHAR(MAX) , EmployerCity VARCHAR(MAX) , EmployerState VARCHAR(MAX) , EmployerCountry VARCHAR(MAX) , EmployerZipCode VARCHAR(MAX) , DefaultRenderingProviderID VARCHAR(MAX) , DefaultRenderingProviderExternalID VARCHAR(MAX) , DefaultRenderingProviderFullName VARCHAR(MAX) , PrimaryCarePhysicianID VARCHAR(MAX) , PrimaryCarePhysicianExternalID VARCHAR(MAX) , PrimaryCarePhysicianFullName VARCHAR(MAX) , ReferringProviderID VARCHAR(MAX) , ReferringProviderExternalID VARCHAR(MAX) , ReferringProviderFullName VARCHAR(MAX) , EmergencyName VARCHAR(MAX) , EmergencyPhone VARCHAR(MAX) , EmergencyPhoneExt VARCHAR(MAX) ) ; CREATE TABLE #Cases ( TID INT IDENTITY(1, 1) , nodeID INT , CaseID VARCHAR(MAX) , ExternalID VARCHAR(MAX) , CaseName VARCHAR(MAX) , CaseDescription VARCHAR(MAX) , ReferringProviderID VARCHAR(MAX) , ReferringProviderExternalID VARCHAR(MAX) , ReferringProviderFullName VARCHAR(MAX) , SendPatientStatements VARCHAR(MAX) , PayerScenario VARCHAR(MAX) , RelatedToAutoAccident VARCHAR(MAX) , RelatedToAutoAccidentState VARCHAR(MAX) , RelatedToEmployment VARCHAR(MAX) , RelatedToPregnancy VARCHAR(MAX) , RelatedToAbuse VARCHAR(MAX) , RelatedToOther VARCHAR(MAX) , RelatedToEPSDT VARCHAR(MAX) , EPSDTReason VARCHAR(MAX) , RelatedToFamilyPlanning VARCHAR(MAX) , RelatedToEmergency VARCHAR(MAX) , InjuryStartDate VARCHAR(MAX) , InjuryEndDate VARCHAR(MAX) , SameorSimilarIllnessStartDate VARCHAR(MAX) , SameorSimilarIllnessEndDate VARCHAR(MAX) , UnabletoWorkStartDate VARCHAR(MAX) , UnabletoWorkEndDate VARCHAR(MAX) , RelatedDisabilityStartDate VARCHAR(MAX) , RelatedDisabilityEndDate VARCHAR(MAX) , RelatedHospitalizationStartDate VARCHAR(MAX) , RelatedHospitalizationEndDate VARCHAR(MAX) , LastMenstrualPeriodDate VARCHAR(MAX) , LastSeenDate VARCHAR(MAX) , ReferralDate VARCHAR(MAX) , AcuteManifestationDate VARCHAR(MAX) , LastXRayDate VARCHAR(MAX) , AccidentDate VARCHAR(MAX) , Active VARCHAR(MAX) ) ; CREATE TABLE #Location ( LocationID VARCHAR(MAX) , LocationName VARCHAR(MAX) , AddressLine1 VARCHAR(MAX) , AddressLine2 VARCHAR(MAX) , City VARCHAR(MAX) , State VARCHAR(MAX) , Country VARCHAR(MAX) , ZipCode VARCHAR(MAX) , BillingName VARCHAR(MAX) , Phone VARCHAR(MAX) , PhoneExt VARCHAR(MAX) , FaxPhone VARCHAR(MAX) , FaxPhoneExt VARCHAR(MAX) , NPI VARCHAR(MAX) , FacilityIDType VARCHAR(MAX) , FacilityID VARCHAR(MAX) , CLIANumber VARCHAR(MAX) , POS VARCHAR(MAX) ) ; CREATE TABLE #Alert ( [Message] VARCHAR(MAX) , ShowWhenDisplayingPatientDetails VARCHAR(MAX) , ShowWhenSchedulingAppointments VARCHAR(MAX) , ShowWhenEnteringEncounters VARCHAR(MAX) , ShowWhenViewingClaimDetails VARCHAR(MAX) , ShowWhenPostingPayments VARCHAR(MAX) , ShowWhenPreparingPatientStatements VARCHAR(MAX) ) ; CREATE TABLE #Insurance ( TID INT IDENTITY(1, 1) , parentNodeID INT , InsurancePolicyID VARCHAR(MAX) , ExternalID VARCHAR(MAX) , CompanyID VARCHAR(MAX) , CompanyName VARCHAR(MAX) , PlanID VARCHAR(MAX) , PlanName VARCHAR(MAX) , AddressLine1 VARCHAR(MAX) , AddressLine2 VARCHAR(MAX) , City VARCHAR(MAX) , State VARCHAR(MAX) , Country VARCHAR(MAX) , ZipCode VARCHAR(MAX) , AdjusterPrefix VARCHAR(MAX) , AdjusterFirstName VARCHAR(MAX) , AdjusterMiddleName VARCHAR(MAX) , AdjusterLastName VARCHAR(MAX) , AdjusterSuffix VARCHAR(MAX) , AdjusterPhoneNumber VARCHAR(MAX) , AdjusterPhoneNumberExt VARCHAR(MAX) , AdjusterFaxNumber VARCHAR(MAX) , AdjusterFaxNumberExt VARCHAR(MAX) , PolicyNumber VARCHAR(MAX) , PolicyGroupNumber VARCHAR(MAX) , Copay VARCHAR(MAX) , Deductible VARCHAR(MAX) , EffectiveStartDate VARCHAR(MAX) , EffectiveEndDate VARCHAR(MAX) , InsuredPatientRelationshipToInsured VARCHAR(MAX) , InsuredPrefix VARCHAR(MAX) , InsuredFirstName VARCHAR(MAX) , InsuredMiddleName VARCHAR(MAX) , InsuredLastName VARCHAR(MAX) , InsuredSuffix VARCHAR(MAX) , InsuredAddressLine1 VARCHAR(MAX) , InsuredAddressLine2 VARCHAR(MAX) , InsuredCity VARCHAR(MAX) , InsuredState VARCHAR(MAX) , InsuredCountry VARCHAR(MAX) , InsuredZipCode VARCHAR(MAX) , InsuredIDNumber VARCHAR(MAX) , InsuredSocialSecurityNumber VARCHAR(MAX) , InsuredDateofBirth VARCHAR(MAX) , InsuredGender VARCHAR(MAX) , InsuredPolicyThroughEmployer VARCHAR(MAX) , InsuredEmployer VARCHAR(MAX) , PolicyNotes VARCHAR(MAX) , Active VARCHAR(MAX) , Precedence VARCHAR(MAX) ) ; CREATE TABLE #Authorization ( TID INT IDENTITY(1, 1) , parentNodeID INT , AuthorizationID VARCHAR(MAX) , InsurancePolicyID VARCHAR(MAX) , InsurancePlanID VARCHAR(MAX) , InsurancePlanName VARCHAR(MAX) , Number VARCHAR(MAX) , NumberOfVisits VARCHAR(MAX) , ContactFullName VARCHAR(MAX) , ContactPhone VARCHAR(MAX) , ContactPhoneExt VARCHAR(MAX) , Notes VARCHAR(MAX) , StartDate VARCHAR(MAX) , EndDate VARCHAR(MAX) ) ; CREATE TABLE #InsuranceCaseMap ( CaseNodeID INT , InsurancesNodeID INT ) ; CREATE TABLE #AuthoCaseMap ( CaseNodeID INT , AuthosNodeID INT ) ; DECLARE @xdoc INT ; EXEC sp_xml_preparedocument @xdoc OUTPUT, @updatePatientInfo ; INSERT INTO #GeneralInfo ( PatientID , ExternalVendorID , PatientExternalID , PracticeID , PracticeExternalID , PracticeName , Prefix , FirstName , MiddleName , LastName , Suffix , SocialSecurityNumber , DateofBirth , Age , Gender , MedicalRecordNumber , MaritalStatus , ReferralSource , AddressLine1 , AddressLine2 , City , State , Country , ZipCode , HomePhone , HomePhoneExt , WorkPhone , WorkPhoneExt , MobilePhone , MobilePhoneExt , EmailAddress , Note , CollectionCategoryName , GuarantorDifferentThanPatient , GuarantorPrefix , GuarantorFirstName , GuarantorMiddleName , GuarantorLastName , GuarantorSuffix , RelationshiptoGuarantor , GuarantorAddressLine1 , GuarantorAddressLine2 , GuarantorCity , GuarantorState , GuarantorCountry , GuarantorZipCode , EmploymentStatus , EmployerID , EmployerName , EmployerAddressLine1 , EmployerAddressLine2 , EmployerCity , EmployerState , EmployerCountry , EmployerZipCode , DefaultRenderingProviderID , DefaultRenderingProviderExternalID , DefaultRenderingProviderFullName , PrimaryCarePhysicianID , PrimaryCarePhysicianExternalID , PrimaryCarePhysicianFullName , ReferringProviderID , ReferringProviderExternalID , ReferringProviderFullName , EmergencyName , EmergencyPhone , EmergencyPhoneExt ) SELECT PatientID = ISNULL(PatientID, '') , ExternalVendorID = dbo.fn_ZeroLengthStringToNull(ExternalVendorID) , PatientExternalID = dbo.fn_ZeroLengthStringToNull(PatientExternalID) , PracticeID = dbo.fn_ZeroLengthStringToNull(PracticeID) , PracticeExternalID = dbo.fn_ZeroLengthStringToNull(PracticeExternalID) , PracticeName = dbo.fn_ZeroLengthStringToNull(PracticeName) , Prefix = dbo.KAPI_fn_TrimOrNULL(Prefix) , FirstName = dbo.fn_ZeroLengthStringToNull(FirstName) , MiddleName = dbo.KAPI_fn_TrimOrNULL(MiddleName) , LastName = dbo.fn_ZeroLengthStringToNull(LastName) , Suffix = dbo.KAPI_fn_TrimOrNULL(Suffix) , SocialSecurityNumber = dbo.KAPI_fn_StripNonNumericOrNULL(SocialSecurityNumber) , DateofBirth = dbo.KAPI_fn_TrimOrNULL(DateofBirth) , Age = dbo.KAPI_fn_TrimOrNULL(Age) , Gender = dbo.fn_ZeroLengthStringToNull(Gender) , MedicalRecordNumber = dbo.KAPI_fn_TrimOrNULL(MedicalRecordNumber) , MaritalStatus = dbo.fn_ZeroLengthStringToNull(MaritalStatus) , ReferralSource = dbo.KAPI_fn_TrimOrNULL(ReferralSource) , AddressLine1 = dbo.KAPI_fn_TrimOrNULL(AddressLine1) , AddressLine2 = dbo.KAPI_fn_TrimOrNULL(AddressLine2) , City = dbo.KAPI_fn_TrimOrNULL(City) , State = dbo.KAPI_fn_TrimOrNULL(State) , Country = dbo.KAPI_fn_TrimOrNULL(Country) , ZipCode = dbo.KAPI_fn_StripNonNumericOrNULL(ZipCode) , HomePhone = dbo.KAPI_fn_StripNonNumericOrNULL(HomePhone) , HomePhoneExt = dbo.KAPI_fn_StripNonNumericOrNULL(HomePhoneExt) , WorkPhone = dbo.KAPI_fn_StripNonNumericOrNULL(WorkPhone) , WorkPhoneExt = dbo.KAPI_fn_StripNonNumericOrNULL(WorkPhoneExt) , MobilePhone = dbo.KAPI_fn_StripNonNumericOrNULL(MobilePhone) , MobilePhoneExt = dbo.KAPI_fn_StripNonNumericOrNULL(MobilePhoneExt) , EmailAddress = dbo.KAPI_fn_TrimOrNULL(EmailAddress) , Note = dbo.KAPI_fn_TrimOrNULL(Note) , CollectionCategoryName = dbo.fn_ZeroLengthStringToNull(CollectionCategoryName) , GuarantorDifferentThanPatient = dbo.KAPI_fn_TrimOrNULL(GuarantorDifferentThanPatient) , GuarantorPrefix = dbo.KAPI_fn_TrimOrNULL(GuarantorPrefix) , GuarantorFirstName = dbo.KAPI_fn_TrimOrNULL(GuarantorFirstName) , GuarantorMiddleName = dbo.KAPI_fn_TrimOrNULL(GuarantorMiddleName) , GuarantorLastName = dbo.KAPI_fn_TrimOrNULL(GuarantorLastName) , GuarantorSuffix = dbo.KAPI_fn_TrimOrNULL(GuarantorSuffix) , RelationshiptoGuarantor = dbo.fn_ZeroLengthStringToNull(RelationshiptoGuarantor) , GuarantorAddressLine1 = dbo.KAPI_fn_TrimOrNULL(GuarantorAddressLine1) , GuarantorAddressLine2 = dbo.KAPI_fn_TrimOrNULL(GuarantorAddressLine2) , GuarantorCity = dbo.KAPI_fn_TrimOrNULL(GuarantorCity) , GuarantorState = dbo.KAPI_fn_TrimOrNULL(GuarantorState) , GuarantorCountry = dbo.KAPI_fn_TrimOrNULL(GuarantorCountry) , GuarantorZipCode = dbo.KAPI_fn_StripNonNumericOrNULL(GuarantorZipCode) , EmploymentStatus = dbo.fn_ZeroLengthStringToNull(EmploymentStatus) , EmployerID = dbo.fn_ZeroLengthStringToNull(EmployerID) , EmployerName = dbo.fn_ZeroLengthStringToNull(EmployerName) , EmployerAddressLine1 = dbo.KAPI_fn_TrimOrNULL(EmployerAddressLine1) , EmployerAddressLine2 = dbo.KAPI_fn_TrimOrNULL(EmployerAddressLine2) , EmployerCity = dbo.KAPI_fn_TrimOrNULL(EmployerCity) , EmployerState = dbo.KAPI_fn_TrimOrNULL(EmployerState) , EmployerCountry = dbo.KAPI_fn_TrimOrNULL(EmployerCountry) , EmployerZipCode = dbo.KAPI_fn_StripNonNumericOrNULL(EmployerZipCode) , DefaultRenderingProviderID = ISNULL(DefaultRenderingProviderID, '') , DefaultRenderingProviderExternalID = dbo.fn_ZeroLengthStringToNull(DefaultRenderingProviderExternalID) , DefaultRenderingProviderFullName = dbo.fn_ZeroLengthStringToNull(DefaultRenderingProviderFullName) , PrimaryCarePhysicianID = ISNULL(PrimaryCarePhysicianID, '') , PrimaryCarePhysicianExternalID = dbo.fn_ZeroLengthStringToNull(PrimaryCarePhysicianExternalID) , PrimaryCarePhysicianFullName = dbo.fn_ZeroLengthStringToNull(PrimaryCarePhysicianFullName) , ReferringProviderID = ISNULL(ReferringProviderID, '') , ReferringProviderExternalID = dbo.fn_ZeroLengthStringToNull(ReferringProviderExternalID) , ReferringProviderFullName = dbo.fn_ZeroLengthStringToNull(ReferringProviderFullName) , EmergencyName = dbo.fn_ZeroLengthStringToNull(EmergencyName) , EmergencyPhone = dbo.KAPI_fn_StripNonNumericOrNULL(EmergencyPhone) , EmergencyPhoneExt = dbo.KAPI_fn_StripNonNumericOrNULL(EmergencyPhoneExt) FROM OPENXML(@xdoc,'/PatientUpdate') WITH ( PatientID VARCHAR(MAX) 'PatientID', ExternalVendorID VARCHAR(MAX) 'ExternalVendorID', PatientExternalID VARCHAR(MAX) 'PatientExternalID', PracticeID VARCHAR(MAX) 'Practice/PracticeID', PracticeName VARCHAR(MAX) 'Practice/PracticeName', PracticeExternalID VARCHAR(MAX) 'Practice/ExternalID', Prefix VARCHAR(MAX) 'Prefix', FirstName VARCHAR(MAX) 'FirstName', MiddleName VARCHAR(MAX) 'MiddleName', LastName VARCHAR(MAX) 'LastName', Suffix VARCHAR(MAX) 'Suffix', SocialSecurityNumber VARCHAR(MAX) 'SocialSecurityNumber', DateofBirth VARCHAR(MAX) 'DateofBirth', Age VARCHAR(MAX) 'Age', Gender VARCHAR(MAX) 'Gender', MedicalRecordNumber VARCHAR(MAX) 'MedicalRecordNumber', MaritalStatus VARCHAR(MAX) 'MaritalStatus', ReferralSource VARCHAR(MAX) 'ReferralSource', AddressLine1 VARCHAR(MAX) 'AddressLine1', AddressLine2 VARCHAR(MAX) 'AddressLine2', City VARCHAR(MAX) 'City', State VARCHAR(MAX) 'State', Country VARCHAR(MAX) 'Country', ZipCode VARCHAR(MAX) 'ZipCode', HomePhone VARCHAR(MAX) 'HomePhone', HomePhoneExt VARCHAR(MAX) 'HomePhoneExt', WorkPhone VARCHAR(MAX) 'WorkPhone', WorkPhoneExt VARCHAR(MAX) 'WorkPhoneExt', MobilePhone VARCHAR(MAX) 'MobilePhone', MobilePhoneExt VARCHAR(MAX) 'MobilePhoneExt', EmailAddress VARCHAR(MAX) 'EmailAddress', Note VARCHAR(MAX) 'Note', CollectionCategoryName VARCHAR(MAX) 'CollectionCategoryName', GuarantorDifferentThanPatient VARCHAR(MAX) 'Guarantor/DifferentThanPatient', GuarantorPrefix VARCHAR(MAX) 'Guarantor/Prefix', GuarantorFirstName VARCHAR(MAX) 'Guarantor/FirstName', GuarantorMiddleName VARCHAR(MAX) 'Guarantor/MiddleName', GuarantorLastName VARCHAR(MAX) 'Guarantor/LastName', GuarantorSuffix VARCHAR(MAX) 'Guarantor/Suffix', RelationshiptoGuarantor VARCHAR(MAX) 'Guarantor/RelationshiptoGuarantor', GuarantorAddressLine1 VARCHAR(MAX) 'Guarantor/AddressLine1', GuarantorAddressLine2 VARCHAR(MAX) 'Guarantor/AddressLine2', GuarantorCity VARCHAR(MAX) 'Guarantor/City', GuarantorState VARCHAR(MAX) 'Guarantor/State', GuarantorCountry VARCHAR(MAX) 'Guarantor/Country', GuarantorZipCode VARCHAR(MAX) 'Guarantor/ZipCode', EmploymentStatus VARCHAR(MAX) 'Employer/EmploymentStatus', EmployerID VARCHAR(MAX) 'Employer/EmployerID', EmployerName VARCHAR(MAX) 'Employer/EmployerName', EmployerAddressLine1 VARCHAR(MAX) 'Employer/AddressLine1', EmployerAddressLine2 VARCHAR(MAX) 'Employer/AddressLine2', EmployerCity VARCHAR(MAX) 'Employer/City', EmployerState VARCHAR(MAX) 'Employer/State', EmployerCountry VARCHAR(MAX) 'Employer/Country', EmployerZipCode VARCHAR(MAX) 'Employer/ZipCode', DefaultRenderingProviderID VARCHAR(MAX) 'DefaultRenderingProvider/ProviderID', DefaultRenderingProviderExternalID VARCHAR(MAX) 'DefaultRenderingProvider/ExternalID', DefaultRenderingProviderFullName VARCHAR(MAX) 'DefaultRenderingProvider/FullName', PrimaryCarePhysicianID VARCHAR(MAX) 'PrimaryCarePhysician/PhysicianID', PrimaryCarePhysicianExternalID VARCHAR(MAX) 'PrimaryCarePhysician/ExternalID', PrimaryCarePhysicianFullName VARCHAR(MAX) 'PrimaryCarePhysician/FullName', ReferringProviderID VARCHAR(MAX) 'ReferringProvider/ProviderID', ReferringProviderExternalID VARCHAR(MAX) 'ReferringProvider/ExternalID', ReferringProviderFullName VARCHAR(MAX) 'ReferringProvider/FullName', EmergencyName VARCHAR(MAX) 'EmergencyName', EmergencyPhone VARCHAR(MAX) 'EmergencyPhone', EmergencyPhoneExt VARCHAR(MAX) 'EmergencyPhoneExt' ) ; INSERT INTO #Location ( LocationID , LocationName , AddressLine1 , AddressLine2 , City , State , Country , ZipCode , BillingName , Phone , PhoneExt , FaxPhone , FaxPhoneExt , NPI , FacilityIDType , FacilityID , CLIANumber , POS ) SELECT LocationID = dbo.fn_ZeroLengthStringToNull(LocationID) , LocationName = dbo.fn_ZeroLengthStringToNull(LocationName) , AddressLine1 = dbo.KAPI_fn_TrimOrNULL(AddressLine1) , AddressLine2 = dbo.KAPI_fn_TrimOrNULL(AddressLine2) , City = dbo.KAPI_fn_TrimOrNULL(City) , State = dbo.KAPI_fn_TrimOrNULL(State) , Country = dbo.KAPI_fn_TrimOrNULL(Country) , ZipCode = dbo.KAPI_fn_StripNonNumericOrNULL(ZipCode) , BillingName = dbo.KAPI_fn_TrimOrNULL(BillingName) , Phone = dbo.KAPI_fn_StripNonNumericOrNULL(Phone) , PhoneExt = dbo.KAPI_fn_StripNonNumericOrNULL(PhoneExt) , FaxPhone = dbo.KAPI_fn_StripNonNumericOrNULL(FaxPhone) , FaxPhoneExt = dbo.KAPI_fn_StripNonNumericOrNULL(FaxPhoneExt) , NPI = dbo.KAPI_fn_TrimOrNULL(NPI) , FacilityIDType = dbo.KAPI_fn_TrimOrNULL(FacilityIDType) , FacilityID = dbo.KAPI_fn_TrimOrNULL(FacilityID) , CLIANumber = dbo.KAPI_fn_TrimOrNULL(CLIANumber) , POS = dbo.KAPI_fn_TrimOrNULL(POS) FROM OPENXML(@xdoc,'/PatientUpdate/DefaultServiceLocation') WITH ( LocationID VARCHAR(MAX) 'LocationID', LocationName VARCHAR(MAX) 'LocationName', AddressLine1 VARCHAR(MAX) 'AddressLine1', AddressLine2 VARCHAR(MAX) 'AddressLine2', City VARCHAR(MAX) 'City', State VARCHAR(MAX) 'State', Country VARCHAR(MAX) 'Country', ZipCode VARCHAR(MAX) 'ZipCode', BillingName VARCHAR(MAX) 'BillingName', Phone VARCHAR(MAX) 'Phone', PhoneExt VARCHAR(MAX) 'PhoneExt', FaxPhone VARCHAR(MAX) 'FaxPhone', FaxPhoneExt VARCHAR(MAX) 'FaxPhoneExt', NPI VARCHAR(MAX) 'NPI', FacilityIDType VARCHAR(MAX) 'FacilityIDType', FacilityID VARCHAR(MAX) 'FacilityID', CLIANumber VARCHAR(MAX) 'CLIANumber', POS VARCHAR(MAX) 'POS' ) INSERT INTO #Cases ( nodeID , CaseID , ExternalID , CaseName , CaseDescription , ReferringProviderID , ReferringProviderExternalID , ReferringProviderFullName , SendPatientStatements , PayerScenario , RelatedToAutoAccident , RelatedToAutoAccidentState , RelatedToEmployment , RelatedToPregnancy , RelatedToAbuse , RelatedToOther , RelatedToEPSDT , EPSDTReason , RelatedToFamilyPlanning , RelatedToEmergency , InjuryStartDate , InjuryEndDate , SameorSimilarIllnessStartDate , SameorSimilarIllnessEndDate , UnabletoWorkStartDate , UnabletoWorkEndDate , RelatedDisabilityStartDate , RelatedDisabilityEndDate , RelatedHospitalizationStartDate , RelatedHospitalizationEndDate , LastMenstrualPeriodDate , LastSeenDate , ReferralDate , AcuteManifestationDate , LastXRayDate , AccidentDate , Active ) SELECT nodeID , CaseID = dbo.KAPI_fn_TrimOrNULL(CaseID) , ExternalID = dbo.fn_ZeroLengthStringToNull(ExternalID) , CaseName = dbo.fn_ZeroLengthStringToNull(CaseName) , CaseDescription = dbo.KAPI_fn_TrimOrNULL(CaseDescription) , ReferringProviderID = dbo.KAPI_fn_TrimOrNULL(ReferringProviderID) , ReferringProviderExternalID = dbo.fn_ZeroLengthStringToNull(ReferringProviderExternalID) , ReferringProviderFullName = dbo.fn_ZeroLengthStringToNull(ReferringProviderFullName) , SendPatientStatements = dbo.KAPI_fn_TrimOrNULL(SendPatientStatements) , PayerScenario = dbo.fn_ZeroLengthStringToNull(PayerScenario) , RelatedToAutoAccident = dbo.KAPI_fn_TrimOrNULL(RelatedToAutoAccident) , RelatedToAutoAccidentState = dbo.KAPI_fn_TrimOrNULL(RelatedToAutoAccidentState) , RelatedToEmployment = dbo.KAPI_fn_TrimOrNULL(RelatedToEmployment) , RelatedToPregnancy = dbo.KAPI_fn_TrimOrNULL(RelatedToPregnancy) , RelatedToAbuse = dbo.KAPI_fn_TrimOrNULL(RelatedToAbuse) , RelatedToOther = dbo.KAPI_fn_TrimOrNULL(RelatedToOther) , RelatedToEPSDT = dbo.KAPI_fn_TrimOrNULL(RelatedToEPSDT) , EPSDTReason = dbo.KAPI_fn_TrimOrNULL(EPSDTReason) , RelatedToFamilyPlanning = dbo.KAPI_fn_TrimOrNULL(RelatedToFamilyPlanning) , RelatedToEmergency = dbo.KAPI_fn_TrimOrNULL(RelatedToEmergency) , InjuryStartDate = ISNULL(InjuryStartDate, '') , InjuryEndDate = ISNULL(InjuryEndDate, '') , SameorSimilarIllnessStartDate = ISNULL(SameorSimilarIllnessStartDate, '') , SameorSimilarIllnessEndDate = ISNULL(SameorSimilarIllnessEndDate, '') , UnabletoWorkStartDate = ISNULL(UnabletoWorkStartDate, '') , UnabletoWorkEndDate = ISNULL(UnabletoWorkEndDate, '') , RelatedDisabilityStartDate = ISNULL(RelatedDisabilityStartDate, '') , RelatedDisabilityEndDate = ISNULL(RelatedDisabilityEndDate, '') , RelatedHospitalizationStartDate = ISNULL(RelatedHospitalizationStartDate, '') , RelatedHospitalizationEndDate = ISNULL(RelatedHospitalizationEndDate, '') , LastMenstrualPeriodDate = ISNULL(LastMenstrualPeriodDate, '') , LastSeenDate = ISNULL(LastSeenDate, '') , ReferralDate = ISNULL(ReferralDate, '') , AcuteManifestationDate = ISNULL(AcuteManifestationDate, '') , LastXRayDate = ISNULL(LastXRayDate, '') , AccidentDate = ISNULL(AccidentDate, '') , Active = dbo.fn_ZeroLengthStringToNull(Active) FROM OPENXML(@xdoc,'/PatientUpdate/Cases/Case') WITH ( nodeID INT '@mp:id', CaseID VARCHAR(MAX) 'CaseID', ExternalID VARCHAR(MAX) 'ExternalID', CaseName VARCHAR(MAX) 'CaseName', CaseDescription VARCHAR(MAX) 'CaseDescription', ReferringProviderID VARCHAR(MAX) 'ReferringProviderID', ReferringProviderExternalID VARCHAR(MAX) 'ReferringProviderExternalID', ReferringProviderFullName VARCHAR(MAX) 'ReferringProviderFullName', SendPatientStatements VARCHAR(MAX) 'SendPatientStatements', PayerScenario VARCHAR(MAX) 'PayerScenario', RelatedToAutoAccident VARCHAR(MAX) 'CaseCondition/RelatedToAutoAccident', RelatedToAutoAccidentState VARCHAR(MAX) 'CaseCondition/RelatedToAutoAccidentState', RelatedToEmployment VARCHAR(MAX) 'CaseCondition/RelatedToEmployment', RelatedToPregnancy VARCHAR(MAX) 'CaseCondition/RelatedToPregnancy', RelatedToAbuse VARCHAR(MAX) 'CaseCondition/RelatedToAbuse', RelatedToOther VARCHAR(MAX) 'CaseCondition/RelatedToOther', RelatedToEPSDT VARCHAR(MAX) 'CaseCondition/RelatedToEPSDT', EPSDTReason VARCHAR(MAX) 'CaseCondition/EPSDTReason', RelatedToFamilyPlanning VARCHAR(MAX) 'CaseCondition/RelatedToFamilyPlanning', RelatedToEmergency VARCHAR(MAX) 'CaseCondition/RelatedToEmergency', InjuryStartDate VARCHAR(MAX) 'CaseDates/InjuryStartDate', InjuryEndDate VARCHAR(MAX) 'CaseDates/InjuryEndDate', SameorSimilarIllnessStartDate VARCHAR(MAX) 'CaseDates/SameorSimilarIllnessStartDate', SameorSimilarIllnessEndDate VARCHAR(MAX) 'CaseDates/SameorSimilarIllnessEndDate', UnabletoWorkStartDate VARCHAR(MAX) 'CaseDates/UnabletoWorkStartDate', UnabletoWorkEndDate VARCHAR(MAX) 'CaseDates/UnabletoWorkEndDate', RelatedDisabilityStartDate VARCHAR(MAX) 'CaseDates/RelatedDisabilityStartDate', RelatedDisabilityEndDate VARCHAR(MAX) 'CaseDates/RelatedDisabilityEndDate', RelatedHospitalizationStartDate VARCHAR(MAX) 'CaseDates/RelatedHospitalizationStartDate', RelatedHospitalizationEndDate VARCHAR(MAX) 'CaseDates/RelatedHospitalizationEndDate', LastMenstrualPeriodDate VARCHAR(MAX) 'CaseDates/LastMenstrualPeriodDate', LastSeenDate VARCHAR(MAX) 'CaseDates/LastSeenDate', ReferralDate VARCHAR(MAX) 'CaseDates/ReferralDate', AcuteManifestationDate VARCHAR(MAX) 'CaseDates/AcuteManifestationDate', LastXRayDate VARCHAR(MAX) 'CaseDates/LastXRayDate', AccidentDate VARCHAR(MAX) 'CaseDates/AccidentDate', Active VARCHAR(MAX) 'Active' ) ; INSERT INTO #Insurance ( InsurancePolicyID , ExternalID , parentNodeID , CompanyID , CompanyName , PlanID , PlanName , AddressLine1 , AddressLine2 , City , State , Country , ZipCode , AdjusterPrefix , AdjusterFirstName , AdjusterMiddleName , AdjusterLastName , AdjusterSuffix , AdjusterPhoneNumber , AdjusterPhoneNumberExt , AdjusterFaxNumber , AdjusterFaxNumberExt , PolicyNumber , PolicyGroupNumber , Copay , Deductible , EffectiveStartDate , EffectiveEndDate , InsuredPatientRelationshipToInsured , InsuredPrefix , InsuredFirstName , InsuredMiddleName , InsuredLastName , InsuredSuffix , InsuredAddressLine1 , InsuredAddressLine2 , InsuredCity , InsuredState , InsuredCountry , InsuredZipCode , InsuredIDNumber , InsuredSocialSecurityNumber , InsuredDateofBirth , InsuredGender , InsuredPolicyThroughEmployer , InsuredEmployer , PolicyNotes , Active , Precedence ) SELECT InsurancePolicyID = dbo.fn_ZeroLengthStringToNull(InsurancePolicyID) , ExternalID = dbo.fn_ZeroLengthStringToNull(ExternalID) , parentNodeID , CompanyID = dbo.fn_ZeroLengthStringToNull(CompanyID) , CompanyName = dbo.fn_ZeroLengthStringToNull(CompanyName) , PlanID = dbo.fn_ZeroLengthStringToNull(PlanID) , PlanName = dbo.fn_ZeroLengthStringToNull(PlanName) , AddressLine1 = dbo.KAPI_fn_TrimOrNULL(AddressLine1) , AddressLine2 = dbo.KAPI_fn_TrimOrNULL(AddressLine2) , City = dbo.KAPI_fn_TrimOrNULL(City) , State = dbo.KAPI_fn_TrimOrNULL(State) , Country = dbo.KAPI_fn_TrimOrNULL(Country) , ZipCode = dbo.KAPI_fn_StripNonNumericOrNULL(ZipCode) , AdjusterPrefix = dbo.KAPI_fn_TrimOrNULL(AdjusterPrefix) , AdjusterFirstName = dbo.fn_ZeroLengthStringToNull(AdjusterFirstName) , AdjusterMiddleName = dbo.KAPI_fn_TrimOrNULL(AdjusterMiddleName) , AdjusterLastName = dbo.fn_ZeroLengthStringToNull(AdjusterLastName) , AdjusterSuffix = dbo.KAPI_fn_TrimOrNULL(AdjusterSuffix) , AdjusterPhoneNumber = dbo.KAPI_fn_StripNonNumericOrNULL(AdjusterPhoneNumber) , AdjusterPhoneNumberExt = dbo.KAPI_fn_StripNonNumericOrNULL(AdjusterPhoneNumberExt) , AdjusterFaxNumber = dbo.KAPI_fn_StripNonNumericOrNULL(AdjusterFaxNumber) , AdjusterFaxNumberExt = dbo.KAPI_fn_StripNonNumericOrNULL(AdjusterFaxNumberExt) , PolicyNumber = dbo.KAPI_fn_TrimOrNULL(PolicyNumber) , PolicyGroupNumber = dbo.KAPI_fn_TrimOrNULL(PolicyGroupNumber) , Copay = dbo.fn_ZeroLengthStringToNull(Copay) , Deductible = dbo.fn_ZeroLengthStringToNull(Deductible) , EffectiveStartDate = dbo.KAPI_fn_TrimOrNULL(EffectiveStartDate) , EffectiveEndDate = dbo.KAPI_fn_TrimOrNULL(EffectiveEndDate) , InsuredPatientRelationshipToInsured = dbo.fn_ZeroLengthStringToNull(InsuredPatientRelationshipToInsured) , InsuredPrefix = dbo.KAPI_fn_TrimOrNULL(InsuredPrefix) , InsuredFirstName = dbo.fn_ZeroLengthStringToNull(InsuredFirstName) , InsuredMiddleName = dbo.KAPI_fn_TrimOrNULL(InsuredMiddleName) , InsuredLastName = dbo.fn_ZeroLengthStringToNull(InsuredLastName) , InsuredSuffix = dbo.KAPI_fn_TrimOrNULL(InsuredSuffix) , InsuredAddressLine1 = dbo.KAPI_fn_TrimOrNULL(InsuredAddressLine1) , InsuredAddressLine2 = dbo.KAPI_fn_TrimOrNULL(InsuredAddressLine2) , InsuredCity = dbo.KAPI_fn_TrimOrNULL(InsuredCity) , InsuredState = dbo.KAPI_fn_TrimOrNULL(InsuredState) , InsuredCountry = dbo.KAPI_fn_TrimOrNULL(InsuredCountry) , InsuredZipCode = dbo.KAPI_fn_StripNonNumericOrNULL(InsuredZipCode) , InsuredIDNumber = dbo.KAPI_fn_TrimOrNULL(InsuredIDNumber) , InsuredSocialSecurityNumber = dbo.KAPI_fn_StripNonNumericOrNULL(InsuredSocialSecurityNumber) , InsuredDateofBirth = dbo.KAPI_fn_TrimOrNULL(InsuredDateofBirth) , InsuredGender = dbo.KAPI_fn_ConvertToGenderCode(InsuredGender) , InsuredPolicyThroughEmployer = dbo.KAPI_fn_TrimOrNULL(InsuredPolicyThroughEmployer) , InsuredEmployer = dbo.KAPI_fn_TrimOrNULL(InsuredEmployer) , PolicyNotes = dbo.KAPI_fn_TrimOrNULL(PolicyNotes) , Active = dbo.fn_ZeroLengthStringToNull(Active) , Precedence = dbo.fn_ZeroLengthStringToNull(Precedence) FROM OPENXML(@xdoc,'/PatientUpdate/Cases/Case/Policies/Policy') WITH ( InsurancePolicyID VARCHAR(MAX) 'InsurancePolicyID', ExternalID VARCHAR(MAX) 'ExternalID', parentNodeID INT '@mp:parentid', CompanyID VARCHAR(MAX) 'CompanyID', CompanyName VARCHAR(MAX) 'CompanyName', PlanID VARCHAR(MAX) 'PlanID', PlanName VARCHAR(MAX) 'PlanName', AddressLine1 VARCHAR(MAX) 'PlanAddressLine1', AddressLine2 VARCHAR(MAX) 'PlanAddressLine2', City VARCHAR(MAX) 'PlanCity', State VARCHAR(MAX) 'PlanState', Country VARCHAR(MAX) 'PlanCountry', ZipCode VARCHAR(MAX) 'PlanZipCode', AdjusterPrefix VARCHAR(MAX) 'Adjuster/Prefix', AdjusterFirstName VARCHAR(MAX) 'Adjuster/FirstName', AdjusterMiddleName VARCHAR(MAX) 'Adjuster/MiddleName', AdjusterLastName VARCHAR(MAX) 'Adjuster/LastName', AdjusterSuffix VARCHAR(MAX) 'Adjuster/Suffix', AdjusterPhoneNumber VARCHAR(MAX) 'Adjuster/PhoneNumber', AdjusterPhoneNumberExt VARCHAR(MAX) 'Adjuster/PhoneNumberExt', AdjusterFaxNumber VARCHAR(MAX) 'Adjuster/FaxNumber', AdjusterFaxNumberExt VARCHAR(MAX) 'Adjuster/FaxNumberExt', PolicyNumber VARCHAR(MAX) 'PolicyNumber', PolicyGroupNumber VARCHAR(MAX) 'PolicyGroupNumber', Copay VARCHAR(MAX) 'Copay', Deductible VARCHAR(MAX) 'Deductible', EffectiveStartDate VARCHAR(MAX) 'EffectiveStartDate', EffectiveEndDate VARCHAR(MAX) 'EffectiveEndDate', InsuredPatientRelationshipToInsured VARCHAR(MAX) 'Insured/PatientRelationshipToInsured', InsuredPrefix VARCHAR(MAX) 'Insured/Prefix', InsuredFirstName VARCHAR(MAX) 'Insured/FirstName', InsuredMiddleName VARCHAR(MAX) 'Insured/MiddleName', InsuredLastName VARCHAR(MAX) 'Insured/LastName', InsuredSuffix VARCHAR(MAX) 'Insured/Suffix', InsuredAddressLine1 VARCHAR(MAX) 'Insured/AddressLine1', InsuredAddressLine2 VARCHAR(MAX) 'Insured/AddressLine2', InsuredCity VARCHAR(MAX) 'Insured/City', InsuredState VARCHAR(MAX) 'Insured/State', InsuredCountry VARCHAR(MAX) 'Insured/Country', InsuredZipCode VARCHAR(MAX) 'Insured/ZipCode', InsuredIDNumber VARCHAR(MAX) 'Insured/IDNumber', InsuredSocialSecurityNumber VARCHAR(MAX) 'Insured/SocialSecurityNumber', InsuredDateofBirth VARCHAR(MAX) 'Insured/DateofBirth', InsuredGender VARCHAR(MAX) 'Insured/Gender', InsuredPolicyThroughEmployer VARCHAR(MAX) 'Insured/PolicyThroughEmployer', InsuredEmployer VARCHAR(MAX) 'Insured/Employer', PolicyNotes VARCHAR(MAX) 'PolicyNotes', Active VARCHAR(MAX) 'Active', Precedence VARCHAR(MAX) 'Precedence' ) ; INSERT INTO #InsuranceCaseMap ( CaseNodeID , InsurancesNodeID ) SELECT parentNodeID , nodeID FROM OPENXML(@xdoc,'/PatientUpdate/Cases/Case/Policies') WITH ( parentNodeID INT '@mp:parentid', nodeID INT '@mp:id') ; INSERT INTO #Authorization ( parentNodeID , AuthorizationID , InsurancePolicyID , InsurancePlanID , InsurancePlanName , Number , NumberOfVisits , ContactFullName , ContactPhone , ContactPhoneExt , Notes , StartDate , EndDate ) SELECT parentNodeID , AuthorizationID = dbo.fn_ZeroLengthStringToNull(AuthorizationID) , InsurancePolicyID = dbo.fn_ZeroLengthStringToNull(InsurancePolicyID) , InsurancePlanID = dbo.fn_ZeroLengthStringToNull(InsurancePlanID) , InsurancePlanName = dbo.fn_ZeroLengthStringToNull(InsurancePlanName) , Number = dbo.KAPI_fn_TrimOrNULL(Number) , NumberOfVisits = dbo.fn_ZeroLengthStringToNull(NumberOfVisits) , ContactFullName = dbo.KAPI_fn_TrimOrNULL(ContactFullName) , ContactPhone = dbo.KAPI_fn_StripNonNumericOrNULL(ContactPhone) , ContactPhoneExt = dbo.KAPI_fn_StripNonNumericOrNULL(ContactPhoneExt) , Notes = dbo.KAPI_fn_TrimOrNULL(Notes) , StartDate = dbo.fn_ZeroLengthStringToNull(StartDate) , EndDate = dbo.fn_ZeroLengthStringToNull(EndDate) FROM OPENXML(@xdoc,'/PatientUpdate/Cases/Case/Authorizations/Authorization') WITH ( parentNodeID INT '@mp:parentid', AuthorizationID VARCHAR(MAX) 'AuthorizationID', InsurancePolicyID VARCHAR(MAX) 'InsurancePolicyID', InsurancePlanID VARCHAR(MAX) 'InsurancePlanID', InsurancePlanName VARCHAR(MAX) 'InsurancePlanName', Number VARCHAR(MAX) 'Number', NumberOfVisits VARCHAR(MAX) 'NumberOfVisits', ContactFullName VARCHAR(MAX) 'ContactFullName', ContactPhone VARCHAR(MAX) 'ContactPhone', ContactPhoneExt VARCHAR(MAX) 'ContactPhoneExt', Notes VARCHAR(MAX) 'Notes', StartDate VARCHAR(MAX) 'StartDate', EndDate VARCHAR(MAX) 'EndDate' ) ; INSERT INTO #AuthoCaseMap ( CaseNodeID , AuthosNodeID ) SELECT parentNodeID , nodeID FROM OPENXML(@xdoc,'/PatientUpdate/Cases/Case/Authorizations') WITH ( parentNodeID INT '@mp:parentid', nodeID INT '@mp:id') ; INSERT INTO #Alert ( [Message] , ShowWhenDisplayingPatientDetails , ShowWhenSchedulingAppointments , ShowWhenEnteringEncounters , ShowWhenViewingClaimDetails , ShowWhenPostingPayments , ShowWhenPreparingPatientStatements ) SELECT [Message] = dbo.KAPI_fn_TrimOrNULL([Message]) , ShowWhenDisplayingPatientDetails = dbo.fn_ZeroLengthStringToNull(ShowWhenDisplayingPatientDetails) , ShowWhenSchedulingAppointments = dbo.fn_ZeroLengthStringToNull(ShowWhenSchedulingAppointments) , ShowWhenEnteringEncounters = dbo.fn_ZeroLengthStringToNull(ShowWhenEnteringEncounters) , ShowWhenViewingClaimDetails = dbo.fn_ZeroLengthStringToNull(ShowWhenViewingClaimDetails) , ShowWhenPostingPayments = dbo.fn_ZeroLengthStringToNull(ShowWhenPostingPayments) , ShowWhenPreparingPatientStatements = dbo.fn_ZeroLengthStringToNull(ShowWhenPreparingPatientStatements) FROM OPENXML(@xdoc,'/PatientUpdate/Alert') WITH ( [Message] VARCHAR(MAX) 'Message', ShowWhenDisplayingPatientDetails VARCHAR(MAX) 'ShowWhenDisplayingPatientDetails', ShowWhenSchedulingAppointments VARCHAR(MAX) 'ShowWhenSchedulingAppointments', ShowWhenEnteringEncounters VARCHAR(MAX) 'ShowWhenEnteringEncounters', ShowWhenViewingClaimDetails VARCHAR(MAX) 'ShowWhenViewingClaimDetails', ShowWhenPostingPayments VARCHAR(MAX) 'ShowWhenPostingPayments', ShowWhenPreparingPatientStatements VARCHAR(MAX) 'ShowWhenPreparingPatientStatements' ) ; EXEC sp_xml_removedocument @xdoc ; DECLARE @ExternalVendorID INT ; DECLARE @PracticeID INT ; DECLARE @PracticeExternalID VARCHAR(25) ; DECLARE @ParamPracticeID VARCHAR(128) ; DECLARE @PracticeName VARCHAR(128) ; --Verify ExternalVendorID SELECT @ExternalVendorID = CASE WHEN ExternalVendorID IS NULL THEN NULL ELSE CAST(ExternalVendorID AS INT) END FROM #GeneralInfo ; SELECT @ExternalVendorID = ExternalVendorID FROM dbo.ExternalVendor WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID ; IF @@ROWCOUNT = 0 SET @ExternalVendorID = NULL ; SELECT @ParamPracticeID = PracticeID , @PracticeName = PracticeName , @PracticeExternalID = PracticeExternalID FROM #GeneralInfo ; EXEC dbo.WebServiceDataProvider_FindPractice @ParamPracticeID, @PracticeName, @ExternalVendorID, @PracticeExternalID, 'PatientUpdate.Practice', @updatePatientInfo OUTPUT, @PracticeID OUTPUT ; --This error is serious enough to kill further processing IF @PracticeID IS NULL GOTO ON_MANUAL_ERROR ; SELECT @PracticeName = Name FROM dbo.Practice WITH ( NOLOCK ) WHERE PracticeID = @PracticeID ; --If CollectionCategoryName specified check for existence or create one DECLARE @CollectionCategoryID INT IF EXISTS ( SELECT 1 FROM #GeneralInfo WHERE CollectionCategoryName IS NOT NULL ) BEGIN SELECT @CollectionCategoryID = cc.CollectionCategoryID FROM #GeneralInfo g INNER JOIN dbo.CollectionCategory cc ( NOLOCK ) ON g.CollectionCategoryName = cc.CollectionCategoryName ; IF @CollectionCategoryID IS NULL BEGIN DECLARE @CollectionCategoryName NCHAR(200) ; SELECT @CollectionCategoryName = CollectionCategoryName FROM #GeneralInfo ; EXEC @CollectionCategoryID= dbo.GeneralDataProvider_CreateCollectionCategory 1, @CollectionCategoryName, NULL, 1, 0, NULL, NULL, 0, NULL ; END END --Data Validation Patient DECLARE @PatientID INT , @PatientExternalID VARCHAR(25) , @PatientPrefix VARCHAR(16) , @PatientFirstName VARCHAR(32) , @PatientMiddleName VARCHAR(32) , @PatientLastName VARCHAR(32) , @PatientSuffix VARCHAR(16) , @PatientAddressLine1 VARCHAR(128) , @PatientAddressLine2 VARCHAR(128) , @PatientCity VARCHAR(32) , @PatientState VARCHAR(2) , @PatientCountry VARCHAR(32) , @PatientZipCode VARCHAR(9) , @PatientGender VARCHAR(1) , @PatientMaritalStatus VARCHAR(1) , @MaritalStatus VARCHAR(32) , @PatientHomePhone VARCHAR(10) , @PatientHomePhoneExt VARCHAR(10) , @PatientWorkPhone VARCHAR(10) , @PatientWorkPhoneExt VARCHAR(10) , @PatientMobilePhone VARCHAR(10) , @PatientMobilePhoneExt VARCHAR(10) , @PatientDateOfBirth DATETIME , @PatientSSN CHAR(9) , @PatientEmail VARCHAR(128) , @PatientNote VARCHAR(MAX) , @GuarantorDifferentThanPatient TINYINT , @EmploymentStatus CHAR(1) , @PatientMedicalRecordNumber VARCHAR(128) , --Employer Details @EmployerID INT , @EmployerName VARCHAR(32) , @EmployerAddressLine1 VARCHAR(128) , @EmployerAddressLine2 VARCHAR(128) , @EmployerCity VARCHAR(32) , @EmployerState VARCHAR(2) , @EmployerCountry VARCHAR(32) , @EmployerZipCode VARCHAR(9) , --Guarantor Details @GuarantorPrefix VARCHAR(16) , @GuarantorFirstName VARCHAR(32) , @GuarantorMiddleName VARCHAR(32) , @GuarantorLastName VARCHAR(32) , @GuarantorSuffix VARCHAR(16) , @RelationshiptoGuarantor VARCHAR(16) , @GuarantorAddressLine1 VARCHAR(128) , @GuarantorAddressLine2 VARCHAR(128) , @GuarantorCity VARCHAR(32) , @GuarantorState VARCHAR(2) , @GuarantorCountry VARCHAR(32) , @GuarantorZipCode VARCHAR(9) , --Provider Details @DefaultRenderingProviderID INT , @DefaultRenderingProviderFullName VARCHAR(256) , @DefaultRenderingProviderExternalID VARCHAR(25) , @PrimaryCarePhysicianID INT , @PrimaryCarePhysicianFullName VARCHAR(256) , @PrimaryCarePhysicianExternalID VARCHAR(25) , @ReferringProviderID INT , @ReferringProviderFullName VARCHAR(256) , @ReferringProviderExternalID VARCHAR(25) , --Referral Source Details @ReferralSource VARCHAR(65) , @Active BIT , @EmergencyName VARCHAR(128) , @EmergencyPhone VARCHAR(10) , @EmergencyPhoneExt VARCHAR(10) ; SELECT @PatientID = dbo.KAPI_fn_ConvertToINT(PatientID, 0, NULL) , @PatientExternalID = PatientExternalID , @PatientPrefix = Prefix , @PatientFirstName = FirstName , @PatientMiddleName = MiddleName , @PatientLastName = LastName , @PatientSuffix = Suffix , @PatientAddressLine1 = AddressLine1 , @PatientAddressLine2 = AddressLine2 , @PatientCity = City , @PatientState = State , @PatientCountry = Country , @PatientZipCode = ZipCode , @PatientGender = Gender , @MaritalStatus = dbo.fn_ZeroLengthStringToNull(MaritalStatus) , @PatientMaritalStatus = CASE WHEN LEN(MaritalStatus) = 1 AND MaritalStatus IN ( 'M', 'S', 'D', 'W' ) THEN MaritalStatus ELSE NULL END , @PatientHomePhone = HomePhone , @PatientHomePhoneExt = HomePhoneExt , @PatientWorkPhone = WorkPhone , @PatientWorkPhoneExt = WorkPhoneExt , @PatientMobilePhone = MobilePhone , @PatientMobilePhoneExt = MobilePhoneExt , @PatientDateOfBirth = dbo.KAPI_fn_ConvertToDateTime(DateofBirth, 0, NULL) , @PatientSSN = SocialSecurityNumber , @PatientEmail = EmailAddress , @PatientNote = Note , @GuarantorDifferentThanPatient = CASE WHEN GuarantorDifferentThanPatient IS NULL THEN NULL ELSE dbo.KAPI_fn_ConvertToBIT(GuarantorDifferentThanPatient) END , @EmploymentStatus = CASE WHEN EmploymentStatus IN ( 'E', 'R', 'S', 'T', 'U' ) THEN EmploymentStatus ELSE NULL END , @PatientMedicalRecordNumber = MedicalRecordNumber , @EmployerID = dbo.KAPI_fn_ConvertToINT(EmployerID, 0, NULL) , @EmployerName = EmployerName , @EmployerAddressLine1 = EmployerAddressLine1 , @EmployerAddressLine2 = EmployerAddressLine2 , @EmployerCity = EmployerCity , @EmployerState = EmployerState , @EmployerCountry = EmployerCountry , @EmployerZipCode = EmployerZipCode , @GuarantorPrefix = GuarantorPrefix , @GuarantorFirstName = GuarantorFirstName , @GuarantorMiddleName = GuarantorMiddleName , @GuarantorLastName = GuarantorLastName , @GuarantorSuffix = GuarantorSuffix , @RelationshiptoGuarantor = RelationshiptoGuarantor , @GuarantorAddressLine1 = GuarantorAddressLine1 , @GuarantorAddressLine2 = GuarantorAddressLine2 , @GuarantorCity = GuarantorCity , @GuarantorState = GuarantorState , @GuarantorCountry = GuarantorCountry , @GuarantorZipCode = GuarantorZipCode , @DefaultRenderingProviderID = dbo.KAPI_fn_ConvertToINT(DefaultRenderingProviderID, 0, NULL) , @DefaultRenderingProviderExternalID = DefaultRenderingProviderExternalID , @DefaultRenderingProviderFullName = DefaultRenderingProviderFullName , @PrimaryCarePhysicianID = dbo.KAPI_fn_ConvertToINT(PrimaryCarePhysicianID, 0, NULL) , @PrimaryCarePhysicianExternalID = PrimaryCarePhysicianExternalID , @PrimaryCarePhysicianFullName = PrimaryCarePhysicianFullName , @ReferringProviderID = dbo.KAPI_fn_ConvertToINT(ReferringProviderID, 0, NULL) , @ReferringProviderExternalID = ReferringProviderExternalID , @ReferringProviderFullName = ReferringProviderFullName , @ReferralSource = ReferralSource , @EmergencyName = EmergencyName , @EmergencyPhone = EmergencyPhone , @EmergencyPhoneExt = EmergencyPhoneExt FROM #GeneralInfo ; --Try to match @ReferralSource DECLARE @PatientReferralSourceID INT ; SELECT @PatientReferralSourceID = PatientReferralSourceID FROM dbo.PatientReferralSource WITH ( NOLOCK ) WHERE PatientReferralSourceCaption = @ReferralSource ; DECLARE @DefinedMaritalStatus VARCHAR(1) ; IF @PatientMaritalStatus = 'U' AND @MaritalStatus IS NOT NULL AND LEN(@MaritalStatus) > 1 SELECT @DefinedMaritalStatus = MaritalStatus FROM dbo.MaritalStatus WITH ( NOLOCK ) WHERE LongName = @MaritalStatus ; SET @PatientMaritalStatus = COALESCE(@DefinedMaritalStatus, @PatientMaritalStatus) ; --If @EmployerID supplied, validate, if not but @EmployerName supplied, match or create SELECT @EmployerID = EmployerID FROM dbo.Employers WITH ( NOLOCK ) WHERE EmployerID = @EmployerID ; IF @@ROWCOUNT = 0 SET @EmployerID = NULL ; IF @EmployerID IS NULL AND @EmployerName IS NOT NULL BEGIN SELECT @EmployerID = EmployerID FROM dbo.Employers WHERE EmployerName = @EmployerName ; END IF @EmployerID IS NOT NULL BEGIN --Check if Update is necessary IF NOT EXISTS ( SELECT 1 FROM dbo.Employers WITH ( NOLOCK ) WHERE EmployerID = @EmployerID AND @EmployerName IS NOT NULL AND EmployerName = @EmployerName ) OR ( @EmployerAddressLine1 IS NOT NULL OR @EmployerAddressLine2 IS NOT NULL OR @EmployerCity IS NOT NULL OR @EmployerState IS NOT NULL OR @EmployerCountry IS NOT NULL OR @EmployerZipCode IS NOT NULL ) BEGIN --Fields that are not passed (Fields that are NULL) should not be modified SELECT @EmployerName = COALESCE(@EmployerName, EMployerName) , @EmployerAddressLine1 = COALESCE(@EmployerAddressLine1, AddressLine1) , @EmployerAddressLine2 = COALESCE(@EmployerAddressLine2, AddressLine2) , @EmployerCity = COALESCE(@EmployerCity, City) , @EmployerState = COALESCE(@EmployerState, State) , @EmployerCountry = COALESCE(@EmployerCountry, Country) , @EmployerZipCode = COALESCE(@EmployerZipCode, ZipCode) FROM dbo.Employers WITH ( ROWLOCK, UPDLOCK ) WHERE EmployerID = @EmployerID ; EXEC dbo.EmployerDataProvider_UpdateEmployer @EmployerID, @EmployerName, @EmployerAddressLine1, @EmployerAddressLine2, @EmployerCity, @EmployerState, @EmployerCountry, @EmployerZipCode ; END END --Add a New Employer IF @EmployerID IS NULL AND @EmployerName IS NOT NULL BEGIN EXEC @EmployerID= dbo.EmployerDataProvider_CreateEmployer @EmployerName, @EmployerAddressLine1, @EmployerAddressLine2, @EmployerCity, @EmployerState, @EmployerCountry, @EmployerZipCode ; END --If @DefaultRenderingProviderID supplied try to validate, or else do a full name match DECLARE @MatchedRenderingProviderIsReferring BIT ; SET @MatchedRenderingProviderIsReferring = 0 ; SELECT @DefaultRenderingProviderID = DoctorID , @MatchedRenderingProviderIsReferring = [External] FROM dbo.Doctor WITH ( NOLOCK ) WHERE DoctorID = @DefaultRenderingProviderID AND PracticeID = @PracticeID AND ActiveDoctor = 1 ; IF @@ROWCOUNT = 0 SET @DefaultRenderingProviderID = NULL ; IF @DefaultRenderingProviderID IS NULL SELECT @DefaultRenderingProviderID = DoctorID , @MatchedRenderingProviderIsReferring = [External] FROM dbo.Doctor D ( NOLOCK ) WHERE RTRIM(ISNULL(D.FirstName + ' ', '') + ISNULL(D.MiddleName + ' ', '')) + ISNULL(' ' + D.LastName, '') + ISNULL(', ' + dbo.fn_ZeroLengthStringToNull(D.Degree), '') LIKE '%' + @DefaultRenderingProviderFullName + '%' AND PracticeID = @PracticeID AND ActiveDoctor = 1 ; IF @DefaultRenderingProviderID IS NULL AND @DefaultRenderingProviderExternalID IS NOT NULL AND @ExternalVendorID IS NOT NULL BEGIN SELECT @DefaultRenderingProviderID = d.DoctorID , @MatchedRenderingProviderIsReferring = d.[External] FROM dbo.ExternalIDToDoctorMap etd ( NOLOCK ) INNER JOIN dbo.Doctor d ( NOLOCK ) ON etd.DoctorID = d.DoctorId WHERE etd.ExternalVendorID = @ExternalVendorID AND etd.ExternalID = @DefaultRenderingProviderExternalID AND d.PracticeID = @PracticeID AND d.ActiveDoctor = 1 ; END IF @MatchedRenderingProviderIsReferring = 1 BEGIN SET @updatePatientInfo = dbo.KareoAPI_ErrorMerge(@updatePatientInfo, 'PatientUpdate.RenderingProvider', 6600, 'Invalid rendering provider. Specified provider is a Referring Provider') ; -- Input Rendering provider invalid. Do not modify existing value. SET @DefaultRenderingProviderID = NULL ; GOTO ON_MANUAL_ERROR ; END --If @PrimaryCarePhysicianID supplied try to validate, or else do a full name match SELECT @PrimaryCarePhysicianID = DoctorID FROM dbo.Doctor WITH ( NOLOCK ) WHERE DoctorID = @PrimaryCarePhysicianID AND PracticeID = @PracticeID AND [External] = 0 AND ActiveDoctor = 1 ; IF @@ROWCOUNT = 0 SET @PrimaryCarePhysicianID = NULL ; IF @PrimaryCarePhysicianID IS NULL SELECT @PrimaryCarePhysicianID = DoctorID FROM dbo.Doctor D ( NOLOCK ) WHERE RTRIM(ISNULL(D.FirstName + ' ', '') + ISNULL(D.MiddleName + ' ', '')) + ISNULL(' ' + D.LastName, '') + ISNULL(', ' + dbo.fn_ZeroLengthStringToNull(D.Degree), '') LIKE '%' + @PrimaryCarePhysicianFullName + '%' AND PracticeID = @PracticeID AND [External] = 0 AND ActiveDoctor = 1 ; --Attempt an External ID match for provider IF @PrimaryCarePhysicianID IS NULL AND @PrimaryCarePhysicianExternalID IS NOT NULL AND @ExternalVendorID IS NOT NULL BEGIN SELECT @PrimaryCarePhysicianID = d.DoctorID FROM dbo.ExternalIDToDoctorMap etd ( NOLOCK ) INNER JOIN dbo.Doctor d ( NOLOCK ) ON etd.DoctorID = d.DoctorId WHERE etd.ExternalVendorID = @ExternalVendorID AND etd.ExternalID = @PrimaryCarePhysicianExternalID AND d.PracticeID = @PracticeID AND d.[External] = 0 AND d.ActiveDoctor = 1 ; END --If @ReferringProviderID supplied try to validate, or else do a full name match SELECT @ReferringProviderID = DoctorID FROM dbo.Doctor WITH ( NOLOCK ) WHERE DoctorID = @ReferringProviderID AND PracticeID = @PracticeID AND ActiveDoctor = 1 ; IF @@ROWCOUNT = 0 SET @ReferringProviderID = NULL IF @ReferringProviderID IS NULL SELECT @ReferringProviderID = DoctorID FROM dbo.Doctor D ( NOLOCK ) WHERE RTRIM(ISNULL(D.FirstName + ' ', '') + ISNULL(D.MiddleName + ' ', '')) + ISNULL(' ' + D.LastName, '') + ISNULL(', ' + dbo.fn_ZeroLengthStringToNull(D.Degree), '') LIKE '%' + @ReferringProviderFullName + '%' AND PracticeID = @PracticeID AND ActiveDoctor = 1 IF @ReferringProviderID IS NULL AND @ReferringProviderExternalID IS NOT NULL AND @ExternalVendorID IS NOT NULL BEGIN SELECT @ReferringProviderID = d.DoctorID FROM dbo.ExternalIDToDoctorMap etd ( NOLOCK ) INNER JOIN dbo.Doctor d ( NOLOCK ) ON etd.DoctorID = d.DoctorId WHERE etd.ExternalVendorID = @ExternalVendorID AND etd.ExternalID = @ReferringProviderExternalID AND d.PracticeID = @PracticeID AND d.ActiveDoctor = 1 END --Validate Default Service Location Data if passed DECLARE @LocationID INT , @LocationName VARCHAR(32) , @LocationAddressLine1 VARCHAR(128) , @LocationAddressLine2 VARCHAR(128) , @LocationCity VARCHAR(32) , @LocationState VARCHAR(2) , @LocationCountry VARCHAR(32) , @LocationZipCode VARCHAR(9) , @BillingName VARCHAR(100) , @LocationPhone VARCHAR(10) , @LocationPhoneExt VARCHAR(10) , @LocationFax VARCHAR(10) , @LocationFaxExt VARCHAR(10) , @NPI VARCHAR(10) , @FacilityIDType VARCHAR(50) , @FacilityID VARCHAR(50) , @CLIANumber VARCHAR(30) , @POS CHAR(2) , @GroupNumberTypeID INT , @TimeZoneID INT , @PayToName VARCHAR(25) , @PayToAddressLine1 VARCHAR(256) , @PayToAddressLine2 VARCHAR(256) , @PayToCity VARCHAR(128) , @PayToState VARCHAR(2) , @PayToCountry VARCHAR(32) , @PayToZipCode VARCHAR(9) , @PayToPhone VARCHAR(10) , @PayToPhoneExt VARCHAR(10) , @PayToFax VARCHAR(10) , @PayToFaxExt VARCHAR(10) , @EIN VARCHAR(9) , @BillTypeID INT SELECT @LocationID = dbo.KAPI_fn_ConvertToINT(LocationID, 0, NULL) , @LocationName = LocationName , @LocationAddressLine1 = AddressLine1 , @LocationAddressLine2 = AddressLine2 , @LocationCity = City , @LocationState = State , @LocationCountry = Country , @LocationZipCode = ZipCode , @BillingName = BillingName , @LocationPhone = Phone , @LocationPhoneExt = PhoneExt , @LocationFax = FaxPhone , @LocationFaxExt = FaxPhoneExt , @NPI = NPI , @FacilityIDType = FacilityIDType , @FacilityID = FacilityID , @CLIANumber = CLIANumber , @POS = POS FROM #Location IF @FacilityIDType <> '' AND @FacilityIDType IS NOT NULL BEGIN SELECT @GroupNumberTypeID = GroupNumberTypeID FROM dbo.GroupNumberType WITH ( NOLOCK ) WHERE ANSIReferenceIdentificationQualifier = @FacilityIDType IF @GroupNumberTypeID IS NULL SELECT @GroupNumberTypeID = GroupNumberTypeID FROM dbo.GroupNumberType WITH ( NOLOCK ) WHERE TypeName = @FacilityIDType END --If @LocationID supplied, validate, if not but @LocationName supplied, match or create SELECT @LocationID = ServiceLocationID FROM dbo.ServiceLocation WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND ServiceLocationID = @LocationID IF @@ROWCOUNT = 0 SET @LocationID = NULL IF @LocationID IS NULL AND @LocationName IS NOT NULL BEGIN SELECT @LocationID = ServiceLocationID FROM dbo.ServiceLocation WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND Name = @LocationName END IF @LocationID IS NOT NULL BEGIN --Check if an Update is necessary IF NOT EXISTS ( SELECT 1 FROM dbo.ServiceLocation WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND ServiceLocationID = @LocationID AND @LocationName IS NOT NULL AND Name = @LocationName ) OR @LocationAddressLine1 IS NOT NULL OR @LocationAddressLine2 IS NOT NULL OR @LocationCity IS NOT NULL OR @LocationState IS NOT NULL OR @LocationCountry IS NOT NULL OR @LocationZipCode IS NOT NULL OR @BillingName IS NOT NULL OR @LocationPhone IS NOT NULL OR @LocationPhoneExt IS NOT NULL OR @LocationFax IS NOT NULL OR @LocationFaxExt IS NOT NULL OR @NPI IS NOT NULL OR @FacilityID IS NOT NULL OR @CLIANumber IS NOT NULL OR @POS IS NOT NULL OR @GroupNumberTypeID IS NOT NULL BEGIN --Fields that are not passed (Fields that are NULL) should not be modified SELECT @LocationName = COALESCE(@LocationName, Name) , @BillingName = COALESCE(@BillingName, BillingName) , @LocationAddressLine1 = COALESCE(@LocationAddressLine1, AddressLine1) , @LocationAddressLine2 = COALESCE(@LocationAddressLine2, AddressLine2) , @LocationCity = COALESCE(@LocationCity, City) , @LocationState = COALESCE(@LocationState, State) , @LocationCountry = COALESCE(@LocationCountry, Country) , @LocationZipCode = COALESCE(@LocationZipCode, ZipCode) , @POS = COALESCE(@POS, PlaceOfServiceCode) , @LocationPhone = COALESCE(@LocationPhone, Phone) , @LocationPhoneExt = COALESCE(@LocationPhoneExt, PhoneExt) , @LocationFax = COALESCE(@LocationFax, FaxPhone) , @LocationFaxExt = COALESCE(@LocationFaxExt, FaxPhoneExt) , @NPI = COALESCE(@NPI, NPI) , @GroupNumberTypeID = COALESCE(@GroupNumberTypeID, FacilityIDType) , @FacilityID = COALESCE(@FacilityID, HCFABox32FacilityID) , @CLIANumber = COALESCE(@CLIANumber, CLIANumber) , @TimeZoneID = COALESCE(@TimeZoneID, TimeZoneID) , @PayToName = COALESCE(@PayToName, PayToName) , @PayToAddressLine1 = COALESCE(@PayToAddressLine1, PayToAddressLine1) , @PayToAddressLine2 = COALESCE(@PayToAddressLine2, PayToAddressLine2) , @PayToCity = COALESCE(@PayToCity, PayToCity) , @PayToState = COALESCE(@PayToState, PayToState) , @PayToCountry = COALESCE(@PayToCountry, PayToCountry) , @PayToZipCode = COALESCE(@PayToZipCode, PayToZipCode) , @PayToPhone = COALESCE(@PayToPhone, PayToPhone) , @PayToPhoneExt = COALESCE(@PayToPhoneExt, PayToPhoneExt) , @PayToFax = COALESCE(@PayToFax, PayToFax) , @PayToFaxExt = COALESCE(@PayToFaxExt, PayToFaxExt) , @EIN = COALESCE(@EIN, EIN) , @BillTypeID = COALESCE(@BillTypeID, BillTypeID) FROM dbo.ServiceLocation WITH ( ROWLOCK, UPDLOCK ) WHERE ServiceLocationID = @LocationID EXEC dbo.ServiceLocationDataProvider_UpdateServiceLocation @LocationID, @LocationName, @BillingName, @LocationAddressLine1, @LocationAddressLine2, @LocationCity, @LocationState, @LocationCountry, @LocationZipCode, @POS, @LocationPhone, @LocationPhoneExt, @LocationFax, @LocationFaxExt, @NPI, @GroupNumberTypeID, @FacilityID, @CLIANumber, @TimeZoneID, @PayToName, @PayToAddressLine1, @PayToAddressLine2, @PayToCity, @PayToState, @PayToCountry, @PayToZipCode, @PayToPhone, @PayToPhoneExt, @PayToFax, @PayToFaxExt, @EIN, @BillTypeID END END IF @LocationID IS NULL AND @LocationName IS NOT NULL BEGIN --Set Defaults for NON NULL Columns SELECT @POS = ISNULL(@POS, '11') , @GroupNumberTypeID = ISNULL(@GroupNumberTypeID, 28) EXEC @LocationID= dbo.ServiceLocationDataProvider_CreateServiceLocation @PracticeID, @LocationName, @BillingName, @LocationAddressLine1, @LocationAddressLine2, @LocationCity, @LocationState, @LocationCountry, @LocationZipCode, @POS, @LocationPhone, @LocationPhoneExt, @LocationFax, @LocationFaxExt, @NPI, @GroupNumberTypeID, @FacilityID, @CLIANumber, @TimeZoneID = 5, @PayToName = NULL, @PayToAddressLine1 = NULL, @PayToAddressLine2 = NULL, @PayToCity = NULL, @PayToState = NULL, @PayToCountry = NULL, @PayToZipCode = NULL, @PayToPhone = NULL, @PayToPhoneExt = NULL, @PayToFax = NULL, @PayToFaxExt = NULL, @EIN = NULL, @BillTypeID = NULL END --Try to validate PatientID SELECT @PatientID = PatientID FROM dbo.Patient WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientID = @PatientID IF @@ROWCOUNT = 0 SET @PatientID = NULL --Try to use the ExternalID if no PatientID identified yet and ExternalID is provided IF @PatientID IS NULL AND @PatientExternalID IS NOT NULL AND @ExternalVendorID IS NOT NULL BEGIN SELECT @PatientID = PatientID FROM dbo.ExternalIDToPatientMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND ExternalID = @PatientExternalID IF @PatientID IS NULL SET @updatePatientInfo = dbo.KareoAPI_ErrorMerge(@updatePatientInfo, 'PatientUpdate.PatientExternalID', 0, 'No Patient could be identified with the External ID passed.') END IF @PatientID IS NULL BEGIN IF @PatientFirstName IS NULL SET @updatePatientInfo = dbo.KareoAPI_ErrorMerge(@updatePatientInfo, 'PatientUpdate.FirstName', 0, 'Patient''s First Name must be specified if PatientID is not') IF @PatientLastName IS NULL SET @updatePatientInfo = dbo.KareoAPI_ErrorMerge(@updatePatientInfo, 'PatientUpdate.LastName', 0, 'Patient''s Last Name must be specified if PatientID is not') --This error is serious enough to kill further processing IF @PatientFirstName IS NULL OR @PatientLastName IS NULL GOTO ON_MANUAL_ERROR IF ( SELECT COUNT(PatientID) FROM dbo.Patient WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND FirstName = @PatientFirstName AND LastName = @PatientLastName ) = 1 SELECT @PatientID = PatientID FROM dbo.Patient WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND FirstName = @PatientFirstName AND LastName = @PatientLastName END IF @PatientID IS NULL BEGIN SET @updatePatientInfo = dbo.KareoAPI_ErrorMerge(@updatePatientInfo, 'PatientUpdate.PatientID', 0, 'No Patient could be identified') GOTO ON_MANUAL_ERROR END --Get relationship code DECLARE @GaurantorRelationShip VARCHAR(1) SELECT @GaurantorRelationShip = RelationShip FROM dbo.RelationShip WITH ( NOLOCK ) WHERE LongName = @RelationshiptoGuarantor IF @GaurantorRelationShip IS NULL SELECT @GaurantorRelationShip = RelationShip FROM dbo.RelationShip WITH ( NOLOCK ) WHERE RelationShip = @RelationshiptoGuarantor --Fields that are not passed (Fields that are NULL) should not be modified DECLARE @PatientEmailCorrespondence BIT IF @PatientDateOfBirth = '1/1/1900' BEGIN SET @PatientDateOfBirth = NULL END SELECT @PatientPrefix = COALESCE(@PatientPrefix, Prefix) , @PatientFirstName = COALESCE(@PatientFirstName, FirstName) , @PatientMiddleName = COALESCE(@PatientMiddleName, MiddleName) , @PatientLastName = COALESCE(@PatientLastName, LastName) , @PatientSuffix = COALESCE(@PatientSuffix, Suffix) , @PatientAddressLine1 = COALESCE(@PatientAddressLine1, AddressLine1) , @PatientAddressLine2 = COALESCE(@PatientAddressLine2, AddressLine2) , @PatientCity = COALESCE(@PatientCity, City) , @PatientState = COALESCE(@PatientState, State) , @PatientCountry = COALESCE(@PatientCountry, Country) , @PatientZipCode = COALESCE(@PatientZipCode, ZipCode) , @PatientGender = CASE WHEN @PatientGender IS NULL THEN Gender ELSE COALESCE(@PatientGender, Gender) END , @PatientMaritalStatus = COALESCE(@PatientMaritalStatus, MaritalStatus) , @PatientHomePhone = COALESCE(@PatientHomePhone, HomePhone) , @PatientHomePhoneExt = COALESCE(@PatientHomePhoneExt, HomePhoneExt) , @PatientWorkPhone = COALESCE(@PatientWorkPhone, WorkPhone) , @PatientWorkPhoneExt = COALESCE(@PatientWorkPhoneExt, WorkPhoneExt) , @PatientMobilePhone = COALESCE(@PatientMobilePhone, MobilePhone) , @PatientMobilePhoneExt = COALESCE(@PatientMobilePhoneExt, MobilePhoneExt) , @PatientDateOfBirth = COALESCE(@PatientDateOfBirth, DOB) , @PatientSSN = COALESCE(@PatientSSN, SSN) , @PatientEmail = COALESCE(@PatientEmail, EmailAddress) , @PatientEmailCorrespondence = COALESCE(@PatientEmailCorrespondence, SendEmailCorrespondence) , @GuarantorDifferentThanPatient = COALESCE(@GuarantorDifferentThanPatient, ResponsibleDifferentThanPatient) , @EmploymentStatus = COALESCE(@EmploymentStatus, EmploymentStatus) , @PatientMedicalRecordNumber = COALESCE(@PatientMedicalRecordNumber, MedicalRecordNumber) , @EmployerID = COALESCE(@EmployerID, EmployerID) , @GuarantorPrefix = COALESCE(@GuarantorPrefix, ResponsiblePrefix) , @GuarantorFirstName = COALESCE(@GuarantorFirstName, ResponsibleFirstName) , @GuarantorMiddleName = COALESCE(@GuarantorMiddleName, ResponsibleMiddleName) , @GuarantorLastName = COALESCE(@GuarantorLastName, ResponsibleLastName) , @GuarantorSuffix = COALESCE(@GuarantorSuffix, ResponsibleSuffix) , @GaurantorRelationShip = COALESCE(@GaurantorRelationShip, ResponsibleRelationshipToPatient) , @GuarantorAddressLine1 = COALESCE(@GuarantorAddressLine1, ResponsibleAddressLine1) , @GuarantorAddressLine2 = COALESCE(@GuarantorAddressLine2, ResponsibleAddressLine2) , @GuarantorCity = COALESCE(@GuarantorCity, ResponsibleCity) , @GuarantorState = COALESCE(@GuarantorState, ResponsibleState) , @GuarantorCountry = COALESCE(@GuarantorCountry, ResponsibleCountry) , @GuarantorZipCode = COALESCE(@GuarantorZipCode, ResponsibleZipCode) , @DefaultRenderingProviderID = COALESCE(@DefaultRenderingProviderID, PrimaryProviderID) , @PrimaryCarePhysicianID = COALESCE(@PrimaryCarePhysicianID, PrimaryCarePhysicianID) , @ReferringProviderID = COALESCE(@ReferringProviderID, ReferringPhysicianID) , @PatientReferralSourceID = COALESCE(@PatientReferralSourceID, PatientReferralSourceID) , @LocationID = COALESCE(@LocationID, DefaultServiceLocationID) , @Active = COALESCE(@Active, Active) FROM dbo.Patient WITH ( ROWLOCK, UPDLOCK ) WHERE PatientID = @PatientID --Update Patient EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix, @PatientFirstName, @PatientMiddleName, @PatientLastName, @PatientSuffix, @PatientAddressLine1, @PatientAddressLine2, @PatientCity, @PatientState, @PatientCountry, @PatientZipCode, @PatientGender, @PatientMaritalStatus, @PatientHomePhone, @PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt, @PatientDateOfBirth, @PatientSSN, @PatientEmail, @PatientEmailCorrespondence, @GuarantorDifferentThanPatient, @EmploymentStatus, @ReferringProviderID, @DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID, @EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone, @PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userId, @active, 0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt IF @ExternalVendorID IS NOT NULL AND @PatientExternalID IS NOT NULL AND @PatientExternalID <> '' AND NOT EXISTS ( SELECT 1 FROM dbo.ExternalIDToPatientMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND ExternalID = @PatientExternalID AND PatientID = @PatientID ) BEGIN UPDATE epm SET ExternalID = @PatientExternalID FROM dbo.ExternalIDToPatientMap epm ( ROWLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND PatientID = @PatientID IF @@ROWCOUNT = 0 INSERT INTO dbo.ExternalIDToPatientMap ( ExternalVendorID , ExternalID , PatientID ) VALUES ( @ExternalVendorID , @PatientExternalID , @PatientID ) END --Update Guarantor EXEC dbo.PatientDataProvider_UpdatePatientGuarantor @PatientID, @GuarantorPrefix, @GuarantorFirstName, @GuarantorMiddleName, @GuarantorLastName, @GuarantorSuffix, @GaurantorRelationShip, @GuarantorAddressLine1, @GuarantorAddressLine2, @GuarantorCity, @GuarantorState, @GuarantorCountry, @GuarantorZipCode, @userId --Insert Patient Journal Note if any IF @PatientID IS NOT NULL AND @PatientNote IS NOT NULL AND @PatientNote <> '' BEGIN EXEC PatientDataProvider_CreatePatientJournalNote @Hidden = 0, @SoftwareApplicationID = 'K', @CreatedUserID = @userId, @PatientID = @PatientID, @NoteMessage = @PatientNote, @UserName = 'Kareo API' END DECLARE @CaseCount INT DECLARE @CaseStep INT DECLARE @PolicyCount INT DECLARE @PolicyStep INT DECLARE @AuthoCount INT DECLARE @AuthoStep INT SELECT @PolicyCount = 0 , @PolicyStep = 0 , @AuthoCount = 0 , @AuthoStep = 0 SELECT @CaseCount = COUNT(1) FROM #Cases SELECT @CaseStep = 0 --Patient Case Data Work Variables DECLARE @CaseNodeID INT , @CaseID INT , @CaseExternalID VARCHAR(25) , @CaseName VARCHAR(128) , @CaseDescription VARCHAR(MAX) , @PatientCaseReferringProviderID INT , @PatientCaseReferringProviderExternalID VARCHAR(25) , @PatientCaseReferringProviderFullName VARCHAR(256) , @SendPatientStatements BIT , @PayerScenario VARCHAR(128) , @RelatedToAutoAccident BIT , @RelatedToAutoAccidentState CHAR(2) , @RelatedToEmployment BIT , @RelatedToPregnancy BIT , @RelatedToAbuse BIT , @RelatedToOther BIT , @RelatedToEPSDT BIT , @EPSDTReason VARCHAR(256) , @RelatedToFamilyPlanning BIT , @RelatedToEmergency BIT , --Patient Case Date Details @InjuryStartDate DATETIME , @InjuryEndDate DATETIME , @SameorSimilarIllnessStartDate DATETIME , @SameorSimilarIllnessEndDate DATETIME , @UnabletoWorkStartDate DATETIME , @UnabletoWorkEndDate DATETIME , @RelatedDisabilityStartDate DATETIME , @RelatedDisabilityEndDate DATETIME , @RelatedHospitalizationStartDate DATETIME , @RelatedHospitalizationEndDate DATETIME , @LastMenstrualPeriodDate DATETIME , @LastSeenDate DATETIME , @ReferralDate DATETIME , @AcuteManifestationDate DATETIME , @LastXRayDate DATETIME , @AccidentDate DATETIME , @ActiveCase BIT --Insurance Policy Data Work Variables DECLARE @InsurancePolicyID INT , @InsurancePolicyExternalID VARCHAR(25) , @CompanyID INT , @CompanyName VARCHAR(128) , @PlanID INT , @PlanName VARCHAR(128) , @InsPlanAddressLine1 VARCHAR(256) , @InsPlanAddressLine2 VARCHAR(256) , @InsPlanCity VARCHAR(32) , @InsPlanState VARCHAR(2) , @InsPlanCountry VARCHAR(32) , @InsPlanZipCode VARCHAR(10) , @AdjusterPrefix VARCHAR(16) , @AdjusterFirstName VARCHAR(64) , @AdjusterMiddleName VARCHAR(64) , @AdjusterLastName VARCHAR(64) , @AdjusterSuffix VARCHAR(16) , @AdjusterPhoneNumber VARCHAR(10) , @AdjusterPhoneNumberExt VARCHAR(10) , @AdjusterFaxNumber VARCHAR(10) , @AdjusterFaxNumberExt VARCHAR(10) , @PolicyNumber VARCHAR(32) , @PolicyGroupNumber VARCHAR(32) , @Copay MONEY , @Deductible MONEY , @EffectiveStartDate DATETIME , @EffectiveEndDate DATETIME , @InsuredPatientRelationshipToInsured VARCHAR(16) , @InsuredPrefix VARCHAR(16) , @InsuredFirstName VARCHAR(32) , @InsuredMiddleName VARCHAR(32) , @InsuredLastName VARCHAR(32) , @InsuredSuffix VARCHAR(16) , @InsuredAddressLine1 VARCHAR(256) , @InsuredAddressLine2 VARCHAR(256) , @InsuredCity VARCHAR(32) , @InsuredState VARCHAR(2) , @InsuredCountry VARCHAR(32) , @InsuredZipCode VARCHAR(10) , @InsuredIDNumber VARCHAR(32) , @InsuredSocialSecurityNumber CHAR(11) , @InsuredDateofBirth DATETIME , @InsuredGender CHAR(1) , @InsuredPolicyThroughEmployer BIT , @InsuredEmployer VARCHAR(128) , @PolicyNotes VARCHAR(MAX) , @CardOnFile BIT , @InsuredPhone VARCHAR(10) , @InsuredPhoneExt VARCHAR(10) , @InsuranceProgramTypeID INT , @ActiveInsurance BIT , @Precedence INT , @MaxPrecedence INT , @CurrentPrecedence INT , @GroupName VARCHAR(14) , @ReleaseOfInformation VARCHAR(1) --Authorization Data Work Variables DECLARE @AuthorizationID INT , @AuthoInsurancePolicyID INT , @AuthoInsurancePlanID INT , @AuthoInsurancePlanName VARCHAR(128) , @AuthoNumber VARCHAR(65) , @AuthoNumberOfVisits INT , @AuthoContactFullName VARCHAR(65) , @AuthoContactPhone VARCHAR(10) , @AuthoContactPhoneExt VARCHAR(10) , @AuthoNotes VARCHAR(MAX) , @AuthoStartDate DATETIME , @AuthoEndDate DATETIME DECLARE @InsRelationShip VARCHAR(1) WHILE @CaseStep < @CaseCount BEGIN SET @CaseStep = @CaseStep + 1 SELECT @CaseNodeID = nodeID , @CaseID = dbo.KAPI_fn_ConvertToINT(CaseID, 0, NULL) , @CaseExternalID = ExternalID , @CaseName = CaseName , @CaseDescription = CaseDescription , @PatientCaseReferringProviderID = dbo.KAPI_fn_ConvertToINT(ReferringProviderID, 0, NULL) , @PatientCaseReferringProviderExternalID = ReferringProviderExternalID , @PatientCaseReferringProviderFullName = ReferringProviderFullName , @SendPatientStatements = dbo.KAPI_fn_ConvertToBIT(SendPatientStatements) , @PayerScenario = dbo.fn_ZeroLengthStringToNull(PayerScenario) , @RelatedToAutoAccident = dbo.KAPI_fn_ConvertToBIT(RelatedToAutoAccident) , @RelatedToAutoAccidentState = RelatedToAutoAccidentState , @RelatedToEmployment = dbo.KAPI_fn_ConvertToBIT(RelatedToEmployment) , @RelatedToPregnancy = dbo.KAPI_fn_ConvertToBIT(RelatedToPregnancy) , @RelatedToAbuse = dbo.KAPI_fn_ConvertToBIT(RelatedToAbuse) , @RelatedToOther = dbo.KAPI_fn_ConvertToBIT(RelatedToOther) , @RelatedToEPSDT = dbo.KAPI_fn_ConvertToBIT(RelatedToEPSDT) , @EPSDTReason = EPSDTReason , @RelatedToFamilyPlanning = dbo.KAPI_fn_ConvertToBIT(RelatedToFamilyPlanning) , @RelatedToEmergency = dbo.KAPI_fn_ConvertToBIT(RelatedToEmergency) , --Patient Case Dates Details @InjuryStartDate = dbo.KAPI_fn_ConvertToDateTime(InjuryStartDate, 0, NULL) , @InjuryEndDate = dbo.KAPI_fn_ConvertToDateTime(InjuryEndDate, 0, NULL) , @SameorSimilarIllnessStartDate = dbo.KAPI_fn_ConvertToDateTime(SameorSimilarIllnessStartDate, 0, NULL) , @SameorSimilarIllnessEndDate = dbo.KAPI_fn_ConvertToDateTime(SameorSimilarIllnessEndDate, 0, NULL) , @UnabletoWorkStartDate = dbo.KAPI_fn_ConvertToDateTime(UnabletoWorkStartDate, 0, NULL) , @UnabletoWorkEndDate = dbo.KAPI_fn_ConvertToDateTime(UnabletoWorkEndDate, 0, NULL) , @RelatedDisabilityStartDate = dbo.KAPI_fn_ConvertToDateTime(RelatedDisabilityStartDate, 0, NULL) , @RelatedDisabilityEndDate = dbo.KAPI_fn_ConvertToDateTime(RelatedDisabilityEndDate, 0, NULL) , @RelatedHospitalizationStartDate = dbo.KAPI_fn_ConvertToDateTime(RelatedHospitalizationStartDate, 0, NULL) , @RelatedHospitalizationEndDate = dbo.KAPI_fn_ConvertToDateTime(RelatedHospitalizationEndDate, 0, NULL) , @LastMenstrualPeriodDate = dbo.KAPI_fn_ConvertToDateTime(LastMenstrualPeriodDate, 0, NULL) , @LastSeenDate = dbo.KAPI_fn_ConvertToDateTime(LastSeenDate, 0, NULL) , @ReferralDate = dbo.KAPI_fn_ConvertToDateTime(ReferralDate, 0, NULL) , @AcuteManifestationDate = dbo.KAPI_fn_ConvertToDateTime(AcuteManifestationDate, 0, NULL) , @LastXRayDate = dbo.KAPI_fn_ConvertToDateTime(LastXRayDate, 0, NULL) , @AccidentDate = dbo.KAPI_fn_ConvertToDateTime(AccidentDate, 0, NULL) , @ActiveCase = dbo.KAPI_fn_ConvertToBIT(Active) FROM #Cases WHERE TID = @CaseStep --Try To Resolve @PayerScenario to @PayerScenarioID DECLARE @PayerScenarioID INT SELECT @PayerScenarioID = PayerScenarioID FROM dbo.PayerScenario WITH ( NOLOCK ) WHERE Name = @PayerScenario --Check if 5 - Commercial exists IF @PayerScenarioID IS NULL BEGIN SELECT @PayerScenarioID = PayerScenarioID FROM PayerScenario WHERE PayerScenarioID = 5 END --Randomly pick up the first payer scenario if 5 doesn't exist (better than crashing like it does today) IF @PayerScenarioID IS NULL BEGIN SELECT @PayerScenarioID = MIN(PayerScenarioID) FROM PayerScenario END --If @PatientCaseReferringProviderID supplied try to validate, or else do a full name match SELECT @PatientCaseReferringProviderID = DoctorID FROM dbo.Doctor WITH ( NOLOCK ) WHERE DoctorID = @PatientCaseReferringProviderID AND PracticeID = @PracticeID AND ActiveDoctor = 1 IF @@ROWCOUNT = 0 SET @PatientCaseReferringProviderID = NULL IF @PatientCaseReferringProviderID IS NULL SELECT @PatientCaseReferringProviderID = DoctorID FROM dbo.Doctor D ( NOLOCK ) WHERE RTRIM(ISNULL(D.FirstName + ' ', '') + ISNULL(D.MiddleName + ' ', '')) + ISNULL(' ' + D.LastName, '') + ISNULL(', ' + dbo.fn_ZeroLengthStringToNull(D.Degree), '') LIKE '%' + @PatientCaseReferringProviderFullName + '%' AND PracticeID = @PracticeID AND ActiveDoctor = 1 IF @PatientCaseReferringProviderID IS NULL AND @PatientCaseReferringProviderExternalID IS NOT NULL AND @ExternalVendorID IS NOT NULL BEGIN SELECT @PatientCaseReferringProviderID = d.DoctorID FROM dbo.ExternalIDToDoctorMap etd ( NOLOCK ) INNER JOIN dbo.Doctor d ( NOLOCK ) ON etd.DoctorID = d.DoctorId WHERE etd.ExternalVendorID = @ExternalVendorID AND etd.ExternalID = @PatientCaseReferringProviderExternalID AND d.PracticeID = @PracticeID AND d.ActiveDoctor = 1 END DECLARE @EPSDTCodeID INT SELECT @EPSDTCodeID = EPSDTCodeID FROM dbo.EPSDTCode WITH ( NOLOCK ) WHERE Code = @EPSDTReason IF @EPSDTCodeID IS NULL SELECT @EPSDTCodeID = EPSDTCodeID FROM dbo.EPSDTCode WITH ( NOLOCK ) WHERE [Description] = @EPSDTReason --Update Specific Logic --Requires a match with an existing Case IF @CaseID IS NOT NULL OR @CaseName IS NOT NULL BEGIN --Validate passed Case ID SELECT @CaseID = PatientCaseID FROM dbo.PatientCase WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientID = @PatientID AND PatientCaseID = @CaseID --If CaseID did not match, try matching by passed Case Name IF @@ROWCOUNT = 0 BEGIN SET @CaseID = NULL SELECT @CaseID = PatientCaseID FROM dbo.PatientCase WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientID = @PatientID AND Name = @CaseName END --If a Case has still not been identified and an External ID is passed attempt match IF @CaseID IS NULL AND @CaseExternalID IS NOT NULL AND @ExternalVendorID IS NOT NULL BEGIN SELECT @CaseID = PatientCaseID FROM dbo.ExternalIDToPatientCaseMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND ExternalID = @CaseExternalID END IF @CaseID IS NOT NULL BEGIN DECLARE @ShowExpiredInsurancePolicies BIT SELECT @CaseName = COALESCE(@CaseName, Name) , @CaseDescription = COALESCE(@CaseDescription, Notes) , @PatientCaseReferringProviderID = COALESCE(@PatientCaseReferringProviderID, ReferringPhysicianID) , @SendPatientStatements = COALESCE(@SendPatientStatements, StatementActive) , @PayerScenarioID = COALESCE(@PayerScenarioID, PayerScenarioID) , @RelatedToAutoAccident = COALESCE(@RelatedToAutoAccident, AutoAccidentRelatedFlag) , @RelatedToAutoAccidentState = COALESCE(@RelatedToAutoAccidentState, AutoAccidentRelatedState) , @RelatedToEmployment = COALESCE(@RelatedToEmployment, EmploymentRelatedFlag) , @RelatedToPregnancy = COALESCE(@RelatedToPregnancy, PregnancyRelatedFlag) , @RelatedToAbuse = COALESCE(@RelatedToAbuse, AbuseRelatedFlag) , @RelatedToOther = COALESCE(@RelatedToOther, OtherAccidentRelatedFlag) , @RelatedToEPSDT = COALESCE(@RelatedToEPSDT, EPSDT) , @EPSDTCodeID = COALESCE(@EPSDTCodeID, EPSDTCodeID) , @RelatedToFamilyPlanning = COALESCE(@RelatedToFamilyPlanning, FamilyPlanning) , @RelatedToEmergency = COALESCE(@RelatedToEmergency, EmergencyRelated) , @ActiveCase = COALESCE(@ActiveCase, Active) , @ShowExpiredInsurancePolicies = ShowExpiredInsurancePolicies FROM dbo.PatientCase WITH ( ROWLOCK, UPDLOCK ) WHERE PatientCaseID = @CaseID EXEC dbo.PatientDataProvider_UpdatePatientCase @CaseID, @PracticeID, @PatientID, @CaseName, @CaseDescription, @PatientCaseReferringProviderID, @ActiveCase, @PayerScenarioID, @RelatedToAutoAccident, @RelatedToAutoAccidentState, @RelatedToAbuse, @RelatedToEmployment, @RelatedToOther, @RelatedToPregnancy, @RelatedToEPSDT, @EPSDTCodeID, @RelatedToFamilyPlanning, @ShowExpiredInsurancePolicies, NULL, NULL, @SendPatientStatements, @userId, @RelatedToEmergency --Insert or Update a Case's ExternalID IF @ExternalVendorID IS NOT NULL AND @CaseExternalID IS NOT NULL AND @CaseExternalID <> '' AND NOT EXISTS ( SELECT 1 FROM dbo.ExternalIDToPatientCaseMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND ExternalID = @CaseExternalID AND PatientCaseID = @CaseID ) BEGIN UPDATE epcm SET ExternalID = @CaseExternalID FROM dbo.ExternalIDToPatientCaseMap epcm ( ROWLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND PatientCaseID = @CaseID IF @@ROWCOUNT = 0 INSERT INTO dbo.ExternalIDToPatientCaseMap ( ExternalVendorID , ExternalID , PatientCaseID ) VALUES ( @ExternalVendorID , @CaseExternalID , @CaseID ) END INSERT INTO #CasesAffected ( CaseNodeID , PatientID , PatientCaseID , ExternalID ) VALUES ( @CaseNodeID , @PatientID , @CaseID , @CaseExternalID ) DECLARE @PatientCaseDateID INT --Insert Patient Case Date records IF @InjuryStartDate IS NOT NULL OR @InjuryEndDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 2 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @InjuryStartDate = COALESCE(@InjuryStartDate, StartDate) , @InjuryEndDate = COALESCE(@InjuryEndDate, EndDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 2, @InjuryStartDate, @InjuryEndDate, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 2, @InjuryStartDate, @InjuryEndDate, @userId, @userId END IF @SameorSimilarIllnessStartDate IS NOT NULL OR @SameorSimilarIllnessEndDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 3 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @SameorSimilarIllnessStartDate = COALESCE(@SameorSimilarIllnessStartDate, StartDate) , @SameorSimilarIllnessEndDate = COALESCE(@SameorSimilarIllnessEndDate, EndDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 3, @SameorSimilarIllnessStartDate, @SameorSimilarIllnessEndDate, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 3, @SameorSimilarIllnessStartDate, @SameorSimilarIllnessEndDate, @userId, @userId END IF @UnabletoWorkStartDate IS NOT NULL OR @UnabletoWorkEndDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 4 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @UnabletoWorkStartDate = COALESCE(@UnabletoWorkStartDate, StartDate) , @UnabletoWorkEndDate = COALESCE(@UnabletoWorkEndDate, EndDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 4, @UnabletoWorkStartDate, @UnabletoWorkEndDate, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 4, @UnabletoWorkStartDate, @UnabletoWorkEndDate, @userId, @userId END IF @RelatedDisabilityStartDate IS NOT NULL OR @RelatedDisabilityEndDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 5 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @RelatedDisabilityStartDate = COALESCE(@RelatedDisabilityStartDate, StartDate) , @RelatedDisabilityEndDate = COALESCE(@RelatedDisabilityEndDate, EndDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 5, @RelatedDisabilityStartDate, @RelatedDisabilityEndDate, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 5, @RelatedDisabilityStartDate, @RelatedDisabilityEndDate, @userId, @userId END IF @RelatedHospitalizationStartDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 6 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @RelatedHospitalizationStartDate = COALESCE(@RelatedHospitalizationStartDate, StartDate) , @RelatedHospitalizationEndDate = COALESCE(@RelatedHospitalizationEndDate, EndDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 6, @RelatedHospitalizationStartDate, @RelatedHospitalizationEndDate, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 6, @RelatedHospitalizationStartDate, @RelatedHospitalizationEndDate, @userId, @userId END IF @LastMenstrualPeriodDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 7 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @LastMenstrualPeriodDate = COALESCE(@LastMenstrualPeriodDate, StartDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 7, @LastMenstrualPeriodDate, NULL, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 7, @LastMenstrualPeriodDate, NULL, @userId, @userId END IF @LastSeenDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 8 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @LastSeenDate = COALESCE(@LastSeenDate, StartDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 8, @LastSeenDate, NULL, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 8, @LastSeenDate, NULL, @userId, @userId END IF @ReferralDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 9 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @ReferralDate = COALESCE(@ReferralDate, StartDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 9, @ReferralDate, NULL, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 9, @ReferralDate, NULL, @userId, @userId END IF @AcuteManifestationDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 10 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @AcuteManifestationDate = COALESCE(@AcuteManifestationDate, StartDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 10, @AcuteManifestationDate, NULL, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 10, @AcuteManifestationDate, NULL, @userId, @userId END IF @LastXRayDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 11 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @LastXRayDate = COALESCE(@LastXRayDate, StartDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 11, @LastXRayDate, NULL, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 11, @LastXRayDate, NULL, @userId, @userId END IF @AccidentDate IS NOT NULL BEGIN SELECT @PatientCaseDateID = MAX(PatientCaseDateID) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND PatientCaseID = @CaseID AND PatientCaseDateTypeID = 12 IF @PatientCaseDateID IS NOT NULL BEGIN SELECT @AccidentDate = COALESCE(@AccidentDate, StartDate) FROM dbo.PatientCaseDate WITH ( NOLOCK ) WHERE PatientCaseDateID = @PatientCaseDateID EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID, @PracticeID, @CaseID, 12, @AccidentDate, NULL, @userId SET @PatientCaseDateID = NULL END ELSE EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 12, @AccidentDate, NULL, @userId, @userId END END END --Create Default or specified patient case IF @PatientID IS NOT NULL AND @CaseID IS NULL BEGIN --Set Defaults SELECT @RelatedToAutoAccident = ISNULL(@RelatedToAutoAccident, 0) , @RelatedToAbuse = ISNULL(@RelatedToAbuse, 0) , @RelatedToEmployment = ISNULL(@RelatedToEmployment, 0) , @RelatedToOther = ISNULL(@RelatedToOther, 0) , @RelatedToPregnancy = ISNULL(@RelatedToPregnancy, 0) , @RelatedToEPSDT = ISNULL(@RelatedToEPSDT, 0) , @RelatedToFamilyPlanning = ISNULL(@RelatedToFamilyPlanning, 0) , @RelatedToEmergency = ISNULL(@RelatedToEmergency, 0) , @PayerScenarioID = ISNULL(@PayerScenarioID, 5) , @CaseName = COALESCE(@CaseName, CASE WHEN @CaseStep = 1 THEN 'Default Case' WHEN @CaseStep > 1 THEN 'Default Case ' + CAST(@CaseStep AS VARCHAR) END) SET @ActiveCase = ISNULL(@ActiveCase, 1) EXEC @CaseID= dbo.PatientDataProvider_CreatePatientCase @PatientID, @PracticeID, @CaseName, @CaseDescription, @PatientCaseReferringProviderID, @ActiveCase, @PayerScenarioID, @RelatedToAutoAccident, @RelatedToAutoAccidentState, @RelatedToAbuse, @RelatedToEmployment, @RelatedToOther, @RelatedToPregnancy, @RelatedToEPSDT, @EPSDTCodeID, @RelatedToFamilyPlanning, 1, NULL, NULL, 1, @userID, @RelatedToEmergency, @CaseExternalID, @ExternalVendorID INSERT INTO #CasesAffected ( CaseNodeID , PatientID , PatientCaseID , ExternalID ) VALUES ( @CaseNodeID , @PatientID , @CaseID , @CaseExternalID ) --Insert Patient Case Date records IF @InjuryStartDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 2, @InjuryStartDate, @InjuryEndDate, @userId, @userId IF @SameorSimilarIllnessStartDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 3, @SameorSimilarIllnessStartDate, @SameorSimilarIllnessEndDate, @userId, @userId IF @UnabletoWorkStartDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 4, @UnabletoWorkStartDate, @UnabletoWorkEndDate, @userId, @userId IF @RelatedDisabilityStartDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 5, @RelatedDisabilityStartDate, @RelatedDisabilityEndDate, @userId, @userId IF @RelatedHospitalizationStartDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 6, @RelatedHospitalizationStartDate, @RelatedHospitalizationEndDate, @userId, @userId IF @LastMenstrualPeriodDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 7, @LastMenstrualPeriodDate, NULL, @userId, @userId IF @LastSeenDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 8, @LastSeenDate, NULL, @userId, @userId IF @ReferralDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 9, @ReferralDate, NULL, @userId, @userId IF @AcuteManifestationDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 10, @AcuteManifestationDate, NULL, @userId, @userId IF @LastXRayDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 11, @LastXRayDate, NULL, @userId, @userId IF @AccidentDate IS NOT NULL EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID, @CaseID, 12, @AccidentDate, NULL, @userId, @userId END IF NOT EXISTS ( SELECT 1 FROM #CasesAffected WHERE CaseNodeID = @CaseNodeID ) INSERT INTO #CasesAffected ( CaseNodeID , PatientID , ExternalID ) VALUES ( @CaseNodeID , @PatientID , @CaseExternalID ) SELECT @PolicyCount = MAX(I.TID) FROM #InsuranceCaseMap ICM INNER JOIN #Insurance I ON ICM.InsurancesNodeID = I.parentNodeID WHERE ICM.CaseNodeID = @CaseNodeID IF @PatientID IS NOT NULL AND @CaseID IS NOT NULL -- Store the existing policies in case we need to deactivate them if policies are only inserted INSERT #ExistingPolicies ( InsurancePolicyID ) SELECT InsurancePolicyID FROM InsurancePolicy WITH ( NOLOCK ) WHERE PatientCaseID = @CaseID DECLARE @PolicyInserted BIT DECLARE @PolicyUpdated BIT SET @PolicyInserted = 0 SET @PolicyUpdated = 0 WHILE @PolicyStep < @PolicyCount AND @CaseID IS NOT NULL BEGIN SET @PolicyStep = @PolicyStep + 1 SELECT @InsurancePolicyID = dbo.KAPI_fn_ConvertToINT(InsurancePolicyID, 0, NULL) , @InsurancePolicyExternalID = ExternalID , @CompanyID = dbo.KAPI_fn_ConvertToINT(CompanyID, 0, NULL) , @CompanyName = CompanyName , @PlanID = dbo.KAPI_fn_ConvertToINT(PlanID, 0, NULL) , @PlanName = PlanName , @InsPlanAddressLine1 = AddressLine1 , @InsPlanAddressLine2 = AddressLine2 , @InsPlanCity = City , @InsPlanState = State , @InsPlanCountry = Country , @InsPlanZipCode = ZipCode , @AdjusterPrefix = AdjusterPrefix , @AdjusterFirstName = AdjusterFirstName , @AdjusterMiddleName = AdjusterMiddleName , @AdjusterLastName = AdjusterLastName , @AdjusterSuffix = AdjusterSuffix , @AdjusterPhoneNumber = AdjusterPhoneNumber , @AdjusterPhoneNumberExt = AdjusterPhoneNumberExt , @AdjusterFaxNumber = AdjusterFaxNumber , @AdjusterFaxNumberExt = AdjusterFaxNumberExt , @PolicyNumber = PolicyNumber , @PolicyGroupNumber = PolicyGroupNumber , @Copay = dbo.KAPI_fn_ConvertToMoney(Copay, 1, 0) , @Deductible = dbo.KAPI_fn_ConvertToMoney(Deductible, 1, 0) , @EffectiveStartDate = dbo.KAPI_fn_ConvertToDateTime(EffectiveStartDate, 0, NULL) , @EffectiveEndDate = dbo.KAPI_fn_ConvertToDateTime(EffectiveEndDate, 0, NULL) , @InsuredPatientRelationshipToInsured = InsuredPatientRelationshipToInsured , @InsuredPrefix = InsuredPrefix , @InsuredFirstName = InsuredFirstName , @InsuredMiddleName = InsuredMiddleName , @InsuredLastName = InsuredLastName , @InsuredSuffix = InsuredSuffix , @InsuredAddressLine1 = InsuredAddressLine1 , @InsuredAddressLine2 = InsuredAddressLine2 , @InsuredCity = InsuredCity , @InsuredState = InsuredState , @InsuredCountry = InsuredCountry , @InsuredZipCode = InsuredZipCode , @InsuredIDNumber = InsuredIDNumber , @InsuredSocialSecurityNumber = InsuredSocialSecurityNumber , @InsuredDateofBirth = dbo.KAPI_fn_ConvertToDateTime(InsuredDateofBirth, 0, NULL) , @InsuredGender = InsuredGender , @InsuredPolicyThroughEmployer = COALESCE(dbo.KAPI_fn_ConvertToBIT(InsuredPolicyThroughEmployer), 0) , @InsuredEmployer = InsuredEmployer , @PolicyNotes = PolicyNotes , @ActiveInsurance = dbo.KAPI_fn_ConvertToBIT(Active) , @Precedence = dbo.KAPI_fn_ConvertToINT(Precedence, 0, NULL) FROM #Insurance WHERE TID = @PolicyStep --Validate Insurance Company ID, or try to match exact company name or Create new record SELECT @CompanyID = InsuranceCompanyID FROM dbo.InsuranceCompany WITH ( NOLOCK ) WHERE InsuranceCompanyID = @CompanyID IF @@ROWCOUNT = 0 SET @CompanyID = NULL IF @CompanyID IS NULL BEGIN SELECT TOP 1 @CompanyID = InsuranceCompanyID FROM dbo.InsuranceCompany IC ( NOLOCK ) WHERE InsuranceCompanyName = @CompanyName AND ( -- "All practices" scoped companies and companies created for that practice IC.ReviewCode = 'R' OR COALESCE(IC.CreatedPracticeID, 0) = @PracticeID ) ORDER BY CASE WHEN COALESCE(IC.CreatedPracticeID, 0) = @PracticeID THEN 1 WHEN IC.ReviewCode = 'R' THEN 2 END IF @CompanyID IS NULL AND @CompanyName IS NOT NULL BEGIN EXEC @CompanyID= dbo.InsurancePlanDataProvider_CreateInsuranceCompany @name = @CompanyName, @street_1 = '', @street_2 = '', @city = '', @state = '', @country = '', @zip = NULL, @contact_prefix = '', @contact_first_name = '', @contact_middle_name = '', @contact_last_name = '', @contact_suffix = '', @phone = NULL, @phone_x = NULL, @fax = NULL, @fax_x = NULL, @notes = '', @practice_id = @PracticeID, @bill_secondary_insurance = 0, @UseFacilityID = 1, @eclaims_disable = 0, @review_code = 'R', @BillingFormID = 13, @SecondaryPrecedenceBillingFormID = 13, @AcceptAssignment = 1, @UseSecondaryElectronicBilling = 0, @UseCoordinationOfBenefits = 1, @ExcludePatientPayment = 0 END END --Validate Insurance Plan ID, or try to match exact plan anme or create a new record SELECT @PlanID = InsuranceCompanyPlanID FROM dbo.InsuranceCompanyPlan WITH ( NOLOCK ) WHERE InsuranceCompanyPlanID = @PlanID IF @@ROWCOUNT = 0 SET @PlanID = NULL IF @PlanID IS NULL BEGIN SELECT TOP 1 @PlanID = InsuranceCompanyPlanID FROM dbo.InsuranceCompanyPlan AS IP WITH ( NOLOCK ) LEFT JOIN dbo.InsuranceCompany AS IC ON IP.InsuranceCompanyID = IC.InsuranceCompanyID LEFT OUTER JOIN PracticeToInsuranceCompany PTICP ON PTICP.InsuranceCompanyID = IC.InsuranceCompanyID AND PTICP.PracticeID = @PracticeID WHERE PlanName = @PlanName AND ( @CompanyID IS NULL OR IP.InsuranceCompanyID = @CompanyID ) AND ( ( COALESCE(IP.CreatedPracticeID, 0) = @PracticeID AND COALESCE(IC.CreatedPracticeID, 0) = @PracticeID ) OR -- [Plan and Company is created in that practice] ( IP.ReviewCode = 'R' AND COALESCE(IC.CreatedPracticeID, 0) = @PracticeID ) OR -- [Plan is for all practices and Company is created in that practice] ( IP.ReviewCode = 'R' AND IC.ReviewCode = 'R' ) OR -- [Plan and Company are for all practices] ( COALESCE(IP.CreatedPracticeID, 0) = @PracticeID AND IC.ReviewCode = 'R' ) ) -- [Plan is created in that practice and Company is for all practices] ORDER BY CASE WHEN ( COALESCE(IP.CreatedPracticeID, 0) = @PracticeID AND COALESCE(IC.CreatedPracticeID, 0) = @PracticeID ) THEN 1 WHEN ( COALESCE(IP.CreatedPracticeID, 0) = @PracticeID AND IC.ReviewCode = 'R' ) THEN 2 WHEN ( IP.ReviewCode = 'R' AND COALESCE(IC.CreatedPracticeID, 0) = @PracticeID ) THEN 3 WHEN ( IP.ReviewCode = 'R' AND IC.ReviewCode = 'R' ) THEN 4 END IF @CompanyID IS NOT NULL AND @PlanID IS NULL AND @PlanName IS NOT NULL BEGIN EXEC @PlanID= dbo.InsurancePlanDataProvider_CreateInsurancePlan @company_id = @CompanyID, @name = @PlanName, @street_1 = @InsPlanAddressLine1, @street_2 = @InsPlanAddressLine2, @city = @InsPlanCity, @state = @InsPlanState, @country = @InsPlanCountry, @zip = @InsPlanZipCode, @contact_prefix = '', @contact_first_name = '', @contact_middle_name = '', @contact_last_name = '', @contact_suffix = '', @phone = '', @phone_x = '', @fax = '', @fax_x = '', @notes = '', @practice_id = @PracticeID END END --Get relationship code SET @InsRelationShip = NULL SELECT @InsRelationShip = RelationShip FROM dbo.RelationShip WITH ( NOLOCK ) WHERE LongName = @InsuredPatientRelationshipToInsured IF @InsRelationShip IS NULL SELECT @InsRelationShip = RelationShip FROM dbo.RelationShip WITH ( NOLOCK ) WHERE RelationShip = @InsuredPatientRelationshipToInsured SET @InsRelationShip = ISNULL(@InsRelationShip, 'S') --If Insurance Policy ID is passed, validate, else create if no such policy SELECT @InsurancePolicyID = InsurancePolicyID FROM dbo.InsurancePolicy WITH ( NOLOCK ) WHERE PracticeID = @PracticeID AND InsurancePolicyID = @InsurancePolicyID IF @@ROWCOUNT = 0 SET @InsurancePolicyID = NULL IF @InsurancePolicyID IS NULL AND @InsurancePolicyExternalID IS NOT NULL AND @InsurancePolicyExternalID <> '' AND @ExternalVendorID IS NOT NULL SELECT @InsurancePolicyID = InsurancePolicyID FROM dbo.ExternalIDToInsurancePolicyMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND ExternalID = @InsurancePolicyExternalID IF @InsurancePolicyID IS NULL BEGIN --Insert Insurance Policy record IF @PlanID IS NOT NULL BEGIN SET @ActiveInsurance = ISNULL(@ActiveInsurance, 1) EXEC @InsurancePolicyID= dbo.PatientDataProvider_CreateInsurancePolicy @CaseID, @PracticeID, @PlanID, @PolicyNumber, @PolicyGroupNumber, @Copay, @Deductible, @EffectiveStartDate, @EffectiveEndDate, 0, @InsRelationShip, @InsuredPrefix, @InsuredFirstName, @InsuredMiddleName, @InsuredLastName, @InsuredSuffix, @InsuredSocialSecurityNumber, @InsuredDateofBirth, @InsuredPolicyThroughEmployer, @InsuredEmployer, @InsuredGender, @InsuredAddressLine1, @InsuredAddressLine2, @InsuredCity, @InsuredState, @InsuredCountry, @InsuredZipCode, NULL, NULL, @InsuredIDNumber, @PolicyNotes, @AdjusterPrefix, @AdjusterFirstName, @AdjusterMiddleName, @AdjusterLastName, @AdjusterSuffix, @AdjusterPhoneNumber, @AdjusterPhoneNumberExt, @AdjusterFaxNumber, @AdjusterFaxNumberExt, @ActiveInsurance, NULL, @userId, @InsurancePolicyExternalID, @ExternalVendorID INSERT INTO #PoliciesAffected ( CaseNodeID , RID , PatientCaseID , InsurancePolicyID , InsuranceCompanyPlanID , ExternalID ) SELECT @CaseNodeID , @PolicyStep , @CaseID , InsurancePolicyID , InsuranceCompanyPlanID , CASE WHEN @ExternalVendorID IS NULL THEN NULL ELSE @InsurancePolicyExternalID END AS ExternalID FROM dbo.InsurancePolicy IP ( NOLOCK ) WHERE InsurancePolicyID = @InsurancePolicyID SET @PolicyInserted = 1 END END ELSE BEGIN SELECT @PlanID = COALESCE(@PlanID, InsuranceCompanyPlanID) , @PolicyNumber = COALESCE(@PolicyNumber, PolicyNumber) , @PolicyGroupNumber = COALESCE(@PolicyGroupNumber, GroupNumber) , @Copay = COALESCE(@Copay, Copay) , @Deductible = COALESCE(@Deductible, Deductible) , @EffectiveStartDate = COALESCE(@EffectiveStartDate, PolicyStartDate) , @EffectiveEndDate = COALESCE(@EffectiveEndDate, PolicyEndDate) , @CardOnFile = CardOnFile , @InsRelationShip = COALESCE(@InsRelationShip, PatientRelationshipToInsured) , @InsuredPrefix = COALESCE(@InsuredPrefix, HolderPrefix) , @InsuredFirstName = COALESCE(@InsuredFirstName, HolderFirstName) , @InsuredMiddleName = COALESCE(@InsuredMiddleName, HolderMiddleName) , @InsuredLastName = COALESCE(@InsuredLastName, HolderLastName) , @InsuredSuffix = COALESCE(@InsuredSuffix, HolderSuffix) , @InsuredDateofBirth = COALESCE(@InsuredDateofBirth, HolderDOB) , @InsuredSocialSecurityNumber = COALESCE(@InsuredSocialSecurityNumber, HolderSSN) , @InsuredPolicyThroughEmployer = COALESCE(@InsuredPolicyThroughEmployer, HolderThroughEmployer) , @InsuredEmployer = COALESCE(@InsuredEmployer, HolderEmployerName) , @InsuredGender = COALESCE(@InsuredGender, HolderGender) , @InsuredAddressLine1 = COALESCE(@InsuredAddressLine1, HolderAddressLine1) , @InsuredAddressLine2 = COALESCE(@InsuredAddressLine2, HolderAddressLine2) , @InsuredCity = COALESCE(@InsuredCity, HolderCity) , @InsuredState = COALESCE(@InsuredState, HolderState) , @InsuredCountry = COALESCE(@InsuredCountry, HolderCountry) , @InsuredZipCode = COALESCE(@InsuredZipCode, HolderZipCode) , @InsuredPhone = HolderPhone , @InsuredPhoneExt = HolderPhoneExt , @InsuredIDNumber = COALESCE(@InsuredIDNumber, DependentPolicyNumber) , @PolicyNotes = COALESCE(@PolicyNotes, Notes) , @AdjusterPrefix = COALESCE(@AdjusterPrefix, AdjusterPrefix) , @AdjusterFirstName = COALESCE(@AdjusterFirstName, AdjusterFirstName) , @AdjusterMiddleName = COALESCE(@AdjusterMiddleName, AdjusterMiddleName) , @AdjusterLastName = COALESCE(@AdjusterLastName, AdjusterLastName) , @AdjusterSuffix = COALESCE(@AdjusterSuffix, AdjusterSuffix) , @AdjusterPhoneNumber = COALESCE(@AdjusterPhoneNumber, Phone) , @AdjusterPhoneNumberExt = COALESCE(@AdjusterPhoneNumberExt, PhoneExt) , @AdjusterFaxNumber = COALESCE(@AdjusterFaxNumber, Fax) , @AdjusterFaxNumberExt = COALESCE(@AdjusterFaxNumberExt, FaxExt) , @ActiveInsurance = COALESCE(@ActiveInsurance, Active) , @InsuranceProgramTypeID = InsuranceProgramTypeID , @userId = COALESCE(@userId, ModifiedUserID) , @GroupName = GroupName , @ReleaseOfInformation = ReleaseOfInformation FROM dbo.InsurancePolicy WITH ( NOLOCK ) WHERE InsurancePolicyID = @InsurancePolicyID EXEC dbo.PatientDataProvider_UpdateInsurancePolicy @InsurancePolicyID, @PlanID, @PracticeID, @CaseID, @PolicyNumber, @PolicyGroupNumber, @Copay, @Deductible, @EffectiveStartDate, @EffectiveEndDate, @CardOnFile, @InsRelationShip, @InsuredPrefix, @InsuredFirstName, @InsuredMiddleName, @InsuredLastName, @InsuredSuffix, @InsuredSocialSecurityNumber, @InsuredDateofBirth, @InsuredPolicyThroughEmployer, @InsuredEmployer, @InsuredGender, @InsuredAddressLine1, @InsuredAddressLine2, @InsuredCity, @InsuredState, @InsuredCountry, @InsuredZipCode, @InsuredPhone, @InsuredPhoneExt, @InsuredIDNumber, @PolicyNotes, @AdjusterPrefix, @AdjusterFirstName, @AdjusterMiddleName, @AdjusterLastName, @AdjusterSuffix, @AdjusterPhoneNumber, @AdjusterPhoneNumberExt, @AdjusterFaxNumber, @AdjusterFaxNumberExt, @ActiveInsurance, @InsuranceProgramTypeID, @userId, @GroupName, @ReleaseOfInformation IF @ExternalVendorID IS NOT NULL AND @InsurancePolicyExternalID IS NOT NULL AND @InsurancePolicyExternalID <> '' AND NOT EXISTS ( SELECT 1 FROM dbo.ExternalIDToInsurancePolicyMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND ExternalID = @InsurancePolicyExternalID AND InsurancePolicyID = @InsurancePolicyID ) BEGIN UPDATE eim SET ExternalID = @InsurancePolicyExternalID FROM dbo.ExternalIDToInsurancePolicyMap eim ( ROWLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND InsurancePolicyID = @InsurancePolicyID IF @@ROWCOUNT = 0 INSERT INTO dbo.ExternalIDToInsurancePolicyMap ( ExternalVendorID , ExternalID , InsurancePolicyID ) VALUES ( @ExternalVendorID , @InsurancePolicyExternalID , @InsurancePolicyID ) END INSERT INTO #PoliciesAffected ( CaseNodeID , RID , PatientCaseID , InsurancePolicyID , InsuranceCompanyPlanID , ExternalID ) SELECT @CaseNodeID , @PolicyStep , @CaseID , InsurancePolicyID , InsuranceCompanyPlanID , CASE WHEN @ExternalVendorID IS NULL THEN NULL ELSE @InsurancePolicyExternalID END AS ExternalID FROM dbo.InsurancePolicy IP ( NOLOCK ) WHERE InsurancePolicyID = @InsurancePolicyID SET @PolicyUpdated = 1 END IF @Precedence IS NOT NULL AND @Precedence > 0 BEGIN SELECT @MaxPrecedence = MAX(Precedence) , @CurrentPrecedence = MAX(CASE WHEN InsurancePolicyID = @InsurancePolicyID THEN Precedence ELSE NULL END) FROM dbo.InsurancePolicy WITH ( NOLOCK ) WHERE PatientCaseID = @CaseID IF @CurrentPrecedence <> @Precedence BEGIN --Check what direction precedence is being changed to IF @CurrentPrecedence - @Precedence < 0 --Precedence is moving up BEGIN UPDATE IP SET Precedence = @Precedence + 1000000 FROM dbo.InsurancePolicy IP WITH ( ROWLOCK, UPDLOCK ) WHERE PatientCaseID = @CaseID AND InsurancePolicyID = @InsurancePolicyID UPDATE IP SET Precedence = Precedence - 1 FROM dbo.InsurancePolicy IP WITH ( ROWLOCK, UPDLOCK ) WHERE PatientCaseID = @CaseID AND Precedence BETWEEN @CurrentPrecedence AND @Precedence UPDATE IP SET Precedence = @Precedence FROM dbo.InsurancePolicy IP WHERE PatientCaseID = @CaseID AND InsurancePolicyID = @InsurancePolicyID END ELSE -- Precedence is moving down BEGIN UPDATE IP SET Precedence = @Precedence + 1000000 FROM dbo.InsurancePolicy IP WITH ( ROWLOCK, UPDLOCK ) WHERE PatientCaseID = @CaseID AND InsurancePolicyID = @InsurancePolicyID UPDATE IP SET Precedence = Precedence + 1 FROM dbo.InsurancePolicy IP WITH ( ROWLOCK, UPDLOCK ) WHERE PatientCaseID = @CaseID AND Precedence BETWEEN @Precedence AND @CurrentPrecedence UPDATE IP SET Precedence = CASE WHEN @Precedence > @MaxPrecedence THEN @MaxPrecedence ELSE @Precedence END FROM dbo.InsurancePolicy IP WHERE PatientCaseID = @CaseID AND InsurancePolicyID = @InsurancePolicyID END END END IF NOT EXISTS ( SELECT 1 FROM #PoliciesAffected WHERE CaseNodeID = @CaseNodeID AND RID = @PolicyStep ) INSERT INTO #PoliciesAffected ( CaseNodeID , RID , PatientCaseID , ExternalID ) VALUES ( @CaseNodeID , @PolicyStep , @CaseID , CASE WHEN @ExternalVendorID IS NULL THEN NULL ELSE @InsurancePolicyExternalID END ) END -- If policies were only inserted and not updated go ahead and deactivate the previously existing policies IF @PolicyUpdated = 0 AND @PolicyInserted = 1 AND EXISTS ( SELECT 1 FROM #ExistingPolicies ) UPDATE IP SET Active = 0 FROM dbo.InsurancePolicy IP WITH ( ROWLOCK, UPDLOCK ) WHERE InsurancePolicyID IN ( SELECT InsurancePolicyID FROM #ExistingPolicies ) SELECT @AuthoCount = MAX(A.TID) FROM #AuthoCaseMap ACM INNER JOIN #Authorization A ON ACM.AuthosNodeID = A.parentNodeID WHERE ACM.CaseNodeID = @CaseNodeID WHILE @AuthoStep < @AuthoCount AND @CaseID IS NOT NULL BEGIN SET @AuthoStep = @AuthoStep + 1 SELECT @AuthorizationID = dbo.KAPI_fn_ConvertToINT(AuthorizationID, 0, NULL) , @AuthoInsurancePolicyID = dbo.KAPI_fn_ConvertToINT(InsurancePolicyID, 0, NULL) , @AuthoInsurancePlanID = dbo.KAPI_fn_ConvertToINT(InsurancePlanID, 0, NULL) , @AuthoInsurancePlanName = InsurancePlanName , @AuthoNumber = Number , @AuthoNumberOfVisits = dbo.KAPI_fn_ConvertToINT(NumberOfVisits, 1, NULL) , @AuthoContactFullName = ContactFullName , @AuthoContactPhone = ContactPhone , @AuthoContactPhoneExt = ContactPhoneExt , @AuthoNotes = Notes , @AuthoStartDate = dbo.KAPI_fn_ConvertToDateTime(StartDate, 0, NULL) , @AuthoEndDate = dbo.KAPI_fn_ConvertToDateTime(EndDate, 0, NULL) FROM #Authorization WHERE TID = @AuthoStep IF @AuthorizationID IS NOT NULL BEGIN --Attempt to Validate ID SELECT @AuthorizationID = InsurancePolicyAuthorizationID FROM dbo.InsurancePolicyAuthorization IPA ( NOLOCK ) INNER JOIN dbo.InsurancePolicy IP ( NOLOCK ) ON IPA.InsurancePolicyID = IP.InsurancePolicyID WHERE IPA.InsurancePolicyAuthorizationID = @AuthorizationID AND IP.PatientCaseID = @CaseID AND IP.PracticeID = @PracticeID AND IP.Active = 1 END IF @@ROWCOUNT = 0 SET @AuthorizationID = NULL IF @AuthorizationID IS NULL AND @AuthoNumber IS NOT NULL BEGIN --Attempt to Match up an existing Authorization SELECT @AuthorizationID = InsurancePolicyAuthorizationID FROM dbo.InsurancePolicyAuthorization IPA ( NOLOCK ) INNER JOIN dbo.InsurancePolicy IP ( NOLOCK ) ON IPA.InsurancePolicyID = IP.InsurancePolicyID WHERE IP.PatientCaseID = @CaseID AND IP.PracticeID = @PracticeID AND IP.Active = 1 AND ( AuthorizationNumber = @AuthoNumber AND ( @AuthoInsurancePolicyID IS NULL OR IP.InsurancePolicyID = @AuthoInsurancePolicyID ) ) END IF @AuthorizationID IS NULL BEGIN --Attempt to match up an insurance policy ID in order to create new Autho IF @AuthoInsurancePolicyID IS NOT NULL BEGIN SELECT @AuthoInsurancePolicyID = InsurancePolicyID FROM dbo.InsurancePolicy IP ( NOLOCK ) WHERE IP.InsurancePolicyID = @AuthoInsurancePolicyID AND IP.PatientCaseID = @CaseID AND IP.PracticeID = @PracticeID AND IP.Active = 1 IF @@ROWCOUNT = 0 SET @AuthoInsurancePolicyID = NULL END IF @AuthoInsurancePolicyID IS NULL BEGIN --Validate Insurance Plan ID, or try to match exact plan name SELECT @AuthoInsurancePlanID = InsuranceCompanyPlanID FROM dbo.InsuranceCompanyPlan WITH ( NOLOCK ) WHERE InsuranceCompanyPlanID = @AuthoInsurancePlanID IF @@ROWCOUNT = 0 SET @AuthoInsurancePlanID = NULL IF @AuthoInsurancePlanID IS NULL AND @AuthoInsurancePlanName IS NOT NULL BEGIN SELECT @AuthoInsurancePolicyID = IP.InsurancePolicyID FROM dbo.InsurancePolicy IP ( NOLOCK ) INNER JOIN dbo.InsuranceCompanyPlan ICP ( NOLOCK ) ON IP.InsuranceCompanyPlanID = ICP.InsuranceCompanyPlanID WHERE IP.PatientCaseID = @CaseID AND IP.PracticeID = @PracticeID AND IP.Active = 1 AND PlanName = @AuthoInsurancePlanName END END IF @AuthoInsurancePlanID IS NOT NULL AND @AuthoInsurancePolicyID IS NULL BEGIN SELECT TOP 1 @AuthoInsurancePolicyID = IP.InsurancePolicyID FROM dbo.InsurancePolicy IP ( NOLOCK ) WHERE IP.PatientCaseID = @CaseID AND IP.PracticeID = @PracticeID AND IP.InsuranceCompanyPlanID = @AuthoInsurancePlanID AND IP.Active = 1 END IF @AuthoInsurancePolicyID IS NOT NULL AND @AuthoNumber IS NOT NULL AND @AuthoNumberOfVisits IS NOT NULL BEGIN EXEC @AuthorizationID= dbo.PatientDataProvider_CreateInsurancePolicyAuthorization @AuthoInsurancePolicyID, @AuthoNumber, @AuthoNumberOfVisits, @AuthoStartDate, @AuthoEndDate, @AuthoContactFullName, @AuthoContactPhone, @AuthoContactPhoneExt, @AuthoNotes, @userId INSERT INTO #AuthosAffected ( CaseNodeID , RID , PatientCaseID , AuthorizationID , InsurancePolicyID ) SELECT @CaseNodeID , @AuthoStep , @CaseID , InsurancePolicyAuthorizationID , InsurancePolicyID FROM dbo.InsurancePolicyAuthorization WITH ( NOLOCK ) WHERE InsurancePolicyAuthorizationID = @AuthorizationID END END ELSE BEGIN SELECT @AuthoInsurancePolicyID = COALESCE(@AuthoInsurancePolicyID, InsurancePolicyID) , @AuthoNumber = COALESCE(@AuthoNumber, AuthorizationNumber) , @AuthoNumberOfVisits = COALESCE(@AuthoNumberOfVisits, AuthorizedNumberOfVisits) , @AuthoStartDate = COALESCE(@AuthoStartDate, StartDate) , @AuthoEndDate = COALESCE(@AuthoEndDate, EndDate) , @AuthoContactFullName = COALESCE(@AuthoContactFullName, ContactFullname) , @AuthoContactPhone = COALESCE(@AuthoContactPhone, ContactPhone) , @AuthoContactPhoneExt = COALESCE(@AuthoContactPhoneExt, ContactPhoneExt) , @AuthoNotes = COALESCE(@AuthoNotes, Notes) FROM dbo.InsurancePolicyAuthorization WITH ( NOLOCK ) WHERE InsurancePolicyAuthorizationId = @AuthorizationID EXEC dbo.PatientDataProvider_UpdateInsurancePolicyAuthorization @insurance_policy_authorization_id = @AuthorizationID, @insurance_policy_id = @AuthoInsurancePolicyID, @authorization_number = @AuthoNumber, @authorized_visits = @AuthoNumberOfVisits, @start_date = @AuthoStartDate, @end_date = @AuthoEndDate, @contact_full_name = @AuthoContactFullName, @contact_phone = @AuthoContactPhone, @contact_phone_x = @AuthoContactPhoneExt, @notes = @AuthoNotes, @modified_user_id = @userId INSERT INTO #AuthosAffected ( CaseNodeID , RID , PatientCaseID , AuthorizationID , InsurancePolicyID ) SELECT @CaseNodeID , @AuthoStep , @CaseID , InsurancePolicyAuthorizationID , InsurancePolicyID FROM dbo.InsurancePolicyAuthorization WITH ( NOLOCK ) WHERE InsurancePolicyAuthorizationID = @AuthorizationID END --If No Autho was affected indicate so by supplying an empty result set IF NOT EXISTS ( SELECT 1 FROM #AuthosAffected WHERE CaseNodeID = @CaseNodeID AND RID = @AuthoStep ) INSERT INTO #AuthosAffected ( CaseNodeID, RID, PatientCaseID ) VALUES ( @CaseNodeID, @AuthoStep, @CaseID ) END END --Validate Alert Data and insert if valid DECLARE @AlertMessage VARCHAR(MAX) , @ShowWhenDisplayingPatientDetails BIT , @ShowWhenSchedulingAppointments BIT , @ShowWhenEnteringEncounters BIT , @ShowWhenViewingClaimDetails BIT , @ShowWhenPostingPayments BIT , @ShowWhenPreparingPatientStatements BIT SELECT @AlertMessage = [Message] , @ShowWhenDisplayingPatientDetails = dbo.KAPI_fn_ConvertToBIT(ShowWhenDisplayingPatientDetails) , @ShowWhenSchedulingAppointments = dbo.KAPI_fn_ConvertToBIT(ShowWhenSchedulingAppointments) , @ShowWhenEnteringEncounters = dbo.KAPI_fn_ConvertToBIT(ShowWhenEnteringEncounters) , @ShowWhenViewingClaimDetails = dbo.KAPI_fn_ConvertToBIT(ShowWhenViewingClaimDetails) , @ShowWhenPostingPayments = dbo.KAPI_fn_ConvertToBIT(ShowWhenPostingPayments) , @ShowWhenPreparingPatientStatements = dbo.KAPI_fn_ConvertToBIT(ShowWhenPreparingPatientStatements) FROM #Alert IF @AlertMessage IS NOT NULL OR @ShowWhenDisplayingPatientDetails IS NOT NULL OR @ShowWhenSchedulingAppointments IS NOT NULL OR @ShowWhenEnteringEncounters IS NOT NULL OR @ShowWhenViewingClaimDetails IS NOT NULL OR @ShowWhenPostingPayments IS NOT NULL OR @ShowWhenPreparingPatientStatements IS NOT NULL BEGIN IF NOT EXISTS ( SELECT 1 FROM dbo.PatientAlert WITH ( NOLOCK ) WHERE PatientID = @PatientID ) BEGIN EXEC dbo.PatientDataProvider_CreatePatientAlert @PatientID, @AlertMessage, @ShowWhenDisplayingPatientDetails, @ShowWhenSchedulingAppointments, @ShowWhenEnteringEncounters, @ShowWhenViewingClaimDetails, @ShowWhenPostingPayments, @ShowWhenPreparingPatientStatements, @userId, @userId END ELSE BEGIN DECLARE @PatientAlertID INT SELECT @PatientAlertID = PatientAlertID , @AlertMessage = COALESCE(@AlertMessage, AlertMessage) , @ShowWhenDisplayingPatientDetails = COALESCE(@ShowWhenDisplayingPatientDetails, ShowInPatientFlag) , @ShowWhenSchedulingAppointments = COALESCE(@ShowWhenSchedulingAppointments, ShowInAppointmentFlag) , @ShowWhenEnteringEncounters = COALESCE(@ShowWhenEnteringEncounters, ShowInEncounterFlag) , @ShowWhenViewingClaimDetails = COALESCE(@ShowWhenViewingClaimDetails, ShowInClaimFlag) , @ShowWhenPostingPayments = COALESCE(@ShowWhenPostingPayments, ShowInPaymentFlag) , @ShowWhenPreparingPatientStatements = COALESCE(@ShowWhenPreparingPatientStatements, ShowInPatientStatementFlag) FROM dbo.PatientAlert WITH ( NOLOCK ) WHERE PatientID = @PatientID EXEC dbo.PatientDataProvider_UpdatePatientAlert @PatientAlertID, @PatientID, @AlertMessage, @ShowWhenDisplayingPatientDetails, @ShowWhenSchedulingAppointments, @ShowWhenEnteringEncounters, @ShowWhenViewingClaimDetails, @ShowWhenPostingPayments, @ShowWhenPreparingPatientStatements, @userId END END --Make sure we try to feedback External IDs, even if they were not supplied --this step takes a lookup against Kareo records for Patient, Practice, PatientCase, and InsurancPolicy IF @PatientExternalID IS NULL AND @ExternalVendorID IS NOT NULL SELECT @PatientExternalID = ExternalID FROM dbo.ExternalIDToPatientMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND PatientID = @PatientID IF @PracticeExternalID IS NULL AND @ExternalVendorID IS NOT NULL SELECT @PracticeExternalID = ExternalID FROM dbo.ExternalIDToPracticeMap WITH ( NOLOCK ) WHERE ExternalVendorID = @ExternalVendorID AND PracticeID = @PracticeID ; IF EXISTS ( SELECT 1 FROM #CasesAffected WHERE ExternalID IS NULL ) AND @ExternalVendorID IS NOT NULL BEGIN UPDATE CA SET ExternalID = PC.ExternalID FROM #CasesAffected CA INNER JOIN dbo.ExternalIDToPatientCaseMap PC ( NOLOCK ) ON PC.ExternalVendorID = @ExternalVendorID AND CA.PatientCaseID = PC.PatientCaseID WHERE CA.ExternalID IS NULL ; END IF EXISTS ( SELECT 1 FROM #PoliciesAffected WHERE ExternalID IS NULL ) AND @ExternalVendorID IS NOT NULL BEGIN UPDATE PA SET ExternalID = IP.ExternalID FROM #PoliciesAffected PA INNER JOIN dbo.ExternalIDToInsurancePolicyMap IP ( NOLOCK ) ON IP.ExternalVendorID = @ExternalVendorID AND PA.InsurancePolicyID = IP.InsurancePolicyID WHERE PA.ExternalID IS NULL ; END --Return General Patient Info SELECT @PatientID AS PatientID , @ExternalVendorID AS ExternalVendorID , @PatientExternalID AS PatientExternalID , @PracticeID AS PracticeID , @PracticeExternalID AS PracticeExternalID , @PracticeName AS PracticeName , @EmployerID AS EmployerID , @LocationID AS DefaultServiceLocationID ; --Return Case Structures (i.e. Case, Policies, Authos) SELECT ( SELECT ISNULL(CAST(CA.PatientCaseID AS VARCHAR(20)), '') AS 'CaseID' , ISNULL(CA.ExternalID, '') AS 'CaseExternalID' , ( SELECT ISNULL(CAST(PA.InsurancePolicyID AS VARCHAR(20)), '') AS InsurancePolicyID , ISNULL(CAST(PA.InsuranceCompanyPlanID AS VARCHAR(20)), '') AS InsurancePolicyPlanID , ISNULL(CAST(ICP.InsuranceCompanyID AS VARCHAR(20)), '') AS InsurancePolicyCompanyID , ISNULL(PA.ExternalID, '') AS InsurancePolicyExternalID FROM #PoliciesAffected PA LEFT JOIN dbo.InsuranceCompanyPlan ICP ( NOLOCK ) ON PA.InsuranceCompanyPlanID = ICP.InsuranceCompanyPlanID WHERE PA.CaseNodeID = CA.CaseNodeID ORDER BY PA.RID FOR XML PATH('Policy') , TYPE ) AS Policies , ( SELECT AA.AuthorizationID , AA.InsurancePolicyID FROM #AuthosAffected AA WHERE AA.CaseNodeID = CA.CaseNodeID ORDER BY AA.RID FOR XML PATH('Authorization') , TYPE ) AS Authorizations FROM #CasesAffected CA ORDER BY CA.CaseNodeID FOR XML PATH('Case') , ROOT('Cases') , TYPE ) AS CaseResults ; GOTO CLEANUP ; ON_MANUAL_ERROR: SELECT NULL PatientID , @ExternalVendorID AS ExternalVendorID , @PatientExternalID AS PatientExternalID , NULL PracticeID , @PracticeExternalID AS PracticeExternalID , NULL PracticeName , NULL EmployerID , NULL DefaultServiceLocationID ; SELECT NULL CaseResults ; SELECT @updatePatientInfo errors ; IF @@TRANCOUNT > 0 ROLLBACK TRAN ; GOTO CLEANUP ; CLEANUP: --DROP TABLE #GeneralInfo --DROP TABLE #Location --DROP TABLE #Cases --DROP TABLE #Insurance --DROP TABLE #Authorization --DROP TABLE #Alert --DROP TABLE #InsuranceCaseMap --DROP TABLE #AuthoCaseMap --DROP TABLE #CasesAffected --DROP TABLE #PoliciesAffected --DROP TABLE #AuthosAffected --DROP TABLE #ExistingPolicies END[/code]Wed, 06 Mar 2013 09:34:17 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspx1) http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx. Note there are 2 additional parts to this blog series. 2) With such a complex construct (2000 line sproc, trigger(s) in play, etc) you may be best served by getting a professional on board to assist you. You could spend days going back and forth on this forum or wind up with a solution that causes other problems.Wed, 06 Mar 2013 08:06:54 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxLooks like the same proc initiating the update so we can expect the same access pattern but the trancount is 2. What else was done inside the transaction, before the procs were called? And what else is going in the proc? Is there conditional logic that could affect the data access pattern?Tue, 05 Mar 2013 22:46:23 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxHere is the xml and the actual graph is attached. I put the xml into the analysis script which is how I came up with the deadlock being between the trigger and the update statement.[code="xml"]<deadlock-list><deadlock victim="processf945c8"><process-list><process id="process32e868" taskpriority="0" logused="516" waitresource="KEY: 348:1438969838829568 (6a00b35de28b)" waittime="3931" ownerId="1102032551" transactionname="UPDATE" lasttranstarted="2013-03-01T10:39:03.790" XDES="0x1000996700" lockMode="U" schedulerid="7" kpid="18796" status="suspended" spid="267" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-03-01T10:39:03.760" lastbatchcompleted="2013-03-01T10:39:03.760" clientapp=".Net SqlClient Data Provider" hostname="KPROD-API01" hostpid="5592" loginname="dev" isolationlevel="read committed (2)" xactid="1102032551" currentdb="348" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"><executionStack><frame procname="superbill_5621_prod.dbo.tr_IU_Patient_ChangeTime" line="20" stmtstart="1136" stmtend="1422" sqlhandle="0x03005c01f90a702aee0870017f9d00000000000000000000">UPDATE P SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB) FROM dbo.Patient P INNER JOIN inserted i ON P.PatientID = i.PatientID </frame><frame procname="superbill_5621_prod.dbo.PatientDataProvider_UpdatePatient" line="58" stmtstart="3208" stmtend="5998" sqlhandle="0x03005c01423a25111b997c01fa9f00000100000000000000">UPDATE dbo.Patient SET Prefix = @prefix, FirstName = @first_name, MiddleName = @middle_name, LastName = @last_name, Suffix = @suffix, AddressLine1 = @address_1, AddressLine2 = @address_2, City = @city, State = @state, Country = @country, ZipCode = @zip, Gender = @gender, MaritalStatus = @marital_status, HomePhone = @home_phone, HomePhoneExt = @home_phone_x, WorkPhone = @work_phone, WorkPhoneExt = @work_phone_x, DOB = @dob, SSN = @ssn, EmailAddress = RTRIM(LTRIM(@email)), SendEmailCorrespondence = @sendEmailCorrespondence, ResponsibleDifferentThanPatient = @guarantor_different, EmploymentStatus = @employment_status, ReferringPhysicianID = @referrer_id, PrimaryProviderID = @primary_provider_id, PatientReferralSourceID = @patient_referral_source_id, DefaultServiceLocationID = @patient_default_location_id, EmployerID = @employer_id, MedicalRecordNumber = @medical_record_number, MobilePhone = @mobile_phone, MobilePhoneExt = </frame><frame procname="superbill_5621_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1720" stmtstart="176520" stmtend="178404" sqlhandle="0x03005c010ecaf473e772b2006aa100000100000000000000">EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix, @PatientFirstName, @PatientMiddleName, @PatientLastName, @PatientSuffix, @PatientAddressLine1, @PatientAddressLine2, @PatientCity, @PatientState, @PatientCountry, @PatientZipCode, @PatientGender, @PatientMaritalStatus, @PatientHomePhone, @PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt, @PatientDateOfBirth, @PatientSSN, @PatientEmail, @PatientEmailCorrespondence, @GuarantorDifferentThanPatient, @EmploymentStatus, @ReferringProviderID, @DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID, @EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone, @PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userId, @active, 0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt </frame></executionStack><inputbuf>Proc [Database Id = 348 Object Id = 1945422350] </inputbuf></process><process id="processf945c8" taskpriority="0" logused="0" waitresource="KEY: 348:72057594105298944 (6b00585d2e03)" waittime="3946" ownerId="1102032550" transactionname="UPDATE" lasttranstarted="2013-03-01T10:39:03.790" XDES="0x56a3d8370" lockMode="U" schedulerid="3" kpid="19368" status="suspended" spid="225" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-03-01T10:39:03.757" lastbatchcompleted="2013-03-01T10:39:03.757" clientapp=".Net SqlClient Data Provider" hostname="KPROD-API02" hostpid="5432" loginname="dev" isolationlevel="read committed (2)" xactid="1102032550" currentdb="348" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame procname="superbill_5621_prod.dbo.PatientDataProvider_UpdatePatient" line="58" stmtstart="3208" stmtend="5998" sqlhandle="0x03005c01423a25111b997c01fa9f00000100000000000000">UPDATE dbo.Patient SET Prefix = @prefix, FirstName = @first_name, MiddleName = @middle_name, LastName = @last_name, Suffix = @suffix, AddressLine1 = @address_1, AddressLine2 = @address_2, City = @city, State = @state, Country = @country, ZipCode = @zip, Gender = @gender, MaritalStatus = @marital_status, HomePhone = @home_phone, HomePhoneExt = @home_phone_x, WorkPhone = @work_phone, WorkPhoneExt = @work_phone_x, DOB = @dob, SSN = @ssn, EmailAddress = RTRIM(LTRIM(@email)), SendEmailCorrespondence = @sendEmailCorrespondence, ResponsibleDifferentThanPatient = @guarantor_different, EmploymentStatus = @employment_status, ReferringPhysicianID = @referrer_id, PrimaryProviderID = @primary_provider_id, PatientReferralSourceID = @patient_referral_source_id, DefaultServiceLocationID = @patient_default_location_id, EmployerID = @employer_id, MedicalRecordNumber = @medical_record_number, MobilePhone = @mobile_phone, MobilePhoneExt = </frame><frame procname="superbill_5621_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1720" stmtstart="176520" stmtend="178404" sqlhandle="0x03005c010ecaf473e772b2006aa100000100000000000000">EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix, @PatientFirstName, @PatientMiddleName, @PatientLastName, @PatientSuffix, @PatientAddressLine1, @PatientAddressLine2, @PatientCity, @PatientState, @PatientCountry, @PatientZipCode, @PatientGender, @PatientMaritalStatus, @PatientHomePhone, @PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt, @PatientDateOfBirth, @PatientSSN, @PatientEmail, @PatientEmailCorrespondence, @GuarantorDifferentThanPatient, @EmploymentStatus, @ReferringProviderID, @DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID, @EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone, @PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userId, @active, 0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt </frame></executionStack><inputbuf>Proc [Database Id = 348 Object Id = 1945422350] </inputbuf></process></process-list><resource-list><keylock hobtid="72057594105298944" dbid="348" objectname="superbill_5621_prod.dbo.Patient" indexname="CI_Patient_PracticeID_PatientID" id="lock1dcda6980" mode="X" associatedObjectId="72057594105298944"><owner-list><owner id="process32e868" mode="X"/></owner-list><waiter-list><waiter id="processf945c8" mode="U" requestType="wait"/></waiter-list></keylock><keylock hobtid="1438969838829568" dbid="348" objectname="superbill_5621_prod.dbo.Patient" indexname="PK_Patient" id="lock67c1bce80" mode="U" associatedObjectId="1438969838829568"><owner-list><owner id="processf945c8" mode="U"/></owner-list><waiter-list><waiter id="process32e868" mode="U" requestType="wait"/></waiter-list></keylock></resource-list></deadlock></deadlock-list>[/code]Tue, 05 Mar 2013 09:13:52 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxSwitch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.[code]DBCC TRACEON(1222,-1)[/code]Tue, 05 Mar 2013 00:32:42 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxYou should look at the queries found in the deadlock trace and the resources that are being locked. You can try tuning these queries. But sometimes you may need to review the entire logic.Need more details if you need further helpMon, 04 Mar 2013 19:44:55 GMTjoeroshanhttp://www.sqlservercentral.com/Forums/Topic1426546-360-1.aspxI have a deadlock between a trigger and a stored procedure stored procedure is always the victim of the deadlock. I believe I know what the issue is but I am not sure how to fix it.I have one stored procedure that calls multiple stored procedures. One of the inner stored procedures updates a patient table which has the trigger on it. The trigger updates one of the fields that is being updated by the stored procedure. I think the deadlock is occurring because there is a transaction in the application around the multiple stored procedures so that when the trigger is updating the column the stored procedure is also trying to commit its changes. Does this make sense?Would it make any difference if I were to take the innards of the stored procedure that's being called and put them inline in the stored procedure that's calling it? Would it wait to try to run the trigger until after the update is committed? I can certainly show the stored procedure but its over 2000 lines long. Let me know if you need additional information.thanksMon, 04 Mar 2013 18:06:00 GMTpamozer