Procurement integration eBusiness Suite R12.1.3 on premise with Fusion Procurement Cloud R10

During one of our demos we investigated the following integration scenario. A requisition for a sourcing request was created in eBS and should automatically be converted to an RFQ in the cloud environment.

In order to do direct integration from the eBS environment on premise to the cloud sourcing environment we need to do some security setup on the eBS database server (a wallet and an ACL).

Find webservice

First we need to investigate which webservice in cloud we can use. So we check out the new Oracle Enterprise Repository

http://www.oracle.com/webfolder/technetwork/docs/HTML/oer-redirect.html

And navigate to Procurement, SOAP Webservices and in our case R10. Choose SOAP Web Services for Oracle Procurement Cloud and navigate to Business Object Services. You will find the following services

·        Purchase Agreement

·        Purchase Order

·        Purchase Order Service Version 2

·        Supplier Negotiation Version 2

·        Update Preparer and Requester

We need the Supplier Negotiation Version 2. If you choose that link

http://docs.oracle.com/cloud/farel10/procurementcs_gs/OESWP/Supplier_Negotiation_Version_2_NegotiationManageService_svc_4.htm#oracle.apps.prc.pon.negotiations.negotiationsServiceV2.NegotiationManageService_OpSect

You can review the operations that are available and the operation we need is initializeNegotation.

You can investigate the service to see which elements we need to pass. Currently you cannot enter a negotiation template so you have to add all requirements (otherwise the system would copy these of the template). For our demo we needed

• Header
• Lines
• Requirements

We don’t add a fixed list of suppliers on forehand, but you could also add those and for example diffent currencies as well.

So now we know which service to invoke, we can setup our security.

Setup wallet

The wallet must be defined on the database server. An example for the setup is shown below (in this case it’s a wallet on my local PC).

orapki wallet create -wallet C:\Oracle\wallet -pwd password -auto_login
orapki wallet add -wallet C:\Oracle\wallet -trusted_cert -cert "[cert_path]\root.cer" -pwd password

orapki wallet add -wallet C:\Oracle\wallet -trusted_cert -cert "[cert_path]\intermed.cer" -pwd password
orapki wallet add -wallet C:\Oracle\wallet -trusted_cert -cert "[cert_path]\[instance].oracle.com.cer" -pwd password

Import certificates into the wallet

After the wallet has been setup we need to import the certificates from the webservice we want to invoke into the wallet. First you need the WSDL to your environment that you want to connect to.

https://{server}-prc.{domain}/prcPonNegotiations/NegotiationManageServiceV2?WSDL

Open this URL in Internet Explorer and choose Internet Options, Content, Certificates. Here you can download the certificates. Use the Base64 encoded X.509 version. Now you can upload these in the wallet using the wallet manager.

Trusted Certificates:

Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject:        CN=VeriSign Class 3 Public Primary Certification Authority - G5,OU=(c) 2006 VeriSign\, Inc. - For authorized use only,OU=VeriSign Trust Network,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net

Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject:        CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net

Subject:        CN=Symantec Class 3 Secure Server CA - G4,OU=Symantec Trust Network,O=Symantec Corporation,C=US

Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject:        CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US

Define Access Control List

After the wallet you create an ACL in the eBS database under SYSTEM user. Here [user] must be replaced with the user that is granted access (in our case it would be APPS). Also make sure you change the host to point to the correct endpoint.

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_fusion_file.xml',
    description  => 'ACL UTL_HTTP to Fusion Cloud',
    principal    => [user],
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);


  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('acl_fusion_file.xml' ,[user], TRUE, 'resolve');

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'acl_fusion_file.xml',
    host        => '*.prc.[datacenter].oraclecloud.com',
    lower_port  => NULL,
    upper_port  => NULL);

END;

/

Test if your setup is correct!

You can use SQL to test if the setup of your wallet and ACL is correct. Call this procedure passing the WSDL, the file path to your wallet and the password of the wallet. If necessary you can add the proxy also as parameter.

select utl_http.request('https://{host}-prc.{domain}/prcPonNegotiations/NegotiationManageServiceV2?WSDL', '','file:/db/rdbms12/ssl','welcome1') from dual;

If you get this

ORA-29273: HTTP request failed

ORA-29024: Certificate validation failure

ORA-06512: at "SYS.UTL_HTTP", line 1491

ORA-06512: at line 1

29273. 00000 -  "HTTP request failed"

*Cause:    The UTL_HTTP package failed to execute the HTTP request.

*Action:   Use get_detailed_sqlerrm to check the detailed error message.

           Fix the error and retry the HTTP request.

or

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1577

ORA-28750: unknown error

ORA-06512: at line 1

29273. 00000 -  "HTTP request failed"

*Cause:    The UTL_HTTP package failed to execute the HTTP request.

*Action:   Use get_detailed_sqlerrm to check the detailed error message.

           Fix the error and retry the HTTP request

It usually means you did not import the correct certificates. Make sure this line is not in the wallet

Subject:        CN=*.{domain},O=Oracle Corporation,L=Redwood Shores,ST=California,C=US

Technical overview

Now our solution consists of the following components

•  initializeNegotiationWS: Procedure to call the webservice from the database given the correct parameters.

• Create_RFQ : Procedure to fetch the parameters for the webservice based on the requisition header.

• Trigger on requisition header to submit procedure Create_RFQ. 

initializeNegotationWS

This procedure is the main part of our solution. The procedure receives parameters filled by Create_RFQ. We hardcode the sections and requirements in this call using the cursor C_Requirement_Sections and C_Requirements. Currently there does not seem to be a webservice to get the requirements from the template.

Otherwise you should first call the webservice to fetch these values and pass them to this webservice. In the example we have two sections. Each section has one requirement and both weigh for 50%. One is a numeric internal, the other a text manual.

I've build it so you can call it using Create_RFQ either for testing only (it displays the payload, but does NOT make the actual call) or for real.

In the requirements section we added some formatting to make sure you can pass enters (use ![CDATA[...<br />]]) and the first line is made bold by using <b> ... </b> within the requirements text.

CREATE OR REPLACE PROCEDURE initializeNegotiationWS
(
     p_title              IN VARCHAR2
    ,p_currency             IN VARCHAR2
    ,p_doctype             IN VARCHAR2
    ,p_outcome            IN VARCHAR2
    ,p_business_unit      IN VARCHAR2
    ,p_style             IN VARCHAR2
    ,p_response_currency IN VARCHAR2
    ,p_approved_date     IN VARCHAR2
    ,p_layout             IN VARCHAR2
    ,p_line_num             IN VARCHAR2
    ,p_item_description  IN VARCHAR2
    ,p_UOM_Code             IN VARCHAR2
    ,p_line_type_id         IN NUMBER
    ,p_category             IN VARCHAR2
    ,p_unit_price        IN NUMBER
    ,p_quantity          IN NUMBER
    ,p_test_only         IN VARCHAR2 DEFAULT 'N'   
    ,x_result            OUT NUMBER
    ,x_msg               OUT VARCHAR2
    )
