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

Rpauley,

Please inform us on the main table(s) that you use in the query.

I have translated your question as: how old is a distribution order? Why not take the creation  date of the header or the line?

Steve

@Rpauley 

Have you tried:

objstate NOT IN (
        'Closed',
        'Cancelled'
    )
    AND   trunc(TO_DATE(objversion,'yyyymmddhh24miss') ) <= trunc(sysdate)-61
    AND   trunc(TO_DATE(objversion,'yyyymmddhh24miss') ) >= trunc(sysdate)-120
    

John

@johnw66 

 This was my actual first post on this subject, you did help me thru getting it thru the Distro Order History, but if there was a way to get it thru the Distro Order View would be better. i Tried the way you just posted and it doesnt pull any data, as it isnt pulling the latest history of the status change. thanks for all your help

@johnw66 

 i have the below SQL for the Distro Order History View, when i use this in my Matrix it i can not get it to allow me to navigate to the Distro Order Screen. and this SQL doesnt work under the @AO.Distribution_Order View

 

&AO.Distribution_Order_API.Get_Demand_Site(order_no) = '#USER_DEFAULT_SITE#' AND  objstate NOT IN (
        'Closed',
        'Cancelled'
    )


    AND   (
        trunc(TO_DATE(objversion,'yyyymmddhh24miss') ) <= (
            SELECT
                trunc(SYSDATE) - 30
            FROM
                dual
        )
        AND   trunc(TO_DATE(objversion,'yyyymmddhh24miss') ) >= (
            SELECT
                trunc(SYSDATE) - 60
            FROM
                dual
        )
    ) AND &AO.Distribution_Order_API.Get_ObjState(order_no) NOT IN ('Closed','Cancelled') 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

) 
 

Reply


Terms & ConditionsCookie settings