Purchase Requisition BPM Approval Workflow Tables in Oracle Fusion

9 Sep 2021 oracleappsadmin

The Oracle Fusion allows to approve the Purchase Requisition. The Purchase Requisition is integrated using BPM Approval Workflow in Oracle Fusion. 

The blog provides the BPM Approval Workflow table details used in Oracle Fusion

Purchase Requisition BPM Approval Workflow Tables 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

The below given SQL Query retrieves the Purchase Requisition BPM Approval data

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_CONDITIONS_TBL.condition_number,
       POR_AMX_CONDITIONS_TBL.attribute_key cond_key,
       POR_AMX_CONDITIONS_TBL.dimension_key,
       POR_AMX_CONDITIONS_TBL.operator,
       POR_AMX_CONDITIONS_TBL.condition_string
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,
       POR_AMX_CONDITIONS POR_AMX_CONDITIONS_TBL
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_CONDITIONS_TBL.rule_id = POR_AMX_ACTIONS_TBL.rule_id
   and POR_AMX_CONDITIONS_TBL.task_id = POR_AMX_ACTIONS_TBL.task_id
   and POR_AMX_TASKS_TBL.task_key = 'ReqApproval'
   and POR_AMX_DIMENSIONS_TBL.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL';