Creating webservices in PL/SQL for SOA Gateway eBS R12

Creating webservices in PL/SQL for SOA Gateway eBS R12

To create a webservice in eBS R12 you can use the Integrated SOA Gateway. To do this you have to follow the following steps

Step 1: Create database package with annotation

First create a database package with extension pls (!) and the following annotation


CREATE OR REPLACE PACKAGE XXXORAWS AS
/* $Header: XXXORAWS $ */
/*#
* Read Oracle User information webservice
* @rep:scope public
* @rep:product FND
* @rep:displayname General Oracle Webservices for Service Integration
* @rep:category BUSINESS_ENTITY ORACLEWS
*/

Step 2: Create database package procedure with annotation

Now create a procedure for example to create orders through a webservice. In this example we have an order header and lines in one field (XML input). Note that you can pass tables and record constructs as well,

PROCEDURE W_Order (
    OrderHeaderRec   IN T_OrderHeaderRec ,
    OrderLinesTbl    IN T_OrderLineTbl,

but if you call this from other applications they may not always support that!

/*#
* This procedure is for creating the order web service
* @param Account_Name          Account_Name
* @param Tax_Reference_Number  Tax_Reference_Number
* @param Ship_Account_Number   Ship_Account_Number
* @param Ship_Address      Ship_Address
* @param Ship_PC    Ship_PC
* @param Ship_City       Ship_City
* @param Ship_State       Ship_State
* @param Ship_Country_Code     Ship_Country_Code
* @param Bill_Account_Number Bill_Account_Number
* @param Bill_Address      Bill_Address
* @param Bill_PC    Bill_PC
* @param Bill_City       Bill_City
* @param Bill_State       Bill_State
* @param Bill_Country_Code     Bill_Country_Code
* @param Order_Number          Order_Number
* @param ordered_date          ordered_date
* @param Order_Amount     Order_Amount
* @param Currency              Currency
* @param ORDER_TYPE_CODE       ORDER_TYPE_CODE
* @param Cr_Card_Pay_Status    Cr_Card_Pay_Status
* @param Cr_Card_Acceptance    Cr_Card_Acceptance
* @param Email_Address         Email_Address
* @param Tax_Perc     Tax_Perc
* @param Tax_Amount     Tax_Amount
* @param Discount_codes    Discount_codes
* @param Po_Number             Po_Number
* @param OrderLines Orderlines
* @param x_output XML Return Message
* @rep:displayname Create webshop/TRP order
* @rep:scope public
* @rep:lifecycle active
*/
 Procedure W_OrderWS (
  Account_Name          IN VARCHAR2
, Tax_Reference_Number  IN VARCHAR2
, Ship_Account_Number   IN VARCHAR2
, Ship_Address      IN VARCHAR2
, Ship_PC    IN VARCHAR2
, Ship_City       IN VARCHAR2
, Ship_State   IN VARCHAR2
, Ship_Country_Code     IN VARCHAR2
, Bill_Account_Number IN VARCHAR2
, Bill_Address      IN VARCHAR2
, Bill_PC    IN VARCHAR2
, Bill_City       IN VARCHAR2
, Bill_State   IN VARCHAR2
, Bill_Country_Code     IN VARCHAR2
, Order_Number          IN VARCHAR2
, ordered_date          IN DATE
, Order_Amount      IN NUMBER
, Currency              IN VARCHAR2
, ORDER_TYPE_CODE       IN VARCHAR2
, Cr_Card_Pay_Status    IN VARCHAR2
, Cr_Card_Acceptance    IN VARCHAR2
, Email_Address         IN VARCHAR2
, Tax_Perc       IN NUMBER
, Tax_Amount   IN NUMBER
, Discount_codes  IN VARCHAR2
, PO_Number             IN VARCHAR2
,  OrderLines    IN CLOB
 ,x_output              OUT VARCHAR2);
 

Step 3: Create your database logic

Now your procedure can do anything with the given input data ofcourse. In our case it returns the status using something like this

 TYPE
   T_XML_Outputs IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;

 
 G_XML_OUTPUT   T_XML_Outputs;
 G_XML_OUTPUT_ID NUMBER := 1;
 G_MAX_LENGTH   NUMBER := 32000;
 G_XML_ERROR    VARCHAR2(1000);
 G_XML_LENGTH   NUMBER := 0;
 G_ERROR_PNT    VARCHAR2(240);

...

 XXXORAWS.Output_To_Webservice (XXORACLEWS.xml_header
         || Chr(10) || '  <status>' || l_status || '</status>'
         || Chr(10) || '  <errorcode>' || l_error_code || '</errorcode>'
         || Chr(10) || '  <msg>' || l_msg || '</msg>'
         || Chr(10) || '  <debug_msg>' || l_debug_msg || '</debug_msg>'
                                    || Chr(10) || '  <DEBUG_MESSAGES>' || g_debug_messages || '</DEBUG_MESSAGES>'    
         || Chr(10) || '  <operation>W_OrderWS</operation>'
         || Chr(10) || '   </oracle>');

And the output to webservice function is something like this .. it depends on what you want to output if this is a large text or not. Most write webservices output only the status, but we also creat read services that display a listof something and that output can be quite long.

PROCEDURE Output_To_Webservice(
    p_add_xml VARCHAR2 )
IS
BEGIN
  -- We split the output in pieces. In most cases we only need one piece.
  XXXORAWS.G_XML_LENGTH                                                                            := XXXORAWS.G_XML_LENGTH                       + NVL (LENGTH (p_add_xml),0);
  IF NVL (LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID)),0) + NVL (LENGTH (p_add_xml),0) < XXXORAWS.G_MAX_Length
    --    IF XXXORAWS.G_XML_LENGTH < XXXORAWS.G_MAX_LENGTH * XXXORAWS.G_XML_OUTPUT_ID
    THEN
    NULL;
  ELSE
    XXXORAWS.G_XML_OUTPUT_ID                := XXXORAWS.G_XML_OUTPUT_ID + 1;
    XXXORAWS.G_XML_OUTPUT (G_XML_OUTPUT_ID) := NULL; -- Init
  END IF;
  XXXORAWS.G_ERROR_PNT                             := 'Add XML ' || XXXORAWS.G_XML_OUTPUT_ID || ' size piece is ' || LENGTH (p_add_xml) || ' and length current ' || LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID));
  XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID) := XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID) || p_add_xml;
  XXXORAWS.G_ERROR_PNT                             := 'Added XML ' || XXXORAWS.G_XML_OUTPUT_ID || ' size piece is ' || LENGTH (p_add_xml) || ' and length current ' || LENGTH (XXXORAWS.G_XML_OUTPUT (XXXORAWS.G_XML_OUTPUT_ID));
 END Output_To_Webservice;
An example is a service that displays all countries in Oracle


------------------------------------------------------------
  -- This procedure lists all countries in Oracle.
  ------------------------------------------------------------
  Procedure R_CountryWS
  IS
    CURSOR C_Countries
    IS
    SELECT t.territory_code
    , Initcap (t.nls_territory) nls_territory
    , T.Territory_Short_Name
    , T.Description
    FROM   FND_TERRITORIES_VL T
    WHERE T.Obsolete_Flag = 'N'
    ORDER BY t.territory_short_name
    ;
  BEGIN
    l_status := Fnd_Api.g_ret_sts_success;
    l_msg    := null;
       XXXORAWS.Output_To_Webservice (xml_header || Chr(10) ||
                '  <status>' || l_status || '</status>' || Chr(10) ||
                '  <errorcode>' || l_error_code || '</errorcode>' || Chr(10) ||
                '  <msg>' || l_msg || '</msg>' || Chr(10) ||
                '  <operation>R_CountryWS</operation>' || Chr(10) ||
                '  <countries>' || Chr(10));

    FOR C IN C_Countries
      LOOP
   XXXORAWS.Output_To_Webservice ('    <country>' ||  Chr(10) ||
          '      <code>' || C.Territory_Code || '</code>' || Chr(10) ||
          '      <nlscode>' || C.NLS_Territory || '</nlscode>' || Chr(10) ||
          '      <name>' || C.Territory_Short_Name || '</name>' || Chr(10) ||
          '      <description>' || C.Description || '</description>' || Chr(10) ||
          '    </country>' || Chr(10));
      END LOOP; -- Countries

   XXXORAWS.Output_To_Webservice ('  </countries>' || Chr(10) || xml_footer);
    exception
        when others then
           l_status := fnd_api.g_ret_sts_error;
           l_msg    := sqlerrm;
           l_error_code := G_COUNTRY_ERROR;
           XXXORAWS.Error_To_Webservice (xml_header || Chr(10) ||
                '  <status>' || l_status || '</status>' || Chr(10) ||
                '  <errorcode>' || l_error_code || '</errorcode>' || Chr(10) ||
                '  <msg>' || l_msg || '</msg>' || Chr(10) ||
                '  <operation>R_CountryWS</operation>' || Chr(10) ||
              xml_footer);
  END; -- R_CountryWS

