Item Unassignment and Delete Items

There can be scenarios where an Item is mistakenly assigned to a warehouse but we need to un-assign the item from the warehouse , We have 2 options available for this operation depending on the number of items and the number of warehouses from when there items have to be unassigned.


However note that item un-assignment can only be performed provided no material transactions have been booked for the item.


If the data volume is less users can query the items from Item master and uncheck the check boxes of item assignment to the specific warehouses and then save the changes.


For large data volumes we can have follow the step by step approach as below :

1. Delete Item Costs --> Any cost for the item needs to be deleted. This can be achieved by running DELETE scripts on the tables :
1. cst_item_costs
2. cst_item_cost_details
3. CST_COST_UPDATES
4. CST_STANDARD_COSTS

Once the item costs have been cleared we need to group the items into Deletion Groups.
For this an entry needs to be made in the Table :

1. BOM_DELETE_GROUPS --> 1 Entry is made for each Organization (warehouse) from which the item has to be un-assigned
2. BOM_DELETE_ENTITIES --> Entry for individual item is made

Once the data insert is complete the user needs to navigate the screen for Delete Items , Search the BOM Deletion group , review and delete it to complete item un-assignment

Item Attachment Details - SQL and code

We might be faced with a situation when we have attachment text uploaded for an Item and we wish to see that information . In such a scenario we have the option of opening the item and see the attachment from EBS screen , or use the below SQL to fetch the attachment text for 1 or more items

select long_text,att1.media_id,msib.segment1,text1.rowid row_id,mp.organization_code
  from fnd_documents_long_text text1,
  FND_ATTACHED_DOCS_FORM_VL att1,
  mtl_system_items_b msib,
  mtl_parameters mp
  where text1.media_id = att1.media_id
  and att1.entity_name = 'MTL_SYSTEM_ITEMS'
  and att1.PK1_VALUE = msib.organization_id
  and att1.PK2_VALUE = msib.inventory_item_id
  and mp.organization_id = msib.organization_id
  --and att1.creation_date like sysdate ;

Long Text is the attachment description on the Item.

In another scenario the business knew what is the Item Attachment description and wants to know the Set of items where this description exists, Unfortunately the above SQL cannot be used in this case since the Attachment description is a long text column with datatype LONG  which cannot be used in a SQL WHERE clause , However the requirement can be met by using the script in anonymous block as below : Additional condition and checks can be added as need be.

/* The code will search across item description
     and find the item with the given description
     current code will check all the items where item has attachment description
     text as 'test' , and will display the item details
  */
declare

l_chr_desc varchar2(32000);
l_chr_desc_substr varchar2(100);
l_num_media_id number;

cursor c1 is
select long_text,att1.media_id,msib.segment1,text1.rowid row_id,mp.organization_code
  from fnd_documents_long_text text1,
  FND_ATTACHED_DOCS_FORM_VL att1,
  mtl_system_items_b msib,
  mtl_parameters mp
  where text1.media_id = att1.media_id
  and att1.entity_name = 'MTL_SYSTEM_ITEMS'
  and att1.PK1_VALUE = msib.organization_id
  and att1.PK2_VALUE = msib.inventory_item_id
  and mp.organization_id = msib.organization_id
  --and att1.creation_date like sysdate
  ;

begin

  for c1_rec in C1
  LOOP
     select long_text,media_id
       into l_chr_desc,l_num_media_id
       from apps.fnd_documents_long_text
       where rowid = c1_rec.row_id;

     l_chr_desc_substr := substr(l_chr_desc,1,25);

     If l_chr_desc_substr = 'test'
     then
        dbms_output.put_line('l_num_media_id '||l_num_media_id);
        dbms_output.put_line('Item  '||c1_rec.segment1);
        dbms_output.put_line('IWarehouse  '||c1_rec.organization_code);
     end if;

   
  end loop;
 
exception
when others
then
dbms_output.put_line('error  '||SQLERRM);
end;

Any feedback on Improvements is appreciated .

Item Categories -Category Set and Assignment

When a business maintains an inventory an essential requirement is the assignment of the items to specific categories

To understand this we need to have information of what these terminologies exactly represent

We will try to present here a brief overview of the item categories

Category Set : A category set can be said to a template or a schema on the basis of which we want to categorize items ,
Eg : A business may be involved in manufacturing of good which it wants to categorize on the basis of the countries where it wants to sell a product .In such a scenario the business will define a Category : SELL_COUNTRY and then assign items to this category set specifying to which Country the item is sold.

in order to perform this we need to perform some Steps :

1. Define Flex filed Structures for Item Category Sets : i.e,. If the business wishes to categrize items on the basis of SELL_COUNTRY , they need to create a KFF with specifying the details of the segments the combination of which will specify a valid value
2. Define Categories : Once the Flex-field  is defined we need to navigate to the item categories window and specify the values of categories which can be assigned under this category set
3. Once Item categories have been set up we need to navigate to the Category set window and create the Category set with details of the item categories
4. Once the setup is done the individual Items can be assigned to this categories

If we try to portray this scenario in the above sense take the example as below

Business decides to categorize items on the basis of the Regions :

1. Create a Flexfield with say 3 Segments : Continent - Country - State/Region
2. Specify the values for this Flexfield by Defining the Values Eg : Asia - India-Maharashtra 
3. Create a Category Set SELL_COUNTRY and then specify these values , default  values 
4. Assign the items to a valid category

Once this is done business can build reports to identify which goods are available for selling in Particular regions

One of the main uses of this is Item Assignment.

In a business we will have a number of warehouses , Depending on the Item categories we can run the warehouse assignment for a specific set of Category values thus assigning the items to Warehouses

In the above Example if a business has Multiple warehouses in India for which are under a hierarchy Eg : India-Mahrashtra

In this now if there are many items which need to be available to this warehouses , Item Assignment can be run for this hierarchy, Item Category Sell_COUNTRY with Item category as : Asia-India - Maharashtra. Thus all the items under this category will be picked and then assigned to the warehouses in the hierarchy

Going by business operations it is not possible to perform this activity manually for all items, such a categorization makes the business work efficiently and free from manual errors