2 Replies Latest reply on Jan 24, 2009 6:03 PM by 681556

    Where in the icx tables are price break and price break quantities stored?

    681556
      I am trying to build a query from the icx tables that will show me all the BPA line price breaks and quantities.

      I cannot seem to find any documentation on what is specifically is extracted from the BPA lines when an internal catalog build is performed. I am able to validate that in iP, the price breaks are being taken into consideration when I create Requisitions with various quantities, but I do not know where iP is storing this information. Perhaps the data isn't stored and is taken from the core app po_line_locations_all table at the time the requisition is being created?

      I have a BPA, line 27, that has multiple price break lines.

      select rt_item_id, price_type, contract_num, contract_line_num, allow_price_override_flag, not_to_exceed_price, value_basis
      from apps.icx_cat_item_prices
      where contract_num = 'xxxxxx' and contract_line_num = '27'
      and price_type = 'BLANKET';

      All I could find when running this query was the price break information for the first price break line, none of the other price break lines.

      If anybody knows where the documentation is that tells me exactly what is extracted from the core application BPA to the icx tables, I would be greatly appreciative. Even better, if someone already knows the answer to either 1) price break and quantity are not stored in icx tables or 2) they are stored and you have SQL that shows me how to find it, I would be so very appreciative to have this information.

      Edited by: user6287397 on Jan 24, 2009 6:40 AM
        • 1. Re: Where in the icx tables are price break and price break quantities stored?
          Niels LM.
          Hi,

          can U specify version. There are significant changes in R12 compared to 11i10 for the content and as far as I know also from 11.5.7 to 11.5.10 there were made some thurough changes to the loader mechanism.

          /Niels LM
          • 2. Re: Where in the icx tables are price break and price break quantities stored?
            681556
            I got the answer. :-)

            Price breaks details are not stored in any icx tables. iP retrieves the information based on the need by date entered on the requisition.

            Oracle support referred me to the Oracle® Purchasing Release 11i10 Open Interfaces and APIs.

            The java code - SourceDocHelper.java - is responsible to get the price information by calling the procedure po_price_break_grp.get_price_break (POXPRBKB.pls DefaultPricing ) For a given a Source Document (Quotation/Catalog), Quantity and Unit of Measure, this procedure derives the best price for the calling routine.

            The SQL used to get the price information that was sent to me is attached. Note that this SQL uses the need by date to get the right price in case of price break used at the distribution level.

            SELECT poll.price_override
            , round(poll.price_override * v_conversion_rate,
            l_base_curr_ext_precision )
            , poh.rate_date
            , poh.rate
            , poh.currency_code
            , poh.rate_type
            , poll.price_discount
            , poll.price_override
            , decode( poll.line_location_id,
            null, pol.unit_meas_lookup_code,
            poll.unit_meas_lookup_code)
            , poll.line_location_id -- SERVICES FPJ
            FROM po_headers_all poh -- FPI GA
            , po_lines_all pol -- FPI GA
            , po_line_locations_all poll -- FPI GA
            WHERE poh.po_header_id = p_source_document_header_id
            and poh.po_header_id = pol.po_header_id
            and pol.line_num = p_source_document_line_num
            and pol.po_line_id = poll.po_line_id
            and ( p_required_currency is null
            or poh.currency_code = p_required_currency )
            and ( p_required_rate_type is null
            or poh.rate_type = p_required_rate_type )
            and nvl(poll.unit_meas_lookup_code, nvl(p_unit_of_measure,
            pol.unit_meas_lookup_code))
            = nvl(p_unit_of_measure, pol.unit_meas_lookup_code)

            /* */
            /*
            Change sysdate to l_pricing_date in order to use the Need By
            Date
            to determine the price.
            */
            and (trunc(nvl(l_pricing_date, trunc(sysdate))) >= trunc(poll.
            start_date) -- FPJ Custom Price
            OR
            poll.start_date is null)
            and (trunc(nvl(l_pricing_date, trunc(sysdate))) <= trunc(poll.
            end_date) -- FPJ Custom Price
            OR
            poll.end_date is null)
            /* */
            --Bug #2693408: added nvl clause to quantity check
            and nvl(poll.quantity, 0) <= nvl(p_in_quantity, 0)


            /* */
            /*
            Determining the price based on ship-to-location and
            destination organization
            */
            and ((poll.ship_to_location_id = v_ship_to_location_id OR poll.
            ship_to_location_id is null)
            AND
            (poll.ship_to_organization_id = p_destination_org_id OR poll.
            ship_to_organization_id is null))

            /* */

            and poll.shipment_type in ('PRICE BREAK', 'QUOTATION')

            -- <2721775 START>: Make sure Quotation Price Breaks are Approved.
            --
            AND ( -- ( poll.shipment_type IS NULL )
            ( poll.shipment_type = 'PRICE BREAK' )
            OR ( ( poll.shipment_type = 'QUOTATION' )
            AND ( ( poh.approval_required_flag <> 'Y' )
            OR ( EXISTS ( SELECT ('Price Break is Approved')
            FROM po_quotation_approvals pqa
            WHERE pqa.line_location_id = poll.line_location_id
            AND pqa.approval_type IN ('ALL
            ORDERS', 'REQUISITIONS')
            AND trunc(nvl(l_pricing_date,
            sysdate)) -- FPJ Custom Price
            BETWEEN
            trunc(nvl(start_date_active, sysdate-1))
            AND trunc(nvl(end_date
            _active, sysdate+1)))))))
            -- <2721775 END>

            order by poll.ship_to_organization_id ASC, poll.ship_to_location_id ASC,
            NVL(poll.quantity, 0) DESC,
            trunc(poll.creation_date) DESC, poll.price_override ASC; /*
            */