'This is an example VB .NET source code for translating an EDI X12 837X098 Professional using the Framework EDI component. 'The complete sample program can be downloaded from our HIPAA EDI Support webpage at http://www.edidev.com/example_hipaa.html Private Sub cmdTranslate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTranslate.Click Dim oEdiDoc As ediDocument = Nothing Dim oSchemas As ediSchemas = Nothing Dim oSegment As ediDataSegment = Nothing Dim sValue As String Dim sEntity As String = "" Dim sQlfr As String = "" Dim sNm1Qlfr As String = "" Dim nLineCount As Integer = 0 Dim sSefFile As String = "" Dim sEdiFile As String = "" Dim sSegmentID As String = "" Dim nArea As Integer = 0 Dim sLoopSection As String = "" Dim sSql As String = "" Me.Cursor = Cursors.WaitCursor sPath = AppDomain.CurrentDomain.BaseDirectory sSefFile = "837_X098A1.EVAL0.SEF" 'Evaluation SEF files sEdiFile = "837.x12" ediDocument.Set(oEdiDoc, New ediDocument) oEdiDoc.CursorType = DocumentCursorTypeConstants.Cursor_ForwardOnly ediSchemas.Set(oSchemas, oEdiDoc.GetSchemas) oSchemas.EnableStandardReference = False oEdiDoc.LoadSchema(sPath & sSefFile, 0) oEdiDoc.LoadEdi(txtEdiFile.Text) 'open connection to access database oConnection.Open() 'Get the firs data segment in the EDI file ediDataSegment.Set(oSegment, oEdiDoc.FirstDataSegment) 'Iterate through all segments in the EDI file While Not oSegment Is Nothing sSegmentID = oSegment.ID nArea = oSegment.Area sLoopSection = oSegment.LoopSection If nArea = 0 Then If sSegmentID = "ISA" Then 'add ISA data into the Interchange table sSql = "insert into [Interchange] (SenderId, SenderQlfr, ReceiverID, ReceiverQlfr, ControlNo, InterDate) " _ & " values " _ & "(@SenderId, @SenderQlfr, @ReceiverID, @ReceiverQlfr, @ControlNo, @InterDate); " _ & "SELECT scope_identity()" oDaInterchange.InsertCommand = New SqlCommand(sSql, oConnection) oDaInterchange.InsertCommand.Parameters.AddWithValue("@SenderId", oSegment.DataElementValue(6)) ' Interchange Sender ID (I06) oDaInterchange.InsertCommand.Parameters.AddWithValue("@SenderQlfr", oSegment.DataElementValue(5)) ' Interchange ID Qualifier (I05) oDaInterchange.InsertCommand.Parameters.AddWithValue("@ReceiverID", oSegment.DataElementValue(8)) ' Interchange Receiver ID (I07) oDaInterchange.InsertCommand.Parameters.AddWithValue("@ReceiverQlfr", oSegment.DataElementValue(7)) ' Interchange ID Qualifier (I05) oDaInterchange.InsertCommand.Parameters.AddWithValue("@ControlNo", oSegment.DataElementValue(13)) ' Interchange Control Number (I12) oDaInterchange.InsertCommand.Parameters.AddWithValue("@InterDate", oSegment.DataElementValue(9)) ' Interchange Date (I08) nInterKey = oDaInterchange.InsertCommand.ExecuteScalar() ElseIf sSegmentID = "GS" Then 'add GS segment data into FuncGroup table sSql = "insert into [FuncGroup](Interkey, FuncID, SenderID, ControlNo, GroupVersion) " _ & " values (@Interkey, @FuncID, @SenderID, @ControlNo, @GroupVersion); " _ & "SELECT scope_identity()" oDaFuncGroup.InsertCommand = New SqlCommand(sSql, oConnection) oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@Interkey", nInterKey) 'FuncGroup table linked to Interchange table primary key oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@FuncID", oSegment.DataElementValue(1)) ' Functional Identifier Code (479) oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@SenderID", oSegment.DataElementValue(2)) ' Application Sender's Code (142) oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@ControlNo", oSegment.DataElementValue(6)) ' Group Control Number (28) oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GroupVersion", oSegment.DataElementValue(8)) ' Version / Release / Industry Identifier Code (480) nGroupKey = oDaFuncGroup.InsertCommand.ExecuteScalar() ElseIf sSegmentID = "GE" Then ElseIf sSegmentID = "IEA" Then End If ElseIf nArea = 1 Then If sLoopSection = "" Then If sSegmentID = "ST" Then 'assign ST data element values to oDaX098Header table fields 'X098Header sSql = "insert into [X098Header](Groupkey, MessageNo, " _ & "ControlNo, ReferenceID, ReferenceDate,ReceiverCompanyName, ReceiverCode, " _ & "SubmitterCompanyName, SubmitterCode) " _ & "values " _ & "(@Groupkey, @MessageNo, @ControlNo, @ReferenceID, @ReferenceDate, @ReceiverCompanyName, @ReceiverCode, " _ & "@SubmitterCompanyName, @SubmitterCode); " _ & "SELECT scope_identity()" oDaX098Header.InsertCommand = New SqlCommand(sSql, oConnection) oDaX098Header.InsertCommand.Parameters.AddWithValue("@Groupkey", nGroupKey) 'X098Header table linked to FuncGroup table by this primary key oDaX098Header.InsertCommand.Parameters.AddWithValue("@MessageNo", oSegment.DataElementValue(1)) 'Transaction Set Identifier Code oDaX098Header.InsertCommand.Parameters.AddWithValue("@ControlNo", oSegment.DataElementValue(2)) 'Transaction Set Control Number oDaX098Header.InsertCommand.Parameters.AddWithValue("@ReferenceID", "") oDaX098Header.InsertCommand.Parameters.AddWithValue("@ReferenceDate", "") oDaX098Header.InsertCommand.Parameters.AddWithValue("@ReceiverCompanyName", "") oDaX098Header.InsertCommand.Parameters.AddWithValue("@ReceiverCode", "") oDaX098Header.InsertCommand.Parameters.AddWithValue("@SubmitterCompanyName", "") oDaX098Header.InsertCommand.Parameters.AddWithValue("@SubmitterCode", "") nHeaderKey = oDaX098Header.InsertCommand.ExecuteScalar() ElseIf sSegmentID = "BHT" Then sSql = "UPDATE [X098Header] SET ReferenceID = @ReferenceID, ReferenceDate = @ReferenceDate " _ & " where Headerkey = @Headerkey" oDaX098Header.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Header.UpdateCommand.Parameters.AddWithValue("@ReferenceID", oSegment.DataElementValue(3)) 'Reference Identification oDaX098Header.UpdateCommand.Parameters.AddWithValue("@ReferenceDate", oSegment.DataElementValue(4)) 'Date oDaX098Header.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderKey) oDaX098Header.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "REF" Then End If ElseIf sLoopSection = "NM1" Then If sSegmentID = "NM1" Then 'get the entity qualifier for the NM1 loop to determine if the loop instance contains submitter or receiver information sNm1Qlfr = oSegment.DataElementValue(1) End If If sNm1Qlfr = "41" Then 'LOOP 1000A SUBMITTER If sSegmentID = "NM1" Then sSql = "UPDATE [X098Header] SET ReceiverCompanyName = @ReceiverCompanyName, ReceiverCode = @ReceiverCode " _ & " where Headerkey = @Headerkey" oDaX098Header.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Header.UpdateCommand.Parameters.AddWithValue("@ReceiverCompanyName", oSegment.DataElementValue(3)) 'Name Last or Organization Name oDaX098Header.UpdateCommand.Parameters.AddWithValue("@ReceiverCode", oSegment.DataElementValue(9)) 'Identification Code oDaX098Header.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderKey) oDaX098Header.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "PER" Then sValue = oSegment.DataElementValue(2) ' Name sValue = oSegment.DataElementValue(4) ' Communication Number sValue = oSegment.DataElementValue(6) ' Communication Number End If ElseIf sNm1Qlfr = "40" Then 'LOOP 1000B RECEIVER If sSegmentID = "NM1" Then sSql = "UPDATE [X098Header] SET SubmitterCompanyName = @SubmitterCompanyName, SubmitterCode = @SubmitterCode " _ & " where Headerkey = @Headerkey" oDaX098Header.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Header.UpdateCommand.Parameters.AddWithValue("@SubmitterCompanyName", oSegment.DataElementValue(3)) 'Name Last or Organization Name oDaX098Header.UpdateCommand.Parameters.AddWithValue("@SubmitterCode", oSegment.DataElementValue(9)) 'Identification Code oDaX098Header.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderKey) oDaX098Header.UpdateCommand.ExecuteNonQuery() End If End If 'sNm1Qlfr End If 'sLoopSection ElseIf nArea = 2 Then 'segments that are not in a loop in Area 2 of file If sLoopSection = "" Then If sSegmentID = "SE" Then 'end of transaction set End If ElseIf sLoopSection = "HL" And sSegmentID = "HL" Then 'get the entity loop qualifier to determine if the HL loop instance contains the provider, subscriber or dependent information sEntity = oSegment.DataElementValue(3) End If If sEntity = "20" Then 'LOOP 2000A BILLING PROVIDER If sLoopSection = "HL" Then sSql = "insert into [X098ProviderInfo](Headerkey, CompanyName,NationalID, Address1,City,State,Zip) " _ & "values (@Headerkey, @CompanyName, @NationalID, @Address1, @City, @State ,@Zip); " _ & "SELECT scope_identity()" oDaX098ProviderInfo.InsertCommand = New SqlCommand(sSql, oConnection) oDaX098ProviderInfo.InsertCommand.Parameters.AddWithValue("@Headerkey", nHeaderKey) oDaX098ProviderInfo.InsertCommand.Parameters.AddWithValue("@CompanyName", "") oDaX098ProviderInfo.InsertCommand.Parameters.AddWithValue("@NationalID", "") oDaX098ProviderInfo.InsertCommand.Parameters.AddWithValue("@Address1", "") oDaX098ProviderInfo.InsertCommand.Parameters.AddWithValue("@City", "") oDaX098ProviderInfo.InsertCommand.Parameters.AddWithValue("@State", "") oDaX098ProviderInfo.InsertCommand.Parameters.AddWithValue("@Zip", "") nProviderKey = oDaX098ProviderInfo.InsertCommand.ExecuteScalar() ElseIf sLoopSection = "HL;NM1" Then If sSegmentID = "NM1" Then sNm1Qlfr = oSegment.DataElementValue(1) ' Entity Identifier Code End If If sNm1Qlfr = "85" Then 'LOOP 2010AA BILLING PROVIDER NAME If sSegmentID = "NM1" Then sSql = "UPDATE [X098ProviderInfo] SET CompanyName = @CompanyName, NationalID = @NationalID " _ & " where ProviderKey = @ProviderKey" oDaX098ProviderInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@CompanyName", oSegment.DataElementValue(3)) 'Name Last or Organization Name oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@NationalID", oSegment.DataElementValue(9)) 'Identification Code oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@ProviderKey", nProviderKey) oDaX098ProviderInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N3" Then sSql = "UPDATE [X098ProviderInfo] SET Address1 = @Address1 " _ & " where ProviderKey = @ProviderKey" oDaX098ProviderInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@Address1", oSegment.DataElementValue(1)) 'Address Information oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@ProviderKey", nProviderKey) oDaX098ProviderInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N4" Then sSql = "UPDATE [X098ProviderInfo] SET City = @City, State = @State, Zip = @Zip " _ & " where ProviderKey = @ProviderKey" oDaX098ProviderInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@City", oSegment.DataElementValue(1)) 'City Name oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@State", oSegment.DataElementValue(2)) 'State or Province Code oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@Zip", oSegment.DataElementValue(3)) 'Postal Code oDaX098ProviderInfo.UpdateCommand.Parameters.AddWithValue("@ProviderKey", nProviderKey) oDaX098ProviderInfo.UpdateCommand.ExecuteNonQuery() End If ElseIf sNm1Qlfr = "87" Then 'LOOP 2010AB PAY-TO-PROVIDER If sSegmentID = "NM1" Then ElseIf sSegmentID = "N3" Then ElseIf sSegmentID = "N4" Then End If End If 'sNm1Qlfr End If 'sLoopSection ElseIf sEntity = "22" Then 'LOOP 2000B SUBSCRIBER If sLoopSection = "HL" Then If sSegmentID = "HL" Then sSql = "insert into [X098SubscriberInfo](Providerkey, IndividualRelation, SubscriberLastOrgName, SubscriberFirstname, " _ & "SubscriberMemberID, SubscriberAddress, SubscriberCity, SubscriberZip, SubscriberDOB, " _ & "SubscriberGender, PayerLastOrgName, PayerID) " _ & "values " _ & "(@Providerkey, @IndividualRelation, @SubscriberLastOrgName, @SubscriberFirstname, " _ & "@SubscriberMemberID, @SubscriberAddress, @SubscriberCity, @SubscriberZip, @SubscriberDOB, " _ & "@SubscriberGender, @PayerLastOrgName, @PayerID) " _ & "SELECT scope_identity()" oDaX098SubscriberInfo.InsertCommand = New SqlCommand(sSql, oConnection) oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@ProviderKey", nProviderKey) oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@IndividualRelation", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberLastOrgName", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberFirstname", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberMemberID", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberAddress", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberCity", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberZip", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberDOB", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@SubscriberGender", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@PayerLastOrgName", "") oDaX098SubscriberInfo.InsertCommand.Parameters.AddWithValue("@PayerID", "") nSubscriberKey = oDaX098SubscriberInfo.InsertCommand.ExecuteScalar() ElseIf sSegmentID = "SBR" Then 'Subscriber information sSql = "UPDATE [X098SubscriberInfo] SET IndividualRelation = @IndividualRelation " _ & "where SubscriberKey = @SubscriberKey" oDaX098SubscriberInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@IndividualRelation", oSegment.DataElementValue(2)) oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098SubscriberInfo.UpdateCommand.ExecuteNonQuery() End If ElseIf sLoopSection = "HL;NM1" Then If sSegmentID = "NM1" Then sNm1Qlfr = oSegment.DataElementValue(1) End If If sNm1Qlfr = "IL" Then 'LOOP 2010BA SUBSCRIBER NAME If sSegmentID = "NM1" Then sSql = "UPDATE [X098SubscriberInfo] SET SubscriberLastOrgName = @SubscriberLastOrgName, " _ & "SubscriberFirstname = @SubscriberFirstname, SubscriberMemberID = @SubscriberMemberID " _ & "where SubscriberKey = @SubscriberKey" oDaX098SubscriberInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberLastOrgName", oSegment.DataElementValue(3)) 'Name Last or Organization Name oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberFirstname", oSegment.DataElementValue(4)) 'Name First oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberMemberID", oSegment.DataElementValue(9)) 'Identification Code oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098SubscriberInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N3" Then sSql = "UPDATE [X098SubscriberInfo] SET SubscriberAddress = @SubscriberAddress " _ & "where SubscriberKey = @SubscriberKey" oDaX098SubscriberInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberAddress", oSegment.DataElementValue(1)) 'Address Information oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098SubscriberInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N4" Then sSql = "UPDATE [X098SubscriberInfo] SET SubscriberCity = @SubscriberCity, SubscriberZip = @SubscriberZip " _ & "where SubscriberKey = @SubscriberKey" oDaX098SubscriberInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberCity", oSegment.DataElementValue(1)) 'City Name oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberZip", oSegment.DataElementValue(3)) 'Postal Code oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098SubscriberInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "DMG" Then sSql = "UPDATE [X098SubscriberInfo] SET SubscriberDOB = @SubscriberDOB, SubscriberGender = @SubscriberGender " _ & "where SubscriberKey = @SubscriberKey" oDaX098SubscriberInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberDOB", oSegment.DataElementValue(2)) 'DOB oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberGender", oSegment.DataElementValue(3)) 'Gender oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098SubscriberInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "REF" Then End If ElseIf sNm1Qlfr = "PR" Then 'LOOP 2010BB PAYER NAME If sSegmentID = "NM1" Then sSql = "UPDATE [X098SubscriberInfo] SET PayerLastOrgName = @PayerLastOrgName, PayerID = @PayerID " _ & "where SubscriberKey = @SubscriberKey" oDaX098SubscriberInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@PayerLastOrgName", oSegment.DataElementValue(3)) 'Name oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@PayerID", oSegment.DataElementValue(9)) 'payer id oDaX098SubscriberInfo.UpdateCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098SubscriberInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N2" Then End If End If 'sNm1Qlfr End If 'sLoopSection 'SUBSCRIBER'S CLAIMS LOOP 2300 Call Proc_2300_Claim(oSegment, sSegmentID, sLoopSection, sNm1Qlfr, sQlfr, nLineCount) ElseIf sEntity = "23" Then 'LOOP 2000C DEPENDENT If sLoopSection = "HL" Then If sSegmentID = "HL" Then sSql = "insert into [X098DependentInfo](SubscriberKey, Relationship, " _ & "Lastname, Firstname, Address, City, State, Zip, DOB, Gender) " _ & "values (@SubscriberKey, @Relationship, " _ & "@Lastname, @Firstname, @Address, @City, @State, @Zip, @DOB, @Gender); " _ & "SELECT scope_identity()" oDaX098DependentInfo.InsertCommand = New SqlCommand(sSql, oConnection) oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@Relationship", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@Lastname", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@Firstname", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@Address", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@City", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@State", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@Zip", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@DOB", "") oDaX098DependentInfo.InsertCommand.Parameters.AddWithValue("@Gender", "") nDependentKey = oDaX098DependentInfo.InsertCommand.ExecuteScalar() ElseIf sSegmentID = "PAT" Then 'Individual Relationship Code If oSegment.DataElementValue(1) = "01" Then sValue = "SPOUSE" ElseIf oSegment.DataElementValue(1) = "19" Then sValue = "CHILD" Else sValue = "OTHER" End If sSql = "UPDATE [X098DependentInfo] SET Relationship = @Relationship " _ & " where DependentKey = @DependentKey" oDaX098DependentInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@Relationship", sValue) oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@DependentKey", nDependentKey) oDaX098DependentInfo.UpdateCommand.ExecuteNonQuery() End If 'Segment ID ElseIf sLoopSection = "HL;NM1" Then If sSegmentID = "NM1" Then sNm1Qlfr = oSegment.DataElementValue(1) End If If sNm1Qlfr = "QC" Then 'LOOP 2010CA PATIENT If sSegmentID = "NM1" Then sSql = "UPDATE [X098DependentInfo] SET Lastname = @Lastname, Firstname = @Firstname " _ & " where DependentKey = @DependentKey" oDaX098DependentInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@Lastname", oSegment.DataElementValue(3)) 'Name Last or Organization Name oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@Firstname", oSegment.DataElementValue(4)) 'Name First oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@DependentKey", nDependentKey) oDaX098DependentInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N3" Then sSql = "UPDATE [X098DependentInfo] SET Address = @Address " _ & " where DependentKey = @DependentKey" oDaX098DependentInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@Address", oSegment.DataElementValue(1)) 'Address Information oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@DependentKey", nDependentKey) oDaX098DependentInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N4" Then sSql = "UPDATE [X098DependentInfo] SET City = @City, State = @State, Zip = @Zip " _ & " where DependentKey = @DependentKey" oDaX098DependentInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@City", oSegment.DataElementValue(1)) 'City Name oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@State", oSegment.DataElementValue(2)) 'State or Province Code oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@Zip", oSegment.DataElementValue(3)) 'Postal Code oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@DependentKey", nDependentKey) oDaX098DependentInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "DMG" Then sSql = "UPDATE [X098DependentInfo] SET DOB = @DOB, Gender = @Gender " _ & " where DependentKey = @DependentKey" oDaX098DependentInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@DOB", oSegment.DataElementValue(2)) 'Date Time Period oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@Gender", oSegment.DataElementValue(3)) 'Gender Code oDaX098DependentInfo.UpdateCommand.Parameters.AddWithValue("@DependentKey", nDependentKey) oDaX098DependentInfo.UpdateCommand.ExecuteNonQuery() End If 'Segment ID End If 'sNm1Qlfr End If 'sLoopSection 'DEPENDENTS CLAIMS LOOP 2300 Call Proc_2300_Claim(oSegment, sSegmentID, sLoopSection, sNm1Qlfr, sQlfr, nLineCount) End If 'sEntity ElseIf nArea = 3 Then End If ediDataSegment.Set(oSegment, oSegment.Next) 'oSegment = oSegment.Next End While 'close connection to Access database oConnection.Close() 'clear memory of SEF files oSchemas.Dispose() Me.Cursor = Cursors.Default MsgBox("Done") 'clear memory of EDI, and remove any temp files oEdiDoc.Dispose() End Sub Private Sub Proc_2300_Claim(ByRef oSegment As ediDataSegment, ByRef sSegmentID As String, ByRef sLoopSection As String, ByRef sNm1Qlfr As String, ByRef sQlfr As String, ByRef nLineCount As Integer) Dim sValue As String Dim sSql As String 'This procedure is called by the Subscriber 2000B loop, as well as by Dependent/Patient 2000C loop If sLoopSection = "HL;CLM" Then If sSegmentID = "CLM" Then sSql = "insert into [X098Claims](SubscriberKey, DependentKey, PatientAccountNo, ClaimAmount, " _ & "OnsetDate, RenderingLastname, RenderingFirstname, RenderingID, FacilityName, FacilityID, " _ & "FacilityAddr, FacilityCity, FacilityState, FacilityZip) " _ & "values " _ & "(@SubscriberKey, @DependentKey, @PatientAccountNo, @ClaimAmount, " _ & "@OnsetDate, @RenderingLastname, @RenderingFirstname, @RenderingID, @FacilityName, @FacilityID, " _ & "@FacilityAddr, @FacilityCity, @FacilityState, @FacilityZip); " _ & "SELECT scope_identity()" oDaX098Claims.InsertCommand = New SqlCommand(sSql, oConnection) oDaX098Claims.InsertCommand.Parameters.AddWithValue("@SubscriberKey", nSubscriberKey) oDaX098Claims.InsertCommand.Parameters.AddWithValue("@DependentKey", nDependentKey) oDaX098Claims.InsertCommand.Parameters.AddWithValue("@PatientAccountNo", oSegment.DataElementValue(1)) oDaX098Claims.InsertCommand.Parameters.AddWithValue("@ClaimAmount", oSegment.DataElementValue(2)) oDaX098Claims.InsertCommand.Parameters.AddWithValue("@OnsetDate", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@RenderingLastname", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@RenderingFirstname", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@RenderingID", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@FacilityName", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@FacilityID", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@FacilityAddr", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@FacilityCity", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@FacilityState", "") oDaX098Claims.InsertCommand.Parameters.AddWithValue("@FacilityZip", "") nClaimsKey = oDaX098Claims.InsertCommand.ExecuteScalar() ElseIf sSegmentID = "DTP" Then sQlfr = oSegment.DataElementValue(1) If sQlfr = "938" Then 'Order sValue = oSegment.DataElementValue(3) ElseIf sQlfr = "454" Then 'Initial Treatment sValue = oSegment.DataElementValue(3) ElseIf sQlfr = "330" Then 'Referral Date sValue = oSegment.DataElementValue(3) ElseIf sQlfr = "304" Then 'Date Last Seen sValue = oSegment.DataElementValue(3) ElseIf sQlfr = "431" Then 'Onset of Current Illness/Symptom sSql = "UPDATE [X098Claims] SET OnsetDate = @OnsetDate " _ & " where ClaimsKey = @ClaimsKey" oDaX098Claims.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@OnsetDate", oSegment.DataElementValue(3)) 'Date oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@ClaimsKey", nClaimsKey) oDaX098Claims.UpdateCommand.ExecuteNonQuery() End If ElseIf sSegmentID = "REF" Then ElseIf sSegmentID = "HI" Then End If 'sSegmentID ElseIf sLoopSection = "HL;CLM;NM1" Then If sSegmentID = "NM1" Then sNm1Qlfr = oSegment.DataElementValue(1) End If If sNm1Qlfr = "82" Then 'LOOP 2310B RENDERING PROVIDER NAME If sSegmentID = "NM1" Then sSql = "UPDATE [X098Claims] SET RenderingLastname = @RenderingLastname, RenderingFirstname = @RenderingFirstname, RenderingID = @RenderingID " _ & " where ClaimsKey = @ClaimsKey" oDaX098Claims.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@RenderingLastname", oSegment.DataElementValue(3)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@RenderingFirstname", oSegment.DataElementValue(4)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@RenderingID", oSegment.DataElementValue(9)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@ClaimsKey", nClaimsKey) oDaX098Claims.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "PRV" Then End If ElseIf sNm1Qlfr = "77" Then 'LOOP 2310D SERVICE FACILITY LOCATION If sSegmentID = "NM1" Then sSql = "UPDATE [X098Claims] SET FacilityName = @FacilityName, FacilityID = @FacilityID " _ & " where ClaimsKey = @ClaimsKey" oDaX098Claims.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@FacilityName", oSegment.DataElementValue(3)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@FacilityID", oSegment.DataElementValue(9)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@ClaimsKey", nClaimsKey) oDaX098Claims.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N3" Then sSql = "UPDATE [X098Claims] SET FacilityAddr = @FacilityAddr " _ & " where ClaimsKey = @ClaimsKey" oDaX098Claims.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@FacilityAddr", oSegment.DataElementValue(1)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@ClaimsKey", nClaimsKey) oDaX098Claims.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "N4" Then sSql = "UPDATE [X098Claims] SET FacilityCity = @FacilityCity, FacilityState = @FacilityState, FacilityZip = @FacilityZip " _ & " where ClaimsKey = @ClaimsKey" oDaX098Claims.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@FacilityCity", oSegment.DataElementValue(1)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@FacilityState", oSegment.DataElementValue(2)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@FacilityZip", oSegment.DataElementValue(3)) oDaX098Claims.UpdateCommand.Parameters.AddWithValue("@ClaimsKey", nClaimsKey) oDaX098Claims.UpdateCommand.ExecuteNonQuery() End If End If 'sNm1Qlfr ElseIf sLoopSection = "HL;CLM;LX" Then 'LOOP 2400 SERVICE LINE If sSegmentID = "LX" Then sSql = "insert into [X098ServiceInfo](Claimskey, ServiceLine, ServiceID, ServiceAmount, Diagnosis, ServiceDate) " _ & "values (@Claimskey, @ServiceLine, @ServiceID, @ServiceAmount, @Diagnosis, @ServiceDate); " _ & "SELECT scope_identity()" oDaX098ServiceInfo.InsertCommand = New SqlCommand(sSql, oConnection) oDaX098ServiceInfo.InsertCommand.Parameters.AddWithValue("@Claimskey", nClaimsKey) oDaX098ServiceInfo.InsertCommand.Parameters.AddWithValue("@ServiceLine", nLineCount + 1) oDaX098ServiceInfo.InsertCommand.Parameters.AddWithValue("@ServiceID", "") oDaX098ServiceInfo.InsertCommand.Parameters.AddWithValue("@ServiceAmount", "") oDaX098ServiceInfo.InsertCommand.Parameters.AddWithValue("@Diagnosis", "") oDaX098ServiceInfo.InsertCommand.Parameters.AddWithValue("@ServiceDate", "") nServiceInfoKey = oDaX098ServiceInfo.InsertCommand.ExecuteScalar() ElseIf sSegmentID = "SV1" Then sSql = "UPDATE [X098ServiceInfo] SET ServiceID = @ServiceID, ServiceAmount = @ServiceAmount, Diagnosis = @Diagnosis " _ & " where ServiceInfoKey = @ServiceInfoKey" oDaX098ServiceInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098ServiceInfo.UpdateCommand.Parameters.AddWithValue("@ServiceID", oSegment.DataElementValue(1, 2)) ' Product/Service ID oDaX098ServiceInfo.UpdateCommand.Parameters.AddWithValue("@ServiceAmount", oSegment.DataElementValue(2)) ' Monetary Amount oDaX098ServiceInfo.UpdateCommand.Parameters.AddWithValue("@Diagnosis", oSegment.DataElementValue(7, 1)) ' Diagnosis Code Pointer oDaX098ServiceInfo.UpdateCommand.Parameters.AddWithValue("@ServiceInfoKey", nServiceInfoKey) oDaX098ServiceInfo.UpdateCommand.ExecuteNonQuery() ElseIf sSegmentID = "DTP" Then sQlfr = oSegment.DataElementValue(1) If sQlfr = "472" Then sSql = "UPDATE [X098ServiceInfo] SET ServiceDate = @ServiceDate " _ & " where ServiceInfoKey = @ServiceInfoKey" oDaX098ServiceInfo.UpdateCommand = New SqlCommand(sSql, oConnection) oDaX098ServiceInfo.UpdateCommand.Parameters.AddWithValue("@ServiceDate", oSegment.DataElementValue(3)) 'Service Date oDaX098ServiceInfo.UpdateCommand.Parameters.AddWithValue("@ServiceInfoKey", nServiceInfoKey) oDaX098ServiceInfo.UpdateCommand.ExecuteNonQuery() ElseIf sQlfr = "330" Then sValue = oSegment.DataElementValue(3) 'Referral Date End If End If End If 'sLoopSection End Sub