Query To Get Item Attached To Certain Sub inventories in Oracle Apps R12 - Orapoint

This query will help you to get item attached to certain sub inventories with certain columns like organization code, attribute category, uom code, inventory_item_status_code, item_catalog_group_name, wip_supply_subinventory, wip_supply_locator in Oracle R12 Application.

SELECT 
   b.organization_code                         ,
   a.segment1                                  ,
   a.attribute_category                        ,
   a.description                               ,
   a.primary_uom_code                          ,
   a.inventory_item_status_code                ,
   c.segment1 item_catalog_group_name          ,
   a.wip_supply_type                           ,
   a.wip_supply_subinventory                   ,
   d.concatenated_segments wip_supply_locator
 FROM mtl_system_items_b a                     ,
   org_organization_definitions b              ,
   mtl_item_catalog_groups_b c                 ,
   mtl_item_locations_kfv d
 WHERE d.inventory_location_id(+) = a.wip_supply_locator_id
  AND d.organization_id       (+) = a.organization_id
  AND c.item_catalog_group_id (+) = a.item_catalog_group_id
  AND b.organization_id           = a.organization_id
  AND b.organization_code         = :p_organization_code
  order by a.inventory_item_id;

Query to get Item number organization code, secondary inventory name based on organization code.

SELECT B.SEGMENT1,C.ORGANIZATION_CODE,A.SECONDARY_INVENTORY
 FROM  MTL_ITEM_SUB_INVENTORIES A,
       MTL_SYSTEM_ITEMS_B       B,
       ORG_ORGANIZATION_DEFINITIONS C
 WHERE C.ORGANIZATION_ID  =  B.ORGANIZATION_ID
 AND   C.ORGANIZATION_ID  = A.ORGANIZATION_ID
 AND   B.INVENTORY_ITEM_ID =  A.INVENTORY_ITEM_ID
 AND   C.ORGANIZATION_CODE = :P_ORGANIZATION_CODE;

Query to get Item’s element name, element sequence, element value based on item number and organization id.

SELECT b.segment1,a.element_name,a.element_sequence,a.element_value 
FROM MTL_DESCR_ELEMENT_VALUES a,
     mtl_system_items_b b 
 WHERE b.inventory_item_id = a.inventory_item_id
 AND   b.organization_id   = :p_organization_id
 AND   b.segment1 = :p_item_number
 order by a.inventory_item_id,a.element_sequencee;

Query to get inventory number, organization code, cost, inventory asset flag, cost element, resource code, basis based on cost type and organization id.

SELECT                                                                                                                                      b.inventory_item_id,b.segment1,c.organization_code,
 x.usage_rate_or_amount cost,a.based_on_rollup_flag,a.inventory_asset_flag, 
        y.cost_element,z.resource_code,
     (select meaning
         from  mfg_lookups lu
         where lu.lookup_type = 'cst_basis'
   and   lu.lookup_code = basis_type) basis
 FROM apps.cst_cost_types d, 
      apps.cst_item_costs a, 
   apps.cst_item_cost_details x,
      apps.mtl_system_items_b b, 
      apps.org_organization_definitions c ,
   cst_cost_elements y,
   bom_resources z
 WHERE y.cost_element_id   = x.cost_element_id
 and   z.resource_id       = x.resource_id
 and   x.organization_id   = a.organization_id 
 and   x.inventory_item_id = a.inventory_item_id 
 and   x.cost_type_id      = a.cost_type_id 
 and   c.organization_id   = b.organization_id 
 and   c.organization_id   = a.organization_id 
 and   b.inventory_item_id = a.inventory_item_id 
 and   d.cost_type_id      = a.cost_type_id 
 and   d.cost_type         = :p_cost_type -- can be taken as Frozen or Pending as needed
 and   a.organization_id = :p_organization_id;

Query to get customer name, customer number, item definition, party site number, operating unit name, customer item number, customer item description, commodity code, item number, preference number based on customer number, customer name, party site number.

SELECT          A.CUSTOMER_NAME,A.CUSTOMER_NUMBER,A.ITEM_DEFINITION_LEVEL,C.PARTY_SITE_NUMBER,
            D.NAME OPERATING_UNIT_NAME,A.CUSTOMER_ITEM_NUMBER,A.CUSTOMER_ITEM_DESC,A.COMMODITY_CODE,
            G.SEGMENT1 ITEM_NUMBER,F.PREFERENCE_NUMBER
      FROM  APPS.HR_OPERATING_UNITS          D,
            APPS.MTL_CUSTOMER_ITEMS_ALL_V    A,
            APPS.RA_ADDRESSES_ALL            B,
            APPS.HZ_PARTY_SITES              C,
            APPS.MTL_CUSTOMER_ITEM_XREFS     F,
            APPS.MTL_SYSTEM_ITEMS_B          G
      WHERE G.INVENTORY_ITEM_ID = F.INVENTORY_ITEM_ID
      AND   G.ORGANIZATION_ID   = F.MASTER_ORGANIZATION_ID
      AND   F.CUSTOMER_ITEM_ID = A.CUSTOMER_ITEM_ID
      AND   C.PARTY_SITE_ID  =  B.PARTY_SITE_ID
      AND   B.ADDRESS_ID     =  A.ADDRESS_ID
      AND   B.ORG_ID         =  D.ORGANIZATION_ID
     -- AND   A.CUSTOMER_ITEM_NUMBER = :P_CUSTOMER_ITEM_NUMBER 
     -- AND   A.CUSTOMER_NAME= :P_CUSTOMER_NAME 
     -- AND  A.CUSTOMER_NUMBER = :P_CUSTOMER_NUMBER 
     -- AND C.PARTY_SITE_NUMBER = :P_PARTY_SITE_NUMBER ;