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:

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.

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