How to Use Dynamic Queries To Determine Approvers for iProcurement Requisitions - Example Based on Requisition Line Requester

This type of functionality requires customization which is not completely covered in this answer, but the
information contained here should be helpful in understanding some of the capability and some options
to consider.

AME provides Dynamic Query functionality that may be used to generate approvers based on the line
level REQUESTER or other requisition header or line level values.

As an Example, use the following Dynamic Query to generate an Approval group based on the
requisition line level REQUESTER (Note: Additional logic may be needed if there are 2 or more
different requesters specified on the requisition lines. If all the lines on the requisition have the same requester, then this should work fine.)

1.  Please review the following EXAMPLE 1 for details about a new ATTRIBUTE, CONDITION, and RULE that can be created. The ATTRIBUTE returns a true or false value. If all of the requesters on the requisition lines are the same as the preparer of the requisition, then the attribute returns as true.
If one or more of the requesters on the requisition lines is different than the preparer of the requisition, then the attribute returns as false. 

Then the RULE checks the CONDITION and adds an additional approver (using an Approver Group) if the ATTRIBUTE is false - (false in this case means at least one requester is different than the preparer)

EXAMPLE 1

1. Navigate to AME responsibility
2. Choose Setup for Purchase Requisition Approval transaction type
3. Create a new Attribute
 

Attribute Name	Attribute Description	Category	Usage Type	Item Class	Data Type
REQUESTERS_SAME_AS_PREPARER	True if All Requesters on Req Lines Are Same As Preparer on Req Header	Others	Dynamic	Header	Boolean

Query used for the new attribute is as follows:
SELECT MIN (myvalue)

  FROM (SELECT DECODE (prla.to_person_id, prha.preparer_id, 'true', 'false') myvalue

        FROM po_requisition_headers_all prha, po_requisition_lines_all prla

        WHERE prla.requisition_header_id = prha.requisition_header_id

        AND prha.requisition_header_id = :transactionId)

4. Create a new Condition based on the above new Attribute

REQUESTERS_SAME_AS_PREPARER is False

5. Create a new Rule that uses the above new Condition based on the new Attribute

Item is Dave-Germany-001 and Requester Not Preparer
- if REQUESTERS_SAME_AS_PREPARER is False (i.e., if at least one of the requests is not the same as the preparer of the requisition)
- then add an additional post list approver from a specific approval group defined previously


6. Test the issue
- Create a new requisition in iProcurement Vision Germany
- Specify the requester the same as the preparer
- checkout step 2 of 3 (see the approval list does not include the additional post list approver)
- go back to checkout step 1 of 3
- change the requester to be different than the preparer
- checkout step 2 of 3 (see the approval list DOES include the ADDITIONAL post list approver) 

 2.  To augment the Example 1 further, Example 2 below shows some modifications which retrieves all the Supervisors above the Requester, and adds all these supervisors to the approval list.  This example creates
multiple approver groups which each return a single approver in the supervisor approver hierarchy above the requester.

EXAMPLE 2

Requester --> Supervisor 1 (approver group 1)--> Supervisor 2 (approver group 2) --> Supervisor 3 (approver group 3) ... Supervisor 10 (approver group)

Setup Approver groups 1-10 where each approver group is based on a dynamic query that returns the supvisor corresponding to that supervisor level (up to 10 levels). (or up to 20 if you will ever have 20 supervisors above the requester)

Below is the query to use (for Supervisor 2 level). You just need to modify the rownum value in the last line of the query to specify the supervisor level for each corresponding supervisor level / approver group definition.

