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