Script for End Date the Price List Line for an Item using API

Script for End Date the Price List Line for an Item.
API: QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST

 

DECLARE

   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 (3000);

   l_user_id                   NUMBER := 12345;

   l_responsibilityid          NUMBER := 12345;

   l_application_id            NUMBER := 660;

BEGIN

   -- Setting the Enviroment --

   mo_global.set_policy_context ('S', 83);

   mo_global.init ('ONT');

   BEGIN

      fnd_global.apps_initialize (user_id        => l_user_id,

                                  resp_id        => l_responsibilityid,

                                  resp_appl_id   => l_application_id);

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line (

            ' Error While Apps Initialization : ' || SQLERRM);

   END;

   l_price_list_line_tbl (k).list_line_id := 123456; --LIST_LINE_ID from qp_list_lines_v table

   l_price_list_line_tbl (k).operation := apps.qp_globals.g_opr_update;

   l_price_list_line_tbl (k).end_date_active := SYSDATE - 1;

   --Calling the API to End date the pricing record for the item

   BEGIN

      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);

   EXCEPTION

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.PUT_LINE ('API Error: ' || SQLERRM);

   END;

   IF l_return_status <> apps.fnd_api.g_ret_sts_success

   THEN

      DBMS_OUTPUT.PUT_LINE ('Error While Updating the End date');

      FOR j3 IN 1 .. l_msg_count

      LOOP

         l_msg_data := apps.oe_msg_pub.get (p_msg_index => j3, p_encoded => 'F');

         DBMS_OUTPUT.PUT_LINE (

            'Error Message ' || j3 || ' is: ' || l_msg_data);

      END LOOP;

   ELSE

      DBMS_OUTPUT.PUT_LINE (

         'End Dated the Price list Line: ' || l_return_status);

   END IF;

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE ('Error While Updating the End date');

END;

/

********************************************************************

Navigation to check whether the Price List line is end dated or not 

********************************************************************

 

Navigate to Responsibility: US OM Super User
                                  ->   Pricing
                                  ->   Price Lists
                                  ->   Price List Setup

Query for Price List Name : USD Current Price List and then

Query for Price List Line in Product value :  <Item Name>

Check if the Item Price is end dated.