Implementation Guide for Oracle Billing Insight > Customizing Payment >

Generating Accounts Receivables (A/R Files)

It is often necessary to synchronize Payment with a biller's A/R software. Payment sends A/R files periodically to a biller's A/R software, which includes the payments being made through Payment. The format of the file varies among billers. To support this function, Payment has the pmtARIntegrator job, which uses a template and XML/XSLT to generate output in a variety of file formats.

The pmtARIntegrator job queries the database to get proper payments, and then writes the payments into a flat file or an XML file using the Payment Template Engine. The XML file can be further transformed into other format by using XSLT.

The default implementation of the pmtARIntegrator job performs the following steps:

1. Queries the database to get a list of check or credit card payments, or both. The query is defined in arQuery.xml file, which finds all the check and credit card payments where the payee_id matches the current job DDN, the status is 8 (paid) and flexible_field_3 is N.
2. Invokes the process() method of the default implementation of com.edocs.common.payment.tasks.ar.IARPaymentIntegrator, which is com.edocs.common.payment.tasks.ar.SampleARPaymentIntegrator. In this method, ARPaymentIntegrator writes the payments into a flat file or XML file using the Payment Template Engine. There are two templates provided by Payment:
   • arFlat_template.txt. Generates a flat A/R file
   • arXML_template.txt. Generates an XML file

     The output file name is: ar_yyyyMMddHHmmssSSS.extension, where extension matches the extension of the template file.

3. Inside the process() method, if the output is an XML file, then SampleARPaymentIntegrator can optionally apply an XSLT file against the output file to transform it into another format. The transformed file name is: ar_trans_yyyyMMddHHmmssSSS.extention, where extension is defined by the pmtARIntegrator job configuration.
4. Inside the process() method, SampleARPaymentIntegrator updates flexible_field_3 of both check and credit card payments to Y, and writes that to database. This ensures these payments will not be processed again by the next run of pmtARIntegrator.

Customizing the arQuery.xml File

The SQL queries used by the pmtARIntegrator job are defined in an XML file, arQuery.xml, which is provided by the default Payment installation. The arQuery.xml file is based on Oracle XMLQuery technology.

CAUTION:  XMLQuery supports paging, but this feature must not be used for this job.

Most of the A/R file creation is done by an implementation class of the interface com.edocs.common.payment.tasks.ar.IARPaymentIntegrator. This adaptor interface provides maximum flexibility for customizing this job. The default implementation is com.edocs.common.payment.tasks.ar.SampleARPaymentIntegrator.

Before the query is executed in the database, the job invokes the getMap() method of IARPaymentIntegrator, which gets a list of objects that are used to replace the variables "?" defined in the SQL query of the arQuery.xml file. For more information about IARPaymentIntegrator, see Accessing Oracle Billing Insight Data Dictionary and Javadoc for details on accessing Oracle Billing Insight Javadoc.

The default IARPaymentIntegrator implementation, SampleARPaymentIntegrator, uses this arQuery.xml file for database query:

<?xml version="1.0" encoding="UTF-8"?>
<query-spec>
<data_source_type>SQL</data_source_type>
<query name="checkQuery">
<sql-stmt><![CDATA[select payment_id, payer_account_number, amount, pay_date, status, payee_id from EDX_PMT_TRANSACTION where payee_id = ? and status = 108 and arFlag = 'N' and PAYMENT_TYPE='check']]></sql-stmt>
<param name="payee_id" type="java.lang.Integer" position="1"/>
<!--param name="last_modify_time" type="java.sql.Timestamp" position="2" /-->
</query>
<query name="creditCardQuery">
<sql-stmt><![CDATA[select payment_id, payer_account_number, amount, pay_date, status, payee_id from EDX_PMT_TRANSACTION where payee_id = ? and status = 108 and arFlag = 'N' and PAYMENT_TYPE='ccard']]></sql-stmt>
<param name="payee_id" type="java.lang.Integer" position="1"/>
</query>

<query name="debitCardQuery">
<sql-stmt><![CDATA[select payment_id, payer_account_number, amount, pay_date, status, payee_id from EDX_PMT_TRANSACTION where payee_id = ? and status = 108 and arFlag = 'N' and PAYMENT_TYPE='dcard']]></sql-stmt>
<param name="payee_id" type="java.lang.Integer" position="1"/>
</query-spec>

The following queries are defined:

• checkQuery. Queries check payments
• creditCardQuery. Queries credit card payments
• debitCardQuery. Queries debit card payments

Both creditCardQuery and debitCardQuery get all the successful payments (status=8) of the current payee (biller or DDN of current job) from the relevant Oracle Billing Insight Payment tables. They both use arFlag as a flag to prevent a payment from being sent to the A/R job twice. This flag is initially set to N when the payment is created. After the A/R job runs, the SampleARPaymentIntegrator changes the flag to Y.

