Detail SQL Query Using Workflow/BPM tables in oracle fusion

Query to extract information related to the PR Purchase requisition BPM approval rules.

select par.rule_id,

par.rule_number,

par.rule_name,

par.DISPLAY_RULE_NAME,

par.description,

par.CONDITIONS_STRING,

par.PRIORITY,

par.active_flag,

pat.LOOKUP_CODE pat_LOOKUP_CODE,

pap.PARTICIPANT_KEY,

paa.ACTION_CODE,

paa.APPROVAL_GROUP_NAME,

pac.condition_number,

pac.attribute_key cond_key,

pac.dimension_key,

pac.operator,

pac.condition_string

from POR_AMX_RULES PAR,

POR_AMX_PARTICIPANTS pap,

POR_AMX_DIMENSIONS   PAD,

POR_AMX_TASKS PAT,

(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 PAA,

POR_AMX_CONDITIONS PAC

where par.active_flag = 'Y'

and par.PARTICIPANT_ID = pap.PARTICIPANT_ID

and par.task_id = pap.task_id

and pas.STAGE_ID = pap.STAGE_ID

and pas.task_id = pap.task_id

and pas.stage_id = pap.stage_id

--and pap.DIMENSION_ID = pad.DIMENSION_ID

and PAT.task_id = pad.task_id

and rul_max.max_obj = par.OBJECT_VERSION_NUMBER

and rul_max.max_rul_name = par.rule_name

and paa.rule_id = par.rule_id

and paa.task_id = par.task_id

and pac.rule_id = paa.rule_id

and pac.task_id = paa.task_id

and pat.task_key = 'ReqApproval'

and pad.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL'

BPM Approval (Workflow) Table list in Oracle Fusion

BPM(Workflow) Table list in Oracle Fusion

1. POR_AMX_RULES
2. POR_AMX_PARTICIPANTS
3. POR_AMX_DIMENSIONS
4. POR_AMX_TASKS
5. POR_AMX_STAGES
6. POR_AMX_ACTIONS
7. POR_AMX_CONDITIONS

SQL Query

The sql query below extracts information about the BPM approval rules for Purchase requisitions data

SELECT par.rule_id,
       par.rule_number,
       par.rule_name,
       par.DISPLAY_RULE_NAME,
       par.description,
       par.CONDITIONS_STRING,
       par.PRIORITY,
       par.active_flag,
       pat.LOOKUP_CODE pat_LOOKUP_CODE,
       pap.PARTICIPANT_KEY,
       paa.ACTION_CODE,
       paa.APPROVAL_GROUP_NAME,
       pac.condition_number,
       pac.attribute_key cond_key,
       pac.dimension_key,
       pac.operator,
       pac.condition_string
FROM   POR_AMX_RULES PAR,
       POR_AMX_PARTICIPANTS pap,
       POR_AMX_DIMENSIONS   PAD,
       POR_AMX_TASKS PAT,
       (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 PAA,
       POR_AMX_CONDITIONS PAC
WHERE  par.active_flag = 'Y'
   and par.PARTICIPANT_ID = pap.PARTICIPANT_ID
   and par.task_id = pap.task_id
   and pas.STAGE_ID = pap.STAGE_ID
   and pas.task_id = pap.task_id
   and pas.stage_id = pap.stage_id
   and PAT.task_id = pad.task_id
   and rul_max.max_obj = par.OBJECT_VERSION_NUMBER
   and rul_max.max_rul_name = par.rule_name
   and paa.rule_id = par.rule_id
   and paa.task_id = par.task_id
   and pac.rule_id = paa.rule_id
   and pac.task_id = paa.task_id
   and pat.task_key = 'ReqApproval'
   and pad.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL';