Sample Script for Creating the List Price Using qp_price_list_pub.process_price_list

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                   := 'RVBD_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;
/


ref : http://oracleappssolutiions.blogspot.nl/2013/11/sample-script-for-creating-list-price.html