Wednesday, September 21, 2011

PO cuts project...

It's hard to believe that it's been ten days since my last post. I have been really busy polishing up my RPGLE Free and SQL skills. Thank fully I am able to do this while making some money to pay the bills.

I finely reverse engineered VAI's S2K send mail program. Many thanks to Scott Kelment and his published article on QtmmSendMail API. I now know the nuts and bolts of sending HTML mail from the iSeries. I always knew the capability was there just never had the time to figure it out. At this point I can embed SNDMAIL in any custom program to send HTML in the body of an Email message using a source physical file member.

While I was looking a Scott's QtmmSendMail information I discovered he published the complete code to retrieve SMTP address from the IBM directory entries by user name. This was another great example of using IBM API's. I guess my only fear is that IBM would change a API on subsequent releases. I hope this does not come back to haunt me but I truly believe in using what is available rather than reinventing the wheel.

I also showed Jason Vickery Surveyor/400 from Linoma software. This software came up in conversation regarding the future upgrade of the iSeries hardware and software for NanjingUSA and a replacement for WRKDBF. WRKDBF is a great product for free (as it was for years) and priced at $449.00 is still ok but for $1000.00 Surveyor/400 was a no brainer. I downloaded / installed the product, and Jason was thrilled. He now had a windows based software with a ton of features such as:

* View and convert spooled files to a PDF or TXT format

* View, add, change and delete database records using a spreadsheet-like interface

* Download files to your workstation or to the IFS into delimited, fixed, XML or HTML files

* Upload text files into iSeries database files (MUCH easier and faster than Client Access)

* FTP objects between iSeries

* Create, run and save SQL statements

* Run 5250 screens with a built-in emulator

* Automate data transfers from the PC or System i

* Display and change data areas

Surveyor/400 has a neat feature that allows you to export files to the IFS in Excel format. The command EXPDTA can be embedded in a CL. I could have just modified and used code publicly available to create my own custom command but since we now have Surveyor/400 I decided to use their command instead.

In reading the EXPDTA documentation I discovered that Surveyor/400 required Java 1.5 on the Iseries. I only had 1.4 installed. I loaded the IBM licensed program from CD and reapplied the latest Java PTF group. It's kind of neat to do all this remotely. Jason inserted the CD and I completed the rest of the process myself.

So armed with all this wonderful information I completed client request to enhance a program. A few years back I designed, and retained Rick Santiago to write, a custom backorder program based on orders in VAI S2K for Wal-Mart. Orders from Wal-Mart come in different flavors such as ship to store, ship to Wal-mart.Com warehouse, ship to warehouse and DSDC (ship to DC mark for store). The DSDC orders are a little challenging in the fact that many orders are created for a single PO. Users run the program by selecting customer (built for future enhancement), invoice date from and invoice date thru. The data is output to a file that is manually transferred from the iSeries and imported into Excel. Once in Excel the headings are changed and then the spreadsheet is emailed.

At the time Wal-Mart was ship complete or cancel order customer. I was also getting ready to take a new position and relocate to Jacksonville. I was a little short on time so the process was not very pretty but functional.

The new requirement is to add the original order quantity to the spreadsheet and since I was working on the program, automate the process as much as possible.

I delivered the completed programming Monday and works great. The user enters Customer, Invoice date from and Invoice date thru and a few minutes later receives an E-mail with a link to the file on a network share. The Heading is 14 and bold and the column names are 12, bold and underlined.

I accomplished all the goals and then some. The link in the Email was the cherry on top. It just tickles me that I know longer have to send files attached in Email and that I was able to automate the whole process.


Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more, and more expensive clerks even though they call them "operators" or "programmers." ~Peter F. Drucker

No comments:

Post a Comment