iProcurement Bulk Inactivation/Deletion of Catalog Items

-----------------------------------------------------------------------------------------------------------------------------------------------------

To delete items from iProcurement Catalog/Category tables we can use the following APIs

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--API to delete items from Catalog

    ICX_POR_DELETE_CATALOG.delete_items_in_catalog (p_catalog_name        IN VARCHAR2);

   

    --API to delete items from Category

    ICX_POR_DELETE_CATALOG.delete_items_in_category (

                                    errbuf            OUT NOCOPY VARCHAR2,

                                    retcode           OUT NOCOPY VARCHAR2,

                                    p_rt_category_id   IN NUMBER,

                                    p_category_key     IN VARCHAR2);

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

To delete Item-Category assignment use -  INV_ITEM_CATEGORY_PUB.Delete_Category_Assignment standard API. Following is the sample code:-

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE

   l_return_status       VARCHAR2 (80);

   l_error_code          NUMBER;

   l_msg_count           NUMBER;

   l_msg_data            VARCHAR2 (80);

   l_category_id         NUMBER;

   l_category_set_id     NUMBER;

   l_inventory_item_id   NUMBER;

   l_organization_id     NUMBER;

BEGIN

   SELECT mcs_tl.category_set_id

     INTO l_category_set_id

     FROM mtl_category_sets_tl mcs_tl

    WHERE mcs_tl.category_set_name = 'INV_COLORS_SET';

   SELECT mcb.category_id

     INTO l_category_id

     FROM mtl_categories_b mcb

    WHERE mcb.segment1 = 'RED'

      AND mcb.structure_id =

             (SELECT mcs_b.structure_id

                FROM mtl_category_sets_b mcs_b

               WHERE mcs_b.category_set_id =

                                 (SELECT mcs_tl.category_set_id

                                    FROM mtl_category_sets_tl mcs_tl

                                   WHERE category_set_name = 'INV_COLORS_SET'));

   SELECT DISTINCT (inventory_item_id)

              INTO l_inventory_item_id

              FROM mtl_system_items_kfv msik

             WHERE msik.concatenated_segments = 'Category_Item';

   SELECT organization_id

     INTO l_organization_id

     FROM mtl_parameters

    WHERE organization_code = &P_ORG_CODE;

   inv_item_category_pub.delete_category_assignment

                                  (p_api_version            => 1.0,

                                   p_init_msg_list          => fnd_api.g_false,

                                   p_commit                 => fnd_api.g_true,

                                   x_return_status          => l_return_status,

                                   x_errorcode              => l_error_code,

                                   x_msg_count              => l_msg_count,

                                   x_msg_data               => l_msg_data,

                                   p_category_id            => l_category_id,

                                   p_category_set_id        => l_category_set_id,

                                   p_inventory_item_id      => l_inventory_item_id,

                                   p_organization_id        => l_organization_id

                                  );

END;