LeasePak Utilities

247 FDI Asset Extracts

• The utility extracts information about vehicle assets on newly booked leases. Only those leases that are booked since the utility was last run till today (i.e., when the utility is being run), are picked. Note that leases that have been marked as void/cancel or assets marked for title release are not selected.
  
  Suppose the utility was run on June 4, 2010 and again on June 14, 2010. The utility would pull assets from only those leases that are booked between June 4 (after running the utility) and June 14.
  
  The utility processes a user-customizable stored procedure that outputs vehicle 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 information extracted from LeasePak into a third party software package such as FDI.



• The utility takes snapshots of certain data in LeasePak's database each time it is run. It does a comparison between current data and the snapshot data from the previous extract to determine the changes. It then takes the changes, extracts the appropriate information, and writes it to a fixed length text file that meets FDI's record format.

Note that the utility creates five fixed length text files, each dedicated to a different set of changes found in the comparison.
The performance of LeasePak is not impacted unless this utility is run at the same time other processes are running.

Running the FDI Asset Extracts Utility

After logging on the LeasePak server, start the LeasePak Utilities. This utility runs from the command line the same as other LeasePak utilities run. The interface is as follows:

This utility option will extract asset information and create one XML and 5 different flat files to be sent to FDI.

Do you wish to continue (Y/N)?

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.

The utility continues processing.

• FDI asset extract utility process the stored procedure to extract all new bookings. This utility converts the output of the stored procedure to an XML file. This XML file can later be transformed using an XSL template to get it in the final format if needed.
• First, it extracts assets on leases that were booked since the last time the utility was run, to create an XML file. See the section XML output for details.
• Second, it extracts data for the five fixed length text files by comparing the current LeasePak data with lease and asset snapshots made the last time the utility was run. Any asset currently in LeasePak that is attached to a lease (where that lease number exists in the lease snapshot data) is analyzed to determine which record types need to be extracted for that asset. Multiple record types could be extracted for the same asset. Each record type makes up a file of that type, namely an A file, D file, R file, T file, and V file. See the section Fixed Length Text files output for details.
• Lastly, FDI assets extract utility snapshots the current state of the leases and assets for use in comparison the next time the utility is run.

Tip: You can automate the processing of the FDI Asset Extracts 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.

Processing and Output of N file data:

The utility will process the stored procedure using the date and time of the utility when it was last run and will retrieve the date and time the utility was last run from a control file created on the server when the utility was first run and place it in $udata directory. LeasePak updates this file to the current date and time when the utility completes processing. If there is no control file placed LeasePak assumes that the utility has never been run, and creates a new control file, and pass a date and time of 1/1/1980 120000 for processing this time. The utility will process the results of stored procedure to create an XML file and creates the file with 'N' records. The utility will than process the results of the stored procedure to create an XML file which can later be transformed into another format if required.

The client needs to extract a full listing of vehicle assets that reside in LeasePak and have had various events occur to them since the last extract for sending to FDI. This process of extracting asset information and updating FDI runs daily.

The client can automate processing of this new utility by creating a script to run the utility that is then scheduled to run daily.

Here is an example of a possible script that could be scheduled to run on 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
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.

The XML output

You can run the FDI Asset Extracts utility to extract information about all vehicle assets in LeasePak attached to a lease that has been booked since the last time the utility was run. However, if a lease has been marked as void/cancel using a user defined field, its assets are not selected. Nor is an asset selected if it has been marked for title release using a user defined field. This asset information will be output in an XML file. LeasePak place the file in the directory from which the user ran the utility.

The utility extracts the following information from LeasePak to format it into an XML file. Where two fields have the same name, one of the fields is renamed in the output to distinguish which piece of information it stores.

Description	Table	Field	Field Renamed
Lessee/Borrower Long Name	RAL	nam_long_s
Lessee/Borrower Address line 1	RAL	add_s
Lessee/Borrower Address line 2	RAL	add2_s
Lessee/Borrower City	RAL	cty_s
Lessee/Borrower State	RAL	st_s
Lessee/Borrower Zip	RAL	zip_s
Lessee/Borrower FAX Phone	RAL	hom_phn_s
Lessee/Borrower Business Phone	RAL	bus_phn_s
Co-Lessee/Co-Borrower Long Name	RAL	nam_long_s	co_nam_long_s
Region	REQ	reg_s
Office	REQ	off_s
Lease/Loan Number	REQ	lse_s
Asset Number	REQ	unit_s
Asset Identification (VIN)	REQ	id_s
Asset Year	REQ	yr_s
Asset Dealer Number	REQ	dlr_s
Equipment State	REQ	eq_st_s
Asset Amount Financed	REQ	org_d
Office	RLS	off_s
Accrual method lessor	RLS	acc_l_s
Lease/Loan date	RLS	d_lsd_s
Lease/Loan Maturity date	RLS	d_mat_s
System booking date	RLS	d_boo_s
Activity status	RLS	act_s
Physical Damage - Yes or No	RUA	fieldl7_s
Asset Status	RUA	field41_s
Vehicle Manufacturer	RVD	mnf_nam_s
Vehicle Model	RVD	mdl_nam_s

The XML file generated will be placed in the directory from where user runs the utility, and LeasePak auto name it as: lputilfdi_asset_ext.xml.

When the FDI Asset Extracts utility is run for the first time, it creates a control file in the $udata directory, which stores the date and time the utility is run. As the file is not available the first time, the program assumes the utility has never been run and passes a date and time of 1/1/1980 120000 for processing.

When the control file is available, the utility retrieves the last run date and time from it. This control file is updated to the current date and time once utility completes processing.

Transformation

If the information contained in the utility's XML output is to be input in a third-party software package such as FDI, 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.

The Fixed Length Text files' output

When the FDI Asset Extracts utility is run, it takes a snapshot of certain data fields related to each lease and vehicle asset on the system and stores both in a separate flat file. Subsequently, the lease current state snapshot file contains all leases that have not been indicated as void/cancel using a user defined field. Moreover, if all the assets on the lease have had their titles released, the lease is not included. The asset current state snapshot file contains information about all assets in the selected leases.

The lease current state snapshot file, named lputilfdi_lease_snap.dat, is created in the $udata directory with the following structure:

Description	Table	Field	Type	Length
Lease number	RLS	lse_s	char	10
Maturity date	RLS	d_mat_s	char	8
UDF void/cancel	RUG	field22_s	char	1
Lessee name	RAL	nam_long_s	char	30
Lessee address	RAL	add_s	char	30
Lessee address 2	RAL	add2_s	Char	30
Lessee city	RAL	cty_s	Char	24
Lessee state	RAL	st_s	Char	2
Lessee zip	RAL	zip_s	Char	9
Lessee home phone (fax)	RAL	hom_phn_s	Char	10
Lessee business phone	RAL	bus_phn_s	Char	10
Co-lessee name1	RAL	nam_long_s	Char	30
Number assets2		<calculation>	Integer	10

The asset current state snapshot file, named lputilfdi_asset_snap.dat, is created in the $udata directory with the following structure:

Description	Table	Field	Type	Length
Lease number	REQ	lse_s	Char	10
Asset number	REQ	unit_s	char	10
VIN	REQ	id_s	char	25
Dealer3	REQ	dlr_s	char	8
Equipment State	REQ	eq_st_s	char	2
Asset year	REQ	yr_s	char	4
Manufacturer name	RVD	mnf_nam_s	char	10
Model name	RVD	mdl_nam_s	char	11
Title filing code4	REQU	ucc_s	char	4
Title filing state4	REQU	ucc_st_s	char	2
Title filing date4	REQU	d_ucc_s	char	8
Title filing number4	REQU	ucc_num_s	char	10
UDF Title release to	RUA	field46_s	char	36
Physical damage	RUA	fieldl7_s	char	36
Recovery code5		(recov_cd_s)	char	1

If a lease did not get selected for the lputilfdi_lease_snap.dat file, the assets attached to it will not be selected in the lputilfdi_asset_snap.dat file. Moreover, an asset will not be included if it is not attached to a lease.

Fixed Length Text files

To create five flat text files, the utility compares what is currently in the LeasePak database with what was snapshot the last time the utility was run to determine which types of records should be extracted for an asset. On comparison, it creates a file for each of the following types of changes made to lease and asset records. Therefore, none of these files will include assets that are extracted this time as part of the stored procedure to create N (N is a number of records LeasePak creates after running this utility) records. None of these files includes assets that are extracted this time in the XML file. If the lease and all of its assets were extracted previously as T records for void/cancel, then no additional records will be sent in these extracts. If the asset has been sent as an R record in a previous extract, then it will not be sent in any of these extracts.

A - Account change

If an asset is attached to a lease that it was not attached to the last time the utility was run, but the lease was reported in that past, then this asset is assumed to have been added on later and is reported as an account number change.

Note:An asset with an A record can also have a T record if it's added to a lease that is being void/cancelled this time and it can also have an R record if its current asset user defined field indicates it should be released. It cannot have a D record or V record because there is no previous record to compare it to.

D - Data change

If any of the following information changes on an asset or lease since the last time the utility was run, a D record is sent to indicate those changes.

• Lease maturity date (d_mat_s)
• Lessee name (nam_long_s)
• Lessee address line 1 (add_s)
• Lessee address line 2 (add2_s)
• Lessee city (cty_s)
• Lessee state (st_s)
• Lessee zip (zip_s)
• Lessee fax (hom_phn_s)
• Lessee business phone (bus_phn_s)
• Co-lessee name (nam_long_s)
• Asset dealer number (dlr_s)
• Asset year (yr_s)
• Asset manufacturer (mnf_nam_s)
• Asset model (mdl_nam_s)
• Equipment state (eq_st_s) [only when ucc_s <> 'FILE']
• Code of title filing for vehicle (ucc_s where ucc_typ_s = 'VEH')
• State of title filing for vehicle (ucc_st_s where ucc_typ_s = 'VEH')
  [only when ucc_s = 'FILE']
• Date of title filing for vehicle (d_ucc_s where ucc_typ_s = 'VEH') [only
  when ucc_s = 'FILE']
• Title filing number for vehicle (ucc_num_s where ucc_typ_s = 'VEH')
  [only when ucc_s = 'FILE']
• Physical damage (fieldl7_s)
• Recovery code (recov_cd_s)

Note: An asset with a D record can also have a V record sent but it will not have an A record. No D record is sent for an asset if an R record is being sent during this extract because the R record will take care of it. No D record can be sent for an asset if a T record is being sent for the lease the asset is on during this extract or ever has been sent in the past.

R - Release of title

Each asset has a user defined field that indicates who the title should be released to (rua.field46_s). If this field is not blank and if it was blank, the last time the extract ran, then an R record is created this time to request the title release.

Note: An asset with an R record can also have a T record if it's attached to a lease that is being void/cancelled this time. It can also have an A record if it was not attached to its current lease last time. No D record is sent for an asset when an R record is sent.

T - Void/cancel

If a mistake is made and a lease should have all of its assets voided/cancelled, then a user defined field (rug.field22_s) is set to YES. If this field was not YES at the time of the last extract, but the lease did exist then, a T record is created this time to void/cancel it.

Note: An asset with a T record can also have an A, V, R, or D record sent.

V - VIN change

If an asset's current VIN is different than the last time the extract was run, a V record is sent to indicate that change.

Note: An asset with a V record can also have a T or D record sent but it will not have an A or R record. If an R record is sent in this extract or any past extract, then the V record is skipped.

The five fixed length text files generated after comparison are placed in the directory from which you ran the utility, and are auto named as follows:

• A file: lprfdia.rpt
• D file: lprfdid.rpt
• R file: lprfdir.rpt
• T file: lprfdit.rpt
• N file: lprfdiv.rpt

Exception Logs

For each data file being created, an exception file may also be created. The exception file has the same name as the data file with the ending being _excpt.log, and will be placed in the directory from which the utility was run. If the exception log already exists, new information will be appended to the end. Information is written to the exception log instead of the data file when the data encountered in the extract does not fit the format of the data file. For example, if a numeric value for the FDI-SALES-TAX field is longer than 8 digits, it will not fit. In this case, two lines are written to the exception log followed by a blank line. The first line has the date, time, and error message describing what does not fit, like "Sales tax amount of 100000000 too large for FDI-SALES-TAX". The second line has the information that would have been output to the data file with asterisks (*) in the positions where the data would not fit. Processing then continues as normal.

