Sample Code Oracle Price List API

Oracle Pricing Covers the basic feature like defining price lists ,discounts,surcharges for the items.

Price Lists : Price Lists allows you to define the prices for the goods.

Qualifiers: Qualifiers controls who qualifies for what. You can define qualifier based on customer groups,order types,order amount etc. and attach this to price list . Whenever sales order is entered
it verifies whether qualifier is applicable for this price list or not

Pricing attributes : This gives you added functionality of defining price based on some attributes.

Modifiers : Price list defines the base prices, Modifier controls the discounts for the price list

Prerequisites setup required for Price Lists 

There are two type of Price list

1.Order Management Price list
2. PO Price List


To execute API we need to set the Profile option , based on which price list you want to run API.

Below are the option to set the various price lists.

FOR OM PRICELIST

  a. QP: Pricing Transaction Entity = Order Fulfillment     and save.

   b. QP: Source System Code to 'Oracle Pricing’ (if not already) and save

FOR PO PRICELIST

Prerequisites setup required for PO. Since query is of PO pricing type so before running API this setup is mandatory

1. be sure to set the following Profile Option values at user level
   a. QP: Pricing Transaction Entity = PROCUREMENT and save.
   b. QP: Source System Code to 'Oracle Purchasing’ (if not already) and save.

-

                                                                                                                                                   

Below is the API script to end date inactive price list. This price list will work only if  its profile Option set to
QP: Pricing Transaction Entity = Order Fulfillment .

DECLARE

   CURSOR curr_price
   IS
      SELECT   QPH.LIST_HEADER_ID,
               QPL.LIST_LINE_ID,
               QPH.NAME,
               QPL.END_DATE_ACTIVE
        FROM   QP_LIST_HEADERS QPH,
               QP_LIST_LINES QPL,
               QP_PRICING_ATTRIBUTES QPA
       WHERE       QPH.LIST_HEADER_ID = QPL.LIST_HEADER_ID
               AND qpa.list_line_iD = qpl.list_line_id
               AND QPA.list_header_id = QPL.LIST_HEADER_ID
               AND QPH.LIST_TYPE_CODE = 'PRL'
               AND QPH.START_DATE_ACTIVE <= '31-MAR-2011'
               AND QPL.END_DATE_ACTIVE IS NULL
               AND QPH.SOURCE_SYSTEM_CODE = 'QP'
               AND QPH.ATTRIBUTE1 IN ('NLSP', 'FACTORS')
               AND QPA.PRICING_ATTRIBUTE_CONTEXT IS NULL;

   /* *************Declaration of variables ****************************  */
   c_return_status             VARCHAR2 (20);
   c_error_data                VARCHAR2 (20000);
   n_msg_count                 NUMBER;
   c_msg_data                  VARCHAR2 (20000);
   n_err_count                 NUMBER;
   Z                           NUMBER;
   J                           NUMBER;
   K                           NUMBER;
   x_return_status             VARCHAR2 (1000);
   x_error_message             VARCHAR2 (1000);
   l_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
   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_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;
   p_end_date                  DATE := SYSDATE;
BEGIN
   l_price_list_line_tbl.DELETE;
   l_pricing_attr_tbl.DELETE;
   l_price_list_line_val_tbl.DELETE;
   l_qualifiers_tbl.DELETE;
   l_qualifiers_val_tbl.DELETE;
   l_pricing_attr_val_tbl.DELETE;
   x_price_list_line_tbl.DELETE;
   x_price_list_line_val_tbl.DELETE;
   x_qualifiers_tbl.DELETE;
   x_qualifiers_val_tbl.DELETE;
   x_pricing_attr_tbl.DELETE;
   x_pricing_attr_val_tbl.DELETE;
   fnd_global.apps_initialize (user_id        => 1422,
                               resp_id        => 55558,
                               resp_appl_id   => 660);
   Z := 0;
   J := 0;
   K := 0;
   J := J + 1;

   FOR i IN curr_price
   LOOP
      l_price_list_line_tbl (J).list_header_id := i.list_header_id;
      l_price_list_line_tbl (J).list_line_id := i.list_line_id;
      l_price_list_line_tbl (J).end_date_active := p_end_date;
      l_price_list_line_tbl (J).operation := 'UPDATE';
      Qp_Price_List_Pub.process_price_list (
         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             => c_return_status,
         x_msg_count                 => n_msg_count,
         x_msg_data                  => c_msg_data,
         p_price_list_rec            => l_price_list_rec,
         p_price_list_val_rec        => l_price_list_val_rec,
         p_price_list_line_tbl       => l_price_list_line_tbl,
         p_price_list_line_val_tbl   => l_price_list_line_val_tbl,
         p_qualifiers_tbl            => l_qualifiers_tbl,
         p_qualifiers_val_tbl        => l_qualifiers_val_tbl,
         p_pricing_attr_tbl          => l_pricing_attr_tbl,
         p_pricing_attr_val_tbl      => l_pricing_attr_val_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
      );
      Z := Z + 1;
   END LOOP;

   COMMIT;
   x_return_status := c_return_status;
   DBMS_OUTPUT.PUT_LINE (c_return_status);
   DBMS_OUTPUT.PUT_LINE ('total rows updated ' || Z);

   IF (c_return_status <> Fnd_Api.g_ret_sts_success)
   THEN
      ROLLBACK;
      Oe_Msg_Pub.Count_And_Get (p_count => n_err_count, p_data => c_error_Data);
      c_error_data := NULL;

      FOR i IN 1 .. n_err_count
      LOOP
         c_msg_Data :=
            Oe_Msg_Pub.Get (p_msg_index   => Oe_Msg_Pub.G_NEXT,
                            p_encoded     => Fnd_Api.G_FALSE);
         c_error_data := c_error_Data || c_msg_data;
      END LOOP;

      x_error_message := 'Error in xxxx_price_list_update :' || c_error_data;

      DBMS_OUTPUT.PUT_LINE ('ERROR DETAILS  ' || c_error_data);
      DBMS_OUTPUT.PUT_LINE ('ERROR DETAILS  ' || c_return_status);
   ELSE
      NULL;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Error in CALL API ' || SQLCODE || ' - ' || SQLERRM
      );
      DBMS_OUTPUT.PUT_LINE (
         'Error in CALL API ' || SQLCODE || ' - ' || SQLERRM
      );
END;
/