Import Workers Compensation script

We determine in the system constant the path to the folder where you store files with information of workers compensation.

The script runs on a set schedule.

• It searches for the file with the format Workers Comp <YYMMDD>.XLSX at the specified folder and takes the file with the current day.
• The script calculates the pay date from the A5 string (first found date).
• Then it searches for the payroll period that has a default payroll check with the calculated pay date.
• If it finds the required one, then import continues.
• Next step is search for not default check. If it finds, then it records data from the file to the check's cost rate table. Otherwise, the script creates a new not default check and import rates to the table as well.

In either cases, Pay Date in Payroll Checks is calculated as follows: calculated Pay Date from the A5 string + 1 day.

The script pull in next data from the file:

• Search Employee in the FCX by Employee ID specified in C column (Emp Id).
• If it finds, then takes all positive amount from I column (Premium).

Payroll Item is determined in the script settings. 

It doesn't import empty, 0 and negative amounts.

• During the import, the system clear the cost rates table in the payroll check and records data from the file.
• As soon as import is finished, the system saves the check and recalculates the related payroll period.

Load Employee Payroll Data (PTO hrs) from File

We determine in the system constant the path to the folder where you store files with information of vacation and sick hours (used, available).

The script runs on a set schedule.

• It searches for the file with the format "PTO " <YYMMDD>.CSV at the specified folder and takes the file with the current day.
• If it finds the required one, then proceed import.

The script pulls in next data from the file:

• Searches Employee in the FCX by Employee ID specified in the 8 column.
• If it finds, then it goes to the 12th column (description) and determines where vacation or sick hours.

Anything that corresponds to “Vacation” or “PTO” = vacation hours, "Sick" = sick hours respectively. The register is not important here.

• Vacation or Sick Used hrs from the 16th column. 
• Vacation or Sick Available Hours from the 17th column.

Hrs are recorded to the corresponding fields in the Employee card: Vacation Hours Available, Vacation Hours Used (Synced), Sick Hours Available, Sick Hours Used (Synced).