select UHRent_STATUS.FILE_NAME_UHRent as FILE_NAME_UHRent, UHRent_STATUS.FIN_AUDIT_ID_UHRent as FIN_AUDIT_ID_AUD
from
(SELECT max(B_LOADID) as MAX_B_LOADID, file_name FROM
SA_FIN_UH_SUN_SAF
where b_loadid = (select max(b_loadid) from SA_FIN_UH_SUN_SAF)
GROUP BY file_name
) UHRent
join
(select AUD.FILE_NAME as FILE_NAME_UHRent, AUD.FIN_AUDIT_ID as FIN_AUDIT_ID_UHRent, AUD.B_LOADID as B_LOADID_UHRent
from SA_FIN_AUDIT AUD join
(select max(B_LOADID) as LOAD_ID_MAX, FILE_NAME
from SA_FIN_AUDIT
where (file_name like 'NB%' OR file_name like 'MYTVH%')
group by FILE_NAME) UHRent_MAX
on AUD.FILE_NAME = UHRent_MAX.FILE_NAME
and AUD.B_LOADID = UHRent_MAX.LOAD_ID_MAX
and AUD.STATUS = 'New')UHRent_STATUS
on UHRent.file_name = UHRent_STATUS.FILE_NAME_UHRent
AND UHRent.MAX_B_LOADID > UHRent_STATUS.B_LOADID_UHRent;