Here is an example code of QP price list import.
procedure price_list_import(errbuf out varchar2,
retcode out varchar2,
p_price_list_name in varchar2,
p_price_list_desc in varchar2,
p_line_type_code in varchar2 default ‘PRL’,
p_currency_code in varchar2 default ‘USD’,
p_aritmetic_operator in varchar2 default ‘UNIT_PRICE’,
p_qualifier_context in varchar2 default ‘PO_SUPPLIER’,
p_qualifier_attribute in varchar2 default ‘QUALIFIER_ATTRIBUTE1′,
p_comparison_operator_code in varchar2 default ‘=’,
p_qualifier_precedence in number default 3
)
is
gpr_return_status varchar2 (1) := null;
gpr_msg_count number := 0;
gpr_msg_data varchar2 (2000);
gpr_price_list_rec qp_price_list_pub.price_list_rec_type;
gpr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
gpr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
gpr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
gpr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
gpr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
gpr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
gpr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
k number := 1;
j number := 1;
l_current_inventory_item_id number;
l_current_uom_code mtl_system_items_b.primary_uom_code%type;
l_current_vendor_id number;
l_qual_group_num number := 1;
l_qp_line_id number;
l_line_index number := 1;
cursor c_lines
is
select xeql.*
from xxtg_eam_qp_lines_tmp xeql
where list_line_id = -1
–and inventory_item_id is null
and nvl(success_flag,’N') = ‘N’
and segment1 in (select segment1
from mtl_system_items_b);
cursor c_qualifier
is
select xeql.*
from xxtg_eam_qp_qualifier_vals_tmp xeql
where list_line_id is null
and nvl(success_flag,’N') = ‘N’
–and qualifier_value is null
and qualifier in (select vendor_name
from ap_suppliers);
begin
–dbms_output.put_line(‘after get price list ‘);
/* set the list_header_id to g_miss_num */
gpr_price_list_rec.operation := qp_globals.g_opr_create;
gpr_price_list_rec.list_header_id := fnd_api.g_miss_num;
gpr_price_list_rec.name := p_price_list_name;
gpr_price_list_rec.list_type_code := p_line_type_code;
gpr_price_list_rec.description := p_price_list_desc;
gpr_price_list_rec.currency_code := p_currency_code;
–looping at price list lines. for each line an attribute is created.
for r_lines in c_lines
loop
begin
select inventory_item_id,
primary_uom_code
into l_current_inventory_item_id,
l_current_uom_code
from mtl_system_items_b msib
where msib.segment1 = r_lines.segment1
and rownum = 1;
exception
when no_data_found then
l_current_inventory_item_id := null;
continue;
end;
–QP_LIST_LINES_s
select qp_list_lines_s.nextval
into l_qp_line_id
from dual;
gpr_price_list_line_tbl (k).list_line_id := l_qp_line_id;
gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
gpr_price_list_line_tbl(k).list_line_id := fnd_api.g_miss_num;
gpr_price_list_line_tbl (k).list_line_type_code := ‘PLL’;
gpr_price_list_line_tbl(k).inventory_item_id := l_current_inventory_item_id;
gpr_price_list_line_tbl (k).operand := r_lines.price;
gpr_price_list_line_tbl (k).arithmetic_operator := p_aritmetic_operator;
dbms_output.put_line(‘attribute ‘);
gpr_pricing_attr_tbl (k).list_line_id := l_qp_line_id;
gpr_pricing_attr_tbl (k).operation := qp_globals.g_opr_create;
gpr_pricing_attr_tbl (k).pricing_attribute_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl (k).list_line_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl (k).product_attribute_context := ‘ITEM’;
gpr_pricing_attr_tbl (k).product_attribute := ‘PRICING_ATTRIBUTE1′;
gpr_pricing_attr_tbl (k).excluder_flag := ‘N’;
gpr_pricing_attr_tbl (k).price_list_line_index := k;
gpr_pricing_attr_tbl (k).product_attr_value := l_current_inventory_item_id;
gpr_pricing_attr_tbl (k).product_uom_code := l_current_uom_code;
dbms_output.put_line(‘attribute done’);
l_line_index := l_line_index + 1;
update xxtg.xxtg_eam_qp_lines_tmp tmp
set tmp.inventory_item_id = l_current_inventory_item_id
where tmp.segment1 = r_lines.segment1;
dbms_output.put_line(‘l_current_inventory_item_id: ‘ || l_current_inventory_item_id || ‘ r_lines.segment1: ‘ || r_lines.segment1);
fnd_file.put_line(FND_FILE.LOG,’l_current_inventory_item_id: ‘ || l_current_inventory_item_id || ‘ r_lines.segment1: ‘ || r_lines.segment1);
k := k + 1;
end loop;
/*
product_attr_value stores inventory item id – product_attribute for Item Number
is Pricing_Attribute1 product_attribute_context is ITEM. Each line can have one
or more pricing attributes. PRICE_LIST_LINE_INDEX is used to link the child
(pricing attributes) to the parent(line).
When you have pricing attributes like color, length, width etc, populate the
fields pricing_attribute_context, pricing_attribute, pricing_attr_value_from,
pricing_attr_value_to and comparison_operator_code ( ‘=’ or ‘between’) and
repeat the product_attr_value and its attribute and context for each record.
*/
–looping at qualifier. Here we define vendors that can use this price list.
for r_qualifier in c_qualifier
loop
begin
select vendor_id
into l_current_vendor_id
from ap_suppliers aps
where aps.vendor_name = r_qualifier.qualifier;
exception
when no_data_found then
l_current_vendor_id := null;
continue;
end;
–gpr_qualifiers_tbl(j).list_line_id := fnd_api.g_miss_num;
gpr_qualifiers_tbl (j).operation := qp_globals.g_opr_create;
gpr_qualifiers_tbl (j).qualifier_attr_value := l_current_vendor_id;
gpr_qualifiers_tbl (j).qualifier_context := p_qualifier_context;
gpr_qualifiers_tbl (j).qualifier_attribute := p_qualifier_attribute;
gpr_qualifiers_tbl (j).comparison_operator_code := p_comparison_operator_code;
gpr_qualifiers_tbl (j).qualifier_precedence := p_qualifier_precedence;
gpr_qualifiers_tbl (j).qualifier_grouping_no := l_qual_group_num;
l_qual_group_num := l_qual_group_num + 1;
update xxtg.xxtg_eam_qp_qualifier_vals_tmp
set QUALIFIER_VALUE = l_current_vendor_id
where qualifier = r_qualifier.qualifier;
dbms_output.put_line(‘l_current_vendor_id: ‘ || l_current_vendor_id || ‘ r_lines.segment1: ‘ || r_qualifier.qualifier);
fnd_file.put_line(FND_FILE.LOG,’l_current_vendor_id: ‘ || l_current_vendor_id || ‘ r_lines.segment1: ‘ || r_qualifier.qualifier);
j := j + 1;
end loop;
–dbms_output.put_line(‘before process price list ‘);
qp_price_list_pub.process_price_list (
p_api_version_number => 1,
p_init_msg_list => fnd_api.g_true,
p_return_values => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => gpr_return_status,
x_msg_count => gpr_msg_count,
x_msg_data => gpr_msg_data,
p_price_list_rec => gpr_price_list_rec,
p_price_list_line_tbl => gpr_price_list_line_tbl,
p_pricing_attr_tbl => gpr_pricing_attr_tbl,
p_qualifiers_tbl => gpr_qualifiers_tbl,
p_qualifiers_val_tbl => gpr_qualifiers_val_tbl,
x_price_list_rec => ppr_price_list_rec,
x_price_list_val_rec => ppr_price_list_val_rec,
x_price_list_line_tbl => ppr_price_list_line_tbl,
x_price_list_line_val_tbl => ppr_price_list_line_val_tbl,
x_qualifiers_tbl => ppr_qualifiers_tbl,
x_qualifiers_val_tbl => ppr_qualifiers_val_tbl,
x_pricing_attr_tbl => ppr_pricing_attr_tbl,
x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl);
if gpr_return_status <> fnd_api.g_ret_sts_success
then
dbms_output.put_line(‘unsuccessfull:’ || gpr_msg_count || ‘-’ || gpr_msg_data);
fnd_file.put_line(FND_FILE.LOG,’unsuccessfull:’ || gpr_msg_data);
rollback;
for m in 1 .. gpr_msg_count
loop
gpr_msg_data := oe_msg_pub.get (p_msg_index => m, p_encoded => ‘F’);
fnd_file.put_line(FND_FILE.LOG,’exception – ‘ || gpr_msg_data);
dbms_output.put_line(‘exception - ‘ || gpr_msg_data);
end loop;
raise fnd_api.g_exc_unexpected_error;
end if;
if gpr_return_status = fnd_api.g_ret_sts_success
then
dbms_output.put_line(‘success’);
fnd_file.put_line(FND_FILE.LOG,’success’);
update xxtg_eam_qp_lines_tmp
set success_flag = ‘Y’;
update xxtg_eam_qp_qualifier_vals_tmp
set success_flag = ‘Y’;
end if;
exception
when fnd_api.g_exc_error
then
gpr_return_status := fnd_api.g_ret_sts_error;
fnd_file.put_line(FND_FILE.LOG,’exception:’ || gpr_return_status);
rollback;
when fnd_api.g_exc_unexpected_error
then
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
fnd_file.put_line(FND_FILE.LOG,’exception:’ || gpr_return_status);
for k in 1 .. gpr_msg_count
loop
gpr_msg_data := oe_msg_pub.get (p_msg_index => k, p_encoded => ‘F’);
fnd_file.put_line(FND_FILE.LOG,’exception – ‘ || gpr_msg_data);
end loop;
rollback;
when others
then
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
fnd_file.put_line(FND_FILE.LOG,’exception – others:’ || gpr_return_status);
rollback;
end price_list_import;
