-
Notifications
You must be signed in to change notification settings - Fork 16
Sql Server Database Integration
You can import your X12 into a SQL Server database by using the OopFactoryX12.ImportX12 console app. This will create the following schema in the database once you have loaded a file:
Container: keeps all Int or BigInt identities for the Interchange, FunctionalGroup, TransactionSet and Loop tables.
Revision - allows for edits of the X12 and keeping track of comments related to those edits. A record of 0 is added by default to represent the initial load of all X12.
X12CodeList - This will contain the lookup values for any elements in a segment that has been Indexed into a separate table.
Interchange, FunctionalGroup, TransactionSet, Loop, Segment - this is the database realization of the Interchange model into a relational model. All segments are shown as they were in the x12 message in the Segment column of the Segment table.
NM1, N1, N3 ... - these are the indexed segment tables specified in the IndexedSegment configuration values. It uses the X12 specification for each segment to define the table. Because of this, there is the potential that something in the segment was not parsable. It may either get truncated or was not parsable to the segment specification's data type. These are errors in the X12, but this will be noted in the log file of the ImportX12 app. The original segment will always remain as a string in the Segment column of the Segment table.
ParsingError - because the indexed tables are typed into decimal and datetime when relevant, this table will contain any errors translating the target data type or any errors exceeding the max length of a string element. The errorId will be recorded in the indexed segment table. The original Segment table will always contain the original value from the x12 file.
The application has the following application settings. Just point the connection string to an empty database and all the tables, views and user-defined functions will be created on-the-fly when you import a file.
schema - schema of the Interchange, FunctionalGroup, TransactionSet, Loop, Segment tables and any indexed segment tables. If you are using a schema other than "dbo" make sure that you create it first in your target database. The ImportX12 app will create tables, but will expect the schema to already exist.
containerSchema - schema of the common tables: Container and Revision
ThrowExceptionOnSyntaxErrors - indicates whether an exception should be thrown on. When the value is false an warning will be sent through the ParsingWarning event instead. Hierarchical Parent Id references that don't exist, if false, than the HL will be treated as a top level HL without a parent Segments that don't occur in the transaction set's specification, if false, the segment will parsed as a segment of the current loop where it was found.
IndexedSegments - a comma delimited list of X23 segment IDs that you would like to have as individual tables. The specification of that segment ID will be used to determine how many element columns to create and of which data type. For example, if you are parsing 837 health claims you would use the list "AMT,BHT,CAS,CL1,CLM,CN1,CR1,CR2,CR3,CR4,CR5,CR6,CR7,CR8,CRC,CTP,CUR,DMG,DN1,DN2,DSB,DTP,FRM,HCP,HI,HL,HSD,III,IMM,K3,LIN,LQ,LUI,LX,MEA,MIA,MOA,N2,N3,N4,NM1,NTE,OI,PAT,PER,PRV,PS1,PWK,QTY,RAS,REF,SBR,SV1,SV2,SV3,SV4,SV5,SV6,SV7,SVD,TOO,UR". See Transaction Set Configuration for a full list.
ParseDirectory - the directory to search for X12 files
ParseSearchPattern - the file search pattern to filter files for parsing with the ParseDirectory
ArchiveDirectory - the directory to move each file the parses and imports successfully.
FailureDirectory - the directory to move any file that fails to parse or load. If the Interchange record was written, the HasErrors column will be set to true on failed files.
The application allows you to configure the containerSchema separately from the schema so that you can choose to partition your x12. This can be useful in the following scenarios:
- You want each client's data in a separate schema so their data will never co-mingle and each client does not suffer performance issues because another client has a large amount of records.
- You want to separate your X12 into inbound and outbound messages.
- You want to set separate security on different schemas for different users in your environment so they only access what they need to know, since most X12 contains Personally Identifiable Information.
- The system is designed as an INSERT ONLY transactional database. Updates and Deletes are handled by adding revisions. The built in user-defined functions take this into account and only show the latest revision of each segment.
In most cases you won't be doing any revisions, especially if the X12 messages represent TRUE EDI messages that came to you from a trading partner through a clearinghouse. Mistakes in the X12 should be handled by acknowledgment files that reject bad transactions. However, there are other use cases of X12 that are not TRUE EDI. When the transaction came to you in some other format and you have a data entry system that creates X12 so that you have a common process for integrating all incoming transactions, errors may have been introduced. In this case errors in your X12 may be your responsibility and you might want a process for creating edits of you X12. In this case you will be able to use the SaveRevision method of the SqlTransactionRepository to make edits that will produce valid X12 for your downstream processes.