Oracle iProcurement Integration with Other Modules

Approval Process

In conjunction with Oracle Workflow, Oracle iProcurement facilitates a paperless, automated approval process for purchase requisitions with the following features:

  Submitted purchase requisitions may be tracked as they proceed through the routing and approval process.

  Approvers can delegate approval responsibility and/or use e-mail including proxies for approvals rather than logging into the Oracle system. E-mail approval capability is very useful for road warriors.

  Supporting documents can be attached with the automated purchase requisition, which approvers can review.

  Hard copies and manual/physical approval processes can be eliminated.

Supplier Management

Oracle iProcurement shares suppliers’ tables with Oracle Purchasing, Inventory, and Payables creating an integrated environment for supplier information that is available to select, update, or create new in Oracle iProcurement.

  Supplier contact information, when provided (i.e., vendor contact name, phone number, e-mail address), can be stored to the database via Oracle iProcurement and queried when creating new requisitions in both Oracle iProcurement and Oracle Purchasing.

Catalog Management

Oracle iProcurement is optionally a catalog-driven procurement system, which helps businesses organize the items, services, and vendors they routinely procure products from.

  The catalog management process allows the creation of catalogs containing items and services from which organizations procure goods.

  The catalog management process allows requesters to select and purchase items and services from on-line catalogs using drop down functionality.

  Requesters creating purchase requisitions can easily do so following easy-to-use, wizard-driven catalogs with instructions for ordering items and services.

Order Process

The approval and order creation process provided by Oracle iProcurement simplifies the procurement process with the following functions:

  The shopping process locates and selects items AND can be used to perform a comparative analysis of different items.

  The receive order process allows requesters to receive items and control the details for the delivery of orders.

  The checkout and submit process allows requesters to specify the quantity of purchase.

 Data Validation

Like any Oracle product, business rules, cross-validation and security rules, and other data validation rules can be defined and enforced.  Oracle iProcurement validates drop-down lists (LOV) for Project Accounting, for example:

  Expenditure Type and Project Number can be selected from a pick list during data entry of purchase requisitions.

  Project sub-tasks are validated against Oracle Projects, and transaction controls are used to prevent purchase requisitions against sub-tasks.

Integration with Oracle E-Business Suite

Oracle iProcurement integrates seamlessly with the Oracle E-Business Suite and supports a single global instance allowing multiple users simultaneous access to key functions and features.

Oracle General Ledger Integration

Oracle General Ledger shares accounting accounting flexfield and charge account combinations with Oracle iProcurement, in addition to providing a set of books (SOB) and exchange rates for the module.

Oracle Purchasing Integration     

Oracle Purchasing can use the Catalog Extract feature to load and create the Oracle iProcurement catalog.

Oracle Purchasing can use the purchase orders created via Oracle iProcurement’s requisitions option for inquiry and for receiving activity.

Oracle Payables Integration 

Oracle Payables manages catalog content and requisitioning using Oracle iProcurement functionality.

Oracle Payables uses the purchase orders and receipts created in Oracle iProcurement and Oracle Purchasing for two and three-way matching and accounting.

Oracle Payables tracks projects and the expenditures related to projects created in Oracle iProcurement.

Oracle HRMS Integration              

Oracle iProcurement requesters and approvers are validated against in the employee-supervisor relationship defined in Oracle HRMS.

It is also possible to validate requesters and approvers against Oracle Projects project and task manager assignments, but this does require some customizations of the workflow to achieve.

Oracle Workflow Integration                                                                                                                                     

Oracle Workflow sends Oracle iProcurement notifications to requesters and approvers, using the Requisition Approval Workflow, PO Create Document Workflow, and PO Approval Workflow.

Oracle e-Commerce and XML Gateway Integration

Oracle iProcurement integrates with e-Commerce and the XML Gateway for use with electronic purchase order transmissions

Conclusion

Oracle iProcurement is a mature product designed to facilitate online shopping that provides requesters the ability to create, manage, and track purchase requisitions and purchase orders, and helps managers and buying professionals to source and approve requisitions before needless spending occurs.

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