Classic report with checkbox

When an action needs to be taken on specific records of a classic report, you need some kind of row-selector.
I came accross a situation where I couldn't used multi-row processing.
So I used the checkbox (apex_item.checkbox) in the SQL for a classic report.
The process fired at page-submit should now only process those rows that are checked.

This is not a difficult thing to do, just some logical steps you need to follow.

In this example you see a screen a supervisor can approve multiple holiday requests in one go for all the people in his team.

The supervisor can only reject a request by going to a detailled screen (clicking on the edit icon). But as a quick approve solution, the supervisor can select multiple requests and approve them in one go.

The checkbox in the first column is displayed with the apex_item.checkbox function:

In the header you notice a "select-all" / "de-select-all" checkbox. This works as a toggle function by editing the header of the column, entering following line:
 <input type="checkbox" title="Selecteer of de-selecteer alle aanvragen" onclick="$f_CheckFirstColumn(this)" />

The button "Goedkeuren" ( = approve button) performs a submit page. And so the process "Multi Select Approve" is launched.

The process needs to figure out wheater or not a line is selected. To play it nice, when you press the approve button, but no rows are selected, you get an error message.

You see that in the select statement in the first step, the internal ID that is used = 1: apex_item.checkbox( 1, p.proposal_id).
By doing this, I can refer to it on the processing side by using the function apex_application.g_f01.

So first thing that is done is to see if there are rows selected. If not the error message is posted to the screen and an error is raised. Hence the page processing is stopped immediately.
If at least one is selected we will go in a loop, performing the action - in this case launching the kick_proposal procedure - for each SELECTED row.


Here you go. Nothing too difficult, but can be handy in many situations.
Happy to share ....

Logout-URL for APEX applications

Closely linked to my previous post is how to set the Logout URL (&LOGOUT_URL.). Because when working with linked applications, the Logout-URL will possibly need to redirect the user to the login application that contains some kind of portal page for the different applications.

Most often this URL is displayed as a link on top of every page, usely next to the login-user-name.

Setup is done via Navigation Bar Entries in the Shared Components section.

As target there are two available options: a Page in this Application or a URL.
In this case most easy solution is to opt for the URL, pointing to the variable &LOGOUT_URL. that is available throughout the application.

It's as easy as this. But now of course you want to know how to influence this &LOGOUT_URL.-variable.
Well, this is done via Shared Components > Authentication Schemes.

Pick the Current scheme and navigate to the Post-Logout URL tab.

This identifies the URL that the user will navigate to after having correctly logged out of the application. The first parameter after f?p= represents the application alias (or application ID) of the APEX application you want to return to. In this case the application I want to start form has an alias called MYTEST.

For information: the second parameter is the page ID (or alias) of that application.

That's really all there is to it.
Happy to share.

Multiple (linked) applications in one workspace

Shouldn't I start this blog post with the same sentence as I started all the e-mails I wrote today??
Well, yes: HAPPY NEW YEAR to you all and above everything else, I'm wishing you good health for 2015 and beyond!


This blog is about how to manage multiple linked APEX applications in a single workspace.
A workspace is considered a logical work area which is associated with some APEX applications (one or many).
It's no rocket science and has been done many times before. I'm just pointing out how I got around with the issue.
I'm using APEX 4.2.5 on an Oracle 11g database.

Question often is: how do I structure all that? Will I put multiple applications in one single workspace or do I create a workspace per application.

I believe you can't just say one option is better than the other.
Multiple workspaces creates a bit more maintenance work for a DBA, but it's probably nicer to structure your Application Landscape if you have many different applications to maintain for different business areas.
One single workspace is easy, also for the developer by the way. But it's easy to get lost in the list of applications that may or may not have things in common.

My general thought on the matter is to bundle logical applications in one workspace.
But of course, sometimes it may be that you start with one application. You add a second one that is not necessarily linked with the first one, but to go fast you didn't create a second WS. And before you know it you've got too many apps in one WS.
Fact is, if you want to link - in an easy way - from app 1 to app 2, it is far easier if they are in the same WS.
So for this blog, I started from the assumption that all applications are in the same work space.