And this would be exposed in our package header like this. Note that you don't have to add the annotation in the package body.

  ------------------------------------------------------------
  -- This procedure lists all countries in Oracle.
  ------------------------------------------------------------
/*#
* This procedure returns a list of countries
* @param x_output XML Return Message
* @rep:displayname Oracle Country List
* @rep:scope public
* @rep:lifecycle active
*/  
  Procedure R_CountryWS (x_output OUT CLOB)
    ;

Step 4 : Install the service

I usually create a driver script where I prompt the user to enter the version of the webservice. If left empty we do not re-install the service.

rm -f L*.log
if [ "${WEB_VERSION}" ]
then
echo "Loading webservice package" >> $LOGFILE
echo "Version is "$WEB_VERSION >> $LOGFILE
#cp XXXORAWS.pls $INSTALLATION_DIR/install/sql
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=SYSADMIN XXX:install/sql:XXXORAWS.pls:$WEB_VERSION=/$TWO_TASK/apps/apps_st/appl/xxx/12.0.0/install/sql/XXXORAWS.pls
$FND_TOP/bin/FNDLOAD $APPS_USER/$APPS_PASS 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XXXORAWS_pls.ildt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
for i in `ls -dF L*.log`
  do
   cat $i >> $LOGFILE
  done
else
  echo "Not reinstalling webservices." >> $LOGFILE
fi

Step 5 : Generate and deploy the webservice

Login as Integrated SOA Gateway responsibility as SYSADMIN and navigate to the Integration Repository. Perform a search using the button on the right. Choose Advanced Options and look for interface type Custom. This should show our webservice.


Open the service and click [Generate WSDL]. If you do not see this button you are not logged in as SYSADMIN.

Now choose Deploy button on the left.If you have added a new function it should be granted to the user you use for accessing the webservices (usually one specific EBS user with no responsibilities). before you can use it. Click on the function you want to change and grant the user.

  

 

 Step 6: Test webservice

Use  View WSDL to see the wsdl and remove ?wsdl. Now you get to the test page.
Here you can test the service. It's good practice to create one generic service that actually does nothing except for return the name of the database so you always know you are pointing at the right environment and that the service is working.

You have to pass the username/password and responsibility key. You can see these up in EBS with a responsibility without any menu items so the user can't login.

Choose invoke at the bottom and see the output in P_OUTPUT to see the result.

Fetch last active contract for a given item and customer account

Fetch last active contract for a given item and customer account

This query retrieves the contract related to a specific item for an account.

     SELECT  H.Id
       ,       H.Contract_Number
       ,       H.Contract_Number_Modifier
       ,       L.Start_date -- H.Start_Date
       ,       L.End_Date -- H.End_Date
       ,       DECODE (H.Sts_Code,'ACTIVE',L.Sts_Code,H.Sts_Code) Sts_Code
       ,       HT.Short_Description
       FROM    OKC_K_LINES_B L
       ,       OKC_K_ITEMS   I
       ,       OKC_K_HEADERS_B H
       ,       OKC_K_HEADERS_TL HT
       ,       HZ_CUST_SITE_USES_ALL BAUI
       ,       HZ_CUST_SITE_USES_ALL SAUI
       ,       hz_cust_acct_sites_all bsi
       ,       hz_cust_acct_sites_all ssi
       WHERE   1=1
       -- Site use of the contract line relate to cust account
       AND     L.Bill_To_Site_Use_id = BAUI.Site_Use_Id
       AND     L.Ship_To_Site_Use_Id = SAUI.Site_Use_Id
       AND     BAUI.Cust_Acct_SIte_Id = bsi.cust_acct_site_id
       AND     SAUI.Cust_Acct_Site_Id = ssi.cust_acct_site_id
       AND     bsi.cust_account_id = cp_bill_account_id
       AND     ssi.cust_account_id = cp_ship_account_id
       --
       AND     L.ID = I.Cle_Id
       AND     H.Id = HT.Id
       AND     HT.Language = USERENV ('LANG')
       AND     I.Object1_Id1 IN cp_inventory_item_id
       AND     I.Object1_Id2 = cp_organization_Id
       AND     L.Dnz_Chr_Id = h.id
       -- Fetch active contracts first, then the one with the latest end date/start date.
       -- If you find multiple lines with the same end date, take the lowest
       -- start date.
       ORDER BY DECODE (h.sts_code,'ACTIVE',0,1), NVL (L.End_Date,L.Start_Date) DESC, L.Start_Date,H.Contract_Number desc
       ;

