-- 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:
-- Yearly PO Summary Output:Output:
72888