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.

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)

Simple request, simple solution

I work for an international company that has organizations all over the place, and some users can’t figure out the currency for some organizations easily, which can be a problem when analyzing item costs.

So, we got a fairly simple request. The request was to create a way for users to look up the currency code when looking at the Item Costs screen in Oracle.

One member of our team suggested two ways to solve the problem. The first was to create a descriptive flexfield to populate the currency code. The second one was to customize the item cost form, and add the currency code to the form.

Both seemed too complex for my tastes. A simple request deserves a simple solution.

I had an idea. A simple idea. “What about a really simple personalization?”

Ten minutes later, the problem was solved. The team member with the complex solutions admitted my idea worked, but he didn’t like that it wasn’t scalable. I’m not even sure what that means in this context.

The Boss liked the solution. The group requesting the change liked the solution. Everyone is happy (well, other than it-won’t-scale boy).

Here is the simple solution:

Navigate to the Item Costs Summary (CSTFITCT) form. Go to Help > Diagnostics > Custom Code > Personalize

Create the forms personalization by first creating a custom menu item under the Tools menu

Seq: 10
Description: Create the Currency Code menu
Level: Form
Condition Tab
Processing Context: Not in Enter-Query Mode
Context: (if you wanted you could turn this on for certain users or responsibilities)
Actions Tab
Seq: 10
Type: Menu
Description: Activate Tools Menu
Menu Entry: MENU1
(Oracle left MENU1 – MENU15 to us developers to use at our whim, but don’t use the SPECIALs)
Menu Label: &Currency Code

Then we activate that menu item and have it display a simple message with the currency code

Seq: 20
Description: Display the currency code
Level: Function
Condition Tab
Trigger Event: MENU1
Processing Mode: Both
Actions Tab
Seq: 10
Type: Message
Description: Display a message
Language: All
Message Type: Show
Message Text:
=SELECT ‘The currency code is ‘||gsob.currency_code FROM
gl_sets_of_books gsob
INNER JOIN org_organization_definitions morg on morg.set_of_books_id = GSOB.SET_OF_BOOKS_ID
WHERE morg.organization_id = fnd_profile.value(‘MFG_ORGANIZATION_ID’)

And you’re done. The message text must start with the =SELECT and you can’t concatenate it with regular text, so any additional text has to be done in the SQL statement.

When the user has selected an organization and navigated to the Item Costs Summary screen, he or she can easily select Currency Code from the Tools menu.

A simple message box will pop up with the currency code for that inventory organization. Easy.

Let me know if you have any better ways to solve this problem in the comments below. Especially if it is scalable.