DBA Data[Home] [Help]

APPS.QP_MULTI_CURRENCY_PVT SQL Statements

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

Line: 12

    l_insert_into_tmp		IN VARCHAR2
   ,l_price_formula_id		IN NUMBER
   ,l_operand_value		IN NUMBER
   ,l_pricing_effective_date    IN DATE
   ,l_line_index		IN NUMBER
   ,l_modifier_value		IN NUMBER
   ,l_formula_based_value       OUT NOCOPY NUMBER
   ,l_return_status	        OUT NOCOPY VARCHAR2
)
IS

l_status 		VARCHAR2(1);
Line: 27

	SELECT line_index
		, attribute_type
		, context
		, attribute
		, pricing_status_code
		, qp_number.canonical_to_number(value_from)
	FROM qp_npreq_line_attrs_tmp lattr
	WHERE attribute_type IN ('PRICING', 'PRODUCT')
		AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
		AND EXISTS (
			SELECT format_type
			FROM fnd_flex_value_sets VSET,
	                    qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS
        		WHERE vset.flex_value_set_id = segments.user_valueset_id
				AND segments.application_id = 661
               			AND pcontexts.prc_context_type <> 'QUALIFIER'
               			AND pcontexts.prc_context_code = lattr.context
               			AND segments.segment_mapping_column = lattr.attribute
   			        AND segments.prc_context_id = pcontexts.prc_context_id
              			AND vset.format_type = 'N'
	);
Line: 50

	SELECT line_index
		,attribute_type
		,context
		,attribute
		,pricing_status_code
		,qp_number.canonical_to_number(value_from)
	FROM qp_npreq_line_attrs_tmp 		lattr
	WHERE attribute_type IN ('PRICING', 'PRODUCT')
		AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
		AND EXISTS(
			SELECT format_type
			FROM   fnd_flex_value_sets vset
				,fnd_descr_flex_column_usages	dflex
			WHERE  vset.flex_value_set_id = dflex.flex_value_set_id
				AND dflex.application_id = 661
				AND dflex.descriptive_flexfield_name = 'QP_ATTR_DEFNS_PRICING'
				AND dflex.descriptive_flex_context_code = lattr.context
				AND dflex.application_column_name = lattr.attribute
				AND    vset.format_type = 'N'
		);
Line: 89

  qp_preq_grp.engine_debug('QP_MULTI_CURRENCY_PVT.Process_Formula_API - l_insert_into_tmp = '
                                  || l_insert_into_tmp);
Line: 112

  IF l_insert_into_tmp = 'Y' THEN

    -- Change flexible mask to mask below for formula pattern use
    /*qp_number.canonical_mask :=
    '00999999999999999999999.99999999999999999999999999999999999999';*/ --Bug No: 8427852
Line: 118

     delete from qp_preq_line_attrs_formula_tmp;
Line: 126

	l_line_index_tbl.delete;
Line: 127

	l_attribute_type_tbl.delete;
Line: 128

	l_context_tbl.delete;
Line: 129

	l_attribute_tbl.delete;
Line: 130

	l_pricing_status_code_tbl.delete;
Line: 131

	l_value_from_tbl.delete;
Line: 143

		INSERT INTO qp_preq_line_attrs_formula_tmp
			(line_index, attribute_type, context,
			 attribute, pricing_status_code, value_from)
		VALUES
			( l_line_index_tbl(i), l_attribute_type_tbl(i), l_context_tbl(i),
			  l_attribute_tbl(i), l_pricing_status_code_tbl(i),
			  qp_number.number_to_canonical(l_value_from_tbl(i))
                );
Line: 158

/*    INSERT INTO
      qp_preq_line_attrs_formula_tmp
      (
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,value_from
      )
      SELECT
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,qp_number.number_to_canonical(to_number(value_from))
      FROM
        qp_npreq_line_attrs_tmp 		lattr
      WHERE
        attribute_type IN ('PRICING', 'PRODUCT')
      AND
        pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
      AND
        EXISTS(
               SELECT format_type
               FROM fnd_flex_value_sets VSET,
                    qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS
               WHERE vset.flex_value_set_id = segments.user_valueset_id
               AND segments.application_id = 661
               AND pcontexts.prc_context_type <> 'QUALIFIER'
               AND pcontexts.prc_context_code = lattr.context
               AND segments.segment_mapping_column = lattr.attribute
               AND segments.prc_context_id = pcontexts.prc_context_id
               AND vset.format_type = 'N'
              );*/
