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

2 comments:

  1. 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. select * from
    apps.oe_reasons_v
    where header_id = 123445

    ReplyDelete