Inventory Transactions

-- ##############################################################################
--      UNCOSTED TRANSACTIONS
-- ##############################################################################

-- uncosted transactions per period and inv org

  SELECT TO_CHAR (mmt.creation_date, 'RRRR-MM') month_
       , haou.name inv_org
       , COUNT (*) txns
       , MIN(mmt.creation_date) oldest
       , MAX(mmt.creation_date) newest
       , TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((SYSDATE - min(mmt.creation_date)),'day')),'+000000000',''),'.000000000','')) oldest_
       , TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((SYSDATE - max(mmt.creation_date)),'day')),'+000000000',''),'.000000000','')) newest_
    FROM inv.mtl_material_transactions mmt
    JOIN inv.mtl_transaction_types mtt     ON mmt.transaction_type_id = mtt.transaction_type_id
    JOIN hr.hr_all_organization_units haou ON mmt.organization_id =     haou.organization_id 
   WHERE mmt.costed_flag IS NOT NULL
     AND mmt.costed_flag = 'E'
GROUP BY haou.name
       , haou.organization_id
       , TO_CHAR (mmt.creation_date, 'RRRR-MM')
ORDER BY haou.organization_id
       , TO_CHAR (mmt.creation_date, 'RRRR-MM');

-- uncosted transactions per month, inv org and transaction type

  SELECT TO_CHAR (mmt.creation_date, 'RRRR-MM') month_
       , haou.name inv_org
       , haou.organization_id org_id
       , mtt.transaction_type_name tx_type
       , mmt.costed_flag costed
       , COUNT (*) txns
       , min(mmt.creation_date) oldest
       , max(mmt.creation_date) newest
    FROM inv.mtl_material_transactions mmt
    JOIN inv.mtl_transaction_types mtt     ON mmt.transaction_type_id = mtt.transaction_type_id
    JOIN hr.hr_all_organization_units haou ON mmt.organization_id =     haou.organization_id 
   WHERE mmt.costed_flag IS NOT NULL
--     AND mmt.creation_date > '10-DEC-2013'
GROUP BY haou.name
       , haou.organization_id
       , mtt.transaction_type_name
       , mmt.costed_flag
       , TO_CHAR (mmt.creation_date, 'RRRR-MM')
ORDER BY haou.organization_id
       , TO_CHAR (mmt.creation_date, 'RRRR-MM');

-- ##############################################################################
--      basic transaction details
-- ##############################################################################

-- without accounting links

      SELECT mmt.transaction_id tx_id
           , mmt.organization_id org_id
           , mmt.primary_quantity * -1 qty
           , mmt.transaction_date tx_date
           , mmt.creation_date cr_date
           , mmt.last_update_date
           , mmt.request_id
           , haou.name inv_org
           , mtt.transaction_type_name tx_type
           , msib.segment1 item
           , msib.list_price_per_unit
           , DECODE(mmt.costed_flag,'','Yes','N','No','Other') costed
           , mmt.costed_flag
           , ppa.segment1 proj
           , ppa.distribution_rule
           , pt.task_number task
           , fu.description cr_by
           , gcc.segment1
           , gcc.segment2       
--           , (SELECT COUNT(*) ct from inv.mtl_transaction_accounts mta WHERE mta.transaction_id = mmt.transaction_id) acct_lines
           , mmt.subinventory_code subinv
           , mmt.transaction_quantity tx_qty
           , mmt.transaction_reference tx_ref
        FROM inv.mtl_material_transactions mmt
        JOIN inv.mtl_system_items_b msib       ON mmt.inventory_item_id =       msib.inventory_item_id
                                              AND mmt.organization_id =         msib.organization_id
        JOIN inv.mtl_transaction_types mtt     ON mmt.transaction_type_id =     mtt.transaction_type_id 
        JOIN applsys.fnd_user fu               ON mmt.created_by =              fu.user_id 
        JOIN hr.hr_all_organization_units haou ON mmt.organization_id =         haou.organization_id
   LEFT JOIN pa.pa_projects_all ppa            ON mmt.source_project_id =       ppa.project_id
   LEFT JOIN pa.pa_tasks pt                    ON mmt.source_task_id =          pt.task_id
   LEFT JOIN gl.gl_code_combinations gcc       ON mmt.distribution_account_id = gcc.code_combination_id
       WHERE 1 = 1
