March 4, 2013 at 6:06 pm
I 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.
thanks
March 4, 2013 at 7:44 pm
You 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 help
March 5, 2013 at 12:32 am
Switch 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.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2013 at 9:13 am
Here 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.
<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">
UPDATEdbo.Patient
SETPrefix = @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">
UPDATEdbo.Patient
SETPrefix = @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>
March 5, 2013 at 10:46 pm
Looks 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?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 8:06 am
1) 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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2013 at 9:34 am
Basically 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.
IF EXISTS ( SELECT *
FROM SYSOBJECTS
WHERE Name = 'WebServiceDataProvider_UpdatePatient'
AND TYPE = 'P' )
DROP PROCEDURE dbo.WebServiceDataProvider_UpdatePatient
GO
/*
WebServiceDataProvider_UpdatePatient
'<PatientUpdate>
<PatientID>1</PatientID>
<Practice>
<PracticeID>1</PracticeID>
</Practice>
<ZipCode>92868</ZipCode>
</PatientUpdate>'
*/
CREATE PROCEDURE [dbo].[WebServiceDataProvider_UpdatePatient]
@updatePatientInfo XML ,
@userid INT = 0
AS
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,
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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 3,
@SameorSimilarIllnessStartDate,
@SameorSimilarIllnessEndDate,
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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 4,
@UnabletoWorkStartDate,
@UnabletoWorkEndDate, @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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 5,
@RelatedDisabilityStartDate,
@RelatedDisabilityEndDate,
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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 6,
@RelatedHospitalizationStartDate,
@RelatedHospitalizationEndDate,
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,
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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 8, @LastSeenDate,
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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 9, @ReferralDate,
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,
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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 11, @LastXRayDate,
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,
SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 12, @AccidentDate,
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,
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,
IF @LastMenstrualPeriodDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 7, @LastMenstrualPeriodDate, NULL,
IF @LastSeenDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 8, @LastSeenDate, NULL, @userid,
IF @ReferralDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 9, @ReferralDate, NULL, @userid,
IF @AcuteManifestationDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 10, @AcuteManifestationDate, NULL,
IF @LastXRayDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 11, @LastXRayDate, NULL, @userid,
IF @AccidentDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 12, @AccidentDate, NULL, @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,
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,
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
March 6, 2013 at 12:28 pm
Oooh, a GOTO, haven't seen one of those in a while 😀
What else is going on in the TRIGGER? code?
And can you post the tables and all indexes of the tables involved in the deadlock?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 12:59 pm
The 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.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--===========================================================================
-- TR -- IU -- PATIENT -- CHANGE TIME
--===========================================================================
CREATE TRIGGER [dbo].[tr_IU_Patient_ChangeTime] ON [dbo].[Patient]
FOR INSERT, UPDATE
AS
BEGIN
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)
RETURN
END
GO
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]
GO
SET ANSI_PADDING OFF
GO
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]
GO
CREATE 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]
GO
CREATE 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]
GO
CREATE 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]
GO
CREATE 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]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_CollectionCategory] FOREIGN KEY([CollectionCategoryID])
REFERENCES [dbo].[CollectionCategory] ([CollectionCategoryID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_CollectionCategory]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Doctor] FOREIGN KEY([PrimaryProviderID])
REFERENCES [dbo].[Doctor] ([DoctorID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Doctor]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Employers] FOREIGN KEY([EmployerID])
REFERENCES [dbo].[Employers] ([EmployerID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Employers]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_InsuranceProgram] FOREIGN KEY([InsuranceProgramCode])
REFERENCES [dbo].[InsuranceProgram] ([InsuranceProgramCode])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_InsuranceProgram]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_PatientReferralSource] FOREIGN KEY([PatientReferralSourceID])
REFERENCES [dbo].[PatientReferralSource] ([PatientReferralSourceID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_PatientReferralSource]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_Practice] FOREIGN KEY([PracticeID])
REFERENCES [dbo].[Practice] ([PracticeID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_Practice]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_PrimaryCarePhysicianID] FOREIGN KEY([PrimaryCarePhysicianID])
REFERENCES [dbo].[Doctor] ([DoctorID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_PrimaryCarePhysicianID]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_ReferringPhysician] FOREIGN KEY([ReferringPhysicianID])
REFERENCES [dbo].[Doctor] ([DoctorID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_ReferringPhysician]
GO
ALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_ServiceLocationID] FOREIGN KEY([DefaultServiceLocationID])
REFERENCES [dbo].[ServiceLocation] ([ServiceLocationID])
GO
ALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_ServiceLocationID]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_CreatedUserID] DEFAULT (0) FOR [CreatedUserID]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_ModifiedUserID] DEFAULT (0) FOR [ModifiedUserID]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[Patient] ADD DEFAULT ((1)) FOR [SendEmailCorrespondence]
GO
ALTER TABLE [dbo].[Patient] ADD DEFAULT ((0)) FOR [PhonecallRemindersEnabled]
GO
ALTER TABLE [dbo].[Patient] ADD CONSTRAINT [DF_Patient_Guid] DEFAULT (newid()) FOR [PatientGuid]
GO
March 6, 2013 at 1:14 pm
I still don't think we have seen the code for object 1945422350.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 1:18 pm
My vote is for the huge covering index being the culprit here...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2013 at 1:23 pm
These are the two indexes involved in the deadlock:
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]
GO
CREATE 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]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 1:58 pm
One 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.
March 6, 2013 at 3:04 pm
Sorry About that here is the code:
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) = NULL
AS
BEGIN
IF @email IS NULL OR LTRIM(@email) = ''
BEGIN
SET @sendEmailCorrespondence = 0 ;
END
UPDATEdbo.Patient
SETPrefix = @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
WHEREPatientID = @patient_id ;
END
March 6, 2013 at 3:20 pm
So, 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?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply