LeasePak Utilities

244 Lease & Asset Information Extract

The Lease & Asset Information Extract utility extracts information about all leases and their associated assets. Only those leases are pulled that are active, have open AR, or have been terminated within the last 7 days. 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 and the XSLT process. This is especially helpful if you want to input the lease and 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 is run at the same time other processes are running.

You can run the Lease & Asset Information Extract utility to extract information about leases and their associated assets from a particular portfolio or from all portfolios. As a 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.

After logging on the LeasePak server, start the LeasePak Utilities. This utility runs from the command line the same as other LeasePak utilities.

Interface

The interface is as follows:

This utility option will process the customizable stored procedure, cp_smm_extract.sql, to extract data for all leases and their associated 
assets once they have been booked until the assets have been sold for more than 7 days.

Do you wish to continue (Y/N)? y 

Enter 'Y' 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' if you want to process a particular portfolio. Enter 'Y' or 'y'. The program will process for all portfolios or for a specific portfolio depending on the option selected. Enter a valid portfolio number. The utility continues processing to generate the XML file, which contains information of leases and their associated assets that fall in the particular 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 a lease in a 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 a lease in a portfolio:

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
Business phone	RAL	bus_phn_s
SS/BusID	RCX	ss_s
Lease	RLS	lse_s
Lessee number	RLS	les_s
Lease date	RLS	d_lsd_s
Lease commencement date	RLS	d_com_s
Maturity date	RLS	d_mat_s
System booking date	RLS	d_boo_s
Last rebook date	RLS	d_reb_s
Lease extension date	RLS	d_lxt_s
Activity status date	RLS	d_act_s
Lease type code	RLS	ls_typ_s
Lease term	RLS	Trmj
Lessor unearned rental income	RLS	l_ue_d
Contract receivable	RLS	rec_d
Activity status	RLS	act_s
Date of Payoff/Termination	RPS	d_pay_s
Filing date	RUG	field5_s
Chapter type	RUG	field6_s
Maturity-excess mile (cents/mile)	RCA	tma_emc_d
Company	REQ	com_s
Region	REQ	reg_s
Office	REQ	off_s
Asset (unique identifier)	REQ	unit_s
Asset identification (VIN)	REQ	id_s
List price	REQ	lst_d
Original acquisition cost	REQ	org_d
Residual amount	REQ	res_d
Equipment acquisition date	REQ	d_acq_s
Asset disposition date	REQ	d_disp_s
Vendor (dealer)	REQ	dlr_s
Depreciation L-T-D book	REQ	ltd_bkd_d
New/used (N/U)	REQ	new_c
Net book value at payoff/termination	REQ	nbv_pay_d
Asset model code	REQ	mdl_s
Asset manufacturer code	REQ	mnf_s
Asset payment amount	REQ	pmt_amt_d
Average yearly allowable mileage	REQ	mil_aam_l
Proceeds of sale	RCS	pro_sale_d
Repo date	RUA	field6_s as field6a_s
Legal sale date	RUA	fieldS_s
Grounding hours / mileage	RUA	fieldll_s
Asset status	RUA	field41_s
Title release to	RUA	field46_s
Title release client	RUA	field47_s
Repo Agent	RUA	field50_s (trim all spaces and right justify in 9 character field) as client_id_s
Months expired	calculation	mon_expired_l

When LeasePak generates XML file 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 Lease & Asset information 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.

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

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.

Transformation

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.

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.