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

ETEXT Templates for Check Printing - Part 2

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

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

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

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

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