The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_I_request_type_code.delete;
g_I_line_id.delete;
g_I_line_index.delete;
g_I_line_type_code.delete;
g_I_pricing_effective_date.delete;
g_I_active_date_first.delete;
g_I_active_date_second.delete;
g_I_line_quantity.delete;
g_I_line_uom_code.delete;
g_I_currency_code.delete;
g_I_price_flag.delete;
G_I_ACTIVE_DATE_FIRST_TYPE.delete;
G_I_ACTIVE_DATE_SECOND_TYPE.delete;
G_I_PRICED_QUANTITY.delete;
G_I_PRICED_UOM_CODE.delete;
G_I_UNIT_PRICE.delete;
G_I_PERCENT_PRICE.delete;
G_I_UOM_QUANTITY.delete;
G_I_ADJUSTED_UNIT_PRICE.delete;
G_I_UPD_ADJUSTED_UNIT_PRICE.delete;
G_I_PROCESSED_FLAG.delete;
G_I_PROCESSING_ORDER.delete;
G_I_PRICING_STATUS_CODE.delete;
G_I_PRICING_STATUS_TEXT.delete;
G_I_ROUNDING_FLAG.delete;
G_I_ROUNDING_FACTOR.delete;
G_I_QUALIFIERS_EXIST_FLAG.delete;
G_I_PRICING_ATTRS_EXIST_FLAG.delete;
G_I_PRICE_LIST_ID.delete;
G_I_VALIDATED_FLAG.delete;
G_I_PRICE_REQUEST_CODE.delete;
G_I_USAGE_PRICING_TYPE.delete;
G_I_LINE_CATEGORY.delete;
g_I_line_extras_tbl.delete;
g_I_A_LINE_INDEX.delete;
g_I_A_CONTEXT.delete;
g_I_A_ATTRIBUTE_TYPE.delete;
g_I_A_ATTRIBUTE.delete;
g_I_A_VALUE_FROM.delete;
G_I_A_VALIDATED_FLAG.delete;
G_I_A_LINE_DETAIL_INDEX.delete;
G_I_A_ATTRIBUTE_LEVEL.delete;
G_I_A_LIST_HEADER_ID.delete;
G_I_A_LIST_LINE_ID.delete;
G_I_A_SETUP_VALUE_FROM.delete;
G_I_A_VALUE_TO.delete;
G_I_A_SETUP_VALUE_TO.delete;
G_I_A_GROUPING_NUMBER.delete;
G_I_A_NO_QUALIFIERS_IN_GRP.delete;
G_I_A_APPLIED_FLAG.delete;
G_I_A_PRICING_STATUS_CODE.delete;
G_I_A_PRICING_STATUS_TEXT.delete;
G_I_A_QUALIFIER_PRECEDENCE.delete;
G_I_A_DATATYPE.delete;
G_I_A_PRICING_ATTR_FLAG.delete;
G_I_A_QUALIFIER_TYPE.delete;
G_I_A_PRODUCT_UOM_CODE.delete;
G_I_A_EXCLUDER_FLAG.delete;
G_I_A_PRICING_PHASE_ID.delete;
G_I_A_INCOMPATABILITY_GRP_CODE.delete;
G_I_A_LINE_DETAIL_TYPE_CODE.delete;
G_I_A_MODIFIER_LEVEL_CODE.delete;
G_I_A_PRIMARY_UOM_FLAG.delete;
PROCEDURE delete_line_from_qp_input(
p_line_index IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
i NUMBER;
l_method_name VARCHAR2(50) := 'delete_line_from_qp_input';
fte_freight_pricing_util.print_msg(l_log_level,'delete line where i = '||i);
g_I_request_type_code.delete(i);
g_I_line_id.delete(i);
g_I_line_index.delete(i);
g_I_line_type_code.delete(i);
g_I_pricing_effective_date.delete(i);
g_I_active_date_first.delete(i);
g_I_active_date_second.delete(i);
g_I_line_quantity.delete(i);
g_I_line_uom_code.delete(i);
g_I_currency_code.delete(i);
g_I_price_flag.delete(i);
G_I_ACTIVE_DATE_FIRST_TYPE.delete(i);
G_I_ACTIVE_DATE_SECOND_TYPE.delete(i);
G_I_PRICED_QUANTITY.delete(i);
G_I_PRICED_UOM_CODE.delete(i);
G_I_UNIT_PRICE.delete(i);
G_I_PERCENT_PRICE.delete(i);
G_I_UOM_QUANTITY.delete(i);
G_I_ADJUSTED_UNIT_PRICE.delete(i);
G_I_UPD_ADJUSTED_UNIT_PRICE.delete(i);
G_I_PROCESSED_FLAG.delete(i);
G_I_PROCESSING_ORDER.delete(i);
G_I_PRICING_STATUS_CODE.delete(i);
G_I_PRICING_STATUS_TEXT.delete(i);
G_I_ROUNDING_FLAG.delete(i);
G_I_ROUNDING_FACTOR.delete(i);
G_I_QUALIFIERS_EXIST_FLAG.delete(i);
G_I_PRICING_ATTRS_EXIST_FLAG.delete(i);
G_I_PRICE_LIST_ID.delete(i);
G_I_VALIDATED_FLAG.delete(i);
G_I_PRICE_REQUEST_CODE.delete(i);
G_I_USAGE_PRICING_TYPE.delete(i);
G_I_LINE_CATEGORY.delete(i);
g_I_line_extras_tbl.delete(i);
fte_freight_pricing_util.print_msg(l_log_level,'delete line attr where j = '||j);
g_I_A_LINE_INDEX.delete(j);
g_I_A_CONTEXT.delete(j);
g_I_A_ATTRIBUTE_TYPE.delete(j);
g_I_A_ATTRIBUTE.delete(j);
g_I_A_VALUE_FROM.delete(j);
G_I_A_VALIDATED_FLAG.delete(j);
G_I_A_LINE_DETAIL_INDEX.delete(j);
G_I_A_ATTRIBUTE_LEVEL.delete(j);
G_I_A_LIST_HEADER_ID.delete(j);
G_I_A_LIST_LINE_ID.delete(j);
G_I_A_SETUP_VALUE_FROM.delete(j);
G_I_A_VALUE_TO.delete(j);
G_I_A_SETUP_VALUE_TO.delete(j);
G_I_A_GROUPING_NUMBER.delete(j);
G_I_A_NO_QUALIFIERS_IN_GRP.delete(j);
G_I_A_APPLIED_FLAG.delete(j);
G_I_A_PRICING_STATUS_CODE.delete(j);
G_I_A_PRICING_STATUS_TEXT.delete(j);
G_I_A_QUALIFIER_PRECEDENCE.delete(j);
G_I_A_DATATYPE.delete(j);
G_I_A_PRICING_ATTR_FLAG.delete(j);
G_I_A_QUALIFIER_TYPE.delete(j);
G_I_A_PRODUCT_UOM_CODE.delete(j);
G_I_A_EXCLUDER_FLAG.delete(j);
G_I_A_PRICING_PHASE_ID.delete(j);
G_I_A_INCOMPATABILITY_GRP_CODE.delete(j);
G_I_A_LINE_DETAIL_TYPE_CODE.delete(j);
G_I_A_MODIFIER_LEVEL_CODE.delete(j);
G_I_A_PRIMARY_UOM_FLAG.delete(j);
END delete_line_from_qp_input;
PROCEDURE delete_line_from_qp_output(
p_line_index IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
j NUMBER;
l_method_name VARCHAR2(50) := 'delete_line_from_qp_output';
g_O_line_tbl.delete(p_line_index);
fte_freight_pricing_util.print_msg(l_log_level,'delete line where i = '||p_line_index);
fte_freight_pricing_util.print_msg(l_log_level,'delete line attr where j = '||j);
g_O_line_detail_tbl.delete(j);
END delete_line_from_qp_output;
PROCEDURE delete_set_from_line_event(p_set_num IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
i NUMBER :=0;
l_method_name VARCHAR2(50) := 'delete_set_from_line_event';
fte_freight_pricing_util.set_method(l_log_level,'delete_set_from_line_event');
delete_line_from_qp_input(
p_line_index => i,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_delete_set_failed;
delete_line_from_qp_output(
p_line_index => i,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_delete_set_failed;
fte_freight_pricing_util.unset_method(l_log_level,'delete_set_from_line_event');
WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_set_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'delete_set_failed');
fte_freight_pricing_util.unset_method(l_log_level,'delete_set_from_line_event');
fte_freight_pricing_util.unset_method(l_log_level,'delete_set_from_line_event');
END delete_set_from_line_event;
PROCEDURE delete_lines(p_line_index IN NUMBER,
x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
x_qp_output_detail_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2)
IS
i NUMBER;
l_method_name VARCHAR2(50) := 'delete_lines';
fte_freight_pricing_util.set_method(l_log_level,'delete_lines');
delete_line_from_qp_input(
p_line_index => p_line_index,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed;
delete_line_from_qp_output(
p_line_index => p_line_index,
x_return_status => l_return_status);
raise FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed;
fte_freight_pricing_util.unset_method(l_log_level,'delete_lines');
WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'delete_qpline_failed');
fte_freight_pricing_util.unset_method(l_log_level,'delete_lines');
fte_freight_pricing_util.unset_method(l_log_level,'delete_lines');
END delete_lines;
G_I_LINE_INDEX.delete(i);
g_I_line_extras_tbl.delete(i);
G_I_A_LINE_INDEX.delete(i);
l_control_rec.temp_table_insert_flag := 'N';
select
request_type_code,
line_id,
line_index,
line_type_code,
pricing_effective_date,
line_quantity,
line_uom_code,
priced_quantity,
priced_uom_code,
currency_code,
unit_price,
adjusted_unit_price,
price_flag,
extended_price,
start_date_active_first,
start_date_active_second,
pricing_status_code,
pricing_status_text
from qp_preq_lines_tmp;
select
line_detail_index,
line_detail_type_code,
line_index,
list_header_id,
list_line_id,
list_line_type_code,
adjustment_amount,
charge_type_code,
charge_subtype_code,
line_quantity,
operand_calculation_code,
operand_value,
automatic_flag,
override_flag,
pricing_status_code,
pricing_status_text
from qp_ldets_v
where pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
and ( list_line_type_code = QP_PREQ_GRP.G_DISCOUNT
or ((list_line_type_code = QP_PREQ_GRP.G_SURCHARGE
or list_line_type_code = QP_PREQ_GRP.G_PRICE_BREAK_TYPE ) -- TL
and charge_subtype_code IS NOT NULL) -- TL
);
SELECT
ldets.line_detail_index,
ldets.line_detail_type_code,
ldets.line_index,
ldets.list_header_id,
ldets.list_line_id,
ldets.list_line_type_code,
ldets.adjustment_amount,
ldets.charge_type_code,
ldets.charge_subtype_code,
ldets.line_quantity,
ldets.operand_calculation_code,
ldets.operand_value,
ldets.automatic_flag,
ldets.override_flag,
ldets.pricing_status_code,
ldets.pricing_status_text
FROM qp_ldets_v ldets,
qp_preq_rltd_lines_tmp rltd
WHERE
rltd.related_line_index = ldets.line_index
AND rltd.related_line_detail_index = ldets.line_detail_index
AND rltd.line_index = c_parent_line_index
AND rltd.line_detail_index = c_parent_line_detail_index
AND rltd.relationship_type_code = QP_PREQ_GRP.G_PBH_LINE
AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND ldets.list_line_type_code = QP_PREQ_GRP.G_SURCHARGE
AND ldets.operand_calculation_code = 'LUMPSUM'
AND ldets.line_quantity >0;
g_O_line_tbl.DELETE;
g_O_line_detail_tbl.DELETE;
QP_PREQ_GRP.INSERT_LINES2(
p_LINE_INDEX => G_I_LINE_INDEX,
p_LINE_TYPE_CODE => G_I_LINE_TYPE_CODE,
p_PRICING_EFFECTIVE_DATE => G_I_PRICING_EFFECTIVE_DATE,
p_ACTIVE_DATE_FIRST => G_I_ACTIVE_DATE_FIRST,
p_ACTIVE_DATE_FIRST_TYPE => G_I_ACTIVE_DATE_FIRST_TYPE,
p_ACTIVE_DATE_SECOND => G_I_ACTIVE_DATE_SECOND,
p_ACTIVE_DATE_SECOND_TYPE => G_I_ACTIVE_DATE_SECOND_TYPE,
p_LINE_QUANTITY => G_I_LINE_QUANTITY,
p_LINE_UOM_CODE => G_I_LINE_UOM_CODE,
p_REQUEST_TYPE_CODE => G_I_REQUEST_TYPE_CODE,
p_PRICED_QUANTITY => G_I_PRICED_QUANTITY,
p_PRICED_UOM_CODE => G_I_PRICED_UOM_CODE,
p_CURRENCY_CODE => G_I_CURRENCY_CODE,
p_UNIT_PRICE => G_I_UNIT_PRICE,
p_PERCENT_PRICE => G_I_PERCENT_PRICE,
p_UOM_QUANTITY => G_I_UOM_QUANTITY,
p_ADJUSTED_UNIT_PRICE => G_I_ADJUSTED_UNIT_PRICE,
p_UPD_ADJUSTED_UNIT_PRICE => G_I_UPD_ADJUSTED_UNIT_PRICE,
p_PROCESSED_FLAG => G_I_PROCESSED_FLAG,
p_PRICE_FLAG => G_I_PRICE_FLAG,
p_LINE_ID => G_I_LINE_ID,
p_PROCESSING_ORDER => G_I_PROCESSING_ORDER,
p_PRICING_STATUS_CODE => G_I_PRICING_STATUS_CODE,
p_PRICING_STATUS_TEXT => G_I_PRICING_STATUS_TEXT,
p_ROUNDING_FLAG => G_I_ROUNDING_FLAG,
p_ROUNDING_FACTOR => G_I_ROUNDING_FACTOR,
p_QUALIFIERS_EXIST_FLAG => G_I_QUALIFIERS_EXIST_FLAG,
p_PRICING_ATTRS_EXIST_FLAG => G_I_PRICING_ATTRS_EXIST_FLAG,
p_PRICE_LIST_ID => G_I_PRICE_LIST_ID,
p_VALIDATED_FLAG => G_I_VALIDATED_FLAG,
p_PRICE_REQUEST_CODE => G_I_PRICE_REQUEST_CODE,
p_USAGE_PRICING_TYPE => G_I_USAGE_PRICING_TYPE,
p_line_category => G_I_LINE_CATEGORY,
x_status_code => l_return_status,
x_status_text => l_return_status_text);
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after QP_PREQ_GRP_INSERT_LINES2');
raise FTE_FREIGHT_PRICING_UTIL.g_qp_insert_lines2_failed;
QP_PREQ_GRP.INSERT_LINE_ATTRS2(
p_LINE_INDEX_tbl => G_I_A_LINE_INDEX,
p_LINE_DETAIL_INDEX_tbl => G_I_A_LINE_DETAIL_INDEX,
p_ATTRIBUTE_LEVEL_tbl => G_I_A_ATTRIBUTE_LEVEL,
p_ATTRIBUTE_TYPE_tbl => G_I_A_ATTRIBUTE_TYPE,
p_LIST_HEADER_ID_tbl => G_I_A_LIST_HEADER_ID,
p_LIST_LINE_ID_tbl => G_I_A_LIST_LINE_ID,
p_CONTEXT_tbl => G_I_A_CONTEXT,
p_ATTRIBUTE_tbl => G_I_A_ATTRIBUTE,
p_VALUE_FROM_tbl => G_I_A_VALUE_FROM,
p_SETUP_VALUE_FROM_tbl => G_I_A_SETUP_VALUE_FROM,
p_VALUE_TO_tbl => G_I_A_VALUE_TO,
p_SETUP_VALUE_TO_tbl => G_I_A_SETUP_VALUE_TO,
p_GROUPING_NUMBER_tbl => G_I_A_GROUPING_NUMBER,
p_NO_QUALIFIERS_IN_GRP_tbl => G_I_A_NO_QUALIFIERS_IN_GRP,
p_COMPARISON_OPERATOR_TYPE_tbl => G_I_A_COMPARISON_OPERATOR_TYPE,
p_VALIDATED_FLAG_tbl => G_I_A_VALIDATED_FLAG,
p_APPLIED_FLAG_tbl => G_I_A_APPLIED_FLAG,
p_PRICING_STATUS_CODE_tbl => G_I_A_PRICING_STATUS_CODE,
p_PRICING_STATUS_TEXT_tbl => G_I_A_PRICING_STATUS_TEXT,
p_QUALIFIER_PRECEDENCE_tbl => G_I_A_QUALIFIER_PRECEDENCE,
p_DATATYPE_tbl => G_I_A_DATATYPE,
p_PRICING_ATTR_FLAG_tbl => G_I_A_PRICING_ATTR_FLAG,
p_QUALIFIER_TYPE_tbl => G_I_A_QUALIFIER_TYPE,
p_PRODUCT_UOM_CODE_TBL => G_I_A_PRODUCT_UOM_CODE,
p_EXCLUDER_FLAG_TBL => G_I_A_EXCLUDER_FLAG,
p_PRICING_PHASE_ID_TBL => G_I_A_PRICING_PHASE_ID,
p_INCOMPATABILITY_GRP_CODE_TBL => G_I_A_INCOMPATABILITY_GRP_CODE,
p_LINE_DETAIL_TYPE_CODE_TBL => G_I_A_LINE_DETAIL_TYPE_CODE,
p_MODIFIER_LEVEL_CODE_TBL => G_I_A_MODIFIER_LEVEL_CODE,
p_PRIMARY_UOM_FLAG_TBL => G_I_A_PRIMARY_UOM_FLAG,
x_status_code => l_return_status,
x_status_text => l_return_status_text);
FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after QP_PREQ_GRP_INSERT_LINE_ATTRS2');
raise FTE_FREIGHT_PRICING_UTIL.g_qp_insert_line_attrs2_failed;
WHEN FTE_FREIGHT_PRICING_UTIL.g_qp_insert_lines2_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'qp_insert_lines2_failed');
WHEN FTE_FREIGHT_PRICING_UTIL.g_qp_insert_line_attrs2_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'qp_insert_line_attrs2_failed');
fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Temp Table Insert Flag '||g_I_control_rec.temp_table_insert_flag);
G_I_LINE_INDEX.DELETE;
G_I_LINE_TYPE_CODE.DELETE;
G_I_PRICING_EFFECTIVE_DATE.DELETE;
G_I_ACTIVE_DATE_FIRST.DELETE;
G_I_ACTIVE_DATE_FIRST_TYPE.DELETE;
G_I_ACTIVE_DATE_SECOND.DELETE;
G_I_ACTIVE_DATE_SECOND_TYPE.DELETE;
G_I_LINE_QUANTITY.DELETE;
G_I_LINE_UOM_CODE.DELETE;
G_I_REQUEST_TYPE_CODE.DELETE;
G_I_PRICED_QUANTITY.DELETE;
G_I_PRICED_UOM_CODE.DELETE;
G_I_CURRENCY_CODE.DELETE;
G_I_UNIT_PRICE.DELETE;
G_I_PERCENT_PRICE.DELETE;
G_I_UOM_QUANTITY.DELETE;
G_I_ADJUSTED_UNIT_PRICE.DELETE;
G_I_UPD_ADJUSTED_UNIT_PRICE.DELETE;
G_I_PROCESSED_FLAG.DELETE;
G_I_PRICE_FLAG.DELETE;
G_I_LINE_ID.DELETE;
G_I_PROCESSING_ORDER.DELETE;
G_I_PRICING_STATUS_CODE.DELETE;
G_I_PRICING_STATUS_TEXT.DELETE;
G_I_ROUNDING_FLAG.DELETE;
G_I_ROUNDING_FACTOR.DELETE;
G_I_QUALIFIERS_EXIST_FLAG.DELETE;
G_I_PRICING_ATTRS_EXIST_FLAG.DELETE;
G_I_PRICE_LIST_ID.DELETE;
G_I_VALIDATED_FLAG.DELETE;
G_I_PRICE_REQUEST_CODE.DELETE;
G_I_USAGE_PRICING_TYPE.DELETE;
G_I_LINE_CATEGORY.DELETE;
G_I_A_LINE_INDEX.DELETE;
G_I_A_LINE_DETAIL_INDEX.DELETE;
G_I_A_ATTRIBUTE_LEVEL.DELETE;
G_I_A_ATTRIBUTE_TYPE.DELETE;
G_I_A_LIST_HEADER_ID.DELETE;
G_I_A_LIST_LINE_ID.DELETE;
G_I_A_CONTEXT.DELETE;
G_I_A_ATTRIBUTE.DELETE;
G_I_A_VALUE_FROM.DELETE;
G_I_A_SETUP_VALUE_FROM.DELETE;
G_I_A_VALUE_TO.DELETE;
G_I_A_SETUP_VALUE_TO.DELETE;
G_I_A_GROUPING_NUMBER.DELETE;
G_I_A_NO_QUALIFIERS_IN_GRP.DELETE;
G_I_A_COMPARISON_OPERATOR_TYPE.DELETE;
G_I_A_VALIDATED_FLAG.DELETE;
G_I_A_APPLIED_FLAG.DELETE;
G_I_A_PRICING_STATUS_CODE.DELETE;
G_I_A_PRICING_STATUS_TEXT.DELETE;
G_I_A_QUALIFIER_PRECEDENCE.DELETE;
G_I_A_DATATYPE.DELETE;
G_I_A_PRICING_ATTR_FLAG.DELETE;
G_I_A_QUALIFIER_TYPE.DELETE;
G_I_A_PRODUCT_UOM_CODE.DELETE;
G_I_A_EXCLUDER_FLAG.DELETE;
G_I_A_PRICING_PHASE_ID.DELETE;
G_I_A_INCOMPATABILITY_GRP_CODE.DELETE;
G_I_A_LINE_DETAIL_TYPE_CODE.DELETE;
G_I_A_MODIFIER_LEVEL_CODE.DELETE;
G_I_A_PRIMARY_UOM_FLAG.DELETE;
g_I_line_extras_tbl.DELETE;
g_O_line_tbl.DELETE;
g_O_line_detail_tbl.DELETE;
g_engine_defaults_tab.DELETE;