LeasePak Utilities

245 Maturing Leases Extract

This utility extracts out of LeasePak the assets attached to leases that are scheduled to mature at the defined number of days from today or less. Extracting asset information for assets attached to leases, which are scheduled to mature 'X' (x is a number of days user defines at the time when utility runs) days from today out of LeasePak to transfer that information into SMM.

The Maturing Leases Extract utility extracts information about active/non-sold assets attached to leases that have not yet matured. You have to specify a certain number of days, for example, 10. The utility takes the date on which it is run and adds the specified number of days (for example, 10) to arrive at a date till which it checks for open leases, and considers only those that mature by the arrived date. Suppose today is June 14, 2010. If the utility is run today, it would extract information about active assets from all open leases that mature by June 24, 2010 (which is today plus 10 days). Note that this includes leases maturing before June 14 until June 24 inclusive.

The utility processes a user-customizable stored procedure that outputs data into an XML file. If required, the XML file can be transformed into a fixed-length record text file by using an XSL file/template and the XSLT process. This is especially helpful if you want to input the asset information extracted from LeasePak into a third party software package such as SMM.

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 runs at the same time other processes are running.

You can automate the processing of this new utility by creating a script to run the utility that then will be scheduled to run every night.

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 
360 
EOF 
exit 0 

NOTE: The above script is only an example showing the input requirements for executing a LeasePak utility. It is structured to operate in a LeasePak release instance installed per NetSol's recommendations and instructions. Netsol assumes that the script will be used through a directly connected terminal or through a NetSol-supported terminal emulation program. The user's own systems and circumstances could easily vary and necessitate deviation from this example.

Running the Maturing Leases Extract utility

You can run the Maturing Leases Extract utility to extract information about active assets attached to open leases in a particular portfolio or all portfolios. As a portfolio contains a number of leases, running the utility for one portfolio extracts asset information from all leases in the portfolio. However, if you run it for all portfolios, information would be extracted from all open leases existing in LeasePak.

After logging on the LeasePak server, start the LeasePak Utilities. 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_MAT_LSE_EXT>,to extract data for all assets attached to a lease that will be maturing the number of days entered from today or less.

Do you wish to continue (Y/N)?

Enter 'Yv or 'y' to continue. However if you enter any other response including (blank), the system exits the utility and returns you back to the LeasePak Utilities main menu.

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. (Enter 'N' or 'n' to process/run for a particular portfolio). The utility continues processing to generate the XML file, which contains asset information from all portfolios. The program will display:

Enter the portfolio, <Return> to exit:

Enter a valid portfolio number. The program will display:

Number of days prior to maturity, <Return> to exit:

Enter a positive integer, which represents the number of days. The utility takes the date on which it is run and adds the entered number of days (for example, 10) to determine the date till which it checks for open leases, and pulls information only from those that mature by that date. Suppose today is June 14, 2010. The utility would extract active assets from all open leases that mature by June 24, 2010 (which is today plus 10 days). Note that this includes leases maturing before June 14 through June 24.

The utility continues processing to generate the XML file, which contains information of active assets that fall in the specified portfolio.

The XML file generated is 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.

Note that if the stored procedure, as provided by NetSol, is not modified, the utility extracts the following information from an open lease:

Processing:

To process maturing leases extract, create a new custom stored procedure. The stored procedure will have a set f input parameters that the user cannot change. The user may change anything else within the stored procedure. None of the updates will include within the specified stored procedure from LeasePak. The stored procedure will apply the following conditions to determine if the information should be included in the selection:

• Input parameter Portfolio must equal the asset's portfolio number. LeasePak utility will accept a value as the portfolio value that indicates all portfolios. If user passes this value, the stored procedure will not restrict the data pulled to only one portfolio.
• Select only assets that are currently not sold which is indicated by the asset status not being equal to 'NSLD' or 'USLD'.
• Select only assets that are designed to valid lease which is indicated by the asset lease (req.lse_s) being equal to lease (rls.lse_s) record and not being blank.
• The lease the asset is attached to must have an original expiration date <=today + 'x' days where 'x' is the input parameter for days to maturity.

The stored procedure takes input of portfolio and days to maturity parameters.

The stored procedure will select the following fields from the LeasePak database:

Description	Table	Field
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
Home phone	RAL	hom_phn_s
Business phone	RAL	bus_phn_s
SS/Bus ID	RCX	ss_s
Lease	RLS	lse_s
Lessee number	RLS	les_s
Lease date	RLS	d_lsd_s
Lease commencement date	RLS (RHS)	d_com_s
Maturity date	RLS (RHS)	d_mat_s
Last payment due date	RLS	d_lst_s
Lease extension date	RLS	d_lxt_s
Security deposit (lease level)	RLS	sec_d
Lease term	RLS (RHS)	trm_l
Number of days delinquent	RLS	days_delq_l
Chapter type	RUG	field6_s
Company	REQ	com_s
Region	REQ	reg_s
Office	REQ	off_s
Asset (unique identifier)	REQ	unit_s
Asset identification (VIN)	REQ	id_s
Residual amount	REQ	res_d
Original residual amount	REQ (RHQ)	res_d as     org_res_d
Asset disposition date	REQ	d_disp_s
Vendor (dealer)	REQ	dlr_s
Asset year code	REQ	yr_s
Average yearly allowable mileage	REQ	mil_aam_l
Asset model code	REQ	mdl_s
Asset manufacturer code	REQ	mnf_s
Asset payment amount	REQ	pmt_amt_d
Repo date	RUA	field6_s as      field6a_s
Grounding date	RUA	field9_s
Asset status	RUA	field41_s

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 Maturing Leases 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.

Output and Transaformation:

The Maturing Leases Extract utility outputs a generic XML file using the column names of the stored procedures' output table as an element names. The next step is to transform the utility's XML output into a new file. If the information contained in the utility's XML output is to be input in a third-party software package (such as SMM), 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.

Once the Maturing Leases Extract utility completes, user manually or by script must call the transformation process. Calling the transformation process is 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. Xsltproc tool can be used to test this process. To call the xsltproc tool the user must use the following parameter format:

xsltproc [options] stylesheet file [file …]

For example:

xsltproc -o /SMM/Maturing.txt new_xsl_file.xsl utility_output_file.xml 

Where -o /SMM/Maturing.txt indicates to save the output to the directory /SMM/ and name the final file output Maturing.txt, new_xsl_file.xsl is the name of the new XSL file.

For example, if you want to transform the XML file into a fixed length text file, you need an XSL file/template (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.