Desktop Integration Framework – not just a fairy tale

by Kenny Miller, Principal Consultant for Rocela

Long, long ago in a place far away, a Rocela consultant blogged about the Desktop Integration Framework (DIF). The blog read like a fairy tale – it described a mythical new tool from Oracle but failed to provide any evidence to prove it had ever been seen in the real world.

I was that consultant. As promised, I’ve returned to demonstrate that DIF is not a fairy tale. It may even be worthy of all the hype!

What is DIF?

Applications Desktop Integration (ADI) is Oracle’s standard tool for connecting Oracle EBS with Microsoft Office tools e.g. HR letter generation using Word, and uploading GL journals using Excel. The problem with ADI had always been that only Oracle could decide what it was used it for. There was no supported way to create your own ADI document for something that Oracle hadn’t already provided.

DIF solves this problem. It’s available from R12.1.2 onwards, and provides a supported method of creating custom ADI documents, allowing Excel data to be properly validated and uploaded through any interface or API into Oracle EBS.

Why should we get excited by DIF?

No matter how hard we try, we just can’t keep our Oracle users away from their favourite Microsoft tools. Can anyone imagine a world without Excel?

We’ve all had to build solutions to get data out of Excel for loading into Oracle EBS. The usual approach is to save the data as a CSV file, transfer it to the Oracle server, use SQL*Loader or External Tables to load into a staging table for validation, then call an API or interface to create the data in Oracle. It’s always complicated. It always has too many things to go wrong. Users never really understand how it’s all supposed to work.

DIF allows us to develop our own ADI documents. We can provide our users with the means of creating or updating data in Oracle EBS, directly from within Excel. These ADI documents fully comply with the EBS security model, and real-time list-of-values can be provided to further improve data quality. All validation failures are reported immediately in the document, allowing for correction and re-loading. There are no files to transfer, no missing records caused by loading errors etc.

What do you need to use DIF?

DIF is only available for R12.1.2 and R12.1.3. To use DIF, all you need is for the Desktop Integration Manager responsibility to be added to your user. There are no special roles or grants required.

To get you started, I’d recommend the following material:

  • Oracle note 807319.1 which lists all the Transfer of Information (TOI) content for R12.1. Search the note for Implement and Use Oracle E-Business Suite Desktop Integration Framework and you’ll find some informative eSeminars.
  • The Oracle E-Business Suite Desktop Integration Framework Developer’s Guide. R12.1.3 is available here. R12.1.2 is only available in note 979354.1. There are minor differences between DIF in R12.1.2 and R12.1.3 so I’d recommend you use the relevant version.

Enough chit-chat, let’s see how it works!

I’ve created an example to demonstrate that DIF doesn’t have to be difficult. As you’ll see from the Developer’s Guide, there’s plenty more than DIF can do, but I just wanted to show you how quickly something useful can be developed.

1)      Program Code

CREATE or REPLACE PACKAGE BODY xx_webadi_dif_pkg AS

  -------------------------------------------------------------------------
  -- FUNCTION create_value_set_value (Public)                            --
  -------------------------------------------------------------------------
    -- A simple function to create a value in an independent value set.  --
    -----------------------------------------------------------------------

  FUNCTION create_value_set_value(p_value varchar2,
                                  p_description varchar2)
    RETURN varchar2 IS

    -- Variable Declarations.

    x_storage_value   varchar2(240);

  BEGIN

    -- Call the API. There are no status or error parameters,
    -- the API throws an error if required.

    fnd_flex_val_api.create_independent_vset_value
     (p_flex_value_set_name => 'XX_WEBADI_DIF_Test',
      p_flex_value          => p_value,
      p_description         => p_description,
      x_storage_value       => x_storage_value);

    -- No need to commit because ADI will commit if all rows
    -- processed successfully. Just need to return null
    -- because if we get to here then there have been no
    -- errors.

    RETURN null;

  EXCEPTION WHEN others THEN

    -- Return the error thrown by the API.

    RETURN sqlerrm;

  END create_value_set_value;

