How to create Comma Separated (CSV) files using eText Templates

By Jag - April 04, 2014

Creating a comma delimited file/output in R12 is an easy process considering that not much effort goes into creating such template given the understanding of the eText templates. E-text outbound templates provide the ease of just placing right columns in right place and you experience the joy of getting comma separated files as output that can be saved to desktop.
Such requirement is often experienced in Accounts Payable application where the payment data is extracted to file either in Fixed Length/Position based or Delimiter based format. Here, we shall look at process of creating delimiter based templates which provided delimiter based text output.

Let us take a look at the business requirement.
The user formats payments in Payables and wants to view the output in csv format, save the output as a txt file in CSV format to his/her desktop. So a CSV format payment file must be generated whenever a payment is formatted (Format Payment Instructions concurrent program is run in the background). The user also wishes the luxury of viewing the CSV format payment file from the standard program,Format Payment InstructionsView Output, option.
There are certain points that must be observed here.
  1. To display the payment data that user wishes using e-text template requires certain configurations.
  2. Viewing the payment data in csv format from the standard program, Format payment Instructions, would require that the standard program must use a custom template that would give the output in csv format.
  3. The template must read xml data that contains the payment details that only user wants displayed in the csv format file.
For Point 1, a template has to be created that gives output in csv format.
Oracle provides standard delimiter based templates that can be modified as per the requirement and effort in creating an E-text outbound template from scratch is not required.
Step 1: Write code to generate the XML file
We now have to write code to generate the payment XML file. As the template requires data in xml format to process and display as output in CSV format, Oracle has provided a standard package that helps you pass the data to the Format Payment Instructions program in XML format.
The “IBY_FD_EXTRACT_EXT_PUB” package is a standard package that contains multiple functions. These functions return the data at payment instruction level, payment level, payment document(invoice) level, payment document line(invoice line) level.
This package contains functions that have no code by default but code can be added as per our requirements to generate the payment file. More information on this package can be found here.
As I want my file to contain one line for each payment and only one file for each payment process (payment batch), I shall use the functionGet_Ins_Ext_Agg. This function returns payment data at payment instruction level.
The above screenshot shows that the function takes payment instruction id as parameter. Now all I need to do is write a query by passingp_payment_instruction_id as parameter to get payment details for a particular payment instruction or payment batch.
Below is a sample code for function, Get_Ins_Ext_Agg. It returns a few payment fields usually displayed in a payment data file as xml data.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
FUNCTION get_ins_ext_agg (p_payment_instruction_id IN NUMBER)
RETURN XMLTYPE
IS
   l_payment_xml_csr          sys_refcursor;
l_payment_xml_data         XMLTYPE;
   l_dest_country             VARCHAR2 (10)  := NULL;
   l_debit_acc_id             VARCHAR2 (100) := NULL;
   l_payment_curr             VARCHAR2 (10)  := NULL;
   l_payment_amt              NUMBER         := 0;
   l_payment_date             VARCHAR2 (20)  := NULL;
   l_bic                      VARCHAR2 (100) := NULL;
   l_bene_acc_num             VARCHAR2 (100) := NULL;
   l_bene_name                VARCHAR2 (500) := NULL;
   l_credit_curr              VARCHAR2 (10)  := NULL;
   l_payment_instruction_id   NUMBER         := 0;
BEGIN
   BEGIN
      l_payment_instruction_id := p_payment_instruction_id;
      OPEN l_payment_xml_csr FOR
         SELECT ieba.country_code dest_country,
                   ip.int_bank_branch_number
                || ip.int_bank_account_number debit_acc_id,
                ip.payment_currency_code payment_curr,
                ip.payment_amount payment_amt, ip.payment_date payment_date,
                ip.ext_eft_swift_code bic,
                ip.ext_bank_account_number bene_acc_num,
UPPER (SUBSTR (ip.payee_name, 1, 35)) bene_name
           FROM iby_payments_all ip, iby_ext_bank_accounts ieba
          WHERE 1 = 1
            AND ip.payment_instruction_id = l_payment_instruction_id
            AND ip.external_bank_account_id = ieba.ext_bank_account_id(+);
