Calling BPEL/ESB Webservice from PL/SQL

DECLARE
--
  v_bookmark          NUMBER:=0;
  soap_request        CLOB;
  soap_respond        CLOB;
  resp                XMLTYPE;
  web_service_failure EXCEPTION;
  v_request_url       VARCHAR2(1000) := 'http://12.12.12.12:7777/orabpel/operations/WarehousePreadvice/1.0';
  -- operation name in webservice
  v_operation_name    VARCHAR2(100):='createWhPreAdvice';
  -- namespace name
  v_xmlns_url         VARCHAR2(1000) := 'xmlns:ns1="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadviceRequest%22';
  v_error_code        VARCHAR2(1000);
  v_error_description VARCHAR2(1000);
  v_error_status      VARCHAR2(1000);
  soap_request_len    NUMBER:=0;
  http_req            UTL_HTTP.REQ;
  http_resp           UTL_HTTP.RESP;
  resp                XMLTYPE;
  i                   INTEGER;
  p_errorcode         VARCHAR2(1000);
  p_errordescription  VARCHAR2(1000);
  p_errorstatus       VARCHAR2(1000);
  v_index             NUMBER:=1;
--
--
BEGIN
--
  DBMS_OUTPUT.PUT_LINE('Program Begins '||TO_CHAR(SYSDATE,'dd-mon-yyyy hh24:mi:ss'));
--
    
      soap_request:='<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsdl="http://WarehousePreadvice/wsdl" xmlns:ns1="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadvice" xmlns:ns2="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadviceRequest">
                      <soapenv:Header/>
                       <soapenv:Body>
                         <ns:createWhPreAdvice xmlns:ns="http://WarehousePreadvice/wsdl">
                          <payload xmlns:ns1="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadvice">
                           <ns1:WhPreAdviceRequest xmlns:ns2="http://xmlns.hdnl.com/EnterpriseObject/Core/WarehousePreadviceRequest">
                            <ns2:PreadviceHeader>
                                 <ns2:ClientId>'||idx.clientId||'</ns2:ClientId>
                                 <ns2:OwnerId>'||idx.owner||'</ns2:OwnerId>
                                 <ns2:PreAdviceId>'||idx.PreAdviceId||'</ns2:PreAdviceId>
                                 <ns2:ClientCustomerReference>'||idx.ClientCustomerReference||'</ns2:ClientCustomerReference>
                                 <ns2:SiteId/>
                                 <ns2:SupplierId>'||idx.SupplierId||'</ns2:SupplierId>
                                 <ns2:CollectionReqd>'||idx.CollectionFlag||'</ns2:CollectionReqd>
                                 <ns2:ReturnFlag>'||idx.ReturnFlag||'</ns2:ReturnFlag>
                                 <ns2:Name>'||idx.Customer_Name||'</ns2:Name>
                                 <ns2:Address1>'||idx.AddressLine1||'</ns2:Address1>
                                 <ns2:Address2>'||idx.AddressLine2||'</ns2:Address2>
                                 <ns2:Town>'||idx.Town||'</ns2:Town>
                                 <ns2:County>'||idx.County||'</ns2:County>
                                 <ns2:Postcode>'||idx.PostCode||'</ns2:Postcode>
                                 <ns2:Country>'||idx.CountryCode||'</ns2:Country>
                                 <ns2:ContactPhone>'||idx.ContactPhone||'</ns2:ContactPhone>
                                 <ns2:Status/>
                                 <ns2:ActionType/>
                                 <ns2:Consignment>'||idx.Consignment||'</ns2:Consignment>
                                 <ns2:PreadviceLines>
                                     <ns2:PreadviceLine>
                                         <ns2:ClientId>'||idx.Line_clientId||'</ns2:ClientId>
                                         <ns2:PreAdviceId>'||idx.Line_PreAdviceId||'</ns2:PreAdviceId>
                                         <ns2:QtyDue>'||idx.QtyDue||'</ns2:QtyDue>
                                         <ns2:ConfigId/>
                                         <ns2:LineId>'||idx.LineID||'</ns2:LineId>
                                         <ns2:TagId>'||idx.TagId||'</ns2:TagId>
                                         <ns2:SkuId>'||idx.SkuId||'</ns2:SkuId>
                                         <ns2:UserDefDate1/>
                                         <ns2:UserDefType1>'||idx.UserDefType1||'</ns2:UserDefType1>
                                         <ns2:UserDefType2/>
                                         <ns2:UserDefType3>'||idx.UserDefType3||'</ns2:UserDefType3>
                                         <ns2:UserDefType4/>
                                         <ns2:UserDefType5/>
                                         <ns2:UserDefType6/>
                                         <ns2:ActionType/>
                                         <ns2:ConditionId/>
                                     </ns2:PreadviceLine>
                                 </ns2:PreadviceLines>
                            </ns2:PreadviceHeader>
                           </ns1:WhPreAdviceRequest>
                          </payload>
                         </ns:createWhPreAdvice>
                       </soapenv:Body>
                      </soapenv:Envelope>';
    --
    -- Set up transfer protocols
    --
      DBMS_OUTPUT.PUT_LINE('Soap Request Populated... ');
      DBMS_OUTPUT.PUT_LINE('Setting Transfer Protocols... ');
    --
      UTL_HTTP.SET_TRANSFER_TIMEOUT(500);
      http_req := UTL_HTTP.BEGIN_REQUEST(v_request_url, 'POST', 'HTTP/1.1');
      UTL_HTTP.SET_HEADER(http_req, 'Content-Type', 'text/xml');     -- since we are dealing with plain text in XML documents
      UTL_HTTP.SET_HEADER(http_req, 'Content-Length', LENGTH(soap_request));
      UTL_HTTP.SET_HEADER(http_req, 'SOAPAction', v_operation_name); -- required to specify this is a SOAP communication
    -- Check length of package - if > 32767 need to chop up
      soap_request_len:=LENGTH(soap_request);
    --
      DBMS_OUTPUT.PUT_LINE('Setting Request Length is '||soap_request_len);
    --
      IF LENGTH(soap_request) <='32767'
      THEN
         UTL_HTTP.WRITE_TEXT(http_req, soap_request);
      ELSE
        --Need to loop around clob
          WHILE v_index <= soap_request_len
          LOOP
              UTL_HTTP.WRITE_TEXT(http_req, SUBSTR(soap_request, v_index, 32000));
              v_index := v_index + 32000;
          END LOOP;
        --
      END IF;
    --
      DBMS_OUTPUT.PUT_LINE('Request= '||soap_request);
      http_resp:= UTL_HTTP.GET_RESPONSE(http_req);
      DBMS_OUTPUT.PUT_LINE('HTTP Response status code  : ' || http_resp.status_code);
      DBMS_OUTPUT.PUT_LINE('HTTP Response reason phrase: ' || http_resp.reason_phrase);
      DBMS_OUTPUT.PUT_LINE('HTTP Response http version : ' || http_resp.http_version);
      UTL_HTTP.READ_LINE(http_resp, soap_respond);
      UTL_HTTP.END_RESPONSE(http_resp);
      DBMS_OUTPUT.PUT_LINE('Response  '||soap_respond);
      DBMS_OUTPUT.PUT_LINE('Response Received');
      DBMS_OUTPUT.PUT_LINE('--------------------------');
      DBMS_OUTPUT.PUT_LINE ( 'Status code: ' || http_resp.status_code );
    --
--
EXCEPTION
--
  WHEN web_service_failure THEN
  DBMS_OUTPUT.PUT_LINE('Web Service Failure ');
--
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Exception Block No DATA Found ');
--
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Exception Block others '||SQLERRM);
--
END;
/