AME Approval Purchase Requisition with custom approver list

AME Approval Purchase Requisition with custom approver list

Profile option

Make sure the profile option AME: Installed is set to Yes for Oracle Payables. Navigate to System Administrator, Profile, System and query AME: Installed.

Approval Roles

Login as SYSADMIN and navigate to User Management. Query the user you want to use for setup AME and use Update. Do not use SYSADMIN.
Choose Assign Roles.
Assign Approvals Management Business Analyst and Approvals Management Administrator.

Grants

Choose authorization Functional Administrator and choose Create Grants.
Give the grant a name and description.
Grantee type is specific user. Grantee is the user that is assigned to do the setup for AME.
Object AME Transaction Types. Data context type All Rows.
Set AME Calling Applications.
Now run Workflow Background Engine.

Fix required?

In some cases we could not see the roles assigned even if we did these steps. We needed to update the roles as follows
update wf_roles
set start_date = to_date('01-01-1900','DD-MM-YYYY')
where name like 'UMX|AME%'
and start_date is null
Note that you may also need to do this for  UMX|UMX_EXT_ADMIN.
If you don’t see any menu options after assigning the role check the following. Best is to create a new user and do not use SYSADMIN.
734280.1 - UMX Error: 'There are no functions available for this responsibility' And/Or 'There are no valid navigations for this responsibility' when Accessing 'User Management' Responsibility.
1. Log into the applications as SYSADMIN User.
2. Choose User Management responsibility.
3. Navigate to Users web page.
4. Search and find the user you want to inherit the Security Administrator and Customer Administrator Roles.
5. Click on Update Icon.
6. Click on Assign Roles button.
7. Find and choose 'Security Administrator' Role.
8. Apply.
9. Repeat the Steps (6-8) for 'Customer Administrator' Role.

Setup Approval Groups

Login as Approvals Management Business Analyst.
On the right side of the page choose Transaction Type Purchase Requisition Approval.

Choose Approver groups and create.

Name: Approvers requisition
Description: Approval group for purchase requisitions
Usage Type: Dynamic
Order Number: 10
Voting Method: Serial
We usually use a custom package to find the persons that should approve. This could be on position hierarchy, cost center hierarchy, but also with one-time off approvers, project management approval, etc. The package we use is XXPZ_AME_PKG. We also use a sequence and sub-sequence for ordering, but that is not required of course.

Query

SELECT 'person_id:'||x.person_id
FROM TABLE (CAST(XXPZ_AME_PKG.approval_requisition(:transactionId) AS xxpz_approval_table) ) x
WHERE NOT EXISTS (select null from po_requisition_headers_all prh where prh.requisition_header_id = :transactionId and prh.preparer_id = x.person_id)
ORDER BY x.sequence
,x.sub_sequence

Setup Action Types

Go back to the dashboard and choose Action Types.
Choose use existing action type and choose approval-group chain of authority.
Choose next and finish.

Setup Rules

Go back to the dashboard and choose Approval Rules. Choose create.
Item class : Header
Rule type : List creation
Name  : Approval Requisition
Do not add conditions, add action type approval-group chain of authority.

Change document type

Login as Purchasing, Superuser.
Choose Setup, Purchasing, Document Types.
Choose Requisition, Purchase and update.
Change Approval Transaction Type in PURCHASE_REQ.

Create type (database)

create or replace type XXPZ_approval_type as object
(person_id number
,sequence number
,sub_sequence number
,text varchar2(4000))
;
/
create or replace type XXPZ_approval_table as table of XXPZ_approval_type;
/

Create package (database)

