Resolving a deadlock

  • 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

  • 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

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Here is the xml and the actual graph is attached. I put the xml into the analysis script which is how I came up with the deadlock being between the trigger and the update statement.

    <deadlock-list><deadlock victim="processf945c8"><process-list><process id="process32e868" taskpriority="0" logused="516" waitresource="KEY: 348:1438969838829568 (6a00b35de28b)" waittime="3931" ownerId="1102032551" transactionname="UPDATE" lasttranstarted="2013-03-01T10:39:03.790" XDES="0x1000996700" lockMode="U" schedulerid="7" kpid="18796" status="suspended" spid="267" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-03-01T10:39:03.760" lastbatchcompleted="2013-03-01T10:39:03.760" clientapp=".Net SqlClient Data Provider" hostname="KPROD-API01" hostpid="5592" loginname="dev" isolationlevel="read committed (2)" xactid="1102032551" currentdb="348" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056"><executionStack><frame procname="superbill_5621_prod.dbo.tr_IU_Patient_ChangeTime" line="20" stmtstart="1136" stmtend="1422" sqlhandle="0x03005c01f90a702aee0870017f9d00000000000000000000">

    UPDATE P

    SET DOB = dbo.fn_ReplaceTimeInDate(i.DOB)

    FROM dbo.Patient P INNER JOIN

    inserted i ON

    P.PatientID = i.PatientID </frame><frame procname="superbill_5621_prod.dbo.PatientDataProvider_UpdatePatient" line="58" stmtstart="3208" stmtend="5998" sqlhandle="0x03005c01423a25111b997c01fa9f00000100000000000000">

    UPDATEdbo.Patient

    SETPrefix = @prefix,

    FirstName = @first_name,

    MiddleName = @middle_name,

    LastName = @last_name,

    Suffix = @suffix,

    AddressLine1 = @address_1,

    AddressLine2 = @address_2,

    City = @city,

    State = @state,

    Country = @country,

    ZipCode = @zip,

    Gender = @gender,

    MaritalStatus = @marital_status,

    HomePhone = @home_phone,

    HomePhoneExt = @home_phone_x,

    WorkPhone = @work_phone,

    WorkPhoneExt = @work_phone_x,

    DOB = @dob,

    SSN = @ssn,

    EmailAddress = RTRIM(LTRIM(@email)),

    SendEmailCorrespondence = @sendEmailCorrespondence,

    ResponsibleDifferentThanPatient = @guarantor_different,

    EmploymentStatus = @employment_status,

    ReferringPhysicianID = @referrer_id,

    PrimaryProviderID = @primary_provider_id,

    PatientReferralSourceID = @patient_referral_source_id,

    DefaultServiceLocationID = @patient_default_location_id,

    EmployerID = @employer_id,

    MedicalRecordNumber = @medical_record_number,

    MobilePhone = @mobile_phone,

    MobilePhoneExt = </frame><frame procname="superbill_5621_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1720" stmtstart="176520" stmtend="178404" sqlhandle="0x03005c010ecaf473e772b2006aa100000100000000000000">

    EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix,

    @PatientFirstName, @PatientMiddleName, @PatientLastName,

    @PatientSuffix, @PatientAddressLine1, @PatientAddressLine2,

    @PatientCity, @PatientState, @PatientCountry, @PatientZipCode,

    @PatientGender, @PatientMaritalStatus, @PatientHomePhone,

    @PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt,

    @PatientDateOfBirth, @PatientSSN, @PatientEmail,

    @PatientEmailCorrespondence, @GuarantorDifferentThanPatient,

    @EmploymentStatus, @ReferringProviderID,

    @DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID,

    @EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone,

    @PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userid, @active,

    0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt </frame></executionStack><inputbuf>

    Proc [Database Id = 348 Object Id = 1945422350] </inputbuf></process><process id="processf945c8" taskpriority="0" logused="0" waitresource="KEY: 348:72057594105298944 (6b00585d2e03)" waittime="3946" ownerId="1102032550" transactionname="UPDATE" lasttranstarted="2013-03-01T10:39:03.790" XDES="0x56a3d8370" lockMode="U" schedulerid="3" kpid="19368" status="suspended" spid="225" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-03-01T10:39:03.757" lastbatchcompleted="2013-03-01T10:39:03.757" clientapp=".Net SqlClient Data Provider" hostname="KPROD-API02" hostpid="5432" loginname="dev" isolationlevel="read committed (2)" xactid="1102032550" currentdb="348" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"><executionStack><frame procname="superbill_5621_prod.dbo.PatientDataProvider_UpdatePatient" line="58" stmtstart="3208" stmtend="5998" sqlhandle="0x03005c01423a25111b997c01fa9f00000100000000000000">

    UPDATEdbo.Patient

    SETPrefix = @prefix,

    FirstName = @first_name,

    MiddleName = @middle_name,

    LastName = @last_name,

    Suffix = @suffix,

    AddressLine1 = @address_1,

    AddressLine2 = @address_2,

    City = @city,

    State = @state,

    Country = @country,

    ZipCode = @zip,

    Gender = @gender,

    MaritalStatus = @marital_status,

    HomePhone = @home_phone,

    HomePhoneExt = @home_phone_x,

    WorkPhone = @work_phone,

    WorkPhoneExt = @work_phone_x,

    DOB = @dob,

    SSN = @ssn,

    EmailAddress = RTRIM(LTRIM(@email)),

    SendEmailCorrespondence = @sendEmailCorrespondence,

    ResponsibleDifferentThanPatient = @guarantor_different,

    EmploymentStatus = @employment_status,

    ReferringPhysicianID = @referrer_id,

    PrimaryProviderID = @primary_provider_id,

    PatientReferralSourceID = @patient_referral_source_id,

    DefaultServiceLocationID = @patient_default_location_id,

    EmployerID = @employer_id,

    MedicalRecordNumber = @medical_record_number,

    MobilePhone = @mobile_phone,

    MobilePhoneExt = </frame><frame procname="superbill_5621_prod.dbo.WebServiceDataProvider_UpdatePatient" line="1720" stmtstart="176520" stmtend="178404" sqlhandle="0x03005c010ecaf473e772b2006aa100000100000000000000">

    EXEC dbo.PatientDataProvider_UpdatePatient @PatientID, @PatientPrefix,

    @PatientFirstName, @PatientMiddleName, @PatientLastName,

    @PatientSuffix, @PatientAddressLine1, @PatientAddressLine2,

    @PatientCity, @PatientState, @PatientCountry, @PatientZipCode,

    @PatientGender, @PatientMaritalStatus, @PatientHomePhone,

    @PatientHomePhoneExt, @PatientWorkPhone, @PatientWorkPhoneExt,

    @PatientDateOfBirth, @PatientSSN, @PatientEmail,

    @PatientEmailCorrespondence, @GuarantorDifferentThanPatient,

    @EmploymentStatus, @ReferringProviderID,

    @DefaultRenderingProviderID, @PatientReferralSourceID, @LocationID,

    @EmployerID, @PatientMedicalRecordNumber, @PatientMobilePhone,

    @PatientMobilePhoneExt, @PrimaryCarePhysicianID, @userid, @active,

    0, @EmergencyName, @EmergencyPhone, @EmergencyPhoneExt </frame></executionStack><inputbuf>

    Proc [Database Id = 348 Object Id = 1945422350] </inputbuf></process></process-list><resource-list><keylock hobtid="72057594105298944" dbid="348" objectname="superbill_5621_prod.dbo.Patient" indexname="CI_Patient_PracticeID_PatientID" id="lock1dcda6980" mode="X" associatedObjectId="72057594105298944"><owner-list><owner id="process32e868" mode="X"/></owner-list><waiter-list><waiter id="processf945c8" mode="U" requestType="wait"/></waiter-list></keylock><keylock hobtid="1438969838829568" dbid="348" objectname="superbill_5621_prod.dbo.Patient" indexname="PK_Patient" id="lock67c1bce80" mode="U" associatedObjectId="1438969838829568"><owner-list><owner id="processf945c8" mode="U"/></owner-list><waiter-list><waiter id="process32e868" mode="U" requestType="wait"/></waiter-list></keylock></resource-list></deadlock></deadlock-list>

  • 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

  • 1) http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx. Note there are 2 additional parts to this blog series.

    2) With such a complex construct (2000 line sproc, trigger(s) in play, etc) you may be best served by getting a professional on board to assist you. You could spend days going back and forth on this forum or wind up with a solution that causes other problems.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • 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

  • 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

  • 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

  • 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

  • My vote is for the huge covering index being the culprit here...

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • These are the two indexes involved in the deadlock:

    CREATE UNIQUE CLUSTERED INDEX [CI_Patient_PracticeID_PatientID] ON [dbo].[Patient]

    (

    [PracticeID] ASC,

    [PatientID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Patient]...

    ...

    CONSTRAINT [PK_Patient] PRIMARY KEY NONCLUSTERED

    (

    [PatientID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • One of my thoughts on the indexes was to Make the Primary Key PK_Patient the Clustered index and then make the Practice_patientid index only a nonclustered index on the practiceID.

  • Sorry About that here is the code:

    CREATE PROCEDURE dbo.PatientDataProvider_UpdatePatient

    @patient_id INT,

    @prefix VARCHAR(16),

    @first_name VARCHAR(32),

    @middle_name VARCHAR(32),

    @last_name VARCHAR(32),

    @suffix VARCHAR(16),

    @address_1 VARCHAR(128) = NULL,

    @address_2 VARCHAR(128) = NULL,

    @city VARCHAR(32) = NULL,

    @state VARCHAR(2) = NULL,

    @country VARCHAR(32) = NULL,

    @zip VARCHAR(9) = NULL,

    @gender VARCHAR(1) = 'U',

    @marital_status VARCHAR(1) = 'U',

    @home_phone VARCHAR(10) = NULL,

    @home_phone_x VARCHAR(10) = NULL,

    @work_phone VARCHAR(10) = NULL,

    @work_phone_x VARCHAR(10) = NULL,

    @dob DATETIME = NULL,

    @ssn CHAR(9) = NULL,

    @email VARCHAR(128) = NULL,

    @sendEmailCorrespondence BIT = 1,

    @guarantor_different BIT = 0,

    @employment_status CHAR(1) = NULL,

    @referrer_id INT = NULL,

    @primary_provider_id INT = NULL,

    @patient_referral_source_id INT = NULL,

    @patient_default_location_id INT = NULL,

    @employer_id INT = NULL,

    @medical_record_number VARCHAR(128) = NULL,

    @mobile_phone VARCHAR(10) = NULL,

    @mobile_phone_x VARCHAR(10) = NULL,

    @primary_care_physician_id INT = NULL,

    @modified_user_id INT = 0,

    @active BIT,

    @PhonecallRemindersEnabled BIT = 0,

    @EmergencyName VARCHAR(128) = NULL,

    @EmergencyPhone VARCHAR(10) = NULL,

    @EmergencyPhoneExt VARCHAR(10) = NULL

    AS

    BEGIN

    IF @email IS NULL OR LTRIM(@email) = ''

    BEGIN

    SET @sendEmailCorrespondence = 0 ;

    END

    UPDATEdbo.Patient

    SETPrefix = @prefix,

    FirstName = @first_name,

    MiddleName = @middle_name,

    LastName = @last_name,

    Suffix = @suffix,

    AddressLine1 = @address_1,

    AddressLine2 = @address_2,

    City = @city,

    State = @state,

    Country = @country,

    ZipCode = @zip,

    Gender = @gender,

    MaritalStatus = @marital_status,

    HomePhone = @home_phone,

    HomePhoneExt = @home_phone_x,

    WorkPhone = @work_phone,

    WorkPhoneExt = @work_phone_x,

    DOB = @dob,

    SSN = @ssn,

    EmailAddress = RTRIM(LTRIM(@email)),

    SendEmailCorrespondence = @sendEmailCorrespondence,

    ResponsibleDifferentThanPatient = @guarantor_different,

    EmploymentStatus = @employment_status,

    ReferringPhysicianID = @referrer_id,

    PrimaryProviderID = @primary_provider_id,

    PatientReferralSourceID = @patient_referral_source_id,

    DefaultServiceLocationID = @patient_default_location_id,

    EmployerID = @employer_id,

    MedicalRecordNumber = @medical_record_number,

    MobilePhone = @mobile_phone,

    MobilePhoneExt = @mobile_phone_x,

    PrimaryCarePhysicianID = @primary_care_physician_id,

    ModifiedDate = GETDATE(),

    ModifiedUserID = @modified_user_id,

    Active = @Active,

    PhonecallRemindersEnabled = @PhonecallRemindersEnabled,

    EmergencyName = @EmergencyName,

    EmergencyPhone = @EmergencyPhone,

    EmergencyPhoneExt = @EmergencyPhoneExt

    WHEREPatientID = @patient_id ;

    END

  • So, you update the DOB with the sproc UPDATE statement, and then the trigger updates the DOB too (after removing the time)? Uh, maybe just strip off the time before you do the sproc UPDATE and eliminate the trigger?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply