AME查询所有审批规则

SELECT TP.APPLICATION_ID,
       RU.RULE_ID,
       RU.ACTION_ID,
       RU.APPROVAL_GROUP_ID,
       GP.START_DATE,
       GP.END_DATE,
       GP.IS_STATIC,
       GP.VOTING_REGIME,
       ---------------------------------------------
       TP.TRANSACTION_TYPE_ID,
       TP.TRANSACTION_TYPE_NAME,
       RU.RULE_NAME,
       decode(RU.RULE_TYPE, '1', '列表', '例外') RULE_TYPE,
       decode(RU.Approver_Category, 'A', '审批人', '供参考') Approver_Category,
       RU.ACTION_NAME,
       GP.GROUP_NAME,
       GP.ORDER_NUMBER,
       GP.USAGE_TYPE,
       GP.QUERY_STRING,
       GP.VOTING_REGIME_TL
  FROM (SELECT DISTINCT ACA.APPLICATION_NAME    TRANSACTION_TYPE_NAME,
                        ACA.TRANSACTION_TYPE_ID,
                        ACA.APPLICATION_ID      APPLICATION_ID
          FROM AME_CALLING_APPS ACA
         WHERE 1 = 1
           and ACA.END_DATE >= sysdate
           and ACA.Start_Date < = sysdate
           and ((ACA.TRANSACTION_TYPE_ID LIKE 'CUX%' OR
               ACA.FND_APPLICATION_ID = 20003) OR
               ACA.TRANSACTION_TYPE_ID IN
               ('PURCHASE_ORDER', 'PURCHASE_REQ'))) TP,
       (SELECT R.RULE_ID,
               R.DESCRIPTION RULE_NAME,
               A.ACTION_ID,
               A.DESCRIPTION ACTION_NAME,
               RU.ITEM_ID APPLICATION_ID,
               to_char(A.PARAMETER) APPROVAL_GROUP_ID,
               RU.Approver_Category,
               R.RULE_TYPE
          FROM AME_RULES         R,
               AME_ACTIONS       A,
               AME_ACTION_USAGES AU,
               AME_RULE_USAGES   RU
         WHERE 1 = 1
           AND R.RULE_ID = AU.RULE_ID
           AND AU.ACTION_ID = A.ACTION_ID
           AND RU.RULE_ID = R.RULE_ID
           and A.END_DATE >= sysdate
           and A.Start_Date < = sysdate
           and R.END_DATE >= sysdate
           and R.Start_Date < = sysdate
           and AU.END_DATE >= sysdate
           and AU.Start_Date < = sysdate
           and RU.END_DATE >= sysdate
           and RU.Start_Date < = sysdate
           AND RU.ITEM_ID in
               (SELECT C.APPLICATION_ID
                  FROM AME_CALLING_APPS C
                 WHERE ((C.TRANSACTION_TYPE_ID LIKE 'CUX%' OR
                       C.FND_APPLICATION_ID = 20003) OR
                       C.TRANSACTION_TYPE_ID IN
                       ('PURCHASE_ORDER', 'PURCHASE_REQ')))) RU,
       (SELECT to_char(APPROVERGROUPEO.APPROVAL_GROUP_ID) APPROVAL_GROUP_ID,
               APPROVERGROUPCONFIGEO.APPLICATION_ID APPLICATION_ID,
               APPROVERGROUPEO.START_DATE,
               APPROVERGROUPEO.END_DATE,
               APPROVERGROUPEO.NAME GROUP_NAME,
               APPROVERGROUPEO.QUERY_STRING,
               APPROVERGROUPEO.IS_STATIC,
               APPROVERGROUPCONFIGEO.VOTING_REGIME,
               APPROVERGROUPCONFIGEO.ORDER_NUMBER,
               QRYLOOKUP.MEANING USAGE_TYPE,
               VOTINGLOOKUP.MEANING VOTING_REGIME_TL
          FROM AME_APPROVAL_GROUPS_VL    APPROVERGROUPEO,
               AME_APPROVAL_GROUP_CONFIG APPROVERGROUPCONFIGEO,
               FND_LOOKUPS               QRYLOOKUP,
               FND_LOOKUPS               VOTINGLOOKUP
         WHERE APPROVERGROUPEO.APPROVAL_GROUP_ID =
               APPROVERGROUPCONFIGEO.APPROVAL_GROUP_ID
           AND QRYLOOKUP.LOOKUP_TYPE = 'AME_QUERY_USAGE_TYPE'
           AND QRYLOOKUP.LOOKUP_CODE = APPROVERGROUPEO.IS_STATIC
           AND VOTINGLOOKUP.LOOKUP_TYPE = 'AME_APG_VOTING_REGIME'
           AND VOTINGLOOKUP.LOOKUP_CODE =
               APPROVERGROUPCONFIGEO.VOTING_REGIME
           AND SYSDATE BETWEEN APPROVERGROUPEO.START_DATE AND
               NVL(APPROVERGROUPEO.END_DATE - (1 / 84600), SYSDATE)
           AND SYSDATE BETWEEN APPROVERGROUPCONFIGEO.START_DATE AND
               NVL(APPROVERGROUPCONFIGEO.END_DATE - (1 / 84600), SYSDATE)) GP


 WHERE 1 = 1
   AND TP.APPLICATION_ID = RU.APPLICATION_ID
   AND TP.APPLICATION_ID = GP.APPLICATION_ID
   AND RU.APPROVAL_GROUP_ID = GP.APPROVAL_GROUP_ID
   --and RU.RULE_NAME = '%采购%'
 ORDER BY TP.TRANSACTION_TYPE_NAME asc,
          RU.RULE_NAME             asc,
          GP.ORDER_NUMBER          asc