END xx_webadi_dif_pkg;

PL/SQL packaged procedures and functions can be used by DIF, but they must only have parameters of simple datatypes i.e. varchar2, number or date. Many standard API’s use complex parameters, and it’s not unusual to have to call multiple API’s to get the job done; therefore I’m of the opinion that a custom wrapper will almost always be needed.

There are various approaches to error handling but, because I’ve decided to always write a wrapper, I can also standardise the way I handle errors. For me, it just seems logical to use a function and return any error messages from the function. This is the approach I’ve taken in the example.

2)      Integrator

“An integrator is a set of metadata that encapsulates all the information needed to integrate a particular Oracle E-Business Suite task with a desktop application.”

Select Create Integrator from the Desktop Integration Manager responsibility. Enter the details below, and select the Next button to move to Step 2.


3)      Interface

“An interface is a set of metadata that determines how the integrator uploads data from a desktop document to Oracle E-Business Suite.”

Assuming you’ve created the XX_WEDADI_DIF_PKG package, then enter the details below, and select the Apply button.

This automatically creates the interface, including an attribute for each of the function parameters. The attributes can be updated to make them required, change the prompts, assign default values, create validations and list-of-values etc. This example doesn’t require any updates to attributes, so the integrator can now be saved by selecting the Submit button. Steps 3 to 5 are not required for this example, but it’s worth making time to understand them.

4)      Layout

“A layout is a set of metadata that determines what fields appear in the desktop document and how those fields are displayed and formatted.”

Select Manage Integrators from the Desktop Integration Manager responsibility. Select the Define Layout button and select the newly created Integrator from the drop-list, then select the Go button.

You can experiment with the Placement and Default Values later. For the moment, we just need to place both fields on the Line, and then select the Next button.

You can now control field positions, widths etc. Again, you can experiment later, but for the moment set the values as shown, then select the Apply button.

That’s all we need to do. Now we’re ready to test it!

5)      Testing

If you have problems using ADI in Excel, then Oracle document 452452.1 will help you trouble-shoot.

Select Manage Integrators from the Desktop Integration Manager responsibility. Then search for the integrator, select the radio-button, and select the Preview button.

Select your appropriate version of Excel, and then move through the various options until an Excel worksheet opens

Data is uploaded by selecting Add-ins-> Oracle -> Upload. The following example shows what happens if I attempt to upload the same value twice. The first record is accepted, but the second record is rejected as a duplicate. I didn’t explicitly commit within my packaged function, therefore ADI performed a rollback because of the error, meaning the first record was not committed.

After correction, I’m able to successfully upload my data.

WebADI issues a commit only when all records have are processed successfully. The values have now been added to my Value Set.

How is DIF deployed?

1)      Form Function

Now we can create a Form Function to deploy our Integrator to our users! Use the following SQL to determine the values needed for the parameters:

SELECT integrator_code
FROM bne_integrators_vl
WHERE user_name = 'XX Value Set Test';
--XX_VALUE_SET_TEST_XINTG

SELECT layout_code
FROM bne_layouts_vl
WHERE integrator_code = 'XX_VALUE_SET_TEST_XINTG';
--VALUESETTEST

SELECT content_code
FROM bne_contents_vl
WHERE integrator_code = 'XX_VALUE_SET_TEST_XINTG';
--XX_VALUE_SET_TEST_CNT1

Create the Form Function as follows:

2)      FNDLOAD

FNDLOAD can be used to move the Integrator, and Layout, from one environment to another. Check that patch 10385955 has been applied first – if it hasn’t, your Integrator will be created with a source of Oracle, not Custom, meaning you won’t be able to update it!

To download:

FNDLOAD apps/&pwd 0 Y DOWNLOAD /
$BNE_TOP/patch/115/import/bneintegrator.lct /
XX_VALUE_SET_TEST_XINTG.ldt BNE_INTEGRATORS /
INTEGRATOR_ASN=FND INTEGRATOR_CODE=XX_VALUE_SET_TEST_XINTG

To upload:

FNDLOAD apps/&pwd 0 Y UPLOAD /
$BNE_TOP/patch/115/import/bneintegrator.lct /
XX_VALUE_SET_TEST_XINTG.ldt

Summary

There’s a lot more to DIF than I’ve covered in this example, but I hope I’ve demonstrated how quickly something useful can be developed. It’s not difficult to expand this example to build complex solutions. I’ve already used DIF for an inventory item migration – templates were applied to create new items, which were then assigned to different organizations. I borrowed most of the program code from previous projects, but it only took a couple of hours to pull everything together and have a working ADI document upload.

This is the power of DIF – it doesn’t just provide genuinely useful new functionality, but it also provides a framework to quickly develop and deploy this new functionality.

And they lived happily ever after…..


Advertisements

One small step for man, one giant leap for Web ADI

by Kenny Miller, Principal Consultant for Rocela

I recently blogged about ”BI Publisher becoming every accountant’s best friend”. However, as we all know, it’s difficult to keep an accountant happy for long. They’ll likely soon be asking an awkward question of their new best buddy:

“OK, I can see that BI Publisher is great for downloading data from Oracle EBS (E-Business Suite) straight into Excel, but can I do it the other way round? Can I upload data from Excel into Oracle EBS?”

BI Publisher is a reporting tool. It can’t upload data into Oracle EBS. So what other options are available?

Many of us will have developed custom CSV interfaces, where users create data in Excel, save it as a CSV file, which they somehow transfer to the Oracle EBS server, before SQL*Loader (or External Tables, if you’ve “moved with the times”) populates the data into a temporary table where finally it’s processed by a custom program using a standard API or interface. A convoluted solution which has its problems:

  • Having so many “moving parts” means there are a number of different ways for the whole thing to fall over in a crumpled heap (technical term, meaning to “perform sub-optimally”).
  • It’s difficult in Excel to validate the data to be accurate and relevant, without resorting to something complicated like ODBC or Visual Basic. For example, does a cost centre value entered in Excel actually exist in Oracle EBS, is an accounting date in an open period etc?

If you’re interested enough to still be reading then I expect you already know all about Client and Web ADI in Oracle EBS (for those of you still on 11i, be aware that it is Web ADI only in R12). Application Desktop Integrator (ADI) is Oracle’s standard tool for connecting Oracle EBS with MS Office tools e.g. HR letter generation using MS Word, and uploading GL journals using MS Excel.

The problem with ADI had always been that only Oracle could decide what you used it for. There was no supported way to create your own ADI document for something that Oracle hadn’t already provided. HR users have always “felt the love” from Oracle who provided them with numerous standard ADI documents. Unfortunately most of our accountant friends missed out on an invite to the ADI party. Oracle doesn’t provide even a single standard ADI document for either Payables or Receivables, so you won’t be using ADI to load your supplier invoices or your cash receipts.

However, this has all now changed! Available from R12.1.2 onwards is the “Desktop Integration Framework” (DIF)! Finally there is a supported way of creating custom ADI documents, allowing data to be properly validated and processed through any interface or API into Oracle EBS. Only time will tell if this is a “giant leap for Web ADI” but it’s certainly a welcome new option to have available, and one I thought you’d like to know about it.

If you want to learn more then I’d recommend the following material:

  • Oracle note 807319.1 which lists all the Transfer of Information (TOI) content for R12.1. Search the note for “Implement and Use Oracle E-Business Suite Desktop Integration Framework” and you’ll find a very informative eSeminar.
  • The “Oracle E-Business Suite Desktop Integration Framework Developer’s Guide” in the documentation library, which for R12.1.3 is available here.

I’m hoping to use DIF soon to develop a custom ADI document for a client. I’ll blog again once I’ve done so – I’m sure you’d like to know if the reality lives up to the hype. In the meantime, if you have any experiences with DIF you’d like to share, then please let me know.

 

 

%d bloggers like this: