LeasePak Utilities

242 VIN/Serial Number Extract

The VIN/Serial Number Extract utility extracts VIN information on newly booked leases and creates a fixed length text file. You can then send this file to a third-party service for any purpose, such as looking up license plate information.

LeasePak allows to extract VINs of assets attached to all leases that belong to a particular portfolio or assets attached to leases in all portfolios. Only those assets are included that fit the following criteria:

• The asset must be either New or Used with the asset status equals to NLSE or ULSE.
• The asset is classified as a vehicle. In LeasePak language, the asset class (cls_s) begins with 'V'.
• The asset is in the portfolio specified by the user when running the utility - if all portfolios are not requested. (The utility allows you to extract asset information from leases that belong to one portfolio or from all portfolios.)
• The lease to which the asset belongs has not been paid off or terminated (currently lease status is not 'NP' or 'NC').
• The lease does not have an accrual type of '*AX*', '*AF*', '*IX*', or '*IF*'.
• The lease booking date is less than today minus 30 days if no file is placed in the $udata directory).
• The lease booking date is greater than or equal to the last extract date minus 30 days if an extract already exists or an empty file is placed in the $udata directory.

Note:The VIN/Serial Number Extract utility uses the lease booking date and not the asset booking date to select which assets to extract. If you book an asset and do not place it on a lease, the utility will not select the asset to be extracted. If you add an asset to a lease after the extract has pulled the assets from that lease, the utility will not select the new asset for extract.

When using the utility for the first time, you can choose to run it in any of the following ways:

With an empty file

Create an empty file in the $udata directory on your LeasePak server.

• If you want to run the utility for a particular portfolio, name the file p#_vin_extract_YYYYMMDD.dat (where # is replaced with the portfolio number).
  The utility only considers extract files previously created for the same portfolio selection as that which the utility is being run for this time. This means that if you want to run the utility for portfolio 04, name that file as p4_vin_extract_YYYYMMDD.dat.
• If you intend to run the utility for all portfolios, name the file as vin_extract_YYYYMMDD.dat. Next, change YYYYMMDD to a valid date. No assets on leases booked 30 days prior to this date will be pulled.
  For example, if you do not want to include any asset on a lease booked prior to August 31, 2009 but you do want to include those booked on August 31, 2009, then replace YYYYMMDD with 20090930 (which is August 31, 2009 plus 30 days). The file name would then be vin_extract_20090930.dat (when no portfolio is specified).

Without any file

Run the utility without creating any file in the $udata directory. For example, if today is October 15, 2009 and you run the utility today with no file in the $udata directory, the utility would pull assets on leases booked prior to September 15, 2009.

The extracted information includes the following data for each asset with the indicated length for each field.

The extracted information includes the following data for each asset with the indicated length for each field.

Field	Starting Position	Length	Comments
VIN	1	17
MODEL YEAR	18	4	Optional field - Model year of the vehicle
EXPIRY DATE	(yyyymm)22	6	Optional field
FILLER	28	1
STATE ABBREVIATION	29	2	Optional field - State the vehicle is expected to be registered into
TYPE (Constant)	31	1	"F"
CUSTOMER DATA	32	19	Optional field- "NISSAN COMMERCIAL"
POLK ASSIGNED CUST ID (Constant)	51	7	SYEDZOO

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

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

	#!/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
	NISSAN COMMERCIAL
	SYEDZ00
	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 client's own systems and circumstances could easily vary and necessitate deviation from this example.

Running the VIN/Serial Number Extract utility

This utility will extract data for all vehicle assets on active leases booked at least 30 days ago and not included in a prior extract. 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:

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

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?	

Do one of the following: Enter 'Y' or 'y', if you wish to run for all portfolios. (Or enter 'N' if you wish to run for a particular portfolio. The program will proceed and display the message for particular portfolio input accordingly and proceeds accordingly). The program will display:

	
	Enter up to 19 characters of custom data to be included with each record extracted:

Enter any text up to 19 characters; the program displays

	
	Enter a customer ID for the system receiving the extract, <Return> to exit

Enter any text up to 7 characters.

Processing:

The utility continues processing to generate the text file with fixed length fields containing the VINs of assets that fall in a particular portfolio or all portfolios. The record for each asset is maintained separately in the file.

This utility uses the values entered at the input prompts to process the following extract: The program will select the VIN (id_s), model year (yr_s), and state (eq_st_s) for each asset record (req) where the following conditions apply:

• The asset status must be equals to NLSE or ULSE.
• The asset class (cls_s) begins with 'V'.
• The is in the portfolio entered by the user when running the utility if all portfolios is not requested.
• The program will find the lease the asset is on and verify the following also:
1. The lease status does not equal to 'NP*' or 'NC*'
2. The lease does not have an accrual type of '*AX*', '*AF*', '*IX*', or '*IF*'.
3. The lease booking date is less than today minus 30 days.
4. The lease booking date is greater than or equal to the last extract date (30 days if an extract already exist).

Note: The program will determine the last extract date by retrieving the newest extracted file created from this utility that is in the $udata directory on the server. The program will only consider extract files previously created for the same portfolio selection as that which the utility is being run for this time. If the program finds the last extract date to be equal to today, the program will assume this is a re-run and therefore go to the next most recent extract file and use that date instead. If the program finds a date that is not valid date, it will assume the extract has not been run in the past and disagreed this condition.

The VIN/Serial Number Extract utility transforms the data in each extracted record to create a record formatted as follows:

Starting Position	Length	Data	Transformation
1	17	id_s	Trim any leading spaces and then take the first 17 characters of id_s and truncate an additional data
18	4	yr_s	(no change)
22	6		Fill with spaces
28	1		Fill with spaces
29	2	eq_st_s	(no change)
31	1		(no change)
32	19	Response to prompt 5	Left justify and pad the right with spaces if needed
51	7	Response to prompt 6	Left justify and pad the right with spaces if needed

For Example:

5N1AZ36A54C2519822004 MSFNISSAN COMMERCIAL SYEDZ00
1N4NG11C8SN0023792008 TXFNISSAN COMMERCIAL SYEDZ00

Output:

The utility will output a fixed length file and place it in the $udata directory. When it is generated for all portfolios, it is auto named as, vin_extract_YYYYMMDD.dat. If it is generated against a particular portfolio, it is auto named as, p#_vin_extract_YYYYMMDD.dat, where # is the portfolio number. For example, the name p2__vin_extract_YYYYMMDD.dat indicates that the fixed length file has been generated against portfolio # 2. YYYYMMDD represents the date the utility is run.

Completed file will be placed under $udata directory. User are responsible and have to clean up these flat files in the $udata directory by archiving them somewhere if desired or deleting them. However, the most recent file extract must always remain in the directory so that the utility can determine the last time it was ran. The case is similar to running the utility with an empty file in the $udata directory see heading, With an empty file). If you do not leave any file in the directory, the case is similar to running the utility with no file in the $udata directory (see heading, Without any file).

Note the following:

• The VIN/Serial Number Extract utility only considers extract files previously created for the same portfolio selection as that which the utility is being run for this time.
• If you run the utility more than once on the same day, the utility overwrites the previously extracted file for that day and replaces it with a new file as if it had not been previously run.
• If the utility finds a date that is not a valid date, it assumes the utility has not been previously run for that portfolio. The case is similar to running the utility with no file in the $udata directory (refer to the heading, Without any file).

Tip: You can either run the utility manually or write a script that will run the utility through a cron job schedule or EOP suite.