8 Replies Latest reply: Jun 7, 2013 6:11 AM by user7751421 RSS

    Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different

    user7751421
      Hi,

      I have trouble in using QP_PREQ_PUB.PRICE_REQUEST to derive the price of an item.

      RDBMS : 11.1.0.7.0
      Oracle Applications : 12.1.3

      From the Pricing Request Viewer or from Order Entry Line, The price lis coming as expected, but not from QP_PREQ_PUB.PRICE_REQUEST API.

      Here are the details -

      Primary Price List - 'Primary'
      Secondary Price List - 'Secondary1' - Item Price 40 (Precedence 150)
      Secondary Price List - 'Secondary2' - Item Price 25 (Precedence 130) , Custom Qualifier Qualifier_Attribute31 = > Attribute1('Yes/No') from Item Master.

      From the application, it is giving 25. Where as using the below code it is giving 40, Can you please advise what could be the issue/fix for it?


      DECLARE
      p_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
      p_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
      p_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
      p_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
      p_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
      p_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
      p_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
      p_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
      x_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
      x_line_qual QP_PREQ_GRP.QUAL_TBL_TYPE;
      x_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
      x_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
      x_line_detail_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
      x_line_detail_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
      x_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
      x_return_status VARCHAR2(240);
      x_return_status_text VARCHAR2(240);
      qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
      line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
      line_rec QP_PREQ_GRP.LINE_REC_TYPE;
      detail_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
      ldet_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
      rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
      x_price_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
      x_qual_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
      v_line_tbl_cnt integer;

      I BINARY_INTEGER;
      l_version VARCHAR2(240);
      l_file_val VARCHAR2(60);
      L_MODIFIER VARCHAR2(4000);
      L_ADJUSTMENT_AMOUNT VARCHAR2(300);
      L_MOD_NAME VARCHAR2(240);
      l_unit_price number;
      l_selling_price number;

      BEGIN

      MO_GLOBAL.INIT('ONT');
      MO_GLOBAL.SET_POLICY_CONTEXT('S',89);
      --FND_GLOBAL.APPS_INITIALIZE(1130,21623,660);


      DBMS_OUTPUT.PUT_LINE('1');
      v_line_tbl_cnt := 1;

      ---- Control Record
      p_control_rec.pricing_event := 'BATCH';
      p_control_rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
      p_control_rec.simulation_flag := 'Y';
      p_control_rec.source_order_amount_flag := 'Y';
      p_control_rec.PUBLIC_API_CALL_FLAG := 'N';
      p_control_rec.rounding_flag := 'Q';
      p_control_Rec.manual_discount_flag := 'N';
      --p_control_rec.MANUAL_ADJUSTMENTS_CALL_FLAG := 'Y';
      p_control_rec.request_type_code := 'ONT';
      p_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';

      DBMS_OUTPUT.PUT_LINE('2');

      line_rec.request_type_code :='ONT';
      --line_rec.PRICE_LIST_HEADER_ID := 8007;
      line_rec.line_id :=2125125; -- Order Line Id. This can be any thing for this script
      line_rec.line_Index :=1; -- Request Line Index
      line_rec.line_type_code := 'LINE'; -- LINE or ORDER(Summary Line)
      line_rec.pricing_effective_date := TRUNC(sysdate); -- Pricing as of what date ?
      line_rec.active_date_first := TRUNC(sysdate); -- Can be Ordered Date or Ship Date
      line_rec.active_date_second := TRUNC(sysdate); -- Can be Ordered Date or Ship Date
      line_rec.active_date_first_type := 'ORD'; -- ORD/SHIP
      line_rec.active_date_second_type :='SHIP'; -- ORD/SHIP
      line_rec.line_quantity := 1; -- Ordered Quantity
      line_rec.line_uom_code := 'EA'; -- Ordered UOM Code
      line_rec.currency_code := 'USD'; -- Currency Code
      line_rec.price_flag := 'Y'; -- Price Flag can have 'Y' , 'N'(No pricing) , 'P'(Phase)
      p_line_tbl(1) := line_rec;

      DBMS_OUTPUT.PUT_LINE('3');
      ---- Line Attribute Record
      line_attr_rec.LINE_INDEX := 1;
      line_attr_rec.PRICING_CONTEXT :='ITEM'; --
      line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE3';
      line_attr_rec.PRICING_ATTR_VALUE_FROM :='ALL';
      line_attr_rec.VALIDATED_FLAG :='Y';
      p_line_attr_tbl(1) := line_attr_rec;


      line_attr_rec.LINE_INDEX := 1;
      line_attr_rec.PRICING_CONTEXT :='ITEM'; --
      line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
      --line_attr_rec.COMPARISON_OPERATOR_CODE         := 'BETWEEN';
      line_attr_rec.PRICING_ATTR_VALUE_FROM := '4466'; -- INVENTORY ITEM ID
      line_attr_rec.VALIDATED_FLAG :='Y';
      p_line_attr_tbl(2) := line_attr_rec;


      line_attr_rec.LINE_INDEX := 1;
      line_attr_rec.PRICING_CONTEXT :='ITEM'; --
      line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
      --line_attr_rec.COMPARISON_OPERATOR_CODE         := 'BETWEEN';
      line_attr_rec.PRICING_ATTR_VALUE_FROM := '2395'; -- INVENTORY ITEM ID
      line_attr_rec.VALIDATED_FLAG :='Y';
      p_line_attr_tbl(3) := line_attr_rec;

      line_attr_rec.LINE_INDEX := 1;
      line_attr_rec.PRICING_CONTEXT :='ITEM'; --
      line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE30';
      --line_attr_rec.COMPARISON_OPERATOR_CODE         := 'BETWEEN';
      line_attr_rec.PRICING_ATTR_VALUE_FROM := 'Yes'; -- INVENTORY ITEM ID
      line_attr_rec.VALIDATED_FLAG :='Y';
      p_line_attr_tbl(3) := line_attr_rec;


      DBMS_OUTPUT.PUT_LINE('4');

      ---- Qualifier Attribute Record

      qual_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index
      qual_rec.QUALIFIER_CONTEXT :='MODLIST';
      qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE4';
      qual_rec.QUALIFIER_ATTR_VALUE_FROM :='8007'; -- PRICE LIST ID
      qual_rec.COMPARISON_OPERATOR_CODE := '=';
      qual_rec.VALIDATED_FLAG :='Y';
      p_qual_tbl(1) := qual_rec;

      DBMS_OUTPUT.PUT_LINE('5');

      qual_rec.line_index := 1;
      qual_rec.qualifier_context := 'ITEM';
      qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE31';
      qual_rec.qualifier_attr_value_from := 'Yes';
      qual_rec.comparison_operator_code := '=';
      qual_rec.validated_flag := 'Y';
      p_qual_tbl (2) := qual_rec;

      DBMS_OUTPUT.PUT_LINE('6');

      OE_ORDER_PUB.G_LINE.inventory_item_id := 4466;

      DBMS_OUTPUT.PUT_LINE(Fnd_Profile.VALUE('QP_CUSTOM_SOURCED') );


      --QP_Attr_Mapping_PUB.Build_Contexts
      --( p_request_type_code => 'ONT',
      -- p_line_index => 1,
      -- p_pricing_type_code => 'L'
      --);

      QP_Attr_Mapping_PUB.Build_Contexts(
      p_request_type_code => 'ONT',
      p_pricing_type => 'L',
      --p_org_id => 89,
      x_price_contexts_result_tbl => x_price_contexts_result_tbl,
      x_qual_contexts_result_tbl => x_qual_contexts_result_tbl );


      I := x_qual_contexts_result_tbl.FIRST;
      IF I IS NOT NULL THEN
      LOOP
      DBMS_OUTPUT.PUT_LINE('Context: '||x_qual_contexts_result_tbl(I).context_name);
      DBMS_OUTPUT.PUT_LINE('attribute_name: '||x_qual_contexts_result_tbl(I).attribute_name);
      DBMS_OUTPUT.PUT_LINE('attribute_value: '||x_qual_contexts_result_tbl(I).attribute_value);
      EXIT WHEN I = x_qual_contexts_result_tbl.LAST;
      I := x_qual_contexts_result_tbl.NEXT(I);
      END LOOP;
      END IF;


      I := x_price_contexts_result_tbl.FIRST;
      IF I IS NOT NULL THEN
      LOOP
      DBMS_OUTPUT.PUT_LINE('Context: '||x_price_contexts_result_tbl(I).context_name);
      DBMS_OUTPUT.PUT_LINE('attribute_name: '||x_price_contexts_result_tbl(I).attribute_name);
      DBMS_OUTPUT.PUT_LINE('attribute_value: '||x_price_contexts_result_tbl(I).attribute_value);
      EXIT WHEN I = x_price_contexts_result_tbl.LAST;
      I := x_price_contexts_result_tbl.NEXT(I);
      END LOOP;
      END IF;



      DBMS_OUTPUT.PUT_LINE(Fnd_Profile.VALUE('QP_CUSTOM_SOURCED') );


      DBMS_OUTPUT.PUT_LINE('7');

      l_version := QP_PREQ_GRP.GET_VERSION;

      dbms_output.put_line(OE_ORDER_PUB.G_LINE.price_list_id);

      dbms_output.put_line(OE_ORDER_PUB.G_LINE.inventory_item_id);

      QP_PREQ_PUB.PRICE_REQUEST
      (p_line_tbl,
      p_qual_tbl,
      p_line_attr_tbl,
      p_line_detail_tbl,
      p_line_detail_qual_tbl,
      p_line_detail_attr_tbl,
      p_related_lines_tbl,
      p_control_rec,
      x_line_tbl,
      x_line_qual,
      x_line_attr_tbl,
      x_line_detail_tbl,
      x_line_detail_qual_tbl,
      x_line_detail_attr_tbl,
      x_related_lines_tbl,
      x_return_status,
      x_return_status_text);


      -- Return Status Information ..
      DBMS_OUTPUT.PUT_LINE('8');
      DBMS_OUTPUT.PUT_LINE('Return Status text '|| x_return_status_text);
      DBMS_OUTPUT.PUT_LINE('Return Status '|| x_return_status);

      DBMS_OUTPUT.PUT_LINE('+---------Information Returned to Caller---------------------+ ');

      DBMS_OUTPUT.PUT_LINE('-------------Request Line Information-------------------');

      I := x_line_tbl.FIRST;
      IF I IS NOT NULL THEN
      LOOP
      DBMS_OUTPUT.PUT_LINE('Line Index: '||x_line_tbl(I).line_index);
      DBMS_OUTPUT.PUT_LINE('Unit_price: '||x_line_tbl(I).unit_price);
      DBMS_OUTPUT.PUT_LINE('Percent price: '||x_line_tbl(I).percent_price);
      DBMS_OUTPUT.PUT_LINE('Adjusted Unit Price: '||x_line_tbl(I).adjusted_unit_price);
      DBMS_OUTPUT.PUT_LINE('Pricing status code: '||x_line_tbl(I).status_code);
      DBMS_OUTPUT.PUT_LINE('Pricing status text: '||x_line_tbl(I).status_text);
      EXIT WHEN I = x_line_tbl.LAST;
      I := x_line_tbl.NEXT(I);
      END LOOP;
      END IF;

      DBMS_OUTPUT.PUT_LINE('-----------Pricing Attributes Information-------------');

      I := x_line_detail_attr_tbl.FIRST;
      IF I IS NOT NULL THEN
      LOOP

      DBMS_OUTPUT.PUT_LINE('Line detail Index '||x_line_detail_attr_tbl(I).line_detail_index);
      DBMS_OUTPUT.PUT_LINE('Context '||x_line_detail_attr_tbl(I).pricing_context);
      DBMS_OUTPUT.PUT_LINE('Attribute '||x_line_detail_attr_tbl(I).pricing_attribute);
      DBMS_OUTPUT.PUT_LINE('Value '||x_line_detail_attr_tbl(I).pricing_attr_value_from);
      DBMS_OUTPUT.PUT_LINE('Status Code '||x_line_detail_attr_tbl(I).status_code);
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');

      EXIT WHEN I = x_line_detail_attr_tbl.last;
      I:=x_line_detail_attr_tbl.NEXT(I);

      END LOOP;
      END IF;

      DBMS_OUTPUT.PUT_LINE('-----------Qualifier Attributes Information-------------');

      I := x_line_detail_qual_tbl.FIRST;
      IF I IS NOT NULL THEN
      LOOP
      DBMS_OUTPUT.PUT_LINE('Line Detail Index '||x_line_detail_qual_tbl(I).line_detail_index);
      DBMS_OUTPUT.PUT_LINE('Context '||x_line_detail_qual_tbl(I).qualifier_context);
      DBMS_OUTPUT.PUT_LINE('Attribute '||x_line_detail_qual_tbl(I).qualifier_attribute);
      DBMS_OUTPUT.PUT_LINE('Value '||x_line_detail_qual_tbl(I).qualifier_attr_value_from);
      DBMS_OUTPUT.PUT_LINE('Status Code '||x_line_detail_qual_tbl(I).status_code);
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');

      EXIT WHEN I = x_line_detail_qual_tbl.last;
      I:=x_line_detail_qual_tbl.NEXT(I);

      END LOOP;
      END IF;

      I := x_line_detail_tbl.FIRST;

      DBMS_OUTPUT.PUT_LINE('------------Price List/Discount Information------------');

      IF I IS NOT NULL THEN
      LOOP
      L_MOD_NAME := '';
      DBMS_OUTPUT.PUT_LINE('Line Index: '||x_line_detail_tbl(I).line_index);
      DBMS_OUTPUT.PUT_LINE('Line Detail Index: '||x_line_detail_tbl(I).line_detail_index);
      DBMS_OUTPUT.PUT_LINE('Line Detail Type:'||x_line_detail_tbl(I).line_detail_type_code);
      DBMS_OUTPUT.PUT_LINE('List Header Id: '||x_line_detail_tbl(I).list_header_id);
      DBMS_OUTPUT.PUT_LINE('List Line Id: '||x_line_detail_tbl(I).list_line_id);
      DBMS_OUTPUT.PUT_LINE('List Line Type Code: '||x_line_detail_tbl(I).list_line_type_code);
      DBMS_OUTPUT.PUT_LINE('Adjustment Amount : '||x_line_detail_tbl(I).adjustment_amount);
      DBMS_OUTPUT.PUT_LINE('Line Quantity : '||x_line_detail_tbl(I).line_quantity);
      DBMS_OUTPUT.PUT_LINE('Operand Calculation Code: '||x_line_detail_tbl(I).Operand_calculation_code);
      DBMS_OUTPUT.PUT_LINE('Operand value: '||x_line_detail_tbl(I).operand_value);
      DBMS_OUTPUT.PUT_LINE('Automatic Flag: '||x_line_detail_tbl(I).automatic_flag);
      DBMS_OUTPUT.PUT_LINE('Override Flag: '||x_line_detail_tbl(I).override_flag);
      DBMS_OUTPUT.PUT_LINE('status_code: '||x_line_detail_tbl(I).status_code);
      DBMS_OUTPUT.PUT_LINE('status text: '||x_line_detail_tbl(I).status_text);
      DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
      EXIT WHEN I = x_line_detail_tbl.LAST;

      BEGIN
      SELECT NAME INTO L_MOD_NAME
      FROM QP_LIST_HEADERS_ALL
      WHERE LIST_HEADER_ID=x_line_detail_tbl(I).list_header_id
      AND x_line_detail_tbl(I).list_line_type_code <>'PLL'
      AND x_line_detail_tbl(I).adjustment_amount IS NOT NULL;
      L_MODIFIER := L_MODIFIER||'~'||L_MOD_NAME;
      EXCEPTION
      WHEN OTHERS THEN
      NULL;
      END;



      IF x_line_detail_tbl(I).list_line_type_code <>'PLL' AND x_line_detail_tbl(I).adjustment_amount IS NOT NULL THEN
      L_ADJUSTMENT_AMOUNT := L_ADJUSTMENT_AMOUNT||'~'||x_line_detail_tbl(I).adjustment_amount;
      END IF;
      I := x_line_detail_tbl.NEXT(I);
      END LOOP;
      END IF;

      SELECT line_unit_price , order_uom_selling_price
      INTO l_unit_price , l_selling_price
      FROM qp_preq_lines_tmp
      WHERE pricing_status_code = Qp_Preq_Pub.g_status_updated
      ORDER BY line_id;

      DBMS_OUTPUT.PUT_LINE('Unit Price := '||l_unit_price);--x_line_tbl(1).UNIT_PRICE );
      DBMS_OUTPUT.PUT_LINE('Selling Price := '||l_selling_price);---x_line_tbl(1).LINE_UNIT_PRICE );
      DBMS_OUTPUT.PUT_LINE('L_MODIFIER: '||L_MODIFIER);
      DBMS_OUTPUT.PUT_LINE('L_ADJUSTMENT_AMOUNT: '||L_ADJUSTMENT_AMOUNT);
      DBMS_OUTPUT.PUT_LINE('--------------Related Lines Information for Price Breaks/Service Items---------------');

      I := x_related_lines_tbl.FIRST;
      IF I IS NOT NULL THEN
      LOOP
      DBMS_OUTPUT.PUT_LINE('Line Index :'||x_related_lines_tbl(I).line_index);
      DBMS_OUTPUT.PUT_LINE('Line Detail Index: '||x_related_lines_tbl(I).LINE_DETAIL_INDEX);
      DBMS_OUTPUT.PUT_LINE('Relationship Type Code: '||x_related_lines_tbl(I).relationship_type_code);
      DBMS_OUTPUT.PUT_LINE('Related Line Index: '||x_related_lines_tbl(I).RELATED_LINE_INDEX);
      DBMS_OUTPUT.PUT_LINE('Related Line Detail Index: '||x_related_lines_tbl(I).related_line_detail_index);
      DBMS_OUTPUT.PUT_LINE('Status Code: '|| x_related_lines_tbl(I).STATUS_CODE);
      EXIT WHEN I = x_related_lines_tbl.LAST;
      I := x_related_lines_tbl.NEXT(I);
      END LOOP;




      END IF;

      END; -- procedure --;

      Edited by: user7751421 on Apr 20, 2013 8:54 AM
        • 1. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
          Activesometimes-Oracle
          Why are you passing two inventory item id's for 'PRICING_ATTRIBUTE1' ?
          • 2. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
            user7751421
            Answer is

            Pass ine_attr_rec.VALIDATED_FLAG :='Y' as 'N' instead of 'Y' which will consider the qualifiers on the price list.
            • 3. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
              Akil Pariyani
              I tried with bellow code but it shows both Unit Price (price after apply all adjustment) and List price same_

              What I need is get unit selling price before order book, when customer called for inquiry only._

              DECLARE
              p_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
              p_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
              p_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
              p_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
              p_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
              p_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
              p_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
              p_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
              x_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
              x_line_qual QP_PREQ_GRP.QUAL_TBL_TYPE;
              x_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
              x_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
              x_line_detail_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
              x_line_detail_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
              x_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
              x_return_status VARCHAR2 (240);
              x_return_status_text VARCHAR2 (240);
              qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
              line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
              line_rec QP_PREQ_GRP.LINE_REC_TYPE;
              detail_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
              ldet_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
              rltd_rec QP_PREQ_GRP.RELATED_LINES_REC_TYPE;
              x_price_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
              x_qual_contexts_result_tbl QP_Attr_Mapping_PUB.Contexts_Result_Tbl_Type;
              v_line_tbl_cnt INTEGER;

              I BINARY_INTEGER;
              l_version VARCHAR2 (240);
              l_file_val VARCHAR2 (60);
              L_MODIFIER VARCHAR2 (4000);
              L_ADJUSTMENT_AMOUNT VARCHAR2 (300);
              L_MOD_NAME VARCHAR2 (240);
              l_unit_price NUMBER;
              l_selling_price NUMBER;
              l_item_id number:=17155;
              BEGIN
              MO_GLOBAL.INIT ('ONT');
              MO_GLOBAL.SET_POLICY_CONTEXT ('S', 91);
              --FND_GLOBAL.APPS_INITIALIZE(1130,21623,660);


              DBMS_OUTPUT.PUT_LINE ('1');
              v_line_tbl_cnt := 1;

              ---- Control Record
              p_control_rec.pricing_event := 'LINE';
              p_control_rec.calculate_flag := 'Y';
              p_control_rec.simulation_flag := 'Y';
              p_control_rec.source_order_amount_flag := 'Y';
              p_control_rec.PUBLIC_API_CALL_FLAG := 'N';
              p_control_rec.rounding_flag := 'Q';
              p_control_Rec.manual_discount_flag := 'N';
              --p_control_rec.MANUAL_ADJUSTMENTS_CALL_FLAG := 'Y';
              p_control_rec.request_type_code := 'ONT';
              p_control_rec.TEMP_TABLE_INSERT_FLAG := 'Y';

              DBMS_OUTPUT.PUT_LINE ('2');

              line_rec.request_type_code := 'ONT';
              -- line_rec.PRICE_LIST_HEADER_ID := 386481;
              line_rec.line_id := 72202; -- Order Line Id. This can be any thing for this script
              line_rec.line_Index := 1; -- Request Line Index
              line_rec.line_type_code := 'LINE'; -- LINE or ORDER(Summary Line)
              line_rec.pricing_effective_date := TRUNC (SYSDATE); -- Pricing as of what date ?
              line_rec.active_date_first := TRUNC (SYSDATE); -- Can be Ordered Date or Ship Date
              line_rec.active_date_second := TRUNC (SYSDATE); -- Can be Ordered Date or Ship Date
              line_rec.active_date_first_type := 'ORD'; -- ORD/SHIP
              line_rec.active_date_second_type := 'SHIP'; -- ORD/SHIP
              line_rec.line_quantity := 10; -- Ordered Quantity
              line_rec.line_uom_code := 'Ea'; -- Ordered UOM Code
              line_rec.currency_code := 'USD'; -- Currency Code
              line_rec.price_flag := 'Y'; -- Price Flag can have 'Y' , 'N'(No pricing) , 'P'(Phase)
              p_line_tbl (1) := line_rec;

              DBMS_OUTPUT.PUT_LINE ('3');
              ---- Line Attribute Record
              line_attr_rec.LINE_INDEX := 1;
              line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
              line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE3';
              line_attr_rec.PRICING_ATTR_VALUE_FROM := 'ALL';
              line_attr_rec.VALIDATED_FLAG := 'N';
              p_line_attr_tbl (1) := line_attr_rec;


              line_attr_rec.LINE_INDEX := 1;
              line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
              line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
              --line_attr_rec.COMPARISON_OPERATOR_CODE := 'BETWEEN';
              line_attr_rec.PRICING_ATTR_VALUE_FROM := '17155'; -- INVENTORY ITEM ID
              line_attr_rec.VALIDATED_FLAG := 'Y';
              p_line_attr_tbl (2) := line_attr_rec;


              -- line_attr_rec.LINE_INDEX := 1;
              -- line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
              -- line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE1';
              -- --line_attr_rec.COMPARISON_OPERATOR_CODE := 'BETWEEN';
              -- line_attr_rec.PRICING_ATTR_VALUE_FROM := '72201'; -- INVENTORY ITEM ID
              -- line_attr_rec.VALIDATED_FLAG := 'Y';
              -- p_line_attr_tbl (3) := line_attr_rec;
              --
              -- line_attr_rec.LINE_INDEX := 1;
              -- line_attr_rec.PRICING_CONTEXT := 'ITEM'; --
              -- line_attr_rec.PRICING_ATTRIBUTE := 'PRICING_ATTRIBUTE30';
              -- --line_attr_rec.COMPARISON_OPERATOR_CODE := 'BETWEEN';
              -- line_attr_rec.PRICING_ATTR_VALUE_FROM := 'Yes'; -- INVENTORY ITEM ID
              -- line_attr_rec.VALIDATED_FLAG := 'Y';
              -- p_line_attr_tbl (3) := line_attr_rec;


              DBMS_OUTPUT.PUT_LINE ('4');

              ---- Qualifier Attribute Record

              qual_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index
              qual_rec.QUALIFIER_CONTEXT := 'MODLIST';
              qual_rec.QUALIFIER_ATTRIBUTE := 'QUALIFIER_ATTRIBUTE4';
              qual_rec.QUALIFIER_ATTR_VALUE_FROM := '386481'; -- PRICE LIST ID
              qual_rec.COMPARISON_OPERATOR_CODE := '=';
              qual_rec.VALIDATED_FLAG := 'Y';
              p_qual_tbl (1) := qual_rec;

              DBMS_OUTPUT.PUT_LINE ('5');

              -- qual_rec.line_index := 1;
              -- qual_rec.qualifier_context := 'ITEM';
              -- qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE31';
              -- qual_rec.qualifier_attr_value_from := 'Yes';
              -- qual_rec.comparison_operator_code := '=';
              -- qual_rec.validated_flag := 'Y';
              -- p_qual_tbl (2) := qual_rec;

              qual_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index
              qual_rec.QUALIFIER_CONTEXT := 'CUSTOMER';
              qual_rec.QUALIFIER_ATTRIBUTE := 'QUALIFIER_ATTRIBUTE5';
              qual_rec.QUALIFIER_ATTR_VALUE_FROM := 5728; -- Customer Account ID
              qual_rec.COMPARISON_OPERATOR_CODE := '=';
              qual_rec.VALIDATED_FLAG := 'Y';
              p_qual_tbl (3) := qual_rec;

              qual_rec.LINE_INDEX := 1;
              qual_rec.QUALIFIER_CONTEXT := 'ITEM_CATEGORY';
              qual_rec.QUALIFIER_ATTRIBUTE := 'QUALIFIER_ATTRIBUTE35';
              qual_rec.QUALIFIER_ATTR_VALUE_FROM := 'TOOLS.HARDWARE TOOLS (DISCOUNTABLE).'; -- item category
              qual_rec.COMPARISON_OPERATOR_CODE := '=';
              qual_rec.VALIDATED_FLAG := 'N';
              p_qual_tbl (4) := qual_rec;

              DBMS_OUTPUT.PUT_LINE ('6');

              OE_ORDER_PUB.G_LINE.inventory_item_id := l_item_id;

              DBMS_OUTPUT.PUT_LINE (Fnd_Profile.VALUE ('QP_CUSTOM_SOURCED'));


              --QP_Attr_Mapping_PUB.Build_Contexts
              --( p_request_type_code => 'ONT',
              -- p_line_index => 1,
              -- p_pricing_type_code => 'L'
              --);

              QP_Attr_Mapping_PUB.
              Build_Contexts (
              p_request_type_code => 'ONT',
              p_pricing_type => 'L',
              --p_org_id => 89,
              x_price_contexts_result_tbl => x_price_contexts_result_tbl,
              x_qual_contexts_result_tbl => x_qual_contexts_result_tbl);


              I := x_qual_contexts_result_tbl.FIRST;

              IF I IS NOT NULL
              THEN
              LOOP
              DBMS_OUTPUT.
              PUT_LINE (
              'Context: ' || x_qual_contexts_result_tbl (I).context_name);
              DBMS_OUTPUT.
              PUT_LINE (
              'attribute_name: '
              || x_qual_contexts_result_tbl (I).attribute_name);
              DBMS_OUTPUT.
              PUT_LINE (
              'attribute_value: '
              || x_qual_contexts_result_tbl (I).attribute_value);
              EXIT WHEN I = x_qual_contexts_result_tbl.LAST;
              I := x_qual_contexts_result_tbl.NEXT (I);
              END LOOP;
              END IF;


              I := x_price_contexts_result_tbl.FIRST;

              IF I IS NOT NULL
              THEN
              LOOP
              DBMS_OUTPUT.
              PUT_LINE (
              'Context: ' || x_price_contexts_result_tbl (I).context_name);
              DBMS_OUTPUT.
              PUT_LINE (
              'attribute_name: '
              || x_price_contexts_result_tbl (I).attribute_name);
              DBMS_OUTPUT.
              PUT_LINE (
              'attribute_value: '
              || x_price_contexts_result_tbl (I).attribute_value);
              EXIT WHEN I = x_price_contexts_result_tbl.LAST;
              I := x_price_contexts_result_tbl.NEXT (I);
              END LOOP;
              END IF;



              DBMS_OUTPUT.PUT_LINE (' Fnd_Profile.VALUE (QP_CUSTOM_SOURCED): ' || Fnd_Profile.VALUE ('QP_CUSTOM_SOURCED'));


              DBMS_OUTPUT.PUT_LINE ('7');

              l_version := QP_PREQ_GRP.GET_VERSION;

              DBMS_OUTPUT.put_line (OE_ORDER_PUB.G_LINE.price_list_id);

              DBMS_OUTPUT.put_line (OE_ORDER_PUB.G_LINE.inventory_item_id);

              QP_PREQ_PUB.PRICE_REQUEST (p_line_tbl,
              p_qual_tbl,
              p_line_attr_tbl,
              p_line_detail_tbl,
              p_line_detail_qual_tbl,
              p_line_detail_attr_tbl,
              p_related_lines_tbl,
              p_control_rec,
              x_line_tbl,
              x_line_qual,
              x_line_attr_tbl,
              x_line_detail_tbl,
              x_line_detail_qual_tbl,
              x_line_detail_attr_tbl,
              x_related_lines_tbl,
              x_return_status,
              x_return_status_text);


              -- Return Status Information ..
              DBMS_OUTPUT.PUT_LINE ('8');
              DBMS_OUTPUT.PUT_LINE ('Return Status text ' || x_return_status_text);
              DBMS_OUTPUT.PUT_LINE ('Return Status ' || x_return_status);

              DBMS_OUTPUT.
              PUT_LINE (
              '+---------Information Returned to Caller---------------------+ ');

              DBMS_OUTPUT.
              PUT_LINE ('-------------Request Line Information-------------------');

              I := x_line_tbl.FIRST;

              IF I IS NOT NULL
              THEN
              LOOP
              DBMS_OUTPUT.PUT_LINE ('Line Index: ' || x_line_tbl (I).line_index);
              DBMS_OUTPUT.PUT_LINE ('Unit_price: ' || x_line_tbl (I).unit_price);
              DBMS_OUTPUT.
              PUT_LINE ('Percent price: ' || x_line_tbl (I).percent_price);
              DBMS_OUTPUT.
              PUT_LINE (
              'Adjusted Unit Price: ' || x_line_tbl (I).adjusted_unit_price);
              DBMS_OUTPUT.
              PUT_LINE ('Pricing status code: ' || x_line_tbl (I).status_code);
              DBMS_OUTPUT.
              PUT_LINE ('Pricing status text: ' || x_line_tbl (I).status_text);
              EXIT WHEN I = x_line_tbl.LAST;
              I := x_line_tbl.NEXT (I);
              END LOOP;
              END IF;

              DBMS_OUTPUT.
              PUT_LINE ('-----------Pricing Attributes Information-------------');

              I := x_line_detail_attr_tbl.FIRST;

              IF I IS NOT NULL
              THEN
              LOOP
              DBMS_OUTPUT.
              PUT_LINE (
              'Line detail Index '
              || x_line_detail_attr_tbl (I).line_detail_index);
              DBMS_OUTPUT.
              PUT_LINE ('Context ' || x_line_detail_attr_tbl (I).pricing_context);
              DBMS_OUTPUT.
              PUT_LINE (
              'Attribute ' || x_line_detail_attr_tbl (I).pricing_attribute);
              DBMS_OUTPUT.
              PUT_LINE (
              'Value ' || x_line_detail_attr_tbl (I).pricing_attr_value_from);
              DBMS_OUTPUT.
              PUT_LINE ('Status Code ' || x_line_detail_attr_tbl (I).status_code);
              DBMS_OUTPUT.
              PUT_LINE ('---------------------------------------------------');

              EXIT WHEN I = x_line_detail_attr_tbl.LAST;
              I := x_line_detail_attr_tbl.NEXT (I);
              END LOOP;
              END IF;

              DBMS_OUTPUT.
              PUT_LINE ('-----------Qualifier Attributes Information-------------');

              I := x_line_detail_qual_tbl.FIRST;

              IF I IS NOT NULL
              THEN
              LOOP
              DBMS_OUTPUT.
              PUT_LINE (
              'Line Detail Index '
              || x_line_detail_qual_tbl (I).line_detail_index);
              DBMS_OUTPUT.
              PUT_LINE (
              'Context ' || x_line_detail_qual_tbl (I).qualifier_context);
              DBMS_OUTPUT.
              PUT_LINE (
              'Attribute ' || x_line_detail_qual_tbl (I).qualifier_attribute);
              DBMS_OUTPUT.
              PUT_LINE (
              'Value ' || x_line_detail_qual_tbl (I).qualifier_attr_value_from);
              DBMS_OUTPUT.
              PUT_LINE ('Status Code ' || x_line_detail_qual_tbl (I).status_code);
              DBMS_OUTPUT.
              PUT_LINE ('---------------------------------------------------');

              EXIT WHEN I = x_line_detail_qual_tbl.LAST;
              I := x_line_detail_qual_tbl.NEXT (I);
              END LOOP;
              END IF;

              I := x_line_detail_tbl.FIRST;

              DBMS_OUTPUT.
              PUT_LINE ('------------Price List/Discount Information------------');

              IF I IS NOT NULL
              THEN
              LOOP
              L_MOD_NAME := '';
              DBMS_OUTPUT.
              PUT_LINE ('Line Index: ' || x_line_detail_tbl (I).line_index);
              DBMS_OUTPUT.
              PUT_LINE (
              'Line Detail Index: ' || x_line_detail_tbl (I).line_detail_index);
              DBMS_OUTPUT.
              PUT_LINE (
              'Line Detail Type:'
              || x_line_detail_tbl (I).line_detail_type_code);
              DBMS_OUTPUT.
              PUT_LINE (
              'List Header Id: ' || x_line_detail_tbl (I).list_header_id);
              DBMS_OUTPUT.
              PUT_LINE ('List Line Id: ' || x_line_detail_tbl (I).list_line_id);
              DBMS_OUTPUT.
              PUT_LINE (
              'List Line Type Code: '
              || x_line_detail_tbl (I).list_line_type_code);
              DBMS_OUTPUT.
              PUT_LINE (
              'Adjustment Amount : ' || x_line_detail_tbl (I).adjustment_amount);
              DBMS_OUTPUT.
              PUT_LINE ('Line Quantity : ' || x_line_detail_tbl (I).line_quantity);
              DBMS_OUTPUT.
              PUT_LINE (
              'Operand Calculation Code: '
              || x_line_detail_tbl (I).Operand_calculation_code);
              DBMS_OUTPUT.
              PUT_LINE ('Operand value: ' || x_line_detail_tbl (I).operand_value);
              DBMS_OUTPUT.
              PUT_LINE (
              'Automatic Flag: ' || x_line_detail_tbl (I).automatic_flag);
              DBMS_OUTPUT.
              PUT_LINE ('Override Flag: ' || x_line_detail_tbl (I).override_flag);
              DBMS_OUTPUT.
              PUT_LINE ('status_code: ' || x_line_detail_tbl (I).status_code);
              DBMS_OUTPUT.
              PUT_LINE ('status text: ' || x_line_detail_tbl (I).status_text);
              DBMS_OUTPUT.PUT_LINE ('-------------------------------------------');
              EXIT WHEN I = x_line_detail_tbl.LAST;


              BEGIN
              SELECT NAME
              INTO L_MOD_NAME
              FROM QP_LIST_HEADERS_ALL
              WHERE LIST_HEADER_ID = x_line_detail_tbl (I).list_header_id
              AND x_line_detail_tbl (I).list_line_type_code = 'PLL'
              AND x_line_detail_tbl (I).adjustment_amount IS NOT NULL;

              L_MODIFIER := L_MODIFIER || '~' || L_MOD_NAME;
              EXCEPTION
              WHEN OTHERS
              THEN
              NULL;
              END;



              IF x_line_detail_tbl (I).list_line_type_code = 'PLL'
              AND x_line_detail_tbl (I).adjustment_amount IS NOT NULL
              THEN
              L_ADJUSTMENT_AMOUNT :=
              L_ADJUSTMENT_AMOUNT
              || '~'
              || x_line_detail_tbl (I).adjustment_amount;
              END IF;

              I := x_line_detail_tbl.NEXT (I);
              END LOOP;
              END IF;

              SELECT line_unit_price, order_uom_selling_price
              INTO l_unit_price, l_selling_price
              FROM qp_preq_lines_tmp
              WHERE pricing_status_code = Qp_Preq_Pub.g_status_updated
              ORDER BY line_id;

              DBMS_OUTPUT.PUT_LINE ('Unit Price := ' || l_unit_price); --x_line_tbl(1).UNIT_PRICE );
              DBMS_OUTPUT.PUT_LINE ('Selling Price := ' || l_selling_price); ---x_line_tbl(1).LINE_UNIT_PRICE );
              DBMS_OUTPUT.PUT_LINE ('L_MODIFIER: ' || L_MODIFIER);
              DBMS_OUTPUT.PUT_LINE ('L_ADJUSTMENT_AMOUNT: ' || L_ADJUSTMENT_AMOUNT);
              DBMS_OUTPUT.
              PUT_LINE (
              '--------------Related Lines Information for Price Breaks/Service Items---------------');

              I := x_related_lines_tbl.FIRST;

              IF I IS NOT NULL
              THEN
              LOOP
              DBMS_OUTPUT.
              PUT_LINE ('Line Index :' || x_related_lines_tbl (I).line_index);
              DBMS_OUTPUT.
              PUT_LINE (
              'Line Detail Index: '
              || x_related_lines_tbl (I).LINE_DETAIL_INDEX);
              DBMS_OUTPUT.
              PUT_LINE (
              'Relationship Type Code: '
              || x_related_lines_tbl (I).relationship_type_code);
              DBMS_OUTPUT.
              PUT_LINE (
              'Related Line Index: '
              || x_related_lines_tbl (I).RELATED_LINE_INDEX);
              DBMS_OUTPUT.
              PUT_LINE (
              'Related Line Detail Index: '
              || x_related_lines_tbl (I).related_line_detail_index);
              DBMS_OUTPUT.
              PUT_LINE ('Status Code: ' || x_related_lines_tbl (I).STATUS_CODE);
              EXIT WHEN I = x_related_lines_tbl.LAST;
              I := x_related_lines_tbl.NEXT (I);
              END LOOP;
              END IF;
              EXCEPTION
              WHEN OTHERS THEN
              DBMS_OUTPUT.
              PUT_LINE ('SQLERRM' || SQLERRM);
              END;


              Kindly provide sugession if I miss any thing.


              Regards,
              Akil

              Edited by: 1004537 on May 9, 2013 7:20 AM

              Edited by: 1004537 on May 9, 2013 7:20 AM
              • 4. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
                Activesometimes-Oracle
                You hijacked another's thread ;)

                p_control_rec.pricing_event := 'LINE'
                Give it as 'BATCH'.
                Normally Batch processing event will be attached all pricing phases. Modifiers will be attached to Pricing phases like 'List Line Adjustment','All Lines Adjustment'..
                So Batch processing will apply all eligible modifiers in all pricing phases.
                • 5. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
                  Akil Pariyani
                  ActiveSomeTimes wrote:
                  You hijacked another's thread ;)

                  p_control_rec.pricing_event := 'LINE'
                  Give it as 'BATCH'.
                  Normally Batch processing event will be attached all pricing phases. Modifiers will be attached to Pricing phases like 'List Line Adjustment','All Lines Adjustment'..
                  So Batch processing will apply all eligible modifiers in all pricing phases.
                  Thanx for your reply sir,

                  I tried with 'BATCH' also but didn't get different price,


                  I am fresher and don't have much more information regarding this, can u please send me working code if you have, or any setup that I suppose to check before implement it.

                  You can send it on akil.pariyani@gmail.com

                  Regards,
                  Akil
                  • 6. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
                    user7751421
                    Try using this as I stated -


                    Answer is

                    Pass ine_attr_rec.VALIDATED_FLAG :='Y' as 'N' instead of 'Y' which will consider the qualifiers on the price list.

                    Pass all VALIDATED_FLAG as 'N'
                    • 7. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
                      Akil Pariyani
                      user7751421 wrote:
                      Try using this as I stated -


                      Answer is

                      Pass ine_attr_rec.VALIDATED_FLAG :='Y' as 'N' instead of 'Y' which will consider the qualifiers on the price list.

                      Pass all VALIDATED_FLAG as 'N'
                      Thanx for your replay

                      I try with all line_attr_rec.VALIDATED_FLAG := 'N' and qual_rec.VALIDATED_FLAG := 'Y';

                      In toad I found Following error, I Pest few line of output

                      File : /usr/tmp/l0017087.dbg
                      Stratergic Product linep NOT Found --Error not occured with our other client is there any setup issue?
                      Return Status text Routine: QP_PREQ_PUB.PRICE_REQUEST SUCCESS
                      Return Status S
                      ---------Information Returned to Caller---------------------
                      -------------Request Line Information-------------------
                      Line Index: 1
                      Unit_price: 44.71
                      Percent price:
                      Adjusted Unit Price: 44.71
                      Pricing status code: UPDATED
                      Pricing status text:
                      -----------Pricing Attributes Information-------------
                      Line detail Index 2
                      Context ITEM
                      Attribute PRICING_ATTRIBUTE1
                      Value 20485
                      Status Code
                      ---------------------------------------------------

                      If possible can you please send me any use full document or working code, If possibe u can send it on akil.pariyani@gmail.com

                      THANX and Regards,
                      Akil

                      Edited by: Akil Pariyani on Jun 7, 2013 12:13 AM
                      • 8. Re: Price from Order Line and Price from QP_PREQ_PUB.PRICE_REQUEST is different
                        user7751421
                        CREATE OR REPLACE PACKAGE BODY APPS.xx_price_book_pkg_2 AS

                        FUNCTION get_unit_price (p_item_id IN NUMBER, p_price_list IN VARCHAR2,P_ORG_CODE IN VARCHAR2)
                        RETURN NUMBER IS
                        p_line_tbl qp_preq_grp.line_tbl_type;
                        p_qual_tbl qp_preq_grp.qual_tbl_type;
                        p_line_attr_tbl qp_preq_grp.line_attr_tbl_type;
                        p_line_detail_tbl qp_preq_grp.line_detail_tbl_type;
                        p_line_detail_qual_tbl qp_preq_grp.line_detail_qual_tbl_type;
                        p_line_detail_attr_tbl qp_preq_grp.line_detail_attr_tbl_type;
                        p_related_lines_tbl qp_preq_grp.related_lines_tbl_type;
                        p_control_rec qp_preq_grp.control_record_type;
                        x_line_tbl qp_preq_grp.line_tbl_type;
                        x_line_qual qp_preq_grp.qual_tbl_type;
                        x_line_attr_tbl qp_preq_grp.line_attr_tbl_type;
                        x_line_detail_tbl qp_preq_grp.line_detail_tbl_type;
                        x_line_detail_qual_tbl qp_preq_grp.line_detail_qual_tbl_type;
                        x_line_detail_attr_tbl qp_preq_grp.line_detail_attr_tbl_type;
                        x_related_lines_tbl qp_preq_grp.related_lines_tbl_type;
                        x_return_status VARCHAR2 (240);
                        x_return_status_text VARCHAR2 (240);
                        qual_rec qp_preq_grp.qual_rec_type;
                        line_attr_rec qp_preq_grp.line_attr_rec_type;
                        line_rec qp_preq_grp.line_rec_type;
                        rltd_rec qp_preq_grp.related_lines_rec_type;
                        g_sysdate DATE := SYSDATE;
                        l_list_name qp_list_headers_all.name%TYPE;
                        l_list_dtl_name qp_list_headers_all.name%TYPE;
                        i BINARY_INTEGER;
                        l_version VARCHAR2 (240);
                        p_price_list_id NUMBER;
                        l_list_price NUMBER := 0;
                        l_selling_price NUMBER := 0;
                        l_charges NUMBER := 0;
                        l_shipping_charges NUMBER := 0;
                        l_modifier VARCHAR2 (4000);
                        l_adjustment_amount VARCHAR2 (300) := 0;
                        l_mod_name VARCHAR2 (240);
                        l_currency VARCHAR2 (10);
                        l_attribute1 VARCHAR2 (240);
                        l_uom_code VARCHAR2 (30);
                        L_CATEGORY_ID NUMBER;
                        L_ORGANIZATION_ID NUMBER;

                        TYPE PRICE_TMP IS TABLE OF xx.xx_ITEM_PRICE_TMP%ROWTYPE INDEX BY BINARY_INTEGER;

                        L_PRICE_TMP PRICE_TMP;

                        BEGIN
                        BEGIN
                        SELECT list_header_id, currency_code
                        INTO p_price_list_id, l_currency
                        FROM qp_list_headers_all qh
                        WHERE name = p_price_list;
                        EXCEPTION
                        WHEN OTHERS THEN
                        p_price_list_id := '';
                        l_currency := 'USD';
                        END;

                        BEGIN
                        SELECT primary_uom_code, NVL (msi.attribute1,'X'),msi.ORGANIZATION_ID
                        INTO l_uom_code, l_attribute1, L_ORGANIZATION_ID
                        FROM mtl_system_items_b msi, mtl_parameters mp
                        WHERE msi.inventory_item_id = p_item_id
                        AND msi.organization_id = mp.organization_id
                        AND mp.organization_code = p_org_code ;
                        EXCEPTION
                        WHEN OTHERS THEN
                        l_uom_code := 'EA';
                        l_attribute1 := 'X';
                        L_ORGANIZATION_ID := 90;
                        END;

                        BEGIN
                        SELECT CATEGORY_ID
                        INTO L_CATEGORY_ID
                        FROM MTL_ITEM_CATEGORIES_V MIC
                        WHERE CATEGORY_SET_NAME = 'Primary Item Category'
                        AND INVENTORY_ITEM_ID=p_item_id
                        AND ORGANIZATION_ID = L_ORGANIZATION_ID;
                        EXCEPTION
                        WHEN OTHERS THEN
                        L_CATEGORY_ID := 0;
                        END;

                        ---- Control Record
                        p_control_rec.pricing_event := 'BATCH';
                        p_control_rec.calculate_flag := 'Y';
                        p_control_rec.simulation_flag := 'N';
                        p_control_rec.source_order_amount_flag := 'Y';
                        p_control_rec.public_api_call_flag := 'N';
                        p_control_rec.rounding_flag := 'Q';
                        p_control_rec.manual_discount_flag := 'N';
                        p_control_rec.manual_adjustments_call_flag := 'N';
                        p_control_rec.request_type_code := 'ONT';
                        p_control_rec.temp_table_insert_flag := 'Y';

                        line_rec.request_type_code := 'ONT';
                        line_rec.line_id := 22293; -- Order Line Id. This can be any thing for this script
                        line_rec.line_index := '1';
                        line_rec.line_type_code := 'LINE';
                        line_rec.pricing_effective_date := g_sysdate;
                        line_rec.active_date_first := g_sysdate;
                        line_rec.active_date_second := g_sysdate;
                        line_rec.active_date_first_type := 'NO TYPE';
                        line_rec.active_date_second_type := 'NO TYPE';
                        line_rec.line_quantity := 1;
                        line_rec.line_uom_code := l_uom_code;
                        line_rec.currency_code := l_currency;
                        line_rec.price_flag := 'Y';
                        p_line_tbl (1) := line_rec;

                        line_attr_rec.line_index := 1;
                        line_attr_rec.pricing_context := 'ITEM'; --
                        line_attr_rec.pricing_attribute := 'PRICING_ATTRIBUTE3';
                        line_attr_rec.pricing_attr_value_from := 'ALL';
                        line_attr_rec.validated_flag := 'N';
                        p_line_attr_tbl (1) := line_attr_rec;

                        line_attr_rec.line_index := 1;
                        line_attr_rec.pricing_context := 'ITEM';
                        line_attr_rec.pricing_attribute := 'PRICING_ATTRIBUTE1';
                        line_attr_rec.pricing_attr_value_from := TO_CHAR (p_item_id);
                        line_attr_rec.validated_flag := 'N';
                        p_line_attr_tbl (2) := line_attr_rec;

                        line_attr_rec.line_index := 1;
                        line_attr_rec.pricing_context := 'ITEM';
                        line_attr_rec.pricing_attribute := 'PRICING_ATTRIBUTE2';
                        line_attr_rec.pricing_attr_value_from := TO_CHAR (L_CATEGORY_ID);
                        line_attr_rec.validated_flag := 'N';
                        p_line_attr_tbl (3) := line_attr_rec;

                        qual_rec.line_index := 1;
                        qual_rec.qualifier_context := 'MODLIST';
                        qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE4';
                        qual_rec.qualifier_attr_value_from := TO_CHAR (p_price_list_id);
                        qual_rec.comparison_operator_code := '=';
                        qual_rec.validated_flag := 'N';
                        p_qual_tbl (1) := qual_rec;

                        qual_rec.line_index := 1;
                        qual_rec.qualifier_context := 'ITEM';
                        qual_rec.qualifier_attribute := 'QUALIFIER_ATTRIBUTE31';
                        qual_rec.qualifier_attr_value_from := l_attribute1;
                        qual_rec.comparison_operator_code := '=';
                        qual_rec.validated_flag := 'N';
                        p_qual_tbl (2) := qual_rec;


                        l_version := qp_preq_grp.get_version;

                        BEGIN
                        qp_preq_pub.price_request (p_line_tbl
                        , p_qual_tbl
                        , p_line_attr_tbl
                        , p_line_detail_tbl
                        , p_line_detail_qual_tbl
                        , p_line_detail_attr_tbl
                        , p_related_lines_tbl
                        , p_control_rec
                        , x_line_tbl
                        , x_line_qual
                        , x_line_attr_tbl
                        , x_line_detail_tbl
                        , x_line_detail_qual_tbl
                        , x_line_detail_attr_tbl
                        , x_related_lines_tbl
                        , x_return_status
                        , x_return_status_text);
                        EXCEPTION
                        WHEN OTHERS THEN
                        NULL;
                        END;

                        IF x_return_status = 'E' THEN
                        l_list_price := 0;
                        l_selling_price := 0;
                        ELSE
                        i := x_line_tbl.FIRST;

                        IF i IS NOT NULL THEN
                        LOOP
                        l_list_price := l_list_price + NVL (x_line_tbl (i).unit_price, 0);
                        l_selling_price := l_selling_price + NVL (x_line_tbl (i).adjusted_unit_price, 0);
                        EXIT WHEN i = x_line_tbl.LAST;
                        i := x_line_tbl.NEXT (i);
                        END LOOP;
                        END IF;



                        IF x_line_tbl (i).header_id IS NOT NULL THEN
                        BEGIN
                        SELECT name
                        INTO l_list_name
                        FROM qp_list_headers_all
                        WHERE list_header_id = x_line_tbl (i).header_id;
                        EXCEPTION
                        WHEN OTHERS THEN
                        l_list_name := '';
                        END;
                        END IF;

                        i := x_line_detail_tbl.FIRST;



                        IF i IS NOT NULL THEN
                        LOOP
                        l_mod_name := '';
                        l_list_dtl_name := '';

                        IF x_line_detail_tbl (i).list_header_id IS NOT NULL THEN
                        BEGIN
                        SELECT name
                        INTO l_list_dtl_name
                        FROM qp_list_headers_all
                        WHERE list_header_id = x_line_detail_tbl(I).list_header_id
                        AND ACTIVE_FLAG = 'Y'
                        AND NVL(START_DATE_ACTIVE,SYSDATE)<=SYSDATE
                        AND NVL(END_DATE_ACTIVE,SYSDATE)>=SYSDATE;
                        EXCEPTION
                        WHEN OTHERS THEN
                        l_list_dtl_name := '';
                        END;
                        END IF;


                        IF x_line_detail_tbl (i).CREATED_FROM_LIST_TYPE_CODE = 'CHARGES' AND l_list_dtl_name <> 'SHIPPING CHARGE' THEN
                        l_charges := l_charges + NVL (x_line_detail_tbl (i).adjustment_amount, 0);
                        END IF;

                        IF x_line_detail_tbl (i).CREATED_FROM_LIST_TYPE_CODE = 'CHARGES' AND l_list_dtl_name = 'SHIPPING CHARGE' THEN
                        l_shipping_charges := l_shipping_charges + NVL (x_line_detail_tbl (i).adjustment_amount, 0);
                        END IF;

                        BEGIN
                        SELECT name
                        INTO l_mod_name
                        FROM qp_list_headers_all
                        WHERE list_header_id = x_line_detail_tbl (i).list_header_id AND x_line_detail_tbl (i).list_line_type_code <> 'PLL' AND x_line_detail_tbl (i).adjustment_amount IS NOT NULL;

                        l_modifier := l_modifier || '~' || l_mod_name;
                        EXCEPTION
                        WHEN OTHERS THEN
                        NULL;
                        END;



                        IF x_line_detail_tbl (i).list_line_type_code <> 'PLL' AND x_line_detail_tbl (i).adjustment_amount IS NOT NULL THEN
                        l_adjustment_amount := l_adjustment_amount + NVL (x_line_detail_tbl (i).adjustment_amount, 0);
                        END IF;

                        EXIT WHEN i = x_line_detail_tbl.LAST;
                        i := x_line_detail_tbl.NEXT (i);
                        END LOOP;
                        END IF;
                        END IF;

                        L_PRICE_TMP(1).NAME :='LIST_NAME';
                        L_PRICE_TMP(1).VALUE := l_list_name;
                        L_PRICE_TMP(2).NAME :='SELLING_PRICE';
                        L_PRICE_TMP(2).VALUE := l_selling_price;
                        L_PRICE_TMP(3).NAME :='LIST_PRICE';
                        L_PRICE_TMP(3).VALUE := l_list_price;
                        L_PRICE_TMP(4).NAME :='CHARGES';
                        L_PRICE_TMP(4).VALUE := l_charges;
                        L_PRICE_TMP(5).NAME :='SHIPPING_CHARGE';
                        L_PRICE_TMP(5).VALUE := l_shipping_charges;
                        L_PRICE_TMP(6).NAME :='MODIFIER';
                        L_PRICE_TMP(6).VALUE := l_modifier;


                        FOR J IN 1..L_PRICE_TMP.COUNT
                        LOOP
                        INSERT INTO xx.xx_ITEM_PRICE_TMP VALUES(L_PRICE_TMP(J).NAME,L_PRICE_TMP(J).VALUE,P_ORG_CODE);
                        END LOOP;
                        L_PRICE_TMP.DELETE;
                        RETURN 1;
                        EXCEPTION
                        WHEN OTHERS THEN
                        RETURN 0;
                        END get_unit_price;
                        END xx_price_book_pkg_2;
                        /