Who Am I? An investigation into R12 UMX Proxy User functionality : Part 1

Just back from a short vacation and I learnt an important lesson.

My tent is NOT waterproof.

Not the best thing to find out at 2.30am on a dark, wet and cold night in the middle of a Farmers field in the English countryside!

A different post from me today.

On my current project, we've been struggling with the requirement to allow Administrators and other Users to perform the functions of another User without knowing their password e.g. submit and review reports, approve documents, perform iprocurement transactions and so on.

My friends at Solution Beacon turned me on to the new R12 User Management (UMX) Proxy User functionality to allow a user to sign on to the E-Business suite and assume ALL the responsibilities of another User WITHOUT knowing their password.

Turns out that this functionality is a perfect fit.... or so we thought!

In this post, I'll walk through the steps to set up the UMX Proxy User functionality. In my next post, I'll discuss the biggest stumbling blocks: auditing and control.


Steps to setup R12 User Management Proxy User Functionality:

Setting up this functionality is actually very simple. To illustrate, I'll refer to two types of E-Business suite Users:
1) MANAGER - this is the User who will delegate responsibility to perform transactions on their behalf to the Administrator user. In my examples, this user name is KWOODROW1.
2) ADMINISTRATOR - this is the user who will sign on to the E-Business suite and assume the responsibilities of the Manager user. In my examples, this user name is JSMITH.

Step 1
- Connect to the R12 E-Business Suite as ANY User (e.g. SYSADMIN) with the responsibility 'User Management' and open the 'Users' form.
- Search for the user name of the MANAGER. In this case, the user name is KWOODROW1.
- Assign the role 'Manage Proxies' to this user name e.g.



Step 2
Now connect as the MANAGER user e.g. KWOODROW1 and navigate to the 'Preferences' form:



Click on the 'Manage Proxies' option and then, the 'Add People' button.

This will allow you to add the User Names of those Users that you wish to perform actions on your behalf:



Step 3
Now, add the user names of all Users that you wish to assume your responsibilities.
In my example, I add the user name of my ADMINISTRATOR JSMITH:


Apply the changes and exit the E-Business suite.

Step 4
OK - now comes the fun stuff.

Connect as the ADMINISTRATOR JSMITH:




Note - the new options 'Switch User' and 'Return to Self' available:



As its name suggest 'Switch User' is the magic button that allows you to 'become' the other User without having to enter their password i.e. JSMITH can become KWOODROW1 and access their responsibility list.

Step 5

Select the 'People Icon' to switch your user to that of KWOODROW1:



Step 6
As if by magic, you are navigated to the home page of the proxied user, can access any of their current responsibilities and can perform action on their behalf in the E-Business that they themself are allowed to perform:


Note - the label 'Logged in As JSMITH Proxy For KWOODROW1':



Step 7
OK - to return to the original user session, select the option 'Return to Self'



That's all there is to it! Powerful functionality - but very simple to set up.

In my next post, I'll look at the Auditing and control options available for this new functionality.

BIP XSL-TEXT Templates : Part 2

Not much changes in Birmingham.

It seems like an eternity ago that I was at university there - but after only a couple of hours and a fine Balti in a BYOB restaurant, I felt like I'd never left.

Anyway, enough of my weekend! Let's get back to XSL-TEXT templates.

Excluding XSL-FO, there are 3 different types of XSLT script templates available in BI Publisher. These are currently not greatly documented in the BIP User Guide. However, the difference between them is the format of the output created by the XSLT script i.e.:

XSL-HTML	A XSLT script to transform an XML document into a HTML file
XSL-XML	A XSLT script to transform an XML document into another XML document
XSL-TEXT	A XSLT script to transform an XML document into a TEXT file

Note: The XSL element used to set the output type is:



Generally, it is a lot simpler to use an RTF or eText template to create the report layout - and I would ALWAYS recommend using an RTF or eText template where possible for this reason.

However, there comes a time when it may be easier to write XSL and XPATH directly rather than live with any shortcomings of the RTF or eText template type.

One such example of this is to add blank lines and additional formatting logic into eText templates.

In a previous blog on R12 Payments and eText templates, I built the following text file using an eText template:



Here is a XSL-TEXT template to build the same text file using the same R12 Payments XML data source:



A simple XSLT example... however, the point is that I can reference any XSL element, XPATH or extended SQL function directly. I have complete control over the layout of the text file being created.

Here are the source files:

Sample XML Data File
XSL-TEXT template

I built and tested this example using the BIP Enterprise Server (but this works equally well from Oracle E-Business Suite.)

To test this example using the BIP Server:

1) Create a new report and register the XML file as a data source:



2) Upload the XSL-TEXT layout template:



3) View the final transformed output:

BIP XSL-TEXT Templates : Part 1

Just a quick one from me today as I am of to sunny Birmingham for the weekend.

I've blogged a lot recently about ETEXT Templates and how they are used in BI Publisher to generate text files for EDI and EFT:

ETEXT Part 1

ETEXT templates are a great way of creating simple fixed or delimited text files and the Oracle Payments team have delivered loads of predefined ETEXT templates for Payments processing.

However, they are not the only way to generate text files from BI Publisher!

It is also worth considering XSL-TEXT templates.

I've found that XSL-TEXT templates are really just XSLT scripts that transform the XML into text output only.

Note:
xsl:text is actually an element or command of XSLT to write literal text strings, entity references and #PCDATA to the output file.

If you are scratching your head over what XSLT is... then, a great reference to get you up and running quickly is the W3schools tutorial.

In my next post, I'll walk through some examples of XSL-TEXT templates - but first, I have to go and join the queues of traffic trying to leave London. No fun at 5pm on a Friday night!

Regrouping XML files whilst developing the RTF layout

