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.


Whilst working on a client project recently I created a page in APEX with a number of different regions, selectable via a Region Selector. The way this page was to be used, the user may not always want to click on every single region each time they use the page. Unfortunately the default behaviour with APEX is that all regions are rendered on the page at load time, so if some queries take a short while to run then your user is waiting for data to return that they aren't even going to use.
What I really wanted was for the code in the region to only actually run when the user chose the region from the selector. I even posted on the OTN Forums to ask if anyone had done similar in the past. Ultimately though it seemed nobody has, so I thought I'd give it a try.

To explain the concept first, this idea works by creating a page item which is checked in a query predicate. i.e.

Select *
  From my_table
 Where :p1_show_data = 'Y';

We take advantage of the fact that the optimizer can deduce that if :p1_show_data does not equal 'Y' then the query is going to return all rows.
By exploiting that, we can devise a solution where our region queries check the value of an item which is empty when originally rendered, however is populated when the region is refreshed - and we trigger a refresh of the corresponding region when the user selects a tab.

Here is how I achieved this (note - demo done in APEX 5.1.2, however should be backwards compatible across at least APEX 5.x).

First we create a hidden page item that restricts the queries. I created one called P1_SHOW_REGION. Then we modify our queries to take advantage of this.

select * from table(delay_table(5))  where :P1_SHOW_REGION is not null

My delay_table is simply a pipelined function that takes a value and waits that number of seconds to return values - it lets me test the report regions by simulating a long-running query. I'll post the code in the comments.
Next I created a before header process which resets the session state for the item - ensuring it is blank when the page loads.

Blank Item

Then I created a computation firing after regions which sets the value to "Y" - so the value is set in the session state ready to be used by our region refresh process.

Set Value

Finally I created the following JavaScript snippet and added this to the Execute When Page Loads section of the page definition.

var regionSelectorShown = new Array("Empty");

$(".apex-rds").data("onRegionChange",function(mode,activeTab) {
  if (typeof regionSelectorShown[activeTab.href] === 'undefined') {
    regionSelectorShown[activeTab.href]="Y";
    $(activeTab.href).trigger("apexrefresh");
  }
});

Execute on Page Load

So what is that doing? We are adding a callback on the onRegionChange event, which when triggered adds the name of the region (activeTab.href) to the array regionSelectorShown. This is purely so once we have shown the region within a page, we don't re-execute the query again if the user tabs out and back in again. Then we call the apexrefresh trigger passing in the region name to the jQuery selector - this causes a partial page refresh (PPR) of the region - which now sees the value of :P1_SHOW_DATA as "Y" and thus executes the query in full.
Now we see when we load the page, the region shown first fires the callback and shows initially. Then as we click through other regions, we get the processing icon (whilst waiting for the data to come back via my delay_table function) and the region shows. If we tab out and back in again, we don't re-call the refresh process as the regionSelectorShown array has a value indexed by the region ID indicating we have already shown it and so don't need to again.

Region Loading

 

Region Loaded

As always, there is always room for improvement and extension of this - if you do so then I'd really appreciate it if you could drop me a line in the comments so others can benefit. It would be nice (and I'd have thought relativly easy) if this kind of functionality was considered for inclusion in the standard APEX build as a feature in future releases.

Last modified on Continue reading
Tagged in: APEX
in Technical 174 1
0

A while back I created a post describing how to produce an organization chart in Oracle APEX using Google visualizations. If you didn't catch that then go and take a look here first before reading on as it will provide the background reading to this post.

So in this post I am going to demo how we can do this in OBIEE - and it's actually quite easy because OBIEE has already done a lot of the work for us.

First we need a level based hierarchy (or even just a representation of a hierarchy as levels across columns). This is how all BI Applications hierarchies are implemented, for example the organization and position hierarchies. I am going to use SampleApp with the "Sample Sales"."Offices" hierarchy.

Columns

Then we simply select all the columns in our hierarchy into a simple analytic. As we have multiple top level nodes I have applied a filter to restrict to just one company, however this isn't necessary - if you have multiple top level nodes then you simply get multiple trees.

Analytic

If we use the default Table view then we see something like this. Note I have changed the column order in this view simply to make the hierarchy structure clearer.

Table Results

Last modified on Continue reading
Tagged in: OBIEE Oracle BI 12c
in Techniques 344 2
0

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 453 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 816 2
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 571 0
0