Query to get Requestion Approval rules (DOA)

 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