Tuesday, 16 August 2022

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 

No comments:

Post a Comment