Format of new part number

You can use Workflows to validate if it is 10 digits long and gives a message if not saying it should be 10 digits long and preceded by zeros. But unless there is an API to get it I’m not sure if Workflows can be used to generate the next number in a sequence.

Hi Roger - I suspect that if the workflow would work for you as outlined by Lahirumala, you could probable add some SQL code to the work flow to identify the next appropriate number  - something like:

(I’m not sure on the table names and I have not tested the nesting of the sql functions)

select lpad((max trunc(part_no))+1,10,0) from master_part

You cannot use SQL in workflows. Only projections/APIs.

@CKZREGLI normally the Engineering Part number is given from a DB sequence if left blank.

• ENG_PART_MASTER_SEQ → engineering part
• PART_NUMBER_ID_SEQ → for master part

Everytime a new Engineering Part is created and the eng part no is left blank, the system will get the next number from this sequence.

Hi Marcel,

I tried to query ENG_PART_MASTER_SEQ  and PART_NUMBER_ID_SEQ but both tables were not reachable with quick report .. are those tables in Aurena too ?

@CKZREGLI these are not tables. They are database sequences.

Here is a link to shed some light on how these would work:

https://www.techonthenet.com/oracle/sequences.php

@Marcel.Ausan Happen to have an idea what the functional thought is why there are two sequences that can produce a part number?

I've not checked any coding if a cross check is done between the sequences when a new part number request is done.

@eqbstal no idea why there are 2 sequences. I would assume Part_Catalog_Tab and Engineering Part Revisions are using each it’s own sequence. I would also think it’s better if they share the same sequence, but once again I’m not familiar with the engineering process to be able to tell what was the requirement to have a different sequence.

Hi @Marcel.Ausan , I understand how the sequecne is generated. How is the starting value taken ? Is it hardcoded in the database as 1000000 ?  Is there any place in the client where we can set the starting value for the part no ? 

@Teeni Abeysekara it’s initialized to 1000000 by R&D. There’s no place in the Aurena where you could change this.  

I think it is a shame that the sequence can't be set using Enterprise Identity Series. You will find customers, employees, manufacturers and many more there, but no part number. Weird.

We have the following in place to have part numbering in the format 999-999-999:

select * from
(select '100-' || substr(part_no, 1, 3) || '-' || substr(part_no, 4, 3)  "Free Part Numbers" from 
(SELECT TO_CHAR(LEVEL + range_.min_ - 1, 'FM000000') AS Part_no
        FROM (SELECT '000001' AS min_,
                     '099999' AS max_
                FROM DUAL) range_
  CONNECT BY LEVEL <= range_.max_ - range_.min_ + 1)
      MINUS
      SELECT part_no "Free Part Numbers"
        FROM ifsapp.part_catalog
      MINUS
      SELECT part_no "Free Part Numbers"
        FROM ifsapp.eng_part_master
      MINUS
      SELECT part_no "Free Part Numbers"
        FROM ifsapp.inventory_part
      MINUS
      SELECT part_no "Free Part Numbers"
        FROM ifsapp.purchase_part
  ORDER BY 1
)
where rownum < 11

When a new part is needed, this quick report is used. If you are able to push this in an api, this could be called from the workflow to return just one part each time a new part number is requested.

As you may see the first 3 numbers are always made up of 100. This is to overcome problems with performance. Once we hit something in the neighborhood of 999950 we will think of an update for that.

I put this in for those that want to know which gaps there are in the numbering system and don't want to have any logic in the number. Next it is made out of 3 separate 3-digit parts as this is for easy communication 100, 3, 23 as spoken means 100-003-023 as part number. It also helps overcoming the problem of there are 4 or 5 zeros mentioned in between.

@Marcel.Ausan Thank you for the confirmation