A Practical Use for AI in APEX – Delivery Bookings

Traditionally, Oracle APEX applications with an appointment/delivery booking system would show a date/time ordered list of available time slots for the end user to select. Depending on the complexity of the system, some filters may only show certain date ranges or simply morning/afternoon. These systems can be inefficient as the end user tries to find a timeslot that suits their needs. For example, they might be available any Monday or Tuesday but not on other days, which makes hunting for those slots difficult. Ultimately, these systems haven't changed for many years. So, let me show you how to leverage the power of AI within APEX to filter the results efficiently and make the experience quicker and more fluid for the end user.

I am using Oracle 23ai, the first version to support Vector column types, and Oracle APEX version 24.2.

Setting up the example data

Let's create our table where we will store our delivery slots. This will be a simple table that holds a date and time for the start and end of the delivery slot, a location that the delivery team will cover (because a delivery team based in London will not deliver to Scotland), and the number of delivery employees that will be in the van )as some items, like sofas and beds will need more than one person). Lastly, we need a column to show if a slot has been booked already. The code I've used for this can be found here.

Now for some example data. Click here for the script that I've used if you want to follow this example closely.

For the purposes of the example, this would be enough, but let's make the application a little more complete and create a table to hold some products and another for customer data. The products will automatically retrieve the number of delivery employees needed to handle the product, and the customer data will hold the customer's location to match up with the delivery location.

To start, we'll create the products table; the code is available here.

 
And to create the records, I'm using the script found here.

 
Lastly, the table to hold the customer data is available here.

Creating the App

For this example, I've created an empty application. Firstly, I've added a simple page on page 1 to collect the customer's name, location and the product they are ordering. I've used a simple select list to ensure that the data matches the delivery locations, as well as a select list for the product. I've then added a button that submits the page.

On the page processing, I've added a simple process to save the customer data to the CUSTOMERS table and then navigate to a second page where we will select the delivery slot.

For the delivery page, I've created a simple report that gets all delivery slots that match the delivery location, have at least the correct number of delivery employees and have not already been booked. Usually, we would add another column to allow the user to click and select the desired delivery slot, but we're going to amend this screen to provide some AI magic.

Adding the AI Vector Search

To enable the AI processing, I need to add a column to the DEL_SLOTS table to store the vector details. If you are building your application from scratch, you would do this as part of the initial table creation, but I want to show you how easy it is to implement this feature into an existing page. So, switching to the SQL Commands, let's run the following command:

ALTER TABLE del_slots ADD delivery_search   VECTOR;

Now we need to populate the Vector embeddings. For this demo, I have already set up a Vector Provider using Cohere, as they provide a free API service (with usage limits), but you can use the service that is right for you. To see how to set up a Vector Provider, click here.

To populate the entries, we need to reference the APEX_AI package and use the GET_VECTOR_EMBEDDINGS function, passing in the columns you want the embedding to be created around. This is the code I have used with the p_service_static_id being the name of the Vector Provider, so substitute this for your own.

We now need to set up a search configuration. These contain information about the searchable data source.

Go to App Builder, select the application you are working on, and then go to the Shared Components. Within the Navigation section, click the Search Configurations link and then the Create button.

Enter a meaningful name for the Search Configuration and select Oracle Vector Search before clicking Next.

Select the Vector Provider you have previously set up, and then select the table you have used to store the embeddings. In my case, this would be DEL_SLOTS. Then click on Next.

The final screen is where you need to state the details about the table, such as the Primary Key and the column where the vector embeddings are stored, etc. Once you have set these, click the Create Search Configuration button.

Once created, we need to amend the search to restrict the returned slots to only those which have not already been booked. Go back into the Search Configuration by clicking on it and scroll down to the Source section to find the Where Clause. In here, add the following:

DELIVERY_BOOKED IS NULL

Now we have the search set up, let's add it to the application.

Return to the App Builder and navigate to the page you created to show the results. In my example, this is page 2. Amend the Type of the Available Slots region from Classic Report to Search.

The first thing we need to add is a search field for the end user to type into, so add a text field to the Available Slots region. I've called this P2_SEARCH.

You will see that APEX has automatically created a search item on the page. I've amended the name to be more meaningful, and within the attributes, you will need to set the Search Configuration to the name of the configuration we set up earlier.

The final piece is to add the name of the search text field item in the attributes of the Search Region.

So let's try it out.

Execute Page 1, and we are shown the initial screen. I've entered a random name as the customer and selected the location where this customer resides. Lastly, I've chosen a sofa as the product the customer is buying, and I know that this requires two people to deliver it.

Clicking on Save takes me to page 2, where the AI vector search will perform its magic. In the search box, I typed Tuesday to show all the delivery slots that are on a Tuesday. Note that the search doesn't restrict to only those records that match; it is simply reordering based on how well the AI has matched the records.

Now let's try something a little more taxing. I'm now going to ask for a specific date, so I've entered Tuesday, 16th. Note that in the above screenshot, these records were further down the list.

Now that's better. I wonder what would happen if I entered a time, as I'm sure I'm busy most of the day, and only free around 11 am. So let's enter Tuesday, 16th, at 11 am.

Now the time slot I wanted has magically appeared at the top of the list!

I would then need to implement the procedure to book the delivery slot and link that to clicking on the row on the screen.

Conclusion

AI is forming a greater part of our lives; however, we are not restricted to chatbots. As the technology becomes more understood, the boundaries are being pushed back. AI is a great help behind the scenes, but, as proven here, it can also enhance the customer experience by quickly and easily filtering data based on typed instructions instead of select boxes, radio buttons or check boxes.

With each new version of the Oracle database and of APEX itself, new AI features are created and existing features are streamlined, making the developer's job easier as well as enhancing the customer experience.

For more information, check out our Oracle APEX Services and AI Services, or contact us today, and one of our expert developers will be in touch.