select a.position, a.join_date
  into :detail.position, :detail.join_date
  from another_table a
  where a.some_column = :detail.some_other_column
    and ...

department.department=emp_details.employee_number

hrp_reference.rf_description = hrp_per_all_assignments.department_number

CREATE TABLE departments
(department_id NUMBER PRIMARY KEY,
 department_name VARCHAR2(30));
 
CREATE TABLE employees
(department_id NUMBER REFERENCES departments.department_id,
 employee_id   NUMBER PRIMARY KEY,
 employee_name VARCHAR2(30));

departments.department_id = employees.department_id

CREATE VIEW employees_v AS
SELECT
ha.department_number,
ha.<other relevant columns>,
....
hp.full_name,
hp.<other relevant columns>,
....
FROM hrp_per_all_assignments ha, hrp_per_all_people hp
WHERE ha.assignment_number = hp.employee_number

hrp_reference.rf_description = employees_v.department_number