Fix transaction amounts with incorrect number of fractional parts

Fix transaction amounts with incorrect number of fractional parts

Use the following instructions to fix transactions with an incorrect number of fractional parts:

  1. Set the @readonly parameter to 1 and run the script. Script will return the transactions with incorrect number of fractional parts, claim balances and statuses. Also the script will return fixed claim balances, statuses, and transaction amounts for preview.
  2. If preview looks correct, please set @readonly parameter to 0 and run the scriptagain to fix the fractional parts.

 

--@readonly = 1 - to check, @readonly = 0 - to fix

 

declare @readonly int = 1

 

begin tran

 

declare @trs table

(

    trans_id int,

    source_table varchar(100),

    amount FLOAT,

    claim_id int

)

 

declare @trs_fixed table

(

    trans_id int,

    source_table varchar(100),

    amount FLOAT,

    claim_id int

)

 

--FIND TRANSACTIONS

 

insert into @trs(trans_id, source_table, amount, claim_id)

SELECT trans_detail_id,'transaction_detail', trans_detail_amnt, claim_id

FROM transaction_detail td WITH(NOLOCK)

WHERE ABS(td.trans_detail_amnt % 1 * 100 % 1) > 0

union all

SELECT trans_detail_id,'transaction_detail_archive' ,trans_detail_amnt, claim_id

FROM transaction_detail_archive td WITH(NOLOCK)

WHERE ABS(td.trans_detail_amnt % 1 * 100 % 1) > 0

union all

SELECT trans_detail_id_preprocessing,'transaction_detail_preprocessing' ,trans_detail_amnt, claim_id

FROM transaction_detail_preprocessing td WITH(NOLOCK)

WHERE ABS(td.trans_detail_amnt % 1 * 100 % 1) > 0

union all

SELECT trans_detail_id,'transaction_info_detail' ,trans_detail_amnt, claim_id

FROM transaction_info_detail td WITH(NOLOCK)

WHERE ABS(td.trans_detail_amnt % 1 * 100 % 1) > 0

union all

SELECT trans_detail_id,'transaction_info_detail_archive' ,trans_detail_amnt, claim_id

FROM transaction_info_detail_archive td WITH(NOLOCK)

WHERE ABS(td.trans_detail_amnt % 1 * 100 % 1) > 0

union all

SELECT claim_detail_id,'transaction_claim_detail',trans_detail_amnt, claim_id

FROM transaction_claim_detail td WITH(NOLOCK)

WHERE ABS(td.trans_detail_amnt % 1 * 100 % 1) > 0

union all

SELECT trans_preclaim_id,'transaction_preclaim',trans_amnt, claim_id

FROM transaction_preclaim td WITH(NOLOCK)

WHERE ABS(td.trans_amnt % 1 * 100 % 1) > 0

 

--SHOW TRANSACTIONS

 

select osbal.claim_id, osbal.description as status, osbal.OS_BALANCE as claim_os_balance, t.trans_id, t.amount, t.source_table

from @trs t

    inner join

    (

                    select t.claim_id, csc.description, SUM(t.trans_detail_amnt) as OS_BALANCE

                    from ttt_vw t with(nolock)

                                    inner join claims c with(nolock) on t.claim_id = c.claim_id

                                    inner join claim_status_codes csc with(nolock) on c.status = csc.status

                    where t.claim_id in(select claim_id from @trs)

                    group by t.claim_id, csc.description

    ) osbal on t.claim_id = osbal.claim_id

order by osbal.claim_id

 

--FIX TRANSACTION AMOUNTS

 

update transaction_detail set trans_detail_amnt = CONVERT(MONEY,ROUND(trans_detail_amnt,2))

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_detail')

 

update transaction_detail_archive set trans_detail_amnt = CONVERT(MONEY,ROUND(trans_detail_amnt,2))

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_detail_archive')

   

update transaction_detail_preprocessing set trans_detail_amnt = CONVERT(MONEY,ROUND(trans_detail_amnt,2))

where trans_detail_id_preprocessing in

    (select trans_id from @trs t where t.source_table = 'transaction_detail_preprocessing')

 

update transaction_info_detail set trans_detail_amnt = CONVERT(MONEY,ROUND(trans_detail_amnt,2))

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_info_detail')

 

update transaction_info_detail_archive set trans_detail_amnt = CONVERT(MONEY,ROUND(trans_detail_amnt,2))

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_info_detail_archive')

 

update transaction_preclaim set trans_amnt = CONVERT(MONEY,ROUND(trans_amnt,2))

where trans_preclaim_id in

    (select trans_id from @trs t where t.source_table = 'transaction_preclaim')

   

update transaction_claim_detail set trans_detail_amnt = CONVERT(MONEY,ROUND(trans_detail_amnt,2))

