U04 End of Period

U0437 Vertex Interface: L Series

For more information about End of Period,
refer to U04 End of Period Overview.

Vertex O Series users: Vertex O Series does not use this EOP Process. Instead, refer to Vertex® Indirect Tax O Series overview for more information.

Vertex L Series: This EOP Process requires the Vertex L Series optional license module.

Users must use U0701 Location and U0713 State update to add new Canadian provinces and cities and to update tax rates respectively.

U0437 Vertex Interface L Series will automatically insert/update the rlo Location and rnm Name tables using Vertex's data file. The Vertex Interface automates location updates and tax rate maintenance. Without the Vertex interface, the user must enter all location information, including tax rates, through U0701 Location update. In locations where a cap is placed on the use tax which may be charged on each asset on every billing ("maximum use tax"), Vertex passes those maximums on to LeasePak. This interface is executed via EOD/EOM processing in U0401 Submit and is only accessible if Vertex L Series is part of the user license.

The Vertex Interface update takes care of all tax rate changes so the users do not need to perform rate updates. It is highly recommended that the user not manually add locations if the Vertex Interface module has been purchased. If any records are manually added to the RLO/RNM tables, these records will not be updated by the Vertex update. The Lookup function in U0701 Location may not work if the user manually adds locations that do not use the standard Geo Codes. Rates may be manually updated if necessary, but eventually, should Vertex attempt to post the same rate change, the manual rate change will be pushed to the Previous rate column and the current rates installed.

LeasePak will track historical tax rate changes as well as store the correct tax rates from Vertex to provide tax calculations for back dated payments and reversals. When the tax rates are updated as part of the End of Period process, the RST column tax_rate_s will determine which rate, if any, will be loaded for locations under the same state. LeasePak maintains the RST column tax_rate_s through the field TAX RATE TO USE in the U0713 State update. The user can set TAX RATE TO USE to Sales, Use, Rental, or None for each state location.

The first record of Vertex datafile contains the date of Vertex update. This date is compared to the RCG column d_vtx_upd_ymd_s to prevent the same datafile being posted twice or to give an error message if a datafile was skipped.

A full sales tax datafile is received from Vertex monthly. The vertex interface will look for the STAXMAST in the following directory:

$TOPDIR/vertex

The End of Period only processes the Vertex file when the U0712EP switch "Run Vertex this EOP Y/N" is set to 'Y'.

It is virtually impossible to change a database from manually maintained system (through U0701 Location) to Vertex. Massive and costly customized conversions are required to do this. To avoid such an expense, the Vertex interface should be purchased and used from the very beginning. The only other alternative is to maintain a manual system that uses geocodes and data identical to Vertex. Attempting to use a Vertex update on an active database with a manual location system will most likely result in massive data corruption.

For the new LeasePak user whose database contains only seed data, the first Vertex run is processed as follows:

• Read Vertex datafile sequentially
• For each location create a new RLO record with the key equal to the 9-character Geo Code, with a space in the fourth position of county code
• For each location create a new RNM record with the location name

For LeasePak 6.2 prior version vertex will look for the STAXMAST file, vertex interface will rename it with SEQEXP in the following directory.

$TOPDIR/vertex

Once vertex found datafile STAXMAST, Vertex will process the file with the following exceptions:

• Instead of reading in 200 bytes at a time, it will read in 800 bytes at a time because each record now consists of 800 bytes instead of 200 bytes.
• In the 800 byte record, the additional 600 bytes will be ignored.
• The vertex will only use the first 200 bytes of each record and will parse them the same way it parsed from SEQEXP.
• At the end of processing file vertex will rename the STAXMAST file the same way it previously renamed the SEQEXP file.

Two Custom General (RCG) columns are used to control the Vertex update. After processing the Vertex datafile for the first time, the RCG switch vertex_run_c is set to 'Y' and the column d_vtx_upd_ymd_s is updated with the date of the Vertex datafile. These columns are used to prevent duplicating the update and to check if any update has been missed.

After the Vertex datafile has been loaded for the first time, subsequent datafiles are processed by reading the Vertex datafile sequentially and inserting/updating records that have been added or changed. The RLO/RNM tables are updated with new rates, new locations, and other changes. After successful completion of the update, the Vertex SEQEXP datafile is renamed to VERTEX_YYMMDD.DAT, and the RCG column d_vtx_upd_ymd_s is updated with the date of the Vertex datafile.

An audit report is produced to list all changes from the last update and to list exceptions. Exceptions are flagged with an * (asterisk).

Standalone Vertex file Preprocessor :

For LeasePak prior to 6.2 versions the Vertex data file will look for the STAXMAST and rename it with SEQEXP and copied to the vertex directory:

$TOPDIR/vertex

For clients using LeasePak 6.2 prior versions are required to run the following script. The script will look for STAXMAST in directory:

$TOPDIR/vertex

