Using Oracle JSON_Table to Insert Suppliers and Invoices

The JSON_TABLE function in Oracle can be used to insert JSON data into Oracle E-Business Suite. This article will show you how to insert suppliers, supplier sites, and invoices from a JSON dataset.

The Initial Request

Let’s pretend have a list of people that we owe a refund. We need to import these people into Oracle E-Business Suite, create an invoice for them, and cut each one a check. But, we have several hundred, so we don’t want to enter these by hand.

So, how do you do this? Here is one option:

Let’s use JSON!

Why? A couple reasons:

  1. Oracle’s JSON_TABLE function makes JSON easy to use in Oracle 12c
  2. An Excel spreadsheet is easily converted to JSON
  3. It’s always fun to learn something new

The JSON Data

If you’ve never encountered JSON in the wild, it’s still pretty to figure out just by looking at it. JSON is split up into key/value pairs, separated by a colon (:).  JSON is much more compact and flexible than XML, and it becoming very popular on the web. Here is a sample of what JSON data looks like:

While the above is valid JSON data, I have a much bigger file, full of 100 fake customers available here: SMJ_JSONData_file.

JSON is pretty easily converted from any other data source. Oracle database (as of 12c) can export JSON. And, if your accountant just gives you an Excel spreadsheet it can be easily converted with online JSON conversion tools too.

The PL/SQL Code using JSON_TABLE

Now we need an outline of what is going to happen in this code. Here is a quick summary of what we need to do:

  • Declare variables
  • Load external file of JSON data
  • Build cursor using Oracle’s JSON_TABLE function
  • Start a loop for each JSON record
    • Add a supplier record using create_vendor API
    • Add a supplier site record using create_vendor_site API
    • Insert an invoice into AP_INVOICES_INTERFACE table
    • Insert an invoice line into AP_INVOICE_LINES_INTERFACE
    • Output some messages so we know if everything is working

Here is some PL/SQL which will do the trick:

The additional points to consider

JSON_TABLE made it really easy to build a cursor from raw JSON data. After that, it was pretty simple PL/SQL. If you’re copying and pasting the above code, here are a few things to consider:

  1. Verify the location of the JSON file on the server (see line 13) and that it has the correct permissions
  2. Many of the DBMS_OUTPUT.put_line lines are there simply for instruction and debugging
  3. Make sure you have valid values for the following:
    • vendor_type_lookup_code
    • pay_group_lookup_code
    • org_id
    • accts_pay_code_combination_id

But, this works and will add 100 new suppliers and 100 new invoices from the JSON source data. Since many of the modern APIs return data in JSON format, JSON_TABLE will be an important and useful function in your repertoire.

Create an E-Business Suite User using a PL/SQL script

Some tasks are easier to do using a simple database script. If you need to create an E-Business Suite user account for testing purposes, it is faster to use a script.

Sure, you could navigate to Application Administrator > User Administration and create an E-Business Suite user account in the Users form. But that just takes too long. And, most of the time you’re creating a simple test user for a specific purpose in a test instance. Don’t waste your time with Users form — it’s much easier using the script below.

Create an E-Business Suite user account with a script instead of trudging through the forms.

But I’m guessing you already have a database session up, so just copy, paste, and run the script below. It will quickly and easily create an E-Business Suite user “USER_NAME” with the password “PASSWORD”. Update those values before running the script. Also, don’t forget to run this under the APPS schema.

After you create an E-Business Suite user, a natural next step is to assign that user roles and responsibilities necessary to complete your testing. I wrote another PL/SQL script that assigns common system administrator responsibilities which would be a great logical next step.

Ultimate script to automate system administrator access

Most Oracle developers and analysts don’t have system administrator access in Oracle Applications production instances due to company policy or SOX/PCI compliance. That lack of access trickles down during every clone process, and each fresh copy requires setting up user accounts all over again. Most of us have to log into Oracle Applications and manually add all of the system administrator access by choosing the different responsibilities we need.

At my current job, I have access to a reporting instance. It is a nightly refresh of all production data. So, it’s extremely useful for testing code and new setups before moving into production. But, because it is refreshed daily I had to automate system administrator access in Oracle Applications.

The reporting instance I often used has forced me to rethink this process of adding system administrator access. I didn’t want to manually update my user account each time. I’m a techno-functional-developer, so I have very few responsibilities granted to my account in production (I’m way too dangerous), but may need a variety of responsibilities depending on my current project.

So, I wrote a script to automate system administrator access in Oracle Applications. It does the following:

  1. identifies the username being changed
  2. creates a list of responsibilities to be added
  3. creates a table of profile options to be set
    • Java Color Scheme
    • Diagnostics
    • Personalizations
  4. updates profile options from table in #3
  5. adds several hardcoded roles
  6. adds responsibilities based on the list from #2

 

This script could be used to:

  • add system admin access following a clone
  • learn some PL/SQL basic code structures
  • onboard a new analyst or developer or consultant (just adapt the list of responsibilities)
  • automate granting responsibilities to a type of user (for example, if your accounting group is always bringing on new GL accountants who always need a specific set of GL responsibilities in different ledger sets)

Integrated SOA Gateway: Generate WSDL Example

This is a step-by-step guide to deploying a WSDL from the Integrated SOA Gateway in Oracle’s E-Business Suite 12.1.3. This is a simple example that should open up the world of making some of the canned APIs and services available for integration projects.

If you’re looking for hundreds of pages of in-depth info, please refer to Oracle’s Integrated SOA Gateway User Guide. We’re going to keep things simple here and walk though an example step-by-step.

Open the Integration Repository

So, to start off select the Integrated SOA Gateway responsibility and then the Integration Repository. If, when selecting the Integration Repository you don’t also see the Administration area, then you need to assign the correct UMX|FND_IREP_ADMIN role.

Integration Repository navigation

Find the business service interface

For this tutorial, we will deploy the WSDL for the SOA Health Check Test Package. It’s a good one to start out with because it is a very simple PL/SQL package. To find this one you can either search for it by name or internal name (SOA_DIAG_TST). Or, simply browse to it as shown in the image below.

Browsing through the Integration Repository to find the SOA Health Check Test Package

Clicking on the SOA Health Check Test Package link will display more about what functions are available in this package. Please note that it is a PL/SQL interface.

Clicking on the Test Function link in the Procedures and Functions section toward the bottom of the page will display more information about what this function will be doing. You will noticed that it takes in 3 parameters (ARG1, ARG2, and ARG3 — not a very creating naming convention, but this is a very simple function) and returns a number.

Details of the Test Function

Given how simple this particular function is, it would be an ideal first WSDL to deploy from the Integrated SOA Gateway. It would also be a great way to test connectivity to a partner who will be consuming E-Business Suite web services.

Grant appropriate access

Since we now know the details about the SOA Health Check Test Package, it’s time to decide who should have access to the TestFunction function.

Create Grant for Test Function

Check the checkbox for the Test Function near the bottom of the page and click on the Create Grant button. This will take you to the Create Grants page where you can grant to a specific user, a group of users, or open it up for everyone. Since this is a simple example, it would probably be easiest to grant access to All Users. Then hit the Apply button.

Integrated SOA Gateway Generate WSDL Example Create Grant

You should get an information message saying that a grant has been created successfully for operation ‘Test Function’.

Generate WSDL

The final step is to click on the Generate WSDL button in the top right corner. Simple right? Again, you should see an information message, and when the operation is finished you should notice a new section in the main page of the SOA Health Check Test Package. The Web Service – Web Service Provider area now clearly shows the status as Deployed. The WSDL URI is also shown for your environment.

integrated_soa_gateway_responsibility0

You’re done!

Start using the Integrated SOA Gateway

The Integrated SOA Gateway in the Oracle E-Business Suite (EBS 12.1.3) is a fantastic resource for system integrators. But, the Oracle-provided documentation isn’t very clear. Here are some notes that may help you start using the Integrated SOA Gateway.

The obvious first step is to assign the responsibility “Integrated SOA Gateway” to the user needing access. This will give that user access to browse through all of the APIs provided by the Integrated SOA Gateway. But, it is a read-only access. Feel free to browse through the available APIs and get familiar with what is out there.

To really start using the Integrated SOA Gateway, you will need to assign the “Irep Administrator” role. After this is done correctly, you should be able to see an Administration tab at the top of the Integration Repository page.

The "Administration" tab only shows up after assigning the correct role.

There are a few key roles which unlock some of the hidden features of the Integrated SOA Gateway:

  • System Integration Analyst (UMX|FND_SYSTEM_INTEGRATION_ANALYST)
  • System Integration Developer (UMX|FND_SYSTEM_INTEGRATION_DEVELOPER)
  • Irep Administrator (UMX|FND_IREP_ADMIN)

Unfortunately Oracle wasn’t very consistent with their naming conventions in this case. At first, I only granted myself the “System Integration Developer” role because I didn’t know about Irep Administrator. As a result, I walked through the Oracle documentation ( and kept wondering why my “Administration” tab wasn’t showing up. So, the key is to assign the Irep Administrator role as shown below.

  1. Log into the Oracle EBS applications
  2. Navigate to User Management responsibility
  3. Navigate to Users web page
  4. Find the user
  5. Click on Update
  6. Click on Assign Roles button
  7. Find and choose “Irep Administrator” role
  8. Apply

Assign Irep Administrator role to start using the Integrated SOA Gateway

Please note: If you haven’t granted roles before, you’ll need to follow the same steps after logging in as SYSADMIN and performing the same steps for your user. But grant the “Security Administrator” role, which allows you to assign other roles (like Irep Administrator).

The workflow background engine will need to run successfully before the changes are applied. But, that’s it. Now you have unlocked the Administration tab. Now you can really start using the Integrated SOA Gateway. You should also notice the “Generate WSDL” button is available so you can start deploying web services right from the Integration Repository.

Now the Generate WSDL button should be available