--         AND mmt.transaction_id = 12345678
         AND mmt.creation_date > '07-SEP-2016'
--         AND msib.segment1 IN ('A:1234')
    --     AND mmt.creation_date > '27-AUG-2014'
    --     and MMT.ORGANIZATION_ID = 111
    --     AND mtt.transaction_type_name = 'Average cost update'
    --     AND fu.user_name = 'SYSADMIN'
    --     AND mmt.costed_flag IS NOT NULL
    --     AND ppa.segment1 = 'ABC123'
    --     AND mmt.organization_id = 12
    --     AND mmt.costed_flag = 'E'
    ORDER BY mmt.transaction_id DESC;

-- with accounting links

      SELECT mmt.transaction_id tx_id
           , mmt.organization_id org_id
           , mmt.primary_quantity * -1 qty
           , mmt.transaction_date tx_date
           , mmt.creation_date tx_created
           , mta.creation_date mta_created
           , mmt.last_update_date
           , haou.name inv_org
           , mtt.transaction_type_name tx_type
           , msib.segment1 item
           , msib.list_price_per_unit
           , mta.primary_quantity
           , mta.base_transaction_value
           , DECODE(mmt.costed_flag,'','Yes','N','No','Other') costed
           , ppa.segment1 proj
           , pt.task_number task
           , fu.description cr_by
           , gcc.segment1
           , gcc.segment2
        FROM inv.mtl_material_transactions mmt
        JOIN inv.mtl_system_items_b msib       ON mmt.inventory_item_id =   msib.inventory_item_id
                                              AND mmt.organization_id =     msib.organization_id
        JOIN inv.mtl_transaction_accounts mta  ON mmt.transaction_id =      mta.transaction_id
        JOIN inv.mtl_transaction_types mtt     ON mmt.transaction_type_id = mtt.transaction_type_id
        JOIN gl.gl_code_combinations gcc       ON mta.reference_account =   gcc.code_combination_id
        JOIN applsys.fnd_user fu               ON mmt.created_by =          fu.user_id
        JOIN hr.hr_all_organization_units haou ON mmt.organization_id =     haou.organization_id
   LEFT JOIN pa.pa_projects_all ppa            ON mmt.source_project_id =   ppa.project_id
   LEFT JOIN pa.pa_tasks pt                    ON MMT.SOURCE_TASK_ID =      pt.task_id
       WHERE 1 = 1 
--         AND mmt.creation_date > '23-JUN-2015'
         AND msib.segment1 IN ('A:1234')
    --     AND gcc.segment1 != 'AAAAAAAA'
--         AND mmt.transaction_id IN (15197558,15197557,15197570)
    --     AND mmt.costed_flag = 'E'
    --     AND mmt.organization_id = 12
         AND 1 = 1;

-- ##############################################################################
--      transactions - counting
-- ##############################################################################

-- basic transaction details - count by user

  SELECT mtt.transaction_type_name tx_type
       , fu.description
       , COUNT (*) ct
    FROM inv.mtl_material_transactions mmt
    JOIN inv.mtl_system_items_b msib      ON mmt.inventory_item_id =   msib.inventory_item_id
                                         AND mmt.organization_id =     msib.organization_id
    JOIN inv.mtl_transaction_accounts mta ON mmt.transaction_id =      mta.transaction_id
    JOIN inv.mtl_transaction_types mtt    ON mmt.transaction_type_id = mtt.transaction_type_id
    JOIN gl.gl_code_combinations gcc      ON mta.reference_account =   gcc.code_combination_id
    JOIN applsys.fnd_user fu              ON mmt.created_by =          fu.user_id
   WHERE 1 = 1
     AND mmt.creation_date BETWEEN '20-OCT-2014' AND '25-OCT-2014'
