iProcurement Preferences Query in Oracle iProcurement

/* Formatted on 2016/10/19 10:55 (Formatter Plus v4.8.8) */
SELECT responsibility_id, r.*
  FROM apps.fnd_responsibility_vl r
 WHERE last_update_date > SYSDATE - 7 AND end_date IS NOT NULL;

SELECT   (SELECT full_name
            FROM per_all_people_f p
           WHERE p.person_id = pfa.employee_id
             AND SYSDATE BETWEEN effective_start_date
                             AND NVL (effective_end_date, SYSDATE))
                                                           employee_full_name,
         (SELECT user_name
            FROM fnd_user u
           WHERE u.employee_id = pfa.employee_id
             AND SYSDATE BETWEEN start_date AND NVL (end_date, SYSDATE)) racf,
         nickname,
         (SELECT concatenated_segments
            FROM gl_code_combinations_kfv gl
           WHERE gl.code_combination_id =
                                        pfa.charge_account_id)
                                                              gl_account_code,
         default_account primary_flag,
--         (SELECT hl.location_code
--            FROM apps.fnd_profile_options a,
--                 apps.fnd_profile_options_vl b,
--                 apps.fnd_profile_option_values c,
--                 apps.fnd_user e,
--                 apps.fnd_responsibility_vl g,
--                 apps.fnd_application h,
--                 hr_locations hl
--           WHERE a.application_id = 178
--             AND b.user_profile_option_name =
--                                     'POR : Preferences - Deliver to Location'
--             AND a.profile_option_name = b.profile_option_name
--             AND a.profile_option_id = c.profile_option_id
--             AND a.application_id = c.application_id
--             AND c.level_value = e.user_id(+)
--             AND c.level_value = g.responsibility_id(+)
--             AND c.level_value = h.application_id(+)
--             AND hl.location_id = c.profile_option_value
--             AND e.employee_id = pfa.employee_id
--             AND SYSDATE BETWEEN e.start_date AND NVL (e.end_date, SYSDATE)
--             --and rownum = 1
--             ) location_code,
             (SELECT hl.location_code
            FROM apps.fnd_profile_options a,
                 apps.fnd_profile_options_vl b,
                 apps.fnd_profile_option_values c,
                 hr_locations hl,
                 fnd_user fu
           WHERE a.application_id = 178
             AND b.user_profile_option_name =
                                     'POR : Preferences - Deliver to Location'
             AND a.profile_option_name = b.profile_option_name
             AND a.profile_option_id = c.profile_option_id
             AND a.application_id = c.application_id
             AND c.level_value = fu.user_id
             and fu.employee_id =  pfa.employee_id
             AND hl.location_id = c.profile_option_value
             AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)
             ) loc_code,
         (SELECT user_name
            FROM fnd_user u
           WHERE u.user_id = pfa.created_by
            --AND SYSDATE BETWEEN start_date AND NVL (end_date, SYSDATE)
         ) created_by,
         (SELECT responsibility_name
            FROM fnd_responsibility_vl
           WHERE responsibility_id = pfa.responsibility_id
             AND application_id = 178) resposibility_name
    FROM por_fav_charge_accounts pfa
   WHERE responsibility_id IN
                     (50825, 50834, 50835, 50836, 50837, 51428, 51429, 51430)
ORDER BY employee_full_name;