And convert it in to SEQEXP. The script will read in 800 bytes at a time of the new STAXMAST file. The script will then write out the first 200 bytes of the 800 bytes just read to a file named SEQEXP. It will continue to do that until the entire STAXMAST file has been read in and written out to the SEQEXP file. It will then rename the STAXMAST file to VERTEX_STAX_YYMMDD.DAT where YYMMDD equals the current year month and day. The SEQEXP file is now in the format that LeasePak can read and process

Users using prior version are required to run following new standalone preprocessor script :

#!/usr/bin/perl -w
# ---------------------------------------------------------------
# Name: staxmast_conv.pl
#
# Description: Converts the STAXMAST file from VERTEX so that
# it can be used in conjunction with LeasePak for
# versions pre-release LP 6.2 MR3.
#
# Parameters: None [r]
# ---------------------------------------------------------------
# Author: Paul Banke
# Date: 04/20/2010
# ---------------------------------------------------------------
# ---------------------------------------------------------------
# Packages
# ---------------------------------------------------------------
use strict;
# ---------------------------------------------------------------
# Parameters
# ---------------------------------------------------------------
# ---------------------------------------------------------------
# Get the current date & time
# ---------------------------------------------------------------
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$mon += 1;
$year -= 100;
# ---------------------------------------------------------------
# Variables
# ---------------------------------------------------------------
my $vertex = $ENV{'vertex'};
my $ifile = $vertex . "/STAXMAST";
my $ofile = $vertex . "/SEQEXP";
my $nfile = $vertex . sprintf("/VERTEX_STAX_%02d%02d%02d.DAT", $year, $mon, $mday);
# ---------------------------------------------------------------
# read and convert our file
# ---------------------------------------------------------------
open IFILE, $ifile or die $!." <$ifile>";
open OFILE, ">".$ofile or die $!." <$ofile>";
binmode IFILE;
my ($buf, $data, $n);
while (($n = read IFILE, $data, 800) != 0) {
    print OFILE substr($data,0,200);
}
close(IFILE);
close(OFILE);
# ---------------------------------------------------------------
# rename our original file
# ---------------------------------------------------------------
rename ($ifile, $nfile);
$ifile) or die $!." <$ifile>";

Vertex Interface Audit

Geo Code  State                County               City                      E New Value Old Value Eff Date Description
========= ==================== ==================== ========================= = ========= ========= ======== =======================
010730560 ALABAMA              JEFFERSON            HOMEWOOD                        4.000     3.000  1/01/05 RATE CHANGE
011171050 ALABAMA              SHELBY               VESTAVIA HILLS                                           NEW CITY
021850480 ALASKA               NORTH SLOPE BOROUGH  WAINWRIGHT                      0.000     3.000  1/02/03 RATE CHANGE
030070640 ARIZONA              GILA                 PAYSON                          2.120     2.000  1/01/05 RATE CHANGE
030130983 ARIZONA              MARICOPA             GOODYEAR                       100.00     40.00          MAX TAX AMOUNT CHANGE
040010903 ARKANSAS             ARKANSAS             ALMYRA                          1.000     0.000  1/01/05 RATE CHANGE
040070011 ARKANSAS             BENTON               LITTLE FLOCK                    1.000     0.000  1/01/05 RATE CHANGE
040451123 ARKANSAS             FAULKNER             VILONIA                         2.000     1.500  1/01/05 RATE CHANGE
050451260 CALIFORNIA           MENDOCINO            FORT BRAGG                      0.500     0.000          RATE CHANGE
050451260 CALIFORNIA           MENDOCINO            FORT BRAGG                      0.500     0.000          TRANSIT RATE CHANGE
171510750 KANSAS               PRATT                PRATT                           0.750     1.000  1/01/05 RATE CHANGE
171770000 KANSAS               SHAWNEE                                              1.150     0.900  1/01/05 RATE CHANGE
190632487 LOUISIANA            LIVINGSTON           SPRINGFIELD                     2.000     1.500  1/01/05 RATE CHANGE
190650000 LOUISIANA            MADISON                                              3.500     2.500  1/01/05 RATE CHANGE
190670080 LOUISIANA            MOREHOUSE            BASTROP                         3.000     2.500  1/01/05 RATE CHANGE
190730620 LOUISIANA            OUACHITA             MONROE                          1.000     0.500 12/01/04 COUNTY OVERRIDE CHANGE

U0437A Vertex Interface Audit reports changes in tax rates and exceptions.

• VERTEX GEO CODE
  The Vertex Geo Code of the changed record

• STATE
  The state of changed or exception record

• COUNTY
  The county of changed or exception record

• CITY
  The city of changed or exception record
• E
  
  Exceptions are flagged with an * (asterisk). An exception occurs when a location's rates could not be loaded, or when a location was deleted by Vertex. When Vertex deletes a location, U0437 LeasePak Vertex Interface does not delete the location from the Location (RLO) and Name (RNM) tables.

• NEW VALUE
  
  The new rate or amount is displayed.
• OLD VALUE
  
  The old rate or amount is displayed.
• EFF DATE
  The effective date of the changed record is displayed.
• DESCRIPTION
  Nature of change