PO Purchase Summary Report By Category

SELECT null                    C_FLEX_CAT
,      poh.segment1                   PO_Number             
,      T.type_name                 PO_Type
,      ppf.full_name                  Buyer
,      pov.vendor_name                Vendor
,      poh.creation_date              Creation_Date                
,      poh.currency_code              C_CURRENCY            
,     SUM ( DECODE ( POL.order_type_lookup_code,               
                                 'RATE', POLL.amount,
                                 'FIXED PRICE', POLL.amount,
                                 NVL (POLL.price_override, 0) * (NVL(POLL.quantity, 0) - NVL(POLL.quantity_cancelled, 0)))
              ) C_AMOUNT
  ,    ROUND(SUM ( DECODE ( POL.order_type_lookup_code,              
                                 'RATE', POLL.amount,
                                 'FIXED PRICE', POLL.amount,
                                 NVL (POLL.price_override, 0) * (NVL(POLL.quantity, 0) - NVL(POLL.quantity_cancelled, 0)))) *
                               NVL(POH.rate, 1), :C_PRECISION) C_AMOUNT_FUNC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_sort', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEX_CAT_SORT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP
FROM   per_people_f                   ppf
,      po_vendors                     pov
,      mtl_categories                 mca
,      po_line_locations           poll
,      po_lines                       pol
,      po_headers                     poh
,      po_document_types_all_tl   T
,      po_document_types_all_b    B
,      po_releases                    por
WHERE  pol.category_id                = mca.category_id
AND    pol.po_header_id               = poh.po_header_id
AND    poll.po_header_id              = poh.po_header_id
AND    poll.po_release_id              = por.po_release_id(+)
AND    poll.po_line_id	= pol.po_line_id
AND    pov.vendor_id  (+)             = poh.vendor_id 
AND    pov.vendor_name is not null
&WHERE_PERFORMANCE
AND    ppf.person_id                  = poh.agent_id
AND    trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
AND    B.document_type_code        in ('PO','PA')
AND    B.document_subtype          = poh.type_lookup_code 
and      poll.shipment_type not in ('PRICE BREAK','PLANNED')
 AND B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE 
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE 
AND B.ORG_ID = T.ORG_ID  
AND B.ORG_ID = poh.ORG_ID  
AND T.LANGUAGE = USERENV('LANG')
AND    &P_WHERE_CAT     
GROUP BY  poh.segment1                       
,      T.type_name                
,      ppf.full_name                 
,      pov.vendor_name              
,      poh.creation_date             
,      poh.currency_code   
,     nvl(poh.rate,1),
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_sort', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') ,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
order by 10, 11, 2, 3, 4, 5, 6, 7, 8, 9