Group Team
PREREQUISITE
-----------------
Following are prerequisite before updating category for an Item.

  1. Organization must be defined in Oracle.
  2. Item must be defined.
  3. Category set name and Category Combination Should already exists for the Item.
  4. New Category that needs to be assigned to the Item Should be the part of already existing category set name.

In Below example Item 'NewTestItem' is considered. Its inventory item id is 83456 and belong to 'Vision operation' Organization with Org id 106.
Category Set name 'Inventory' with category Set id '1' and Category 'NONE' with category id '5' is already assigned to the Item.
New Category 'TestCategory' with category id 1198 needs to be Updated to the Item.
New Category is part of the Category Set 'Inventory'.
Below Code can be used for Updating the Category set and Category Combination for the item 'NewTestItem'.
Code is tested in 11.5.10


DECLARE

X_RETURN_STATUS  VARCHAR2(1000);
X_ERRORCODE         NUMBER;
X_MSG_COUNT        NUMBER;
X_MSG_DATA          VARCHAR2(1000);
l_error_message       VARCHAR2 (1000) := NULL;
x_msg_index_out      NUMBER;
BEGIN
  --Apps Initialization is available in another section. Use the below link to know in detail
        Apps_Initialize;


   INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT
                                               P_API_VERSION            => 1.0, 
                                                   P_INIT_MSG_LIST         => FND_API.G_FALSE, 
                                                   P_COMMIT                   => FND_API.G_FALSE,
                                                   P_CATEGORY_ID           => 5,
                                                   P_OLD_CATEGORY_ID    => 1198
                                                   P_CATEGORY_SET_ID    => 1
                                                   P_INVENTORY_ITEM_ID  => 83456
                                                   P_ORGANIZATION_ID     => 106,
                                                   X_RETURN_STATUS       => X_RETURN_STATUS, 
                                                   X_ERRORCODE              => X_ERRORCODE, 
                                                   X_MSG_COUNT             => X_MSG_COUNT, 
                                                   X_MSG_DATA               => X_MSG_DATA);

    IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line ('Item Category Assignment using API is Successful');
   ELSE
      BEGIN
         IF (fnd_msg_pub.count_msg > 1)
         THEN
            FOR k IN 1 .. fnd_msg_pub.count_msg
            LOOP
               fnd_msg_pub.get (p_msg_index            => k,
                                             p_encoded            => 'F',
                                             p_data                 => x_msg_data,
                                             p_msg_index_out   => x_msg_index_out
                                            );

               DBMS_OUTPUT.PUT_LINE('x_msg_data:= ' || x_msg_data);
               IF x_msg_data IS NOT NULL
               THEN
                  l_error_message := l_error_message || '-' || x_msg_data;
               END IF;
            END LOOP;
         ELSE
            --Only one error
            fnd_msg_pub.get (p_msg_index             => 1,
                                           p_encoded            => 'F',
                                           p_data                 => x_msg_data,
                                           p_msg_index_out   => x_msg_index_out
                                         );
            l_error_message := x_msg_data;
         END IF;

         DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                               || l_error_message
                              );
         ROLLBACK;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_message := SQLERRM;
            DBMS_OUTPUT.put_line (   'Error encountered by the API is '
                                  || l_error_message
                                 );
      END;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                   (   'Error in Assigning Category to an Item and error is '
                    || SUBSTR (SQLERRM, 1, 200)
                   );
END;
/
1 Response
  1. Sridevi K Says:

    Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment