woensdag 27 april 2016

Integrate Procurement Cloud Contracts and eBS Procure to Pay (on premise)

In the previous blog we investigated an integration between Oracle eBS on premise and Oracle Sourcing in the cloud.


The integration scenario is shown above. We performed a direct integration without any middleware starting in eBS on premise with a requisition for a sourcing event. This triggered a web service to create the sourcing event directly in cloud. After rewarding the sourcing event another process is triggered to fetch the awarded event and create the purchase order in eBS (either through the open interface, the API (see for example http://pamkoertshuis.blogspot.nl/2015/11/open-interface-requisition-to-purchase.html)  or a custom exposed web service through Integrated SOA Gateway for example).

Of course the same scenario can be handled using middleware (like Oracle SOA or any other servicebus). The following scenario starts in the cloud environment with a purchasing contract. All contracts are managed centrally in the cloud environment and we want to enforce the contract agreements on our subsystems where procure to pay is handled.
So in generic terms, we are looking for the following integration.

Supplier qualification is in this scenario also done in the cloud, so our cloud environment is the master for our supplier base and the suppliers need to be interfaced to the subsystems (which could be only one, but also multiple).

Our main concern now is how to get the data from our cloud environment to our subsystems. Of course there is always the method of calling web services, but how do we know which contracts and suppliers have been created?
The procurement cloud solution however comes with a very nice solution for this. The cloud solution already contains a standard SOA process that is triggered by the creation/updating of both contracts and suppliers.
For contracts this is called the Purchasing Integration SOA, for suppliers the Supplier Sync Service. Both are BPEL processes that accept events from procurement cloud and which call a set of web services to handle the requests.

The ECM contract fulfillment SOA implementation has attempted to modularize integration with the target procurement application based on the purchasing flow that is derived from the contract type of the given contract:
  • If a contract is created from a contract type with intent as 'Buy' and contract type class as 'Enterprise Contract', then purchase orders can be initiated from the fulfillment lines of the contract.
  • If a contract is created from a contract type with intent as 'Buy', contract type class as 'Agreement', and lines are allowed on the contract type, then blanket purchase agreements can be initiated from the fulfillment lines of the contract.
  • If a contract is created from a contract type with intent as 'Buy', contract type class as 'Agreement', and lines are not allowed on the contract type, then contract purchase agreement can be initiated from the fulfillment line of the contract.

For the internal handling of contracts to purchase orders it calls the same SOA process, but it can also call third party web services (so called intermediary web services). So in a full diagram, this is what we can achieve.
Note that I also described our previous integration scenario in the diagram, where instead of a direct integration from eBS to the cloud we could use an intermediary service in our integration layer.

In the current scenario we do our strategic procurement (contracts management, supplier qualification) in the cloud and we use the Purchasing Integration SOA and Supplier Sync Service to send our data to an integration layer. This integration layer contains the intermediary web services required for the integration, which in their place handle the specific requests necessary for the subsystems. In eBS for example it could fill the open interface tables, call an API or call a custom web service exposed through Integrated SOA Gateway. Of course integration with eBS is something we are already familiair with!

The intermediary web service needs to be of a specific format (the interface/WSDL is fixed, see below), which is described in
Note that in theory we could create this web service also directly in eBS (using Integrated SOA Gateway for example) and do a direct integration.

Now all you have to is register the intermediary web service in Manage Contract and Procurement System Integration.

As you can see here the system supports two methods of integration: Direct and Indirect. With direct integration you call a web service that immediately returns the result and the contract information is updated with the information returned by the service.
Using indirect integration it assumes you use an integration pattern with staging tables (like the open interface of eBS) and you run the ECM Contract Fulfillment Batch program to return the result to the cloud.
You can specify multiple endpoints here, but in our scenario it makes sense to create one intermediary service on the integration layer which handles the transformation to the different subsystems.

For suppliers the setup is more or less similar, except that you can only specify one endpoint (in this case you are likely to use middleware to transfer the supplier data to multiple systems).

So all we need to do in our integration scenario is write the logic for the intermediary service. The triggering of the business events and the invocation of our web services is handled by setup in the cloud!

Interface Intermediary Webservice

<?xml version="1.0" encoding="UTF-8"?>
<wsdl:definitions name="PurchasingInterfaceService" targetNamespace=http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/ xmlns:ns1="http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/contracts PurchaseDocument/types/"
xmlns:plnk="http://schemas.xmlsoap.org/ws/2003/05/partner-link/" xmlns:client=http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/ xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">

<name>PurchasingInterfaceService</name> <docCategories> <category>None</category> </docCategories>
<plnk:partnerLinkType name="FusionPurchasingInterfaceProcess"> <plnk:role name="PurchasingInterfaceProcessProvider">

<plnk:portType name="client:PurchasingInterfaceService"/> </plnk:role>
<plnk:role name="PurchasingInterfaceProcessRequester"> <plnk:portType name="client:PurchasingInterfaceServiceResponse"/> </plnk:role>

<schema xmlns="http://www.w3.org/2001/XMLSchema">

<import namespace="http://xmlns.oracle.com/apps/contracts/deliverableTracking/purchasingInterfaceService/contract sPurchaseDocument/types/"
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<import namespace="http://schemas.xmlsoap.org/ws/2003/03/addressing" schemaLocation="oramds:/apps/org/xmlsoap/schemas/ws/2003/03/addressing/ws-addressing.xsd"/> </schema>
<wsdl:message name="GetPurchaseDocumentRequest">
<wsdl:part name="payload" element="ns1:GetPurchaseDocumentRequest"/>
<wsdl:message name="GetPurchaseDocumentResponse">
<wsdl:part name="payload" element="ns1:GetPurchaseDocumentResponse"/>
<wsdl:message name="CreatePurchaseDocumentRequest">
<wsdl:part name="payload" element="ns1:CreatePurchaseDocumentRequest"/>
<wsdl:message name="GetInterfaceDocDetailsRequest">

<wsdl:part name="payload" element="ns1:GetInterfaceDocDetailsMessage"/> </wsdl:message>
<wsdl:message name="CreatePurchaseDocumentResponse">

<wsdl:part name="payload" element="ns1:CreatePurchaseDocumentResponse"/> </wsdl:message>
<wsdl:message name="GetInterfaceDocDetailsResponse">

<wsdl:part name="payload" element="ns1:GetInterfaceDocDetailsMessage"/> </wsdl:message>
<wsdl:message name="TestRequest">

<wsdl:part name="payload" element="ns1:TestRequest"/> </wsdl:message>
<wsdl:message name="TestResponse">

<wsdl:part name="result" element="ns1:TestResponse"/> </wsdl:message>
<wsdl:portType name="PurchasingInterfaceService">

<wsdl:operation name="getPurchasingActivityDetails">
<wsdl:input message="client:GetPurchaseDocumentRequest"/>

<wsdl:output message="client:GetPurchaseDocumentResponse"/> </wsdl:operation>
<wsdl:operation name="createPurchaseDocument">

<wsdl:input message="client:CreatePurchaseDocumentRequest"/> </wsdl:operation>
<wsdl:operation name="getInterfacedPurchasingDocumentDetails">

<wsdl:input message="client:GetInterfaceDocDetailsRequest"/> </wsdl:operation>
<wsdl:operation name="testIntegration">

<wsdl:input message="client:TestRequest"/>
<wsdl:output message="client:TestResponse"/> </wsdl:operation>
<wsdl:portType name="PurchasingInterfaceServiceResponse">

<wsdl:operation name="createPurchaseDocumentResponse"> <wsdl:input message="client:CreatePurchaseDocumentResponse"/>
<wsdl:operation name="getInterfacedPurchasingDocumentDetailsResponse">
<wsdl:input message="client:GetInterfaceDocDetailsResponse"/> page11image1216 page11image1640 page11image1800
</wsdl:operation> </wsdl:portType> </wsdl:definitions> 

maandag 25 april 2016

Fetch profile options

To find profile options set on different levels you can use the following query. You can filter on level (application, responsibility, user, etc) if necessary.

SELECT AP.Application_Short_Name
,      DECODE (OV.Level_Id,10001,'Site', 10002,'Applicatie',10003, 'Autorisatie', 10004,'Gebruiker') level_Id
,      DECODE (OV.Level_Id,10002,
(SELECT A.Application_Short_Name FROM FND_APPLICATION A WHERE A.Application_Id = OV.Level_Value)
(SELECT R.Responsibility_Name FROM FND_RESPONSIBILITY_TL R, FND_RESPONSIBILITY RR WHERE R.Responsibility_Id = OV.Level_Value   AND OV.Level_Value_Application_Id = R.Application_Id AND R.Language = USERENV ('LANG')
AND R.Responsibility_Id = RR.Responsibility_Id AND R.Application_Id = RR.Application_Id  )

    (SELECT U.User_Name FROM FND_USER U WHERE U.User_Id = OV.Level_Value)
                              , OV.Level_Value) Level_Value
    ,      OV.Level_Value
    ,      OV.Profile_Option_Value
    ,    FND_PROFILE_OPTIONS          OP
    ,    FND_APPLICATION              AP
    WHERE OV.Profile_Option_id          = OP.Profile_Option_id
    AND   OP.Profile_Option_Name        = OP_T.PROFILE_OPTION_NAME
    AND   OV.Application_Id             = AP.Application_Id

    dinsdag 12 april 2016

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

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

    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.

    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.


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



    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.


    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. 


    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

        -- 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
        SELECT '<neg:NegotiationSections>' || chr(13) ||
                '<neg:SectionName>Section 1 description</neg:SectionName>' ||chr(13) Requirement_Section
            , 'Section1' section_name
        FROM   DUAL
        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)
        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'
        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);

        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'
        -- 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
          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/">
                <neg:NegotiationTitle>' || l_title || '</neg:NegotiationTitle>
                <neg:CloseDate>' || to_char (l_approved_date+15,'YYYY-MM-DD') || 'T00:00:00.000000000</neg:CloseDate>
                <neg:CurrencyCode>'|| l_currency || '</neg:CurrencyCode>
                <neg:Doctype>' || l_doctype || '</neg:Doctype>
                <neg:FreightTerms>Buyer pays freight</neg:FreightTerms>
                <neg:NegotiationLayoutName>' || l_layout || '</neg:NegotiationLayoutName>
                <neg:PaymentTerms>Net 30</neg:PaymentTerms>
                <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:Category>' || l_category || '</neg:Category>
                   <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);
            FOR S IN C_Requirement_Sections
                  l_soap_request := l_soap_request ||

                FOR R IN C_Requirements (cp_section => S.Section_Name)
                  l_soap_request := l_soap_request ||
                  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 ||            

        dbms_output.put_line ('Test only: ' || p_test_only);
        IF p_test_only = 'N'
          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 ...');
          l_chunkData := NULL;
          l_chunkData := SUBSTR(l_soap_request, l_chunkStart, l_chunkLength);
          IF p_test_only = 'N'
            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'
        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');
            -- 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
                              , length(l_buffer)
                              , l_buffer);
          end loop;
          EXCEPTION WHEN UTL_HTTP.end_of_body
            dbms_output.put_line ('Exception reading response ... ');
        dbms_output.put_line ('End response');
        l_error_pnt := 'end response';
        l_error_action := null;


        -- 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;
        END IF;
        dbms_output.put_line('End of procedure ...');

          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;


    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.

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

      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
              CLOSE C_Requisition;
              l_exists := 0;
             CLOSE C_Requisition;
             l_exists := 1;
          END IF;
      IF l_exists = 1
      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
           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

    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.


         g_attribute            VARCHAR2(240)        := '[Attribute]';
      CURSOR C_Last_Req
    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);
      MO_GLOBAL.Init ('PO');
      OPEN  C_Last_Req;
      FETCH C_Last_Req INTO l_req;
      IF C_Last_Req%NOTFOUND
        DBMS_OUTPUT.Put_Line ('Cannot find requisition for testing ...');
         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);
          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);