When using arFlag as an A/R flag, you can create an index for it to increase performance.

Each of the queries in the arQuery.xml file has an SQL variable ('?') that must be resolved before the query can be sent to the database. The A/R job calls the getMap() method of IARPaymentIntegrator to get a Map of query variables, and uses their values to replace the '?'s in the query. The names of the Map elements match those defined in the param tags of the query tags.

For example, the default arQuery.xml file has the param tag:

<param name="payee_id" type="java.lang.Integer" position="1"/>

To support this, define a Map element whose name is payee_id and whose value (which must be an Integer, and contains the DDN reference number) replaces the question mark (?) with payee_id in the query:

select payment_id, payer_account_number, amount, pay_date, status, payee_id from EDX_PMT_TRANSACTION where payee_id = ? and status = 108 and arFlag = 'N' and PAYMENT_TYPE='check'

The query result set will be transferred to a list of checks (ICheck objects) for checkQuery, credit cards (ICreditCard objects) for creditCardQuery, debit cards(ICreditCard objects) for debitCardQuery, and then pass that list to the process() method of IARPaymentIntegrator.

CAUTION:  The XML Query object supports paging, but do not use this feature for A/R query.

You can modify this file to use different queries.

Querying Case Study

The new requirement for this example is to retrieve all payments whose status is returned or paid between 5:00PM today (the job run date) and 5:00PM yesterday (yesterday's job run date).

To try a query case study

1. Change the arQuery.xml file for checkQuery:

   <query name="checkQuery">
   <sql-stmt><![CDATA[select * from EDX_PMT_TRANSACTION where payee_id=? and status in (108,-104) and last_modify_time >= ? and last_modify_time < ? and PAYMENT_TYPE='check']] </sql-stmt>
   <param name="payee_id" type="java.lang.Integer" position="1"/>
   <param name="min_last_modify_time" type="java.sql.Timestamp" position="2"/>
   <param name="max_last_modify_time" type="java.sql.Timestamp" position="3"/>
   </query>

   TIP:   Use java.sql.Timestamp instead of java.util.Date.

2. Change the arQuery.xml file for creditCardQuery. Because you are adding more question marks to the query, override the getMap() method of the default ARPaymentIntegrator:

   package com.edocs.common.ps.ar;
   import java.util.*;
   import com.edocs.common.payment.util.DateUtil;
   public class MyARIntegrator extends ARPaymentIntegrator
   { /**Override this method to populate the SQL variables in arQuery.xml
   */public Map getMap(ARPaymentIntegratorParams payIntegratorParam,
   String objectFlag) throws Exception
   { //call super class because need to get the payee_id value
   Map map = super.getMap(payIntegratorParam, objectFlag) //no need to check objectFlag because we actually populate the //same values for both checkQuery and creditCardQuery Date today = new Date(); today = DateUtil.dayStart(today);//set to 00:00:00AM Date today5 = DateUtil.addHours(today, 17); //set to 05:00:00PM
   Date yesterday5 = DateUtil.addHours(today, -7) ;//set to 05:00:00PM of yesterday
   map.put("min_last_modify_time", DateUtil.toSqlTimestamp(yesterday5));
   map.put("max_last_modify_time", DateUtil.toSqlTimestamp(today5))}
   

3. To make the cutoff time configurable instead of fixed at 5:00PM, use the flexible configuration fields of the A/R job, which are passed in as part of ARPaymentIntegratorParams. For more information about ARPaymentIntegratorParams, see Accessing Oracle Billing Insight Data Dictionary and Javadoc to access the Javadoc.
4. Compile your class using the payment_client.jar and payment-1.0-SNAPSHOT.jar that comes with Oracle Billing Insight, package the compiled class into the payment EAR files, and redeploy the EAR files.
5. Log into the Command Center and change the configuration of the A/R job to use the new implementation of the IARPaymentIntegrator, com.edocs.common.ps.ar.MyARIntegrator.

   For more information about using the Command Center, see Administration Guide for Oracle Billing Insight.

Customizing the arFlat_template.txt File

Payments returned by the arQuery.xml file are written to an A/R file using an Oracle Billing Insight Payment template file. Two templates come with Oracle Billing Insight:

• arFlat_template.txt. Generates a flat A/R file
• arXML_template.xml. Generates an XML A/R file

The arFlat_template.txt file generates a sample flat A/R file. If this file includes most of your required data, but the format is not what you want, then you can edit the template file to generate your own format. For more information about using the Template class, see Accessing Oracle Billing Insight Data Dictionary and Javadoc.

The A/R job using arFlat_template.txt does the following:

• Loops through the list of check and credit card payments to print out their details.
• Calculates the totals for check debits, check credits, credit card debits and credit card credits (reversals).

Customizing the arXML_template.xml File

The arXML_template.xml file generates the same information as arFlat_template.txt, but in XML format. After creating the XML file, you can use XSLT to transform it into another XML file or into a flat file. The default arTransform.xsl transforms the original XML file into the same format as the one generated by arFlat_template.txt. Using XSLT is the recommended way to do the customization.

The A/R job using the arXML_template.xml file does the following:

• Loops through the list of check and credit card payments to print out their details.
• Calculates the totals for check debits, check credits, credit card debits and credit card credits (reversals).

To generate different file formats, change arTransform.xsl. Or, customize the arXML_template.xml file directly.

Customizing the arXML_template.xml File and Using XSLT to Generate an XML Flat AR File

The arXML_template.xml file generates the same information as arFlat_template.txt, but in XML format. After generating the XML file, you can use XSLT to transfer it into another XML file or into a flat file. The default arTransform.xsl transforms the XML file into the same format as the one generated by arFlat_template.txt. XSLT is the recommended way to make the customization.

This template does the following:

• Loops through the list of check and credit card payments to print out their details.
• Calculates the totals for check debits, check credits, credit card debits and credit card credits (reversals).

To generate different file formats, change arTransform.xsl. If required, then you can also customize the arXML_template.xml file.

To rename the generated files

• To rename the files generated by these utilities you must write an implementation of IARPaymentIntegrator. The following code demonstrates how to rename the XSLT output file to another name:

  import java.io.*;
  public class MyARIntegrator extends ARPaymentIntegrator
  {protected void getTransformedARFileName(ARPaymentIntegratorParams payIntegratorParam, ) throws Exception{
  return "newARName.txt";}}

Reimplement IARPaymentIntegrator

You might want to reimplement the default SampleARPaymentIntegrator if you want to add any of the following features.

To reimplement the default SampleARPaymentIntegrator

1. Rename the default AR files.
2. Change the SQL query to add more "?" variables and to set values for those variables in the IARPaymentIntegrator implementation.
3. Add any additional steps, such as putting more objects into Template context before it is parsed.
4. Change the result of the template parsing. For example, because of limitations of Template engine, sometimes unwanted empty new lines are added. Remove those lines.
5. Modify the check or credit card objects before they are updated in the database. By default, only the arFlag field is updated from N to Y. Another alternative is to update the check or credit card object in the template, and all your updates will be updated in the database.

   To add any of the preceding features, you must extend from SampleARPaymentIntegrator and configure the pmtARIntegrator job to use your implementation.

   You can overwrite following methods for your customization:

   • getARFileName(). Overwrite to change the name of the AR flat file generated from arFlat_template.txt.
   • getMap(). Overwrite

Select Only Check or Credit and Debit Card Payments

A biller might support only one of check or credit and debit card payments. In this case, you must configure the pmtARIntegrator job to leave the Credit card or Debit card query name in XML query file field blank. To optionally remove any reference to the unavailable payment type, customize the template files (arFlat_template.txt or arXML_template.xml).

Compiling and Packaging a Custom IARIntegrator

If you reimplement IARIntegrator or you have some custom Java classes to call from the AR template, then you must recompile and package your changes.

In most cases, you put your custom code into payment_custom.jar. Unfortunately, the IARIntegrator and its related classes are packaged as part of ejb-Payment-ar.jar, not payment_custom.jar, so a different procedure is required.

To compile, put ejb-payment-ar.jar along with payment-1.0-SNAPSHOT.jar, payment_custom.jar and payment_client.jar in your class path to reimplement IARIntegrator.

To package, drop all your AR custom classes into the ejb-payment-ar.jar.

A/R Filenames

The generated A/R files have default names of ar_yyyyMMddHHmmssSSS.template_file_ext. In the files, template_file_ext is the file extension of the template file. The XSLT transformed file has a default name of ar_trans_yyyyMMddHHmmssSSS.extension. The extension is defined in the pmtARIntegrator job configuration. You can rename these files to a more meaningful name.

To rename the files, write an implementation of IARPaymentIntegrator. The following code demonstrates how to rename the XSLT output file to another name:

package com.edocs.common.ps.ar;
import com.edocs.common.payment.tasks.ar.*;
public class MyARIntegrator extends ARPaymentIntegrator
{/**Override this method to give a new name*/protected void getTransformedARFileName(ARPaymentIntegratorParams payIntegratorParam, ) throws Exception
{return "newARName.txt";}}

Single Payment Type

A biller might have only ACH and not credit card or debit card payments, or conversely. In this case, you can customize the template files (arFlat_template.txt or arXML_template.xml) to remove any references to the unavailable payment type.

Or, when configuring the pmtARIntegrator job enter an empty value for the Check query name in XML query file or Credit card or Debit card query name in XML query file parameter.