Problem:
I have a dataset that includes a PERIOD, SOLD_TO and PRODUCT_CATEGORY with a group of ORDER_NUMBER values in each category. I would like to use an APEX tree to drill down through these three levels to get to the itemized list of orders.
The examples that I have seen using a tree all use a hiererarchical query that “loops back” to itself. For example, each person in the tree has a manager and managers are persons. So, when we start with a manager, we can connect back and find all the persons they manage, then connect back on those persons to see whom they manage, etc.
My situation is different in that we start with a period, then connect back to find all the vendors for that period, then connect back to find the products for that vendor and finally list the orders that satisfy all three levels.
Example
Here is a visualization in Excel of the tree structure that I am looking to replicate in APEX.
Sample Data
The PERIOD_VENDOR_PRODUCT table contains the unique hierarchy. The PERIOD_VENDOR_PRODUCT_ORDER table provides the itemized list of orders for each distinct bucket. The joined dataset at the bottom simply assembles the data in a flat format and most likely will not be needed to create the query necessary for APEX, but I am not sure of that.
WITH
PERIOD_VENDOR_PRODUCT AS
(
SELECT '2022-12' PERIOD, 'EXXON' SOLD_TO, 'B1' PRODUCT_CATEGORY FROM DUAL UNION ALL
SELECT '2022-12' PERIOD, 'EXXON' SOLD_TO, 'BD' PRODUCT_CATEGORY FROM DUAL UNION ALL
SELECT '2022-12' PERIOD, 'SHELL' SOLD_TO, 'B1' PRODUCT_CATEGORY FROM DUAL UNION ALL
SELECT '2022-12' PERIOD, 'MOBIL' SOLD_TO, 'PB' PRODUCT_CATEGORY FROM DUAL
),
PERIOD_VENDOR_PRODUCT_ORDER AS
(
SELECT '2022-12' PERIOD, 'EXXON' SOLD_TO, 'B1' PRODUCT_CATEGORY, 'A00001' ORDER_NUMBER FROM DUAL UNION ALL
SELECT '2022-12' PERIOD, 'EXXON' SOLD_TO, 'B1' PRODUCT_CATEGORY, 'A00002' ORDER_NUMBER FROM DUAL UNION ALL
SELECT '2022-12' PERIOD, 'EXXON' SOLD_TO, 'BD' PRODUCT_CATEGORY, 'A00010' ORDER_NUMBER FROM DUAL UNION ALL
SELECT '2022-12' PERIOD, 'SHELL' SOLD_TO, 'B1' PRODUCT_CATEGORY, 'B00011' ORDER_NUMBER FROM DUAL UNION ALL
SELECT '2022-12' PERIOD, 'MOBIL' SOLD_TO, 'PB' PRODUCT_CATEGORY, 'C00020' ORDER_NUMBER FROM DUAL
)
SELECT PVP.*, PVPO.ORDER_NUMBER
FROM PERIOD_VENDOR_PRODUCT PVP
LEFT OUTER JOIN PERIOD_VENDOR_PRODUCT_ORDER PVPO
ON PVPO.PERIOD = PVP.PERIOD
AND PVPO.SOLD_TO = PVP.SOLD_TO
AND PVPO.PRODUCT_CATEGORY = PVP.PRODUCT_CATEGORY
Desired Solution
As I understand it, the “tree” page in APEX works on a “hierarchical” query structure. So, I am thinking I need a hierarchical query that is able to connect back on a changing parent (period, then vendor, then product) to get to the itemized values.
Consequently, I think I need either a) an appropriate hierarchical query for my dataset, or b) guidance on how to implement the tree using my flattened data or similar.
Any assistance would be appreciated.
Rob