Supervisor Level     Approver Group        rownum
1        1         1
2        2         2
...
10    10       10
SELECT x.myameid

  FROM (SELECT 'PERSON_ID:' || pecx.employee_id myameid, ROWNUM myrownum

          FROM FND_USER fndu,

               per_employees_current_x pecx,

               (    SELECT PERA.SUPERVISOR_ID

                      FROM PER_ASSIGNMENTS_F PERA

                     WHERE EXISTS

                              (SELECT '1'

                                 FROM PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1

                                WHERE     TRUNC (SYSDATE) BETWEEN PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE

                                      AND PERF.PERSON_ID = PERA.SUPERVISOR_ID

                                      AND PERA1.PERSON_ID = PERF.PERSON_ID

                                      AND TRUNC (SYSDATE) BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE

                                      AND PERA1.PRIMARY_FLAG = 'Y'

                                      AND PERA1.ASSIGNMENT_TYPE = 'E'

                                      AND EXISTS

                                             (SELECT '1'

                                                FROM PER_PERSON_TYPES PPT

                                               WHERE     PPT.SYSTEM_PERSON_TYPE IN ('EMP', 'EMP_APL')

                                                     AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID))

                START WITH     PERA.PERSON_ID =

                                  (SELECT MIN (prla.to_person_id)

                                     FROM po_requisition_headers_all prha,

                                          po_requisition_lines_all prla

                                    WHERE     prla.to_person_id <>

                                                 prha.preparer_id

                                          AND prla.requisition_header_id = prha.requisition_header_id

                                          AND prha.requisition_header_id = :transactionId)

                           AND TRUNC (SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE

                           AND PERA.PRIMARY_FLAG = 'Y'

                           AND PERA.ASSIGNMENT_TYPE = 'E'

                CONNECT BY     PRIOR PERA.SUPERVISOR_ID = PERA.PERSON_ID

                           AND TRUNC (SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE

                           AND PERA.PRIMARY_FLAG = 'Y'

                           AND PERA.ASSIGNMENT_TYPE = 'E') c

         WHERE     fndu.employee_id = c.supervisor_id

               AND pecx.employee_id = c.supervisor_id) x

 WHERE myrownum = 2

Then create a RULE that uses actions of 1) require approval from ... approver group 1; 2) require approval from ... approver group 2; ... etc
- The single RULE will require approval from all 10 of the approver groups. (if there are only 5 supervisors above the requester, then only the first five approver group queries will return a value, and only 5 supervisors will be in the approval list. )

3.  Example 3 shows how to check whether a certain user is the Nth supervisor above the preparer.

EXAMPLE 3:

Check whether a certain person / user is the 1st (or Nth) supervisor above the preparer of the requisition

Define attribute [ XX Supervisor is Casey Brown ] based on a dynamic query like the following (this will check if the person associated to username 'CBROWN' is the 1st supervisor above the Preparer of the requisition.)
SELECT NVL ('true', 'false')

  FROM (SELECT 'PERSON_ID:' || pecx.employee_id myameid, ROWNUM myrownum

          FROM FND_USER fndu,

               per_employees_current_x pecx,

               (    SELECT PERA.SUPERVISOR_ID

                      FROM PER_ASSIGNMENTS_F PERA

                     WHERE EXISTS

                              (SELECT '1'

                                 FROM PER_PEOPLE_F PERF, PER_ASSIGNMENTS_F PERA1

                                WHERE     TRUNC (SYSDATE) BETWEEN PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE

                                      AND PERF.PERSON_ID = PERA.SUPERVISOR_ID

                                      AND PERA1.PERSON_ID = PERF.PERSON_ID

                                      AND TRUNC (SYSDATE) BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE

                                      AND PERA1.PRIMARY_FLAG = 'Y'

                                      AND PERA1.ASSIGNMENT_TYPE = 'E'

                                      AND EXISTS

                                             (SELECT '1'

                                                FROM PER_PERSON_TYPES PPT

                                               WHERE     PPT.SYSTEM_PERSON_TYPE IN ('EMP', 'EMP_APL')

                                                     AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID))

                START WITH     PERA.PERSON_ID =

                                  (SELECT preparer_id

                                     FROM po_requisition_headers_all prha

                                    WHERE prha.requisition_header_id = :transactionId)

                           AND TRUNC (SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE

                           AND PERA.PRIMARY_FLAG = 'Y'

                           AND PERA.ASSIGNMENT_TYPE = 'E'

                CONNECT BY     PRIOR PERA.SUPERVISOR_ID = PERA.PERSON_ID

                           AND TRUNC (SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND PERA.EFFECTIVE_END_DATE

                           AND PERA.PRIMARY_FLAG = 'Y'

                           AND PERA.ASSIGNMENT_TYPE = 'E') c

         WHERE     fndu.employee_id = c.supervisor_id

               AND pecx.employee_id = c.supervisor_id

               AND fndu.user_name = 'CBROWN') x

 WHERE myrownum = 1

Define a Condition based on the above CUSTOM attribute:  XX Supervisor is Casey Brown = true
Define a Rule that uses the above condition and adds approvers as required based on this condition