Solved

SUPPLY_ORDER_ANALYSIS_TAB Table and supply_order_analysis_api.generate_tree() Procedure

  • 4 January 2023
  • 6 replies
  • 109 views
M
Rank
Userlevel 1
Badge +7

Hi Everyone,

 

As part of some BI work we need to be able to track an order as it flows through Supply Chain Order Analysis within the oracle database. (E.g. we want to track an order based on the original customer order number, even when the order gets a new order number).

 

We have been looking at the Supply Chain Order Analysis screen and the related SUPPLY_ORDER_ANALYSIS_TAB table in the Oracle database. This table seems to contain the data that we are looking for, however the SUPPLY_ORDER_ANALYSIS_TAB table gets regenerated each time a user visits the “Supply Chain Order Analysis” screen via the supply_order_analysis_api.generate_tree() procedure and will not contain an order unless someone visits that screen for looking for that order.

 

Does anyone know how we could recreate the logic for the supply_order_analysis_api.generate_tree() procedure? It has to be in the database somewhere but I just don’t know where to look.

 

Thanks,

 

Mark Mruss

icon

Best answer by Hypervox 10 January 2023, 17:46

View original

6 replies

M
Rank
Userlevel 1
Badge +7

Hi,

Just giving this a bump to see if anyone knows the answer.

 

To describe what I am looking for in other words, I’m trying to find out how orders, whose order numbers change as the order moves through the supply order analysis (from site to site), are related.

 

supply_order_analysis_api.generate_tree() will generate the hierarchy in the SUPPLY_ORDER_ANALYSIS_TAB table but that doesn’t work for us, as we would like to perform our own analysis. 

 

Thanks,

 

Mark Mruss

H
Rank
Userlevel 3
Badge +8

If I recall correctly, if you work backwards from the last member of the hierarchy you describe, the fields DEMAND_CODE and ORDER_REF1 should help you find the previous member in the hierarchy.

For example, a demand code of “Int Purch Trans” on a customer order points to an internal purchase order - it’s number stored in ORDER_REF1.

M
Rank
Userlevel 1
Badge +7

If I recall correctly, if you work backwards from the last member of the hierarchy you describe, the fields DEMAND_CODE and ORDER_REF1 should help you find the previous member in the hierarchy.

For example, a demand code of “Int Purch Trans” on a customer order points to an internal purchase order - it’s number stored in ORDER_REF1.

 

Thanks for the information do you know which tables I should be inspecting? I’ve been able to find DEMAND_CODE in CUSTOMER_ORDER_LINE_TAB along with DEMAND_ORDER_REF1 - 4, but it doesn’t seem to contain all the information that I thought I would be able to see and I can’t see the hierarchy that I can see on the Supply Order Analysis screen.

 

This seems like a great step forward for me, any further hints you would provide would be appreciated. 

H
Rank
Userlevel 3
Badge +8

Hi MarkMr,

the tables you need to view depend on the demand code.  For example, if the demand code is “Int Purch Trans” you’ll need to look at PURCHASE_ORDER_LINE_TAB and match the order_ref fields to the purchase order line no etc...

M
Rank
Userlevel 1
Badge +7

Thanks! That helps and I’ve been able to start following the tree through the PURCHASE_ORDER_LINE_TAB. Do you happen to know which tables I would need to use based on the ICD and ICT demand codes? 

 

Edit: After looking more I think it’s in CUSTOMER_ORDER_LINE_TAB. I’m continuing to go down this rabbit hole!

H
Rank
Userlevel 3
Badge +8

Glad I could help - that’s exactly how I felt when I was looking at this for intersite orders 😂

Reply


Terms & ConditionsCookie settings