AS

    -------------------------------------------------------------------------------------------------------------
    -- This is the actual webservice call. We pass all parameters from the Create_RFQ procedure, call the
    -- service and read the response. We return the rfq number as x_result.
    -------------------------------------------------------------------------------------------------------------

    -------------------------------------------------------------------------------------------------------------
    -- Will be parameters from the trigger on the requisition ..
    -------------------------------------------------------------------------------------------------------------

       l_title             VARCHAR2(240) := NVL (p_title,'MW - Laptop Replacement');   
    l_currency            VARCHAR2(240) := NVL (p_currency,'USD');   
    l_doctype            VARCHAR2(240) := NVL (p_doctype,'RFQ');
    l_outcome           VARCHAR2(240) := NVL (p_outcome,'Purchase Order');
    l_business_unit     VARCHAR2(240) := NVL (p_business_unit,'US1 Business Unit');
    l_style                VARCHAR2(240) := NVL (p_style,'Standard Negotiation');
    l_response_currency VARCHAR2(240) := NVL (p_response_currency,'GBP');
    l_approved_date     DATE          := NVL (p_approved_date,sysdate);
    l_layout            VARCHAR2(240) := NVL (p_layout,'Negotiation Layout');
    l_category          VARCHAR2(240) := NVL (p_category,'Computer Supplies');
   
    l_nr_of_reqs        NUMBER;
   
    -------------------------------------------------------------------------------------------------------------
    -- In theory we fetch these from the template first and add them here. It would be better if we could
    -- pass the template in the service ..
    -------------------------------------------------------------------------------------------------------------
    CURSOR C_Requirement_Sections
    IS
    SELECT '<neg:NegotiationSections>' || chr(13) ||
            '<neg:SectionName>Section 1 description</neg:SectionName>' ||chr(13) Requirement_Section
        , 'Section1' section_name
    FROM   DUAL
    UNION
    SELECT '<neg:NegotiationSections>' ||  chr(13) ||
            '<neg:SectionName>Section 2 description</neg:SectionName>' || chr(13) Requirement_Section
            , 'Section 2' section_name
    FROM   DUAL   
    ;
   
   
    CURSOR C_Requirements (cp_section VARCHAR2)
    IS
    SELECT ' <neg:NegotiationRequirements> ' ||chr(13) ||
            ' <neg:MaximumScore>4</neg:MaximumScore> ' ||chr(13) ||
            ' <neg:RequirementText><![CDATA[<b>Some text</b><br /> ' ||chr(13) ||
            ' Some more text: <br />]]></neg:RequirementText> ' ||chr(13) ||
            ' <neg:Datatype>Number</neg:Datatype> ' ||chr(13) ||
            ' <neg:KnockoutScore>1</neg:KnockoutScore> '||chr(13) ||
            ' <neg:ResponseTypeCode>INTERNAL</neg:ResponseTypeCode> ' ||chr(13) ||
            ' <neg:ScoringMethod>Manual</neg:ScoringMethod> ' ||chr(13) ||
            ' <neg:NumberValue>2</neg:NumberValue> ' ||chr(13) ||
            ' <neg:Weight>0.5</neg:Weight> '|| chr(13) Requirement
    FROM   DUAL
    WHERE  cp_section = 'Section1'
        UNION
    SELECT ' <neg:NegotiationRequirements> ' ||chr(13) ||
            ' <neg:RequirementText><![CDATA[<b>Some text.</b> <br />'||chr(13) ||
