Oracle嵌套多层，找不到表的别名问题

情况是这样的，在一个帆软报表的统计里面，多表联合查询。其中一个字段既要和外表关联还要按时间倒序后取第一条记录。

这样是可以的，但是放在主SQL中 提示找不到AP.ID。

SELECT *
FROM (
         SELECT t.REPORT_RATING
         FROM IAM_REPORT_HEAR_TEMPLATE t
         WHERE t.PROJECT_ID = '-1750442436769594485'
         ORDER BY t.MODIFY_TIME DESC
     )
WHERE ROWNUM = 1;

下面是嵌套多层，找不到表别名的解决办法：

SELECT
  AU.UNITNAME AS 作业单位,
  SW.USER_NAME AS 姓名,
  TO_CHAR(FR.PUBLISH_TIME, 'yyyy') AS 年度,
  TO_CHAR(AP.AUDITSCHEDULE_START, 'YYYY-MM-DD') || ' 至 ' || TO_CHAR(AP.AUDITSCHEDULE_END, 'YYYY-MM-DD') AS 现场审计日期,
  AP.PROJECT_NAME AS 参与项目,
  PS.PROJECT_GRADE AS 立项层级,
  SW.PARTICIPATE_TYPE AS 参与形式,
  PS.PROJECT_TOTAL AS 项目积分设定值,
  AP.PREDICTED_WORKDAYS AS 标准人天,
  (SELECT SUM(w.DAYS) FROM IAM_PROJECT_SCORE_WORKLOAD w WHERE w.PROJECT_SCORE_ID = PS.ID AND w.USER_ROLE IN ('主审', '组员')) AS 实际人天,
  SW.DAYS AS 参与天数,
  PS.STANDARD_WORKING_DAY AS 标准日上限值,
--  TRUNC(PS.PROJECT_TOTAL / (SELECT SUM(w.DAYS) FROM IAM_PROJECT_SCORE_WORKLOAD w WHERE w.PROJECT_SCORE_ID = PS.ID AND w.USER_ROLE IN ('主审', '组员')) * PS.STANDARD_WORKING_DAY, 2) AS 实际积分,
--  SW.SCORE AS 实际积分,
TRUNC(PS.PROJECT_TOTAL * SW.DAYS  / (SELECT SUM(w.DAYS) FROM IAM_PROJECT_SCORE_WORKLOAD w WHERE w.PROJECT_SCORE_ID = PS.ID AND w.USER_ROLE IN ('主审', '组员')), 2) AS 实际积分,
  SW.USER_ROLE AS 项目角色,
  CASE WHEN SW.USER_ROLE = '组长' THEN 1 ELSE SW.DIVISION_COEFFICIENT END AS 编组分工系数,
 (SELECT tt.REPORT_RATING FROM (
    SELECT t.REPORT_RATING, t.PROJECT_ID, t.MODIFY_TIME
    FROM IAM_REPORT_HEAR_TEMPLATE t
    ORDER BY t.MODIFY_TIME DESC) tt , IAM_AUDIT_PROJECT app
    WHERE tt.PROJECT_ID = app.ID AND ap.id = app.ID AND ROWNUM = 1) AS 项目报告等级,
 (SELECT tt.SJZLXS FROM (
    SELECT t.SJZLXS, t.PROJECT_ID, t.MODIFY_TIME
    FROM IAM_REPORT_HEAR_TEMPLATE t
    ORDER BY t.MODIFY_TIME DESC) tt , IAM_AUDIT_PROJECT app
    WHERE tt.PROJECT_ID = app.ID AND ap.id = app.ID AND ROWNUM = 1) AS 审计质量系数,
  TRUNC((SELECT SUM(w.SCORE) / COUNT(1) FROM IAM_PROJECT_SCORE_WORKLOAD w WHERE w.PROJECT_SCORE_ID = PS.ID AND w.USER_ROLE IN ('主审', '组员')), 2) AS 组员平均积分,
--  TRUNC(TRUNC(PS.PROJECT_TOTAL / (SELECT SUM(w.DAYS) FROM IAM_PROJECT_SCORE_WORKLOAD w WHERE w.PROJECT_SCORE_ID = PS.ID AND w.USER_ROLE IN ('主审', '组员')) * PS.STANDARD_WORKING_DAY, 2) *
--      CASE WHEN SW.USER_ROLE = '组长' THEN 1 ELSE SW.DIVISION_COEFFICIENT END *
--      (SELECT t.sjzlxs FROM IAM_REPORT_HEAR_TEMPLATE t WHERE t.PROJECT_ID = AP.ID AND ROWNUM = 1), 2) AS 项目积分,
TRUNC(TRUNC(PS.PROJECT_TOTAL * SW.DAYS  / (SELECT SUM(w.DAYS) FROM IAM_PROJECT_SCORE_WORKLOAD w WHERE w.PROJECT_SCORE_ID = PS.ID AND w.USER_ROLE IN ('主审', '组员')), 2)*
      CASE WHEN SW.USER_ROLE = '组长' THEN 1 ELSE SW.DIVISION_COEFFICIENT END *
      (SELECT t.sjzlxs FROM IAM_REPORT_HEAR_TEMPLATE t WHERE t.PROJECT_ID = AP.ID AND ROWNUM = 1), 2) AS 项目积分,
  TO_CHAR(ROUND(nvl(SW.BACK_INTEGRAL, 0), 2), 'fm99999999999990.00') AS 项目回溯,
  TRUNC(TRUNC(PS.PROJECT_TOTAL / (SELECT SUM(w.DAYS) FROM IAM_PROJECT_SCORE_WORKLOAD w WHERE w.PROJECT_SCORE_ID = PS.ID AND w.USER_ROLE IN ('主审', '组员')) * PS.STANDARD_WORKING_DAY, 2) *
      CASE WHEN SW.USER_ROLE = '组长' THEN 1 ELSE SW.DIVISION_COEFFICIENT END *
      (SELECT t.sjzlxs FROM IAM_REPORT_HEAR_TEMPLATE t WHERE t.PROJECT_ID = AP.ID AND ROWNUM = 1), 2) + TO_CHAR(ROUND(nvl(SW.BACK_INTEGRAL, 0), 2), 'fm99999999999990.00') AS 最终积分,
  (SELECT WM_CONCAT(h.HSJF_ADJUST_REASON) FROM IAM_PROJECT_SCORE_HSJF h WHERE h.WORKLOAD_ID = AP.ID AND USER_NAME = (SW.USER_NAME || '（' || SW.USER_ROLE ||  '）')) AS 备注栏
FROM IAM_AUDIT_PROJECT AP
         LEFT JOIN IAM_MAJOR_AUDIT_UNIT AU ON AU.PROJECT_ID = AP.ID
         LEFT JOIN IAM_FORMAL_REPORT FR ON FR.PROJECT_ID = AP.ID
         LEFT JOIN IAM_PROJECT_SCORE PS ON AP.ID = PS.AUDIT_ID
         LEFT JOIN IAM_PROJECT_SCORE_WORKLOAD SW ON SW.PROJECT_SCORE_ID = PS.ID
WHERE SW.USER_NAME LIKE '%${userName}%'
  AND  AU.UNITID in ('${unit_id}')
  AND FR.PUBLISH_TIME >= TO_DATE('${startDate}', 'yyyy-mm-dd') AND FR.PUBLISH_TIME <= TO_DATE('${endDate}', 'yyyy-mm-dd')
  AND AP.PROJECT_NAME LIKE '%${projectName}%'