The blog provides the commonly used Oracle EBS R12 Pricing SQL Queries in Order Management

EBS R12 SQL to get active prices of an item available in different price lists

SELECT TO_CHAR (QL.OPERAND) UNIT_PRICE,
QL.LIST_HEADER_ID PRICE_LIST_ID,
QPHT.NAME PRICE_LIST_NAME,
QL.END_DATE_ACTIVE PRICE_LIST_END_DATE,
MSI.SEGMENT1 ITEM_NAME,
QL.LAST_UPDATE_DATE
FROM APPS.QP_LIST_LINES QL,
APPS.QP_PRICING_ATTRIBUTES QA,
QP_LIST_HEADERS_TL QPHT,
MTL_SYSTEM_ITEMS_B MSI,
APPS.MTL_PARAMETERS MP
WHERE     QL.LIST_HEADER_ID = QPHT.LIST_HEADER_ID
AND QL.LIST_HEADER_ID = QA.LIST_HEADER_ID
AND QL.LIST_LINE_ID = QA.LIST_LINE_ID
AND TRUNC (SYSDATE) BETWEEN TRUNC (
NVL (QL.START_DATE_ACTIVE, SYSDATE))
AND TRUNC (
NVL (QL.END_DATE_ACTIVE,
TRUNC (SYSDATE)))
AND QA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND QA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND QA.PRODUCT_ATTR_VALUE = TO_CHAR (MSI.INVENTORY_ITEM_ID)
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_CODE = ('Enter Organization code')
AND MSI.SEGMENT1 IN ('Enter Item Name')

EBS R12 SQL to get pricing and adjustments details for an item in a given order

SELECT OOHL.CONTEXT CONTEXT,
ORDERED_ITEM ITEM_NAME,
UNIT_LIST_PRICE SELLING_PRICE,
UNIT_SELLING_PRICE LIST_PRICE,
OPERAND DISCOUNT_RATE
FROM OE_ORDER_LINES_ALL OOLL,
OE_PRICE_ADJUSTMENTS OE,
OE_ORDER_HEADERS_ALL OOHL
WHERE     OOHL.HEADER_ID = OOLL.HEADER_ID
AND OOHL.HEADER_ID = OE.HEADER_ID
AND OOLL.LINE_ID = OE.LINE_ID
AND ORDER_NUMBER = <ENTER SALES ORDER>
AND ORDERED_ITEM='Enter Item Name'

EBS R12 SQL to get  concatenated category values for an item in a specific organization

SELECT MC.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_B MSI,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_TL MCS,
MTL_CATEGORIES_B_KFV MC,
MTL_PARAMETERS MP
WHERE     MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_CODE = 'Enter organization code'
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MCS.CATEGORY_SET_NAME = 'Provide category set name'
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MSI.SEGMENT1 = 'Enter item name'

EBS R12 SQL to get product service relationship  mapped to a particular product

SELECT RELATED_ITEM_ID PRODUCT,
ATTR_NUM2 SERVICE,
MRI.START_DATE,
MRI.END_DATE
FROM MTL_RELATED_ITEMS MRI, MTL_SYSTEM_ITEMS_B MSI
WHERE     MSI.INVENTORY_ITEM_ID = MRI.INVENTORY_ITEM_ID
AND RELATED_ITEM_ID = <PRODUCT INVENTORY ITEM ID>
AND ATTR_NUM2 =<SERVICE INVENTORY ITEM ID>