Oracle E-Business Suite printing made easy with Pasta!

by Kenny Miller, Principal Consultant for Rocela

This one is for my fellow EBS veterans who’ve spent more time than they’d like to admit fiddling with application printer settings. Maybe, like me, you’ve been aware of Pasta but have been too busy to find out what it is and how it works.

Necessity is indeed the mother of all invention so, when recently faced with a challenging printing requirement, I was forced to find out all about Pasta. I liked what I found, so I thought I’d share my experiences with you.

The Requirement

My client is upgrading from R11 to R12. In R11, they only needed to print text output directly from the concurrent manager. However R12 comes with many more standard XML Publisher reports, so they now have a requirement to print PDF output.

My client also has hundreds of printers, from many different manufacturers and of different vintages! More specifically the requirement was:

1) Print all text and PDF output directly from the concurrent manager.
2) Print in PCL because:
a.  Not all printers support PostScript.
b.  Not all operating system print queues are enabled for PostScript, even for printers that actually do support it.
3) Minimise the amount of application set-up changes required.

So what is Pasta?

The following is taken from the System Administrator’s Configuration Guide:

“Pasta is an Oracle E-Business Suite utility that converts text report files to PostScript and also enables the printing of custom PostScript reports from Oracle E-Business Suite. The reports can then be directed to any PostScript printer.

Setting up your system to use Pasta is much simpler than the standard Oracle E-Business Suite printer setup procedure. The Printer Type, Printer Driver, and SRW driver files are provided. The only setup required to begin printing is the registration of the printer with Oracle E-Business Suite.

Many printing options can be defined using the Pasta configuration file (pasta.cfg). You no longer need to maintain multiple drivers and styles for each printer.

Pasta is provided as an executable named FNDPSTAX.”

I make no apology for copying this – it’s actually a good summary of Pasta. However the first paragraph only mentions PostScript i.e. converting text to PostScript, printing custom PostScript reports to PostScript printers etc. Mere mortals may have given up at this point, thinking Pasta is only about PostScript and isn’t able to do PCL. Yours truly is made of sterner stuff, and decided to have a look “under the hood” to find out what Pasta is really capable of.

PostScript or PCL?

Well, “out-of-the-box” Pasta actually does neither! All it’ll do is print text!

However, the Pasta configuration file does provide a “pre-processing” option, which can invoke any executable that supports an input file and an output file. Therefore, executables such as pdftops and acroread can be used to convert PDF’s to PostScript, or Ghostscript can be used to convert PDF’s to PCL!

The default configuration file is $FND_TOP/resource/pasta.cfg and by adding the following line to this file, PCL printing is enabled for both text and PDF output:

preprocess=gs -q -dNOPAUSE -dBATCH -sDEVICE=pxlcolor \

-dNORANGEPAGESIZE -sOutputFile={outfile} {infile}

In summary, this is using the Ghostscript executable (gs) to convert the input file to PCL. The device switch of pxlcolor tells Ghostscript to convert to colour output in PCL XL format. PCL XL (also known as PXL) is part of PCL version 6 which was released by HP in 1996, and should therefore be supported by all modern printers.

Problem Solved

My client is now able to print text and PDF output in a variety of different print styles, to printers from different manufacturers. To do this, all that was required was the following:

1) The change to pasta.cfg described above.
2) Changing the printer type to the seeded:
–PASTA Universal Printer Type
3) Bounce the concurrent manager.

Sounds too easy!

It would be very time-consuming to test all combinations of print styles and printer types; therefore you need to be realistic enough to expect the occasional anomaly. Pasta caters for exceptions by providing:

1) Printer specific configuration files.
2) Printer Driver specific configuration files which can be specified in the arguments for the Printer Driver.
3) A combination of Printer and Printer Driver specific configuration files!

If you’re able to successfully print output from the command line, then Pasta will be able print the same output from the concurrent manager.

Summary

