[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT price_book_line_id
FROM qp_price_book_lines
WHERE sync_action_code = 'N'
AND price_book_header_id = a_price_book_header_id;
l_deleted_line_id_tbl NUMBER_TYPE;
l_new_deleted_line_id_tbl NUMBER_TYPE;
CURSOR deleted_lines_cur(a_full_price_book_header_id NUMBER,
a_delta_price_book_header_id NUMBER)
IS
SELECT a.price_book_line_id, a.item_number, a.product_uom_code,
a.list_price, a.net_price, a.line_status_code
FROM qp_price_book_lines a
WHERE a.price_book_header_id = a_full_price_book_header_id
AND NOT EXISTS (SELECT 'X'
FROM qp_price_book_lines b
WHERE b.price_book_header_id=a_delta_price_book_header_id
AND b.item_number = a.item_number
AND b.product_uom_code = a.product_uom_code)
ORDER BY a.price_book_line_id;
CURSOR deleted_line_dets_cur(a_full_price_book_header_id NUMBER,
a_delta_price_book_header_id NUMBER,
a_deleted_line_id_first NUMBER,
a_deleted_line_id_last NUMBER)
IS
SELECT price_book_line_det_id, price_book_line_id
FROM qp_price_book_line_details
WHERE price_book_header_id = a_full_price_book_header_id
AND price_book_line_id IN (SELECT a.price_book_line_id
FROM qp_price_book_lines a
WHERE a.price_book_header_id =
a_full_price_book_header_id
AND NOT EXISTS
(SELECT 'X'
FROM qp_price_book_lines b
WHERE b.price_book_header_id =
a_delta_price_book_header_id
AND b.item_number = a.item_number
AND b.product_uom_code =
a.product_uom_code)
)
AND price_book_line_id BETWEEN
a_deleted_line_id_first AND a_deleted_line_id_last
ORDER BY price_book_line_det_id;
SELECT price_book_header_id
INTO l_full_price_book_header_id
FROM qp_price_book_headers_vl
WHERE price_book_type_code = 'F'
AND price_book_name = p_delta_price_book_name
AND customer_id = p_delta_price_book_customer_id;
UPDATE qp_price_book_lines a
SET a.sync_action_code = 'N' -- unchanged lines will be removed from delta
WHERE a.price_book_header_id = p_delta_price_book_header_id
AND EXISTS (SELECT 'X'
FROM qp_price_book_lines b
WHERE b.price_book_header_id = l_full_price_book_header_id
AND b.item_number = a.item_number
AND b.product_uom_code = a.product_uom_code
AND nvl(b.list_price, 0) = nvl(a.list_price, 0)
AND nvl(b.net_price, 0) = nvl(a.net_price, 0));
UPDATE qp_price_book_lines a
SET a.sync_action_code = 'A' --Add
WHERE a.price_book_header_id = p_delta_price_book_header_id
AND NOT EXISTS (SELECT 'X'
FROM qp_price_book_lines b
WHERE b.price_book_header_id = l_full_price_book_header_id
AND b.item_number = a.item_number
AND b.product_uom_code = a.product_uom_code);
UPDATE qp_price_book_lines a
SET sync_action_code = 'R' --Replace
WHERE price_book_header_id = p_delta_price_book_header_id
AND EXISTS (SELECT 'X'
FROM qp_price_book_lines b
WHERE b.price_book_header_id = l_full_price_book_header_id
AND b.item_number = a.item_number
AND b.product_uom_code = a.product_uom_code
AND (nvl(b.list_price, 0) <> nvl(a.list_price, 0) OR
nvl(b.net_price, 0) <> nvl(a.net_price, 0)));
OPEN deleted_lines_cur(l_full_price_book_header_id,
p_delta_price_book_header_id);
l_deleted_line_id_tbl.delete;
l_new_deleted_line_id_tbl.delete;
l_item_number_tbl.delete;
l_uom_code_tbl.delete;
l_list_price_tbl.delete;
l_net_price_tbl.delete;
l_line_status_code_tbl.delete;
FETCH deleted_lines_cur BULK COLLECT INTO l_deleted_line_id_tbl,
l_item_number_tbl, l_uom_code_tbl, l_list_price_tbl,
l_net_price_tbl, l_line_status_code_tbl LIMIT rows;
INSERT INTO qp_price_book_lines
(price_book_line_id, price_book_header_id,
item_number,
product_uom_code,
list_price,
net_price,
sync_action_code, line_status_code, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login)
VALUES (qp_price_book_lines_s.nextval, p_delta_price_book_header_id,
l_item_number_tbl(i),
l_uom_code_tbl(i),
l_list_price_tbl(i),
l_net_price_tbl(i),
'D', l_line_status_code_tbl(i), sysdate,
l_user_id, sysdate, l_user_id,
l_login_id)
RETURNING price_book_line_id BULK COLLECT INTO l_new_deleted_line_id_tbl;
FORALL i IN l_new_deleted_line_id_tbl.FIRST..l_new_deleted_line_id_tbl.LAST
INSERT INTO qp_price_book_attributes
(price_book_attribute_id, price_book_line_det_id,
price_book_line_id, price_book_header_id,
pricing_prod_context, pricing_prod_attribute,
pricing_prod_attr_value_from, pricing_attr_value_to,
comparison_operator_code, pricing_prod_attr_datatype,
attribute_type, creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT qp_price_book_attributes_s.nextval, price_book_line_det_id,
l_new_deleted_line_id_tbl(i), p_delta_price_book_header_id,
pricing_prod_context, pricing_prod_attribute,
pricing_prod_attr_value_from, pricing_attr_value_to,
comparison_operator_code, pricing_prod_attr_datatype,
attribute_type, sysdate, l_user_id, sysdate, l_user_id,
l_login_id
FROM qp_price_book_attributes
WHERE price_book_line_id = l_deleted_line_id_tbl(i)
AND price_book_line_det_id = -1;
EXIT WHEN deleted_lines_cur%NOTFOUND;
CLOSE deleted_lines_cur;
l_unchanged_line_id_tbl.delete;
DELETE FROM qp_price_book_break_lines
WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
DELETE FROM qp_price_book_attributes
WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
DELETE FROM qp_price_book_line_details
WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
DELETE FROM qp_price_book_lines
WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
DELETE FROM qp_price_book_messages
WHERE price_book_line_id = l_unchanged_line_id_tbl(i)
AND pb_input_header_id IS NULL;
Internal API to Insert Price Book Lines, Attributes, Break Lines and Messages
******************************************************************************/
PROCEDURE Insert_Price_Book_Content(
p_pb_input_header_rec IN qp_pb_input_headers_vl%ROWTYPE,
p_pb_input_lines_tbl IN QP_PRICE_BOOK_UTIL.pb_input_lines_tbl,
p_price_book_header_id IN NUMBER
)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
CURSOR insert_lines2_cur (a_price_book_header_id NUMBER,
a_effective_date DATE,
a_item_quantity NUMBER,
a_request_type_code VARCHAR2,
a_currency_code VARCHAR2,
a_price_based_on VARCHAR2,
a_pl_agr_bsa_id NUMBER)
IS
SELECT 'LINE', a_effective_date,
null, null, --active_date_first, active_first_date_type
null, null, --active_date_second, active_first_second_type
a_item_quantity, --line_quantity
product_uom_code, --line_uom_code
a_request_type_code,
null, null, --priced_quantity, priced_uom_code
a_currency_code,
null, null, null, --unit_price, percent_price, uom_quantity
null, null, --adjusted_unit_price, upd_adjusted_unit_price
QP_PREQ_GRP.G_NOT_PROCESSED, --processed_flag
'Y', --price_flag
price_book_line_id, --line_id
null, --processing_order
QP_PREQ_GRP.G_STATUS_UNCHANGED, --pricing_status_code
null, --pricing_status_text
'Q', --rounding_flag
null, --rounding_factor
'N', --qualifiers_exist_flag
'N', --pricing_attrs_exist_flag
decode(a_price_based_on, 'PRICE_LIST',
a_pl_agr_bsa_id, NULL), --price_list_id
'N', --validated_flag
null, --price_request_code
null, --usage_pricing_type
item_number
FROM qp_price_book_lines
WHERE price_book_header_id = a_price_book_header_id
ORDER BY price_book_line_id;
CURSOR insert_line_attrs2_cur (a_line_index NUMBER,
a_request_type_code VARCHAR2,
a_pb_input_header_id NUMBER)
IS
SELECT a_line_index, null, --line_index, line_detail_index
QP_PREQ_GRP.G_LINE_LEVEL, --attribute_level
decode(l.attribute_type,
'PRICING_ATTRIBUTE', QP_PREQ_GRP.G_PRICING_TYPE,
l.attribute_type), --attribute_type
null, null, --list_header_id, list_line_id
l.context, --context
l.attribute, --attribute
l.attribute_value, --value_from
null, null, null, --setup_value_from, value_to, setup_value_to
null, null, --grouping_number, no_qualifiers_in_group
null, --comparison_operator_type
'N', --validated_flag
QP_PREQ_GRP.G_LIST_NOT_APPLIED, --applied_flag
QP_PREQ_GRP.G_STATUS_UNCHANGED, --pricing_status_code
null, null, --pricing_status_text, qualifier_precedence
null, --datatype
QP_PREQ_GRP.G_YES, --pricing_attr_flag
null, null, --qualifier_type, product_uom_code
null, null, --excluder_flag, pricing_phase_id
null, null, --incompatibility_grp_code, line_detail_type_code
null, null --modifier_level_code, primary_uom_flag
FROM qp_pb_input_lines l
WHERE pb_input_header_id = a_pb_input_header_id
AND EXISTS (SELECT 'x'
FROM qp_pte_segments qppseg, qp_prc_contexts_b qpcon,
qp_segments_b qpseg, qp_pte_request_types_b qpreq
WHERE qpcon.prc_context_code = l.context
AND qpcon.prc_context_type = l.attribute_type
AND qpseg.prc_context_id = qpcon.prc_context_id
AND qpseg.segment_mapping_column = l.attribute
AND qppseg.segment_id = qpseg.segment_id
AND qpreq.request_type_code = a_request_type_code
AND qppseg.pte_code = qpreq.pte_code
AND qppseg.user_sourcing_method = 'USER ENTERED');
SELECT qpsour.seeded_value_string,
qpsour.user_value_string,
qpreq.order_level_global_struct,
qpreq.line_level_global_struct
FROM
qp_segments_b qpseg,
qp_attribute_sourcing qpsour,
qp_prc_contexts_b qpcon,
qp_pte_request_types_b qpreq,
qp_pte_segments qppseg
WHERE
qpsour.segment_id = qpseg.segment_id
AND qpsour.attribute_sourcing_level = a_sourcing_level
AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
AND qpsour.request_type_code = a_request_type_code
AND qpseg.prc_context_id = qpcon.prc_context_id
AND qpreq.request_type_code = qpsour.request_type_code
AND qppseg.pte_code = qpreq.pte_code
AND qppseg.segment_id = qpsour.segment_id
AND qppseg.sourcing_enabled = 'Y'
AND qpcon.prc_context_type = a_context_type
AND qpcon.prc_context_code = a_context_code
AND qpseg.segment_mapping_column = a_segment_mapping_column
AND rownum = 1;
SELECT price_book_line_id, item_number --inventory_item_id
FROM qp_price_book_lines
WHERE price_book_header_id = a_price_book_header_id;
SELECT line_index, line_unit_price list_price,
order_uom_selling_price net_price, line_id,
pricing_status_code, pricing_status_text
FROM qp_preq_lines_tmp
ORDER BY line_index;
SELECT a.created_from_list_header_id, a.created_from_list_line_id,
a.list_line_no,
decode(pricing_phase_id, 1, b.line_unit_price, a.order_qty_adj_amt)
list_price,
a.order_qty_operand modifier_operand,
a.operand_calculation_code modifier_application_method,
a.order_qty_adj_amt adjustment_amount,
a.created_from_list_line_type, a.pricing_phase_id,
a.price_break_type_code, a.line_index, a.line_detail_index,
b.line_id
FROM qp_preq_ldets_tmp a, qp_preq_lines_tmp b
WHERE a.line_index = b.line_index
AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND a.line_detail_type_code = 'NULL' --not a child line
AND a.applied_flag = 'Y' --automatic and applied discounts
AND nvl(a.accrual_flag,'N') = 'N' --exclude accruals
ORDER BY a.line_index,
decode(pricing_phase_id, 1, 1, 2), --to order pll before modifiers
decode(a.created_from_list_line_type, 'FREIGHT_CHARGE', null,
a.pricing_group_sequence),
decode(a.created_from_list_line_type, 'FREIGHT_CHARGE', 2, 1);
SELECT 1
INTO l_pb_line_count
FROM qp_price_book_lines
WHERE price_book_header_id = p_price_book_header_id
AND rownum = 1;
OPEN insert_lines2_cur(p_price_book_header_id,
p_pb_input_header_rec.effective_date,
p_pb_input_header_rec.item_quantity,
p_pb_input_header_rec.request_type_code,
p_pb_input_header_rec.currency_code,
p_pb_input_header_rec.price_based_on,
p_pb_input_header_rec.pl_agr_bsa_id);
l_line_type_code_tbl.delete;
l_pricing_effective_date_tbl.delete;
l_active_date_first_tbl.delete;
l_active_date_first_type_tbl.delete;
l_active_date_second_tbl.delete;
l_active_date_second_type_tbl.delete;
l_line_quantity_tbl.delete;
l_line_uom_code_tbl.delete;
l_request_type_code_tbl.delete;
l_priced_quantity_tbl.delete;
l_priced_uom_code_tbl.delete;
l_currency_code_tbl.delete;
l_unit_price_tbl.delete;
l_percent_price_tbl.delete;
l_uom_quantity_tbl.delete;
l_adjusted_unit_price_tbl.delete;
l_upd_adjusted_unit_price_tbl.delete;
l_processed_flag_tbl.delete;
l_price_flag_tbl.delete;
l_line_id_tbl.delete;
l_pricing_status_code_tbl.delete;
l_pricing_status_text_tbl.delete;
l_rounding_flag_tbl.delete;
l_rounding_factor_tbl.delete;
l_qualifiers_exist_flag_tbl.delete;
l_pricing_attrs_exist_flag_tbl.delete;
l_price_list_id_tbl.delete;
l_validated_flag_tbl.delete;
l_price_request_code_tbl.delete;
l_usage_pricing_type_tbl.delete;
l_item_number_tbl.delete;
FETCH insert_lines2_cur BULK COLLECT INTO
l_line_type_code_tbl, l_pricing_effective_date_tbl,
l_active_date_first_tbl, l_active_date_first_type_tbl,
l_active_date_second_tbl, l_active_date_second_type_tbl,
l_line_quantity_tbl, l_line_uom_code_tbl, l_request_type_code_tbl,
l_priced_quantity_tbl, l_priced_uom_code_tbl,
l_currency_code_tbl,
l_unit_price_tbl, l_percent_price_tbl, l_uom_quantity_tbl,
l_adjusted_unit_price_tbl, l_upd_adjusted_unit_price_tbl,
l_processed_flag_tbl, l_price_flag_tbl,
l_line_id_tbl, l_processing_order_tbl,
l_pricing_status_code_tbl, l_pricing_status_text_tbl,
l_rounding_flag_tbl, l_rounding_factor_tbl,
l_qualifiers_exist_flag_tbl, l_pricing_attrs_exist_flag_tbl,
l_price_list_id_tbl, l_validated_flag_tbl,
l_price_request_code_tbl, l_usage_pricing_type_tbl,
l_item_number_tbl LIMIT rows;
l_line_index_tbl.delete;
l_count := l_count + insert_lines2_cur%ROWCOUNT; --highest index of the previous loop
fnd_file.put_line(FND_FILE.LOG, 'insert_lines2_cur rowcount = '|| l_count);
QP_PREQ_GRP.INSERT_LINES2(
p_LINE_INDEX => l_line_index_tbl,
p_LINE_TYPE_CODE => l_line_type_code_tbl,
p_PRICING_EFFECTIVE_DATE => l_pricing_effective_date_tbl,
p_ACTIVE_DATE_FIRST => l_active_date_first_tbl,
p_ACTIVE_DATE_FIRST_TYPE => l_active_date_first_type_tbl,
p_ACTIVE_DATE_SECOND => l_active_date_second_tbl,
p_ACTIVE_DATE_SECOND_TYPE => l_active_date_second_type_tbl,
p_LINE_QUANTITY => l_line_quantity_tbl,
p_LINE_UOM_CODE => l_line_uom_code_tbl,
p_REQUEST_TYPE_CODE => l_request_type_code_tbl,
p_PRICED_QUANTITY => l_priced_quantity_tbl,
p_PRICED_UOM_CODE => l_priced_uom_code_tbl,
p_CURRENCY_CODE => l_currency_code_tbl,
p_UNIT_PRICE => l_unit_price_tbl,
p_PERCENT_PRICE => l_percent_price_tbl,
p_UOM_QUANTITY => l_uom_quantity_tbl,
p_ADJUSTED_UNIT_PRICE => l_adjusted_unit_price_tbl,
p_UPD_ADJUSTED_UNIT_PRICE => l_upd_adjusted_unit_price_tbl,
p_PROCESSED_FLAG => l_processed_flag_tbl,
p_PRICE_FLAG => l_price_flag_tbl,
p_LINE_ID => l_line_id_tbl,
p_PROCESSING_ORDER => l_processing_order_tbl,
p_PRICING_STATUS_CODE => l_pricing_status_code_tbl,
p_PRICING_STATUS_TEXT => l_pricing_status_text_tbl,
p_ROUNDING_FLAG => l_rounding_flag_tbl,
p_ROUNDING_FACTOR => l_rounding_factor_tbl,
p_QUALIFIERS_EXIST_FLAG => l_qualifiers_exist_flag_tbl,
p_PRICING_ATTRS_EXIST_FLAG => l_pricing_attrs_exist_flag_tbl,
p_PRICE_LIST_ID => l_price_list_id_tbl,
p_VALIDATED_FLAG => l_validated_flag_tbl,
p_PRICE_REQUEST_CODE => l_price_request_code_tbl,
p_USAGE_PRICING_TYPE => l_usage_pricing_type_tbl,
x_status_code => l_return_status,
x_status_text => l_return_status_text
);
fnd_file.put_line(FND_FILE.LOG, 'insert_lines2 return status '|| l_return_status);
fnd_file.put_line(FND_FILE.LOG, 'insert_lines2 return text'|| l_return_status_text);
l_adhoc_lines_tbl.DELETE;
SELECT price_list_id
INTO l_adhoc_lines_tbl(k).attribute_value
FROM oe_agreements_vl
WHERE agreement_id = p_pb_input_header_rec.pl_agr_bsa_id;
SELECT order_number
INTO l_blanket_number
FROM oe_blanket_headers_all
WHERE header_id = p_pb_input_header_rec.pl_agr_bsa_id;
SELECT price_list_id
INTO l_bsa_hdr_price_list_id
FROM oe_blanket_headers_all
WHERE header_id = p_pb_input_header_rec.pl_agr_bsa_id;
SELECT a.price_list_id
INTO l_bsa_line_price_list_id
FROM oe_blanket_lines_all a, oe_blanket_lines_ext b
WHERE a.header_id = p_pb_input_header_rec.pl_agr_bsa_id
AND a.line_id = b.line_id
AND a.inventory_item_id = l_item_number_tbl(ii)
AND p_pb_input_header_rec.effective_date BETWEEN
nvl(trunc(b.start_date_active),
p_pb_input_header_rec.effective_date) AND
nvl(trunc(b.end_date_active),
p_pb_input_header_rec.effective_date);
OPEN insert_line_attrs2_cur(l_line_index_tbl(ii),
p_pb_input_header_rec.request_type_code,
p_pb_input_header_rec.pb_input_header_id);
l_attrs_line_index_tbl.delete;
l_attrs_line_detail_index_tbl.delete;
l_attrs_attribute_level_tbl.delete;
l_attrs_attribute_type_tbl.delete;
l_attrs_list_header_id_tbl.delete;
l_attrs_list_line_id_tbl.delete;
l_attrs_context_tbl.delete;
l_attrs_attribute_tbl.delete;
l_attrs_value_from_tbl.delete;
l_attrs_setup_value_from_tbl.delete;
l_attrs_value_to_tbl.delete;
l_attrs_setup_value_to_tbl.delete;
l_attrs_grouping_number_tbl.delete;
l_attrs_no_quals_in_grp_tbl.delete;
l_attrs_comp_oper_type_tbl.delete;
l_attrs_validated_flag_tbl.delete;
l_attrs_applied_flag_tbl.delete;
l_attrs_pri_status_code_tbl.delete;
l_attrs_pri_status_text_tbl.delete;
l_attrs_qual_precedence_tbl.delete;
l_attrs_datatype_tbl.delete;
l_attrs_pricing_attr_flag_tbl.delete;
l_attrs_qualifier_type_tbl.delete;
l_attrs_product_uom_code_tbl.delete;
l_attrs_excluder_flag_tbl.delete;
l_attrs_pricing_phase_id_tbl.delete;
l_attrs_incomp_grp_code_tbl.delete;
l_attrs_line_det_typ_code_tbl.delete;
l_attrs_modif_level_code_tbl.delete;
l_attrs_primary_uom_flag_tbl.delete;
FETCH insert_line_attrs2_cur BULK COLLECT INTO
l_attrs_line_index_tbl, l_attrs_line_detail_index_tbl,
l_attrs_attribute_level_tbl, l_attrs_attribute_type_tbl,
l_attrs_list_header_id_tbl, l_attrs_list_line_id_tbl,
l_attrs_context_tbl, l_attrs_attribute_tbl,
l_attrs_value_from_tbl, l_attrs_setup_value_from_tbl,
l_attrs_value_to_tbl, l_attrs_setup_value_to_tbl,
l_attrs_grouping_number_tbl, l_attrs_no_quals_in_grp_tbl,
l_attrs_comp_oper_type_tbl, l_attrs_validated_flag_tbl,
l_attrs_applied_flag_tbl, l_attrs_pri_status_code_tbl,
l_attrs_pri_status_text_tbl, l_attrs_qual_precedence_tbl,
l_attrs_datatype_tbl, l_attrs_pricing_attr_flag_tbl,
l_attrs_qualifier_type_tbl, l_attrs_product_uom_code_tbl,
l_attrs_excluder_flag_tbl, l_attrs_pricing_phase_id_tbl,
l_attrs_incomp_grp_code_tbl, l_attrs_line_det_typ_code_tbl,
l_attrs_modif_level_code_tbl, l_attrs_primary_uom_flag_tbl
LIMIT rows;
QP_PREQ_GRP.INSERT_LINE_ATTRS2(
p_LINE_INDEX_tbl => l_attrs_line_index_tbl,
p_LINE_DETAIL_INDEX_tbl => l_attrs_line_detail_index_tbl,
p_ATTRIBUTE_LEVEL_tbl => l_attrs_attribute_level_tbl,
p_ATTRIBUTE_TYPE_tbl => l_attrs_attribute_type_tbl,
p_LIST_HEADER_ID_tbl => l_attrs_list_header_id_tbl,
p_LIST_LINE_ID_tbl => l_attrs_list_line_id_tbl,
p_CONTEXT_tbl => l_attrs_context_tbl,
p_ATTRIBUTE_tbl => l_attrs_attribute_tbl,
p_VALUE_FROM_tbl => l_attrs_value_from_tbl,
p_SETUP_VALUE_FROM_tbl => l_attrs_setup_value_from_tbl,
p_VALUE_TO_tbl => l_attrs_value_to_tbl,
p_SETUP_VALUE_TO_tbl => l_attrs_setup_value_to_tbl,
p_GROUPING_NUMBER_tbl => l_attrs_grouping_number_tbl,
p_NO_QUALIFIERS_IN_GRP_tbl => l_attrs_no_quals_in_grp_tbl,
p_COMPARISON_OPERATOR_TYPE_tbl => l_attrs_comp_oper_type_tbl,
p_VALIDATED_FLAG_tbl => l_attrs_validated_flag_tbl,
p_APPLIED_FLAG_tbl => l_attrs_applied_flag_tbl,
p_PRICING_STATUS_CODE_tbl => l_attrs_pri_status_code_tbl,
p_PRICING_STATUS_TEXT_tbl => l_attrs_pri_status_text_tbl,
p_QUALIFIER_PRECEDENCE_tbl => l_attrs_qual_precedence_tbl,
p_DATATYPE_tbl => l_attrs_datatype_tbl,
p_PRICING_ATTR_FLAG_tbl => l_attrs_pricing_attr_flag_tbl,
p_QUALIFIER_TYPE_tbl => l_attrs_qualifier_type_tbl,
p_PRODUCT_UOM_CODE_TBL => l_attrs_product_uom_code_tbl,
p_EXCLUDER_FLAG_TBL => l_attrs_excluder_flag_tbl,
p_PRICING_PHASE_ID_TBL => l_attrs_pricing_phase_id_tbl,
p_INCOMPATABILITY_GRP_CODE_TBL => l_attrs_incomp_grp_code_tbl,
p_LINE_DETAIL_TYPE_CODE_TBL => l_attrs_line_det_typ_code_tbl,
p_MODIFIER_LEVEL_CODE_TBL => l_attrs_modif_level_code_tbl,
p_PRIMARY_UOM_FLAG_TBL => l_attrs_primary_uom_flag_tbl,
x_status_code => l_return_status,
x_status_text => l_return_status_text
);
fnd_file.put_line(FND_FILE.LOG, 'insert_line_attrs2 return status '||l_return_status);
fnd_file.put_line(FND_FILE.LOG, 'insert_line_attrs2 return text'||l_return_status_text);
EXIT WHEN insert_line_attrs2_cur%NOTFOUND;
END LOOP; --Loop over cursor insert_line_attrs2_cur
CLOSE insert_line_attrs2_cur;
l_control_rec.temp_table_insert_flag := 'N';
l_line_index_tbl.delete;
l_line_id_tbl.delete;
l_list_price_tbl.delete;
l_net_price_tbl.delete;
l_pricing_status_code_tbl.delete;
l_pricing_status_text_tbl.delete;
UPDATE qp_price_book_lines
SET list_price = l_list_price_tbl(i),
net_price = l_net_price_tbl(i)
WHERE price_book_line_id = l_line_id_tbl(i);
IF l_pricing_status_code_tbl(i) NOT IN ('UPDATED', 'UNCHANGED') THEN
--Exclude informational messages
INSERT INTO qp_price_book_messages
(message_id, message_type, message_code, message_text,
pb_input_header_id, price_book_header_id, price_book_line_id,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login
)
VALUES
(qp_price_book_messages_s.nextval, 'E', l_pricing_status_code_tbl(i),
l_pricing_status_text_tbl(i), null, p_price_book_header_id,
l_line_id_tbl(i), sysdate, l_user_id, sysdate, l_user_id,
l_login_id
);
l_line_id_tbl.delete;
l_item_number_tbl.delete;
INSERT INTO qp_price_book_attributes
(price_book_attribute_id, price_book_line_det_id,
price_book_line_id, price_book_header_id,
pricing_prod_context, pricing_prod_attribute,
comparison_operator_code, pricing_prod_attr_value_from,
pricing_attr_value_to, pricing_prod_attr_datatype,
attribute_type, creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)
SELECT qp_price_book_attributes_s.nextval, -1,
l_line_id_tbl(i), p_price_book_header_id,
'ITEM', 'PRICING_ATTRIBUTE2', --Item Category
'=', category_id, null, 'N',
'PRODUCT', sysdate, l_user_id, sysdate,
l_user_id, l_login_id
FROM (SELECT DISTINCT a.category_id
FROM mtl_item_categories a, mtl_categories_b b,
mtl_category_sets_b c, mtl_default_category_sets d
WHERE a.inventory_item_id = l_item_number_tbl(i)
AND a.organization_id = l_inv_org_id --inventory org, not OU
AND b.category_id = a.category_id
AND c.structure_id = b.structure_id
AND d.category_set_id = c.category_set_id
AND d.functional_area_id IN
(SELECT ssf.functional_area_id
FROM qp_pte_source_systems pss,
qp_pte_request_types_b prt,
qp_sourcesystem_fnarea_map ssf
WHERE pss.pte_code = prt.pte_code
AND pss.enabled_flag = 'Y'
AND prt.enabled_flag = 'Y'
AND ssf.enabled_flag = 'Y'
AND prt.request_type_code = p_pb_input_header_rec.request_type_code
AND pss.pte_source_system_id = ssf.pte_source_system_id)
);
l_cf_list_header_id_tbl.delete;
l_cf_list_line_id_tbl.delete;
l_list_line_no_tbl.delete;
l_list_price_tbl.delete;
l_modifier_operand_tbl.delete;
l_modifier_appl_method_tbl.delete;
l_adjustment_amount_tbl.delete;
l_list_line_type_code_tbl.delete;
l_price_break_type_code_tbl.delete;
l_line_index_tbl.delete;
l_line_detail_index_tbl.delete;
l_line_id_tbl.delete;
l_pricing_phase_id_tbl.delete;
l_pb_line_det_id_tbl.delete;
l_pb_line_id_tbl.delete;
l_line_index2_tbl.delete;
l_line_detail_index2_tbl.delete;
l_list_line_type_code2_tbl.delete;
INSERT INTO qp_price_book_line_details
(price_book_line_det_id, price_book_line_id,
price_book_header_id,
list_header_id, list_line_id,
list_line_no, list_price,
modifier_operand, modifier_application_method, adjustment_amount,
adjusted_net_price, list_line_type_code,
price_break_type_code,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)
VALUES
(qp_price_book_line_details_s.nextval, l_line_id_tbl(j),
p_price_book_header_id,
l_cf_list_header_id_tbl(j), l_cf_list_line_id_tbl(j),
decode(l_pricing_phase_id_tbl(j), 1, null, l_list_line_no_tbl(j)),
--insert null for list_line_no in case of PLL, PBH price list line
decode(l_list_price_tbl(j), l_adjustment_amount_tbl(j), null,
l_list_price_tbl(j)),
l_modifier_operand_tbl(j), l_modifier_appl_method_tbl(j),
l_adjustment_amount_tbl(j), null,
l_list_line_type_code_tbl(j), l_price_break_type_code_tbl(j),
sysdate, l_user_id, sysdate, l_user_id, l_login_id)
RETURNING price_book_line_det_id, price_book_line_id, l_line_index_tbl(j),
l_line_detail_index_tbl(j), l_list_line_type_code_tbl(j)
BULK COLLECT INTO l_pb_line_det_id_tbl, l_pb_line_id_tbl,
l_line_index2_tbl, l_line_detail_index2_tbl, l_list_line_type_code2_tbl;
INSERT INTO qp_price_book_attributes
(price_book_attribute_id, price_book_line_det_id,
price_book_line_id, price_book_header_id,
pricing_prod_context, pricing_prod_attribute,
comparison_operator_code, pricing_prod_attr_value_from,
pricing_attr_value_to, pricing_prod_attr_datatype,
attribute_type, creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)
SELECT qp_price_book_attributes_s.nextval, a.pb_line_det_id,
a.pb_line_id, a.price_book_header_id,
a.context, a.attribute,
a.comparison_operator_type_code, a.setup_value_from,
a.setup_value_to, a.datatype, a.attribute_type,
sysdate, l_user_id, sysdate,
l_user_id, l_login_id
FROM (SELECT DISTINCT l_pb_line_det_id_tbl(k) pb_line_det_id,
l_pb_line_id_tbl(k) pb_line_id,
p_price_book_header_id price_book_header_id,
a.context, a.attribute,
a.comparison_operator_type_code, a.setup_value_from,
a.setup_value_to, a.datatype,
decode(a.attribute_type, QP_PREQ_GRP.G_PRICING_TYPE,
'PRICING_ATTRIBUTE', a.attribute_type) attribute_type
FROM qp_preq_line_attrs_tmp a, qp_list_headers_vl b
WHERE a.line_index = l_line_index2_tbl(k)
AND a.line_detail_index = l_line_detail_index2_tbl(k)
AND a.list_header_id = b.list_header_id
AND b.list_type_code = 'PRL'
AND a.attribute_type = QP_PREQ_GRP.G_PRICING_TYPE
--Only pricing attributes
AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW) a;
INSERT INTO qp_price_book_break_lines
(price_book_break_line_id, price_book_line_det_id,
price_book_line_id, price_book_header_id,
pricing_context, pricing_attribute, pricing_attr_value_from,
pricing_attr_value_to, comparison_operator_code,
pricing_attribute_datatype, operand, application_method,
recurring_value,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login)
SELECT /*+ ORDERED index(a QP_PREQ_LINE_ATTRS_TMP_N3) */ qp_price_book_break_lines_s.nextval, l_pb_line_det_id_tbl(k),
l_pb_line_id_tbl(k), p_price_book_header_id,
a.context, a.attribute, a.value_from,
a.value_to, a.comparison_operator_type_code,
a.datatype, b.operand_value, b.operand_calculation_code,
b.recurring_value,
sysdate, l_user_id, sysdate, l_user_id, l_login_id
FROM qp_preq_rltd_lines_tmp r, qp_preq_ldets_tmp b, qp_preq_line_attrs_tmp a
WHERE l_list_line_type_code2_tbl(k) = 'PBH'
AND r.line_index = l_line_index2_tbl(k)
AND r.line_detail_index = l_line_detail_index2_tbl(k)
AND r.relationship_type_code = QP_PREQ_GRP.G_PBH_LINE -- just in case db goes to table before first condition
AND b.line_detail_index = r.related_line_detail_index
AND a.line_detail_index = b.line_detail_index;
EXIT WHEN insert_lines2_cur%NOTFOUND;
END LOOP; --Loop over cursor insert_lines2_cur
CLOSE insert_lines2_cur;
END Insert_Price_Book_Content;
UPDATE qp_pb_input_headers_b
SET request_id = x_request_id
WHERE pb_input_header_id = p_pb_input_header_id;
SELECT DISTINCT product_attr_value item_id
--Since an item can occur multiple times in a pl if attributes differ
FROM qp_pricing_attributes
WHERE list_header_id = a_price_list_id
AND product_attribute = 'PRICING_ATTRIBUTE1' --Item Number
AND product_attribute_context = 'ITEM';
SELECT DISTINCT cat.inventory_item_id item_id
FROM mtl_item_categories cat
WHERE cat.organization_id = a_organization_id
AND (cat.category_id = a_category_id
OR
EXISTS (SELECT 'Y'
FROM eni_denorm_hierarchies
WHERE parent_id = a_category_id and
child_id = cat.category_id)
);
SELECT msi.inventory_item_id item_id
FROM mtl_system_items_b msi
WHERE msi.organization_id = a_organization_id
AND msi.purchasing_enabled_flag = decode(a_pricing_perspective,
'PO', 'Y', msi.purchasing_enabled_flag)
AND EXISTS (SELECT 'X'
FROM mtl_item_categories mic
WHERE inventory_item_id = msi.inventory_item_id
AND organization_id = msi.organization_id);
SELECT msi.inventory_item_id item_id, msi.primary_uom_code uom_code
FROM mtl_system_items msi, mtl_units_of_measure muom
WHERE msi.organization_id = a_organization_id
AND msi.inventory_item_id = a_item_id
AND muom.uom_code = msi.primary_uom_code
AND nvl(muom.disable_date, trunc(a_effective_date) + 1) >
trunc(a_effective_date)
UNION
SELECT a_item_id item_id, muom.uom_code uom_code
FROM mtl_system_items msi2,
mtl_units_of_measure muom2,
mtl_uom_conversions mcon,
mtl_units_of_measure muom,
mtl_uom_classes mcl
WHERE muom2.uom_code = msi2.primary_uom_code
and msi2.organization_id = a_organization_id
and msi2.inventory_item_id = a_item_id
and nvl(muom2.disable_date,
trunc(a_effective_date) + 1) >
trunc(a_effective_date)
AND mcon.uom_class = muom2.uom_class
AND mcon.inventory_item_id = 0
AND mcon.uom_code = muom.uom_code
AND nvl(mcon.disable_date,trunc(a_effective_date)+1) >
trunc(a_effective_date)
AND mcl.uom_class = muom.uom_class
AND nvl(mcl.disable_date,trunc(a_effective_date)+1) >
trunc(a_effective_date)
AND nvl(muom.disable_date,trunc(a_effective_date)+1) >
trunc(a_effective_date)
AND EXISTS (
SELECT 'x'
FROM qp_pricing_attributes pa
WHERE pa.product_attribute_context = 'ITEM'
AND pa.product_attribute = 'PRICING_ATTRIBUTE1'
AND pa.product_attr_value = To_Char(a_item_id)
AND pa.product_uom_code = muom.uom_code
AND pa.qualification_ind IN (4,6,20,22)
AND pa.pricing_phase_id = 1
)
UNION
SELECT a_item_id item_id, muom.uom_code uom_code
FROM mtl_units_of_measure muom,
mtl_uom_conversions mcon,
mtl_uom_classes mcl
WHERE mcon.uom_code = muom.uom_code
AND mcon.inventory_item_id = a_item_id
AND mcl.uom_class = muom.uom_class
AND nvl(mcl.disable_date,trunc(a_effective_date)+1) >
trunc(a_effective_date)
AND nvl(muom.disable_date,trunc(a_effective_date)+1) >
trunc(a_effective_date)
AND nvl(mcon.disable_date,trunc(a_effective_date)+1) >
trunc(a_effective_date)
--bug 8970504
UNION
SELECT a_item_id item_id, a.product_uom_code
FROM qp_list_lines_v a,
mtl_system_items msi,
mtl_units_of_measure muom,
mtl_uom_classes mcl
WHERE a.product_attribute_context = 'ITEM'
and a.product_attribute = 'PRICING_ATTRIBUTE1'
and a.product_attr_value = to_char(a_item_id)
AND a.pricing_attribute_context IS NULL
AND a.pricing_phase_id = 1
AND msi.inventory_item_id = a.product_attr_value
AND msi.organization_id = a_organization_id
AND muom.uom_code = a.product_uom_code
AND nvl(muom.disable_date,trunc(NVL(a_effective_date,sysdate))+1) >
trunc(NVL(a_effective_date,sysdate))
AND mcl.uom_class = muom.uom_class
AND nvl(mcl.disable_date,trunc(NVL(a_effective_date,sysdate))+1) >
trunc(NVL(a_effective_date,sysdate))
AND Trunc(NVL(a_effective_date,sysdate))
BETWEEN TRUNC(Nvl(a.start_date_active,NVL(a_effective_date,sysdate)))
AND TRUNC(Nvl(a.end_date_active,NVL(a_effective_date,sysdate)));
SELECT price_book_line_id, list_price
FROM qp_price_book_lines
WHERE price_book_header_id = p_price_book_header_id
ORDER BY price_book_line_id;
SELECT *
INTO l_pb_input_header_rec
FROM qp_pb_input_headers_vl
WHERE pb_input_header_id = p_pb_input_header_id;
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
SELECT price_book_header_id
INTO l_publish_price_book_header_id
FROM qp_price_book_headers_vl
WHERE customer_id = l_pb_input_header_rec.customer_attr_value
AND price_book_type_code =
l_pb_input_header_rec.price_book_type_code
AND price_book_name = l_pb_input_header_rec.price_book_name;
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit;--inserted message with pb_input_header_id
SELECT price_book_header_id, request_id
INTO l_overwrite_pb_header_id, l_old_request_id
FROM qp_price_book_headers_vl
WHERE price_book_name = l_pb_input_header_rec.price_book_name
AND price_book_type_code =
l_pb_input_header_rec.price_book_type_code
AND customer_id = l_pb_input_header_rec.customer_attr_value;
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit; --inserted message with pb_input_header_id
DELETE FROM qp_price_book_headers_b
WHERE price_book_header_id = l_overwrite_pb_header_id
RETURNING pb_input_header_id, document_id
INTO l_old_input_header_id, l_document_id;
DELETE FROM qp_price_book_headers_tl
WHERE price_book_header_id = l_overwrite_pb_header_id;
DELETE FROM qp_price_book_lines
WHERE price_book_header_id = l_overwrite_pb_header_id;
DELETE FROM qp_price_book_line_details
WHERE price_book_header_id = l_overwrite_pb_header_id;
DELETE FROM qp_price_book_attributes
WHERE price_book_header_id = l_overwrite_pb_header_id;
DELETE FROM qp_price_book_break_lines
WHERE price_book_header_id = l_overwrite_pb_header_id;
DELETE FROM qp_price_book_messages
WHERE price_book_header_id = l_overwrite_pb_header_id;
DELETE FROM qp_documents
WHERE document_id = l_document_id;
DELETE FROM qp_price_book_messages
WHERE pb_input_header_id = l_old_input_header_id;
DELETE FROM qp_pb_input_headers_b
WHERE pb_input_header_id = l_old_input_header_id;
DELETE FROM qp_pb_input_headers_tl
WHERE pb_input_header_id = l_old_input_header_id;
DELETE FROM qp_pb_input_lines
WHERE pb_input_header_id = l_old_input_header_id;
SELECT price_book_header_id, request_id
INTO l_corr_delta_pb_header_id, l_old_delta_request_id
FROM qp_price_book_headers_vl
WHERE price_book_name = l_pb_input_header_rec.price_book_name
AND price_book_type_code = 'D'
AND customer_id = l_pb_input_header_rec.customer_attr_value;
rollback; --Delete stmts on corresponding Full Price Book data
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit; --inserted message with pb_input_header_id
DELETE FROM qp_price_book_headers_b
WHERE price_book_header_id = l_corr_delta_pb_header_id
RETURNING pb_input_header_id, document_id
INTO l_delta_input_header_id, l_delta_document_id;
DELETE FROM qp_price_book_headers_tl
WHERE price_book_header_id = l_corr_delta_pb_header_id;
DELETE FROM qp_price_book_lines
WHERE price_book_header_id = l_corr_delta_pb_header_id;
DELETE FROM qp_price_book_line_details
WHERE price_book_header_id = l_corr_delta_pb_header_id;
DELETE FROM qp_price_book_attributes
WHERE price_book_header_id = l_corr_delta_pb_header_id;
DELETE FROM qp_price_book_break_lines
WHERE price_book_header_id = l_corr_delta_pb_header_id;
DELETE FROM qp_price_book_messages
WHERE price_book_header_id = l_corr_delta_pb_header_id;
DELETE FROM qp_documents
WHERE document_id = l_delta_document_id;
DELETE FROM qp_price_book_messages
WHERE pb_input_header_id = l_delta_input_header_id;
DELETE FROM qp_pb_input_headers_b
WHERE pb_input_header_id = l_delta_input_header_id;
DELETE FROM qp_pb_input_headers_tl
WHERE pb_input_header_id = l_delta_input_header_id;
DELETE FROM qp_pb_input_lines
WHERE pb_input_header_id = l_delta_input_header_id;
fnd_file.put_line(FND_FILE.LOG, 'Before inserting price book header');
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Header(
p_pb_input_header_rec => l_pb_input_header_rec,
x_price_book_header_id => l_publish_price_book_header_id);
fnd_file.put_line(FND_FILE.LOG, 'After inserting price book header'||
to_char(l_publish_price_book_header_id));
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit; --inserted message with pb_input_header_id
'INSERT_PRICE_BOOK_HEADER_ERROR';
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit; --inserted message with pb_input_header_id
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Header(
p_pb_input_header_rec => l_pb_input_header_rec,
x_price_book_header_id => l_publish_price_book_header_id);
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit; --inserted message with pb_input_header_id
'INSERT_PRICE_BOOK_HEADER_ERROR';
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit; --inserted message with pb_input_header_id
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
commit; --inserted message with pb_input_header_id
l_item_tbl.delete;
l_item2_tbl.delete;
l_uom_tbl.delete;
INSERT INTO qp_price_book_lines
(price_book_line_id,
price_book_header_id,
item_number,
product_uom_code,
sync_action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(qp_price_book_lines_s.nextval,
l_publish_price_book_header_id,
l_item2_tbl(j),
l_uom_tbl(j),
'R',
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id
);
l_item2_tbl.delete;
l_uom_tbl.delete;
INSERT INTO qp_price_book_lines
(price_book_line_id,
price_book_header_id,
item_number,
product_uom_code,
sync_action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(qp_price_book_lines_s.nextval,
l_publish_price_book_header_id,
l_item2_tbl(j),
l_uom_tbl(j),
'R',
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id
);
l_item_tbl.delete;
l_item2_tbl.delete;
l_uom_tbl.delete;
INSERT INTO qp_price_book_lines
(price_book_line_id,
price_book_header_id,
item_number,
product_uom_code,
sync_action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(qp_price_book_lines_s.nextval,
l_publish_price_book_header_id,
l_item2_tbl(j),
l_uom_tbl(j),
'R',
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id
);
l_item_tbl.delete;
l_item2_tbl.delete;
l_uom_tbl.delete;
INSERT INTO qp_price_book_lines
(price_book_line_id,
price_book_header_id,
item_number,
product_uom_code,
sync_action_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(qp_price_book_lines_s.nextval,
l_publish_price_book_header_id,
l_item2_tbl(j),
l_uom_tbl(j),
'R',
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id
);
UPDATE qp_price_book_headers_b
SET request_id = l_child_request_id
WHERE price_book_header_id = l_publish_price_book_header_id;
SELECT * BULK COLLECT
INTO l_pb_input_lines_tbl
FROM qp_pb_input_lines
WHERE pb_input_header_id = p_pb_input_header_id;
fnd_file.put_line(FND_FILE.LOG, 'Before Insert_Price_Book_Content ');
Insert_Price_Book_Content(
p_pb_input_header_rec => l_pb_input_header_rec,
p_pb_input_lines_tbl => l_pb_input_lines_tbl,
p_price_book_header_id => p_price_book_header_id);
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
fnd_file.put_line(FND_FILE.LOG, 'After Insert_Price_Book_Content ');
l_line_id_tbl.delete;
l_list_price_tbl.delete;
l_line_det_id_tbl2.delete;
l_net_price_tbl.delete;
l_line_id_tbl2.delete;
l_line_det_id_tbl.delete;
l_adjustment_amount_tbl.delete;
SELECT /*+ index(qpbdtls qp_price_book_line_details_n1) */
qpbdtls.price_book_line_det_id, qpbdtls.adjustment_amount --bug 8933586
BULK COLLECT INTO l_line_det_id_tbl, l_adjustment_amount_tbl
FROM qp_price_book_line_details qpbdtls
WHERE qpbdtls.price_book_line_id = l_line_id_tbl(i)
ORDER BY qpbdtls.price_book_line_det_id;
UPDATE qp_price_book_line_details
SET adjusted_net_price = l_net_price_tbl(m)
WHERE price_book_line_det_id = l_line_det_id_tbl2(m);
UPDATE qp_price_book_lines
SET net_price = l_net_price_tbl(m)
WHERE price_book_line_id = l_line_id_tbl2(m);
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
DELETE FROM qp_price_book_messages
WHERE price_book_header_id = p_price_book_header_id
AND message_code like 'PUB_%';
SELECT document_id
INTO l_document_id
FROM qp_price_book_headers_all_b
WHERE price_book_header_id = p_price_book_header_id;
UPDATE qp_price_book_headers_all_b
SET document_id = null
WHERE price_book_header_id = p_price_book_header_id;
DELETE FROM qp_documents
WHERE document_id = l_document_id;
SELECT PUB_OUTPUT_DOCUMENT_TYPE
INTO l_document_type
FROM QP_PB_INPUT_HEADERS_B
WHERE pb_input_header_id = p_pb_input_header_id;
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;
QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(l_price_book_messages_tbl);
l_price_book_messages_tbl.delete;