Oracle EBS - SFDC Integration using Oracle REST Data Services (ORDS) and Oracle Apex

Design 

Prerequisites

It is prerequisite to have below software installed on Oracles EBS Application server(compatible with EBS version)

            >  Oracle Apex  4.2.1

            > JAVA Version should be greater than 1.6.0.20 for ORDS installation

            >  Oracle REST Data Services 2.0.8.163.10.40

            >  PL/SQL libraries to extract JSON (pljson)

Implementation 

Download/Install/Configure ORDS and Apex on Oracle EBS instance.

(Please refer my previous posts for this step)

Login to Oracle Application Express

Go to RESTful webservice

     Navigation : SQL Workshop > RESTful Services

Create RESTful Services Module

Create Module

Create Template

Create Handler which will call PL/SQL APIs post hitting web service at SFDC.

Use below Source code in Source for below Handler which interfaces with EBS APIs.

DECLARE

j_obj json;

BEGIN

j_obj:= rest_person_demo1(:p1);

json_ac.object_htp(j_obj);

EXCEPTION

WHEN OTHERS THEN

htp.p('Error : ' || SQLERRM);

END;

Create parameters to capture JSON passed from SFDC.

Select source type as a URI to resource handler parameter.

Create API which will extract JSON received in web service from SFDC and will call hz_party_v2pub.create_person API in oracle EBS.

CREATE OR REPLACE FUNCTION APPS.rest_person_demo1 (sfdc_json VARCHAR2)

   RETURN json

AS

   v_acc_number        VARCHAR (1000);

   v_acc_name          VARCHAR (1000);

   v_status            VARCHAR2 (100);

   l_response          NUMBER;

   v_app_ac_json       json                           := json ();

   v_mainjson          json                           := json ();

   p_person_rec        hz_party_v2pub.person_rec_type;

   x_cust_account_id   NUMBER;

   x_account_number    VARCHAR2 (2000);

   x_party_id          NUMBER;

   x_party_number      VARCHAR2 (2000);

   x_profile_id        NUMBER;

   x_return_status     VARCHAR2 (2000);

   x_msg_count         NUMBER;

   x_msg_data          VARCHAR2 (2000);

   vmsgdummy           VARCHAR2 (5000);

   lerrmsg             VARCHAR2 (5000);

   lretmsg             VARCHAR2 (1000);

   finaljson           json                           := json ();

   oracle_json         json;

   oracleinfo          json_value                     := json_value ();

   oracle_list         json_list                      := json_list ();

BEGIN

   v_app_ac_json := json (sfdc_json);

   v_mainjson := json_ext.get_json (v_app_ac_json, 'Person');

   DBMS_OUTPUT.put_line ('Json-' || sfdc_json);

   mo_global.init ('AR');

   mo_global.set_org_context (204, NULL, 'AR');

   mo_global.set_policy_context ('S', 204);

   fnd_msg_pub.delete_msg (NULL);

   fnd_msg_pub.initialize;

   p_person_rec.person_first_name := v_mainjson.json_data (1).str;

   p_person_rec.person_last_name := v_mainjson.json_data (2).str;

   p_person_rec.attribute4 := v_mainjson.json_data (3).str;

   p_person_rec.attribute5 := v_mainjson.json_data (4).str;

   p_person_rec.created_by_module := 'TCA_V2_API';

   hz_party_v2pub.create_person (p_init_msg_list      => 'T',

                                 p_person_rec         => p_person_rec,

                                 x_party_id           => x_party_id,

                                 x_party_number       => x_party_number,

                                 x_profile_id         => x_profile_id,

                                 x_return_status      => x_return_status,

                                 x_msg_count          => x_msg_count,

                                 x_msg_data           => x_msg_data

                                );

   IF x_return_status <> 'S'

   THEN

      FOR j IN 1 .. x_msg_count

      LOOP

         fnd_msg_pub.get (j, fnd_api.g_false, x_msg_data, vmsgdummy);

         lerrmsg := lerrmsg || ('Msg' || TO_CHAR (j) || ': ' || x_msg_data);

      END LOOP;

      finaljson := json ();

      oracleinfo := json_value ();

      BEGIN

         oracle_json := json ();

         oracle_json.put ('Party_Id', TO_CHAR (x_party_id));

         oracle_json.put ('Party_Number', x_party_number);

         oracle_json.put ('Profile_Id', TO_CHAR (x_profile_id));

         oracleinfo := json_ac.object_to_json_value (oracle_json);

         json_ac.array_append (oracle_list, oracleinfo);

      END;

      finaljson.put ('status', 'Error');

      finaljson.put ('message', lerrmsg);

      finaljson.put ('Person', oracle_list);

      RETURN finaljson;

   ELSE

      COMMIT;

      finaljson := json ();

      oracleinfo := json_value ();

      BEGIN

         oracle_json := json ();

         oracle_json.put ('Party_Id', TO_CHAR (x_party_id));

         oracle_json.put ('Party_Number', x_party_number);

         oracle_json.put ('Profile_Id', TO_CHAR (x_profile_id));

         oracleinfo := json_ac.object_to_json_value (oracle_json);

         json_ac.array_append (oracle_list, oracleinfo);

      END;

      finaljson.put ('status', 'Success');

      finaljson.put ('message', 'Person created Successfully');

      finaljson.put ('Person', oracle_list);

      RETURN finaljson;

   END IF;

   lretmsg :=

         'Party Id = '

      || TO_CHAR (x_party_id)

      || 'Party Number = '

      || x_party_number

      || 'Profile Id = '

      || TO_CHAR (x_profile_id)

      || 'x_return_status = '

      || SUBSTR (x_return_status, 1, 255)

      || 'x_msg_data = '

      || x_msg_data

      || 'lErrMsg: '

      || lerrmsg;

   --return lretmsg;

   IF x_msg_count > 1

   THEN

      FOR i IN 1 .. x_msg_count

      LOOP

         lretmsg :=

               lretmsg

            || '  '

            || i

            || '. '

            || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255);

      END LOOP;

   -- return lretmsg;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('ERROR');

      DBMS_OUTPUT.put_line (SQLCODE || ' ' || SUBSTR (SQLERRM, 1, 64));

      finaljson.put ('status', 'Failure');

      finaljson.put ('message', SQLCODE || ' ' || SUBSTR (SQLERRM, 1, 64));

      RETURN finaljson;

END;

/

To get web service URL click on Test button from resource handler

This will open a browser with web service URL.

URL

       http://<IP_ADDRESS>:8889/ords/levey/create_account?p1={p1}

Publish the URL on Internet in order to get it accessed from SFDC.

SFDC will call this WS from there end by appending Person details JSON to URL.

Person will get created at Oracle instantly after firing the URL at SFDC.

Testing of WS from Oracle side

Click on set Bind Parameters from Resource handler

Enter below JSON  in Bind Variable

{"Person":{"First_name":"John","Last_name":"Smith","Phone":"12345","Email":"test@test.com"}} 

Hit the Test button from the same page. Post clicking it will call web service and Oracle will return Party information in Json format.

This JSON is used by SFDC to replicate Party information on Contact page.

Steps at SFDC:

            Create Page with First Name, Last Name, Phone and Email.

            Create one button on same page.

            Create a logic to build a JSON on SFDC to pass required fields in Oracle to call API.

            Create a logic to append the JSON to URL and hit from button.

We can use existing seeded pages like Account, Opportunity, Product etc and write trigger at particular stage or on any button to call web service.

NOTE: It may require to whitelist SFDC IP address in your firewall/network to call/access Oracle APIs from SFDC.

    Related Posts :

Oracle REST Data Services (ORDS) Installation on Linux Server and Configuration with Oracle EBS

Accessing Oracle APEX Using Embedded PL/SQL Gateway

Posted in: ORDS