General Queries For Pricing In Order Management (OM)

Posted by

Below query will give the information about 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’)

Below query will provide 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’

Below query will provide 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’

Below query will provide information about product service relationship i.e. what are all the services 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>