Copying a Modifier List:
You can quickly create a new modifier by copying an existing one. The modifier lines, pricing attributes, and qualifiers will be copied from the original modifier to
the new one. The following attributes are copied to the new modifier: price breaks, other item discounts, coupons, promotional goods, setups and line.
If we are in a situation to copy modifier with more number of lines and huge number of Qualifiers attached to those lines with changes in the Product Attributes then use the following package to create new Modifier List
from an existing Modifier List with required changes in the new modifier lines.
Package Specification:
CREATE OR REPLACE
PACKAGE XX_MODIFIER_COPY_PKG
IS
PROCEDURE XX_MODIFIER_COPY(
p_copy_from IN VARCHAR2,
p_name IN VARCHAR2,
p_description IN VARCHAR2,
p_prod_attribute IN VARCHAR2,
p_prod_value IN VARCHAR2 );
END XX_MODIFIER_COPY_PKG;
/
Parameters Description:
p_copy_from ->> Is the Existing Modifier which we you need to copy
p_name --> New Modifier Name
p_description --> New Description
p_prod_attribute --> If there are any changes with respect to Product Attribute from Old to New then pass new value to this parameter. If not, you can pass NULL
Example: From Item Number to Item Category
p_prod_value --> If there are any changes with respect to Product Attribute from Old to New then pass new value to this parameter. If not, you can pass NULL
Package Body:
CREATE OR REPLACE
PACKAGE body XX_MODIFIER_COPY_PKG
IS
PROCEDURE XX_MODIFIER_COPY(
p_copy_from IN VARCHAR2,
p_name IN VARCHAR2,
p_description IN VARCHAR2,
p_prod_attribute IN VARCHAR2,
p_prod_value IN VARCHAR2 )
IS
CURSOR modifier_cur
IS
SELECT *
FROM APPS.QP_LIST_HEADERS QPH
WHERE 1=1
AND qph.name LIKE p_copy_from;--'%IND%ABRIVO%HP%';--'%IND%ABRIVO%INTRO%';
CURSOR modifier_line_cur(p_list_header_id NUMBER)
IS
SELECT qpll.*,--qppa.list_line_id,
qppa.excluder_flag,
qppa.accumulate_flag,
qppa.product_attribute_context,
qppa.product_attribute,
qppa.product_attr_value,
qppa.product_uom_code attr_uom,
qppa.pricing_attribute_context,
qppa.pricing_attribute,
qppa.pricing_attr_value_from,
qppa.pricing_attr_value_to,
qppa.attribute_grouping_no,
qppa.product_attribute_datatype,
qppa.pricing_attribute_datatype,
qppa.comparison_operator_code,
qppa.CONTEXT attr_context,
qppa.attribute1 attr_attribute1,
qppa.attribute2 attr_attribute2,
qppa.attribute3 attr_attribute3,
qppa.attribute4 attr_attribute4,
qppa.attribute5 attr_attribute5,
qppa.attribute6 attr_attribute6,
qppa.attribute7 attr_attribute7,
qppa.attribute8 attr_attribute8,
qppa.attribute9 attr_attribute9,
qppa.attribute10 attr_attribute10,
qppa.attribute11 attr_attribute11,
qppa.attribute12 attr_attribute12,
qppa.attribute13 attr_attribute13,
qppa.attribute14 attr_attribute14,
qppa.attribute15 attr_attribute15,--qppa.list_header_id,
qppa.pricing_phase_id attr_pricing_phase_id,
qppa.qualification_ind attr_qualification_ind,
qppa.pricing_attr_Value_from_number,
qppa.pricing_attr_value_to_number
FROM apps.qp_list_headers qph,
apps.qp_list_lines qpll,
apps.qp_pricing_attributes qppa
WHERE 1 =1
AND qph.list_header_id = qpll.list_header_id
AND qppa.list_line_id = qpll.list_line_id
AND qph.list_header_id = qppa.list_header_id
AND qph.list_header_id = p_list_header_id;
CURSOR qualifier_cur(p_list_line_id NUMBER)
IS
SELECT qpq.*
FROM apps.qp_qualifiers qpq
WHERE 1 =1
AND list_line_id = p_list_line_id;--5084994;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_msg_tbl oe_msg_pub.msg_tbl_type;
l_control_rec qp_globals.control_rec_type;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_output VARCHAR2 (1000);
ip_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
ip_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
ip_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
ip_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
ip_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ip_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ip_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
ip_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
op_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
op_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
op_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
op_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
op_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
op_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
op_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
op_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
op_pricing_attr_tbl1 qp_modifiers_pub.pricing_attr_tbl_type;
op_pricing_attr_val_tbl1 qp_modifiers_pub.pricing_attr_val_tbl_type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
t_output VARCHAR2 (1000);
x_debug_file VARCHAR2 (100);
l_user_id NUMBER;
l_responsibility_id NUMBER;
l_application_id NUMBER;
k NUMBER := 1;
i NUMBER := 1;
j NUMBER := 1;
l NUMBER := 1;
pa NUMBER := 1;
l_q_grp NUMBER := 1;
l_list_line_id NUMBER;
l_modifier_number NUMBER;
l_terms_id NUMBER;
nexti NUMBER;
v_list_line_id NUMBER;
l_price_by_formula_id NUMBER;
l_org_id NUMBER;
l_responsibility_name VARCHAR2 (100);
l_status VARCHAR2(10);
l_cust_account_id NUMBER;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE 1 =1
AND user_name = 'CONVERSION';
oe_msg_pub.initialize;
oe_debug_pub.initialize;
--X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.setdebuglevel (5);
l_responsibility_name := 'Order Management Super User';
SELECT responsibility_id,
application_id
INTO l_responsibility_id,
l_application_id
FROM fnd_responsibility_tl
WHERE 1 =1
AND responsibility_name = l_responsibility_name;
fnd_global.apps_initialize ( l_user_id, l_responsibility_id, l_application_id );
mo_global.init ('ONT');
/* Create a Modifier header */
FOR modifier_rec IN modifier_cur
LOOP
BEGIN
ip_modifier_list_rec.attribute1 := modifier_rec.attribute1;
ip_modifier_list_rec.attribute10 := modifier_rec.attribute10;
ip_modifier_list_rec.attribute11 := modifier_rec.attribute11;
ip_modifier_list_rec.attribute12 := modifier_rec.attribute12;
ip_modifier_list_rec.attribute13 := modifier_rec.attribute13;
ip_modifier_list_rec.attribute14 := modifier_rec.attribute14;
ip_modifier_list_rec.attribute15 := modifier_rec.attribute15;
ip_modifier_list_rec.attribute2 := modifier_rec.attribute2;
ip_modifier_list_rec.attribute3 := modifier_rec.attribute3;
ip_modifier_list_rec.attribute4 := modifier_rec.attribute4;
ip_modifier_list_rec.attribute5 := modifier_rec.attribute5;
ip_modifier_list_rec.attribute6 := modifier_rec.attribute6;
ip_modifier_list_rec.attribute7 := modifier_rec.attribute7;
ip_modifier_list_rec.attribute8 := modifier_rec.attribute8;
ip_modifier_list_rec.attribute9 := modifier_rec.attribute9;
ip_modifier_list_rec.automatic_flag := modifier_rec.automatic_flag ;
ip_modifier_list_rec.comments := p_description;--modifier_rec.comments; -- Description Field
ip_modifier_list_rec.context := modifier_rec.context;
ip_modifier_list_rec.currency_code := modifier_rec.currency_code;
ip_modifier_list_rec.discount_lines_flag := modifier_rec.discount_lines_flag;
ip_modifier_list_rec.end_date_active := modifier_rec.end_date_active;
ip_modifier_list_rec.freight_terms_code := modifier_rec.freight_terms_code;
ip_modifier_list_rec.gsa_indicator := modifier_rec.gsa_indicator;
ip_modifier_list_rec.list_type_code := modifier_rec.list_type_code;
ip_modifier_list_rec.prorate_flag := modifier_rec.prorate_flag;
ip_modifier_list_rec.rounding_factor := modifier_rec.rounding_factor;
ip_modifier_list_rec.ship_method_code := modifier_rec.ship_method_code;
ip_modifier_list_rec.start_date_active := modifier_rec.start_date_active;
ip_modifier_list_rec.terms_id := modifier_rec.terms_id;
ip_modifier_list_rec.source_system_code := modifier_rec.source_system_code;
ip_modifier_list_rec.active_flag := modifier_rec.active_flag;
ip_modifier_list_rec.parent_list_header_id := modifier_rec.parent_list_header_id;
ip_modifier_list_rec.start_date_active_first := modifier_rec.start_date_active_first;
ip_modifier_list_rec.end_date_active_first := modifier_rec.end_date_active_first;
ip_modifier_list_rec.active_date_first_type := modifier_rec.active_date_first_type;
ip_modifier_list_rec.start_date_active_second:= modifier_rec.start_date_active_second;
ip_modifier_list_rec.global_flag := modifier_rec.global_flag;
ip_modifier_list_rec.end_date_active_second := modifier_rec.end_date_active_second;
ip_modifier_list_rec.active_date_second_type := modifier_rec.active_date_second_type;
ip_modifier_list_rec.ask_for_flag := modifier_rec.ask_for_flag;
ip_modifier_list_rec.name := p_name;
ip_modifier_list_rec.description := p_name;
ip_modifier_list_rec.version_no := modifier_rec.version_no;
--ip_modifier_list_rec.return_status := modifier_rec.return_status;
--ip_modifier_list_rec.db_flag := modifier_rec.db_flag;
ip_modifier_list_rec.operation := qp_globals.g_opr_create;
ip_modifier_list_rec.pte_code := modifier_rec.pte_code;
ip_modifier_list_rec.list_source_code := modifier_rec.list_source_code;
ip_modifier_list_rec.shareable_flag := modifier_rec.shareable_flag;
ip_modifier_list_rec.org_id := modifier_rec.orig_org_id;
k := 1;
j := 1;
pa := 1;
l :=1;
IF ip_qualifiers_tbl.COUNT > 0 THEN
ip_qualifiers_tbl.DELETE;
END IF;
IF ip_modifiers_tbl.COUNT > 0 THEN
ip_modifiers_tbl.DELETE;
END IF;
IF ip_pricing_attr_tbl.COUNT > 0 THEN
ip_pricing_attr_tbl.DELETE;
END IF;
FOR modifier_line_rec IN modifier_line_cur(modifier_rec.list_header_id)
LOOP
BEGIN
ip_modifiers_tbl(j).arithmetic_operator := modifier_line_rec.arithmetic_operator;
ip_modifiers_tbl(j).attribute1 := modifier_line_rec.attribute1;
ip_modifiers_tbl(j).attribute10 := modifier_line_rec.attribute10;
ip_modifiers_tbl(j).attribute11 := modifier_line_rec.attribute11;
ip_modifiers_tbl(j).attribute12 := modifier_line_rec.attribute12;
ip_modifiers_tbl(j).attribute13 := modifier_line_rec.attribute13;
ip_modifiers_tbl(j).attribute14 := modifier_line_rec.attribute14;
ip_modifiers_tbl(j).attribute15 := modifier_line_rec.attribute15;
ip_modifiers_tbl(j).attribute2 := modifier_line_rec.attribute2;
ip_modifiers_tbl(j).attribute3 := modifier_line_rec.attribute3;
ip_modifiers_tbl(j).attribute4 := modifier_line_rec.attribute4;
ip_modifiers_tbl(j).attribute5 := modifier_line_rec.attribute5;
ip_modifiers_tbl(j).attribute6 := modifier_line_rec.attribute6;
ip_modifiers_tbl(j).attribute7 := modifier_line_rec.attribute7;
ip_modifiers_tbl(j).attribute8 := modifier_line_rec.attribute8;
ip_modifiers_tbl(j).attribute9 := modifier_line_rec.attribute9;
ip_modifiers_tbl(j).automatic_flag := modifier_line_rec.automatic_flag;
ip_modifiers_tbl(j).comments := modifier_line_rec.comments;
ip_modifiers_tbl(j).context := modifier_line_rec.context;
ip_modifiers_tbl(j).effective_period_uom := modifier_line_rec.effective_period_uom;
ip_modifiers_tbl(j).end_date_active := modifier_line_rec.end_date_active;
ip_modifiers_tbl(j).estim_accrual_rate := modifier_line_rec.estim_accrual_rate;
ip_modifiers_tbl(j).generate_using_formula_id := modifier_line_rec.GENERATE_USING_FORMULA_ID;
ip_modifiers_tbl(j).inventory_item_id := modifier_line_rec.inventory_item_id;
--ip_modifiers_tbl(j).list_header_id := modifier_line_rec.list_header_id;
ip_modifiers_tbl(j).list_line_type_code := modifier_line_rec.list_line_type_code;
ip_modifiers_tbl(j).list_price := modifier_line_rec.list_price;
ip_modifiers_tbl(j).modifier_level_code := modifier_line_rec.modifier_level_code;
ip_modifiers_tbl(j).number_effective_periods := modifier_line_rec.number_effective_periods;
ip_modifiers_tbl(j).operand := modifier_line_rec.operand;
ip_modifiers_tbl(j).organization_id := modifier_line_rec.organization_id;
ip_modifiers_tbl(j).override_flag := modifier_line_rec.override_flag;
ip_modifiers_tbl(j).percent_price := modifier_line_rec.percent_price;
ip_modifiers_tbl(j).price_break_type_code := modifier_line_rec.price_break_type_code;
ip_modifiers_tbl(j).price_by_formula_id := modifier_line_rec.price_by_formula_id;
ip_modifiers_tbl(j).primary_uom_flag := modifier_line_rec.primary_uom_flag;
ip_modifiers_tbl(j).print_on_invoice_flag := modifier_line_rec.print_on_invoice_flag;
ip_modifiers_tbl(j).rebate_trxn_type_code := modifier_line_rec.rebate_transaction_type_code;
ip_modifiers_tbl(j).related_item_id := modifier_line_rec.related_item_id;
ip_modifiers_tbl(j).relationship_type_id := modifier_line_rec.relationship_type_id;
ip_modifiers_tbl(j).reprice_flag := modifier_line_rec.reprice_flag;
ip_modifiers_tbl(j).revision := modifier_line_rec.revision;
ip_modifiers_tbl(j).revision_date := modifier_line_rec.revision_date;
ip_modifiers_tbl(j).revision_reason_code := modifier_line_rec.revision_reason_code;
ip_modifiers_tbl(j).start_date_active := modifier_line_rec.start_date_active;
ip_modifiers_tbl(j).substitution_attribute := modifier_line_rec.substitution_attribute;
ip_modifiers_tbl(j).substitution_context := modifier_line_rec.substitution_context;
ip_modifiers_tbl(j).substitution_value := modifier_line_rec.substitution_value;
ip_modifiers_tbl(j).accrual_flag := modifier_line_rec.accrual_flag;
ip_modifiers_tbl(j).pricing_group_sequence := modifier_line_rec.pricing_group_sequence;
ip_modifiers_tbl(j).incompatibility_grp_code := modifier_line_rec.incompatibility_grp_code;
/*ip_modifiers_tbl(j).rltd_modifier_id := modifier_line_rec.rltd_modifier_id;
ip_modifiers_tbl(j).from_rltd_modifier_id := modifier_line_rec.from_rltd_modifier_id;
ip_modifiers_tbl(j).to_rltd_modifier_id := modifier_line_rec.to_rltd_modifier_id;
ip_modifiers_tbl(j).rltd_modifier_grp_no := modifier_line_rec.rltd_modifier_grp_no;
ip_modifiers_tbl(j).rltd_modifier_grp_type := modifier_line_rec.rltd_modifier_grp_type;*/
ip_modifiers_tbl(j).pricing_phase_id := modifier_line_rec.pricing_phase_id;
ip_modifiers_tbl(j).product_precedence := modifier_line_rec.product_precedence;
ip_modifiers_tbl(j).expiration_period_start_date := modifier_line_rec.expiration_period_start_date;
ip_modifiers_tbl(j).number_expiration_periods := modifier_line_rec.number_expiration_periods;
ip_modifiers_tbl(j).expiration_period_uom := modifier_line_rec.expiration_period_uom;
ip_modifiers_tbl(j).expiration_date := modifier_line_rec.expiration_date;
ip_modifiers_tbl(j).estim_gl_value := modifier_line_rec.estim_gl_value;
ip_modifiers_tbl(j).benefit_price_list_line_id := modifier_line_rec.benefit_price_list_line_id;
ip_modifiers_tbl(j).benefit_limit := modifier_line_rec.benefit_limit;
ip_modifiers_tbl(j).charge_type_code := modifier_line_rec.charge_type_code;
ip_modifiers_tbl(j).charge_subtype_code := modifier_line_rec.charge_subtype_code;
ip_modifiers_tbl(j).benefit_qty := modifier_line_rec.benefit_qty;
ip_modifiers_tbl(j).benefit_uom_code := modifier_line_rec.benefit_uom_code;
ip_modifiers_tbl(j).accrual_conversion_rate := modifier_line_rec.accrual_conversion_rate;
ip_modifiers_tbl(j).proration_type_code := modifier_line_rec.proration_type_code;
ip_modifiers_tbl(j).include_on_returns_flag := modifier_line_rec.include_on_returns_flag;
--ip_modifiers_tbl(j).return_status := modifier_line_rec.return_status;
--ip_modifiers_tbl(j).db_flag := modifier_line_rec.db_flag;
ip_modifiers_tbl(j).operation := QP_GLOBALS.G_OPR_CREATE;
--ip_modifiers_tbl(j).Modifier_parent_index := modifier_line_rec.Modifier_parent_index;
ip_modifiers_tbl(j).qualification_ind := modifier_line_rec.qualification_ind;
ip_modifiers_tbl(j).net_amount_flag := modifier_line_rec.net_amount_flag;
ip_modifiers_tbl(j).accum_attribute := modifier_line_rec.accum_attribute;
ip_modifiers_tbl(j).continuous_price_break_flag := modifier_line_rec.continuous_price_break_flag;
ip_pricing_attr_tbl(k).accumulate_flag := modifier_line_rec.accumulate_flag;
ip_pricing_attr_tbl(k).attribute1 := modifier_line_rec.attr_attribute1;
ip_pricing_attr_tbl(k).attribute10 := modifier_line_rec.list_line_id;--modifier_line_rec.attr_attribute10;
ip_pricing_attr_tbl(k).attribute11 := modifier_line_rec.attr_attribute11;
ip_pricing_attr_tbl(k).attribute12 := modifier_line_rec.attr_attribute12;
ip_pricing_attr_tbl(k).attribute13 := modifier_line_rec.attr_attribute13;
ip_pricing_attr_tbl(k).attribute14 := modifier_line_rec.attr_attribute14;
ip_pricing_attr_tbl(k).attribute15 := modifier_line_rec.attr_attribute1;
ip_pricing_attr_tbl(k).attribute2 := modifier_line_rec.attr_attribute2;
ip_pricing_attr_tbl(k).attribute3 := modifier_line_rec.attr_attribute3;
ip_pricing_attr_tbl(k).attribute4 := modifier_line_rec.attr_attribute4;
ip_pricing_attr_tbl(k).attribute5 := modifier_line_rec.attr_attribute5;
ip_pricing_attr_tbl(k).attribute6 := modifier_line_rec.attr_attribute6;
ip_pricing_attr_tbl(k).attribute7 := modifier_line_rec.attr_attribute7;
ip_pricing_attr_tbl(k).attribute8 := modifier_line_rec.attr_attribute8;
ip_pricing_attr_tbl(k).attribute9 := modifier_line_rec.attr_attribute9;
ip_pricing_attr_tbl(k).attribute_grouping_no := modifier_line_rec.attribute_grouping_no;
ip_pricing_attr_tbl(k).context := modifier_line_rec.attr_CONTEXT;
ip_pricing_attr_tbl(k).excluder_flag := modifier_line_rec.excluder_flag;
--ip_pricing_attr_tbl(k).list_line_id := modifier_line_rec.list_line_id;
ip_pricing_attr_tbl(k).pricing_attribute := modifier_line_rec.pricing_attribute;
ip_pricing_attr_tbl(k).pricing_attribute_context := modifier_line_rec.pricing_attribute_context;
ip_pricing_attr_tbl(k).pricing_attr_value_from := modifier_line_rec.pricing_attr_value_from;
ip_pricing_attr_tbl(k).pricing_attr_value_to := modifier_line_rec.pricing_attr_value_to;
ip_pricing_attr_tbl(k).product_attribute := NVL(p_prod_attribute,modifier_line_rec.product_attribute);--modifier_line_rec.product_attribute;
ip_pricing_attr_tbl(k).product_attribute_context := modifier_line_rec.product_attribute_context;
ip_pricing_attr_tbl(k).product_attr_value := p_prod_value;--modifier_line_rec.product_attr_value;
ip_pricing_attr_tbl(k).product_uom_code := modifier_line_rec.attr_uom;
ip_pricing_attr_tbl(k).product_attribute_datatype := modifier_line_rec.product_attribute_datatype;
ip_pricing_attr_tbl(k).pricing_attribute_datatype := modifier_line_rec.pricing_attribute_datatype;
ip_pricing_attr_tbl(k).comparison_operator_code := modifier_line_rec.comparison_operator_code;
--ip_pricing_attr_tbl(k).list_header_id := modifier_line_rec.list_header_id;
ip_pricing_attr_tbl(k).pricing_phase_id := modifier_line_rec.attr_pricing_phase_id;
ip_pricing_attr_tbl(k).pricing_attr_value_from_number := modifier_line_rec.pricing_attr_Value_from_number;
ip_pricing_attr_tbl(k).pricing_attr_value_to_number := modifier_line_rec.pricing_attr_value_to_number;
ip_pricing_attr_tbl(k).qualification_ind := modifier_line_rec.attr_qualification_ind;
ip_pricing_attr_tbl(k).operation := QP_GLOBALS.G_OPR_CREATE;
ip_pricing_attr_tbl(k).MODIFIERS_index := k;
k := k + 1;
j := j + 1;
END;
END LOOP; -- Modifiers(lines) loaded
DBMS_OUTPUT.PUT_LINE('Modifier Line Pricing Attributes Done');
DBMS_OUTPUT.PUT_LINE('CAlling API');
oe_msg_pub.initialize;
qp_modifiers_pub.process_modifiers (p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_true, p_return_values => fnd_api.g_true, p_commit => fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_modifier_list_rec => ip_modifier_list_rec, p_modifiers_tbl => ip_modifiers_tbl, p_pricing_attr_tbl => ip_pricing_attr_tbl, x_modifier_list_rec => op_modifier_list_rec, x_modifier_list_val_rec => op_modifier_list_val_rec, x_modifiers_tbl => op_modifiers_tbl, x_modifiers_val_tbl => op_modifiers_val_tbl, x_qualifiers_tbl => op_qualifiers_tbl, x_qualifiers_val_tbl => op_qualifiers_val_tbl, x_pricing_attr_tbl => op_pricing_attr_tbl, x_pricing_attr_val_tbl => op_pricing_attr_val_tbl );
DBMS_OUTPUT.PUT_LINE('API CALLED and return status: '|| l_return_status);
DBMS_OUTPUT.PUT_LINE('No of Modifoers Loaded: '||op_pricing_attr_tbl.COUNT);
IF(l_return_status = 'S') THEN
FOR i IN 1 .. op_pricing_attr_tbl.COUNT
LOOP
--DBMS_OUTPUT.PUT_LINE('Entered first Line: '|| op_pricing_attr_tbl(i).list_line_id ||' and mapping ID: '|| op_pricing_attr_tbl(i).attribute10);
--DBMS_OUTPUT.PUT_LINE('Line Number: '|| i);
l_q_grp :=1;
l :=1;
IF ip_qualifiers_tbl.COUNT > 0 THEN
ip_qualifiers_tbl.DELETE;
END IF;
l_list_line_id := TO_NUMBER(op_pricing_attr_tbl(i).attribute10);
FOR qualifier_rec IN qualifier_cur(l_list_line_id)
LOOP
l_status := NULL;
ip_qualifiers_tbl(l).qualifier_attr_value := to_number(qualifier_rec.qualifier_attr_value);
ip_qualifiers_tbl(l).attribute1 := qualifier_rec.attribute1;
ip_qualifiers_tbl(l).attribute10 := qualifier_rec.attribute10;
ip_qualifiers_tbl(l).attribute11 := qualifier_rec.attribute11 ;
ip_qualifiers_tbl(l).attribute12 := qualifier_rec.attribute12 ;
ip_qualifiers_tbl(l).attribute13 := qualifier_rec.attribute13 ;
ip_qualifiers_tbl(l).attribute14 := qualifier_rec.attribute14 ;
ip_qualifiers_tbl(l).attribute15 := qualifier_rec.attribute15 ;
ip_qualifiers_tbl(l).attribute2 := qualifier_rec.attribute2 ;
ip_qualifiers_tbl(l).attribute3 := qualifier_rec.attribute3 ;
ip_qualifiers_tbl(l).attribute4 := qualifier_rec.attribute4 ;
ip_qualifiers_tbl(l).attribute5 := qualifier_rec.attribute5 ;
ip_qualifiers_tbl(l).attribute6 := qualifier_rec.attribute6 ;
ip_qualifiers_tbl(l).attribute7 := qualifier_rec.attribute7;
ip_qualifiers_tbl(l).attribute8 := qualifier_rec.attribute8 ;
ip_qualifiers_tbl(l).attribute9 := qualifier_rec.attribute9;
ip_qualifiers_tbl(l).comparison_operator_code := qualifier_rec.comparison_operator_code;
ip_qualifiers_tbl(l).context := qualifier_rec.context;
ip_qualifiers_tbl(l).end_date_active := qualifier_rec.end_date_active;
ip_qualifiers_tbl(l).excluder_flag := qualifier_rec.excluder_flag;
ip_qualifiers_tbl(l).list_header_id := op_pricing_attr_tbl (i).list_header_id;
ip_qualifiers_tbl(l).list_line_id := op_pricing_attr_tbl (i).list_line_id;
ip_qualifiers_tbl(l).qualifier_attribute := qualifier_rec.qualifier_attribute ;
ip_qualifiers_tbl(l).qualifier_attr_value_to := to_number(qualifier_rec.qualifier_attr_value_to);
ip_qualifiers_tbl(l).qualifier_context := qualifier_rec.qualifier_context ;
ip_qualifiers_tbl(l).qualifier_grouping_no := qualifier_rec.qualifier_grouping_no ;
ip_qualifiers_tbl(l).qualifier_precedence := qualifier_rec.qualifier_precedence ;
ip_qualifiers_tbl(l).start_date_active := qualifier_rec.start_date_active ;
ip_qualifiers_tbl(l).operation := qp_globals.g_opr_create;
l := l +1;
l_q_grp := l_q_grp+1;
END LOOP; --Qualifiers Assigned
--END LOOP; --Qualifiers for all modifier Lines
qp_modifiers_pub.process_modifiers (p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_true, p_return_values => fnd_api.g_true, p_commit => fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_qualifiers_tbl => ip_qualifiers_tbl, p_qualifiers_val_tbl => ip_qualifiers_val_tbl, x_modifier_list_rec => op_modifier_list_rec, x_modifier_list_val_rec => op_modifier_list_val_rec, x_modifiers_tbl => op_modifiers_tbl, x_modifiers_val_tbl => op_modifiers_val_tbl, x_qualifiers_tbl => op_qualifiers_tbl, x_qualifiers_val_tbl => op_qualifiers_val_tbl, x_pricing_attr_tbl => op_pricing_attr_tbl1, x_pricing_attr_val_tbl => op_pricing_attr_val_tbl1 );
DBMS_OUTPUT.PUT_LINE('Qualifiers Loaded Successfully');
DBMS_OUTPUT.PUT_LINE('Return Status: '|| l_return_status || ' for line: '|| op_pricing_attr_tbl (i).list_line_id || ' Copied from: '||l_list_line_id);
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE('Error Messages from API: '|| l_msg_data);
END LOOP;
END LOOP;
END IF;
END;
--end loop; -- Modifier List
--DBMS_OUTPUT.put_line ('mod. tbl cnt:' || op_qualifiers_tbl.COUNT);
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE('Error Messages from API: '|| l_msg_data);
END LOOP;
END LOOP; -- Modifier List
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in Procedure: ' || SQLERRM);
END XX_MODIFIER_COPY;
end XX_MODIFIER_COPY_PKG;
/
You can quickly create a new modifier by copying an existing one. The modifier lines, pricing attributes, and qualifiers will be copied from the original modifier to
the new one. The following attributes are copied to the new modifier: price breaks, other item discounts, coupons, promotional goods, setups and line.
If we are in a situation to copy modifier with more number of lines and huge number of Qualifiers attached to those lines with changes in the Product Attributes then use the following package to create new Modifier List
from an existing Modifier List with required changes in the new modifier lines.
Package Specification:
CREATE OR REPLACE
PACKAGE XX_MODIFIER_COPY_PKG
IS
PROCEDURE XX_MODIFIER_COPY(
p_copy_from IN VARCHAR2,
p_name IN VARCHAR2,
p_description IN VARCHAR2,
p_prod_attribute IN VARCHAR2,
p_prod_value IN VARCHAR2 );
END XX_MODIFIER_COPY_PKG;
/
Parameters Description:
p_copy_from ->> Is the Existing Modifier which we you need to copy
p_name --> New Modifier Name
p_description --> New Description
p_prod_attribute --> If there are any changes with respect to Product Attribute from Old to New then pass new value to this parameter. If not, you can pass NULL
Example: From Item Number to Item Category
p_prod_value --> If there are any changes with respect to Product Attribute from Old to New then pass new value to this parameter. If not, you can pass NULL
Package Body:
CREATE OR REPLACE
PACKAGE body XX_MODIFIER_COPY_PKG
IS
PROCEDURE XX_MODIFIER_COPY(
p_copy_from IN VARCHAR2,
p_name IN VARCHAR2,
p_description IN VARCHAR2,
p_prod_attribute IN VARCHAR2,
p_prod_value IN VARCHAR2 )
IS
CURSOR modifier_cur
IS
SELECT *
FROM APPS.QP_LIST_HEADERS QPH
WHERE 1=1
AND qph.name LIKE p_copy_from;--'%IND%ABRIVO%HP%';--'%IND%ABRIVO%INTRO%';
CURSOR modifier_line_cur(p_list_header_id NUMBER)
IS
SELECT qpll.*,--qppa.list_line_id,
qppa.excluder_flag,
qppa.accumulate_flag,
qppa.product_attribute_context,
qppa.product_attribute,
qppa.product_attr_value,
qppa.product_uom_code attr_uom,
qppa.pricing_attribute_context,
qppa.pricing_attribute,
qppa.pricing_attr_value_from,
qppa.pricing_attr_value_to,
qppa.attribute_grouping_no,
qppa.product_attribute_datatype,
qppa.pricing_attribute_datatype,
qppa.comparison_operator_code,
qppa.CONTEXT attr_context,
qppa.attribute1 attr_attribute1,
qppa.attribute2 attr_attribute2,
qppa.attribute3 attr_attribute3,
qppa.attribute4 attr_attribute4,
qppa.attribute5 attr_attribute5,
qppa.attribute6 attr_attribute6,
qppa.attribute7 attr_attribute7,
qppa.attribute8 attr_attribute8,
qppa.attribute9 attr_attribute9,
qppa.attribute10 attr_attribute10,
qppa.attribute11 attr_attribute11,
qppa.attribute12 attr_attribute12,
qppa.attribute13 attr_attribute13,
qppa.attribute14 attr_attribute14,
qppa.attribute15 attr_attribute15,--qppa.list_header_id,
qppa.pricing_phase_id attr_pricing_phase_id,
qppa.qualification_ind attr_qualification_ind,
qppa.pricing_attr_Value_from_number,
qppa.pricing_attr_value_to_number
FROM apps.qp_list_headers qph,
apps.qp_list_lines qpll,
apps.qp_pricing_attributes qppa
WHERE 1 =1
AND qph.list_header_id = qpll.list_header_id
AND qppa.list_line_id = qpll.list_line_id
AND qph.list_header_id = qppa.list_header_id
AND qph.list_header_id = p_list_header_id;
CURSOR qualifier_cur(p_list_line_id NUMBER)
IS
SELECT qpq.*
FROM apps.qp_qualifiers qpq
WHERE 1 =1
AND list_line_id = p_list_line_id;--5084994;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_msg_tbl oe_msg_pub.msg_tbl_type;
l_control_rec qp_globals.control_rec_type;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_output VARCHAR2 (1000);
ip_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
ip_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
ip_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
ip_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
ip_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ip_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ip_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
ip_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
op_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
op_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
op_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
op_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
op_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
op_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
op_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
op_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
op_pricing_attr_tbl1 qp_modifiers_pub.pricing_attr_tbl_type;
op_pricing_attr_val_tbl1 qp_modifiers_pub.pricing_attr_val_tbl_type;
mll_rec qp_list_lines%ROWTYPE;
pra_rec qp_pricing_attributes%ROWTYPE;
t_output VARCHAR2 (1000);
x_debug_file VARCHAR2 (100);
l_user_id NUMBER;
l_responsibility_id NUMBER;
l_application_id NUMBER;
k NUMBER := 1;
i NUMBER := 1;
j NUMBER := 1;
l NUMBER := 1;
pa NUMBER := 1;
l_q_grp NUMBER := 1;
l_list_line_id NUMBER;
l_modifier_number NUMBER;
l_terms_id NUMBER;
nexti NUMBER;
v_list_line_id NUMBER;
l_price_by_formula_id NUMBER;
l_org_id NUMBER;
l_responsibility_name VARCHAR2 (100);
l_status VARCHAR2(10);
l_cust_account_id NUMBER;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE 1 =1
AND user_name = 'CONVERSION';
oe_msg_pub.initialize;
oe_debug_pub.initialize;
--X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.setdebuglevel (5);
l_responsibility_name := 'Order Management Super User';
SELECT responsibility_id,
application_id
INTO l_responsibility_id,
l_application_id
FROM fnd_responsibility_tl
WHERE 1 =1
AND responsibility_name = l_responsibility_name;
fnd_global.apps_initialize ( l_user_id, l_responsibility_id, l_application_id );
mo_global.init ('ONT');
/* Create a Modifier header */
FOR modifier_rec IN modifier_cur
LOOP
BEGIN
ip_modifier_list_rec.attribute1 := modifier_rec.attribute1;
ip_modifier_list_rec.attribute10 := modifier_rec.attribute10;
ip_modifier_list_rec.attribute11 := modifier_rec.attribute11;
ip_modifier_list_rec.attribute12 := modifier_rec.attribute12;
ip_modifier_list_rec.attribute13 := modifier_rec.attribute13;
ip_modifier_list_rec.attribute14 := modifier_rec.attribute14;
ip_modifier_list_rec.attribute15 := modifier_rec.attribute15;
ip_modifier_list_rec.attribute2 := modifier_rec.attribute2;
ip_modifier_list_rec.attribute3 := modifier_rec.attribute3;
ip_modifier_list_rec.attribute4 := modifier_rec.attribute4;
ip_modifier_list_rec.attribute5 := modifier_rec.attribute5;
ip_modifier_list_rec.attribute6 := modifier_rec.attribute6;
ip_modifier_list_rec.attribute7 := modifier_rec.attribute7;
ip_modifier_list_rec.attribute8 := modifier_rec.attribute8;
ip_modifier_list_rec.attribute9 := modifier_rec.attribute9;
ip_modifier_list_rec.automatic_flag := modifier_rec.automatic_flag ;
ip_modifier_list_rec.comments := p_description;--modifier_rec.comments; -- Description Field
ip_modifier_list_rec.context := modifier_rec.context;
ip_modifier_list_rec.currency_code := modifier_rec.currency_code;
ip_modifier_list_rec.discount_lines_flag := modifier_rec.discount_lines_flag;
ip_modifier_list_rec.end_date_active := modifier_rec.end_date_active;
ip_modifier_list_rec.freight_terms_code := modifier_rec.freight_terms_code;
ip_modifier_list_rec.gsa_indicator := modifier_rec.gsa_indicator;
ip_modifier_list_rec.list_type_code := modifier_rec.list_type_code;
ip_modifier_list_rec.prorate_flag := modifier_rec.prorate_flag;
ip_modifier_list_rec.rounding_factor := modifier_rec.rounding_factor;
ip_modifier_list_rec.ship_method_code := modifier_rec.ship_method_code;
ip_modifier_list_rec.start_date_active := modifier_rec.start_date_active;
ip_modifier_list_rec.terms_id := modifier_rec.terms_id;
ip_modifier_list_rec.source_system_code := modifier_rec.source_system_code;
ip_modifier_list_rec.active_flag := modifier_rec.active_flag;
ip_modifier_list_rec.parent_list_header_id := modifier_rec.parent_list_header_id;
ip_modifier_list_rec.start_date_active_first := modifier_rec.start_date_active_first;
ip_modifier_list_rec.end_date_active_first := modifier_rec.end_date_active_first;
ip_modifier_list_rec.active_date_first_type := modifier_rec.active_date_first_type;
ip_modifier_list_rec.start_date_active_second:= modifier_rec.start_date_active_second;
ip_modifier_list_rec.global_flag := modifier_rec.global_flag;
ip_modifier_list_rec.end_date_active_second := modifier_rec.end_date_active_second;
ip_modifier_list_rec.active_date_second_type := modifier_rec.active_date_second_type;
ip_modifier_list_rec.ask_for_flag := modifier_rec.ask_for_flag;
ip_modifier_list_rec.name := p_name;
ip_modifier_list_rec.description := p_name;
ip_modifier_list_rec.version_no := modifier_rec.version_no;
--ip_modifier_list_rec.return_status := modifier_rec.return_status;
--ip_modifier_list_rec.db_flag := modifier_rec.db_flag;
ip_modifier_list_rec.operation := qp_globals.g_opr_create;
ip_modifier_list_rec.pte_code := modifier_rec.pte_code;
ip_modifier_list_rec.list_source_code := modifier_rec.list_source_code;
ip_modifier_list_rec.shareable_flag := modifier_rec.shareable_flag;
ip_modifier_list_rec.org_id := modifier_rec.orig_org_id;
k := 1;
j := 1;
pa := 1;
l :=1;
IF ip_qualifiers_tbl.COUNT > 0 THEN
ip_qualifiers_tbl.DELETE;
END IF;
IF ip_modifiers_tbl.COUNT > 0 THEN
ip_modifiers_tbl.DELETE;
END IF;
IF ip_pricing_attr_tbl.COUNT > 0 THEN
ip_pricing_attr_tbl.DELETE;
END IF;
FOR modifier_line_rec IN modifier_line_cur(modifier_rec.list_header_id)
LOOP
BEGIN
ip_modifiers_tbl(j).arithmetic_operator := modifier_line_rec.arithmetic_operator;
ip_modifiers_tbl(j).attribute1 := modifier_line_rec.attribute1;
ip_modifiers_tbl(j).attribute10 := modifier_line_rec.attribute10;
ip_modifiers_tbl(j).attribute11 := modifier_line_rec.attribute11;
ip_modifiers_tbl(j).attribute12 := modifier_line_rec.attribute12;
ip_modifiers_tbl(j).attribute13 := modifier_line_rec.attribute13;
ip_modifiers_tbl(j).attribute14 := modifier_line_rec.attribute14;
ip_modifiers_tbl(j).attribute15 := modifier_line_rec.attribute15;
ip_modifiers_tbl(j).attribute2 := modifier_line_rec.attribute2;
ip_modifiers_tbl(j).attribute3 := modifier_line_rec.attribute3;
ip_modifiers_tbl(j).attribute4 := modifier_line_rec.attribute4;
ip_modifiers_tbl(j).attribute5 := modifier_line_rec.attribute5;
ip_modifiers_tbl(j).attribute6 := modifier_line_rec.attribute6;
ip_modifiers_tbl(j).attribute7 := modifier_line_rec.attribute7;
ip_modifiers_tbl(j).attribute8 := modifier_line_rec.attribute8;
ip_modifiers_tbl(j).attribute9 := modifier_line_rec.attribute9;
ip_modifiers_tbl(j).automatic_flag := modifier_line_rec.automatic_flag;
ip_modifiers_tbl(j).comments := modifier_line_rec.comments;
ip_modifiers_tbl(j).context := modifier_line_rec.context;
ip_modifiers_tbl(j).effective_period_uom := modifier_line_rec.effective_period_uom;
ip_modifiers_tbl(j).end_date_active := modifier_line_rec.end_date_active;
ip_modifiers_tbl(j).estim_accrual_rate := modifier_line_rec.estim_accrual_rate;
ip_modifiers_tbl(j).generate_using_formula_id := modifier_line_rec.GENERATE_USING_FORMULA_ID;
ip_modifiers_tbl(j).inventory_item_id := modifier_line_rec.inventory_item_id;
--ip_modifiers_tbl(j).list_header_id := modifier_line_rec.list_header_id;
ip_modifiers_tbl(j).list_line_type_code := modifier_line_rec.list_line_type_code;
ip_modifiers_tbl(j).list_price := modifier_line_rec.list_price;
ip_modifiers_tbl(j).modifier_level_code := modifier_line_rec.modifier_level_code;
ip_modifiers_tbl(j).number_effective_periods := modifier_line_rec.number_effective_periods;
ip_modifiers_tbl(j).operand := modifier_line_rec.operand;
ip_modifiers_tbl(j).organization_id := modifier_line_rec.organization_id;
ip_modifiers_tbl(j).override_flag := modifier_line_rec.override_flag;
ip_modifiers_tbl(j).percent_price := modifier_line_rec.percent_price;
ip_modifiers_tbl(j).price_break_type_code := modifier_line_rec.price_break_type_code;
ip_modifiers_tbl(j).price_by_formula_id := modifier_line_rec.price_by_formula_id;
ip_modifiers_tbl(j).primary_uom_flag := modifier_line_rec.primary_uom_flag;
ip_modifiers_tbl(j).print_on_invoice_flag := modifier_line_rec.print_on_invoice_flag;
ip_modifiers_tbl(j).rebate_trxn_type_code := modifier_line_rec.rebate_transaction_type_code;
ip_modifiers_tbl(j).related_item_id := modifier_line_rec.related_item_id;
ip_modifiers_tbl(j).relationship_type_id := modifier_line_rec.relationship_type_id;
ip_modifiers_tbl(j).reprice_flag := modifier_line_rec.reprice_flag;
ip_modifiers_tbl(j).revision := modifier_line_rec.revision;
ip_modifiers_tbl(j).revision_date := modifier_line_rec.revision_date;
ip_modifiers_tbl(j).revision_reason_code := modifier_line_rec.revision_reason_code;
ip_modifiers_tbl(j).start_date_active := modifier_line_rec.start_date_active;
ip_modifiers_tbl(j).substitution_attribute := modifier_line_rec.substitution_attribute;
ip_modifiers_tbl(j).substitution_context := modifier_line_rec.substitution_context;
ip_modifiers_tbl(j).substitution_value := modifier_line_rec.substitution_value;
ip_modifiers_tbl(j).accrual_flag := modifier_line_rec.accrual_flag;
ip_modifiers_tbl(j).pricing_group_sequence := modifier_line_rec.pricing_group_sequence;
ip_modifiers_tbl(j).incompatibility_grp_code := modifier_line_rec.incompatibility_grp_code;
/*ip_modifiers_tbl(j).rltd_modifier_id := modifier_line_rec.rltd_modifier_id;
ip_modifiers_tbl(j).from_rltd_modifier_id := modifier_line_rec.from_rltd_modifier_id;
ip_modifiers_tbl(j).to_rltd_modifier_id := modifier_line_rec.to_rltd_modifier_id;
ip_modifiers_tbl(j).rltd_modifier_grp_no := modifier_line_rec.rltd_modifier_grp_no;
ip_modifiers_tbl(j).rltd_modifier_grp_type := modifier_line_rec.rltd_modifier_grp_type;*/
ip_modifiers_tbl(j).pricing_phase_id := modifier_line_rec.pricing_phase_id;
ip_modifiers_tbl(j).product_precedence := modifier_line_rec.product_precedence;
ip_modifiers_tbl(j).expiration_period_start_date := modifier_line_rec.expiration_period_start_date;
ip_modifiers_tbl(j).number_expiration_periods := modifier_line_rec.number_expiration_periods;
ip_modifiers_tbl(j).expiration_period_uom := modifier_line_rec.expiration_period_uom;
ip_modifiers_tbl(j).expiration_date := modifier_line_rec.expiration_date;
ip_modifiers_tbl(j).estim_gl_value := modifier_line_rec.estim_gl_value;
ip_modifiers_tbl(j).benefit_price_list_line_id := modifier_line_rec.benefit_price_list_line_id;
ip_modifiers_tbl(j).benefit_limit := modifier_line_rec.benefit_limit;
ip_modifiers_tbl(j).charge_type_code := modifier_line_rec.charge_type_code;
ip_modifiers_tbl(j).charge_subtype_code := modifier_line_rec.charge_subtype_code;
ip_modifiers_tbl(j).benefit_qty := modifier_line_rec.benefit_qty;
ip_modifiers_tbl(j).benefit_uom_code := modifier_line_rec.benefit_uom_code;
ip_modifiers_tbl(j).accrual_conversion_rate := modifier_line_rec.accrual_conversion_rate;
ip_modifiers_tbl(j).proration_type_code := modifier_line_rec.proration_type_code;
ip_modifiers_tbl(j).include_on_returns_flag := modifier_line_rec.include_on_returns_flag;
--ip_modifiers_tbl(j).return_status := modifier_line_rec.return_status;
--ip_modifiers_tbl(j).db_flag := modifier_line_rec.db_flag;
ip_modifiers_tbl(j).operation := QP_GLOBALS.G_OPR_CREATE;
--ip_modifiers_tbl(j).Modifier_parent_index := modifier_line_rec.Modifier_parent_index;
ip_modifiers_tbl(j).qualification_ind := modifier_line_rec.qualification_ind;
ip_modifiers_tbl(j).net_amount_flag := modifier_line_rec.net_amount_flag;
ip_modifiers_tbl(j).accum_attribute := modifier_line_rec.accum_attribute;
ip_modifiers_tbl(j).continuous_price_break_flag := modifier_line_rec.continuous_price_break_flag;
ip_pricing_attr_tbl(k).accumulate_flag := modifier_line_rec.accumulate_flag;
ip_pricing_attr_tbl(k).attribute1 := modifier_line_rec.attr_attribute1;
ip_pricing_attr_tbl(k).attribute10 := modifier_line_rec.list_line_id;--modifier_line_rec.attr_attribute10;
ip_pricing_attr_tbl(k).attribute11 := modifier_line_rec.attr_attribute11;
ip_pricing_attr_tbl(k).attribute12 := modifier_line_rec.attr_attribute12;
ip_pricing_attr_tbl(k).attribute13 := modifier_line_rec.attr_attribute13;
ip_pricing_attr_tbl(k).attribute14 := modifier_line_rec.attr_attribute14;
ip_pricing_attr_tbl(k).attribute15 := modifier_line_rec.attr_attribute1;
ip_pricing_attr_tbl(k).attribute2 := modifier_line_rec.attr_attribute2;
ip_pricing_attr_tbl(k).attribute3 := modifier_line_rec.attr_attribute3;
ip_pricing_attr_tbl(k).attribute4 := modifier_line_rec.attr_attribute4;
ip_pricing_attr_tbl(k).attribute5 := modifier_line_rec.attr_attribute5;
ip_pricing_attr_tbl(k).attribute6 := modifier_line_rec.attr_attribute6;
ip_pricing_attr_tbl(k).attribute7 := modifier_line_rec.attr_attribute7;
ip_pricing_attr_tbl(k).attribute8 := modifier_line_rec.attr_attribute8;
ip_pricing_attr_tbl(k).attribute9 := modifier_line_rec.attr_attribute9;
ip_pricing_attr_tbl(k).attribute_grouping_no := modifier_line_rec.attribute_grouping_no;
ip_pricing_attr_tbl(k).context := modifier_line_rec.attr_CONTEXT;
ip_pricing_attr_tbl(k).excluder_flag := modifier_line_rec.excluder_flag;
--ip_pricing_attr_tbl(k).list_line_id := modifier_line_rec.list_line_id;
ip_pricing_attr_tbl(k).pricing_attribute := modifier_line_rec.pricing_attribute;
ip_pricing_attr_tbl(k).pricing_attribute_context := modifier_line_rec.pricing_attribute_context;
ip_pricing_attr_tbl(k).pricing_attr_value_from := modifier_line_rec.pricing_attr_value_from;
ip_pricing_attr_tbl(k).pricing_attr_value_to := modifier_line_rec.pricing_attr_value_to;
ip_pricing_attr_tbl(k).product_attribute := NVL(p_prod_attribute,modifier_line_rec.product_attribute);--modifier_line_rec.product_attribute;
ip_pricing_attr_tbl(k).product_attribute_context := modifier_line_rec.product_attribute_context;
ip_pricing_attr_tbl(k).product_attr_value := p_prod_value;--modifier_line_rec.product_attr_value;
ip_pricing_attr_tbl(k).product_uom_code := modifier_line_rec.attr_uom;
ip_pricing_attr_tbl(k).product_attribute_datatype := modifier_line_rec.product_attribute_datatype;
ip_pricing_attr_tbl(k).pricing_attribute_datatype := modifier_line_rec.pricing_attribute_datatype;
ip_pricing_attr_tbl(k).comparison_operator_code := modifier_line_rec.comparison_operator_code;
--ip_pricing_attr_tbl(k).list_header_id := modifier_line_rec.list_header_id;
ip_pricing_attr_tbl(k).pricing_phase_id := modifier_line_rec.attr_pricing_phase_id;
ip_pricing_attr_tbl(k).pricing_attr_value_from_number := modifier_line_rec.pricing_attr_Value_from_number;
ip_pricing_attr_tbl(k).pricing_attr_value_to_number := modifier_line_rec.pricing_attr_value_to_number;
ip_pricing_attr_tbl(k).qualification_ind := modifier_line_rec.attr_qualification_ind;
ip_pricing_attr_tbl(k).operation := QP_GLOBALS.G_OPR_CREATE;
ip_pricing_attr_tbl(k).MODIFIERS_index := k;
k := k + 1;
j := j + 1;
END;
END LOOP; -- Modifiers(lines) loaded
DBMS_OUTPUT.PUT_LINE('Modifier Line Pricing Attributes Done');
DBMS_OUTPUT.PUT_LINE('CAlling API');
oe_msg_pub.initialize;
qp_modifiers_pub.process_modifiers (p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_true, p_return_values => fnd_api.g_true, p_commit => fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_modifier_list_rec => ip_modifier_list_rec, p_modifiers_tbl => ip_modifiers_tbl, p_pricing_attr_tbl => ip_pricing_attr_tbl, x_modifier_list_rec => op_modifier_list_rec, x_modifier_list_val_rec => op_modifier_list_val_rec, x_modifiers_tbl => op_modifiers_tbl, x_modifiers_val_tbl => op_modifiers_val_tbl, x_qualifiers_tbl => op_qualifiers_tbl, x_qualifiers_val_tbl => op_qualifiers_val_tbl, x_pricing_attr_tbl => op_pricing_attr_tbl, x_pricing_attr_val_tbl => op_pricing_attr_val_tbl );
DBMS_OUTPUT.PUT_LINE('API CALLED and return status: '|| l_return_status);
DBMS_OUTPUT.PUT_LINE('No of Modifoers Loaded: '||op_pricing_attr_tbl.COUNT);
IF(l_return_status = 'S') THEN
FOR i IN 1 .. op_pricing_attr_tbl.COUNT
LOOP
--DBMS_OUTPUT.PUT_LINE('Entered first Line: '|| op_pricing_attr_tbl(i).list_line_id ||' and mapping ID: '|| op_pricing_attr_tbl(i).attribute10);
--DBMS_OUTPUT.PUT_LINE('Line Number: '|| i);
l_q_grp :=1;
l :=1;
IF ip_qualifiers_tbl.COUNT > 0 THEN
ip_qualifiers_tbl.DELETE;
END IF;
l_list_line_id := TO_NUMBER(op_pricing_attr_tbl(i).attribute10);
FOR qualifier_rec IN qualifier_cur(l_list_line_id)
LOOP
l_status := NULL;
ip_qualifiers_tbl(l).qualifier_attr_value := to_number(qualifier_rec.qualifier_attr_value);
ip_qualifiers_tbl(l).attribute1 := qualifier_rec.attribute1;
ip_qualifiers_tbl(l).attribute10 := qualifier_rec.attribute10;
ip_qualifiers_tbl(l).attribute11 := qualifier_rec.attribute11 ;
ip_qualifiers_tbl(l).attribute12 := qualifier_rec.attribute12 ;
ip_qualifiers_tbl(l).attribute13 := qualifier_rec.attribute13 ;
ip_qualifiers_tbl(l).attribute14 := qualifier_rec.attribute14 ;
ip_qualifiers_tbl(l).attribute15 := qualifier_rec.attribute15 ;
ip_qualifiers_tbl(l).attribute2 := qualifier_rec.attribute2 ;
ip_qualifiers_tbl(l).attribute3 := qualifier_rec.attribute3 ;
ip_qualifiers_tbl(l).attribute4 := qualifier_rec.attribute4 ;
ip_qualifiers_tbl(l).attribute5 := qualifier_rec.attribute5 ;
ip_qualifiers_tbl(l).attribute6 := qualifier_rec.attribute6 ;
ip_qualifiers_tbl(l).attribute7 := qualifier_rec.attribute7;
ip_qualifiers_tbl(l).attribute8 := qualifier_rec.attribute8 ;
ip_qualifiers_tbl(l).attribute9 := qualifier_rec.attribute9;
ip_qualifiers_tbl(l).comparison_operator_code := qualifier_rec.comparison_operator_code;
ip_qualifiers_tbl(l).context := qualifier_rec.context;
ip_qualifiers_tbl(l).end_date_active := qualifier_rec.end_date_active;
ip_qualifiers_tbl(l).excluder_flag := qualifier_rec.excluder_flag;
ip_qualifiers_tbl(l).list_header_id := op_pricing_attr_tbl (i).list_header_id;
ip_qualifiers_tbl(l).list_line_id := op_pricing_attr_tbl (i).list_line_id;
ip_qualifiers_tbl(l).qualifier_attribute := qualifier_rec.qualifier_attribute ;
ip_qualifiers_tbl(l).qualifier_attr_value_to := to_number(qualifier_rec.qualifier_attr_value_to);
ip_qualifiers_tbl(l).qualifier_context := qualifier_rec.qualifier_context ;
ip_qualifiers_tbl(l).qualifier_grouping_no := qualifier_rec.qualifier_grouping_no ;
ip_qualifiers_tbl(l).qualifier_precedence := qualifier_rec.qualifier_precedence ;
ip_qualifiers_tbl(l).start_date_active := qualifier_rec.start_date_active ;
ip_qualifiers_tbl(l).operation := qp_globals.g_opr_create;
l := l +1;
l_q_grp := l_q_grp+1;
END LOOP; --Qualifiers Assigned
--END LOOP; --Qualifiers for all modifier Lines
qp_modifiers_pub.process_modifiers (p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_true, p_return_values => fnd_api.g_true, p_commit => fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_qualifiers_tbl => ip_qualifiers_tbl, p_qualifiers_val_tbl => ip_qualifiers_val_tbl, x_modifier_list_rec => op_modifier_list_rec, x_modifier_list_val_rec => op_modifier_list_val_rec, x_modifiers_tbl => op_modifiers_tbl, x_modifiers_val_tbl => op_modifiers_val_tbl, x_qualifiers_tbl => op_qualifiers_tbl, x_qualifiers_val_tbl => op_qualifiers_val_tbl, x_pricing_attr_tbl => op_pricing_attr_tbl1, x_pricing_attr_val_tbl => op_pricing_attr_val_tbl1 );
DBMS_OUTPUT.PUT_LINE('Qualifiers Loaded Successfully');
DBMS_OUTPUT.PUT_LINE('Return Status: '|| l_return_status || ' for line: '|| op_pricing_attr_tbl (i).list_line_id || ' Copied from: '||l_list_line_id);
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE('Error Messages from API: '|| l_msg_data);
END LOOP;
END LOOP;
END IF;
END;
--end loop; -- Modifier List
--DBMS_OUTPUT.put_line ('mod. tbl cnt:' || op_qualifiers_tbl.COUNT);
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE('Error Messages from API: '|| l_msg_data);
END LOOP;
END LOOP; -- Modifier List
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error in Procedure: ' || SQLERRM);
END XX_MODIFIER_COPY;
end XX_MODIFIER_COPY_PKG;
/
No comments:
Post a Comment