Query Tuning advice

 Hi I have a tuning question, i have an sql for which i have explain plan ,

SELECT *
FROM
 (SELECT AESTY.SEEN_DTTM FROM AE_ATTENDANCES AEATT INNER JOIN AE_STAYS AESTY
  ON NVL(AESTY.ARCHV_FLAG,'N') = 'N' AND AESTY.AEATT_REFNO =
  AEATT.AEATT_REFNO AND AESTY.SEEN_DTTM < :B1 INNER JOIN PROF_CARERS PROCA ON
  NVL(PROCA.ARCHV_FLAG,'N') = 'N' AND PROCA.PROCA_REFNO = AESTY.PROCA_REFNO
  INNER JOIN REFERENCE_VALUE_IDS RFVLI ON NVL(RFVLI.ARCHV_FLAG,'N') = 'N' AND
  RFVLI.IDENTIFIER IN ('DOCTOR','NURSE','MHPRAC') AND RFVLI.RFVAL_REFNO =
  PROCA.PRTYP_REFNO AND RFVLI.RITYP_CODE = 'VEMD' WHERE AEATT.ARCHV_FLAG IS