Free OEBS SQL – Think Quality! Think Global!

I am amazed how often I see the very same piece of code used by many different bloggers, often with very little thought as to the quality of the code or the audience it’s going to. We all want to get the most out of free code and avoid the pitfalls, so here’s some things to consider for bloggers and users alike. Full article http://www.piper-rx.com/pages/tips/think_global.pdf

 

 

 

Improving the business efficiency of OEBS workflow. Part 2: Validating e-mail addresses

This paper is the second of a two (2) part series. Part 2 covers e-mail address validation using a 12 point PLSQL e-mail addresses validation function.

Using the PLSQL function we cover e-mail validation of addresses in both the wf_local_roles and wf_local_users objects and in a number of base application objects that populate the workflow roles and users objects.

The paper and the PLSQL function can be viewed or downloaded at the following addresses

PDF document, 500 Kb
http://www.piper-rx.com/pages/papers/email_validation_part_two.pdf

PLSQL, 8 Kb
http://www.piper-rx.com/pages/papers/piper_rx_email_validation.sql

-Gary

Better Date and Elapsed Time Reporting Formats for Business Users

I am a business user, so why say to me “it ran for 0.049 days”?

All too often I see reports created by technical staff and DBAs being presented to management and business users with elapsed times (such as concurrent request run times) calculated in days or seconds and this often causes frustration amongst report recipients. Here are some typical examples:-

• A concurrent request run time of 1 hour and 10 minutes is commonly reported as:

• 0.0486 days or
• 4,200 seconds

 • An incident report that states the issue occurred 20-Sep-10 12:24:23. What is often more helpful to the user is to understand the day of the week of the incident; was that a Monday (our high processing day) or a Tuesday? And seeing the seconds reported usually adds no value at all…

What you should remember when dealing with management or users is they usually hate having to perform mental gymnastics to calculate a time, what is 0.02 days or 1,454 seconds?

Would it not be better if the time was quoted as 28 minutes? I believe it’s always preferable when presenting information to management and business users to convert elapsed time to Days, Hours and Minutes.

The following is an example report showing both YY:MM:DD and DD:HH:MI formats:

 

 The full article including example SQL can be found at:

 http://www.piper-rx.com/pages/tips/date_time_formatting.pdf

Remember; always make life easier for your target user. They will appreciate it and in turn will be more likely to be supportive when you need it!

Shortening large columns in user facing reports

Where a database column and its content is larger than the size you wish to show in a report, it is easy to just substring the column value to fit your report column size. However, this often does not look that good in user facing reports.

To demonstrate this concept below I have used the application users description column in applsys.fnd_user which is a varchar2(240)...

SELECT fu.user_id user_id,
               fu.user_name user_name,
               fu.description description
   FROM applsys.fnd_user fu;



In most cases when you write a report you substring the column to the size of the column in your report. Whilst sub stringing the column value is the easy solution and in your mind acceptable, you should remember your reports are aimed at end users wouldn’t it look more professional if you added three (3) full stops to a sub stringed value to clearly indicate the column has been truncated.

SELECT fu.user_id user_id,
                fu.user_name user_name,
                substr(fu.description, 1, 30)
                decode(sign(length(fu.description) - 30), 1, '...') description
FROM applsys.fnd_user fu;

If your report column size is 30, set the two (2) values in the example SQL to 27, that is 27 characters plus 3 for the full stops.

A very simple piece of code but what a difference. You should consider using this for any user report where a data column that has been truncated.

Remember:
“Always make life easier for your target user”


- Gary