Multiple level nested Corelated query

My multiple level nested corelated query is not fetching correct
result. It work fine on small set of data, but fails on larger set of
data. Any clue?

Explaining data storing and discussing design would be tough for me
here, still to show you how complex I have created my life, here is the
query:

select
(
SELECT Top 1 RowNSBranchID FROM AssoExtBranchToNSBranchMstM AM
-- MMM
WHERE AM.RowExtSysID IN
(
SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =
(
SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' AND
RowID =
(
SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2 AND
ExtSysCode = UM.SystemCode
)
)
)
AND RowExtBranchID IN
(
SELECT RowID FROM ExternalBranchMstM
WHERE ExtBranchID =
(
SELECT ExtBranchID FROM ExternalBranchMstM
WHERE ROWID =
(
SELECT RowID FROM ExternalBranchMstM
WHERE ROWID =
(
SELECT MAX(ROWID) FROM ExternalBranchMstM WHERE MCStatus = 2 AND
ExtBranchCode = UM.UpBranchCode
AND RowExtSysID IN
(
SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =
(
SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' AND
RowID =
(
SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2
AND ExtSysCode = UM.SystemCode
)
)
)
)
AND (SF = 'Active')
)
)
)
AND AM.SF = 'Active'
order by AssoID desc,TrackID desc
) nsbranchid, UM.*

from
TmpInProcessData062005MstM UM

.