[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT to_rltd_modifier_id
FROM qp_rltd_modifiers
WHERE to_rltd_modifier_id = a_list_line_id;
/* Procedure to Delete Duplicate Lines potentially created effective */
/* dates not retained while copying lines from one price list to */
/* another. */
/***********************************************************************/
PROCEDURE Delete_Duplicate_Lines (p_effective_dates_flag VARCHAR2,
p_new_list_header_id NUMBER)
IS
/* Commented out bu dhgupta for bug 2100785 */
/*
CURSOR del_dup_cur (a_new_list_header_id NUMBER)
IS
SELECT *
FROM qp_list_lines a
WHERE EXISTS (SELECT NULL
FROM qp_list_lines b
WHERE a.inventory_item_id = b.inventory_item_id
AND a.list_line_type_code = b.list_line_type_code
AND a.list_header_id = b.list_header_id
AND a.list_header_id = a_new_list_header_id
AND a.list_line_id < b.list_line_id
AND nvl(a.automatic_flag,'x') = nvl(b.automatic_flag,'x')
AND nvl(a.modifier_level_code,'x') =
nvl(b.modifier_level_code,'x')
AND nvl(a.list_price,-1) = nvl(b.list_price,-1)
AND nvl(a.primary_uom_flag,'x') =
nvl(b.primary_uom_flag,'x')
AND nvl(a.organization_id,-1) = nvl(b.organization_id,-1)
AND nvl(a.related_item_id,-1) = nvl(b.related_item_id,-1)
AND nvl(a.relationship_type_id,-1) =
nvl(b.relationship_type_id,-1)
AND nvl(a.substitution_context,'x') =
nvl(b.substitution_context,'x')
AND nvl(a.substitution_attribute,'x') =
nvl(b.substitution_attribute,'x')
AND nvl(a.substitution_value,'x') =
nvl(b.substitution_value,'x')
AND nvl(a.context,'x') = nvl(b.context,'x')
AND nvl(a.attribute1,'x') = nvl(b.attribute1, 'x')
AND nvl(a.attribute2,'x') = nvl(b.attribute2, 'x')
AND nvl(a.comments,'x') = nvl(b.comments,'x')
AND nvl(a.attribute3,'x') = nvl(b.attribute3,'x')
AND nvl(a.attribute4,'x') = nvl(b.attribute4,'x')
AND nvl(a.attribute5,'x') = nvl(b.attribute5,'x')
AND nvl(a.attribute6,'x') = nvl(b.attribute6,'x')
AND nvl(a.attribute7,'x') = nvl(b.attribute7,'x')
AND nvl(a.attribute8,'x') = nvl(b.attribute8,'x')
AND nvl(a.attribute9,'x') = nvl(b.attribute9,'x')
AND nvl(a.attribute10,'x') = nvl(b.attribute10,'x')
AND nvl(a.attribute11,'x') = nvl(b.attribute11,'x')
AND nvl(a.attribute12,'x') = nvl(b.attribute12,'x')
AND nvl(a.attribute13,'x') = nvl(b.attribute13,'x')
AND nvl(a.attribute14,'x') = nvl(b.attribute14,'x')
AND nvl(a.attribute15,'x') = nvl(b.attribute15,'x')
AND nvl(a.price_break_type_code,'x') =
nvl(b.price_break_type_code,'x')
AND nvl(a.percent_price,-1) = nvl(b.percent_price,-1)
AND nvl(a.price_by_formula_id,-1) =
nvl(b.price_by_formula_id,-1)
AND nvl(a.number_effective_periods,-1) =
nvl(b.number_effective_periods,-1)
AND nvl(a.effective_period_uom,'x') =
nvl(b.effective_period_uom,'x')
AND nvl(a.arithmetic_operator,'x') =
nvl(b.arithmetic_operator,'x')
AND nvl(a.operand,-1) = nvl(b.operand,-1)
AND nvl(a.override_flag,'x') = nvl(b.override_flag,'x')
AND nvl(a.print_on_invoice_flag,'x') =
nvl(b.print_on_invoice_flag,'x')
AND nvl(a.rebate_transaction_type_code,'x') =
nvl(b.rebate_transaction_type_code,'x')
AND nvl(a.estim_accrual_rate,-1) =
nvl(b.estim_accrual_rate,-1)
AND nvl(a.generate_using_formula_id,-1) =
nvl(b.generate_using_formula_id,-1)
AND nvl(a.reprice_flag,'x') = nvl(b.reprice_flag,'x')
AND nvl(a.accrual_flag, 'x') = nvl(b.accrual_flag, 'x')
AND nvl(a.pricing_group_sequence, -1) =
nvl(b.pricing_group_sequence, -1)
AND nvl(a.incompatibility_grp_code, 'x') =
nvl(b.incompatibility_grp_code, 'x')
AND nvl(a.list_line_no, 'x') = nvl(b.list_line_no, 'x')
AND nvl(a.product_precedence, -1) =
nvl(b.product_precedence, -1)
AND nvl(a.pricing_phase_id, -1) = nvl(b.pricing_phase_id, -1)
AND nvl(a.number_expiration_periods, -1) =
nvl(b.number_expiration_periods, -1)
AND nvl(a.expiration_period_uom, 'x') =
nvl(b.expiration_period_uom, 'x')
AND nvl(a.estim_gl_value, -1) = nvl(b.estim_gl_value, -1)
AND nvl(a.accrual_conversion_rate, -1) =
nvl(b.accrual_conversion_rate, -1)
AND nvl(a.benefit_price_list_line_id, -1) =
nvl(b.benefit_price_list_line_id, -1)
AND nvl(a.proration_type_code, 'x') =
nvl(b.proration_type_code, 'x')
AND nvl(a.benefit_qty, -1) = nvl(b.benefit_qty, -1)
AND nvl(a.benefit_uom_code, 'x') = nvl(b.benefit_uom_code, 'x')
AND nvl(a.charge_type_code, 'x') = nvl(b.charge_type_code, 'x')
AND nvl(a.charge_subtype_code, 'x') =
nvl(b.charge_subtype_code, 'x')
AND nvl(a.benefit_limit, -1) = nvl(b.benefit_limit, -1)
AND nvl(a.include_on_returns_flag, 'x') =
nvl(b.include_on_returns_flag, 'x')
AND nvl(a.qualification_ind, -1) = nvl(b.qualification_ind, -1)
) FOR UPDATE;
SELECT list_line_id
FROM qp_list_lines
WHERE list_header_id=a_new_list_header_id;
l_PRICE_LIST_LINE_tbl(K).operation := QP_GLOBALS.G_OPR_DELETE;
DELETE qp_pricing_attributes pa
WHERE pa.list_line_id = l_list_lines_id.list_line_id;
DELETE qp_list_lines
WHERE list_line_id = l_list_lines_id.list_line_id;
DELETE qp_pricing_attributes pa
WHERE pa.list_line_id = l_del_dup_cur_rec.list_line_id;
DELETE qp_list_lines
WHERE CURRENT OF del_dup_cur;
END Delete_Duplicate_Lines;
SELECT list_header_id
FROM qp_qualifiers a
WHERE a.qualifier_context = p_context
AND a.qualifier_attribute = p_attribute
AND a.qualifier_attr_value = TO_CHAR(p_from_list_header_id)
AND a.list_header_id IN
(SELECT list_header_id
--fix for bug 4673872
FROM qp_list_headers_all_b
WHERE list_type_code = 'DLT');
SELECT *
FROM qp_qualifiers
WHERE list_header_id = p_from_discount_header_id
AND list_line_id = -1;
SELECT *
FROM qp_qualifiers
WHERE list_header_id = p_from_discount_header_id
AND list_line_id <> -1;
SELECT *
FROM qp_list_lines
WHERE list_header_id = p_from_discount_header_id;
SELECT *
FROM qp_pricing_attributes
WHERE list_line_id = p_from_discount_line_id;
SELECT *
FROM qp_rltd_modifiers
WHERE from_rltd_modifier_id = a_list_line_id;
SELECT name, description, version_no
FROM qp_list_headers_tl
WHERE list_header_id = a_list_hdr_id;
SELECT qp_list_headers_b_s.nextval
INTO l_new_discount_header_id
FROM dual;
INSERT INTO qp_list_headers_all_b
(
list_header_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
list_type_code,
start_date_active,
end_date_active,
automatic_flag,
-- exclusive_flag,
currency_code,
rounding_factor,
ship_method_code,
freight_terms_code,
terms_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
comments,
discount_lines_flag,
gsa_indicator,
prorate_flag,
source_system_code,
active_flag,
parent_list_header_id,
start_date_active_first,
end_date_active_first,
active_date_first_type,
start_date_active_second,
end_date_active_second,
active_date_second_type,
ask_for_flag,
currency_header_id, -- Multi-Currency SunilPandey
pte_code -- Attribute Manager, Giri
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYSTEM_HEADER_REF
)
SELECT
l_new_discount_header_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_conc_login_id,
p_conc_program_application_id,
p_conc_program_id,
sysdate,
p_conc_request_id,
list_type_code,
start_date_active,
end_date_active,
automatic_flag,
-- exclusive_flag,
currency_code,
rounding_factor,
ship_method_code,
freight_terms_code,
terms_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
comments,
discount_lines_flag,
gsa_indicator,
prorate_flag,
source_system_code,
active_flag,
parent_list_header_id,
start_date_active_first,
end_date_active_first,
active_date_first_type,
start_date_active_second,
end_date_active_second,
active_date_second_type,
ask_for_flag,
currency_header_id, -- Multi-Currency SunilPandey
pte_code -- Attribute Manager, Giri
--ENH Upgrade BOAPI for orig_sys...ref RAVI
--,nvl(ORIG_SYSTEM_HEADER_REF,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(l_new_discount_header_id))
-- Bug 5201918
--bug#6636843. Moving this function call to before the query and storing the value in a
--variable and then using it.
--,QP_PRICE_LIST_UTIL.Get_Orig_Sys_Hdr(l_new_discount_header_id)
,l_new_orig_system_hrd_Ref
--fix for bug 4673872
FROM qp_list_headers_b
WHERE list_header_id = qp_from_discounts_rec.list_header_id;
INSERT INTO qp_list_headers_tl
(last_update_login,
name,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
list_header_id,
language,
source_lang,
version_no
)
SELECT
p_conc_login_id,
--l_name || to_char(l_new_discount_header_id),
l_qp_list_headers_tl_rec.name || to_char(l_new_discount_header_id), --bug#6636843.
--l_description,
l_qp_list_headers_tl_rec.description, --bug#6636843.
sysdate,
p_user_id,
sysdate,
p_user_id,
l_new_discount_header_id,
l.language_code,
userenv('LANG'),
--l_version_no
l_qp_list_headers_tl_rec.version_no --bug#6636843.
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS (SELECT NULL
FROM qp_list_headers_tl t
WHERE t.list_header_id = l_new_discount_header_id
AND t.language = l.language_code);
SELECT qp_qualifiers_s.nextval
INTO l_new_qualifier_id
FROM dual;
SELECT ACTIVE_FLAG, LIST_TYPE_CODE
INTO l_active_flag, l_list_type_code
--fix for bug 4673872
FROM QP_LIST_HEADERS_ALL_B
WHERE LIST_HEADER_ID = l_new_discount_header_id;
INSERT INTO qp_qualifiers
(
qualifier_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
excluder_flag,
comparison_operator_code,
qualifier_context,
qualifier_attribute,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
qualifier_rule_id,
qualifier_grouping_no,
qualifier_attr_value,
list_header_id,
list_line_id,
created_from_rule_id,
start_date_active,
end_date_active,
qualifier_precedence,
qualifier_datatype,
qualifier_attr_value_to,
active_flag,
list_type_code,
qual_attr_value_from_number,
qual_attr_value_to_number,
search_ind,
distinct_row_count,
qualifier_group_cnt,
header_quals_exist_flag,
qualify_hier_descendents_flag -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
(
l_new_qualifier_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_conc_login_id,
p_conc_program_application_id,
p_conc_program_id,
sysdate,
p_conc_request_id,
l_qp_qualifiers_rec.excluder_flag,
l_qp_qualifiers_rec.comparison_operator_code,
l_qp_qualifiers_rec.qualifier_context,
l_qp_qualifiers_rec.qualifier_attribute,
l_qp_qualifiers_rec.context,
l_qp_qualifiers_rec.attribute1,
l_qp_qualifiers_rec.attribute2,
l_qp_qualifiers_rec.attribute3,
l_qp_qualifiers_rec.attribute4,
l_qp_qualifiers_rec.attribute5,
l_qp_qualifiers_rec.attribute6,
l_qp_qualifiers_rec.attribute7,
l_qp_qualifiers_rec.attribute8,
l_qp_qualifiers_rec.attribute9,
l_qp_qualifiers_rec.attribute10,
l_qp_qualifiers_rec.attribute11,
l_qp_qualifiers_rec.attribute12,
l_qp_qualifiers_rec.attribute13,
l_qp_qualifiers_rec.attribute14,
l_qp_qualifiers_rec.attribute15,
l_qp_qualifiers_rec.qualifier_rule_id,
l_qp_qualifiers_rec.qualifier_grouping_no,
l_qp_qualifiers_rec.qualifier_attr_value,
l_new_discount_header_id,
l_qp_qualifiers_rec.list_line_id,
l_qp_qualifiers_rec.created_from_rule_id,
l_qp_qualifiers_rec.start_date_active,
l_qp_qualifiers_rec.end_date_active,
l_qp_qualifiers_rec.qualifier_precedence,
l_qp_qualifiers_rec.qualifier_datatype,
l_qp_qualifiers_rec.qualifier_attr_value_to,
l_active_flag,
l_list_type_code,
l_qual_attr_value_from_number,
l_qual_attr_value_to_number,
l_qp_qualifiers_rec.search_ind,
l_qp_qualifiers_rec.distinct_row_count,
l_qp_qualifiers_rec.qualifier_group_cnt,
l_qp_qualifiers_rec.header_quals_exist_flag,
l_qp_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(l_new_qualifier_id)
,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_qp_qualifiers_rec.list_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
);
SELECT qp_list_lines_s.nextval
INTO l_new_discount_line_id
FROM dual;
INSERT INTO qp_list_lines
(
list_line_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
list_header_id,
list_line_type_code,
start_date_active,
end_date_active,
automatic_flag,
modifier_level_code,
list_price,
primary_uom_flag,
inventory_item_id,
organization_id,
related_item_id,
relationship_type_id,
substitution_context,
substitution_attribute,
substitution_value,
revision,
revision_date,
revision_reason_code,
context,
attribute1,
attribute2,
comments,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
price_break_type_code,
percent_price,
price_by_formula_id,
number_effective_periods,
effective_period_uom,
arithmetic_operator,
operand,
override_flag,
print_on_invoice_flag,
rebate_transaction_type_code,
estim_accrual_rate,
generate_using_formula_id,
reprice_flag,
accrual_flag,
pricing_group_sequence,
incompatibility_grp_code,
list_line_no,
product_precedence,
pricing_phase_id,
expiration_period_start_date,
number_expiration_periods,
expiration_period_uom,
expiration_date,
estim_gl_value,
accrual_conversion_rate,
benefit_price_list_line_id,
proration_type_code,
benefit_qty,
benefit_uom_code,
charge_type_code,
charge_subtype_code,
benefit_limit,
include_on_returns_flag,
qualification_ind
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
(
l_new_discount_line_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_conc_login_id,
p_conc_program_application_id,
p_conc_program_id,
sysdate,
p_conc_request_id,
l_new_discount_header_id,
l_qp_discount_lines_rec.list_line_type_code,
l_qp_discount_lines_rec.start_date_active,
l_qp_discount_lines_rec.end_date_active,
l_qp_discount_lines_rec.automatic_flag,
l_qp_discount_lines_rec.modifier_level_code,
l_qp_discount_lines_rec.list_price,
l_qp_discount_lines_rec.primary_uom_flag,
l_qp_discount_lines_rec.inventory_item_id,
l_qp_discount_lines_rec.organization_id,
l_qp_discount_lines_rec.related_item_id,
l_qp_discount_lines_rec.relationship_type_id,
l_qp_discount_lines_rec.substitution_context,
l_qp_discount_lines_rec.substitution_attribute,
l_qp_discount_lines_rec.substitution_value,
l_qp_discount_lines_rec.revision,
l_qp_discount_lines_rec.revision_date,
l_qp_discount_lines_rec.revision_reason_code,
l_qp_discount_lines_rec.context,
l_qp_discount_lines_rec.attribute1,
l_qp_discount_lines_rec.attribute2,
l_qp_discount_lines_rec.comments,
l_qp_discount_lines_rec.attribute3,
l_qp_discount_lines_rec.attribute4,
l_qp_discount_lines_rec.attribute5,
l_qp_discount_lines_rec.attribute6,
l_qp_discount_lines_rec.attribute7,
l_qp_discount_lines_rec.attribute8,
l_qp_discount_lines_rec.attribute9,
l_qp_discount_lines_rec.attribute10,
l_qp_discount_lines_rec.attribute11,
l_qp_discount_lines_rec.attribute12,
l_qp_discount_lines_rec.attribute13,
l_qp_discount_lines_rec.attribute14,
l_qp_discount_lines_rec.attribute15,
l_qp_discount_lines_rec.price_break_type_code,
l_qp_discount_lines_rec.percent_price,
l_qp_discount_lines_rec.price_by_formula_id,
l_qp_discount_lines_rec.number_effective_periods,
l_qp_discount_lines_rec.effective_period_uom,
l_qp_discount_lines_rec.arithmetic_operator,
l_qp_discount_lines_rec.operand,
l_qp_discount_lines_rec.override_flag,
l_qp_discount_lines_rec.print_on_invoice_flag,
l_qp_discount_lines_rec.rebate_transaction_type_code,
l_qp_discount_lines_rec.estim_accrual_rate,
l_qp_discount_lines_rec.generate_using_formula_id,
l_qp_discount_lines_rec.reprice_flag,
l_qp_discount_lines_rec.accrual_flag,
l_qp_discount_lines_rec.pricing_group_sequence,
l_qp_discount_lines_rec.incompatibility_grp_code,
l_qp_discount_lines_rec.list_line_no,
l_qp_discount_lines_rec.product_precedence,
l_qp_discount_lines_rec.pricing_phase_id,
l_qp_discount_lines_rec.expiration_period_start_date,
l_qp_discount_lines_rec.number_expiration_periods,
l_qp_discount_lines_rec.expiration_period_uom,
l_qp_discount_lines_rec.expiration_date,
l_qp_discount_lines_rec.estim_gl_value,
l_qp_discount_lines_rec.accrual_conversion_rate,
l_qp_discount_lines_rec.benefit_price_list_line_id,
l_qp_discount_lines_rec.proration_type_code,
l_qp_discount_lines_rec.benefit_qty,
l_qp_discount_lines_rec.benefit_uom_code,
l_qp_discount_lines_rec.charge_type_code,
l_qp_discount_lines_rec.charge_subtype_code,
l_qp_discount_lines_rec.benefit_limit,
l_qp_discount_lines_rec.include_on_returns_flag,
l_qp_discount_lines_rec.qualification_ind
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(l_new_discount_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
);
SELECT qp_pricing_attributes_s.nextval
INTO l_new_pricing_attribute_id
FROM dual;
INSERT INTO qp_pricing_attributes
(pricing_attribute_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
list_line_id,
list_header_id,
pricing_phase_id,
qualification_ind,
excluder_flag,
accumulate_flag,
product_attribute_context,
product_attribute,
product_attr_value,
product_uom_code,
pricing_attribute_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
attribute_grouping_no,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
product_attribute_datatype,
pricing_attribute_datatype,
comparison_operator_code,
pricing_attr_value_from_number,
pricing_attr_value_to_number
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_PRICING_ATTR_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
(l_new_pricing_attribute_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_conc_login_id,
p_conc_program_application_id,
p_conc_program_id,
sysdate,
p_conc_request_id,
l_new_discount_line_id, /* new discount line id */
l_new_discount_header_id,
l_qp_pricing_attributes_rec.pricing_phase_id,
l_qp_pricing_attributes_rec.qualification_ind,
l_qp_pricing_attributes_rec.excluder_flag,
l_qp_pricing_attributes_rec.accumulate_flag,
l_qp_pricing_attributes_rec.product_attribute_context,
l_qp_pricing_attributes_rec.product_attribute,
l_qp_pricing_attributes_rec.product_attr_value,
l_qp_pricing_attributes_rec.product_uom_code,
l_qp_pricing_attributes_rec.pricing_attribute_context,
l_qp_pricing_attributes_rec.pricing_attribute,
l_qp_pricing_attributes_rec.pricing_attr_value_from,
l_qp_pricing_attributes_rec.pricing_attr_value_to,
l_qp_pricing_attributes_rec.attribute_grouping_no,
l_qp_pricing_attributes_rec.context,
l_qp_pricing_attributes_rec.attribute1,
l_qp_pricing_attributes_rec.attribute2,
l_qp_pricing_attributes_rec.attribute3,
l_qp_pricing_attributes_rec.attribute4,
l_qp_pricing_attributes_rec.attribute5,
l_qp_pricing_attributes_rec.attribute6,
l_qp_pricing_attributes_rec.attribute7,
l_qp_pricing_attributes_rec.attribute8,
l_qp_pricing_attributes_rec.attribute9,
l_qp_pricing_attributes_rec.attribute10,
l_qp_pricing_attributes_rec.attribute11,
l_qp_pricing_attributes_rec.attribute12,
l_qp_pricing_attributes_rec.attribute13,
l_qp_pricing_attributes_rec.attribute14,
l_qp_pricing_attributes_rec.attribute15,
l_qp_pricing_attributes_rec.product_attribute_datatype,
l_qp_pricing_attributes_rec.pricing_attribute_datatype,
l_qp_pricing_attributes_rec.comparison_operator_code,
l_pric_attr_value_from_number,
l_pric_attr_value_to_number
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(l_new_pricing_attribute_id)
,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_discount_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
);
SELECT qp_qualifiers_s.nextval
INTO l_new_qualifier_id
FROM dual;
SELECT ACTIVE_FLAG, LIST_TYPE_CODE
INTO l_active_flag, l_list_type_code
FROM QP_LIST_HEADERS_B
WHERE LIST_HEADER_ID = l_new_discount_header_id;
INSERT INTO qp_qualifiers
(
qualifier_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
excluder_flag,
comparison_operator_code,
qualifier_context,
qualifier_attribute,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
qualifier_rule_id,
qualifier_grouping_no,
qualifier_attr_value,
list_header_id,
list_line_id,
created_from_rule_id,
start_date_active,
end_date_active,
qualifier_precedence,
qualifier_datatype,
qualifier_attr_value_to,
active_flag,
list_type_code,
qual_attr_value_from_number,
qual_attr_value_to_number,
search_ind,
distinct_row_count,
qualifier_group_cnt,
header_quals_exist_flag,
qualify_hier_descendents_flag -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
(
l_new_qualifier_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_conc_login_id,
p_conc_program_application_id,
p_conc_program_id,
sysdate,
p_conc_request_id,
l_qp_qualifiers_rec.excluder_flag,
l_qp_qualifiers_rec.comparison_operator_code,
l_qp_qualifiers_rec.qualifier_context,
l_qp_qualifiers_rec.qualifier_attribute,
l_qp_qualifiers_rec.context,
l_qp_qualifiers_rec.attribute1,
l_qp_qualifiers_rec.attribute2,
l_qp_qualifiers_rec.attribute3,
l_qp_qualifiers_rec.attribute4,
l_qp_qualifiers_rec.attribute5,
l_qp_qualifiers_rec.attribute6,
l_qp_qualifiers_rec.attribute7,
l_qp_qualifiers_rec.attribute8,
l_qp_qualifiers_rec.attribute9,
l_qp_qualifiers_rec.attribute10,
l_qp_qualifiers_rec.attribute11,
l_qp_qualifiers_rec.attribute12,
l_qp_qualifiers_rec.attribute13,
l_qp_qualifiers_rec.attribute14,
l_qp_qualifiers_rec.attribute15,
l_qp_qualifiers_rec.qualifier_rule_id,
l_qp_qualifiers_rec.qualifier_grouping_no,
l_qp_qualifiers_rec.qualifier_attr_value,
l_new_discount_header_id,
l_new_discount_line_id,
--l_qp_qualifiers_rec.list_line_id, changed for bug 8326619
l_qp_qualifiers_rec.created_from_rule_id,
l_qp_qualifiers_rec.start_date_active,
l_qp_qualifiers_rec.end_date_active,
l_qp_qualifiers_rec.qualifier_precedence,
l_qp_qualifiers_rec.qualifier_datatype,
l_qp_qualifiers_rec.qualifier_attr_value_to,
l_active_flag,
l_list_type_code,
l_qual_attr_value_from_number,
l_qual_attr_value_to_number,
l_qp_qualifiers_rec.search_ind,
l_qp_qualifiers_rec.distinct_row_count,
l_qp_qualifiers_rec.qualifier_group_cnt,
l_qp_qualifiers_rec.header_quals_exist_flag,
l_qp_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(l_new_qualifier_id)
,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_qp_qualifiers_rec.list_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_discount_header_id)
);
SELECT qp_rltd_modifiers_s.nextval
INTO l_new_rltd_modifier_id
FROM dual;
INSERT INTO qp_rltd_modifiers
(creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
rltd_modifier_id,
rltd_modifier_grp_no,
from_rltd_modifier_id,
to_rltd_modifier_id,
rltd_modifier_grp_type
)
VALUES
(sysdate,
p_user_id,
sysdate,
p_user_id,
p_conc_login_id,
l_qp_rltd_modifiers_rec.context,
l_qp_rltd_modifiers_rec.attribute1,
l_qp_rltd_modifiers_rec.attribute2,
l_qp_rltd_modifiers_rec.attribute3,
l_qp_rltd_modifiers_rec.attribute4,
l_qp_rltd_modifiers_rec.attribute5,
l_qp_rltd_modifiers_rec.attribute6,
l_qp_rltd_modifiers_rec.attribute7,
l_qp_rltd_modifiers_rec.attribute8,
l_qp_rltd_modifiers_rec.attribute9,
l_qp_rltd_modifiers_rec.attribute10,
l_qp_rltd_modifiers_rec.attribute11,
l_qp_rltd_modifiers_rec.attribute12,
l_qp_rltd_modifiers_rec.attribute13,
l_qp_rltd_modifiers_rec.attribute14,
l_qp_rltd_modifiers_rec.attribute15,
l_new_rltd_modifier_id,
l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
l_new_from_id,
l_new_to_id,
l_qp_rltd_modifiers_rec.rltd_modifier_grp_type
);
insert_flag varchar2(1);
last_update_date QP_LIST_LINES.last_update_date%TYPE,
last_updated_by QP_LIST_LINES.last_updated_by%TYPE,
last_update_login QP_LIST_LINES.last_update_login%TYPE,
program_application_id QP_LIST_LINES.program_application_id%TYPE,
program_id QP_LIST_LINES.program_id%TYPE,
program_update_date QP_LIST_LINES.program_update_date%TYPE,
request_id QP_LIST_LINES.request_id%TYPE,
list_header_id QP_LIST_LINES.list_header_id%TYPE,
list_line_type_code QP_LIST_LINES.list_line_type_code%TYPE,
automatic_flag QP_LIST_LINES.automatic_flag%TYPE,
modifier_level_code QP_LIST_LINES.modifier_level_code%TYPE,
list_price QP_LIST_LINES.list_price%TYPE,
primary_uom_flag QP_LIST_LINES.primary_uom_flag%TYPE,
inventory_item_id QP_LIST_LINES.inventory_item_id%TYPE,
organization_id QP_LIST_LINES.organization_id%TYPE,
related_item_id QP_LIST_LINES.related_item_id%TYPE,
relationship_type_id QP_LIST_LINES.relationship_type_id%TYPE,
substitution_context QP_LIST_LINES.substitution_context%TYPE,
substitution_attribute QP_LIST_LINES.substitution_attribute%TYPE,
substitution_value QP_LIST_LINES.substitution_value%TYPE,
revision QP_LIST_LINES.revision%TYPE,
revision_date QP_LIST_LINES.revision_date%TYPE,
revision_reason_code QP_LIST_LINES.revision_reason_code%TYPE,
context QP_LIST_LINES.context%TYPE,
attribute1 QP_LIST_LINES.attribute1%TYPE,
attribute2 QP_LIST_LINES.attribute2%TYPE,
comments QP_LIST_LINES.comments%TYPE,
attribute3 QP_LIST_LINES.attribute3%TYPE,
attribute4 QP_LIST_LINES.attribute4%TYPE,
attribute5 QP_LIST_LINES.attribute5%TYPE,
attribute6 QP_LIST_LINES.attribute6%TYPE,
attribute7 QP_LIST_LINES.attribute7%TYPE,
attribute8 QP_LIST_LINES.attribute8%TYPE,
attribute9 QP_LIST_LINES.attribute9%TYPE,
attribute10 QP_LIST_LINES.attribute10%TYPE,
attribute11 QP_LIST_LINES.attribute11%TYPE,
attribute12 QP_LIST_LINES.attribute12%TYPE,
attribute13 QP_LIST_LINES.attribute13%TYPE,
attribute14 QP_LIST_LINES.attribute14%TYPE,
attribute15 QP_LIST_LINES.attribute15%TYPE,
price_break_type_code QP_LIST_LINES.price_break_type_code%TYPE,
percent_price QP_LIST_LINES.percent_price%TYPE,
price_by_formula_id QP_LIST_LINES.price_by_formula_id%TYPE,
number_effective_periods QP_LIST_LINES.number_effective_periods%TYPE,
effective_period_uom QP_LIST_LINES.effective_period_uom%TYPE,
arithmetic_operator QP_LIST_LINES.arithmetic_operator%TYPE,
operand QP_LIST_LINES.operand%TYPE,
override_flag QP_LIST_LINES.override_flag%TYPE,
print_on_invoice_flag QP_LIST_LINES.print_on_invoice_flag%TYPE,
rebate_transaction_type_code QP_LIST_LINES.rebate_transaction_type_code%TYPE,
estim_accrual_rate QP_LIST_LINES.estim_accrual_rate%TYPE,
generate_using_formula_id QP_LIST_LINES.generate_using_formula_id%TYPE,
start_date_active QP_LIST_LINES.start_date_active%TYPE,
end_date_active QP_LIST_LINES.end_date_active%TYPE,
reprice_flag QP_LIST_LINES.reprice_flag%TYPE,
accrual_flag QP_LIST_LINES.accrual_flag%TYPE,
pricing_group_sequence QP_LIST_LINES.pricing_group_sequence%TYPE,
incompatibility_grp_code QP_LIST_LINES.incompatibility_grp_code%TYPE,
list_line_no QP_LIST_LINES.list_line_no%TYPE,
product_precedence QP_LIST_LINES.product_precedence%TYPE,
pricing_phase_id QP_LIST_LINES.pricing_phase_id%TYPE,
expiration_period_start_date QP_LIST_LINES.expiration_period_start_date%TYPE,
number_expiration_periods QP_LIST_LINES.number_expiration_periods%TYPE,
expiration_period_uom QP_LIST_LINES.expiration_period_uom%TYPE,
expiration_date QP_LIST_LINES.expiration_date%TYPE,
estim_gl_value QP_LIST_LINES.estim_gl_value%TYPE,
accrual_conversion_rate QP_LIST_LINES.accrual_conversion_rate%TYPE,
benefit_price_list_line_id QP_LIST_LINES.benefit_price_list_line_id%TYPE,
proration_type_code QP_LIST_LINES.proration_type_code%TYPE,
benefit_qty QP_LIST_LINES.benefit_qty%TYPE,
benefit_uom_code QP_LIST_LINES.benefit_uom_code%TYPE,
charge_type_code QP_LIST_LINES.charge_type_code%TYPE,
charge_subtype_code QP_LIST_LINES.charge_subtype_code%TYPE,
benefit_limit QP_LIST_LINES.benefit_limit%TYPE,
include_on_returns_flag QP_LIST_LINES.include_on_returns_flag%TYPE,
qualification_ind QP_LIST_LINES.qualification_ind%TYPE,
recurring_value QP_LIST_LINES.recurring_value%TYPE, -- block pricing
continuous_price_break_flag QP_LIST_LINES.continuous_price_break_flag%TYPE
--Continuous Price Breaks
);
l_select_stmt VARCHAR2(9000);
SELECT *
FROM qp_pricing_attributes
WHERE list_line_id = p_from_list_line_id;
/* First part of cursor qp_qualifiers_cur selects qualifiers while the second part
selects secondary price list */
CURSOR qp_qualifiers_cur(p_from_list_header_id NUMBER, p_context VARCHAR2,
p_attribute VARCHAR2, p_discount_flag VARCHAR2)
IS
SELECT *
FROM qp_qualifiers q
WHERE (q.list_header_id = p_from_list_header_id AND
q.qualifier_attribute <> p_attribute AND --Added for 2200425
Exists (Select Null
--fix for bug 4673872
From qp_list_headers_all_b a
Where a.list_header_id = p_from_list_header_id
And a.list_type_code = 'PRL'
)
)
OR
(q.qualifier_context = p_context AND
q.qualifier_attribute = p_attribute AND
q.qualifier_attr_value = TO_CHAR(p_from_list_header_id) AND
--fix for bug 4673872
EXISTS (select null from qp_list_headers_all_b a --Added for 2200425
where a.list_header_id =q.list_header_id
And a.list_type_code = 'PRL')
/* and --Commented out for 2200425
Exists (Select Null
From qp_list_headers_b a
Where a.list_header_id = p_from_list_header_id
And a.list_type_code = 'PRL'
)*/
);
SELECT *
FROM qp_rltd_modifiers
WHERE from_rltd_modifier_id = a_list_line_id;
SELECT COUNT(*) INTO l_exists
--fix for bug 4673872
FROM qp_secu_list_headers_vl plh
/* WHERE plh.list_type_code = 'PRL' */
/* Commented the above line for bug 1343801 */
WHERE plh.list_type_code in ('PRL' , 'AGR')
AND plh.name = p_new_price_list_name;
/** Following code inserts price list header information **/
-- Get next list_header_id
SELECT qp_list_headers_b_s.nextval
INTO l_new_list_header_id
FROM dual;
INSERT INTO qp_list_headers_all_b
(
list_header_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
list_type_code,
start_date_active,
end_date_active,
automatic_flag,
-- exclusive_flag,
currency_code,
rounding_factor,
ship_method_code,
freight_terms_code,
terms_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
comments,
discount_lines_flag,
gsa_indicator,
prorate_flag,
source_system_code,
active_flag,
parent_list_header_id,
start_date_active_first,
end_date_active_first,
active_date_first_type,
start_date_active_second,
end_date_active_second,
active_date_second_type,
ask_for_flag,
currency_header_id, -- Multi-Currency SunilPandey
pte_code, -- Attribute Manager, Giri
global_flag, -- sfiresto 2853767
orig_org_id -- sfiresto 2853767
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYSTEM_HEADER_REF
)
SELECT
l_new_list_header_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_conc_login_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate,
l_conc_request_id,
list_type_code, /* can be changed to 'PRL' but is implicit */
fnd_date.canonical_to_date(p_start_date_active), --2735911
fnd_date.canonical_to_date(p_end_date_active), --2735911
automatic_flag,
-- exclusive_flag,
currency_code,
rounding_factor,
ship_method_code,
freight_terms_code,
terms_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
comments,
discount_lines_flag,
gsa_indicator,
prorate_flag,
source_system_code,
active_flag,
parent_list_header_id,
start_date_active_first,
end_date_active_first,
active_date_first_type,
start_date_active_second,
end_date_active_second,
active_date_second_type,
ask_for_flag,
currency_header_id, -- Multi-Currency SunilPandey
pte_code, -- Attribute Manager, Giri
p_global_flag, -- sfiresto 2853767
p_org_id -- sfiresto 2853767
--ENH Upgrade BOAPI for orig_sys...ref RAVI
--,nvl(ORIG_SYSTEM_HEADER_REF,to_char(l_new_list_header_id))
-- Bug 5201918
--,to_char(list_header_id) --7309992
,to_char(l_new_list_header_id) --bug#12423441
--fix for bug 4673872
FROM qp_list_headers_all_b
WHERE list_header_id = p_from_list_header_id;
SELECT list_type_code,pte_code,source_system_code
INTO v_list_type_code,v_pte_code,v_source_system_code
--fix for bug 4673872
FROM qp_list_headers_all_b
WHERE list_header_id = p_from_list_header_id;
INSERT INTO qp_list_headers_tl
(last_update_login,
name,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
list_header_id,
language,
source_lang,
version_no
)
SELECT
l_conc_login_id,
p_new_price_list_name,
p_description,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_new_list_header_id,
l.language_code,
userenv('LANG'),
'1'
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS (SELECT NULL
FROM qp_list_headers_tl t
WHERE t.list_header_id = l_new_list_header_id
AND t.language = l.language_code);
SELECT qp_qualifiers_s.nextval
INTO l_new_qualifier_id
FROM dual;
SELECT ACTIVE_FLAG, LIST_TYPE_CODE
INTO l_active_flag, l_list_type_code
--fix for bug 4673872
FROM QP_LIST_HEADERS_ALL_B
WHERE LIST_HEADER_ID = l_qp_qualifiers_rec.list_header_id;
INSERT INTO qp_qualifiers
(
qualifier_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
excluder_flag,
comparison_operator_code,
qualifier_context,
qualifier_attribute,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
qualifier_rule_id,
qualifier_grouping_no,
qualifier_attr_value,
list_header_id,
list_line_id,
created_from_rule_id,
start_date_active,
end_date_active,
qualifier_precedence,
qualifier_datatype,
qualifier_attr_value_to,
active_flag,
list_type_code,
qual_attr_value_from_number,
qual_attr_value_to_number,
search_ind,
distinct_row_count,
qualifier_group_cnt,
header_quals_exist_flag,
qualify_hier_descendents_flag -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_QUALIFIER_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
(
l_new_qualifier_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_conc_login_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate,
l_conc_request_id,
l_qp_qualifiers_rec.excluder_flag,
l_qp_qualifiers_rec.comparison_operator_code,
l_qp_qualifiers_rec.qualifier_context,
l_qp_qualifiers_rec.qualifier_attribute,
l_qp_qualifiers_rec.context,
l_qp_qualifiers_rec.attribute1,
l_qp_qualifiers_rec.attribute2,
l_qp_qualifiers_rec.attribute3,
l_qp_qualifiers_rec.attribute4,
l_qp_qualifiers_rec.attribute5,
l_qp_qualifiers_rec.attribute6,
l_qp_qualifiers_rec.attribute7,
l_qp_qualifiers_rec.attribute8,
l_qp_qualifiers_rec.attribute9,
l_qp_qualifiers_rec.attribute10,
l_qp_qualifiers_rec.attribute11,
l_qp_qualifiers_rec.attribute12,
l_qp_qualifiers_rec.attribute13,
l_qp_qualifiers_rec.attribute14,
l_qp_qualifiers_rec.attribute15,
l_qp_qualifiers_rec.qualifier_rule_id,
l_qp_qualifiers_rec.qualifier_grouping_no,
l_qp_qualifiers_rec.qualifier_attr_value,
l_qp_qualifiers_rec.list_header_id,
l_qp_qualifiers_rec.list_line_id,
l_qp_qualifiers_rec.created_from_rule_id,
l_qp_qualifiers_rec.start_date_active,
l_qp_qualifiers_rec.end_date_active,
l_qp_qualifiers_rec.qualifier_precedence,
l_qp_qualifiers_rec.qualifier_datatype,
l_qp_qualifiers_rec.qualifier_attr_value_to,
l_active_flag,
l_list_type_code,
l_qual_attr_value_from_number,
l_qual_attr_value_to_number,
l_qp_qualifiers_rec.search_ind,
l_qp_qualifiers_rec.distinct_row_count,
l_qp_qualifiers_rec.qualifier_group_cnt,
l_qp_qualifiers_rec.header_quals_exist_flag,
l_qp_qualifiers_rec.qualify_hier_descendents_flag -- Added for TCA
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(l_new_qualifier_id)
,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_qp_qualifiers_rec.list_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_qp_qualifiers_rec.list_header_id)
);
/** Following code inserts price list lines information **/
--copy only price list lines
/* Need to copy only those price list lines whose end-date is not less than
sysdate */
l_select_stmt :=
'SELECT
q.list_line_id,
q.creation_date,
q.created_by,
q.last_update_date,
q.last_updated_by,
q.last_update_login,
q.program_application_id,
q.program_id,
q.program_update_date,
q.request_id,
q.list_header_id,
q.list_line_type_code,
q.automatic_flag,
q.modifier_level_code,
q.list_price,
q.primary_uom_flag,
q.inventory_item_id,
q.organization_id,
q.related_item_id,
q.relationship_type_id,
q.substitution_context,
q.substitution_attribute,
q.substitution_value,
q.revision,
q.revision_date,
q.revision_reason_code,
q.context,
q.attribute1,
q.attribute2,
q.comments,
q.attribute3,
q.attribute4,
q.attribute5,
q.attribute6,
q.attribute7,
q.attribute8,
q.attribute9,
q.attribute10,
q.attribute11,
q.attribute12,
q.attribute13,
q.attribute14,
q.attribute15,
q.price_break_type_code,
q.percent_price,
q.price_by_formula_id,
q.number_effective_periods,
q.effective_period_uom,
q.arithmetic_operator,
q.operand,
q.override_flag,
q.print_on_invoice_flag,
q.rebate_transaction_type_code,
q.estim_accrual_rate,
q.generate_using_formula_id,
q.start_date_active,
q.end_date_active,
q.reprice_flag,
q.accrual_flag,
q.pricing_group_sequence,
q.incompatibility_grp_code,
q.list_line_no,
q.product_precedence,
q.pricing_phase_id,
q.expiration_period_start_date,
q.number_expiration_periods,
q.expiration_period_uom,
q.expiration_date,
q.estim_gl_value,
q.accrual_conversion_rate,
q.benefit_price_list_line_id,
q.proration_type_code,
q.benefit_qty,
q.benefit_uom_code,
q.charge_type_code,
q.charge_subtype_code,
q.benefit_limit,
q.include_on_returns_flag,
q.qualification_ind,
q.recurring_value, -- block pricing
q.continuous_price_break_flag --Continuous Price Breaks
FROM qp_list_lines q
WHERE q.list_header_id = :frm
AND (q.end_date_active IS NULL OR trunc(q.end_date_active) >= trunc(sysdate)) --Modified by dhgupta for 2100785
AND q.list_line_id IN
(SELECT DISTINCT a.list_line_id
FROM qp_pricing_attributes a
WHERE a.list_line_id = q.list_line_id ';
l_select_stmt := l_select_stmt ||
'AND TO_NUMBER(a.product_attr_value) IN
(SELECT inventory_item_id
FROM mtl_system_items m
WHERE (m.inventory_item_id = TO_NUMBER(a.product_attr_value)) ';
l_select_stmt := l_select_stmt ||
'AND a.product_attribute_context = ''ITEM''
AND a.product_attribute = ''PRICING_ATTRIBUTE1''
AND EXISTS
(SELECT ''X''
FROM mtl_system_items m
WHERE (m.inventory_item_id = TO_NUMBER(a.product_attr_value)) ';
l_select_stmt := l_select_stmt ||
'AND (m.segment1 BETWEEN ' || p_segment1_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment2 BETWEEN ' || p_segment2_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment3 BETWEEN ' || p_segment3_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment4 BETWEEN ' || p_segment4_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment5 BETWEEN ' || p_segment5_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment6 BETWEEN ' || p_segment6_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment7 BETWEEN ' || p_segment7_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment8 BETWEEN ' || p_segment8_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment9 BETWEEN ' || p_segment9_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment10 BETWEEN ' || p_segment10_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment10 BETWEEN ' || p_segment11_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment12 BETWEEN ' || p_segment12_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment13 BETWEEN ' || p_segment13_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment14 BETWEEN ' || p_segment14_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment15 BETWEEN ' || p_segment15_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment16 BETWEEN ' || p_segment16_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment17 BETWEEN ' || p_segment17_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment18 BETWEEN ' || p_segment18_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment19 BETWEEN ' || p_segment19_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND (m.segment20 BETWEEN ' || p_segment20_lohi || ') ';
l_select_stmt := l_select_stmt ||
'AND m.inventory_item_id IN
( SELECT ic.inventory_item_id
FROM mtl_item_categories ic
WHERE ic.inventory_item_id = m.inventory_item_id
AND ic.organization_id = m.organization_id
AND (ic.category_id = :category_id or ic.category_id in ( select parent_id
FROM eni_denorm_hierarchies
WHERE child_id = :category_id and
organization_id = ic.organization_id and
exists (select ''Y'' from QP_SOURCESYSTEM_FNAREA_MAP A, qp_pte_source_systems B ,
mtl_default_category_sets c, mtl_category_sets d
where A.PTE_SOURCE_SYSTEM_ID = B.PTE_SOURCE_SYSTEM_ID and
B.PTE_CODE = :pte_code and
B.APPLICATION_SHORT_NAME = :source_system_code and
A.FUNCTIONAL_AREA_ID = c.FUNCTIONAL_AREA_ID and
c.CATEGORY_SET_ID = d.CATEGORY_SET_ID and
d.HIERARCHY_ENABLED = ''Y'' and
A.ENABLED_FLAG = ''Y'' and B.ENABLED_FLAG = ''Y'')))
AND ic.category_set_id = :category_set_id
) ';
l_select_stmt := l_select_stmt ||
'AND m.inventory_item_id IN
( SELECT ic.inventory_item_id
FROM mtl_item_categories ic
WHERE ic.inventory_item_id = m.inventory_item_id
AND ic.organization_id = m.organization_id
AND ic.category_set_id = :category_set_id
) ';
l_select_stmt := l_select_stmt || ') )';
l_select_stmt := l_select_stmt || ') ';
fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||l_select_stmt);
OPEN qp_list_lines_cv FOR l_select_stmt USING p_from_list_header_id;
OPEN qp_list_lines_cv FOR l_select_stmt USING p_from_list_header_id,p_category_id,p_category_id,v_pte_code,v_source_system_code,p_category_set_id;
OPEN qp_list_lines_cv FOR l_select_stmt USING p_from_list_header_id,p_category_set_id;
are still active. Cursor qp_list_lines_cv selects all active lines, therefore,
orphaned child lines are also copied to new price list.The following logic
excludes such orphaned lines from being copied. */
insert_flag :='N';
select from_rltd_modifier_id into l_line_id
from qp_rltd_modifiers
where to_rltd_modifier_id=l_qp_list_lines_rec.list_line_id;
insert_flag :='Y';
select count(*) into l_cnt from qp_list_lines where list_line_id=l_line_id
AND ((end_date_active IS NULL) OR (trunc(end_date_active) >= trunc(sysdate)));
insert_flag :='Y';
fnd_file.put_line(FND_FILE.LOG,'>>>>>>>>>>>>>>>>'||'insert flag is ' || insert_flag);
If insert_flag ='Y' then /* end changes for bug3067774 */
-- Get next list_line_id
SELECT qp_list_lines_s.nextval
INTO l_new_list_line_id
FROM dual;
INSERT INTO qp_list_lines
(
list_line_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
list_header_id,
list_line_type_code,
start_date_active,
end_date_active,
automatic_flag,
modifier_level_code,
list_price,
primary_uom_flag,
inventory_item_id,
organization_id,
related_item_id,
relationship_type_id,
substitution_context,
substitution_attribute,
substitution_value,
revision,
revision_date,
revision_reason_code,
context,
attribute1,
attribute2,
comments,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
price_break_type_code,
percent_price,
price_by_formula_id,
number_effective_periods,
effective_period_uom,
arithmetic_operator,
operand,
override_flag,
print_on_invoice_flag,
rebate_transaction_type_code,
estim_accrual_rate,
generate_using_formula_id,
reprice_flag,
accrual_flag,
pricing_group_sequence,
incompatibility_grp_code,
list_line_no,
product_precedence,
pricing_phase_id,
expiration_period_start_date,
number_expiration_periods,
expiration_period_uom,
expiration_date,
estim_gl_value,
accrual_conversion_rate,
benefit_price_list_line_id,
proration_type_code,
benefit_qty,
benefit_uom_code,
charge_type_code,
charge_subtype_code,
benefit_limit,
include_on_returns_flag,
qualification_ind,
recurring_value, -- block pricing
continuous_price_break_flag --Continuous Price Breaks
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
(
l_new_list_line_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_conc_login_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate,
l_conc_request_id,
l_new_list_header_id,
l_qp_list_lines_rec.list_line_type_code,
DECODE (p_effective_dates_flag,
'Y', l_qp_list_lines_rec.start_date_active,
NULL), /* If flag='Y', retain start date from copied line */
/* else default start date */
DECODE (p_effective_dates_flag,
'Y', l_qp_list_lines_rec.end_date_active,
NULL), /* If flag='Y', retain end date from copied line */
/* else default end date */
l_qp_list_lines_rec.automatic_flag,
l_qp_list_lines_rec.modifier_level_code,
l_qp_list_lines_rec.list_price,
l_qp_list_lines_rec.primary_uom_flag,
l_qp_list_lines_rec.inventory_item_id,
l_qp_list_lines_rec.organization_id,
l_qp_list_lines_rec.related_item_id,
l_qp_list_lines_rec.relationship_type_id,
l_qp_list_lines_rec.substitution_context,
l_qp_list_lines_rec.substitution_attribute,
l_qp_list_lines_rec.substitution_value,
l_qp_list_lines_rec.revision,
l_qp_list_lines_rec.revision_date,
l_qp_list_lines_rec.revision_reason_code,
l_qp_list_lines_rec.context,
l_qp_list_lines_rec.attribute1,
l_qp_list_lines_rec.attribute2,
l_qp_list_lines_rec.comments,
l_qp_list_lines_rec.attribute3,
l_qp_list_lines_rec.attribute4,
l_qp_list_lines_rec.attribute5,
l_qp_list_lines_rec.attribute6,
l_qp_list_lines_rec.attribute7,
l_qp_list_lines_rec.attribute8,
l_qp_list_lines_rec.attribute9,
l_qp_list_lines_rec.attribute10,
l_qp_list_lines_rec.attribute11,
l_qp_list_lines_rec.attribute12,
l_qp_list_lines_rec.attribute13,
l_qp_list_lines_rec.attribute14,
l_qp_list_lines_rec.attribute15,
l_qp_list_lines_rec.price_break_type_code,
l_qp_list_lines_rec.percent_price,
l_qp_list_lines_rec.price_by_formula_id,
l_qp_list_lines_rec.number_effective_periods,
l_qp_list_lines_rec.effective_period_uom,
l_qp_list_lines_rec.arithmetic_operator,
l_qp_list_lines_rec.operand,
l_qp_list_lines_rec.override_flag,
l_qp_list_lines_rec.print_on_invoice_flag,
l_qp_list_lines_rec.rebate_transaction_type_code,
l_qp_list_lines_rec.estim_accrual_rate,
l_qp_list_lines_rec.generate_using_formula_id,
l_qp_list_lines_rec.reprice_flag,
l_qp_list_lines_rec.accrual_flag,
l_qp_list_lines_rec.pricing_group_sequence,
l_qp_list_lines_rec.incompatibility_grp_code,
l_qp_list_lines_rec.list_line_no,
l_qp_list_lines_rec.product_precedence,
l_qp_list_lines_rec.pricing_phase_id,
l_qp_list_lines_rec.expiration_period_start_date,
l_qp_list_lines_rec.number_expiration_periods,
l_qp_list_lines_rec.expiration_period_uom,
l_qp_list_lines_rec.expiration_date,
l_qp_list_lines_rec.estim_gl_value,
l_qp_list_lines_rec.accrual_conversion_rate,
l_qp_list_lines_rec.benefit_price_list_line_id,
l_qp_list_lines_rec.proration_type_code,
l_qp_list_lines_rec.benefit_qty,
l_qp_list_lines_rec.benefit_uom_code,
l_qp_list_lines_rec.charge_type_code,
l_qp_list_lines_rec.charge_subtype_code,
l_qp_list_lines_rec.benefit_limit,
l_qp_list_lines_rec.include_on_returns_flag,
l_qp_list_lines_rec.qualification_ind,
l_qp_list_lines_rec.recurring_value, -- block pricing
decode(nvl(FND_PROFILE.VALUE('QP_CONTINUOUS_PB'),'Y'),'Y','Y','N') -- Continuous Price Breaks -- 14103598
--ENH Upgrade BOAPI for orig_sys...ref RAVI
--,to_char(l_qp_list_lines_rec.list_line_id) --7309992
--,to_char(l_qp_list_lines_rec.list_header_id) --7309992
,to_char(l_new_list_line_id) --bug#12423441
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_list_header_id) --bug#12423441
);
fnd_file.put_line(FND_FILE.LOG,'Inserted line');
/* Select qp_pricing_attributes records for the 'from' list_line_id */
FOR l_qp_pricing_attributes_rec IN qp_pricing_attributes_cur (
l_qp_list_lines_rec.list_line_id)
LOOP
-- Get next pricing_attribute_id
SELECT qp_pricing_attributes_s.nextval
INTO l_new_pricing_attribute_id
FROM dual;
INSERT INTO qp_pricing_attributes
(pricing_attribute_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
list_line_id,
list_header_id,
pricing_phase_id,
qualification_ind,
excluder_flag,
accumulate_flag,
product_attribute_context,
product_attribute,
product_attr_value,
product_uom_code,
pricing_attribute_context,
pricing_attribute,
pricing_attr_value_from,
pricing_attr_value_to,
attribute_grouping_no,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
product_attribute_datatype,
pricing_attribute_datatype,
comparison_operator_code,
pricing_attr_value_from_number,
pricing_attr_value_to_number
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,ORIG_SYS_PRICING_ATTR_REF
,ORIG_SYS_LINE_REF
,ORIG_SYS_HEADER_REF
)
VALUES
(l_new_pricing_attribute_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_conc_login_id,
l_conc_program_application_id,
l_conc_program_id,
sysdate,
l_conc_request_id,
l_new_list_line_id, /* new list line id */
l_new_list_header_id,
l_qp_pricing_attributes_rec.pricing_phase_id,
l_qp_pricing_attributes_rec.qualification_ind,
l_qp_pricing_attributes_rec.excluder_flag,
l_qp_pricing_attributes_rec.accumulate_flag,
l_qp_pricing_attributes_rec.product_attribute_context,
l_qp_pricing_attributes_rec.product_attribute,
l_qp_pricing_attributes_rec.product_attr_value,
l_qp_pricing_attributes_rec.product_uom_code,
l_qp_pricing_attributes_rec.pricing_attribute_context,
l_qp_pricing_attributes_rec.pricing_attribute,
l_qp_pricing_attributes_rec.pricing_attr_value_from,
l_qp_pricing_attributes_rec.pricing_attr_value_to,
l_qp_pricing_attributes_rec.attribute_grouping_no,
l_qp_pricing_attributes_rec.context,
l_qp_pricing_attributes_rec.attribute1,
l_qp_pricing_attributes_rec.attribute2,
l_qp_pricing_attributes_rec.attribute3,
l_qp_pricing_attributes_rec.attribute4,
l_qp_pricing_attributes_rec.attribute5,
l_qp_pricing_attributes_rec.attribute6,
l_qp_pricing_attributes_rec.attribute7,
l_qp_pricing_attributes_rec.attribute8,
l_qp_pricing_attributes_rec.attribute9,
l_qp_pricing_attributes_rec.attribute10,
l_qp_pricing_attributes_rec.attribute11,
l_qp_pricing_attributes_rec.attribute12,
l_qp_pricing_attributes_rec.attribute13,
l_qp_pricing_attributes_rec.attribute14,
l_qp_pricing_attributes_rec.attribute15,
l_qp_pricing_attributes_rec.product_attribute_datatype,
l_qp_pricing_attributes_rec.pricing_attribute_datatype,
l_qp_pricing_attributes_rec.comparison_operator_code,
l_pric_attr_value_from_number,
l_pric_attr_value_to_number
--ENH Upgrade BOAPI for orig_sys...ref RAVI
,to_char(l_new_pricing_attribute_id)
,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=l_new_list_line_id)
,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_new_list_header_id)
);
fnd_file.put_line(FND_FILE.LOG,'Inserted pricing attribute');
SELECT qp_rltd_modifiers_s.nextval
INTO l_new_rltd_modifier_id
FROM dual;
INSERT INTO qp_rltd_modifiers
(creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
rltd_modifier_id,
rltd_modifier_grp_no,
from_rltd_modifier_id,
to_rltd_modifier_id,
rltd_modifier_grp_type
)
VALUES
(sysdate,
l_user_id,
sysdate,
l_user_id,
l_conc_login_id,
l_qp_rltd_modifiers_rec.context,
l_qp_rltd_modifiers_rec.attribute1,
l_qp_rltd_modifiers_rec.attribute2,
l_qp_rltd_modifiers_rec.attribute3,
l_qp_rltd_modifiers_rec.attribute4,
l_qp_rltd_modifiers_rec.attribute5,
l_qp_rltd_modifiers_rec.attribute6,
l_qp_rltd_modifiers_rec.attribute7,
l_qp_rltd_modifiers_rec.attribute8,
l_qp_rltd_modifiers_rec.attribute9,
l_qp_rltd_modifiers_rec.attribute10,
l_qp_rltd_modifiers_rec.attribute11,
l_qp_rltd_modifiers_rec.attribute12,
l_qp_rltd_modifiers_rec.attribute13,
l_qp_rltd_modifiers_rec.attribute14,
l_qp_rltd_modifiers_rec.attribute15,
l_new_rltd_modifier_id,
l_qp_rltd_modifiers_rec.rltd_modifier_grp_no,
l_new_from_id,
l_new_to_id,
l_qp_rltd_modifiers_rec.rltd_modifier_grp_type
);
Delete_Duplicate_Lines(p_effective_dates_flag, l_new_list_header_id);
/* This code will call the API to update the denormalized columns on QP_QUALIFIERS*/
QP_MAINTAIN_DENORMALIZED_DATA.UPDATE_QUALIFIERS
(ERR_BUFF => errbuf,
RETCODE => retcode,
P_LIST_HEADER_ID => l_new_list_header_id,
p_List_Header_Id_high => l_new_discount_header_id,
p_UPDATE_TYPE => 'ALL'); ---Added 2 more parameters for bug 8326619;
--error from update denormalized columns
fnd_file.put_line(FND_FILE.LOG,'Error in Update of denormalized columns in QP_Qualifiers');
fnd_file.put_line(FND_FILE.LOG,'Update of denormalized columns in QP_Qualifiers completed successfully');
select min(list_line_id), max(list_line_id)
into l_min_list_line_id, l_max_list_line_id
from qp_list_lines
where list_header_id = l_new_list_header_id;
QP_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_list_header_id,
null,
l_min_list_line_id,
l_max_list_line_id);
QP_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(l_new_list_header_id,
l_min_list_line_id,l_max_list_line_id);
QP_ATTR_GRP_PVT.update_pp_lines(l_new_list_header_id,
l_min_list_line_id,l_max_list_line_id);
select min(list_line_id), max(list_line_id)
into l_min_list_line_id, l_max_list_line_id
from qp_list_lines
where list_header_id = l_list_header_id;
QP_PS_ATTR_GRP_PVT.Update_Qual_Segment_id(l_new_list_header_id,
null,
-1,
-1); --bug#11925158
QP_PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(l_new_list_header_id,
l_min_list_line_id,l_max_list_line_id);
QP_PS_ATTR_GRP_PVT.update_pp_lines(l_new_list_header_id,
l_min_list_line_id,l_max_list_line_id);