Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Resolving a deadlock Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 6:06 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:05 AM
Points: 1,069, Visits: 421
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
Post #1426546
Posted Monday, March 4, 2013 7:44 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:19 AM
Points: 647, Visits: 1,311
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


-- Roshan Joe
*******************************************
There are two types of DBAs. Those who has skills and those who have permissions
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1426567
Posted Tuesday, March 5, 2013 12:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1426618
Posted Tuesday, March 5, 2013 9:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:05 AM
Points: 1,069, Visits: 421
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">
UPDATE dbo.Patient
SET Prefix = @prefix,
FirstName = @first_name,
MiddleName = @middle_name,
LastName = @last_name,
Suffix = @suffix,
AddressLine1 = @address_1,
AddressLine2 = @address_2,
City = @city,
State = @state,
Country = @country,
ZipCode = @zip,
Gender = @gender,
MaritalStatus = @marital_status,
HomePhone = @home_phone,
HomePhoneExt = @home_phone_x,
WorkPhone = @work_phone,
WorkPhoneExt = @work_phone_x,
DOB = @dob,
SSN = @ssn,
EmailAddress = RTRIM(LTRIM(@email)),
SendEmailCorrespondence = @sendEmailCorrespondence,
ResponsibleDifferentThanPatient = @guarantor_different,
EmploymentStatus = @employment_status,
ReferringPhysicianID = @referrer_id,
PrimaryProviderID = @primary_provider_id,
PatientReferralSourceID = @patient_referral_source_id,
DefaultServiceLocationID = @patient_default_location_id,
EmployerID = @employer_id,
MedicalRecordNumber = @medical_record_number,
MobilePhone = @mobile_phone,
MobilePhoneExt = </frame><frame procname="superbill_5621_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1720" stmtstart="176520" stmtend="178404" sqlhandle="0x03005c010ecaf473e772b2006aa100000100000000000000">
EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix,
@PatientFirstName, @PatientMiddleName, @PatientLastName,
@PatientSuffix, @PatientAddressLine1, @PatientAddressLine2,
@PatientCity, @PatientState, @PatientCountry, @PatientZipCode,
@PatientGender, @PatientMaritalStatus, @PatientHomePhone,
@PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt,
@PatientDateOfBirth, @PatientSSN, @PatientEmail,
@PatientEmailCorrespondence, @GuarantorDifferentThanPatient,
@EmploymentStatus, @ReferringProviderID,
@DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID,
@EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone,
@PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userId, @active,
0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt </frame></executionStack><inputbuf>
Proc [Database Id = 348 Object Id = 1945422350] </inputbuf></process><process id="processf945c8" taskpriority="0" logused="0" waitresource="KEY: 348:72057594105298944 (6b00585d2e03)" waittime="3946" ownerId="1102032550" transactionname="UPDATE" lasttranstarted="2013-03-01T10:39:03.790" XDES="0x56a3d8370" lockMode="U" schedulerid="3" kpid="19368" status="suspended" spid="225" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-03-01T10:39:03.757" lastbatchcompleted="2013-03-01T10:39:03.757" clientapp=".Net SqlClient Data Provider" hostname="KPROD-API02" hostpid="5432" loginname="dev" isolationlevel="read committed (2)" xactid="1102032550" currentdb="348" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame procname="superbill_5621_prod.dbo.PatientDataProvider_UpdatePatient" line="58" stmtstart="3208" stmtend="5998" sqlhandle="0x03005c01423a25111b997c01fa9f00000100000000000000">
UPDATE dbo.Patient
SET Prefix = @prefix,
FirstName = @first_name,
MiddleName = @middle_name,
LastName = @last_name,
Suffix = @suffix,
AddressLine1 = @address_1,
AddressLine2 = @address_2,
City = @city,
State = @state,
Country = @country,
ZipCode = @zip,
Gender = @gender,
MaritalStatus = @marital_status,
HomePhone = @home_phone,
HomePhoneExt = @home_phone_x,
WorkPhone = @work_phone,
WorkPhoneExt = @work_phone_x,
DOB = @dob,
SSN = @ssn,
EmailAddress = RTRIM(LTRIM(@email)),
SendEmailCorrespondence = @sendEmailCorrespondence,
ResponsibleDifferentThanPatient = @guarantor_different,
EmploymentStatus = @employment_status,
ReferringPhysicianID = @referrer_id,
PrimaryProviderID = @primary_provider_id,
PatientReferralSourceID = @patient_referral_source_id,
DefaultServiceLocationID = @patient_default_location_id,
EmployerID = @employer_id,
MedicalRecordNumber = @medical_record_number,
MobilePhone = @mobile_phone,
MobilePhoneExt = </frame><frame procname="superbill_5621_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1720" stmtstart="176520" stmtend="178404" sqlhandle="0x03005c010ecaf473e772b2006aa100000100000000000000">
EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix,
@PatientFirstName, @PatientMiddleName, @PatientLastName,
@PatientSuffix, @PatientAddressLine1, @PatientAddressLine2,
@PatientCity, @PatientState, @PatientCountry, @PatientZipCode,
@PatientGender, @PatientMaritalStatus, @PatientHomePhone,
@PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt,
@PatientDateOfBirth, @PatientSSN, @PatientEmail,
@PatientEmailCorrespondence, @GuarantorDifferentThanPatient,
@EmploymentStatus, @ReferringProviderID,
@DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID,
@EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone,
@PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userId, @active,
0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt </frame></executionStack><inputbuf>
Proc [Database Id = 348 Object Id = 1945422350] </inputbuf></process></process-list><resource-list><keylock hobtid="72057594105298944" dbid="348" objectname="superbill_5621_prod.dbo.Patient" indexname="CI_Patient_PracticeID_PatientID" id="lock1dcda6980" mode="X" associatedObjectId="72057594105298944"><owner-list><owner id="process32e868" mode="X"/></owner-list><waiter-list><waiter id="processf945c8" mode="U" requestType="wait"/></waiter-list></keylock><keylock hobtid="1438969838829568" dbid="348" objectname="superbill_5621_prod.dbo.Patient" indexname="PK_Patient" id="lock67c1bce80" mode="U" associatedObjectId="1438969838829568"><owner-list><owner id="processf945c8" mode="U"/></owner-list><waiter-list><waiter id="process32e868" mode="U" requestType="wait"/></waiter-list></keylock></resource-list></deadlock></deadlock-list>



  Post Attachments 
Deadlock.JPG (21 views, 48.22 KB)
Post #1426889
Posted Tuesday, March 5, 2013 10:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1427173
Posted Wednesday, March 6, 2013 8:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 4,467, Visits: 6,396
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 at GMail
Post #1427436
Posted Wednesday, March 6, 2013 9:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:05 AM
Points: 1,069, Visits: 421
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,
@userId
END

