PeopleSoft Blog: HCM NA Payroll

Showing posts with label HCM NA Payroll. Show all posts

PS_PAY_DEDUCTION table

Field name – DED_CLASS

Benefit plan set up is as follows:

An employee enrolled into a savings plan where there is some percentage match from employer, TWO rows would be created in PS_PAY_DEDUCTION table with different DED_CLASS values, one as ‘Before-Tax’ and other as ‘Nontaxable Btax Benefit’

How to setup a general deduction:
Define general deduction in deduction table.
Add it to a Company
Override the value for individual employee under Employee Pay data in NA payroll menu

SQL statements in COBOL programs

Advanced COBOL for Structured and Object-Oriented Programming (3rd Edition)

This is the extract from peoplebooks:

Nonstandard SQL Commands

With PeopleSoft Data Mover, you can also use the following nonstandard SQL commands created by PeopleSoft: STORE and ERASE. Use the commands to change COBOL SQL statements in PS_SQLSTMT_TBL.

Example:if you see 'ZZZZZZ_S_PYDATA' in any cobol program, look for a dms script with name ZZZZZZ
and inside that you would see a 'SELECT (S) ' statement.

murachs mainframe cobol By Murach, Mike/ Prince, Anne/ Menendez, Raul

Tables populated after running PSPPYRUN - NA payroll calculation cobol process

--CAN payroll tables

SELECT * FROM PS_CAN_CHECK_YTD;

SELECT * FROM PS_CAN_DED_BALANCE;
SELECT * FROM PS_CAN_ERN_BALANCE;

SELECT * FROM PS_CAN_TAX_BALANCE;

--warning/ information messages

SELECT * FROM PS_BAS_MESSAGE;
SELECT * FROM PS_BEN_BI_MESSAGE;
SELECT * FROM PS_COBRA_MESSAGE;
SELECT * FROM PS_CALC_MESSAGE;

SELECT * FROM PS_DED_MESSAGE;
SELECT * FROM PS_PI_MESSAGE;
SELECT * FROM PS_RETRODED_MSG;
SELECT * FROM PS_RETROPAY_MSG;
SELECT * FROM PS_SV403_MESSAGE;
SELECT * FROM PS_PAY_MESSAGE;

--for current payroll

SELECT * FROM PS_BOND_LOG;
SELECT * FROM PS_DED_ARREARS;
SELECT * FROM PS_DED_PREPAY;
SELECT * FROM PS_FLSA_ADDL_DATA;
SELECT * FROM PS_GARN_EMPL_DED;
SELECT * FROM PS_GVT_CONFIRM_TBL;
SELECT * FROM PS_GVT_PAY_LIMITS;
SELECT * FROM PS_GVT_SF113G_DATA;
SELECT * FROM PS_HP_CONTPAY_DTL;
SELECT * FROM PS_INS_EARNS_BAL;
SELECT * FROM PS_LEAVE_ACCRUAL;
SELECT * FROM PS_PAY_CAL_BAL_ID;
SELECT * FROM PS_PAY_CHECK;
SELECT * FROM PS_PAY_CNTRACT_DTL;
SELECT * FROM PS_PAY_DEDUCTION;
SELECT * FROM PS_PAY_DISTRIBUTN;
SELECT * FROM PS_PAY_EARNINGS;
SELECT * FROM PS_PAY_GARNISH;
SELECT * FROM PS_PAY_GARN_OVRD;
SELECT * FROM PS_PAY_INS_EARNS;
SELECT * FROM PS_PAY_LINE;
SELECT * FROM PS_PAY_ONE_TIME;
SELECT * FROM PS_PAY_OTH_EARNS;
SELECT * FROM PS_PAY_PAGE;
SELECT * FROM PS_PAY_SPCL_EARNS;

--for general balances

SELECT * FROM PS_DEDUCTION_BAL;
SELECT * FROM PS_EARNINGS_BAL;

SELECT * FROM PS_CHECK_YTD;

SELECT * FROM PS_GARN_BALANCE;

SELECT * FROM PS_TAX1042_BALANCE;
SELECT * FROM PS_TAX_BALANCE;

--Time and Labor tables

SELECT * FROM PS_PY_LDTLEMP_RC;
SELECT * FROM PS_PY_LDTLEMP_TBL;
SELECT * FROM PS_PY_LDTLEMP_WRK1;
SELECT * FROM PS_PY_LDTLEMP_WRK2;
SELECT * FROM PS_PY_LDTLPMSG_TBL;
SELECT * FROM PS_PY_LDTL_RC;
SELECT * FROM PS_PY_LDTL_TBL;
SELECT * FROM PS_PY_LDTL_WRK;
SELECT * FROM PS_PY_XREF_WRK;
SELECT * FROM PS_TL_XREFSQ_TAO;
SELECT * FROM PS_TL_XREF_TBL;

--Retro process

SELECT * FROM PS_RETRO_PAYCHECK;

--temp/staging tables with pay_line data used in pay calc process. Some of them are for future use

SELECT * FROM PS_WRK_EARNINGS;
SELECT * FROM PS_WRK_LINE;
SELECT * FROM PS_WRK_PSHUP_TXN;
SELECT * FROM PS_WRK_SEQ_CHECK;

--Tax tables

SELECT * FROM PS_PAY_TAX;
SELECT * FROM PS_PAY_TAX_1042;
SELECT * FROM PS_PAY_TAX_CAN;
SELECT * FROM PS_PAY_TAX_OVRD;
SELECT * FROM PS_PAY_CTX_OVRD;

Tables populated after running PSPPYBLD

--running the PSPPYBLD process - Create Paysheet

SELECT * FROM PS_PAY_CAL_BAL_ID WHERE PAY_END_DT='30-JUN-2011';
SELECT * FROM PS_PAY_CTX_OVRD -- CAN payroll
SELECT * FROM PS_PAY_EARNINGS WHERE PAY_END_DT='30-APR-2011' ;
SELECT * FROM PS_PAY_DEDUCTION WHERE PAY_END_DT='31-JaN-2011';
SELECT * FROM PS_PAY_PAGE WHERE PAY_END_DT='30-JUN-2011';
SELECT * FROM PS_PAY_LINE WHERE PAY_END_DT='31-JaN-2011';
SELECT * FROM PS_PAY_OTH_EARNS WHERE PAY_END_DT='30-JUN-2011';
SELECT * FROM PS_PAY_ONE_TIME WHERE PAY_END_DT='30-JUN-2011';
SELECT * FROM PS_PAY_GARN_OVRD;
SELECT * FROM PS_PAY_TAX_OVRD;

--error/ info messages
SELECT * FROM PS_CALC_MESSAGE
SELECT * FROM PS_COBRA_MESSAGE
SELECT * FROM PS_BAS_MESSAGE;
SELECT * FROM PS_BEN_BI_MESSAGE;
SELECT * FROM PS_DED_MESSAGE WHERE EMPLID='AV19'; -- no rows
SELECT * FROM PS_PAY_MESSAGE;
SELECT * FROM PS_PI_MESSAGE; -- Payroll Interface
SELECT * FROM PS_RETRODED_MSG;
SELECT * FROM PS_RETROPAY_MSG;

--Time and Labor tables
SELECT * FROM PS_PY_LDTLEMP_RC;
SELECT * FROM PS_PY_LDTLEMP_TBL;
SELECT * FROM PS_PY_LDTLEMP_WRK1;
SELECT * FROM PS_PY_LDTLEMP_WRK2;
SELECT * FROM PS_PY_LDTLPMSG_TBL;
SELECT * FROM PS_PY_LDTL_RC;
SELECT * FROM PS_PY_LDTL_TBL;
SELECT * FROM PS_PY_LDTL_WRK;
SELECT * FROM PS_PY_PYBL_TM_WRK;
SELECT * FROM PS_PY_XREF_WRK --Work Record for T&L Update

maximum contribution to Savings Plans 401k and/or 403b

