Oracle Pricing SQL Query

Price List :
     Price lists are essential for ordering products because each item entered on an order must  have a price. Each price list contains basic list information and one or more pricing lines, price breaks, pricing attributes, qualifiers, and secondary price lists.

Modifiers:
      Using modifiers, you can set up price adjustments, benefits, freight and special charges, and promotional limits to control spending or usage. You can define simple discounts and surcharges as well as more advanced deals and promotions. Modifiers can adjust net price either up or down

Qualifiers:
      Qualifiers are used in sync with Price Lists and Modifiers. Qualifiers can be set   According to the business needs. A qualifier consists of one or more conditions that define eligibility for a discount, promotion, or surcharge

SQL QUERY

SELECT qph.NAME "Modifier Name",
       qpl.list_line_no "Modifier Line No",
       qph.description "Modifier Description",
       qph.comments,
       qpl.list_line_type_code,
       qpq.qualifier_context,
       qph.orig_org_id,
       qph.active_flag,
       qpl.start_date_active,
       qpl.end_date_active,
       qph.list_type_code,
       qpl.modifier_level_code,
       qpl.organization_id,
       oeoh.order_number,
       qpl.pricing_phase_id,
       qpl.list_line_no,
       qpq.header_quals_exist_flag,
       qpq.qualifier_datatype,
       qpq.qualifier_attribute,
       qpq.segment_id,
       qpq.CONTEXT,
       qpq.qualifier_grouping_no
  FROM qp_list_headers_all   qph,
       qp_list_lines         qpl,
       qp_pricing_attributes qpa,
       qp_qualifiers         qpq,
       qp_qualifier_rules    qqr,
       oe_order_lines_all    oeol,
       oe_order_headers_all  oeoh
 WHERE qph.list_header_id = qpl.list_header_id
   AND qpl.list_header_id = qpa.list_header_id
   AND qpl.list_line_id = qpq.list_line_id(+)
   AND qqr.qualifier_rule_id(+) = qpq.created_from_rule_id
   AND oeol.price_list_id(+) = qpl.list_header_id
   AND oeol.header_id = oeoh.header_id(+)
   AND qpl.list_line_id = qpa.list_line_id(+)
   AND qpl.pricing_phase_id > 1
   AND qph.name = :P_Price_list_name

Comments

Post a Comment

Popular posts from this blog

Oracle BI Publisher Tags For-each grouping for Matrix report

For-Each Extensions For-each tags can be confusing.  We will be going over the following tags:     For-each@ section :    For-each@ cell   For-each- group :   field1; field2;     For-each-group: current-group() ;field3;   For-each-group@ column       Sample XML:  <CORPORATION>      <NAME>Vision</NAME>     <DEPT>     <DEPT_NAME>IT</DEPT_NAME>      <EMPLOYEE>         <EMPNO>6</EMPNO>        <ENAME>Rich Walker</ENAME>        <JOB>CLERK</JOB>        <SAL>100</SAL>     </<EMPLOYEE>>     <EMPLOYEE>        ...
Read more

SQL Scripts for Custom Table Creation in Oracle Application 12.2.x

Due to introduction of Online Patching, Post 12.2.x version, there are certain steps to be followed while creating Custom Table. 1. For new table creations - after creating the table in custom schema execute below script to generate edition view and synonym for it in APPS schema. exec AD_ZD_TABLE.UPGRADE('xxcust_schema','xxcust_Table'); 2. For table alterations - after running the DDL run below script to regenerate the edition view for synchronizing any table changes. exec AD_ZD_TABLE.PATCH('xxcust_schema','xxcust_table') 3. Table grants must be done through API to avoid invalids. exec AD_ZD.GRANT_PRIVS('SELECT','xxcust_table,'xx_role') 4. To view objects in all editions, add "_ae" to the data dictionary views select * from user_objects_ae; 5. To update seed data tables in Patch edition, execute prepare command. example:  exec ad_zd_seed.prepare('WF_MESSAGES');  The prepare Procedure will creat...
Read more

Oracle Fusion Business Intelligence Roles

Oracle Business Intelligence roles apply to both Oracle Business Intelligence Publisher and Oracle Fusion Transactional Business Intelligence. They grant access to Business Intelligence functionality, such as the ability to run or author reports. These roles are in addition to the roles that grant access to reports, subject areas, Business Intelligence catalog folders, and HCM data. This topic describes the Business Intelligence roles. This table lists the Business Intelligence roles. Business Intelligence Role Description BI Consumer Role Runs Business Intelligence reports. BI Author Role Creates and edits reports. BI Administrator Role Performs administrative tasks such as creating and editing dashboards and modifying security permissions for reports, folders, and so on. BI Publisher Data Model Developer Role Creates and edits Business Intelligence Publisher data models. BI Consumer Role The predefined Transactional Business Intelligence Transacti...
Read more