LeasePak Utilities

246 UCC Filing Import

UCC Filing Import utility creates or updates title-filing records, imports a text file and updates title filing records for the appropriate assets. This utility also creates two custom preprocessor scripts and an XSL template that can be used to convert certain data files that are in different formats to the desired text file format needed for the import or update process in this utility. UCC Filing Import utility performs following steps to complete processing of this utility:

• The first text file updates title-filing records for the appropriate asset.
• The second text file updates the assets in LeasePak to show that UCC filing has been submitted for them.
• The third text file updates the assets in LeasePak to show that UCC filing has been filed for them.

This utility accepts an input file with the following pieces of data in each record:

*See &The Input File & Format& for the description of these below mentioned points.

• Asset or Lease number (but not both)
• Title filling status code
• Title filing date (default to system date is missing and required)
• Title filing state
• Title filing county or city
• Title filing number
• Title filing term

As per process flow, each step that occurs and the output that is generated are discussed as follows:

• The first text file, which updates title filing records, is created from an XML file. The XML file is generated by running LeasePak's UCC Filing Extract utility. (This file is mainly created to be submitted to Diligenz with information for UCC filings.)
• This XML file has to be converted into a text file for use by the UCC Filing Import utility. This utility processes the file to create or update UCC filing records.
• A file is received from Diligenz confirming records sent for the day. Compare this confirmation file to the submitted XML file and output the result in a text file.
• A file is received from Diligenz acknowledging records that have been successfully filed. You have to convert this file into a text file so that it can be processed by the UCC Filing Import utility.

The next step updates the UCC filing record on each asset that was just extracted to have the current date.



Diligenz processes all incoming files and creates a confirmation file for all files received that day, once a day. You must compare the XML files sent with the confirmation file received back. It is recommended that users must use a custom script for the comparison, the result of which should be output in a text file. The UCC filing import utility uses the text file to update the confirmed records to a status of submitted.



Diligenz creates an acknowledgment file once a day with all filings that have been successfully filed since the last file was created. You must convert this acknowledgement file into a text file by using a custom script. This text file is used by the UCC Filing Import utility to update the acknowledged records to a status of filed.



The utility creates an exception and audit reports and looks for the current title filing on the asset that matches the title filing type and update the first one found record. If no title filing record of the type indicated exists on the asset, the utility must create a new record otherwise writes an exception report. Each asset can have only five title filing records. If an asset has more than one title filing record with the same type, only the first one will be updated.

NOTE:Users must purchase UCC Filling module to access this utility.

Performance of LeasePak will not affect unless this utility or scripts are run at the same time other processes are running.

The Input File & Format

This utility takes an input file to pass in data that needs to be updated or added to assets for title filings. Name this input file as titlefilings.dat and place it in the $udata directory.In the input file format, the input file should contain one line for each lease or asset to process. Each line must contain either a lease number or asset number but not both. Each line must contain at least the following mandatory elements separated by a comma:

Lease or asset number: Lease or asset number indicates that the individual asset or all active assets attached to a specific lease, which needs to be updated. This element must be preceded by an L (for lease) or A (for asset). No space is allowed between the character L or A and the number.

Title filing type: The title filing type indicates the type of title filing to create or update. This element must be preceded by a T (for type). No space is allowed between the character T and type code.

If any title filing type other than NONE is indicated, each line must contain the following mandatory elements separated by a comma, otherwise these elements may not be sent:

Title filing status code: The title filing status code indicates the code that matches the current status of the title filing. This element must be preceded by a C (for code). No space is allowed between the character C and the status code.

Title filing date: Title filing date indicatess the date of the actual filing or attempt to file. This element must be preceded by a D (for date). It must be in YYMMDD format. No space is allowed between the character D and the date. If this element is mandatory but is not sent in input file, the utility will use the system date for the value of this missing element.

Title filing state: The title filing date will indicate that the 2-character province or state code for the filing location. This element must be preceded by an S (for state). No space is allowed between the character S and the state.

Title filing term: Title filing term indicates the number of months the filing covers before it expires. This element must be preceded by an M (for months). No space is allowed between the character M and the term.

Each line may also contain the following optional elements separated by a coma if the title filing type is not NONE:

Title filing county or city: This element of input file format ndicates that the 4-character county or city code for the filing location and must exist in the (rlo) Location table as either county location code or city location code where the code provided is the state code entered for this record. This element must be preceded by a P (for place). No space is allowed between the character P and the county or city code. If this element not listed, no changes will be made to this field for the title filing record.

Title filing number: The title filing number indicates that the number on the title certificate. This element must be preceded by N and the filing number. If this element is not listed, no changes will be made to this field for the title filing record.

Examples

The following are examples of possible data lines in a title filings input file.

L5813132A1,TUCC,CRFPN,D091216,SOR,M60

