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_BULK_PREQ_GRP SQL Statements

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

Line: 13


Procedure Bulk_insert_lines(p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE,
			    p_line_rec IN OE_WSH_BULK_GRP.Line_Rec_Type,
                            p_org_id IN NUMBER DEFAULT NULL, --added for moac
			    x_return_status OUT NOCOPY VARCHAR2,

			    x_return_status_text OUT NOCOPY VARCHAR2) IS

l_qual_ctxts_result_tbl QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE;

Line: 71


    insert into qp_int_lines
    (
    LINE_INDEX,
    LINE_TYPE_CODE,
    PRICING_EFFECTIVE_DATE,
    START_DATE_ACTIVE_FIRST,
    ACTIVE_DATE_FIRST_TYPE,
    START_DATE_ACTIVE_SECOND,
    ACTIVE_DATE_SECOND_TYPE,
    LINE_QUANTITY,
    LINE_UOM_CODE,
    REQUEST_TYPE_CODE,
    PRICED_QUANTITY,
    PRICED_UOM_CODE,
    CURRENCY_CODE,
    UNIT_PRICE,
    ADJUSTED_UNIT_PRICE,
    PRICE_FLAG,
    UOM_QUANTITY,
    LINE_ID,
    HEADER_ID,
    ROUNDING_FACTOR,
    UPDATED_ADJUSTED_UNIT_PRICE,
    PRICE_LIST_HEADER_ID,
    PRICE_REQUEST_CODE,
    USAGE_PRICING_TYPE,
    LINE_CATEGORY,
    CONTRACT_START_DATE,
    CONTRACT_END_DATE,
    CATCHWEIGHT_QTY,
    ACTUAL_ORDER_QUANTITY,
    PRICING_STATUS_CODE,
    PRICING_EVENT

    )
    values
    (
    p_header_rec.header_index(i)+l_line_count,--p_header_rec.line_index(i),--Will OM populate this as well??
    'ORDER',
    p_header_rec.PRICING_DATE(i),
    p_header_rec.ORDERED_DATE(i),--p_line_rec.ACTIVE_DATE_FIRST(i)),
    'ORDER',--p_line_rec.ACTIVE_DATE_FIRST_TYPE(i),
    NULL,--p_line_rec.ACTIVE_DATE_SECOND(i)),
    NULL,--p_line_rec.ACTIVE_DATE_SECOND_TYPE(i),
    NULL,

    NULL,
    'ONT',--request_type_code hard-coded
    NULL,
    NULL,
    p_header_rec.transactional_curr_code(i),
    NULL,
    NULL,
    'Y',--this needs to change after OM adds calculate_price_flag to their rec structure--p_header_rec.CALCULATE_PRICE_FLAG(i),
    NULL,--service pricing not supported
    NULL,--line_id is null for summary line
    p_header_rec.HEADER_ID(i),

    NULL,--rounding_factor is null
    NULL,--updated_adjusted_unit_price w/b null for OM
    p_header_rec.PRICE_LIST_ID(i),
    NULL,--price_request_code not required as limits not supported
    QP_PREQ_GRP.G_REGULAR_USAGE_TYPE,--usage_pricing_type
    'ORDER',--line_category
    NULL,--CONTRACT_START_DATE
    NULL,--CONTRACT_END_DATE
    NULL,--CATCHWEIGHT_QTY
    NULL,--ACTUAL_ORDER_QUANTITY
    'X',
    p_header_rec.event_code(i)
    );

Line: 146


	select count(*) into l_preq_count
	from qp_int_lines ;

Line: 152


 G_line_index.delete;

Line: 154


  G_attr_type.delete;

Line: 155


  G_attr_context.delete;

Line: 156


  G_attr_attr.delete;

Line: 157


  G_attr_value.delete;

Line: 158


  G_validated_flag.delete;

Line: 212


