This section identifies and describes the most important TCTEP database tables. This section will include descriptions of the most significant columns in those tables. Additionally, this section will describe the structure of certain columns which utilize a JSON structure. An asterisk(*) denotes row containing JSON structure information for above column.
Common Columns
The following columns are present in a majority of the tables, having the same meaning and purpose across all artifacts. Rather repeat the columns in each table in the dictionary, they are presented here together for simplicity.
|
Column Name |
Data Type |
Description |
|
IsReadOnly |
bit |
Is used within the Portal to forcefully make the data element read only and not updatable by any user. One example of this is rules in the tRuleType with RuleType of 1. These are SNIP 1 & 2 rules which are always enabled, thus the portal does not allow a user to disable the rules. |
|
IsActive\IsEnabled |
bit |
This signifies that the data element is or is not enabled. This is a value set by users in the portal, for example routes in the tRoute table can be set with IsActive bit of false, disabling the route from executing. |
|
IsDeleted |
bit |
Marks the row as deleted within the context of TConnect. Any record that has IsDeleted = 1 is hidden from the Portal, and not taken into account withn the TConnect services when processing files.
For example, a route in the tRoute table that is deleted will only mark the row as deleted. The route will disappear from the portal, will never be executed by the services, but will still remain in the database for audit purposes. |
|
CreateDate |
|
Defaulted to GETDATE(), represents the date and time of record creation |
|
UpdateDate |
|
Defaulted to GETDATE(), represents the most recent date and time of record alteration. These are commonly set by triggers on the individual tables |
|
CreateUser |
|
Defaulted to SUSER_NAME(),represents the user initiating the action represented in the table. For example for routes, it would be the user who created the route. These are commonly set by triggers on the individual tables |
|
UpdateUser |
|
Defaulted to SUSER_NAME(),represents the user who last acted on a record in the table. For example for routes, it would be the user who last updated, enabled or disabled a route. These are commonly set by triggers on the individual tables |
|
Description |
|
Descriptions are commonly only included in tables for ease of use. Descriptions don’t affect processing in services, but may be used within the portal to convey detailed information about a specific row. For example, descriptions in the tEventCode table are presented in the detail page of the portal instead of the EventCodeIds that are used by the services. |
|
Primary Keys (<tableName>Id) |
INT |
|
|
LoadMachine |
varchar |
Computer name of originator of record |
Portal Configuration Tables
|
config.tAlertConfig – Types of alerts that are sent by TConnect services |
||
|
Column Name |
Data Type |
Description |
|
AlertType |
varchar |
Type of the alert |
|
IsActive |
bit |
See Common Columns |
|
IsDeleted |
bit |
See Common Columns |
|
AlertRecipient |
varchar |
Single email address of alert recipient |
|
portal.tFeed – This table controls the EDI transactions that display within the T-Connect Portal |
||
|
Column Name |
Data Type |
Description |
|
FeedName |
varchar |
The display label for an EDI transaction |
|
TransactionNumber |
varchar |
A short label for the transaction. This joins to the x12.tTransactionType table |
|
DefaultSearchFilter |
varchar |
JSON array listing each ST01 and GS08 pair representing each version of the transaction. |
|
DefaultSearchFilter JSON* |
|
page: Default page to show the transaction set type in the home page of the portal,
ediSearchIndex: JSON array of ST01 and GS08 pairs representing each version of the transaction supported. st01: ST01 ex: “834” gs08: GS08 value, ex: "005010X220A1" |
|
portal.tDropDowns – Elements to populate user controls in Portal |
||
|
Column Name |
Data Type |
Description |
|
DropDownType |
nvarchar |
Type name of drop down or user control. Used as primary identification in portal |
|
DropDownName |
nvarchar |
Display name of user control item. |
|
DropDownValue |
nvarchar |
Backing value of user control item |
|
trk.tEmailAlert – Controls the HTML template that can be used for a specified event code in the tEventCode table. Currently only validation templates are specified. Others can be user created and referenced within the IEmailAlertService in the Tctep solution |
||
|
Column Name |
Data Type |
Description |
|
EventCode |
int |
Specific event code that can spawn an email alert for the specific template. References the tEventCode table |
|
AlertMessageContent |
varchar |
HTML template for the email |
|
IsMessageHtml |
bit |
1 if an HTML template, 0 if Rich Text |
|
|
||
|
trk.tEvent – Tracking of all system events from the Tctep services. Events in the tEventCode table are shown in the Portal’s message detail page. |
||
|
Column Name |
Data Type |
Description |
|
LoadMachine |
varchar |
Name of the server who logged the event |
|
Scope |
varchar |
Function or method optionally associated with the specific event instance |
|
trk.tEventCode – Tracking of all system events from the Tctep services. EventCode types referenced in the tEvent table. |
||
|
Column Name |
Data Type |
Description |
|
ShortCode |
varchar |
Name of the matching EventCode within Tctep solution’s TctepRepository.Events.EventCode enumeration. Each event code has to match the EventCode enumeration in Tctep. |
|
Description |
varchar |
See Common Columns |
|
trk.tException – Contains all system and validation exceptions thrown by services. Exceptions are based on System.Exceptions or custom implementations implementing System.Exception interface |
||
|
Column Name |
Data Type |
Description |
|
LoadMachine |
varchar |
Name of the server and source of the error |
|
ExceptionTypeName |
varchar |
Namespace and typename of thrown error. Will always be System.Exception or a custom exception object implementing System.Exception. TtepConnector contains a base custom exception implementation ‘ConnectorExtension’ which can be utilized |
|
MachineName |
varchar |
Name of the server who logged the error |
|
Scope |
varchar |
Function or method optionally associated with the specific event instance |
|
Description |
varchar |
Error description text, usually Message property or custom string generated by originating source of error |
|
StackTrace |
varchar |
Stack track property of exception object |
|
ToString |
varchar |
Output of ‘ToString’ done on exception object |
|
trk.tProcessStageCode – Contains stages that can be associated to a transaction instance as the source of the transaction (file). This table is primarily used to associate a file as inbound, outbound or resubmission |
||
|
Column Name |
Data Type |
Description |
|
ShortCode |
varchar |
Name of the matching ProcessStage enumeration within Tctep solution’s TctepRepository.Config.Enums.ProcessStage enumeration. Each short code has to match to a corresponding ProcessStage enum |
|
Description |
varchar |
Friendly description of enumeration. This is used as a fallback descriptor for a route’s name if no route name or step name is found or specified |
|
LoadMachine |
varchar |
Not used |
|
x12.tEdiSearchField – Details the specific search criteria that can be used for searching within the Portal. These will prompt persistence of each set of criteria and parsed value for every file processed similar to an index. Also used to store the segment in each EDI transaction set that can be used to count the number of records in the file. This is surfaced in the Transaction Set and Message pages of the Portal |
||
|
Column Name |
Data Type |
Description |
|
LastMachine |
varchar |
Last machine of the user who updated a record |
|
LoopName |
varchar |
Name of the loop to be persisted (indexed) |
|
SegmentName |
varchar |
Name of the segment to be persisted |
|
ElementIndex |
int |
Ordinal position of the element within the segment |
|
ElementName |
varchar |
Friendly name to track the persisted index. Element names show in the message page search criteria drop down user controls as types of criteria to search by. |
|
QualifierPos |
int |
Position of a qualifier within the segment, if a qualifier is used to identify the index. For example if a REF or DTP segment is persisted, a different qualifier value can represent a different EDI value. |
|
Qualifier |
varchar |
Text value of specific qualifier to persist Ex: D9 for DTP01 as qualifier if only DTP segments with DTP01 of ‘D9’ are desired |
|
IsRecordIndicator |
bit |
1 if the index can be used to count the number of records in the transaction set. Ex: CLM segments identify individual detail level records in an 837 claim. |
|
LoadMachine |
varchar |
Computer name of user who added an entry |
|
|
||
|
x12.tEdiSearchIndex – Contains each persisted search index value associated to the search field in the tEdiSearchField table and the EDI file in the tInboundEdi table. This table drives all searches performed in the portal. |
||
|
Column Name |
Data Type |
Description |
|
SegmentNumber |
int |
Number of the segment as it appears within the ST/SE transaction |
|
Val |
varchar |
Raw value persisted |
|
ValType |
varchar |
Type of value persisted, references ElementName in the tEdiSearchField table |
|
LoadMachine |
varchar |
Computer name of user who added an entry |
|
LastMachine |
varchar |
Not used |
|
|
||
|
x12.tX12AcknowledgementCode – List of 999 acknowledgement codes recognized within TConnect for the purpose of persisting EDI transactions within different databases according to the result of processing. A transaction set that fails validation can be optionally stored in a different database than valid EDI transactions |
||
|
Column Name |
Data Type |
Description |
|
X12AcknowledgementCode |
char |
Ack code A, E, M, P, R, W, X corresponding to the many acknowledgement reason codes in an EDI 999 |
|
Description |
varchar |
Friendly description |
|
LoadMachine |
varchar |
Computer name of user who added an entry |
|
|
||
|
x12.tX12ReleaseCode – Listing of all ST01 and GS08 tuples that make up all supported transaction set types and versions supported by TConnect. Joined against tX12TransactionSet, used by the Transaction Set page. |
||
|
Column Name |
Data Type |
Description |
|
ST01 |
Int |
ST01 value for transaction set, part of the composite primary key |
|
GS08 |
varchar |
GS08 value for transaction set, part of the composite primary key |
|
Description |
varchar |
See Common Columns |
|
LoadMachine |
varchar |
Computer name of user who added an entry |
|
|
||
|
x12.tX12TransactionSet – table description here |
||
|
Column Name |
Data Type |
Description |
|
LoadMachine |
varchar |
Computer name of user who added an entry |
|
Description |
varchar |
See Common Columns |
SQL Polling Configuration –Resubmission Process
|
pol.tPollingExtension – Custom implementation class of IPollingExtension interface used in TctepPollingService. Polling extension is invoked and passed a recordset as an input parameter |
||
|
Column Name |
Data Type |
Description |
|
PollingExtensionName |
varchar |
Class name of polling extension |
|
FullyQualifiedName |
varchar |
Fully qualified assembly name of polling extension |
|
PollingDataSource |
varchar |
Connection string used by polling extension |
|
PollingDataProcedure |
varchar |
Stored procedure executed with expected recordset to be passed into IPollingExtension |
|
TransactionType |
varchar |
Optional transaction type used by polling extension by way of dependency injection |
|
pol.tPollingStage – Staged records queried and used as input for an extension in tPollingExtension |
||
|
Column Name |
Data Type |
Description |
|
PollingStatus |
bit |
Status of record, 0 = not picked up, 1 = picked up |
|
FileName |
varchar |
Filename of any EDI file to be outbounded by a polling extension |
|
PollingSchedule |
datetime |
Not used |
|
DateCreated |
datetime |
Date created of polled record |
|
TransactionType |
varchar |
Optional transaction type used by polling extension by way of dependency injection |
|
ReleasedStatus |
bit |
Not used |
Route Configurations
|
|
|
|
|
portal.tFormTemplates – All JSON templates used in the portal for Route page forms |
||
|
Column Name |
Data Type |
Description |
|
TemplateName |
varchar |
Descriptive name of the template. Displayed on the route form popup as the title of the form |
|
TemplateDescription |
varchar |
Descriptive name, For user descriptive purposes only |
|
TemplateJson |
varchar |
JSON template dictating the user controls, and required fields composing the popup form. See Configuration of Display Forms for more information |
|
TemplateJson JSON* |
|
See Configuration of Display Forms for more information |
|
rt.tLocationContentType – Describes the type of content that is used in send steps. Usage of these values in a send step acts as a filter to allow only the type selected. |
||
|
Column Name |
Data Type |
Description |
|
Name |
varchar |
Type of content used in a send step |
|
Description |
varchar |
Descriptive name of the type of content |
|
rt.tRoute – Route configuration driving end to end processing within services |
||
|
Column Name |
Data Type |
Description |
|
RouteName |
Varchar |
Friendly name for a route |
|
RouteStartDate |
Datetime |
Not used |
|
RouteEndDate |
Datetime |
Not used |
|
IsActive |
Bit |
See Common Columns |
|
IsDeleted |
Bit |
See Common Columns |
|
|
||
|
rt.tRouteLocation – Routing configuration of receive or send step |
||
|
Column Name |
Data Type |
Description |
|
LocationName |
Varchar |
Friendly name for receive or send step |
|
IsReceive |
bit |
1 if location represents a receive step, 0 if send |
|
AddressUri |
varchar |
URI of location’s transport. Ex: File or SFTP URI or SQL connection string |
|
Configuration |
varchar |
User updated configuration JSON for step. Originates from tFormTemplate JSON and is updated by the user in thee portal. |
|
Configuration JSON* |
|
Json Structure Description – See Configuration of Display Forms for more information
The Configuration field is serialized to the FormTemplateFields entity within ConnectorConfigSqlRepository of Tctep service |
|
PollingInterval |
int |
Frequency in seconds between executions of a receive step |
|
IsDeleted |
bit |
See Common Columns |
|
|
||
|
rt.tRouteOperation – Routing configuration for operation step including user specified configuration populated through the Portal’s route form |
||
|
Column Name |
Data Type |
Description |
|
OperationName |
varchar |
Friendly name for step |
|
Configuration |
varchar |
User updated configuration JSON for step. Originates from tFormTemplate JSON and is updated by the user in thee portal. |
|
Configuration JSON* |
|
Json Structure Description – See Configuration of Display Forms for more information |
|
IsDeleted |
bit |
See Common Columns |
|
rt.tRouteOperationType – Type of route operation step possible in a route including information needed to load the step through reflection within TConnect services |
||
|
Column Name |
Data Type |
Description |
|
Name |
varchar |
Friendly name for step type |
|
ClassName |
varchar |
Class name of built in step or custom step |
|
AssemblyName |
varchar |
Fully qualified assembly name of step. This field is not necessary if the step is part of the ServiceExtensions Tctep project |
|
DefaultConfiguration |
varchar |
Not used |
|
Default Configuration JSON |
|
Not used |
|
rt.tStepType – Reference table for step types in the route location table |
||
|
Column Name |
Data Type |
Description |
|
StepTypeName |
nvarchar |
Type of step, 1 – receive, 2 = operation step, 3 = send |
|
|
||
|
rt.tTransportType – Type of transport used in a send or receive step, used by the portal and services |
||
|
Column Name |
Data Type |
Description |
|
Name |
varchar |
Friendly name for transport type |
|
Description |
varchar |
See Common Columns |
|
AllowReceive |
bit |
True if transport can be used in a receive step |
|
AllowSend |
bit |
True if transport can be used in a send step |
Portal Roles and Security
|
sec.tPage – List of pages in the Portal that are possible to secure. Referenced within the tRoleAccess table as RoleAccessItemId/Name |
||
|
Column Name |
Data Type |
Description |
|
PageName |
varchar |
Friendly name of the page. Will show as RoleAccessItemName in the tRoleAccess table |
|
IsActive |
bit |
See Common Columns |
|
|
||
|
sec.tPrincipal – Local or Active Directory principals assigned to a role in tRole. The principal can be a local windows or active directory user or a group. Populated by the portal |
||
|
Column Name |
Data Type |
Description |
|
PrincipalName |
varchar |
Name of principal, user or group. The name has to be fully qualified. Example: localhost\user, domain\user or domain\group |
|
IsActive |
bit |
See Common Columns |
|
IsDeleted |
bit |
See Common Columns |
|
IsPrincipalAGroup |
bit |
1 if principal entered is an AD or local windows group. 0 otherwise. |
|
|
||
|
sec.tRole – Contains all roles that have been added in the Portal. A role is a logical container for principals allowing assignment of access to multiple principals at once |
||
|
Column Name |
Data Type |
Description |
|
RoleName |
varchar |
Friendly name for a role |
|
IsActive |
bit |
See Common Columns |
|
IsDeleted |
bit |
See Common Columns |
|
|
||
|
sec.tRoleAccess – Contains an entry for each individual permission assigned to a role. This table is maintained by the Portal and dictates the pages that a role can access, actions possible in those pages, and trading partners or transaction set types visible |
||
|
Column Name |
Data Type |
Description |
|
RoleName |
Varchar |
The role name in tRole table. This column is automatically generated. |
|
RoleAccessGroupName |
varchar |
The type name of artifact type associated to this access entry. This will usually be ‘Pages’ ‘Trading Partners’ or ‘Transaction Set Types’. This column is automatically generated. |
|
RoleAccessItemName |
varchar |
Name of the referenced individual role access item type. For example the name of a specific page, trading partner or transaction set type. This column is automatically generated. |
|
IsGlobalAccess |
bit |
True if the entry applies to all items of the listed RoleAccessGroup. For example if 1 for a row containing role access group of ‘Pages’, then the role will have the same RoleAccessLevelId applied. |
|
RoleAccessLevelId |
Int |
Level of access for the particular entry. 0. (Deny) - Hides the page, trading partner or transaction set type from the portal for that user. 1. (Read) – Shows but can’t be changed. For Pages this prevents making any changes to any element in the page, such as disabling, deleting or adding routes or trading partners, or resubmitting failed EDI transactions 2. (ReadWrite) Full access to the item |
|
sec.tRoleAccessType – Type of role access possible in the referenced tRoleAccess table. |
||
|
Column Name |
Data Type |
Description |
|
RoleAccessTypeName |
varchar |
Deny, Read, ReadWrite corresponding to RoleAccessTypeId values (below) |
|
RoleAccessTypeId |
int |
Level of access for the particular entry. 1. (Deny) - Hides the page, trading partner or transaction set type from the portal for that user. 2. (Read) – Shows but can’t be changed. For Pages this prevents making any changes to any element in the page, such as disabling, deleting or adding routes or trading partners, or resubmitting failed EDI transactions (ReadWrite) Full access to the item |
Service Configuration
|
svc.tServiceType – Allows for the specification of a type of service used as a receive or send service. Currently only ‘FILE’ service type is used. |
||
|
Column Name |
Data Type |
Description |
|
ServiceTypeName |
varchar |
Friendly name for the type of service. |
|
ServiceTypeDescription |
varchar |
Friendly description for the type of service. |
|
svc.tService – Contains all services that can be referenced through the ServiceId in a Tctep service’s application config file. |
||
|
Column Name |
Data Type |
Description |
|
ServiceName |
varchar |
Friendly name of the service, visible in the portal when showing the source of a file |
|
MachineName |
varchar |
The server name allowed to run the service listed. |
|
IsOnline |
bit |
Similar to the IsEnabled or IsActive field, allows turning off a service for the corresponding MachineName. |
|
svc.tReceiveService – Contains configuration for a referenced tService specific to processing receiving or polling for transactions to process |
||
|
Column Name |
Data Type |
Description |
|
ScheduleConfig |
nvarchar |
Configuration dictating when the service is active and able to process files. This enables scheduled maintenance or downtime periods where the service will be on – but not processing files.
The ScheduleConfig is serialized to the SchedulingInfo entity within IConnectorScheduleingRepository of Tctep service |
|
ScheduleConfig JSON* |
|
{ "StartDate": <Start Date – service will not process files prior to start date> "EndDate": <End Date – service will not process files past date specified> "ServiceWindows": { <array of service windows> "Monday": { <day of week> "StartTime": "11:00:32", <start time in 24H format> "Duration": "00:05:00" <Duration of downtime hhmmss> } } } |
|
ServiceConfig |
nvarchar |
Configuration dictating default runtime settings for the service as well as settings allowing for interaction between different services. For example the url for the validation service is contained within the JSON configuration |
|
ServiceConfig JSON* |
|
{ "ServiceName": "Routing Service",<name of the service> "MachineName": "WIN-7P0T8AAIQ4S", "ValidateAndInsertServiceName": <url of the http validation service> "http://localhost/X12ValidatorService/InsertAndValidate/999", <url of the http validation service> "ValidateAndSplitServiceName": "http://localhost/X12ValidatorService/ValidateAndSplit", "PollingInterval": 5000, <frequency that service will refresh configuration. For routing service, this would be the frequency that any change performed through the portal is picked up by the tctep services> "NumberOfPolls": 0, <maximum quantity of refreshes before the service is shut down.> "AllowUnrecognizedPartner": false, <Allows globally accepting trading partners even if not present in the Portal’s trading partner page> "EnableIntakeEnrichRules": true, <Not used> "ExternalEnrichDb": "", <Not used> "Folders": [<Not Used> ] } |
Trading Partner Management
|
tp.tTradingPartner – Individual trading partners and company/entity contact information that is not used or surfaced in the Portal, but can be used for internal tracking of trading partner information. Populated by the portal on trading partner creation or update |
||
|
Column Name |
Data Type |
Description |
|
TradingPartnerName |
varchar |
Friendly name of trading partner. Shown in trading partner page in the Portal |
|
TradingPartnerAddress |
varchar |
Not used – available for internal client use |
|
TradingPartnerCity |
varchar |
Not used – available for internal client use |
|
TradingPartnerState |
char |
Not used – available for internal client use |
|
TradingPartnerZip |
varchar |
Not used – available for internal client use |
|
TradingPartnerComment |
varchar |
Not used – available for internal client use |
|
IsActive |
bit |
See Common Columns |
|
TradingPartnerStartDate |
date |
Not used |
|
TradingPartnerEndDate |
date |
Not used |
|
ContactType |
varchar |
Not used – available for internal client use |
|
AltCompany |
varchar |
Not used – available for internal client use |
|
ContactLastName |
varchar |
Not used – available for internal client use |
|
ContactFirstname |
varchar |
Not used – available for internal client use |
|
ContactTitle |
varchar |
Not used – available for internal client use |
|
ContactPhone1 |
varchar |
Not used – available for internal client use |
|
Phone1Ext |
varchar |
Not used – available for internal client use |
|
ContactPhone2 |
varchar |
Not used – available for internal client use |
|
Phone2Ext |
varchar |
Not used – available for internal client use |
|
Fax |
varchar |
Not used – available for internal client use |
|
ContactEmail1 |
varchar |
Not used – available for internal client use |
|
ContactEmail2 |
varchar |
Not used – available for internal client use |
|
CC_ContactName |
varchar |
Not used – available for internal client use |
|
CC_ContactTitle |
varchar |
Not used – available for internal client use |
|
CC_ContactAddress |
varchar |
Not used – available for internal client use |
|
CC_ContactCity |
varchar |
Not used – available for internal client use |
|
CC_ContactZip |
varchar |
Not used – available for internal client use |
|
AllowUnspecifiedTransactionSets |
bit |
Not used |
|
IsDeleted |
bit |
See Common Columns |
|
tp.tTradingPartnerIdentification – Listing of all trading partner qualifiers and identifiers assigned to a trading partner in ISA05/06 or ISA07/08. An identifier / qualifier pair can only be assigned to a single trading partner. Referenced by tAgreement table. Populated by the portal on trading partner creation or update |
||
|
Column Name |
Data Type |
Description |
|
Qualifier |
varchar |
Qualifier value from ISA05 / 07 |
|
Identifier |
varchar |
Identifier value from ISA06 / 08 |
|
IsDeleted |
bit |
See Common Columns |
|
tp.tAgreement – Lists all agreements between trading partners by reference to trading partner identifier. Populated by the portal on agreement creation or update |
||
|
Column Name |
Data Type |
Description |
|
AgreementName |
varchar |
Friendly name of the agreement, shown as the agreement name in the Portal’s trading partner page |
|
TransactionType |
int |
Not used |
|
ElementDelimiter |
varchar |
Delimiter between elements, the 4th character in the ISA segment, typically ‘*’ |
|
SegmentDelimiter |
varchar |
Delimiter between segments, ISA15, typically ‘:’ |
|
IsDeleted |
bit |
See Common Columns |
|
trk.tResubmissionAudit – Shows each EDI transaction resubmitted through the Portal’s resubmission process. A record is added for each change detected between the original file and resubmitted file. Useful for audit and tracking purposes beyond what is shown in the tInboundEdi table |
||
|
Column Name |
Data Type |
Description |
|
LoadDate |
datetime |
Date of resubmission |
|
LineText |
varchar |
Stores raw EDI line that was altered. |
|
ChangeType |
varchar |
Type of change performed in the resubmission for a specific line. |
|
LineNum |
int |
Line number of change detected within the file |
|
LoadMachine |
varchar |
Computer name of originator of resubmission |
Rule Management
|
vld.tRuleType – Type of rule supported by the services. Each rule type represents a category of rules in the Portal’s rule page. |
||
|
Column Name |
Data Type |
Description |
|
RuleType |
int |
Primary key of rule |
|
Description |
varchar |
See Common Columns |
|
IsEnabled |
bit |
See Common Columns |
|
IsReadOnly |
bit |
See Common Columns |
|
LoadMachine |
varchar |
See Common Columns |
|
vld.tValidationRuleset – table description here |
||
|
Column Name |
Data Type |
Description |
|
RuleType |
int |
Reference to tRuleType table categorizing the individual rule |
|
Description |
varchar |
See Common Columns |
|
IsEnabled |
bit |
See Common Columns |
|
IsReadOnly |
bit |
See Common Columns |
|
LoadMachine |
varchar |
See Common Columns |
|
vld.tAgreementRules – Mapping between an agreement in tAgreement and tRuleType. Managed by the Portal’s Rule page |
||
|
Column Name |
Data Type |
Description |
|
AgreementId |
int |
Reference to an agreement in tAgreement table |
|
RuleId |
Int |
Reference to a rule in tValidationRuleSet table |
|
IsEnabled |
bit |
See Common Columns |
|
LoadMachine |
varchar |
See Common Columns |
|
vld.tX12TransactionSetRules – Mapping between a transaction set in tX12TransactionSet and tRuleType. Managed by the Portal’s Rule page |
||
|
Column Name |
Data Type |
Description |
|
ST01 |
Int |
ST01 and GS08 for which the rule is applicable to. There is a ST01 and GS08 pair for each version and rule |
|
GS08 |
varchar |
ST01 and GS08 for which the rule is applicable to. There is a ST01 and GS08 pair for each version and rule |
|
RuleId |
Int |
Reference to a rule in tValidationRuleSet table |
|
IsEnabled |
bit |
See Common Columns |
|
LoadMachine |
varchar |
See Common Columns |
|
|
||
|
vld.tX12ValidationResult – Stores the result of rule execution performed by the Tctep Validation Service. Used within the vGetEdiFileInfo SQL view in the Portal |
||
|
Column Name |
Data Type |
Description |
|
LineNum |
int |
Line number within the file of validation error |
|
StLineNum |
int |
Line number within the parent transaction (ST) of the error |
|
LoopName |
varchar |
Name of the X12 Loop containing the error. |
|
SegName |
varchar |
Name of the X12 Segment containing the error. |
|
X12Order |
int |
Ordinal position of the segment |
|
PositionInSegment |
int |
Ordinal position of the element within the segment |
|
PositionInComponent |
int |
Ordinal position of the component within the element |
|
PositionInRepeater |
int |
Not used |
|
BadDataElement |
varchar |
Raw text value that was found to be invalid |
|
ErrMsg |
varchar |
Friendly message detailing the error. |
|
Ack999Code |
varchar |
Single digit Ack code representing the severity of the error |
|
Severity |
varchar |
Descriptive severity of the error |
|
Resolved |
bit |
1 if resubmission through the portal resulted in resolution of the validation error. 0 if no resubmission has occurred |
|
RuleType |
int |
Reference to tRuleType table presenting type of rule executed |
|
LoadMachine |
varchar |
See Common Columns |
Transactional Tables and EDI Storage
|
x12.tInboundEdi – Stores raw EDI text and metadata of a snapshot of the EDI transaction at each stage of processing within a route |
||
|
Column Name |
Data Type |
Description |
|
LastMachine |
varchar |
See Common Columns |
|
OriginalFileName |
varchar |
File name or source of EDI transaction |
|
InboundUri |
varchar |
Full file path, uri, or connection string of EDI source |
|
ISA_String |
varchar |
Raw ISA string of EDI transaction |
|
SegmentTerminator |
char |
ISA16 value |
|
CR |
bit |
Carrage return character used for parsing |
|
LF |
bit |
Line feed character used for parsing |
|
EdiData |
varbinary |
SQL FileStream raw EDI data |
|
RecordCount |
bigint |
Count of records in file if an tEdiSearchField for the ST01/GS08 pair was found with IsRecordIndicator = 1 |
|
ChildMessage |
bit |
Differentiates between a parent EDI transaction and each subsequent message spawned during processing, such as acks |
|
LoadMachine |
varchar |
See Common Columns |
|
x12.tPoisonMessage – Copy of the content in tInboundEdi persisted if the EDI transaction failed processing |
||
|
Column Name |
Data Type |
Description |
|
OriginalFileName |
varchar |
File name or source of EDI transaction |
|
InboundUri |
varchar |
Full file path, uri, or connection string of EDI source |
|
FirstChunk |
nvarchar |
Raw EDI text snippet |
|
FileData |
varbinary |
SQL FileStream storage of EDI |
|
LoadMachine |
varchar |
See Common Columns |
|
x12.tGs – Stores GS loop information for a record in the tInboundEdi table |
||
|
Column Name |
Data Type |
Description |
|
InboundEdiId |
Bigint |
Reference to EDI transaction represented by the current GS record |
|
GS_String |
varchar |
Raw string representation of the entire GS segment |
|
GS01 – GS08 |
varchar |
GS01 through GS08 values parsed from the GS string each have their own columns in the table |
|
LoadMachine |
varchar |
See Common Columns |
|
x12.tSt – Stores ST loop information for a record in the tInboundEdi table |
||
|
Column Name |
Data Type |
Description |
|
GsId |
Int |
Reference to parent GS loop in the tGs table |
|
ST01 |
char |
ST01 value |
|
ST02 |
Varchar |
ST02 value |
|
ST03 |
Varchar |
ST03 value |
|
LoadMachine |
varchar |
See Common Columns |
|
x12.tTConnectConnectionString – Lookup table for the TConnnectEDIXXX connection string of the EDI persistence database. This is used within the TConnect services, and found by looking up ST01, GS08 and Ack code |
||
|
Column Name |
Data Type |
Description |
|
X12AcknowledgementCode |
char |
Single digit ack code, reference to tX12AcknowledgementCode, part of composite key |
|
ST01 |
Int |
ST01 value, part of composite key |
|
GS08 |
Varchar |
GS08 value, part of composite key |
|
ConnectionString |
varchar |
Connection string of SQL database to store EDI files with specific ST01, GS08 and Ack code. |
|
LoadMachine |
varchar |
See Common Columns |
Deprecated Tables
|
pol.tPollingStatusType – Not used |
||||
|
Column Name |
Data Type |
Description |
||
|
Name |
varchar |
Name of polling status type field |
||
|
|
||||
|
pol.tPollingType – Not used |
||||
|
Column Name |
Data Type |
Description |
||
|
PollingTypeName |
varchar |
Not used |
||
|
|
||||
|
pol.tRouteReceiveStage – Not used |
||||
|
Column Name |
Data Type |
Description |
||
|
EdiData |
varbinary |
Not Used |
||
|
ext.tSampleDestination – Not used |
||||
|
Column Name |
Data Type |
Description |
||
|
FileName |
varchar |
File name of file sent |
||
|
FileData |
varchar |
EDI text of file |
||
|
sched.tMsgSchedule – Not used |
||
|
Column Name |
Data Type |
Description |
|
deadlineDayWeek |
Int |
Not used |
|
deadlineTime |
Time |
Not used |
|
|
||
|
sched.tReports – Not used |
||
|
Column Name |
Data Type |
Description |
|
rptMsg |
varchar |
Not used |
|
rptTimestamp |
datetime |
Not used |
|
svc.tSendService – Not used |
||
|
Column Name |
Data Type |
Description |
|
PublicAddress |
nvarchar |
Not used |
|
ServiceConfig |
nvarchar |
Not used |
|
ServiceConfig JSON* |
|
Not used |
|
vld.tICD10CM – Not used |
||
|
Column Name |
Data Type |
Description |
|
DiagCode |
varchar |
|
|
DiagDescription |
varchar |
|
|
LoadMachine |
varchar |
See Common Columns |