Spark SQL correlated subquery not identifying parent columns

Question

I am trying to migrate a query from SQL Server to Spark SQL. It is running fine on SQL Server but having issues in Spark SQL. I found that spark SQL does not support sub queries But I am avoiding to make lot of changes to this query as it was provided by DBA.

SELECT g.HTRAN
FROM property pr
inner join gldetail g on g.hprop = pr.hmy
INNER JOIN acct ac on g.hacct = ac.hmy
INNER JOIN trans tr on tr.hmy = g.htran
INNER JOIN accttreexref x on ac.hmy = x.hacct
INNER JOIN accttreedetail a on a.hmy = x.hdetail
LEFT OUTER JOIN person r ON (g.hperson = r.hmy)
WHERE a.htree = 4
AND x.hdetail IN (SELECT hDetail
                        FROM accttreexref actx
                        WHERE hAcct IN (
                                SELECT hmy
                                FROM acct
                                WHERE SCODE IN (
                                        SELECT ownremitac1
                                        FROM PROPBUT_OCASH po
                                        WHERE po.HCODE = pr.hmy
                                        )
                                )
                            AND Htree IN (
                                SELECT a.hmy
                                FROM AcctTree a
                                WHERE scode = tr.hmy
                                )
                        )

So when I am running it PO.HCODE = pr.hmy condition is unable to find pr.hmy which is the parent column and similar is the case with tr.hmy.