PLSQL query to fetch Approver Name in AME Approver Groups

 https://aporaclepayables.blogspot.com/2022/08/plsql-query-to-fetch-approver-name-in.html

SELECT *

  FROM (select AAG.NAME, ac.APPLICATION_NAME,  ame_approver_type_pkg.getApproverDisplayName3(agi.parameter) APPROVER,

               appTypesLookUp.Meaning APPROVER_TYPE,

               agi.order_number ORDER_NUMBER

          from ame_approval_group_items agi, AME_APPROVAL_GROUPS_VL AAG, AME_APPROVAL_GROUP_CONFIG AAGC,

         ame_calling_apps_vl ac, 

               (select *

                  from wf_roles

                 where status = 'ACTIVE'

                   AND nvl(expiration_date, sysdate) >= sysdate

                   AND orig_system in ('PER', 'POS', 'FND_USR')) roles,

               FND_LOOKUPS appTypesLookUp

         where sysdate between agi.start_date and

               nvl(agi.end_date - (1 / 86400), sysdate)

           AND agi.parameter_name = 'wf_roles_name'

           AND roles.name(+) = agi.parameter

           and AAG.APPROVAL_GROUP_ID = agi.approval_group_id

           and ac.APPLICATION_id = AAGC.Application_Id 

         --  and AAGC.Application_Id = -1

           and AAGC.Approval_Group_Id = AAG.APPROVAL_GROUP_ID

           AND appTypesLookUp.lookup_type = 'FND_WF_ORIG_SYSTEMS'

           AND appTypesLookUp.lookup_code =

               ame_approver_type_pkg.getApproverOrigSystem2(agi.parameter)

        --   AND agi.approval_group_id = :1

        union

        select AAG.NAME, AC.Application_name, apg.name               APPROVER,

               appTypesLookUp.Meaning APPROVER_TYPE,

               agi.order_number       ORDER_NUMBER

          from ame_approval_groups      apg,

               ame_approval_group_items agi,

               FND_LOOKUPS              appTypesLookUp,  AME_APPROVAL_GROUPS_VL AAG, ame_calling_apps_vl ac,

                AME_APPROVAL_GROUP_CONFIG  AAGC

         where sysdate between agi.start_date and

               nvl(agi.end_date - (1 / 86400), sysdate)

           AND sysdate between apg.start_date and

               nvl(apg.end_date - (1 / 86400), sysdate)

           AND agi.parameter_name = 'OAM_group_id'

           AND agi.parameter = to_char(apg.approval_group_id)

           and AAG.APPROVAL_GROUP_ID = agi.approval_group_id

           and AAGC.Approval_Group_Id = AAG.APPROVAL_GROUP_ID

           and ac.APPLICATION_id = AAGC.Application_Id 

           AND appTypesLookUp.lookup_type = 'AME_NESTED_GROUP'

           AND appTypesLookUp.lookup_code = 'NESTED_GROUP'

         --  and AAGC.Application_Id = -1

         --  AND agi.approval_group_id = :2

           ) QRSLT

 ORDER BY ORDER_NUMBER