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 ;