I came accross a customer who's operating in one single workspace. The main idea is that some of the apps will be made available for their employees (internal only) as a self-service platform. In this case HR or Payroll related.

When I came into play, the whole set of apps already existed and I was extending the existing ones with new functionality. By doing so, I tried to introduce also somewhat more integration and structure  with a minimal effort.
So I also created a new Administrator module, used by the system administrators for declarative work. In this app I created some shared objects like List of Values. Other applications are subscribed to those LOV's to be able to use exactly the same values.

The main (already existing) app is considered as a home page or portal page as you wish.
I'm showing some screenshots, but in order not to invade my customer's privacy, I removed all things that may refer to the customer (any resemblance to actual individuals, or companies, or events are purely coincidental).
The following LIST is part of that main page:

Each of these links points you to a different APEX application.

To be able to make use of links between those applications, it's best to use Application Alias names. You could also refer to application-id's, but that is not my preferred way of doing things. You never know what may happen with ID's once you start moving applications from one environment to another (from DEVELOPMENT to TEST tot PRODUCTION).

Therefore, per application come up with a (short) Application Alias name. In the APEX Builder you edit your application definition and in the name tab, make sure to enter a short, but meaningful Application Alias.

In the example I was writing about, this application 150 is the so called home page. So a sort of banner was created that in APEX is displayed as an image-button that looks like this:

And that button was added to the Breadcrumbs region on Position 01 (you understand that this is just an example and can be put anywhere).

Now the actual thing to do concerning the subject of this blog:

The URL Target is the important part here. It's always structured in the same way and all parts are devided by ':'. But you don't necessarily need to provide all parameters. So keep it as short as possible, but know you can extend with parameters like you would do in refering to (by example) Interactive Reports or passing on bind variables ...

In this simplified case: "f?p=" is the starting point.
First parameter is your application alias (or if you insist the application ID).
Followed by the page you want to refer to in the given application.
Followed by the session, easily referred to as "&SESSION."
You URL Tagret could look like this: f?p=HOME:1:&SESSION.

Oh, you will not be surprised that you can refer to a Page Alias in the same way as you are refering to an Application Alias. But it's not so likely that those page-ID's are going to change. So personally I've rarely given an alias to a page. Perhaps the alias HOMEPAGE would be appropriate if you have a dedicated home page. But other than that I don't really see the point.


Well, as I told you ... no rocket science and no new mind blowing things here, but ...

Happy to share ...

Base URL for EBS integrated with APEX

A question that is asked to me by many different clients running APEX application integrated with  the E-Business Suite: "how can I reference environment dependant things?".

It all comes down to the many environments that are bing used. Obvioiusly you have the absolute minimum: a Development environment (we'll call it DEV) and a Production environment (which we'll call PROD).
You can have a number of environments in between those, which have different names everywhere I go. For example you may have a specific "Project Environment" or a "User Acceptance Test Environment". You may also have an "Integration Test Environment".
All these environments have obviously a different URL-construction. It all depends on how your IT department is structuring its architecture. There are no strict rules ... it can be WHATEVER!

But sometimes you just need an exact reference to say where you can find things. Perhaps you're forced to use a CSS-strategy where IT wants you to store all CSS things in one place.
Or you have a specific logout-link that needs to point to the login URL of you application (in fact, being EBS or not makes no difference here). But for suer this URL is different in your DEV compared to the one in PROD!

So I came up some years ago with a solution for EBS integrated APEX applications. Using the available profile options.
This solution was built on an EBS 12.0.3 environment, but it still works also on later versions.
The APEX version it was first developped in was APEX 4.0, but it is still working on APEX 4.2.6 and it's using absolutely only standard APEX stuff. No rocket science whatsoever. So no reason to think why this would stop working once APEX 5.0 gets introduced.

Here are the ingredients and the recipe