insert into qp_int_line_attrs
                (line_index,
                 attribute_level,
                 attribute_type,
                 context,
                 attribute,
                 value_from,
                 validated_flag,
                 applied_flag,
                 pricing_status_code,
                 pricing_attr_flag
                )
   VALUES	(G_line_index(i)+l_line_count,
                 QP_PREQ_PUB.G_LINE_LEVEL,
		 G_attr_type(i),
                 G_attr_context(i),
                 G_attr_attr(i),
                 G_attr_value(i),
                 NVL (G_validated_flag(i), 'N'),
                 QP_PREQ_PUB.G_LIST_NOT_APPLIED,
                 QP_PREQ_PUB.G_STATUS_UNCHANGED,
                 QP_PREQ_PUB.G_YES
                );

Line: 244


			oe_debug_pub.add('Error inserting to qp_preq_line_attrs_temp' || sqlerrm);

Line: 271


    insert into qp_int_lines
   (
    LINE_INDEX,
    LINE_TYPE_CODE,
    PRICING_EFFECTIVE_DATE,
    START_DATE_ACTIVE_FIRST,
    ACTIVE_DATE_FIRST_TYPE,
    START_DATE_ACTIVE_SECOND,
    ACTIVE_DATE_SECOND_TYPE,
    LINE_QUANTITY,
    LINE_UOM_CODE,
    REQUEST_TYPE_CODE,
    PRICED_QUANTITY,
    PRICED_UOM_CODE,
    CURRENCY_CODE,
    UNIT_PRICE,
    ADJUSTED_UNIT_PRICE,
    PRICE_FLAG,
    UOM_QUANTITY,
    LINE_ID,
    HEADER_ID,
    ROUNDING_FACTOR,
    UPDATED_ADJUSTED_UNIT_PRICE,
    PRICE_LIST_HEADER_ID,
    PRICE_REQUEST_CODE,
    USAGE_PRICING_TYPE,
    LINE_CATEGORY,
    CONTRACT_START_DATE,
    CONTRACT_END_DATE,
    CATCHWEIGHT_QTY,
    ACTUAL_ORDER_QUANTITY,
    PRICING_STATUS_CODE
    )
    values
(
--select
    p_line_rec.LINE_INDEX(i),
    'LINE',                      --line_type_code
    p_line_rec.PRICING_DATE(i),   --OM will default the right value
   --line.START_DATE_ACTIVE_FIRST,
    l_active_date_first_tbl(i),
    'ORDER',--p_line_rec.ACTIVE_DATE_FIRST_TYPE(i),
    NULL,--p_line_rec.ACTIVE_DATE_SECOND(i)),
    NULL,--p_line_rec.ACTIVE_DATE_SECOND_TYPE(i),
    p_line_rec.ORDERED_QUANTITY(i),
    p_line_rec.ORDER_QUANTITY_UOM(i),
    'ONT',--request_type_code hard-coded
    p_line_rec.PRICING_QUANTITY(i),
    p_line_rec.PRICING_QUANTITY_UOM(i),
--    line.currency_code,
    l_CURRENCY_CODE_tbl(i),   --this w/b populated by OM
    p_line_rec.unit_list_price_per_pqty(i),
    p_line_rec.unit_selling_price_per_pqty(i),

    p_line_rec.CALCULATE_PRICE_FLAG(i),----OM will default the right value
    NULL,--uom_qty service pricing is not supported for this phase of HVOP
    p_line_rec.LINE_ID(i),
    p_line_rec.HEADER_ID(i),
    NULL,--rounding_factor is null
    NULL,--UPDATED_ADJUSTED_UNIT_PRICE is null for OM
    p_line_rec.PRICE_LIST_ID(i),
    NULL,--price_req_code is null limits not supported for this phase of HVOP
    QP_PREQ_GRP.G_REGULAR_USAGE_TYPE,--usage_pricing_type
    p_line_rec.LINE_CATEGORY_CODE(i),
    NULL,--CONTRACT_START_DATE
    NULL,--CONTRACT_END_DATE
    NULL,--CATCHWT_QTY

    NULL,--ACTUAL_ORDER_QTY
    'X'  );

Line: 353


  G_line_index.delete;