--     AND mmt.organization_id = 127
--     AND fu.user_name = 'SYSADMIN'
--     AND fu.description = 'Bob Hope'
GROUP BY mtt.transaction_type_name
       , fu.description
ORDER BY 2 DESC;

-- transaction count per inv_org

  SELECT mmt.organization_id
       , haou.name
       , MAX (mmt.creation_date) most_recent
       , COUNT (*) ct
    FROM inv.mtl_material_transactions mmt
    JOIN hr.hr_all_organization_units haou ON mmt.organization_id = haou.organization_id
GROUP BY mmt.organization_id
       , haou.name;

-- ##############################################################################
--      LINKED TO PROJECT AND EXPENDITURE ITEM
-- ##############################################################################

      SELECT mmt.transaction_id inv_tx_id
           , mmt.creation_date inv_tx_date
           , haou.name inv_org
           , mtt.transaction_type_name tx_type
           , msib.segment1 inv_item
           , DECODE(mmt.costed_flag,'','Yes','N','No','Other') costed
           , ppa.segment1 project
           , pt.task_number task
           , gcc.segment1
           , gcc.segment2
           , fu.description cr_by
           , mmt.transaction_quantity tx_qty
           , peia.expenditure_item_id exp_item_id
           , peia.creation_date
           , peia.expenditure_item_date
           , peia.expenditure_type
           , peia.quantity
           , peia.raw_cost
           , peia.raw_cost_rate
           , peia.transaction_source
           , peia.system_linkage_function
        FROM inv.mtl_material_transactions mmt
        JOIN inv.mtl_system_items_b msib       ON mmt.inventory_item_id =           msib.inventory_item_id
                                              AND mmt.organization_id =             msib.organization_id
        JOIN inv.mtl_transaction_types mtt     ON mmt.transaction_type_id =         mtt.transaction_type_id
        JOIN applsys.fnd_user fu               ON mmt.created_by =                  fu.user_id
        JOIN hr.hr_all_organization_units haou ON mmt.organization_id =             haou.organization_id
        JOIN pa.pa_projects_all ppa            ON mmt.source_project_id =           ppa.project_id
        JOIN pa.pa_tasks pt                    ON mmt.source_task_id =              pt.task_id
   LEFT JOIN gl.gl_code_combinations gcc       ON mmt.distribution_account_id =     gcc.code_combination_id
        JOIN pa.pa_expenditure_items_all peia  ON ppa.project_id =                  peia.project_id
                                              AND peia.inventory_item_id =          msib.inventory_item_id
                                              AND peia.orig_transaction_reference = mmt.transaction_id
       WHERE 1 = 1
         AND peia.expenditure_item_id IN (12345678)
    ORDER BY mmt.transaction_id DESC;

Inventory Units of Measure

-- ##############################################################################
--      INVENTORY UNITS OF MEASURE
-- ##############################################################################

    SELECT moumt.unit_of_measure
         , moumt.uom_code
         , moumt.description
         , moumt.base_uom_flag
         , moumt.creation_date
         , fu.description cr_by
         , muc.uom_class
         , muc.conversion_rate
      FROM inv.mtl_units_of_measure_tl moumt
 LEFT JOIN inv.mtl_uom_conversions muc ON moumt.unit_of_measure = muc.unit_of_measure
      JOIN applsys.fnd_user fu         on moumt.created_by      = fu.user_id
--       AND moumt.unit_of_measure = '30m'
     WHERE 1 = 1
