Purchase Categories in purchace order

Category Details

SELECT prha.segment1

     , mcb.segment1 || '.' || mcb.segment2 cat

     , prla.*

  FROM po.po_requisition_headers_all prha

     , po.po_requisition_lines_all prla

     , inv.mtl_categories_b mcb

 WHERE prha.requisition_header_id = prla.requisition_header_id

   AND mcb.category_id = prla.category_id

--     AND mcb.segment1 LIKE '%AB%'

   AND 1 = 1;

 

Category Count

SELECT   mcb.segment1 || '.' || mcb.segment2 cat

       , COUNT(*)

    FROM po.po_requisition_headers_all prha

       , po.po_requisition_lines_all prla

       , inv.mtl_categories_b mcb

   WHERE prha.requisition_header_id = prla.requisition_header_id

     AND mcb.category_id = prla.category_id

--     AND mcb.segment1 LIKE '%AB%'

GROUP BY mcb.segment1 || '.' || mcb.segment2

ORDER BY 2 DESC;

 

Category Count 2

 How many requisition lines reference each category

SELECT   COUNT(DISTINCT prla.requisition_line_id)

       , mcb.segment1 || '.' || mcb.segment2 CATEGORY

       , mcb.attribute11 acct_code

       , fvl.description account_code_desc

       , SUM(prla.unit_price * prla.quantity) total_spend

    FROM inv.mtl_categories_b mcb

       , po.po_requisition_lines_all prla

       , apps.fnd_flex_values_vl fvl

   WHERE mcb.category_id = prla.category_id

     AND fvl.flex_value = mcb.attribute11

     AND mcb.disable_date IS NULL

     AND prla.creation_date >= '01-DEC-2009'

GROUP BY mcb.segment1 || '.' || mcb.segment2

       , fvl.description

       , mcb.attribute11

ORDER BY 1 DESC;

 

Category linked to charge accounts\

SELECT DISTINCT mcb.category_id
              , mcb.creation_date
              , mcb.segment1 || '.' || mcb.segment2 category_name
              , mcb.segment2
              , mcb.attribute11 account_code
              , fvl.description account_code_desc
              , pct.NAME commodity
           FROM apps.fnd_flex_values_vl fvl
              , inv.mtl_categories_b mcb
              , po.po_commodities_tl pct
              , po.po_commodity_categories pcc
          WHERE fvl.flex_value = mcb.attribute11
            AND pcc.category_id(+) = mcb.category_id
            AND pct.commodity_id(+) = pcc.commodity_id
--            AND mcb.segment1 || '.' || mcb.segment2 = 'AA.BB'
            AND mcb.disable_date IS NULL
       ORDER BY 1
              , 2;