Converting Employee GOV T Mandated Orders

The Employee Gov't Mandated Orders functionality has been de-supported for Costpoint 7.0.

If you are upgrading to Costpoint 7.0 from Costpoint 5.x or Costpoint 6.x, a conversion script automatically runs during installation to accomplish the following:

  1. Generate a Manage Employee Garnishments parent record (EMPL_GRN) for each Employee Gov’t Mandated Orders record in the database.
  2. Populate the EMPL_E_DED_GARN table with garnishment information so that the garnishment balance is correct when you close the payroll year.

Generate a Manage Employee Garnishments Parent Record

The conversion scripts use data from the Employee Gov’t Mandated Orders (EMPL_GARN) table to populate the parent Manage Employee Garnishments (EMPL_GRN) table. Because there was no Garnishment ID in the Employee Gov’t Mandated Orders screen, the ID is the Deduction code with the number 1 appended. For example, if the Deduction code from the Employee Gov’t Mandated Orders (EMPL_GARN) table is CSGARN, the Garnishment ID would be CSGARN1.

The Employee Garnishments (EMPL_GRN) records are built as follows:

EMPL_GRN Column Field Data Source
EMPL_ID Employee ID EMPL_GARN.empl_id
GARN_ID Garnishment ID EMPL_GARN.order_ded_cd with a 1 appended
GARN_STATUS_CD Garnishment Status If the EMPL_GARN.active_fl = Y, populate with A (Active).

Otherwise, populate with S (Garnishment Satisfied).

DED_CD Deduction code EMPL_GARN.order_ded_cd
REMIT_ID Remittance ID EMPL_GARN.garn_remit_to
GARN_WORK_ST Work state EMPL_TAX.suta_state_cd where EMPL_TAX.empl_id = EMPL_GARN.empl_id

If the employee’s EMPL_TAX.suta_state_cd is NULL, populate with the EMPL_TAX.wh_state_cd.  

If the employee’s EMPL_TAX.suta_state_cd is NULL and the employee’s EMPL_TAX.wh_state_cd is NULL, populate with the employee's Address State (EMPL.mail_state_cd).

If all three states above are NULL, populate with the State assigned to the employee's Taxable Entity (TAXBLE_ENTITY.mail_state_dc).

If all four states above are NULL, populate with AL.

START_DT Start date EMPL_GARN.order_start_dt
END_DT End date NULL
PRIORITY_NO Garnishment Priority EMPL_GARN.order_priority_no
RECPT_DT Date received EMPL_GARN.order_rec_dt
ISSUE_STATE Issue state NULL
ISSUE_COUNTY Issue county NULL
CASE_NO Court Order/Case number EMPL_GARN.case_no
ACCT_NO Account number EMPL_GARN.acct_no
JURIS Jurisdiction EMPL_GARN.juris
NOTES Notes EMPL_GARN.order_desc
MODIFIED_BY ID of the user who last created or modified row DELTEK
TIME_STAMP Date and time of row creation or last modification System Date and Time
ROWVERSION System-assigned number to handle row concurrency 0

Populate the EMPL_E_DED_GARN Table with Garnishment Information

If the Garnishment Method column from the Payroll Settings table = O for a Company (PR_SETTINGS.garn_mthd_cd = O), another conversion script runs after step #1 to populate the EMPL_E_DED_GARN table with garnishment information for any employees assigned to the company so that the garnishment balance is correct when you close the payroll year. The conversion script uses the newly inserted Manage Employee Garnishment (EMPL_GRN) records to create Employee Earnings Garnishment Deduction records for any garnishment deductions in the Employee Earnings Deductions table. The script selects all Employee Earnings Deductions records where the following are all true:

  • EMPL_EARN_DED.empl_id = EMPL_GRN.empl_id
  • EMPL_EARN_DED.ded_cd = EMPL_GRN.ded_cd
  • EMPL_EARN_DED.chk_dt >= EMPL_GRN.start_dt
  • EMPL_EARN_DED.chk_dt <=  EMPL_GRN.end_dt (if EMPL_GRN.end_dt is not NULL)
  • EMPL_EARN_DED.empl_id is assigned to the Company that has a Garnishment Method of O in the Payroll Settings table.

The script then creates a child EMPL_E_DED_GARN record for each of the selected Employee Earnings Deductions records:

EMPL_E_DED_GARN Column Field Value/Source
EMPL_ID Employee ID EMPL_EARN_DED.empl_id
CHK_DT Check Date EMPL_EARN_DED.chk_dt
S_PAYCHK_TYPE Paycheck Type EMPL_EARN_DED.s_paychk_type
FY_CD Fiscal Year EMPL_EARN_DED.fy_cd
PD_NO Period EMPL_EARN_DED.pd_no
POST_SEQ_NO Posting Sequence # EMPL_EARN_DED.post_seq_no
TAXBLE_ENTITY_ID Taxable Entity EMPL_EARN_DED.taxble_entity_id
DED_CD Deduction Code EMPL_EARN_DED.ded_cd
GARN_ID Garnishment ID Populate with the Employee/Garnishment Deduction combination's EMPL_GRN.garn_id
S_DED_TYPE Deduction Type DED_CD_CORP.s_ded_type where ded_cd = Garnishment Deduction Code being processed
GARN_AMT Garnishment Amount EMPL_EARN_DED.ded_amt
MODIFIED_BY ID of the user who last created or modified row DELTEK
TIME_STAMP Date and time of row creation or last modification System Date and Time
ROWVERSION System-assigned number to handle row concurrency 0

After installing Costpoint 7.0, you must update the deduction types currently assigned to the garnishment deductions. The valid deduction types for the Employee Gov’t Mandated Orders functionality were:

  • Garnishment of Wages
  • Child Support Payments
  • Tax Levy

These three deduction types are not valid for use with the Costpoint 7.0 Employee Garnishments functionality. You must open the Manage Deductions screen, query each of the existing garnishment deductions, and change the Deduction Type to one of the new garnishment deduction types:

  • Student Loan Garnishment
  • Creditor Debt Garnishment
  • Federal Admin Garnishment
  • Child Medical Support
  • Child Support Garnishment
  • Federal Tax Levy
  • State Tax Levy