How to Change Item Price and other values Using PriceList API (Oracle apps 11i or R12)

qp_list_lines_v is the base view/table for Items PriceList information
select * from qp_list_lines_v
Listed Package (Stored Proc & Function) and PriceList API call helps to change any column in table ‘qp_list_lines_v’
This sample code allows changing the price of given item.

–Package Spec–
CREATE OR REPLACE PACKAGE APPS.XYZ_CHANGE_PRICE_LIST
AS
   PROCEDURE CHANGEITEMPRICE (x_message           OUT NOCOPY VARCHAR2,
                                p_PRODUCTVALUE   IN            VARCHAR2,
                                p_UNITPRICE      IN            VARCHAR2,
                                p_PRICELIST      IN            VARCHAR2);

   FUNCTION GET_PRICELISTID (p_PRICELIST IN VARCHAR2)
      RETURN VARCHAR2;
END XYZ_CHANGE_PRICE_LIST;
/
–Package Spec–
–Package body–
CREATE OR REPLACE PACKAGE BODY APPS.XYZ_CHANGE_PRICE_LIST
AS
   PROCEDURE CHANGEITEMPRICE (x_message           OUT NOCOPY VARCHAR2,
                                p_PRODUCTVALUE   IN            VARCHAR2,
                                p_UNITPRICE      IN            VARCHAR2,
                                p_PRICELIST      IN            VARCHAR2)
   IS
      l_RESULT                    VARCHAR2 (100);
      l_LIST_HEADER_ID            VARCHAR2 (10);

      x_price_list_rec            apps.qp_price_list_pub.price_list_rec_type;
      x_price_list_val_rec        apps.qp_price_list_pub.price_list_val_rec_type;
      x_price_list_line_tbl       apps.qp_price_list_pub.price_list_line_tbl_type;
      x_price_list_line_val_tbl   apps.qp_price_list_pub.price_list_line_val_tbl_type;
      x_qualifiers_tbl            apps.qp_qualifier_rules_pub.qualifiers_tbl_type;
      x_qualifiers_val_tbl        apps.qp_qualifier_rules_pub.qualifiers_val_tbl_type;
      x_pricing_attr_tbl          apps.qp_price_list_pub.pricing_attr_tbl_type;
      x_pricing_attr_val_tbl      apps.qp_price_list_pub.pricing_attr_val_tbl_type;
      k                           NUMBER := 1;
      l_price_list_line_tbl       apps.qp_price_list_pub.price_list_line_tbl_type;
      l_return_status             VARCHAR2 (1) := NULL;
      l_msg_count                 NUMBER := 0;
      l_msg_data                  VARCHAR2 (2000);

      CURSOR c1
      IS
         SELECT   UNIQUE a.LIST_LINE_ID, a.LIST_HEADER_ID
           FROM   APPS.QP_LIST_LINES_V a, APPS.MTL_SYSTEM_ITEMS_FVL b
          WHERE       a.product_attr_value = TO_CHAR (b.inventory_item_id)
                  AND b.Segment1 = p_PRODUCTVALUE
                  AND a.LIST_HEADER_ID = GET_PRICELISTID (p_PRICELIST);
   BEGIN
      FOR rec IN c1
      LOOP
         l_price_list_line_tbl (k).list_line_id := rec.LIST_LINE_ID;
         l_price_list_line_tbl (k).operation := apps.qp_globals.g_opr_update;
         l_price_list_line_tbl (k).OPERAND := p_UNITPRICE;
         –call the API to end date the pricing records for the item
   —

         apps.qp_price_list_pub.process_price_list (
            p_api_version_number        => 1,
            p_init_msg_list             => apps.fnd_api.g_false,
            p_return_values             => apps.fnd_api.g_false,
            p_commit                    => apps.fnd_api.g_false,
            x_return_status             => l_return_status,
            x_msg_count                 => l_msg_count,
            x_msg_data                  => l_msg_data,
            p_price_list_line_tbl       => l_price_list_line_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 l_return_status <> apps.fnd_api.g_ret_sts_success
         THEN
            apps.fnd_file.put_line (apps.fnd_file.LOG, ‘API Failure’);

            FOR J3 IN 1 .. l_msg_count
            LOOP
               l_msg_data :=
                  apps.oe_msg_pub.get (p_msg_index => J3, p_encoded => ‘F’);
               apps.fnd_file.put_line (
                  apps.fnd_file.LOG,
                  ‘err msg ‘ || J3 || ‘ is: ‘ || l_msg_data
               );
            END LOOP;
         ELSE
            apps.fnd_file.put_line (
               apps.fnd_file.LOG,
               ‘Tthe Price list Line, API SUCCESS’ || l_return_status
            );
         END IF;
      –DBMS_OUTPUT.put_line (‘l_return_status: ‘ || l_return_status);
      END LOOP;

      x_message := l_return_status;
   END CHANGEITEMPRICE;

   FUNCTION GET_PRICELISTID (p_PRICELIST IN VARCHAR2)
      RETURN VARCHAR2
   IS
      l_RESULT   VARCHAR2 (50);
      l_COUNT    NUMBER;

      CURSOR c1
      IS
         SELECT   PRICE_LIST_ID
           FROM   OE_PRICE_LISTS
          WHERE   name = p_PRICELIST;
   BEGIN
      FOR rec IN c1
      LOOP
         l_RESULT := rec.PRICE_LIST_ID;
      END LOOP;

      RETURN l_RESULT;
   END GET_PRICELISTID;
END XYZ_CHANGE_PRICE_LIST;
/
–Package body–
— To Test—
DECLARE
  X_MESSAGE VARCHAR2(32767);
  P_PRODUCTVALUE VARCHAR2(32767);
  P_UNITPRICE VARCHAR2(32767);
  P_PRICELIST VARCHAR2(32767);

BEGIN
  X_MESSAGE := NULL;
  P_PRODUCTVALUE := ‘1000’;
  P_UNITPRICE := ‘2045.34’;
  P_PRICELIST := ‘RegularPrice’;

  APPS.XYZ_CHANGE_PRICE_LIST.CHANGEITEMPRICE ( X_MESSAGE, P_PRODUCTVALUE, P_UNITPRICE, P_PRICELIST );
  COMMIT;
  DBMS_OUTPUT.put_line (‘X_MESSAGE: ‘ || X_MESSAGE);
END;
— To Test—