Catalog query link between QA_PLANS,MTL_SYSTEM_ITEMS_KFV,QA_CHARS ,QA_PLAN_CHARS

 SELECT   DISTINCT PLN.NAME "PLAN_NAME",
                    --PLN.PLAN_ID,
                    --ICG.ITEM_CATALOG_GROUP_ID,
                    --MSI.INVENTORY_ITEM_ID,
                    MSI.CONCATENATED_SEGMENTS ITEM_CODE,
                    MSI.DESCRIPTION ITEM_DESCRIPTION,
                    MSI.PRIMARY_UOM_CODE,
                    qpc.CHAR_NAME "COLLECTION_ELEMENT_NAME",
                    PLN_CHR.RESULT_COLUMN_NAME "COLLECTION_ELEMENT",
                    ELE_VAL.ELEMENT_VALUE
    --  PAR.ORGANIZATION_ID
    FROM   MTL_SYSTEM_ITEMS_KFV MSI,
           MTL_ITEM_CATALOG_GROUPS_VL ICG,
           QA_PLANS PLN,
           MTL_ITEM_CATALOG_GROUPS_VL CAT_GRP,
           MTL_DESCRIPTIVE_ELEMENTS DES_ELE,
           MTL_DESCR_ELEMENT_VALUES ELE_VAL,
           QA_CHARS CHARS,
           QA_PLAN_CHARS PLN_CHR,
           qa_plan_chars_v qpc
   --    MTL_PARAMETERS PAR
   WHERE       MSI.ITEM_CATALOG_GROUP_ID = ICG.ITEM_CATALOG_GROUP_ID
           AND ICG.SEGMENT1 = PLN.NAME
           AND CAT_GRP.ITEM_CATALOG_GROUP_ID = DES_ELE.ITEM_CATALOG_GROUP_ID
           AND CAT_GRP.SEGMENT1 = PLN.NAME
           AND PLN.PLAN_ID = PLN_CHR.PLAN_ID
           AND PLN_CHR.CHAR_ID = CHARS.CHAR_ID
           AND CHARS.NAME = DES_ELE.ELEMENT_NAME
           AND DES_ELE.ELEMENT_NAME = ELE_VAL.ELEMENT_NAME
           AND MSI.INVENTORY_ITEM_ID = ELE_VAL.INVENTORY_ITEM_ID
           AND ELE_VAL.ELEMENT_VALUE IS NOT NULL
           --   and PAR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
           --and PAR.ORGANIZATION_ID =104
           --     AND PLN.PLAN_ID                 = 562
           --    and ELE_VAL.ELEMENT_VALUE
           --  AND ELE_VAL.INVENTORY_ITEM_ID     = 373647
           AND PLN.PLAN_ID = qpc.PLAN_ID
           --and qpc.CHAR_NAME ='ITEM WIDTH IMPERIAL'
           AND DATATYPE_MEANING <> 'Character'
           AND PLN_CHR.RESULT_COLUMN_NAME = qpc.RESULT_COLUMN_NAME
           --and  qpc.RESULT_COLUMN_NAME ='CHARACTER12'
           --  AND PLN_CHR.RESULT_COLUMN_NAME =  'CHARACTER12'
           --   and  MSI.CONCATENATED_SEGMENTS ='JIG3010'
           -- AND PLN.NAME                 ='PULSE JET BAG'--'PLEATED STD CARTRIDGES'
           -- and DECODE( TRANSLATE(ELE_VAL.ELEMENT_VALUE,'0123456789.',' '), NULL, 'N','C') ='C'
           AND LENGTH(TRIM(TRANSLATE (ELE_VAL.ELEMENT_VALUE,
                                      ' +-.0123456789',
                                      ' '))) IS NOT NULL
           AND MSI.ROWID =
                 (SELECT   MIN (ROWID)
                    FROM   MTL_SYSTEM_ITEMS_KFV ITEM
                   WHERE   ITEM.ITEM_CATALOG_GROUP_ID =
                              MSI.ITEM_CATALOG_GROUP_ID
                           AND ITEM.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID)
ORDER BY   PLN.NAME, MSI.CONCATENATED_SEGMENTS

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.

Query to findout the FP on the form

SELECT   ffcr.SEQUENCE "Seq", ffcr.description "Description",
         DECODE (ffcr.rule_type,
                 'F', 'Form',
                 'A', 'Function',
                 'Other'
                ) "Level",
         ffcr.enabled "Enabled", ffcr.trigger_event "Trigger Event",
         ffcr.trigger_object "Trigger Object", ffcr.condition "Condition",
         DECODE (ffcr.fire_in_enter_query,
                 'Y', 'Both',
                 'N', 'Not in Enter-Query Mode',
                 'O', 'Only in Enter-Query Mode',
                 'Other'
                ) "Processing Mode",
         ffca.SEQUENCE "Action Seq", ffca.action_type "Action Type",
         ffca.summary "Action Description", ffca.LANGUAGE "Action Language",
         ffca.enabled "Action Enabled", ffca.property_value,
         ffca.argument_type, ffca.target_object, ffca.object_type,
         ffca.folder_prompt_block, ffca.MESSAGE_TYPE, ffca.MESSAGE_TEXT,
         ffca.builtin_type, ffca.builtin_arguments, ffca.property_name,
         ffca.menu_entry, ffca.menu_label, ffca.menu_seperator,
         ffca.menu_enabled_in, ffca.menu_action, ffca.menu_argument_long,
         ffca.menu_argument_short, ffca.action_id,
         ffca.request_application_id
    FROM apps.fnd_form_custom_rules ffcr, apps.fnd_form_custom_actions ffca
   WHERE ffcr.function_name = 'ENG_BOMFDBOM'
     AND ffcr.form_name = 'BOMFDBOM'
 --    and ffcr.SEQUENCE =64
     AND ffcr.ID = ffca.rule_id(+)
ORDER BY ffcr.SEQUENCE, ffca.SEQUENCE;