Replace lease and asset snapshots

Once the output process is complete, the utility replaces the current snapshot files with new snapshot files of the current lease and asset records for use the next time the utility is run. It also updates the date and time of the last time the extract was run in the control file.

Sample XSL Template - New Record (N):

Transform the utility's XSL output file into a new flat file in a format that meets FDI's current input file structure. The XSL file is a sample file. NetSol will not maintain the XSL file to fulfill future design changes made to FDI's input file structure. Create XSL template that will transform the XML file generated by the new utility into a new flat file format.

Following is the file structure of the new record flat text file with fixed length records. The XSL file must create output that meets this format.

FDI Field Name	Data Type	Start	Width
FDI-TRAN-TYPE	Char (1)	1	1
FDI-ACCT-TYPE	Char (1)	2	1
FDI-LESSEE-NAME	Char (30)	3	30
FDI-CO-LESSEE-NAME	Char (30)	33	30
FDI-CUST-ADDR-LINE1	Char (30)	63	30
FDI-CUST-CITY	Char (24)	93	24
FDI-CUST-STATE	Char (2)	117	2
FDI-CUST-ZIPCODE	Char (10)	119	10
FDI-CUST-HM-PHONE	Numeric (10)	129	10
FDI-CUST-POB-PHONE	Numeric (10)	139	10
FDI-ACCT-NBR	Char (25)	149	25
FDI-LOAN-NUMBER	Char (20)	174	20
FDI-LOAN-SUFFIX	Char (5)	194	5
FDI-BRANCH	Char (5)	199	5
FDI-VIN	Char (30)	204	30
FDI-VEHICLE-YR	Numeric (4)	234	4
FDI-VEHICLE-MAKE	Char (10)	238	10
FDI-VEHICLE-MODEL	Char (11)	248	11
FDI-BOOKED-DATE	MMDDYYYY	259	8
FDI-MATURITY-DATE	MMDDYYYY	267	8
FILLER	Spaces	275	112
FDI-DEALER-NUMBER	Char (8)	387	8
FDI-BOOKING-STATE	Char (2)	395	2
FILLER	Spaces	397	2
FDI-LOAN-TYPE	Char (3)	399	3
FDI-CUST-ADDR-LINE2	Char (30)	402	30
FILLER	Spaces	432	232
FDI-TITLE-FILING-DATE	MMDDYYYY	664	8
FDI-TRAILER-DATE	MMDDYYYY	672	10
FDI-TRAILER-COUNT	Numeric (10)	682	10
FILLER	Spaces	692	32
FDI-ACCT-EFFCTVE-DATE	MMDDYYYY	724	8
FDI-TITLE-NUMBER	Char (10)	732	10
FILLER	Spaces	742	463
FDI-AMT-FINANCED	Numeric (14)	1205	14
FILLER	Spaces	1219	162
FDI-PHYSICAL-DAMAGE-CD	Char (1)	1381	1
FDI-RECOVERY-CD	Char (1)	1382	1
FILLER	Spaces	1383	71
FDI-CO-ACCT-LAST-NAME	Char (25)	1454	25
FDI-CO-ACCT-FIRST-NAME	Char (15)	1479	15
FILLER	Space	1494	1
FDI-BORR-BUS-NAME	Char (60)	1495	60

Transforming to FDI New Record File

The XSL template must populate each respective FDI field in the new record file with the values of the indicated element, text string, or calculated results.

