Payroll: Changing formulas IDs

By default, a formula cannot be changed once the Definition is saved.

To bypass this prohibition, get everyone out of payroll and execute the following line in IBCONSOLE:

ALTER TRIGGER prgcompdef_aupdate INACTIVE

Set the old formula to NULL.

update prtcompdef set formula_id=null where def_id='XXXXX'

Optionally Delete any conflicting employee factor records:

DELETE FROM PRTEMPFACT WHERE DEF_ID='XXXXX';

To preserve the values you could use querygen before deleting or rename the factors and transfer the value by query and then delete.

Then return to IBCONSOLE and execute:

ALTER TRIGGER prgcompdef_aupdate ACTIVE

Now update the company definition with the New formula.

update prtcompdef set formula_id='<New Formula ID>' where def_id='XXXXX'

NOTE: This trigger automatically updates prtcompdef and prtempdef - by disabling it, you are losing this capability. If the payroll you are replacing had employee definition values or company definition values, they will not be deleted.

By adding the new formula after reactivating the trigger, the new prtempdef factors WILL be automatically activated by the trigger. (If the formula is added before the trigger is reactivated and the new formula has compdef/empdef (factor) values, they will not be added and must be done manually.)

Sample Update:

ALTER TRIGGER prgcompdef_aupdate INACTIVE;
UPDATE prtcompdef set formula_id=null where def_id='HSA_CALC';
DELETE FROM  PRTEMPFACT where def_id='HSA_CALC';
ALTER TRIGGER prgcompdef_aupdate ACTIVE;
UPDATE prtcompdef set formula_id='HSA_EMP' where def_id='HSA_CALC';