Oracle EBS Advanced Pricing Tables

8251人阅读 评论(0) 收藏 举报
分类:

Tables

QP_LIST_HEADERS_B
QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions.

QP_LIST_LINES
QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B. This table stores all types of list lines; price list lines, all types of modifiers including price modifier list lines used to derive factors. The different types of list lines are based on Lookup Type, 'LIST_LINE_TYPE_CODE'.

QP_PRICING_ATTRIBUTES
QP_PRICING_ATTRIBUTES stores product information and pricing attributes. The PRODUCT_ATTRIBUTE and PRODUCT_ATTRIBUTE_VALUE columns identify the product or level in the item hierarchy, i.e item context at which the price or modifier is set. The PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE and PRICING_ATTR_VALUE_FROM columns store the pricing attributes which further define what is being priced. If the PRICING_ATTRIBUTE_CONTEXT is VOLUME the pricing attributes column stores the break unit attribute, e.g. item quantity and both the PRICING_ATTR_VALUE_FROM and PRICING_ATTR_VALUE_TO columns can be populated. The Product information is repeated for all pricing attributes. A record is always created for the VOLUME context which may or may not have an attribute defined.



Relationship

QP_LIST_HEADERS_B  <---LIST_HEADER_ID ---> QP_LIST_LINES

QP_LIST_LINES <---LIST_HEADER_ID,LIST_LINE_ID ---> QP_PRICING_ATTRIBUTES

QP_PRICING_ATTRIBUTES <---PRODUCT_ATTR_VALUE <> TO_CHAR(MSI.INVENTORY_ITEM_ID)---> mtl_system_items_b


Useful Queries

SELECT * FROM QP_LIST_HEADERS_B WHERE list_header_id IN (SELECT list_header_id FROM QP_LIST_HEADERS_TL WHERE name = 'Corporate');--Price List Name

SELECT line.*
FROM QP_LIST_LINES line,QP_LIST_HEADERS_B header
WHERE line.LIST_HEADER_ID = header.LIST_HEADER_ID
AND line.list_header_id IN (SELECT list_header_id FROM QP_LIST_HEADERS_TL WHERE name = 'Corporate'); --Price List Name


SELECT *
FROM   QP_LIST_HEADERS_B SPL    ,
       QP_LIST_LINES SPLL       ,
       QP_PRICING_ATTRIBUTES QPA
WHERE  SPLL.LIST_HEADER_ID           = SPL.LIST_HEADER_ID
AND    QPA.LIST_HEADER_ID            = SPL.LIST_HEADER_ID
AND    SPLL.LIST_LINE_ID             = QPA.LIST_LINE_ID
AND  QPA.LIST_HEADER_ID IN (SELECT list_header_id FROM QP_LIST_HEADERS_TL WHERE name = 'Corporate'); --Price List Name

SELECT qpa.*
FROM   QP_LIST_HEADERS_B SPL    ,
       QP_LIST_LINES SPLL       ,
       QP_PRICING_ATTRIBUTES QPA,
       MTL_SYSTEM_ITEMS_B MSI
WHERE  MSI.ORGANIZATION_ID           = 244
AND    MSI.INVENTORY_ITEM_ID         = 434257
AND    SPL.LIST_HEADER_ID            = 164075
AND    SPLL.LIST_HEADER_ID           = SPL.LIST_HEADER_ID
AND    QPA.LIST_HEADER_ID            = SPL.LIST_HEADER_ID
AND    SPLL.LIST_LINE_ID             = QPA.LIST_LINE_ID
AND    QPA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND    QPA.PRODUCT_ATTRIBUTE         = 'PRICING_ATTRIBUTE1'
AND    QPA.PRODUCT_ATTR_VALUE        = TO_CHAR(MSI.INVENTORY_ITEM_ID)
AND    QPA.PRODUCT_UOM_CODE          = MSI.PRIMARY_UOM_CODE
AND    QPA.PRICING_ATTRIBUTE_CONTEXT IS NULL
AND    QPA.EXCLUDER_FLAG                   = 'N'
AND    QPA.PRICING_PHASE_ID                =1;



查看评论

Oracle EBS 常用查询 - 价格表查询

--根据价格表名称查询价格表头信息 Select Qp_List_Headers_Tl.Name As Price_List_Name, Qp_List_Headers_Tl.Description...
  • chris_mao
  • chris_mao
  • 2016-08-11 22:03:43
  • 1788

Oracle EBS Advanced Pricing Tables .

Tables QP_LIST_HEADERS_B QP_LIST_HEADERS_B stores the header information for all lists. List types...
  • u011350763
  • u011350763
  • 2013-07-09 12:25:44
  • 529

Oracle Advanced Pricing White Papers

Oracle Order Management - Version 11.5.10.0 and later Oracle Advanced Pricing - Version 11.5.10 and...
  • caixingyun
  • caixingyun
  • 2014-11-22 19:28:52
  • 2095

Oracle Advanced Supply Chain Planning

Oracle Advanced Supply Chain Planning Tables Oracle ASCP – List of database tables: MSC_ABC_CLAS...
  • suntyq
  • suntyq
  • 2010-11-16 01:04:00
  • 3626

Oracle EBS 高级定价中定价属性的定义过程

Oracle EBS 高级定价中定价属性的定义过程模拟案例:A公司有这样的价格策略,XX物品FOB条款为FOB(离岸价)时定价为30元/个。FOB条款为CFR(到岸价)时价格为50元/个。很明显,我们...
  • u012025054
  • u012025054
  • 2016-04-25 15:38:59
  • 3573

rs.open sql,conn,1,1全接触

经常会在连接DB的时候用到,就是不知道代表什么意思。。。RS.OPEN SQL,CONN,A,BA: ADOPENFORWARDONLY(=0) 只读,且当前数据记录只能向下移动ADOPENKEY...
  • siow
  • siow
  • 2006-09-07 22:10:00
  • 1081

Oracle Workflow Tables

Workflow Definition TablesWF_ITEM_TYPESThe WF_ITEM_TYPES table defines an item that is transitioning...
  • pan_tian
  • pan_tian
  • 2012-11-09 17:41:07
  • 4331

ASN (Advance Shipment Notice) 流程 以及shipment 相关的bug

EBS 实在是一个充满bug 的系统. 最近碰到一些和shipment 相关的bug, 就在下面写出来供大家娱乐. 首先建一个PO: 到 isupplier portal 找到po, 创建a...
  • zhangyu19881125
  • zhangyu19881125
  • 2014-05-16 17:53:38
  • 2699

实现Advanced Table-in-Advanced Table、

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE ...
  • ar7043
  • ar7043
  • 2009-07-22 15:44:00
  • 307

Oracle Apps DBA工具:ADADMIN使用说明

ADADMIN用途 ADADMIN主要用于Oracle Apps文件系统和数据库的维护工作,是Apps DBA常用的工具。 (ADADMIN执行文件位于$AD_TOP/bin下) ...
  • cunxiyuan108
  • cunxiyuan108
  • 2014-09-04 11:28:04
  • 1619
    个人资料
    专栏达人 持之以恒
    等级:
    访问量: 491万+
    积分: 5万+
    排名: 65