--       AND moumt.uom_code LIKE 'M%'
       AND moumt.creation_date > '01-NOV-2000'
       AND 1 = 1;

    -- count per inv org

    SELECT haou.name
         , msib.primary_uom_code
         , msib.primary_unit_of_measure
         , COUNT(*) ct
      FROM inv.mtl_system_items_b msib
      JOIN hr.hr_all_organization_units haou ON msib.organization_id = haou.organization_id
      JOIN applsys.fnd_user fu               ON msib.last_updated_by = fu.user_id
     WHERE 1 = 1
       AND msib.enabled_flag = 'Y'
       AND 1 = 1
  GROUP BY haou.name
         , msib.primary_uom_code
         , msib.primary_unit_of_measure
  ORDER BY haou.name
         , msib.primary_uom_code;

Inventory Sub Inventories

-- ##############################################################################
--      INVENTORY SUB INVENTORIES
-- ##############################################################################

SELECT haou.name inv_org
     , msi.secondary_inventory_name
     , msi.description
     , msi.inventory_atp_code
     , msi.availability_type
     , msi.reservable_type
     , msi.locator_type
     , msi.depreciable_flag
     , msi.creation_date
     , fu. description cr_by
  FROM inv.mtl_secondary_inventories msi
  JOIN applsys.fnd_user fu               ON msi.created_by =      fu.user_id
  JOIN hr.hr_all_organization_units haou ON msi.organization_id = haou.organization_id;

Inventory Organization Access

-- ##############################################################################
--      INVENTORY ORGANIZATION ACCESS
-- ##############################################################################

-- Inventory > Setup > Organizations > Organization Access

-- resps linked to inv orgs

SELECT oa.creation_date
     , haou.name org
     , fat.application_name application
     , frt.responsibility_name resp
     , oa.creation_date access_created_on
     , fu.description created_by
  FROM inv.org_access oa
  JOIN hr.hr_all_organization_units haou ON oa.organization_id =     haou.organization_id
  JOIN applsys.fnd_application_tl fat    ON oa.resp_application_id = fat.application_id
  JOIN applsys.fnd_responsibility_tl frt ON oa.responsibility_id =   frt.responsibility_id
  JOIN applsys.fnd_user fu               ON oa.created_by =          fu.user_id
 WHERE 1 = 1;

-- count of inv orgs per resp

  SELECT frt.responsibility_name resp
       , COUNT (*) ct
    FROM inv.org_access oa
    JOIN hr.hr_all_organization_units haou ON oa.organization_id =     haou.organization_id
    JOIN applsys.fnd_responsibility_tl frt ON oa.responsibility_id =   frt.responsibility_id
    JOIN applsys.fnd_application_tl fat    ON oa.resp_application_id = fat.application_id
   WHERE 1 = 1
GROUP BY frt.responsibility_name
ORDER BY frt.responsibility_name;

Inventory Requisitions

-- ##############################################################################
--      INVENTORY REQUISITIONS
-- ##############################################################################

   SELECT prha.segment1 req
        , prha.creation_date req_ct_dt
        , prla.creation_date line_cr_dt
        , numtodsinterval((prla.creation_date-prha.creation_date),'day') diff
        , fu.description cr_by 
        , prha.authorization_status
        , haou.name inv_org
        , msib.segment1 item_code
        , msib.inventory_item_id item_id
        , prla.line_num line
        , prla.unit_meas_lookup_code uom
        , prla.unit_price price
        , prla.quantity qty
        , hla.location_code deliver_to
     FROM po.po_requisition_headers_all prha
        , po.po_requisition_lines_all prla
        , inv.mtl_system_items_b msib
        , hr.hr_all_organization_units haou
        , hr.hr_locations_all hla
        , applsys.fnd_user fu
    WHERE prha.requisition_header_id = prla.requisition_header_id
      AND prla.item_id = msib.inventory_item_id
      AND msib.organization_id = prla.destination_organization_id
      AND prla.destination_organization_id = haou.organization_id
      AND prla.deliver_to_location_id = hla.location_id
      and prha.created_by = fu.user_id
      AND prla.destination_type_code = 'INVENTORY'
