Joining 2 CTe together in ORACLE SQL

with
cte_base
as
(
select 
e.PERSON_REF,
e.EMPLOYEE_NUMBER,
coalesce(rtrim(ltrim(known_as)),per.FIRST_FORNAME)  as FORENAME,
per.SURNAME as SURNAME,
per.NI_NO NINO,
per.INITIALS INITS,
per.TITLE TITLE,
post.number_R as POST_NUMBER,
post.long_desc as POST_LONG_DESC,
e.START_DATE EMPSTARTDATE,
e.END_DATE EMPENDDATE,
CGrp.ID CGRPID,
CGrp.LONG_DESC CGRPDESC,
PGrp.ID PGRPID,
PGrp.LONG_DESC PGRPDESC,
narr_occtype.long_desc as OCC_TYPE,
narr_joinreason.long_desc as POST_JOIN_REASON,
narr_joinreason2.long_desc as EMP_JOIN_REASON,
ph.projected_end_date
from D550M e
left outer join D500M per on e.PERSON_REF = per.PERSON_REF
left outer join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REF
left outer join D100M PGrp on e.PAY_STR_REF = PGrp.REF 
left outer join D580M ph on e.PERSON_REF = ph.PERSON_REF and
( ph.start_date <= sysdate and (ph.end_date is null or ph.end_Date >= sysdate))
left outer join D200M post on ph.ref  = post.ref
left outer join D800M narr_occtype on ph.occ_type = narr_occtype.narrative_code and narr_occtype.narrative_category = 'OCCTYP'
left outer join D800M narr_joinreason on ph.join_reason= narr_joinreason.narrative_code and narr_joinreason.narrative_category = 'RSNCHG'
left outer join D800M narr_joinreason2 on e.orig_how_joined = narr_joinreason2.narrative_code and narr_joinreason2.narrative_category = 'RSNCHG'

),

CTE_PostGrade AS
(
select e.PERSON_REF,
Gr.NUMBER_R GRADE
from D550M e
left join D580M ph on e.PERSON_REF = ph.PERSON_REF
left join D554M phGr on phGr.PERSON_REF = ph.PERSON_REF and
phGr.POST_REF = ph.REF
left join D400M Gr on Gr.REF = phGr.REF
where ph.MAIN_FLAG = 'Y'
and e.START_DATE <= trunc(SYSDATE) and coalesce(e.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
and ph.START_DATE <= trunc(SYSDATE) and coalesce(ph.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
and phGr.START_DATE <= trunc(SYSDATE) and coalesce(phGr.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
)
select 
--PERSON_REF,
EMPLOYEE_NUMBER,
FORENAME as KNOWN_AS,
SURNAME,
TITLE,
POST_NUMBER,
POST_LONG_DESC,
EMPSTARTDATE,
EMPENDDATE,
CGRPID,
CTE_PostGrade.NUMBER_R GRADE,
CGRPDESC,
PGRPID,
PGRPDESC,
OCC_TYPE,
POST_JOIN_REASON,
EMP_JOIN_REASON,
projected_end_date
from 
cte_lv
CTE_PostGrade

WHERE
EMP_JOIN_REASON NOT IN ('contractor','FTC New Starter')
AND
EMPSTARTDATE = sysdate -7

Select allOpen in new window