Line: 354


  G_attr_type.delete;

Line: 355


  G_attr_context.delete;

Line: 356


  G_attr_attr.delete;

Line: 357


  G_attr_value.delete;

Line: 358


  G_validated_flag.delete;

Line: 397


insert into qp_int_line_attrs
                (line_index,
                 attribute_level,
                 attribute_type,
                 context,
                 attribute,
                 value_from,
                 validated_flag,
                 applied_flag,
                 pricing_status_code,
                 pricing_attr_flag
                )
   VALUES	(G_line_index(i),
                 QP_PREQ_PUB.G_LINE_LEVEL,
		 G_attr_type(i),
                 G_attr_context(i),
                 G_attr_attr(i),
                 G_attr_value(i),
                 G_validated_flag(i),
                 QP_PREQ_PUB.G_LIST_NOT_APPLIED,
                 QP_PREQ_PUB.G_STATUS_UNCHANGED,
                 QP_PREQ_PUB.G_YES
                );

Line: 434


x_return_status_text := 'Exception in Bulk_insert_lines '||SQLERRM;

Line: 438


END Bulk_insert_lines;

Line: 440


Procedure Bulk_insert_adj(x_return_status OUT NOCOPY VARCHAR2,
                         x_return_status_text OUT NOCOPY VARCHAR2) IS
ldet_count number;

Line: 450


insert into qp_int_ldets
  (LINE_DETAIL_INDEX,
  LINE_DETAIL_TYPE_CODE,
  PRICE_BREAK_TYPE_CODE,
  LINE_INDEX,
  CREATED_FROM_LIST_HEADER_ID,
  CREATED_FROM_LIST_LINE_ID,
  CREATED_FROM_LIST_LINE_TYPE,
  CREATED_FROM_LIST_TYPE_CODE,
  PRICING_GROUP_SEQUENCE,
  PRICING_PHASE_ID,
  OPERAND_CALCULATION_CODE,
  OPERAND_VALUE,
  SUBSTITUTION_TYPE_CODE,
  SUBSTITUTION_VALUE_FROM,
  SUBSTITUTION_VALUE_TO,
  PRICE_FORMULA_ID,
  PRICING_STATUS_CODE,
  PRODUCT_PRECEDENCE,
  INCOMPATABILITY_GRP_CODE,
  APPLIED_FLAG,
  AUTOMATIC_FLAG,
  OVERRIDE_FLAG,
  MODIFIER_LEVEL_CODE,
  BENEFIT_QTY,
  BENEFIT_UOM_CODE,
  LIST_LINE_NO,
  ACCRUAL_FLAG,
  ACCRUAL_CONVERSION_RATE,
  ESTIM_ACCRUAL_RATE,
  CHARGE_TYPE_CODE,
  CHARGE_SUBTYPE_CODE,
  PROCESS_CODE,
  LINE_QUANTITY,
  UPDATED_FLAG, -- begin shu, fix Bug 2599822
  CALCULATION_CODE,
  CHANGE_REASON_CODE,
  CHANGE_REASON_TEXT,
  ADJUSTMENT_AMOUNT)
