Desktop Integration Framework – not just a fairy tale
March 22, 2012 Leave a comment
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…..
