Oracle ERP and Business Intelligence Blog

Discoverer 11G Web Services

2009-07-02T02:50:00.000-07:00

Great to read about the Oracle Discoverer Web Services that have been released for public consumption as part of Discoverer 11G and Fusion Middleware 11G:

Here and Here

To quote from the User Guide:

The Oracle BI Discoverer Web Services are part of an Application Programming Interface (API) that enables a client to do the following:
- Obtain Discoverer connections, workbooks, and worksheets
- Execute worksheet queries
- Obtain worksheet content using the SOAP protocol (version 1.1 with JAX-WS/document wrapped format)

I've worked on a number of sites that have made a big investment in Discoverer reporting. It will be interesting to see how this new functionality will be used in the future.

BI Lessons Learnt

2009-05-27T03:03:00.000-07:00

Spent an interesting day at a potential customer recently discussing:

- The lessons that I've learnt on recent Oracle BI projects

- How we could apply these lessons to develop an optimum OBIEE rollout strategy.

In no particular order, some of the key lessons that I highlighted and discussed in greater depth were:

1) Releasing too much functionality at once can overwhelm users and support processes.

2) Requirements for Reports and dashboards evolve as users gain more understanding of the tool capabilities.

3) Involve Users as early as possible in Development process.

- Especially if requirements are unclear or poorly understood.

- Report and Dashboard Prototyping often works well.

4) Ensure End Users understand OBIEE Report and Dashboard functionality and capabilities before the Design process starts e.g.

- View selectors, Column Selectors, Dashboard prompts, Delivery options, BI Publisher integration, and so on

5) Develop organization wide Dashboard and Report 'Look and Feel' standards and enforce them.

6) Review quality of data model regularly: e.g. ensure consistent mappings, conforming dimensions, and so on.

7) System performance and perceived 'acceptable' response time is key to end user acceptance.

8) Maintain a library of existing and re-usable solution.

9) End User Training should be focused and timely.

Reviewing my lessons learnt led on to a debate on iterative and agile development methodologies and how they could be best applied in an OBIEE implementation. I'll post some more on that on a future date.

Migrating a Discoverer EUL to OBI EE metadata

2008-08-07T04:16:00.000-07:00

OBI EE 10.1.3.4 has been released today on OTN.

One of the areas of new functionality that I am very interested in is the ability to migrate certain components of Discoverer metadata to BI Server / Answers.

The New Features documentation states:

Release 10.1.3.4 includes a utility to accelerate the migration of Oracle BI Discoverer metadata (in the form of an EEX file) to Oracle BI Enterprise Edition Plus metadata (in the form of an RPD file). The utility is a command line executable file that can be found in the \OracleBI\Server\bin directory. An accompanying instruction document can be found in the \OracleBI\Server\Document directory. This release migrates only Discoverer metadata and only for relational data sources.

I'll be examining and hopefully posting further on the migration process in the next few days.

OBI EE and .NET Framework V2

2008-07-30T07:09:00.001-07:00

As promised to Chetan... I stumbled across a frustrating incompatibility whilst installing OBI EE 10.1.3.3.3 on a Windows machine earlier.

OBI EE requires Version 2 of MS .NET Framework.

During OBI EE installation, it also launches the installer for Microsoft .NET Framework V2.
However, if you already have a later version of .NET Framework loaded, the .NET Framework install will end with an 'Incompatibility' error.

The OBI EE installation completes successfully.

However, it fails to deploy the analytics application to the OC4J instance correctly.

My workaround was to deinstall the later .NET Framework from the machine and then, restart the OBI EE installation process.

Let me know if you have a better solution!

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

2008-07-25T10:25:00.000-07:00

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

2008-07-08T04:46:00.000-07:00

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

2008-07-04T08:22:00.000-07:00

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

2008-06-30T10:51:00.000-07:00

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

2008-06-23T09:38:00.000-07:00

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

2008-06-23T06:53:00.000-07:00

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

2008-06-20T05:18:00.000-07:00

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 :-)

ETEXT Templates for Check Printing - Part 2

2008-06-17T03:14:00.000-07:00

As explained in my last post, many standalone check print solutions integrated with Oracle E-business suite require only a fixed position text file of check data.

