The Rolox file was designed by me in 1995 at the request of the owner of the company. He did not want to use Outlook contacts and wanted it in green screen. He also needs three fields to identify who would get a Christmas card, party invitation or wreath sent to an address as well as the standard contact information fields. The client wanted a one stop process for looking up phone numbers by name.
Simple enough; I created the file with fields as needed and a maintenance program to add, update or change records in the file. There are three different types of records in the Rolox file, entries of people that are neither customers or vendors, customers from VAI VARCUST file and vendors from VAI VAPVEND file. This process is created on the iSeries in RPG III and worked as required for six years.
I opened the Rolox file and through my due diligence I realized something is not right. I ran some SQL over the files to determine what the current status of the Rolox file and found many discrepancies.
select * from r37files/vapvend where not exists
(select * from r37ceudta/rolox where acdel = rxstus and acvend = rxid and 2 = rxecd);
select * from r37files/varcust where not exists
(select * from r37ceudta/rolox where rmdel = rxstus and rmcust = rxid and 1 = rxecd);
There were thousands of records that did not exist in the Rolox file. This should not be the case since both the VAI customer maintenance and vendor maintenance programs were modified to add, change, delete from the Rolox file or at least so I remembered.
I then started looking at the VAI maintenance programs and found that the programs were not in the VAI modified library. This was a little disturbing. Before going any further I call the client and asked him to show me exactly where he was seeing the problem. First I realized that there are multiple menu options for the Rolox, maintain/edit and search. The client is using the search option and has not used the maintain/edit option in years.
At this point it all came back to me, back in 2000 I upgraded the client VAI S2K from version 3.2 to 3.7. During the planning stage I took advantage of the upgrade to get rid of the Rolox file and use the VAI customer and vendor files exclusively. The only additional fields are the party, card and wreath fields. Version 3.7 has a new feature called user defined fields. User defined fields can be created for the following files:
1. Setup
2. Item
3. Item Balance
4. Vendor
5. Chart of Accounts
6. Customer
7. Contacts
8. Ship-To
9. Prospect
10. Opportunity
I had completely forgotten that I sold the client on allowing me to create a new inquiry program that would eliminate the Rolox file and only use the customer and vendor master files.
So the fix is a two second fix; I changed the following logical files to select records equal to status active only. They were originally coded as COMP(NE 'D')
-----------------------------------------------------
VARCST90 - AR CUSTOMER MASTER LOGICAL BY NAME
-----------------------------------------------------
R ARCUSTR PFILE(VARCUST)
K RMNAME
S RMDEL COMP(EQ 'A')
--------------------------------------------------
VAPVND90 - AP VENDOR MASTER LOGICAL BY NAME
--------------------------------------------------
R APVENDR PFILE(VAPVEND)
K ACNAME
S ACACT COMP(EQ 'A')
Recompiled the logical files and program.
I have done so many installations and modifications over the past ten years for many different clients and employers, I guess this one just slipped by me.
The client is happy and I am not charging him for a two second change that took me all day. While I was doing this I also installed and configured SQL Explorer on my PC and practiced SQL UPDATE, INSERT INTO, WHERE EXISTS, WHERE NOT EXISTS and SELECT. So not a total loss.
I have removed the old menu option and Rolox file from the system as I should have done years ago.
It is a good feeling to know what I created ten years ago was the right thing to do and will stand the test of time.
~Richard
No comments:
Post a Comment