SQL Query to get Requisition and Purchasing Document Approval Rules

 /*SQL Query to get Requisition Approval Rules*/

SELECT DISTINCT * FROM (

SELECT

     tal.MEANING as task,

     pal.MEANING as participant,

     rules.rule_number,

     actions.action_number,

     rules.display_rule_name,

     rules.description,

     rules.active_flag,

     prl.MEANING as priority,

     rules.sandbox_flag,

     decode(rules.always_apply_flag, 'Y', 'Rule always applies',

rules.conditions_string) as conditions,

     actions.action_code,

     decode(actions.action_code, 'AUTOMATIC', actions.SET_OUTCOME_TO_CODE,

null) as AUTOMATIC_OUTCOME,

     decode(actions.action_code, 'AUTOMATIC', null,

actions.ROUTE_USING_CODE) as ROUTE_USING_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SINGLE_APPROVER', actions.USER_TYPE_CODE,

null)) as SINGLE_APPROVER_TYPE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SINGLE_APPROVER', pn1.DISPLAY_NAME, null))

as SINGLE_APPROVER_NAME,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY',

actions.SUP_CHAIN_OF_CODE, 'JOB_LEVEL', actions.SUP_CHAIN_OF_CODE, null)) as

APPROVAL_CHAIN_OF,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY',

actions.START_WITH_CODE, 'JOB_LEVEL', actions.START_WITH_CODE, null)) as

APPROVAL_START_WITH,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY',

actions.NUMBER_OF_APPROVAL_LEVELS, null)) as NUMBER_OF_SUPERVISORY_LEVELS,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'JOB_LEVEL', actions.MINIMUM_JOB_LEVEL,

'POSITION_HIERARCHY', actions.MINIMUM_JOB_LEVEL, null)) as MINIMUM_JOB_LEVEL,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'JOB_LEVEL', actions.INCLUDE_CODE, null)) as

INCLUDE_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY', pn2.DISPLAY_NAME,

'JOB_LEVEL', pn2.DISPLAY_NAME, null)) as TOP_WORKER,      

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'APPROVAL_GROUP',

actions.APPROVAL_GROUP_NAME, null)) as APPROVAL_GROUP_NAME,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'APPROVAL_GROUP',

actions.ALLOW_EMPTY_GROUPS_FLAG, null)) as ALLOW_EMPTY_GROUPS_FLAG,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY',

actions.POSITION_HIERARCHY, null)) as POSITION_HIERARCHY,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY',

actions.POSITION_CHAIN_OF_CODE, null)) as POSITION_CHAIN_OF_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY',

actions.POSITION_START_WITH_CODE, null)) as POSITION_START_WITH_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY', actions.POSITION_ID,

null)) as TOP_POSITION_ID

     --, apg.approvalgroupname

     , (CASE WHEN (actions.action_code <> 'AUTOMATIC' and actions.ROUTE_USING_CODE ='APPROVAL_GROUP')

THEN apgm.sequence

ELSE NULL

END) sequence

     , (CASE WHEN (actions.action_code <> 'AUTOMATIC' and actions.ROUTE_USING_CODE ='APPROVAL_GROUP')

THEN APGM.MEMBER

ELSE NULL

END) MEMBER

, parts.PARTICIPANT_KEY

, TASKS.TASK_KEY

 

FROM fusion.POR_AMX_RULES rules,

     fusion.POR_AMX_ACTIONS actions,

     fusion.PER_PERSON_NAMES_F pn1,

     fusion.PER_PERSON_NAMES_F pn2,

     fusion.POR_AMX_TASKS tasks,

     fusion.POR_AMX_PARTICIPANTS parts,

     fusion.FND_LOOKUP_VALUES tal,

     fusion.FND_LOOKUP_VALUES pal,

     fusion.FND_LOOKUP_VALUES prl,

     fa_fusion_soainfra.WFAPPROVALGROUPS apg,

     fa_fusion_soainfra.WFAPPROVALGROUPMEMBERS apgm

WHERE 1=1

and rules.task_id = tasks.task_id

 and rules.rule_id = actions.rule_id

 and actions.worker_id = pn1.PERSON_ID(+)

 and pn1.effective_start_date(+) < sysdate

 and pn1.effective_end_date(+) > sysdate

 and pn1.name_type(+) = 'GLOBAL'

 and actions.DO_NOT_ROUTE_BEYOND_ID = pn2.PERSON_ID(+)

 and pn2.effective_start_date(+) < sysdate

 and pn2.effective_end_date(+) > sysdate

 and pn2.name_type(+) = 'GLOBAL'

 and rules.participant_id = parts.PARTICIPANT_ID

 and tasks.LOOKUP_CODE = tal.LOOKUP_CODE

 and tal.LOOKUP_TYPE = tasks.TASK_LOOKUP

 and tal.LANGUAGE = 'US'

 and parts.LOOKUP_CODE = pal.LOOKUP_CODE

 and pal.LOOKUP_TYPE = tasks.PARTICIPANT_LOOKUP

 and pal.LANGUAGE = 'US'

 and nvl(rules.PRIORITY, 'MEDIUM') = prl.LOOKUP_CODE

 and prl.LOOKUP_TYPE = 'ORA_POR_AMX_RULE_PRIORITY'

 and prl.LANGUAGE = 'US'

 and rules.SANDBOX_FLAG <> 'Y'

 and tasks.TASK_KEY = 'ReqApproval'

 and actions.approval_group_name = apg.approvalgroupname(+)

 and apg.approvalgroupid = apgm.approvalgroupid(+)

 )

order by task_key, PARTICIPANT_KEY, rule_number

======================================================================

/*SQL Query to get Purchasing Documents Approval rules*/

