Advanced Pricing : Specific for Factor Lists and it uses the Public API - QP_MODIFIERS_PUB.Process_Modifiers to create Factor Lists

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;

Advanced Pricing : Public API to create all 7 different types of Formula lines QP_PRICE_FORMULA_PUB.Process_Price_Formula

Oracle Metalink Ref: How to create Pricing Formulas with Factor Lists using Public API's ? (Doc ID 454089.1)


 
SET DOC OFF
SET VERIFY OFF

/* $Header: QPPFXMP1.sql 120.0 2005/06/02 01:07:59 appldev noship $*/
--set serveroutput on

/***************************************************************************
   Sample script which inserts a Pricing Formula with 7 price formula lines,
   each of a different type. This is to demonstrate the 7 formula line types
   that are supported in Pricing Formulas. They are Price List Line (PLL),
   Function (FUNC), List Price(LP), Numeric Constant (NUM), Pricing Attribute
   (PRA), Factor List(ML) and Modifier Value(MV). Basic Pricing only supports 3
   formula line types - Pricing Attribute(PRA), Numeric Constant(NUM) and
   Factor List(ML). All 7 are supported in Advanced Pricing.

   A pricing formula header record and 7 price formula lines are created.
   For the formula line of type 'Factor List', the list_header_id of an already
   existing Factor List is used in this sample script.
  
   This script must be modified by the user such that the column
      gpr_formula_lines_tbl(K).price_modifier_list_id
   is populated with a valid list_header_id of an existing Factor List and
   the column
      gpr_formula_lines_tbl(K).price_list_line_id
   is populated with a valid list_line_id of an existing Price List Line from
   the instance where this script is run.

   Please read the Oracle Pricing User's Guide (Appendix A & B) to understand
   the flex fields and seed data.

****************************************************************************/

REM FILETYPE NOEXEC
REM dbdrv command
REM dbdrv: none

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

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_val_rec                          qp_price_formula_pub.formula_val_rec_type;
   gpr_formula_lines_tbl                        qp_price_formula_pub.formula_lines_tbl_type;
   gpr_formula_lines_val_tbl                    qp_price_formula_pub.formula_lines_val_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;

   k                                            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               := 'Sample1-PF 1025-1';
   gpr_formula_rec.description        := 'Sample Pricing Formula';
   gpr_formula_rec.formula            := 'SQRT(1)*2-NVL(3,4)/5+6';
--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;


   /* Create price formula line 1 of type 'List Price'(LP) */
   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  := 'LP';
gpr_formula_lines_tbl(k).step_number  := 1;
gpr_formula_lines_tbl(k).operation:= qp_globals.g_opr_create;


   /* Create price formula line 2 of type 'Price List Line'(PLL) */
   k  := 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:= 'PLL';
gpr_formula_lines_tbl(k).step_number  := 2;
gpr_formula_lines_tbl(k).price_list_line_id := 293195;
--Corresponds to the list_line_id of the item 'dw01' on the Price --List          
-- 'Testing 1023'.   gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


  /* Create price formula line 3 of type 'Pricing Attribute'(PRA) */
   k    := 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 := 'PRA';
gpr_formula_lines_tbl(k).step_number     := 3;
gpr_formula_lines_tbl(k).pricing_attribute_context:= 'PRICING ATTRIBUTE';
gpr_formula_lines_tbl(k).pricing_attribute:= 'PRICING_ATTRIBUTE12';
 -- Corresponds to the Pricing Attribute 'Insurance Cost'
gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;

   /* Create price formula line 4 of type 'Numeric Constant'(NUM) */
   k    := 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 := 'NUM';
gpr_formula_lines_tbl(k).step_number    := 4;
gpr_formula_lines_tbl(k).numeric_constant := 1000;
gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


   /* Create price formula line 5 of type 'Function'(FUNC) */   -
-- User must customize the QP_CUSTOM.Get_Custom_Price function
-- to return a numeric value and also set the profile option
-- 'QP: Get Custom Price Customized' to 'Yes' at the Site Level to
--successfully use this formula line type (FUNC) in their formulas.
   k := 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 := 'FUNC';
gpr_formula_lines_tbl(k).step_number := 5;
gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


   /* Create price formula line 6 of type 'Factor List'(ML) */

   k  := 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   := 6;
