Solved

Aging Distribution Status

  • 9 November 2020
  • 20 replies
  • 245 views
R
Rank
Userlevel 3
Badge +7

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

icon

Best answer by paul harland 12 November 2020, 18:44

View original

20 replies

R
Rank
Userlevel 7
Badge +18

Good morning,

You could use the DISTRIBUTION_ORDER_HISTORY which is the view of the Distribution Order > History tab

 

Regards

 

John

R
Rank
Userlevel 3
Badge +7

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

 

 

paul harland
Rank
Userlevel 7
Badge +24

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. 

R
Rank
Userlevel 3
Badge +7

How would I do this in the data source developer, I am new to this thank you for your help

paul harland
Rank
Userlevel 7
Badge +24

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)

R
Rank
Userlevel 3
Badge +7

I Get this error message.

 

 

paul harland
Rank
Userlevel 7
Badge +24

ok, try

 

AND
objversion = 
(select max(objversion) 
from &AO.distribution_order_history h

where distribution_order_history.order_no = h.order_no)

R
Rank
Userlevel 3
Badge +7

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)

paul harland
Rank
Userlevel 7
Badge +24

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

)

R
Rank
Userlevel 3
Badge +7

Thank you, i didnt think about that, still learning as i go LOL. Thank you again that did do the trick

R
Rank
Userlevel 3
Badge +7

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

 

 

paul harland
Rank
Userlevel 7
Badge +24

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) 

 

 

R
Rank
Userlevel 3
Badge +7

Thank you again always something simple that stumps me LOL

R
Rank
Userlevel 3
Badge +7

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)

paul harland
Rank
Userlevel 7
Badge +24

do you have a column for order no?

That is what you need to be searching on.

R
Rank
Userlevel 3
Badge +7

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.

paul harland
Rank
Userlevel 7
Badge +24

What if you uncheck use data source

Check both site and status

R
Rank
Userlevel 3
Badge +7

I tried that way and it navigates to the DO screen however it pulls all DO’s even ones in closed status

paul harland
Rank
Userlevel 7
Badge +24

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.

R
Rank
Userlevel 3
Badge +7

Thank you

Reply


Terms & ConditionsCookie settings