Advanced Pricing by using Get_Custom_Price API

Step 1: Setup system profile

Set the profile option ‘QP: Get Custom Price Customized’ to ‘Yes’. If the profile is set to ‘Yes’, then the pricing engine would execute this function if it is attached to a formula.

Step 2: Create Formula navigate to Pricing --> Pricing Formula --> Formulas Setup

Step 3: Note down the PRICE_FORMULA_ID:

Navigation: Help --> Diagnostics --> Examine

 

Step 4: change in QP_CUSTOM. get_custom_price and compile it.

CREATE OR REPLACE PACKAGE BODY APPS.QP_CUSTOM
AS
   FUNCTION get_custom_price (
      p_price_formula_id       IN NUMBER,
      P_list_price             IN NUMBER,
      P_price_effective_date   IN DATE,
      P_req_line_attrs_tbl     IN qp_formula_price_calc_pvt.req_line_attrs_tbl
   )
      RETURN NUMBER
   IS
      L_item_id                  NUMBER;
      L_organization_id          NUMBER;
      L_header_id                NUMBER;
      L_line_id                  NUMBER;
      l_order_price              NUMBER (9, 2);   -- total order selling price
      l_item_cost                NUMBER (9, 2);
      l_categories               VARCHAR2 (2000);
      l_margin                   VARCHAR2 (150);

      cursor c1 is select MEANING
          from   FND_LOOKUP_VALUES_VL 
          where lookup_type ='GEPS_PRICELIST_FORMULA'
          and ENABLED_FLAG ='Y'
          and nvl(END_DATE_ACTIVE,sysdate) >= sysdate;


   BEGIN
      --sss_proc(1,'start');
      L_item_id := OE_ORDER_PUB.G_LINE.inventory_item_id;
      L_organization_id := OE_ORDER_PUB.G_LINE.ship_from_org_id;
      L_line_id := OE_ORDER_PUB.G_LINE.line_id;

for i in c1 loop

      IF p_price_formula_id = i.MEANING--c_clark_less_20
      THEN
         --sss_proc(2,'start1');

         BEGIN
            SELECT   qp.OPERAND
              INTO   l_order_price
              FROM   apps.qp_list_lines_v qp,
                     inv.mtl_system_items_b mb,
                     apps.qp_list_headers qh
             WHERE   qp.product_attr_value = TO_CHAR (mb.inventory_item_id)
                     AND mb.ORGANIZATION_ID = 104
                     AND qp.list_header_id = qh.list_header_id
                     AND qh.NAME = 'Global USD Pricelist'
                     --and mb.segment1 ='1331501';
                     AND mb.inventory_item_id = L_item_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_order_price := NULL;
         END;


   
      END IF;
end loop;

     RETURN l_order_price;

   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_custom_price;
END qp_custom;
/

Step 5: update formula column in price list.