Oracle EBS R12 Purchasing, Inventory, Order Management Queries

To Find Duplicate Item Category Code

SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments

Get Number Of canceled requisition

SELECT a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name|| ‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name “Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY)) CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY

Number of line processed in Order Management

SELECT COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;

To Check Item Catogry For Inventory master (No Of Segments May Varry)

SELECT ood.organization_name,
segment1|| ‘-’|| segment2|| ‘-’|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name

Check Locators for inventory Inventory Org Wise(Number of segment may varry)

SELECT mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID

Display All Subinventories Setup
select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME “Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name

To Select Unit Of measure exist in ebusiness suite

select uom_code,unit_of_measure
from mtl_units_of_measure

Query to find out Customer Master Information. Customer Name, Account Number, Adress etc.
select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID

Query to find on Hand Quantity

select sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188

Qunatity on order, Expected Deliver

select sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE

Query to find Item Code, Item Description Oracle Item Master Query

select item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item

Query to Find out On Hand Quantity of specific Item Oracle inventory
select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id

Qty On Order,

Expected deivery date(select sum(ordered_quantity),

scheduled_ship_date from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info

–Total Received Qty
select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0)

Total received Qty in 9 months
select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270))

Total issued quantity in 9 months

select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_iss_qty_9mths, –Average monthly consumption
(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0) ;

Display all categories that the Item Belongs
SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv

Another Query to Get Onhand Qty With Oranization ID, Item Code, Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-’|| msib.segment2|| ‘-’|| msib.segment3|| ‘-’|| msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)