Thursday, 17 July 2014

Count of Orders Cancelled by Reason, Date and User Query

/* Formatted on 7/17/2014 12:34:23 PM (QP5 v5.115.810.9015) */
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

No comments:

Post a Comment