Introduction:
This blog has the SQL query that can be used to pull the data for the oracle cloud critical users having what roles & for which BU it is assigned.
Cause of the issue:
Business wants this report for Audit tracking purpose.
How do we solve:
Create a BI report in fusion using below SQL query to extract the details.
| SELECT DISTINCT
username, NULL AS “REVIEW_ACTION”, NULL AS “COMMENTS”, NULL AS “TICKET_NUMBER”, ( CASE WHEN username LIKE ‘%xxxx%’ THEN ‘XXX’ ELSE ‘XXX’ END ) XXXX, ( SELECT CASE WHEN lv.tag = ‘AP’ THEN ‘Accounts Payable’ WHEN lv.tag = ‘AR’ THEN ‘Accounts Receivables’ WHEN lv.tag = ‘CM’ THEN ‘Cash Management’ WHEN lv.tag = ‘CO’ THEN ‘Collections’ WHEN lv.tag = ‘FA’ THEN ‘Fixed Assets’ WHEN lv.tag = ‘GL’ THEN ‘General Ledger’ WHEN lv.tag = ‘IPRO’ THEN ‘IPRO’ WHEN lv.tag = ‘IT’ THEN ‘IT’ WHEN lv.tag = ‘PPM’ THEN ‘PPM’ ELSE tag END FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ AND lv.meaning = rname AND ROWNUM = 1 ) “BROADER_CATEGORIES”, user_first_last, mgr_first_last, rname, rcode, buname, user_bu, user_resp_start_date, mger_name, job_title, user_last_logon FROM ( SELECT DISTINCT pu.username username, ppn.first_name || ‘ ‘ || ppn.last_name AS user_first_last, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name AS mgr_first_last , prdt.role_name rname, prd.role_common_name rcode , regexp_substr(gl.name, ‘[^ ]+’, 1, 2) AS buname
, fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, gl_access_sets gl, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND role.access_set_id = gl.access_set_id AND ppm.assignment_type (+) IN ( ‘E’, ‘C’ ) AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ( regexp_substr(gl.name, ‘[^ ]+’, 1, 2) IN ( :buname ) OR ( ( ( SELECT LISTAGG(regexp_substr(bu_name, ‘[^ ]+’, 1, 2), ‘, ‘) WITHIN GROUP( ORDER BY regexp_substr(bu_name, ‘[^ ]+’, 1, 2) ) FROM fun_all_business_units_v WHERE regexp_substr(bu_name, ‘[^ ]+’, 1, 2) IN ( :buname ) ) ) IS NULL ) ) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION SELECT DISTINCT pu.username, ppn.first_name || ‘ ‘ || ppn.last_name, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name , prdt.role_name, prd.role_common_name , regexp_substr(bu.bu_name, ‘[^ ]+’, 1, 2) , fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, fun_all_business_units_v bu, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND role.org_id = bu.bu_id AND ppm.assignment_type (+) = ‘E’ AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ( regexp_substr(bu.bu_name, ‘[^ ]+’, 1, 2) IN ( :buname ) OR ( ( ( SELECT LISTAGG(regexp_substr(bu_name, ‘[^ ]+’, 1, 2), ‘, ‘) WITHIN GROUP( ORDER BY regexp_substr(bu_name, ‘[^ ]+’, 1, 2) ) FROM fun_all_business_units_v WHERE regexp_substr(bu_name, ‘[^ ]+’, 1, 2) IN ( :buname ) ) ) IS NULL ) ) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION SELECT DISTINCT pu.username, ppn.first_name || ‘ ‘ || ppn.last_name, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name , prdt.role_name, prd.role_common_name , regexp_substr(book.book_type_code, ‘[^ ]+’, 1, 2) , fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, fa_book_controls book, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND book.book_control_id = role.book_id AND ppm.assignment_type (+) = ‘E’ AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ( regexp_substr(book.book_type_code, ‘[^ ]+’, 1, 2) IN ( :buname ) OR ( ( ( SELECT LISTAGG(regexp_substr(bu_name, ‘[^ ]+’, 1, 2), ‘, ‘) WITHIN GROUP( ORDER BY regexp_substr(bu_name, ‘[^ ]+’, 1, 2) ) FROM fun_all_business_units_v WHERE regexp_substr(bu_name, ‘[^ ]+’, 1, 2) IN ( :buname ) ) ) IS NULL ) ) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION SELECT DISTINCT pu.username, ppn.first_name || ‘ ‘ || ppn.last_name, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name , prdt.role_name, prd.role_common_name , regexp_substr(cost.cost_org_name, ‘[^ ]+’, 2, 3) , fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, cst_cost_orgs_v cost, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND cost.cost_org_id = role.cst_organization_id AND ppm.assignment_type (+) = ‘E’ AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ( regexp_substr(cost.cost_org_name, ‘[^ ]+’, 2, 3) IN ( :buname ) OR ( ( ( SELECT LISTAGG(regexp_substr(bu_name, ‘[^ ]+’, 1, 2), ‘, ‘) WITHIN GROUP( ORDER BY regexp_substr(bu_name, ‘[^ ]+’, 1, 2) ) FROM fun_all_business_units_v WHERE regexp_substr(bu_name, ‘[^ ]+’, 1, 2) IN ( :buname ) ) ) IS NULL ) ) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION SELECT DISTINCT pu.username, ppn.first_name || ‘ ‘ || ppn.last_name, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name , prdt.role_name, prd.role_common_name , CASE WHEN regexp_substr(inv.organization_code, ‘[^_]+’, 1, 2) = ‘ITR’ THEN regexp_substr(inv.organization_code, ‘[^_]+’, 2, 3) ELSE regexp_substr(inv.organization_code, ‘[^_]+’, 1, 2) END , fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, inv_org_parameters inv, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND inv.organization_id = role.inv_organization_id AND ppm.assignment_type (+) = ‘E’ AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ( regexp_substr(inv.organization_code, ‘[^_]+’, 1, 2) IN ( :buname ) OR ( ( ( SELECT LISTAGG(regexp_substr(bu_name, ‘[^ ]+’, 1, 2), ‘, ‘) WITHIN GROUP( ORDER BY regexp_substr(bu_name, ‘[^ ]+’, 1, 2) ) FROM fun_all_business_units_v WHERE regexp_substr(bu_name, ‘[^ ]+’, 1, 2) IN ( :buname ) ) ) IS NULL ) ) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION SELECT DISTINCT pu.username, ppn.first_name || ‘ ‘ || ppn.last_name, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name , prdt.role_name, prd.role_common_name , CASE WHEN st.set_name = ‘Common Set’ OR st.set_name = ‘Enterprise Set’ THEN ‘All’ ELSE regexp_substr(st.set_name, ‘[^ ]+’, 1, 2) END , fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, fnd_setid_sets_vl st, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND st.set_id = role.set_id AND ppm.assignment_type (+) = ‘E’ AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ( regexp_substr(st.set_name, ‘[^ ]+’, 1, 2) IN ( :buname ) OR ( ( ( SELECT LISTAGG(regexp_substr(bu_name, ‘[^ ]+’, 1, 2), ‘, ‘) WITHIN GROUP( ORDER BY regexp_substr(bu_name, ‘[^ ]+’, 1, 2) ) FROM fun_all_business_units_v WHERE regexp_substr(bu_name, ‘[^ ]+’, 1, 2) IN ( :buname ) ) ) IS NULL ) ) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION SELECT DISTINCT pu.username, ppn.first_name || ‘ ‘ || ppn.last_name, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name , prdt.role_name, prd.role_common_name , regexp_substr(interco.interco_org_name, ‘[^ -]+’, 1, 2) , fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, fun_interco_organizations interco, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id –AND prdt.language = USERENV (‘lang’) AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND interco.interco_org_id = role.interco_org_id AND ppm.assignment_type (+) = ‘E’ AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION SELECT DISTINCT pu.username, ppn.first_name || ‘ ‘ || ppn.last_name, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name , prdt.role_name, prd.role_common_name , regexp_substr(gl.name, ‘[^ ]+’, 1, 2) –gl.name , fuv.bu_name AS user_bu, role.start_date_active user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn, fun_user_role_data_asgnmnts role, gl_ledgers gl, per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND gl.ledger_id = role.ledger_id AND ppm.assignment_type (+) = ‘E’ AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username) AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND role.active_flag (+) = ‘Y’ AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’
UNION SELECT DISTINCT pu.username username, ppn.first_name || ‘ ‘ || ppn.last_name AS user_first_last, ppnf_s.first_name || ‘ ‘ || ppnf_s.last_name AS mgr_first_last , prdt.role_name rname, prd.role_common_name rcode , NULL AS buname , fuv.bu_name AS user_bu, pur.start_date AS user_resp_start_date, pu1.username mger_name, pj.name job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, per_all_people_f ppf, per_person_names_f_v ppn , per_all_assignments_m ppm, per_assignment_supervisors_f pas, per_all_assignments_m paam_s, per_all_people_f papf_s, per_person_names_f_v ppnf_s, fun_all_business_units_v fuv, per_users pu1, per_jobs pj, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND ppf.person_id = pu.person_id AND sysdate BETWEEN ppf.effective_start_date (+) AND ppf.effective_end_date (+) AND sysdate BETWEEN ppn.effective_start_date (+) AND ppn.effective_end_date (+) AND pu.person_id = ppn.person_id
AND ppm.assignment_type (+) IN ( ‘E’, ‘C’ ) AND ppm.assignment_status_type (+) = ‘ACTIVE’ AND pu.person_id = ppm.person_id (+) AND ppf.person_id = ppm.person_id (+) AND sysdate BETWEEN ppm.effective_start_date (+) AND ppm.effective_end_date (+) AND ppf.person_id = pas.person_id (+) AND ppm.assignment_id = pas.assignment_id (+) AND sysdate BETWEEN pas.effective_start_date (+) AND pas.effective_end_date (+) AND pas.manager_assignment_id = paam_s.assignment_id (+) AND paam_s.assignment_type (+) = ‘E’ AND paam_s.assignment_status_type (+) = ‘ACTIVE’ AND sysdate BETWEEN paam_s.effective_start_date (+) AND paam_s.effective_end_date (+) AND pas.manager_id = papf_s.person_id (+) AND sysdate BETWEEN papf_s.effective_start_date (+) AND papf_s.effective_end_date (+) AND papf_s.person_id = ppnf_s.person_id (+) AND sysdate BETWEEN ppnf_s.effective_start_date (+) AND ppnf_s.effective_end_date (+) AND pu.username = nvl(:username1, pu.username)
AND ppm.business_unit_id = fuv.bu_id (+) AND pas.manager_id = pu1.person_id (+) AND ppm.job_id = pj.job_id (+) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND prdt.role_common_name NOT IN ( SELECT DISTINCT r1.role_name FROM fun_user_role_data_asgnmnts r1 WHERE 1 = 1 AND r1.user_guid = pu.user_guid AND r1.active_flag = ‘Y’ ) AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ UNION –User is not as employee
SELECT DISTINCT pu.username username, NULL AS user_first_last , NULL AS mgr_first_last, prdt.role_name rname, prd.role_common_name rcode , regexp_substr(fuv.bu_name, ‘[^ ]+’, 1, 2) , NULL AS user_bu, pur.start_date AS user_resp_start_date, NULL AS mger_name, NULL AS job_title , nvl(fnd.last_connect1, asu.last_login_date) user_last_logon FROM per_user_roles pur, per_users pu, per_roles_dn_vl prdt, per_roles_dn prd, fun_user_role_data_asgnmnts role, fun_all_business_units_v fuv, ( SELECT user_guid, MAX(last_connect) last_connect1 FROM fnd_sessions WHERE 1 = 1 GROUP BY user_guid ) fnd, ase_user_login_info asu WHERE pu.user_id = pur.user_id AND nvl(pu.start_date, sysdate) <= sysdate AND nvl(pu.end_date, sysdate) >= sysdate AND prdt.role_id = pur.role_id AND prdt.role_id = prd.role_id AND pu.active_flag (+) = ‘Y’ AND pu.username = nvl(:username1, pu.username) AND pu.user_guid = fnd.user_guid (+) AND pu.user_guid = asu.user_guid (+) AND pu.person_id IS NULL AND prdt.role_name IN ( SELECT lv.meaning FROM fnd_lookup_values_vl lv WHERE lv.lookup_type = ‘LOOKUP’ ) AND pur.active_flag = ‘Y’ AND pu.suspended = ‘N’ AND pu.user_guid = role.user_guid AND prdt.role_common_name = role.role_name AND role.org_id = fuv.bu_id
) WHERE user_resp_start_date BETWEEN nvl(:start_date, user_resp_start_date) AND nvl(:end_date, sysdate) |
