SELECT
    d.krevenueelement,
    NVL(d.ktransactionline, tl.nuniquekey) AS ktransactionline,
    d.raveragebillingfxrate AS rBillingRate,
    d.raveragerevrecrate AS rRevenueRate
FROM
    revreclassificationdetail d
JOIN (
    SELECT
        dr.ktransactionline,
        dr.krevenueelement,
        dr.kperiod,
        dr.reclassdetailkey
    FROM (
        SELECT
            rd.ktransactionline,
            rd.krevenueelement,
            filterperiod.nkey AS kperiod,
            MAX(rd.nkey) KEEP (DENSE_RANK LAST ORDER BY rd.dcreated) AS reclassdetailkey
        FROM
            revreclassificationdetail rd
        JOIN
            accountingperiod reclassperiod ON rd.kperiod = reclassperiod.nkey
        JOIN
            accountingperiod filterperiod ON filterperiod.nkey IN (" + NLStringUtil.join(accountingPeriods, ",") + ")
        LEFT JOIN
            tranline tl ON rd.krevenueelement = tl.krevenueelement
        WHERE
            filterperiod.dstart >= reclassperiod.dstart
            AND filterperiod.bisyear = 'F'
            AND filterperiod.bisadjust = 'F'
            AND filterperiod.btaxperiod = 'F'
            AND filterperiod.binactive = 'F'
            AND filterperiod.bisquarter = 'F'
            AND rd.kaccountingbook = sys_context('nl_user_info', 'kBook')
            AND rd.krevenueelement IS NOT NULL
            AND NOT EXISTS (
                SELECT 1
                FROM reclassmergedelements me
                JOIN accountingperiod meap ON me.kperiod = meap.nkey
                WHERE rd.krevenueelement = me.krevenueelement
                AND (
                    meap.dstart > reclassperiod.dstart
                    OR (meap.nkey = reclassperiod.nkey AND me.dcreated > rd.dcreated)
                )
                AND meap.dstart <= filterperiod.dstart
            )
        GROUP BY
            rd.ktransactionline,
            rd.krevenueelement,
            filterperiod.nkey
    ) dr
    LEFT JOIN revreclassificationdetail d ON dr.reclassdetailkey = d.nkey
    LEFT JOIN tranline tl ON dr.krevenueelement = tl.krevenueelement
    WHERE
        dr.kperiod IN (
            SELECT MAX(nKey) KEEP (DENSE_RANK LAST ORDER BY dstart)
            FROM accountingperiod
            WHERE nkey IN (" + NLStringUtil.join(accountingPeriods, ",") + ")
            AND bIsQuarter = 'F'
            AND bIsYear = 'F'
            AND bTaxPeriod = 'F'
            AND bIsAdjust = 'F'
        )
        AND d.krevenueelement IS NOT NULL
) drf ON drf.reclassdetailkey = d.nkey
LEFT JOIN
    tranline tl ON d.krevenueelement = tl.krevenueelement
WHERE
    d.krevenueelement IS NOT NULL;