tag:blogger.com,1999:blog-57827919105259297082024-03-14T14:27:03.707+11:00Gary PiperA Practical Approach To Oracle E-Business Suite<br>
www.piper-rx.comUnknownnoreply@blogger.comBlogger16125tag:blogger.com,1999:blog-5782791910525929708.post-67554494665493038732013-07-23T14:52:00.000+10:002013-07-23T16:24:26.985+10:00Free OEBS SQL – Think Quality! Think Global!<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">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 </span><a href="http://www.piper-rx.com/pages/tips/think_global.pdf">http://www.piper-rx.com/pages/tips/think_global.pdf</a><br />
<br />
</div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;"><span style="font-size: 12pt;"><o:p></o:p></span></span> </div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<o:p><span style="font-family: Arial;"> </span></o:p></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-40289556813344744152012-05-15T14:12:00.000+10:002012-05-15T14:12:21.579+10:00Health tip #3 – Avoid having too many concurrent managers<span style="font-family: Arial;">Here we go again! After having written and blogged about
this topic a fair bit in the past, having too many managers is a problem I
still continue to see all too often. A good example is a site I worked with
recently; it had 20+ managers just to run the workflow background processes.<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Putting aside the need to address the excessive workflow background processes
(which is another problem in its own right) there are major issues with the
concurrent managers: <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Problem #1 – Allowing 20 workflow background processes to
run at once competes for the same resources on the same tables.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Problem #2 – Is one of basic queuing theory –i.e. 20
processes all competing for the same CPU and data resources.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">In theory, 1 CPU handles 2 concurrent processes so in this
case unless the site has 10 CPUs it runs the risk of CPU bottlenecking
problems. The sting in the tail is these problems become most evident when you
least need them to, like at high processing times such as month end.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">What to do? It’s a simple choice. In this case identify why
you <strong><u>think</u></strong> you need so many concurrent managers. You need to fix the cause and
cut down on the number of managers, not just focus on the symptoms.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Want to know more? Take a look at the paper <a href="http://www.piper-rx.com/pages/papers/cm101.html">Concurrent Manager 101 & 202</a> on the <a href="http://www.piper-rx.com/">www.piper-rx.com</a> website.<span style="mso-spacerun: yes;"> </span>In fact there is heaps of FREE information
and tips on all aspects of OEBS Applications Administration at PIPER-Rx.com so
why not check it out!<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<br /></div>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-5782791910525929708.post-55363373673350735762012-03-13T06:00:00.000+11:002012-03-13T06:00:47.881+11:00Concurrent Managers Health Tip #2 – Scheduled Request Issues.<span style="font-family: Arial;">Scheduled requests are one of the mainstays of OEBS
concurrent processing.<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">One of the benefits of recurring scheduled requests is they
are “set and forget” they just happily run and resubmit without you having to
do a thing. <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">The biggest issue I’ve found, and this applies to almost all
sites, is the “forget” component. I regularly find scheduled requests that have
been running for years that are no longer required, and also duplicate
scheduled requests which have come about because someone doesn’t know the
original request exists. Both these issues are a waste of processing resources.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Another issue with scheduled requests is that I often find
resource intensive requests originally set to run overnight now creeping into
the working day because the request is resubmitting from the END of the prior
run.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">You should periodically review your scheduled requests to
help avoid these issues.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: Arial;">Want to know more?<o:p></o:p></span></b></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Download the simple OEBS scheduled requests report ordered
by program - TOAD Reports format report.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<a href="http://www.piper-rx.com/pages/reports_free.html#_concurrent_requests"><span style="color: #2288bb; font-family: Arial;">http://www.piper-rx.com/pages/reports_free.html#_concurrent_requests</span></a><o:p></o:p></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Report ID - PRXCMS-002 <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Using this report you should be able to identify both your
duplicates and those requests with a resubmit of END. By multiplying the “Re-submit”
value by the “Resub Count” you can get some idea how long the request has been
resubmitting.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Refer to PAM tutorial 19 “When Scheduled Requests Go Wrong”<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<a href="http://www.piper-rx.com/pages/papers.html"><span style="color: #2288bb; font-family: Arial;">http://www.piper-rx.com/pages/papers.html</span></a><span style="font-family: Arial;">
<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-12408354380077972492012-02-03T11:39:00.001+11:002012-02-03T11:39:50.316+11:00Concurrent Managers Health Tip #1 – Rebuild your indexes!<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">This is really a simple one and you would be surprised how many
companies large and small overlook this.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">I have seen examples of sites with over 50 million rows in
the <b style="mso-bidi-font-weight: normal;"><i style="mso-bidi-font-style: normal;"><span style="color: blue;">fnd_concurrent_requests_u1</span></i></b> indexes; a huge
waste of space! More importantly however is the problem of poor performance in
such a core OEBS activity e.g. 10 concurrent manager processes running with a
30 second sleep time equates to 28,800 hits on your <b style="mso-bidi-font-weight: normal;"><i style="mso-bidi-font-style: normal;"><span style="color: blue;">fnd_concurrent_requests</span></i></b>
table and its indexes (this does not include your users submitting requests and
checking if their requests have completed). Get the picture why this is so
important.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">As part of regular maintenance practices you should rebuild
your indexes on routinely purged objects on a regular basis, especially the
indexes on <b style="mso-bidi-font-weight: normal;"><i style="mso-bidi-font-style: normal;"><span style="color: blue;">fnd_concurrent_requests</span></i></b>.<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Want to know if you need to rebuild your indexes? The
following article includes everything you need to know together with code and
there is also a TOAD report. These can be found on the PIPER-Rx “tips” page: </span><a href="http://www.piper-rx.com/pages/tips.html"><span style="color: blue; font-family: Arial;">http://www.piper-rx.com/pages/tips.html</span></a><o:p></o:p></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt 36pt;">
<span style="font-family: Arial;"><i style="mso-bidi-font-style: normal;"><span style="color: blue; mso-bidi-font-family: Arial;">Rebuilding indexes
in an OEBS environment</span></i><i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-family: Arial;"> - A guide to rebuilding indexes in an OEBS
environment <o:p></o:p></span></i></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt 36pt;">
<i style="mso-bidi-font-style: normal;"><span style="mso-bidi-font-family: Arial;"><span style="font-family: Arial;">View and Download associated <span style="color: blue;">TOAD report</span> (TRD format) - Report id - FDBA002-10 <o:p></o:p></span></span></i></div>
<br />
<span style="font-family: Arial;">Also remember some OEBS application monitors provide alerting
to remind you when your indexes may need maintenance.<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="font-family: Arial;">Happy Rebuilding </span><span style="font-family: Wingdings; mso-ascii-font-family: Arial; mso-char-type: symbol; mso-hansi-font-family: Arial; mso-symbol-font-family: Wingdings;"><span style="mso-char-type: symbol; mso-symbol-font-family: Wingdings;">J</span></span><o:p></o:p></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-30295491669005004812011-12-09T09:37:00.001+11:002011-12-09T09:50:48.528+11:00Improving the business efficiency of OEBS workflow. Part 2: Validating e-mail addresses<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US"><span style="font-family: Arial;">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.<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US"><span style="font-family: Arial;">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.<o:p></o:p></span></span></div>
<br /><span lang="EN-US"><span style="font-family: Arial;">The paper and the PLSQL function can be
viewed or downloaded at the following addresses<o:p></o:p></span></span><br />
<br /><span style="font-family: Arial;"><span class="normal"><span lang="EN-US" style="font-size: 7.5pt;">PDF
document, 500 Kb</span></span><span lang="EN-US"><o:p></o:p></span></span><br />
<span lang="EN-US"><a href="http://www.piper-rx.com/pages/papers/email_validation_part_two.pdf"><span style="color: blue; font-family: Arial;">http://www.piper-rx.com/pages/papers/email_validation_part_two.pdf</span></a><o:p></o:p></span><br />
<br /><span style="font-family: Arial;"><span class="normal"><span lang="EN-US" style="font-size: 7.5pt;">PLSQL,
8 Kb</span></span><span lang="EN-US"><o:p></o:p></span></span><br />
<span lang="EN-US"><a href="http://www.piper-rx.com/pages/papers/piper_rx_email_validation.sql"><span style="color: blue; font-family: Arial;">http://www.piper-rx.com/pages/papers/piper_rx_email_validation.sql</span></a><o:p></o:p></span><br />
<br /><span lang="EN-US"><span style="font-family: Arial;">-Gary<o:p></o:p></span></span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-47550824767224391212011-11-20T08:48:00.001+11:002011-12-09T09:41:00.712+11:00Improving the business efficiency of OEBS workflow. Part 1: Missing e-mail addresses<span lang="EN-US"><span style="font-family: Arial;">This paper is part one (1) of a two (2)
part series. Part 1 covers null e-mail addresses in your workflow roles objects
and in part 2 we covers e-mail address validation including a free 12 point PLSQL function
for validating e-mail addresses.<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US"><o:p><span style="font-family: Arial;"> </span></o:p></span><span lang="EN-US"><span style="font-family: Arial;">In this paper we cover the issue of missing
(null) workflow e-mail addresses:<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US"><o:p><span style="font-family: Arial;"> </span></o:p></span><span lang="EN-US" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">v<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span lang="EN-US"><span style="font-family: Arial;">The importance of a SYSADMIN e-mail address<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-bidi-font-weight: bold; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">v<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span lang="EN-US" style="mso-bidi-font-weight: bold;"><span style="font-family: Arial;">What happens when an invalid
e-mail address is encountered?<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">v<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span lang="EN-US"><span style="font-family: Arial;">Assessing the damage in your instance ( Free SQL )<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span lang="EN-US" style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-bidi-font-weight: bold; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">v<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";"> </span></span></span><span lang="EN-US" style="mso-bidi-font-weight: bold;"><span style="font-family: Arial;">And how to rectify the issue<o:p></o:p></span></span></div>
<br />
<span lang="EN-US"><span style="font-family: Arial;">The paper can be viewed or downloaded at
the following address:<o:p></o:p></span></span><br />
<b><span lang="EN-US"><span style="font-family: Arial;"><a href="http://www.piper-rx.com/pages/papers/email_validation_part_one.pdf">Part 1: Missing e-mail addresses<o:p></o:p></a></span></span></b><br />
<span style="font-family: Arial;"><span class="normal"><span lang="EN-US" style="font-size: 7.5pt;">PDF
document, 300 Kb</span></span><span lang="EN-US"><o:p></o:p></span></span><br />
<br />
<span lang="EN-US"><span style="font-family: Arial;">In part 2 of the “Improving the business
efficiency of OEBS workflow” series we will cover email address validation.
Part 2 also includes an 12 point email address validation function.<o:p></o:p></span></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US"><span style="font-family: Arial;">-Gary<o:p></o:p></span></span></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-38598368004612942892011-02-28T10:55:00.000+11:002011-02-28T10:55:34.149+11:00Did you know you can change an apps user’s account name?<span style="color: red;">First and foremost this is done via the normal apps Security > User > Define screen.</span><br />
<br />
Whilst the account name (<strong><span style="color: blue;">user_name</span></strong>) has to be unique which is enforced by the <span style="color: blue;"><strong>fnd_user_u2</strong></span> index, the internal application key is based on the account ID (<span style="color: blue;"><strong>user_id</strong></span>) not the name <br />
<br />
So why would you change an account name? <br />
<ul><li>A name change by deed poll or marriage </li>
<li>A new employee has the same name as an ex-employee </li>
<li>The user name was entered incorrectly </li>
<li>You want to implement a user naming standard </li>
</ul><strong>Scenario:</strong> Once a user is no longer with the company, end date the account and change the account name adding an Z_ to the beginning of the user name e.g. The account GPIPER becomes Z_GPIPER. Now the account name GPIPER can be used by a new employee. This has the added benefit of allowing you to filter reports excluding ex-employees, or getting them to sort last in your reports. <br />
<br />
Of course you will need to get this approach signed off by Oracle support and internal audit first. Usually, as long as there is consistent documented evidence of the change audit are generally ok <br />
<br />
You are not doing anything dodgy here, this is all done via the standard Oracle application screens (Security > User > Define) just select the user, change the users name and commit. <br />
<br />
<strong><span style="color: red;">Note:</span></strong> If you change the name of an active account, you will need to reset that user's password and let them know the password you have set and that they will be required to change their password on first connecting with the new name. <br />
<br />
Of course you wouldn’t even attempt to do this without checking with Oracle support and you’re your audit team before changing application user names and then testing, testing etc…..Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-71724732430039523382011-01-15T19:59:00.000+11:002011-01-15T19:59:22.962+11:00Rebuilding indexes in an OEBS environment<span style="font-family: Arial, Helvetica, sans-serif;">Index maintenance is all too often overlooked and can, over time, have a serious impact on performance. Ever growing indexes account for large amounts of wasted space and failing to perform regular index rebuilds on dynamic application tables is just throwing away performance.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">When APPLSYS has over a thousand indexes the OEBS application has over 60,000 indexes (version and module dependant) it often gets too hard to identify what indexes require rebuilding and they simply don’t get rebuilt. Believe it or not, most sites I visit have not rebuilt indexes on even the basic regularly purged objects such as concurrent requests and sign-on audit tables in years.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong>Example:</strong></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">A site I recently reviewed generates approximately 35,900 <span style="color: blue;">fnd_concurrent _requests</span> records per day which are being purged nightly holding 7 days history on-line. The indexes had not been rebuilt for over 4 years. Thus in this instance the base table should be holding approximately 251,000 records whereas the primary key index <span style="color: blue;">fnd_concurrent_requests_u1</span> would be holding approximately 52 million rows. 99.9% deleted rows. This is an interesting one when you calculate the space the index is holding, I estimate over 1.4 Gb for <span style="color: blue;">fnd_concurrent_requests_u1</span> alone.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">In this paper we will provide a simple list of indexes to start with, and a method of identifying if any of these indexes should be rebuilt.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">We also cover why you should exercise caution when using an auto index rebuild script you find on the web, some do not readily translate to an OEBS environment</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The full paper can be found at <a href="http://www.piper-rx.com/pages/tips.html">http://www.piper-rx.com/pages/tips.html</a> - Look for "Rebuilding indexes in an OEBS environment" in the tips section</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-21465804966579132522010-12-17T14:00:00.001+11:002010-12-18T06:39:39.216+11:00Aged User Accounts<span style="font-family: Arial, Helvetica, sans-serif;">Much like the Accounts Receivable ages debtor accounts function in any business, we can age application user accounts that have never connected to the application or have not connected in some time via the full and self-service screens.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Why would we want to do this? Having people who have long left the business retaining active accounts is a security issue, and all the best managed sites have processes in place to check that such accounts are being closed on a timely basis.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">It is possible to generate a list of application user accounts that have either never connected or have not connected in the last 120 days. </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The first step is to identify the number of accounts you have that are candidates for review:</span><br />
<br />
<span style="color: blue;">SELECT</span> count(*)<br />
<span style="color: blue;"> FROM</span> applsys.fnd_user fu<br />
<span style="color: blue;">WHERE</span> (fu.last_logon_date is null<br />
or (sysdate - fu.last_logon_date) >= 120 )<br />
and (fu.end_date is null<br />
or fu.end_date > sysdate )<br />
and fu.user_name not in ('SYSADMIN','GUEST');<br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The second step is to produce a list of application accounts that are candidates for review:</span><br />
<br />
<span style="color: blue;">SELECT</span> fu.user_id,<br />
fu.user_name,<br />
substr(fu.description, 1, 40) <br />
decode(sign(length(fu.description) - 40), 1, '...') description,<br />
nvl(to_char(fu.last_logon_date, 'DD-Mon-YY HH24:MI'), 'Never Connected')<br />
last_connect_date,<br />
decode(fu.last_logon_date, null, null,<br />
round( (sysdate - fu.last_logon_date),0) ) days_since_last_connect <br />
<span style="color: blue;"> FROM </span>applsys.fnd_user fu<br />
<span style="color: blue;">WHERE</span> (fu.last_logon_date is null<br />
or (sysdate - fu.last_logon_date) >= 120 )<br />
and (fu.end_date is null<br />
or fu.end_date > sysdate )<br />
and fu.user_name not in ('SYSADMIN','GUEST')<br />
<span style="color: blue;">ORDER by</span> decode(fu.last_logon_date, null, (sysdate - 10000), fu.last_logon_date),<br />
fu.user_name;<br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The output will look something like the following example:</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_-C78DMDmil8/TQrQm08Rh1I/AAAAAAAAAD8/G0czVF-yejY/s1600/aged_accounts.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="58" n4="true" src="http://3.bp.blogspot.com/_-C78DMDmil8/TQrQm08Rh1I/AAAAAAAAAD8/G0czVF-yejY/s320/aged_accounts.png" width="320" /></span></a></div><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong>Remember: Not all Aged User Accounts are candidates for end dating:</strong></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">There are of course exceptions, those accounts that must exist but are never used and those that are used very infrequently. You can exclude these by simply adding these accounts to your SQL statement:</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong> and fu.user_name not in (‘SYSADMIN’,’GUEST’)</strong></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">You should run the second report say once per month as part of your normal application maintenance routine and send it to your HR department or whoever manages your application user accounts. It shows the business you are on top of managing the application and the business loves anything to do with security...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-26726726112826916872010-11-19T12:49:00.000+11:002010-11-19T12:49:30.578+11:00Is the number of concurrent manager processes you have causing performance issues?<span style="font-family: Arial, Helvetica, sans-serif;">All too often I see sites with far too many concurrent manager processes and the site wonders why they have intermittent performance issues. Remember, having more concurrent manager processes does not mean more throughput.</span><br />
<br />
<strong><span style="font-family: Arial, Helvetica, sans-serif;">So, how many concurrent manager processes should you have?</span></strong><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Add up the number of standard and custom concurrent manager processes you have. If that value exceeds 2 times the number of CPUs (multi core adjusted) on the box you most likely have too many manager processes. If you are experiencing intermittent performance issues, particularly around high processing times like financial month ends, too many manager processes would most likely be one of your reasons.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The following SQL will list your concurrent managers:</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: blue;">SELECT</span> concurrent_queue_name,<br />
max_processes,<br />
<span style="font-family: Arial, Helvetica, sans-serif;"> running_processes,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> nvl(sleep_seconds,0) sleep_seconds,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> cache_size,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> decode(enabled_flag, </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> 'Y', 'Enabled', 'N', 'Disabled', 'Unknown' ) status</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> <span style="color: blue;">FROM</span> applsys.fnd_concurrent_queues</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: blue;">ORDER by</span> decode(enabled_flag, 'Y', 1, 'N', 0, enabled_flag ) DESC,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> max_processes DESC,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> decode(concurrent_queue_name,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> 'FNDICM', 'AA',</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> 'FNDCRM', 'AB',</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> 'STANDARD', 'AC',</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> concurrent_queue_name);</span></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><div style="text-align: center;"><strong><span style="font-family: Arial, Helvetica, sans-serif;">Example Output</span></strong></div><div style="text-align: center;"><a href="http://1.bp.blogspot.com/_-C78DMDmil8/TOXVCLmlDtI/AAAAAAAAAD4/jfHlLJP64-c/s1600/cm_list.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="57" ox="true" src="http://1.bp.blogspot.com/_-C78DMDmil8/TOXVCLmlDtI/AAAAAAAAAD4/jfHlLJP64-c/s320/cm_list.png" width="320" /></span></a></div><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong>A real world example of too many managers:</strong></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">A site I reviewed had 54 standard manager processes on a 4 CPU box - 54 / 4 = <strong>13.5</strong> </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">In this example there can be up to 54 concurrent requests running through the standard managers at any time which will just plain flood the CPUs. Generally running two (2) concurrent requests per CPU is sufficient to leave enough overhead for normal processing activities; any more than that and the risk increases of CPU flooding. And you would hope this site does run too many FSG’s... as we all know the damage they can do...</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Believe it or not I have even seen 108 standard managers on an 8 CPU box..... Hmmm..... </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Flooding the CPUs with concurrent requests leaves very little available CPU for normal user requests. As a result users tend to experience poor performance with their forms etc... It’s basic queuing theory... To make matters worse, these intermittent performance issues tend to occur around peak processing times when the concurrent manager load is at its peak; exactly the time users are clearly very busy and don’t want to be experiencing performance issues.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">If you have a “problem” with an excessive number of standard and custom concurrent managers processes, what you need to do is lower the number of processes. This is easier said than done, as once they exist the business is very reluctant to let them go. But it is worth persisting as it will make a difference!</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">For more information refer to the paper I wrote: </span><br />
<br />
<a href="http://www.piper-rx.com/pages/papers/cm101.pdf"><span style="font-family: Arial, Helvetica, sans-serif;">http://www.piper-rx.com/pages/papers/cm101.pdf</span></a><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Believe it or not, and much to my surprise, this is the most down loaded paper on my web site. Even though I wrote this paper in 2004, it still holds true today.... not much changes in OEBS, and for good reason; stability in accounting and business systems is what businesses want. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br />
</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-1560341724141752382010-11-06T11:59:00.000+11:002010-11-06T11:59:18.955+11:00Case Review - How did I manage to get 30 million rows in my fnd_logins table?<span lang="EN-US" style="font-family: "Arial", "sans-serif"; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-US;">The site in presented with over 30 million rows in the <b><i><span style="color: #002060;">fnd_logins </span></i></b>table, growing at a rate of approximately 25,000 records per day.</span><br />
<br />
<span lang="EN-US" style="font-family: "Arial", "sans-serif"; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-US;">Based on the site’s application activity, the estimated number of rows that should be held in the fnd_logins table should be around 800,000 records (i.e. holding 32 days history on-line). It is expected that this number should reduce post concurrent manager activity review.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The site was running the concurrent program Purge Signon Audit data (FNDSCPRG) daily as part of their normal maintenance program to purge the sign-on audit data and was unaware of the high growth rate in the sign-on audit tables.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">On review of the site’s scheduled requests it was found that the Purge Signon Audit data was being run daily with the date argument set to 10-Oct-06, however, “the increment date parameter each run” check box had not been checked when the scheduled request was created. As such the data parameter has not been incrementing with each run, thus with each run the program has been purging all sign-on audit records prior to 10-Oct-06. As a result any record added after that date has not been being purged.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">So, since 10-Oct-06 (Over 1,200 days at the time of presentation) the program has been running but purging nothing.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Whilst there are several inherent performance issues, the biggest impact to the application would be when the concurrent program runs. The concurrent program Purge Signon Audit data (FNDSCPRG) uses an un-indexed column start_time in its execution. As such the purge program will execute a full scan on each of the target tables in order to determine the rows to delete. So for this site, that would include a daily full scan of a 30 million to not find any rows to delete.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The case review shows how I would “Fix” the issue including deleting the unwanted rows and setting up a new Purge Signon Audit data scheduled program run. </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The full case review can be found at - </span><br />
<a href="http://www.piper-rx.com/pages/case_reviews/case_sign_on_audit.pdf">http://www.piper-rx.com/pages/case_reviews/case_sign_on_audit.pdf</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-52840899263035395662010-10-10T10:04:00.000+11:002010-10-10T10:04:24.193+11:00Better Date and Elapsed Time Reporting Formats for Business Users<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><strong>I am a business user, so why say to me “it ran for 0.049 days”?</strong></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">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:-</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">• A concurrent request run time of 1 hour and 10 minutes is commonly reported as:</span><br />
<ul><li><span style="font-family: Arial, Helvetica, sans-serif;">0.0486 days or</span></li>
<li><span style="font-family: Arial, Helvetica, sans-serif;">4,200 seconds</span></li>
</ul><span style="font-family: Arial, Helvetica, sans-serif;"> • 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…</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">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? </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">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.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The following is an example report showing both YY:MM:DD and DD:HH:MI formats:</span><br />
<div><span style="font-family: Arial, Helvetica, sans-serif;"> </span><a href="http://1.bp.blogspot.com/_-C78DMDmil8/TLDxw4VoowI/AAAAAAAAAD0/Fx9c7Jy1kNg/s1600/date_time_format.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" ex="true" height="157" src="http://1.bp.blogspot.com/_-C78DMDmil8/TLDxw4VoowI/AAAAAAAAAD0/Fx9c7Jy1kNg/s320/date_time_format.png" width="320" /></span></a></div><br />
<div><span style="font-family: Arial, Helvetica, sans-serif;"> The full article including example SQL can be found at: </span></div><span style="font-family: Arial, Helvetica, sans-serif;"> </span><a href="http://www.piper-rx.com/pages/tips/date_time_formatting.pdf"><span style="font-family: Arial, Helvetica, sans-serif;">http://www.piper-rx.com/pages/tips/date_time_formatting.pdf</span></a><span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<br />
<span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><strong><em>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!</em></strong></span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-32314723336400305762010-09-21T09:30:00.001+10:002010-09-21T21:33:16.185+10:00A workflow Case Review on how to lower the number of Workflow Background Processes<span style="font-family: Arial, Helvetica, sans-serif;">Having encountered a number of sites running upward of over 5,000 workflow background processes per day, in some cases over 15,000 per day, it never ceases to amaze me how so many sites get themselves into so much trouble running far too many background processes. Not only does this thrash your application but it also has an impact on your concurrent request tables as each request adds a record in the <span style="color: blue;"><strong><em>fnd_concurrent_requests</em></strong></span> table for each run.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Just recently I have been corresponding with a site that contacted me regarding issues with their workflow application and this has prompted me to put together a case review to articulate how I would look to resolve the issue of too many workflow background processes for the particular scenario outlined.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The full case review can be found on my web site:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><a href="http://www.piper-rx.com/pages/case_reviews/case_wf_bgp.pdf"><span style="font-family: Arial, Helvetica, sans-serif;">http://www.piper-rx.com/pages/case_reviews/case_wf_bgp.pdf</span></a><br />
<br />
<span style="font-family: Arial;"></span><span style="font-family: Arial, Helvetica, sans-serif;">It’s pointless looking for poor performing code with this issue…</span><br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="font-family: "Arial", "sans-serif";">This is a good example of my statement <strong><em><span style="color: blue;">“Not all performance issues are solved by code tuning”. </span></em></strong></span></div><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">-Gary</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-24515156436294254432010-09-13T16:01:00.000+10:002010-09-13T16:01:47.956+10:00Shortening large columns in user facing reportsWhere 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.<br />
<br />
To demonstrate this concept below I have used the application users description column in <span style="color: blue;"><strong><em>applsys.fnd_user</em></strong></span> which is a varchar2(240)...<br />
<br />
<strong><span style="color: blue;">SELECT</span></strong> fu.user_id user_id,<br />
fu.user_name user_name,<br />
fu.description description<br />
<strong><span style="color: blue;">FROM</span></strong> applsys.fnd_user fu;<br />
<br />
<a href="http://3.bp.blogspot.com/_-C78DMDmil8/TI29EWoaqYI/AAAAAAAAADc/0hzaGvTj9MY/s1600/description_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="53" ox="true" src="http://3.bp.blogspot.com/_-C78DMDmil8/TI29EWoaqYI/AAAAAAAAADc/0hzaGvTj9MY/s400/description_1.png" width="400" /></a><br />
<br />
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.<br />
<br />
<strong><span style="color: blue;">SELECT</span></strong> fu.user_id user_id,<br />
fu.user_name user_name,<br />
substr(fu.description, 1, 30) <br />
decode(sign(length(fu.description) - 30), 1, '...') description<br />
<span style="color: blue;"><strong>FROM</strong></span> applsys.fnd_user fu; <br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_-C78DMDmil8/TI289fgqI8I/AAAAAAAAADU/R12vJtNHemM/s1600/description_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="192" ox="true" src="http://3.bp.blogspot.com/_-C78DMDmil8/TI289fgqI8I/AAAAAAAAADU/R12vJtNHemM/s400/description_2.png" width="400" /></a></div><br />
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.<br />
<br />
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.<br />
<br />
Remember:<br />
<em><span style="color: blue;"><strong>“Always make life easier for your target user”</strong></span></em><br />
<br />
<br />
- GaryUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-58383155107526356162010-08-28T11:13:00.002+10:002010-09-01T07:08:50.067+10:00General Ledger - GL Program OptimizerThe General Ledger - GL program optimizer is a concurrent program that is available to the GL super user responsibility and it has two functions:<br />
<br />
<ul><li>Collect statistics to aid in the running of FSGs and posting</li>
<li>Build indexes on gl_code_conbinations where they do not exist.</li>
</ul><br />
The program collects and stores information on the spread of balances in <strong><em><span style="color: blue;">gl.gl_balances</span></em></strong> which is used by both the GL posting program and FSGs. Given that the number of balances in <strong><em><span style="color: blue;">gl.gl_balances</span></em></strong> increases each month with the month end roll process, it is important for the overall performance of posting and FSGs to keep the GL program optimizer stats up to date.<br />
<br />
<br />
<strong>When was the GL Program Optimizer last run?</strong><br />
The following SQL shows you when the GL program optimizer was last run and by whom:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: x-small;"><strong><span style="color: blue;">SELECT</span></strong> fu.user_name || ' - ' ||fu.description "Last Run By",</span></span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> to_char(max(rts.last_update_date),'DD-Mon-YY HH24:MI') "Last Run",</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> trunc(sysdate - max(rts.last_update_date)) "Age (Days)"</span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: x-small;"><strong><span style="color: blue;"> FROM</span></strong> rg.rg_table_sizes rts,</span></span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> applsys.fnd_user fu</span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: x-small;"><strong><span style="color: blue;"> WHERE</span></strong> rts.last_updated_by = fu.user_id</span></span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-size: x-small;"><strong><span style="background-color: white; color: blue;"> GROUP by</span></strong> fu.user_name, fu.description;</span></span><br />
<br />
It is not uncommon for the program to have never been run.<br />
<P>This program is run by logging onto the GL super user responsibility and then running the concurrent program - GL Program Optimizer.<br />
When running the GL Program Optimizer there are two options:<br />
<ul><li>Collect Statistics (Default Yes) -Set this value to Yes</li>
<li>Rebuild Indexes (Default No) - Set this value to No</li>
</ul><br />
<strong><span style="color: red;">Warning:</span></strong> Do not set the Rebuild Indexes option to Yes as this could cause more damage and may have an adverse effect on the entire General Ledger application. FYI Older releases dropped existing code combination indexes and rebuild indexes as defined in the flexfield definitions. Current releases will maintain existing indexes and build only those that have been defined in the flexfiled definitions that do not currently exist.<br />
Given the above warning, what we don’t want is any of the GL super users finding the GL program optimizer program and running it thinking they are doing the right thing; I have been there and it’s not pretty. That’s why I always remove it from the GL super user responsibility and add it to the Systems Administrator responsibility which they cannot get to.<br />
<p><strong>How often should GL Program Optimizer be run?</strong><br />
<p>I generally set this up as a scheduled job to be run once per month, a couple of days after the month end roll over.<br />
<p>Good Luck....Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5782791910525929708.post-61546391028306919682010-08-13T15:48:00.001+10:002010-08-14T15:57:38.719+10:00What OEBS Application modules are installed?<span style="font-family: Arial, Helvetica, sans-serif;">The following genreates a list of the OEBS Application modules you have installed including their version and patch levels...</span><br />
<br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;">SELECT fa.application_id appn_id,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> decode(fa.application_short_name,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> 'SQLAP', 'AP', 'SQLGL', 'GL', fa.application_short_name ) appn_short_name,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> substr(fat.application_name,1,50)</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> decode(sign(length(fat.application_name) - 50), 1, '...') Application,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> fa.basepath Base_path,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> fl.meaning install_status,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> nvl(fpi.product_version, 'Not Available') product_version,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> nvl(fpi.patch_level, 'Not Available') patch_level,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI') last_update_date,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> nvl(fu.user_name, '* Install *') Updated_by</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> FROM applsys.fnd_application fa,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> applsys.fnd_application_tl fat,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> applsys.fnd_user fu,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> applsys.fnd_product_installations fpi,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> apps.fnd_lookups fl</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> WHERE fa.application_id = fat.application_id</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fat.language(+) = userenv('LANG')</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fa.application_id = fpi.application_id</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fpi.last_updated_by = fu.user_id(+)</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fpi.status = fl.lookup_code</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fl.lookup_type = 'FND_PRODUCT_STATUS'</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;">UNION ALL</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;">SELECT fa.application_id,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> fa.application_short_name,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> substr(fat.application_name,1,50)</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> decode(sign(length(fat.application_name) - 50), 1, '...'),</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> fa.basepath,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> 'Not Available',</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> 'Not Available', </span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> 'Not Available',</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI'),</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> nvl(fu.user_name, '* Install *') </span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> FROM applsys.fnd_application fa,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> applsys.fnd_application_tl fat,</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> applsys.fnd_user fu</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;">WHERE fa.application_id = fat.application_id</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fat.language(+) = userenv('LANG')</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fa.last_updated_by = fu.user_id(+)</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> and fa.application_id not in </span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> ( SELECT fpi.application_id</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;"> FROM applsys.fnd_product_installations fpi )</span><br />
<span style="font-family: "Courier New", Courier, monospace; font-size: x-small;">ORDER by 2; </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong>Example output:</strong></span><br />
<a href="http://4.bp.blogspot.com/_-C78DMDmil8/TGTbBLYlvvI/AAAAAAAAAC8/hqiMMM08LJY/s1600/application_list.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="136" ox="true" src="http://4.bp.blogspot.com/_-C78DMDmil8/TGTbBLYlvvI/AAAAAAAAAC8/hqiMMM08LJY/s640/application_list.PNG" width="640" /></a><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong><span style="color: red;">Note:</span></strong> For readability I have converted the SQLAP to AP and SQLGL to GL – this looks so much better for users...</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Hope this is useful....</span>Unknownnoreply@blogger.com0