Tracking Position Name and Business Unit Changes Over Time

WITH position_param AS (
    SELECT TRIM(REGEXP_SUBSTR(:P_POSITION_CODE, '[^,]+', 1, LEVEL)) AS POSITION_CODE,
           LEVEL AS SORT_ORDER
    FROM dual
    CONNECT BY REGEXP_SUBSTR(:P_POSITION_CODE, '[^,]+', 1, LEVEL) IS NOT NULL
),
latest_position AS (
    SELECT
        hp.*,
        ROW_NUMBER() OVER (PARTITION BY hp.POSITION_ID ORDER BY hp.EFFECTIVE_START_DATE DESC) AS rn
    FROM
        fusion.HR_ALL_POSITIONS_F hp
    WHERE
        :P_EFFECTIVE_DATE BETWEEN hp.EFFECTIVE_START_DATE AND hp.EFFECTIVE_END_DATE
),
latest_name AS (
    SELECT
        hpt1.POSITION_ID,
        hpt1.NAME AS CURRENT_POSITION_NAME,
        hpt1.EFFECTIVE_START_DATE AS CHANGE_EFFECTIVE_DATE
    FROM
        fusion.HR_ALL_POSITIONS_F_TL hpt1
    WHERE
        hpt1.LANGUAGE = 'US'
        AND hpt1.EFFECTIVE_START_DATE = (
            SELECT MAX(hpt2.EFFECTIVE_START_DATE)
            FROM fusion.HR_ALL_POSITIONS_F_TL hpt2
            WHERE hpt2.POSITION_ID = hpt1.POSITION_ID
              AND hpt2.LANGUAGE = 'US'
              AND hpt2.EFFECTIVE_START_DATE <= :P_EFFECTIVE_DATE
        )
),
previous_name AS (
    SELECT
        hpt1.POSITION_ID,
        hpt1.NAME AS PREVIOUS_POSITION_NAME
    FROM
        fusion.HR_ALL_POSITIONS_F_TL hpt1
    WHERE
        hpt1.LANGUAGE = 'US'
        AND hpt1.EFFECTIVE_START_DATE = (
            SELECT MAX(hpt2.EFFECTIVE_START_DATE)
            FROM fusion.HR_ALL_POSITIONS_F_TL hpt2
            WHERE hpt2.POSITION_ID = hpt1.POSITION_ID
              AND hpt2.LANGUAGE = 'US'
              AND hpt2.EFFECTIVE_START_DATE < (
                  SELECT MAX(hpt3.EFFECTIVE_START_DATE)
                  FROM fusion.HR_ALL_POSITIONS_F_TL hpt3
                  WHERE hpt3.POSITION_ID = hpt1.POSITION_ID
                    AND hpt3.LANGUAGE = 'US'
                    AND hpt3.EFFECTIVE_START_DATE <= :P_EFFECTIVE_DATE
              )
        )
),
previous_position AS (
    SELECT
        hp.POSITION_ID,
        hp.BUSINESS_UNIT_ID AS PREV_BU_ID
    FROM
        fusion.HR_ALL_POSITIONS_F hp
    WHERE
        hp.EFFECTIVE_START_DATE = (
            SELECT MAX(hp2.EFFECTIVE_START_DATE)
            FROM fusion.HR_ALL_POSITIONS_F hp2
            WHERE hp2.POSITION_ID = hp.POSITION_ID
              AND hp2.EFFECTIVE_START_DATE < :P_EFFECTIVE_DATE
        )
)
SELECT
    pos_param.POSITION_CODE,
    hp.POSITION_ID,
    ln.CURRENT_POSITION_NAME,
    pn.PREVIOUS_POSITION_NAME,
    TO_CHAR(ln.CHANGE_EFFECTIVE_DATE, 'MM/DD/YYYY') AS NEW_POSITION_START_DATE,
    fbu.BU_NAME AS CURRENT_BUSINESS_UNIT,
    pfbu.BU_NAME AS PREVIOUS_BUSINESS_UNIT
FROM
    position_param pos_param
JOIN latest_position hp
    ON pos_param.POSITION_CODE = hp.POSITION_CODE
    AND hp.rn = 1
LEFT JOIN latest_name ln
    ON hp.POSITION_ID = ln.POSITION_ID
LEFT JOIN previous_name pn
    ON hp.POSITION_ID = pn.POSITION_ID
LEFT JOIN previous_position pp
    ON hp.POSITION_ID = pp.POSITION_ID
LEFT JOIN fusion.FUN_ALL_BUSINESS_UNITS_V fbu
    ON hp.BUSINESS_UNIT_ID = fbu.BU_ID
LEFT JOIN fusion.FUN_ALL_BUSINESS_UNITS_V pfbu
    ON pp.PREV_BU_ID = pfbu.BU_ID
ORDER BY pos_param.SORT_ORDER

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.