What table is the PR13 - Tax Info 2 tab data stored in? I see using ctrl+O that it says RGP. Is that the PRREGPARM table? If so, how do I get the data for the following fields:
THANKS! Tammy
yes it is the PRREGPARM table and i link it with the EMDEDMASTER and EMPLOYEE tables - not sure if this sql will come through but you can try to use it
SELECT LSF_EMDEDMASTR.COMPANY, LSF_EMDEDMASTR.EMPLOYEE, LSF_EMPLOYEE.LAST_NAME, LSF_EMPLOYEE.FIRST_NAME, LSF_EMPLOYEE.EMAIL_ADDRESS, LSF_EMPLOYEE.PROCESS_LEVEL, LSF_EMDEDMASTR.DED_CODE, LSF_EMDEDMASTR.TAX_EXEMPT_FLG, LSF_EMPLOYEE.TERM_DATE, LSF_PRREGPARM.TOPIC, LSF_PRREGPARM.FLD_NBR, LSF_PRREGPARM.S_VALUE, LSF_PRREGPARM.D_VALUEFROM (LSF_EMDEDMASTR INNER JOIN LSF_EMPLOYEE ON LSF_EMDEDMASTR.EMPLOYEE = LSF_EMPLOYEE.EMPLOYEE) INNER JOIN LSF_PRREGPARM ON LSF_EMPLOYEE.EMPLOYEE = LSF_PRREGPARM.EMPLOYEEWHERE (((LSF_EMDEDMASTR.DED_CODE)="1FIT") AND ((LSF_EMPLOYEE.TERM_DATE)=#1/1/1800#));
also there was a patch that came in to update the HRHIST table for data atributes but of course once you update the fields employees have to change after to be able to see the changes in the PR280 report
Tammy, the fldnbrs for the values you are looking for is 6001 - 6005 and the Tax-ID-Code is whatever you named the Deduction Code for Fed Taxes. To get the field number values for the fields you can query on the PRREGDICT file similar to the PADICT table to find the HR Field Nbrs.
Andrea, Thank you! This got me to what I needed. In case someone else is looking, here is my SQL with all values on one line per person.
SELECT ED.COMPANY, ED.EMPLOYEE, E.LAST_NAME, E.FIRST_NAME, E.EMAIL_ADDRESS, E.PROCESS_LEVEL, ED.DED_CODE, ED.TAX_EXEMPT_FLG, E.TERM_DATE, RGP.TOPIC, max(case when RGP.FLD_NBR = 6001 then cast(RGP.S_VALUE as varchar) else '' end) as FORM_YEAR, max(case when RGP.FLD_NBR = 6002 then cast(RGP.S_VALUE as varchar) else '' end) as MULTIPLE_JOBS, max(case when RGP.FLD_NBR = 6003 then cast(RGP.S_VALUE as varchar) else '' end) as DEPENDENTS, max(case when RGP.FLD_NBR = 6004 then cast(RGP.S_VALUE as varchar) else '' end) as OTHER_INCOME, max(case when RGP.FLD_NBR = 6005 then cast(RGP.S_VALUE as varchar) else '' end) as DEDUCTIONSFROM LSLMDB.ls_apps.EMDEDMASTR EDINNER JOIN LSLMDB.ls_apps.EMPLOYEE E ON ED.EMPLOYEE = E.EMPLOYEEINNER JOIN LSLMDB.ls_apps.PRREGPARM RGP ON E.EMPLOYEE = RGP.EMPLOYEEWHERE ED.DED_CODE = '1001' and RGP.FLD_NBR in (6001,6002,6003,6004,6005)group BY ED.COMPANY, ED.EMPLOYEE, E.LAST_NAME, E.FIRST_NAME, E.EMAIL_ADDRESS, E.PROCESS_LEVEL, ED.DED_CODE, ED.TAX_EXEMPT_FLG, E.TERM_DATE, RGP.TOPIC
JReese, Thank you! This too helped me get to my end result. Appreciate your assistance!
Tammy