IF @SameorSimilarIllnessStartDate IS NOT NULL
OR @SameorSimilarIllnessEndDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 3

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @SameorSimilarIllnessStartDate = COALESCE(@SameorSimilarIllnessStartDate,
StartDate) ,
@SameorSimilarIllnessEndDate = COALESCE(@SameorSimilarIllnessEndDate,
EndDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 3,
@SameorSimilarIllnessStartDate,
@SameorSimilarIllnessEndDate,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 3,
@SameorSimilarIllnessStartDate,
@SameorSimilarIllnessEndDate,
@userId, @userId
END

IF @UnabletoWorkStartDate IS NOT NULL
OR @UnabletoWorkEndDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 4

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @UnabletoWorkStartDate = COALESCE(@UnabletoWorkStartDate,
StartDate) ,
@UnabletoWorkEndDate = COALESCE(@UnabletoWorkEndDate,
EndDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 4,
@UnabletoWorkStartDate,
@UnabletoWorkEndDate,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 4,
@UnabletoWorkStartDate,
@UnabletoWorkEndDate, @userId,
@userId
END

IF @RelatedDisabilityStartDate IS NOT NULL
OR @RelatedDisabilityEndDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 5

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @RelatedDisabilityStartDate = COALESCE(@RelatedDisabilityStartDate,
StartDate) ,
@RelatedDisabilityEndDate = COALESCE(@RelatedDisabilityEndDate,
EndDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 5,
@RelatedDisabilityStartDate,
@RelatedDisabilityEndDate,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 5,
@RelatedDisabilityStartDate,
@RelatedDisabilityEndDate,
@userId, @userId
END

IF @RelatedHospitalizationStartDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 6

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @RelatedHospitalizationStartDate = COALESCE(@RelatedHospitalizationStartDate,
StartDate) ,
@RelatedHospitalizationEndDate = COALESCE(@RelatedHospitalizationEndDate,
EndDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 6,
@RelatedHospitalizationStartDate,
@RelatedHospitalizationEndDate,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 6,
@RelatedHospitalizationStartDate,
@RelatedHospitalizationEndDate,
@userId, @userId
END

IF @LastMenstrualPeriodDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 7

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @LastMenstrualPeriodDate = COALESCE(@LastMenstrualPeriodDate,
StartDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 7,
@LastMenstrualPeriodDate,
NULL, @userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 7,
@LastMenstrualPeriodDate, NULL,
@userId, @userId
END

IF @LastSeenDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 8

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @LastSeenDate = COALESCE(@LastSeenDate,
StartDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 8,
@LastSeenDate, NULL,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 8, @LastSeenDate,
NULL, @userId, @userId
END

IF @ReferralDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 9

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @ReferralDate = COALESCE(@ReferralDate,
StartDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 9,
@ReferralDate, NULL,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 9, @ReferralDate,
NULL, @userId, @userId
END

IF @AcuteManifestationDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 10

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @AcuteManifestationDate = COALESCE(@AcuteManifestationDate,
StartDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 10,
@AcuteManifestationDate,
NULL, @userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 10,
@AcuteManifestationDate, NULL,
@userId, @userId
END

IF @LastXRayDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 11

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @LastXRayDate = COALESCE(@LastXRayDate,
StartDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 11,
@LastXRayDate, NULL,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 11, @LastXRayDate,
NULL, @userId, @userId
END

IF @AccidentDate IS NOT NULL
BEGIN
SELECT @PatientCaseDateID = MAX(PatientCaseDateID)
FROM dbo.PatientCaseDate WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND PatientCaseID = @CaseID
AND PatientCaseDateTypeID = 12

IF @PatientCaseDateID IS NOT NULL
BEGIN
SELECT @AccidentDate = COALESCE(@AccidentDate,
StartDate)
FROM dbo.PatientCaseDate
WITH ( NOLOCK )
WHERE PatientCaseDateID = @PatientCaseDateID

EXEC dbo.PatientDataProvider_UpdatePatientCaseDate @PatientCaseDateID,
@PracticeID, @CaseID, 12,
@AccidentDate, NULL,
@userId

SET @PatientCaseDateID = NULL
END
ELSE
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 12, @AccidentDate,
NULL, @userId, @userId
END
END
END

--Create Default or specified patient case
IF @PatientID IS NOT NULL
AND @CaseID IS NULL
BEGIN
--Set Defaults
SELECT @RelatedToAutoAccident = ISNULL(@RelatedToAutoAccident,
0) ,
@RelatedToAbuse = ISNULL(@RelatedToAbuse, 0) ,
@RelatedToEmployment = ISNULL(@RelatedToEmployment,
0) ,
@RelatedToOther = ISNULL(@RelatedToOther, 0) ,
@RelatedToPregnancy = ISNULL(@RelatedToPregnancy,
0) ,
@RelatedToEPSDT = ISNULL(@RelatedToEPSDT, 0) ,
@RelatedToFamilyPlanning = ISNULL(@RelatedToFamilyPlanning,
0) ,
@RelatedToEmergency = ISNULL(@RelatedToEmergency,
0) ,
@PayerScenarioID = ISNULL(@PayerScenarioID, 5) ,
@CaseName = COALESCE(@CaseName,
CASE WHEN @CaseStep = 1
THEN 'Default Case'
WHEN @CaseStep > 1
THEN 'Default Case '
+ CAST(@CaseStep AS VARCHAR)
END)

SET @ActiveCase = ISNULL(@ActiveCase, 1)

EXEC @CaseID= dbo.PatientDataProvider_CreatePatientCase @PatientID,
@PracticeID, @CaseName, @CaseDescription,
@PatientCaseReferringProviderID, @ActiveCase,
@PayerScenarioID, @RelatedToAutoAccident,
@RelatedToAutoAccidentState, @RelatedToAbuse,
@RelatedToEmployment, @RelatedToOther,
@RelatedToPregnancy, @RelatedToEPSDT, @EPSDTCodeID,
@RelatedToFamilyPlanning, 1, NULL, NULL, 1,
@userID, @RelatedToEmergency, @CaseExternalID,
@ExternalVendorID

INSERT INTO #CasesAffected
( CaseNodeID ,
PatientID ,
PatientCaseID ,
ExternalID
)
VALUES ( @CaseNodeID ,
@PatientID ,
@CaseID ,
@CaseExternalID
)

--Insert Patient Case Date records
IF @InjuryStartDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 2, @InjuryStartDate, @InjuryEndDate,
@userId, @userId

IF @SameorSimilarIllnessStartDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 3, @SameorSimilarIllnessStartDate,
@SameorSimilarIllnessEndDate, @userId, @userId

IF @UnabletoWorkStartDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 4, @UnabletoWorkStartDate,
@UnabletoWorkEndDate, @userId, @userId

IF @RelatedDisabilityStartDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 5, @RelatedDisabilityStartDate,
@RelatedDisabilityEndDate, @userId, @userId

IF @RelatedHospitalizationStartDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 6, @RelatedHospitalizationStartDate,
@RelatedHospitalizationEndDate, @userId,
@userId

IF @LastMenstrualPeriodDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 7, @LastMenstrualPeriodDate, NULL,
@userId, @userId

IF @LastSeenDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 8, @LastSeenDate, NULL, @userId,
@userId

IF @ReferralDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 9, @ReferralDate, NULL, @userId,
@userId

IF @AcuteManifestationDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 10, @AcuteManifestationDate, NULL,
@userId, @userId

IF @LastXRayDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 11, @LastXRayDate, NULL, @userId,
@userId

IF @AccidentDate IS NOT NULL
EXEC dbo.PatientDataProvider_CreatePatientCaseDate @PracticeID,
@CaseID, 12, @AccidentDate, NULL, @userId,
@userId

END

IF NOT EXISTS ( SELECT 1
FROM #CasesAffected
WHERE CaseNodeID = @CaseNodeID )
INSERT INTO #CasesAffected
( CaseNodeID ,
PatientID ,
ExternalID
)
VALUES ( @CaseNodeID ,
@PatientID ,
@CaseExternalID
)

SELECT @PolicyCount = MAX(I.TID)
FROM #InsuranceCaseMap ICM
INNER JOIN #Insurance I ON ICM.InsurancesNodeID = I.parentNodeID
WHERE ICM.CaseNodeID = @CaseNodeID

IF @PatientID IS NOT NULL
AND @CaseID IS NOT NULL
-- Store the existing policies in case we need to deactivate them if policies are only inserted
INSERT #ExistingPolicies
( InsurancePolicyID
)
SELECT InsurancePolicyID
FROM InsurancePolicy WITH ( NOLOCK )
WHERE PatientCaseID = @CaseID

DECLARE @PolicyInserted BIT
DECLARE @PolicyUpdated BIT
SET @PolicyInserted = 0
SET @PolicyUpdated = 0

WHILE @PolicyStep < @PolicyCount
AND @CaseID IS NOT NULL
BEGIN
SET @PolicyStep = @PolicyStep + 1

SELECT @InsurancePolicyID = dbo.KAPI_fn_ConvertToINT(InsurancePolicyID,
0, NULL) ,
@InsurancePolicyExternalID = ExternalID ,
@CompanyID = dbo.KAPI_fn_ConvertToINT(CompanyID,
0, NULL) ,
@CompanyName = CompanyName ,
@PlanID = dbo.KAPI_fn_ConvertToINT(PlanID, 0,
NULL) ,
@PlanName = PlanName ,
@InsPlanAddressLine1 = AddressLine1 ,
@InsPlanAddressLine2 = AddressLine2 ,
@InsPlanCity = City ,
@InsPlanState = State ,
@InsPlanCountry = Country ,
@InsPlanZipCode = ZipCode ,
@AdjusterPrefix = AdjusterPrefix ,
@AdjusterFirstName = AdjusterFirstName ,
@AdjusterMiddleName = AdjusterMiddleName ,
@AdjusterLastName = AdjusterLastName ,
@AdjusterSuffix = AdjusterSuffix ,
@AdjusterPhoneNumber = AdjusterPhoneNumber ,
@AdjusterPhoneNumberExt = AdjusterPhoneNumberExt ,
@AdjusterFaxNumber = AdjusterFaxNumber ,
@AdjusterFaxNumberExt = AdjusterFaxNumberExt ,
@PolicyNumber = PolicyNumber ,
@PolicyGroupNumber = PolicyGroupNumber ,
@Copay = dbo.KAPI_fn_ConvertToMoney(Copay, 1,
0) ,
@Deductible = dbo.KAPI_fn_ConvertToMoney(Deductible,
1, 0) ,
@EffectiveStartDate = dbo.KAPI_fn_ConvertToDateTime(EffectiveStartDate,
0, NULL) ,
@EffectiveEndDate = dbo.KAPI_fn_ConvertToDateTime(EffectiveEndDate,
0, NULL) ,
@InsuredPatientRelationshipToInsured = InsuredPatientRelationshipToInsured ,
@InsuredPrefix = InsuredPrefix ,
@InsuredFirstName = InsuredFirstName ,
@InsuredMiddleName = InsuredMiddleName ,
@InsuredLastName = InsuredLastName ,
@InsuredSuffix = InsuredSuffix ,
@InsuredAddressLine1 = InsuredAddressLine1 ,
@InsuredAddressLine2 = InsuredAddressLine2 ,
@InsuredCity = InsuredCity ,
@InsuredState = InsuredState ,
@InsuredCountry = InsuredCountry ,
@InsuredZipCode = InsuredZipCode ,
@InsuredIDNumber = InsuredIDNumber ,
@InsuredSocialSecurityNumber = InsuredSocialSecurityNumber ,
@InsuredDateofBirth = dbo.KAPI_fn_ConvertToDateTime(InsuredDateofBirth,
0, NULL) ,
@InsuredGender = InsuredGender ,
@InsuredPolicyThroughEmployer = COALESCE(dbo.KAPI_fn_ConvertToBIT(InsuredPolicyThroughEmployer),
0) ,
@InsuredEmployer = InsuredEmployer ,
@PolicyNotes = PolicyNotes ,
@ActiveInsurance = dbo.KAPI_fn_ConvertToBIT(Active) ,
@Precedence = dbo.KAPI_fn_ConvertToINT(Precedence,
0, NULL)
FROM #Insurance
WHERE TID = @PolicyStep

--Validate Insurance Company ID, or try to match exact company name or Create new record

SELECT @CompanyID = InsuranceCompanyID
FROM dbo.InsuranceCompany WITH ( NOLOCK )
WHERE InsuranceCompanyID = @CompanyID

IF @@ROWCOUNT = 0
SET @CompanyID = NULL

IF @CompanyID IS NULL
BEGIN

SELECT TOP 1
@CompanyID = InsuranceCompanyID
FROM dbo.InsuranceCompany IC ( NOLOCK )
WHERE InsuranceCompanyName = @CompanyName
AND ( -- "All practices" scoped companies and companies created for that practice
IC.ReviewCode = 'R'
OR COALESCE(IC.CreatedPracticeID,
0) = @PracticeID
)
ORDER BY CASE WHEN COALESCE(IC.CreatedPracticeID,
0) = @PracticeID
THEN 1
WHEN IC.ReviewCode = 'R' THEN 2
END

IF @CompanyID IS NULL
AND @CompanyName IS NOT NULL
BEGIN
EXEC @CompanyID= dbo.InsurancePlanDataProvider_CreateInsuranceCompany @name = @CompanyName,
@street_1 = '', @street_2 = '',
@city = '', @state = '',
@country = '', @zip = NULL,
@contact_prefix = '',
@contact_first_name = '',
@contact_middle_name = '',
@contact_last_name = '',
@contact_suffix = '',
@phone = NULL, @phone_x = NULL,
@fax = NULL, @fax_x = NULL,
@notes = '',
@practice_id = @PracticeID,
@bill_secondary_insurance = 0,
@UseFacilityID = 1,
@eclaims_disable = 0,
@review_code = 'R',
@BillingFormID = 13,
@SecondaryPrecedenceBillingFormID = 13,
@AcceptAssignment = 1,
@UseSecondaryElectronicBilling = 0,
@UseCoordinationOfBenefits = 1,
@ExcludePatientPayment = 0
END

END

--Validate Insurance Plan ID, or try to match exact plan anme or create a new record
SELECT @PlanID = InsuranceCompanyPlanID
FROM dbo.InsuranceCompanyPlan WITH ( NOLOCK )
WHERE InsuranceCompanyPlanID = @PlanID

IF @@ROWCOUNT = 0
SET @PlanID = NULL

IF @PlanID IS NULL
BEGIN
SELECT TOP 1
@PlanID = InsuranceCompanyPlanID
FROM dbo.InsuranceCompanyPlan AS IP WITH ( NOLOCK )
LEFT JOIN dbo.InsuranceCompany AS IC ON IP.InsuranceCompanyID = IC.InsuranceCompanyID
LEFT OUTER JOIN PracticeToInsuranceCompany PTICP ON PTICP.InsuranceCompanyID = IC.InsuranceCompanyID
AND PTICP.PracticeID = @PracticeID
WHERE PlanName = @PlanName
AND ( @CompanyID IS NULL
OR IP.InsuranceCompanyID = @CompanyID
)
AND ( ( COALESCE(IP.CreatedPracticeID,
0) = @PracticeID
AND COALESCE(IC.CreatedPracticeID,
0) = @PracticeID
)
OR -- [Plan and Company is created in that practice]
( IP.ReviewCode = 'R'
AND COALESCE(IC.CreatedPracticeID,
0) = @PracticeID
)
OR -- [Plan is for all practices and Company is created in that practice]
( IP.ReviewCode = 'R'
AND IC.ReviewCode = 'R'
)
OR -- [Plan and Company are for all practices]
( COALESCE(IP.CreatedPracticeID,
0) = @PracticeID
AND IC.ReviewCode = 'R'
)
) -- [Plan is created in that practice and Company is for all practices]
ORDER BY CASE WHEN ( COALESCE(IP.CreatedPracticeID,
0) = @PracticeID
AND COALESCE(IC.CreatedPracticeID,
0) = @PracticeID
) THEN 1
WHEN ( COALESCE(IP.CreatedPracticeID,
0) = @PracticeID
AND IC.ReviewCode = 'R'
) THEN 2
WHEN ( IP.ReviewCode = 'R'
AND COALESCE(IC.CreatedPracticeID,
0) = @PracticeID
) THEN 3
WHEN ( IP.ReviewCode = 'R'
AND IC.ReviewCode = 'R'
) THEN 4
END

IF @CompanyID IS NOT NULL
AND @PlanID IS NULL
AND @PlanName IS NOT NULL
BEGIN

EXEC @PlanID= dbo.InsurancePlanDataProvider_CreateInsurancePlan @company_id = @CompanyID,
@name = @PlanName,
@street_1 = @InsPlanAddressLine1,
@street_2 = @InsPlanAddressLine2,
@city = @InsPlanCity,
@state = @InsPlanState,
@country = @InsPlanCountry,
@zip = @InsPlanZipCode,
@contact_prefix = '',
@contact_first_name = '',
@contact_middle_name = '',
@contact_last_name = '',
@contact_suffix = '', @phone = '',
@phone_x = '', @fax = '',
@fax_x = '', @notes = '',
@practice_id = @PracticeID

END
END

--Get relationship code
SET @InsRelationShip = NULL

SELECT @InsRelationShip = RelationShip
FROM dbo.RelationShip WITH ( NOLOCK )
WHERE LongName = @InsuredPatientRelationshipToInsured

IF @InsRelationShip IS NULL
SELECT @InsRelationShip = RelationShip
FROM dbo.RelationShip WITH ( NOLOCK )
WHERE RelationShip = @InsuredPatientRelationshipToInsured

SET @InsRelationShip = ISNULL(@InsRelationShip, 'S')

--If Insurance Policy ID is passed, validate, else create if no such policy
SELECT @InsurancePolicyID = InsurancePolicyID
FROM dbo.InsurancePolicy WITH ( NOLOCK )
WHERE PracticeID = @PracticeID
AND InsurancePolicyID = @InsurancePolicyID

IF @@ROWCOUNT = 0
SET @InsurancePolicyID = NULL

IF @InsurancePolicyID IS NULL
AND @InsurancePolicyExternalID IS NOT NULL
AND @InsurancePolicyExternalID <> ''
AND @ExternalVendorID IS NOT NULL
SELECT @InsurancePolicyID = InsurancePolicyID
FROM dbo.ExternalIDToInsurancePolicyMap WITH ( NOLOCK )
WHERE ExternalVendorID = @ExternalVendorID
AND ExternalID = @InsurancePolicyExternalID

IF @InsurancePolicyID IS NULL
BEGIN

--Insert Insurance Policy record
IF @PlanID IS NOT NULL
BEGIN

SET @ActiveInsurance = ISNULL(@ActiveInsurance,
1)

EXEC @InsurancePolicyID= dbo.PatientDataProvider_CreateInsurancePolicy @CaseID,
@PracticeID, @PlanID,
@PolicyNumber, @PolicyGroupNumber,
@Copay, @Deductible,
@EffectiveStartDate,
@EffectiveEndDate, 0,
@InsRelationShip, @InsuredPrefix,
@InsuredFirstName,
@InsuredMiddleName,
@InsuredLastName, @InsuredSuffix,
@InsuredSocialSecurityNumber,
@InsuredDateofBirth,
@InsuredPolicyThroughEmployer,
@InsuredEmployer, @InsuredGender,
@InsuredAddressLine1,
@InsuredAddressLine2, @InsuredCity,
@InsuredState, @InsuredCountry,
@InsuredZipCode, NULL, NULL,
@InsuredIDNumber, @PolicyNotes,
@AdjusterPrefix,
@AdjusterFirstName,
@AdjusterMiddleName,
@AdjusterLastName, @AdjusterSuffix,
@AdjusterPhoneNumber,
@AdjusterPhoneNumberExt,
@AdjusterFaxNumber,
@AdjusterFaxNumberExt,
@ActiveInsurance, NULL, @userId,
@InsurancePolicyExternalID,
@ExternalVendorID

INSERT INTO #PoliciesAffected
( CaseNodeID ,
RID ,
PatientCaseID ,
InsurancePolicyID ,
InsuranceCompanyPlanID ,
ExternalID
)
SELECT @CaseNodeID ,
@PolicyStep ,
@CaseID ,
InsurancePolicyID ,
InsuranceCompanyPlanID ,
CASE WHEN @ExternalVendorID IS NULL
THEN NULL
ELSE @InsurancePolicyExternalID
END AS ExternalID
FROM dbo.InsurancePolicy IP ( NOLOCK )
WHERE InsurancePolicyID = @InsurancePolicyID

SET @PolicyInserted = 1
END
END
ELSE
BEGIN
SELECT @PlanID = COALESCE(@PlanID,
InsuranceCompanyPlanID) ,
@PolicyNumber = COALESCE(@PolicyNumber,
PolicyNumber) ,
@PolicyGroupNumber = COALESCE(@PolicyGroupNumber,
GroupNumber) ,
@Copay = COALESCE(@Copay, Copay) ,
@Deductible = COALESCE(@Deductible,
Deductible) ,
@EffectiveStartDate = COALESCE(@EffectiveStartDate,
PolicyStartDate) ,
@EffectiveEndDate = COALESCE(@EffectiveEndDate,
PolicyEndDate) ,
@CardOnFile = CardOnFile ,
@InsRelationShip = COALESCE(@InsRelationShip,
PatientRelationshipToInsured) ,
@InsuredPrefix = COALESCE(@InsuredPrefix,
HolderPrefix) ,
@InsuredFirstName = COALESCE(@InsuredFirstName,
HolderFirstName) ,
@InsuredMiddleName = COALESCE(@InsuredMiddleName,
HolderMiddleName) ,
@InsuredLastName = COALESCE(@InsuredLastName,
HolderLastName) ,
@InsuredSuffix = COALESCE(@InsuredSuffix,
HolderSuffix) ,
@InsuredDateofBirth = COALESCE(@InsuredDateofBirth,
HolderDOB) ,
@InsuredSocialSecurityNumber = COALESCE(@InsuredSocialSecurityNumber,
HolderSSN) ,
@InsuredPolicyThroughEmployer = COALESCE(@InsuredPolicyThroughEmployer,
HolderThroughEmployer) ,
@InsuredEmployer = COALESCE(@InsuredEmployer,
HolderEmployerName) ,
@InsuredGender = COALESCE(@InsuredGender,
HolderGender) ,
@InsuredAddressLine1 = COALESCE(@InsuredAddressLine1,
HolderAddressLine1) ,
@InsuredAddressLine2 = COALESCE(@InsuredAddressLine2,
HolderAddressLine2) ,
@InsuredCity = COALESCE(@InsuredCity,
HolderCity) ,
@InsuredState = COALESCE(@InsuredState,
HolderState) ,
@InsuredCountry = COALESCE(@InsuredCountry,
HolderCountry) ,
@InsuredZipCode = COALESCE(@InsuredZipCode,
HolderZipCode) ,
@InsuredPhone = HolderPhone ,
@InsuredPhoneExt = HolderPhoneExt ,
@InsuredIDNumber = COALESCE(@InsuredIDNumber,
DependentPolicyNumber) ,
@PolicyNotes = COALESCE(@PolicyNotes,
Notes) ,
@AdjusterPrefix = COALESCE(@AdjusterPrefix,
AdjusterPrefix) ,
@AdjusterFirstName = COALESCE(@AdjusterFirstName,
AdjusterFirstName) ,
@AdjusterMiddleName = COALESCE(@AdjusterMiddleName,
AdjusterMiddleName) ,
@AdjusterLastName = COALESCE(@AdjusterLastName,
AdjusterLastName) ,
@AdjusterSuffix = COALESCE(@AdjusterSuffix,
AdjusterSuffix) ,
@AdjusterPhoneNumber = COALESCE(@AdjusterPhoneNumber,
Phone) ,
@AdjusterPhoneNumberExt = COALESCE(@AdjusterPhoneNumberExt,
PhoneExt) ,
@AdjusterFaxNumber = COALESCE(@AdjusterFaxNumber,
Fax) ,
@AdjusterFaxNumberExt = COALESCE(@AdjusterFaxNumberExt,
FaxExt) ,
@ActiveInsurance = COALESCE(@ActiveInsurance,
Active) ,
@InsuranceProgramTypeID = InsuranceProgramTypeID ,
@userId = COALESCE(@userId,
ModifiedUserID) ,
@GroupName = GroupName ,
@ReleaseOfInformation = ReleaseOfInformation
FROM dbo.InsurancePolicy WITH ( NOLOCK )
WHERE InsurancePolicyID = @InsurancePolicyID

EXEC dbo.PatientDataProvider_UpdateInsurancePolicy @InsurancePolicyID,
@PlanID, @PracticeID, @CaseID,
@PolicyNumber, @PolicyGroupNumber, @Copay,
@Deductible, @EffectiveStartDate,
@EffectiveEndDate, @CardOnFile,
@InsRelationShip, @InsuredPrefix,
@InsuredFirstName, @InsuredMiddleName,
@InsuredLastName, @InsuredSuffix,
@InsuredSocialSecurityNumber,
@InsuredDateofBirth,
@InsuredPolicyThroughEmployer,
@InsuredEmployer, @InsuredGender,
@InsuredAddressLine1, @InsuredAddressLine2,
@InsuredCity, @InsuredState,
@InsuredCountry, @InsuredZipCode,
@InsuredPhone, @InsuredPhoneExt,
@InsuredIDNumber, @PolicyNotes,
@AdjusterPrefix, @AdjusterFirstName,
@AdjusterMiddleName, @AdjusterLastName,
@AdjusterSuffix, @AdjusterPhoneNumber,
@AdjusterPhoneNumberExt,
@AdjusterFaxNumber, @AdjusterFaxNumberExt,
@ActiveInsurance, @InsuranceProgramTypeID,
@userId, @GroupName, @ReleaseOfInformation

IF @ExternalVendorID IS NOT NULL
AND @InsurancePolicyExternalID IS NOT NULL
AND @InsurancePolicyExternalID <> ''
AND NOT EXISTS ( SELECT 1
FROM dbo.ExternalIDToInsurancePolicyMap
WITH ( NOLOCK )
WHERE ExternalVendorID = @ExternalVendorID
AND ExternalID = @InsurancePolicyExternalID
AND InsurancePolicyID = @InsurancePolicyID )
BEGIN
UPDATE eim
SET ExternalID = @InsurancePolicyExternalID
FROM dbo.ExternalIDToInsurancePolicyMap eim ( ROWLOCK )
WHERE ExternalVendorID = @ExternalVendorID
AND InsurancePolicyID = @InsurancePolicyID

IF @@ROWCOUNT = 0
INSERT INTO dbo.ExternalIDToInsurancePolicyMap
( ExternalVendorID ,
ExternalID ,
InsurancePolicyID
)
VALUES ( @ExternalVendorID ,
@InsurancePolicyExternalID ,
@InsurancePolicyID
)
END

INSERT INTO #PoliciesAffected
( CaseNodeID ,
RID ,
PatientCaseID ,
InsurancePolicyID ,
InsuranceCompanyPlanID ,
ExternalID
)
SELECT @CaseNodeID ,
@PolicyStep ,
@CaseID ,
InsurancePolicyID ,
InsuranceCompanyPlanID ,
CASE WHEN @ExternalVendorID IS NULL
THEN NULL
ELSE @InsurancePolicyExternalID
END AS ExternalID
FROM dbo.InsurancePolicy IP ( NOLOCK )
WHERE InsurancePolicyID = @InsurancePolicyID

SET @PolicyUpdated = 1
END

IF @Precedence IS NOT NULL
AND @Precedence > 0
BEGIN
SELECT @MaxPrecedence = MAX(Precedence) ,
@CurrentPrecedence = MAX(CASE
WHEN InsurancePolicyID = @InsurancePolicyID
THEN Precedence
ELSE NULL
END)
FROM dbo.InsurancePolicy WITH ( NOLOCK )
WHERE PatientCaseID = @CaseID

IF @CurrentPrecedence <> @Precedence
BEGIN
--Check what direction precedence is being changed to
IF @CurrentPrecedence - @Precedence < 0 --Precedence is moving up
BEGIN

UPDATE IP
SET Precedence = @Precedence
+ 1000000
FROM dbo.InsurancePolicy IP
WITH ( ROWLOCK,
UPDLOCK )
WHERE PatientCaseID = @CaseID
AND InsurancePolicyID = @InsurancePolicyID

UPDATE IP
SET Precedence = Precedence
- 1
FROM dbo.InsurancePolicy IP
WITH ( ROWLOCK,
UPDLOCK )
WHERE PatientCaseID = @CaseID
AND Precedence BETWEEN @CurrentPrecedence
AND
@Precedence

UPDATE IP
SET Precedence = @Precedence
FROM dbo.InsurancePolicy IP
WHERE PatientCaseID = @CaseID
AND InsurancePolicyID = @InsurancePolicyID

END
ELSE -- Precedence is moving down
BEGIN

UPDATE IP
SET Precedence = @Precedence
+ 1000000
FROM dbo.InsurancePolicy IP
WITH ( ROWLOCK,
UPDLOCK )
WHERE PatientCaseID = @CaseID
AND InsurancePolicyID = @InsurancePolicyID

UPDATE IP
SET Precedence = Precedence
+ 1
FROM dbo.InsurancePolicy IP
WITH ( ROWLOCK,
UPDLOCK )
WHERE PatientCaseID = @CaseID
AND Precedence BETWEEN @Precedence
AND
@CurrentPrecedence

UPDATE IP
SET Precedence = CASE
WHEN @Precedence > @MaxPrecedence
THEN @MaxPrecedence
ELSE @Precedence
END
FROM dbo.InsurancePolicy IP
WHERE PatientCaseID = @CaseID
AND InsurancePolicyID = @InsurancePolicyID

END
END
END

IF NOT EXISTS ( SELECT 1
FROM #PoliciesAffected
WHERE CaseNodeID = @CaseNodeID
AND RID = @PolicyStep )
INSERT INTO #PoliciesAffected
( CaseNodeID ,
RID ,
PatientCaseID ,
ExternalID
)
VALUES ( @CaseNodeID ,
@PolicyStep ,
@CaseID ,
CASE WHEN @ExternalVendorID IS NULL
THEN NULL
ELSE @InsurancePolicyExternalID
END
)

END

-- If policies were only inserted and not updated go ahead and deactivate the previously existing policies
IF @PolicyUpdated = 0
AND @PolicyInserted = 1
AND EXISTS ( SELECT 1
FROM #ExistingPolicies )
UPDATE IP
SET Active = 0
FROM dbo.InsurancePolicy IP WITH ( ROWLOCK, UPDLOCK )
WHERE InsurancePolicyID IN ( SELECT InsurancePolicyID
FROM #ExistingPolicies )

SELECT @AuthoCount = MAX(A.TID)
FROM #AuthoCaseMap ACM
INNER JOIN #Authorization A ON ACM.AuthosNodeID = A.parentNodeID
WHERE ACM.CaseNodeID = @CaseNodeID

WHILE @AuthoStep < @AuthoCount
AND @CaseID IS NOT NULL
BEGIN
SET @AuthoStep = @AuthoStep + 1

SELECT @AuthorizationID = dbo.KAPI_fn_ConvertToINT(AuthorizationID,
0, NULL) ,
@AuthoInsurancePolicyID = dbo.KAPI_fn_ConvertToINT(InsurancePolicyID,
0, NULL) ,
@AuthoInsurancePlanID = dbo.KAPI_fn_ConvertToINT(InsurancePlanID,
0, NULL) ,
@AuthoInsurancePlanName = InsurancePlanName ,
@AuthoNumber = Number ,
@AuthoNumberOfVisits = dbo.KAPI_fn_ConvertToINT(NumberOfVisits,
1, NULL) ,
@AuthoContactFullName = ContactFullName ,
@AuthoContactPhone = ContactPhone ,
@AuthoContactPhoneExt = ContactPhoneExt ,
@AuthoNotes = Notes ,
@AuthoStartDate = dbo.KAPI_fn_ConvertToDateTime(StartDate,
0, NULL) ,
@AuthoEndDate = dbo.KAPI_fn_ConvertToDateTime(EndDate,
0, NULL)
FROM #Authorization
WHERE TID = @AuthoStep

IF @AuthorizationID IS NOT NULL
BEGIN
--Attempt to Validate ID
SELECT @AuthorizationID = InsurancePolicyAuthorizationID
FROM dbo.InsurancePolicyAuthorization IPA ( NOLOCK )
INNER JOIN dbo.InsurancePolicy IP ( NOLOCK ) ON IPA.InsurancePolicyID = IP.InsurancePolicyID
WHERE IPA.InsurancePolicyAuthorizationID = @AuthorizationID
AND IP.PatientCaseID = @CaseID
AND IP.PracticeID = @PracticeID
AND IP.Active = 1
END

IF @@ROWCOUNT = 0
SET @AuthorizationID = NULL

IF @AuthorizationID IS NULL
AND @AuthoNumber IS NOT NULL
BEGIN
--Attempt to Match up an existing Authorization
SELECT @AuthorizationID = InsurancePolicyAuthorizationID
FROM dbo.InsurancePolicyAuthorization IPA ( NOLOCK )
INNER JOIN dbo.InsurancePolicy IP ( NOLOCK ) ON IPA.InsurancePolicyID = IP.InsurancePolicyID
WHERE IP.PatientCaseID = @CaseID
AND IP.PracticeID = @PracticeID
AND IP.Active = 1
AND ( AuthorizationNumber = @AuthoNumber
AND ( @AuthoInsurancePolicyID IS NULL
OR IP.InsurancePolicyID = @AuthoInsurancePolicyID
)
)
END

IF @AuthorizationID IS NULL
BEGIN
--Attempt to match up an insurance policy ID in order to create new Autho

IF @AuthoInsurancePolicyID IS NOT NULL
BEGIN
SELECT @AuthoInsurancePolicyID = InsurancePolicyID
FROM dbo.InsurancePolicy IP ( NOLOCK )
WHERE IP.InsurancePolicyID = @AuthoInsurancePolicyID
AND IP.PatientCaseID = @CaseID
AND IP.PracticeID = @PracticeID
AND IP.Active = 1

IF @@ROWCOUNT = 0
SET @AuthoInsurancePolicyID = NULL
END

IF @AuthoInsurancePolicyID IS NULL
BEGIN
--Validate Insurance Plan ID, or try to match exact plan name
SELECT @AuthoInsurancePlanID = InsuranceCompanyPlanID
FROM dbo.InsuranceCompanyPlan WITH ( NOLOCK )
WHERE InsuranceCompanyPlanID = @AuthoInsurancePlanID

IF @@ROWCOUNT = 0
SET @AuthoInsurancePlanID = NULL

IF @AuthoInsurancePlanID IS NULL
AND @AuthoInsurancePlanName IS NOT NULL
BEGIN
SELECT @AuthoInsurancePolicyID = IP.InsurancePolicyID
FROM dbo.InsurancePolicy IP ( NOLOCK )
INNER JOIN dbo.InsuranceCompanyPlan ICP ( NOLOCK ) ON IP.InsuranceCompanyPlanID = ICP.InsuranceCompanyPlanID
WHERE IP.PatientCaseID = @CaseID
AND IP.PracticeID = @PracticeID
AND IP.Active = 1
AND PlanName = @AuthoInsurancePlanName
END
END

IF @AuthoInsurancePlanID IS NOT NULL
AND @AuthoInsurancePolicyID IS NULL
BEGIN
SELECT TOP 1
@AuthoInsurancePolicyID = IP.InsurancePolicyID
FROM dbo.InsurancePolicy IP ( NOLOCK )
WHERE IP.PatientCaseID = @CaseID
AND IP.PracticeID = @PracticeID
AND IP.InsuranceCompanyPlanID = @AuthoInsurancePlanID
AND IP.Active = 1
END

IF @AuthoInsurancePolicyID IS NOT NULL
AND @AuthoNumber IS NOT NULL
AND @AuthoNumberOfVisits IS NOT NULL
BEGIN

EXEC @AuthorizationID= dbo.PatientDataProvider_CreateInsurancePolicyAuthorization @AuthoInsurancePolicyID,
@AuthoNumber, @AuthoNumberOfVisits,
@AuthoStartDate, @AuthoEndDate,
@AuthoContactFullName,
@AuthoContactPhone,
@AuthoContactPhoneExt, @AuthoNotes,
@userId

INSERT INTO #AuthosAffected
( CaseNodeID ,
RID ,
PatientCaseID ,
AuthorizationID ,
InsurancePolicyID
)
SELECT @CaseNodeID ,
@AuthoStep ,
@CaseID ,
InsurancePolicyAuthorizationID ,
InsurancePolicyID
FROM dbo.InsurancePolicyAuthorization
WITH ( NOLOCK )
WHERE InsurancePolicyAuthorizationID = @AuthorizationID

END

END
ELSE
BEGIN
SELECT @AuthoInsurancePolicyID = COALESCE(@AuthoInsurancePolicyID,
InsurancePolicyID) ,
@AuthoNumber = COALESCE(@AuthoNumber,
AuthorizationNumber) ,
@AuthoNumberOfVisits = COALESCE(@AuthoNumberOfVisits,
AuthorizedNumberOfVisits) ,
@AuthoStartDate = COALESCE(@AuthoStartDate,
StartDate) ,
@AuthoEndDate = COALESCE(@AuthoEndDate,
EndDate) ,
@AuthoContactFullName = COALESCE(@AuthoContactFullName,
ContactFullname) ,
@AuthoContactPhone = COALESCE(@AuthoContactPhone,
ContactPhone) ,
@AuthoContactPhoneExt = COALESCE(@AuthoContactPhoneExt,
ContactPhoneExt) ,
@AuthoNotes = COALESCE(@AuthoNotes,
Notes)
FROM dbo.InsurancePolicyAuthorization WITH ( NOLOCK )
WHERE InsurancePolicyAuthorizationId = @AuthorizationID

EXEC dbo.PatientDataProvider_UpdateInsurancePolicyAuthorization @insurance_policy_authorization_id = @AuthorizationID,
@insurance_policy_id = @AuthoInsurancePolicyID,
@authorization_number = @AuthoNumber,
@authorized_visits = @AuthoNumberOfVisits,
@start_date = @AuthoStartDate,
@end_date = @AuthoEndDate,
@contact_full_name = @AuthoContactFullName,
@contact_phone = @AuthoContactPhone,
@contact_phone_x = @AuthoContactPhoneExt,
@notes = @AuthoNotes,
@modified_user_id = @userId

INSERT INTO #AuthosAffected
( CaseNodeID ,
RID ,
PatientCaseID ,
AuthorizationID ,
InsurancePolicyID
)
SELECT @CaseNodeID ,
@AuthoStep ,
@CaseID ,
InsurancePolicyAuthorizationID ,
InsurancePolicyID
FROM dbo.InsurancePolicyAuthorization
WITH ( NOLOCK )
WHERE InsurancePolicyAuthorizationID = @AuthorizationID
END

--If No Autho was affected indicate so by supplying an empty result set
IF NOT EXISTS ( SELECT 1
FROM #AuthosAffected
WHERE CaseNodeID = @CaseNodeID
AND RID = @AuthoStep )
INSERT INTO #AuthosAffected
( CaseNodeID, RID, PatientCaseID )
VALUES ( @CaseNodeID, @AuthoStep, @CaseID )
END
END

--Validate Alert Data and insert if valid
DECLARE @AlertMessage VARCHAR(MAX) ,
@ShowWhenDisplayingPatientDetails BIT ,
@ShowWhenSchedulingAppointments BIT ,
@ShowWhenEnteringEncounters BIT ,
@ShowWhenViewingClaimDetails BIT ,
@ShowWhenPostingPayments BIT ,
@ShowWhenPreparingPatientStatements BIT

SELECT @AlertMessage = [Message] ,
@ShowWhenDisplayingPatientDetails = dbo.KAPI_fn_ConvertToBIT(ShowWhenDisplayingPatientDetails) ,
@ShowWhenSchedulingAppointments = dbo.KAPI_fn_ConvertToBIT(ShowWhenSchedulingAppointments) ,
@ShowWhenEnteringEncounters = dbo.KAPI_fn_ConvertToBIT(ShowWhenEnteringEncounters) ,
@ShowWhenViewingClaimDetails = dbo.KAPI_fn_ConvertToBIT(ShowWhenViewingClaimDetails) ,
@ShowWhenPostingPayments = dbo.KAPI_fn_ConvertToBIT(ShowWhenPostingPayments) ,
@ShowWhenPreparingPatientStatements = dbo.KAPI_fn_ConvertToBIT(ShowWhenPreparingPatientStatements)
FROM #Alert

IF @AlertMessage IS NOT NULL
OR @ShowWhenDisplayingPatientDetails IS NOT NULL
OR @ShowWhenSchedulingAppointments IS NOT NULL
OR @ShowWhenEnteringEncounters IS NOT NULL
OR @ShowWhenViewingClaimDetails IS NOT NULL
OR @ShowWhenPostingPayments IS NOT NULL
OR @ShowWhenPreparingPatientStatements IS NOT NULL
BEGIN
IF NOT EXISTS ( SELECT 1
FROM dbo.PatientAlert WITH ( NOLOCK )
WHERE PatientID = @PatientID )
BEGIN
EXEC dbo.PatientDataProvider_CreatePatientAlert @PatientID,
@AlertMessage, @ShowWhenDisplayingPatientDetails,
@ShowWhenSchedulingAppointments,
@ShowWhenEnteringEncounters,
@ShowWhenViewingClaimDetails,
@ShowWhenPostingPayments,
@ShowWhenPreparingPatientStatements, @userId,
@userId
END
ELSE
BEGIN
DECLARE @PatientAlertID INT
SELECT @PatientAlertID = PatientAlertID ,
@AlertMessage = COALESCE(@AlertMessage,
AlertMessage) ,
@ShowWhenDisplayingPatientDetails = COALESCE(@ShowWhenDisplayingPatientDetails,
ShowInPatientFlag) ,
@ShowWhenSchedulingAppointments = COALESCE(@ShowWhenSchedulingAppointments,
ShowInAppointmentFlag) ,
@ShowWhenEnteringEncounters = COALESCE(@ShowWhenEnteringEncounters,
ShowInEncounterFlag) ,
@ShowWhenViewingClaimDetails = COALESCE(@ShowWhenViewingClaimDetails,
ShowInClaimFlag) ,
@ShowWhenPostingPayments = COALESCE(@ShowWhenPostingPayments,
ShowInPaymentFlag) ,
@ShowWhenPreparingPatientStatements = COALESCE(@ShowWhenPreparingPatientStatements,
ShowInPatientStatementFlag)
FROM dbo.PatientAlert WITH ( NOLOCK )
WHERE PatientID = @PatientID

EXEC dbo.PatientDataProvider_UpdatePatientAlert @PatientAlertID,
@PatientID, @AlertMessage,
@ShowWhenDisplayingPatientDetails,
@ShowWhenSchedulingAppointments,
@ShowWhenEnteringEncounters,
@ShowWhenViewingClaimDetails,
@ShowWhenPostingPayments,
@ShowWhenPreparingPatientStatements, @userId
END

END

--Make sure we try to feedback External IDs, even if they were not supplied
--this step takes a lookup against Kareo records for Patient, Practice, PatientCase, and InsurancPolicy
IF @PatientExternalID IS NULL
AND @ExternalVendorID IS NOT NULL
SELECT @PatientExternalID = ExternalID
FROM dbo.ExternalIDToPatientMap WITH ( NOLOCK )
WHERE ExternalVendorID = @ExternalVendorID
AND PatientID = @PatientID

IF @PracticeExternalID IS NULL
AND @ExternalVendorID IS NOT NULL
SELECT @PracticeExternalID = ExternalID
FROM dbo.ExternalIDToPracticeMap WITH ( NOLOCK )
WHERE ExternalVendorID = @ExternalVendorID
AND PracticeID = @PracticeID ;

IF EXISTS ( SELECT 1
FROM #CasesAffected
WHERE ExternalID IS NULL )
AND @ExternalVendorID IS NOT NULL
BEGIN
UPDATE CA
SET ExternalID = PC.ExternalID
FROM #CasesAffected CA
INNER JOIN dbo.ExternalIDToPatientCaseMap PC ( NOLOCK ) ON PC.ExternalVendorID = @ExternalVendorID
AND CA.PatientCaseID = PC.PatientCaseID
WHERE CA.ExternalID IS NULL ;
END

IF EXISTS ( SELECT 1
FROM #PoliciesAffected
WHERE ExternalID IS NULL )
AND @ExternalVendorID IS NOT NULL
BEGIN
UPDATE PA
SET ExternalID = IP.ExternalID
FROM #PoliciesAffected PA
INNER JOIN dbo.ExternalIDToInsurancePolicyMap IP ( NOLOCK ) ON IP.ExternalVendorID = @ExternalVendorID
AND PA.InsurancePolicyID = IP.InsurancePolicyID
WHERE PA.ExternalID IS NULL ;
END

--Return General Patient Info
SELECT @PatientID AS PatientID ,
@ExternalVendorID AS ExternalVendorID ,
@PatientExternalID AS PatientExternalID ,
@PracticeID AS PracticeID ,
@PracticeExternalID AS PracticeExternalID ,
@PracticeName AS PracticeName ,
@EmployerID AS EmployerID ,
@LocationID AS DefaultServiceLocationID ;

--Return Case Structures (i.e. Case, Policies, Authos)
SELECT ( SELECT ISNULL(CAST(CA.PatientCaseID AS VARCHAR(20)), '') AS 'CaseID' ,
ISNULL(CA.ExternalID, '') AS 'CaseExternalID' ,
( SELECT ISNULL(CAST(PA.InsurancePolicyID AS VARCHAR(20)),
'') AS InsurancePolicyID ,
ISNULL(CAST(PA.InsuranceCompanyPlanID AS VARCHAR(20)),
'') AS InsurancePolicyPlanID ,
ISNULL(CAST(ICP.InsuranceCompanyID AS VARCHAR(20)),
'') AS InsurancePolicyCompanyID ,
ISNULL(PA.ExternalID, '') AS InsurancePolicyExternalID
FROM #PoliciesAffected PA
LEFT JOIN dbo.InsuranceCompanyPlan ICP ( NOLOCK ) ON PA.InsuranceCompanyPlanID = ICP.InsuranceCompanyPlanID
WHERE PA.CaseNodeID = CA.CaseNodeID
ORDER BY PA.RID
FOR
XML PATH('Policy') ,
TYPE
) AS Policies ,
( SELECT AA.AuthorizationID ,
AA.InsurancePolicyID
FROM #AuthosAffected AA
WHERE AA.CaseNodeID = CA.CaseNodeID
ORDER BY AA.RID
FOR
XML PATH('Authorization') ,
TYPE
) AS Authorizations
FROM #CasesAffected CA
ORDER BY CA.CaseNodeID
FOR
XML PATH('Case') ,
ROOT('Cases') ,
TYPE
) AS CaseResults ;

GOTO CLEANUP ;

ON_MANUAL_ERROR:

SELECT NULL PatientID ,
@ExternalVendorID AS ExternalVendorID ,
@PatientExternalID AS PatientExternalID ,
NULL PracticeID ,
@PracticeExternalID AS PracticeExternalID ,
NULL PracticeName ,
NULL EmployerID ,
NULL DefaultServiceLocationID ;

SELECT NULL CaseResults ;

SELECT @updatePatientInfo errors ;

IF @@TRANCOUNT > 0
ROLLBACK TRAN ;

GOTO CLEANUP ;

CLEANUP:

--DROP TABLE #GeneralInfo
--DROP TABLE #Location
--DROP TABLE #Cases
--DROP TABLE #Insurance
--DROP TABLE #Authorization
--DROP TABLE #Alert
--DROP TABLE #InsuranceCaseMap
--DROP TABLE #AuthoCaseMap

--DROP TABLE #CasesAffected
--DROP TABLE #PoliciesAffected
--DROP TABLE #AuthosAffected
--DROP TABLE #ExistingPolicies

END

Post #1427498
Posted Wednesday, March 6, 2013 12:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1427588
Posted Wednesday, March 6, 2013 12:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:05 AM
Points: 1,069, Visits: 421
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


Post #1427598
Posted Wednesday, March 6, 2013 1:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1427605
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse