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

GROUP BY

   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 ,

       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 

Oracle EBS: How to Create Request Group and Assign Request Group to Responsibility

  

Step1: Create a Request Group.

Navigate: System Administrator Responsibility > Security > Responsibility > Request

Group Name: TLM OM Inquiry

Application: Order Management

Type: Program

Name: Telamon SO Cancelled Item Details Report

 

Step2: Assign Request Group to Responsibility.

Navigate: System Administrator Responsibility > Security > Responsibility > Define

Query Responsibility Name: TLM Order Management Inquiry

Add Request Group: TLM OM Inquiry

Save it.