• Application item AI_BASE_URL to store the base of the URL;
• Application process Init items after login to fill the Application Item;

and optional, just as examples:

• A list entry with path reference;
• Navigation Bar entry to your Home page;
• Navigation Bar entry to your Logout page.

For now I make the assumption that you have correctly integrated your EBS environment with your APEX environment. If you have done this, you will be able to use the secured views of EBS in your APEX app and you will be able to use the EBS API's as well. This is a must for this solution to work properly!

Step by step approach

In the Application Builder, navigate to the shared components to create the Application Item and the Application Process.

1. Application Item

Create a new Application Item. It is ALWAYS a good idea to use the prefex "AI" for an application item. It's just easy to recognize it every where in your code.

As the item serves to capture the base of the URL, I believe AI_BASE_URL is a good name.

Tip: use functional meaningful names for your items. Certainly application items. But also keep in mind that you will be typing them several times. Even if you have 30 characters to spare ... you may want to choose short names!

Creating an Application Item is very simple. Just remember to set your scope, in this case Application. And think about security: Restrict it to be set from the browser.
It would have been a good idea to enter in the comment field e.g. the place where you are using/setting the item. Although of course the help functionality in APEX is pretty good as well to find that out!

2. Application Process

We need an application process that fires just after login. Remember, you're working in an EBS integrated environment. So the login page is omitted, but the login DOES happen.

Create a new process:

We're using an EBS API to capture a standard profile option that keeps the ... yes exactly: base URL information.
In the example I set the PROD URL to be used in case the API would return no value. This is done because profile options may be updated by the system administrator. Normally that shouldn't be the case. But better save than sorry! This obviously means you have to test your stuff. Because when you use it in a logout-url, after logging out from a DEV environment for which the BASE-URL is not found, it will point to the PROD URL. This can be very confusing!!

Tip: only fill the PROD-URL that you use in the NVL construciton just before you move your app to production. Or as a minimum after you have decently tested everything on different environments. I usually set it to "google.be" until the very last moment!

 In fact ... you're now done. You did everything to fill the AI. This is really all there is to it. Now all you have to do is use it in a proper way.

Here are some examples ...

3. References to images (and other stuff)

In a given situation it was opted to upload a whole set of images and a general CSS file to a specific location on the server. This was a sub-structure of the "AI_BASE_URL".
In (e.g.) the menu list entries some of these images where used as reference icons. I used the AI_BASE_URL to reference these images:

4. Navigation Bar entry using URL target

In EBS the Home page is a specific page from which you start every action. It identifies who you are and what role (or roles) you have. And for each role (authorisation) which actions you are allowed to do.
In most cases your created APEX extension will be one of the actions (called form functions in EBS).
So you want in your integrated APEX app a link to this home page. This is done because it would be impossible to navigate back to your EBS environment if you hadn't foreseen this. So the - bad - alternative is that you logout or even kill your browser session. And then obviously a new login in required to continue with your work.



In this example that "Home" URL is constructed, making use of the AI_BASE_URL:

And exactly the same was done for the Logout URL:

Happy to share ...

Starting to blog at the end of 2014 ... why do that?

Hey all

I thought of starting my own blog many times before. Why go ahead with it now? Isn't it old style of communicating?

Well ... no, I don't think so. This is not a diary. Certainly not for private stuff.
I intend to write down professional experiences that I want to share.
So I can find things back for my own reference. And in the same way perhaps every now and then somebody else may benefit from it as well.

It is the very active APEX community who's actually bringing me to the point that I am now: launching my own blog. Thank you #ORCLAPEX for that!

Well, I'm not the kind of person who always thinks he's right in what he writes, says, does.
Though of course I'm trying very hard to be right or as close as possible anyway.
So if you would find an error here or there ... please do not hesitate to correct me. Though I urge you to do it in a polite way. I will not tolerate some responses I read now and then on blogs or forums. I mean well and if you can't see that in my blogs, then please: just stay away ;-)

I hope you enjoy reading or even learn from my posts.
See you on-line!

Roeland