Skip to main content

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
        )
    )

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) :grin:

 

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.


Thank you


Reply