Join between table and query

SELECT res_registration.allotmentno AS allotmentno,
       TO_CHAR (res_registration.allotmentdate,
                'DD/MM/YYYY') AS allotmentdate, res_registration.NAME AS NAME,
       res_mstsector.sectorname AS sectorname,
       res_mstblock.blockcode AS BLOCK,
       NVL (res_mstplotmaster.plotnumber, res_registration.plot_no) AS plotno,
       res_registration.actualplotsize AS plotsize,
       res_mstpayplan.payplan AS payplan,
       res_registration.CATEGORY AS CATEGORY,
       res_mstsociety.soc_name AS socname,
          ' L '
       || TO_CHAR (res_regfinancedetails.leasedeeddate, 'DD/MM/YYYY')
       || ' F '
       || TO_CHAR (res_regfinancedetails.functionalexedate, 'DD/MM/YYYY')
                                                                     AS lease,
       res_registration.refund AS refund, res_registration.fatherhusbname,
       '(' || res_registration.NAME || ')' AS signature,
       res_registration.add1, res_registration.add2, res_registration.add3,
          res_registration.city
       || '-'
       || res_registration.pin
       || ','
       || state.statename city,
       res_registration.address1, res_registration.address2,
       res_registration.address3,
       res_registration.city1 || '-' || res_registration.pin1 AS citynew,
       res_mstblock.blockname
  FROM res_registration LEFT JOIN res_mstplotmaster
       ON res_registration.fkplotid = res_mstplotmaster.pkplotid
       LEFT JOIN res_mstsector
       ON res_mstplotmaster.fksectorid = res_mstsector.pksectorid
       LEFT JOIN res_mstblock
       ON res_mstplotmaster.fkblockid = res_mstblock.pkblockid
       LEFT JOIN res_regfinancedetails
       ON res_registration.pkregid = res_regfinancedetails.fkregid
       LEFT JOIN res_mstsociety
       ON res_registration.soc_code = res_mstsociety.soc_code
       LEFT JOIN res_mstpayplan
       ON res_registration.fkpayplanid = res_mstpayplan.pkpayplanid
       INNER JOIN res_mstscheme
       ON res_registration.fkschemeid = res_mstscheme.pkschemeid
       LEFT JOIN mststate state ON res_registration.stateid = state.pkstateid
       LEFT OUTER JOIN
       (SELECT DISTINCT fkregid
                   FROM res_mstpayment
                  WHERE res_mstpayment.duetype = 'F' and  ISACTIVE=1 and ISDELETED=0) r
       ON r.fkregid = res_registration.pkregid
 WHERE (   TRIM (res_registration.refund) NOT IN ('T', 'F', 'H', 'A')
        OR res_registration.fkrefundid IS NULL
       )
   AND res_registration.isactive = 1
   AND res_registration.isdeleted = 0
   AND r.fkregid IS NULL
   AND allotmentno IS NOT NULL
--ORDER BY allotmentno ASC;
--schemecode,