LeasePak Utilities

248 Vendor Extract

The Vendor Extract utility extracts information about all dealers or vendors existing in LeasePak. It processes a user-customizable stored procedure that outputs data into an XML file. Once utility completes processing and if required, the XML file can be transformed into a fixed-length record text file by using an XSL file or template and the XSLT process. This is especially helpful if user wants to input the vendor information extracted from LeasePak into a third party software package such as FDI.

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

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

Running the Vendor Extract utility

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_FDI_VEN>,to extract data for all vendors.

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 to proceed.

The utility continues processing to generate the XML file, LeasePak will auto names it as lputilfdi_vendor_ext.xml, and places it in the directory from which you ran the utility.

The utility will process the stored procedure and then process the results of the stored procedure to create an XML file, which can later be transformed into another format.

You can automate processing of this utility by creating a script to run the utility that is then will be scheduled to run on weekly basis.

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

NOTE that 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 users own systems and circumstances could easily vary and necessitate deviation from this example.

Users will need to create a custom stored procedure. The stored procedure will select the following fields from LeasePak database:

Description	Table	Field
Vendor (dealer)	RLR	dlr_s
Long name	RLR	nam_long_s
Address	RLR	add_s
Address 2	RLR	add2_s
City	RLR	cty_s
State	RLR	st_s
Zip code	RLR	zip_s
Phone number	RLR	phn_s
Contact name 1	RLR	cont_naml_s
Activity status	RLR	stat_s
Fax number	RUO	fieldl_s

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

Output

Once utility processing completes and outputs XML file. LeasePak places the utility output file in the directory from which the user ran the utility. The utility determines the element names in the XML output by using the column names of the resulting stored procedure output after putting header and record elements in.

Here is an example of one possible output:

<?xml version="1.0" encoding="iso-8859-1" ?> 
<!-- [vendor_extract] --> 
<endor_extract hrefs_on="-1" lpmreq_on="-1" popups_on="-1"> 
<header> 
	<run_dt>11/03/09</run_dt> 
	<run_tm>12:05pm</run_tm> 
	<user_s>USER1</user_s> 
	<version_s>HP-UX Sybase 6.10.2931</version_s> 
	<div_name_s>EQUIPMENT LEASE DIVISION</div_name_s> 
</header> 
<result item="1"> 
	<row item="1"> 
		<dlr_s>36</dlr_s> 
		<nam_long_s>KELLERMAN PUBLISHING</nam_long_s> 
		<add_s>89 Oak St.</add_s> 
		<add2_s /> 
		<cty_s>Ashland</cty_s> 
		<st_s>OR</st_s> 
		<zip_s>97500</zip_s> 
		<phn_s /> 
		<cont_nam1_s>Fred Weaver</cont_nam1_s> 
		<stat_s>ACTV</stat_s> 
		<field1_s /> 
	</row> 
	<row item="2"> 
		<dlr_s>711</dlr_s> 
		<nam_long_s>Stan's Automotive</nam_long_s> 
		<add_s>47892 Main St.</add_s> 
		<add2_s>Suite 100</add2_s> 
		<cty_s>Lansing</cty_s> 
		<st_s>MI</st_s> 
		<zip_s>493769987</zip_s> 
		<phn_s>5176038972</phn_s> 
		<cont_nam1_s>Stan Schumaker</cont_nam1_s> 
		<stat_s>PNDG</stat_s> 
		<field1_s>5176038999</field1_s> 
	</row> 
</result> 
</vendor_extract> 

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.