with por_lookup_list as (
select lookup_type,
lookup_code,
meaning
from fnd_lookups
where enabled_flag='Y'
and sysdate between nvl(start_date_active,to_date('01/01/1951','DD/MM/YYYY')) and nvl(end_date_active,to_date('31/12/4712','DD/MM/YYYY'))
and lookup_type in ('POR_AMX_ACTION','POR_AMX_ROUTE_TO','POR_AMX_SINGLE_APPROVER','YES_NO'))
select tl.meaning TASK,
r.rule_id,
r.display_rule_name RULE,
(select meaning
from por_lookup_list
where r.active_flag=lookup_code
and lookup_type ='YES_NO') isactive,
sl.meaning STAGE,
decode(ALWAYS_APPLY_FLAG,'Y','Always Apply', r.conditions_string) CONDITION,
pl.meaning PARTICIPANT,
(select meaning
from por_lookup_list
where a.action_code=lookup_code
and lookup_type ='POR_AMX_ACTION')action_type,
(select meaning
from por_lookup_list
where a.ROUTE_USING_CODE=lookup_code
and lookup_type ='POR_AMX_ROUTE_TO' ) Route_Using,
(select meaning
from por_lookup_list
where a.user_type_code=lookup_code
and lookup_type ='POR_AMX_SINGLE_APPROVER' )User_Type,
paag.APPROVAL_GROUP_NAME Approval_Group,
(select listagg(fwags.member,',') as mem
from fa_fusion_soainfra.wfapprovalgroups fwag,
fa_fusion_soainfra.wfapprovalgroupmembers fwags
where fwag.approvalgroupid=fwags.approvalgroupid
and fwag.approvalgroupname=paag.approval_group_name
group by fwag.approvalgroupid,
fwag.approvalgroupname) members
from por_amx_rules r,
por_amx_tasks t,
fnd_lookups tl,
por_amx_participants p,
fnd_lookups pl,
por_amx_stages s,
fnd_lookups sl,
por_amx_actions a,
por_amx_approval_groups paag
where 1=1
and r.sandbox_flag='Y'
and r.task_id=t.task_id
and tl.lookup_type=t.task_lookup
and tl.lookup_code=t.lookup_code
and r.participant_id=p.participant_id
and t.participant_lookup=pl.lookup_type
and pl.lookup_code=p.lookup_code
and s.stage_id=p.stage_id
and t.stage_lookup=sl.lookup_type
and s.lookup_code=sl.lookup_code
and a.rule_id=r.rule_id
and r.active_flag ='Y'
and a.approval_group_name = paag.APPROVAL_GROUP_NAME
--and paag.APPROVAL_GROUP_NAME='DOA_ITA_L2_03'
--and t.task_key=:bindTaskKey
and (t.task_lookup in (:p_task_name) or coalesce(:p_task_name, null) is null)
and (r.active_flag in (:p_active) or coalesce(:p_active, null) is null)
and r.creation_date between nvl(:p_dt_from,r.creation_date) and nvl(:p_dt_to,r.creation_date)
order by sl.meaning,
pl.meaning desc