Multiple level of approval using AME

Multiple level of approval using AME

• Define attributes as per the requirement 
• Define conditions as per the requirement 
• Define approver group
  Give Name and Description
  Select Order Number-1
  Select Voting method-Serial
  Select Usage Type-Dynamic

Write Query

SELECT * FROM (SELECT xx_approval_pkg.approval_insert('REQAPPRV',10,:transactionId) aa FROM DUAL
union all
SELECT xx_approval_pkg.approval_insert('REQAPPRV',20,:transactionId) aa FROM DUAL
union all
SELECT xx_approval_pkg.approval_insert('REQAPPRV',30,:transactionId) aa FROM DUAL
union all
SELECT xx_approval_pkg.approval_insert('REQAPPRV',40,:transactionId) aa FROM DUAL) x WHERE aa <> 'PERSON_ID:0'

• Define Action Type
• Define Rule.

Logic behind my program,
Query will call xx_approval_pkg.approval_insert package.function
here, REQAPPRV --means Requisition approval--Can pass any value(EXPENSE,INVOICE,REQAPPRV)
Sequence -10,20,30,40 ---Can send any number of sequences(I tested with upto 100)
:transactionId is the requisition header id for Requosition.

If sequence is 10, program will create hierarchy information and will store in a tem table xxvir.xxvirtu_doc_supervisor and return personid for sequence 10 only. next sequence onwards only return personid (no hierachy creation). 
If for any sequence value does not retrun any value that means it will retrun personid=0.

xxvir.xxvirtu_doc_supervisor table values
seq --- person_id---itemtype---itemkey
10----1001-----REQAPPRV---XXX
20---1002---REQAPPRV---XXX
30---1003---REQAPPRV---XXX.


FUNCTION approval_insert (
      p_itemtype      VARCHAR2,
      p_itemkey       VARCHAR2,
      p_sequence      NUMBER,
      p_document_id   NUMBER
   )
      RETURN VARCHAR2
   AS
      v_person_id   NUMBER;
   --PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      IF p_sequence = 10
      THEN
         IF p_itemtype = 'EXPENSE'
         THEN
            exp_approval_list_insert (p_itemtype         => 'EXPENSE',
                                      p_itemkey          => NULL,
                                      p_document_id      => p_document_id
                                     );
         ELSIF p_itemtype = 'INVOICE'
         THEN
            inv_approval_list_insert (p_itemtype         => 'INVOICE',
                                      p_itemkey          => NULL,
                                      p_document_id      => p_document_id
                                     );
         ELSIF p_itemtype = 'REQAPPRV'
         THEN
            req_approval_list_insert (p_itemtype         => 'REQAPPRV',
                                      p_itemkey          => NULL,
                                      p_document_id      => p_document_id
                                     );
         END IF;
      --RETURN 0;
      END IF;

      --ELSE
      BEGIN
         SELECT person_id
           INTO v_person_id
           FROM xxvir.xxvirtu_doc_supervisor
          WHERE itemtype = p_itemtype
            AND document_id = p_document_id
            AND sequence_number = p_sequence;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_person_id := 0;
            RETURN ('PERSON_ID:' || v_person_id);
      END;

      --RETURN v_person_id;
      RETURN ('PERSON_ID:' || v_person_id);
   -- END IF;
   END;

Based on the document type and document id program will generate the hierarchy and will store the person id value in  xxvir.xxvirtu_doc_supervisor table.

AME query will return like 1001,1002,1003 (person ids) and then approval message will go to first 1001, once it approves the approval message will go to 1002 and then to1003. Finally if 1003 approves then the requisition will be approved.