Line: 197

    INSERT INTO
      qp_preq_line_attrs_formula_tmp
      (
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,value_from
      )
      SELECT
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,value_from
      FROM
        qp_npreq_line_attrs_tmp 		lattr
      WHERE
        attribute_type IN ('PRICING', 'PRODUCT')
      AND
        pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
      AND
        NOT  EXISTS(
               SELECT format_type
               FROM fnd_flex_value_sets VSET,
                    qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS
               WHERE vset.flex_value_set_id = segments.user_valueset_id
               AND segments.application_id = 661
               AND pcontexts.prc_context_type <> 'QUALIFIER'
               AND pcontexts.prc_context_code = lattr.context
               AND segments.segment_mapping_column = lattr.attribute
               AND segments.prc_context_id = pcontexts.prc_context_id
               AND vset.format_type = 'N'
              );
Line: 239

    	l_line_index_tbl.delete;
Line: 240

	l_attribute_type_tbl.delete;
Line: 241

	l_context_tbl.delete;
Line: 242

	l_attribute_tbl.delete;
Line: 243

	l_pricing_status_code_tbl.delete;
Line: 244

	l_value_from_tbl.delete;
Line: 256

		INSERT INTO qp_preq_line_attrs_formula_tmp
			(line_index, attribute_type, context,
			 attribute, pricing_status_code, value_from)
		VALUES
			( l_line_index_tbl(i), l_attribute_type_tbl(i), l_context_tbl(i),
			  l_attribute_tbl(i), l_pricing_status_code_tbl(i),
			  qp_number.number_to_canonical(l_value_from_tbl(i))
                );
Line: 270

    /*INSERT INTO
      qp_preq_line_attrs_formula_tmp
      (
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,value_from
      )
      SELECT
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,qp_number.number_to_canonical(to_number(value_from))
      FROM
        qp_npreq_line_attrs_tmp 		lattr
      WHERE
        attribute_type IN ('PRICING', 'PRODUCT')
      AND
        pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
      AND
        EXISTS(
               SELECT format_type
               FROM   fnd_flex_value_sets 		vset
                     ,fnd_descr_flex_column_usages	dflex
               WHERE  vset.flex_value_set_id = dflex.flex_value_set_id
               AND    dflex.application_id = 661
               AND    dflex.descriptive_flexfield_name = 'QP_ATTR_DEFNS_PRICING'
               AND    dflex.descriptive_flex_context_code = lattr.context
               AND    dflex.application_column_name = lattr.attribute
               AND    vset.format_type = 'N'
              ) ;*/
Line: 312

    INSERT INTO
      qp_preq_line_attrs_formula_tmp
      (
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,value_from
      )
      SELECT
        line_index
       ,attribute_type
       ,context
       ,attribute
       ,pricing_status_code
       ,value_from
      FROM
        qp_npreq_line_attrs_tmp 		lattr
      WHERE
        attribute_type IN ('PRICING', 'PRODUCT')
      AND
        pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
      AND
        NOT  EXISTS(
               SELECT format_type
               FROM   fnd_flex_value_sets 		vset
                     ,fnd_descr_flex_column_usages	dflex
               WHERE  vset.flex_value_set_id = dflex.flex_value_set_id
               AND    dflex.application_id = 661
               AND    dflex.descriptive_flexfield_name = 'QP_ATTR_DEFNS_PRICING'
               AND    dflex.descriptive_flex_context_code = lattr.context
               AND    dflex.application_column_name = lattr.attribute
           --    AND    vset.format_type IN ('X','Y','C')
	       AND    vset.format_type = 'N'
              );
Line: 419