'Some more text. ]]></neg:RequirementText>' ||chr(13) ||
            ' <neg:Datatype>Text</neg:Datatype> ' ||chr(13) ||
            ' <neg:KnockoutScore>1</neg:KnockoutScore> '||chr(13) ||
            ' <neg:ResponseTypeCode>REQUIRED</neg:ResponseTypeCode> ' ||chr(13) ||
            ' <neg:DisplayTargetFlag>1</neg:DisplayTargetFlag> ' ||chr(13) ||
            ' <neg:ScoringMethod>Automatic</neg:ScoringMethod> ' ||chr(13) ||
            ' <neg:TextValue>Complete</neg:TextValue> ' ||chr(13) ||
            ' <neg:Weight>0.5</neg:Weight> '|| chr(13) ||
            ' <neg:NegotiationRequirementScores> ' || chr(13) ||
            '   <neg:TextValue>Incomplete</neg:TextValue>' || chr(13) ||
            '   <neg:Score>1</neg:Score>' || chr(13) ||
            ' </neg:NegotiationRequirementScores> ' || chr(13) ||
            ' <neg:NegotiationRequirementScores> ' || chr(13) ||
            '   <neg:TextValue>Complete</neg:TextValue>' || chr(13) ||
            '   <neg:Score>2</neg:Score>' || chr(13) ||           
            ' </neg:NegotiationRequirementScores> ' || chr(13) Requirement
    FROM   DUAL
    WHERE  cp_section = 'Section2'
    ;

   
   

    -------------------------------------------------------------------------------------------------------------
    -- Sample from our internal environment, this is environment specific stuff
    -------------------------------------------------------------------------------------------------------------
    g_wallet_path    VARCHAR2(240) := '/.../wallet';
    g_wallet_pwd    VARCHAR2(240) := 'welcome1';
   
    -------------------------------------------------------------------------------------------------------------
    -- Webservice specific stuff
    -------------------------------------------------------------------------------------------------------------
    g_operation     VARCHAR2(240) := 'initializeNegotiation';
    g_namespace     VARCHAR2(240) := 'http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2';
    g_wsdl          VARCHAR2(240) := 'https://origin-ucf1-fap1781-prc.oracledemos.com:443/prcPonNegotiations/NegotiationManageServiceV2';
    g_fusion_un        VARCHAR2(240) := '[Fusion Username]';
    g_fusion_pwd    VARCHAR2(240) := '[Fusion Password]';
    g_proxy         VARCHAR2(240) := null; -- 'http://dmz-proxy.[host]:80';
    g_soap_action   VARCHAR2(240) := 'http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/initializeNegotiation';
   
   
   
    -------------------------------------------------------------------------------------------------------------
    -- General variables
    -------------------------------------------------------------------------------------------------------------
    g_soap_env      VARCHAR2(240) := 'http://schemas.xmlsoap.org/soap/envelope/';   
    l_soap_request  varchar2(30000);
   
    l_result         VARCHAR2(32767) := null;
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_counter        PLS_INTEGER;
    l_length         PLS_INTEGER;
   
    l_resp_xml       XMLType;
    l_result_nr         varchar2(240);
   
    l_ns_map         varchar2(2000) ;
    x_clob           CLOB;
    l_buffer         VARCHAR2(32767);    
    l_chunkStart      NUMBER := 1;
    l_chunkData      VARCHAR2(32000);
    l_chunkLength      NUMBER := 32000; 
   
    l_error_code     VARCHAR2(240);
    l_error_pnt         VARCHAR2(240);
    l_error_action   VARCHAR2(240);
   
    l_uom_code         VARCHAR2(240);
   
  BEGIN

    l_error_pnt := 'Init';
    l_error_action := 'See error message';
    -------------------------------------------------------------------------------------------------------------
    -- Initialize settings for the HTTP call. This is webservice specific stuff!
    -------------------------------------------------------------------------------------------------------------
   
    l_ns_map := l_ns_map ||' xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" ';
    l_ns_map := l_ns_map ||' xmlns:wsa="http://www.w3.org/2005/08/addressing" ';   
    l_ns_map := l_ns_map ||' xmlns:ns0="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/types/"';
    l_ns_map := l_ns_map ||' xmlns="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/types/"';
   

    IF p_test_only = 'N'
    THEN
   
    -------------------------------------------------------------------------------------------------------------
    -- Sets the Oracle wallet used for request, required for HTTPS
    -------------------------------------------------------------------------------------------------------------
    l_error_pnt := 'setting wallet';
    dbms_output.put_line ('Set wallet: ' || g_wallet_path);
    UTL_HTTP.set_wallet('file:' || g_wallet_path, g_wallet_pwd);
   
    -- If necessary set proxy.
    g_proxy := null;
    IF g_proxy IS NOT NULL
    THEN
      dbms_output.put_line ('Set proxy: ' || g_proxy);
      UTL_HTTP.set_proxy (g_proxy);
    END IF;
   
  
    l_error_pnt := 'creating request based on wallet';
    l_error_action := 'Wallet Manager set permissions on the files, so they can only be read by the user who created them. Either modify the permissions or copy the files to another location. And then in your "set_wallet" reference the new location. ';
    -- Creates new HTTP request. You will get an error if it cannot reaad the wallet.
    l_http_request := UTL_HTTP.begin_request(g_wsdl, 'POST','HTTP/1.1');  
   
    l_error_pnt := 'setting authentication';
    -- Configure the authentication details on the request
    UTL_HTTP.SET_AUTHENTICATION(l_http_request, g_fusion_un, g_fusion_pwd);
  
    -- Configure the request content type to be xml and set the content length
    l_error_pnt := 'setting header';
    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset="UTF-8"');

    -------------------------------------------------------------------------------------------------------------
    -- Default parameters and build payload
    -------------------------------------------------------------------------------------------------------------
    l_title             := NVL (l_title,'MW - Laptop Replacement');   
    l_currency            := NVL (l_currency,'USD');   
    l_doctype            := NVL (l_doctype,'RFQ');
    l_outcome           := NVL (l_outcome,'Purchase Order');
    l_business_unit     := NVL (l_business_unit,'US1 Business Unit');
    l_style                := NVL (l_style,'Standard Negotiation');
    l_response_currency := NVL (l_response_currency,'GBP');
    l_layout            := NVL (l_layout,'Negotiation Layout');
    l_category          := NVL (p_category,'Computer Supplies');
   
    l_uom_code            := 'zzu'; -- Is each
   
    END IF; -- If we are only testing, we just want to show the payload!
   
    l_error_pnt := 'creating payload';
   
   
   
    dbms_output.put_line (l_doctype || ' ' || l_title || '  for ' || l_category);


l_soap_request :=
'<soapenv:Envelope xmlns:neg="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/" xmlns:neg1="http://xmlns.oracle.com/apps/flex/prc/pon/commonPon/negLine/" xmlns:neg2="http://xmlns.oracle.com/apps/flex/prc/pon/commonPon/negHeader/" xmlns:neg3="http://xmlns.oracle.com/apps/flex/prc/pon/commonPon/negHeaderExt/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/prc/pon/negotiations/negotiationsServiceV2/types/">
   <soapenv:Body>
      <typ:initializeNegotiation>
         <typ:negotiationEntry>
            <neg:AllowOtherResponseCurrencyFlag>0</neg:AllowOtherResponseCurrencyFlag>
            <neg:NegotiationTitle>' || l_title || '</neg:NegotiationTitle>
            <neg:ResponseVisibility>Blind</neg:ResponseVisibility>
            <neg:CloseDate>' || to_char (l_approved_date+15,'YYYY-MM-DD') || 'T00:00:00.000000000</neg:CloseDate>
            <neg:OpenImmediatelyFlag>1</neg:OpenImmediatelyFlag>
            <neg:CurrencyCode>'|| l_currency || '</neg:CurrencyCode>
            <neg:DisplayBestPriceBlindFlag>1</neg:DisplayBestPriceBlindFlag>
            <neg:Doctype>' || l_doctype || '</neg:Doctype>
            <neg:FOB>Origin</neg:FOB>
            <neg:FreightTerms>Buyer pays freight</neg:FreightTerms>
            <neg:DisplayRequirementScoresFlag>1</neg:DisplayRequirementScoresFlag>
            <neg:EnableRequirementWeightsFlag>1</neg:EnableRequirementWeightsFlag>
            <neg:DefaultMaximumRequirementScore>5</neg:DefaultMaximumRequirementScore>
            <neg:NegotiationLayoutName>' || l_layout || '</neg:NegotiationLayoutName>
            <neg:PaymentTerms>Net 30</neg:PaymentTerms>
            <neg:PersonId>300000047340498</neg:PersonId>
            <neg:Outcome>' || l_outcome || '</neg:Outcome>
            <neg:ProcurementBusinessUnit>'|| l_business_unit || '</neg:ProcurementBusinessUnit>
            <neg:ResponseLayoutName>Response Layout</neg:ResponseLayoutName>
            <neg:Style>' || l_style || '</neg:Style>
            <neg:NegotiationLines>
               <neg:Category>' || l_category || '</neg:Category>
               <neg:GroupTypeCode>LINE</neg:GroupTypeCode>
               <neg:ItemDescription>' || nvl (p_item_description,'Test description') || '</neg:ItemDescription>
               <neg:LineNumber>' || nvl (p_line_num,1) || '</neg:LineNumber>
               <neg:LineTypeId>' || nvl(p_line_type_id,1) || '</neg:LineTypeId>
               <neg:Quantity unitCode="' || NVL (l_uom_code,'zzu') || '">'|| p_quantity || '</neg:Quantity>
               <neg:UOMCode>'|| NVL (l_uom_code,'zzu')  || '</neg:UOMCode>
               <neg:CurrentPrice>' || nvl (p_unit_price,100) || '</neg:CurrentPrice>
               <neg:TargetPrice>' || to_char (nvl (p_unit_price,100) * 0.7) || '</neg:TargetPrice>
               <neg:ResponseStartPrice>' || nvl (p_unit_price,100) || '</neg:ResponseStartPrice>
            </neg:NegotiationLines>' || Chr(13);
       
   
        <<Sections>>
        FOR S IN C_Requirement_Sections
        LOOP
       
              l_soap_request := l_soap_request ||
                S.Requirement_Section;

       
            <<Requirements>>
            FOR R IN C_Requirements (cp_section => S.Section_Name)
            LOOP
           
              l_soap_request := l_soap_request ||
                R.Requirement;           
              l_soap_request := l_soap_request  || '  </neg:NegotiationRequirements> ' || chr(13);
            END LOOP Requirements;
           
            l_soap_request := l_soap_request || '  </neg:NegotiationSections> ' || chr(13);
           
        END LOOP Sections;
       

       