This data line directs the UCC File import utility to update or add a new title filing record with the type of UCC to all assets currently attached to the lease 5813132A1. The status code will be RFPN, with a date of 12/16/09, in the state of OR for 60 months.

A425,TUCC,CFILE,D091216,SCA,M60,N925759

This data line directs the UCC File import utility to update or add a new title filing record with the type of UCC to asset number 425. The status code will be FILE, with a date of 12/16/09, in the state of CA for 60 months and the title filing number of 925759.

L489264,TNONE

This data line directs the UCC File import utility to add a title filing record of none on all assets currently attached to lease number 489264 if a NONE record does not already exist.

Interface

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

This utility option will process the input file to create or update title filing records.

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 a valid LeasePak client string password to proceed.

The utility continues processing. It uses the text file (which has been converted from the XML file) to import data into LeasePak.

Processing

To update title filing records for the appropriate asset, the utility processes each record in the input file. If the record is for a lease, it checks that the lease exists and is not terminated or paid off, otherwise an exception record is written for the lease. If the lease is active, each asset attached to the lease with a status indicating the asset to be on lease is processed. If the input record is for an individual asset, the utility only checks to see if the asset exists and does not matters if it is on lease or not. For each asset being processed by the utility, an exception record is written if the update does not succeed, or an audit report record is written if the update is successful. If an exception is generated, the utility writes the exception record and continues processing with the next asset.

Also, note the following:

• The utility looks for a current title filing on the asset that matches the title filing type and updates the first one found.
• If no title filing record of the type indicated exists on the asset, the utility creates a new record and generates an audit report otherwise writes an exception record.
• Each asset can only have five title filing records.
• If an asset has more than one title filing record with the same type, only the first one is updated.

Each element in the input file relates to a field in the requ Asset UCC table in LeasePak, and correspondingly updates that field. The mapping is as follows:

Column Name	Description	Input file element preceded by
unit_s	Asset	A
d_ucc_s	Title filings - filing date	D
ucc_st_s	Title filings - state of filing	S
ucc_s	Title filings - filing code	C
ucc_typ_s	Title filing type	T
ucc_cnty_s	Title county of filing	P
ucc_num_s	Title file number	N
ucc_trm_l	Title filings - term	M

Since the utility is only updating records if they already exist and will only create new records where none exist, the input file could be run multiple times through the utility without duplicate occurrance.

Output

UCC Filing Import utility out outputs an audit report or an exception report for each process being processed by the utility. If the utility process is successful in processing assets, utility generates an audit report as an output.

Audit Report

The utility will create an audit report with the following information for all successful updates.


LEASE: If the input file is updating a lease, this will display the lease number for each associated asset. If the input file is updating an asset, this will be blank.

ASSET: The asset number of the asset being updated will be displayed.

TYPE: The code that indicates the type of title filing being created or updated will be displayed.

N: N for new title filing record created or U for existing title filing record updated will be displayed.

DATE: Date of the title filing will display.

CODE: Status code of the title filing record will be displayed.

ST: 2-character code of the title filing state or province will be displayed.

CNTY: 4-character code of the county or city the title filing is for will be displayed.

TRM: The term of the title filing will be displayed.

NUMBER: The number of the title filing will be displayed.

If the utility process fails in processing and updating assets than this utility generates an exception report as an output.

Exception Report

The utility will create an exception report with the following information for all updates in case if utility processing fails:

LEASE/ASSET: The lease number preceded by an L or the asset number preceded by an A will indicate which lease or asset the input file was attempting to update will be displayed. TYPE: The title filing type code that the input file was trying to update or create will be displayed. MESSAGE: The error that was encountered which caused the update to be unsuccessful will be displayed.

XSL sample Template

Users will need to create an XSL template that will transform the XML file sent to Diligenz into a new text file format that can be read by the import utility. For the purpose of transformation, the DTD file will contain the specifically needed elements.

Note: The XSL file is a sample. NetSol will not maintain the XSL file to meet future design changes made to the UCC filing import input file structure.

Transforming to Input File

The XSL template must populate each respective UCC input field in the new file with the prefix letter followed by the values of the indicated element, text string, or derived field and then a comma between fields. Each record element will be one line in the output file.

UCC Input Field	Prefix	Value
Lease	L	<SubmitterRef>,
Title filing type	T	"UCC,"
Title filings - filing code	C	"FLPN,"
Title filings - state of filing	S	<State>,
Title filings - term	M	"60"

Below is an example of a possible file:

L5813132A1,TUCC,CFLPN,SOR,M60

L58174924,TUCC,CFLPN,SCA,M60

Tip: To transform the XML file (created by the UCC Filing Extract utility) into a fixed length text file (which can be processed by the UCC Filing Import utility), you need an XSL file/template 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.