The logic for your package can be anything of course. Here is an example
create or replace
PACKAGE XXPZ_AME_PKG
AS
FUNCTION approval_requisition(p_requisition_header_id IN po_requisition_headers_all.requisition_header_id%type)
RETURN XXPZ_approval_table;
FUNCTION approval_invoice(p_invoice_id IN ap_invoices_all.invoice_id%type)
RETURN XXPZ_approval_table;
FUNCTION approval_hold(p_hold_id IN ap_holds_all.hold_id%type)
RETURN XXPZ_approval_table;
END XXPZ_AME_PKG;
/
The following is an excerpt of the entire package
create or replace PACKAGE BODY XXPZ_AME_PKG
AS

 /*************************APPROVAL_REQUISITION*************************/

 FUNCTION approval_requisition(p_requisition_header_id IN po_requisition_headers_all.requisition_header_id%type)
 RETURN XXPZ_approval_table
 IS

 --
 BEGIN

 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||':'||l_api_name||'()+');
 --
 ----------------------------------------------------------------------------------------------
 -- Initialisation.
 ----------------------------------------------------------------------------------------------
 l_XXPZ_approval.delete();
 l_sequence         := 0;
 l_sub_sequence     := 0;
 G_ORG_ID := FND_PROFILE.Value ('ORG_ID');
 IF NVL (G_ORG_ID,'-1') = '-1'
 THEN
  G_ORG_ID := Get_Org_Id (p_requisition_header_id => p_requisition_header_id, p_invoice_id => NULL);
 END IF;
 G_LEDGER_ID     := Get_Ledger_Id (p_org_id => G_ORG_ID, x_base_currency => G_BASE_CURRENCY);
 G_COSTCENTER_VALUESET_ID := Get_Valueset_Costcenter (p_ledger_id => G_LEDGER_ID, p_cao_id => null);
 G_ITEM_ORG_ID    := Get_Master_Item_Org_Id;
 G_PROJECT_ADMINISTRATOR  := Get_Project_Admin_Role_Id;

    FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Org id=' || G_ORG_ID || ', Ledger Id=' || G_LEDGER_ID ||
            ', CostCenter Valueset=' || G_COSTCENTER_VALUESET_ID || ', Item master org=' || G_ITEM_ORG_ID ||
            ', Project Admin=' || G_PROJECT_ADMINISTRATOR);


 --
 <<balance>>
 FOR i IN c_balance (b_requisition_header_id => p_requisition_header_id)
 LOOP
 --

 l_sub_sequence := 0;
 l_sequence := l_sequence + 1;
 l_cost_center := null;
 l_amount_limit := 0;
 l_person_id := null;

    FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'In c_balance: project=' || i.project_id || ',text=' || i.text ||
    ', amount=' || i.amount);

 --
 ----------------------------------------------------------------------------------------------
 -- Re-calculate to the base currency.
 ----------------------------------------------------------------------------------------------
 l_amount := Convert_To_Base_Currency (p_from_currency => i.currency_code, p_to_currency => G_BASE_CURRENCY, p_amount => i.amount);
 --
 --
 ----------------------------------------------------------------------------------------------
 -- If PO is related to a project, we fetch the project manager as approver. If it is not
 -- project related we check the cost center hierarchy.
 ----------------------------------------------------------------------------------------------
 IF i.project_id IS NOT NULL
 THEN
   --
   -- fetch projectmgr
   l_person_id := get_project_approver(p_project_id => i.project_id);
   --

   l_sub_sequence := l_sub_sequence + 1;
   l_XXPZ_approval.extend;
   l_XXPZ_approval(l_XXPZ_approval.count) := (XXPZ_approval_type(l_person_id, l_sequence, l_sub_sequence, i.text ||chr(10)||'ROLE: projectmanager '));
   --

   -- If project manager does not have sufficient limit, we follow the regular approval by
   -- retrieving the cost center from the project. Now we fetch the position the other way
   -- around: we have a person and fetch his position.

   l_position_id   := get_position_by_person (p_person_id => l_person_id);
   l_position_name := get_position_name(p_position_id => l_position_id);
   l_amount_limit  := get_amount_limit(p_position_id => l_position_id);

   -- If it exceeds the limit of the PM, we continue using the cost center approval flow.

    FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Project related, PM=' || l_person_id ||
    ',position=' || l_position_id ||' (' || l_position_name || '), limit=' || l_amount_limit || ', amount=' || l_amount);



 END IF; -- Project manager found
 -- If we found a project manager, person id is not null. If his limit is sufficient,
 -- amount limit >= l_amount. So we only continue if we either did not have a PM or
 -- his limit is not sufficient.
 IF l_person_id IS NULL OR (l_amount > l_amount_limit)
 THEN

 ----------------------------------------------------------------------------------------------
 -- So now we know whether for this cost center approval is required based on the cost center exemption.
 ----------------------------------------------------------------------------------------------
 --
 IF l_approval_required = G_YES
 THEN
 --
 l_text := i.text ||chr(10)|| 'BALANCE: ' || TO_CHAR(l_amount, G_NUMBER_FORMAT) || ' ' ||i.currency_code ||chr(10)|| 'COST CENTER: ' ||i.cost_center;
 --
 <<amount_above_limit>>
 WHILE (l_amount > l_amount_limit or l_person_id is null)
 LOOP
 --
 ----------------------------------------------------------------------------------------------
 -- Get cost center and position related to this cost center. Fetch the person with this
 -- position and get the amount limit. This is the first budget approver.
 ----------------------------------------------------------------------------------------------
 IF l_cost_center is null
 THEN
 l_cost_center := i.cost_center;
 ELSE
 l_cost_center := get_next_cost_center(p_cost_center => l_cost_center);
 l_text := l_text||' => '||l_cost_center;
 END IF;
 --
 -- Fetch position related to cost center
 l_position_id := get_first_position(p_cost_center => l_cost_center);
 --
 IF l_position_id is not null
 THEN
 l_person_id := get_person(p_position_id => l_position_id);
 l_position_name := get_position_name(p_position_id => l_position_id);
 l_amount_limit := get_amount_limit(p_position_id => l_position_id);
 --
 IF l_person_id is not null
 THEN
 --
 l_role := G_FIRST_BUDGETAPPROVER;

    FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'1st Approver added cc=' || l_cost_center || ', ' || l_person_id || ', position=' || l_position_name || ', limit=' || l_amount_limit);

 --

 l_sub_sequence := l_sub_sequence + 1;
 l_XXPZ_approval.extend;
 l_XXPZ_approval(l_XXPZ_approval.count) := (XXPZ_approval_type(l_person_id, l_sequence, l_sub_sequence, regexp_replace(l_text||chr(10)||'ROL: '||l_role||chr(10)||'POSITIE: '||l_position_name||chr(10)||'LIMIET: '||TO_CHAR(l_amount_limit, G_NUMBER_FORMAT) || ' ' ||i.currency_code,'( ){2,}',' ')));
   --
 END IF;
 --
 END IF;
 --

 --
 END LOOP amount_above_limit;
 --

 END IF; -- Project related

 ----------------------------------------------------------------------------------------------
 --
 END LOOP balance;
 --

 --
 END IF;
 --

    FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||':'||l_api_name||'()+');

 RETURN l_XXPZ_approval;
 --
 EXCEPTION
 WHEN x_exception THEN

    FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Failed due to ' || x_error_message);

    raise_application_error(-20001,x_error_message);
 WHEN OTHERS THEN

    FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Failed unexpected due to ' || x_error_message);

  raise_application_error(-20001,'XXPZ_ame_pkg.approval_requisition: unexpected error when determining the approval list | '||SQLERRM);
 --
 END approval_requisition;

 END XXPZ_AME_PKG;
 /