Monday, October 20, 2008

Count of Orders Cancelled by Reason, Date and User

This query can give count of canceled orders by user, date and reason


SELECT TO_CHAR(oolh.hist_creation_date,'YYYY-MM-DD')
     , usr.description
     , oolh.reason_code
     , count(distinct oolh.header_id) Orders
  FROM apps.oe_order_lines_history oolh
     , apps.fnd_user               usr
 WHERE oolh.hist_creation_date > TRUNC(SYSDATE) -90
   AND oolh.hist_type_code     = 'CANCELLATION'
   AND oolh.last_updated_by    = usr.user_id
 GROUP BY TO_CHAR(hist_creation_date,'YYYY-MM-DD')
        , usr.description
        , reason_code
 ORDER BY 1

Posted by Sanjai Misra at 8:20 PM
Labels:

2 comments:

  1. AnonymousWednesday, April 23, 2014 at 4:12:00 PM CDT

    select * from
    apps.OE_ORDER_LINES_HISTORY
    where line_id = 123445
    AND hist_type_code = 'CANCELLATION'
    AND flow_status_code = 'CANCELLED' -- this may be required on versioned configs

    ReplyDelete
  2. ajitabh tiwariWednesday, January 4, 2017 at 5:05:00 AM CST

    select * from
    apps.oe_reasons_v
    where header_id = 123445

    ReplyDelete

Subscribe to: Post Comments (Atom)