APPS.QP_DENORMALIZED_PRICING_ATTRS SQL Statements

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

Line: 7

PROCEDURE Update_Row_Count(p_list_header_id_low  IN NUMBER,
                           p_list_header_id_high IN NUMBER);

Line: 16

  p_update_type in varchar2
) is
  l_list_header_id_tbl     num_type;

Line: 26

      SELECT min(list_header_id), max(list_header_id)
      INTO   l_list_header_id_low, l_list_header_id_high
      FROM   qp_list_headers_b
      WHERE  list_type_code = 'PML';

Line: 51

  SELECT list_header_id
  BULK COLLECT INTO l_list_header_id_tbl
  FROM   qp_list_headers_b
  WHERE  list_type_code = 'PML'
  AND    list_header_id BETWEEN l_list_header_id_low AND l_list_header_id_high;

Line: 67

PROCEDURE UPDATE_SEARCH_IND(
  err_buff out nocopy  varchar2,
  retcode out  nocopy number,
  p_list_header_id_low in number,
  p_list_header_id_high in number,
  p_list_header_id_tbl in num_type,
  p_update_type in varchar2 default 'BATCH'
) IS
  l_pricing_attr_id_tbl    num_type;

Line: 86

  OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :UPDATE_SEARCH_IND');

Line: 88

  QP_Denormalized_Pricing_Attrs.Update_Row_Count(l_list_header_id_low, l_list_header_id_high);

Line: 96

      UPDATE qp_pricing_attributes
      SET    search_ind = null
      WHERE  list_header_id = l_list_header_id_tbl(k);

Line: 104

SELECT min(a.pricing_attribute_id), a.list_line_id
  BULK COLLECT INTO l_pricing_attr_id_tbl, l_list_line_id_tbl
  FROM   qp_pricing_attributes a
  WHERE  a.distinct_row_count = (SELECT min(b.distinct_row_count)
                                 FROM   qp_pricing_attributes b
                                 WHERE  b.list_line_id = a.list_line_id)
  AND    a.list_header_id IN (SELECT list_header_id
                              FROM   qp_list_headers_b
                              WHERE  list_type_code = 'PML'
                              AND    list_header_id BETWEEN l_list_header_id_low
                                     AND l_list_header_id_high)
  GROUP BY a.list_line_id;

Line: 120

      UPDATE qp_pricing_attributes
      SET    search_ind = 1
      WHERE  pricing_attribute_id = l_pricing_attr_id_tbl(i);

Line: 125

  l_pricing_attr_id_tbl.DELETE; --Clear the plsql table.

Line: 131

      UPDATE qp_pricing_attributes
      SET    search_ind = 2
      WHERE  (search_ind <> 1 or search_ind IS NULL)
      AND    list_line_id = l_list_line_id_tbl(j);

Line: 137

  l_list_line_id_tbl.DELETE; --Clear the plsql table.

Line: 144

      UPDATE qp_list_lines l
      SET    l.group_count = (select count(*)
                              from   qp_pricing_attributes a
                              where  a.list_line_id = l.list_line_id
                              and    a.search_ind = 2)
      WHERE  l.list_header_id = l_list_header_id_tbl(k);

Line: 152

  IF p_update_type IN ('ALL','FACTOR','BATCH') THEN
    commit;

Line: 161

END UPDATE_SEARCH_IND;

Line: 163

PROCEDURE UPDATE_SEARCH_IND(
  err_buff out nocopy  varchar2,
  retcode out  nocopy number,
  p_list_header_id_low in number,
  p_list_header_id_high in number,
  p_update_type in varchar2
) IS
  l_list_header_id_tbl    num_type;

Line: 176

OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :UPDATE_SEARCH_IND -called from Concurrent Program');

Line: 177

QP_Denormalized_Pricing_Attrs.PREPARE_INPUT_DATA(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);

Line: 179

QP_Denormalized_Pricing_Attrs.UPDATE_SEARCH_IND(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);

Line: 181

END UPDATE_SEARCH_IND;

Line: 184

PROCEDURE Update_Row_Count(p_list_header_id_low  IN NUMBER,
                           p_list_header_id_high IN NUMBER)
IS
BEGIN
  OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :Update_Row_Count');

Line: 191

  UPDATE qp_pricing_attributes a
  SET    a.distinct_row_count =
	   (SELECT /*+  INDEX(aa QP_PRICING_ATTRIBUTES_N6) */ count(*)
            FROM   qp_pricing_attributes aa
            WHERE  aa.pricing_attribute_context = a.pricing_attribute_context
            AND    aa.pricing_attribute = a.pricing_attribute
            AND    aa.pricing_attr_value_from = a.pricing_attr_value_from
            AND    nvl(aa.pricing_attr_value_to,'-x') =
                   nvl(a.pricing_attr_value_to,'-x')
            AND    aa.comparison_operator_code = a.comparison_operator_code
            AND    aa.list_header_id  = a.list_header_id
            AND    aa.pricing_attribute_context IS NOT NULL)
  WHERE a.list_header_id IN (SELECT list_header_id
                             FROM   qp_list_headers_b
                             WHERE  list_type_code = 'PML'
                             AND    list_header_id BETWEEN p_list_header_id_low
                                    AND p_list_header_id_high);

Line: 209

END Update_Row_Count;

Line: 226

      SELECT min(list_header_id), max(list_header_id)
      INTO   l_list_header_id_low, l_list_header_id_high
      FROM   qp_list_headers_b
      WHERE  list_type_code = 'PML';

Line: 251

  DELETE FROM qp_factor_list_attrs
  WHERE  list_header_id BETWEEN l_list_header_id_low AND l_list_header_id_high;

