Monday, November 14, 2016

Query to Get Items and Formula Attached To Price List


SELECT qp.name price_list_name,
  qpl.PRODUCT_ATTR_VAL_DISP item,
  mtl.description,
  qpl.PRODUCT_ID ,
  qpl.OPERAND Product_price,
  qpl.attribute1 cost,
  qpl.PRICE_BY_FORMULA_ID Formula_id,
  qpf.NAME formula_name
FROM qp_secu_list_headers_v qp,
  qp_list_lines_v qpl,
  qp_price_formulas_vl qpf,
  mtl_system_items_b mtl
WHERE qp.name              ='<Price List Name>'
AND qp.LIST_HEADER_ID      =qpl.LIST_HEADER_ID
AND qpl.PRICE_BY_FORMULA_ID=qpf.PRICE_FORMULA_ID
AND qpl.PRODUCT_ID         =mtl.inventory_item_id
AND mtl.organization_id    =NVL(p_organization_id,tl.organization_id)