The structure of the seeded XML file for the R12 EBS Subledger accounting (SLA) report 'Actual Journal Entries' is pretty flat.

Element 'JE_LINES' is the parent of all other GL Journal elements:




My requirement is to build a report of all journal entries grouped by Period Name and Journal Category Name.

At first glance, it looks like building this hierarchial report using the existing SLA XML source would be pretty tricky.

Luckily, BI Publisher has some great functionality for regrouping XML files into new hierarchies whilst you are building the report layout.

I can make any element a parent of all other elements at the same level . I can reorganize the 'Actual Journal Entries' hierarchy so that all elements at the same level as 'PERIOD_NAME' are now its children i.e. the hierarchy is:



Also, I can nest groups within groups and make the hierarchy as complex as necessary to fulfill my requirement:



This makes it a lot easier to control the layout, implement totalling and summations
AND re-use many existing data sources WITHOUT additional customisation.

That is always good news :-)

To implement regrouping in your RTF template, either:

1) Use the following functionality of the BI Publisher Desktop Add In:

-- Regroup whilst using the table wizard

Here I am grouping by Period_Name and then, Je_Category_Name:



-- Regroup whilst inserting a repeating group:

Here I am regrouping by Period_Name only:



2) OR Use the regrouping syntax directly in your template:



i.e to regroup by period_name:



And the syntax for nesting groups is:



i.e. to group by JE_CATEGORY_NAME within the PERIOD_NAME group:




The result:

Based upon the flat XML structure, my finished RTF template includes all of the required hierarchies:




Note: You can also :
- Regroup by the results of expressions. Syntax =

Padding eText files with blank lines

MikeM asked:
"How to pad an eText template with blank lines so the text file entries end up on the correct position on the stationary?
e.g. There are 25 lines available on the stationary for invoices but only 5 invoices to be paid on this payment - what steps should be taken to insert 20 blank lines after the 5 invoice lines?"


The solution that I use is to add an additional dummy Extend tag for each required blank line to the Payments Disbursement file. These are created under the 'OutboundPayment' parent.

You can add new tags to the Payments file by calling PL/SQL package 'IBY_FD_EXTRACT_EXT_PUB'. See previous blog post: http://kwoodrow.blogspot.com/2008/06/part-1-adding-new-fields-to-r12.html


After adding the new tags, the resulting XML file should look something like:
(Note - in this case, there are 3 blank lines added to the file)




Then, in the eText template, under the 'OutboundPayment' level, create a new record to add these blank lines to the file:
(Note - this is highlighted in yellow)




Finally, test the template using the BIP Template Viewer:




Anyone got a different solution?

Previewing and testing eText templates

Just a quick post today.

I've had a few emails asking how we test eText templates.

Rather than using the Word Plug in to preview the result, you test eText templates using the 'Template Viewer' included with the BIP desktop toolkit.

From the Windows Start menu, click on : All Programs, Oracle BI Publisher Desktop, Template Viewer:



Once the Template Viewer is open:
- select your working directory i.e. the folder containing your eText template and preview XML data
- change the output format to eText :



and then click on the button 'Start Processing' to view the generated text file with Unix / Linux line endings:

ETEXT Templates for Check Printing - Part 3

In the first 2 blogs in this series, we completed:

Point 1)- Define the fixed position Check file required by the third party software AND
Point 2)- Map our required text file against the standard Oracle Payments Disbursement XML file.

OK - let's get to the meat of the solution.... building the actual ETEXT template.

Point 3)- Build an ETEXT template according to the structure defined in point 1 and based upon the XML file from point 2.


ETEXT templates are used to generate Fixed or delimited text files from BI Publisher.

All commands are created within a RTF document( i.e. MS Word).

ETEXT templates start with global commands and then have line commands for each line required in the file and field commands for each individual field within the line i.e. to produce our 3 line text file from Part 2:

NCKEVIN WOODROW 01-JUN-20081872 1000,000.00

12 HOLLYWOOD BLVD LOS ANGELES

CALIFORNIA 90210

The ETEXT template structure is:


- GLOBAL FILE COMMANDS
--- NEW LINE COMMANDS
------ FIELDS WITHIN LINE COMMANDS
--- NEW LINE COMMANDS
------ FIELDS WITHIN LINE COMMANDS
--- NEW LINE COMMANDS
------ FIELDS WITHIN LINE COMMANDS


GLOBAL FILE COMMANDS:
These are the commands and parameters that affect the whole text file being produced. This includes:
- TEMPLATE TYPE: the type of text file being created i.e. Fixed or delimited
- OUTPUT CHARACTER SET: the appropriate character set
- NEW RECORD CHARACTER: the character used to mark the end of a line and the start of a new line i.e. carriage return

NEW LINE COMMANDS:
These commands affect the line as it written to the text file i.e.
- LEVEL: the corresponding parent tag or level within the XML file.
- DISPLAY CONDITION: any conditions that must be met before the line is written
- NEW RECORD: the addition of a new line marker to the file


FIELDS WITHIN LINE COMMANDS: These commands add each individual field to the current line. They include: - POSITION: the starting position of the field on the line(for Fixed position files) - LENGTH: the length of the field - PAD: any required padding of the field - DATA: the source of the field i.e. a default value or an element fromthe XML file

For greater explanation, check out Chapter 4 of the BIP User Guide.

OK - that's the talk finished!!!

Here is an example ETEXT template and Payments XML file to build the 3 line text file specified in Point 1 in Part 2 of this blog series:

XML Sample

ETEXT Template

Use the 'Template Viewer' supplied with Oracle BI Publisher desktop to test them for yourself.

OK, I admit that it is a simple example - but I hope that it is just enough to get you started with your own template!!!

  As always, let me know :-)