Wednesday, 15 February 2017

Oracle APEX and DocuSign part 3

APEXOfficePrint and DocuSign part 3

Sign your AOP documents

Oracle APEX and DocuSign

Introductory two posts can be found here: part 1 and part 2.

Today's post is the final in a series. So far we have signed up with DocuSign, configured it, learned about APIs available, webhooks and run the basic example which triggered a document sent to our inbox

The end goal of today is to store a sign document in a DB table.

Again I will assume that AOP document is already generated and stored in DB table. 

We completed part 2 with a real case demo showing the whole process, now let's see all of the components.  

Key thing what we will be using here is a webhook - it can be manual or can be pre-configured by DocuSign engine. 

Step 1. Create an envelope -> send it to an email address but including a webhook this time:
 webhook_url varchar2(100) := 'http://your_apex_webservice/app/docusign/';
    event_notification := '{"url": "' || webhook_url || '",
        "loggingEnabled": "true",
        "requireAcknowledgment": "true",
        "useSoapInterface": "false",
        "includeCertificateWithSoap": "false",
        "signMessageWithX509Cert": "false",
        "includeDocuments": "true",
        "includeEnvelopeVoidReason": "true",
        "includeTimeZone": "true",
        "includeSenderAccountAsCustomField": "true",
        "includeDocumentFields": "true",
        "includeCertificateOfCompletion": "true",
        "envelopeEvents": [ {"envelopeEventStatusCode": "completed"}],
        "recipientEvents": [ {"recipientEventStatusCode": "Completed"}]

    l_CLOB := apex_web_service.blob2clobbase64(l_BLOB);
    l_body := '{
                "status": "sent",
                "emailSubject": "Request a signature via email example",
                "documents": [{
                    "documentId": "2",
                    "name": "contract.pdf",
                    "documentBase64": "';

    l_body := l_body || l_CLOB;
    l_body := replace(l_body, chr(13) || chr(10), null);
    l_body := l_body || '"
                        "recipients": {
                            "signers": [{
                                "name": "your name",
                                "email": "your email address",
                                "recipientId": "1",
                                "tabs": {
                                    "signHereTabs": [{
                                        "xPosition": "25",
                                        "yPosition": "50",
                                        "documentId": "2",
                                        "pageNumber": "1"
                        } ,
                        "eventNotification": ' || event_notification || ',
                        "status": "sent"
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_web_service.g_request_headers(2).name := 'X-DocuSign-Authentication';
apex_web_service.g_request_headers(2).value := 

     '{  "Username":"your docusign username",
         "Password":"your pwd",
         "IntegratorKey":"your integKey" }';
l_result := apex_web_service.make_rest_request(

   p_http_method   => 'POST',
   p_body          => l_body);
dbms_output.put_line('l_result =' || substr(l_result,1,500) );
Notice parts highlighted in yellow. These are differences from basic example we did in part2 post. Basically what is happening as part of envelope we are adding additional properties to declare new webhook to be used.  

If you decide to go with automatic, pretty much it comes to the same thing as example above except you have to configure your Connect entity to point to your APEX web service for all required events. Plus there is no need for webhook_url variable.

Step 2. Create APEX RESTful service as

as code for POST method use something similar to:
   --GET XML returned by webhook
    l_clob :=  wwv_flow_utilities.blob_to_clob(:body);
  l_envelope_id := substr(l_clob, instr(l_clob, '<EnvelopeID>')+12,  ((instr(l_clob, '</EnvelopeID>')) - (instr(l_clob, '<EnvelopeID>')+12)) );
   --GET what documents are in envelope
    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';
    apex_web_service.g_request_headers(2).name := 'X-DocuSign-Authentication';
   apex_web_service.g_request_headers(2).value := '{  

        "Username":"your username",
        "Password":"your password",
        "IntegratorKey":"your integKey" }';
    l_result := apex_web_service.make_rest_request(
        p_http_method   => 'GET');
    -- Display the whole SOAP document returned.
    --dbms_output.put_line('l_result =' || substr(l_result,1,500) );
    --parse result to get DOCUMENT_ID
    apex_json.parse(l_json, l_result);
 l_document_id:=apex_json.get_varchar2(p_path => 'envelopeDocuments[1].documentId', p_values => l_json);
    --dbms_output.put_line('l_result =' || l_document_id ); 
   l_result_b := apex_web_service.make_rest_request_b(
 p_url=>''||l_envelope_id ||'/documents/'|| l_document_id ||'?show_changes=false',
   p_http_method   => 'GET');
    insert into dummy (b, id) values (l_result_b, 1);  
   --logger.log('This is working test');

Big note: as part of DocuSign AOI envelope documentation it is mentioned that webhook should return a full document as a part of XML tag <PDFBytes> but I never got this working. 

Better said all XML documents tags returned were incomplete. I raised it on DocuSign forums but no answers till this day. Not sure though why. 
That is why in example above we are using a work around. Get the envelope ID from a webhook, for that envelope call a special API to get all documents contained in an envelope then get the content of particular document. 
Really there is not much to it than that. Of course you would not use this as your production code and you could rewrite some parts of it but basic idea is there. 
Things to be are aware of: 
  • please make sure you are using a correct URL to your APEX webservice.
  • envelope can include more than one document so you would have to loop through JSON returned before getting individual contents
  • workflow scenario can be much more complex including several signatures etc....

This simple demo it in action:here

Hope this will explain few questions you might have.


1 comment:

  1. Great post from Lino, I followed all the steps and it works. Nice implementation to have in APEX....good job