SELECT DISTINCT b.pid,
                d.bo_name,
                d.fname,
                d.lname,
                e.ss_user_type,
                x.latshrtname
FROM   rsf_suser_vw b,
       person c,
       NAME d,
       rsf_suser e,
       item x
WHERE  EXISTS (SELECT 'x'
               FROM   NAME y
               WHERE  y.id = c.id
                      AND ( y.fname_srch LIKE '%'
                            AND y.lname_srch LIKE '%' ))
       AND b.pid = c.pid
       AND c.id = d.id
       AND c.pid = e.pid
       AND e.sales_user_type = x.fieldvalue
       AND x.fieldname = 'SUSER_TYPE'
       AND EXISTS (SELECT 1
                   FROM   (SELECT DISTINCT p1.hierarchy_node
                           FROM   hierarchynode p1
                           WHERE  p1.hierarchy_name = 'GLOBE'
                                  AND connect_by_isleaf = 1
                           START WITH p1.hierarchy_node IN (SELECT territory_id
                                                            FROM   team
                                                            WHERE  pid = '558')
                           CONNECT BY PRIOR p1.hierarchy_node =
                                            p1.hierarchy_node_name
                           INTERSECT
                           SELECT DISTINCT p1.hierarchy_node
                           FROM   hierarchynode p1
                           WHERE  p1.hierarchy_name = 'GLOBE'
                                  AND connect_by_isleaf = 1
                           START WITH p1.hierarchy_node = '1093'
                           CONNECT BY PRIOR p1.hierarchy_node =
                                            p1.hierarchy_node_name)
                   WHERE  p1.territory_id);