Product Attribute Set Instance (ASI) Lot Serial Enhancement

The purpose of this post is to provide an alternate method for managing lot and serial data in iDempiere. It uses the existing Attribute Set Instance (ASI) field; however, it changes the ASI field type from the "Product Attribute" to "Search".

Problem Statement

The Attribute architecture was created to support many situations including lot and serialization of product. Because it can do so many things, it does not support basic lot and serial functionality very well. For example:

The ASI field does not allow you to scan in a lot/serial number.
The ASI popup dialog does not handle large volumes of records well.
The ASI popup dialog does not handle null attribute values well.

Solution Statement

Update the Attribute Set Instance (ASI) field to behave like a normal search field instead of using the Product Attribute popup dialog. Here are the benefits:

You can scan in lot/serial numbers from a bar code scanner.
You can perform advanced searches and lookups.
You can have null attributes if you wish.
You do not need to make programming changes if you wish to change the ASI table.

Demonstration

Introduction
0:01:45 Run SQL script
0:02:15 Review changes.
0:03:15 Creating new ASI records
0:07:00 Material Receipt demonstration
- Create new Product with new Attribute Set
- 0:09:00 Create Purchase Order
- 0:09:30 Create Material Receipt
0:10:30 Physical Inventory demonstration
0:12:00 Review SQL script (identifier, quick entry, read only, etc...)
Link to Locator Enhancement
Let me know if you are trying this enhancement

Script

The below script will update your iDempiere instance to support the new way of using Attributes. There is no plugin or pack in necessary. Simply run the below script.

-- WARNING: This script should be run and tested against a "TEST" instance before run on a "PRODUCTION" instance!!

-- Remove M_AttributeSetInstance.M_AttributeSetInstance_ID as an identifier
UPDATE AD_Column SET IsIdentifier='N', IsUpdateable='N',Updated=TO_TIMESTAMP('2014-08-22 13:41:54','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Column_ID=8472
;

-- Set M_AttributeSetInstance.SerNo as an identifier
UPDATE AD_Column SET IsIdentifier='Y', SeqNo=1,Updated=TO_TIMESTAMP('2014-08-22 13:42:10','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Column_ID=8470
;

-- update all occurances of Attribute Set Instances to be a search field instead of an ASI popup
update ad_column
set ad_reference_id = 30
where AD_Element_ID=2019
and AD_Column_ID <> 8472
;

-- set Attribute Set Instance fields to not 'read only'
Update ad_field
set isreadonly='N'
where AD_Tab_ID=758
;

-- set Attribute Set Instance window from "query" to "maintain"
UPDATE AD_Window SET WindowType='M',Updated=TO_TIMESTAMP('2014-08-22 14:03:10','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Window_ID=358
;

-- Enable quick entry of ASI records without needing to zoom to the window and click new.
UPDATE AD_Field SET IsQuickEntry='Y',Updated=TO_TIMESTAMP('2014-08-22 13:55:17','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_Field_ID in (12256, 12260, 12255)
;

Comments

The above SQL statement will not change how forms look. Example includes the Product Info window/form. The Attribute Set Instance field is hard-coded to the Product Attribute from. Interestingly, this form still works as a search tool.