eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.QP_FORMULA_PRICE_CALC_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 34


    SELECT line_index, attribute_type, context, attribute, value_from value
    FROM   qp_int_line_attrs_t lattr
    WHERE  request_id = p_request_id
    AND    line_index = p_line_index
    AND    attribute_type IN ('PRICING','PRODUCT')
    AND    pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;

Line: 44


    SELECT line_index, attribute_type, context, attribute, value_from value
    FROM   qp_int_line_attrs_t lattr
    WHERE  request_id = p_request_id
    AND    line_index = p_line_index
    AND    attribute_type IN ('PRICING','PRODUCT','QUALIFIER')
    AND    pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;

Line: 86


      EXECUTE IMMEDIATE 'SELECT ' || p_formula || ' FROM DUAL';

Line: 129


FUNCTION Select_From_Dual(p_formula       IN VARCHAR2,
                          p_operand_tbl   IN QP_FORMULA_RULES_PVT.T_OPERAND_TBL_TYPE)
RETURN NUMBER
IS
  l_char              VARCHAR2(1) := '';

Line: 136


  l_select_stmt       VARCHAR2(20000) := '';

Line: 180


  l_select_stmt := 'SELECT '|| l_new_formula || ' FROM DUAL';

Line: 181


  EXECUTE IMMEDIATE l_select_stmt INTO l_formula_value;

Line: 183


END Select_From_Dual;

Line: 212


  SELECT step_number, price_formula_line_type_code, numeric_constant,
         pricing_attribute, pricing_attribute_context,
         price_formula_id, price_list_line_id
  FROM   qp_price_formula_lines
  WHERE  price_formula_id = a_price_formula_id;

Line: 243


  SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N8) index(t qp_preq_line_attrs_frml_tmp_n1) */ -- 9362867
         a.list_header_id, l.list_line_id, l.operand,
         l.start_date_active, l.end_date_active, l.group_count,
         fl.price_formula_id, fl.step_number
  FROM   qp_price_formula_lines fl, qp_factor_list_attrs fla,
         qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a,
         qp_list_lines l
  WHERE  t.context = a.pricing_attribute_context
  AND    t.attribute = a.pricing_attribute
  AND    fl.price_formula_line_type_code = 'ML'
  AND    t.line_index = a_line_index
  AND    t.attribute_type in ('PRICING','PRODUCT')
  AND    fl.price_formula_id = a_price_formula_id
  AND    t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
  AND    fla.list_header_id = fl.price_modifier_list_id
  AND    fla.pricing_attribute_context = t.context
  AND    fla.pricing_attribute =  t.attribute
  AND    a.list_header_id = fla.list_header_id
  AND    a.list_line_id = l.list_line_id
  AND    a.search_ind = 1
  AND    t.value_from between
            a.pattern_value_from_positive and a.pattern_value_to_positive
  AND    a_price_effective_date between                            --3520634 start
         nvl(l.start_date_active,a_price_effective_date) and
         nvl(l.end_date_active,a_price_effective_date)
  UNION  --separate sqls for positive and negative pattern_values for 3520634
  SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N10) index(t qp_preq_line_attrs_frml_tmp_n1) */ -- 9362867
         a.list_header_id, l.list_line_id, l.operand,
         l.start_date_active, l.end_date_active, l.group_count,
         fl.price_formula_id, fl.step_number
  FROM   qp_price_formula_lines fl, qp_factor_list_attrs fla,
         qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a,
         qp_list_lines l
  WHERE  t.context = a.pricing_attribute_context
  AND    t.attribute = a.pricing_attribute
  AND    fl.price_formula_line_type_code = 'ML'
  AND    t.line_index = a_line_index
  AND    t.attribute_type in ('PRICING','PRODUCT')
  AND    fl.price_formula_id = a_price_formula_id
  AND    t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
  AND    fla.list_header_id = fl.price_modifier_list_id
  AND    fla.pricing_attribute_context = t.context
  AND    fla.pricing_attribute =  t.attribute
  AND    a.list_header_id = fla.list_header_id
  AND    a.list_line_id = l.list_line_id
  AND    a.search_ind = 1
  AND    t.value_from between
            a.pattern_value_from_negative and a.pattern_value_to_negative
  AND    a_price_effective_date between
         nvl(l.start_date_active,a_price_effective_date) and
         nvl(l.end_date_active,a_price_effective_date)
  ORDER BY 8;  --3520634 end

Line: 322


  SELECT /*+  index(t QP_PREQ_LINE_ATTRS_FRML_TMP_N1) index(A QP_PRICING_ATTRIBUTES_N2) */ --Bug 7452538 Added index hints
     a.list_line_id     --Bug 8359591 Removing ordered hint
   -- /*+ ordered */ a.list_line_id     --5900728
  FROM   qp_factor_list_attrs fla,
         qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a
  WHERE  fla.list_header_id = a_list_header_id
  AND    fla.pricing_attribute_context = t.context
  AND    fla.pricing_attribute = t.attribute
  AND    t.context = a.pricing_attribute_context
  AND    t.attribute = a.pricing_attribute
  AND    t.line_index = a_line_index
  AND    t.attribute_type in ('PRICING','PRODUCT')
  AND    t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
  AND    a.list_header_id = fla.list_header_id
  AND    a.list_line_id = a_list_line_id
  AND    a.search_ind = 2
   AND(t.value_from BETWEEN a.pattern_value_from_positive
   AND a.pattern_value_to_positive OR t.value_from BETWEEN a.pattern_value_from_negative
   AND a.pattern_value_to_negative)
  GROUP BY a.list_line_id
  HAVING   count(*) = a_group_count;

Line: 390


  SELECT formula
  FROM   qp_price_formulas_b
  WHERE  price_formula_id = a_price_formula_id
  AND    (start_date_active IS NULL OR
		start_date_active <= a_price_effective_date)
  AND    (end_date_active IS NULL   OR
		end_date_active >= a_price_effective_date);

Line: 411


  SELECT value_from
  FROM   qp_preq_line_attrs_formula_tmp
  WHERE  context = a_pricing_attribute_context
  AND    attribute = a_pricing_attribute
  AND    line_index = a_line_index
  AND    attribute_type in ('PRICING','PRODUCT')
  AND    pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;

Line: 471


  SELECT line_index, attribute_type, context, attribute, value_from value
  --FROM   qp_npreq_line_attrs_tmp
  -- bug2425851
  FROM   qp_preq_line_attrs_formula_tmp
  WHERE  line_index = a_line_index
  AND    attribute_type IN ('PRICING','PRODUCT');

Line: 481


  SELECT line_index, attribute_type, context, attribute, value_from value
 FROM   qp_preq_line_attrs_formula_tmp
  WHERE  line_index = a_line_index
  AND    attribute_type IN ('PRICING','PRODUCT','QUALIFIER');

Line: 515


   SELECT name
     INTO l_formula_name
     FROM qp_price_formulas_tl
    WHERE price_formula_id = p_price_formula_id
    AND   language = userenv('LANG')	   --bug#13250347
     AND rownum<2;

Line: 551


  SELECT count(*)
  INTO   l_no_of_comps
  FROM   qp_price_formula_lines
  WHERE  price_formula_id = p_price_formula_id;

Line: 635


      l_null_step_number_tbl.DELETE(l_factors_rec.step_number);

Line: 681


        l_null_step_number_tbl.DELETE(l_factors_rec.step_number);

Line: 930


        SELECT operand
        INTO   l_formula_line_tbl(l_rec.step_number).component_value
        FROM   qp_list_lines
        WHERE  list_line_id = l_rec.price_list_line_id;

Line: 985


        l_req_line_attrs_tbl.delete; --3531890 attribute were getting accumulated for every step

Line: 988


IF QP_PREQ_GRP.G_INSERT_FORMULA_STEP_VALUES = 'Y' THEN
	IF l_debug = FND_API.G_TRUE THEN
	QP_PREQ_GRP.engine_debug('Before populating formula stepvalues temp table');