l_payment_xml_data := XMLTYPE (l_payment_xml_csr);
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                               'Unable to fetch the XML data for payments'
|| SQLERRM
                           );
   END;
   RETURN (l_payment_xml_data);
END get_ins_ext_agg;
Below is an example of the XML data returned by the function, Get_Ins_Ext_Agg:
1
2
3
4
5
6
7
8
9
10
11
12
<ROWSET>
   <ROW>
      <DEST_COUNTRY>GB</DEST_COUNTRY>
      <DEBIT_ACC_ID>32143900876354</DEBIT_ACC_ID>
      <PAYMENT_CURR>GBP</PAYMENT_CURR>
      <PAYMENT_DATE>02102013</PAYMENT_DATE>
      <PAYMENT_AMT>100</PAYMENT_AMT>
<BIC>309268
      <BENE_ACC_NUM>12345678</BENE_ACC_NUM>
      <BENE_NAME>TEST BENE NAME</BENE_NAME>
   </ROW>
</ROWSET>
The ROWSET and ROW tags are attached by the function XMLTYPE () when converting the rows into XML type. These tags can be used for grouping in eText template.
Compile the package, IBY_FD_EXTRACT_EXT_PUB.

Step 2: Retrieve the seeded template
Login to Oracle Applications and navigate to XML Publisher Administrator responsibility where in you can query for an E-text outbound template. An example of such delimiter based e-text outbound template is “EDIFACT PAYMUL Format“.
The standard RTF, IBYDE_PYML_en.rtf, attached to above mentioned template can be used as an example.
Log into XML Publisher Administrator, go to the Templates tab. Search for Template name = EDIFACT PAYMUL Format.
eText template
Download the seeded template. The template is given below.
Note: The template is in *.docx format as the site does not allow *.rtf format.

Step 3: Create the custom template
We are going to create a custom template so that when Format Payment Instructions program is run, it is  picked up to show the desired output.
Register the template as you have seen in Step 2.
template screenshot
Save the template. The eText template is given below.
Note: The file format is in *.docx instead of *.rtf as the site does not allow uploading *.rtf. You may save the file as *.rtf to use the file in Oracle.
Important: Create the custom template in Payments Application only and use the seeded data definition,”Oracle Payments Funds Disbursement Payment Instruction Extract 1.0” as Data Definition for the custom XML Publisher Report you are building.
——————————————————————————————————
How to create the eText template
We shall now discuss the internals of the eText template. An RTF template can be made once the package is modified to return the payment details in XML format.
The standard template (IBYDE_PYML_en from Step 2) contains few tables that are important when displaying fields.
  • The first table for Format Setup will contain details about the template type.
    Format Setup:
    Hint: Define formatting options…
<TEMPLATE TYPE>DELIMITER_BASED
<OUTPUT CHARACTER SET>iso-8859-1
<NEW RECORD CHARACTER>Carriage Return
The above details make it a delimiter based template.
The next table for Format Data Records is where the data fields are mapped along with the delimiter (comma) as shown below.
  • Format Data Records:Hint: This is the body of the format. Define your format records here.Create one table for each record or group of records that are at the same level.
