Wednesday, May 26, 2010


Oracle apps allow us to load the price list information via API named QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST.
Below script will help you understand the usage of the above API. This script was tested in R12.1.1 Instance

  v_return_status             VARCHAR2(1) := NULL;
  v_msg_count                 NUMBER      := 0;
  v_msg_data                  VARCHAR2 (2000);
  v_price_list_rec            qp_price_list_pub.price_list_rec_type;
  v_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
  v_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
  v_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
  v_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
  v_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
  v_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
  v_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;

  v_price_list_rec.list_header_id             := 708;
  v_price_list_rec.list_type_code             := 'PRL';
  v_price_list_rec.operation                  := qp_globals.g_opr_update;
  v_price_list_line_tbl(1).list_header_id     := 708;
  v_price_list_line_tbl(1).list_line_id       := fnd_api.g_miss_num;
  v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
  v_price_list_line_tbl(1).operation          := qp_globals.g_opr_create;
  v_price_list_line_tbl(1).operand            := 10;
  v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
  v_price_list_line_tbl(1).start_date_active  := '30-APR-2011';
  v_price_list_line_tbl(1).start_date_active  := '29-MAY-2011';
  v_price_list_line_tbl(1).organization_id    := NULL;
  v_pricing_attr_tbl(1).pricing_attribute_id  := fnd_api.g_miss_num;
  v_pricing_attr_tbl(1).list_line_id          := fnd_api.g_miss_num;
  v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
  v_pricing_attr_tbl(1).product_attribute     := 'PRICING_ATTRIBUTE1';
  v_pricing_attr_tbl(1).product_attr_value    := '2600';
  v_pricing_attr_tbl(1).product_uom_code      := 'KG';   
  v_pricing_attr_tbl(1).excluder_flag         := 'N';
  v_pricing_attr_tbl(1).attribute_grouping_no := 1;
  v_pricing_attr_tbl(1).price_list_line_index := 1;
  v_pricing_attr_tbl(1).operation             := qp_globals.g_opr_create;
  dbms_output.put_line('Calling API to Enter Item Into 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      => v_return_status
      ,x_msg_count          => v_msg_count
      ,x_msg_data           => v_msg_data
      ,p_price_list_rec     => v_price_list_rec
      ,p_price_list_line_tbl=> v_price_list_line_tbl
      ,p_pricing_attr_tbl   => v_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_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
      ,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
    IF v_return_status = fnd_api.g_ret_sts_success THEN
      DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull');
      DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
      FOR i IN 1 .. v_msg_count
        v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
        dbms_output.put_line( i|| ') '|| v_msg_data);
      END LOOP;
    END IF;

2 Responses to “Price List Import via API (QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST) in R12”

Bhaskar Reddy said...
August 30, 2011 at 3:14 AM

Hi here i'm sending my requirement can u send me code for this...

Should update ‘Pricelist ’ with standard cost.
• If item not in ‘Pricelist’ , program should automatically add new item, cost & other required information.
• Able to update column “Static Formula” with “Cost + 15 %”(Will change in future & need flexibility to change in program/definition of Quick code suggested).
• We need to provide a new lookup code to capture the variables Price list name and formula name.
• Mark-up % is built in the formula and hence it need not be in the lookup code.
• user cst_cost_api.get_item_cost(1,:inventory_item_id, organization_id,NULL,NULL) or getting the current cost of an item
• to check the request_id which is updating the above cst table
• as soon as we can get the list of item whose cost is getting updated we can update our price list

Anonymous said...
November 26, 2013 at 12:44 AM

Can you please Update the Script for creating the Price Breaks for the List Line crated using above API..????

Post a Comment


The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.