Random Number SKU Generator

[peterd]

Hi,
I would like to have as a SKU random generated 6 digit number or 7 digit with the last digit as a checksum.
Is Idempiere got incorporated Random Number Generator?

Thanks
Peter D

[Carlos Antonio Ruiz Gomez]

No, but I think it must be something simple to add - even on database side.

Regards,

Carlos Ruiz

[peterd]

Hi,
Thanks Carlos for info.
In the end I decided to have 5 digit part reference preferably random unique number.
Some of products have EAN-13 barcodes so five digits internal SKU probably is right,
and could form basis if we want to expand to GS1 system in the future.

Thanks
Petr

[peterd]

Hi,

I learned that it could be done with the RANDOM() Function

postgres=# SELECT ROUND(RANDOM() * 100000);
 round
-------
 79544
(1 row)

next i would have to learn how to implement it on a table and I think that should be some check for uniqueness.

Best Regards
Peter

[Carlos Antonio Ruiz Gomez]

Very interesting, thanks for the feedback.

For the " last digit as a checksum" part I found this implementation:
http://wiki.postgresql.org/wiki/Luhn_algorithm

With those functions in postgresql you can define the default for the SKU column:

For example:
- to generate SKU on saving
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))

- to generate SKY as default on product window, define the default for column SKU as:
@SQL=select ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))


Regards,

Carlos Ruiz

--

[peterd]

Carlos,
Thank you for a code it works perfectly.

Best Regards
PeterD

[peterd]

Hi,
I implemented this against my database and got SKU numbers generated as product has been added.
But I just funding that SKU numbers are not unique as I coming across of products with the same SKU number.
How to make sure that generated numbers are unique?

Best Regards
Peter

[Deepak Pansheriya (Logilite.com)]

Peter,

You need to write ModelValidator for Product window to check uniqueness of SKU and if not regenerate number.

If you have expertise on PLSQL, you can write stored procedure and do defaulting as Carlos suggested at above.

[peterd]

Thanks Deepak,

It seems I could not find any  samples how to implement solutions mentioned.
At the moment I got this code applied on m_product table as suggested by Carlos and 5 digit SKU number is created on product window save, but from time to time is duplicated, so numbers are not unique.

ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))

Best Regards
Peter

[peterd]

I do not know if this is right place to ask.
If samebody with database knowledge could implement for me a code that SKU numbers generated could be unique.

Best Regards
Peter

[Deepak Pansheriya (Logilite.com)]

Peter,

Default logic can't help you to validate uniqueness, Either need to write stored procedure for same or need to add validator.

[Jan Thielemann]

I think you should write a function to encapsulate "ltrim(to_char(luhn_generate(

round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in the table.

in pseudo code it would look something like this:

generateMProductSKU(){
    skuGen=""

    needToGenerate = true

    while(needToGenerate){

      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false

   }
   return skuGen

}

[peterd]

Is this code has to replace sku "default value" or function created?

[peterd]

Hi
I would like to thank you all who contributed to this thread in particular Carlos Gomez, Ian Thielemann
and Roxane  Reid-Bennett from postgresql forums whose wrapped up sql code for Random Unique SKU Generator.

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := to_char(luhn_generate(round(random()*10000)::int), '00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();

Best Regards
Peter