where claim_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_claim_detail')

   

select 'FIXED TRANSACTION AMOUNTS'

 

--SHOW FIXED TRANSACTION AMOUNTS

 

insert into @trs_fixed(trans_id,amount, claim_id, source_table)

select trans_detail_id, trans_detail_amnt, claim_id  ,'transaction_detail'from transaction_detail

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_detail')

union all

select trans_detail_id, trans_detail_amnt, claim_id,'transaction_detail_archive' from transaction_detail_archive

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_detail_archive')

    union all

select trans_detail_id_preprocessing, trans_detail_amnt, claim_id,'transaction_detail_preprocessing' from transaction_detail_preprocessing

where trans_detail_id_preprocessing in

    (select trans_id from @trs t where t.source_table = 'transaction_detail_preprocessing')

union all

select trans_detail_id, trans_detail_amnt, claim_id,'transaction_info_detail' from transaction_info_detail

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_info_detail')

union all

select trans_detail_id, trans_detail_amnt, claim_id,'transaction_info_detail_archive' from transaction_info_detail_archive

where trans_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_info_detail_archive')

union all

select trans_preclaim_id, trans_amnt, claim_id,'transaction_preclaim' from transaction_preclaim

where trans_preclaim_id in

    (select trans_id from @trs t where t.source_table = 'transaction_preclaim')

union all   

select claim_detail_id, trans_detail_amnt, claim_id,'transaction_claim_detail' from transaction_claim_detail

where claim_detail_id in

    (select trans_id from @trs t where t.source_table = 'transaction_claim_detail')

   

declare @paid_claims_not_in_paid_status table

(

    claim_id int

)

 

--SET PAID STATUS FOR PAID CLAIMS NOT IN PAID STATUS

insert into @paid_claims_not_in_paid_status

select osbal.claim_id

from @trs_fixed t

    inner join

    (

                    select t.claim_id, csc.description, c.status, SUM(t.trans_detail_amnt) as OS_BALANCE

                    from ttt_vw t with(nolock)

                                    inner join claims c with(nolock) on t.claim_id = c.claim_id

                                    inner join claim_status_codes csc with(nolock) on c.status = csc.status

                    where t.claim_id in(select claim_id from @trs)

                    group by t.claim_id, csc.description, c.status

                    having SUM(t.trans_detail_amnt) = 0.0 and c.status != 7

    ) osbal on t.claim_id = osbal.claim_id            

order by osbal.claim_id

 

update c set status = 7

from claims c with(nolock)

    inner join @paid_claims_not_in_paid_status pc on c.claim_id = pc.claim_id

 

select osbal.claim_id, osbal.description as status, osbal.OS_BALANCE as claim_os_balance, t.trans_id, t.amount, t.source_table

from @trs_fixed t

    inner join

    (

                    select t.claim_id, csc.description, SUM(t.trans_detail_amnt) as OS_BALANCE

                    from ttt_vw t with(nolock)

                                    inner join claims c with(nolock) on t.claim_id = c.claim_id

                                    inner join claim_status_codes csc with(nolock) on c.status = csc.status

                    where t.claim_id in(select claim_id from @trs)

                    group by t.claim_id, csc.description

    ) osbal on t.claim_id = osbal.claim_id            

order by osbal.claim_id

   

if @readonly = 1

begin

    rollback tran

end else begin

    commit tran

end

   

    • Related Articles

    • Incomplete primary payer amounts Pmts + Adjst + Pt. Resp must equal charges

      In order to coordinate two or more plans via an electronic claim, the primary payment must have transactions that list the patient's responsibility, how much the primary insurance allowed, and then any payments and adjustments. All these must balance ...

    • How to Fix Claim Awaiting Transaction Processing Error

      Symptom- There no prior or unposted transaction prior to this patient yet whenever you try to post a payment, a message pops up 'This claim was awaiting transaction processing when you pulled up the claim data, but has since finished processing. You ...

    • How to Update an Allowed Amount transaction

      How do I edit the allowed amount transaction that has posted from and ERA or manual payment? From the Claim Transaction Detail Screen for a specific Claim: 1. Click on the Allowed line  (...) 2. In the box that opens below change the amount to ...

    • How to update the Billing Inquiries Phone Number

      How do we update the billing inquires phone number? Please follow these steps: Log in to Provider Suite>Settings Tab>Locate the field:  Billing Inquiries Phone #>Change the Number>Click the Update button. NOTE: You must be an "Account Manager" user ...

    • Original Reference Number (ICN) Search Instructions

      The ICN is pulled from the claim unless there is an ICN from an EOB. This means that for all claims where the payment was posted from an ERA, there is no need to add the ICN in the Original Reference #: field on Claim Final, because it will not ...