Customizing Oracle Evergreen Check Printing

So, you decided that you must customize the Evergreen check printing process anyway. That's ok, I had to modify it, too.

So if you read this posting you know I said never ever change it. Well the business had to have certain changes, and most of these changes were needed. They do help speed up processing and cut down on costs.

Some things that you need to know about the evergreen print process. Evergreen uses the print driver file to store the escape character sequnces for formatting and placing various objects down to be printed. In the report design these are mostly found in little field boxes that make calls to the esc character code number (100,101,200,201 etc). This is a good listing of various escape seqences. HP ESC codes

The changes discussed here are the following:
1) print overflows from seperate tray
2) Addiong marks for envelope stuffers
3) sending second signature checks to a different output tray
4) modifying all overflows to go to overflow tray

First change, the ability to print your overflows from a seperate tray than your check. This way you are not printing all of the extra remittance pages on check stock. Not printing on check stock has two purposes. If you look at the code in the report there is logic when assiging the void and using the void section of the report. A seperate print driver code will need to be created that appears like the standard page except you make a call for printing to a different tray. For me all of the special print driver codes that I added were in the 400 range. You will also have to make changes to the orginal print code to print to the first tray for checks. Then the printer will need ot be configured to only use the designated tray for printing.
1) change exitisting print driver to print to specefic tray for check stock
2) add new driver that looks like 1 but prints to a different tray for overflow documents
3) change printer setup to only select paper designated tray only

Mailing large check runs is a tedious process and most companies utilize a envelope stuffer. These stuffers usually read marks on the paper to know when to start stuffing a new envelope. You will need to print driver codes for this. The first code tells where to start printing the mark. The second code returns the print curser back to where it was before printing the mark. This way you do not have to worry about all ofthe other print elements being moved or adjusted in ways that you are not expecting. In my case a double mark printing was used for a new document and a single mark notified the stuffer to continue stuffing.

Sending checks that require second signature to different output tray. Checks that required a second signature needed to be seperated, it was easier to just place it in the code to break it out for a different destination tray. This is also achieved by makeing changes to the print drivers.

We made an adjustment to include overflow checks as well. The stuffer wanted the checks to be the first document and not the last as the stndard program prints. The alternate tray leaves the checks face up instead of face down in the main output tray on top of the printer. This then placed the overflow in the correct way so that they too could be stuffed easily.

Single Source of Truth

One of the more common issues for businesses today is reporting on business activity. Reporting issues and the reasons for these issues are endless. If you are in business, you most likely have reporting issues.

Why do companies need reporting? To help the people in charge of running the company, run the company as efficiently and as effectively has possible. Every decision that is made has an effect on the bottom line. Decisions have a better chance of having a positive affect to the bottom line when the business has more accurate information about its activity.

Growth through acquisitions creates a lot of challenges. When one company acquires another, a lot of duplication is introduced. Not all duplication can be eliminated immediately, and some duplication may exist for years beyond the original acquisition date. This holds true especially with the IT solutions that handle, track, record, and report business activity.

Most of the IT solutions for each of the acquired companies are most likely different from the other companies’ solutions. These differences will range from systems that record business activity, to the nomenclature that each of the individual business use to describe their business activity.

Because of the differences between the acquired companies, multiple challenges exist when trying to report overall business activity.

Accurate reporting is essential for a company to be and remain successful. It is through reporting that informed decisions can be made.

The answer for this need for accurate reporting is something I call: The Single Source of Truth.

First Ruby Script

I wrote my first ruby script today. I’ve been playing with rails and trying things out by example for the past few weeks., but this is the first time a wrote something that I actually needed to get something done at work. One of the projects I worked on last year was upgrading our imaging solution for AP. The imaging solution was over 3 major releases old. We were behind a total of 17 dot releases, which would get us to a point where we could jump to the current version. This old version that we were on had started to develop issues that just couldn’t be solved. One of the major issues was that some users could not view the images. As best as we could determine at the time, incompatibilities had arisen due to changes in Java. The core product was written for Java 1.2 or 1.3. Very old and very unsupported. The imaging solution and almost everything that it used from 3^rd parties was no longer supported, but that is a different story.

Turns out it wasn’t the java it was the actual image file. Somewhere in the chain of scanning, and transmitting the image, some of the tif files have become slightly corrupted. I guess new versions of Java now check the file instead of just blindly displaying the image or displaying what it can display without so much of a warning that something is wrong..

So now I know that some of the image files are corrupt and need to be fixed. How do I get these corrupt files? The images are on an ftp server, and I have a query that gives me the file information based upon information stored on an invoice. Running a query and manually ftp’ing and retrieving the images is very time consuming. A single invoice could have several pages associated with it: Meaning several image files for a document since each page is stored as a separate image.

This would be a great place to try out ruby, perl is a pain to get working on a windows PC. Ruby turned out to be very easy to install. I did start off with two separate ruby scripts and then combined the two into a single script.

The first step was to try to ftp to the server and retrieve one of the files. Googling gave me this link from Ruby Docs . This helped me get to a point to ftp the file back. This saved some time, but it would be really helpful if I could query the Oracle Database and retrieve the files based on the query.

A query of “ruby oracle” brought back some very good articles. These articles are on Oracle’s site: Accessorize Oracle Database with Ruby , Ruby on Rails on Oracle: A Simple Tutorial , and Tips for Optimizing Rails on Oracle . I haven’t read all of the articles in their entirety, but it did narrow down what I had to do very quickly.

I downloaded Ruby/OCI8 at http://rubyforge.org/projects/ruby-oci8/ followed the directions for installing on a windows machine and was ready to try running a query. I took one of the example queries in one of the articles and modified it to meet my needs.

Connecting to the database wasn’t clear, and having text values contained within single quotes also gave me a few issues.

I’ve worked with perl’s DBI with other project’s so ruby’s was not that hard to figure out. The only issue I had was that some of the calls were not clear that they were calls. I’ve modified the connect line to reflect something more readable.

dbh = DBI.connect('DBI:OCI8:[DBINSTANCE]', 'user', 'password')

Ruby’s wiki had a How to Connect to Oracle page that was very helpful. My experience in the past with DB connection examples are that you always use the default DB name and have only one DB.

Once I got past the connect issue I ran into a new one where ruby wasn’t liking the single quote used in the query. This was easily resolved by double quoting the entire query.

Below is the end result. Start to finish including downloading and installing everything needed. 2 hours. This will save me hours of time now. With audit season coming on strong, the request of images that can’t be retrieved through the image solution are only going to increase.

--Start Script

require 'dbi'
require 'net/ftp'

dbh = DBI.connect('DBI:OCI8:[DBINSTANCE]', 'user', 'password')