l_soap_request := l_soap_request ||            
         '</typ:negotiationEntry>
      </typ:initializeNegotiation>
   </soapenv:Body>
</soapenv:Envelope>';


    dbms_output.put_line ('Test only: ' || p_test_only);
    IF p_test_only = 'N'
    THEN
      dbms_output.put_line ('Call service and get output.');
    -------------------------------------------------------------------------------------------------------------
    -- In our case the soap request is actually not that long, but your call may have been a blob. In that case
    -- you need to chunk it and pass it to the request.
    -------------------------------------------------------------------------------------------------------------
   
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_soap_request));
    --dbms_lob.getlength(convert(l_soap_request, 'UTF-8'));
    UTL_HTTP.set_header(l_http_request, 'Transfer-Encoding', 'chunked');
  
    -- Set the SOAP action to be invoked; while the call works without this the value is expected to be set based on standards
    utl_http.set_header(l_http_request, 'SOAPAction', g_soap_action);
   
    END IF;
   
    -------------------------------------------------------------------------------------------------------------
    -- Write the xml payload to the request.
    -------------------------------------------------------------------------------------------------------------
    l_error_pnt := 'writing payload';
    dbms_output.put_line ('Writing payload ...');
    LOOP
      l_chunkData := NULL;
      l_chunkData := SUBSTR(l_soap_request, l_chunkStart, l_chunkLength);
     
      IF p_test_only = 'N'
      THEN
        UTL_HTTP.write_text(l_http_request, l_chunkData);
      END IF;
     
      -- For us to test later
      dbms_output.put_line (l_chunkdata);
     
      IF (LENGTH(l_chunkData) < l_chunkLength)
        THEN EXIT;
      END IF;
      l_chunkStart := l_chunkStart + l_chunkLength;
    END LOOP;
   
    IF p_test_only = 'N'
    THEN
   
    l_error_pnt := 'getting response';
    l_error_action := null;
    --  Get the response and process it
    l_http_response := UTL_HTTP.get_response(l_http_request);

    -- Create a CLOB to hold web service response
    -- Again, in our case we only get one value, but we like to keep this procedure a little generic.
    dbms_lob.createtemporary(x_clob, FALSE );
    dbms_lob.open(x_clob, dbms_lob.lob_readwrite);
   
    l_error_pnt := 'open lob for getting response';
    l_error_action := null;
   
    dbms_output.put_line ('Reading response');
 
    begin
 
      loop
        -- Copy the web service response body in a buffer string variable l_buffer
        utl_http.read_text(l_http_response, l_buffer);
       
        dbms_output.put_line (l_buffer);
 
        -- Append data from l_buffer to CLOB variable
        dbms_lob.writeappend(x_clob
                          , length(l_buffer)
                          , l_buffer);
      end loop;
     
      EXCEPTION WHEN UTL_HTTP.end_of_body
      THEN
        NULL;
        dbms_output.put_line ('Exception reading response ... ');
    END;
   
    dbms_output.put_line ('End response');
   
    l_error_pnt := 'end response';
    l_error_action := null;

    UTL_HTTP.end_response(l_http_response);
   

    -------------------------------------------------------------------------------------------------------------
    -- Now we have our clob in XML and read the values we need from the XML.
    -------------------------------------------------------------------------------------------------------------
    l_error_pnt := 'reading response';
    l_error_action := null;
    dbms_output.put_line ('Status code response is ' || l_http_response.status_code);
   
    if l_http_response.status_code IN (200) THEN
      l_resp_xml := XMLType.createXML(x_clob);
 
      SELECT  extractValue(l_resp_xml, '/env:Envelope/env:Body/ns0:initializeNegotiationResponse/result', l_ns_map) 
      INTO  l_result_nr
      FROM dual;
 
      dbms_output.put_line('Result = '||l_result_nr);
      x_result := l_result_nr;
     
     
   
    end if;
   
    dbms_lob.freetemporary(x_clob);
   
    END IF;
   
    dbms_output.put_line('End of procedure ...');

   
   
    EXCEPTION 
      WHEN Others THEN 
        l_error_code := SQLERRM;
        DBMS_OUTPUT.Put_Line ('Error: ' || l_error_pnt || ': ' || l_error_code);
        DBMS_OUTPUT.Put_line ('Action: ' || l_error_action);
        x_msg         := l_error_code;
        x_result     := null;

   