--      AND haou.name = 'This Store'
      AND prha.creation_date > '24-MAY-2016'
--      AND msib.segment1 = 'A:123'
 ORDER BY prha.segment1 DESC
        , prla.line_num;

Inventory Orgs

This lists Inventory Orgs, or Inventory Organizations

-- ##############################################################################
--      INVENTORY ORGS
-- ##############################################################################

      SELECT haou.organization_id org_id
           , mp.organization_code org_code
           , haou.name
           , haou.date_from
           , haou.creation_date
           , hla.location_code location
           , hla.address_line_1
           , hla.address_line_2
           , hla.address_line_3
           , hla.town_or_city
           , hla.country
           , hla.postal_code
        FROM hr.hr_all_organization_units haou
   LEFT JOIN hr.hr_locations_all hla                 ON haou.location_id =     hla.location_id
        JOIN apps.hr_organization_information_v hoiv ON haou.organization_id = hoiv.organization_id
        JOIN inv.mtl_parameters mp                   ON haou.organization_id = mp.organization_id
       WHERE 1 = 1
         AND hoiv.org_information1 = 'INV'
    ORDER BY 1;

Inventory Items – Locators

-- ##############################################################################
--      INVENTORY ITEMS - LOCATORS
-- ##############################################################################

SELECT msib.segment1
     , msib.description
     , mslav.*
  FROM apps.mtl_secondary_locators_all_v mslav
  JOIN inv.mtl_system_items_b msib ON mslav.inventory_item_id = msib.inventory_item_id 
 WHERE msib.organization_id = 83;

Inventory Items

-- ##############################################################################
--      INVENTORY ITEMS - DETAILS
-- ##############################################################################

    SELECT haou.name inv_org
         , msib.inventory_item_id
         , msib.segment1
         , msib.list_price_per_unit
         , msib.purchasing_enabled_flag
         , msib.creation_date
         , msib.last_update_date
         , fu.description upd_by
         , msib.primary_uom_code
         , msib.primary_unit_of_measure
         , msib.unit_of_issue
         , msib.enabled_flag
         , msib.inventory_item_status_code
         , msib.description
    --     , (SELECT COUNT(*) FROM inv.mtl_material_transactions mmt where MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID) tx_ct
    --     , (SELECT MAX(creation_date) FROM inv.mtl_material_transactions mmt where MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID) last_tx
         , gcc1.concatenated_segments sales_account
         , gcc2.concatenated_segments expense_account
         , gcc3.concatenated_segments cost_of_sales_account
         , micv.category_concat_segs category
         , micv.segment1 cat_seg1
         , micv.segment2 cat_seg2
      FROM inv.mtl_system_items_b msib
      JOIN hr.hr_all_organization_units haou ON msib.organization_id = haou.organization_id
      JOIN applsys.fnd_user fu               ON msib.last_updated_by = fu.user_id
      JOIN gl_code_combinations_kfv gcc1     ON msib.sales_account = gcc1.code_combination_id
      JOIN gl_code_combinations_kfv gcc2     ON msib.expense_account = gcc2.code_combination_id
      JOIN gl_code_combinations_kfv gcc3     ON msib.cost_of_sales_account = gcc3.code_combination_id
 LEFT JOIN mtl_item_categories_v micv        ON micv.inventory_item_id = msib.inventory_item_id AND micv.organization_id = msib.organization_id
     WHERE 1 = 1
--       AND msib.unit_of_issue IS NOT NULL
--       AND msib.enabled_flag = 'Y'
--       AND msib.inventory_item_status_code = 'Active'
--       AND haou.name = 'This Org'
--       AND haou.name = 'That Org'
       AND msib.segment1 = 'A:123'
--       AND msib.creation_date > '01-JAN-2015'
--       AND msib.organization_id = 84
--       AND msib.segment1 IN ('A:123', 'B:123')
--       AND msib.eam_item_type = 2
--       AND 1 = 1
    ORDER BY msib.creation_date DESC;

