-- Yearly PO Analysis

SELECT 
   DECODE(Grouping(TO_CHAR(poh.creation_date,'YYYY')),
     0, TO_CHAR(poh.creation_date,'YYYY'),
     'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')') 
   yearly_po_counts,
   COUNT(*) line_count,
   COUNT(DISTINCT poh.segment1) po_count,
   ROUND(SUM(pol.quantity),2) py_qty,
   ROUND(SUM(pol.unit_price * pol.quantity)/SUM(pol.quantity),4) avg_price 
  FROM  po.po_headers_all poh,
        po.po_lines_all pol, 
        po.po_line_locations_all plo,
        po.Po_Distributions_All Pda
  Where Poh.Po_Header_Id = Pol.Po_Header_Id 
  AND plo.po_header_id = pol.po_header_id
  AND plo.po_line_id = pol.po_line_id
  and pol.unit_price <> 0
  and pol.quantity > 0
  AND NVL(plo.cancel_flag, 'N') != 'Y'
  AND plo.quantity_cancelled = 0
  AND plo.po_header_id = pda.po_header_id
  And Plo.Po_Line_Id = Pda.Po_Line_Id
  And Pda.Destination_Type_Code = 'INVENTORY'
  GROUP BY ROLLUP(to_char(poh.creation_date,'YYYY'))
ORDER BY to_char(poh.creation_date,'YYYY');

-- Yearly PO Summary Output:Output: