How is PS Global Payroll formula metadata stored?

There are two main tables

PS_GP_FORMULA and

PS_GP_FORMULA_DTL


The DTL record has a row for each line of the formula.

There are a few useful things you can do in Query or SQL – like finding which formulas reference a given element, or looking for where a particular error/warning is called from (as in this bit of SQL I begged off a person of superior intellect):

SELECT
B.PIN_NM,B.DESCR,A.PIN_NUM,A.FRML_FLD2_DEC_VAL,A.FRML_FLD1_DEC_VAL,Z.FRM
L_FLD2_DEC_VAL,Z.FRML_FLD1_DEC_VAL

FROM PS_GP_FORMULA_DTL A ,PS_GP_FORMULA_DTL Z ,PS_GP_PIN B
WHERE A.PIN_NUM = Z.PIN_NUM
and A.EFFDT = Z.EFFDT
AND A.PIN_NUM=B.PIN_NUM
AND ((A.FRML_FLD2_DAT_TYP='4'
AND A.FRML_FLD2_DEC_VAL='301')
OR
(A.FRML_FLD1_DAT_TYP='4'
AND A.FRML_FLD1_DEC_VAL='301'))
AND((Z.FRML_FLD2_DAT_TYP='4'
AND Z.FRML_FLD2_DEC_VAL='21008 ')
OR
(Z.FRML_FLD1_DAT_TYP='4'
AND Z.FRML_FLD1_DEC_VAL='21008 '))

(The references to 21008 and 301 need to be amended with the appropriate message set number and message as appropriate)

You can also (if you are brave and have suitable access), do some manipulation of the formula content.

There is a vague rumour that PeopleSoft had an in-house text based editor for GP formulas at some point – but they were too frightened of it to allow it out into the wild. It would be great if some enterprising technical person could write something one day – but it will probably never happen. Even an AppEngine that could take a text file as input and parse it into appropriate formula rows would be good and would speed up creation and amendment of formulas immensely. Oh well – I can dream.

Most times if you are making changes, you will want to start out with the last version of the formula you had – however, you may wish to “rip and replace” – which you can do with SQL like this (or similar)


delete from PS_GP_FORMULA_DTL where PIN_NUM =101283 AND EFFDT ='2012-01-01 00:00:00.000' AND SEQ_NUM5 >1

Warning – don’t use this unless you really know what you’re doing and don’t come crying to me if you’ve destroyed the only copy of that 4000 line formula you wrote – you have been warned!