Line: 255

  INSERT INTO qp_factor_list_attrs
   (SELECT DISTINCT a.list_header_id,
           a.pricing_attribute_context, a.pricing_attribute
    FROM   qp_pricing_attributes a, qp_list_headers_b b
    WHERE  a.list_header_id = b.list_header_id
    AND    b.list_type_code = 'PML'
    AND    b.list_header_id BETWEEN
           l_list_header_id_low AND l_list_header_id_high);

Line: 270

PROCEDURE Update_Pricing_Attributes(
                     p_list_header_id_low  IN NUMBER default null,
                     p_list_header_id_high IN NUMBER default null,
                     p_update_type         IN VARCHAR2 default 'BATCH')
IS

l_pricing_attr_id_tbl    num_type;

Line: 290

OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :Update_Pricing_Attributes');

Line: 293

QP_Denormalized_Pricing_Attrs.PREPARE_INPUT_DATA(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);

Line: 300

QP_Denormalized_Pricing_Attrs.UPDATE_SEARCH_IND(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);

Line: 316

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_negative =
           LEAST(qp_number.number_to_canonical(pricing_attr_value_from_number),
                 qp_number.number_to_canonical(pricing_attr_value_to_number)),
           pattern_value_to_negative =
           GREATEST(
                 qp_number.number_to_canonical(pricing_attr_value_from_number),
                 qp_number.number_to_canonical(pricing_attr_value_to_number)),
           pattern_value_from_positive = null,
           pattern_value_to_positive = null
      WHERE  comparison_operator_code = 'BETWEEN'
      AND    pricing_attribute_datatype = 'N'
      AND    list_header_id = l_list_header_id_tbl(k)
      AND    pricing_attr_value_from_number < 0
      AND    pricing_attr_value_to_number < 0;

Line: 335

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_positive =
           LEAST(qp_number.number_to_canonical(pricing_attr_value_from_number),
                 qp_number.number_to_canonical(pricing_attr_value_to_number)),
           pattern_value_to_positive =
           GREATEST(
                 qp_number.number_to_canonical(pricing_attr_value_from_number),
                 qp_number.number_to_canonical(pricing_attr_value_to_number)),
           pattern_value_from_negative = null,
           pattern_value_to_negative = null
      WHERE  comparison_operator_code = 'BETWEEN'
      AND    pricing_attribute_datatype = 'N'
      AND    list_header_id = l_list_header_id_tbl(k)
      AND    pricing_attr_value_from_number >= 0
      AND    pricing_attr_value_to_number >= 0;

Line: 354

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_negative =
                 '-' || LTRIM(qp_number.number_to_canonical(0)),
             pattern_value_to_negative =
                 qp_number.number_to_canonical(pricing_attr_value_from_number),
             pattern_value_from_positive = null,
             pattern_value_to_positive = null
      WHERE  comparison_operator_code = 'BETWEEN'
      AND    pricing_attribute_datatype = 'N'
      AND    list_header_id = l_list_header_id_tbl(k)
      AND    pricing_attr_value_from_number < 0
      AND    pricing_attr_value_to_number = 0;

Line: 370

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_negative =
                 '-' || LTRIM(qp_number.number_to_canonical(0)),
             pattern_value_to_negative =
                 qp_number.number_to_canonical(pricing_attr_value_from_number),
             pattern_value_from_positive = qp_number.number_to_canonical(0),
             pattern_value_to_positive =
                 qp_number.number_to_canonical(pricing_attr_value_to_number)
      WHERE  comparison_operator_code = 'BETWEEN'
      AND    pricing_attribute_datatype = 'N'
      AND    list_header_id = l_list_header_id_tbl(k)
      AND    pricing_attr_value_from_number < 0
      AND    pricing_attr_value_to_number > 0;

Line: 387

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_positive =
               qp_number.number_to_canonical(pricing_attr_value_from_number),
             pattern_value_to_positive =
               qp_number.number_to_canonical(pricing_attr_value_from_number),
             pattern_value_from_negative = null,
             pattern_value_to_negative = null
      WHERE  comparison_operator_code = '='
      AND    pricing_attribute_datatype = 'N'
      AND    list_header_id = l_list_header_id_tbl(k)
      AND    pricing_attr_value_from_number >= 0;

Line: 402

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_negative =
               qp_number.number_to_canonical(pricing_attr_value_from_number),
             pattern_value_to_negative =
               qp_number.number_to_canonical(pricing_attr_value_from_number),
             pattern_value_from_positive = null,
             pattern_value_to_positive = null
      WHERE  comparison_operator_code = '='
      AND    pricing_attribute_datatype = 'N'
      AND    list_header_id = l_list_header_id_tbl(k)
      AND    pricing_attr_value_from_number < 0;

Line: 416

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_positive =
                      LEAST(pricing_attr_value_from, pricing_attr_value_to),
             pattern_value_to_positive =
                      GREATEST(pricing_attr_value_from, pricing_attr_value_to),
             pattern_value_from_negative = null,
             pattern_value_to_negative = null
      WHERE  comparison_operator_code = 'BETWEEN'
      AND    pricing_attribute_datatype IN ('X','Y','C')
      AND    list_header_id = l_list_header_id_tbl(k);

Line: 429

      UPDATE qp_pricing_attributes
      SET    pattern_value_from_positive = pricing_attr_value_from,
             pattern_value_to_positive = pricing_attr_value_from,
             pattern_value_from_negative = null,
             pattern_value_to_negative = null
      WHERE  comparison_operator_code = '='
      AND    pricing_attribute_datatype IN ('X','Y','C')
      AND    list_header_id = l_list_header_id_tbl(k);

Line: 440

  IF p_update_type IN ('ALL','FACTOR','BATCH') THEN
    commit;

Line: 444

  l_list_header_id_tbl.DELETE; --Clear the plsql table.

Line: 457

END Update_Pricing_Attributes;