Canon T3i 18.0MP Digital SLR Camera with 18-55mm IS Lens - Digital (Google Affiliate Ad)

HCM 9.1
PeopleTools 8.51.08

Requirement:
Enroll employees into Savings plans where they want to contribute upto the maximum limit set by IRS, but at the same time, distribute the deduction amount over the remaining pay periods of the calendar year.

Here are some of the actual SQLs which I wrote for my requirement, but can easily be tweaked for any specific case.

/*Get IRS defined limit for 402g   = A */
SQLExec("SELECT DED_YTD_MAX FROM PS_LIMIT_TBL A WHERE A.LIMIT_TYPE='402' AND A.EFFDT=(SELECT MAX(B.EFFDT) FROM PS_LIMIT_TBL B WHERE A.LIMIT_TYPE=B.LIMIT_TYPE)", &l_MaxIRSLimit);

/*Get Prev Employer contribution to 401k if any   = B */
SQLExec("SELECT DED_ADJ FROM PS_SAVINGS_MGT_EE WHERE EMPLID=:1 AND COMPANY=:2 AND CALENDAR_YEAR=:3 AND LIMIT_TYPE='402' AND LIMIT_EXT_TYPE='A'", xxx_AET.EMPLID, xxx_AET.COMPANY, &CurrCalYear, &l_401k_AdjAmtPrevEmployer);

/*Get current year YTD balance   = C */
SQLExec("SELECT DED_YTD FROM PS_DEDUCTION_BAL WHERE EMPLID=:1 AND PLAN_TYPE IN (40,46) AND DED_CLASS='B' AND COMPANY=:2 AND BALANCE_ID='CY' AND BALANCE_YEAR=:3 AND BENEFIT_RCD_NBR=:4 ", xxx_AET.EMPLID, xxx_AET.COMPANY, &CurrCalYear, xxx_AET.BENEFIT_RCD_NBR, &l_401K_CurrYrYTDBal);

/*Calculate remaining pay periods for this year = D */
&l_RemainingPayPeriodsThisYr = 8;

/*Perform (A - B - C)/D */
&l_401kDedAmount = (&l_MaxIRSLimit + &l_401k_AdjAmtPrevEmployer + &l_401K_CurrYrYTDBal) / &l_RemainingPayPeriodsThisYr;

/*Call the Savings Plan Component Interface to insert a new row
Name of the component - SAVINGS_PLANS*/

Some useful navigations related to this requirement:
Main > Benefits > Enroll in Benefits > Savings Plans (Employee deductions for enrolled plans)

Main > Benefits > Enroll in Benefits > Savings Management
(Employee deductions that have already happened e.g. 401k contribution at previous employer)

Main > Set Up HRMS > Product Related > Base Benefits > Rates and Rules > Limit Table
(define IRS limit for 402g deductions)

PAY_LINE table analysis in NA payroll

JOB_CHANGE and LOCKED_FOR_CONF fields

SCENARIO 1

paysheet process is run
pay "intial calc" is run
pay "final calc" is run - on PAY_LINE table, LOCKED_FOR_CONF flag is set to 'Y'
any subsequent "initial calc" runs would reset the LOCKED_FOR_CONF to 'N' and if the pay group 'Automatic Paysheet Update' flag is 'Y', it would pick up
the job data changes

SCENARIO 2

paysheet process is run + pay group 'Automatic Paysheet Update' flag is 'Y' + only "initial calc" process is run
when employee job data changes, PAY_LINE table field 'JOB_CHANGE' would be updated to 'R'
payroll would pick up the changes in next run of pay "initial calc" until or "final calc"
PAY_LINE table field 'JOB_CHANGE' would be updated to 'Y'

SCENARIO 3

paysheet process is run + pay group 'Automatic Paysheet Update' flag is 'Y' + final calc process is run
when employee job data changes, PAY_LINE table field 'JOB_CHANGE' would not be updated to 'R'
payroll would not pick up the changes in next run of pay "initial calc" or "final calc"

Pages