LeasePak Utilities

243 Credit Exposure Extract

The Credit Exposure Extract utility extracts credit exposure information about all leases that are active, have open accounts receivable (AR), or have been terminated within the last 7 days, along with the lease's lessee information. The utility processes a user-customizable stored procedure that outputs data into an XML file. If required, the XML file can be transformed into fixed-length record text files by using an XSL file and the XSLT process. This is especially helpful if you want to input the lease information extracted from LeasePak into a third party software package such as eCredit.

Note:The stored procedure is a .sql file placed in the $live/sql/syb directory.

The performance of LeasePak is not impacted unless this utility is run at the same time other processes are running.

Here is an example of a possible script that could be scheduled to run on a daily basis:

  
	#!/bin/sh 
	ENVNAME=$1 
	PWD_USER=$2 
	if [ "$ENVNAME" = "" ]; then 
	echo "Error: $Usage $0 environment [user-passwd]" 
	exit 1 
	fi 
	if [ "$PWD_USER" = "" ]; then 
	printf "Password: " 
	read PWD_USER 
	fi 
	if [ "$PWD_USER" = "" ]; then 
	echo "Password required" 
	exit 1 
	fi 
	if [ ! -d $top/env/$ENVNAME ]; then 
	echo "$ENVNAME environment does not exist" 
	exit 1 
	fi 
	. $top/env/$ENVNAME/etc/.lpprofile 
	$uexe/lpadriver.exe /util ### << EOF 	
	Y 
	$PWD_USER 
	Y 
	EOF 
	exit 0 
	

Transformation of XML output into two new flat files:

The utility Credit Exposure Extract will output a generic XML file using the column names of the stored procedures' output table as element names. Most third-party software packages will have a specific format they require for the input file to their system. Therefore, the next step will be to transform the utility's XML output into new file that meet the third party software package's requirements. In this case, you will be required to transform the XML file to meet eCredit's input file requirements. Once the new Credit Exposure Extract utility completes, the user or a script will call the transformation process. Calling the transformation process is usually a command line call of whatever transformation tool the client chooses to use and passing in parameters such as the name of the input XML file, the name of the XSL file, and the name of the output file.

The file is ready for processing by a third party software package:

The client may choose to use the output of the original utility or the output from a subsequent transformation. The file the client chooses to use must then be input into the third party software package using whatever process that third party package requires. In this case, the client will ftp the files to another server and combine them with files extracted out of other systems before inputting into eCredit.

You can run the Credit Exposure Extract utility to extract credit exposure information from a particular portfolio or from all portfolios. As portfolio contains a number of leases, running the utility for one portfolio extracts information from all leases in the portfolio. However, if you run it for all portfolios, information would be extracted from all leases that have been entered in LeasePak, as each lease belongs to a portfolio. Moreover, information is filtered from leases that are active, have open AR, or have been terminated within the last 7 days.

This utility runs from the command line the same as other LeasePak utilities. The interface is as follows:

	This utility option will process the customizable stored procedure, <CP_CRD_EXP> , to extract data for all leases which are active or have open AR.
	Do you wish to continue (Y/N)?
	

Enter 'Y' or 'y', the program will display

Client Password?

Enter the correct LeasePak client string password to proceed. The program will display:

Do you wish to run for all portfolios (Y/N), <Return> to exit?

Enter 'Y' or 'y' to process all the portfolios together. The utility will continue processing to generate the XML file, which would contain credit exposure information of leases from all portfolios. If you want to run for a specific portfolio enter 'N' or 'n' The program will display:

	Enter the portfolio, <Return> to exit: 

Enter a valid portfolio number. The utility will continue processing to generate the XML file, which would contain credit exposure information of leases that fall in the particular portfolio.

The XML file generated will be placed in the directory from which you ran the utility. The element names in the XML output are determined by using the column names of the stored procedure's output.

If the stored procedure, as provided by NetSol, is not modified, the utility will extract the following information from a lease in a portfolio:

Description	Table	Field
SS/BusID	RCX	ss_s
Lessee long name	RAL	nam_long_s
Lessee address	RAL	add_s
Lessee address 2	RAL	add2_s
Lessee city	RAL	cty_s
Lessee state	RAL	st_s
Lessee zip code	RAL	zip_s
Business phone	RAL	bus_phn_s
Billing address	RAL	bill_add_s
Billing address 2	RAL	bill_add2_s
Billing address city	RAL	bill_cty_s
Billing address state	RAL	bill_st_s
Billing address zip code	RAL	bill_zip_S
Office	RLS	com_s
Lease	RLS	lse_s
Lessee	RLS	les_s
Total acquisition cost	RLS	tot_org_d
Residual amount	RLS	tot_res_d
Total delinquent amount	RLS	del_amt_d
Accrued principal	RLS	prn_d
Accumulated depreciation	RLS	acl_depr_d
Lessor unearned rental income	RLS	l_ue_d
Unearned residual	RLS	r_ue_d
Contract receivable	RLS	rec_d
Lease date	RLS	d_lsd_s
Next payment due date	RLS	d_pmt_s
Number of times delinquent [1]	RLS	dellj
Number of times delinquent [2]	RLS	del2_l
Number of times delinquent [3]	RLS	del3_l
Lease term	RLS	trmj
Vendor (dealer)	RLS	dlr_s
Activity status	RLS	act_s
Accrual method lessor	RLS	acc_l_s
Number of assets on this lease	RLS	n_unt_l
Pre-authorize switch (Y/N)	RLS	pap_pmt_c
Credit quality	RLSA	cred_quality_s
Number of payment reschedules (deferrals)	RLSA	pmt_rsc_l
NSF counter	RLSB	nsf_count_l
Credit Guarantee	RUG	fieldl9_s
Residual Guarantee	RUG	field20_s
Gain/Loss on Termination	RPS	gnls_d
Date of Payoff/Termination	RPS	d_pay_s
Payment amount billed	RLSP	pmt_amt_d
Payment frequency code	RLSP	pmt_cd_s
Asset identification (VIN)	REQ	id_s
List price	REQ	lst_d (total for lease)
Total amount billed	RAR	tamt_b_d (total for lease)
Total amount paid	RAR	tamt_p_d (total for lease)
Payment due date	RAR(2)	d_pmtdu_s (min for lease)

Note: As per your requirements, the stored procedure can be modified to include a different set of data.

When the XML file is generated for all portfolios, it is auto named as, lputil_crd_exp_extract.xml. If it is generated against a particular portfolio, it is auto named as, p<portfolio number>_lputil_crd_exp_extract.xml. For example, the name p2_lputil_crd_exp_extract.xml indicates that the XML has been generated against portfolio # 2.

Tip: You can automate the processing of the Credit Exposure Extract utility by creating a script to run the utility. You can also schedule the frequency for running the utility, such as daily, weekly, and so on.

It is strongly recommended that the script should not be scheduled to run while EOP is running.

Transformation

If the information contained in the utility's XML output is to be input in a third-party software package (such as eCredit), then you may have to transform the XML file into a new format as most third-party software require the input file to have a specific format.

For transformation, you need an XSL file (which should be placed in the $uprg directory) and have an XSLT processor installed on the server where you wish to do the transformation. Xsltproc is an example of an XSLT processor, which is a free command line tool for applying XSL stylesheets to XML documents.