The third party software takes care of the layout and presentation of the document.

In order to fulfil this requirement, we have to:

1)- Define the structure of the fixed position Check file required by the third party software.
2)- Understand the XML file created for Payment Disbursements.
3)- Build an ETEXT template according to the structure defined in point 1 and based upon the XML file from point 2.
4)- Set up Oracle Payments to use our custom ETEXT template for Check printing.

OK - let’s look at each of these points:

1)- Define the structure of the Check file required by the third party software:

Before I start to build my template or write any code, I like to map the required file structure completely i.e. define what records appear on each line, their relative positions, length and any padding that is required.
Note: You may need to liaise with your third party vendor or check their documentation to complete this exercise!

I recommend creating a table with at least the following information:

- Line Num: the line number in the text file
- Field Name: the name of the attribute in the file
- Position From: the position number where the attribute value starts on the line
- Field Length: the length of the attribute
- Padding character: any padding character if the attribute length is less than the field length
- XML File Source: the source tag value from the Payments Disbursement XML file
- Default Value: the default value of the field

For example, take the following extract from a fixed position check file:

NCKEVIN WOODROW                                     01-JUN-20081872                1000,000.00

12 HOLLYWOOD BLVD                                 LOS ANGELES

CALIFORNIA                                        90210

The structure of this file is:

Line Num	Field Name	Position From	Field Length	Padding Char
1	New Cheque Indicator	1	2	none
1	Payee Name	3	50	' '
1	Payment Date(DD-MON-YYYY)	53	11	none
1	Check Number	64	20	' '
1	Check Amount(####,###.00)	84	11	' '

2	Address Line 1	1	50	' '
2	Address Line 2	51	50	' '

3	Address Line 3	1	50	' '

......and so on

Trust me - if you take time to complete this exercise, building the ETEXT template will be very simple!!

2)- Understand the XML file created for Payment Disbursements:

The BI Publisher data template for Payment Disbursements is:
- Name: Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
- Code: IBY_FD_INSTRUCTION_1_0

In previous blog post, I outlined the structure of the XML file created and how to add custom fields to the XML file produced:

http://kwoodrow.blogspot.com/2008/06/part-2-adding-new-fields-to-r12-payment.html

This file is also well documented in Appendix K of the Oracle Payments Implementation Guide (Part Num: B28872-03).

OK - Here is my table from Point 1 updated with the data source from the XML file:

Line Num  Field Name                 XML Data Source (XPATH syntax)
--------  -------------------------  ------------------------------
1         New Cheque Indicator     
1         Payee Name                 OutboundPaymentInstruction/OutboundPayment/Payee/Name
1         Payment Date(DD-MON-YYYY)  OutboundPaymentInstruction/OutboundPayment/PaymentDate
1         Check Number               OutboundPaymentInstruction/OutboundPayment/PaymentNumber/CheckNumber
1         Check Amount(####,###.00)  OutboundPaymentInstruction/OutboundPayment/PaymentAmount

2         Address Line 1             OutboundPaymentInstruction/OutboundPayment/Payee/Address/AddressLine1
2         Address Line 2             OutboundPaymentInstruction/OutboundPayment/Payee/Address/AddressLine2

3         Address Line 3             OutboundPaymentInstruction/OutboundPayment/Payee/Address/AddressLine3

Now we know the exact structure and data source of the text file required building the ETEXT template will take no time!

I'll cover that tomorrow.

ETEXT Templates for Check Printing - Part 1

2008-06-13T02:48:00.000-07:00

There has been a few questions on the BI Publisher OTN forums recently around creating cheque (check) print files in ASCII format:
http://forums.oracle.com/forums/thread.jspa?threadID=637081&tstart=0

This is because many 3rd party cheque printing solutions (e.g. Bottomline / Paybase) only require an ASCII file of cheque data. The 3rd party software then takes care of the layout of the cheque, signatures and security markings etc.

Unfortunately for us, the Oracle Payments team have only delivered BIP cheque printing templates in RTF format.

If you try and set the output type to TEXT for these, the resulting file will be full of pesky control characters and other such symbols.

Trust me, however hard you try... your standalone check printer is going to spit this file out!

Don't panic!!! There is a solution - ETEXT templates.

ETEXT templates were originally developed for creating fixed position or delimited format Text files for EFT and EDI transmission.

However, their uses should NOT be limited to EFT and EDI.

WHENEVER you need to generate a fixed or delimited text file from an XML Publisher data source, think 'ETEXT'.

You define the structure, format and logic of your TEXT file in RTF format (i.e. using Microsoft Word) and when this is applied against your XML data file, the appropriate TEXT file is produced.

In my next post, I'll take you through the steps to build an ETEXT template for cheque printing.

Before that, take a look at my previous posting on adding new fields to the R12 Funds Disbursment XML file:
http://kwoodrow.blogspot.com/2008/06/part-2-adding-new-fields-to-r12-payment.html

It is important to understand the 4 level structure of the Payments XML document in order to correctly build the ETEXT file.

Formatting Dates passed from EBS to XML Publisher Templates

2008-06-09T06:25:00.000-07:00

I often get asked by Template Designers how to change the format mask of the date values passed from EBS data templates.

It seems pretty obvious until you discoverer that the seeded Value set for concurrent program date validation 'FND_STANDARD_DATE' returns a date in the XML file with the format YYYY-MM-DD hh:mm:ss+HH:MM e.g 2008-06-09 00:00:00.0

XML Publisher is expecting the date to be in the format: YYYY-MM-DDThh:mm:ss+HH:MM e.g 2008-06-09T00:00:00.0

Notice, that pesky 'T' separator between the date and time component in the second date!!!

A post from Tim back in 2006 nicely explains this issue: blogs.oracle.com/xmlpublisher/2006/11/03

He recommends using the following sql syntax for coding dates directly in your data template:

to_char(TRX_DATE, 'YYYY-MM-DD"T"hh:mm:ss+HH:MM')

where TRX_DATE is the date field being selected

This works well if you can change the data template. However, there are many cases in EBS where the data templates cannot be viewed or modified.

One solution that I have found is to add the 'T' seperator to the date field in the Template e.g.

Create a new form field in your template using the code:

?xdofx:replace(TRX_DATE,' ','T')?

where TRX_DATE is the tag name of the date field.

You can then happily format the date in your template using a standard Microsoft format mask!

Anyone got another solution?

Part 2: Adding new fields to the R12 Payment Funds Disbursement XML file

2008-06-06T05:37:00.000-07:00

IBY_FD_EXTRACT_EXT_PUB is a standard PL/SQL package that is used to extend (i.e. add additional tags to) the XML file generated during a R12 Oracle Payments ‘Payment Process Request’:

This XML file is then used as the data source for the XML Publisher cheque or electronic file presentation layout.

To understand how to use IBY_FD_EXTRACT_EXT_PUB, we have to understand the structure of the XML file created by the Payments process request.

There are 4 main levels to the file. These are:

Top Level: Outbound Payment Instruction
This is the top level of the XML File and there is one Outbound Payment Instruction per Payment process request.

Level 2: Outbound Payment:
This is the Payment Level i.e. an individual cheque or BACS payment amount to a supplier. There can be multiple Outbound Payments per Outbound Payment Instruction.

Level 3: Document Payable:
Details the documents (i.e. invoices) being paid. There can be multiple Document Payable tags per Outbound Payment

Level 4: Document Payable Line:
This level details the invoice line. There can be multiple Document Payable Line tags per Document Payable.

Here is an example Payments file viewed using XML notepad:

Additional XML tags can be added at each of these 4 levels by coding different PL/SQL functions in IBY_FD_EXTRACT_EXT_PUB.
The following table lists the functions you need to modify to add additional tags to each level of the XML file:

XML File Level	IBY_FD_EXTRACT_EXT_PUB Function To Modify	Example of Parameter Usage
OutboundPaymentInstruction	Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER)	SELECT * FROM iby_pay_instructions_all WHERE payment_instruction_id = p_payment_instruction_id;
OutboundPayment	Get_Pmt_Ext_Agg(p_payment_id IN NUMBER)	SELECT * FROM iby_payments_all ipa WHERE ipa.payment_id = p_payment_id;
DocumentPayable	Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER)	SELECT * FROM iby_docs_payable_all dp WHERE dp.document_payable_id = P_document_payable_id;
DocumentPayableLine	Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER)
PaymentProcessProfile	Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER)	SELECT * FROM iby_pay_service_requests WHERE payment_service_request_id = p_payment_service_request_id;