I’ve found Pasta powerful but simple to use. If you already know your way round EBS printer set-ups then you’ll take to Pasta like a duck-to-water. Even if you don’t, it’s well worth investing some time to better understand it. It’ll be well worth it.

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…..


Oracle BI Publisher set to become every accountant’s best friend (Part 2)

by Kenny Miller, Principal Consultant for Rocela

In my last post (Part I – Oracle BI Publisher set to become every Accountants best friend – 21st September 2011), I spoke about the newly added Excel functionality Oracle introduced into IIg BIP, otherwise known as ‘True Excel’ and the advantages that this brings to Oracle EBS users.

So in this post, I’ll run through some report conversion examples.

Data selection can achieved with any tool that produces XML output. It’s easy to convert any standard Oracle Reports concurrent program – simply change the output format of the program to XML (see example below). This is the simplest method for generating XML output for concurrent programs, but PL/SQL, SQL*Plus, and Java Concurrent Programs can also be used.

Report Conversion Example

There is no need to convert all existing custom reports to BIP – these will continue to run in R12 (assuming that the impact of any database changes are resolved).

However all payment format output (e.g. BACS and cheque prints) MUST be converted from reports to BIP. The payment functionality in Payables has been replaced in R12 with a new Payments application which only uses BIP to generate report output.

The following is a simple example of how to convert a standard program.

Create a custom Concurrent Program

Copy the program, changing the Output Format to XML:

Add to Request Group

The new program needs added to a request group, just as any other concurrent program does:

The request should now be run to generate XML output (this will then be used to develop the layout of the report):

BIP Data Definition

Use the “XML Publisher Administrator” responsibility to create a Data Definition. By using the same name for both the Concurrent Program Short Name and the Data Definition (in this example “FNDSCRUR_XML”) our concurrent program becomes a BIP concurrent program.

Microsoft Word Desktop Template Builder

The Template Builder installs as a plug-in to Microsoft Word, and is used to develop the report layout. Describing how to develop the layout is beyond the scope of this document. However, the Template Builder includes a comprehensive User Guide:

The following two layouts were developed, one designed to produce PDF output, and the other designed to produce Excel output (XHTML – not “True Excel”):

FNDSCRUR_XML_PDF.rtf

FNDSCRUR_XML_XLS.rtf

BIP Template

Use the “XML Publisher Administrator” responsibility to create a Template for the developed layouts:

Submit Concurrent Request

When we go to submit our request, because we’ve linked our Data Definition to two different templates, we now have the option of selecting which output we want to produce:

The program output using the template “Users of a Responsibility (XML) PDF” is:

“True Excel”

At the time of writing, only the 11g Template Builder can be used to build “True Excel” templates for R11 and R12. However, only the 10g Template Builder can be used to develop RTF templates for R11 and R12.

Unfortunately, the 10g and 11g Template Builders can’t both be installed on a machine at the same time, meaning anyone who will be developing both “True Excel” and RTF templates will either need to install/de-install as required, or install in multiple separate machines or virtual machines.

Chapter 6 of the “Report Designer’s Guide for Oracle Business Intelligence Publisher” describes how to create a “True Excel” template. See http://download.oracle.com/docs/cd/E21764_01/bi.1111/e13881.pdf for more details.

eText Templates

Normally the layout for BI Publisher reports is created using a RTF template. Another type of template for BI Publisher is eText. This gives precise control over where each character appears in the output, and is used to produce the output for interface files e.g. payment output files. An example of an eText template is shown below:

In conclusion

To sum up, BI Publisher makes reporting directly from EBS to Excel much easier than it’s ever been before. Simple tabular reports can be easily created using the XHTML approach, but why not take some time to fully explore the benefits of “True Excel”? For those that haven’t used BI Publisher before then hopefully the simple example shown above removes any “fear of the unknown”.

BI Publisher may well be Oracle’s reporting tool of the future, but it’s here right now, and it’s not difficult to use. Why not take advantage of it?!?

%d bloggers like this: