Sales Order & Fulfillment Backorder Summary Report Oracle Fusion OM

 Write a query TO produce a high-level executive summary OF sales ORDER activity within om. It should calculate total ORDER volumes, line counts,AND tracks backordered demand TO measure fulfillment efficiency AND backlog risk.

-----------------
--Sales Order & Fulfillment Backorder Summary Report

SELECT Count(DISTINCT dha.header_id) AS total_sales_orders,
       Count(DISTINCT dla.line_id)   AS total_order_lines,
       Count(DISTINCT
       CASE
              WHEN Upper(dfla.status_code) LIKE '%BACKORDER%'
              OR     Upper(dfla.status_code) IN ('AWAIT_SHIP',
                                                 'BACKORDERED') THEN dla.line_id
       END) AS backordered_lines_count,
       Count(DISTINCT
       CASE
              WHEN Upper(dfla.status_code) LIKE '%BACKORDER%'
              OR     Upper(dfla.status_code) IN ('AWAIT_SHIP',
                                                 'BACKORDERED') THEN dha.header_id
       END) AS orders_with_backorders_count
FROM   doo_headers_all dha,
       doo_lines_all dla,
       doo_fulfill_lines_all dfla
WHERE  1=1
AND    dha.header_id = dla.header_id
AND    dla.line_id = dfla.line_id
       /* Include submitted / open sales orders */
AND    (
              dha.submitted_flag = 'Y'
       OR     dha.status_code = 'SUBMITTED')
       /* Softened line type check to cover standard sell/buy order lines */
AND    (
              dla.line_type_code IN ('LINE',
                                     'ORA_BUY',
                                     'ORA_SELL')
       OR     dla.line_type_code IS NOT NULL)

Result: