Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form
John Keymer

John Keymer

My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.


A question that comes up all the time on the OTN Forums is "How do I know who updated this record?". Generally the response is to point the member in the direction of the last_updated_by column on the table. The next question often then comes up... So how do I see what the old value was?". Hmmmm... a little more tricky. We can try to look at flashback query however if the update was more than a few hours ago on a busy OLTP system then the chances are the undo has been cleared out. Sometimes we get lucky and the table holds a date-tracked history (HRMS tables for example). But usually it's just tough luck - once the data has been overwritten, it's gone (without resorting to backups).
So if you have some important tables in your E-Business Suite system that you wish to track changes on such as this, then you might want to consider enabling AuditTrail on those tables. It doesn't require any additional licences and is relatively easy to configure. Rather than just narrating what it does, I'll explain with an example.

Assume we have a concern that somebody might update the definition of a form function in EBS and do things we should be tracking; things like altering the parameters to the form. First we need to know what table(s) the data is being maintained in. There are several ways to do this, however often the easiest it to simply do Help > Record History or Help > Diagnostics > Examine > [SYSTEM|LAST_QUERY] and track the view through to the base table(s). So in our case it is fnd_form_functions.

Form Last Query

In order to use AuditTrail the table needs to be registered with the application. For standard tables this is (generally) done - you can check for the existence of a record in FND_TABLES - however for custom tables you'll need to register the table using ad_dd.register_table and ad_dd.register_column, then register the primary key of the table using ad_dd.register_primary_key and ad_dd.register_primary_key_column. Unfortunately there isn't a screen with update capabilities to do this. What I do for custom tables is use a script which reads the data dictionary for a given table and does all this for me. Anyway, once we have the table registered, we need to enable the owner of that table for auditing. In the System Administrator responsibility, navigate to Security > AuditTrail (all our configuration will be done via this menu path) and select the Install option. For our table, fnd_form_functions, the owner is APPLSYS.

SQL> Select owner, table_name
  2  From all_tables
  3  Where table_name='FND_TABLES';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
APPLSYS                        FND_TABLES

In my vision instance this user is already enabled.

Enable User for Auditing

Next we need to create an Audit Group - this is a logical grouping of like audited tables. Navigate to the Groups menu option and we'll create that. Note that we have to set the initial status to Enable Requested. This is very important.

Last modified on Continue reading
Tagged in: E-Business Suite
in Technical 225 0
0

Many clients I have worked for have expressed an interest in a graphical organization chart generated from their E-Business Suite ERP system. Unfortunately the org chart diagrammer in EBS isn't really all that great.

EBS Diagrammer
And the strategic Oracle reporting tools (Business Intelligence etc) are even worse!

It's actually surprisingly difficult to render a tree structure - it can be done purely using lists and CSS, and whilst extremely powerful, it can become difficult to manage for a beginner. So I thought I'd present another option - using Google Charts Organization Chart API (which I suspect is just a wrapper around HTML lists anyway). I will do this for now in Oracle APEX however the principle applies equally to other technologies (I will possibly post follow-ups with OBIEE etc in the future).
So how does the API work? Basically we include the API on our page and then make calls to the various methods provided. The developer page has a simple example that we can use as our starting point. Please take a quick look over there before continuing reading.

Ok, so let's start off. First of all we will create a new APEX desktop application. I am using the very latest APEX 5.1.1.00.087 build, however this should apply to most recent versions.

APEX Application

Next we need the query which will generate the hierarchy. The nice thing about this is that Google charts does that for you if you provide parent/child tuples, so there is no need to traverse the hierarchy ourselves. The following simple query gives us the current primary organization structure records.

Select org_parent.name parent, org_child.name child
  From per_organization_structures pos,
       per_org_structure_versions posv,
       per_org_structure_elements pose,
       per_business_groups pbg,
       hr_all_organization_units org_parent,
       hr_all_organization_units org_child
 Where posv.organization_structure_id = pos.organization_structure_id
   And pose.org_structure_version_id = posv.org_structure_version_id
   And pbg.business_group_id = pos.business_group_id
   And org_parent.organization_id = pose.organization_id_parent
   And org_child.organization_id = pose.organization_id_child
   And Trunc(Sysdate) >= posv.date_from 
   And (Trunc(Sysdate) <= posv.date_to Or posv.date_to Is Null)
   And pos.primary_structure_flag = 'Y'
   And pbg.name = 'Vision Corporation';

