PER Approvals Deviation

          select ame_approver_type_pkg.getApproverDisplayName3(aprd.name) name
                ,aprd.approver_order_number order_number
                ,ame_approver_deviation_pkg.getreasonDescription(aprd.reason) reason
                ,to_char(aprd.effective_date,'YYYY-MM-DD"T"HH24:MI:SS') effective_date
                ,apprcategory.meaning apprcategory
                ,origsys.meaning origsystem
                ,aprd.display_name display_name
                ,aprd.authority authority
                ,(select meaning from 
                 fnd_lookups where lookup_type = 'AME_APPROVAL_STATUS'
                 and lookup_code = aprd.approval_status ) approval_status
                ,actl.user_action_type_name action_type
                ,aprd.member_order_number member_order_number
                ,aprd.api_insertion api_insertion
            from ame_txn_approvers aprd
                ,fnd_lookups origsys 
                ,fnd_lookups apprcategory
                ,ame_action_types_vl actl
            where temp_transactions_id = :transSequenceId
              and aprd.item_class = :tempItemClass
              and aprd.item_id = :itmid
              and origsys.lookup_type = 'FND_WF_ORIG_SYSTEMS'
              and origsys.lookup_code = aprd.orig_system
              and apprcategory.lookup_type = 'AME_APPROVER_CATEGORY'
              and apprcategory.lookup_code = aprd.approver_category
              and aprd.action_type_id = actl.action_type_id(+)
              and sysdate between nvl(actl.start_date,sysdate-(1/86400)) and nvl(actl.end_Date,sysdate)
           order by aprd.temp_transactions_id,aprd.approver_order_number