Thursday, April 10, 2008

Partially returned Orders


SELECT order_number
, ordered_date
, ordLines
, returnLines
FROM (SELECT /*+ cardinality (oeH 1) */
oeH.order_number
, oeh.Ordered_date
, count(distinct oeL.line_id) ordLines
, count(distinct oeLR.line_id) returnLines
FROM apps.oe_order_headers_all oeH
, apps.oe_order_lines_all oeL
, apps.oe_order_lines_all oeLR
WHERE oeH.ordered_date BETWEEN SYSDATE - 50
AND SYSDATE - 49.75
AND oeH.header_id = oeL.header_id
AND oeL.line_id = oeLR.reference_line_id (+)
GROUP BY oeH.order_number
, ordered_date
HAVING COUNT(*) > 1
) a
WHERE ordLines > returnLines
AND returnLines > 0;

No comments:

Post a Comment