Knowledge Base Knowledge Base Operations Other

            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

               

            Updated: 30 Aug 2017 10:45 AM
            Helpful?  
            Help us to make this article better
            0 0