The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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)
);
select count(*) into l_preq_count
from qp_int_lines ;
G_line_index.delete;
G_attr_type.delete;
G_attr_context.delete;
G_attr_attr.delete;
G_attr_value.delete;
G_validated_flag.delete;
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
);
oe_debug_pub.add('Error inserting to qp_preq_line_attrs_temp' || sqlerrm);
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' );
G_line_index.delete;
G_attr_type.delete;
G_attr_context.delete;
G_attr_attr.delete;
G_attr_value.delete;
G_validated_flag.delete;
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
);
x_return_status_text := 'Exception in Bulk_insert_lines '||SQLERRM;
END Bulk_insert_lines;
Procedure Bulk_insert_adj(x_return_status OUT NOCOPY VARCHAR2,
x_return_status_text OUT NOCOPY VARCHAR2) IS
ldet_count number;
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);
select nvl(max(line_detail_index),0) into line_ldet_count from qp_int_ldets; -- fix for bug 3406218
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);
oe_debug_pub.add ('QP_BULK_PREQ_GRP.bulk_insert_adj inserted adj count='||SQL%ROWCOUNT);
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;
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);
x_return_status_text := 'Exception in Bulk_insert_adj '||SQLERRM;
End Bulk_insert_adj;