Sunday, July 29, 2012

iSeries SQL Function Proper Case.....

One of my clients asked me to update the tax processing currently working in VAI System 2000 on the iSeries. The requirement is set up the county tax file and update the customer master with the proper county code based on the city field in the customer master.

Challenges abound, the city field in the customer file is mixed with all capitals, all lower case, proper case and misspellings. And yes, I have to learn how to fill out Florida sales tax form, which is where we find a $5000.00 limit on county tax. Always a twist somewhere.

First I looked for availability of current taxes for the State of Florida with city, county and current tax. I found Tax-Rates.org and for 39.00 bucks with the information needed, I downloaded CSV, changed to Excel and uploaded to the iSeries using Data Transfer To IBM i. This enables me to create the file on the IBM i automatically during the upload.

Once I have the file on the IBM i I have to overcome the case problem with the RMCITY field. There should be a way to do this without any code. I can do it in RPGLE using embedded SQL no problem but know there must be a way with just an SQL UPDATE statement.

Well, sort of correct, SQL does not have a PROPER function but I found this code about Functions with downloadable code, cool. First brush up on Control Statements and Create Function article by Scott Klement. It did not take me to long to figure out. I was tripped up on the definition of the inbound field but soon realized @TCSTRING VARCHAR(100) is the data field being returned.

I opened System i Navigator and expanded Databases so I can see Functions.




Right click and select New - SQL and the following is displayed -

 

Change the following -


Function = PROPER,
Description = Change Case To Proper
Type = VARCHAR,
Length = 100
Check = Same result returned from …....(Deteministic)
Data access = Modifies SQL data
Specific name = PROPER




Select the Parameters tab and then Insert, the following is displayed -





Change the Parameter Name to @TCSTRING, change Type to VARCHAR, Length to 100  and check of Return Null on Null input.





I downloaded the code and opened in notepad. Copied from BEGIN to END. I left out the SQL parameters since we are selecting them in the wizard. There are easier ways to just create the Function from the copied code, but the wizard breaks it out and explains it a little better.





Click on OK and the Wizard closes and the following should be displayed.




Now we can generate the SQL, right click on PROPER and select Generate.

If I need to change a field to be consistent like the city field in customer master I just run the following SQL statement.

update transfer.VARCUST
set rmcity = qgpl.proper(rmcity)

Several things to figure out, why library where the function is located in has to be specified, I thought it would come from the library list. And I did not take to the time to understand all the checkboxes in the wizard. The function performed as I needed and enabled me to continue on with my project.

I finished identifying the missing city county combinations and update the list.

The following SQL updated the county code field in the customer master based on the cross reference FL_CTYTXa file. There is a little problem with the SQL and I am looking for how it should be done correctly but what I created worked and updated the field as desired.

update transfer.varcust set rmcnty =
(select rkcnty
from transfer.VARCNTY, transfer.FL_CTYTXa
where city = rmcity
and salestaxregion = rkdesc
and rmstat = 'FL'
)
where exists
(select *
from transfer.VARCNTY, transfer.FL_CTYTXa
where city = rmcity
and salestaxregion = rkdesc
and rmstat = 'FL'
)

Previously I update the VARCNTY county tax file with the Florida taxes.

Now on to identifying where to put the hook in tax processing to only charge county tax on the first $5000.00 and testing.   

Any suggestions or questions, please send them on.

~Richard

Talk doesn't cook rice.  ~Chinese Proverb

Sunday, July 22, 2012

Next challenge, EDI integration....

My intended daily blog has turned into a when I can blog. This is a good thing since it means I am getting paid.

My current contract analyzing and documenting EDI processes for a major retailer in preparation for replacement of software or full outsourcing. The software currently being used is Trusted Link on an iSeries with a flat file IFS interface to Retail merchandising System based on Oracle.

If kept in house the software mandate is to run on Microsoft platform and all processes discontinued on the iSeries. Interface architecture is to be replaced with an Enterprise Bus Service package that will be used across the organization. I am also being asked to help with implementation new WMS from Highjump and TMS from Mercurygate software which requires interoperability with EDI processes.

Two potential software candidates that can fill the EDI and Enterprise Bus Service requirements are Extol EBI and Microsoft BizTalk.

This is exciting stuff and I am being given the opportunity to use my iSeries, EDI, integration, warehousing, distribution and management skills to help move a fast paced environment from a single ERP based system to a best of breed software using the cloud where possible environment. I will be getting exposure to Oracle, Cloud processing and Microsoft products such as SSIS, BizTalk and .Net.

I do still want to keep my iSeries programming skills up and am fortunate to have active clients requiring programming development and IT assistance. I am currently working on updating a System 2000 tax processes.

Have a great week,

~Richard

A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila.  ~Mitch Ratcliffe