Hierarchical query on employees and their supervisor in Oracle HR

Hierarchical query on employees and their supervisor in Oracle HR

We all know the famous emp/dept hierarchical queries to show employees belonging to a department and employees with their manager. I recently came across a question on how to query all employees and their managers (called supervisors in HR) in a hierarchical manor so I created this query as combination of what I've read in https://technology.amis.nl/2005/08/16/hierarchical-query-with-nodes-from-different-tables-dept-and-emp-nodes-in-one-tree/) and Oracle database schema.

Note that the employee - supervisor link can be circular. In that case you will not find those employees in the tree, since there is no top node where the manager (supervisor_id) is empty.

with emps as
(
-- The managers
select s.person_id empno
,      s.first_name || ' ' || s.last_name ename
,      sa.supervisor_id mgr
from   per_people_x s
, per_assignments_x sa
where    s.person_id = sa.person_id
and exists (select 1 from per_assignments_x a where a.supervisor_id = s.person_id)
union
-- The employees
select p.person_id empno -- all EMP-nodes
,      p.first_name || ' ' || p.last_name ename
,      a.supervisor_id mgr
from   per_people_x p, per_assignments_x a
where   p.person_id = a.person_id
)
select lpad(' ', level*3)||ename ename
from   emps
connect
by     prior empno = mgr
start
with  
nvl (mgr,-1) = -1
/

JDeveloper issue deploying ADF to local integrated weblogic server: Unable to reserve .lok file for Integrated Weblogic Server

JDeveloper issue deploying ADF to local integrated weblogic server: Unable to reserve .lok file for Integrated Weblogic Server

When you close JDeveloper unexpectedly without shutting down the integrated weblogic server properly, you make get the following error as soon as you try to deploy your project to the integrated WS again

 "Unable to reserve the .lok file for Integrated WebLogic Server (IntegratedWebLogicServer)"

Open the task manager on your console en kill the java.exe process that is running. Now retry.

If above does not help then go to C:\Users\..\AppData\Roaming\JDeveloper\system..\DefaultDomain . and delete the edit.lok file.

Compound trigger for mutating table

Compound trigger for mutating table

For some this is sliced cookie ;-), but I hadn't encountered mutating table for a long time and was still set on creating a package and three triggers (before update/insert, before update/insert for each row and after update/insert) to collect in a pl/sql table the changes to perform some kind of action ... so something like this (yeah the code is not very useful, it was just for testing purposes)

Old style

create table xxx_compound_test (nr number, action_date date);
insert into xxx_compound_test values (1,null);
insert into xxx_compound_test values (2,null);
insert into xxx_compound_test values (3,null);
create or replace package xxx_compound_test_pkg
as
  type
    r_mail_test is record
    (
      nr        number
    , recipient varchar2(240)
    , message   varchar2(240)
    );
   
  type
    t_mail_test is table of r_mail_test index by binary_integer;
   
  g_mail_test t_mail_test;
  g_nr_of_values number;
  procedure init;
  procedure add_value (added_value r_mail_test);
end;
/
create or replace package body xxx_compound_test_pkg
as

  procedure init
  is
  begin
    g_mail_test.delete;
    g_nr_of_values := 0;
  end;
 
  procedure add_value (added_value r_mail_test)
  is
  begin
    g_nr_of_values := g_nr_of_values + 1;
    dbms_output.put_line ('Adding value ' || g_nr_of_values || ' for nr ' || added_value.nr);
    g_mail_test (g_nr_of_values).nr := added_value.nr;
    g_mail_test (g_nr_of_values).recipient := added_value.recipient;
    g_mail_test (g_nr_of_values).message := added_value.message;
  end;