gpr_formula_lines_tbl(k).price_modifier_list_id := 50174;
   -- Corresponds to the list_header_id of an existing Factor List
   -- 'ABC'
 gpr_formula_lines_tbl(k).operation := qp_globals.g_opr_create;


   /* Create price formula line 7 of type 'List Price'(MV) */
   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 := 'MV';
   gpr_formula_lines_tbl(k).step_number  := 7;
   gpr_formula_lines_tbl(k).operation   := qp_globals.g_opr_create;

   --dbms_output.put_line('before process price formula ');

   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;
--dbms_output.put_line('after process price formula ');

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);
      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;

Sample Script for Creating the List Price Using qp_price_list_pub.process_price_list

this Script is tested in 11.5.10.2, so please test the same before using it in your Production instance.

------- Script to Create the Price List Line (Line Type) For an Item In the Price List.
DECLARE
   x_return_status                                   VARCHAR2 (1) := NULL;
   x_msg_count                                       NUMBER := 0;
   x_msg_data                                        VARCHAR2 (2000);
   l_price_list_rec                                  qp_price_list_pub.price_list_rec_type;
   l_price_list_val_rec                              qp_price_list_pub.price_list_val_rec_type;
   l_price_list_line_tbl                             qp_price_list_pub.price_list_line_tbl_type;
   l_price_list_line_val_tbl                         qp_price_list_pub.price_list_line_val_tbl_type;
   l_qualifiers_tbl                                  qp_qualifier_rules_pub.qualifiers_tbl_type;
   l_qualifiers_val_tbl                              qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   l_pricing_attr_tbl                                qp_price_list_pub.pricing_attr_tbl_type;
   l_pricing_attr_val_tbl                            qp_price_list_pub.pricing_attr_val_tbl_type;
   x_price_list_rec                                  qp_price_list_pub.price_list_rec_type;
   x_price_list_val_rec                              qp_price_list_pub.price_list_val_rec_type;
   x_price_list_line_tbl                             qp_price_list_pub.price_list_line_tbl_type;
   x_price_list_line_val_tbl                         qp_price_list_pub.price_list_line_val_tbl_type;
   x_qualifiers_tbl                                  qp_qualifier_rules_pub.qualifiers_tbl_type;
   x_qualifiers_val_tbl                              qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   x_pricing_attr_tbl                                qp_price_list_pub.pricing_attr_tbl_type;
   x_pricing_attr_val_tbl                            qp_price_list_pub.pricing_attr_val_tbl_type;
   k                                                 NUMBER := 1;
   j                                                 NUMBER := 1;
