0
0
WITH users_qry AS (
SELECT
papf.person_number "Person Number",
ppnf_emp.full_name "Person Name",
pu_emp.username "User Account Name",
pu_emp.user_start_date,
pu_emp.user_end_date,
pu_emp.is_user_account_active,
-- pur_emp.method_code "RoleProvisioningMethod",
CASE
WHEN ( pur_emp.is_user_role_active = 'Yes'
AND pur_emp.is_user_role_terminated = 'No' ) THEN
'Active'
WHEN ( pur_emp.is_user_role_active = 'No'
AND pur_emp.is_user_role_terminated = 'No' ) THEN
'NeitherActiveNorTerminated'
WHEN ( pur_emp.is_user_role_active = 'Yes'
AND pur_emp.is_user_role_terminated = 'Yes' ) THEN
'ActiveAndTerminated'
WHEN ( pur_emp.is_user_role_active = 'No'
AND pur_emp.is_user_role_terminated = 'Yes' ) THEN
'InactiveAndNotTerminated'
END AS "User Role Status",
pur_emp.role_start_date "User Role Start Date",
pur_emp.role_end_date "User Role End Date",
-- prd_emp.role_common_name "RoleCommonName",
-- prd_emp.multitenancy_common_name "MultitenancyCommonName",
prdt_emp.role_name "User Role Name"
-- papf.effective_start_date,
FROM
per_all_people_f papf
JOIN (
SELECT
ppnf.full_name,
ppnf.person_id
FROM
per_person_names_f ppnf
WHERE
ppnf.name_type = 'GLOBAL'
--AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
) ppnf_emp ON ( ppnf_emp.person_id = papf.person_id
-- AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
)
LEFT OUTER JOIN (
SELECT
pu.person_id,
pu.user_id,
pu.username,
to_char(pu.start_date, 'DD-MM-RRRR') user_start_date,
to_char(pu.end_date, 'DD-MM-RRRR') user_end_date,
--decode(pu.active_flag, 'N', 'Inactive', 'Y', 'Active')
decode(pu.suspended, 'N', 'Active', 'Y', 'Inactive') is_user_account_active,
decode(pu.suspended, 'N', 'No', 'Y', 'Yes') is_user_account_suspended,
pu.user_distinguished_name
FROM
per_users pu
) pu_emp ON ( pu_emp.person_id = papf.person_id
--AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
)
LEFT OUTER JOIN (
SELECT
pur.user_id,
pur.role_id,
pur.role_guid,
decode(pur.method_code, 'A', 'Automatic', 'M', 'Manually',
'E', 'Externally Provisioned') method_code,
decode(pur.active_flag, 'N', 'No', 'Y', 'Yes') is_user_role_active,
decode(pur.terminated_flag, 'N', 'No', 'Y', 'Yes') is_user_role_terminated,
to_char(pur.start_date, 'DD-MM-RRRR') role_start_date,
nvl(to_char(pur.end_date, 'DD-MM-RRRR'),
'31-12-4012') role_end_date
FROM
per_user_roles pur
) pur_emp ON ( pu_emp.user_id = pur_emp.user_id )
JOIN (
SELECT
prd.role_id,
prd.role_guid,
prd.abstract_role,
prd.job_role,
prd.data_role,
decode(prd.active_flag, 'N', 'No', 'Y', 'Yes') is_role_active,
prd.role_common_name,
-- prd.x,
prd.role_distinguished_name
FROM
per_roles_dn prd
) prd_emp ON ( pur_emp.role_id = prd_emp.role_id
AND pur_emp.role_guid = prd_emp.role_guid )
JOIN (
SELECT
prdt.role_id,
prdt.role_name,
prdt.description roledescription,
prdt.source_lang
FROM
per_roles_dn_tl prdt
) prdt_emp ON ( prd_emp.role_id = prdt_emp.role_id
AND pur_emp.role_id = prdt_emp.role_id
AND prdt_emp.source_lang = 'US' )
WHERE
1 = 1
--AND papf.person_number = NVL ( :pn_person_number, papf.person_number)
--AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
GROUP BY
papf.person_number,
ppnf_emp.full_name,
pu_emp.username,
-- pur_emp.method_code,
pur_emp.is_user_role_active,
pur_emp.is_user_role_terminated,
pur_emp.role_start_date,
pur_emp.role_end_date,
-- prd_emp.role_common_name,
-- prd_emp.multitenancy_common_name,
prdt_emp.role_name,
pu_emp.user_start_date,
pu_emp.user_end_date,
pu_emp.is_user_account_active
ORDER BY
papf.person_number
)
SELECT
ROWNUM,
users_qry.*
FROM
users_qry
I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.