CTE does not work for all cases

I’ve received a request to create a BI Publisher report that meets the following business requirement:

• Given a date parameter (e.g., 31/12/2024), the report must return all employees hired up to and including that date.
• For each employee, the report should display the assignment active at that time.
• If the employee was terminated before the given date, the report should still include them, showing the last valid assignment the person had before termination.

Solution Design:

To address this, I created the following three subqueries (CTEs):

1. N_PER_ALL_PEOPLE

SELECT *

FROM (

  SELECT PAPF.*,

         ROW_NUMBER() OVER (

           PARTITION BY person_id