We’d like to change the next number on the shipment_seq to 40000 to avoid duplication of numbers in an archive shipping system. Will it let me do this in PL/SQL developer with no implications? (Can’t see anywhere within IFS to do this)

Thanks, Chris.

 

 

Chris,

We have done this with the Purchase Order Number sequence (PUR_ORDER_NO), and it appears to work fine. I did check the dependencies for the sequence first, and it is only used in the Purchase Order API package, as one might expect.

SHIPMENT_SEQ appears to be the same way. It is only used in Shipment_API, so it should be safe to change. You will obviously want to make the change in DEV and TEST and rigorously test your Shipment processes to make sure everything looks like you expect.

(I am not an IFS employee nor a consulting partner, so understand I am just an internal developer for a company implementing IFS APPS 10).

Thanks,

Joe Kaufman

Hi @chrisplant,

I also have done this at least twice for customers a few years ago and haven’t heard any complains yet. It’s just a value to be used as the primary key value for the table, so you shouldn’t have any complications as long as the next value generated is unique. 

You can simply run SQL statements like below to achieve this. 

This will set the step size for the next value. Since you want the next value to be 40000 set the value as 20860 as 40000-19139=20861

ALTER SEQUENCE shipment_seq INCREMENT BY 20860;

Now get the next value from the sequence with below. 

SELECT shipment_seq.nextval FROM dual;

This should give you the value as 39999. Once that is verified, set the step size back to 1.

ALTER SEQUENCE shipment_seq INCREMENT BY 1;

Now the next value generated by this sequence will be 40000.

As Joe has mentioned above, make sure to try this on a test environment first and also perform some testing. 

Hope this helps!

Moving upward in an ascending sequence is safe to do. You’ll find your sequences already have holes because sequence IDs that get consumed during a session aren’t reused by another session after an error rollback.

Side note:

In the critical areas of IFS where sequences can’t have holes, the application uses regular tables to manage the numbers, since those kinds of updates get rolled back on error. (For example, see “Enterprise Identities Series” and “Voucher Series per Voucher Type”.) Messing around with those is far less safe from an accounting or compliance perspective.

You can also drop the sequence and create it anew to set a new starting value, and you don’t need to change the step increment in that case. These are the commands I used in SQL Developer for the PUR_ORDER_NO sequence:

DROP SEQUENCE PUR_ORDER_NO

CREATE SEQUENCE PUR_ORDER_NO MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1 START WITH 100000 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;

COMMIT;

This starts the sequence at 100000.

Doing a DROP and CREATE is obviously ill-advised for a busy database! A new purchase order being added during that time would certainly error out, and the user would probably lose that work. In our case, we are implementing this before going live with purchase orders, so it is safe.

Thanks,

Joe Kaufman

Thanks everyone for your input, I’ll give this a go in TEST next week. Much appreciated. Chris.