END initializeNegotiationWS;
/

Create_RFQ

This procedure fetches all necessary data from the requisition line and passes this to the webservice. Note that we execute the RFQ for each line as long as the line is RFQ required. We pass the data from the requisition header, because we want to call this from a trigger on the requisition header, so we cannot query on the requisition headers in this procedure.

Note: In our query we also look at a specific attribute on the template (now called [Attribute]). You can remove that here of course! If you run it like this it uses the outerjoins to find the requisition regardless of the specific attribute.

CREATE OR REPLACE PROCEDURE Create_RFQ
(
  p_requisition_header_id            IN NUMBER
, p_description                        IN VARCHAR2
, p_approved_date                    IN DATE
, p_test_only                        IN VARCHAR2 DEFAULT 'N'
, x_return                            OUT VARCHAR2
)
IS
 -- You can test this externally using ..
 -- Test: begin Create_RFQ (531792,'Test', sysdate,'Y'); end;

   --
   -- This procedure fetches information it needs to call the webservice like header information
   -- passed by the trigger (we pass this from the trigger because we cannot do a query on
   -- the requisition header if the trigger is firing - mutating table). We fetch line information
   -- and actually fire for each line. For the demo each Req will only have one line, but you could
   -- make an additional restriction here.
   --

    g_attribute            VARCHAR2(240)        := '[Attribute]';
   
    CURSOR C_Requisition
    (
       cp_requisition_header_id        po_requisition_headers.requisition_header_id%TYPE
    )
    IS
    SELECT     pl.requisition_line_id
    ,         pt.template_name
    ,         pta.attribute_name
    ,         pti.attribute_value
    ,       p_description description -- ph.description
    ,       p_approved_date approved_date --ph.approved_date
    ,       pl.line_num
    ,       pl.item_description
    ,       pl.currency_code currency
    ,       pl.line_type_id
    ,       pl.unit_meas_lookup_code
    ,       pl.unit_price
    ,       pl.quantity
    ,       'Computer Supplies'         Category_Segment
    ,       'RFQ'                        DocType
    ,       'US1 Business Unit'            Business_Unit
    ,       'Standard Negotiation'        RFQ_Style
    ,       'Negotiation Layout'        Layout
    ,       'Purchase Order'            Outcome
    FROM     por_templates_v             pt
    ,         por_template_attributes_v     pta
    ,         por_template_info             pti
    ,       po_requisition_lines        pl
    WHERE     pt.template_code (+)                            = pta.template_code
    AND     pta.attribute_code (+)                            = pti.attribute_code
    --AND     pl.requisition_header_id                      = ph.requisition_header_id
    AND     pti.requisition_line_id (+)                        = pl.requisition_line_id
    AND     pl.requisition_header_id                         = cp_requisition_header_id
    AND     NVL (pta.attribute_name,g_attribute)              = g_attribute
    AND     NVL (pl.RFQ_Required_Flag,'N')                     = 'Y'
    ;
   
    l_requisition     C_Requisition%ROWTYPE;

    x_result        NUMBER;
    x_msg            VARCHAR2(240);
    l_exists        NUMBER := 0;
   