Line: 1001


          INSERT INTO qp_nformula_step_values_tmp
          (price_formula_id,
           step_number,
           component_value,
           price_formula_line_type_code,
           line_index,
           list_line_type_code,
           list_header_id,
           list_line_id
          )
          VALUES
          (l_formula_line_tbl(l_rec.step_number).price_formula_id,
           l_formula_line_tbl(l_rec.step_number).step_number,
           l_formula_line_tbl(l_rec.step_number).component_value,
           l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code,
           l_formula_line_tbl(l_rec.step_number).line_index,
           l_formula_line_tbl(l_rec.step_number).list_line_type_code,
           l_formula_line_tbl(l_rec.step_number).list_header_id,
           l_formula_line_tbl(l_rec.step_number).list_line_id
          );

Line: 1089


      l_null_step_number_tbl.DELETE(l_factors_rec.step_number);

Line: 1130


        l_null_step_number_tbl.DELETE(l_factors_rec.step_number);

Line: 1155


/* commented for bug 7188211 - temp table insert moved above
  IF QP_PREQ_GRP.G_INSERT_FORMULA_STEP_VALUES = 'Y' THEN

    IF l_debug = FND_API.G_TRUE THEN
    QP_PREQ_GRP.engine_debug('Before populating formula stepvalues temp table');

Line: 1172


          INSERT INTO qp_nformula_step_values_tmp
          (price_formula_id,
           step_number,
           component_value,
           price_formula_line_type_code,
           line_index,
           list_line_type_code,
           list_header_id,
           list_line_id
          )
          VALUES
          (l_formula_line_tbl(i).price_formula_id,
           l_formula_line_tbl(i).step_number,
           l_formula_line_tbl(i).component_value,
           l_formula_line_tbl(i).price_formula_line_type_code,
           l_formula_line_tbl(i).line_index,
           l_formula_line_tbl(i).list_line_type_code,
           l_formula_line_tbl(i).list_header_id,
           l_formula_line_tbl(i).list_line_id
          );

Line: 1226


      SELECT SEEDED_VALUE INTO l_pkg_ver FROM qp_parameters_b WHERE PARAMETER_CODE='BLD_FORMULA_ACTV_VER' AND ROWNUM=1;

Line: 1284


    l_formula_value := Select_From_Dual(l_formula, l_operand_tbl);

Line: 1293


        l_null_step_number_tbl.DELETE;

Line: 1305


  l_formula_line_tbl.DELETE; --Clear the temp table table

Line: 1373


      l_formula_line_tbl.DELETE; --Clear the temp table table

Line: 1389


      l_formula_line_tbl.DELETE; --Clear the temp table table

Line: 1412


      l_formula_line_tbl.DELETE; --Clear the temp table table

Line: 1447


   SELECT formula into l_formula from qp_price_formulas_b
   WHERE price_formula_id = p_price_formula_id;

Line: 1450


  SELECT count(*) into l_no_of_comps from qp_price_formula_lines
   WHERE price_formula_id = p_price_formula_id;

Line: 1477


     SELECT price_formula_line_type_code,
            pricing_attribute_context,
            pricing_attribute
       INTO l_price_formula_line_type_code,
            l_pricing_attribute_context,
            l_pricing_attribute
       FROM qp_price_formula_lines
      WHERE price_formula_id = p_price_formula_id
        AND step_number = l_index;

Line: 1501


         SELECT     nvl(SEGMENTS_TL.SEEDED_SEGMENT_NAME,SEGMENTS_TL.USER_SEGMENT_NAME)
         INTO  l_attribute
 	 FROM  qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS , qp_segments_tl SEGMENTS_TL
		WHERE pcontexts.prc_context_code       =  l_pricing_attribute_context
		AND   segments.segment_mapping_column  =  l_pricing_attribute
		AND   segments.prc_context_id          =  pcontexts.prc_context_id
		AND   segments.segment_id              =  segments_tl.segment_id
		AND   segments_tl.language	       = userenv('LANG') --bug#13250347
                AND   rownum<2;

Line: 1583


    x_formula_value := Select_From_Dual(p_formula, l_operand_tbl);