DBCC TRACEON(1222,-1)

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

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

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

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