How to implement nested querey in INFORMATICA

You can use in Source Qualifier or Lookup SQL override depending on your requirements. I slightly changed SQL:

select nvl(a.RATING,'NULL') vRating
from W_MBS_AM_BLDG_INSP_HDR_F a
where (W_UPDATE_DT, inspection_date_wid) =(select max(w_update_dt), max(INSPECTION_DATE_WID
from W_MBS_AM_BLDG_INSP_HDR_F b
where b.PROJECT_WID = a.PROJECT_WID)