I am trying to see Aging Distribution Orders, currently what i have shows me the object version of the report which if anything changes then it drops off my category, i want to see only the status history change that is over a certin amount of days. any help would be greatly appreciated. Thank you in advance below is how my current SQL is written
objstate NOT IN ( 'Closed', 'Cancelled' ) AND ( trunc(TO_DATE(objversion,'yyyymmddhh24miss') ) <= ( SELECT trunc(SYSDATE) - 61 FROM dual ) AND trunc(TO_DATE(objversion,'yyyymmddhh24miss') ) >= ( SELECT trunc(SYSDATE) - 120 FROM dual ) )
Page 1 / 1
Good morning,
You could use the DISTRIBUTION_ORDER_HISTORY which is the view of the Distribution Order > History tab
Regards
John
John,
i have attempted to use the history, the problem i have with using the history is it doesnt just pull the oldest line it pulls all history lines, so if i put this into a matrix or a counter instead of having 4 aging distro orders i end up have 30, any suggestions would be greatly appreciated. thank you again
You need to filter the history table so that it only shows the most recent record for each distribution order.
The cleanest way to do this is an IAL (and then use that in the datasource), but it is possible through the data source developer as well.
How would I do this in the data source developer, I am new to this thank you for your help
the syntax is a little odd on this page, but you should be able to use the following in order to get only the latest history record for each order
AND objversion = (select max(objversion) from distribution_order_history h
where distribution_order_history.order_no = h.order_no)
I Get this error message.
ok, try
AND objversion = (select max(objversion) from &AO.distribution_order_history h
where distribution_order_history.order_no = h.order_no)
Thank you that did work, i did have to add one section below so that it pulled the newest State as well other wise it would pull 3 records for one day if it moved that many times in one day, for your reference below is what i did. Thank you again for your help in this
AND objversion = (select max(objversion) from &AO.distribution_order_history h
where distribution_order_history.order_no = h.order_no) AND state = (select max(state) from &AO.distribution_order_history h
where distribution_order_history.order_no = h.order_no)
No problem…
Hmm, “Max(State)” might not work as you expect. What you have created there will possibly exclude some distribution orders altogether - which you don’t want. (max state would mean the last state alphabetically, not in terms of the process - so e.g. Planned would be preferred to Delivered).
Ok so if you had multiple simultaneous (same second, not just same day) history logs for the same order, you get 3 rows…
Here’s a better solution (it looks pedantic, but this is what you need)
AND objversion = (select max(objversion) from &AO.distribution_order_history h
where distribution_order_history.order_no = h.order_no)
and not exists (
select 1
from &AO.distribution_order_history h
where distribution_order_history.order_no = h.order_no
and distribution_order_history.objversion = h.objversion
and distribution_order_history.objid > h.objid
)
Thank you, i didnt think about that, still learning as i go LOL. Thank you again that did do the trick
ok one more question on this, is there a way to get it to navigate from history to the actual distro order screen, below is the address i am using and the screen shot of the error i am getting, i feel like i had this at one point but now its not working thank you again
ifsapf:tbwOverviewDistributionOrders
yeah - uncheck Use Data Source Condition
You will probably have an Order No column. Only check that one (that’s the only search filter you want to use)
Thank you again always something simple that stumps me LOL
Paul,
When i try to use two columns for Site Description and DO Status, i get the same error message as before, if i uncheck the Use Data Source it will work however it pulls all DO’s for the site instead of what is aging, is there a way to put it in the data source to pull from the DO or do i need to alter my columns some how. or am i asking to much LOL, below is how my columns are written right now
Site Description is written as &AO.site_api.get_description(&AO.Distribution_Order_API.Get_Demand_Site(order_no))
DO Status is written as
&AO.Distribution_Order_API.Get_ObjState(order_no)
do you have a column for order no?
That is what you need to be searching on.
I do have a column for Order No, how ever if i use Order No in the Element section then the Matrix would be unreadable if there are alot of old DO’s. so having it list by site and status makes it easier to navigate.
What if you uncheck use data source
Check both site and status
I tried that way and it navigates to the DO screen however it pulls all DO’s even ones in closed status
It’s a little too complex to help with this over this forum
I would suggest you reach out to local IFS resources if they are available.
alternatively - start a new thread for this with some screenshots.