(select
  rownum,
  'NULL',
  ll.PRICE_BREAK_TYPE_CODE,
  line.LINE_INDEX,
  ll.LIST_HEADER_ID,
  ll.LIST_LINE_ID,
  ll.LIST_LINE_TYPE_CODE,
  lhdr.LIST_TYPE_CODE,
  ll.PRICING_GROUP_SEQUENCE,
  ll.PRICING_PHASE_ID,
  ll.ARITHMETIC_OPERATOR,
  nvl(adj_iface.OPERAND_PER_PQTY,adj_iface.OPERAND),
  ll.SUBSTITUTION_ATTRIBUTE,
  ll.SUBSTITUTION_VALUE,
  NULL,
  ll.PRICE_BY_FORMULA_ID,
  QP_PREQ_PUB.G_STATUS_UNCHANGED,
  ll.PRODUCT_PRECEDENCE,
  ll.INCOMPATIBILITY_GRP_CODE,
  adj_iface.APPLIED_FLAG,
  ll.AUTOMATIC_FLAG,
  ll.OVERRIDE_FLAG,
  ll.MODIFIER_LEVEL_CODE,
  ll.BENEFIT_QTY,
  ll.BENEFIT_UOM_CODE,
  ll.LIST_LINE_NO,
  ll.ACCRUAL_FLAG,
  ll.ACCRUAL_CONVERSION_RATE,
  ll.ESTIM_ACCRUAL_RATE,
  ll.CHARGE_TYPE_CODE,
  ll.CHARGE_SUBTYPE_CODE,
  QP_PREQ_PUB.G_STATUS_NEW,
  line.priced_quantity, --adj_iface.range_break_quantity, --need to find out what engine populates this as
  adj_iface.UPDATED_FLAG, -- begin shu, fix Bug 2599822
  NULL,
  NULL,
  NULL,
  adj_iface.ADJUSTED_AMOUNT_PER_PQTY
From  oe_price_adjs_iface_all adj_iface,
      oe_lines_iface_all lines_iface,
      qp_int_lines line,
      qp_list_lines ll,
      qp_list_headers_b lhdr
Where adj_iface.ORDER_SOURCE_ID = lines_iface.ORDER_SOURCE_ID
and adj_iface.ORIG_SYS_DOCUMENT_REF = lines_iface.ORIG_SYS_DOCUMENT_REF
and adj_iface.ORIG_SYS_LINE_REF = lines_iface.ORIG_SYS_LINE_REF
and line.line_type_code = 'LINE'
and   lines_iface.line_id = line.line_id
--and   lines_iface.header_id = line.header_id
and   ll.list_line_id = adj_iface.list_line_id
and   lhdr.list_header_id = ll.list_header_id);

Line: 542


select nvl(max(line_detail_index),0) into line_ldet_count from qp_int_ldets;  -- fix for bug 3406218

Line: 549


insert into qp_int_ldets
  (LINE_DETAIL_INDEX,
  LINE_DETAIL_TYPE_CODE,
  PRICE_BREAK_TYPE_CODE,
  LINE_INDEX,
  CREATED_FROM_LIST_HEADER_ID,
  CREATED_FROM_LIST_LINE_ID,
  CREATED_FROM_LIST_LINE_TYPE,
  CREATED_FROM_LIST_TYPE_CODE,
  PRICING_GROUP_SEQUENCE,
  PRICING_PHASE_ID,
  OPERAND_CALCULATION_CODE,
  OPERAND_VALUE,
  SUBSTITUTION_TYPE_CODE,
  SUBSTITUTION_VALUE_FROM,
  SUBSTITUTION_VALUE_TO,
  PRICE_FORMULA_ID,
  PRICING_STATUS_CODE,
  PRODUCT_PRECEDENCE,
  INCOMPATABILITY_GRP_CODE,
  APPLIED_FLAG,
  AUTOMATIC_FLAG,
  OVERRIDE_FLAG,
  MODIFIER_LEVEL_CODE,
  BENEFIT_QTY,
  BENEFIT_UOM_CODE,
  LIST_LINE_NO,
  ACCRUAL_FLAG,
  ACCRUAL_CONVERSION_RATE,
  ESTIM_ACCRUAL_RATE,
  CHARGE_TYPE_CODE,
  CHARGE_SUBTYPE_CODE,
  PROCESS_CODE,
  LINE_QUANTITY,
  UPDATED_FLAG, -- begin shu, fix Bug 2599822
  CALCULATION_CODE,
  CHANGE_REASON_CODE,
  CHANGE_REASON_TEXT,
  ADJUSTMENT_AMOUNT)
