# Incident 7799

[Cases 7799: NCE-800 Datafejl i anvendt på aftale efter fix af migrationsproblemer](https://goto.netcompany.com/cases/GTE787/SYGNYFK/Lists/Tasks/DispForm.aspx?ID=7799&ContentTypeId=0x010800CB5F8BCD73E7AC4AADB8CC9BE4F48A15)
[Cases 6454: NCE-534 1 police fejler i kassation af policer](https://goto.netcompany.com/cases/GTE787/SYGNYFK/Lists/Tasks/DispForm.aspx?ID=6454)

## Described issue in case

- Person_id: 6b7b276a-b74d-3e18-0000-000000003932
- Old Police_id: 85ac6cc7-9fe8-38fa-0000-000000024261
- Old Police_nr: 00148065
- New Police_id: 85ac6cc7-9fe8-38fa-0000-00000000374c
- New Police_nr: 00014156


In `anvendt_paa_aftale` the old police_id is used instead of the new one.


SELECT
    person_id,
    apa.police_id,
    start_date,
    end_date,
    apa.anvendt_forsikringssum,
    apa.anvendt_selvrisiko,
    apa.aftale_forsikringsaar_id
FROM
    sd.anvendt_paa_aftale AS apa
INNER JOIN
    sd.police AS police ON police.id = apa.police_id
WHERE
    police.person_id = '6b7b276a-b74d-3e18-0000-000000003932'
ORDER BY
    start_date;


The query below outputs all the tilskuds displayed in person's Tilskud page. The first incosistency here is that `apa_police_id` differs from `rg_police_id`.

SELECT
    anvendt_forsikringssum,
    anvendt_selvrisiko,
    apa.police_id AS apa_police_id,
    rg.police_id AS rg_police_id,
    tilskud_id,
    regningslinje_id,
    regning_id
FROM
    sd.anvendt_paa_aftale AS apa
INNER JOIN
    sd.tilskudsberegning AS tb ON apa.id = tb.anvendt_paa_aftale_id
INNER JOIN
    sd.regningslinje AS rg ON rg.id = tb.regningslinje_id
INNER JOIN
    sd.police AS police ON police.id = apa.police_id
WHERE
    police.person_id = '6b7b276a-b74d-3e18-0000-000000003932';


- `anvendt selvrisiko` should equal the sum of `police_nedsaettelse_selvrisiko`
- `anvendt forsikringssum` should equal the sum of `beregnet_tilskud`

## Investigate other inconsistencies

Select all individuals who have
- `anvendt_paa_aftale` and `regningslinje` `police_id` mismatch OR
- `anvendt_selvrisiko` != SUM(tb.`police_nedsaettelse_selvrisiko`) OR
- `anvendt_forsikringssum` != SUM(t.`beregnet_tilskud`)

SELECT DISTINCT
        police.person_id
    FROM sd.anvendt_paa_aftale AS apa
    INNER JOIN sd.tilskudsberegning AS tb
        ON apa.id = tb.anvendt_paa_aftale_id
    INNER JOIN sd.regningslinje AS rg
        ON rg.id = tb.regningslinje_id
    INNER JOIN sd.tilskud AS t
        ON rg.id = t.regningslinje_id
    INNER JOIN
        sd.police AS police ON police.id = apa.police_id
    GROUP BY 
        apa.id,
        apa.police_id,
        rg.police_id,
        anvendt_forsikringssum,
        anvendt_selvrisiko,
        person_id
    HAVING
        apa.police_id != rg.police_id OR
        anvendt_selvrisiko != SUM(tb.police_nedsaettelse_selvrisiko) OR
        anvendt_forsikringssum != SUM(t.beregnet_tilskud)


So, there are 12 people in total with incosistent values.

### anvendt_paa_aftale and regningslinje police_id mismatch

Select all people where `anvendt_paa_aftale.police_id` != `regningslinje.police_id`.

SELECT DISTINCT
    person_id,
    apa.police_id,
    apa.oprettetaf
FROM sd.anvendt_paa_aftale AS apa
INNER JOIN sd.tilskudsberegning AS tb
    ON apa.id = tb.anvendt_paa_aftale_id
INNER JOIN sd.regningslinje AS rg
    ON rg.id = tb.regningslinje_id
INNER JOIN sd.police as police
    ON police.id = apa.police_id
WHERE apa.police_id != rg.police_id

Run on PREPROD, the query above outputs:

## Solution

1. Define all individuals which have `anvendt_paa_aftale` and `regningslinje` `police_id` mismatch. According to [investigation](https://hackmd.io/7LYix-i4QFGGND1a6zKwvw?both#anvendt_paa_aftale-and-regningslinje-police_id-mismatch), they are the ones with person ids:

- 6b7b276a-b74d-3e18-0000-000000000485
- 6b7b276a-b74d-3e18-0000-0000000014d7
- 6b7b276a-b74d-3e18-0000-000000003396
- 6b7b276a-b74d-3e18-0000-000000003932

2. For each person with `anvendt_paa_aftale` and `regningslinje` `police_id` mismatch, fix the data. To do that, compare `regningslinje.behandling_start/end` to `police_start/end` of `anvendt_paa_aftale` and `aftale_forsikringsaar.gyldigFra/Til`. Set the `regningslinje.anvendt_paa_aftale_id` with the correct `anvendt_paa_aftale` reference, where the dates are active.

    SELECT
        apa.id as apa_id,
        police.start_date as police_start,
        police.end_date as police_end,
        apa.police_id AS apa_police_id,
        rg.police_id AS rg_police_id,
        rg.behandling_start as rg_behandling_start,
        rg.behandling_slut as rg_behandling_slut,
        af.gyldig_fra,
        af.gyldig_til,
        tb.id
    FROM
        sd.anvendt_paa_aftale AS apa
    INNER JOIN
        sd.aftale_forsikringsaar AS af ON af.id = apa.aftale_forsikringsaar_id
    INNER JOIN
        sd.tilskudsberegning AS tb ON apa.id = tb.anvendt_paa_aftale_id
    INNER JOIN
        sd.regningslinje AS rg ON rg.id = tb.regningslinje_id
    INNER JOIN
        sd.police AS police ON police.id = apa.police_id
    WHERE
        police.person_id = 'PERSON_ID'
    ORDER BY 
        police_start;

### Person 6b7b276a-b74d-3e18-0000-000000000485


With blue are highlighted the records with mismatched `anvendt_paa_aftale` and `regningslinje` `police_id`. To define the correct `apa_id`, search for `police_start/end` and `gyldig_fra/til` that are active for `behandling_start/end` (highlighted with red). This gives the right `apa_id`, highlighted with green. And the SQL query to update this:

    UPDATE sd.tilskudsberegning
    SET
        anvendt_paa_aftale_id = '1aa9431c-a8be-3db6-0000-0000000225d9',
        aendret                = current_timestamp,
        aendretaf              = 'NC ANGPA SYGNYFK-7799'
    WHERE
        id = 'bae17759-7b2a-3328-0000-0000000305ca' AND
        id = 'bae17759-7b2a-3328-0000-0000000305cb' AND
        id = 'bae17759-7b2a-3328-0000-0000000305cf'

### Person id 6b7b276a-b74d-3e18-0000-0000000014d7


All of the `anvendt_paa_aftale` records associated with this person have the same reference to `aftale_forsikringsaar`:


The SQL query to resolve this:

    UPDATE sd.tilskudsberegning
    SET
        anvendt_paa_aftale_id = '1aa9431c-a8be-3db6-0000-00000002397f',
        aendret                = current_timestamp,
        aendretaf              = 'NC ANGPA SYGNYFK-7799'
    WHERE
        id = (
            SELECT tb.id
            FROM sd.anvendt_paa_aftale AS apa
            INNER JOIN sd.tilskudsberegning AS tb ON apa.id = tb.anvendt_paa_aftale_id
            INNER JOIN sd.regningslinje AS rg ON rg.id = tb.regningslinje_id
            INNER JOIN sd.police AS police ON police.id = apa.police_id
            WHERE tb.anvendt_paa_aftale_id = apa.id
                AND police.person_id = '6b7b276a-b74d-3e18-0000-0000000014d7'
                AND NOT (
                    rg.behandling_start BETWEEN police.start_date AND police.end_date
                    OR rg.behandling_slut BETWEEN police.start_date AND police.end_date
                )
        );

The subquery results to the `tilskudsberegning` ids that need to be updated:

    SELECT tb.id
    FROM sd.anvendt_paa_aftale AS apa
    INNER JOIN sd.tilskudsberegning AS tb ON apa.id = tb.anvendt_paa_aftale_id
    INNER JOIN sd.regningslinje AS rg ON rg.id = tb.regningslinje_id
    INNER JOIN sd.police AS police ON police.id = apa.police_id
    WHERE tb.anvendt_paa_aftale_id = apa.id
        AND police.person_id = '6b7b276a-b74d-3e18-0000-0000000014d7'
        AND NOT (
            rg.behandling_start BETWEEN police.start_date AND police.end_date
            OR rg.behandling_slut BETWEEN police.start_date AND police.end_date
        )


### Person id 6b7b276a-b74d-3e18-0000-000000003396


The SQL query to resolve this:

    UPDATE sd.tilskudsberegning
    SET
        anvendt_paa_aftale_id = '1aa9431c-a8be-3db6-0000-000000025eb1',
        aendret                = current_timestamp,
        aendretaf              = 'NC ANGPA SYGNYFK-7799'
    WHERE
        id = 'bae17759-7b2a-3328-0000-000000033a79';

### Person id 6b7b276a-b74d-3e18-0000-000000003932


The SQL query to resolve this:

    UPDATE sd.tilskudsberegning
    SET
        anvendt_paa_aftale_id = '29e71ae2-2a7c-4ca3-aa15-d459842b2af9',
        aendret                = current_timestamp,
        aendretaf              = 'NC ANGPA SYGNYFK-7799'
    WHERE
        id = 'bae17759-7b2a-3328-0000-000000034149' AND
        id = 'bae17759-7b2a-3328-0000-00000003414a'

3. After the police_id mismatch is resolved by executing the queries in step 2, it should be examined the reason why the aggregate values `anvendt_forsikringssum` and `anvendt_selvrisiko` do not match the desired sums.

- Are they all coming from incident patch?
- Are some of the mismatches from the system itself?

To investigate this, run this SQL query:

    SELECT
        person_id,
        apa.id as apa_id,
        anvendt_forsikringssum,
        t.beregnet_tilskud,
        anvendt_selvrisiko,
        tb.police_nedsaettelse_selvrisiko,
        tb.aendretaf as tb_aendretaf,
        tb.oprettetaf as tb_oprettetaf,
        t.aendretaf as t_aendretaf,
        t.oprettetaf as t_oprettetaf,
        apa.aendretaf as apa_aendretaf,
        apa.oprettetaf as apa_oprettetaf
    FROM
        sd.anvendt_paa_aftale AS apa
    INNER JOIN
        sd.tilskudsberegning AS tb ON apa.id = tb.anvendt_paa_aftale_id
    INNER JOIN
        sd.regningslinje AS rg ON rg.id = tb.regningslinje_id
    inner join sd.tilskud as t
        on rg.id = t.regningslinje_id
    INNER JOIN
        sd.police AS police ON police.id = apa.police_id
    WHERE
        apa.id IN (
            SELECT DISTINCT
                apa.id
            FROM sd.anvendt_paa_aftale AS apa
            INNER JOIN sd.tilskudsberegning AS tb
                ON apa.id = tb.anvendt_paa_aftale_id
            INNER JOIN sd.regningslinje AS rg
                ON rg.id = tb.regningslinje_id
            INNER JOIN sd.tilskud AS t
                ON rg.id = t.regningslinje_id
            GROUP BY 
                apa.id,
                apa.police_id,
                rg.police_id,
                anvendt_forsikringssum,
                anvendt_selvrisiko
            HAVING
                apa.police_id != rg.police_id OR
                anvendt_selvrisiko != SUM(tb.police_nedsaettelse_selvrisiko) OR
                anvendt_forsikringssum != SUM(t.beregnet_tilskud)
        )
    ORDER BY 
        person_id, apa_id, police.start_date;

The query above outputs all of the `anvendt_paa_aftale` records with incorrect aggregate values `anvendt_forsikringssum` and `anvendt_selvrisiko` and the associated way of creation/update, where we can confirm that they all are coming from database patches.

4. Update the `anvendt_forsikringssum` and `anvendt_selvrisiko` to match the sums SUM(`tilskudsberegning.police_nedsaettelse_selvrisiko`) and SUM(`tilskud.beregnet_tilskud`) respectively.

To do so, for each `anvendt_paa_aftale_id` with invalid data, calculate the sums, using the query below:

    SELECT
        apa.id as anvendt_paa_aftale_id,
        COALESCE(SUM(tb.police_nedsaettelse_selvrisiko), 0) AS Sum_tb_police_nedsaettelse_selvrisiko, 
        COALESCE(SUM(t.beregnet_tilskud), 0) AS Sum_t_beregnet_tilskud
    FROM sd.anvendt_paa_aftale AS apa
    INNER JOIN sd.tilskudsberegning AS tb
        ON apa.id = tb.anvendt_paa_aftale_id
    INNER JOIN sd.regningslinje AS rg 
        ON rg.id = tb.regningslinje_id
    INNER JOIN sd.tilskud AS t
        ON rg.id = t.regningslinje_id
    GROUP BY 
        apa.id,
        anvendt_forsikringssum,
        anvendt_selvrisiko
    HAVING
        anvendt_selvrisiko != SUM(tb.police_nedsaettelse_selvrisiko) OR
        anvendt_forsikringssum != SUM(t.beregnet_tilskud)

Run on PREPROD, the query outputs:


which aggrees with the calculated sums using excel:


The query above will be used as a subquery to set the aggregate values:

    UPDATE anvendt_paa_aftale apa
    SET 
        anvendt_selvrisiko = res.sum_selvrisiko, 
        anvendt_forsikringssum = res.sum_forsikringssum, 
        aendret = current_timestamp, 
        aendretaf = 'NC ANGPA SYGNYFK-7799'
    FROM (
        SELECT
            apa.id as anvendt_paa_aftale_id,
            COALESCE(SUM(tb.police_nedsaettelse_selvrisiko), 0) AS Sum_tb_police_nedsaettelse_selvrisiko, 
            COALESCE(SUM(t.beregnet_tilskud), 0) AS Sum_t_beregnet_tilskud
        FROM sd.anvendt_paa_aftale AS apa
        INNER JOIN sd.tilskudsberegning AS tb
            ON apa.id = tb.anvendt_paa_aftale_id
        INNER JOIN sd.regningslinje AS rg 
            ON rg.id = tb.regningslinje_id
        INNER JOIN sd.tilskud AS t
            ON rg.id = t.regningslinje_id
        GROUP BY 
                apa.id,
                anvendt_forsikringssum,
                anvendt_selvrisiko
        HAVING
            anvendt_selvrisiko != SUM(tb.police_nedsaettelse_selvrisiko) OR
            anvendt_forsikringssum != SUM(t.beregnet_tilskud)
    ) AS res
    WHERE 
        apa.id = res.anvendt_paa_aftale_id;

5. After the database patches in steps 2 and 4 are executed on PREPROD, we should investigate if there are people with incorrect paid amounts. To do so,

The anvendt_selvrisiko should be used up completely (equal the aftale_forsikringsaar_selvrisiko* forsikringsaar_skaleringsfaktor) before start using the forsikringssum. If apa.anvendt_selvrisiko != maximum on the aftale_forsikringsaar.selvrisiko, then the person was either deducted too much or too little selvrisiko before we started using forsikringssum.

To determine the individuals with incorrect paid amounts:


1, 4, 6, 7 are close. If they should be corrected might be up to "d".

For how to recalculate, there are two options:

- Retract and recalculate the regnings in TAS, which will trigger a recalculation in esys.
Could be dangerous if these are old migrated regnings that don't exist perfectly in TAS anymore?
- Create retraction and copy for each regning via patch, and create haendelses to start AnlaegTilskud for each regning.
Goes counter to the principle that regnings come from TAS. Need to be careful how the patch is structured so that we are sure that the created retractions and copies match the original regnings that should be recalculated.