As shown in the example below, it is best practice to group all of your custom tags within a parent tag called 'Extend'

EXAMPLE:

The following is an example of adding an 'Extend' tag and some additional invoice data to the ‘DocumentPayable’ level of the XML file:

--
-- This API is called once per document payable.
-- Implementor should construct the extract extension elements
-- at the document level as a SQLX XML Aggregate
-- and return the aggregate.
--
FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER)
RETURN XMLTYPE
IS
v_att1 ap_invoices_all.attribute1%TYPE;
v_att2 ap_invoices_all.attribute2%TYPE;
v_att3 ap_invoices_all.attribute3%TYPE;
v_detail_line VARCHAR2(255);
v_result XMLTYPE;
BEGIN

SELECT ai.attribute1,ai.attribute2,ai.attribute3
INTO v_att1, v_att2, v_att3
FROM iby_docs_payable_all dp,ap_invoices_all ai
WHERE dp.calling_app_doc_unique_ref2 = ai.invoice_id
AND dp.document_payable_id = p_document_payable_id;

v_detail_line := xxcsd_build_inv_line(p_document_payable_id);

--now build the XML string
SELECT XMLConcat(XMLElement("Extend",
XMLElement("XXCSD_INVOICE_DFF1", v_att1),
XMLElement("XXCSD_INVOICE_DFF2", v_att2),
XMLElement("XXCSD_INVOICE_DFF3", v_att3),
XMLElement("XXCSD_INV_DETAIL", v_detail_line)
)
)
INTO v_result
FROM dual;

RETURN v_result;

END Get_Doc_Ext_Agg;

As you can see, I’ve added 4 new tags ‘XXCSD_INVOICE_DFF1’, ‘XXCSD_INVOICE_DFF2’, ‘XXCSD_INVOICE_DFF3’ and ‘XXCSD_INV_DETAIL’ to the XML File:

These new XML tags can now be referenced in my XML Publisher presentation template and added to my cheque or electronic payment file layout.

For example, here is the tag ‘XXCSD_INV_DETAIL’ used in an ETEXT template:

Part 1: Adding new fields to the R12 Payment Funds Disbursement XML file

2008-06-03T08:47:00.000-07:00

Hands up if you or someone you know has customised the standard 11i (or earlier) Format Payments program (APXPBFOR.rdf) to add additional fields, change the cheque / check layout or something similiar??

Now, hands up if you have customised the RDF for your 11i BACS or electronic paymentfile??

Crikey… there are a lot of hands up out there.

In fact, I also have worked on lots of sites in the past where we have ‘tweaked’ the content and layout of these seeded 11i payment documents. For example, on my current project, there is a requirement to add some descriptive flexfield data from the invoice record (ap_invoices_all)
onto the cheque.

Well, the good news is…
From R12, the Payments module has replaced the previous Payables functionality and has reimplemented all the templates using XML Publisher :-)

And the bad news is…
From R12, the Payments module has replaced the previous Payables functionality and has reimplemented all the templates using XML Publisher :-(

OK – that was a poor attempt at humour – but from the technical and work-load perspective, the upgrade to Payments really is a double edge sword:
You get all of the great XML Publisher functionality BUT you have to re-implement any customisations to your payment instructions!

Luckily for us, Oracle has made it relatively easy to configure the R12 payment instructions and add new fields, content and layout. They have provided:

1) - A PL/SQL Package ‘IBY_FD_EXTRACT_EXT_PUB‘ to add additional tags to the XML file created during the Discbursement Process.
2) - Loads of RTF and ETEXT presentation templates for BACS, cheque printing and other payment types.

In my next post, I’ll take you through the steps to add new fields and tags to the XML file created by the XML Publisher data definition ‘Oracle Payments Funds Disbursement Payment Instruction Extract 1.0’.

XDOLoader Part 2 - UPLOAD syntax

2008-05-30T09:01:00.000-07:00

Yesterday, I ran through the DOWNLOAD syntax of XDOLoader to download XML Publisher templates and data definitions to a local machine.
OK - I admit that having the file download syntax without the corresponding UPLOAD syntax is not much use! So, let's take a closer look at the UPLOAD syntax:

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-XDO_FILE_TYPE \
-NLS_LANG \
-FILE_NAME \
-CUSTOM_MODE [FORCENOFORCE] \
-LOG_FILE

I'll only describe those parameters that are different from yesterday:

XDO_FILE_TYPE: The XML Publisher Type i.e. PDF, RTF, XLS, XSL-FO, XSL-HTML, XSL-XML,
XSL-TEXT, XSD, XML, RTF-ETEXT
NLS_LANG: The value of your NLS_LANG environment variable e.g. American_America.WE8ISO8859P1
FILE_NAME: The name of XML Publisher File to upload
CUSTOM_MODE: Set this to FORCE to update an existing template

UPLOAD EXAMPLE:

Here's a shell script that I use to upload files:
------------------------
# Get parameters
apps_psw=$1
jdbc_con=$2
app_short_name=$3
lob_code=$4
filename=$5
filetype=$6
#Upload XML Publisher Template
% java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_psw \
-JDBC_CONNECTION $jdbc_con \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME $app_short_name \
-LOB_CODE $lob_code \
-NLS_LANG American_America.WE8ISO8859P1 \
-LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE $filetype \
-FILE_NAME $filename \
-CUSTOM_MODE FORCE \
-LOG_FILE xdotmpl.log
------------------------

ADDITIONAL NOTES:

1) You must set your APPL_TOP and CLASSPATH before running the XDOLoader
2) Don't forget to FNDLOAD the XML Publisher metadata using xdotmpl.lct before uploading the XML Publisher template or data template. Without this, you wont be able to view the file uploaded from the XML Publisher Administrator responsibility. I'll leave that syntax for you to look up ;-)

XDOLoader Part 1 - XML Publisher DOWNLOAD mode syntax

2008-05-29T07:11:00.000-07:00

It surprises me how few people are aware of the generic loader (XDOLoader) for XML / BI Publisher (BIP) files.

Like FNDLOAD for AOL / XML Publisher metadata and WFLOAD for Workflow Item Types, XDOLoader is is a command line utility that allows you to migrate the BIP files between environments. The following BIP file types can be upload and downloaded:

- TEMPLATE
- XML_SCHEMA
- XML_SAMPLE
- DATA TEMPLATE

Call XDOLoader in DOWNLOAD mode to create a local copy of the relevant object.
Call XDOLoader in UPLOAD mode to load your local definitions into the database.

Here's the DOWNLOAD syntax and some examples for Oracle E-Business suite users:

DOWNLOAD MODE SYNTAX:

% java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-LOG_FILE \
-DEBUG

KEY (for E-Business suite Users):

-DB_USERNAME: apps username for the target instance
-DB_PASSWORD: apps password for the target instance
-JDBC_CONNECTION: JDBC connection string (e.g. server:port:sid)
of the target instance
-LOB_TYPE: The XML Publisher object type.
One of 'TEMPLATE', 'XML_SCHEMA', 'XML_SAMPLE' or 'DATA TEMPLATE'
-APPS_SHORT_NAME: Application short name used to register the XML Publisher object
-LOB_CODE: Template code i.e. concurrent program short name
-LANGUAGE: ISO language code(i.e. en) used when registering the object
-TERRITORY: ISO language code(i.e. GB) used when registering the object
-LOG_FILE: Name of the log file. The default filename is xdotmpl.log
-DEBUG: Run in Debug mode. Valid values are 'true' or 'false'

DOWNLOAD MODE EXAMPLES:

This is the simple shell script that I use to download 'GB English' presentation and data templates.
(Note - Change your language and territory as appropriate):

-----------------------------------------
# Get parameters
apps_psw=$1
jdbc_con=$2
app_short_name=$3
lob_code=$4

# Download XML Publisher Template RTF and XSL
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_psw \
-JDBC_CONNECTION $jdbc_con \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME $app_short_name \
-LOB_CODE $lob_code \
-LANGUAGE en \
-TERRITORY GB \
-LOG_FILE xdotmpl.log \
-DEBUG true

# Download XML Publisher dataTemplate
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_psw \
-JDBC_CONNECTION $jdbc_con \
-LOB_TYPE DATA_TEMPLATE \
-APPS_SHORT_NAME $app_short_name \
-LOB_CODE $lob_code \
-LANGUAGE en \
-TERRITORY GB \
-LOG_FILE xdotmpl.log \
-DEBUG true

-----------------------------------------

ADDITIONAL NOTES:

1) Don't forget to download your XML Publisher metadata using the FNDLOAD utility. The control file name is xdotmpl.lct.

More on the UPLOAD syntax tomorrow!

Another Quick Reminder: Linking Discoverer Reports to standard Oracle Applications Menu

2008-05-27T13:32:00.000-07:00

Here are the steps required to link a standard Discoverer workbook to an Oracle Applications menu:

Step	Description	Tool
1	Note the value of the workbook ‘Identifier’. Open the workbook, navigate to ‘File -> Manage Workbooks -> Properties.	Discoverer Plus
2	Create a new Form Function (Application -> Function). Required values: Function: XX???_??? User Function Name: Report Name Description: ??? Type: SSWA plsql function Parameters: Workbook=Step 1 Identifier HTML Call: OracleOasis.RunDiscoverer	System Administrator
3	Link the form function to the appropriate menu (Application -> Menu).	System Administrator
4	Share the business areas used by the workbook with the responsibilities associated with the modified menu	Discoverer Administrator
5	Share the workbook with the responsibilities associated with the modified menu	Discoverer Plus

Associated Profile Options:

Profile Name	Description	Value
ICX_DISCOVERER_USEVIEWER	Determines if Discoverer Viewer or Discoverer Plus is launched when a workbook is opened from a menu	Yes or No

References:

Metalink Note: 278095.1

Quick Reminders: Steps to set up General Ledger security for Discoverer

2008-05-25T04:43:00.000-07:00

Here’s a quick reminder of the steps required to setup GL ledger and segment value security for the seeded Discoverer General Ledger Business Area:

Set the profile option ‘FII: GL BIS Discoverer access’ for all GL responsibilities that will run Discoverer workbooks.
Ensure that the profile option ‘GL Set of Books Name’ (‘GL Ledger Name in R12) has been set at responsibility level for all GL responsibilities that will run Discoverer workbooks.
Ensure that the ‘Security Enabled’ flag is set to Yes for all ‘enabled’ segments of the chart of accounts. (Setup, Flexfields, Key, Segments)
Define a flexfield value set security rule for each segment in the chart of accounts that INCLUDES all possible values in the Security Rule Elements i.e. Include 000000 ZZZZZZ (Setup, Flexfields, Validation, Security, Define)
Assign the value set security rules created in Step 4 to all of the GL responsibilities that will run Discoverer Workbooks (Setup, Flexfields, Validation, Security, Assign)

Normal service resumes

2008-05-24T13:25:00.000-07:00

I've been heads down, hands on and snowed under for the last few months working on an R12 implementation in the UK.

I've been doing some interesting things with Oracle Payments and XML Publisher using E-Text templates to integrate their check printing and BACS extract programs with their BottomLine / Paybase solution. I promise a post on this shortly.

Also, have been developing multiple materialised views for GL Balances, Journal Transactions and Drill down to Sub-ledgers reporting that can be integrated into both a Discoverer business area and the OBIEE BI Server.

I'm off to India (Pune) soon to brush up on my OAF skills. I've never been to India before so am really looking forward to the trip. Am planning to spend a few days in Goa after to relax a.k.a. party ;-) and work on my tan.

Connecting to Discoverer 10G

2007-12-12T02:15:00.000-08:00

Does this sound familiar?

Connecting to Discoverer Administrator or Desktop as an apps user for the first time on a a project and you get the error message:

"Invalid Username and Password"

You have the oracle apps user checkbox ticked and have validated that both the username and password are correct and have checked your TNS Names file.

Rather than shout at the Apps DBA, here is what you need to do:

1) Create the following 2 new environment variables (path=control panel, system, advanced, environment variables) :

FND_SECURE =\secure
FND_TOP=

for me is C:\Oracle\BIToolsHome_1\

2) Rename the application dbc file from the $FND_TOP/secure directory to the name of the connect entry in your TNSNAMES.ORA file.

For example, if your TNSNAMES entry looks like this:
MYDATA.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = myserver.myorg.com)(Port = 1521)) (CONNECT_DATA = (SID = MYDATA)) )

then your dbc file must be named MYDATA.DBC

Happy discovering!

Some people are never happy

2007-09-25T05:31:00.000-07:00

As we move forward with our 'adoption' of BI / XML Publisher here, I've been thinking more about what we really need to get up and running quickly.

It is simple really ;-) At the top of our 'wish list' is:

- A comprehensive library of reusable templates

Forget about those pesky XSL-FO ones, we need easily customizable RTF / MS Word templates for EVERY standard AP, PO, GL, AR and FA Oracle e-business suite report and we need them FAST. Translations would be nice as well :-)

I’m not asking for too much, am I? :-)

I demo'ed some BPA functionality to the project team here yesterday.

They loved:

- The simplicity of the template editor:
-- Adding and deleting items, images and messages
-- Moving regions and contents around
-- Changing item properties

- The Integration with the AR Transactions form.

- The Ease of integrating additional data sources with the standard AR data.

The problem was that the demo was a little too successful! It started a discussion on why some other customer facing documents didn't have similar applications integrated with the core functionality.

As they say 'Some people are never happy' ;-)

Cowboy Coding

2007-09-20T06:09:00.000-07:00

I had to smile when I read this on Wikipedia. I'm sure that we have all met somebody who is an expert in this art:

Cowboy Coding is a software development methodology without an actual defined method – team members do whatever they feel is right. Typical cowboy coding will involve no initial definition of the purpose or scope of the project, no formal description of the project, and will often involve one programmer. It is characterized by a single programmer jumping into the writing of the software often working from his own idea of what the software should do. It is often characterized by a lack of any documentation for either the requirements of the project or the design of the software overall.

Read the rest on wikipedia: http://en.wikipedia.org/wiki/Cowboy_coding

Sleeping easy with 11.5.10 Bill Presentment Architecture - Part 1

2007-09-18T09:11:00.000-07:00

I am currently reviewing a customer's existing AR invoice printing and bill presentation process.

As part of their original implementation, they had customised the standard Reports 6i invoice print program (RAXINV) for invoice printing. This had proved to be a very difficult solution to maintain. Now they were upgrading their Oracle technology stack, they were keen to review different solutions for invoice printing.

Like POXPRPOP, the name RAXINV can strike terror into the heart of many a poor unsuspecting appstechie. I can still recall sleepless nights:
- sweating over all those frames and repeating groups
- counting out the character cells
and plucking up the courage to turn flex or confine mode off :-)

When Oracle released Bill Presentment Architecture or BPA, I am sure that someone in Development was trying to apologise!

Now built upon Oracle XML Publisher technology, BPA is actually quite a simple solution to implement. After only an hour or so, we had developed a very neat prototype for the customer and they are very excited to implement this solution.

In my next post, I'll outline the steps to get started with BPA.

My old friend 'Workflow'

2007-09-14T08:01:00.000-07:00

I read today that Error correction support ended for Oracle Workflow last week. (http://www.oracle.com/technology/products/ias/workflow/workflow_sod.html)

I've been on a number of projects that attempted to use workflow technology in unique ways.

I remember 7 or 8 years ago and before the Oracle Teleservice application was available, I was the Workflow Lead on a project for a large Health Insurance company in California. We were integrating their Customer service application with the Oracle Advance Benefits module using Oracle Workflow and Advanced Queuing.

Today, the SOA suite and BPEL are perfect fits for this requirement but at that time, Oracle Workflow was all we had!

We were lucky that there was a Cheesecake Factory very close to the office in Woodland Hills. I remember fondly the long nights spent there discussing the solution design, intricate workflow processes and of course, eating cheesecake.

So tonight, I'll raise my glass for the end of my old friend 'Workflow'. Now, if only we could get a Cheesecake Factory franchise in London...