<LEVEL>ROW
<MAXIMUM LENGTH><FORMAT><DATA><TAG><DESCRIPTION>
<NEW RECORD>FileHeaderRec
10AlphaDEST_COUNTRYT007Destination Country
1Alpha‘,’Data Element Separator
100AlphaDEBIT_ACC_IDT010Debit account number
1Alpha‘,’Data Element Separator
3AlphaPAYMENT_CURRT013Payment Currency
1Alpha‘,’Data Element Separator
20NumberPAYMENT_DATET014Payment Date
1Alpha‘,’Data Element Separator
20AlphaPAYMENT_AMTT015Payment Amount
1Alpha‘,’Data Element Separator
50AlphaBICT022BIC/Swift code
1Alpha‘,’Data Element Separator
50AlphaBENE_ACC_NUMT028Beneficiary Account Number
1Alpha‘,’Data Element Separator
100AlphaBENE_NAMET030Beneficiary Name
<END LEVEL>ROW
Template Glossary
TagExplanation
<LEVEL>This tag can be considered as group at which the loop occurs. Giving value as “ROW” would indicate that the data is looped at ROW tag in XML data.
<MAXIMUM LENGTH>Specifies the maximum length for the data field.
<FORMAT>Specifies the data type of each field mentioned in the table.
<DATA>Specifies the column name in the XML data that is mapped in the template.
<TAG>Can be used to provide comments and readability of the template.
<DESCRIPTION>Can be used to provide comments and readability of the template.
<NEW RECORD>Specifies at which point the template must go into next line. “FileHeaderRec” can be used to specify that the next line would happen at the end of the table at which point the template would go into loop as the was given as “ROW” tag from the XML data.
<END LEVEL>Specifies the end of the template table which means when the entire XML data is displayed, the template processing ends.
Test the modified template in MS Word
When the template is ready, one easy way to test the output is by passing the XML data to the RTF template file. eText output cannot be viewed in conventional manner by loading xml data and clicking the view PDF button in MS word. A jar application “tmplviewer” can be used to view the output. This application is located in your BI Publisher installation folder. E.g. (C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\TemplateViewer).
Steps to test the template
  • Place the xml file and rtf file in one folder.
  • Navigate to the folder using “Browse” button and you can see the xml file in left section and rtf file in right section.
  • Click and highlight both the files and give the Output Format as eText.
  • Press the “Start Processing” button.
  • The template is processed and notepad file opens that contains the data in CSV format as shown below.
——————————————————————————————————

Payables configuration
Configuration plays a very important part. This is because configuration directs Oracle to pick up the custom programs, templates through the seeded programs/packages. Let us check the setup of the seeded program, Format Payment Instructions.
Concurrent program:
Concurrent executable:
Whenever a payment is processed, Format Payment Instructions, concurrent program is run the program invokes theIBY_FD_EXTRACT_EXT_PUB package to generate the disbursement data in xml format. This XML file is passed to the assigned template for EFT Payments.
We shall create the following,
  1. Payment Format
  2. Payment Process Profile
Payment Format
Responsibility: AP Manager/Super User
Navigation: Setup > Payment > Payment Administrator
Click on Formats > Go To Task
On clicking
Search for Format Name, Electronic Payment File – SEPA.
Payment format creation
Click on the format
Payment format creation 2
Click on Update button
Attach template to format
Check the fields, Data Extract and XML Publisher Template, on the top right (expanded image below)
Attach template to format 2
The Data Extract program is what we have discussed earlier (Step 2) and the template is the one we have created (Step 3)in the previous steps.
When a payment process request (payment batch) is processed, the above Payment Document can be specified and when the payment is formatted, Format payment Instructions, program will pick the eText template for processing the output.

Payment Process Profile
Create a new payment process profile
Responsibility: AP Manager/Super User
Navigation: Setup > Payment > Payment Administrator
Click on Payment Process Profiles > Go To Task
On clicking
Search for Name = %SEPA%
PPP
Click on the selected Payment Process Profile.
Go to Payment Instruction Format tab
Payment Instruction Format
Note the value for Payment Instruction Format (expanded image below).
Payment Instruction Format Detail screenshot
The value is Electronic Payment File – SEPA. This is the payment format created for the disbursement (verified above).
This Payment Process Profile in Payment Administrator setup is attached to a Payment document created for EFT payments.
Generate the file
Now when a payment is processed, “Format Payment Instructions” concurrent program is run, above mentioned standard function is called and xml data is returned to the assigned template for EFT Payments.
The user can click on the above concurrent program output and view the output in CSV format and also save it to the desktop as txt file. Below is an example of how the output looks like
The user has saved the output as a “Text File”

Tag:  how to create comma separated csv file
comma separated values csv file
csv comma separated values free download
csv comma separated excel
csv file comma delimited
csv comma delimited file format
  • Share:

You Might Also Like

0 comments