(select
  rownum+line_ldet_count,
  'NULL',
  ll.PRICE_BREAK_TYPE_CODE,
  line.LINE_INDEX,
  ll.LIST_HEADER_ID,
  ll.LIST_LINE_ID,
  ll.LIST_LINE_TYPE_CODE,
  lhdr.LIST_TYPE_CODE,
  ll.PRICING_GROUP_SEQUENCE,
  ll.PRICING_PHASE_ID,
  ll.ARITHMETIC_OPERATOR,
  nvl(adj_iface.OPERAND_PER_PQTY,adj_iface.OPERAND),
  ll.SUBSTITUTION_ATTRIBUTE,
  ll.SUBSTITUTION_VALUE,
  NULL,
  ll.PRICE_BY_FORMULA_ID,
  QP_PREQ_PUB.G_STATUS_UNCHANGED,
  ll.PRODUCT_PRECEDENCE,
  ll.INCOMPATIBILITY_GRP_CODE,
  adj_iface.APPLIED_FLAG,
  ll.AUTOMATIC_FLAG,
  ll.OVERRIDE_FLAG,
  ll.MODIFIER_LEVEL_CODE,
  ll.BENEFIT_QTY,
  ll.BENEFIT_UOM_CODE,
  ll.LIST_LINE_NO,
  ll.ACCRUAL_FLAG,
  ll.ACCRUAL_CONVERSION_RATE,
  ll.ESTIM_ACCRUAL_RATE,
  ll.CHARGE_TYPE_CODE,
  ll.CHARGE_SUBTYPE_CODE,
  QP_PREQ_PUB.G_STATUS_NEW,
  line.priced_quantity, --adj_iface.range_break_quantity, --need to find out what engine populates this as
  adj_iface.UPDATED_FLAG, -- begin shu, fix Bug 2599822
  NULL,
  NULL,
  NULL,
  adj_iface.ADJUSTED_AMOUNT_PER_PQTY
From  oe_price_adjs_iface_all adj_iface,
      oe_headers_iface_all hdrs_iface,
      qp_int_lines line,
      qp_list_lines ll,
      qp_list_headers_b lhdr
Where adj_iface.ORDER_SOURCE_ID = hdrs_iface.ORDER_SOURCE_ID
and adj_iface.ORIG_SYS_DOCUMENT_REF = hdrs_iface.ORIG_SYS_DOCUMENT_REF
and line.line_type_code = 'ORDER'
and adj_iface.ORIG_SYS_LINE_REF is null
and   hdrs_iface.header_id = line.header_id     -- fix for bug 3406218
and line.line_id is null
--and   lines_iface.header_id = line.header_id
and   ll.list_line_id = adj_iface.list_line_id
and   lhdr.list_header_id = ll.list_header_id);

Line: 643


    oe_debug_pub.add ('QP_BULK_PREQ_GRP.bulk_insert_adj inserted adj count='||SQL%ROWCOUNT);

Line: 647


select count(*) into ldet_count
From  oe_price_adjs_iface_all adj_iface,
      oe_lines_iface_all lines_iface,
      qp_int_lines line,
      qp_list_lines ll,
      qp_list_headers_b lhdr
Where adj_iface.ORIG_SYS_LINE_REF = lines_iface.ORIG_SYS_LINE_REF
and   lines_iface.line_id = line.line_id
--and   lines_iface.header_id = line.header_id
and   ll.list_line_id = adj_iface.list_line_id
and   lhdr.list_header_id = ll.list_header_id;

Line: 665


    FOR cl in (select line_index, line_detail_index, operand_value, operand_calculation_code, updated_flag, applied_flag
              ,created_from_list_line_id, automatic_flag, override_flag from qp_int_ldets)
    LOOP
      oe_debug_pub.add('Inserted ldet details: line_index '||cl.line_index||' line_detail_index '||cl.line_detail_index
      ||' created_from_list_line_id '||cl.created_from_list_line_id||' operand_value '||cl.operand_value||' arithmetic_operator '
      ||cl.operand_calculation_code||' updated_flag '||cl.updated_flag||' applied_flag '||cl.applied_flag||' automatic_flag '
      ||cl.automatic_flag||' override_flag '||cl.override_flag);

Line: 683


x_return_status_text := 'Exception in Bulk_insert_adj '||SQLERRM;

Line: 687


End Bulk_insert_adj;