How to Create a CM EDI RT171 Using SQL Sync
Goal
The goal of this document is to demonstrate how to create a query to be used with SQL Server Management Studio to find “Swap Interest Stamp 90.0900” on a specific date, and to use the information stored in FInan to create a CM EDI Record 171.
Global Interest Swap
Swap Interest Stamp is accomplished by running 2-S-5 from the main menu and selecting the claims and interest rates as appropriate.
If you run the program by mistake or with the wrong values, you may change claims by mistake. This is a volume tool so it could affect many thousands of claims.
Interest Stamp 90.09100
You can see an example where the claim was updated using the Global Interest Swap program.
CM EDI Record 171
Collection-Master CM EDI Record 171 may update interest values on claims in Collection-Master. The above example was created using the following SQL Server Query:
- @Code should be 90.09100 and is the code created by the Global Interest Swap.
- @System_Date should be the date of the stamp and will be used to find the interest information from the Prior Transactions.
- The SQL query finds the interest information from the “Prior Transaction” to the 90.09100 and uses that information to create a Record 171 entry.
Declare @Code as Varchar(8)
Declare @System_Date as Varchar(10)
Set @Code=’90.09100′
Set @System_Date=’2022-06-24′
Select *
Into #Stamp
FROM
( Select Max([Record_Number]) as [Record_Number],fileno
FROM [dbo].[FINAN] with (Nolock)
Where [code]=@Code and system_date=@System_DaTE
GROUP by FILENO) Stamp
–Select * from #Stamp
Select *
Into #Prior
From
(
Select MAX(Finan.Record_Number) as Record_Number,Finan.Fileno
FROM [dbo].[FINAN] with (Nolock)
Inner Join #Stamp
On Finan.Fileno=#Stamp.Fileno
and Finan.Record_Number<#Stamp.Record_Number
GROUP BY Finan.Fileno
) PRIOR
— Select * from #Prior
Select *
Into #CM171
From
(
Select FINAN.*
FROM [dbo].[FINAN] with (Nolock)
Inner Join #Prior
On Finan.Fileno=#Prior.Fileno
and Finan.Record_Number=#Prior.Record_Number
) CM171
Select
‘171’ as [171]
,’D’ as [H]
,#CM171.FILENO as [FIRM_FILENO]
,#CM171.Trans_Date as [INT_DATE]
,Case
When BPJ=” and master.JMT_DATE!=” THEN ”
When BPJ!=’J’ THEN cast(#CM171.PER_DIEM_RATE as Varchar(20))
ELSE ”
END as PRE_J_RATE
,Case
When BPJ=” and master.JMT_DATE!=” THEN cast(#CM171.PER_DIEM_RATE as Varchar(20))
When BPJ=’J’ THEN cast(#CM171.PER_DIEM_RATE as Varchar(20))
ELSE ”
END as POST_J_RATE
,#CM171.[Int]as [Stored_INT] — -[TOT_INTEREST]
,’#’ as [#]
from #CM171
inner join [dbo].[MASTER]
on #cm171.fileno=master.fileno
— where bpj=” and PRE_J_RATe>0
Drop Table #Stamp
Drop Table #Prior
Drop Table #CM171
Using Record 171 in Collection-Master
Once you have created a text file with the Record 171 information, you can import the information into Collection-Master using CM EDI. Remember that you can only process Open or Closed claims at any given time, so it’s OK to run the import for Open claims, and it will reject the closed claims. And then run the import for Closed claims, and it will reject the Open claims. Advanced Users, please feel free to enhance the SQL query to filter by Open or Closed status.