3 Replies Latest reply on Aug 16, 2011 10:35 AM by Sandeep Gandhi, Consultant

    item subinventory and procurement details table info

    makdutakdu
      hi all


      i have this requirement to take a report of all Spare parts and Other Inventory Organization Items, which are active, from ERP with the Last Procurement details. If the Procurement information is not available , the supplier name field should be blank. also to provide the UOM, inventory org and sub inventory information too against each item.

      i tried the following query from the INV abc super user responsibility
      select DESCRIPTION,segment1,segment2,segment3,PRIMARY_UOM_CODE,PRIMARY_UNIT_OF_MEASURE ,
      organization_id
      from mtl_system_items where 
      rowid IN 
      (
      select rowid from 
      (
      select rowid, row_number() over (partition by SEGMENT1 order by null) rn from mtl_system_items
      where  INVENTORY_ITEM_STATUS_CODE ='Active'
      
      and    organization_id  IN (105,121)
      ) 
      where rn = 1
      )
      kindly guide me to include subinventory information ,when the Master Item form is opened in tools there is Item Subinventory ,i am not
      able to get the table name related to item subinventory so i could include in my query
      also how do i get the procurement information

      kindly guide

      thanking in advance
        • 1. Re: item subinventory and procurement details table info
          Sandeep Gandhi, Consultant
          Why would you not simplify the query as below
          SELECT DISTINCT description, segment1, segment2, segment3, primary_uom_code,
                          primary_unit_of_measure, organization_id
                     FROM mtl_system_items
                    WHERE inventory_item_status_code = 'Active'
                      AND organization_id IN (105, 121) 
          The item-subinventory information is in MTL_ITEM_SUB_INVENTORIES_ALL_V . You can find the name of a table/view used in a form by going to help > about this record.

          For procurement information, you will have to go to po_lines_all and join msi.inventory_item_id with pla.item_id.
          For vendor information, you will have to join pla.po_header_id with po_headers_all.po_header_id and then jump to po_vendors using pha.vendor_id.

          For a technical overview of purchasing, see http://apps2fusion.com/apps/fm/16-iprocurement/438-oracle-purchasing-technical .

          Hope this helps,
          Sandeep Gandhi
          1 person found this helpful
          • 2. Re: item subinventory and procurement details table info
            makdutakdu
            hi


            thank you very much for the helpful guidance

            could you please guide as to which column info has to be fetched in the query
            for the procurement details from the table po_lines_all

            also which column wud show the subinventory information ( i see source_subinventory
            and secondary_inventory) from the view MTL_ITEM_SUB_INVENTORIES_ALL_V

            with the joins you have suggested i have made th query below
            SELECT DISTINCT msi.description, msi.segment1, msi.segment2, msi.segment3, msi.primary_uom_code,misalv.SECONDARY_INVENTORY,misalv.source_subinventory,
            pov.vendor_name,
                           msi.primary_unit_of_measure, msi.organization_id
                      FROM mtl_system_items msi,MTL_ITEM_SUB_INVENTORIES_ALL_V misalv, po_lines_all pla,po_headers_all pha,po_vendors pov
                     WHERE msi.inventory_item_id=misalv.inventory_item_id and 
                       msi.inventory_item_id= pla.item_id and 
                       pla.po_header_id=pha.po_header_id and
                       pha.vendor_id=pov.vendor_id and 
                        msi.organization_id=misalv.organization_id and 
                       inventory_item_status_code = 'Active'
                       AND msi.organization_id IN (105, 121)
            now this query has only 3586 reecords whereas the initial query you had posted had 7785 records

            i suppose the items without vendor name ,procuremnt details dont come up thts why the decrease in record count
            is it possible to show those items too which dont have subinventry,procurement,vendor info

            kindly guide
            thanking in advance
            • 3. Re: item subinventory and procurement details table info
              Sandeep Gandhi, Consultant
              could you please guide as to which column info has to be fetched in the query for the procurement details from the table po_lines_all
              Depends on what details you need. You may get price from po_lines_all
              also which column wud show the subinventory information
              Use secondary_inventory
              is it possible to show those items too which dont have subinventry,procurement,vendor info
              You need to use outer joins (please google the term and go through some examples)
              Try the following
              SELECT DISTINCT msi.description, msi.segment1, msi.segment2, msi.segment3, msi.primary_uom_code,misalv.SECONDARY_INVENTORY,misalv.source_subinventory,
              pov.vendor_name,
                             msi.primary_unit_of_measure, msi.organization_id
                        FROM mtl_system_items msi,MTL_ITEM_SUB_INVENTORIES_ALL_V misalv, po_lines_all pla,po_headers_all pha,po_vendors pov
                       WHERE msi.inventory_item_id=misalv.inventory_item_id(+) and 
                         msi.inventory_item_id= pla.item_id(+) and 
                         pla.po_header_id=pha.po_header_id(+) and
                         pha.vendor_id=pov.vendor_id(+) and 
                          msi.organization_id=misalv.organization_id(+) and 
                         inventory_item_status_code = 'Active'
                         AND msi.organization_id IN (105, 121)
              Hope this helps,
              Sandeep Gandhi