Case Study:

Managing Office Supplies with Oracle 10g Express.

sample Our case study is based upon employing Oracle 10g Express, with it's embedded RAD, Application Express 2.0, to manage ordering, tracking , and delivery of office supply items.

Before getting started, you may find this study easier to follow by viewing the application first. This will give you the 'big picture' and provide a more tangible picture of the study.

While some case studies do include detailed companion tutorials, this case study is going to focus on the basic task at hand, and provide coding specifics only where the author feels they are either required or have some unusual or time-saving code to share that is essential to the study.

Please note that for both practical and security reasons, some features on the application are disabled. Additionally, the budget totals (those amount spent for the month by the department) do not accrue on the application. This was done to allow all users to sample the main features without any single users being able to max-out a department's budget.

We will refer to this application as 'Sarah's Application' as office supplies had, to this point, been managed on paper by an individual, "Sarah". True scenario, fictitious name.

Please note that on our application there are two logins available, 'Dept' will show you the end-users view of the application as well as allow you place sample orders. The 'Sarah' login will show you the back-end view which features the reports and application administrative functions.

Finally, please note that while all coding contained below can be applied to Application Express 3.0, our case study is specific to 10g Express in that it was employed in order to minimize costs, provide for rapid deployment without taxing our existing Oracle structures, and the application was to be run off of a PC with no special server components (Oracle 10g Express comes with all needed connectivity components).

The request was to create a simple web-based application that could be accessed via the companies intranet to place orders for office supplies. The application also had the following requirements:

Orders should be placed and tracked by Department.
The office supply budget per department is cumulative (for fiscal year) and is allocated on a rolling, monthly basis.
The amount of the allocation is based on headcount. With one exception for whom there would be no budgetary cap.
Departments were of three types: Operations, Executive, and Senior Executive. Operations being allocated $7.50 per month per head and Executive $41.00 per month per head. Senior Executive having no budgetary cap.
Operations Departments are themselves the sum of the current projects under them at any given time. This was a particularly unique challenge as we needed to allot their current month based on the project headcounts without either rewarding or penalizing their rolling budgets for those departments whose component projects have grown, shrank, disappeared, or increased.
Provide Sarah with reports to be used for auditing, tracking, and to provide to the supply vendor.
The vendor supply report would be monthly (as supplies are ordered on a monthly basis) while other reports would allow Sarah to filter for any given date range, department, supply item, etc...
The application should be fully administrated Sarah herself. That is, an administrative dashboard within the application itself that would allow Sarah to perform required administrative tasks, specifically; adding and editing supply items, overriding department budgetary caps where required, as well as adding and revoking and changing department access and passwords.
**Important Note** in our actual application, the department information required a link to our HR database in order to provide current headcount. As this was done for a large Contact Center that encompassed a large multitude of projects, the headcount at the department level would fluctuate by month as new projects (clients) were added or expired, or project headcounts expanded or shrank. However, to maintain simplicity, our sample will use a table "Department" which can be manually populated for both department name and headcount. Should you chose to create an application based on our study, you would simply populate this table via a link to your HR database. If you have few departments and the headcount is either relatively stable (or not a factor in allocation) you might very well opt to use the manual population option employed in our study.

In summary, the requirement was for an application to order and track office supplies that would keep order sizes to what was actually required, keep departments within their budgets, and just generally free up Sarah for more productive activities.

Our implementation begins with a free download of Oracle's 10g Express Database. Please note all system requirements and restrictions as outlined by Oracle. There are two additions available, one for Western European languages only, and a "Universal" edition which supports non-Latin alphabets. The Universal edition is approximately twice the size of the Western European edition.

If you are completely new to Oracle 10g Express, the installed database, which is entirely browser based, comes with ample help and documentation. The Oracle home page contains much more as well, including tutorials, forums, and sample applications.

The database actually comes with three sample applications that you can install with a couple of clicks. We strongly encourage anyone who is new to Oracle 10g Express, and Application Express, to install the "Sample Application".

While we will detail much in our study (as well as provide a link to the completed application), installing the Oracle "Sample Application" will provide you with a complete 'back-end' view of an application. This is important as many of the items we will cover, such as Collections, Lists of Values (LOVs), Page Processes, and more are also featured in the "Sample Application".

Issues

Before delving into the issues and particulars, let's quickly note that the front end of this application (the one the end user placing an order sees), consists of three pages.

P1_ this is the opening page in which the user selects their department
P2- this is the main "shopping cart" page in which the user selects the items they wish to order. It is also the page in which much of our resides as it forms the gateway to Orders table as well as the times that determine what the user (departments) budget is.
P3_ this is the 'Thank You' page which shows the order summary and form which the user can print or save their summary and then logout.

Allotment Amounts:

One of the fist issues we had to tackle was to automate the monthly ordering cycle. In doing so, we needed to take into consideration the 'age' of the various projects that compose each department. This was a particularly important requirement (see item 5 above). So, if a project is only been active for two months, we wanted their rolling allotment to reflect only two months.

We did this by creating a view which measured the projects start date against the current month. In other words, we subtracted SYSDATE from dual from the projects start date. Extracting the numeric month (DW/dual) from both. The difference giving us the number of months the project has been in operation and therefore providing the correct rolling budget.

This application, in fact, went through several evolutions. Initially, we wanted Operation Managers to be able to delegate ordering to their respective team leaders. This is good strategy operationally as the team leaders know what they need and it saved the operation managers the trouble of having to be the conduit for all of their projects supply orders.

