关闭

Oracle EBS Advanced Pricing Tables

8192人阅读 评论(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;



0
0
查看评论

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

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

Oracle EBS Advanced Pricing Tables .

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 ...
  • u011350763
  • u011350763
  • 2013-07-09 12:25
  • 522

Oracle EBS Advanced Pricing Tables

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 ...
  • qq609600523
  • qq609600523
  • 2015-05-15 13:29
  • 330

InterCompany Shipping业务流程

前提:物料(Item)中以下属性必须选中:• Customer Ordered• Customer Order Enabled• Internal Ordered• Internal Order Enabled• Invoiceable Item• Invoice Enabled• Cost Ena...
  • oraclebs
  • oraclebs
  • 2008-11-10 16:02
  • 6290

Oracle Advanced Pricing White Papers

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

Oracle EBS Tables

<br />iSupport Message存放表:<br /> jtf_amv_items_b, jtf_amv_items_tl
  • luohuap
  • luohuap
  • 2011-04-02 09:16
  • 123

实现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
  • 298

ASN(Advanced Shipment Notices)和ASBN(Advanced Shipment Billing Notices)

1. Buyer creates a new Purchase orderResponsibility: Manufacturing and Distribution Manager(N)Purchasing->P->Purchase Orders2. Supplier checks n...
  • oraclebs
  • oraclebs
  • 2008-10-17 14:00
  • 2082

oracle ebs 简介

哎。。。。现在这年头,只搞db人越来越少,dba的地位越来越低。。。。没办法。。位了生存,哥哥决定再跨一个新的领域,去oracle的应用领域去学oracle ebs去。。。 开工: ebs的内部技术架构:简单概括为如下几个部分:多组织、多语言、多币种、模块化,集成性、并发处理、多技术混用。 我们看一...
  • changyanmanman
  • changyanmanman
  • 2013-07-31 23:15
  • 2687

Oracle EBS各个版本使用情况的调查

ITPUB上对Oracle EBS各个版本使用情况做了个小调查,主要想看看现阶段国内使用EBS版本的情况。2个多月统计的结果如下图,101人参与了投票,从上图看出,R12.1使用率已接近六成。11.5.10的比例也不算低,仍有24%的用户在使用。 11.5.10之前的古董版本,也还有公司在使用,估计...
  • pan_tian
  • pan_tian
  • 2012-11-10 11:32
  • 2960
    个人资料
    • 访问:4802357次
    • 积分:51426
    • 等级:
    • 排名:第66名
    • 原创:907篇
    • 转载:54篇
    • 译文:5篇
    • 评论:348条