l_insert_into_tmp 	VARCHAR2(1);
Line: 487

  SELECT
    b.line_index
   ,b.line_detail_index
   ,b.operand_value
   ,b.operand_calculation_code
   ,b.base_currency_code
   ,b.pricing_effective_date
   ,a.currency_header_id
   ,a.to_currency_code
   ,a.fixed_value
   ,a.price_formula_id
   ,a.conversion_type
   ,a.conversion_date_type
   ,a.conversion_date
   ,a.rounding_factor
   ,a.markup_operator
   ,a.markup_value
   ,a.markup_formula_id
  FROM
    qp_currency_details a,
    qp_npreq_ldets_tmp   b
    --j_qp_npreq_ldets_tmp b
  WHERE
    	a.currency_header_id = b.currency_header_id
  AND	a.to_currency_code = b.order_currency
  AND   a.currency_detail_id = b.currency_detail_id
  AND   TRUNC(b.pricing_effective_date) >= TRUNC(nvl(a.start_date_active,b.pricing_effective_date))
  AND   TRUNC(b.pricing_effective_date) <= TRUNC(nvl(a.end_date_active, b.pricing_effective_date))
  AND   b.created_from_list_type_code IN ('PRL', 'AGR')
  AND   b.CREATED_FROM_LIST_LINE_TYPE <> 'PBH' ---7681676 PHB line does not have operand_value, so no rounding is required
  AND   b.operand_calculation_code IN ('UNIT_PRICE','BLOCK_PRICE','BREAKUNIT_PRICE')
  AND   b.pricing_status_code = 'N';
Line: 539

   l_insert_into_tmp := 'Y'; --Bug No. 8323485, performace reason, moved from loop to here
Line: 545

    line_index_t.DELETE;
Line: 546

    line_detail_index_t.DELETE;
Line: 547

    operand_value_t.DELETE;
Line: 548

    operand_calc_code_t.DELETE;
Line: 549

    base_currency_code_t.DELETE;
Line: 550

    pricing_effective_date_t.DELETE;
Line: 551

    currency_header_id_t.DELETE;
Line: 552

    to_currency_code_t.DELETE;
Line: 553

    fixed_value_t.DELETE;
Line: 554

    price_formula_id_t.DELETE;
Line: 555

    conversion_type_t.DELETE;
Line: 556

    conversion_date_type_t.DELETE;
Line: 557

    conversion_date_t.DELETE;
Line: 558

    rounding_factor_t.DELETE;
Line: 559

    markup_operator_t.DELETE;
Line: 560

    markup_value_t.DELETE;
Line: 561

    markup_formula_id_t.DELETE;
Line: 562

    result_operand_value_t.DELETE;
Line: 563

    error_message_t.DELETE;
Line: 564

    status_code_t.DELETE;
Line: 565

    lines_status_code_t.DELETE;
Line: 612

        lines_status_code_t(J) := 'UPDATED';
Line: 764

            l_insert_into_tmp
           ,price_formula_id_t(J)
           ,operand_value_t(J)
           ,pricing_effective_date_t(J)
           ,line_index_t(J)
           ,l_modifier_value
           ,l_formula_based_value
           ,l_formula_status
          );
Line: 797

          l_insert_into_tmp:= 'N';
Line: 998

             l_insert_into_tmp
            ,markup_formula_id_t(J)
            ,result_operand_value_t(J)
            ,pricing_effective_date_t(J)
            ,line_index_t(J)
            ,markup_value_t(J)
            ,l_formula_based_value
            ,l_formula_status
           );
Line: 1029

           l_insert_into_tmp := 'N';
Line: 1128

        UPDATE qp_npreq_ldets_tmp
        SET    operand_value       = result_operand_value_t(K)
              ,pricing_status_text = error_message_t(K)
              ,pricing_status_code = status_code_t(K)
        WHERE  line_index        = line_index_t(K)
        AND    line_detail_index = line_detail_index_t(K)
        AND    pricing_status_code = 'N';
Line: 1138

        UPDATE qp_npreq_lines_tmp
        SET unit_price = decode(operand_calc_code_t(K) , 'LIST_PRICE', result_operand_value_t(K),NULL),
            percent_price = decode(operand_calc_code_t(K) , 'PERCENT_PRICE', result_operand_value_t(K)
                                                          , NULL)
              ,pricing_status_text = error_message_t(K)
              ,pricing_status_code = lines_status_code_t(K)
        WHERE  line_index = line_index_t(K);