However, as this was one of our first forays into APEX, the initial model I created turned out to be functional but overly complicated and could not be administered by Sarah, but only by one of our DBAs. This defeated the parameters set forth above, so it was dropped in favor of department only orders.

In retrospect, and if your skill level is up to it, this is probably a better approach.

In fact, even after we switched to the department-only ordering, most operation managers chose to simply delegate their passwords to their most senior team leaders so as not to be bothered with it.

Fraud Prevention:

Similarly, we needed to prevent departments from "back-editing". That is, while departments require the ability to edit orders for the active month, we did not want them going back and editing out supplies ordered in previous months in order to increase their rolling allotment.

The following where clause to the editing page report provides users with the ability to access only those orders for the current month:

to_date("ORDERS"."ORDERDATE",'DD-MON-YY') >= (SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YY') FROM dual)

One the items in the select statement the where clause applies to must, of course, include:

SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MM-YY') FROM dual

Keeping Budget Parameters:

Some simple PL/SQL page items provide us with the departments current budget, rolling budget, and available budget. You will notice these on the sample application on the shopping cart in the "Order for - " region.

The running annual budget and the amount spent to date are simply items based on SQL Exists Queries.

In our example :

P2_MAX is the item behind which is an Exists Query that pulls the annual budget.

P2_SPENT is the item behind which is an Exists Query that pulls the amount spent to date.

So, the value displayed under available balance is a simple PL/SQL statement of the the difference the running budget and what has been spent to date. Please note the 'to_number' conversion required:

to_number(:P2_MAX) - to_number(:P2_SPENT)

Most importantly, this also forms the basis of whether or not the "Proceed to Checkout" button appears or the "Warning: You Have Exceed You Budget" message appears. This is also a simple PL/SQL statement:

to_number(:P2_SOFAR) < to_number(:P2_BUDGET)

P2_SOFAR is the amount that is being spent is based upon a collection 'ITEMS' we employed in the shopping cart style order process where c003 is the sum amount of items added to the cart:

select sum(c003)
from htmldb_collections
where collection_name = 'ITEMS'

Collections are whole field of study in themselves, but you can get good practical example of collections from the Oracle "Sample Application", as well as online resources.

If you are totally new to working with collections, they are, in the simplest sense, a kind of 'shopping cart' functionality you can add to you application.

It allows users to add items to their 'cart' (in this case our 'ITEMS' collection which, in turn, is reflected in the "Cart for..." report), as well as remove them, without actually committing any of the items to our Orders table until some pre-defined event. In our case, clicking the "Proceed to Checkout" button.

Some Brass Tacks:

Enabling Remote Client Access:

In order for users to be able to access the application via your companies intranet, you need to enable HTTP access for remote clients. To do so, on the database home page, go to Administration>Manage HTTP Access and select the 'Available form local server and remote clients' radio button. I have noticed on several occasions that when making this change, I received a "Page Can Not be Displayed Message". I don't know if this is something to specific to our network or not. In any event, the change did in fact take place. So if you do get such an error message go back through the full Administration>Manage HTTP Access path and verify that the change has been made.

Setting Authorization and Authentication.

Authentication Options:

Application Express in 10g Express offers three types of authentication: 'Application Express', "Database User', 'Database Account', and 'Custom'.

Application Express Authentication is implemented by simply going to Home>Application Builder>Application Administration>Manage Users. Here you can view and create application users and user groups. For small and relatively stable user populations, this is probably the easiest authentication method to implement. It is important to note that these users are application users only and that any users you create from this menu will not be able to access the database itself using these credentials. Quite Handy.

Database Users Authentication is implemented by simply going to Home>Database Users>Manage Users. Here you can view and create database users and user groups. While you will need to create database user accounts for your DBAs, it is not recommended to use Database User authentication for your application. The reason, obviously, is that unless your project requires otherwise, you don't want application users having access to the database itself.

Custom Authentication We chose this method for our application and simply used the custom hash and authorization functions that are used in the Oracle "Sample Application". If you are new to APEX, this is a good chance to get acquainted both with functions as well as user and application level security. Again, using the "Sample Application" as a guide, it also allowed us to create a very user friendly front end for managing users and passwords. View Password Management Page Be sure to use the 'sarah' login to view this page.

Authorization Schemes: Authorization can be set at virtually every level of the application, and can be done so quite easily. Whether it is a tab, a page, a page item, or just about anything else.

Authorization Scheme Type: Authorization schemes can be based upon SQL queries, PL/SQL functions returning Booleans, and other methods. We chose 'Exists SQL Query' as our authorization Scheme Type. For our expression, we simply used:

select 1 from users
where :APP_USER = 'SARAH'

We named this scheme 'Sarah Lock', and wherever required, simply go to the Security setting for the page, button, item, process, or whatever and selected the scheme you have created from the drop down.

Conclusions:

This particular implementation worked well for several reasons. Not the least of which was cost. The free download of Oracle 10g Express, combined with installing it on a PC that was not being utilized at that point (thus increasing, so to speak, our ROI on the machine).

One of the parameters that played in our favor is that the application was being used by a relatively small group of users and was accessed not very frequently (once, sometime twice, per user per month).

The set up can and will work for larger and more frequently accessed applications, but at a certain threshold you would obviously want to move the application onto any existing Oracle database you may have, or by going with a full 10g install on an actual, dedicated application server.

All-in-all, an inexpensive and effective solution.

Questions, Feedback, or Comments?

sample If you have any feedback, please contact us or post your questions to the forums.

Case Study:

Managing Office Supplies with Oracle 10g Express.

Questions, Feedback, or Comments?

About Articles, Tutorials, and Case Studies

Submit Your Work!