Let’s review a fun problem shared on Oracle Cloud Connect by a popular pizza company! Deliveries were delayed due to difficulties finding the delivery dock, and they wanted to add a phone number to the PO documents to help.
This is a common issue in supply chains, and let’s start by talking about Locations!
What is a Location in Oracle?
Oracle uses Locations to indicate things like Ship To (the receiving dock) and Deliver To (incase it’s a delivery for a person in a cubicle somewhere, who isn’t at the receiving dock or warehouse) on the PO. Each user has a default Deliver To and they can override this while making a purchase; purchases generated by the Planning engine or things like Min/Max also have an associated Location with them.
I find that Locations are often a big issue during conversions, due to poor data quality. In other ERP’s, users may have entered the data in different orders. Sometimes they put notes in the address line, sometimes they put details like door, dock, floor and more, in the address line instead of in dedicated fields for those values.
It may help to turn on address validation in Oracle Cloud, but that won’t help with legacy data that must be cleaned up manually. It’s a great opportunity to standardize across the organization and it can help new hires as well, to have data in a standard format. For example, if I need to order a new computer monitor, how can I tell from Location Code or Name exactly where I sit?
In the case of Little Caesars, data cleanup wasn’t necessarily the issue, they may also have just had a complicated warehouse situation. In order to help clarify with drivers, they wanted to print the phone number of the deliver-to location on the PO documents.
The simplest solution? Paint a big sign at each warehouse with the phone number on it. But that might require a permit, could be damaged by the weather, might be overlooked by a driver focused on reading the paperwork, and doesn’t let us take advantage of the Oracle tools for managing our data. IE, if the phone number changes, you have to repaint the sign. By storing the data in your ERP and accessing it as needed, you can update in one spot and easily access anywhere.
So, I suggested an alternative solution: The data is stored in the Location already, it’s just not exposed in the Purchase Order Data Model, and therefore, not available in the seeded report. First, I confirmed this by looking at the seeded data model query and searching the tables listed–it helps that I already had experience with these tables and had a good idea of which one to look in.
Then before we really get started, we always have to archive the seeded Data Model and re-create/unarchive the file in the same folder structure, but inside of Custom folders in OTBI. This may exist already, depending on your implementation, if not, just create it. Also, make sure the report is linked to the data model in the custom folder.
Editing the Data Model
This is because Oracle looks first for a report with this name in the Custom folder section, and if none is found, then it looks for the seeded version. We never edit seeded versions as they may be replaced in quarterly updates, and by editing the seeded version, you lose the original version…kind of hard to go back from that.
Now we can edit the PO Data Model, and dive into the query. The PO data model has 2 queries which are similar and we have to edit both, but they both join to the same table.
The table we need in this situation is HR_LOCATIONS_ALL_F_VL, which is a view that contains the telephone number already concatenated, in telephone_number_1 field.
In each of the two queries, they have assigned a different alias to this same table. The code is the same, but we have to change the alias. We add this to the Schedule (LineLocation) query, and the Header queries, and in each instance, we add it for the draft and the archive version. The Schedule query alias calls this table ShipToLocation, and the Header query alias calls this table ShipToLoc, that’s the only difference between the code.
The only thing missing from Telephone_Number_1 is the extension, so we concatenate that along with the text “ext”, and the Coalesce operator means that if the phone number was null, then it will return a blank.
COALESCE(ShipToLocation.TELEPHONE_NUMBER_1, ”) ||
‘ext’ ||
COALESCE(ShipToLocation.MAINPHONE_EXTENSION1, ”) AS ShipToScheduleFullPhone,
COALESCE(ShipToLoc.TELEPHONE_NUMBER_1, ”) ||
‘ext’ ||
COALESCE(ShipToLoc.MAINPHONE_EXTENSION1, ”) AS ShipToHeaderFullPhone,
Here is an example of how you can add this code to the queries:
Test by going to the Data tab, query on a PO and Legal Entity and verify the data is correct, then you are ready to modify the report. Obviously, save the query/data model. Then, save your example as Sample Data, then use the Properties tab to export new Sample XML. If you skip the Sample Data step, it won’t work!
Editing the Report
Then, go to the PDF report in Custom folder and click Edit on the layout, this will download the RTF file.
Open in Word after you install the BI Publisher plugin, and import the Sample XML you just downloaded
Put the fields where you want them, like header field inthe header ship to, and the schedule ship to in the line level.
Then save with a new name, and add the layout back in Catalog and set it as the default.
Now you can generate the PO PDF and see that it contains the ship to location phone number! This same technique can be used to add all sorts of information.
Have you faced a similar situation where you needed to edit a data model to add data? Tell us more!