BEGIN
   x_return_status                                                    := NULL;
   x_msg_count                                                        := NULL;
   x_msg_data                                                         := NULL;
   l_price_list_rec.list_header_id                                    := 6007;
   l_price_list_rec.list_type_code                                    := 'PRL';
   l_price_list_rec.operation                                         := qp_globals.g_opr_update;
   l_price_list_line_tbl (1).list_header_id                           := 6007;
   l_price_list_line_tbl (1).list_line_id                             := fnd_api.g_miss_num;
   l_price_list_line_tbl (1).list_line_type_code                      := 'PLL';
   l_price_list_line_tbl (1).operation                                := qp_globals.g_opr_create;
   l_price_list_line_tbl (1).operand                                  := 10;
   l_price_list_line_tbl (1).arithmetic_operator                      := 'UNIT_PRICE';
   l_price_list_line_tbl (1).start_date_active                        := '26-NOV-2013';
   l_price_list_line_tbl (1).organization_id                          := NULL;
   l_pricing_attr_tbl (1).pricing_attribute_id                        := fnd_api.g_miss_num;
   l_pricing_attr_tbl (1).list_line_id                                := fnd_api.g_miss_num;
   l_pricing_attr_tbl (1).product_attribute_context                   := 'ITEM';
   l_pricing_attr_tbl (1).product_attribute                           := 'PRICING_ATTRIBUTE1';
   l_pricing_attr_tbl (1).product_attr_value                          := '809342'; --- Inventory Item id
   l_pricing_attr_tbl (1).product_uom_code                            := 'Ea';
   l_pricing_attr_tbl (1).excluder_flag                               := 'N';
   l_pricing_attr_tbl (1).attribute_grouping_no                       := 1;
   l_pricing_attr_tbl (1).price_list_line_index                       := 1;
   l_pricing_attr_tbl (1).operation                                   := qp_globals.g_opr_create;
   DBMS_OUTPUT.put_line ('Calling qp_price_list_pub.process_price_list API to Define List Price For a Item');
   DBMS_OUTPUT.put_line ('*********************************************************************************');
   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_false
                                       , p_commit                      => fnd_api.g_false
                                       , x_return_status               => x_return_status
                                       , x_msg_count                   => x_msg_count
                                       , x_msg_data                    => x_msg_data
                                       , p_price_list_rec              => l_price_list_rec
                                       , p_price_list_line_tbl         => l_price_list_line_tbl
                                       , p_pricing_attr_tbl            => l_pricing_attr_tbl
                                       , x_price_list_rec              => x_price_list_rec
                                       , x_price_list_val_rec          => x_price_list_val_rec
                                       , x_price_list_line_tbl         => x_price_list_line_tbl
                                       , x_qualifiers_tbl              => x_qualifiers_tbl
                                       , x_qualifiers_val_tbl          => x_qualifiers_val_tbl
                                       , x_pricing_attr_tbl            => x_pricing_attr_tbl
                                       , x_pricing_attr_val_tbl        => x_pricing_attr_val_tbl
                                       , x_price_list_line_val_tbl     => x_price_list_line_val_tbl
                                        );

   IF x_price_list_line_tbl.COUNT > 0
   THEN
      FOR k IN 1 .. x_price_list_line_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('No Of Records Created Successfully : ' || k);
         DBMS_OUTPUT.put_line ('Return Status : ' || x_price_list_line_tbl (k).return_status);
         DBMS_OUTPUT.put_line ('List Line id : ' || x_price_list_line_tbl (k).list_line_id);
      END LOOP;
   END IF;

   IF x_price_list_line_tbl (k).return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('Item loaded successfully into the price list');
   ELSE
      ROLLBACK;
      DBMS_OUTPUT.put_line ('Error While Loading Item in Ptice List');
   END IF;

   FOR k IN 1 .. x_msg_count
   LOOP
      x_msg_data                                                         :=
                                                                         oe_msg_pub.get (p_msg_index                   => k
                                                                                       , p_encoded                     => 'F');
      DBMS_OUTPUT.put_line ('Error While Loading Item in Ptice List : ' || k || ' is: ' || x_msg_data);
   END LOOP;
END;


---- Script to Create the Price Break Header Line type with Price Breaks.

