TctepDb
Querying Data
The following query will show every step executed across all routes. Refer to the TctepDb Data Dictionary for details on the fields within the database.
select * from TctepDb.x12.tInboundEdi
It is worth calling out a few important fields:
- InboundEdiId - the id of a file processed within a step in the route.
- ParentEdiId - the InboundEdiId of the initial file processed in a route, usually within a receive step.
- InterchangeId - a GUID representing an individual route's execution of a file
The following query will show all validation failures encountered. This shows the same validation results as seen in the Portal, along with additional detail.
select * from TctepDb.vld.tX12ValidationResult
TConnectEDI Databases
Querying Data
Each TConnect EDI database, such as TConnectEDI837P and TConnectEDI834, contain a set of general artifacts that surface a consistent set of functionality across all TConnect databases.
The EDIFileInformation table shows every file loaded into the TConnectEDI databases. The ExternalCorrelationToken field represents the InterchangeId of the route and PersistStep that processed the EDI file.
select * from TConnectEDI837P.EDI837P.EDIFileInformation
Extracting and Outbounding Data
The usp_GetEdiText stored procedure creates an EDI file based by joining all of the loops and segment tables joining up to the parent EdiFileInformation table for the given EdiFileId integer. Shown as '9' below.
exec TConnectEDI837P.dbo.usp_GetEdiText 9
The above stored procedure will create an EDI file as a result set containing EDI text, automating the process of outbounding EDI is easy when using the EDI Gateway. The TConnect DB Adapter automates outbounding using routes.
To queue an outbound file, the following script can be used to seed a record into the OutboundStage table. The EdiFileId inserted into the OutboundStage table is used to generate an EDI file when the TConnect DB Adapter step executes the usp_GetOutboundBatch stored procedure.
Use TConnectEDI837P
GO
DECLARE @batchId INT = 1
DECLARE @ediFileId INT = 1
INSERT INTO dbo.OutboundStage (BatchStatus, InterchangeId, BatchID, RecordID, CreatedDate, TransactionType, EDIReceivingPartyName, EDISendingPartyName)
VALUES ('READY', NEWID(),@batchId, @ediFileId,GETDATE(), '837P', '','')
TConnectEDI837P
There is a view within the TConnectEDI837P database that aggregates many of the primary tables within the claim database to provide a basic view of the submitter, provider, subscriber, patient, claim and service lines.
SELECT * FROM [TConnectEDI837P].[EDI837P].[vwConsolidatedClaim]
Correlating Between Tctep and TConnectEDI Databases
Below is a script that will join the search fields in TctepDb to the claims in the TConnectEDI837P Db. It will show all claims along with the date processed (CreateDate) and REF*D9 (ValType of ReceiverCode) from the tEdiSearchField table. Additional ValTypes can be added into the where clause at the end of the script.
USE [TConnectEDI837P]
GO
CREATE OR ALTER VIEW [EDI837P].[vwConsolidatedClaim] AS
SELECT
EIF.EDIFileID
, EIF.externalCorrelationToken
, EIF.ISA06 AS ISA06
, EIF.ISA08 AS ISA08
, EIF.ISA09 AS FileReceiveDate
, EIF.ISA10 AS FileReceiveTime
, '837P' AS ClaimType
, [1000].EDI837P_BeginningofHierarchicalTransaction_BHT04 AS LogDate
, EDI837P_L1000A_SubmitterName_NM103 AS Submitter
, EDI837P_L2010AA_BillingProviderName_NM103 AS BillingProviderLastName
, EDI837P_L2010AA_BillingProviderName_NM104 AS BillingProviderFirstName
, EDI837P_L2010AA_BillingProviderName_NM109 AS BillingProviderNPI
, EDI837P_L2010AA_BillingProviderTaxIdentification_REF02 AS BillingProviderTaxID
, EDI837P_L2010AB_Pay_toAddress_Address_N301 AS PayToAddress1
, EDI837P_L2010AB_Pay_toAddress_Address_N302 AS PayTo_Addr2
, EDI837P_L2010AB_Pay_toAddressCity_State_ZIPCode_N401 AS PayToCity
, EDI837P_L2010AB_Pay_toAddressCity_State_ZipCode_N402 AS PayToState
, EDI837P_L2010AB_Pay_toAddressCity_State_ZipCode_N403 AS PayToZip
, EDI837P_L2010AA_BillingProviderAddress_N301 AS BillingAddress1
, EDI837P_L2010AA_BillingProviderAddress_N302 AS BillingAddress2
, EDI837P_L2010AA_BillingProviderCity_State_ZipCode_N401 AS BillingCity
, EDI837P_L2010AA_BillingProviderCity_State_ZipCode_N402 AS BillingState
, EDI837P_L2010AA_BillingProviderCity_State_ZIPCode_N403 AS BillingZip
, EDI837P_L2010BA_SubscriberName_NM103 AS SubscriberLastName
, EDI837P_L2010BA_SubscriberName_NM104 AS SubscriberFirstName
, EDI837P_L2010BA_SubscriberName_NM105 AS SubscriberMiddleInitial
, EDI837P_L2010BA_SubscriberDemographicInformation_DMG03 AS SubscriberGender
, EDI837P_L2010BA_SubscriberName_NM109 AS SubscriberPrimaryIdentifier
, EDI837P_L2010BA_SubscriberDemographicInformation_DMG02 AS SubscriberDOB
, ISNULL(EDI837P_L2010BA_SubscriberAddress_N301,'') + ' '
+ ISNULL(EDI837P_L2010BA_SubscriberAddress_N302,'') AS SubscriberAddress
, EDI837P_L2010BA_SubscriberCity_State_ZIPCode_N401 AS SubscriberCity
, EDI837P_L2010BA_SubscriberCity_State_ZIPCode_N402 AS SubscriberState
, EDI837P_L2010BA_SubscriberCity_State_ZIPCode_N403 AS SubscriberZIP
, EDI837P_L2010CA_PatientName_NM103 AS PatientLastName
, EDI837P_L2010CA_PatientName_NM104 AS PatientFirstName
, EDI837P_L2010CA_PatientName_NM105 AS PatientMiddleInitial
, EDI837P_L2010CA_PatientDemographicInformation_DMG03 AS PatientGender
, EDI837P_L2010CA_PatientDemographicInformation_DMG02 AS PatientDOB
, ISNULL(EDI837P_L2010CA_PatientAddress_N301,'') + ' '
+ ISNULL(EDI837P_L2010CA_PatientAddress_N302,'') AS PatientAddress
, EDI837P_L2010CA_PatientCity_State_ZIPCode_N401 AS PatientCity
, EDI837P_L2010CA_PatientCity_State_ZIPCode_N402 AS PatientState
, EDI837P_L2010CA_PatientCity_State_ZIPCode_N403 AS PatientZip
, EDI837P_L2010BA_SubscriberSecondaryIdentification_REF02 AS SubscriberSuppleMentalIdentifier
, EDI837P_PayerClaimControlNumber_REF02 AS PayerControllNumber
, EDI837P_ClaimIdentifierForTransmissionIntermediaries_REF02 AS ClaimIdentRef02
, [2320].EDI837P_L2330B_OtherPayerClaimControlNumber_REF02 AS OtherPayerControl
, EDI837P_Date_ServiceDate_DTP03 AS DateofService
, [2300].EDI837P_ClaimInformation_CLM01 AS CLM01ControlNumber
, [2300].EDI837P_ClaimInformation_CLM02 AS CLM02ClaimAmt
, EDI837P_ClaimInformation_CLM07 AS CLM07MedicareAssignment
, EDI837P_ClaimInformation_CLM08 AS CLM08BenefitAssignment
, EDI837P_OtherInsuranceCoverageInformation_OI03 AS OI03OtherInsuranceCoverage
, EDI837P_PayerClaimControlNumber_REF02 AS DCN
, EDI837P_L2310B_RenderingProviderSpecialtyInformation_PRV03 AS ProviderTaxonomy
, EDI837P_L2310B_RenderingProviderName_NM103 AS RenderingProviderLastName
, EDI837P_L2310B_RenderingProviderName_NM104 AS RenderingProviderFirstName
, EDI837P_L2310B_RenderingProviderName_NM109 AS RenderingProviderNPI
, [2400].EDI837P_ProfessionalService_SV101_02 AS LineItemProcedureCode
, [2400].EDI837P_ProfessionalService_SV102 AS LineItemAmount
FROM
EDI837P.EDI837P_L1000_Loop [1000] WITH (NOLOCK)
INNER JOIN EDI837P.EDIFileInformation EIF WITH (NOLOCK) ON [1000].EDIFILEID = EIf.edifileid
INNER JOIN EDI837P.EDI837P_L2000A_Loop [2000A] WITH (NOLOCK) ON [1000].EDI837P_L1000_00501_837_P_ID = [2000A].EDI837P_L1000_00501_837_P_ID
INNER JOIN EDI837P.EDI837P_L2000B_Loop [2000B] WITH (NOLOCK) ON [2000A].[EDI837P_L2000A_ID]=[2000B].[EDI837P_L2000A_ID]
LEFT JOIN EDI837P.EDI837P_L2010BB_REF_PayerSecondaryIdentification [2000BR] WITH (NOLOCK) ON [2000B].[EDI837P_L2000B_ID]=[2000BR].[EDI837P_L2000B_ID]
LEFT JOIN EDI837P.EDI837P_L2000C_Loop [2000C] WITH (NOLOCK) ON [2000C].[EDI837P_L2000B_ID]= [2000B].[EDI837P_L2000B_ID]
INNER JOIN
(
SELECT
l2300.*
, cl2300.EDI837P_L2000A_ID
FROM EDI837P.EDI837P_L2300_Loop l2300 WITH (NOLOCK)
INNER JOIN EDI837P.EDI837P_L2300_ClaimLookup cl2300 WITH (NOLOCK) ON l2300.EDI837P_L2300_ClaimLookup_ID = cl2300.EDI837P_L2300_ClaimLookup_ID
) [2300] ON [2000A].[EDI837P_L2000A_ID] = [2300].[EDI837P_L2000A_ID]
LEFT JOIN EDI837P.EDI837P_L2320_Loop [2320] WITH (NOLOCK) ON [2300].[EDI837P_L2300_ID]= [2320].[EDI837P_L2300_ID]
LEFT JOIN EDI837P.EDI837P_L2400_Loop [2400] WITH (NOLOCK) ON [2300].[EDI837P_L2300_ID] = [2400].[EDI837P_L2300_ID]
GO
USE TctepDb
GO
DECLARE @OperationTypeId INT
SET @OperationTypeId = (SELECT TOP 1 OperationTypeId from rt.tRouteOperationType t WHERE t.ClassName = 'PersistStep') -- only show files that went through the 'PersistStep'
select top 1000
i.InterchangeId, --Guid shown in the address bar of the portal
i.CreateDate, --timestamp of when TConnect inserted the file
idx.InboundEdiId,
Val, -- The value corresponding to the ValType, matches the [Element Value] box in the Portal Message page
ValType, -- The type corresponding to 'val' column. This matches the [Element Name] drop down box in the Portal Message page
v.* --Show all of the claim view
from x12.tEdiSearchIndex idx
INNER JOIN x12.tInboundEdi i
on idx.InboundEdiId = i.ParentEdiId
INNER JOIN rt.tRouteOperation r
on i.RouteOperationId = r.OperationId
INNER JOIN TConnectEDI837P.EDI837P.EDIFileInformation fi
on fi.externalCorrelationToken = i.InterchangeId
INNER JOIN TConnectEDI837P.EDI837P.vwConsolidatedClaim v
on fi.EDIFileID = v.EDIFileID
where
ValType = 'ReceiverCode' --tEdiSearchField Column [ElementName] of 'ReceiverCode' exists by default pointing to Loop 2300 REF*D9
and idx.GS08 = '005010X222A1' -- GS08 value for 837P file
AND r.OperationTypeId = @OperationTypeId