end;
/

create or replace trigger xxx_compound_test_bu before update on xxx_compound_test
begin
  xxx_compound_test_pkg.init;
end;
/
create or replace trigger xxx_compound_test_bru before update on xxx_compound_test
for each row
declare
  l_added_value xxx_compound_test_pkg.r_mail_test;
 
begin
  l_added_value.recipient := 'Test ' || :new.nr;
  l_added_value.message   := 'Test message';
  l_added_value.nr        := :new.nr;
 
  xxx_compound_test_pkg.add_value (l_added_value);
end;
/
create or replace trigger xxx_compound_test_au after update on xxx_compound_test
begin
  -- do your mail thingy
  dbms_output.put_line ('Nr of records: ' || xxx_compound_test_pkg.g_nr_of_values);
 
  <<mails>>
  for i in xxx_compound_test_pkg.g_mail_test.first .. xxx_compound_test_pkg.g_mail_test.last
  loop
    dbms_output.put_line ('Sending mail to ' || xxx_compound_test_pkg.g_mail_test (i).recipient);  
  end loop mails;
 
end;
/

New style

But a compound trigger is much simpler since you only need one trigger and no package to maintain ..

create or replace trigger xxx_mail_test_comp for update of action_date on xxx_compound_test
compound trigger
  type
    r_mail_test is record
    (
      nr        number
    , recipient varchar2(240)
    , message   varchar2(240)
    );
   
  type
    t_mail_test is table of r_mail_test index by binary_integer;
   
  g_mail_test t_mail_test;
  g_nr_of_values number;
  l_added_value r_mail_test;
  procedure init
  is
  begin
    g_mail_test.delete;
    g_nr_of_values := 0;
  end init;
 
  procedure add_value (added_value r_mail_test)
  is
  begin
    g_nr_of_values := g_nr_of_values + 1;
    dbms_output.put_line ('Adding value ' || g_nr_of_values || ' for nr ' || added_value.nr);
    g_mail_test (g_nr_of_values).nr := added_value.nr;
    g_mail_test (g_nr_of_values).recipient := added_value.recipient;
    g_mail_test (g_nr_of_values).message := added_value.message;
  end add_value;
  
  BEFORE STATEMENT IS
  BEGIN
    init;
  END BEFORE STATEMENT;
 
  AFTER EACH ROW IS
  BEGIN
    l_added_value.recipient := 'Test ' || :new.nr;
    l_added_value.message   := 'Test message';
    l_added_value.nr        := :new.nr;
 
    add_value (l_added_value);
  END AFTER EACH ROW;
 
  AFTER STATEMENT IS
  BEGIN
    dbms_output.put_line ('Nr of records: ' || g_nr_of_values);
   
    <<mails>>
    for i in g_mail_test.first .. g_mail_test.last
    loop
      dbms_output.put_line ('Sending mail to ' || g_mail_test (i).recipient);  
    end loop mails;
  END AFTER STATEMENT;
END;
/

Compile all invalid custom objects

Compile all invalid custom objects

I use this script on every installation of custom software to make sure all objects are compiled at the end.

SET SERVEROUTPUT ON SIZE 1000000
SET TERM ON
SELECT Status, Object_Type, Object_Name
FROM ALL_OBJECTS
WHERE Status = 'INVALID'
AND Object_Type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW')
;
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 3) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY','VIEW')
                  AND    status != 'VALID'
                  AND    (object_name like 'XX%'
    )
                  ORDER BY 4)
  LOOP
    dbms_output.put_line ('Compiling ' || cur_rec.object_type || ' ' || cur_rec.object_name);
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSIF cur_rec.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
   ELSIF cur_rec.object_Type = 'VIEW' THEN
   EXECUTE IMMEDIATE 'ALTER VIEW "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('ERROR ON ' || cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);       
    END;
  END LOOP;
END;
/
SELECT Status, Object_Type, Object_Name
FROM ALL_OBJECTS
WHERE Status = 'INVALID'
AND Object_Type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW')
;

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;
 /