-- ##############################################################################
--      INVENTORY ITEMS - COUNTING
-- ##############################################################################

  SELECT haou.name
       , haou.organization_id org_id
       , COUNT (*) ct
       , MAX (msib.last_update_date) last_updated
       , MIN(msib.segment1) start_item
       , MAX(msib.segment1) end_item
    FROM inv.mtl_system_items_b msib
    JOIN hr.hr_all_organization_units haou ON msib.organization_id = haou.organization_id 
   WHERE msib.enabled_flag = 'Y'
     AND msib.inventory_item_status_code = 'Active'
GROUP BY haou.name
       , haou.organization_id
ORDER BY 3 DESC;

SELECT haou.name
     , count(*) ct
  FROM inv.mtl_system_items_b msib
  JOIN hr.hr_all_organization_units haou ON msib.organization_id = haou.organization_id
 WHERE msib.unit_of_issue IS NOT NULL
group by haou.name
order by 2 desc;

-- ##############################################################################
--      APPROVED SUPPLIER LISTS AND UNITS OF MEASURE
-- ##############################################################################

SELECT haou.name
     , msib.segment1
     , msib.last_update_date
     , msib.primary_uom_code
     , msib.primary_unit_of_measure
     , msib.unit_of_issue
     , paa.purchasing_unit_of_measure asl_uom
     , pv.vendor_name asl_supplier
     , pvsa.vendor_site_code asl_site
     , msib.enabled_flag
     , msib.inventory_item_status_code
     , msib.description
     , (SELECT COUNT(*) FROM inv.mtl_material_transactions mmt WHERE mmt.inventory_item_id = msib.inventory_item_id) tx_ct
     , (SELECT MAX(creation_date) FROM inv.mtl_material_transactions mmt WHERE mmt.inventory_item_id = msib.inventory_item_id) last_tx
  FROM inv.mtl_system_items_b msib
  JOIN hr.hr_all_organization_units haou ON msib.organization_id =   haou.organization_id 
  JOIN po.po_asl_attributes paa          ON msib.inventory_item_id = paa.item_id 
  JOIN po.po_approved_supplier_list pasl ON paa.asl_id =             pasl.asl_id
  JOIN ap.ap_suppliers pv                ON pasl.vendor_id =         pv.vendor_id 
  JOIN ap.ap_supplier_sites_all pvsa     ON pasl.vendor_site_id =    pvsa.vendor_site_id
                                        AND pv.vendor_id =           pvsa.vendor_id
 WHERE pasl.disable_flag IS NULL -- ignore disabled ASL details
--   AND msib.unit_of_issue IS NOT NULL
   AND haou.name = 'UOM: Life Sciences Store'
--  AND msib.segment1 = 'L:T14SL'
--  AND msib.enabled_flag = 'Y'
--  AND msib.inventory_item_status_code = 'Active'
--  AND msib.eam_item_type = 2
   AND 1 = 1;

Inventory Transactions – Costing Errors

Sometimes the costing of an Inventory Transaction can error (costed_flag = ‘E’). From my experience, when the errors happen, other transactions that need to be costed stack up behind the errored transactions and will not be costed either.

When I encountered this issue, I wrote some SQL to help find out more details about the errored transactions. Sometimes we needed datafixes to clear the errors, provided by Oracle via Service Requests.

-- ##############################################################################
--      INVENTORY TRANSACTIONS - COSTING ERRORS - BASIC DETAILS
-- ##############################################################################

-- error details with creation date, created by, transaction type etc.

  SELECT mmt.transaction_id tx_id
       , mmt.organization_id org_id
       , mtt.transaction_type_name tx_type
       , msib.segment1 item
       , msib.creation_date item_cr_date
       , mmt.request_id
       , mmt.costed_flag
       , mmt.last_update_date upd_date
       , mmt.creation_date cr_date
       , fu.description cr_by
       , mtt.transaction_type_name tx_type
    FROM inv.mtl_material_transactions mmt
    JOIN inv.mtl_system_items_b msib   ON mmt.inventory_item_id =   msib.inventory_item_id
                                      AND mmt.organization_id =     msib.organization_id 
    JOIN inv.mtl_transaction_types mtt ON mmt.transaction_type_id = mtt.transaction_type_id 
    JOIN applsys.fnd_user fu           ON mmt.created_by =          fu.user_id 
   WHERE 1 = 1
--     AND TRUNC(mmt.last_update_date) = '02-DEC-2013'
--     AND mmt.last_update_date BETWEEN TO_DATE('02-DEC-2013 16:09:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('02-DEC-2013 16:14:00', 'DD-MON-YYYY HH24:MI:SS')
--     AND mmt.error_code = 'CST_INVALID_ACCT_ALIAS'
     AND mmt.costed_flag = 'E'
--     AND mmt.organization_id = 84
ORDER BY mmt.organization_id;

-- ##############################################################################
--      A MORE LIMITED SELECT
-- ##############################################################################

SELECT mmt.*
     , fu.description
  FROM apps.mtl_material_transactions mmt
  JOIN applsys.fnd_user fu           ON mmt.created_by =          fu.user_id 
  JOIN inv.mtl_transaction_types mtt ON mmt.transaction_type_id = mtt.transaction_type_id 
 WHERE mmt.organization_id = 84
--   AND mmt.creation_date > '04-NOV-2013'
   AND costed_flag = 'E';


-- ##############################################################################
--      ERROR COUNTING 1
-- ##############################################################################

  SELECT COUNT (costed_flag) total
       , costed_flag cflag
       , SUBSTR (ERROR_CODE
               , 1
               , 40)
            code
       , SUBSTR (error_explanation
               , 1
               , 100)
            explan
    FROM apps.mtl_material_transactions
  HAVING costed_flag IN ('E', 'N')
GROUP BY costed_flag
       , ERROR_CODE
       , error_explanation;

-- ##############################################################################
--      ERROR COUNTING 2
-- ##############################################################################

  SELECT mtt.transaction_type_name tx_type
       , SUM((mmt.primary_quantity) * mmt.actual_cost) chg
       , COUNT(*) ct
       , count(distinct mmt.source_project_id) proj_ct
    FROM inv.mtl_material_transactions mmt
    JOIN inv.mtl_system_items_b msib      ON mmt.inventory_item_id =   msib.inventory_item_id
                                         AND mmt.organization_id =     msib.organization_id 
    JOIN inv.mtl_transaction_types mtt    ON mmt.transaction_type_id = mtt.transaction_type_id 
    JOIN inv.mtl_transaction_accounts mta ON mmt.transaction_id =      mta.transaction_id 
    JOIN applsys.fnd_user fu              ON mmt.created_by =          fu.user_id
   WHERE 1 = 1
     AND mmt.creation_date >= '01-NOV-2013'
     AND mmt.creation_date < '10-NOV-2013'
--     AND mmt.costed_flag IN ('E','N')
     AND mmt.organization_id = 84
GROUP BY mtt.transaction_type_name
ORDER BY mmt.organization_id;

Inventory Assignment Sets

I think Inventory Assignment Sets are related to Sourcing Rules…

-- ##############################################################################
--      INVENTORY ASSIGNMENT SETS
-- ##############################################################################

        SELECT mas.assignment_set_name assignment_set
             , msa.assignment_set_id
             , msa.organization_id
             , haou.name
             , count(*) ct
          FROM mrp.mrp_sr_assignments msa
     LEFT JOIN mrp.mrp_assignment_sets mas ON msa.assignment_set_id = mas.assignment_set_id
          JOIN hr.hr_all_organization_units haou on msa.organization_id = haou.organization_id
      GROUP BY mas.assignment_set_name
             , msa.assignment_set_id
             , msa.organization_id
             , haou.name;