Inventory Item Attributes, Categories, Vendor & Costs

Query to get safety stock Quantity of inventory items:

select msib.segment1,c.INVENTORY_ITEM_ID,SAFETY_STOCK_QUANTITY
from mtl_safety_stocks c,
 mtl_system_items_b msib
  where c.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
    and c.ORGANIZATION_ID = msib.ORGANIZATION_ID
    and c.SAFETY_STOCK_QUANTITY <> 0
    and msib.ORGANIZATION_ID = 12
    and msib.segment1 in ('98765');


Query to get the inventory item vendor and costs :

select
msi.inventory_item_id "Item ID",
msi.segment1 "Part Number",
msi.description  "Part Description - Internal",
decode (msi.item_type,'XX_SUPPORT_ITEM','SUPPORT ITEM'
                                       ,'XX_BULK_ITEM','BULK ITEM'
                                       ,'P','PURCHASED ITEM'
                                       ,'SA','SUBASSEMBLY'
                                       ,'PH','PHANTOM ITEM'
                                       ,'XX_FG','FINGOODS'
                   ,msi.item_type ) "ITEM_TYPE",
decode(MSI.PLANNING_MAKE_BUY_CODE, 1, 'MAKE'
                                 , 2, 'BUY'
       ,MSI.PLANNING_MAKE_BUY_CODE) "MAKE_BUY",    
msi.PRIMARY_UNIT_OF_MEASURE,                  
msi.INVENTORY_ITEM_STATUS_CODE  "Activity Flag",
msi.UNIT_WEIGHT  "Product Weight",
msi.WEIGHT_UOM_CODE  "Product Weight Unit of Measure",
msi.START_DATE_ACTIVE  "Part Effective Date",
msi.END_DATE_ACTIVE  "Part Obsolete Date",
msi.PURCHASING_ITEM_FLAG  "Salable Part Flag (Y/N)",
msi._ITEM_FLAG  "Product Configuration Flag",
msi.CUSTOMER_ORDER_FLAG  "Direct Sale to Customer Flag",
(select distinct aps.segment1
                from apps.PO_Approved_SUPPLIER_list asl,
                     --apps.mtl_system_items_b msib,
                     apps.ap_suppliers aps
              where msi.inventory_item_id = asl.item_id
                and asl.vendor_id = aps.vendor_id              
               -- and msib.organization_id = msi.ORGANIZATION_ID
               and rownum = 1
                ) Vendor_Num,
(select distinct aps.vendor_name
                from apps.PO_Approved_SUPPLIER_list asl,
                     --apps.mtl_system_items_b msib,
                     apps.ap_suppliers aps
              where msi.inventory_item_id = asl.item_id
                and asl.vendor_id = aps.vendor_id              
                --and msib.organization_id = msi.ORGANIZATION_ID
                and rownum = 1
                ) Vendor_Name,
msi.LIST_PRICE_PER_UNIT  "List Price",
cic.PL_MATERIAL "MATERIAL",
cic.MATERIAL_OVERHEAD_COST "MATERIAL_OVERHEAD",
cic.RESOURCE_COST "RESOURCE",
cic.OUTSIDE_PROCESSING_COST "OUTSIDE_PROCESSING",
cic.OVERHEAD_COST "OVERHEAD",
cic.ITEM_COST   "Standard Cost Per Unit",
cic.Material_COST "Actual Cost Per Unit",
Loc.name  "MFG Company Name",
Loc.Address_line_1  "MFG Address Line 1",
Loc.Address_line_2  "MFG Address Line 2",
Loc.Address_line_3  "MFG Address Line 3",
Loc.Town_or_city   "MFG City",
Loc.Region_2  "MFG State/Province",
Loc.Country  "MFG Country  ",
Loc.Postal_code  "MFG Postal Code",
from apps.mtl_system_items_b msi,
     apps.cst_item_costs cic,
(SELECT   Haou.Organization_id,
                   Haou.name,
                   Hrl.Address_line_1,
                   Hrl.Address_line_2,
                   Hrl.Address_line_3,
                   Hrl.Town_or_city,
                   Hrl.Region_2,
                   Hrl.Postal_code,
                   Hrl.Country,
                   Telephone_number_1
                FROM   Hr_all_organization_units Haou, Hr_locations Hrl
               WHERE   Hrl.Location_id = Haou.Location_id) Loc
where Loc.Organization_id = Msi.Organization_id
    and cic.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
    and cic.ORGANIZATION_ID = msi.ORGANIZATION_ID
    and cic.COST_TYPE_ID =1
