|
Implementation Guide for Oracle Self-Service E-Billing > 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 Payment 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:
- Queries the Payment database to get a list of check and/or credit card payments. 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.
- Invokes the process() method of the default implementation of com.edocs.payment.tasks.ar.IARPaymentIntegrator, which is com.edocs.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:
- Inside the process() method, if the output is an XML file, 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.
- 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.payment.tasks.ar.IARPaymentIntegrator. This adaptor interface provides maximum flexibility for customizing this job. The default implementation is com.edocs.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 Self-Service E-Billing Javadoc for details on accessing Oracle Self-Service E-Billing 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 * from check_payments where payee_id = ? and statu
s = 8 ]]></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 * from creditcard_payments where payee_id = ? and st
atus = 8 and flexible_field_3 = 'N']]></sql-stmt>
<param name="payee_id" type="java.lang.Integer" position="1"/>
</query>
</query-spec>
Two queries are defined:
- checkQuery. Queries check payments
- creditCardQuery. Queries credit card payments
Both these queries get all the successful payments (status=8) of the current payee (biller or DDN of current job) from the relevant Oracle Self-Service E-Billing Payment tables. They both use flexible_field_3 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 flexible_field_3 as an A/R flag, you can create an index for it to increase performance. Oracle Self-Service E-Billing Payment provides a script just for that purpose: create_ar_index.sql. This script is not run when the Oracle Self-Service E-Billing Payment database is created, run it manually.
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 * from check_payments where payee_id = ? and status = 8 and flexible_field_3 = 'N'
The query result set will be transferred to a list of checks (ICheck objects) for checkQuery, and credit cards (ICreditCard objects) for creditCardQuery, 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
- Change the arQuery.xml file for checkQuery:
<query name="checkQuery">
<sql-stmt><![CDATA[select * from check_payments where payee_id=? and status in (8,-4) and last_modify_time >= ? and last_modify_time < ? ]] </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.
- 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.ps.ar;
import java.util.*;
import com.edocs.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));
}
- 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 Self-Service E-Billing Javadoc to access the Javadoc.
- Compile your class using the Payment_client.jar and Payment_common.jar that comes with Oracle Self-Service E-Billing, package the compiled class into the payment EAR files, and redeploy the EAR files.
- Log into the Command Center and change the configuration of the A/R job to use the new implementation of the IARPaymentIntegrator, com.edocs.ps.ar.MyARIntegrator.
Customizing the arFlat_template.txt File
Payments returned by the arQuery.xml file are written to an A/R file using an Oracle Self-Service E-Billing Payment template file. Two templates come with Oracle Self-Service E-Billing:
- 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, you can edit the template file to generate your own format. For more information about using the Template class, see Accessing Oracle Self-Service E-Billing 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. If you are familiar with XSLT, this is the recommended way to do 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, you can also customize the arXML_template.xml file. To rename the generated files
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
- Rename the default AR files.
- Change the SQL query to add more "?" variables and to set values for those variables in the IARPaymentIntegrator implementation.
- Add any additional steps, such as putting more objects into Template context before it is parsed.
- 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.
- Modify the check or credit card objects before they are updated in the database. By default, only flexible_field_3 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 Card Payments
A biller might support only one of check or credit card payments. In this case, you must configure the pmtARIntegrator job to leave the Credit 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, 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_common.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, where the template_file_ext is the file extension of the template file. The XSLT transformed file has default name of ar_trans_yyyyMMddHHmmssSSS.extension, where extension is defined by 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.ps.ar;
import com.edocs.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 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 query name in XML query file parameter.
|