BEGIN

  MO_GLOBAL.Init ('PO');

  OPEN  C_Requisition (cp_requisition_header_id => p_requisition_header_id);
  FETCH C_Requisition INTO l_requisition;
      IF C_Requisition%NOTFOUND
      THEN
          CLOSE C_Requisition;
          l_exists := 0;
      ELSE
         CLOSE C_Requisition;
         l_exists := 1;
      END IF;
 
 
  IF l_exists = 1
  THEN
 
  dbms_output.put_line ('Creating ' || l_requisition.DocType || ' for ' || l_requisition.Description || ' with style ' || l_requisition.RFQ_Style);

   initializeNegotiationWS (
     p_title              => l_requisition.Description
    ,p_currency             => l_requisition.Currency
    ,p_doctype             => l_requisition.DocType
    ,p_outcome            => l_requisition.Outcome
    ,p_business_unit      => l_requisition.Business_Unit
    ,p_style             => l_requisition.RFQ_Style
    ,p_response_currency => l_requisition.Currency
    ,p_approved_date     => l_requisition.Approved_Date
    ,p_layout             => l_requisition.Layout
    ,p_line_num             => l_requisition.Line_Num
    ,p_item_description  => l_requisition.Item_Description
    ,p_UOM_Code             => l_requisition.Unit_Meas_Lookup_Code
    ,p_line_type_id         => l_requisition.Line_Type_Id
    ,p_category             => l_requisition.Category_Segment
    ,p_unit_price        => l_requisition.unit_price
    ,p_quantity          => l_requisition.quantity
    ,p_test_only         => p_test_only
    ,x_result            => x_result
    ,x_msg               => x_msg
   );
  
   dbms_output.put_line ('Result: ' || to_char (x_result) || ' - ' || x_msg);
  
  
   --------------------------------------------------------------------------------------------------------
   -- Update requisition header with sourcing id
   --------------------------------------------------------------------------------------------------------
   IF x_result IS NOT NULL
   THEN
      
       x_return := 'Reference ' || l_requisition.DocType || ' ' || to_char (x_result);
      
       -- Do update here to copy back x_return to PO_REQUISITION_LINES_ALL for example
      
  
   END IF;
  
   END IF; -- Exists
  
END;
/

Finally you can call Create_RFQ from a trigger on PO_REQUISITION_HEADERS_ALL.

Create_RFQ (p_requisition_header_id => :New.Requisition_Header_id,p_test_only => 'N',p_description => :New.Description, p_approved_date => :New.Approved_Date,x_return => x_return);
      

Now you can use the return value to store on the requisition header.

You can restrict it to fire only on approval

WHEN (New.Approved_Date IS NOT NULL AND Old.Approved_Date IS NULL)

And you could either check here if there are any lines with RFQ_Required_Flag set to Yes or leave that to Create_RFQ.

Test from database

I created a test script to test the call in the Database rather than having to create a Purchase requisition in eBusiness Suite as well.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
     g_attribute            VARCHAR2(240)        := '[Attribute]';
   
  CURSOR C_Last_Req
  IS
SELECT     pl.requisition_line_id
    ,   pl.requisition_header_id
    ,         pt.template_name
    ,         pta.attribute_name
    ,         pti.attribute_value
    ,       ph.description
    ,       ph.approved_date
    FROM     por_templates_v             pt
    ,         por_template_attributes_v     pta
    ,         por_template_info             pti
    ,       po_requisition_lines        pl
    ,       po_requisition_headers      ph
    WHERE     pt.template_code (+)                            = pta.template_code
    AND     pta.attribute_code (+)                            = pti.attribute_code
    AND     pti.requisition_line_id (+)                        = pl.requisition_line_id
    AND     NVL (pta.attribute_name,g_attribute)              = g_attribute
    AND     pl.requisition_header_id                        = ph.requisition_header_id
    --AND   ph.requisition_header_id                        = 12345
    ORDER BY ph.requisition_header_id DESC
  ;
 
  l_req C_Last_Req%ROWTYPE;
  x_return VARCHAR2(240);
 
BEGIN
  MO_GLOBAL.Init ('PO');
 
  OPEN  C_Last_Req;
  FETCH C_Last_Req INTO l_req;
  IF C_Last_Req%NOTFOUND
  THEN
    DBMS_OUTPUT.Put_Line ('Cannot find requisition for testing ...');
  ELSE
     DBMS_OUTPUT.Put_Line ('Requisition: ' || l_req.Requisition_Header_Id);
  END IF;
  CLOSE C_Last_Req;
 
  DBMS_OUTPUT.Put_Line ('Requisition description: ' || l_req.Description);
 
  Create_RFQ
    (
      p_requisition_header_id            => l_req.Requisition_Header_Id
    , p_description                        => NVL (l_req.Description,'Test')
    , p_approved_date                    => l_req.Approved_Date
    , p_test_only                        => 'N'
    , x_return                            => x_return
    );
   
  DBMS_OUTPUT.Put_Line ('Resultaat: ' || x_return);
END;
/