Metalink Ref: How to create Pricing Formulas with Factor Lists using Public API's ? (Doc ID 454089.1)
NOTE:1963675.1 - R12: Advanced Pricing API
How to Set Profile Options for Use With Advanced Pricing (Doc ID 553399.1)
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
/*$Header: QPPFXMP2.sql 120.0 2005/06/02 00:18:46 appldev noship $*/
--set serveroutput on
/***************************************************************************
Sample script which inserts a Pricing Formula and 1 price formula line
of type Factor List(ML) A new Factor List is created in this sample script.
Factor Lists can be created/modified only in the Pricing Formulas Form in
the Factors window if using the application. A factor list once created
in one formula may be used in other formulas as well. Any modification to
a factor list's factors will be reflected in all the formulas using the
factor list.
However, while using API's to create Factor Lists, we use the Modifier API.
A factor list, its factors and pricing attributes use the
same tables as a Modifier List, Modifiers and Pricing Pricing Attributes.
A factor List is a modifier list with a list_type_code of 'PML' and a factor
is a Modifier with a list_line_type_code of 'PMR'.
A pricing formula header record and 1 price formula line of type factor
list('ML') are created. Corresponding to the formula line of type factor
list, 1 Factor List record (Modifier List) and 1 factor record (Modifier)
are created. In this script, for the factor record, a base pricing attribute
record and an associated pricing attribute record are created. The
Modifiers API is used to create the factor list, factor and their
pricing attributes.
Please read the Oracle Pricing User Guide (Appendix A & B) to understand
the flex fields and seed data.
****************************************************************************/
REM FILETYPE NOEXEC
REM dbdrv command
REM dbdrv: none
DECLARE
gpr_return_status VARCHAR2(1) := NULL;
gpr_msg_count NUMBER := 0;
gpr_msg_data VARCHAR2(2000);
gpr_formula_rec qp_price_formula_pub.formula_rec_type;
gpr_formula_lines_tbl qp_price_formula_pub.formula_lines_tbl_type;
ppr_formula_rec qp_price_formula_pub.formula_rec_type;
ppr_formula_val_rec qp_price_formula_pub.formula_val_rec_type;
ppr_formula_lines_tbl qp_price_formula_pub.formula_lines_tbl_type;
ppr_formula_lines_val_tbl qp_price_formula_pub.formula_lines_val_tbl_type; gpr_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
gpr_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
gpr_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
ppr_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
ppr_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
ppr_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
ppr_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type; ppr_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_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;
k NUMBER := 1;
j NUMBER := 1;
i NUMBER := 1;
BEGIN
/* Set the price_formula_id to g_miss_num to Create the Price Formula Record(Header)*/
gpr_formula_rec.price_formula_id := fnd_api.g_miss_num;
gpr_formula_rec.name := 'Sample2-PF 1025-6';
gpr_formula_rec.description := 'Sample Pricing Formula';
gpr_formula_rec.formula := '1';
--Any valid Mathematical Expression including built-in database functions.
--Every operand must correspond to a step_number in a price formula line.
gpr_formula_rec.operation := qp_globals.g_opr_create;
/* Prior to creating the formula line of type 'Factor List', we first create
a Factor List, Factors and pricing attributes using the Modifiers API.
This is because the list_header_id of the Factor_List(Modifier_List) must
be populated in the price_modifier_list_id column of the Formula Line
Record which is a mandatory column when formula_line_type_code is 'ML'. */
/* Create Factor List (Modifier List) record */
gpr_modifier_list_rec.list_header_id := fnd_api.g_miss_num;
gpr_modifier_list_rec.name := 'SAMPLE FACTOR LIST 6';
gpr_modifier_list_rec.currency_code := 'USD';
gpr_modifier_list_rec.list_type_code := 'PML';
--For Factor Lists the Modifier List Type is 'PML'.
gpr_modifier_list_rec.operation := qp_globals.g_opr_create;
/* Create Factor (Modifier) record 1 */
j := 1;
gpr_modifiers_tbl(j).list_header_id := fnd_api.g_miss_num;
gpr_modifiers_tbl(j).list_line_id := fnd_api.g_miss_num;
gpr_modifiers_tbl(j).list_line_type_code := 'PMR';
--For Factors the Modifier Type is 'PMR'.
gpr_modifiers_tbl(j).operand := 0.8;
--Corresponds to the Adjustment Factor
gpr_modifiers_tbl(j).arithmetic_operator := 'UNIT_PRICE';
gpr_modifiers_tbl(j).modifier_level_code := 'NULL';
gpr_modifiers_tbl(j).operation := qp_globals.g_opr_create;
--Any number of Pricing Attributes may be created for a Factor. But
--only the first pricing attribute record is considered as the Base
--Pricing Attribute and all subsequent Pricing Attributes for the
--same Factor will be considered Associated Pricing Attributes. All
--pricing attributes for a factor are treated as 'AND' conditions.
--Factors are treated as 'OR' conditions. User does not have to make
--any special changes or considerations as far as setup goes.
/* Create Pricing Attribute 1 (Base Pricing Attribute) for Factor 1.*/
i := 1;
gpr_pricing_attr_tbl(i).list_line_id:= fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).pricing_attribute_id:= fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).modifiers_index := 1;
--Corresponds to the Factor Number. In this case it is 1.
gpr_pricing_attr_tbl(i).pricing_attribute_context:= 'PRICING ATTRIBUTE';
gpr_pricing_attr_tbl(i).pricing_attribute:= 'PRICING ATTRIBUTE12';
--Corresponds to the Pricing Attribute 'Insurance Cost'
gpr_pricing_attr_tbl(i).pricing_attr_value_from := '100';
gpr_pricing_attr_tbl(i).pricing_attr_value_to := '120';
gpr_pricing_attr_tbl(i).comparison_operator_code := 'BETWEEN';
gpr_pricing_attr_tbl(i).operation := qp_globals.g_opr_create;
/*Create Pricing Attribute 2(Associated Pricing Attribute)for Factor 1.*/
i := i + 1;
gpr_pricing_attr_tbl(i).list_line_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).pricing_attribute_id:= fnd_api.g_miss_num;
gpr_pricing_attr_tbl(i).modifiers_index := 1;
--Corresponds to the Factor Number. In this case it is 1.
gpr_pricing_attr_tbl(i).pricing_attribute_context:= 'PRICING ATTRIBUTE';
gpr_pricing_attr_tbl(i).pricing_attribute:= 'PRICING ATTRIBUTE16';
--Corresponds to the Pricing Attribute 'Freight Cost'
gpr_pricing_attr_tbl(i).pricing_attr_value_from := '11';
gpr_pricing_attr_tbl(i).comparison_operator_code := '=';
gpr_pricing_attr_tbl(i).operation := qp_globals.g_opr_create;
qp_modifiers_pub.process_modifiers(
p_api_version_number => 1.0
, p_init_msg_list => fnd_api.g_false
, p_return_values => fnd_api.g_false
, 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_modifier_list_rec => gpr_modifier_list_rec
, p_modifiers_tbl => gpr_modifiers_tbl
, p_pricing_attr_tbl => gpr_pricing_attr_tbl
, x_modifier_list_rec => ppr_modifier_list_rec
, x_modifier_list_val_rec => ppr_modifier_list_val_rec
, x_modifiers_tbl => ppr_modifiers_tbl
, x_modifiers_val_tbl => ppr_modifiers_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
RAISE fnd_api.g_exc_unexpected_error;
END IF;
/* Create price formula line 1 of type 'Factor List'(ML) */
k := 1;
gpr_formula_lines_tbl(k).price_formula_id := fnd_api.g_miss_num;
gpr_formula_lines_tbl(k).price_formula_line_id:= fnd_api.g_miss_num;
gpr_formula_lines_tbl(k).formula_line_type_code := 'ML';
gpr_formula_lines_tbl(k).step_number := 1;
gpr_formula_lines_tbl(k).price_modifier_list_id := ppr_modifier_list_rec.list_header_id;
-- Corresponds to the list_header_id of the new Factor List
-- created above.
gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;
qp_price_formula_pub.process_price_formula(
p_api_version_number => 1.0
, p_init_msg_list => fnd_api.g_false
, p_return_values => fnd_api.g_false
, 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_formula_rec => gpr_formula_rec
, p_formula_lines_tbl => gpr_formula_lines_tbl
, x_formula_rec => ppr_formula_rec
, x_formula_val_rec => ppr_formula_val_rec
, x_formula_lines_tbl => ppr_formula_lines_tbl
, x_formula_lines_val_tbl => ppr_formula_lines_val_tbl);
IF gpr_return_status <> fnd_api.g_ret_sts_success
THEN
RAISE fnd_api.g_exc_unexpected_error;
END IF;
EXCEPTION
WHEN fnd_api.g_exc_error
THEN
gpr_return_status := fnd_api.g_ret_sts_error;
-- Get message count and data
-- dbms_output.put_line('err msg 1 is : ' || gpr_msg_data);
ROLLBACK;
WHEN fnd_api.g_exc_unexpected_error
THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
--dbms_output.put_line(' msg count 2 is : ' || gpr_msg_count);
FOR k IN 1 .. gpr_msg_count
LOOP
gpr_msg_data := oe_msg_pub.get(p_msg_index => k
, p_encoded => 'F');
/*
oe_msg_pub.Count_And_Get
( p_count => gpr_msg_count
,p_data => gpr_msg_data
);
*/
-- Get message count and data
-- dbms_output.put_line('err msg ' || k ||'is: ' || gpr_msg_data);
NULL;
END LOOP;
ROLLBACK;
WHEN OTHERS
THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error; -- Get message count and data
-- dbms_output.put_line('err msg 3 is : ' || gpr_msg_data);
ROLLBACK;
END;
/
COMMIT;
EXIT;
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.