Commodities in Purchase Order

Simple Commodity Check

SELECT pct.*

  FROM po.po_commodities_tl pct;

Count of Categories per Commodity

SELECT pct.NAME

     , (SELECT COUNT(*)

          FROM po.po_commodity_categories pcc

         WHERE pcc.commodity_id = pct.commodity_id) category_count

  FROM po.po_commodities_tl pct

     , po.po_commodities_b cob

 WHERE pct.commodity_id = cob.commodity_id

   AND cob.active_flag = 'Y';

-- another version

SELECT   pct.NAME

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

    FROM po.po_commodities_tl pct

       , po.po_commodity_categories pcc

       , inv.mtl_categories_b mcb

   WHERE pcc.category_id(+) = mcb.category_id

     AND pct.commodity_id(+) = pcc.commodity_id

     AND mcb.disable_date IS NULL

ORDER BY 1

       , 2;

Commodity Grants

Commodities can be linked to Buyers - these SQLs provide the details

SELECT DISTINCT pct.NAME commodity
              , papf.full_name
              , bus_gp.NAME person_bg
              , fu.user_name
              , mcb.segment1 || '.' || mcb.segment2 cat
              , roles_sql.role_name
           FROM po.po_commodities_tl pct
              , po.po_commodity_grants pcg
              , po.po_commodity_categories pcc
              , hr.per_all_people_f papf
              , inv.mtl_categories_b mcb
              , hr.hr_all_organization_units_tl bus_gp
              , applsys.fnd_user fu
              , (SELECT fmv.user_menu_name role_name
                      , fmv.menu_id
                   FROM apps.fnd_menu_entries me
                      , apps.fnd_form_functions f
                      , apps.fnd_objects o
                      , apps.fnd_menus_vl fmv
                  WHERE o.obj_name = 'PO_COMMODITY'
                    AND f.object_id = o.object_id
                    AND me.function_id = f.function_id
                    AND fmv.menu_id = me.menu_id
                    AND fmv.TYPE = 'SECURITY') roles_sql
          WHERE pct.commodity_id = pcg.commodity_id
            AND papf.business_group_id = bus_gp.organization_id
            AND papf.person_id = pcg.person_id
            AND papf.person_id = fu.employee_id
            AND pct.commodity_id(+) = pcc.commodity_id
            AND pcc.category_id(+) = mcb.category_id
            AND pcg.menu_id = roles_sql.menu_id
            AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
       ORDER BY bus_gp.NAME
              , pct.NAME
              , papf.full_name;

-- SIMPLE LIST
SELECT DISTINCT papf.full_name
              , papf.employee_number
              , bus_gp.NAME person_bg
              , pct.name
           FROM po.po_commodities_tl pct
              , po.po_commodity_grants pcg
              , po.po_commodity_categories pcc
              , hr.per_all_people_f papf
              , inv.mtl_categories_b mcb
              , hr.hr_all_organization_units_tl bus_gp
          WHERE pct.commodity_id = pcg.commodity_id
            AND papf.business_group_id = bus_gp.organization_id
            AND papf.person_id = pcg.person_id
            AND pct.commodity_id(+) = pcc.commodity_id
            AND pcc.category_id(+) = mcb.category_id
            AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
       ORDER BY 2
              , 1;

--buyer linked to commodity, and buyer's role
SELECT   pct.NAME commodity
       , papf.full_name buyer
       , papf.business_group_id buyer_org
       , roles_sql.role_name
    FROM apps.po_commodity_grants grants
       , po.po_commodities_tl pct
       , hr.per_all_people_f papf
       , (SELECT fmv.user_menu_name role_name
               , fmv.menu_id
            FROM apps.fnd_menu_entries me
               , apps.fnd_form_functions f
               , apps.fnd_objects o
               , apps.fnd_menus_vl fmv
           WHERE o.obj_name = 'PO_COMMODITY'
             AND f.object_id = o.object_id
             AND me.function_id = f.function_id
             AND fmv.menu_id = me.menu_id
             AND fmv.TYPE = 'SECURITY') roles_sql
   WHERE grants.commodity_id = pct.commodity_id
     AND grants.person_id = papf.person_id
     AND grants.menu_id = roles_sql.menu_id
     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
ORDER BY papf.business_group_id
       , pct.NAME;