rs = dbh.prepare("select FILENAME, substr(COMPLETE_FILENAME, 32,21) from [table]
where document_id = 952154")

rs.execute

#Connect to FTP Server to retrieve image files

ftp = Net::FTP.new('[ftp_server]')
ftp.debug_mode=true
ftp.login(user = "user", passwd = "password")

while rsRow = rs.fetch do #loop through query results

#retrieve image files returned by query
ftp.chdir(rsRow[1])
ftp.getbinaryfile(rsRow[0], File.basename(rsRow[0]))

end

rs.finish

dbh.disconnect

ftp.close

--End Script

Risks of outsourcing, knowledge retention

For the past 2 ½ years, I have had the privilege of working in an outsourced environment. The company employs a few developers who oversee on site developers and development being performed over seas. This model does have its advantages. We are able to ramp up development quickly as the demand on a project dictates. The cost of development and re-development of our Oracle Apps customizations is decreased. Actually the incremental costs are lowered or rise at a lower rate over time. This allows IT to be more responsive to business changes, and have more resources available to meet project demands.

Outsourcing has its limits and risks. The first thing to remember with outsourcing for Oracle Applications is that you are dealing with developers who will at most only have 1-2 years experience in coding and coding in the Apps environment. If you pay more you may get someone with 5+ year’s experience. These programmers may have access to internal resources on their side to assist them in meeting development requirements but will they utilize them? This lack of experience increases the need for your in house developers who are managing the projects and code base to be very knowledgeable of all of the processes and enforcing code standards. This makes your development group of people very valuable to the company, and leaves the company at risk of losing knowledge if you developer leaves the company.

Contracts rule the relationship between your company and the outsource company. After 2 years, I’m starting to see turnover with our onsite and off shore developers. Why? The number one reason is costs. Everyone likes their annual raises; they are semi written into the contracts. The issue is that the job market for an Oracle Apps developer usually raises the costs for a developer greater than what was written into a contract. This causes turnover. Older contracts lose their experienced developers to new contracts. Why? The new contracts can afford to pay the experienced developer more money. This leaves the older contract capped at a certain experience level 1-2 years on average.

What happens when you do not keep your in house developers who are managing your code base up to market on their salaries and benefits? Your developers tend to leave, usually not one at a time, but several in a span of weeks. If one developer is not happy about what he/she is making. Then there is a high chance that the rest are thinking the same. Especially after the first developer leaves and tells everyone what he/she will be making. If your company is like the companies that I’ve worked for, your custom processes are probably stored on a network drive somewhere. Most likely grouped by project, or some other sort of method that know one knows how or why, except maybe the people who have walked out the door. With your experience walking out the door, you’re out sourced solution starts to perform less efficiently. Development times lengthen without the proper guidance. Utilization of your existing code base decreases. IT’s relationship with the Business becomes strained because of delays in meeting business demands.

Remember outsourcing is a way for IT to become more responsive to the business’ needs and in meeting a business’ demand for services. Outsourcing is not necessarily a way to do more with less or save the company money. Outsourcing is a way to have development done on demand and not incur the extra overhead for keeping idle developers on standby until a project is ready for them to code. If this is kept in mind and your in house developers are viewed more as managers of your code based assets instead of replaceable overhead. Then you will have a successful out sourced experience. Otherwise you may end up with a solution that costs the same and has the same results as if everything had been sourced internally.

What is the average life of code?

I’ve been taking a look at some processes that process our EDI invoices. There are 3 main groups of programs. Only 2 groups are being currently used. There used to be four but that is a different story.

The business decided to add a third party vendor to monitor freight charges by the major freight carriers that the business uses. After a manual sampling of invoices it was determined that the business could save “millions”. The Third party vendor likes to use EDI for invoicing. Luckily these freight carriers were sending some invoices to us through EDI. I said some. Actually only for 2 locations and the second location was for only 1 carrier and was added late last year.

The current processes can only handle what they were written for. (the 1 location) We discontinued one process when the vendor started sending in duplicate invoice numbers. Different charges but same invoice number. That violated our unique constraint for invoice numbers on a vendor. Since then we have upgraded the database, the vendor was bought out by another freight carrier, and now that set of programs don’t work.

I usually don’t look at code, but with our OOD project going on, resources are tight. We are under a code freeze, no new programs can be developed and changes to existing ones can not be made unless it’s a production issue. I can’t get changes based on the premise it will save “Millions” of dollars. I usually have a good idea on how the code should look from what I see with the end results and what the developers tell me. The program is more flexible than what I was told it was. Instead of most everything was hard coded only about half was. I did confirm my worst fears. The program inserts directly into the AP invoice tables. BAD, BAD, BAD, BAD, BAD There is a perfectly good payables interface and interface process that has been around since at least 10.7.

One set of code was written in 1998, the other set in 2000. Both programs share a similar history after that. Updates to the code every 2 years or so. This got me to thinking. With some of the other issues I have to deal with. What is the average life of code? These two sets of programs where written 8 and 6 years ago, the latter being a lighter copy of the first. Hours have been spent writing the code, translating the 810 file into another file format that was easier to load and process. In total these 2 processes processed 20 invoices a month. Another issue I have with these 2 program sets, no cost savings except to the carrier since they don’t have to send paper detailing the thousands of shipments per invoice.

Even though the programs were originally written 8 and 6 years they have been periodically updated with major revisions every 2 years. These revisions either reflect changes in the business, or changes in the infrastructure. (upgrading from 10.7 to 11i). Two years seems like a reasonable timeline for the life of code in an ERP environment, from my experience and seeing some more examples at this job. I guess if I was going to apply this to the financial aspects of a project development I would limit the life to two years. You would need to achieve a target ROI using the two year timeline. Too bad the projects here are not analyzed with those criteria.