SELECT DISTINCT
    t1.kunde_id, t2.navn_upc, t1.fakt_avtaleid, t2.postnr, t4.abm_lopenr, t4.tariffid
FROM
    CUSTOMER.salgsordre t1
INNER JOIN
    CUSTOMER.kunde t2 ON t1.kunde_id = t2.kunde_id
INNER JOIN
    CUSTOMER.change_import t3 ON t1.kunde_id = t3.kunde_id AND t1.abm_lopenr = t3.abm_lopenr
INNER JOIN (
    SELECT ABV2.kunde_id, ABV2.ABM_LOPENR, ABV2.fradato, ABV2.tariffid, ROW_NUMBER() over (PARTITION BY kunde_id,abm_lopenr ORDER BY fradato DESC) AS rn
    FROM customer.ab_versjon ABV2
) t4 ON t1.kunde_id = t4.kunde_id AND t1.abm_lopenr = t4.abm_lopenr AND t4.rn = 1
WHERE
    (TRUNC(TO_DATE(t3.tidspkt, 'DD-MM-YYYY')) = TO_DATE(SYSDATE, 'DD-MM-YYYY')-1
AND
    t1.virksh_id = '2' AND t1.opphorskode NOT IN 'A'
AND
    t2.pers_foret_ind = 'P'
AND
    t3.meldingstype = 'NKU' AND t3.undertype = 'L')
GROUP BY
    t1.kunde_id, t2.navn_upc, t1.fakt_avtaleid, t2.postnr, t4.abm_lopenr, t4.tariffid