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