DECLARE
   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_line_tbl                           qp_price_list_pub.price_list_line_tbl_type;
   gpr_pricing_attr_tbl                              qp_price_list_pub.pricing_attr_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_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;
   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
   gpr_price_list_rec.list_header_id                                  := 6007;
   gpr_price_list_rec.list_type_code                                  := 'PRL';
   gpr_price_list_rec.currency_code                                   := 'USD';
   gpr_price_list_rec.operation                                       := qp_globals.g_opr_update;
   k                                                                  := 1;
   gpr_price_list_line_tbl (k).list_line_id                           := fnd_api.g_miss_num;
   gpr_price_list_line_tbl (k).list_line_type_code                    := 'PBH';
   gpr_price_list_line_tbl (k).price_break_type_code                  := 'POINT';
   gpr_price_list_line_tbl (k).operation                              := qp_globals.g_opr_create;
   gpr_price_list_line_tbl (k).product_precedence                     := 220;
   gpr_price_list_line_tbl (k).arithmetic_operator                    := 'UNIT_PRICE';
   j                                                                  := 1;
   gpr_pricing_attr_tbl (j).pricing_attribute_id                      := fnd_api.g_miss_num;
   gpr_pricing_attr_tbl (j).list_line_id                              := fnd_api.g_miss_num;
   gpr_pricing_attr_tbl (j).product_attribute_context                 := 'ITEM';
   gpr_pricing_attr_tbl (j).product_attribute                         := 'PRICING_ATTRIBUTE1';
   gpr_pricing_attr_tbl (j).product_attr_value                        := '797346';
   gpr_pricing_attr_tbl (j).product_uom_code                          := 'Ea';
   gpr_pricing_attr_tbl (j).excluder_flag                             := 'N';
   gpr_pricing_attr_tbl (j).price_list_line_index                     := 1;
   gpr_pricing_attr_tbl (j).operation                                 := qp_globals.g_opr_create;
   k                                                                  := k + 1;
   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).operation                              := qp_globals.g_opr_create;
   gpr_price_list_line_tbl (k).operand                                := 10;
   gpr_price_list_line_tbl (k).arithmetic_operator                    := 'UNIT_PRICE';
   gpr_price_list_line_tbl (k).rltd_modifier_group_no                 := 1;
   gpr_price_list_line_tbl (k).product_precedence                     := 220;
   gpr_price_list_line_tbl (k).price_break_header_index               := 1;
   j                                                                  := j + 1;
   gpr_pricing_attr_tbl (j).pricing_attribute_id                      := fnd_api.g_miss_num;
   gpr_pricing_attr_tbl (j).list_line_id                              := fnd_api.g_miss_num;
   gpr_pricing_attr_tbl (j).product_attribute_context                 := 'ITEM';
   gpr_pricing_attr_tbl (j).product_attribute                         := 'PRICING_ATTRIBUTE1';
   gpr_pricing_attr_tbl (j).product_attr_value                        := '797346';
   gpr_pricing_attr_tbl (j).product_uom_code                          := 'Ea';
   gpr_pricing_attr_tbl (j).pricing_attribute_context                 := 'VOLUME';
   gpr_pricing_attr_tbl (j).pricing_attribute                         := 'PRICING_ATTRIBUTE10';
   gpr_pricing_attr_tbl (j).pricing_attr_value_from                   := '1';
   gpr_pricing_attr_tbl (j).pricing_attr_value_to                     := '20';
   gpr_pricing_attr_tbl (j).comparison_operator_code                  := 'BETWEEN';
   gpr_pricing_attr_tbl (j).excluder_flag                             := 'N';
   gpr_pricing_attr_tbl (j).price_list_line_index                     := 2;
   gpr_pricing_attr_tbl (j).operation                                 := qp_globals.g_opr_create;
   gpr_msg_data                                                       := NULL;
   DBMS_OUTPUT.put_line ('Calling qp_price_list_pub.process_price_list API to Define List Price For a Item');
   DBMS_OUTPUT.put_line ('*********************************************************************************');
   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_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_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
                                       , 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 ppr_price_list_line_tbl.COUNT > 0
   THEN
      FOR k IN 1 .. ppr_price_list_line_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('No Of Records Created Successfully : ' || k);
         DBMS_OUTPUT.put_line ('Return Status : ' || ppr_price_list_line_tbl (k).return_status);
         DBMS_OUTPUT.put_line ('List Line id : ' || ppr_price_list_line_tbl (k).list_line_id);
      END LOOP;
   END IF;

   IF gpr_return_status <> fnd_api.g_ret_sts_success
   THEN
      RAISE fnd_api.g_exc_unexpected_error;
   ELSE
      COMMIT;
   END IF;
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
      gpr_return_status                                                  := fnd_api.g_ret_sts_error;
      DBMS_OUTPUT.put_line ('err msg 1 is : ' || gpr_msg_data);
   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');
         DBMS_OUTPUT.put_line ('err msg ' || k || 'is:  ' || gpr_msg_data);
      END LOOP;
   WHEN OTHERS
   THEN
      gpr_return_status                                                  := fnd_api.g_ret_sts_unexp_error;
      DBMS_OUTPUT.put_line ('err msg 3 is : ' || gpr_msg_data);
END;
/

---- Script to Create the Price Break Header Line type with Price Breaks and attributes.

DECLARE
   x_return_status                                   VARCHAR2 (1) := NULL;
   x_msg_count                                       NUMBER := 0;
   x_msg_data                                        VARCHAR2 (2000);
   l_price_list_rec                                  qp_price_list_pub.price_list_rec_type;
   l_price_list_line_tbl                             qp_price_list_pub.price_list_line_tbl_type;
   l_pricing_attr_tbl                                qp_price_list_pub.pricing_attr_tbl_type;
   x_price_list_rec                                  qp_price_list_pub.price_list_rec_type;
   x_price_list_val_rec                              qp_price_list_pub.price_list_val_rec_type;
   x_price_list_line_tbl                             qp_price_list_pub.price_list_line_tbl_type;
   x_price_list_line_val_tbl                         qp_price_list_pub.price_list_line_val_tbl_type;
   x_pricing_attr_tbl                                qp_price_list_pub.pricing_attr_tbl_type;
   x_pricing_attr_val_tbl                            qp_price_list_pub.pricing_attr_val_tbl_type;
   x_qualifiers_tbl                                  qp_qualifier_rules_pub.qualifiers_tbl_type;
   x_qualifiers_val_tbl                              qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   k                                                 NUMBER := 1;
   j                                                 NUMBER := 1;
   i                                                 NUMBER := 1;
BEGIN
   /* set the list_header_id to g_miss_num */
   l_price_list_rec.list_header_id                                    := 6007;
   l_price_list_rec.list_type_code                                    := 'PRL';
/* you can set the currency of price list to whatever, say FRA */
   l_price_list_rec.currency_code                                     := 'USD';
   l_price_list_rec.operation                                         := qp_globals.g_opr_update;
   --Create a Price List Line of type 'PBH'
   k                                                                  := 1;
   l_price_list_line_tbl (k).list_line_id                             := fnd_api.g_miss_num;
   l_price_list_line_tbl (k).list_line_type_code                      := 'PBH';
   l_price_list_line_tbl (k).price_break_type_code                    := 'POINT';
   l_price_list_line_tbl (k).operation                                := qp_globals.g_opr_create;
   l_price_list_line_tbl (k).operand                                  := 10;
   l_price_list_line_tbl (k).arithmetic_operator                      := 'UNIT_PRICE';
   /*
     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. we use
     PRICE_LIST_LINE_INDEX 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. */
   j                                                                  := 1;
   /* Pricing Attribute( with only Product information) record for Price List Line of type 'PBH' */
   l_pricing_attr_tbl (j).pricing_attribute_id                        := fnd_api.g_miss_num;
   l_pricing_attr_tbl (j).list_line_id                                := fnd_api.g_miss_num;
   l_pricing_attr_tbl (j).product_attribute_context                   := 'ITEM';
   l_pricing_attr_tbl (j).product_attribute                           := 'PRICING_ATTRIBUTE1';
   l_pricing_attr_tbl (j).product_attr_value                          := '809346';
   l_pricing_attr_tbl (j).product_uom_code                            := 'Ea';
   l_pricing_attr_tbl (j).excluder_flag                               := 'N';
   l_pricing_attr_tbl (j).price_list_line_index                       := 1;
   l_pricing_attr_tbl (j).operation                                   := qp_globals.g_opr_create;
   j                                                                  := j + 1;
   /* Pricing Attribute( non Product) record for Price List Line of type 'PBH' */
   l_pricing_attr_tbl (j).pricing_attribute_id                        := fnd_api.g_miss_num;
   l_pricing_attr_tbl (j).list_line_id                                := fnd_api.g_miss_num;
   l_pricing_attr_tbl (j).product_attribute_context                   := 'ITEM';
   l_pricing_attr_tbl (j).product_attribute                           := 'PRICING_ATTRIBUTE1';
   l_pricing_attr_tbl (j).product_attr_value                          := '809346';
   l_pricing_attr_tbl (j).product_uom_code                            := 'Ea';
   l_pricing_attr_tbl (j).pricing_attribute_context                   := 'SERV_DURATION';
   l_pricing_attr_tbl (j).pricing_attribute                           := 'PRICING_ATTRIBUTE1';
   l_pricing_attr_tbl (j).pricing_attr_value_from                     := '1';
   l_pricing_attr_tbl (j).pricing_attr_value_to                       := '10';
   l_pricing_attr_tbl (j).comparison_operator_code                    := 'BETWEEN';
   l_pricing_attr_tbl (j).excluder_flag                               := 'N';
   l_pricing_attr_tbl (j).price_list_line_index                       := 1;
   l_pricing_attr_tbl (j).operation                                   := qp_globals.g_opr_create;

   --Create a Price List Line of type 'PLL', a child price break line
   k                                                                  := k + 1;
   l_price_list_line_tbl (k).list_line_id                             := fnd_api.g_miss_num;
   l_price_list_line_tbl (k).list_line_type_code                      := 'PLL';
   l_price_list_line_tbl (k).operation                                := qp_globals.g_opr_create;
   l_price_list_line_tbl (k).operand                                  := 10;
   l_price_list_line_tbl (k).arithmetic_operator                      := 'UNIT_PRICE';
   l_price_list_line_tbl (k).rltd_modifier_group_no                   := 1;
   l_price_list_line_tbl (k).price_break_header_index                 := 1;
   -- This must point to the PBH line. In this example this is a child of the PBH above where K=1.
   --Create a Pricing Attribute with pricing context as 'Volume' and Pricing Attribute as 'Item Quantity''
   --needed to complete the creation of a child price break line.
   j                                                                  := j + 1;
   /* Pricing Attribute( with specific pricing attribute context and attribute) record
    for Price List Line of type 'PLL' which is a child Price Break Line */
   l_pricing_attr_tbl (j).pricing_attribute_id                        := fnd_api.g_miss_num;
   l_pricing_attr_tbl (j).list_line_id                                := fnd_api.g_miss_num;
   l_pricing_attr_tbl (j).product_attribute_context                   := 'ITEM';
   l_pricing_attr_tbl (j).product_attribute                           := 'PRICING_ATTRIBUTE1';
   l_pricing_attr_tbl (j).product_attr_value                          := '809346';
   l_pricing_attr_tbl (j).product_uom_code                            := 'Ea';
   l_pricing_attr_tbl (j).pricing_attribute_context                   := 'VOLUME';
   l_pricing_attr_tbl (j).pricing_attribute                           := 'PRICING_ATTRIBUTE10';   --'Item Quantity'
   l_pricing_attr_tbl (j).pricing_attr_value_from                     := '10';
   l_pricing_attr_tbl (j).pricing_attr_value_to                       := '20';
   l_pricing_attr_tbl (j).comparison_operator_code                    := 'BETWEEN';
   l_pricing_attr_tbl (j).excluder_flag                               := 'N';
   l_pricing_attr_tbl (j).price_list_line_index                       := 2;
   --Because this is a pricing attribute of the line K = 2
   l_pricing_attr_tbl (j).operation                                   := qp_globals.g_opr_create;
   DBMS_OUTPUT.put_line ('Calling qp_price_list_pub.process_price_list API to Define List Price For a Item');
   DBMS_OUTPUT.put_line ('*********************************************************************************');
   qp_price_list_pub.process_price_list (p_api_version_number          => 1
                                       , p_init_msg_list               => fnd_api.g_false
                                       , p_return_values               => fnd_api.g_false
                                       , p_commit                      => fnd_api.g_false
                                       , x_return_status               => x_return_status
                                       , x_msg_count                   => x_msg_count
                                       , x_msg_data                    => x_msg_data
                                       , p_price_list_rec              => l_price_list_rec
                                       , p_price_list_line_tbl         => l_price_list_line_tbl
                                       , p_pricing_attr_tbl            => l_pricing_attr_tbl
                                       , x_price_list_rec              => x_price_list_rec
                                       , x_price_list_val_rec          => x_price_list_val_rec
                                       , x_price_list_line_tbl         => x_price_list_line_tbl
                                       , x_price_list_line_val_tbl     => x_price_list_line_val_tbl
                                       , x_qualifiers_tbl              => x_qualifiers_tbl
                                       , x_qualifiers_val_tbl          => x_qualifiers_val_tbl
                                       , x_pricing_attr_tbl            => x_pricing_attr_tbl
                                       , x_pricing_attr_val_tbl        => x_pricing_attr_val_tbl
                                        );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      RAISE fnd_api.g_exc_unexpected_error;
   ELSE
      COMMIT;
   END IF;
EXCEPTION
   WHEN fnd_api.g_exc_error
   THEN
      x_return_status                                                    := fnd_api.g_ret_sts_error;
   WHEN fnd_api.g_exc_unexpected_error
   THEN
      x_return_status                                                    := fnd_api.g_ret_sts_unexp_error;

      FOR k IN 1 .. x_msg_count
      LOOP
         x_msg_data                                                         :=
                                                                         oe_msg_pub.get (p_msg_index                   => k
                                                                                       , p_encoded                     => 'F');
         DBMS_OUTPUT.put_line ('err msg ' || k || 'is:  ' || x_msg_data);
      END LOOP;
   WHEN OTHERS
   THEN
      x_return_status                                                    := fnd_api.g_ret_sts_unexp_error;
END;
/