Loading

Wednesday, May 26, 2010

Price List Import via API (QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST) in R12

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

SET SERVEROUTPUT ON;
DECLARE
  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;

BEGIN
  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');
 
  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      => 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
      COMMIT;
      DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull');
    ELSE
      DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
      ROLLBACK;
      FOR i IN 1 .. v_msg_count
      LOOP
        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;
END;               

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

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

Disclaimer

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.