Answer: There are several ways to create a Data Transformation process. One way is a SQL script using the database's innate programming language. The main trick in our table structure is the AutoID column that is used in the detail table as foreign key. This means that after the header record is written, we need to ask the database for the ID it just assigned to the new record. Then we can take this ID and write it in the detail record.
Here is a sample SQL script that populates HIPAA Claim Master's tables with data from a fictitious other table. Just three fields are used to show the principle.
declare mycur cursor for select ID from OldTable
declare @iid int
fetch next from mycur into @iid
while @@fetch_status = 0
DECLARE @DataID bigint;
INSERT INTO dbo.EDI_Claims (TradingPartnerID, BillProvLast)
(SELECT TradingPartnerID, BillProvLast FROM OldTable WHERE OldTable.ID = @iid);
SELECT @DataID = scope_identity();
insert into EDI_ClaimDetail (ClaimID, LineNumber, Amount) (SELECT @DataID, '1', c.Amount FROM OldTable as c
WHERE c.ID = @iid);
fetch next from mycur into @iid ;
If I put the same file in the inbox folder and have it process it to a database, will it export the same data to the database twice?
The optional EDI Exchange module tracks every file that is processed in a database in a table called TransLog.
EDI Exchange checks before processing that the product of trading partner ID and filename is unique. If the file has been processed before, you will receive a message and the file will be moved to the Suspend directory.
NOTE: Without EDI Exchange, there is nothing preventing you from loading the same file multiple times.
Solution: Download the latest version. All our products have been updated to a new version. Sometimes you will need the full install. The .Net frame work needs to be updated and the ordinary Microsoft Installer (*.msi) files does not do this update automatically. The full install has a zip file with Setup.exe program which checks for all prerequisites and the standard .msi file. This Setup.exe executable will verify or install the .Net Framework 4.6.1 and other C++ libraries that we now upgraded to.
- Uninstall previous version. You will not lose any of your settings.
- You might even delete the folder C:\Program Files\HIPAAsuite\[hipaasuite product]
- Download the zip file with the Setup.exe program. This Setup.exe executable will verify or install the .Net Framework 4.6.1 and other C++ libraries that we upgraded to.
- Setup.exe will then perform the installation and from there on no further problems should persist.
There are two possibilities.
- The file was processed twice. Though we prevent the processing of the same filename by the same trading partner. If the file is renamed the claims will be imported and duplicated.
- The claim was submitted twice by the provider, then these identical claims have different file names. and maybe a different claimFilingCode.