Now we'll create a region to display our org chart. The demo on Google goes this by placing the API calls in the Head section of the page, however we can equally do it in the Body section. There are a number of different ways to pushing the data to the API - looping through the rows, as a JSON structure etc - to keep it simple I'm going to use the former and PL/SQL Dynamic Content region. Including the API calls, and calls to Htp.P to output the HTML, we get something along the lines of this as a starter for ten.

Last modified on Continue reading
Tagged in: APEX E-Business Suite
in Technical 428 0
0

I was inspired to post this blog by a post on the OTN Forums where a user was asking about calculating the consumption of hours against a target - that is, given a target of X hours with N hours per day, how many days would it be until they reached the target? I knew I had done similar things several times in the past, and each time I always seem to forget the actual logic and end up having to look it up again. So hopefully this post will also be also be a little reminder for me if nothing else.
Queries such as this can actually take many guises, but they all share the same underlying principle. A common use is in a warehouse environment where we have stock located in many different locations (because we can of course only fit a finite quantity of an item in a single location/box/storage unit etc) and we get a big order for an item placed. We want to iteratively pick all the items from each locator until need less than is in a locator, then we pick just the amount that is remaining.

Warehouse Diagram

Using the above example, if we had a request for item ABC of quantity 60, we could easily walk from left to right and pick 20 from Locator A, 30 from Locator B, and then the remaining 10 from the 20 in Locator D.
Often though there are requirements for stock usage in warehouses such as FIFO (First In, First Out), which basically translates to Pick the oldest stock first. This is because we don't want stock with a limited shelf life perishing whilst pickers only ever take the stock which is nearest the door!

I've seen a number of different implementations of this within an Oracle environment, some good, some not so good. The latter tend to be approaches which loop around in PL/SQL and count up the quantity from each location. What I want to do here though is demonstrate a pure SQL way of doing this using Analytic Functions. It's not particularly complicated, but it's one of those things that when you come to do it you end up scratching your head for ages trying to remember how to do it!
I'm also a big fan of do it in a single statement where possible/sensible - there's something quite satisfying with solving (seemingly) complicated problems within a single query/command :) .

Last modified on Continue reading
Tagged in: sql
in Technical 346 0
0

With every major release of Oracle Applications in recent years there has been a new look and feel for OAF pages. BLAF (11i), Swan (R12.1) and finally Skyros (R12.2). One would imagine that once R12.3 comes out (or even the latter end of R12.2) it will be with the Alta skin - R12.2 already has some nods towards this.
Customer response to the E-Business Suite look and feel certainly is a mixed bag. Many dislike the washed out feel of Swan, some feel BLAF is far too dated and others simply want their ERP system to match their corporate branding.
The Oracle Forms modules are pretty much how they come - there are number of different colour themes you can apply such as red, blue, purple and green, however there isn't really a great deal to change. OAF pages however are somewhat different. They do have a very distinct look and feel. Fortunately, it is possible to customise these to great lengths, however unfortunately it's not all that intuitive. Hopefully this blog will help get you started. I will be using an E-Business Suite 12.2.4 vision instance, simply because it's what I have to hand as a pre-built VM on my laptop. The same applies to all releases though.

First you need to give yourself the Customising Look and Feel Administrator responsibility. It only has one option.

CLAF Responsibility

Once we enter the responsibility we will choose Create Look and Feel

Step 1

Depending on the version of EBS you're using, the options you'll see for the base look and feel will vary. I'd suggest choosing simple-desktop to begin with.
Clicking Next takes us to the page where everything is done. However before I jump into that (we'll come back to it shortly) we will click through and save the look and feel so we can demonstrate the starting point.

Last modified on Continue reading
Tagged in: E-Business Suite
in Technical 502 2
0

After being announced at Oracle Openworld 2016 for cloud-only, 12.2 is now finally available as an on-premise release and available from Oracle E-Delivery 

12.2 E-Delivery

We've been using it in the Cloud for a while here at Beyond on our Exadata instance and you may have already seen my previous blog posts on some of the new features such as Analytic Views. More to come soon.

Last modified on Continue reading
in Technical 650 0
0