We're using Oracle EBS Order Management 11.5.10 (hope I'm posting this in the right location).
I'm tasked with pulling data from the order management system for reporting. In a lot of our Order PTO models, the important product information is located on the top order line, the child lines have "generic" details that don't go well for reporting.
For each order line I pull, I need to some how find a way to look up the top order line. I've asked our EBS team and they are not familiar enough with the system to give me logic on how to lookup the top order line.
Is there any native function or logic for getting back to the top line of an order?
I could try to reverse engineer the system but I feel like this must be the same problem others have faced before and I didn't want to re-invent the wheel.
Has anyone had to accomplish this before? Is there a standard way to trace this?
Sure. Let's assume we have the following structure
ORDER_NUMBER, LINE_NUMBER, CHILD_NUMBER, LINE_ID, Product, Amount, Top Line ID
ABC,1,1,100, Package A, $100, 100
ABC,1,2,101, Bundled Item 1, $50, 100
ABC,1,3,102, Bundled Item 2, $50, 100
ABC,2,1,103, Package B, $50, 103
ABC,2,2,104, Bundled Item 1, $50, 103
So in the above example, there is a single order "ABC". That order has two packages each with a different PTO model. Package A has 2 bundled items and package B has 1 bundled item.
I want a column like that last column, namely Top Line ID that always holds a reference to the record that is on the top line of the PTO model. At this point in time, I don't know a specific column or function or logic that gives me that information.
You are correct, that a single order can have multiple PTO models. But a single order Line should only be part of one PTO model (as far as I know).