Introduction
This blog explains how to get pricelist details for an item
#Code#
SELECT QLH_TL.NAME “List Price”
,QLH_TL.DESCRIPTION “Description”
,QLH_TL.VERSION_NO “Version”
,QLH_B.LIST_TYPE_CODE “List Type”
,QLH_B.CURRENCY_CODE “Curr Code”
,MSI.SEGMENT1 “Item Number”
,MSI.DESCRIPTION “Item Description”
,MSI.PRIMARY_UNIT_OF_MEASURE “UOM”
,QPLL.LIST_LINE_ID PRICE_LIST_LINE_ID
,QPLL.CREATION_DATE
,QPLL.CREATED_BY
,QPLL.LAST_UPDATE_DATE
,QPLL.LAST_UPDATED_BY
,QPLL.LAST_UPDATE_LOGIN
,QPLL.LIST_HEADER_ID PRICE_LIST_ID
,APPS.QP_PRICE_LIST_PVT.GET_INVENTORY_ITEM_ID (QPLL.LIST_LINE_ID) INVENTORY_ITEM_ID
,APPS.QP_PRICE_LIST_PVT.GET_PRODUCT_UOM_CODE (QPLL.LIST_LINE_ID) UOM_CODE
,QPLL.ARITHMETIC_OPERATOR METHOD_CODE
,QPLL.OPERAND LIST_PRICE
,QPLL.GENERATE_USING_FORMULA_ID PRICING_RULE_ID
,’Y’ REPRICE_FLAG
,APPS.QP_PRICE_LIST_PVT.GET_PRICING_ATTR_CONTEXT (QPLL.LIST_LINE_ID) PRICING_CONTEXT
,APPS.QP_PRICE_LIST_PVT.GET_PRICING_ATTRIBUTE (QPLL.LIST_LINE_ID, ‘PRICING_ATTRIBUTE1’)
,APPS.QP_PRICE_LIST_PVT.GET_PRICING_ATTRIBUTE (QPLL.LIST_LINE_ID, ‘PRICING_ATTRIBUTE2’)
,QPLL.START_DATE_ACTIVE
,QPLL.END_DATE_ACTIVE
,APPS.QP_PRICE_LIST_PVT.GET_CUSTOMER_ITEM_ID (QPLL.LIST_LINE_ID) CUSTOMER_ITEM_ID
,QPLL.PRIMARY_UOM_FLAG
,QPLL.REVISION_DATE
FROM APPS.MTL_SYSTEM_ITEMS_B MSI
,APPS.QP_LIST_HEADERS_B QLH_B
,APPS.QP_LIST_HEADERS_TL QLH_TL
,APPS.QP_LIST_LINES QPLL
WHERE MSI.INVENTORY_ITEM_ID = APPS.QP_PRICE_LIST_PVT.GET_INVENTORY_ITEM_ID (QPLL.LIST_LINE_ID)
AND MSI.ORGANIZATION_ID = APPS.QP_UTIL.GET_ITEM_VALIDATION_ORG
AND QPLL.LIST_LINE_TYPE_CODE = ‘PLL’
AND QLH_B.LIST_HEADER_ID = QPLL.LIST_HEADER_ID
AND QLH_B.LIST_HEADER_ID = QLH_TL.LIST_HEADER_ID
AND QLH_TL.LANGUAGE = ‘US’;
Conclusion
This query will be useful to get pricelist details for an item