FDI Field Name	Value
FDI-TRAN-TYPE	"N"
FDI-ACCT-TYPE	If <acc_l_s> like „*IX*? or „*AX*? or „*IF*? or „*AF*? or starts with 'M' then "F" else "L"
FDI-LESSEE-NAME	<nam_long_s>
FDI-CO-LESSEE-NAME	<co_nam_long_s>
FDI-CUST-ADDR-LINE1	<add_s>
FDI-CUST-CITY	<cty_s>
FDI-CUST-STATE	<st_s>
FDI-CUST-ZIPCODE	<zip_s>
FDI-CUST-HM-PHONE	<hom_phn_s>
FDI-CUST-POB-PHONE	<bus_phn_s>
FDI-ACCT-NBR	<lse_s>
FDI-LOAN-NUMBER	<unit_s>
FDI-LOAN-SUFFIX	"F" + <reg_s>
FDI-BRANCH	<off_s>
FDI-VIN	<id_s>
FDI-VEHICLE-YR	<yr_s> (make four digits)
FDI-VEHICLE-MAKE	<mnf_nam_s>
FDI-VEHICLE-MODEL	<mdl_nam_s>
FDI-BOOKED-DATE	<d_boo_s>
FDI-MATURITY-DATE	<d_mat_s>
FILLER	[spaces]
FDI-DEALER-NUMBER	<dlr_s> (truncate one character from the left)
FDI-BOOKING-STATE	<eq_st_s>
FILLER	[spaces]
FDI-LOAN-TYPE	"VEH"
FDI-CUST-ADDR-LINE2	<add2_s>
FILLER	[spaces]
FDI-TITLE-FILING-DATE	[spaces]
FDI-TRAILER-DATE	[spaces]
FDI-TRAILER-COUNT	[spaces]
FILLER	[spaces]
FDI-ACCT-EFFCTVE-DATE	<d_lsd_s>
FDI-TITLE-NUMBER	[spaces]
FILLER	[spaces]
FDI-AMT-FINANCED	<org_d>
FILLER	[spaces]
FDI-PHYSICAL-DAMAGE-CD	<field17_s>(first character only)
FDI-RECOVERY-CD
FILLER	[spaces]
FDI-CO-ACCT-LAST-NAME	<co_nam_long_s> (starting with 16th character until 40th character)
FDI-CO-ACCT-FIRST-NAME	<co_nam_long_s> (first 15 characters)
FILLER	[spaces]
FDI-BORR-BUS-NAME	<nam_long_s> (If 60th character is a comma, replace it with a space.)

As the XSL processes each record of the XML file, it will keep a count of the number of records written. When competed the XSL will write one last record with the following information.

FDI Field Name	Value
FDI-TRAN-TYPE	"Z"
FDI-ACCT-TYPE	[spaces]
FDI-LESSEE-NAME	[spaces]
FDI-CO-LESSEE-NAME	[spaces]
FDI-CUST-ADDR-LINE1	[spaces]
FDI-CUST-CITY	[spaces]
FDI-CUST-STATE	[spaces]
FDI-CUST-ZIPCODE	[spaces]
FDI-CUST-HM-PHONE	[spaces]
FDI-CUST-POB-PHONE	[spaces]
FDI-ACCT-NBR	[spaces]
FDI-LOAN-NUMBER	[spaces]
FDI-LOAN-SUFFIX	[spaces]
FDI-BRANCH	[spaces]
FDI-VIN	[spaces]
FDI-VEHICLE-YR	[spaces]
FDI-VEHICLE-MAKE	[spaces]
FDI-VEHICLE-MODEL	[spaces]
FDI-BOOKED-DATE	[spaces]
FDI-MATURITY-DATE	[spaces]
FILLER	[spaces]
FDI-DEALER-NUMBER	[spaces]
FDI-BOOKING-STATE	[spaces]
FILLER	[spaces]
FDI-LOAN-TYPE	[spaces]
FDI-CUST-ADDR-LINE2	[spaces]
FILLER	[spaces]
FDI-TITLE-FILING-DATE	[spaces]
FDI-TRAILER-DATE	Date of extract MMDDYYYY
FDI-TRAILER-COUNT	Number of records
FILLER	[spaces]
FDI-ACCT-EFFCTVE-DATE	[spaces]
FDI-TITLE-NUMBER	[spaces]
FILLER	[spaces]
FDI-AMT-FINANCED	[spaces]
FILLER	[spaces]
FDI-PHYSICAL-DAMAGE-CD	[spaces]
FDI-RECOVERY-CD	[spaces]
FILLER	[spaces]
FDI-CO-ACCT-LAST-NAME	[spaces]
FDI-CO-ACCT-FIRST-NAME	[spaces]
FILLER	[spaces]
FDI-BORR-BUS-NAME	[spaces]