and segment1 = '1234'
AND msi.organization_id = 123;


##Query to get item cross references:

SELECT distinct msi.segment1 "Part Number",
       msi.inventory_item_id  "Item ID",
       msi.description "Part Description",
       mci.customer_item_number "Customer Part Number",
       --mci.customer_id,hca.cust_account_id,
       --hp.party_name     
       ----------------sold to------------
       Sold_acct.account_number "Sold-To Customer Number",
       Sold_acct.account_name "Sold-to Company Name",
       Sold_loc.Address1  "Sold-to Address Line 1",
       Sold_loc.Address2  "Sold-to Address Line 2",
       Sold_loc.Address3  "Sold-to Address Line 3",
       Sold_loc.City  "Sold-to City",
       NVL (Sold_loc.State, Sold_loc.Province)  "Sold-to State/Province",
       Sold_loc.Country  "Sold-to Country  ",
       Sold_loc.Postal_code  "Sold-to Postal Code",
       'EMG ORACLE ERP' "Source Location ERP System",
       null "Part Owning Account Number"
       ,Loc.name "Part Owning Company Name",
       Loc.Address_line_1 "Part Owning Address Line 1",
       Loc.Address_line_2 "Part Owning Address Line 2",
       Loc.Address_line_3 "Part Owning Address Line 3",
       Loc.Town_or_city  "Part Owning City",
       Loc.Region_2 "Part Owning State/Province",
       Loc.Country "Part Owning Country",
       Loc.Postal_code "Part Owning Postal Code"
        FROM apps.mtl_customer_items mci,
             apps.mtl_customer_item_xrefs mcix,
             apps.mtl_system_items_b msi,
        ----------------sold to------------ 
             apps.hz_cust_accounts Sold_acct,
             apps.Hz_cust_acct_sites_all Sold_acct_site,
             apps.Hz_cust_site_uses_all Sold_site_use,
             apps.Hz_party_sites Sold_site,
             apps.Hz_locations Sold_loc
        ------------------------------------
             ,(SELECT   Haou.Organization_id,
               Haou.name,
               Hrl.Address_line_1,
               Hrl.Address_line_2,
               Hrl.Address_line_3,
               Hrl.Town_or_city,
               Hrl.Region_2,
               Hrl.Postal_code,
               Hrl.Country,
               Telephone_number_1
     FROM   Hr_all_organization_units Haou,
            Hr_locations Hrl
    WHERE   Hrl.Location_id = Haou.Location_id) Loc   
             --,apps.hz_parties hp
        WHERE mci.customer_item_id = mcix.customer_item_id     
        AND   mcix.inventory_item_id = msi.inventory_item_id
        --and   mcix.master_organization_id=msi.organization_id
        and  Msi.Organization_id = Loc.Organization_id
         AND mci.customer_id = Sold_acct.cust_account_id
         AND Sold_acct.Cust_account_id = Sold_acct_site.Cust_account_id
         AND Sold_acct_site.Cust_acct_site_id = Sold_site_use.Cust_acct_site_id
         AND Sold_acct_site.Party_site_id = Sold_site.Party_site_id
         AND Sold_loc.Location_id = Sold_site.Location_id
         AND msi.organization_id  = 123                   
            --and msi.inventorY_item_id = 246
            order by 1;

Query to get Item Category

SELECT *

                        FROM

                            mtl_item_categories dm_mic,

                            mtl_categories_B_KFV dm_mc,

                            mtl_category_sets_vl dm_mcst,

                            mtl_item_categories pl_mic,

                            mtl_categories_B_KFV pl_mc,

                            mtl_category_sets_vl pl_mcst

                        WHERE 1=1

                        AND pl_mic.inventory_item_id = 1499916

                        AND pl_mic.organization_id = 143

                        AND pl_mic.CATEGORY_ID = pl_mc.CATEGORY_ID

                        AND pl_mic.category_set_id = pl_mcst.category_set_id

                        AND pl_mcst.category_set_name = 'Category Set'

                        AND dm_mic.inventory_item_id = <:item_id>

                        AND dm_mic.organization_id = <:ship_from_org_id>

                        AND dm_mic.CATEGORY_ID = dm_mc.CATEGORY_ID

                        AND dm_mic.category_set_id = dm_mcst.category_set_id

                        AND dm_mcst.category_set_name = 'CATEGORIES';

                        --and pl_mc.segment2 like CHARACTER1||'%';