Friday 18 July 2014

SQL Query to Extract Oracle Pricing Agreements

/* Formatted on 7/18/2014 9:28:01 AM (QP5 v5.115.810.9015) */
SELECT oat.name "Agreement name",
       oab.revision_date "Revision Date",
       hca.account_number "Bill to customer number",
       hp.party_name "Bill to customer name",
       rt.name "Agreement terms",
       oab.start_date_active "Effective date from",
       oab.end_date_active "Effective date to",
       oab.attribute1 "Meter reading digits",
       qp_price_list_line_util.get_product_value ('QP_ATTR_DEFNS_PRICING',
                                                  qppr.product_attribute_context,
                                                  qppr.product_attribute,
                                                  qppr.product_attr_value
       )
          "Product Value",
       msi.description "Product description",
       qppr.product_uom_code "Unit of measure",
       flv_line_type.meaning "Line Type",
       flv_price_brk.meaning "Price Break Type",
       flv_arth_oprtr.meaning "Application Meansure",
       qpll.start_date_active "Start Date",
       qpll.end_date_active "End Date",
       qppr.pricing_attribute_context "Pricing Context",
       (SELECT qpst.seeded_segment_name
        FROM apps.qp_prc_contexts_b qpcb,
             apps.qp_segments_b qpsb,
             apps.qp_segments_tl qpst
        WHERE     1 = 1
              AND qppr.pricing_attribute_context = qpcb.prc_context_code
              AND qpcb.prc_context_type = 'PRICING_ATTRIBUTE'
              AND qpcb.prc_context_id = qpsb.prc_context_id
              AND qpsb.segment_id = qpst.segment_id
              AND qpsb.segment_mapping_column = qppr.pricing_attribute)
          "Pricing Value",
       qppr.pricing_attr_value_from_number "From Value",
       qppr.pricing_attr_value_to_number "To Value",
       qpll1.operand price
FROM apps.oe_agreements_tl oat,
     apps.oe_agreements_b oab,
     apps.hz_cust_accounts hca,
     apps.hz_parties hp,
     apps.ra_terms_tl rt,
     apps.qp_pricing_attributes qppr,
     apps.qp_list_lines qpll,
     apps.qp_list_lines qpll1,
     apps.mtl_system_items_b msi,
     apps.fnd_lookup_values flv_line_type,
     apps.fnd_lookup_values flv_price_brk,
     apps.fnd_lookup_values flv_arth_oprtr,
     apps.qp_list_headers_b qplh
WHERE     1 = 1
      AND oat.agreement_id = oab.agreement_id
      AND oab.sold_to_org_id = hca.cust_account_id
      AND hca.party_id = hp.party_id
      AND oab.term_id = rt.term_id
      AND oab.price_list_id = qplh.list_header_id
      AND qplh.list_header_id = qpll.list_header_id
      AND qplh.list_header_id = qpll1.list_header_id
      AND qpll.list_line_type_code = 'PBH'
      AND qpll1.list_line_id = qppr.list_line_id
      AND qpll.revision_date = qpll1.revision_date
      AND qpll1.list_line_type_code = 'PLL'
      AND msi.inventory_item_id = qppr.product_attr_value
      AND flv_line_type.lookup_code = qpll.list_line_type_code
      AND flv_line_type.lookup_type = 'LIST_LINE_TYPE_CODE'
      AND flv_price_brk.lookup_code = qpll.price_break_type_code
      AND flv_price_brk.lookup_type = 'PRICE_BREAK_TYPE_CODE'
      AND flv_arth_oprtr.lookup_code = qpll.arithmetic_operator
      AND flv_arth_oprtr.lookup_type = 'ARITHMETIC_OPERATOR'
      AND msi.organization_id = 141;

No comments:

Post a Comment