Oracle Application

Saturday 3 September 2022

Oracle Fusion: SQL Query to get the User Assigned Roles Access

SELECT

pu.user_id,

pu.username,

ppnf.full_name,

prdt.role_id,

prdt.role_name,

prd.role_common_name,

prdt.description,

to_char(pur.start_date, 'DD-MON-YYYY') role_start_date,

to_char(pur.end_date, 'DD-MON-YYYY') role_end_date,

prd.abstract_role,

prd.job_role,

prd.data_role,

prd.duty_role,

prd.active_flag

FROM

per_user_roles pur,

per_users pu,

per_roles_dn_tl prdt,

per_roles_dn prd,

per_person_names_f ppnf

WHERE

1 = 1

AND pu.user_id = pur.user_id

AND prdt.role_id = pur.role_id

AND prdt.language = userenv('lang')

AND prdt.role_id = prd.role_id

AND nvl(pu.suspended, 'N') = 'N'

AND ppnf.person_id = pu.person_id

AND ppnf.name_type = 'GLOBAL'

AND pu.active_flag = 'Y'

AND nvl(pu.start_date, sysdate) <= sysdate

AND nvl(pu.end_date, sysdate) >= sysdate

AND pu.username = 'cr@gmail.com' -- Enter the Username

ORDER BY

pu.username,

prdt.role_name

Tuesday 16 August 2022

Oracle Fusion: BPM - SQL Query to Get Approval Group Details

SELECT

FWG.APPROVALGROUPID ,

PPNF.DISPLAY_NAME EMP_NAME,

PAPF.PERSON_NUMBER EMP_NUM,

FWGM.MEMBER USER_NAME,

FWGM.TYPE,

FWGM.SEQUENCE,

FWG.APPROVALGROUPNAME

FROM

FA_FUSION_SOAINFRA.WFAPPROVALGROUPS FWG,

FA_FUSION_SOAINFRA.WFAPPROVALGROUPMEMBERS FWGM,

PER_USERS PU,

PER_PERSON_NAMES_F PPNF,

PER_ALL_PEOPLE_F PAPF

WHERE 1=1

AND FWG.APPROVALGROUPID = FWGM.APPROVALGROUPID

AND FWGM.MEMBER=PU.USERNAME

AND PU.PERSON_ID=PPNF.PERSON_ID

AND PPNF.NAME_TYPE='GLOBAL'

AND PAPF.PERSON_ID=PPNF.PERSON_ID

AND PAPF.PERSON_NUMBER = NVL(:P_EMP_NUM,PAPF.PERSON_NUMBER)

AND FWGM.MEMBER=NVL(:P_USERNAME,FWGM.MEMBER)

GROUP BY

PPNF.DISPLAY_NAME ,

PAPF.PERSON_NUMBER ,

FWGM.MEMBER ,

FWGM.TYPE,

FWGM.SEQUENCE,

FWG.APPROVALGROUPNAME

ORDER BY PPNF.DISPLAY_NAME ,FWG.APPROVALGROUPNAME

Oracle Fusion: BPM - SQL Query to get Purchase Requisition Approval Rule

SELECT POR_AMX_RULES_TBL.rule_id,

POR_AMX_RULES_TBL.rule_number,

POR_AMX_RULES_TBL.rule_name,

POR_AMX_RULES_TBL.DISPLAY_RULE_NAME,

POR_AMX_RULES_TBL.description,

POR_AMX_RULES_TBL.CONDITIONS_STRING,

POR_AMX_RULES_TBL.PRIORITY,

POR_AMX_RULES_TBL.active_flag,

POR_AMX_TASKS_TBL.LOOKUP_CODE pat_LOOKUP_CODE,

POR_AMX_PARTICIPANTS_TBL.PARTICIPANT_KEY,

POR_AMX_ACTIONS_TBL.ACTION_CODE,

POR_AMX_ACTIONS_TBL.APPROVAL_GROUP_NAME,

POR_AMX_ACTIONS_TBL.WORKER_ID,

ppnf.DISPLAY_NAME EMP_NAME

FROM POR_AMX_RULES POR_AMX_RULES_TBL,

POR_AMX_PARTICIPANTS POR_AMX_PARTICIPANTS_TBL,

POR_AMX_DIMENSIONS POR_AMX_DIMENSIONS_TBL,

POR_AMX_TASKS POR_AMX_TASKS_TBL,

(select RULE_NAME max_rul_name, max(OBJECT_VERSION_NUMBER) max_obj

from POR_AMX_RULES

group by RULE_NAME) rul_max,

POR_AMX_STAGES PAS,

POR_AMX_ACTIONS POR_AMX_ACTIONS_TBL

,per_person_names_f ppnf

WHERE POR_AMX_RULES_TBL.active_flag = 'Y'

and POR_AMX_RULES_TBL.PARTICIPANT_ID = POR_AMX_PARTICIPANTS_TBL.PARTICIPANT_ID

and POR_AMX_RULES_TBL.task_id = POR_AMX_PARTICIPANTS_TBL.task_id

and pas.STAGE_ID = POR_AMX_PARTICIPANTS_TBL.STAGE_ID

and pas.task_id = POR_AMX_PARTICIPANTS_TBL.task_id

and pas.stage_id = POR_AMX_PARTICIPANTS_TBL.stage_id

and POR_AMX_TASKS_TBL.task_id = POR_AMX_DIMENSIONS_TBL.task_id

and rul_max.max_obj = POR_AMX_RULES_TBL.OBJECT_VERSION_NUMBER

and rul_max.max_rul_name = POR_AMX_RULES_TBL.rule_name

and POR_AMX_ACTIONS_TBL.rule_id = POR_AMX_RULES_TBL.rule_id

and POR_AMX_ACTIONS_TBL.task_id = POR_AMX_RULES_TBL.task_id

and POR_AMX_TASKS_TBL.task_key = 'ReqApproval'

and POR_AMX_DIMENSIONS_TBL.TABLE_NAME = 'POR_REQUISITION_HEADERS_ALL'

AND POR_AMX_RULES_TBL.DISPLAY_RULE_NAME='UK Finance Manager Approval - Andrew Craggs'

AND POR_AMX_ACTIONS_TBL.WORKER_ID=ppnf.person_id(+) -- Approval Rule not assigned Worker also need to fetch