SELECT DISTINCT * FROM (

select 

     tal.MEANING as task,

     pal.MEANING as participant,

     rules.rule_number,

     actions.action_number,

     rules.display_rule_name,

     rules.description,

     rules.active_flag,

     prl.MEANING as priority,

     rules.sandbox_flag,

     decode(rules.always_apply_flag, 'Y', 'Rule always applies',

rules.conditions_string) as conditions,

     actions.action_code,

     decode(actions.action_code, 'AUTOMATIC', actions.SET_OUTCOME_TO_CODE,

null) as AUTOMATIC_OUTCOME,

     decode(actions.action_code, 'AUTOMATIC', null,

actions.ROUTE_USING_CODE) as ROUTE_USING_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SINGLE_APPROVER', actions.USER_TYPE_CODE,

null)) as SINGLE_APPROVER_TYPE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SINGLE_APPROVER', pn1.DISPLAY_NAME, null))

as SINGLE_APPROVER_NAME,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY',

actions.SUP_CHAIN_OF_CODE, 'JOB_LEVEL', actions.SUP_CHAIN_OF_CODE, null)) as

APPROVAL_CHAIN_OF,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY',

actions.START_WITH_CODE, 'JOB_LEVEL', actions.START_WITH_CODE, null)) as

APPROVAL_START_WITH,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY',

actions.NUMBER_OF_APPROVAL_LEVELS, null)) as NUMBER_OF_SUPERVISORY_LEVELS,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'JOB_LEVEL', actions.MINIMUM_JOB_LEVEL,

'POSITION_HIERARCHY', actions.MINIMUM_JOB_LEVEL, null)) as MINIMUM_JOB_LEVEL,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'JOB_LEVEL', actions.INCLUDE_CODE, null)) as

INCLUDE_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'SUPERVISORY_HIERARCHY', pn2.DISPLAY_NAME,

'JOB_LEVEL', pn2.DISPLAY_NAME, null)) as TOP_WORKER,      

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'APPROVAL_GROUP',

actions.APPROVAL_GROUP_NAME, null)) as APPROVAL_GROUP_NAME,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'APPROVAL_GROUP',

actions.ALLOW_EMPTY_GROUPS_FLAG, null)) as ALLOW_EMPTY_GROUPS_FLAG,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY',

actions.POSITION_HIERARCHY, null)) as POSITION_HIERARCHY,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY',

actions.POSITION_CHAIN_OF_CODE, null)) as POSITION_CHAIN_OF_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY',

actions.POSITION_START_WITH_CODE, null)) as POSITION_START_WITH_CODE,

     decode(actions.action_code, 'AUTOMATIC', null,

decode(actions.ROUTE_USING_CODE, 'POSITION_HIERARCHY', actions.POSITION_ID,

null)) as TOP_POSITION_ID

     --, apg.approvalgroupname

     , (CASE WHEN (actions.action_code <> 'AUTOMATIC' and actions.ROUTE_USING_CODE ='APPROVAL_GROUP')

THEN apgm.sequence

ELSE NULL

END) sequence

     , (CASE WHEN (actions.action_code <> 'AUTOMATIC' and actions.ROUTE_USING_CODE ='APPROVAL_GROUP')

THEN APGM.MEMBER

ELSE NULL

END) MEMBER

, parts.PARTICIPANT_KEY

, TASKS.TASK_KEY

from fusion.POR_AMX_RULES rules,

     fusion.POR_AMX_ACTIONS actions,

     fusion.PER_PERSON_NAMES_F pn1,

     fusion.PER_PERSON_NAMES_F pn2,

     fusion.POR_AMX_TASKS tasks,

     fusion.POR_AMX_PARTICIPANTS parts,

     fusion.FND_LOOKUP_VALUES tal,

     fusion.FND_LOOKUP_VALUES pal,

     fusion.FND_LOOKUP_VALUES prl,

     fa_fusion_soainfra.WFAPPROVALGROUPS apg,

     fa_fusion_soainfra.WFAPPROVALGROUPMEMBERS apgm

where rules.task_id = tasks.task_id

 and rules.rule_id = actions.rule_id

 and actions.worker_id = pn1.PERSON_ID(+)

 and pn1.effective_start_date(+) < sysdate

 and pn1.effective_end_date(+) > sysdate

 and pn1.name_type(+) = 'GLOBAL'

 and actions.DO_NOT_ROUTE_BEYOND_ID = pn2.PERSON_ID(+)

 and pn2.effective_start_date(+) < sysdate

 and pn2.effective_end_date(+) > sysdate

 and pn2.name_type(+) = 'GLOBAL'

 and rules.participant_id = parts.PARTICIPANT_ID

 and tasks.LOOKUP_CODE = tal.LOOKUP_CODE

 and tal.LOOKUP_TYPE = tasks.TASK_LOOKUP

 and tal.LANGUAGE = 'US'

 and parts.LOOKUP_CODE = pal.LOOKUP_CODE

 and pal.LOOKUP_TYPE = tasks.PARTICIPANT_LOOKUP

 and pal.LANGUAGE = 'US'

 and nvl(rules.PRIORITY, 'MEDIUM') = prl.LOOKUP_CODE

 and prl.LOOKUP_TYPE = 'ORA_POR_AMX_RULE_PRIORITY'

 and prl.LANGUAGE = 'US'

 and rules.SANDBOX_FLAG <> 'Y'

 and tasks.TASK_KEY = 'DocumentApproval'

 -- and tasks.TASK_KEY = 'ReqApproval'

 and actions.approval_group_name = apg.approvalgroupname(+)

 and apg.approvalgroupid = apgm.approvalgroupid(+)

 )

order by task_key, PARTICIPANT_KEY, rule_number

Click Here for reference catalogs.